Step by Step Configuration of Oracle Cascading Standby Database

Description:

  • In this article we are going to see Step by Step Configuration of Oracle Cascading Standby Database 11.2.0.4 in RHEL-6.8.
  • To reduce the load on your primary system, you can implement cascaded destinations, where by a standby database receives its redo data from another standby database, instead of directly from the primary database  [ Primary->stby1->stby2 ]
  • The cascaded standby database does not receive its redo directly from the Primary database and this type of data guard configuration offloads the overhead associated with performing redo transport from a primary database to a cascading standby database.
  • A physical standby database to retransmit the incoming redo data it receives from the primary database to other remote destinations in the same manner as the primary database

Environment details of the cascaded standby database.

Let’s Start the Demo:-

Primary Server side Configurations:-

Step1:-Change Archivelog mode:
SQL> archive log list
SQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_prim' scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;

Make sure the archive directory is created in physically in OS level.

Step2:-Change force logging mode:
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;
Step3:-Adding Redologfile for standby database:

SQL> alter database add standby logfile group 4 '/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log' size 50m;
SQL> alter database add standby logfile group 5 '/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log' size 50m;
SQL> alter database add standby logfile group 6 '/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log' size 50m;
Check the newly added standby redo log files with the below qurey:
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step4:-Add the network entry in primary and standby side(All the servers):

Listener Entry(Primary):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle

Listener Entry(Standby1):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY1)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby1.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle

Listener Entry(Standby2):-
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY2)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby2.oracledbwr.com)(PORT = 1624))
)
)
ADR_BASE_INTENSITY = /oradb/app/oracle

Tnsnames entry(All three servers):-
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)

ORCL_STBY1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby1.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY1)
)
)

ORCL_STBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby2.oracledbwr.com)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY2)
)
)

Start the listener in all three servers and try to tnsping to each other. If the tnsping is successful, go ahead for the next step, else fix the tnsping issue and go to the next step.

$ tnsping ORCL_PRIM
$ tnsping ORCL_STBY1
$ tnsping ORCL_STBY2

Step5:-Changing parameters in primary database:
SQL> ALTER SYSTEM SET db_unique_name='ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2='service=ORCL_STBY1 async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_3='service=ORCL_STBY2 async valid_for=(standby_logfiles,standby_role) db_unique_name=ORCL_STBY2' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_state_3='DEFER' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server='ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client='ORCL_PRIM' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1' SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP

Step6:-Check the changed parameters:

SQL> @dg_params.sql

Create a pfile and password file and copy to the standby servers:
SQL> create pfile from spfile;
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=manager entries=5

Copy the remote login password file (orapwORCL) from $ORACLE_HOME/dbs of primary database server to standby1 & standby2 database server to the location $ORACLE_HOME/dbs:
$ scp $ORACLE_HOME/dbs/initORCL.ora oracle@standby1:$ORACLE_HOME/dbs/initORCL.ora
$ scp $ORACLE_HOME/dbs/orapwORCL oracle@standby1:$ORACLE_HOME/dbs/orapwORCL
$ scp $ORACLE_HOME/dbs/initORCL.ora oracle@standby2:$ORACLE_HOME/dbs/initORCL.ora
$ scp $ORACLE_HOME/dbs/orapwORCL oracle@standby2:$ORACLE_HOME/dbs/orapwORCL

Standby1 Server side Configurations:-

Step1:-Change the necessary parameters in the copied pfile as like below

$ cat $ORACLE_HOME/dbs/initORCL.ora
*.__oracle_base='/oradb/app/oracle'# ORACLE_BASE set from environment
*.audit_file_dest='/oradb/app/oracle/admin/ORCL_STBY1/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl','/oradb/app/oracle/flash_recovery_area/ORCL_STBY1/control02.ctl'
*.db_block_size=8192
*.db_domain='doyensys.com'
*.db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1'
*.db_name='ORCL'
*.db_recovery_file_dest='/oradb/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8G
*.db_unique_name='ORCL_STBY1'
*.diagnostic_dest='/oradb/app/oracle'
*.fal_client='ORCL_STBY1'
*.fal_server='ORCL_PRIM'
*.job_queue_processes=20
*.log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)'
*.log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby1 valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY1'
*.log_archive_dest_2='service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM'
*.log_archive_dest_3='service=ORCL_STBY2 async valid_for=(standby_logfiles,standby_role) db_unique_name=ORCL_STBY2'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='orcls1_%t_%s_%r.arc'
*.log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY1'
*.memory_max_target=692060160
*.memory_target=629145600
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step2:-Create directory Structure in Standby database

$ mkdir -p /oradb/app/oracle/admin/ORCL_STBY1/adump
$ mkdir -p /oradb/app/oracle/oradata/ORCL_STBY1
$ mkdir -p /oradb/app/oracle/flash_recovery_area/ORCL_STBY1
$ mkdir -p /oradb/app/oracle/archive/orcl_stby1

Step3:-Start the standby1 database using pfile

$ sqlplus ‘/ as sysdba’

SQL> startup nomount
SQL> exit

Step4:-Connect to the rman

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY1

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 1 22:53:03 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1508077812)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=37 device type=DISK
allocated channel: p2
channel p2: SID=49 device type=DISK
allocated channel: p3
channel p3: SID=50 device type=DISK
allocated channel: p4
channel p4: SID=51 device type=DISK
allocated channel: s1
channel s1: SID=19 device type=DISK
Starting Duplicate Db at 01-JUL-18 22:53:30
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' ;
}
executing Memory Script
Starting backup at 01-JUL-18 22:53:30
Finished backup at 01-JUL-18 22:53:31
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl';
restore clone controlfile to '/oradb/app/oracle/flash_recovery_area/ORCL_STBY1/control02.ctl' from
'/oradb/app/oracle/oradata/ORCL_STBY1/control01.ctl';
}
executing Memory Script

Starting backup at 01-JUL-18 22:53:32
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180701T225332 RECID=10 STAMP=980376813
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 01-JUL-18 22:53:35
Starting restore at 01-JUL-18 22:53:35
channel s1: copied control file copy
Finished restore at 01-JUL-18 22:53:36
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
"/oradb/app/oracle/oradata/ORCL_STBY1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf";
set newname for datafile 2 to
"/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf";
set newname for datafile 3 to
"/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf";
set newname for datafile 4 to
"/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf";
set newname for datafile 5 to
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF ";
set newname for datafile 6 to
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF";
set newname for datafile 7 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf";
set newname for datafile 8 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf";
set newname for datafile 9 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf";
set newname for datafile 10 to
"/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf" datafile
2 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf" datafile
3 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf" datafile
4 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf" datafile
5 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF " datafile
6 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF" datafile
7 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf" datafile
8 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf" datafile
9 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf" datafile
10 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradb/app/oracle/oradata/ORCL_STBY1/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 backup at 01-JUL-18 22:53:42
channel p1: starting datafile copy
input datafile file number=00008 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom02.dbf
channel p2: starting datafile copy
input datafile file number=00007 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom01.dbf
channel p3: starting datafile copy
input datafile file number=00009 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom03.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:17:00
channel p4: starting datafile copy
input datafile file number=00006 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:10:54
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:09:43
channel p4: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:08:01
channel p4: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:11:19
channel p4: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01_1.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf tag=TAG20180701T225342
channel p3: datafile copy complete, elapsed time: 01:01:07
channel p3: starting datafile copy
input datafile file number=00010 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom04.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf tag=TAG20180701T225342
channel p3: datafile copy complete, elapsed time: 00:00:17
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf tag=TAG20180701T225342
channel p4: datafile copy complete, elapsed time: 00:05:31
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf tag=TAG20180701T225342
channel p2: datafile copy complete, elapsed time: 01:23:48
output file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf tag=TAG20180701T225342
channel p1: datafile copy complete, elapsed time: 01:25:21
Finished backup at 02-JUL-18 00:19:04
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/undotbs01_1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/APEX01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=18 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom03.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=19 STAMP=980381949 file name=/oradb/app/oracle/oradata/ORCL_STBY1/custom04.dbf
Finished Duplicate Db at 02-JUL-18 00:19:26
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step5:-Connect to the standby1 database and start the recovery process

SQL> alter database recover managed standby database using current logfile disconnect;

Step6:-Physical Standby Database is Performing Correctly

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Standby2 Server side Configurations:-

Step1:-Change the necessary parameters in the copied pfile as like below

$ cat $ORACLE_HOME/dbs/initORCL.ora
*.__oracle_base='/oradb/app/oracle'# ORACLE_BASE set from environment
*.audit_file_dest='/oradb/app/oracle/admin/ORCL_STBY2/adump'
*.audit_trail='DB'
*.compatible='11.2.0.4.0'
*.control_files='/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl','/oradb/app/oracle/flash_recovery_area/ORCL_STBY2/control02.ctl'
*.db_block_size=8192
*.db_domain='doyensys.com'
*.db_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY2'
*.db_name='ORCL'
*.db_recovery_file_dest='/oradb/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=8G
*.db_unique_name='ORCL_STBY2'
*.diagnostic_dest='/oradb/app/oracle'
*.fal_client='ORCL_STBY2'
*.fal_server='ORCL_STBY1'
*.job_queue_processes=20
*.log_archive_config='dg_config=(ORCL_PRIM,ORCL_STBY1,ORCL_STBY2)'
*.log_archive_dest_1='LOCATION=/oradb/app/oracle/archive/orcl_stby2'
*.log_archive_format='orcls2_%t_%s_%r.arc'
*.log_file_name_convert='/oradb/app/oracle/oradata/ORCL_PRIM','/oradb/app/oracle/oradata/ORCL_STBY2'
*.memory_max_target=692060160
*.memory_target=629145600
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step2:-Create directory Structure in Standby database

$ mkdir -p /oradb/app/oracle/admin/ORCL_STBY2/adump
$ mkdir -p /oradb/app/oracle/oradata/ORCL_STBY2
$ mkdir -p /oradb/app/oracle/flash_recovery_area/ORCL_STBY2
$ mkdir -p /oradb/app/oracle/archive/orcl_stby2

Step3:-Start the standby2 database using pfile

$ sqlplus ‘/ as sysdba’

SQL> startup nomount
SQL> exit

Step4:-Connect to the rman

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY2

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 2 18:17:44 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1508077812)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=62 device type=DISK
allocated channel: p2
channel p2: SID=29 device type=DISK
allocated channel: p3
channel p3: SID=59 device type=DISK
allocated channel: p4
channel p4: SID=36 device type=DISK
allocated channel: s1
channel s1: SID=19 device type=DISK
Starting Duplicate Db at 02-JUL-18 18:17:53
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' ;
}
executing Memory Script
Starting backup at 02-JUL-18 18:17:53
Finished backup at 02-JUL-18 18:17:54
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl';
restore clone controlfile to '/oradb/app/oracle/flash_recovery_area/ORCL_STBY2/control02.ctl' from
'/oradb/app/oracle/oradata/ORCL_STBY2/control01.ctl';
}
executing Memory Script

Starting backup at 02-JUL-18 18:17:54
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180702T181754 RECID=11 STAMP=980446676
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 02-JUL-18 18:17:58
Starting restore at 02-JUL-18 18:17:58
channel s1: copied control file copy
Finished restore at 02-JUL-18 18:17:59
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
"/oradb/app/oracle/oradata/ORCL_STBY2/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf";
set newname for datafile 2 to
"/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf";
set newname for datafile 3 to
"/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf";
set newname for datafile 4 to
"/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf";
set newname for datafile 5 to
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF ";
set newname for datafile 6 to
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF";
set newname for datafile 7 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf";
set newname for datafile 8 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf";
set newname for datafile 9 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf";
set newname for datafile 10 to
"/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf" datafile
2 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf" datafile
3 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf" datafile
4 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf" datafile
5 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF " datafile
6 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF" datafile
7 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf" datafile
8 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf" datafile
9 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf" datafile
10 auxiliary format
"/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradb/app/oracle/oradata/ORCL_STBY2/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 backup at 02-JUL-18 18:18:05
channel p1: starting datafile copy
input datafile file number=00008 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom02.dbf
channel p2: starting datafile copy
input datafile file number=00007 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom01.dbf
channel p3: starting datafile copy
input datafile file number=00009 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom03.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:14:02
channel p4: starting datafile copy
input datafile file number=00006 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:09:30
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/APEX01.DBF
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:17:02
channel p4: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:08:15
channel p4: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:11:03
channel p4: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01_1.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf tag=TAG20180702T181805
channel p3: datafile copy complete, elapsed time: 01:06:02
channel p3: starting datafile copy
input datafile file number=00010 name=/oradb/app/oracle/oradata/ORCL_PRIM/custom04.dbf
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf tag=TAG20180702T181805
channel p3: datafile copy complete, elapsed time: 00:00:37
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf tag=TAG20180702T181805
channel p4: datafile copy complete, elapsed time: 00:07:03
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf tag=TAG20180702T181805
channel p2: datafile copy complete, elapsed time: 01:31:51
output file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf tag=TAG20180702T181805
channel p1: datafile copy complete, elapsed time: 01:33:54
Finished backup at 02-JUL-18 19:51:59
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/undotbs01_1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/APEX01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom02.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom03.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=20 STAMP=980452324 file name=/oradb/app/oracle/oradata/ORCL_STBY2/custom04.dbf
Finished Duplicate Db at 02-JUL-18 19:52:20
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step5:-Connect to the standby1 database and start the recovery process

SQL> alter database recover managed standby database using current logfile disconnect;

In Primary switch logfile and the archived log file in both standby servers by executing the below script:

SQL> @stby_log_applied.sql

Primary:

Standby1:

Standby2:

Successfully completed the cascading standby database configuration

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

Reference:-

https://docs.oracle.com/cd/B19306_01/server.102/b14239/cascade_appx.htm#g639625

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Step by Step Configuring Dataguard on Different Operating Systems (Heterogeneous)

Description:-

In this article we are going to configure Oracle Heterogeneous Standby with Primary in Linux & Standby in Windows and  see the below configuration details.Primary Server side Configurations:-

Step1:-Change Archivelog mode
SQL> archive log listSQL> shut immediate
SQL> startup mount
SQL> alter system set log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim’ scope=spfile;
SQL> alter database archivelog;
SQL> alter database open;

Step2:-Change force logging mode
SQL> alter database force logging;
SQL> select force_logging,log_mode from v$database;Step3:-Adding Redologfile for standby database
SQL> alter database add standby logfile group 4 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo04.log’ size 50m;
SQL> alter database add standby logfile group 5 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo05.log’ size 50m;
SQL> alter database add standby logfile group 6 ‘/oradb/app/oracle/oradata/ORCL_PRIM/redo06.log’ size 50m;

Now check the all the database files location:-

SQL> @all_db_files.sqlSQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step4:-Adding the network entry in primary and standby side(Both servers)
Listener Entry(Primary):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_PRIM)
(ORACLE_HOME = /oradb/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1624))
)
)

Listener Entry(Standby):-
*******************
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STBY)
(ORACLE_HOME = D:\app\oracle\product\11.2.0.4\db_1)
(SID_NAME = ORCL)
)
)

Tnsnames entry(Both):-
******************
ORCL_PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_PRIM)
)
)

ORCL_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL_STBY)
)
)

Start the listener in both primary & standby and tnsping to primary & standby:-
$ tnsping ORCL_PRIM

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:47:15
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_PRIM)))
OK (10 msec)

$ tnsping ORCL_STBY

TNS Ping Utility for Linux: Version 11.2.0.4.0 – Production on 24-JUN-2018 20:48:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1624))) (CONNECT_DATA = (SERVICE_NAME = ORCL_STBY)))
OK (10 msec)

Step5:-Changing parameters in primary database
SQL> ALTER SYSTEM SET db_unique_name=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/oradb/app/oracle/archive/orcl_prim valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=ORCL_STBY async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_server=’ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET fal_client=’ORCL_PRIM’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> ALTER SYSTEM SET log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM’,’D:\app\oracle\oradata\ORCL_STBY’ SCOPE=SPFILE;
SQL> SHUT IMMEDIATE
SQL> STARTUP

Verify All the required parameters are set properly with the below script:-SQL> @stby_parameters.sql

Standby Server side Configurations:-

Step1:-Password file creation
Set ORACLE_BASE,ORACLE_HOME & PATH:-
$ set ORACLE_SID=ORCL
$ set ORACLE_BASE=D:\app\oracle
$ set ORACLE_HOME=%ORACLE_BASE%\product\11.2.0.4\db_1
$ set PATH=%ORACLE_HOME%\bin;%PATH%

Copy the remote login password file (orapwORCL) from the primary database server in $ORACLE_HOME/dbs directory to the
standby database server in $ORACLE_HOME/database:-

$ pscp $ORACLE_HOME/dbs/orapwORCL administrator@192.168.1.106:D:\app\oracle\product\11.2.0.4\db_1\database

Step2:-Changing parameters in standby database:-

In the $ORACLE_HOME/database directory of the standby system, create an initialization parameter file named initORCL.ora with the below parameter.

ORCL.__oracle_base=’D:\app\oracle’#ORACLE_BASE set from environment
*.audit_file_dest=’D:\app\oracle\admin\ORCL_STBY\adump’
*.audit_trail=’db’
*.compatible=’11.2.0.4′
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.db_name=’ORCL’
*.db_recovery_file_dest=’D:\app\oracle\fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.db_unique_name=’ORCL_STBY’
*.diagnostic_dest=’D:\app\oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
*.fal_client=’ORCL_STBY’
*.fal_server=’ORCL_PRIM’
*.log_archive_config=’dg_config=(ORCL_PRIM,ORCL_STBY)’
*.log_archive_dest_1=’LOCATION=D:\app\oracle\archive\orcl_stby valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_STBY’
*.log_archive_dest_2=’service=ORCL_PRIM async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL_PRIM’
*.log_archive_max_processes=5
*.log_file_name_convert=’/oradb/app/oracle/oradata/ORCL_PRIM/’,’D:\app\oracle\oradata\ORCL_STBY\’
*.memory_target=1509949440
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

Step3:-Create directory Structure in Standby database

$ cd %ORACLE_BASE%
$ mkdir admin\ORCL_STBY
$ cd admin\ORCL_STBY
$ mkdir adump pfile dpdump
$ mkdir -p D:\app\oracle\oradata\ORCL_STBY
$ mkdir -p D:\app\oracle\fast_recovery_area\ORCL_STBY

Step4:-Setup the windows service for oracle and start the standby database using pfile

$ oradim -new -sid ORCL -SYSPWD manager -pfile D:\app\oracle\product\11.2.0.4\db_1\database\initORCL.ora

$ sqlplus ‘/ as sysdba’
SQL> create spfile from pfile=’D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora’;
SQL> startup nomount

Step5:-Connect to the rman(Make sure the tnsnames.ora file has the primary & standby database entry in %ORACLE_HOME%\network\admin location)

$ rman target sys/manager@ORCL_PRIM auxiliary sys/manager@ORCL_STBY
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 27 00:57:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1507437661)
connected to auxiliary database: ORCL (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=44 device type=DISK
allocated channel: p2
channel p2: SID=28 device type=DISK
allocated channel: p3
channel p3: SID=48 device type=DISK
allocated channel: p4
channel p4: SID=47 device type=DISK
allocated channel: s1
channel s1: SID=134 device type=DISK
Starting Duplicate Db at 27-JUN-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/oradb/app/oracle/product/11.2.0.4/db_1/dbs/orapwORCL' auxiliary format
'D:\app\oracle\product\11.2.0.4\db_1\DATABASE\PWDorcl.ORA' ;
}
executing Memory Script
Starting backup at 27-JUN-18
Finished backup at 27-JUN-18
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format 'D:\APP\ORACLE\ORADATA\ORCL_STBY\CONTROL01.CTL';
}
executing Memory Script
Starting backup at 27-JUN-18
channel p1: starting datafile copy
copying standby control file
output file name=/oradb/app/oracle/product/11.2.0.4/db_1/dbs/snapcf_ORCL.f tag=TAG20180627T063140 RECID=3 STAMP=979885900
channel p1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-18
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
"D:\APP\ORACLE\ORADATA\ORCL_STBY\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF";
set newname for datafile 5 to
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF" datafile
5 auxiliary format
"D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\APP\ORACLE\ORADATA\ORCL_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
Starting backup at 27-JUN-18
channel p1: starting datafile copy
input datafile file number=00003 name=/oradb/app/oracle/oradata/ORCL_PRIM/undotbs01.dbf
channel p2: starting datafile copy
input datafile file number=00001 name=/oradb/app/oracle/oradata/ORCL_PRIM/system01.dbf
channel p3: starting datafile copy
input datafile file number=00002 name=/oradb/app/oracle/oradata/ORCL_PRIM/sysaux01.dbf
channel p4: starting datafile copy
input datafile file number=00004 name=/oradb/app/oracle/oradata/ORCL_PRIM/users01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:02:22
channel p4: starting datafile copy
input datafile file number=00005 name=/oradb/app/oracle/oradata/ORCL_PRIM/example01.dbf
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF tag=TAG20180627T063150
channel p3: datafile copy complete, elapsed time: 00:03:03
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF tag=TAG20180627T063150
channel p4: datafile copy complete, elapsed time: 00:01:32
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF tag=TAG20180627T063150
channel p2: datafile copy complete, elapsed time: 00:04:27
output file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF tag=TAG20180627T063150
channel p1: datafile copy complete, elapsed time: 00:04:48
Finished backup at 27-JUN-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=979866122 file name=D:\APP\ORACLE\ORADATA\ORCL_STBY\EXAMPLE01.DBF
Finished Duplicate Db at 27-JUN-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN> exit
Recovery Manager complete.

Step6:-Copy the control file to FRA

SQL> shut immediate
$ copy D:\app\oracle\oradata\ORCL_STBY\CONTROL01.CTL D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL
SQL> create pfile from spfile;

Change the control_files parameter value using any text editor in the pfile:

$ notepad D:\app\oracle\product\11.2.0.4\db_1\database\INITorcl.ora

From:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’

To:-
*.control_files=’D:\app\oracle\oradata\ORCL_STBY\control01.ctl’,’D:\app\oracle\fast_recovery_area\ORCL_STBY\CONTROL02.CTL’

Step7:-Startup the standby database and start the media recovery process

SQL> create spfile from pfile;
SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;

Now the Primary & Standby database status and roles:-

Primary:Standby:Now, check the current sequence of archive log in both primary & standby.

Primary:

SQL> archive log listStandby:

SQL> archive log listFrom the above two snaps from primary & standby has the same archive log sequence applied. Now, switch a redo log in primary and check the alert log in standby.

Primary:

SQL> alter system switch logfile;Standby:

SQL> @taln.sql 20Using the above script, we can see the alert log from sqlplus and from the above snap, the sequence 125 has been shipped from primary to standby.

 

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