【Vegas原创】ORA-01194解决方法：使用在线日志恢复数据库

2年前 (2022) 程序员胖胖胖虎阿

165 0 0

情形： 数据文件要迁移，所以我在open的状态下，物理复制了数据文件，又mount改了数据文件路径。这可好了，不让open了，提示ORA-01194，说system01.dbf需要recover~

以下是我更改路径的SQL脚本：

alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\SYSTEM01.DBF' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\SYSTEM01.DBF';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\UNDOTBS01.DBF' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\UNDOTBS01.DBF';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\SYSAUX01.DBF' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\SYSAUX01.DBF';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\USERS01.DBF' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\USERS01.DBF';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\EXAMPLE01.DBF' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\EXAMPLE01.DBF';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\DRSYS' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\DRSYS';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\INDX' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\INDX';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\RBS' to 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\RBS';
alter database rename file 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\TOOLS' TO 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\TOOLS'

解决方法：这种情况下，在线日志文件肯定没丢，所以，恢复起来就容易很多了，起因是我刚才先物理复制，之后才shutdown，mount，rename，所以使用修改路径前的redolog进行在线日志解决就行：

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10161838 generated at 04/13/2010 09:41:05 needed for thread 1
ORA-00289: suggestion :
F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\HISTEST\ARCHIVELOG\2010_04_13\O1_MF

_1_15_%U_.ARC
ORA-00280: change 10161838 for thread 1 is in sequence #15

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\product\10.2.0\oradata\hisdb\redo01.log
ORA-00310: archived log contains sequence 14; sequence 15 required
ORA-00334: archived log: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISDB\REDO01.LOG'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\HISTEST\SYSTEM01.DBF'

_1_15_%U_.ARC
ORA-00280: change 10161838 for thread 1 is in sequence #15

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\product\10.2.0\oradata\hisdb\redo02.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;