一、shutdown Oracle数据库,用数据文件覆盖以前的数据文件,并将控制文件及重做日志文件删除;
二、切换到oracle用户并进入Sqlplus,用sys用户连接到oracle;
三、先关闭然后再启动数据库,但不挂载数据文件;
SQL> shutdown immediate;
SQL> startup nomount;
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
--SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/databar/redo01.log' SIZE 100M,
GROUP 2 '/u01/app/oracle/oradata/databar/redo02.log' SIZE 100M,
GROUP 3 '/u01/app/oracle/oradata/databar/redo03.log' SIZE 100M
--STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/databar/users01.dbf',
'/u01/app/oracle/oradata/databar/undotbs01.dbf',
'/u01/app/oracle/oradata/databar/system01.dbf',
'/u01/app/oracle/oradata/databar/sysaux01.dbf'
--CHARACTER SET ZHS16GBK(依情况可省略)
;
五、创建控制文件成功后,执行以下语句打开数据库,加上RESETLOGS参数是为了重新生成重做日志文件;
SQL> alter database open RESETLOGS;
如果出现以下错误:
alter database open resetlogs
*
第 1 行出现错误:
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1:'/u01/app/oracle/oradata/databar/system01.dbf'
解决方法如下:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/databar/system01.dbf'
如果出现上面的错误,则输入以下语句:
SQL> set wrap off
SQL> set lin 300
SQL> select file#,ts#,status,name from v$datafile;
#查看下当前数据文件的状态
然后再执行下面的SQL语句
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
如果在上面的SQL语句执行失败后在执行下面的SQL语句
SQL> recover database using backup controlfile until cancel; 执行下面的SQL语句之后会出阿仙下面的提示,
ORA-00279: change 476049 generated at 01/08/2010 12:35:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/DATABAR/archivelog/2010_08_01/2010_01_08O1_MF_1_18_%U_.ARC
ORA-00280: change 476049 for thread 1 is in sequence #18
Specify log: {=suggested | filename | AUTO | CANCEL}
#在此处先填写日志文件REDO01.LOG' 的具体路径(在的第四步的生成控制文件的脚本中),然后回车,如果还出现错误,那么再执行
#recover database using backup controlfile until cancel;
#然后在填写 REDO02.LOG' ' 的具体路径
#直到输入文件路径回车之后出现以下成功提示:
Log applied.
Media recovery complete.
六、执行以下语句打开数据库,加上RESETLOGS参数是为了重新生成重做日志文件;*注意一定要执行这句SQl语句
SQL> alter database open resetlogs;
Database altered.
七、将临时表空间加入到实例上,加以重用:
SQL> alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/databar/TEMP01.DBF' reuse;
至此,数据库恢复完成,现在也可以利用imp导入正式服务器上的最新数据了。
-------------------自己测试文档:
*******************************
一、shutdown Oracle数据库,用数据文件覆盖以前的数据文件,并将控制文件及重做日志文件删除;
二、切换到oracle用户并进入Sqlplus,用sys用户连接到oracle;
三、先关闭然后再启动数据库,但不挂载数据文件;
SQL> shutdown immediate;
SQL> startup nomount;
四、创建控制文件
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
--SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:/app/Administrator/oraData/ORCL/REDO01.LOG' SIZE 50M,
GROUP 2 'D:/app/Administrator/oraData/ORCL/REDO02.LOG' SIZE 50M,
GROUP 3 'D:/app/Administrator/oraData/ORCL/REDO03.LOG' SIZE 50M
--STANDBY LOGFILE
DATAFILE
'D:/app/Administrator/oraData/ORCL/USERS01.DBF',
'D:/app/Administrator/oraData/ORCL/UNDOTBS01.DBF',
'D:/app/Administrator/oraData/ORCL/SYSAUX01.DBF',
'D:/app/Administrator/oraData/ORCL/SYSTEM01.DBF',
'D:/app/Administrator/oraData/ORCL/BHDATA.DBF',
'D:/app/Administrator/oraData/ORCL/BHMAIL.DBF',
'D:/app/Administrator/oraData/ORCL/BHINDEX.DBF',
'D:/app/Administrator/oraData/ORCL/ZHADVICES.DBF',
'D:/app/Administrator/oraData/ORCL/ZHARCHIVES.DBF',
'D:/app/Administrator/oraData/ORCL/ZHBASIS.DBF',
'D:/app/Administrator/oraData/ORCL/ZHEXPENSES.DBF',
'D:/app/Administrator/oraData/ORCL/ZHINDEX.DBF',
'D:/app/Administrator/oraData/ORCL/ZHLAB.DBF',
'D:/app/Administrator/oraData/ORCL/ZHMEDICINE.DBF',
'D:/app/Administrator/oraData/ORCL/ZHSERVICES.DBF'
;
五、创建控制文件成功后,执行以下语句打开数据库,加上RESETLOGS参数是为了重新生成重做日志文件;
SQL> alter database open RESETLOGS;
SQL> recover database until cancel;
SQL> recover database using backup controlfile until cancel;
--按照提示填写
D:/app/Administrator/oraData/ORCL/REDO02.LOG
SQL> alter database open RESETLOGS;
---由于密码文件没有覆盖
SQL> Alter User system identified By oracle;
--由于没有控制临时表空间 增加临时表空间
SQL> Alter TABLESPACE TEMP Add Tempfile 'D:/app/Administrator/oraData/ORCL/TEMP01.DBF' SIZE 50M AUTOEXTEND ON;
备注:如果有大数据类型的数据存储:例如Xml Clob数据的话,就可能出此案数据块的损坏问题