v Getting started with Dataguard – Concepts of Dataguard
v Link : http://docs.oracle.com/cd/E11882_01/server.112/e25608/standby.htm#i50947
§ Once the standby database is created and configured, Data Guard automatically maintains the standby database by transmitting primary database redo data to the standby system, where the redo data is applied to the standby database.
v Stand by database – A standby can be of three type
§ Physical Standby Database
§ Logical Standby Database
§ Snapshot Standby Database
o Physical Standby Database:
A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.
o A physical standby database can be opened for read-only access and used to offload queries from a primary database. If a license for the Oracle Active Data Guard option has been purchased, Redo Apply can be active while the physical standby database is open, thus allowing queries to return results that are identical to what would be returned from the primary database. This capability is known as the real-time query feature.
o If a license for the Oracle Active Data Guard option has not been purchased, a physical standby database cannot be open while Redo Apply is active, so the following rules must be observed when opening a physical standby database instance or starting Redo Apply:
Redo
Apply must be stopped before any physical standby database instance is opened
If
one or more physical standby instances are open, those instances must be
stopped or restarted in a mounted state before starting Redo Apply.
v Benefits of the Physical Standby
§ Disaster recovery and High Availability
§ Data Protection
§ Reduction in primarily database workload
§ Performance
v Creating a Physical Standby Database:
A. Preparing the Primary Database for Standby Database Creation
B. Step-by-Step Instructions for Creating a Physical Standby Database
C. Post-Creation Steps
A. Preparing the Primary Database for Standby Database Creation
Ø Enable Forced logging
Ø Configure redo transport Authentication
Ø Configure The Primary Database to Receive Redo Data
Ø Set Primary Database Initialization Parameters
Ø Enabling Archiving
ü Enabling Forced Logging:
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.
ü Configure Redo Transport Authentication: -- Assuming to use passwordfile
If the SSL authentication requirements are not met, each member of a Data Guard configuration must be configured to use a remote login password file and every physical standby database in the configuration must have an up-to-date copy of the password file from the primary database.
Whenever
you grant or revoke the SYSDBA or SYSOPER privileges or change the login
password of a user who has these privileges, you must replace the password file
at each physical or snapshot standby database in the configuration with a fresh
copy of the password file from the primary database.
ü Configure The Physical Database to Receive Redo logs: -- Optional task
Although
this task is optional, Oracle recommends that a primary database be configured
to receive redo data when a Data Guard configuration is created. By following
this best practice, your primary database will be ready to quickly transition
to the standby role and begin receiving redo data.
The
synchronous and asynchronous redo transport modes require that a redo transport
destination have a standby redo log. A standby redo log is used to store redo
received from another Oracle database. Standby redo logs are structurally
identical to redo logs, and are created and managed using the same SQL
statements used to create and manage redo logs.
Redo
received from another Oracle database via redo transport is written to the
current standby redo log group by an RFS foreground process.
· Steps by Step :task
V$ARCHIVE_DEST --view can be queried to see the current
settings and status for each redo transport destination.
1. Creating and Managing a Standby Redo Log
2. Configuring Standby Redo Log Archival
3. Cases Where Redo Is Written Directly To an Archived Redo Log File
1. Creating and Managing a Standby Redo log:
The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the V$LOG view to determine how many redo log groups are in the redo log at the redo source database and query the V$THREAD view to determine how many redo threads exist at the redo source database.
SQL> SELECT GROUP#, BYTES FROM V$LOG;
SQL> V$THREAD
Perform the following query on a redo destination database to determine the size of each log file and the number of log groups in the standby redo log: -- STANDBY REDO DEDTINATION
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
For example, assume that the redo log on the redo source database has two redo log groups and that each of those contain one 500 MB redo log file. In this case, the standby redo log should have at least 3 standby redo log groups to satisfy the requirement that a standby redo log must have at least one more redo log group than the redo log at the redo source database.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog3.rdo') SIZE 500M;
2. Configure Standby for Redo log for Archival
Standby Redo Log Archival to a Local FIle System Location
§ Take the following steps to set up standby redo log archival to a local file system location:
1. Set the LOCATION attribute of a LOG_ARCHIVE_DEST_n parameter to a valid pathname.
2. Set the VALID_FOR attribute of the same LOG_ARCHIVE_DEST_n parameter to a value that allows standby redo log archival
§ The following are some sample parameter values that might be used to configure a physical standby database to archive its standby redo log to a local file system location:
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive
VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
--Below lines are taken from a blog*
Below
is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Availability
configuration.
LOG_ARCHIVE_DEST_2=’SERVICE=standby
SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’
3. Cases Where Redo Is Written Directly To an Archived Redo Log File
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. The LOG_ARCHIVE_DEST_n parameter that is used for this purpose is determined when the standby database is mounted, and this choice is reevaluated each time a LOG_ARCHIVE_DEST_n parameter is modified.
ü Set Primary Database Initialization Parameters:
On
the primary database, you define initialization parameters that control redo transport
services while the database is in the primary role. There are additional
parameters you need to add that control the receipt of the redo data and apply
services when the primary database is transitioned to the standby role.
· Primary Database: Primary Role Initialization Parameters
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
shows
the additional standby role initialization parameters on the primary database.
These parameters take effect when the primary database is transitioned to the
standby role.
· Primary Database: Standby Role Initialization Parameters
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO
ü Enabling Archiving:
If
archiving is not enabled, issue the following statements to put the primary
database in ARCHIVELOG mode and enable automatic archiving:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
B. Step-by-Step Instructions for Creating a Physical Standby Database:
Creating a Physical Standby Database
Create a Backup Copy of the Primary Database Data files PRIMARY
Create a Control File for the Standby Database PRIMARY
Create a Parameter File for the Standby Database PRIMARY
Copy Files from the Primary System to the Standby System PRIMARY
Set Up the Environment to Support the Standby Database STANDBY
Start the Physical Standby Database STANDBY
Verify the Physical Standby Database Is Performing Properly STANDBY
v Link : http://docs.oracle.com/cd/E11882_01/server.112/e25608/standby.htm#i50947
§ Once the standby database is created and configured, Data Guard automatically maintains the standby database by transmitting primary database redo data to the standby system, where the redo data is applied to the standby database.
v Stand by database – A standby can be of three type
§ Physical Standby Database
§ Logical Standby Database
§ Snapshot Standby Database
o Physical Standby Database:
A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called Redo Apply, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms.
o A physical standby database can be opened for read-only access and used to offload queries from a primary database. If a license for the Oracle Active Data Guard option has been purchased, Redo Apply can be active while the physical standby database is open, thus allowing queries to return results that are identical to what would be returned from the primary database. This capability is known as the real-time query feature.
o If a license for the Oracle Active Data Guard option has not been purchased, a physical standby database cannot be open while Redo Apply is active, so the following rules must be observed when opening a physical standby database instance or starting Redo Apply:
Redo
Apply must be stopped before any physical standby database instance is opened
If
one or more physical standby instances are open, those instances must be
stopped or restarted in a mounted state before starting Redo Apply.v Benefits of the Physical Standby
§ Disaster recovery and High Availability
§ Data Protection
§ Reduction in primarily database workload
§ Performance
v Creating a Physical Standby Database:
A. Preparing the Primary Database for Standby Database Creation
B. Step-by-Step Instructions for Creating a Physical Standby Database
C. Post-Creation Steps
A. Preparing the Primary Database for Standby Database Creation
Ø Enable Forced logging
Ø Configure redo transport Authentication
Ø Configure The Primary Database to Receive Redo Data
Ø Set Primary Database Initialization Parameters
Ø Enabling Archiving
ü Enabling Forced Logging:
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.
ü Configure Redo Transport Authentication: -- Assuming to use passwordfile
If the SSL authentication requirements are not met, each member of a Data Guard configuration must be configured to use a remote login password file and every physical standby database in the configuration must have an up-to-date copy of the password file from the primary database.
Whenever
you grant or revoke the SYSDBA or SYSOPER privileges or change the login
password of a user who has these privileges, you must replace the password file
at each physical or snapshot standby database in the configuration with a fresh
copy of the password file from the primary database.ü Configure The Physical Database to Receive Redo logs: -- Optional task
Although
this task is optional, Oracle recommends that a primary database be configured
to receive redo data when a Data Guard configuration is created. By following
this best practice, your primary database will be ready to quickly transition
to the standby role and begin receiving redo data.
The
synchronous and asynchronous redo transport modes require that a redo transport
destination have a standby redo log. A standby redo log is used to store redo
received from another Oracle database. Standby redo logs are structurally
identical to redo logs, and are created and managed using the same SQL
statements used to create and manage redo logs.
Redo
received from another Oracle database via redo transport is written to the
current standby redo log group by an RFS foreground process.· Steps by Step :task
V$ARCHIVE_DEST --view can be queried to see the current
settings and status for each redo transport destination.1. Creating and Managing a Standby Redo Log
2. Configuring Standby Redo Log Archival
3. Cases Where Redo Is Written Directly To an Archived Redo Log File
1. Creating and Managing a Standby Redo log:
The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the V$LOG view to determine how many redo log groups are in the redo log at the redo source database and query the V$THREAD view to determine how many redo threads exist at the redo source database.
SQL> SELECT GROUP#, BYTES FROM V$LOG;
SQL> V$THREAD
Perform the following query on a redo destination database to determine the size of each log file and the number of log groups in the standby redo log: -- STANDBY REDO DEDTINATION
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
For example, assume that the redo log on the redo source database has two redo log groups and that each of those contain one 500 MB redo log file. In this case, the standby redo log should have at least 3 standby redo log groups to satisfy the requirement that a standby redo log must have at least one more redo log group than the redo log at the redo source database.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog2.rdo') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog3.rdo') SIZE 500M;
2. Configure Standby for Redo log for Archival
Standby Redo Log Archival to a Local FIle System Location
§ Take the following steps to set up standby redo log archival to a local file system location:
1. Set the LOCATION attribute of a LOG_ARCHIVE_DEST_n parameter to a valid pathname.
2. Set the VALID_FOR attribute of the same LOG_ARCHIVE_DEST_n parameter to a value that allows standby redo log archival
§ The following are some sample parameter values that might be used to configure a physical standby database to archive its standby redo log to a local file system location:
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive
VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
--Below lines are taken from a blog*
Below
is an example of the LOG_ARCHIVE_DEST_n parameter for a Maximum Availability
configuration.
LOG_ARCHIVE_DEST_2=’SERVICE=standby
SYNC AFFIRM NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=standby
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)’3. Cases Where Redo Is Written Directly To an Archived Redo Log File
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. The LOG_ARCHIVE_DEST_n parameter that is used for this purpose is determined when the standby database is mounted, and this choice is reevaluated each time a LOG_ARCHIVE_DEST_n parameter is modified.
ü Set Primary Database Initialization Parameters:
On
the primary database, you define initialization parameters that control redo transport
services while the database is in the primary role. There are additional
parameters you need to add that control the receipt of the redo data and apply
services when the primary database is transitioned to the standby role.· Primary Database: Primary Role Initialization Parameters
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
shows
the additional standby role initialization parameters on the primary database.
These parameters take effect when the primary database is transitioned to the
standby role.· Primary Database: Standby Role Initialization Parameters
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO
ü Enabling Archiving:
If
archiving is not enabled, issue the following statements to put the primary
database in ARCHIVELOG mode and enable automatic archiving:SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
B. Step-by-Step Instructions for Creating a Physical Standby Database:
Creating a Physical Standby Database
Create a Backup Copy of the Primary Database Data files PRIMARY
Create a Control File for the Standby Database PRIMARY
Create a Parameter File for the Standby Database PRIMARY
Copy Files from the Primary System to the Standby System PRIMARY
Set Up the Environment to Support the Standby Database STANDBY
Start the Physical Standby Database STANDBY
Verify the Physical Standby Database Is Performing Properly STANDBY
No comments:
Post a Comment