一、获取当前控制文件的SQL语句:
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter database backup controlfile to trace;
Database altered.
--当前controlfile的创建SQL 已经被写到.trc里了 点开查看即可(有条专门的SQL语句可以查看当前的trc文件)
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG --如果想使用当前的日志文件,一定要记得把resetlogs 改成" noResetlogs"
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 2 '/oracle/app/oracle/oradata/orcl/redo02_2.log' SIZE 50M,
GROUP 3 '/oracle/app/oracle/oradata/orcl/redo03.log' SIZE 50M,
GROUP 4 '/oracle/app/oracle/oradata/orcl/redo04.rdo' SIZE 52M
-- STANDBY LOGFILE
DATAFILE
'/oracle/app/oracle/oradata/orcl/system01.dbf',
'/oracle/app/oracle/oradata/orcl/undotbs01.dbf',
'/oracle/app/oracle/oradata/orcl/sysaux01.dbf',
'/oracle/app/oracle/oradata/orcl/users01.dbf',
'/oracle/app/oracle/oradata/orcl/lijie.dbf',
'/oracle/app/oracle/oradata/orcl/lijie_temp1.dbf',
'/oracle/app/oracle/oradata/orcl/undo01.dbf',
'/oracle/app/oracle/oradata/orcl/undo02.dbf'
CHARACTER SET ZHS16GBK
;
二、模拟控制文件丢失:
[oracle@localhost ~]$ cd /oracle/app/oracle/oradata/orcl
[oracle@localhost orcl]$ ls
archive control02.ctl example01.dbf lijie_temp1.dbf redo02_2.log redo02.log redo03.log sysaux01.dbf temp01.dbf undo02.dbf users01.dbf
control01.ctl control03.ctl lijie.dbf redo01.log redo02_3.log redo02.log.mv redo04.rdo system01.dbf undo01.dbf undotbs01.dbf
[oracle@localhost orcl]$ rm -f control01.ctl
[oracle@localhost orcl]$ rm -f control02.ctl
[oracle@localhost orcl]$ rm -f control03.ctl
三、启动DB 到nomount 下 ,创建controlfile(这时的数据库肯定是起不来了,想看启动错误的去试下):
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 16 10:17:38 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
SQL>
1 CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 2
4 MAXDATAFILES 30
5 MAXINSTANCES 1
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 2 '/oracle/app/oracle/oradata/orcl/redo02_2.log' SIZE 50M,
9 GROUP 3 '/oracle/app/oracle/oradata/orcl/redo03.log' SIZE 50M,
10 GROUP 4 '/oracle/app/oracle/oradata/orcl/redo04.rdo' SIZE 52M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/oracle/app/oracle/oradata/orcl/system01.dbf',
14 '/oracle/app/oracle/oradata/orcl/undotbs01.dbf',
15 '/oracle/app/oracle/oradata/orcl/sysaux01.dbf',
16 '/oracle/app/oracle/oradata/orcl/users01.dbf',
17 '/oracle/app/oracle/oradata/orcl/lijie.dbf',
18 '/oracle/app/oracle/oradata/orcl/lijie_temp1.dbf',
19 '/oracle/app/oracle/oradata/orcl/lijie_temp1.dbf',
20 '/oracle/app/oracle/oradata/orcl/undo01.dbf',
21 '/oracle/app/oracle/oradata/orcl/undo02.dbf'
22 CHARACTER SET ZHS16GBK
Control file created.
SQL>shutdown immediate; --也可以不关闭,但为了便于查看ERROR 在这里我shutdown 了
注意:datafile 后面的文件一定要加完整,否则在rman备份时会出现 RMAN-06056:
RMAN> backup database;
Starting backup at 16-MAR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
could not read file header for datafile 5 error reason 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/16/2010 10:29:08
RMAN-06056: could not access datafile 5
--这时应为在建立控制文件时 我少指定了个数据文件
四、打开数据库,做media recover:
SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oracle/app/oracle/oradata/orcl/example01.dbf'
SQL> recover datafile '/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-00279: change 1390779 generated at 03/16/2010 08:53:04 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_16/o1_mf_1_1_%u_.
arc
ORA-00280: change 1390779 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto ---我选的"AUTO"--我的ORACLE处于归档模式,且日志文件在默认位置
ORA-00279: change 1390780 generated at 03/16/2010 08:55:23 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_16/o1_mf_1_2_%u_.
arc
ORA-00280: change 1390780 for thread 1 is in sequence #2
ORA-00278: log file
'/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2010_03_16/o1_mf_1_1_5sx
oqw3x_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.