Database replay captures all external database calls made to system during capture period. This capture can be replayed against test system for the purpose of testing new software, evaluating bug fixes, or optimization of current code. The capture includes all relevant information about the client request, such as SQL text bind values, transaction information. Background activities and scheduler jobs are not captured. Additional client requests that are not captured are:
- Direct path load SQL*Loader.
- Shared Server requests (MTS)
- Oracle Streams.
- Advance Replication streams.
- NON-PL/SQL based advance queuing(AQ)
- Oracle Call Interface (OCI) based object navigation.
- NON SQL-based object access.
- Distributed transactions.
It is best practice to restart database before capturing production workloads, to allow started transactions to complete or be rolled back and avoid partial captures. By default all transactions by users are captured. You can either filter by exclude or include user sessions but not both. Use the DBMS_WORKLOAD_CAPTURE package ADD_FILTER and DELETE_FILTER for this purpose.
Have a well-defined starting point of capture with no user sessions active. It is recommended to starting the database in RESTICTED mode and switching the database to unrestricted mode once workload capture beings. Use DBMS_WORKLOAD_CAPTURE package START_CAPTURE and FINISH_CAPTURE. DBMS_WORKLOAD_CAPTURE package EXPORT_AWR – allows you to get statistics from production workload machine. You can use EM to monitor workload capture or the views DBA_WORKLOAD_CAPTURES and DBA_WORLOAD_FILTERS.
After workload is captured, you must preprocess the capture files prior to using them in replay. This changes them into replay files and creates necessary meta data. They can be replayed multiple times. It is suggested to move captured files to another machine for preprocessing, which can be a very intensive process. Use the package DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE to do the preprocessing of capture.
On replay to test system the database uses the external program called replay client. Multiple replay clients may be necessary and all DML and SQL will be replayed just as in production.
This article details the capture, processing and replay of a simple table creation and data insert on a single database system.
- Logon your server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Thu Jan 12 09:03:22 EST 2017 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$
- Logon to SQLPLUS as sysdba.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 07:05:13 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Shutdown and restart the database with ‘startup force’ to terminate all transactions.
SQL> startup force
ORACLE instance started.
Total System Global Area 549453824 bytes
Fixed Size 2926616 bytes
Variable Size 268437480 bytes
Database Buffers 272629760 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL>
- Exit out of SQLPLUS, create a directory for storage, and change permissions to 777. NOTE: A newly created empty directory should be used for REPLAY Capture.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[oracle@linux2 ~]$ mkdir /opt/app/sql_cap
[oracle@linux2 ~]$ chmod 777 /opt/app/sql_cap
[oracle@linux2 ~]$
- Log back into SQLPLUS as sysdba and create a directory object for capture operations. NOTE: This directory should be empty.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 09:09:57 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
SQL> create directory SQL_CAP as ‘/opt/app/sql_cap’;
Directory created.
SQL>
- Flush the shared_pool.
SQL> alter system flush shared_pool;
System altered.
SQL>
- Now begin the capture with DBMS_WORKLOAD_CAPTURE.START_CAPTURE procedure passing in the name and directory objects to be used.
SQL> execute DBMS_WORKLOAD_CAPTURE.START_CAPTURE(‘REPLAY_TEST’,’SQL_CAP’);
PL/SQL procedure successfully completed.
SQL>
- Now logon as the user scott/tiger, create a test table REPLAY_TEST, load the table with 100 records, and commit the transaction.
SQL> connect scott/tiger
Connected.
SQL> create table replay_test(col_a number,
col_b char(1));
2
Table created.
SQL> declare
val_b char(1):=’a’;
val_a number:=0;
begin
while val_a<100
loop
insert into replay_test(col_a, col_b)
values(val_a, val_b);
val_a:=val_a+1;
if val_b=’z’
then
val_b:=’a’;
else
val_b:=chr(ascii(val_b) + 1);
end if;
end loop;
end;
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
- Exit SQLPLUS and reconnect as sysdba.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 09:14:43 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Execute the DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE; procedure to stop the capture process.
SQL> execute DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE;
PL/SQL procedure successfully completed.
SQL>
- Now you have to process the capture by calling the procedure DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(‘SQL_CAP’); and passing in the directory object where the capture is stored.
SQL> execute DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(‘SQL_CAP’);
PL/SQL procedure successfully completed.
SQL>
- Because we are replaying this capture against the same database, we need to remove the objects and data inserted during the capture to remove any confusion.
SQL> drop table scott.replay_test;
Table dropped.
SQL>
- Prepare the database for replay with the procedures DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY and DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY.
SQL> execute DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(‘REPLAY_TEST’,’SQL_CAP’);
PL/SQL procedure successfully completed.
SQL> execute DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY;
PL/SQL procedure successfully completed.
SQL>
- Exit out of SQLPLUS and use the WRC utility to replay contents of your directory object. NOTE: You can also execute replay from inside of database with DBMS_WORKLOAD_REPLAY.START_REPLAY procedure.
wrc scott/tiger mode=replay replaydir=/opt/app/sql_cap
[oracle@linux2 ~]$ wrc scott/tiger mode=replay replaydir=/opt/app/sql_cap
Workload Replay Client: Release 12.1.0.2.0 – Production on Fri Jan 13 11:08:26 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Wait for the replay to start (11:08:26)
Replay client 1 started (11:08:39)
- In a second session of SQLPLUS as sysdba execute the procedure DBMS_WORKLOAD_REPLAY.START_REPLAY ();
SQL> execute DBMS_WORKLOAD_REPLAY.START_REPLAY ();
PL/SQL procedure successfully completed.
SQL>
- Select count(*) from scott.replay_test and see that the previous session has been re-executed.
SQL> select count(*) from scott.replay_test;
COUNT(*)
———-
100
SQL>
- Complete the transaction in the first command lines session with CTRL-C
- This completes simple example of database replay.
Larry Catt
OCP