Database replay and consolidated database replay

Brief introduction

In a database migration and upgrade scenario, we often encounter a problem: how to simulate real business stress when doing stress testing, there are many ways to solve this problem, such as: The application develops the simulation program or uses the Stress test tool simulation, such as load runner, but, If you want to say which method can best simulate the real business pressure, I think it is the database Replay (DB Replay) function of Oracle, Replay function is part of real application testing, Its basic principle diagram is as follows:

Simply put, database replay can "capture" the payload (workload capture) on the production database and save it as a binary file in a certain format. The saved load files are copied to the test environment, and after certain processing, the load (workload replay) can be "replayed" in the test environment to simulate the real pressure and carry out the stress test.

The Database replay is a 11g feature, but the load is captured, Support for the 10.2.0.4 database, which is also in line with the needs of many domestic customers, because there are a large number of customers, because the system has been running on the 10GR2 version of the Oracle database, there are upgrade requirements.

Consolidated database Replay is a function that occurs after 11.2.0.2, which literally means integrated replay of the databases that can "capture" the load of different systems and integrate them into a replay for database consolidation testing and scale-up testing.

Later we use the experiment to detail the database replay related functions, the experiment uses the 10.2.0.5 as the load capture end, uses the 12.1.0.2 as the load replay end.

Preparation (Installation of database replay related patches)

First, according to the document Mandatory patches for Database testing functionality for the current and Earlier releases (Doc ID 560977.1), check whether the hit patch is playing It's over. Both the capture end and the replay end are hit.

Capture of loads

The capture side is a 10.2.0.5 that is installed on OL5.9, and the database SID is Test1.

Create a Directory

Create a directory on the OS and create the appropriate database directory objects to hold the payload data:

$ cd/u01/app/oracle

$ mkdir Wrc_dir1

Sql>create OR REPLACE DIRECTORY wrc_dir1 as '/u01/app/oracle/wrc_dir1/';

Open 10GR2 's workload Capture support

Although database replay supports load "capture" of more than 10.2.0.4 versions, 10GR2 databases need to be set up with an initialization parameter for the database to function properly, and the online documentation requires that the script be run $oracle_home/rdbms/ Admin/wrrenbl.sql, actually this script only does one thing, is sets the initialization parameter pre_11g_enable_capture=true.

[Email protected] ~]$ sqlplus "/As SYSDBA"

Sql*plus:release 10.2.0.5.0-production on Fri Dec 23 10:57:26 2016

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Sql> @?/rdbms/admin/wrrenbl.sql

sql> SET FEEDBACK 1

Sql> SET Numwidth 10

sql> SET Linesize 80

Sql> SET Trimspool on

sql> SET TAB OFF

sql> SET PAGESIZE 100

Sql> alter system set pre_11g_enable_capture=true sid= ' * ';

System altered.

After the modification, the database needs to be restarted, not only for parameters to take effect, but also for all sessions to reconnect.

Creating experimental users and objects

Create the database user App1 and create the table Test_table1 in the APP1 schema:

Sql>create table Test_table1 (col1 number, col2 varchar2 (20));

Start capturing load

User execution with SYS:

BEGIN

Dbms_workload_capture.start_capture (name = ' Test1_cap ',

dir = ' Wrc_dir1 ',

Duration = NULL);

END;

/

Simulated load

Log in to the database using the App1 user and execute:

Begin

For I in 1..10000 loop

INSERT into test_table1 values (i, ' test1: ' | | i);

End Loop;

Commit

End

/

Insert 10,000 records in the Test_table1 table.

Then disconnect the session.

Stop capturing load

Sql>exec dbms_workload_capture.finish_capture;

Replay of the Load

The replay end is a 12.1.0.2 database installed on OL7.1, and the database SID is Test3.

Preparatory work

To create a directory and database object:

$ mkdir–p/u01/app/oracle/con_dir/wrc_dir1

Sql>create OR REPLACE DIRECTORY wrc_dir1 as '/u01/app/oracle/con_dir/wrc_dir1/';

Copy the captured payload file to/u01/app/oracle/con_dir/wrc_dir1.

Create App1 users on the replay side and create and capture table test_table1 with the same structure on the side.

Working with load files

Execute with SYS User:

BEGIN

Dbms_workload_replay. Process_capture (capture_dir = ' Wrc_dir1 ', parallel_level=>1);

END;

/

BEGIN

Dbms_workload_replay. Initialize_replay (replay_name = ' replay1 ',

Replay_dir = ' Wrc_dir1 ');

END;

/

exec dbms_workload_replay.prepare_replay;

Start the WRC client

The WRC client is the program body responsible for reading load files and replaying the load. Replay requires the match of the WRC client in addition to issuing instructions in the SQL environment. command to start the WRC client:

$ WRC system/welcome1 Mode=replay Replaydir=./wrc_dir1

Start replay

sql> exec Dbms_workload_replay.start_replay;

The WRC client exits before the flag replay is complete, and the following is the actual output that is executed:

[Email protected] con_dir]$ WRC system/welcome1 mode=replay replaydir=./wrc_dir1

Workload Replay client:release 12.1.0.2.0-production on Sun Jan 1 20:46:15 2017

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

Wait for the replay to start (20:46:15)

Replay Client 1 started (20:46:45)

Replay Client 1 finished (20:50:27)

At this point we can see the replay end App1 User Test_table1 table is inserted 10,000 records, indicating that the load is correctly replayed.

In addition, you can view the replay report and get the report command when finished:

Sql>set pagesize 0 Long 30000000 longchunksize linesize 155

Sql>select Dbms_workload_replay. Report (replay_id =, format = ' HTML ') from dual;

Of course, to do more detailed analysis, or to see the corresponding time period of the AWR report is better.

Consolidated Database Replay Integration Scenario

For example, there are two apps that run on separate databases, and customers want to consolidate two applications on a single database, but don't know if the new database can withstand the stress. Then you can use consolidated Database replay to simulate the test.

Capture-side prep work

In the previous example, we did the capture from the database test1, and then we created another 10.2.0.5 database called Test2, which also captures a load on test2.

$ cd/u01/app/oracle

$ mkdir Wrc_dir2

Execute in the Test2 database:

Sql>create OR REPLACE DIRECTORY wrc_dir2 as '/u01/app/oracle/wrc_dir2/';

Create the database user app2 and create the table Test_table2 in the APP2 schema:

Sql>create table Test_table2 (col1 number, col2 varchar2 (20));

Modify the initialization parameters and restart the Test2 database.

Start capturing

User SYS user executes:

BEGIN

Dbms_workload_capture.start_capture (name = ' Test2_cap ',

dir = ' Wrc_dir2 ',

Duration = NULL);

END;

/

Analogue pressure

Using the APP2 user to log in to the database Test2, execute the following script to simulate the pressure:

Begin

For I in 1..10000 loop

INSERT into test_table2 values (i, ' test2: ' | | i);

End Loop;

Commit

End

/

Then disconnect the connection.

Stop capture

Sql>exec dbms_workload_capture.finish_capture;

Replay-side Prep work

We still use the 12.1.0.2 environment of the OL7.1 on the previous experiment, using the TEST3 database.

Create the Consolidated database Replay directory:

Sql>create OR REPLACE DIRECTORY con_dir as '/u01/app/oracle/con_dir/';

Create a directory wrc_dir2 that holds the test2 payload. (Wrc_dir1 has been created in the previous experiment.) ）

$ cd/u01/app/oracle/con_dir

$ mkdir Wrc_dir2

Sql>create OR REPLACE DIRECTORY wrc_dir2 as '/u01/app/oracle/con_dir/wrc_dir2/';

Both Wrc_dir1 and Wrc_dir2 are subdirectories of Con_dir.

Create APP2 users on the database Test3 and create and capture table test_table2 with the same structure on the side.

(App1 users and Test_table1 have been created on the database test3 in the last experiment)

Process load and set replay schedule

User execution with SYS:

Sql>exec dbms_workload_replay.set_replay_directory (' Con_dir ');

Sql>exec dbms_workload_replay.begin_replay_schedule (' S1 '); --S1 is the name for the timetable.

Sql>select dbms_workload_replay.add_capture (' Wrc_dir1 ') from dual;

Sql>select dbms_workload_replay.add_capture (' Wrc_dir2 ') from dual;

Sql>exec Dbms_workload_replay.end_replay_schedule; --Save Replay schedule

Sql>exec dbms_workload_replay.initialize_consolidated_replay (' CR1 ', ' S1 ');

Sql>exec dbms_workload_replay.prepare_consolidated_replay (synchronization = ' object_id ');

--The parameter synchronization determines how the replay replays the load, object_id the sequence of operations on the object, in the scope of the object and the referenced object, in the order in which the replay replay the payload, in the same sequence as when it was captured.

Start the WRC client

Because it is 2 different loads, you need to start at least 2 WRC clients

$ WRC system/welcome1 mode=replay Replaydir=/u01/app/oracle/con_dir &

$ WRC system/welcome1 mode=replay Replaydir=/u01/app/oracle/con_dir &

Start replay

Execute with SYS User:

Sql>exec Dbms_workload_replay.start_consolidated_replay;

After the replay, we can see that in the database Test3 App1 and app2 two users under the Test_table1 and Test_table2 tables are inserted 10,000 records, proof that the load is correctly replayed.

Scale-up scene

Sometimes, we want to know the future of business growth, whether the database can withstand, such as the simulation of the future of the business to increase the number of cases, you can also use consolidated database Replay. The simplest way to use this is to capture, copy multiple copies, and replay.

For example, the above experiment, the load captured from the Test1 library, we can copy 2 copies, placed in the Wrc_dir1 and Wrc_dir2, the other steps exactly the same, when we replay, is to perform a double test1 on the captured load.

The actual effect is that 20,000 records are inserted in the Test_table1 table under the App1 user.

Of course, scale-up is not just so simple, corresponding to the time shifting, that is, by adjusting the times, so that the peak of multiple loads coincide together, there is workload folding, is to load, divided into different time periods, The load is then executed in parallel for different time periods. The space is limited and left to everyone to study.

Problems:

If there is no error, but the replay side does not replay the load, it is possible that the WRC client has a problem, you can try to re-compile the WRC client on the replay side:

$ cd $ORACLE _home/rdbms/lib

$ make-f ins_rdbms.mk IWRC

Database replay and consolidated database replay