How to build ACTIVE STANDBY DATABASE using RMAN DUPLICATE command in ORACLE DATABASE 19c:-
Database duplication copies the target database over the network to the auxiliary destination and then creates the duplicate database.
You do not need pre-existing RMAN backups and copies.
Data Guard is the name for Oracle’s standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 19c active standby setup using RMAN duplicate method posted here.
Some key points before proceeding with the ACTIVE STANDBY setup.
- Primary database should be in archivelog mode.
- Initialization parameter “db_name” should be same on both primary and standby database.
- Initialization parameter “db_unique_name” should be different on primary and standby databases.
1. Checking Primary database name & locations.
************************* Primary Side ************************
[oracle@rac01p ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 21 03:09:15 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORADB READ WRITE
SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING LOG_MODE
--------------------- ------------
NO ARCHIVELOG ----------------- DB should be archive log mode
SQL> alter database force logging; -------------- Enable force logging
Database altered.
SQL>
SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING LOG_MODE
--------------------------------------- ------------
YES ARCHIVELOG
SQL>
SQL>
SQL> SELECT GROUP#,BYTES FROM V$LOG;
GROUP# BYTES
---------- ----------
1 52428800
2 52428800
set pages 200
set lines 200
col MEMBER for a50
select lf.group#,l_type.log_type as type, lf.member
from v$logfile lf
join (
select group#,'ORL' as log_type from v$log
union
select group#,'SRL' as log_type from v$standby_log) l_type
on lf.group#=l_type.group#
order by lf.group#;
GROUP# TYPE MEMBER
---------- --------- --------------------------------------------------
1 ORL /ORACLE/ORADB/base/log/redo01_1.redo
1 ORL /ORACLE/ORADB/ext/mirrorlog/redo01_2.redo
2 ORL /ORACLE/ORADB/base/log/redo02_1.redo
2 ORL /ORACLE/ORADB/ext/mirrorlog/redo02_2.redo
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;
Create standby REDO LOGS :
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/ORACLE/ORADB/base/log/redo03_1.log','/ORACLE/ORADB/ext/mirrorlog/redo03_2.log') SIZE 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 4 ('/ORACLE/ORADB/base/log/redo04_1.log','/ORACLE/ORADB/ext/mirrorlog/redo04_2.log') SIZE 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 5 ('/ORACLE/ORADB/base/log/redo05_1.log','/ORACLE/ORADB/ext/mirrorlog/redo05_2.log') SIZE 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 6 ('/ORACLE/ORADB/base/log/redo06_1.log','/ORACLE/ORADB/ext/mirrorlog/redo06_2.log') SIZE 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 7 ('/ORACLE/ORADB/base/log/redo07_1.log','/ORACLE/ORADB/ext/mirrorlog/redo07_2.log') SIZE 100M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 8 ('/ORACLE/ORADB/base/log/redo08_1.log','/ORACLE/ORADB/ext/mirrorlog/redo08_2.log') SIZE 100M;
Database altered.
On both sides and add the logfiles as above:
alter database add standby logfile group 4;
alter database add standby logfile group 5;
alter database add standby logfile group 6;
alter database add standby logfile group 7;
alter database add standby logfile group 8;
set lines 200 pages 200;
col member for a45;
select lf.group#,l_type.log_type as type, lf.member
from v$logfile lf
join (
select group#,'ORL' as log_type from v$log
union
select group#,'SRL' as log_type from v$standby_log) l_type
on lf.group#=l_type.group#
order by lf.group#;
GROUP# TYPE MEMBER---------- --------- --------------------------------------------------
1 ORL /ORACLE/ORADB/base/log/redo01_1.redo
1 ORL /ORACLE/ORADB/ext/mirrorlog/redo01_2.redo
2 ORL /ORACLE/ORADB/base/log/redo02_1.redo
2 ORL /ORACLE/ORADB/ext/mirrorlog/redo02_2.redo
3 ORL /ORACLE/ORADB/base/log/redo03_1.log
3 ORL /ORACLE/ORADB/ext/mirrorlog/redo03_2.log
4 SRL /ORACLE/ORADB/base/log/redo04_1.log
4 SRL /ORACLE/ORADB/ext/mirrorlog/redo04_2.log
5 SRL /ORACLE/ORADB/base/log/redo05_1.log
5 SRL /ORACLE/ORADB/ext/mirrorlog/redo05_2.log
6 SRL /ORACLE/ORADB/base/log/redo06_1.log
6 SRL /ORACLE/ORADB/ext/mirrorlog/redo06_2.log
7 SRL /ORACLE/ORADB/base/log/redo07_1.log
7 SRL /ORACLE/ORADB/ext/mirrorlog/redo07_2.log
14 rows selected.
SQL> SELECT GROUP#,type,member,STATUS FROM V$logfile where type='STANDBY';
GROUP# TYPE MEMBER STATUS
---------- ----------- -------------------------------------------------- -----------
4 STANDBY /ORACLE/ORADB/base/log/redo04_1.log
4 STANDBY /ORACLE/ORADB/ext/mirrorlog/redo04_2.log
5 STANDBY /ORACLE/ORADB/base/log/redo05_1.log
5 STANDBY /ORACLE/ORADB/ext/mirrorlog/redo05_2.log
6 STANDBY /ORACLE/ORADB/base/log/redo06_1.log
6 STANDBY /ORACLE/ORADB/ext/mirrorlog/redo06_2.log
7 STANDBY /ORACLE/ORADB/base/log/redo07_1.log
7 STANDBY /ORACLE/ORADB/ext/mirrorlog/redo07_2.log
8 rows selected.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
System altered.
SQL> ALTER SYSTEM SET db_file_name_convert='/ORACLE/ORADB/base','/ORACLE/DBORA02/base','/ORACLE/ORADB/ext','/ORACLE/DBORA02/ext' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET log_file_name_convert='/ORACLE/ORADB/base/log','/ORACLE/DBORA02/base/log','/ORACLE/ORADB/ext/mirrorlog','/ORACLE/DBORA02/ext/mirrorlog' SCOPE=SPFILE;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@rac01p ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 21 03:30:22 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 687865856 bytes
Database Buffers 1442840576 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
Create the LISTENER and TNS file Primary side ****************************************************
[oracle@rac01p admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.11)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ASG01P_DGMGRL.samik.com)
(SID_NAME = ASG01P)
(ORACLE_HOME = /applications/oracle/19.3.0.0)
)
(SID_DESC =
(SID_NAME = ASG01PDG)
(ORACLE_HOME = /applications/oracle/19.3.0.0)
)
)
[oracle@rac01p admin]$
[oracle@rac01p admin]$
[oracle@rac01p admin]$ cat tnsnames.ora
ASG01P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ASG01P)
)
)
ASG01PDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ASG01PDG)
)
)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
oracle@rac01p admin]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:41:03
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.1.11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 01-DEC-2020 12:33:38
Uptime 0 days 2 hr. 7 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/19.3.0.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac01p/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.1.11)(PORT=1521)))
Services Summary...
Service "ASG01P" has 2 instance(s).
Instance "ASG01P", status UNKNOWN, has 1 handler(s) for this service...
Instance "ASG01P", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac01p admin]$ tnsping ASG01PDG
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 13:48:31
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 = 172.20.1.140)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01PDG)))
TNS-12543: TNS:destination host unreachable
[oracle@rac02p ~]$ tnsping ASG01P
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:31:25
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 = 172.20.1.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01P)))
TNS-12543: TNS:destination host unreachable
SOLUTION:
https://rameshoradba.blogspot.com/2017/05/tns-12543-tnsdestination-host.html
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
oracle@rac01p admin]$ tnsping ASG01P
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:36:54
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 = 172.20.1.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01P)))
OK (0 msec)
[oracle@rac01p admin]$
[oracle@rac01p admin]$ tnsping ASG01PDG
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:36:58
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 = 172.20.1.140)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01PDG)))
OK (20 msec)
Create the LISTENER and TNS file STANDBY SIDE ******************************************************
[oracle@rac02p admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.140)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ASG01PDG_DGMGRL.samik.com)
(SID_NAME = ASG01PDG)
(ORACLE_HOME = /u01/app/oracle/19.3.0.0)
)
(SID_DESC =
(SID_NAME = ASG01P)
(ORACLE_HOME = /u01/app/oracle/19.3.0.0)
)
)
[oracle@rac02p admin]$ cat tnsnames.ora
ASG01PDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ASG01PDG)
)
)
ASG01P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ASG01P)
)
)
[oracle@rac02p admin]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:41:42
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.1.140)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 01-DEC-2020 12:38:45
Uptime 0 days 2 hr. 2 min. 56 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/19.3.0.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac02p/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.20.1.140)(PORT=1521)))
Services Summary...
Service "ASG01PDG" has 1 instance(s).
Instance "ASG01PDG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac02p admin]$ tnsping ASG01PDG
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:38:29
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 = 172.20.1.140)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01PDG)))
OK (0 msec)
[oracle@rac02p admin]$
[oracle@rac02p admin]$ tnsping ASG01P
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-DEC-2020 14:38:32
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 = 172.20.1.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01P)))
OK (1020 msec)
[oracle@rac01p dbs]$ scp -r orapwASG01P oracle@172.20.1.140:/u01/app/oracle/19.3.0.0/dbs
The authenticity of host '172.20.1.140 (172.20.1.140)' can't be established.
ECDSA key fingerprint is SHA256:8/fBA4J5tatoaMH0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '172.20.1.140' (ECDSA) to the list of known hosts.
oracle@172.20.1.140's password:
orapwASG01P 100% 6144 650.7KB/s 00:00
[oracle@rac01p dbs]$
col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a18;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- ---------------
ASG01P rac01p OPEN ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PRIMARY
[oracle@rac02p dbs]$ scp -r orapwASG01PDG oracle@172.20.1.11:/u01/app/oracle/19.3.0.0/dbs
The authenticity of host '172.20.1.11 (172.20.1.11)' can't be established.
ECDSA key fingerprint is SHA256:vCXzkCY/twB/Y9EBKeWod7jyhQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '172.20.1.11' (ECDSA) to the list of known hosts.
oracle@172.20.1.11's password:
orapwASG01PDG 100% 6144 668.7KB/s 00:00
[oracle@rac02p dbs]$
*********Start the Standby DB in nomount*****************
[oracle@rac02p dbs]$ cat initASG01PDG.ora
*.audit_file_dest='/ORACLE/DBORA02/audit'
*.audit_trail='DB'
*.compatible='19.0.0'
*.control_file_record_keep_time=38
*.control_files='/ORACLE/DBORA02/base/control/control01.ctl','/ORACLE/DBORA02/ext/mirrorcontrol/control02.ctl'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/ORACLE/DBORA02/base','/ORACLE/DBORA02/base','/ORACLE/DBORA02/ext','/ORACLE/DBORA02/ext'
*.db_name='ASG01P'
*.db_unique_name='ASG01PDG'
*.diagnostic_dest='/ORACLE/DBORA02'
*.job_queue_processes=10
*.log_archive_dest_1='location=/ORACLE/DBORA02/archive'
*.log_archive_format='arc%t_%s_%r.arc'
*.log_file_name_convert='/ORACLE/DBORA02/base/log','/ORACLE/DBORA02/base/log','/ORACLE/DBORA02/ext/mirrorlog','/ORACLE/DBORA02/ext/mirrorlog'
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=780
*.sga_max_size=5G
*.sga_target=5G
*.standby_file_management='AUTO'
[oracle@rac02p dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 1 15:00:26 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5368708176 bytes
Fixed Size 8907856 bytes
Variable Size 956301312 bytes
Database Buffers 4395630592 bytes
Redo Buffers 7868416 bytes
SQL>
STANDBY SIDE :
[oracle@rac02p ~]$ rman target sys/sys123@ASG01P auxiliary sys/sys123@ASG01PDG
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Dec 1 15:08:25 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ASG01P (DBID=1408767655)
connected to auxiliary database: ASG01P (not mounted)
RMAN>
RMAN>
run
{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'ASG01P','ASG01PDG'
set db_name='ASG01P'
set db_unique_name='ASG01PDG'
set db_file_name_convert='/ORACLE/ORADB/base','/ORACLE/DBORA02/base','/ORACLE/ORADB/ext','/ORACLE/DBORA02/ext'
set log_file_name_convert='/ORACLE/ORADB/base/log','/ORACLE/DBORA02/base/log','/ORACLE/ORADB/ext/mirrorlog','/ORACLE/DBORA02/ext/mirrorlog'
set control_files='/ORACLE/DBORA02/ext/control/control01.ctl','/ORACLE/DBORA02/base/log/mirrorcontrol/control02.ctl'
set log_archive_max_processes='5'
set log_archive_dest_1='LOCATION=/ORACLE/DBORA02/archive'
set log_archive_dest_state_1='ENABLE'
set diagnostic_dest='/ORACLE/DBORA02'
set core_dump_dest='/ORACLE/DBORA02'
set audit_file_dest='/ORACLE/DBORA02/audit'
set standby_file_management='AUTO'
set compatible='19.0.0'
nofilenamecheck;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
release channel t6;
}
Starting Duplicate Db at 18.12.20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=657 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=131 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=268 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/19.3.0.0/dbs/orapwASG01PDG' ;
restore clone from service 'ASG01P' spfile to
'/u01/app/oracle/19.3.0.0/dbs/spfileASG01PDG.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/19.3.0.0/dbs/spfileASG01PDG.ora''";
}
executing Memory Script
Starting backup at 18.12.20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=141 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=274 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=405 device type=DISK
Finished backup at 18.12.20
Starting restore at 18.12.20
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ASG01P
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/19.3.0.0/dbs/spfileASG01PDG.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 18.12.20
sql statement: alter system set spfile= ''/u01/app/oracle/19.3.0.0/dbs/spfileASG01PDG.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''ASG01P'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''ASG01PDG'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/ORACLE/ORADB/base'', ''/ORACLE/DBORA02/base'', ''/ORACLE/ORADB/ext'', ''/ORACLE/DBORA02/ext'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/ORACLE/ORADB/base/log'', ''/ORACLE/DBORA02/base/log'', ''/ORACLE/ORADB/ext/mirrorlog'', ''/ORACLE/DBORA02/ext/mirrorlog'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/ORACLE/DBORA02/ext/control/control01.ctl'', ''/ORACLE/DBORA02/base/log/mirrorcontrol/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/ORACLE/DBORA02/archive'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_state_1 =
''ENABLE'' comment=
'''' scope=spfile";
sql clone "alter system set diagnostic_dest =
''/ORACLE/DBORA02'' comment=
'''' scope=spfile";
sql clone "alter system set core_dump_dest =
''/ORACLE/DBORA02'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/ORACLE/DBORA02/audit'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set compatible =
''19.0.0'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''ASG01P'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''ASG01PDG'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/ORACLE/ORADB/base'', ''/ORACLE/DBORA02/base'', ''/ORACLE/ORADB/ext'', ''/ORACLE/DBORA02/ext'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/ORACLE/ORADB/base/log'', ''/ORACLE/DBORA02/base/log'', ''/ORACLE/ORADB/ext/mirrorlog'', ''/ORACLE/DBORA02/ext/mirrorlog'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/ORACLE/DBORA02/ext/control/control01.ctl'', ''/ORACLE/DBORA02/base/log/mirrorcontrol/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/ORACLE/DBORA02/archive'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_state_1 = ''ENABLE'' comment= '''' scope=spfile
sql statement: alter system set diagnostic_dest = ''/ORACLE/DBORA02'' comment= '''' scope=spfile
sql statement: alter system set core_dump_dest = ''/ORACLE/DBORA02'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/ORACLE/DBORA02/audit'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''19.0.0'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 5368708176 bytes
Fixed Size 8907856 bytes
Variable Size 956301312 bytes
Database Buffers 4395630592 bytes
Redo Buffers 7868416 bytes
contents of Memory Script:
{
restore clone from service 'ASG01P' standby controlfile;
}
executing Memory Script
Starting restore at 18.12.20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=262 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=131 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=268 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ASG01P
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/ORACLE/DBORA02/ext/control/control01.ctl
output file name=/ORACLE/DBORA02/base/log/mirrorcontrol/control02.ctl
Finished restore at 18.12.20
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/ORACLE/DBORA02/ext/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/ORACLE/DBORA02/base/system01.dbf";
set newname for datafile 2 to
"/ORACLE/DBORA02/base/sysaux01.dbf";
set newname for datafile 3 to
"/ORACLE/DBORA02/ext/undotbs01.dbf";
set newname for datafile 4 to
"/ORACLE/DBORA02/base/users01.dbf";
restore
from nonsparse from service
'ASG01P' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /ORACLE/DBORA02/ext/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 18.12.20
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ASG01P
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /ORACLE/DBORA02/base/system01.dbf
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: using network backup set from service ASG01P
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00002 to /ORACLE/DBORA02/base/sysaux01.dbf
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: using network backup set from service ASG01P
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /ORACLE/DBORA02/ext/undotbs01.dbf
channel ORA_AUX_DISK_4: starting datafile backup set restore
channel ORA_AUX_DISK_4: using network backup set from service ASG01P
channel ORA_AUX_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_4: restoring datafile 00004 to /ORACLE/DBORA02/base/users01.dbf
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_4: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:06
Finished restore at 18.12.20
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1059480139 file name=/ORACLE/DBORA02/base/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=1059480139 file name=/ORACLE/DBORA02/base/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=1059480139 file name=/ORACLE/DBORA02/ext/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=1059480139 file name=/ORACLE/DBORA02/base/users01.dbf
Finished Duplicate Db at 18.12.20
RMAN>
[oracle@rac02p ~]$ ps -ef | grep pmon
oracle 127086 1 0 15:25 ? 00:00:00 ora_pmon_ASG01PDG
oracle 127327 127296 0 15:28 pts/0 00:00:00 grep --color=auto pmon
[oracle@rac02p ~]$
[oracle@rac02p ~]$ . oraenv
ORACLE_SID = [ASG01PDG] ?
The Oracle base remains unchanged with value /u01/app/oracle
After cloning : on both sides
GLOBAL_DBNAME = db_unique_name_DGMGRL.db_domain
ASG01P_DGMGRL.samik.com
ASG01PDG_DGMGRL.samik.com
[oracle@rac02p ~]$ cat /u01/app/oracle/19.3.0.0/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.140)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ASG01P_DGMGRL.samik.com)
(SID_NAME = ASG01P)
(ORACLE_HOME = /u01/app/oracle/19.3.0.0)
)
(SID_DESC =
(SID_NAME = ASG01PDG)
(ORACLE_HOME = /u01/app/oracle/19.3.0.0)
)
)
[oracle@rac01p admin]$ cat tnsnames.ora
ASG01P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ASG01P_DGMGRL.samik.com)
#(SERVICE_NAME = ASG01P)
(UR=A)
)
)
ASG01PDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ASG01PDG_DGMGRL.samik.com)
#(SERVICE_NAME = ASG01PDG)
(UR=A)
)
)
ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.11)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01P_DGMGRL.samik.com)))';
alter system register;
ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.1.140)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ASG01PDG_DGMGRL.samik.com)))';
alter system register;
[oracle@rac02p ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 1 15:57:09 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a15;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ---------------
ASG01PDG rac02p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PHYSICAL STANDB
Y
On Primary:
SQL> alter system set dg_broker_start=true;
System altered.
SQL>
SQL> show parameter dg_broker_config;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/19.3.0.0/
dbs/dr1ASG01P.dat
dg_broker_config_file2 string /u01/app/oracle/19.3.0.0/
dbs/dr2ASG01P.dat
SQL>
SQL> sho parameter dg_broker_start;
NAME TYPE VALUE
------------------------------------ --------------- ------------
dg_broker_start boolean TRUE
SQL> show parameter broker;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/19.3.0.0/
dbs/dr1ASG01P.dat
dg_broker_config_file2 string /u01/app/oracle/19.3.0.0/
dbs/dr2ASG01P.dat
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
On Standby:
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL>
SQL> show parameter dg_broker_config;
NAME TYPE VALUE
------------------------------------ ------------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/19.3.0.0/
dbs/dr1ASG01PDG.dat
dg_broker_config_file2 string /u01/app/oracle/19.3.0.0/
dbs/dr2ASG01PDG.dat
SQL>
SQL> sho parameter dg_broker_start;
NAME TYPE VALUE
------------------------------------ ------------- ---------------
dg_broker_start boolean TRUE
SQL> show parameter broker;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/19.3.0.0/
dbs/dr1ASG01PDG.dat
dg_broker_config_file2 string /u01/app/oracle/19.3.0.0/
dbs/dr2ASG01PDG.dat
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
Create the Dataguard Broker configuration. On the primary:
[oracle@rac01p dbs]$ which dgmgrl
/u01/app/oracle/19.3.0.0/bin/dgmgrl
[oracle@rac01p dbs]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Dec 18 12:32:39 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> connect sys/Welcome#123@ASG01P
Connected to "ASG01P"
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration 'DG_CONFIG' as primary database is 'ASG01P' connect identifier is ASG01P;
Configuration "ASG01P_dg_config" created with primary database "ASG01P"
DGMGRL> show configuration;
Configuration - ASG01P_dg_config
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
Now add the standby database:
DGMGRL> ADD DATABASE ASG01PDG AS CONNECT IDENTIFIER IS ASG01PDG MAINTAINED AS PHYSICAL;
Database "ASG01Pdg" added
DGMGRL> show configuration;
Configuration - ASG01P_dg_config
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
ASG01Pdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - ASG01P_dg_config
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
ASG01Pdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 26 seconds ago)
DGMGRL> show database ASG01P;
Database - ASG01P
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ASG01P
Database Status:
SUCCESS
DGMGRL> enable database 'ASG01P';
Enabled.
DGMGRL> show database ASG01PDG;
Database - ASG01Pdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 15.00 KByte/s
Real Time Query: OFF
Instance(s):
ASG01PDG
Database Status:
SUCCESS
Standby Side:
[oracle@rac02p ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Dec 18 12:56:22 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL> connect sys/sys123@ASG01PDG
Connected to "ASG01PDG"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - ASG01P_dg_config
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
ASG01Pdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 38 seconds ago)
ON PRIMARY:
DGMGRL> show database verbose "ASG01P";
Database - ASG01P
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ASG01P
Properties:
DGConnectIdentifier = 'ASG01P'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac01p'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac01p)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ASG01P_DGMGRL)(INSTANCE_NAME=ASG01P)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /ORACLE/ORADB/diag/rdbms/ASG01P/ASG01P/trace/alert_ASG01P.log
Data Guard Broker log : /ORACLE/ORADB/diag/rdbms/ASG01P/ASG01P/trace/drcASG01P.log
Database Status:
SUCCESS
DGMGRL> show database verbose "ASG01PDG";
Database - ASG01Pdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 8.00 KByte/s
Active Apply Rate: 483.00 KByte/s
Maximum Apply Rate: 483.00 KByte/s
Real Time Query: OFF
Instance(s):
ASG01PDG
Properties:
DGConnectIdentifier = 'ASG01Pdg'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac02p'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac02p)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ASG01PDG_DGMGRL)(INSTANCE_NAME=ASG01PDG)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /ORACLE/DBORA02/diag/rdbms/ASG01Pdg/ASG01PDG/trace/alert_ASG01PDG.log
Data Guard Broker log : /ORACLE/DBORA02/diag/rdbms/ASG01Pdg/ASG01PDG/trace/drcASG01PDG.log
Database Status:
SUCCESS
STANDBY SIDE:
col process for a10;
col CLIENT_PID for a12;
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#
from gv$managed_standby
order by CLIENT_PROCESS,THREAD#,SEQUENCE#;
SQL> select PROCESS,STATUS,SEQUENCE# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH CLOSING 76
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 79
ARCH CLOSING 77
ARCH CLOSING 72
ARCH CLOSING 78
RFS IDLE 0
RFS IDLE 80
RFS IDLE 0
MRP0 APPLYING_LOG 80
11 rows selected.
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#; 2 3 4 5 6 7 8
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 71 71 0
SQL> /
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 79 79 0
SQL> SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1; 2 3 4
Last Sequence Generated Thread
----------------------- ----------
79 1
Switch Over:
dgmgrl
connect sys/sys123@ASG01P
connect sys/sys123@ASG01PDG
dgmgrl sys/sys123@ASG01P
dgmgrl sys/sys123@ASG01PDG
NOTE : alter database switchover to ASG01PDG verify; in manual DG setup
NOTE :
validate database ASG01PDG;
validate database ASG01P;
show configuration verbose;
DGMGRL> show configuration verbose;
Configuration - DG_CONFIG
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
ASG01Pdg - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'ASG01P_CFG'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
NEW Primary:
SQL> col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a15;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ---------------
ASG01PDG rac02p OPEN ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PRIMARY
NEW STANDBY:
SQL> col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a15;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ---------------
ASG01P rac01p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PHYSICAL STANDBY
SQL> SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1; 2 3 4
Last Sequence Generated Thread
----------------------- ----------
90 1
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#; 2 3 4 5 6 7 8
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 90 90 0
[oracle@rac02p dbs]$ dgmgrl sys/Welcome#123@ASG01P
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Dec 18 14:39:01 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ASG01P"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
Configuration - ASG01P_dg_config
Protection Mode: MaxPerformance
Members:
ASG01Pdg - Primary database
ASG01P - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 61 seconds ago)
[oracle@rac02p admin]$ sqlplus "/ as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 18 14:56:20 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 5368708176 bytes
Fixed Size 8907856 bytes
Variable Size 956301312 bytes
Database Buffers 4395630592 bytes
Redo Buffers 7868416 bytes
Database mounted.
col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a15;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ---------------
ASG01PDG rac02p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PHYSICAL STANDBY
****************************************************************************
Switchover:
DGMGRL> validate database ASG01PDG;
Database Role: Physical standby database
Primary Database: ASG01Pdg
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
ASG01Pdg: Off
ASG01P : Off
Managed by Clusterware:
ASG01Pdg: NO
ASG01P : NO
Validating static connect identifier for the primary database ASG01Pdg...
The static connect identifier allows for a connection to database "ASG01Pdg".
[oracle@rac01p ~]$ dgmgrl sys/sys123@ASG01P
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Dec 30 13:41:29 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ASG01P"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> SWITCHOVER TO ASG01PDG;
Performing switchover NOW, please wait...
Operation requires a connection to database "ASG01Pdg"
Connecting ...
Connected to "ASG01PDG"
Connected as SYSDBA.
New primary database "ASG01Pdg" is opening...
Operation requires start up of instance "ASG01P" on database "ASG01P"
Starting instance "ASG01P"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ASG01P"
Database mounted.
Connected to "ASG01P"
Switchover succeeded, new primary is "ASG01Pdg"
DGMGRL>
SQL> col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a18;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ---------------
ASG01PDG rac02p OPEN ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PRIMARY
SQL> col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a18;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ---------------
ASG01P rac01p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PHYSICAL STANDBY
SQL> SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1; 2 3 4
Last Sequence Generated Thread
----------------------- ----------
199 1
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#; 2 3 4 5 6 7 8
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 199 199 0
Primary:
DGMGRL> SHOW CONFIGURATION;
Configuration - DG_CONFIG
Protection Mode: MaxPerformance
Members:
ASG01Pdg - Primary database
ASG01P - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 61 seconds ago)
STANDBY:
DGMGRL> show configuration;
Configuration - DG_CONFIG
Protection Mode: MaxPerformance
Members:
ASG01Pdg - Primary database
ASG01P - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 25 seconds ago)
Switch Back:
DGMGRL> validate database ASG01P
Database Role: Physical standby database
Primary Database: ASG01Pdg
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
ASG01Pdg: Off
ASG01P : Off
Managed by Clusterware:
ASG01Pdg: NO
ASG01P : NO
Validating static connect identifier for the primary database ASG01Pdg...
The static connect identifier allows for a connection to database "ASG01Pdg".
DGMGRL>
DGMGRL>
DGMGRL> SWITCHOVER TO ASG01P;
Performing switchover NOW, please wait...
Operation requires a connection to database "ASG01P"
Connecting ...
Connected to "ASG01P"
Connected as SYSDBA.
New primary database "ASG01P" is opening...
Operation requires start up of instance "ASG01PDG" on database "ASG01Pdg"
Starting instance "ASG01PDG"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ASG01PDG"
Database mounted.
Connected to "ASG01PDG"
Switchover succeeded, new primary is "ASG01P"
DGMGRL> show configuration;
Configuration - DG_CONFIG
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
ASG01Pdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 54 seconds ago)
Primary Side:
DGMGRL> show configuration;
Configuration - DG_CONFIG
Protection Mode: MaxPerformance
Members:
ASG01P - Primary database
ASG01Pdg - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 54 seconds ago)
SQL> col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a18;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ------------------
ASG01P rac01p OPEN ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PRIMARY
col host_name for a15;
set lines 200 pages 200;
col INSTANCE_NAME for a15;
col PROTECTION_MODE for a20;
col STATUS for a8;
col INSTANCE_ROLE for a18;
col DATABASE_STATUS for a15;
col DATABASE_ROLE for a18;
col VERSION for a15;
col VERSION_FULL for a15
select INSTANCE_NAME,HOST_NAME,STATUS,DATABASE_STATUS,PROTECTION_MODE,INSTANCE_ROLE,VERSION,VERSION_FULL,database_role from v$database,v$instance;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
INSTANCE_NAME HOST_NAME STATUS DATABASE_STATUS PROTECTION_MODE INSTANCE_ROLE VERSION VERSION_FULL DATABASE_ROLE
--------------- --------------- -------- --------------- -------------------- ------------------ --------------- --------------- ------------------
ASG01PDG rac02p MOUNTED ACTIVE MAXIMUM PERFORMANCE PRIMARY_INSTANCE 19.0.0.0.0 19.3.0.0.0 PHYSICAL STANDBY
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1; 2 3 4
Last Sequence Generated Thread
----------------------- ----------
209 1
210 1
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#; 2 3 4 5 6 7 8
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 210 209 1
1 209 209 0
1 209 210 -1
1 210 210 0
**********************************************************************
DMON --- Process
Alert logfile: Same location as DB alert logfile
[oracle@rac01p trace]$ pwd
/ORACLE/ORADB/diag/rdbms/ASG01P/ASG01P/trace
drcASG01P.log
No comments:
Post a Comment