A! Help: dataguard broker

Showing posts with label dataguard broker. Show all posts

Saturday, March 26, 2022

Initiating Switchover on a Physical Standby

It seems the best instance to intiaiate the switchover changes based on whether data guard broker is used or not. Oracle documentation states when performing switchover using SQL, the switchover process is initiated on the primary database.

Initiate the switchover on the primary database, BOSTON, by issuing the following SQL statement

On the data guard broker documentation it doesn't explicitly states where to initiate the switchover. In practice switchover command could be initiated while logged into any instance. However, executing switchover on the primary instance when data gaurd broker is used will output the following in the data guard broker log. The DG configuration is

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest  - Primary database
    dgtest2 - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 9 seconds ago)

Switchover command is issued while connected to the current primary, dgtest.

2022-03-21T10:41:33.122+00:00
Forwarding MON_PROPERTY operation to member dgtest2 for processing
2022-03-21T10:41:42.319+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest2
Switchover to physical standby database cannot be initiated from the primary database
redirecting connection to switchover target database dgtest2...
...using connect identifier: dgtest2tns
SWITCHOVER TO dgtest2
Notifying Oracle Clusterware to prepare primary database for switchover
2022-03-21T10:41:44.319+00:00
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest2']
2022-03-21T10:42:02.573+00:00
SQL [ALTER DATABASE SWITCHOVER TO 'dgtest2'] executed successfully
2022-03-21T10:42:03.652+00:00
Switchover successful

From the output it seems that DG broker is creating a remote connection to the standby (new primary) using the TNS entry used when the standby was added to the data guard broker.

On the otherhand if connected to the standby and switchover command is issued no such message appears.

DGMGRL>  show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

New primary is dgtest2 and switchover command is issued from standby, dgtest. The output on DG broker log of the primary (dgtest2) shows the below

2022-03-21T10:50:27.266+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest
2022-03-21T10:50:28.682+00:00
Notifying Oracle Clusterware to prepare primary database for switchover
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest']
2022-03-21T10:50:49.451+00:00
SQL [ALTER DATABASE SWITCHOVER TO 'dgtest'] executed successfully

Similary if the switchvoer is issued from another standby that is not the future primary (in this confiugration dgtest3) then again the primary's DG broker log output will have the below output

2022-03-21T11:31:51.242+00:00
Initiating a healthcheck...
SWITCHOVER TO dgtest2
2022-03-21T11:31:52.601+00:00
Notifying Oracle Clusterware to prepare primary database for switchover
Executing SQL: [ALTER DATABASE SWITCHOVER TO 'dgtest2']

Tuesday, September 28, 2021

Failover and Reinstate With Multiple Physical Standbys

The data guard configuration consists of three databases.

 DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest3 - Primary database
    dgtest  - Physical standby database
    dgtest2 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 67 seconds ago)

At the moment dgtest3 is the primary. One of the standby (dgtest2) databases is open in mount mode.

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
dgtest2                        MOUNTED

The other standby (dgtest) is an active data guard and is open in read only mode with log apply.

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
dgtest                         READ ONLY WITH APPLY

To simulate primary DB failure it is shutdown with abort.

SQL> shutdown abort;
ORACLE instance shut down.

Checking the data guard configuration from dgtest2 shows an error state.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest3 - Primary database
    Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    dgtest  - Physical standby database
    dgtest2 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 0 seconds ago)

One of the existing standbys could be choosen for failover. In this case dgtest2( instance in mount mode) was chosen. Validating the dgtest2 show that it is ready for failover.

DGMGRL> validate database dgtest2

  Database Role:     Physical standby database
  Primary Database:  dgtest3
    Warning: primary database was not reachable

  Ready for Switchover:  No
  Ready for Failover:    Yes (Primary Not Running)

  Flashback Database Status:
    dgtest3:  Unknown
    dgtest2:  On

  Managed by Clusterware:
    dgtest3:  Unknown
    dgtest2:  YES
    Validating static connect identifier for the primary database dgtest3...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-132)(PORT=1521))(CONNECT                                                                                                                          _DATA=(SERVICE_NAME=dgtest3_DGMGRL)(INSTANCE_NAME=dgtest3)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    dgtest3 TEMP Files:  Unknown
    dgtest2 TEMP Files:  3

  Data file Online Move in Progress:
    dgtest3:  Unknown
    dgtest2:  No

  Transport-Related Information:
    Transport On:  No
    Gap Status:    Unknown
    Transport Lag:  0 seconds (computed 24 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    dgtest3 Standby Redo Log Files:  Unknown
    dgtest2 Online Redo Log Files:   Unknown
    dgtest2 Standby Redo Log Files:  Unknown

Initiate failover to dgtest2

DGMGRL> failover to dgtest2;
Performing failover NOW, please wait...
Failover succeeded, new primary is "dgtest2"

During the failover process the active data guard instance continue to function. It will cancel the current redo apply service and start a new one with the incarnation that results from the failover. At the same time any references to the old primary will be removed (such as fal_server).

2021-09-20T09:53:29.718199+00:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2021-09-20T09:53:29.720039+00:00
PR00 (PID:5312): MRP0: Background Media Recovery cancelled with status 16037
2021-09-20T09:53:29.720747+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5312.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:5312): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4236455
Stopping change tracking
2021-09-20T09:53:29.943216+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5312.trc:
ORA-16037: user requested cancel of managed recovery operation
2021-09-20T09:53:30.067266+00:00
Background Media Recovery process shutdown (dgtest)
2021-09-20T09:53:30.720982+00:00
Managed Standby Recovery Canceled (dgtest)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2021-09-20T09:53:32.641452+00:00
ALTER SYSTEM SET fal_server='dgtest2tns' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2021-09-20T09:53:32.658894+00:00
Attempt to start background Managed Standby Recovery process (dgtest)
Starting background process MRP0
2021-09-20T09:53:32.673122+00:00
MRP0 started with pid=63, OS id=5816
2021-09-20T09:53:32.674424+00:00
Background Managed Standby Recovery process started (dgtest)
2021-09-20T09:53:36.270863+00:00
 rfs (PID:5820): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:11152)
Deleted Oracle managed file +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_0_seq_0.371.1083750817
2021-09-20T09:53:37.697852+00:00
 Started logmerger process
2021-09-20T09:53:37.712394+00:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:5826): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2021-09-20T09:53:37.888440+00:00
 rfs (PID:5824): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is SYNC (PID:10754)
 rfs (PID:5824): New archival redo branch: 1083750811 current: 1067878072
 rfs (PID:5824): Primary database is in MAXIMUM AVAILABILITY mode
 rfs (PID:5824): Changing standby controlfile to RESYNCHRONIZATION level
 rfs (PID:5824): Standby controlfile consistent with primary
2021-09-20T09:53:37.951928+00:00
 rfs (PID:5824): Selected LNO:6 for T-1.S-3 dbid 4024401720 branch 1083750811
2021-09-20T09:53:37.974747+00:00
Parallel Media Recovery started with 8 slaves
2021-09-20T09:53:38.018389+00:00
Stopping change tracking
PR00 (PID:5826): Media Recovery Waiting for T-1.S-382
2021-09-20T09:53:38.440514+00:00
 rfs (PID:5855): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:11166)
2021-09-20T09:53:38.505442+00:00
 rfs (PID:5855): Selected LNO:7 for T-1.S-2 dbid 4024401720 branch 1083750811
 rfs (PID:5855): A new recovery destination branch has been registered
 rfs (PID:5855): New Archival REDO Branch(resetlogs_id): 1083750811  Prior: 1067878072
 rfs (PID:5855): Archival Activation ID: 0xf0d1268f Current: 0xf0d199cf
 rfs (PID:5855): Effect of primary database OPEN RESETLOGS
 rfs (PID:5855): Managed Standby Recovery process is active
2021-09-20T09:53:38.536066+00:00
Incarnation entry added for Branch(resetlogs_id): 1083750811 (dgtest)
2021-09-20T09:53:38.548638+00:00
Setting recovery target incarnation to 2
2021-09-20T09:53:38.678029+00:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2021-09-20T09:53:39.126620+00:00
PR00 (PID:5826): MRP0: Incarnation has changed! Retry recovery...
2021-09-20T09:53:39.127329+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5826.trc:
ORA-19906: recovery target incarnation changed during recovery
PR00 (PID:5826): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Stopping change tracking
2021-09-20T09:53:39.233028+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5826.trc:
ORA-19906: recovery target incarnation changed during recovery
2021-09-20T09:53:39.432821+00:00
 rfs (PID:5858): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:11172)
2021-09-20T09:53:39.480429+00:00
 rfs (PID:5858): Opened log for T-1.S-382 dbid 4024401720 branch 1067878072
2021-09-20T09:53:39.509873+00:00
 rfs (PID:5858): Archived Log entry 746 added for B-1067878072.T-1.S-382 ID 0xf0d199cf LAD:2
2021-09-20T09:53:39.573601+00:00
 Started logmerger process
2021-09-20T09:53:39.614023+00:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:5861): Managed Standby Recovery starting Real Time Apply
2021-09-20T09:53:39.622286+00:00
ARC8 (PID:5169): Archived Log entry 747 added for T-1.S-2 ID 0xf0d1268f LAD:1
2021-09-20T09:53:39.678880+00:00
max_pdb is 3
2021-09-20T09:53:39.878314+00:00
Parallel Media Recovery started with 8 slaves
2021-09-20T09:53:39.894206+00:00
Media Recovery start incarnation depth : 1, target inc# : 2, irscn : 4236456
Stopping change tracking
2021-09-20T09:53:39.917344+00:00
 rfs (PID:5863): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:11158)
2021-09-20T09:53:39.974576+00:00
 rfs (PID:5863): Opened log for T-1.S-1 dbid 4024401720 branch 1083750811
2021-09-20T09:53:40.001952+00:00
 rfs (PID:5863): Archived Log entry 748 added for B-1083750811.T-1.S-1 ID 0xf0d1268f LAD:2
2021-09-20T09:53:40.011908+00:00
PR00 (PID:5861): Media Recovery Log +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_1_seq_382.371.1083750819
2021-09-20T09:53:40.134398+00:00
PR00 (PID:5861): Resetting standby activation ID 4040268239 (0xf0d199cf)
2021-09-20T09:53:40.138397+00:00
Media Recovery End-Of-Redo indicator encountered
2021-09-20T09:53:40.138471+00:00
Media Recovery Continuing
2021-09-20T09:53:40.401844+00:00
PR00 (PID:5861): Media Recovery Log +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_1_seq_1.454.1083750819
2021-09-20T09:53:40.499037+00:00
PR00 (PID:5861): Media Recovery Log +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_1_seq_2.372.1083750819
2021-09-20T09:53:40.692602+00:00
PR00 (PID:5861): Media Recovery Waiting for T-1.S-3 (in transit)
2021-09-20T09:53:40.698462+00:00
Recovery of Online Redo Log: Thread 1 Group 6 Seq 3 Reading mem 0
  Mem# 0: +FRA/DGTEST/ONLINELOG/group_6.423.1067949163
2021-09-20T09:53:41.024853+00:00
 rfs (PID:5824): Changing standby controlfile to MAXIMUM AVAILABILITY level
2021-09-20T09:53:41.041788+00:00
 rfs (PID:5824): Selected LNO:7 for T-1.S-4 dbid 4024401720 branch 1083750811
2021-09-20T09:53:41.045351+00:00
ARC9 (PID:5171): Archived Log entry 749 added for T-1.S-3 ID 0xf0d1268f LAD:1
2021-09-20T09:53:41.118868+00:00
PR00 (PID:5861): Media Recovery Waiting for T-1.S-4 (in transit)
2021-09-20T09:53:41.124519+00:00
Recovery of Online Redo Log: Thread 1 Group 7 Seq 4 Reading mem 0
  Mem# 0: +FRA/DGTEST/ONLINELOG/group_7.424.1067949165

The instance failed over to will stop it's current redo apply service and convert to a read/write primary. It is intresting to see some of the lines in the log output "switchover" instead of failover.

2021-09-20T09:53:24.419649+00:00
Beginning failover to database dgtest2.
Starting background process NSV1
2021-09-20T09:53:24.492112+00:00
NSV1 started with pid=16, OS id=12324
2021-09-20T09:53:29.976461+00:00
ALTER DATABASE FAILOVER TO dgtest2
2021-09-20T09:53:29.976612+00:00
RSM0 (PID:11547): The Time Management Interface (TMI) is being enabled for role transition
RSM0 (PID:11547): information.  This will result in messages beingoutput to the alert log
RSM0 (PID:11547): file with the prefix 'TMI: '.  This is being enabled to make the timing of
RSM0 (PID:11547): the various stages of the role transition available for diagnostic purposes.
RSM0 (PID:11547): This output will end when the role transition is complete.
TMI: dbsdrv failover to target BEGIN 2021-09-20 09:53:29.977021
Terminal Recovery requested in process 11547
TMI: adbdrv termRecovery BEGIN 2021-09-20 09:53:29.978960
RSM0 (PID:11547): Terminal Recovery: Stopping real time apply
2021-09-20T09:53:29.980343+00:00
PR00 (PID:11627): MRP0: Background Media Recovery cancelled with status 16037
2021-09-20T09:53:29.980556+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_11627.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:11627): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4236455
Stopping change tracking
2021-09-20T09:53:30.221440+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_11627.trc:
ORA-16037: user requested cancel of managed recovery operation
2021-09-20T09:53:30.319390+00:00
Background Media Recovery process shutdown (dgtest2)
2021-09-20T09:53:30.981370+00:00
RSM0 (PID:11547): Terminal Recovery: Stopped real time apply
2021-09-20T09:53:30.986937+00:00
Attempt to do a Terminal Recovery (dgtest2)
TMI: adbdrv termRecovery END 2021-09-20 09:53:30.986957
2021-09-20T09:53:30.987291+00:00
Media Recovery Start: Managed Standby Recovery (dgtest2)
2021-09-20T09:53:30.992470+00:00
Serial Media Recovery started
RSM0 (PID:11547): Managed Standby Recovery not using Real Time Apply
max_pdb is 3
Stopping change tracking
RSM0 (PID:11547): Begin: SRL archival
RSM0 (PID:11547): End: SRL archival
RSM0 (PID:11547): Terminal Recovery timestamp is '09/20/2021 09:53:31'
RSM0 (PID:11547): Terminal Recovery: applying standby redo logs.
RSM0 (PID:11547): Terminal Recovery: thread 1 seq# 382 redo required
2021-09-20T09:53:31.259258+00:00
RSM0 (PID:11547): Terminal Recovery:
2021-09-20T09:53:31.264259+00:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 382 Reading mem 0
  Mem# 0: +FRA/DGTEST2/ONLINELOG/group_5.423.1067949477
Terminal Recovery finished with No-Data-Loss
2021-09-20T09:53:31.297911+00:00
Incomplete Recovery applied until change 4236456 time 09/20/2021 09:52:41
2021-09-20T09:53:31.312164+00:00
Media Recovery Complete (dgtest2)
Terminal Recovery: successful completion
RSM0 (PID:11547): Forcing ARSCN to IRSCN for TR SCN:0x000000000040a4a8
RSM0 (PID:11547): Attempt to set limbo arscn SCN:0x000000000040a4a8 irscn SCN:0x000000000040a4a8
RSM0 (PID:11547): Resetting standby activation ID 4040268239 (0xf0d199cf)
Stopping change tracking
2021-09-20T09:53:31.464571+00:00
ALTER DATABASE SWITCHOVER TO PRIMARY (dgtest2)
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-09-20 09:53:31.466893
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-09-20 09:53:31.467051
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-09-20 09:53:31.468362
Backup controlfile written to trace file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_rsm0_11547.trc
Standby terminal recovery start SCN: 4236455
RESETLOGS after incomplete recovery UNTIL CHANGE 4236456 time 09/20/2021 09:52:41
RSM0 (PID:11547): ORL pre-clearing operation disabled by switchover
Online log +DATA/DGTEST2/ONLINELOG/group_1.289.1067949469: Thread 1 Group 1 was previously cleared
Online log +FRA/DGTEST2/ONLINELOG/group_1.419.1067949471: Thread 1 Group 1 was previously cleared
Online log +DATA/DGTEST2/ONLINELOG/group_2.290.1067949471: Thread 1 Group 2 was previously cleared
Online log +FRA/DGTEST2/ONLINELOG/group_2.420.1067949473: Thread 1 Group 2 was previously cleared
Online log +DATA/DGTEST2/ONLINELOG/group_3.291.1067949473: Thread 1 Group 3 was previously cleared
Online log +FRA/DGTEST2/ONLINELOG/group_3.421.1067949473: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4236454
2021-09-20T09:53:31.635945+00:00
Setting recovery target incarnation to 2
2021-09-20T09:53:31.673002+00:00
RSM0 (PID:11547): RT: Role transition work is not done
RSM0 (PID:11547): The Time Management Interface (TMI) is being enabled for role transition
RSM0 (PID:11547): information.  This will result in messages beingoutput to the alert log
RSM0 (PID:11547): file with the prefix 'TMI: '.  This is being enabled to make the timing of
RSM0 (PID:11547): the various stages of the role transition available for diagnostic purposes.
RSM0 (PID:11547): This output will end when the role transition is complete.
RSM0 (PID:11547): Redo network throttle feature is disabled at mount time
2021-09-20T09:53:31.710578+00:00
RSM0 (PID:11547): Database role cleared from PHYSICAL STANDBY [kcvs.c:1099]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2021-09-20 09:53:31.712243
TMI: dbsdrv failover to target END 2021-09-20 09:53:31.712367
Failover completed with No-Data-Loss.
Completed: ALTER DATABASE FAILOVER TO dgtest2
2021-09-20T09:53:31.975876+00:00
RSM0 (PID:11547): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18222]
2021-09-20T09:53:31.976280+00:00
ARC9 (PID:11172): Becoming the 'no SRL' ARCH
2021-09-20T09:53:31.983848+00:00
ALTER SYSTEM SET log_archive_dest_2='service="dgtesttns"','SYNC AFFIRM delay=0 optional compression=disable max_fai                                                                                                                          lure=0 reopen=300 db_unique_name="dgtest" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
2021-09-20T09:53:32.015296+00:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
ALTER DATABASE OPEN
2021-09-20T09:53:32.030768+00:00
TMI: adbdrv open database BEGIN 2021-09-20 09:53:32.030695
Data Guard Broker initializing...
Ping without log force is disabled:
  instance mounted in exclusive mode.
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED
2021-09-20T09:53:32.116297+00:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2021-09-20T09:53:32.116442+00:00
Crash Recovery excluding pdb 3 which was cleanly closed.
Endian type of dictionary set to little
2021-09-20T09:53:32.136779+00:00
Assigning activation ID 4040238735 (0xf0d1268f)
LGWR (PID:10754): Primary database is in MAXIMUM AVAILABILITY mode
2021-09-20T09:53:32.140956+00:00
LGWR (PID:10754): LAD:2 is UNSYNCHRONIZED
LGWR (PID:10754): LAD:1 is not serviced by LGWR
2021-09-20T09:53:33.177813+00:00
Thread 1 advanced to log sequence 2 (thread open)
Redo log for group 2, sequence 2 is not located on DAX storage
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: +DATA/DGTEST2/ONLINELOG/group_2.290.1067949471
  Current log# 2 seq# 2 mem# 1: +FRA/DGTEST2/ONLINELOG/group_2.420.1067949473
Successful open of redo thread 1
2021-09-20T09:53:33.246706+00:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
2021-09-20T09:53:33.287479+00:00
ARC1 (PID:11156): Archived Log entry 520 added for T-1.S-1 ID 0xf0d1268f LAD:1
2021-09-20T09:53:33.290677+00:00
TT03 (PID:12462): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2021-09-20T09:53:33.549541+00:00
Undo initialization recovery: Parallel FPTR failed: start:740398 end:740405 diff:7 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 740397 end: 740435 diff: 38 ms (0.0 seconds)
[11547] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 740435 end: 740959 diff: 524 ms (0.5 seconds)
Undo initialization finished serial:0 start:740397 end:740967 diff:570 ms (0.6 seconds)
Dictionary check beginning
Dictionary check complete
2021-09-20T09:53:34.245425+00:00
Database Characterset is AL32UTF8
No Resource Manager plan active
2021-09-20T09:53:35.468394+00:00
joxcsys_required_dirobj_exists: directory object exists with required path /opt/app/oracle/product/19.x.0/dbhome_2/                                                                                                                          javavm/admin/, pid 11547 cid 1
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2021-09-20T09:53:36.133706+00:00
AQPC started with pid=70, OS id=12472
PDB$SEED(2):Pluggable database PDB$SEED opening in read only
2021-09-20T09:53:36.942191+00:00
PDB$SEED(2):Autotune of undo retention is turned on.
2021-09-20T09:53:37.055480+00:00
TT03 (PID:12462): Enabling archival of deferred physical standby SRLs
2021-09-20T09:53:37.152868+00:00
TT03 (PID:12462): Archived Log entry 521 added for T-1.S-382 ID 0xf0d199cf LAD:1
2021-09-20T09:53:37.246644+00:00
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:744276 end:744276 diff:0 ms (0.0 seconds)
PDB$SEED(2):Pluggable database PDB$SEED dictionary check beginning
2021-09-20T09:53:38.232036+00:00
Thread 1 advanced to log sequence 3 (LGWR switch),  current SCN: 4236634
  Current log# 3 seq# 3 mem# 0: +DATA/DGTEST2/ONLINELOG/group_3.291.1067949473
  Current log# 3 seq# 3 mem# 1: +FRA/DGTEST2/ONLINELOG/group_3.421.1067949473
2021-09-20T09:53:38.316860+00:00
PDB$SEED(2):Pluggable Database PDB$SEED Dictionary check complete
2021-09-20T09:53:38.327245+00:00
ARC5 (PID:11164): Archived Log entry 522 added for T-1.S-2 ID 0xf0d1268f LAD:1
2021-09-20T09:53:38.328930+00:00
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2021-09-20T09:53:38.503847+00:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2021-09-20T09:53:38.642191+00:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2021-09-20T09:53:38.835626+00:00
PDB$SEED(2):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2021-09-20T09:53:39.622239+00:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN

After the failover completes the data guard broker status shows old primary is disabled and need reinstating.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 40 seconds ago)

If the old primary issue is rectified it would be reinstated as a standby (instead of adding as a new standby). When the old primary is started the DG broker will detect failover has happened and will prevent the opening the opening of the DB in read/write mode.

Completed: ALTER DATABASE MOUNT /* db agent *//* {0:0:312} */
ALTER DATABASE OPEN /* db agent *//* {0:0:312} */
Data Guard Broker initializing...
2021-09-20T09:58:19.408297+00:00
Starting Data Guard Broker (DMON)
Starting background process INSV
2021-09-20T09:58:19.425650+00:00
INSV started with pid=49, OS id=5982
2021-09-20T09:58:23.448529+00:00
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
2021-09-20T09:58:23.467893+00:00
Starting background process NSV1
2021-09-20T09:58:23.484569+00:00
NSV1 started with pid=50, OS id=5987
2021-09-20T09:58:27.484306+00:00
Starting background process NSV2
2021-09-20T09:58:27.501947+00:00
NSV2 started with pid=51, OS id=5992
2021-09-20T09:58:30.605237+00:00
Starting background process RSM0
2021-09-20T09:58:30.620725+00:00
RSM0 started with pid=52, OS id=5997
2021-09-20T09:58:31.448386+00:00
Data Guard: broker startup completed
Data Guard determines a failover has occurred - instance will not be opened
ORA-16649 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:0:312} */...

The database will be left at mount mode.

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
dgtest3                        MOUNTED

From the current primary DG broker run the reinstate command.

DGMGRL> reinstate database dgtest3
Reinstating database "dgtest3", please wait...
Reinstatement of database "dgtest3" succeeded

During the reinstate process the old primary will be flashback to the time where failover occured and converted to a physical standby.

2021-09-20T09:59:51.612704+00:00
FLASHBACK DATABASE TO SCN 4236454
2021-09-20T09:59:51.774069+00:00
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
 Started logmerger process
2021-09-20T09:59:52.058152+00:00
max_pdb is 3
2021-09-20T09:59:52.297907+00:00
Parallel Media Recovery started with 8 slaves
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_373.332.1083750351
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_374.264.1083750365
2021-09-20T09:59:52.812785+00:00
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_375.265.1083750375
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_376.267.1083750649
2021-09-20T09:59:53.157016+00:00
Media Recovery End-Of-Redo indicator encountered
2021-09-20T09:59:53.157085+00:00
Media Recovery Continuing
2021-09-20T09:59:53.202162+00:00
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_377.271.1083750659
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_378.269.1083750671
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_379.268.1083750683
2021-09-20T09:59:53.834774+00:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 380 Reading mem 0
  Mem# 0: +DATA/DGTEST3/ONLINELOG/group_1.299.1069533175
  Mem# 1: +FRA/DGTEST3/ONLINELOG/group_1.460.1069533177
2021-09-20T09:59:53.955443+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 381 Reading mem 0
  Mem# 0: +DATA/DGTEST3/ONLINELOG/group_2.300.1069533177
  Mem# 1: +FRA/DGTEST3/ONLINELOG/group_2.459.1069533177
2021-09-20T09:59:54.017466+00:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 382 Reading mem 0
  Mem# 0: +DATA/DGTEST3/ONLINELOG/group_3.301.1069533179
  Mem# 1: +FRA/DGTEST3/ONLINELOG/group_3.458.1069533179
2021-09-20T09:59:54.086085+00:00
Incomplete Recovery applied until change 4236455 time 09/20/2021 09:52:41
2021-09-20T09:59:54.094097+00:00
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 4236454
alter database convert to physical standby
2021-09-20T09:59:54.263414+00:00
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (dgtest3)
Clearing standby activation ID 4040262352 (0xf0d182d0)
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 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Offline data file 2 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 4 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 6 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
RSM0 (PID:5997): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8936]
2021-09-20T09:59:54.325231+00:00
RSM0 (PID:5997): Waiting for all non-current ORLs to be archived
2021-09-20T09:59:54.325324+00:00
RSM0 (PID:5997): All non-current ORLs have been archived
RSM0 (PID:5997): Clearing online redo logfile 1 +DATA/DGTEST3/ONLINELOG/group_1.299.1069533175
RSM0 (PID:5997): Clearing online redo logfile 2 +DATA/DGTEST3/ONLINELOG/group_2.300.1069533177
RSM0 (PID:5997): Clearing online redo logfile 3 +DATA/DGTEST3/ONLINELOG/group_3.301.1069533179
Clearing online log 1 of thread 1 sequence number 380
Clearing online log 2 of thread 1 sequence number 381
Clearing online log 3 of thread 1 sequence number 382
2021-09-20T10:00:03.959609+00:00
RSM0 (PID:5997): Clearing online redo logfile 1 complete
RSM0 (PID:5997): Clearing online redo logfile 2 complete
RSM0 (PID:5997): Clearing online redo logfile 3 complete
RSM0 (PID:5997): RT: Role transition work is not done
RSM0 (PID:5997): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2021-09-20T10:00:03.970236+00:00
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby

At the same time the DG broker will add log archive destination on the primary for the reinstated physical standby.

ALTER SYSTEM SET log_archive_dest_3='service="dgtest3tns"','SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="dgtest3" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;

The other standby will get an update on its fal_server with reference to the reinstated standby.

ALTER SYSTEM SET fal_server='dgtest2tns','dgtest3tns' SCOPE=BOTH;

At the end the data guard configuration will have same number of standbys as before but with a different primary.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 58 seconds ago)

Saturday, August 14, 2021

ORA-16713: The Oracle Data Guard broker command timed out When Changing LogXptMode

After setting up a new dataguard configuration and broker the change of log transport mode to sync fail as below.

DGMGRL> edit database mystby set property LogXptMode='SYNC';
Error: ORA-16713: The Oracle Data Guard broker command timed out.

Failed.

Sync mode was needed to change the protection mode. There was no other DG issue and redo transport and log apply was working fine. Transport mode change was only failing on the standby at that time. However, the sync mode parameters gets setup on the primary's log archive destination. Log transport mode of the two instances looks like below due to this failure.

  Transport-Related Property Settings:
    Property                        myprod Value             mystby Value
    LogXptMode                      SYNC                     ASYNC

MOS doc 2007507.1 says this is due to rsm* process hanging and to kill it so it will respawn. This didn't help.

 ps ax | grep rsm
26686 pts/3    S+     0:00 grep --color=auto rsm
30386 ?        Ss     0:01 ora_rsm0_myprod
$ kill -9 30386

 ps ax | grep rsm
27157 ?        Ss     0:00 ora_rsm0_myprod  <--------- new process spawn
27161 pts/3    S+     0:00 grep --color=auto rsm

DGMGRL> edit database mystby set property LogXptMode='SYNC';
Error: ORA-16713: The Oracle Data Guard broker command timed out.

Failed.

MOS doc 1322877.1 suggested increasing OperationTimeout. That too didn't help.

DGMGRL>  EDIT CONFIGURATION SET PROPERTY OperationTimeout=600;
Property "operationtimeout" updated

DGMGRL> edit database mystby set property LogXptMode='SYNC';
Error: ORA-16713: The Oracle Data Guard broker command timed out.

Failed.

Few other MOS suggested clearing up ARDCI location. But those docs were related to DG broker hanging during validation. In this case validation ran fine without any issue.

Finally what resolved the issue was disabling the configuration and updating the log transport mode and enabling it again (maybe this respwaned the rsm cleanly than killing it).

DGMGRL> disable configuration;
Disabled.

DGMGRL> edit database mystby set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> enable configuration;
Enabled.

This worked as expected. The transprot node changes are now visible on the log_archive_dest on the primary.

log_archive_dest_3   string service="mystbytns", SYNC AFFIRM delay=0 optional c

  Transport-Related Property Settings:
    Property                        myprod Value             mystby Value
    LogXptMode                      SYNC                     SYNC

After this it was possible to change the protection mode.

Monday, March 22, 2021

Removing a Standby Database From a Data Guard Configuration With RedoRoutes

There is an earlier post which shows how to remove a standby database from a data guard configuration when redo routes are not used. This post shows steps for removing standby databases when redo routes are in use.
The existing data guard setup is as follows.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2  - Primary database
    db1  - Physical standby database
    db3  - Physical standby database
      db4  - Physical standby database (receiving current redo)
    fs1  - Far sync instance
      db5  - Physical standby database
      db6  - Physical standby database
      db7  - Physical standby database
      db8  - Physical standby database
      db9  - Physical standby database
      db10 - Physical standby database

  Members Not Receiving Redo:
  fs2  - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

It was decided to remove standby databases db7-db10 and far sync instance fs2 from the data guard configuration. Simply running the remove database command will result in an error due to references in the redoroutes for the standby databases that are being removed.

DGMGRL> remove database db10;
Error: ORA-16691: cannot remove a configuration member that is specified in a RedoRoutes property

Failed.

If the redoroute property of the databases that will remain the data guard configuration is to be checked then references to databases being removed could be found.

DGMGRL> show database db2 redoroutes
  RedoRoutes = '(LOCAL : ( db1 SYNC), ( db3 SYNC PRIORITY=1, db4 SYNC PRIORITY=2),( fs1 SYNC PRIORITY=1, fs2 SYNC PRIORITY=2, db5 ASYNC, db6 ASYNC, db7 ASYNC, db8 ASYNC, db9 ASYNC, db10 ASYNC))'

Therefore before the standby databases are removed update the redoroutes of the databases that will remain in the data guard, by removing any references to those standby databsaes that are being removed. One exmaple redoroute update for above data guard configuration is given below.

edit database  db1 set property redoroutes='(LOCAL : ( db2 SYNC), ( db3 SYNC PRIORITY=1,  db4 SYNC PRIORITY=2),( fs1 SYNC PRIORITY=1,  fs2 SYNC PRIORITY=2,  db5 ASYNC, db6 ASYNC))( db3 :  db2 ASYNC)( db4 :  db2 ASYNC)';

Once all the redo routes are updated the data guard configuratoin may show error state while it revalidate, however, the error state could be ignored and could proceed with the removal of the standby databases.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2  - Primary database
    db1  - Physical standby database
    db3  - Physical standby database
      db4  - Physical standby database (receiving current redo)
    fs1  - Far sync instance
      db5  - Physical standby database
      db6  - Physical standby database

  Members Not Receiving Redo:
  db7  - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  db8  - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  db9  - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  db10 - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  fs2  - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 71 seconds ago)

DGMGRL> remove database db10;
Removed database " db10" from the configuration
DGMGRL> remove database db9;
Removed database " db9" from the configuration
DGMGRL> remove database db8;
Removed database " db8" from the configuration
DGMGRL> remove database db7
Removed database " db7" from the configuration

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2 - Primary database
    db1 - Physical standby database
    db3 - Physical standby database
      db4 - Physical standby database (receiving current redo)
    fs1 - Far sync instance
      db5 - Physical standby database
      db6 - Physical standby database

  Members Not Receiving Redo:
  fs2 - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

Similarly once all references to far sync instance (fs2) is removed from existing redoroutes that too could be removed from the data guard configuraiton. Redo route update without any reference to fs2 on one of the databases is given below.

edit database  db1 set property redoroutes='(LOCAL : ( db2 SYNC), ( db3 SYNC PRIORITY=1,  db4 SYNC PRIORITY=2),( fs1 SYNC PRIORITY=1,  db5 ASYNC, db6 ASYNC))( db3 :  db2 ASYNC)( db4 :  db2 ASYNC)';

Execute remove far_sync to remove the far sync instance.

DGMGRL> remove far_sync fs2;
Removed far sync instance " fs2" from the configuration
DGMGRL>  show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2 - Primary database
    db1 - Physical standby database
    db3 - Physical standby database
      db4 - Physical standby database (receiving current redo)
    fs1 - Far sync instance
      db5 - Physical standby database
      db6 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 64 seconds ago)

Saturday, August 8, 2020

Removing a Failed Standby Database From a Data Guard Configuration

A previous post explained steps for removing a standby instance from a data guard configuration. This post explains steps for the same but when the standby being removed has failed and cannot be reached (or connect into).
In a standby configuration with multiple standby databases once instance is unreachable due to hardware failure. The issue is irrecoverable and only option is to rebuild the node and the standby instance. In mean time the existing standby configuration will give an error state due to the unavailability of the failed instance.

DGMGRL> show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)
  ppdb8  - Physical standby database
    Error: ORA-12170: TNS:Connect timeout occurred

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 96 seconds ago)

As the first step remove any references to the failed instance on RedoRoutes.

Then issue the remove command which will succeed with a warning.

DGMGRL> remove database ppdb8;
Warning: ORA-16620: one or more members could not be reached for a remove operation

Removed database "ppdb8" from the configuration

The warning is due to broker being unable to connect to the failed instance to execute the clean up commands. The dataguard broke log shows this.

2020-07-29T12:33:36.403+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Metadata Resync failed. Status = ORA-12170
2020-07-29T12:33:48.691+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration fc_pp_dg Warning ORA-16607: one or more members have failed
Primary Database ppdb1 Success ORA-0: normal, successful completion
Physical Standby Database ppdb2 Success ORA-0: normal, successful completion
Physical Standby Database ppdb3 Success ORA-0: normal, successful completion
Physical Standby Database ppdb4 Success ORA-0: normal, successful completion
Physical Standby Database ppdb5 Success ORA-0: normal, successful completion
Physical Standby Database ppdb6 Success ORA-0: normal, successful completion
Far Sync Instance ppfs1 Success ORA-0: normal, successful completion
Far Sync Instance ppfs2 Success ORA-0: normal, successful completion
Physical Standby Database ppdb8 Error ORA-12170: TNS:Connect timeout occurred
Physical Standby Database ppdb9 Success ORA-0: normal, successful completion
Physical Standby Database ppdb10 Success ORA-0: normal, successful completion
2020-07-29T12:34:00.979+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:05.646+00:00
REMOVE DATABASE ppdb8
2020-07-29T12:34:17.939+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Database ppdb8 (0x0a001000) could not be contacted for database removal, status = ORA-12170
2020-07-29T12:34:31.571+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:33.297+00:00
Database ppdb8 removal completed with warning ORA-16620
REMOVE DATABASE completed with warning ORA-16620

However, all the other databases that are part of the dataguard configuration would have had their log_archive_config parameter updated by removing any reference to the failed database.

NAME                           VALUE
------------------------------ -----------------------------------------
log_archive_config             dg_config=(ppdb1,ppdb2,ppdb3,ppdb4,ppfs1,
                               ppdb5,ppdb6,ppdb9,ppdb10,ppfs2)

Once the failed instance is removed the dataguard broke shows status success.

DGMGRL>  show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

Monday, October 1, 2018

Converting a Physical Standby to a Snapshot Standby

Converting a physical standby to a snapshot standby allows it to be open for read/write access. This could enable various types of testing to be carried out on the snapshot standby. This post list steps for converting a physical standby to snapshot standby and then convert it back to a physical standby.
1. Current data guard configuration is as follows.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2. It is not necessary to have flashback enabled for snapshot to work. As per Oracle documentation "if Flashback database is disabled, it is automatically enabled during conversion to a snapshot standby database. The broker automatically restarts the database to the mounted state if it had been opened with Flashback Database disabled. No user action is required". However, a fast recovery area must be configured on the physical standby. According to Oracle documentation "this is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area". For this post the flashback feature was deliberately turned off to verify that snapshot conversion can go through without it. However, it's good practice to have flashback enabled in a DG configuration. Turning off flashback is not part of converting to snapshot standby and no need to do the below steps.
On primary

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;
Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

On standby

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database flashback off;
Database altered.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

3. Conversion to snapshot standby is done using the DGMGRL.

DGMGRL> convert database stdby to snapshot standby;
Converting database "stdby" to a Snapshot Standby database, please wait...
Database "stdby" converted successfully

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Use of DGMGRL automate lot of the steps needed for conversion such as stopping the redo apply and initializing DG after the conversion. These steps could be identified by looking at the standby alert log.

Wed Sep 19 16:22:13 2018
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Sep 19 16:22:13 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /opt/app/oracle/diag/rdbms/stdby/stdby/trace/stdby_pr00_2301.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 16269612
Wed Sep 19 16:22:13 2018
MRP0: Background Media Recovery process shutdown (stdby)
Managed Standby Recovery Canceled (stdby)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
alter database convert to snapshot standby
Starting background process RVWR
Wed Sep 19 16:22:14 2018
RVWR started with pid=34, OS id=2429
Wed Sep 19 16:22:15 2018
Archived Log entry 4995 added for thread 1 sequence 399 ID 0x172868bb dest 1:
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_09/19/2018 16:22:14
Killing 1 processes with pids 2334 (all RFS) in order to disallow current and fu                                                                                                              ture RFS connections. Requested by OS process 2293
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 16269612
Resetting resetlogs activation ID 388524219 (0x172868bb)
Online log +DATA/stdby/onlinelog/group_1.264.964883451: Thread 1 Group 1 was previously cleared
Online log +FRA/stdby/onlinelog/group_1.258.964883451: Thread 1 Group 1 was previously cleared
...
Standby became primary SCN: 16269610
Wed Sep 19 16:22:18 2018
Setting recovery target incarnation to 6
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete

4. After physical standby is converted to snapshot standby it will not carry out redo apply. However, it will continue to receive redo from primary thus protecting the primary in the event of failure. This could be verified by querying managed standby view. On standby run

SQL> select status,sequence#,block# from v$managed_standby where client_process='LGWR';

STATUS        SEQUENCE#     BLOCK#
------------ ---------- ----------
IDLE                402        409

Make a note of block and sequence number. Execute a log switch on primary and query the standby again, which would show change in sequence# and/or block#. This indicate redo shipping is working as expected.

STATUS        SEQUENCE#     BLOCK#
------------ ---------- ----------
IDLE                403          4

5. At this point the snapshot standby is open for read/write. Carry out any testing needed. Once done with testing convert back to physical standby. Any changes done on snapshot standby DB will be lost once converted back to physical standby.

6. Connect to DGMGRL from primary and execute the convert command.

DGMGRL> convert database stdby to physical standby;
Converting database "stdby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "stdby" on database "stdby"
Shutting down instance "stdby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby" on database "stdby"
Starting instance "stdby"...
ORACLE instance started.
Database mounted.
Continuing to convert database "stdby" ...
Operation requires shutdown of instance "stdby" on database "stdby"
Shutting down instance "stdby"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "stdby" on database "stdby"
Starting instance "stdby"...
ORACLE instance started.
Database mounted.
Database "stdby" converted successfully

7. Finally verify the DG status and redo apply has begun.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Databases:
    prod  - Primary database
    stdby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database stdby

Database - stdby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      95.00 KByte/s
  Real Time Query: OFF
  Instance(s):
    stdby

Database Status:
SUCCESS

Saturday, September 8, 2018

DG Broker Continue With Status "ROLLING DATABASE MAINTENANCE IN PROGRESS" Even After ROLLBACK_PLAN is Called

With Oracle 12c a new PL/SQL package called DBMS_ROLLING was provided which greatly automate the rolling upgrade of databases with minimum down time and maximum protection. Refer MOS 2086512.1 and the white paper from MAA. Using of DBMS_ROLLING requires active data guard license option. DBMS_ROLLING could only be used on versions 12.1.0.2 or above. For other versions use transient logical standby method for upgrade.
The database version used for this post is 12.2.0.1. Prior releases required disabling DG broker when dbms_rolling was used. But there's improvements in 12.2 version according to Oracle documentaion. As of Oracle Database 12c Release 2 (12.2.0.1), Data Guard broker can remain on during a DBMS_ROLLING rolling upgrade; there is no longer any need to disable it.
Broker support is enabled by default during execution of the DBMS_ROLLING.BUILD_PLAN procedure if the broker is enabled at the time of the call. When broker support is enabled, the broker sets up the redo transport destinations as necessary from the original primary database as well as from the rolling upgrade target, manages instance count on the upgrade target if it is an Oracle RAC database, and notifies Oracle Clusterware and Global Data Services as appropriate during the course of the rolling upgrade.
Broker support can be manually controlled using the DBMS_ROLLING parameter, DG BROKER. Although role transitions are typically performed using the broker, the switchover step in a rolling upgrade should continue to be performed using the DBMS_ROLLING.SWITCHOVER procedure.
Further more the broker admin doc states "When an upgrade is performed using the PL/SQL package, DBMS_ROLLING, the DG_BROKER_CONFIG_FILEn parameters must specify a location outside of Oracle home. (The default location is the dbs directory in Oracle home.)"
The current data guard configuration consists of two databases and DG broker status is shown below.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 44 seconds ago)

The supplement log status is as below.

select SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_PL 
from v$database;

SUP SUPPLEME SUP SUP SUP SUP
--- -------- --- --- --- ---
NO  NO       NO  NO  NO  NO

In previous version after the dbms_rolling upgrade supplement log setting had to be manually rolled back. But with 12.2 this was not necessary as seen later in the post.
This post shows steps up to starting the upgrade plan and then rolling back. In the critical path after the start plan one would move onto switchover. Rollback cannot be called if switchover has happened (Refer page 5 on white paper mentioned above).
1. To begin the upgrade using dbms_rolling put the standby in mount mode and run init_plan. As there's only two databases in the configuration, the only standby is made to be the future primary.

SQL> exec DBMS_ROLLING.INIT_PLAN(future_primary=>'london');
PL/SQL procedure successfully completed.

Check the plan parameter status with

select scope, name, curval from dba_rolling_parameters order by scope, name;

2. Next execute the build plan and check the plan values.

SQL> exec DBMS_ROLLING.BUILD_PLAN;
PL/SQL procedure successfully completed.

SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;

3. Run the start plan to begin the conversion of the physical standby to a transient logical standby.

SQL> exec DBMS_ROLLING.START_PLAN;
PL/SQL procedure successfully completed.

This step does following things.

i. Backing up the control file for each database to a trace file. These could be verified on the alert logs of primary and standby. On primary alert log

RTS(17286): executing rolling upgrade instruction 26 from plan revision 1
alter database backup controlfile to 'rolling_change_backup.f' reuse
Completed: alter database backup controlfile to 'rolling_change_backup.f' reuse

On standby alert log

RTS(2699): executing rolling upgrade instruction 28 from plan revision 1
alter database backup controlfile to 'rolling_change_backup.f' reuse
Completed: alter database backup controlfile to 'rolling_change_backup.f' reuse

ii. Creating flashback database guaranteed restore points both on primary and standby. Alert logs entries similar to below could be seen on both databases.

2018-08-16T06:32:40.016320-04:00
RTS(17286): executing rolling upgrade instruction 22 from plan revision 1
Created guaranteed restore point DBMSRU_INITIAL

iii. Building a LogMiner dictionary at the primary database. Following entries could be seen on primary alert log

Thu Aug 16 06:32:49 2018
Logminer Bld: Done
LOGMINER: PLSQL Quiesce mode enabled
LOGMINER: Dictionary Build: Waiting for completion of transactions started at or before scn 11927360 (0x0000000000b5ff40)
LOGMINER: Dictionary Build: All transactions started at or before scn 11927360 (0x0000000000b5ff40) have completed
2018-08-16T06:32:49.308962-04:00
LOGSTDBY: Dictionary Build PLSQL Quiesce - SID: 2268, wait_init: 300 sec, wait_timeout: 3600 sec.

iv. Recovering the designated physical standby into a transient logical standby database. Following could be seen on standby alert log.

RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
Completed: alter database recover to logical standby keep identity

At this stage the DG broker will show standby as a transient logical standby database and give a warning status as rolling database maintenance is active. Importantly (reason for this post) the DG broker status says "ROLLING DATABASE MAINTENANCE IN PROGRESS".

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Transient logical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS


DGMGRL> show database london

Database - london

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          2 seconds (computed 0 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    london

  Database Warning(s):
    ORA-16866: database converted to transient logical standby database for rolling database maintenance

Database Status:
WARNING

v. Loading the LogMiner dictionary into the logical standby database. Following entries could be seen on standby alert log.

LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 267, +FRA/LONDON/foreign_archivelog/colombo/2018_08_16/thread_1_seq_267.365.984292375
LOGMINER: Preparing to load 1 dictionaries for session 1
LOGMINER: Finalizing dictionary load for session 1
LOGMINER: Gathering statistics on logminer dictionary. (incremental, nonparallel)
LOGMINER: Completed dictionary load for session 1
LOGMINER: End mining logfiles during dictionary load for session 1

vi. Configuring LGS databases with the transient logical standby database. As there was no other standbys in the configuration, no leading group standbys (LGS) to configure.

4. Ideally from this point one would upgrade the standby to new DB version and do a switchover and upgrade the trailing group master. But if for some reason rolling back is needed it has to happen before the switchover is done. This post is based on an issue encountered after the rollback. In order to rollback the steps done by dbms_rolling put the standby DB in mount mode and execute the rollback_plan.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE      OPEN_MODE
----------------   -----------
LOGICAL STANDBY    READ WRITE

SQL> startup force mount;

SQL> select open_mode from v$database;

OPEN_MODE
-----------
MOUNTED

SQL> exec DBMS_ROLLING.ROLLBACK_PLAN;
PL/SQL procedure successfully completed.

5. Rollback command will convert the transient logical standby to a physical standby and also rollback supplemental logging settings. If rollback failed then supplemental logging data setting must be manually cleared.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------
PHYSICAL STANDBY MOUNTED

select SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_PL 
from v$database;

SUP SUPPLEME SUP SUP SUP SUP
--- -------- --- --- --- ---
NO  NO       NO  NO  NO  NO

6. End the upgrade plan with a call to destroy plan.

SQL> exec DBMS_ROLLING.DESTROY_PLAN;
PL/SQL procedure successfully completed.

7. Though the databases are back to the state they were before the upgrade began, the data guard broker continue to say "ROLLING DATABASE MAINTENANCE IN PROGRESS".

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database

  Members Not Receiving Redo:
  london  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS

Even though standby is listed as not receiving redo, this is not actually the case. Apply on status is shown on DG broker output and redo apply could be verified by looking at the standby alert log.

DGMGRL> show database london

Database - london

  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: 10.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    london

Database Status:
SUCCESS

Validate database also does not give any errors.

DGMGRL> validate database london

  Database Role:     Physical standby database
  Primary Database:  colombo

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    colombo:  YES
    london :  YES

The reason for DG broker to output an erroneous message is the notifying of the dbms_rolling ending is included only in the finish phase.

SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;
...
83 colombo    FINISH         Notify Data Guard broker that DBMS_ROLLING has finished
84 london     FINISH         Notify Data Guard broker that DBMS_ROLLING has finished

Rollback and destroy phases has no provisions to notify the DG Broker that dbms_rolling has finished. Due to this DG broker continue to output an incorrect status. As per MOS 2347179.1 this has been identified as a bug (26921039) which is fixed in 18c but no backport available for 12c.

Even though DG broker gives a incorrect status, this doesn't affect the ability to switchover.

Connected to "colombo"
Connected as SYSDG.
DGMGRL> switchover to london
Performing switchover NOW, please wait...
Operation requires a connection to database "london"
Connecting ...
Connected to "london"
Connected as SYSDBA.
New primary database "london" is opening...
Oracle Clusterware is restarting database "colombo" ...
Switchover succeeded, new primary is "london"
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  london  - Primary database

  Members Not Receiving Redo:
  colombo - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
    ROLLING DATABASE MAINTENANCE IN PROGRESS


DGMGRL> validate database colombo

  Database Role:     Physical standby database
  Primary Database:  london

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    london :  YES
    colombo:  YES

  Log Files Cleared:
    london Standby Redo Log Files:   Cleared
    colombo Online Redo Log Files:   Not Cleared
    colombo Standby Redo Log Files:  Available


DGMGRL> switchover to colombo
Performing switchover NOW, please wait...
Operation requires a connection to database "colombo"
Connecting ...
Connected to "colombo"
Connected as SYSDBA.
New primary database "colombo" is opening...
Oracle Clusterware is restarting database "london" ...
Switchover succeeded, new primary is "colombo"
DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  colombo - Primary database
    london  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

Only workaround mentioned in 2347179.1 to clear this incorrect DG broker status message is to restart the primary which will incur an unnecessary downtime. Testing showed that restarting the primary does clear the incorrect status message. As seen above outputs, even a switchover also does the same (as it results in DB being restarted).
Alternatively recreating the DG broker also cleared the incorrect DG broker status. This seems to be a better option as it doesn't require a down time.

Tuesday, August 21, 2018

Removing a Standby Database From a Data Guard Configuration

The post shows the steps for removing a standby database from a data guard configuration and make it available for read/write access. The data guard configuration consists of two standby databases.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    stbycdb  - Physical standby database
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 17 seconds ago)

The standby database named "stby2cdb" is to be removed from the configuration. This is achieved by the use of "remove database" command in the data guard broker. If "preserve destination" clause is not used then this command remove all reference to the database being removed from other databases that will remain the data guard configuration. The default behaviour is to remove all references to the removed database so simply run the remove command with the database name

DGMGRL> remove database stby2cdb;
Removed database "stby2cdb" from the configuration

The data guard configuration reflect the change.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 26 seconds ago)

On the alert log of the primary (prodcdb) it could be seen that log_archive_config and log_archive_destination parameter being modified. The commands to update these parameters are issue by the data guard broker.

2018-08-06T12:05:04.410188+01:00
Destination LOG_ARCHIVE_DEST_3 no longer supports SYNCHRONIZATION
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
2018-08-06T12:05:04.433444+01:00
ALTER SYSTEM SET log_archive_dest_3='' SCOPE=BOTH;
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(prodcdb,stbycdb)']
2018-08-06T12:05:04.476398+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(prodcdb,stbycdb)' SCOPE=BOTH;
2018-08-06T12:05:06.540027+01:00
ALTER SYSTEM SWITCH ALL LOGFILE start (prodcdb)

On the alert log of the remaining standby (stbycdb) it could be seen that log_archive_config parameter is updated to reflect only the databases that remain on the data guard configuration.

2018-08-06T13:38:40.813124+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(stbycdb,prodcdb)']
2018-08-06T13:38:40.886959+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(stbycdb,prodcdb)' SCOPE=BOTH;

On the alert log of the database that was removed (stby2cdb), the log_archive_config and fal_server parameter values were updated by removing any references to the database in the DG configuration.

2018-08-06T14:31:27.917074+01:00
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
2018-08-06T14:31:27.927364+01:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;

Though these DG related parameters were updated and reset all databases, the db/log file name convert related value remain the same Following output is from production DB.

DGMGRL> show database prodcdb  LogFileNameConvert
  LogFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'

DGMGRL>  show database prodcdb DbFileNameConvert
  DbFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'

Update DbFileNameConvert and LogFileNameConvert values on all databases that remain in DG configuration by removing any references to the removed database. The changes require a restart of the database to take effect.

DGMGRL> edit database prodcdb  set property 'LogFileNameConvert'='/stbycdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "LogFileNameConvert" updated

DGMGRL>  edit database prodcdb  set property 'DbFileNameConvert'='/stbycdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "DbFileNameConvert" updated

DGMGRL>  edit database stbycdb set property 'DbFileNameConvert'='/prodcdb/,/stbycdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "DbFileNameConvert" updated

DGMGRL>  edit database stbycdb set property 'LogFileNameConvert'='/prodcdb/,/stbycdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "LogFileNameConvert" updated

If possible carry out a switchover to test the DG configuration.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

DGMGRL> switchover to stbycdb
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

This conclude the removing of a standby database from a DG configuration.

The physical standby that was removed from the DG configuration could be converted to an active database open for read/write. If the DG configuration was in maximum availability or maximum protection mode then lower the protection mode of the database to maximum performance. If not opening the database will fail with LGWR complaining the following.

LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 LGWR standby database required
2018-08-06T15:01:23.198018+01:00
Errors in file /opt/app/oracle/diag/rdbms/stby2cdb/stby2cdb/trace/stby2cdb_lgwr_28545.trc:
ORA-16072: a minimum of one standby database destination is required

Run the following to lower the protection mode

SQL>  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  UNPROTECTED

Activate the physical standby for read/write.

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Reset db/log file name convert parameters. This requires restart of the database to take affect.

alter system reset db_file_name_convert scope=spfile;
alter system reset log_file_name_convert scope=spfile;

Set DG broker auto start to false

alter system set dg_broker_start=false scope=both;

If archive log deletion policy was set to DG specific policy such as "CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY" then reset it to none

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

The standby was managed by clusterware as part of Oracle restart configuration. The database configuration information would still be listed as physical standby and mount or read only open mode (whichever was set for standby).

srvctl config database -d stby2cdb
Database unique name: stby2cdb
...
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY

Modify the database to reflect its new role and start up mode

srvctl modify database -d stby2cdb -role primary -startoption open

srvctl config database -d stby2cdb
Database unique name: stby2cdb
...
Start options: open
Stop options: immediate
Database role: PRIMARY

Modify any database services that were created to start when the database was in physical standby role.

srvctl config service -d stby2cdb -s abc
Service name: abc
Cardinality: SINGLETON
Service role: PHYSICAL_STANDBY

Modify to the service configuration so it starts when the database is in primary role (if this is applicable to business needs).

srvctl modify  service -d stby2cdb -s abc -l primary

srvctl config service -d stby2cdb -s abc
Service name: abc
Cardinality: SINGLETON
Service role: PRIMARY

Shutdown the database and start it using srvctl to validate the configuration.

srvctl start database -d stby2cdb

Wednesday, March 14, 2018

Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB

This post list the steps for adding a data guard broker to an existing 12.2 Data Guard setup. There's an earlier post on setting up data guard broker on 12cR1. The primary database is called prodcdb and standby database is called stbycdb (refer here for other names in DG setup).
12.2 also introduced several new features. Below are few highlights from the DG broker guide.
To increase high availability, you can now set multiple fast-start failover targets and the broker automatically selects which one to use.
You can now start observers on multiple hosts to manage a single Data Guard broker configuration.
The new Data Guard broker property ApplyInstances can be used to engage multiinstance Redo Apply as well as to restrict the number of instances that are involved in redo apply on an Oracle Real Application Clusters (Oracle RAC) database.
The broker now supports migrating a pluggable database (PDB) from one multitenant container database (CDB) to another on the same host. You can migrate a PDB from a primary CDB to another primary CDB or failover a PDB from a standby CDB to a primary CDB.
The DGMGRL commands SHOW DATABASE VERBOSE and SHOW INSTANCE VERBOSE now show the locations of the Oracle alert log file and of the broker log file.
The configuration member to which a connection is made using the CONNECT command is now displayed upon successful completion of the command.
1. DG broker uses static listener service names to connect and start databases. Add static service names to listener.ora file in GI_HOME as grid user. Oracle documentation states as of Oracle Database 12.1.0.2 "a static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used. Broker will use the clusterware to restart an instance". In this case the static _DGMGRL service was created to illustrate the steps that could be used in non-RAC and non Oracle restart setups.
On primary DB

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = prodcdb)
                (SID_NAME = prodcdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
        (SID_DESC =
                (GLOBAL_DBNAME = prodcdb_DGMGRL)
                (SID_NAME = prodcdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )

)

On standby DB

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = stbycdb)
                (SID_NAME = stbycdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )
        (SID_DESC =
                (GLOBAL_DBNAME = stbycdb_DGMGRL)
                (SID_NAME = stbycdb)
                (ORACLE_HOME = /opt/app/oracle/product/12.2.0/dbhome_1)
        )

)

Restart the listeners on both primary and standby and view static service registration. On primary

Service "prodcdb_DGMGRL" has 1 instance(s).
  Instance "prodcdb", status UNKNOWN, has 1 handler(s) for this service...

On standby

Service "stbycdb_DGMGRL" has 1 instance(s).
  Instance "stbycdb", status UNKNOWN, has 1 handler(s) for this service...

2. As this is non-RAC database setup, the default locations are chosen for dg broker config files. If this was a RAC configuration then the dg broker config file location must be shared by all instances. On primary the dg broker config files location is shown below.

NAME                           VALUE
------------------------------ ------------------------------------------------------------
dg_broker_config_file1         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr1prodcdb.dat
dg_broker_config_file2         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr2prodcdb.dat

On standby

NAME                           VALUE
------------------------------ ------------------------------------------------------------
dg_broker_config_file1         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr1stbycdb.dat
dg_broker_config_file2         /opt/app/oracle/product/12.2.0/dbhome_1/dbs/dr2stbycdb.dat

3. Set dg broker to auto start on both primary and standby

alter system set dg_broker_start=true scope=both sid='*';

4. Create the DG broker configuration by adding the primary database.

$ dgmgrl /
Welcome to DGMGRL, type "help" for information.
Connected to "prodcdb"
Connected as SYSDG.
DGMGRL> create configuration dg12c2 as primary database is prodcdb connect identifier is prodcdbtns;
Configuration "dg12c2" created with primary database "prodcdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

4. Add the standby database into the DG broker. Before adding the standby database, the LOG_ARCHIVE_DEST_n value used for redo transport must be cleared on on both primary and standby. Adding of the standby DB would fail without clearing the LOG_ARCHIVE_DEST_n value

DGMGRL> add database stbycdb as connect identifier is stbytns;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

Reason for this has been explained in the previous 12cR1 dg broker post and MOS 1582179.1. Once log_archive_dest is cleared, standby DB addition works. In this DG setup, log_archive_dest_2 is used for redo transport. Reset it on both primary and standby

SQL> alter system reset log_archive_dest_2 scope=both;

System altered.

Add the standby database

DGMGRL> add database stbycdb as connect identifier is stbytns;
Database "stbycdb" added

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

5. Enable the configuration

DGMGRL>  enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 168 seconds ago)

6. At this point the DG broker configuration is complete. But it is good to check out the database status, inconsistent properties and validate the databases.

DGMGRL> show database prodcdb

Database - prodcdb

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    prodcdb

Database Status:
SUCCESS

DGMGRL> show database stbycdb

Database - stbycdb

  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: 2.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stbycdb

Database Status:
SUCCESS

If the verbose option is used then as stated in new features section log files are shown

DGMGRL> show database verbose prodcdb
...
  Log file locations:
    Alert log               : /opt/app/oracle/diag/rdbms/prodcdb/prodcdb/trace/alert_prodcdb.log
    Data Guard Broker log   : /opt/app/oracle/diag/rdbms/prodcdb/prodcdb/trace/drcprodcdb.log

Database Status:
SUCCESS

Check inconsistent properties

DGMGRL> show database prodcdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

 DGMGRL>  show database stbycdb inconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

Validate the databases

 DGMGRL> validate database prodcdb

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    prodcdb:  YES

DGMGRL> validate database stbycdb

  Database Role:     Physical standby database
  Primary Database:  prodcdb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    prodcdb:  YES
    stbycdb:  YES

7. As part of the DG setup, if possible carry out a switchover to check if DG broker can bring up the databases without any issue.

DGMGRL> show configuration when primary is stbycdb

Configuration when stbycdb is primary - dg12c2

  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Connect with password to dgmgrl and perform the switchover

$ dgmgrl sys/prodcdbdb

DGMGRL> switchover to stbycdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 18 seconds ago)

On the new primary (stbycdb) the PDBs will be in read write mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDBAPP1                        READ WRITE NO

On the new standby (prodcdb) PDBs will be in mount mode

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         5 PDBAPP1                        MOUNTED

Switchover back to original primary

DGMGRL> switchover to prodcdb
Performing switchover NOW, please wait...
Operation requires a connection to database "prodcdb"
Connecting ...
Connected to "prodcdb"
Connected as SYSDBA.
New primary database "prodcdb" is opening...
Oracle Clusterware is restarting database "stbycdb" ...
Switchover succeeded, new primary is "prodcdb"

This conclude the setting up of DG broker for an existing 12.2 data guard setup with CDB. Below is an additional step, showing how to increase the protection mode to maximum availability using DG Broker.

Changing Protection Mode to Maximum Availability
The current protection mode is maximum performance with redo transport mode set to ASYNC.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxPerformance
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database prodcdb LogXptMode
  LogXptMode = 'ASYNC'

DGMGRL> show database stbycdb LogXptMode
  LogXptMode = 'ASYNC'

To change the protection mode set the redo transport to SYNC and upgrade the protection mode

DGMGRL> edit database prodcdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL>  edit database stbycdb set property LogXptMode='SYNC';
Property "logxptmode" updated

DGMGRL> show database prodcdb LogXptMode
  LogXptMode = 'SYNC'

DGMGRL> show database stbycdb LogXptMode
  LogXptMode = 'SYNC'

  
DGMGRL>  EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

Thursday, October 1, 2015

Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync

This post list the steps for setting up data guard broker configuration for an existing data guard configuration with far sync. The data guard configuration is the same setup that had far sync instances added earlier. The current setup of the data guard configuration is given below.

Before setting up the dg broker, patch 19399918 (included in 12.1.0.2.4) and 19571599 (at the time of writing only available for system patched with 12.1.0.2.4) must be applied on all oracle homes involved, this include oracle homes used for far sync instances as well. Without the patch there'll be issues with the dg configuration and the switchover will fail when the redo route has an alternate option. However the switchover works without the alternate option on the redo route.

DGMGRL> show database ent12c1 LogXptStatus
Error: ORA-16777: unable to find the destination entry of a standby database in V$ARCHIVE_DEST

DGMGRL> switchover to ent12c1s;
Performing switchover NOW, please wait...
Error: ORA-16778: redo transport

Another pre-req is clearing of log_archvie_dest_* parameters in all the instances (including far sync instances). Trying to create a dg configuration or add a (far_sync) instance will fail.

DGMGRL> create configuration ent12c1_dgb as primary database is ent12c1 connect identifier is ent12c1tns;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

DGMGRL> add database ent12c1s as connect identifier is ent12c1stns;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

DGMGRL> ADD FAR_SYNC fs12c1 AS CONNECT IDENTIFIER IS fs12c1tns;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

This is a new requirement introduced in 12c which states "as of Oracle Database 12c Release 1 (12.1), for all databases to be added to a broker configuration, any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared."
1. To add the dg broker configuration clear the existing log_archive_dest_* parameter. In the existing setup destination 2 and 3 were used for log shipping.

alter system set log_Archive_dest_2='' scope=both;
alter system set log_Archive_dest_3='' scope=both;
alter system set log_archive_dest_state_3='enable' scope=both;

2. Create the dg broker configuration by adding primary, standby and far sync instances. In this case ENT12C1 is the primary database while ENT12C1S is the standby and FS12C1 is the far sync instance the primary database ships redo synchronously. After a role switch when the ENT12C1S becomes the new primary then it uses FS12C1S as the far sync instance to transport redo synchronously.

create configuration ent12c1_dgb as primary database is ent12c1 connect identifier is ent12c1tns;
add database ent12c1s as connect identifier is ent12c1stns;
add FAR_SYNC fs12c1 as connect identifier is fs12c1tns;
add FAR_SYNC fs12c1s as connect identifier is fs12c1stns;

show configuration;

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    ent12c1s - Physical standby database
    fs12c1   - Far sync instance
    fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

3. Enable the configuration and far_sync.

enable configuration;
Enabled.
enable far_sync fs12c1;
Enabled.
enable far_sync fs12c1s;
Enabled.

4. Set the redo transport modes sync for far_sync (default is async) and appropriate redo routes for each instance. In this first instance the redo routes are set such that primary ship to far sync and far sync ships to standby. There's no redo route between primary and standby. This is different to earlier setup and will be changed in subsequent steps. It's also worth noting that "if a database receives redo from a database or far sync instance where the RedoRoutes property has been configured with a redo transport mode, then the mode specified by that RedoRoutes property value overrides the value of the LogXptMode property".

EDIT far_sync fs12c1 SET PROPERTY LogXptMode='SYNC';
EDIT far_sync fs12c1s SET PROPERTY LogXptMode='SYNC';

EDIT DATABASE ent12c1 SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1 SYNC)';
EDIT FAR_SYNC fs12c1 SET PROPERTY 'RedoRoutes' = '(ent12c1 : ent12c1s ASYNC)';
EDIT DATABASE ent12c1s SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1S SYNC)';
EDIT FAR_SYNC fs12c1s SET PROPERTY 'RedoRoutes' = '(ent12c1s : ent12c1 ASYNC)';

5. Verify the configuration and validate the database and far sync instances

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show configuration when primary is ent12c1s

Configuration when ent12c1s is primary - ent12c1_dgb

  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

DGMGRL> validate far_sync fs12c1
    Member Role:        Far Sync Instance
    When Primary Is:    ent12c1

    Active Redo Source: ent12c1
    Redo Destinations:
                        ent12c1s

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              ent12c1                 fs12c1
    1         3                       4                       Sufficient SRLs

DGMGRL> validate far_sync fs12c1s when primary is ent12c1s
    Member Role:        Far Sync Instance
    When Primary Is:    ent12c1s
    Redo Destinations:
                        ent12c1

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              ent12c1s                fs12c1s
    1         3                       4                       Sufficient SRLs


DGMGRL> validate database ent12c1s

  Database Role:     Physical standby database
  Primary Database:  ent12c1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    ent12c1:   Off
    ent12c1s:  Off

  Transport-Related Property Settings:
    Property                        ent12c1 Value            ent12c1s Value
    RedoRoutes                      (LOCAL : FS12C1 SYNC)    (LOCAL : FS12C1S SYNC)

DGMGRL> validate database ent12c1

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    ent12c1:  Off

6. During the validation of the databases the alert log will show the following warning.

SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE

However the switchover worked fine without any issues.

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"


DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 3 seconds ago)

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"

7. Once the switchovers are completed successfully it's time to modify the redo routes so there is an alternate path between primary and standby in case of far_sync failure. The redo transport mode between primary and standby would be async. In order to provide an alternative redo route the maxfailure property must be set to a non-zero value. Without this change alternate route cannot be specified

DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
Error: ORA-16863: A member whose MaxFailure property is set to zero cannot have an alternate destination.

EDIT DATABASE ent12c1 SET PROPERTY 'MaxFailure' = 1;
EDIT FAR_SYNC fs12c1 SET PROPERTY 'MaxFailure' = 1;
EDIT DATABASE ent12c1s SET PROPERTY 'MaxFailure' = 1;
EDIT FAR_SYNC fs12c1s SET PROPERTY 'MaxFailure' = 1;

The maxfailure property must be set for far sync as well. Even though they don't have a alternate destination set on them, failure to do so will result in the above error and it's not possible to change the maxfailure to 0 once the alternate destination is set.

DGMGRL>  show far_sync fs12c1 RedoRoutes
  RedoRoutes = '(ent12c1 : ent12c1s ASYNC)'

DGMGRL> EDIT FAR_SYNC fs12c1 SET PROPERTY 'MaxFailure' =0;
Error: ORA-16864: The MaxFailure property cannot be set to zero for a member that has an alternate destination.

8. Set the redo routes for the databases so there's an alternate route for redo transport if far sync is not available. As mentioned earlier the patch 19399918 must be applied on all instances (inclusive of far_sync) for this to work.

EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
EDIT DATABASE ent12c1s SET PROPERTY RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC FALLBACK))';

9. Verify the configuration shows the alternate destination.

DGMGRL> show configuration verbose

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database
    ent12c1s - Physical standby database (alternate of fs12c1)

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

DGMGRL> show configuration when primary is ent12c1s

Configuration when ent12c1s is primary - ent12c1_dgb

  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database
    ent12c1  - Physical standby database (alternate of fs12c1s)

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

DGMGRL> validate database ent12c1s

  Database Role:     Physical standby database
  Primary Database:  ent12c1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    ent12c1:   Off
    ent12c1s:  Off

  Transport-Related Property Settings:
    Property                        ent12c1 Value            ent12c1s Value
    RedoRoutes                      (LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC

Check if the alternate location is visible on the v$archive_dest view of the primary database

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                    TRANSMIT_MOD
-------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2   VALID     fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3   ALTERNATE ent12c1stns                    ASYNCHRONOUS

In case of far_sync failure the alternate destination will become valid.

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             ALTERNATE fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3             VALID     ent12c1stns                    ASYNCHRONOUS

10. Check of inconsistent properties and rectify if any exists. Finally carry out switchovers.

DGMGRL> show database ent12c1 InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> show database ent12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> show far_sync fs12c1 InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL>  show far_sync fs12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"

This completes setting up of dg broker for existing data guard configuration with far sync instances. The below section list some of the issues observed during various testing of the setup.

Observed Issues
1. It seems how the far sync is terminated has an effect on the fail over of the archive destination. If the far sync was terminated abruptly (shutdown abort) then the primary database's archive log destination fails over to alternative path give (v$archive_dest output given earlier). But when the far_sync starts up again the log archive destination status for the path from far_sync to standby comes up as idle. Following output is from far_sync

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                              TRANSMIT_MOD
-------------------- --------- ---------------------------------------- ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS
LOG_ARCHIVE_DEST_2   IDLE      ent12c1stns                              ASYNCHRONOUS
STANDBY_ARCHIVE_DEST VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS

Even after leaving it for 24 hour period the destination did not resolve itself. Manually intervention was needed

ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;

2. If the far_sync has a clean shutdown (shutdown immediate instead of shutdown abort) then when it comes up again the log archive dest status come up as valid. However the failover on the primary doesn't happen to alternate destination. The destination to far_sync remains deferred and alternate stays as it is. Following output from primary when far_sync is shutdown cleanly

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             DEFERRED  fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3             ALTERNATE ent12c1stns                    ASYNCHRONOUS

When the far_sync comes up again the deferred destination becomes valid again. In this scenario can lead to data loss as redo transport doesn't happen between primary and standby even though an alternate path was specified.

3. Increasing the protection mode was not possible with the redo routes defined with ALT and ASYNC.

DGMGRL> show database ent12c1 RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))'
DGMGRL> show database ent12c1s RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC FALLBACK))'
DGMGRL> show far_sync fs12c1 RedoRoutes
  RedoRoutes = '(ent12c1 : ent12c1s ASYNC)'
DGMGRL> show far_sync fs12c1s RedoRoutes
  RedoRoutes = '(ent12c1s : ent12c1 ASYNC)'

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

There was no such problem before when the setup was not using the dg broker. Whatever the problem seem to be localized to dg broker setup. Removing the alternate option on the redo route from primary and standby databases allowed the upgrade of the protection mode, even though between far sync and standby redo was shipping async.

DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1 SYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE ent12c1s SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1S SYNC)';
Property "RedoRoutes" updated

On primary

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                    TRANSMIT_MOD
-------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2   VALID     fs12c1tns                      PARALLELSYNC

On far sync

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                              TRANSMIT_MOD
-------------------- --------- ---------------------------------------- ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS
LOG_ARCHIVE_DEST_2   VALID     ent12c1stns                              ASYNCHRONOUS
STANDBY_ARCHIVE_DEST VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS

Increase of protection mode works and switchvoer is possible without any issue

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration;

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 59 seconds ago)

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"


DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"

Even though ALT+ASYCN combination didn't work, ALT+SYNC combination allowed increase of protection mode and switchover without any issue.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS maxperformance;
Succeeded.

DGMGRL> show configuration;

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s SYNC FALLBACK))';
Property "redoroutes" updated
DGMGRL> EDIT DATABASE ent12c1s SET PROPERTY RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 SYNC FALLBACK))' ; 
Property "redoroutes" updated

DGMGRL> show database ent12c1 RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s SYNC FALLBACK))'
DGMGRL>  show database ent12c1s RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 SYNC FALLBACK))'
DGMGRL> show far_sync fs12c1 RedoRoutes
  RedoRoutes = '(ent12c1 : ent12c1s ASYNC)'
DGMGRL>  show far_sync fs12c1s RedoRoutes
  RedoRoutes = '(ent12c1s : ent12c1 ASYNC)'


DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 50 seconds ago)

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"

This creates a alternate route between primary and standby when far sync fails. In this case redo shipment happens in SYNC mode. When far sync is up

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                                STATUS    DESTINATION                    TRANSMIT_MOD
---------------------------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1                       VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2                       VALID     fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3                       ALTERNATE ent12c1stns                    PARALLELSYNC

After far sync fails

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                                STATUS    DESTINATION                    TRANSMIT_MOD
---------------------------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1                       VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2                       ALTERNATE fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3                       VALID     ent12c1stns                    PARALLELSYNC

Even though this works it's not same as the setup exited before dg broker was used and one of the key points of far sync to avoid sync redo shipping between primary and standby over long distance. It is also not possible to change the redo mode to ASYNC.

DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
Error: ORA-16900: change of LogXptMode or RedoRoutes property violates overall protection mode

A SR was raised about this issue. After some investigation Oracle came with a patch "Patch 20695119 MERGE REQUEST ON TOP OF DATABASE PSU 12.1.0.2.2 FOR BUGS 19399918 19571599" (only works of 12.1.0.2.2). After applying this patch the dg broke allowed increasing the protection mode but when a switchover was issued failed with "ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x2B29FF483BA0], [rfrxpts2dfp.1]". There was no resolution for this from Oracle and SR went nowhere.
Searching MOS it was discovered that recently Oracle has released patch 19571599 (requires 12.1.0.2.4 applied DB Home) which was not available outside of the merge patch earlier. After applying this patch it was possible to create the dg broker configuration same as before.

EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
EDIT DATABASE ent12c1s SET PROPERTY RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC FALLBACK))';

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 35 seconds ago)

With this configuration and patches in place when the far sync goes down the dg broker lowers the protection mode to maximum performance to reflect the new redo shipping mode of ASYNC. Following entries could be seen on standby's alert log

Primary database is in MAXIMUM PERFORMANCE mode
Changing standby controlfile to MAXIMUM PERFORMANCE mode

When the far sync comes up again the protection mode is upgraded to maximum availability. On far sync alert log

Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level

On standby's alert log

Primary database is in MAXIMUM PERFORMANCE mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level

Even though standby's protection level goes up and down primary's protection level remains the same at maximum availability and this can cause warning to appear on the dg broker configuation

DGMGRL> show configuration
Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    ent12c1s - Physical standby database (alternate of fs12c1)

  Members Not Receiving Redo:
  fs12c1   - Far sync instance
    Warning: ORA-01034: ORACLE not available

  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 31 seconds ago)

DGMGRL> show database ent12c1 statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *    WARNING ORA-16629: database reports a different protection level from the protection mode

Another issue observed is that at times the alternate route disappear from the log archive dest entries even though the state of the entries remains alternate. On primary (there's no alternate option)

SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             VALID     fs12c1stns                     PARALLELSYNC

This will lead to log transport properties being inconsistent and switchovers failing

DGMGRL> show database ent12c1 InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE
         ent12c1               fs12c1           RedoRoutes                 NONE             ent12c1s

When standby becomes primary

DGMGRL> show database ent12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE
        ent12c1s              fs12c1s           RedoRoutes                 NONE              ent12c1

Show database would have entries similar to following

Instance(s):
    ent12c1s
      Error: ORA-16736: unable to find the destination entry of standby database "ent12c1" in V$ARCHIVE_DEST
      Warning: ORA-16715: redo transport-related property RedoRoutes of standby database "fs12c1s" is inconsistent
      Warning: ORA-16728: consistency check for property LogXptMode found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a standby database in V$ARCHIVE_DEST

One of the easiest way to resolve this is to disable and enable redo transport on the primary

DGMGRL>  EDIT DATABASE ent12c1s SET state='transport-off';
DGMGRL> EDIT DATABASE ent12c1s SET state='transport-on';

But this may not be possible when the protection mode is maximum availability. In such cases simply enabling transport, while it's already on works

DGMGRL> edit database ent12c1s set state='transport-off';
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

DGMGRL> edit database ent12c1s set state='transport-on';
Succeeded.

Check if the alternate route appears on the archive destination

SQL>  SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             VALID     fs12c1stns                     PARALLELSYNC
LOG_ARCHIVE_DEST_3             ALTERNATE ent12c1tns                     ASYNCHRONOUS

Continue with the switchover

DGMGRL> switchover to ent12c1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"

Related Posts
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
11gR2 Standalone Data Guard (with ASM and Role Separation)

Useful metalink notes
Cascaded Standby Databases in Oracle 12c [ID 1542969.1]
Create Configuration Failing with ORA-16698 [ID 1582179.1]
Data Guard 12c New Feature: Far Sync Standby [ID 1565071.1]
12c Create Dataguard Broker Configuration - DGMGRL [ID 1583588.1]
Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration [ID 1302539.1]
Configuring Active Data Guard Far Sync Using a Terminal Standby Database as Alternate Log Archive Destination in Data Guard Broker [ID 1918316.1]

A! Help

Labels

Saturday, March 26, 2022

Initiating Switchover on a Physical Standby

Tuesday, September 28, 2021

Failover and Reinstate With Multiple Physical Standbys

Saturday, August 14, 2021

ORA-16713: The Oracle Data Guard broker command timed out When Changing LogXptMode

Monday, March 22, 2021

Removing a Standby Database From a Data Guard Configuration With RedoRoutes

Saturday, August 8, 2020

Removing a Failed Standby Database From a Data Guard Configuration

Monday, October 1, 2018

Converting a Physical Standby to a Snapshot Standby

Saturday, September 8, 2018

DG Broker Continue With Status "ROLLING DATABASE MAINTENANCE IN PROGRESS" Even After ROLLBACK_PLAN is Called

Tuesday, August 21, 2018

Removing a Standby Database From a Data Guard Configuration

Wednesday, March 14, 2018

Creating Data Guard Broker for an Existing 12.2 Data Guard Setup with CDB

Thursday, October 1, 2015

Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync

About Me

Downloads

Quick Response

Popular

Blog Archive

Total Pageviews

Followers

Oracle Documentation