Oracle RDBMS 11.2 Online patch

    Today I had a colleague asking if he can install a opatch online or should he do it in a traditional fashion and any risk involved with it. To answer this I had to give some explanation and examples, which I thought would be useful for others if posted in a blog.

   A normal patch comprises of one or more object (.o) files and/or libraries (.a files). Installation requires shutting down the RDBMS instance, re-linking the oracle binary, and restarting the instance. Whereas the online patch can be applied to a live RDBMS instance.

    An online patch contains a single shared library; installing an online patch does not require shutting down the instance or relinking the oracle binary. An online patch can be installed/un-installed using Opatch (which uses oradebug commands internally).Since this is done with oradebug if you have multiple databases running from a single oracle home you have push them across to the databases you require.

  

  Is patching online always recommended if we have the option to do so ? I would say use the online option only in cases of a quick fix required and a downtime cannot be borrowed immediately. Else stay away from the online option. Online patches as mentioned earlier are share libraries and require additional memory since the modified functions have to be in the memory. Oracle defines the overhead calculation as below:

Unix : memory overhead = ( # of processes +1) x size of ( .pch file)

 In my example the overhead is as below:
 
 processes parameter is set to 200, and the .pch file size is 1.67 MB

 Memory overhead = (200 + 1) * 1.67 MB = 335 MB
 
 When we have multiple online patches like these the overhead would go up, so even if we apply online patches it is recommended to remove them and install it in the normal fashion during a downtime acquired later.

 Here we will take a example of applying patch 17018214 to a database in 11.2.0.3 in AIX, to see the actual steps and how it gets loaded.

 
How to determine a patch can be applied online:

1. It should be mentioned in the readme and patch should have a online directory and under its subdirectories a .pch file

2. cod to the patch directory and run : opatch query -all online
   The output should contain the text "Patch is an online patch: true"
   
How to apply the patch online:

syntax 
   
$ORACLE_HOME/OPatch/opatch apply online -connectString :::,:::

I am using a single node command since I am using a single node command as below:

$ORACLE_HOME/OPatch/opatch apply online -connectString TST:sys:oracle

The output should contain text as below after successful application

Patching component oracle.rdbms, 11.2.0.3.0...
Installing and enabling the online patch 'bug17018214.pch', on database 'TST'.

Verifying the update...
Patch 17018214 successfully applied

How to verify the patch is applied

 Login via sqlplus as a sysdba and query vie oradebug, similarly enabling and disabling the patch can be done via oradebug, but when you want to remove the online patch it should be done via opatch. You can also check via the procmap command for the pmon process to see the shared libraries being loaded, you can use pmap for solaris and Linux.

SQL> oradebug patch list

Patch File Name                                   State
================                                =========
bug17018214.pch                                  ENABLED

I had issues with procmap, hence writing down a sample pmap output of the pmon process after the onilne patch is installed:

00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug17018214.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug17018214.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug17018214.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]

Also you can enable and disable the patch with the below commands.

SQL> oradebug patch disable bug17018214.pch
Statement processed.
SQL> oradebug patch list

Patch File Name                                   State
================                                =========
bug17018214.pch                                  DISABLED

SQL> oradebug patch enable bug17018214.pch
Statement processed.
SQL> oradebug patch list

Patch File Name                                   State
================                                =========
bug17018214.pch                                  ENABLED

 If you are wondering where the patch information is stored, its stored in $ORACLE_HOME/hpatch 
  
$ ls
bug17018214.pch            bug17018214.pchIFPT.fixup  bug17018214.so             orapatchIFPT.cfg

How to rollback the online patch

syntax 

opatch rollback -id -connectString  :::

The command in my single node database as below/;

$ORACLE_HOME/OPatch/opatch rollback -id 17018214 -connectString TST:sys:oracle

The output should contain text as below after succesfully revoking

The patch will be removed from database instances.
Disabling and removing online patch 'bug17018214.pch', on database 'TST'

RollbackSession removing interim patch '17018214' from inventory

Reference:
MoS Note Doc ID 761111.1 - RDBMS Online Patching Aka Hot Patching       

ORA-15260 and ORA-15027

I am writing this post to show the trivial mistakes we could do while transitioning from 10g/11gR1 ASM to 11gR2 ASM/Grid Infrastructure.Yes I did make it as well !
  
       $ sqlplus / as sysdba
     SQL> ALTER diskgroup DATA mount;
         ALTER diskgroup DATA mount
         *
         ERROR AT line 1:
         ORA-15032: NOT ALL alterations performed
         ORA-15260: permission denied ON ASM disk GROUP
 
   This is not because you have some wrong privileges for the disks or diskgroups but we have got to the old habit of logging in as sysdba to ASM instance.Login as sysasm and this will go away, Starting from Oracle 11g, SYSASM role should be used to administer the ASM instances.
  
  
         $ sqlplus / as sysasm
     SQL> ALTER diskgroup DATA mount;
   
         Diskgroup altered.
       
       
        When I tried to drop the data diskgroup I get this error ORA-15027, I did login as sysasm.
       
       
         $ sqlplus / as sysasm
    SQL> drop diskgroup DATA including contents;
       
         *
         ERROR at line 1:
         ORA-15039: diskgroup not dropped
         ORA-15027: active use of diskgroup "DATA" precludes its dismount
 
        The next obvious thing I do is to check the V$ASM_CLIENT to see the active sessions. But without any results.
       
        
        SQL> select * from v$asm_client;
          no rows selected
        
         The reason for this is that I have the spfile for the ASM instance in the same diskgroup and its used by the instance. I had to move the spfile out of the diskgroup , in my case out of ASM since this is the only diskgroup we have. The issue got resolved after this.
        
        
           SQL> create pfile='$ORACLE_HOME/dbs/init+ASM.ora' from spfile='';
     SQL> shutdown immediate;
     SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora
     SQL> drop diskgroup DATA including contents;

Flushing a single SQL from the shared pool

Many DBA's want to flush independent sql's when there is a situation to reparse the sql for testing or even at times to put a quick workaround for plan deviations. Traditionally we opt for flushing the shared pool, which at times could break another sql.So what could we do to avoid it, cant we flush one sql at a time ? there was a partial answer to this by Kerry Osborne by creating and dropping outlines.Which was great but dint work semlessly always (no other option if you are still stuck in a old version).


But if you are in 10.2.0.4 or above there is another winner in DBMS_SHARED_POOL.PURGE which does purge a single sql.

The DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release, but might not work if the db was upgraded from 10.2.0.X to 10.2.0.4. For 10.2.0.2/3 patch 5614566 can be installed to get this going.


The syntax for the procedure is as below and I will show more examples for different scenarios through this article.



DBMS_SHARED_POOL.PURGE (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)


SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
1384ubj5yw6d4 select * from dba_objects where rownum < 5 SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='1384ubj5yw6d4';

ADDRESS HASH_VALUE
---------------- ----------
000000019BFCB8E8 1273895332


SQL> alter session set events '5614566 trace name context forever'; ---> This is event protected in 10.2.0.4, not required for 11g

Session altered.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000019BFCB8E8,1273895332','C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='1384ubj5yw6d4';

no rows selected


Perfect that has flushed one sql now.


Before we close we will take a look at one other variant of this procedure to clear a sequnce from the shared pool which could find a use in the DBA's life.

SQL> create sequence test_seq start with 1 increment by 1 cache 5; ---> created a sequence with cache value 5

Sequence created.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
1

exec DBMS_SHARED_POOL.PURGE ('TEST_SEQ','Q')

PL/SQL procedure successfully completed.

SQL> select test_seq.nextval from dual; ---> since we flushed the sequence with cache value of 5, the sequence has jummped to six

NEXTVAL
----------
6


Hope you found this usefull...

ASM mount fails with ORA-15032 + ORA-15063

Development DBA's were using a single node Dell BOX for 11gR1 ASM , RDBMS and later decided to move it to two node RAC and hence deinstralled the existing software and had left the DB files as such so that they can use the same DB's after the the RAC setup. After the CRS,ASM and RDBMS were installed, they had 2 new disks to be added for the RAC nodes.

Using DBCA an asm instance was created and a diskgroup called DG1 was created with the 2 new disks, and one of the ASM instance's alert log started thowing the below error.My help was sought to see if anything was faced like this in production.


ERROR: diskgroup DG1 was not mounted
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DG1"
ORA-15038: disk '' size mismatch with diskgroup [1048576] [4096] [512]
ERROR: ALTER DISKGROUP ALL MOUNT

First check the disks in both the nodes :

Node - 1

SQL> select MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,NAME,PATH,TOTAL_MB,FREE_MB from v$asm_disk;

MOUNT_S HEADER_STA MODE_ST NAME PATH TOTAL_MB FREE_MB
------- ---------- ------- ------------------ -------------------------------------------------- ---------- ----------
CLOSED MEMBER ONLINE /dev/raw/raw3 0 0
CLOSED FOREIGN ONLINE /dev/raw/raw5 0 0
CLOSED MEMBER ONLINE /dev/raw/raw1 0 0
CLOSED FOREIGN ONLINE /dev/raw/raw2 0 0
IGNORED MEMBER ONLINE /dev/raw/raw4 0 0


Node - 2

SQL> select MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,NAME,PATH,TOTAL_MB,FREE_MB from v$asm_disk;

MOUNT_S HEADER_STA MODE_ST NAME PATH TOTAL_MB FREE_MB
------- ---------- ------- ------------------ -------------------------------------------------- ---------- ----------
CLOSED FOREIGN ONLINE /dev/raw/raw2 0 0
CLOSED FOREIGN ONLINE /dev/raw/raw5 0 0
CACHED MEMBER ONLINE DG1_0001 /dev/raw/raw3 547419 168410
CACHED MEMBER ONLINE DG1_0000 /dev/raw/raw4 547419 168257

Inference 1:

From the above details /dev/raw/raw4 was the old disk mounted in the old standalone ASM, which is not made visible in the new node.

Note: Also I could see all the files from the standalone DB synchronized in the second node DG1, this takes us closer to our issue.

From this I used kfed to check what the disk headers had to say

(Contenet shortened for better reading)

[oracle@wv1devdb03b dev]$ kfed read /dev/raw/raw1

kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: DG1_0000 ; 0x028: length=8
kfdhdb.grpname: DG1 ; 0x048: length=3
kfdhdb.fgname: DG1_0000 ; 0x068: length=8
kfdhdb.capname: ; 0x088: length=0

[oracle@wv1devdb03b dev]$ kfed read /dev/raw/raw4

kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: DG1_0000 ; 0x028: length=8
kfdhdb.grpname: DG1 ; 0x048: length=3
kfdhdb.fgname: DG1_0000 ; 0x068: length=8
kfdhdb.capname: ; 0x088: length=0


[oracle@wv1devdb03b dev]$ kfed read /dev/raw/raw3

kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: DG1_0001 ; 0x028: length=8
kfdhdb.grpname: DG1 ; 0x048: length=3
kfdhdb.fgname: DG1_0001 ; 0x068: length=8
kfdhdb.capname: ; 0x088: length=0

Yes now we know the issue, the disk '/dev/raw/raw1' was earlier mounted as diskgroup DG1 (and of course was not cleaned up) , this could have better if the new disks were created with a new diskgroup name viz., DG2. So the end issue is we have mismatching set of disks for DG1 in both the nodes and also we have two disks with the name as "DG1_000".

So what could be done to resolve this (dev setup gives me more liberty :-) ), in our case the below :

1. Commented the /dev/raw/raw1 and restarted node 1.
2. ASM now had same disks in both sides and it has come up fine.
3. We returned the old disk raw1 to the storage team.

What could be done to avoid this :

1. Mounted the raw1 disk on both the nodes.
2. Else could have created the diskgroup with a different name, very simple I guess.

Oracle 11G : root.sh fails with - Failure at final check of Oracle CRS stack. 10

I was setting up a Oracle 11G RAC in a two node Linux cluster and got into a issue while running the root.sh in the second node of the cluster as below:


/rdbms/crs/root.sh
Checking to see if Oracle CRS stack is already configured
/etc/oracle does not exist. Creating it now.

Setting the permissions on OCR backup directory
Setting up Network socket directories
Oracle Cluster Registry configuration upgraded successfully
clscfg: EXISTING configuration version 4 detected.
clscfg: version 4 is 11 Release 1.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: devdb03b devdb03b-priv devdb03b
node 2: devdb03a devdb03a-priv devdb03a
clscfg: Arguments check out successfully.

NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
Failure at final check of Oracle CRS stack.
10

After the error I was manually evaluating the basic setup was right, there were a couple of issues which were trivial and had escaped the clufy verification:

1. The private and virtual host names were commented in the /etc/hosts file in one node.
2. The time was not synced in both the nodes which could cause node eviction.

[oracle@devdb03b cssd]$ date
Thu Oct 28 10:49:38 GMT 2010
[oracle@devdb03a ~]$ date
Thu Oct 28 10:48:39 GMT 2010


After the required changes were done the installation was cleaned up following the following note and reinstalled.

Note: How to Clean Up After a Failed 10g or 11.1 Oracle Clusterware Installation [ID 239998.1]

Which still dint resolve the issue, after some more analysis on the trace dumps from the ocssd , we could see that the network heart beat was not coming through for some other reason like a port block or a firewall issue, checking the /etc/services and iptables confirmed it.

[ CSSD]2010-10-28 10:55:58.709 [1098586432] >TRACE: clssnmReadDskHeartbeat: node 1, devdb03a, has a disk HB, but no network HB, DHB has rcfg 183724820, wrtcnt, 476, LATS 51264, lastSeqNo 476, timestamp 1288262691/387864

OL=tcp)(HOST=wv1
devdb03b-priv)(P
ORT=49895))

iptables was enabled and had many restrictions, so after adding the following in the iptables and restarting the nodes (as in one node the crs restart was hanging forever).

In node devdb03a


ACCEPT all -- devdb03b anywhere

In node devdb03b


ACCEPT all -- devdb03a anywhere

After this the crs became healthy, but no resources were there.

This was due to the root.sh failure in the second node, to fix this the vipca was run as rot user from the first node and everything fell in place quickly, and all the vip,ons and gsd came up fine.

Oracle Netbackup restore to a different user/server

I am working on an environment where we have (Oracle RMAN + Netbackup) for our backup strategy, and today we had one of our APPS DBA seeking help for restoring the production backup to a different server (dev) in a different user.

For restoring in a different server it was straight forward as I had done it multiple times before, the solution is as below to send the name of the client which took the backup via NB_ORA_CLIENT parameter, which will let the netbackup client browse the backups taken from the production server (prd-bkp).

Note: the actual client here is prd-bkp

run
{
host "date";
allocate channel t1 DEVICE TYPE sbt_tape PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1' format '%d_dbf_%u_%t' ;
send 'NB_ORA_CLIENT=prd-bkp';
restore controlfile to '/upg04/FINDEV/control01.ctl' ;
set until time "to_date('05-10-2010 10:01:00','dd-mm-yyyy hh24:mi:ss')";
release channel t1 ;
debug off;
host "date";
}

Coming to the second issue, where we have to restore the file to a different user, I had to work to analyze the issue and from the help of the backup admin I pulled out the log files from the netbackup master server and could see the following message


07:21:46.888 [6581] <2> db_valid_master_server: dev-bkp is not a valid server
07:21:46.933 [6581] <2> process_request: command C_BPLIST_4_5 (82) received
07:21:46.933 [6581] <2> process_request: list request = 329199 82 oradev dbadev prd-bkp dev-bkp
dev-bkp NONE 0 3 999 1281405910 1284084310 4 4 1 1 1 0 4 7230 9005 4 0 C C C C C 0 2 0 0 0
07:21:46.947 [6581] <2> get_type_of_client_list_restore: list and restore not specified for dev-bkp
07:21:46.947 [6581] <2> get_type_of_client_free_browse: Free browse allowed for dev-bkp
07:21:46.948 [6581] <2> db_valid_client: -all clients valid-
07:21:46.949 [6581] <2> fileslist: sockfd = 9
07:21:46.949 [6581] <2> fileslist: owner = oradev
07:21:46.949 [6581] <2> fileslist: group = dbadev
07:21:46.949 [6581] <2> fileslist: client = prd-bkp
07:21:46.949 [6581] <2> fileslist: sched_type = 12

The reason being the backup was done from oracle user - dba group, restore was tried from oradev user - dbadev group. Here the user and group has to match for the restore to succeed. Changing the existing setup to a production like user was not possible because we had users like oradev,oratst in the box which are all going to have copies from production.

Finally we could see that since Netbackup 6.0 MP4 we could read the backup images if the groups of the users were same, voila we were in 6.5. This change was possible for us to do with the help of the sysadmin without compromising on security.By doing so the backup went fine the solution was as below:

1.Stop the oracle instance runnign in dev.
2.Changed the group of oradev from dbadev to dba.
3.Changed the binaries ownership to oradev:dba.
4.Start the oracle instance with the oradev user.
5.Restart the backup

Now we had a smooth restore and below was the log from the Netbackup master server.


07:53:11.923 [9423] <2> db_valid_client: -all clients valid-
07:53:11.924 [9423] <2> fileslist: sockfd = 9
07:53:11.924 [9423] <2> fileslist: owner = oradev
07:53:11.924 [9423] <2> fileslist: group = dba
07:53:11.924 [9423] <2> fileslist: client = prd-bkp
07:53:11.924 [9423] <2> fileslist: sched_type = 12.

Oracle Virtual Index

Oracle has the undocumented feature which helps to test if a specific index creation will be used by a query to improve performance during a tuning exercise. This is a fake index hence its very fast to do the initial test and also does not occupy space, given below is a very simple example on testing the virtual index.



1.Create a test table for the activity.

create table test as select * from dba_objects;

2.Collect the statistics for the table

EXEC DBMS_STATS.gather_table_stats(user, 'TEST')

3.Check the plan nfor the query

explain plan for
select * from test where object_id=200;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 196 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 196 (1)| 00:00:03 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

1 - filter("OBJECT_ID"=200)

13 rows selected.

4.Create a virtuala index with nosegment clause.

create index test_idx1 on test(object_id) nosegment;

5.Alter session to use the virtual index

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

6.Collect statistics for the table
Note: The statistics will not be populated for the index.

EXEC DBMS_STATS.gather_table_stats(user, 'TEST', cascade => TRUE);


7.Check the virtual index exists
Note: The information is not populated in DBA_INDEXES but in DBA_OBJECTS

SQL> SELECT o.object_name AS fake_index_name FROM user_objects o
WHERE o.object_type = 'INDEX' AND NOT EXISTS
(SELECT null FROM user_indexes i WHERE o.object_name = i.index_name );

FAKE_INDEX_NAME
------------------------------------------------------------------------
TEST_IDX1


8.Check the plan to see if the index is used.

explain plan for
select * from test where object_id=200;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2624864549

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=200)

14 rows selected.


9.Drop the virtual index and create a real one if it helps.

drop index test_idx1;

Port/Move Sql Profiles in 10gR1

Today I was working on a migration project, where I had to port some sql profiles from 10gR1 to 10gR2 which was really critical to the performance of the application. Metalink has documentation to move the profiles from 10gR2 to 10gR2, but nothing for my requirement 10gR1.

Wihhout any documented information finally I was able to find some undocumented information.Thanks to Christian Antognini's papers which helped me to workaround my issue.Here is what I did to port the sql profiles:

SQL Profile consists of auxiliary statistics specific to that statement and are stored as profile attributes in the data dictionary, which can be retrieved from the two tables SQLPROF$ and SQLPROF$ATTR.

In the source I have a profile called 'SYS_SQLPROF_091112150758738', which can be retrieved as below:

select sp.sp_name, sa.attr#, sa.attr_val
from SQLPROF$ sp, SQLPROF$ATTR sa
where sp.signature = sa.signature
and sp.category = sp.category
and sp.sp_name = 'SYS_SQLPROF_091112150758738'
order by sp.sp_name, sa.attr#

SP_NAME ATTR# ATTR_VAL
------------------------------ ---------- ----------------------------------------
SYS_SQLPROF_091112150758738 1 FIRST_ROWS(1)
SYS_SQLPROF_091112150758738 2 OPTIMIZER_FEATURES_ENABLE(default)

Now I have the attributes from the source, which can be imported as a profile into the destination using the import_sql_profile procedure of the dbms_sqltune package as below:

exec dbms_sqltune.import_sql_profile(name => 'SYS_SQLPROF_091112150758738',description => 'SQL profile created for porting the profile from 10gR1',category => 'DEFAULT',sql_text => 'select XXXXXXXXX from XXXXXXX where XXXXXXXXX and XXXXXXXXXXX',profile => sqlprof_attr('FIRST_ROWS(1)','OPTIMIZER_FEATURES_ENABLE(default)'),replace => FALSE,force_match => FALSE);

After the import I wanted to make sure of two things :

1. The profile is used in the statement.
2. I have the optimal plan as I had in the source DB.

Both can be checked with the explain plan as below:

explain plan for
select XXXXXXXXX from XXXXXXX where XXXXXXXXX and XXXXXXXXXXX;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

I had a similar plan and also could see the below in the explain plan which says the profile is used.I am not sure if this is supported but did work for me.

Note
-----
- SQL profile "SYS_SQLPROF_091112150758738" used for this statement


Note: You can follow metalink document ID 457531.1 for 10gR2 : [How To Move SQL Profiles From One Database To Another Database ] for 10gR2

For people who do not have access to metalink below are the steps for migrating the profiles in 10Gr2:

In the source database :

1. Create a staging table for the profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'PROF_BR',schema_name=>'SYS');

2.Pack the profiles to the staging table

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090806080427252');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_081029060743314');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090806054340984');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090812082621064');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090819052716720');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_091112150758738');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_091206044219682');


3.Export the table

expdp directory=POC1 dumpfile=PROF_BR.dmp logfile=PROF_BR.log JOB_NAME=EXP_PROF_BR tables=SYS.PROF_BR


In the destination database :

4. Import the staging table

impdp directory=POC1 dumpfile=PROF_BR.dmp logfile=PROF_BR.imp JOB_NAME=IMP_PROF_BR tables=SYS.PROF_BR


5.Unpack the profiles

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'PROF_BR');

6. Test the queries plan to see the usage of the sql profile.

Linux high memory utilization

Today I was working on a project when the tester said the memory utilization was close to 100% always.First I was surprised as this was a pure DB box (RH 4) and has a single instance using only 50% of the memory for the Db.

As usual the first thing I did was to check the TOP/vmstat command, but this does not give the correct picture as Linux uses the spare memory available for caching the disk blocks (yes might be useful for a slow storage - extra cache) which is also accounted in the TOP command.


TOP:

Mem: 49433916k total, 47201932k used, 2231984k free, 903556k buffers
Swap: 70894792k total, 4k used, 70894788k free, 22948700k cached

VMSTAT:

vmstat 2 4
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 3 4 2250296 903688 22950204 0 0 1149 38 1 0 1 1 95 3 0
1 4 4 2249804 903688 22950204 0 0 39225 27 13506 35171 3 2 86 10 0
1 3 4 2250604 903688 22950204 0 0 42305 61 14293 35447 3 2 85 11 0
0 3 4 2250836 903688 22950204 0 0 29921 96 10224 25105 3 1 85 11 0



So in Linux we have to read the cached value carefully to calculate the actual memory used or the easier way is to cuse the free -m command as below.Here 22795 is the actual memory used and 25479 is the free memory.


free -m
total used free shared buffers cached
Mem: 48275 46090 2185 0 882 22412
-/+ buffers/cache: 22795 25479
Swap: 69233 0 69233

Linux HugePages for Oracle

HugePages is a feature in the Linux kernel with release 2.6. This feature provides the alternative to the 4K page size providing bigger pages, and is a very useful feature when we have a big SGA configured for the Oracle database.


To setup HugePages, the following changes must be completed:

Set the vm.nr_hugepages kernel parameter to a required value.

In this test case we will use a 20GB SGA , we can calculate the vm.nr_hugepages as below

Assuming we have only one instance running in the box, and also we have a 2MB hugepage for Linux x86_64.

(20480 MB [SGA] + 260 [buffer+ reserve for other applications to use shared memory] )/2 MB = 10500

and from the above derivation we set the value as below:

sysctl -w vm.nr_hugepages=10500


/etc/securities/limits.conf must be updated to increase soft and hard memlock values for oracle userid.

oracle soft memlock 20971520
oracle hard memlock 20971520

After setting this up, we will test to see if SGA is using HugePages.

The value, (HugePages_Total- HugePages_Free)*2MB will be the approximate size of SGA.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1536M
sga_target big integer 1536M

SQL> alter system set sga_max_size=20g scope=spfile sid='*';

srvctl stop database -d test
srvctl start database -d test

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 20G
sga_target big integer 1536M

$ cat /proc/meminfo |grep HugePages
HugePages_Total: 10500
HugePages_Free: 10297
HugePages_Rsvd: 10038

SQL> alter system set sga_target=20g scope=both sid='test2';

$ cat /proc/meminfo |grep HugePages
HugePages_Total: 10500
HugePages_Free: 818
HugePages_Rsvd: 559

Note : When started up using sqlplus the hugepages is not being used, bu twhen started up using srvctl it works.

[Metalink Reference HugePages on Linux: What It Is... and What It Is Not... [ID 361323.1]]
[Metalink Reference Shell Script to Calculate Values Recommended HugePages / HugeTLB Configuration [ID 401749.1]]

Oracle 10132 trace

Almost all of the DBA's use the 10046 trace and many use 10053 trace, one of traces not often used is the 10132 trace.This is generally used to generate the execution plan for the hard parses.Which can be enabled at the system level to get/ baseline the plans for the hard parsed statements after the event is set.We can also use it for session level as a shorter version of 10053 trace.

And most importantly the trace file is well formatted and easily readable even without using any utility like tkprof.


The event 10132 can be enabled and disabled in the following ways:

Enable and disable the event for the current session.

ALTER SESSION SET events '10132 trace name context forever'
ALTER SESSION SET events '10132 trace name context off'

Enable and disable the event for the whole database.

Note: this setting does not take effect immediately but only for sessions created after the modification.

ALTER SYSTEM SET events '10132 trace name context forever'
ALTER SYSTEM SET events '10132 trace name context off'


Sample output:

*** SERVICE NAME:(SYS$USERS) 2009-11-25 10:23:15.898
*** SESSION ID:(2160.19196) 2009-11-25 10:23:15.898


Current SQL statement for this session:
SELECT so_status_name
FROM job_queue jq,
so_job_queue sjq
WHERE jq.module = :sModule
AND jq.arg_3 = :sUser
AND jq.arg_4 = :sFile
AND jq.arg_5 = :sDate
AND jq.jobid = sjq.so_jobid
Plan Table
--------
-------------------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | | | 6 | | | | | | |
| NESTED LOOPS | | 1 | 48 | 6 | | | | | | |
| TABLE ACCESS FULL | JOB_QUEUE | 1 | 33 | 5 | | | | | | |
| TABLE ACCESS BY INDEX ROWID | SO_JOB_QUEUE | 1 | 15 | 1 | | | | | | |
| INDEX UNIQUE SCAN | SO_JOB_QUEUE_IDX | 1 | | | | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------

How to upload huge files to Oracle support

Oracle's ftp site can be used to do the job by following the below steps:

Move the file to your local machine and tar/zip them appropriately.

Open a command window.

Start ftp:
C:\T> ftp
ftp>

Connect to the oracle ftp server and connect as user anonymous:
ftp> open ftp.oracle.com
User (bigip-ftp.oracle.com:(none)): anonymous

Use as a password a valid email address used in metalink:

331 Please specify the password.
Password:
230 Login successful.

Move into the directory "support/incoming":

ftp> pwd
257 "/"
ftp> cd support/incoming
250 Directory successfully changed.

Create a new directory with the name of your service request number:
ftp> mkdir
257 "/support/incoming/" created
ftp> cd
250 Directory successfully changed.

Set the transfer mode to binary:
ftp> bin
200 Switching to Binary mode.

Put the files on the ftp server:
ftp> put

Use the command pwd to check the current directory:
ftp> pwd
257 "/support/incoming/"

REMARK: For security reasons the command ls cannot be executed

Exit from ftp site:
ftp> quit

Inform Support that the file has been uploaded in the following location:
ftp://ftp.oracle.com/ftp/anonymous/support/incoming//filename

vnetd + Oracle RMAN restore from tape using NetBackup

I had a unique issue when working on a retore using rman, the issue was the restore was started with 10 channels and the restore was happening with one channel and the other channels were not reading any data and finally failed with “cannot connect socket” error from netbackup side.

The rman side failed with a generic MML error as below.

ORA-19624: operation failed, retry possible
ORA-19507: failed to retrieve sequential file, handle="AAAPRD_dbf_sokhhtgt_689501725", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file


This issue went for a day or two and finally with the help of a system admin we resolved the issue.


The issue was like below :

1.The database server failed to establish more vnetd connections from master/media servers of Netbackup.

1. vnetd is used by default from NBU 6.0 and up. So NetBackup uses the firewalled configuration even if you do not have a firewall.

2. If vnetd fails NBU failback to normal deamon port connection (bpcd, bpbrm etc).


This was finally fixed by changing the per_source value in /etc/xinetd.conf from 10 to UNLIMITED after whcih all the channels running fine.

Oracle 10.1.0.5 database opened with 10.2.0.4 home

We had a case where a dba had opened a 10.1.0.5 database with a 10.2.0.4 Home and with the init parameter compatible=10.2.0.4 by mistake.

After that the dba attempted to start the db using 10.1.0.5, which resulted in the following error as the compatibility information had been written allover
the controlfiles, datafiles etc.


/rdbms/v10.1/dbs >sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 4 05:37:07 2009

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1323752 bytes
Variable Size 164351256 bytes
Database Buffers 436207616 bytes
Redo Buffers 2097152 bytes
SQL> @ /tmp/ctrl.sql
CREATE CONTROLFILE SET DATABASE "RETRDEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01130: database file version 10.2.0.4.0 incompatible with ORACLE version
10.1.0.4.0
ORA-01110: data file 1: '+DG8_DEV/retrdev/datafile/system.256.629212191'



Since compatibility parameter cannot be rolled back in this case, we dint have any backups as this was a developement environment.To workaround this we had

to do the following:

1.Upgrade the database to 10.2.0.4
2.Export the database.
3.Recreate a 10.1.0.5 database and import it.

So we have to be carefullwhicle settign such parameters.

Oracle 10g RAC check/modify private interconnect information

Query the private interconnect information from the database:

SQL> select * from gv$cluster_interconnects ;

INST_ID NAME IP_ADDRESS IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
1 eth0 192.168.124.186 NO OS dependent software

Query the private interconnect information using oifcfg:

$ oifcfg getif
eth1 192.168.128.0 global public
eth2 192.168.127.0 global cluster_interconnect

Check the interconnect information from the alert log

And also the alert log will throw the information during start up just after displaying the non-default init parameters:

open_cursors = 300
pga_aggregate_target = 73400320
Cluster communication is configured to use the following interface(s) for this instance
192.168.124.186


You can check the available interfaces using

$ oifcfg iflist
eth0 192.168.124.0
eth1 192.168.128.0
eth2 192.168.127.0
ib1 10.192.2.0



To change the setings follow the below steps:

delete the wrong settings

oifcfg delif -global eth1/192.168.128.0
oifcfg delif -global eth2/192.168.127.0



oifcfg setif -global eth0/192.168.124.0:public
oifcfg setif -global ib1/10.192.2.0:cluster_interconnect

The new settings would take effect after the database bounce.


Note: The crs picks the interconnect information from the /etc/hosts and the database picks from the configuration of oifcfg.

How to recreate a undo tablespace

There was an issue in the undo tablespace , where the size of one of the datafiles in the undo tablespace(ASM) did not match the size in the control file and I did the folowing to recreate the undo tablespace:

1. Make sure the database was last cleanly shut down.

sqlplus /nolog
SQL>connect sys/change@db as sysdba
SQL> shutdown immediate

2. mount database in RESTRICT mode, using pfile.

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora
ORACLE instance started. Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.

3. Try to offline drop the bad datafile.

SQL> ALTER DATABASE DATAFILE 'C:\ORADATA\DB\UNDOTBS2_02.DBF' OFFLINE DROP;

*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

or this SQL:

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

4. Use this query to see how many rollback segments were corrupted:

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17$ NEEDS RECOVERY UNDOTBS2
_SYSSMU18$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19$ NEEDS RECOVERY UNDOTBS2
_SYSSMU20$ NEEDS RECOVERY UNDOTBS2

5. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

Make sure you uncomment “undo_management=AUTO”, and specify you want to use UNDOTBS1 as undo tablespace.

#undo_management=AUTO
undo_tablespace=UNDOTBS1

6. Start the database again:

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora


7. Drop bad rollback segments


SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
…

SQL> drop rollback segment "_SYSSMU20$";
Rollback segment dropped.

8. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————
SYSTEM ONLINE SYSTEM
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1
_SYSSMU21$ ONLINE UNDOTBS1

9. Now drop bad undo TABLESPACE UNDOTBS2;

SQL> drop TABLESPACE UNDOTBS2;

10. Recreate the undo rollback tablespace with all its rollback segments

SQL>CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'C:\oradata\DB\UNDOTBS01.DBF' SIZE 2000M reuse AUTOEXTEND ON ;


11. Change undo tablespace

ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;

12. Remove the following line from pfile

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16 $','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

and uncomment “undo_management=AUTO”

undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

13. Shutdown database

SQL>shutdown immediate;

14. Edit initCRM_18.ora, make sure you change ‘undo_tablespace=UNDOTBS2″ to “undo_tablespace=UNDOTBS1″, then start oracle database:


sqlplus /nolog
SQL>connect sys/change@db as sysdba
SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

15. Create Undo tablespace:

SQL>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'C:\oradata\db\UNDOTBS02.DBF' SIZE 2000M reuse AUTOEXTEND ON ;
SQL>DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

16. Startup database with spfile

SQL> startup;
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

Oracle 10g CLONE FROM RAC TO NON-RAC (RMAN)

Database

Target database – PRD10G => This is the source database in RACRecovery Catalog – RMCAT10G (in Machard) => This is the catalog database for RMANAuxillary database(New DB) – TST => This is the target database Non-RAC
Note: Backups are in tape , MML is Netbackup

Steps

1. The first step would be to create a auxillary instance, which will be in no-mount state.To prepare a instance, create a pfile from the spfile of PRD10G.
Eg. Create pfile=’/tmp/initprd.ora’ from spfile;

2. Then edit this pfile to remove all the parameters denoting the Cluster, in our case I commented it as below;
#tst1.__db_cache_size=260046848#tst2.__db_cache_size=260046848....#*.cluster_database=true#*.remote_listener='LISTENERS_tst'*.background_dump_dest='/rdbms/v10.1/admin/tst/bdump'*.compatible='10.1.0.4.0'*.control_files='+DG4_DEV/tst/controlfile/backup.277.629493875','+DG_DEV/tst/controlfile/backup.264.629493875'*.core_dump_dest='/rdbms/v10.1/admin/tst/cdump'*.user_dump_dest='/rdbms/v10.1/admin/tst/udump'*.db_block_size=8192*.db_create_file_dest='+DG4_DEV' ===> Modify it accoring to the file system or ASM diskgroup in the destination server....*.sessions=170*.sga_target=471859200*.undo_management='AUTO'undo_tablespace='UNDOTBS1'
3. Create a password file to login remote to the auxillay instance.

4. Add entries to the Listener.ora file and also add the prd and rmcat10g entries to the tnsnames.ora if they are not available.

5. Create any directories specified in the pfile eg.bdump,udump etc.

6. Use this pfile to startup the new instance in nomount mode
Startup new instance to check if all parameters are correct and for further RMAN operations
SQL> show parameter cluster_database
NAME TYPE VALUE------------------------------------ ----------- ------------------------------cluster_database boolean FALSEcluster_database_instances integer 1
SQL> show parameter thread
NAME TYPE VALUE------------------------------------ ----------- ------------------------------thread integer 0

7. Check the location of the target database datafiles so that we can specify the new names for them, in our case:
SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DG4/prd/datafile/system.260.629141721+DG4/prd/datafile/undotbs1.276.629141721...+DG4/prd/datafile/usermgr_admin.256.629142617
12 rows selected.

8. Connect to RMAN with – PRD as target, RMCAT10G as catalog and TST as Auxiliary instances.

9. From the catalog RC_% Views find the time or SCN that you want to duplicate, In our case it is up to a specific time.

10. Then run a run block similar to this:
run{allocate auxiliary channel c1 device type sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';allocate auxiliary channel c2 device type sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
duplicate target database to 'tst' db_file_name_convert=('+DG4/prd/datafile/','+DG4_DEV/tst/datafile/')logfilegroup 1('+DG4_DEV/tst/onlinelog/redo01.log') size 100M,group 2 ('+DG4_DEV/tst/onlinelog/redo02.log') size 100MUNTIL TIME "to_date('2007-08-08:07:00:00','YYYY-MM-DD:HH24:MI:SS')" ;
release channel c1;release channel c2;}

This will clone a database called tst from prd as a non-rac database.

Happy cloning.

Enabling Trace in Oracle

Tracing a session is a imporatant phase of a problem analysis for a query,job in Oracle.There are various methods of enabling trace for sessions.Following are a few methods for enabling trace.
To check if any trace is enabled in the current database, the following query can be used:
select * from DBA_ENABLED_TRACES ;

1. Enable trace at instance level
Start trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = TRUE;
Stop trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = FALSE;

2. Enable trace for the current session
Start trace:
ALTER SESSION SET sql_trace = TRUE; (or)EXECUTE dbms_session.set_sql_trace (TRUE); (or)EXECUTE dbms_support.start_trace;Stop trace:
ALTER SESSION SET sql_trace = FALSE; (or)EXECUTE dbms_session.set_sql_trace (FALSE); (or)EXECUTE dbms_support.stop_trace;

3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
Start trace:
EXECUTE DBMS_MONITOR..start_trace_in_session (SID, SERIAL#);
With Waits and Binds
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE);
With Waits
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE, binds=>TRUE);

Stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

Oracle 10g - RMAN Block change tracking

There are performance issues associated with incremental backups where most of the times we have issues with the CPU/IO being consumed more. In Oracle 10g it is possible to track changed blocks using a change tracking file.

Prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup. Oracle 10g new feature, block change tracking, minimizes number of blocks RMAN needs to read to a strict minimum. With block change tracking enabled RMAN accesses on disk only blocks that were changed since the latest base incremental backup.

Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups.

The current change tracking status can be displayed using the following query:
SELECT status FROM v$block_change_tracking;

Change tracking is enabled using the ALTER DATABASE command:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/ts1/oradata/block_change_track.dbf';

The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.

Change tracking can be disabled using the following command:
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Renaming or moving a tracking file can be accomplished in the normal way using the ALTER DATABASE RENAME FILE command. If the instance cannot be restarted you can simply disable and re-enable change tracking to create a new file. This method does result in the loss of any current change information.

Background Process – Change Tracking Writer (CTWR). This process takes care of logging information about changed blocks in block change tracking file.

Increasing the Speed of Export

Exporting data is a common day to day activity done by most of the DBA's, and when it comes to speeding up the Export jobs these are few tips on it :

• Use Direct Path – Direct path exports (DIRECT=Y) allow the export utility to skip the SQL evaluation buffer, whereas the conventional path export executes SQL SELECT statements. With direct path, the data is read from disk into the buffer cache, returning rows directly to the export client. This can offer substantial performance gains, depending on the actual data. When using the direct path, the recordlength parameter should also be used to optimize performance.

• Use Subsets – By subsetting the data using the QUERY option, the export process is only executed against the data that needs to be exported. If tables have old rows that are never updated, the old data should be exported once, and from that point only the newer data subsets should be exported. Subsets cannot be specified with direct path exports since SQL is necessary to create the subset.

Note: Use a par file for the query as it can help reduce a lot of formatting on the command.

• Use a Larger Buffer – For conventional path exports, a larger buffer will increase the number of rows that are processed between each physical write to the export file. Fewer physical writes equals greater performance. The following formula can be used to determine a proper buffer size:
buffer size = rows in array * max row size

• Separate Tables – Separate those tables that require consistent=y from those that don’t, in order to expedite the export. This way, the performance penalty will only be incurred for those tables that actually require it.
For the table with one million rows, the following benchmark tests were performed using the different export options.

• Indexes=No - This will reduce the time taken to export the indexes which is worth creating after the import.

• Set a higher value for the recordlength parameter - Specifies the length of the file record in bytes. This parameter affects the amount of data that accumulates before it is written to disk. The highest value is 64KB.