oracle cktp,第七章.维护重做日志文件

1.一个Oracle数据库必须至少有两个重做日志文件,并且分别属于两个不同的重做日志文件组。

即：每个重做日志组必须包含最少一个重做日志文件。

2.一个Oracle数据库必须至少有两个重做日志文件组。

3. How Redo Files Work

a.Online Redo log files are usred in a cyclic fashion.

b.When a online redo log file is full, LGWR will move to the next log group.

---- Called a log switch

The act of switching from one log file group to the other is called a log switch

---- Checkpoint operation also accours

A checkpoint is the writing of dirty (modified) blocks from buffer cache to disk.

由DBWn进程执行操作。

---- Information written to the control file

CKTP由检查点进程把信息写入控制文件中。

4. LWGR进程

Oracle服务器将对数据库所做的所有更改按顺序记录到重做日志缓冲区中。LGWR进程把重做条目从重做日志缓冲区写入联机重做日志组的其中一个组，这个组叫做当前联机重做日志组。

LGWR进程将在以下情况下写入：

a.当提交事务处理时

b.当重做日志缓冲区被写满三分之一时

c.当重做日志缓冲区内的已更改记录超过1M时

d.在DBWn将数据库缓冲区高速缓存中修改的块写入数据文件之前

1.先写联机重做日志，再写数据

2.查询重做日志组，日志成员及其状态

SQL> desc v$log;

NameNull?Type

----------------------------------------- -------- ---------------------------- GROUP#NUMBER

THREAD#NUMBER

SEQUENCE#NUMBER

BYTESNUMBER

MEMBERSNUMBER

ARCHIVEDVARCHAR2(3)

STATUSVARCHAR2(16)

FIRST_CHANGE#NUMBER

FIRST_TIME

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

GROUP#THREAD#SEQUENCE# STATUS

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

1123 CURRENT

2121 INACTIVE

3122 INACTIVE

3.强制执行logswitch

SQL> alter system switch logfile;

System altered.

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

GROUP#THREAD#SEQUENCE# STATUS

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

1123 ACTIVE

2124 CURRENT

3 122 INACTIVE

SQL> alter system switch logfile;

System altered.

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

GROUP#THREAD#SEQUENCE# STATUS

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

1123 ACTIVE

2124 ACTIVE

3125 CURRENT

4.Checkpoints can be forced by:

A checkpoint is the writing of dirty(modified) blocks from the buffer cache to disk

然后由DBWn进程执行

A logswitch会触发A checkpoint accour.

例如：fast_start_mttr_target=600

SQL>show parameter fast

SQL>alter system set fast_start_mttr_target=600 scope=both;

5.手动执行checkpoint

SQL>alter system checkpoint;

6.Adding Redo File Groups

SQL>desc v$logfile;

SQL>col member format a50

SQL>select * from v$logfile;

SQL>alter database add logfile group 4

2('/u01/oradata/wilson/redo04.log') size 50M;

7.Adding Redo File Members

SQL> alter database add logfile group 4

2'/u01/oradata/wilson/redo04a.log' to group 4;

SQL>select * from v$logfile;

SQL>!

[oracle node1@wilson wilson]$ls

[oracle node1@wilson wilson]$exit

SQL>

8.Dropping Redo File Members

SQL>select * from v$logfile;

SQL>alter database drop logfile member '/u01/oradata/wilson/redo04a.log';

SQL>select * from v$logfile;

9. Dropping Redo Files Groups

SQL>clear scr清屏

SQL>desc v$log;

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

SQL>alter database drop logfile group 3;

SQL>

10.Relocate & Rename

Relocate or rename online redo log files in one of the two following ways:

--- ALTER DATABASE RENAME FILE COMMAND

a.shut down the database.

b.Copy the online redo log files to the new location.

c.Place the database in MOUNT mode.

d.Execute the command.

e.Open database fro normal operation.

ALTER DATABASE RENAME FILE

‘$HOME/ORADATA/u01/log2a.rdo’ to

‘/$HOME/ORADATA/u02/log1c.rdo’;

--- Add new members and drop old members.

For example:

11.Clear redo log files

---ALTER DATABASE CLEAR LOGFILE command can be used to reinitialize an online redo log fie.

Alter database clear logfile group 2;

---Use the UNARCHIVED keyword to avoid archiving the corrupted online redo log file.

Alter database clear unarchived logfile group 2;

12.Redo File Configuration

13.Manage Redo Files with OMF

14.Group $ Member information

v$logDisplay the redo log file information from the control file.

v$logfileIdentifies redo log groups and members and member status.

v$log_historyContains log history information.

15.What is Redo Threads?

In the RAC system, each instance has to have its own redo log groups. The redo log file groups of an instancd are collectively called a ‘thread’, or more appropriately, a ‘redo log thread’. Each instance has its own redo thread. The redo log groups function in a true circular fashion; as one fills up, another redo log records the redo entries. In a stand-alone instance, there is only one thread. In a RAC system, typically you have as many threads sa instances. The thread number identifies each thread. The threads mah have diffirent members of redo groups, but each group must have two members.

16.Archived Redo Log Files

查看数据库是否处于归档模式中.

[root@test ~]# su - oracle

[oracle@test ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 8 05:43:56 2008

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

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area285212672 bytes

Fixed Size1218992 bytes

Variable Size71304784 bytes

Database Buffers209715200 bytes

Redo Buffers2973696 bytes

Database mounted.

Database opened.

SQL> desc v$database;

NameNull?Type

----------------------------------------- -------- ---------------------------- DBIDNUMBER

NAMEVARCHAR2(9)

CREATEDDATE

RESETLOGS_CHANGE#NUMBER

RESETLOGS_TIMEDATE

PRIOR_RESETLOGS_CHANGE#NUMBER

PRIOR_RESETLOGS_TIMEDATE

LOG_MODEVARCHAR2(12)

CHECKPOINT_CHANGE#NUMBER

ARCHIVE_CHANGE#NUMBER

CONTROLFILE_TYPEVARCHAR2(7)

CONTROLFILE_CREATEDDATE

CONTROLFILE_SEQUENCE#NUMBER

CONTROLFILE_CHANGE#NUMBER

CONTROLFILE_TIMEDATE

OPEN_RESETLOGSVARCHAR2(11)

VERSION_TIMEDATE

OPEN_MODEVARCHAR2(10)

PROTECTION_MODEVARCHAR2(20)

PROTECTION_LEVELVARCHAR2(20)

REMOTE_ARCHIVEVARCHAR2(8)

ACTIVATION#NUMBER

SWITCHOVER#NUMBER

DATABASE_ROLEVARCHAR2(16)

ARCHIVELOG_CHANGE#NUMBER

ARCHIVELOG_COMPRESSIONVARCHAR2(8)

SWITCHOVER_STATUSVARCHAR2(20)

DATAGUARD_BROKERVARCHAR2(8)

GUARD_STATUSVARCHAR2(7)

SUPPLEMENTAL_LOG_DATA_MINVARCHAR2(8)

SUPPLEMENTAL_LOG_DATA_PKVARCHAR2(3)

SUPPLEMENTAL_LOG_DATA_UIVARCHAR2(3)

FORCE_LOGGINGVARCHAR2(3)

PLATFORM_IDNUMBER

PLATFORM_NAMEVARCHAR2(101)

RECOVERY_TARGET_INCARNATION#NUMBER

LAST_OPEN_INCARNATION#NUMBER

CURRENT_SCN NUMBER

FLASHBACK_ONVARCHAR2(18)

SUPPLEMENTAL_LOG_DATA_FKVARCHAR2(3)

SUPPLEMENTAL_LOG_DATA_ALLVARCHAR2(3)

DB_UNIQUE_NAMEVARCHAR2(30)

STANDBY_BECAME_PRIMARY_SCNNUMBER

FS_FAILOVER_STATUSVARCHAR2(21)

FS_FAILOVER_CURRENT_TARGETVARCHAR2(30)

FS_FAILOVER_THRESHOLDNUMBER

FS_FAILOVER_OBSERVER_PRESENTVARCHAR2(7)

FS_FAILOVER_OBSERVER_HOSTVARCHAR2(512)

SQL> select log_mode fromv$database;

LOG_MODE

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

NOARCHIVELOG

SQL> select log_mode fromv$database;

LOG_MODE

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

NOARCHIVELOG

SQL> desc v$instance;

NameNull?Type

----------------------------------------- -------- ---------------------------- INSTANCE_NUMBERNUMBER

INSTANCE_NAMEVARCHAR2(16)

HOST_NAMEVARCHAR2(64)

VERSIONVARCHAR2(17)

STARTUP_TIMEDATE

STATUSVARCHAR2(12)

PARALLELVARCHAR2(3)

THREAD#NUMBER

ARCHIVERVARCHAR2(7)

LOG_SWITCH_WAITVARCHAR2(15)

LOGINSVARCHAR2(10)

SHUTDOWN_PENDINGVARCHAR2(3)

DATABASE_STATUSVARCHAR2(17)

INSTANCE_ROLEVARCHAR2(18)

ACTIVE_STATEVARCHAR2(9)

BLOCKEDVARCHAR2(3)

SQL> select archiver from v$instance;

ARCHIVE

-------

STOPPED

17. Archived Redo Log Files