Database Administrator

Oracle 11g2 Database Replay Features

Before system changes are made, such as hardware and software upgrades, extensive testing is usually performed in a test environment to validate the changes. However, despite the testing, the new system often experiences unexpected behavior when it enters production because the testing was not performed using a realistic workload. The inability to simulate a realistic workload during testing is one of the biggest challenges when validating system changes

Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to fully assess the impact of the change, including undesired results, new contention points, or plan regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergence.

Database Replay performs workload capture of external client workload at the database level and has negligible performance overhead. Capturing the production workload eliminates the need to develop simulation workloads or scripts, resulting in significant cost reduction and time savings. By using Database Replay, realistic testing of complex applications that previously took months using load simulation tools can now be completed in days. This enables you to rapidly test changes and adopt new technologies with a higher degree of confidence and at lower risk.

You can use Database Replay to test any significant system changes, including:

Database and operating system upgrades

Configuration changes, such as conversion of a database from a single instance to an Oracle Real Application Clusters (Oracle RAC) environment

Storage, network, and interconnect changes

Operating system and hardware migrations

You can use Database Replay to capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to test the effects of a system change without affecting the production system.

Database Replay supports workload capture on a system running Oracle Database 10g Release 2 and newer releases. In order to capture a workload on a system running Oracle Database 10g Release 2, the database version can be 10.2.0.4 or higher. Workload replay is only supported on systems running Oracle Database 11g Release 1 and newer releases.

Before capturing a database workload, carefully consider the following options:

*Restarting the Database

–to avoid capture partial transaction

*Defining the Workload Filters

–inclusion filters or exclusion filters, not both

*Setting Up the Capture Directory

Oracle 11g2 Database Replay Steps
Create Tablespace “USERS”   SQL>CREATE TABLESpace users DATAFILE ‘/u01/oracle/dbdata/tstd11s/data/user01.dbf’ size 500m autoextend on maxsize 1g;  Create DIRECTORY to store Replay related files.
SQL>CREATE OR REPLACE DIRECTORY dbreplay_capture_dir AS ‘/u01/oracle/dbdata/dbreplay_capture/’;  Start Capture process
SQL>
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name     => ‘tst_capture_7′,
                                       dir      => ‘dbreplay_CAPTURE_DIR’,                                       duration => NULL);
END;
/  These below activities will be capture in replay process
SQL>CREATE USER dbreplay_test IDENTIFIED BY dbreplay_test QUOTA UNLIMITED ON users;SQL>GRANT CONNECT, CREATE TABLE TO dbreplay_test;SQL>CONN dbreplay_test/dbreplay_test
SQL>CREATE TABLE dbreplay_test_tab (   id           NUMBER,
description VARCHAR2(50), CONSTRAINT dbreplay_test_tab_pk PRIMARY KEY (id)
) tablespace users;
SQL>
BEGIN FOR i IN 1 .. 100000 LOOP    INSERT INTO dbreplay_test_tab (id, description)    VALUES (i, ‘Description for ‘ || i);
END LOOP; COMMIT;
END;
/ To stop the replay process
SQL>CONN / AS SYSDBA
SQL>
BEGIN DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
To find a Capture ID, Name
SQL>conn / as sysdbaSQL> SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info(‘dbreplay_CAPTURE_DIR’)
FROM   dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(‘dbreplay_CAPTURE_DIR’)
—————————————————————                                                             8
SQL> COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;         8 tst_capture_7
To get report
DECLARE
l_report CLOB;
BEGIN
l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 8,format    => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;

Database Administrator

Wednesday, May 9, 2012

Analyze Queue Table (locked table)

Tuesday, May 8, 2012

Oracle 11g2 Database Replay

Followers

Labels