The prior article in this series demonstrated a relatively straightforward scenario: how to capture a simulated application workload within the current Oracle 11g production environment (P+0) and then replay that same workload in the next iteration of that same environment (P+1). This article tackles a somewhat more ambitious task because it will illustrate how to:

• Capture and record the simulated application workload from a single-instance Oracle 10gR2 database, including the corresponding Automatic Workload Repository (AWR) data for that recorded workload
• Transfer the simulated workload to an Oracle 11gR1 Real Application Clusters (RAC) testing environment
• Preprocess the workload for replay, including remapping the connections to different RAC load-balanced services
• Replay the workload in the Oracle 11gR1 RAC test environment
• Identify application performance issues, data divergence, or error divergence

Since I concentrated on how to perform these tasks almost exclusively within the Oracle 11gR1 Enterprise Manager GUI, I’ll concentrate on how to use Oracle 11gR1’s supplied PL/SQL packages, DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY, to accomplish the same tasks via SQL*Plus and scripting.

Phase 1: Record a Workload in Single-Instance Environment

To keep this capture and replay scenario simple – and because I hate wasting anything useful - I’ll utilize the same PL/SQL objects that I constructed in the prior article to generate a workload for capture against a single-instance Oracle 10gR2 database. Since the minimum required release level to capture a workload in Oracle 10gR2 is 10.2.0.4. I first used the Database Upgrade Assistant (DBUA) to patch an existing Oracle 10gR2 database named DB10G up to Oracle Release 10.2.0.4 – a relatively painless process that takes about 30 minutes in my simulated Oracle 10gR2 production environment.

Feature Upgrade Alert
Note that as of July 18, 2008, it's now also possible to use an Oracle 9iR2 database as a potential target for Real Application Testing workload capture. The minimum release level required for execution of DBMS_WORKLOAD_CAPTURE in an Oracle 9i or 10g database environment, which patch levels to apply, and instructions for applying the patches is detailed in this document from Oracle Technical Support.

I then executed the same scripts and PL/SQL code to create and initialize a suitable environment just prior to executing a simulated application workload for capture. (See the first section of the prior article for a summary of the scripts and code to accomplish this.)

Preparing for Workload Capture. Now that my source database’s environment is initialized, I’ll initiate the capture of an actual workload. Listing 3.1 shows how I used procedure DBMS_WORKLOAD_CAPTURE.ADD_FILTER to first apply some appropriate filters to eliminate capture of user sessions that are producing “uninteresting” activity or need to be ignored during the capture period. In this case, I want to make sure that any activity related to either Enterprise Manager Database Control or Grid Control won’t be captured.

Starting Workload Capture. The code shown in Listing 3.2 illustrates how I used procedure DBMS_WORKLOAD_CAPTURE.START_CAPTURE to initiate workload capture. This procedure first checks the target directory (DBRCONTROL) for any prior executions of workload capture files; if any are found, it returns an error and won’t allow the current workload capture attempt to continue. If the capture startup is successful, however, the DB10G database’s alert log will recognize that a DBR capture operation is underway:

. . .
Mon Jun 23 19:40:41 2008
ALTER SYSTEM SET pre_11g_enable_capture=TRUE SCOPE=BOTH;
Mon Jun 23 19:40:44 2008
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(): Starting database capture at 06/23/2008 19:40:44
. . .

Since I’m capturing the workload against an Oracle 10gR2 database, note that I also had to set the dynamic initialization parameter PRE_11G_ENABLE_CAPTURE to TRUE before starting the capture.

Generating a Workload. To simulate multiple executions of similar code by different users, I prepared and executed a simple shell script named 10gSI_RandomLoadGenerator.sh. Like its similar predecessors in the prior article, it starts up approximately 80 user sessions that perform a random set of tasks like executing simple queries that do CPU-intensive computations, generating complex queries against the AP schema, or performing intense bursts of DML that add several thousand rows into the AP schema’s tables.

Note that I’ve also configured the DB10G database to use just one service name (DB10G) regardless of the type of operation being performed. I’ve added this service name as a potential connection alias in my database’s TNSNAMES.ORA configuration file. (In later steps, I’ll illustrate how to remap this connection to a different service name during workload replay against my Oracle 11gR1 RAC environment with DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION.)

Halting Workload Capture. To halt the workload’s capture, I executed procedure DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE to stop the workload capture operation (see Listing 3.3). Note that the successful conclusion of the DBR capture operation is also recorded in database DB10G’s alert log:

. . .
Mon Jun 23 19:42:21 2008
Thread 1 advanced to log sequence 43 (LGWR switch)
  Current log# 3 seq# 43 mem# 0: /u01/app/oracle/oradata/db10g/redo03.log
Mon Jun 23 19:44:35 2008
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(): Stopped database capture successfully at 06/23/2008 19:44:32

Viewing Workload Capture Results. To review the results of what the workload capture operation actually captured, I executed procedure DBMS_WORKLOAD_CAPTURE.REPORT to generate a summary report (see Listing 3.4). The output from this report can be viewed in text-only format in Report 3.1 or in HTML format here.

Phase 2: Preparing for Replay

After I successfully captured an appropriate workload against my Oracle 10gR2 single-instance database, I tackled the preparation of my target environment – an Oracle 11gR1 Real Application Cluster (RAC) clustered database:

• I configured a two-node Real Applications Cluster (RACNODE1 and RACNODE2) using Oracle Clusterware 11.1.0.6.0 to configure and manage the clustered environment.
• I deployed an Oracle 11.1.0.6.0 ASM instance on each node of the cluster and created two ASM disk groups, +DATA and +FRA, on shared disk storage for my RAC database’s files.
• I created a new RAC database named RACDB using the standard Oracle 11gR1 “seed” database template. This deployed two RAC instances, RACDB1 and RACDB2, one on each node of the cluster, each serviced by a single Listener.
• I deployed a new RAC service named TESTLBA as a preferred service on both nodes and tuned it for maximum connection management performance in a simulated OLTP environment that utilizes the Load Balancing Advisor to distribute connections across both database instances. Listing 3.5 shows the SRVCTL commands, the invocation of DBMS_SERVICE.MODIFY_SERVICE, and the TNSNAMES.ORA network configuration entries I deployed to configure the TESTLBA service appropriately.
• Finally, I created precisely the same database tables, indexes, and PL/SQL packages in the RACDB clustered database that I had previously created in the DB10G Oracle 10.2.0.4.0 database. I then restored the initial state of tables AP.VENDORS, AP.INVOICES and AP_INVOICE_DETAILS by executing script APInitialization.sql.

Preparing the Workload. Now that my Database Replay target environment is in place, I’m ready to prepare the workload that I captured in single-instance mode for its eventual replay in a RAC database environment:

• I added a new physical directory, /home/oracle/DBRControl, on nodes RACNODE1 and RACNODE2.
• I created a new directory object named DBRCONTROL in the RACDB database, aimed it at the physical directory I just created on each node, and granted the appropriate access privileges to this directory object.
• I copied the files that I generated against the DB10G database during DBR Workload Capture to the same physical directory on both nodes. Note that I could have copied these workload replay files to just one of the nodes – say, the one that normally handles the least work – because any node in the RAC cluster can be used to manage the replay of the captured DBR workload.
• Finally, I executed procedure DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE to preprocess the captured workload for its eventual replay. This procedure parses the information in the files that recorded a shadow copy of each session’s operations against the DB10G database and prepares the workload for replay against the RACDB database.

Listing 3.6 shows the commands I issued to create the physical directories on each node, the corresponding directory object in the RACDB database, and (after all workload capture recorded files have been copied to the appropriate physical directories) how I executed procedure DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE to preprocess the captured workload

Previous   Next

Back to DBAsupport.com