======================================= USING ORACLE 9i RECOVERY MANAGER (RMAN) ======================================= Content: 1. INITIALIZATION PARAMETERS 2. CONNECTION WITH RMAN 3. VIEWS TO CONSULT 4. RMAN BACKUP CONFIGURATIONS 5. REPORTS AND LISTS 6. BACKUP, COPY, RESTORE AND RECOVER 7. RETENTION POLICY 8. MANTAINING RMAN 9. FORMAT OPTIONS 10. CREATING RECOVERY CATALOG 11. OTHER RMAN OPTIONS ---------------------------- 1. INITIALIZATION PARAMETERS ---------------------------- * Some RMAN related database initialization parameters: control_file_record_keep_time Time in days to retention records in the Control File. (default: 7 days) large_pool_size Memory pool used for RMAN in backup/restore operations. shared_pool_size Memory pool used for RMAN in backup/restore operations (only if large pool is not configured). ----------------------- 2. CONNECTION WITH RMAN ----------------------- * To connect on a target database: connect target / connect target username/password connect target username/password@target_db * To connect on a catalog database: connect catalog username/password connect catalog username/password@catalog_db Obs: If you do not connect in a catalog database (if you connect only in a target database), you are going to use repository information in the control file of the target database. ------------------- 3. VIEWS TO CONSULT ------------------- * Views to consult into the target database: v$backup_device Device types accepted for backups by RMAN. v$archived_log Redo logs archived. v$backup_corruption Corrupted blocks in backups. v$copy_corruption Corrupted blocks in copies. v$database_block_corruption Corrupted blocks in the database after last backup. v$backup_datafile Backups of datafiles. v$backup_redolog Backups of redo logs. v$backup_set Backup sets made. v$backup_piece Pieces of previous backup sets made. v$session_long_ops Long operations running at this time. * Views to consult into the RMAN catalog database: rc_database Information about the target database. rc_datafile Information about the datafiles of target database. rc_tablespace Information about the tablespaces of target database. rc_stored_script Stored scripts. rc_stored_script_line Source of stored scripts. ----------------------------- 4. RMAN BACKUP CONFIGURATIONS ----------------------------- * Some channel, device and backup configurations: configure default device type to [disk|sbt]; configure device type disk paralelism 3; configure channel device type disk format '/bkp/%U'; configure channel device type disk maxpiecesize 2G; configure channel device type disk clear; configure datafile backup copies for device type disk to 2; configure backup optimization on; set controlfile autobackup format for device type disk to '/bkp/controlfile_%F'; -------------------- 5. REPORTS AND LISTS -------------------- * Lists of backups and copies: list backup of database; list backup of datafile "/.../bkp/users01.dbf"; list copy of tablespace "SYSTEM"; list copy; * Report the database structure: report schema; * Report unrecoverable datafiles in the database: report unrecoverable; * Show command: show datafile backup copies; show default device type; show device type; show channel; show all; ------------------------------------ 6. BACKUP, COPY, RESTORE AND RECOVER ------------------------------------ * The BACKUP command and some backup scripts: backup full datafile x,y,z incremental level x include current controlfile archivelog all delete [all] input copies x filesperset x maxsetsize xM diskratio x format = '/backup/bkp_%U.bkp'; run { allocate channel c1 [device] type disk maxpiecesize = 4G format = '/disk1/backup/database.bkp'; backup database channel c1 tag='DB_BACKUP; *or* backup datafile '/u01/oradata/users01.dbf'; *or* backup database plus archivelog; *or* backup archive log all delete all input; etc... } run { allocate channel c1 type sbt; allocate channel c2 type sbt; backup incremental level = 0 format '/disk1/backup/df_%d_%s_%p.bkp' (datafile 1,2 channel c1 tag=DF1) (datafile 1,2 channel c1 tag=DF1); alter system archive log current; } * The COPY command and some copy scripts: copy datafile '/oradata/users01.dbf' TO '/backup/users01.dbf' tag=DF3, datafile 4 to TO '/backup/users04.dbf' tag=DF4, archivelog 'arch_1060.arch' TO 'arch_1060.bak' tag=CP2ARCH16; run { allocate channel c1 type disk; copy datafile '/oradata/users01.dbf' TO '/backup/users01.dbf' tag=DF3, archivelog 'arch_1060.arch' TO 'arch_1060.bak' tag=CP2ARCH16; } * The RESTORE and RECOVER commands and some scripts: restore database; recover database; * Changing tablespace users offline to start a recover of this tablespace: run { sql "alter tablespace users offline immediate"; restore tablespace users; recover database users; sql "alter tablespace users online"; } * Restoring and recovering datafile 1 into a new location: run { set newname for datafile 1 to '/new_dir/system01.dbf'; restore database; switch datafile all; recover database; } * Incomplete recover, i.e., recover until a time in the past: run { allocate channel c1 type disk; [ set until time = 'yyyy-mm-dd:hh:mi:ss'; ] [ set until sequence nn thread 1; ] restore database; recover database; alter database open resetlogs; } ------------------- 7. RETENTION POLICY ------------------- * Configuring the retention policy: configure retention policy to recovery window of 7 days; configure retention policy to redundancy 2; configure retention policy to days 3; configure retention policy clear; * Showing the actual retention policy configuration: show retention policy; * Reporting datafiles that need backup according to the retention policy: report need backup; report need backup incremental 3 database; report need backup days 3; report need backup days 3 tablespace system; report need backup redundancy 2; report need backup recovery window of 3 days; * Reporting obsolete backup's according to the retention policy: report obsolete; * Deleting obsolete backup files according to the retention policy: delete obsolete; delete noprompt obsolete; * Overriding the configured retention policy: change backupset 123 keep forever nologs; change datafilecopy '/disk1/oradata/users01.dbf' keep until 'SYSDATE+30'; ------------------ 8. MANTAINING RMAN ------------------ * To resync the catalog with database structure and archived logs: resync catalog; * Check if the files generated by RMAN to disk already exists. If some files were deleted in the operation system, the status of these file will be marked as expired in the RMAN repository. crosscheck; crosscheck backup; crosscheck backupset of database; crosscheck copy; * To remove these expired backups or files: delete expired; delete noprompt expired backup of tablespace users; * To delete backups and copies: delete backupset 102; delete datafilecopy '/disk1/users01.dbf'; * To change the status of some backups or copies to unavailable come back to available: change backup of controlfile unavaliable; change backup of controlfile available; change datafilecopy '/disk1/copy/users01.dbf' unavailable; change copy of archivelog sequence between 230 and 240 unavailable; * To catalog or uncatalog in RMAN repository some copies of datafiles, archivelogs and controlfies made by users using OS commands: catalog datafilecopy '/disk1/backup/sample01.dbf'; catalog archivelog '/oradata/archive/arch_20.arc', '/oradata/archive/arch_21.arc'; catalog controlfilecopy '/oradata/copy/controlfile.ctl'; change datafilecopy '/disk1/backup/sample01.dbf' uncatalog; change archivelog '/oradata/archive/arch_20.arc', '/oradata/archive/arch_21.arc' uncatalog; change controlfilecopy '/oradata/copy/controlfile.ctl' uncatalog; ----------------- 9. FORMAT OPTIONS ----------------- * Format options that should be used in backup and copy commands: %U - The same as '%u_%p_%c'. %u - An eight characters unique name generated by RMAN. %t - Backup set timestamp . %n - Database name with x characters. %d - Database name. %s - Backup set number. %p - Component number in the backup set. %c - Copy number in the backup set. ----------------------------- 10. CREATING RECOVERY CATALOG ----------------------------- * Six steps to create the recovery catalog (considering that the database for the RMAN catalog is already created): 1. Create tablespace for RMAN: SQL> create tablespace RMAN_TS datafile '/disk1/oradata/u03/rman_ts01.dbf' size 20M extent management local uniform size 128K; 2. Create RMAN catalog user: SQL> create user RMAN_USER identified by RMAN_PASSWORD default tablespace RMAN_TS quota unlimited on RMAN_TS; 3. Grant some privileges to RMAN user: SQL> grant connect, resource to RMAN_USER; SQL> grant recovery_catalog_owner to RMAN_USER; 4. Connect into catalog database and create the catalog: % rman catalog RMAN_USER/RMAN_PASSWORD@cat_db log=create_catalog.log RMAN> create catalog tablespace RMAN_TS; RMAN> exit; 5. Connect into the target database and into the catalog database: % rman target sys/oracle@target_db RMAN> connect catalog RMAN_USER/RMAN_PASSWORD@cat_db 6. Connected into the both databases, register target database: RMAN> register database; DONE! ---------------------- 11. OTHER RMAN OPTIONS ---------------------- *** Other options or other possible commands if you uses RMAN repository into a catalog database: * Register a new version of the database: reset database; * To create, replace, print, execute and delete scripts in RMAN catalog repository: create script SCRIPT_NAME { ... commands ... } replace script SCRIPT_NAME { ... new commands ... } print script SCRIPT_NAME; run {execute script SCRIPT_NAME;} delete script SCRIPT_NAME; ======================================= USING ORACLE 9i RECOVERY MANAGER (RMAN) Revision 1.0 (Jul 2005) Written by Roverli P. Ziwich roverli@roverli.net http://www.roverli.net =======================================