8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Recovery Manager (RMAN) in Oracle 8i
Recovery manager is a platform non-specific utility for coordinating you backup and restoration procedures across multiple servers. In my opinion it's value is limited if you only have on or two instances, but it comes into it's own where large numbers of instances on multiple platforms are used. The reporting features alone mean that you should never find yourself in a position where your data is in danger due to failed backups.
The functionality of RMAN is too diverse to be covered in this article so I shall focus on the basic backup and recovery functionality.
- Create Recovery Catalog
- Register Database
- Cold Backup
- Hot Backup
- Restore & Recover The Whole Database
- Restore & Recover A Subset Of The Database
- Incomplete Recovery
- Lists And Reports
Related articles.
- Recovery Manager in Oracle 9i
- Recovery Manager (RMAN) Enhancements In Oracle 9i
- Recovery Manager (RMAN) Enhancements in Oracle Database 10g
- Recovery Manager (RMAN) Enhancements in Oracle Database 11g Release 1
- Data Recovery Advisor in Oracle Database 11g Release 1
- Incrementally Updated Backups : Rolling Forward Image Copies Using RMAN
Create Recovery Catalog
First create a user to hold the recovery catalog.
CONNECT sys/password@TSH1 -- Create tablepsace to hold repository CREATE TABLESPACE "TOOLS" DATAFILE 'E:\ORACLE\ORADATA\DDBA1\TOOLS01.DBF' SIZE 10M AUTOEXTEND ON NEXT 1024K EXTENT MANAGEMENT LOCAL; -- Create rman schema owner CREATE USER rman IDENTIFIED BY rman TEMPORARY TABLESPACE temp DEFAULT TABLESPACE tools QUOTA UNLIMITED ON tools; GRANT connect, resource, recovery_catalog_owner TO rman;
Then create the recovery catalog.
c:> rman catalog rman/rman@tsh1 Recovery Manager: Release 8.1.7.0.0 - Production RMAN-06008: connected to recovery catalog database RMAN-06428: recovery catalog is not installed RMAN> create catalog tablespace tools; RMAN-06431: recovery catalog created RMAN> exit Recovery Manager complete. C:>
Register Database
Each database to be backed up by RMAN must be registered.
C:>rman target sys/password@tsh1 rcvcat rman/rman@dba1 msglog 'C:OracleBackupTSH1TSH1_Daily_Backup.log' Recovery Manager: Release 8.1.7.0.0 - Production RMAN-06005: connected to target database: TSH1 (DBID=955315395) RMAN-06008: connected to recovery catalog database RMAN> register database; RMAN-03022: compiling command: register RMAN-03023: executing command: register RMAN-08006: database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-08002: starting full resync of recovery catalog RMAN-08004: full resync complete RMAN>
Existing user-created backups can be added to the catalog.
catalog datafilecopy 'C:\Oracle\Oradata\TSH1.dbf'; catalog archivelog 'log1', 'log2', 'log3', ... 'logN';
Cold Backup
This RMAN script starts by doing a a clean mount of the database. It then backs up the datafiles, controlfile and archivelogs, with old archive logs deleted in the process. Finally the database is opened.
replace script 'TSH1_daily_backup' { # make sure database is shutdown cleanly shutdown immediate; startup force dba pfile=c:\Oracle\Admin\TSH1\pfile\init.ora; shutdown immediate; #Mount the database and start backup startup mount pfile=c:\Oracle\Admin\TSH1\pfile\init.ora; # Backup datafile, controlfile and archivelogs allocate channel ch1 type disk format 'C:\Oracle\Backup\TSH1\%d_DB_%u_%s_%p'; backup database include current controlfile tag = 'TSH1_daily_backup'; release channel ch1; # Open the database alter database open; # Archive all logfiles including current sql 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # Backup outdated archlogs and delete them allocate channel ch1 type disk format 'C:\Oracle\Backup\TSH1\%d_ARCH_%u_%s_%p'; backup archivelog until time 'Sysdate-2' all delete input; release channel ch1; # Backup remaining archlogs allocate channel ch1 type disk format 'C:\Oracle\Backup\TSH1\%d_ARCH_%u_%s_%p'; backup archivelog all; release channel ch1; }
The file can be loaded as a stored script and run using the following commands.
RMAN> @c:\Oracle\Backup\TSH1_daily_backup.txt RMAN> run {execute script TSH1_daily_backup; }
The RMAN output can be a bit unnerving at first. You should expect something like the following.
RMAN> run {execute script TSH1_daily_backup; } RMAN-03021: executing script: TSH1_daily_backup RMAN-03022: compiling command: shutdown RMAN-06405: database closed RMAN-06404: database dismounted RMAN-06402: Oracle instance shut down RMAN-03022: compiling command: startup RMAN-06196: Oracle instance started RMAN-06199: database mounted RMAN-06400: database opened Total System Global Area 13375516 bytes Fixed Size 75804 bytes Variable Size 12402688 bytes Database Buffers 819200 bytes Redo Buffers 77824 bytes RMAN-03022: compiling command: shutdown RMAN-06405: database closed RMAN-06404: database dismounted RMAN-06402: Oracle instance shut down RMAN-03022: compiling command: startup RMAN-06193: connected to target database (not started) RMAN-06196: Oracle instance started RMAN-06199: database mounted Total System Global Area 13375516 bytes Fixed Size 75804 bytes Variable Size 12402688 bytes Database Buffers 819200 bytes Redo Buffers 77824 bytes RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: ch1 RMAN-08500: channel ch1: sid=14 devtype=DISK RMAN-03022: compiling command: backup RMAN-03023: executing command: backup RMAN-08008: channel ch1: starting full datafile backupset RMAN-08502: set_count=51 set_stamp=437320255 creation_time=09-AUG-01 RMAN-08010: channel ch1: specifying datafile(s) in backupset RMAN-08522: input datafile fno=00001 name=C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF RMAN-08011: including current controlfile in backupset RMAN-08522: input datafile fno=00009 name=C:\ORACLE\ORADATA\TSH1\DES601.DBF RMAN-08522: input datafile fno=00002 name=C:\ORACLE\ORADATA\TSH1\RBS01.DBF RMAN-08522: input datafile fno=00008 name=C:\ORACLE\ORADATA\TSH1\OEM_REPOSITORY.ORA RMAN-08522: input datafile fno=00003 name=C:\ORACLE\ORADATA\TSH1\USERS01.DBF RMAN-08522: input datafile fno=00004 name=C:\ORACLE\ORADATA\TSH1\TEMP01.DBF RMAN-08522: input datafile fno=00006 name=C:\ORACLE\ORADATA\TSH1\INDX01.DBF RMAN-08522: input datafile fno=00007 name=C:\ORACLE\ORADATA\TSH1\DR01.DBF RMAN-08522: input datafile fno=00005 name=C:\ORACLE\ORADATA\TSH1\TOOLS01.DBF RMAN-08013: channel ch1: piece 1 created RMAN-08503: piece handle=C:\ORACLE\BACKUP\TSH1\TSH1_DB_1JD11UHV_51_1 comment=NONE RMAN-08525: backup set complete, elapsed time: 00:05:52 RMAN-03023: executing command: partial resync RMAN-08003: starting partial resync of recovery catalog RMAN-08005: partial resync complete RMAN-03022: compiling command: release RMAN-03023: executing command: release RMAN-08031: released channel: ch1 RMAN-03022: compiling command: alter db RMAN-06400: database opened RMAN-03022: compiling command: sql RMAN-06162: sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT RMAN-03023: executing command: sql RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: ch1 RMAN-08500: channel ch1: sid=14 devtype=DISK RMAN-03022: compiling command: backup RMAN-03025: performing implicit partial resync of recovery catalog RMAN-03023: executing command: partial resync RMAN-08003: starting partial resync of recovery catalog RMAN-08005: partial resync complete RMAN-03023: executing command: backup RMAN-08009: channel ch1: starting archivelog backupset RMAN-08502: set_count=52 set_stamp=437320626 creation_time=09-AUG-01 RMAN-08014: channel ch1: specifying archivelog(s) in backup set RMAN-08504: input archivelog thread=1 sequence=226 recid=11 stamp=437307841 RMAN-08504: input archivelog thread=1 sequence=227 recid=12 stamp=437309722 RMAN-08504: input archivelog thread=1 sequence=228 recid=13 stamp=437316806 RMAN-08504: input archivelog thread=1 sequence=229 recid=14 stamp=437317665 RMAN-08504: input archivelog thread=1 sequence=230 recid=15 stamp=437319111 RMAN-08504: input archivelog thread=1 sequence=231 recid=16 stamp=437320622 RMAN-08013: channel ch1: piece 1 created RMAN-08503: piece handle=C:\ORACLE\BACKUP\TSH1\TSH1_ARCH_1KD11UTI_52_1 comment=NONE RMAN-08525: backup set complete, elapsed time: 00:00:04 RMAN-03023: executing command: partial resync RMAN-08003: starting partial resync of recovery catalog RMAN-08005: partial resync complete RMAN-03022: compiling command: release RMAN-03023: executing command: release RMAN-08031: released channel: ch1 RMAN> exit Recovery Manager complete.
The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but a full resync should be scheduled at regular intervals.
resync catalog;
Hot Backup
Hot backups using RMAN are very simple. There is no need to alter the tablespace or database mode.
run { allocate channel ch1 type disk format 'd:\oracle\backup%d_DB_%u_%s_%p'; backup database; backup archivelog all; release channel ch1; }
Restore & Recover The Whole Database
Recovering from a media failure is as simple.
run { startup mount pfile=c:\Oracle\Admin\TSH1\pfile\init.ora; allocate channel ch1 type disk; restore database; recover database; release channel ch1; }
This will result in all datafiles being restored then recovered. RMAN will apply archive logs as necessary until the recovery is complete. The sort of results you should expect are shown below.
Recovery Manager: Release 8.1.7.0.0 - Production RMAN-06005: connected to target database: TSH1 (DBID=955315395) RMAN-06008: connected to recovery catalog database RMAN> run { 2> startup mount pfile=c:\Oracle\Admin\TSH1\pfile\init.ora; 3> 4> allocate channel ch1 type disk; 5> 6> restore database; 7> recover database; 8> 9> release channel ch1; 10>} RMAN-03022: compiling command: startup RMAN-06193: connected to target database (not started) RMAN-06196: Oracle instance started RMAN-06199: database mounted Total System Global Area 13375516 bytes Fixed Size 75804 bytes Variable Size 12402688 bytes Database Buffers 819200 bytes Redo Buffers 77824 bytes RMAN-03022: compiling command: allocate RMAN-03023: executing command: allocate RMAN-08030: allocated channel: ch1 RMAN-08500: channel ch1: sid=14 devtype=DISK RMAN-03022: compiling command: restore RMAN-03022: compiling command: IRESTORE RMAN-03023: executing command: IRESTORE RMAN-08016: channel ch1: starting datafile backupset restore RMAN-08502: set_count=51 set_stamp=437320255 creation_time=09-AUG-01 RMAN-08089: channel ch1: specifying datafile(s) to restore from backup set RMAN-08523: restoring datafile 00001 to C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF RMAN-08523: restoring datafile 00002 to C:\ORACLE\ORADATA\TSH1\RBS01.DBF RMAN-08523: restoring datafile 00003 to C:\ORACLE\ORADATA\TSH1\USERS01.DBF RMAN-08523: restoring datafile 00004 to C:\ORACLE\ORADATA\TSH1\TEMP01.DBF RMAN-08523: restoring datafile 00005 to C:\ORACLE\ORADATA\TSH1\TOOLS01.DBF RMAN-08523: restoring datafile 00006 to C:\ORACLE\ORADATA\TSH1\INDX01.DBF RMAN-08523: restoring datafile 00007 to C:\ORACLE\ORADATA\TSH1\DR01.DBF RMAN-08523: restoring datafile 00008 to C:\ORACLE\ORADATA\TSH1\OEM_REPOSITORY.ORA RMAN-08523: restoring datafile 00009 to C:\ORACLE\ORADATA\TSH1\DES601.DBF RMAN-08023: channel ch1: restored backup piece 1 RMAN-08511: piece handle=C:\ORACLE\BACKUP\TSH1\TSH1_DB_1JD11UHV_51_1 tag=TSH1_DAILY_BACKUP params=NULL RMAN-08024: channel ch1: restore complete RMAN-03023: executing command: partial resync RMAN-08003: starting partial resync of recovery catalog RMAN-08005: partial resync complete RMAN-03022: compiling command: recover RMAN-03022: compiling command: recover(1) RMAN-03022: compiling command: recover(2) RMAN-03022: compiling command: recover(3) RMAN-03023: executing command: recover(3) RMAN-08054: starting media recovery RMAN-08055: media recovery complete RMAN-03022: compiling command: recover(4) RMAN-03022: compiling command: release RMAN-03023: executing command: release RMAN-08031: released channel: ch1 RMAN> exit Recovery Manager complete.
Once this process us complete the database can be opened using the ALTER DATABASE OPEN
command.
Restore & Recover A Subset Of The Database
A subset of the database can be restored in a similar fashion.
run { sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE'; restore tablespace users; recover tablespace users; sql 'ALTER TABLESPACE users ONLINE'; }
Incomplete Recovery
As you would expect, RMAN allows incomplete recovery to a specified time, SCN or sequence number.
run { set until time 'Nov 15 2000 09:00:00'; # set until scn 1000; # alternatively, you can specify SCN # set until sequence 9923; # alternatively, you can specify log sequence number restore database; recover database; } alter database open resetlogs;
The incomplete recovery requires the database to be opened using the RESETLOGS
option.
Lists And Reports
RMAN has extensive listing and reporting functionality allowing you to monitor you backups and maintain the recovery catalog. Here are a few useful commands.
# Show all backup details list backup; # Show items that need 7 days worth of # archivelogs to recover completely report need backup days = 7 database; # Show/Delete items not needed for recovery report obsolete; delete obsolete; # Show/Delete items not needed for point-in-time # recovery within the last week report obsolete recovery window of 7 days; delete obsolete recovery window of 7 days; # Show/Delete items with more than 2 newer copies available report obsolete redundancy = 2 device type disk; delete obsolete redundancy = 2 device type disk; # Show datafiles that connot currently be recovered report unrecoverable database; report unrecoverable tablespace 'USERS';
It's worth spending some time looking at all the reporting capabilities whilst deciding whether you should switch from shell scripting to RMAN. It might just influence your decision.
For more information see:
- Recovery Manager (RMAN) Enhancements In Oracle9i
- Oracle9i Recovery Manager (RMAN)
- RMAN Enhancements in Oracle Database 10g
- RMAN Enhancements in Oracle Database 11g
- Incrementally Updated Backups : Rolling Forward Image Copies Using RMAN
- Oracle8i Backup and Recovery Guide
Hope this helps. Regards Tim...