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:
- Uses more resources than physical.
- 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
Cancel managed recovery operations
(SEC)
SQL> ALTER DATABASE RECOVER
MANAGE STANDBY DATABASE CANCEL;
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
---------------------------------
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
1. Identify the
existing archived redo logs (PRI + SEC)
SQL> SELECT SEQUENCE#,
FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER
BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
1 23-FEB-04 23-FEB-04
2 23-FEB-04 23-FEB-04
3 23-FEB-04 23-FEB-04
3. Verify that the new
archived redo log was received (SEC)
SQL> SELECT SEQUENCE#,
FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER
BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
1 23-FEB-04 23-FEB-04
2 23-FEB-04 23-FEB-04
3 23-FEB-04 23-FEB-04
4 23-FEB-04 03-MAR-04
4. Verify that
the new archived redo log was applied (SEC)
SQL> SELECT SEQUENCE#,APPLIED
FROM V$ARCHIVED_LOG ORDER BY
SEQUENCE#;
SEQUENCE# APP
---------- ---
4 YES
Dynamic
performance views – can be query in managed recovery mode
V$MANAGED_STANDBY
– monitoring the log transport and apply activities
V$ARCHIVE_DEST_STATUS
– determine on the primary the most recently archived redo log at the standby
destination
V$ARCHIVE_DEST
- confirm from the primary site that automatic archiving to the standby is
working
V$LOG_HISTORY
- viewing the whole archive log history
V$STANDBY_LOG
- verifying that the standby redo log groups are created and running correctly
Data
Guard Broker:
Data
Guard Broker is distributed management framework that automates and centralizes
the creation, maintenance, and monitoring of Data Guard configurations
It offers
also an easy-to-use interfaces:
Oracle9i
Data Guard Manager, which is the Data Guard graphical user interface (GUI)
integrated in the Oracle Enterprise Manager tool.
Data
Guard command-line interface (CLI)
Determine the SCN of the STANDBY database.
On the standby database, find the SCN which will be used for the incremental backup at the primary database:
You need to use the 'lowest SCN' from the queries below:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
--------------
3164433
SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ;
MIN(F.FHSCN)
----------------
3162298
You need to use the 'lowest SCN' from the queries, in this example is SCN: 3162298. Therefore, From the above you need to backup from SCN 3162298
Redo Transport Compression in a Data Guard Environment (Doc ID 729551.1)
Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)
How to confirm if Redo Transport Compression is used In Dataguard? (Doc ID 1927057.1)
