Repair the database with the method of creating a data file (9201)
Author: David Zhang (Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
Foreword
When a data file is lost, you can quickly recover data using the method of rebuilding data files. The topic of this is that the topic of the current control file (or the latest backup to TRACE control file, while rebuilding the control file) And all archive logs, as well as the current online log file (ie, create all archive logs and online logs from the file to the current time, apply all the redo records from the file to create all the redo records to recover the data file).
Therefore, after changing the database structure, you should immediately back up the database, and if the condition does not allow the backup database, then the control file should be backed up.
This recovery method is especially tried to not back up this data file or the backup data file is not available when the file is damaged.
One current control file
Production test data
View current data
SQL> Archive Log List;
Database log mode archive mode
Automatic archive enable
Archive terminal E: / Oracle / ORADATA / TEST / ARCHIVE
The earliest summary log sequence 1
Next Archive Log Sequence 2
Current log sequence 2
SQL> Conn Lunar / Lunar
connected.
SQL> SELECT *.
Fly
------------
15
16
25
26
Create new tablespaces and data files
SQL> conn / as sysdba
connected.
SQL> CREATE TABLESPACE TEST Logging DataFile
2 'E: /oracle/ora92/test/test01.dbf' size 2m
3 AutoExtend ON
4 Next 64K
5 MaxSize Unlimited
6 extent management local;
The table space has been created.
SQL> ALTER USER LUNAR Quota Unlimited on Test;
The user has changed.
SQL>
Backup control file
SQL> ALTER DATABASE
Backup controlfile to 'd: /cold_backup/control_bk.ctl';
Database altered.
SQL> ALTER SYSTEM SET USER_DUMP_DEST = 'D: / Cold_Backup';
The system has changed.
SQL> ALTER DATABASE Backup ControlFile to TRACE;
The database has been changed.
SQL> ALTER system set user_dump_dest = 'E: / Oracle / Admin / Test / Udump';
The system has changed.
SQL> Host Dir D: / Cold_Backup
The volume in the driver D is Programs
The serial number of the volume is A06D-D424
D: / fold_backup directory
2002-02-02 21:07
2002-02-02 21:07
2003-02-06 14:34
2003-02-06 14:44 1,875,968 control_bk.ctl2003-02-06 14:44 4,578 test_ora_1844.trc
2 files 1,880,874 bytes
3 catalogs 2, 367, 717, 376 available bytes
SQL>
Insert test data in new data files
SQL> Conn Lunar / Lunar
connected.
SQL> CREATE TABLE LUNAR (AA Number) TABLESPACE TEST;
The table has been created.
SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 from User_Tables
3 where Table_name = 'lunar';
Table_name tablespace_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
Lunar Test
SQL>
SQL> INSERT INTO LUNAR VALUES (1);
It has created a row.
SQL> INSERT INTO LUNAR VALUES (2);
It has created a row.
SQL> commit;
Submitted.
SQL> SELECT * from Lunar;
AA
------------
1
2
SQL> INSERT INTO LUNAR VALUES (3);
It has created a row.
SQL>
Open an AS SYSDBA window to generate CheckPoin
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 Thu Feb 6 14:48:11 2003
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> Show User
User is "Lunar"
SQL> INSERT INTO LUNAR VALUES (4);
It has created a row.
SQL> INSERT INTO LUNAR VALUES (5);
It has created a row.
SQL>
Back to the window of AS Sysdba, Switch logfile
SQL> ALTER SYSTEM SWITCH LOGFILE;
SYSTEM altered.
SQL>
Go back to the original window, continue to add data, then commit
SQL> Show User
User is "Lunar"
SQL> INSERT INTO LUNAR VALUES (6);
It has created a row.
SQL> commit;
Submitted.
SQL>
Back to the window of AS Sysdba, Checkpoint
SQL> ALTER System Checkpoint;
SYSTEM altered.
SQL>
Go back to the original window and continue to add data
SQL> INSERT INTO LUNAR VALUES (7);
It has created a row.
SQL> SELECT * from Lunar;
AA
------------
1
2
(Commit)
3
(Checkpoint)
4
5
(Switch logfile)
6
(Commit, Checkpoint)
Seduce
(Crash)
7 lines have been selected.
It should be able to restore all commit data, namely 6
Back to the AS SYSDBA window, analog database Crash
SQL> Shutdown Abort
Oracle Instance Shut Down.
SQL>
Delete new data files
SQL> Host
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> DEL E: /Oracle/ora92/test/test01.dbf
C: /> EXIT
SQL>
Start database, error
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
Database mounted.
ORA-01157: Cannot Identify / Lock Data File 8 - See Dbwr TRACE FILE
ORA-01110: Data File 8: 'E: /oracle/ora92/test/test01.dbf'
SQL>
Restore database
Reconstruction of damaged data files
SQL> ALTER DATABASE CREATE DATAFILE 'E: /Oracle/ora92/test/test01.dbf';
Database altered.
SQL>
Restore damaged data files (front roll data)
SQL> Recover DataFile 'E: /oracle/ora92/test/test01.dbf';
Media Recovery Complete.
SQL>
Open the database
SQL> ALTABASE OPEN;
Database altered.
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 2
Next log sequence to archive 5current log sequence 5
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT * from Lunar;
AA
------------
1
2
3
4
5
6
6 rows selected.
SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 from User_Tables
3 where Table_name = 'lunar';
Table_name tablespace_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
Lunar Test
SQL>
Two use backup control files (To file)
Production test data
View current data
SQL> conn / as sysdba
Connected.
SQL> DROP TABLESPACE TEST INCLUDING CONTENTS;
TABLESPACE DROPPED.
SQL> Host
Del
E: /oracle/ora92/test/test01.dbf
SQL> Archive Log List;
Database log mode archive mode
Automatic archival enabled
Archive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 7
Next log sequence to archive 10
Current Log Sequence 10
SQL>
Create new tablespaces and data files
SQL> CREATE TABLESPACE TEST Logging DataFile
2 'E: /oracle/ora92/test/test01.dbf' size 2m
3 AutoExtend ON
4 Next 64K
5 MaxSize Unlimited
6 extent management local;
The table space has been created.
SQL> ALTER USER LUNAR Quota Unlimited on Test;
The user has changed.
SQL>
Backup control file
SQL> ALTER DATABASE Backup ControlFile
To 'd: /cold_backup/control_bk.ctl';
Database altered.
SQL> ALTER SYSTEM SET USER_DUMP_DEST = 'D: / Cold_Backup';
SYSTEM altered.
SQL> ALTER DATABASE Backup ControlFile to TRACE;
Database altered.
SQL> ALTER system set user_dump_dest = 'E: / Oracle / Admin / Test / Udump';
SYSTEM altered.
SQL> Host Dir D: / Cold_Backup
The volume in the driver D is Programs
The serial number of the volume is A06D-D424
D: / fold_backup directory
2002-02-02 21:07
2003-02-06 14:34
2003-02-06 16:25 1,875,968 control_bk.ctl
2003-02-06 16:25 4,982 Test_ora_1868.trc
2 files 1,880,950 bytes
3 catalogs 2,365,833,216 available bytes
SQL>
Insert test data in new data files
SQL> Conn Lunar / Lunar
connected.
SQL> CREATE TABLE LUNAR (AA Number) TABLESPACE TEST;
The table has been created.
SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 from User_Tables
3 where Table_name = 'lunar';
Table_name tablespace_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
Lunar Test
SQL>
SQL> INSERT INTO LUNAR VALUES (1);
It has created a row.
SQL> INSERT INTO LUNAR VALUES (2);
It has created a row.
SQL> commit;
Submitted.
SQL> SELECT * from Lunar;
AA
------------
1
2
SQL> INSERT INTO LUNAR VALUES (3);
It has created a row.
SQL>
Open an AS SYSDBA window to generate CheckPoin
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production On Thu Feb 6 14:48:11 2003
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;
The system has changed.
SQL>
Go back to the original window and continue to add data
SQL> Show User
User is "Lunar"
SQL> INSERT INTO LUNAR VALUES (4);
It has created a row.
SQL> INSERT INTO LUNAR VALUES (5);
It has created a row.
SQL>
Back to the window of AS Sysdba, Switch logfile
SQL> ALTER SYSTEM SWITCH LOGFILE;
SYSTEM altered.
SQL>
Go back to the original window, continue to add data, then commitsql> show user
User is "Lunar"
SQL> INSERT INTO LUNAR VALUES (6);
It has created a row.
SQL> commit;
Submitted.
SQL>
Back to the window of AS Sysdba, Checkpoint
SQL> ALTER System Checkpoint;
SYSTEM altered.
SQL>
Go back to the original window and continue to add data
SQL> INSERT INTO LUNAR VALUES (7);
It has created a row.
SQL> SELECT * from Lunar;
AA
------------
1
2
(Commit)
3
(Checkpoint)
4
5
(Switch logfile)
6
(Commit, Checkpoint)
Seduce
(Crash)
7 lines have been selected.
It should be able to restore all commit data, namely 6
Back to the AS SYSDBA window, analog database Crash
SQL> Shutdown Abort
Oracle Instance Shut Down.
SQL>
Delete new data files
C: /> DEL E: /Oracle/ora92/test/test01.dbf
C: /> DEL E: / Oracle / ORA92 / TEST / Control *. *
C: /> DIR E: / ORACLE / ORA92 / TEST / Control *. * E: /oracle/ora92/test/test01.dbf
The volume in the driver E is Oracle
The serial number of the volume is 707A-C50C
E: / Oracle / ORA92 / TEST directory
E: / Oracle / ORA92 / TEST directory
Can't find a document
C: />
Start database, error
C: /> set nls_lang = american_america.us7ascii
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Thu Feb 6 15:30:13 2003
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>
Restore database
Copy the backup control file back
C: /> COPY D: /COLD_BACKUP/CONTROL_BK.CTL E: /Oracle/ora92/test/control02.ctl
1 file has been copied.
C: /> COPY D: /COLD_BACKUP/Control_bk.ctl e: /oracle/ora92/test/control03.ctl has copied 1 file.
C: />
Load but do not open the database
SQL> Startup Mount;
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.
SQL>
Reconstruction of damaged data files (establish this file on OS)
SQL> ALTER DATABASE CREATE DATAFILE 'E: /Oracle/ora92/test/test01.dbf';
Database altered.
SQL>
Restore database (useing backup controlfile)
SQL> Recover Database Using Backup ControlFile;
ORA-00279: Change 1156110 Generated AT 02/06/2003 16:25:09 Neread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_10.arc
ORA-00280: Change 1156110 for thread 1 is in sequence # 10
Specify log: {
Auto (has applied the last archive log)
ORA-00279: Change 1156200 Generated AT 02/06/2003 16:28:05 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_11.arc
ORA-00280: Change 1156200 for thread 1 is in sequence # 11
ORA-00278: log file 'E: /oracle/oradata/test/archive/1_10.arc' no longer neseded
For this recovery
ORA-00308: Cannot Open Archived log 'E: /oracle/oradata/test/archive/1_11.arc'
ORA-27041: UNABLE TO OPEN FILE
OSD-04002: ??????
O / S-Error: (OS 2) ???????????
SQL>
Recovery again (Until Cancel Using Backup ControlFile)
SQL> Recover Database Until Cancel Using Backup ControlFile;
ORA-00279: Change 1156200 Generated AT 02/06/2003 16:28:05 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_11.arc
ORA-00280: Change 1156200 for Thread 1 Is in Sequence # 11Specify log: {
Cancel
ORA-01547: Warning: recover succeeded but open replandlogs 10 Get Error Below
ORA-01194: File 1 Needs More Recovery to Be Consistent
ORA-01110: Data File 1: 'E: /oracle/ora92/test/system01.dbf'
ORA-01112: Media Recovery Not Started
Description Requires the current log file to recover the database
Use the current log file to restore the database (front roll)
SQL> Recover Database Until Cancel Using Backup ControlFile;
ORA-00279: Change 1156200 Generated AT 02/06/2003 16:28:05 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_11.arc
ORA-00280: Change 1156200 for thread 1 is in sequence # 11
Specify log: {
E: /oracle/ora92/test/redo01.log
ORA-00310: Archived Log Contains Sequence 10; SEQUENCE 11 Required
ORA-00334: Archived log: 'E: /oracle/ora92/test/redo01.log'
ORA-01547: Warning: recover succeeded but open replandlogs 10 Get Error Below
ORA-01194: File 1 Needs More Recovery to Be Consistent
ORA-01110: Data File 1: 'E: /oracle/ora92/test/system01.dbf'
SQL> Recover Database Until Cancel Using Backup ControlFile;
ORA-00279: Change 1156200 Generated AT 02/06/2003 16:28:05 Needed for Thread 1
ORA-00289: Suggestion: E: /Oracle/oradata/test/archive/1_11.arc
ORA-00280: Change 1156200 for thread 1 is in sequence # 11
Specify log: {
E: /oracle/ora92/test/redo02.log
Log Applied.
Media Recovery Complete.
SQL>
RESETLOGS Opens Database
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
Verify recovery
SQL> Archive log List
Database log mode archive mode
Automatic Archival EnableDarchive Destination E: / Oracle / ORADATA / TEST / Archive
Oldest Online Log Sequence 1
Next log sequence to archive 1
Current Log Sequence 1
SQL>
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT * from Lunar;
AA
------------
1
2
3
4
5
6
6 rows selected.
SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 from User_Tables
3 where Table_name = 'lunar';
Table_name tablespace_name
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
Lunar Test
SQL>
