Oracle11g控制文件丢了怎么办?

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

前期准备

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

  1. [oracle@ora11g ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 16 18:14:10 2017
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to an idle instance.
  5. SYS@cams>startup;
  6. ORACLE instance started.
  7. Total System Global Area 776646656 bytes
  8. Fixed Size         2257272 bytes
  9. Variable Size         478154376 bytes
  10. Database Buffers     289406976 bytes
  11. Redo Buffers         6828032 bytes
  12. Database mounted.
  13. Database opened.
  14. SYS@cams>show parameter control_files;
  15. NAME                 TYPE     VALUE
  16. ------------------------------------ ----------- ------------------------------
  17. control_files             string     /u01/app/oracle/oradata/cams/c
  18.                          ontrol01.ctl, /u01/app/oracle/
  19.                          fast_recovery_area/cams/contro
  20.                          l02.ctl

然后查看两个控制文件的详细信息

  1. [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
  2. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 18:17 /u01/app/oracle/oradata/cams/control01.ctl
  3. [oracle@ora11g ~]$ ll /u01/app/oracle/fast_recovery_area/cams/control02.ctl
  4. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 18:18 /u01/app/oracle/fast_recovery_area/cams/control02.ctl

可以看到,两个数据库控制文件的详细信息一致,包括大小,用户组,读写权限等。

第一种情况:数据库处于启动状态,控制文件有多路复用,部分控制文件丢失

修改其中一个控制文件的名字,模拟控制文件丢失

  1. [oracle@ora11g ~]$ cd /u01/app/oracle/oradata/cams
  2. [oracle@ora11g cams]$ ls | grep control
  3. control01.ctl
  4. [oracle@ora11g cams]$ mv control01.ctl control01.ctl.bak
  5. [oracle@ora11g cams]$ ls | grep control
  6. control01.ctl.bak

这里发现一个有意思的现象,在控制文件破坏之前已经建立的连接在操作时不受影响,即使是从控制文件查询数据库信息:

  1. SYS@cams>select open_mode from v$database;
  2. OPEN_MODE
  3. --------------------
  4. READ WRITE

但是,用sqlplus重新建立的连接,操作就直接报错了:

  1. [oracle@ora11g ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 18 21:06:54 2017
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  7. SYS@cams>select open_mode from v$database;
  8. select open_mode from v$database
  9. *
  10. ERROR at line 1:
  11. ORA-00210: cannot open the specified control file
  12. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  13. ORA-27041: unable to open file
  14. Linux-x86_64 Error: 2: No such file or directory
  15. Additional information: 3

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

  1. SYS@cams>select count(*) from dba_tablespaces;
  2.   COUNT(*)
  3. ----------
  4.      8

但是查看alert日志,也是可以看到已经有报错信息输出了

  1. [oracle@ora11g ~]$ tail -f /u01/app/oracle/diag/rdbms/cams/cams/trace/alert_cams.log
  2. Starting background process SMCO
  3. Fri Aug 18 20:29:46 2017
  4. SMCO started with pid=25, OS id=2586
  5. Fri Aug 18 20:35:37 2017
  6. Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_2653.trc:
  7. ORA-00210: cannot open the specified control file
  8. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  9. ORA-27041: unable to open file
  10. Linux-x86_64 Error: 2: No such file or directory
  11. Additional information: 3
  12. Fri Aug 18 20:39:36 2017
  13. Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m000_2699.trc:
  14. ORA-00210: cannot open the specified control file
  15. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  16. ORA-27041: unable to open file
  17. Linux-x86_64 Error: 2: No such file or directory
  18. Additional information: 3
  19. Fri Aug 18 20:39:37 2017
  20. Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_2701.trc:
  21. ORA-00210: cannot open the specified control file
  22. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  23. ORA-27041: unable to open file
  24. Linux-x86_64 Error: 2: No such file or directory
  25. Additional information: 3

这种控制文件丢失的情况并不是很严重,按照下面操作步骤就能完美恢复控制文件:

1.关闭数据库实例

  1. SYS@cams>shutdown immediate;
  2. ORA-00210: cannot open the specified control file
  3. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  4. ORA-27041: unable to open file
  5. Linux-x86_64 Error: 2: No such file or directory
  6. Additional information: 3
  7. SYS@cams>shutdown abort;
  8. ORACLE instance shut down.

2.将正常的控制文件拷贝至丢失的控制文件所在位置

  1. [oracle@ora11g ~]$ cp /u01/app/oracle/fast_recovery_area/cams/control02.ctl /u01/app/oracle/oradata/cams/control01.ctl
  2. [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
  3. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:26 /u01/app/oracle/oradata/cams/control01.ctl

3.启动数据库实例

  1. SYS@cams>startup;
  2. ORACLE instance started.
  3. Total System Global Area 776646656 bytes
  4. Fixed Size         2257272 bytes
  5. Variable Size         478154376 bytes
  6. Database Buffers     289406976 bytes
  7. Redo Buffers         6828032 bytes
  8. Database mounted.
  9. Database opened.

4.执行语句检查数据库是否恢复正常

  1. SYS@cams>select open_mode from v$database;
  2. OPEN_MODE
  3. --------------------
  4. READ WRITE

至此,数据库恢复正常。如果数据库做了控制文件多路复用,然后出现其中部分控制文件丢失的情况,都可以用该方法进行恢复。简单的总结,就是在数据库关闭的情况下,用正常的控制文件去替换丢失的控制文件,然后启动即可。

第二种情况:数据库处于关闭状态,控制文件有多路复用,部分控制文件丢失

首先关闭数据库

  1. SYS@cams>shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.

然后将其中一个控制文件重命名,模拟控制文件丢失

  1. [oracle@ora11g cams]$ ls | grep control
  2. control01.ctl
  3. control01.ctl.bak
  4. [oracle@ora11g cams]$ mv control01.ctl control01.ctl.bak1
  5. [oracle@ora11g cams]$ ls | grep control
  6. control01.ctl.bak
  7. control01.ctl.bak1

启动数据库,发现报错

  1. SYS@cams>startup;
  2. ORACLE instance started.
  3. Total System Global Area 776646656 bytes
  4. Fixed Size         2257272 bytes
  5. Variable Size         478154376 bytes
  6. Database Buffers     289406976 bytes
  7. Redo Buffers         6828032 bytes
  8. ORA-00205: error in identifying control file, check alert log for more info

查看trace日志文件

  1. [oracle@ora11g ~]$ tail -n 20 /u01/app/oracle/diag/rdbms/cams/cams/trace/alert_cams.log
  2. SMON started with pid=13, OS id=3162
  3. Fri Aug 18 21:31:41 2017
  4. RECO started with pid=14, OS id=3164
  5. Fri Aug 18 21:31:41 2017
  6. MMON started with pid=15, OS id=3166
  7. starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’...
  8. Fri Aug 18 21:31:41 2017
  9. MMNL started with pid=16, OS id=3168
  10. starting up 1 shared server(s) ...
  11. ORACLE_BASE from environment = /u01/app/oracle
  12. Fri Aug 18 21:31:41 2017
  13. ALTER DATABASE MOUNT
  14. ORA-00210: cannot open the specified control file
  15. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  16. ORA-27037: unable to obtain file status
  17. Linux-x86_64 Error: 2: No such file or directory
  18. Additional information: 3
  19. ORA-205 signalled during: ALTER DATABASE MOUNT...
  20. Fri Aug 18 21:31:41 2017
  21. Checker run found 1 new persistent data failures

然后可以打开trace日志文件,找到数据库启动时候的参数信息:

  1. Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora
  2. System parameters with non-default values:
  3.   processes = 150
  4.   memory_target = 744M
  5.   control_files = “/u01/app/oracle/oradata/cams/control01.ctl”
  6.   control_files = “/u01/app/oracle/fast_recovery_area/cams/control02.ctl”
  7.   db_block_size = 8192
  8.   compatible = “11.2.0.4.0”
  9.   db_recovery_file_dest = “/u01/app/oracle/fast_recovery_area”
  10.   db_recovery_file_dest_size= 4182M
  11.   undo_tablespace = “UNDOTBS1″
  12.   remote_login_passwordfile= “EXCLUSIVE”
  13.   db_domain = “”
  14.   dispatchers = “(PROTOCOL=TCP) (SERVICE=camsXDB)”
  15.   job_queue_processes = 1000
  16.   audit_file_dest = “/u01/app/oracle/admin/cams/adump”
  17.   audit_trail = “DB”
  18.   db_name = “cams”
  19.   open_cursors = 300
  20.   diagnostic_dest = “/u01/app/oracle”

根据数据库启动时的参数信息,可以进行控制文件恢复。处理故障的方法就和第一种情况类似,先关闭数据库,然后用正常的控制文件去替换丢失的控制文件,然后启动数据库后进行验证即可。

第三种情况:数据库处于启动状态,全部控制文件丢失

将所有控制文件都重命名,模拟全部控制文件丢失

  1. [oracle@ora11g ~]$ mv /u01/app/oracle/oradata/cams/control01.ctl /u01/app/oracle/oradata/cams/control01.ctl.bak2
  2. [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams | grep control
  3. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
  4. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
  5. -rw-r-----. 1 oracle oinstall 9945088 Aug 19 10:44 control01.ctl.bak2
  6. [oracle@ora11g ~]$ mv /u01/app/oracle/fast_recovery_area/cams/control02.ctl /u01/app/oracle/fast_recovery_area/cams/control02.ctl.bak
  7. [oracle@ora11g ~]$ ll /u01/app/oracle/fast_recovery_area/cams
  8. total 9712
  9. -rw-r-----. 1 oracle oinstall 9945088 Aug 19 10:44 control02.ctl.bak

sqlplus打开一个新的连接,从控制文件查看数据库信息,做一些结构化变更,包括:

添加,删除或重命名数据文件

添加或删除表空间,或更改表空间的读/写状态

添加或删除重做日志文件或重做日志组

这里为了操作简单,修改表空间的读/写状态:

  1. [oracle@ora11g ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:46:35 2017
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  7. SYS@cams>select open_mode from v$database;
  8. select open_mode from v$database
  9.                       *
  10. ERROR at line 1:
  11. ORA-00210: cannot open the specified control file
  12. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  13. ORA-27041: unable to open file
  14. Linux-x86_64 Error: 2: No such file or directory
  15. Additional information: 3
  16. SYS@cams>select tablespace_name,status from dba_tablespaces;
  17. TABLESPACE_NAME      STATUS
  18. ------------------------------ ---------
  19. SYSTEM             ONLINE
  20. SYSAUX             ONLINE
  21. UNDOTBS1         ONLINE
  22. TEMP             ONLINE
  23. USERS             ONLINE
  24. EXAMPLE          ONLINE
  25. FINCHINAFCDD         ONLINE
  26. FINCHINAFCDD_BIGTABLE     ONLINE
  27. 8 rows selected.
  28. SYS@cams>alter tablespace example read only;
  29. alter tablespace example read only
  30. *
  31. ERROR at line 1:
  32. ORA-00603: ORACLE server session terminated by fatal error
  33. ORA-00210: cannot open the specified control file
  34. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  35. ORA-27041: unable to open file
  36. Linux-x86_64 Error: 2: No such file or directory
  37. Additional information: 3
  38. ORA-00210: cannot open the specified control file
  39. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  40. ORA-27041: unable to open file
  41. Linux-x86_64 Error: 2: No such file or directory
  42. Additional information: 3
  43. Process ID: 2705
  44. Session ID: 11 Serial number: 7
  45. SYS@cams>select tablespace_name,status from dba_tablespaces;
  46. ERROR:
  47. ORA-03114: not connected to ORACLE

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

  1. [oracle@ora11g ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:57:55 2017
  3. Copyright (c) 1982, 2013, Oracle. All rights reserved.
  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  7. SYS@cams>alter user sh identified by sh account unlock;
  8. User altered.
  9. SYS@cams>conn sh/sh
  10. Connected.
  11. SH@cams>create table test (id number,name varchar2(20));
  12. Table created.
  13. SH@cams>insert into test values(1,‘joe’);
  14. 1 row created.
  15. SH@cams>insert into test values(2,‘jeff’);
  16. 1 row created.
  17. SH@cams>update test set name=‘jack’ where id=2;
  18. 1 row updated.
  19. SH@cams>select * from test where id=2;
  20.     ID NAME
  21. ---------- --------------------
  22.      2 jack
  23. SH@cams>delete from test where id=2;
  24. 1 row deleted.
  25. SH@cams>select count(*) from test;
  26.   COUNT(*)
  27. ----------
  28.      1
  29. SH@cams>

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

  1. SH@cams>drop table test;
  2. Table dropped.
  3. SH@cams>select count(*) from test;
  4. select count(*) from test
  5.                      *
  6. ERROR at line 1:
  7. ORA-00942: table or view does not exist
  8. SH@cams>show recycle
  9. ORIGINAL NAME     RECYCLEBIN NAME        OBJECT TYPE DROP TIME
  10. ---------------- ------------------------------ ------------ -------------------
  11. TEST         BIN$VxOFH0JXCxjgU4IKqMCSFw==$0 TABLE     2017-08-19:11:05:37
  12. SH@cams>flashback table “BIN$VxOFH0JXCxjgU4IKqMCSFw==$0″ to before drop rename to test1;
  13. Flashback complete.
  14. SH@cams>select * from test1;
  15.     ID NAME
  16. ---------- --------------------
  17.      1 joe

Oracle数据库在控制文件全部丢失的情况下,还能提供那么多服务,已经很了不起了。现在,我们最重要的事情就是恢复控制文件,保证数据库所有功能都可以正常运行,操作步骤如下:

1.列出数据库的所有数据文件和重做日志文件。

首先尝试用数据库视图查看:

  1. SYS@cams>SELECT MEMBER FROM V$LOGFILE;
  2. SELECT MEMBER FROM V$LOGFILE
  3. *
  4. ERROR at line 1:
  5. ORA-00210: cannot open the specified control file
  6. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  7. ORA-27041: unable to open file
  8. Linux-x86_64 Error: 2: No such file or directory
  9. Additional information: 3
  10. SYS@cams>SELECT NAME FROM V$DATAFILE;
  11. SELECT NAME FROM V$DATAFILE
  12.                  *
  13. ERROR at line 1:
  14. ORA-00210: cannot open the specified control file
  15. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  16. ORA-27041: unable to open file
  17. Linux-x86_64 Error: 2: No such file or directory
  18. Additional information: 3
  19. SYS@cams>SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘control_files’;
  20. VALUE
  21. --------------------------------------------------------------------------------
  22. /u01/app/oracle/oradata/cams/control01.ctl, /u01/app/oracle/fast_recovery_area/c
  23. ams/control02.ctl

用不了V$LOGFILEV$DATAFILE视图,这里选择去服务器上查找数据库的所有数据文件和重做日志文件,如果没有调整过的话,数据文件和控制文件在路径$ORACLE_BASE/oradata/$ORACLE_SID下面:

  1. [oracle@ora11g cams]$ cd $ORACLE_BASE/oradata/$ORACLE_SID
  2. [oracle@ora11g cams]$ pwd
  3. /u01/app/oracle/oradata/cams
  4. [oracle@ora11g cams]$ ll
  5. total 73973336
  6. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
  7. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
  8. -rw-r-----. 1 oracle oinstall 9945088 Aug 19 11:36 control01.ctl.bak2
  9. -rw-r-----. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
  10. -rw-r-----. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
  11. -rw-r-----. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
  12. -rw-r-----. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
  13. -rw-r-----. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
  14. -rw-r-----. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
  15. -rw-r-----. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
  16. -rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
  17. -rw-r-----. 1 oracle oinstall 52429312 Aug 19 11:36 redo02.log
  18. -rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
  19. -rw-r-----. 1 oracle oinstall 765468672 Aug 19 11:36 sysaux01.dbf
  20. -rw-r-----. 1 oracle oinstall 870326272 Aug 19 11:36 system01.dbf
  21. -rw-r-----. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
  22. -rw-r-----. 1 oracle oinstall 429924352 Aug 19 11:35 undotbs01.dbf
  23. -rw-r-----. 1 oracle oinstall 5251072 Aug 19 11:16 users01.dbf
  24. [oracle@ora11g cams]$ du -sm *
  25. 10    control01.ctl.bak
  26. 10    control01.ctl.bak1
  27. 10    control01.ctl.bak2
  28. 331    example01.dbf
  29. 8625    finchina01.dbf
  30. 8025    finchina02.dbf
  31. 8225    finchina03.dbf
  32. 1025    finchina101.dbf
  33. 10241    finchina1.dbf
  34. 32768    finchina.dbf
  35. 51    redo01.log
  36. 51    redo02.log
  37. 51    redo03.log
  38. 731    sysaux01.dbf
  39. 831    system01.dbf
  40. 853    temp01.dbf
  41. 411    undotbs01.dbf
  42. 6    users01.dbf

对于表空间,为了防止落下,先查看有哪些表空间:

  1. SYS@cams>select tablespace_name,status from dba_tablespaces;
  2. TABLESPACE_NAME      STATUS
  3. ------------------------------ ---------
  4. SYSTEM             ONLINE
  5. SYSAUX             ONLINE
  6. UNDOTBS1         ONLINE
  7. TEMP             ONLINE
  8. USERS             ONLINE
  9. EXAMPLE          ONLINE
  10. FINCHINAFCDD         ONLINE
  11. FINCHINAFCDD_BIGTABLE     ONLINE
  12. 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.
关闭数据库。

  1. SYS@cams>shutdown immediate;
  2. ORA-00210: cannot open the specified control file
  3. ORA-00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  4. ORA-27041: unable to open file
  5. Linux-x86_64 Error: 2: No such file or directory
  6. Additional information: 3
  7. SYS@cams>shutdown abort;
  8. ORACLE instance shut down.

3.备份数据库的所有数据文件和重做日志文件。

  1. [oracle@ora11g cams]$ tar zcvf cams_backup.tar.gz *
  2. control01.ctl.bak
  3. control01.ctl.bak1
  4. control01.ctl.bak2
  5. example01.dbf
  6. finchina01.dbf
  7. finchina02.dbf
  8. finchina03.dbf
  9. finchina101.dbf
  10. finchina1.dbf
  11. finchina.dbf
  12. redo01.log
  13. redo02.log
  14. redo03.log
  15. sysaux01.dbf
  16. system01.dbf
  17. temp01.dbf
  18. undotbs01.dbf
  19. users01.dbf
  20. [oracle@ora11g cams]$ ll
  21. total 88069332
  22. -rw-r--r--. 1 oracle oinstall 14434295403 Aug 19 18:00 cams_backup.tar.gz
  23. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
  24. -rw-r-----. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
  25. -rw-r-----. 1 oracle oinstall 9945088 Aug 19 13:43 control01.ctl.bak2
  26. -rw-r-----. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
  27. -rw-r-----. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
  28. -rw-r-----. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
  29. -rw-r-----. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
  30. -rw-r-----. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
  31. -rw-r-----. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
  32. -rw-r-----. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
  33. -rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
  34. -rw-r-----. 1 oracle oinstall 52429312 Aug 19 13:43 redo02.log
  35. -rw-r-----. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
  36. -rw-r-----. 1 oracle oinstall 765468672 Aug 19 13:40 sysaux01.dbf
  37. -rw-r-----. 1 oracle oinstall 870326272 Aug 19 13:40 system01.dbf
  38. -rw-r-----. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
  39. -rw-r-----. 1 oracle oinstall 429924352 Aug 19 13:42 undotbs01.dbf
  40. -rw-r-----. 1 oracle oinstall 5251072 Aug 19 11:16 users01.dbf

4.启动一个新的实例,但不要挂载或打开数据库:

  1. SYS@cams>startup nomount;
  2. ORACLE instance started.
  3. Total System Global Area 776646656 bytes
  4. Fixed Size         2257272 bytes
  5. Variable Size         478154376 bytes
  6. Database Buffers     289406976 bytes
  7. Redo Buffers         6828032 bytes

5.使用CREATE CONTROLFILE语句为数据库创建一个新的控制文件。

  1. CREATE CONTROLFILE
  2.    REUSE DATABASE cams
  3.    LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/cams/redo01.log’),
  4.            GROUP 2 (‘/u01/app/oracle/oradata/cams/redo02.log’),
  5.            GROUP 3 (‘/u01/app/oracle/oradata/cams/redo03.log’)
  6.    NORESETLOGS
  7.    DATAFILE ‘/u01/app/oracle/oradata/cams/example01.dbf’,
  8.             ‘/u01/app/oracle/oradata/cams/finchina01.dbf’,
  9.             ‘/u01/app/oracle/oradata/cams/finchina02.dbf’,
  10.             ‘/u01/app/oracle/oradata/cams/finchina03.dbf’,
  11.             ‘/u01/app/oracle/oradata/cams/finchina101.dbf’,
  12.             ‘/u01/app/oracle/oradata/cams/finchina1.dbf’,
  13.             ‘/u01/app/oracle/oradata/cams/finchina.dbf’,
  14.             ‘/u01/app/oracle/oradata/cams/sysaux01.dbf’,
  15.             ‘/u01/app/oracle/oradata/cams/system01.dbf’,
  16.             ‘/u01/app/oracle/oradata/cams/temp01.dbf’,
  17.             ‘/u01/app/oracle/oradata/cams/undotbs01.dbf’,
  18.             ‘/u01/app/oracle/oradata/cams/users01.dbf’
  19.    MAXLOGFILES 50
  20.    MAXLOGMEMBERS 3
  21.    MAXLOGHISTORY 400
  22.    MAXDATAFILES 200
  23.    MAXINSTANCES 6
  24.    NOARCHIVELOG

提示错误:

  1. ERROR at line 1:
  2. ORA-01503: CREATE CONTROLFILE failed
  3. ORA-01160: file is not a data file
  4. ORA-01110: data file : ‘/u01/app/oracle/oradata/cams/temp01.dbf’

这里去掉CREATE CONTROLFILE语句里面的临时表空间

  1. CREATE CONTROLFILE
  2.    REUSE DATABASE cams
  3.    LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/cams/redo01.log’),
  4.            GROUP 2 (‘/u01/app/oracle/oradata/cams/redo02.log’),
  5.            GROUP 3 (‘/u01/app/oracle/oradata/cams/redo03.log’)
  6.    NORESETLOGS
  7.    DATAFILE ‘/u01/app/oracle/oradata/cams/example01.dbf’,
  8.             ‘/u01/app/oracle/oradata/cams/finchina01.dbf’,
  9.             ‘/u01/app/oracle/oradata/cams/finchina02.dbf’,
  10.             ‘/u01/app/oracle/oradata/cams/finchina03.dbf’,
  11.             ‘/u01/app/oracle/oradata/cams/finchina101.dbf’,
  12.             ‘/u01/app/oracle/oradata/cams/finchina1.dbf’,
  13.             ‘/u01/app/oracle/oradata/cams/finchina.dbf’,
  14.             ‘/u01/app/oracle/oradata/cams/sysaux01.dbf’,
  15.             ‘/u01/app/oracle/oradata/cams/system01.dbf’,
  16.             ‘/u01/app/oracle/oradata/cams/undotbs01.dbf’,
  17.             ‘/u01/app/oracle/oradata/cams/users01.dbf’
  18.    MAXLOGFILES 50
  19.    MAXLOGMEMBERS 3
  20.    MAXLOGHISTORY 400
  21.    MAXDATAFILES 200
  22.    MAXINSTANCES 6
  23.    NOARCHIVELOG

看到提示Control file created.

查看数据库的状态,可以看到数据库成功切换为mount状态

  1. SYS@cams>select open_mode from v$database;
  2. OPEN_MODE
  3. --------------------
  4. MOUNTED

5.正常打开数据库,必要时进行数据库恢复,然后再打开数据库。

  1. SYS@cams>alter database open;
  2. alter database open
  3. *
  4. ERROR at line 1:
  5. ORA-01113: file 1 needs media recovery
  6. ORA-01110: data file 1: ‘/u01/app/oracle/oradata/cams/system01.dbf’
  7. SYS@cams>recover database;
  8. Media recovery complete.
  9. SYS@cams>alter database open;
  10. Database altered.

6.进行简单的数据库检查,修复一些未处理的问题。

  1. SYS@cams>select name,open_mode from v$database;
  2. NAME     OPEN_MODE
  3. --------- --------------------
  4. CAMS     READ WRITE
  5. SYS@cams>select tablespace_name,status from dba_tablespaces;
  6. TABLESPACE_NAME      STATUS
  7. ------------------------------ ---------
  8. SYSTEM             ONLINE
  9. SYSAUX             ONLINE
  10. UNDOTBS1         ONLINE
  11. TEMP             ONLINE
  12. USERS             ONLINE
  13. EXAMPLE          ONLINE
  14. FINCHINAFCDD         ONLINE
  15. FINCHINAFCDD_BIGTABLE     ONLINE
  16. 8 rows selected.

检查trace日志文件

  1. Sat Aug 19 20:15:42 2017
  2. Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_4792.trc:
  3. ORA-25153: Temporary Tablespace is Empty
  4. Sat Aug 19 20:16:39 2017
  5. Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_j000_4820.trc:
  6. ORA-25153: Temporary Tablespace is Empty

发现ORA-25153错误,查看临时表空间视图:

  1. SYS@cams>select * from dba_temp_files;
  2. no rows selected
  3. SYS@cams>select * from v$tempfile;
  4. no rows selected

为数据库添加临时表空间,文件已经存在,使用reuse语句复用即可

  1. SYS@cams>alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/cams/temp01.dbf’ size 853m reuse autoextend on;
  2. Tablespace altered.
  3. SYS@cams>select * from dba_temp_files;
  4. FILE_NAME
  5. --------------------------------------------------------------------------------
  6.    FILE_ID TABLESPACE_NAME         BYTES     BLOCKS STATUS
  7. ---------- ------------------------------ ---------- ---------- -------
  8. RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
  9. ------------ --- ---------- ---------- ------------ ---------- -----------
  10. /u01/app/oracle/oradata/cams/temp01.dbf
  11.      1 TEMP              894435328     109184 ONLINE
  12.      1 YES 3.4360E+10 4194302         1 893386752     109056
  13. SYS@cams>select * from v$tempfile;
  14.      FILE# CREATION_CHANGE# CREATION_     TS# RFILE# STATUS ENABLED
  15. ---------- ---------------- --------- ---------- ---------- ------- ----------
  16.      BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
  17. ---------- ---------- ------------ ----------
  18. NAME
  19. --------------------------------------------------------------------------------
  20.      1     84418072 19-AUG-17     3     1 ONLINE READ WRITE
  21.  894435328 109184     894435328     8192
  22. /u01/app/oracle/oradata/cams/temp01.dbf

数据库现已打开并可用。

第四种情况:数据库处于关闭状态,全部控制文件丢失

这种情况下的处理方法和第三种情况基本一致,只是如果控制文件没有恢复好,数据库是不能对外提供服务的。但是第三种情况下,数据库还能提供和控制文件无关的增删改查等服务。

最后总结
控制文件的多路复用以及控制文件的备份是很重要的,使用
ALTER DATABASE BACKUP CONTROLFILE语句备份你的控制文件。你有两个选择:

使用下列语句将控制文件备份到二进制文件(现有控制文件的副本):

ALTER DATABASE BACKUP CONTROLFILE TO ‘/u01/app/oracle/oradata/cams/control.bkp';

生成可以用于重新创建控制文件的SQL语句:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

此命令将SQL脚本写入trace文件,可以对其进行抓取和编辑以重现控制文件。通过查看告警日志可以确定跟踪文件的名称和位置。

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » Oracle11g控制文件丢了怎么办?

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址