How to Perform User Managed Inconsistent Backup & Recovery (With Example)

Reader Mode

1. Backup all data file and Control file by using following commands on every Sunday.

(BACKUP SCRIPTS)

host ocopy C:OracleRDBMS*.001 e:temp
host del C:OracleRDBMS*.001 /q
set echo on
alter tablespace system begin backup;
host ocopy E:LOCALORADATASYSTEM.DBF e:bu
alter tablespace system end backup;
alter tablespace undotbs begin backup;
host ocopy E:LOCALORADATAUNDOTBS.DBF e:bu
alter tablespace undotbs end backup;
alter tablespace data begin backup;
host ocopy E:LOCALORADATADATA.DBF e:bu
alter tablespace data end backup;
alter tablespace “INDEX” begin backup;
host ocopy E:LOCALORADATAFLEX_INDEX.DBF e:bu
alter tablespace “INDEX” end backup;
alter database backup controlfile to ‘e:bucontrol01.bak’ reuse ;
host ocopy C:OracleRDBMS*.001 e:bu /q

2. Backup all Archivelog files by using following commands on Daily Basic.

host ocopy C:OracleRDBMS*.001 e:localbu /q

3. Suppose You have Sunday hot backup and you have also Monday to Wednesday archive log file backup. Suppose any data file corrupt or missed on Thursday, how will you recover database up to Wednesday.

4. When you triying to open database following error accore:

SQL> startup
ORACLE instance started.

Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 62914560 bytes
Database Buffers 54525952 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘E:LOCALORADATASYSTEM.DBF’

5. Check backup status and messing file (Required recovery) and current log sicquence of all data file.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 FILE NOT FOUND 0
2 NOT ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 314021 13-OCT-06
4 NOT ACTIVE 314027 13-OCT-06

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
———- ——- ——- —————————————————————– ———- ———
1 ONLINE ONLINE FILE NOT FOUND 0

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 304 52428800 1 NO CURRENT 335069 14-OCT-06
2 1 302 52428800 1 YES INACTIVE 335067 14-OCT-06
3 1 303 52428800 1 YES INACTIVE 335068 14-OCT-06

6. Restore datafile from backup location.

7. Try to open database by using following command:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘E:LOCALORADATASYSTEM.DBF’

8. SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 334936 14-OCT-06
2 NOT ACTIVE 334942 14-OCT-06
3 ACTIVE 334949 14-OCT-06
4 NOT ACTIVE 334977 14-OCT-06

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
———- ——- ——- —————————————————————– ———- ———
1 ONLINE ONLINE 334936 14-OCT-06
3 ONLINE ONLINE 334949 14-OCT-06

SQL> select * from v$log;

SQL> select * from v$log_history;

RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#
———- ———- ———- ———- ————- ——— ————
1 603754350 1 1 313930 13-OCT-06 314115
2 603754360 1 2 314115 13-OCT-06 314116
3 603754369 1 3 314116 13-OCT-06 314118
4 603754369 1 4 314118 13-OCT-06 314119
5 603754374 1 5 314119 13-OCT-06 314120
6 603754374 1 6 314120 13-OCT-06 314121
7 603754379 1 7 314121 13-OCT-06 314122
8 603754379 1 8 314122 13-OCT-06 314123
9 603754385 1 9 314123 13-OCT-06 314124
10 603754385 1 10 314124 13-OCT-06 314125
11 603754390 1 11 314125 13-OCT-06 314126

303 603822241 1 303 335068 14-OCT-06 335069

SQL> recover datafile 1;
ORA-00279: change 334936 generated at 10/14/2006 16:13:22 needed for thread 1
ORA-00289: suggestion : C:ORACLERDBMSARC00208.001
ORA-00280: change 334936 for thread 1 is in sequence #208

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO

SQL*Plus: Release 9.0.1.0.1 – Production on Fri Oct 13 21:20:42 2006

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where ::= [/][@] | /

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning option
JServer Release 9.0.1.1.1 – Production

SQL> set linesize 7777
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 1 52428800 1 NO CURRENT 313930 13-OCT-06
2 1 0 52428800 1 YES UNUSED 0
3 1 0 52428800 1 YES UNUSED 0

SQL> select * from v$recover_file;

no rows selected

SQL> select * from v$bachup;
select * from v$bachup
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 251373 12-OCT-06
2 NOT ACTIVE 251381 12-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 314007 13-OCT-06
2 ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 314007 13-OCT-06
2 ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 314007 13-OCT-06
2 ACTIVE 314014 13-OCT-06
3 NOT ACTIVE 251388 12-OCT-06
4 NOT ACTIVE 251396 12-OCT-06

SQL> /