Oracle的日志挖掘

日志挖掘（LogMnr）

①在参数文件中设置utl_file_dir路径

②重启

③创建directory file 使用execute dbms_logmnr_d build()

④添加日志文件 使用dbms_logmnr add_logfile

或删除日志文件 使用dbms_logmnr removefile

⑤启动logmnr 使用dbms_logmnr start_logmnr

⑥查询 使用V$logmnr_contents（sqlredo/sqlundo）

设置utl_file_dir目录

alter system set utl_file_dir= D:\app\Leo\utl_file_dir scope=spfile

建立日志挖掘的数据字典

execute dbms_logmnr_d build( shwdict ora D:\app\Leo\utl_file_dir )//第二个参数要与参数utl_file_dir的设置完全匹配

无此包 则需执行@d:\oracle\ora \rdbms\admin\dbmslmd sql

创建分析的文件列表（可以是在线或归档日志）

execute dbms_logmnr add_logfile( D:\app\Leo\archivelog \ARC _ dbms_logmnr new)

execute dbms_logmnr add_logfile( D:\app\Leo\archivelog \ARC _ dbms_logmnr addfile)

execute dbms_logmnr add_logfile( D:\app\Leo\archivelog \ARC _ dbms_logmnr addfile)

option new 创建一个新的日志列表 addfile 向列表中添加日志文件

remove_logfile 删除

在视图v$logmnr_logs中可以看到添加的日志列表

select log_id filename from v$logmnr_logs

开始分析

execute dbms_logmnr start_logmnr(dictfilename=>D:\app\Leo\utl_file_dir\shwdict ora )

execute dbms_logmnr start_logmnr(dictfilename=>d:\oracle\shwdict ora startscn=>xxxxxx endscn=>xxxxx starttime=>to_date( : : yyyymmdd hh :mi:ss ) endtime=>to_date( : : yyyymmdd hh :mi:ss ))

没有建立数据字典 使用dbms_logmnr dict_from_online_catalog选项参数 则logminer期望在所指定的dbms_logmnr add_logfile或重做日志中找到数据字典

exec sys dbms_logmnr start_logmnr(options=>sys dbms_logmnr dict_from_online_catalog)

查询分析内容

select to_char(timestamp yyyy mm dd hh :mi:ss ) time sql_redo from v$logmnr_contents where seg_owner= SCOTT

结束分析

execute dbms_logmnr end_logmnr

部分日志归档不出的解决

①将数据库至于force logging模式

alter database force logging

select force_logging from v$database

②查看表是否处于归档模式

select table_name logging from user_tables

③查看数据库是否处于启用了补充日志（启动了 则每条update写redo时会附加列值唯一信息）

SQL>select supplemental_log_data_pk supplemental_log_data_ui from v$database

SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI

NO NO

SQL>alter database add supplemental log data (primary key unique index) columns

Database altered

SQL>select supplemental_log_data_pk supplemental_log_data_ui from v$database

SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI

lishixinzhi/Article/program/Oracle/201311/17087

5.1 创建数据文件大小20M的表空间

>CREATE TABLESPACE usertbs1 datafile

'D:\tmp\usertbs1.DBF' SIZE 20M

5.2 区定制分配

>CREATE TABLESPACE usertbs1 datafile

'D:\tmp\usertbs1.DBF' SIZE 20M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

5.3 每次自动扩展1M，最大值为100MB

>ALTER DATABASE DATAFILE

'D:\tmp\usertbs1.DBF'

AUTOEXTEND ON NEXT 1M MAXSIZE 100M

5.4 创建临时表空间

>CREATE TEMPORARY TABLESPACE temptbs TEMPFILE

'D:\tmp\temptbs.DBF' SIZE 20M

EXTEND MANAGEMENT LOCAL UNIFORM SIZE 10M

/*设置当前默认表空间*/

>ALTER DATABASE DEFAULT TABLESPACE temptbs

5.5 对表空间联机脱机

>ALTER TABLESPACE usertbs1 OFFLINE

>ALTER TABLESPACE usertbs1 ONLINE

5.9 为表空间添加数据文件

>ALTER TABLESPACE usertbs1 ADD DATAFILE

'D:\tmp\USERS05.DBF' SIZE 50M

10题与9题一样

5.12 数据文件更名

>ALTER TABLESPACE usertbs1 OFFLINE

手动改数据文件名为 USERS005.DBF

>ALTER TABLESPACE usertbs1 RENAME DATAFILE

'D:\tmp\USERS05.DBF' TO

'D:\tmp\USERS005.DBF'

>ALTER TABLESPACE usertbs1 ONLINE

5.14 添加重做日志组，包含redo5a.log和redo5b.log,大小为5MB

>ALTER DATABASE ADD LOGFILE GROUP 4

('D:\tmp\redo5a.log', 'D:\tmp\redo5b.log')

SIZE 5M

5.15 添加日志文件成员

>ALTER DATABASE ADD LOGFILE MEMBER

'D:\tmp\redo5c.log' TO GROUP 4

5.16 切换归档模式且自动归档

>SHUTDOWN IMMEDIATE

>STARTUP MOUNT

>ALTER DATABASE ARCHIVELOG

>ALTER DATABASE OPEN

5.17 设置数据库归档路径

>ALTER SYSTEM SET db_recovery_file_dest="D:\ORACLE\BACKUP"

6.2 建表

>CREATE TABLE exer_class(

CNO NUMBER(2) PRIMARY KEY,

CNAME VARCHAR(20),

NUM NUMBER(3)

)

>CREATE TABLE exer_studnet(

SNO NUMBER(4) PRIMARY KEY,

SNAME VARCHAR(10) UNIQUE,

SAGE NUMBER,

SEX CHAR(2),

CNO NUMBER(2)

)

6.3 添加检查约束

>ALTER TABLE exer_studnet ADD

CONSTRAINT S_CK CHECK (SAGE BETWEEN 0 AND 100)

6.4 取值为M或F，默认M

>ALTER TABLE exer_studnet ADD

CONSTRAINT SEX_CK CHECK(SEX IN ('M','F'))

>ALTER TABLE exer_studnet MODIFY SEX DEFAULT 'M'

6.5 建立唯一索引

>CREATE UNIQUE INDEX class_name_index ON exer_class(CNAME)

6.6 创建视图

>CREATE VIEW cl_stu_view as

SELECT c.CNO, c.CNAME, s.SNO, s.SNAME FROM exer_class c, exer_studnet s

WHERE s.CNO=c.CNO

6.7 创建序列

>CREATE SEQUENCE no_seq START WITH 100000001 INCREMENT BY 1 NOCACHE NOCYCLE

6.8 表格分区

>CREATE TABLESPACE EXAMPLE DATAFILE

'D:\tmp\EXAMPLE.DBF' SIZE 50M

>CREATE TABLESPACE ORCLTBS1 DATAFILE

'D:\tmp\ORCLTBS1.DBF' SIZE 50M

>CREATE TABLESPACE ORCLTBS2 DATAFILE

'D:\tmp\ORCLTBS2.DBF' SIZE 50M

>CREATE TABLE exer_student_range(

SNO NUMBER(4) PRIMARY KEY,

SNAME VARCHAR(10) UNIQUE,

SAGE NUMBER,

SEX CHAR(2),

CNO NUMBER(2)

)

PARTITION BY RANGE(SAGE)(

PARTITION P1 VALUES LESS THAN (20) TABLESPACE EXAMPLE,

PARTITION P2 VALUES LESS THAN (30) TABLESPACE ORCLTBS1,

PARTITION P3 VALUES LESS THAN (MAXVALUE) TABLESPACE ORCLTBS2

)

6.9 按性别分区

>CREATE TABLE exer_student_list(

SNO NUMBER(4) PRIMARY KEY,

SNAME VARCHAR(10) UNIQUE,

SAGE NUMBER,

SEX CHAR(2),

CNO NUMBER(2)

)

PARTITION BY LIST(SEX)(

PARTITION male VALUES('M') TABLESPACE ORCLTBS1,

PARTITION female VALUES('F') TABLESPACE ORCLTBS2

)

6.10 本地分区索引

>CREATE INDEX student_local ON exer_student_range(SAGE) LOCAL

1.查看当前日志组成员

SQL>select member from v$logfile

MEMBER

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

/u01/oracle/oradata/orcl/redo03.log

/u01/oracle/oradata/orcl/redo02.log

/u01/oracle/oradata/orcl/redo01.log

2. 查看当前日志组状态：

SQL>select group#,members,bytes/1024/1024,status from v$log

GROUP# MEMBERS BYTES/1024/1024 STATUS

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

1 1 50 ACTIVE

2 1 50 CURRENT

3 1 50 INACTIVE

现在有三个日志成员，大小为50M，欲更改为100M

增加日志组

SQL>alter database add logfile group 4 ('/u01/oracle/oradata/orcl/redo04.log') size 100M

SQL>alter database add logfile group 5 ('/u01/oracle/oradata/orcl/redo05.log') size 100M

SQL>alter database add logfile group 6 ('/u01/oracle/oradata/orcl/redo06.log') size 100M

3.切换到新增的日志组上

SQL>alter system switch logfile

System altered.

SQL>alter system switch logfile

System altered.

SQL>select group#,members,bytes/1024/1024,status from v$log

GROUP# MEMBERS BYTES/1024/1024 STATUS

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

1 1 50 INACTIVE

2 1 50 INACTIVE

3 1 50 ACTIVE

4 1 100 CURRENT

5 1 100 UNUSED

6 1 100 UNUSED

a. CURRENT指当前的日志文件，在进行实例恢复时是必须的；

b. ACTIVE是指活动的非当前日志，在进行实例恢复时会被用到。Active状态意味着，Checkpoint尚未完成，因此该日志文件不能被覆盖。

c. INACTIVE是非活动日志，在实例恢复时不再需要，但在介质恢复时可能需要。

d. UNUSED表示该日志从未被写入，可能是刚添加的，或RESETLOGS后被重置。

4.删除旧的日志组

SQL>alter database drop logfile group 1

Database altered.

SQL>alter database drop logfile group 2

2 /

Database altered.

SQL>alter database drop logfile group 3

alter database drop logfile group 3

*

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance dbserver (thread 1)

ORA-00312: online log 3 thread 1: '/u01/oracle/oradata/orcl/redo03.log'

由于log 3 日志成员还出去active 状态，所以不能drop掉的，再次执行 alter system switch logfile

SQL>select group#,members,bytes/1024/1024,status from v$log

GROUP# MEMBERS BYTES/1024/1024 STATUS

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

3 1 50 INACTIVE

4 1 100 ACTIVE

5 1 100 CURRENT

6 1 100 UNUSED

SQL>alter database drop logfile group 3

Database altered.

SQL>select group#,members,bytes/1024/1024,status from v$log

GROUP# MEMBERS BYTES/1024/1024 STATUS

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

4 1 100 ACTIVE

5 1 100 ACTIVE

6 1 100 CURRENT

在 *** 作系统下删除掉redolog 日志文件

mv /u01/oracle/oradata/orcl/redo0[1-3].log /tmp

---