Oracle 11g new Features - Database Capture and Replay

Database Capture and Replay

Oracle 11g come with a cool new feature to capture the changes in a database at a specified interval and replay them in a different database. This is similar to running a macro to capture all the changes in the computer screen and playing the macro in other computers.

Oracle uses two new DBMS packages to perform this “DBMS_WORKLOAD_CAPTURE” and “DBMS_WORKLOAD_APPLY”.

In the example below I’ll be performing the capture and replay on the same database. I’ll set the “restore point” before the capture process. Once the capture process is done, I’ll flashback the database to the restore point and replay the captured task on the same database.

Enable flash back on the database first. This is not a requirement, I am doing this to be able to flash back the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2121800 bytes
Variable Size 2566918072 bytes
Database Buffers 1694498816 bytes
Redo Buffers 12242944 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.
Elapsed: 00:00:00.10
SQL> alter database flashback on;
Database altered.

Elapsed: 00:00:01.40
SQL> alter database open;
Database altered.
Elapsed: 00:00:15.39

SQL> SELECT flashback_on, log_mode
2 FROM v$database;
FLASHBACK_ON LOG_MODE
------------------ ------------
YES ARCHIVELOG
Elapsed: 00:00:00.00

Capture Process

Lets start with creating a directory to hold the captured information.

SQL> create directory capture_dir as 'd:\database_capture';
Directory created.
Elapsed: 00:00:00.04

Here I am creating a restore point to flashback the database for replay.

SQL> create restore point praveen;
Restore point created.
Elapsed: 00:00:00.09

We need to use DBMS_WORKLOAD_CAPTURE.start_capture package to start the capture process. The start_capture process captures everything that’s happening in the database, In this example I am only interested in the actions performed by user “SCOTT”. I have to use some filters for my capture process. The procedure “ADD_FILTER” under the same package is used to filter the capture process.

Lets look at the parameters required for the “ADD_FILTER” option.

SQL> desc DBMS_WORKLOAD_CAPTURE
PROCEDURE ADD_FILTER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
FNAME VARCHAR2 IN
FATTRIBUTE VARCHAR2 IN
FVALUE VARCHAR2 IN

Fname - Any name for your Filter

Fattribute - The attribute which the filter needs to be applied.
The possible values are( INSTANCE_NUMBER - type NUMBER,USER - type STRING,MODULE - type STRING,ACTION - type STRING,PROGRAM - type STRING,SERVICE - type STRING)

Fvalue - The value for the attribute (wild cards are permitted)

sqlplus / as sysdba
SQL> exec DBMS_WORKLOAD_CAPTURE.ADD_FILTER ('CAPTURE_FILTER1','USER','SCOTT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06

Now lets start the capture process by executing the DBMS_WORKLOAD_CAPTURE.START_CAPTURE package.

Procedure definition
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
name IN VARCHAR2,
dir IN VARCHAR2,
duration IN NUMBER DEFAULT NULL,(in sec)
default_action IN VARCHAR2 DEFAULT 'INCLUDE',
auto_unrestrict IN BOOLEAN DEFAULT TRUE);


SQL> execute DBMS_WORKLOAD_CAPTURE.start_capture ('db_capture1','CAPTURE_DIR',NULL,'EXCLUDE',FALSE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.75
SQL>

Here the parameter exclude means - Exclude all the changes from other users, expect the changes from user scott as specified in the filter

Perform some actions at the database as user scott.

SQL> connect scott/xxxxxxx
Connected.
SQL> ED
Wrote file afiedt.buf
1 CREATE USER PRAVEEN1 IDENTIFIED BY PRAVEEN
2 DEFAULT TABLESPACE USERS
3 TEMPORARY TABLESPACE TEMP
4* QUOTA UNLIMITED ON USERS
SQL> /
User created.

SQL> CREATE TABLE SCOTT.PRAVEEN AS SELECT * FROM SCOTT.TEST;
Table created.
Elapsed: 00:00:00.12
SQL> GRANT CONNECT, DBA TO PRAVEEN1;
Grant succeeded.
Elapsed: 00:00:00.03
SQL>

Now log in as sysdba and stop the capture process

SQL> EXECUTE DBMS_WORKLOAD_CAPTURE.finish_capture;
PL/SQL procedure successfully completed.
Elapsed: 00:00:34.01
SQL>

Here are the contents of my capture_directory

D:\database_capture>dir
Volume in drive D is Data
Volume Serial Number is 9820-5C5B

Directory of D:\database_capture

03/24/2009 05:23 PM .
03/24/2009 05:23 PM ..
03/24/2009 05:18 PM 0 wcr_4mkawa80025q8.rec
03/24/2009 05:18 PM 0 wcr_4mkawac002708.rec
03/24/2009 05:19 PM 0 wcr_4mkawah0023q0.rec
03/24/2009 05:19 PM 0 wcr_4mkawaw00252c.rec
03/24/2009 05:19 PM 0 wcr_4mkawb400254h.rec
03/24/2009 05:19 PM 736 wcr_4mkawd40025nw.rec
03/24/2009 05:19 PM 0 wcr_4mkawds0025r4.rec
03/24/2009 05:19 PM 0 wcr_4mkawfn002408.rec
03/24/2009 05:19 PM 0 wcr_4mkawfn0026k0.rec
03/24/2009 05:19 PM 68 wcr_4mkawhs0024gs.rec
03/24/2009 05:20 PM 970 wcr_4mkawph00248n.rec
03/24/2009 05:20 PM 1,390 wcr_4mkawqc0027ws.rec
03/24/2009 05:20 PM 68 wcr_4mkawsc0022zc.rec
03/24/2009 05:20 PM 0 wcr_4mkawsh0027ch.rec
03/24/2009 05:21 PM 970 wcr_4mkawx40027rh.rec
03/24/2009 05:21 PM 68 wcr_4mkawzw0026fh.rec
03/24/2009 05:22 PM 0 wcr_4mkax58002330.rec
03/24/2009 05:22 PM 68 wcr_4mkax7c0023fn.rec
03/24/2009 05:22 PM 992 wcr_4mkax880022cw.rec
03/24/2009 05:23 PM 43,490 wcr_cr.html
03/24/2009 05:23 PM 22,006 wcr_cr.text
03/24/2009 05:23 PM 192 wcr_fcapture.wmd
03/24/2009 05:18 PM 128 wcr_scapture.wmd
23 File(s) 71,146 bytes
2 Dir(s) 96,016,297,984 bytes free

The data dictionary dba_workload_capture will provide information about the capture process.
SQL> col name for a15
SQL> select id, name from dba_workload_captures;
ID NAME
---------- ---------------
1 db_capture1
Elapsed: 00:00:00.01

You are done with the capture process

Replay

Here I am planning to replay on the same database which I performed the capture.I have created a new directory named database_replay and copied all the contents from database_capture directory. This step is optional.

SQL> create directory replay_dir as 'd:\database_replay';
Directory created.
Elapsed: 00:00:00.04
SQL>

The steps below are required only for testing and it applies to this scenario. This step is not a requirement for replay

Here we shutdown the database and flashback the database to a point before the capture process.

Shutdown the database and recover it to a point before the capture process.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2121800 bytes
Variable Size 2566918072 bytes
Database Buffers 1694498816 bytes
Redo Buffers 12242944 bytes
Database mounted.

Flash back the database to the restore point Praveen (perfomed before the capture process started).

SQL> flashback database to restore point praveen;
Flashback complete.
Elapsed: 00:00:06.31
SQL> alter database open resetlogs;
Database altered.

Query the database to see, if the user "praveen1" exists (created during capture process)

SQL> select username from dba_users where username like 'PRAVEEN1';
no rows selected
Elapsed: 00:00:00.00
SQL> select count(*) from dba_tables where table_name like 'PRAVEEN' and owner like 'SCOTT';
COUNT(*)
----------
0

Three procedures under the package dbms_workload_capture are used for replay. The steps below are not optional.

PROCEDURE PROCESS_CAPTURE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CAPTURE_DIR VARCHAR2 IN


PROCEDURE INITIALIZE_REPLAY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPLAY_NAME VARCHAR2 IN
REPLAY_DIR VARCHAR2 IN

PROCEDURE PREPARE_REPLAY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SYNCHRONIZATION BOOLEAN IN DEFAULT
CONNECT_TIME_SCALE NUMBER IN DEFAULT
THINK_TIME_SCALE NUMBER IN DEFAULT
THINK_TIME_AUTO_CORRECT BOOLEAN IN DEFAULT

The first step is to initialize and prepare the database for replay

sqlplus / as sysdba

SQL> execute DBMS_WORKLOAD_REPLAY.process_capture('REPLAY_DIR');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.98
SQL> EXECUTE DBMS_WORKLOAD_REPLAY.initialize_replay ('DATABASE_REPLAY1','REPLAY_DIR');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
SQL> EXECUTE DBMS_WORKLOAD_REPLAY.prepare_replay;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.25

There is a replay client that’s available in oracle 11g named “wrc” in both unix and windows environments. The wrc client can read the captured work and replay them.

You have to openup a new window or terminal session type the command at the "os" prompt.

D:\>wrc userid=scott password=xxxxx replaydir='d:\database_replay' mode=replay

Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Mar 26 17:35:23 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (17:35:23)

The session will wait till you manually start the replay.

Go back to sqlplus and login as sysdba on other window

SQL> execute DBMS_WORKLOAD_REPLAY.start_replay;

PL/SQL procedure successfully completed.

You can check the staus of apply at wrc session.

Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Mar 26 17:35:23 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (17:35:23)
Replay started (17:35:45)
Replay finished (17:39:17)
D:\>

At this point you are done with Replay.

Test your database to see the captured changes exist in the database.
-------------------------------------------------------------------------------
SQL> select username from dba_users where username like 'PRAVEEN1';

USERNAME
------------------------------
PRAVEEN1
Elapsed: 00:00:00.01
SQL>
SQL> select count(*) from SCOTT.PRAVEEN;
COUNT(*)
----------
14
Elapsed: 00:00:00.01

Conclusion
This feature allows the database administrator to simulate the workload to test database level changes through the recreation of actual workloads. You can use OEM grid control to perform the same using GUI interface. I'll disucss other new features in future post