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

In this issue, the editor will bring you the experimental analysis of Online Redo Log damage treatment. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Oracle core files include control files, data files and online redo logs (Online Redo Log). Online Redo Log and Control File adopt the strategy of data redundancy for multipath protection respectively. Whether it is Control File or Online Redo Log Group Member, you can specify multiple identical file objects and distribute them across different storage media. In the event of a media failure, such as a hard disk media failure, we can simply replace it with files from other storage locations.

Therefore, even in a formal production environment, it is rare for Control File and Online Redo Log corruption to be unrecoverable if proper control file membership groups and Online Redo Log groups are set up.

But if this happens, how should we deal with it?

1. Discussion on experimental environment and influencing factors.

We choose Oracle 10g environment to carry on the experiment.

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

-- the database is in archive mode

SQL > archive log list

Database log mode archive mode

Automatic archiving enabled

End of Archive USE_DB_RECOVERY_FILE_DEST

The earliest online log sequence 237

Next archive log sequence 239

Current log sequence 239

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

GROUP# ARCHIVED STATUS FIRST_CHANGE#

--

1 YES INACTIVE 3567149

2 YES INACTIVE 3572305

3 NO CURRENT 3572332

In the experiment, we will delete the Online Redo Log group member file when the database is closed. Note that in the Windows environment, due to operating system limitations, there is no way to delete a file that is in use or related to the instance.

Three potential factors may affect the final result: log archiving mode, database shutdown mode, and log group status deletion.

The log archiving mode indicates whether Oracle does additional archiving of the online redo log member that has already been written. The significance of maintaining a continuous archive information for Oracle is that a full recovery of complete recovery can be achieved. In archive mode, we can start with a past backup set, use the archive log to push forward and repeat the transaction, and finally apply it to the current log group to restore it to a full recovery point. If the log has been archived, it means that the contents of the log have been written to the data file, and the state must be non-Active. In our experiment, the data file is not a lost object, so the loss of logs that have been written to the data file will not have a fatal effect.

There are several ways to close the database in Oracle, but generally speaking, there are only two major categories: consistent closure and non-consistent closure. Consistency shutdown means that Oracle writes unwritten dirty blocks to the data file before shutting down the database, and the control file is consistent with the data file. Under the condition of consistency off, Oracle does not need to carry out the Instance Recovery process in the open phase. Shutdown abort is the only non-consistent shutdown, which is the same as power outage. Under non-consistent shutdown, Oracle needs to instance recovery in the open phase, and this process requires the cooperation of redo log.

Delete log status. Whether the deleted log group is the current log group is also an important factor. If it is the current log group, it means that Oracle needs to read and write to the filegroup in the startup state. If the current log group is not deleted, it may have the same problem, because the non-current log group may be in the Active state.

Next, we carry on the experiment separately.

2. Delete non-current log group when it is completely closed

The current log file status is as follows:

SQL > select group#, type, member from v$logfile

GROUP# TYPE MEMBER

-

3 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO03A.LOG

2 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02A.LOG

1 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO01A.LOG

1 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO01B.LOG

3 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO03B.LOG

2 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02B.LOG

6 rows selected

The current log group number is 3, close the database and delete 2 sets of log files.

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

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

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

E:\ oracle\ product\ 10.2.0\ oradata\ orcl > dir

The volume in drive E has no label.

The serial number of the volume is 7CD0-C497

E:\ oracle\ product\ 10.2.0\ oradata\ orcl directory

2012 09 22 13:20

2012-09-22 13:20..

2012-09-22 12:04 CHANGETRACKING

2012-09-22 13:19 7356416 CONTROL01.CTL

2012-09-22 13:19 7356416 CONTROL02.CTL

2012-09-22 13:19 7356416 CONTROL03.CTL

2012-09-22 13:19 104865792 EXAMPLE01.DBF

2012-09-22 13:19 52429312 REDO01A.LOG

2012-09-22 13:19 52429312 REDO01B.LOG

2012-09-22 13:19 52429312 REDO02A.LOG_bak

2012-09-22 13:19 52429312 REDO02B.LOG_bak

2012-09-22 13:19 52429312 REDO03A.LOG

2012-09-22 13:19 52429312 REDO03B.LOG

2012-09-22 13:19 304095232 SYSAUX01.DBF

(for reasons of space, omit part of the content. )

16 files 3178867712 bytes

3 directories 204274311168 available bytes

Restart the database, and then Oracle reports an error during the mount to open phase because the log file defined in the control file cannot be found.

SQL > startup

The ORACLE routine has been started.

Total System Global Area 603979776 bytes

Fixed Size 1250380 bytes

Variable Size 155192244 bytes

Database Buffers 440401920 bytes

Redo Buffers 7135232 bytes

The database is loaded.

ORA-00313: unable to open a member of log group 2 (for thread 1)

ORA-00312: online log 2 thread 1:

'e:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02A.LOG'

ORA-00312: online log 2 thread 1:

'e:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02B.LOG'

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

In general, if the scene is completely closed, we can ensure that Oracle writes everything in the online redo log to the data file and keeps it consistent.

For non-current log member groups, if it is deleted by mistake, there are no too many problems, it just needs to be rebuilt.

SQL > alter database clear logfile group 2

The database has changed.

-- fully open, no data loss.

SQL > alter database open

The database has changed.

E:\ oracle\ product\ 10.2.0\ oradata\ orcl directory

2012 09 22 13:23

2012-09-22 13:23..

2012-09-22 12:04 CHANGETRACKING

2012-09-22 13:20 7356416 CONTROL01.CTL

2012-09-22 13:20 7356416 CONTROL02.CTL

2012-09-22 13:20 7356416 CONTROL03.CTL

2012-09-22 13:19 104865792 EXAMPLE01.DBF

2012-09-22 13:23 ONLINELOG

2012-09-22 13:19 52429312 REDO01A.LOG

2012-09-22 13:19 52429312 REDO01B.LOG

2012-09-22 13:23 52429312 REDO02A.LOG

2012-09-22 13:19 52429312 REDO02A.LOG_bak

2012-09-22 13:23 52429312 REDO02B.LOG

2012-09-22 13:19 52429312 REDO02B.LOG_bak

2012-09-22 13:19 52429312 REDO03A.LOG

2012-09-22 13:19 52429312 REDO03B.LOG

(for reasons of space, some are omitted. )

18 files 3283726336 bytes

4 directories 204164952064 available bytes

Oracle recreates the log file after clear log.

3. Delete the current log group when it is completely closed.

What should we do if the current log group is deleted when it is completely closed?

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

GROUP# ARCHIVED STATUS FIRST_CHANGE#

--

1 YES INACTIVE 3567149

2 NO CURRENT 3576416

3 YES INACTIVE 3572332

SQL > select group#, type, member from v$logfile

GROUP# TYPE MEMBER

-

3 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO03A.LOG

2 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02A.LOG

1 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO01A.LOG

1 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO01B.LOG

3 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO03B.LOG

2 ONLINE E:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02B.LOG

6 rows selected

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

Delete the log group member and restart.

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

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

SQL > startup

The ORACLE routine has been started.

Total System Global Area 603979776 bytes

Fixed Size 1250380 bytes

Variable Size 159386548 bytes

Database Buffers 436207616 bytes

Redo Buffers 7135232 bytes

The database is loaded.

ORA-00313: unable to open a member of log group 2 (for thread 1)

ORA-00312: online log 2 thread 1:

'e:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02A.LOG'

ORA-00312: online log 2 thread 1:

'e:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02B.LOG'

Use the Clear method to try to restore.

SQL > alter database clear logfile group 2

Alter database clear logfile group 2

*

An error occurred on line 1:

ORA-00350: log 2 (log of instance orcl, thread 1) needs to be archived

ORA-00312: online log 2 thread 1:

'e:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02A.LOG'

ORA-00312: online log 2 thread 1:

'e:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ ORCL\ REDO02B.LOG'

The contents of the current log need to be archived, so clear log cannot be done directly. The author guesses: if this is a non-archiving mode, can it be successful? As a matter of fact, here is the experimental process of insertion.

-- current log group is 1

SQL > alter database clear logfile group 1

Database altered.

SQL > alter database open

Database altered.

SQL > select open_mode from v$database

OPEN_MODE

-

READ WRITE

The current data file is consistent.

SQL > select ts#, checkpoint_change#, last_change# from v$datafile

TS# CHECKPOINT_CHANGE# LAST_CHANGE#

0 3577306 3577306

1 3577306 3577306

2 3577306 3577306

4 3577306 3577306

6 3577306 3577306

You can use recover to let Oracle perform a virtual restore action to return to the final state.

SQL > recover database until cancel

Complete the media recovery.

SQL > alter database open

Alter database open

*

An error occurred on line 1:

ORA-01589: to open the database, you must use the RESETLOGS or NORESETLOGS option

SQL > alter database open resetlogs

The database has changed.

Although it is until cancel, there is no data to lose. It's just that in open, you need to use resetlog mode to start a new dynasty.

SQL > select open_mode, current_scn from v$database

OPEN_MODE CURRENT_SCN

--

READ WRITE 3577513

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

GROUP# ARCHIVED STATUS FIRST_CHANGE# SEQUENCE#

--

1 NO CURRENT 3577308 2

2 YES INACTIVE 3577307 1

3 YES UNUSED 0 0

Conclusion: under the condition of consistency off, if there is a problem with the online log group, even if the file is lost, there will be no data loss, because the data file is consistent.

The above is the experimental analysis of Online Redo Log damage treatment shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to 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.