Lost all data files, control files, redo log file (9201)
Author: David Zhang (Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
Control file (hot standby) recovery using a backup
Test data before the hot spare
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 0
Next log sequence to archive 1
Current Log Sequence 1
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
15
16
25
26
4 rows selected.
SQL> TRUNCATE TABLE TEST;
Table truncated.
SQL> conn / as sysdba
Connected.
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 0
Next log sequence to archive 1
Current Log Sequence 1
SQL>
Hot stand database
SQL> conn / as sysdba
Connected.
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 0
Next log sequence to archive 1
Current Log Sequence 1
SQL> @d: / hot_backup / hotBackup_for_win
SQL> SET TERMOUT OFF
1 file has been copied.
1 file has been copied.
1 file has been copied.
1 file has been copied.
1 file has been copied.
1 file has been copied.
1 file has been copied.
1 file has been copied.
SQL>
SQL> Host Copy E: / Oracle / ORADATA / TEST / Archive /*.* D: / Hot_Backup / Archive
E: /oracle/oradata/test/archive/1_1.arc
1 file has been copied.
SQL> Host Dir D: / Hot_Backup / Archive
The volume in the driver D is Programs
The serial number of the volume is A06D-D424
D: / hot_backup / archive directory 2002-02-04 13:59
2002-02-04 13:59
2002-02-05 22:07 180, 224 1_1.arc
1 file 180,224 bytes
2 catalogs 1,623,019,520 available bytes
SQL>
Test data after making hot backup
Submit some test data (increased after backup)
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
No rows selected
SQL> INSERT INTO TEST VALUES (25);
1 row created.
SQL> INSERT INTO TEST VALUES (26);
1 row created.
SQL> commit;
COMMIT COMPLETE.
SQL> INSERT INTO TEST VALUES (27);
1 row created.
SQL> SELECT *.
25
26
27
3 rows selected.
SQL>
Open a window to create a checkpoint
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> set nls_lang = american_america.us7ascii
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 21:28:47 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i
Enterprise
Edition Release 9.2.0.1.0 - Production
With the partitioning, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.1.0 - Production
SQL> ALTER System Checkpoint;
SYSTEM altered.
SQL>
Go back to the original window and continue to add data
SQL> INSERT INTO TEST VALUES (28);
1 row created.
SQL>
Back to the window of AS Sysdba, Switch logfile
SQL> ALTER SYSTEM SWITCH LOGFILE;
SYSTEM altered.
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 1
Next log sequence to archive 3
Current Log Sequence 3
SQL>
Go back to the original window, after commit, continue to add data
SQL> commit;
COMMIT COMPLETE.
SQL> INSERT INTO TEST VALUES (29); 1 row created.
SQL>
Go back to the window of AS Sysdba, generate checkpoints
SQL> ALTER System Checkpoint;
SYSTEM altered.
SQL>
Go back to the original window, do not contact data without COMMIT
SQL> INSERT INTO TEST VALUES (30);
1 row created.
SQL> SELECT *.
25 (New DataAfter Hot Backup)
26
(commit)
27
(Checkpoint)
Twist
(Switch logfile)
(commit)
29
(Checkpoint)
30
(shutdown abort)
Rows SELECTED.
SQL>
Can only be restored to all commit data before Switch logfile, namely: 26
Back to the AS SYSDBA window, analog database Crash
SQL> Shutdown Abort
Oracle Instance Shut Down.
SQL>
Delete all database files (data files, control files, redo log file)
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> DEL E: / Oracle / ORA92 / TEST /*.
E: / Oracle / ORA92 / TEST /*. Is it confirmed (Y / N)? Y
C: /> DIR E: / Oracle / ORA92 / TEST
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
2002-01-13 21:59
2002-01-13 21:59
0 file 0 bytes
2 catalogs 2, 803, 036, 160 available bytes
C: /> EXIT
SQL> Startup
ORA-12571: TNS: Packet Writer Failure
SQL> conn / as sysdba
Connected to an iDLE Instance.
SQL> Startup
Oracle Instance Started.
Total System Global Area 135338868 BYtes
Fixed size 453492 bytes
Variable size 109051904 bytes
Database buffers 25165824 BYTES
Redo buffers 667648 bytes
ORA-00205: Error In Identifying ControlFile, Check Alert log for more info
SQL> Shutdown Abort
Oracle Instance Shut Down.
SQL>
Restore database
Copy the nearest hot spare
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> COPY D: / Hot_backup /*.* E: / Oracle / ORA92 / TESTD: /HOT_BACKUP/HOTBACKUP_FOR_WIN.SQL
D: /hot_backup/HotBackup.sql
D: /hot_backup/HotBackup.log
D: /hot_backup/system01.dbf
D: /hot_backup/undotbs01.dbf
D: /hot_backup/drsys01.dbf
D: /hot_backup/indx01.dbf
D: /hot_backup/tools01.dbf
D: /hot_backup/users01.dbf
D: /hot_backup/xdb01.dbf
D: /hot_backup/rman01.dbf
D: /hot_backup/control02.ctl
D: /hot_backup/test_ora_1024.trc
13 files have been copied.
C: /> DIR E: / Oracle / ORA92 / TEST
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
2002-01-13 21:59
2002-01-13 21:59
2002-02-05 21:52 367, 009, 792 system01.dbf
2002-02-05 21:52 52, 436, 992 undotbs01.dbf
2002-02-05 21:52 104, 865, 792 drsys01.dbf
2002-02-05 21:52 31, 465, 472 indx01.dbf
2002-02-05 21:52 31, 465, 472 Tools01.dbf
2002-02-05 21:52 31, 465, 472 users01.dbf
2002-02-05 21:52 52, 436, 992 xdb01.dbf
2002-02-05 21:52 52, 436, 992 rman01.dbf
2002-02-05 22:07 1,875,968 control02.ctl
2002-02-05 22:07 4,905 Test_ora_1024.trc
10 files 725, 463, 849 bytes
2 catalog 2,077,544,448 available bytes
C: /> COPY E: /Oracle/ora92/test/control02.ctl e: /oracle/ora92/test/control03.ctl
1 file has been copied.
C: /> DIR E: / Oracle / ORA92 / TEST
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
2002-01-13 21:59
2002-01-13 21:59
2002-02-05 22:07 1,875,968 control03.ctl
2002-02-05 21:52 367, 009, 792 system01.dbf
2002-02-05 21:52 52, 436, 992 undotbs01.dbf
2002-02-05 21:52 104, 865, 792 drsys01.dbf
2002-02-05 21:52 31, 465, 472 indx01.dbf2002-02-05 21:52 31, 465, 472 Tools01.dbf
2002-02-05 21:52 31, 465, 472 users01.dbf
2002-02-05 21:52 52, 436, 992 xdb01.dbf
2002-02-05 21:52 52, 436, 992 rman01.dbf
2002-02-05 22:07 1,875,968 control02.ctl
2002-02-05 22:07 4,905 Test_ora_1024.trc
11 files 727, 339, 817 bytes
2 catalog 2,075,668,480 available bytes
C: />
Load but do not open the database
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> set nls_lang = american_america.us7ascii
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 20:58:25 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to an iDLE Instance.
SQL> Startup Nomount
Oracle Instance Started.
Total System Global Area 135338868 BYtes
Fixed size 453492 bytes
Variable size 109051904 bytes
Database buffers 25165824 BYTES
Redo buffers 667648 bytes
SQL> ALTABASE MOUNT;
Database altered.
SQL>
Recover the database using the Using Backup ControlFile
SQL> Recover data;
ORA-00283: Recovery Session Canceled Due To ErrorS
ORA-01610: Recovery Using The Backup ControlFile Option Must Be Done
SQL> Recover Database Using Backup ControlFile;
ORA-00279: Change 1088428 generated at 02/05/2002 22:03:48 Needed for thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_1.arc
ORA-00280: Change 1088428 for Thread 1 is in sequence # 1
Specify log: {
Auto
ORA-00279: Change 1088540 generated at 02/05/2002 22:07:14 Needing for thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_2.arcora-00280: change 1088540 for thread 1 is in sequence # 2
ORA-00278: log file 'E: /oracle/oradata/test/archive/1_1.arc' no longer needed
For this recovery
ORA-00279: Change 1088612 generated at 02/05/2002 22:10:14 Needed for thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_3.arc
ORA-00280: Change 1088612 for Thread 1 Is in Sequence # 3
ORA-00278: log file 'e: /oracle/oradata/test/archive/1_2.arc' no longer neseded
For this recovery
ORA-00308: Cannot Open Archived log 'E: /oracle/oradata/test/archive/1_3.arc'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: Unable to open the file
O / S-Error: (OS 2) The system cannot find the specified file.
SQL>
Recover again using the unsilfile of Until Cancel Useing Backup ControlFile
SQL> ALTER DATABASE OPEN RESETLOGS;
Alter Database Open Resetlogs
*
Error At Line 1:
ORA-01113: File 1 Needs Media Recovery
ORA-01110: Data File 1: 'E: /oracle/ora92/test/system01.dbf'
SQL> Recover Database Until Cancel Using Backup ControlFile;
ORA-00279: Change 1088612 generated at 02/05/2002 22:10:14 Needed for thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_3.arc
ORA-00280: Change 1088612 for Thread 1 Is in Sequence # 3
Specify log: {
Cancel
Media recovery canceled.
SQL>
Open database using resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
Please note the status of the update time of the database file at this time:
SQL> Host Dir E: / Oracle / ORA92 / TEST
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
2002-01-13 21:59
2002-01-13 21:59
2002-02-05 22:07 1,875,968 control03.ctl
2002-02-05 22:25 31,457,792 redo01.log2002-02-05 22:25 31,457,792 redo02.log
2002-02-05 22:25 31, 457, 792 redo03.log
2002-02-05 22:25 1,049,088 redo04a.log
2002-02-05 22:25 1,049,088 redo04b.log
2002-02-05 22:25 367, 009, 792 system01.dbf
2002-02-05 22:25 52, 436, 992 undotbs01.dbf
2002-02-05 22:25 104, 865, 792 Drsys01.dbf
2002-02-05 22:25 31, 465, 472 indx01.dbf
2002-02-05 22:25 31, 465, 472 Tools01.dbf
2002-02-05 22:25 31, 465, 472 users01.dbf
2002-02-05 22:25 52, 436, 992 xdb01.dbf
2002-02-05 22:25 52, 436, 992 rman01.dbf
2002-02-05 22:07 1,875,968 control02.ctl
2002-02-05 22:07 4,905 Test_ora_1024.trc
16 files 823, 811, 369 bytes
2 catalogs 1,979,166,720 available bytes
SQL>
Create temporary files for temporary tablespace
SQL> ALTER TABLESPACE TEMP
2 Add Tempfile 'E: /oracle/ora92/test/temp01.dbf' size 20m;
TableSpace altered.
SQL> Host Dir E: / Oracle / ORA92 / TEST
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
2002-01-13 21:59
2002-01-13 21:59
2002-02-05 22:07 1,875,968 control03.ctl
2002-02-05 22:25 31, 457, 792 redo01.log
2002-02-05 22:25 31, 457, 792 redo02.log
2002-02-05 22:25 31, 457, 792 redo03.log
2002-02-05 22:25 1,049,088 redo04a.log
2002-02-05 22:25 1,049,088 redo04b.log
2002-02-05 22:25 367, 009, 792 system01.dbf
2002-02-05 22:25 52, 436, 992 undotbs01.dbf
2002-02-05 22:25 104, 865, 792 Drsys01.dbf
2002-02-05 22:25 31, 465, 472 indx01.dbf
2002-02-05 22:25 31, 465, 472 Tools01.dbf2002-02-05 22:25 31,465,472 users01.dbf
2002-02-05 22:25 52, 436, 992 xdb01.dbf
2002-02-05 22:25 52, 436, 992 rman01.dbf
2002-02-05 22:07 1,875,968 control02.ctl
2002-02-05 22:07 4,905 Test_ora_1024.trc
2002-02-05 22:27 20,979,712 TEMP01.DBF
17 files 844,791,081 bytes
2 catalog 1,958,187,008 available bytes
SQL>
Verify recovery
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 0
Next log sequence to archive 1
Current Log Sequence 1
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
Fly
------------
25
26
SQL>
Lost some data commit data, because those data are in REDO, yet not filed
