Tuesday, 28 August 2012

11g R2 – Preparing the Primary Database for Standby Database Creation


Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Preparing the Primary Database for Standby Database Creation


Enable Forced Logging
Place the database in FORCE LOGGING mode using SQL*Plus.  This statement will wait for all unlogged direct I/O to flush, so it could take some time.
SQL> alter database force logging;

You want to enable FORCE LOGGING, because in some SQL statements, the user has the option of specifying the NOLOGGING clause.  Even although this statement indicates that the database operation is not logged in the online redo log file; a redo record is still written, but there is no data associated with this record.  This can result in not all the changes being available at the standby site, which could result in data loss, and a manual recovery process, in order to catch up on the missing data.

If the SSL authentication requirements have not been met; then each member of a Data Guard configuration must be configured to use a remote login password file.  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 from a user, or change the password of a user with SYSDBA or SYSOPER privileges; then you must update each Standby or Snapshot database with a fresh copy of the password file from the Primary database.

Data Guard uses Oracle Net sessions to transport redo data and control messages between the members of a Data Guard configuration.  These Redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol, or a remote login password file if the SSL authentication requirements are not met.

The SSL authentication requirements are met if one of the following requirements are present:
·         The databases are members of the same Oracle Internet Directory (OID) enterprise domain, and it allows the use of current user database links.
·         The LOG_ARCHIVE_DEST_n and FAL_SERVER database initialization parameters, that correspond to the databases; use Oracle NET connect descriptors configured for SSL.
·         Each Database has an Oracle Wallet or supported hardware security module that contains a user certificate with a Distinguished Name(DN), that matches the DN in the OID entry for the database.

Oracle recommends that you configure the primary database to receive redo data, when a standby configuration is created.  This way the Primary database can quickly transition to a Standby Database when needed.
There should be at least one more standby redo log group, then the number of redo log groups.  The size should be the same as the Redo Logs. 
SQL> alter database add standby logfile (‘/u01…/slog3.rdo’) size 500M;
Or
SQL> alter database add standby logfile thread 1 size 500m;

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 that need to be set to control the receipt of data and apply services, when the database is transitioned to a standby database role.

Database
DB_UNIQUE_NAME
Oracle Net Service Name
Primary
pretoria
pretoria
Physical Standby
Jhb
jhb

Primary Database: Primary Role Initialization Parameters
DB_NAME=pretoria
DB_UNIQUE_NAME=pretoria
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(Pretoria,jhb)’
CONTROL_FILES=’/u03/pretoria/control1.ctl’,’u04/pretoria/control2.ctl’
LOG_ARCHIVE_DEST_1=’LOCATION=/arch1/Pretoria/
                                              VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
                                               DB_UNIQUE_NAME=pretoria’
LOG_ARCHIVE_DEST_2=’SERVICE=jhb ASYNC
                                              VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                               DB_UNIQUE_NAME=jhb’
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

Then we need the additional parameters for when the primary database is transitioned to a standby database role.
FAL_SERVER=jhb
DB_FILE_NAME_CONVERT=’jhb’,’pretoria’
LOG_FILE_NAME_CONVERT=
       ‘/arch1/jhb/’,’/arch1/Pretoria/’,’/arch2/jhb/’,’/arch2/Pretoria/’
STANDBY_FILE_MANAGEMENT=Auto

If the parameters are set up like this, then none of the parameters need to change after a Role Transition.

Parameter
Recommended Setting
DB_NAME
On a primary database, specify the name used when the database was created.  On a physical standby database, use the DB_NAME of the Primary Database.
DB_UNIQUE_NAME
Specify a unique name for each database.  This name stays with the database and does not change, even if the primary and standby databases reverse roles.
LOG_ARCHIVE_CONFIG
The DG_CONFIG attribute of this parameter must be explicitly set on each database in a Data Guard configuration to enable full Data Guard functionality.  Set DG_CONFIG to a text string that contains the DB_UNIQUE_NAME of each database in the configuration, with each name in this list separated by a comma.
CONTROL_FILES
Specify the path name for the control files on the primary database.  It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control file.
LOG_ARCHIVE_DEST_n
Specify where the redo data is to be archived on te Primary and Standby systems.
·         LOG_ARCHIVE_DEST_1 archives redo data generated by the primary database from the local online redo log files to the local archived reo log files in /arch1/pretoria/
·         LOG_ARCHIVE_DEST_2 is valid only for the primary role.  This destination transmits redo data to the remote physical standby destination ‘jhb ’.

If a fast recovery area was configured with DB_RECOVERY_FILE_DEST initialization parameter.  Then if you have not configured a local archiving destination with the LOCATION attribute, DATA GUARD wil automatically use the LOG_ARCHIVE_DEST_1 initialization parameter as the default destination for local archiving.
LOG_ARCHIVE_DEST_STATE_n
Specify ENABLE to allow redo transport services to transmit redo data to the specified destination.
REMOTE_LOGIN_PASSWORDFILE
This parameter must be set to EXCLUSIVE or SHARED if a remote login password file is used to authenticate administrative users or redo transport sessions.
LOG_ARCHIVE_FORMAT
Specify the format for the archived redo log file using a thread %t, sequence number %s, and resetlogs ID %r.
LOG_ARCHIVE_MAX_PROCESSES = interger
Specify the number from 1 to 30 of archiver (ARCn) processes you want Oracle software to invoke initially.  The default value is 4.
FAL_SERVER
Specify the Oracle Net service name of the FAL server (typically this is the database running in the primary role).  When the ‘pretoria’ database is running in the standby role, it uses the ‘jhb’ database as the FAL server from which to fetch(request) missing archived redo log files if ‘jhb’ is unable to automatically send the missing log files.
FB_FILE_NAME_CONVERT
Specify the path name and filename location of the primary database datafiles, followed by the standby location.  This parameter converts the path names of the primary database datafiles to the standby datafile path names.  If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is required.  Note that this parameter is only used to convert path names for physical standby databases.  Multiple pairs of paths may be specified by this parameter.
LOG_FILE_NAME_CONVERT
Specify the location of the primary database online redo log files followed by the standby location.  This parameter converts the path names of the primary database log files to the path names on the standby database.  If the standby database is on the same system as the primary database or if the directory structure where the log files are located on the standby system is different from the primary system, then this parameter is required.  Multiple pairs of paths may be specified by this parameter.
STANDBY_FILE_MANAGEMENT
Set to AUTO so when datafiles are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.



Review you parameters carefully, to determine if there are any additional parameters that may need to be specified.  For example the Dump Destination parameters, if the directory location is different on the Primary and Standby databases.

If archiving is not enabled, you must put the database in ARCHIVELOG mode.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
To verify that you are in archivelog mode.
SQL> archive log list


P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.

1 comment:

  1. BlueHost is ultimately one of the best website hosting company for any hosting services you require.

    ReplyDelete