Datagaurd

Overview:
  • Data Guard Reason for Deployment
  • Data Guard Architecture
  • Data Guard Service on Primary Database
  • Data Guard PHYSICAL STANDBY-LIFE HISTORY
  • Data Guard LOGICAL STANDBY-LIFE HISTORY
  • Data Guard Protection Mode
  • Data Guard Role Transitions
  • Data Guard Configuration Steps

DATA GUARD REASON FOR DEPLOYMENT
  • Power Failure
  • Fire
  • Natural Disaster
  • Planned Maintenance
  • Human Error – Against Data Security

DATA GUARD ARCHITECTURE


    DATA GUARD SERVICES ON PRIMARY DATABASE

      • Log Writer Process(LGWR)- Collects redo information and updates the online redo logs. It can also create local archive redo logs and transmit online redo to standby databases.
      • Arc hiver Process (ARCn) - One or more arc hiver processes make copies of online redo logs either locally or remotely for standby databases.
      • Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request.

      DATA GUARD SERVICES ON STANDBY DATABASE

        • Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
        • Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.
        • Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).
        • Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.
        DATA GUARD PHYSICAL STANDBY- LIFE HISTORY

          • Technology Introduced in ORACLE 7.2 as STANDBY.
          • Technology Marked as DATA GUARD in ORACLE 8.1.7 and above.
          • Standby is Identical copy of PRIMARY DATABASE.
          • Redo logs are transported from PRIMARY to STANDBY and applied on STANDBY.

          DATA GUARD LOGICAL STANDBY- LIFE HISTORY

            • Introduced in Oracle 9.2
            • Redo copied from Primary to Standby
            • Changes converted into Logical Change Records(LCR)
            • LCR applied on standby(SQL Apply)
            • Standby database can be opened for updates and can create new indexes for propagated objects.


            DATA GUARD PROTECTION MODE


              MAXIMUM PROTECTION MODE:
              • Zero Data loss.
              • Redo synchronously transported to Standby database.


              • Redo must be applied to at least one standby before transactions on primary can be committed
              • Processing on primary is suspended if no standby is available

              MAXIMUM AVAILABILITY MODE:
              • Minimal Data loss
              • Similar to Maximum protection Mode
              • If no STANDBY database is available processing continues on PRIMARY.

              MAXIMUM PERFORMANCE MODE:
              • It’s a default mode
              • Redo asynchronously shipped to standby database


              • If no standby database is available processing continues on PRIMARY.

              DATA GUARD ROLE TRANSITION


                DATAGUARD – SWITCH OVER
                • Planned failover to standby database
                • Original primary becomes new standby
                • Original standby becomes new primary
                • No data loss
                • Can switchback at any time
                DATAGUARD – FAIL OVER
                • Unplanned failover to standby database
                • Old primary may need to be rebuilt
                • Old standby becomes new standby
                • Possible data loss.

                 Procedure to create a Standby Database Using LGWR





                  Role
                  Step
                  Oracle DBA
                  The steps described in this document configure the standby database for maximum performance mode, which is the default data protection mode. The database will also be a Physical standby database with all directories in the same format as Production. The instruction relates to Oracle version 9iR2 (9.2).
                  Oracle DBA
                  Before starting the creation of the DataGuard Physical Standby database, verify that by adding Standby redo logs on the Primary & Standby systems, you will not exceed the MAXLOGFILES and MAXLOGMEMBERS values. Generate an ascii controlfile by running on the Primary database:

                  alter database backup controlfile to trace ;

                  The trace file is written to the UDUMP area, confirm that the values are sufficient for the number of redo log groups and members to reflect Standby redo logs (the same number of groups as exist for redo log groups) to be added.
                  If the MAXLOGFILES or MAXLOGMEMBERS values need to be adjusted, see Metalink Note 1008318.6 “HOW TO CHANGE MAXDATAFILES, MAXLOGFILES AFTER CREATING DATABASE”
                  Note – this is not applicable to 10gR2 databases.

                  Oracle DBA
                  Checklist of the tasks that you perform on the primary database to prepare for physical standby database creation:

                  a. Verify Archiving (as standard for DB build task)
                  b. Verify password file (as standard DB build task)
                  c. Enable Forced Logging
                  d. Configure Primary DB Oracle Net components
                  e. Set Primary Database Initialization Parameters
                  e. Create a backup of the primary database
                  f. Create a standby controlfile
                  g. Create an initialization parameter file for the standby
                  h. Transfer files to the standby host.
                  i. Prepare the standby host
                  j. Create the standby password file
                  l. Create an spfile for the standby instance
                  m. Start the standby database
                  n. Begin shipping redo to the standby database

                  Oracle DBA
                  Verify Archiving on the Primary database is enabled, under a Standard Installation the correct definition should exist by default.

                  Verify that the database is in Archive log mode:

                  archive log list ;

                  The “Database log mode” should be set to “Archive Mode”

                  Verify a local Archiving destination is defined and that automatic Archiving is enabled. The Archivelog destination should be defined using the log_archive_dest_1 parameter and the log_archive_start parameter set to TRUE:

                  show parameter log_archive_dest ;
                  show parameter log_archive_start ;

                  If the database needs to be put into Archive Log mode and / or the parameter values set to the correct values, then these changes should be applied as follows:

                  To define a log archive destination where none exists:

                  alter system set log_archive_dest_1=<location> scope=BOTH;
                  alter system set log_archive_dest_state_1=enable scope=BOTH;

                  To define a log archive destination using log_archive_dest_1 where log_archive_dest exists:
                  alter system reset log_archive_dest scope=SPFILE;
                  alter system set log_archive_dest_1=<location> scope=SPFILE;
                  alter system set log_archive_dest_state_1=enable scope=SPFILE;

                  and then bounce the Primary database for the parameter changes to take effect.

                  To enable automatic archiving:

                  alter system set log_archive_start=TRUE scope=BOTH;

                  To enable archivelog mode:

                  shutdown immediate;
                  startup mount;
                  alter database archivelog;
                  alter database open;

                  Oracle DBA
                  Create a password file, under a standard 9iR2 the correct definition should exist by default on the Primary database. If the primary database does not currently have a password file, create one with the following steps:

                  cd $ORACLE_HOME/dbs
                  orapwd file=orapw<PRIMARYSID> password=<password>

                  Once the password file is created, you must set the following parameter in the spfile while the database is in the nomount state:

                  alter system set remote_login_passwordfile=exclusive scope=spfile;

                  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
                  As the ORACLE user on the production database server, in the $ORACLE_HOME/network/admin directory, update the tnsnames.ora file with entries for the standby database.
                  i.e.
                  <SERVER>-<PRIMARYSID>, <SID>_<PRIMARYSITE> =
                  (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=<server>.<xxx>.<yyy>.<zzz>)(PORT=<PORTNUMBER>))
                  (CONNECT_DATA =
                  (SID = <PRIMARYSID>)
                  )
                  )

                  <SERVER>-<REMOTESID>, <SID>_<REMOTESITE> =
                  (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=<server>.<xxx>.<yyy>.<zzz>)(PORT=<PORTNUMBER>))
                  (CONNECT_DATA =
                  (SID = <REMOTESID>)
                  )
                  )

                  Oracle DBA
                  Configure the primary initialization parameters, when configuring the initialization parameters on the primary database, it is important to consider future role transitions. We must configure the parameters to control log transport services and log apply services so that the database will seamlessly operate in either role with no parameter modification. While the database is mounted on a primary controlfile, the standby parameters are not read and are not put into effect, so they will not affect the operation of the database while in the primary role.

                  The parameters shown here are to be placed into the primary init.ora:

                  ## Primary Role Parameters ##
                  ARCHIVE_LAG_TARGET=1800

                  DB_NAME=<PRIMARYSID>
                  SERVICE_NAMES=’<SERVER>.<XXX>.<YYY>.<ZZZ>’

                  LOG_ARCHIVE_DEST_1=
                  'LOCATION=/<path>/oradata/<PRIMARYSID>/archive'

                  LOG_ARCHIVE_DEST_2=
                  'SERVICE=”<SERVER>-<REMOTESID>”',' LGWR ASYNC NOAFFIRM NODELAY
                  OPTIONAL NOMAX_FAILURE REOPEN=300'

                  LOG_ARCHIVE_DEST_STATE_1=ENABLE
                  LOG_ARCHIVE_DEST_STATE_2=DEFER

                  REMOTE_ARCHIVE_ENABLE=TRUE
                  STANDBY_FILE_MANAGEMENT=AUTO
                  FAL_SERVER=<SID>_<REMOTESITE>
                  FAL_CLIENT=<SID>_<PRIMARYSITE>
                  DG_BROKER_START=FALSE
                  LOCAL_LISTENER=’LISTENER_<SID>’
                  STANDBY_ARCHIVE_DEST=’/<path>/oradata/<PRIMARYSID>/archive’


                  ## Standby Role Parameters Not needed if directories are the same##
                  #DB_FILE_NAME_CONVERT=('<remote_file_path>','/<local_file_path>')
                  #LOG_FILE_NAME_CONVERT=('<remote_file_path>','/<local_file_path>')

                  Note: We initially defer LOG_ARCHIVE_DEST_2 until the standby has been created and brought to the mount state.

                  Oracle DBA
                  Once the above parameters have been placed into a backup of the spfile ( a backup of the spfile can be obtained by running:

                  create pfile= '/<path>/<path>/<path>/init<primary>.ora' from spfile;

                  the primary database can then be shutdown:

                  sqlplus ‘/as sysdba’
                  shutdown immediate;

                  Once the database is shutdown then we can activate the pfile to be an spfile by:

                  create spfile from pfile='/<path>/<path>/<path>/init<primary>.ora'

                  Then we can restart the database:

                  startup

                  Oracle DBA
                  Create a RMAN backup of the Controlfile “for Standby” :


                  oradb <SID>
                  rman target /

                  run {
                  allocate channel c1 type disk format
                  '/<path>/oradata/d2d/<SID>/%d_stndby_controlfile_%U.rbck';
                  BACKUP CURRENT CONTROLFILE FOR STANDBY;
                  release channel c1;
                  }

                  Then create a backup of the primary database, a physical standby can be created using RMAN as long as all of the necessary archivelogs are available to bring the database to a consistent state.

                  cd /u01/util
                  /zoe/util/rman_backup_generic <SID>

                  Oracle DBA
                  Create an initialization parameter file for the standby, if your primary database is using an spfile, you will need to create a pfile for use by the standby. Enter the following command on the primary database:

                  create pfile= '/<path>/<path>/<path>/init<standby>.ora' from spfile;

                  Once you have a pfile created for your standby, you will need to adjust several parameters. Below are parameters that needed to be modified in our configuration:

                  DB_NAME=<REMOTESID>
                  SERVICE_NAMES='<SERVER>.<XXX>.<YYY>.<ZZZ>'

                  LOG_ARCHIVE_DEST_1=
                  'LOCATION=/<path>/oradata/<REMOTESID>/archive'

                  LOG_ARCHIVE_DEST_2=
                  'SERVICE=”<SERVER>-<PRIMARYSID>”',' LGWR ASYNC NOAFFIRM NODELAY
                  OPTIONAL NOMAX_FAILURE REOPEN=300'

                  LOG_ARCHIVE_DEST_STATE_1=ENABLE
                  LOG_ARCHIVE_DEST_STATE_2=DEFER

                  REMOTE_ARCHIVE_ENABLE=TRUE
                  STANDBY_FILE_MANAGEMENT=AUTO
                  FAL_SERVER=<SID>_<PRIMARYSITE>
                  FAL_CLIENT=<SID>_<REMOTESITE>
                  DG_BROKER_START=FALSE
                  LOCAL_LISTENER='LISTENER_<SID>'
                  STANDBY_ARCHIVE_DEST='/<path>/oradata/<REMOTESID>/archive'

                  ARCHIVE_LAG_TARGET=1800
                  AUDIT_TRAIL=NONE

                  ## Standby Role Parameters only if directories are different##
                  #DB_FILE_NAME_CONVERT=('<remote_file_path>','/<local_file_path>')
                  #LOG_FILE_NAME_CONVERT=('<remote_file_path>','/<local_file_path>')

                  Please note that other parameters, such as dump destinations, may need to be modified depending on your environment.

                  Oracle DBA
                  Transfer the RMAN backup files to the standby host. Using an operating system utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host. Use the same target directory structure on the Standby server for the backup location as exists on the source Primary server.

                  Oracle DBA
                  Prepare the standby host, install the same version of the Oracle as exists on the Primary site (including patchsets and Security Patches) as described in the appropriate Instruction document and create the directory structures that the database will be populating. Ie:

                  mkdir /uxx1/oradata/<SID>
                  mkdir /uxx2/oradata/<SID>
                  mkdir /uxx3/oradata/<SID>
                  mkdir /uxx4/oradata/<SID>
                  mkdir /uxx5/oradata/<SID>
                  mkdir /uxx5/oradata/<SID>/archive
                  mkdir /uxxx/oradata/<SID>/export
                  mkdir /uxxx/oradata/<SID>/d2dbackup

                  mkdir $ORACLE_BASE/admin
                  mkdir $ORACLE_BASE/admin/<SID>
                  mkdir $ORACLE_BASE/admin/<SID>/bdump
                  mkdir $ORACLE_BASE/admin/<SID>/adump
                  mkdir $ORACLE_BASE/admin/<SID>/cdump
                  mkdir $ORACLE_BASE/admin/<SID>/create
                  mkdir $ORACLE_BASE/admin/<SID>/pfile
                  mkdir $ORACLE_BASE/admin/<SID>/scripts
                  mkdir $ORACLE_BASE/admin/<SID>/udump


                  Oracle DBA
                  Edit the /etc/oratab file and place in this file the details for the standby database:

                  <SID>:<ORACLE_HOME>:<STARTUP_FLAG>

                  Set you environment by using :

                  . oraenv

                  Oracle DBA
                  Create the standby database password file. The password for the sys user must be the same as specified for the primary database. To create the standby password file, enter the following commands:

                  cd $ORACLE_HOME/dbs
                  orapwd file=orapw<REMOTESID> password=<same password as production>

                  Oracle DBA
                  Configure Oracle Net components on the Standby server. We must have Oracle Net listeners and Oracle Net tnsnames.ora aliases on both the primary and standby host in order to support Data Guards communication needs. Specifically the following should be configured:

                  A listener running on the primary host
                  An Oracle Net alias in the primary tnsnames.ora that points to the standby listener

                  Both of the items listed above will exist.

                  A listener running on the standby host
                  An Oracle Net alias in the standby tnsnames.ora that points to the primary listener

                  After the Oracle 9i Installation Work Instruction has been followed for the standby software install, there should be a listener running on the standby server. Confirm this is in place with an ORACLE_SID setting to match the DB name.

                  Oracle DBA
                  As the ORACLE user on the standby database server, in the $ORACLE_HOME/network/admin directory, update the tnsnames.ora file with entries for the Primary database.
                  i.e.
                  <SERVER>-<PRIMARYSID>, <SID>_<PRIMARYSITE> =
                  (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=<server>.<xxx>.<yyy>.<zzz>)(PORT=<PORTNUMBER>))
                  (CONNECT_DATA =
                  (SID = <PRIMARYSID>)
                  )
                  )

                  <SERVER>-<REMOTESID>, <SID>_<REMOTESITE> =
                  (DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=<server>.<xxx>.<yyy>.<zzz>)(PORT=<PORTNUMBER>))
                  (CONNECT_DATA =
                  (SID = <REMOTESID>)
                  )
                  )

                  Oracle DBA
                  Create an spfile for the standby instance, if so desired, convert the initialization parameter file copied from the primary into an spfile by entering the following command on the standby instance:

                  startup nomount pfile=’/<path>/<path>/<path>/<name of pfile>’
                  create spfile from pfile=’/<path>/<path>/<path>/<name of pfile>’;
                  shutdown immediate

                  Oracle DBA
                  Use RMAN to build the Standby database using the hot backup and the standby controlfile backup from the primary:

                  Note – for Windows platform databases, the Oracle service will need to be created first using the oradim command, for example:

                  C:\ > oradim -NEW -SID DGDB01 -STARTMODE manual

                  On the standby database log into sqlplus as the sysdba user and:

                  startup nomount
                  exit;

                  Then log into rman:

                  rman

                  RMAN> connect target sys/<password>@<ALIAS PRIMARY>
                  RMAN> connect AUXILIARY sys/<password>@<ALIAS REMOTE>

                  run {
                  [set archivelog destination to ‘<arclog destination on standby>’;]

                  allocate auxiliary channel t1 type disk;
                  DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
                  release channel t1;

                  }

                  After RMAN has built the Standby database and applied the Archive logs from the backup set, shutdown the Standby database and then mount it again

                  shutdown immediate ;
                  startup nomount
                  alter database mount standby database ;
                  exit

                  Oracle DBA
                  Create Standby Redo Logs on the Primary and Standby databases:

                  On the standby database log into sqlplus as the sysdba user. To verify that by adding the Standby redo logs you will not exceed the MAXLOGFILES and MAXLOGMEMBERS values, generate an ascii controlfile by running:

                  alter database backup controlfile to trace ;
                  Verify these values in the trace file generated in the UDUMP area. You will need to create 1 Standby redo log member per redo log group that exists.

                  The Standby redo logs need to be the same size as the redo log files that exist on the production database and there should be as many Standby logs as there are redo log groups. Verify the number & size of the redo logs by running:

                  select group#, bytes from v$log order by 1;

                  Then create the Standby redo logs. For each redo log group run:

                  alter database add standby logfile group <n>
                  <path to log a location>/stdby<nn>.rdo size <xxx> ;

                  When the Standby redo logs are created on the Standby database, repeat the process for the Primary database (note, this is not strictly required until the first switchover/failover occurs, but good practice to retain consistency between the Primary & Standby databases)

                  Oracle DBA
                  Begin shipping redo to the standby database, if you remember, earlier we deferred LOG_ARCHIVE_DEST_2 on the primary database until we had the standby mounted. Now it is time to enable that destination and begin shipping redo to the standby. On the primary database, enter the following command:

                  alter system set log_archive_dest_state_2=enable scope=both;

                  Perform a log switch on the primary and verify that the transmission of that log was successful:

                  alter system switch logfile;
                  select status, error from v$archive_dest where dest_id=2;

                  If the transmission was successful, the status of the destination should be valid. If the status is invalid, investigate the error listed in the error column to correct any issues.

                  With the Oracle Net infrastructure enabled and a Primary site log switch run, the FAL process should have automatically resolved any Archive log gaps that existed. Verify this by checking the Archive log directory on the Primary and Standby servers and the Standby Database alert log.

                  On the Standby database, set recovery to apply any Archive log files produced from the point when the Production backup was run and the current time.

                  alter database recover managed standby database disconnect;

                  As a final check, switch the logfiles again on the Primary database and monitor the Standby database alert log to verify that this has been written (from the Standby redo log) to the Standby server Archive log location and applied successfully to the Standby database.





                  Role
                  Step
                  Oracle DBA
                  The Primary and standby databases need to have the dataguard broker started. On both databases set DG_BROKER_START=TRUE. As SYSDBA, issue the command

                  alter system set DG_BROKER_START=TRUE scope=both;

                  Both databases need to be restarted.
                  A normal startup on primary and a “startup nomount” and “alter database mount standby database” on the standby
                  Oracle DBA
                  Once the Oracle databases, standby and production, are working correctly the implementer can configure DataGuard. This is done by entering DataGuard in line mode (on PRIMARY):

                  dgmgrl

                  connect sys/<password>

                  CREATE CONFIGURATION <SID> AS
                  PRIMARY SITE IS '<SERVER>-<PRIMARYSID>'
                  RESOURCE IS <SID>
                  HOSTNAME IS <PRIMARY_HOSTNAME>
                  INSTANCE NAME IS <SID>
                  SERVICE NAME IS <SERVER>-<PRIMARYSID>
                  SITE IS MAINTAINED AS PHYSICAL;

                  show configuration;

                  Configuration 'u9208' is
                  Primary Site is 'UKBLX119-U9208'
                  Current status for "u9208":
                  DISABLED
                  Oracle DBA
                  To add the standby database to the configuration next run DGMGRL on the primary server:

                  dgmgrl

                  connect sys/<password>

                  CREATE SITE '<SERVER>-<REMOTESID>'
                  RESOURCE IS <SID>
                  HOSTNAME IS <REMOTE_HOSTNAME>
                  INSTANCE NAME IS <SID>
                  SERVICE NAME IS <SERVER>-<REMOTESID>
                  SITE IS MAINTAINED AS PHYSICAL;

                  SHOW CONFIGURATION;

                  Configuration 'u9208' is
                  Primary Site is 'UKBLX119-U9208'
                  Standby Site is 'UKBLX123-U9208'
                  Current status for "u9208":
                  DISABLED
                  Oracle DBA
                  Enable the entire configuration, including all of the databases, with the following DGMGRL command on the Primary server:

                  dgmgrl

                  connect sys/<password>

                  DGMGRL> enable configuration
                  Enabled.

                  At this point, shutdown the Primary & Standby databases.

                  Restart the Primary database as normal.
                  Then start the Standby database using a “STARTUP NOMOUNT”.

                  At this point, the DataGuard Broker should take control of the DG setup and put the Standby database into Managed recovery Mode.

                  Verify and check the Primary & Standby database alert logs.
                  Confirm the DataGuard Broker is OK by running on the Primary and Standby servers:


                  DGMGRL> show configuration
                  Configuration 'u9208' is
                  Primary Site is 'U9208-ukblx119'
                  Standby Site is 'U9208-ukblx123'
                  Current status for "u9208":
                  SUCCESS

                  Oracle DBA
                  This has enabled the dataguard configuration

                  Oracle DBA
                  The Dataguard Broker configuration has an effect in that a LOG_ARCIVE_DEST_3 parameter is set and populated :

                  log_archive_dest_3='service="ukblx123-u9208"','LGWR NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 async=2048
                  register nonet_timeout'

                  and this is also enabled :

                  log_archive_dest_state_3='ENABLE'

                  Oracle DBA
                  From the dataguard command line you then can test the switchover by issuing the command:

                  dgmgrl
                  connect sys/<password>
                  DGMGRL> SWITCHOVER TO ‘<SID>_<REMOTESITE>’;

                  Oracle DBA
                  Once the Switchover command above command is completed, add the temporary tablespace to the standby database. The command for this will be in a backup controlfile to trace on the primary server. (udump area)

                  Oracle DBA
                  The audit_trail parameter needs to be amended. On the new Primary DB, this needs to be set to “audit_trail=db” and on the Standby, to “audit_trail=none”.

                  The primary & Standby databases will need to be shutdown & re-started (startup nomount for the Standby) for the role transition to be completed.

                  Oracle DBA
                  The database can then be switched back:

                  dgmgrl
                  connect sys/<password>
                  DGMGRL> SWITCHOVER TO ‘<SID>_<PRIMARYSITE>’;

                  Oracle DBA
                  Reset the audit_trail parameter on the Primary & Standby databases
                  The primary & Standby databases will need to be shutdown & re-started (startup nomount for the Standby) for the role transition to be completed

                  No comments:

                  Post a Comment

                  Subscribe to: Post Comments (Atom)