Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2885559
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2010-09-26 14:16:02

11G的数据库重放功能,可以捕获某一个数据库的活动,并记录在二进制文件中,
然后将文件复制到测试数据库,进行数据库活动的重新操作。  流程如下:

1、启动捕获进程
2、记录数据库活动到二进制文件中
3、关闭捕获流程,并将二进制文件复制到测试数据库
4、启动重放进程
5、进行数据库活动重演

其中捕获过程通过DBMS_WORKLOAD_CAPTURE包来实现,重放过程通过DBMS_WORKLOAD_REPLAY来实现。
DBMS_WORKLOAD_CAPTURE包可以参考下面链接:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_workload_capture.htm#CHDHFBJE
DBMS_WORKLOAD_REPLAY包可以参考下面链接:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_workload_replay.htm#CHDHHIDH

首先创建测试用户:

SQL> conn / as sysdba
Connected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/test11g/system01.dbf
/oradata/test11g/sysaux01.dbf
/oradata/test11g/undotbs01.dbf
/oradata/test11g/users01.dbf
/oradata/test11g/example01.dbf

SQL> create tablespace test datafile '/oradata/test11g/test01.dbf' size 50m;

Tablespace created.

SQL>  grant connect,resource to test identified by test;

Grant succeeded.

SQL> alter user test default tablespace test;

User altered.

SQL> create restore point sp1;

Restore point created.

SQL> create or replace directory test as '/home/oracle/dbcapture';

Directory created.

SQL> grant all on directory test to test;

Grant succeeded.

测试环境已经建立好。


一、启动捕获进程


SQL> conn system/oracle
Connected.

SQL> EXEC DBMS_WORKLOAD_CAPTURE.START_CAPTURE('CAPTURE1','TEST');

PL/SQL procedure successfully completed.

SQL> SELECT ID,NAME,DBNAME,DBVERSION,DIRECTORY,STATUS FROM DBA_WORKLOAD_CAPTURES;

        ID NAME       DBNAME     DBVERSION  DIRECTORY  STATUS
---------- ---------- ---------- ---------- ---------- --------------------
         1 CAPTURE1   TEST11G    11.2.0.1.0 TEST       IN PROGRESS
        

数据库后台ALERT日志文件中可以看到如下信息:

Sun Sep 26 09:31:18 2010
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(): Starting database capture at 09/26/2010 09:31:17

test对象对应的操作系统目录下将会生成几个文件:

[oracle@dbtest ~]$ ls -l dbcapture/
total 20
drwxr-xr-x  2 oracle oinstall 4096 Sep 26 09:31 cap
drwxr-xr-x  3 oracle oinstall 4096 Sep 26 09:31 capfiles
-rw-r--r--  1 oracle oinstall    0 Sep 26 09:31 wcr_cap_00001.start

二、模拟数据库活动

SQL> CONN TEST/TEST 
ERROR:
ORA-01017: invalid username/password; logon denied

11G的密码已经区分大小写了。

Warning: You are no longer connected to ORACLE.
SQL> conn test/test
Connected.
SQL> CREATE TABLE T(ID INT,NAME VARCHAR2(30));

Table created.

SQL> ! vi insert.sql

INSERT INTO T VALUES(1,'ICOL$');
INSERT INTO T VALUES(2,'I_USER1');
INSERT INTO T VALUES(3,'CON$');
INSERT INTO T VALUES(4,'UNDO$');
INSERT INTO T VALUES(5,'C_COBJ#');
INSERT INTO T VALUES(6,'I_OBJ#');
INSERT INTO T VALUES(7,'PROXY_ROLE_DATA$');
INSERT INTO T VALUES(8,'I_IND1');
INSERT INTO T VALUES(9,'I_CDEF2');
INSERT INTO T VALUES(10,'I_OBJ5');
INSERT INTO T VALUES(11,'I_PROXY_ROLE_DATA$_1');
INSERT INTO T VALUES(12,'FILE$');
INSERT INTO T VALUES(13,'UET$');
INSERT INTO T VALUES(14,'I_FILE#_BLOCK#');
INSERT INTO T VALUES(15,'I_FILE1');
INSERT INTO T VALUES(16,'I_CON1');
INSERT INTO T VALUES(17,'I_OBJ3');
INSERT INTO T VALUES(18,'I_TS#');
INSERT INTO T VALUES(19,'I_CDEF4');
INSERT INTO T VALUES(20,'IND$');
INSERT INTO T VALUES(21,'SEG$');
INSERT INTO T VALUES(22,'C_TS#');
。。。。。

SQL> @insert.sql

省略显示。

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(1) FROM T;

  COUNT(1)
----------
     10000


    
三、结束捕获进程

SQL> conn system/oracle
Connected.

SQL> EXEC DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;

PL/SQL procedure successfully completed.

SQL> SELECT ID,NAME,DBNAME,DBVERSION,DIRECTORY,STATUS FROM DBA_WORKLOAD_CAPTURES;

        ID NAME       DBNAME     DBVERSION  DIRECTORY  STATUS
---------- ---------- ---------- ---------- ---------- --------------------
         1 CAPTURE1   TEST11G    11.2.0.1.0 TEST       COMPLETED

从后台ALERT文件中可以看到如下信息:
Sun Sep 26 10:18:09 2010
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(): Stopped database capture
(not all sessions  could flush their capture buffers) at 09/26/2010 10:18:09

此时数据库的活动全部记录在以下目录中的文件中

[oracle@dbtest ~]$ ls -l dbcapture/
total 16
drwxr-xr-x  2 oracle oinstall 4096 Sep 26 10:18 cap
drwxr-xr-x  3 oracle oinstall 4096 Sep 26 09:31 capfiles


四、闪回到SP1时刻

由于只有一个11G的数据库环境,因此重放也是在这个数据库中进行的。此时
需要闪回到以前创建表的时候,以便观察。

SQL> CONN / AS SYSDBA
Connected.


SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  343154688 bytes
Fixed Size                  1336428 bytes
Variable Size             260049812 bytes
Database Buffers           75497472 bytes
Redo Buffers                6270976 bytes
Database mounted.


SQL> FLASHBACK DATABASE TO RESTORE POINT SP1;

Flashback complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> CONN TEST/test
Connected.
SQL> select tname from tab;

no rows selected

SQL> create or replace directory test as '/home/oracle/dbcapture';

Directory created.

SQL> grant all on directory test to test;

Grant succeeded.

五、准备重放进程

首先进行预处理

SQL> EXEC DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('TEST');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('REPLAY1','TEST');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY;  

PL/SQL procedure successfully completed.


以ORACLE用户登录到操作系统中,启动重放客户端

[oracle@dbtest ~]$ wrc userid=system password=oracle replaydir=/home/oracle/dbcapture/

Workload Replay Client: Release 11.2.0.1.0 - Production on Sun Sep 26 10:47:24 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Wait for the replay to start (10:47:24)


此时可以看到等待重放。

重新回到SQLPLUS下:

六、开始重放

SQL> EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY;

PL/SQL procedure successfully completed.

SQL> SELECT ID,NAME,DBNAME,DBVERSION,DIRECTORY,STATUS FROM DBA_WORKLOAD_REPLAYS;

        ID NAME       DBNAME     DBVERSION  DIRECTORY  STATUS
---------- ---------- ---------- ---------- ---------- --------------------
         1 REPLAY1    TEST11G    11.2.0.1.0 TEST       IN PROGRESS
        
此时可以看到重放客户端会出现 Replay started


[oracle@dbtest ~]$ wrc userid=system password=oracle replaydir=/home/oracle/dbcapture/

Workload Replay Client: Release 11.2.0.1.0 - Production on Sun Sep 26 10:47:24 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Wait for the replay to start (10:47:24)
Replay started (10:49:01)

ALERT文件中会出现:

Sun Sep 26 10:49:01 2010
DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 09/26/2010 10:49:00


重放完成后,DBA_WORKLOAD_REPLAYS的STATUS列会标记为COMPLETED.


SQL> SELECT ID,NAME,DBNAME,DBVERSION,DIRECTORY,STATUS FROM DBA_WORKLOAD_REPLAYS;

        ID NAME       DBNAME     DBVERSION  DIRECTORY  STATUS
---------- ---------- ---------- ---------- ---------- --------------------
         1 REPLAY1    TEST11G    11.2.0.1.0 TEST       COMPLETED
        
        
重放客户端会自动退出

[oracle@dbtest ~]$ wrc userid=system password=oracle replaydir=/home/oracle/dbcapture/

Workload Replay Client: Release 11.2.0.1.0 - Production on Sun Sep 26 10:47:24 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Wait for the replay to start (10:47:24)
Replay started (10:49:01)
Replay finished (11:35:30)
[oracle@dbtest ~]$

10000条记录,竟然重放了1个小时,可能和虚拟机有关系。

 

ALERT日志文件记录如下内容:

Sun Sep 26 11:34:07 2010
DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 09/26/2010 11:34:07
Sun Sep 26 11:34:24 2010
DM00 started with pid=34, OS id=5583, job SYS.SYS_EXPORT_TABLE_01
Sun Sep 26 11:34:30 2010
DW00 started with pid=35, OS id=5585, wid=1, job SYS.SYS_EXPORT_TABLE_01

怀疑数据是通过数据泵导入的。


SQL> CONN TEST/test
Connected.

SQL> SELECT COUNT(1) FROM T;

  COUNT(1)
----------
     10000

阅读(2136) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

chinaunix网友2010-09-27 10:50:56

很好的, 收藏了 推荐一个博客,提供很多免费软件编程电子书下载: http://free-ebooks.appspot.com