• No results found

Standby

N/A
N/A
Protected

Academic year: 2021

Share "Standby"

Copied!
19
0
0

Loading.... (view fulltext now)

Full text

(1)

1. Overview

The following document describes the setup of an Oracle9i Data Guard (e.g. Standby) system. The database

runs on Oracle9i version 9.2.0.5 on a Sun Solaris platform. The task is to create an Oracle9i Data Guard

environment using a single physical standby database on a separate server. The Data Guard standby

database has been set up in "managed recovery" mode to automate the process of keeping the standby in

sync with the primary database. OEM is been installed on a separate server and has been configured to

monitor the DataGuard installation.

2. System Requirements

2.1 Kernel Parameters

Kernel parameter values for shared memory segments and semaphore settings are shown below. See the

Oracle9i Installation Guide for specific parameter guidelines. The following should be added to /etc/system:

# Settings for oracle

set shmsys:shminfo_shmmax=1610612736 set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=100 set shmsys:shminfo_shmseg=10 set semsys:seminfo_semmni=100

set semsys:seminfo_semmsl=160 <= Minimum recommendation is 256 set semsys:seminfo_semmns=470 <= Minimum recommendation is 1024 set semsys:seminfo_semopm=100

set semsys:seminfo_semvmx=32767 #

2.2 Disk Space

2.2.1 Oracle Software

Minimum of 3 GB for this installation.

2.2.2 Swap Space

Disk space equal to the system’s physical memory, or 1GB, whichever is greater.

2.2.3 Temporary Space

Up to 400 MB of space in the /tmp directory.

3. Standby Creation

3.1 Software Installation

3.1.1 Directory Setup

A new directory structure for the standby database's "admin" directories was created and the datafile

directories were located on the same NFS mounted file systems as the source database. The following is the

basic directory structure:

ORACLE_BASE = /u01/app/oracle ORACLE_HOME = /u01/app/oracle/product/9.2.0

Admin directories:

/u01/app/oracle/product/9.2.0/adump /bdump /cdump /pfile /udump

(2)

3.1.1.1 Primary Database Backup

A new level 0 backup was taken of the primary database with a standby backup controlfile.

3.1.1.2 Standby Database

Datafile directories:

/u03/oradata/stby

Controlfile directories:

/u03/oradata/stby

Redo log directories:

/u03/oradata/stby

Standby archive destination:

/u02/oradata/stby

3.2 Primary Database Preparation

3.2.1 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 prevents the use of the "nologging" feature, which would not be replicated in the redo logs, and

therefore applied on the standby database.

3.2.2 Create A Password File

This is required to enable RMAN to create the standby database. After creating the password file with the

orapwd utility, the following init.ora parameter must be set:

remote_login_passwordfile='EXCLUSIVE'

3.2.3 Create A Standby Controlfile

A backup controlfile from the primary database is required with the standby option specified:

SQL> alter database create standby controlfile as '<file>';

3.2.3.1 Backup With Standby Controlfile

The following option was added to the RMAN "backup database" command to insure that a controlfile was

available in standby format for RMAN to create the standby database. This is important to also allow for

future re-instantiation of the standby database through RMAN.

CONFIGURE CONTROLFILE AUTOBACKUP ON;

Appended to the backup command:

(3)

3.2.4 Additional init.ora Parameters

log_archive_dest_2='service=stby ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register'

or

log_archive_dest_2='service=stby LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register'

log_archive_dest_state_2='ENABLE' log_checkpoint_interval=10000 log_checkpoint_timeout=1800

3.2.5 Create An SPFILE For OEM Support

Oracle's Enterprise Manager requires spfile support for remote administration of DataGuard installations.

This can be created with the following command:

SQL> create spfile from pfile;

This will create an spfile in the default $ORACLE_HOME/dbs directory and will take affect on the next

database startup.

3.3 Standby Database Creation

To create the standby database the following basic steps were performed:

• Configure Oracle Server software

• Create standby database environment

• Configure Network Files

• Configure the Primary Database init.ora Parameters

• Configure the Standby Database init.ora Parameters

• Start the Standby Instance

• Create the Standby database with RMAN

• Place the Standby Database in recovery mode

The following sections will detail each of these steps.

3.3.1 Configure Oracle Server Software

The primary database is running Oracle 9i version 9.2.0.5. The ORACLE_HOME for the standby database

server was installed with version 9.2.0.5 as well.

3.3.2 Create Standby Database Environment

3.3.2.1 Standby Database Password File

A new password file was created for the standby database instance using the orapwd utility.

orapwd file=orapwDBSID password=xxxxxx entries=20

3.3.2.2 Configure the Standby Database init.ora Parameters

The source database init.ora parameters were copied and the following added or changed:

aq_tm_processes=0

archive_lag_target=0 db_name = DBSID

(4)

instance_name = DBSID # control_files = ('/u01/oradata/DBSID/control01.ctl', '/u02/oradata/DBSID/control02.ctl', '/u03/oradata/DBSID/control03.ctl') # background_dump_dest = '/u01/app/oracle/admin/DBSID/bdump' core_dump_dest = '/u01/app/oracle/admin/DBSID/cdump' user_dump_dest = '/u01/app/oracle/admin/DBSID/udump' # compatible = '9.2.0.0.0' # job_queue_processes=0 #

# Standby specific parameters # db_file_name_convert = ('/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby') # log_file_name_convert = ('/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby') # standby_archive_dest = '/u01/app/oracle/admin/stby_arch/' standby_file_management=auto remote_archive_enable=true #

# Set to allow log apply services to automatically resolve archive gaps # fal_client='(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=primary)(PORT=1521)))(CONNECT_DATA=(SID=DBSID)(ORACLE_HOME=/u 01/app/oracle/product/9.2.0)(SERVER=DEDICATED)))' fal_server='(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=stby)(PORT=1521)))(CONNECT_DATA=(SID=DBSID)(ORACLE_HOME=/u01/ app/oracle/product/9.2.0)(SERVER=DEDICATED)))' #

# Set logging Parameters #

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/admin/DBSID/arch/'

log_archive_dest_2='SERVICE=DBSID_stby ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register' log_archive_dest_state_1='ENABLE' log_archive_dest_state_2='DEFER' log_archive_format = 'DBSID_%t_%s_%r.arc' log_archive_max_processes=2 log_archive_min_succeed_dest=1 log_archive_start=true log_archive_trace=0

Note: The standby_archive_dest parameter should match the log_archive_dest_n parameter.

3.3.3 Configure Network Files

On the primary database a network entry must be available that specifies how to SQL*Net to the standby

database. This is used to transfer archivelog files to the standby database. On the standby database itself

there should be an entry to register the database with the listener and optionally a network entry to specify

SQL*Net access to the standby database.

3.3.3.1 Primary Database

3.3.3.1.1 tnsnames.ora File

The tnsnames.ora file and/or Oracle Names/OID should be updated with information to allow access to the

standby database. The following was added to the tnsnames.ora file for the primary database:

DBSID_stby = (DESCRIPTION = (ADDRESS_LIST =

(5)

(ADDRESS = (PROTOCOL = TCP)(HOST = stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBSID) ) )

3.3.3.2 Standby Database

3.3.3.2.1 sqlnet.ora File

On the standby database the following entries should exist:

# SQLNET.ORA Network Configuration File:

/u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora # Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

3.3.3.3 listener.ora File

On the standby database the following entry should be added to the listener.ora file:

# LISTENER.ORA Network Configuration File:

#/u01/app/oracle/product/9.2.0/network/admin/listener.ora # Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = stby)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (SID_NAME = DBSID) ) )

3.3.3.3.1 tnsnames.ora File

The following example shows the standby database entry in the standby server's tnsnames.ora file:

DBSID_stby =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBSID) ) ) DBSID = (DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = DBSID) )

(6)

3.3.4 Create spfile

It is strongly recommended that the standby be run with an spfile rather than a standard init.ora. Both OEM

and DataGuard require an SPFILE in order to remotely manage the standby database.

An spfile was created from the init.ora which was used to initially start the standby database. This was

required for OEM support of it's DataGuard option. The following will create an spfile from an init.ora file

and put it in the default location (i.e. $ORACLE_HOME/dbs):

SQL> create spfile from pfile;

3.3.5 Create the Standby Database with RMAN

The standby database is created using RMAN and the latest online backup and archivelog files from the

primary database. The standby creation requires the use of a "standby controlfile" so the primary database

backup is made with the additional clause "include current controlfile for standby".

In order to create the standby database with RMAN the standby instance must be started in nomount mode.

RMAN will then create a "duplicate" database for "standby". RMAN expects to find the backups in the

same place that they were created in so the backup destination directory structure is duplicated and the

backup files are copied from the primary system to this destination on the standby database.

The following shows the commands used to create the standby database:

/u01/app/oracle/local/bkup/restore$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 6 11:39:45 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba Connected to an idle instance. SQL> startup nomount;

ORACLE instance started.

Total System Global Area 403412268 bytes Fixed Size 451884 bytes Variable Size 335544320 bytes Database Buffers 67108864 bytes Redo Buffers 307200 bytes SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production /u01/app/oracle/local/bkup/restore$

export NLS_LANG="AMERICAN_AMERICA.UTF8" export NLS_DATE_FORMAT="DD-MON-YY HH24:MI"

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: DBSID (DBID=1260836409) connected to recovery catalog database

RMAN>

connected to auxiliary database: DBSID (not mounted)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

sql statement: ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI"

Starting Duplicate Db at 06-APR-06 13:52 allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=12 devtype=DISK allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=13 devtype=DISK

(7)

printing stored script: Memory Script {

restore clone standby controlfile to clone_cf; replicate clone controlfile from clone_cf;

sql clone 'alter database mount standby database'; }

executing script: Memory Script

Starting restore at 06-APR-06 13:52

using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring controlfile

output filename=/u01/oradata/DBSID/control01.ctl channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_1_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restore complete Finished restore at 06-APR-06 13:52 replicating controlfile input filename=/u01/oradata/DBSID/control01.ctl output filename=/u01/oradata/DBSID/control02.ctl output filename=/u01/oradata/DBSID/control03.ctl

sql statement: alter database mount standby database

printing stored script: Memory Script {

set until scn 7240492351118; set newname for datafile 1 to "/u01/oradata/DBSID/system01.dbf"; set newname for datafile 2 to "/u01/oradata/DBSID/undotbs01.dbf"; set newname for datafile 3 to "/u01/oradata/DBSID/cwmlite01.dbf"; set newname for datafile 4 to "/u01/oradata/DBSID/drsys01.dbf"; set newname for datafile 5 to "/u01/oradata/DBSID/nvdmrpt_tab01.dbf"; set newname for datafile 6 to "/u01/oradata/DBSID/nvdmrpt_tab02.dbf"; set newname for datafile 7 to "/u01/oradata/DBSID/odm01.dbf"; set newname for datafile 8 to "/u01/oradata/DBSID/tools01.dbf"; set newname for datafile 9 to "/u01/oradata/DBSID/users01.dbf"; set newname for datafile 10 to "/u01/oradata/DBSID/xdb01.dbf"; set newname for datafile 11 to "/u01/oradata/DBSID/srcdba_tab01.dbf"; set newname for datafile 12 to "/u01/oradata/DBSID/rhn_tab01.dbf"; set newname for datafile 13 to "/u01/oradata/DBSID/asdqa2_tab01.dbf"; set newname for datafile 14 to "/u01/oradata/DBSID/asdqa2_tab02.dbf"; set newname for datafile 15 to "/u01/oradata/DBSID/rhn_tab02.dbf"; set newname for datafile 16 to "/u01/oradata/DBSID/radia4_tab01.dbf"; set newname for datafile 17 to "/u01/oradata/DBSID/radia4_tab02.dbf"; set newname for datafile 18 to "/u01/oradata/DBSID/RADIA4DEV_TAB1.dbf"; set newname for datafile 19 to "/u01/oradata/DBSID/RADIA4DEV_TAB2.dbf"; set newname for datafile 20 to "/u01/oradata/DBSID/bigfix_tab01.dbf"; set newname for datafile 21 to "/u01/oradata/DBSID/ASDDEV_TAB1.dbf"; set newname for datafile 22 to "/u01/oradata/DBSID/ASDDEV_TAB2.dbf"; set newname for datafile 23 to

(8)

"/u01/oradata/DBSID/RADIA4QA_TAB1.dbf"; set newname for datafile 24 to "/u01/oradata/DBSID/RADIA4QA_TAB2.dbf"; restore check readonly clone database ; }

executing script: Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 06-APR-06 13:52

using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00005 to /u01/oradata/DBSID/nvdmrpt_tab01.dbf

restoring datafile 00007 to /u01/oradata/DBSID/odm01.dbf restoring datafile 00008 to /u01/oradata/DBSID/tools01.dbf restoring datafile 00011 to /u01/oradata/DBSID/srcdba_tab01.dbf restoring datafile 00012 to /u01/oradata/DBSID/rhn_tab01.dbf restoring datafile 00013 to /u01/oradata/DBSID/asdqa2_tab01.dbf restoring datafile 00016 to /u01/oradata/DBSID/radia4_tab01.dbf restoring datafile 00017 to /u01/oradata/DBSID/radia4_tab02.dbf restoring datafile 00018 to /u01/oradata/DBSID/RADIA4DEV_TAB1.dbf restoring datafile 00021 to /u01/oradata/DBSID/ASDDEV_TAB1.dbf

(9)

restoring datafile 00022 to /u01/oradata/DBSID/ASDDEV_TAB2.dbf restoring datafile 00024 to /u01/oradata/DBSID/RADIA4QA_TAB2.dbf channel ORA_AUX_DISK_2: starting datafile backupset restore

channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oradata/DBSID/system01.dbf

restoring datafile 00002 to /u01/oradata/DBSID/undotbs01.dbf restoring datafile 00003 to /u01/oradata/DBSID/cwmlite01.dbf restoring datafile 00004 to /u01/oradata/DBSID/drsys01.dbf restoring datafile 00006 to /u01/oradata/DBSID/nvdmrpt_tab02.dbf restoring datafile 00009 to /u01/oradata/DBSID/users01.dbf restoring datafile 00010 to /u01/oradata/DBSID/xdb01.dbf

restoring datafile 00014 to /u01/oradata/DBSID/asdqa2_tab02.dbf restoring datafile 00015 to /u01/oradata/DBSID/rhn_tab02.dbf restoring datafile 00019 to /u01/oradata/DBSID/RADIA4DEV_TAB2.dbf restoring datafile 00020 to /u01/oradata/DBSID/bigfix_tab01.dbf restoring datafile 00023 to /u01/oradata/DBSID/RADIA4QA_TAB1.dbf channel ORA_AUX_DISK_2: restored backup piece 1

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_1_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_1_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_2: restored backup piece 2

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_2_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restored backup piece 2

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_2_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_2: restored backup piece 3

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_3_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restored backup piece 3

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_3_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_2: restored backup piece 4

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_4_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restored backup piece 4

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_4_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restored backup piece 5

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_5_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restore complete

channel ORA_AUX_DISK_2: restored backup piece 5

piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_5_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_2: restore complete Finished restore at 06-APR-06 14:16

printing stored script: Memory Script {

switch clone datafile all; }

executing script: Memory Script

datafile 2 switched to datafile copy

input datafilecopy recid=79 stamp=587053019 filename=/u01/oradata/DBSID/undotbs01.dbf datafile 3 switched to datafile copy

input datafilecopy recid=80 stamp=587053020 filename=/u01/oradata/DBSID/cwmlite01.dbf datafile 4 switched to datafile copy

input datafilecopy recid=81 stamp=587053020 filename=/u01/oradata/DBSID/drsys01.dbf datafile 5 switched to datafile copy

input datafilecopy recid=82 stamp=587053020 filename=/u01/oradata/DBSID/nvdmrpt_tab01.dbf datafile 6 switched to datafile copy

input datafilecopy recid=83 stamp=587053020 filename=/u01/oradata/DBSID/nvdmrpt_tab02.dbf datafile 7 switched to datafile copy

input datafilecopy recid=84 stamp=587053020 filename=/u01/oradata/DBSID/odm01.dbf datafile 8 switched to datafile copy

input datafilecopy recid=85 stamp=587053020 filename=/u01/oradata/DBSID/tools01.dbf datafile 9 switched to datafile copy

input datafilecopy recid=86 stamp=587053020 filename=/u01/oradata/DBSID/users01.dbf datafile 10 switched to datafile copy

input datafilecopy recid=87 stamp=587053020 filename=/u01/oradata/DBSID/xdb01.dbf datafile 11 switched to datafile copy

(10)

datafile 12 switched to datafile copy

input datafilecopy recid=89 stamp=587053020 filename=/u01/oradata/DBSID/rhn_tab01.dbf datafile 13 switched to datafile copy

input datafilecopy recid=90 stamp=587053020 filename=/u01/oradata/DBSID/asdqa2_tab01.dbf datafile 14 switched to datafile copy

input datafilecopy recid=91 stamp=587053020 filename=/u01/oradata/DBSID/asdqa2_tab02.dbf datafile 15 switched to datafile copy

input datafilecopy recid=92 stamp=587053020 filename=/u01/oradata/DBSID/rhn_tab02.dbf datafile 16 switched to datafile copy

input datafilecopy recid=93 stamp=587053020 filename=/u01/oradata/DBSID/radia4_tab01.dbf datafile 17 switched to datafile copy

input datafilecopy recid=94 stamp=587053020 filename=/u01/oradata/DBSID/radia4_tab02.dbf datafile 18 switched to datafile copy

input datafilecopy recid=95 stamp=587053020 filename=/u01/oradata/DBSID/RADIA4DEV_TAB1.dbf datafile 19 switched to datafile copy

input datafilecopy recid=96 stamp=587053020 filename=/u01/oradata/DBSID/RADIA4DEV_TAB2.dbf datafile 20 switched to datafile copy

input datafilecopy recid=97 stamp=587053020 filename=/u01/oradata/DBSID/bigfix_tab01.dbf datafile 21 switched to datafile copy

input datafilecopy recid=98 stamp=587053021 filename=/u01/oradata/DBSID/ASDDEV_TAB1.dbf datafile 22 switched to datafile copy

input datafilecopy recid=99 stamp=587053021 filename=/u01/oradata/DBSID/ASDDEV_TAB2.dbf datafile 23 switched to datafile copy

input datafilecopy recid=100 stamp=587053021 filename=/u01/oradata/DBSID/RADIA4QA_TAB1.dbf datafile 24 switched to datafile copy

input datafilecopy recid=101 stamp=587053021 filename=/u01/oradata/DBSID/RADIA4QA_TAB2.dbf

printing stored script: Memory Script { set until scn 7240492351118; recover standby clone database delete archivelog ; }

executing script: Memory Script

executing command: SET until clause

Starting recover at 06-APR-06 14:17 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=2216

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/bkupdir/DBSID/DBSID.arch.1rhfr941_1_1 tag=INCR0_DISK_DBSID params=NULL

channel ORA_AUX_DISK_1: restore complete

archive log filename=/u01/app/oracle/DBSID_arch/arch_1_2216.arc thread=1 sequence=2216 channel clone_default: deleting archive log(s)

archive log filename=/u01/app/oracle/DBSID_arch/arch_1_2216.arc recid=1 stamp=587053025 media recovery complete

Finished recover at 06-APR-06 14:17 Finished Duplicate Db at 06-APR-06 14:17

RMAN>

Recovery Manager complete.

3.3.5.1 RMAN Standby Database Creation Script

#

$ORACLE_HOME/bin/rman nocatalog target sys/xxxxxx@DBSID log=dupdb.log <<-EOF connect auxiliary /

# run { #

(11)

#

sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI"'; #set until time = '08-SEP-03 01:00';

#

duplicate target database for standby nofilenamecheck

dorecover; }

exit; EOF

3.4 Standby Redo Log Files

Standby redo log files are required for higher protection levels where LGWR is used to write redo records.

There is no downside to creating them for both the primary and standby and this allows a switchover back

to the primary. They must be created as the same size and number of groups and members as the primary

online redo log database.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

--- --- --- standby_file_management string auto

SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

--- --- --- standby_file_management string MANUAL

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl02a.rdo' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl03a.rdo' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl04a.rdo' size 50m;

Database altered.

SQL>

Note: If standby log files are already defined (e.g. from the primary database), then they must be dropped

and recreated.

SQL> alter database drop standby logfile '/u01/oradata/DBSID/srl01a.rdo';

Database altered.

SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 500m;

Database altered.

SQL>

It is recommended to always create one more standby redo log file than there is on the primary database.

SQL> alter system set standby_file_management=auto;

(12)

System altered.

SQL> show parameter standby_file_management

NAME TYPE VALUE

--- --- --- standby_file_management string AUTO

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

SQL>

To review the newly create standby log files:

SQL> select * from v$standby_log;

GROUP# THREAD# SEQUENCE# BYTES USED ARC STATUS --- --- --- --- --- --- --- FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME

--- --- --- --- 4 0 0 52428800 1024 YES UNASSIGNED 0 0 5 0 0 52428800 1024 YES UNASSIGNED 0 0 6 0 0 52428800 1024 YES UNASSIGNED 0 0 7 0 0 52428800 1024 YES UNASSIGNED 0 0 SQL>

To enable LGWR sending of redo records on the primary database change the definition of

log_archive_dest_3:

SQL> l

1* select * from v$archive_dest where dest_id < 4; DEST_ID

--- DEST_NAME

--- STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE

--- --- --- --- --- --- DESTINATION

--- LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS REG FAIL_DATE

--- --- --- --- --- --- --- FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE

--- --- --- --- ERROR --- ALTERNATE --- DEPENDENCY --- REMOTE_TEMPLATE --- QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE

--- --- --- --- --- --- --- 1

LOG_ARCHIVE_DEST_1

VALID OPTIONAL SYSTEM PRIMARY ARCH ACTIVE /u01/app/oracle/admin/DBSID/arch

(13)

4071 300 0 0 ARCH YES 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC 2 LOG_ARCHIVE_DEST_2

VALID OPTIONAL SYSTEM STANDBY ARCH ACTIVE STBY_DBSID 4071 300 0 0 ARCH YES 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC 3 LOG_ARCHIVE_DEST_3

INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC

SQL> alter system set log_archive_dest_3='service=DBSID_stby lgwr async noaffirm delay=0 optional max_failure=0 reopen=300 register' scope=both;

System altered.

SQL> alter system set log_archive_dest_state_3='ENABLE' scope=both;

System altered.

SQL> select * from v$archive_dest 2 where dest_id = 3; DEST_ID --- DEST_NAME --- STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE

--- --- --- --- --- --- DESTINATION

--- LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS REG FAIL_DATE

--- --- --- --- --- --- --- FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE

--- --- --- --- ERROR --- ALTERNATE --- DEPENDENCY --- REMOTE_TEMPLATE --- QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE

--- --- --- --- --- --- --- 3

LOG_ARCHIVE_DEST_3

VALID OPTIONAL SYSTEM STANDBY LGWR PENDING DBSID_stby 0 300 0 0 LGWR YES 0 0 0 0 NONE NONE NONE

(14)

0 0 0 ASYNCHRONOUS 2048 NO PUBLIC

SQL>

alter system set log_archive_dest_3='service= DBSID_stby LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL max_failure=0 reopen=300 register' scope=both;

alter system set log_archive_dest_state_3='ENABLE' scope=both;

Note that now two definitions have been created. The "log_archive_dest_2" definition defines an archiver

path to transfer archived redo log files and the "log_archive_dest_3" definition defines a log writer path.

Once the log writer definition is enabled it will become effective at the next log switch. If there is a failure

Oracle will fall back to the archiver definition if possible, and Oracle will attempt to use the log writer

definition at each log switch.

Oracle Documentation:

The minimum configuration should have one more standby redo log group than the

primary database.

It might be necessary to create additional standby log groups on the physical standby

database, so that the archival operation has time to complete before the standby redo log

is reused by the RFS process. If the primary database is operating in maximum

protection mode and a standby redo log cannot be allocated, the primary database

instance might shut down immediately. If the primary database is operating in maximum

protection mode or maximum availability mode, then the primary database might wait

for the standby redo log to become available. Therefore, be sure to allocate an adequate

number of standby redo logs.

Caution:

Whenever you add an online redo log to the primary database, you

must add a corresponding standby redo log to the standby

database. If you do not add a standby redo log to the standby

database, the primary database might shut down.

4. Standby Management

4.1 Start the Standby Instance

The standby instance must be started and placed in standby mount mode. The database should never be

"opened" because this will invalidate the "standby" status. In addition, the database must be mounted in

order to access the controlfile to allow file name changes and subsequent recovery. The following

commands are used:

1.

Use SQL*Plus to connect to the standby database instance. For example, enter:

SQL> CONNECT / AS SYSDBA

2.

Start the Oracle instance at the standby database without mounting the

database. For example, enter:

(15)

3.

Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

4.2 Modes and Recovery

Once the standby database has been recovered up to the last archived redo log file, it must be kept current

by the application of log files once they have been archived from the primary database. This can be done in

"manual recovery" mode or in "managed recovery" mode. The standby database can also be opened in

"read only" mode which allows query access to the standby database. No recovery can take place while the

database is open in read only mode though, and so time must be arranged for periodic recoveries to take

place in order to keep the standby in sync with the primary database.

For Oracle9i and above the standby database can be "caught up" and run in managed recovery mode with

the following commands:

SQL> alter database recover managed standby database disconnect;

It is still possible to perform manual recovery (this was required in 8i standby database) with the command:

SQL> recover standby database;

Note: dbcontrol supports the startup of a DataGuard or standby database in managed recovery mode.

4.3 Startup and Shutdown

In order to maintain managed recovery of the standby database, the following guidelines must be followed:

"To avoid creating gap sequences, follow these rules:



Start the standby databases and listeners before starting the primary database.



Shut down the primary database before shutting down the standby database.

If you violate either of these two rules, then the standby database is down while the

primary database is open and archiving. Consequently, Oracle can create a gap

sequence. When you restart the standby database later, you must synchronize the

standby database manually with the primary database before you can initiate

managed recovery."

1

Once Data Guard is implemented this is typically not a problem since Data Guard will take care of

re-synchronization.

4.4 Events That Affect the Standby Database

The following events can cause standby database issues:

• Add datafile of create tablespace

• Drop or delete a tablespace or datafile

• Rename a datafile

• Alter the primary database control file (using the SQL ALTER DATABASE CREATE

CONTROLFILE statement)

• Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause

• Change initialization parameter

See the Oracle Data Guard Concepts and Administration, Release 2 (9.2) manual for more details.

1

(16)

4.5 Managed Recovery Mode

Managed recovery mode allows for the automatic application of archived redo log files to the standby

database. Managed recovery mode should be initiated with a disconnected session. The following syntax

will accomplish this, and is used by dbcontrol:

SQL> alter database recover managed standby database disconnect;

4.6 Troubleshooting

4.6.1 Managed Recovery Errors

In order to initiate managed recovery all archived redo log files must be applied to the standby database.

Managed recovery will not retrospectively apply archived redo log files.

4.6.2 Archiver Errors

If archived redo log files are not being transferred to the standby database then check the "state" of the

archive destination in the v$archive_dest view on the primary database. If this state is in error then the

standby listener service may have failed. Once that is corrected, you must force the listener to begin

re-transmission with the command:

SQL> alter system set log_archive_dest_2='service=stby_DBSID ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register';

If DataGuard is enabled then this should be issued automatically.

If the primary archive destination has stopped, possibly due to a full disk, fix the problem and issue:

archive log start

4.6.3 Standby Recovery Commands

The following shows a case of "manually" recovering the standby database outside of "managed recovery

mode". This might be used when performing manual time lag recovery of a standby database. This is the

scenario used by the "stby_maint" script when implementing the "restore" option as part of a 10g AS high

availability environment using DataGuard.

SQL> connect / as sysdba Connected.

SQL> alter database recover automatic standby database until cancel; alter database recover automatic standby database until cancel *

ERROR at line 1:

ORA-00279: change 158556003 generated at 01/03/2006 11:42:22 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/admin/DBSID/arch/DBSID_1_10886.arc ORA-00280: change 158556003 for thread 1 is in sequence #10886

ORA-00278: log file '/u01/app/oracle/admin/ DBSID/arch/DBSID_1_10886.arc' no longer needed for this recovery

ORA-16145: archival for thread# 1 sequence# 10886 in progress

SQL> alter database recover cancel;

Database altered.

SQL>

5. DataGuard Setup

DataGuard can be set up manually (e.g. the DataGuard Broker) or it can be set up through Enterprise

Manager (i.e. OEM). The following will briefly detail the OEM setup:

(17)

1. The 9.2 OEM console can be started from the OEM server. It is the Oracle Management Server

(OMS) and has the OEM software configured. The command is:

oemapp console

2. Once started, login to the management server using the SYSMAN userid.

3. Discover the standby node and verify the the standby database has been discovered as well.

4. Navigate to Tools -> Database Applications -> Data Guard Manager. A new window will pop up

for the Data Guard Manager.

5. Run the "Create Configuration Wizard" to create the Data Guard site.

6. System Management

6.1 oratab File

The oratab file should be updated so that the standby database entry has an "N" for the third field. This will

insure that the "dbstart" utility does not inadvertantly start and open the database and invalidate the standby

status. An example is:

DBSID:/u01/app/oracle/product/9.2.0:N

6.2 Utilities

In order to facilitate the management of standby databases the dbcontrol utility has been modified to be

standby database aware. The standby database's oratab entry must still be set with the third field set to "N"

in order to prevent the accidental opening of the database at boot time. A new comment is supported in the

oratab file to identify the standby database to the dbcontrol utility. The format is

#STANDBY:<SID>:<OPTION>

where SID is the standby database SID and option is NOSTART, MOUNT, MANAGED, or READ.

With this comment identifying a corresponding standby database entry, dbcontrol will not open the standby

database and will only perform operations using the "standby" option. The following has been added to the

oratab file for database "stby":

#STANDBY:DBSID:MANAGED

6.2.1 orasetup

orasetup is a korn/bash shell script that replaces the Oracle supplied script "oraenv". This file should be

located in the ORACLE_BASE/local/script directory. This script will set an Oracle environment properly

for all versions of the database. It also recognizes the "Y" flag for databases and sets ORACLE_SID or the

"N" flag and set TWO_TASK. This is also done for standby databases to insure that the database is not

accidentally connected to without forcing ORACLE_SID to be set explicitly.

This utility can be run from any user with the following syntax:

. orasetup <SID>

This assumes a korn or bash shell and that $ORACLE_BASE/local/script is set in the PATH.

6.2.2 dbcontrol

dbcontrol is a korn/bash shell script that replaces dbstart and dbshut and should be located in the

ORACLE_BASE/local/script directory. A "log" file directory should also exist at

(18)

ORACLE_BASE/local/log as dbcontrol will create a log file if invoked by another process (e.g. if called by

dbora at startup or shutdown). dbcontrol has been enhanced to manage all database utilities as defined in the

oratab file and can be called during system startup or shutdown to start or stop all database services.

dbcontrol has a help menu and can be used with a single command line or interactively.

6.2.3 dbora

dbora is a custom bourne shell script that is called by the init process at startup and shutdown. The script

calls dbcontrol with “START ALL” or “STOP ALL” parameters depending on whether it’s being called

during a startup or shutdown of the machine. The file should be located in the /etc/init.d directory during

database installation.

6.2.4 stby_maint

stby_maint is a custom script to manage the deletion of applied archived redo log files at the standby site.

This file should be located in the ORACLE_BASE/local/script directory and an entry should be placed in

the crontab file to run once a day.

(19)

7. References

Oracle Data Guard Concepts and Administration, Release 2 (9.2), Part Number A96653-02

Oracle Note: 166689.1, Archived Logs Are Not Applied to Standby Database in Managed Recovery Mode,

Last Revision Date: 15-APR-2003

Oracle Note: 108264.1, Archive Logs Not Applied in Managed Recovery Mode, Last Revision Date:

02-OCT-2002

Oracle Note: 106699.1, ORA-16032 Attempting to Transfer Archive Logs to Standby Database, Last

Revision Date: 09-MAY-2002

Oracle Note: 123962.1, Archive logs not being applied automatically to local standby database, Last

Revision Date: 09-MAY-2002

Oracle Note: 68537.1, Init.ora Parameter "LOG_ARCHIVE_DEST_n" Reference Note, Last Revision Date:

29-JUN-2001

Oracle Note: 118409.1, Creating a Standby Database using RMAN (Recovery Manager), Last Revision

Date: 10-DEC-2002

Oracle Note: 183570.1, Creating a 9i Data Guard Database with RMAN (Recovery Manager), Last

Revision Date: 27-JAN-2003

References

Related documents

Our examination of the records shows that while the prosecution established through the testimony of SPO2 Sanchez that the sale of the prohibited drug by the accused-appellant

Transgenic perennial ryegrass plants expressing wheat fructosyltransferase genes accumulate increased amounts of fructan and acquire increased tolerance on a cellular level to

It will trace the meaning of the veil from the colonial period through unveiling and new veiling movements of 19 th and 20 th century across the Middle East, and finally to

This problem is solved by determining which data is used by more than one person and storing it on a network server – a central computer that provides a storage device and

• Compare the test print with the Printech offset printout or known good Printech digital master. Reject if any

To bal- ance this, it is true that most skills users will not be dealing with those who have profound psychological issues and, with good training, a counselling skills approach can

Study Study N Patient Follow- Treatment Adherence Mean Definition Adherent Predictors Definition Virologic design characteristics up regimen assessment adherence good/perfect

Partners from North East of Gothenburg: the Community Center Hammarkullen, the Folk High School in Angered, the Library and Citizens’ Advice Bureau operated by the City District of