Creating Physical Standby using RMAN Backup:
A physical standby database is an exact, block-for-block copy of a primary database.
A physical standby is maintained as an exact copy through a process called Redo Apply,
in which redo data received from a primary database is continuously applied to a
physical standby database using the database recovery mechanisms.
Primary database side changes:
select name,open_mode,database_role,PROTECTION_LEVEL from V$database;
Step:1 Enable Archive log mode and Flashback on
Flashback database is highly recommended because in case of failover,
you need not re-create primary database from scratch
Step:2 Enable Force Logging
select force_logging from v$database;
alter database force logging;
Step:3 Adding Redologfile for standby database
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
step4:-Changing parameters in primary database
SQL> ALTER SYSTEM SET db_unique_name=’rman’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(rman,standby)’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=rman’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=standby’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server=’STANDBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client=’RMAN’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert=’/u01/app/oracle/oradata/STANDBY/datafile’,’/u01/app/oracle/oradata/RMAN/datafile’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert=’/u01/app/oracle/oradata/STANDBY/onlinelog’,’/u01/app/oracle/oradata/RMAN/onlinelog’ SCOPE=SPFILE;
SQL> alter system set log_archive_config='dg_config=(rman,standby)' SCOPE=SPFILE;
The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the
standby sites. The DB_UNIQUE_NAME of the primary database is primary and the DB_UNIQUE_NAME
of the standby database is standby.
The primary database is configured to ship redo log stream to the standby database.
STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the
primary database, these changes are made to the standby databases automatically.
The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.
Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using
Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.
Step:5 Adding the network entry in primary and standby side(Both servers)
After entries ,ensure tnsping and ping works on both side
Step 6:CREATE PFILE FROM SPFILE
CREATE PFILE='/home/oracle/initrman.ora' FROM SPFILE;
Standby side configuration:
Step1:- Moving password file and Rman database backup files from primary to standby using scp
scp orapwrman oracle@192.168.1.50:$ORACLE_HOME/dbs
scp backup oracle@192.168.1.50:/home/oracle
Step2:-Creating necessary folders
mkdir -p '/u01/app/oracle/admin/rman/adump'
mkdir -p /u01/app/oracle/oradata/RMAN
mkdir -p /u01/app/oracle/fast_recovery_area/RMAN
Step3:- start the standby database using the edited pfile
Startup nomount pfile=/u01/app/oracle/product/12.2.0/db_1/dbs/initrman.ora
Step4:- connect to the rman and issue the duplicate command
RMAN> duplicate target database for standby backup location '/home/oracle/backup' nofilenamecheck;
Starting Duplicate Db at 16-MAR-21
contents of Memory Script:
{
restore clone standby controlfile from '/home/oracle/backup/Control_RMAN_3tvpr00k_125';
}
executing Memory Script
Starting restore at 16-MAR-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/RMAN/controlfile/o1_mf_j2rdy8xp_.ctl
output file name=/u01/app/oracle/fast_recovery_area/rman/RMAN/controlfile/o1_mf_j2rdy925_.ctl
Finished restore at 16-MAR-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/STANDBY/datafile/o1_mf_temp_%u_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-MAR-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STANDBY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/database_RMAN_3mvpqvik_118
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup/database_RMAN_3mvpqvik_118 tag=TAG20210315T202139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:41
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/STANDBY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/STANDBY/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/database_RMAN_3lvpqvik_117
channel ORA_AUX_DISK_1: piece handle=/home/oracle/backup/database_RMAN_3lvpqvik_117 tag=TAG20210315T202139
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 16-MAR-21
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1067372985 file name=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_system_j51kwdfk_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1067372985 file name=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_sysaux_j51kxxnw_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1067372985 file name=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_undotbs1_j51kwdg2_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1067372985 file name=/u01/app/oracle/oradata/STANDBY/datafile/o1_mf_users_j51kxxp0_.dbf
Finished Duplicate Db at 16-MAR-21
Step 5:
SQL> select name,open_mode,database_role,PROTECTION_LEVEL from V$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL
--------- -------------------- ---------------- --------------------
RMAN MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE
Successfully configured the Oracle 12c Data Guard Physical Standby
Recent Posts