转载

Oracle11g控制文件丢了怎么办?

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

前期准备

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

  1. [oracle@ora11g ~]$ sqlplus / as sysdba

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

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  7. Connected to an idle instance.

  9. SYS@cams>startup;
  10. ORACLE instance started.

  12. Total System Global Area 776646656 bytes
  13. Fixed Size         2257272 bytes
  14. Variable Size         478154376 bytes
  15. Database Buffers     289406976 bytes
  16. Redo Buffers         6828032 bytes
  17. Database mounted.
  18. Database opened.
  19. SYS@cams>show parameter control_files;

  21. NAME                 TYPE     VALUE
  22. ------------------------------------ ----------- ------------------------------
  23. control_files             string     /u01/app/oracle/oradata/cams/c
  24.                          ontrol01.ctl, /u01/app/oracle/
  25.                          fast_recovery_area/cams/contro
  26.                          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;

  3. OPEN_MODE
  4. --------------------
  5. READ WRITE


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

  1. [oracle@ora11g ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 18 21:06:54 2017

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  12. SYS@cams>select open_mode from v$database;
  13. select open_mode from v$database
  14. *
  15. ERROR at line 1:
  16. ORA-00210: cannot open the specified control file
  17. ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
  18. ORA-27041: unable to open file
  19. Linux-x86_64 Error: 2: No such file or directory
  20. Additional information: 3


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

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

  3.   COUNT(*)
  4. ----------
  5.      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.

  4. Total System Global Area 776646656 bytes
  5. Fixed Size         2257272 bytes
  6. Variable Size         478154376 bytes
  7. Database Buffers     289406976 bytes
  8. Redo Buffers         6828032 bytes
  9. Database mounted.
  10. Database opened.


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

  1. SYS@cams>select open_mode from v$database;

  3. OPEN_MODE
  4. --------------------
  5. 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.

  4. Total System Global Area 776646656 bytes
  5. Fixed Size         2257272 bytes
  6. Variable Size         478154376 bytes
  7. Database Buffers     289406976 bytes
  8. Redo Buffers         6828032 bytes
  9. 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打开一个新的连接，从控制文件查看数据库信息，做一些结构化变更，包括：

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

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

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

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

  1. [oracle@ora11g ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:46:35 2017

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  12. SYS@cams>select open_mode from v$database;
  13. select open_mode from v$database
  14.                       *
  15. ERROR at line 1:
  16. ORA-00210: cannot open the specified control file
  17. ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
  18. ORA-27041: unable to open file
  19. Linux-x86_64 Error: 2: No such file or directory
  20. Additional information: 3

  22. SYS@cams>select tablespace_name,status from dba_tablespaces;

  24. TABLESPACE_NAME      STATUS
  25. ------------------------------ ---------
  26. SYSTEM             ONLINE
  27. SYSAUX             ONLINE
  28. UNDOTBS1         ONLINE
  29. TEMP             ONLINE
  30. USERS             ONLINE
  31. EXAMPLE          ONLINE
  32. FINCHINAFCDD         ONLINE
  33. FINCHINAFCDD_BIGTABLE     ONLINE

  35. 8 rows selected.

  37. SYS@cams>alter tablespace example read only;
  38. alter tablespace example read only
  39. *
  40. ERROR at line 1:
  41. ORA-00603: ORACLE server session terminated by fatal error
  42. ORA-00210: cannot open the specified control file
  43. ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
  44. ORA-27041: unable to open file
  45. Linux-x86_64 Error: 2: No such file or directory
  46. Additional information: 3
  47. ORA-00210: cannot open the specified control file
  48. ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
  49. ORA-27041: unable to open file
  50. Linux-x86_64 Error: 2: No such file or directory
  51. Additional information: 3
  52. Process ID: 2705
  53. Session ID: 11 Serial number: 7

  55. SYS@cams>select tablespace_name,status from dba_tablespaces;
  56. ERROR:
  57. ORA-03114: not connected to ORACLE


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

  1. [oracle@ora11g ~]$ sqlplus / as sysdba

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

  5. Copyright (c) 1982, 2013, Oracle. All rights reserved.


  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

  14. User altered.

  16. SYS@cams>conn sh/sh
  17. Connected.
  18. SH@cams>create table test (id number,name varchar2(20));

  20. Table created.

  22. SH@cams>insert into test values(1,'joe');

  24. 1 row created.

  26. SH@cams>insert into test values(2,'jeff');

  28. 1 row created.

  30. SH@cams>update test set name='jack' where id=2;

  32. 1 row updated.

  34. SH@cams>select * from test where id=2;

  36.     ID NAME
  37. ---------- --------------------
  38.      2 jack

  40. SH@cams>delete from test where id=2;

  42. 1 row deleted.

  44. SH@cams>select count(*) from test;

  46.   COUNT(*)
  47. ----------
  48.      1

  50. SH@cams>


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

  1. SH@cams>drop table test;

  3. Table dropped.

  5. SH@cams>select count(*) from test;
  6. select count(*) from test
  7.                      *
  8. ERROR at line 1:
  9. ORA-00942: table or view does not exist


  12. SH@cams>show recycle
  13. ORIGINAL NAME     RECYCLEBIN NAME        OBJECT TYPE DROP TIME
  14. ---------------- ------------------------------ ------------ -------------------
  15. TEST         BIN$VxOFH0JXCxjgU4IKqMCSFw==$0 TABLE     2017-08-19:11:05:37
  16. SH@cams>flashback table "BIN$VxOFH0JXCxjgU4IKqMCSFw==$0" to before drop rename to test1;

  18. Flashback complete.

  20. SH@cams>select * from test1;

  22.     ID NAME
  23. ---------- --------------------
  24.      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

  11. SYS@cams>SELECT NAME FROM V$DATAFILE;
  12. SELECT NAME FROM V$DATAFILE
  13.                  *
  14. ERROR at line 1:
  15. ORA-00210: cannot open the specified control file
  16. ORA-00202: control file: '/u01/app/oracle/oradata/cams/control01.ctl'
  17. ORA-27041: unable to open file
  18. Linux-x86_64 Error: 2: No such file or directory
  19. Additional information: 3


  22. SYS@cams>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';

  24. VALUE
  25. --------------------------------------------------------------------------------
  26. /u01/app/oracle/oradata/cams/control01.ctl, /u01/app/oracle/fast_recovery_area/c
  27. ams/control02.ctl


用不了V$LOGFILE和V$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;

  3. TABLESPACE_NAME      STATUS
  4. ------------------------------ ---------
  5. SYSTEM             ONLINE
  6. SYSAUX             ONLINE
  7. UNDOTBS1         ONLINE
  8. TEMP             ONLINE
  9. USERS             ONLINE
  10. EXAMPLE          ONLINE
  11. FINCHINAFCDD         ONLINE
  12. FINCHINAFCDD_BIGTABLE     ONLINE

  14. 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.

  4. Total System Global Area 776646656 bytes
  5. Fixed Size         2257272 bytes
  6. Variable Size         478154376 bytes
  7. Database Buffers     289406976 bytes
  8. 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;

  3. OPEN_MODE
  4. --------------------
  5. 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'


  9. SYS@cams>recover database;
  10. Media recovery complete.
  11. SYS@cams>alter database open;

  13. Database altered.


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

  1. SYS@cams>select name,open_mode from v$database;

  3. NAME     OPEN_MODE
  4. --------- --------------------
  5. CAMS     READ WRITE

  7. SYS@cams>select tablespace_name,status from dba_tablespaces;

  9. TABLESPACE_NAME      STATUS
  10. ------------------------------ ---------
  11. SYSTEM             ONLINE
  12. SYSAUX             ONLINE
  13. UNDOTBS1         ONLINE
  14. TEMP             ONLINE
  15. USERS             ONLINE
  16. EXAMPLE          ONLINE
  17. FINCHINAFCDD         ONLINE
  18. FINCHINAFCDD_BIGTABLE     ONLINE

  20. 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;

  3. no rows selected

  5. SYS@cams>select * from v$tempfile;

  7. no rows selected

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

  1. SYS@cams>alter tablespace temp add tempfile '/u01/app/oracle/oradata/cams/temp01.dbf' size 853m reuse autoextend on;

  3. Tablespace altered.

  5. SYS@cams>select * from dba_temp_files;

  7. FILE_NAME
  8. --------------------------------------------------------------------------------
  9.    FILE_ID TABLESPACE_NAME         BYTES     BLOCKS STATUS
  10. ---------- ------------------------------ ---------- ---------- -------
  11. RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
  12. ------------ --- ---------- ---------- ------------ ---------- -----------
  13. /u01/app/oracle/oradata/cams/temp01.dbf
  14.      1 TEMP              894435328     109184 ONLINE
  15.      1 YES 3.4360E+10 4194302         1 893386752     109056


  18. SYS@cams>select * from v$tempfile;

  20.      FILE# CREATION_CHANGE# CREATION_     TS# RFILE# STATUS ENABLED
  21. ---------- ---------------- --------- ---------- ---------- ------- ----------
  22.      BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
  23. ---------- ---------- ------------ ----------
  24. NAME
  25. --------------------------------------------------------------------------------
  26.      1     84418072 19-AUG-17     3     1 ONLINE READ WRITE
  27.  894435328 109184     894435328     8192
  28. /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文件，可以对其进行抓取和编辑以重现控制文件。通过查看告警日志可以确定跟踪文件的名称和位置。

正文到此结束
所属分类： 编程技术

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 java-demo Maven教程 springcloud-demo hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 ANTLR教程 SpringCloud Ant教程 java实例教程 Hazelcast教程 XStream教程 Elastic-Job-Lite 深入浅出MyBatis ibaties教程 SVN教程 rabittmq教程 Hadoop教程 solr教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 使用 Docker 打包和运行 Vue 应用
  2. 2 Dependency Check命令行方式扫描jar包的安全漏洞
  3. 3 maven使用Dependency-Check来扫描安全漏洞
  4. 4 使用sonarqube分析代码质量
  5. 5 使用screw来对比数据库表和字段差异
  6. 6 Spring Boot 实现防盗链功能
  7. 7 在Spring Boot中的实现国际化（i18n）
  8. 8 一个用于 SMB/CIFS 网络的 Java 库-JCIFS
  9. 9 Spring Boot 3.x嵌入MongoDB 进行测试
  10. 10 java如何解析和生成yaml文件？
网站信息
  • 文章总数：82,737 篇
  • 文件总数：284,312 个
  • 标签总数：2,416 个
  • 分类总数：85 个
  • 留言数量：2,559 条
  • 在线人数：637 人
  • 运行天数：4,439天
  • 最后更新：2024年12月22日19点
Loading...

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

[HBLOG]公众号 HBLOG HBLOG