Physical Standby Database Implementation

Database	DB_NAME	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	ORCL	ORCL	ORCL
Physical standby	ORCL	ORCL_STBY	ORCL_STBY

Implementation

1)       Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE

------------

NOARCHIVELOG

2)       If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

3)       Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

4)      Set Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "ORCL" on the primary database.

SQL> show parameter db_name

NAME                                                                                      TYPE       VALUE

------------------------------------ ----------- ------------------------------

db_name                                                         string     ORCL

SQL> show parameter db_unique_name

NAME                                                                                      TYPE       VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                                                               string     ORCL

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "ORCL_STBY".

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STBY)';

LOG_ARCHIVE_DEST_1='LOCATION=/backup/archs VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STBY';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

ALTER SYSTEM SET FAL_SERVER=ORCL_STBY;

ALTER SYSTEM SET FAL_CLIENT=ORCL;

--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='ORCL_STBY','ORCL' SCOPE=SPFILE;

--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='ORCL_STBY','ORCL'  SCOPE=SPFILE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

5)       Create a Backup Copy of the Primary Database Datafiles Use can use any backup method . We prefer RMAN

RMAN>backup database plus archivelog;

6)       Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database.

STARTUP MOUNT;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl_stby.ctl';

ALTER DATABASE OPEN:

7)       Create a parameter file for the standby database.

CREATE PFILE='/tmp/initorcl_stby.ora' FROM SPFILE;

8)    Modifying Initialization Parameters for a Physical Standby Database

Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.

*.db_unique_name='ORCL_STBY'

*.fal_server='ORCL'

*.log_archive_dest_2='SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

*.STANDBY_FILE_MANAGEMENT='AUTO'

9)    Create a window based service 

 Oradim –new  -sid  orcl_stby –inpwsd  pass

for unix export ORACLE_SID

10)     CP password file from primary to  standby an rename as  per standby database name

11)    Configure listener on standby

12)    Add TNS entry on both primary and standby
13)   stratstandby using pfile '/tmp/initorcl_stby.ora'

   

   stratup nomount pfile =’/tmp/initorcl_stby.ora’;

14)    Create a server parameter file for standby database

   Create spfile from pfile =’/tmp/initorcl_stby.ora’;

  Shu immediate;

15)    Start physical standby database

Startup nomount

16)    Restore database from  primary database backup

RMAN> restore database;

17)    Create Redo Logs

Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
You should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo01.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo02.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo03.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL /standby_redo04.log') SIZE 50M;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

18)    Startup redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECTS FROM SESSION;

19)    Test archival operations to the physical standby database. Swich logfile on primary database;

        ALTER SYSTEM SWITCH LOGFILE;

20)     Verify the new redo data was archived on the standby database.

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

   FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME

---------- ------------------ ------------------

         8 11-JUL-14 17:50:45 11-JUL-14 17:50:53

         9 11-JUL-14 17:50:53 11-JUL-14 17:50:58

        10 11-JUL-14 17:50:58 11-JUL-14 17:51:03