Lost all data files, control files, redo log file (9201)
Author: David Zhang (Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
Two use rebuild control files (hot spare) recovery
Test data before the hot spare
SQL> Conn Lunar / Lunar
Connected.
SQL> TRUNCATE TABLE TEST;
Table truncated.
SQL> INSERT INTO TEST VALUES (15);
1 row created.
SQL> INSERT INTO TEST VALUES (16);
1 row created.
SQL> commit;
COMMIT COMPLETE.
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 1
Next log sequence to archive 1
Current Log Sequence 1
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>
Test data after hot spare
Submit some test data (increased after backup)
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
15
16
2 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>
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 OPTIONSJSERVER 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 *.
NEW DATAS AFTER HOT Backup
16
25
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
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, 802, 962, 432 can be used bytes
C: />
C: /> EXIT
SQL>
Restore database
Return the nearest hot spare copy
C: /> COPY D: / Hot_backup /*.* E: / Oracle / ORA92 / TEST
D: /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_1972.trc
13 files have been copied.
C: />
C: /> EXIT
SQL>
Edit and modify the backup control file (Backup to TRACE)
Create ControlFile Reuse Database "test1" replandlogs archivelog
MaxLogfiles 5
MaxLogmembers 3
MaxDataFiles 100
MaxInstances 1
MaxLoghistory 226
Logfile
Group 1 'E: /oracle/ora92/test/redo01.log' size 30m,
Group 2 'E: /oracle/ora92/test/redo02.log' size 30m,
Group 3 'E: /oracle/ora92/test/redo03.log' size 30m,
GROUP 4
'E: /oracle/ora92/test/redo04a.log',
'E: /oracle/ora92/test/redo04b.log'
Size 1M
DataFile
'E: /oracle/ora92/test/system01.dbf',
'E: /oracle/ora92/test/undotbs01.dbf',
'E: /oracle/ora92/test/drsys01.dbf',
'E: /oracle/ora92/test/indx01.dbf',
'E: /oracle/ora92/test/tools01.dbf',
'E: /oracle/ora92/test/USERS01.DBF',
'E: /oracle/ora92/test/xdb01.dbf',
'E: /oracle/ora92/test/rman01.dbf'
Character set zhs16gbk
;
NOMOUNT database
SQL> conn / as sysdba
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>
Reconstruction control file
SQL> Create ControlFile Reuse Database "Test1" Resetlogs ArchiveLog
2 MaxLogfiles 5
3 MaxLogmembers 3
4 MaxDataFiles 100
5 MaxInstances 1
6 MaxLoghistory 226
7 logfile
8 group 1 'E: /oracle/ora92/test/redo01.log' size 30m,
9 Group 2 'E: /Oracle/ora92/test/redo02.log' size 30m,
10 Group 3 'E: /oracle/ora92/test/redo03.log' size 30m,
11 Group 4
12 'E: /oacle/ora92/test/redo04a.log',
13 'E: /oacle/ora92/test/redo04b.log'
14) SIZE 1M
15 DataFile
16 'E: /oacle/ora92/test/system01.dbf',
17 'E: /oracle/ora92/test/undotbs01.dbf',
18 'E: /oracle/ora92/test/drsys01.dbf',
19 'E: /oacle/ora92/test/indx01.dbf',
20 'E: /oacle/ora92/test/tools01.dbf',
21 'E: /oacle/ora92/test/USERS01.DBF',
22 'E: /oracle/ora92/test/xdb01.dbf',
23 'E: /oacle/ora92/test/rman01.dbf'
24 Character Set ZHS16GBK
25;
Control File Created.
SQL>
Restore database (useing backup controlfile)
SQL> Recover Database Using Backup ControlFile
ORA-00279: Change 1087799 generated at 02/05/2002 21:19:44 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_1.arc
ORA-00280: Change 1087799 for Thread 1 is in sequence # 1
Specify log: {
Auto
ORA-00279: Change 1087918 Generated at 02/05/2002 21:23:09 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_2.arc
ORA-00280: Change 1087918 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 1088081 Generated at 02/05/2002 21:31:09 Needed for thread 1OR-00289: Suggestion: E: /Oracle/oradata/test/archive/1_3.arc
ORA-00280: Change 1088081 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> 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 21:49 367, 009, 792 system01.dbf
2002-02-05 21:49 52, 436, 992 Undotbs01.dbf
2002-02-05 21:49 104, 865, 792 drsys01.dbf
2002-02-05 21:49 31, 465, 472 indx01.dbf
2002-02-05 21:49 31, 465, 472 Tools01.dbf
2002-02-05 21:49 31, 465, 472 users01.dbf
2002-02-05 21:49 52, 436, 992 xdb01.dbf
2002-02-05 21:49 52, 436, 992 rman01.dbf
2002-02-05 21:49 1,875,968 control02.ctl
2002-02-05 21:49 1,875,968 Control03.ctl
10 files 727, 334, 912 bytes
2 catalog 2,075,586,560 available bytes
SQL>
Recovery again (Until Cancel Using 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 1088081 generated at 02/05/2002 21:31:09 Needed for thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_3.arc
ORA-00280: Change 1088081 for thread 1 is in sequence # 3Specify log: {
Cancel
Media recovery canceled.
SQL>
RESETLOGS Opens Database
SQL> ALTER DATABASE OPEN RESETLOGS;
Database 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 21:52 31, 457, 792 redo01.log
2002-02-05 21:52 31, 457, 792 redo02.log
2002-02-05 21:52 31, 457, 792 redo03.log
2002-02-05 21:52 1,049,088 redo04a.log
2002-02-05 21:52 1,049,088 redo04b.log
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 21:49 1,875,968 control02.ctl
2002-02-05 21:49 1,875,968 Control03.ctl
15 files 823, 806, 464 bytes
2 catalogs 1,979,092,992 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 *.
15
16
25
26
4 rows selected.
SQL>
Lost some data commit data, because those data are in REDO, yet not filed
