11gR1 New Features
·
Compression of redo traffic over the
network in a Data Guard configuration : This feature improves redo
transport performance when resolving redo gaps by compressing redo
before it is transmitted over the network.
· You can now find the
DB_UNIQUE_NAME
of the primary database from the standby database by querying the new
PRIMARY_DB_UNIQUE_NAME column in the V$DATABASE view. Also, Oracle Data
Guard release 11g ensures each database's DB_UNIQUE_NAME is different.
After upgrading to 11g, any databases with the same DB_UNIQUE_NAME will
not be able to communicate with each other.
·
Heterogeneous Data Guard Configuration: This feature allows a mix of Linux and Windows primary and standby databases in the same Data Guard configuration.
Specific to Redo Apply (Physical Standby Database);
·
Real-time query capability of physical standby
·
Snapshot standby
·
Lost-write detection using a physical
standby : A "lost write" is a serious form of data corruption that can
adversely impact a database. It occurs when an I/O subsystem
acknowledges the completion of a block write in the database, while in
fact the write did not occur in the persistent storage. This feature
allows a physical standby database to detect lost writes to a primary or
physical standby database.
· A number of enhancements in RMAN help to simplify backup and recovery
operations across all primary and physical standby databases, when using
a catalog. Also, you can use the
RMAN DUPLICATE command to create a physical standby database over the network without a need for pre-existing database backups.
11gR2 New Features
· The new
ALTER SYSTEM FLUSH REDO SQL
statement can be used at failover time to flush unsent redo from a
mounted primary database to a standby database, thereby allowing a zero
data loss failover to be peformed even if the primary database is not
running in a zero data loss data protection mode.
· The
FAL_CLIENT database initialization parameter is no longer required.
Specific to Redo Apply (Physical Standby Database);
·
A corrupted data block in a primary
database can be automatically replaced with an uncorrupted copy of that
block from a physical standby database that is operating in real-time
query mode. A corrupted block in a physical standby database can also be
automatically replaced with an uncorrupted copy of the block from the
primary database.
Some Keynotes on Prerequisites
· The
COMPATIBLE database
initialization parameter must be set to the same value on all databases
in a Data Guard configuration, except when using a logical standby
database, which can have a higher COMPATIBLE setting than the primary
database.
· To protect against unlogged direct writes in the primary database that cannot be propagated to the standby database,
turn on FORCE LOGGING
at the primary database before performing datafile backups for standby
creation. Keep the database in FORCE LOGGING mode as long as the standby
database is required.
Important Notes on Creating a Physical Standby
· If the standby database will be hosted on a Windows system, use the
ORADIM utility to create a Windows service. For example:
WINNT> oradim –NEW –SID boston –STARTMODE manual
· If the primary database has a
database encryption wallet,
copy it to the standby database system and configure the standby
database to use this wallet.(The database encryption wallet must be
copied from the primary database system to each standby database system
whenever the master encryption key is updated.)
· Post-Creation :
o Upgrade the data protection mode
o Enable Flashback Database : Flashback Database removes the need to re-create the primary database after a failover.
Important Notes on Creating a Logical Standby
· Note that a logical standby database uses standby redo logs (SRLs) for
redo received from the primary database, and also writes to online redo
logs (ORLs) as it applies changes to the standby database. Thus,
logical standby databases often require additional ARCn processes to
simultaneously archive SRLs and ORLs. Additionally, because
archiving of ORLs takes precedence over archiving of SRLs, a greater number of SRLs may be needed on a logical standby during periods of very high workload.
· Determine Support for Data Types and Storage Attributes for Tables
· Ensure Table Rows in the Primary Database Can Be Uniquely Identified (
Look doc. for details)
·
Step-by-Step Instructions for Creating a Logical Standby Database:
o Create a Physical Standby Database
o Stop Redo Apply on the Physical Standby Database
o Prepare the Primary Database to Support a Logical Standby Database
§ LOG_ARCHIVE_DEST_3= 'LOCATION=/arch2/chicago/VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=chicago'
§
EXECUTE DBMS_LOGSTDBY.BUILD;
o Transition to a Logical Standby Database
§ ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;
§ In releases prior to Oracle Database 11g, you needed to create a new password file before you
opened the logical standby database. This is no longer needed.
§ SHUTDOWN; / STARTUP MOUNT;
§ LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/ VALID_FOR=
(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2= 'SERVICE=chicago ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3= 'LOCATION=/arch2/boston/ VALID_FOR=
(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=boston'
o Open the Logical Standby Database
§ ALTER DATABASE OPEN RESETLOGS;
§ ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Protection Modes:
· Execute the following SQL statement on the primary database in order to set the data protection mode.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
· Perform the following query on the primary db to confirm that it is operating in the new protection mode:
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;
Redo Transport Services:
o Redo Transport Authentication
Using SSL
o Redo Transport Authentication
Using a Password File: By default, the password of the SYS user is used to authenticate redo transport sessions when a password file is used. The
REDO_TRANSPORT_USER database
initialization parameter can be used to select a different user
password for redo transport authentication by setting this parameter to
the name of any user who has been granted the
SYSOPER
privilege. For administrative ease, Oracle recommends that the
REDO_TRANSPORT_USER parameter be set to the same value on the redo
source database and at each redo transport destination.
· LOG_ARCHIVE_DEST_STATE_n Initialization Parameter Values:
ENABLE, DEFER, ALTERNATE
· LOG_ARCHIVE_DEST_n parameter attributes :
o AFFIRM and NOAFFIRM
o ALTERNATE (not supported for LOG_ARCHIVE_DEST_11 through LOG_ARCHIVE_DEST_31)
o COMPRESSION (licensed)
o DB_UNIQUE_NAME
o DELAY
o LOCATION and SERVICE (LOCATION is not supported for LOG_ARCHIVE_DEST_11 thr 31)
o MANDATORY (not supported for LOG_ARCHIVE_DEST_11 through 31)
o MAX_CONNECTIONS
o MAX_FAILURE
o NET_TIMEOUT
o NOREGISTER
o REOPEN
o SYNC and ASYNC (SYNC is not supported for LOG_ARCHIVE_DEST_11 through 31)
o SERVICE (mandatory attribute for a redo transport destination)
o TEMPLATE
o VALID_FOR
· Redo received by a standby database is written
directly to an archived redo log file
if a standby redo log group is not available or if the redo was sent to
resolve a redo gap. When this occurs, redo is written to the location
specified by the LOCATION attribute of one LOG_ARCHIVE_DEST_n parameter
that is valid for archiving redo received from another database.
· If the redo source database is an Oracle Real Applications Cluster
(Oracle RAC) or Oracle One Node database, query the V$LOG view at the
redo source database to determine how many redo threads exist and
specify the corresponding thread numbers when adding redo log groups to
the standby redo log.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
Apply Services
· Canceling a Time Delay
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;
· If you define a delay for a destination that has real-time apply enabled,
the delay is ignored.
· As an alternative to setting an apply delay, you can use
Flashback Database to recover from the application of corrupted or erroneous data to the standby database. Flashback Database can quickly and easily flash back a standby database to an arbitrary point in time.
· Applying Redo Data to Logical Standby Databases
o Starting SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY [IMMEDIATTE];
o Stopping SQL Apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Role Transitions
Switchover Notes
·
Remove any delay in applying redo that may be in effect on the standby database that will become the new primary database.
· Before performing a switchover from an Oracle RAC primary database to a physical standby database,
shut down all but one primary database instance.
· Before performing a switchover to a physical standby database that is
in real-time query mode, consider bringing all instances of that standby
database to the mounted but not open state for the fastest possible
role transition.
SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$DATAGUARD_STATS;
NAME VALUE
------------------------ ---------------- --
apply finish time +00 03:09:38.7
apply lag +00 02:09:53
estimated startup time 48
standby has been open N
transport lag +00 00:11:12
Failover Notes
· If a standby database currently running in maximum protection mode will be involved in the failover, first
place it in maximum performance mode.
· If possible,
copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it.
·
Flush any unsent redo from the primary
database to the target standby database. If the primary database can be
mounted, it may be possible to flush any unsent archived and current
redo from the primary database to the standby database
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;
· Stop Redo Apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
· Finish applying all received redo data
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
· SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
· SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
· SQL> ALTER DATABASE OPEN;
· Using Flashback Database After a Switchover:
If the switchover involved a physical standby
database, the primary and standby database roles are preserved during
the flashback operation. That is, the role in which the database is
running does not change when the database is flashed back to the target
SCN or time to which you flashed back the database.
If the
switchover involved a logical standby database, flashing back changes
the role of the standby database to what it was at the target SCN or
time to which you flashed back the database.
· Using Flashback Database After a Failover
You can use Flashback Database to convert the
failed primary database to a point in time before the failover occurred
and then convert it into a standby database. (
Converting a Failed Primary Into a Standby Database Using Flashback Database)
Real-time query
· A physical standby database instance cannot be opened if Redo Apply is
active on a mounted instance of that database. Use the following SQL
statements to stop Redo Apply, open a standby instance read-only, and
restart Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
SQL> SELECT open_mode FROM V$DATABASE;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
Apply Lag
· To obtain the apply lag, query the V$DATAGUARD_STATS view. For example:
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';
NAME VALUE DATUM_TIME TIME_COMPUTED
--------- ------------- ------------------- ------------------
apply lag +00 00:00:00 05/27/2009 08:54:16 05/27/2009 08:54:17
The apply lag metric is computed using data that is periodically received from the primary database. The
DATUM_TIME column contains a timestamp of when this data was last received by the standby database. The
TIME_COMPUTED column
contains a timestamp taken when the apply lag metric was calculated.
The difference between the values in these columns should be less than
30 seconds. If the difference is larger
than this, the apply lag metric may not be accurate.
STANDBY_MAX_DATA_DELAY
·
If STANDBY_MAX_DATA_DELAY is set to 0, a
query issued to a physical standby database is guaranteed to return the
exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.
· The following additional restrictions apply if STANDBY_MAX_DATA_DELAY
is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is
used:
* The standby database must receive redo data via the SYNC transport.
* The redo transport status at the standby database must be
SYNCHRONIZED and the primary database must be running in either maximum
protection mode or maximum availability mode.
* Real-time apply must be enabled.
Automatic Repair of Corrupt Data Blocks
·
A physical standby database operating in
real-time query mode can also be used to repair corrupt data blocks in a
primary database. If a corrupt data block is discovered on a
physical standby database, the server attempts to automatically repair
the corruption by obtaining a copy of the block from the primary
database. No additional configuration is needed. If automatic repair is
not possible, an ORA-1578 error is returned.
· The RMAN RECOVER BLOCK command is used to manually repair a corrupted
data block. This command searches several locations for an uncorrupted
copy of the data block.
Tuning Queries on a Physical Standby Database
· Appendix D of the Active Data Guard 11g Best Practices white paper
describes how to tune queries for optimal performance on a physical
standby database.
Using RMAN
Interchangeability of Backups in a Data Guard Environment
· RMAN commands use the recovery catalog metadata to behave
transparently across different physical databases in the Data Guard
environment. For example, you can back up a tablespace on a physical
standby database and restore and recover it on the primary database.
Similarly, you can back up a tablespace on a primary database and
restore and recover it on a physical standby database.
Note: Backups of logical standby databases are not usable at the primary database.
· Backups of standby control files and nonstandby control files are
interchangeable. For example, you can restore a standby control file on a
primary database and a primary control file on a physical standby
database. This interchangeability means that
you can offload control file backups to one database in a Data Guard environment.
Association of Backups in a Data Guard Environment
· The recovery catalog tracks the files in the Data Guard environment by associating every database file or backup file with a
DB_UNIQUE_NAME.
The database that creates a file is associated with the file. For
example, if RMAN backs up the database with the unique name of standby1,
then standby1 is associated with this backup. A backup remains
associated with the database that created it unless you use the
CHANGE ... RESET DB_UNIQUE_NAME to associate the backup with a different database.
Accessibility of Backups in a Data Guard Environment
· In a Data Guard environment, the recovery catalog considers disk
backups as accessible only to the database with which it is associated,
whereas tape backups created on one database are accessible to all
databases.
Note: You can FTP a backup from a standby host to a primary host or vice
versa, connect as TARGET to the database on this host, and then
CATALOG the backup. After a file is cataloged by the target database,
the file is associated with the target database.
RMAN Configurations
· For primary ;
Specify when archived logs can be deleted with the
CONFIGURE ARCHIVELOG DELETION POLICY command.
For example, if you want to delete logs after ensuring that they
shipped to all destinations, use the following configuration:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
If you want to delete logs after ensuring that they were applied on all standby destinations, use the following configuration:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
For standby ;
Enable automatic deletion of archived logs once they are applied at the standby database:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
· Configure the connect string for the primary database and all standby
databases, so that RMAN can connect remotely and perform
resynchronization when the
RESYNC CATALOG FROM DB_UNIQUE_NAME command is used.
RMAN> CONFIGURE DB_UNIQUE_NAME BOSTON CONNECT IDENTIFIER 'boston_conn_str';
· Commands for Daily Tape Backups Using Disk as Cache
The script to perform daily backups looks as follows:
RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;
-- Resynchronizes the information from all other database sites (primary and other standby databases)
RECOVER COPY OF DATABASE WITH TAG 'OSS';
--
Rolls forward level 0 copy of the database by applying the level 1
incremental backup taken the day before. On the first day this command
is run there will be no roll forward because there is no incremental
level 1 yet. A level 0 incremental will be created by the BACKUP DEVICE
TYPE DISK ... DATABASE command. Again on the second day there is no roll
forward because there is only a level 0 incremental.
BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'OSS' DATABASE;
--
Create a new level 1 incremental backup. On the first day this command
is run, this will be a level 0 incremental. On the second and following
days, this will be a level 1 incremental.
BACKUP DEVICE TYPE SBT ARCHIVELOG ALL;
-- Backs up archived logs to tape according to the deletion policy in place.
BACKUP BACKUPSET ALL;
-- Backs up any backup sets created as a result of incremental backup creation.
DELETE ARCHIVELOG ALL;
-- Deletes archived
logs according to the log deletion policy set by the CONFIGURE
ARCHIVELOG DELETION POLICY command. If the archived logs are in a fast
recovery area, then they are automatically deleted when more open disk
space is required.
Registering and Unregistering Databases in a Data Guard Environment
· Only the primary database must be explicitly registered using the
REGISTER DATABASE command. You do this after connecting RMAN to the recovery catalog and primary database as TARGET.
· A new standby is automatically registered in the recovery catalog when you connect to a standby database or when the
CONFIGURE DB_UNIQUE_NAME command is used to configure the connect identifier.
· To unregister information about a specific standby database, you can use the
UNREGISTER DB_UNIQUE_NAME command.
Reporting in a Data Guard Environment
· Use the
RMAN LIST, REPORT, and SHOW commands with the FOR DB_UNIQUE_NAME clause to view information about a specific database.
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
RMAN> LIST ARCHIVELOG ALL FOR DB_UNIQUE_NAME BOSTON;
RMAN> REPORT SCHEMA FOR DB_UNIQUE_NAME BOSTON;
RMAN> SHOW ALL FOR DB_UNIQUE_NAME BOSTON;
