Oracle Real Application Testing 10g

23 Dec, 2017 Free Essays 0

REAL APPLICATION TESTING ON 10G – STEP BY STEP Alejandro Vargas Principal Support Consultant Oracle Advanced Customer Services Summary………………………………………………………………………………………………………………………………………………………. 3 Patches Required for RAT……………………………………………………………………………………………………………………………….. Project Implementation Steps…………………………………………………………………………………………………………………………… 5 Clone production database on test server, start it up…………………………………………………………………………………………… 6 Create 11g database on test server on Windows………………………………………………………………………………………………… Download patches for RAT………………………………………………………………………………………………………………………………. 6 Download RAT scripts and docs from OTN………………………………………………………………………………………………………… 7 Patches Installation…………………………………………………………………………………………………………………………………………. Install patch 6998002 on Windows 10g database……………………………………………………………………………………………….. 9 Install patch 6903335 on Windows 10g clone database……………………………………………………………………………………… 7 1 Install patch 7044721 on Windows 11g test database……………………………………………………………………………………….. 22 Configure and execute functional RAT tests on Windows…………………………………………………………………………………… 6 Create A Small Test Inside To Clone Of Production To Test On Linux…………………………………………………………………. 36 Test Capture On The Production Clone…………………………………………………………………………………………………………… 36 Create a filter for the session to capture…………………………………………………………………………………………………………… 38 Create a Directory to store the captured load……………………………………………………………………………………………………. 9 Start the Capture process………………………………………………………………………………………………………………………………. 9 3 Stopping a Workload Capture………………………………………………………………………………………………………………………… 41 Check capture process ID and status………………………………………………………………………………………………………………. 1 Exporting AWR Data for Workload Capture……………………………………………………………………………………………………… 43 After completing the run gather awr statistics……………………………………………………………………………………………………. 45 1/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Generating a Workload Capture Report Using APIs………………………………………………………………………………………….. 6 Workload Preprocessing……………………………………………………………………………………………………………………………….. 47 Workload Replay………………………………………………………………………………………………………………………………….. ……… 47 Steps for Replaying a Database Workload……………………………………………………………………………………………………….. 0 Prepare the Replay Database………………………………………………………………………………………………………………………… 51 Execute the Replay Database………………………………………………………………………………………………………………………… 51 SQL Performance Analyzer……………………………………………………………………………………………………………………………. 5 SQL Performance Analyzer Workflow Steps…………………………………………………………………………………………………….. 55 Capture the SQL workload using API’s…………………………………………………………………………………………………………….. 5 5 Create An Empty Sql Tuning Set To Be Populated With Some Load…………………………………………………………………… 56 Load SQL Statements on the STS………………………………………………………………………………………………………………….. 6 Displaying the Contents of a SQL Tuning Set…………………………………………………………………………………………………… 60 Transporting a SQL Tuning Set………………………………………………………………………………………………………………………. 63 Transport The Load Table To The Test System………………………………………………………………………………………………… 6 FTP the table to the test system……………………………………………………………………………………………………………………… 65 Unpack the STS Into the Test System……………………………………………………………………………………………………………… 8 6 Prepare The Test System…………………………………………………………….. ……………………………………………………………….. 6 Creating a SQL Performance Analyzer Task…………………………………………………………………………………………………….. 9 6 Executing the SQL Workload Before a Change………………………………………………………………………………………………… 70 Generate an Report with Analysis Of Task Results……………………………………………………………………………………………. 72 2/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Summary

This document is a step-by-step example of using Real Application Testing (RAT) to evaluate a platform migration of a 10. 2. 0. 3 production database from Windows 32bit to Linux 64 bit. The project consider capturing and running production loads on a test environment using SQL Performance Analyzer (SPA) on a 10g production clone on Linux; and Database Replay on an production clone upgraded to 11g on Linux. Both tests should provide information to evaluate if the Linux environment actual configuration is capable to sustain a production load.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!

order now

The examples provided are limited, RAT offers a wide range of possible configurations, and this is an example of one of these multiple possibilities. Complete information about RAT requirements for pre 11g databases can be found on Note 560977. 1 Real Application Testing functionality for pre-11g database releases is installed using the “opatch” utility and following instructions for the generic and platform specific “README” for the patches. 11g do need also a patch to be able to use SQL traces from previous releases and create an SQL Tuning Set (STS). 3/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Patches Required for RAT

Here I’m relating only to the patches I did use on this test, a complete list of RAT required patches for various platforms could be found on Metalink on Note 560977. 1. If you want to only use Database Replay or SQL Performance Analyzer, then only the patch for that particular functionality needs to be applied. If you want to use both Database Replay and SQL Performance Analyzer, the patches for both functionalities need to be applied. Database Replay is only possible when using an 11g Database for the replay phase Patches for Database Replay on Windows: Source Destination Patch 10. 2. 0. 3. 0 32-bit >=11. 1. . 6. 0 10. 2. 0. 3. 0 + patch bundle 6998002 Patches for Database Replay on Linux: Source Destination 10. 2. 0. 3. 0 >=11. 1. 0. 6. 0 Available on Bundle 23. Patch 10. 2. 0. 3. 0 + one off patch 6974999 Patch for SQL Performance Analyzer on Windows Source Destination Patch 10. 2. 0. 3 10. 2. 0. 4 i) 11. 1. 0. 6 + 6865809(7044721) AND ii) 10. 2. 0. 3 + 6903335 AND iii) 10. 2. 0. 4 + 6877038 Download RAT documentation and scripts from http://www. oracle. com/technology/software/products/database/oracle10g/realapptesting. html 4/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Project Implementation Steps 1.

Clone production database on test server, start it up 2. Create 11g database on test server on Windows 3. Download patch 6998002 for Windows 32 bit 4. Download patch 6903335 for Windows 32 bit 5. Download patch 7044721 for 11. 0. 6. 1 on windows 6. Download patch 6974999 for 10. 2. 0. 3 on Linux 7. Download RAT scripts and docs from OTN 8. Install patch 6998002 on Windows 10g clone database 9. Install patch 6903335 on Windows 10g clone database 10. Install patch 7044721 on Windows 11g test database 11. Install patch 6974999 on Linux 10g test database 12. Configure and execute functional RAT tests on Windows 13. Install 11g on Linux 14.

Create 11g Database on Linux 15. Migrate clone of production Database from windows to Linux 16. Configure and execute functional RAT tests on Linux 17. Install patch 6903335 on production database 18. Capture load on production Database 19. Replay production load on Linux 5/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Clone production database on test server, start it up Rman was used to create a clone of the production database on the test server. Create 11g database on test server on Windows 11. 1. 0. 6 was installed on the same test server where the 10g clone of production was created, A new 11g database REALAPT was created

Download patches for RAT RAT is available from 10. 2. 0. 4 patch set. For previous releases is made available into one off patches, a complete matrix of patches can be found on Metalink on Note 560977. 1. Patch 7044721 is required on 11. 0. 6. 1 to be able to use SQL traces from previous releases and create an SQL Tuning Set (STS) from them. The following patches were downloaded and applied for this project: Patch 6998002 for Windows 32 bit Patch 6903335 for Windows 32 bit Patch 7044721 for 11. 0. 6. 1 on windows Patch 6974999 for 10. 2. 0. 3 on Linux 6/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP

Download RAT scripts and docs from OTN RAT functionality can be run from Enterprise Manager or manually using the provided API’s. Information about both approaches can be obtained from the 10g RAT user Guide that can be downloaded from OTN. The RAT user guide comes also as a part of the 11g manuals, can be downloaded from download. oracle. com. Patches Installation The patches required for enabling RAT on pre 11g versions and for the 11g version itself are installed using opatch. A healthy inventory is required, and also you need to setup the path to opatch on the PATH environment variable.

The patch install is a little bit complex and each one do require several steps, check carefully the requisites as they are different depending on the patch and the configuration, in general you will see these steps on most of them: • • • • • • • • • 7/74 Download Install run catcpu Recompile with utlrp recompile_precheck Shutdown Startup upgrade view_recompile_ Shutdown REAL APPLICATION TESTING ON 10G – STEP BY STEP • • Startup Recompile with utlrp If you get from opatch errors related to the unavailability to copy some dll files, it is related to an Oracle executable still running, to identify it use the command: tasklist /m i. . : tasklist /m oran10. dll It will show the program name and PID that is blocking it. You can shutdown this process and retry opatch apply. 8/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Install patch 6998002 on Windows 10g database To install the patches prepare the environment to include the path to opatch set ORACLE_HOME=c:oracleproduct10. 2. 0db_1 set ORACLE_BASE=c:oracle set ORACLE_SID=ORCL set PATH=c:oracleproduct10. 2. 0db_1in;C:oracleproduct10. 2. 0db_1OPatch;C:Program FilesOraclejre1. 3. 1in;C:ProgramFilesOraclejre1. 1. in;C:WINDOWSsystem32;C:WINDO WS;C:WINDOWSSystem32Wbem;C:Program FilesWindows Imaging Shutdown the database and apply the patch: C:RAT-PATCHESpatch-6998002_10203_WINNT6998002>opatch apply Invoking OPatch 10. 2. 0. 3. 0 Oracle interim Patch Installer version 10. 2. 0. 3. 0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home Central Inventory from OPatch version OUI version OUI location Log file location 52AM. log : : : : : : : c:oracleproduct10. 2. 0db_1 C:Program FilesOracleInventory n/a 10. 2. 0. 3. 0 10. 2. 0. 3. 0 c:oracleproduct10. 2. db_1oui c:oracleproduct10. 2. 0db_1cfgtoollogsopatchopatch2008-09-28_11-03- ApplySession applying interim patch ‘6998002’ to OH ‘c:oracleproduct10. 2. 0db_1’ ApplySession: Optional component(s) [ oracle. rdbms. lbac, 10. 2. 0. 3. 0 ] , [ oracle. rdbms. dv, 10. 2. 0. 3. 0 ] , [ oracle. ntoledb. odp_net_2, 10. 2. 0. 2. 20 ] , [ oracle. toledb, 10. 2. 0. 2. 20 ] , [ oracle. oo4o, 10. 2. 0. 2. 20 ] , [ oracle. ntoramts, 10. 2. 0. 3. 0 ] , [ oracle. has. crs, 9/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP 10. 2. 0. 3. 0 ] , [ oracle. ldap. esm, 10. 2. 0. 1. 0 ] , [ oacle. precomp. lang, 10. 2. . 3. 0 ] , [ oracle. rdbms. dv. oc4j, 10. 2. 0. 3. 0 ] , [ oracle. rdbms. ic, 10. 2. 0. 3. 0 ] not present in the Oracle Home or a higher version is found. Subset patches: 5071931 The fixes for Patch 5071931 are included in the patch currently being installed (6998002). If you continue, they will be rolled back and the new patch (699800) will be installed. OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = ‘c:oracleproduct10. 2. 0db_1’) Is the local system eady for patching? Do you want to proceed? [y|n] y User Responded with: Y Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing up files affected by the patch ‘6998002’ for restore. This might take a while… Once opatch succeeded to apply the patch continue with the other required steps: > cd %ORACLE_HOME%BUNDLEPatch23 > sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> STARTUP SQL> @catcpu. sql SQL> QUIT 10/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP C:oracleproduct10. 2. 0db_1BUNDLEPATCH23>sqlplus “/ as sysdba” SQL*Plus: Release 10. . 0. 3. 0 – Production on Sun Sep 28 12:27:35 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10. 2. 0. 3. 0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT * FROM registry$history where ID = ‘6452863’; no rows selected @recompile_precheck_jan2008cpu. sql Running precheck. sql… Number of views to be recompiled :2404 ———————————————————————-Number of objects to be recompiled :4906 Please follow the README. xt instructions for running viewrecomp. sql PL/SQL procedure successfully completed. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade 12/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 1464608 bytes Variable Size 494757600 bytes Database Buffers 1635778560 bytes Redo Buffers 15482880 bytes Database mounted. Database opened. SQL> @@view_recompile_jan2008cpu. sql PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. 1 row created. Commit complete. No. of Invalid Objects is :2228 Please refer to README. html to for instructions on validating these objects PL/SQL procedure successfully completed. Logfile for the current viewrecomp. sql session is : vcomp_ORCL_28Sep2008_12_33_52. log SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 1464608 bytes Variable Size 494757600 bytes 13/74

REAL APPLICATION TESTING ON 10G – STEP BY STEP Database Buffers 1635778560 bytes Redo Buffers 15482880 bytes Database mounted. Database opened. SQL> @? /rdbms/admin/utlrp TIMESTAMP ——————————————————————————-COMP_TIMESTAMP UTLRP_BGN 2008-09-28 12:44:36 DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid objects in the database.

Recompilation time is proportional to the number of invalid objects in the database, so this command may take a long time to execute on a database with a large number of invalid objects. Use the following queries to track recompilation progress: 1. Query returning the number of invalid objects remaining. This number should decrease with time. SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); 2. Query returning the number of objects compiled so far. This number should increase with time.

SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; This script automatically chooses serial or parallel recompilation based on the number of CPUs available (parameter cpu_count) multiplied by the number of threads per CPU (parameter parallel_threads_per_cpu). On RAC, this number is added across all RAC nodes. UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel recompilation. Jobs are created without instance affinity so that they can migrate across RAC nodes. Use the following queries to verify 14/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP

DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC># whether UTL_RECOMP jobs are being created and run correctly: 1. Query showing jobs created by UTL_RECOMP SELECT job_name FROM dba_scheduler_jobs WHERE job_name like ‘UTL_RECOMP_SLAVE_%’; 2. Query showing UTL_RECOMP jobs that are running SELECT job_name FROM dba_scheduler_running_jobs WHERE job_name like ‘UTL_RECOMP_SLAVE_%’; PL/SQL procedure successfully completed. TIMESTAMP ——————————————————————————-COMP_TIMESTAMP UTLRP_END 2008-09-28 12:45:12

PL/SQL procedure successfully completed. DOC> The following query reports the number of objects that have compiled DOC> with errors (objects that compile with errors have status set to 3 in DOC> obj$). If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ——————257 DOC> The following query reports the number of errors caught during 5/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION ————————–331 Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK Ultra Search VALIDATE_WK done with no error PL/SQL procedure successfully completed.

In this case the invalid objects were related to some user applications and not relevant to RAT This finish the patch installation process on 10g 10. 2. 0. 3 16/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Install patch 6903335 on Windows 10g clone database To install the patches prepare the environment to include the path to opatch set ORACLE_HOME=c:oracleproduct10. 2. 0db_1 set ORACLE_BASE=c:oracle set ORACLE_SID=ORCL set PATH=c:oracleproduct10. 2. 0db_1in;C:oracleproduct10. 2. 0db_1OPatch;C:Program FilesOraclejre1. 3. 1in;C:ProgramFilesOraclejre1. . 8in;C:WINDOWSsystem32;C:WINDO WS;C:WINDOWSSystem32Wbem;C:Program FilesWindows Imaging Shutdown the database and apply the patch: C:RAT-PATCHESpatch-6903335_10203_Windiows6903335>opatch apply Invoking OPatch 10. 2. 0. 3. 0 Oracle interim Patch Installer version 10. 2. 0. 3. 0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home Central Inventory from OPatch version OUI version OUI location Log file location 52PM. log : : : : : : : c:oracleproduct10. 2. 0db_1 C:Program FilesOracleInventory n/a 10. 2. 0. 3. 0 10. . 0. 3. 0 c:oracleproduct10. 2. 0db_1oui c:oracleproduct10. 2. 0db_1cfgtoollogsopatchopatch2008-09-25_17-31- ApplySession applying interim patch ‘6903335’ to OH ‘c:oracleproduct10. 2. 0db_1’ 17/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = ‘c:oracleproduct10. 2. 0db_1’) Is the local system ready for patching? Do you want to proceed? y|n] y User Responded with: Y Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing up files affected by the patch ‘6903335’ for restore. This might take a while… Backing up files affected by the patch ‘6903335’ for rollback. This might take a while… Patching component oracle. rdbms, 10. 2. 0. 3. 0… Copying file to “c:oracleproduct10. 2. 0db_1
dbmsadminprvtspao. plb” ApplySession adding interim patch ‘6903335’ to inventory Verifying the update… Inventory check OK: Patch ID 6903335 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 6903335 are present in Oracle Home. The local system has been patched and can be restarted. OPatch succeeded. C:RAT-PATCHESpatch-6903335_10203_Windiows6903335>opatch lsinventory Invoking OPatch 10. 2. 0. 3. 0 Oracle interim Patch Installer version 10. 2. 0. 3. 0 Copyright (c) 2005, Oracle Corporation. All rights reserved.. 18/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Oracle Home Central Inventory from OPatch version OUI version OUI location Log file location 11PM. log : : : : : : : c:oracleproduct10. 2. 0db_1 C:Program FilesOracleInventory n/a 10. . 0. 3. 0 10. 2. 0. 3. 0 c:oracleproduct10. 2. 0db_1oui c:oracleproduct10. 2. 0db_1cfgtoollogsopatchopatch2008-09-25_17-42- Lsinventory Output file location : c:oracleproduct10. 2. 0db_1cfgtoollogsopatchlsinvlsinventory2008-09-25_17-42-11PM. txt ——————————————————————————-Installed Top-level Products (4): Oracle Database 10g Oracle Database 10g Products Oracle Database 10g Release 2 Patch Set 1 Oracle Database 10g Release 2 Patch Set 2 There are 4 products installed in this Oracle Home. 0. 2. 0. 1. 0 10. 2. 0. 1. 0 10. 2. 0. 2. 0 10. 2. 0. 3. 0 Interim patches (3) : Patch 6903335 : applied on Thu Sep 25 17:32:06 IDT 2008 Created on 12 May 2008, 08:56:41 hrs US/Pacific Bugs fixed: 6903335 Patch 5071931 : applied on Sun Mar 16 15:17:14 IST 2008 Created on 2 May 2007, 07:29:31 hrs PST8PDT Bugs fixed: 19/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP 5071931 Patch 5746875 : applied on Thu Feb 07 14:08:45 IST 2008 Created on 28 Feb 2007, 07:19:02 hrs US/Eastern Bugs fixed: 5126270, 5726033, 5746875 ——————————————————————————OPatch succeeded. Once patch 6903335 is successfully installed the following actions needs to be implemented Patch Special Instructions: ————————–cd $OH/rdbms/admin sqlplus / as sysdba drop table plan_table$; -@catplan. sql -@dbmsxpln. sql -@prvtxpln. plb -@prvtspao. plb — drop the plan table if was upgraded. recreate the plan table reload dbms_xplan spec reload dbms_xplan implementation reload dbms_sqlpa If the Oracle inventory is not setup correctly this utility will fail.

To check accessibility to the inventory you can use the command The execution of prvtspao. plb failed to compile DBMS_SQLPA, you can see the errors on the following log, the solution was available from Note 605317. 1 and 565600. 1 that actually solved the problem: drop plan_table$ and plan_table before retrying prvtspao. plb SQL> @? /rdbms/admin/prvtspao. plb 20/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP PL/SQL procedure successfully completed. Package created. No errors. Synonym created. No errors. Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY DBMS_SQLPA: LINE/COL ERROR ——– —————————————————–123/5 PL/SQL: SQL Statement ignored 129/44 PL/SQL: ORA-00904: “OTHER_XML”: invalid identifier SQL> alter package DBMS_SQLPA compile body; Warning: Package Body altered with compilation errors. SQL> show err Errors for PACKAGE BODY DBMS_SQLPA: LINE/COL ——-123/5 129/44 ERROR —————————————————–PL/SQL: SQL Statement ignored PL/SQL: ORA-00904: “OTHER_XML”: invalid identifier This finish the patch installation process on 10g 10. 2. 0. 3 21/74

REAL APPLICATION TESTING ON 10G – STEP BY STEP Install patch 7044721 on Windows 11g test database Setup the environment to 11g set ORACLE_HOME=D:ORACLE11gdb11g set ORACLE_BASE=D:ORACLE11g set ORACLE_SID=REALAPT set PATH=D:ORACLE11gdb11gin;D:ORACLE11gdb11gOPatch;C:Program FilesOraclejre1. 3. 1in;C:Program FilesOraclejre1. 1. 8in;C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem; Shutdown all resources running on the 11g home and execute opatch apply from the patch directory C:RAT-PATCHESpatch-7044721_111060_WINNT7044721>opatch apply File Not Found Invoking OPatch 11. 1. 0. 6. Oracle Interim Patch Installer version 11. 1. 0. 6. 0 Copyright (c) 2007, Oracle Corporation. All rights reserved. Oracle Home Central Inventory from OPatch version OUI version OUI location Log file location : : : : : : : D:ORACLE11gdb11g C:Program FilesOracleInventory n/a 11. 1. 0. 6. 0 11. 1. 0. 6. 0 D:ORACLE11gdb11goui D:ORACLE11gdb11gcfgtoollogsopatchopatch2008-09-25_18-14-23PM. log ApplySession applying interim patch ‘7044721’ to OH ‘D:ORACLE11gdb11g’ Running prerequisite checks… 22/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Patch 7044721: Optional component(s) missing : [ oracle. rdbms. c, 11. 1. 0. 6. 0 ] , [ oracle. rdbms. tg4msql, 11. 1. 0. 6. 0 ] , [ oracle. sysman. agent, 11. 1. 0. 6. 0 ] , [ oracle. ntoledb. odp_net, 11. 1. 0. 6. 20 ] , [ oracle. ntoledb. odp_net_2, 11. 1. 0. 6. 20 ] OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = ‘D:ORACLE11gdb11g’) Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files and inventory (not for auto-rollback) for the Oracle Home Backing up files affected by the patch ‘7044721’ for restore.

This might take a while… Backing up files affected by the patch ‘7044721’ for rollback. This might take a while… Execution of ‘cmd /C “C:RAT-PATCHESpatch7044721_111060_WINNT7044721customscriptspre. bat” -apply 7044721 ‘: Return Code = 0 Patching component oracle. rdbms. rsf, 11. 1. 0. 6. 0… Copying file to “D:ORACLE11gdb11ginoraclient11. dll” Copying file to “D:ORACLE11gdb11g
dbmsadminoraclient11. sym” Copying file to “D:ORACLE11gdb11ginorageneric11. dll” Copying file to “D:ORACLE11gdb11ginorapls11. dll” Copying file to “D:ORACLE11gdb11g
dbmsadminorageneric11. ym” Copying file to “D:ORACLE11gdb11g
dbmsadminorapls11. sym” Copying file to “D:ORACLE11gdb11gliborapls11. lib” Copying file to “D:ORACLE11gdb11gplsqlmesgplwus. msb” Patching component oracle. rdbms, 11. 1. 0. 6. 0… Updating jar file “D:ORACLE11gdb11g
dbmsjlibCDC. jar” with ”
dbmsjlibCDC. jaroracleCDCAdvanceChangeSet. class” 23/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Copying file to “D:ORACLE11gdb11ginoracle. exe” Copying file to “D:ORACLE11gdb11g
dbmsadminoracle. sym” Copying file to “D:ORACLE11gdb11g
dbmsadmincatplan. ql” Copying file to “D:ORACLE11gdb11g
dbmsadmindbmsspa. sql” Copying file to “D:ORACLE11gdb11g
dbmsadmindbmssqlt. sql” Copying file to “D:ORACLE11gdb11g
dbmsadmindbmssqlu. sql” Copying file to “D:ORACLE11gdb11g
dbmsadmindbmsxpln. sql” Copying file to “D:ORACLE11gdb11g
dbmsadminprvsspai. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvssqli. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtxpln. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvsadv. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtadv. lb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtdadv. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtspa. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtspai. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtsqlu. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtsqli. plb” Copying file to “D:ORACLE11gdb11g
dbmsadminprvtsqlt. plb” Copying file to “D:ORACLE11gdb11gBundleview_recompileview_recompile_bundlepatch4. sql” Copying file to “D:ORACLE11gdb11gBundleview_recompile
ecompile_precheck_bundlepatch4. ql” Copying file to “D:ORACLE11gdb11gBundlePatch4catcpu. sql” Copying file to “D:ORACLE11gdb11gBundlePatch4catcpu_rollback. sql” Copying file to “D:ORACLE11gdb11gBundlePatch4sdocpu. sql” Copying file to “D:ORACLE11gdb11gBundlePatch4javcpu. sql” Patching component oracle. oracore. rsf, 11. 1. 0. 6. 0… Copying file to “D:ORACLE11gdb11ginoracore11. dll” Copying file to “D:ORACLE11gdb11g
dbmsadminoracore11. sym” Copying file to “D:ORACLE11gdb11gliboracore11. lib” Patching component oracle. sdo. locator, 11. 1. 0. 6. 0… Copying file to “D:ORACLE11gdb11gmdadminprvtpidx. lb” 24/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Patching component oracle. ons, 11. 1. 0. 5. 0… Copying file to “D:ORACLE11gdb11gopmninons. exe” Patching component oracle. rdbms. rsf. ic, 11. 1. 0. 6. 0… Copying file to “D:ORACLE11gdb11ginoci. dll” Patching component oracle. rdbms. oci, 11. 1. 0. 6. 0… Copying file to “D:ORACLE11gdb11gocilibmsvcoci. lib” Patching component oracle. rdbms. hsodbc, 11. 1. 0. 6. 0… Copying file to “D:ORACLE11gdb11gindg4odbc. exe” ApplySession adding interim patch ‘7044721’ to inventory Verifying the update…

Inventory check OK: Patch ID 7044721 is registered in Oracle Home inventory with proper meta-data. Files check OK: Files from Patch ID 7044721 are present in Oracle Home. ——————————————————————————-******************************************************************************** ** ATTENTION ** ** ** ** Please note that the Patch Installation (Patch Deinstallation) is not ** ** complete until all the Post Installation (Post Deinstallation) ** ** instructions noted in the Readme accompanying this patch, have been ** ** successfully completed. * ** ** ***************************************************************************** *** ******************************************************************************** ——————————————————————————-Execution of ‘cmd /C “C:RAT-PATCHESpatch7044721_111060_WINNT7044721customscriptspost. bat” -apply 7044721 ‘: 25/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Return Code = 0 The local system has been patched and can be restarted. OPatch succeeded.

After the patch the script catcpu located on directory $ORACLE_HOME BundlePatch4 needs to be executed D:ORACLE11gdb11gBundlePatch4>sqlplus / as sysdba SQL> @catcpu. sql Session altered. Session altered. PL/SQL procedure successfully completed. TIMESTAMP ——————————————————————————-COMP_TIMESTAMP CPU_BGN 2008-09-25 18:29:45 PL/SQL procedure successfully completed. Library created. No errors. Library created. Package created. No errors. … … (more output) …

TIMESTAMP ——————————————————————————-COMP_TIMESTAMP SDO 2008-09-25 18:30:30 DBUA_TIMESTAMP SDO VALID 2008-09-25 18:30:30 26/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP 0 rows deleted. 1 row created. PL/SQL procedure successfully completed. TIMESTAMP ——————————————————————————-COMP_TIMESTAMP CPU_END 2008-09-25 18:30:30 No. of Invalid Objects is :28 Please refer to README. html to for instructions on validating these objects PL/SQL procedure successfully completed.

Logfile for the current catcpu. sql session is : APPLY_REALAPT_25Sep2008_18_29_45. log SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’; OBJECT_NAME ——————————————————————————-DBA_HIST_SQLBIND DBA_HIST_SQLBIND DBA_SQLSET_BINDS USER_SQLSET_BINDS USER_SQLSET_BINDS ALL_SQLSET_BINDS ALL_SQLSET_BINDS PRVT_REPORT_TAGS PRVT_SQLADV_INFRA PRVT_SQLSET_INFRA PRVT_SQLPROF_INFRA DBMS_MANAGEMENT_PACKS PRVT_HDM WRI$_ADV_HDM_T DBMS_UNDO_ADV 27/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP

DBMS_SPM DBMS_SMB_INTERNAL DBMS_SMB PRVT_WORKLOAD_NOPRIV WRI$_ADV_SQLACCESS_ADV WRI$_ADV_TUNEMVIEW_ADV PRVT_PARTREC_NOPRIV DBMS_XPLAN DBMS_STATS_INTERNAL DBMS_SQLDIAG_INTERNAL DBMS_SQLTCB_INTERNAL DBMS_SQLDIAG DBMS_CUBE_ADVISE 28 rows selected. SQL> @? /rdbms/admin/utlrp TIMESTAMP ——————————————————————————-COMP_TIMESTAMP UTLRP_BGN 2008-09-25 18:37:53 DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid objects in the database.

Recompilation time is proportional to the number of invalid objects in the database, so this command may take a long time to execute on a database with a large number of invalid objects. Use the following queries to track recompilation progress: 1. Query returning the number of invalid objects remaining. This number should decrease with time. SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); 28/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC># 2.

Query returning the number of objects compiled so far. This number should increase with time. SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; This script automatically chooses serial or parallel recompilation based on the number of CPUs available (parameter cpu_count) multiplied by the number of threads per CPU (parameter parallel_threads_per_cpu). On RAC, this number is added across all RAC nodes. UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel recompilation. Jobs are created without instance affinity so that they can migrate across RAC nodes.

Use the following queries to verify whether UTL_RECOMP jobs are being created and run correctly: 1. Query showing jobs created by UTL_RECOMP SELECT job_name FROM dba_scheduler_jobs WHERE job_name like ‘UTL_RECOMP_SLAVE_%’; 2. Query showing UTL_RECOMP jobs that are running SELECT job_name FROM dba_scheduler_running_jobs WHERE job_name like ‘UTL_RECOMP_SLAVE_%’; PL/SQL procedure successfully completed. TIMESTAMP ——————————————————————————-COMP_TIMESTAMP UTLRP_END 2008-09-25 18:38:21 PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled 29/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP DOC> with errors (objects that compile with errors have status set to 3 in DOC> obj$). If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ——————0 DOC> The following query reports the number of errors caught during DOC> recompilation.

If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION ————————–0 PL/SQL procedure successfully completed. Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK Ultra Search VALIDATE_WK done with no error PL/SQL procedure successfully completed. SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’; no rows selected 30/74

REAL APPLICATION TESTING ON 10G – STEP BY STEP After that need to check if view recompilation was done, this select should return one row, if not perform view recompilation SQL> SELECT * FROM registry$history where ID = ‘6452863’; no rows selected exit cd D:ORACLE11gdb11gBundleview_recompile D:ORACLE11gdb11gBundleview_recompile>sqlplus / as sysdba SQL> @recompile_precheck_bundlepatch4. sql Running precheck. sql… Number of views to be recompiled :2733 ———————————————————————-Number of objects to be recompiled :5543 Please follow the README. xt instructions for running viewrecomp. sql PL/SQL procedure successfully completed. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. Database opened. 292933632 1332752 226494960 58720256 6385664 bytes bytes bytes bytes bytes 31/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP SQL> @view_recompile_bundlepatch4. sql PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. row created. Commit complete. No. of Invalid Objects is :33 Please refer to README. html to for instructions on validating these objects PL/SQL procedure successfully completed. Logfile for the current viewrecomp. sql session is : vcomp_REALAPT_28Sep2008_09_26_40. log SQL> @? /rdbms/admin/utlrp TIMESTAMP ——————————————————————————-COMP_TIMESTAMP UTLRP_BGN 2008-09-28 09:31:05 DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid objects in the database.

Recompilation time is proportional to the number of invalid objects in the database, so this command may take a long time to execute on a database with a large number of invalid objects. Use the following queries to track recompilation progress: 1. Query returning the number of invalid objects remaining. This number should decrease with time. SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); 32/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC> DOC># 2.

PL/SQL procedure successfully completed. DOC> The following query reports the number of objects that have compiled 33/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP DOC> with errors (objects that compile with errors have status set to 3 in DOC> obj$). If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects.

DOC># OBJECTS WITH ERRORS ——————0 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION ————————–0 PL/SQL procedure successfully completed.

Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK Ultra Search VALIDATE_WK done with no error PL/SQL procedure successfully completed. SQL> SQL> shutdown immediate Database closed. Database dismounted. 34/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area Fixed Size Variable Size Database Buffers Redo Buffers Database mounted. Database opened. SQL> 292933632 1332752 226494960 58720256 6385664 bytes bytes bytes bytes bytes

This finish the patch installation process on 11g 11. 1. 0. 6 35/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Configure and execute functional RAT tests on Windows Create A Small Test Inside To Clone Of Production To Test On Linux >sqlplus “/ as sysdba” SQL> create tablespace avtest datafile ‘E:ORACLEORADATAORCLAVTST_01_TBS. DBF’ size 100m; Tablespace created. SQL> create user avargas identified by oracle default tablespace avtest temporary tablespace temp; User created. SQL> grant dba , all privileges to avargas; Grant succeeded.

SQL> connect avargas/oracle Connected. SQL> create table test1 as select * from dba_source; Table created. SQL> create table test2 as select * from dba_users; Table created. Test Capture On The Production Clone Parameter PRE_11G_ENABLE_CAPTURE=TRUE is required only on 10. 2. 0. 4 lower releases do not need to set it. 36/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Before capturing the database may be restarted to assure all transactions are captured, otherwise uncommitted transactions may be left out of the captured load.

Workload Capture Restrictions The following types of client requests are not captured in a workload in the current release: • • • • • • • • • • Direct path load of data from external files using utilities such as SQL*Loader Shared server requests (Oracle MTS) Oracle Streams Advanced Replication streams Non-PL/SQL based Advanced Queuing (AQ) Flashback queries Oracle Call Interface (OCI) based object navigations Non SQL-based object access Distributed transactions (any distributed transactions that are captured will be replayed as local transactions) Remote DESCRIBE and COMMIT operations

Using Database Replay requires four main steps • • • • Workload Capture Workload Preprocessing Workload Replay Analysis and Reporting Capturing a Database Workload Using APIs Capturing a database workload using the DBMS_WORKLOAD_CAPTURE package involves: 37/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP • • • • Adding and Removing Workload Filters Starting a Workload Capture Stopping a Workload Capture Exporting AWR Data for Workload Capture Create a filter for the session to capture BEGIN DBMS_WORKLOAD_CAPTURE.

ADD_FILTER ( fname => ‘user_avargas’, fattribute => ‘USER’, fvalue => ‘AVARGAS’); END; / SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE. ADD_FILTER ( 3 fname => ‘user_avargas’, 4 fattribute => ‘USER’, 5 fvalue => ‘AVARGAS’); 6 END; 7 / PL/SQL procedure successfully completed. To disable the filter can use: BEGIN DBMS_WORKLOAD_CAPTURE. DELETE_FILTER (fname => ‘AVARGAS’); END; / 38/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Create a Directory to store the captured load SQL> create or replace directory loadcapt as ‘E:RAT_LOAD_CAPTURE’; Directory created.

SQL> grant read , write on directory loadcapt to public; Grant succeeded. Start the Capture process BEGIN DBMS_WORKLOAD_CAPTURE. START_CAPTURE (name => ‘ORCL Test Capture’, dir => ‘LOADCAPT’, duration => 600); END; / SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE. START_CAPTURE (name => ‘ORCL Test Capture’, 3 dir => ‘LOADCAPT’, 4 duration => 600); 5 END; 6 / PL/SQL procedure successfully completed. In this example the filter previously created is considered an exclusion filter, user avargas is excluded from the capture. 39/74

REAL APPLICATION TESTING ON 10G – STEP BY STEP To use it as an inclusion filter for the user defined on it add the parameter “default_action => EXCLUDE” that indicates to exclude all users except the user or users included in a defined filter. The default action used is INCLUDE, if not defined will be used and any defined user on a filter will be excluded. BEGIN DBMS_WORKLOAD_CAPTURE. START_CAPTURE (name => ‘ORCL Test Capture 2’, dir => ‘LOADCAPT’, duration => 600, default_action => ‘EXCLUDE’); END; / Make some load as avargas

SQL> delete from test1 where substr(name,1,10)=’MMS_TIPUL_’; 26740 rows deleted. SQL> commit; Commit complete. SQL> delete from test1 where substr(name,1,10)=’MRK_PIZUR_’; 23849 rows deleted. SQL> commit; Commit complete. SQL> delete from test1 where substr(name,1,5)like ‘MGMT’; 0 rows deleted. SQL> delete from test1 where substr(name,1,5)like ‘MGMT_’; 62217 rows deleted. 40/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP SQL> rollback; Rollback complete. SQL> delete from test1 where substr(name,1,5)like ‘MGMT_’; 62217 rows deleted.

SQL> commit; Commit complete. Stopping a Workload Capture To stop the workload capture, use the FINISH_CAPTURE procedure: BEGIN DBMS_WORKLOAD_CAPTURE. FINISH_CAPTURE (); END; / SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE. FINISH_CAPTURE (); 3 END; 4 / PL/SQL procedure successfully completed. Check capture process ID and status COL NAME FOR A25 COL DIRECTORY FOR A15 COL STATUS FOR A20 SELECT ID,NAME,DIRECTORY,STATUS FROM DBA_WORKLOAD_CAPTURES / 41/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP

SQL> COL NAME FOR A25 SQL> COL DIRECTORY FOR A15 SQL> COL STATUS FOR A20 SQL> SELECT ID,NAME,DIRECTORY,STATUS FROM DBA_WORKLOAD_CAPTURES / ID NAME DIRECTORY STATUS ———- ————————- ————— ——————-1 ORCL Test Capture LOADCAPT COMPLETED After second run SQL> SQL> SQL> SQL> 2 COL NAME FOR A25 COL DIRECTORY FOR A15 COL STATUS FOR A20 SELECT ID,NAME,DIRECTORY,STATUS FROM DBA_WORKLOAD_CAPTURES / NAME ————————ORCL Test Capture ORCL Test Capture 2 DIRECTORY ————–LOADCAPT LOADCAPT STATUS ——————-COMPLETED COMPLETED

ID ———1 3 42/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Exporting AWR Data for Workload Capture Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run the AWR Compare Period report on a pair of workload captures or replays. To export AWR data, use the EXPORT_AWR procedure: BEGIN DBMS_WORKLOAD_CAPTURE. EXPORT_AWR (capture_id => 3); END; / SQL> BEGIN 2 DBMS_WORKLOAD_CAPTURE. EXPORT_AWR (capture_id => 1); 3 END; 4 / PL/SQL procedure successfully completed. Check the Capture process directory and files

E: >CD E:RAT_LOAD_CAPTURE E:RAT_LOAD_CAPTURE>dir Volume in drive E is New Volume Volume Serial Number is 9870-7791 Directory of E:RAT_LOAD_CAPTURE 09/28/2008 09/28/2008 03:30 PM 03:30 PM . .. 43/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 03:21 PM 1,112 wcr_4jrvp5w0021m8. rec 03:14 PM 1,125 wcr_4jrvp5w0025us. rec 03:22 PM 1,400 wcr_4jrvqsn0024yw. rec 03:42 PM 1,586,958,336 WCR_CA. DMP 03:42 PM 29,543 wcr_ca. log 03:22 PM 39,070 wcr_cr. html 03:22 PM 19,720 wcr_cr. text 03:22 PM 199 wcr_fcapture. md 03:14 PM 134 wcr_scapture. wmd 9 File(s) 1,587,050,639 bytes 2 Dir(s) 4,089,331,712 bytes free The same directory after having removed the previous files and run the 2nd Capture E:RAT_LOAD_CAPTURE>dir Volume in drive E is New Volume Volume Serial Number is 9870-7791 Directory of E:RAT_LOAD_CAPTURE 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 04:09 04:09 04:03 04:08 04:04 04:04 04:04 04:04 04:04 04:04 04:04 04:04 04:04 04:04 PM PM PM PM PM PM PM PM PM PM PM PM PM PM 741 33,280 733 733 733 734 734 734 734 733 733 67 . . wcr_4jrw0r800207c. rec wcr_4jrw0sc00258n. rec wcr_4jrw0y0002074. rec wcr_4jrw0y00020mw. rec wcr_4jrw0y00020t0. rec wcr_4jrw0y00021zc. rec wcr_4jrw0y000248s. rec wcr_4jrw0y00024q8. rec wcr_4jrw0y00024rs. rec wcr_4jrw0y00025j4. rec wcr_4jrw0y00025r0. rec wcr_4jrw0yc0020nh. rec 44/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP 9/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 09/28/2008 04:04 PM 04:04 PM 04:05 PM 04:05 PM 04:05 PM 04:06 PM 04:06 PM 04:06 PM 04:07 PM 04:07 PM 04:07 PM 04:08 PM 04:08 PM 04:08 PM 04:09 PM 04:09 PM 04:09 PM 04:09 PM 04:03 PM 31 File(s) 2 Dir(s) 68 wcr_4jrw0yc00225c. rec 68 wcr_4jrw0yc0024bh. rec 68 wcr_4jrw15w0021z0. rec 68 wcr_4jrw15w0024a4. rec 68 wcr_4jrw15w0024rn. rec 68 wcr_4jrw1dc0021zn. rec 68 wcr_4jrw1dc0022q0. ec 68 wcr_4jrw1dc0024qh. rec 68 wcr_4jrw1nw002100. rec 68 wcr_4jrw1nw0024n0. rec 68 wcr_4jrw1nw0025sn. rec 67 wcr_4jrw1wc0020p4. rec 68 wcr_4jrw1wc0021bh. rec 68 wcr_4jrw1wc0025yc. rec 983 wcr_4jrw1zc0022sh. rec 38,582 wcr_cr. html 19,376 wcr_cr. text 203 wcr_fcapture. wmd 136 wcr_scapture. wmd 100,920 bytes 5,676,171,264 bytes free After completing the run gather awr statistics BEGIN DBMS_WORKLOAD_CAPTURE. EXPORT_AWR (capture_id => 3); END; / 16:20:35 SQL> BEGIN 16:20:42 2 DBMS_WORKLOAD_CAPTURE. EXPORT_AWR (capture_id => 3); 16:20:42 3 END; 16:20:42 4 / 5/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP PL/SQL procedure successfully completed. Elapsed: 00:14:32. 87 The export_awr generated this file on the capture directory for capture_id 3: 09/28/2008 04:35 PM 1,588,432,896 WCR_CA. DMP Generating a Workload Capture Report Using APIs Before applying the captured workload it is convenient to check it. To do that we use the workload capture report that contains captured workload statistics, information about the top session activities that were captured, and any workload filters used during the capture process.

To generate a report on the latest workload capture, use the DBMS_WORKLOAD_CAPTURE. GET_CAPTURE_INFO procedure and the DBMS_WORKLOAD_CAPTURE. REPORT function: DECLARE cap_id NUMBER; cap_rpt CLOB; BEGIN cap_id := DBMS_WORKLOAD_CAPTURE. GET_CAPTURE_INFO(dir => ‘LOADCAPT’); cap_rpt := DBMS_WORKLOAD_CAPTURE. REPORT(capture_id => 3, format => DBMS_WORKLOAD_CAPTURE. TYPE_TEXT); END; / 46/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Workload Preprocessing Once the workload has been captured, the information in the capture files need to be preprocessed.

Preprocessing creates all necessary metadata needed for replaying the workload. This must be done once for every captured workload before they can be replayed. After the captured workload is preprocessed, it can be replayed repeatedly on a replay system running the same version of Oracle Database. On the 11g database create a directory pointing to the directory where the capture load files are located create or replace directory LOADCAPT as ‘E:RAT_LOAD_CAPTURE’; grant read , write on directory LOADCAPT to public; SQL> EXEC DBMS_WORKLOAD_REPLAY.

PROCESS_CAPTURE(capture_dir=>’LOADCAPT’); PL/SQL procedure successfully completed. Workload Replay Workload reply has the following stages: Setting Up the Test System: You need to prepare the database to replay, usually you will make a backup of the database where the originated workload was captured, and upgrade it or migrate it to match the new scenario. On this case I’m using an export from the 10g database and import on the 11g database: Export file: EXPDAT. DMP > avargas. dmp 47/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP 1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2 Export grants (yes/no): yes > Export table data (yes/no): yes > Compress extents (yes/no): yes > Export done in IW8ISO8859P8 character set and AL16UTF16 NCHAR character set About to export specified users … User to be exported: (RETURN to quit) > avargas User to be exported: (RETURN to quit) > . exporting pre-schema procedural objects and actions . exporting foreign function library names for user AVARGAS . exporting PUBLIC type synonyms . exporting private type synonyms . xporting object type definitions for user AVARGAS About to export AVARGAS’s objects … . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export AVARGAS’s tables via Conventional Path … . . exporting table TEST1 562461 rows exported . . exporting table TEST2 0 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . xporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions 48/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully without warnings. On 11g: SQL> CREATE TABLESPACE AVTEST ; Tablespace created. SQL> CREATE USER AVARGAS IDENTIFIED BY ORACLE DEFAULT TABLESPACE AVTEST TEMPORARY TABLESPACE TEMP; User created.

SQL> GRANT DBA, ALL PRIVILEGES TO AVARGAS; Grant succeeded. E:RAT_LOAD_CAPTURE>imp avargas Import: Release 11. 1. 0. 6. 0 – Production on Thu Oct 2 12:34:27 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11. 1. 0. 6. 0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Import file: EXPDAT. DMP > avargas. dmp Enter insert buffer size (minimum is 8192) 30720> Export file created by EXPORT:V10. 02. 01 via conventional path import done in

IW8MSWIN1255 character set and AL16UTF16 NCHAR character set import server uses IW8ISO8859P8 character set (possible charset conversion) export client uses IW8ISO8859P8 character set (possible charset conversion) List contents of import file only (yes/no): no > Ignore create error due to object existence (yes/no): no > Import grants (yes/no): yes > Import table data (yes/no): yes > Import entire export file (yes/no): no > yes . importing AVARGAS’s objects into AVARGAS 49/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP . . importing table . . importing table “TEST1” “TEST2” 562461 rows imported 0 rows imported

Import terminated successfully without warnings. Steps for Replaying a Database Workload Replay is implemented invoking the wrc utility from the 11g $ORACLE_HOME/bin directory. First we run it using the calibrate option, that provide information on the captured load that we can use to execute the replay: E:RAT_LOAD_CAPTURE;d: D:ORACLE11gdb11gBIN;cd D:ORACLE11gdb11gin D:ORACLE11gdb11gBIN;wrc mode=calibrate replaydir=E:RAT_LOAD_CAPTURE Workload Replay Client: Release 11. 1. 0. 6. 0 – Production on Thu Oct 2 12:45:27 2008 Copyright (c) 1982, 2007, Oracle.

All rights reserved. Report for Workload in: E:RAT_LOAD_CAPTURE ———————-Recommendation: Consider using at least 1 clients divided among 1 CPU(s). Workload Characteristics: – max concurrency: 1 sessions – total number of sessions: 1 Assumptions: – 1 client process per 50 concurrent sessions – 4 client process per CPU – think time scale = 100 – connect time scale = 100 50/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP – synchronization = TRUE Prepare the Replay Database Before actually replaying the load we need to prepare the replay database BEGIN DBMS_WORKLOAD_REPLAY. nitialize_replay ( replay_name => ‘AVTEST_REPLAY’, replay_dir => ‘LOADCAPT’); DBMS_WORKLOAD_REPLAY. prepare_replay ( synchronization => TRUE); END; / SQL> BEGIN 2 DBMS_WORKLOAD_REPLAY. initialize_replay ( 3 replay_name => ‘AVTEST_REPLAY’, 4 replay_dir => ‘LOADCAPT’); 5 DBMS_WORKLOAD_REPLAY. prepare_replay ( 6 synchronization => TRUE); 7 END; 8 / PL/SQL procedure successfully completed. Execute the Replay Database Now we can execute the wrc utility to run the load: D:ORACLE11gdb11gBIN>wrc avargas/ORACLE@REALAPT mode=replay replaydir=E:RAT_LOAD_CAPTURE 51/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP

Workload Replay Client: Release 11. 1. 0. 6. 0 – Production on Thu Oct 2 13:12:10 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (13:12:10) Left this window open and open a new window for the next step From within sqlplus start the replay To actually start the replay we need to execute DBMS_WORKLOAD_REPLAY. start_replay from within sqlplus BEGIN DBMS_WORKLOAD_REPLAY. start_replay; END; / E:RAT_LOAD_CAPTURE;sqlplus / as sysdba SQL; BEGIN 2 DBMS_WORKLOAD_REPLAY. start_replay; 3 END; 4 / PL/SQL procedure successfully completed. The wrc utility will show that replay started

D:ORACLE11gdb11gBIN;wrc avargas/ORACLE@REALAPT mode=replay replaydir=E:RAT_LOAD_CAPTURE Workload Replay Client: Release 11. 1. 0. 6. 0 – Production on Thu Oct 2 13:12:10 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Wait for the replay to start (13:12:10) Replay started (13:15:46) Replay finished (13:23:02) 52/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Once the replay finish we can analyze its performance SQL; COLUMN name FORMAT A20 SQL; SELECT id, name FROM dba_workload_replays; ID NAME ———- ——————-1 AVTEST_REPLAY DECLARE l_report CLOB; BEGIN l_report := DBMS_WORKLOAD_REPLAY. eport(replay_id => 1, format => DBMS_WORKLOAD_REPLAY. TYPE_HTML); END; / SQL> DECLARE 2 l_report CLOB; 3 BEGIN 4 l_report:= DBMS_WORKLOAD_REPLAY. report(replay_id => 1, format => DBMS_WORKLOAD_REPLAY. TYPE_HTML); 5 END; 6 / PL/SQL procedure successfully completed. It is possible to check the awr reports the replay span to further check replay performance SQL> SQL> 1* SQL> col name for a20 l select id,name,AWR_BEGIN_SNAP,AWR_END_SNAP from DBA_WORKLOAD_REPLAYS / 53/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP

ID NAME AWR_BEGIN_SNAP AWR_END_SNAP ———- ——————– ————– ———–1 AVTEST_REPLAY 167 168 SQL> @? /rdbms/admin/awrrpt Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ———– ———— ——– ———–3981543668 REALAPT 1 realapt Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter ‘html’ for an HTML report, or ‘text’ for plain text Defaults to ‘html’ Enter value for report_type: text Type Specified: … .. text End of Report Report written to awrrpt_1_167_168. txt 54/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP SQL Performance Analyzer SQL Performance Analyzer Workflow Steps Capture the SQL workload: Capture the set of SQL statements in a SQL Tuning Set (STS) Measure the performance of the workload before the change: SQL Performance Analyzer executes the SQL statements captured in the SQL Tuning Set and generates execution plans and execution statistics for each statement.

Make a change: Make the change whose effect on SQL performance you intend to measure. SQL Performance Analyzer can analyze the effect of many types of system changes. For example, you can test a database upgrade, new index creation, initialization parameter changes, optimizer statistics refresh, and so on. Measure the performance of the workload after the change: After you have made the planned change, SQL Performance Analyzer re-executes the SQL statements and produces execution plans and execution statistics for each SQL statement a second time.

This execution result represents a new set of performance data that SQL Performance Analyzer uses for subsequent comparison. Compare performance: Compares the performance of SQL statements before and after the change and produce a report identifying any changes in execution plans or performance of the SQL statements. Capture the SQL workload using API’s To capture the SQL workload we use the DBMS_SQLPA package 55/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP Main Functions of the DBMS_SQLPA Package

Function CREATE_ANALYSIS_TASK SET_ANALYSIS_TASK_PARAMETER EXECUTE_ANALYSIS_TASK DROP_ANALYSIS_TASK REPORT_ANALYSIS_TASK Purpose Create a SQL Performance Analyzer task to process and analyze a SQL Tuning Set Set a SQL analysis task parameter value Run a previously created task Drop a task, deleting all performance data Generate a report of analysis task results Create An Empty Sql Tuning Set To Be Populated With Some Load BEGIN DBMS_SQLTUNE. CREATE_SQLSET( sqlset_name =; ‘RATSQLATEST’, description =; ‘RAT SQL Analyzer Test’); END; / SQL; BEGIN 2 DBMS_SQLTUNE.

CREATE_SQLSET( 3 sqlset_name =; ‘RATSQLATEST’, 4 description =; ‘RAT SQL Analyzer Test’); 5 END; 6 / PL/SQL procedure successfully completed. Load SQL Statements on the STS First we can create a simulated load, i. e. : 56/74 REAL APPLICATION TESTING ON 10G – STEP BY STEP E:RAT_SQL_ANALYZE;sqlplus avargas/oracle SQL; @runload Table truncated. 678266 rows created. 236 rows created. 678266 rows deleted. Commit complete. That keeps running in a cycle for a while, and then we check the snapshot id’s and create a new AWR snapshot EXEC dbms_workload_repository. reate_snapshot; SQL; EXEC dbms_workload_repository. create_snapshot; PL/SQL procedure successfully completed. Now we get the last snapshot_ids and use them to gather the sql statement Snap Instance DB Name Snap Id Snap Started Level ———— ———— ——— —————— —-ORCL ORCL 17388 02 Oct 2008 15:00 1 17389 02 Oct 2008 15:12 1 The LOAD_SQLSET procedure populates the STS with selected SQL statements. An STS can be loaded from statements coming from the workload repository, another STS, or the cursor cache.

For both the workload repository and STS, predefined table functions can be used to select columns from the source to populate a new STS. 57/74 REAL APPLICATION T

Oracle Real Application Testing 10g

Related posts: