192.168.56.75 node1.localdomain node1 -- Primary
192.168.56.76 node2.localdomain node2 -- Physical Stnadby
192.168.56.73 node3.localdomain node3 -- cascade physical standby
On Primary :
-----------------
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL_node1,ORCL_node2)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_node2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_node2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=10;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=ORCL_node2;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
orapwd file=$ORACLE_HOME/dbs/orapwORCL password=sysadmin force=y
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl_stndby.ctl' ;
SQL> create pfile='/tmp/pfile.ora' from spfile ;
Amend the PFILE making the entries relevant for the standby database.
*.db_unique_name='ORCL_node2'
*.fal_server='ORCL_node1'
*.log_archive_dest_2='SERVICE=ORCL_node1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_node1'
On standby :
$ mkdir -p /data01/app/oracle/oradata/orcl/ --> Datafiles
$ mkdir -p /data01/app/oracle/arch --> Archive
$ mkdir -p /data01/app/oracle/admin/orcl/adump --> audit_file_dest
Copy the files from the primary to the standby server:
# Standby controlfile to all locations.
scp /tmp/orcl_stndby.ctl oracle@node2:/data01/app/oracle/oradata/orcl/control01.ctl
scp /tmp/orcl_stndby.ctl oracle@node2:/data01/app/oracle/oradata/orcl/control02.ctl
$ # Parameter file.
$ scp /tmp/pfile.ora oracle@node2:/tmp/pfile_orcl_node2.ora
$ # Remote login password file.
scp /data01/app/oracle/product/11.2.0.3/db_1/dbs/orapwORCL oracle@node2:/data01/app/oracle/product/11.2.0.3/db_1/dbs/orapwORCL
Start Listener on standby :
Create a static listener and start is on standby :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /data01/app/oracle/product/11.2.0.3/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /data01/app/oracle/product/11.2.0.3/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.localdomain)(PORT = 1521))
)
)
Create Standby Redo Logs on Primary Server :
ALTER DATABASE ADD STANDBY LOGFILE ('/data01/app/oracle/oradata/orcl/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/data01/app/oracle/oradata/orcl/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/data01/app/oracle/oradata/orcl/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/data01/app/oracle/oradata/orcl/standby_redo04.log') SIZE 50M;
On standby :
----------------
export ORACLE_SID=orcl
SQL> startup nomount pfile='/tmp/pfile_orcl_node2.ora' ;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 494931288 bytes
Database Buffers 339738624 bytes
Redo Buffers 2379776 bytes
[oracle@node2 ~]$ rman target sys/sysadmin@ORCL_node1 AUXILIARY sys/sysadmin@ORCL_node2
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jun 18 04:58:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1470927095)
connected to auxiliary database: ORCL (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
SPFILE
SET db_unique_name='ORCL_node2'
SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL_node1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_node1'
SET FAL_SERVER='ORCL_node1'
NOFILENAMECHECK;2> 3> 4> 5> 6>
Starting Duplicate Db at 18-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/data01/app/oracle/product/11.2.0.3/db_1/dbs/orapworcl' auxiliary format
'/data01/app/oracle/product/11.2.0.3/db_1/dbs/orapworcl' targetfile
'/data01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora' auxiliary format
'/data01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora' ;
sql clone "alter system set spfile= ''/data01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora''";
}
executing Memory Script
Starting backup at 18-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Finished backup at 18-JUN-17
sql statement: alter system set spfile= ''/data01/app/oracle/product/11.2.0.3/db_1/dbs/spfileorcl.ora''
contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''ORCL_node2'' comment=
'''' scope=spfile";
sql clone "alter system set LOG_ARCHIVE_DEST_2 =
''SERVICE=ORCL_node1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_node1'' comment=
'''' scope=spfile";
sql clone "alter system set FAL_SERVER =
''ORCL_node1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_unique_name = ''ORCL_node2'' comment= '''' scope=spfile
sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''SERVICE=ORCL_node1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_node1'' comment= '''' scope=spfile
sql statement: alter system set FAL_SERVER = ''ORCL_node1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 536874328 bytes
Database Buffers 297795584 bytes
Redo Buffers 2379776 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/data01/app/oracle/oradata/orcl/control01.ctl';
restore clone controlfile to '/data01/app/oracle/oradata/orcl/control02.ctl' from
'/data01/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/data01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_orcl.f tag=TAG20170618T050116 RECID=3 STAMP=946962085
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 18-JUN-17
Starting restore at 18-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 18-JUN-17
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{
set newname for tempfile 1 to
"/data01/app/oracle/oradata/orcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/data01/app/oracle/oradata/orcl/system01.dbf";
set newname for datafile 2 to
"/data01/app/oracle/oradata/orcl/sysaux01.dbf";
set newname for datafile 3 to
"/data01/app/oracle/oradata/orcl/undotbs01.dbf";
set newname for datafile 4 to
"/data01/app/oracle/oradata/orcl/users01.dbf";
set newname for datafile 5 to
"/data01/app/oracle/oradata/orcl/example01.dbf";
set newname for datafile 6 to
"/data01/app/oracle/oradata/orcl/ggtbs.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/data01/app/oracle/oradata/orcl/system01.dbf" datafile
2 auxiliary format
"/data01/app/oracle/oradata/orcl/sysaux01.dbf" datafile
3 auxiliary format
"/data01/app/oracle/oradata/orcl/undotbs01.dbf" datafile
4 auxiliary format
"/data01/app/oracle/oradata/orcl/users01.dbf" datafile
5 auxiliary format
"/data01/app/oracle/oradata/orcl/example01.dbf" datafile
6 auxiliary format
"/data01/app/oracle/oradata/orcl/ggtbs.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /data01/app/oracle/oradata/orcl/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
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/data01/app/oracle/oradata/orcl/system01.dbf
output file name=/data01/app/oracle/oradata/orcl/system01.dbf tag=TAG20170618T050153
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/data01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/data01/app/oracle/oradata/orcl/sysaux01.dbf tag=TAG20170618T050153
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/data01/app/oracle/oradata/orcl/example01.dbf
output file name=/data01/app/oracle/oradata/orcl/example01.dbf tag=TAG20170618T050153
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/data01/app/oracle/oradata/orcl/ggtbs.dbf
output file name=/data01/app/oracle/oradata/orcl/ggtbs.dbf tag=TAG20170618T050153
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/data01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/data01/app/oracle/oradata/orcl/undotbs01.dbf tag=TAG20170618T050153
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/data01/app/oracle/oradata/orcl/users01.dbf
output file name=/data01/app/oracle/oradata/orcl/users01.dbf tag=TAG20170618T050153
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 18-JUN-17
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/data01/app/oracle/arch/ORCL_NODE1/archivelog/2017_06_18/o1_mf_1_10_dndjq5b9_.arc" auxiliary format
"/data01/app/oracle/arch/ORCL_NODE2/archivelog/2017_06_18/o1_mf_1_10_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=10 RECID=6 STAMP=946962469
output file name=/data01/app/oracle/arch/ORCL_NODE2/archivelog/2017_06_18/o1_mf_1_10_08s73019_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 18-JUN-17
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /data01/app/oracle/arch/ORCL_NODE2/archivelog/2017_06_18/o1_mf_1_10_08s73019_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data01/app/oracle/arch/ORCL_NODE2/archivelog/2017_06_18/o1_mf_1_10_08s73019_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=946962481 file name=/data01/app/oracle/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=946962482 file name=/data01/app/oracle/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=946962482 file name=/data01/app/oracle/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=946962483 file name=/data01/app/oracle/oradata/orcl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=946962484 file name=/data01/app/oracle/oradata/orcl/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=946962485 file name=/data01/app/oracle/oradata/orcl/ggtbs.dbf
contents of Memory Script:
{
set until scn 1155236;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 18-JUN-17
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file /data01/app/oracle/arch/ORCL_NODE2/archivelog/2017_06_18/o1_mf_1_10_08s73019_.arc
archived log file name=/data01/app/oracle/arch/ORCL_NODE2/archivelog/2017_06_18/o1_mf_1_10_08s73019_.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:06
Finished recover at 18-JUN-17
Finished Duplicate Db at 18-JUN-17
SQL> SQL> select process,sequence# , status from v$managed_standby ;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 11 CLOSING
RFS 0 IDLE
RFS 12 IDLE
RFS 0 IDLE
MRP0 12 APPLYING_LOG
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Primary Site:
==============
DB Name : ORCL
DB Unique name : ORCL_node1
Hostname : node1
Cascading Site:
===============
DB Name : ORCL
DB Unique name : ORCL_node2
Hostname : node2
Cascaded Site:
================
DB Name : ORCL
DB Unique name : ORCL_node3
Hostname : node3
Make sure that the TNS entries for ORCL_node1/ORCL_node2/ORCL_node3 exist on each of the 3 nodes. Copy the tns files on all nodes
Add a static entry about the details of ORCL_node3 instance to the listener.ora file on the cascaded standby host “node3"and start the listener .
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /data01/app/oracle/product/11.2.0.3/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /data01/app/oracle/product/11.2.0.3/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3.localdomain)(PORT = 1521))
)
)
--> Copy the password file from “ORCL_node2” to the cascaded standby site “ORCL_node3” :
scp /data01/app/oracle/product/11.2.0.3/db_1/dbs/orapwORCL oracle@node3:/data01/app/oracle/product/11.2.0.3/db_1/dbs/orapwORCL
Create a copy of pfile ( node1) and remove all standby related parameters scp it to node3:
------------------------------------------------------------------------------------
pfile -- removed all the standby parameter
[oracle@node3 admin]$ cat /tmp/pfile_standby_cascade.ora
*.audit_file_dest='/data01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data01/app/oracle/oradata/orcl/control01.ctl','/data01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest_size=21474836480
*.db_recovery_file_dest='/data01/app/oracle/arch'
*.db_unique_name='ORCL_node3'
*.diagnostic_dest='/data01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
--> Create standby controlfile and mount ORCL_node3 in physical standby mode
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl_node3.ctl' ;
Database altered.
scp /tmp/orcl_node3.ctl oracle@node3:/data01/app/oracle/oradata/orcl/control01.ctl
scp /tmp/orcl_node3.ctl oracle@node3:/data01/app/oracle/oradata/orcl/control02.ctl
[oracle@node3 admin]$
SQL> export ORALCE_SID=orcl
SQL> startup mount pfile='/tmp/pfile_standby_cascade.ora' ;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 494931288 bytes
Database Buffers 339738624 bytes
Redo Buffers 2379776 bytes
Database mounted.
SQL> select database_role from v$database ;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
RMAN> restore database ; #### Restore from full backup of primary
Starting restore at 18-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /data01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /data01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /data01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /data01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data01/app/oracle/oradata/orcl/ggtbs.dbf
channel ORA_DISK_1: reading from backup piece /data01/app/oracle/rman/rman_bkp_new_0bs735tk_1_1
channel ORA_DISK_1: piece handle=/data01/app/oracle/rman/rman_bkp_new_0bs735tk_1_1 tag=TAG20170618T064820
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 18-JUN-17
Now that the cascaded standby database is restored ,set the parameters on the primary and the cascading standby databases accordingly.
================================================================================================================================
=======
Node1
=======
alter system set log_archive_config='DG_CONFIG=(ORCL_node1,ORCL_node2,ORCL_node3)' sid='*' ;
=======
Node2
=======
alter system set log_archive_config='DG_CONFIG=(ORCL_node1,ORCL_node2,ORCL_node3)' sid='*' ;
alter system set log_archive_dest_3='service=ORCL_node3 valid_for=(all_logfiles,all_roles) db_unique_name=ORCL_node3' sid='*' ;
========
Node3
========
alter system set log_archive_config='DG_CONFIG=(ORCL_node1,ORCL_node2,ORCL_node3)' sid='*' ;
alter system set standby_file_management=AUTO sid='*' ;
alter system set fal_server='ORCL_node2' sid='*' ;
alter system set fal_client='ORCL_node3' sid='*' ;
Now start the redo ship on cascadig standby :
===============================================
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
MRP0 APPLYING_LOG 19
RFS IDLE 0
RFS IDLE 0
