Site StandbyDb returned ORA-16664

The ORA-16664 occur4es in Data Guard log files as following:

...
..
.
Site StandbyDb returned ORA-16664.
...
..
.

Solution

a) Check both alert and data guard logs of primary database

b) Check both alert and data guard logs of standby database with following errors:

ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925

...
..

.
OS Audit file could not be created; failing after 6 retries

...

After cleaned the old audit files, then everything is fine.

DGMGRL> show database verbose "STANDBYDB";

...
..
.
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Average Apply Rate:      4.38 MByte/s
  Active Apply Rate:       1.11 MByte/s
  Maximum Apply Rate:      46.39 MByte/s
..
..
.
Database Status:
SUCCESS

DGMGRL>

ORA-00308: cannot open archived log

Standby database recovery stopped with the following errors:

Wed Aug 17 10:43:35 2022
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 8 slaves
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Log /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kkwp0_.arc
Error opening /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kkwp0_.arc
Attempting refetch
MRP0: Background Media Recovery terminated with error 308
Errors in file /u01/app/oracle/diag/rdbms/prdb/PRDB/trace/PRDB_pr00_17264.trc:
ORA-00308: cannot open archived log '/fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kkwp0_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
MRP0: Background Media Recovery process shutdown (PRDB)

Check the missing archive log does exist:

$ ls -ltr /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_*_.arc
-rw-r-----. 1 oracle oinstall 910116352 Aug 11 09:58 /fra/PRDB/archivelog/2022_08_11/o1_mf_1_68911_kh8kjvh9_.arc

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

ORA-10485: Real-Time Query cannot be enabled while applying migration redo

After upgraded databases using DataGuard rolling method, then standby database reports ORA-10485 error:

ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Recovery interrupted!
stopping change tracking
2022-04-29T21:52:31.513013+10:00
Errors in file /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/STBOEMREP_mrp0_23330.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
2022-04-29T21:52:31.513045+10:00
Background Media Recovery process shutdown (STBOEMREP)

Oracle support explains this ORA error:

$ oerr ora 10485
10485, 00000, "Real-Time Query cannot be enabled while applying migration redo."
// *Cause:  The Real-Time Query feature was enabled when an attempt was made
//          to recover through migration redo generated during primary upgrades or
//          downgrades.
// *Action: Close the standby database in order to recover through
//          migration redo. Reenable the Real-Time Query feature afterwards.

CAUSE

Real-time query has been ON when upgrading the primary database.

SOLUTION

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 3221223152 bytes
Fixed Size                  9139952 bytes
Variable Size             687865856 bytes
Database Buffers         2516582400 bytes
Redo Buffers                7634944 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


SQL> alter database recover managed standby database cancel;

Database altered.


SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> alter database open;

Database altered.

DGMGRL>  show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

DGMGRL> show database verbose "stboemrep";

Database - stboemrep

  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: 12.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    ON
  Instance(s):
    STBOEMREP

  Properties:
    DGConnectIdentifier             = 'stboemrep'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'stbnode1.virtuallab'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=stbnode1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBOEMREP_DGMGRL)(INSTANCE_NAME=STBOEMREP)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/alert_STBOEMREP.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/stboemrep/STBOEMREP/trace/drcSTBOEMREP.log

Database Status:
SUCCESS

DGMGRL>

How To Manually Failover to Standby Database When Primary Database Got Issues

In normal situation, DBA can use Data Guard utility to switchover or failover database roles. DBA can also use SQL command to switchover or failver database roles.

This post demonstrate how to failover to standby database in 12c when Primary Databases is lost due to various reasons.

Check Database Role

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY

Stop Standby Database Recovery

SQL> alter database recover managed standby database cancel;
Database altered.

Complete Recovery and Transit Current Standby as Primary

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.

After this command, this standby database will NOT be able to go back to standby database any more.

Activate the Standby Database

SQL>alter database activate standby database;
Database altered.

Open the Database

SQL>alter database open;


SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
---------- -------------------- ----------------
MOUNTED    MAXIMUM PERFORMANCE   PRIMARY

The Last and Not the Least

  • Backup new primary database ASAP.
  • Reinstate the old primary as new standby database, if flashback on. Otherwise rebuild the standby database as per normal processes.

Role Transitions with Oracle Data Guard by using SQL

As a DBA, there are many situations required to switchover database role in Data Guard environment. For post 12c, the easy way is to execute one line command to switchover database:

SQL> alter database switchover to SBYTEST;

OR

DGMGRL> switchover to SBYTEST; 

This post demonstrates how to implement role transitions step by step by using SQL.

On Primary Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY
SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO STANDBY
The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request
SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

Verify switchover is OK or not.

SQL>  alter database switchover to SBYTEST verify;
 alter database switchover to SBYTETS verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

Check alert log, and fix the problem until getting the following result.

SQL> alter database switchover to SBYTEST verify;


Database altered.

Primary role transition to standby, and check alert log for details.

SQL> alter database commit to switchover to standby with session shutdown;

Database altered.

After the previous step, the new standby database is down. so we have to manually startup and start recovery.

SQL> startup nomount;

SQL> alter database mount standby database;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered. 

Now we move onto standby database.

On Standby Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

This is the initial switchover status before primary switchover to standby yet.

After primary switchover to standby already, then continue the following steps.

SQL>  select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL>  alter database commit to switchover to PRIMARY with session shutdown;

Database altered.


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
SBYTEST  MOUNTED              PRIMARY


SQL> alter database open;

Database altered.

Reconfigure Data Guard Broker because databases role changed.

DGMGRL > show configuration;

DGMGRL > disable configuration;

DGMGRL > enable configuration;

Finally if it is RAC environment, modify database configurations like “Start options” and “Database role”, etc.

$srvctl config database -d TEST

$ srvctl modify  database -d TEST -role PHYSICAL_STANDBY -startoption "READ ONLY"

$ srvctl stop database -d TEST

$srvctl start database -d TEST
$srvctl config database -d SBYTEST

$srvctl modify  database -d SBYTEST -role PRIMARY -startoption OPEN 

$ srvctl stop database -d SBYTEST

$srvctl start database -d SBYTEST
DGMGRL> show configuration;

Configuration - TESTDB

  Protection Mode: MaxPerformance
  Members:
  SBYTEST - Primary database
     TEST  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)