This manual describes an overview of ‘RMAN (Recovery Manager) commands’ to make a backup and restore of a complete or partitial Oracle Database. A backup and restore of datafiles, tablespaces, pfile, archivelog files.
Prereq
- Installation of Oracle Enterprise Linux or other.
- Installation of Oracle Database Server 10g/11g/12c.
Software
The necessary software can be downloaded by clicking on the link(s) below.
- n.a.
Check Backups
Backups
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
RMAN> SHOW ALL; SHOW RETENTION POLICY; SHOW DEVICE TYPE; SHOW DEFAULT DEVICE TYPE; SHOW CHANNEL; SHOW MAXSETSIZE; RMAN> CROSSCHECK BACKUP; RMAN> CROSSCHECK COPY; RMAN> CROSSCHECK backup of database; RMAN> CROSSCHECK backup of controlfile; RMAN> CROSSCHECK archivelog all; |
Availability of backups between two dates
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt; RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-AUG-04' AND '31-DEC-04'; RMAN> LIST BACKUP; RMAN> LIST BACKUP OF DATABASE; RMAN> LIST BACKUP SUMMARY; RMAN> LIST INCARNATION; RMAN> LIST BACKUP BY FILE; RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL; RMAN> LIST COPY OF DATAFILE 1, 2, 3; RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY; RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437; RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy"; RMAN> LIST BACKUPSET OF DATAFILE 1; |
Backup
Back up the database, and control file
The control file keeps information that tells you how the backups are named.
|
1 2 |
RMAN> BACKUP DATABASE; RMAN> BACKUP CURRENT CONTROLFILE; |
Backup datafiles
|
1 2 3 |
RMAN> BACKUP AS BACKUPSET DATAFILE 'ORACLE_HOME/oradata/trgt/users01.dbf', 'ORACLE_HOME/oradata/trgt/tools01.dbf'; |
Backup all datafiles in the database
(bit-for-bit copy of the database)
|
1 |
RMAN> BACKUP AS COPY DATABASE; |
Backup archive logs
|
1 |
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-28' AND 'SYSDATE-7'; |
Backup tablespace
|
1 |
RMAN> BACKUP TABLESPACE system, users, tools; |
Backup controlfile
|
1 |
RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy'; |
Backup parameter file
|
1 |
RMAN> BACKUP SPFILE; |
Backup all
|
1 |
RMAN> BACKUP BACKUPSET ALL; |
Create a consitent backup and keep the backup for a year. (it overulez the retention policy parameter)
|
1 2 3 |
RMAN> SHUTDOWN; RMAN> STARTUP MOUNT; RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS; |
Backup controle
Backup Validation.
|
1 |
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL; |
Backup options
|
1 |
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT CLEAR; |
Disk backups to the /tmp directory (%U unique naming format)
|
1 |
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/%U'; |
Configure RMAN to backup the control file
|
1 |
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; |
By default the controlfile is backup-ed to the flash recovery area.
|
1 2 |
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/cfilebackups/cf%F'; |
Configure RMAN to keep backups 7 days.
|
1 |
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; |
Keep three backups of each datafile
|
1 |
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3; |
Parallel backup
Configure backups to be executed parallel with two sbt channels. Reset any CONFIGURE setting to its default by running the command with the CLEAR option
|
1 2 3 |
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 2; RMAN> CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR; RMAN> CONFIGURE RETENTION POLICY CLEAR; |
Progress script backup
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB" col input_mbytes for 99,999,990.00 justify right head "READ_MB" col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB" col output_device_type for a10 justify left head "DEVICE" col complete for 990.00 justify right head "COMPLETE %" col compression for 990.00 justify right head "COMPRESS|% ORIG" col est_complete for a20 head "ESTIMATED COMPLETION" col recid for 9999999 head "ID" select recid , output_device_type , dbsize_mbytes , input_bytes/1024/1024 input_mbytes , output_bytes/1024/1024 output_mbytes , (output_bytes/input_bytes*100) compression , (mbytes_processed/dbsize_mbytes*100) complete , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete from v$rman_status rs , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) where status='RUNNING' and output_device_type is not null / |
Backupset Compression
The AS COMPRESSED BACKUPSET option of the backup command allows RMAN to perform binary compression of backupsets. The resulting backupsets do not need to be uncompressed during recovery. It is most useful in the following circumstances:
- You are performing disk-based backup with limited disk space.
- You are performing backups across a network where network bandwidth is limiting.
- You are performing backups to tape, CD or DVD where hardware compression is not available.
The following examples assume that some persistent parameters are configured in a similar manner to those listed below.
|
1 2 3 4 5 |
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/MYSID/%d_DB_%u_%s_%p'; |
The AS COMPRESSED BACKUPSET option can be used explicitly in the backup command.
|
1 2 3 4 5 6 |
RMAN> # Whole database and archivelogs. BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG; # Datafiles 1 and 5 only. BACKUP AS COMPRESSED BACKUPSET DATAFILE 1,5; |
Alternatively the option can be defined using the configure command.
|
1 2 3 4 5 6 |
RMAN> # Configure compression. CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET; # Whole database and archivelogs. BACKUP DATABASE PLUS ARCHIVELOG; |
Point in time recovery
Preview restore restore of the database to an older time.
|
1 |
RMAN> restore database preview until time "to_date('01-06-2012 15:39:00','dd-mm-yyyy hh24:mi:ss')"; |
Restore and recover of the whole database
|
1 2 3 4 |
RMAN> STARTUP FORCE MOUNT; RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; RMAN> ALTER DATABASE OPEN; |
Restore and recover a tablespace
|
1 2 3 4 |
RMAN> SQL 'ALTER TABLESPACE users OFFLINE'; RMAN> RESTORE TABLESPACE users; RMAN> RECOVER TABLESPACE users; RMAN> SQL 'ALTER TABLESPACE users ONLINE'; |
Restore and recover a datafile
|
1 2 3 4 |
RMAN> SQL 'ALTER DATABASE DATAFILE 64 OFFLINE'; RMAN> RESTORE DATAFILE 64; RMAN> RECOVER DATAFILE 64; RMAN> SQL 'ALTER DATABASE DATAFILE 64 ONLINE';Restore the Control file, (to all locations specified in the parameter file) then restore the database, using that control file: |
Media recovery
- Mount or open the database.
- Mount the database when performing whole database recovery, or open the database when performing online tablespace recovery.
- To perform incomplete recovery, use the SET UNTIL command to specify the time, SCN, or log sequence number at which recovery terminates. Alternatively, specify the UNTIL clause on the RESTORE and RECOVER commands.
- Restore the necessary files with the RESTORE command.
- Recover the datafiles with the RECOVER command.
- Place the database in its normal state. For example, open it or bring recovered tablespaces online.
|
1 2 3 4 5 6 7 8 9 |
RMAN> STARTUP NOMOUNT; RMAN>RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt; RESTORE CONTROLFILE; ALTER DATABASE MOUNT; RESTORE DATABASE; } |
Restore Validation confirms that a restore could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.
|
1 |
RMAN> RESTORE DATABASE VALIDATE; |
|
1 2 3 4 5 6 7 |
RMAN> RUN{ ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/fs1/%U'; ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/fs2/%U'; BACKUP (TABLESPACE system,finance,marketing FILESPERSET 20) (DATAFILE 62,63,64); } |
Delete backups en archives
|
1 2 3 4 5 6 7 8 |
RMAN> crosscheck backupset; RMAN> crosscheck backup; RMAN> crosscheck archivelog all; RMAN> list archivelog all; RMAN> delete expired backup; RMAN> delete expired backupset; RMAN> delete expired archivelog all; RMAN> delete obsolete; |
Crosschecking checks the catalog of the controlfile with the physical backups.
Delete Error messages
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
RMAN> crosscheck backuppiece 'BACKUP$PRODUCTIERMAN_1_1'; RMAN> delete force backuppiece'BACKUP$PRODUCTIERMANE_1_1'; using channel ORA_DISK_1 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 8516 8516 1 1 EXPIRED DISK BACKUP$PRODUCTIERMAN_1_1 Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece backup piece handle=BACKUP$PRODUCTIERMAN_1_1 recid=8516 stamp=835059674 Deleted 1 objects |
Information
- n.a.
Please let me know if this manual ‘RMAN (Recovery Manager) commands’ was usefull to you. If there are faults or you have suggestions regarding this manual, please let me know. No rights can be derived from this manual.
Regards,
Maarten
