第五章 Redo Log Files(2005.6.9)

来源：互联网发布：linux系统开发教程编辑：程序博客网时间：2024/06/05 10:41

2005-6-8

Basic Dynamic Performance View

Dynamic Performance View

(accessible in the NOMOUNT stage or above)

Description

V$PARAMETER

Contains information about the initialization parameter

V$SGA

Contains summary information on the SGA

V$OPTION

Lists options that are installed with the Oracle server

V$PROCESS

Contains information about the currently active process

V$SESSION

Lists current session information

V$VERSION

Lists the version number and the components

Dynamic Performance View

( accessible in the MOUNT stage or above)

Description

V$THREAD

Contains thread information, for example about the redo log groups

V$CONTROLFILE

Lists the names of the control files (Even though available, this view returns no rows in NOMOUNT state.)

V$DATABASE

Contains database information

V$DATAFILE

Contains data file information from the control file

V$INSTANCE

Displays the state of the current instance

V$LOGFILE

Contains information about the online redo log files

How to terminate a user session

Example : To kill scott’s session

SQLPLUS> SELECT sid, serial# FROM v$session WHERE

2> username=‘SCOTT’;

SID SERIAL#

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

7 15

SQLPLUS> ALTER SYSTEM KILL SESSION ‘7,15’;

5. Maintaining Redo Log Files

Features and Contents of Redo Log Files

· 用来存放事务历史

· The redo log files are only used for recovery.

· 同一组中两成员内容相同

· 进程 LGWR 在一个组中的所有在线重做日志文件中写入相同内容.

· The Oracle数据库最少要两个在线重做日志组来维持正常操作

· 每个组中的成员具有相同的日志序列号和文件大小. 当前的日志序列号是存储在控制文件和所有数据文件的头中。Views about Redo Log Files

SQLPLUS> SELECT group#,sequence#,bytes,members,status

2> FROM v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS

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

1 688 1048576 1 CURRENT

2 689 1048576 1 INACTIVE

2 rows selected.

SQLPLUS> SELECT *

2> FROM v$logfile;

GROUP# STATUS MEMBER

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

1 /DISK3/log1a.rdo

2 /DISK4/log2a.rdo

2 rows selected.

Maintenance Operations on Redo Log files

Example 1 : Adding Online Redo Log Groups

SQLPLUS> ALTER DATABASE ADD LOGFILE

2> (‘/DISK3/log3a.rdo’) size 1M;

Example 2 : Adding Online Redo Log Members

SQLPLUS> ALTER DATABASE ADD LOGFILE MEMBER

2> ‘/DISK4/log1b.rdo’ TO GROUP 1,

3> ‘/DISK4/log2b.rdo’ TO GROUP 2;

Example 3 : Dropping Online Redo Log Groups

SQLPLUS> ALTER DATABASE DROP LOGFILE GROUP 3;

Example 4 : Dropping Online Redo Log Members

SQLPLUS> ALTER DATABASE DROP LOGFILE MEMBER

2> ‘/DISK4/log2b.dbf’;

maintaining redo log files

forcing log switches and checkpoints

log switches : alter system switch logfile;

checkpoints : alter system checkpoint;

设置FAST_START_MTTR_TARGET 参数（MTTR=mean time to recovery)

checkpoints用来同步，同步频率越高，发生系统错误时所需RECOVERY时间越短

同步相关的参数：fast_start_io_target(8i引入）

fast_start_mttr_target(9i引入)建议使用(数据库内部通过它改变其它三个参数，精度更高）

log_checkpoint_interval

log_checkpoint_timeout

adding online redo log groups

select * from v$log;(显示有几个组）

添加日志组：alter database add logfile group 3

('d:/oracle/oradata/ora9i/redo03a.ora','d:/oracle/oradata/ora9i/redo03b.ora')

size 10m;

添加日志成员：alter database add logfile member

'd:/oracle/oradata/ora9i/redo06c.ora' to group 3

droping online redo log groups

删除日志组: alter database drop logfile group 3;(只删除控制文件中的信息）

restricted(限制）----current log group

active log group

not archived log group

删除日志成员：alter database drop logfile member 'd:/oracle/oradata/ora9i/rddo04a.ora'

restricted(限制）----at least one member per group

clearing ,relocating or renaming online redo log file

clear logfile相当于reinit log file

alter database clear logfile 'd:/oracle/oradata/ora9i/log2a.rdo';

alter database clear unarchived logfile group 5;(清空没有归档的日志文件，实际备份产生断点）

（1），在操作系统中把文件改名或移动

（2），alter database rename file 'd:/oracle/oradata/ora9i/log2a.rdo' to 'd:/oracle/oradata/ora9i/log2b.rdo';

online redo log configuration

重作日志实现多工，有多个组，组成员在不同DISK上，建议成员文件大小一致，成员数一致

通过OMF管理REDO LOG文件

SHOW PARAEMTERS DB_CREATE_ONLINE

ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='D:/ORACLE/ORALOG';

ALTER DATABASE ADD LOGFILE GROUP 6; (名子由ORACLE派生，大小100M）

ALTER DATABASE DROP LOGFILE GROUP 6；（自己删除）

obtaining group and member information

v$log ----日志组信息（log group）

status---current/active/inactive/unused

v$logfile----日志成员信息（log member）

status---blank –in use

stale –imcomplete

日志分析==日志挖掘---logmnr

1) set log directory utl_file_dir(init.ora文件中)

2) restartup

desc dbms_logmnr_d.build; (重建标准数据包 start d:/oracle/ora90/rdbms/admin/catproc.sql) create directory file dbms_logmnr_d.build