Lost all data file recovery
(Excluding Control and REDO)
Author: David Zhang (Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
Hot stand database
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tuesday February 5 17:39:48 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> @d: / hot_backup / hotBackup_for_win
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> Host Copy E: / Oracle / ORADATA / TEST / Archive /*.* D: / Hot_Backup / Archive
E: /oracle/oradata/test/archive/1_74.arc
E: /oracle/oradata/test/archive/1_75.arc
E: /oracle/oradata/test/archive/1_76.arc
E: /oracle/oradata/test/archive/1_77.arc
E: /oracle/oradata/test/archive/1_78.arc
E: /oracle/oradata/test/archive/1_79.arc
E: /oracle/oradata/test/archive/1_80.arc
E: /oracle/oradata/test/archive/1_81.arc
E: /oracle/oradata/test/archive/1_82.arc
E: /oracle/oradata/test/archive/1_83.arc
E: /oracle/oradata/test/archive/1_84.arc
E: /oracle/oradata/test/archive/1_85.arc
12 files have 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 15:29 2,560 1_74.arc
2002-02-05 15:29 1,024 1_75.arc
2002-02-05 15:29 1,024 1_76.arc
2002-02-05 15:33 45,056 1_77.arc
2002-02-05 16:37 46,080 1_78.arc
2002-02-05 16:38 1,536 1_79.arc2002-02-05 16:46 1,536 1_80.arc
2002-02-05 16:54 142, 848 1_81.arc
2002-02-05 16:54 1,024 1_82.arc
2002-02-05 16:54 2,560 1_83.arc
2002-02-05 17:33 109, 568 1_84.arc
2002-02-05 17:43 177, 152 1_85.arc
12 files 531,968 bytes
2 catalogs 1,622,589,440 available bytes
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest profile log sequence 83
Next Archive Log Sequence 86
Current log sequence 86
SQL>
Simulate all data file damage
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> Copy AdobeWeb.log E: /oacle/ora92/test/xdb01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/USERS01.DBF
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/undotbs01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/tools01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/system01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/rman01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/indx01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/drsys01.dbf
1 file has been copied.
C: /> Copy AdobeWeb.log E: /oracle/ora92/test/temp01.dbf
Change E: / ORACLE/Ora92/test/temp01.dbf? (Yes / No / ALL): ALL
1 file has been copied.
C: />
C: /> DIR E: / ORACLE / ORA92 / TEST / *. DBF
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
2002-01-15 19:02 0 drsys01.dbf
2002-01-15 19:02 0 index01.dbf
2002-01-15 19:02 0 rman01.dbf
2002-01-15 19:02 0 system01.dbf2002-01-15 19:02 0 Temp01.dbf
2002-01-15 19:02 0 Tools01.dbf
2002-01-15 19:02 0 Undotbs01.dbf
2002-01-15 19:02 0 Users01.dbf
2002-01-15 19:02 0 xdb01.dbf
9 files 0 bytes
0 directory 2,702,540,800 available bytes
C: /> EXIT
SQL>
Write the database when the database is wrong, the checkpoint is generated when the database will Crash
SQL> conn / as sysdba
connected.
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest profile log sequence 83
Next Archive Log Sequence 86
Current log sequence 86
SQL> Conn Lunar / Lunar
connected.
SQL> SELECT *.
twenty two
twenty three
100
111
4 lines have been selected.
SQL> INSERT INTO TEST VALUES (222);
INSERT INTO TEST VALUES (222)
*
Error is located on the first line:
ORA-01115: IO errors when reading blocks from file 6 (block # 35)
ORA-01110: Data file 6: 'E: /oracle/ora92/test/USERS01.DBF'
ORA-27091: SKGFQIO: I / O operation could not be performed
ORA-27070: SKGFDISP: Asynchronous Read / write failed
OSD-04006: readfile () failed, unable to read files
O / S-Error: (OS 38) arrives at the end of the file.
SQL> conn / as sysdba
connected.
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest profile log sequence 83
Next Archive Log Sequence 86
Current log sequence 86
SQL> ALTER SYSTEM SWITCH LOGFILE;
The system has changed.
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest summary log sequence 84
Next Archive Log Sequence 87
Current log sequence 87
SQL> ALTER System Checkpoint;
Alter System Checkpoint
*
Error is located on the first line:
ORA-03113: End of the file of the communication channel
SQL> conn / as sysdba
The idle routine is connected.
SQL> Startup
Oracle routines have been started.
Total System Global Area 135338868 BYtes
Fixed size 453492 bytes
Variable size 109051904 bytesdatabase buffers 25165824 bytes
Redo buffers 667648 bytes
The database is loaded.
ORA-01122: Database file 1 Verification failed
ORA-01110: Data file 1: 'E: /oracle/ora92/test/system01.dbf'
ORA-01251: Unknown file title version of the file number 1
SQL>
Restore database
Copy all the data files of the hot standby
SQL> Startup
Oracle routines have been started.
Total System Global Area 135338868 BYtes
Fixed size 453492 bytes
Variable size 109051904 bytes
Database buffers 25165824 BYTES
Redo buffers 667648 bytes
The database is loaded.
ORA-01122: Database file 1 Verification failed
ORA-01110: Data file 1: 'E: /oracle/ora92/test/system01.dbf'
ORA-01251: Unknown file title version of the file number 1
SQL> Shutdown Immediate;
ORA-01109: Database is not open
The database has been uninstalled.
Oracle routines have been closed.
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> Copy D: / Hot_backup / *. Dbf E: / Oracle / ORA92 / TEST
D: /hot_backup/system01.dbf
Change E: / ORACLE/Ora92/test/system01.dbf? (YES / NO / ALL): ALL
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
8 files have been copied.
C: /> EXIT
SQL>
Load but do not open the database
SQL> Startup Mount
Oracle routines have been started.
Total System Global Area 135338868 BYtes
Fixed size 453492 bytes
Variable size 109051904 bytes
Database buffers 25165824 BYTES
Redo buffers 667648 bytes
The database is loaded.
SQL>
Restore database
SQL> Recover data;
Complete media recovery.
SQL>
Open the database
SQL> ALTABASE OPEN;
The database has been changed.
SQL>
Verify recovery
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest profile log sequence 85
Next Archive Log Sequence 88
Current log sequence 88
SQL> Conn Lunar / Lunar
connected.
SQL> SELECT *.
twenty two
twenty three
100
111
4 lines have been selected.
SQL>