A Useful Sql To Check Tablespace Growth

To find latest 30 days tablespace growth report:

set heading on
set linesize 5500
set pages 999

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
   , ts.tsname
   , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
   , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
   FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
  , DBA_HIST_TABLESPACE_STAT ts
    , DBA_HIST_SNAPSHOT sp
    , DBA_TABLESPACES dt
    WHERE tsu.tablespace_id= ts.ts#
   AND tsu.snap_id = sp.snap_id
   AND ts.tsname = dt.tablespace_name
   AND ts.tsname  IN ('TEST1','TEST2')
   AND sp.begin_interval_time  between sysdate -30 and sysdate
   GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
   ORDER BY ts.tsname, days;

To find specific date of tablespace report: ie 01sep2012 or 11nov0212

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
   , ts.tsname
   , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
   , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
   FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
  , DBA_HIST_TABLESPACE_STAT ts
    , DBA_HIST_SNAPSHOT sp
    , DBA_TABLESPACES dt
    WHERE tsu.tablespace_id= ts.ts#
   AND tsu.snap_id = sp.snap_id
   AND ts.tsname = dt.tablespace_name
   AND ts.tsname  IN ('TEST1','TEST2')
   AND TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') = '01-09-2012'
   GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
   ORDER BY ts.tsname, days;

then you can easily figure how much space growth between 01sep2012 and 11nov0212

How To View Oracle Text Query Exec Plan

Oracle Text Query is not like normal sql which we can get sql plan via "explain plan for" "select * from table(dbms_xplan.display());".....

It has specific API to get them:

Creating the Explain Table
To create an explain table called test_explain for example, use the following SQL
statement:
create table test_explain(
explain_id varchar2(30),
id number,
parent_id number,
operation varchar2(30),
options varchar2(30),
object_name varchar2(64),
position number,
cardinality number);

Running CTX_QUERY.EXPLAIN
To obtain the expansion of a query expression such as comp% OR ?smith, use CTX_QUERY.EXPLAIN as follows:
ctx_query.explain(
index_name => ''TEST_IDCTEXT2',
text_query => 'comp% OR ?smith',
explain_table => 'test_explain',
sharelevel => 0,
explain_id => 'Test');

exec ctx_query.explain(index_name => 'TEST_IDCTEXT2',text_query => '((((DEFINESCORE((test), RELEVANCE * .1)) )))  and  (((((EMPL%) WITHIN dDocAccount)) or (((CATEST1%) WITHIN dDocAccount)) or (((GIT/GLOBAL#0023IT#0023ONLY%) WITHIN dDocAccount)) or (((GIT/ALL%) WITHIN dDocAccount)) or (((idcnull) WITHIN dDocAccount))))',explain_table => 'test_explain',sharelevel => 0,explain_id => 'Test');

Retrieving Data from Explain Table
To read the explain table, you can select the columns as follows:

  select lpad('   ',level-1)||operation||' '||options||' '||object_name "Plan"
 from test_explain
  connect by prior id = parent_id
start with id=1
 order siblings by position

How Exadata Offload Some RMAN Incremtal Backup Load To Cell

It's well known that from 10g, oracle db has a new feature to enable block change tracking. It does a great job to avoid RMAN to scan the whole data files while only track the data that have been changed since the last backup.

At that time, exadata was not out yet. With new Exadata with storage cell storage, It enhances the block change tracking and offload some incremental backup load to Cell. Here is how it works

The key is on _bct_chunk_size which is hidden parameter ,default is 0 .
However CTWR(Change Tracking Writer) uses 32K(a chunk).It can't be 0 though in reality.
If we have DB Block set 8K, it means the minimum  track unit of CTWR is 4 db blocks.
If one of 4 is changed, all 4 are tracked in tracking files

Before Exadata, RMAN need some work to pick the changes blocks from the chunk. Now with Exadata, this work can be offloaded into Cell. On Exadata, oracle recommend AU size to be 4M.
4M can hold quite a few 32k(chunk) . It gives enough space for Cell to filter or inspect blocks in the AU, then return the only changed blocks to Database, thus offload some load for RMAN.

To check ASM AU size is simple:
select ALLOCATION_UNIT_SIZE,NAME from v$asm_diskgroup;

How To Delete Backupset Via BP Key

Sometimes we have 2 copies of 1 backupset, they are with the same tag.
If we wanna delete 1 and keep only 1 copy, we can't delete it via tag like -- delete backupset tag '***'

In this case we need to use " change backuppiece <BP_KEY> delete;"

How To Use NETSTAT To Find Interrupted Connections

This note is taking ldap connection for example. We use can the same method  for other type of connections
 Below example is to show ldap connections are interrupted by firewall. Application side has no idea what happen ,thus spinning on ldap search.


 apps%  netstat -ap | grep ldap|  grep 24512
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)
tcp        0      0 test.us.test.com:24512 bigip-testldap-stage.us.:ldap ESTABLISHED 27714/java

And I worked with LDAP engineer to get the corresponding netstat output on the ldap server:

ldap$ netstat -ap | grep 24512
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)
tcp        0      0 ldap.us.test.com:ldap  test.us.test.com:24512 ESTABLISHED 1442/oidldapd

So the connection was established on both sides and thus login to WebLogic Admin had no issue.

After around 30mins. Did the same netstat test and only apps server had showed an output while the connection on the ldap server was already gone/closed.

apps%  netstat -ap | grep ldap|  grep 24512
(Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)
tcp        0      0 test.us.test.com::24512 bigip-testldap-stage.us.:ldap ESTABLISHED 27714/java

ldap$ netstat -ap | grep 24512
    (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.)

...nothing

So the connections are interrupted by firewall.
In this case, I got the spinning (Ldap stuck) issue trying to logon to the WebLogic Admin console.
The solution is simple. Add a regular test to make connections alive.

Error When Enabling Block Change Tracking For RMAN

Here is the common error you see when you enable it:

SQL> alter database enable block change tracking;
alter database enable block change tracking

ERROR at line 1:
ORA-19773: must specify change tracking file name

The error is due to we need set both db_create_file_dest

If not,
alter system set db_create_file_dest='+DATA' scope=both sid='*';

or manually use location:

alter database enable block change tracking using file 'location';

All these parameters are dynamic, no need to bounce DB

A few Tips To Save The Database Statistics

For baselines and reference: export AWR
        ~rdbms/admin/awrextr.sql
        ~rdbms/admin/awrload.sql

 For diagnostics: always get AWR and ASH reports
        ~rdbms/admin/ashrpt
        ~rdbms/admin/awrrpt

 For emergencies and “last gasp”: get ASH dump(*)
         oradebug ashdump