Oracle11g控制檔案丟了怎麼辦?

dbasdk發表於2017-08-28
Oracle

本文測試控制檔案丟失後的恢復方法。文中沒有提及使用實時（準實時）備份恢復，因為如果擁有實時（準實時）備份，處理方法的本質和前二種情況類似。

前期準備

首先連上資料庫，檢視控制檔案所在路徑

  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檔案，可以對其進行抓取和編輯以重現控制檔案。透過檢視告警日誌可以確定跟蹤檔案的名稱和位置。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-2144209/，如需轉載，請註明出處，否則將追究法律責任。

相關文章