Prerequisites
Create a Oracle 9i database with the name DBA01 running in ARCHIVELOG mode.
For simplicity the database can be created with all files
located under one common area:
For example “/opt/oracle/oradata/dba01/”
Archive log files can be located at “/opt/oracle/oradata/dba01/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.
dba01:/opt/oracle>rman target / catalog rman/rman@rmanp
Recovery Manager: Release
9.2.0.6.0 - Production
Copyright (c) 1995, 2002,
Oracle Corporation. All rights reserved.
connected to target database:
DBA01 (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/dba01/system01.dbf
2 204800 UNDOTBS1 YES /opt/oracle/oradata/dba01/undotbs01.dbf
3 20480 DRSYS NO /opt/oracle/oradata/dba01/drsys01.dbf
4 140160 EXAMPLE NO
/opt/oracle/oradata/dba01/example01.dbf
5 25600 INDX NO /opt/oracle/oradata/dba01/indx01.dbf
6 20480 ODM NO /opt/oracle/oradata/dba01/odm01.dbf
7 10240 TOOLS NO /opt/oracle/oradata/dba01/tools01.dbf
8 25600 USERS NO /opt/oracle/oradata/dba01/users01.dbf
9 39040 XDB NO /opt/oracle/oradata/dba01/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_dba01.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-05
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/dba01/system01.dbf
input datafile fno=00002
name=/opt/oracle/oradata/dba01/undotbs01.dbf
input datafile fno=00004
name=/opt/oracle/oradata/dba01/example01.dbf
input datafile fno=00009
name=/opt/oracle/oradata/dba01/xdb01.dbf
input datafile fno=00005
name=/opt/oracle/oradata/dba01/indx01.dbf
input datafile fno=00008
name=/opt/oracle/oradata/dba01/users01.dbf
input datafile fno=00003
name=/opt/oracle/oradata/dba01/drsys01.dbf
input datafile fno=00006
name=/opt/oracle/oradata/dba01/odm01.dbf
input datafile fno=00007
name=/opt/oracle/oradata/dba01/tools01.dbf
channel ORA_DISK_1: starting
piece 1 at 31-DEC-05
channel ORA_DISK_1: finished
piece 1 at 31-DEC-05
piece
handle=/opt/oracle/backup/bkp.01h7ltaj_1_1 comment=NONE
channel ORA_DISK_1: backup
set complete, elapsed time: 00:01:15
Finished backup at 31-DEC-05
Starting Control File and
SPFILE Autobackup at 31-DEC-05
piece
handle=/opt/oracle/backup/bkp_cf_c-4054272425-20051231-00 comment=NONE
Finished Control File and
SPFILE Autobackup at 31-DEC-05
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
------- -- -- - -----------
--------------- ------- ------- ---
12888157 B 0 A
DISK 31-DEC-05 1
1 TAG20051231T095219
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV
------- ---- -- ----------
----------- ------------ ---------------
12888169 Full 1M
DISK 00:00:00 31-DEC-05
BP Key: 12888170 Status: AVAILABLE Tag:
Piece Name:
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-00
Controlfile Included: Ckp SCN: 183178 Ckp time: 31-DEC-05
RMAN> list backup of spfile;
List of Backup Sets
===================
BS Key Type LV
------- ---- -- ----------
----------- ------------ ---------------
12888169 Full 0
DISK 00:00:00 31-DEC-05
BP Key: 12888170 Status: AVAILABLE Tag:
Piece Name:
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-00
SPFILE Included: Modification time: 31-DEC-05
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 myobjects
tablespace users
as select * from dba_objects;
create or
replace procedure
load_data
is
begin
for i in 1 .. 10
loop
insert into
myobjects
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 myobjects;
COUNT(*)
----------
318746
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/dba01/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/dba01/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;
dba01:/opt/oracle>ls -lrt /opt/oracle/oradata/dba01/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-05
BP Key: 12889316 Status: AVAILABLE Tag: TAG20051231T104955
Piece Name:
/opt/oracle/backup/bkp.03h7m0mk_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-05 169683 31-DEC-05
1
85 169683 31-DEC-05 172518 31-DEC-05
1
86 172518 31-DEC-05 175256 31-DEC-05
1
87 175256 31-DEC-05 177990 31-DEC-05
1
88 177990 31-DEC-05 180724 31-DEC-05
1
89 180724 31-DEC-05 183489 31-DEC-05
1
90 183489 31-DEC-05 186278 31-DEC-05
1
91 186278
31-DEC-05 189061 31-DEC-05
1
92 189061 31-DEC-05 189120 31-DEC-05
1
93 189120 31-DEC-05 189128 31-DEC-05
1
94 189128 31-DEC-05 189137 31-DEC-05
1
95 189137 31-DEC-05 190732 31-DEC-05
1
96 190732 31-DEC-05 190977 31-DEC-05
1
97 190977 31-DEC-05 190986 31-DEC-05
1
98 190986 31-DEC-05 190996 31-DEC-05
1
99 190996 31-DEC-05 191014 31-DEC-05
1
100 191014
31-DEC-05 191055 31-DEC-05
1
101 191055 31-DEC-05 191063 31-DEC-05
1
102 191063 31-DEC-05 191077 31-DEC-05
1
103 191077 31-DEC-05 191087 31-DEC-05
1
104 191087 31-DEC-05 191100 31-DEC-05
1
105 191100 31-DEC-05 191112 31-DEC-05
1
106 191112 31-DEC-05 191124 31-DEC-05
1
107 191124 31-DEC-05 191133 31-DEC-05
1
108 191133 31-DEC-05 191145 31-DEC-05
1
109 191145 31-DEC-05 191156 31-DEC-05
1
110 191156 31-DEC-05 191166 31-DEC-05
1
111 191166 31-DEC-05 191177 31-DEC-05
1
112 191177 31-DEC-05 191192 31-DEC-05
1
113 191192 31-DEC-05 191200 31-DEC-05
1
114 191200 31-DEC-05 191216 31-DEC-05
1
115 191216 31-DEC-05 191230 31-DEC-05
1
116 191230 31-DEC-05 191238 31-DEC-05
1
117 191238 31-DEC-05 191253 31-DEC-05
1
118 191253 31-DEC-05 191262 31-DEC-05
1
119 191262 31-DEC-05 191275 31-DEC-05
1
120 191275 31-DEC-05 191285 31-DEC-05
1
121 191285 31-DEC-05 191293 31-DEC-05
1
122 191293 31-DEC-05 191309 31-DEC-05
1
123 191309 31-DEC-05 191317 31-DEC-05
1
124 191317 31-DEC-05 191329 31-DEC-05
1
125 191329 31-DEC-05 191346 31-DEC-05
1
126 191346 31-DEC-05 191358 31-DEC-05
1 127 191358
31-DEC-05 191370 31-DEC-05
1
128 191370 31-DEC-05 191378 31-DEC-05
1
129 191378 31-DEC-05 191395 31-DEC-05
1
130 191395 31-DEC-05 192745 31-DEC-05
1
131 192745 31-DEC-05 193033 31-DEC-05
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.
dba01:/opt/oracle/backup>ls -lrt
total 496740
-rw-r----- 1 oracle
dba 455118848 Dec 31 09:53
bkp.01h7ltaj_1_1
-rw-r----- 1 oracle
dba 1892352 Dec 31 09:53
bkp_cf_c-4054272425-20051231-00
-rw-r----- 1 oracle
dba 49178112 Dec 31 10:49
bkp.03h7m0mk_1_1
-rw-r----- 1 oracle
dba 1957888 Dec 31 10:50
bkp_cf_c-4054272425-20051231-01
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 .
dba01:/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 :
dba01:/opt/oracle/scripts>cat bkp_inc_lev0.rcv
run
{
backup incremental level 0
database plus archivelog delete input;
}
Scenario 1: Loss of a datafile
dba01:/opt/oracle/oradata/dba01>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/dba01/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.
dba01:/opt/oracle/oradata/dba01>rman target / catalog rman/rman@rmanp
Recovery Manager: Release
9.2.0.6.0 - Production
Copyright (c) 1995, 2002,
Oracle Corporation. All rights reserved.
connected to target database:
DBA01 (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-05
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/dba01/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-05
RMAN> recover datafile 8;
Starting recover at 31-DEC-05
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished recover at 31-DEC-05
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.
dba01:/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-05
Backup Piece 12888158 31-DEC-05 /opt/oracle/backup/bkp.01h7ltaj_1_1
Backup Set 12888169 31-DEC-05
Backup Piece 12888170 31-DEC-05
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-00
Backup Set 12889315 31-DEC-05
Backup Piece 12889316 31-DEC-05 /opt/oracle/backup/bkp.03h7m0mk_1_1
Backup Set 12889366 31-DEC-05
Backup Piece 12889367 31-DEC-05
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-01
Backup Set 12889556 31-DEC-05
Backup Piece 12889557 31-DEC-05 /opt/oracle/backup/bkp.05h7m2rs_1_1
Backup Set 12889581 31-DEC-05
Backup Piece 12889582 31-DEC-05
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-02
Backup Set 12896130 31-DEC-05
Backup Piece 12896131 31-DEC-05
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-03
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-05
Backup Piece 12888158 31-DEC-05 /opt/oracle/backup/bkp.01h7ltaj_1_1
Backup Set 12888169 31-DEC-05
Backup Piece 12888170 31-DEC-05 /opt/oracle/backup/bkp_cf_c-4054272425-20051231-00
Backup Set 12889315 31-DEC-05
Backup Piece 12889316 31-DEC-05 /opt/oracle/backup/bkp.03h7m0mk_1_1
Backup Set 12889366 31-DEC-05
Backup Piece 12889367 31-DEC-05 /opt/oracle/backup/bkp_cf_c-4054272425-20051231-01
Backup Set 12889556 31-DEC-05
Backup Piece 12889557 31-DEC-05 /opt/oracle/backup/bkp.05h7m2rs_1_1
Backup Set 12889581 31-DEC-05
Backup Piece 12889582 31-DEC-05 /opt/oracle/backup/bkp_cf_c-4054272425-20051231-02
Backup Set 12896130 31-DEC-05
Backup Piece 12896131 31-DEC-05
/opt/oracle/backup/bkp_cf_c-4054272425-20051231-03
Do you really want to delete
the above objects (enter YES or NO)? YES
deleted backup piece
backup piece
handle=/opt/oracle/backup/bkp.01h7ltaj_1_1 recid=1 stamp=578483543
deleted backup piece
backup piece
handle=/opt/oracle/backup/bkp_cf_c-4054272425-20051231-00 recid=2
stamp=578483615
deleted backup piece
backup piece handle=/opt/oracle/backup/bkp.03h7m0mk_1_1
recid=3 stamp=578486997
deleted backup piece
backup piece
handle=/opt/oracle/backup/bkp_cf_c-4054272425-20051231-01 recid=4
stamp=578487003
deleted backup piece
backup piece
handle=/opt/oracle/backup/bkp.05h7m2rs_1_1 recid=5 stamp=578489213
deleted backup piece
backup piece
handle=/opt/oracle/backup/bkp_cf_c-4054272425-20051231-02 recid=8
stamp=578489283
deleted backup piece
backup piece
handle=/opt/oracle/backup/bkp_cf_c-4054272425-20051231-03 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
------- -- -- - -----------
--------------- ------- ------- ---
12889562 B 0 A
DISK 31-DEC-05 1
1 TAG20051231T112654
12896580 B 1 A
DISK 31-DEC-05 1
1 TAG20051231T121214
Scenario 2: Loss of all datafiles
dba01:/opt/oracle/oradata/dba01>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/dba01/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.
dba01:/opt/oracle/oradata/dba01>rman target / catalog rman/rman@rmanp
RMAN> restore database;
Starting restore at 31-DEC-05
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/dba01/system01.dbf
restoring datafile 00002 to
/opt/oracle/oradata/dba01/undotbs01.dbf
restoring datafile 00003 to
/opt/oracle/oradata/dba01/drsys01.dbf
restoring datafile 00004 to
/opt/oracle/oradata/dba01/example01.dbf
restoring datafile 00005 to
/opt/oracle/oradata/dba01/indx01.dbf
restoring datafile 00006 to
/opt/oracle/oradata/dba01/odm01.dbf
restoring datafile 00007 to
/opt/oracle/oradata/dba01/tools01.dbf
restoring datafile 00008 to
/opt/oracle/oradata/dba01/users01.dbf
restoring datafile 00009 to
/opt/oracle/oradata/dba01/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-05
RMAN> recover database;
Starting recover at 31-DEC-05
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/dba01/system01.dbf
destination for restore of
datafile 00002: /opt/oracle/oradata/dba01/undotbs01.dbf
destination for restore of
datafile 00003: /opt/oracle/oradata/dba01/drsys01.dbf
destination for restore of
datafile 00004: /opt/oracle/oradata/dba01/example01.dbf
destination for restore of
datafile 00005: /opt/oracle/oradata/dba01/indx01.dbf
destination for restore of
datafile 00006: /opt/oracle/oradata/dba01/odm01.dbf
destination for restore of
datafile 00007: /opt/oracle/oradata/dba01/tools01.dbf
destination for restore of
datafile 00008: /opt/oracle/oradata/dba01/users01.dbf
destination for restore of
datafile 00009: /opt/oracle/oradata/dba01/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-05
RMAN> sql 'alter database open ';
sql statement: alter database
open
Let us verify that the data has been fully restored by
querying the MYOBJECTS table
dba01:/opt/oracle/oradata/dba01>sqlplus system/oracle
SQL> select count(*) from myobjects;
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-05:12:43:16
We will now drop the table MYOBJECTS 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 myobjects;
COUNT(*)
----------
608516
SQL> drop table myobjects;
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;
dba01:/opt/oracle/oradata/dba01>rman target / catalog rman/rman@rmanp
Recovery Manager: Release
9.2.0.6.0 - Production
Copyright (c) 1995, 2002,
Oracle Corporation. All rights reserved.
connected to target database:
DBA01 (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 {
2> set until time "to_date('12/31/05 12:43:00','mm/dd/yy
hh24:mi:ss')";
3> restore database;
4> recover database;
5> }
executing command: SET until
clause
Starting restore at 31-DEC-05
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/dba01/system01.dbf
restoring datafile 00002 to
/opt/oracle/oradata/dba01/undotbs01.dbf
restoring datafile 00003 to
/opt/oracle/oradata/dba01/drsys01.dbf
restoring datafile 00004 to
/opt/oracle/oradata/dba01/example01.dbf
restoring datafile 00005 to
/opt/oracle/oradata/dba01/indx01.dbf
restoring datafile 00006 to
/opt/oracle/oradata/dba01/odm01.dbf
restoring datafile 00007 to
/opt/oracle/oradata/dba01/tools01.dbf
restoring datafile 00008 to
/opt/oracle/oradata/dba01/users01.dbf
restoring datafile 00009 to
/opt/oracle/oradata/dba01/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-05
Starting recover at 31-DEC-05
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/dba01/system01.dbf
destination for restore of
datafile 00002: /opt/oracle/oradata/dba01/undotbs01.dbf
destination for restore of
datafile 00003: /opt/oracle/oradata/dba01/drsys01.dbf
destination for restore of
datafile 00004: /opt/oracle/oradata/dba01/example01.dbf
destination for restore of
datafile 00005: /opt/oracle/oradata/dba01/indx01.dbf
destination for restore of
datafile 00006: /opt/oracle/oradata/dba01/odm01.dbf
destination for restore of
datafile 00007: /opt/oracle/oradata/dba01/tools01.dbf
destination for restore of
datafile 00008: /opt/oracle/oradata/dba01/users01.dbf
destination for restore of
datafile 00009: /opt/oracle/oradata/dba01/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/dba01/arch/arch174.log
archive log thread 1 sequence
175 is already on disk as file /opt/oracle/oradata/dba01/arch/arch175.log
archive log thread 1 sequence
176 is already on disk as file /opt/oracle/oradata/dba01/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/dba01/arch/arch173.log thread=1 sequence=173
archive log
filename=/opt/oracle/oradata/dba01/arch/arch174.log thread=1 sequence=174
archive log
filename=/opt/oracle/oradata/dba01/arch/arch175.log thread=1 sequence=175
media recovery complete
Finished recover at 31-DEC-05
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.
dba01:/opt/oracle/oradata/dba01>sqlplus system/oracle
SQL> select count(*) from myobjects;
COUNT(*)
----------
608516
Because we have done a RESETLOGS we need to reset the
database in the recovery catalog.
dba01:/opt/oracle/oradata/dba01>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.
dba01:/opt/oracle/oradata/dba01>rm *.*
dba01:/opt/oracle/oradata/dba01>
cd $ORACLE_HOME/dbs
dba01:/opt/oracle/oradata/dba01>
rm spfiledba01.ora
The data load program will fail as the database has crashed
dba01:/opt/oracle/oradata/dba01>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/dba01/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.
dba01:/opt/oracle/oradata/dba01>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
dba01:/opt/oracle/product9206/dbs>ls
c-4054272425-20051225-01
………
RMAN> set dbid=1855015217;
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’;
dba01:/opt/oracle/oradata/dba01>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 MYOBJECTS table
has been successfully restored.
SQL> conn system/oracle
Connected.
SQL> select count(*) from myobjects;
COUNT(*)
----------
608516
We will now generate some redo activity to create some
archive log files which we will then take a backup using RMAN
dba01:/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-06
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-06
channel ORA_DISK_1: finished
piece 1 at 02-JAN-06
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-06
Starting Control File and
SPFILE Autobackup at 02-JAN-06
piece
handle=/opt/oracle/backup/bkp_cf_c-4054272425-20060102-01 comment=NONE
Finished Control File and
SPFILE Autobackup at 02-JAN-06
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-06
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-06 732999 02-JAN-06
1
2 732999 02-JAN-06 733004 02-JAN-06
1
3 733004 02-JAN-06 733011 02-JAN-06
1
4 733011 02-JAN-06 733017 02-JAN-06
1
5 733017 02-JAN-06 733050 02-JAN-06
1
6 733050
02-JAN-06 733060 02-JAN-06
1
7 733060 02-JAN-06 733079 02-JAN-06
1
8 733079 02-JAN-06 733096 02-JAN-06
1
9 733096 02-JAN-06 733103 02-JAN-06
1
10 733103 02-JAN-06 733109 02-JAN-06
1
11 733109 02-JAN-06 733116 02-JAN-06
1
12 733116 02-JAN-06 733141 02-JAN-06
1
13 733141 02-JAN-06 733182 02-JAN-06
1
14 733182 02-JAN-06 733247 02-JAN-06
1 15
733247 02-JAN-06 733292 02-JAN-06
1
16 733292 02-JAN-06 733300 02-JAN-06
1
17 733300 02-JAN-06 733307 02-JAN-06
1
18 733307 02-JAN-06 733359 02-JAN-06
1
19 733359 02-JAN-06 733366 02-JAN-06
1
20 733366 02-JAN-06 733373 02-JAN-06
1
21 733373 02-JAN-06 733415 02-JAN-06
1
22 733415 02-JAN-06 733422 02-JAN-06
1
23 733422 02-JAN-06 733433 02-JAN-06
1 24 733433
02-JAN-06 733440 02-JAN-06
1
25 733440 02-JAN-06 733455 02-JAN-06
1
26 733455 02-JAN-06 733485 02-JAN-06
1
27 733485 02-JAN-06 733528 02-JAN-06
1
28 733528 02-JAN-06 733597 02-JAN-06
1
29 733597 02-JAN-06 733689 02-JAN-06
1
30 733689 02-JAN-06 733748 02-JAN-06
1
31 733748 02-JAN-06 733768 02-JAN-06
1
32 733768 02-JAN-06 733788 02-JAN-06
1 33 733788
02-JAN-06 733794 02-JAN-06
CLEAN UP THE
ENVIRONMENT
dba01:/opt/oracle>sqlplus system/oracle
SQL> drop table myobjects;
Table dropped.
SQL> drop procedure load_data;
Procedure dropped.
dba01:/opt/oracle>sqlplus
rman/rman@rmanp
SQL> select db_key,dbid
from rc_database where name='DBA01';
DB_KEY
DBID
---------- ----------
12887510 4054272425
SQL> execute
dbms_rcvcat.unregisterdatabase(12887510,4054272425);
PL/SQL procedure successfully
completed.
Confirm by connecting to the recovery catalog
dba01:/opt/oracle>rman target / catalog rman/rman@rmanp
Recovery Manager: Release
9.2.0.6.0 - Production
Copyright (c) 1995, 2002,
Oracle Corporation. All rights reserved.
connected to target database:
DBA01 (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
No comments:
Post a Comment