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

The main content of this article is to explain "how to solve all the redo log group problems lost by oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to solve all the redo log group problems lost by oracle"!

Suppose all redo log groups are lost and are dealt with separately in the following situations:

1.Oracle does not open archiving, consistency closes the database

2.Oracle does not open archiving, non-consistency closes the database

3.Oracle opens archiving, consistency closes the database

4.Oracle opens archiving, inconsistently shuts down the database

One: Oracle does not open archiving, consistency closes the database

There is a strange situation in the course of my experiment. I first deleted the redo file from the operating system level, but the database normally creates tables and insert data. My understanding is that when you commit, the lgwr process will trigger the lgwr process to rinse the new redo from the redo log buffer to the redo file, but if the redo file has been deleted, it will report an error, but he did not report an error:

[root@testdb59 / data/u01/app/oracle/oradata/stdb59] # ll

Total 13697796

-rw-r- 1 oracle oinstall 144916480 Apr 5 22:30 control01.ctl

-rw-r- 1 oracle oinstall 2147491840 Apr 5 22:26 liuwenhe.dbf

-rw-r- 1 oracle oinstall 52429312 Apr 5 22:26 redo01.log

-rw-r- 1 oracle oinstall 52429312 Apr 5 22:29 redo03.log

-rw-r- 1 oracle oinstall 4938801152 Apr 5 22:26 soe3.dbf

-rw-r- 1 oracle oinstall 2469404672 Apr 5 22:26 soe.dbf

-rw-r- 1 oracle oinstall 2705334272 Apr 5 22:26 sysaux01.dbf

-rw-r- 1 oracle oinstall 786440192 Apr 5 22:26 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Oct 16 12:37 temp01.dbf

-rw-r- 1 oracle oinstall 1073750016 Apr 5 22:26 temp.dbf

-rw-r- 1 oracle oinstall 309338112 Apr 5 22:26 undotbs01.dbf

-rw-r- 1 oracle oinstall 166469632 Apr 5 22:26 users01.dbf

Delete redo Fil

[root@testdb59 / data/u01/app/oracle/oradata/stdb59] # rm * .log

Check again and find that there is no redo file.

[root@testdb59 / data/u01/app/oracle/oradata/stdb59] # ll

Total 13595388

-rw-r- 1 oracle oinstall 144916480 Apr 5 22:50 control01.ctl

-rw-r- 1 oracle oinstall 2147491840 Apr 5 22:50 liuwenhe.dbf

-rw-r- 1 oracle oinstall 4938801152 Apr 5 22:50 soe3.dbf

-rw-r- 1 oracle oinstall 2469404672 Apr 5 22:50 soe.dbf

-rw-r- 1 oracle oinstall 2705334272 Apr 5 22:50 sysaux01.dbf

-rw-r- 1 oracle oinstall 786440192 Apr 5 22:50 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Oct 16 12:37 temp01.dbf

-rw-r- 1 oracle oinstall 1073750016 Apr 5 22:41 temp.dbf

-rw-r- 1 oracle oinstall 309338112 Apr 5 22:50 undotbs01.dbf

-rw-r- 1 oracle oinstall 166469632 Apr 5 22:50 users01.dbf

SQL > create table t (int int)

Table created.

SQL > insert into t values

1 row created.

SQL > commit

SQL > alter system switch logfile

System altered.

SQL > alter system checkpoint

System altered.

I don't understand! Asked the teacher, only to know that the original opened file handle is still there, there is no more after reboot! You will make a mistake.

(in vitro: that is to say, rm this file, but this file actually exists, first talk about its working principle, and then I will share the experiment with you, the working principle is not difficult, this tool needs to be implemented on the ext3 or ext4 file system, because the ext3 file system is a journal file system, ext3 file system stores information by the inode number and block block storage.

What? Don't know what the inode is? And block blocks? Well, in the explanation point, for example: a partition such as a book, then the block block is the content of each page of the book, and the inode number is the book's directory. When the system looks for files, find the inode number first and then find the block fast information on the hard disk according to the inode number. See?

Let's talk about the principle of deletion. When a file on the hard disk is deleted, it is not erased on the hard disk as it really thought. He disconnected the chain of the inode number and the block block, but the real data is still on the hard disk. Do you feel that deleting on the windos is so fast? do you not think about it? when you re-copy the new file where you delete the file, you will overwrite the previous file. In other words, it doesn't matter if you delete it, don't put the file in that location.)

Because the database is consistently closed, that is, there is no need for instance recovery, that is, you do not need the lost redo, so you can delete and rebuild the database directly, or you can recover the lost redo by recover database. Therefore, there are two recovery methods for this situation:

Method 1: directly clear the corresponding redo log group! That is, delete and re-establish!

SQL > shutdown immediate # consistency off

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 1275071648 bytes

Database Buffers 318767104 bytes

Redo Buffers 7319552 bytes

Database mounted.

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 30641

Current log sequence 30642

Clean up and delete newly created or directly clear all redo log groups, including current state and active status redo log groups!

SQL > alter database clear logfile group 1

Database altered.

SQL > alter database clear logfile group 3

Database altered.

SQL > alter database open

Database altered.

Method 2: recover method to restore the redo log, in the course of my experiment, sometimes this method will report an error, if an error is reported, then use the first way to recover!

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 536874104 bytes

Database Buffers 289406976 bytes

Redo Buffers 2392064 bytes

Database mounted.

SQL >

# recover the lost redo file, but you need open resetlogs before it can be created automatically!

SQL > recover database until cancel

Media recovery complete.

SQL > alter database open resetlogs

Database altered.

Second: Oracle does not open archiving, non-consistency shuts down the database

[root@testdb59 / data/u01/app/oracle/oradata/stdb59] # rm-f * .log

SQL > shu abort # non-consistent shutdown of database

ORACLE instance shut down.

If you try to use the previous clear or recover database at this time, you will get an error and cannot recover it. Since instance recovery is required at this time, please refer to another article (Oracle principle-understanding of rollforward and rollback of oracle instance recovery). The error is as follows:

First try to rebuild, when you try to clear the current log group, an error message is required! Because non-consistent shutdown does require instance recovery using redo with missing active and current states!

First start the database to the mount state

SQL > alter database clear logfile group 3

Alter database clear logfile group 3

*

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance stdb59 (thread 1)

ORA-00312: online log 3 thread 1:

'/ data/u01/app/oracle/oradata/stdb59/redo03.log'

Then try recover database, and the result is definitely not allowed, because the redo needed for instance recovery has been lost!

SQL > recover database until cancel

ORA-00279: change 21959466 generated at 04/06/2019 21:15:45 needed for thread 1

ORA-00289: suggestion:

/ data/u01/app/oracle/fast_recovery_area/STDB59/archivelog/2019_04_06/o1_mf_1_2_%

U_.arc

ORA-00280: change 21959466 for thread 1 is in sequence # 2

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

CANCEL

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

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

ORA-01110: data file 1:'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

ORA-01112: media recovery not started

SQL > alter database open RESETLOGS

Alter database open RESETLOGS

ERROR at line 1:

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

ORA-01110: data file 1:'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

In view of this situation, the recovery method is as follows:

Use an implicit parameter _ allow_resetlogs_corruption to force the database to start. After setting this parameter, Oracle will skip some consistency checks during the database Open process, so that the database may skip the inconsistent state and reach the purpose of the open database.

SQL > create pfile='/home/oracle/pfile.ora' from spfile

File created.

Then add to / home/oracle/pfile.ora

*. _ allow_resetlogs_corruption=true

SQL > startup mount pfile='/home/oracle/pfile.ora'

SQL > recover database until cancel; # to recover missing redo files

ORA-00279: change 21959471 generated at 04/06/2019 22:34:01 needed for thread 1

ORA-00289: suggestion:

/ data/u01/app/oracle/fast_recovery_area/STDB59/archivelog/2019_04_06/o1_mf_1_2_%

U_.arc

ORA-00280: change 21959471 for thread 1 is in sequence # 2

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

CANCEL

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

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

ORA-01110: data file 1:'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

ORA-01112: media recovery not started

If you are lucky, you can directly open the database in resetlogs!

SQL > alter database open RESETLOGS

Database altered.

If you encounter the following error, you will have to rebuild the control file:

SQL > alter database open RESETLOGS

Alter database open RESETLOGS

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00704: bootstrap process failure

ORA-00600: internal error code, arguments: [2662], [0], [21959484], [0]

[21959877], [4194545], [], []

Process ID: 13177

Session ID: 63 Serial number: 5

Rebuild the database control file

1) an error will be reported if you directly use the following alter database backup controlfile

SQL > alter database backup controlfile to trace as'/ data/u01/control_rebuild.trc'

Alter database backup controlfile to trace as'/ data/u01/control_rebuild.trc'

*

ERROR at line 1:

ORA-16433: The database must be opened in read/write mode.

2) you can also use the following specific format to rebuild

Query redo information of the database:

SQL > select GROUP#,MEMBER from v$logfile

GROUP# MEMBER

3 / data/u01/app/oracle/oradata/stdb59/redo03.log

1/ data/u01/app/oracle/oradata/stdb59/redo01.log

Query the datafile information of the database

SQL > select MEMBER from v$logfile

MEMBER

/ data/u01/app/oracle/oradata/stdb59/redo03.log

/ data/u01/app/oracle/oradata/stdb59/redo01.log

/ data/u01/app/oracle/oradata/stdb59/redo04.log

/ data/u01/app/oracle/oradata/stdb59/redo05.log

/ data/u01/app/oracle/oradata/stdb59/redo06.log

/ data/u01/app/oracle/oradata/stdb59/redo07.log

Find out the database character set:

SQL > select userenv ('language') nls_lang from dual

NLS_LANG

AMERICAN_AMERICA.AL32UTF8

Then edit the script to create the control file: note that the testdb57 here is db_name. If it is the main library converted from adg, do not write db_unique_name.

CREATE CONTROLFILE REUSE DATABASE 'testdb57' NORESETLOGS ARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 226

LOGFILE

GROUP 3'/ data/u01/app/oracle/oradata/stdb59/redo03.log' SIZE 50m

GROUP 1'/ data/u01/app/oracle/oradata/stdb59/redo01.log' SIZE 50m

DATAFILE

'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/sysaux01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/undotbs01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/users01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/liuwenhe.dbf'

'/ data/u01/app/oracle/oradata/stdb59/soe.dbf'

'/ data/u01/app/oracle/oradata/stdb59/soe3.dbf'

CHARACTER SET AL32UTF8

Then start the database directly to the nomount state and execute the creation script

SQL > startup nomount pfile='/home/oracle/pfile.ora'

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 1275071648 bytes

Database Buffers 318767104 bytes

Redo Buffers 7319552 bytes

CREATE CONTROLFILE REUSE DATABASE 'testdb57' NORESETLOGS ARCHIVELOG

MAXLOGFILES 50

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 226

LOGFILE

GROUP 3'/ data/u01/app/oracle/oradata/stdb59/redo03.log' SIZE 50m

GROUP 1'/ data/u01/app/oracle/oradata/stdb59/redo01.log' SIZE 50m

DATAFILE

'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/sysaux01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/undotbs01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/users01.dbf'

'/ data/u01/app/oracle/oradata/stdb59/liuwenhe.dbf'

'/ data/u01/app/oracle/oradata/stdb59/soe.dbf'

'/ data/u01/app/oracle/oradata/stdb59/soe3.dbf'

CHARACTER SET AL32UTF8

Control file created.

Then use oradebug to advance the in-memory scn number so that subsequent recover can be executed to recover the missing redo file, because the recover process reads the in-memory scn. Note that alter session set events' 10015 trace name adjust_scn level 10 years; this method has expired in 11.2.0.4.

(digression: let's talk about Oracle's SCN first. Within the database, SCN is an one-way incremental number that is included in control files, data files, online Redo logs, archive logs, and backup collections. In the internal file, SCN is saved through Base and Wrap. Base is the base bit of SCN numbering and is saved by 32-bit binary bits. Once the 32-bit length is exceeded, the system will automatically carry in the Wrap. That is, the number of rounding times more than 4G represented by Wrap)

SQL > oradebug poke 0x06001AE70 4 0x001B7740

Oradebug advances the scn number. In the poke command, the first bit parameter is the corresponding number of memory bits written, the second bit parameter is the write length, and the third bit parameter is the write value. The default write value is decimal. Here we specify to write hexadecimal (starting with 0x). Each value segment corresponds to 8 hexadecimals, and the corresponding number of digits is 4 digits.

First find out the scn number in the control file of the database

SQL > select file#, checkpoint_change# from v$datafile

FILE# CHECKPOINT_CHANGE#

--

1 21959486

2 21959486

3 21959486

4 21959486

5 21959486

6 21959486

7 21959486

7 rows selected.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug DUMPvar SGA kcsgscn_

Kcslf kcsgscn_ [06001AE70, 06001AEA0) = 014F14A2 00000001 00000000 00000000 000000EB 00000000 00000000 00000000 0000000000000000 6001AB50 00000000

SQL > oradebug poke 0x06001AE70 4 21959486

BEFORE: [06001AE70, 06001AE74) = 00000000

AFTER: [06001AE70, 06001AE74) = 014F133E

(or you can convert 21959486 to hexadecimal and then modify it

SQL > select to_char (21959486, 'XXXXXXXXXXX') from dual

TO_CHAR (2195

-

14F133E

SQL > oradebug poke 0x06001AE70 4 0x14F133E

BEFORE: [06001AE70, 06001AE74) = 00000000

AFTER: [06001AE70, 06001AE74) = 014F133E)

Check again that it has indeed become 014F133E (corresponding to 21959486 in decimal system)

SQL > oradebug DUMPvar SGA kcsgscn_

Kcslf kcsgscn_ [06001AE70, 06001AEA0) = 014F133E 00000000 00000000 00000000 0000000000000000 6001AB50 00000000

Then perform a recover for an incomplete recovery:

SQL > recover database until cancel

ORA-00279: change 21959486 generated at 04/06/2019 23:52:28 needed for thread 1

ORA-00289: suggestion:

/ data/u01/app/oracle/fast_recovery_area/STDB59/archivelog/2019_04_07/o1_mf_1_2_%

U_.arc

ORA-00280: change 21959486 for thread 1 is in sequence # 2

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

CANCEL

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

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

ORA-01110: data file 1:'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

ORA-01112: media recovery not started

SQL > alter database open resetlogs

Database altered.

So far the recovery has been successful!

Three: oracle starts archiving, consistency closes.

This situation is the same as case 1, and no instance recovery is required, so you can simply delete the redo groups that are renewed or owned by recover.

Method 1: directly clear the corresponding redo log group! That is, delete and re-establish!

SQL > shutdown immediate # consistency off

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 1275071648 bytes

Database Buffers 318767104 bytes

Redo Buffers 7319552 bytes

Database mounted.

Clean up and delete newly created or directly clear all redo log groups, including current state and active status redo log groups!

SQL > alter database clear logfile group 1

Database altered.

SQL > alter database clear logfile group 3

Database altered.

SQL > alter database open

Database altered.

Method 2: recover method to restore the redo log, in the course of my experiment, sometimes this method will report an error, if an error is reported, then use the first way to recover!

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 536874104 bytes

Database Buffers 289406976 bytes

Redo Buffers 2392064 bytes

Database mounted.

SQL >

# recover the lost redo file, but you need open resetlogs before it can be created automatically!

SQL > recover database until cancel

Media recovery complete.

SQL > alter database open resetlogs

Database altered.

Four: open archiving, non-consistent close

In this case, incomplete recovery can only be done with the help of archived logs!

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

-

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

--

1 1 39 52428800 512 1 YES

INACTIVE 4318162327 20-APR-19 4318209770 20-APR-19

3 1 40 52428800 512 1 NO

CURRENT 4318209770 20-APR-19 2.8147E+14

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 39

Next log sequence to archive 40

Current log sequence 40

Delete redo log Fil

[oracle@testdb59 stdb59] $rm-f * .log

Then non-consistent shutdown

SQL > shu abort

ORACLE instance shut down.

Resolution process:

SQL > startup mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size 2253664 bytes

Variable Size 1275071648 bytes

Database Buffers 318767104 bytes

Redo Buffers 7319552 bytes

Database mounted.

# recover the lost redo file, but you need open resetlogs before it can be created automatically!

SQL > recover database until cancel

Media recovery complete.

Try to open it in resetlog mode. If the error is as follows, you have to rely on the implicit parameter _ allow_resetlogs_corruption.

SQL > alter database open RESETLOGS

Alter database open RESETLOGS

*

ERROR at line 1:

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

ORA-01110: data file 1:'/ data/u01/app/oracle/oradata/stdb59/system01.dbf'

Use an implicit parameter _ allow_resetlogs_corruption to force the database to start. After setting this parameter, Oracle will skip some consistency checks during the database Open process, so that the database may skip the inconsistent state and reach the purpose of the open database.

SQL > create pfile='/home/oracle/pfile.ora' from spfile

File created.

Then add to / home/oracle/pfile.ora

*. _ allow_resetlogs_corruption=true

SQL > startup mount pfile='/home/oracle/pfile.ora'

SQL > alter database open RESETLOGS

Database altered.

Then consistency shuts down the database, removes the implicit parameter _ allow_resetlogs_corruption, and restarts the database!

At this point, I believe you have a deeper understanding of "how to solve all the redo log group problems lost by oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.