|
Introduction
Traditional Backup and recovery
from tape, storage based remote-mirroring, and database log shipping are the
traditional solutions for data protection and disaster recovery (DR)
requirements. Unfortunately, traditional solutions also have the traditional
shortcomings of being unable to reliably deliver aggressive objectives for both
recovery point (data protection) and recovery time (high availability), or do so
in a way that delivers maximum return on investment by avoiding underutilized
assets, high acquisition and support costs, and increased complexity.
Oracle Data Guard redefines what users should expect from a disaster recovery
solution. It can address both High Availability and Disaster Recovery
requirements, and is the ideal complement to Oracle Real Application Clusters
(Oracle RAC). Data Guard has the requisite knowledge of the Oracle database to
reliably protect a standby database from corruptions that attempt to propagate
from a primary database. It is straightforward to implement and manage. It also
enables all standby databases, both physical and logical, to be used for
productive purposes while in standby role. Data Guard delivers:
• Reliability– optimum data protection and availability. You always know the
state of your standby database and it can very quickly (in seconds), assume the
primary role.
• Lower cost and complexity – mature capabilities and rich management interface,
with most features included in Oracle Enterprise Edition
• Maximum return on investment – All standby databases can be utilized for
production purposes while in standby role. Idle resources are eliminated WITHOUT
increasing complexity.
Data Guard is a central component of an integrated Oracle Database High
Availability (HA) solution set that helps organizations ensure business
continuity by minimizing the various kinds of planned and unplanned downtime
that can affect their businesses.
Data Guard provides the management, monitoring, and automation software
infrastructure to create, maintain, and monitor one or more standby databases to
protect enterprise data from failures, disasters, errors, and data corruptions.
If the user desires, Data Guard will automatically failover production to a
standby system if the primary fails in order to maintain high availability
required for mission critical applications. In addition to providing HA/DR, Data
Guard standby databases can also support production functions for reporting,
query, backup and test, while in a standby role.
Data Guard is comprised of time-proven services in three different areas: Redo
Transport Services, Apply Services, and Role Management Services.
Data Guard
Oracle Data Guard is the new name for Oracle8i Standby Server, incorporating a
large number of new features.
- Architecture
- Database Synchronization Options
- Setup No-Data-Divergence
- Setup Primary Database
- Setup Standby Database
- Start Managed Standby Recovery
- Protect Primary Database
- Cancel Managed Standby Recovery
- Activating A Standby Database
- Backup Standby Database
- Database Switchover
- Database Failover
- Automatic Archive Gap Detection
- Background Managed Recovery
- Delayed Redo Application
Architecture
The Oracle 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.
The services required on the primary database are:
- Archiver Process (ARCn) - One or more archiver processes make copies of
online redo logs either locally or remotely for standby databases.
- 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.
- 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.
Database Synchronization Options
Data Guard can be configured to run with varying synchronization modes
indicating the potential for data loss:
- No-Data-Loss mode : This simply means that the log transport services will not
acknowledge modifications to the primary database until they are available to
the standby database. This doesn't mean that the modifications have been applied
to the standby database, merely that the log information is available to the log
apply services should failover occur. This mode is implemented using standby
redo logs on the standby server.
- Minimal-Data-Loss mode : When the performance requirements of the primary
database are the top priority this mode provides the optimum balance of data
protection and performance.
- No-Data-Divergence mode : This is an extension of the no-data-loss mode
whereby modifications to the primary database are prevented if conectivity
between the primary and at least one standby database is unavailable.
Setup No-Data-Divergence
To setup no-data-divergence, the most extreme level of data protection, then do
the following:
Setup Primary Database
- Shutdown the database using: SHUTDOWN IMMEDIATE
- Backup all database files.
- Add an entry for the standby server into the tnsnames.ora file:
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
* Assuming your database in already in ARCHIVELOG mode one of the archive
destinations will be set. Add the other entires:
CONTROL_FILES=primary.ctl
COMPATIBLE=9.0.1.0.0 (may change based on the version)
LOG_ARCHIVE_START=true
LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle\Oradata\TSH1\Archive MANDATORY REOPEN=30'
LOG_ARCHIVE_DEST_2='SERVICE=stby1 LGWR SYNC AFFIRM'
LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_FORMAT=arc%t_%s.arc
REMOTE_ARCHIVE_ENABLE=true
The LGWR SYNC AFFIRM keywords indicate that the Logwriter should synchronously
write updates to the online redo logs to this location and wait for confirmation
of the write before proceeding. The remote site will process and archive these
standby redo logs to keep the databases synchronized. This whole process can
impact performance greatly but provides maximum data security.
* Startup the database using: STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\initinstancename.ora
* Create standby database controlfile using: ALTER DATABASE CREATE STANDBY
CONTROLFILE AS 'c:\stbycf.f';
Setup Standby Database
* Copy the production backup files to the standby server.
* Copy the standby controlfile to the standby server.
* Alter the control_files and archive parameters of the init.ora as follows:
SERVICE_NAMES = stby1
CONTROL_FILES=standby.ctl
COMPATIBLE=9.0.1.0.0
LOG_ARCHIVE_START=true
LOCK_NAME_SPACE=stby1
FAL_SERVER=prim1
FAL_CLIENT=stby1
# Uncomment is filename conversion is needed
#DB_FILE_NAME_CONVERT=("/primary","/standby")
#LOG_FILE_NAME_CONVERT=("/primary","/standby")
STANDBY_ARCHIVE_DEST=C:\Oracle\Oradata\TSH1\Archive
LOG_ARCHIVE_DEST_1='LOCATION=C:\Oracle\Oradata\TSH1\Archive'
LOG_ARCHIVE_TRACE=127
LOG_ARCHIVE_FORMAT=arc%t_%s.arc
STANDBY_FILE_MANAGEMENT=auto
REMOTE_ARCHIVE_ENABLE=true
Add the following entries into the listener.ora file:
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512))
)
STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)
The file should resemble the following:
# LISTENER.ORA Network Configuration File: C:\Oracle\Ora901\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 1512))
)
)
(DESCRIPTION =
(PROTOCOL_STACK =
(PRESENTATION = GIOP)
(SESSION = RAW)
)
(ADDRESS = (PROTOCOL = TCP)(HOST = myHost)(PORT = 2481))
)
)
STANDBY_LISTENER = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(PORT=1512)(HOST=myHost))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (SID_NAME = PLSExtProc)(ORACLE_HOME = C:\Oracle\Ora901)(PROGRAM =
extproc))
(SID_DESC = (ORACLE_HOME = C:\Oracle\Ora901) (SID_NAME = TSH1)
)
)
* Reload the listener file using lsnrctl reload from the command prompt.
* Add the following entry into the tnsnames.ora file:
stby1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myServerName)(PORT = 1512))
)
(CONNECT_DATA =
(SERVICE_NAME = stby1.world)
)
)
Create standby redo logs on the standby database to receive online redo
information from the Logwriter on the primary database. The minimum number of
groups required is an exact match, number and size, of the primary database, but
performance may be increased by adding more:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
('C:\Oracle\Oradata\TSH1\redo1a.log','C:\Oracle\Oradata\TSH1\redo1b.log') SIZE
500K;
Start Managed Standby Recovery
During managed recovery the transfer of archivelogs is controlled by the servers
without user intervention.
* Copy all archive logs from the primary to the standby server. This is the only
time you should need to do this.
* From sqlplus do the following:
SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Protect Primary Database
Now that Data Guard is configured and running the primary database can be
prevented from applying updates unless the update has been sent to at least one
standby location. Connect to the primary database and execute:
ALTER DATABASE SET STANDBY DATABASE PROTECTED;
Cancel Managed Standby Recovery
To stop managed standby recovery:
SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
The database can subsequently be switched back to recovery mode as follows:
SQL> -- Startup managed recovery
SQL> CONNECT sys/password@standby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> -- Protect primary database
SQL> CONNECT sys/password@primary1 AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
Activating A Standby Database
If the primary database is not available the standby database can be activated
as a primary database using the following statements:
SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up
immediately. The previous primary database can then be configured as a standby.
Backup Standby Database
Backups of the standby database can only be performed if the database is shut
down or in read only mode. Read only mode is best for managed recovery systems
as archive logs will still be transfered during the backup process, thus
preventing gap sequences. Once the server is in the desired mode simply copy the
appropriate database files.
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby).
These roles can be altered at runtime without loss of data or resetting of redo
logs. This process is known as a Switchover and can be performed using the
following statements:
-- Convert primary database to standby
CONNECT sys/change_on_install@prim1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
-- Shutdown primary database
SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby database
STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
-- Convert standby database to primary
CONNECT sys/change_on_install@stby1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- Shutdown standby database
SHUTDOWN IMMEDIATE;
-- Open old standby database as primary
STARTUP PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
This process has no affect on alternative standby locations. The process of
converting the instances back to their original roles is known as a Switchback.
The switchback is accomplished by performing another switchover.
Database Failover
Graceful Database Failover occurs when database failover causes a standby
database to be converted to a primary database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
This process will recovery all or some of the application data using the standby
redo logs, therefore avoiding reinstantiation of other standby databases. If
completed successfully, only the primary database will need to be reinstatiated
as a standby database.
Forced Database Failover changes one standby database to a primary database.
Application data may be lost neccessitating the reinstantiation of the primary
and all standby databases.
Automatic Archive Gap Detection
Gaps in the sequence of archive logs can be created when changes are applied to
the primary database while the standby database is unavailable. In Oracle8i the
archive redo logs associated with these gaps had to be identified using the
V$ARCHIVE_GAP view and copied manually to the standby server before managed
recovery could be initiated again. In Oracle most of these gap sequences can be
resolved automatically. The following parameters must be added to the standby
init.ora file where the values indicate net services names.
FAL_SERVER = 'primary_db1'
FAL_CLIENT = 'standby_db1'
The FAL server is normally the primary database, but can be another standby
database. Once the standby database is placed in managed recovery mode it will
automatically check for gap sequences. If it finds any it will request the
appropriate files from the primary database via the FAL server. If the gap
sequences cannot be resolved the files have to be recovered manually.
Background Managed Recovery
In Oracle8i managed recovery caused the user session to hang until the process
was stopped by the user. This type of recovery is still available along with a
background recovery that spawns a new background process and frees the user
session:
-- User session hangs
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
-- User session released
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Delayed Redo Application
Application of the archived redo logs to the standby database can be delayed
using the DELAY keyword. If a rogue statement significantly damages the primary
database the DBA can choose to switch to the standby database, which will be in
a state prior to this action:
-- Delay application of archived redo logs by 30 minutes.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30;
-- Return to no delay (Default).
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
For further information see:
How to create Logical Standby?
Standby Creation Example:
Oracle provides two types of standby databases:
1. Physical Standby Database
Standby database is called “physical” if the physical structure of stand by
exactly matches with stand by structure. Archived redo log transferred from
primary database will be directly applied to the stand by database.
2. Logical Standby Database
Stand by database is called “logical”, the physical structure of both databases
do not match and from the archived redo log we create SQL statements then these
statements will be applied to stand by database
How to Create Logical Standby Database?
Now, let us talk about how we can create one logical standby database and
administer logical standby database.
Before we create logical database perform the following checks to make sure the
primary database qualify to have logical standby database.
• Determine whether primary database contains data types like LONG, NCLOB, LONG
RAW, BFILE those are not supported by standby database.
• Ensure that the tables in primary database can be uniquely identified.
• Ensure that the primary database is in ARCHIVELOG mode and that archiving is
enabled.
• Ensure supplemental logging is enabled on the primary database. To see whether
supplemental logging is enabled, start a SQL session and query the V$DATABASE
fixed view. For example, enter:
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI
FROM V$DATABASE;
SUP SUP
--- ---
YES YES
If supplemental logging is not enabled, execute the following statements:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX)
COLUMNS;
SQL> ALTER SYSTEM SWITCH LOGFILE;
• Ensure LOG_PARALLELISM init.ora parameter is set to 1 (default value).If you
plan to be performing switchover operations with the logical standby then you
must create an alternate tablespace in the primary database for logical standby
system tables. Use the DBMS_LOGMNR_D.SET_TABLESPACE procedure to move the tables
into the new tablespace. For example:
SQL> EXECUTE LOGMNR_D.SET_TABLESPACE
'logical_tblsp');
Steps to create Logical Standby Database
1. On primary database, perform cold backup.
2. Bring the primary database to mount state to create backup of control file.
3. Open the primary database and build the log miner dictionary.
SQL> ALTER system enable restricted session;
SQL> ALTER DATABASE OPEN;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
4. Archive the current online redo log and disable the restricted session.
5. Identify the archived redo log that contains the log miner dictionary
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES'
AND STANDBY_DEST='NO';
6. . Copy all these files with init parameter file to the stand by database.
7. On standby system, modify the copied primary init file to support the logical
standby feature. Some parameters affected are
control_files,standby_archive_dest,parallel_max_servers,instance_name.
8. Start and mount the standby database in exclusive mode.
9. Turn on the database guard.
ALTER DATABASE GUARD ALL;
10. Open the logical standby followed by a shutdown immediate or normal.
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SHUTDOWN IMMEDIATE;
11. Open the logical standby database in exclusive mode.
12. Create temporary table space for the standby database.
13. On logical standby database, register the archived log identified in step 5
and run the following command.
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE
'/u01/oradata/stby/arch/arc1_28.arc';
14. Run the following ALTER DATABASE statement and include the INITIAL keyword
to begin SQL apply operations for the first time on the logical standby.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
15. Configure listener on the standby database.
16. Once listeners running on both primary and standby databases run the
following command.
ALTER SYSTEM RESGITER;
17. Create tns names entry for primary database in standby host. Similarly,
create tns names entry for standby database in primary host.
18. Enable archiving in the logical standby database.
19. Start archive the current online redo log file. Verify that the remote
archiving succeeded by running the following select statement.
SQL> SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;
20. Verifying archived redo logs are being applied. To verify that the archived
redo logs are being applied, query the V$LOGSTDBY view. This view provides
information about the processes that are reading redo log information and
applying it to the logical standby databases. You can also query the
DBA_LOGSTDBY_PROGRESS view to find out the progress of SQL apply operations. The
V$LOGSTDBY_STATS view shows the state of the coordinator process and information
about the SQL transactions that have been applied to the logical standby
database.
Refer more information on Oracle Data Guard Concepts and Administration.
Information and source : Oracle
|
