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.
No comments:
Post a Comment