Oracle Recovery Manager (10g)

ORACLE RMAN (10g)

10g enhancements and some more functionalities.

Connecting to rman:

Host#> rman 

Rman> connect target /

Rman> connect target dbuser as sysdba/passwd@TNS

Rman> connect catalog rman/pass@TNS

Rman> connect auxiliary dbuser/dbpass@TNS

### interactive mode

Host#> rman target / catalog rman/pass@TNS

Host#> rman target / nocatalog

### batch mode

Host#> rman target / catalog rman/rman@d cmdfile <cmd_file_name> log

<log_file_name>

Host#> rman target / @‘daily.rcv’

Rman> report schema

### creating rman catalog

1-  Connect to rman catalog database with sysdba privileges

2-  Create catalog user with unlimited quota and grant

recovery_catalog_owner, connect, resource

3-  Start rman with recovery catalog options

4-  run “create catalog” command in rman

5-  register the target database with “register database” command

Channels:

-- %U = %u_%p_%c

-   %c:  copy number of the backup file

-   %t:  backup set timestamp

-   %n: database name

-   %d:  database name 

-   %p:  backup piece number

Rman>

configure device type disk|sbt parallelism 3; ### max 255

configure default device type to disk;

configure device type disk clear;

configure default device type clear;

configure channel device type disk  Rate 5M;

      Readrate 5M;

      Format=’/disk2/backup/%U’;

      Maxopenfiles=10;

      Maxpiecesize=2G;

### setting disk IO rates

configure channel 1 device type disk rate 5M;

configure channel 2 device type disk rate 1M;

### rac option for backing up

configure channel 1 device type disk connect=’sys/password@node1’;

configure channel 2 device type disk connect=’sys/password@node2’;

### clearing channel settings

configure channel device type disk clear;

configure channel 1 device type disk clear;

### backing up to different disk media

configure channel 1 device type disk format ’/disk1/%U’;

configure channel 2 device type disk format ’/disk2/%U’;

Show all;

Show device type;

Show default device type;

Show channel;

### command blocks

Rman>

Run

{

  Allocate channel c1 device type disk;

  Backup datafile 3,4,5;

}

Backup:

### backup database

Rman>

Backup database include current controlfile;

Backup database plus archivelog [delete all [input]];

Backup [device type disk|sbt] database; 

Backup database tag=‘<tag_name>’;

Backup not backed up since time ‘sysdate-3’ database plus archivelog;

Backup format ‘path/%U’ database fileperset 3;

Backup full database [skip offline|readonly|inaccessible];

### incremental and differential backups

Backup incremental level 0 database;

Backup incremental level 1 database;

Backup incremental level 1 differential database;

Backup incremental level 1 cumulative database;

### overriding backup retentions 

Backup database keep until time “to_date(’03.08.2007’,’DD.MM.YYYY’)”;

Backup database keep forever nologs;

### validation of backups

Backup validate database archivelog all;

### backup parameters

Backup tablespace data_ts filesperset=10 maxsetsize=20G;

Backup tablespace <ts_name>;

### backup backupsets and imagecopies

Backup backupset 12;

Backup backupset completed before ‘sysdate-7’ delete input;

Backup backupset all [delete input];

Backup copy of database delete input;

### configure backup copies

Configure datafile backup copies for device type disk to 3;

Configure archivelog backup copies for device type disk to 3;

Configure datafile backup copies for device type disk clear;

Backup device type disk copies 3 datafile 7 format=

“/temp/%U”,“/data2/backup/%U”,“?/dbs/%U”;

### backup tablespace

Backup tablespace users format=“users_%u%p%c”;

Configure exclude for tablespace <ts_name>;

Backup database; ### will exclude the tablespaces

Backup database noexclude; ### if you dont want to exclude the ts

Configure exclude for tablespace <ts_name> clear;

### backup database archivelog

Backup archivelog all [delete [all] input];

Backup archivelog from sequence 121 until sequence 125;

Backup archivelog from time ‘sysdate-30’ until time ‘sysdate-7’;

### backing up controlfile and spfile

Backup (spfile) (current controlfile);

4 | Page  

Backup current controlfile tag=‘<tag_name>’;

Backup current controlfile format ‘?/oradata/%F.ctl’;

Backup controlfilecopy '/tmp/control01.ctl';

### automatically backup controlfile

Configure controlfile autobackup on | off;

Backup current controlfile tag=’controlfile_27072007’;

Configure controlfile autobackup format [for device type disk] to

'?/oradata/%F.ctl';

### backup datafile 

Backup datafile ‘system.dbf’;

Backup datafile 48;

Backup device type disk datafilecopy ‘/data2/backup/online/system_02.dbf’;

Backup datafilecopy ‘/tmp/data01.dbf’;

### copying database files

Copy [current] controlfile to ‘/tmp/control01.ctl’;

Copy datafile 1 to ‘/tmp/1.dbf’;

Copy datafile  1 to ‘/tmp/1.dbf’,

    2 to ‘/tmp/2.dbf’,

    3 to ‘/tmp/3.dbf’;

Copy datafile 1 to ‘/data2/backup/datafile.dbf’;

### reporting backups

List backup;

List backup of database [summary];

List backup of datafile 48,49,50;

List backup of datafile ‘<datafile_full_path/name>’;

List backup of tablespace users;

List backup of controlfile;

List backup of archivelog all;

List backup of archivelog [summary];

List backup of archivelog from sequence 1000 until sequence 1050;

List copy;

List copy of tablespace <ts_name>;

List copy of archivelog from time=‘sysdate-7’;

### compressed backups

RMAN> Backup as compressed backupset format ‘/oradata/backup/%d_%u.dbf’ 

database;

RMAN> Sql ‘alter database archive log current’;

RMAN> Backup as compressed backupset format ‘..’ archivelog all delete all 

input;

### image copies and backupsets

RMAN> Backup as backupset format ‘../data_06.bus’ datafile 6;

RMAN> Backup as backupset  datafile 6;

RMAN> Backup as copy database;

### incremental restore to imagecopies

RMAN> backup as copy incremental level 0 database tag db_whole_copy;

RMAN> backup incremental level 1 for recover of copy with tag db_whole_copy 

database tag db_copy_upd;

RMAN> recover copy of database with tag db_whole_copy;

RMAN> delete backupset tag db_copy_upd;

Restore & Recover:

### Preparing Recovery

SQL> 

Select Status from v$instance;

SQL> 

Select 

file#, 

status, 

error, 

recover, 

tablespace_name, 

name 

from 

v$datafile_header;

SQL>

Select  

    r.file# as df#, 

    d.name as df_name, 

    t.name as tbsp_name, 

    d.status, 

    r.error, 

    r.change#, 

    r.time

from 

    v$recover_file r, 

    v$datafile d, 

    v$tablespace t

where

    t.ts# = d.ts# and 

    d.file# = r.file#;

### validating backed up database files

Rman>

Restore validate;

Restore database validate;

### recovering full db

Restore database;

Recover database;

Recover database

  # optionally, delete logs restored for recovery and limit disk space used

  Delete archivelog maxsize 100M

  # optionally, skip the recovery of some tablespaces

  Skip tablespace history;  

Recover database [delete archivelog [maxsize=1G]];

### incomplete recovery

run

{ 

  set until time 'Nov 15 2001 09:00:00';

  # you can use until clause with restore or recover commands

  # restore until ‘sysdate-7’;

  # recover until ‘sysdate-7’;

  # alternatively, you can specify SCN

  # set until scn 1000;

  # alternatively, you can specify log sequence number

  # set until sequence 9923;  

6 | Page  

  restore database;

  recover database;

  sql ‘alter database open resetlogs’;

}

### recovering partial db

sql 'alter tablespace <ts_name> offline immediate';

restore tablespace <ts_name>;

recover tablespace <ts_name>;

sql 'alter tablespace <ts_name> online;

### recovering spfile

Rman>

Startup force nomount; # force because spfile is lost

Restore spfile; # if you are using a catalog

Restore spfile from autobackup; # if in NOCATALOG mode

Restore spfile to ‘/tmp/spfile<SID>.ora’ [from autobackup];

Restore spfile to pfile ‘/tmp/spfile<SID>.ora’ [from autobackup];

### restoring controlfile from auto backup

Rman>

Restore controlfile from auto backup; --can be run in nomount mode

### restoring to a new location

### restoring datafiles to a new location

run

{

  sql 'alter tablespace users offline immediate';

  sql 'alter tablespace tools offline immediate';

  # restore the datafile to a new location

  set newname for datafile '?/trgt/users01.dbf' to '/tmp/users01.dbf';

  set newname for datafile '?/trgt/tools01.dbf' to '/tmp/tools01.dbf';

  restore tablespace users, tools;

# point control file to new filenames  

  switch datafile all;

  recover tablespace users, tools;

  sql 'alter tablespace users online;

  sql 'alter tablespace tools online;

}

Rman>

Set newname for datafile ‘?/oradata/dbn/tools01.dbf’ to ‘/tmp/tools01.dbf’;

Restore datafile ‘?/oradata/dbname/tools01.dbf’;

Rman>

Switch datafile ‘/tmp/tools01.dbf’ to datafilecopy ‘<new dbf name>’;

### restoring controlfile to a new location

run

{ 

  # to restore a control file created before a certain date, issue the 

  # following

  # set command using a valid date for 'date_string'. you can also specify 

  # an scn or log sequence number.

  # set until time = 'date_string'; 

  restore controlfile to '/tmp/control01.ctl'; # restore to new location

  # replicate the control file manually to control_files locations

  restore controlfile from '/tmp/control01.ctl';

  startup mount;

}

### restoring archivelogs to a new location

run

{ 

  set archivelog destination to '/oracle/temp_restore';

  restore archivelog all;

  # restore and recover datafiles as needed

}

run 

{ 

  # set a new location for logs 1 through 10.

  set archivelog destination to '/tmp';

  restore archivelog from sequence 1 until sequence 10;

  # set a new location for logs 11 through 20.

  set archivelog destination to '?/oradata';

  restore archivelog from sequence 11 until sequence 20;

  # set a new location for logs 21 through 30.

  set archivelog destination to '?/dbs';

  restore archivelog from sequence 21 until sequence 30;

  restore archivelog from time 'sysdate-20' until time 'sysdate-19';

  restore archivelog from time “to_date(’20.02.2008’,’DD.MM.YYYY HH24:MI:SS’)” 

                    until time “to_date(’22.02.2008’,’DD.MM.YYYY HH24:MI:SS’)”;

  # restore and recover datafiles as needed

}