- 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. We will use RESTORE from PRIMARY SERVICE method here which is suitable for small database size. Also for this a good network bandwith is required between Primary & Standby.
- Prerequisites:
- Environment:
- Now start configuring the ODG:
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.
| 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 |
==> 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
First fetch some details about Primary Database:
[oracle@source ~]$ sqlplus / as sysdba
SQL> def
DEFINE _DATE = "18-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> 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
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;
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,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCLDR
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.
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.
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> 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> SHOW PARAMETER LOG_ARCHIVE_MAX_PROCESSES;
NAME TYPE VALUE
-------------------------- -------- ------------------------------
log_archive_max_processes integer 30
SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
NAME TYPE VALUE
--------------------------- -------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
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.
[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)
)
)
Create pfile:
SQL> create pfile='/u01/app/oracle/rmandc/initorcldc.ora' from spfile;
[oracle@source rmandc]$ cd $ORACLE_HOME/dbs
[oracle@source dbs]$ cp orapworcldc /u01/app/oracle/rmandc/
[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 18 11:28 initorcldc.ora
-rw-r-----. 1 oracle oinstall 2.0K Jan 18 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:
initorcldc.ora 100% 1632 52.9KB/s 00:00
orapworcldc 100% 2048 105.3KB/s 00:00
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'
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'
[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/
[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 18 12:02 /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcldr
Refer Create listener using NETCA
[oracle@target ~]$
[oracle@target ~]$ cd $ORACLE_HOME/network/admin
[oracle@target admin]$
[oracle@target admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = target.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = orcldr)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = orcldr)
)
)
[oracle@target admin]$
[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)
)
)
[oracle@target admin]$ 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 18-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)))
Services Summary...
Service "ORCLDR" has 1 instance(s).
Instance "orcldr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[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 18 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 = "18-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 ~]$
[oracle@target ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 18 11:28:39 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>
RMAN> restore standby controlfile from service 'orcldc';
restore standby controlfile from service 'orcldc';
Starting restore at 18-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: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
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 18-JAN-25
RMAN>
RMAN> alter database mount;
alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
run
{
restore database from service 'orcldc';
}
RMAN> run
{
restore database from service 'orcldc';
}run
2> {
3> restore database from service 'orcldc';
4>
}
Starting restore at 18-JAN-25
Starting implicit crosscheck backup at 18-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
Crosschecked 27 objects
Finished implicit crosscheck backup at 18-JAN-25
Starting implicit crosscheck copy at 18-JAN-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JAN-25
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18-JAN-25
RMAN>
RMAN> report schema;
report schema;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1320 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
3 950 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
5 650 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
6 450 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
9 660 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
10 520 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@target ~]$
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
SQL> col DEST_NAME for a20
SQL> col DESTINATION for a20
SQL> col error for a30
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/product/19.0.0/dbhome_1/dbs/arch VALID
2 LOG_ARCHIVE_DEST_2 ORCLDR VALID
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
==> 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
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 18-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 18-JAN-2025 12:57:18 3.78 227
==> 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 APPDATA datafile '/u01/app/oracle/oradata/ORCL/orclpdb/appdata01.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/appdata01.dbf
SQL> select name from v$tablespace;
NAME
----------------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
APPDATA
6 rows selected.
Thanks for visiting!!
0 comments:
Post a Comment