Oracle Data Guard
Data Availability Modes in Data Guard
Data Availability Mode Configuration Process
For manual standby configuration you need to specify a:
Oracle Data Guard Manager
Oracle Data Guard Manager is an easy to use OEM application. It provides the monitoring, control and automation necessary to manager Data Guard configuration, including the primary and standby databases, log transfer and log apply services. There are two standby databases – logical and physical.
What is a Physical Standby Database?
A physical standby database is physically identical to the primary database on a block for block basis. The physical standby database is updated by performing recovery of the log files received from the primary database. It can either be recovering data or or open for read only reporting.
A production (primary) database is linked to one or more physical and/or logical standby databases (up to nine0 that are copies of the production database. The standby database is updated by automatically shipping redo logs as the primary database creates them. Redo information is applied to each standby database by using standard Oracle recovery.
The primary database is open and active. Physical standby databases are either in recover mode, or read-only, both not both. During an unplanned downtime of the primary database you can perform a failover to one of the standby databases.
During planned downtime for hardware upgrades and maintenance you can do a switchover where a standby database becomes the primary database.
Tasks to perform to set up the data guard
Before you can create a standby database using Data Guard manager, you need to make sure that your preferred credentials in OEM are set correctly. To do this:
Start, Programs, OMS, sysman/sysman.
From the console, select the Configuration pulldown, select preferences, preferred credentials. Select your primary database (orcl.world) and make sure sys is contained in the user name as SYSDBA.
For your DEFAULT node, make sure that username can “Log on as batch job”.
You also need to make sure that this user is an administrator, assigned to the ORA_DBA group.
From OEM, select tools, database applications, data guard manager.
Select Configuration Wizard. The Wizard creates a data Guard configuration that contains two sites: a primary and a standby site that are typically located on different nodes. Before creating a standby database, some prerequisites have to be met.
Create a Data Guard configuration named config1, click next. In the Primary database pane add the primary database to the configuration – orcl.world. Enter site_a for the primary site name, click next.
In the Standby Creation method choose Create a New Physical Standby database and click next. You can create the physical standby database in any Oracle 9i home. Select the node and Oracle home for the standby database. Enter site_b for the standby site name, click next.
In the Datafile Copy Location screen you will create a separate directory and place all the physical standby database datafiles in that directory. Copy all files to same directory on standby node and Standby archived log file directory fields enter the directory d:\oracleoradata\orcl2 and click next.
The Wizard selects appropriate values for all parameters. Make sure the standby database name is set to orcl2 and click next. Processing completes.
From the Data Guard manager Navigator expand your configuration config1. Login as sys through your primary database orcl.
Your physical standby database has now been created and is online. You will next simulate a working environment and how the Data Guard Manager can be used to handle log files and database downtime.
Changing the state of the physical standby database – Online, Apply Off, Read Only and Offline) when the log files are transported and applied to the standby database.
Expand site_b, then click on site_b_orcl2. Notice that the status is currently set to online. You will change the state of the physical database to Apply Off. Set State button.
Select Apply off state, and click OK – database is mounted standby, log apply is stopped.
To see what happens when a log is created switch to the OEM console. Since the primary database was shut down and restarted when you created the standby database you will need to reconnect to the database. Once you login, expand the primary database then Storage and right click on the Redo log groups. Select Switch Logfile.
Switch to data Guard manager and select the site_b_orcl2
database again. Then select Log files tab. You will notice that the logs have
been transported from the primary database but not applied. You will change the
state of the physical standby to online and then see that logs are applied.
Restart the Intelligent agent on the standby node, refresh the nodes in OEM.
Expand site_b then click on site_b_orcl2. Click set state. Choose read only – database is open read only, log apply is stopped. The database is now read-only.
Select data guard configuration config1, click data protection tab. To be in Maximum protection mode you will need to create the Standby Redo Logs. Click on the Standby Redo Log Assistant. Click OK to create the standby redo logs on both the primary database and physical standby database.
To change the data protection mode to maximum protection the LogXptMode must be set to SYNC. Select the physical standby database, site_b_orcl2, click the Properties tab. Click in the value filed for the property name LogXptMode, select SYNC from dropdown, click next. Now you are ready to change the data protection mode for site_a. Select config1, select Data Protection, Maximum Protection.
Changing the protection mode requires the primary instance to be shutdown and restarted. If one or both nodes are down biring them up in NOMOUNT state.
Creating and registering Data Guard test events
Switch to EM console. Right click on Events and Create Event. Enter Data Guard events:
Data Guard Actual Apply Delay
Data Guard Data Not Applied
Data Guard Logs Not Applied
Data Guard Logs Not Shipped
Data Guard Potential Data Loss
Data Guard Status
For each selected test set the critical threshold to 3 and the warning threshold to 1. Select interval to 1 minute, select register and add to library.
Performing a switchover
Go to Data Guard configuration config1, right click on configuration, choose switchover.
Physical vs Logical standby databases
Physical standby databases –protection from user and logical errors, fast failover, ability to do a planned failover for maintenance, ability to offload backups from the primary database, ability to open standby database as read-only for reporting.
Logical standby database is different from physical standby database because it does not perform media recovery, but instead applies SQL statement from the primary database. The block per block differences between the two in logical standby database.
Logical standby database has some benefits – ability to offload reporting from the primary database, ability to create additional objects to support better reporting, enables rolling upgrades of the primary database.
The logical standby database does not support all Oracle datatypes. The first step is to determine which application uses the unsupported datatypes:
SQL> SELECT DISTINCT owner, table_name
FROM dba_logstdby_unsupported
ORDER BY owner;
If your application uses unsupported datatypes, use physical standby. The next consideration is to find out if you need your database to be open read only or read-write while the updates are applied.
Creating a Physical Standby
Shutdown immediate;
Startup mount;
Alter database archivelog;
Alter database open;
The LOG_ARCHIVE_START is no longer required in 10G.
Due to new log transport security and authentication features it is mandatory to create a password file. In addition the SYS password has to be identical on all servers for log transport services to function.
$cd $ORACLE_HOME/dbs
$orapwd file=orapwOrlando password=not4u
Once the password file is created you must set the following parameter in the spfile while the database is in NOMOUNT state.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORFILE=exclusive SCOPE=spfile;
Any logging operations performed on the primary are not fully logged with the redo stream. In the force logging mode all nologging operations are permitted to run, but the logging still takes place.
SQL> ALTER DATABASE FORCE LOGGING;
Certain protection modes, such as maximum protection and maximum availability, mandate that the redo logs be present. Standby redo logs are highly recommended regardless of your standby mode on both nodes.
SQL> ALTER DATABASE
ADD STANDBY LOGFILE THREAD 1
(‘/database/10gDR/sr1a.dbf’) SIZE 500M;
You have to configure the primary init parameters so the database will seamlessly operate in either primary or standby mode without init modifications.
## Primary role parameters ##
DB_UNIQUE_NAME=
SERVICE_NAMES=
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(
LOG_ARCHIVE_DEST_1=
‘LOCATION=/database/10gDR/
VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_2
‘SERVICE=Nashville_hasun1
VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
## Standby role parameters ##
DB_FILE_NAME_CONVERT=
(‘/database/10gDR/Orlando/’,’/database/10gDR/Nashville/’)
LOG_FILE_NAME_CONVERT=
(‘/database/10gDR/Orlando/’,’/database/10gDR/Nashville/’)
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=Nashville_hasun1
FAL_CLIENT=Orlando_hasun1
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
‘/database/10gDR/
Or in RMAN: BACKUP CURRENT CONTROLFILE FOR STANDBY;
If your primary database is using an spfile, you can create a pfile for use with standby database.
SQL> CREATE PFILE ‘/database/10gDR/
Once you have a pfile created for your standby you will need to add a few parameters.
Control_files=(“/database/10gDR/data/control_Nashville.ctl”)
DB_UNIQUE_NAME=
SERVICE_NAME=
LOG_ARCHIVE_CONFIG=’DB_CONFIG=(
LOG_ARCHIVE_DEST_1=
‘LOCATION=/database/10gDR/
VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_2=’SERVICE=Orlando_hasun1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
## Standby role parameters ##
DB_FILE_NAME_CONVERT=
(‘/database/10gDR/Orlando/’,’/database/10gDR/Orlando/’)
LOG_FILE_NAME_CONVERT=
(‘/database/10gDR/Orlando/’,’/database/10gDR/Orlando/’)
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=Orlando_hasun1
FAL_CLIENT= Nashville_hasun1
Using an OS utility transfer backup files form the primary database, controlfile, init file to the standby host. If you are using RMAN, use the ‘… duplicate for standby’ operation to create a physical standby from RMAN backups.
On Windows, you have to create a service on standby host.
Oradim –NEW –SID
Ensure that DRS_START is set to TRUE in both primary and standby.
Add an entry to oratab on standby so the agent will not fail.
The primary database tempfile does not move to standby, manually add them.
- A listener running on the primary host
- A listener running on the standby host
- An Oracle net alias on the primary that points to the standby listener
- An Oracle net alias on the standby that points to the primary listener
SQL> CREATE SPFILE FROM PFILE;
SQL> STARTUP MOUNT; in Oracle10g, in previous versions it
is
On the primary node enter the following command:
SQL> ALTER SYSTEM SET log_archive_dest_state_2=enable scope=both;
Next, perform a log switch on the primary nodeand verify if the transmission of the log is successful.
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT status, error FROM v$archive_dest
WHERE dest_id=2;
If the transmission was successful, then the destination was successful.
The following statement will instruct the standby to start applying changes from redo logs:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Creating a Logical Standby
When supplemental logging is enabled, additional info is placed in the redo stream which is later used in SQL Apply to properly apply the row changes. To determine if the supplemental logging is enabled:
SQL> SELECT supplemental_log_data_pk AS pk_log,
Supplemental__log_data_ui AS ui_log
FROM v$database;
If either column reports NO, you must enable supplemental logging:
SQL> ALTER DATABASE
ADD SUPPLEMENTAL LOG DATA
(primary key, unique index) COLUMNS;
The following is an addition to the existing physical standby init file.
## Primary role parameters ##
DB_UNIQUE_NAME=
SERVICE_NAMES=
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(
LOG_ARCHIVE_DEST_1=
‘LOCATION=/database/10gDR/
VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_2
‘SERVICE=Nashville_hasun1
VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_3
‘SERVICE=Nashville_reports_hasun1
VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE)
DB_UNIQUE_NAME=Nashville_Reports
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=DEFER
## Standby role parameters ##
DB_FILE_NAME_CONVERT=
(‘/database/10gDR/Nashville/’,’/database/10gDR/Orlando/’)
LOG_FILE_NAME_CONVERT=
(‘/database/10gDR/
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=Nashville_hasun1
FAL_CLIENT=Orlando_hasun1
Create pfile from spfile for standby database:
SQL> CREATE PFILE FROM SPFILE;
Now that we have the text initialization parameter file, we can modify it to support a logical standby.
## Primary role parameters ##
DB_UNIQUE_NAME=Nashville_Reports
SERVICE_NAME=Nashville_Reports
LOG_ARCHIVE_CONFIG=’DB_CONFIG=(
LOG_ARCHIVE_DEST_1=
‘LOCATION=/database/10gDR/
VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME=Nashville_Reports
LOG_ARCHIVE_DEST_2=’SERVICE=
VALID_FOR=(ONLINE_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=
LOG_ARCHIVE_DEST_3=
‘LOCATION=/database/10gDR/Nashville_Reports/stby_arch/
VALID_FOR=(STANDBY_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME=Nashville_Reports
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
## Standby role parameters ##
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=Orlando_hasun1
FAL_CLIENT= Nashville_Reports_hasun1
SQL> CREATE DATABASE LOGICAL STANDBY CONTROLFILE AS ‘/database/backup/control.ctl’;
Transfer the controlfile to the standby host’s CONTROL_FILES directories.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISONNECT;
We need to check in the alert.log file for the media recovery completion message: MRP0: Media recovery complete.
Activate the standby database:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
It is a good practice to rename your database to something different from the value of the primary database. To do this we must first shut down the standby database and bring it back in the MOUNT state.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Next, we use the nid utility to change the database name to a distinct value on OS level.
Nid TARGET=SYS/Not4u DBNAME=Nashville_Reports. After that change the DB_NAME and ORACLE_SID to the new database name, generate a new password file.
SQL> ALTERE TABLESPACE temp
ADD TEMPFILE ‘/database/10gDR/temp.dbf’
SIZE 100M REUSE;
9. Create a database link to the primary database
This is necessary for use of the DBMS_LOGSTBY.INSTANTIATE_TABLE procedure. Reset the GLOBAL_DBNAME for logical standby so it is not the same as the primary database name.
10. Start the SQL APPLY engine
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
SQL> SELECT type, high_scn, status
FROM v$logstdby;
Another view is DBA_LOGSTDBY_PROGRESS view. The APPLIED_SCN lists the highest SCN that have been applied to the standby database, while the NEWEST_SCN has been received from the primary.
Oracle9i R2: Data
Guard SQL Logical Standby SQL Apply
Before
creating a logical standby database
The
following steps are handled automatically by Data Guard Manager:
-
Checking
for unsupported data types
-
Activating
supplemental logging
-
Placing
PRIMARY in ARHIVELOG, if not already
To ensure
successful of the logical standby some preliminary actions must be performed:
-
Ensure
row uniqueness
-
Verify
primary is running in ARCHIVELOG
-
Enable
FORCE LOGGING
-
Start
Resource Manager if you wish to perform a hot backup
-
Create
an alternate tablespace for logical standby system tables
The
following data types are not supported in logical standby: Simple and nested
abstract data types, collections (nested tables and VARRAY), object references,
ROWID, UROWID, LONG, LONG RAW. Tables with unsupported data types appear in the
DBA_LONGSTBY_UNSUPPORTED view. Every table there will be automatically skipped.
In
addition, the following are the objects not supported in logical standby
databases:
- Tables
and sequences in SYS schema
- Tables
with unsupported data types
- Tables
used to support functional materialized view
- Global
temporary tables
- IOTs
- User
tables that use data segment compression
- No PL/SQL
job can be executed until the standby made primary
Before setting up a logical
standby database, you should make sure the logical standby database can
maintain the data types and tables in your primary database.
To determine whether your
primary database contains unsupported objects, use the DBA_LOGSTDBY_UNSUPPORTED
view. For example, enter the following query on the primary database to list
the names of primary database tables (and columns and data types in those
tables) that are not supported by logical standby databases:
SQL> SELECT *
2 FROM dba_logstdby_unsupported;
If the primary database contains
unsupported tables, Log Apply Services will automatically exclude the tables
DML when applying redo logs to the logical standby database. If these
unsupported tables are critical to your business needs, consider modifying your
application or use a physical standby database instead.
Note: This view does not show any tables from the SYS
schema. This is because changes to the SYS schema objects are not applied to
the Logical Standby database.
In order to maintain data in a
logical standby database, the Log Apply Services must be able to identify the
columns that uniquely identify each row that have been updated in the primary
database. Table rows must be uniquely identified, either through the use of
primary keys on the primary database or through the supplemental logging
feature (discussed later) to identify rows that have been updated in the
primary database.
The key column in the above view
is BAD_COLUMN. If this view returns a row for a given table you may want to
consider adding primary or unique key constraint on the table. The
DBA_LOGSTDBY_NOT_UNIQUE view identifies tables that have no primary and no
non-null unique indexes. Most of the tables displayed in this view are
supported because their columns contain enough information to be maintained in
a logical standby database. Some tables, however, cannot be supported because
their columns do not contain the necessary information. Unsupported tables
usually contain a column defined using an unsupported datatype. This view contains
the following columns:
·
OWNER VARCHAR2(30) Schema name
·
TABLE_NAME VARCHAR2(30) Name of the table
·
BAD_COLUMN VARCHAR2(1) This column contains a value of Y or N:
Y indicates the table column is defined using an unbounded data type,
such as a CLOB. If two rows in the table match except in their LOB column, then
the table cannot be maintained properly. Log Apply Services will attempt to
maintain these tables, but you must ensure the application does not allow
duplicate rows based on the non-unbounded columns.
N indicates that enough column information is present to maintain the
table in the logical standby database, but the Log Transport Services and Log
Apply Services would run more efficiently if you added a primary key. You
should consider adding a disabled RELY constraint to these tables if the
application ensures that each row is unique outside the database.
To
maintain a table in a logical standby database, the supplemental logging
function automatically adds column data to the redo log for every update
performed on that table, as follows:
·
If the table has a primary key or a unique index with a non-null
column, the amount of additional information added to the redo log is minimal.
·
If the table does not have a primary key, supplemental logging
automatically creates a key by adding all scalar values for each row to the
redo log. However, this automated key creation might result in an unnecessary
increase in the amount of information written to the redo logs, and there is no
guarantee that this key is unique. This case is detected by a N in BAD_COLUMN
of the DBA_LOGSTDBY_NOT_UNIQUE view.
Note: Be careful to select columns for the disabled RELY constraint
that will create a primary key. If the columns selected for the RELY constraint
do not make a primary key for that table, Log Apply Services will fail to apply
redo information to the logical standby database.
FORCE
LOGGING mode is strongly recommended for logical standby databases to ensure
data availability at the standby database. Data Guard Manager does not
automatically enable this feature, nor does it perform any checks for it. This
mode controls whether or not the primary database server logs all changes in
the database except for changes to temporary tablespaces and temporary
segments.
·
FORCE LOGGING: This setting takes precedence over and is
independent of any NOLOGGING or FORCE LOGGING settings you specify for
individual tablespaces and any NOLOGGING setting you specify for individual
database objects. All ongoing, unlogged operations must finish before forced
logging can begin.
·
NO FORCE LOGGING: Cancels the force logging mode. NO FORCE
LOGGING is the default.
You
can issue the following query to determine the force logging status of the
primary database:
SQL>
SELECT force_logging FROM v$database;
FORCE_LOGGING
-------------
YES
Note: It is recommended to set the FORCE LOGGING clause before
performing the backup operation to create the standby database and remain in
FORCE LOGGING mode for as long as the standby database is active.
You must enable supplemental
logging before you create the logical standby database. This is required by
LogMiner so that it can uniquely identify the redo records. Enabling
supplemental logging ensures there is enough information in the redo stream to
transform the data back into SQL. You should also ensure archiving of the
current log file as the logical standby database cannot use redo logs that
contain both supplemental log data and non-supplemental log data.
Using Data Guard Manager to
create your Logical Standby database will automatically turn supplemental
logging on.
The
columns listed will have a value of YES when supplemental logging is turned on.
The values of YES means for each one:
·
SUPPLEMENTAL_LOG_DATA_MIN
LogMiner will have sufficient information to support chained rows and various
storage arrangements.
·
SUPPLEMENTAL_LOG_DATA_PK
All columns of the primary key are placed into the redo log whenever there is
an update.
·
SUPPLEMENTAL_LOG_DATA_UI
If any unique key columns are modified, all other columns belonging to the
unique key are also logged.
Note: If you enable supplemental logging on your primary database and
you have already created physical standby databases, then you must perform the
same command on each physical standby database to ensure that future switchovers
work correctly. This is only required if you have not used Data Guard Manager
to establish your standby configuration.
Logical standby databases use a
number of tables defined in the SYS and SYSTEM schemas. By default, these
tables are created in the SYSTEM tablespace. Some of these tables can rapidly
become very large. To prevent these tables from expanding the SYSTEM
tablespace, you should move the tables to a separate tablespace. Move the
tables to the new tablespace before they are populated during the logical
standby creation process.
After opening the primary
database, use the CREATE TABLESPACE statement to create a new tablespace for
the logical standby tables and use the DBMS_LOGMNR_D.SET_TABLESPACE procedure
to move the tables into the new tablespace.
The above example shows how to
create a new tablespace named LOGMNRTS$ and move the logical standby tables
into that tablespace.
Note: The above step is not performed by Data Guard Manager.
If the tablespace exists prior to the creation of the LogMiner data dictionary
on the standby site, then LogMiner will use the pre-created, and appointed
tablespace. If it does not exist, then LogMiner will use the SYSTEM tablespace
as the default.
Logical standby databases use a
number of tables defined in the SYS and SYSTEM schemas. By default, these
tables are created in the SYSTEM tablespace. Some of these tables can rapidly
become very large. To prevent these tables from expanding the SYSTEM
tablespace, you should move the tables to a separate tablespace. Move the
tables to the new tablespace before they are populated during the logical
standby creation process.
After opening the primary
database, use the CREATE TABLESPACE statement to create a new tablespace for
the logical standby tables and use the DBMS_LOGMNR_D.SET_TABLESPACE procedure
to move the tables into the new tablespace.
The above example shows how to
create a new tablespace named LOGMNRTS$ and move the logical standby tables
into that tablespace.
Note: The above step is not performed by Data Guard Manager.
If the tablespace exists prior to the creation of the LogMiner data dictionary
on the standby site, then LogMiner will use the pre-created, and appointed
tablespace. If it does not exist, then LogMiner will use the SYSTEM tablespace
as the default.
Using The Configuration Wizard
At
this step all recognized existing databases by Enterprise Manager are
displayed. In order to ensure a successful configuration creation using the
configuration wizard you must verify the following tasks:
·
The primary and standby databases must be Oracle9i release
9.2.0 or later if the standby database is a logical standby database.
·
If you plan to add an existing logical standby database, it
must be an Oracle9i release 9.2.0 database.
·
If you plan to create a new logical standby database, you
must have installed Oracle9i release 9.2.0 on the standby node.
Standby Environment Configuration Conditions
For
the successful configuration of a standby environment, several conditions must
be satisfied; they are discussed below.
New Databases
If you
want to create a new database in a standby configuration, that database cannot
already be part of an existing standby configuration, and must have never
existed in a standby configuration before.
Configure the Primary Database
The
primary database must be configured with a remote login password file so you
can connect to it from Enterprise Manager with SYSDBA privileges. The wizard
requires you connect to the primary database as a user with SYSDBA privileges.
To do this, there must be an Oracle Net listener running on the primary
database on a TCP address. The primary database must be configured to use the
server parameter (SPFILE) file.
Configure the Standby Site
When
adding an existing standby database, the standby database must meet the same
requirements as for the primary site. If you use the wizard to create the
standby database, you do not need to do this step.
Start the Intelligent Agent on the Primary and Standby Sites
See the
Enterprise Manager documentation for more information.
Discover the Primary and Standby Nodes using the Discovery Wizard
Discovering
the primary node identifies the primary database. Discovering the standby nodes
identifies either the standby database (if it exists) or the Oracle home where
you plan to create the new standby database.
Note: If a physical standby is to be included in the configuration, the
minimum release can be Oracle9i release 9.0.
In the following screen
illustrations we are using the Configuration Wizard to create a new logical
standby database. Data Guard Manager makes numerous environment checks as it
collects its information. It will warn you if your shared pool is lower then
160 MB. Although a Logical standby can be run with a shared pool smaller then
160 MB, you may have performance issues in a production environment if it is
below this.
If the primary database is open
and Resource Manager is not turned on then you will see the warning screen
above. If you are not running the primary database with the Resource Manager
active, the wizard will shutdown the primary instance. This ensures a quiesced
database. This quiesced state is essential to allow you to perform
administrative operations that cannot be safely performed in the presence of
concurrent transactions or PL/SQL operations.
If you are running with the
Resource Manager active, you will avoid this shutdown step. However, the
Resource Manager must have been active since the primary instance startup. If
you do not have Resource Manager active, you can define the
RESOURCE_MANAGER_PLAN initialization parameter to use a resource plan and then
shut down and start up the primary database. If you do not have a resource
plan, use one of the supplied plans by defining the SYSTEM_PLAN attribute. The
following example shows how to set the RESOURCE_MANAGER_PLAN initialization
parameter:
SQL> ALTER SYSTEM SET
2 RESOURCE_MANAGER_PLAN=SYSTEM_PLAN SCOPE=BOTH;
SQL> SHUTDOWN
SQL> STARTUP
If the RESOURCE_MANAGER_PLAN
initialization parameter was not defined when you started the instance, you
must restart your primary database to ensure that the Resource Manager is
running. However, this can still be faster than performing a cold backup
procedure.
Note: Even though RESOURCE_MANAGER_PLAN is a dynamic
parameter, it must be set since the startup of the primary instance in order to
support a successful logical standby creation. If you must set the parameter
dynamically, ensure the primary database is restarted prior to attempting a
logical standby creation. Resource Manager is required to effect the database quiesce.
This screen illustrates the
structures that are not supported by the Log Apply Services. It is equivalent
to the data returned by querying the data dictionary view
DBA_LOGSTDBY_UNSUPPORTED.
The Configuration Wizard will
set the conditions to enable supplemental logging for the primary database. The
supplemental information maintained in the redo logs helps SQL apply operations
correctly maintain tables in the logical standby database.
Setting the Standby Oracle
Home
This screen allows you to set
the name of the standby site.
If you are having both the
primary and the logical standby databases on the same machine, then the
LOCK_NAME_SPACE parameter will be set by the wizard in both initialization
parameter files with different values. Because both databases have the same
database name, they cannot be mounted at the same time on the same machine
unless this parameter is used.
If the list of Oracle homes is
empty, an error dialog is shown when the page displays and the wizard will exit
automatically. The configuration wizard retrieves the architecture information
from Enterprise Manager discovery information and compares the standby site
against the primary site. The check does not validate directory structure or
software versions.
Datafile Copy Location
This screen allows you to
specify the path names for the data files and controlfile locations. You can
choose to copy all files to the same directory location or select a different
location for the associated files. You can also use the file location assistant.
This allows you to set up a pattern from the primary to the standby side. Or
you can explicitly type in each one.
The Configuration wizard will
confirm if you wish to create the directory paths if they do not already exist.
The wizard will set the following
initialization parameters:
DB_FILE_NAME_CONVERT
Converts the filename of a new
datafile on the primary database to a filename on the standby database
LOG_FILE_NAME_CONVERT
Converts the filename of a new log
file on the primary database to the filename of a log file on the standby
database
LOG_ARCHIVE_FORMAT
The filename formats of archived redo
logs should include a sequence number. Specify the LOG_ARCHIVE_FORMAT
initialization parameter to make the sequence number part of the filename for
the archived redo log. For example: LOG_ARCHIVE_FORMAT = log%t_%s.arc
LOG_ARCHIVE_DEST_n
Automatically defines a destination
and the attributes for the Log Transport Services. Defaults to ARCH and ASYNC
attributes.
LOG_PARALLELISM
Ensures the value of the LOG_PARALLELISM
parameter is set the value of 1. Changing this parameter affects the way redo
logs are written. Logminer cannot read log files if this parameter is greater
than 1.
PARALLEL_MAX_SERVERS
Set on the standby databases. This
can be set on the primary database if you expect to perform switchover
operations. Log Apply Services use parallel execution processes to perform
processing, and use parallel apply algorithms to maintain a high level of
database performance. A minimum of 5 parallel query processes is required.
Thus, the value of the PARALLEL_MAX_SERVERS parameter must be 5 or greater. By
default, all of the available parallel query processes are used for Log Apply
Services. To strictly control the number of processes used by Log Apply
Services, use the procedure DBMS_LOGSTDBY.APPLY_SET ('MAX_SERVERS',...). For
more information on this procedure refer to the next lesson in this course.
STANDBY_ARCHIVE_DEST
The STANDBY_ARCHIVE_DEST parameter on
the logical standby database allows the RFS process to identify where to locate
incoming archive log files produced by the primary database.
STANDBY_FILE_MANAGEMENT
Enables or disables automatic standby
file management. When automatic standby file management is enabled, operating
system file additions and deletions on the primary database are replicated on
the standby database. Care must be taken when setting STANDBY_FILE_MANAGEMENT
and DB_FILE_NAME_CONVERT so that existing standby files will not be
accidentally overwritten. If the standby database is on the same system as the
primary database, then ensure that the primary and standby systems do not point
to the same files.
SHARED_POOL_SIZE
Set on the primary and standby
databases. Log Apply Services use the shared pool’s system global area (SGA) to
stage the information read from the redo logs. The more SGA available, the more
information can be staged. By default, one quarter of the value set for the
SHARED_POOL_SIZE parameter will be used by Log Apply Services. Use the
DBMS_LOGSTDBY.APPLY_SET('MAX_SGA',...) procedure to strictly control the amount
of SGA allocated for Log Apply Services.
Configuration Options
The configuration options screen
allows you to set the unique name for the instance. This sets the INSTANCE_NAME
initialization parameter.
You are able to view and edit
the initialization parameter file. However, you should not alter any of the
Data Guard required parameters. Any such alteration will be ignored by Data
Guard Manager, and defaults will apply. It is recommended that you use this
window to set the password for the SYS user to a non-default value.
The wizard automatically
modifies the standby node’s listener configuration file. The wizard assumes
that it will find the listener configuration file in the default location of
the standby Oracle home chosen for the new standby. You can specify a different
location for the file, ensuring that the file exists on the node before
beginning.
The parameter DB_BROKER_START
parameter is set to true. This enables Oracle to determine whether or not the
Data Guard Broker (DMON) process should be started. The automatic startup of
the DMON process is simplified by setting DG_BROKER_START to true in the
initialization parameter file.
Summary Window
This
summary screen displays some of the information collated. Pressing Finish on
this screen will bring you to the last screen of the wizard which shows the
progress of the creation of the logical standby. Once it is completed press
Close and you will return to the Data Guard Manager main window.
As
the Configuration wizard will use hot backup mode to copy the files, ensure
that there are no RMAN backup operations in place at the same time. You can
query v$session_longops using the following query to determine if RMAN is
running:
SQL>
SELECT sid, serial#, context, sofar, totalwork,
2
ROUND(sofar/totalwork*100,2) "% COMPLETE"
3 FROM v$session_longops
4 WHERE opname LIKE 'RMAN%'
5 AND opname NOT LIKE '%aggregate%'
6 AND totalwork != 0
7 AND sofar <> totalwork;
The
columns from the resulting SQL statement which is run in MONITORJOB, refer to
RMAN jobs in this manner:
·
SOFAR: Number of blocks read/or put into the buffer by the RMAN
process
·
TOTALWORK: Total number of blocks to be read and/or put into the
buffer
·
%COMPLETE: Percentage of SOFAR/TOTALWORK * 100
Progress Window
Having
set the Data Guard Manager configuration options through the previous screens,
Data Guard Manager now implements the changes:
·
Supplemental logging is enabled on the primary site as the very
first action
·
The primary database is shutdown and restarted as the Resource
Manager was not enabled for the instance
·
The datafiles and controlfile are copied in hot backup mode to the
standby site and renamed to the new directory structure (if necessary)
·
If your primary database had tempfiles, they were not copied to
the standby. New temporary datafiles are added to the standby site
·
The logical standby database name is changed using the DBNEWID
utility
·
The Log Apply Services is started for the logical standbv site.
·
The server parameter file is created for the standby
·
The Data Guard initialization parameters are set for the standby
·
The Data Guard Broker is started
·
The configuration is enabled
Data Guard Manager starts the
Log Apply Services and builds the LogMiner dictionary into the redo stream.
Building the LogMiner dictionary adds the primary Oracle data dictionary
information in the archived redo logs so that Log Apply Services can correctly
interpret the redo data. This dictionary information is used by the logical
apply engine to manage the logical standby database.
On the primary database, the
Configuration Wizard executes the above SQL statement to build the dictionary.
Data Guard Manager uses the
DBNEWID (nid) operating system utility to change the database name of the
logical standby database. Changing the name prevents any interaction between
this copy of the primary database and the original primary database. The
following example starts and mounts the database, changes the database name,
and shuts down the standby database:
SQL> STARTUP
PFILE=init$Log1.ora EXCLUSIVE MOUNT;
OS> nid
TARGET=SYS/CHANGE_ON_INSTALL
DBNAME=ORCL3 SETNAME=YES
Connected to database ORCL (DBID=1456557175)
Control Files in database:
/private2/ade/oradata/orcl3/t_cf1.f
Change database ID and database name ORCL to ORCL3? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1456557175 to 416458362
Changing database name from ORCL to ORCL3
Control File /private2/ade/oradata/orcl3/t_cf1.f - modified
Datafile /private2/ade/oradata/orcl3/t_db1.f - dbid changed, wrote new name
Datafile /private2/ade/oradata/orcl3/log_mnrts.f-dbid changed, wrote new name
Control File /private2/ade/oradata/orcl3/t_cf1.f-dbid changed, wrote new name
Database name changed to ORCL3.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL3 changed to 416458362.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.
SQL> SHUTDOWN IMMEDIATE
The Configuration Wizard changes
the DBNAME initialization parameter on the standby.
Note: For more details on this utility please refer to the Oracle9i
Database Utilities Release 2 (9.2).
By default, it is not possible
for a non privileged user to modify data on a logical standby database. This is
because the database guard is automatically set to ALL when the Log Apply
Services are started by the Data Guard Manager or when the following SQL
command is issued:
SQL> ALTER DATABASE START
LOGICAL STANDBY APPLY INITIAL;
With this level of security,
only SYS can modify data.
As a DBA you have the ability to change this security level to
the STANDBY level. In this case, users are able to modify data that is not
maintained by the logical apply engine. That is users will have access to any
manually skipped objects on the standby (see later in this course), or any
explicitly created objects on the standby when in the STANDBY security level. A
security level of NONE allows any user access to the standby database.
SQL Commands for logical standby
ALTER
DATABASE COMMIT TO SWITCHOVER
Performs a switchover operation to change the current primary database to the
standby role, and to change one standby database to a primary role. The SQL
statement clauses will differ depending on the standby configuration (primary /
physical / logical). Can only be entered when the logical standby database is
open.
ALTER
DATABASE START LOGICAL STANDBY APPLY
This statement starts
Log Apply Services on the logical standby database. Include the INITIAL keyword to begin Log Apply Services for
the first time on the logical standby database. This statement also prevents
users from making any modifications to the data being maintained in the logical
standby database. Can only be entered when the logical standby database is
open.
ALTER
DATABASE REGISTER LOGFILE
Allows the manual
registration of archived redo logs. This is useful for when a database backup
and associated log files are being recovered from an archive to be used as a
new standby. It is also useful for disaster scenarios where the final log file
hasn’t been successfully archived. The administrator can manually copy the file
to the standby, then use this command to feed it to the logical standby
database apply engine.
ALTER
DATABASE {STOP|ABORT} LOGICAL STANDBY APPLY
Stops Log Apply Services
on a logical standby database. Can only be entered when the logical standby
database is open.
ALTER
DATABASE ACTIVATE LOGICAL STANDBY DATABASE
Performs a forced
failover operation, where the primary database is removed from the Standby
environment and a standby database assumes the primary role.This command will
succeed no matter what the state of the existing primary database is. If more
than one logical standby database exists, care must be taken to ensure the same
amount of log data is available on all the systems. The standby database must
be mounted before it can be activated.
Bringing the standby database online manually
SQL> STARTUP NOMOUNT
SQL> ALTER DATABASE
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
On primary –
SQL> STARTUP
SQL> SELECT STATUS FROM V$ARCHIVE_DESTINATION
Active
On standby –
SQL> ALTER DATABASE RECOVER MANAGED STANDBY FINISH;
SQL> ALTER DATABASE OPEN READ ONLY;
Database manual switchover steps
SQL> SELECT switchover_status FROM v$database;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SQL> SELECT switchover_status FROM v$database;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY;