Oracle Database Backup & Recovery Using Recovery Manager(RMAN) TUTORIAL

Create a Oracle database with the name PRİMARYDB running in ARCHIVELOG mode.

For simplicity the database can be created with all files located under one common area:

For example “/opt/oracle/oradata/primarydb/”

Archive log files can be located at “/opt/oracle/oradata/primarydb/arch”

Use small redo log file sizes (1 megabyte) to simulate redo activity.

Before using RMAN, we need to first register the database with the RMAN catalog.

primarydb:/opt/oracle>rman target / catalog rman/rman@rmanp

Recovery Manager: Release 9.2.0.6.0 – Production

connected to target database: PRİMARYDB (DBID=4054272425)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

After the database has been registered with the recovery catalog, we can confirm if the target database structure has been correctly registered in the catalog database.

RMAN> report schema;

Report of database schema

File K-bytes    Tablespace           RB segs Datafile Name

—- ———- ——————– ——- ——————-

1        348160 SYSTEM               YES     /opt/oracle/oradata/primarydb/system01.dbf

2        204800 UNDOTBS1             YES     /opt/oracle/oradata/primarydb/undotbs01.dbf

3         20480 DRSYS                NO      /opt/oracle/oradata/primarydb/drsys01.dbf

4        140160 EXAMPLE              NO      /opt/oracle/oradata/primarydb/example01.dbf

5         25600 INDX                 NO      /opt/oracle/oradata/primarydb/indx01.dbf

6         20480 ODM                  NO      /opt/oracle/oradata/primarydb/odm01.dbf

7         10240 TOOLS                NO      /opt/oracle/oradata/primarydb/tools01.dbf

8         25600 USERS                NO      /opt/oracle/oradata/primarydb/users01.dbf

9         39040 XDB                  NO      /opt/oracle/oradata/primarydb/xdb01.dbf

We can then configure a number of backup parameters so that the same information need not be provided every time a backup is performed.

In this case we are configuring RMAN so that all database backups (unless specifically mentioned) go to the common location ‘/opt/oracle/backup’ with the prefix ‘bkp’.

The backup piece name is automatically generated by RMAN because we are using the ‘%U’ placeholder.

We are also configuring the control file to be automatically backed up whenever a BACKUP or RESTORE command is issued as well as when the structure of the database changes.

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/opt/oracle/backup/bkp.%U’;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/opt/oracle/backup/bkp_cf_%F’;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> SHOW ALL;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/opt/oracle/backup/bkp_cf_%F’;

CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘/opt/oracle/backup/bkp.%U’;

RMAN configuration has no stored or default parameters

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/oracle/product9206/dbs/snapcf_primarydb.f’; # default

RMAN configuration has no stored or default parameters

RMAN configuration has no stored or default parameters

We will now take a Level 0 Incremental Backup of the database.

RMAN> backup incremental level 0 database;

Starting backup at 31-DEC-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=18 devtype=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/opt/oracle/oradata/primarydb/system01.dbf

input datafile fno=00002 name=/opt/oracle/oradata/primarydb/undotbs01.dbf

input datafile fno=00004 name=/opt/oracle/oradata/primarydb/example01.dbf

input datafile fno=00009 name=/opt/oracle/oradata/primarydb/xdb01.dbf

input datafile fno=00005 name=/opt/oracle/oradata/primarydb/indx01.dbf

input datafile fno=00008 name=/opt/oracle/oradata/primarydb/users01.dbf

input datafile fno=00003 name=/opt/oracle/oradata/primarydb/drsys01.dbf

input datafile fno=00006 name=/opt/oracle/oradata/primarydb/odm01.dbf

input datafile fno=00007 name=/opt/oracle/oradata/primarydb/tools01.dbf

channel ORA_DISK_1: starting piece 1 at 31-DEC-19

channel ORA_DISK_1: finished piece 1 at 31-DEC-19

piece handle=/opt/oracle/backup/bkp.25f4567jh comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

Finished backup at 31-DEC-19

Starting Control File and SPFILE Autobackup at 31-DEC-19

piece handle=/opt/oracle/backup/bkp_kd_s-9544272425-20051231-11 comment=NONE

Finished Control File and SPFILE Autobackup at 31-DEC-19

Note: The controlfile and Server Parameter File has automatically been backed up by RMAN as well

We will then query the catalog to list information about this backup.

RMAN> list backup of database summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Tag

——- — — – ———– ————— ——- ——- —

12888157 B  0  A DISK        31-DEC-19       1       1       TAG20051231T095219

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

12888169 Full    1M         DISK        00:00:00     31-DEC-19

        BP Key: 12888170   Status: AVAILABLE   Tag:

        Piece Name: /opt/oracle/backup/bkp_kd_s-9544272425-20051231-11

  Controlfile Included: Ckp SCN: 183178       Ckp time: 31-DEC-19

RMAN> list backup of spfile;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

——- —- — ———- ———– ———— —————

12888169 Full    0          DISK        00:00:00     31-DEC-19

        BP Key: 12888170   Status: AVAILABLE   Tag:

        Piece Name: /opt/oracle/backup/bkp_kd_s-9544272425-20051231-11

  SPFILE Included: Modification time: 31-DEC-19

To simulate some backup and recovery scenarios we will create a table in the SYSTEM schema stored in the USERS tablespace.

We will also create a procedure to load records into this table to simulate database activity.

create table testschema

tablespace users

as select * from dba_objects;

create  or replace procedure

load_data

is

begin

for i in 1 .. 10

loop

insert into testschema

select * from sys.dba_objects;

end loop;

commit;

end;

/

Let us now execute the procedure and load data in the table. Records in the table will vary by database as it depends on the number of database objects. Note that loading data in the table has generated archive log files as well.

SQL> exec load_data

PL/SQL procedure successfully completed.

SQL> select count(*) from testschema;

  COUNT(*)

———-

    318746

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /opt/oracle/oradata/primarydb/arch 

Oldest online log sequence     128

Next log sequence to archive   130

Current log sequence           130

Note: the archive destination can vary based on the physical location of the archive log files

SQL> !ls -l /opt/oracle/oradata/primarydb/arch

total 47256

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch100.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch101.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch102.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch103.log

-rw-r—–    1 oracle   dba       1046016 Dec 31 10:41 arch104.log

-rw-r—–    1 oracle   dba       1046528 Dec 31 10:41 arch105.log

-rw-r—–    1 oracle   dba       1046528 Dec 31 10:41 arch106.log

-rw-r—–    1 oracle   dba       1046016 Dec 31 10:41 arch107.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch108.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch109.log

-rw-r—–    1 oracle   dba       1046016 Dec 31 10:41 arch110.log

-rw-r—–    1 oracle   dba       1044480 Dec 31 10:41 arch111.log

-rw-r—–    1 oracle   dba       1046016 Dec 31 10:41 arch112.log

-rw-r—–    1 oracle   dba       1044480 Dec 31 10:41 arch113.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch114.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch115.log

-rw-r—–    1 oracle   dba       1043968 Dec 31 10:41 arch116.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch117.log

-rw-r—–    1 oracle   dba       1046528 Dec 31 10:41 arch118.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch119.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch120.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch121.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch122.log

-rw-r—–    1 oracle   dba       1043968 Dec 31 10:41 arch123.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch124.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch125.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch126.log

-rw-r—–    1 oracle   dba       1043456 Dec 31 10:41 arch127.log

-rw-r—–    1 oracle   dba       1044480 Dec 31 10:41 arch128.log

-rw-r—–    1 oracle   dba       1046016 Dec 31 10:41 arch129.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 08:27 arch84.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 08:43 arch85.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 09:01 arch86.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 09:20 arch87.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 09:38 arch88.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 09:55 arch89.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:13 arch90.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:31 arch91.log

-rw-r—–    1 oracle   dba       1043968 Dec 31 10:31 arch92.log

-rw-r—–    1 oracle   dba       1043456 Dec 31 10:31 arch93.log

-rw-r—–    1 oracle   dba       1046528 Dec 31 10:31 arch94.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:40 arch95.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch96.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch97.log

-rw-r—–    1 oracle   dba       1047040 Dec 31 10:41 arch98.log

-rw-r—–    1 oracle   dba       1045504 Dec 31 10:41 arch99.log

We will now take a backup of the archive log files now – after the backup completes, the files will be physically deleted from disk.

RMAN> sql “ALTER SYSTEM ARCHIVE LOG CURRENT”;

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

primarydb:/opt/oracle>ls -lrt /opt/oracle/oradata/primarydb/arch

total 0

After the archivelog backup has completed and the files physically deleted from disk, we will query the catalog to confirm that the record of this archivelog backup does exist.

RMAN> LIST BACKUP OF ARCHIVELOG ALL;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

——- ———- ———– ———— —————

12889315 46M        DISK        00:00:03     31-DEC-19

        BP Key: 12889316   Status: AVAILABLE   Tag: TAG20051231T104955

        Piece Name: /opt/oracle/backup/bkp.31f7m0mk_1_1

  List of Archived Logs in backup set 12889315

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  —- ——- ———- ——— ———- ———

  1    84      167358     31-DEC-19 169683     31-DEC-19

  1    85      169683     31-DEC-19 172518     31-DEC-19

  1    86      172518     31-DEC-19 175256     31-DEC-19

  1    87      175256     31-DEC-19 177990     31-DEC-19

  1    88      177990     31-DEC-19 180724     31-DEC-19

  1    89      180724     31-DEC-19 183489     31-DEC-19

  1    90      183489     31-DEC-19 186278     31-DEC-19

  1    91      186278     31-DEC-19 189061     31-DEC-19

  1    92      189061     31-DEC-19 189120     31-DEC-19

  1    93      189120     31-DEC-19 189128     31-DEC-19

  1    94      189128     31-DEC-19 189137     31-DEC-19

  1    95      189137     31-DEC-19 190732     31-DEC-19

  1    96      190732     31-DEC-19 190977     31-DEC-19

  1    97      190977     31-DEC-19 190986     31-DEC-19

  1    98      190986     31-DEC-19 190996     31-DEC-19

  1    99      190996     31-DEC-19 191014     31-DEC-19

  1    100     191014     31-DEC-19 191055     31-DEC-19

  1    101     191055     31-DEC-19 191063     31-DEC-19

  1    102     191063     31-DEC-19 191077     31-DEC-19

  1    103     191077     31-DEC-19 191087     31-DEC-19

  1    104     191087     31-DEC-19 191100     31-DEC-19

  1    105     191100     31-DEC-19 191112     31-DEC-19

  1    106     191112     31-DEC-19 191124     31-DEC-19

  1    107     191124     31-DEC-19 191133     31-DEC-19

  1    108     191133     31-DEC-19 191145     31-DEC-19

  1    109     191145     31-DEC-19 191156     31-DEC-19

  1    110     191156     31-DEC-19 191166     31-DEC-19

  1    111     191166     31-DEC-19 191177     31-DEC-19

  1    112     191177     31-DEC-19 191192     31-DEC-19

  1    113     191192     31-DEC-19 191200     31-DEC-19

  1    114     191200     31-DEC-19 191216     31-DEC-19

  1    115     191216     31-DEC-19 191230     31-DEC-19

  1    116     191230     31-DEC-19 191238     31-DEC-19

  1    117     191238     31-DEC-19 191253     31-DEC-19

  1    118     191253     31-DEC-19 191262     31-DEC-19

  1    119     191262     31-DEC-19 191275     31-DEC-19

  1    120     191275     31-DEC-19 191285     31-DEC-19

  1    121     191285     31-DEC-19 191293     31-DEC-19

  1    122     191293     31-DEC-19 191309     31-DEC-19

  1    123     191309     31-DEC-19 191317     31-DEC-19

  1    124     191317     31-DEC-19 191329     31-DEC-19

  1    125     191329     31-DEC-19 191346     31-DEC-19

  1    126     191346     31-DEC-19 191358     31-DEC-19

  1    127     191358     31-DEC-19 191370     31-DEC-19

  1    128     191370     31-DEC-19 191378     31-DEC-19

  1    129     191378     31-DEC-19 191395     31-DEC-19

  1    130     191395     31-DEC-19 192745     31-DEC-19

  1    131     192745     31-DEC-19 193033     31-DEC-19

We can also run the following command to list all the backups of the archive log files performed in the past 24 hours.

RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME ‘SYSDATE -1’;

Please note that the backup location has backup pieces pertaining to the database backup, the control file autobackup as well as the archive log backups.

primarydb:/opt/oracle/backup>ls -lrt

total 496740

-rw-r—–    1 oracle   dba      455118848 Dec 31 09:53 bkp.25f4567jh

-rw-r—–    1 oracle   dba       1892352 Dec 31 09:53 bkp_kd_s-9544272425-20051231-11

-rw-r—–    1 oracle   dba      49178112 Dec 31 10:49 bkp.31f7m0mk_1_1

-rw-r—–    1 oracle   dba       1957888 Dec 31 10:50 bkp.fghh54m0mk_1_1

We will now take a level 0 incremental backup by using a RMAN command file which can be invoked from the command line by calling the rman executable .

primarydb:/opt/oracle/scripts>rman target / catalog rman/rman@rmanp cmdfile=bkp_inc_lev0.rcv log=bkp_inc_lev0.log

These are the contents of the .rcv file :

primarydb:/opt/oracle/scripts>cat bkp_inc_lev0.rcv

run

{

backup incremental level 0 database plus archivelog delete input;

}

Scenario 1:    Loss of a datafile

primarydb:/opt/oracle/oradata/primarydb>rm  users01.dbf

Because the datafile holding the USERS tablespace has been deleted at the O/S level to simulate the media failure, the load_data procedure will fail.

SQL> exec load_data

BEGIN load_data; END;

*

ERROR at line 1:

ORA-01116: error in opening database file 8

ORA-01110: data file 8: ‘/opt/oracle/oradata/primarydb/users01.dbf’

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

ORA-06512: at “SYSTEM.LOAD_DATA”, line 7

ORA-06512: at line 1

We will perform a RMAN datafile recovery by restoring and recovering only one single datafile that has been affected by the media failure. We need to take the datafile offline before performing the recovery.

primarydb:/opt/oracle/oradata/primarydb>rman target / catalog rman/rman@rmanp

Recovery Manager: Release 9.2.0.6.0 – Production

connected to target database: PRİMARYDB (DBID=4054272425)

connected to recovery catalog database

RMAN> sql ‘alter database datafile 8 offline’;

sql statement: alter database datafile 8 offline

RMAN> restore datafile 8;

Starting restore at 31-DEC-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00008 to /opt/oracle/oradata/primarydb/users01.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/backup/bkp.06h7m2ru_1_1 tag=TAG20051231T112654 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 31-DEC-19

RMAN> recover datafile 8;

Starting recover at 31-DEC-19

using channel ORA_DISK_1

starting media recovery

media recovery complete

Finished recover at 31-DEC-19

RMAN> sql ‘alter database datafile 8 online’;

sql statement: alter database datafile 8 online

Let us now load data in the table to confirm that our media recovery has worked

SQL> exec load_data

PL/SQL procedure successfully completed.

We can similarly write a RMAN command file to take a level 1 incremental backup.

Note: while doing a restore and recover, RMAN will try to use a Level 1 incremental backup first over using archive log files.

primarydb:/opt/oracle/scripts>cat bkp_inc_lev1.rcv

run

{

backup incremental level 1 database plus archivelog delete input;

}

Now take a Level 1 backup using the command file listed above in the same way as the Level 0 backup was taken from the command line.

Based on the retention policy chosen, we can list the backups that are obsolete as well as delete them physically from disk.

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

Report of obsolete backups and copies

Type                 Key    Completion Time    Filename/Handle

——————– —— —————— ——————–

Backup Set           12888157 31-DEC-19

  Backup Piece       12888158 31-DEC-19          /opt/oracle/backup/bkp.25f4567jh

Backup Set           12888169 31-DEC-19

  Backup Piece       12888170 31-DEC-19          /opt/oracle/backup/bkp_kd_s-9544272425-20051231-11

Backup Set           12889315 31-DEC-19

  Backup Piece       12889316 31-DEC-19          /opt/oracle/backup/bkp.31f7m0mk_1_1

Backup Set           12889366 31-DEC-19

  Backup Piece       12889367 31-DEC-19          /opt/oracle/backup/bkp.fghh54m0mk_1_1

Backup Set           12889556 31-DEC-19

  Backup Piece       12889557 31-DEC-19          /opt/oracle/backup/bkp.gbm45m2rs_1_1

Backup Set           12889581 31-DEC-19

  Backup Piece       12889582 31-DEC-19          /opt/oracle/backup/bkp_mbn9854272425-20051231-02

Backup Set           12896130 31-DEC-19

  Backup Piece       12896131 31-DEC-19          /opt/oracle/backup/bkp_bn_j-9994272425-20051231-09

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=9 devtype=DISK

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

——————– —— —————— ——————–

Backup Set           12888157 31-DEC-19

  Backup Piece       12888158 31-DEC-19          /opt/oracle/backup/bkp.25f4567jh

Backup Set           12888169 31-DEC-19

  Backup Piece       12888170 31-DEC-19          /opt/oracle/backup/bkp_kd_s-9544272425-20051231-11

Backup Set           12889315 31-DEC-19

  Backup Piece       12889316 31-DEC-19          /opt/oracle/backup/bkp.31f7m0mk_1_1

Backup Set           12889366 31-DEC-19

  Backup Piece       12889367 31-DEC-19          /opt/oracle/backup/bkp.fghh54m0mk_1_1

Backup Set           12889556 31-DEC-19

  Backup Piece       12889557 31-DEC-19          /opt/oracle/backup/bkp.gbm45m2rs_1_1

Backup Set           12889581 31-DEC-19

  Backup Piece       12889582 31-DEC-19          /opt/oracle/backup/bkp_mbn9854272425-20051231-02

Backup Set           12896130 31-DEC-19

  Backup Piece       12896131 31-DEC-19          /opt/oracle/backup/bkp_bn_j-9994272425-20051231-09

Do you really want to delete the above objects (enter YES or NO)? YES

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp.25f4567jh recid=1 stamp=578483543

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp_kd_s-9544272425-20051231-11 recid=2 stamp=578483615

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp.31f7m0mk_1_1 recid=3 stamp=578486997

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp.fghh54m0mk_1_1 recid=4 stamp=578487003

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp.gbm45m2rs_1_1 recid=5 stamp=578489213

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp_mbn9854272425-20051231-02 recid=8 stamp=578489283

deleted backup piece

backup piece handle=/opt/oracle/backup/bkp_bn_j-9994272425-20051231-09 recid=9 stamp=578491304

Deleted 7 objects

We can also run the command to delete obsolete backup pieces without having to answer the prompt

RMAN > delete noprompt obsolete;

We can query the catalog to confirm that only one level 0 backup and one level 1 backup exist. The earlier level 0 backup has been deleted because it was found to be obsolete considering the RMAN retention policy chosen.

RMAN> list backup of database summary;

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Tag

——- — — – ———– ————— ——- ——- —

12889562 B  0  A DISK        31-DEC-19       1       1       TAG20051231T112654

12896580 B  1  A DISK        31-DEC-19       1       1       TAG20051231T121214

Scenario 2:    Loss of all datafiles

primarydb:/opt/oracle/oradata/primarydb>rm *.dbf

Since all the datafiles have been deleted to simulate loss of all datafiles, the procedure to load data will also fail subsequently.

SQL> exec load_data

BEGIN load_data; END;

*

ERROR at line 1:

ORA-01116: error in opening database file 2

ORA-01110: data file 2: ‘/opt/oracle/oradata/primarydb/undotbs01.dbf’

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

ORA-06512: at “SYSTEM.LOAD_DATA”, line 7

ORA-06512: at line 1

Shutdown the database and mount it in preparation for media recovery

SQL> conn / as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

primarydb:/opt/oracle/oradata/primarydb>rman target / catalog rman/rman@rmanp

RMAN> restore database;

Starting restore at 31-DEC-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=14 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /opt/oracle/oradata/primarydb/system01.dbf

restoring datafile 00002 to /opt/oracle/oradata/primarydb/undotbs01.dbf

restoring datafile 00003 to /opt/oracle/oradata/primarydb/drsys01.dbf

restoring datafile 00004 to /opt/oracle/oradata/primarydb/example01.dbf

restoring datafile 00005 to /opt/oracle/oradata/primarydb/indx01.dbf

restoring datafile 00006 to /opt/oracle/oradata/primarydb/odm01.dbf

restoring datafile 00007 to /opt/oracle/oradata/primarydb/tools01.dbf

restoring datafile 00008 to /opt/oracle/oradata/primarydb/users01.dbf

restoring datafile 00009 to /opt/oracle/oradata/primarydb/xdb01.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/backup/bkp.06h7m2ru_1_1 tag=TAG20051231T112654 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 31-DEC-19

RMAN> recover database;

Starting recover at 31-DEC-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /opt/oracle/oradata/primarydb/system01.dbf

destination for restore of datafile 00002: /opt/oracle/oradata/primarydb/undotbs01.dbf

destination for restore of datafile 00003: /opt/oracle/oradata/primarydb/drsys01.dbf

destination for restore of datafile 00004: /opt/oracle/oradata/primarydb/example01.dbf

destination for restore of datafile 00005: /opt/oracle/oradata/primarydb/indx01.dbf

destination for restore of datafile 00006: /opt/oracle/oradata/primarydb/odm01.dbf

destination for restore of datafile 00007: /opt/oracle/oradata/primarydb/tools01.dbf

destination for restore of datafile 00008: /opt/oracle/oradata/primarydb/users01.dbf

destination for restore of datafile 00009: /opt/oracle/oradata/primarydb/xdb01.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/backup/bkp.0bh7m5gu_1_1 tag=TAG20051231T121214 params=NULL

channel ORA_DISK_1: restore complete

starting media recovery

media recovery complete

Finished recover at 31-DEC-19

RMAN> sql ‘alter database open ‘;

sql statement: alter database open

Let us verify that the data has been fully restored by querying the TESTSCHEMA table

primarydb:/opt/oracle/oradata/primarydb>sqlplus system/oracle

SQL> select count(*) from testschema;

  COUNT(*)

———-

    608516

Scenario 3:    Point-In-Time Recovery

Note the current database clock time

SQL> select to_char(sysdate,’DD-MON-YY:HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’D

——————

31-DEC-19:12:43:16

We will now drop the table TESTSCHEMA and then perform an RMAN point-in-time recovery to restore the database to a point in time just before the table was dropped. Note the number of rows in the table.

SQL> select count(*) from testschema;

  COUNT(*)

———-

    608516

SQL> drop table testschema;

Table dropped.

Shutdown and then mount the database in preparation for restore and recovery.

SQL> conn / as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

primarydb:/opt/oracle/oradata/primarydb>rman target / catalog rman/rman@rmanp

Recovery Manager: Release 9.2.0.6.0 – Production

connected to target database: PRİMARYDB (DBID=4054272425)

connected to recovery catalog database

Note – the incremental backup since available is restored before applying the archive log files. The archive log files that will be applied are those that have been generated after the Level 1 incremental backup.

Since we are restoring the database to a point in the past, we will have to open the database with the RESETLOGS option.

RMAN> run {

set until time “to_date(’12/31/05 12:43:00′,’mm/dd/yy hh24:mi:ss’)”;

restore database;

recover database;

}

executing command: SET until clause

Starting restore at 31-DEC-19

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=14 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /opt/oracle/oradata/primarydb/system01.dbf

restoring datafile 00002 to /opt/oracle/oradata/primarydb/undotbs01.dbf

restoring datafile 00003 to /opt/oracle/oradata/primarydb/drsys01.dbf

restoring datafile 00004 to /opt/oracle/oradata/primarydb/example01.dbf

restoring datafile 00005 to /opt/oracle/oradata/primarydb/indx01.dbf

restoring datafile 00006 to /opt/oracle/oradata/primarydb/odm01.dbf

restoring datafile 00007 to /opt/oracle/oradata/primarydb/tools01.dbf

restoring datafile 00008 to /opt/oracle/oradata/primarydb/users01.dbf

restoring datafile 00009 to /opt/oracle/oradata/primarydb/xdb01.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/backup/bkp.06h7m2ru_1_1 tag=TAG20051231T112654 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 31-DEC-19

Starting recover at 31-DEC-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /opt/oracle/oradata/primarydb/system01.dbf

destination for restore of datafile 00002: /opt/oracle/oradata/primarydb/undotbs01.dbf

destination for restore of datafile 00003: /opt/oracle/oradata/primarydb/drsys01.dbf

destination for restore of datafile 00004: /opt/oracle/oradata/primarydb/example01.dbf

destination for restore of datafile 00005: /opt/oracle/oradata/primarydb/indx01.dbf

destination for restore of datafile 00006: /opt/oracle/oradata/primarydb/odm01.dbf

destination for restore of datafile 00007: /opt/oracle/oradata/primarydb/tools01.dbf

destination for restore of datafile 00008: /opt/oracle/oradata/primarydb/users01.dbf

destination for restore of datafile 00009: /opt/oracle/oradata/primarydb/xdb01.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/backup/bkp.0bh7m5gu_1_1 tag=TAG20051231T121214 params=NULL

channel ORA_DISK_1: restore complete

starting media recovery

archive log thread 1 sequence 174 is already on disk as file /opt/oracle/oradata/primarydb/arch/arch174.log

archive log thread 1 sequence 175 is already on disk as file /opt/oracle/oradata/primarydb/arch/arch175.log

archive log thread 1 sequence 176 is already on disk as file /opt/oracle/oradata/primarydb/arch/arch176.log

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=173

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/backup/bkp.0ch7m5i1_1_1 tag=TAG20051231T121249 params=NULL

channel ORA_DISK_1: restore complete

archive log filename=/opt/oracle/oradata/primarydb/arch/arch173.log thread=1 sequence=173

archive log filename=/opt/oracle/oradata/primarydb/arch/arch174.log thread=1 sequence=174

archive log filename=/opt/oracle/oradata/primarydb/arch/arch175.log thread=1 sequence=175

media recovery complete

Finished recover at 31-DEC-19

RMAN> sql ‘alter database open resetlogs’;

sql statement: alter database open resetlogs

We will now connect to the database to confirm that our table which we had earlier dropped has been restored as well as the record count in the table matches that taken before the table drop.

primarydb:/opt/oracle/oradata/primarydb>sqlplus system/oracle

SQL> select count(*) from testschema;

  COUNT(*)

———-

    608516

Because we have done a RESETLOGS we need to reset the database in the recovery catalog.

primarydb:/opt/oracle/oradata/primarydb>rman target / catalog rman/rman@rmanp

RMAN> reset database;

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

We will now take a fresh level 0 incremental backup

RMAN> backup incremental level 0 database;

Scenario 4:    Loss of datafiles, control files, online redo log files and server parameter file

We will simulate a complete machine failure by deleting all the data files, redo log files and the control files as well.

primarydb:/opt/oracle/oradata/primarydb>rm *.*

primarydb:/opt/oracle/oradata/primarydb> cd $ORACLE_HOME/dbs

primarydb:/opt/oracle/oradata/primarydb> rm spfileprimarydb.ora

The data load program will fail as the database has crashed

primarydb:/opt/oracle/oradata/primarydb>sqlplus system/oracle

SQL> exec load_data

BEGIN load_data; END;

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 1

ORA-01110: data file 1: ‘/opt/oracle/oradata/primarydb/system01.dbf’

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

We need to restore the spfile first from the RMAN backup – we start the instance using a ‘dummy’ initialization file. Then the control file will be restored followed by the data files and finally the database will be recovered by applying any archive log files if required.

Since we are restoring the database using a backup of the control file, we need to open the database with the RESETLOGS option. Subsequently, the database has also to be reset in the recovery catalog.

primarydb:/opt/oracle/oradata/primarydb>rman target / catalog rman/rman@rmanp

RMAN> run {

2> startup nomount force;

3> }

Oracle instance started

Total System Global Area     522786352 bytes

Fixed Size                      452144 bytes

Variable Size                301989888 bytes

Database Buffers             218103808 bytes

Redo Buffers                   2240512 bytes

We then need to set the DBID – this can be obtained by listing the files generated by the control file autobackup that had been enabled earlier. The DBID can be found as part of the file name

primarydb:/opt/oracle/product9206/dbs>ls

c-4054272425-20051225-01 

………

RMAN> set dbid=5415015219;

executing command: SET DBID

RMAN> restore spfile;

RMAN> restore controlfile;

RMAN> restore database;

RMAN> sql “alter database mount”;

RMAN> recover database;

RMAN > sql ‘alter database open resetlogs’;

primarydb:/opt/oracle/oradata/primarydb>rman target / catalog rman/rman@rmanp

RMAN> reset database;

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

Connect to the database and confirm that the TESTSCHEMA table has been successfully restored.

SQL> conn system/oracle

Connected.

SQL> select count(*) from testschema;

  COUNT(*)

———-

    608516

We will now generate some redo activity to create some archive log files which we will then take a backup using RMAN

primarydb:/opt/oracle/backup>sqlplus system/oracle

SQL> exec load_data

PL/SQL procedure successfully completed.

RMAN> backup archivelog from sequence=1 until sequence=33;

( Please provide sequence numbers pertaining to your database)

Starting backup at 02-JAN-19

starting full resync of recovery catalog

full resync complete

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=17 devtype=DISK

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=1 recid=267 stamp=578670250

input archive log thread=1 sequence=2 recid=268 stamp=578670251

input archive log thread=1 sequence=3 recid=269 stamp=578670251

input archive log thread=1 sequence=4 recid=270 stamp=578670252

input archive log thread=1 sequence=5 recid=271 stamp=578670252

input archive log thread=1 sequence=6 recid=272 stamp=578670252

input archive log thread=1 sequence=7 recid=273 stamp=578670253

input archive log thread=1 sequence=8 recid=274 stamp=578670253

input archive log thread=1 sequence=9 recid=275 stamp=578670253

input archive log thread=1 sequence=10 recid=276 stamp=578670253

input archive log thread=1 sequence=11 recid=277 stamp=578670253

input archive log thread=1 sequence=12 recid=278 stamp=578670253

input archive log thread=1 sequence=13 recid=279 stamp=578670253

input archive log thread=1 sequence=14 recid=280 stamp=578670253

input archive log thread=1 sequence=15 recid=281 stamp=578670254

input archive log thread=1 sequence=16 recid=282 stamp=578670254

input archive log thread=1 sequence=17 recid=283 stamp=578670254

input archive log thread=1 sequence=18 recid=284 stamp=578670254

input archive log thread=1 sequence=19 recid=285 stamp=578670254

input archive log thread=1 sequence=20 recid=286 stamp=578670255

input archive log thread=1 sequence=21 recid=287 stamp=578670255

input archive log thread=1 sequence=22 recid=288 stamp=578670255

input archive log thread=1 sequence=23 recid=289 stamp=578670255

input archive log thread=1 sequence=24 recid=290 stamp=578670255

input archive log thread=1 sequence=25 recid=291 stamp=578670255

input archive log thread=1 sequence=26 recid=292 stamp=578670256

input archive log thread=1 sequence=27 recid=293 stamp=578670256

input archive log thread=1 sequence=28 recid=294 stamp=578670256

input archive log thread=1 sequence=29 recid=295 stamp=578670256

input archive log thread=1 sequence=30 recid=296 stamp=578670256

input archive log thread=1 sequence=31 recid=297 stamp=578670256

input archive log thread=1 sequence=32 recid=298 stamp=578670257

input archive log thread=1 sequence=33 recid=299 stamp=578670257

channel ORA_DISK_1: starting piece 1 at 02-JAN-19

channel ORA_DISK_1: finished piece 1 at 02-JAN-19

piece handle=/opt/oracle/backup/bkp.0jh7rjvr_1_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09

Finished backup at 02-JAN-19

Starting Control File and SPFILE Autobackup at 02-JAN-19

piece handle=/opt/oracle/backup/bkp_cf_c-4054272425-20060102-01 comment=NONE

Finished Control File and SPFILE Autobackup at 02-JAN-19

List the backup of archivelogs taken in the past 24 hours

RMAN> list backup of archivelog from time ‘sysdate -1’;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

——- ———- ———– ———— —————

12949809 32M        DISK        00:00:02     02-JAN-19

        BP Key: 12949810   Status: AVAILABLE   Tag: TAG20060102T134947

        Piece Name: /opt/oracle/backup/bkp.0jh7rjvr_1_1

  List of Archived Logs in backup set 12949809

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  —- ——- ———- ——— ———- ———

  1    1       731581     02-JAN-19 732999     02-JAN-19

  1    2       732999     02-JAN-19 733004     02-JAN-19

  1    3       733004     02-JAN-19 733011     02-JAN-19

  1    4       733011     02-JAN-19 733017     02-JAN-19

  1    5       733017     02-JAN-19 733050     02-JAN-19

  1    6       733050     02-JAN-19 733060     02-JAN-19

  1    7       733060     02-JAN-19 733079     02-JAN-19

  1    8       733079     02-JAN-19 733096     02-JAN-19

  1    9       733096     02-JAN-19 733103     02-JAN-19

  1    10      733103     02-JAN-19 733109     02-JAN-19

  1    11      733109     02-JAN-19 733116     02-JAN-19

  1    12      733116     02-JAN-19 733141     02-JAN-19

  1    13      733141     02-JAN-19 733182     02-JAN-19

  1    14      733182     02-JAN-19 733247     02-JAN-19

  1    15      733247     02-JAN-19 733292     02-JAN-19

  1    16      733292     02-JAN-19 733300     02-JAN-19

  1    17      733300     02-JAN-19 733307     02-JAN-19

  1    18      733307     02-JAN-19 733359     02-JAN-19

  1    19      733359     02-JAN-19 733366     02-JAN-19

  1    20      733366     02-JAN-19 733373     02-JAN-19

  1    21      733373     02-JAN-19 733415     02-JAN-19

  1    22      733415     02-JAN-19 733422     02-JAN-19

  1    23      733422     02-JAN-19 733433     02-JAN-19

  1    24      733433     02-JAN-19 733440     02-JAN-19

  1    25      733440     02-JAN-19 733455     02-JAN-19

  1    26      733455     02-JAN-19 733485     02-JAN-19

  1    27      733485     02-JAN-19 733528     02-JAN-19

  1    28      733528     02-JAN-19 733597     02-JAN-19

  1    29      733597     02-JAN-19 733689     02-JAN-19

  1    30      733689     02-JAN-19 733748     02-JAN-19

  1    31      733748     02-JAN-19 733768     02-JAN-19

  1    32      733768     02-JAN-19 733788     02-JAN-19

  1    33      733788     02-JAN-19 733794     02-JAN-19

CLEAN UP THE ENVIRONMENT

primarydb:/opt/oracle>sqlplus system/oracle

SQL> drop table testschema;

Table dropped.

SQL> drop procedure load_data;

Procedure dropped.

primarydb:/opt/oracle>sqlplus rman/rman@rmanp

SQL> select db_key,dbid from rc_database where name=’PRİMARYDB’;

    DB_KEY       DBID

———- ———-

  12887510 4054272425

SQL> execute dbms_rcvcat.unregisterdatabase(12887510,4054272425);

PL/SQL procedure successfully completed.

Confirm by connecting to the recovery catalog

primarydb:/opt/oracle>rman target / catalog rman/rman@rmanp

Recovery Manager: Release 9.2.0.6.0 – Production

connected to target database: PRİMARYDB (DBID=4054272425)

connected to recovery catalog database

RMAN> list backup of database;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of list command at 01/03/2006 14:44:19

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog