通过使用resetlog恢复控制文件恢复数据库
环境： rhel 5.5,Oracle10.2.0.1 xmanager4.0 通过备份控制文件后，对数据库进行一系列的操作，关闭数据库；恢复控制文件，启动到
环境：
rhel 5.5,Oracle10.2.0.1
xmanager4.0
通过备份控制文件后，对数据库进行一系列的操作，关闭数据库；恢复控制文件，启动到mount阶段在使用resetlogs重新创建控制文件，找回我们的数据。
备份控制文件
backupdatafile 1;
RMAN>backup datafile 1;
RMAN>backup datafile 1;
Startingbackup at 26-JUN-12
usingchannel ORA_DISK_1
channelORA_DISK_1: starting compressed full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
inputdatafile fno=00001 name=/opt/oracle/product/10.2.0/oradata/cuug/system01.dbf
channelORA_DISK_1: starting piece 1 at 26-JUN-12
channelORA_DISK_1: finished piece 1 at 26-JUN-12
piecehandle=/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/backupset/2012_06_26/o1_mf_nnndf_TAG20120626T114501_7yld1y1q_.bkptag=TAG20120626T114501 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:08:06
channelORA_DISK_1: throttle time: 0:06:39
Finishedbackup at 26-JUN-12
StartingControl File Autobackup at 26-JUN-12
piecehandle=/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/autobackup/2012_06_26/o1_mf_n_786973989_7yldk6mm_.bkpcomment=NONE
FinishedControl File Autobackup at 26-JUN-12
做一些操作和日志切换
SQL>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
-------------------- ----------------
1 9 INACTIVE
2 10 CURRENT
3 7 INACTIVE
4 8 INACTIVE
[oracle@rhel5cuug]$ cp cuug01.dbf cuug01.dbf_bak
SQL>select username,default_tablespace from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE
------------------------------------------------------------
SCOTT CUUG
SQL>create table scott.a as select * from tab;
Tablecreated.
SQL>create table scott.aa as select * fromtab;
SQL>select count(*) from scott.a;
COUNT(*)
----------
3642
SQL>create tablespace test datafile'/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf' size 100m;
Tablespacecreated.
Tablespacecreated.
SQL>alter system switch logfile;
Systemaltered.
SQL>select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/users01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/system01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf
SQL>select group#,status,sequence# from v$log;
GROUP#STATUS SEQUENCE#
-------------------------- ----------
1INACTIVE 9
2CURRENT 10
3INACTIVE 7
4INACTIVE 8
SQL>shutdown abort
ORACLEinstance shut down.
删除控制文件，修改cuug的数据文件
[oracle@rhel5cuug]$ mkdir bak
[oracle@rhel5cuug]$ mv *.ctl bak/
[oracle@rhel5cuug]$ mv cuug01.dbf cuug01.bak
SQL>startup
ORACLEinstance started.
TotalSystem Global Area 218103808 bytes
FixedSize 1218604 bytes
VariableSize 62916564 bytes
DatabaseBuffers 150994944 bytes
RedoBuffers 2973696 bytes
ORA-00205:error in identifying control file, check alert log for more info
恢复控制文件
[oracle@rhel510.2.0]$ rman target /
RecoveryManager: Release 10.2.0.1.0 - Production on Mon Jun 25 10:26:22 2012
Copyright(c) 1982, 2005, Oracle. All rightsreserved.
connectedto target database: orcl (not mounted)
RMAN>restore controlfile from'/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/autobackup/2012_06_26/o1_mf_n_786973989_7yldk6mm_.bkp';
Startingrestore at 26-JUN-12
usingchannel ORA_DISK_1
channelORA_DISK_1: restoring control file
channelORA_DISK_1: restore complete, elapsed time: 00:00:03
outputfilename=/opt/oracle/product/10.2.0/oradata/cuug/control01.ctl
outputfilename=/opt/oracle/product/10.2.0/oradata/cuug/control02.ctl
outputfilename=/opt/oracle/product/10.2.0/oradata/cuug/control03.ctl
Finishedrestore at 26-JUN-12
把控制文件
标记trace文件
SQL>alter session set tracefile_identifier='cuug';
Sessionaltered.
SQL>alter database mount;
Databasealtered.
SQL>alter database backup controlfile to trace;
Databasealtered.
重建控制文件，先关闭数据库，删除之前从备份中恢复出来的控制文件，启动到nomount阶段
SQL>shutdown immediate
ORA-01109:database not open
Databasedismounted.
ORACLEinstance shut down.
SQL>startup nomount;
ORACLEinstance started.
TotalSystem Global Area 218103808 bytes
FixedSize 1218604 bytes
VariableSize 79693780 bytes
DatabaseBuffers 134217728 bytes
RedoBuffers 2973696 bytes
使用noresetlogs创建，因为联机日志还在，所以可以使用noresetlogs的方法创建
[oracle@rhel5orcl]$ ls *.ctl
control01.ctl control02.ctl control03.ctl
[oracle@rhel5orcl]$ rm *.ctl
[oracle@rhel5udump]$ vi cuug_ora_4744_cuug.trc
CREATECONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP 1'/opt/oracle/product/10.2.0/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2'/opt/oracle/product/10.2.0/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3'/opt/oracle/product/10.2.0/oradata/orcl/redo03.log' SIZE 50M,
GROUP 4'/opt/oracle/product/10.2.0/oradata/orcl/redo04.log' SIZE 50M
--STANDBY LOGFILE
DATAFILE
'/opt/oracle/product/10.2.0/oradata/orcl/system01.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/sysaux01.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/users01.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/rmans01.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/cuug01.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/ORCL/datafile/o1_mf_zxm_7ycm0twl_.dbf',
'/opt/oracle/product/10.2.0/oradata/orcl/ORCL/datafile/o1_mf_cuug_7ycm20jq_.dbf'
CHARACTERSET UTF8
;
此时打开数据库会提示错误
首先要恢复数据文件
查询数据文件
SQL>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/product/10.2.0/oradata/cuug/system01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/users01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf
/opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00007
SQL>alter database rename file '/opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00007'to '/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf';
SQL>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/product/10.2.0/oradata/cuug/system01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/users01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf
/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf
目前数据文件还不一致，需要进行介质恢复，，但是不用using子句
SQL>recover database using backup controlfile;
ORA-00279:change 708399 generated at 06/26/2012 12:11:13 needed for thread 1
ORA-00289:suggestion :
/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/archivelog/2012_06_26/o1_mf_
1_10_%u_.arc
ORA-00280:change 708399 for thread 1 is in sequence #10
Specifylog: {
/opt/oracle/product/10.2.0/oradata/cuug/redo02.log
Logapplied.
Mediarecovery complete
SQL>alter database open resetlogs;
Databasealtered.
查询恢复状态
selectfile_name,tablespace_name,bytes/1024/1024 MB fromdba_data_files
FILE_NAME TABLESPACE_NAME MB
------------------------------------------------------------------------------------------ ----------
/opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf CUUG 200
/opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf RMANS 500
/opt/oracle/product/10.2.0/oradata/cuug/users01.dbf USERS 5
/opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf SYSAUX 250
/opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf UNDOTBS1 25
/opt/oracle/product/10.2.0/oradata/cuug/system01.dbf SYSTEM 480
/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf TEST 100
SQL>select count(*) from scott.a
2 ;
COUNT(*)
----------
3642
SQL>select count(*) from scott.aa;
COUNT(*)
----------
3642
恢复完成。