Setting Oracle Dataguard on RAC 19c - MAA Methodology
In this article we will be configuring Dataguard on RAC nodes. We will be using 2 Node RAC setup on Primary site and 2 Node RAC on standby Site.
For Step-by-Step RAC installation on OEL7.3 please click Step by Step Oracle RAC Setup
Assumptions
=> 2 Node RAC on Primary Site - Primary Site having 2 Node of RAC installed on OEL 7.3 and using 19c Cluster and 19c Oracle Home. => 2 Node RAC on Standby Site - Standby Site Having 2 Node of RAC installed on OEL 7.3 and using 19C Cluster and 19c Oracle Home. => Primary Site Details - Primary Site is having working Database and details are below. => Standby Site Details - Standby Site is having only Oracle Software Installed and no database is there. => Manual Method for DB restoration -We will be using completely Manual Method for Database restore and DG setup. => Configuration details for servers are as below.-=> Hosts detail of Primary Site.
=> Host detail of Standby Site.
Primay Site Configurations.
=> Check Force Logging & Archivelog Mode as below
QL> select log_mode,database_role,open_mode from gv$database;
LOG_MODE DATABASE_ROLE OPEN_MODE
------------ ---------------- --------------------
ARCHIVELOG PRIMARY READ WRITE
ARCHIVELOG PRIMARY READ WRITE
SQL> select force_logging from gv$database;
FORCE_LOGGING
---------------------------------------
NO
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from gv$database;
FORCE_LOGGING
---------------------------------------
YES
YES
=> Check DB_UNIQUE_NAME
[oracle@racnoden1 ~]$ sqlplus "/as sysdba"
SQL> show parameter db_uniq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string DBSET
SQL>
=> Configure Dataguard Parameter as below.
check values of Dataguard related parameters as below.
SQL> set linesize 500 pages 100
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name',
'db_unique_name',
'log_archive_config',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_client',
'fal_server',
'db_file_name_convert',
'log_file_name_convert',
'standby_file_management');
NAME VALUE
-------------------------------------------------- --
db_file_name_convert
log_file_name_convert
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_client
fal_server
log_archive_config
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management MANUAL
remote_login_passwordfile EXCLUSIVE
db_name DBSET
db_unique_name DBSET
15 rows selected.
Run below command to configure DG parameter
SQL > alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBSET,DBTAR)' scope=both sid='*';
SQL > alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSET' scope=both sid='*';
SQL > alter system set LOG_ARCHIVE_DEST_2='SERVICE=TNSTAR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR' scope=both sid='*';
SQL > alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
SQL > alter system set log_archive_max_processes=8 scope=both sid='*';
SQL > alter system set fal_server=DBTAR scope=both sid='*';
SQL > alter system set fal_client=DBSET scope=both sid='*';
SQL > alter system set db_file_name_convert='DBTAR','DBSET' scope=spfile sid='*';
SQL > alter system set log_file_name_convert='DBTAR','DBSET' scope=spfile sid='*';
SQL > alter system set standby_file_management=AUTO scope=both sid='*';
Check details again as below.
SQL> set linesize 500 pages 100
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1',
|'log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_client','fal_server',
'db_file_name_convert','log_file_name_convert', 'standby_file_management');
NAME VALUE
-------------------------------------------------- -----
db_file_name_convert
log_file_name_convert
log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSET
log_archive_dest_2 SERVICE=TNSTAR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_client DBSET
fal_server DBTAR
log_archive_config DG_CONFIG=(DBSET,DBTAR)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 8
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name DBSET
db_unique_name DBSET
15 rows selected.
SQL>
=> Configure Standby Redo Logs
we will create standby redo log groups on Primary sites and that will be calculated as below,
(maximum # of Online logfiles group per group +1) * maximum # of threads
(2+1)*2 = 6 groups for Standby Logfiles
Current Online redo logs details as below
SQL> select GROUP#,THREAD#,BYTES/1024/1024,STATUS,MEMBERS FROM V$lOG;
GROUP# THREAD# BYTES/1024/1024 STATUS MEMBERS
---------- ---------- --------------- ---------------- ----------
1 1 200 INACTIVE 2
2 1 200 CURRENT 2
3 2 200 CURRENT 2
4 2 200 INACTIVE 2
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5('+DATA/DBSET/STANDBYLOGS/stdby_01.log','+FRA/DBSET/STANDBYLOGS/stdby_11.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6('+DATA/DBSET/STANDBYLOGS/stdby_02.log','+FRA/DBSET/STANDBYLOGS/stdby_22.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7('+DATA/DBSET/STANDBYLOGS/stdby_03.log','+FRA/DBSET/STANDBYLOGS/stdby_33.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 8('+DATA/DBSET/STANDBYLOGS/stdby_04.log','+FRA/DBSET/STANDBYLOGS/stdby_44.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 9('+DATA/DBSET/STANDBYLOGS/stdby_05.log','+FRA/DBSET/STANDBYLOGS/stdby_55.log') SIZE 200M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 10('+DATA/DBSET/STANDBYLOGS/stdby_06.log','+FRA/DBSET/STANDBYLOGS/stdby_66.log') SIZE 200M;
Database altered.
SQL>
SQL> SELECT INST_ID, GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS
FROM GV$STANDBY_LOG ORDER BY 2,1 2 ;
INST_ID GROUP# THREAD# SEQUENCE# ARCHIVE STATUS
------- ------ ---------- ---------- ------- ----------
1 5 1 0 YES UNASSIGNED
2 5 1 0 YES UNASSIGNED
1 6 1 0 YES UNASSIGNED
2 6 1 0 YES UNASSIGNED
1 7 1 0 YES UNASSIGNED
2 7 1 0 YES UNASSIGNED
1 8 2 0 YES UNASSIGNED
2 8 2 0 YES UNASSIGNED
1 9 2 0 YES UNASSIGNED
2 9 2 0 YES UNASSIGNED
1 10 2 0 YES UNASSIGNED
2 10 2 0 YES UNASSIGNED
12 rows selected.
SQL>
=> Check TNS details
[oracle@racnoden1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /ora_home/app/19c/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TNSSET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBSET)
)
)
TNSTAR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTAR)
)
)
[oracle@racnoden1 admin]$
=> Check Listener status
[grid@racnoden1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-JUN-2022 16:28:39
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 24-JUN-2022 15:20:18
Uptime 0 days 1 hr. 8 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid_home/app/19c/grid/network/admin/listener.ora
Listener Log File /grid_home/app/oracle/diag/tnslsnr/racnoden1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.103)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_OCR" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "DBSET" has 1 instance(s).
Instance "DBSET1", status READY, has 1 handler(s) for this service...
Service "DBSETXDB" has 1 instance(s).
Instance "DBSET1", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@racnoden1 ~]$
=> Check tnsping status .
[oracle@racnoden1 admin]$ tnsping TNSSET
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-JUN-2022 16:22:41
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racnode-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBSET)))
OK (10 msec)
[oracle@racnoden1 admin]$ tnsping TNSTAR
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-JUN-2022 16:24:13
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = racset-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBTAR)))
OK (10 msec)
=> Take RMAN backup.
We will use RMAN to take backup and do manual recovery for DG setup.
cat rman_bkp.scr
run
{
sql "alter system switch logfile";
allocate channel ch1 type disk format '/tmp/bkp_prim/Primary_bkp_for_stndby_%U';
backup database;
backup current controlfile for standby format '/tmp/bkp_prim/stndby_ctl.ctl';
sql "alter system archive log current";
}
[oracle@racnoden1 bkp_prim]$ rman target / cmdfile=rman_bkp.scr msglog=rman_log.log
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
[oracle@racnoden1 bkp_prim]$
[oracle@racnoden1 bkp_prim]$
[oracle@racnoden1 bkp_prim]$ ls -lrt
total 1255896
-rwxrwxrwx. 1 oracle oinstall 284 Jun 24 17:47 rman_bkp.scr
-rw-r-----. 1 oracle asmadmin 1266229248 Jun 24 17:47 Primary_bkp_for_stndby_0510sg8s_1_1
-rw-r-----. 1 oracle asmadmin 19791872 Jun 24 17:47 stndby_ctl.ctl
-rw-r--r--. 1 oracle oinstall 2259 Jun 24 17:47 rman_log.log
[oracle@racnoden1 bkp_prim]$
=> take backup of Spfile and ASM-Password file.
SQL> create pfile='/tmp/bkp_prim/pfile_prod.ora' from spfile;
File created.
SQL> exit
[grid@racnoden1 ~]$ asmcmd
ASMCMD> pwd
+DATA/DBSET/PASSWORD
ASMCMD>
ASMCMD> pwcopy +DATA/DBSET/PASSWORD/pwddbset.256.1107118247 /tmp/bkp_prim/orapwprim
copying +DATA/DBSET/PASSWORD/pwddbset.256.1107118247 -> /tmp/bkp_prim/orapwprim
ASMCMD>
ASMCMD> exit
[grid@racnoden1 ~]$
[oracle@racnoden1 dbs]$ cd /tmp/bkp_prim/
[oracle@racnoden1 bkp_prim]$ ls -lrt
total 1247440
-rw-r--r--. 1 oracle oinstall 246 Jun 24 17:14 rman_bkp.scr
-rw-r-----. 1 oracle asmadmin 1266229248 Jun 24 17:47 Primary_bkp_for_stndby_0510sg8s_1_1
-rw-r--r--. 1 oracle oinstall 2243 Jun 24 17:14 rman_log.log
-rw-r-----. 1 grid oinstall 2048 Jun 24 17:20 orapwprim
-rw-r--r--. 1 oracle asmadmin 2347 Jun 24 17:21 pfile_prod.ora
-rw-r-----. 1 oracle asmadmin 19791872 Jun 24 17:47 stndby_ctl.ctl
[oracle@racnoden1 bkp_prim]$
=>Transfer files to standby nodes.
transfer tnsnames.ora file on both nodes of Standby Site.
[oracle@racnoden1 admin]$ scp tnsnames.ora oracle@racsetn1:/ora_home/app/19c/db/network/admin/tnsnames.ora
oracle@racsetn1's password:
tnsnames.ora 100% 493 914.6KB/s 00:00
[oracle@racnoden1 admin]$ scp tnsnames.ora oracle@racsetn2:/ora_home/app/19c/db/network/admin/tnsnames.ora
tnsnames.ora 100% 493 730.8KB/s 00:00
[oracle@racnoden1 admin]$
Transfer backup file ,ControlFile , password file ,pfile to Standbby node as below.
[oracle@racnoden1 bkp_prim]$ scp * racsetn1:/tmp/bkp_tar/
oracle@racsetn1's password:
orapwprim 100% 2048 2.8MB/s 00:00
pfile_prod.ora 100% 2347 3.9MB/s 00:00
Primary_bkp_for_stndby_0510sg8s_1_1 100% 1199MB 85.6MB/s 00:14
rman_bkp.scr 100% 246 406.0KB/s 00:00
rman_log.log 100% 2243 4.1MB/s 00:00
stndby_ctl.ctl 100% 19MB 81.2MB/s 00:00
Standby Site Configurations
=> Directory Creation on Both Nodes. [oracle@racsetn1 ~]$ mkdir -p /ora_home/app/oracle/admin/DBSET/adump [oracle@racsetn2 ~]$ mkdir -p /ora_home/app/oracle/admin/DBSET/adump [oracle@racsetn1 bkp_tar]$ cd /ora_home/app/oracle/diag/rdbms/ [oracle@racsetn1 rdbms]$ mkdir dbtar [oracle@racsetn2 bkp_tar]$ cd /ora_home/app/oracle/diag/rdbms/ [oracle@racsetn12 rdbms]$ mkdir dbtar Create directory for ASM File storage as below same as Primary Setup [grid@racsetn1 ~]$ asmcmd -p ASMCMD [+] > ls DATA/ FRA/ OCR/ ASMCMD [+] > cd DATA ASMCMD [+DATA] > mkdir DBTAR ASMCMD [+DATA] > ls DBTAR/ ASMCMD [+DATA] > cd .. ASMCMD [+] > cd FRA ASMCMD [+FRA] > mkdir DBTAR ASMCMD [+FRA] > ls DBTAR/ ASMCMD [+FRA] > cd DBTAR ASMCMD [+FRA/DBTAR] > mkdir ARCHIVELOG AUTOBACKUP CONTROLFILE ONLINELOG STANDBYLOGS ASMCMD [+FRA/DBTAR] > ls ARCHIVELOG/ AUTOBACKUP/ CONTROLFILE/ ONLINELOG/ STANDBYLOGS/ ASMCMD [+FRA/DBTAR] > cd ../.. ASMCMD [+] > cd DATA/DBTAR ASMCMD [+DATA/DBTAR] > mkdir CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE PASSWORD STANDBYLOGS TEMPFILE ASMCMD [+DATA/DBTAR] > ls CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ PASSWORD/ STANDBYLOGS/ TEMPFILE/ ASMCMD [+DATA/DBTAR] > exit [grid@racsetn1 ~]$ logout [oracle@racsetn1 admin]$ => Modifying Parameter file on Stanby Site. Now we will modify pfile which was copied from Primary Site. [oracle@racsetn1 bkp_tar]$ cat pfile_prod.ora *.audit_file_dest='/ora_home/app/oracle/admin/DBSET/adump' *.audit_trail='db' *.cluster_database=true *.compatible='19.0.0' *.control_files='+DATA/DBTAR/CONTROLFILE/control_01.ctl','+FRA/DBTAR/CONTROLFILE/control_02.ctl' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_file_name_convert='DBSET','DBTAR' *.db_unique_name='DBTAR' *.db_name='DBSET' *.db_recovery_file_dest='+FRA' *.db_recovery_file_dest_size=8256m *.diagnostic_dest='/ora_home/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=DBSETXDB)' *.fal_client='DBTAR' *.fal_server='DBSET' family:dw_helper.instance_mode='read-only' DBTAR1.instance_number=1 DBTAR2.instance_number=2 *.local_listener='-oraagent-dummy-' *.log_archive_config='DG_CONFIG=(DBSET,DBTAR)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBTAR' *.log_archive_dest_2='SERVICE=TNSSET LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSET' *.log_archive_dest_state_2='defer' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=8 *.log_file_name_convert='DBSET','DBTAR' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=871m *.processes=300 *.remote_login_passwordfile='exclusive' *.sga_target=2612m *.standby_file_management='AUTO' DBSET2.thread=2 DBSET1.thread=1 DBSET2.undo_tablespace='UNDOTBS2' DBSET1.undo_tablespace='UNDOTBS1' [oracle@racsetn1 bkp_tar]$ => Set environment varialble for Standby Node-1 [oracle@racsetn1 bkp_tar]$ cat ~/.bash_profile export PATH export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/ora_home/app/oracle export ORACLE_HOME=/ora_home/app/19c/db export ORACLE_SID=DBTAR1 export BASE_PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$BASE_PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib [oracle@racsetn1 bkp_tar]$ => Start Standby database using Pfile in NOMOUNT mode as below. [oracle@racsetn1 dbtar]$ sqlplus "/as sysdba" Connected to an idle instance. SQL> startup nomount pfile='/tmp/bkp_tar/pfile_prod.ora'; ORACLE instance started. Total System Global Area 2751463160 bytes Fixed Size 8900344 bytes Variable Size 637534208 bytes Database Buffers 2097152000 bytes Redo Buffers 7876608 bytes SQL> => Restore Controlfile on standby database and Mount database as below. [oracle@racsetn1 dbtar]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jun 24 17:56:28 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: DBSET (not mounted) RMAN> restore controlfile from '/tmp/bkp_tar/stndby_ctl.ctl'; Starting restore at 24-JUN-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=61 instance=DBTAR1 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=+DATA/DBTAR/CONTROLFILE/control_01.ctl output file name=+FRA/DBTAR/CONTROLFILE/control_02.ctl Finished restore at 24-JUN-22 RMAN> alter database mount; released channel: ORA_DISK_1 Statement processed RMAN> exit Recovery Manager complete. => Check DB Status as below. SQL> select instance_name,name,host_name,open_mode,database_role ,controlfile_type from v$database,v$instance; INSTANCE_NAME NAME HOST_NAME OPEN_MODE DATABASE_ROLE CONTROL ------------------------------------------------------------------------------------------------------------------------------------- DBTAR1 DBSET racsetn1.ace2oracle.lab MOUNTED PHYSICAL STANDBY STANDBY => Catalog Backups which was copied from Primary as below. [oracle@racsetn1 bkp_tar]$ rman target / RMAN> catalog start with '/tmp/bkp_tar'; using target database control file instead of recovery catalog searching for all files that match the pattern /tmp/bkp_tar List of Files Unknown to the Database ===================================== File Name: /tmp/bkp_tar/Primary_bkp_for_stndby_0510sg8s_1_1 File Name: /tmp/bkp_tar/stndby_ctl.ctl Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /tmp/bkp_tar/Primary_bkp_for_stndby_0510sg8s_1_1 File Name: /tmp/bkp_tar/stndby_ctl.ctl RMAN> crosscheck backup of database; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=54 instance=DBTAR1 device type=DISK crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/tmp/bkp_tar/Primary_bkp_for_stndby_0510sg8s_1_1 RECID=6 STAMP=1108233277 Crosschecked 1 objects => Restore and Recover database from Backup. [oracle@racsetn1 bkp_tar]$ rman target / RMAN> run { # allocate a channel to the tape device ALLOCATE CHANNEL d1 DEVICE TYPE disk; # rename the datafiles and online redo logs set newname for datafile 1 to '+DATA'; set newname for datafile 3 to '+DATA'; set newname for datafile 4 to '+DATA'; set newname for datafile 5 to '+DATA'; set newname for datafile 7 to '+DATA'; RESTORE DATABASE; SWITCH DATAFILE ALL; SWITCH TEMPFILE ALL; } 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> allocated channel: d1 channel d1: SID=54 instance=DBTAR1 device type=DISK executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 24-JUN-22 channel d1: starting datafile backup set restore channel d1: specifying datafile(s) to restore from backup set channel d1: restoring datafile 00001 to +DATA channel d1: restoring datafile 00003 to +DATA channel d1: restoring datafile 00004 to +DATA channel d1: restoring datafile 00005 to +DATA channel d1: restoring datafile 00007 to +DATA channel d1: reading from backup piece /tmp/bkp_tar/Primary_bkp_for_stndby_0510sg8s_1_1 channel d1: piece handle=/tmp/bkp_tar/Primary_bkp_for_stndby_0510sg8s_1_1 tag=TAG20220624T174708 channel d1: restored backup piece 1 channel d1: restore complete, elapsed time: 00:00:07 Finished restore at 24-JUN-22 datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=1108233375 file name=+DATA/DBTAR/DATAFILE/system.261.1108233369 datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=1108233375 file name=+DATA/DBTAR/DATAFILE/sysaux.260.1108233369 datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=1108233375 file name=+DATA/DBTAR/DATAFILE/undotbs1.259.1108233369 datafile 5 switched to datafile copy input datafile copy RECID=9 STAMP=1108233375 file name=+DATA/DBTAR/DATAFILE/undotbs2.258.1108233369 datafile 7 switched to datafile copy input datafile copy RECID=10 STAMP=1108233375 file name=+DATA/DBTAR/DATAFILE/users.257.1108233369 released channel: d1 RMAN> REPORT SCHEMA; RMAN-06139: warning: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name DBTAR List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 900 SYSTEM *** +DATA/DBTAR/DATAFILE/system.261.1108233369 3 580 SYSAUX *** +DATA/DBTAR/DATAFILE/sysaux.260.1108233369 4 345 UNDOTBS1 *** +DATA/DBTAR/DATAFILE/undotbs1.259.1108233369 5 25 UNDOTBS2 *** +DATA/DBTAR/DATAFILE/undotbs2.258.1108233369 7 5 USERS *** +DATA/DBTAR/DATAFILE/users.257.1108233369 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 +DATA/MUST_RENAME_THIS_TEMPFILE_1.4294967295.4294967295 RMAN> EXIT Recovery Manager complete. => Create spfile for Standby database as below. [oracle@racsetn1 bkp_tar]$ sqlplus "/as sysdba" SQL> create spfile='+DATA/DBTAR/PARAMETERFILE/spfile_new.ora' from pfile='/tmp/bkp_tar/pfile_prod.ora'; File created. => Create ASM-Password file Standby database as below, Ignore errors. [grid@racsetn1 ~]$ asmcmd ASMCMD> pwcopy --dbuniquename DBTAR -f /tmp/bkp_tar/orapwprim +DATA/DBTAR/PASSWORD/orapwDBTAR PRCD-1120 : The resource for database DBTAR could not be found. PRCR-1001 : Resource ora.dbtar.db does not exist copying /tmp/bkp_tar/orapwprim -> +DATA/DBTAR/PASSWORD/orapwDBTAR ASMCMD-9453: failed to register password file as a CRS resource ASMCMD> exit [grid@racsetn1 ~]$ =>Add databas to Cluster as below from Oracle user [oracle@racsetn1 bkp_tar]$ srvctl add database -db DBTAR -dbname DBSET -oraclehome /ora_home/app/19c/db -pwfile '+DATA/DBTAR/PASSWORD/orapwDBTAR' -spfile '+DATA/DBTAR/PARAMETERFILE/spfile_new.ora' [oracle@racsetn1 bkp_tar]$ srvctl add instance -db DBTAR -i DBTAR1 -n racsetn1 [oracle@racsetn1 bkp_tar]$ srvctl add instance -db DBTAR -i DBTAR2 -n racsetn2 [oracle@racsetn1 bkp_tar]$ srvctl status database -d DBTAR Instance DBTAR1 is not running on node racsetn1 Instance DBTAR2 is not running on node racsetn2 [oracle@racsetn1 bkp_tar]$ srvctl modify database -db DBTAR -role PHYSICAL_STANDBY -startoption mount [oracle@racsetn1 bkp_tar]$ [oracle@racsetn1 bkp_tar]$ srvctl config database -d DBTAR Database unique name: DBTAR Database name: DBSET Oracle home: /ora_home/app/19c/db Oracle user: oracle Spfile: +DATA/DBTAR/PARAMETERFILE/spfile_new.ora Password file: +DATA/DBTAR/PASSWORD/orapwDBTAR Domain: Start options: mount Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: Disk Groups: Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: oinstall OSOPER group: oper Database instances: DBTAR1,DBTAR2 Configured nodes: racsetn1,racsetn2 CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is administrator managed [oracle@racsetn1 bkp_tar]$ => Start Database on Standby Site. [oracle@racsetn1 bkp_tar]$ srvctl start database -d DBTAR [oracle@racsetn1 bkp_tar]$ ps -eaf |grep pmon oracle 1022 1 0 18:52 ? 00:00:00 ora_pmon_DBTAR1 oracle 1588 26014 0 18:52 pts/0 00:00:00 grep --color=auto pmon grid 32731 1 0 15:00 ? 00:00:00 asm_pmon_+ASM1 [oracle@racsetn1 bkp_tar]$ sqlplus "/as sysdba" SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBSET MOUNTED PHYSICAL STANDBY DBSET MOUNTED PHYSICAL STANDBY SQL> exit =>Cleaning and creating Online Redo Logfile & Standby Logfiles & Tempfiles on Standby database. [oracle@racsetn1 bkp_tar]$ sqlplus "/as sysdba" Change Standby_File_Management parameter to MANUAL SQL> alter system set standby_file_management='MANUAL'; System altered. SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBSET MOUNTED PHYSICAL STANDBY DBSET MOUNTED PHYSICAL STANDBY Cleaning and creating Standby Logfiles. SQL>ALTER DATABASE DROP LOGFILE GROUP 5 SQL>ALTER DATABASE DROP LOGFILE GROUP 6; SQL>ALTER DATABASE DROP LOGFILE GROUP 7; SQL> ALTER DATABASE DROP LOGFILE GROUP 8; SQL>ALTER DATABASE DROP LOGFILE GROUP 9; SQL>ALTER DATABASE DROP LOGFILE GROUP 10; SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5('+DATA/DBTAR/STANDBYLOGS/stdby_01.log','+FRA/DBTAR/STANDBYLOGS/stdby_11.log') SIZE 200M; SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6('+DATA/DBTAR/STANDBYLOGS/stdby_02.log','+FRA/DBTAR/STANDBYLOGS/stdby_22.log') SIZE 200M; SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7('+DATA/DBTAR/STANDBYLOGS/stdby_03.log','+FRA/DBTAR/STANDBYLOGS/stdby_33.log') SIZE 200M; SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 8('+DATA/DBTAR/STANDBYLOGS/stdby_04.log','+FRA/DBTAR/STANDBYLOGS/stdby_44.log') SIZE 200M; SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 9('+DATA/DBTAR/STANDBYLOGS/stdby_05.log','+FRA/DBTAR/STANDBYLOGS/stdby_55.log') SIZE 200M; SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 10('+DATA/DBTAR/STANDBYLOGS/stdby_06.log','+FRA/DBTAR/STANDBYLOGS/stdby_66.log') SIZE 200M; Cleaning and creating Online Logfiles. SQL>ALTER DATABASE DROP LOGFILE GROUP 1; SQL>ALTER DATABASE DROP LOGFILE GROUP 2; SQL>ALTER DATABASE DROP LOGFILE GROUP 3; SQL>ALTER DATABASE DROP LOGFILE GROUP 4; SQL>ALTER DATABASE ADD LOGFILE THREAD 1 group 1('+DATA/DBTAR/ONLINELOG/online_01.log','+FRA/DBTAR/ONLINELOG/online_11.log') SIZE 200M; SQL>ALTER DATABASE ADD LOGFILE THREAD 1 group 2('+DATA/DBTAR/ONLINELOG/online_02.log','+FRA/DBTAR/ONLINELOG/online_22.log') SIZE 200M; SQL>ALTER DATABASE ADD LOGFILE THREAD 1 group 3('+DATA/DBTAR/ONLINELOG/online_03.log','+FRA/DBTAR/ONLINELOG/online_33.log') SIZE 200M; SQL>ALTER DATABASE ADD LOGFILE THREAD 2 group 4('+DATA/DBTAR/ONLINELOG/online_04.log','+FRA/DBTAR/ONLINELOG/online_44.log') SIZE 200M; Cleaning and creating Temp Datafiles. SQL> select NAME from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/MUST_RENAME_THIS_TEMPFILE_1.4294967295.4294967295 SQL> ALTER DATABASE RENAME FILE '+DATA/MUST_RENAME_THIS_TEMPFILE_1.4294967295.4294967295' to '+DATA/DBTAR/TEMPFILE/tmpfile_01.dat'; Database altered. Change Standby_File_Management parameter to AUTO SQL> alter system set standby_file_management='AUTO'; System altered. SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBSET MOUNTED PHYSICAL STANDBY DBSET MOUNTED PHYSICAL STANDBY SQL> exit => Rclycle database and start in READ-ONLY mode as below. [oracle@racsetn1 bkp_tar]$ srvctl stop database -d DBTAR [oracle@racsetn1 bkp_tar]$ srvctl start database -db DBTAR -startoption "READ ONLY" [oracle@racsetn1 bkp_tar]$ sqlplus "/as sysdba" SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBSET READ ONLY PHYSICAL STANDBY DBSET READ ONLY PHYSICAL STANDBY => Start MRP process on standby database. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select name,open_mode,database_role from gv$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBSET READ ONLY WITH APPLY PHYSICAL STANDBY DBSET READ ONLY WITH APPLY PHYSICAL STANDBY SQL>
Testing and Analysis of DG Setup
Let we test above built setup and analyze available Alert logfiles as below.
=> Create test table on primary database .
[oracle@racnoden1 admin]$ sqlplus "/as sysdba"
SQL> create table test(ind int);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
1
=> Test if data is available on Standby database.
[oracle@racsetn1 trace]$ sqlplus "/as sysdba"
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
=> Alert Log on Primary Database.
[oracle@racnoden1 trace]$ sqlplus "/as sysdba"
[oracle@racnoden1 trace]$ tail -100f alert_DBSET1.log
2022-06-24T19:51:41.259571+05:30
ALTER SYSTEM SWITCH ALL LOGFILE start (DBSET1)
2022-06-24T19:51:41.290419+05:30
Thread 1 advanced to log sequence 16 (LGWR switch)
Current log# 2 seq# 16 mem# 0: +DATA/DBSET/ONLINELOG/group_2.263.1107118397
Current log# 2 seq# 16 mem# 1: +FRA/DBSET/ONLINELOG/group_2.258.1107118399
2022-06-24T19:51:41.290838+05:30
ALTER SYSTEM SWITCH ALL LOGFILE complete (DBSET1)
2022-06-24T19:51:41.393495+05:30
ARC5 (PID:11220): Archived Log entry 24 added for T-1.S-15 ID 0x2a2f92b4 LAD:1
2022-06-24T19:51:41.723665+05:30
TT03 (PID:6290): SRL selected for T-1.S-16 for LAD:2
=> Alert Log on Standby Database.
As we can see redo data is being written on Standby logfiles on Standby database.
MRP0 (PID:30413): Media Recovery Waiting for T-1.S-16 (in transit)
2022-06-24T19:51:41.818555+05:30
Recovery of Online Redo Log: Thread 1 Group 5 Seq 16 Reading mem 0
Mem# 0: +DATA/DBTAR/STANDBYLOGS/stdby_01.log
Mem# 1: +FRA/DBTAR/STANDBYLOGS/stdby_11.log
2022-06-24T19:51:42.374896+05:30
ARC5 (PID:29647): Archived Log entry 8 added for T-2.S-8 ID 0x2a2f92b4 LAD:1
2022-06-24T19:51:42.701482+05:30
MRP0 (PID:30413): Media Recovery Waiting for T-2.S-9 (in transit)
2022-06-24T19:51:42.705750+05:30
Recovery of Online Redo Log: Thread 2 Group 8 Seq 9 Reading mem 0
Mem# 0: +DATA/DBTAR/STANDBYLOGS/stdby_04.log
Mem# 1: +FRA/DBTAR/STANDBYLOGS/stdby_44.log
=> Check synch status on Primary
SQL> select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 22
2 10
SQL>
SQL> alter system switch all logfile;
System altered.
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 22
2 10
SQL> /
THREAD# MAX(SEQUENCE#)
---------- --------------
1 23
2 11
SQL>
=> Check synch status on Standby
SQL> select name,open_mode,database_role,cdb from gv$database;
NAME OPEN_MODE DATABASE_ROLE CDB
--------- -------------------- ---------------- ---
DBSET READ ONLY PHYSICAL STANDBY NO
DBSET READ ONLY PHYSICAL STANDBY NO
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 22
2 10
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 22 22 0
2 10 10 0
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 23
2 11
SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444