Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of the resetlogs operation of the Oracle database recovery tutorial. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Experimental environment: RHEL 5.4 + Oracle 11.2.0.3

If it is a qualified Oracle DBA, the keyword resetlogs should be extremely sensitive, when confirming the need for this kind of operation must think twice, if you are not particularly sure, even if you spend more time to apply for senior DBA personnel to assist you to confirm, do not attempt to implement, to avoid misoperation caused by established losses after regret.

1. Which scenes can be resetlogs

First of all, it is important to make it clear that the resetlogs operation is very dangerous and will only be used when the library is not fully restored.

SQL > alter database open resetlogs;- > open the database and reset the online logs

The official description is as follows:

Incomplete recovery, also called database point-in-time recovery, results in a noncurrent version of the database. In this case, you do not apply all of the redo generated after the restored backup. Typically, you perform point-in-time database recovery to undo a user error when Flashback Database is not possible.

To perform incomplete recovery, you must restore all data files from backups created before the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. Resetting the logs creates a new stream of log sequence numbers starting with log sequence 1.

The official description is actually very clear, but in fact, many junior DBA partners are always confused when they encounter such a scene in their actual work, and even misoperation leads to disaster.

Here I use an experiment to illustrate common scenarios:

Requirement: machine A database PROD1, which needs to be recovered from the backup set of machine An in different directories of machine B.

Machine A:

-- the sequence of the current current redolog of machine An is 57:SQL > select * from v$log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME-- -1 1 55 52428800 512 1 YES INACTIVE 2051572 19-MAY-19 2060361 19-MAY-19 2 1 56 52428800 512 1 YES INACTIVE 2060361 19-MAY-19 2060436 19-MAY-19 3 15752428800 512 1 NO CURRENT 2060436 19-MAY-19 2.8147Ehammer 14Muir-A machine did it once. Database backup: RMAN > backup database include current controlfile plus archivelog delete all input Starting backup at 19-MAY-19current log archivedallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=23 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=189 device type=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: SID=21 device type=DISKchannel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread=1 sequence=57 RECID=3 STAMP=1008670991channel ORA_DISK_1: starting piece 1 at 19-MAY-19channel ORA_DISK_1: finished piece 1 at 19 Murray- 19piece handle=/home/oracle/backup/0cu1u68l_1_1.bak tag=TAG20190519T102315 comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log (s) archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_57_860888149.dbf RECID=3 STAMP=1008670991Finished backup at 19-MAY-19Starting backup at 19-MAY-19using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00002 name=/u01/app / oracle/oradata/PROD1/sysaux01.dbfchannel ORA_DISK_1: starting piece 1 at 19-MAY-19channel ORA_DISK_2: starting compressed full datafile backup setchannel ORA_DISK_2: specifying datafile (s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbfchannel ORA_DISK_2: starting piece 1 at 19-MAY-19channel ORA_DISK_3: starting compressed full datafile backup setchannel ORA_DISK_3: Specifying datafile (s) in backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbfchannel ORA_DISK_3: starting piece 1 at 19-MAY-19channel ORA_DISK_3: finished piece 1 at 19-MAY-19piece handle=/home/oracle/backup/0fu1u68p_1_1.bak tag=TAG20190519T102319 comment=NONEchannel ORA_DISK_3: backup set complete Elapsed time: 00:00:26channel ORA_DISK_3: starting compressed full datafile backup setchannel ORA_DISK_3: specifying datafile (s) in backup setincluding current control file in backup setchannel ORA_DISK_3: starting piece 1 at 19-MAY-19channel ORA_DISK_3: finished piece 1 at 19-MAY-19piece handle=/home/oracle/backup/0gu1u69j_1_1.bak tag=TAG20190519T102319 comment=NONEchannel ORA_DISK_3: backup set complete Elapsed time: 00:00:01channel ORA_DISK_1: finished piece 1 at 19-MAY-19piece handle=/home/oracle/backup/0du1u68p_1_1.bak tag=TAG20190519T102319 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:03channel ORA_DISK_2: finished piece 1 at 19-MAY-19piece handle=/home/oracle/backup/0eu1u68p_1_1.bak tag=TAG20190519T102319 comment=NONEchannel ORA_DISK_2: backup set complete Elapsed time: 00:01:23Finished backup at 19-MAY-19Starting backup at 19-MAY-19current log archivedusing channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread=1 sequence=58 RECID=4 STAMP=1008671084channel ORA_DISK_1: starting piece 1 at 19-MAY-19channel ORA_DISK_1: finished piece 1 at 19-MAY-19piece handle=/home/oracle/backup/0hu1u6bg_1_1.bak tag=TAG20190519T102446 comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log (s) archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_58_860888149.dbf RECID=4 STAMP=1008671084Finished backup at 19-MAY-19Starting Control File and SPFILE Autobackup at 19-MAY-19piece handle=/home/oracle/backup/control/c-2082231315-20190519-01 comment=NONEFinished Control File and SPFILE Autobackup at 19-MAY-19RMAN >-- you can see that the log of the backup database is automatic before and after Archived the current redolog (57 and 58) So after the backup is completed, the current log sequence changes to 59.SQL > select * from v$log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME-- -1 1 58 52428800 512 1 YES INACTIVE 2060691 19-MAY-19 2060767 19-MAY-19 2 1 59 52428800 512 1 NO CURRENT 2060767 19-MAY-19 2.8147E+14 3 1 57 52428800 512 1 YES INACTIVE 2060436 19-MAY-19 2060691 19-MAY-19

At this point, transfer the backup set to machine B, such as / u03/backup directory, and expect to restore it to / u03/oradata/PROD1 directory. If the final restore is based on this backup set, at most the recovery of sequence 58 will be over, and sequence 59 will not be found (because 59 is still the redolog of the current current). Oracle believes that this is the most basic incomplete recovery and requires a resetlogs operation.

-- specify to restore to / u03/oradata/RMAN > run {2 > set newname for database to'/ u03 RMAN'/ u03 RMAN oradata; PROD1amp% Upright transaction 3 > restore database;4 >}-- switch to the recovered oradata copy from the previous step: RMAN > switch database to copy Datafile 1 switched to datafile copy "/ u03/oradata/PROD1/data_D-PROD1_TS-SYSTEM_FNO-1" datafile 2 switched to datafile copy "/ u03/oradata/PROD1/data_D-PROD1_TS-SYSAUX_FNO-2" datafile 3 switched to datafile copy "/ u03/oradata/PROD1/data_D-PROD1_TS-UNDOTBS1_FNO-3" datafile 4 switched to datafile copy "/ u03/oradata/PROD1/data_D-PROD1_TS-USERS_FNO-4" datafile 5 switched to datafile copy "/ U03/oradata/PROD1/data_D-PROD1_TS-EXAMPLE_FNO-5 "- try to recover the database: RMAN > recover database Starting recover at 19-MAY-19using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=102 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=9 device type=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: SID=112 device type=DISKstarting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=58channel ORA_DISK_1: reading from backup piece / home/oracle/backup/0hu1u6bg_1_1.bakchannel ORA_DISK_1 : errors found reading piece handle=/home/oracle/backup/0hu1u6bg_1_1.bakchannel ORA_DISK_1: failover to piece handle=/u03/backup/0hu1u6bg_1_1.bak tag=TAG20190519T102446channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete Elapsed time: 00:00:01archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_58_860888149.dbf thread=1 sequence=58unable to find archived logarchived log thread=1 sequence=59RMAN-00571: = = RMAN-00569: = ERROR MESSAGE STACK FOLLOWS = RMAN-00571: = = RMAN-03002: failure of recover command at 05 with sequence 19 11:04:21RMAN-06054 2019 11:04:21RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 59 and starting SCN of 2060767RMAN >

You can see that there is an error message at last, which tells you that you cannot find the log of sequence 59, which is inevitable, because 59 is still the redo log of A machine current.

You must verify that the path is correct before 2.resetlogs

2.1 check whether the scn recorded in the header of the control file and the data file are consistent first

SQL > select checkpoint_change# from vaulting datafile / CHECKPOINTCHANGELEN-2060767 2060767 2060767 2060767SQL > select checkpoint_change# from VIPINTCHECKPOINTCHANGELES headers-2060767 2060767 2060767

2.2 an error will be reported if you try to OPEN directly at this time.

SQL > alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Prompt us to open the library must use the RESETLOGS or NORESETLOGS option.

2.3 here comes the point. Can we open resetlogs now?

Of course not! Remember to confirm the path!

-- query finds temporary files and redo log paths are not what we expected: SQL > select name from v$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-5SQL > select name from v$tempfile NAME----/u01/app/oracle/oradata/PROD1/temp01.dbfSQL > select member from v$logfile MEMBER----/u01/app/oracle/oradata/PROD1/redo03.log/u01/app/oracle/oradata/PROD1/redo02.log/u01/app/oracle/oradata/PROD1/ The redo01.log--rename is renamed to the directory we expect: SQL > alter database rename file'/ u01 _ name _ Database altered.SQL > alter database rename file'/ u01Accord oradata Database altered.SQL > redo01.log' to'/ u03redo01.log'to'/ u03inoradataandredo01.logTranslationDatabase altered.SQL > alter database rename file'/ u01inoradataUniplicationPROD1andredo02.logataPROD1andredo02.logadatabase altered.SQL'/ u03aporadatalPROD1redo02.logadatabase database altered.SQL > alter database rename file'/ u01aporacleandoradataandPROD1redo03.log'/ u03oradataPROD1andredo03.log NAME----/u03/oradata/PROD1/temp01.dbfSQL > select member from v$logfile MEMBER----/u03/oradata/PROD1/redo03.log/u03/oradata/PROD1/redo02.log/u03/oradata/PROD1/redo01.log-- finally tries to open open library: SQL > alter database open Alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL > alter database open resetlogs;Database altered. Thank you for reading! On the "Oracle database recovery tutorial resetlogs operation example analysis," this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.