How To Create A Standby Database Using RMAN IOUG 2003 - Presentation Q19 By:

Husam Tomeh Sr. Database Administrator

Date: Tuesday, April 29, 2003 @ 11:45 am - 12:15 pm Room: Asia 4

IOUG 2003, Q19 - Husam Tomeh

1

Agenda • • • • • • • • • •

Objective and Scope RMAN – Recovery Manager Oracle 9i Standby database Environment Configuration Preparation Setup How-to Steps Tips Scripts Scripts Output Q&A IOUG 2003, Q19 - Husam Tomeh

2

Objective and Scope • Provide a basic understanding of the Oracle 9i standby database feature, built in the Recovery Manager (RMAN) tool. • Demonstrate a step-by-step procedure on how to prepare and create a standby database using Recovery Manager, as an alternative approach to other well-known conventional methodologies. • Explore few tips which would hopefully make your implementation procedure a lot easier, faster, and error-free experience. • For all DBAs and other folks who are interested in new features of RMAN and Standby database. IOUG 2003, Q19 - Husam Tomeh

3

RMAN – Recovery Manager • Oracle tool to allow backup, restore, and recover data, control, or/and archivelog files. • Invoked as a CLI-based (Command Line Interface) , or GUI-based (Graphical User Interface) utility – OEM Backup Manager • Manages and logs all backup and recovery operations automatically by storing the metadata about these operations in the control file, or in a recovery catalog. • Provides built-in inventory listing and reporting as well as corruption detection capabilities. • Besides backup and recovery, RMAN allows to duplicate a database, or create a standby database. IOUG 2003, Q19 - Husam Tomeh

4

Oracle 9i Standby database • Introduced in Oracle 7.3, the standby database has been enhanced dramatically through later releases, ending with Oracle 9i Data Guard to become one of the vital, cost-effective solutions for higher availability. • The Oracle standby database is a database replica created off the primary (production) database as a backup copy on a separate server, and running in an automatic recovery mode. In case of a failure of the production (primary) database/server, a failover to the standby is necessary which takes few minutes. • A failover is when the standby database is activated manually to become the new primary database. The original primary database becomes useless, and needs to be re-built as a standby database. IOUG 2003, Q19 - Husam Tomeh

5

Environment Configuration • Primary server (prod10) : -

Fujitsu PrimePower 800 hardware platform. Sun Solaris 2.8 Operating System Oracle 9.2 server software Oracle 9.2 primary database (prod)

• Standby server (prod15) : - Fujitsu PrimePower 800 hardware platform. - Sun Solaris 2.8 Operating System - Oracle 9.2 server software - Oracle 9.2 standby database (prodSB)

IOUG 2003, Q19 - Husam Tomeh

6

Preparation Setup 1) Oracle NET Setup : - Modify/set up all necessary Oracle Net listener and tnsnames files required on both the primary and standby servers appropriately.

2) Initialization pfile Setup : - Set all necessary initialization parameters in the initialization parameter file (init.ora pfile) for the primary database. - Copy the pfile of the primary database to the standby site and modify it properly for all necessary parameters for a standby database. IOUG 2003, Q19 - Husam Tomeh

7

Preparation Setup 3) Password file Setup: - Create a password file for the standby database as this is a prerequisite to be able to connect to the standby instance as an auxiliary instance with sysdba privileges in order to start the restore process.

4) Standby instance and listener Startup : - Start the standby instance with nomount option (control file is not read). Start the standby listener.

IOUG 2003, Q19 - Husam Tomeh

8

Preparation Setup 1.a) Oracle NET (listener) Setup : Primary listener.ora file

Standby listener.ora file

listener_prod listener_prodSB (address_list = (address_list = (address = (protocol = tcp) (address = (protocol = tcp) (host = prod10) (host = prod15) (port = 1525) (port = 1525) ) ) ) ) sid_list_listener_prod sid_list_listener_prodSB (sid_list = (sid_list = (sid_desc = (sid_desc = (oracle_home=/u01/app/oracle/product/9.2) (oracle_home=/u01/app/oracle/product/9.2) (sid_name = prod) (sid_name = prodSB) ) ) ) ) IOUG 2003, Q19 - Husam Tomeh

9

Preparation Setup 1.b) Oracle NET (tnsnames) Setup : Primary tnsnames.ora file prod (description = (address = (protocol = tcp) (host = prod10) (port = 1525) (connect_data=(sid=prod) ) ) prodSB (description = (address = (protocol = tcp) (host = prod15) (port = 1525) (connect_data = (sid = prodSB) ) )

Standby tnsnames.ora file prod (description = (address = (protocol = tcp) (host = prod10) (port = 1525) (connect_data = (sid = prod) ) ) prodSB (description = (address = (protocol = tcp) (host = prod15) (port = 1525) (connect_data = (sid = prodSB) ) )

IOUG 2003, Q19 - Husam Tomeh

10

Preparation Setup 2) Initialization pfile Setup :

Primary init.ora pfile

Standby init.ora pfile

db_name = prod instance_name = prod fal_server = prod fal_client = prodSB log_archive_dest_1 = 'LOCATION=/u02/arch/prod' log_archive_dest_2 = 'SERVICE=prodSB lgwr async noaffirm' log_archive_dest_state_2 = enable standby_archive_dest = '/u02/arch/prod' log_archive_start = true log_archive_format = prod_%s.arc remote_login_passwordfile = EXCLUSIVE

db_name = prod instance_name = prodSB fal_server = prod fal_client = prodSB log_archive_dest_1 = 'LOCATION=/u02/arch/prod' log_archive_dest_2 = 'SERVICE=prodSB lgwr async noaffirm' log_archive_dest_state_2 = enable standby_archive_dest = '/u02/arch/prod' log_archive_start = true log_archive_format = prod_%s.arc remote_login_passwordfile = EXCLUSIVE

IOUG 2003, Q19 - Husam Tomeh

11

Preparation Setup 3) Password File Creation (for the standby) : prod15$>

orapwd file=$ORACLE_HOME/dbs/orapwdprodSB \ password=mypwd entries=4

4.a) Standby instance startup (nomount) : export ORACLE_SID=prodSB prod15$> sqlplus ’/as sysdba’ prod15$>

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Mar 22 15:21:51 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance.

SQL>

startup nomount pfile=initprodSB.ora

ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers

555713960 730536 335544320 218103808 1335296

bytes bytes bytes bytes bytes

IOUG 2003, Q19 - Husam Tomeh

12

Preparation Setup 4.b) Standby Listener startup : - Start up the listener of the standby database : prod15$> lsnrctl start listener_prodSB

- To check status of the listener : prod15$> lsnrctl status listener_prodSB

Now, that the preparation setup has been completed, let’s proceed with a step-by-step procedure on how to create the standby database using Recovery Manager (RMAN). IOUG 2003, Q19 - Husam Tomeh

13

Step 1: Back up the primary database for standby 1) Using RMAN, connect to primary (target) database : prod10$> prod10$>

export ORACLE_SID=prod rman nocatalog target /

2) Backup the primary database files : RMAN>

backup database ;

3) Backup the current control file of the primary database for standby: RMAN>

backup current controlfile for standby ;

IOUG 2003, Q19 - Husam Tomeh

14

Step 1: Back up the primary database for standby (cont’) 4) Archive the current redo log of the primary database to ensure that the control file checkpoint is archived : RMAN>

sql ”alter system archive log current” ;

5) Backup the archivelogs of primary database : RMAN>

backup archivelog all;

6) Disconnect RMAN connection to the primary database : RMAN>

exit ;

IOUG 2003, Q19 - Husam Tomeh

15

Step 2: Restore/clone to the standby server 1) Transfer (ftp) the generated RMAN backup pieces to the remote standby server (prod15). 2) Using RMAN, connect to the primary (target) database , and to the auxiliary (standby) instance : export ORACLE_SID=prod prod10$> rman target / auxiliary sys/mypwd@prodSB prod10$>

IOUG 2003, Q19 - Husam Tomeh

16

Step 2: Restore/clone to the standby server (cont’) 3) Using 9i Data Guard feature of RMAN, restore/replicate the database on the standby server : RMAN>

duplicate target database \ for standby dorecover nofilenamecheck;

The above command automatically : a) restores standby control file(s) on the standby site. b) mounts the standby database. c) restores all data files to the standby site. d) sets newname for the datafiles if defined in the pfile e) performs media recover and apply necessary archive logs (“dorecover” keyword). IOUG 2003, Q19 - Husam Tomeh

17

Step 3: Place the standby in “managed recovery” mode • Now, the standby database has been built and mounted, with both the primary and standby listeners up, place the standby database in “managed recovery” mode to continue the synchronization of new redo log entries from the primary database : SQL>

recover standby database ;

SQL>

alter database recover managed

\

standby database disconnect ;

IOUG 2003, Q19 - Husam Tomeh

18

Verify operation of standby database • Manually apply a couple of log switches on the primary database: SQL>

alter system switch logfile ;

• Check the alert file and related dynamic data dictionary views (v$) for new archivelog files received and applied to the standby site : SQL>

select name,created,open_mode, protection_mode,database_role from v$database;

SQL>

select process,status,thread#,sequence#, block#,blocks from v$managed_standby;

IOUG 2003, Q19 - Husam Tomeh

19

Tips - Tip 1: The RMAN ‘duplicate’ command does NOT restore a locally-managed TEMP tablespace. Remember to issue the following DDL statement to re-create it when it’s time to activate the standby database due to a failover, or when altering the standby database from a recovery mode to a read-only mode (for querying) : SQL> alter tablespace TEMP Add tempfile \ ’/u02/oradata/prod/temp01.dbf’ - Tip 2: The RMAN ‘ duplicate for standby ’ command does NOT create redo logs for the standby database since the standby uses the archive logs to sync in with its primary database. When it’s time to activate the standby database, the redo logs will be created automatically.

IOUG 2003, Q19 - Husam Tomeh

20

Tips

(cont’)

RMAN-03009 : failure of backup command on ORA_DISK_1 channel ORA-19602 : cannot backup/copy active file in NOARCHIVELOG mode - Cause : Error generated during an attempt to issue “ backup database ”

command where the primary database is not in “archivelog” mode. - Tip 3:

Configure the primary database in “ARCHIVELOG” mode. 1) Ensure proper archiving parameters are set properly in the initialization pfile. 2) Shutdown the primary database gracefully 3) SQL> startup mount 4) SQL> alter database archivelog ; 5) SQL> alter database open ; 6) SQL> archive log list ; # check status

IOUG 2003, Q19 - Husam Tomeh

21

Tips

(cont’)

RMAN-00554 : initialization of internal recovery manager pkg failed RMAN-04006 : error from auxiliary database ORA-01031 : insufficient privileges - Cause : Error generated during an attempt to connect to the auxiliary

- Tip 4:

instance (standby) to start the restore process where no password file was created. “ rman target / auxiliary sys/mypwd@prodSB ” 1) Ensure that the standby instance is shutdown before attempting to create the password file. 2) Ensure that the initialization parameter, remote_login_passwordfile=EXCLUSIVE 3) Create a password file for the standby (auxiliary) instance: orapwd file=$ORACLE_HOME/dbs/orapwdprodSB \ password=mypwd entries=4 4) Check v$pwfile_users to ensure that “sys” has sysdba privilege:

select * from pwfile_users ; USERNAME

SYSDB

SYSOP

SYS

TRUE

TRUE

IOUG 2003, Q19 - Husam Tomeh

22

Tips

(cont’)

RMAN-03002 : failure of Duplicate Db command at 03/23/03 15:17 RMAN-05501 : aborting duplication of target database RMAN-05001 : auxiliary filename with a file used by target database - Cause : Error generated during an attempt to “duplicate target

database for standby dorecover” . RMAN failed restoring a data file on the standby server which has the same name on the target (primary) server. - Tip 5:

a) For a two-server configuration of the 9i DataGuard where the primary and standby databases are on separate servers, use the keyword “nofilenamecheck” when in the ‘duplicate for standby ’ command : “duplicate target database for standby dorecover \ nofilenamecheck ; ” b) For one-server configuration where the primary and standby are on the same server, never use “nofilenamecheck” keyword Instead modify the standby pfile to rename the data/redo files: db_file_name_convert = (’/u02/oradata/prod’, ’/u02/oradata/prodSB’) log_file_name_convert = (’/u02/redo/prod’ , ’/u02/redo/prodSB’) lock_name_space = prodSB # avoid ORA-1102 (sharing same DB name) IOUG 2003, Q19 - Husam Tomeh

23

Script #1 : rman_create_standby_bkup.sh #!/bin/ksh export ORACLE_SID=prod # primary(target) database export ORATAB=/var/opt/oracle/oratab export ORACLE_HOME="`grep ^$ORACLE_SID $ORATAB | cut -d: -f2`" export ORACLE_HOME=${ORACLE_HOME:-/u01/app/oracle/product/9.2.0} export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/ccs/lib export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin rman nocatalog target / <
IOUG 2003, Q19 - Husam Tomeh

24

Script #2 : rman_restore_standby_dup.sh #!/bin/ksh export export export export export export export export

ORACLE_SID=prod # primary(target) database ORATAB=/var/opt/oracle/oratab ORACLE_HOME="`grep ^$ORACLE_SID $ORATAB | cut -d: -f2`" ORACLE_HOME=${ORACLE_HOME:-/u01/app/oracle/product/9.2.0} LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/ccs/lib PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin ORACLE_TERM=ansi NLS_LANG=american

rman target / auxiliary sys/mypwd@prodSB << EOF duplicate target database for standby dorecover nofilenamecheck ; exit ; EOF echo "\nStandby database is built and mounted" echo "\nVerify then place standby in managed recovery mode"

IOUG 2003, Q19 - Husam Tomeh

25

Script #1 Output Recovery Manager: Release 9.2.0.1.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: prod (DBID=1654104275) using target database controlfile instead of recovery catalog RMAN> Starting backup at Mar 23 2003 16:18:35 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=17 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current controlfile in backupset input datafile fno=00003 name=/u02/oradata/prod/cd_repository_f1.dbf input datafile fno=00001 name=/u02/oradata/prod/system01.dbf input datafile fno=00002 name=/u02/oradata/prod/undo01.dbf input datafile fno=00004 name=/u02/oradata/prod/tools01.dbf input datafile fno=00005 name=/u02/oradata/prod/users01.dbf channel ORA_DISK_1: starting piece 1 at Mar 23 2003 16:18:36 channel ORA_DISK_1: finished piece 1 at Mar 23 2003 16:19:21 piece handle=/dba/backup/backupDB_prod_489428316 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 Finished backup at Mar 23 2003 16:19:22 IOUG 2003, Q19 - Husam Tomeh

26

Script #1 Output (cont’) RMAN> Starting backup at Mar 23 2003 16:19:22 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including standby controlfile in backupset channel ORA_DISK_1: starting piece 1 at Mar 23 2003 16:19:22 channel ORA_DISK_1: finished piece 1 at Mar 23 2003 16:19:23 piece handle=/dba/backup/backupSTBYCTL_prod_489428362 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at Mar 23 2003 16:19:23 RMAN> sql statement: alter system archive log current

IOUG 2003, Q19 - Husam Tomeh

27

Script #1 Output (cont’) RMAN> Starting backup at Mar 23 2003 16:19:25 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=6 recid=26 stamp=484407125 input archive log thread=1 sequence=156 recid=27 stamp=489343781 input archive log thread=1 sequence=157 recid=28 stamp=489346309 input archive log thread=1 sequence=158 recid=29 stamp=489346344 input archive log thread=1 sequence=159 recid=30 stamp=489347020 input archive log thread=1 sequence=160 recid=31 stamp=489347024 input archive log thread=1 sequence=161 recid=32 stamp=489347187 input archive log thread=1 sequence=162 recid=33 stamp=489347189 input archive log thread=1 sequence=163 recid=34 stamp=489428365 input archive log thread=1 sequence=164 recid=35 stamp=489428367 channel ORA_DISK_1: starting piece 1 at Mar 23 2003 16:19:28 channel ORA_DISK_1: finished piece 1 at Mar 23 2003 16:19:29 piece handle=/dba/backup/backupARCs_prod_489428368 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at Mar 23 2003 16:19:29 IOUG 2003, Q19 - Husam Tomeh

28

Script #1 Output (cont’) RMAN> Recovery Manager complete. copy/ftp the backup pieces to the standby site Verify the auxiliary (standby) instance is in nomount mode

~~~~~~~~~~~ End of Script #1 Output ~~~~~~~~~~~~~~~~~

IOUG 2003, Q19 - Husam Tomeh

29

Script #2 Output Recovery Manager: Release 9.2.0.1.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: prod (DBID=1654104275) connected to auxiliary database: prod (not mounted) RMAN> Starting Duplicate Db at Mar 23 2003 16:24:53 using target database controlfile instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=12 devtype=DISK 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'; }

IOUG 2003, Q19 - Husam Tomeh

30

Script #2 Output (cont’) executing script: Memory Script Starting restore at Mar 23 2003 16:24:54 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring controlfile output filename=/u02/redo1/prod/control02.ctl channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/dba/backup/backupSTBYCTL_prod_489428362 tag=TAG20030323T161922 params=NULL channel ORA_AUX_DISK_1: restore complete Finished restore at Mar 23 2003 16:24:58 replicating controlfile input filename=/u02/redo1/prod/control02.ctl output filename=/u02/oradata/prod/control01.ctl sql statement: alter database mount standby database

IOUG 2003, Q19 - Husam Tomeh

31

Script #2 Output (cont’) printing stored script: Memory Script { set until scn 1685334; set newname for datafile 1 to "/u02/oradata/prod/system01.dbf"; set newname for datafile 2 to "/u02/oradata/prod/undo01.dbf"; set newname for datafile 3 to "/u02/oradata/prod/cd_repository_f1.dbf"; set newname for datafile 4 to "/u02/oradata/prod/tools01.dbf"; set newname for datafile 5 to "/u02/oradata/prod/users01.dbf"; restore check readonly clone database ; }

IOUG 2003, Q19 - Husam Tomeh

32

Script #2 Output (cont’) executing executing executing executing executing executing executing

script: Memory Script command: SET until clause command: SET NEWNAME command: SET NEWNAME command: SET NEWNAME command: SET NEWNAME command: SET NEWNAME

Starting restore at Mar 23 2003 16:25:07 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u02/oradata/prod/system01.dbf restoring datafile 00002 to /u02/oradata/prod/undo01.dbf restoring datafile 00003 to /u02/oradata/prod/cd_repository_f1.dbf restoring datafile 00004 to /u02/oradata/prod/tools01.dbf restoring datafile 00005 to /u02/oradata/prod/users01.dbf channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/dba/backup/backupDB_prod_489428316 tag=TAG20030323T161836 params=NULL channel ORA_AUX_DISK_1: restore complete Finished restore at Mar 23 2003 16:25:34 IOUG 2003, Q19 - Husam Tomeh

33

Script #2 Output (cont’) printing stored script: Memory Script { switch clone datafile all; } executing script: Memory Script datafile 2 switched to datafile copy input datafilecopy recid=21 stamp=489428734 filename=/u02/oradata/prod/undo01.dbf datafile 3 switched to datafile copy input datafilecopy recid=22 stamp=489428734 filename=/u02/oradata/prod/cd_repository_f1.db datafile 4 switched to datafile copy input datafilecopy recid=23 stamp=489428734 filename=/u02/oradata/prod/tools01.dbf datafile 5 switched to datafile copy input datafilecopy recid=24 stamp=489428734 filename=/u02/oradata/prod/users01.dbf

IOUG 2003, Q19 - Husam Tomeh

34

Script #2 Output (cont’) printing stored script: Memory Script { set until scn 1685334; recover standby clone database delete archivelog ; } executing script: Memory Script executing command: SET until clause Starting recover at Mar 23 2003 16:25:34 using channel ORA_AUX_DISK_1 starting media recovery

IOUG 2003, Q19 - Husam Tomeh

35

Script #2 Output (cont’) starting media recovery channel ORA_AUX_DISK_1: starting archive log restore to default destin channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=163 channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=164 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/dba/backup/backupARCs_prod_489428368 tag=TAG20030323T161928 channel ORA_AUX_DISK_1: restore complete archive log filename=/u02/arch/prod/prod_163.arc thread=1 sequence=163 channel clone_default: deleting archive log(s) archive log filename=/u02/arch/prod/prod_163.arc recid=1 stamp=489428737 archive log filename=/u02/arch/prod/prod_164.arc thread=1 sequence=164 channel clone_default: deleting archive log(s) archive log filename=/u02/arch/prod/prod_164.arc recid=2 stamp=489428737 media recovery complete Finished recover at Mar 23 2003 16:25:38 Finished Duplicate Db at Mar 23 2003 16:25:38 RMAN> Recovery Manager complete. ~~~~~~~~~~~~~~~~~ End of Script# 2 Output. ~~~~~~~~~~~~~~~~~~~~~~~~~ IOUG 2003, Q19 - Husam Tomeh

36

References • Oracle 9i R2 Documentation, Oracle Corporation. • Oracle RMAN pocket reference, Darl Kuhn & Scott Schulze, O’Reilly. • Oracle DBA – Tips & Technologies, Sumit Sarin • Implementing Oracle9i Data Guard for Higher Availability, Daniel Liu, www.dbazine.com/liu4.html • Many thanks to Larry Carpenter of Oracle Corporation, Ann Collins, Larry Bailey, and Daniel Liu of First American RES for their constructive comments and reviews. IOUG 2003, Q19 - Husam Tomeh

37

Q&A How To Create A Standby Database Using RMAN IOUG 2003 - Presentation Q19

Husam Tomeh [email protected] [email protected]

http://www.firstam.com IOUG 2003, Q19 - Husam Tomeh

38