NotesfromDataGuard
There are two types of Standby databases: 1, Physical standby database block-for-block basis the physically identical with the primary database user recovery technology 2, Logical standby database shares the same schema definition withe th
There are two types of Standby databases:
1, Physical standby database
block-for-block basis
the physically identical with the primary database
user recovery technology
2, Logical standby database
shares the same schema definition withe the primary database
executing sql statements on the standby database
use logMiner technology
There are three types of services provided with Data Guard:
1, redo transport services
2, log apply service: including redo apply and SQL apply
3, Role-management services
Oracle Data Guard supports two role-transition operations:
1, Switchover
2, Failover
Oracle Data Guard Data Protection Modes:
1,Maximum Protection
2,Maximum Availability
3,Maximum Performance
Benefits of Implementing Oracle Data Guard:
1,You can use a logical standby for real-time reporting and the physical standby database for point-in-time reporting.
2,Logical standby database is open and ready for reporting at all times.
Note:Standby database can use a different directory structure from the primary database.
On the primary database,Data Guard redo transport services use the following processes:
1,Log Writer(LGWR) process
2,Archiver(ARCn) Process
3,Fetch archive log(FAL)
Note:You can configure a primary database to ship redo information to a single standby database by using either LGWR or ARCn,but not both.
On the standby database,Data Guard log apply services use the following processes:
1,Remote file server(RFS) process
2,Archiver(ARCn) process
3,Managed recovery process(MRP)
4,Logical standby process(LSP)
Standby Redo Logs:
A standby redo log is required to implement:
1, The maximum protection and maximum availability levels of data protection.
2,Real-time apply
3,Cascaded redo log destinations
Standby redo logs are recommended for maximum performance data protection mode
Unless you are using the real-time apply feature,standby redo logs must be archived before the data can be applied to the standby database.
The standby archival operation occurs automatically.
The Data Guard physical standby Redo Apply architecture consists of:
A production(primary) database,which is linked to one or more standby databases(up to nine) that are identical copies of the production database.
--The limit of nine standby databases is imposed by the LOG_ARCHIVE_DEST_n parameter.In Oracle Database 10g,the maximum number of destinations is 10. One is used as the local archive destination,leaving the other nine for uses such as the standby database.
Note: You can use the Cascaded Redo Log Destination feature to incorporate more than nine standby databases in your configuration.
--The primary database is open and active.The standby databases are either in recovery mode or open in read-only mode,but not both.
--Redo is applied to each standby database by using standard Oracle recovery techniques.
Logical Standby Database: SQL Apply Architecture
Instead of using media recovery to apply changes(as in the physical standby database configuration),archived redo log information is transformed
into equivalent SQL statements by using LogMiner technology.These SQL statements are then applied to the logical standby database.The logical
standby database is open in read/write mode and is available for reporting capabilities.
The RECOVERY_MODE column of the V$ARCHIVE_DEST_STATUS view contains the value MANAGED REAL TIME APPLY when log apply services are running in
real-time mode.
For physical standby database,the managed recovery process(MRP) applies the redo from the standby redo log files after the remote file server(RFS) process finishes writing.To start real-time apply for a physical standby database,issue the following command:
alter database recover managed standby database using current logfile;
For logical standby database,the logical standby process(MRP) applies the redo from the standby redo log files after the remote file server(RFS) process finishes writing.To start real-time apply for a logical standby database,issue the following command:
alter database start logical standby apply immediate;
VALID_FOR : ALL_LOGFILES,ALL_ROLES is not recommended setting for a logical standby for any destination.Because a logical standby is an open
database that is creating its own redo, there is a real possibility of having the log files overwrite each other.This gives you a system that is
unrecoverabl and/or unable to keep synchronized with the primary database.
There is only one invalid combination: STANDBY_LOGFILE,PRIMARY_ROLE.
SELECT dest_id,valid_type,valid_role,valid_now from v$archive_dest;
select * from v$standby_log;
select * from v$logfile where type='STANDBY';
Although standby redo logs are used only when the database is operating in the standby role,you should create standby redo logs on the primary
database so that switching roles does not require additional DBA intervention.
You can maintain the standby database in one of the following modes:
For physical standby databases
1,Redo Apply
2,Open read-only mode
For Logical standby databases
Open read/write mode
Data Guard Broker:Requirments
1,Enterprise Edition of Oracle Databae 10g
2,LOCAL_LISTERNER on each instance must resolve to an address that is reachable by all members
3,GLOABL_DBNAME attribute must be set to a concatenation of: db_unique_name_DGMGRL.db_domain
The Data Guard monitor comprises two components: the DMON process and the configuration file.
Data Guard configuration file:
1,default names are dr1
2,default location for unix and linux:$ORACLE_HOME/dbs. for windows:ORACLE_HOME\database
startup mount;
alter database force logging; The default value is No.
ARCHIVE_LAG_TARGET: It defines the mean time to failover in the event your primary database fails and you must fail over to the standby.
It likes the parameter of "fast_start_mttr_target" mttr:mean time to recovery target
The standby_archive_dest initialization parameter overrides the directory locaition that is specified with the LOG_ARCHVIE_DEST_n parameter
if both the parameters are specified. So you should set standby_archive_dest to the same location as the local archvive destination for the
physical standby database so that all necessary archvied redo log files for the standby database are in the same location.
Defining the Redo Transport Mode
Use the attributes of LOG_ARCHIVE_DEST_n
1,ARCH and LGWR
2,SYNC and ASYNC(LGWR only)
3,AFFIRM and NOAFFIRM
(primary)(修改成MAXIMUM PROTECTION)
SQL> alter system set log_archive_dest_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE';
(standby)
SQL> alter system set log_archive_dest_2='SERVICE=orclpri LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl NODELAY MAX_CONNECTIONS=2 REOPEN=300 NOMAX_FAILURE';
Redo Transport Mode: ARCH|SYNC|ASYNC
ARCH: You do not need standby redo log files for this mode. This mode enables the lowest grade of protection to the primary database as well as
the lowest performance impact.
ASYNC: (LGWR,ASYNC,NOAFFIRM) This mode,along with standby redo log files,enables a moderate grade of protection to the primary database and incurs a lower performance impact.
SYNC:(LGWR,SYNC,AFFIRM) This mode, along with standby redo log files,is required for the maximum protection or maximum availability protection
modes.This redo transport mode enables the highest grade of data protection to the primary database,but it also incurs the highest performance
impact.
Data Protection Mode:
1,Maximum protection
2,Maximum availability
3,Maximum performance
Logical Standby Database features:
alter database guard all|standby|none; The default value is all,which means only the SYS can modify the data.
Preparing to create a logical standby database:
1,check for unsupported data types
2,Be aware of unspported DDL commands
3,Ensure row uniqueness
4,Verify that the primary database is configured for archivelog mode
5,Enable supplemental logging
Unsupported objects:
1,Tables and sequences in the SYS schema
2,Tables using table compression
3,Tables used to support materialized views
4,Global temporary tables
5,Tables with unsupported data types(BFILE,ROWID and UROWID,User-defiened types,object types REFs,Varrays,Nested tables,XMLtype)
Query DBA_LOGSTDBY_UNSUPPORTED on the primary database for tables with unsupported data types:
Query DBA_LOGSTDB_NOT_UNIQUE on the primary database to find tables without a unique identifier.
Add a primary key or unique index to ensure that SQL apply can efficiently apply data updates.
Preparing to create a logical standby database.Be sure to check that the initialization parameters have the following values:
PARALLEL_MAX_SERVERS>5
LOG_PARALLELISM=1
SHARED_POOL_SIZE:160M or higher(recommended)
Creating a logical Standby database:
1,create a physical standby database
2,stop redo apply on the physical standby database
3,prepare the primary database to support a logical standby database
4,Build a logMiner Dictionary in the redo data
5,convert to a logical standby database
6,open the logical standby database
7,Verify that the logical standby database is performing properly.
Fast-start failover can be enabled for automatic failover
select thread#,low_sequence#,high_sequence# from v$archive_gap;
alter database register physical logfile 'filespec1';