Recovery method in the case of BBED losing Archive Files 01/30 Update SLTechnology News&Howtos

Recovery method in the case of BBED losing Archive Files

2025-01-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

BBED lost archived files in the case of recovery methods, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

[BBED] recovery of data files in the case of missing archived files

1.1 BLOG document structure map

1.2 introduction 1.2.1 introduction and precautions

Technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) Olympiad:

If ① loses the recovery of data files in the case of archiving, bbed and implied parameters (key)

Media recovery in the process of ② database startup and the relationship between scn number

How ③ BBED modifies file headers

Complete database in ④ archiving and non-archiving mode

Tips:

① if the article code format is out of order, it is recommended to use QQ, Sogou or 360browser, you can also download the pdf format document to view, pdf document download address: http://yunpan.cn/cdEQedhCs2kFz (extraction code: ed9b)

② I use a gray background and pink font to show the areas that require special attention in the output part of the command in this BLOG. For example, in the following example, the maximum archive log number of thread 1 is 33 thread 2, and the maximum archive log number is 43, which requires special attention; while the command generally uses a yellow background and red font marking; the comments on the code or the output part of the code are generally expressed in blue font.

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

[ZFXXDB1:root]: / > lsvg-o

T_XDESK_APP1_vg

Rootvg

[ZFXXDB1:root]: / >

00:27:22 SQL > alter tablespace idxtbs read write

= "2097152, 512, 1024, 1024, 1024, 1G

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

1.2.2 links to related reference articles

ORACLE 11g TSPITR restores deleted tablespace http://blog.itpub.net/26736162/viewspace-1681706/

[RMAN] replacement variable is used in RMAN script-- rman complete script http://blog.itpub.net/26736162/viewspace-1673725/ under windows

[TSPITR] automatic recovery http://blog.itpub.net/26736162/viewspace-1671741/ based on point-in-time in RMAN tablespace

[recommended] [RMAN] recovery procedure http://blog.itpub.net/26736162/viewspace-1623938/ for misoperation of rm-rf

[recommended] [RMAN] restore database with backup (medium)-additional http://blog.itpub.net/26736162/viewspace-1621938/

[recommended] [RMAN] use backup to restore database (part two) http://blog.itpub.net/26736162/viewspace-1621672/

[recommended] [RMAN] use backup to restore database (medium) http://blog.itpub.net/26736162/viewspace-1621661/

[recommended] [RMAN] use backup to restore database (part I) http://blog.itpub.net/26736162/viewspace-1621581/

[recommended] [RMAN] RMAN cross-version recovery (part two) http://blog.itpub.net/26736162/viewspace-1562583/

[recommended] summary of Oracle component series http://blog.itpub.net/26736162/viewspace-1562441/

[recommended] [RMAN] RMAN cross-version recovery (medium) http://blog.itpub.net/26736162/viewspace-1561352/

[recommended] [RMAN] RMAN cross-version recovery (part I) http://blog.itpub.net/26736162/viewspace-1561185/

[recommended] FAQ about restoring or copying between different versions and platforms http://blog.itpub.net/26736162/viewspace-1549041/

[recommended] recovery of lost undo tablespace files (4)-- restore http://blog.itpub.net/26736162/viewspace-1458787/ without backup and recover

[recommended] recovery of lost undo tablespace files (3)-- restore http://blog.itpub.net/26736162/viewspace-1458750/ without backup and redo

[recommended] recovery of lost undo tablespace files (2)-- restore http://blog.itpub.net/26736162/viewspace-1458663/ without backup and redo

[recommended] recovery of lost undo tablespace files (1)-- backup http://blog.itpub.net/26736162/viewspace-1458654/

[recommended] oracle controls the recovery of http://blog.itpub.net/26736162/viewspace-1426552/ for files without archived logs

[recommended] ORACLE read-only data file backup and recovery http://blog.itpub.net/26736162/viewspace-1425283/

1.2.3 introduction to this article

Some time ago, the company trained us to recover truncate data from dul. In the next few days, I have been studying the recovery of truncate. I want to summarize the recovery method of truncate, but when I encounter BBED, I can recover through BBED, but I am not familiar with this tool. I have never used it before. In fact, I have heard about this tool for a long time and always wanted to learn BBED, but I have not learned it because I have not encountered practical use. Now I have studied the BBED first. The recovery of truncate will be sent to blog after a period of time.

This article first introduces some knowledge points of media recovery in the process of database startup, and then introduces the method of BBED modifying the data file header to promote SCN to achieve complete recovery, instead of using resetlogs to open the database, using the implicit parameter _ allow_resetlogs_corruption to open the database is just an episode.

When we do the experiment, we will experiment in two situations, one is the linux environment, the other is the aix environment. Under linux, we use bbed and the implicit parameter _ allow_resetlogs_corruption to recover, in which the scn number is pushed directly to the latest scn number, which is consistent with the scn number of other data file headers. In the aix environment, we delete one of the archive files and modify the scn number of the missing archive file when we modify scn. This simulates as little data loss as possible and deepens the understanding of redo apply.

1.3 Literacy of related knowledge points (excerpt from the Internet)

The relevant knowledge points of this chapter still need to know, wheat seedlings have been sorted out, and the previous blog is not quite the same, the content is a little more.

1.3.1 Media recovery during database startup

There is a close relationship between scn and the recovery process of oracle database. Only when we have a good understanding of this relationship, can we deeply understand the principle of recovery and solve the problems in this aspect.

1.3.1.1 SCN and CHECKPOINT

When the checkpoint occurs, the CKPT process writes the current SCN number to the data file header and the control file, and informs the DBWR process to write the data block to the data file.

The CKPT process also records RBA (redo block address) in the control file to indicate where the Recovery needs to start in the log.

There are four SCN numbers associated with checkpoint, three of which are stored in the control file and one in the header of the data file.

These four are:

1.System Checkpoint SCN

When the checkpoint is completed, ORACLE stores the System Checkpoint SCN number in the control file.

We can query it with the following SQL statement:

Select checkpoint_change# from v$database

2.Datafile Checkpoint SCN

When the checkpoint is completed, ORACLE stores the Datafile Checkpoint SCN number in the control file.

We can query the Datafile Checkpoinnt SCN numbers of all data files with the following SQL statement.

Select name,checkpoint_change# from v$datafile

3.Start SCN number

ORACLE stores the Start SCN number in the header of the data file.

This SCN is used to check whether the database startup process needs to do media recovery.

We can query through the following SQL statement:

Select name,checkpoint_change# from v$datafile_header

4.End SCN number

ORACLE stores the End SCN number in the control file.

This SCN number is used to check whether the database startup process needs to do instance recovery.

We can query through the following SQL statement:

Select name,last_change# from v$datafile

Under the condition of normal operation of the database, for the readable and writable data file of online, the SCN number is NULL.

Select checkpoint_change# from v$database

SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a

SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a

Select file#,online_status,change# from v$recover_file

1.3.1.2 SCN number and database startup and shutdown

In the process of database startup, when the System Checkpoint SCN, Datafile Checkpoint SCN and Start SCN numbers are all the same, the database can be started normally without media recovery. When one of the three is different, you need to do media recovery.

During the startup process, ORACLE first checks whether media recovery is needed, and then checks whether instance recovery.

If the database shuts down normally, a checkpoint will be triggered and the END SCN number of the data file will be set to the Start SCN number of the corresponding data file. When the database starts, it is found that they are consistent, so there is no need to do instance recovery. After the database starts normally, ORACLE sets the END SCN number to NULL.

If the database is shut down abnormally, the END SCN number will be NULL. You need to do instance recovery.

1.3.1.3 Why do you need System checkpoint SCN number and Datafile Checkpoint SCN number

Why does ORACLE record the System checkpoint SCN number in the control file as well as the Datafile Checkpoint SCN number for each data file?

There are two reasons:

1. For read-only tablespaces, the data files have the same Datafile Checkpoint SCN, Start SCN, and END SCN numbers.

All three SCN will be frozen while the tablespace is read-only.

two。 If the control file is not the current control file, the System checkpoint will be less than the Start SCN or END SCN number.

By recording these SCN numbers, you can tell whether the control file is the current control file.

1.3.1.4 recover database using backup controlfile

When a Start SCN number exceeds the System Checkpoint SCN number, the control file is not the current control file, so you need to use using backup controlfile when doing recover. This is one of the reasons why you need to record SystemCheckpoint SCN.

It is worth mentioning here that when rebuilding the control file, the data of the System Checkpoint SCN 0dDatafile Checkpoint SCN comes from Start SCN. According to the above description, it is necessary to use using backup controlfile to do recovery.

1.3.2 modify the file header to promote SCN, skip archiving to achieve full recovery

The data file in a tablespace is corrupted and recovered using a previous backup, but the required archive files cannot be provided, and the tablespace stores historical data and rarely changes. However, due to other reasons (regularly storing new data into the tablespace), it cannot become read-only mode.

In this case, due to the lack of archiving, the database cannot recovery, but the data related to the tablespace has changed little or no. In this case, we can change the check point number of the header of the data file to let oracle avoid checking the file, achieve full recovery, and open the database in time.

1. Abnormal power outage of the database, resulting in corruption of the data file in the users tablespace

2. Dump the data file from the early backup to the target location

3. Recovery the database. If the archive log is damaged, the tablespace is not modified, but it does not become read only, and the database cannot be fully recovery, so it cannot be opened:

SQL > alter database open

Alter database open * ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4:'/ u01 * app *

SQL > recover database

ORA-00279: change 1951719 generated at 08/25/2012 13:52:08 needed for thread 1 ORA-00289: suggestion:

/ home/oracle/oracle/product/10.2.0/db_1/dbs/arch2_107_783745676.dbf

ORA-00280: change 1951719 for thread 1 is in sequence # 107

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log

'/ home/oracle/oracle/product/10.2.0/db_1/dbs/arch2_107_783745676.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

4. Since the table space has not been modified, no information about the change of the table space should be recorded in the archive log, but oracle does not know it, so it must be archived before it can carry out recovery. At this time, our solution is to change the check point number of the data file header of the table space to the same as that recorded in the control file, then oracle will not do media recovery, but only instance recovery. Then recovery can be successful, which can be achieved using bbed.

1.3.3 what if the file is in windows or ASM

Copy the database file to the FS of linux or aix, and return to the file location of the database in copy after the BBED modification is completed.

1.3.4 RBA (Redo Block Address)

RBA is the address of redo entries in the redo log file.

A "Redo Block Address" (RBA) describes a physical location within aredo log file.

RBA consists of the following three parts:

(1) the log file sequence number (4 bytes)

(2) the log file block number (4 bytes)

(3) the byte offset into the block at which the redo record starts (2bytes)

For example, RBA [0x19.2.10] stands for Log squence25, Block number 2 with byte offset 16.

Note the format here: all 16.

Redo is closely related to checkpoint. When the log_checkpoints_to_alert parameter is set to true, checkpoint will be written to alert log when it occurs.

-Chapter II Experiment part (1) 2.1 introduction of the experimental environment

Project

Db Typ

Single instance

Db version

11.2.0.3.0

Db storage

Host IP address / hosts configuration

192.168.59.129

OS version and kernel version

Linux rhel5 2.6.18-194.el5 64 bit

Archiving mode

Archive Mode

ORACLE_SID

Ora11g

2.2 Experimental objectives

First of all, we delete the system file and all the archived files at the OS level, and then use the previous backup to restore the system file. Finally, we use BBED to modify the file header and promote the SCN number to open the database. Another experiment is to use the implicit parameter _ allow_resetlogs_corruption to open the database. To open the database normally means that the experiment is successful. With regard to whether data is lost or not, one more recovery means is always a good thing.

2.3 Experimental process 2.3.1 Simulation system data file is lost and archive is deleted

First, rman backs up the system data file No. 1:

[oracle@rhel5:/home/oracle] # rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Thu Apr 7 17:00:17 2016

Connected to target database: ORA11G (DBID=8302811)

RMAN > list backupset

Using target database control file instead of recovery catalog

Specification does not match any backup in the repository

RMAN > list copy

Specification does not match any datafile copy in the repository

Specification does not match any control file copy in the repository

Specification does not match any archived log in the repository

RMAN > backup datafile 1

Starting backup at 2016-04-07 17:00:35

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=133 device type=DISK

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf

Channel ORA_DISK_1: starting piece 1 at 2016-04-07 17:00:36

Channel ORA_DISK_1: finished piece 1 at 2016-04-07 17:03:24

Piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp tag=TAG20160407T170035 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:02:49

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Including current control file in backup set

Including current SPFILE in backup set

Channel ORA_DISK_1: starting piece 1 at 2016-04-07 17:03:26

Channel ORA_DISK_1: finished piece 1 at 2016-04-07 17:03:29

Piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_ncsnf_TAG20160407T170035_cjd8lygf_.bkp tag=TAG20160407T170035 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 2016-04-07 17:03:29

RMAN >

Delete data file No. 1 and delete the archive, restart the error report:

[oracle@rhel5:/home/oracle] # sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:05:26 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > drop table T_LHR_20160407_02

Drop table T_LHR_20160407_02

ERROR at line 1:

ORA-00942: table or view does not exist

SYS@ora11g > create table T_LHR_20160407_02 as select * from all_objects

Table created.

SYS@ora11g > update T_LHR_20160407_02 set object_id=100

72458 rows updated.

SYS@ora11g > commit

Commit complete.

SYS@ora11g > alter database datafile 1 offline

Alter database datafile 1 offline

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

"SYSTEM tablespaces cannot be offline. Ordinary files can. We can delete them at the os level."

SYS@ora11g > select count (1) from T_LHR_20160407_02

COUNT (1)

72458

SYS@ora11g > col name format A60

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2217678

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2218426 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2217678 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2217678 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2217678 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2217678 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2217678 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2217678 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf NO 2218426 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2217678 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2217678 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf NO 2217678 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2217678 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2217678 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2217678 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

No rows selected

SYS@ora11g > alter system switch logfile

System altered.

SYS@ora11g >

System altered.

SYS@ora11g >

System altered.

SYS@ora11g > col name format A60

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2220979

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2220979 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2220979 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2220979 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2220979 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2220979 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2220979 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2220979 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf NO 2220979 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2220979 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2220979 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf NO 2220979 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2220979 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2220979 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2220979 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

No rows selected

After switching logs, we can see that the scn numbers are all the same (all 2220979). Let's delete file 1 below.

SYS@ora11g >! Rm / u01/app/oracle/oradata/ora11g/system01.dbf

SYS@ora11g > alter system switch logfile

System altered.

SYS@ora11g > /

System altered.

SYS@ora11g > /

= "in the process of switching logs, there is a certain chance that the database down will be dropped. If not, shutdown abort will be dropped, and then restart.

SYS@ora11g > shutdown abort

ORACLE instance shut down.

SYS@ora11g > startup

ORACLE instance started.

Total System Global Area 513585152 bytes

Fixed Size 2229864 bytes

Variable Size 390072728 bytes

Database Buffers 113246208 bytes

Redo Buffers 8036352 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1:'/ u01 *

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 0

SYS@ora11g >

SYS@ora11g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

SYS@ora11g >

SYS@ora11g > show parameter recovery

NAME TYPE VALUE

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_

Area

Db_recovery_file_dest_size big integer 4122M

Recovery_parallelism integer 0

You can see that the No.1 data file cannot be found, and then we delete the archive to make sure that it cannot be recovered through recover

[oracle@rhel5:/home/oracle] # cd / u01/app/oracle/fast_recovery_area/ORA11G/archivelog

[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog] # ll

Total 4

Drwxr-x--- 2 oracle asmadmin 4096 Apr 7 17:09 2016_04_07

[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog] # rm-rf *

[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog] # cd

[oracle@rhel5:/home/oracle] #

Next, we restore the No. 1 data file through rman. Because the archive is missing, we cannot recover. If it is not a system,undo data file, we can offline it, and then we can open the database. But system must online to open the database, so the database cannot be opened at present:

[oracle@rhel5:/home/oracle] # rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Thu Apr 7 17:10:32 2016

Connected to target database: ORA11G (DBID=8302811, not open)

RMAN > restore datafile 1

Starting restore at 2016-04-07 17:10:40

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=129 device type=DISK

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/ora11g/system01.dbf

Channel ORA_DISK_1: reading from backup piece / u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp

Channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp tag=TAG20160407T170035

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:01:55

Finished restore at 2016-04-07 17:12:36

RMAN > recover datafile 1

Starting recover at 2016-04-07 17:14:48

Using channel ORA_DISK_1

Starting media recovery

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 04/07/2016 17:14:49

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 11 and starting SCN of 2221005 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 10 and starting SCN of 2221002 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 2220999 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 8 and starting SCN of 2220996 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 7 and starting SCN of 2220979 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 6 and starting SCN of 2220975 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 2220972 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 4 and starting SCN of 2217677 found to restore

RMAN > recover datafile 1 until sequence 3 thread 1

Starting recover at 2016-04-07 17:20:09

Using channel ORA_DISK_1

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 04/07/2016 17:20:09

RMAN-06556: datafile 1 must be restored from backup older than SCN 2197424

= "you can see that the recover operation cannot be performed because the archive is lost, and the incomplete recovery of rman cannot be completed. Try the following through the incomplete recovery of SQL.

[oracle@rhel5:/home/oracle] # sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:20:49 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12

SYS@ora11g > alter database open

Alter database open

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:'/ u01 *

SYS@ora11g > recover database using backup controlfile until cancel

ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2218426 for thread 1 is in sequence # 4

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2:'/ u01 *

SYS@ora11g > alter database open

Alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ora11g > alter database open ressetlogs

Alter database open ressetlogs

ERROR at line 1:

ORA-02288: invalid OPEN mode

SYS@ora11g > alter database open resetlogs

Alter database open resetlogs

ERROR at line 1:

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2:'/ u01 *

SYS@ora11g > col name format A60

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2221005

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2221005 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2218426 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 2218426

SYS@ora11g >

You can see that the Ckp SCN of file No. 1 is different from that of other files, so the database cannot be opened. The following describes how to restore the open database based on the implicit parameter _ allow_resetlogs_corruption and BBED modification headers.

2.3.1.1 method 1: implicit parameter _ allow_resetlogs_corruption

[oracle@rhel5:/home/oracle] # sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:20:49 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12

SYS@ora11g > alter database open

Alter database open

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:'/ u01 *

SYS@ora11g > recover database using backup controlfile until cancel

ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2218426 for thread 1 is in sequence # 4

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2:'/ u01 *

SYS@ora11g > alter database open

Alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ora11g > alter database open resetlogs

Alter database open resetlogs

ERROR at line 1:

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2:'/ u01 *

SYS@ora11g > col name format A60

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2221005

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2221005 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2218426 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 2218426

SYS@ora11g >

SYS@ora11g > alter system set "_ allow_resetlogs_corruption" = true scope=spfile

System altered.

SYS@ora11g > recover database using backup controlfile until cancel

ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2218426 for thread 1 is in sequence # 4

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2:'/ u01 *

SYS@ora11g > alter database open

Alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ora11g >

SYS@ora11g > alter database open resetlogs

Alter database open resetlogs

ERROR at line 1:

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2:'/ u01 *

The database cannot be opened without complete recovery, so implicit parameters can only be used.

SYS@ora11g >

SYS@ora11g > startup force

ORACLE instance started.

Total System Global Area 513585152 bytes

Fixed Size 2229864 bytes

Variable Size 390072728 bytes

Database Buffers 113246208 bytes

Redo Buffers 8036352 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ora11g > alter database open resetlogs

Alter database open resetlogs

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Process ID: 15341

Session ID: 191 Serial number: 3

SYS@ora11g > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel5:/home/oracle] # sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:28:54 2016

Connected to an idle instance.

SYS@ora11g > startup mount

ORACLE instance started.

Total System Global Area 513585152 bytes

Fixed Size 2229864 bytes

Variable Size 390072728 bytes

Database Buffers 113246208 bytes

Redo Buffers 8036352 bytes

Database mounted.

SYS@ora11g > alter database open

Database altered.

SYS@ora11g > col name format A60

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2238438

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2238438 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2238438 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2238438 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2238438 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2238438 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2238438 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2238438 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf NO 2238438 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2238438 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2238438 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf NO 2238438 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2238438 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2238438 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2238438 ONLINE

7 rows selected.

The ckp scn is consistent and the database is open. Next, we delete the parameter _ allow_resetlogs_corruption from spfile, and then back up the database. We can delete this parameter in our test.

SYS@ora11g > alter system set "_ allow_resetlogs_corruption" = false scope=spfile

System altered.

SYS@ora11g > alter system reset "_ allow_resetlogs_corruption" scope=spfile sid='*'

System altered.

SYS@ora11g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ora11g > startup

ORACLE instance started.

Total System Global Area 513585152 bytes

Fixed Size 2229864 bytes

Variable Size 390072728 bytes

Database Buffers 113246208 bytes

Redo Buffers 8036352 bytes

Database mounted.

Database opened.

SYS@ora11g > show parameter _ allow_resetlogs_corruption

SYS@ora11g >

= "implied parameters have been cleared from spfile, so you can back up the database."

Alarm log:

ALTER DATABASE OPEN

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORA-1589 signalled during: ALTER DATABASE OPEN...

Alter database open resetlogs

RESETLOGS is being done without consistancy checks. This may result

In a corrupted database. The database should be recreated.

RESETLOGS after incomplete recovery UNTIL CHANGE 2218426

Archived Log entry 46 added for thread 1 sequence 10 ID 0x94f151 dest 1:

Thu Apr 07 17:28:15 2016

Archived Log entry 47 added for thread 1 sequence 11 ID 0x94f151 dest 1:

Archived Log entry 48 added for thread 1 sequence 12 ID 0x94f151 dest 1:

Clearing online redo logfile 1/ u01/app/oracle/oradata/ora11g/redo01.log

Clearing online log 1 of thread 1 sequence number 10

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 / u01/app/oracle/oradata/ora11g/redo02.log

Clearing online log 2 of thread 1 sequence number 11

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 / u01/app/oracle/oradata/ora11g/redo03.log

Clearing online log 3 of thread 1 sequence number 12

Clearing online redo logfile 3 complete

Resetting resetlogs activation ID 9761105 (0x94f151)

Online log / u01/app/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared

Online log / u01/app/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared

Online log / u01/app/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared

Thu Apr 07 17:28:25 2016

Setting recovery target incarnation to 4

Thu Apr 07 17:28:25 2016

Assigning activation ID 9776087 (0x952bd7)

LGWR: STARTING ARCH PROCESSES

Thu Apr 07 17:28:25 2016

ARC0 started with pid=21, OS id=15440

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thu Apr 07 17:28:26 2016

ARC1 started with pid=22, OS id=15447

Thu Apr 07 17:28:26 2016

ARC2 started with pid=23, OS id=15450

Thread 1 opened at log sequence 1

Current log# 1 seq# 1 mem# 0: / u01/app/oracle/oradata/ora11g/redo01.log

Successful open of redo thread 1

Thu Apr 07 17:28:27 2016

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Apr 07 17:28:27 2016

SMON: enabling cache recovery

Thu Apr 07 17:28:27 2016

ARC3 started with pid=24, OS id=15452

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the'no FAL' ARCH

ARC1: Becoming the'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39761):

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Incident details in: / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39761/ora11g_ora_15341_i39761.trc

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39761/ora11g_ora_15341_i39761.trc:

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareaORA11GOnArchivelogAccording to 2016404, 07qo1mf110cjd8vcy1.arc'.

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Thu Apr 07 17:28:35 2016

Dumping diagnostic data in directory= [CDMP _ 20160407172835], requested by (instance=1, osid=15341), summary= [incident=39761].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Error 600 happened during db open, shutting down database

USER (ospid: 15341): terminating the instance due to error

Thu Apr 07 17:28:35 2016

ORA-1092: opitsk aborting process

Thu Apr 07 17:28:35 2016

ORA-1092: opitsk aborting process

Instance terminated by USER, pid = 15341

ORA-1092 signalled during: alter database open resetlogs...

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39762):

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Incident details in: / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39762/ora11g_ora_15341_i39762.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39763):

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Incident details in: / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39763/ora11g_ora_15341_i39763.trc

Thu Apr 07 17:28:37 2016

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-27300: OS system dependent operation:semctl failed with status: 22

ORA-27301: OS failure message: Invalid argument

ORA-27302: failure occurred at: sskgpwpost1

ORA-27303: additional information: semid = 4292611

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39764):

ORA-00600: internal error code, arguments: [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Incident details in: / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39764/ora11g_ora_15341_i39764.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Thu Apr 07 17:28:39 2016

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=40809):

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Incident details in: / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_40809/ora11g_ora_15341_i40809.trc

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_40809/ora11g_ora_15341_i40809.trc:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], []

Thu Apr 07 17:29:01 2016

Adjusting the default value of parameter parallel_max_servers

From 160 to 135 due to the value of parameter processes

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT = 27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = / u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: rhel5

Release: 2.6.18-194.el5

Version: # 1 SMP Tue Mar 16 21:52:39 EDT 2010

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile / u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11g.ora

System parameters with non-default values:

Processes = 150

Memory_target = 492m

Control_files = "/ u01/app/oracle/oradata/ora11g/control01.ctl"

Control_files = "/ u01/app/oracle/fast_recovery_area/ora11g/control02.ctl"

Db_block_size = 8192

Compatible = "11.2.0.0.0"

Db_recovery_file_dest = "/ u01/app/oracle/fast_recovery_area"

Db_recovery_file_dest_size= 4122M

_ allow_resetlogs_corruption= TRUE

Undo_tablespace = "UNDOTBS1"

Remote_login_passwordfile= "EXCLUSIVE"

Db_domain = ""

Dispatchers = "(PROTOCOL=TCP) (SERVICE=ora11gXDB)"

Audit_file_dest = "/ u01/app/oracle/admin/ora11g/adump"

Audit_trail = "DB"

Db_name = "ora11g"

Open_cursors = 300

Diagnostic_dest = "/ u01/app/oracle"

Thu Apr 07 17:29:01 2016

PMON started with pid=2, OS id=15634

Thu Apr 07 17:29:01 2016

PSP0 started with pid=3, OS id=15636

Thu Apr 07 17:29:02 2016

VKTM started with pid=4, OS id=15641 at elevated priority

VKTM running at (1) millisec precision with DBRM quantum (100) ms

Thu Apr 07 17:29:02 2016

GEN0 started with pid=5, OS id=15645

Thu Apr 07 17:29:02 2016

DIAG started with pid=6, OS id=15647

Thu Apr 07 17:29:02 2016

DBRM started with pid=7, OS id=15649

Thu Apr 07 17:29:02 2016

DIA0 started with pid=8, OS id=15651

Thu Apr 07 17:29:02 2016

MMAN started with pid=9, OS id=15655

Thu Apr 07 17:29:02 2016

DBW0 started with pid=10, OS id=15657

Thu Apr 07 17:29:02 2016

LGWR started with pid=11, OS id=15660

Thu Apr 07 17:29:02 2016

CKPT started with pid=12, OS id=15662

Thu Apr 07 17:29:02 2016

SMON started with pid=13, OS id=15664

Thu Apr 07 17:29:02 2016

RECO started with pid=14, OS id=15666

Thu Apr 07 17:29:02 2016

MMON started with pid=15, OS id=15668

Thu Apr 07 17:29:02 2016

MMNL started with pid=16, OS id=15670

Starting up 1 dispatcher (s) for network address'(ADDRESS= (PARTIAL=YES) (PROTOCOL=TCP)) '...

Starting up 1 shared server (s)...

ORACLE_BASE from environment = / u01/app/oracle

Thu Apr 07 17:29:03 2016

ALTER DATABASE MOUNT

Thu Apr 07 17:29:06 2016

Sweep [inc] [40809]: completed

Sweep [inc] [39764]: completed

Sweep [inc] [39763]: completed

Sweep [inc] [39762]: completed

Sweep [inc] [39761]: completed

Sweep [inc2] [40809]: completed

Sweep [inc2] [39764]: completed

Sweep [inc2] [39763]: completed

Sweep [inc2] [39762]: completed

Sweep [inc2] [39761]: completed

Successful mount of redo thread 1, with mount id 9775647

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Thu Apr 07 17:29:11 2016

Alter database open

Beginning crash recovery of 1 threads

Parallel recovery started with 3 processes

Started redo scan

Completed redo scan

Read 1 KB redo, 3 data blocks need recovery

Started redo application at

Thread 1: logseq 1, block 3, scn 2218432

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0

Mem# 0: / u01/app/oracle/oradata/ora11g/redo01.log

Completed redo application of 0.00MB

Completed crash recovery at

Thread 1: logseq 1, block 5, scn 2238435

3 data blocks read, 3 data blocks written, 1 redo k-bytes read

LGWR: STARTING ARCH PROCESSES

Thu Apr 07 17:29:11 2016

ARC0 started with pid=24, OS id=15751

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thu Apr 07 17:29:12 2016

ARC1 started with pid=25, OS id=15756

Thread 1 advanced to log sequence 2 (thread open)

Thu Apr 07 17:29:12 2016

ARC2 started with pid=26, OS id=15758

Thread 1 opened at log sequence 2

Current log# 2 seq# 2 mem# 0: / u01/app/oracle/oradata/ora11g/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Thu Apr 07 17:29:12 2016

ARC3 started with pid=27, OS id=15760

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the'no FAL' ARCH

ARC1: Becoming the'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Archived Log entry 49 added for thread 1 sequence 1 ID 0x952bd7 dest 1:

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

[15725] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:85654704 end:85655584 diff:880 (8 seconds)

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

Thu Apr 07 17:29:13 2016

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

Replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Thu Apr 07 17:29:16 2016

QMNC started with pid=28, OS id=15774

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open

Thu Apr 07 17:29:24 2016

Db_recovery_file_dest_size of 4122 MB is 18.36% used. This is a

User-specified limit on the amount of space that will be used by this

Database for recovery-related files, and does not reflect the amount of

Space available in the underlying filesystem or ASM diskgroup.

Thu Apr 07 17:29:24 2016

Starting background process CJQ0

Thu Apr 07 17:29:24 2016

CJQ0 started with pid=31, OS id=15820

Skip the consistency check of the database through _ allow_resetlogs_corruption and successfully open the database, but there are some cases in which the database cannot be opened even if this parameter is added. At this time, we have to rely on our BBED. Remember to back up the database immediately after resetlogs.

2.3.1.2 method 2: bbed recovery-modify the file header to promote SCN, skip archiving to achieve full recovery

First check the scn number of the system file header:

[oracle@rhel5:/home/oracle] # sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:41:50 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > col name format A60

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2239089

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2218426 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf NO 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2239089 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 2218426

SYS@ora11g >

SYS@ora11g > select to_char (2239089), to_char (2218426) FROM DUAL

TO_CHAR (2239 TO_CHAR (2218

222a71 21d9ba = "convert decimal to hexadecimal

SYS@ora11g >

The current ckp scn is 2218426 (hexadecimal: 21d9ba). You need to modify ckp scn to 2239089 (hexadecimal: 222a71) to start the database. Below, we modify it through bbed:

BBED compiles and prepares the code. For the compilation process, refer to:

Make-f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed

Vi / home/oracle/file.txt

Set line 9999 pagesize 9999

Vi / home/oracle/bbed.par

Blocksize=8192

Listfile=/home/oracle/file.txt

Mode=edit

Bbed parfile=/home/oracle/bbed.par

[oracle@rhel5:/home/oracle] # ll

Total 51328

-rwxr-xr-x 1 oracle dba 56 Apr 7 09:38 bbed.par

-rwxr-xr-x 1 oracle dba 27648 Apr 7 16:13 bifile.bbd

Drwxr-xr-x 2 oracle dba 4096 Apr 7 09:38 Desktop

Drwxr-xr-x 2 oracle dba 4096 Apr 7 09:38 dul

-rwxr-xr-x 1 oracle dba 393Apr 7 17:46 file.txt

Drwxr-xr-x 8 oracle dba 4096 Apr 7 09:38 gdul

-rwxr-xr-x 1 oracle dba 7354 Apr 7 16:31 log.bbd

-rwxr-xr-x 1 oracle dba 52436992 Apr 7 09:38 lxtbs01.dbf

Drwxr-xr-x 3 oracle oinstall 4096 Apr 7 10:41 oradiag_oracle

Drwxr-xr-x 2 oracle dba 4096 Apr 7 09:38 rman_bak

[oracle@rhel5:/home/oracle] # cat bbed.par

Blocksize=8192

Listfile=/home/oracle/file.txt

Mode=edit

[oracle@rhel5:/home/oracle] # cat file.txt

1/ u01/app/oracle/oradata/ora11g/system01.dbf 754974720

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 765460480

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 73400320

4 / u01/app/oracle/oradata/ora11g/users01.dbf 11796480

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 10485760

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 20971520

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 52428800

[oracle@rhel5:/home/oracle] # bbed parfile=bbed.par

Password:

BBED: Release 2.0.0.0.0-Limited Production on Thu Apr 7 17:46:53 2016

*! For Oracle Internal Use only!! *

BBED > info

File# Name Size (blks)

1/ u01/app/oracle/oradata/ora11g/system01.dbf 92160

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 93440

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 8960

4 / u01/app/oracle/oradata/ora11g/users01.dbf 1440

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 1280

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2560

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 6400

BBED > set dba 1

DBA 0x00400001 (4194305 1)

BBED > show

FILE# 1

BLOCK# 1

OFFSET 0

DBA 0x00400001 (4194305 1)

FILENAME / u01/app/oracle/oradata/ora11g/system01.dbf

BIFILE bifile.bbd

LISTFILE / home/oracle/file.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

BBED >

BBED > p kcvfhckp

Struct kcvfhckp, 36 bytes @ 484,

Struct kcvcpscn, 8 bytes @ 484

Ub4 kscnbas @ 4840x0021d9ba

Ub2 kscnwrp @ 488 0x0000

Ub4 kcvcptim @ 4920x36277bb4

Ub2 kcvcpthr @ 4960x0001

Union u, 12 bytes @ 500

Struct kcvcprba, 12 bytes @ 500

Ub4 kcrbaseq @ 500 0x00000004

Ub4 kcrbabno @ 504 0x0000099d

Ub2 kcrbabof @ 508 0x0010

Ub1 kcvcpetb [0] @ 512 0x02

Ub1 kcvcpetb [1] @ 513 0x00

Ub1 kcvcpetb [2] @ 514 0x00

Ub1 kcvcpetb [3] @ 515 0x00

Ub1 kcvcpetb [4] @ 516 0x00

Ub1 kcvcpetb [5] @ 517 0x00

Ub1 kcvcpetb [6] @ 518 0x00

Ub1 kcvcpetb [7] @ 519 0x00

= "here we see that the value of 0x0021d9ba is the ckp scn number of the header of the No. 1 data file we just checked. Let's dump the header block to see how it is stored:

BBED > d / v dba 1 offset 484 count 64

File: / u01/app/oracle/oradata/ora11g/system01.dbf (1)

Block: 1 Offsets: 484 to 547 Dba:0x00400001

Bad92100 00000000 b47b2736 01000000 l.

04000000 9d090000 1000e080 02000000 l.

00000000 00000000 00000000 l.

It is very strange that what we see is bad92100, instead of the two stores we expected. Our experimental environment is linux, which belongs to Little byte order, and the order of these two values under aix is the same. This is especially important to note that when we modify it through modify, we must also invert it, that is, the original 2239089 (hexadecimal: 222a71). The time of modification is: 712a71, and there are 2 zeros that must be omitted as follows.

BBED > modify / x 712a22 dba 1 offset 484

Warning: contents of previous BIFILE will be lost. Proceed? (Ybig N) y

File: / u01/app/oracle/oradata/ora11g/system01.dbf (1)

Block: 1 Offsets: 484 to 547 Dba:0x00400001

712a2200 00000000 b47b2736 01000000 04000000 9d090000 1000e080 02000000

00000000 00000000 00000000

BBED > d / v dba 1 offset 484 count 64

File: / u01/app/oracle/oradata/ora11g/system01.dbf (1)

Block: 1 Offsets: 484 to 547 Dba:0x00400001

712a2200 00000000 b47b2736 01000000 l q* ".

04000000 9d090000 1000e080 02000000 l.

00000000 00000000 00000000 l.

= "you can see that it has been modified

BBED > p kcvfhckp

Struct kcvfhckp, 36 bytes @ 484,

Struct kcvcpscn, 8 bytes @ 484

Ub4 kscnbas @ 4840x00222a71

Ub2 kscnwrp @ 488 0x0000

Ub4 kcvcptim @ 4920x36277bb4

Ub2 kcvcpthr @ 4960x0001

Union u, 12 bytes @ 500

Struct kcvcprba, 12 bytes @ 500

Ub4 kcrbaseq @ 500 0x00000004

Ub4 kcrbabno @ 504 0x0000099d

Ub2 kcrbabof @ 508 0x0010

Ub1 kcvcpetb [0] @ 512 0x02

Ub1 kcvcpetb [1] @ 513 0x00

Ub1 kcvcpetb [2] @ 514 0x00

Ub1 kcvcpetb [3] @ 515 0x00

Ub1 kcvcpetb [4] @ 516 0x00

Ub1 kcvcpetb [5] @ 517 0x00

Ub1 kcvcpetb [6] @ 518 0x00

Ub1 kcvcpetb [7] @ 519 0x00

BBED > sum apply = "apply changes to bbed

Check value for File 1, Block 1:

Current = 0x2a6c, required = 0x2a6c

BBED >

Check the ckp scn number of the data file, and it is consistent:

SYS@ora11g > col name format A50

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2239089

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf NO 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2239089 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 2239089

SYS@ora11g > recover datafile 1

Media recovery complete.

SYS@ora11g > select file#,online_status,change# from v$recover_file

No rows selected

SYS@ora11g > alter database open

Database altered.

= "the database is opened normally without using resetlogs mode, o (∩ _ ∩) o

In general, the database can be opened normally, and the next thing to do is to back up the database, or expdp to reorganize the database. It is worth noting that I first use implicit parameters to restore, do resetlogs operation, and then use bbed to restore. As a result, when the recover data file is said to be backed up, it is said that the backup belongs to an orphan incarnation. In fact, the reset inside the rman is fine, and the following is also an episode. Simply record it:

SYS@ora11g > recover datafile 1

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1:'/ u01 *

SYS@ora11g > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Thu Apr 07 17:51:50 2016

Alarm log shows that it belongs to incarnation#=3:

ALTER DATABASE RECOVER datafile 1

Media Recovery Start

Serial Media Recovery started

Datafile 1 (ckpscn 2239089) is orphaned on incarnation#=3

Media Recovery failed with error 19909

ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1...

The following uses rman to reset database:

[oracle@rhel5:/home/oracle] # rman target /

Recovery Manager: Release 11.2.0.3.0-Production on Thu Apr 7 17:52:47 2016

Connected to target database: ORA11G (DBID=8302811, not open)

RMAN > list incarnation of database

Using target database control file instead of recovery catalog

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

11 ORA11G 8302811 PARENT 1 2011-09-17 09:46:04

2 2 ORA11G 8302811 PARENT 995548 2016-03-21 11:53:04

3 3 ORA11G 8302811 PARENT 2157406 2016-04-07 16:44:29

4 4 ORA11G 8302811 CURRENT 2218427 2016-04-07 17:28:05

RMAN > list backupset

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

8 Full 628.38M DISK 00:02:46 2016-04-07 17:03:22

BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20160407T170035

Piece Name: / u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp

List of Datafiles in backup set 8

File LV Type Ckp SCN Ckp Time Name

1 Full 2218426 2016-04-07 17:00:36 / u01/app/oracle/oradata/ora11g/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

9 Full 9.64m DISK 00:00:02 2016-04-07 17:03:27

BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20160407T170035

Piece Name: / u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_ncsnf_TAG20160407T170035_cjd8lygf_.bkp

SPFILE Included: Modification time: 2016-04-07 16:49:24

SPFILE db_unique_name: ORA11G

Control File Included: Ckp SCN: 2218482 Ckp time: 2016-04-07 17:03:24

RMAN > reset database to incarnation 3

Database reset to incarnation 3

RMAN > list incarnation of database

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

11 ORA11G 8302811 PARENT 1 2011-09-17 09:46:04

2 2 ORA11G 8302811 PARENT 995548 2016-03-21 11:53:04

3 3 ORA11G 8302811 CURRENT 2157406 2016-04-07 16:44:29

4 4 ORA11G 8302811 ORPHAN 2218427 2016-04-07 17:28:05

RMAN > exit

Recovery Manager complete.

[oracle@rhel5:/home/oracle] # sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:54:33 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ora11g > col name format A50

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2239089

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf NO 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2239089 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 2239089

SYS@ora11g >

SYS@ora11g > recover datafile 1

ORA-00279: change 2239089 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2239089 for thread 1 is in sequence # 4

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log'/ u01 ActionActionoracleActionfastfastened recoveryCharacareAera ORA11GOnArchivelogAccording to 201604G07qo1mfand4cjd8sssxchang.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SYS@ora11g > select file#,online_status,change# from v$recover_file

FILE# ONLINE_ CHANGE#

1 ONLINE 2239089

2 ONLINE 2239089

3 ONLINE 2239089

4 ONLINE 2239089

5 ONLINE 2239089

6 ONLINE 2239089

7 ONLINE 2239089

7 rows selected.

SYS@ora11g > col name format A50

SYS@ora11g > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

2239089

SYS@ora11g > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE

7 rows selected.

SYS@ora11g > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME REC CHECKPOINT_CHANGE# STATUS

1/ u01/app/oracle/oradata/ora11g/system01.dbf 2239089 ONLINE

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE

4 / u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE

5 / u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE

6 / u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE

7 / u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE

7 rows selected.

SYS@ora11g > select file#,online_status,change# from v$recover_file

Recover datafile 1

FILE# ONLINE_ CHANGE#

1 ONLINE 2239089

2 ONLINE 2239089

3 ONLINE 2239089

4 ONLINE 2239089

5 ONLINE 2239089

6 ONLINE 2239089

7 ONLINE 2239089

7 rows selected.

SYS@ora11g > alter database open

Alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

= "it seems that the reset database can only be opened by resetlogs after the database.

SYS@ora11g > alter database open resetlogs

Database altered.

SYS@ora11g >

Alarm log:

Thu Apr 07 17:52:29 2016

Db_recovery_file_dest_size of 4122 MB is 18.36% used. This is a

User-specified limit on the amount of space that will be used by this

Database for recovery-related files, and does not reflect the amount of

Space available in the underlying filesystem or ASM diskgroup.

Thu Apr 07 17:54:07 2016

Setting recovery target incarnation to 3

Thu Apr 07 17:54:58 2016

ALTER DATABASE RECOVER datafile 1

Media Recovery Start

Serial Media Recovery started

ARCH: STARTING ARCH PROCESSES

Thu Apr 07 17:54:59 2016

ARC0 started with pid=21, OS id=20808

ARC0: Archival started

ARCH: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Warning: Recovery target destination is ina sibling branch

Of the controlfile checkpoint. Recovery will only recover

Changes to datafiles.

Thu Apr 07 17:55:00 2016

ARC1 started with pid=22, OS id=20813

Thu Apr 07 17:55:00 2016

ARC2 started with pid=23, OS id=20815

Thu Apr 07 17:55:00 2016

ARC3 started with pid=24, OS id=20817

ARC1: Archival started

ARC2: Archival started

ARC2: Becoming the'no FAL' ARCH

ARC2: Becoming the'no SRL' ARCH

ARC1: Becoming the heartbeat ARCH

ORA-279 signalled during: ALTER DATABASE RECOVER datafile 1...

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Thu Apr 07 17:55:22 2016

ALTER DATABASE RECOVER CONTINUE DEFAULT

Media Recovery Log / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

Errors with log/ u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT...

ALTER DATABASE RECOVER CONTINUE DEFAULT

Media Recovery Log / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

Errors with log/ u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT...

ALTER DATABASE RECOVER CANCEL

Media Recovery Canceled

Completed: ALTER DATABASE RECOVER CANCEL

Thu Apr 07 17:55:46 2016

ALTER DATABASE RECOVER datafile 1

Media Recovery Start

Serial Media Recovery started

Media Recovery failed with error 1610

ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1...

Thu Apr 07 17:56:04 2016

Alter database open

Errors in file / u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_20712.trc:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORA-1589 signalled during: alter database open...

Alter database open resetlogs

RESETLOGS is being done without consistancy checks. This may result

In a corrupted database. The database should be recreated.

RESETLOGS after incomplete recovery UNTIL CHANGE 2239089

Archived Log entry 77 added for thread 1 sequence 28 ID 0x952bd7 dest 1:

Archived Log entry 78 added for thread 1 sequence 29 ID 0x952bd7 dest 1:

Archived Log entry 79 added for thread 1 sequence 27 ID 0x952bd7 dest 1:

Clearing online redo logfile 1/ u01/app/oracle/oradata/ora11g/redo01.log

Clearing online log 1 of thread 1 sequence number 28

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 / u01/app/oracle/oradata/ora11g/redo02.log

Clearing online log 2 of thread 1 sequence number 29

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 / u01/app/oracle/oradata/ora11g/redo03.log

Clearing online log 3 of thread 1 sequence number 27

Thu Apr 07 17:56:16 2016

Clearing online redo logfile 3 complete

Resetting resetlogs activation ID 9776087 (0x952bd7)

Online log / u01/app/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared

Online log / u01/app/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared

Online log / u01/app/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared

Thu Apr 07 17:56:16 2016

Setting recovery target incarnation to 5

Thu Apr 07 17:56:16 2016

Assigning activation ID 9778453 (0x953515)

Thread 1 opened at log sequence 1

Current log# 1 seq# 1 mem# 0: / u01/app/oracle/oradata/ora11g/redo01.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Apr 07 17:56:16 2016

SMON: enabling cache recovery

[20712] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:87282814 end:87284294 diff:1480 (14 seconds)

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

Thu Apr 07 17:56:27 2016

No Resource Manager plan active

Replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Thu Apr 07 17:56:29 2016

QMNC started with pid=25, OS id=21121

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Thu Apr 07 17:56:36 2016

ORA-1 encountered when generating server alert SMG-4120

Thu Apr 07 17:56:40 2016

Completed: alter database open resetlogs

ORA-1 encountered when generating server alert SMG-4121

Thu Apr 07 17:56:47 2016

Starting background process CJQ0

Thu Apr 07 17:56:47 2016

CJQ0 started with pid=29, OS id=21198

Thu Apr 07 17:56:51 2016

Time drift detected. Please check VKTM trace file for more details.

2.4 Summary of the experiment

The recovery of data files in the case of lost archived files can be restored through implicit parameters or BBED modification headers. Whether or not data is lost depends on the situation. For example, ordinary files are dropped by offline and there are no data updates, so we will not lose data after we restore the data file. As for which method is better, I can not generalize. When I test, I may modify resetlogs many times, the single implied parameter or bbed can not successfully open the library, but the combination of the two is finally repaired, so I still have to be flexible.

The motto of wheat seedlings: do more experiments and think more!

Chapter III experiment part (2) III. 1 introduction to the experimental environment

Project

Db Typ

Single instance

Db version

11.2.0.4.0

Db storage

Host IP address / hosts configuration

192.168.59.129

OS version and kernel version

AIX 7.164-bit

Archiving mode

Archive Mode

ORACLE_SID

Oralhr

III. 2 Experimental objectives

First of all, we delete the system file at the OS level, delete one of the archive files, use the previous backup to restore the system file, and finally use BBED to modify the file header to the next log number of the lost log to simulate the loss of as little data as possible.

III. 3 experiment III. 3.1 simulate the loss of system data files and delete one of the archives

First, rman backs up the system data file No. 1:

[ZHLHRDB2:oracle]: / home/oracle > rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Mon Apr 11 14:30:41 2016

Connected to target database: ORALHR (DBID=394957205)

RMAN > list backupset

Specification does not match any backup in the repository

RMAN > list copy

Specification does not match any datafile copy in the repository

Specification does not match any control file copy in the repository

Specification does not match any archived log in the repository

RMAN > list archivelog all

Specification does not match any archived log in the repository

RMAN > backup datafile 1

Starting backup at 2016-04-11 14:31:42

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00001 name=/oracle/app/oracle/oralhr/system01.dbf

Channel ORA_DISK_1: starting piece 1 at 2016-04-11 14:31:42

Channel ORA_DISK_1: finished piece 1 at 2016-04-11 14:31:45

Piece handle=/oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp tag=TAG20160411T143142 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Including current control file in backup set

Including current SPFILE in backup set

Channel ORA_DISK_1: starting piece 1 at 2016-04-11 14:31:46

Channel ORA_DISK_1: finished piece 1 at 2016-04-11 14:31:47

Piece handle=/oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_ncsnf_TAG20160411T143142_cjpk6lwq_.bkp tag=TAG20160411T143142 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2016-04-11 14:31:47

RMAN > list backup of datafile 1

Using target database control file instead of recovery catalog

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

45 Full 657.98M DISK 00:00:03 2016-04-11 14:31:45

BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20160411T143142

Piece Name: / oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp

List of Datafiles in backup set 45

File LV Type Ckp SCN Ckp Time Name

1 Full 1283795 2016-04-11 14:31:42 / oracle/app/oracle/oralhr/system01.dbf

RMAN >

RMAN > exit

Recovery Manager complete.

[ZHLHRDB2:oracle]: / home/oracle >

Create a new table t_lhr_20160411_02, insert one piece of data at a time, and then perform the switch logfile operation every 3 insert operations to ensure that there are 3 insert operation records in each archive file:

[ZHLHRDB2:oracle]: / home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 11 14:36:12 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

And Real Application Testing options

SYS@oralhr > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 44

Next log sequence to archive 46

Current log sequence 46

SYS@oralhr > show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

Db_recovery_file_dest string / oracle/app/oracle

Db_recovery_file_dest_size big integer 4182M

SYS@oralhr > col status for A10

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 46 CURRENT 1282753 2016-04-11 13:55:44

2 44 INACTIVE 1282665 2016-04-11 13:55:01

3 45 INACTIVE 1282745 2016-04-11 13:55:44

SYS@oralhr >

SYS@oralhr > alter system switch logfile

System altered.

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 46 ACTIVE 1282753 2016-04-11 13:55:44

2 47 CURRENT 1283935 2016-04-11 14:37:10

3 45 INACTIVE 1282745 2016-04-11 13:55:44

The current log sequence is 47. On 47, we build the table and insert 3 pieces of data:

SYS@oralhr > create table t_lhr_20160411_02 (id number)

Table created.

SYS@oralhr > insert into t_lhr_20160411_02 values (1)

1 row created.

SYS@oralhr >

SYS@oralhr > insert into t_lhr_20160411_02 values (2)

1 row created.

SYS@oralhr > insert into t_lhr_20160411_02 values (3)

1 row created.

SYS@oralhr > commit

Commit complete.

SYS@oralhr > alter system switch logfile

System altered.

The current log sequence is 48, and on 48, we insert 3 pieces of data:

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 46 ACTIVE 1282753 2016-04-11 13:55:44

2 47 ACTIVE 1283935 2016-04-11 14:37:10

3 48 CURRENT 1284002 2016-04-11 14:39:47

SYS@oralhr > insert into t_lhr_20160411_02 values (4)

1 row created.

SYS@oralhr > insert into t_lhr_20160411_02 values (5)

1 row created.

SYS@oralhr > insert into t_lhr_20160411_02 values (6)

1 row created.

SYS@oralhr > commit

Commit complete.

SYS@oralhr > alter system switch logfile

System altered.

The current log sequence is 49, and we insert 3 pieces of data on 49:

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 49 CURRENT 1284016 2016-04-11 14:40:18

2 47 ACTIVE 1283935 2016-04-11 14:37:10

3 48 ACTIVE 1284002 2016-04-11 14:39:47

SYS@oralhr > insert into t_lhr_20160411_02 values (7)

1 row created.

SYS@oralhr > insert into t_lhr_20160411_02 values (8)

1 row created.

SYS@oralhr > insert into t_lhr_20160411_02 values (9)

1 row created.

SYS@oralhr > commit

Commit complete.

SYS@oralhr > alter system switch logfile

System altered.

For the table-building operation, 3 records were inserted in log 47, 47, 48, and 49, respectively, with a total of 9 records in t_lhr_20160411_02

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 49 ACTIVE 1284016 2016-04-11 14:40:18

2 50 CURRENT 1284034 2016-04-11 14:40:54

3 48 ACTIVE 1284002 2016-04-11 14:39:47

SYS@oralhr > select count (1) from t_lhr_20160411_02

COUNT (1)

nine

SYS@oralhr >

SYS@oralhr > select name from v$datafile

NAME

/ oracle/app/oracle/oralhr/system01.dbf

/ oracle/app/oracle/oralhr/sysaux01.dbf

/ oracle/app/oracle/oralhr/undotbs01.dbf

/ oracle/app/oracle/oralhr/users01.dbf

/ oracle/app/oracle/oralhr/example01.dbf

= "Delete File 1

SYS@oralhr >! Rm / oracle/app/oracle/oralhr/system01.dbf

SYS@oralhr > alter system switch logfile

System altered.

SYS@oralhr >

SYS@oralhr > alter system switch logfile

System altered.

SYS@oralhr > alter system switch logfile

System altered.

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 52 INACTIVE 1284103 2016-04-11 14:43:48

2 53 CURRENT 1284106 2016-04-11 14:43:51

3 51 INACTIVE 1284100 2016-04-11 14:43:45

If log no. 53 is inserted into one record, t_lhr_20160411_02 has a total of 10 records

SYS@oralhr > insert into t_lhr_20160411_02 values (10)

1 row created.

SYS@oralhr > commit

Commit complete.

SYS@oralhr > select count (1) from t_lhr_20160411_02

COUNT (1)

ten

SYS@oralhr > alter system switch logfile

System altered.

SYS@oralhr > alter system flush buffer_cache

System altered.

SYS@oralhr > select count (1) from dba_objects

COUNT (1)

86827

SYS@oralhr > alter system flush buffer_cache

System altered.

SYS@oralhr > alter system switch logfile

System altered.

= "in the process of switching logs, there is a certain chance that the database down will be dropped. If not, shutdown abort will be dropped, and then restart.

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 55 CURRENT 1284157 2016-04-11 14:46:06

2 53 INACTIVE 1284106 2016-04-11 14:43:51

3 54 INACTIVE 1284128 2016-04-11 14:44:51

SYS@oralhr >

SYS@oralhr > shutdown abort

ORACLE instance shut down.

SYS@oralhr > startup

ORACLE instance started.

Total System Global Area 3089920000 bytes

Fixed Size 2250360 bytes

Variable Size 721422728 bytes

Database Buffers 2348810240 bytes

Redo Buffers 17436672 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1:'/ oracle/app/oracle/oralhr/system01.dbf'

SYS@oralhr > col status for A10

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 55 CURRENT 1284157 2016-04-11 14:46:06

3 54 INACTIVE 1284128 2016-04-11 14:44:51

2 53 INACTIVE 1284106 2016-04-11 14:43:51

SYS@oralhr >

SYS@oralhr > col name format A60

SYS@oralhr > col ERROR format A30

SYS@oralhr > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

1284157

SYS@oralhr >

SYS@oralhr > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1 / oracle/app/oracle/oralhr/system01.dbf 1284157 SYSTEM

2 / oracle/app/oracle/oralhr/sysaux01.dbf 1284157 ONLINE

3 / oracle/app/oracle/oralhr/undotbs01.dbf 1284157 ONLINE

4 / oracle/app/oracle/oralhr/users01.dbf 1284157 ONLINE

5 / oracle/app/oracle/oralhr/example01.dbf 1284157 ONLINE

SYS@oralhr >

SYS@oralhr > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS

1 0 ONLINE

2 / oracle/app/oracle/oralhr/sysaux01.dbf NO 1284157 ONLINE

3 / oracle/app/oracle/oralhr/undotbs01.dbf NO 1284157 ONLINE

4 / oracle/app/oracle/oralhr/users01.dbf NO 1284157 ONLINE

5 / oracle/app/oracle/oralhr/example01.dbf NO 1284157 ONLINE

SYS@oralhr >

SYS@oralhr > select file#,online_status,change#,ERROR from v$recover_file

FILE# ONLINE_STATUS CHANGE# ERROR

1 ONLINE 0 FILE NOT FOUND

SYS@oralhr >

You can see that data file No. 1 cannot be found, and then we delete Archive No. 48 to make sure that it cannot be recovered through recover

[ZHLHRDB2:oracle]: / oracle > rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Mon Apr 11 14:55:39 2016

Connected to target database: ORALHR (DBID=394957205, not open)

RMAN > delete archivelog sequence 48

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=97 device type=DISK

List of Archived Log Copies for database with db_unique_name ORALHR

Key Thrd Seq S Low Time

100 1 48 A 2016-04-11 14:39:47

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_48_cjpkplfj_.arc

Do you really want to delete the above objects (enter YES or NO)? YES

Deleted archived log

Archived log file name=/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_48_cjpkplfj_.arc RECID=100 STAMP=908894418

Deleted 1 objects

RMAN >

RMAN > list archivelog all

List of Archived Log Copies for database with db_unique_name ORALHR

Key Thrd Seq S Low Time

98 1 46 A 2016-04-11 13:55:44

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc

99 1 47 A 2016-04-11 14:37:10

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc

101 1 49 A 2016-04-11 14:40:18

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc

102 1 50 A 2016-04-11 14:40:54

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc

103 1 51 A 2016-04-11 14:43:45

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc

104 1 52 A 2016-04-11 14:43:48

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc

105 1 53 A 2016-04-11 14:43:51

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_53_cjpkz30t_.arc

106 1 54 A 2016-04-11 14:44:51

Name: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_54_cjpl1g47_.arc

[ZHLHRDB2:oracle]: / oracle > rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Mon Apr 11 14:58:16 2016

Connected to target database: ORALHR (DBID=394957205, not open)

RMAN > restore datafile 1

Starting restore at 2016-04-11 14:58:24

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=97 device type=DISK

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / oracle/app/oracle/oralhr/system01.dbf

Channel ORA_DISK_1: reading from backup piece / oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp

Channel ORA_DISK_1: piece handle=/oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp tag=TAG20160411T143142

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 2016-04-11 14:58:32

RMAN > recover datafile 1

Starting recover at 2016-04-11 14:58:45

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 46 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc

Archived log for thread 1 with sequence 47 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc

Archived log for thread 1 with sequence 49 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc

Archived log for thread 1 with sequence 50 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc

Archived log for thread 1 with sequence 51 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc

Archived log for thread 1 with sequence 52 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc

Archived log for thread 1 with sequence 53 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_53_cjpkz30t_.arc

Archived log for thread 1 with sequence 54 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_54_cjpl1g47_.arc

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 04/11/2016 14:58:45

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 48 and starting SCN of 1284002 found to restore

Due to the lack of log number 48, we cannot complete the recover operation, so we first apply redo to log number 48.

RMAN > recover datafile 1 until sequence 48

Starting recover at 2016-04-11 14:59:20

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 46 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc

Archived log for thread 1 with sequence 47 is already on disk as file / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc

Archived log file name=/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc thread=1 sequence=46

Archived log file name=/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc thread=1 sequence=47

Media recovery complete, elapsed time: 00:00:00

Finished recover at 2016-04-11 14:59:20

RMAN >

You can see that the recover operation cannot be completed because archive 48 is missing.

III. 3.2 analyze log applications

[ZHLHRDB2:oracle]: / oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 11 15:06:12 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

And Real Application Testing options

SYS@oralhr > col name for A80

SYS@oralhr > set linesize 9999 pagesize 9999

SYS@oralhr > col FIRST_CHANGE# for 9999999999999999

SYS@oralhr > SELECT NAME

2 sequence#

3 a.FIRST_CHANGE#

4 FROM v$archived_log a

5 WHERE resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)

6 AND a.sequence# > = 45

7 ORDER BY a.sequence#

NAME SEQUENCE# FIRST_CHANGE#

45 1282745

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc 46 1282753

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc 47 1283935

48 1284002

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc 49 1284016

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc 50 1284034

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc 51 1284100

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc 52 1284103

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_53_cjpkz30t_.arc 53 1284106

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_54_cjpl1g47_.arc 54 1284128

10 rows selected.

SYS@oralhr >

SYS@oralhr > col status for A10

SYS@oralhr > select GROUP#, SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME

1 55 CURRENT 1284157 2016-04-11 14:46:06

3 54 INACTIVE 1284128 2016-04-11 14:44:51

2 53 INACTIVE 1284106 2016-04-11 14:43:51

SYS@oralhr >

SYS@oralhr > col name format A60

SYS@oralhr > col ERROR format A30

SYS@oralhr > select checkpoint_change# from v$database

CHECKPOINT_CHANGE#

1284157

SYS@oralhr > SELECT a.FILEX, a.NAMEMagna. CHECKPOINTCHANGEPROLER. LASTPOINTCHANGEPROGRAPHY status FROM v$datafile a

FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

1 / oracle/app/oracle/oralhr/system01.dbf 1284157 SYSTEM

2 / oracle/app/oracle/oralhr/sysaux01.dbf 1284157 ONLINE

3 / oracle/app/oracle/oralhr/undotbs01.dbf 1284157 ONLINE

4 / oracle/app/oracle/oralhr/users01.dbf 1284157 ONLINE

5 / oracle/app/oracle/oralhr/example01.dbf 1284157 ONLINE

SYS@oralhr >

SYS@oralhr > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS

1 / oracle/app/oracle/oralhr/system01.dbf YES 1284002 ONLINE

2 / oracle/app/oracle/oralhr/sysaux01.dbf NO 1284157 ONLINE

3 / oracle/app/oracle/oralhr/undotbs01.dbf NO 1284157 ONLINE

4 / oracle/app/oracle/oralhr/users01.dbf NO 1284157 ONLINE

5 / oracle/app/oracle/oralhr/example01.dbf NO 1284157 ONLINE

SYS@oralhr > select file#,online_status,change#,ERROR from v$recover_file

FILE# ONLINE_STATUS CHANGE# ERROR

1 ONLINE 1284002

SYS@oralhr >

= "convert to hexadecimal

SYS@oralhr > select to_char (1284002) cur_scn,to_char (1284016) target_scn,to_char (49) target_seq from dual

CUR_SCN TARGET_SCN TARGET_SEQ

1397a2 1397b0 31

SYS@oralhr >

You can see that the log is applied to the beginning of log 48, then use bbed to skip log 48 and recover from the beginning of 49

= "We can only apply redo to log No. 48 first, then skip No. 48 through BBED, and continue to restore logs in application 49 and beyond:

III. 3.3 bbed modify file header

[ZHLHRDB2:oracle]: / home/oracle > l

Total 112

-rw-r--r-- 1 oracle dba 57 Apr 05 17:01 bbed.par

-rw-r--r-- 1 oracle dba 17408 Apr 11 11:38 bifile.bbd

-rw-r--r-- 1 oracle asmadmin 5715 Apr 06 15:34 ctl.sql

-rw-r--r-- 1 oracle dba 253 Apr 05 17:00 file.txt

-rw-r--r-- 1 oracle dba 10536 Apr 11 15:02 log.bbd

Drwxr-xr-x 3 oracle dba 256 Apr 08 11:02 oracle_bk

Drwxr-xr-x 4 oracle dba 256 Apr 05 16:54 rman_bak

-rw-r--r-- 1 oracle dba 757 Apr 11 10:02 rman_bk_db_archive_lhr.sh

-rw-r--r-- 1 oracle dba 1023 Apr 08 11:25 rman_bk_db_lhr.sh

[ZHLHRDB2:oracle]: / home/oracle > cat bbed.par

Blocksize=8192

Listfile=/home/oracle/file.txt

Mode=edit

[ZHLHRDB2:oracle]: / home/oracle > cat file.txt

1 / oracle/app/oracle/oralhr/system01.dbf 786432000

2 / oracle/app/oracle/oralhr/sysaux01.dbf 513802240

3 / oracle/app/oracle/oralhr/undotbs01.dbf 94371840

4 / oracle/app/oracle/oralhr/users01.dbf 5242880

5 / oracle/app/oracle/oralhr/example01.dbf 328335360

[ZHLHRDB2:oracle]: / home/oracle > bbed parfile=bbed.par

Password:

BBED: Release 2.0.0.0.0-Limited Production on Mon Apr 11 15:02:46 2016

*! For Oracle Internal Use only!! *

BBED > info

File# Name Size (blks)

1 / oracle/app/oracle/oralhr/system01.dbf 96000

2 / oracle/app/oracle/oralhr/sysaux01.dbf 62720

3 / oracle/app/oracle/oralhr/undotbs01.dbf 11520

4 / oracle/app/oracle/oralhr/users01.dbf 640

5 / oracle/app/oracle/oralhr/example01.dbf 40080

BBED > show

FILE# 1

BLOCK# 1

OFFSET 0

DBA 0x00400001 (4194305 1)

FILENAME / oracle/app/oracle/oralhr/system01.dbf

BIFILE bifile.bbd

LISTFILE / home/oracle/file.txt

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

BBED > p kcvfhckp

Struct kcvfhckp, 160 bytes @ 484

Struct kcvcpscn, 8 bytes @ 484

Ub4 kscnbas @ 4840x001397a2

Ub2 kscnwrp @ 488 0x0000

Ub4 kcvcptim @ 4920x362ca0b3

Ub2 kcvcpthr @ 4960x0001

Union u, 12 bytes @ 500

Struct kcvcprba, 12 bytes @ 500

Ub4 kcrbaseq @ 500 0x00000030

Ub4 kcrbabno @ 504 0x00000002

Ub2 kcrbabof @ 508 0x0000

Ub1 kcvcpetb [0] @ 512 0x02

Ub1 kcvcpetb [1] @ 513 0x00

Ub1 kcvcpetb [2] @ 514 0x00

Ub1 kcvcpetb [3] @ 515 0x00

Ub1 kcvcpetb [4] @ 516 0x00

Ub1 kcvcpetb [5] @ 517 0x00

Ub1 kcvcpetb [6] @ 518 0x00

Ub1 kcvcpetb [7] @ 519 0x00

Ub1 kcvcpetb [8] @ 520 0x00

Ub1 kcvcpetb [9] @ 521 0x00

Ub1 kcvcpetb [10] @ 522 0x00

Ub1 kcvcpetb [11] @ 523 0x00

Ub1 kcvcpetb [12] @ 524 0x00

Ub1 kcvcpetb [13] @ 525 0x00

Ub1 kcvcpetb [14] @ 526 0x00

Ub1 kcvcpetb [15] @ 527 0x00

Ub1 kcvcpetb [16] @ 528 0x00

Ub1 kcvcpetb [17] @ 529 0x00

Ub1 kcvcpetb [18] @ 530 0x00

Ub1 kcvcpetb [19] @ 531 0x00

Ub1 kcvcpetb [20] @ 532 0x00

Ub1 kcvcpetb [21] @ 533 0x00

Ub1 kcvcpetb [22] @ 534 0x00

Ub1 kcvcpetb [23] @ 535 0x00

Ub1 kcvcpetb [24] @ 536 0x00

Ub1 kcvcpetb [25] @ 537 0x00

Ub1 kcvcpetb [26] @ 538 0x00

Ub1 kcvcpetb [27] @ 539 0x00

Ub1 kcvcpetb [28] @ 540 0x00

Ub1 kcvcpetb [29] @ 5410x00

Ub1 kcvcpetb [30] @ 542 0x00

Ub1 kcvcpetb [31] @ 543 0x00

Ub1 kcvcpetb [32] @ 544 0x00

Ub1 kcvcpetb [33] @ 545 0x00

Ub1 kcvcpetb [34] @ 546 0x00

Ub1 kcvcpetb [35] @ 547 0x00

Ub1 kcvcpetb [36] @ 548 0x00

Ub1 kcvcpetb [37] @ 549 0x00

Ub1 kcvcpetb [38] @ 550 0x00

Ub1 kcvcpetb [39] @ 551 0x00

Ub1 kcvcpetb [40] @ 5520x00

Ub1 kcvcpetb [41] @ 553 0x00

Ub1 kcvcpetb [42] @ 554 0x00

Ub1 kcvcpetb [43] @ 555 0x00

Ub1 kcvcpetb [44] @ 556 0x00

Ub1 kcvcpetb [45] @ 557 0x00

Ub1 kcvcpetb [46] @ 558 0x00

Ub1 kcvcpetb [47] @ 559 0x00

Ub1 kcvcpetb [48] @ 560 0x00

Ub1 kcvcpetb [49] @ 5610x00

Ub1 kcvcpetb [50] @ 562 0x00

Ub1 kcvcpetb [51] @ 563 0x00

Ub1 kcvcpetb [52] @ 564 0x00

Ub1 kcvcpetb [53] @ 565 0x00

Ub1 kcvcpetb [54] @ 566 0x00

Ub1 kcvcpetb [55] @ 567 0x00

Ub1 kcvcpetb [56] @ 568 0x00

Ub1 kcvcpetb [57] @ 569 0x00

Ub1 kcvcpetb [58] @ 570 0x00

Ub1 kcvcpetb [59] @ 5710x00

Ub1 kcvcpetb [60] @ 5720x00

Ub1 kcvcpetb [61] @ 5730x00

Ub1 kcvcpetb [62] @ 5740x00

Ub1 kcvcpetb [63] @ 575 0x00

Ub1 kcvcpetb [64] @ 576 0x00

Ub1 kcvcpetb [65] @ 577 0x00

Ub1 kcvcpetb [66] @ 578 0x00

Ub1 kcvcpetb [67] @ 579 0x00

Ub1 kcvcpetb [68] @ 580 0x00

Ub1 kcvcpetb [69] @ 5810x00

Ub1 kcvcpetb [70] @ 582 0x00

Ub1 kcvcpetb [71] @ 5830x00

Ub1 kcvcpetb [72] @ 584 0x00

Ub1 kcvcpetb [73] @ 585 0x00

Ub1 kcvcpetb [74] @ 586 0x00

Ub1 kcvcpetb [75] @ 5870x00

Ub1 kcvcpetb [76] @ 588 0x00

Ub1 kcvcpetb [77] @ 589 0x00

Ub1 kcvcpetb [78] @ 590 0x00

Ub1 kcvcpetb [79] @ 5910x00

Ub1 kcvcpetb [80] @ 5920x00

Ub1 kcvcpetb [81] @ 5930x00

Ub1 kcvcpetb [82] @ 594 0x00

Ub1 kcvcpetb [83] @ 595 0x00

Ub1 kcvcpetb [84] @ 5960x00

Ub1 kcvcpetb [85] @ 597 0x00

Ub1 kcvcpetb [86] @ 598 0x00

Ub1 kcvcpetb [87] @ 599 0x00

Ub1 kcvcpetb [88] @ 600 0x00

Ub1 kcvcpetb [89] @ 601 0x00

Ub1 kcvcpetb [90] @ 602 0x00

Ub1 kcvcpetb [91] @ 603 0x00

Ub1 kcvcpetb [92] @ 604 0x00

Ub1 kcvcpetb [93] @ 605 0x00

Ub1 kcvcpetb [94] @ 606 0x00

Ub1 kcvcpetb [95] @ 607 0x00

Ub1 kcvcpetb [96] @ 608 0x00

Ub1 kcvcpetb [97] @ 609 0x00

Ub1 kcvcpetb [98] @ 610 0x00

Ub1 kcvcpetb [99] @ 611 0x00

Ub1 kcvcpetb @ 612 0x00

Ub1 kcvcpetb [101] @ 613 0x00

Ub1 kcvcpetb @ 614 0x00

Ub1 kcvcpetb @ 615 0x00

Ub1 kcvcpetb @ 616 0x00

Ub1 kcvcpetb @ 617 0x00

Ub1 kcvcpetb @ 618 0x00

Ub1 kcvcpetb @ 619 0x00

Ub1 kcvcpetb @ 620 0x00

Ub1 kcvcpetb @ 621 0x00

Ub1 kcvcpetb @ 622 0x00

Ub1 kcvcpetb @ 623 0x00

Ub1 kcvcpetb @ 624 0x00

Ub1 kcvcpetb @ 625 0x00

Ub1 kcvcpetb @ 626 0x00

Ub1 kcvcpetb @ 627 0x00

Ub1 kcvcpetb @ 628 0x00

Ub1 kcvcpetb @ 629 0x00

Ub1 kcvcpetb @ 630 0x00

Ub1 kcvcpetb @ 631 0x00

Ub1 kcvcpetb @ 632 0x00

Ub1 kcvcpetb @ 633 0x00

Ub1 kcvcpetb @ 634 0x00

Ub1 kcvcpetb @ 635 0x00

Ub1 kcvcpetb @ 636 0x00

Ub1 kcvcpetb @ 637 0x00

Ub1 kcvcpetb @ 638 0x00

Ub1 kcvcpetb @ 639 0x00

Ub1 kcvcpetb @ 640 0x00

The scn of the data file is recorded in the four bytes at the beginning of the block offset of file 1.

BBED > d / v dba 1 offset 484 count 64

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 484 to 547 Dba:0x00400001

001397a2 0000c828 362ca0b3 00010000 l.

00000030 00000002 00000000 02000000 l... 0.

00000000 00000000 00000000 l.

Storage in AIX is in positive order, which is the opposite of linux

BBED > modify / x 001397b0 dba 1 offset 484

Warning: contents of previous BIFILE will be lost. Proceed? (Ybig N) Y

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 484 to 547 Dba:0x00400001

001397b0 0000c828 362ca0b3 00010000 00000030 00000002 00000000 02000000

00000000 00000000 00000000

BBED > sum apply

Check value for File 1, Block 1:

Current = 0x0065, required = 0x0065

BBED > d / v dba 1 offset 484 count 64

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 484 to 547 Dba:0x00400001

001397b0 0000c828 362ca0b3 00010000 l.

00000030 00000002 00000000 02000000 l... 0.

00000000 00000000 00000000 l.

BBED >

SYS@oralhr > col name for A80

SYS@oralhr > set linesize 9999 pagesize 9999

SYS@oralhr > col FIRST_CHANGE# for 9999999999999999

SYS@oralhr > SELECT NAME

2 sequence#

3 a.FIRST_CHANGE#

4 FROM v$archived_log a

5 WHERE resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)

6 AND a.sequence# = 49

7 ORDER BY a.sequence#

NAME SEQUENCE# FIRST_CHANGE#

/ oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc 49 1284016

SYS@oralhr > SELECT a.FILEX Magna. Name Magna. RECOVERJA. CHECKPOINTCHANGEPROGY status FROM v$datafile_header a

FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS

1 / oracle/app/oracle/oralhr/system01.dbf YES 1284016 ONLINE

2 / oracle/app/oracle/oralhr/sysaux01.dbf NO 1284157 ONLINE

3 / oracle/app/oracle/oralhr/undotbs01.dbf NO 1284157 ONLINE

4 / oracle/app/oracle/oralhr/users01.dbf NO 1284157 ONLINE

5 / oracle/app/oracle/oralhr/example01.dbf NO 1284157 ONLINE

SYS@oralhr >

BBED > d / v dba 1 offset 500 count 64

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 500 to 563 Dba:0x00400001

00000030 00000002 00000000 02000000 l... 0.

00000000 00000000 00000000 l.

The rba of the header block of the data file is also needed to skip the archive. It consists of seq#, log block#, and offset (fixed at 16), which determines where the archive log is applied to the data file. Rba starts at 500 consecutive bytes at the header block offset of the data file

BBED > modify / x 00000031 dba 1 offset 500

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 500 to 563 Dba:0x00400001

00000031 00000002 00000000 02000000 00000000 00000000 00000000

00000000 00000000 00000000

BBED > sum apply

Check value for File 1, Block 1:

Current = 0x0064, required = 0x0064

BBED > modify / x 00000010 dba 1 offset 508

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 508 to 571 Dba:0x00400001

00000010 02000000 00000000 00000000 00000000

00000000 00000000 00000000

BBED >

BBED > sum apply

Check value for File 1, Block 1:

Current = 0x0074, required = 0x0074

BBED > d / v dba 1 offset 500 count 64

File: / oracle/app/oracle/oralhr/system01.dbf (1)

Block: 1 Offsets: 500 to 563 Dba:0x00400001

00000031 00000002 00000010 02000000 l... 1.

00000000 00000000 00000000 l.

BBED > p kcvfhckp

Struct kcvfhckp, 160 bytes @ 484

Struct kcvcpscn, 8 bytes @ 484

Ub4 kscnbas @ 4840x001397b0

Ub2 kscnwrp @ 488 0x0000

Ub4 kcvcptim @ 4920x362ca0b3

Ub2 kcvcpthr @ 4960x0001

Union u, 12 bytes @ 500

Struct kcvcprba, 12 bytes @ 500

Ub4 kcrbaseq @ 500 0x00000031

Ub4 kcrbabno @ 504 0x00000002

Ub2 kcrbabof @ 508 0x0000

Ub1 kcvcpetb [0] @ 512 0x02

Ub1 kcvcpetb [1] @ 513 0x00

Ub1 kcvcpetb [2] @ 514 0x00

Ub1 kcvcpetb [3] @ 515 0x00

Ub1 kcvcpetb [4] @ 516 0x00

Ub1 kcvcpetb [5] @ 517 0x00

Ub1 kcvcpetb [6] @ 518 0x00

Ub1 kcvcpetb [7] @ 519 0x00

Ub1 kcvcpetb [8] @ 520 0x00

Ub1 kcvcpetb [9] @ 521 0x00

Ub1 kcvcpetb [10] @ 522 0x00

Ub1 kcvcpetb [11] @ 523 0x00

Ub1 kcvcpetb [12] @ 524 0x00

Ub1 kcvcpetb [13] @ 525 0x00

Ub1 kcvcpetb [14] @ 526 0x00

Ub1 kcvcpetb [15] @ 527 0x00

Ub1 kcvcpetb [16] @ 528 0x00

Ub1 kcvcpetb [17] @ 529 0x00

Ub1 kcvcpetb [18] @ 530 0x00

Ub1 kcvcpetb [19] @ 531 0x00

Ub1 kcvcpetb [20] @ 532 0x00

Ub1 kcvcpetb [21] @ 533 0x00

Ub1 kcvcpetb [22] @ 534 0x00

Ub1 kcvcpetb [23] @ 535 0x00

Ub1 kcvcpetb [24] @ 536 0x00

Ub1 kcvcpetb [25] @ 537 0x00

Ub1 kcvcpetb [26] @ 538 0x00

Ub1 kcvcpetb [27] @ 539 0x00

Ub1 kcvcpetb [28] @ 540 0x00

Ub1 kcvcpetb [29] @ 5410x00

Ub1 kcvcpetb [30] @ 542 0x00

Ub1 kcvcpetb [31] @ 543 0x00

Ub1 kcvcpetb [32] @ 544 0x00

Ub1 kcvcpetb [33] @ 545 0x00

Ub1 kcvcpetb [34] @ 546 0x00

Ub1 kcvcpetb [35] @ 547 0x00

Ub1 kcvcpetb [36] @ 548 0x00

Ub1 kcvcpetb [37] @ 549 0x00

Ub1 kcvcpetb [38] @ 550 0x00

Ub1 kcvcpetb [39] @ 551 0x00

Ub1 kcvcpetb [40] @ 5520x00

Ub1 kcvcpetb [41] @ 553 0x00

Ub1 kcvcpetb [42] @ 554 0x00

Ub1 kcvcpetb [43] @ 555 0x00

Ub1 kcvcpetb [44] @ 556 0x00

Ub1 kcvcpetb [45] @ 557 0x00

Ub1 kcvcpetb [46] @ 558 0x00

Ub1 kcvcpetb [47] @ 559 0x00

Ub1 kcvcpetb [48] @ 560 0x00

Ub1 kcvcpetb [49] @ 5610x00

Ub1 kcvcpetb [50] @ 562 0x00

Ub1 kcvcpetb [51] @ 563 0x00

Ub1 kcvcpetb [52] @ 564 0x00

Ub1 kcvcpetb [53] @ 565 0x00

Ub1 kcvcpetb [54] @ 566 0x00

Ub1 kcvcpetb [55] @ 567 0x00

Ub1 kcvcpetb [56] @ 568 0x00

Ub1 kcvcpetb [57] @ 569 0x00

Ub1 kcvcpetb [58] @ 570 0x00

Ub1 kcvcpetb [59] @ 5710x00

Ub1 kcvcpetb [60] @ 5720x00

Ub1 kcvcpetb [61] @ 5730x00

Ub1 kcvcpetb [62] @ 5740x00

Ub1 kcvcpetb [63] @ 575 0x00

Ub1 kcvcpetb [64] @ 576 0x00

Ub1 kcvcpetb [65] @ 577 0x00

Ub1 kcvcpetb [66] @ 578 0x00

Ub1 kcvcpetb [67] @ 579 0x00

Ub1 kcvcpetb [68] @ 580 0x00

Ub1 kcvcpetb [69] @ 5810x00

Ub1 kcvcpetb [70] @ 582 0x00

Ub1 kcvcpetb [71] @ 5830x00

Ub1 kcvcpetb [72] @ 584 0x00

Ub1 kcvcpetb [73] @ 585 0x00

Ub1 kcvcpetb [74] @ 586 0x00

Ub1 kcvcpetb [75] @ 5870x00

Ub1 kcvcpetb [76] @ 588 0x00

Ub1 kcvcpetb [77] @ 589 0x00

Ub1 kcvcpetb [78] @ 590 0x00

Ub1 kcvcpetb [79] @ 5910x00

Ub1 kcvcpetb [80] @ 5920x00

Ub1 kcvcpetb [81] @ 5930x00

Ub1 kcvcpetb [82] @ 594 0x00

Ub1 kcvcpetb [83] @ 595 0x00

Ub1 kcvcpetb [84] @ 5960x00

Ub1 kcvcpetb [85] @ 597 0x00

Ub1 kcvcpetb [86] @ 598 0x00

Ub1 kcvcpetb [87] @ 599 0x00

Ub1 kcvcpetb [88] @ 600 0x00

Ub1 kcvcpetb [89] @ 601 0x00

Ub1 kcvcpetb [90] @ 602 0x00

Ub1 kcvcpetb [91] @ 603 0x00

Ub1 kcvcpetb [92] @ 604 0x00

Ub1 kcvcpetb [93] @ 605 0x00

Ub1 kcvcpetb [94] @ 606 0x00

Ub1 kcvcpetb [95] @ 607 0x00

Ub1 kcvcpetb [96] @ 608 0x00

Ub1 kcvcpetb [97] @ 609 0x00

Ub1 kcvcpetb [98] @ 610 0x00

Ub1 kcvcpetb [99] @ 611 0x00

Ub1 kcvcpetb @ 612 0x00

Ub1 kcvcpetb [101] @ 613 0x00

Ub1 kcvcpetb @ 614 0x00

Ub1 kcvcpetb @ 615 0x00

Ub1 kcvcpetb @ 616 0x00

Ub1 kcvcpetb @ 617 0x00

Ub1 kcvcpetb @ 618 0x00

Ub1 kcvcpetb @ 619 0x00

Ub1 kcvcpetb @ 620 0x00

Ub1 kcvcpetb @ 621 0x00

Ub1 kcvcpetb @ 622 0x00

Ub1 kcvcpetb @ 623 0x00

Ub1 kcvcpetb @ 624 0x00

Ub1 kcvcpetb @ 625 0x00

Ub1 kcvcpetb @ 626 0x00

Ub1 kcvcpetb @ 627 0x00

Ub1 kcvcpetb @ 628 0x00

Ub1 kcvcpetb @ 629 0x00

Ub1 kcvcpetb @ 630 0x00

Ub1 kcvcpetb @ 631 0x00

Ub1 kcvcpetb @ 632 0x00

Ub1 kcvcpetb @ 633 0x00

Ub1 kcvcpetb @ 634 0x00

Ub1 kcvcpetb @ 635 0x00

Ub1 kcvcpetb @ 636 0x00

Ub1 kcvcpetb @ 637 0x00

Ub1 kcvcpetb @ 638 0x00

Ub1 kcvcpetb @ 639 0x00

Ub1 kcvcpetb @ 640 0x00

BBED >

III. 3.4 recover datafile and open Library

SYS@oralhr > recover datafile 1

ORA-00279: change 1284016 generated at 04/11/2016 14:39:47 needed for thread 1

ORA-00289: suggestion: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc

ORA-00280: change 1284016 for thread 1 is in sequence # 49

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: change 1284034 generated at 04/11/2016 14:40:54 needed for thread 1

ORA-00289: suggestion: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc

ORA-00280: change 1284034 for thread 1 is in sequence # 50

ORA-00279: change 1284100 generated at 04/11/2016 14:43:45 needed for thread 1

ORA-00289: suggestion: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc

ORA-00280: change 1284100 for thread 1 is in sequence # 51

ORA-00279: change 1284103 generated at 04/11/2016 14:43:48 needed for thread 1

ORA-00289: suggestion: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc

ORA-00280: change 1284103 for thread 1 is in sequence # 52

Log applied.

Media recovery complete.

SYS@oralhr >

SYS@oralhr > alter database open

Database altered.

SYS@oralhr > select count (1) from t_lhr_20160411_02

COUNT (1)

seven

SYS@oralhr >

The recovery is complete, and the data is missing three records in log 48.

The database opens normally.

III. 4 experiment summary

If only part of the archive file is lost, then use BBED to modify the file header to skip the missing archive log, thus apply all the existing archive logs, which can ensure the minimum data loss, so how to recover or use flexibly in the event of missing archive.

In the course of the experiment, you can simulate several scenarios, such as what will happen if you only modify the header offset484, but not the rda of the offset500. If the No. 47 archive is lost (that is, the redo log of the table is lost), will there be problems with the subsequent redo apply, etc., you can think more and do experiments to prove it, and simply record it:

SYS@oralhr > recover datafile 1

ORA-00279: change 1284016 generated at 04/11/2016 14:37:10 needed for thread 1

ORA-00289: suggestion: / oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc

ORA-00280: change 1284016 for thread 1 is in sequence # 49

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00283: recovery session canceled due to errors

ORA-10562: Error occurred while applying redo to data block (file# 1, block# 98185)

ORA-10564: tablespace SYSTEM

ORA-01110: data file 1:'/ oracle/app/oracle/oralhr/system01.dbf'

ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 88595

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [98185], [6110], [], []

ORA-01112: media recovery not started

Solution:

Recover database using backup controlfile until cancel

Alter database open resetlogs

If it still cannot be solved, add the following implied parameters:

Alter system set "_ allow_resetlogs_corruption" = true scope=spfile

Recover database using backup controlfile until cancel

Alter database open resetlogs

Startup force

Alter database open resetlogs

Alter system set "_ allow_resetlogs_corruption" = false scope=spfile

Alter system reset "_ allow_resetlogs_corruption" scope=spfile sid='*'

Chapter IV full backup of the database, shell script

After the above experiment, what we need to do is to back up the database. The following wheat seedlings provide you with two shell scripts based on archived and non-archived rman complete database. Pay attention to modify the contents of the yellow background to your own actual environment, in which the non-archived complete database needs to be restarted to mount state, or you can use other hot backup scripts.

IV. 1 filing, complete with open

Note that the yellow part is modified:

Export ORACLE_SID=ora11g

Export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Export PATH=$ORACLE_HOME/bin:$PATH

MYDATE= `date +'% Y% m% d% H% M% S``

BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID

Mkdir-p $BACKUP_DIR/log

Rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.