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

Let's deal with a more complex situation, where the log is corrupted in the case of a non-consistent shutdown. This situation is of great practical significance. Because Oracle may not allow consistency to close when an error occurs. Inexperienced processors tend to hastily shut down, and the scenarios we get are often inconsistent.

4. Inconsistency disables non-current log group processing-- Inactive log group

In the case of non-consistent shutdown, the damage to all members of the online log group is very complex, and there is also a potential for data loss. What we're talking about here is a non-consistent shutdown, that is, after shutdown abort or a forced power outage. Because the Oracle database adopts the strategy of writing log first, we can not guarantee that all the dirty blocks (Dirty Block) are written to the data file at the shutdown point, and the data files and control files are consistent in SCN time.

But when the database is restarted, during the open phase, Oracle instance recovery, check point from the last incremental checkpoint, repeats the transaction, and rolls back the transaction. The Instance Recovery process is also known as the rollforward rollback process, or the Cache Recovery and Transaction Recovery procedures.

If you do an inconsistent shutdown, you will need to do instance recovery, and what you need most to do instance recovery is online redo log. If we delete online redo log group exactly what is needed for instance recovery, then we can only sacrifice part of the data for incomplete recovery.

The non-current log group we deleted is also risky, because after the log is switched to a non-current log, the corresponding dirty blocks of the log group cannot be written to the data file for a period of time. This is the active status of the log file. This section discusses the deletion of non-current log members, divided into Inactive log groups and non-Inactive log groups.

We choose the Linux version of Oracle 11g for the next experiment.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

The current log is as follows:

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

GROUP# STATUS ARCHIVED SEQUENCE#

--

1 INACTIVE YES 108

2 CURRENT NO 110

3 INACTIVE YES 109

SQL > drop table t

Table dropped

SQL > create table t as select * from dba_objects

Table created

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

GROUP# STATUS MEMBER

-

3 / u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0_.log

3 / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp_.log

2 / u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log

2 / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

1/ u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log

1/ u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log

6 rows selected

After forced downtime, delete the log group 1 object.

[oracle@bspdev ~] $sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 6 22:27:57 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL > conn / as sysdba

Connected.

SQL > shutdown abort

ORACLE instance shut down.

[oracle@bspdev ~] $mv / u01/oradata/WILSON/onlinelog/o1_mf_1_870jlj6v_.log / u01/oradata/WILSON/onlinelog/redogroup1.log

[oracle@bspdev ~] $rm / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870jljvk_.log

[oracle@bspdev ~] $

After reboot, the corresponding log file cannot be found and the error is reported directly.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 562040400 bytes

Database Buffers 281018368 bytes

Redo Buffers 5132288 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 5059

Session ID: 1 Serial number: 5

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file / u01/diag/rdbms/wilson/wilson/trace/wilson_ora_5059.trc:

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

ORA-00312: onlinelog 1 thread 1:'/ u01 thread oradata Universe WILSON Onlinelog Onlinelog. log'

ORA-00312: onlinelog 1 thread 1:'/ u01 Universe flashbacks recover WILSONAccording to WILSONAccording to onlinelog Universe O1cm MFN 1x 870jljvk.log'

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

Instance terminated by USER, pid = 5059

Reboot to the mount state for processing.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup mount

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 562040400 bytes

Database Buffers 281018368 bytes

Redo Buffers 5132288 bytes

Database mounted.

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

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

GROUP# STATUS ARC SEQUENCE#

-

1 INACTIVE YES 108

3 INACTIVE YES 109

2 CURRENT NO 110

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

CHECKPOINT_CHANGE# LAST_CHANGE#

--

1950243 1950243

1950243 1950243

1950243 1950243

1950243 1950243

1950243 1950243

1950243 1950243

1950243 1950243

7 rows selected.

SQL > select checkpoint_change# from v$datafile_header

CHECKPOINT_CHANGE#

-

1950243

1950243

1950243

1950243

1950243

1950243

1950243

7 rows selected.

SQL >

Although the startup failed, we did not see a serious consistency problem. We try to start the database.

SQL > alter database clear logfile group 1

Database altered.

SQL > alter database open

Database altered.

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

GROUP# STATUS ARCHIVED SEQUENCE#

--

1 CURRENT NO 111

2 INACTIVE YES 110

3 INACTIVE YES 109

This kind of scene is easier to deal with. Let's discuss what if the log of Active status is deleted.

5. Inconsistency closes non-current log group processing-- Active log group

We construct the Active log group object.

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

GROUP# STATUS MEMBER

-

3 / u01/oradata/WILSON/onlinelog/o1_mf_3_7xt456o0_.log

3 / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_7xt45bvp_.log

2 / u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log

2 / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

1/ u01/oradata/WILSON/onlinelog/o1_mf_1_870kg322_.log

1/ u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870kg3mr_.log

6 rows selected

SQL > alter system switch logfile

System altered

SQL > alter system switch logfile

System altered

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

GROUP# STATUS ARCHIVED SEQUENCE#

--

1 CURRENT NO 114

2 ACTIVE YES 113

3 INACTIVE YES 112

After that, the database is forced to close and the corresponding log group 2 is deleted.

SQL > shutdown abort

ORACLE instance shut down.

SQL >

[oracle@bspdev trace] $mv / u01/oradata/WILSON/onlinelog/o1_mf_2_7xt44w3k_.log / u01/oradata/WILSON/onlinelog/redogroup2.log

[oracle@bspdev trace] $rm / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_7xt450rv_.log

Restart the database.

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 562040400 bytes

Database Buffers 281018368 bytes

Redo Buffers 5132288 bytes

Database mounted.

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

ORA-00312: online log 2 thread 1:

'/ u01According to flashworthy recoveryWILSONAccording to WILSONAccording to onlinelogAccording to' / u01According to flashworthy recoveryWILSONAccording to onlinelogAccording to'/ u01According to flashworthy recoveryWILSONAccording to onlinelog'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1:

'/ u01aporadataPlus WILSONOnlinelogUniplex o1millimfand2y7xt44w3kyog.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

Check the file status.

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

CHECKPOINT_CHANGE# LAST_CHANGE#

--

1970578

1970578

1970578

1970578

1970578

1970578

1970578

7 rows selected.

SQL > select checkpoint_change# from v$datafile_header

CHECKPOINT_CHANGE#

-

1970578

1970578

1970578

1970578

1970578

1970578

1970578

7 rows selected.

From the case of v$datafile, we can see that the shutdown is an incomplete shutdown. We can only try to recover.

SQL > recover database

ORA-00279: change 1970578 generated at 10/06/2012 22:59:02 needed for thread 1

ORA-00289: suggestion:

/ u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

ORA-00280: change 1970578 for thread 1 is in sequence # 113

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

Cancel

Media recovery cancelled.

It is proposed to restore logs that use sequence#=113, but this is exactly the object that was deleted. Therefore, the second choice is to use incomplete recovery.

SQL > recover database until cancel

ORA-00279: change 1970578 generated at 10/06/2012 22:59:02 needed for thread 1

ORA-00289: suggestion:

/ u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

ORA-00280: change 1970578 for thread 1 is in sequence # 113

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

Auto

ORA-00279: change 1970779 generated at 10/06/2012 23:00:47 needed for thread 1

ORA-00289: suggestion:

/ u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_114_%u_.arc

ORA-00280: change 1970779 for thread 1 is in sequence # 114

ORA-00278: log file

'/ u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_113_870kwzkl_.arc

'no longer needed for this recovery

ORA-00308: cannot open archived log

'/ u01According to flashworthy recoveryrecovery area, WILSONAccording to ArchivelogAccording to 2012' 10'06 Universe'o1 'flashworthiness 114% upright .arc'

ORA-27037: unable to obtain file status

Linux 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:

'/ u01aporadataPlacement WILSONUnixxt3yzhjob.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:

'/ u01aporadataPlacement WILSONUnixxt3yzhjob.dbf'

Try to open it in RMAN SCN mode.

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

GROUP# SEQUENCE# FIRST_CHANGE#

1 114 1970779

3 112 1950574

2 113 1970577

RMAN > recover database until sequence 112

Starting recover at 06-OCT-12

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=18 device type=DISK

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of recover command at 10/06/2012 23:19:42

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

The date of the System file is beyond the scope of recover. You can only attempt to restore with RMAN backups.

RMAN > restore database

Starting restore at 06-OCT-12

Using channel ORA_DISK_1

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/oradata/WILSON/datafile/o1_mf_system_7xt3yzhj_.dbf

Channel ORA_DISK_1: restoring datafile 00002 to / u01/oradata/WILSON/datafile/o1_mf_sysaux_7xt3yzkb_.dbf

Channel ORA_DISK_1: restoring datafile 00003 to / u01/oradata/WILSON/datafile/o1_mf_undotbs1_7xt3yzl5_.dbf

Channel ORA_DISK_1: restoring datafile 00004 to / u01/oradata/WILSON/datafile/o1_mf_users_805nxydh_.dbf

Channel ORA_DISK_1: restoring datafile 00005 to / u01/oradata/WILSON/datafile/o1_mf_example_7xt46m9x_.dbf

Channel ORA_DISK_1: restoring datafile 00006 to / u01/oradata/WILSON/datafile/o1_mf_nbscommo_820frtg1_.dbf

Channel ORA_DISK_1: restoring datafile 00007 to / u01/oradata/WILSON/datafile/o1_mf_nbscommo_820ft5y5_.dbf

Channel ORA_DISK_1: reading from backup piece / u01/flash_recovery_area/WILSON/backupset/2012_10_06/o1_mf_nnndf_TAG20121006T220912_870gwjoy_.bkp

Channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/WILSON/backupset/2012_10_06/o1_mf_nnndf_TAG20121006T220912_870gwjoy_.bkp tag=TAG20121006T220912

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:02:46

Finished restore at 06-OCT-12

-- partial recovery

RMAN > recover database until sequence 112

Starting recover at 06-OCT-12

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 106 is already on disk as file / u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_106_870gzbfg_.arc

Archived log for thread 1 with sequence 107 is already on disk as file / u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_107_870jlysq_.arc

Archived log for thread 1 with sequence 108 is already on disk as file / u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_108_870jo98m_.arc

Archived log for thread 1 with sequence 109 is already on disk as file / u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_109_870jx2gj_.arc

Archived log for thread 1 with sequence 110 is already on disk as file / u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_110_870kgmdp_.arc

Archived log for thread 1 with sequence 111 is already on disk as file / u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_111_870knhwb_.arc

Archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_106_870gzbfg_.arc thread=1 sequence=106

Archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_107_870jlysq_.arc thread=1 sequence=107

Archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_108_870jo98m_.arc thread=1 sequence=108

Archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_109_870jx2gj_.arc thread=1 sequence=109

Archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_110_870kgmdp_.arc thread=1 sequence=110

Archived log file name=/u01/flash_recovery_area/WILSON/archivelog/2012_10_06/o1_mf_1_111_870knhwb_.arc thread=1 sequence=111

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

Finished recover at 06-OCT-12

Obviously, thanks to archiving mode and backup collections. We seem to have successfully completed the recover process. Let's try to open the database and note that incomplete has been restored here, and we have to bear this part of the data loss.

SQL > conn / as sysdba

Connected.

SQL > alter database open resetlogs

Database altered.

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

GROUP# STATUS MEMBER

-

3 / u01/oradata/WILSON/onlinelog/o1_mf_3_870n4lsg_.log

3 / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_3_870n4o31_.log

2 / u01/oradata/WILSON/onlinelog/o1_mf_2_870n48hc_.log

2 / u01/flash_recovery_area/WILSON/onlinelog/o1_mf_2_870n4dtl_.log

1/ u01/oradata/WILSON/onlinelog/o1_mf_1_870n42n1_.log

1/ u01/flash_recovery_area/WILSON/onlinelog/o1_mf_1_870n44z3_.log

6 rows selected

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

GROUP# STATUS ARCHIVED SEQUENCE#

--

1 CURRENT NO 1

2 UNUSED YES 0

3 UNUSED YES 0

Summary: when incomplete shuts down the database and deletes the members of the Active status log group, we have to bear the corresponding data loss, and some of the data may be lost as a result. The author's later processing is to use a backup collection of the previous rman to perform a non-complete recovery to the last log filegroup that can be found. Here, we re-emphasize the importance of archiving mode and backup.

If we delete a current log member, it is also very complicated to deal with things without being completely closed. In the next section, we choose another way to deal with it.

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.