Data Guard

DATA GUARD:

Data Guard was introduced from 9i onwards (before it was called as Standby Server).

Data Protection modes:

Maximum Protection	No Data loss	Synchronous redo shipping
Maximum Availability	No Data loss	Synchronous redo shipping
Maximum Performance	0 – few seconds Data loss	Asynchronous redo shipping

Maximum Protection Mode:

-          Highest level of data protection

-          Enforce protection of every transaction.

-          If last standby is unavailable processing stop  at primary DB.

-          Good for Financial systems where no data loss is acceptable

Maximize Availability:

-          Enforces protection of every transaction.

-          If last standby is unavailable, processing continues at primary DB.

-          When it is re-available, synchronization is automatic.

Maximum Performance:

-          Highest level of performance

-          Protects from failure of any single component.

-          Least impact on primary/production DB.

-          Useful for applications that can tolerate some data loss.

There are 2 types of standby databases. They  are

1.      Local

2.      Remote

1)      Local Standby database:

Ø  Appropriate for highest data protection mode.

Ø  LAN links are cheap, reliable, have high bandwidth and low latency.

Ø  Switch over operations are very fast.

2)      Remote Standby database:

Ø  Best solution for Disaster Recovery.

Ø  WAN links are generally more expensive, less reliable, have lower bandwidth and higher latency.

Ø  Suitable for highest performance asynchronous data protection mode.

Primary database name: dg

Standby Database name: stdby

STEP 1: Create the same structure in standby database like primary database.

Ex:

Primary: /u01/dg/oradata

Standby:/u01/stdby/oradata

STEP 2: prepare primary database.

1.      Check FORCE_LOGGING is enabled or not by using V$DATABASE.

Enable Force Logging

SQL>STARTUP

SQL>SELECT NAME,

CONTROLFILE_TYPE,

FORCE_LOGGING,

OPEN_MODE

 FROM V$DATABASE;

SQL> ALTER DATABASE FORCE LOGGING;

STEP 3: create standby controlfile.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/././..’;

STEP 4: Primary Database parameters:

db_name=dg

db_block_size=4096

sga_target=500M

db_create_file_dest='/u01/dg/oradata'

undo_management=auto

undo_tablespace=undotbs

db_unique_name=dg

control_files='/u01/dg/oradata/control01.ctl','/u01/dg/oradata/control02.ctl'

#log_archive_dest=/u01/dg/admin/arch

log_archive_format=arch_%s_%t_%r_%d.arc

db_domain=apps.com

background_dump_dest=/u01/dg/admin/bdump

user_dump_dest=/u01/dg/admin/udump

core_dump_dest=/u01/dg/admin/cdump

dg_broker_start=true

remote_login_passwordfile='EXCLUSIVE'

log_archive_dest_1='LOCATION=/u01/dg/admin/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg'

log_archive_config='DG_CONFIG=(dg,stdby)'

log_archive_dest_2='SERVICE=stdby VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) LGWR ASYNC REOPEN=10 DB_UNIQUE_NAME=stdby'

FAL_CLIENT=dg

FAL_SERVER=stdby

standby_file_management=auto

STEP 5: Standby database parameters:

db_name=dg

db_block_size=4096

sga_target=500M

db_unique_name=stdby

undo_management=auto

undo_tablespace=undotbs

control_files=/u01/stdby/oradata/stdby01.ctl

#log_archive_dest=/u01/stdby/admin/arch

log_archive_format=arch_%s_%t_%r_%d.arc

db_domain=apps.com

background_dump_dest=/u01/stdby/admin/bdump

user_dump_dest=/u01/stdby/admin/udump

core_dump_dest=/u01/stdby/admin/cdump

standby_file_management=auto

dg_broker_start=true:

log_archive_dest_1='LOCATION=/u01/stdby/admin/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'

log_archive_config='DG_CONFIG=(dg,stdby)'

log_archive_dest_2='SERVICE=dg VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) LGWR ASYNC REOPEN=10 DB_UNIQUE_NAME=dg'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

FAL_CLIENT=stdby

FAL_SERVER=dg

standby_archive_dest='/u01/stdby/admin/arch' (this parameter is deprecated in 11g onwards)

remote_login_passwordfile='EXCLUSIVE'

db_file_name_convert= 'dg','stdby'

log_file_name_convert= 'dg','stdby'

STEP 6: Network configuration

Primary listener.ora file

dg = (DESCRIPTION =

      (ADDRESS = (HOST=192.168.1.99)(PROTOCOL = TCP) (PORT = 1533)))

SID_LIST_dg= (SID_LIST =

               (SID_DESC =

                (ORACLE_HOME=/u01/oraravi/db/tech_st/10.2.0)

                      (SID_NAME = dg )))

Primary tnsnames.ora file:

dg =   (DESCRIPTION =

         ( ADDRESS = (PROTOCOL = TCP) (PORT =1533) (HOST =192.168.1.99))

          (CONNECT_DATA =

            (SERVICE_NAME = dg)))

stdby = (DESCRIPTION =

         ( ADDRESS = (PROTOCOL = TCP) (PORT =1533) (HOST =192.168.1.77))

          (CONNECT_DATA =

            (SERVICE_NAME = stdby)))

Standby listener.ora

stdby = (DESCRIPTION =

         (ADDRESS = (HOST=192.168.1.77)(PROTOCOL = TCP) (PORT = 1533)))

SID_LIST_stdby = (SID_LIST =

                (SID_DESC =

                 (ORACLE_HOME=/u01/oraprod/proddb/10.2.0)

                      (SID_NAME = stdby )))

Standby tnsnames.ora

dg =   (DESCRIPTION =

         ( ADDRESS = (PROTOCOL = TCP) (PORT =1533) (HOST =192.168.1.99))

          (CONNECT_DATA =

            (SERVICE_NAME = dg)))

STEP 7: create standby log files in primary for standby database.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP <number>  ‘/../../../../’ SIZE <same as online redo log sizes>;

STEP 8: create password files for both primary and standby.

$orapwd file=orapw<sid> password=<XXXXX>  ignorecase=y

STEP 9: Take a backup and copy data files, log files, standby control file, Password file and archives from primary database to standby database.

STEP 10: start the standby database.

SQL> STARTUP NOMOUNT;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> SELECT database_role FROM v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

STEP 11: recover standby archives by using the following statement.

 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Switch over or Fail over case:

Altered at run time without loss of data or resetting of redo logs.

Primary to Standby:-

SQL> select DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS,DATAGUARD_BROKER from v $database;

DATABASE_ROLE    GUARD_S SWITCHOVER_STATUS    DATAGUAR

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

PRIMARY          NONE    TO STANDBY           DISABLED

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

SQL> alter database commit to switchover to standby;

Database altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1337632 bytes

Variable Size             314574560 bytes

Database Buffers          201326592 bytes

Redo Buffers                5869568 bytes

SQL> alter database mount standby database;

Database altered.

Standby to primary:-

SQL> alter database recover managed standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shut

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1337632 bytes

Variable Size             314574560 bytes

Database Buffers          201326592 bytes

Redo Buffers                5869568 bytes

Database mounted.

Database opened.

SQL> alter system switch logfile;

System altered.

Change  DATA Protection modes:-

 Go to primary database.

SQL> shut immediate;

SQL> startup mount;

SQL> alter database standby database set to maximize [protection / availability / performance];

SQL> alter database open;

Data Guard Broker:

-          Distributed management framework that automates & centralizes the creation, maintenance, and monitoring of Data Guard configuration.

-          Management operations can be performed locally or remotely through the broker’s easy to use interfaces

Ø  GUI based oracle data guard manager.

Ø  Data Guard command-line Interface.

Errors :

Archives not transferred from primary to standby?

Errors in file /u01/prod/oracle/product/diag/rdbms/stdby/stdby/trace/stdby_arc3_ 18736.trc:

ORA-16191: Primary log shipping client not logged on standby

FAL[server, ARC3]: Error 16191 creating remote archivelog file 'primary'

FAL[server, ARC3]: FAL archive failed, see trace file.

Errors in file /u01/prod/oracle/product/diag/rdbms/stdby/stdby/trace/stdby_arc3_ 18736.trc:

ORA-16055: FAL request rejected

ARCH: FAL archive failed. Archiver continuing

ORACLE Instance stdby - Archival Error. Archiver continuing.

Thu May 09 07:17:33 2013

Error 1017 received logging on to the standby

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

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.

      returning error ORA-16191

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

Solution:

Check password file and re-define the password file.

orapwd file=orapwstdby password=welcome ignorecase=y

In 11g set the parameter  “sec_case_sensitive_logon=false”

Error:-

SQL> startup

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1337632 bytes

Variable Size             314574560 bytes

Database Buffers          201326592 bytes

Redo Buffers                5869568 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 22464

Session ID: 1 Serial number: 7

In alert log file error shoes:

ORA-16072: a minimum of one standby database destination is required

Errors in file /u01/prod/oracle/product/diag/rdbms/stdby/stdby/trace/stdby_lgwr_22385.trc:

Solution:

SQL> startup mount

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1337632 bytes

Variable Size             314574560 bytes

Database Buffers          201326592 bytes

Redo Buffers                5869568 bytes

Database mounted.

SQL> alter database set standby database to maximize {AVAILABILITY/PERFORMANCE/PROTECTION};

Database altered.

SQL> alter database open;

Database altered.

**********************************************************
*********************************************

Physical versus Logical Standby database:

Physical: It is kept synchronized with the primary database by recovering the redo data received from the primary database. The recovery operation applies changes block-for-block using the physical row ID

• A physically identical copy of the primary database on a block-for-block basis.
•  To Synch with primary through Redo Apply.
• Read only access, but only in an Exclusive Mode (no redo apply at the same time).
• Can be used to offload Backups.
• Redo apply is faster and most efficient approach to apply to changes.

Logical: It is kept synchronized with the primary database by transforming the data in the redo logs received from primary database into SQL statements and then executing the SQL statements on the standby database.

• Contains the same logical information as the production database, although a physical organization and structure of the data can be different.
• To Synch with primary through SQL Apply.
• Read only access can be queried for reports while logs are being applied  via SQL.
• Can create additional indexes and Materialized views for better query performance.

Limitations of Logical:

1. Uses more resources than physical.
2. Has some restrictions on datatypes. type of tables, DDL and DML.

Note: we can create Temporary Tablespace with different Name and Size on Standby Database.

Shutting down/Stop the Physical Standby:

deffer the archive log destination 2 and perform a log switch on primary database.( it is not possible in the maximum protection mode the primary database have to be shutdown first).

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;

Find out if the standby database is performing managed recovery (SEC):

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS STATUS

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

ARCH    CLOSING

ARCH    CLOSING

RFS     WRITING

MRP0    WAIT_FOR_LOG

RFS     RECEIVING

RFS     RECEIVING

4. Cancel managed recovery operations (SEC)

SQL> ALTER DATABASE RECOVER MANAGE STANDBY DATABASE CANCEL;

5. Shut down the standby database (SEC)

SQL> SHUTDOWN IMMEDIATE;

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

Switching between the Read-Only Access and Managed-Recovery Mode

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

1. Cancel log apply services

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Open the database for read-only access

SQL> ALTER DATABASE OPEN READ ONLY;

Return back to managed-recovery mode:

3. Terminate all active user sessions on the standby database.

4. Restart log apply services:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

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

Role Management – Switchover

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

1. Verify that it is possible to perform a switchover operation (PRI)

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

SESSIONS ACTIVE

2. Convert the primary database to the new standby (PRI)

SQL> alter database commit to switchover to physical standby with session shutdown;

3. Shutdown the former primary and mount as a standby database (PRI)

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

at this point both databases are configured as standby <=

4. Defer the remote archive destination on the old primary (OLD PRI)

SQL> alter system set log_archive_dest_state_2=defer;

5. Verify that the physical standby can be converted to the new primary (SEC)

SQL> select switchover_status from v$database;

6. Convert the physical standby to the new primary (SEC)

SQL> alter database commit to switchover to primary with session shutdown;

7. Shutdown and startup the new primary (NEW PRI)

SQL> shutdown immediate

SQL> startup

8. Initiate Log Apply Services (NEW SEC)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

9. Enable Archiving to the Physical Standby Database (NEW PRI)

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;

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

Role Management – Failover

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

0. Consider if it would be faster to repair the primary database than to perform a role transition.

1. Identify and resolve any archived redo log gaps.

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

 THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

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

         1            13             14

2. In order to determine if there are any missing archived redo logs

you have to query the V$ARCHIVED_LOG view on all available databases in the configuration to obtain the highest sequence number for each thread.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

3. Register any copied archive log

SQL>  ALTER DATABASE REGISTER PHYSICAL LOGFILE '/ORA/dbs02/oracle/admin/arch/arch_0001_0000000015.dbf';

4. Initiate the failover operation on the target physical standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;

5. Convert the physical standby database to the primary role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

6. Shut down and restart the new primary database.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

7.  Enable the archiving destination on the new primary

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Queries:

 Primary:

SQL> select TYPE,DEST_NAME,status,error from v$archive_dest;

TYPE    DEST_NAME                 STATUS    ERROR

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

PUBLIC  LOG_ARCHIVE_DEST_1        VALID

PUBLIC  LOG_ARCHIVE_DEST_2        ERROR     ORA-16191: Primary log shipping client not logged on standby

Standby:

SQL> select status,process from v$managed_standby;

STATUS       PROCESS

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

CONNECTED    ARCH

CONNECTED    ARCH

CONNECTED    ARCH

CONNECTED    ARCH

WAIT_FOR_LOG MRP0