This document describes the different steps to follow to set up a standby database in standalone using Oracle dataguard, with production site set up with Oracle RAC cluster database.
PREREQUISITES
The DR configuration assumes certain installations and configurations have already been completed, including:
- Installation of the operating systems for the various database servers at both sites
- Configuration of the Grid Infrastructure and ASM storage at both sites
- Installation of the RAC Cluster database at the primary site
- Installation of ORACLE_HOME at the DR site (for a standalone database on DR)
- Existing network connection between the primary and secondary sites
Below is a summary of the main configuration parameters for the primary and secondary databases:
RAC Cluster nodes and ASM informations
Replication architecture
A. PRIMARY DATABASE PREPARATION
The production environment must be correctly configured to operate in Data Guard mode:
Enable Force Logging.
It is necessary to force the logging of actions even for objects where the NOLOGGING clause is enabled. This clause prevents the generation of records in the redo log for DDL queries. Replication is based on information from the redo logs archived in the archive files.
SQL>alter database force logging sid=’*’;
Modify the init file parameters
Below are the important parameters to consider for replication in the primary site’s init file:
DB_NAME=PRIM
DB_UNIQUE_NAME= PRIM
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM, STBY)’
LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= PRIM
LOG_ARCHIVE_DEST_2=’SERVICE= STBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= STBY’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER= STBY
DB_FILE_NAME_CONVERT=’STBY’,’PRIM’
LOG_FILE_NAME_CONVERT= ‘STBY’,’PRIM’
STANDBY_FILE_MANAGEMENT=AUTO
Modify database parameters like below :
SQL> alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STBY)’ scope=both sid=’*’;
SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM’ scope=both sid=’*’;
SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=STBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY’ scope=both sid=’*’;
SQL> alter system set log_archive_max_processes=8 scope=both sid=’*’;
SQL> alter system set fal_server=STBY scope=both sid=’*’;
SQL> alter system set fal_client=’PRIM1′ sid=’PRIM1′;
SQL> alter system set fal_client=’PRIM2′ sid=’PRIM2′;
SQL> alter system set db_file_name_convert=’STBY’,’PRIM’ scope=spfile sid=’*’;
SQL> alter system set log_file_name_convert=’STBY’,’PRIM’ scope=spfile sid=’*’;
SQL> alter system set standby_file_management=AUTO scope=both sid=’*’;
SQL> alter system set remote_login_passwordfile=’EXCLUSIVE’ sid=’*’;
SQL> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=spfile sid=’*’;
Enable Archivelog mode
Verify that the primary database is in archive log mode; if not, enable archiving for the cluster as shown below:
Disable archive mode from any node (ex: node1)
SQL> alter system set cluster_database=FALSE scope=spfile sid=’PRIM1′;
Stop all instances
$ srvctl stop database -d PRIM
Mount instance 1 and enable archiving
$export ORACLE_SID = PRIM1
SQL> startup mount
SQL> alter database archivelog
Re-enable cluster mode
SQL> alter system set cluster_database=true scope=spfile sid=’PRIM1′;
Stop the instance
SQL> shutdown immediate
Restart all instances
$ srvctl start database -d PRIM
Restart all services (if needed). Exple for service SERV :
$ srvctl start service -d SERV
Create Standby Redo Logs (SLRs)
It is necessary to create redo log files for the standby database on both the primary and secondary databases. The number of SLR groups to create must be greater than or equal to (threads) * (groups per thread + 1).
In our case: Threads = 2, Groups per thread = 2, so a minimum of 2 * (2 + 1) = 6 SLRs must be created.
Therefore, 6 SLR groups will be created, each with a size of 3 GB.
Note: Do not duplicate members within the SLR groups to be created.
To check actual prod redo groups and members, execute :
SQL> set line 300
SQL> col lmember format a60
SQL> set pagesize 50
SQL> select thread#,l.group#,l.status,substr(lf.member,lf,50) lmember ,l.bytes/1024/1024 size_Mb from v$log l,v$logfile lf where l.group#=lf.group# order by 1,2;
Create SLRS for Primary database
Create destination for SLRs in ASM :
ASMCMD [+FRA] > mkdir +FRA/PRIM/STANDBYLOG
Disable standby_file_management
SQL> alter system set standby_file_management=manual scope=both sid=’*’;
Add SLRs for thread 1
SQL> alter database add standby logfile thread 1 group 5 ‘+FRA/PRIM/STANDBYLOG/standby_group_05.log’ size 3G;
SQL> alter database add standby logfile thread 1 group 6 ‘+FRA/PRIM/STANDBYLOG/standby_group_06.log’ size 3G;
SQL> alter database add standby logfile thread 1 group 7 ‘+FRA/PRIM/STANDBYLOG/standby_group_07.log’ size 3G;
Add SLRs for thread 2
SQL> alter database add standby logfile thread 2 group 8 ‘+FRA/PRIM/STANDBYLOG/standby_group_08.log’ size 3G;
SQL> alter database add standby logfile thread 2 group 9 ‘+FRA/PRIM/STANDBYLOG/standby_group_09.log’ size 3G;
SQL> alter database add standby logfile thread 2 group 10 ‘+FRA/PRIM/STANDBYLOG/standby_group_10.log’ size 3G;
Re-enable standby_file_management
SQL> alter system set standby_file_management=auto scope=both sid=’*’;
Note: The redo and SLR files on the DR will be automatically created using the log_file_name_convert parameter in the DR initialization file.
Listener and tnsnames.ora
To avoid listener errors with the SCAN Listener, which redirects connections based on server load, it is recommended to create a local listener that points to one of the nodes. A common error with the SCAN Listener during RMAN duplication is copying the password file from the primary site to the DR site.
Create the LISTENER_N2 listener on the node-clu2 node, which listens on port 1530, with the following content in the file $ORACLE_HOME/network/admin/listener.ora.
The following should be done on Production node 2 :
SID_LIST_LISTENER_N2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIM2)
(ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = PRIM2) ) )
ADR_BASE_LISTENER_N2 = /opt/app/oracle
LISTENER_N2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node-clu2-vip)(PORT = 1530))
Add these entries in TNS files on DR and node 2:
STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node-dr)(PORT = 1530)) )
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY)))
PRIM_N2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node-clu2-vip)(PORT = 1530))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)))
B. PREPARING THE SECONDARY DATABASE
Password File
Create the password file for the secondary database by either:
- Copying the password file from the primary database
Use scp to copy the password file from $ORACLE_HOME/dbs on PROD to DR:
$scp $ORACLE_HOME/dbs/orapwPRIM1 oracle@node-dr: /opt/app/oracle/product/12.2.0/dbhome_1/dbs/orapwSTBY
- Creating the password file
Use the orapwd command to create the file in $ORACLE_HOME/dbs:
$orapwd file= orapwSTBY password=***** [entries=5] [force=y] [ ignorecase=y]
Note: It is recommended to use the scp password file copy method to avoid certain issues such as below in alertSID.log file:
PING[ARC2]: Heartbeat failed to connect to standby ‘STBY’. Error is 16191.
Error 1017 received logging on to the standby
Create pfile for DR
Create a file (exple initdup.ora) with entries similar to PROD but with some DR specs in italics:
*.db_cache_size=15G
*.pga_aggregate_target=20G
*.db_block_size=32768
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+DATA’
*.db_create_online_log_dest_2=’+FRA’
*.audit_file_dest=’/opt/app/oracle/admin/STBY/adump’
*.cluster_database=false
*.db_file_name_convert=’PRIM’,’STBY’
*.log_file_name_convert=’PRIM’,’STBY’
*.audit_trail=XML,EXTENDED
*.compatible=’12.2.0′
*.control_files=’+DATA/STBY/control01.ctl’,’+FRA/STBY/control02.ctl’
*.db_name=’PRIM’
*.db_unique_name=’STBY’
*.fal_server=’PRIM’
*.fal_client=’STBY’
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=500g
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY’
*.log_archive_dest_2=’SERVICE=PRIM ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM’
*.log_archive_config=’DG_CONFIG=(PRIM,STBY)’
*.log_archive_dest_state_2=’ENABLE’
*.diagnostic_dest=’/opt/app/oracle’
*.local_listener=’LISTENER_STBY’
*.log_archive_format=’%t_%s_%r.arc’
*.log_archive_max_processes=8
*.open_cursors=300
*.processes=3200
*.remote_login_passwordfile=’EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.sga_target=50g
*.undo_tablespace=’UNDOTBS1′
*.standby_file_management=’AUTO’
Listener Configuration
Create a listener (here LISTENER_DG) as shown below, in the listener.ora file. Then start the listener with the lsnrctl command.
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node-dr)(PORT = 1530)) ) )
SID_LIST_LISTENER_DG=
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = STBY) (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = STBY) ) )
$lsnrctl start LISTENER_DG
Create the necessary directories
Create the destination folders for audit files:
$mkdir -p /opt/app/oracle/admin/STBY/adump
Prepare the script for duplication
Edit the duplication file to clone the primary database as a standby database. You can enforce certain parameters to be applied in the init file using the spfile clause.
The duplication file is shown below:
$vi /home/oracle/dup.rcv
RUN {
ALLOCATE CHANNEL C1 TYPE DISK;
ALLOCATE CHANNEL C2 TYPE DISK;
ALLOCATE CHANNEL C3 TYPE DISK;
ALLOCATE CHANNEL C4 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL D1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL D2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL D3 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL D4 TYPE DISK;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
spfile
set control_files=’+DATA/STBY/control01.ctl’,’+FRA/STBY/control02.ctl’
set log_file_name_convert=’PRIM’,’STBY’
set db_file_name_convert=’PRIM’,’STBY’
set db_name=’PRIM’
set cluster_database=’FALSE’
set audit_file_dest=’/opt/oracle/audit’
set db_unique_name=’STBY’
set db_recovery_file_dest=’+FRA’
set fal_client=’STBY’
set fal_server=’PRIM’
set log_archive_config=’dg_config=(STBY,PRIM)’
set log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY’
set log_archive_dest_2=’SERVICE=PRIM ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM’
set standby_file_management=’AUTO’
Start the instance on the DR using the initdup.ora file:
SQL>startup nomount pfile=’?/dbs/initdup.ora’
Defer the copy of the production archives to the DR to avoid potential errors:
SQL> alter system set log_archive_dest_state_2=defer;
Execute the standby database creation command (here in silent mode with nohup):
$nohup rman target sys/<Sys_pwd>@PRIM_N2 auxiliary sys/<Sys_pwd>@STBY cmdfile=/home/oracle/dup.rcv log=/home/oracle/dup.log &
Enable the copy of the production archives to the DR:
SQL> alter system set log_archive_dest_state_2=enable;
Below is an example of the content of the dup.log file from the execution of the duplication command:
Recovery Manager: Release 12.2.0.1.0 – Production on Sat Dec 2 16:37:55 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=3017680630)
connected to auxiliary database: PRIM (not mounted)
RMAN> RUN {
2> ALLOCATE CHANNEL C1 TYPE DISK;
3> ALLOCATE CHANNEL C2 TYPE DISK;
4> ALLOCATE CHANNEL C3 TYPE DISK;
5> ALLOCATE CHANNEL C4 TYPE DISK;
6> ALLOCATE AUXILIARY CHANNEL D1 TYPE DISK;
7> ALLOCATE AUXILIARY CHANNEL D2 TYPE DISK;
8> ALLOCATE AUXILIARY CHANNEL D3 TYPE DISK;
9> ALLOCATE AUXILIARY CHANNEL D4 TYPE DISK;
10> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
11> spfile
12> set control_files=’+DATA/STBY/control01.ctl’,’+FRA/STBY/control02.ctl’
13> set db_file_name_convert=’PRIM’,’STBY’
14> set log_file_name_convert=’PRIM’,’STBY’
15> set db_name=’PRIM’
16> set cluster_database=’FALSE’
17> set audit_file_dest=’/opt/oracle/audit’
18> set db_unique_name=’STBY’
19> set db_recovery_file_dest=’+FRA’
20> set fal_client=’PRIM’
21> set fal_server=’PRIM’
22> set log_archive_config=’dg_config=(STBY,PRIM)’
23> set log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY’
24> set log_archive_dest_2=’SERVICE=PRIM ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM’
25> set standby_file_management=’AUTO’
26> NOFILENAMECHECK;
27> }
28>
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=2083 instance=PRIM2 device type=DISK
allocated channel: C2
channel C2: SID=2471 instance=PRIM2 device type=DISK
allocated channel: C3
channel C3: SID=2654 instance=PRIM2 device type=DISK
allocated channel: C4
channel C4: SID=3039 instance=PRIM2 device type=DISK
allocated channel: D1
channel D1: SID=970 device type=DISK
allocated channel: D2
channel D2: SID=1091 device type=DISK
allocated channel: D3
channel D3: SID=1212 device type=DISK
allocated channel: D4
channel D4: SID=1333 device type=DISK
Starting Duplicate Db at 02-DEC-2017 16:38:12
current log archived
contents of Memory Script:
{
backup as copy reuse
targetfile ‘+DATA/PRIM/PASSWORD/pwdPRIM.276.935327979’ auxiliary format
‘/opt/app/oracle/product/12.2.0/dbhome_1/dbs/orapwSTBY’ ;
restore clone from service ‘PRIM_N2’ spfile to
‘/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfileSTBY.ora’;
sql clone “alter system set spfile= ”/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfileSTBY.ora””;
}
executing Memory Script
Starting backup at 02-DEC-2017 16:38:24
Finished backup at 02-DEC-2017 16:38:25
Starting restore at 02-DEC-2017 16:38:25
channel D1: starting datafile backup set restore
channel D1: using network backup set from service PRIM_N2
channel D1: restoring SPFILE
output file name=/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfileSTBY.ora
channel D1: restore complete, elapsed time: 00:00:01
Finished restore at 02-DEC-2017 16:38:27
sql statement: alter system set spfile= ”/opt/app/oracle/product/12.2.0/dbhome_1/dbs/spfileSTBY.ora”
contents of Memory Script:
{
sql clone “alter system set control_files =
”+DATA/STBY/control01.ctl”, ”+FRA/STBY/control02.ctl” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”PRIM”, ”STBY” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”PRIM”, ”STBY” comment=
”” scope=spfile”;
sql clone “alter system set db_name =
”PRIM” comment=
”” scope=spfile”;
sql clone “alter system set cluster_database =
FALSE comment=
”” scope=spfile”;
sql clone “alter system set audit_file_dest =
”/opt/oracle/audit” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”STBY” comment=
”” scope=spfile”;
sql clone “alter system set db_recovery_file_dest =
”+FRA” comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”PRIM” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”PRIM” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(STBY,PRIM)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_1 =
”LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_2 =
”SERVICE=PRIM ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ”+DATA/STBY/control01.ctl”, ”+FRA/STBY/control02.ctl” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”PRIM”, ”STBY” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”PRIM”, ”STBY” comment= ”” scope=spfile
sql statement: alter system set db_name = ”PRIM” comment= ”” scope=spfile
sql statement: alter system set cluster_database = FALSE comment= ”” scope=spfile
sql statement: alter system set audit_file_dest = ”/opt/oracle/audit” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”STBY” comment= ”” scope=spfile
sql statement: alter system set db_recovery_file_dest = ”+FRA” comment= ”” scope=spfile
sql statement: alter system set fal_client = ”PRIM” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”PRIM” comment= ”” scope=spfile
sql statement: alter system set log_archive_config = ”dg_config=(STBY,PRIM)” comment= ”” scope=spfile
sql statement: alter system set log_archive_dest_1 = ”LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY” comment= ”” scope=spfile
sql statement: alter system set log_archive_dest_2 = ”SERVICE=PRIM ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIM” comment= ”” scope=spfile
sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 128849018880 bytes
Fixed Size 29873376 bytes
Variable Size 37580966688 bytes
Database Buffers 90999619584 bytes
Redo Buffers 238559232 bytes
allocated channel: D1
channel D1: SID=914 device type=DISK
allocated channel: D2
channel D2: SID=1028 device type=DISK
allocated channel: D3
channel D3: SID=1142 device type=DISK
allocated channel: D4
channel D4: SID=1256 device type=DISK
contents of Memory Script:
{
restore clone from service ‘PRIM_N2’ standby controlfile;
}
executing Memory Script
Starting restore at 02-DEC-2017 16:40:17
channel D1: starting datafile backup set restore
channel D1: using network backup set from service PRIM_N2
channel D1: restoring control file
channel D1: restore complete, elapsed time: 00:00:02
output file name=+DATA/STBY/control01.ctl
output file name=+FRA/STBY/control02.ctl
Finished restore at 02-DEC-2017 16:40:20
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
“+DATA/STBY/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
“+DATA/STBY/system01.dbf”;
set newname for datafile 2 to
“+DATA/STBY/sysaux01.dbf”;
set newname for datafile 3 to
“+DATA/STBY/undotbs101.dbf”;
set newname for datafile 4 to
“+DATA/STBY/undotbs201.dbf”;
set newname for datafile 5 to
“+DATA/STBY/users01.dbf”;
set newname for datafile 6 to
“+DATA”;
set newname for datafile 7 to
“+DATA”;
set newname for datafile 8 to
“+DATA”;
set newname for datafile 9 to
“+DATA”;
set newname for datafile 10 to
“+DATA”;
restore
from service ‘PRIM_N2’ clone database
;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA/STBY/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 02-DEC-2017 16:40:26
channel D1: starting datafile backup set restore
channel D1: using network backup set from service PRIM_N2
channel D1: specifying datafile(s) to restore from backup set
channel D1: restoring datafile 00001 to +DATA/STBY/system01.dbf
channel D2: starting datafile backup set restore
channel D2: using network backup set from service PRIM_N2
channel D2: specifying datafile(s) to restore from backup set
channel D2: restoring datafile 00002 to +DATA/STBY/sysaux01.dbf
channel D3: starting datafile backup set restore
channel D3: using network backup set from service PRIM_N2
channel D3: specifying datafile(s) to restore from backup set
channel D3: restoring datafile 00003 to +DATA/STBY/undotbs101.dbf
channel D4: starting datafile backup set restore
channel D4: using network backup set from service PRIM_N2
channel D4: specifying datafile(s) to restore from backup set
channel D4: restoring datafile 00004 to +DATA/STBY/undotbs201.dbf
channel D1: restore complete, elapsed time: 00:01:18
channel D1: starting datafile backup set restore
channel D1: using network backup set from service PRIM_N2
channel D1: specifying datafile(s) to restore from backup set
channel D1: restoring datafile 00005 to +DATA/STBY/users01.dbf
channel D1: restore complete, elapsed time: 00:01:15
channel D1: starting datafile backup set restore
channel D1: using network backup set from service PRIM_N2
channel D1: specifying datafile(s) to restore from backup set
channel D1: restoring datafile 00006 to +DATA
channel D2: restore complete, elapsed time: 00:03:38
channel D2: starting datafile backup set restore
channel D2: using network backup set from service PRIM_N2
channel D2: specifying datafile(s) to restore from backup set
channel D2: restoring datafile 00007 to +DATA
channel D4: restore complete, elapsed time: 00:07:33
channel D4: starting datafile backup set restore
channel D4: using network backup set from service PRIM_N2
channel D4: specifying datafile(s) to restore from backup set
channel D4: restoring datafile 00008 to +DATA
channel D3: restore complete, elapsed time: 00:07:49
channel D3: starting datafile backup set restore
channel D3: using network backup set from service PRIM_N2
channel D3: specifying datafile(s) to restore from backup set
channel D3: restoring datafile 00009 to +DATA
channel D4: restore complete, elapsed time: 00:00:23
channel D4: starting datafile backup set restore
channel D4: using network backup set from service PRIM_N2
channel D4: specifying datafile(s) to restore from backup set
channel D4: restoring datafile 00010 to +DATA
channel D3: restore complete, elapsed time: 00:06:14
channel D4: restore complete, elapsed time: 00:06:16
channel D2: restore complete, elapsed time: 00:31:15
channel D1: restore complete, elapsed time: 09:37:35
Finished restore at 03-DEC-2017 02:20:34
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service ‘PRIM_N2’
archivelog from scn 10556541166137;
switch clone datafile all;
}
executing Memory Script
Starting restore at 03-DEC-2017 02:21:24
channel D1: starting archived log restore to default destination
channel D1: using network backup set from service PRIM_N2
channel D1: restoring archived log
archived log thread=1 sequence=4931
channel D2: starting archived log restore to default destination
channel D2: using network backup set from service PRIM_N2
channel D2: restoring archived log
archived log thread=1 sequence=4932
channel D3: starting archived log restore to default destination
channel D3: using network backup set from service PRIM_N2
channel D3: restoring archived log
archived log thread=1 sequence=4933
channel D4: starting archived log restore to default destination
channel D4: using network backup set from service PRIM_N2
channel D4: restoring archived log
archived log thread=1 sequence=4934
channel D1: restore complete, elapsed time: 00:00:03
channel D1: starting archived log restore to default destination
channel D1: using network backup set from service PRIM_N2
channel D1: restoring archived log
archived log thread=1 sequence=4935
channel D1: restore complete, elapsed time: 00:00:45
channel D1: starting archived log restore to default destination
channel D1: using network backup set from service PRIM_N2
channel D1: restoring archived log
archived log thread=1 sequence=4936
channel D1: restore complete, elapsed time: 00:00:02
channel D1: starting archived log restore to default destination
channel D1: using network backup set from service PRIM_N2
channel D1: restoring archived log
archived log thread=2 sequence=2254
channel D4: restore complete, elapsed time: 00:01:13
channel D4: starting archived log restore to default destination
channel D4: using network backup set from service PRIM_N2
channel D4: restoring archived log
archived log thread=2 sequence=2255
channel D2: restore complete, elapsed time: 00:01:16
channel D2: starting archived log restore to default destination
channel D2: using network backup set from service PRIM_N2
channel D2: restoring archived log
archived log thread=2 sequence=2256
channel D2: restore complete, elapsed time: 00:00:26
channel D2: starting archived log restore to default destination
channel D2: using network backup set from service PRIM_N2
channel D2: restoring archived log
archived log thread=2 sequence=2257
channel D2: restore complete, elapsed time: 00:00:01
channel D1: restore complete, elapsed time: 00:01:18
channel D3: restore complete, elapsed time: 00:02:27
channel D4: restore complete, elapsed time: 00:01:13
Finished restore at 03-DEC-2017 02:23:52
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=961727032 file name=+DATA/STBY/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=961727032 file name=+DATA/STBY/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=961727032 file name=+DATA/STBY/undotbs101.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=961727032 file name=+DATA/STBY/undotbs201.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=961727033 file name=+DATA/STBY/users01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=961727033 file name=+DATA/STBY/DATAFILE/kyc_data.258.961692181
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=961727033 file name=+DATA/STBY/DATAFILE/kyc_index.265.961692247
datafile 8 switched to datafile copy
input datafile copy RECID=13 STAMP=961727033 file name=+DATA/STBY/DATAFILE/kyc2.257.961692481
datafile 9 switched to datafile copy
input datafile copy RECID=14 STAMP=961727033 file name=+DATA/STBY/DATAFILE/undotbs2.263.961692497
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=961727033 file name=+DATA/STBY/DATAFILE/undotbs1.261.961692505
contents of Memory Script:
{
set until scn 10556925833926;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 03-DEC-2017 02:23:53
starting media recovery
archived log for thread 1 with sequence 4932 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4932.336.961726887
archived log for thread 1 with sequence 4933 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4933.335.961726887
archived log for thread 1 with sequence 4934 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4934.333.961726887
archived log for thread 1 with sequence 4935 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4935.256.961726887
archived log for thread 1 with sequence 4936 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4936.334.961726933
archived log for thread 2 with sequence 2254 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2254.332.961726935
archived log for thread 2 with sequence 2255 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2255.331.961726961
archived log for thread 2 with sequence 2256 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2256.330.961726963
archived log for thread 2 with sequence 2257 is already on disk as file +FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2257.329.961726987
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4932.336.961726887 thread=1 sequence=4932
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2254.332.961726935 thread=2 sequence=2254
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4933.335.961726887 thread=1 sequence=4933
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2255.331.961726961 thread=2 sequence=2255
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4934.333.961726887 thread=1 sequence=4934
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2256.330.961726963 thread=2 sequence=2256
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4935.256.961726887 thread=1 sequence=4935
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_1_seq_4936.334.961726933 thread=1 sequence=4936
archived log file name=+FRA/STBY/ARCHIVELOG/2017_12_03/thread_2_seq_2257.329.961726987 thread=2 sequence=2257
media recovery complete, elapsed time: 00:04:08
Finished recover at 03-DEC-2017 02:28:04
Finished Duplicate Db at 03-DEC-2017 02:30:20
released channel: C1
released channel: C2
released channel: C3
released channel: C4
released channel: D1
released channel: D2
released channel: D3
released channel: D4
Recovery Manager complete.
POST DUPLICATION OPERATIONS
Status of the standby database after duplication
Connect to the DR database and check that the database is created as PHYSICAL STANDBY. The redos of threads or nodes 1 and 2 have been created on the DR
SQL> select name,database_role,open_mode from v$database;
SQL> select thread#,l.group#,l.status,substr(lf.member,1,40) lmember ,l.bytes/1024/1024 size_Mb
2 from v$log l,v$logfile lf where l.group#=lf.group# order by thread#,2,4;
Below is the list of redo and SLR files created by the duplication command :
SQL> select * from v$logfile order by 1,4;
Enabling Synchronization
Enable synchronization between the primary and secondary databases using the command below:
SQL> alter database recover managed standby database using current logfile disconnect;
or
SQL> alter database recover managed standby database disconnect;
Synchronization Verification
Verify that synchronization is working correctly using the following command:
SQL> select sequence#, first_time, next_time, applied, status from v$archived_log where status=’A’ order by sequence#;
Note: APPLIED=’YES’ means that the archive file has been successfully copied and applied to the DR. If the archive has not yet been applied or is in the process of being applied, APPLIED=’NO’.
NB: We note archives of 22xx and 49xx sequences, coming from the 2 nodes of the clustered databases on production.
From different production databases nodes, execute the command below and check with command above that archive files are received and applied on the DR:
SQL> alter system switch logfile;
To check for dataguard issue, you can run below commands :
SQL> select severity, error_code, message, to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’) from v$dataguard_status where dest_id=2;
SQL> select process, status, sequence#,block#,blocks,delay_mins from v$managed_standby
BLOG DBA TIPS AND TRICKS 