Data Guard Physical Standby Creation

After setting up new database with ASM , customer also would like to have a physical standby database data guard configured.New version 12c may also have new properties for standby database creation , but i have used traditional methods. Following steps contain these methods ,

Primary Server  : PNODE

Standby Server : SNODE
Db Name : test

I assume that all servers have necessary Oracle binaries installed for ASM and Oracle database . Also ASM diskgroup(s) are ready as same as primary.

1. Primary database must be configured as force logging mode.This step is necessary for ensuring that all changes made in the database will be captured.

2.While primary db is online full backup is taken with following script

rman target /

run {
  allocate channel t1 DEVICE TYPE DISK;
  allocate channel t2 DEVICE TYPE DISK;
  allocate channel t3 DEVICE TYPE DISK;
  allocate channel t4 DEVICE TYPE DISK;
  backup
    as compressed backupset
    full
    format '/dump/stbyBackup/bkpstby-%s-%T-%U'
    (database);
  release channel t1;
  release channel t2;
  release channel t3;
  release channel t4;
}

Locations and directories should be changed according to your enviroments.

3. After backup is completed successfully, standby database controlfile is created on primary ,

alter database create standby controlfile as '/dump/stbyBackup/stbyCtl.ctl';

4. All backup files,standby controlfile,primary password file , primary spfile is copied to standby server into related directories.

5. Create initfile from spfile on standby server and change following parameters ,

sqlplus / as sysdba
create pfile='inittest.ora' from spfile='spfiletest.ora';

## Change controlfiles parameter showing that new standby controlfile (stbyCtl.ctl)

- control_files ='home/oracle/stbyCtl.ctl' ## This parameter should be changed for your locations.
- DB_UNIQUE_NAME=teststby
- LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,teststby)'
-log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=teststby'
- log_archive_dest_state_1='ENABLE'
- FAL_SERVER=test
- FAL_CLIENT=teststby
- STANDBY_FILE_MANAGEMENT =AUTO

6. You can recreate spfile from initfile

sqlplus / as sysdba
create spfile='spfiletest.ora' from pfile='inittest.ora';

7.Startup database in mount mode.

sqlplus / as sysdba
startup mount

8.Begin restore process ,

rman target /

run {
  allocate channel t1 DEVICE TYPE DISK;
  allocate channel t2 DEVICE TYPE DISK;
  allocate channel t3 DEVICE TYPE DISK;
  allocate channel t4 DEVICE TYPE DISK;
  restore database;
  release channel t1;
  release channel t2;
  release channel t3;
  release channel t4;
}

9. Create stanby redo log files on standby server. 

When you have created standby controlfile following warnings occured to create standby redolog files ,

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 524288000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 524288000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 524288000;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 524288000;

Size attribute depends on current redolog file size on primary server.

10. Add tnsnames entires into tnsnames.ora on standby server 

test = (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=PNODE)(PORT=1521))
                (CONNECT_DATA=(SID=test))
            )

teststby= (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=SNODE)(PORT=1521))
                (CONNECT_DATA=(SID=test))

            )

11. For listener there should be already configured listener from grid home.

Until now, we have created standby database,configured tns and parameters.From this point, primary database parameters will be set and redo apply process will be started.

12. For primary database following parameters are set.

-   log_archive_config='DG_CONFIG=(test,teststby)'
- log_archive_dest_2='SERVICE=teststby LGWR async=20840 reopen=15 max_failure=10 optional net_timeout=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=teststby'
-  standby_file_management='AUTO'

13. Add teststby tnsnames entry into tnsnames.ora on primary server

teststby= (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=SNODE)(PORT=1521))
                (CONNECT_DATA=(SID=test))

            )

14. By tnsping from both servers connection is controlled.

tnsping test
tnsping teststby

Both commands must return "OK" ,

tnsping teststby >
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=teststby)(PORT=1521)) (CONNECT_DATA=(SID=test)))
OK (10 msec)

15. startup standby database on stanby server and log 

startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session; 

recovery operation starts and redo apply services apply logs to the standby database.

16. Enable log shipping on primary 

alter system set log_archive_dest_state=ENABLE;