阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle 11g控制文件丢失后的恢复方法

200次阅读
没有评论

共计 20641 个字符,预计需要花费 52 分钟才能阅读完成。

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

前期准备

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

  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. control_files            string    /u01/app/oracle/oradata/cams/c
  17.                         ontrol01.ctl, /u01/app/oracle/
  18.                         fast_recovery_area/cams/contro
  19.                         l02.ctl

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

  1. [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
  2. rwr. 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. rwr. 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. 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. ORA00210: cannot open the specified control file
  12. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  13. ORA27041: unable to open file
  14. Linuxx86_64 Error: 2: No such file or directory
  15. Additional information: 3

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

  1. SYS@cams>select count(*) from dba_tablespaces;
  2.   COUNT(*)
  3.     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. ORA00210: cannot open the specified control file
  8. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  9. ORA27041: unable to open file
  10. Linuxx86_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. ORA00210: cannot open the specified control file
  15. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  16. ORA27041: unable to open file
  17. Linuxx86_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. ORA00210: cannot open the specified control file
  22. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  23. ORA27041: unable to open file
  24. Linuxx86_64 Error: 2: No such file or directory
  25. Additional information: 3

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

1.关闭数据库实例

  1. SYS@cams>shutdown immediate;
  2. ORA00210: cannot open the specified control file
  3. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  4. ORA27041: unable to open file
  5. Linuxx86_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. rwr. 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. 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. ORA00205: 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. ORA00210: cannot open the specified control file
  15. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  16. ORA27037: unable to obtain file status
  17. Linuxx86_64 Error: 2: No such file or directory
  18. Additional information: 3
  19. ORA205 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 serverside spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora
  2. System parameters with nondefault 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. rwr. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
  4. rwr. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
  5. rwr. 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. rwr. 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. ORA00210: cannot open the specified control file
  12. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  13. ORA27041: unable to open file
  14. Linuxx86_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. SYSTEM            ONLINE
  19. SYSAUX            ONLINE
  20. UNDOTBS1        ONLINE
  21. TEMP            ONLINE
  22. USERS            ONLINE
  23. EXAMPLE         ONLINE
  24. FINCHINAFCDD        ONLINE
  25. FINCHINAFCDD_BIGTABLE    ONLINE
  26. 8 rows selected.
  27. SYS@cams>alter tablespace example read only;
  28. alter tablespace example read only
  29. *
  30. ERROR at line 1:
  31. ORA00603: ORACLE server session terminated by fatal error
  32. ORA00210: cannot open the specified control file
  33. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  34. ORA27041: unable to open file
  35. Linuxx86_64 Error: 2: No such file or directory
  36. Additional information: 3
  37. ORA00210: cannot open the specified control file
  38. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  39. ORA27041: unable to open file
  40. Linuxx86_64 Error: 2: No such file or directory
  41. Additional information: 3
  42. Process ID: 2705
  43. Session ID: 11 Serial number: 7
  44. SYS@cams>select tablespace_name,status from dba_tablespaces;
  45. ERROR:
  46. ORA03114: 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.     2 jack
  22. SH@cams>delete from test where id=2;
  23. 1 row deleted.
  24. SH@cams>select count(*) from test;
  25.   COUNT(*)
  26.     1
  27. 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. ORA00942: table or view does not exist
  8. SH@cams>show recycle
  9. ORIGINAL NAME    RECYCLEBIN NAME        OBJECT TYPE DROP TIME
  10. TEST        BIN$VxOFH0JXCxjgU4IKqMCSFw==$0 TABLE    20170819:11:05:37
  11. SH@cams>flashback table “BIN$VxOFH0JXCxjgU4IKqMCSFw==$0” to before drop rename to test1;
  12. Flashback complete.
  13. SH@cams>select * from test1;
  14.     ID NAME
  15.     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. ORA00210: cannot open the specified control file
  6. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  7. ORA27041: unable to open file
  8. Linuxx86_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. ORA00210: cannot open the specified control file
  15. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  16. ORA27041: unable to open file
  17. Linuxx86_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. /u01/app/oracle/oradata/cams/control01.ctl, /u01/app/oracle/fast_recovery_area/c
  22. 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. rwr. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
  7. rwr. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
  8. rwr. 1 oracle oinstall 9945088 Aug 19 11:36 control01.ctl.bak2
  9. rwr. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
  10. rwr. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
  11. rwr. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
  12. rwr. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
  13. rwr. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
  14. rwr. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
  15. rwr. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
  16. rwr. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
  17. rwr. 1 oracle oinstall 52429312 Aug 19 11:36 redo02.log
  18. rwr. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
  19. rwr. 1 oracle oinstall 765468672 Aug 19 11:36 sysaux01.dbf
  20. rwr. 1 oracle oinstall 870326272 Aug 19 11:36 system01.dbf
  21. rwr. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
  22. rwr. 1 oracle oinstall 429924352 Aug 19 11:35 undotbs01.dbf
  23. rwr. 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. SYSTEM            ONLINE
  4. SYSAUX            ONLINE
  5. UNDOTBS1        ONLINE
  6. TEMP            ONLINE
  7. USERS            ONLINE
  8. EXAMPLE         ONLINE
  9. FINCHINAFCDD        ONLINE
  10. FINCHINAFCDD_BIGTABLE    ONLINE
  11. 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. ORA00210: cannot open the specified control file
  3. ORA00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
  4. ORA27041: unable to open file
  5. Linuxx86_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. rwrr. 1 oracle oinstall 14434295403 Aug 19 18:00 cams_backup.tar.gz
  23. rwr. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
  24. rwr. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
  25. rwr. 1 oracle oinstall 9945088 Aug 19 13:43 control01.ctl.bak2
  26. rwr. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
  27. rwr. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
  28. rwr. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
  29. rwr. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
  30. rwr. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
  31. rwr. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
  32. rwr. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
  33. rwr. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
  34. rwr. 1 oracle oinstall 52429312 Aug 19 13:43 redo02.log
  35. rwr. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
  36. rwr. 1 oracle oinstall 765468672 Aug 19 13:40 sysaux01.dbf
  37. rwr. 1 oracle oinstall 870326272 Aug 19 13:40 system01.dbf
  38. rwr. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
  39. rwr. 1 oracle oinstall 429924352 Aug 19 13:42 undotbs01.dbf
  40. rwr. 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. ORA01503: CREATE CONTROLFILE failed
  3. ORA01160: file is not a data file
  4. ORA01110: 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. MOUNTED

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

  1. SYS@cams>alter database open;
  2. alter database open
  3. *
  4. ERROR at line 1:
  5. ORA01113: file 1 needs media recovery
  6. ORA01110: 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. CAMS    READ WRITE
  4. SYS@cams>select tablespace_name,status from dba_tablespaces;
  5. TABLESPACE_NAME     STATUS
  6. SYSTEM            ONLINE
  7. SYSAUX            ONLINE
  8. UNDOTBS1        ONLINE
  9. TEMP            ONLINE
  10. USERS            ONLINE
  11. EXAMPLE         ONLINE
  12. FINCHINAFCDD        ONLINE
  13. FINCHINAFCDD_BIGTABLE    ONLINE
  14. 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. ORA25153: 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. ORA25153: 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.    FILE_ID TABLESPACE_NAME        BYTES    BLOCKS STATUS
  6. RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
  7. /u01/app/oracle/oradata/cams/temp01.dbf
  8.     1 TEMP             894435328    109184 ONLINE
  9.     1 YES 3.4360E+10 4194302        1 893386752    109056
  10. SYS@cams>select * from v$tempfile;
  11.      FILE# CREATION_CHANGE# CREATION_    TS# RFILE# STATUS ENABLED
  12.      BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
  13. NAME
  14.     1    84418072 19AUG17    3    1 ONLINE READ WRITE
  15.  894435328 109184    894435328    8192
  16. /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 文件,可以对其进行抓取和编辑以重现控制文件。通过查看告警日志可以确定跟踪文件的名称和位置。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/147442.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计20641字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中