控制文件过久的模拟实验

首先我们需要备份一个当前的控制文件
之后create table t1(id int,name varchar2(10));
begin
for i in 1..10 loop       --插入几条数据
insert into t1(i,'book');
end loop;
end;
commit;
alter system switch logfile;
conn /as sysdba
shutdown immediate ;
这时候吧当前的controfile给rm掉
之后我们用rman恢复之前有备份旧的controlfile
rman target /
RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/back/untitled folder/control01.ctl'
2> ;


Starting restore at 29-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK


channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 29-JUN-15

controlfile恢复了之后我们将数据库至于mount阶段

RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


RMAN> alter database open;


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/29/2015 20:22:45
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file

这就模拟成功了所谓的控制文件过旧的情况

此时不要因为数据库打不开慌张，我们有办法恢复~

登陆到sqlplus 下 as sysdba
SQL> select file#,checkpoint_change# from v$datafile;


     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1      2161802
2      2161802
3      2161802
4      2161802
5      2161802
6      2161802
7      2161802


7 rows selected.


SQL> select file#,checkpoint_change# from v$datafile_header;


     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1      2163224
2      2163224
3      2163224
4      2163224
5      2163224
6      2163224
7      2163224


7 rows selected.

发现控制文件的scn 都小于数据文件的头 这也说明了控制文件过旧

之后我们需要重建控制文件将他恢复到最新

SQL> alter database backup controlfile to trace as '/u01/control.sql';


Database altered.

cat 查看control.sql

在nomount 下重建controlfile

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/test_tablespace.dbf',
  '/u01/app/oracle/oradata/orcl/rman.dbf',
  '/u01/app/oracle/oradata/orcl/test02.dbf'
CHARACTER SET WE8MSWIN1252
;

此时控制文件就是最新的了

alter database open;

数据库成功代开了

正如我们之前说的不要慌张，冷静下来想解决办法。


SQL> select file#,checkpoint_change# from V$datafile;


     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1      2163224
2      2163224
3      2163224
4      2163224
5      2163224
6      2163224
7      2163224


7 rows selected.


SQL> select file#,checkpoint_change# from V$datafile_header;


     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1      2163224
2      2163224
3      2163224
4      2163224
5      2163224
6      2163224
7      2163224


7 rows selected.

这下控制文件的scn 和数据文件头部的scn保持一致了所以数据库能够成功低打开了

这时候我们查询之前创建的一张表
SQL> select * from t1;


ID TYPE
---------- ----------
1 book
2 book
3 book
4 book
5 book
6 book
7 book
8 book
9 book
10 book


10 rows selected.

数据没有丢失

我们的数据库完好无损~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30163683/viewspace-1716686/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30163683/viewspace-1716686/