Contents

This document gives high level steps to create physical standby database for primary database using RMAN backups taken from primary database.

Naming convention – Primary (Primary database name)

Standby (standby database name)

Below are the high-level steps to accomplish the task:

Step 1: Backup the database that includes backup of data files, archive logs and control file for standby
Step 2: Move the backups to the standby server
Step 3: Make proper changes in the parameter files of both primary and standby database, configure Oracle net services on both the servers.
Step 4: Do the restore and recover on standby database
Step 5: Configure the primary database to transfer the archives to standby and put the standby database in recover managed mode

Step 1: Backup the primary database that includes backup of data files, archive logs and control file for standby

[oracle@drserver01][oracle]> rman target /

run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> allocate channel c3 type disk;
6> backup database plus archivelog;
7> }

RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup current controlfile for standby;
5> }

The above script is very generic it can be modified based on DBA perspective.

Step 2: Move the backups to the standby server

Post backup completion use SCP to move the backup pieces from Primary server to standby server.

Step 3: Make proper changes in the parameter files of both primary and standby database

Ensure the primary database is set to FORCE LOGGING and the database is in ARCHIVELOG mode, else convert the database to Archive log mode and use the below command to put the primary database to FORCE LOGGING.

SELECT NAME, FORCE_LOGGING, LOG_MODE FROM GV$DATABASE;

The output should be YES for force logging, else execute the below command to enable force logging in primary database.

Alter database force logging;

Add the below parameter in primary database parameter file:

log_archive_dest_2=SERVICE=STANDBY LGWR ASYNC NOAFFIRM VALID_FOR= (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY

log_archive_config =DG_CONFIG= (PRIMARY, STANDBY)

*.standby_file_management='auto'

Create a pfile from primary database and transfer it to standby modify/add the below parameters in standby database parameter file:
Configure Oracle net services on both the servers.
Don’t remove underscore or any other parameters while creating pfile for your standby database.

db_unique_name='STANDBY'
*.instance_name='stanby'
db_file_name_convert=/DATA01/PRIMARY/DATAFILE/, +DATA01/PRIMARY/DATAFILE/,/DATA02/PRIMARY/DATAFILE/,+DATA01/PRIMARY/DATAFILE/

*.log_file_name_convert='/REDO/STANDBY/','+DATA01/PRIMARY/ONLINELOG/'
*.db_create_file_dest='/DATA01/PRIMARY/DATAFILE/'

*.db_create_online_log_dest_1='/REDO/STANDBY/'

*.core_dump_dest='/oradb01/app/oracle/diag/rdbms/STANDBY/STANDBY/cdump'

*.control_files='/DATA01/STANDBY/CONTROLFILE/control01.ctl','/DATA02/STANDBY/CONTROLFILE/control02.ctl’

*.standby_file_management='auto'

Modify the instance name in standby server parameter file based on environment, for example the prod instance on DC RAC is PRIMARY1 & PRIMARY2 so for differentiation purpose we have created instance in DR as standby.

Step 4: Do the restore and recover on standby database

Start the standby database in nomount state using the parameter file created in step: 3

[oracle@drserver01][oracle]>sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 11 14:11:20 2019

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile='/BACKUP/DCBACKUP/standby_pfile.ora'

ORACLE instance started.

Total System Global Area 7.7309E+10 bytes

Fixed Size 6101992 bytes

Variable Size 2.0133E+10 bytes

Database Buffers 5.6908E+10 bytes

Redo Buffers 262332416 bytes

SQL> exit

To restore control file you need to use the below command:

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 11 14:16:49 2019

connected to target database: PRIMARY (not mounted)

RMAN> restore standby controlfile from '/BACKUP/DCBACKUP/PRIMARY_CTLFORSTANDBY.bkp';

Starting restore at 11-FEB-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=8898 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/DATA01/STANDBY/CONTROLFILE/control01.ctl

output file name=/DATA02/STANDBY/CONTROLFILE/control02.ctl

Finished restore at 11-FEB-19

Mount the standby database

sql 'alter database mount';

Catalog the backup before starting the restore,

catalog start with '/BACKUP/DCBACKUP/';

Restore the standby database using below command

# Sample script:
# ---------------
# If filesystem layout is different at standby, use SET NEWNAME to restore files
# for more details see RMAN documentation

RMAN> run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK ;
set newname for datafile 1 to '/u01/oracle/product/ora10g/oradata/standby/data/sys01.dbf' ;
set newname for datafile 2 to '/u01/oracle/product/ora10g/oradata/standby/data/undo01.dbf' ;
set newname for datafile 3 to '/u01/oracle/product/ora10g/oradata/standby/data/sysaux01.dbf';
set newname for datafile 4 to '/u01/oracle/product/ora10g/oradata/standby/data/rman.dbf' ;
SQL "ALTER DATABASE RENAME FILE ''/u01/oracle/product/ora10g/oradata/primary/log/redo01.log''
TO ''/u01/oracle/product/ora10g/oradata/standby/log/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u01/oracle/product/ora10g/oradata/primary/log/redo02.log''
TO ''/u01/oracle/product/ora10g/oradata/standby/log/redo02.log'' ";
restore database;
switch datafile all ;
}

In our case (for standby database restoration) we used the script below,

run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk3 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk5 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk6 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk7 DEVICE TYPE DISK;
ALLOCATE CHANNEL disk8 DEVICE TYPE DISK;
      set newname for datafile 1 to '/DATA01/STANDBY/datafile/system.150.975869111':
      set newname for datafile 3 to '/DATA01/STANDBY/datafile/system.360.975869111';
      set newname for datafile 1 to '/DATA01/STANDBY/datafile/system.155.975869595';
      set newname for datafile 5 to '/DATA01/STANDBY/datafile/system.138.975869619';
      set newname for datafile 5 to '/DATA01/STANDBY/datafile/system.105.975869515';
      set newname for datafile 6 to '/DATA01/STANDBY/datafile/system.366.975869111';
      set newname for datafile 7 to '/DATA01/STANDBY/datafile/system.396.975869319';
      set newname for datafile 8 to '/DATA01/STANDBY/datafile/system.368.975869191';
      set newname for datafile 9 to '/DATA01/STANDBY/datafile/system.385.975869111';
      set newname for datafile 10 to '/DATA01/STANDBY/datafile/system.115.975869537';
      set newname for datafile 11 to '/DATA01/STANDBY/datafile/system.100.975869111';
      set newname for datafile 13 to '/DATA01/STANDBY/datafile/tbs1_undots1.106.975869551';
      set newname for datafile 11 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab.397.975869655';
      set newname for datafile 15 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab1.108.975869111';
      set newname for datafile 15 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab3.136.975869111';
      set newname for datafile 16 to '/DATA01/STANDBY/datafile/tbs1_ts_log.113.975869601';
      set newname for datafile 17 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab5.395.975869597';
      set newname for datafile 18 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab5.379.975869677';
      set newname for datafile 19 to '/DATA01/STANDBY/datafile/tbs1_ts_seed.109.975869111';
      set newname for datafile 30 to '/DATA01/STANDBY/datafile/tbs1_ts_seed.358.975869111';
      set newname for datafile 31 to '/DATA01/STANDBY/datafile/tbs1_ts_summary.131.975869111';
      set newname for datafile 33 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.365.975869111';
      set newname for datafile 31 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.115.975869139';
      set newname for datafile 35 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.378.975869111';
      set newname for datafile 35 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.101.975869111';
      set newname for datafile 36 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.139.975869111';
      set newname for datafile 37 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.115.975869111';
      set newname for datafile 38 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.107.975869111';
      set newname for datafile 39 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.103.975869111';
      set newname for datafile 10 to '/DATA01/STANDBY/datafile/trdcf.381.975869167';
      set newname for datafile 11 to '/DATA01/STANDBY/datafile/oradatatab11232.399.975869585';
      set newname for datafile 13 to '/DATA01/STANDBY/datafile/olap11212.111.975869111';
      set newname for datafile 11 to '/DATA01/STANDBY/datafile/owapubt.130.975869551';
      set newname for datafile 15 to '/DATA01/STANDBY/datafile/datatab2.111.975869559';
      set newname for datafile 15 to '/DATA01/STANDBY/datafile/sysaux.361.975869139';
      set newname for datafile 16 to '/DATA01/STANDBY/datafile/tbs1_ts_tools.395.975869575';
      set newname for datafile 17 to '/DATA01/STANDBY/datafile/rim.119.975869661';
      set newname for datafile 18 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.117.975869599';
      set newname for datafile 19 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.365.975869575';
      set newname for datafile 50 to '/DATA01/STANDBY/datafile/tbs1_undots1.157.975869111';
      set newname for datafile 51 to '/DATA01/STANDBY/datafile/system.385.975869111';
      set newname for datafile 53 to '/DATA01/STANDBY/datafile/system.151.975869111';
      set newname for datafile 51 to '/DATA01/STANDBY/datafile/system.390.975869111';
      set newname for datafile 55 to '/DATA01/STANDBY/datafile/system.116.975869111';
      set newname for datafile 55 to '/DATA01/STANDBY/datafile/system.115.975869111';
      set newname for datafile 56 to '/DATA01/STANDBY/datafile/system.359.975869111';
      set newname for datafile 57 to '/DATA01/STANDBY/datafile/system.370.975869139';
      set newname for datafile 58 to '/DATA01/STANDBY/datafile/system.135.975869111';
      set newname for datafile 59 to '/DATA01/STANDBY/datafile/system.357.975869111';
      set newname for datafile 50 to '/DATA01/STANDBY/datafile/system.388.975869111';
      set newname for datafile 51 to '/DATA01/STANDBY/datafile/system.363.975869139';
      set newname for datafile 53 to '/DATA01/STANDBY/datafile/system.373.975869111';
      set newname for datafile 51 to '/DATA01/STANDBY/datafile/system.398.975869111';
      set newname for datafile 55 to '/DATA01/STANDBY/datafile/tbs1_ts_seed.151.975869111';
      set newname for datafile 55 to '/DATA01/STANDBY/datafile/tbs1_ts_seed.391.975869111';
      set newname for datafile 56 to '/DATA01/STANDBY/datafile/tbs1_ts_seed.371.975869111';
      set newname for datafile 57 to '/DATA01/STANDBY/datafile/databasefile.131.975869139';
      set newname for datafile 58 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.113.975869139';
      set newname for datafile 59 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab.110.975869551';
      set newname for datafile 60 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab1.118.975869177';
      set newname for datafile 61 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab3.153.975869179';
      set newname for datafile 63 to '/DATA01/STANDBY/datafile/tbs1_ts_nologging.376.975869615';
      set newname for datafile 61 to '/DATA01/STANDBY/datafile/tbs1_ts_datatab5.391.975869179';
      set newname for datafile 65 to '/DATA01/STANDBY/datafile/tbs1_ts_seed.105.975869617';
      set newname for datafile 65 to '/DATA01/STANDBY/datafile/tbs1_ts_summary.375.975869185';
      set newname for datafile 66 to '/DATA01/STANDBY/datafile/tbs1_ts_tools.135.975869685';
      set newname for datafile 67 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.137.975869185';
      set newname for datafile 68 to '/DATA01/STANDBY/datafile/tbs1_ts_tx_data.111.975869687';
      set newname for datafile 69 to '/DATA01/STANDBY/datafile/sysaux.383.975869111';
      set newname for datafile 70 to '/DATA01/STANDBY/datafile/yymeerrty.380.975869515';
      set newname for datafile 71 to '/DATA01/STANDBY/datafile/tbs1_undots3.361.975937391';
      set newname for datafile 73 to '/DATA01/STANDBY/datafile/tbs1_undots3.393.975937391';
restore database;
Switch datafile all;
release CHANNEL isk1;
release CHANNEL disk3;
release CHANNEL disk1;
release CHANNEL disk5;
release CHANNEL disk5;
release CHANNEL disk6;
release CHANNEL disk7;
release CHANNEL disk8;
}

Recover database until last archive log sequence#, check primary database RMAN backup log file for getting the archive log sequence#.

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 12 10:24:36 2019

connected to target database: PRIMARY (DBID=345678912098, not open)

RMAN> recover database until sequence 4428 thread 1;

Starting recover at 12-FEB-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2229 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=3343 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=4453 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=5565 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=6677 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=7785 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=8900 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination