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

The use of ALTER DATABASE CLEAR UNARCHIVED LOGFILE

Before moving on to today's topic, let's discuss two fields in a v$log dynamic performance view:

We need to pay attention to two fields in the figure above: STATUS and ARCHIVED.

STATUS has three states: when log switching occurs, CURRENT,ACTIVE and INACTIVE; will trigger the database to execute CHECKPOINT,CHECKPOINT once and trigger the DBWn process to write dirty data in memory to the hard disk. Once the write is completed, the STATUS of the previous log group will become INACTIVE, indicating that if a downtime occurs at this time, you no longer need to change the log of the log group for instance recovery. On the contrary, you need to use the log of the log group to complete the instance recovery. In the actual work, it is found that for a "not busy" database, the status of the log group ACTIVE may last for several hours, and if there is a power outage or abnormal shutdown at this time, the data of the two log groups may be lost; from this point, for the sake of data security, log group members should not be set too large, there should be a "suitable" size.

ARCHIVED has two states: YES,NO;YES indicates that the ARCH process has copied the Online Redo LogFile of the log group to the archive destination, and becoming an Archived Redo Logfile,NO indicates that the copy has not been completed. There is no inevitable relationship between the status of the log group and whether the log group is archived or not, they are controlled by different processes. In the event of a failure and manual recovery, we should pay attention to observe the latest log group status and whether it is archived, which may help us in the recovery process. In addition, the data of V$LOG comes from control files, so the recovered control files or rebuilt control files cannot describe the latest log group status of the database.

Here is a simple example to discuss the use of ALTER DATABASE CLEAR UNARCHIVED LOGFILE:

C:\ Users\ LIUBINGLIN > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Friday January 4 23:22:26 2013

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

Connect to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

SQL > select status from v$instance

STATUS

-

OPEN

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 267825152 bytes

Fixed Size 1384044 bytes

Variable Size 109052308 bytes

Database Buffers 150994944 bytes

Redo Buffers 6393856 bytes

The database is loaded.

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

GROUP# THREAD# SEQUENCE# STATUS

--

41 41 CURRENT

3 1 0 UNUSED

SQL > alter database clear unarchived logfile group 4

The database has changed.

SQL > alter database open

The database has changed.

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

GROUP# THREAD# SEQUENCE# STATUS

--

3 1 42 CURRENT

4 1 0 UNUSED

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 267825152 bytes

Fixed Size 1384044 bytes

Variable Size 109052308 bytes

Database Buffers 150994944 bytes

Redo Buffers 6393856 bytes

The database is loaded.

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

GROUP# THREAD# SEQUENCE# STATUS

--

4 1 0 UNUSED

3 1 42 CURRENT

SQL > alter database clear unarchived logfile group 3

Alter database clear unarchived logfile group 3

*

An error occurred on line 1:

ORA-01624: log 3 is required for emergency recovery of instance orcl (thread 1)

ORA-00312: online log 3 thread 1:'F:\ APP\ ORACLE\ ORADATA\ ORCL2\ REDO03.LOG'

SQL > alter database open

The database has changed.

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

GROUP# THREAD# SEQUENCE# STATUS

--

3 1 42 INACTIVE

4 1 43 CURRENT

SQL > alter system switch logfile

The system has changed.

SQL > shutdown abort

The ORACLE routine has been closed.

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 267825152 bytes

Fixed Size 1384044 bytes

Variable Size 109052308 bytes

Database Buffers 150994944 bytes

Redo Buffers 6393856 bytes

The database is loaded.

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

GROUP# THREAD# SEQUENCE# STATUS

--

4 1 43 ACTIVE

3 1 44 CURRENT

SQL > alter database clear unarchived logfile group 4

Alter database clear unarchived logfile group 4

*

An error occurred on line 1:

ORA-01624: log 4 is required for emergency recovery of instance orcl (thread 1)

ORA-00312: online log 4 thread 1:'F:\ APP\ ORACLE\ ORADATA\ ORCL2\ REDO04.LOG'

Summary: ALTER DATABASE CLEAR UNARCHIVED LOGFILE can only be used to clear log groups with CURRENT status under normal shutdown.

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.