Recovery Series 2 - Files Loss in Archiving Mode

- Reform method does not realize the recovery of system table space

SQL> INSERT INTO TEST_EXCEL VALUES ('26', TO_DATE ('2008-02-22', 'YYYY-MM-DD'), 'TEST3');

It has created a row.

SQL> ALTER SYSTEM SWITCH LOGFILE;

The system has changed.

SQL> ALTER SYSTEM SWITCH LOGFILE;

The system has changed. SQL> CONN / AS SYSDBA is connected. SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed. SQL> HostMicrosoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp.

C: /> del d: /oracle9/oradata/nbxtdb/nbxttp.ora successfully removed NBXTTP. ORA

C: /> EXIT

SQL> StartUporacle routines have been started.

Total System Global Area 378608760 Bytesfixed Size 453752 bytesvariable size 167772160 bytesdatabase buffers 209715200 BYTESREDO BUFFERS 667648 BYTES Database Loading. ORA-01157: Unable to identify / lock data files 9 - See DBWR Tracking file ORA-01110: Data file 9: 'D: /oracle9/oradata/nbxtdb/nbxttp.ora'

SQL> SELECT * FROM V $ Recover_File;

File # online online _--------------- ------- Error Change # ------------------- ---------------------------------------------- ---- ------ Time ---------- 9 Online OnlineFile Not Found 0

SQL> ALTER DATABASE DATAFILE 9 OFFLINE DROP;

The database has been changed.

SQL> ALTABASE OPEN;

The database has been changed.

SQL> HostMicrosoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp.

C: /> COPY E: / ORAHOTBAK/NBXTTTP.ORA D: / ORACLE9 / ORADATA / NBXTDB has copied 1 file.

C: /> EXIT

SQL> Recover DataFile 9; ORA-00279: Change 356135 (Generated at 05/27/2004 20:50:36) For thread 1 is an essentible ORA-00289: Suggest: d: / Oracle9 / ORADATA / NBXTDB / ARCHIVE / ARC00100100 .Arcora-00280: Change 356135 For thread 1 is made according to sequence # 100

Specify log: { = suggested | filename | auto | ca Zan} autoora-00279: Change 356192 (Generated at 05/27/2004 20:50:40) For thread 1 is an essentible ORA-00289: Recommendation: D: / ORACORA-00280: Change 356192 For thread 1 is ORA-00278 performed in sequence # 101: This recovery no longer needs log file 'D: / Oracle9 / ORADATA / NBXTDB / Archive / Arc00100100.arc'ora-00279: Change 356702 (Generated at 05/27/2004 20:58:10) For thread 1 is an essentible ORA-00289: Suggestion: d: /oacle9/oradata/nbxtdb/archive/arc00100102.arcora -00280: Change 356702 For thread 1 is ORA-00278 performed in sequence # 102: This recovery no longer needs log file 'd: /oracle9/oradata/nbxtdb/archive/ARC00100101.ARC'

Applicable logs. Complete media recovery. SQL> ALTER DATABASE DATAFILE 9 Online;

The database has been changed.

SQL> CONN TEST / TEST; SQL> SELECT * from test_excel;

Accident Code Time Type ----------------------------------------- ----- 23 22- February -08 TEST125 22-February -08 TEST226 22-February -08 TEST32 24-November -03 Vehicle damage 12 07-February -03 Height fall from 13 21 to October -03 Height fall from 1 22 to March -02 22

7 lines have been selected.

SQL>