本文测试控制文件丢失后的恢复方法。文中没有提及使用实时(准实时)备份恢复,因为如果拥有实时(准实时)备份,处理方法的本质和前二种情况类似。
前期准备
首先连上数据库,查看控制文件所在路径
-
[oracle@ora11g ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 16 18:14:10 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
Connected to an idle instance.
-
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 478154376 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 6828032 bytes
-
Database mounted.
-
Database opened.
-
SYS@cams>show parameter control_files;
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
control_files string /u01/app/oracle/oradata/cams/c
-
ontrol01.ctl, /u01/app/oracle/
-
fast_recovery_area/cams/contro
-
l02.ctl
然后查看两个控制文件的详细信息
-
[oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 18:17 /u01/app/oracle/oradata/cams/control01.ctl
-
[oracle@ora11g ~]$ ll /u01/app/oracle/fast_recovery_area/cams/control02.ctl
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 18:18 /u01/app/oracle/fast_recovery_area/cams/control02.ctl
可以看到,两个数据库控制文件的详细信息一致,包括大小,用户组,读写权限等。
第一种情况:数据库处于启动状态,控制文件有多路复用,部分控制文件丢失
修改其中一个控制文件的名字,模拟控制文件丢失
-
[oracle@ora11g ~]$ cd /u01/app/oracle/oradata/cams
-
[oracle@ora11g cams]$ ls | grep control
-
control01.ctl
-
[oracle@ora11g cams]$ mv control01.ctl control01.ctl.bak
-
[oracle@ora11g cams]$ ls | grep control
-
control01.ctl.bak
这里发现一个有意思的现象,在控制文件破坏之前已经建立的连接在操作时不受影响,即使是从控制文件查询数据库信息:
-
SYS@cams>select open_mode from v$database;
-
-
OPEN_MODE
-
--------------------
-
READ WRITE
但是,用sqlplus重新建立的连接,操作就直接报错了:
-
[oracle@ora11g ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 18 21:06:54 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SYS@cams>select open_mode from v$database;
-
select open_mode from v$database
-
*
-
ERROR at line 1:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
当然,数据库还在提供服务,做一些和控制文件无关的操作都是可以支持的:
-
SYS@cams>select count(*) from dba_tablespaces;
-
-
COUNT(*)
-
----------
-
8
但是查看alert日志,也是可以看到已经有报错信息输出了
-
[oracle@ora11g ~]$ tail -f /u01/app/oracle/diag/rdbms/cams/cams/trace/alert_cams.log
-
Starting background process SMCO
-
Fri Aug 18 20:29:46 2017
-
SMCO started with pid=25, OS id=2586
-
Fri Aug 18 20:35:37 2017
-
Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_2653.trc:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
Fri Aug 18 20:39:36 2017
-
Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m000_2699.trc:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
Fri Aug 18 20:39:37 2017
-
Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_2701.trc:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
这种控制文件丢失的情况并不是很严重,按照下面操作步骤就能完美恢复控制文件:
1.关闭数据库实例
-
SYS@cams>shutdown immediate;
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
SYS@cams>shutdown abort;
-
ORACLE instance shut down.
2.将正常的控制文件拷贝至丢失的控制文件所在位置
-
[oracle@ora11g ~]$ cp /u01/app/oracle/fast_recovery_area/cams/control02.ctl /u01/app/oracle/oradata/cams/control01.ctl
-
[oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:26 /u01/app/oracle/oradata/cams/control01.ctl
3.启动数据库实例
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 478154376 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 6828032 bytes
-
Database mounted.
-
Database opened.
4.执行语句检查数据库是否恢复正常
-
SYS@cams>select open_mode from v$database;
-
-
OPEN_MODE
-
--------------------
-
READ WRITE
至此,数据库恢复正常。如果数据库做了控制文件多路复用,然后出现其中部分控制文件丢失的情况,都可以用该方法进行恢复。简单的总结,就是在数据库关闭的情况下,用正常的控制文件去替换丢失的控制文件,然后启动即可。
第二种情况:数据库处于关闭状态,控制文件有多路复用,部分控制文件丢失
首先关闭数据库
-
SYS@cams>shutdown immediate;
-
Database closed.
-
Database dismounted.
-
ORACLE instance shut down.
然后将其中一个控制文件重命名,模拟控制文件丢失
-
[oracle@ora11g cams]$ ls | grep control
-
control01.ctl
-
control01.ctl.bak
-
[oracle@ora11g cams]$ mv control01.ctl control01.ctl.bak1
-
[oracle@ora11g cams]$ ls | grep control
-
control01.ctl.bak
-
control01.ctl.bak1
启动数据库,发现报错
-
SYS@cams>startup;
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 478154376 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 6828032 bytes
-
ORA-00205: error in identifying control file, check alert log for more info
查看trace日志文件
-
[oracle@ora11g ~]$ tail -n 20 /u01/app/oracle/diag/rdbms/cams/cams/trace/alert_cams.log
-
SMON started with pid=13, OS id=3162
-
Fri Aug 18 21:31:41 2017
-
RECO started with pid=14, OS id=3164
-
Fri Aug 18 21:31:41 2017
-
MMON started with pid=15, OS id=3166
-
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
-
Fri Aug 18 21:31:41 2017
-
MMNL started with pid=16, OS id=3168
-
starting up 1 shared server(s) ...
-
ORACLE_BASE from environment = /u01/app/oracle
-
Fri Aug 18 21:31:41 2017
-
ALTER DATABASE MOUNT
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27037: unable to obtain file status
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
ORA-205 signalled during: ALTER DATABASE MOUNT...
-
Fri Aug 18 21:31:41 2017
-
Checker run found 1 new persistent data failures
然后可以打开trace日志文件,找到数据库启动时候的参数信息:
-
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora
-
System parameters with non-default values:
-
processes = 150
-
memory_target = 744M
-
control_files = "/u01/app/oracle/oradata/cams/control01.ctl"
-
control_files = "/u01/app/oracle/fast_recovery_area/cams/control02.ctl"
-
db_block_size = 8192
-
compatible = "11.2.0.4.0"
-
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
-
db_recovery_file_dest_size= 4182M
-
undo_tablespace = "UNDOTBS1"
-
remote_login_passwordfile= "EXCLUSIVE"
-
db_domain = ""
-
dispatchers = "(PROTOCOL=TCP) (SERVICE=camsXDB)"
-
job_queue_processes = 1000
-
audit_file_dest = "/u01/app/oracle/admin/cams/adump"
-
audit_trail = "DB"
-
db_name = "cams"
-
open_cursors = 300
-
diagnostic_dest = "/u01/app/oracle"
根据数据库启动时的参数信息,可以进行控制文件恢复。处理故障的方法就和第一种情况类似,先关闭数据库,然后用正常的控制文件去替换丢失的控制文件,然后启动数据库后进行验证即可。
第三种情况:数据库处于启动状态,全部控制文件丢失
将所有控制文件都重命名,模拟全部控制文件丢失
-
[oracle@ora11g ~]$ mv /u01/app/oracle/oradata/cams/control01.ctl /u01/app/oracle/oradata/cams/control01.ctl.bak2
-
[oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams | grep control
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 19 10:44 control01.ctl.bak2
-
[oracle@ora11g ~]$ mv /u01/app/oracle/fast_recovery_area/cams/control02.ctl /u01/app/oracle/fast_recovery_area/cams/control02.ctl.bak
-
[oracle@ora11g ~]$ ll /u01/app/oracle/fast_recovery_area/cams
-
total 9712
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 19 10:44 control02.ctl.bak
用sqlplus打开一个新的连接,从控制文件查看数据库信息,做一些结构化变更,包括:
l 添加,删除或重命名数据文件
l 添加或删除表空间,或更改表空间的读/写状态
l 添加或删除重做日志文件或重做日志组
这里为了操作简单,修改表空间的读/写状态:
-
[oracle@ora11g ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:46:35 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SYS@cams>select open_mode from v$database;
-
select open_mode from v$database
-
*
-
ERROR at line 1:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
-
SYS@cams>select tablespace_name,status from dba_tablespaces;
-
-
TABLESPACE_NAME STATUS
-
------------------------------ ---------
-
SYSTEM ONLINE
-
SYSAUX ONLINE
-
UNDOTBS1 ONLINE
-
TEMP ONLINE
-
USERS ONLINE
-
EXAMPLE ONLINE
-
FINCHINAFCDD ONLINE
-
FINCHINAFCDD_BIGTABLE ONLINE
-
-
8 rows selected.
-
-
SYS@cams>alter tablespace example read only;
-
alter tablespace example read only
-
*
-
ERROR at line 1:
-
ORA-00603: ORACLE server session terminated by fatal error
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
Process ID: 2705
-
Session ID: 11 Serial number: 7
-
-
SYS@cams>select tablespace_name,status from dba_tablespaces;
-
ERROR:
-
ORA-03114: not connected to ORACLE
在进行结构化变更操作之后,数据库连接被自行断开了,不过如果再建立一个连接,还是可以进行数据库的增删改查操作的:
-
[oracle@ora11g ~]$ sqlplus / as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:57:55 2017
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
Connected to:
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-
-
SYS@cams>alter user sh identified by sh account unlock;
-
-
User altered.
-
-
SYS@cams>conn sh/sh
-
Connected.
-
SH@cams>create table test (id number,name varchar2(20));
-
-
Table created.
-
-
SH@cams>insert into test values(1,'joe');
-
-
1 row created.
-
-
SH@cams>insert into test values(2,'jeff');
-
-
1 row created.
-
-
SH@cams>update test set name='jack' where id=2;
-
-
1 row updated.
-
-
SH@cams>select * from test where id=2;
-
-
ID NAME
-
---------- --------------------
-
2 jack
-
-
SH@cams>delete from test where id=2;
-
-
1 row deleted.
-
-
SH@cams>select count(*) from test;
-
-
COUNT(*)
-
----------
-
1
-
-
SH@cams>
不只是增删改查操作,只要不要涉及到控制文件的读写,还可以进行其他操作,比如drop table之后从recyclebin恢复删除的表:
-
SH@cams>drop table test;
-
-
Table dropped.
-
-
SH@cams>select count(*) from test;
-
select count(*) from test
-
*
-
ERROR at line 1:
-
ORA-00942: table or view does not exist
-
-
-
SH@cams>show recycle
-
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-
---------------- ------------------------------ ------------ -------------------
-
TEST BIN$VxOFH0JXCxjgU4IKqMCSFw==$0 TABLE 2017-08-19:11:05:37
-
SH@cams>flashback table "BIN$VxOFH0JXCxjgU4IKqMCSFw==$0" to before drop rename to test1;
-
-
Flashback complete.
-
-
SH@cams>select * from test1;
-
-
ID NAME
-
---------- --------------------
-
1 joe
Oracle数据库在控制文件全部丢失的情况下,还能提供那么多服务,已经很了不起了。现在,我们最重要的事情就是恢复控制文件,保证数据库所有功能都可以正常运行,操作步骤如下:
1.列出数据库的所有数据文件和重做日志文件。
首先尝试用数据库视图查看:
-
SYS@cams>SELECT MEMBER FROM V$LOGFILE;
-
SELECT MEMBER FROM V$LOGFILE
-
*
-
ERROR at line 1:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
-
SYS@cams>SELECT NAME FROM V$DATAFILE;
-
SELECT NAME FROM V$DATAFILE
-
*
-
ERROR at line 1:
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
-
-
SYS@cams>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
-
-
VALUE
-
--------------------------------------------------------------------------------
-
/u01/app/oracle/oradata/cams/control01.ctl, /u01/app/oracle/fast_recovery_area/c
-
ams/control02.ctl
用不了V$LOGFILE和V$DATAFILE视图,这里选择去服务器上查找数据库的所有数据文件和重做日志文件,如果没有调整过的话,数据文件和控制文件在路径$ORACLE_BASE/oradata/$ORACLE_SID下面:
-
[oracle@ora11g cams]$ cd $ORACLE_BASE/oradata/$ORACLE_SID
-
[oracle@ora11g cams]$ pwd
-
/u01/app/oracle/oradata/cams
-
[oracle@ora11g cams]$ ll
-
total 73973336
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 19 11:36 control01.ctl.bak2
-
-rw-r-----. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
-
-rw-r-----. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
-
-rw-r-----. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
-
-rw-r-----. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
-
-rw-r-----. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
-
-rw-r-----. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
-
-rw-r-----. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
-
-rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
-
-rw-r-----. 1 oracle oinstall 52429312 Aug 19 11:36 redo02.log
-
-rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
-
-rw-r-----. 1 oracle oinstall 765468672 Aug 19 11:36 sysaux01.dbf
-
-rw-r-----. 1 oracle oinstall 870326272 Aug 19 11:36 system01.dbf
-
-rw-r-----. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
-
-rw-r-----. 1 oracle oinstall 429924352 Aug 19 11:35 undotbs01.dbf
-
-rw-r-----. 1 oracle oinstall 5251072 Aug 19 11:16 users01.dbf
-
[oracle@ora11g cams]$ du -sm *
-
10 control01.ctl.bak
-
10 control01.ctl.bak1
-
10 control01.ctl.bak2
-
331 example01.dbf
-
8625 finchina01.dbf
-
8025 finchina02.dbf
-
8225 finchina03.dbf
-
1025 finchina101.dbf
-
10241 finchina1.dbf
-
32768 finchina.dbf
-
51 redo01.log
-
51 redo02.log
-
51 redo03.log
-
731 sysaux01.dbf
-
831 system01.dbf
-
853 temp01.dbf
-
411 undotbs01.dbf
-
6 users01.dbf
对于表空间,为了防止落下,先查看有哪些表空间:
-
SYS@cams>select tablespace_name,status from dba_tablespaces;
-
-
TABLESPACE_NAME STATUS
-
------------------------------ ---------
-
SYSTEM ONLINE
-
SYSAUX ONLINE
-
UNDOTBS1 ONLINE
-
TEMP ONLINE
-
USERS ONLINE
-
EXAMPLE ONLINE
-
FINCHINAFCDD ONLINE
-
FINCHINAFCDD_BIGTABLE ONLINE
-
-
8 rows selected.
将获取到的数据文件和重做日志文件整理成列表:
编号
|
重做日志文件
|
大小(M)
|
1
|
/u01/app/oracle/oradata/cams/redo01.log
|
51
|
2
|
/u01/app/oracle/oradata/cams/redo02.log
|
51
|
3
|
/u01/app/oracle/oradata/cams/redo03.log
|
51
|
编号
|
表空间文件
|
大小(M)
|
1
|
/u01/app/oracle/oradata/cams/example01.dbf
|
331
|
2
|
/u01/app/oracle/oradata/cams/finchina01.dbf
|
8625
|
3
|
/u01/app/oracle/oradata/cams/finchina02.dbf
|
8025
|
4
|
/u01/app/oracle/oradata/cams/finchina03.dbf
|
8225
|
5
|
/u01/app/oracle/oradata/cams/finchina101.dbf
|
1025
|
6
|
/u01/app/oracle/oradata/cams/finchina1.dbf
|
10241
|
7
|
/u01/app/oracle/oradata/cams/finchina.dbf
|
32768
|
8
|
/u01/app/oracle/oradata/cams/sysaux01.dbf
|
731
|
9
|
/u01/app/oracle/oradata/cams/system01.dbf
|
831
|
10
|
/u01/app/oracle/oradata/cams/temp01.dbf
|
853
|
11
|
/u01/app/oracle/oradata/cams/undotbs01.dbf
|
411
|
12
|
/u01/app/oracle/oradata/cams/users01.dbf
|
6
|
2.关闭数据库。
-
SYS@cams>shutdown immediate;
-
ORA-00210: cannot open the specified control file
-
ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
-
ORA-27041: unable to open file
-
Linux-x86_64 Error: 2: No such file or directory
-
Additional information: 3
-
SYS@cams>shutdown abort;
-
ORACLE instance shut down.
3.备份数据库的所有数据文件和重做日志文件。
-
[oracle@ora11g cams]$ tar zcvf cams_backup.tar.gz *
-
control01.ctl.bak
-
control01.ctl.bak1
-
control01.ctl.bak2
-
example01.dbf
-
finchina01.dbf
-
finchina02.dbf
-
finchina03.dbf
-
finchina101.dbf
-
finchina1.dbf
-
finchina.dbf
-
redo01.log
-
redo02.log
-
redo03.log
-
sysaux01.dbf
-
system01.dbf
-
temp01.dbf
-
undotbs01.dbf
-
users01.dbf
-
[oracle@ora11g cams]$ ll
-
total 88069332
-
-rw-r--r--. 1 oracle oinstall 14434295403 Aug 19 18:00 cams_backup.tar.gz
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
-
-rw-r-----. 1 oracle oinstall 9945088 Aug 19 13:43 control01.ctl.bak2
-
-rw-r-----. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
-
-rw-r-----. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
-
-rw-r-----. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
-
-rw-r-----. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
-
-rw-r-----. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
-
-rw-r-----. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
-
-rw-r-----. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
-
-rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
-
-rw-r-----. 1 oracle oinstall 52429312 Aug 19 13:43 redo02.log
-
-rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
-
-rw-r-----. 1 oracle oinstall 765468672 Aug 19 13:40 sysaux01.dbf
-
-rw-r-----. 1 oracle oinstall 870326272 Aug 19 13:40 system01.dbf
-
-rw-r-----. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
-
-rw-r-----. 1 oracle oinstall 429924352 Aug 19 13:42 undotbs01.dbf
-
-rw-r-----. 1 oracle oinstall 5251072 Aug 19 11:16 users01.dbf
4.启动一个新的实例,但不要挂载或打开数据库:
-
SYS@cams>startup nomount;
-
ORACLE instance started.
-
-
Total System Global Area 776646656 bytes
-
Fixed Size 2257272 bytes
-
Variable Size 478154376 bytes
-
Database Buffers 289406976 bytes
-
Redo Buffers 6828032 bytes
5.使用CREATE CONTROLFILE语句为数据库创建一个新的控制文件。
-
CREATE CONTROLFILE
-
REUSE DATABASE cams
-
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/cams/redo01.log'),
-
GROUP 2 ('/u01/app/oracle/oradata/cams/redo02.log'),
-
GROUP 3 ('/u01/app/oracle/oradata/cams/redo03.log')
-
NORESETLOGS
-
DATAFILE '/u01/app/oracle/oradata/cams/example01.dbf',
-
'/u01/app/oracle/oradata/cams/finchina01.dbf',
-
'/u01/app/oracle/oradata/cams/finchina02.dbf',
-
'/u01/app/oracle/oradata/cams/finchina03.dbf',
-
'/u01/app/oracle/oradata/cams/finchina101.dbf',
-
'/u01/app/oracle/oradata/cams/finchina1.dbf',
-
'/u01/app/oracle/oradata/cams/finchina.dbf',
-
'/u01/app/oracle/oradata/cams/sysaux01.dbf',
-
'/u01/app/oracle/oradata/cams/system01.dbf',
-
'/u01/app/oracle/oradata/cams/temp01.dbf',
-
'/u01/app/oracle/oradata/cams/undotbs01.dbf',
-
'/u01/app/oracle/oradata/cams/users01.dbf'
-
MAXLOGFILES 50
-
MAXLOGMEMBERS 3
-
MAXLOGHISTORY 400
-
MAXDATAFILES 200
-
MAXINSTANCES 6
-
NOARCHIVELOG
提示错误:
-
ERROR at line 1:
-
ORA-01503: CREATE CONTROLFILE failed
-
ORA-01160: file is not a data file
-
ORA-01110: data file : '/u01/app/oracle/oradata/cams/temp01.dbf'
这里去掉CREATE CONTROLFILE语句里面的临时表空间
-
CREATE CONTROLFILE
-
REUSE DATABASE cams
-
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/cams/redo01.log'),
-
GROUP 2 ('/u01/app/oracle/oradata/cams/redo02.log'),
-
GROUP 3 ('/u01/app/oracle/oradata/cams/redo03.log')
-
NORESETLOGS
-
DATAFILE '/u01/app/oracle/oradata/cams/example01.dbf',
-
'/u01/app/oracle/oradata/cams/finchina01.dbf',
-
'/u01/app/oracle/oradata/cams/finchina02.dbf',
-
'/u01/app/oracle/oradata/cams/finchina03.dbf',
-
'/u01/app/oracle/oradata/cams/finchina101.dbf',
-
'/u01/app/oracle/oradata/cams/finchina1.dbf',
-
'/u01/app/oracle/oradata/cams/finchina.dbf',
-
'/u01/app/oracle/oradata/cams/sysaux01.dbf',
-
'/u01/app/oracle/oradata/cams/system01.dbf',
-
'/u01/app/oracle/oradata/cams/undotbs01.dbf',
-
'/u01/app/oracle/oradata/cams/users01.dbf'
-
MAXLOGFILES 50
-
MAXLOGMEMBERS 3
-
MAXLOGHISTORY 400
-
MAXDATAFILES 200
-
MAXINSTANCES 6
-
NOARCHIVELOG
看到提示“Control file created.”
查看数据库的状态,可以看到数据库成功切换为mount状态
-
SYS@cams>select open_mode from v$database;
-
-
OPEN_MODE
-
--------------------
-
MOUNTED
5.正常打开数据库,必要时进行数据库恢复,然后再打开数据库。
-
SYS@cams>alter database open;
-
alter database open
-
*
-
ERROR at line 1:
-
ORA-01113: file 1 needs media recovery
-
ORA-01110: data file 1: '/u01/app/oracle/oradata/cams/system01.dbf'
-
-
-
SYS@cams>recover database;
-
Media recovery complete.
-
SYS@cams>alter database open;
-
-
Database altered.
6.进行简单的数据库检查,修复一些未处理的问题。
-
SYS@cams>select name,open_mode from v$database;
-
-
NAME OPEN_MODE
-
--------- --------------------
-
CAMS READ WRITE
-
-
SYS@cams>select tablespace_name,status from dba_tablespaces;
-
-
TABLESPACE_NAME STATUS
-
------------------------------ ---------
-
SYSTEM ONLINE
-
SYSAUX ONLINE
-
UNDOTBS1 ONLINE
-
TEMP ONLINE
-
USERS ONLINE
-
EXAMPLE ONLINE
-
FINCHINAFCDD ONLINE
-
FINCHINAFCDD_BIGTABLE ONLINE
-
-
8 rows selected.
检查trace日志文件
-
Sat Aug 19 20:15:42 2017
-
Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_4792.trc:
-
ORA-25153: Temporary Tablespace is Empty
-
Sat Aug 19 20:16:39 2017
-
Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_j000_4820.trc:
-
ORA-25153: Temporary Tablespace is Empty
发现ORA-25153错误,查看临时表空间视图:
-
SYS@cams>select * from dba_temp_files;
-
-
no rows selected
-
-
SYS@cams>select * from v$tempfile;
-
-
no rows selected
为数据库添加临时表空间,文件已经存在,使用reuse语句复用即可:
-
SYS@cams>alter tablespace temp add tempfile '/u01/app/oracle/oradata/cams/temp01.dbf' size 853m reuse autoextend on;
-
-
Tablespace altered.
-
-
SYS@cams>select * from dba_temp_files;
-
-
FILE_NAME
-
--------------------------------------------------------------------------------
-
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
-
---------- ------------------------------ ---------- ---------- -------
-
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-
------------ --- ---------- ---------- ------------ ---------- -----------
-
/u01/app/oracle/oradata/cams/temp01.dbf
-
1 TEMP 894435328 109184 ONLINE
-
1 YES 3.4360E+10 4194302 1 893386752 109056
-
-
-
SYS@cams>select * from v$tempfile;
-
-
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
-
---------- ---------------- --------- ---------- ---------- ------- ----------
-
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
-
---------- ---------- ------------ ----------
-
NAME
-
--------------------------------------------------------------------------------
-
1 84418072 19-AUG-17 3 1 ONLINE READ WRITE
-
894435328 109184 894435328 8192
-
/u01/app/oracle/oradata/cams/temp01.dbf
数据库现已打开并可用。
第四种情况:数据库处于关闭状态,全部控制文件丢失
这种情况下的处理方法和第三种情况基本一致,只是如果控制文件没有恢复好,数据库是不能对外提供服务的。但是第三种情况下,数据库还能提供和控制文件无关的增删改查等服务。
最后总结
控制文件的多路复用以及控制文件的备份是很重要的,使用ALTER DATABASE BACKUP CONTROLFILE语句备份你的控制文件。你有两个选择:
l 使用下列语句将控制文件备份到二进制文件(现有控制文件的副本):
ALTER DATABASE BACKUP CONTROLFILE TO '/u01/app/oracle/oradata/cams/control.bkp';
l 生成可以用于重新创建控制文件的SQL语句:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
此命令将SQL脚本写入trace文件,可以对其进行抓取和编辑以重现控制文件。通过查看告警日志可以确定跟踪文件的名称和位置。