Lost all data files, control files, redo log file (9201)
Author: David Zhang (Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
Four Use reconstruction control files to do (cold back) recovery
Test data before making cold backup
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:20:52 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> 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 *.
No rows selected
SQL>
Cold 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> EXIT
Disconnected from 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
Single open a window to make a cold
C: /> d:
D: /> CD D: / clonedb
D: / clonedb> cold_backup.bat> Cold_Backup.log
D: / clonedb>
View the cool log
D: / clonedb> echo beg Cold_Backup Database ...
Begin Cold_Backup Database ...
D: / clonedb> Date / T
2002-02-05 Tuesday
D: / clonedb> Time / T
23:22
D: / clonedb> sqlplus "/ as sysdba" @ shutdown.sqlsql * plus: release 9.2.0.1.0 - Production on Tue Feb 5 23:22:24 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
Connected.
Database closed.
Database dismount.
Oracle Instance Shut Down.
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
Database mounted.
Database opened.
Database closed.
Database dismount.
Oracle Instance Shut Down.
Disconnected from 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
D: / clonedb> Copy E: / Oracle / ORA92 / TEST / *. * D: / clonedb
E: /oracle/ora92/test/cold_backup.bat
E: /oracle/ora92/test/shutdown.sql
E: /oracle/ora92/test/startup.sql
E: /oracle/ora92/test/cold_backup.log
E: /oracle/ora92/test/control03.ctl
E: /oracle/ora92/test/redo01.log
E: /oracle/ora92/test/redo02.log
E: /oracle/ora92/test/redo03.log
E: /oracle/ora92/test/redo04a.log
E: /oracle/ora92/test/redo04b.log
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
E: /oracle/ora92/test/control02.ctl
E: /oracle/ora92/test/test_ora_1024.trc
E: /oracle/ora92/test/temp01.dbfe: /oracle/ora92/test/pwdtest.ora
E: /oracle/ora92/test/test_ora_1800.trc
22 files have been copied.
D: / clonedb> Copy E: /oracle/ora92/database/pwdtest.ora d: / clonedb
1 file has been copied.
D: / clonedb> Copy E: / Oracle / ORADATA / TEST / Archive D: / Clones / Archive
E: / Oracle / ORADATA / TEST / ARCHIVE / *
0 files have been copied.
D: / clonedb> sqlplus "/ as sysdba" @ startup.sql
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 23:26:37 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to an iDLE Instance.
Connected to an iDLE Instance.
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
Database mounted.
Database opened.
SYSTEM altered.
Database altered.
SYSTEM altered.
Disconnected from 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
D: / clonedb> Date / T
2002-02-05 Tuesday
D: / clonedb> Time / T
23:26
D: / clonedb> echo the fold backup database success.
The Cold Backup Database Successed.
Test data after making cold backup
Submit some test data (increased after backup)
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
Fly
------------
15
16
SQL>
SQL> INSERT INTO TEST VALUES (25);
1 row created.
SQL>
SQL> INSERT INTO TEST VALUES (26);
1 row created.
SQL>
SQL> commit;
COMMIT COMPLETE.
SQL>
SQL> INSERT INTO TEST VALUES (27);
1 row created.
SQL>
SQL> SELECT *.
Fly
------------
15
16
25
26
27
SQL>
Open a window to create a checkpoint
Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 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 2
Current log sequence 2
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 *.
15
16
25 (New DataAfter Cold 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> HostMICROSoft 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, 413, 568 available bytes
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 22:55:33 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
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>
SQL> Shutdown Abort
Oracle Instance Shut Down.
SQL>
Restore database
Cut the nearest cold copy
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
D: / clonedb> Copy D: / clonedb /*.* (Oracle / ORA92 / TEST
D: /clonedb/cold_backup.log
D: /clonedb/control02.ctl
D: /clonedb/control03.ctl
D: /clonedb/drsys01.dbf
D: /clonedb/indx01.dbf
D: /clonedb/redo01.log
D: /clonedb/redo02.log
D: /clonedb/redo03.log
D: /clonedb/redo04a.log
D: /clonedb/redo04b.log
D: /clonedb/rman01.dbf
D: /clonedb/system01.dbf
D: /clonedb/temp01.dbf
D: /clonedb/tools01.dbf
D: /clonedb/undotbs01.dbf
D: /clonedb/Users01.dbf
D: /clonedb/xdb01.dbf
D: /clonedb/pwdtest.ora
D: /clonedb/test_ora_1912.trc
D: /clonedb/cold_backup.bat
D: /clonedb/shutdown.sql
D: /clonedb/startup.sql
22 files have been copied. D: / clonedb>
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 1090465 Generated AT 02/06/2002 00:12:04 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_1.arc
ORA-00280: Change 1090465 for Thread 1 is in sequence # 1
Specify log: {
ORA-00279: Change 1090629 Generated AT 02/06/2002 00:18:31 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_2.arc
ORA-00280: Change 1090629 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-00308: Cannot Open Archived log 'E: /oracle/oradata/test/archive/1_2.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>
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-06 00:16 2,864 Cold_Backup.log
2002-02-06 00:30 1,875,968 Control02.ctl
2002-02-06 00:30 1,875,968 Control03.ctl
2002-02-06 00:31 104, 865, 792 drsys01.dbf
2002-02-06 00:31 31, 465, 472 indx01.dbf
2002-02-06 00:11 31, 457, 792 redo01.log
2002-02-06 00:11 31, 457, 792 redo02.log
2002-02-06 00:11 31, 457, 792 redo03.log
2002-02-06 00:12 1,049,088 redo04a.log
2002-02-06 00:12 1,049,088 redo04b.log
2002-02-06 00:31 52, 436, 992 rman01.dbf
2002-02-06 00:31 367, 009, 792 system01.dbf
2002-02-05 22:27 20,979,712 TEMP01.DBF
2002-02-06 00:31 31, 465, 472 Tools01.dbf
2002-02-06 00:31 52, 436, 992 Undotbs01.dbf
2002-02-06 00:31 31, 465, 472 users01.dbf
2002-02-06 00:31 52, 436, 992 xdb01.dbf
2002-02-05 21:49 3,072 PWDTEST.ORA
2002-02-06 00:16 4,902 Test_ora_1912.trc2002-02-05 22:41 340 Cold_Backup.bat
2002-02-05 11:28 72 shutdown.sql
2002-02-05 12:19 281 Startup.sql
22 files 844,797,707 bytes
2 catalogs 1,957,179,392 available bytes
SQL>
Reparade it from the coldup copy back to Redo Log File
SQL> Host
Del
E: / Oracle / ORA92 / TEST / REDO *. *
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-06 00:16 2,864 Cold_Backup.log
2002-02-06 00:30 1,875,968 Control02.ctl
2002-02-06 00:30 1,875,968 Control03.ctl
2002-02-06 00:31 104, 865, 792 drsys01.dbf
2002-02-06 00:31 31, 465, 472 indx01.dbf
2002-02-06 00:31 52, 436, 992 rman01.dbf
2002-02-06 00:31 367, 009, 792 system01.dbf
2002-02-05 22:27 20,979,712 TEMP01.DBF
2002-02-06 00:31 31, 465, 472 Tools01.dbf
2002-02-06 00:31 52, 436, 992 Undotbs01.dbf
2002-02-06 00:31 31, 465, 472 users01.dbf
2002-02-06 00:31 52, 436, 992 xdb01.dbf
2002-02-05 21:49 3,072 PWDTEST.ORA
2002-02-06 00:16 4,902 Test_ora_1912.trc
2002-02-05 22:41 340 cold_backup.bat
2002-02-05 11:28 72 shutdown.sql
2002-02-05 12:19 281 Startup.sql
17 files 748, 326, 155 bytes
2 catalog 2,053,668,864 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 1090629 Generated AT 02/06/2002 00:18:31 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_2.arc
ORA-00280: Change 1090629 for thread 1 is in sequence # 2
Specify 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-06 00:16 2,864 Cold_Backup.log
2002-02-06 00:30 1,875,968 Control02.ctl
2002-02-06 00:30 1,875,968 Control03.ctl
2002-02-06 00:34 104, 865, 792 drsys01.dbf
2002-02-06 00:34 31, 465, 472 indx01.dbf
2002-02-06 00:34 31, 457, 792 redo01.log
2002-02-06 00:34 31, 457, 792 redo02.log
2002-02-06 00:34 31, 457, 792 redo03.log
2002-02-06 00:34 1,049,088 redo04a.log
2002-02-06 00:34 1,049,088 redo04b.log
2002-02-06 00:34 52, 436, 992 rman01.dbf
2002-02-06 00:34 367, 009, 792 system01.dbf
2002-02-05 22:27 20,979,712 TEMP01.DBF
2002-02-06 00:34 31, 465, 472 Tools01.dbf
2002-02-06 00:34 52, 436, 992 Undotbs01.dbf
2002-02-06 00:34 31, 465, 472 users01.dbf
2002-02-06 00:34 52, 436, 992 xdb01.dbf
2002-02-05 21:49 3,072 PWDTEST.ORA
2002-02-06 00:16 4,902 Test_ora_1912.trc
2002-02-05 22:41 340 Cold_Backup.bat2002-02-05 11:28 72 shutdown.sql
2002-02-05 12:19 281 Startup.sql
22 files 844,797,707 bytes
2 catalogs 1,957,175,296 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
