Check Primary and Physical Standby in sync

Primary Database
1. Enable archive log - SELECT log_mode FROM v$database;(NOARCHIVELOG) ; ALTER DATABASE ARCHIVELOG;
2. ALTER DATABASE FORCE LOGGING;
3. Set log_archive_config of primary and standby ; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';
4. Set archive log for standby
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
    SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
5. Set primary to switch roles to become standby
    SET FAL_SERVER=DB11G_STBY;
    SET STANDBY_FILE_MANAGEMENT=AUTO;
6. Set primary and standby tnsnames in tnsnames.ora
7. Backup Primary Database
8. Create Standby control file and pfile
    ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
    CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
    *.db_unique_name='DB11G_STBY'
    *.fal_server='DB11G'
    *.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

Standby Database
1. Start listener
2. Create spfile
    CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';
3. Restore databse
4. Create online redo logs
    SET STANDBY_FILE_MANAGEMENT=MANUAL;
    ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
    ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
    ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
    SET STANDBY_FILE_MANAGEMENT=AUTO;
5. Create standby redo logs
    ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
    ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
6. Start the apply process on standby server
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Done...

Protection Mode
Maximum Availability - On primary transactions do not commit until redo data been written to the online redo log of standby - on failure, switchs to Max perf
    LOG_ARCHIVE_DEST_2=AFFIRM ; SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
Maximum Performance - Transactions on the primary commit as soon as redo information has been written to the online redo log
    LOG_ARCHIVE_DEST_2=NOAFFIRM ; SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Maximum Protection - On primary transactions do not commit until redo data been written to the online redo log of standby - On failure, primary shutdown
    LOG_ARCHIVE_DEST_2=AFFIRM ; SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

SELECT protection_mode FROM v$database;

 

Database switchover
Primary
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
shutdown ; startup nomount;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Standby
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
shutdown; startup

Failover
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Script to check in Primary Database:

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

Output:

    Thread Last Sequence Generated

---------- -----------------------

         1                     315

         1                     315

         2                     194

         2                     194

Script to check in standby database:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
  IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
         (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
  IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Output:

    Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------
         1                    315                   315          0
         2                    193                   193          0

Switch archive log in RAC database:

alter system archive log current;

Archived log not synchronized or transferred to standby

select dest_id,status,error from v$archive_dest;

Check MRP(Managed Recovery Process)

select recovery_mode from v$archive_dest_status; 

Start MRP

alter database recover managed standby database disconnect;

Register missing archive log

alter database register or replace logfile '/u01/archive/2_67197_740404074.dbf';

http://oraclemamukutti.blogspot.in/2011/09/recover-gaps-in-standby-database-from.html

Archive log apply speed MRP speed

set linesize 400
col Values for a65
col Recover_start for a21

select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values" from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);