On this activity we are going to create a Physical Standby from a 19c in CDB configuration:
These are the details for the primary DB:
INST HOST INST_NAME STATUS DB_ROLE OPEN_MODE STARTUP_TIME
---- ------------------ ---------- ---------- ------------- ----------------- --------------------
*1 RACnode1 test19c1 OPEN PRIMARY READ WRITE 30-OCT-2021 21:31:17
2 RACnode2 test19c2 OPEN PRIMARY READ WRITE 30-OCT-2021 21:40:51
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST19C_PDB1 READ WRITE NO
SQL> show parameter db_name
NAME TYPE VALUE
------------------------ ------- -----------------------------
db_name string test19c
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------ ------- ------------------------------
db_unique_name string test19cOur plan is to create a Standby DB with these details:
Server: standbyracnode
db_unique_name: TESTSTB
db_name: TEST19C
SID: TESTSTBSTEPS:
1:- Prepare Primary database
Enable force logging and archivelog mode:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 8
Current log sequence 8
SQL> alter database force logging;
Database altered.
SQL> Create Standby Redo Log (SRL) files:
**We are creating SRL’s now because if they exist, the RMAN duplicate command will create SRL’s in the Standby in the a same process
Check the current size of the redo log:
col member format a50
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 2,1
/
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- -------------------------------------------------- ---------- ---------- ---------
1 1 +RECO/TEST19C/ONLINELOG/group_1.314.1087130049 YES INACTIVE 200
1 1 +DATA/TEST19C/ONLINELOG/group_1.288.1087130033 YES INACTIVE 200
2 1 +RECO/TEST19C/ONLINELOG/group_2.312.1087130049 NO CURRENT 200
2 1 +DATA/TEST19C/ONLINELOG/group_2.295.1087130033 NO CURRENT 200
3 2 +DATA/TEST19C/ONLINELOG/group_3.294.1087134633 YES INACTIVE 200
3 2 +RECO/TEST19C/ONLINELOG/group_3.289.1087134643 YES INACTIVE 200
4 2 +DATA/TEST19C/ONLINELOG/group_4.292.1087134655 NO CURRENT 200
4 2 +RECO/TEST19C/ONLINELOG/group_4.321.1087134667 NO CURRENT 200
8 rows selected.
SQL>
Documentation suggest to add an extra log on the SRL (ORL+1), if not Standby will have issues using real time apply.
SQL> alter database add standby logfile thread 1 group 11 ('+RECO') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 12 ('+RECO') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 13 ('+RECO') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 21 ('+RECO') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 22 ('+RECO') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 23 ('+RECO') size 200M;
Database altered.
SQL>
**Naming convention on groups here is to have a easy job in case in future we need to add extra logs
Primary Database Initilization Parameters:
alter system set log_archive_config='dg_config=(TEST19C,TESTSTB)' scope=spfile;
alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=TEST19C' scope=spfile;
alter system set log_archive_dest_2='service=TESTSTB async valid_for=(online_logfiles,primary_role) db_unique_name=TESTSTB' scope=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set fal_server=TESTSTB scope=spfile;
alter system set db_file_name_convert='TEST19C','TESTSTB' scope=spfile;
alter system set log_file_name_convert='TEST19C','TESTSTB' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
alter system set log_archive_dest_state_2=defer scope=spfile; <<-- enable once all completed**Now, if you are planing to set up DATAGUARD BROKER, you don’t need to change these parameters since the broker will setup them for you. On this activity we did not set them and we configured the broker HERE.
2:- Add TNSNAME entries in both primary and standby nodes
Add these to tnsnames.ora in all nodes:
TEST19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RACnode-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test19c)
(UR = A)
)
)
TESTSTB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyracnode)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = teststb)
(UR = A)
)
)
Configure static listener entries (only on Standby node was enough for me):
[oracle@standbyracnode ~]$ ls -tlr $ORACLE_HOME/network/admin/listener.ora
-rw-r--r--. 1 oracle oinstall 1470 Aug 13 17:47 /u01/app/19c/grid/network/admin/listener.ora
[oracle@standbyracnode ~]$
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTSTB)
(ORACLE_HOME = /u01/app/oracle/product/19c/db_1/)
(SID_NAME = TESTSTB)
)
)3:- Copy Password file and create audit trail location on standby:
[oracle@RACnode1 ~]$ srvctl config db -d test19c | grep -i pass
Password file: +DATA/TEST19C/PASSWORD/pwdtest19c.281.1087129357
[oracle@RACnode1 ~]$
ASMCMD> cp +DATA/TEST19C/PASSWORD/pwdtest19c.281.1087129357 /tmp/TEST19C_pass
copying +DATA/TEST19C/PASSWORD/pwdtest19c.281.1087129357 -> /tmp/TEST19C_pass
ASMCMD>
[oracle@RACnode1 ~]$ scp -p /tmp/TEST19C_pass standbyracnode:/u01/app/oracle/product/19c/db_1/dbs/orapwTESTSTB
oracle@standbyracnode's password:
TEST19C_pass 100% 2048 146.1KB/s 00:00
[oracle@RACnode1 ~]$
[oracle@standbyracnode ~]$ mkdir /u01/app/oracle/admin/teststb/
[oracle@standbyracnode ~]$ mkdir /u01/app/oracle/admin/teststb/adump
[oracle@standbyracnode ~]$ 4:- Create and start Auxiliady DB
Create an init.ora file for standby (auxialary) db TESTSTB:
[oracle@standbyracnode ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19c/db_1
[oracle@standbyracnode ~]$ cd $ORACLE_HOME/dbs
[oracle@standbyracnode dbs]$ echo "DB_NAME=TESTSTB" > initTESTSTB.ora
[oracle@standbyracnode dbs]$
[oracle@standbyracnode dbs]$ export ORACLE_SID=TESTSTB
[oracle@standbyracnode dbs]$ Startup the auxialary in nomount mode:
[oracle@standbyracnode ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 30 22:40:26 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 306183456 bytes
Fixed Size 8895776 bytes
Variable Size 239075328 bytes
Database Buffers 50331648 bytes
Redo Buffers 7880704 bytes
SQL> 5:- Connect to RMAN and duplicate DB
RMAN> connect target sys/Pas5w0rd@TEST19C
connected to target database: TEST19C (DBID=2463024481)
RMAN> connect auxiliary sys/Pas5w0rd@TESTSTB
connected to auxiliary database: TESTSTB (not mounted)
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel a1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'TEST19C','TESTSTB'
set db_unique_name='TESTSTB'
set db_name="TEST19C"
set CLUSTER_DATABASE='FALSE'
set control_files='+DATA/TESTSTB/CONTROLFILE/control01.ctl','+RECO/TESTSTB/CONTROLFILE/control02.ctl'
set db_file_name_convert='+DATA/TEST19C','+DATA/TESTSTB'
set log_file_name_convert='+DATA/TEST19C','+DATA/TESTSTB'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(TEST19C,TESTSTB)'
set db_recovery_file_dest='+RECO'
set audit_file_dest='/u01/app/oracle/admin/teststb/adump';
release channel p1;
release channel p2;
}This will run for a while depending on the size of your DB, once completed, it will finish like this:
...
...
input datafile copy RECID=14 STAMP=1087346500 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undotbs1.294.1087346421
datafile 13 switched to datafile copy
input datafile copy RECID=15 STAMP=1087346500 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/undo_2.295.1087346441
datafile 14 switched to datafile copy
input datafile copy RECID=16 STAMP=1087346500 file name=+DATA/TESTSTB/CF6A704554271263E0530218A8C04D71/DATAFILE/users.298.1087346471
Finished Duplicate Db at 31-OCT-21
released channel: p1
released channel: p2
released channel: a1
RMAN> 6:- Configure DATAGUARD BROKER.
You can follow the steps here: DGMGRL: How to enable BROKER
7:- Verify state of standby
INST HOST INST_NAME STATUS DB_ROLE OPEN_MODE STARTUP_TIME
---- ------------------ ---------- ---------- ----------------- --------------------- --------------------
*1 standbyracnode TESTSTB OPEN PHYSICAL STANDBY READ ONLY WITH APPLY 31-OCT-2021 01:11:38
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TEST19C_PDB1 READ ONLY NO
SQL>
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TEST19C
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TESTSTB
DB in sync?
NAME VALUE DATUM_TIME
------------------------------ -------------------- ------------------------------
transport lag +00 00:00:00 10/31/2021 01:59:10
apply lag +00 00:00:00 10/31/2021 01:59:10
apply finish time +00 00:00:00.000
estimated startup time 283
INST_ID PROCESS MRP_STAT THREAD# SEQUENCE# BLOCK#
---------- --------- ------------- ---------- ---------- ----------
1 MRP0 APPLYING_LOG 1 10 279
1 RFS RECEIVING 1 10 283
1 RFS RECEIVING 2 6 248
SQL> /
INST_ID PROCESS MRP_STAT THREAD# SEQUENCE# BLOCK#
---------- --------- ------------- ---------- ---------- ----------
1 MRP0 APPLYING_LOG 1 10 287
1 RFS RECEIVING 1 10 291
1 RFS RECEIVING 2 6 256
SQL> /
INST_ID PROCESS MRP_STAT THREAD# SEQUENCE# BLOCK#
---------- --------- ------------- ---------- ---------- ----------
1 MRP0 APPLYING_LOG 1 10 290
1 RFS RECEIVING 1 10 291
1 RFS RECEIVING 2 6 257
SQL>
Broker state?
DGMGRL> show configuration
Configuration - dg_test19c
Protection Mode: MaxPerformance
Members:
test19c - Primary database
teststb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 10 seconds ago)
DGMGRL> show database 'teststb'
Database - teststb
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: 6.00 KByte/s
Real Time Query: ON
Instance(s):
TESTSTB
Database Status:
SUCCESS
DGMGRL> And that’s it, these are the steps to create a standby db from 0 in 19c.
If you follow all the steps, you should not have errors, but here are the list of possible errors you would face if some steps are missed:
The only think I found is that the process multiplexes the creation of SRL. There is nothing wrong with that, but Oracle suggest to not multiplex SRL for performance reasons.
Comments