Oracle DBA Tips and Techniques

By Arjun Raja

CREATION OF STANDBY DATABASE 11g Rel 2 ON SAME HOST.

Posted by Arjun Raja on August 3, 2010

Create a standby database on the same host, using RMAN with a backup from an active database .

PRIMARY DATABASE – ORCL ON TEST010 BOX

STANDBY DATABASE – STBY ON TEST010 BOX.

In PRIMARY – orcl database >

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/oracle/product/11.2

sql > Alter database force logging;

Create password file for the primary and standby database.

cd $ORACLE_HOME/dbs

orapwd file=orapworcl entries=10 password=temp force=y

cp orapworcl orapwstby – Do not create separate password file for standby – use cp .

Try a test connection –

$ sqlplus sys/temp@orcl as sysdba – should connect to primary.

ON PRIMARY – ORCL database.

alter system set LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL’;

alter system set LOG_ARCHIVE_DEST_2=’SERVICE=STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY’;

alter system set FAL_SERVER=STBY;

alter system set FAL_CLIENT=ORCL;

alter system set DB_UNIQUE_NAME=’ORCL’ scope=spfile;

alter system set log_archive_config=’dg_config=(orcl,stby)’;

STANDBY DATABASE ON SAME BOX.

Copy initorcl.ora and edit to reflect all stby entries.

vi initstby.ora

stby.__db_cache_size=155189248
stby.__java_pool_size=4194304
stby.__large_pool_size=4194304
stby.__oracle_base=’/u01/ofsad2’#ORACLE_BASE set from environment
stby.__pga_aggregate_target=159383552
stby.__sga_target=423624704
stby.__shared_io_pool_size=0
stby.__shared_pool_size=243269632
stby.__streams_pool_size=4194304
*.audit_file_dest=’/u01/ofsad2/admin/stby/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/oradata/stby/control01.ctl’,’/u02/oradata/stby/control02.ctl’
*.db_block_size=8192
*.db_file_name_convert=’/u02/oradata/orcl/’,’/u02/oradata/stby/’
*.db_name=’orcl’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area’
*.db_recovery_file_dest_size=7340032000
*.db_unique_name=’stby’
*.diagnostic_dest=’/u01/ofsad2′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)’
*.fal_client=’STBY’
*.fal_server=’ORCL’
*.log_archive_config=’dg_config=(orcl,stby)’
*.LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY’
*.log_archive_dest_2=’SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’
*.log_archive_dest_state_2=’ENABLE’
*.log_file_name_convert=’/u02/oradata/orcl/’,’/u02/oradata/stby/’
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=423624704
*.standby_file_management=’auto’
*.undo_tablespace=’UNDOTBS1′

. setstby

sql > create spfile from pfile;

Configure Listener for Static service registration

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = apex4)
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = apex4)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = stby)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
)

Configure service name in tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

STARTUP NOMOUNT STANDBY DATABASE –

sql > startup nomount;

Now run rman command to create standby database –

stby:/u01/oracle/product/11.2/dbs> rman target sys/temp@orcl auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 27 11:55:10 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/27/2010 12:00:53
RMAN-06217: not connected to auxiliary database with a net service name

Therefore connect to auxiliary using connect string –

rman target sys/temp@orcl auxiliary sys/temp@stby

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 27 12:02:22 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/oracle/product/11.2/dbs/orapworcl’ auxiliary format
‘/u01/oracle/product/11.2/dbs/orapwstby’ ;
}
executing Memory Script

Starting backup at 27/JUL/10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
Finished backup at 27/JUL/10

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u02/oradata/stby/control01.ctl’;
restore clone controlfile to ‘/u02/oradata/stby/control02.ctl’ from
‘/u02/oradata/stby/control01.ctl’;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2/dbs/snapcf_orcl.f tag=TAG20100727T121951 RECID=4 STAMP=725458793
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27/JUL/10

Starting restore at 27/JUL/10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27/JUL/10

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/stby/temp01.dbf”;
switch clone tempfile all;
“/u02/oradata/stby/fla_tbs1.dbf” ;
and more …
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

And more…

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/orcl/sysaux01.dbf
And more….
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like “/u02/oradata/flash_recovery_area/ORCL/archivelog/2010_07_27/o1_mf_1_221_64wqpxq8_.arc” auxiliary format
“/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_%u_.arc” ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=221 RECID=180 STAMP=725458909
output file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc

datafile 1 switched to datafile copy
And more…
input datafile copy RECID=10 STAMP=725458912 file name=/u02/oradata/stby/fla_tbs1.dbf

contents of Memory Script:
{
set until scn 5284605;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27/JUL/10
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 221 is already on disk as file /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
archived log file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc thread=1 sequence=221
media recovery complete, elapsed time: 00:00:01
Finished recover at 27/JUL/10
Finished Duplicate Db at 27/JUL/10

RMAN>

CREATE STANDBY LOGS –

Run on both instances –

ON PRIMARY

alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo04.rdo’ size 52428800;

ON STANDBY

alter database add standby logfile ‘/u02/oradata/stby/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo04.rdo’ size 52428800;

On STANDBY – START MEDIA RECOVERY.

sql > Recover managed standby database disconnect;

Tail –f alert logs in standy site to check if all is well…

Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_269_65dll3bx_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_270_65dll45g_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_271_65dll3z5_.arc
Media Recovery Waiting for thread 1 sequence 272 (in transit)

Also confirm with this sql that all scripts are in place…

ON PRIMARY –

select thread#, sequence#, archived, applied from v$archived_log where dest_id=2 and sequence# in ( select max(sequence#) from v$archived_log);

THREAD# SEQUENCE# ARC APPLIED
———- ———- — ———
1 274 YES YES
SQL>

select
LOG_ARCHIVED_ON_PRIMARY, LOG_APPLIED_ON_STANDBY, LOG_ARCHIVED_ON_PRIMARY-LOG_APPLIED_ON_STANDBY LOG_GAP
from
(select sequence# LOG_ARCHIVED_ON_PRIMARY from v$archived_log where dest_id=1 and archived=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=1)) primary,
(select sequence# LOG_APPLIED_ON_STANDBY from v$archived_log where dest_id=2 and applied=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=2))
/

LOG_ARCHIVED_ON_PRIMARY LOG_APPLIED_ON_STANDBY LOG_GAP

274 274 0

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: