Oracle数据库恢复教程之resetlogs操作

2023-08-23 08:19:05 150

实验环境：RHEL5.4+Oracle11.2.0.3

如果是一名合格的OracleDBA，对resetlogs这种关键字都应该是极其敏感的，当确认需要这种操作时一定要三思而后行，如果自己不是特别确认，哪怕多花些时间申请去让高级DBA人员协助你一起确认，也不要擅自去尝试执行，避免误操作造成既定损失后追悔莫及。

1.哪些场景可以resetlogs

首先要明确resetlogs操作非常危险的，也只有在进行不完全恢复开库时会使用到。

SQL>alterdatabaseopenresetlogs;
->openthedatabaseandresettheonlinelogs

官方的描述如下：

Incompleterecovery,alsocalleddatabasepoint-in-timerecovery,resultsinanoncurrentversionofthedatabase.Inthiscase,youdonotapplyalloftheredogeneratedaftertherestoredbackup.Typically,youperformpoint-in-timedatabaserecoverytoundoausererrorwhenFlashbackDatabaseisnotpossible.
Toperformincompleterecovery,youmustrestorealldatafilesfrombackupscreatedbeforethetimetowhichyouwanttorecoverandthenopenthedatabasewiththeRESETLOGSoptionwhenrecoverycompletes.Resettingthelogscreatesanewstreamoflogsequencenumbersstartingwithlogsequence1.

官方的描述其实很清晰，但是实际很多初级DBA小伙伴们在实际工作中遇到这样的场景时却总是有些困惑，甚至误操作引发灾难。

我这里以一个实验来具体说明常见场景：

需求：A机数据库PROD1，现需在B机不同目录下用A机的备份集恢复出来；

A机：

--A机当前currentredolog的sequence是57:
SQL>select*fromv$log;

GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMNEXT_CHANGE#NEXT_TIME
--------------------------------------------------------------------------------------------------------------------------
1155524288005121YESINACTIVE205157219-MAY-19206036119-MAY-19
2156524288005121YESINACTIVE206036119-MAY-19206043619-MAY-19
3157524288005121NOCURRENT206043619-MAY-192.8147E+14

--A机做了一次数据库备份：
RMAN>backupdatabaseincludecurrentcontrolfileplusarchivelogdeleteallinput;


Startingbackupat19-MAY-19
currentlogarchived
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=23devicetype=DISK
allocatedchannel:ORA_DISK_2
channelORA_DISK_2:SID=189devicetype=DISK
allocatedchannel:ORA_DISK_3
channelORA_DISK_3:SID=21devicetype=DISK
channelORA_DISK_1:startingcompressedarchivedlogbackupset
channelORA_DISK_1:specifyingarchivedlog(s)inbackupset
inputarchivedlogthread=1sequence=57RECID=3STAMP=1008670991
channelORA_DISK_1:startingpiece1at19-MAY-19
channelORA_DISK_1:finishedpiece1at19-MAY-19
piecehandle=/home/oracle/backup/0cu1u68l_1_1.baktag=TAG20190519T102315comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01
channelORA_DISK_1:deletingarchivedlog(s)
archivedlogfilename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_57_860888149.dbfRECID=3STAMP=1008670991
Finishedbackupat19-MAY-19

Startingbackupat19-MAY-19
usingchannelORA_DISK_1
usingchannelORA_DISK_2
usingchannelORA_DISK_3
channelORA_DISK_1:startingcompressedfulldatafilebackupset
channelORA_DISK_1:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00002name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
channelORA_DISK_1:startingpiece1at19-MAY-19
channelORA_DISK_2:startingcompressedfulldatafilebackupset
channelORA_DISK_2:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00001name=/u01/app/oracle/oradata/PROD1/system01.dbf
inputdatafilefilenumber=00004name=/u01/app/oracle/oradata/PROD1/users01.dbf
channelORA_DISK_2:startingpiece1at19-MAY-19
channelORA_DISK_3:startingcompressedfulldatafilebackupset
channelORA_DISK_3:specifyingdatafile(s)inbackupset
inputdatafilefilenumber=00005name=/u01/app/oracle/oradata/PROD1/example01.dbf
inputdatafilefilenumber=00003name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
channelORA_DISK_3:startingpiece1at19-MAY-19
channelORA_DISK_3:finishedpiece1at19-MAY-19
piecehandle=/home/oracle/backup/0fu1u68p_1_1.baktag=TAG20190519T102319comment=NONE
channelORA_DISK_3:backupsetcomplete,elapsedtime:00:00:26
channelORA_DISK_3:startingcompressedfulldatafilebackupset
channelORA_DISK_3:specifyingdatafile(s)inbackupset
includingcurrentcontrolfileinbackupset
channelORA_DISK_3:startingpiece1at19-MAY-19
channelORA_DISK_3:finishedpiece1at19-MAY-19
piecehandle=/home/oracle/backup/0gu1u69j_1_1.baktag=TAG20190519T102319comment=NONE
channelORA_DISK_3:backupsetcomplete,elapsedtime:00:00:01
channelORA_DISK_1:finishedpiece1at19-MAY-19
piecehandle=/home/oracle/backup/0du1u68p_1_1.baktag=TAG20190519T102319comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:01:03
channelORA_DISK_2:finishedpiece1at19-MAY-19
piecehandle=/home/oracle/backup/0eu1u68p_1_1.baktag=TAG20190519T102319comment=NONE
channelORA_DISK_2:backupsetcomplete,elapsedtime:00:01:23
Finishedbackupat19-MAY-19

Startingbackupat19-MAY-19
currentlogarchived
usingchannelORA_DISK_1
usingchannelORA_DISK_2
usingchannelORA_DISK_3
channelORA_DISK_1:startingcompressedarchivedlogbackupset
channelORA_DISK_1:specifyingarchivedlog(s)inbackupset
inputarchivedlogthread=1sequence=58RECID=4STAMP=1008671084
channelORA_DISK_1:startingpiece1at19-MAY-19
channelORA_DISK_1:finishedpiece1at19-MAY-19
piecehandle=/home/oracle/backup/0hu1u6bg_1_1.baktag=TAG20190519T102446comment=NONE
channelORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01
channelORA_DISK_1:deletingarchivedlog(s)
archivedlogfilename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_58_860888149.dbfRECID=4STAMP=1008671084
Finishedbackupat19-MAY-19

StartingControlFileandSPFILEAutobackupat19-MAY-19
piecehandle=/home/oracle/backup/control/c-2082231315-20190519-01comment=NONE
FinishedControlFileandSPFILEAutobackupat19-MAY-19

RMAN>

--可以看到备份数据库的日志前后都自动归档了当前的redolog（57和58），所以备份完成后，当前日志sequence变为59.
SQL>select*fromv$log;

GROUP#THREAD#SEQUENCE#BYTESBLOCKSIZEMEMBERSARCSTATUSFIRST_CHANGE#FIRST_TIMNEXT_CHANGE#NEXT_TIME
--------------------------------------------------------------------------------------------------------------------------
1158524288005121YESINACTIVE206069119-MAY-19206076719-MAY-19
2159524288005121NOCURRENT206076719-MAY-192.8147E+14
3157524288005121YESINACTIVE206043619-MAY-19206069119-MAY-19

此时把备份集传输到B机，比如/u03/backup目录下，期望恢复到/u03/oradata/PROD1目录下。如果最终只是根据这个备份集去恢复，那最多恢复完sequence58就结束了，找不到sequence59（因为59还是当前current的redolog）。Oracle认为这就是最基本的不完全恢复，需要resetlogs操作。

--指定恢复到/u03/oradata/
RMAN>run{
2>setnewnamefordatabaseto'/u03/oradata/PROD1/%U';
3>restoredatabase;
4>}

--切换到上步恢复出来的copy复本：
RMAN>switchdatabasetocopy;

datafile1switchedtodatafilecopy"/u03/oradata/PROD1/data_D-PROD1_TS-SYSTEM_FNO-1"
datafile2switchedtodatafilecopy"/u03/oradata/PROD1/data_D-PROD1_TS-SYSAUX_FNO-2"
datafile3switchedtodatafilecopy"/u03/oradata/PROD1/data_D-PROD1_TS-UNDOTBS1_FNO-3"
datafile4switchedtodatafilecopy"/u03/oradata/PROD1/data_D-PROD1_TS-USERS_FNO-4"
datafile5switchedtodatafilecopy"/u03/oradata/PROD1/data_D-PROD1_TS-EXAMPLE_FNO-5"

--尝试恢复数据库：
RMAN>recoverdatabase;

Startingrecoverat19-MAY-19
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:SID=102devicetype=DISK
allocatedchannel:ORA_DISK_2
channelORA_DISK_2:SID=9devicetype=DISK
allocatedchannel:ORA_DISK_3
channelORA_DISK_3:SID=112devicetype=DISK

startingmediarecovery

channelORA_DISK_1:startingarchivedlogrestoretodefaultdestination
channelORA_DISK_1:restoringarchivedlog
archivedlogthread=1sequence=58
channelORA_DISK_1:readingfrombackuppiece/home/oracle/backup/0hu1u6bg_1_1.bak
channelORA_DISK_1:errorsfoundreadingpiecehandle=/home/oracle/backup/0hu1u6bg_1_1.bak
channelORA_DISK_1:failovertopiecehandle=/u03/backup/0hu1u6bg_1_1.baktag=TAG20190519T102446
channelORA_DISK_1:restoredbackuppiece1
channelORA_DISK_1:restorecomplete,elapsedtime:00:00:01
archivedlogfilename=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_58_860888149.dbfthread=1sequence=58
unabletofindarchivedlog
archivedlogthread=1sequence=59
RMAN-00571:===========================================================
RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:===========================================================
RMAN-03002:failureofrecovercommandat05/19/201911:04:21
RMAN-06054:mediarecoveryrequestingunknownarchivedlogforthread1withsequence59andstartingSCNof2060767

RMAN>

可以看到最后有报错信息，就是告诉你找不到sequence59的日志，这是必然的，因为59还是A机current的redo日志。

2.resetlogs前必须确认路径正确

2.1先查看控制文件和数据文件头记录的scn是否一致

SQL>selectcheckpoint_change#fromv$datafile;

CHECKPOINT_CHANGE#
------------------
2060767
2060767
2060767
2060767
2060767

SQL>selectcheckpoint_change#fromv$datafile_header;

CHECKPOINT_CHANGE#
------------------
2060767
2060767
2060767
2060767
2060767

2.2此时如果尝试直接OPEN会报错

SQL>alterdatabaseopen;
alterdatabaseopen
*
ERRORatline1:
ORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabaseopen

提示我们开库必须使用RESETLOGS或者NORESETLOGS选项。

2.3重点来了，现在可以openresetlogs吗？

当然不行！记得一定要确认好路径！！

--查询发现临时文件以及redo日志的路径都不是我们所期望的：
SQL>selectnamefromv$datafile;

NAME
--------------------------------------------------------------------------------
/u03/oradata/PROD1/data_D-PROD1_TS-SYSTEM_FNO-1
/u03/oradata/PROD1/data_D-PROD1_TS-SYSAUX_FNO-2
/u03/oradata/PROD1/data_D-PROD1_TS-UNDOTBS1_FNO-3
/u03/oradata/PROD1/data_D-PROD1_TS-USERS_FNO-4
/u03/oradata/PROD1/data_D-PROD1_TS-EXAMPLE_FNO-5

SQL>selectnamefromv$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/temp01.dbf

SQL>selectmemberfromv$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/redo03.log
/u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log

--rename重命名为我们期望的目录：
SQL>alterdatabaserenamefile'/u01/app/oracle/oradata/PROD1/temp01.dbf'to'/u03/oradata/PROD1/temp01.dbf';

Databasealtered.

SQL>alterdatabaserenamefile'/u01/app/oracle/oradata/PROD1/redo01.log'to'/u03/oradata/PROD1/redo01.log';

Databasealtered.

SQL>alterdatabaserenamefile'/u01/app/oracle/oradata/PROD1/redo02.log'to'/u03/oradata/PROD1/redo02.log';

Databasealtered.

SQL>alterdatabaserenamefile'/u01/app/oracle/oradata/PROD1/redo03.log'to'/u03/oradata/PROD1/redo03.log';

Databasealtered.

--再次检查确认：
SQL>selectnamefromv$tempfile;

NAME
--------------------------------------------------------------------------------
/u03/oradata/PROD1/temp01.dbf

SQL>selectmemberfromv$logfile;

MEMBER
--------------------------------------------------------------------------------
/u03/oradata/PROD1/redo03.log
/u03/oradata/PROD1/redo02.log
/u03/oradata/PROD1/redo01.log

--最终尝试open开库：
SQL>alterdatabaseopen;
alterdatabaseopen
*
ERRORatline1:
ORA-01589:mustuseRESETLOGSorNORESETLOGSoptionfordatabaseopen


SQL>alterdatabaseopenresetlogs;

Databasealtered.

总结：

很多初级人员有可能是对setnewnamefordatabase这个有误解，以为这里的database包括了临时文件，redo日志文件，误以为自己已经把新库所有路径都指向到了期望位置。但实际并不是这样，这也说明了不确认的操作一定要在测试环境测试验证后才可以在生产环境操作。大家可以想象一下，如果是理解有误没确认日志路径直接执行了resetlogs，那么如果B机正好有别的库用到同名的这些路径，亦或是整个恢复操作就是直接在A机的本机其他目录临时基于某个时间点恢复出一套库，那将会是一场大的生产事故。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

Oracle数据库恢复教程之resetlogs操作

热门推荐

随机推荐