Oracle DATA GUARD

Data Guard Sync Status

Method :1
=========
PRIMARY
========

sqlplus "/ as sysdba"
set pages 50000 lines 32767col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

set pages 50000 lines 32767Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)

=======================================================================

Method :2
=========

STANDBY
========
sqlplus "/ as sysdba"

set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT

col time format a40
select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

=======================================================================

Method :3
=========
PRIMARY
========
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;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------

Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;

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;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
                                                                   0 OR min
=======================================================================

Method :4
=========
PRIMARY
========
Switch log minimun 3 times
--------------------------
alter system switch logfile;       or
alter system switch all logfile;   or
alter system archive log current;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;

STANDBY
========
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;

=======================================================================

Method :5
=========

STANDBY
========
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM GV$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0      N/A            1000 APPLIED_LOG

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;  ------ the status of redo log files
LGWR RFS 1000 CURRENT

Both the above current values must be same.

RMAN Incremental Backup Scenario in DR (Standby Database)

RMAN Incremental Backup Scenario in DR (Standby Database)


Checking Dataguard SYNC status

Standby Database (DR)
----------------
SQL>select distinct(checkpoint_time) from v$datafileheader;

CHECKPOINT_TIME
---------------
24-04-1998 18:10:20 ----------------->Should be current date and time to be in SYNC

DR database is out of Sync since Checkpoint time (as above output) leading to Archive Job failure on Production. We need to apply RMAN incremental backup as Archives are on TAPE and taking time to get restored.

Oracle DBA Interview Questions and Answers - Dataguard

Oracle Data Guard Interview Questions and Answers

What is Dataguard?

Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

What is DG Broker?
DG Broker “it is the management and monitoring tool”.
Oracle dataguard broker is a distributed management framework that automates and centralizes the creation , maintenance and monitoring of DG configuration.
All management operations can be performed either through OEM, which uses the broker (or)  broker specified command-line tool interface “DGMGRL”.


What is the difference between Dataguard and Standby?

Dataguard  : 
Dataguard is mechanism/tool to maintain standby database.
The dataguard is set up between primary and standby instance .
Data Guard is only available on Enterprise Edition.

Standby Database :
Physical standby database provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis.
Standby capability is available on Standard Edition. 

REFERENCE:
http://neeraj-dba.blogspot.in/2011/06/difference-between-dataguard-and.html

What are the differences between Physical/Logical standby databases? How would you decide which one is best suited for your environment?
Physical standby DB:
As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database.
It synchronized with the primary database with Apply Redo to the standby DB.
Logical Standby DB:
As the name logical information is the same as the production database, it may be physical structure can be different.
It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.
We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.
We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.

For OLTP large transaction database it is better to choose logical standby database.

Explain Active Dataguard?

11g  Active Data Guard
Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production database.
Oracle Active Data Guard also enables the use of fast incremental backups when offloading backups to a standby database, and can provide additional benefits of high availability and disaster protection against planned or unplanned outages at the production site.

What is a Snapshot Standby Database?
11g Snapshot Standby Database
Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database.
We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production database and then finally revert it to  it’s earlier state as a physical standby database.
While the snapshot standby database is open in read-write mode, redo is being received from the primary database, but is not applied.
After converting it back to a physical standby database, it is resynchronized with the primary by applying the accumalated redo data which was earlier shipped from the primary database but not applied.
Using a snapshot standby, we are able to do real time application testing using near real time production data. Very often we are required to do production clones for the purpose of testing. But using snapshot standby databases we can meet the same requirement sparing the effort,time,resources and disk space.

REFERENCE:
http://gavinsoorma.com/2009/07/11g-snapshot-standby-database/

Snapshot Standby Database (UPDATEABLE SNAPSHOT FOR TESTING)
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.

REFERENCE:
http://docs.oracle.com/cd/B28359_01/server.111/b28294/title.htm

What is the Default mode will the Standby will be, either SYNC or ASYNC?
ASYNC

Dataguard Architechture?
Data Guard Configurations:
A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically. There are no restrictions on where the databases are located, provided they can communicate with each other.

Dataguard Architecture
The Oracle 9i Data Guard architecture incorporates the following items: 

• Primary Database - A production database that is used to create standby databases. The archive logs from the primary database are transfered and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
• Standby Database - A replica of the primary database.
• Log Transport Services - Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.
• Network Configuration - The primary database is connected to one or more standby databases using      Oracle Net.
• Log Apply Services - Apply the archived redo logs to the standby database. The Managed Recovery      Process (MRP) actually does the work of maintaining and applying the archived redo logs.
• Role Management Services - Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.
• Data Guard Broker - Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.

Primary Database:
A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.

Standby Database:
A standby database is a transactionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database.
The types of standby databases are as follows:

Physical standby database:
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

Logical standby database:
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database. 

What are the services required on the primary and standby database ?
The services required on the primary database are:
• Log Writer Process (LGWR) - Collects redo information and updates the online redo logs. It can also      create local archived redo logs and transmit online redo to standby databases.
• Archiver Process (ARCn) - One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
• Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .
The services required on the standby database are:
• Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
• Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.
• Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).
• Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.

What is RTS (Redo Transport Services) in Dataguard?
It controls the automated transfer of redo data from the production database to one or more archival destinations. The redo transport services perform the following tasks:
a) Transmit redo data from the primary system to the standby systems in the configuration.
b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.
c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the
primary database or another standby database. 

What are the Protection Modes in Dataguard?

Data Guard Protection Modes
This section describes the Data Guard protection modes.
In these descriptions, a synchronized standby database is meant to be one that meets the minimum requirements of the configured data protection mode and that does not have a redo gap. Redo gaps are discussed in Section 6.3.3.

Maximum Availability
This protectionmode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.
This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance
This protectionmode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).
This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.
This is the default protection mode.

Maximum Protection
This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.
Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

How to delay the application of logs to a physical standby? 

A standby database automatically applies redo logs when they arrive from the primary database. But in some cases, we want to create a time lag between the archiving of a redo log at the primary site, and the application of the log at the standby site.

Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database.

Example: For 60min Delay:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby_srvc DELAY=60';
The DELAY attribute is expressed in minutes.
The archived redo logs are still automatically copied from the primary site to the standby site, but the logs are not immediately applied to the standby database. The logs are applied when the specified time interval expires.

Steps to create Physical Standby database?

1.Take a full hot backup of Primary database
2.Create standby control file
3.Transfer full backup, init.ora, standby control file to standby node.
4.Modify init.ora file on standby node.
5.Restore database
6.Recover Standby database
(Alternatively, RMAN DUPLICATE DATABASE FOR STANDBY DO RECOVERY can be also used)
7.Setup FAL_CLIENT and FAL_SERVER parameters on both sides
8.Put Standby database in Managed Recover mode

What are the DATAGUARD PARAMETERS in Oracle?
Set Primary Database Initialization Parameters
----------------------------------------------
On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Primary Database: Standby Role Initialization Parameters
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=  '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO

Prepare an Initialization Parameter File for the Standby Database
-----------------------------------------------------------------
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:
CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Modifying Initialization Parameters for a Physical Standby Database.

DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=  'LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=  'SERVICE=chicago LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston

http://sarith.wordpress.com/tag/oracle-10g-data-guard/


Primary DB Dataguard Parameter?

Parameters responsible for Failover in Dataguard?

Difference between FAL_SERVER (Fetch Archive Log) Vs. FAL_Client

What is RFS?

If DR has different file location, which parameter need to be chamged?

Can we have Standby Database in STANDARD EDITION?

Can we have Primary Database in SOLARIS OS and Standby in LINUX OS?

What are DR creation Prerequisites?
Dataguard creation steps?

Why password file in dataguard?

Active Data Guard Enabled / Disabled

How to Check Active Data Guard is  Enabled  or Not 

SQL> select  'Using Active Data Guard' ADG   from v$managed_standby m,v$database d where m.process like 'MRP%'  ;
        ADG
-----------------------
Using Active Data Guard

or from standby datbase

SQL> select open_mode,controlfile_type from v$database;
OPEN_MODE              CONTROLFILE
--------------------   ------------------
READ ONLY WITH APPLY   STANDBY

Conversion from Physical standby to Active Data Guard standby
-------------------------------------------------------------

Real-time query
===============

A physical standby database can be opened for read-only access and used to offload queries from a primary database. In addition, if a license for the Oracle Active Data Guard option has been purchased, a physical standby database can also be open while Redo Apply is active. This capability is known as the real-time query feature.

Redo Apply must be stopped before any physical standby database instance is opened.

If one or more physical standby instances are open, those instances must be stopped or restarted in a mounted state before starting Redo Apply

SQL> SELECT open_mode FROM V$DATABASE;

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

Stop Apply Services
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> SHUT IMMEDIATE

SQL> START NOMOUNT

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>  SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect;

Database altered.

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

It enables us to have a physical standby read only open, while redo apply is still done in the background.

The Active Data Guard  contains the following features
------------------------------------------------------
Physical Standby with Real-time Query
Fast Incremental Backup on Physical Standby
Automatic Block Repair

Real-Time Apply is Enabled / Disabled

How to check real-time apply is enabled or not on Physical Standby Database

To start the real time apply (standby redo logfiles are must for it)

On Physical Standby
--------------------- 
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_2             VALID     LOCAL          NO  MANAGED

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

On Physical Standby
--------------------- 
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         1 LOG_ARCHIVE_DEST_2             VALID     LOCAL          YES  MANAGED REAL TIME APPLY

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

Dataguard Physical Standby Start / Stop

How to start/stop Dataguard Physical Standby Database

SHUTDOWN
========
status of mrp:
NON-RAC:
select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;
RAC:
select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby;

cancelling mrp:
recover managed standby database cancel;

shut immediate;

STARTUP
=======
startup nomount;

alter database mount standby database;

STARTING MRP0
=============
If a non-real-time apply standby then issue the following SQL statement to start Redo Apply:

alter database recover managed standby database disconnect from session;
--OR--
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION PARALLEL 64;

SQL> SELECT open_mode FROM V$DATABASE;

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

If a real-time apply standby then issue the following SQL statement to start Redo Apply:

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

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Now that the standby is in real-time query mode (that is, the standby is open in read-only mode and Redo Apply is active).

status of mrp:
NON-RAC:
select process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby;
RAC:
select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby;

OPEN_MODE = READ ONLY WITH APPLY
--------------------------------
alter database open readonly;

Dataguard Troubleshooting / Commands

Dataguard Troubleshooting / Commands

select * from v$archive_gap;
select * from v$dataguard_stats;

select flashback_on from v$database;
SELECT * FROM v$block_change_tracking;

show parameter fal;
!tnsping <server/client>
show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;

SELECT DEST_ID,dest_name,status,type,srl,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

RECOVERY_MODE
-------------
MANAGED

On Primary Database
===================
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest where DESTINATION dest_id=2;
/

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
/
select max(sequence#),thread# from gv$log group by thread#;

set numwidth 15
select max(sequence#) current_seq,archived,status from v$log;
/


On Standby Database
===================
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP
/
select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;
/
select max(sequence#),thread# from gv$archived_log where applied='YES' group by thread#;
/
set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status;
/

FIND GAP
--------
select thread#,low_sequence#,high_sequence# from v$archive_log;

LISTNER VERIFICATION FROM PRIMATY DB
------------------------------------
select status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

DEFER Log Shipping
------------------
alter system set log_archive_dest_state_2='DEFER' scope=both;

alter system set dg_broker_start=false;

ENABLE Log Shipping
-------------------
alter system set log_archive_dest_state_2='ENABLE' scope=both;

alter system set dg_broker_start=true;

DELAY CHANGE
------------
SQL> alter system set log_archive_dest_2='ARCH DELAY=15 OPTIONAL REOPEN=60 SERVICE=S1';


ARCHIVE_LAG_TARGET tells Oracle to make sure to switch a log every n seconds
----------------------------------------------------------------------------
ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 1800 SCOPE=BOTH;
This sets the maximum lag to 30 mins.

On Primary to Display info about all log destinations
=====================================================
set pages 300 lines 300
set numwidth 15
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
select ds.dest_id id,ad.status,ds.database_mode db_mode,ad.archivertype,ds.recovery_mode, ds.protection_mode,ds.standby_logfile_count "SRLs",ds.standby_logfile_active active,ds.archived_seq# from v$archive_dest_status ds,v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE'  order by ds.dest_id
/

On Primary to Display log destinations options
==============================================
set pages 300 lines 300
set numwidth 10
column id format 99
select dest_id id ,archiver,transmit_mode,affirm,async_blocks async,net_timeout net_time,delay_mins delay, reopen_secs reopen,register,binding from v$archive_dest order by dest_id
/


==================================================================================================================

Standby Database
================
select NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL, CURRENT_SCN,FLASHBACK_ON,FORCE_LOGGING from v$database;

Some possible statuses for the MRP
----------------------------------
ERROR - This means that the process has failed. See the alert log or v$dataguard_status for further information.

WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed. Switch an archive log on the primary and requery v$managed_standby to see if the status changes to APPLYING_LOG.

WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved. Review the alert log to see if FAL_SERVER has been called to resolve the gap.

APPLYING_LOG - Process is applying the archived redo log to the standby database.

CHECK MANAGED RECOVERY PROCESS : SHOWS STATUS OF ARCH,RFS,MRP PROCESS.
------------------------------
select inst_id,process,status,client_process,thread#,sequence#,block#,blocks,delay_mins from gv$managed_standby;

select * from gv$active_instances;

!ps -ef|grep -i mrp

STARTING MRP0
-------------
ALTER DATABSE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

STOPING MRP0
------------
RECOVER MANAGED STANDBY DATABASE CANCEL;

To Display MRP0 Speed
---------------------
set pages 300 lines 300
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);

checking log transfer and apply
-------------------------------
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# ;
select count(*) from V$ARCHIVED_LOG where applied='NO';
/

TIME TAKEN TO APPLY A LOG
-------------------------
set pages 300 lines 300

select TIMESTAMP,completion_time "ArchTime",SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM",round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)",round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec", round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1, (TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec",round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)" from v$archived_log a, v$dataguard_status dgs where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','') and dgs.FACILITY = 'Log Apply Services' order by TIMESTAMP desc;
/

CHECKING FOR DATAGAURD ERROR
----------------------------
set pages 300 lines 300
column Timestamp Format a20
column Facility  Format a24
column Severity  Format a13
column Message   Format a80 trunc

Select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS') Timestamp,Facility,Severity,error_code,message_num,Message from v$dataguard_status where severity in ('Error','Fatal') order by Timestamp;

select  *  from v$ARCHIVE_GAP;

--OR---
Here is another script with v$dataguard_status:

select *
  from (select TIMESTAMP,
               completion_time "ArchTime",
               SEQUENCE#,
               round((blocks * block_size) / (1024 * 1024), 1) "Size Meg",
               round((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                      OVER(order by TIMESTAMP)) * 24 * 60 * 60,
                     1) "Diff(sec)",
               round((blocks * block_size) / 1024 /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     1) "KB/sec",
               round((blocks * block_size) / (1024 * 1024) /
                     decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                            0,
                            1,
                            (TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
                             OVER(order by TIMESTAMP)) * 24 * 60 * 60),
                     3) "MB/sec",
               round(((lead(TIMESTAMP, 1, TIMESTAMP) over(order by TIMESTAMP)) -
                     completion_time) * 24 * 60 * 60,
                     1) "Lag(sec)"
          from v$archived_log a, v$dataguard_status dgs
         where a.name = replace(dgs.MESSAGE, 'Media Recovery Log ', '')
           and dgs.FACILITY = 'Log Apply Services'
         order by TIMESTAMP desc)
 where rownum < 10;

Finding Missing Logs on Standby
-------------------------------
select local.thread#,local.sequence# from (select thread#,sequence# from v$archived_log where dest_id=1) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#)
/

Check which logs have not been applied
--------------------------------------
alter session set nls_date_format='YYYY-MM-DD HH24:MI.SS';
SELECT SEQUENCE#, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

REGISTRYING LOGFILE
-------------------
alter database register logfile '/file/path/';

RECOVERY PROGRESS ON STANDBY SITE
---------------------------------
v$managed_standby
v$archived_standby

v$archive_dest_status -  TO FIND THE LAST ARCHIVED LOG RECEIVED AND APPLIED ON THIS SITE.
select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;

v$log_history
select max(sequence#),latest_archive_log from v$log_history;

v$archived_log - individual archive log
select thread#,sequence#,applied,registrar from v$archived_log;

standby_file_management - playes when attributes of datafiles are modified primary site.
-IF IT IS RAW DEVICE STANDBY_FILE_MANAGEMENT SHOULD BE MANUAL.OTHERWISE AUTO

http://oraclerac.weebly.com/standby.html

==================================================================================================================

TROUBLESHOOTING A PHYSICAL STANDBY DATABASE:

NOTE: Pls check Metalink 232649.1 (Data Guard Gap Detection and Resolution)

On Standby server:

Run the below query to check the type of Standby database, PHYSCIAL or LOGICAL:

sqlplus "/ as sysdba"
select database_role from v$database;

If Physical Standby then follow:

Step1: Check which logs have not been applied:
======
alter session set nls_date_format='YYYY-MM-DD HH24:MI.SS';
SELECT SEQUENCE#, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step2:Check if there is a gap in the archive logs:
======
SELECT * FROM V$ARCHIVE_GAP;

If there is a gap, then it is most likely that the log has been compressed on the Primary server, and the Standby FAL service cannot retrieve the log.If so, then temporarily stop archivelog compression job on the primary and unzip the required archive logs. After a few minutes, the FAL service will retrieve the log and the Standby apply services will resume.Check the progress by running the SQL in step-1 above.
If the logs haven't been processed after 5-10 minutes, then you will have to perform the following tasks:

Step3: Copy the (zipped) log to the standby archive log destination on the Standby server, (unzip the archive), and register,

ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_443.arc';

Step4: Check if this is a 'real-time apply standby:
=======
select recovery_mode from V$ARCHIVE_DEST_STATUS;

Step5: Stop/restart the standby apply services:
=======
alter database recover managed standby database cancel;

If a real-time apply standby then:
alter database recover managed standby database using current logfile disconnect from session;

Found this:
RECOVER MANAGED STANDBY DATABASE cancel;
ORA-16136: Managed Standby Recovery not active

RECOVER MANAGED STANDBY DATABASE disconnect from session;
Media recovery complete.

Else (non- realtime apply):
alter database recover managed standby database disconnect from session;

Check the progress by running the SQL in step-1 above.

Useful Standby query:
----------------------------
Startup standby database

startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect;

To remove a delay from a standby
alter database recover managed standby database cancel;
alter database recover managed standby database nodelay disconnect;

Cancel managed recovery
alter database recover managed standby database cancel;

Register a missing log file
alter database register physical logfile '<fullpath/filename>';

If FAL doesn't work and it says the log is already registered
alter database register or replace physical logfile '<fullpath/filename>';

If that doesn't work, try this...

shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;

>> wait for the recovery to finish - then cancel

shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;


Check which logs are missing (Run this on the standby)

select local.thread#, local.sequence# from
       (select thread#, sequence# from  v$archived_log where dest_id=1) local where  local.sequence# not in
       (select sequence# from v$archived_log where dest_id=2 and thread# = local.thread#);

Disable/Enable archive log destinations
alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';


Turn on fal tracing on the primary db
alter system set LOG_ARCHIVE_TRACE = 128;

Stop the Data Guard broker
alter system set dg_broker_start=false;

Show the current instance role
select name, open_mode, database_role from v$database;
=====
Logical standby apply stop/start
Stop Logical standby >> alter database stop logical standby apply;

Start Logical standby >> alter database start logical standby apply;

See how up to date a physical standby is: (Run this on the primary)
set numwidth 15
select    max(sequence#) current_seq from    v$log;

Then run this on the standby
set numwidth 15
select max(applied_seq#) last_seq from v$archive_dest_status;

Display info about all log destinations (run on the primary)

set lines 100 set numwidth 15 column ID format 99 column "SRLs" format 99 column active format 99 col type format a4

select ds.dest_id id , ad.status , ds.database_mode db_mode , ad.archiver type , ds.recovery_mode , ds.protection_mode , ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active , ds.archived_seq# from v$archive_dest_status ds , v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE' order by ds.dest_id;

Display log destinations options (run on the primary)

set numwidth 8 lines 100 column id format 99
select dest_id id , archiver , transmit_mode , affirm , async_blocks async , net_timeout net_time , delay_mins delay , reopen_secs reopen , register,binding from v$archive_dest order by dest_id;

List any standby redo logs

set lines 100 pages 999 col member format a70
select st.group# , st.sequence# , ceil(st.bytes / 1048576) mb , lf.member from v$standby_log st , v$logfile lf where st.group# = lf.group#;

Script for Standby archivelog monitoring….(removed the duplicate rows)

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;

=======================================================================

Dataguard Broker Configured or Not

How to check Dataguard Configured or Not

show parameter fal

show parameter dg_broker_start

show parameter log_archive_dest

Error 1031 received logging on to the standby

ISSUE

Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.

PRIMARY
=======
Primary database Alert log error:
--------------------------------
Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.

Chech DR sync and see the difference.

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------

set pages 999 lines 999

col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by timestamp;

show parameter log_archive_dest_state_2;

LISTNER VERIFICATION FROM PRIMATY DB
------------------------------------
select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

FIND GAP
--------
select thread#,low_sequence#,high_sequence# from gv$archive_log;

ps -ef |grep tns
lsnrctl status 

DR
==
DR database Alert log error:
----------------------------
Error 1031 received logging on to the standby

set pages 999 lines 999

col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by timestamp;

select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;

PROCESS STATUS
------- ------------
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG

checking log transfer and apply
-------------------------------
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM gV$ARCHIVED_LOG ORDER BY SEQUENCE#;
select count(*) from GV$ARCHIVED_LOG where applied='NO';

Redo transfer was not happening. When we checked in the
v$managed_process data dictionary view, we could see that RFS was not starting.

Here,
ora-01031 usually appears when some sysdba session failes to authenticate
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.

1.The RFS process was not started in the standby which indicates that standby is not receiving any redo information from the primary.

2.In the primary alert log file we could see the errors indicating that the primary is not able to communicate with the standby instance. The error observed was "Error 1031 received logging on to the standby".

3.The time stamp of the password file on the primary and the standby was different. This indicated the possibility of having the wrong password file in the standby.

SOLUTION:
=========
For the redo transfer to take place efficiently, the password file on standby should be a copy from the primary and renamed standby.We can use v$pwd_file_users data dictionary view to check if the password file is used

1.copy the password file from the primary to the standby and renamed the password file in the following format ie orapw<sid> . 

2.Restar the media recovery process on the standby. 

Once the above steps are completed we could see that redo shipping and redo apply is taking place.

Copy the latest Password file from available PRIMARY Node to rest of PRIMARY and STANDBY nodes:
---------------------------------------------------------------------------------------------- 
Primary (Node 1)
----------------
cd $ORACLE_HOME/dbs
ls -lrt
scp orapw<sid> oracle@PROD_NODE_2_hostname:/oracle/home/dbs

scp orapw<sid> oracle@DR_NODE_1_hostname:/oracle/home/dbs
scp orapw<sid> oracle@DR_NODE_2_hostname:/oracle/home/dbs

or

DR
==
select * from gv$pwfile_users;

we have to check sec_case_sensitive_logon parameter on primary and standby.

SQL> show parameter sec_case_sensitive_logon;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>

We have to recreate the passwd file or copy Primary server to Standby server.

In cause sec_case_sensitive_logon parameter value is true, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10 ignorecase=y

In cause sec_case_sensitive_logon parameter value is false, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10

DR
==
cancelling mrp process:
alter database recover managed standby database cancel;

starting mrp process:
alter database recover managed standby database disconnect from session;

select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby; 

Chech DR sync and see the difference. 

http://troubleurheart.blogspot.in/2011/01/monitor-dataguard-status.html
http://alazydbasblog.blogspot.in/2012/04/steps-to-resolve-error-1031-received.html
http://bitbach.wordpress.com/2010/08/27/pingarc1-heartbeat-failed-to-connect-to-standby-dgp-error-is-1031/
www.dbaglobe.com/2011/01/monitor-dataguard-status.html
http://muthuappsdba.blogspot.in/2012/12/error-16191-pingarc2-heartbeat-failed.html

Source : Internet

SWITCHOVER and SWITCHBACK in Data guard

Switching Over to a Physical Standby Database

SWITCHOVER STEPS:-

PRIMARY
-------

SQL>archive log list
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>alter database commit to switchover to physical standby with session shutdown;
SQL>shutdown immediate
SQL>startup nomount
SQL>alter database mount standby database;
SQL>alter system set log_archive_dest_state_2=defer scope=both;
SQL>select name,open_mode,database_role,switchover_status from v$database;
SQL>show parameter log_archive_dest_state_2;

STANDBY
-------
SQL>archive log list
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate
SQL>startup

OLD PRIMARY ----->New STANDBY
-----------
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>recover managed standby database disconnect;
SQL>select process,status,sequence# from v$managed_standby;

OLD STANDBY ----->New PRIMARY
-----------
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>show parameter log_archive_dest_state_2;
SQL>alter system set log_archive_dest_state_2=enable scope=both;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>select status, gap_status from v$archive_dest_status where dest_id = 2;  --------11gr2 onwards


SWITCHBACK STEPS:-

OLD STANDBY ----->New PRIMARY
-----------

SQL>archive log list
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>alter database commit to switchover to physical standby with session shutdown;
SQL>shutdown immediate
SQL>startup nomount
SQL>alter database mount standby database;
SQL>alter system set log_archive_dest_state_2=defer scope=both;
SQL>select name,open_mode,database_role,switchover_status from v$database;
SQL>show parameter log_archive_dest_state_2;

OLD PRIMARY ----->New STANDBY
-----------
SQL>archive log list
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate
SQL>startup

OLD STANDBY ----->New PRIMARY ----->ORIGINAL STANDBY
-----------
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>recover managed standby database disconnect;
SQL>select process,status,sequence# from v$managed_standby;

OLD PRIMARY ----->New STANDBY ----->ORIGINAL STANDBY
-----------
SQL>select name,instance_name,open_mode,database_role,switchover_status from v$database,v$instance;
SQL>show parameter log_archive_dest_state_2;
SQL>alter system set log_archive_dest_state_2=enable scope=both;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>select status, gap_status from v$archive_dest_status where dest_id = 2; --------11gr2 onwards



SOURCE : Internet

Reference(s):
http://emrebaransel.blogspot.in/2008/08/dataguard-switchover-guide-physical_09.html
http://subhendrasahu.blogspot.in/2012/05/switchover-from-primaryto-standby.html

http://shivanandarao.wordpress.com/2012/08/28/dataguard-failover/

DGMGRL Commands

dgmgrl (Data Guard Manager) utility

$dgmgrl

DGMGRL>connect /
DGMGRL>show configuration;
DGMGRL>show database '<database_name>';

DGMGRL>disable configuration;
DGMGRL>enable configuration;

DGMGRL> SHOW RESOURCE 'Sales_db';
DGMGRL> SHOW RESOURCE VERBOSE Sales_db;

The following example displays the last 20 lines of the SHOW LOG command output for the SITE_NAME site.
DGMGRL> SHOW LOG LATEST ON SITE 'SITE_NAME';
DRSLOG
LINE  OBJECT_ID DATE                LOG
--------------------------------------------------------------------------------

The following example displays the last 20 lines of the database alert log for the SITE_NAME site.

DGMGRL> SHOW LOG ALERT LATEST ON SITE 'SITE_NAME';
DRSLOG
LINE LOG
--------------------------------------------------------------------------------

dgmgrl (Data Guard Manager) utility

$dgmgrl [-silent | -echo] [username/password[@connect_identifier] [dgmgrl_command]]
$dgmgrl sys/pwd
$dgmgrl sys/pwd@oltp
$dgmgrl -logfile observer.log / "stop observer"
$dgmgrl sys/test@dgprimary "show database 'prod'"
$dgmgrl -silent sys/test@dgprimary "show configuration verbose"

ADD - Adds a standby database to the broker configuration.
DGMGRL> ADD DATABASE db_name [AS CONNECT IDENTIFIER IS conn_identifier] [MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> ADD DATABASE 'testdb' AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE 'logdb' AS CONNECT IDENTIFIER IS logdb MAINTAINED AS LOGICAL;
DGMGRL> ADD DATABASE 'devdb' AS CONNECT IDENTIFIER IS devdb.foo.com;

CONNECT - Connects to an Oracle database instance.
DGMGRL> CONNECT username/password[@connect_identifier]
DGMGRL> CONNECT sys;
DGMGRL> CONNECT sys@test;
DGMGRL> CONNECT sys/pwd;
DGMGRL> CONNECT sys/pwd@dwh;
$dgmgrl connect sys

CONVERT - Converts a database from one type to another (from Oracle 11g).
DGMGRL> CONVERT DATABASE database_name TO {SNAPSHOT STANDBY|PHYSICAL STANDBY};
DGMGRL> CONVERT DATABASE 'devdb' to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE 'devdb' to PHYSICAL STANDBY;

CREATE - Creates a broker configuration.
DGMGRL> CREATE CONFIGURATION config_name AS PRIMARY DATABASE IS db_name CONNECT IDENTIFIER IS conn_ident;
DGMGRL> CREATE CONFIGURATION 'dg' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod.foo.com;
DGMGRL> CREATE CONFIGURATION 'dg_test' AS PRIMARY DATABASE IS 'test' CONNECT IDENTIFIER IS test;

DISABLE - Disables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE CONFIGURATION;

DGMGRL> DISABLE DATABASE database_name;
DGMGRL> DISABLE DATABASE 'devdb';

DGMGRL> DISABLE FAST_START FAILOVER [FORCE | CONDITION condition];
DGMGRL> DISABLE FAST_START FAILOVER;
DGMGRL> DISABLE FAST_START FAILOVER FORCE;

EDIT - Edits a configuration, database, or instance.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;

DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'LogArchiveFormat'='log_%t_%s_%r_%d.arc';
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE devdb SET PROPERTY 'ReopenSecs'=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY Arch 

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE 'proddb' ON DATABASE 'proddb' SET PROPERTY 'StandbyArchiveLocation'='/oradata/arch/';

ENABLE - Enables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;

DGMGRL> ENABLE DATABASE database_name;
DGMGRL> ENABLE DATABASE 'devdb';

DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];
DGMGRL> ENABLE FAST_START FAILOVER;

EXIT - Exits the program.
DGMGRL> EXIT;

FAILOVER - Changes a standby database to be the primary database.
DGMGRL> FAILO';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='TRANSPORT-ON';
DGMGRL> EDIT DATABASE prodb SET STATE='LOG-TRANSPORT-OFF';
DGMGRL> EDIT DATABASE devdb SET STATE='ONLINE' WITH APPLY INSTANCE=devdb2;

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET AUTO PFILE [={init_file_path|OFF}];
DGMGRL> EDIT INSTANCE 'devdb1' ON DATABASE 'devdb' SET AUTO PFILE='initdevdb1.ora';


DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE 'proddb' ON DATABASE 'proddb' SET PROPERTY 'StandbyArchiveLocation'='/oradata/arch/';

ENABLE - Enables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;

DGMGRL> ENABLE DATABASE database_name;
DGMGRL> ENABLE DATABASE 'devdb';

DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];
DGMGRL> ENABLE FAST_START FAILOVER;

EXIT - Exits the program.
DGMGRL> EXIT;

FAILOVER - Changes a standby database to be the primary database.
DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]
DGMGRL> FAILOVER TO "testdb";
DGMGRL> FAILOVER TO "snapdb" IMMEDIATE;

HELP - Displays description and syntax for a command.
DGMGRL> HELP [command];
DGMGRL> HELP REINSTATE
DGMGRL> HELP EDIT

QUIT - Exits the program.
DGMGRL> QUIT;

REINSTATE - Changes a database marked for reinstatement into a viable standby.
DGMGRL> REINSTATE DATABASE database_name;
DGMGRL> REINSTATE DATABASE prim1;

REM - Comment to be ignored by DGMGRL.
DGMGRL> REM [comment];

REMOVE - Removes a configuration, Oracle database, or instance.
DGMGRL> REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
DGMGRL> REMOVE CONFIGURATION;
DGMGRL> REMOVE CONFIGURATION PRESERVE DESTINATIONS;


DGMGRL> REMOVE DATABASE database_name [PRESERVE DESTINATIONS];
DGMGRL> REMOVE DATABASE devdb;
DGMGRL> REMOVE DATABASE standby PRESERVE DESTINATIONS;

DGMGRL> REMOVE INSTANCE instance_name [ON DATABASE database_name];
DGMGRL> REMOVE INSTANCE inst1 ON DATABASE racdb;

SHOW - Displays information about a configuration, database, instance or FSFO.
DGMGRL> SHOW CONFIGURATION [VERBOSE];
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION VERBOSE;

DGMGRL> SHOW DATABASE [VERBOSE] db_name [property_name];
DGMGRL> SHOW DATABASE 'devdb';
DGMGRL> SHOW DATABASE VERBOSE 'test';
DGMGRL> SHOW DATABASE 'dwhdb' 'StatusReport';
DGMGRL> SHOW DATABASE 'proddb' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'proddb' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'testdb' 'ArchiveLagTarget';
DGMGRL> SHOW DATABASE 'testdb' 'LogShipping';
DGMGRL> SHOW DATABASE 'testdb' 'PreferredApplyInstance';
DGMGRL> SHOW DATABASE 'proddb' 'StatusReport';
DGMGRL> SHOW DATABASE 'testdb' 'RecvQEntries';
DGMGRL> SHOW DATABASE 'proddb' 'SendQEntries';

DGMGRL> SHOW INSTANCE [VERBOSE] instance_name [property_name] [ON DATABASE db_name];
DGMGRL> SHOW INSTANCE inst1;
DGMGRL> SHOW INSTANCE VERBOSE inst3;
DGMGRL> SHOW INSTANCE testdb 'TopWaitEvents';

DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;

SHUTDOWN - Shuts down a currently running Oracle instance.
DGMGRL> SHUTDOWN [NORMAL | IMMEDIATE | ABORT];
DGMGRL> SHUTDOWN;
DGMGRL> SHUTDOWN NORMAL;
DGMGRL> SHUT IMMEDIATE;
DGMGRL> SHUT ABORT;

SQL - Executes a SQL statement
DGMGRL> SQL "sql_statement";

START - Starts the fast-start failover(FSFO) observer.
DGMGRL> START OBSERVER [FILE=observer_configuration_file];
DGMGRL> START OBSERVER;

STARTUP - Starts an Oracle database instance.
DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT  |  MOUNT | OPEN [READ ONLY|READ WRITE]];
DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;
DGMGRL> STARTUP FORCE;
DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initdwh.ora NOMOUNT;

STOP - Stops the fast-start failover(FSFO) observer.
DGMGRL> STOP OBSERVER;

SWITCHOVER - Switches roles between a primary and standby database.
DGMGRL> SWITCHOVER TO standby_database_name;
DGMGRL> SWITCHOVER TO "standby";

VALIDATE - command to checks whether the database is ready for a role transition or not.

DGMGRL> VALIDATE DATABASE ...;    -- From Oracle Database 12c

$ORACLE_HOME/rdbms/log/drc*.log

alter system set dg_broker_start=false;
alter system set dg_broker_start=false sid='*';
alter system set dg_broker_start=FALSE SCOPE=spfile SID='*';
alter system set dg_broker_start=true;
alter system set dg_broker_start=true sid='*';
alter system set dg_broker_start=TRUE SCOPE=spfile SID='*';

alter system set dg_broker_config_file1='/u01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';
alter system set dg_broker_config_file2='/u01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';

Source: Internet

Recover Standby DB from a missing archivelog

A Physical Standby database relies on continuous application of archivelogs from a Primary Database to be in synch with it. In Oracle Database versions prior to 10g in the event of an archivelog gone missing or corrupt you had to rebuild the standby database from scratch.

In 10g you can use an incremental backup from SCN and recover the standby using the same to compensate for the missing archivelogs as shown below

Step 1: On the standby database check the current scn.
STDBY> set numwidth 30;
STDBY> select current_scn from v$database;

CURRENT_SCN
-----------
123456789

Step 2: On the primary database create the needed incremental backup from the above SCN
rman target /
RMAN> {
allocate channel c1 type disk; 
BACKUP INCREMENTAL FROM SCN 123456789 DATABASE
}
Step 3: SCP the backup files to standby server to /tmp/incr_bkp folder.

searching for all files that match the pattern /tmp/incr_bkp/

List of Files Unknown to the Database
=====================================

Step 4: Catalog the Incremental Backup Files at the Standby Database
/tmp/incr_bkp > rman target /

RMAN> CATALOG START WITH '/tmp/incr_bkp/';

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

Step 5: Apply the Incremental Backup to the Standby Database
RMAN> RECOVER DATABASE NOREDO;
Media recovery complete.

Step 6: Put the standby database back to managed recovery mode.
STDBY>> recover managed standby database disconnect;


From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence 
GAP - thread 1 sequence ....
**************************************************
This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

Step 7: On the primary create new standby controlfile
PRIM>alter database create standby controlfile as ‘/tmp/incr_bkp/standby01.ctl’;
System altered.

Step 8: At Standby .. Replace standby controlfile at all location as shown by controle_files parameter.
Copy the standby control file to the standby site. Shutdown the stanby database and replace the stanby controlfiles and restart the standby database in managed recovery mode...


Note: - FOR STANDBY DATABASES ON ASM additional steps is required after replacing the stanby control file. Like renaming datafiles ...

DGMGRL SWITCHOVER SWITCHBACK

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;

PROCESS CLIENT_P  SEQUENCE# STATUS
------- -------- ---------- ------
MRP0      N/A            1000 APPLIED_LOG

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;  ------ the status of redo log files
LGWR RFS 1000 CURRENT

Both the above current values must be same.


$ps -ef|grep dmon|grep -v grep

show parameter broker;

$dgmgrl

DGMGRL> connect /

Broker config pre switchover:
============================

DGMGRL> show configuration;

Databases:
    TESTPRI - Primary database
    TESTDG  - Physical standby database

Switchover Now:
--------------
DGMGRL> switchover to 'TESTDG';


Broker config post switchover:
=============================

DGMGRL> show configuration;

Databases:

    TESTPRI - Physical standby database

    TESTDG  - Primary database

Switchback Now:
---------------

DGMGRL> connect sys/sys@TESTDG

Connected.

DGMGRL> switchover to 'TESTPRI';

DGMGRL> exit


Troubleshooting Tips:
---------------------
1. Remember to create TEMP files on DG(new primary) after switchover

2. Give sys passsword explicitly as time of connecting using DGMGRL for switchover else it fails. Let us take an example where sys password was not given.

DGMGRL> connect /

Connected.

DGMGRL> switchover to 'TESTDG';

Performing switchover NOW. Please wait...

Operation requires shutdown of instance "TESTPRIR1" on database "TESTPRI".

Shutting down instance "TESTPRIR1"...

ORA-01017: invalid username/password; logon denied

You are no longer connected to ORACLE

Please connect again.

Unable to shut down instance "TESTPRIR1".

You must shut down instance "TESTPRIR1" manually.

Operation requires shutdown of instance "TESTPRI" on database "TESTDG".

You must shut down instance "TESTPRI" manually.

Operation requires startup of instance "TESTPRIR1" on database "TESTPRI".

You must start instance "TESTPRIR1" manually.

Operation requires startup of instance "TESTPRI" on database "TESTDG".

You must start instance "TESTPRI" manually.

Switchover succeeded. New primary is "TESTDG"


Fix:
----
Just manually stop and start the instances. Switchover of role reversal is already done.

3. Before executing the switchover you may reduce the number of ARCH processes to the minimum needed for both remote and local archiving. Additional ARCH processes can take additional time to shutdown thereby increasing overall switchover timings. Once the switchover has been completed you can reenable the additional ARCH processes.'log_archive_max_processes' is the parameter in question here.

This is it. So we saw how easy it was to switchover/switchback using Oracle Dataguard broker. If the configuration is set correctly, it's very easy to manage.

DATAGUARD SWITCHOVER SWITCHBACK

Pre-Switchover Steps:-
---------------------
1.Blackout Databases
2.Freeze Jobs
3.Stop Streams

------------------------------------------------------------------------------------------------------------------
PRIMARY NODE 1 (n1pr1)
----------------------
connect / as sysdba

set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select 

name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE,PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_c

har(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,gv$instance;

column role format a7 tru
column name format a10 wrap
select name,database_role,log_mode,protection_mode,protection_level from v$database;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status 

where dest_id in (1,2);

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

show parameter log_archive_dest_2;

select flashback_on from v$database;
YES 
if not, enable using,
alter database flashback ON; 

create restore point <before_test_ddmmyy> guarantee flashback database;

set linesize 121
col scn format 99999999
col time format a32

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name from gv$restore_point;

STANDBY NODE 1 (n1pr1)
----------------------
connect / as sysdba

set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select 

name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE,PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_c

har(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,gv$instance;

column role format a7 tru
column name format a10 wrap
select name,database_role,log_mode,protection_mode,protection_level from v$database;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status 

where dest_id in (1,2);

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

create restore point <before_test_ddmmyy> guarantee flashback database;

set linesize 121
col scn format 99999999
col time format a32

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name from gv$restore_point;

------------------------------------------------------------------------------------------------------------------
check Sync:
----------
DR SYNC STATUS
------------------------------------------------------------------------------------------------------------------

SWITCH OVER TO STANDBY
========================
@PRIMARY
--------
PRIMARY NODE 1 (p1nr1)
----------------------

SELECT * FROM v$block_change_tracking;
STATUS     FILENAME BYTES
---------- -------- -----

if ENABLED,
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

srvctl stop instance -d <database> -i <instance_n2pr2> -o immediate

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PRIMARY           TO STANDBY

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup mount;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PHYSICAL STANDBY  TO PRIMARY          ----------------> NEW STANDBY

@STANDBY
--------
PHYSICAL STANDBY 1 (n1dr1)
--------------------------

SELECT * FROM v$block_change_tracking;
STATUS     FILENAME BYTES
---------- -------- -----

if ENABLED,
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

srvctl stop instance -d <database> -i <instance_n2dr2> -o immediate

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PHYSICAL STANDBY  TO PRIMARY          

alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PRIMARY           TO STANDBY          ----------------> NEW PRIMARY           
------------------------------------------------------------------------------------------------------------------
Switchback to the old Primary  
======================
@NEW STANDBY
------------
PRIMARY NODE 1 (n1pr1)
----------------------
srvctl start instance -d <database> -i <instance_n2pr2> -o mount
connect / as sysdba
SELECT * FROM v$block_change_tracking;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PRIMARY           TO STANDBY          ---------------->BACK TO PRIMARY

@NEW PRIMARY
------------
PHYSICAL STANDBY NODE 1 (n1dr1)
-------------------------------
srvctl start instance -d <database> -i <instance_n2dr2> -o mount
connect / as sysdba
SELECT * FROM v$block_change_tracking;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup mount;

SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE     SWITCHOVER_STATUS
----------------  --------------------
PHYSICAL STANDBY  TO PRIMARY          ---------------->BACK TO STANDBY             

alter database recover managed standby database disconnect from session; 
         
------------------------------------------------------------------------------------------------------------------
check Sync:
----------
DR SYNC STATUS
------------------------------------------------------------------------------------------------------------------

Post-Switchback Steps:-
---------------------
1.Un-Blackout Databases
2.Un-Freeze Jobs
3.Start Streams
4.Drop restore points created at both Primary and Standby after confirmation from application team.
drop restore point <before_test_ddmmyy>;

set linesize 200
col scn format 99999999
col time format a35

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name from gv$restore_point;

DATAGUARD SYNC STATUS

@PRIMARY

connect / as sysdba
set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

select * from v$archive_gap;
select * from v$dataguard_stats;

show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;

Set lines 1000
Set pages 1000
Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off 
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual; 
select '****************Standby Log ship and Log Apply Status*****************' from dual; 
set head on 
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)

alter system switch logfile;        or
alter system switch all logfile;    or
alter system archive log current;

/
/

Set lines 1000
Set pages 1000
Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off 
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual; 
select '****************Standby Log ship and Log Apply Status*****************' from dual; 
set head on 
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/

THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)


@STANDBY
--------
connect / as sysdba
set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7

select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"from v$database,gv$instance;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;

select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);

select * from v$archive_gap;
select * from v$dataguard_stats;

show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;

PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT

col time format a40
select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

-------------------------------------------OR----------------------------------------------

DR SYNC STATUS
--------------------

@PRIMARY

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;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------

alter system switch logfile;
/
/

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;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
                                                                   0 OR min

-------------------------------------------OR--------------------------

DR SYNC STATUS
--------------------
@PRIMARY

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;

@STANDBY

select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;

-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
 @STANDBY

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0      N/A            1000 APPLIED_LOG

select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;  ------ the status of redo log files
LGWR RFS 1000 CURRENT

Both the above current values must be same.