Lets see how to build a
cascaded standby database .
Our Existing Primary
and Physical standby database.
Primary database.
=================
SQL> select
name,db_unique_name,open_mode,database_role from gv$database;
NAME
DB_UNIQUE_NAME
OPEN_MODE DATABASE_ROLE
-------------- ----------------------------
-------------------- ------------------------
DELL12PR dell12pr
READ WRITE
PRIMARY
DELL12PR dell12pr
READ WRITE
PRIMARY
First Standby database.
=========================
DATABASE_HOST
DB_NAME
DATABASE_ROLE OPEN_MODE
STARTUP_TIME
------------------------------ ---------------- ----------------
-------------------- --------------------
vmachine-dr2 dell12dr2 PHYSICAL STANDBY READ
ONLY WITH APPLY 01-MAY-21
vmachine-dr1 dell12dr1 PHYSICAL STANDBY READ
ONLY WITH APPLY 01-MAY-21
We will now need to
build a cascaded standby database which should receives the logfiles from the
first standby database.
Second Standby database
(Expected).
=================================
SQL> select
name,db_unique_name,open_mode,database_role from gv$database;
NAME
DB_UNIQUE_NAME
OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
DELL12PR xeon12dr
READ ONLY PHYSICAL
STANDBY
DELL12PR xeon12dr
READ ONLY
PHYSICAL STANDBY
Dataguard parameters In
Primary database.
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
dg_broker_config_file1
string
+DATAC1/dell12pr/dr1dell12pr.dat
dg_broker_config_file2 string
+DATAC1/dell12pr/dr2dell12pr.dat
log_archive_config
string dg_config=(dell12pr,dell12dr)
SQL> alter system set
log_archive_config='dg_config=(dell12pr,dell12dr,acer12dr)' scope=both sid='*';
System altered.
SQL> show parameter
config
NAME
TYPE VALUE
------------------------------------
----------- ------------------------------
dg_broker_config_file1
string
+DATAC1/dell12pr/dr1dell12pr.dat
dg_broker_config_file2
string +DATAC1/dell12pr/dr2dell12pr.dat
log_archive_config
string dg_config=(dell12pr,dell12dr,acer12dr)
SQL> ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_3='DEFER' SCOPE=BOTH sid='*';
System altered.
Add the TNS entries for the second standby database to the both the
nodes of the server.
ACER12DR_standby =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = vmachine-
dr1.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =
vmachine-dr2.localdomain)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = acer12dr)
)
)
ACER12DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
vmachine-dr1.localdomain)(PORT
1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =
vmachine-dr2.localdomain)(PORT
1521)) )
(CONNECT_DATA =
(SERVICE_NAME = acer12dr)
)
)
Init file for the
cascaded standby database.
[oracle@vmachine-dr1
dbs]$ cat initacer12dr1.ora
*.audit_file_dest='/u01/app/oracle/admin/acer12dr/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_domain=''
*.db_name='dell12pr'
*.db_unique_name='acer12dr'
*.db_recovery_file_dest='+DATAC1'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=acer12drXDB)'
acer12dr2.instance_number=2
acer12dr1.instance_number=1
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1140m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=3420m
acer12dr2.thread=2
acer12dr1.thread=1
acer12dr1.undo_tablespace='UNDOTBS1'
acer12dr2.undo_tablespace='UNDOTBS2'
=============================================================
Create the adump
directory in both the nodes.
mkdir
-p /u01/app/oracle/admin/acer12dr/adump
=============================================================
Create the password file
for the second standby database or copy the first standbt database password
file as below
[oracle@vmachine-dr1 dbs]$ cp orapwdell12dr1
orapwacer12dr1
[oracle@vmachine-dr1 dbs]$ pwd
/u01/app/oracle/product/12c/db_1/dbs
===========================================================================================================
Build the Second standby
database using rman duplicate
[oracle@vmachine-dr1 ~]$ rman target sys@dell12dr_standby
auxiliary sys@acer12dr_standby
Recovery Manager: Release 12.1.0.2.0 - Production on Sat
May 1 15:48:44 2021
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
target database Password:
connected to target database: DELL12PR (DBID=561887285)
auxiliary database Password:
connected to auxiliary database (not started)
RMAN> startup clone nomount
Oracle instance started
Total System Global Area 3590324224 bytes
Fixed Size
2930608 bytes
Variable Size
889194576 bytes
Database Buffers
2684354560 bytes
Redo Buffers
13844480 bytes
RMAN> duplicate target database for standby from
active database;
Starting Duplicate Db at 01-MAY-21
using target database control file instead of recovery
catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 instance=acer12dr1 device
type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format
'/u01/app/oracle/product/12c/db_1/dbs/orapwacer12dr1' ;
}
executing Memory Script
Starting backup at 01-MAY-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 instance=dell12dr1 device
type=DISK
Finished backup at 01-MAY-21
contents of Memory Script:
{
sql clone "create spfile from
memory";
shutdown clone immediate;
startup clone nomount;
restore clone from service
'dell12dr_standby' standby controlfile;
}
executing Memory Script
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 3590324224 bytes
Fixed Size
2930608 bytes
Variable Size
905971792 bytes
Database Buffers
2667577344 bytes
Redo Buffers
13844480 bytes
Starting restore at 01-MAY-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 instance=acer12dr1 device
type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: using network backup set from
service dell12dr_standby
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:07
output file
name=+DATAC1/ACER12DR/CONTROLFILE/current.1897.1071417077
output file
name=+DATAC1/ACER12DR/CONTROLFILE/current.1895.1071417077
Finished restore at 01-MAY-21
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 clone tempfile 1 to
new;
switch clone tempfile all;
set newname for clone datafile 1 to
new;
set newname for clone datafile 3 to
new;
set newname for clone datafile 4 to
new;
set newname for clone datafile 5 to
new;
set newname for clone datafile 6 to
new;
restore
from service 'dell12dr_standby'
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATAC1 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 01-MAY-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: using network backup set from
service dell12dr_standby
channel ORA_AUX_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to
+DATAC1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: using network backup set from
service dell12dr_standby
channel ORA_AUX_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to
+DATAC1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: using network backup set from
service dell12dr_standby
channel ORA_AUX_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to
+DATAC1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: using network backup set from
service dell12dr_standby
channel ORA_AUX_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to
+DATAC1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set
restore
channel ORA_AUX_DISK_1: using network backup set from
service dell12dr_standby
channel ORA_AUX_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to
+DATAC1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:01
Finished restore at 01-MAY-21
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=16 STAMP=1071417095 file
name=+DATAC1/ACER12DR/DATAFILE/system.1894.1071417085
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=1071417095 file
name=+DATAC1/ACER12DR/DATAFILE/sysaux.1893.1071417089
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=1071417095 file
name=+DATAC1/ACER12DR/DATAFILE/undotbs1.1892.1071417091
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=1071417095 file
name=+DATAC1/ACER12DR/DATAFILE/undotbs2.1891.1071417093
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=1071417095 file
name=+DATAC1/ACER12DR/DATAFILE/users.1890.1071417095
Finished Duplicate Db at 01-MAY-21
rman>
=====================================================================================
Set dataguard related parameters in the first standby database.
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE
FROM V$DATABASE;
NAME OPEN_MODE
DB_UNIQUE_NAME
DATABASE_ROLE
--------- --------------------
------------------------------ ----------------
DELL12PR READ ONLY WITH APPLY dell12dr
PHYSICAL STANDBY
SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
log_archive_config
string
dg_config=(dell12pr)
SQL> ALTER SYSTEM SET
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dell12pr,dell12dr,acer12dr)' SCOPE=BOTH sid='*';
System altered.
SQL> SHOW PARAMETER LOG_ARCHIVE_CONFIG
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
log_archive_config
string
DG_CONFIG=(dell12pr,dell12dr,acer12dr)
Set the Log archive dest
SQL> alter system set
log_archive_dest_3='service=acer12dr VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
LGWR SYNC AFFIRM db_unique_name=acer12dr' scope=both sid='*';
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_3
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_3
string service=acer12dr,
VALID_FOR=(S
TANDBY_LOGFILES,STANDBY_ROLE)
LGWR SYNC AFFIRM db_unique_nam
e=acer12dr
SQL> ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_3='ENABLE' SCOPE=BOTH sid='*';
System altered.
=============================================================
For the cascaded standby
database, create pfile from the default spfile and remove the hidden parameters
and include the RAC parameters.
Create pfile from deffault spfile
SQL> create
pfile='/u01/app/oracle/product/12c/db_1/dbs/initacer12dr1.ora_new' from
spfile='/u01/app/oracle/product/12c/db_1/dbs/spfileacer12dr1.ora';
File created.
Modify the pfile as below.
[oracle@vmachine-dr1 dbs]$ cat initacer12dr1.ora_new
*.audit_file_dest='/u01/app/oracle/admin/acer12dr/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.compatible='12.1.0.2.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'#
connection_brokers default value
*.control_files='+DATAC1/ACER12DR/CONTROLFILE/current.1897.1071417077','+DATAC1/ACER12DR/CONTROLFILE/current.1895.1071417077'#Restore
Controlfile
*.core_dump_dest='/u01/app/oracle/diag/rdbms/acer12dr/acer12dr1/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_domain=''
*.db_name='dell12pr'
*.db_recovery_file_dest='+DATAC1'
*.db_recovery_file_dest_size=4785M
*.db_unique_name='acer12dr'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=acer12drXDB)'
*.log_archive_config='DG_CONFIG=(dell12pr,dell12dr,acer12dr)'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=13012K# log buffer update
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=1140M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at
init.ora
*.processes=300
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=17536K
*.sga_target=3424M
*.skip_unusable_indexes=TRUE
acer12dr2.thread=2
acer12dr1.thread=1
acer12dr1.undo_tablespace='UNDOTBS1'
acer12dr2.undo_tablespace='UNDOTBS2'
acer12dr2.instance_number=2
acer12dr1.instance_number=1
Create spfile in standard location from this pfile as below
SQL> create
spfile='+datac1/ACER12DR/parameterfile/spfileacer12dr1.ora' from
pfile='/u01/app/oracle/product/12c/db_1/dbs/initacer12dr1.ora_new';
File created.
[oracle@vmachine-dr1 dbs]$ srvctl start database -d
acer12dr
[oracle@vmachine-dr1 dbs]$ srvctl status database -d
acer12dr
Instance acer12dr1 is running on node vmachine-dr1
Instance acer12dr2 is running on node vmachine-dr2
On primary database
SQL> create
table CASCADE_TAB1 (name varchar2(100));
Table created.
SQL> insert into
CASCADE_TAB1 values ('THIS TABLE IS FROM PRIMARY DATABASE');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system
switch all logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
============================================================
In the First standby database.
DATABASE_HOST
DB_NAME
DATABASE_ROLE OPEN_MODE
STARTUP_TIME
------------------------------ ----------------
---------------- -------------------- --------------------
vmachine-dr1.localdomain dell12dr1
PHYSICAL STANDBY READ ONLY WITH APPLY 01-MAY-21
vmachine-dr2.localdomain dell12dr2
PHYSICAL STANDBY READ ONLY WITH APPLY 01-MAY-21
SQL> SQL>
INST_ID PROCESS STATUS
THREAD# SEQUENCE#
BLOCK# BLOCKS
---------- --------- ------------ ---------- ----------
---------- ----------
1 MRP0
APPLYING_LOG 2
22 14 102400
SQL> SQL> 2 3
THREAD# ARCHIVED
APPLIED GAP
---------- ---------- ---------- ----------
1
32 32
0
2
21 20
1
SQL> select * from CASCADE_TAB1;
NAME
----------------------------------------------------------------------------------------------------
THIS TABLE IS FROM PRIMARY DATABASE
=============================================================
In the Cascaded Standby database database
[oracle@vmachine-dr1 ~]$ srvctl modify database -d
acer12dr -startoption 'READ ONLY'
[oracle@vmachine-dr1 ~]$ srvctl stop database -d acer12dr
srvctl start database -d acer12dr
[oracle@vmachine-dr1 ~]$ srvctl start database -d
acer12dr
[oracle@vmachine-dr1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 4
15:35:09 2021
Copyright (c) 1982, 2014, Oracle. All rights
reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production
With the Partitioning, Real Application Clusters,
Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> set lines 200
col DATABASE_HOST for a30;
col HOST_NAME for a15;
col DATABASE_ROLE for a10
col OPEN_MODE for a10
col STARTUP_TIME for a20
SELECT i.HOST_NAME "DATABASE_HOST"
,i.INSTANCE_NAME "DB_NAME", d.DATABASE_ROLE "
DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME
from GV$DATABASE d, gv$instance i where
i.INST_ID=d.INST_ID;
select inst_id,process, status, thread#, sequence#,
block#, blocks from gv$managed_standby where process='MRP0';
select a.thread#, (select max (sequence#) from
v$archived_log where archived='YES' and thread#=a.thread#)
archived,max(a.sequence#) applied,
(select max(sequence#) from v$archived_log where
archived='YES' and thread#=a.thread#)-max(a.sequence#)gap
from v$archived_log a where a.applied='YES' group by
a.thread# order by thread#;
SQL> SQL> SQL> SQL> SQL> SQL>
2
DATABASE_HOST
DB_NAME
DATABASE_ROLE OPEN_MODE
STARTUP_TIME
------------------------------ ----------------
---------------- -------------------- --------------------
vmachine-dr1.localdomain acer12dr1
PHYSICAL STANDBY READ ONLY
04-MAY-21
vmachine-dr2.localdomain acer12dr2
PHYSICAL STANDBY READ ONLY
04-MAY-21
SQL> SQL>
no rows selected
SQL> SQL> 2 3
THREAD# ARCHIVED
APPLIED GAP
---------- ---------- ---------- ----------
1
53 52
1
2
37 37
0
SQL> select * from CASCADE_TAB1;
NAME
----------------------------------------------------------------------------------------------------
THIS TABLE IS FROM PRIMARY DATABASE
