Steps To Failover in 12c using SQL*Plus

Steps To Failover in 12c using SQL*Plus
Solution

For Planned Failover :

NOTE :

Make sure both Primary and Standby is working fine .

Verify that the standby database has the most recently archived redo log file for each primary database redo thread.
Ensure that Redo Apply is active at the target standby database.

Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.

For example:


SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;


: Stop Redo Apply.

Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


Switch the physical standby database to the primary role.

Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE FAILOVER TO target_db_name;

++If this statement completes without any errors.

: Open the new primary database.
SQL> ALTER DATABASE OPEN;

: Back up the new primary database.

Oracle recommends that you perform a full backup of the new primary database.


Reference :  http://docs.oracle.com/database/121/SBYDB/role_management.htm#SBYDB5171
Dataguard Failover 12c using SQL*Plus (Doc ID 2144024.1)

DR Switch over steps when DGMRL not working

Primary -

select status,instance_name,database_role from v$database,v$instance;

DR -

select status,instance_name,database_role from v$database,v$instance;

Primary -
alter database switchover to DR_Unique_name verify;


no error message should report which signifies that we are good to go with the switchover.
The alert log on the primary database is the best to watch




Fri Jul 13 15:51:23 2018
alter database switchover to ADR verify
ORA-16474 signalled during: alter database switchover to DR verify...
alter database switchover to ADR verify
Fri Jul 13 15:53:29 2018
SWITCHOVER VERIFY: Send VERIFY request to switchover target ADR
SWITCHOVER VERIFY COMPLETE
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during: alter database switchover to ADR verify...

Primary > alter database switchover to DR-uniquename;

Watch both primery and DR alert logs for any errors.

DR - below should show as Primary

select status,instance_name,database_role from v$database,v$instance;

Primary - below should show as Primary

select status,instance_name,database_role from v$database,v$instance;

to verify the Synch -

Primary(old DR)-
select max(sequence#) from v$archived_log;

DR (Old Primary)-

select max(sequence#) from v$archived_log where applied='YES';

Fatal NI connect error 12514, connecting to: TNS-12564: TNS:connection refused

Issue -

Fatal NI connect error 12514, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=abc.xyz.com)(PORT=9501))(CONNECT_DATA=(SERVICE_NAME=stby)(CID=(PROGRAM=oracle)(HOST=abc234.xyz.com)(USER=oracle))))

  VERSION INFORMATION:
        TNS for Linux: Version 12.1.0.2.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
  Time: 18-JUL-2018 11:59:41
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564

TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0


Assumption -


Issue started after failover test.
During the failover DGMGRL didn't work so had to execute failover command in SQL
Failover is successed - the standby DB became primary (old primary crash - intentionnally)
The errors are reporting on new primary database alert log.

Archive log shipping stopped
Verified the Listener and Tnsnames files - no issues found.

Rebuilt the standby server, during the process on standby DB realized the ARCHIVE_LOG_DEST_2 is in deffer/Null state.

once the LOG_ARCHIVE_DEST_2 set with PRIMARY_ROLE, the Fatal NI connect error 12514 disappear.
Example -
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';


Reference -

LOG_ARCHIVE_DEST_2 is currently ignored because this destination is valid only for the primary role. If a switchover occurs and this instance becomes the primary database.


https://docs.oracle.com/database/121/SBYDB/create_ps.htm#SBYDB4725
Archive Log Shipping From Primary to Standby Fails With ORA-12514 (Doc ID 563801.1)

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

Issue -

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/17/2018 22:04:37
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /Ora/APEX/APEX_1_13_981655091.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Solution -

The archive backup looking for old archive file which is not available on the server.

commands -
RMAN> list backup;
RMAN>crosscheck backup;
RMAN>crosscheck archivelog all;
RMAN>delete expired backup;

These commands delete the expired  backups and archives.

RMAN restore and recovery script in Oracle !12c

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
set until scn 5761438110;
restore database;
recover database;
alter database open resetlogs;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}

RMAN-06556: datafile 1 must be restored from backup older than SCN

Issue - When recovering database until SCN, recovery failed with error  RMAN-03002 and RMAN-06556

using channel ORA_DISK_8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/17/2018 20:53:35
RMAN-06556: datafile 1 must be restored from backup older than SCN 5761437810

Solution -
Below scripts useful to identify the SCN for the data files can be recovered - 
1.
select  min(FHSCN) "LOW FILEHDR SCN", max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN"from X$KCVFH ;

LOW FILEHDR SCN  MAX FILEHDR SCN  Min PITR ABSSCN
---------------- ---------------- ----------------
5761438110       5761438110       0

2.

SQL> select file#, status, checkpoint_change# from v$datafile where STATUS like '%SYS%';

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 SYSTEM          5761438110



RMAN>  recover database until scn 5761438110;

Starting recover at 17-JUL-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 17-JUL-18