oracle控制文件没有更新,Oracle11g控制文件丢了怎么办?-CSDN博客

本文测试控制文件丢失后的恢复方法。文中没有提及使用实时(准实时)备份恢复，因为如果拥有实时(准实时)备份，处理方法的本质和前二种情况类似。

前期准备

首先连上数据库，查看控制文件所在路径

[oracle@ora11g ~]$sqlplus / as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Wed Aug 16 18:14:10 2017

Connected to an idle instance.

SYS@cams>startup;

ORACLE instance started.

Total System Global Area 776646656 bytes

FixedSize2257272 bytes

VariableSize478154376 bytes

Database Buffers 289406976 bytes

Redo Buffers 6828032 bytes

Database mounted.

Database opened.

SYS@cams>show parameter control_files;

NAMETYPEVALUE

------------------------------------ ----------- ------------------------------

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning,OLAP,Data MiningandReal 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 fileordirectory

Additional information:3

当然，数据库还在提供服务，做一些和控制文件无关的操作都是可以支持的：

SYS@cams>select count(*)from dba_tablespaces;

COUNT(*)

----------

但是查看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

Errorsinfile /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 fileordirectory

Additional information:3

Fri Aug 18 20:39:36 2017

Errorsinfile /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 fileordirectory

Additional information:3

Fri Aug 18 20:39:37 2017

Errorsinfile /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 fileordirectory

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 fileordirectory

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

FixedSize2257272 bytes

VariableSize478154376 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

FixedSize2257272 bytes

VariableSize478154376 bytes

Database Buffers 289406976 bytes

Redo Buffers 6828032 bytes

ORA-00205:errorinidentifying control file,check alert logformore 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) fornetwork 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 fileordirectory

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 settingsinserver-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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning,OLAP,Data MiningandReal 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 fileordirectory

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 fileordirectory

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 fileordirectory

Additional information:3

Process ID:2705

Session ID:11 Serial number:7

SYS@cams>select tablespace_name,status from dba_tablespaces;

ERROR:

ORA-03114: notconnected to ORACLE

在进行结构化变更操作之后，数据库连接被自行断开了，不过如果再建立一个连接，还是可以进行数据库的增删改查操作的：

[oracle@ora11g ~]$sqlplus / as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Sat Aug 19 10:57:55 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning,OLAP,Data MiningandReal Application Testing options

SYS@cams>alter user sh identified by sh account unlock;

User altered.

SYS@cams>conn sh/sh

Connected.

SH@cams>create tabletest (id number,namevarchar2(20));

Table created.

SH@cams>insertinto testvalues(1,'joe');

1 row created.

SH@cams>insertinto testvalues(2,'jeff');

1 row created.

SH@cams>updatetestsetname='jack'where id=2;

1 row updated.

SH@cams>select*fromtestwhere id=2;

IDNAME

---------- --------------------

2 jack

SH@cams>delete fromtestwhere id=2;

1 row deleted.

SH@cams>select count(*)fromtest;

COUNT(*)

----------

SH@cams>

不只是增删改查操作，只要不要涉及到控制文件的读写，还可以进行其他操作，比如drop table之后从recyclebin恢复删除的表：

SH@cams>drop tabletest;

Table dropped.

SH@cams>select count(*)fromtest;

select count(*)fromtest

ERROR at line 1:

ORA-00942:tableorview doesnotexist

SH@cams>show recycle

ORIGINALNAMERECYCLEBINNAMEOBJECTTYPEDROP TIME

---------------- ------------------------------ ------------ -------------------

TESTBIN$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;

IDNAME

---------- --------------------

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 fileordirectory

Additional information:3

SYS@cams>SELECTNAMEFROM V$DATAFILE;

SELECTNAMEFROM 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 fileordirectory

Additional information:3

SYS@cams>SELECT VALUE FROM V$PARAMETER WHERENAME = '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)

/u01/app/oracle/oradata/cams/redo01.log

/u01/app/oracle/oradata/cams/redo02.log

/u01/app/oracle/oradata/cams/redo03.log

编号

表空间文件

大小(M)

/u01/app/oracle/oradata/cams/example01.dbf

331

/u01/app/oracle/oradata/cams/finchina01.dbf

8625

/u01/app/oracle/oradata/cams/finchina02.dbf

8025

/u01/app/oracle/oradata/cams/finchina03.dbf

8225

/u01/app/oracle/oradata/cams/finchina101.dbf

1025

/u01/app/oracle/oradata/cams/finchina1.dbf

10241

/u01/app/oracle/oradata/cams/finchina.dbf

32768

/u01/app/oracle/oradata/cams/sysaux01.dbf

731

/u01/app/oracle/oradata/cams/system01.dbf

831

/u01/app/oracle/oradata/cams/temp01.dbf

853

/u01/app/oracle/oradata/cams/undotbs01.dbf

411

/u01/app/oracle/oradata/cams/users01.dbf

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 fileordirectory

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