Reference: www.xifenfei.com2289.html www. askmaclean. comarchivesunix-linux % E4 % B8 % 8B % E5 % 88% A0 % E9 % 99% A4oracle % E6 % 8E % A7 % E5 % 88% B6 % E6 % 96% E4 % BB % B6controlfile % E4 % B8 % BA % E4 % BB % 80% E4 % B9 % 88% E5 % AE % 9E % E4 % BE % 8 Binstance % E6 % B2 % A1 % E6 % 9C % 89
Reference: http://www.xifenfei.com/2289.html http://www.askmaclean.com/archives/unix-linux%E4%B8%8B%E5%88%A0%E9%99%A4oracle%E6%8E%A7%E5%88%B6%E6%96%87%E4%BB%B6controlfile%E4%B8%BA%E4%BB%80%E4%B9%88%E5% AE %9E%E4%BE%8Binstance%E6%B2%A1%E6%9C%89
Reference: http://www.xifenfei.com/2289.html
Http://www.askmaclean.com/archives/unix-linux%E4%B8%8B%E5%88%A0%E9%99%A4oracle%E6%8E%A7%E5%88%B6%E6%96%87%E4%BB%B6controlfile%E4%B8%BA%E4%BB%80%E4%B9%88%E5% AE %9E%E4%BE%8Binstance%E6%B2%A1%E6%9C%89%E7% AB %8B%E5%8D%B3%E5%A5%94%E6%BA%83.html
After deleting all Controlfile control files on Unix/Linux, the instance does not crash immediately. On the contrary, FULL checkpoint can be successfully completed.
Why? --- Note that this problem only applies to Unix/Linux. files opened by other processes cannot be deleted on Windows.
The root cause is to Read or Write a File on Linux/Unix, and the process will Open an Open file descriptor associated with this File. Open file Descriptors (a File descriptor (FD) is an abstract indicator for accessing a file. the term is generally used in POSIX operating systems. in POSIX, a file descriptor is an integer, specifically of the C type int .)
In Linux/Unix, when a file is deleted, it is simply "unlink" to delete a hard link; inode that actually contains File data will not be deleted until all Open File Descriptors associated with the File are closed, the process that has obtained the file descriptor to open can continue reading and writing the file normally as long as the close function is not used to close these descriptors. When all open file characters of the file management are closed, the file is actually deleted.
Note: -- SYSTEM cannot be used for online RECOVER. You need to restart the database.
The UNDO file can be recovered online, but the UNDO file OFFLINE cannot be operated. The UNDO file cannot be DML, and the query can be done. The UNDO file does not involve data in the rollback segment.
Other data files can be recovered online.
The main Commands used are:
Ps-ef | grep dbw | grep-v grep
Ps-ef | grep ckpt | grep-v grep
Ll, proc, 10986, fd
Cp/proc/10986/fd/258/u01/oradata/bys3/system01.dbf
Alter database datafile 6 offline;
Recover datafile 6;
Alter database datafile 6 online;
1. Delete the recovery experiment for the SYSTEM File: session 1:
SYS @ bys3> select * from v $ dbfile;
FILE # NAME
--------------------------------------------------
1/u01/oradata/bys3/system01.dbf
2/u01/oradata/bys3/sysaux01.dbf
3/u01/oradata/bys3/undotbs01.dbf
4/u01/oradata/bys3/user01.dbf
5/u01/oradata/bys3/test1.dbf
6/u01/oradata/bys3/test2.dbf
7/u01/oradata/bys3/test22.dbf
8/u01/oradata/bys3/test3.dbf
SYS @ bys3> select open_mode from v $ database;
OPEN_MODE
--------------------
READ WRITE
#############
Open a new session 2:
View the process ID of DBWR and delete/u01/oradata/bys3/system01.dbf.
[Oracle @ bys3 bys3] $ ps-ef | grep dbw | grep-v grep identifies the process PID---10986,
Note that the DBWR process is checked here. If it is a control file, check the CKPT process.
Oracle 10986 1 0 Mar01? 00:04:10 ora_dbw0_bys3
[Oracle @ bys3 bys3] $ pwd
/U01/oradata/bys3
[Oracle @ bys3 bys3] $ ls
Control01.ctl redo02.log system01.dbf test22.dbf undotbs01.dbf
Control02.ctl redo03.log temp01.dbf test2.dbf user01.dbf
Redo01.log sysaux01.dbf test1.dbf test3.dbf
[Oracle @ bys3 bys3] $ rm-rf system01.dbf
#########################
Back to Session 1:
SYS @ bys3> conn bys/bys
ERROR:
ORA-00604: error occurred at recursive SQL level 2
The ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
The ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
@> Conn/as sysdba
Connected.
SYS @ bys3> select open_mode from v $ database;
OPEN_MODE
--------------------
READ WRITE
######################################## ##
Session 2:
[Oracle @ bys3 ~] $ Ll/proc/10986/fd
Total 0
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 0->/dev/null
L-wx ------ 1 oracle oinstall 64 Mar 7 11: 23 1->/dev/null
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 10->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkBYS3
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 11>/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msb
L-wx ------ 1 oracle oinstall 64 Mar 7 11: 23 2->/dev/null
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 256->/u01/oradata/bys3/control01.ctl
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 257->/u01/oradata/bys3/control02.ctl
Lrwx ------ 1 oracle oinstall 64 Mar 7 258->/u01/oradata/bys3/system01.dbf (deleted) --- this will always flash
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 259->/u01/oradata/bys3/sysaux01.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 260->/u01/oradata/bys3/undotbs01.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 261->/u01/oradata/bys3/user01.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 262->/u01/oradata/bys3/test1.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 263->/u01/oradata/bys3/test2.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 264->/u01/oradata/bys3/test22.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 265->/u01/oradata/bys3/test3.dbf
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 266->/u01/oradata/bys3/temp01.dbf
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 3->/dev/null
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 4->/dev/null
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 5->/dev/null
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 6->/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msb
Lr-x ------ 1 oracle oinstall 64 Mar 7 7->/proc/10986/fd
Lr-x ------ 1 oracle oinstall 64 Mar 7 11: 23 8->/dev/zero
Lrwx ------ 1 oracle oinstall 64 Mar 7 11: 23 9->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_bys3.dat
[Oracle @ bys3 ~] $ Cp/proc/10986/fd/258/u01/oradata/bys3/system01.dbf
[Oracle @ bys3 ~] $ Ll/u01/oradata/bys3/system01.dbf
-Rw-r ----- 1 oracle oinstall 524296192 Mar 7/u01/oradata/bys3/system01.dbf
At this time, ll/proc/10986/fd will still display the File status as :( deleted). If the file is not a SYSTEM file, it will become normal after online RECOVER.
####################
SYS @ bys3> conn bys/bys
Connected.
BYS @ bys3> select TABLESPACE_NAME, STATUS, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME status online _
----------------------------------------------
SYSTEM AVAILABLE SYSTEM
SYSAUX AVAILABLE ONLINE
UNDOTBS1 AVAILABLE ONLINE
USERS AVAILABLE ONLINE
TEST1 AVAILABLE ONLINE
TEST1 AVAILABLE ONLINE
TEST2 AVAILABLE ONLINE
TEST3 AVAILABLE ONLINE
Warning Information in the ALERT Log:
Fri Mar 07 11:17:39 2014
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_m000_14498.trc:
ORA-00604: error occurred at recursive SQL level 2
The ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures
Fri Mar 07 11:18:41 2014
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_m000_14516.trc:
The ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 07 11:18:42 2014
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_m001_1451_trc:
ORA-00604: error occurred at recursive SQL level 2
The ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
############
In this case, you should close the database, open MOUNT, restore the SYSTEM file, and then open the database, so that you can use it normally.
SYS @ bys3> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS @ bys3> startup
ORACLE instance started.
Total System Global Area 225996800 bytes
Fixed Size 1363692 bytes
Variable Size 150995220 bytes
Database Buffers 67108864 bytes
Redo Buffers 6529024 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
SYS @ bys3>
Recover datafile 1;
Media recovery complete.
SYS @ bys3> alter database open;
Database altered.
#############
2. If the loss is not SYSTEM/UNDO, it can be restored online: The method is as follows: BYS @ bys3> alter database datafile 6 offline;
Database altered.
BYS @ bys3> recover datafile 6;
Media recovery complete.
BYS @ bys3> alter database datafile 6 online;
Database altered.
################
3. UNDO's OFFLINE and recovery are the same as above: SYS @ bys3> alter database datafile 3 offline;
Alter database datafile 3 offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
Process ID: 15106
Session ID: 1 Serial number: 5
BYS @ bys3> update dept set deptno = 22;
Update dept set deptno = 22
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
#########
4. The methods for restoring the above data files that are deleted by mistake are basically the same:[Oracle @ bys3 ~] $ Rm-rf/u01/oradata/bys3/control *
[Oracle @ bys3 ~] $ Ps-ef | grep ckpt | grep-v grep ---- check
Is CKPT.
Oracle 15076 1 0? 00:00:01 ora_ckpt_bys3
[Oracle @ bys3 ~] $ Ll/proc/15076/fd
Total 0
Lr-x ------ 1 oracle oinstall 64 Mar 7 0->/dev/null
L-wx ------ 1 oracle oinstall 64 Mar 7 12:12 1->/dev/null
Lrwx ------ 1 oracle oinstall 64 Mar 7 10->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkBYS3
Lr-x ------ 1 oracle oinstall 64 Mar 7 12:12 11->/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msb
L-wx ------ 1 oracle oinstall 64 Mar 7 12:12 2->/dev/null
Lrwx ------ 1 oracle oinstall 64 Mar 7 256->/u01/oradata/bys3/control01.ctl (deleted)
Lrwx ------ 1 oracle oinstall 64 Mar 7 257->/u01/oradata/bys3/control02.ctl (deleted)
Lr-x ------ 1 oracle oinstall 64 Mar 7 12:12 3->/dev/null
Lr-x ------ 1 oracle oinstall 64 Mar 7 4->/dev/null
Lr-x ------ 1 oracle oinstall 64 Mar 7 5->/dev/null
Lr-x ------ 1 oracle oinstall 64 Mar 7 12:12 6->/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus. msb
Lr-x ------ 1 oracle oinstall 64 Mar 7 7->/proc/15076/fd
Lr-x ------ 1 oracle oinstall 64 Mar 7 8->/dev/zero
Lrwx ------ 1 oracle oinstall 64 Mar 7 9->/u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_bys3.dat
[Oracle @ bys3 ~] $ Cp/proc/15076/fd/256/u01/oradata/bys3/control01.ctl
[Oracle @ bys3 ~] $ Cp/proc/15076/fd/257/u01/oradata/bys3/control02.ctl
[Oracle @ bys3 ~] $ Ls/u01/oradata/bys3/control *
/U01/oradata/bys3/control01.ctl/u01/oradata/bys3/control02.ctl
Logs:
Fri Mar 07 12:12:04 2014
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_m000_15540.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/bys3/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Close the database: BYS @ bys3> conn/as sysdba
Connected.
SYS @ bys3> shutdown immediate;
Database closed.
ORA-03113: end-of-file on communication channel
Process ID: 15566
The Session ID is 51 Serial number: 223.
Logs when the database is shut down:
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
* ********************* ATTENTION: ********************
The controlfile header block returned by the OS
Has a sequence number that is too old.
The controlfile might be specified upted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE
Without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE
To the database, if the controlfile is truly specified upted.
In order to re-start the instance safely,
Please do the following:
(1) Save all copies of the controlfile for later
Analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
Alter database backup controlfile to trace;
(3) Unmount the instance.
(4) Use the script in the trace file
RE-create the controlfile and open the database.
Open the database: [oracle @ bys3 ~] $ Sqlplus/as sysdba
SQL * Plus: Release 11.2.0.4.0 Production on Fri Mar 7 12:16:10 2014
Copyright (c) 1982,201 3, Oracle. All rights reserved.
Connected to an idle instance.
SYS @ bys3> startup
ORACLE instance started.
Total System Global Area 225996800 bytes
Fixed Size 1363692 bytes
Variable Size 150995220 bytes
Database Buffers 67108864 bytes
Redo Buffers 6529024 bytes
Database mounted.
Database opened.
Logs when the database is opened
ALTER DATABASE MOUNT
Fri Mar 07 12:16:21 2014
Sweep [inc] [246535]: completed
..........................................
Sweep [inc2] [246423]: completed
Sweep [inc2] [246415]: completed
Sweep [inc2] [246407]: completed
Successful mount of redo thread 1, with mount id 3368434514
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
