learn-oracle


   Oracle Jobs   

Learn Oracle Blog

Google
   Ask A Question   



Preparing the Primary Database for Standby Database Creation

PREVIOUS CHAPTER  |  NEXT CHAPTER





Before you create a standby database you must first ensure the primary database is properly configured.

Oracle DBA- Enable 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.

Oracle DBA-Create a Password File
Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed.

Oracle DBA-Configure a Standby Redo Log

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.

Perform the following steps to configure the standby redo log.

Step 1 Ensure log file sizes are identical on the primary and standby databases.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also have log file sizes of 200K.

Step 2 Determine the appropriate number of standby redo log file groups.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(maximum number of logfiles for each thread + 1) * maximum number of threads

Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.

Step 3 Verify related database parameters and settings.

Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.

Step 4 Create standby redo log file groups.

To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database. The following Example show how to create a new standby redo log file group using the ALTER DATABASE statement with variations of the ADD STANDBY LOGFILE GROUP clause.

Example 1 - Adding a Standby Redo Log File Group to a Specific Thread

The following statement adds a new standby redo log file group to a standby database and assigns it to THREAD 5:

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

The THREAD clause is required only if you want to add one or more standby redo log file groups to a specific primary database thread. If you do not include the THREAD clause and the configuration uses Real Application Clusters (RAC), Data Guard will automatically assign standby redo log file groups to threads at runtime as they are needed by the various RAC instances.

Example 2 - Adding a Standby Redo Log File Group to a Specific Group Number

You can also specify a number that identifies the group using the GROUP clause:

The THREAD clause is required only if you want to add one or more standby redo log file groups to a specific primary database thread. If you do not include the THREAD clause and the configuration uses Real Application Clusters (RAC), Data Guard will automatically assign standby redo log file groups to threads at runtime as they are needed by the various RAC instances.

Example 2 - Adding a Standby Redo Log File Group to a Specific Group Number

You can also specify a number that identifies the group using the GROUP clause:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

Using group numbers can make administering standby redo log file groups easier. However, the group number must be between 1 and the value of the MAXLOGFILES clause. Do not skip log file group numbers (that is, do not number groups 10, 20, 30, and so on), or you will use additional space in the standby database control file.

Step 5 Verify the standby redo log file groups were created.

To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created. For example:

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;


GROUP#     THREAD#    SEQUENCE#  ARC STATUS     
---------- ---------- ---------- --- ----------
         3          1         16 NO  ACTIVE    
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED

Oracle DBA-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 log 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 LGWR 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

These parameters control how redo transport services transmit redo data to the standby system and the archiving of redo data on the local file system. Primary Database: Standby Role Initialization Parameters

FAL_SERVER=boston

FAL_CLIENT=chicago

DB_FILE_NAME_CONVERT='boston','chicago'

LOG_FILE_NAME_CONVERT= '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'

STANDBY_FILE_MANAGEMENT=AUTO

Oracle DBA- Enable 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;

Related Topics:

Learn Oracle - Oracle Data Guard
Learn Oracle - Standby Database Types

PREVIOUS CHAPTER  |  NEXT CHAPTER



We learned Standby database types today. Next we will move to User Interfaces for Administering Data Guard Configurations.

You are just 10 steps away from becoming a Oracle DBA. Materialize your dream by following the The 10 Simple Steps .



More Tutorials on Oracle dba ...



Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information


Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info