![Cover image for Create Standby using RMAN Duplicate [12.2 Version]](https://media.dev.to/cdn-cgi/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Faveh90nnw56ojfvyx9zl.png)
While I was starting another project, I realised I don't have any post covering Standby Database creation.
Everyone has their own way of doing things, but last time I needed to create one, this was how I ended up doing it.
The Primary Database will be called TEST and run in RAC1 cluster (rac1-node1 / rac1-node2)
The Standby Database will be called TESTDG and run in RAC2 cluster (rac2-node1 / rac2-node2)
The Database will have one PDB
I'm using 12.2 version. The general process is very similar to all versions, but there are few things you may need to adjust, specially if you need to use an older version (like having your standby password file in +ASM)
Let's create the primary Database called TEST using DBCA silent option
[oracle@rac1-node1 ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/dbca \
-silent \
-createDatabase \
-templateName General_Purpose.dbc \
-gdbName TEST \
-sid TEST \
-createAsContainerDatabase true \
-numberOfPdbs 1 \
-pdbName TEST_PDB \
-pdbAdminPassword Welcome1 \
-SysPassword Welcome1 \
-SystemPassword Welcome1 \
-emConfiguration NONE \
-storageType ASM \
-redoLogFileSize 250 \
-diskGroupName DATA \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-databaseType MULTIPURPOSE \
-sampleschema true \
-ignorePreReqs \
-nodelist rac1-node1,rac1-node2
We need to change the primary to Archive mode and is always recommended to set "FORCE LOGGING" to avoid nologging corruption
We need to start the Database as mounted in order to change it to Archive Mode, so lets do all the steps in one go:
[oracle@rac1-node1 ~]$ srvctl stop database -d TEST ; srvctl start database -d TEST -o mount
[oracle@rac1-node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 22 10:26:00 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH sid='*';
System altered.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest = '+RECO' scope=BOTH sid='*';
System altered.
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3
SQL> select name, force_logging from v$database;
NAME FORCE_LOGGING
--------- ---------------------------------------
TEST YES
SQL> show parameter reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 20G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
We are also going to add Standby Redo logs.
For Standby Redo Logs, remember to always add 1 group extra than your Redo logs and also, don't Mulitplex them, that way you will make sure Standby performance is as its best.
Since we have 2 Redo Logs groups, we will create 3 Standby Redo Groups for each Thread
If you are really going to use this DB, please always have 3 or more Redo logs groups
SQL> -- REDO Status
col member format a60
col STATUS format a10
set linesize 150
set pagesize 99
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
GROUP# THREAD# MEMBER ARC STATUS FSIZE
---------- ---------- ------------------------------------------------------------ --- ---------- ----------
1 1 +DATA/TEST/ONLINELOG/group_1.354.1048449065 NO CURRENT 250
2 1 +DATA/TEST/ONLINELOG/group_2.339.1048449065 YES INACTIVE 250
3 2 +DATA/TEST/ONLINELOG/group_3.338.1048449561 YES INACTIVE 250
4 2 +DATA/TEST/ONLINELOG/group_4.362.1048449569 NO CURRENT 250
SQL>
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+RECO' SIZE 250M ;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+RECO' SIZE 250M ;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+RECO' SIZE 250M ;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+RECO' SIZE 250M ;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+RECO' SIZE 250M ;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '+RECO' SIZE 250M ;
Database altered.
SQL>
-- Standby REDO Status
col member format a60
col STATUS format a10
set linesize 150
set pagesize 99
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$standby_log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
GROUP# THREAD# MEMBER ARC STATUS FSIZE
---------- ---------- ------------------------------------------------------------ --- ---------- ----------
5 1 +RECO/TEST/ONLINELOG/group_5.1479.1049107011 YES UNASSIGNED 250
6 1 +RECO/TEST/ONLINELOG/group_6.1504.1049107021 YES UNASSIGNED 250
7 1 +RECO/TEST/ONLINELOG/group_7.815.1049107051 YES UNASSIGNED 250
8 2 +RECO/TEST/ONLINELOG/group_8.910.1049107081 YES UNASSIGNED 250
9 2 +RECO/TEST/ONLINELOG/group_9.1494.1049107091 YES UNASSIGNED 250
10 2 +RECO/TEST/ONLINELOG/group_10.1510.1049107101 YES UNASSIGNED 250
6 rows selected.
SQL>
And Restart the Database
[oracle@rac1-node1 ~]$ srvctl stop database -d TEST ; srvctl start database -d TEST
[oracle@rac1-node1 ~]$
Make sure you have the Stanbdy added (TESTDG in our case) to your tnsnames.ora file in The Primary DB_home:
[oracle@rac1-node1 ~]$ nano /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
TESTDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDG)
)
)
[....]
[oracle@rac1-node1 ~]$ tnsping testdg
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 22-AUG-2020 11:07:24
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDG)))
OK (0 msec)
[oracle@rac1-node1 ~]$
[oracle@rac1-node1 ~]$ scp -pr /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora rac1-node2:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
tnsnames.ora 100% 1225 925.6KB/s 00:00
[oracle@rac1-node1 ~]$
We will need the Primary and Standby Passwordfiles to be the same, so lets extract the Primary password and copy it to both nodes in the Standby side:
Since 12c, the Database password file are created in ASM diskgroup by default
Remember to add the Instance name to the password file name, TESTDG1/TESTDG2 in our case and copy it to both nodes
[oracle@rac1-node1 ~]$ asmcmd pwcopy `asmcmd pwget --dbuniquename TEST` /tmp/orapwTEST
copying +DATA/TEST/PASSWORD/pwdtest.281.1048448813 -> /tmp/orapwTEST
[oracle@rac1-node1 ~]$
[oracle@rac1-node1 ~]$ scp -pr /tmp/orapwTEST rac2-node1:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwTESTDG1
orapwTEST 100% 3584 2.9MB/s 00:00
[oracle@rac1-node1 ~]$ scp -pr /tmp/orapwTEST rac2-node2:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwTESTDG2
orapwTEST 100% 3584 2.7MB/s 00:00
[oracle@rac1-node1 ~]$
Something that can avoid some issues during the duplication is to create a pfile In the primary side directing to the spfile location:
[oracle@rac1-node1 ~]$ sqlplus / as sysdba
SQL> show parameter spfile
NAME TYPE VALUE
------ --------- ----------------------------------------------
spfile string +DATA/TEST/PARAMETERFILE/spfile.297.1048449579
[oracle@rac1-node1 ~]$ echo "SPFILE='+DATA/TEST/PARAMETERFILE/spfile.297.1048449579'" > /u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTEST1.ora
[oracle@rac1-node1 ~]$ scp -pr /u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTEST1.ora rac1-node2:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTEST2.ora
initTEST1.ora 100% 56 52.0KB/s 00:00
[oracle@rac1-node1 ~]$
Moving now to the Standby side (RAC2), set first the Database environment
[oracle@rac2-node1 ~]$ . oraenv
ORACLE_SID = [oracle] ? TESTDG1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
The Oracle base has been set to /u01/app/oracle
[oracle@rac2-node1 ~]$
We will need to create the AuditFile and CoreDump Destination folders to about issues during RMAN Duplication process:
[oracle@rac2-node1 ~]$ mkdir -p /u01/app/oracle/admin/TESTDG/cdump
[oracle@rac2-node1 ~]$ mkdir -p /u01/app/oracle/admin/TESTDG/adump
[oracle@rac2-node1 ~]$
Make sure you have tnsnames.ora file with the Primary, in my case, I just added both sides in the Primary side and copy the files to both standby nodes:
[oracle@rac1-node1 ~]$ scp -pr /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora rac2-node1:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
tnsnames.ora 100% 1225 1.2MB/s 00:00
[oracle@rac1-node1 ~]$ scp -pr /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora rac2-node2:/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
tnsnames.ora 100% 1225 41.3KB/s 00:00
[oracle@rac1-node1 ~]$
[oracle@rac2-node1 ~]$ tnsping test
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 22-AUG-2020 10:59:41
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (0 msec)
[oracle@rac2-node1 ~]$
Add a Add a new static entry in node1 listener of the standby cluster (RAC2)
and reload the listener to confirm the service is added
[oracle@rac2-node1 ~]$ nano /u01/app/19.3.0/grid/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME= TESTDG)
(ORACLE_HOME= /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME= TESTDG1)
)
(SID_DESC=
(GLOBAL_DBNAME= st121_DGMGRL)
(ORACLE_HOME= /u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME= st1211)
)
[....]
)
[oracle@rac2-node1 ~]$ . oraenv
ORACLE_SID = [TESTDG1] ? +ASM1
[oracle@rac2-node1 ~]$ lsnrctl reload listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2020 11:22:57
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[oracle@rac2-node1 ~]$ lsnrctl reload listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2020 11:22:57
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[oracle@rac2-node1 ~]$ lsnrctl status listener
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2020 11:22:59
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 22-AUG-2020 10:25:53
Uptime 0 days 0 hr. 57 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac2-node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary...
Service "TESTDG" has 1 instance(s).
Instance "TESTDG1", status UNKNOWN, has 1 handler(s) for this service...
Service "cdbst121_DGMGRL" has 1 instance(s).
Instance "cdbst1211", status UNKNOWN, has 1 handler(s) for this service...
[......]
Create a pfile just with the Primary DB_NAME and start the system as NOMOUNT
The pfile will be called with the locate instance name, TESTDG1 in our case
[oracle@rac2-node1 ~]$ echo 'DB_NAME=TEST' > $ORACLE_HOME/dbs/initTESTDG1.ora
[oracle@rac2-node1 ~]$
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 22 10:57:46 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTESTDG1.ora'
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 8621520 bytes
Variable Size 377487920 bytes
Database Buffers 50331648 bytes
Redo Buffers 8155136 bytes
SQL>
Let's now start with RMAN Duplication.
First start RMAN in Standby side and connect our Target (Primary) and then to the Auxiliary (Standby)
[oracle@rac2-node1 ~]$ rman
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Aug 22 11:43:57 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET sys/Welcome1@TEST;
connected to target database: TEST (DBID=2346595492)
RMAN> CONNECT AUXILIARY sys/Welcome1@TESTDG;
connected to auxiliary database: TEST (not mounted)
Now that we are connected, lets execute Duplication script. You can add more options, but I think this is basic enough and easy to understand
If you have any issues during the script execution, you should stop the new Standby and start as nomount as we did before
# Script #
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE
set 'db_unique_name'='TESTDG'
set instance_name='TESTDG1'
set instance_number='1'
set db_create_online_log_dest_1='+DATA'
set db_recovery_file_dest='+RECO'
set audit_file_dest='/u01/app/oracle/admin/TESTDG/adump'
set core_dump_dest='/u01/app/oracle/admin/TESTDG/cdump'
nofilenamecheck;
}
# Execution #
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE
set 'db_unique_name'='TESTDG'
set instance_name='TESTDG1'
set instance_number='1'
set db_create_online_log_dest_1='+DATA'
set db_recovery_file_dest='+RECO'
set audit_file_dest='/u01/app/oracle/admin/TESTDG/adump'
set core_dump_dest='/u01/app/oracle/admin/TESTDG/cdump'
nofilenamecheck;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=255 instance=TEST1 device type=DISK
allocated channel: prmy2
channel prmy2: SID=500 instance=TEST1 device type=DISK
allocated channel: stby1
channel stby1: SID=661 device type=DISK
Starting Duplicate Db at 22-AUG-20
contents of Memory Script:
{
backup as copy reuse
targetfile '+DATA/TEST/PASSWORD/pwdtest.281.1048448813' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwTESTDG1' targetfile
'+DATA/TEST/PARAMETERFILE/spfile.297.1048449579' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileTESTDG1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0/dbhome_1/dbs/spfileTESTDG1.ora''";
}
executing Memory Script
[......]
input datafile file number=00007 name=+DATA/TEST/DATAFILE/users.327.1048448999
output file name=+DATA/TESTDG/DATAFILE/users.262.1049111999 tag=TAG20200822T115621
channel prmy1: datafile copy complete, elapsed time: 00:00:01
channel prmy1: starting datafile copy
input datafile file number=00014 name=+DATA/TEST/ACDC107EDC390BC2E0530C01A8C0FEF5/DATAFILE/users.298.1048449829
output file name=+DATA/TESTDG/DATAFILE/undotbs2.258.1049111997 tag=TAG20200822T115621
channel prmy2: datafile copy complete, elapsed time: 00:00:03
output file name=+DATA/TESTDG/ACDC107EDC390BC2E0530C01A8C0FEF5/DATAFILE/users.328.1049111999 tag=TAG20200822T115621
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-AUG-20
[....]
input datafile copy RECID=14 STAMP=1049112005 file name=+DATA/TESTDG/ACDC107EDC390BC2E0530C01A8C0FEF5/DATAFILE/undotbs1.331.1049111975
datafile 13 switched to datafile copy
input datafile copy RECID=15 STAMP=1049112005 file name=+DATA/TESTDG/ACDC107EDC390BC2E0530C01A8C0FEF5/DATAFILE/undo_2.330.1049111989
datafile 14 switched to datafile copy
input datafile copy RECID=16 STAMP=1049112005 file name=+DATA/TESTDG/ACDC107EDC390BC2E0530C01A8C0FEF5/DATAFILE/users.328.1049111999
Finished Duplicate Db at 22-AUG-20
released channel: prmy1
released channel: prmy2
released channel: stby1
RMAN>
Since we want to have a 2 Instances DB, we need to so some modifications first
SQL> ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='TESTDG1';
System altered.
SQL> ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='TESTDG2';
System altered.
SQL> ALTER SYSTEM SET INSTANCE_NAME='TESTDG1' SCOPE=SPFILE SID='TESTDG1';
System altered.
SQL> ALTER SYSTEM SET INSTANCE_NAME='TESTDG2' SCOPE=SPFILE SID='TESTDG2';
System altered.
I also recommend you to movet he spfile to a more "friendly named file" to keep some consistency:
SQL> CREATE PFILE='/tmp/TESTDG.ora' FROM SPFILE;
File created.
SQL> CREATE SPFILE='+DATA/TESTDG/PARAMETERFILE/spfile.ora' From PFILE='/tmp/TESTDG.ora';
File created.
[oracle@rac2-node1 ~]$ echo "SPFILE='+DATA/TESTDG/PARAMETERFILE/spfile.ora'" > /u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTESTDG1.ora
[oracle@rac2-node1 ~]$ scp -pr /u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTESTDG1.ora rac2-node2:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initTESTDG2.ora
initTESTDG1.ora 100% 47 45.3KB/s 00:00
[oracle@rac2-node1 ~]$
We can now add the System to Clusterware.
First we add it as Standby Database and then we add the 2 Instances to each node.
[oracle@rac2-node1 ~]$ srvctl add database -d TESTDG -o /u01/app/oracle/product/12.2.0/dbhome_1 -role physical_standby -startoption "read only" -diskgroup DATA,RECO
[oracle@rac2-node1 ~]$ srvctl add instance -d TESTDG -i TESTDG1 -n rac2-node1
[oracle@rac2-node1 ~]$ srvctl add instance -d TESTDG -i TESTDG2 -n rac2-node2
[oracle@rac2-node1 ~]$ srvctl modify database -d TESTDG -p +DATA/TESTDG/PARAMETERFILE/spfile.ora
Another thing we should do is to move the Standby password file to +ASM
[oracle@rac2-node1 ~]$ asmcmd -p
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > cd TESTDG
ASMCMD [+DATA/TESTDG] > mkdir PASSWORD
ASMCMD [+DATA/TESTDG] > cd PASSWORD
ASMCMD [+DATA/TESTDG/PASSWORD] > pwcopy /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwTESTDG1 +DATA/TESTDG/PASSWORD/orapwTESTSDG
copying /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwTESTDG1 -> +DATA/TESTDG/PASSWORD/orapwTESTSDG
ASMCMD [+DATA/TESTDG/PASSWORD] >
[oracle@rac2-node1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? TESTDG1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
The Oracle base has been changed from /u01/app/grid to /u01/app/oracle
[oracle@rac2-node1 ~]$ srvctl modify database -d TESTDG -pwfile +DATA/TESTDG/PASSWORD/orapwTESTSDG
[oracle@rac2-node1 ~]$
[oracle@rac2-node1 ~]$ srvctl config database -d TESTDG
Database unique name: TESTDG
Database name:
Oracle home: /u01/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/TESTDG/PARAMETERFILE/spfile.ora
Password file: +DATA/TESTDG/PASSWORD/orapwTESTSDG
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,RECO
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: TESTDG1,TESTDG2
Configured nodes: rac2-node1,rac2-node2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@rac2-node1 ~]$
Lets restart the DB as mounted to make sure both instances are started correctly
At this point, system may fail to open with "ORA-10458: standby database requires recovery since we are setup Data broker yet, so system is not able to communicate with primary and fecth current REDO
[oracle@rac2-node1 ~]$ sqlplus / as sysdba
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
[oracle@rac2-node1 ~]$ srvctl start database -d TESTDG -o mount
[oracle@rac2-node1 ~]$
Now, we need to add DG_BROKER empty configuration files to both sides
#Primary#
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DATA/TEST/DG_BROKER_CONFIG1.DAT' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+RECO/TEST/DG_BROKER_CONFIG2.DAT' SCOPE=BOTH;
System altered.
SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.
# Standby #
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DATA/TESTDG/DG_BROKER_CONFIG1.DAT' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+RECO/TESTDG/DG_BROKER_CONFIG2.DAT' SCOPE=BOTH;
System altered.
SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.
Datguard Broker is now ready to be setup.
For that, we connect to it from Pimary side, create the Primary Database configuration and then we add the Standby.
As last step, we enable the configuration
[oracle@rac1-node1 ~]$ dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Aug 22 12:40:06 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "TEST"
Connected as SYSDG.
DGMGRL> CREATE CONFIGURATION DG_BROKER_CONFIG AS PRIMARY DATABASE IS TEST CONNECT IDENTIFIER IS TEST;
Configuration "dg_broker_config" created with primary database "test"
DGMGRL> ADD DATABASE TESTDG AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;
Database "testdg" added
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show database TESTDG
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 73.00 KByte/s
Real Time Query: OFF
Instance(s):
TESTDG1 (apply instance)
TESTDG2
Database Status:
SUCCESS
DGMGRL>
If all goes as it should, this is what we should see in the Standby Alertlog, first confirming the DG Broker configuration and immediately after, we see how Recovery process is started and Archivelogs are applied:
2020-08-22T12:41:55.607913+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(testdg)']
2020-08-22T12:41:55.710671+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(testdg)' SCOPE=BOTH;
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(TESTDG,test)']
2020-08-22T12:41:55.888304+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(TESTDG,test)' SCOPE=BOTH;
Starting background process NSV0
2020-08-22T12:41:55.920290+01:00
NSV0 started with pid=55, OS id=6988
2020-08-22T12:42:03.600627+01:00
Starting background process RSM0
2020-08-22T12:42:03.630941+01:00
RSM0 started with pid=67, OS id=7009
2020-08-22T12:42:08.097339+01:00
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='TESTDG1';
2020-08-22T12:42:08.154409+01:00
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='TESTDG1';
2020-08-22T12:42:08.451243+01:00
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
2020-08-22T12:42:08.547972+01:00
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
2020-08-22T12:42:08.805954+01:00
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
2020-08-22T12:42:09.015793+01:00
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
2020-08-22T12:42:09.234752+01:00
ALTER SYSTEM SET data_guard_sync_latency=0 SCOPE=BOTH SID='*';
2020-08-22T12:42:09.388845+01:00
ALTER SYSTEM SET fal_server='test' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
2020-08-22T12:42:09.491980+01:00
Attempt to start background Managed Standby Recovery process (TESTDG1)
Starting background process MRP0
2020-08-22T12:42:09.523322+01:00
MRP0 started with pid=79, OS id=7067
2020-08-22T12:42:09.525487+01:00
MRP0: Background Managed Standby Recovery process started (TESTDG1)
2020-08-22T12:42:10.687790+01:00
RFS[1]: Assigned to RFS process (PID:7069)
RFS[1]: Opened log for T-1.S-3 dbid 2346595492 branch 1048449065
2020-08-22T12:42:11.604173+01:00
Primary database is in MAXIMUM PERFORMANCE mode
2020-08-22T12:42:11.973938+01:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:7076)
RFS[2]: Selected log 8 for T-2.S-5 dbid 2346595492 branch 1048449065
2020-08-22T12:42:12.568782+01:00
RFS[3]: Assigned to RFS process (PID:7079)
RFS[3]: Selected log 9 for T-2.S-4 dbid 2346595492 branch 1048449065
2020-08-22T12:42:12.979727+01:00
RFS[4]: Assigned to RFS process (PID:7081)
RFS[4]: Selected log 5 for T-1.S-5 dbid 2346595492 branch 1048449065
2020-08-22T12:42:13.713759+01:00
RFS[5]: Assigned to RFS process (PID:7083)
RFS[5]: Selected log 6 for T-1.S-4 dbid 2346595492 branch 1048449065
2020-08-22T12:42:13.722897+01:00
Archived Log entry 1 added for thread 1 sequence 3 rlc 1048449065 ID 0x8bde9d9e LAD2 :
2020-08-22T12:42:14.488865+01:00
RFS[6]: Assigned to RFS process (PID:7094)
RFS[6]: Opened log for T-2.S-3 dbid 2346595492 branch 1048449065
2020-08-22T12:42:14.756261+01:00
Starting single instance redo apply (SIRA)
Started logmerger process
2020-08-22T12:42:16.017149+01:00
Managed Standby Recovery starting Real Time Apply
2020-08-22T12:42:16.072516+01:00
Archived Log entry 2 added for T-2.S-4 ID 0x8bde9d9e LAD:1
2020-08-22T12:42:16.601886+01:00
Archived Log entry 3 added for T-1.S-4 ID 0x8bde9d9e LAD:1
2020-08-22T12:42:16.828860+01:00
Archived Log entry 4 added for thread 2 sequence 3 rlc 1048449065 ID 0x8bde9d9e LAD2 :
2020-08-22T12:42:17.381826+01:00
Parallel Media Recovery started with 8 slaves
2020-08-22T12:42:17.723949+01:00
Media Recovery Log +RECO/TESTDG/ARCHIVELOG/2020_08_22/thread_1_seq_3.1090.1049114531
2020-08-22T12:42:17.799127+01:00
Waiting for all non-current ORLs to be archived
2020-08-22T12:42:17.799549+01:00
All non-current ORLs have been archived
2020-08-22T12:42:17.844507+01:00
NOTE: dependency between database TESTDG and diskgroup resource ora.RECO.dg is established
2020-08-22T12:42:18.018819+01:00
Media Recovery Log +RECO/TESTDG/ARCHIVELOG/2020_08_22/thread_2_seq_3.1084.1049114535
2020-08-22T12:42:18.562868+01:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
2020-08-22T12:42:18.660714+01:00
Media Recovery Log +RECO/TESTDG/ARCHIVELOG/2020_08_22/thread_1_seq_4.1094.1049114537
2020-08-22T12:42:19.033745+01:00
Media Recovery Log +RECO/TESTDG/ARCHIVELOG/2020_08_22/thread_2_seq_4.1092.1049114535
2020-08-22T12:42:20.632132+01:00
Media Recovery Waiting for thread 2 sequence 5 (in transit)
2020-08-22T12:42:20.640513+01:00
Recovery of Online Redo Log: Thread 2 Group 8 Seq 5 Reading mem 0
Mem# 0: +DATA/TESTDG/ONLINELOG/group_8.315.1049112501
2020-08-22T12:42:35.534885+01:00
Media Recovery Waiting for thread 1 sequence 5 (in transit)
2020-08-22T12:42:35.544298+01:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 5 Reading mem 0
Mem# 0: +DATA/TESTDG/ONLINELOG/group_5.318.1049112381
Let's restart the Standby to make sure is able to open correctly
[oracle@rac2-node1 ~]$ srvctl stop database -d TESTDG ; srvctl start database -d TESTDG
[oracle@rac2-node1 ~]$ srvctl status database -d TESTDG -v
Instance TESTDG1 is running on node rac2-node1. Instance status: Open,Readonly.
Instance TESTDG2 is running on node rac2-node2. Instance status: Open,Readonly.
[oracle@rac2-node1 ~]$
You can now do a switchover.
Is good idea to do a Validation before you start to make sure both systems are ready
[oracle@rac2-node1 ~]$ dgmgrl
DGMGRL> connect sys/Welcome1@TEST
Connected to "TEST"
Connected as SYSDBA.
DGMGRL>
DGMGRL> validate database TEST
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
test: Off
Managed by Clusterware:
test: YES
DGMGRL> validate database TESTDG
Database Role: Physical standby database
Primary Database: test
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
test : Off
testdg: Off
Managed by Clusterware:
test : YES
testdg: YES
DGMGRL>
DGMGRL> switchover to TESTDG
Performing switchover NOW, please wait...
Operation requires a connection to database "testdg"
Connecting ...
Connected to "TESTDG"
Connected as SYSDBA.
New primary database "testdg" is opening...
Oracle Clusterware is restarting database "test" ...
Connected to "TEST"
Connected to "TEST"
Switchover succeeded, new primary is "testdg"
DGMGRL> show configuration
Configuration - dg_broker_config
Protection Mode: MaxPerformance
Members:
testdg - Primary database
test - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 143 seconds ago)
DGMGRL>
You can do some cleaning, like delete static entry in the listener like we created.
The Steps are a bit long, but once you get use to them, you can just adapt the process to other systems.
In any case, this is only one way to create a Standby, will see more options soon :)
Top comments (0)