RMAN

Data Dictionary Objects

DBA_HIST_INSTANCE_RECOVERY

GV_$RECOVERY_STATUS

DBA_RECOVERABLE_SCRIPT

V_$FLASH_RECOVERY_AREA_USAGE

DBA_RECOVERABLE_SCRIPT_BLOCKS

V_$INSTANCE_RECOVERY

DBA_RECOVERABLE_SCRIPT_ERRORS

V_$RECOVER_FILE

DBA_RECOVERABLE_SCRIPT_PARAMS

V_$RECOVERY_FILE_DEST

GV_$INSTANCE_RECOVERY

V_$RECOVERY_FILE_STATUS

GV_$RECOVER_FILE

V_$RECOVERY_LOG

GV_$RECOVERY_FILE_STATUS

V_$RECOVERY_PROGRESS

GV_$RECOVERY_LOG

V_$RECOVERY_STATUS

GV_$RECOVERY_PROGRESS

Format Directives  

Format	Description
%a	Current database activation id
%A	Zero-filled activation ID
%c	The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256
%d	Database name
%D	Current day of the month from the Gregorian calendar in format DD
%e	Archived log sequence number
%f	Absolute file number
%F	Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name
%h	Archived redo log thread number
%I	DBID
%M	Month in the Gregorian calendar in the format MM
%n	Database name, padded on the right with x characters to a total length of eight characters
%N	Tablespace name. Only valid when backing up datafiles as image copies.
%p	Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.
%r	Resetlogs ID
%s	Backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.
%S	Zero-filled sequence number
%t	Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.
%T	Year, month, and day in the Gregorian calendar in the format: YYYYMMDD
%u	An 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created
%U	A system-generated unique filename (default). %U is different for image copies and backup pieces. For a backup piece, %U is a shorthand for %u_%p_%c and guarantees uniqueness in generated backup filenames. For an image copy of a datafile, %U means the following: data-D-%d_id-%I_TS-%N_FNO-%f_%u For an image copy of an archived redo log, %U means the following: arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u For an image copy of a control file, %U means the following: cf-D_%d-id-%I_%u
%Y	Year in this format: YYYY
%%	Percent (%) character. For example, %%Y translates to the string %Y

Create RMAN User

$ sqlplus / as sysdba

 CREATE TABLESPACE cat_tbs;

 CREATE USER repoomega
     IDENTIFIED BY oracle1
     DEFAULT TABLESPACE cat_tbs
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON cat_tbs;

RMAN> GRANT create session TO repoomega
RMAN> GRANT recovery_catalog_owner TO repoomega;
RMAN> GRANT execute ON dbms_stats TO repoomega;  -- for demos

-- in the operating system shell
$ rman target / catalog repoomega/oracle1@repos

RMAN>SHOW ALL;

Configuring the parameters for RMAN

For Example:

RMAN> configure default device type to disk;
RMAN> configure device type disk parallelism 2 backup type to copy;
RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure backup optimization on;

Allocating a Single Backup Channel

RMAN> RUN {
  ALLOCATE CHANNEL d1 DEVICE TYPE DISK
  FORMAT '/u01/backups/%U';
  BACKUP DATABASE PLUS ARCHIVELOG;
  RELEASE CHANNEL;
}

Spread the backup across several disk drives. Allocate one DEVICE TYPE DISK channel for ach disk drive and specify the format string so that the filenames are on different disks

RUN {
  ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT '/u01/backups/%U';
  ALLOCATE CHANNEL d2 DEVICE TYPE DISK FORMAT '/u02/backups/%U';
  BACKUP DATABASE PLUS ARCHIVELOG;
  RELEASE CHANNEL;
}

Allocate Maintenance Channel

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

BACKUP

Back up a database, tablespace, datafile (current or copy), control file (current or copy), SPFILE, archived log, or backup set.
BACKUP <BackupOperand> CHANNEL '<channel_id>' CHECK LOGICAL COPIES <integer> CUMULATIVE DEVICE TYPE <deviceSpecifier> DISKRATIO <integer> DURATION <FileNameConversionSpecification> FILERPERSET <integer> FORCE
< AUXILIARY FORMAT <formatSpec, NEW> | FORMAT <formatSpec>
<forRecoveryOfSpec>
FULL
<keep option> MAXSETSIZE <sizeSpec> <notBackedUpSpec>
NO CHECKSUM NO EXCLUDE POOL <integer> PROXY [ONLY] REUSE SECTION SIZE <sizeSpec> <skipSpec> TAG '<tag_name>' VALIDATE

BACKUP AS BACKUPSET

RMAN>BACKUP AS BACKUPSET DEVICE TYPE <device_identifier> COPIES <integer> DATABASE FORMAT '<location_and_format>' ....

RMAN>BACKUP AS BACKUPSET (DATAFILE 3, 4, 5, 6, 7) (DATAFILE 8, 9);

RMAN>BACKUP AS BACKUPSET DATAFILE 3, 4, 5, 6, 7, 8, 9;

BACKUP AS COMPRESSED BACKUPSET

RMAN>BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK COPIES 2 DATABASE FORMAT '/u01/db_%U', '/u02/db_%U';

BACKUP AS COPY

RUN {
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT '/u01/%U';
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT '/u02/%U';
  BACKUP AS COPY TABLESPACE system, tools, users, undotbs;
  BACKUP AS COPY CURRENT CONTROLFILE;
}

BACKUP CURRENT

RUN {
  BACKUP CURRENT CONTROLFILE TO DESTINATION '/u01/backups';
}

BACKUP DATABASE

RUN {
  ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
  BACKUP DATABASE;
}

BACKUP DATAFILE

RUN {
  ALLOCATE CHANNEL d1 DEVICE TYPE DISK;
  BACKUP DATAFILE 1 FORMAT '/u01/backups/%U', '/u02/backups/%U';
  BACKUP DATAFILE 6 TO '/u01/backups', '/u02/backups';
}

BACKUP DURATION

Backup will stop if it is not completed in the specified duration

RMAN> BACKUP DURATION 3:30 DATABASE;

BACKUP ... FOR RECOVER OF ...

RMAN > BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG  'incr_upd' DATABASE FORMAT '/oradata/rman/161932/inc_%d_%T_%t_%s_%p';

RUN {
  RECOVER COPY OF DATABASE WITH TAG 'incr_update' UNTIL TIME 'SYSDATE-7';
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_upd'
  DATABASE;
}

BACKUP INCREMENTAL

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
RMAN>BACKUP INCREMENTAL LEVEL 1 DIFFERENTIAL TABLESPACE users;

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;

Complete Closed Database Recovery. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing,

so the database can be opened without reseting the redologs.

1. rman target /

2. startup mount;

3. restore database;

4. recover database;

5. alter database open;

Complete Open Database Recovery. Non system tablespace is missing, database is up

1. rman target /

2. sql 'alter tablespace <tablespace_name> offline immediate';

3. restore datafile 3;

4. recover datafile 3;

5. sql 'alter tablespace <tablespace_name> online';

Complete Open Database Recovery (when the database is initially closed).

Non system tablespace is missing

A user datafile is reported missing when tryin to startup the database. The datafile

can be turned offline and the database started up. Restore and

recovery are performed using Rman. After recovery is performed the datafile can

be turned online again.

1. sqlplus /nolog

2. connect / as sysdba

3. startup mount

4. alter database datafile '<datafile_name>' offline;

5. alter database open;

6. exit;

7. rman target /

8. restore datafile '<datafile_name>';

9. recover datafile '<datafile_name>';

10. sql 'alter tablespace <tablespace_name> online';

Recovery of a Datafile that has no backups (database is up).

If a non system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.

Pre requisites: All relevant archived logs.

1. sqlplus '/ as sysdba'

2. alter tablespace <tablespace_name> offline immediate;

3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;

4. exit

5. rman target /

6. recover tablespace <tablespace_name>;

7. sql 'alter tablespace <tablespace_name> online';

If the create datafile command needs to be executed to place the datafile on a

location different than the original use:

alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as

'/user/oradata/u02/dbtst/newdata01.dbf'

Restore and Recovery of a Datafile to a different location. Database is up.

If a non system datafile is missing and its original location not available, restore

can be made to a different location and recovery performed. Pre requisites: All relevant archived logs, complete cold or hot backup.

1. Use OS commands to restore the missing or corrupted datafile to the new

location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. alter tablespace <tablespace_name> rename datafile

'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';

4. rman target /

5. recover tablespace <tablespace_name>;

6. sql 'alter tablespace <tablespace_name> online';

Control File Recovery

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash. Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter  autobackup of controlfile to ON. You will need the dbid to restore the controlfile,

get it from the name of the backed up controlfile. It is the number following the 'c-' at the start of the name.

1. rman target /

2. set dbid <dbid#>

3. startup nomount;

4. restore controlfile from autobackup;

5. alter database mount;

6. recover database;

7. alter database open resetlogs;

8. make a new complete backup, as the database is open in a new incarnation and

previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when the database crash and needs to be

recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important object was

dropped or incorrect data was committed on it. In this case recovery needs to be performed until before the object was dropped. Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the 'until' recovery needs to be performed.

1. If the database is open, shutdown it to perform full restore.

2. rman target /

3. startup mount;

4. restore database;

5. recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.

6. alter database open resetlogs;

7. make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant. Alternatively you may use instead of until sequence, until time, ie: '2004-12-28:01:01:10'.