Complete Recovery With User-managed Backup.
you can perform complete recovery in the below 5 situations.
User Managed Recovery Scenarios of complete recovery.
1. Complete Closed Database Recovery. System datafile is missing(with recent backups)
2. Complete Open Database Recovery. Non system datafile is missing(with backups).
3. Complete Open Database Recovery (when the database is initially closed). Non system datafile is missing(with backups)
restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time).
you cannot recover or create datafile without backup in the following situation:
SQL> select CONTROLFILE_CREATED from v$database; CONTROLFILE_CREATED -------------------- 07-MAY-2010 01:23:43
SQL> select creation_time,name from v$datafile; CREATION_TIME NAME -------------------- --------------------------------------------- 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf
5. Restore and Recovery of a Datafile to a different location.(Disk corrupted having recent backup and recover the datafile in new Disk location).
User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can be optionally backed up.
Files to be copied:
select name from v$datafile;
select member from v$logfile; # optional
select name from v$controlfile;
1.Complete Closed Database Recovery. System tablespace is missing
If the system tablespace is missing or corrupted the database cannot be started upso a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location from recent backup, ie:
cp -p /user/backup/uman/system01.
2. startup mount;
3. recover datafile 1;
4. alter database open;
workshop1: system datafile recovery with recent backup
SQL> create user rajesh identified by rajesh;
User created.
SQL> grant dba to rajesh;
Grant succeeded.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
i manually deleted the datafile system01.dbf for testing purpose only
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 138412736 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/testdb/system01.dbf'
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> host cp /u01/app/oracle/oradata/backup/system01.dbf /u01/app/oracle/oradata/testdb/system01.dbf
system datafile restored from recent backup
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 7 12:51:16 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 138412736 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> recover datafile 1;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
.
.
.
ORA-00279: change 456039 generated at 05/07/2010 12:46:22 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc
ORA-00280: change 456039 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7hl7dr_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL> select username from dba_users
2 where username='RAJESH';
USERNAME
------------------------------
RAJESH 2.Complete Open Database Recovery. Non system tablespace is missing
If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/
2. alter tablespace offline immediate;
4. alter tablespace online;
workshop2: Non-system datafile recovery from recent backup when database is open
SQL> ALTER USER rajesh DEFAULT TABLESPACE users;
User altered.
SQL> conn rajesh/rajesh;
Connected.
SQL> create table demo(id number);
Table created.
SQL> insert into demo values(123);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from demo;
ID
----------
123
SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
i manually deleted the datafile users01.dbf for testing purpose only
SQL> conn rajesh/rajesh;
Connected.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from demo;
select * from demo
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
SQL> conn sys/oracle as sysdba;
Connected.
SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf
restore the users01.dbf datafile from recent backup to the testdb folder
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> recover tablespace users;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
.....
......
ORA-00279: change 456044 generated at 05/07/2010 12:46:28 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_%u_.arc
ORA-00280: change 456044 for thread 1 is in sequence #13
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_12_5y7hldl2_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter tablespace users online;
Tablespace altered.
SQL> conn rajesh/rajesh;
Connected.
SQL> select * from demo;
ID
----------
123 3.Complete Open Database Recovery (when the database is initially closed).Non system datafile is missing
If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open.Pre requisites: A closed or open database backup and archived logs.
1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2. alter database datafile3 offline; (tablespace cannot be used because the database is not open)
3. alter database open;
4. Use OS commands to restore the missing or corrupted datafile to its original location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/
5. recover datafile 3;
6. alter tablespace online;
workshop 3:Non system datafile is missing
SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter system switch logfile;
System altered.
SQL> select username,default_tablespace from dba_users
2 where username='RAJESH';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
RAJESH USERS
SQL> conn rajesh/rajesh;
Connected.
SQL> create table testtbl (id number);
Table created.
SQL> insert into testtbl values(786);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testtbl;
ID
----------
786
SQL> conn sys/oracle as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --manually deleting the users01.dbf datafile from testdb folder
warning:for testing purpose only
SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 142607040 bytes
Database Buffers 297795584 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
SQL> alter database datafile 4 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf
copying user01.dbf from the recent backup to the testdb folder
SQL> recover datafile 4;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7
Specify log: { =suggested | filename | AUTO | CANCEL} auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
......
.........
ORA-00279: change 456046 generated at 05/07/2010 12:46:29 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_%u_.arc
ORA-00280: change 456046 for thread 1 is in sequence #14
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_5y7hlfbc_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> conn rajesh/rajesh;
Connected.
SQL> select * from testtbl;
ID
----------
786
4.Recovery of a Missing Datafile that has no backups (database is open).
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.
Pre requisites: All relevant archived logs.
1. alter tablespace offline immediate;
2. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf';
3. recover tablespace ;
4. alter tablespace 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'
restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time).
workshop 4: Missing Non-system Datafile having no backups
SQL> alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss'; Session altered. SQL> select controlfile_created from v$database; CONTROLFILE_CREATED -------------------- 07-MAY-2010 16:27:22 SQL> col name format a45 SQL> select creation_time,name from v$datafile; CREATION_TIME NAME -------------------- --------------------------------------------- 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf you cannot re-create the any one of the listed above datafile , without backup. SQL> create tablespace testing datafile 2 '/u01/app/oracle/oradata/testdb/test01.dbf' size 2m; Tablespace created. SQL> select creation_time,name from v$datafile; CREATION_TIME NAME -------------------- --------------------------------------------- 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf 07-MAY-2010 16:32:07 /u01/app/oracle/oradata/testdb/test01.dbf we can re-create test01.dbf file without backup. SQL> select controlfile_created from v$database; CONTROLFILE_CREATED -------------------- 07-MAY-2010 16:27:22 ---we can recover the datafile test01.dbf without backup using
create datafile command in recovery ---in this example i am going to create a table in testing tablespace
and deleted the test01.dbf datafile and recover it without backup and
create datafile recovery command.
SQL> create user jay identified by jay
2 default tablespace testing;
User created.
SQL> grant dba to jay;
Grant succeeded.
SQL> select username,default_tablespace from dba_users
2 where username='JAY';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
JAY TESTING
SQL> conn jay/jay;
Connected.
SQL> create table demo (id number);
Table created.
SQL> insert into demo values(321);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from demo;
ID
----------
321
SQL> conn sys/oracle as sysdba;
Connected.
SQL> host rm -rf /u01/app/oracle/oradata/testdb/test01.dbf
---manually deleting datafile test01.dbf for testing purpose
SQL> conn jay/jay;
Connected.
SQL> select * from demo;
ID
----------
321
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from demo;
select * from demo
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database datafile 5 offline;
Database altered.
----TO CREATE A NEW RECOVERED DATAFILE IN SAME LOCATION.
SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf';
Database altered.
----TO CREATE A NEW RECOVERED DATAFILE IN DIFFERENT LOCATION.
SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf' as '/u03/oradata/test01.dbf';
Database altered.
SQL> recover datafile 5;
ORA-00279: change 454443 generated at 05/07/2010 16:32:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 454443 for thread 1 is in sequence #8
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 454869 generated at 05/07/2010 16:41:38 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_9_%u_.arc
ORA-00280: change 454869 for thread 1 is in sequence #9
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_5y7xcbrm_.arc' no longer needed for this recovery
.....
.......
ORA-00279: change 454874 generated at 05/07/2010 16:41:45 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc
ORA-00280: change 454874 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7xck8j_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> conn jay/jay;
Connected.
SQL> select * from demo;
ID
----------
321
SQL>
5.Restore and Recovery of a Datafile to a different location.
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.
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 offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. recover tablespace ;
5. alter tablespace online;
workshop 5:
SQL> create user lachu identified by lachu
2 default tablespace users;
User created.
SQL> grant dba to lachu;
Grant succeeded.
SQL> conn lachu/lachu;
Connected.
SQL> create table test_tb(id number);
Table created.
SQL> insert into test_tb values(123);
1 row created.
SQL> commit;
Commit complete.
SQL> conn sys/oracle as sysdba;
Connected.
SQL> ---manually deleting users01.dbf datafile for testing purpose
SQL> host rm -rf '/u01/app/oracle/oradata/testdb/users01.dbf'
SQL> conn lachu/lachu;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_TB TABLE
SQL> select * from test_tb;
select * from test_tb
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter database datafile 4 offline;
Database altered.
SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u03/oradata/users01.dbf
--restore datafile user01.dbf to new disk from the recent backup of the database.
SQL> alter tablespace users rename datafile
2 '/u01/app/oracle/oradata/testdb/users01.dbf' to '/u03/oradata/users01.dbf';
Tablespace altered.
SQL> recover datafile 4;
ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc
ORA-00280: change 454383 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc
ORA-00280: change 456007 for thread 1 is in sequence #8
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery
....
......
ORA-00279: change 457480 generated at 05/07/2010 13:09:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_15_%u_.arc
ORA-00280: change 457480 for thread 1 is in sequence #15
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_5y7jxlvg_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> select name from v$datafile;
NAME
---------------------------------------------
/u01/app/oracle/oradata/testdb/system01.dbf
/u01/app/oracle/oradata/testdb/undotbs01.dbf
/u01/app/oracle/oradata/testdb/sysaux01.dbf
/u03/oradata/users01.dbf ----------restored in new location (disk)
SQL> conn lachu/lachu;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_TB TABLE
SQL> select * from test_tb;
ID
----------
123
regards,
rajeshkumar govindarajan
5 comments:
Sir, i try this as per your article but in your article when backup perform datafile name change according to oracle(OMF) so how you using same file name to restore to its original location ,i tried to move omf file name to original location it done but when i performed recovery operation it's fail,bcoz RC cant find the file name..like
cp '/home/oracle/.../dsjshd_3123_.bkp' '/home/oracle/oracle/../rman'
how recover command understand datafile name..pls reply.
ashishsurana@shriramvalue.com
Sir, can u tell me how u change backup datafile name intro its original file name,bcoz at time backup data file name is different and how i can chnage that name into orginal data file name so i apply recovery command in user managaed recovery..Reply it.
hi rajesh,
i'm junior dba currently working in upgrade proj. this post is really helpful for me...
hi rajesh,
i'm junior dba... this is very useful for me... thanks for ur effort..
fantastic explanation bro
keep it up!!!!
Post a Comment