Unable to start listener of standby database

dataguardoracleoracle-11g-r2standby

As part of data guard configuration, I've installed standby database using Oracle Software only and configured .ora files/folders as required. But while trying to start the listener or check the status in standby, it's throwing the following error. 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
64-bit Windows Error: 61: Unknown error

Then, I tried to start db using pfile and that too shows error as follows.

SQL> startup pfile=C:\app_home\product\11.2.0\dbhome_1\database\initLOCAL2.ora
ORA-12560: TNS:protocol adapter error

As I'm doing this for the first time, I'm very much confused at this stage. How can I resolve this?

Best Answer

Here are the steps I follow when creating a standby for a dataguard environment on windows server, please confirm in comments at which step you currently are to help identify your issue here. (prd1 is the current alive instance, stby1 is the standby instance you're trying to create)

1- install the oracle binaries and patch them to the same level as the live instance without create a database on the stby1's host server

2- On the prd1 instance create a control file for the standby ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\CONTROL01.CTL';

3- copy the content of %ORACLE_HOME%\database (windows) to the same path from prd1 to stby1

4- use a CMD window to manually create the windows services for the standby database instance

set ORACLE_HOME=[path to oracle_home]

oradim -new -sid STBY1

set ORACLE_SID=STBY1

sqlplus / as sysdba

startup mount;

5- Make sure the listener.ora and tnsnames.ora files are configured correctly on both prd1 and stby1

%ORACLE_HOME%\network\admin\tnsnames.ora on both server contains something like

prd1_server1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prd1)
    )
  )
stby1_server2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby1)
    )
  )

6- %ORACLE_HOME%\network\admin\listener.ora on server1 (prd1) contains

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =prd1_DGMGRL)
      (ORACLE_HOME = [path to oracle_home])
      (SID_NAME = prd1)
    )
  )

7- listener.ora on stby1 server2 contains something like

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
  )


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME =stby1_DGMGRL)
      (ORACLE_HOME = [path to oracle_home])
      (SID_NAME = stby1)
    )
  )

8- Once you got all the above settings figured out you go on the stby1 instance and recover the database using SQLPLUS

RECOVER STANDBY DATABASE;
Related Question