Step by step for Oracle 11g Database Replay feature

Step by step for Oracle 11g2 Database Replay feature 
 
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 sysdba
SQL> 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;

To export the replay work
 
BEGIN
  DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 8);
END;

 
— At Test Database —– 
 
To create tablespace “USER” 
 
SQL>CREATE TABLESpace users DATAFILE ‘/u01/oracle/dbdata/dbat01/data/dbat01_user01.dbf’ size 500m autoextend on maxsize 1g; 
 
Create DIRECTORY to copy replay files
 
SQL>CREATE OR REPLACE DIRECTORY dbreplay_capture_dir AS ‘/u01/oracle/dbdata/dbreplay_capture/’; 
 
To initial and prepare
SQL>
BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture(‘dbreplay_CAPTURE_DIR’); 
   DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => ‘tst_capture_7’,
                                          replay_dir
  => ‘dbreplay_CAPTURE_DIR’); 
   DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;

 
To calibrate the replay  
 
#wrc mode=calibrate replaydir=/u01/oracle/dbdata/dbreplay_capture

Workload Replay Client: Release 11.2.0.1.0 – Production on Tue Jan 18 15:43:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Report for Workload in: /u01/oracle/dbdata/dbreplay_capture
———————–
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 3 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
– max concurrency: 1 sessions
– total number of sessions: 5
 
Assumptions:
– 1 client process per 50 concurrent sessions
– 4 client process per CPU
– 256 KB of memory cache per concurrent session
– think time scale = 100
– connect time scale = 100
– synchronization = TRUE
 
/u01/oracle/dbdata/dbreplay_capture/cap 
  
To Start Replay process at Test Database
 
#wrc system/xxxx@dbat01  mode=replay replaydir=/u01/oracle/dbdata/dbreplay_capture
 
Workload Replay Client: Release 11.2.0.1.0 – Production on Tue Jan 18 16:02:48 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
Wait for the replay to start (16:02:51)
……. 
 
Open another new session at test Database
————-
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>
BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
 
PL/SQL procedure successfully completed.
————–
Back to 1st session at test Database
 
…….
Wait for the replay to start (16:02:51)
 
Replay started (16:33:26)
 
Replay finished (17:10:39) 
  
To check replay work at test enviroment  
 
SQL> CONN sys/password@test AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = ‘dbreplay_TEST’;
 
TABLE_NAME
——————————
dbreplay_TEST_TAB
 
SQL> SELECT COUNT(*) FROM dbreplay_test.dbreplay_test_tab;
 
  COUNT(*)
———-
    100000
 
 
SQL> set pagesize 0 long 30000000 longchunksize 3000
SQL> set line 2000
SQL> select dbms_workload_replay.report(8,’TEXT’) from dual;
 
DB Replay Report for test_capture_8
—————————————————————————
| DB Name | DB Id
     | Release    | RAC | Replay Name
    | Replay Status |
—————————————————————————
| DBAT01
  | 313434807 | 11.2.0.1.0 | NO  | test_capture_3 | COMPLETED
     |
—————————————————————————
 
Replay Information
——————————————————————————————-|
   Information    | Replay                               | Capture
                              |
——————————————————————————————-| Name
             | test_capture_3                       | test_capture_3
                       |
——————————————————————————————-| Status
           | COMPLETED                            | COMPLETED
                            |
——————————————————————————————-| Database Name
    | DBAT01                               | tstd11s
                              |
——————————————————————————————-| Database Version | 11.2.0.1.0
                           | 11.2.0.1.0
                           |
——————————————————————————————-| Start Time
       | 18-01-11 21:33:26                    | 18-01-11 15:46:02
                    |
——————————————————————————————-| End Time
         | 18-01-11 22:07:49                    | 18-01-11 16:20:29
                    |
——————————————————————————————-| Duration
         | 34 minutes 23 seconds                | 34 minutes 27 seconds
                |
——————————————————————————————-| Directory Object | dbreplay_CAPTURE_DIR
                | dbreplay_CAPTURE_DIR
                |
————————————————————————————————–
| Directory Path
   | /u01/oracle/dbdata/dbreplay_ca | /u01/oracle/dbdata/db_replay_ca |
|
                  | pture/                               | pture/
                               |
————————————————————————————————–
 
Replay Options
———————————————————
|
       Option Name       | Value
                       |
———————————————————
| Synchronization
         | SCN
                         |
———————————————————
| Connect Time
            | 100%
                        |
———————————————————
| Think Time
              | 100%
                        |
———————————————————
| Think Time Auto Correct | TRUE
                       |
———————————————————
| Number of WRC Clients
   | 1 (1 Completed, 0 Running ) |
———————————————————
 
Replay Statistics
—————————————————————
|
        Statistic        | Replay           | Capture
        |
—————————————————————
| DB Time
                 |
   76.383 seconds | 56.847 seconds |
—————————————————————
| Average Active Sessions |
              .04 |
            .03 |
—————————————————————
| User calls
              |               95 |
             95 |
—————————————————————
| Network Time
            |    1.101 seconds | .
              |
—————————————————————
| Think Time
              | 1992.558 seconds | .
              |
—————————————————————
 
Replay Divergence Summary
——————————————————————-
|
                Divergence Type
                | Count | % Total |
——————————————————————-
| Session Failures During Replay
                |     0 |
    0.00 |
——————————————————————-
| Errors No Longer Seen During Replay
           |     0 |
    0.00 |
——————————————————————-
| New Errors Seen During Replay
                 |     0 |
    0.00 |
——————————————————————-
| Errors Mutated During Replay
                  |     1 |
    1.05 |
——————————————————————-
| DMLs with Different Number of Rows Modified
   |     0 |
    0.00 |
——————————————————————-
| SELECTs with Different Number of Rows Fetched |
     4 |
    4.21 |
——————————————————————-
 
——————————————————————————————-Workload Profile Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Top Service/Module/Action
————————————————–
| No data exists for this section of the report. |
————————————————–
Top SQL with Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Top Sessions with Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Replay Divergence Session Failures By Application
————————————————–
| No data exists for this section of the report. |
————————————————–
Error Divergence By Application
——————————————————————————————-| Service
   | Module    | Action    | Capture   | Replay
    | Count | First Occ | Last Occu |
| Name
      | Name      | Name      | Error     | Error     |       | urrence   | rrence
    |
——————————————————————————————- SYS$USERS | SQL*Plus
  | UNNAMED   | ORA-00904 | ORA-15566 | 1
     | 2011-01-1 | 2011-01-1 |
|
           |           |           |           |           |
       | 8T16:38:0 | 8T16:38:0 |
|
           |           |           |           |           |
       | 5.689633- | 5.689633- |
|
           |           |           |           |           |       | 05:00     | 05:00
     |
——————————————————————————————-
By SQL
————————————————–
| No data exists for this section of the report. |
————————————————–
By Session
——————————————————————————————-
| Session ID | Session
      | Capture      | Replay Error | Count | First        | Last
         |
|
            | Serial       | Error        |              |       | Occurrence   | Occurrence
   |
——————————————————————————————-
| 34
         | 293          | ORA-00904    | ORA-15566    | 1
     | 2011-01-18T1 | 2011-01-18T1 |
|
            |              |              |              |
       | 6:38:05.6896 | 6:38:05.6896 |
|
            |              |              |              |       | 33-05:00     | 33-05:00
     |
——————————————————————————————-DML Data Divergence By Application
————————————————–
| No data exists for this section of the report. |
————————————————–
By SQL
————————————————–
| No data exists for this section of the report. |
————————————————–
By Divergence magnitude
————————————————–
| No data exists for this section of the report. |
————————————————–
SELECT Data Divergence By Application
——————————————————————————————-| Service
  | Module   | Action   | Avg Rows | Avg      | Number
   | Count | First Oc | Last Occ |
| Name
     | Name     | Name     | Affected | Absolute | of       |       | currence | urrence
  |
|
          |          |          |          | Rows     | Distinct |       |          |
          |
|
          |          |          |          | Affected | Sessions |       |          |
          |
——————————————————————————————-| SYS$USER | sqlplus@ | UNNAMED
  | -3       | 3        | 2        | 4
     | 2011-01- | 2011-01- |
| S
        | artp6    |          |          |          |          |
       | 18T16:38 | 18T16:56 |
|
          | (TNS     |          |          |          |          |
       | :48.0546 | :08.9631 |
|
         | V1-V3)   |          |          |          |          |
       | 23-05:00 | 96-05:00 |
——————————————————————————————-By Divergence magnitude
——————————————————————
| Max divergence magnitude | Divergence distribution (%) | Count |
——————————————————————
| no rows affected
         | 100                         | 4
     |
——————————————————————
Replay Clients Alerts
————————————————–
| No data exists for this section of the report. |
————————————————–
Replay Filters
————————————————–
| No data exists for this section of the report. |
————————————————–
 
End of Report. 
 

Advertisements

About deepakguptadba

10 years of experience in Database Administrator for Oracle Database 10g//9i/8i/7.x with total 15 years’ comprehensive experience in IT , Pharma / Biotech , Manufacturing, Construction , Auto & Electronics.
This entry was posted in Oracle 11g2 and tagged . Bookmark the permalink.

One Response to Step by step for Oracle 11g Database Replay feature

  1. garima says:

    Good One, Well defined

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s