###重建控制文件之前的准备
如果一开始所有的datafile不在disk上,你将不能recreate controlfile。
如果datafiles在disk上,务必保证没有offlined的datafile
select distinct(status) from v$datafile where status not in ('ONLINE','SYSTEM');
select name, ts#, online$, contents$ from ts$ where online$ =2;
否则,一旦控制文件以resetlogs的方式重建之后,所有的offlined 的datafile不能添加回database。
.在mount状态下获得trc文件
sql> alter database backup controlfile to trace as '/home/oracle/crontol_trace.trc';
根据trc文件编译一个sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "UNIGUARD" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 '/product/app/oracle/oradata/uniguard/redo01.log' SIZE 50M,
GROUP 2 '/product/app/oracle/oradata/uniguard/redo02.log' SIZE 50M,
GROUP 3 '/product/app/oracle/oradata/uniguard/redo03.log' SIZE 50M
-- STANDBY LOGFILE</p><p>DATAFILE
'/product/app/oracle/oradata/uniguard/system01.dbf',
'/product/app/oracle/oradata/uniguard/undotbs01.dbf',
'/product/app/oracle/oradata/uniguard/sysaux01.dbf',
'/product/app/oracle/oradata/uniguard/users01.dbf',
'/product/app/oracle/oradata/uniguard/info01.dbf',
'/product/app/oracle/oradata/uniguard/index01.dbf',
'/product/app/oracle/oradata/uniguard/dev01.dbf',
'/product/app/oracle/oradata/uniguard/index02.dbf',
'/product/app/oracle/oradata/uniguard/dev02.dbf',
'/product/app/oracle/oradata/uniguard/dev03.dbf',
'/product/app/oracle/oradata/uniguard/dev04.dbf'
CHARACTER SET ZHS16GBK;
sql>shutdown immediate
sql>@/home/oracle/xxx.sql
sql>alter database open resetlogs;
如果一开始所有的datafile不在disk上,你将不能recreate controlfile。
如果datafiles在disk上,务必保证没有offlined的datafile
select distinct(status) from v$datafile where status not in ('ONLINE','SYSTEM');
select name, ts#, online$, contents$ from ts$ where online$ =2;
否则,一旦控制文件以resetlogs的方式重建之后,所有的offlined 的datafile不能添加回database。
.在mount状态下获得trc文件
sql> alter database backup controlfile to trace as '/home/oracle/crontol_trace.trc';
根据trc文件编译一个sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "UNIGUARD" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 '/product/app/oracle/oradata/uniguard/redo01.log' SIZE 50M,
GROUP 2 '/product/app/oracle/oradata/uniguard/redo02.log' SIZE 50M,
GROUP 3 '/product/app/oracle/oradata/uniguard/redo03.log' SIZE 50M
-- STANDBY LOGFILE</p><p>DATAFILE
'/product/app/oracle/oradata/uniguard/system01.dbf',
'/product/app/oracle/oradata/uniguard/undotbs01.dbf',
'/product/app/oracle/oradata/uniguard/sysaux01.dbf',
'/product/app/oracle/oradata/uniguard/users01.dbf',
'/product/app/oracle/oradata/uniguard/info01.dbf',
'/product/app/oracle/oradata/uniguard/index01.dbf',
'/product/app/oracle/oradata/uniguard/dev01.dbf',
'/product/app/oracle/oradata/uniguard/index02.dbf',
'/product/app/oracle/oradata/uniguard/dev02.dbf',
'/product/app/oracle/oradata/uniguard/dev03.dbf',
'/product/app/oracle/oradata/uniguard/dev04.dbf'
CHARACTER SET ZHS16GBK;
sql>shutdown immediate
sql>@/home/oracle/xxx.sql
sql>alter database open resetlogs;
以上给出生成创建控制文件脚本并重建控制文件的方法,但是具体恢复中遇到的问题可能需要具体对待,当得不到trace文件,可根据这个格式,查看数据文件、redo更改相关内容再重建.这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的,如果存在备份应该使用备份的控制文件尝试恢复.
数据文件查看命令:select name from v$datafile;
redo查看命令:select* from v$logfile;