Oracle Database 11g RMAN BACKUP & RESTORE STEPS

RMAN BACKUP & RESTORE STEPS Contents Overview 2 1. View Current RMAN Configuration 2 2. Change Few RMAN Configuration Parameters 3 3. Backup Oracle Database 4 4. Assign Backup TAG Name for Quick Identification 5 5. Change Oracle RMAN Backup File Name Format 6 6. Compress a RMAN Backup 7 7. View all RMAN Backups 8 8. RMAN Restore Operations 9 Overview Using Oracle RMAN, you can take a hot backup for your database, which will take a consistent backup even when your DB is up and running. RMAN executable will be in ORACLE_HOME/bin location. RMAN provides you a way to backup, restore and recover oracle database. RMAN is server managed backup and recovery. RMAN works like sqlplus. Just like sqlplus is the client program and the oracle database is the server program Similarly RMAN is the client program and the oracle database is the server program . To connect to the db using RMAN ,First we have to export ORACLE_SID= cd $ORACLE_HOME/bin $rman target / RMAN > Note: Just like we give sqlplus sys as sysdba , here we don’t have to give sys as sysdba because RMAN can connect only as sys user) 1. View Current RMAN Configuration Before we take the backup, we have to configure certain RMAN parameters. For example, how long you want to retain the RMAN backup, etc. Before we modify any configuration, execute the following command to view all current RMAN configuration settings. To connect to RMAN, do the following from command line. This will take you to RMAN> command prompt, from here you can execute all RMAN commands. $ rman target / Recovery Manager: Release 10.2.0.3.0 - Production on Sat Aug 10 11:21:29 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: DEVDB (DBID=821773) RMAN> To view current RMAN configurations, execute “show all”. RMAN> SHOW ALL; using target database control file instead of recovery catalog RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/backup/rman/ctl_%F"; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT "/backup/rman/full_%u_%s_%p" MAXPIECESIZE 2048 M; CONFIGURE MAXSETSIZE TO UNLIMITED; CONFIGURE ENCRYPTION FOR DATABASE OFF; CONFIGURE ENCRYPTION ALGORITHM 'AES128'; CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_devdb.f'; # default As you see above, it displays various RMAN parameters and their current values. 2. Change Few RMAN Configuration Parameters Location: One of the important configuration parameters to set will be, where you want to save the RMAN backup. In the following example, I’m settting the RMAN backup loacation as “/backup/rman/” RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/full_%u_%s_%p'; Retention Period: Next, you should specify how long you want to retain the backup for. When RMAN takes a backup, it automatically deletes all the old backups that are older than the retention period. In the following example, I’m setting the retention period as 7 days, which will keep the DB backup for a week. RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; Verify that the above two changes are done. RMAN> SHOW ALL; .. CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/full_%u_%s_%p'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; Clear a Parameter: If you want to clear a parameter and set its value to default, use CLEAR at the end of the configuration as shown below. RMAN> CONFIGURE RETENTION POLICY CLEAR; In this example, since we cleared the retention policy’s value, it was set to the default value, which is 1. So, the retention policy is set to 1 day as shown below. RMAN> SHOW ALL; CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default 3. Backup Oracle Database Make sure the directory mentioned in the CHANNEK DEVICE TYPE DISK FORMAT is created. i.e /backup/rman/ $ mkdir -p /backup/rman Currently this directory is empty. We’ll see what this has after the backup is taken. $ ls -l /backup/rman total 0 We can take a backup using image copy or in backup set. It is strongly recommended to use RMAN backup sets to backup the database. RMAN stores the backup in backup sets, which are nothing but whole bunch of files which contains the backed-up data. Only RMAN understands the format of these files. So, if you backup an Oracle DB using RMAN, only RMAN knows how to read the backup and restore it. Typically we’ll use “BACKUP AS BACKUPSET” to backup a database. So, to take a full backup of the database without the archive logs, do the following. RMAN> BACKUP AS BACKUPSET DATABASE To take a full backup of the database with the archive logs, do the following: RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG; You can also take a backup of only a specific table space. The following example takes backup of only PRD01 tablespace. RMAN> BACKUP AS BACKUPSET TABLESPACE PRD01; The RMAN backup output will be something similar to the following: RMAN> BACKUP AS BACKUPSET DATABASE Starting backup at 10-AUG-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=193 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=192 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00025 name=/u03/oradata/devdb/devuser07.dbf input datafile fno=00003 name=/u02/oradata/devdb/temp01.dbf channel ORA_DISK_1: starting piece 1 at 10-AUG-13 channel ORA_DISK_2: starting full datafile backupset channel ORA_DISK_2: specifying datafile(s) in backupset input datafile fno=00008 name=/u03/oradata/devdb/devusers05.dbf channel ORA_DISK_2: starting piece 1 at 10-AUG-13 ... .. piece handle=/backup/rman/full_4dogpd0u_4237_1 tag=TAG20130808T114846 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 10-AUG-13 ... Starting Control File and SPFILE Autobackup at 10-AUG-13 piece handle=/backup/rman/ctl_c-758818131-20130808-00 comment=NONE Finished Control File and SPFILE Autobackup at 10-AUG-13 Once the backup is completed, do an ls on the /backup/rman directory, you’ll now see RMAN backup files. $ ls -l /backup/rman total 14588 -rw-r----- 1 oracle dba 14585856 Aug 8 11:48 ctl_c-758818131-20130808-00 -rw-r----- 1 oracle dba 327680 Aug 8 11:48 full_4dogpd0u_4237_1 Note: Once a backup is taken, to view all available database backups from RMAN, you need to use “list” command that is shown further down in one of the examples. While this may be obvious, it is worth repeating again: Since we are taking hotbackup, the Oracle database can be up and running. Make sure your Oracle database is running before you execute any of the above RMAN backup commands. 4. Assign Backup TAG Name for Quick Identification If you are taking lot of backups, it will be easier to assign a tag to a particular backup, which we’ll later use during Oracle recovery (or while using list command to view it). The following example assign a backup tag called “WEEEKLY_PRD01_TBLS_BK_ONLY” to this particular backup. RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01; Starting backup at 10-AUG-13 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00002 name=/u03/oradata/devdb/PRD01_1.dbf channel ORA_DISK_1: starting piece 1 at 10-AUG-13 channel ORA_DISK_1: finished piece 1 at 10-AUG-13 piece handle=/backup/rman/full_4fogpdb3_4239_1 tag=WEEEKLY_PRD01_TBLS_BK_ONLY comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 10-AUG-13 Starting Control File and SPFILE Autobackup at 10-AUG-13 piece handle=/backup/rman/ctl_c-758818131-20130808-01 comment=NONE Finished Control File and SPFILE Autobackup at 10-AUG-13 Once the backup is finished, if you view the files from rman directory, you’ll not see the tag name here. Tag name is used only from RMAN repositories to view and restore backups. So, now you see there are more files in this directory, as we’ve taken couple of backups. $ ls -l /backup/rman/ total 29176 -rw-r----- 1 oracle dba 14585856 Aug 8 11:48 ctl_c-758818131-20130808-00 -rw-r----- 1 oracle dba 14585856 Aug 8 11:54 ctl_c-758818131-20130808-01 -rw-r----- 1 oracle dba 327680 Aug 8 11:48 full_4dogpd0u_4237_1 -rw-r----- 1 oracle dba 327680 Aug 8 11:54 full_4fogpdb3_4239_1 5. Change Oracle RMAN Backup File Name Format If you want the backup files itself will be in a specific format, you need to change the format in the RMAN configuration as shown below. In this example, we’ve appended the tag “full_devdb_bk_” prefix to all our backup files. RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT "/backup/rman/full_devdb_bk_%u_%s_%p" MAXPIECESIZE 2048 M; Now, let us take another backup with this modified configuration. RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01; RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01; Now when you view the RMAN files, you’ll see the new RMAN backup file has this new file name format for the files. This is easier to identify certain information about the backup just by looking at the file names. $ ls -l /backup/rman/ total 43764 -rw-r----- 1 oracle dba 14585856 Aug 8 11:48 ctl_c-758818131-20130808-00 -rw-r----- 1 oracle dba 14585856 Aug 8 11:54 ctl_c-758818131-20130808-01 -rw-r----- 1 oracle dba 14585856 Aug 8 11:56 ctl_c-758818131-20130808-02 -rw-r----- 1 oracle dba 327680 Aug 8 11:48 full_4dogpd0u_4237_1 -rw-r----- 1 oracle dba 327680 Aug 8 11:54 full_4fogpdb3_4239_1 -rw-r----- 1 oracle dba 327680 Aug 8 11:55 full_devdb_bk_4hogpdef_4241_1 6. Compress a RMAN Backup If you are taking a backup of a big database, you’ll notice that the RMAN backup files are bigger (almost same size as the database itself). So, for most situation, you should always tak ea compressed backup of the database. The following example take a compressed backup of the tablepsace PRD01. RMAN> BACKUP AS COMPRESSED BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01; When you view the backup files from the file system level, you will not see any .gz (or .zip, or .bz2) to indicate that the RMAN has taken a compressed backup. The file naming convention will still follow the same as a non-compressed backup. $ ls -l /backup/rman/ total 58352 -rw-r----- 1 oracle dba 14585856 Aug 8 11:48 ctl_c-758818131-20130808-00 -rw-r----- 1 oracle dba 14585856 Aug 8 11:54 ctl_c-758818131-20130808-01 -rw-r----- 1 oracle dba 14585856 Aug 8 11:56 ctl_c-758818131-20130808-02 -rw-r----- 1 oracle dba 14585856 Aug 8 11:59 ctl_c-758818131-20130808-03 -rw-r----- 1 oracle dba 327680 Aug 8 11:48 full_4dogpd0u_4237_1 -rw-r----- 1 oracle dba 327680 Aug 8 11:54 full_4fogpdb3_4239_1 -rw-r----- 1 oracle dba 327680 Aug 8 11:55 full_devdb_bk_4hogpdef_4241_1 -rw-r----- 1 oracle dba 127680 Aug 8 11:59 full_devdb_bk_4jogpdl0_4243_1 Note: The way to tell whether RMAN has take a compressed backup or not, it by looking at the size, and by looking at the output of the RMAN “list” command which is shown in one of the section below. 7. View all RMAN Backups To view all the RMAN backups, execute “list backup summary” as shown below. RMAN> LIST BACKUP SUMMARY; Using target database control file instead of recovery catalog List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- .. 4215 B F A DISK 10-AUG-13 1 1 NO TAG20130808T114846 4216 B F A DISK 10-AUG-13 1 1 NO TAG20130808T114849 4217 B F A DISK 10-AUG-13 1 1 NO WEEEKLY_PRD01_TBLS_BK_ONLY 4218 B F A DISK 10-AUG-13 1 1 NO TAG20130808T115413 4219 B F A DISK 10-AUG-13 1 1 NO WEEEKLY_PRD01_TBLS_BK_ONLY 4220 B F A DISK 10-AUG-13 1 1 NO TAG20130808T115600 4221 B F A DISK 10-AUG-13 1 1 YES WEEEKLY_PRD01_TBLS_BK_ONLY As you see above, it displays various information about the backups. In the above output, it show 7 RMAN backups. The last column shows the “Tag” that we specified when we took a backup. If we didn’t specify any TAG, RMAN creates a default tag with the prefix “TAG” followed by some numbers. You can also see that under the column “Compressed”, the last RMAN backup shows “YES”, which indicates that out of all the 7 RMAN backups, only the last one was compressed. Also, when the RMAN backup is running, if you want to see the process, you can query the V$RMAN_STATUS table from sql*plus as shown below. SQL> SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS; OPERATION STATUS MBYTES_PROCESSED START_TIM END_TIME --------------------------------- ----------------------- ---------------- --------- --------- CONTROL FILE AND SPFILE AUTOBACK COMPLETED 14 07-NOV-12 07-NOV-12 RMAN COMPLETED 0 07-NOV-12 07-NOV-12 RESTORE VALIDATE COMPLETED 0 07-NOV-12 07-NOV-12 RMAN COMPLETED WITH ERRORS 0 07-NOV-12 07-NOV-12 DELETE COMPLETED 0 08-NOV-12 08-NOV-12 BACKUP COMPLETED 0 10-AUG-13 10-AUG-13 CONTROL FILE AND SPFILE AUTOBACK COMPLETED 14 10-AUG-13 10-AUG-13 RMAN COMPLETED WITH ERRORS 1832 10-AUG-13 10-AUG-13 RMAN COMPLETED 0 10-AUG-13 10-AUG-13 ... 8. RMAN Restore Operations In an RMAN restore operation, you select files to be restored and then run the RESTORE command. Typically, you restore files in preparation for media recovery. You can restore the following types of files: ■ Database (all datafiles) ■ Tablespaces ■ Control files ■ Archived redo logs ■ Server parameter files Follow the steps given below to RESTORE DATABASE FROM BACKUP USING RMAN. 1. rman target / 2. startup nomount; Note : As we don’t have control file we cannot start the db in mount mode and also as we don’t have the data files we can not start the database in open mode. so now we have to restore the control files and data files from the backup. 3. RMAN > restore controlfile from '/ you can see the control files restored. 4. RMAN> alter database mount; (you don’t have to go to sqlplus prompt ,you can alter the db from RMAN prompt) 5. RMAN > restore database; (this will restore the database from the backup) (The backup should be available where the RMAN expect it to be) 6. RMAN > recover database (to recover database) 7. RMAN > alter database open resetlogs; database opened. 8. sqlplus / as sysdba 9. select name from v$database; NAME --- FIN 10. Select open_mode from v$databse; OPEN_MODE ------- READD WRITE 11. select tablespace_name from dba_tablespaces; This completes the steps to perform the backup and restore using RMAN.