How To Build A Sandpit Data Guard

Welcome To ABCdba.com » Data Guard » How To Build A Sandpit Data Guard

Fold

Table of Contents

BACKGROUND & OVERVIEW

PRE-REQUISITES & ASSUMPTIONS

BACKGROUND & OVERVIEW

The following documentation provides instructions for building a sandpit Oracle Data Guard environment using the same server.

PRE-REQUISITES & ASSUMPTIONS

Enough disk space for two copies of the database.
Create an extra file system /u02/oradata/<SID>/standby/d1 to house the standby redo logs.
Installation of Enterprise Edition Oracle binaries.
The file locations are examples, amend the scripts as appropriate.

STEP-BY-STEP GUIDE

Log on to the sandpit server
Run create_dir_10g.ksh for the primary database.
Using createdb.sql create the primary database with an spfile.
Configure the primary dataguard components by running add_dg_primary.sql.
Shutdown the primary database.
Clone the primary database using an appropriate method.
- - For example: cd /u02/oradata; cp -R <primary> <standby>
On the primary database, create the standby components by running prep_standby.sql.
Copy the standby control files to the appropriate standby locations.
Amend the standby init.ora as follows:
- - DB_UNIQUE_NAME=<standby>
  - CONTROL_FILES='<standby_control_file_location>/control1.ctl', etc
  - DB_FILE_NAME_CONVERT='<primary>','<standby>'
  - LOG_FILE_NAME_CONVERT='<primary>','<standby>'
  - LOG_ARCHIVE_DEST_1 'LOCATION=<standby_arch_location>'
  - VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  - DB_UNIQUE_NAME=<standby>
  - LOG_ARCHIVE_DEST_2 'SERVICE=<primary> LGWR ASYNC
  - VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  - DB_UNIQUE_NAME=<primary>'
  - FAL_SERVER=<primary>
  - FAL_CLIENT=<standby>
Ensure tnsnames.ora entries exist for both the primary and standby databases.
Create a password file for the standby database - this MUST match the SYS password of the primary database.
Create an spfile for the standby database to use.
Mount the standby database.
Start the datagaurd process by issuing the following command:
- - ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Generate transactions and log files on the primary database.
Use checkapplied.sql and checklog.sql on the primary database to confirm dataguard is working.
Enjoy learning dataguard!

FILE LISTINGS

`create_dir_10g.ksh`

      #!/bin/ksh
      echo
      echo
      echo "This script will create all directories related to the requested SID."
      echo
      echo
      echo "Enter SID name for the directories to be created: \c"
      read sidname
      echo
      echo

      mkdir -p /u02/oradata/$sidname/redo/d1
      mkdir -p /u02/oradata/$sidname/standby/d1

      mkdir -p /u03/oradata/$sidname/indx/d1
      mkdir -p /u03/oradata/$sidname/sysaux/d1
      mkdir -p /u03/oradata/$sidname/system/d1
      mkdir -p /u03/oradata/$sidname/tools/d1
      mkdir -p /u03/oradata/$sidname/users/d1

      mkdir -p /u04/oradata/$sidname/undo/d1

      mkdir -p /u05/oradata/$sidname/temp/d1

      mkdir -p /u06/oradata/$sidname/arch/d1

      mkdir -p /u07/oradata/$sidname/sol_tables/d1

      mkdir -p /u08/oradata/$sidname/sol_indexes/d1

      mkdir -p /u09/oradata/$sidname/export/d1

      mkdir -p /u01/app/oracle/admin/local/bin

      mkdir -p /u01/app/oracle/DBA

      mkdir -p /u01/app/oracle/DBA/CRONTAB
      mkdir -p /u01/app/oracle/DBA/DBCHECKS
      mkdir -p /u01/app/oracle/DBA/FILES
      mkdir -p /u01/app/oracle/DBA/HOUSEKEEPING
      mkdir -p /u01/app/oracle/DBA/SCRIPTS
      mkdir -p /u01/app/oracle/DBA/SQL

      mkdir -p /u01/app/oracle/product/11.1.0

      mkdir -p /u01/app/oracle/admin/$sidname/adump
      mkdir -p /u01/app/oracle/admin/$sidname/bdump
      mkdir -p /u01/app/oracle/admin/$sidname/cdump
      mkdir -p /u01/app/oracle/admin/$sidname/create
      mkdir -p /u01/app/oracle/admin/$sidname/parfile
      mkdir -p /u01/app/oracle/admin/$sidname/pfile
      mkdir -p /u01/app/oracle/admin/$sidname/scripts
      mkdir -p /u01/app/oracle/admin/$sidname/udump

      ln -s /u09/oradata/$sidname/export/d1 /u01/app/oracle/admin/$sidname/exp

      ln -s /u01/app/oracle/DBA/SQL $HOME/DBA

`add_dg_primary.sql`

      ACCEPT sid   PROMPT 'Enter SID name:    '
      ACCEPT dgsid PROMPT 'Enter DG SID name: '
 
      SET TIMING ON
      SET ECHO ON
      SPOOL add_dg_primary.log
 
      ALTER DATABASE FORCE LOGGING
      /
 
      ACCEPT cont PROMPT 'Set up ORAPWD.'
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
      ('/u02/oradata/&sid/standby/d1/redo005a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
      ('/u02/oradata/&sid/standby/d1/redo006a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
      ('/u02/oradata/&sid/standby/d1/redo007a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 8
      ('/u02/oradata/&sid/standby/d1/redo008a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 9
      ('/u02/oradata/&sid/standby/d1/redo009a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
      ('/u02/oradata/&sid/standby/d1/redo010a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 11
      ('/u02/oradata/&sid/standby/d1/redo011a.dbf') SIZE 50m
      /
 
      ALTER DATABASE ADD STANDBY LOGFILE GROUP 12
      ('/u02/oradata/&sid/standby/d1/redo012a.dbf') SIZE 50m
      /
 
      SELECT
         group#, thread#, sequence#, archived, status
      FROM
         v$standby_log
      /
 
      ALTER SYSTEM SET db_unique_name=&sid SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_config='DG_CONFIG=(&sid, &dgsid)' SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_dest='' SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_dest_1=
      'LOCATION=/u06/oradata/APDB1/arch/d1
       VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
       DB_UNIQUE_NAME=&sid'
      SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_dest_2=
      'SERVICE=&dgsid LGWR ASYNC
       VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
       DB_UNIQUE_NAME=&dgsid'
      SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_dest_state_1=ENABLE SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_archive_max_processes=10 SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET fal_server=&dgsid SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET fal_client=&sid SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET db_file_name_convert='&dgsid','&sid' SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET log_file_name_convert='&dgsid','&sid' SCOPE=SPFILE
      /
 
      ALTER SYSTEM SET standby_file_management=AUTO SCOPE=SPFILE
      /
 
      COL log_mode HEA 'ARCHIVE LOG MODE' FOR a16
 
      SELECT
         log_mode
      FROM
         v$database
      /
 
      SPOOL OFF
      SET TIMING OFF
      SET ECHO OFF
 
      SHUTDOWN IMMEDIATE
 
      STARTUP

`prep_standby.sql`

      ACCEPT dgsid PROMPT 'Enter DG SID name: '
 
      SHUTDOWN IMMEDIATE
 
      ACCEPT cont  PROMPT 'Backup Primary DB....'
 
      STARTUP MOUNT
 
      ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/&dgsid..ctl'
      /
 
      ALTER DATABASE OPEN
      /
 
      CREATE pfile='/tmp/init&dgsid..ora' FROM SPFILE
      /

`checkapplied.sql`

      SELECT
         sequence#, applied
      FROM
         v$archived_log
      ORDER BY
         sequence#
      /

`checklog.sql`

      SELECT
         sequence#, first_time, next_time
      FROM
         v$archived_log
      ORDER BY
         sequence#
      /

Page tags: backup dataguard recovery resilience

page revision: 30, last edited: 09 Jun 2011 12:52

Edit Tags History Files Print Site tools + Options

ABCdba.com

The DBA's quick reference website

BACKGROUND & OVERVIEW

PRE-REQUISITES & ASSUMPTIONS

STEP-BY-STEP GUIDE

FILE LISTINGS

create_dir_10g.ksh

add_dg_primary.sql

prep_standby.sql

checkapplied.sql

checklog.sql

`create_dir_10g.ksh`

`add_dg_primary.sql`

`prep_standby.sql`

`checkapplied.sql`

`checklog.sql`