oracle RMAN recovery scenarios and real life usage
Scenario 1
1)How should I connect to Rman?
First read the theory and you can understand that you can use RMAN in 2 ways
a)using recovery catalog
b)using the database control file.
The theory about rman can be accessed here rman backups user guide
If you want to know the syntax for any rman command then use this excellent reference guide Rman reference guide
Almost all production databases using the 1st option i.e the recovery catalog is used.
below are the commands i use to connect to the recovery catalog using RMAn on a unix server.
->log into the unix server as the oracle user.
or
->log into the unix server as your user and do a su to the oracle user.
->make sure that the ORACLE_SID variable is set to your database name.
a)start rman and first connect to your database
rman target /
connected to target database: PRICED (DBID=3676647350)
–you will see the above message which says that i connected to my database whose name is PRICED and the database id is 3676647350
–next i am connecting to the recovery catalog
–the RMAN0iI is the tnsnames entry in my tnsnames.ora file which contains the server name and the recover catalog database name
— the rman/rman_dcc in the below command are the username and password that you created in the recovery catalog database.
–if you dont these details then ask your other dba colleagues.
b)now connect to the recovery catalog database
RMAN> connect catalog rman/rman_dcc@RMAN01I
connected to recovery catalog database
–now you saw the commands to connect to your database and then to the recovery catalog using rman.
Scenario 2
2)How should i connect to the database using rman by explicitly specifying the username and password.
–first type rman on your commandline
dsc03*testinst-> rman
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Nov 25 12:57:25 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN>
–so now you started your rman session above.now connect to your database by specifying the username,password and your database tnsnames.ora entry.
RMAN->connect target omnibackup/omni@PRICED2
connected to target database: PRICED2(DBID=3676647350)
Scenario 3
3)one fine day , we got some error message during a backup of our database. The error message looked like below
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/23/2008 22:31:40
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /oradb/u01/dbxyz/log/dbxyz91775.arc
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
my manager asked me to fix it.
so the error messages looks like the rman was trying to backup some archivelog but rman couldnt find the archivelog on the disk. probably someone deleted the archive log file.
to fix this i must connect to my database using rman and then i should connect to my recovery catalog and then i should run a special command to fix this error. Below are the steps i did.
dsc03*testinst-> rman
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Nov 25 12:57:25 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN->connect target omnibackup/omni@PRICED2
connected to target database: PRICED2(DBID=3676647350)
RMAN->crosscheck archivelog all;
so the above command tells rman which all archivelog files do not exist on disk and asks rman to ignore those files the next time.
next day my backup ran fine.
Scenario 4
4)one day our rman log files had this below error
Starting backup at 22-APR-2008 22:39:05
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/22/2008 22:39:07
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer
SVR4 Error: 959: Unknown error
Additional information: 7011
ORA-19511: Error received from media manager layer, error text:
SBT error = 7011, errno = 959, sbtopen: system error
Most of the times if you get above error then that means you have a problem with permissions or some other problem with your media management layer.so you have to speak to your backup team or storage team
who handles the storage where your backups are stored.
In our case the problem was because of some missing permissions in the log directory where the media manager writes its log files.so the unix admin by mistake changed the permissions and that was causing the
above error as the media manager was not able to create the log files.
as you might have known by now ..when you use rman to backup your datafiles the flow of backup is as below
rman -> talks to media manager(tivoli,tdp0 etc) -> the media manager then backups the database files into tape drives or hard disks in the staorage area network.
Scenario 5
5)media manager configuration file real life example.
we use tivoli as the media manager and below is the example of how the configuration file of a media manager looks like.
DSMI_ORC_CONFIG /opt/tivoli/tsm/client/api/bin64/dsm.opt
DSMI_LOG /var/log/tsm/tdpo
TDPO_FS dbxyz_orc
TDPO_NODE dbxyz-ora
TDPO_OWNER oracle
TDPO_PSWDPATH /opt/tivoli/tsm/client/oracle/bin64
*TDPO_DATE_FMT 1
*TDPO_NUM_FMT 1
*TDPO_TIME_FMT 1
*TDPO_MGMT_CLASS_2 mgmtclass2
*TDPO_MGMT_CLASS_3 mgmtclass3
*TDPO_MGMT_CLASS_4 mgmtclass4
so the media manager log destination as you can see from above is /var/log/tsm/tdpo and as we discussed in scenario 4 above we were missing some permissions on this folder or subfolders..
Scenario 6
6)one day our backup job failed with below error
/opt/oracle/admin/dbxyz/logs/rman/rman_detail.2008.04.22*.log
RMAN-06207: WARNING: 31 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: ————— —————————————————
RMAN-06214: Backup Piece df_dbxyz_ssh3glb2_1_1
RMAN-06214: Backup Piece df_dbxyz_sth3gme7_1_1
RMAN-06214: Backup Piece df_dbxyz_suh3gmon_1_1
RMAN-06214: Backup Piece df_dbxyz_svh3gn28_1_1
this database never has any problems. the backups were always successfull.but sometimes due to a oracle bug etc it can happen that your files are not in database or not in tape but the recovery catalog still
thinks that the file is available. so anyway i have to fix this and below are the sequence of steps that i did to fix this issue.
a)first i checked in oracle metalink if there was a bug and found the below bug
Bug No. 6811694
Filed 11-FEB-2008 Updated 01-APR-2008
Product Oracle Server – Enterprise Edition Product Version 10.2.0.2.0
Platform HP-UX Itanium Platform Version B.11.23
Database Version 10.2.0.2.0 Affects Platforms Generic
Severity Severe Loss of Service Status Code Bug (Response/Resolution)
Base Bug N/A Fixed in Product Version No Data
Problem statement: BACKUP PIECE ALREADY DELETED IS IN THE RECOVERY CATALOG WITH STATUS AVAILABLE
b)our backup script was trying to delete obsolete backups and for some reason it failed giving the above error. If you see the above error it was giving error on some backup pieces. you have to be very
careful when you get error about specific backup pieces. as you know a backupset contains many backup pieces and each backup piece can be one datafile(as an example).
so a backupset can have the status available but it can contain backup pieces which are expired or which have been deleted from the tape due to some reason and the rman recovery catalog does not know it and
still thinks the backup pieces are available.
dsc03*testinst-> rman
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Nov 25 12:57:25 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN->connect target omnibackup/omni@PRICED2
connected to target database: PRICED2(DBID=3676647350)
RMAN-> crosscheck backup;
This ran but it still not fix the problem. Then i had to run a crosscheck backuppiece for all 4 backup pieces that you can see in above error
RMAN-> crosscheck backuppiece "df_dbxyz_ssh3glb2_1_1";
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_DISK_1
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=df_dbxyz_ssh3glb2_1_1 recid=16222 stamp=574378316
Crosschecked 1 objects
RMAN-> crosscheck backuppiece "df_dbxyz_sth3gme7_1_1";
etc
and then rman changed the status of the files from available to expired as it then found out that those backup pieces were somehow not existing on the tapes/disk.
now i ran the delete obsolete and it worked perfectly.
RMAN-> delete obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 35 days
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1051 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=1084 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=1003 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Data Protection for Oracle: version 5.3.3.0
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
——————– —— —————— ——————–
Backup Set 2271 11-NOV-05
Backup Piece 2548 11-NOV-05 df_dbxyz_ssh3glb2_1_1
Backup Set 2272 11-NOV-05
Backup Piece 2549 11-NOV-05 df_dbxyz_sth3gme7_1_1
Backup Set 2273 11-NOV-05
Backup Piece 2550 11-NOV-05 df_dbxyz_suh3gmon_1_1
Backup Set 2274 11-NOV-05
Backup Piece 2551 11-NOV-05 df_dbxyz_svh3gn28_1_1
Scenario 7
7) some list of rman commands that you can use from time to time
RMAN Maintenance
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 14;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/dbBackup/hotbackup/controlfile%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/dbhome/product/10.2.0/dbs/snapcf_tedsdb.f’; # default
—
–List
—
list backup;
list backup summary;
list backup by file;
list expired backup;
list copy of database archivelog all;
—
–Report
—
report obsolete;
report need backup;
—
–Delete
—
delete obsolete;
delete force obsolete;
—
–Crosscheck
—
crosscheck backup;
delete expired backup;
—
–Change
—
CHANGE BACKUPSET TAG year_end_2005 KEEP FOREVER NOLOGS;
CHANGE BACKUPSET TAG year_end_2005 NOKEEP;
—
–Monitor RMAN via v$ views
—
COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE ‘rman%’
/
–Monitor Job Progress
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’
AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/
–Monitor sbt event
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE ‘%sbt%’
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
/
–Monitor backup and restore performance
V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO
–to validate all datafiles
run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}
–To test the most recent RMAN database backup:
RMAN> restore validate database;
–To test the most recent RMAN spfile backup:
RMAN> restore validate spfile to ‘c:\temp\spfile.ora’;
–To test the most recent RMAN controlfile backup:
RMAN> restore validate controlfile to ‘c:\temp\control01.ctl’;
–To test the most recent RMAN archivelog file(s) backup.
–First determine which archivelog sequences are in the last backup:
RMAN> list backup of archivelog all;
or
RMAN> list backup of archivelog all completed after ‘sysdate -1’;
–then:
–the beginning number is inclusive and the ending sequence number is not.
RMAN> restore validate archivelog from sequence ii until sequence XX?;
–To test for earlier backups via TAG
RMAN> restore validate database from tag=;
–RMAN configuration
select * from v$rman_configuration;
—
–Make Cold/Consistent Backup
—
run {
shutdown immedate;
startup mount pfile=/yourpath/initxxx.ora;
backup incremental level 0
database format ‘/yourpath/%d_closed_%U’
tag=PROD_COLD_L0;
shutdown;
startup pfile=/yourpath/initxxx.ora;
}
–RMAN related V$ views
select object_name from dba_objects
where object_name like ‘V$BACKUP%’
and object_type = ‘SYNONYM’;
select object_name from dba_objects
where object_name like ‘V$RMAN%’
and object_type = ‘SYNONYM’;
select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like ‘%BACKUP%’;
* V$CONTROLFILE_RECORD_SECTION
* V$COPY_CORRUPTION
* V$DATABASE
* V$DATABASE_BLOCK_CORRUPTION
* V$DATABASE_INCARNATION
* V$DATAFILE
* V$DATAFILE_COPY
* V$PROXY_ARCHIVEDLOG
* V$PROXY_ARCHIVELOG_DETAILS
* V$PROXY_ARCHIVELOG_SUMMARY
* V$PROXY_COPY_DETAILS
* V$PROXY_COPY_SUMMARY
* V$PROXY_DATAFILE
* V$OBSOLETE_BACKUP_FILES
* V$OFFLINE_RANGE
* V$SESSION_LONGOPS
* V$BACKUP
* V$BACKUP_ARCHIVELOG_DETAILS
* V$BACKUP_ARCHIVELOG_SUMMARY
* V$BACKUP_ASYNC_IO
* V$BACKUP_CONTROLFILE_DETAILS
* V$BACKUP_CONTROLFILE_SUMMARY
* V$BACKUP_COPY_DETAILS
* V$BACKUP_COPY_SUMMARY
* V$BACKUP_CORRUPTION
* V$BACKUP_DATAFILE
* V$BACKUP_DATAFILE_DETAILS
* V$BACKUP_DATAFILE_SUMMARY
* V$BACKUP_DEVICE
* V$BACKUP_FILES
* V$BACKUP_PIECE
* V$BACKUP_PIECE_DETAILS
* V$BACKUP_REDOLOG
* V$BACKUP_SET
* V$BACKUP_SET_DETAILS
* V$BACKUP_SET_SUMMARY
* V$BACKUP_SPFILE
* V$BACKUP_SPFILE_DETAILS
* V$BACKUP_SPFILE_SUMMARY
* V$BACKUP_SYNC_IO
* V$RMAN_BACKUP_JOB_DETAILS
* V$RMAN_BACKUP_SUBJOB_DETAILS
* V$RMAN_BACKUP_TYPE
* V$RMAN_CONFIGURATION
* V$RMAN_ENCRYPTION_ALGORITHMS
* V$RMAN_OUTPUT
* V$RMAN_STATUS
list backup archivelog all — lists all backupsets containing all backed up archive logs
restore validate database — verified whether the last backup was valid or not..
list archivelog logseq 91747; –if you know the archivelog sequence and you want to check if the information exists in the recovery catalog.
restore validate archivelog sequence 91747; –if you want to test if this archive log can be really used during restore.
show all; –shows your current rman configuration
list expired archivelog all; –if you want to check all the expired archivelogs.
EXPIRED Object is not found either in file system (for DISK) or in the media
manager (for sbt). Note that for a backup set to be EXPIRED, all backup
pieces in the set must be EXPIRED.
Note: EXPIRED does not mean the same as OBSOLETE.
The CROSSCHECK command does not delete any files that it is unable to find, but
updates their repository records to EXPIRED. Then, you can run DELETE EXPIRED to
remove the repository records for all expired files as well as any existing physical files
whose records show the status EXPIRED.
Scenario 8
Restoring from Production Database Backup which is present on tape into a Test Server
Prerequisites:
1)A Valid Production Database backup on tape.
2)A Recovery Catalog. (duplicate the recovery catalog if restoring into a test machine
otherwise Oracle thinks that latest backup is present in the
test server.You can copy the catalog schema by doing export
and import with different name.
)
3)The test server should have access to the above mentioned backup tape.
4)The media management library should be able to restore the backups onto the test server. Ask the Backup team to verify that the communication between the tape and the test server is correct.
Steps
1)Delete all the control files , redolog files , datafiles , archive files , init’’sid’’.ora of the test database on the test server. This is to make sure that you have enough space and you don’t end up with unwanted old datafiles in your test server directories. If you have space available, make a backup of your test database so in case you have problems with your last production backup you can atleast give the users a functioning test environment.
All directories have been deleted
No controlfile, no datafile, no initfile, no archivelogfile, no script left
2) Copy the Target Database Init.ora file into the Test Server. Change any parameters if you want to have different control file names,different memory parameters,or if different directory structure for your various ‘DEST’ Directories.
3)Edit your init.ora file and make sure that your remote_password_file parameter is set to ‘ÉXCLUSIVE’. Make sure that the database name is same as in the target database.
4)Start the database instance on the test server in the nomount stage using the pfile clause.
Startup nomount pfile=’/opt/cdb/initcdb.ora’
5)Create a spfile in your test server Instance.
Create spfile from pfile.
6) Create a duplicate of your recovery catalog that is used for the production database.You can export your original catalog and import back as different user.Ignore if you have incompiled views.
7) Make sure that the entries for the duplicate recovery catalog and your test server database exist in the tnsnames.ora file of the test database server.
8)Now open the rman executable on the test database server and connect to the duplicate recovery catalog.
Rman target /
9) connect to the duplicate recovery catalog
connect catalog rmanprod102@rcat10
10) verify that your default channel is set to type sbt_tape by using the command.
Show all
If the parameters don’t correspond to tdp0 then configure the parameters using below command
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
11)If the default channel is not of type sbt_tape , allocate a channel of this type at rman prompt.
allocate channel c1 device type sbt_tape;
12) Restore the control files.
Restore control file
13)Restore the database
Restore database
14)Mount the database so that you can recover the database
alter database mount ;
15)Recover the database
recover database ;
16)In case the recovery finished successfully due to the prescence of all valid archive logs, then just open the database normally
alter database open
17) In case the recovery was incomplete due to some archive logs missing then open the database with resetlogs.
alter database open resetlogs;
now a new reincarnation will be automatically saved in your duplicate recovery catalog.
Trouble Shooting
1)Restore failed due to error “ error from media management library” or something similar . This is due to incorrect configuration of the media management software. Contact the backup team to see whether the media management software is setup correctly. When we did the above restore, it was failing initially as the filespace parameters of the media management software were not set up correctly. when the backup team corrected them the restore was done successfully as rman could then find the files to restore.
2)we were getting connection errors to the recovery catalog due to incorrect entries in the tnsnames.ora file in the test server.make sure that proper entries exist in the tnsnames.ora file.
Scenario 9
Archive log restore
run {
allocate channel ‘dev_0’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_1’ type ‘sbt_tape’
parms PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_2’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_3’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_4’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_5’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
set archivelog destination to ‘/u01/app/oracle/reorg’;
restore archivelog from logseq 1174 until logseq 1250;
release channel d0;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
}
Scenario 10
Database Restore
run {
allocate channel ‘dev_0’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_1’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_2’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_3’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_4’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_5’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
RESTORE database;
Alter database mount;
RECOVER database;
release channel dev_0;
release channel dev_1;
release channel dev_2;
release channel dev_3;
release channel dev_4;
release channel dev_5;
release channel dev_6;
}
Scenario 11
Tablespace and Datafiles Restore
Run {
allocate channel ‘dev_0’ type ‘sbt_tape’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
allocate channel ‘dev_1’ type ‘sbt_tape’
PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
SQL ‘ALTER TABLESPACE RESTORE_TEST OFFLINE’;
RESTORE TABLESPACE RESTORE_TEST;
RECOVER TABLESPACE RESTORE_TEST;
SQL ‘ALTER TABLESPACE RESTORE_TEST ONLINE’;
}
Scenario 12
Control file and Spfile
run
{
allocate channel ‘dev_0’ type ‘sbt_tape’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’; STARTUP FORCE NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
}
Scenario 13
restoring database from production into test server ..sample real life exercise
–Restore test of sampledb Production environment to Pre-Production
a)first i am creating a new user in my recovery catalog.i need this new user because i am going to duplicate my production recovery catalog user into this new user.
the tablespaces should already be created.
Create user rmansampledb identified by xxxx
Default tablespace TS_RMANsampledb
Quota unlimited on TS_RMANsampledb;
Grant recovery_catalog_owner to rmansampledb;
b)now i will export my existing production catalog user using below export parameters
Create clone of RMAN recovery catalog on ‘catalogue’ (rcat10)
With export data :
FILE=expsample.dmp
LOG=expsample.log
BUFFER=512000
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
COMPRESS=Y
OWNER=RMANPROD102
–so once i export my production recovery catalog user using above export parameters i will import them into my new user.you should know how to export a user/schema using above parameters.
–read the export functionality in oracle.also read about the import functionality in oracle if you dont know how to import a export file into oracle.
cat imp.par
FILE=expsample.dmp
LOG=impsample.log
BUFFER=512000
ROWS=Y
INDEXES=Y
GRANTS=Y
COMMIT=Y
SHOW=N
IGNORE=N
FROMUSER=RMANPROD102
TOUSER=RMANsampledb
–once you import there could be some errors/Some invalid objects but ignore them.
–we are using tdpo as the media manager.if you are using a different media manager then you need to speak to your backup team to find out about
–the configuration information about your media manager.
II/
Change the TDPO configuration so that we point to Live workspace on Tivoli
–i am going into the directory to see the configuration file of my media manager.
Cd /opt/tivoli/tsm/client/oracle/bin64
Cat tdpo.opt
***************************************************************************
* IBM Tivoli Storage Manager for Databases
* Data Protection for Oracle
*
* Sample tdpo.opt for the Solaris 64bit Data Protection for Oracle
*********************************************************************
DSMI_ORC_CONFIG /opt/tivoli/tsm/client/api/bin64/dsm.opt
DSMI_LOG /var/log/tsm/tdpo
TDPO_FS sampserver01_orc
TDPO_NODE sampserver01-ora
TDPO_OWNER ora10
TDPO_PSWDPATH /opt/tivoli/tsm/client/oracle/bin64
*TDPO_DATE_FMT 1
*TDPO_NUM_FMT 1
*TDPO_TIME_FMT 1
*TDPO_MGMT_CLASS_2 mgmtclass2
*TDPO_MGMT_CLASS_3 mgmtclass3
*TDPO_MGMT_CLASS_4 mgmtclass4
–i want to see my current configuration parameters in the current unix environment.
Tdpoconf showenv
–below is the output i see when i execute the above command
Data Protection for Oracle Information
Version: 5
Release: 3
Level: 3
Sublevel: 0
Platform: 64bit TDP Oracle SUN
Tivoli Storage Manager Server Information
Server Name: sampserver01-ORA
Server Address: sampserver01.ORA.TSM.UNIX.rbi.DE
Server Type: AIX-RS/6000
Server Port: 1500
Communication Method: TCP/IP
Session Information
Owner Name: ora10
Node Name: sampserver01-ora
Node Type: TDP Oracle SUN
DSMI_DIR: /opt/tivoli/tsm/client/api/bin64
DSMI_ORC_CONFIG: /opt/tivoli/tsm/client/api/bin64/dsm.opt
TDPO_OPTFILE: /opt/tivoli/tsm/client/oracle/bin64/tdpo.opt
Password Directory: /opt/tivoli/tsm/client/oracle/bin64
Compression: FALSE
License Information: License file exists and contains valid license data.
–Now i go to the test server and start a sqlplus session after logging in as the oracle user.
sqlplus /nolog
–it then Starts idle instance
Copy initsampledb.ora file and adapt it (controlfile name changed to dup_control01.ctl …)
sampletest01*sampledb-/opt/oracle/product/10.2.0/network/admin :->sqlplus
SQL*Plus: Release 10.2.0.2.0 – Production on Tue Oct 9 14:59:53 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 2126160 bytes
Variable Size 161320624 bytes
Database Buffers 54525952 bytes
Redo Buffers 4325376 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64
bit Production
With the Partitioning, OLAP and Data Mining options
sampletest01*sampledb-/opt/oracle/product/10.2.0/network/admin :->
sampletest01*sampledb-/opt/oracle/product/10.2.0/network/admin :->
–now connect to rman and Restore database
–by this time your storage team should have changed the configuration so the production tapes are nbow connected to the test server so you can restore the
–database from the production tapes to the test server.
–the rcat10.rbi.de should be a tnsnames entry in the tnsnames.ora file of your test server.
sampletest01*sampledb-/opt/oracle/product/10.2.0/network/admin :->rman target=/ catalog=rmansampledb@rcat10.rbi.de
Recovery Manager: Release 10.2.0.2.0 – Production on Tue Oct 9 15:00:48 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: sampledb (not mounted)
recovery catalog database Password:
connected to recovery catalog database
RMAN>
RMAN>
RMAN> restore controlfile;
Starting restore at 09-OCT-07
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=83 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=158 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=82 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: reading from backup piece df_sampledb_l5iu0q7l_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=df_sampledb_l5iu0q7l_1_1 tag=LV0
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:05
output filename=/global/sampledb-db/oracle/sampledb/data1/dup_control01.ctl
output filename=/global/sampledb-db/oracle/sampledb/data2/dup_control02.ctl
output filename=/global/sampledb-db/oracle/sampledb/data3/dup_control03.ctl
Finished restore at 09-OCT-07
RMAN> alter database mount;
database mounted
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
released channel: ORA_SBT_TAPE_3
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 09-OCT-07
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=158 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=156 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=84 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Data Protection for Oracle: version 5.3.3.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /global/sampledb-db/oracle/sampledb/data1/sampledb_system01.d
bf
restoring datafile 00006 to /global/sampledb-db/oracle/sampledb/data1/sampledb_users01.db
f
restoring datafile 00008 to /global/sampledb-db/oracle/sampledb/data3/sampledb_index01.db
f
channel ORA_SBT_TAPE_1: reading from backup piece df_sampledb_l2iu0q58_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=df_sampledb_l2iu0q58_1_1 tag=LV0
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:26
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /global/sampledb-db/oracle/sampledb/data3/sampledb_indx01.dbf
restoring datafile 00007 to /global/sampledb-db/oracle/sampledb/data3/sampledb_data01.dbf
restoring datafile 00009 to /global/sampledb-db/oracle/sampledb/data3/sampledb_datalob01.
dbf
channel ORA_SBT_TAPE_1: reading from backup piece df_sampledb_l3iu0q58_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=df_sampledb_l3iu0q58_1_1 tag=LV0
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:26
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /global/sampledb-db/oracle/sampledb/data1/sampledb_undo01.dbf
restoring datafile 00003 to /global/sampledb-db/oracle/sampledb/data1/sampledb_sysaux01.d
bf
restoring datafile 00005 to /global/sampledb-db/oracle/sampledb/data1/sampledb_tools01.db
f
channel ORA_SBT_TAPE_1: reading from backup piece df_sampledb_l4iu0q59_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=df_sampledb_l4iu0q59_1_1 tag=LV0
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:36
Finished restore at 09-OCT-07
RMAN> recover database;
Starting recover at 09-OCT-07
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_DISK_1
starting media recovery
channel ORA_SBT_TAPE_1: starting archive log restore to default destination
channel ORA_SBT_TAPE_1: restoring archive log
archive log thread=1 sequence=2225
channel ORA_SBT_TAPE_1: reading from backup piece l9iu0q8g_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=l9iu0q8g_1_1 tag=TAG20071008T214549
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:08
archive log filename=/global/sampledb-db/oracle/sampledb/arch/sampledb_2225_1_613846982.a
rc thread=1 sequence=2225
channel ORA_SBT_TAPE_1: starting archive log restore to default destination
channel ORA_SBT_TAPE_1: restoring archive log
archive log thread=1 sequence=2226
channel ORA_SBT_TAPE_1: reading from backup piece laiu0q97_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=laiu0q97_1_1 tag=TAG20071008T214615
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
archive log filename=/global/sampledb-db/oracle/sampledb/arch/sampledb_2226_1_613846982.a
rc thread=1 sequence=2226
unable to find archive log
archive log thread=1 sequence=2227
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/09/2007 15:07:52
RMAN-06054: media recovery requesting unknown log: thread 1 seq 2227 lowscn 2200
4678738
–ignore the above error.
RMAN> quit
–as we got an error we will open the database with resetlogs. normally you should not get an error.but somehow we were missing an archivelog file in our production database backup
–and hence we were not able to completely restore.
–could be that the backup was done but there was as well some archivelog not backed up duting that point in time.anyway for this exercise as this is a test database you can ignore it.
–whenever you get a error it means you did an incompete restore.in that case you have to restore the database with resetlogs so the datasbase will create new redo logs.
–also at the beginning of this exercise we deleted all the files.so we should open the database with resetlogs as oracle will then create the redo log files.
SQLPLUS / as sysdba
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
–now Recreate the spfile
