Lost control file (in the case of archiving)
Lost a control file in an Oracle 9i environment
Restore points:
1) View the control file currently being used
2) Simulation Loss Control File 'E: / Oracle/ora92/test/control01.ctl'
3) Modify the character set
4) Start the database
5) Check the warning file (alert.log)
6) Recovery (modify parameter files or copy available control files to override damaged control files)
7) Re-open the database
Specific steps:
View the current control files
SQL> Show Parameter Control_Files
Name Type Value
----------------------------------- --- -------------------------------------------------- -
Control_files string e: /oracle/ora92/test/control01.ctl,
E: /oracle/ora92/test/control02.ctl,
E: /oracle/ora92/test/control03.ctl
SQL>
Simulation Loss Control File 'E: / ORACLE/ora92/test/control01.ctl'
(Note that the control file can be deleted online in the UNIX environment; however, the control file cannot be deleted online in the Window environment.)
SQL> Shutdown Immediate;
The database has been closed.
The database has been uninstalled.
Oracle routines have been closed.
SQL> EXIT
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 interrupt
Delete control file E: /oracle/ora92/test/control01.ctl
Modify the character set
C: /> set nls_lang = american_america.us7ascii
Start database
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production on Sat Feb 2 20:31: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> 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 BYTESORA-00205: Error In Identifying ControlFile, Check Alert log for more info
SQL>
It can be seen that the ORA-00205 error is caused because the current control file is not available.
Check the warning file (Alert.log) We can see corruption (or lost) control files is 'E: /oracle/ora92/test/control01.ctl':
Alter Database Mount
SAT feb 02 20:32:10 2002
ORA-00202: ControlFile: 'E: /oracle/ora92/test/control01.ctl'
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.
Close the database
SQL> Shutdown Abort;
Oracle Instance Shut Down.
SQL>
Recovery method 1 - Modify parameter file
Modify parameter file
Remove 'E: /oracle/ora92/test/control01.ctl' from the control_files parameter
Control_files = ("e: /oracle/ora92/test/control02.ctl", "e: /oracle/ora92/test/control03.ctl")
Open the database using the PFile parameter
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production On Sat Feb 2 20:52:15 2002
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to an iDLE Instance.
SQL> Startup Pfile = E: /oracle/admin/test/pfile/init.ora
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.
SQL>
Check about the currently available control file
SQL> Show Parameter Control_Files
Name Type Value
----------------------------------- --- ------------------------------------------
Control_files string e: /oracle/ora92/test/control02.ctl,
E: / Oracle / ORA92 / TEST / Con Trol03.ctl
SQL>
Rebuild SPFILE
SQL> CREATE SPFILE from Pfile = 'e: /oacle/admin/test/pfile/init.ora';
File created.
SQL>
Recovery Method 2 - Copy any of the available control files as damaged control files
Copy any of the available control files to 'e: /oracle/ora92/test/control01.ctl'
SQL> Host Copy E: /oracle/ora92/test/control02.ctl e: /oracle/ora92/test/control01.ctl
1 file has been copied.
SQL>
Reopen the database
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.
Database opened.
SQL>
Check about the currently available control file
SQL> Show Parameter Control_Files
Name Type Value
----------------------------------- --- -------------------------------------------------- -
Control_files string e: /oracle/ora92/test/control01.ctl,
E: /oracle/ora92/test/control02.ctl,
E: /oracle/ora92/test/control03.ctl
SQL>
Lost all control files (excluding data files and REDO)
Restore points
When you lose all control files (excluding data files and REDO), you need to recover your database with backup control files, keeping the data in REDO, you must perform the following steps:
1) Backup database (Backup ControlFile to TRACE)
2) Nomount
3) Reconstruction control files
4) Recover data;
5) ALTER DATABASE OPEN;
Back up the database and prepare for work
(The subject is that the database is all presumed before the following operations, here is the hot backup, the cold backup is sympathy. The following operating instructions, after the partial submission work, no archive, database As Crash, then if you can use the database backup to complete complete recovery, the user lunar should be able to see the two data submitted: 1 and 2. Because there is no archive, they are recovered before the instance is restored; However, INSERT INTO TEST VALUES (3) is not submitted, and should roll back when the instance is restored.)
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> SQLPLUS "Lunar / Lunar"
SQL * Plus: Release 9.2.0.1.0 - Production on Sunday February 3 09:53:24 2002
Copyright (C) 1982, 2002, Oracle Corporation. All Rights Reserved. Connection 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> CREATE TABLE TEST (FF Number);
The table has been created.
SQL> INSERT INTO TEST VALUES (1);
It has created a row.
SQL> INSERT INTO TEST VALUES (2);
It has created a row.
SQL> commit;
Submitted.
SQL> INSERT INTO TEST VALUES (3);
It has created a row.
SQL>
Immediately open a window, analog database Crash
Delete all control files, simulate all control files lost
Then start the data again, the following error prompt appears:
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 moreinfo
Check the alert.log file, you can see the following error message
ORA-00202: ControlFile: 'E: /oracle/ora92/test/control02.ctl'
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.
Sun feb 03 10:09:23 2002
ORA-205 Signalled Database: Alter Database Mount ...
Recover the database using the method of rebuilding control files
Edit the backup control file (To TRACE)
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
Reconstruction control file
SQL> Create ControlFile Reuse Database "Test1" NoreSetlogs 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 DataFile
12 'E: /oacle/ora92/test/system01.dbf',
13 'E: /oracle/ora92/test/undotbs01.dbf',
14 'E: /oacle/ora92/test/drsys01.dbf',
15 'E: /oracle/ora92/test/indx01.dbf',
16 'E: /oracle/ora92/test/tools01.dbf',
17 'E: /oracle/ora92/test/USERS01.DBF',
18 'E: /oacle/ora92/test/xdb01.dbf',
19 'E: /oracle/ora92/test/rman01.dbf'
20 Character Set ZHS16GBK
twenty one ;
Control File Created.
Full recovery database
SQL> Recover data;
Media Recovery Complete.
Open the database
SQL> ALTABASE OPEN;
Database altered.
Verify recovery
SQL> Conn Lunar / Lunar
Connected.
SQL> SELECT *.
FF
------------
1
2
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 21
Next log sequence to archive 23
Current Log Sequence 23
SQL>
