Objective of this exercise:
Setup a standby database using active database duplication in oracle 19c. The database isnt using OMF or ASM.
oracle document used for reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-oracle-data-guard-physical-standby.html#GUID-0DC30726-3471-4588-BFE0-9CA0736328E2
Create an example primary db:
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ORA19C01 -sid ORA19C01 -responseFile NO_VALUE -characterSet AL32UTF8 -sysPassword <syspass> -systemPassword <systempass> -databaseType MULTIPURPOSE -automaticMemoryManagement false -totalMemory 2000 -storageType FS -datafileDestination /oradata -redoLogFileSize 50 -emConfiguration NONE -ignorePreReqs
Setting up the primary DB for dataguard setup:
1) Enable appropriate logging (there are other 2 modes available as well, which is an enhancement to support batch loads)
SQL> alter database force logging;
Database altered.
SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE FORCE_LOGG
--------- -------------------- ---------------- ------------ ----------
ORA19C01 READ WRITE PRIMARY NOARCHIVELOG YES
SQL>
2) Configure redo transport authentication:
setup the below params:
log_archive_dest_n
fal_server
3) Add necessary standby redologs
Always add g+1 number of standby logfile. Where g is the numbe of redologs found in primary.
alter database add standby logfile ('<path>') size 50M;
4) Setup initialization params on primary db (supports both primary and standby role)
primary: chicago
standby: boston
Primary role params:
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
Standby role params:
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='/boston/','/chicago/'
LOG_FILE_NAME_CONVERT='/boston/','/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO
Actual:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA19C01,ORA19C01SB1)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA19C01';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORA19C01SB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA19C01SB1';
alter system set LOG_ARCHIVE_FORMAT='ORA19C01_%t_%s_%r.arc' scope=spfile;
alter system set FAL_SERVER=ORA19C01SB1;
alter system set DB_FILE_NAME_CONVERT='/ORA19C01SB1/','/ORA19C01/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/ORA19C01SB1/','/ORA19C01/' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
5) Create a Backup Copy of the Primary Database Data Files
6) Create a Control File for the Standby Database
7) Create a Parameter File for the Standby Database
8) Copy Files from the Primary System to the Standby System
Note:The steps 5 through 8 will be done using active database cloning.
9) Set Up the Environment to Support the Standby Database
a) Add /etc/oratab entry
standby:
[oracle@localhost dbs]$ tail -1 /etc/oratab
ORA19C01SB1:/u01/app/oracle/product/19.0.0/db_1:N
[oracle@localhost dbs]$
b) Copy password file from primary to standby
Primary:
[oracle@localhost dbs]$ cp orapwORA19C01 orapwORA19C01SB1
[oracle@localhost dbs]$ scp orapwORA19C01SB1 oracle@192.168.50.5:/u01/app/oracle/product/19.0.0/db_1/dbs
oracle@192.168.50.5's password:
orapwORA19C01SB1 100% 2048 236.6KB/s 00:00
[oracle@localhost dbs]$
c) Make static entry in listener & tns entry for both the primary and standby in both the servers
Standby listener:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORA19C01SB1)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = ORA19C01SB1)
)
)
Primary listener:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORA19C01)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = ORA19C01)
)
)
Both tnsnames.ora:
ORA19C01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C01)
)
)
ORA19C01SB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.5)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C01SB1)
)
)
d) Add only db_name and db_unique_name parameter and startup nomount the standby database
[oracle@localhost dbs]$ cat initORA19C01SB1.ora
db_name=ORA19C01
db_unique_name=ORA19C01SB1
[oracle@localhost dbs]$
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA19C01SB1 STARTED
e) start the local listener if not done already in both primary and standby
f) Create the dump destination and controlfile, datafile, redologfile directories manually. Since we arent using ASM or OMF
primary:
[oracle@localhost dbs]$ find /oradata|grep -Ev ".dbf|.log|.ctl|mf"
/oradata
/oradata/ORA19C01
/oradata/ORA19C01/archives
/oradata/ORA19C01/archives/ORA19C01
[oracle@localhost dbs]$
Standby:
[oracle@localhost oradata]$ find /oradata
/oradata
/oradata/ORA19C01SB1
/oradata/ORA19C01SB1/archives
/oradata/ORA19C01SB1/archives/ORA19C01SB1
[oracle@localhost oradata]$
Location of controlfile, datafile, redologfile: /oradata/ORA19C01SB1
Location of archives: /oradata/ORA19C01SB1/archives/ORA19C01SB1
adump: /u01/app/oracle/admin/ORA19C01SB1/adump
10) Frame the active database duplication command
reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/creating-data-guard-standby-database-using-RMAN.html#GUID-C713FD5C-3375-45E4-A78F-4063006DAC5B
ex:
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET "db_unique_name"="foou" COMMENT "Is a duplicate"
SET LOG_ARCHIVE_DEST_2="service=inst3 ASYNC REGISTER
VALID_FOR=(online_logfile,primary_role)"
SET FAL_SERVER="inst1" COMMENT "Is primary"
NOFILENAMECHECK;
Actuals:
File: sbysetup.cmd
Contents:
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
PARAMETER_VALUE_CONVERT='ORA19C01','ORA19C01SB1'
SET "db_name"="ORA19C01"
SET "db_unique_name"="ORA19C01SB1"
SET DB_FILE_NAME_CONVERT='/ORA19C01/','/ORA19C01SB1/'
SET LOG_FILE_NAME_CONVERT='/ORA19C01/','/ORA19C01SB1/'
SET LOG_ARCHIVE_DEST_2="service=ORA19C01 ASYNC REGISTER
VALID_FOR=(online_logfile,primary_role)"
SET FAL_SERVER="ORA19C01"
NOFILENAMECHECK;
Notes:
db_file_name_convert & log_file_name_convert arent required in case you use OMF. Ensure to unset this params in source as well, otherwise you will recieve an RMAN- error.
db_name param is needed in the duplicate command, since your using PARAMETER_VALUE_CONVERT keyword in the duplicate command.
11) Make service based test connection to both primary and standby, if required stop the firewall on the host or make necessary exceptions.
12) Frame a shell script to call the command we framed above....
File: sbysetup.sh
Contents:
cd /home/oracle/dba/SbySetup;
export ORACLE_SID=ORA19C01SB1;export ORAENV_ASK=NO;. oraenv >/dev/null 2>&1;
rman target sys/sys@ORA19C01 auxiliary sys/sys@ORA19C01SB1 cmdfile=sbysetup.cmd log=sbysetup_duplicate.log
13) Invoke the shell script:
nohup sh sbysetup.sh > nh_sbysetup.out &
14) Wait for the duplicate command to finish
15) Verify the output:
Output:
[oracle@localhost SbySetup]$ head -5 sbysetup_duplicate.log
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 9 23:25:59 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@localhost SbySetup]$ head -10 sbysetup_duplicate.log
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 9 23:25:59 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C01 (DBID=3909934395)
connected to auxiliary database: ORA19C01 (not mounted)
RMAN> DUPLICATE TARGET DATABASE
[oracle@localhost SbySetup]$ tail -4 sbysetup_duplicate.log
Finished Duplicate Db at 09-FEB-22
Recovery Manager complete.
---
[oracle@localhost SbySetup]$ grep -iE "RMAN-|ORA-" sbysetup_duplicate.log
[oracle@localhost SbySetup]$
16) Verify file locations:
SQL> select name from v$datafile;
NAME
----------------------------------
/oradata/ORA19C01SB1/system01.dbf
/oradata/ORA19C01SB1/sysaux01.dbf
/oradata/ORA19C01SB1/undotbs01.dbf
/oradata/ORA19C01SB1/users01.dbf
SQL> select member from v$logfile;
MEMBER
----------------------------------
/oradata/ORA19C01SB1/redo03.log
/oradata/ORA19C01SB1/redo02.log
/oradata/ORA19C01SB1/redo01.log
SQL> select name from v$controlfile;
NAME
------------------------------------
/oradata/ORA19C01SB1/control01.ctl
/oradata/ORA19C01SB1/control02.ctl
SQL> select name from v$tempfile;
NAME
--------------------------------
/oradata/ORA19C01SB1/temp01.dbf
SQL>
[oracle@localhost SbySetup]$
17) There is no automatic recovery initated by the duplication command, hence we manually kick off the recovery..
command: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> select process,pid,status,client_process from v$managed_standby;
PROCESS PID STATUS CLIENT_P
--------- ------------------------ ------------ --------
DGRD 14968 ALLOCATED N/A
ARCH 14970 CONNECTED ARCH
DGRD 14972 ALLOCATED N/A
ARCH 14974 CONNECTED ARCH
ARCH 14976 CONNECTED ARCH
ARCH 14978 CONNECTED ARCH
RFS 15576 IDLE UNKNOWN
RFS 15578 IDLE LGWR
RFS 15581 IDLE Archival
MRP0 15790 WAIT_FOR_LOG N/A
10 rows selected.
SQL> /
PROCESS PID STATUS CLIENT_P SEQUENCE#
--------- ------------------------ ------------ -------- ----------
DGRD 14968 ALLOCATED N/A 0
ARCH 14970 CONNECTED ARCH 0
DGRD 14972 ALLOCATED N/A 0
ARCH 14974 CONNECTED ARCH 0
ARCH 14976 CONNECTED ARCH 0
ARCH 14978 CONNECTED ARCH 0
RFS 15576 IDLE UNKNOWN 0
RFS 15578 IDLE LGWR 32
RFS 15581 IDLE Archival 0
MRP0 15790 WAIT_FOR_LOG N/A 32
10 rows selected.
cmd: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>
The recovery is cancelled.
Thanks
