Using RMAN to Clone New and Refresh Existing Databases

Using RMAN to Clone New and
Refresh Existing Databases
YonganCui
ComforceCorporation
2
难者不会，会者不难
It is difficulty if you do not know how
It is easy if you know how
Using RMAN to Clone New and Refresh Existing Databases
3
Agenda
􀁻Introduction
􀁻Refresh existing database
􀁺Prechecktasks
􀁺Refresh script
􀁺Explanation of the script
􀁺Run the script
􀁻Clone a new database
􀁻HP OmniBackperformance tip
4
General Database & Server Configuration
5
Simple Database & Server Configuration
6
Refresh Existing Database
Note: The PRD RMAN backup details will not be discussed here.
7
Refresh PRD to DEV: PrecheckTasks
􀁻Run all of these precheckson the server SDEV
􀁻No need to go through all of these checks if your DEV database has already been backed up to tape using RMAN
8
Refresh PRD to DEV: PrecheckTasks–1/6Check DEV initialization parameter remote_login_passwordfile$ sqlplussys/devsyspwd@devas sysdba(Oracle Version 10)$ sqlplus“sys/devsyspwd@devas sysdba”(Oracle Version 9)*.Change devsyspwdto your DEV SYS passwordORA-01017: invalid username/password; logon denied.Change NONE to SHARED or EXCLUSIVECreate orapwDEVusing orapwdif it does not exist
9
Refresh PRD to DEV: PrecheckTasks–2/6Check the connection to production database PRD$ sqlplussys/prdsyspwd@PRDas sysdbaIf it fails, you may need to add a PRD entry in TNSNAMES.ORA.The remote_login_passwordfilefor PRD should also be SHAREDor EXCLUSIVE.
10
Refresh PRD to DEV: PrecheckTasks–3/6
Install Media Management client software if it does not exist.
You may need to ask your system administrator to do it for you.
11
Refresh PRD to DEV: PrecheckTasks–4a/6
Check the media management API
(when tapes are involved in the refresh process)
Oracle diagnostic tool -stbtest
Use RMAN to backup a small file to tape
12
Refresh PRD to DEV: PrecheckTasks–4b/6RMAN backup a small datafileto tapeRMAN> RUN{ALLOCATE CHANNEL c0 DEVICE TYPE ‘sbt_tape’;BACKUP DATAFILE 2;REAEASE CHANNEL c0;}
13
Refresh PRD to DEV: PrecheckTasks–5/6
Find a place to temporarily hold archivelogs
􀁻Refresh = restore + recovery + db rename + ….
􀁻may need some archive log files
􀁻Log files will be restored into this temporary folder first and then be applied to the DEV database recovery
􀁻Size of all archivelogfiles is dependent upon the PRD backups and DEV refresh time you specified.
􀁻Will discuss the selection of refresh time later
14
Refresh PRD to DEV: PrecheckTasks–6a/6Add datafilepath instruction in the initDEV.orafileDB_FILE_NAME_CONVERT = (/PRD/oradata1/, /DEV/oradata/)DB_FILE_NAME_CONVERT = (/PRD/oradata2/, /DEV/oradata/)LOG_FILE_NAME_CONVERT= (/PRD/redo1/, /DEV/oradata/)LOG_FILE_NAME_CONVERT= (/PRD/redo2/, /DEV/oradata/)PRDDEVDatafileDirectories/PRD/oradata1/PRD/oradata2/PRD/redo1/PRD/redo2/DEV/oradata
15
Refresh PRD to DEV: PrecheckTasks–6b/6
This does not work
DB_FILE_NAME_CONVERT=……
DB_FILE_NAME_CONVERT=……
LOG_FILE_NAME_CONVERT=……
DB_FILE_NAME_CONVERT=……
16
Refresh Script -1/3
1 # refreshdb.rman
2 # rmanscript to refresh a source database to a destination database
3
4 if [ $# != 3 ]
5 then
6 echo "\n**** Usage: refreshdb.rman<sourcedbname> to <destinationdbname> \n"
7 exit 1;
8 fi
9
10 echo "\nDoyou really want to refresh $1 to $3 ? (y/n) : \n\c"
11 read choice
12 if [ $choice = y ]
13Then
14 SID=$1
15 export SIB
16 DID=$3
17 export DID
18 sqlplus"sys/devsyspwd@$DIDas sysdba" <<EOF
19 shutdown immediate
17
Refresh Script -2/3
20 startup nomount
21 exit
22 EOF
23
24 if [ $DID = DEV ]
25 then
26 SYSTEMPWD=devsystempwd
27 export SYSTEMPWD
28 elif[ $DID = TST ]
29 then
30 SYSTEMPWD=tstsystempwd
31 export SYSTEMPWD
32 fi
33
34 rman<<EOF
35 CONNECT CATALOG rman/rmanpwd@rep
36 CONNECT TARGET sys/prdsyspwd@$SID
37 CONNECT AUXILIARY sys/devsyspwd@$DID
38 RUN {
39 allocate AUXILIARY channel 'ach0' DEVICE type 'sbt_tape';
18
Refresh Script -3/3
40 set until time "to_date('060607 14:30','mmddyy hh24:mi')";
41 SET ARCHIVELOG DESTINATION TO '/temp/arch';
42 DUPLICATE TARGET DATABASE TO $DID;
43 shutdown clone;
44 startup clone mount;
45 sqlclone 'alter database noarchivelog';
46 sqlclone 'alter database open';
47 sqlclone "ALTER TABLESPACE TEMP ADD TEMPFILE ''/DEV/temp01_$DID.dbf'' SIZE 60M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 6000M";
48sqlclone 'alter database rename global_nameto $DID';
49 sqlclone 'alter user system identified by $SYSTEMPWD';
50 }
51 EOF
52
53 fi
19
Explanation of the Script -1/84 if [ $# != 3 ]5 then6 echo "\n**** Usage: refreshdb.rman<sourcedbname> to <destinationdbname> \n"7 exit 1;8Fi$ refreshdb.rman**** Usage: refreshdb.rman<sourcedbname> to <destinationdbname>
20
Explanation of the Script -2/810 echo "\nDoyou really want to refresh $1 to $3 ? (y/n) : \n\c"11 read choice􀁻Existing DEV database will be completely overwritten after running the script 􀁻May need to backup the DEV database before you run the refresh
21
Explanation of the Script -3/812 if [ $choice = y ]13 then14 SID=$115 export SIB16 DID=$317 export DID18 sqlplus"sys/devsyspwd@$DIDas sysdba" <<EOF19 shutdown immediate20 startup nomount21 exit22 EOF
22
Explanation of the Script -4/824 if [ $DID = DEV ]25 then26 SYSTEMPWD=devsystempwd27 export SYSTEMPWD28 elif[ $DID = TST ]29 then30 SYSTEMPWD=tstsystempwd31 export SYSTEMPWD32 fi
23
Explanation of the Script -5/834 rman<<EOF35 CONNECT CATALOG rman/rmanpwd@rep36 CONNECT TARGET sys/prdsyspwd@$SID37 CONNECT AUXILIARY sys/devsyspwd@$DIDLine-35: If no RMAN catalog database, remove this line and make sure that the control_file_record_keep_timecovers your refresh time.
24
Explanation of the Script –6a/838 RUN {39 allocate AUXILIARY channel 'ach0' DEVICE type 'sbt_tape';40 set until time "to_date('060607 03:22','mmddyy hh24:mi')";41 SET ARCHIVELOG DESTINATION TO '/temp/arch';42 DUPLICATE TARGET DATABASE TO $DID;
25
Explanation of the Script –6b/8select to_char(max(completion_time), 'mmddyyhh24:mi')from rc_backup_datafilewhere db_name= '$1'and to_char(completion_time,'mmddyy') = '$2';
Run in RmanCatalog database
26
Explanation of the Script -7/843 shutdown clone;44 startup clone mount;45 sqlclone 'alter database noarchivelog';46 sqlclone 'alter database open';
27
Explanation of the Script -8/847 sqlclone "ALTER TABLESPACE TEMP ADD TEMPFILE ''/DEV/temp01_$DID.dbf'' SIZE 60M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 6000M";48 sqlclone 'alter database rename global_nameto $DID';49 sqlclone 'alter user system identified by $SYSTEMPWD';
28
Run the Script$ refreshdb.rmanPRD to DEVDo you really want to refresh PRD to DEV ? (y/n) :
29
Clone a new database TST
30
Clone a new database TST
􀁻Do all the following on TST server:
􀁺Install Oracle binaries if they have not been installed or clone a Oracle home
􀁺Create empty folders in $ORACLE_BASE/admin/TST/bdump, udump, etc
􀁺Create $ORACLE_HOME/dbs/initTST.oraand $ORACLE_HOME/dbs/orapwTST
􀁺Create TST datafiledirectories
􀁻Perform all DEV refresh steps discussed before
31
HP OmniBackRMAN backup performance –1/2
allocate AUXILIARY channel 'ach0' DEVICE type 'sbt_tape';
32
HP OmniBackRMAN backup performance –2/2
allocate AUXILIARY channel 'ach0' DEVICE type 'sbt_tape';
allocate AUXILIARY channel 'ach1' DEVICE type 'sbt_tape’;
allocate AUXILIARY channel 'ach2' DEVICE type 'sbt_tape’;
allocate AUXILIARY channel 'ach3' DEVICE type 'sbt_tape’;
set limit channel ach0 maxopenfiles=1;
set limit channel ach1 maxopenfiles=1;
set limit channel ach2 maxopenfiles=1;
set limit channel ach3 maxopenfiles=1;
33
Summary
􀁻Check remote_login_passwordfile
􀁻Check source db connection
􀁻Check RMAN catalog db connection
􀁻Install Media Management software
􀁻Check the Media Management API
􀁻Find a place for archivelogs
􀁻Add datafilepath instruction in init.ora
􀁻Prepare the refresh script
􀁻Run the script
34
Thank You
Q&A