Borndba

If you have administered Grid Control, you would have known two types of corrective actions.

1. Corrective Action Script
2. Agent Response Script

What it does is you can invoke a remediation script based on an action. Suppose you want to add a datafile if the tablespace usage is nearing 95%, you can write a script and keep in the target server. Configure this script in metrics and policy setting of tablespace usage metrics. So whenever any tablespace usage crossing 95%, the script stored in the target server will be invoked.

The advantage is that it avoid human interference,errors and enhance the effective usage of DBA.

Consider if you have 1000s of targets and each targets you need to add remediation scripts to the metrics, it’s going to take lot of time.
What if i have option to invoke the Agent response script via sqlplus. It’s going to give me better control. I can write a package, which will poll the metrics tables and invoke the scripts based on varying conditions. This is not possible to implement in metrics and policy tab.

Opened a SR with oracle and justification like below is given,
1. We have 1000s of targets for most of which we need to enable response action. Manually updating each target is time consuming. We can’t have template as the scripts getting invoked will have different name based on db name.
2. There are different groups of targets for which invoking response actions script will be based on certain scenarios. If response action can be invoked manually, then we can put our conditions and invoke accordingly.
3. We will have control on one place. Modifying something will be quicker instead of going to each target’s metric & policy and changing.

We have filed enhancement request filed with oracle.

ER IS : Bug 10214800 – INVOKING ” RESPONSE ACTION” VIA SQLPLUS

convert this post to pdf.

If we could simulate the performance issue in sqlplus, consider we can fix the issue most of the time. Incase if you need oracle supports help in a query, you can build the test case like below.

SQL> DECLARE tc clob;
  2  BEGIN
  3  DBMS_SQLDIAG.EXPORT_SQL_TESTCASE(
  4  DIRECTORY=>'DATA_PUMP_DIR',
  5  SQL_ID=>'6bcwxgty705p8',
  6  TESTCASE=>tc);
  7  END;
  8  /

PL/SQL procedure successfully completed.

You need to supply sql_id and directory name. You can zip this and sent it to support. If you give exportdata to true, expdp will include table data.

This is tested in 11gR2.

convert this post to pdf.

impdp will do a direct load import. This is one way to confirm

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
            176.1875

SQL> truncate table t;

Table truncated.

SQL> alter table t COMPRESS FOR OLTP ;

Table altered.

Note: compress for all operation is deprecated in 11gR2 instead it will be compress for oltp

SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append

Import: Release 11.2.0.1.0 - Production on Thu Oct 14 21:30:33 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=data_pump_d
ir dumpfile=xyz.dmp tables='test.t' table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."T" exists. Data will be appended to existing table but
all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T"                                  149.2 MB 1689984 rows
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:31:03

Let us check the size now.

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
             72.1875

From 176mb the size is reduced to 72mb. Not let us try the direct load after truncating the table,

SQL> alter table t compress for DIRECT_LOAD OPERATIONS;

Table altered.

SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append status=2 

Import: Release 11.2.0.1.0 - Production on Thu Oct 14 21:32:28 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA directory=data_pump_d
ir dumpfile=xyz.dmp tables='test.t' table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "TEST"."T" exists. Data will be appended to existing table but
all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:43:55

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
             72.1875

Now the size has reduced from 176mb

SQL> alter table t nocompress;

Table altered.

SQL> host impdp directory=data_pump_dir dumpfile=xyz.dmp tables='test.t' table_e
xists_action=append status=2 

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name='T';

SUM(BYTES/1024/1024)
--------------------
            176.1875

Without compression it’s 176mb.

convert this post to pdf.

If your DB is applied with patch#8198906, then beware that you may run into corruption when extending the table manually.
This patch is obselete now. You have to uninstall and install 9711859.

If your shop is managing 1000s of DBs then how to find out on which installation this patch is applied.

If these DBs are managed in OEM, then you can use below query

select * from  mgmt_inv_patch_fixed_bug  where bug_number=8198906;

convert this post to pdf.