共计 20641 个字符,预计需要花费 52 分钟才能阅读完成。
本文测试 Oracle 11g 控制文件丢失后的恢复方法。文中没有提及使用实时(准实时)备份恢复,因为如果拥有实时(准实时)备份,处理方法的本质和前二种情况类似。
前期准备
首先连上数据库,查看控制文件所在路径
- [oracle@ora11g ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 16 18:14:10 2017
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- Connected to an idle instance.
- SYS@cams>startup;
- ORACLE instance started.
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 478154376 bytes
- Database Buffers 289406976 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
- SYS@cams>show parameter control_files;
- NAME TYPE VALUE
- –––––––––––––––––––––––––––––––––––– ––––––––––– ––––––––––––––––––––––––––––––
- control_files string /u01/app/oracle/oradata/cams/c
- ontrol01.ctl, /u01/app/oracle/
- fast_recovery_area/cams/contro
- l02.ctl
然后查看两个控制文件的详细信息
- [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 18:17 /u01/app/oracle/oradata/cams/control01.ctl
- [oracle@ora11g ~]$ ll /u01/app/oracle/fast_recovery_area/cams/control02.ctl
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 18:18 /u01/app/oracle/fast_recovery_area/cams/control02.ctl
可以看到,两个数据库控制文件的详细信息一致,包括大小,用户组,读写权限等。
第一种情况:数据库处于启动状态,控制文件有多路复用,部分控制文件丢失
修改其中一个控制文件的名字,模拟控制文件丢失
- [oracle@ora11g ~]$ cd /u01/app/oracle/oradata/cams
- [oracle@ora11g cams]$ ls | grep control
- control01.ctl
- [oracle@ora11g cams]$ mv control01.ctl control01.ctl.bak
- [oracle@ora11g cams]$ ls | grep control
- control01.ctl.bak
这里发现一个有意思的现象,在控制文件破坏之前已经建立的连接在操作时不受影响,即使是从控制文件查询数据库信息:
- SYS@cams>select open_mode from v$database;
- OPEN_MODE
- ––––––––––––––––––––
- READ WRITE
但是,用 sqlplus 重新建立的连接,操作就直接报错了:
- [oracle@ora11g ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 18 21:06:54 2017
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- SYS@cams>select open_mode from v$database;
- select open_mode from v$database
- *
- ERROR at line 1:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
当然,数据库还在提供服务,做一些和控制文件无关的操作都是可以支持的:
- SYS@cams>select count(*) from dba_tablespaces;
- COUNT(*)
- ––––––––––
- 8
但是查看 alert 日志,也是可以看到已经有报错信息输出了
- [oracle@ora11g ~]$ tail –f /u01/app/oracle/diag/rdbms/cams/cams/trace/alert_cams.log
- Starting background process SMCO
- Fri Aug 18 20:29:46 2017
- SMCO started with pid=25, OS id=2586
- Fri Aug 18 20:35:37 2017
- Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_2653.trc:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- Fri Aug 18 20:39:36 2017
- Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m000_2699.trc:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- Fri Aug 18 20:39:37 2017
- Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_2701.trc:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
这种控制文件丢失的情况并不是很严重,按照下面操作步骤就能完美恢复控制文件:
1.关闭数据库实例
- SYS@cams>shutdown immediate;
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- SYS@cams>shutdown abort;
- ORACLE instance shut down.
2.将正常的控制文件拷贝至丢失的控制文件所在位置
- [oracle@ora11g ~]$ cp /u01/app/oracle/fast_recovery_area/cams/control02.ctl /u01/app/oracle/oradata/cams/control01.ctl
- [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams/control01.ctl
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:26 /u01/app/oracle/oradata/cams/control01.ctl
3.启动数据库实例
- SYS@cams>startup;
- ORACLE instance started.
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 478154376 bytes
- Database Buffers 289406976 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
4.执行语句检查数据库是否恢复正常
- SYS@cams>select open_mode from v$database;
- OPEN_MODE
- ––––––––––––––––––––
- READ WRITE
至此,数据库恢复正常。如果数据库做了控制文件多路复用,然后出现其中部分控制文件丢失的情况,都可以用该方法进行恢复。简单的总结,就是在数据库关闭的情况下,用正常的控制文件去替换丢失的控制文件,然后启动即可。
第二种情况:数据库处于关闭状态,控制文件有多路复用,部分控制文件丢失
首先关闭数据库
- SYS@cams>shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
然后将其中一个控制文件重命名,模拟控制文件丢失
- [oracle@ora11g cams]$ ls | grep control
- control01.ctl
- control01.ctl.bak
- [oracle@ora11g cams]$ mv control01.ctl control01.ctl.bak1
- [oracle@ora11g cams]$ ls | grep control
- control01.ctl.bak
- control01.ctl.bak1
启动数据库,发现报错
- SYS@cams>startup;
- ORACLE instance started.
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 478154376 bytes
- Database Buffers 289406976 bytes
- Redo Buffers 6828032 bytes
- ORA–00205: error in identifying control file, check alert log for more info
查看 trace 日志文件
- [oracle@ora11g ~]$ tail –n 20 /u01/app/oracle/diag/rdbms/cams/cams/trace/alert_cams.log
- SMON started with pid=13, OS id=3162
- Fri Aug 18 21:31:41 2017
- RECO started with pid=14, OS id=3164
- Fri Aug 18 21:31:41 2017
- MMON started with pid=15, OS id=3166
- starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’...
- Fri Aug 18 21:31:41 2017
- MMNL started with pid=16, OS id=3168
- starting up 1 shared server(s) ...
- ORACLE_BASE from environment = /u01/app/oracle
- Fri Aug 18 21:31:41 2017
- ALTER DATABASE MOUNT
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27037: unable to obtain file status
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- ORA–205 signalled during: ALTER DATABASE MOUNT...
- Fri Aug 18 21:31:41 2017
- Checker run found 1 new persistent data failures
然后可以打开 trace 日志文件,找到数据库启动时候的参数信息:
- Using parameter settings in server–side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilecams.ora
- System parameters with non–default values:
- processes = 150
- memory_target = 744M
- control_files = “/u01/app/oracle/oradata/cams/control01.ctl”
- control_files = “/u01/app/oracle/fast_recovery_area/cams/control02.ctl”
- db_block_size = 8192
- compatible = “11.2.0.4.0”
- db_recovery_file_dest = “/u01/app/oracle/fast_recovery_area”
- db_recovery_file_dest_size= 4182M
- undo_tablespace = “UNDOTBS1”
- remote_login_passwordfile= “EXCLUSIVE”
- db_domain = “”
- dispatchers = “(PROTOCOL=TCP) (SERVICE=camsXDB)”
- job_queue_processes = 1000
- audit_file_dest = “/u01/app/oracle/admin/cams/adump”
- audit_trail = “DB”
- db_name = “cams”
- open_cursors = 300
- diagnostic_dest = “/u01/app/oracle”
根据数据库启动时的参数信息,可以进行控制文件恢复。处理故障的方法就和第一种情况类似,先关闭数据库,然后用正常的控制文件去替换丢失的控制文件,然后启动数据库后进行验证即可。
第三种情况:数据库处于启动状态,全部控制文件丢失
将所有控制文件都重命名,模拟全部控制文件丢失
- [oracle@ora11g ~]$ mv /u01/app/oracle/oradata/cams/control01.ctl /u01/app/oracle/oradata/cams/control01.ctl.bak2
- [oracle@ora11g ~]$ ll /u01/app/oracle/oradata/cams | grep control
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 19 10:44 control01.ctl.bak2
- [oracle@ora11g ~]$ mv /u01/app/oracle/fast_recovery_area/cams/control02.ctl /u01/app/oracle/fast_recovery_area/cams/control02.ctl.bak
- [oracle@ora11g ~]$ ll /u01/app/oracle/fast_recovery_area/cams
- total 9712
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 19 10:44 control02.ctl.bak
用 sqlplus 打开一个新的连接,从控制文件查看数据库信息,做一些结构化变更,包括:
l 添加,删除或重命名数据文件
l 添加或删除表空间,或更改表空间的读 / 写状态
l 添加或删除重做日志文件或重做日志组
这里为了操作简单,修改表空间的读 / 写状态:
- [oracle@ora11g ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:46:35 2017
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- SYS@cams>select open_mode from v$database;
- select open_mode from v$database
- *
- ERROR at line 1:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- SYS@cams>select tablespace_name,status from dba_tablespaces;
- TABLESPACE_NAME STATUS
- –––––––––––––––––––––––––––––– –––––––––
- SYSTEM ONLINE
- SYSAUX ONLINE
- UNDOTBS1 ONLINE
- TEMP ONLINE
- USERS ONLINE
- EXAMPLE ONLINE
- FINCHINAFCDD ONLINE
- FINCHINAFCDD_BIGTABLE ONLINE
- 8 rows selected.
- SYS@cams>alter tablespace example read only;
- alter tablespace example read only
- *
- ERROR at line 1:
- ORA–00603: ORACLE server session terminated by fatal error
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- Process ID: 2705
- Session ID: 11 Serial number: 7
- SYS@cams>select tablespace_name,status from dba_tablespaces;
- ERROR:
- ORA–03114: not connected to ORACLE
在进行结构化变更操作之后,数据库连接被自行断开了,不过如果再建立一个连接,还是可以进行数据库的增删改查操作的:
- [oracle@ora11g ~]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 19 10:57:55 2017
- Copyright (c) 1982, 2013, Oracle. All rights reserved.
- Connected to:
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- SYS@cams>alter user sh identified by sh account unlock;
- User altered.
- SYS@cams>conn sh/sh
- Connected.
- SH@cams>create table test (id number,name varchar2(20));
- Table created.
- SH@cams>insert into test values(1,‘joe’);
- 1 row created.
- SH@cams>insert into test values(2,‘jeff’);
- 1 row created.
- SH@cams>update test set name=‘jack’ where id=2;
- 1 row updated.
- SH@cams>select * from test where id=2;
- ID NAME
- –––––––––– ––––––––––––––––––––
- 2 jack
- SH@cams>delete from test where id=2;
- 1 row deleted.
- SH@cams>select count(*) from test;
- COUNT(*)
- ––––––––––
- 1
- SH@cams>
不只是增删改查操作,只要不要涉及到控制文件的读写,还可以进行其他操作,比如 drop table 之后从 recyclebin 恢复删除的表:
- SH@cams>drop table test;
- Table dropped.
- SH@cams>select count(*) from test;
- select count(*) from test
- *
- ERROR at line 1:
- ORA–00942: table or view does not exist
- SH@cams>show recycle
- ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
- –––––––––––––––– –––––––––––––––––––––––––––––– –––––––––––– –––––––––––––––––––
- TEST BIN$VxOFH0JXCxjgU4IKqMCSFw==$0 TABLE 2017–08–19:11:05:37
- SH@cams>flashback table “BIN$VxOFH0JXCxjgU4IKqMCSFw==$0” to before drop rename to test1;
- Flashback complete.
- SH@cams>select * from test1;
- ID NAME
- –––––––––– ––––––––––––––––––––
- 1 joe
Oracle数据库在控制文件全部丢失的情况下,还能提供那么多服务,已经很了不起了。现在,我们最重要的事情就是恢复控制文件,保证数据库所有功能都可以正常运行,操作步骤如下:
1.列出数据库的所有数据文件和重做日志文件。
首先尝试用数据库视图查看:
- SYS@cams>SELECT MEMBER FROM V$LOGFILE;
- SELECT MEMBER FROM V$LOGFILE
- *
- ERROR at line 1:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- SYS@cams>SELECT NAME FROM V$DATAFILE;
- SELECT NAME FROM V$DATAFILE
- *
- ERROR at line 1:
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- SYS@cams>SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘control_files’;
- VALUE
- ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
- /u01/app/oracle/oradata/cams/control01.ctl, /u01/app/oracle/fast_recovery_area/c
- ams/control02.ctl
用不了 V$LOGFILE 和V$DATAFILE视图,这里选择去服务器上查找数据库的所有数据文件和重做日志文件,如果没有调整过的话,数据文件和控制文件在路径 $ORACLE_BASE/oradata/$ORACLE_SID 下面:
- [oracle@ora11g cams]$ cd $ORACLE_BASE/oradata/$ORACLE_SID
- [oracle@ora11g cams]$ pwd
- /u01/app/oracle/oradata/cams
- [oracle@ora11g cams]$ ll
- total 73973336
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 19 11:36 control01.ctl.bak2
- –rw–r–––––. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
- –rw–r–––––. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
- –rw–r–––––. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
- –rw–r–––––. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
- –rw–r–––––. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
- –rw–r–––––. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
- –rw–r–––––. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
- –rw–r–––––. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
- –rw–r–––––. 1 oracle oinstall 52429312 Aug 19 11:36 redo02.log
- –rw–r–––––. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
- –rw–r–––––. 1 oracle oinstall 765468672 Aug 19 11:36 sysaux01.dbf
- –rw–r–––––. 1 oracle oinstall 870326272 Aug 19 11:36 system01.dbf
- –rw–r–––––. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
- –rw–r–––––. 1 oracle oinstall 429924352 Aug 19 11:35 undotbs01.dbf
- –rw–r–––––. 1 oracle oinstall 5251072 Aug 19 11:16 users01.dbf
- [oracle@ora11g cams]$ du –sm *
- 10 control01.ctl.bak
- 10 control01.ctl.bak1
- 10 control01.ctl.bak2
- 331 example01.dbf
- 8625 finchina01.dbf
- 8025 finchina02.dbf
- 8225 finchina03.dbf
- 1025 finchina101.dbf
- 10241 finchina1.dbf
- 32768 finchina.dbf
- 51 redo01.log
- 51 redo02.log
- 51 redo03.log
- 731 sysaux01.dbf
- 831 system01.dbf
- 853 temp01.dbf
- 411 undotbs01.dbf
- 6 users01.dbf
对于表空间,为了防止落下,先查看有哪些表空间:
- SYS@cams>select tablespace_name,status from dba_tablespaces;
- TABLESPACE_NAME STATUS
- –––––––––––––––––––––––––––––– –––––––––
- SYSTEM ONLINE
- SYSAUX ONLINE
- UNDOTBS1 ONLINE
- TEMP ONLINE
- USERS ONLINE
- EXAMPLE ONLINE
- FINCHINAFCDD ONLINE
- FINCHINAFCDD_BIGTABLE ONLINE
- 8 rows selected.
将获取到的数据文件和重做日志文件整理成列表:
编号 | 重做日志文件 | 大小(M) |
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.关闭数据库。
- SYS@cams>shutdown immediate;
- ORA–00210: cannot open the specified control file
- ORA–00202: control file: ‘/u01/app/oracle/oradata/cams/control01.ctl’
- ORA–27041: unable to open file
- Linux–x86_64 Error: 2: No such file or directory
- Additional information: 3
- SYS@cams>shutdown abort;
- ORACLE instance shut down.
3.备份数据库的所有数据文件和重做日志文件。
- [oracle@ora11g cams]$ tar zcvf cams_backup.tar.gz *
- control01.ctl.bak
- control01.ctl.bak1
- control01.ctl.bak2
- example01.dbf
- finchina01.dbf
- finchina02.dbf
- finchina03.dbf
- finchina101.dbf
- finchina1.dbf
- finchina.dbf
- redo01.log
- redo02.log
- redo03.log
- sysaux01.dbf
- system01.dbf
- temp01.dbf
- undotbs01.dbf
- users01.dbf
- [oracle@ora11g cams]$ ll
- total 88069332
- –rw–r––r––. 1 oracle oinstall 14434295403 Aug 19 18:00 cams_backup.tar.gz
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:25 control01.ctl.bak
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 18 21:29 control01.ctl.bak1
- –rw–r–––––. 1 oracle oinstall 9945088 Aug 19 13:43 control01.ctl.bak2
- –rw–r–––––. 1 oracle oinstall 346038272 Aug 19 10:41 example01.dbf
- –rw–r–––––. 1 oracle oinstall 9042927616 Aug 19 10:41 finchina01.dbf
- –rw–r–––––. 1 oracle oinstall 8413782016 Aug 19 10:41 finchina02.dbf
- –rw–r–––––. 1 oracle oinstall 8623497216 Aug 19 10:41 finchina03.dbf
- –rw–r–––––. 1 oracle oinstall 1073750016 Aug 19 10:41 finchina101.dbf
- –rw–r–––––. 1 oracle oinstall 10737426432 Aug 19 10:41 finchina1.dbf
- –rw–r–––––. 1 oracle oinstall 34359730176 Aug 19 10:41 finchina.dbf
- –rw–r–––––. 1 oracle oinstall 52429312 Aug 19 10:41 redo01.log
- –rw–r–––––. 1 oracle oinstall 52429312 Aug 19 13:43 redo02.log
- –rw–r–––––. 1 oracle oinstall 52429312 Aug 19 10:41 redo03.log
- –rw–r–––––. 1 oracle oinstall 765468672 Aug 19 13:40 sysaux01.dbf
- –rw–r–––––. 1 oracle oinstall 870326272 Aug 19 13:40 system01.dbf
- –rw–r–––––. 1 oracle oinstall 893394944 Aug 19 10:42 temp01.dbf
- –rw–r–––––. 1 oracle oinstall 429924352 Aug 19 13:42 undotbs01.dbf
- –rw–r–––––. 1 oracle oinstall 5251072 Aug 19 11:16 users01.dbf
4.启动一个新的实例,但不要挂载或打开数据库:
- SYS@cams>startup nomount;
- ORACLE instance started.
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 478154376 bytes
- Database Buffers 289406976 bytes
- Redo Buffers 6828032 bytes
5. 使用 CREATE CONTROLFILE 语句为数据库创建一个新的控制文件。
- CREATE CONTROLFILE
- REUSE DATABASE cams
- LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/cams/redo01.log’),
- GROUP 2 (‘/u01/app/oracle/oradata/cams/redo02.log’),
- GROUP 3 (‘/u01/app/oracle/oradata/cams/redo03.log’)
- NORESETLOGS
- DATAFILE ‘/u01/app/oracle/oradata/cams/example01.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina01.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina02.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina03.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina101.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina1.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina.dbf’,
- ‘/u01/app/oracle/oradata/cams/sysaux01.dbf’,
- ‘/u01/app/oracle/oradata/cams/system01.dbf’,
- ‘/u01/app/oracle/oradata/cams/temp01.dbf’,
- ‘/u01/app/oracle/oradata/cams/undotbs01.dbf’,
- ‘/u01/app/oracle/oradata/cams/users01.dbf’
- MAXLOGFILES 50
- MAXLOGMEMBERS 3
- MAXLOGHISTORY 400
- MAXDATAFILES 200
- MAXINSTANCES 6
- NOARCHIVELOG
提示错误:
- ERROR at line 1:
- ORA–01503: CREATE CONTROLFILE failed
- ORA–01160: file is not a data file
- ORA–01110: data file : ‘/u01/app/oracle/oradata/cams/temp01.dbf’
这里去掉 CREATE CONTROLFILE 语句里面的临时表空间
- CREATE CONTROLFILE
- REUSE DATABASE cams
- LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/cams/redo01.log’),
- GROUP 2 (‘/u01/app/oracle/oradata/cams/redo02.log’),
- GROUP 3 (‘/u01/app/oracle/oradata/cams/redo03.log’)
- NORESETLOGS
- DATAFILE ‘/u01/app/oracle/oradata/cams/example01.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina01.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina02.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina03.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina101.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina1.dbf’,
- ‘/u01/app/oracle/oradata/cams/finchina.dbf’,
- ‘/u01/app/oracle/oradata/cams/sysaux01.dbf’,
- ‘/u01/app/oracle/oradata/cams/system01.dbf’,
- ‘/u01/app/oracle/oradata/cams/undotbs01.dbf’,
- ‘/u01/app/oracle/oradata/cams/users01.dbf’
- MAXLOGFILES 50
- MAXLOGMEMBERS 3
- MAXLOGHISTORY 400
- MAXDATAFILES 200
- MAXINSTANCES 6
- NOARCHIVELOG
看到提示“Control file created.”
查看数据库的状态,可以看到数据库成功切换为 mount 状态
- SYS@cams>select open_mode from v$database;
- OPEN_MODE
- ––––––––––––––––––––
- MOUNTED
5.正常打开数据库,必要时进行数据库恢复,然后再打开数据库。
- SYS@cams>alter database open;
- alter database open
- *
- ERROR at line 1:
- ORA–01113: file 1 needs media recovery
- ORA–01110: data file 1: ‘/u01/app/oracle/oradata/cams/system01.dbf’
- SYS@cams>recover database;
- Media recovery complete.
- SYS@cams>alter database open;
- Database altered.
6.进行简单的数据库检查,修复一些未处理的问题。
- SYS@cams>select name,open_mode from v$database;
- NAME OPEN_MODE
- ––––––––– ––––––––––––––––––––
- CAMS READ WRITE
- SYS@cams>select tablespace_name,status from dba_tablespaces;
- TABLESPACE_NAME STATUS
- –––––––––––––––––––––––––––––– –––––––––
- SYSTEM ONLINE
- SYSAUX ONLINE
- UNDOTBS1 ONLINE
- TEMP ONLINE
- USERS ONLINE
- EXAMPLE ONLINE
- FINCHINAFCDD ONLINE
- FINCHINAFCDD_BIGTABLE ONLINE
- 8 rows selected.
检查 trace 日志文件
- Sat Aug 19 20:15:42 2017
- Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_m001_4792.trc:
- ORA–25153: Temporary Tablespace is Empty
- Sat Aug 19 20:16:39 2017
- Errors in file /u01/app/oracle/diag/rdbms/cams/cams/trace/cams_j000_4820.trc:
- ORA–25153: Temporary Tablespace is Empty
发现 ORA-25153 错误,查看临时表空间视图:
- SYS@cams>select * from dba_temp_files;
- no rows selected
- SYS@cams>select * from v$tempfile;
- no rows selected
为数据库添加临时表空间,文件已经存在 ,使用reuse 语句 复用即可:
- SYS@cams>alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/cams/temp01.dbf’ size 853m reuse autoextend on;
- Tablespace altered.
- SYS@cams>select * from dba_temp_files;
- FILE_NAME
- ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
- –––––––––– –––––––––––––––––––––––––––––– –––––––––– –––––––––– –––––––
- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
- –––––––––––– ––– –––––––––– –––––––––– –––––––––––– –––––––––– –––––––––––
- /u01/app/oracle/oradata/cams/temp01.dbf
- 1 TEMP 894435328 109184 ONLINE
- 1 YES 3.4360E+10 4194302 1 893386752 109056
- SYS@cams>select * from v$tempfile;
- FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
- –––––––––– –––––––––––––––– ––––––––– –––––––––– –––––––––– ––––––– ––––––––––
- BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
- –––––––––– –––––––––– –––––––––––– ––––––––––
- NAME
- ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
- 1 84418072 19–AUG–17 3 1 ONLINE READ WRITE
- 894435328 109184 894435328 8192
- /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 文件,可以对其进行抓取和编辑以重现控制文件。通过查看告警日志可以确定跟踪文件的名称和位置。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/147442.htm