Testing FSFO failover in both directions (Primary Database to Physical Standby Database and vice versa)
- Primary Database: PRD
- Physical Standby Database: STD
- RDBMS Version: Oracle 12c (12.1.0.2.0)
- Configured Data Guard Broker with Fast Start Failover Enabled with Observer
1. Checking DG Configuration:
DGMGRL> show configuration;
Configuration - dg_project
Protection Mode: MaxAvailability
Members:
prd - Primary database
std - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 6 seconds ago)
DGMGRL>
DGMGRL> show configuration verbose;
Configuration - dg_project
Protection Mode: MaxAvailability
Members:
prd - Primary database
std - (*) Physical standby database
(*) Fast-Start Failover target
Properties:
FastStartFailoverThreshold = '45'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '60'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: ENABLED
Threshold: 45 seconds
Target: std
Observer: primary
Lag Limit: 60 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS
DGMGRL>
2. Started Observer
[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@prd
Connected as SYSDBA.
DGMGRL> start observer
Observer started
[W000 11/06 02:30:54.80] The primary database returned to SYNC/NOT LAGGING state.
3. Checked Flashback Database from Primary Database (PRD)
Observer to be able to automatically reinstate the former primary database as a standby database after our failover tests, so before each test, make sure that Flashback Database has at least 30 minutes of history. Do this prior to every failover test. If Flashback Database history is insufficient, the observer will not be able to reinstate and you will have to manually reinstate from backup or by primary duplication.
SQL> select (sysdate - oldest_flashback_time)*24*60 as history from v$flashback_database_log;
HISTORY
--------------
266.433333
SQL>
4. Initiate FSFO failover in Primary Database (PRD)
SQL> shut abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@primary ~]$
5. Check "Observer" Messages
[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@prd
Connected as SYSDBA.
DGMGRL> start observer
Observer started
[W000 11/06 02:30:54.80] The primary database returned to SYNC/NOT LAGGING state.
02:37:34.38 Friday, November 06, 2020
Initiating Fast-Start Failover to database "std"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "std"
02:37:36.78 Friday, November 06, 2020
5. Check Physical Standby Database (STD) status
[oracle@standby ~]$ ps -ef | grep pmon
oracle 3807 1 0 02:27 ? 00:00:00 ora_pmon_STD
oracle 4941 3581 0 02:37 pts/0 00:00:00 grep --color=auto pmon
[oracle@standby ~]$
[oracle@standby ~]$ . oraenv
ORACLE_SID = [STD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@standby ~]$
[oracle@standby ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 02:37:58 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL>
6. Execute "Startup Mount" in Old Primary Database (PRD) to initiate reinstate process
Initiate reinstatement by mounting the old primary database (PRD). Note that the database will not open at this point. A database in the primary role will not open until it has verified with the observer that it is still the primary. If the observer finds that the database is no longer the primary, it will attempt to reinstate it as the failover target physical standby database
[oracle@primary ~]$ . oraenv
ORACLE_SID = [PRD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 02:38:30 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1811939328 bytes
Fixed Size 2925504 bytes
Variable Size 1409289280 bytes
Database Buffers 385875968 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL>
7. Check "Alert Logfile" in Old Primary Database (PRD)
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1
System name: Linux
Node name: primary
Release: 3.8.13-35.3.1.el7uek.x86_64
Version: #2 SMP Wed Jun 25 15:27:43 PDT 2014
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/spfilePRD.ora
System parameters with non-default values:
processes = 300
streams_pool_size = 1G
sga_target = 1728M
control_files = "/u01/app/oracle/oradata/PRD/control01.ctl"
control_files = "/u01/app/oracle/fast_recovery_area/PRD/control02.ctl"
db_block_size = 8192
compatible = "12.1.0.2.0"
log_archive_dest_1 = "location=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_1 = "valid_for=(ALL_LOGFILES, ALL_ROLES)"
log_archive_dest_2 = "service="std""
log_archive_dest_2 = "SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="std" net_timeout=30"
log_archive_dest_2 = "valid_for=(online_logfile,all_roles)"
log_archive_dest_3 = "service=orcldb async optional noregister valid_for=(online_logfiles,primary_role) db_unique_name=orcldb"
log_archive_dest_4 = "service=orcldbs async optional noregister valid_for=(online_logfiles,primary_role) db_unique_name=orcldbs"
log_archive_dest_state_2 = "ENABLE"
log_archive_dest_state_3 = "ENABLE"
log_archive_dest_state_4 = "DEFER"
log_archive_min_succeed_dest= 1
fal_server = "std"
log_archive_trace = 0
log_archive_config = "DG_CONFIG=(PRD,STD,orcldb,orcldbs)"
log_archive_format = "%t_%s_%r.dbf"
log_archive_max_processes= 4
archive_lag_target = 0
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 8G
standby_file_management = "AUTO"
enable_goldengate_replication= TRUE
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=PRDXDB)"
local_listener = "(address=(host=primary)(port=1521)(protocol=tcp))"
audit_file_dest = "/u01/app/oracle/admin/PRD/adump"
audit_trail = "DB"
db_name = "PRD"
open_cursors = 300
pga_aggregate_target = 575M
dg_broker_start = TRUE
diagnostic_dest = "/u01/app/oracle"
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Fri Nov 06 02:39:11 2020
PMON started with pid=2, OS id=5691
Starting background process PSP0
Fri Nov 06 02:39:11 2020
PSP0 started with pid=3, OS id=5693
Starting background process VKTM
Fri Nov 06 02:39:11 2020
VKTM started with pid=4, OS id=5695 at elevated (RT) priority
Fri Nov 06 02:39:11 2020
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
Fri Nov 06 02:39:11 2020
GEN0 started with pid=5, OS id=5699
Starting background process MMAN
Fri Nov 06 02:39:11 2020
MMAN started with pid=7, OS id=5703
Starting background process DIAG
Fri Nov 06 02:39:11 2020
DIAG started with pid=8, OS id=5705
Starting background process DBRM
Fri Nov 06 02:39:11 2020
DBRM started with pid=9, OS id=5710
Starting background process VKRM
Fri Nov 06 02:39:11 2020
VKRM started with pid=10, OS id=5712
Starting background process DIA0
Fri Nov 06 02:39:11 2020
DIA0 started with pid=11, OS id=5714
Starting background process DBW0
Fri Nov 06 02:39:11 2020
DBW0 started with pid=12, OS id=5716
Starting background process LGWR
Fri Nov 06 02:39:11 2020
LGWR started with pid=13, OS id=5718
Starting background process CKPT
Fri Nov 06 02:39:12 2020
CKPT started with pid=14, OS id=5720
Starting background process SMON
Fri Nov 06 02:39:12 2020
SMON started with pid=15, OS id=5722
Starting background process RECO
Fri Nov 06 02:39:12 2020
RECO started with pid=16, OS id=5724
Starting background process LREG
Fri Nov 06 02:39:12 2020
LREG started with pid=17, OS id=5726
Starting background process PXMN
Fri Nov 06 02:39:12 2020
PXMN started with pid=18, OS id=5728
Starting background process MMON
Fri Nov 06 02:39:12 2020
MMON started with pid=19, OS id=5730
Starting background process MMNL
Fri Nov 06 02:39:12 2020
MMNL started with pid=6, OS id=5732
Fri Nov 06 02:39:12 2020
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Starting background process DMON
Fri Nov 06 02:39:12 2020
DMON started with pid=22, OS id=5738
ORACLE_BASE from environment = /u01/app/oracle
Fri Nov 06 02:39:12 2020
ALTER DATABASE MOUNT
Fri Nov 06 02:39:15 2020
Using default pga_aggregate_limit of 2048 MB
Starting background process NSS2
Fri Nov 06 02:39:16 2020
NSS2 started with pid=25, OS id=5752
Fri Nov 06 02:39:16 2020
Successful mount of redo thread 1, with mount id 2119427536
Fri Nov 06 02:39:16 2020
Allocated 3407256 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Fri Nov 06 02:39:16 2020
RVWR started with pid=26, OS id=5754
Fri Nov 06 02:39:16 2020
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Nov 06 02:39:20 2020
Starting Data Guard Broker (DMON)
Starting background process INSV
Fri Nov 06 02:39:20 2020
INSV started with pid=27, OS id=5757
Starting background process NSV1
Fri Nov 06 02:39:23 2020
NSV1 started with pid=28, OS id=5765
Starting background process RSM0
Fri Nov 06 02:39:28 2020
RSM0 started with pid=29, OS id=5771
Fri Nov 06 02:39:32 2020
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Fri Nov 06 02:39:32 2020
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Starting background process TMON
Fri Nov 06 02:39:32 2020
TMON started with pid=30, OS id=5773
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
Fri Nov 06 02:39:32 2020
ARC0 started with pid=31, OS id=5775
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Nov 06 02:39:32 2020
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Fri Nov 06 02:39:32 2020
ARC1 started with pid=32, OS id=5777
Starting background process ARC2
Fri Nov 06 02:39:32 2020
ARC2 started with pid=33, OS id=5779
Starting background process ARC3
Fri Nov 06 02:39:32 2020
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Fri Nov 06 02:39:32 2020
ARC3 started with pid=34, OS id=5781
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri Nov 06 02:39:32 2020
ARC1: Becoming the heartbeat ARCH
Starting background process NSV1
Fri Nov 06 02:39:50 2020
NSV1 started with pid=28, OS id=5799
FLASHBACK DATABASE TO SCN 6115836
Fri Nov 06 02:39:53 2020
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fri Nov 06 02:39:53 2020
Serial Media Recovery started
Flashback Media Recovery Log /u01/app/oracle/fast_recovery_area/PRD/archivelog/2020_11_06/o1_mf_1_432_ht8svp2k_.arc
Fri Nov 06 02:39:53 2020
Recovery of Online Redo Log: Thread 1 Group 3 Seq 433 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PRD/redo03.log
Fri Nov 06 02:39:53 2020
Recovery of Online Redo Log: Thread 1 Group 1 Seq 434 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PRD/redo01.log
Fri Nov 06 02:39:53 2020
Recovery of Online Redo Log: Thread 1 Group 2 Seq 435 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/PRD/redo02.log
Fri Nov 06 02:39:53 2020
Incomplete Recovery applied until change 6115837 time 11/06/2020 02:36:46
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 6115836
alter database convert to physical standby
Fri Nov 06 02:39:53 2020
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (PRD)
Clearing standby activation ID 2119373903 (0x7e53144f)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Fri Nov 06 02:39:53 2020
Waiting for all non-current ORLs to be archived...
Fri Nov 06 02:39:53 2020
All non-current ORLs have been archived.
Fri Nov 06 02:39:53 2020
ARC1: Becoming the active heartbeat ARCH
Fri Nov 06 02:39:53 2020
Clearing online redo logfile 1 /u01/app/oracle/oradata/PRD/redo01.log
Clearing online log 1 of thread 1 sequence number 434
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/PRD/redo02.log
Clearing online log 2 of thread 1 sequence number 435
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/PRD/redo03.log
Clearing online log 3 of thread 1 sequence number 433
Clearing online redo logfile 3 complete
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby
Data Guard: Failover target was a Real Time Query standby; attempting to open this standby after reinstatement ...
ALTER DATABASE OPEN READ ONLY
Data Guard Broker initializing...
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Ping without log force is disabled
.
Fri Nov 06 02:39:54 2020
SMON: enabling cache recovery
Fri Nov 06 02:39:54 2020
Dictionary check beginning
Fri Nov 06 02:39:54 2020
RFS[1]: Assigned to RFS process (PID:5805)
RFS[1]: Opened log for thread 1 sequence 435 dbid 1961675719 branch 898012106
Fri Nov 06 02:39:54 2020
Dictionary check complete
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
Fri Nov 06 02:39:54 2020
Archived Log entry 924 added for thread 1 sequence 435 rlc 898012106 ID 0x7e53144f dest 2:
RFS[1]: Opened log for thread 1 sequence 1 dbid 1961675719 branch 1055731054
A new recovery destination branch has been registered
RFS[1]: New Archival REDO Branch(resetlogs_id): 1055731054 Prior: 898012106
RFS[1]: Archival Activation ID: 0x7e538f07 Current: 0x0
RFS[1]: Effect of primary database OPEN RESETLOGS
Fri Nov 06 02:39:54 2020
RFS[1]: Incarnation entry added for Branch(resetlogs_id): 1055731054 (PRD)
Fri Nov 06 02:39:54 2020
Setting recovery target incarnation to 3
Fri Nov 06 02:39:54 2020
Archived Log entry 925 added for thread 1 sequence 1 rlc 1055731054 ID 0x7e538f07 dest 2:
Fri Nov 06 02:39:54 2020
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: ALTER DATABASE OPEN READ ONLY
Fri Nov 06 02:39:55 2020
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
Fri Nov 06 02:39:55 2020
db_recovery_file_dest_size of 8192 MB is 21.18% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='PRD';
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='PRD';
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Fri Nov 06 02:39:56 2020
ALTER SYSTEM SET fal_server='std' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Fri Nov 06 02:39:56 2020
Attempt to start background Managed Standby Recovery process (PRD)
Starting background process MRP0
Fri Nov 06 02:39:56 2020
MRP0 started with pid=36, OS id=5827
Fri Nov 06 02:39:56 2020
MRP0: Background Managed Standby Recovery process started (PRD)
Fri Nov 06 02:39:58 2020
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Standby controlfile consistent with primary
RFS[2]: Assigned to RFS process (PID:5831)
RFS[2]: Selected log 4 for thread 1 sequence 3 dbid 1961675719 branch 1055731054
Fri Nov 06 02:39:58 2020
RFS[3]: Assigned to RFS process (PID:5833)
RFS[3]: Selected log 5 for thread 1 sequence 2 dbid 1961675719 branch 1055731054
Fri Nov 06 02:39:58 2020
Archived Log entry 926 added for thread 1 sequence 2 ID 0x7e538f07 dest 1:
Fri Nov 06 02:40:01 2020
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Fri Nov 06 02:40:01 2020
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[2]: Selected log 5 for thread 1 sequence 4 dbid 1961675719 branch 1055731054
Fri Nov 06 02:40:01 2020
Archived Log entry 927 added for thread 1 sequence 3 ID 0x7e538f07 dest 1:
Fri Nov 06 02:40:01 2020
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 6115838
Fri Nov 06 02:40:01 2020
Waiting for all non-current ORLs to be archived...
Fri Nov 06 02:40:01 2020
All non-current ORLs have been archived.
Fri Nov 06 02:40:01 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/PRD/archivelog/2020_11_06/o1_mf_1_435_ht8tk2hk_.arc
Fri Nov 06 02:40:01 2020
Resetting standby activation ID 2119373903 (0x7e53144f)
Fri Nov 06 02:40:01 2020
Media Recovery End-Of-Redo indicator encountered
Fri Nov 06 02:40:01 2020
Media Recovery Continuing
Fri Nov 06 02:40:01 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/PRD/archivelog/2020_11_06/o1_mf_1_1_ht8tk2ml_.arc
Fri Nov 06 02:40:01 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/PRD/archivelog/2020_11_06/o1_mf_1_2_ht8tk6ms_.arc
Fri Nov 06 02:40:02 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/PRD/archivelog/2020_11_06/o1_mf_1_3_ht8tk9pq_.arc
Media Recovery Waiting for thread 1 sequence 4 (in transit)
Fri Nov 06 02:40:02 2020
Recovery of Online Redo Log: Thread 1 Group 5 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/PRD/onlinelog/o1_mf_5_c6j3rw9s_.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Fri Nov 06 02:40:05 2020
Standby controlfile consistent with primary
RFS[2]: Selected log 4 for thread 1 sequence 5 dbid 1961675719 branch 1055731054
Fri Nov 06 02:40:05 2020
Archived Log entry 928 added for thread 1 sequence 4 ID 0x7e538f07 dest 1:
Fri Nov 06 02:40:05 2020
Media Recovery Waiting for thread 1 sequence 5 (in transit)
Fri Nov 06 02:40:05 2020
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/PRD/onlinelog/o1_mf_4_c6j3rnnc_.log
8. Check again "Observer Status" after "Startup Mount" from Old Primary Database (PRD)
02:39:49.39 Friday, November 06, 2020
Initiating reinstatement for database "prd"...
Reinstating database "prd", please wait...
Reinstatement of database "prd" succeeded
02:40:06.19 Friday, November 06, 2020
9. Check Old Primary Database (PRD) status after above status from "Observer"
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
SQL>
9. Check DG Broker Status
[oracle@primary ~]$ . oraenv
ORACLE_SID = [PRD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@prd
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration verbose;
Configuration - dg_project
Protection Mode: MaxAvailability
Members:
std - Primary database
prd - (*) Physical standby database
(*) Fast-Start Failover target
Properties:
FastStartFailoverThreshold = '45'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '60'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: ENABLED
Threshold: 45 seconds
Target: prd
Observer: primary
Lag Limit: 60 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS
DGMGRL>
10. Test the Transactions from New Primary Database (STD) to Old Primary Database (PRD)
[oracle@standby ~]$ . oraenv
ORACLE_SID = [STD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@standby ~]$
[oracle@standby ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 02:56:03 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL> insert into scott.dept values (26,'SRC-STD','TEXAS');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.dept;
COUNT(*)
----------
40
SQL>
11. Login to "Old Primary Database (PRD)" and check the transactions
[oracle@primary ~]$ . oraenv
ORACLE_SID = [PRD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 02:38:30 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select count(*) from scott.dept;
COUNT(*)
----------
40
SQL>
Repeat in the other direction of Failover from New Primary Database (STD) to Old Primary Database (PRD)
1. Check the status of the STD database and initiate process
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL> shut abort
ORACLE instance shut down.
SQL>
Check Alert Logfile
Fri Nov 06 03:07:04 2020
Shutting down instance (abort)
License high water mark = 9
Fri Nov 06 03:07:04 2020
USER (ospid: 6114): terminating the instance
Fri Nov 06 03:07:05 2020
Instance terminated by USER, pid = 6114
Fri Nov 06 03:07:05 2020
Instance shutdown complete
Fri Nov 06 03:09:19 2020
Starting ORACLE instance (normal) (OS id: 7140)
Fri Nov 06 03:09:19 2020
CLI notifier numLatches:3 maxDescs:519
Fri Nov 06 03:09:19 2020
2. Check "Observer" messages
03:07:55.56 Friday, November 06, 2020
Initiating Fast-Start Failover to database "prd"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "prd"
03:07:58.84 Friday, November 06, 2020
3. Check PRD database status
[oracle@primary ~]$ . oraenv
ORACLE_SID = [PRD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 03:08:52 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL>
4. Execute "Startup Mount" in STD database
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1811939328 bytes
Fixed Size 2925504 bytes
Variable Size 1409289280 bytes
Database Buffers 385875968 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL>
Check "Observer" Messages
03:09:59.51 Friday, November 06, 2020
Initiating reinstatement for database "std"...
Reinstating database "std", please wait...
Reinstatement of database "std" succeeded
03:10:13.66 Friday, November 06, 2020
Check the Alert Logfile
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1
System name: Linux
Node name: standby
Release: 3.8.13-35.3.1.el7uek.x86_64
Version: #2 SMP Wed Jun 25 15:27:43 PDT 2014
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/spfileSTD.ora
System parameters with non-default values:
processes = 300
streams_pool_size = 1G
sga_target = 1728M
control_files = "/u01/app/oracle/oradata/STD/control01.ctl"
db_file_name_convert = "/PRD/"
db_file_name_convert = "/STD/"
log_file_name_convert = "/PRD/"
log_file_name_convert = "/STD/"
db_block_size = 8192
compatible = "12.1.0.2.0"
log_archive_dest_1 = "location=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_1 = "valid_for=(ALL_LOGFILES, ALL_ROLES)"
log_archive_dest_2 = "service="prd""
log_archive_dest_2 = "SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="prd" net_timeout=10"
log_archive_dest_2 = "valid_for=(online_logfile,all_roles)"
log_archive_dest_3 = "service=orcldb async optional noregister valid_for=(online_logfiles,primary_role) db_unique_name=orcldb"
log_archive_dest_4 = "service=orcldbs async optional noregister valid_for=(online_logfiles,primary_role) db_unique_name=orcldbs"
log_archive_dest_state_2 = "ENABLE"
log_archive_dest_state_3 = "ENABLE"
log_archive_dest_state_4 = "DEFER"
log_archive_min_succeed_dest= 1
fal_client = "STD"
fal_server = "prd,orcldb"
log_archive_trace = 0
log_archive_config = "DG_CONFIG=(PRD,STD,orcldb,orcldbs)"
log_archive_format = "%t_%s_%r.dbf"
log_archive_max_processes= 5
archive_lag_target = 0
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 8G
standby_file_management = "AUTO"
enable_goldengate_replication= TRUE
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=STDXDB)"
local_listener = "(address=(host=standby)(port=1521)(protocol=tcp))"
audit_file_dest = "/u01/app/oracle/admin/STD/adump"
audit_trail = "DB"
db_name = "PRD"
db_unique_name = "STD"
open_cursors = 300
pga_aggregate_target = 575M
dg_broker_start = TRUE
diagnostic_dest = "/u01/app/oracle"
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Fri Nov 06 03:09:19 2020
PMON started with pid=2, OS id=7484
Starting background process PSP0
Fri Nov 06 03:09:19 2020
PSP0 started with pid=3, OS id=7486
Starting background process VKTM
Fri Nov 06 03:09:19 2020
VKTM started with pid=4, OS id=7488 at elevated (RT) priority
Fri Nov 06 03:09:19 2020
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
Fri Nov 06 03:09:19 2020
GEN0 started with pid=5, OS id=7492
Starting background process MMAN
Fri Nov 06 03:09:19 2020
MMAN started with pid=6, OS id=7494
Starting background process DIAG
Fri Nov 06 03:09:19 2020
DIAG started with pid=8, OS id=7498
Starting background process DBRM
Fri Nov 06 03:09:19 2020
DBRM started with pid=9, OS id=7500
Starting background process VKRM
Fri Nov 06 03:09:19 2020
VKRM started with pid=10, OS id=7502
Starting background process DIA0
Fri Nov 06 03:09:19 2020
DIA0 started with pid=11, OS id=7504
Starting background process DBW0
Fri Nov 06 03:09:19 2020
DBW0 started with pid=12, OS id=7508
Starting background process LGWR
Fri Nov 06 03:09:19 2020
LGWR started with pid=13, OS id=7512
Starting background process CKPT
Fri Nov 06 03:09:19 2020
CKPT started with pid=15, OS id=7514
Starting background process SMON
Fri Nov 06 03:09:19 2020
SMON started with pid=16, OS id=7516
Starting background process RECO
Fri Nov 06 03:09:19 2020
RECO started with pid=17, OS id=7518
Starting background process LREG
Fri Nov 06 03:09:19 2020
LREG started with pid=18, OS id=7520
Starting background process PXMN
Fri Nov 06 03:09:19 2020
PXMN started with pid=19, OS id=7522
Starting background process MMON
Fri Nov 06 03:09:19 2020
MMON started with pid=20, OS id=7524
Starting background process MMNL
Fri Nov 06 03:09:19 2020
MMNL started with pid=21, OS id=7526
Fri Nov 06 03:09:19 2020
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Starting background process DMON
Fri Nov 06 03:09:19 2020
DMON started with pid=23, OS id=7532
ORACLE_BASE from environment = /u01/app/oracle
Fri Nov 06 03:09:19 2020
ALTER DATABASE MOUNT
Fri Nov 06 03:09:22 2020
Using default pga_aggregate_limit of 2048 MB
Starting background process NSS2
Fri Nov 06 03:09:23 2020
NSS2 started with pid=25, OS id=7547
Fri Nov 06 03:09:23 2020
Successful mount of redo thread 1, with mount id 2119406559
Fri Nov 06 03:09:23 2020
Allocated 3403160 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Fri Nov 06 03:09:23 2020
RVWR started with pid=26, OS id=7549
Fri Nov 06 03:09:23 2020
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Fri Nov 06 03:09:27 2020
Starting Data Guard Broker (DMON)
Starting background process INSV
Fri Nov 06 03:09:27 2020
INSV started with pid=27, OS id=7552
Starting background process NSV0
Fri Nov 06 03:09:30 2020
NSV0 started with pid=28, OS id=7554
Starting background process RSM0
Fri Nov 06 03:09:35 2020
RSM0 started with pid=29, OS id=7572
Fri Nov 06 03:09:39 2020
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Fri Nov 06 03:09:39 2020
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Starting background process TMON
Fri Nov 06 03:09:39 2020
TMON started with pid=30, OS id=7584
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
Fri Nov 06 03:09:39 2020
ARC0 started with pid=31, OS id=7586
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Nov 06 03:09:39 2020
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Fri Nov 06 03:09:39 2020
ARC1 started with pid=32, OS id=7588
Starting background process ARC2
Fri Nov 06 03:09:39 2020
ARC2 started with pid=33, OS id=7590
Starting background process ARC3
Fri Nov 06 03:09:39 2020
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Fri Nov 06 03:09:39 2020
ARC3 started with pid=34, OS id=7592
Starting background process ARC4
Fri Nov 06 03:09:39 2020
ARC4 started with pid=28, OS id=7594
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri Nov 06 03:09:39 2020
ARC1: Becoming the heartbeat ARCH
Starting background process NSV0
Fri Nov 06 03:09:59 2020
NSV0 started with pid=35, OS id=7614
FLASHBACK DATABASE TO SCN 6121282
Fri Nov 06 03:10:02 2020
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fri Nov 06 03:10:02 2020
Serial Media Recovery started
Flashback Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_2_ht8tk59l_.arc
Flashback Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_3_ht8tk8mn_.arc
Flashback Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_4_ht8tkcxf_.arc
Fri Nov 06 03:10:02 2020
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/STD/redo02.log
Fri Nov 06 03:10:02 2020
Recovery of Online Redo Log: Thread 1 Group 3 Seq 6 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/STD/redo03.log
Fri Nov 06 03:10:02 2020
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/STD/redo01.log
Fri Nov 06 03:10:02 2020
Incomplete Recovery applied until change 6121283 time 11/06/2020 03:07:04
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 6121282
alter database convert to physical standby
Fri Nov 06 03:10:03 2020
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (STD)
Clearing standby activation ID 2119405319 (0x7e538f07)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Fri Nov 06 03:10:03 2020
Waiting for all non-current ORLs to be archived...
Fri Nov 06 03:10:03 2020
All non-current ORLs have been archived.
Fri Nov 06 03:10:03 2020
ARC1: Becoming the active heartbeat ARCH
Fri Nov 06 03:10:03 2020
Clearing online redo logfile 1 /u01/app/oracle/oradata/STD/redo01.log
Clearing online log 1 of thread 1 sequence number 7
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/STD/redo02.log
Clearing online log 2 of thread 1 sequence number 5
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/STD/redo03.log
Clearing online log 3 of thread 1 sequence number 6
Clearing online redo logfile 3 complete
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby
Data Guard: Failover target was a Real Time Query standby; attempting to open this standby after reinstatement ...
ALTER DATABASE OPEN READ ONLY
Data Guard Broker initializing...
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
Ping without log force is disabled
.
Fri Nov 06 03:10:03 2020
Error 12154 received logging on to the standby
FAL[client, ARC3]: Error 12154 connecting to prd,orcldb for fetching gap sequence
Fri Nov 06 03:10:03 2020
Errors in file /u01/app/oracle/diag/rdbms/std/STD/trace/STD_arc3_7592.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
Fri Nov 06 03:10:03 2020
SMON: enabling cache recovery
Fri Nov 06 03:10:03 2020
Dictionary check beginning
Dictionary check complete
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: ALTER DATABASE OPEN READ ONLY
Fri Nov 06 03:10:04 2020
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
Fri Nov 06 03:10:04 2020
db_recovery_file_dest_size of 8192 MB is 19.26% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='STD';
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='STD';
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET log_archive_max_processes=5 SCOPE=BOTH SID='*';
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET db_file_name_convert='/PRD/','/STD/' SCOPE=SPFILE;
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET log_file_name_convert='/PRD/','/STD/' SCOPE=SPFILE;
Fri Nov 06 03:10:04 2020
ALTER SYSTEM SET fal_server='prd' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Fri Nov 06 03:10:04 2020
Attempt to start background Managed Standby Recovery process (STD)
Starting background process MRP0
Fri Nov 06 03:10:04 2020
MRP0 started with pid=37, OS id=7653
Fri Nov 06 03:10:04 2020
MRP0: Background Managed Standby Recovery process started (STD)
Fri Nov 06 03:10:06 2020
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Standby controlfile consistent with primary
RFS[1]: Assigned to RFS process (PID:7655)
RFS[1]: Selected log 4 for thread 1 sequence 3 dbid 1961675719 branch 1055732876
A new recovery destination branch has been registered
RFS[1]: New Archival REDO Branch(resetlogs_id): 1055732876 Prior: 1055731054
RFS[1]: Archival Activation ID: 0x7e53e5d0 Current: 0x0
RFS[1]: Effect of primary database OPEN RESETLOGS
RFS[1]: Managed Standby Recovery process is active
Fri Nov 06 03:10:06 2020
RFS[1]: Incarnation entry added for Branch(resetlogs_id): 1055732876 (STD)
Fri Nov 06 03:10:06 2020
Setting recovery target incarnation to 4
Fri Nov 06 03:10:06 2020
Archived Log entry 580 added for thread 1 sequence 3 ID 0x7e53e5d0 dest 1:
Fri Nov 06 03:10:07 2020
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Standby controlfile consistent with primary
RFS[2]: Assigned to RFS process (PID:7661)
RFS[2]: Selected log 4 for thread 1 sequence 4 dbid 1961675719 branch 1055732876
Fri Nov 06 03:10:07 2020
RFS[3]: Assigned to RFS process (PID:7657)
RFS[3]: Opened log for thread 1 sequence 7 dbid 1961675719 branch 1055731054
Fri Nov 06 03:10:07 2020
RFS[4]: Assigned to RFS process (PID:7659)
RFS[4]: Selected log 5 for thread 1 sequence 2 dbid 1961675719 branch 1055732876
Fri Nov 06 03:10:07 2020
Archived Log entry 581 added for thread 1 sequence 7 rlc 1055731054 ID 0x7e538f07 dest 2:
Fri Nov 06 03:10:07 2020
Archived Log entry 582 added for thread 1 sequence 2 ID 0x7e53e5d0 dest 1:
Fri Nov 06 03:10:07 2020
RFS[5]: Assigned to RFS process (PID:7663)
RFS[5]: Opened log for thread 1 sequence 1 dbid 1961675719 branch 1055732876
Fri Nov 06 03:10:07 2020
Archived Log entry 583 added for thread 1 sequence 1 rlc 1055732876 ID 0x7e53e5d0 dest 2:
Fri Nov 06 03:10:09 2020
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 6121284
Fri Nov 06 03:10:09 2020
Waiting for all non-current ORLs to be archived...
Fri Nov 06 03:10:09 2020
All non-current ORLs have been archived.
Fri Nov 06 03:10:09 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_7_ht8w9q3p_.arc
Fri Nov 06 03:10:09 2020
Resetting standby activation ID 2119405319 (0x7e538f07)
Fri Nov 06 03:10:10 2020
Media Recovery End-Of-Redo indicator encountered
Fri Nov 06 03:10:10 2020
Media Recovery Continuing
Fri Nov 06 03:10:10 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_1_ht8w9qc9_.arc
Fri Nov 06 03:10:10 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_2_ht8w9qbf_.arc
Fri Nov 06 03:10:10 2020
Media Recovery Log /u01/app/oracle/fast_recovery_area/STD/archivelog/2020_11_06/o1_mf_1_3_ht8w9p4r_.arc
Media Recovery Waiting for thread 1 sequence 4 (in transit)
Fri Nov 06 03:10:10 2020
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/STD/onlinelog/o1_mf_4_c6kgfjfb_.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Fri Nov 06 03:10:11 2020
Changing standby controlfile to MAXIMUM AVAILABILITY level
RFS[2]: Selected log 5 for thread 1 sequence 5 dbid 1961675719 branch 1055732876
Fri Nov 06 03:10:11 2020
Archived Log entry 584 added for thread 1 sequence 4 ID 0x7e53e5d0 dest 1:
Fri Nov 06 03:10:11 2020
Media Recovery Waiting for thread 1 sequence 5 (in transit)
Fri Nov 06 03:10:11 2020
Recovery of Online Redo Log: Thread 1 Group 5 Seq 5 Reading mem 0
Mem# 0: /u01/app/oracle/fast_recovery_area/STD/onlinelog/o1_mf_5_c6kgfkjm_.log
5. Check the status of Physical Standby Database (STD)
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- --------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY
6. Test the Transactions from Primary Database (PRD) to Physical Standby Database (STD)
[oracle@primary ~]$ . oraenv
ORACLE_SID = [PRD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$
[oracle@primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 03:08:52 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL> insert into scott.dept values (27,'SRC-PRD','TEXAS');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.dept;
COUNT(*)
----------
41
SQL>
Check the transactions count in Physical Standby Database (STD)
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select count(*) from scott.dept;
COUNT(*)
----------
41
SQL>
Check the DGMGRL status
[oracle@primary ~]$ . oraenv
ORACLE_SID = [PRD] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@primary ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@prd
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration;
Configuration - dg_project
Protection Mode: MaxAvailability
Members:
prd - Primary database
std - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS (status updated 42 seconds ago)
DGMGRL> show configuration verbose;
Configuration - dg_project
Protection Mode: MaxAvailability
Members:
prd - Primary database
std - (*) Physical standby database
(*) Fast-Start Failover target
Properties:
FastStartFailoverThreshold = '45'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '60'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
Fast-Start Failover: ENABLED
Threshold: 45 seconds
Target: std
Observer: primary
Lag Limit: 60 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configuration Status:
SUCCESS
DGMGRL> show database prd;
Database - prd
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PRD
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database std;
Database - std
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: 2.00 KByte/s
Real Time Query: ON
Instance(s):
STD
Database Status:
SUCCESS
DGMGRL> show fast_start failover;
Fast-Start Failover: ENABLED
Threshold: 45 seconds
Target: std
Observer: primary
Lag Limit: 60 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)
DGMGRL>
