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