MRMESSIN Oracle Database Administration

ASM Move ASM Disk from one disk group to another, when using ASMlib rename the ASM Disk

2012-02-13T15:17:00.000-05:00

1. determine the disk group asm disk that you want to move, this SQL will provide a list of the ASM diskgroups and their associated disks, ensure that you use the size values to determine that if you remove a disk from the disk group that you have enough free space to do so.

set linesize 100
set pagesize 1000
select dg.name, d.name, d.label, dg.type, d.path, d.state, d.OS_MB, d.TOTAL_MB, d.FREE_MB
from v$asm_disk d, v$asm_diskgroup dg
where dg.group_number = d.group_number
order by dg.name, d.name ;

2. Select the disk to remove, in this case we will select RECO3 to remove from the RECO Disk Group.

set head off
set linesize 100

set pagesize 1000
select dg.name, d.name, d.label, dg.type, d.path, d.state, d.OS_MB, d.TOTAL_MB, d.FREE_MB
from v$asm_disk d, v$asm_diskgroup dg
where dg.group_number = d.group_number
   and dg.name = 'RECO'
order by dg.name, d.name ;

RECO RECO1 RECO1 EXTERN
ORCL:RECO1
NORMAL 24999 24999 15606

RECO RECO2 RECO2 EXTERN
ORCL:RECO2
NORMAL 24999 24999 15605

RECO RECO3 RECO3 EXTERN
ORCL:RECO3
NORMAL 24999 24999 15607

3. Remove the ASM disk from the disk group

SQL> alter diskgroup RECO drop disk RECO3 ;

Diskgroup altered.

4. monitor the disk group rebal operation that removes the disk until complete.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
ERROR_CODE
--------------------------------------------
3 REBAL RUN 1 1 570 10189 903 10

1 row selected.

SQL> select * from v$asm_operation;

no rows selected

5. ensure the disk is no longer part of the diskgroup

set linesize 100
set pagesize 1000
select dg.name, d.name, d.label, dg.type, d.path, d.state, d.OS_MB, d.TOTAL_MB, d.FREE_MB
from v$asm_disk d, v$asm_diskgroup dg
where dg.group_number = d.group_number
and dg.name = 'RECO'
order by dg.name, d.name ;

NAME NAME LABEL TYPE
------------------------------ ------------------------------ ------------------------------- ------
PATH
----------------------------------------------------------------------------------------------------
STATE OS_MB TOTAL_MB FREE_MB
-------- ---------- ---------- ----------
RECO RECO1 RECO1 EXTERN
ORCL:RECO1
NORMAL 24999 24999 10912

RECO RECO2 RECO2 EXTERN
ORCL:RECO2
NORMAL 24999 24999 10909

6. To rename the ASM disk removed to match the disk names of the disk group you are putting the disk into
in this example we are taking the disk from RECO (disk RECO3) and putting into the DATA disk group, some may want to rename the ASM disk from RECO03 to DATA5 to match the new diskgroup
** DATA4 Name is already used as an ASM disk name, but not and ASM lib disk name.

set linesize 100
set pagesize 1000
select dg.name, d.name, d.label, dg.type, d.path, d.state, d.OS_MB, d.TOTAL_MB, d.FREE_MB
from v$asm_disk d, v$asm_diskgroup dg
where dg.group_number = d.group_number
and dg.name = 'DATA'
order by dg.name, d.name ;

NAME NAME LABEL TYPE
------------------------------ ------------------------------ ------------------------------- ------
PATH
----------------------------------------------------------------------------------------------------
STATE OS_MB TOTAL_MB FREE_MB
-------- ---------- ---------- ----------
DATA DATA1 DATA1 EXTERN
ORCL:DATA1
NORMAL 24999 24999 8192

DATA DATA2 DATA2 EXTERN
ORCL:DATA2
NORMAL 24999 24999 8193

DATA DATA3 DATA3 EXTERN
ORCL:DATA3
NORMAL 24999 24999 8195

DATA DATA4 STAGEASMDATA4 EXTERN
ORCL:STAGEASMDATA4
NORMAL 24995 24995 8192

7. Ensure the name we want to use is not used in an ASM Name already.

set linesize 100
set pagesize 1000
column disk_group_name format a20
column disk_file_path format a30
column disk_file_name format a20
column disk_file_fail_group format a20
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;

DISK_GROUP_NAME DISK_FILE_PATH     DISK_FILE_NAME FILE_FAIL_GROUP
--------------- ------------------ -------------- ---------------DATA            ORCL:STAGEASMDATA4 DATA4          DATA4
DATA            ORCL:DATA1         DATA1          DATA1
DATA            ORCL:DATA2         DATA2          DATA2
DATA            ORCL:DATA3         DATA3          DATA3
OCR_VOTE        ORCL:OCR_VOTE01    OCR_VOTE01     OCR_VOTE01
OCR_VOTE        ORCL:OCR_VOTE03    OCR_VOTE03     OCR_VOTE03
OCR_VOTE        ORCL:OCR_VOTE02    OCR_VOTE02     OCR_VOTE02
RECO            ORCL:RECO2         RECO2          RECO2
RECO            ORCL:RECO1         RECO1          RECO1

** As we see here the ASMlib name and DISKFILE NAME do not match, this will cause as disk name issue so we have to give our new disk a different name to avoid a duplicate name in the ASM instance.
In our case we will use DATA5 as it is not used already.

8. When using ASM lib you can rename the disk using ASM lib best to do as root, when using OS level, check the OS level name and change it there for most operating systems there is no change need unless the device name changes. When using the OS check the device name which would be the disk path as they can be name when using multi path so use the configuration there to rename.

[root]# /etc/init.d/oracleasm querydisk -p RECO3
Disk "RECO3" is a valid ASM disk
/dev/sdk1: LABEL="RECO3" TYPE="oracleasm"

[root]# /etc/init.d/oracleasm force-renamedisk RECO3 DATA5
Renaming disk "RECO3" to "DATA5": [ OK ]

9. list the disks if using ASMlib, to ensure the disk is renamed.

[root]# /etc/init.d/oracleasm listdisks
DATA1
DATA2
DATA3
DATA5
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03
RECO1
RECO2
STAGEASMDATA4

10. Ensure disk is a candidate disk now to be added to a diskgroup.

set linesize 100
set pagesize 1000
column disk_group_name format a20
column disk_file_path format a30
column disk_file_name format a20
column disk_file_fail_group format a20
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;

DISK_GROUP_NAME DISK_FILE_PATH DISK_FILE_NAME FILE_FAIL_GROUP
--------------- ------------------ -------------- ---------------
DATA ORCL:STAGEASMDATA4 DATA4 DATA4
DATA ORCL:DATA1 DATA1 DATA1
DATA ORCL:DATA2 DATA2 DATA2
DATA ORCL:DATA3 DATA3 DATA3
OCR_VOTE ORCL:OCR_VOTE01 OCR_VOTE01 OCR_VOTE01
OCR_VOTE ORCL:OCR_VOTE03 OCR_VOTE03 OCR_VOTE03
OCR_VOTE ORCL:OCR_VOTE02 OCR_VOTE02 OCR_VOTE02
RECO ORCL:RECO2 RECO2 RECO2
RECO ORCL:RECO1 RECO1 RECO1
[CANDIDATE] ORCL:DATA5

9. Add disk to disk to group, in our case disk DATA4 to diskgroup DATA

SQL> alter diskgroup DATA add disk 'ORCL:DATA5' ;

Diskgroup altered.

10. Monitor Rebal operation that is bringing in the new disk

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
ERROR_CODE
--------------------------------------------
1 REBAL RUN 1 1 682 13467 987 12

SQL> select * from v$asm_operation;

no rows selected

11. Ensure the disk is now part of the diskgroup

set linesize 100
set pagesize 1000
select dg.name, d.name, d.label, dg.type, d.path, d.state, d.OS_MB, d.TOTAL_MB, d.FREE_MB
from v$asm_disk d, v$asm_diskgroup dg
where dg.group_number = d.group_number
and dg.name = 'DATA'
order by dg.name, d.name ;

NAME NAME LABEL TYPE
------------------------------ ------------------------------ ------------------------------- ------
PATH
----------------------------------------------------------------------------------------------------
STATE OS_MB TOTAL_MB FREE_MB
-------- ---------- ---------- ----------
DATA DATA1 DATA1 EXTERN
ORCL:DATA1
NORMAL 24999 24999 11525

DATA DATA2 DATA2 EXTERN
ORCL:DATA2
NORMAL 24999 24999 11525

DATA DATA3 DATA3 EXTERN
ORCL:DATA3
NORMAL 24999 24999 11525

DATA DATA4 STAGEASMDATA4 EXTERN
ORCL:STAGEASMDATA4
NORMAL 25995 25995 11985

DATA DATA5 DATA5 EXTERN
ORCL:DATA5
NORMAL 24999 24999 11527

Optimal Undo Tablespace Size and Undo Retention

2011-08-11T10:51:00.000-04:00

The undo tablespace and its size is related to the retention period and the transactional activity of the database. These must be regularly reviewed and check to ensure that 1, you are not trying to have a retention period that the undo tablespace can not handle, 2 that you are size properly so that you do not encounter the dreaded ORA-01555 snapshot too old.

Optimal Undo Size and Retention
I have seen many environments where the undo tablespace is far to small for the undo retention setting and they target undo retention the environment requires. This is due to the fact that the undo tablespace was size and undo retention set, but never rechecked or updated as the environment grew or activity changed. Therefore if the expectation is that a flashback query can go back 2 hours, but the tablespace is too small the reality is that they really can not go back 2 hours.

First we need to check the Optimal Undo Size based on our desired retention as in most environments this will change regularly based on increases or decreases in the trasnactional activity.

The script below will ask for the desired undo retention, then will examine your database and tell you the optimal undo retention setting for the current environment as the undo tablespace size and transactional activity indicates, but will also show you what the size of the undo tablespace needs to be to be able to support the desired undo retention you have entered.

---------------------------------------------------------------------------------
-- Script: see_optimal_undo_retention.sql
-- Author: Michael Messina, Management Consultant TUSC a Rolta Company
--
-- Description: Will calculate based on undo utilization and undo size
--               the optimal value for undo_retention.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off

ACCEPT desired_undo_retention PROMPT "Enter Desired Undo Retention in Seconds ->"

DECLARE
   v_desired_undo_retention          NUMBER := &desired_undo_retention ;
   v_block_size                      NUMBER ;
   v_undo_size                       NUMBER ;
   v_undo_blocks_per_sec             NUMBER ;
   v_optimal_undo_retention          NUMBER ;
   v_current_undo_retention          NUMBER ;
   v_undo_size_desired_ret           NUMBER ;

BEGIN
   -- get the current undo retention setting
   select TO_NUMBER(value)
   INTO v_current_undo_retention
   FROM v$parameter
   WHERE name = 'undo_retention' ;

   -- Calculate Actual Undo Size
   SELECT SUM(a.bytes)
   INTO v_undo_size
   FROM v$datafile a,
        v$tablespace b,
        dba_tablespaces c
   WHERE c.contents = 'UNDO'
     AND c.status = 'ONLINE'
     AND b.name = c.tablespace_name
     AND a.ts# = b.ts#;

   -- Calcuate the Undo Blocks per Second
   SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
   INTO v_undo_blocks_per_sec
   FROM v$undostat ;

   -- Get the database block size
   SELECT TO_NUMBER(value)
   INTO v_block_size
   FROM v$parameter
   WHERE name = 'db_block_size';

   v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;
   v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_desired_undo_retention)) / 1024 / 1024 ;

   DBMS_OUTPUT.PUT_LINE ('              Current undo Size --> ' || TO_CHAR(v_undo_size/1024/1024) || 'MB') ;
   DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' || TO_CHAR(v_current_undo_retention) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE (' Optimal undo_retention Setting --> ' || TO_CHAR(ROUND(v_optimal_undo_retention,2)) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE (' Desired undo_retention Setting --> ' || TO_CHAR(ROUND(v_desired_undo_retention,2)) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' || TO_CHAR(ROUND(v_undo_size_desired_ret,2)) || 'MB') ;
END ;
/

Example Execution of script:
SQL> @see_optimal_undo_retention.sql
Enter Desired Undo Retention in Seconds ->7200
old   2:    v_desired_undo_retention          NUMBER := &desired_undo_retention ;
new   2:    v_desired_undo_retention          NUMBER := 7200 ;
Current undo Size --> 1980MB
Current undo_retention Setting --> 900 Seconds
Optimal undo_retention Setting --> 100504.96 Seconds
Desired undo_retention Setting --> 7200 Seconds
Undo Size for desired retention --> 141.84MB

In this example on a very small database with no activity I want a retention of 2 hours, The output shows the current undo tablespace size, the current undo retention setting, the optimal undo retention setting indicates what I could make the undo retention based on the current activity and size of the undo tablespace, the desired undo retention that I entered and the undo tablespace size required to meet my desired undo retention setting.

SQL Performance Analyzer, Step 2 Create Analysis Task, Make the Changes and Review Impact

2011-08-09T19:57:00.001-04:00

I started this with the goal of being able to measure the impact of partitioning a table or set of tables. The first Step is to get all the SQL I can from the system that executes against the table(s) that are intended to be partitioned so that we can ensure that we make a positive impact on the SQL and ensure that we are not going to make a negative impact on any existing SQL.

In this case I am choosing to use SQL Performance Analyzer to measure the impact on the SQL I gathered into my SQL Tuning Set from

** Keep in mind I am using a test environment where I can capture the SQL, execute and measure my changes all in the same database, in same cases you may need to capture the SQL tuning set in production and then transfer the SQL Tuning Set to a test environment to execute the analysis make the changes and then make the comparison. See Transfer SQL Tuning Sets between Databases post from October 2010.

Create an Analysis Task

In this case I will be using the dbms_sqlpa package using the create_analysis_task which will allow me to analyze the SQL in my SQL Tuning Set which is the SQL I captured that executes against the table(s) I am planning on partitioning.

dbms_sqlpa.create_analysis_task(sqlset_name => ‘STS_SPA_1‘,

task_name => ‘sts_spa1_task‘,

description => ‘test partitioning‘);

Execute the Analysis Task

Once the Analysis Task has been created we will need to execute the task to create the baseline that will be used to compare against after we make the changes. We will be using the dbms_sqlpa package and the execute_analysis_task procedure.

dbms_sqlpa.execute_analysis_task(task_name => ‘STS_SPA1_task‘,

execution_type => ‘test execute‘,

execution_name => ‘before_partition_change‘);

Once the execution of the analysis task is complete we can make our changes. Once the change has been made then we will need to execute another analysis task to get the information to compare against the base after the change.

dbms_sqlpa.execute_analysis_task(task_name => ‘STS_SPA1_task‘,

execution_type => ‘test execute‘,

execution_name => ‘after_partition_change‘);

Compare Analysis Task
Once the initial analysis task is done, the change is made the post analysis task is done we are read yo compare the initial to the after. Again we will use the dbms_sqlpa package, this time we will use the execute_analysis task again but will set the analysis to compare performance.

dbms_sqlpa.execute_analysis_task(

task_name => ‘STS_SPA1_task‘,

execution_type => ‘compare performance‘,

execution_name => ‘analysis_results‘,

execution_params => dbms_advisor.arglist(‘execution_name1‘,‘before_partition_change‘,

‘execution_name2‘, ‘after_partition_change‘,

‘comparison_metric‘,‘buffer_gets‘));

Run Compare Report
Once the Compare Analysis is complete we can run a report that will show us the results of the compare analysis. The follow short script will output to a file called SPA_COMPARE_REPORT.out 3 possible reports, the first being the full analysis report, the second being a summary report and the 3 being like a top offenders report called a finding report from the analysis.

---------------------------------------------------------------------------

-- Script: spa_compare.sql

-- Author; Michael R. Messina, Managing Consultant

-- Rolta-TUSC, Infrastructure Services

--

-- Executes a SQL Tuning Set Comparison and then outputs a report

-- from Performance Analyzer.

---------------------------------------------------------------------------

spool SPA_COMPARE_REPORT.out

-- Get the whole report for the single statement case.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task') from dual;

-- Show me the summary for the sts case.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task', 'TEXT', 'TYPICAL', 'SUMMARY')

FROM DUAL;

-- Show me the findings for the statement I'm interested in.

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('STS_SPA1_task', 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;

spool off

SQL Performance Analyzer, Step 1 Create a SQL Tuning Set

2011-08-09T19:31:00.000-04:00

I was dealing with a performance issue for a particular application and wanted to partition the table then measure the impact of the partitioning based on the the SQL uses that table. This got me thinking how could make use of SQL performance Analyzer for this that way I could have the before and after analysis.

First step would be to gather the SQL that executes against the table from the live production system. We can do this by creating a SQL tuning set and putting that SQL into that SQL tuning set. We would base this SQL tuning set on tuning set from the cache over a period time to capture the SQL into our SQL tuning set for the SQL that executes on the table.

Create a SQL Tuning Set

We can use the DBMS_SQLTUNE package to create the SQL tuning set with the SQL that executes against the table. For example if we want to capture the SQL execute against the table for a 24 hour period, take a sample at 5 minute intervals it would looks like the following. We create a SQL Tuning Set called STS_SPA_1, then setup the capture of the SQL Tuning Set to come from the cursor cache over a the 24 hour period for just the SQL that contains ORDER for the ORDERS and ORDERS_ITEMS tables.

---------------------------------------------------------------------------

-- Script: spa_create_sts.sql

-- Author; Michael R. Messina, Managing Consultant

-- Rolta-TUSC, Infrastructure Services

--

-- Setup a SQL Tuning Set to create a for the SQL

-- This will serve SQL Performance Analyzer well or allow us to create profiles.

-- From there we can make generate before and after pictures

-- Then evaluate the impact of changes.

---------------------------------------------------------------------------

--

set feedback on

---------------------------------------------------------------------------

-- Set up the SQL Set and what sql we want to make

-- part of the sql tuning set

---------------------------------------------------------------------------

BEGIN

    -- Create the sql set

    DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_SPA_1');

    -- Limit the sql in the set to Just on the ORDERS and ORDER_ITEMS

    -- The CAPTURE_CURSOR_CACHE_SQLSET will collect SQL statements

    -- over a peeriod of time from the cursor cache.  This helps build a more realistic

    -- set of SQL load from the system by capturing the live SQL for 24 hours at 5 min

    -- intervals

    DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

         sqlset_name => 'STS_SPA_1'

        ,basic_filter=> q'#UPPER(sql_text) LIKE '%ORDER%' #'

        ,time_limit  => 86400

        ,repeat_interval => 300

);

END;

** I would show the steps for Grid Control except the blog would never save with the Grid

Images.

The next Step will be to use the SQL Tuning Set to compare a before and after our changes, which I will be doing with SQL Performance Analyzer. To do this we Create an analysis task..... SQL Performance Analyzer, Step 2 Create Analysis Task

Configure SQL Net using Infiniband Interface

2011-04-05T11:45:00.014-04:00

Infiniband

Infiniband is a high speed network interface typically utilized in System/Storage Area Networks (SANs) which are networks connecting machines and storage in close proximity and require very low latency and very high bandwidth. Oracle has supported the use of infiniband for SQL Net traffic for quite some time. Typically TCP over Infiniband has been utilized.

Sockets Direct Protocol Support

The Sockets Direct Protocol (SDP) is a standard protocol for Infiniband. The SDP protocol is an interface the moves most of the messaging burden to the network interface card therefore reducing the amount of host CPU involved. This frees the host CPU bandwidth for other processing. This is a big advantage for mid-tier server connections to the database that require high performance communication between the database and middle tier application servers. While Oracle provides support for SQL Net client activity via the SDP protocol for SQL Net traffic over an infiniband network it is recommended to stick with TCP over infiniband as I have never gotten the SDP protocol to work as of yet.

Database Server Configuration

A database server with an infiniband network interface has the ability to communicate across that infiniband interface allowing database connections the increased bandwidth and lower latency of infiniband. The SQL-Net interface for the infiniband can be configured using TCP protocol. When using the TCP protocol it is recommended to configure the SDU sizes appropriately on the client and server. On the database server side the Oracle TNS listener must configured to listen for the specific protocol on the infiniband network address so that will be used in the communication initiated to the database so that communication can be recognized. To configure the server to accept connections to the database with the TCP it is recommended to configure 2 listeners, 1 listener for TCP communication over the normal network interface and a second listener for the TCP communication over the infiniband network. If the infiniband connection is management network for activities such as backup, Storage communication, management, data guard, streams or golden gate connectivity a second dedicated listener for this communication is a must. When the second listener is configured a second port is utilized and therefore the connection requests are completely isolated.

Additional Listener TCP

LISTENER_MGMT=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=infini-server)(PORT=1522)) ) )

Database Client Configuration

The database sqlnet client configuration will require a tns entry in the local tnsanames.ora as well as entries in the local sqlnet.ora. When using SDP with infiniband rather than TCP there is no complicated listener or tns setup as with TCP when dealing with larger bandwidth data transfers. Typically the hostname given to a database servers infiniband network interface is not in the DNS therefore you must add the database server hostname for that interface to the client and server hosts file (applies to both Windows and Unix/Linux).

hosts

# Connections (infiniband)

192.168.1.2 db02-priv.localdomain db02-priv

192.168.1.1 db01-priv.localdomain db01-priv

tnsnames.ora (non-rac example)

prd =

(DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS=(PROTOCOL=tcp)(HOST=db01-priv)(PORT=1522))

   (CONNECT_DATA =

     (SERVICE_NAME = prd.localdomain)

tnsnames.ora (rac example)

prd_rac =

(DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS=(PROTOCOL=tcp)(HOST=db01-priv)(PORT=1522))

     (ADDRESS=(PROTOCOL=tcp)(HOST=db02-priv)(PORT=1522))

   (LOAD_BALANCE = ON)

   (FAILOVER = ON)

   (CONNECT_DATA =

     (SERVICE_NAME = prd.localdomain)

     (FAILOVER_MODE=(TYPE=select)(METHOD=basic)(RETRIES=20)(DELAY=15))

Check Database Connection Configuration

After the configuration is complete a good way to test the connectivity is working is to test the connection across the infiniband from a client machine connected to the infiniband network. Using SQL Plus or another client tool that will utilize the Oracle Client interface test the connection to the database through the listener listening on the infiniband network.

C:\> sqlplus messinam@prd

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 1 18:00:10 2010

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management,

OLAP,

Data Mining and Real Application Testing options

SQL>

Running an AWR Compare Report Across Databases

2011-04-05T11:39:00.001-04:00

Extract the AWR Data from Production Database

There are time when I want to run an AWR compare report from my production activity and my test database load test activity. We can do this by extracting the AWR data from the production database and loading it into the test database and then run a compare report between the 2 sets of snap data.

Oracle provides a utility to extract the AWR data from a database. In $ORACLE_HOME/rdbms the script awrextr.sql is used for this purpose for an interactive extract.

ORACLE_HOME/rdbms/admin/awrextr.sql

** Note: This will extract the AWR data for the snap periods that are selected. This processes uses the data pump utility to extract the AWR data to a data pump dump file. Therefore you must have a defined directory to write the dump file.

Move the AWR Datapump Dump File.

Move the Data Pump dump file created by the awrextr.sql script to the test server so it can be loaded.

Load the AWR Data into the Test Database

Oracle makes the import of AWR date as simple as the extraction of AWR data.

Oracle provides a script awrload.sql in the ORACLE_HOME/rdbms/admin directory.

** Note: Before attempting to load the AWR data from the data pump dump file ensure that an Oracle directory exists for the location of the data pump dump file the needs loaded. Also When loading AWR data into another database make sure the tablespace SYSAUX, the stage tablespace selected, example will use SYSAUX, and the TEMP tablespace have enough room to complete the operation. May want to have another session open to the database to monitor freespace.

ORACLE_HOME/rdbms/admin/awrload.sql

Run the Compare Report Between the 2 databases AWR Data

Now that we have AWR data from multiple databases we can compare across snap time periods from one database, run normal AWR reports over snap time periods, or even compare snap time periods across the different databases. Oracle provides an easy way to execute the compares across the script awrddrpi.sql in the ORACLE_HOME/rdbms/admin directory.

ORACLE_HOME/rdbms/admin/awrddrpi.sql

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

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3742289852 1 AWR2 awr2 ORADBPROD01
* 744414708 1 ORCL11G orcl11g MRMESSIN

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for dbid: 3742289852
Using 3742289852 for Database Id for the first pair of snapshots
Enter value for inst_num: 1
Using 1 for Instance Number for the first pair of snapshots

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
awr2 AWR2 1 20 Feb 2008 17:42 1
2 20 Feb 2008 19:00 1
3 20 Feb 2008 20:00 1
4 20 Feb 2008 21:00 1
5 20 Feb 2008 22:00 1
6 20 Feb 2008 23:00 1
7 21 Feb 2008 00:00 1
8 21 Feb 2008 01:00 1
9 21 Feb 2008 02:00 1
10 21 Feb 2008 03:00 1
………

Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
First Begin Snapshot Id specified: 1

Enter value for end_snap: 9
First End Snapshot Id specified: 9

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
3742289852 1 AWR2 awr2 ORADBPROD01
* 744414708 1 ORCL11G orcl11g MRMESSIN

Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for dbid2: 744414708
Using 744414708 for Database Id for the second pair of snapshots
Enter value for inst_num2: 1
Using 1 for Instance Number for the second pair of snapshots

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days2: 3

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl11g ORCL11G 355 26 Feb 2008 07:34 1
356 26 Feb 2008 09:00 1
357 26 Feb 2008 10:00 1
358 26 Feb 2008 11:00 1
……………
405 02 Mar 2008 00:00 1
406 02 Mar 2008 01:00 1
407 02 Mar 2008 02:00 1
408 02 Mar 2008 03:00 1
409 02 Mar 2008 04:00 1
410 02 Mar 2008 05:00 1
411 02 Mar 2008 06:00 1
412 02 Mar 2008 07:00 1
413 02 Mar 2008 08:00 1
………

Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 405
Second Begin Snapshot Id specified: 405

Enter value for end_snap2: 413
Second End Snapshot Id specified: 413

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_1_1_405.txt To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: awrdiff_awr2_orcl11g.txt

Collaborate 2011

2011-04-05T11:37:00.001-04:00

Are you getting ready for collaborate?

I am speaking again this year, hope to see you there.

Oracle 11gR2 Installing CTX Knowledge Base

2011-04-05T11:35:00.000-04:00

Summary
The Oracle Text option requires the use of certain context knowledge bases that are not part of the typical installation of the Oracle 11gR2 database software. To be able to create context indexes these knowledge bases may be required.

Example:
DRG-11446: supplied knowledge base file /u01/app/oracle/product/11.2.0/dbhome_1/ctx/data/enlx/droldUS.dat not installed

1. Download the Oracle Examples Installation (formally Companion CD)

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

2. Unzip the Oracle Examples Installation

$ unzip linux.x64_11gR2_examples.zip

3. Goto the location in the installation media for the CTX Filegroups

$ cd examples/stage/Components/oracle.ctx.companion/11.2.0.1.0/1/DataFiles

4. unzip the CTX Knowledge base files

$ unzip filegroup1.jar
Archive: filegroup1.jar
inflating: ctx/data/enlx/drofdUS.dat
inflating: ctx/data/enlx/drofiUS.dat
inflating: ctx/data/enlx/droldUS.dat
inflating: ctx/data/enlx/droliUS.dat
inflating: ctx/data/enlx/drolkUS.dat
inflating: ctx/data/enlx/drolsUS.dat

5. Goto the location of the knowledge base files we just extracted and copy them to the Oracle Database Home location

$ cd ctx/data/enlx/
$ cp * $ORACLE_HOME/ctx/data/enlx

Oracle Grid Control Exadata Storage Cell Plug-in Installation/Upgrade

2010-10-18T11:47:00.000-04:00

The Oracle Enterprise Manager Grid Control Exadata Storage Cell Plug-in allows that management agent to be able to monitor and assist with the basic management of the Exadata Storage Cells. The Exadata Storage Cell Plug-in is compatible with Oracle Enterprise Manager Grid Control version 10.2.0.4 and above.
The installation and upgrade process is basically the same process. The difference is that on an upgrade is the pre-installation setup will not be needed.

Pre-Installation Setup
Prior to initial installation of the Exadata Storage Cell Plug-in you will need to identify a server that will have an agent that will be the monitoring agent for the Storage Cells. The that will have the Grid Control Agent that will monitor the Storage Cells must have connectivity to the Exadata Storage Cells via SSH. For this to happen that server must have a network interface on the management network or the infini-band for the Exadata.

1. Setup Server with Network connectivity to the Exadata Network which has the storage cells.

2. Setup hosts file on the server that will house the agents to see each of the storage cells.

3. Setup ssh connectivity to each of the storage cells
     a. generate the ssh keys on the sever that will house the agent
          i. ssh-keygen -t dsa -f id_dsa

     b. copy the ssh key to each of the storage cell servers as the celladmin user
          i. scp id_dsa.pub celladmin@dm01cel01:~
          ii. repeat for each storage cell

     c. log into the storage cell as the root user
          i. ssh root@dm01cel01

     d. Add contents of the id_dsa file to the authorized_keys file for the cellmonitor user on each of the storage cells.
          i. check if the authorized_keys file exists for the cellmonitor user
               a. ls -l /home/cellmonitor/.ssh/*
          ii. if the authorized keys file exists for the cellmonitor user
               a. cat /home/celladmin/id_dsa.pub >> /home/cellmonitor/.ssh/authorized_keys
          iii. if the authorized keys file does not exist for the cell monitor user
               a. cp /home/celladmin/id_dsa.pub /home/cellmonitor/.ssh/authorized_keys
          iv. repeat for each of the storage cell servers

4. Test ssh connectivity from the server that will house the Grid Control Agent for each of the storage cells.
     a. ssh -l cellmonitor dm01cel01 cellcli -e 'list cell detail'
        ** If you prompted for a password it not not working correctly need to check/repeat setup steps
        ** If you asked to confirm indicates Yes to continue with connection
     b. repeat test for each storage cell server.

5. Install the Oracle Enterprise Manager Grid Control Agent on the server that will serve as the monitoring Exadata Storage Cell monitoring Agent for the Grid Control Plug-in.

** It is ok for the monitoring agent for the Exadata Storage Cells to be the Gird Control Management Server Agent.

Plug-in Installation
For the plug-in installation the plug-in is imported into OEM Grid Control. This is the case for a new installation or a new version for an existing installation. This also assumes that you have an Oracle Grid Control Management Server/Service Setup and accessible.

1. Download the Oracle Enterprise Manager Grid Control Exadata Storage Server Plug-in.
     a. http://www.oracle.com/technetwork/database/exadata/index.html

2. Import the Plug-in into the Oracle Enterprise Manager Grid Control.
     a. Select Setup from the Top Right portion of the Browser Window
     b. Select Management Plug-ins from the list menu on the right hand side of the window.
     c. Select Import Option
     d. Select Browse to locate the Exadata Storage Cell Plug-in
     e. Locate and Select the Exadata Storage Cell Plug-in File Downloaded and Select Open
     f. Select List Archive to Expand out the Plug-in
     g. Select the oracle_cell Plug-in ensuring the check mark is present next to the Plug-in then select OK
     h. Verify the Plug-in Version is now in the list of Plug-ins.
     j. Management Plug-in Installation Complete.

Plug-in Deployment
The Plug-in deployment is done the same regardless if there is an existing plug-in version deployed. Once the Plug-in is imported you deploy the plug-in the same. This process assumes that the Grid Control Monitoring Agent that will serve as the monitoring agent for the Exadata Storage Cells is installed and registered with you Oracle Management Service.
1. Log into Oracle Enterprise Manager Grid Control Management Service.

2. Set Preferred Credentials for the host of monitoring agent Plug-in will be deployed to as well as the monitoring agent.
     a. Select Preferences from top right of Browser Window displaying management console.
     b. select preferred credentials
     c. select Set Credentials Icon for Host
     d. locate host and enter the credentials for the oracle operating system user credentials
     e. select test credentials to ensure credentials entered are working.
     f. select apply.
     g. select Preferred Credentials in the upper left part of the screen
     h. select Set Credentials Icon for Agent
     i. Locate the agent to set credentials for and enter the oracle operating system user credentials
     j. select apply
     k. Select Home Tab to return to the Home Console Screen

3. Select Setup from the Top Right portion of the Browser Window

4. Select Management Plug-ins from the list menu on the right hand side of the window.

5. For the Exadata Plug-in to Deploy Select the Deploy icon. When Upgrading the Exadata Plug-in Select the new version to deploy.

6. Add Agent to the List of Agents to Deploy Plug-in to.

7. Select the Agent that will serve as the monitoring agent for the Exadata Storage Cells and then press Select.

8. To Add More Agents, repeat the Add Agent Steps, When finished Select Next to Continue with Deploying Plug-in.

9. Select Finish to Deploy Agent.

10. Deployment Completed

Exadata Hybrid Columnar Compression

2010-10-01T14:16:00.039-04:00

Summary
Hybrid Columnar Compression was introduced with the Exadata version 2 and is one of the most highlighted advantages of an Exadata as it improves performance and significantly reduces the storage footprint of the data on physical storage. By reducing the foot print of the data on physical storage it also reduces the foot print of that data in the database cache area as well therefore offering overall improvement in system memory resources in addition to physical storage. While you can use the columnar compression options in Exadata it does not prevent you from using the Oracle 11gR2 Advanced Compression as well giving you three different levels of compress based on the data within your database.

There are 2 types of columnar compression with Exadata, compress for query and compress for archive. The compress for query option offers a high level of compression, but not as high as for archive, but offers better query performance then the for archive option. While the OLTP compression in 11gR2 could work well for more online type access.

The advantages to the multiple types of compression is the level of compression based on the type of data. This gives the ability to true start to consider the Information Lifecycle Management of your data in regards to how it is stored and better manage your storage resources. For example let say you have a very large table partitioned by month where the first 24 months of data is subject to regular insert, update, delete and query activity, then from 24 months to 60 months the data is not updated, but query activity is heavy then data over 60 months is rarely accessed but does need to be available. Your best option for the most recent 24 months of database would most likely be OLTP compression. For the data between 24 and 60 months compress for query columnar compression would be your best option and the data over 60 months old would be best suited for compress for archive column compression.

Compress for Query Columnar Compression
Compress for query Columnar Compress offers a good balance between frequent query access and compression level. The compress for query can be high (default) or low. This indicates the level of compression and subsequently impact on CPU resources to handle the compression. The query option has between a 5-10% runtime hot on direct loads over an uncompress table load which in most cases can be considered minimal when compared to the disk savings achieved. The compression significantly reduces the size of the table and at the same time improves the performance of queries without very high impact on other system resources. With the implementation of Hybrid Columnar Compression for query high reduction consistently has between 60-70% . The table compress for query below shows some real world results when using the compress for query high option. With the implementation of Hybrid Columnar Compression with the compress for archive option

Compress for Query High
Uncompressed(MB) Compress for Query(MB) Reduction(MB) % Reduction
903              344                    559           61.90
1088             408                    680           62.50
960              361                    599           62.40
1088             416                    672           61.76
1152             400                    752           65.28
1091             400                    691           63.33
1216             456                    760           62.50

1112             408                    704           63.31

Compress for Archive Columnar Compression
The compress for archive columnar compression offers the highest level of compression available, but with the higher level of compression the impact to system CPU resources increases therefore making it a less desired option where consistent query access is needed. The archive compress option averages between 70-80% size reduction over uncompressed tables. The archive option is also a significant hit on direct load runtimes as an average 50% runtime hits have been experienced over query compressed table direct loads. This hit on load time could also make this a less desired option for objects with frequent load activities where load runtimes are critical. As the name indicates it works very well for compressing down data that is archived and needs to be available, but is not accessed frequently or load times are a critical requirement.

Compress for Archive
Uncompressed(MB) Compress for Query(MB) Reduction(MB) % Reduction
903              264                    639           70.76
1088             304                    784           72.06
960              272                    688           71.67
1088             312                    776           71.32
1152             336                    816           70.83
1091             328                    763           69.94
1216             352                    864           71.05
1112             304                    808           72.66

Effectively Utilizing the Hybrid Column Compression
Query and Archive Hybrid Columnar Compressions is most effective for historical tables/date where the data no longer changes. Being that they are historical tables they are typically quite large we usually will utilize with a partitioned table as best practice for large tables is to partition them. The most effective use of the Hybrid Columnar Compression is to use them both within your partitioned historical table where the more frequently queries data partitions are query hybrid columnar compressed and the less frequently accessed data is archive columnar compressed. This creates an effect Information Life Cycle Management that will help you manage your partition sizes based on their age and access frequency.

Notice in the example below the more recently accessed data is partitioned by month allowing faster access to data through more fine tuned partition elimination. Space is saved through the query hybrid columnar compression on those partitions. Now as the data gets older partition maintenance is performed to combined partitions by year and the new partition for the entire year utilizes hybrid columnar compression to save a little more space and still keep the data accessible.

Example

CREATE TABLE ARCHIVE."CLAIM" 

   (   "CLAIM_ID"            VARCHAR2(36)   NOT NULL ENABLE, 

       "CLAIM_TYPE_CODE"     VARCHAR2(2)    NOT NULL ENABLE, 

       "PAYER_PROCESS_STAGE" VARCHAR2(10), 

       "SYS_CREATE_DATE"     DATE, 

       "SYS_UPDATE_DATE"     DATE) 

TABLESPACE ARCHIVE_2004 

PARTITION BY RANGE (SYS_CREATE_DATE) interval (NUMTOYMINTERVAL(1,'month'))                

   (PARTITION CLAIM_2004 VALUES LESS THAN (TO_DATE ('1-JAN-2005','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE ARCHIVE_2004 compress for archive high,

    PARTITION CLAIM_2005 VALUES LESS THAN (TO_DATE ('1-JAN-2006','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE ARCHIVE_2005 compress for archive high,

    PARTITION CLAIM_2006 VALUES LESS THAN (TO_DATE ('1-JAN-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE ARCHIVE_2006 compress for archive high,

    PARTITION CLAIM_200701 VALUES LESS THAN (TO_DATE ('1-FEB-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE OTHER_PART_QUERY compress for query,

    PARTITION CLAIM_200702 VALUES LESS THAN (TO_DATE ('1-MAR-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE OTHER_PART_QUERY compress for query,

    PARTITION CLAIM_200703 VALUES LESS THAN (TO_DATE ('1-APR-2007','DD-MON-YYYY'))

                   NOLOGGING TABLESPACE OTHER_PART_QUERY compress for query

)

compress for archive high;

2010-09-17T23:26:00.000-04:00

Hope to see you all at open World
Will be attending the Oracle Exadata SIG
Presenting Sunday and Monday
Spending some time in the Rolta-TUSC Booth Monday
Come by and see me.

Oracle Compression Advisor

2010-03-23T22:35:00.000-04:00

Every once and awhile I come across a valuable utility, I have come across a new
Oracle utility that can be utilized starting with 9i release 2 that can help estimate the space savings from compression.

The process creates tables in the default tablespace of the user executing the procedure. It is recommended that you make sure the default tablespace is other then SYSTEM or SYSAUX.

The process is created via a package procedure available via a download from Oracle Technet at

http://www.oracle.com/technology/products/database/compression/compression-advisor.html

Example of the package installation
[oracle@oel54node1 compression_advisor]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 23 22:33:57 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

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> @dbmscomp.sql

Package created.

Synonym created.

Grant succeeded.

No errors.
SQL> @prvtcomp.plb

Package body created.

Synonym created.

Grant succeeded.

No errors.

Here is an example of its execution

SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('SYS','AUD$','OLTP',20) ;
Sampling table: SYS.AUD$
Sampling percentage: 20%
Compression Type: OLTP
Compression ratio: 1.53

PL/SQL procedure successfully completed.

Change OEM Grid Control SYSMAN Password

2010-02-20T15:32:00.000-05:00

1. Set the ORACLE_HOME and associated environment variables such as PATH, etc.
$ export ORACLE_HOME=/u01/app/oracle/product/oms10g
$ export PATH=ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH

2. Stop the OMS instances that are associated with the repository
$ cd $ORACLE_HOME/opmn/bin
$ opmnctl stopall

3. Log into the OEM Grid Control Repository Database as a DBA user and change the SYSMAN password
$ sqlplus system@oem
SQL> alter user sysman identified by newpassword;
SQL> exit

4. Update the emoms.properties file on each management server host
$ export ORACLE_HOME=/u01/app/oracle/product/oms10g
$ export PATH=ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH
$ cd $ORACLE_HOME/sysman/config
$ cp emoms.properties emoms.properties.backup
$ vi emoms.properties file
Change the Following:
oracle.system.eml.mntr.emdRepPwd=new_password oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

** NOTE: When you restart the OMS, the FALSE setting will change to TRUE and the password will become encrypted in the file

5. On the Management Repository host stop the Grid Control Agent
$ export ORACLE_HOME=/01/app/oracle/product/agent10g
$ cd $ORACLE_HOME/bin
$ ./emctl stop agent

6. Update the targets.xml file in the AGENT_HOME/sysman/emd directory and save changes
$ cd $ORACLE_HOME/sysman/emd
$ vi targets.xml

Update the Following
..
Property NAME="password" VALUE="newpassword" ENCRYPTED="FALSE"
..

** NOTE: The TRUE value will change to FALSE and the newpassword will become encrypted when you restart the Agent.

6. Re-Start the Agent
$ cd $ORACLE_HOME/bin
$ ./emctl start agent

7. Set the ORACLE_HOME, etc for the OMS
$ export ORACLE_HOME=/u01/app/oracle/product/oms10g
$ export PATH=ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH

8. Re-start each of the OMS's associated with the Grid Control Repository database.
$ cd $ORACLE_HOME/opmn/bin
$ ./opmnctl startall

9. Change the OMS using WEB Browser

http://oel54node1.localdomain:7777/em/console/logon/logon

Real Application Testing Database Replay

2010-02-20T15:22:00.000-05:00

Apply all Recommended Patches for Database Replay

1. For 11.1.0.6 a patch is recommended for proper database replay functionality. The patch can be download for 11.1.0.6 for you platform from Metalink. For Unix the patch number is 6865809 and the patch for windows 32-bit 7044721
** For other database platforms see Appendix A: Real Application Testing for Earlier Releases

2. Shutdown all databases and listeners running within the ORACLE_HOME being patched.

3. Ensure that opatch utility version 11.1.0.6.3 is installed prior to installing patch set

C:\Oracle\product\11.1.0\db_1\OPatch>.\opatch version
Invoking OPatch 11.1.0.6.2

OPatch Version: 11.1.0.6.2

OPatch succeeded.

4. Change directory to the location where the 7044721 patch was extracted to

5. Apply the patch using the opatch utility
opatch apply

** Ensure that all patch instructions are followed completely including post apply steps. See specific patch notes.

6. Bring all databases and listeners back up.

7. Verify database connectivity and listener status
Setup Source Database for Database Replay Capture
Database Releases prior to 11g can be utilized for capturing a workload for replay on an 11g and above database, however there is some setup that is required. For database release 10.2.0.4 there are no required patches for the capture functionality however for releases prior to 10.2.0.4 a patch is required.
** See Appendix A: Real Application Testing for Earlier Releases, for Versions prior to 10.2.0.4

Run the wrrenbl.sql script, only required for 10.2.0.4 and is optional for other releases prior to 11g:
Script will enable the PRE_11G_ENABLE_CAPTURE database initialization parameter.

a. cd $ORACLE_HOME/rdbms/admin
b. sqlplus / as sysdba
c. SQL> @wrrenbl.sql

Prepare to Capture Workload
On the source database where we will be capturing the workload for replay we need to prepare the database for capture.

1. Create a directory location where the workload capture file can be written.
a. C:\Oracle>mkdir dbcapture

2. Create a directory within the source database where workload is to be captured.
a. SQL> create directory dbcapture as 'c:\oracle\dbcapture' ;

3. Determine the workload to be captured. In this example the workload is artificially generated using a script process.
** see Appendix B: Script that Generates an Artificial Workload

Capture Workload

1. start the capture process
a. SQL> exec dbms_workload_capture.start_capture(’NRML_ACTIVITY’, ’DBCAPTURE’) ;

2. Run the workload
a. This is where the script for the artificial workload is executed

3. When the workload is complete or you have captured the desired workload stop the capture process.
a. SQL> exec dbms_workload_capture.finish_capture() ;

4. Execute a report on the Capture.
** See Appendix C: Generate Workload Capture Report (HTML)

Prepare the Workload for Replay
** This database that processes the captured workload must be an 11.1.0.6 or above database as the packages to process the captured workload are not available in prior releases.

1. Create a directory location for the captured workload to be paced for processing and replay on both the processing database and the replay database if the processing and replay databases are different. Best practice is to process the workload on the same database that will execute the replay.
a. C:\Oracle> mkdir Replay

2. Copy the captured workload files to another directory if on same host as database where workload was captured or to destination system location to be processed.
a. copy C:\Oracle\dbcapture\* C:\Oracle\Replay

3. Create directory in the oracle database that will process the captured workload.
a. SQL> create directory REPLAY as ‘c:\Oracle\Replay’ ;

Process the Workload for Replay
** This is a resource intensive process therefore it should be done on a non-production system. Therefore if you captured the workload on your production system move the capture files to a test or development system to prepare them for replay.

1. Process the captured workload
a. SQL> exec dbms_workload_replay.process_capture ('REPLAY') ;

The process will create the following files in the REPLAY directory during the processing of the captured workload.

wcr_login.pp
WCR_SCN_ORDER.EXTB
WCR_SEQ_DATA.EXTB
WCR_CONN_DATA.EXTB
wcr_process.wmd

Prepare to Replay the Workload
1. Go to database where replay is to be executed if different from database the processed workload. If the replay database is the same as the database that processed the workload skip to step 3 otherwise process step 2.

2. If not done so already go to Prepare workload for Replay Section and execute the steps on the database that will replay the workload.

3. Initialize the replay
a. SQL> exec dbms_workload_replay.initialize_replay ('TEST_REPLAY','REPLAY') ;

4. Prepare for Workload Replay
a. SQL> exec dbms_workload_replay.prepare_replay(synchronization=>TRUE) ;

5. Calibrate the Workload to determine the number of workload replay clients.
a. At the command line change to the replay directory
i. cd C:\Oracle\Replay
ii. c:\Oracle\Replay> wrc mode=calibrate

Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Jul 24 10:38:41 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Report for Workload in: .
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:
- max concurrency: 9 sessions
- total number of sessions: 46

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

Execute the Workload Replay
Now that the workload has been captured and processed the workload is now ready to be replayed.

1. Take the number of workload replay clients indicated in the previous section that will be needed to execute the processed workload.

2. Open up a window for each workload replay client that will be needed as these will each be separate executions.
** Only Need 1 client as indicated in the pervious step for the example workload

3. From the command line enter the replay command with the wrc utility for the number of replay clients needed, should be one command in each window.
a. C:\Oracle\proccapture>wrc system/xxxxx@orcl11g replaydir=c:\Oracle\Replay

4. Start each of the replay clients needed.

C:\Oracle\proccapture>wrc system/xxxxx@orcl11g replaydir=c:\Oracle\Replay

Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Jul 24 15:09:15 2
008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (15:09:15)
Replay started (15:09:40)
Replay finished (16:01:41)

5. AWR information will be available for the time period where the workload apply was executed, it is generally recommended that the workload apply that is executed at least cover 1 complete snap period to provide the most useful data or that a snap is taken prior to the start and immediately after the workload execution. (this is optional)

SQL> execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

6. In another window login to the database where the workload apply is to be execute and start the workload apply process. You will see an indication that the workload has been started.

$ sqlplus /@orcl11g

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 24 13:55:39 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

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

SQL> exec dbms_workload_replay.start_replay ;

PL/SQL procedure successfully completed.

7. Monitor the workload apply until completed. AWR information will be available for the time period where the workload apply was executed, it is generally recommended that the workload apply that is executed at least cover 1 complete snap period to provide the most useful data or that a snap is taken prior to the start and immediately after the workload execution.

8. Once the workload apply is complete optionally take another AWR snaphot.

SQL> execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

9. Execute workload replay report extraction and evaluate the results from the report.
** See Appendix D: Generate Workload Replay Report (HTML)

Appendix A: Real Application Testing for Earlier Releases
See Metalink Note: 560977.1

Appendix B: Script that Generates an Artificial Workload

-- create_workload.sql
-- put in sql script to be executed to generate workload.
create table orders tablespace users as select * from oe.orders ;

create table order_items tablespace users as select * from oe.order_items ;

-- insert more rows into orders and order items to bulk up amount of data for
-- a more true high volume test.
declare
cursor my_cur is
select * from oe.orders ;
lcnt number ;
v_order_id oe.orders.order_id%type ;
v_order_date oe.orders.order_date%type ;
v_order_mode oe.orders.order_mode%type ;
v_customer_id oe.orders.customer_id%type ;
v_order_status oe.orders.order_status%type ;
v_order_total oe.orders.order_total%type ;
v_sales_rep_id oe.orders.sales_rep_id%type ;
v_promotion_id oe.orders.promotion_id%type ;
cursor my_cur_2 is
select line_item_id, product_id, unit_price, quantity
from oe.order_items where order_id = v_order_id ;
v_line_item_id oe.order_items.line_item_id%type ;
v_product_id oe.order_items.product_id%type ;
v_unit_price oe.order_items.unit_price%type ;
v_quantity oe.order_items.quantity%type ;

begin
select max(order_id) + 1 into lcnt from orders ;

loop
if lcnt = 10000 then
exit ;
end if ;

open my_cur ;
loop
fetch my_cur into v_order_id, v_order_date, v_order_mode, v_customer_id,
v_order_status, v_order_total, v_sales_rep_id, v_promotion_id ;
exit when my_cur%notfound ;

insert into orders values (lcnt, v_order_date, v_order_mode, v_customer_id,
v_order_status, v_order_total, v_sales_rep_id, v_promotion_id) ;
commit ;

open my_cur_2 ;
loop
fetch my_cur_2 into v_line_item_id, v_product_id, v_unit_price, v_quantity ;
exit when my_cur_2%notfound ;

insert into order_items values (lcnt, v_line_item_id, v_product_id, v_unit_price, v_quantity) ;
end loop ;
close my_cur_2 ;

lcnt := lcnt + 1 ;
end loop ;

close my_cur ;
end loop ;
end ;
/

Appendix C: Generate Workload Capture Report (HTML)

-- Capture Report
DECLARE
cap_id NUMBER;
cap_rpt CLOB;
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
directory_name CONSTANT VARCHAR2(80) := 'DBCAPTURE';
v_filename CONSTANT VARCHAR2(80) := 'Capture.html';

BEGIN
cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir=>directory_name);
cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

-- Write Report to file
DBMS_OUTPUT.ENABLE(100000);

-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
file_handle := UTL_FILE.FOPEN(location => directory_name,
filename => v_filename,
open_mode => 'w',
max_linesize => buffer_size);

amount := buffer_size;
offset := 1;

WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(lob_loc => cap_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;

UTL_FILE.PUT(file => file_handle,
buffer => buffer);

UTL_FILE.FFLUSH(file => file_handle);
END LOOP;

UTL_FILE.FCLOSE(file => file_handle);
END;
/

Appendix D: Generate Workload Replay Report (HTML)

-- Replay Report
DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
directory_name CONSTANT VARCHAR2(80) := 'PROCCAPTURE';
v_filename CONSTANT VARCHAR2(80) := 'Replay.html';

BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'PROCCAPTURE');

select max(id)
into rep_id
from dba_workload_replays
where capture_id = cap_id ;

rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);

-- Write Report to file
DBMS_OUTPUT.ENABLE(100000);

-- --------------------------------
-- OPEN NEW XML FILE IN WRITE MODE
-- --------------------------------
file_handle := UTL_FILE.FOPEN(location => directory_name,
filename => v_filename,
open_mode => 'w',
max_linesize => buffer_size);

amount := buffer_size;
offset := 1;

WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;

UTL_FILE.PUT(file => file_handle,
buffer => buffer);

UTL_FILE.FFLUSH(file => file_handle);
END LOOP;

UTL_FILE.FCLOSE(file => file_handle);
END;
/

Transfer SQL Tuning Sets between Databases

2010-02-20T15:17:00.000-05:00

1. Create a staging table to put the SQL tuning sets into. Then put the SQL Tuning Sets into the staging table.
See Appendix A: Create SQL Tuning Set table and put SQL Tuning Sets into table

2. Ensure that you have a directory in Oracle to use to export the table using datapump
SQL> create directory DATA_PUMP_DIR as ‘C:\oracle\product\10.2.0\admin\orcl\dpdump\’ ;

3. Export the SQL Tuning Set Staging Table to a file as to analyze the SQL tuning set will need to be done in a 11g database.

expdp /@orcl directory=DATA_PUMP_DIR dumpfile=sqlset_stage_tab_exp.dmp logfile=sqlnet_stage_tab_exp.log tables=mrmessin.sqlnet_stage_tab

Export: Release 10.2.0.4.0 - Production on Monday, 04 August, 2008 19:40:41

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MRMESSIN"."SYS_EXPORT_TABLE_01": /********@orcl directory=DATA_PUMP_DIR dumpfi
xp.dmp logfile=sqlnet_stage_tab_exp.log tables=MRMESSIN.SQLSET_STAGE_TAB
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "MRMESSIN"."SQLSET_STAGE_TAB" 49.13 KB 44 rows
. . exported "MRMESSIN"."SQLSET_STAGE_TAB_CPLANS" 73.24 KB 175 rows
. . exported "MRMESSIN"."SQLSET_STAGE_TAB_CBINDS" 9.492 KB 0 rows
Master table "MRMESSIN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MRMESSIN.SYS_EXPORT_TABLE_01 is:
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\SQLSET_STAGE_TAB_EXP.DMP
Job "MRMESSIN"."SYS_EXPORT_TABLE_01" successfully completed at 19:41:16

4. create a directory for the 11g database we are going to import the sqlset staging table into.
create directory DATA_PUMP_DIR as ‘c:\oracle\admin\orcl11g\dpdump\’ ;

5. Copy the file to the 10g database DATA_PUMP_DIR location to the DATA_PUMP_DIR location for the 11g database.
copy C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP\SQLSET_STAGE_TAB_EXP.DMP c:\oracle\admin\orcl11g\dpdump\

6. Import the SQL tuning set staging table into the 11g database.

impdp /@orcl11g full=y dumpfile=sqlset_stage_tab_exp.dmp directory=DATA_PUMP_DIR logfile=sqlset_stage_tab_imp.log

Import: Release 11.1.0.6.0 - Production on Monday, 04 August, 2008 20:04:38

Copyright (c) 2003, 2007, Oracle. All rights reserved.

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
Master table "MRMESSIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MRMESSIN"."SYS_IMPORT_FULL_01": /********@orcl11g full=y dumpfile=sqlset_stage_ta
y=DATA_PUMP_DIR logfile=sqlset_stage_tab_imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MRMESSIN"."SQLSET_STAGE_TAB" 49.13 KB 44 rows
. . imported "MRMESSIN"."SQLSET_STAGE_TAB_CPLANS" 73.24 KB 175 rows
. . imported "MRMESSIN"."SQLSET_STAGE_TAB_CBINDS" 9.492 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "MRMESSIN"."SYS_IMPORT_FULL_01" successfully completed at 20:06:22

7. Move the SQL Tuning Sets in the imported Staging table back into database SQL Tuning Sets
See APPENDIX B: Put SQL Tuning Sets in Imported Staging Table Back into Database SQL Tuning Sets

8. Run Analysis on the SQL Tuning Sets

Appendix A: Create SQL Tuning Set table and Put SQL Tuning Sets into Table

---------------------------------------------------------------------------
-- Script: spa_sts_to_table.sql
-- Author; Michael R. Messina, Senior Consultant TUSC, A Rolta Company
--
-- create a staging table to put the SQL tuning sets into and then
-- put the SQL tuning sets into the stage table to be moved to another
-- database.
---------------------------------------------------------------------------
--
set serveroutput on size 1000000

BEGIN
DBMS_OUTPUT.PUT_LINE ('Creating the staging table to put the sql tuning set into.') ;
-- create the staging table to put the sql tuning set into
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_STAGE_TAB',
schema_name => 'MRMESSIN',
tablespace_name => 'USERS');

DBMS_OUTPUT.PUT_LINE ('Putting the first SQL tuning sets into the staging table.') ;
-- put the first SQL tuning set into the staging table
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'STS_SPA_1',
sqlset_owner => 'MRMESSIN',
staging_table_name => 'SQLSET_STAGE_TAB',
staging_schema_owner => 'MRMESSIN');

END ;
/

---------------------------------------------------------------------------
-- Query and view our sql tuning sets
---------------------------------------------------------------------------
select owner, name, created, statement_count from dba_sqlset ;

spool off

APPENDIX B: Put SQL Tuning Sets in Imported Staging Table Back into Database SQL Tuning Sets

---------------------------------------------------------------------------
-- Script: spa_stage_tab_to_sts.sql
-- Author; Michael R. Messina, Senior Consultant TUSC, A Rolta Company
--
-- take the imported staging table for the SQL Tuning Sets and unpack them
-- back into STS for analysis
---------------------------------------------------------------------------
--
-- Write Output to a file for later review if needed.
spool spa_stage_tab_to_sts.out

BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => '%',
sqlset_owner => 'MRMESSIN',
replace => TRUE,
staging_table_name => 'SQLSET_STAGE_TAB',
staging_schema_owner => 'MRMESSIN');
END;
/

---------------------------------------------------------------------------
-- Query and view our sql tuning sets
---------------------------------------------------------------------------
select owner, name, created, statement_count from dba_sqlset ;

spool off

2010-02-19T21:43:00.000-05:00

Just came back from RMOUG and excited about all the technical discussions I was able to have with everyone. I saw some good presentations and met some great people. I was able to get through all my presentations and able to give away a lot of free stuff in them as well. Hope everyone enjoyed the free stuff, I enjoyed giving it out. Come see me at collaborate, I will give out some more free stuff, you never know what I might give out next.

2009-10-17T08:25:00.000-04:00

All,

There was a lot of interest in my Oracle Open World Presentation on Centralizing the Automatic Worklad Repository (Central AWR). If you are interested you can email me, leave a message for me here or watch here as I will update when I have further information on its availability.

Monitoring and Tuning Oracle Flashback Capabilities

2009-01-28T10:38:00.001-05:00

Determining the Current Flashback Database Window
At any given time, the earliest point in time to which you can actually rewind your database by using Flashback Database can be determined by querying the V$FLASHBACK_DATABASE_LOG view as shown in this example:

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;

Monitoring Flashback Logs
Monitoring the flashback logs can be monitored to help understand the overhead and the nature of flashback log generation.

SQL> select * from v$flashback_database_stat ;

Determine Transactional Flashback

Definitions
Current Undo Size – Current space for the undo tablespace in MB

Current undo_retention Setting – value of undo_retention database initialization parameter

Optimal undo_retention Setting – current capability of undo retention in seconds based on undo transactional activity and current undo size.

Desired undo_retention Setting – value in seconds to meeting undo/transactional flashback requirements

Steps to Gather Data for undo/transactional Flashback
1. Locate/Create see_optimal_undo_retention.
See Appendix A: see_optimal_undo_retention.sql

2. Execute see_optimal_undo_retention.sql
SQL> @see_optimal_undo_retention.sql

3. Enter Desired Undo Retention in Seconds
259200 -- 72 hours or 3 days in seconds

4. Evaluate output results

Output from see_optimal_undo_retention.sql
Enter Desired Undo Retention in Seconds ->259200
old 2: v_desired_undo_retention NUMBER := &desired_undo_retention ;
new 2: v_desired_undo_retention NUMBER := 259200 ;

Current undo Size --> 16912MB
Current undo_retention Setting --> 25200 Seconds
Optimal undo_retention Setting --> 221129.88 Seconds
Desired undo_retention Setting --> 259200 Seconds
Undo Size for desired retention --> 10021.36MB

Evaluating output results from see_optimal_undo_retention.sql

Is the current undo_retention optimal?
To evaluate if the current undo_retention is being met and see if undo_retention can be increased look at the current undo_retention setting and the optimal undo_retention_setting over several executions over a period of time. If the optimal undo_retention_setting is consistently more then the current undo_retention_setting then undo_retention can be increased.

Can the database meet the desired undo_retention?
The main consideration from the output is boiled down to the current value of the desired undo_retention setting and the optimal undo retention setting. When the optimal undo retention setting is lower then the desired undo_retention setting it means that the database is not able to meet the desired retention. Evaluate the undo size for desired retention to determine the undo tablespace size require to minimally meet the desired undo_retention.

Monitor Undo Retention

With undo_retention set we need to ensure that the undo_retention is being met by the database. Therefore the undo_retention capability must be consistently monitored.

1. Locate/create verify_undo_retention_target.sql
See Appendix B: verify_undo_retention_target.sql

2. Execute the verify_undo_retention.sql script
SQL> @verify_undo_retention_target

3. Evaluate output from script

Example Output when Undo Retention is being met
SQL> @verify_undo_retention_target

Example Output when Undo Retention is no being met
SQL> @verify_undo_retention_target
Undo Retention Target can not be Met! Undo Adjustments Needed!
---------------------------------------------------------------
Current undo Size --> 30MB
Current undo_retention Setting --> 2000 Seconds
Current undo_retention Capable --> 1106.1 Seconds
Undo Size for desired retention --> 54.24MB

Performance Tuning for Flashback Database

Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.

To achieve good performance for large production databases with Flashback Database enabled, Oracle recommends:

· Use a fast file system for your flash recovery area, preferably without operating system file caching. Files the database creates in the flash recovery area, including flashback logs, are typically large. Operating system file caching is typically not effective for these files, and may actually add CPU overhead for reading from and writing to these files. Thus, it is recommended to use a file system that avoids operating system file caching, such as ASM, or the Solaris 2.8 file system with direct I/O.

· Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.

· If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance

· For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.

The overhead of turning on logging for Flashback Database depends on the read-write mix of the database workload. The more write-intensive the workload, the higher the overhead caused by turning on logging for Flashback Database. (Queries do not change data and thus do not contribute to logging activity for Flashback Database.)

Appendix A: see_optimal_undo_retention.sql

---------------------------------------------------------------------------------
-- Script: see_optimal_undo_retention.sql
-- Author: Michael Messina
--
-- Description: Will calculate based on undo utilization and undo size
-- the optimal value for undo_retention.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off

ACCEPT desired_undo_retention PROMPT "Enter Desired Undo Retention in Seconds ->"

DECLARE
v_desired_undo_retention NUMBER := &desired_undo_retention ;
v_block_size NUMBER ;
v_undo_size NUMBER ;
v_undo_blocks_per_sec NUMBER ;
v_optimal_undo_retention NUMBER ;
v_current_undo_retention NUMBER ;
v_undo_size_desired_ret NUMBER ;

BEGIN
-- get the current undo retention setting
select TO_NUMBER(value)
INTO v_current_undo_retention
FROM v$parameter
WHERE name = 'undo_retention' ;

-- Calculate Actual Undo Size
SELECT SUM(a.bytes)
INTO v_undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

-- Calcuate the Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
INTO v_undo_blocks_per_sec
FROM v$undostat ;

-- Get the database block size
SELECT TO_NUMBER(value)
INTO v_block_size
FROM v$parameter
WHERE name = 'db_block_size';

v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;
v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_desired_undo_retention)) / 1024 / 1024 ;

DBMS_OUTPUT.PUT_LINE (' Current undo Size --> ' TO_CHAR(v_undo_size/1024/1024) 'MB') ;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' TO_CHAR(v_current_undo_retention) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE (' Optimal undo_retention Setting --> ' TO_CHAR(ROUND(v_optimal_undo_retention,2)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE (' Desired undo_retention Setting --> ' TO_CHAR(ROUND(v_desired_undo_retention,2)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' TO_CHAR(ROUND(v_undo_size_desired_ret,2)) 'MB') ;
END ;
/

Appendix B: verify_undo_retention_target.sql

---------------------------------------------------------------------------------
-- Script: verify_undo_retention_target.sql
-- Author: Michael Messina

--
-- Description: Will calculate based on undo utilization and undo size
-- the optimal value for undo_retention and compare against
-- current undo_retention value.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off

DECLARE
v_block_size NUMBER ;
v_undo_size NUMBER ;
v_undo_blocks_per_sec NUMBER ;
v_optimal_undo_retention NUMBER ;
v_current_undo_retention NUMBER ;
v_undo_size_desired_ret NUMBER ;

BEGIN
-- get the current undo retention setting
select TO_NUMBER(value)
INTO v_current_undo_retention
FROM v$parameter
WHERE name = 'undo_retention' ;

-- Calculate Actual Undo Size
SELECT SUM(a.bytes)
INTO v_undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

-- Calculate the Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
INTO v_undo_blocks_per_sec
FROM v$undostat ;

-- Get the database block size
SELECT TO_NUMBER(value)
INTO v_block_size
FROM v$parameter
WHERE name = 'db_block_size';

v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;

if v_current_undo_retention > v_optimal_undo_retention then
v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_current_undo_retention)) / 1024
/ 1024 ;
DBMS_OUTPUT.PUT_LINE ('Undo Retention Target can not be Met! Undo Adjustments Needed!') ;
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------------------') ;
DBMS_OUTPUT.PUT_LINE (' Current undo Size --> ' TO_CHAR(v_undo_size/1024/1024) 'MB')
;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' TO_CHAR(v_current_undo_retention) ' S
econds') ;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Capable --> ' TO_CHAR(ROUND(v_optimal_undo_retention,2
)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' TO_CHAR(ROUND(v_undo_size_desired_ret,2)
) 'MB') ;
end if ;

END ;
/

Oracle Flashback Table

2009-01-28T10:24:00.000-05:00

Summary

Oracle Flashback Table provides the capability to essentially recover a single table or multiple tables to a prior specified point in time without taking impacting any part of the databases availability. Flashback Table allows the contents of one or more individual tables to revert to their state at some past SCN or time. Flashback Table can eliminate the need to perform auxiliary point-in-time recovery operations. Flashback Table can restore tables to a prior point in time while maintaining all associated indexes, triggers and constraints. This is most valuable when a large amount of data in the table has been removed or updated by a process and you want to bring the table back to the state prior to the process execution.

Flashback Table uses the undo tablespace block data to execute the restoration of the table. You do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.

Flashback Table does however require row movement to be enabled and the flashback object privilege or the flashback any table system privilege in addition to select, insert, update, delete and alter permissions on the table.

** Note: This is a permanent process, once the table is flashed back you can not bring it back forward again, therefore be careful about the flash back table operations you perform.

Performing Flashback Table
Simple example will utilize the scott.emp table with the following records:

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Setting up for the example we will remove some rows from the scott.emp table.

SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;

TO_CHAR(SYSDATE,'MM
-------------------
01/23/2009 08:38:01

SQL> delete from scott.emp where empno = 7369 ;

1 row deleted.

SQL> delete from scott.emp where empno = 7782 ;

1 row deleted.

SQL> commit ;

Commit complete.

1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

12 rows selected.

Or a way to show what is missing or updated using a flashback query.

SQL> select * from scott.emp as of timestamp to_timestamp('01/23/2009 08:38:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

2. Identify the time or SCN the table has to go back to.

3. Flashback the table

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Table altered.

SQL> FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
2 TO_TIMESTAMP('2009-01-23 08:38:00', 'YYYY-MM-DD HH24:MI:SS')
3 ;

Flashback complete.

4. Check the table and verify it is back to prior state.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

5. Show that the operation can not be rolled back.

SQL> rollback ;

Rollback complete.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

6. Even if we exit the session and log back in the changes are still in place

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus mrmessin@orcl

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 23 08:56:43 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Oracle Flashback Query

2009-01-28T10:13:00.000-05:00

Summary

Oracle Flashback Query feature utilizes the undo segments within the undo tablespace to execute a query as the query would have returned at a prior point in time using a timestamp or SCN number. This feature is powerful in the event of erroneously delete or update rows. How far back to a previous point in time Flashback Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.

Executing a Flashback Query
Procedure assumes that the flashback capabilities were setup and tuned as outline in Setup Oracle Transactional Flashback Capability (Automatic Undo)

Simple examples will utilize the scott.emp table with the following records:

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Delete Example

Setting up for the example we will remove some rows from the scott.emp table.

SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;

TO_CHAR(SYSDATE,'MM
-------------------
01/21/2009 09:53:21

SQL> delete from scott.emp where empno = 7369 ;

1 row deleted.

SQL> delete from scott.emp where empno = 7782 ;

1 row deleted.

SQL> commit ;

Commit complete.

1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

12 rows selected.

2. Next step is to identify what prior point in time the table has the records you are looking for. This will more then likely require input from the customer that reporting the missing data.
In the example case we will want to go back before 01/21/2009 9:53am.

3. Execute the query with flashback syntax using a timestamp

SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

OR

SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53:00','mm/dd/yyyy hh24:mi:ss') ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
** 7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
** 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

** As you can see the rows deleted are there……

4. Execute Repair and put the deleted rows back

SQL> insert into scott.emp values ('7369','SMITH','CLERK','7902', TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NUL
L,20) ;

1 row created.

SQL> insert into scott.emp values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-1981','DD-MON-YYYY'),2450,N
ULL,10) ;

1 row created.

SQL> commit ;

Commit complete.

Update Example

Setting up for the example we will update some rows from the scott.emp table.

SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;

TO_CHAR(SYSDATE,'MM
-------------------
01/22/2009 10:36:18

SQL> update scott.emp set sal=10000 where empno=7369 ;

1 row updated.

SQL> update scott.emp set sal=10000 where empno=7782 ;

1 row updated.

SQL> commit ;

Commit complete.

1. Verify the data is updated

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10000 20
7782 CLARK MANAGER 7839 09-JUN-81 10000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

2. Next step is to identify what prior point in time the table has the records correctly. This will more then likely require input from the customer that reporting the missing data.

3. Execute the query with flashback syntax using a timestamp

SQL> select * from scott.emp as of timestamp to_timestamp('01/22/2009 10:36:00','mm/dd/yyyy hh24:mi:ss') ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

OR Show only the rows that are different.

SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 10:36:00','mm/dd/yyyy hh24:mi:ss')
2 minus
3 select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

4. Execute Repair and put the deleted rows back

SQL> update scott.emp set sal=800 where empno=7369 ;

1 row updated.

SQL> update scott.emp set sal=2450 where empno=7782 ;

1 row updated.

SQL> commit ;

Commit complete.

Setup Oracle Flashback Database

2009-01-28T09:21:00.001-05:00

Requirements for Flashback Database
1. Your database must be running in ARCHIVELOG mode.

2. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.

3. For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

4. To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter

Setups for Setup Oracle Flashback Database
1. Check that the db_flashback_retention_target, db_recovery_file_dest and db_recovery_file_dest_size are set

SQL> select name, value from v$parameter where name like '%flash%';
NAME VALUE
-------------------------------- --------------------------------------- db_flashback_retention_target 1440

SQL> select name, value from v$parameter where name like '%recovery%';
NAME VALUE
------------------------ --------------------------------------- db_recovery_file_dest /opt/oracle/flash_recovery_area
db_recovery_file_dest_size 2147483648

2. Make sure the database is in archive log mode.
SQL> select name, log_mode from v$database ;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOG

3. If database not in archive log mode turn on Archive log mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.

SQL> alter database archivelog ;
Database altered.

SQL> alter database open ;
Database altered.

4. Turn the flashback on
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;
Database altered.

SQL> alter database open ;
Database altered.

Setup Oracle Transactional Flashback Capability (Automatic Undo)

2009-01-28T09:02:00.000-05:00

1. Check if Automatic Undo Management is begin used, undo tablespace is set and undo_retention is set?

SQL> select name, value from v$parameter where name like 'undo%';

NAME VALUE
---------------------- -----------------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900

2. If Not Using Automatic Undo Setup Automatic Undo

Create undo tablespace
create undo tablespace undotbs1 datafile ‘c:\oracle\data\ORCL\undotbl1.dbf’ size 500M ;

Set undo_tablespace to undo tablespace created
Alter system set undo_tablespace=undotbs1 scope=spfile ;

Set undo_retention to time period for transactional flashbacks alter system set undo_retention=900 scope=spfile ;

3. If had to setup auto undo bounce database
SQL> shutdown immediate
SQL> startup

4. Update transactional flashback sizing and retention over time
See Sizing Transactional Flashback Capabilities - Sizing the undo tablespace and undo_retention

Oracle Flashback Transaction Query

2009-01-28T08:49:00.000-05:00

Summary

Flashback Transaction Query has a lot of the same functionality as Log Miner without the additional overhead associated with Log Miner mining redo logs for undo information. Flashback Transaction Query utilizes an access path to undo data which makes it faster than LogMiner at extracting the undo information. Unlike flashback query, flashback transaction query requires the select any transaction system privilege. How far back to a previous point in time Flashback Transaction Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
See Monitoring and Tuning Flashback Capabilities

Executing Flashback Transaction Query
Procedure assumes that the transaction flashback capabilities were setup and tuned as outline in Setup Transactional Flashback Capability (Automatic Undo)

Simple example will utilize the scott.emp table with the following records:

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

1. Record current scn prior to making any changes

SQL> select current_scn scn from sys.v_$database ;

SCN
----------
1068761

2. Change some records with various inserts, updates and deletes.

SQL> delete from scott.emp where empno = 7369 ;

1 row deleted.

SQL> delete from scott.emp where empno = 7782 ;

1 row deleted.

SQL> update scott.emp set sal=10000 where empno = 7876 ;

1 row updated.

SQL> update scott.emp set sal=0 where empno = 7839 ;

1 row updated.

SQL> commit ;

Commit complete.

3. Verify data changes

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 0 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 10000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

12 rows selected.

4. Review Undo for Changes Made

SQL> select /*undo_change#,*/ undo_sql
2 from flashback_transaction_query
3 where table_name = 'EMP' and
4 table_owner = 'SCOTT' and
5 logon_user = 'SYS' and
6 commit_scn > 1068761
7 order by undo_change# ;

UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');

5. Undo Changes
See Example Script Below Appendix A: Fix Script Example using Flashback Transaction Query

SQL> @flashback_transaction_query.sql
Enter value for scnnumber: 1068761
old 8: commit_scn > &scnnumber
new 8: commit_scn > 1068761
undo_change#: 1
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
undo_change#: 2
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
undo_change#: 3
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81',
'DD-MON-RR'),'2450',NULL,'10');
undo_change#: 4
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80',
'DD-MON-RR'),'800',NULL,'20');

PL/SQL procedure successfully completed.

SQL> commit ;

Commit complete.

Or execute the SQL statements from the prior query

update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';

update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');

insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');

6. Verify Changes are Un-done

SQL> select * from scott.emp ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Appendix A: Fix Script Example using Flashback Transaction Query

SET SERVEROUTPUT ON SIZE 1000000
begin
for r in (

select /*undo_change#,*/ undo_sql
from flashback_trasnaction_query
where table_name = 'EMP' and
table_owner = 'SCOTT' and
logon_user = 'SYS' and
commit_scn > &scnnumber
order by undo_change#
) loop

dbms_output.put_line('r.undo_change#: ' r.undo_change#);
dbms_output.put_line(' ' r.undo_sql);

execute immediate
substr(r.undo_sql, 1, length(r.undo_sql)-1);

end loop;
end ;
/

RMAN Backup Encryption

2009-01-14T21:41:00.000-05:00

Summary

Oracle backups are necessary to ensure that data is not lost in the event of hardware failures. RMAN is the Oracle recommended way to backup Oracle databases. RMAN has significantly improve Oracle database backups through multi-threaded hot backups, compression, simplified recovery and others. However backups are a way the database data could be exposed. Oracle backups by default are not encrypted and therefore unprotected should the backup be exposed to outside copies that are capable of being restored sue to lack of protection. So how to protect RMAN backups from being able to be restored by unauthorized persons and exposing the data? The answer is encrypting the RMAN backups. RMAN backup encryption requires the use of the Advanced Security Option (ASO). Advanced Security Option requires an additional license therefore before using check your Oracle license and ensure you are authorized to use the Advanced Security Option. Keep in mind that image and datafile copies can not be encrypted.

Oracle offers several flavors for encrypting the RMAN backups:

1. Transparent Encryption -> Requires Wallet on backup and recovery. (Do Not Lose the Wallet!)

2. Password Only -> Required Password on backup and recovery (Do Not Lose Password!)

3. Dual (Password or Transparent Encryption) -> Can be backed up or restored using the password or a wallet. This works well where database is restored locally where the wallet exists, but has a need to be able to be restored off site where the wallet does not exist.

When restoring encrypted backups Oracle RMAN always assumes transparent encryption using a wallet. Therefore during a restore operation and using transparent data encryption ensure the wallet is open, when using password only the password must be supplied, when using dual either the wallet must be open or the password must be supplied.

Setup and Create a Wallet for TDE

1. Set the encryptrion wallet location in the sqlnet.ora file on the database server
Unix server sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/encrypt)
)
)
WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/authent)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

2. Create the wallet
o Ensure the oracle account running the database has permissions on the encryption wallet directory defined in the sqlnet.ora file or an ORA-28368: cannot auto-create wallet will occur.
o sqlplus “/ AS SYSDBA”
o SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “mypass” ;

Opening and Closing the Wallet

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password>
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE

Encrypting the RMAN Backup

1. Check the available algorithms for encryption.
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name ;

ALGORITHM_ID ALGORITHM_NAME
------------ ----------------------------------------------------------------
ALGORITHM_DESCRIPTION IS_ RES
---------------------------------------------------------------- --- ---
1 AES128
AES 128-bit key YES NO

2 AES192
AES 192-bit key NO NO

3 AES256
AES 256-bit key NO NO

2. Set the Backup Encryption Type via RMAN

Connect to the target database and RMAN catalog database if being used. Within RMAN set the encryption type via an RMAN configure command. This setting will be stored within the database control file and the RMAN catalog if one is being used.

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES192';

3. Configure the encryption for the backup

Transparent Backup Encryption (Requires Wallet)
CONFIGURE ENCRYPTION FOR DATABASE ON ;

Turn Encryption off

CONFIGURE ENCRYPTION FOR DATABASE OFF ;

Password Only Encryption

SET ENCRYPTION ON IDENTIFIED BY password ONLY ;

Dual Password/Transparent Encryption

SET ENCRYPTION ON IDENTIFIED BY password ;

4. Execute the backup Database Backup Encrypted

Transparent Encryption** Wallet Must be opened

RMAN> RUN {

# Set the RMAN Encryption
CONFIGURE ENCRYPTION FOR DATABASE ON ;

# Backup the database
BACKUP DATABASE ;
}

Database Backup Encrypted using Password Encryption Only

RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY ONLY ;

# Backup the database
BACKUP DATABASE ;
}

Database Backup Encrypted using Password Encryption or Wallet Encryption

RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY ;

# Backup the database
BACKUP DATABASE ;
}

Tablespace Backup Encryption

RMAN> RUN {
# First, clear the current RMAN encryption settings ...
CONFIGURE ENCRYPTION FOR DATABASE OFF ;

# ... then activate encryption for specific tablespaces
CONFIGURE ENCRYPTION FOR TABLESPACE example ON;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted ON;

BACKUP TABLESPACE example, tbs_encrypted;
}

Restore Encrypted RMAN Backup

Transparent Encryption Restore

RMAN> RUN {
RESTORE DATABASE ;
RECOVER DATABASE ;
}

Password Encryption Restore

RMAN> RUN {
SET DECRYPTION IDENTIFIED BY ;
RESTORE DATABASE ;
RECOVER DATABASE ;
}

Oracle Heterogenenous Connectivity to MySQL with Database Link 10g

2008-12-14T17:55:00.000-05:00

1. We need to install the ODBC driver for the non-Oracle database we are going to connect to.

2. Once the ODBC Driver is installed we will need to configure a data source for the non-Oracle database. For Windows open the control panel and locate the Administrative Tools. Then locate the Data Sources (ODBC) and double click on the Data Sources.
Select the System DSN as for the HS service it will need to be a System DSN then Click on Add button.
Select the Driver for the non-Oracle data source. In this case we will be using the MySQL 3.51 Driver for a local MySQL database.
In this case we will give this data source the name of mysql configured on the localhost, we will be connecting with the root user. In this case we are on the default port therefore we done have to configure the connect options.

3. After configuring the connection settings we can test if the connection is working by pressing the test button near the bottom left of the Window. A window will indicate if the connection was successful.

4. Configure the init.ora for the HS service that well be used for the non-Oracle database connectivity.

initmysql.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
#HS_DB_NAME = mysql
HS_FDS_CONNECT_INFO = mysql
HS_FDS_TRACE_LEVEL = ON

#
# Environment variables required for the non-Oracle system
#
#set =

5. Need to add the non-Oracle data source to the listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = C:\oracle\product\10.2.0\db_1\lib32)
)
)

6. Reload the listener and check that we have a service for the non-Oracle data source.

C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl reload

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:21

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

7. Check the listener services to make sure the mysql shows

C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:33

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx)(PORT=2323))
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "mysql" has 1 instance(s).
Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

8. Need to now add the non-Oracle data source to the tnsnames.ora

mysql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
(CONNECT_DATA =
(SID = MYSQL)
)
(HS=OK)
)

9. Check that the non-Oracle data source is reachable via a tnsping.

C:\Oracle\product\10.2.0\db_1\BIN>tnsping mysql

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 14-DEC-2008 18:12:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS=OK))OK (30 msec)

10. Create a public database link and check that it works.

C:\Oracle\product\10.2.0\db_1\BIN>sqlplus /@orcl

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 10 20:10:00 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create public database link mysql connect to "root" identified by "xxxxxxxxxx"
using 'mysql' ;

Database link created.

SQL> select count(*) from alfresco.alf_node@mysql ;

COUNT(*)
----------
1469

SQL>

RMAN Tracing

2008-09-12T10:53:00.001-04:00

RMAN Tracing allows us the capability to get wom insight into what RMAN is doing behind the scenes so to speak.

Trace an RMAN Session

Trace an RMAN session to a trace file using the trace=/tracefilename.trc as part of the rman execution. Then in the RMAN execute set the debug on.

Example:

rman target username/password@target catalog username/password@catalog trace=/tmp/rman_test.trc

RMAN> run
{debug on;
report obsolete;
debug off;
}

Trace via the RMAN Channel

Another way to trace RMAN activity would be to configure the trace when configuring the RMAN channel.

1. Make sure timed_statistics = true for all performance testing.

2. set the NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'.

3. Configure the Channel with Trace/Debug options

trace 1 - general trace info for skg and sbt
trace 5 - verbose channel tracing of skg and sbt calls

CONFIGURE CHANNEL DEVICE TYPE
DISK DEBUG=5 TRACE 1;

or

CONFIGURE CHANNEL DEVICE TYPE SBT PARMS "ENV=(....) DEBUG=5 TRACE 5;

Note: (Trace is only useful for tape backup and restore tracing)

Oracle 11g SecureFiles

2008-09-11T12:00:00.001-04:00

Summary

Oracle Secure Files also known as Fast Files is Oracle’s improvement to storage of large object types (LOBs). Secure Files gives comparable performance to a file system for LOBs and LOBs can store many types from data from images, large amounts of text, word documents, excel spreadsheets, XML, HTML, etc. as well as DICOM formatted medical images.

This is a step forward in helping manage unstructured data with the Oracle database by boosting performance, but also by improving security. Secure Files extends Transparent Data Encryption to LOBs, this not only makes storing and managing unstructured content easier, but improves the security of the unstructured content.

If that was not enough Secure Files also gives advanced file system features such as compression and data de-duplication. Data De-duplication is where duplicate objects in LOBs tied to many records within the database is only stored once rather then a copy for each record. This not only improves storage space, but can also offer performance improvements. Compression like it indicates compresses LOB data transparently offering storage savings and a performance boost, but Oracle takes it a step further and automatically determines if the data is able to be compressed and if so are the space savings from the compression of benefit.

By default normal LOB storage is used, called BASIC file. To use SecureFile for LOB storage the SECUREFILE lob Storage keyword must be used.

The default behavior for securefile usage can be changed via the db_securefile initialization parameter.

db_securefile={ALWAYS FORCE PERMITTED NEVER IGNORE}

ALWAYS – Always attempts to create all LOBs as SECUREFILE LOBs
FORCE - all LOBs created in the system will be created as SECUREFILE LOBs.
PERMITTED – Allows LOBs that use SecureFiles.
NEVER – Never Allows LOBs that use SecureFiles, they will always be BASICFILE LOBs
IGNORE – Ignore errors that otherwise would be raised by forcing LOBs as SECUREFILE LOBs

Can be set dynamically via an ALTER SYSTEM:

SQL> ALTER SYSTEM SET db_securefile = 'ALWAYS' ;

Securefiles offer a lot of benefits over the old LOB storage method, such as deduplication capability, compression and encryption.

Compression has 2 forms medium which is the default and high. Keep in mind the high level of compression the larger resource impact on the database you should expect.

CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
(COMPRESS
CACHE
NOLOGGING );

CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
CACHE
NOLOGGING );

Encryption for SecureFiles is implemented via the Transparent Data Encryption (TDE) and SecureFiles extends that TDE for LOB data types. Encryption is performed at the block level and uses the following valid encryption levels 2DES168, AES128, AES192 (default) and AES256. Keep in mind for SecureFiles the NO SALT option is not supported.

CREATE TABLE t1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE
( CACHE );

DeDuplication can be a powerful feature that can reduce the amount of storage space for LOBs as all duplicated LOBs are only stored once. With the desire to reduce database disk space the compress and DeDuplication can provide significant cost savings for storage.

CREATE TABLE t1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
LOB(a) STORE AS SECUREFILE
( DEDUPLICATE
CACHE)

CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE
( COMPRESS HIGH
DEDUPLICATE
CACHE ) ;

Oracle Secure Files utilizes Shared IO Pool. The shared IO pool is used from the SGA and allocations are always for a specific session and therefore the data is specific to the session. We can look at the Shared IO Pool via the v$sga_dynamic_componets and v$sgainfo V$ views. If we examine the memory structures of the SGA we can see what the Shared IO Pool max size is in relationship to the other memory structures of the database.

SQL> select name, bytes from v$sgainfo ;

NAME BYTES
-------------------------------- ----------
Fixed SGA Size 1334380
Redo Buffers 5844992
Buffer Cache Size 268435456
Shared Pool Size 239075328
Large Pool Size 4194304
Java Pool Size 12582912
Streams Pool Size 4194304
Shared IO Pool Size 0
Granule Size 4194304
Maximum SGA Size 535662592
Startup overhead in Shared Pool 46137344
Free SGA Memory Available 0

SQL> select * from v$sga_dynamic_components where component='Shared IO Pool' ;

COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER
---------- ---------- ------------------- ---------- ------------- ---------
LAST_OPER GRANULE_SIZE
--------- ------------
Shared IO Pool 0
0 0 0 0 STATIC
4194304

When a session is unable to find free memory in the Shared IO Pool, PGA memory would be used. To see PGA memory allocations you can use the V$SECUREFILE_TIMER view which gets an entry each time memory is allocated out of the PGA.

SQL> select * from v$securefile_timer where name like '%PGA%';

NAME LAYER_ID OWNTIME MAXTIME MINTIME INVOCATIONS LAYER_NAME
------------------------- ---------- ------- ------- ------- ----------- --------------------------------------------------
kdlw kcbi PGA alloc timer 2 0 0 0 0
Write gather cache
kdlw kcbi PGA free timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA borrow timer 2 0 0 0 0
Write gather cache
kdlw kcb PGA free timer 2 0 0 0 0
Write gather cache

Oracle 11g Results Cache

2008-09-11T11:42:00.001-04:00

Summary

Oracle 11g has many new features one of particular interest in the idea of a results cache. Results cache allows a query’s result to be cached allowing for large performance improvements for queries that are executed multiple times. The results cache works best for tables with mostly static data and has particular queries that return the same results over and over.

One particular point of interest for the result cache is that fact that the result cache does not work when logged in as sysdba.

We can see in the explain plan of a query when the RESULT CACHE is being used as shown below:

----------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
----------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 85120 1745K 660 (1) 00:00:08
1 RESULT CACHE 5g4sqrbmnk7tw4ct7s8ak9vdgd
2 SORT ORDER BY 85120 1745K 6696K 660 (1) 00:00:08
3 PARTITION REFERENCE ALL 85120 1745K 107 (1) 00:00:02
4 TABLE ACCESS FULL ORDER_ITEMS 85120 1745K 107 (1) 00:00:02
----------------------------------------------------------------------------------------------------------------

Test Results

SQL> set linesize 150
SQL> set timing on
SQL> set echo on
SQL>
SQL> drop table results_cache_test ;

Table dropped.

Elapsed: 00:00:00.10
SQL>
SQL> -- run create a table for our testing
SQL> -- so not to skew results
SQL> create table results_cache_test
2 as
3 select *
4 from dba_tables ;

Table created.

Elapsed: 00:00:00.56
SQL>
SQL> create unique index results_cache_test_pk
2 on results_cache_test (owner, table_name) ;

Index created.

Elapsed: 00:00:00.04
SQL>
SQL> execute dbms_stats.gather_table_stats ('MRMESSIN', 'RESULTS_CACHE_TEST') ;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95
SQL>
SQL> -- set autotrace on for first round with index
SQL> set autotrace on ;
SQL>
SQL> -- run to see the results
SQL> select table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;

TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO

TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378

----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
* 1 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='MRMESSIN')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
SQL> -- run a second time to cache the results
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;

TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO

TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES

14 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378

-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='MRMESSIN')

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN' "

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
SQL> -- run the third time to see query with results cache in place
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;

TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO

TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES

14 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378

-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='MRMESSIN')

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN' "

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> -- drop the index to start second round without index
SQL> drop index results_cache_test_pk ;

Index dropped.

Elapsed: 00:00:00.01
SQL>
SQL> set autotrace on ;
SQL>
SQL> -- run to see the results
SQL> select table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;

TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO

TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES

14 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378

----------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
* 1 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"='MRMESSIN')

Statistics
----------------------------------------------------------
234 recursive calls
0 db block gets
116 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
SQL> -- run a second time to cache the results
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;

TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO

TABLE_NAME CACHE REA
------------------------------ ----- ---
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES

14 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2738702378

-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='MRMESSIN')

Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN' "

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
SQL> -- run the third time to see query with results cache in place
SQL> select /*+ result_cache */ table_name, cache, read_only
2 from results_cache_test
3 where owner = 'MRMESSIN' ;

TABLE_NAME CACHE REA
------------------------------ ----- ---
DEPT N NO
EMP N NO
DEMO_USERS N NO
DEMO_CUSTOMERS N NO
DEMO_ORDERS N NO
DEMO_ORDER_ITEMS N NO
DEMO_PRODUCT_INFO N NO
DEMO_STATES N NO
DEMO_PAGE_HIERARCHY N NO
EAGLE N NO
HELP_TABLE N NO
BIG_EMP N NO
DEMO_IMAGES N NO
BIG_EMP2 N YES

14 rows selected.

Elapsed: 00:00:00.04

Execution Plan
--------------------------------------------------------
Plan hash value: 2738702378

-------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 95 3515 25 (0) 00:00:01
1 RESULT CACHE 6qrad2hzvx6xm6rwrx23ncuwwg
* 2 TABLE ACCESS FULL RESULTS_CACHE_TEST 95 3515 25 (0) 00:00:01
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------
2 - filter("OWNER"='MRMESSIN')

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=3; dependencies=(MRMESSIN.RESULTS_CACHE_TEST); parameters=(nls); name="select /*+ result_cache */ table_name, cache, read_only
from results_cache_test
where owner = 'MRMESSIN'"

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

SQL>
SQL> spool off

Oracle 11g Invisible Indexes

2008-09-11T11:38:00.000-04:00

Summary

Oracle has now provided in what is my opinion a huge step forward in query tuning. It will now allow us to hide indexes from the optimizer without have to drop them. If you have every tuned queries of very large tables and had to drop and recreate indexes in your query tuning testing then you will realize right away how useful this is.

Another huge advantage and use of this feature is for batch process tuning. Ever had a situation where you needed an index that would really help your batch process but hurt OLTP transaction query performance. Well now we can hide the index from OLTP, but allow our batch processes to see it.

To use an invisible index you can set the parameter optimizer_use_invisible_indexes to true. The default of this parameter is false and can be set via an alter session or alter system command. Otherwise invisible indexes are completely ignore by the optimizer including through hints.

A special thing to note about invisible indexes is that they can not get optimizer statistics gathered on them while in an invisible state. Therefore to maintain statistics that index will have to be made visible by altering the index or setting the optimizer_use_invisible_indexes to true.

Example

1. Create a table for use in our example:

SQL> create table my_table as select * from scott.emp ;

Table created.

2. Create an index on our table and make it invisible.

SQL> create index my_index on my_table (ename) invisible ;

Index created.

3. Check that the index is invisible using the new VISIBILITY column of the user_indexes, all_indexes or dba_indexes views.

SQL> select index_name, visibility from user_indexes where index_name = 'MY_INDEX';

INDEX_NAME VISIBILIT
------------------------------ ---------
MY_INDEX INVISIBLE

1 row selected.

4. Attempt to gather statistics on our new and cascading to indexes. Not since the index is invisible this fails.

SQL> execute dbms_stats.gather_table_stats ('MRMESSIN', 'MY_TABLE') ;
BEGIN dbms_stats.gather_table_stats ('MRMESSIN', 'MY_TABLE') ; END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

4. Make the invisible indexes visible.

SQL> alter session set optimizer_use_invisible_indexes = true ;

Session altered.

5. Now gather our statistics

SQL> execute dbms_stats.gather_table_stats ('MRMESSIN', 'MY_TABLE') ;

PL/SQL procedure successfully completed.

6. Set the optimizer_use_invisible_indexes back to false for the start of our testing.

SQL> alter session set optimizer_use_invisible_indexes = true ;

7. Set autotrace so we can see our explain plan and data.

SQL> set autotrace traceonly

8. Select from the table using the indexed column in our where clause which under normal circumstances that optimizer would choose to use the index. Note however we are not using the index because it is invisible.

SQL> select empno, ename from my_table where ename = 'BLAKE';

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3804444429

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 1 10 3 (0) 00:00:01
* 1 TABLE ACCESS FULL MY_TABLE 1 10 3 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='BLAKE')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
477 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

9. Now lets query seeing if we can use the index Hint to use the index. Not the index is not used.

SQL> select /*+ INDEX(my_table my_index) */ empno, ename from my_table where ename = 'BLAKE’ ;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3804444429

----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
----------------------------------------------------------------------------
0 SELECT STATEMENT 1 10 3 (0) 00:00:01
* 1 TABLE ACCESS FULL MY_TABLE 1 10 3 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ENAME"='BLAKE')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
477 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

10. Now let see if the index is used if we set the parameter optimizer_use_invisible_indexes to true.

SQL> alter session set optimizer_use_invisible_indexes = true ;

Session altered.

11. Note that index is now seen and is being used.

SQL> select empno, ename from my_table where ename = 'BLAKE';

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 332163487

-----------------------------------------------------------------------------
Id Operation Name RowsBytesCost %CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 1 10 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWIDMY_TABLE 1 10 2 (0) 00:00:01
*2 INDEX RANGE SCAN MY_INDEX 1 1 (0) 00:00:01
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ENAME"='BLAKE')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
477 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Oracle Stored Outlines

2008-06-10T16:03:00.000-04:00

Summary

Stored outlines, also known as plan stability, can be through o as a representation of a SQL statement execution plan that gives the ability to change a SQL Statements execution behavior without having to change the SQL statement in the application code. This allows the tuning of SQL statements for vendor systems where access to the code is usually restricted or inaccessible. It also provides a better way for tuning SQL allowing for better testing of upgrades. Oracle constantly improves the cost based optimizer with every release and when SQL statements are changed in the application code by adding hints undoing the tuning work can be difficult and time consuming. Stored Outlines can be removed to allow upgrades to be tested to see if Oracle makes better decisions in the newer version therefore allowing Oracle to make the decisions without the use of outlines. If the outlines are still needed then they can be put back in and/or tuned for the new Oracle version.

Requirements for Stored Outlines

Database Parameters
To utilized stored outlines certain database parameters
must be set in the init.ora or spfile.ora

query_rewrite_enabled
star_transformation_enabled
optimizer_features_enabled

System Level Parameter Setting
Also a system level parameter must be set to allow the
use of the stored outlines. This can not be set in the
init.ora or spfile.ora therefore a database startup trigger
is a great way to ensure this is set each time the database
is started.

ALTER SYSTEM SET USE_STORED_OUTLINES = true

CREATE OR REPLACE TRIGGER enable_outline
AFTER STARTUP ON DATABASE
BEGIN
execute immediate 'alter system set use_stored_outlines = true';
END ;

Implementing a Stored Outline
1. Create the stored outline for the SQL Statement the needs
improvement as shown via v$sql.

CREATE OR REPLACE OUTLINE SCHEDULING_QUERY on
select :"SYS_B_00" ,sit_code ,sit_division ,sh_serial ,sh_schstat ,sh_estlen,
TO_CHAR(sh_date,:"SYS_B_01")...
/

Or utilize a SQL statement already in the shared pool. Keep in mind this will generate the name for you. Therefore will need to find out what name the system gave it to manipulate later

-- Identify the SQL statement in the V$SQL view.
SELECT hash_value, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE 'select :"SYS_B_00" ,sit_code ,sit_division%';

HASH_VALUE CHILD_NUMBER
---------- ------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
1119283366 0
select :"SYS_B_00" ,sit_code ,sit_division ,sh_serial ,sh_schstat ,sh_estlen, TO_CHAR(sh_date,:"SYS_B_01")...

1 row selected.

-- Create an outline for the statement.
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 1119283366,
child_number => 0,
category => 'MY_OUTLINES');
END;
/

2. Develop a tuned SQL Statement

3. Create the stored outline for the tuned SQL statement

CREATE OR REPLACE OUTLINE SCHEDULING_QUERY_HINTED on
select /*+ leading(INTSTAMP) */
:"SYS_B_00" ,sit_code ,sit_division ,sh_serial ,sh_schstat ,sh_estlen ,
TO_CHAR(sh_date,:"SYS_B_01") ...;

4. Update OUTLN.OL$HINTS and put the plan for the tuned SQL
Statement into the untuned stored outline.

UPDATE OUTLN.OL$HINTS
SET OL_NAME = DECODE(OL_NAME, 'SCHEDULING_QUERY_HINTED', 'SCHEDULING_QUERY',
'SCHEDULING_QUERY', 'SCHEDULING_QUERY_HINTED'
WHERE OL_NAME IN ('SCHEDULING_QUERY','SCHEDULING_QUERY_HINTED');
commit;

5. Execute an explain plan for the original query to recognized
the stored outline. This will update the Outline to used if
Oracle sees the outline and is using it.

EXPLAIN PLAN FOR
select :"SYS_B_00" ,sit_code ,sit_division ,sh_serial
,sh_schstat ,sh_estlen ,TO_CHAR(sh_date,:"SYS_B_01")...

6. Check to see that the stored outline is now used.

select name, category, used from dba_outlines;

NAME CATEGORY USED
---------------- -------- ---------
SCHEDULING_QUERY DEFAULT USED

Oracle Flashback Drop

2008-05-20T17:35:00.000-04:00

Summary

Oracle 10g introduced a flashback drop feature that will allow you to bring back a dropped table without having to execute any type of recovery operation. This is due to the recyclebin which is what makes the flashback drop feature work. This feature is very useful for those times when a developer comes in who just dropped a table by mistake. Before flashback drop you either had to have an export of the table or recover the database using an auxiliary instance to get the table back and then move the table from the auxiliary instance back into database where the table was dropped. You can however drop a table and bypass the recycle bin as well by using the PURGE option to the drop table.

Some important things to note about the flashback drop feature

1. Think of each schema getting its own Recycle Bin
2. The recycle bin is a logical collection of previously DROPPED objects
3. In Oracle 10g the default action of a DROP TABLE command is to RENAME the table essentially placing the table in the RECYCLE BIN.
4. Objects in the RECYCLE BIN are NOT automatically purged and must be explicitly purged explicitly by a User or DBA action.
5. When a table is dropped the segments that table consumed in the tablespace are still present. Therefore when a table is dropped space is not freed as the table segments are still there.
6. If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space.
7. Where multiple versions are present, it's best to reference the tables via the table name shown in the RECYCLEBIN -> RECYCLEBIN_NAME.
8. For any references to the ORIGINAL_NAME, it is assumed the most recent object is drop version in the referenced question.

Showing the contents of the RECYCLEBIN

Show the current schemas recyclebin
show recyclebin
select * from recyclebin ;

Show the DBA Recyclebin which will show all schema recyclebins
select * from dba_recyclebin ;

Purging the RECYCLEBIN

Purge current schema RECYCLEBIN
PURGE RECYCLEBIN ;

Purge All RECYCLEBINs
PURGE DBA_RECYCLEBIN ;

Purge Objects from RECYCLEBIN
PURGE TABLE tablename ;
PURGE INDEX indexname ;

Purge Objects from RECYCLEBIN for a tablespace
PURGE TABLESPACE ts_name ;
PURGE TABLESPACE ts_name USER username ;

Dropping the table and bypass the RECYCLEBIN

DROP TABLE tablename PURGE ;

Recovering a Table from the RECYCLEBIN

Recover last dropped version of the table
FLASHBACK TABLE tablename TO BEFORE DROP;

Recover the table, but give it a new name
FLASHBACK TABLE tablename TO BEFORE DROP RENAME TO newtablename ;

Recover the table from a prior dropped version of the table
FLASHBACK TABLE “recyclebin name” TO BEFORE DROP ;
FLASHBACK TABLE “recyclebin name” TO BEFORE DROP RENAME TO newtablename ;

Examples Using Flashback Drop

1. Create a table for our testing
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.

2. Insert a record so that we have data
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

3. Drop the table
SQL> DROP TABLE flashback_drop_test;

Table dropped.

4. Show the RECYCLEBIN
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$qLfTuNLERa6PzKkR4l1luA==$0 TABLE 2008-05-20:16:03:36

5. Show that we can no longer select from the table.
SQL> select * from flashback_drop_test;
select * from flashback_drop_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

6. Bring back the last dropped version of the table
SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

Flashback complete.

7. Show that the table is back and we can select from it
SQL> select * from flashback_drop_test;

ID
----------
1

8. Show that the RECYCLEBIN is now empty
SQL> show recyclebin
SQL>

9. Drop the table again using the purge option.
SQL> drop table flashback_drop_test PURGE ;

Table dropped.

10. Show that the recyclebin is empty as we used the PURGE option.
SQL> show recyclebin
SQL>

11. Recreate the table for our further testing.
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.

12. Drop the table again using the purge option.
SQL> drop table flashback_drop_test ;

Table dropped.

13. recreate the table to have multiple versions of the table.
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.

14. Insert a record so that we have data so we will have 2 versions of the table one with a record and one empty.
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> COMMIT;

15. Show that we have the table in the RECYCLEBIN again.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$4/+GRpvFSl6cIWL21YkgPA==$0 TABLE 2008-05-20:16:03:39

16. Show that the we can select directly from the table in the RECYCLEBIN using the RECYCLEBIN name and show that the table in the RECYCLEBIN has no rows.
SQL> select * from "BIN$XzZkNWkNQBiwl9WSVWk49w==$0" ;

no rows selected

17. Show that the current table still exists and has rows
SQL> select * from flashback_drop_test ;

ID
----------
1

18. Drop the table
SQL> drop table flashback_drop_test ;

Table dropped.

19. Show that we now have 2 versions of the table in the recyclebin.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$3FoAb84NSMmiVnhYco2e5w==$0 TABLE 2008-05-20:16:50:25
FLASHBACK_DROP_TEST BIN$XzZkNWkNQBiwl9WSVWk49w==$0 TABLE 2008-05-20:16:46:32

20. Bring back the latest version of the table but with another table name.
SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Flashback complete.

21. Show that we can select from the new table name.
SQL> select * from flashback_drop_test_old;

ID
----------
1

22. Show that we still do not see the original table.
SQL> select * from flashback_drop_test;
select * from flashback_drop_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

23. Show that we still have a version of the table in the RECYCLEBIN
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$XzZkNWkNQBiwl9WSVWk49w==$0 TABLE 2008-05-20:16:46:32

24. Create a new version of the table.
SQL> CREATE TABLE flashback_drop_test ( id NUMBER(10) );

Table created.

25. Insert a row so this version has a record.
SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> COMMIT;

26. Drop the table
SQL> drop table flashback_drop_test ;

Table dropped.

27. Show that the 2 versions are in the RECYCLEBIN
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_TEST BIN$MMV1E9cRRZCvUkC8HM3WhQ==$0 TABLE 2008-05-20:16:58:45
FLASHBACK_DROP_TEST BIN$XzZkNWkNQBiwl9WSVWk49w==$0 TABLE 2008-05-20:16:46:32

28. Restore the older version of the table from the RECYCLEBIN, this one does not have a record in it.
SQL> FLASHBACK TABLE "BIN$XzZkNWkNQBiwl9WSVWk49w==$0" TO BEFORE DROP ;

Flashback complete.

29. Show that the table is the correct version as it will have no record in it.
SQL> select * from flashback_drop_test ;

no rows selected

Oracle Transparent Data Encryption

2008-05-08T19:57:00.000-04:00

Summary

Oracle has provided a way to automatically encrypt data in a column of a table and at the same time decrypt it automatically therefore allowing an application to function as if the column was not encrypted. Transparent data encryption will protect the data in a column in a table with the use of a wallet. Transparent data encryption by default uses the algorithm AES with 192-bit key for data encryption. The following is a short test showing the use of Transparent Data Encryption.

There is overhead to transparent data encryption that can not be ignored as the encryption during inserts and updates, and decryption operations during select operations consume CPU cycles. While this overhead is fairly low it is safer to selectively apply encryption to where it is truly needed on a column basis. Therefore proper load tests should be performed incrementally as column encryption is rolled out so that full impact to system can be measured.

There are three significant things to mention about encryption one being that streams does not support the use of encrypted columns the other two have to do with the use of indexes. One you can not index columns that are encrypted with salt therefore columns encrypted that require an index as the column will be used in searches will need to be encrypted without salt. By default transparent data encryption is done with salt and to encrypt a column without salt the “no salt” option must be specified. Second indexes for encrypted columns behave different in a single respect but can have a large impact. Take table emp for example, add column ssn and encrypt it and build an index on it. We will use 2 query examples:

select ename from emp where ssn = ‘123-45-6789’ ;

This query will use the index and will not matter if the column is encrypted or not.

select ename from emp where ssn like ‘123-45-%’ ;

This query however will depend on if the column is encrypted or not to whether the index is used. If the column is encrypted the index will be ignored, however if the column is not encrypted the index would indeed be used. The reason the index is not used it due to the index now the column has an encrypted value and the ability to substring for an encrypted value is not possible. We will show that the index is indeed not used in the test below.

10g (10.2.0.3 Windows) and 11g (11.1.0.6) Test

1. Set the Wallet location for encryption in the sqlnet.ora file.

10g

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=c:\oracle\product\10.2.0\db_1\network\admin\ENCRYPT)))

11g

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=c:\oracle\product\10.2.0\db_1\network\admin\ENCRYPT)))

2. Create the wallet, initial creation of the wallet will automatically open it.
alter system set encryption key authenticated by "xxxxxxxxx" ;

SQL> alter system set encryption key authenticated by "xxxxxxxxx" ;
System altered.

3. Each time you need to use the wallet it will need to be explicitly opened. The wallet will allow the automatic decryption of data as it is read, otherwise the data of encrypted columns will remain encrypted and not able to be viewed. All other columns that are not encrypted work as normal even when the wallet is not open. In this case since it was just created the wallet is already open.

alter system set encryption wallet open authenticated by "kim11ball" ;

SQL> alter system set encryption wallet open authenticated by "xxxxxxxxx" ;
alter system set encryption wallet open authenticated by "xxxxxxxxx"
*
ERROR at line 1:
ORA-28354: wallet already open

Note: Can close the wallet explicitly with the following alter system set encryption wallet close ;

SQL> alter system set encryption wallet close ;

System altered.

4. Lets open the wallet so we can proceed with the test

SQL> alter system set encryption wallet open authenticated by "xxxxxxxxx" ;

System altered.

5. To test our transparent data encryption by using the emp table, first add an ssn column.

SQL> alter table emp add ssn varchar2(11) ;

Table altered.

SQL> update emp set ssn = ‘222-22-2222’ ;

51 rows updated.

SQL> commit ;

6. Encrypt the ssn column

SQL> alter table emp modify (ssn encrypt) ;

Table altered.

Note: Can turn the column encryption off with the following

SQL> alter table emp modify (ssn decrypt) ;

Table altered.

7. Check the emp table structure as we can see the SSN column show encrypt.

SQL> desc emp
Name Null? Type
----------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SSN VARCHAR2(11) ENCRYPT

8. Let verify the wallet is indeed closed.

SQL> alter system set encryption wallet close;

System altered.

SQL> alter system set encryption wallet open authenticated by "nsfjsnfjksfa" ;
alter system set encryption wallet open authenticated by "nsfjsnfjksfa"
*
ERROR at line 1:
ORA-28353: failed to open wallet

SQL> alter system set encryption wallet close;
alter system set encryption wallet close
*
ERROR at line 1:
ORA-28365: wallet is not open

9. As we can see we are going to do the query with a user called temp the only permissions on this user is select on the emp table and create session.

SQL> select user from dual ;

USER
------------------------------
TEMP

10. If we look at the SSN column we see that the column queries unencrypted, 10g is restricting the access to the column with the wallet closed as expected

SQL> select ssn from emp ;
select ssn from emp
*
ERROR at line 1:
ORA-28365: wallet is not open

11. Not open the wallet for some more testing

SQL> alter system set encryption wallet open authenticated by "xxxxxxxxx" ;

System altered.

12. See if an index can be built, as the summary indicates an index can not be built on an encrypted column with salt.

SQL> create index emp_ssn on emp (ssn) ;
create index emp_ssn on emp (ssn)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt

13. Turn off the salt using the alter table statement so that the index can be built.

SQL> alter table emp modify ssn encrypt no salt ;

Table altered.

14. Now build the index

SQL> create index emp_ssn on emp (ssn) ;

Index created.

15. Now check the affect the encryption has on the index usage.

A: Check using the column in the where clause with an =

SQL> explain plan for select ename from emp where ssn = '123-45-6789' ;

Explained.

SQL> @plan

Plan hash value: 1582005447

---------------------------------------------------------------------------
Id Operation Name RowsBytesCost(%CPU)Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 1 38 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWIDEMP 1 38 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_SSN 1 1 (0) 00:00:01
---------------------------------------------------------------------------

B: Check the index use with the LIKE, as we can see by the explain plan the index is suppressed.

SQL> explain plan for select ename from emp where ssn like '123-45-%' ;

Explained.

SQL> @plan

Plan hash value: 3956160932

---------------------------------------------------------------
Id Operation NameRowsBytesCost(%CPU)Time
---------------------------------------------------------------
0 SELECT STATEMENT 2 76 3 (0)00:00:01
* 1 TABLE ACCESS FULL EMP 2 76 3 (0)00:00:01
---------------------------------------------------------------

16. No remove the encryption from the ssn column and see if the like uses the index as expected and indeed the index is used.

SQL> alter table emp modify (ssn decrypt) ;

Table altered.

SQL> explain plan for select ename from emp where ssn like '123-45-%' ;

Explained.

SQL> @plan

Plan hash value: 1582005447

---------------------------------------------------------------------------
Id Operation Name RowsBytesCost(%CPU) Time
---------------------------------------------------------------------------
0 SELECT STATEMENT 2 28 2 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWIDEMP 2 28 2 (0) 00:00:01
* 2 INDEX RANGE SCAN EMP_SSN 1 1 (0) 00:00:01
---------------------------------------------------------------------------

Oracle 11g Read-Only Tables

2008-05-08T15:23:00.000-04:00

Summary

Prior to 11g you could make an entire tablespace read-only which meant that you had to group all your tables that you wanted to be read-only into a common tablespace or set of tablespaces, or you had to create a trigger that would cause an error. 11G now allows a single table to be made read-only without affecting the other tables in the tablespace. This can help save the overhead of having to move tables to read-only tablespaces when you are read to make the table read-only. Not only can we place the table read-only but can change it back to read-write to allow insert and updates when needed.

The read-only designation for a table will stop all DML (truncate/insert/update/delete/etc) operations and certain DDL operations as well like ALTER TABLE to add/modify/rename/drop columns, ALTER TABLE drop/truncate/exchange (SUB)PARTITION, ALTER TABLE upgrade. Though keep in mind that some DDL can still be done against a table designated read-only like DROP TABLE, ALTER TABLE add/coalesce/merge/modify/move/rename/split (SUB)PARTITION, ALTER TABLE rename/move/add supplemental log/drop supplemental log/deallocate ununsed.
To identify if a table is read-only a new column has been added to dba_tables, user_tables and all_tables called READ_ONLY. This column will contain YES if the table is read-only and NO when the table is not read-only.

Unfortunately at this time Oracle does not allow the ability to just take a partition of a table and make it read-only, to make a partition of a table read-only the partition must be moved to a tablespace that can be marked read-only. One can only hope Oracle will help us out and provide that ability in future releases saving the overhead of move partitions or having to place partitions in separate tablespaces to get the partitions to be read-only.

Putting table into read-only:

alter table big_emp2 read only ;

Taking table out of read-only

alter table big_emp2 read write ;

11g (11.1.0.6 Windows) Test

Create the table for the test and load with dummy data.

SQL> -- create the table for the test
SQL> CREATE TABLE big_emp2
2 (EMPNO NUMBER(4) CONSTRAINT big_emp2_pk primary key disable,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2)
10 )
11 PARTITION BY RANGE (HIREDATE)
12 (PARTITION y1980 VALUES LESS THAN ( TO_DATE('01-JAN-1981','DD-MON-YYYY') ) TABLESPACE USERS,
13 PARTITION y1981 VALUES LESS THAN ( TO_DATE('01-JAN-1982','DD-MON-YYYY') ) TABLESPACE USERS,
14 PARTITION y1982 VALUES LESS THAN ( TO_DATE('01-JAN-1983','DD-MON-YYYY') ) TABLESPACE USERS,
15 PARTITION y1983 VALUES LESS THAN ( TO_DATE('01-JAN-1984','DD-MON-YYYY') ) TABLESPACE USERS,
16 PARTITION y1984 VALUES LESS THAN ( TO_DATE('01-JAN-1985','DD-MON-YYYY') ) TABLESPACE USERS,
17 PARTITION y1985 VALUES LESS THAN ( TO_DATE('01-JAN-1986','DD-MON-YYYY') ) TABLESPACE USERS,
18 PARTITION y1986 VALUES LESS THAN ( TO_DATE('01-JAN-1987','DD-MON-YYYY') ) TABLESPACE USERS,
19 PARTITION y1987 VALUES LESS THAN ( MAXVALUE ) TABLESPACE USERS
20 )
21 /

Table created.

SQL>
SQL> -- create the unique index for the primary key
SQL> create unique index beig_emp2_pk on big_emp2 (empno) ;

Index created.

SQL>
SQL> -- enable the primary key using the unique index
SQL> alter table big_emp2 enable primary key using index ;

Table altered.

SQL> -- load data
SQL> insert into big_emp2 select * from big_emp ;

9254 rows created.

Make the Table Read Only

SQL> alter table big_emp2 read only ;

Table altered.

Check that the table shows read-only

SQL> select table_name, read_only from user_tables where table_name = 'BIG_EMP2';

TABLE_NAME REA
------------------------------ ---
BIG_EMP2 YES

Attempt to do DML operations against the table

SQL> update big_emp2 set salary = salary + 200 ;
update big_emp2 set salary = salary + 200
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> insert into big_emp2 values (0,'My Name','MGR','1000','01-JAN-08',5000,1000,10) ;
insert into big_emp2 values (0,'My Name','MGR','1000','01-JAN-08',5000,1000,10)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> delete from big_emp2 ;
delete from big_emp2
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> truncate table big_emp2 ;
truncate table big_emp2
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

Attempt to do DDL operations against the table

SQL> alter table big_emp2 add ssn varchar2(11) ;
alter table big_emp2 add ssn varchar2(11)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

SQL> ALTER TABLE big_emp2 drop partition y1980 ;
ALTER TABLE big_emp2 drop partition y1980
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "MRMESSIN"."BIG_EMP2"

Moving Cost Based Optimizer Statistics

2008-05-05T20:41:00.000-04:00

We have found that over time test databases do not match production databases is data or even in data volume unless we refresh them frequently. The problem is getting the development and test staff to allow the downtime necessary to do a database refresh from production especially for larger databases. So how do we make sure out execution plans in our test environment match what the production execution plans would be. The answer is to take the cost based optimizer statistics and put them in the production database.

1. To move the cost based optimizer statistics we first create a stats table and put the CBO stats in that stats table. In our example we will use the SYSTEM schema for the stats table.

----------------------------------------------------------------------
-- Setup our stats table and export our stats for the passed schema
-- into the stats table
----------------------------------------------------------------------
DECLARE
v_tbl_cnt NUMBER ;

BEGIN
-- enable DBMS_OUTPUT
dbms_output.enable (1000000) ;

-- Check if our stats table exists
SELECT count(*)
INTO v_tbl_cnt
FROM dba_tables
WHERE owner = 'SYSTEM'
AND table_name = 'CBO_STATS' ;

-- If stats table exists then we must get rid of it to ensure
-- that we always recreate it to ensure structure is correct
IF v_tbl_cnt > 0 THEN
dbms_output.put_line('Having to Remove SYSTEM.CBO_STATS as it already Exists.');

-- Remove our stats table now that we have exported it
DBMS_STATS.drop_stat_table('SYSTEM','CBO_STATS') ;
END IF ;

-- Create our stats table
dbms_output.put_line ('Creating SYSTEM.CBO_STATS Table to Store CBO Stats for Schema &1.') ;
dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'CBO_STATS', tblspace => 'USERS') ;

-- put our schema stats into out created stats table
dbms_output.put_line ('Exporting Schema Stats for &1 into SYSTEM.CBO_STATS table.') ;
dbms_stats.export_schema_stats (ownname => '&1', stattab => 'CBO_STATS', statown => 'SYSTEM') ;
END ;
/

2. Next we will need to export the stats table we just put the cost based optimizer statistics into

----------------------------------------------------------------------
-- export our stats table so it can be imported to another database
-- We will use the DBMS_DATAPUMP Package to do our export to simplify
-- the process
----------------------------------------------------------------------
DECLARE
v_handle NUMBER ;
ind NUMBER ; -- Loop index
spos NUMBER ; -- String starting position
slen NUMBER ; -- String length for output
percent_done NUMBER ; -- Percentage of job complete
job_state VARCHAR2(30) ; -- To keep track of job state
le ku$_LogEntry ; -- For WIP and error messages
js ku$_JobStatus ; -- The job status from get_status
jd ku$_JobDesc ; -- The job description from get_status
sts ku$_Status ; -- The status object returned by get_status

BEGIN
dbms_output.enable (1000000) ;

-- create our datapump export job
dbms_output.put_line ('Opening Export Job') ;
v_handle := DBMS_DATAPUMP.OPEN (operation => 'EXPORT',
job_mode => 'TABLE',
job_name => 'EXPORT_CBO_STATS_JOB',
version => 'COMPATIBLE');

-- set our logfile
dbms_output.put_line ('Setting Log file for Export Job') ;
DBMS_DATAPUMP.ADD_FILE (handle => v_handle,
filename => 'expdp_export_cbo_stats.log',
directory => 'DMPDIR',
filetype => 3) ;

-- set our dump file
dbms_output.put_line ('Setting Dump file for Export Job') ;
DBMS_DATAPUMP.ADD_FILE (handle => v_handle,
filename => 'expdp_export_cbo_stats.dmp',
directory => 'DMPDIR',
filetype => 1) ;

-- Add the schema filter
DBMS_DATAPUMP.METADATA_FILTER(v_handle,'SCHEMA_EXPR','IN (''SYSTEM'')');

-- set the filter for datapump to be the schema we want to export.
dbms_output.put_line ('Adding filter to only get SYSTEM.CBO_STATS') ;
DBMS_DATAPUMP.METADATA_FILTER (handle => v_handle,
name => 'NAME_EXPR',
value => '= ''CBO_STATS''',
object_type => 'TABLE') ;

-- Start the datapump export job
dbms_output.put_line ('Starting Datapump Export Job to dump SYSTEM.CBO_STATS table.') ;
DBMS_DATAPUMP.START_JOB (v_handle) ;

-- May have to use WAIT_FOR_JOB

-- initialize percent_done and job_state values
percent_done := 0 ;
job_state := 'UNDEFINED' ;

-- Loop while the job_state is not completed or stopped
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(v_handle,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts) ;

js := sts.job_status ;

-- If the percentage done changed, display the new value.
if js.percent_done != percent_done then
dbms_output.put_line('*** Job percent done = ' to_char(js.percent_done)) ;

percent_done := js.percent_done ;
end if ;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) then
le := sts.wip ;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) then
le := sts.error ;
else
le := null ;
end if ;
end if ;

if le is not null then
ind := le.FIRST ;

-- loop while loop indicator is not null
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;

-- Indicate that the job finished and gracefully detach from it.
dbms_output.put_line('Data Pump Import Job has completed') ;
dbms_output.put_line('Final job state = ' job_state) ;
dbms_datapump.detach(v_handle) ;

-- Handle exceptions from our export
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception in Data Pump job') ;
dbms_datapump.get_status(v_handle,dbms_datapump.ku$_status_job_error, 0, job_state, sts) ;

IF (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
le := sts.error;

if le is not null then
ind := le.FIRST ;

while ind is not null loop
spos := 1;
slen := length(le(ind).LogText) ;

if slen > 255 then
slen := 255;
end if;

while slen > 0 loop
dbms_output.put_line(substr(le(ind).LogText, spos, slen));
spos := spos + 255;
slen := length(le(ind).LogText) + 1 - spos;
end loop;

ind := le.NEXT(ind);
end loop;
end if;
END IF ;
END ;
/

3. If we use a database link (TARGET) to the database we are moving the stats to and it is on a different host then we can transfer the file across the database link with a DMPDIR defined on the other side. Otherwise we can copy the file through OS FTP, SCP or copy commands.

----------------------------------------------------------------------
-- Transfer the file via DBMS_FILE_TRANSFER over network link
-- Can change process to just do import over network link
----------------------------------------------------------------------
EXEC DBMS_FILE_TRANSFER.PUT_FILE ('DMPDIR', 'expdp_export_cbo_stats.dmp', 'DMPDIR', 'expdp_export_cbo_stats.dmp', 'TARGET') ;

4. Now the stats table can be removed now that we have exported the table and transferred the dump file to the destination.

----------------------------------------------------------------------
-- Clean up by Removing our stats table now that we have exported it
----------------------------------------------------------------------
EXEC DBMS_STATS.drop_stat_table('SYSTEM','CBO_STATS') ;

5. Connect to the destination database so that we can import the stats table.

-- Our example here the 2 databases are on the same host so our directory is still ok
-- Import our stats table into the other database for stats import use
DECLARE
ind NUMBER ; -- Loop index
spos NUMBER ; -- String starting position
slen NUMBER ; -- String length for output
v_handle NUMBER ; -- Data Pump job handle
percent_done NUMBER ; -- Percentage of job complete
job_state VARCHAR2(30) ; -- To keep track of job state
le ku$_LogEntry ; -- For WIP and error messages
js ku$_JobStatus ; -- The job status from get_status
jd ku$_JobDesc ; -- The job description from get_status
sts ku$_Status ; -- The status object returned by get_status

BEGIN
dbms_output.enable (1000000) ;

-- Create a Data Pump job to do a "full" import as only our stats table is there
-- create our datapump export job
v_handle := DBMS_DATAPUMP.OPEN (operation => 'IMPORT',
job_mode => 'FULL',
job_name => 'IMPORT_CBO_STATS_JOB',
version => 'COMPATIBLE');

-- set our logfile for import
DBMS_DATAPUMP.ADD_FILE (handle => v_handle,
filename => 'expdp_import_cbo_stats.log',
directory => 'DMPDIR',
filetype => 3) ;

-- set our dump file
DBMS_DATAPUMP.ADD_FILE (handle => v_handle,
filename => 'expdp_export_cbo_stats.dmp',
directory => 'DMPDIR',
filetype => 1) ;

-- If a table already exists in the destination schema, replace it before loading
-- to ensure the structure is proper as the data prior to is not important here.
DBMS_DATAPUMP.SET_PARAMETER(v_handle,'TABLE_EXISTS_ACTION','REPLACE');

-- Start the job. An exception is returned if something is not set up properly.
DBMS_DATAPUMP.START_JOB(v_handle);

-- The import job should now be running. In the following loop, the job is
-- monitored until it completes. In the meantime, progress information is
-- displayed. Note: this is identical to the export example.

-- initialize percent_done and job_state values
percent_done := 0 ;
job_state := 'UNDEFINED' ;

-- Loop while the job_state is not completed or stopped
while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
dbms_datapump.get_status(v_handle,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts) ;

js := sts.job_status ;

-- If the percentage done changed, display the new value.
if js.percent_done != percent_done then
dbms_output.put_line('*** Job percent done = ' to_char(js.percent_done)) ;

percent_done := js.percent_done ;
end if ;

-- If any work-in-progress (WIP) or Error messages were received for the job,
-- display them.
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0) then
le := sts.wip ;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) then
le := sts.error ;
else
le := null ;
end if ;
end if ;

if le is not null then
ind := le.FIRST ;

-- loop while loop indicator is not null
while ind is not null loop
dbms_output.put_line(le(ind).LogText);
ind := le.NEXT(ind);
end loop;
end if;
end loop;

-- Indicate that the job finished and gracefully detach from it.
dbms_output.put_line('Data Pump Import Job has completed') ;
dbms_output.put_line('Final job state = ' job_state) ;
dbms_datapump.detach(v_handle) ;

-- Handle exceptions from our import
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception in Data Pump job') ;
dbms_datapump.get_status(v_handle,dbms_datapump.ku$_status_job_error, 0, job_state, sts) ;

IF (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0) THEN
le := sts.error;

if le is not null then
ind := le.FIRST ;

while ind is not null loop
spos := 1;
slen := length(le(ind).LogText);

if slen > 255 then
slen := 255;
end if;

while slen > 0 loop
dbms_output.put_line(substr(le(ind).LogText, spos, slen));
spos := spos + 255;
slen := length(le(ind).LogText) + 1 - spos;
end loop;

ind := le.NEXT(ind);
end loop;
end if;
END IF ;
END ;
/

6. If bringing the stats table from a prior version of Oracle into a newer version of Oracle you will need to upgrade the stats table.

------------------------------------------------------------------
-- If moving stats from a previous version of database need to upgrade stats table
------------------------------------------------------------------
exec dbms_stats.upgrade_stat_table ('SYSTEM', 'CBO_STATS') ;

7. Lastly import the statistics into the schema.

------------------------------------------------------------------
-- Import the schema stats exported from the other database
------------------------------------------------------------------
EXEC DBMS_STATS.import_schema_stats('&1','CBO_STATS',NULL,'SYSTEM');

Rename Target in OEM Grid Control Without Removing Target

2008-03-31T15:46:00.000-04:00

1. Go to the target machine when you can change the target name

2. Go to agent ORACLE_HOME/sysman/emd directory

3. Find the targets.xml file

4. make a backup of copy of the targets.xml to something like targets.xml.bak

Edit the file and search for the target name you want to change and then change the name to the new name you want the target to have.

6. Go to agent ORACLE_HOME/bin directory and run the following

emctl stop agent

emctl clearstate agent

emctl start agent

emctl reload agent

emctl upload agent

After a few minutes go into the OEM Grid Control GUI and you should see the new target name

Move OEM Grid Agent to another OMS

2008-03-31T15:41:00.000-04:00

1. Stop the agent: emctl stop agent

2. edit the emd.properties file REPOSITORY_URL parameter to reflect the new OMS information

3. Clean up the agent files

rm -r $ORACLE_HOME/sysman/emd/state/*
rm -r $ORACLE_HOME/sysman/emd/collection/*
rm -r $ORACLE_HOME/sysman/emd/upload/*
rm $ORACLE_HOME/sysman/emd/lastupld.xml
rm $ORACLE_HOME/sysman/emd/agntstmp.txt
rm $ORACLE_HOME/sysman/emd/blackouts.xml
rm $ORACLE_HOME/sysman/emd/protocol.ini

4. emctl clearstate

5. emctl start agent

6. emctl upload

7. emctl status agent

Rebuild targets.xml for Oracle Enterprise Manager Grid Control Agent

2008-03-31T15:34:00.000-04:00

1. Agents installations before 10.2.0.3 have a bug in the 'agentca' command that prevents the targets.xml file from getting populated with the initial parameters (AGENT_TOKEN, ORACLE_EMD, and/or HOST) when the file does not already exist. To work around this we will need to manually create a targets.xml file if it does not exist.

Windows
create the new file in the Agent %ORACLE_HOME%\sysman\emd\ directory. (Where %ORACLE_HOME% is the agent’s home)
Unix$

$ cd $ORACLE_HOME/sysman/emd (where $ORACLE_HOME is the Agent's home)
$ touch targets.xml.

2. Once you have created the targets.xml file, view the Agent's $ORACLE_HOME/sysman/config/emd.properties file. Copy the values for:
agentSeed=12345EMD_URL=http://hostname.oracle.com:9876/emd/main

3. Manually update the new targets.xml and add the following entries to the file following the syntax exactly:

For example:

4. Force target re-discovery:
a. Verify that the environment variables are setup for the Management Agent:
$ORACLE_HOME set$ORACLE_HOME/bin in $PATH

b. The agentca utility is expecting the oraInst.loc to be located in the root of the Agent's $ORACLE_HOME

NOTE: Solaris puts the oraInst.loc in /var/opt/oracle. Agentca will not fine the oraInst.loc on a Solaris platform. For Solaris platforms you need to create a soft link:
$ORACLE_HOME/oraInst.loc to /var/opt/oracle/oraInst.loc
c. Run agentca -d to force a new discovery of the targets on the host

agentca -d

Oracle 11g Interval Partitioning

2008-02-21T20:07:00.000-05:00

New in 11g is Interval Partitioning. This extends the functionality of range partitioning to where you define equal partitions using and interval definition. When using Interval partitioning Oracle will automatically create new partitions as they are needed. Oracle creates the new partition at the time of the first record insert for the new partition. This greatly helps the manageability of partitioned tables by saving the DBA from having to manually create new partitions.

The new interval partitioning valid combinations are Interval, Interval-List, Interval-Hash and Interval-Range.

This will work well for range partitioning where partitioning was done based on date ranges.

Original Range Partitioning Example:

CREATE TABLE mrmessin.emp
(
EMPNO NUMBER(4),
ENAME VARCHAR2(30),
JOB VARCHAR2(20),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE (hiredate)
( PARTITION part_1999 values LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
PARTITION part_2000 values LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
PARTITION part_2001 values LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
PARTITION part_2002 values LESS THAN (TO_DATE('01-JAN-2003','DD-MON-YYYY'))
PARTITION part_2003 values LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY'))
) ;

The new interval partitioning can simplify the creation of the table and then partitions created automatically as data is added that would go into the new partitions.

New Interval Ranger Partitioning Example:

CREATE TABLE mrmessin.emp
(
EMPNO NUMBER(4),
ENAME VARCHAR2(30),
JOB VARCHAR2(20),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
PARTITION BY RANGE (hiredate)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
( PARTITION part_1999 values LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
) ;

Now lets go through an example to see if the partitions are created as the data is inserted. This will allow us to see how the partitions are created and how oracle names them. We will also see if we can rename the new partitions to better names after the fact.

SQL> -- drop the table if it already exists
SQL> drop table mrmessin.emp ;

Table dropped.

SQL>
SQL> -- create a interval partition table.
SQL> -- we will use the definition of the scott.emp table
SQL> CREATE TABLE mrmessin.emp
2 (
3 EMPNO NUMBER(4),
4 ENAME VARCHAR2(30),
5 JOB VARCHAR2(20),
6 MGR NUMBER(4),
7 HIREDATE DATE,
8 SAL NUMBER(7,2),
9 COMM NUMBER(7,2),
10 DEPTNO NUMBER(2)
11 )
12 PARTITION BY RANGE (hiredate)
13 INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
14 (
15 PARTITION part_1999 values LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
16 ) ;

Table created.

SQL>
SQL>
SQL> -- add a primary key to the partitioned table
SQL> alter table mrmessin.emp add constraint emp_empno_pk primary key (empno) disable ;

Table altered.

SQL>
SQL> -- create the unique index for our primary key
SQL> create unique index mrmessin.emp_empno_pk on mrmessin.emp (empno) tablespace users ;

Index created.

SQL>
SQL> -- enable our primary key
SQL> alter table mrmessin.emp enable primary key using index ;

Table altered.

SQL>
SQL> -- gather stats
SQL> EXEC DBMS_STATS.gather_table_stats('MRMESSIN','EMP') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- take a look at the partitions of the table
SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MRMESSIN EMP
PART_1999 USERS 0

SQL> -- let insert some data for a partition.
SQL> insert into mrmessin.emp values (1, 'Mike Messina', 'CEO', 1, to_date('15-NOV-1999', 'DD-MON-YYYY'), 10000, 1000, 1) ;

1 row created.

SQL> insert into mrmessin.emp values (2, 'Joe Messina', 'COO', 1, to_date('10-JAN-2000', 'DD-MON-YYYY'), 10000, 1000, 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL> -- gather stats again
SQL> EXEC DBMS_STATS.gather_table_stats('MRMESSIN','EMP') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- take a look at the partitions of the table
SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MRMESSIN EMP
SYS_P46 USERS 1

MRMESSIN EMP
PART_1999 USERS 1

SQL>
SQL> -- insert some more data that should cause more partitions to be created
SQL> insert into mrmessin.emp values (3, 'Frank Messina', 'CIO', 1, to_date('12-DEC-2001', 'DD-MON-YYYY'), 10000, 1000, 1) ;

1 row created.

SQL> insert into mrmessin.emp values (4, 'Joe Joe Shamo', 'Bouncer', 1, to_date('14-FEB-2002', 'DD-MON-YYYY'), 1000, 100, 1) ;

1 row created.

SQL> insert into mrmessin.emp values (5, 'Mixer Man', 'Bar Tender', 1, to_date('04-APR-2003', 'DD-MON-YYYY'), 1000, 100, 1) ;

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL> -- gather stats again
SQL> EXEC DBMS_STATS.gather_table_stats('MRMESSIN','EMP') ;

PL/SQL procedure successfully completed.

SQL>
SQL> -- take a look at the partitions of the table
SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
MRMESSIN EMP
SYS_P46 USERS 1

MRMESSIN EMP
SYS_P47 USERS 1

MRMESSIN EMP
SYS_P48 USERS 1

MRMESSIN EMP
SYS_P49 USERS 1

MRMESSIN EMP
PART_1999 USERS 1

SQL> alter table mrmessin.emp rename partition sys_p46 to part_2000 ;

Table altered.

SQL> alter table mrmessin.emp rename partition sys_p47 to part_2001 ;

Table altered.

SQL> alter table mrmessin.emp rename partition sys_p48 to part_2002 ;

Table altered.

SQL> alter table mrmessin.emp rename partition sys_p49 to part_2003 ;

Table altered.

SQL> select table_owner, table_name, partition_name, tablespace_name, num_rows
2 from dba_tab_partitions
3 where table_name = 'EMP' ;

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME TABLESPACE_NAME NUM_ROWS
------------------------------ ------------------------------ ---------
MRMESSIN EMP
PART_2000 USERS 1

MRMESSIN EMP
PART_2001 USERS 1

MRMESSIN EMP
PART_2002 USERS 1

MRMESSIN EMP
PART_2003 USERS 1

MRMESSIN EMP
PART_1999 USERS 1

SQL>

Summary

Interval Partitioning provides a huge boost in partition management. This makes implementing partitioning much more attractive to those DBAs that have very full plates making partition maintenance more automatic and reducing our DBA maintenance activities. All other activity appears to match normal range partitioning keeping all the other benefits of range partitioning.

Oracle 11g REF Partitioning

2008-02-21T19:25:00.000-05:00

Oracle REF partitioning is a new partitioning feature with the 11g version of the Oracle database. REF Partitioning is the ability to partition a table based on the foreign key parent-child relationship. In REF partitioning the partitioning key of the child table is inherited from the parent table. REF partitioning also has all partition maintenance operations that change the logical shape of the parent table into the child table. REF Partitioning also improves performance for joins between the parent and child table by enabling partition-wise joins.

Ref partitioning can not be used when the parent table is partitioned using range interval partitioning or virtual column partitioning. The following message will happen upon attempt to create the partitioned table with ref partitioning.

CREATE TABLE order_items
*
ERROR at line 1:
ORA-14659: Partitioning method of the parent table is not supported

Another oddity found in this test was that if I created a partitioned index on the parent table with parallel I could not create the child table with ref partitioning as it got an ORA-0600, however if I created the index without parallel the child table with ref partitioning created ok. To get around this issue I created the partitioned index on the parent table after creating the child table with the ref partitioning.

For the basis as an example lets use the OE.ORDERS and OE.ORDER_ITEMS so create our own versions of the tables insert records where the rows in both sets are exactly the same. This will allow the comparison of a query that could be used against both sets of tables for a comparison. The original tables from OE schema will remain unchanged. For the copies for the partitioning testing we will use a range interval partition for the orders table and a ref partition for the order_items table.

Create the tables and indexes

CREATE TABLE orders
( order_id NUMBER(12) CONSTRAINT orders_order_id_nn NOT NULL,
order_date DATE CONSTRAINT orders_order_date_nn NOT NULL,
order_mode VARCHAR2(8),
customer_id NUMBER(6) CONSTRAINT orders_customer_id_nn NOT NULL,
order_status VARCHAR2(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_order_id_pk PRIMARY KEY (order_id)
)
PARTITION BY RANGE (order_date)
--INTERVAL (NUMTOYMINTERVAL(1,'month'))
( PARTITION p_pre_1999 VALUES LESS THAN (TO_DATE('01-JAN-1999','dd-MON-yyyy')),
PARTITION p_JAN_1999 VALUES LESS THAN (TO_DATE('01-FEB-1999','dd-MON-yyyy')),
PARTITION p_FEB_1999 VALUES LESS THAN (TO_DATE('01-MAR-1999','dd-MON-yyyy')),
PARTITION p_MAR_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','dd-MON-yyyy')),
PARTITION p_APR_1999 VALUES LESS THAN (TO_DATE('01-MAY-1999','dd-MON-yyyy')),
PARTITION p_MAY_1999 VALUES LESS THAN (TO_DATE('01-JUN-1999','dd-MON-yyyy')),
PARTITION p_JUN_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','dd-MON-yyyy')),
PARTITION p_JUL_1999 VALUES LESS THAN (TO_DATE('01-AUG-1999','dd-MON-yyyy')),
PARTITION p_AUG_1999 VALUES LESS THAN (TO_DATE('01-SEP-1999','dd-MON-yyyy')),
PARTITION p_SEP_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','dd-MON-yyyy')),
PARTITION p_OCT_1999 VALUES LESS THAN (TO_DATE('01-NOV-1999','dd-MON-yyyy')),
PARTITION p_NOV_1999 VALUES LESS THAN (TO_DATE('01-DEC-1999','dd-MON-yyyy')),
PARTITION p_DEC_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')),
PARTITION p_JAN_2000 VALUES LESS THAN (TO_DATE('01-FEB-2000','dd-MON-yyyy')),
PARTITION p_FEB_2000 VALUES LESS THAN (TO_DATE('01-MAR-2000','dd-MON-yyyy')),
PARTITION p_MAR_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','dd-MON-yyyy')),
PARTITION p_APR_2000 VALUES LESS THAN (TO_DATE('01-MAY-2000','dd-MON-yyyy')),
PARTITION p_MAY_2000 VALUES LESS THAN (TO_DATE('01-JUN-2000','dd-MON-yyyy')),
PARTITION p_JUN_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','dd-MON-yyyy')),
PARTITION p_JUL_2000 VALUES LESS THAN (TO_DATE('01-AUG-2000','dd-MON-yyyy')),
PARTITION p_AUG_2000 VALUES LESS THAN (TO_DATE('01-SEP-2000','dd-MON-yyyy')),
PARTITION p_SEP_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','dd-MON-yyyy')),
PARTITION p_OCT_2000 VALUES LESS THAN (TO_DATE('01-NOV-2000','dd-MON-yyyy')),
PARTITION p_NOV_2000 VALUES LESS THAN (TO_DATE('01-DEC-2000','dd-MON-yyyy')),
PARTITION p_DEC_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','dd-MON-yyyy'))
)
PARALLEL ;

CREATE TABLE order_items
( order_id NUMBER(12) CONSTRAINT oitems_order_id_nn NOT NULL,
line_item_id NUMBER(3) CONSTRAINT oitems_line_item_id_nn NOT NULL,
product_id NUMBER(6) CONSTRAINT oitems_product_id_nn NOT NULL,
unit_price NUMBER(8) CONSTRAINT oitems_unit_price_nn NOT NULL,
quantity NUMBER(8,2) CONSTRAINT oitems_quantity_nn NOT NULL,
sales_amount NUMBER(12,2) CONSTRAINT oitems_sales_amount_nn NOT NULL,
CONSTRAINT order_items_orders_fk
FOREIGN KEY (order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE (order_items_orders_fk)
PARALLEL ;

CREATE UNIQUE INDEX ORDER_ITEMS_PK
ON ORDER_ITEMS (ORDER_ID, LINE_ITEM_ID)
PARALLEL ;

-- have to create this index with parallel after the ref partition
-- table is created to avoid ORA-0600.
CREATE INDEX ORDERS_ORDER_DATE_NU
ON ORDERS (ORDER_DATE, ORDER_ID)
LOCAL
( PARTITION p_pre_1999,
PARTITION p_JAN_1999,
PARTITION p_FEB_1999,
PARTITION p_MAR_1999,
PARTITION p_APR_1999,
PARTITION p_MAY_1999,
PARTITION p_JUN_1999,
PARTITION p_JUL_1999,
PARTITION p_AUG_1999,
PARTITION p_SEP_1999,
PARTITION p_OCT_1999,
PARTITION p_NOV_1999,
PARTITION p_DEC_1999,
PARTITION p_JAN_2000,
PARTITION p_FEB_2000,
PARTITION p_MAR_2000,
PARTITION p_APR_2000,
PARTITION p_MAY_2000,
PARTITION p_JUN_2000,
PARTITION p_JUL_2000,
PARTITION p_AUG_2000,
PARTITION p_SEP_2000,
PARTITION p_OCT_2000,
PARTITION p_NOV_2000,
PARTITION p_DEC_2000
)
PARALLEL ;
-- Populate the new partitioned tables with the same data
-- from the original tables
insert into orders select * from oe.orders ;
commit ;

insert into order_items
select order_id, line_item_id, product_id, unit_price, quantity, (unit_price*quantity) sales_amount
from oe.order_items ;
commit;

execute dbms_stats.gather_table_stats ('MRMESSIN','ORDERS') ;
execute dbms_stats.gather_table_stats ('MRMESSIN','ORDER_ITEMS') ;

Test the Outcome

Execution One

SQL> set autotrace on
SQL> SELECT TO_DATE(to_char(o.order_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') order_date, sum(oi.sales_amount) sum_sales
2 FROM oe.orders o,
3 oe.order_items oi
4 WHERE o.order_id = oi.order_id
5 AND o.order_date BETWEEN TO_DATE('01-JAN-1999','DD-MON-YYYY')
6 AND TO_DATE('01-MAY-1999','DD-MON-YYYY')
7 GROUP BY order_date
8 ORDER BY order_date ;

ORDER_DAT SUM_SALES
--------- ----------
26-FEB-99 3322700.8
27-FEB-99 526310.4
11-MAR-99 1688284.8
12-MAR-99 2330752
13-MAR-99 952000
20-MAR-99 61651.2
20-MAR-99 1010368
29-MAR-99 345427.2
29-MAR-99 12288
11-APR-99 52352

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 795276733

------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 24 6 (17) 00:00:01
1 SORT ORDER BY 1 24 6 (17) 00:00:01
2 SORT GROUP BY NOSORT 1 24 6 (17) 00:00:01
* 3 FILTER
4 NESTED LOOPS
5 NESTED LOOPS 2 48 5 (0) 00:00:01
6 TABLE ACCESS BY INDEX ROWID ORDERS 1 15 2 (0) 00:00:01
* 7 INDEX RANGE SCAN ORD_ORDER_DATE_IX 1 1 (0) 00:00:01
* 8 INDEX RANGE SCAN ITEM_ORDER_IX 6 1 (0) 00:00:01
9 TABLE ACCESS BY INDEX ROWID ORDER_ITEMS 6 54 3 (0) 00:00:01
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TIMESTAMP' 1999-01-01 00:00:00'<=TIMESTAMP' 1999-05-01 00:00:00')
7 - access("O"."ORDER_DATE">=TIMESTAMP' 1999-01-01 00:00:00' AND "O"."ORDER_DATE"<=TIMESTAMP' 1999-05-01 00:00:00')
8 - access("O"."ORDER_ID"="OI"."ORDER_ID")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
875 consistent gets
0 physical reads
0 redo size
667 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

Execution Two

SQL> SELECT o.order_date, sum(oi.sales_amount) sum_sales
2 FROM orders o,
3 order_items oi
4 WHERE o.order_date BETWEEN TO_DATE('01-JAN-1999','DD-MON-YYYY')
5 AND TO_DATE('01-MAY-1999','DD-MON-YYYY')
6 AND o.order_id = oi.order_id
7 GROUP BY order_date
8 ORDER BY order_date ;

ORDER_DAT SUM_SALES
--------- ----------
26-FEB-99 3322700.8
27-FEB-99 526310.4
11-MAR-99 1688284.8
12-MAR-99 2330752
13-MAR-99 952000
20-MAR-99 61651.2
20-MAR-99 1010368
29-MAR-99 345427.2
29-MAR-99 12288
11-APR-99 52352

10 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1413427856
----------------------------------------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop TQ IN-OUT PQ Distrib
----------------------------------------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 5 105 14 (15) 00:00:01
1 PX COORDINATOR
2 PX SEND QC (ORDER) :TQ10002 5 105 14 (15) 00:00:01 Q1,02 P->S QC (ORDER)
3 SORT GROUP BY 5 105 14 (15) 00:00:01 Q1,02 PCWP
4 PX RECEIVE 5 105 14 (15) 00:00:01 Q1,02 PCWP
5 PX SEND RANGE :TQ10001 5 105 14 (15) 00:00:01 Q1,01 P->P RANGE
6 SORT GROUP BY 5 105 14 (15) 00:00:01 Q1,01 PCWP
* 7 HASH JOIN 1115 23415 13 (8) 00:00:01 Q1,01 PCWP
8 PX RECEIVE 176 2112 1 (0) 00:00:01 Q1,01 PCWP
9 PX SEND BROADCAST :TQ10000 176 2112 1 (0) 00:00:01 Q1,00 P->P BROADCAST
10 PX PARTITION RANGE ITERATOR 176 2112 1 (0) 00:00:01 2 6 Q1,00 PCWC
* 11 INDEX RANGE SCAN ORDERS_ORDER_DATE_NU 176 2112 1 (0) 00:00:01 2 6 Q1,00 PCWP
12 PX BLOCK ITERATOR 21280 187K 11 (0) 00:00:01 1 25 Q1,01 PCWC
13 TABLE ACCESS FULL ORDER_ITEMS 21280 187K 11 (0) 00:00:01 1 25 Q1,01 PCWP
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("O"."ORDER_ID"="OI"."ORDER_ID")
11 - access("O"."ORDER_DATE">=TO_DATE(' 1999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O"."ORDER_DATE"<=TO_DATE(' 1999-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
45 reursive calls
3 db block gets
357 consistent gets
0 physical reads
664 redo size
683 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>

Execution Three

SQL> set linesize 200
SQL> set timing on
SQL>
SQL> spool ref_partitioning_test.out
SQL>
SQL> set autotrace on
SQL> SELECT TO_DATE(to_char(o.order_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') order_date, sum(oi.sales_am
2 FROM oe.orders o,
3 oe.order_items oi
4 WHERE o.order_id = oi.order_id
5 AND o.order_date BETWEEN TO_DATE('01-JAN-1999','DD-MON-YYYY')
6 AND TO_DATE('01-MAY-1999','DD-MON-YYYY')
7 GROUP BY order_date
8 ORDER BY order_date ;

ORDER_DAT SUM_SALES
--------- ----------
26-FEB-99 6645401.6
27-FEB-99 1052620.8
11-MAR-99 3376569.6
12-MAR-99 4661504
13-MAR-99 1904000
20-MAR-99 123302.4
20-MAR-99 2020736
29-MAR-99 690854.4
29-MAR-99 24576
11-APR-99 104704

10 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 795276733

------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 24 6 (17) 00:00:01
1 SORT ORDER BY 1 24 6 (17) 00:00:01
2 SORT GROUP BY NOSORT 1 24 6 (17) 00:00:01
* 3 FILTER
4 NESTED LOOPS
5 NESTED LOOPS 2 48 5 (0) 00:00:01
6 TABLE ACCESS BY INDEX ROWID ORDERS 1 15 2 (0) 00:00:01
* 7 INDEX RANGE SCAN ORD_ORDER_DATE_IX 1 1 (0) 00:00:01
* 8 INDEX RANGE SCAN ITEM_ORDER_IX 6 1 (0) 00:00:01
9 TABLE ACCESS BY INDEX ROWID ORDER_ITEMS 6 54 3 (0) 00:00:01
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TIMESTAMP' 1999-01-01 00:00:00'<=TIMESTAMP' 1999-05-01 00:00:00')
7 - access("O"."ORDER_DATE">=TIMESTAMP' 1999-01-01 00:00:00' AND
"O"."ORDER_DATE"<=TIMESTAMP' 1999-05-01 00:00:00')
8 - access("O"."ORDER_ID"="OI"."ORDER_ID")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1724 consistent gets
0 physical reads
116 redo size
669 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> SELECT o.order_date, sum(oi.sales_amount) sum_sales
2 FROM orders o,
3 order_items oi
4 WHERE o.order_date BETWEEN TO_DATE('01-JAN-1999','DD-MON-YYYY')
5 AND TO_DATE('01-MAY-1999','DD-MON-YYYY')
6 AND o.order_id = oi.order_id
7 GROUP BY order_date
8 ORDER BY order_date ;

ORDER_DAT SUM_SALES
--------- ----------
26-FEB-99 6645401.6
27-FEB-99 1052620.8
11-MAR-99 3376569.6
12-MAR-99 4661504
13-MAR-99 1904000
20-MAR-99 123302.4
20-MAR-99 2020736
29-MAR-99 690854.4
29-MAR-99 24576
11-APR-99 104704

10 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1413427856
------------------------------------------------------------------------------------ Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop
------------------------------------------------------------------------------------
0 SELECT STATEMENT 5 105 21 (10) 00:00:01
1 PX COORDINATOR
2 PX SEND QC (ORDER) :TQ10002 5 105 21 (10) 00:00:01
3 SORT GROUP BY 5 105 21 (10) 00:00:01
4 PX RECEIVE 5 105 21 (10) 00:00:01
5 PX SEND RANGE :TQ10001 5 105 21 (10) 00:00:01
6 SORT GROUP BY 5 105 21 (10) 00:00:01
* 7 HASH JOIN 2231 46851 20 (5) 00:00:01
8 PX RECEIVE 352 4224 1 (0) 00:00:01
9 PX SEND BROADCAST :TQ10000 352 4224 1 (0) 00:00:01
10 PX PARTITION RANGE ITERATOR 352 4224 1 (0) 00:00:01 2 6
* 11 INDEX RANGE SCAN ORDERS_ORDER_DATE_NU 352 4224 1 (0) 00:00:01 2
12 PX BLOCK ITERATOR 42560 374K 18 (0) 00:00:01 1 25
13 TABLE ACCESS FULL ORDER_ITEMS 42560 374K 18 (0) 00:00:01 1 25
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("O"."ORDER_ID"="OI"."ORDER_ID")
11 - access("O"."ORDER_DATE">=TO_DATE(' 1999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O"."O
'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
45 recursive calls
3 db block gets
481 consistent gets
0 physical reads
664 redo size
685 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
10 rows processed

Summary

Initial tests were done without parallel and did not yield expected results, expected results did not really start to happen until parallel was used and the number of rows in the parent and child tables significantly increased. Keep in mind that parallel operations increase CPU utilization and therefore a CPU bound system may not encounter the same results.

Running many tests and increasing the number of rows proportionately in each table with each execution. As the number of rows in orders and orders items increased the larger the gap in buffer gets to resolve the query. On a very small table the non-partitioned table used far less resources, as the number of rows increased the closer the buffer gets got until the partitioned table was taking less resources. Even as the difference in the buffer gets changed as the number of records increased the number of sorts in memory stayed consistent. Another thing noticed in the testing is that the runtime performance stayed more consistent with the partitioned tables more so the run time increase was lower then with the non-partitioned tables as the number of rows increased.

This is a nice new partitioning feature for parent-child relationships and initial testing shows this partitioning could have benefits for tables that are large. Smaller tables did not show any advantages, but the advantages seem to gain as the table sizes/number of rows increases.

Initial testing has shown that this new Oracle Partitioning Feature is worth the time to do further testing on very large data sets.

Oracle Hidden (_) Parameter Values

2008-02-05T20:12:00.000-05:00

To query the hidden (_) parameters for an oracle instance use the following, can limit the list using the name or partial of the name of the parameter you are looking for:

set pagesize 60
set linesize 100
select a.ksppinm NAME,
a.ksppdesc DESCRIPTION,
b.ksppstvl SESSION_VALUE,
c.ksppstvl SYSTEM_VALUE
from x$ksppi a,
x$ksppcv b,
x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and (a.ksppinm like '__%') ;

Oracle see who has been granted SYSDBA

2008-02-05T19:51:00.000-05:00

In Oracle to query who has been granted SYSDBA is done by the v$pwfile_users

SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
-------- ----- ----- -----
SYS TRUE TRUE FALSE
MRMESSIN TRUE FALSE FALSE
SQL>

RMAN Backup using a Wallet for Authentication

2008-02-02T12:33:00.000-05:00

RMAN Backup using a Wallet for Authentication

Ever want to not have passwords in you RMAN backup scripts?
Oracle Wallet will provide a way to connect to the RMAN repository without having to create a way to script in a password.

1. setup password files that allow and disallow logging in with SYSDBA (This is optional and only if you want to be able to control remote login as sysdba)

a. cd $ORACLE_HOME/dbs
b. export ORACLE_SID=rman
c. orapwd file=orapwdbname.nosysdba password= nosysdba=y
d. orapwd file=orapwdbname.sysdba password= nosysdba=n

2. Add processes to enable sysdba for backup and disable when backup is complete (optional only if you want to control when remote sysdba connections are allowed)
a. enable sysdba logins
cp –p orapwdbname.sysdba orapwdbname

b. disable sysdba logins
cp –p orapwdbname.nosysdba orapwdbname

* For our example we will assume that we will have it on and it is left on, but this will give the option to allow and disallow sysdba connections as needed.

3. Avoid having to pass username and password at the command line.
a. add TNS Alias to tnsnames.ora file for RMANSYS connections

RMANSYS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rman)
)
)

b. Create Wallet
cd $ORACLE_HOME/network/admin
mkstore –wrl $ORACLE_HOME/network/admin –create

* Enter password for wallet (will have to be entered 2 times for confirmation)
* Creates 2 files ewallet.p12 and cwallet.sso

c. Create credential for Wallet
mkstore -wrl $ORACLE_HOME/network/admin –createCredential rmansys rman

* should see something like Create credential oracle.security.client.connect_string1

d. Edit sqlnet.ora
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <$ORACLE_HOME*>/network/admin)))

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

* Must put in path for $ORACLE_HOME

e. Restart the database listener
lsnrctl stop
lsnrctl start

4. Now you can use the wallet credentials to login as sys.
sqlplus /@RMANSYS

export ORACLE_SID=trn01
rman target=/ catalog=/@rmansys

Oracle Fine Grained Auditing

2008-02-02T12:23:00.000-05:00

Regulations such as HIPPA have placed upon Oracle Database Administrators (DBAs) the need to audit DDL (database schema changes), DML (inserts/updates/deletes) as well as select access to what is considered “protected information”. Auditing changes has always had value and was typical auditing done in most critical databases. Auditing changes would allow information to be gathered to show when a change happened and who made the change. Another audit need has become more critical due to regulations and that is select or read access to “protected information”. Auditing is now required to gather information on when and who read or accessed certain information.

Oracle auditing has existed in the Oracle database for quite some time and is used to gather information on changes and access to the data in the database. It has provided the ability to audit inserts, updates and deletes on table as well as the ability to audit object creations, modifications and removals as auditing in the database progressed later versions allowed the auditing of selects. Auditing is also able to audit log on and log off from the database and many other operations. However the auditing is always at a high level, what if the need was to only audit under more specific circumstances. To get this level of auditing might require a trigger that must be coded and there fore the code developed and maintained so that some level of criteria could be used to determine if an audit record should be written or not. Oracle Fine Grained Auditing provides the ability to add criteria based auditing to gather information (data) on what has changed or been accessed within the database without the need to write trigger code.

Components for FGA

Oracle FGA is setup and managed though the dbms_fga package and all audit information gathered is accessed via the dba_fga_audit_trail view, the actual sys table that the audit information goes into is fga_log$.

Examine the definition of the dbms_fga package, the dba_fga_audit_trail view and the sys table fga_log$ as these are the objects that will be used in the management of FGA.

SQL> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
PROCEDURE DISABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT

SQL> desc dba_fga_audit_trail
Name Null? Type
----------------------------------------- -------- ------------------
SESSION_ID NOT NULL NUMBER
TIMESTAMP DATE
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
EXT_NAME VARCHAR2(4000)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
SCN NUMBER
SQL_TEXT NVARCHAR2(2000)
SQL_BIND NVARCHAR2(2000)
COMMENT$TEXT VARCHAR2(4000)
STATEMENT_TYPE VARCHAR2(7)
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
TRANSACTIONID RAW(8)
STATEMENTID NUMBER
ENTRYID NUMBER
OBJ_EDITION_NAME VARCHAR2(30)

SQL> desc sys.fga_log$
Name Null? Type
----------------------------------------- -------- -------------------------
SESSIONID NOT NULL NUMBER
TIMESTAMP# DATE
DBUID VARCHAR2(30)
OSUID VARCHAR2(255)
OSHST VARCHAR2(128)
CLIENTID VARCHAR2(64)
EXTID VARCHAR2(4000)
OBJ$SCHEMA VARCHAR2(30)
OBJ$NAME VARCHAR2(128)
POLICYNAME VARCHAR2(30)
SCN NUMBER
SQLTEXT VARCHAR2(4000)
LSQLTEXT CLOB
SQLBIND VARCHAR2(4000)
COMMENT$TEXT VARCHAR2(4000)
PLHOL LONG
STMT_TYPE NUMBER
NTIMESTAMP# TIMESTAMP(6)
PROXY$SID NUMBER
USER$GUID VARCHAR2(32)
INSTANCE# NUMBER
PROCESS# VARCHAR2(16)
XID RAW(8)
AUDITID VARCHAR2(64)
STATEMENT NUMBER
ENTRYID NUMBER
DBID NUMBER
LSQLBIND CLOB
OBJ$EDITION VARCHAR2(30)

Define Auditing Policies

To implement Oracle FGA for a successful audit trail the audit rules and policies must be determined and documented. The organization must determine the compliance rules that the organization is subject to, such as HIPPA and SOX. Identify the auditing requirements to fulfill the compliance rules such as DDL auditing (create table, alter table/index, drop table/index, insert, update and delete on protected data as well as select/read access to protected data) The organization is also free to add additional auditing for its own internal use potentially for security purposes for use in forensic activities in cases of security breaches or activity monitoring.

Setup FGA

To setup FGA policies that trigger audit records will need to be defined using policies. The policies will be created based on the audit rules and policies identified that the organization is subject to as well as auditing rule the business has determined itself wants to have possibly for better tracking for security purposes.

-- drop existing audit policy
execute dbms_fga.drop_policy ('SCOTT','EMP','SALARY_INCREASES') ;

-- Audit all Salary Changes to EMP
begin
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'SALARY_INCREASES',
audit_column => 'SAL',
enable => true, statement_types => 'UPDATE', audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
end;
/

-- drop existing audit policy
execute dbms_fga.drop_policy ('SCOTT','EMP','NEW_EMP_HIGH_SALARY') ;

-- Audit all new employee creations with salary > 2000
begin
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'NEW_EMP_HIGH_SALARY',
audit_condition => 'SAL>=2000',
enable => true, statement_types => 'INSERT', audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
end;
/

-- drop existing audit policy
execute dbms_fga.drop_policy ('SCOTT','BONUS','BONUS_CHANGES') ;

-- Audit all changes on BONUS
begin
dbms_fga.add_policy(
object_schema => 'SCOTT',
object_name => 'BONUS',
policy_name => 'BONUS_CHANGES',
enable => true, statement_types => 'INSERT,UPDATE,DELETE', audit_trail => DBMS_FGA.DB_EXTENDED, audit_column_opts => DBMS_FGA.ANY_COLUMNS
);
end;
/

Show Audit Policies

set linesize 120
set pagesize 50

TTITLE 'FGA Policies'
COL object_schema FORMAT A10 HEADING 'Schema'
COL object_name FORMAT A20 HEADING 'Object Name' WRAP
COL policy_name FORMAT A16 HEADING 'Policy Name' WRAP
COL policy_text FORMAT A24 HEADING 'Policy Text' WRAP
COL policy_column FORMAT A16 HEADING 'Policy Column' WRAP
COL enabled FORMAT A05 HEADING 'Enabled'
COL siud FORMAT A04 HEADING 'SIUD'

SELECT policy_name, policy_text, policy_column, enabled, object_schema, object_name,
DECODE(sel,'YES','Y','N') DECODE(ins,'YES','Y','N')
DECODE(upd,'YES','Y','N') DECODE(del,'YES','Y','N') siud
FROM dba_audit_policies ;

TTITLE OFF

Show Audit Results

Using Fine Grained Audit Trail:

set linesize 120
set pagesize 50

TTITLE 'Fine-Grained Auditing (FGA) Audit Trail'
COL auditdate FORMAT A20 HEADING 'Date/Time'
COL policy_name FORMAT A16 HEADING 'Policy Name' WRAP
COL object_schema FORMAT A10 HEADING 'Schema'
COL object_name FORMAT A20 HEADING 'Object Name' WRAP
COL db_user FORMAT A10 HEADING 'User'
COL sql_text FORMAT A36 HEADING 'SQL Text' WRAP
SELECT TO_CHAR(timestamp,'mm/dd/yyyy hh24:mi:ss') auditdate,
db_user,
object_schema,
object_name,
policy_name,
sql_text
FROM dba_fga_audit_trail
ORDER BY timestamp ;

TTITLE OFF

Could also use the combined audit trail as well to query the FGA Audit Trail this would allow the use of a single query/report for the entire audit trail:

Combined Audit Trail:

SET linesize 120
SET pagesize 50

TTITLE 'Standard/FGA Audit Trail'
COL audittype FORMAT A03 HEADING 'Audit Type'
COL db_user FORMAT A10 HEADING 'User'
COL object_schema FORMAT A06 HEADING 'Schema'
COL object_name FORMAT A20 HEADING 'Object Name' WRAP
COL policy_name FORMAT A16 HEADING 'Policy Name' WRAP
COL auditdate FORMAT A20 HEADING 'Date/Time'
COL sql_text FORMAT A32 HEADING 'SQL Text' WRAP

SELECT DECODE(audit_type, 'Fine Grained Audit', 'FGA’, 'Standard Audit', 'STD', 'U')
auditype,
db_user,
object_schema,
object_name,
policy_name,
TO_CHAR(extended_timestamp,'mm/dd/yyyy hh24:mi:ss') auditdate,
sql_text
FROM dba_common_audit_trail
WHERE db_user NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN')
ORDER BY extended_timestamp, db_user, object_schema, object_name ;

TTITLE OFF

Testing Results

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Wed Jan 30 page 1
FGA Policies

Policy Name Policy Text Policy Column Enabl Schema Object Name SIUD
----------- ----------- ------------- ----- ------ ----------- ----
BONUS_CHANGES YES SCOTT BONUS NYYY
SALARY_INCREASES SAL YES SCOTT EMP NNYN
NEW_EMP_HIGH_SALARY SAL>=2000 YES SCOTT EMP NYNN

1 row created.

1 row updated.

1 row deleted.

Commit complete.

1 row created.

Commit complete.

Wed Jan 30 page 1
Fine-Grained Auditing (FGA) Audit Trail

Date User Schema Object Name Policy Name SQL Text
---------- ---------- ---------- ----------------- ---------------- ------------------------------------
01/30/2008 SYSTEM SCOTT BONUS BONUS_CHANGES delete from scott.bonus
08:07:28
01/30/2008 SYSTEM SCOTT BONUS BONUS_CHANGES insert into scott.bonus values ('BIG
08:07:28 BOSSMAN', 'THEMAN', 10000, 50000)
01/30/2008 SYSTEM SCOTT EMP SALARY_INCREASES update scott.emp set sal=4000 where
08:07:28 empno=7844
01/30/2008 SYSTEM SCOTT EMP NEW_EMP_HIGH_SAL insert into scott.emp values (8000,
08:07:28 ARY 'THEMAN', 'BIGBOSSMAN', NULL, TO_DATE('01-JAN-2008','DD-MON-YYYY'), 10000, 50000, 10)

Managing FGA

We have to manage this audit information being generated as if we do not the amount of audit information will continue to grow and potential lead to performance issues within the database. Therefore was must move audit information out of the system table to another table or even another database so that we can keep it in accordance with the business rules and regulations. After all the audit data is moved and the audit information verified that it has been properly moved we will need to purge the audit data from the system table.

A simple way to manage the fine grained auditing audit trail would be to create a package and use that package to move the data in the audit trail for more long term storage and remove the audit records that have been moved. Simple development of a PL/SQL package that can be scheduled within the database to keep the system audit trail clean by placing in a staging area that can be used by Extraction Transformation Load (ETL) processes for longer term storage and reporting.

Example Package, not complete, but just to give an idea of how this might be approached:

CREATE OR REPLACE PACKAGE manage_audit_trail
IS
PROCEDURE move_fga (p_msg OUT VARCHAR2) ;
PROCEDURE remove_fga (p_days IN NUMBER, p_msg OUT VARCHAR2) ;
PROCEDURE remove_audit (p_days IN NUMBER, p_msg OUT VARCHAR2) ;
END ;
/

CREATE OR REPLACE PACKAGE BODY manage_audit_trail
IS
-- Move Fine Grained Audit data to a staging table
PROCEDURE move_fga (p_msg OUT VARCHAR2)
IS
BEGIN
MERGE INTO AUDIT_STAGE a
USING dba_fga_audit_trail at
ON
(a.SESSION_ID = at.SESSION_ID
a.TIMESTAMP = at.TIMESTAMP
a.DB_USER = at.DB_USER
a.ENTRYID = at.ENTRYID)
WHEN NOT MATCHED THEN
INSERT (a.SESSION_ID, a.TIMESTAMP, a.DB_USER,
a.OS_USER, a.USERHOST, a.CLIENT_ID,
a.ECONTEXT_ID, a.EXT_NAME, a.OBJECT_SCHEMA,
a.OBJECT_NAME, a.POLICY_NAME, a.SCN,
a.SQL_TEXT, a.SQL_BIND, a.COMMENT$TEXT,
a.STATEMENT_TYPE, a.EXTENDED_TIMESTAMP,
a.PROXY_SESSIONID, a.GLOBAL_UID,
a.INSTANCE_NUMBER, a.OS_PROCESS,
a.TRANSACTIONID, a.STATEMENTID,
a.ENTRYID, a.OBJ_EDITION_NAME)
VALUES
(at.SESSION_ID, at.TIMESTAMP, at.DB_USER,
at.OS_USER, at.USERHOST, at.CLIENT_ID,
at.ECONTEXT_ID, at.EXT_NAME, at.OBJECT_SCHEMA,
at.OBJECT_NAME, at.POLICY_NAME, at.SCN,
at.SQL_TEXT, at.SQL_BIND, at.COMMENT$TEXT,
at.STATEMENT_TYPE, at.EXTENDED_TIMESTAMP,
at.PROXY_SESSIONID, at.GLOBAL_UID,
at.INSTANCE_NUMBER, at.OS_PROCESS,
at.TRANSACTIONID, at.STATEMENTID,
at.ENTRYID, at.OBJ_EDITION_NAME) ;

EXCEPTION
WHEN OTHERS THEN
p_msg := SQLERRM ;
END move_fga ;

-- Removes records from the fga audit trail (sys.fga_log$) <=
-- sysdate – number of days passed. # of days defaults to 30 days
PROCEDURE remove_fga (p_days IN NUMBER, p_msg OUT VARCHAR2)
IS
BEGIN
-- initialize p_msg to null
p_msg := NULL ;

-- Check number of days if not default to 30 days

IF p_days IS NULL then
p_days = 30 ;
END IF ;

-- remove the records from the fine grained audit trail
delete
from sys.fga_log$
where timestamp# <= SYSDATE – p_days ;

EXCEPTION
WHEN OTHERS THEN
p_msg := SQLERRM ;
END remove_fga ;

-- Removes records from the audit trail (sys.aud$) <=
-- sysdate – number of days passed. # of days defaults to 30 days
PROCEDURE remove_audit (p_days IN NUMBER, p_msg OUT VARCHAR2)
IS
BEGIN
-- initialize p_msg to null ;
p_msg := NULL ;

-- Check number of days if not default to 30 days
IF p_days IS NULL then
p_days = 30 ;
END IF ;

-- remove the records from the audit trail
delete
from sys.aud$
where timestamp# <= SYSDATE – p_days ;

EXCEPTION
WHEN OTHERS THEN
p_msg := SQLERRM ;
END remove_audit ;
END ;

/

Summary

Oracle Fine Grained Auditing provides the Oracle DBAs a great way to gather the required audit information, define criteria for the gathering of the audit information and actually helps simplify managing the audit data by consolidating all audit data into a single audit trail making the movement and management of audit data for long term reporting simpler. The key to a complete successful audit trail is the ability to report the audit information to answer the questions coming from regulation agencies and internal business entities, like security and compliance offices.

Accessing Monitored Database Objects with OEM Grid Control Reports

2008-02-02T12:12:00.000-05:00

Accessing Monitored Database Objects with OEM Grid Control Reports

Ever want to create an Oracle Enterprise Manager Grid Control Report on using objects in the monitored database not from the gird control repository objects? There is a way

We can create a user in the grid control repository database create a database link to the monitored database create view(s) of the across the database link to the object(s) that we want to be able to built the OEM Grid Control Report on then grant access to the view(s) to the MGMT_VIEW user in the grid control repository. Then you can use the view created in a OEM Grid Control Report.

Example:

-- will set up a user in the grid control repository that will own
-- the private database link and views here we will name the user same
-- as the database
create user prd identified by password ;
alter user prd default tablespace users ;
alter user prd temporary tablespace temp ;

grant create session to prd ;
grant create database link to prd ;
grant create view to prd ;

-- connect as our user so that we can create the database
-- link and view as well as grant to the MGMT_VIEW user
connect prd/password@gridrepository

-- drop the database link if it already exists
drop database link prd ;

-- create the database link to the monitored database where the
-- objects we want to use in our OEM Grid Control Reports exists
create database link prd connect to dbsnmp identified by password using 'PRD' ;

-- Create our view
create or replace view prd.DBA_HIST_SYSMETRIC_SUMMARY as select * from DBA_HIST_SYSMETRIC_SUMMARY@prd ;

-- grant permissions to the MGMT_VIEW user
grant select on prd.DBA_HIST_SYSMETRIC_SUMMARY to MGMT_VIEW ;

-- Now ready to use the prd.DBA_HIST_SYSMETRIC_SUMMARY
-- in our Grid Control Reports