Thursday, August 24, 2006
Backing up an Oracle database with RMAN
Doing is Seeing, Seeing is Believing::
Backing Up an Oracle Database with RMAN
Hari Kaimal
Backing Up an Oracle Database with RMAN
Hari Kaimal
Today I achieved one of my long-standing dreams: I took my first back-up pieces with RMAN. It was really simple, provided you commit a few mistakes first and then consult all the relevant documentation, and finally get it right. The key concepts, as well as my own experiences with this wonderful (and ancient) tool are listed out.
First of all, here are the concepts.
First of all, here are the concepts.
- RMAN prefers that the database is running in ARCHIVELOG mode.
- However, you can run RMAN in NOARCHIVELOG mode also. (There are a few restrictions attached to this sort of cold backup, more on that later).
- If you try to do a real hot backup with RMAN, it won't even try. No matter how tempting the errors you receive, you won't be able to do a hot backup.
- You need to be running the database in restricted mode. The correct mode is very important. Unless the database is available in the correct mode, RMAN won't succeed.
- The correct mode in which you should run the database is the STARTUP MOUNT.
- You can allocate only one channel in Standard Edition.
- There should ideally be a catalog database, but this is not mandatory. You can perform an RMAN backup from the same local machine directly to tape, or maybe you can use the disk and later copy it out.
- RMAN allows tremendous flexibility, but you don't really need to know most of these to successfully use it.
- I found the following sites helpful::
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmbackp.htm#435660 - You may also consult this resource from asktom, which addresses the special case of a database running in NOARCHIVELOG mode::
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:40370408605260 - And this general write-up on RMAN from asktom::
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7275792247455 - Rajendra Gutta has this general script based backup strategy (not entirely concentrating on RMAN)::
http://www.informit.com/articles/article.asp?p=30348&seqNum=2&rl=1
Gutta has also written a book on this topic. - The best thing you can do is, try RMAN routines against a small test database and get up to speed with the tool. Also try to rebuild a database from the backup sets generated by RMAN. I haven't yet tried this, but hopefully it won't be long. I shall post back the experience when I'm done.
The RMAN recovery procedure is frighteningly simple. It can be summarized (for a NOARCHIVELOG database) using no catalog database as::
- Take the database offline (should be mounted but not opened). for this perform the following steps::
- Shutdown with the immediate option.
- Startup with the mount option.
- Invoke the RMAN executable from the command line.
- In the RMAN prompt, connect to the target database.
- Ensure that the destination file system (folder in Windows) has sufficient space to accommodate the data set. Use the following script to backup the database::
RMAN> run { allocate channel c1 type disk
2> maxpiecesize=200M format='F:\rman\d1\df_%U.bak';
3> backup tablespace users;
4> } - RMAN will allocate the channel (only one channel allowed in Standard Edition; trying to allocate multiple channels in SE will get you RMAN-06181: multiple channels requires Enterprise Edition) and perform the backup (of whatever you specified: database, datafile or tablespace) to the specified location. Needless to say, it will be almost as big as there is data in the datafile/database/tablespace. You can see the allocated bytes by querying the V$DATAFILE view. However, to easily see the actual usage (so that you can carefully choose the backup destination), you can use the EM console.
- In the screen dump that follows, you can see how quick the process is (very nearly as quick as a filesystem copy). The files are respectively 128 MB (tablespace users), 122 MB (tablespace system), and 300 + 229 MB (database). Compared to an export (dump) file, this also does not waste space. So we can enumerate the advantages of RMAN in the next section.
Advantages
You might come across so much blah-blah on RMAN from ever so many DBAs and sysadmins who are just as exuberant with the new discovery. However, as the official recovery tool recommended by Oracle Corporation, RMAN has its place among the forefront of recovery and backup tools for the Oracle database. Personally I feel these are the advantages::
You might come across so much blah-blah on RMAN from ever so many DBAs and sysadmins who are just as exuberant with the new discovery. However, as the official recovery tool recommended by Oracle Corporation, RMAN has its place among the forefront of recovery and backup tools for the Oracle database. Personally I feel these are the advantages::
- Direct backup to tape is possible.
- Flexibility to first backup to disk and then use a DVD writer.
- Speed. It's the second quickest after OS file copy. In the examples shown, the speed was close to 4.5 MB per sec. The whole database was backed up in 2'01" flat!
- Flexibility to use multiple channels (in Enterprise version only). Even without, RMAN is very quick.
- Very clean procedure, no nagging details such as in export.
There are some disadvantages also, such as
- The RMAN procedure cannot be used on a live database.
- It cannot get any smaller than a datafile. No option to backup a table.
- Does not check for errors, as export does.
RMAN> run { allocate channel c1 type disk
2> maxpiecesize=200M format='F:\rman\d1\df_%U.bak';
3> backup tablespace users;
4> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 24-AUG-06
channel c1: starting full datafile backup set
channel c1: specifying datafiles) in backup set
input datafile fno=00007 name=G:\ORACLE\ORADATA\ZEINAB\USERS01.DBF
channel c1: starting piece 1 at 24-AUG-06
channel c1: finished piece 1 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_03HRIOS7_1_1.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 24-AUG-06
released channel: c1
RMAN> run { allocate channel c1 type disk
2> maxpiecesize=300M format='F:\rman\d1\df_%U.bak';
3> backup tablespace system;
4> }
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 24-AUG-06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current controlfile in backup set
input datafile fno=00001 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM01.DBF
input datafile fno=00002 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM02.DBF
input datafile fno=00003 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM03.DBF
channel c1: starting piece 1 at 24-AUG-06
channel c1: finished piece 1 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_04HRIOVM_1_1.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:00:35
Finished backup at 24-AUG-06
released channel: c1
RMAN> run { allocate channel c1 type disk
2> maxpiecesize=300M format='F:\rman\d1\df_%U.bak';
3> backup database;
4> }
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 24-AUG-06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current controlfile in backup set
input datafile fno=00004 name=G:\ORACLE\ORADATA\ZEINAB\UNDOTBS01.DBF
input datafile fno=00007 name=G:\ORACLE\ORADATA\ZEINAB\USERS01.DBF
input datafile fno=00001 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM01.DBF
input datafile fno=00002 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM02.DBF
input datafile fno=00003 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM03.DBF
input datafile fno=00008 name=G:\ORACLE\ORADATA\ZEINAB\SCIM.DBF
input datafile fno=00009 name=G:\ORACLE\ORADATA\ZEINAB\SSANET.DBF
input datafile fno=00005 name=G:\ORACLE\ORADATA\ZEINAB\INDX01.DBF
input datafile fno=00006 name=G:\ORACLE\ORADATA\ZEINAB\TOOLS01.DBF
channel c1: starting piece 1 at 24-AUG-06
channel c1: finished piece 1 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_05HRIPHE_1_1.BAK comment=NONE
channel c1: starting piece 2 at 24-AUG-06
channel c1: finished piece 2 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_05HRIPHE_2_1.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:02:01
Finished backup at 24-AUG-06
released channel: c1
RMAN>
2> maxpiecesize=200M format='F:\rman\d1\df_%U.bak';
3> backup tablespace users;
4> }
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 24-AUG-06
channel c1: starting full datafile backup set
channel c1: specifying datafiles) in backup set
input datafile fno=00007 name=G:\ORACLE\ORADATA\ZEINAB\USERS01.DBF
channel c1: starting piece 1 at 24-AUG-06
channel c1: finished piece 1 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_03HRIOS7_1_1.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 24-AUG-06
released channel: c1
RMAN> run { allocate channel c1 type disk
2> maxpiecesize=300M format='F:\rman\d1\df_%U.bak';
3> backup tablespace system;
4> }
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 24-AUG-06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current controlfile in backup set
input datafile fno=00001 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM01.DBF
input datafile fno=00002 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM02.DBF
input datafile fno=00003 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM03.DBF
channel c1: starting piece 1 at 24-AUG-06
channel c1: finished piece 1 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_04HRIOVM_1_1.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:00:35
Finished backup at 24-AUG-06
released channel: c1
RMAN> run { allocate channel c1 type disk
2> maxpiecesize=300M format='F:\rman\d1\df_%U.bak';
3> backup database;
4> }
allocated channel: c1
channel c1: sid=10 devtype=DISK
Starting backup at 24-AUG-06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current controlfile in backup set
input datafile fno=00004 name=G:\ORACLE\ORADATA\ZEINAB\UNDOTBS01.DBF
input datafile fno=00007 name=G:\ORACLE\ORADATA\ZEINAB\USERS01.DBF
input datafile fno=00001 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM01.DBF
input datafile fno=00002 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM02.DBF
input datafile fno=00003 name=G:\ORACLE\ORADATA\ZEINAB\SYSTEM03.DBF
input datafile fno=00008 name=G:\ORACLE\ORADATA\ZEINAB\SCIM.DBF
input datafile fno=00009 name=G:\ORACLE\ORADATA\ZEINAB\SSANET.DBF
input datafile fno=00005 name=G:\ORACLE\ORADATA\ZEINAB\INDX01.DBF
input datafile fno=00006 name=G:\ORACLE\ORADATA\ZEINAB\TOOLS01.DBF
channel c1: starting piece 1 at 24-AUG-06
channel c1: finished piece 1 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_05HRIPHE_1_1.BAK comment=NONE
channel c1: starting piece 2 at 24-AUG-06
channel c1: finished piece 2 at 24-AUG-06
piece handle=F:\RMAN\D1\DF_05HRIPHE_2_1.BAK comment=NONE
channel c1: backup set complete, elapsed time: 00:02:01
Finished backup at 24-AUG-06
released channel: c1
RMAN>
We're done. At this point I understand that we're not really through with this topic, as we have not yet discussed how we can potentially make use of the backup set we have just made. We have the following backups from our database::
- USERS tablespace
- SYSTEM tablespace
- Full database backup
In a future weblog, we shall see how to make use of RMAN in recovering a database. The case is more complicated compared to backup, as there are more possibilities for a system going down... and only one desirable outcome for the recovery process.
Until then.
Hari Kaimal
Until then.
Hari Kaimal
harikaimal@gmail.com
