Check, Fixing and Monitor Dataguard gaps in Oracle Database

Step-1: Check the SCN of Standby ( Data guard ) database and Find the lowest SCN from the following Queries.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

6191944894628

Step-2: Stop the Dataguard MRP ( Apply ) Process and shutdown Standby database.

SQL> alter database recover managed standby database cancel;

SQL> shutdown immediate;

Step-3: Take an Incremental Backup from Production database using the lowest SCN as follows.

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;
        backup as compressed backupset incremental from scn 6191944894628 DATABASE FORMAT '/backup/Standbybkp_%U' tag 'STANDBYbkp';
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
    release channel c5;
    release channel c6;
}

Step-4: Transfer the Incremental backup to the Standby database from Production server.

Step-5: Create a new Standby Controlfile on Production database and transfer it to Standby database.

SQL> alter database create standby controlfile as '/oracle/stand01.ctl';

Step-6: Startup Standby database in nomount mode.

SQL> startup nomount

Step-7: Restore new standby controlfile on Standby database.

RMAN> restore standby controlfile from '/oracle/backup/standb01.ctl';

Step-8: mount the standby database.

SQL> alter database mount standby database;

Step-9: Catalog the new Incremental backups on Standby Server as follows.

RMAN> catalog start with '/oracle/backup/';
List of Cataloged Files
File Name: /oracle/backup/increment.rman

Step-10: start Recover database on Standby database.

RMAN> recover database;
error faced here
RMAN> recover database noredo;
Starting recover at 24-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=227 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/24/2018 11:25:30
RMAN-06094: datafile 417 must be restored
RMAN> switch database to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 01/24/2018 11:40:54
RMAN-06571: datafile 1 does not have recoverable copy

solution :
backup it and restore
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA MOUNTED
SQL>
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
ORA PHYSICAL STANDBY MOUNTE

You can query losing file using the next sql
SQL> col name for a45
SQL> set linesize 300
SQL> select file#,name,bytes,status,to_date(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') "Create",to_date(ONLINE_TIME,'yyyy-mm-dd hh24:mi:ss') "Online" from v$datafile where file#=19;
    1- Shut immediate
    2- Startup nomount
    3- Alter database mount # note you must mount database using old control file because the new have a catalog and datafile you will backup it doesn’t exist in the catalog.
    4- Restore datafile
    5- After that shut immediate again
    6- Alter database using new control file
    7- Repeat from step 5

Step-11:

RMAN> 

run
{
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate channel c3 type disk;
    allocate channel c4 type disk;
    RECOVER DATABASE NOREDO;
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
}

Monitor backup and recovery
select
    sid,start_time,totalwork
    sofar,(sofar/totalwork) * 100 pct_done
    from v$session_longops
    where
        totalwork > sofar
        AND opname NOT LIKE '%aggregate%' 

        AND opname like 'RMAN%';

Drop and recreate standby redo logs

ACTION PLAN
I. ON STANDBY
    1) Cancel managed recovery
        SQL > alter database recover managed standby database cancel;
    2) Make standby_file_management=manual
        SQL > alter system set standby_file_management=manual scope=both sid='*';
    3) Drop existing standby redo logs and recreate them.
        a. Determine the standby redo logs using
            select group#, thread#, status, bytes from v$standby_log;
        b. Drop the group which were returned above. Assuming the above query returned group 5,6,7,8.
      SQL > Alter database drop logfile group 5;
      SQL > Alter database drop logfile group 6;
      SQL > Alter database drop logfile group 7;
      SQL > Alter database drop logfile group 8;
        c. Recreate standby redo logs

Make sure below rules are followed by create standby redo logs.
    i. Do not multiplex them.
    ii. The number of Standby redo logs will be 1 more than the number of online redo logs on the primary.
    iii. The size of standby redo log should be same as that of the online redo log on primary.
        alter database add standby logfile thread 1 group 5 '/u01/app/oracle/product/oradata/standby_redo01.log' size 524288000;
        alter database add standby logfile thread 1 group 6 '/u01/app/oracle/product/oradata/standby_redo02.log' size 524288000;
        alter database add standby logfile thread 1 group 7 '/u01/app/oracle/product/oradata/standby_redo03.log' size 524288000;
        alter database add standby logfile thread 1 group 8 '/u01/app/oracle/product/oradata/standby_redo04.log' size 524288000;
        alter database add standby logfile thread 1 group 9 '/u01/app/oracle/product/oradata/standby_redo05.log' size 524288000;

    4) Set the Standby file management back to auto.
        SQL > alter system set standby_file_management=auto scope=both sid='*';
5) Restart managed recovery
        SQL > alter database recover managed standby database disconnect from session;

II. On Primary
Defer and enable redo transport
  SQL > alter system set log_archive_Dest_state_2=defer;
  SQL > alter system set log_archive_Dest_state_2=enable;
*******************************************************
if there is issue in transferring archives check
    1- network and listener

    2- tnsnames (services)
    3- parameter file (dg_config, log_archive_config, log_archive_dest_n,log_archive_dest_stat)
    4- db_name parameter
    5- destination on standby
*********************************************************

Step-12: Now you can start the MRP Process to apply new logs.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;