Description:-
In this is the article we are going to see step-by-step to create a physical standby database using RMAN in Oracle Database 19c.
Parameters needs to configure both side for Dataguard,
PRIMARY :
[oracle@trichy ~]$ . oraenv ORACLE_SID = [cdb1] ? prod The Oracle base remains unchanged with value /u01/app/oracle [oracle@trichy ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 15 09:14:12 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2415917880 bytes Fixed Size 8899384 bytes Variable Size 520093696 bytes Database Buffers 1879048192 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
Make sure primary is in archivelog mode
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6
Primary side:
Check FORCE LOGGING is enabled,
SQL> select force_logging from v$database; FORCE_LOGGING ---------------- NO SQL> alter database force logging; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING ---------------- YES SQL> alter system set log_archive_config='dg_config=(PROD,STANDBY)'; System altered. SQL> alter system set log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)'; System altered. SQL> alter system set log_archive_dest_2='service=STANDBY lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=STANDBY'; System altered. SQL> alter system set log_archive_dest_state_2=enable; System altered. SQL> alter system set log_archive_dest_state_1=enable; System altered. SQL> alter system set remote_login_passwordfile=exclusive scope=spfile; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile; System altered. SQL> alter system set log_archive_max_processes=10; System altered. SQL> alter system set db_unique_name=PROD scope=spfile; System altered. SQL> alter system set fal_client=PROD; System altered. SQL> alter system set fal_server=STANDBY; System altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup SQL> create pfile='/home/oracle/initprod.ora' from spfile; File created. *.db_unique_name='STANDBY' *.fal_client='STANDBY' *.fal_server='PROD' *.log_archive_config='dg_config=(PROD,STANDBY)' *.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)' *.log_archive_dest_2='service=PROD lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=PROD' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=10 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' [oracle@trichy ~]$ scp -r initprod.ora oracle@192.168.1.14:/u01/app/oracle/product/19.0.0/dbhome_1/dbs The authenticity of host '192.168.1.14 (192.168.1.14)' can't be established. ECDSA key fingerprint is SHA256:jU17jN8XF6AHRAi6HsDXnoQdDYXOlScWvmZWxXK+kw8. ECDSA key fingerprint is MD5:e9:ac:84:59:86:d4:47:ab:e5:ac:89:23:b1:1c:1e:8c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.14' (ECDSA) to the list of known hosts. oracle@192.168.1.14's password: initprod.ora 100% 1566 1.1MB/s 00:00 [oracle@trichy ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [oracle@trichy dbs]$ ls hc_prod.dat init.ora lkPROD orapwprod spfileprod.ora
Copy password file from primary to standby server,
[oracle@trichy dbs]$ scp -r orapwprod oracle@192.168.1.14:/u01/app/oracle/product/19.0.0/dbhome_1/dbs oracle@192.168.1.14's password: orapwprod
Listener and TNS Configuration :-
LISTENER_CONFIG
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD) (ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = prod) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.15 )(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
TNS_CONFIG
PROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) )
Make the respective directories for standby database,
STANDBY :
[oracle@trichy ~]$ mkdir -p /u01/app/oracle/admin/prod/adump [oracle@trichy ~]$ mkdir -p /u01/app/oracle/oradata/PROD/ [oracle@trichy ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD/
Edit the parameters in pfile for standby
*.db_unique_name='STANDBY' *.fal_client='STANDBY' *.fal_server='PROD' *.log_archive_config='dg_config=(PROD,STANDBY)' *.log_archive_dest_1='location=/u01/archive valid_for=(all_logfiles,primary_role)' *.log_archive_dest_2='service=PROD lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=PROD' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=10 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO'
Listener and TNS Configuration :-
STANDBY_LISTENER_CONFIG
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = PROD) (ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = prod) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.14 )(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
STANDBY_TNS_CONFIG
PROD = DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) ) STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) )
Keep the database in NOMOUNT stage to create standby database,
[oracle@trichy ~]$ . oraenv ORACLE_SID = [cdb1] ? prod The Oracle base remains unchanged with value /u01/app/oracle [oracle@trichy ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 15 09:14:12 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 2415917880 bytes Fixed Size 8899384 bytes Variable Size 520093696 bytes Database Buffers 1879048192 bytes Redo Buffers 7876608 bytes SQL>
In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance) [oracle@trichy dbs]$ rman target sys/oracle@PROD Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jun 15 10:42:32 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD (DBID=422602599) RMAN> connect auxiliary sys/oracle@STANDBY connected to auxiliary database: PROD (not mounted) RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 15-JUN-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprod' ;
}
executing Memory Script
Starting backup at 15-JUN-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
Finished backup at 15-JUN-19
duplicating Online logs to Oracle Managed File (OMF) location
contents of Memory Script:
{
restore clone from service 'PROD' standby controlfile;
}
executing Memory Script
Starting restore at 15-JUN-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/PROD/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
Finished restore at 15-JUN-19
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/PROD/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/PROD/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/PROD/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/PROD/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/PROD/users01.dbf";
restore
from nonsparse from service
'PROD' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/PROD/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 15-JUN-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
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/PROD/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
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/PROD/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:23
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:59
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 15-JUN-19
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'PROD'
archivelog from scn 2187262;
switch clone datafile all;
}
executing Memory Script
Starting restore at 15-JUN-19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PROD
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUN-19
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1011006513 file name=/u01/app/oracle/oradata/PROD/users01.dbf
contents of Memory Script:
{
set until scn 2190007;
recover standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 15-JUN-19
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:04
Finished recover at 15-JUN-19
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc RECID=1 STAMP=1011006509
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc RECID=2 STAMP=1011006510
Deleted 2 objects
Finished Duplicate Db at 15-JUN-19.
Post check status for Standby database,
SQL> select name,database_role,open_mode from v$database; NAME DATABASE_ROLE OPEN_MODE ——— ————-------- ——————– PROD PHYSICAL STANDBY MOUNTED Enable the recovery: SQL> alter database recover managed standby database disconnect from session; Database altered. Check the Standby database sync status with primary: SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; Thread Last Sequence Received Last Sequence Applied Difference ———— ———————————— ———–------------------- --------------- 1 10 10 0
Catch Me On:- Hariprasath Rajaram
LinkedIn: https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook: https://www.facebook.com/HariPrasathdba
FB Group: https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
