数据库重放

准备环境

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 100m;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant resource,connect to test;

Grant succeeded.

SQL> grant select on dba_objects to test;

Grant succeeded.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL> create restore point sp1;

Restore point created.

SQL> create directory dump_dir as '/u03/dump_dir';

Directory created.

SQL> grant all on directory dump_dir to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> set pages 0
SQL> create table t1(id number,name varchar2(200));
Table created.
SQL> spool insert.sql
SQL> select 'insert into t1 values ('||object_id||','''||object_name||''');' from dba_objects;
...
SQL> spool off
修改spool出来的脚本，去头，去尾

使用SYS开始捕获
SQL> exec dbms_workload_capture.start_capture('capture1','DUMP_DIR');

PL/SQL procedure successfully completed.

能过数据字典查询捕获任务的状态
SQL> col id for 9
SQL> col name for a10
SQL> col DBNAME for a10
SQL> col DBVERSION for a10
SQL> col DIRECTORY for a15
SQL> col STATUS for a20
SQL> select ID,NAME,DBNAME,DBVERSION,DIRECTORY,STATUS from dba_workload_captures;

ID NAME       DBNAME     DBVERSION  DIRECTORY       STATUS
-- ---------- ---------- ---------- --------------- --------------------
 1 capture1   ORCL       11.2.0.1.0 DUMP_DIR        IN PROGRESS

在操作系统下，查看目录对象中捕获生成的文件状态
[oracle@oel dump_dir]$ du -h
4.0K    ./capfiles/inst1/aj
4.0K    ./capfiles/inst1/ai
4.0K    ./capfiles/inst1/ah
4.0K    ./capfiles/inst1/ag
4.0K    ./capfiles/inst1/aa
4.0K    ./capfiles/inst1/ad
4.0K    ./capfiles/inst1/ae
4.0K    ./capfiles/inst1/ab
4.0K    ./capfiles/inst1/af
4.0K    ./capfiles/inst1/ac
44K     ./capfiles/inst1
48K     ./capfiles
8.0K    ./cap
60K     .

使用test用户，去执行负载的脚本insert.sql

conn test/test
@insert.sql
commit;

使用SYS用户，结束捕获
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   ORCL       11.2.0.1.0 DUMP_DIR        COMPLETED

在操作系统下，查看目录对象中捕获生成的文件状态
[oracle@oel dump_dir]$ du -h
4.0K    ./capfiles/inst1/aj
4.0K    ./capfiles/inst1/ai
4.0K    ./capfiles/inst1/ah
4.0K    ./capfiles/inst1/ag
25M     ./capfiles/inst1/aa
4.0K    ./capfiles/inst1/ad
4.0K    ./capfiles/inst1/ae
4.0K    ./capfiles/inst1/ab
4.0K    ./capfiles/inst1/af
4.0K    ./capfiles/inst1/ac
25M     ./capfiles/inst1
25M     ./capfiles
56K     ./cap
25M     .

将数据库闪回到没有执行捕获和数据库负载之前，即我们创建还原点(sp1)的位置
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  468701184 bytes
Fixed Size                  1337268 bytes
Variable Size             159385676 bytes
Database Buffers          301989888 bytes
Redo Buffers                5988352 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 * from tab;

no rows selected

开始使用SYS进行重放
SQL> conn / as sysdba
Connected.

这一步因为是闪回到过去了，要重新创建路径和授予test权限，路径要一致
SQL> create directory dump_dir as '/home/oracle/dumpdir';

Directory created.

SQL> grant all on directory dump_dir to test;

Grant succeeded.
提供捕获文件所在的目录对象
SQL> exec dbms_workload_replay.process_capture('DUMP_DIR');

PL/SQL procedure successfully completed.

初始化重放任务
SQL> exec dbms_workload_replay.initialize_replay('REPLAY1','DUMP_DIR');

PL/SQL procedure successfully completed.

准备开始重放
SQL> exec dbms_workload_replay.prepare_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    ORCL       11.2.0.1.0 DUMP_DIR        PREPARE

回到操作系统的BASH环境下，执行重放
[oracle@oel ~]$ wrc userid=system password=oracle replaydir=/u03/dump_dir

Workload Replay Client: Release 11.2.0.1.0 - Production on Thu Jun 2 15:22:15 2016

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

Wait for the replay to start (15:22:15)

使用SYS，开始重放
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    ORCL       11.2.0.1.0 DUMP_DIR        IN PROGRESS

在操作系统的BASH环境下，查看重放的进度
[oracle@oel ~]$ wrc userid=system password=oracle replaydir=/u03/dump_dir

Workload Replay Client: Release 11.2.0.1.0 - Production on Thu Jun 2 15:22:15 2016

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

Wait for the replay to start (15:22:15)
Replay started (15:23:14)

等待重放结束，录了多久，就重放多久

重放结束
[oracle@oel ~]$ wrc userid=system password=oracle replaydir=/u03/dump_dir

Workload Replay Client: Release 11.2.0.1.0 - Production on Thu Jun 2 15:22:15 2016

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

Wait for the replay to start (15:22:15)
Replay started (15:23:14)
Replay finished (15:39:52)

检查数据字典中重放任务的状态
SQL> select ID,NAME,DBNAME,DBVERSION,DIRECTORY,STATUS from dba_workload_replays;

ID NAME       DBNAME     DBVERSION  DIRECTORY       STATUS
-- ---------- ---------- ---------- --------------- --------------------
 1 REPLAY1    ORCL       11.2.0.1.0 DUMP_DIR        COMPLETED

检查数据库状态
SQL> conn test/test
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE

6 rows selected.

SQL> select count(*) from t1;

  COUNT(*)
----------
     72569

 select id,name,dbname,dbversion,directory,status from dba_workload_captures;
SQL> exec dbms_workload_capture.delete_capture_info(11);

SQL> DESC dbms_workload_capture;----查看其它的使用方法

SQL> desc dbms_workload_replay;也是一样

SQL> exec dbms_workload_replay.delete_replay_info(1);