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

Most people do not understand the knowledge points of this article "how to recover from Oracle redo file damage", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to recover from Oracle redo file corruption" article.

Redo file corruption involves a variety of scenarios, which can be divided into four parts:

1. According to the status of redo, it can be divided into current, active and inactive.

2. According to the database archiving mode, it can be divided into archived and non-archived.

3. According to whether the dirty block is written to the data file, it can be divided into yes and no.

4. According to the state of the database when it is damaged, it can be divided into online and closed.

Now we will mainly introduce the contents related to redo file recovery through two parts:

1. Introduce the recovery methods of various scenarios according to the redo state dimension.

2. Simulate the operation of several recovery methods

1. Introduce the recovery methods of various scenarios according to the redo status dimension.

1.1.Introduces current redo file recovery:

1.2. introduction to active redo file recovery:

1.3.Introduces inactive redo file recovery:

Second, simulate the operation of several recovery methods

The following two scenarios are mainly selected: "the redo file of current closes the database abnormally when the database is closed abnormally" and "the redo file of current is corrupted when the database is online when the database is not written to the data file in non-archive mode". For the recovery of other scenarios, please refer to the above recovery operation.

2.1. The redo file of current simulates the recovery of damage when the database is shut down abnormally and dirty blocks are not written to the data file in archive mode:

1. Basic database information and redo

SQL > show parameter db_name

NAME TYPE VALUE

-

Db_name string leonliao

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / home/oracle/oradata/leonliao/arch

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

-

1 1 9 52428800 512 1 NO CURRENT 1250771 07-MAR-16 2.8147E+14

2 1 8 52428800 512 1 YES INACTIVE 1250768 07-MAR-16 1250771 07-MAR-16

3 1 7 52428800 512 1 YES INACTIVE 1250765 07-MAR-16 1250768 07-MAR-16

SQL > select * from v$logfile

GROUP# STATUS TYPE MEMBER IS_

3 ONLINE / home/oracle/oradata/leonliao/redo03.log NO

2 ONLINE / home/oracle/oradata/leonliao/redo02.log NO

1 ONLINE / home/oracle/oradata/leonliao/redo01.log NO

2. Insert a record in the t_redo table 2 and shutdown abort to close the database

SQL > select * from t_redo

ID

-

one

SQL > insert into t_redo values (2)

1 row created.

SQL > commit

Commit complete.

SQL > shutdown abort

ORACLE instance shut down.

3. Delete the redo file of current

[oracle@leon1 leonliao] $pwd

/ home/oracle/oradata/leonliao

[oracle@leon1 leonliao] $rm-rf redo01.log

4. Start the database to mount state and try to open the database

SQL > startup mount

ORACLE instance started.

Total System Global Area 626327552 bytes

Fixed Size 2230952 bytes

Variable Size 184550744 bytes

Database Buffers 432013312 bytes

Redo Buffers 7532544 bytes

Database mounted.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1:'/ home/oracle/oradata/leonliao/redo01.log'

ORA-27037: unable to obtain file status

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

Additional information: 3

SQL > alter database clear logfile group 1

Alter database clear logfile group 1

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1:'/ home/oracle/oradata/leonliao/redo01.log'

SQL > alter database clear unarchived logfile group 1

Alter database clear unarchived logfile group 1

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1:'/ home/oracle/oradata/leonliao/redo01.log'

SQL > recover database until cancel

ORA-00279: change 1250771 generated at 03/07/2016 07:35:44 needed for thread 1

ORA-00289: suggestion: / home/oracle/oradata/leonliao/arch/1_9_905840705.dbf

ORA-00280: change 1250771 for thread 1 is in sequence # 9

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

Auto

ORA-00308: cannot open archived log'/ home/oracle/oradata/leonliao/arch/1_9_905840705.dbf'

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'/ home/oracle/oradata/leonliao/arch/1_9_905840705.dbf'

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 1 needs more recovery to be consistent

ORA-01110: data file 1:'/ home/oracle/oradata/leonliao/system01.dbf'

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:'/ home/oracle/oradata/leonliao/system01.dbf'

5. Set the implied parameter _ allow_resetlogs_corruption to true

SQL > alter system set "_ allow_resetlogs_corruption" = true scope=spfile

System altered.

SQL > shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 626327552 bytes

Fixed Size 2230952 bytes

Variable Size 184550744 bytes

Database Buffers 432013312 bytes

Redo Buffers 7532544 bytes

Database mounted.

SQL > alter database open resetlogs

Database altered.

6. Verify whether the data is lost. The record with data 2 has been lost.

SQL > select * from t_redo

ID

-

one

2.2. Simulated recovery of current redo file damage when dirty blocks are not written to the data file when the database is online in non-archive mode:

1. Basic database information and redo

SQL > show parameter db_name

NAME TYPE VALUE

-

Db_name string leonliao

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination / home/oracle/oradata/leonliao/arch

Oldest online log sequence 2

Current log sequence 4

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

-

1 14 52428800 512 1 NO CURRENT 1250086 07-MAR-16 2.8147E+14

2 12 52428800 512 1 NO INACTIVE 1250080 07-MAR-16 1250083 07-MAR-16

3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16

SQL > select * from v$logfile

GROUP# STATUS TYPE MEMBER IS_

3 ONLINE / home/oracle/oradata/leonliao/redo03.log NO

2 ONLINE / home/oracle/oradata/leonliao/redo02.log NO

1 ONLINE / home/oracle/oradata/leonliao/redo01.log NO

2. Create a t_redo table and insert a piece of data

SQL > create table t_redo (id number)

Table created.

SQL > insert into t_redo values (1)

1 row created.

SQL > commit

Commit complete.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

-

1 14 52428800 512 1 NO CURRENT 1250086 07-MAR-16 2.8147E+14

2 12 52428800 512 1 NO INACTIVE 1250080 07-MAR-16 1250083 07-MAR-16

3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16

3. Delete the redo01.log file of current

[oracle@leon1 leonliao] $pwd

/ home/oracle/oradata/leonliao

[oracle@leon1 leonliao] $rm-rf redo01.log

4. Try to initialize the redo01.log file directly by not archiving, but cannot initialize the redo file of current.

SQL > alter database clear unarchived logfile group 1

Alter database clear unarchived logfile group 1

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1:'/ home/oracle/oradata/leonliao/redo01.log'

SQL > alter database clear logfile group 1

Alter database clear logfile group 1

*

ERROR at line 1:

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

ORA-00312: online log 1 thread 1:'/ home/oracle/oradata/leonliao/redo01.log'

5. By switching the current state to the active state and initializing the redo01.log file

SQL > alter system switch logfile

System altered.

SQL > alter database clear logfile group 1

Database altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

-

1 1 0 52428800 512 1 NO UNUSED 1250086 07-MAR-16 1250147 07-MAR-16

2 1 5 52428800 512 1 NO CURRENT 1250147 07-MAR-16 2.8147E+14

3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16

6. Verify whether the data is lost and not lost.

SQL > select * from t_redo

ID

-

1 above is about "Oracle redo file damage how to recover" this article, I believe we all have a certain understanding, I hope the editor to share the content to help you, if you want to know more related knowledge, please pay attention to the industry information channel.

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.