DataGurd

                           Dataguard

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;

SWITCHOVER and SWITCHBACK in Data guard

Switching Over to a Physical Standby Database

SWITCHOVER STEPS:-

PRIMARY
-------
check DR SYNC STATUS
http://select-star-from.blogspot.in/2013/09/data-guard-sync-status.html

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

check DR SYNC STATUS
http://select-star-from.blogspot.in/2013/09/data-guard-sync-status.html

SWITCHBACK STEPS:-

OLD STANDBY ----->New PRIMARY
-----------
check DR SYNC STATUS
http://select-star-from.blogspot.in/2013/09/data-guard-sync-status.html

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

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.

Architecture - 3

3. Architecture - 3

1. Which background process write dirty blocks from database buffer cache to   data files?

  Database writer (DBWR)

2. Which background process writes data from log buffer to redo log files?  

     

     Log writer (LGWR)

3. Which background process performs Crash recovery?

   SMON (system monitor)

4. What is Log Switch?

   The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

5. What is On-line Redo Log?

   The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the   corresponding redo entries temporarily stores in redo log buffers of the SGA are   written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

6. What are the steps involved in Instance Recovery?

Rolling forward to recover data that has not been recorded in data files yet has      been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have  not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held      by transactions in process at the time of the failure. Resolving any Pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

7. What does COMMIT do?

 COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by  the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction  is committed.

8. How do you know whether the process is Server Side Process?

 By seeing the process in ps-ef as ORACLE_SID.

9. When ckpt occurs?

     1. For every 3 seconds
     2. When 1/3rd of DB buffer fills
     3. When log swtich occurs
     4. When database shuts down

10. What are the two steps involved in instance recovery?

  1. Roll forward (redofiles data to datafiles), 2.Roll backward (undo files to data files).

11. What is the use of SMON?

  SMON is an Oracle mandatory background process. It is used for Instance recovery.

12. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?

1.                    SYSTEM - Data dictionary tables.

2.                    DATA - Standard operational tables.

3.                    DATA2- Static tables used for standard operations

4.                    INDEXES - Indexes for Standard operational tables.

5.                    INDEXES1 - Indexes of static tables used for standard operations.

6.                    TOOLS - Tools table.

7.                    TOOLS1 - Indexes for tools table.

8.                    RBS - Standard Operations Rollback Segments,

9.                    RBS1, RBS2 - Additional/Special Rollback segments.

10.                  TEMP - Temporary purpose tablespace

11.                  TEMP_USER - Temporary tablespace for users.

12.                  USERS - User tablespace.

13. How do you know when the process is started?

  Using ps -ef grep process name

14. What is meant by redo log buffer?

Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

Architecture - 2

2. Architecture - 2

1. What is the basic element of Base configuration of an oracle Database?

It consists of one or more data files. One or more control files. Two or more redo log  groups. The Database contains multiple users/schemas one or more rollback segments
one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the database consists of SGA (Database buffer cache,Shared pool ,Redo log buffer)

SMON (System monitor)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGA.

2. What is the function of Optimizer?

The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

3. What is Execution Plan?

The combination of the steps the optimizer chooses to execute a statement is called an execution plan.

4. What are the different approaches used by Optimizer in choosing an execution plan?

Rule-based and Cost-based.

5. What are the different Components of SGA?

1. The fixed SGA (Fixed SGA)
2. Default block buffer (Db cache)
3. Different Standard Block buffers
4. Redo log buffer (Redo log buffer)
5. Java pool (Java pool)
6. Large pool (Large pool)
7. Shared pool (Shared pool)
8. Stream pool (Stream pool)
9. Result cache

6. What is dictionary cache?

The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.

7. What is Database Buffers?

 Database buffers are cache in the SGA used to hold the data blocks that are read from the data    segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size

8. What is the functionality of SYSTEM table space?

System tablespace is a main part of oracle database. All the database information is in it.it is created when database is created.It is managed by oracle server . dba cannot change its contents. it contains the data dictionary objects .

9. What is the function of checkpoint (CKPT)?

Checkpoint is a background process which ensures dbwn process has written data to datafiles and upadates control file and datafile header to establish data consistency.The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.

10. When does LGWR write to the database?

Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File.LGWR   writes  redo  log  entries  into  an  on-line  redo  log  file  when transactions commit and the log buffer files are full.

11. What is Shared SQL Area ?

 A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area.

12. What Does DBWR do?

 Database writer writes modified blocks from the database buffer cache to the data files.

13. What is server processes?

 A server process is one that handles user requests. When you type in a SQL statement, the server process handles the parsing and running of that SQL statement,

14. Name the process which carries the request to the memory components,And also fetches from disk to buffer?
  Server Process

Questions & Answers

1. What is the function of SMON in instance recovery?

A.It writes data to the archive log files.

B.It writes data to the online redo log files.

C.It frees resources held by user processes.

D.It synchronizes data file header and control files.

E.It roles forward by applying changes in the redo log.

F.It writes dirty buffers from the buffer cache to the data files.

correct answer: e

2. Which action occur during a checkpoint?

A.Oracle updates a control file.

B.Oracle performs a delayed block clean-out.

C.Oracle copies the online redo-log to the archive destinations. `

D.Oracle flushes the dirty blocks in the database buffer cache to disk.

correct answer: d

3. SMON process is used to write into LOG files

a.TRUE

b.FALSE

correct answer: b

4. Oracle does not consider a transaction committed until

A. The Data is written back to the disk by DBWR

B. The LGWR successfully writes the changes to redo

C. PMON Process commits the process changes

D. SMON Process Writes the data

correct answer: b

5. How many maximum DBWn(Db writers) we can invoke?

A. 3

B. 1

C. 10

D. 20

correct answer: d

6. Which activity would generate less undo data?

  

A.INSERT

B.UPDATE

C.SELECT

D.DELETE

correct answer: a

7. What happens when a user issues a COMMIT?

A. The CKPT process signals a checkpoint.

B. The DBWn process wrtes the transactions changed buffers to the datafiles.

C. The LGWR flushes the log buffer to the online redo log.

D. The ARCn process writes the change vectors to the archive redo log

correct answer: c

8.What happens when a user process fails?

    A.PMON performs process recovery.

    B.SMON performs ICR

    C.SMON frees resources held by user process.

    D.Oracle Updates a controlfile.

Answer:a

9.What are the free buffers in the database buffer cache.

    A.Buffer that have changed should be pushed to the disk.

    B.Buffers that are currently in use.

    C.Buffer that are being written to disk.

    D.Buffer that can be overwritten.

Answer:d

10.When thw SMON Procees perform ICR.

      A.For every startup

      B.Only at the time of startup after graceful shutdown

      C.Only at the time of startup after abort shutdown

Answer:c