HOW TO CREATE PHYSICAL STANDBY

Primary Database Steps

1) Enable Forced Logging

SQL> ALTER DATABASE FORCE LOGGING;

2) Create a Password File

 

[csnetsouthdb:oem10g] > cd $ORACLE_HOME/dbs
[csnetsouthdb:oem10g] > orapwd file=orapwwebapp password=oracle force=y

      

  3) Set Primary Database Initialization Parameters

LOG_ARCHIVE_DEST_1='LOCATION=/webapp_arch/arch'

LOG_ARCHIVE_DEST_2='SERVICE= WEBAPP_STDBY’

log_archive_dest_state_1='enable'

log_archive_dest_state_2='enable'

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
#Standby role parameters------------------------------------------FAL_SERVER='WEBAPP_STDBY'   (SERVICE NAME for STNDBY)

FAL_CLIENT= 'WEBAPP_PROD'      (SERVICE NAME for PROD)                 

STANDBY_FILE_MANAGEMENT='AUTO'

STANDBY_ARCHIVE_DEST ='/webapp_arch/arch'

4) Make a tns entry in tnsnames.ora in Production

 WEBAPP_STDBY=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.22)(PORT = 1533))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = WEBAPP)

    )

  )

WEBAPP_PROD=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.12)(PORT = 1533))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = WEBAPP)

   )

5) Make an entry in listener.ora in Production

 ORA11G_PROD =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))

      (ADDRESS = (PROTOCOL = TCP)(HOST =10.100.201.12)(PORT = 1533))

    )

)

SID_LIST_ORA11G_PROD =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME=/ora11g/app/oracle/11.1.3)

      (SID_NAME = WEBAPP)

    )

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /ora11g/app/oracle/11.1.3)

      (PROGRAM = extproc)

    ) )

STEPS NEEDS TO PERFORM ON TARGET STNADBY LOCATION

1. Log into the new standby server where UNIX team stored our database.

2. Make a tns entry in tnsnames.ora in new standby

WEBAPP_STDBY=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.22)(PORT = 1533))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = WEBAPP)

    )

  )

WEBAPP_PROD=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.201.12)(PORT = 1533))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = WEBAPP)

    )

  

3. Make an entry in listener.ora in new standby

ORA11G_STDBY =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))

      (ADDRESS = (PROTOCOL = TCP)(HOST =10.100.201.22)(PORT = 1533))

    )

)

SID_LIST_ORA11G_STDBY=

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME=/ora11g/app/oracle/11.1.3)

      (SID_NAME = WEBAPP)

    )

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /ora11g/app/oracle/11.1.3)

      (PROGRAM = extproc)

    ) )

 

4. Edit Parameter file with the following parameter in new standby.

FAL_SERVER='WEBAPP_PROD' (SERVICE NAME for PROD)

FAL_CLIENT='WEBAPP_STDBY'  (SERVICE NAME for STNDBY)

STANDBY_FILE_MANAGEMENT='AUTO'

LOG_ARCHIVE_DEST_1='LOCATION=/webapp_arch/arch'

log_archive_dest_state_1='enable'

STANDBY_ARCHIVE_DEST ='/webapp_arch/arch'

5. LOG IN TO PRIMARY DATABASE and create standby control file.

    alter database create standby controlfile as '/home/users/oem10g/ctlstdby.ctl';

6. copy created control file to target standby location at the same place where we have mentioned in control file.

7.  conn to oracle instance with sysdba privilege

sqlplus " / as sysdba"

8. Then startup nomount

9. Then put the standby database in mount mode.

Alter database mount standby database;

10. Then recover database

 recover standby database;

11. Start the redo apply:

 SQL> alter database recover managed standby database disconnect from session;

12. On the Primary:

SQL> alter system switch logfile;

SQL> alter system archive log current;

13. Verify the Physical Standby Database Is Performing properly or not on both Primary and Standby

SQL>select SEQUENCE#, ARCHIVED, APPLIED, STATUS from v$archived_log;