Sunday, February 2, 2025

Standby Database set up || Oracle Data Guard (ODG)

   No comments
  • Introduction: Oracle Data Guard (ODG) is a high-availability, disaster recovery, and data protection feature or solution provided by Oracle Database. It ensures the availability of critical data by
    maintaining one or more synchronized standby databases as replicas of a primary database. These standby databases can be used for disaster recovery, reporting, or backups, minimizing downtime and data loss in the event of unexpected failures. So here we will do Standby database set up in an easy and step by step manner.

  • Prerequisites:

    • o Primary server with Oracle database Software installed and running database.
    o Standby server with Oracle database Software installed.
    o Connectivity between Primary and Standby server.

  • Environment:
    • Server Primary Standby
    Hostname Source Target
    IP 192.168.80.51 192.168.80.111
    OS OEL 9 OEL 9
    SID ORCLDC ORCLDR
    Service Name ORCLDC ORCLDR

  • Now start configuring the ODG:

1. Connectivity Test
From Source to Target:
[oracle@source ~]$ ping -c 3 target.localdomain
PING target.localdomain (192.168.80.111) 56(84) bytes of data.
64 bytes from target.localdomain (192.168.80.111): icmp_seq=1 ttl=64 time=0.678 ms
64 bytes from target.localdomain (192.168.80.111): icmp_seq=2 ttl=64 time=0.456 ms
64 bytes from target.localdomain (192.168.80.111): icmp_seq=3 ttl=64 time=0.924 ms
--- target.localdomain ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2107ms
rtt min/avg/max/mdev = 0.456/0.686/0.924/0.191 ms
From Target to Source:
[oracle@target ~]$ ping -c 3 source.localdomain
PING source.localdomain (192.168.80.51) 56(84) bytes of data.
64 bytes from source.localdomain (192.168.80.51): icmp_seq=1 ttl=64 time=0.690 ms
64 bytes from source.localdomain (192.168.80.51): icmp_seq=2 ttl=64 time=6.61 ms
64 bytes from source.localdomain (192.168.80.51): icmp_seq=3 ttl=64 time=0.315 ms
--- source.localdomain ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2002ms
rtt min/avg/max/mdev = 0.315/2.536/6.605/2.880 ms

2. Primary Database setup
First fetch some details about Primary Database:
[oracle@source ~]$ sqlplus / as sysdba
SQL> def
DEFINE _DATE = "22-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldc" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1924000000" (CHAR)
SQL> set lines 200 pages 1000
col open_mode for a15
col HOST_NAME for a15
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
-------- ---------------- --------------- ------------
ORCL PRIMARY READ WRITE ARCHIVELOG**
SQL> select banner,banner_full from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL>
SQL> col name for a70
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ----------------------------------------------------------------------
1 /u01/app/oracle/oradata/ORCL/system01.dbf
3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
7 /u01/app/oracle/oradata/ORCL/users01.dbf
8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
11 rows selected.
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
SQL> col MEMBER for a40
SQL> select group#,type,MEMBER from v$logfile order by group#;
GROUP# TYPE MEMBER
---------- ------- ----------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log

** If database is in NOARCHIVELOG mode then do below steps:
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=both;
alter system set db_recovery_file_dest_size=50g scope=both;
Shut immediate;
Startup mount;
Alter database archivelog;
Alter database open;

Now let's do changes in Primary Database for ODG:
SQL> SELECT name, force_logging,log_mode FROM v$database;
NAME FORCE_LOGGING LOG_MODE
--------------- --------------------------------------- ------------
ORCL NO ARCHIVELOG
SQL> alter database force logging;
Database altered.
SQL> SELECT name, force_logging,log_mode FROM v$database;
NAME FORCE_LOGGING LOG_MODE
--------------- --------------------------------------- ------------
ORCL YES ARCHIVELOG
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLDC
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCLDC,ORCLDR)';
System altered.
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(ORCLDC,ORCLDR)
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCLDR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDR';
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ORCLDR NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRI
MARY_ROLE) DB_UNIQUE_NAME=ORCL
DR
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=ORCLDR;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> alter system set archive_lag_target =900;
System altered.
Now we need to set DB_FILE_NAME_CONVERT & LOG_FILE_NAME_CONVERT parameter so that datafile and logfile locations will be converted automatically from Primary to Standby and vice-versa.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCLDR','/u01/app/oracle/oradata/ORCL' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCLDR','/u01/app/oracle/oradata/ORCL' SCOPE=SPFILE;
System altered.
Now just take a bounce of Database so that all parameter will get reflects.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 905969664 bytes
Database Buffers 536870912 bytes
Redo Buffers 7598080 bytes
Database mounted.
Database opened.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.arc
SQL>
SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 30
SQL>
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
Now add standby redo logfiles which will be used to apply the changes whenever primary becomes standby. Number of Standby logs should be one more than the number of Online logs.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo01.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo02.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo03.log') SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/ORCL/standby_redo04.log') SIZE 200M;
Database altered.
SQL> select group#,type,MEMBER from v$logfile order by group#;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORCL/redo01.log
2 ONLINE /u01/app/oracle/oradata/ORCL/redo02.log
3 ONLINE /u01/app/oracle/oradata/ORCL/redo03.log
4 STANDBY /u01/app/oracle/oradata/ORCL/standby_redo01.log
5 STANDBY /u01/app/oracle/oradata/ORCL/standby_redo02.log
6 STANDBY /u01/app/oracle/oradata/ORCL/standby_redo03.log
7 STANDBY /u01/app/oracle/oradata/ORCL/standby_redo04.log
7 rows selected.
3. Primary Service setup
[oracle@source ~]$ cd $ORACLE_HOME/network/admin
[oracle@source admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLDC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = source.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldc)
)
)
ORCLDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldr)
)
)
4.Primary Database and files backup
Create pfile:
SQL> create pfile='/u01/app/oracle/rmandc/initorcldc.ora' from spfile;
Copy password file:
[oracle@source rmandc]$ cd $ORACLE_HOME/dbs
[oracle@source dbs]$ cp orapworcldc /u01/app/oracle/rmandc/
Take full database backup using RMAN:
RMAN> run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup as compressed backupset database format '/u01/app/oracle/rmandc/Fullback_%T_%U';
backup as compressed backupset archivelog all format '/u01/app/oracle/rmandc/Archive_%T_%U';
backup current controlfile for standby format '/u01/app/oracle/rmandc/StbyControlback_%T_%U';
release channel ch1;
release channel ch2;
}
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=299 device type=DISK
allocated channel: ch2
channel ch2: SID=55 device type=DISK
Starting backup at 22-JAN-25
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ch2: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_353fsduj_101_1_1 tag=TAG20250122T113235 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:36
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
channel ch2: starting piece 1 at 22-JAN-25
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_343fsduj_100_1_1 tag=TAG20250122T113235 comment=NONE
channel ch1: backup set complete, elapsed time: 00:02:54
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_363fse1l_102_1_1 tag=TAG20250122T113235 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:17
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
channel ch2: starting piece 1 at 22-JAN-25
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_373fse42_103_1_1 tag=TAG20250122T113235 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:45
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_383fse42_104_1_1 tag=TAG20250122T113235 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:01
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Fullback_20250122_393fse5g_105_1_1 tag=TAG20250122T113235 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-JAN-25
Starting backup at 22-JAN-25
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=1 STAMP=1191028331
input archived log thread=1 sequence=15 RECID=2 STAMP=1191064037
channel ch1: starting piece 1 at 22-JAN-25
channel ch2: starting compressed archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=3 STAMP=1191064563
input archived log thread=1 sequence=17 RECID=4 STAMP=1191065457
input archived log thread=1 sequence=18 RECID=5 STAMP=1191065813
channel ch2: starting piece 1 at 22-JAN-25
channel ch2: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Archive_20250122_3b3fse6m_107_1_1 tag=TAG20250122T113653 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:15
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/Archive_20250122_3a3fse6m_106_1_1 tag=TAG20250122T113653 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:25
Finished backup at 22-JAN-25
Starting backup at 22-JAN-25
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including standby control file in backup set
channel ch1: starting piece 1 at 22-JAN-25
channel ch1: finished piece 1 at 22-JAN-25
piece handle=/u01/app/oracle/rmandc/StbyControlback_20250122_3c3fse7h_108_1_1 tag=TAG20250122T113721 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-JAN-25
Starting Control File and SPFILE Autobackup at 22-JAN-25
piece handle=/u01/app/oracle/fast_recovery_area/ORCLDC/autobackup/2025_01_22/o1_mf_s_1191065849_ms12s1os_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-JAN-25
released channel: ch1
released channel: ch2
RMAN> exit
5.Transfer backup and other files from Primary to Standby
[oracle@source dbs]$ cd /u01/app/oracle/rmandc/
[oracle@source rmandc]$ ls -lrth
total 1.3G
-rw-r--r--. 1 oracle oinstall 1.6K Jan 22 11:28 initorcldc.ora
-rw-r-----. 1 oracle oinstall 94M Jan 22 11:33 Fullback_20250122_353fsduj_101_1_1
-rw-r-----. 1 oracle oinstall 409M Jan 22 11:35 Fullback_20250122_343fsduj_100_1_1
-rw-r-----. 1 oracle oinstall 237M Jan 22 11:35 Fullback_20250122_363fse1l_102_1_1
-rw-r-----. 1 oracle oinstall 134M Jan 22 11:36 Fullback_20250122_373fse42_103_1_1
-rw-r-----. 1 oracle oinstall 227M Jan 22 11:36 Fullback_20250122_383fse42_104_1_1
-rw-r-----. 1 oracle oinstall 64M Jan 22 11:36 Fullback_20250122_393fse5g_105_1_1
-rw-r-----. 1 oracle oinstall 49M Jan 22 11:37 Archive_20250122_3b3fse6m_107_1_1
-rw-r-----. 1 oracle oinstall 69M Jan 22 11:37 Archive_20250122_3a3fse6m_106_1_1
-rw-r-----. 1 oracle oinstall 18M Jan 22 11:37 StbyControlback_20250122_3c3fse7h_108_1_1
-rw-r-----. 1 oracle oinstall 2.0K Jan 22 11:39 orapworcldc
[oracle@source rmandc]$ scp * oracle@target:/u01/app/oracle/rmandr
The authenticity of host 'target (192.168.80.111)' can't be established.
ED25519 key fingerprint is SHA256:u1O6svnS8kY1i6Mv88TDIlYsvqDyISi2Uz3ZBc8kKfY.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'target' (ED25519) to the list of known hosts.
oracle@target's password:
Archive_20250122_3a3fse6m_106_1_1      100% 68MB 58.5MB/s 00:01
Archive_20250122_3b3fse6m_107_1_1      100% 49MB 52.0MB/s 00:00
Fullback_20250122_343fsduj_100_1_1      100% 409MB 24.7MB/s 00:16
Fullback_20250122_353fsduj_101_1_1      100% 93MB 20.9MB/s 00:04
Fullback_20250122_363fse1l_102_1_1      100% 237MB 25.4MB/s 00:09
Fullback_20250122_373fse42_103_1_1      100% 134MB 21.0MB/s 00:06
Fullback_20250122_383fse42_104_1_1      100% 226MB 25.7MB/s 00:08
Fullback_20250122_393fse5g_105_1_1      100% 63MB 33.5MB/s 00:01
initorcldc.ora      100% 1632 52.9KB/s 00:00
orapworcldc      100% 2048 105.3KB/s 00:00
StbyControlback_20250122_3c3fse7h_108_1_1      100% 18MB 57.8MB/s 00:00
6.Standby Database setup
Do the changes in Primary pfile and create pfile initorcldr.ora for Standby. Below parameters have been modified. Sample file is shown further below:
*.audit_file_dest='/u01/app/oracle/admin/orcldr/adump'
*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl'
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.db_unique_name='ORCLDR'
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.fal_server='ORCLDC'
*.log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'
[oracle@target rmandr]$ cat initorcldr.ora
orcldr.__data_transfer_cache_size=0
orcldr.__db_cache_size=486539264
orcldr.__inmemory_ext_roarea=0
orcldr.__inmemory_ext_rwarea=0
orcldr.__java_pool_size=16777216
orcldr.__large_pool_size=16777216
orcldr.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcldr.__pga_aggregate_target=587202560
orcldr.__sga_target=872415232
orcldr.__shared_io_pool_size=50331648
orcldr.__shared_pool_size=285212672
orcldr.__streams_pool_size=0
orcldr.__unified_pga_pool_size=0
*.archive_lag_target=900
*.audit_file_dest='/u01/app/oracle/admin/orcldr/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCLDR/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8192m
*.db_unique_name='ORCLDR'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldcXDB)'
*.enable_pluggable_database=true
*.fal_server='ORCLDC'
*.log_archive_config='DG_CONFIG=(ORCLDC,ORCLDR)'
*.log_archive_dest_2='SERVICE=ORCLDC NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLDC'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/ORCLDR'
*.memory_target=1384m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Create required directories:
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/admin/orcldr/adump
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/oradata/ORCLDR/
[oracle@target rmandr]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDR/
Copy password file in $ORACLE_HOME/dbs :
[oracle@target rmandr]$ cp orapworcldc $ORACLE_HOME/dbs/orapworcldr
[oracle@target rmandr]$ ls -lrth $ORACLE_HOME/dbs/orapworcldr
-rw-r-----. 1 oracle oinstall 2.0K Jan 22 12:02 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcldr
Create listener using NETCA. Start it. Also add TNS service entries for Primary & Standby in tnsnames.ora file.

Refer Create listener using NETCA

[oracle@target ~]$ lsnrctl start
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/target/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=target.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=target.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 22-JAN-2025 12:09:54
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/target/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=target.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@target ~]$
[oracle@target admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLDC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = source.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldc)
)
)
ORCLDR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcldr)
)
)
7.Startup Standby instance and restore the backup
Startup in nomount:
[oracle@target ~]$ . oraenv
ORACLE_SID = [orcl] ? orcldr
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 22 12:13:03 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/rmandr/initorcldr.ora';
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 905969664 bytes
Database Buffers 536870912 bytes
Redo Buffers 7598080 bytes
SQL>
SQL> def
DEFINE _DATE = "22-JAN-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcldr" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1924000000" (CHAR)
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@target ~]$
Restore the controlfile from backup. Then mount the standby database instance:
[oracle@target ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 22 12:14:09 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/rmandr/StbyControlback_20250122_3c3fse7h_108_1_1';
Starting restore at 22-JAN-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCLDR/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl
Finished restore at 22-JAN-25
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
Login into database and verify:
[oracle@target ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 22 12:15:19 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE from v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE
--------- ---------------- -------------------- ------------
ORCL PHYSICAL STANDBY MOUNTED ARCHIVELOG
SQL> show parameter db_unique_name
NAME TYPE VALUE
---------------- ----------- ------------
db_unique_name string ORCLDR
Now catalog the backup files and start the database restoration:
[oracle@target rmandr]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 22 12:18:34 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1718803653, not open)
RMAN>
RMAN> catalog start with '/u01/app/oracle/rmandr';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/rmandr
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/rmandr/Archive_20250122_3a3fse6m_106_1_1
File Name: /u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_343fsduj_100_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_353fsduj_101_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_363fse1l_102_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_373fse42_103_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_383fse42_104_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_393fse5g_105_1_1
File Name: /u01/app/oracle/rmandr/StbyControlback_20250122_3c3fse7h_108_1_1
File Name: /u01/app/oracle/rmandr/initorcldr.ora
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/rmandr/Archive_20250122_3a3fse6m_106_1_1
File Name: /u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_343fsduj_100_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_353fsduj_101_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_363fse1l_102_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_373fse42_103_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_383fse42_104_1_1
File Name: /u01/app/oracle/rmandr/Fullback_20250122_393fse5g_105_1_1
File Name: /u01/app/oracle/rmandr/StbyControlback_20250122_3c3fse7h_108_1_1
List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/rmandr/initorcldr.ora
RMAN-07517: Reason: The file header is corrupted
RMAN> run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel ch1;
release channel ch2;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
allocated channel: ch1
channel ch1: SID=41 device type=DISK
allocated channel: ch2
channel ch2: SID=276 device type=DISK
Starting restore at 22-JAN-25
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel ch1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
channel ch1: reading from backup piece /u01/app/oracle/rmandr/Fullback_20250122_343fsduj_100_1_1
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel ch2: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel ch2: reading from backup piece /u01/app/oracle/rmandr/Fullback_20250122_353fsduj_101_1_1
Finished restore at 22-JAN-25
Starting recover at 22-JAN-25
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_19_ms14zky5_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_20_ms14zl0q_.arc
archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_21_ms15d21o_.arc
channel ch1: starting archived log restore to default destination
channel ch1: restoring archived log
archived log thread=1 sequence=18
channel ch1: reading from backup piece /u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1
channel ch1: piece handle=/u01/app/oracle/rmandr/Archive_20250122_3b3fse6m_107_1_1 tag=TAG20250122T113653
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_01_22/o1_mf_1_18_ms160js6_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:05
Finished recover at 22-JAN-25
released channel: ch1
released channel: ch2
RMAN>
RMAN> exit
8. Now start real time apply
Start log shipment on Primary:
SQL> alter system set log_archive_dest_state_2= ENABLE scope=both;
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL> set lines 200 pages 1000
col DEST_NAME for a20
col DESTINATION for a20
col error for a30SQL> SQL> SQL>
SQL> select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where DEST_NAME='LOG_ARCHIVE_DEST_2';
DEST_ID DEST_NAME STATUS ERROR
---------- -------------------- --------- ------------------------------
2 LOG_ARCHIVE_DEST_2 VALID
SQL> select DEST_ID,DEST_NAME,DESTINATION,STATUS,ERROR from v$archive_dest_status where status not in ('INVALID','INACTIVE');
DEST_ID DEST_NAME DESTINATION STATUS ERROR
---------- -------------------- -------------------- --------- ------------------------------
1 LOG_ARCHIVE_DEST_1 /u01/app/oracle/prod VALID
uct/19.0.0/dbhome_1/
dbs/arch
2 LOG_ARCHIVE_DEST_2 ORCLDR VALID
SQL>
Start Apply At Standby:
SQL> alter database recover managed standby database disconnect from session;
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';
PROCESS STATUS THREAD# SEQUENCE# BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0 APPLYING_LOG 1 26 903
Now do some log switches on Primary and check the sync.
On Primary:
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set lines 200 pages 300
alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
select d.db_unique_name, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
SQL> FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name from v$database) d where a.thread#=b.thread#;
Session altered.
DB_UNIQUE_NAME THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------- ---------- ----------- -------------------- ----------
ORCLDC 1 29 27 22-JAN-2025 12:57:12 2
On Standby:
SQL>select d.db_unique_name, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from v$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR 1 29 29 0 22-JAN-2025 12:57:18 3.78 227
9.Create tablespace on Primary and check on Standby
On Primary:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> create tablespace test datafile '/u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf' size 1g;
Tablespace created.
On Standby :
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 ORCLPDB MOUNTED
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
/u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
SQL> select name from v$tablespace;
NAME
----------------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST
6 rows selected.


Thanks for visiting!!

0 comments:

Post a Comment