Shulou(Shulou.com)06/01 Report--

This article to share with you is about Online Redo Log damage handling example analysis, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

Let's take a look at the current log group deletion processing under inconsistent shutdown conditions.

6. Inconsistency closes the current log group processing

We went back to the 10g Windows version for experimentation.

SQL> select group#, archived, status, first_change#,sequence# from v$log;

GROUP# ARCHIVED STATUS FIRST_CHANGE# SEQUENCE#

---------- -------- ---------------- ------------- ----------

1 NO CURRENT 605509 8

2 YES ACTIVE 604411 7

3 YES INACTIVE 604371 6

SQL> shutdown abort;

The ORACLE routine has been closed.

SQL>

E:\oracle\product\10.2.0\oradata\orcl>rename REDO01B.LOG REDO01B.LOG_bak

E:\oracle\product\10.2.0\oradata\orcl>rename REDO01A.LOG REDO01A.LOG_bak

SQL> startup

The ORACLE routine has been started.

Total System Global Area 603979776 bytes

Fixed Size 1250380 bytes

Variable Size 218106804 bytes

Database Buffers 377487360 bytes

Redo Buffers 7135232 bytes

Database loaded.

ORA-00313: Unable to open member of log group 1 for thread 1

ORA-00312: Online Log 1 Thread 1:

'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_1_85TNYSWS_.L

OG'

ORA-27041: Unable to open file

OSD-04002: ？???？？?

O/S-Error: (OS 2) ？？？????？???？？???

ORA-00312: Online Log 1 Thread 1:

'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ONLINELOG\O1_MF_1_85TNYS8S_.LOG'

ORA-27041: Unable to open file

OSD-04002: ？???？？?

O/S-Error: (OS 2) ？？？????？???？？???

After that, it is difficult to open the database using conventional methods.

SQL> recover database until cancel;

ORA-00279: Change 605036 (generated on 09/23/2012 09:01:03) is required for Thread 1

ORA-00289: Recommendations:

E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_09_23\O1_MF_1_

7_%U_.ARC

ORA-00280: Change 605036 (for thread 1) in sequence #7

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

auto

ORA-00279: Change 605509 (generated on 09/23/2012 09:02:53) is required for Thread 1

ORA-00289: Recommendations:

E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_09_23\O1_MF_1_

8_%U_.ARC

ORA-00280: Change 605509 (for thread 1) in sequence #8

ORA-00278: Log files are no longer required for this recovery

'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_09_23\O1_MF_1

_7_85WQXX8S_.ARC'

ORA-00308: Unable to open archive log

'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_09_23\O1_MF_1

_8_%U_.ARC'

ORA-27041: Unable to open file

OSD-04002: ？???？？?

O/S-Error: (OS 2) ？？？????？???？？???

ORA-01547: WARNING: RECOVER succeeds but OPEN RESETLOGS will result in the following error

ORA-01194: File 1 needs more recovery to maintain consistency

ORA-01110: Data file 1:

'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_85TSQFJM_.DBF'

SQL> alter database open resetlogs;

alter database open resetlogs

*

Error in line 1:

ORA-01194: File 1 needs more recovery to maintain consistency

ORA-01110: Data file 1:

'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DATAFILE\O1_MF_SYSTEM_85TSQFJM_.DBF'

We don't have many good options for dealing with this problem, dealing with the backup restore methods described in the previous section. However, if there is no backup, we need to take risks.

The reason why we cannot enter the open state from mount is mainly because we cannot pass the file header integrity check step. In Oracle, we can use some backdoors to get Oracle through the open phase without checking. Now we need to add these configurations to the parameters.

SQL> shutdown abort;

The ORACLE routine has been closed.

SQL> startup nomount;

The ORACLE routine has been started.

Total System Global Area 603979776 bytes

Fixed Size 1250380 bytes

Variable Size 218106804 bytes

Database Buffers 377487360 bytes

Redo Buffers 7135232 bytes

SQL>

--Generate PFILE

SQL> create pfile='d:\pfile.ora' from spfile;

File created.

The implicit parameter_allow_resetlogs_corruption allows us to avoid consistency checking. Add parameter lines to pfile.

(parameter file content)

_allow_resetlogs_corruption=TRUE

orcl.__ db_cache_size=377487360

orcl.__ java_pool_size=4194304

orcl.__ large_pool_size=4194304

orcl.__ shared_pool_size=209715200

orcl.__ streams_pool_size=0

After that, try opening the database.

SQL> shutdown abort

The ORACLE routine has been closed.

SQL> startup pfile='d:\pfile.ora';

The ORACLE routine has been started.

Total System Global Area 603979776 bytes

Fixed Size 1250380 bytes

Variable Size 218106804 bytes

Database Buffers 377487360 bytes

Redo Buffers 7135232 bytes

Database loaded.

ORA-01589: RESETLOGS or NORESETLOGS option must be used to open database

SQL> alter database open resetlogs;

The database has changed.

SQL> conn sys/oracle@orcl as sysdba

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as SYS

SQL> select group#, archived, status, first_change#,sequence# from v$log;

GROUP# ARCHIVED STATUS FIRST_CHANGE# SEQUENCE#

---------- -------- ---------------- ------------- ----------

1 NO CURRENT 605510 1

2 YES UNUSED 0 0

3 YES UNUSED 0 0

Note: This method has a lot of problems. Adding parameters allows us to bypass consistency verification, but in many scenarios, especially file corruption scenarios, boot may encounter other error messages. Once we open the database, we understand that the database is in danger, and all we can do is backup the important data as soon as possible.

No matter what type of error occurs, a usable, complete backup is our lifesaver. To develop a complete backup strategy, check the effectiveness of backup regularly, and prevent it from burning.

The above is an example analysis of Online Redo Log damage handling. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow 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.