通过使用resetlog恢复控制文件恢复数据库

环境：rhel 5.5,Oracle10.2.0.1xmanager4.0通过备份控制文件后，对数据库进行一系列的操作，关闭数据库；恢复控制文件，启动到mount阶段在使用resetlogs重新创建控制文件，找回我们的数据。备份控制文件backupdatafile 1;RMAN>backup datafile 1;RMAN>backup datafile 1;Startingbackup at 26-JUN-12usingchannel ORA_DISK_1channelORA_DISK_1: starting compressed full datafile backupsetchannelORA_DISK_1: specifying datafile（s） in backupsetinputdatafile fno=00001 name=/opt/oracle/product/10.2.0/oradata/cuug/system01.dbfchannelORA_DISK_1: starting piece 1 at 26-JUN-12channelORA_DISK_1: finished piece 1 at 26-JUN-12piecehandle=/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/backupset/2012_06_26/o1_mf_nnndf_TAG20120626T114501_7yld1y1q_.bkptag=TAG20120626T114501 comment=NONEchannelORA_DISK_1: backup set complete, elapsed time: 00:08:06channelORA_DISK_1: throttle time: 0:06:39Finishedbackup at 26-JUN-12 StartingControl File Autobackup at 26-JUN-12piecehandle=/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/autobackup/2012_06_26/o1_mf_n_786973989_7yldk6mm_.bkpcomment=NONEFinishedControl 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_bakSQL>select username,default_tablespace from dba_users where username="SCOTT"; USERNAME DEFAULT_TABLESPACE------------------------------------------------------------SCOTT CUUGSQL>create table scott.a as select * from tab; Tablecreated. SQL>create table scott.aa as select * fromtab; SQL>select count（*） from scott.a; COUNT（*）---------- 3642SQL>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 abortORACLEinstance shut down. 删除控制文件，修改cuug的数据文件[oracle@rhel5cuug]$ mkdir bak[oracle@rhel5cuug]$ mv *.ctl bak/[oracle@rhel5cuug]$ mv cuug01.dbf cuug01.bakSQL>startupORACLEinstance started. TotalSystem Global Area 218103808 bytesFixedSize 1218604 bytesVariableSize 62916564 bytesDatabaseBuffers 150994944 bytesRedoBuffers 2973696 bytesORA-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-12usingchannel ORA_DISK_1 channelORA_DISK_1: restoring control filechannelORA_DISK_1: restore complete, elapsed time: 00:00:03outputfilename=/opt/oracle/product/10.2.0/oradata/cuug/control01.ctloutputfilename=/opt/oracle/product/10.2.0/oradata/cuug/control02.ctloutputfilename=/opt/oracle/product/10.2.0/oradata/cuug/control03.ctlFinishedrestore 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 immediateORA-01109:database not open Databasedismounted.ORACLEinstance shut down. SQL>startup nomount;ORACLEinstance started. TotalSystem Global Area 218103808 bytesFixedSize 1218604 bytesVariableSize 79693780 bytesDatabaseBuffers 134217728 bytesRedoBuffers 2973696 bytes 使用noresetlogs创建，因为联机日志还在，所以可以使用noresetlogs的方法创建[oracle@rhel5orcl]$ ls *.ctlcontrol01.ctl control02.ctl control03.ctl[oracle@rhel5orcl]$ rm *.ctl [oracle@rhel5udump]$ vi cuug_ora_4744_cuug.trcCREATECONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOGMAXLOGFILES16MAXLOGMEMBERS3MAXDATAFILES100MAXINSTANCES8MAXLOGHISTORY292LOGFILE 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 LOGFILEDATAFILE "/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/UNNAMED00007SQL>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 1ORA-00289:suggestion :/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/archivelog/2012_06_26/o1_mf_1_10_%u_.arcORA-00280:change 708399 for thread 1 is in sequence #10 Specifylog: {<RET>=suggested | filename | AUTO | CANCEL}/opt/oracle/product/10.2.0/oradata/cuug/redo02.logLogapplied.Mediarecovery completeSQL>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恢复完成。 Oracle 10g的catalog配置MySQL 5.6 MRR 的存储过程完美诠释相关资讯 resetlog 本文评论查看全部评论（0）