TIP 114# : All about RAC

If you are starting with new RAC setup or you have a RAC environment and you are looking for performance tuning, stabilization of the environment or even upgrade, the following document comes very handy :

RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) (Doc ID 810394.1)

TIP 106# : Quick tips on Cloning Oracle

As Oracle clone is one of the quickest way to create new software binaries with all the patches from a source database and it comes handy specially when Oracle software installation media is not available. In this post, I am trying to remind myself and readers some tips about Oracle clone which worth to know:

Cloning is not cross platform
Clone usually is done in 2 phases - prepare_clone.pl in source and clone.pl in target
Clone does take care of inventory and relink so further patches can be applied on the new cloned home
For Oracle database and CRS, prepare_clone.pl is not required
ORACLE_HOME and ORACLE_HOME_NAME should be specified when clone.pl is run, in 11g, ORACLE_BASE should be specified also
If Oracle inventory is not in default location, before running clone.pl, edit cs.properties and add 

-ignoreSysPrereqs -invPtrLoc <oraInst_path>/oraInst.loc

Make sure to run root.sh after clone.pl is completed

You may need to create oraInst.loc file manually on target if the file does not exists, to do so, just create a file in default location 
   with inventory_loc=<oraInventory_path>

The alternative of running clone.pl is to use runInstaller for cloning, the format would be like this : 

./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="" ORACLE_HOME_NAME="" ORACLE_BASE="" 

(in Windows it would be setup.exe)

If the server has more than one perl installed, it is recommended to set PERL5LIB 

If clone is done on target server which has inventory and the cloned ORACLE_HOME has already been used at somepoint,

first ORACLE_HOME needs to be de-attached using this command : ./runInstaller -detachHome ORACLE_HOME=

This is why it is recommended to clone in brand new directory to avoid any issue.

Clone log in target is created in $ORACLE_HOME/clone/logs

Enjoy cloning!

TIP 98#: Oracle Restart

I was working on a database maintenance couple days ago and interesting enough, as it is shown in below, I noticed that after killing listener, it is started up somehow...
Environment has 11202 standalone database on Linux 32bit using ASM.

[oracle@localhost bin]$ ps -ef |grep tns
oracle 4365 1 0 15:29 ? 00:00:00 /home/oracle/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 4370 3513 0 15:29 pts/1 00:00:00 grep tns
[oracle@localhost bin]$ kill 4365
[oracle@localhost bin]$ ps -ef |grep tns
oracle 4373 3513 0 15:29 pts/1 00:00:00 grep tns
[oracle@localhost bin]$ ps -ef |grep tns
oracle 4375 3513 0 15:29 pts/1 00:00:00 grep tns
[oracle@localhost bin]$ ps -ef |grep tns
oracle 4385 1 0 15:30 ? 00:00:00 /home/oracle/app/oracle/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 4394 3513 0 15:31 pts/1 00:00:00 grep tns


This turned out to be due to Oracle Restart
For those of you who are not familiar, starting 11gR2, ASM as well as Oracle Restart are installed out of Grid Infrastructure home.
Oracle Restart provides managed startup and restart of a single-instance (non-clustered) Oracle Database, Oracle ASM instance, service, listener, and any other
process running on the server.Oracle Restart is used in standalone server (non-clustered) environments only. For RAC, Clusterware provides this functionality.

This explains why listener was started up automatically, So now that we know why, the question is how to stop different components in 11gR2 for single instance DB ?

Oracle recommends to use srvctl to stop/start different components.
However, tools such as sqlplus, lsnrctl, ascmd are integrated with Oracle Restart and in other words, if any component is stopped/started via these tools, Oracle Restart will not try to intervene.
Also if there is a case like maintenance/patch which DBA wants to keep some components down and does not want Oracle Restart to intervene, the best option is to disable Oracle Restart before starting maintenance. To do so, following below steps:



-------- To stop/disable Oracle Restart --------
cd $GI_HOME/bin
./crsctl stop has
./crsctl disable has
-------- To start/enable Oracle Restart --------
cd $GI_HOME/bin
./crsctl enable has
./crsctl start has




For more information about Oracle Restart and how to bounce DB in 11g, please check out : Oracle® Database Administrator's Guide,11g Release 2 (11.2),Part Number E25494-02111

TIP 94#: Good to know/be reminded of

I was reading couple articles from Tom Kyte in Oracle magazine and I found it would be great to mention some here for reminder/reference ...

Fast Full Index Scan va Full Index Scan
*****************************************
FFI reads the entire index, unsorted. It is called as Tiny version of table. (If you want to make a tiny version of table on some columns, it could be an option as long as you have at least one NOT NULL column). FFI uses Mutiblock IO and it reads the entire index including leaf blocks, branches and root and just ignore branches and root.
FIS however, reads single block, it starts from root and goes down to branche and the leaf blocks and then when it hits the leaf blocks, it reads the bottom of the index.

Null values in bitmap indexes
***********************************
Unlike B*Tree indexes, bitmap indexes always index NULL values.Every row in table is indexed by bitmap index not matter if the value is NULL or NOT NULL

Wide table
***************
If you have a wide table but couple columns are in use not all, in order to improve the performance of fetching records, the following two options can be considered :

Option1
---------
Create index on all necessary columns and Oracle does use FFIS as tiny version of table.

Option2
---------
Break down table to two table, one with most frequent used columns and one with less frequent used columns. Have a view on these two tables and use the view, Whenever most frequent used columns are accessed, Oracle automatically elimiate the second table.

Basic sample :

maintab (col1, col2, col3, col4, col5)
tab1 (col1, col2, col3)
tab2 (col1, col4, col5)
view as select col1,col2,col3, col4,col4 from tab1, tab2 where tab1.col1=tab2.col1


DDL in trigger
*******************
It is wrong to use any DDL in trigger as DDL does implicit commit. Still with pragma autonomous_transaction, it is wrong to use DDL in trigger. If there is no choice, it is better to submit DDL as a job instead of executing DDL in trigger..

Easy Connect
*****************
10g on wards, allows to use easy connect to connect to DB. For easy connect, in sqlnet.ora we should have EZCONNECT in NAMES.DIRECTORY_PATH. If this setting is in place, easy connect can be used in this way - //host:port/db

TIP 91# : Refresh of materialized view erros with overflow

When I showed up in office couple weeks ago, a client called and complained that a materialized view refresh failed, this refresh has been working with no errors in years. The client confirmed that there was no code change in the materialized view or any underlying tables.Refreshing the materialized view manually also failed in couple second with the same error :


ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 1



The errors first looked like it was due to data in underlying tables which can not be fit in the defined materialized view. As a test, I captured the select statement for the materialized view and populated it in a temporary table to see if any column data types in temporary table is different from the materialized view column data type.
Interesting enough, all columns in temporary table and materialized view were the same in terms of data type and the length.So it can not be data type overflow ?!!!
Tracing the refresh (10046) did not resolve anything.

Eventually found that the issue was not the materialized view by itself but it was due to overflow of dba_tab_modifications which keeps track of insert/update/delete for each object.
Since the materialized view is refreshed on regular basis, Oracle 's number which keeps track of changes were high for the materialized view and when the refresh was done they were overflowed. Metalink ID 882997.1 implicitly reported this issue.

Oracle released a one-off patch for this issue but in interim, gathering stats on the materialized view should reset the number in the dictionary and then refresh should run in success :



select inserts, deletes from sys.mon_mods_all$ where obj# = &object_id;

INSERTS DELETES
---------- ----------
4295948847 4283835810

exec dbms_stats.gather_table_stats('@owner','&mv_name',estimate_percent=>5,cascade=> false);
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
select inserts, deletes from sys.mon_mods_all$ where obj# = 189467;

select inserts, deletes from sys.mon_mods_all$ where obj# = &object_id;

no rows



For your info, this issue was reported in 11gR1 (11.1.0.7)

TIP 76#: How to find peeked value of bind variables

Oracle usually recommends to be sensitive in using bind variables. Starting Oralce 9i, Oracle introduced a concept of bind variable peeking. This means that the first time a SQL statement containing bind predicates is parsed, the optimizer will look at the value of the bind variable and use that value for creating the execution plan for the query.
This plan is then stored and used for all future executions regardless of changed bind values. If the initial bind value is not representative of the other values that are provided in future executions of the query, then, although the access path is good for the intial value, future executions may perform poorly. This may result in suboptimal query performance for some iterations of the query.

As I said, bind variable peeking is TRUE by default in 9.0 and above. This feature could be disabled with setting _OPTIM_PEEK_USER_BINDS to FALSE.

In this post, I am trying to demonstrate how to find the peeked value for bind variable, This helps in the case of poor performance to see if Oracle execute query based on the right value. (Sample was run in 10.2.0.3)

Test 1
In this test, a query with bind variable was run against test table.
When bind_data column in v$sql is not null,SQL has bind variables.
v$sql_bind_capture is used to find which bind value has been peeked by Oracle.



Test 2
In this test, new value (100) was passed to the same query but as it is shown Oracle still has -1 as a peek value.


Test 3
In this test, shared pool was flushed from test table. This forced Oracle to peek again the value. This time it peeked 100.

TIP 74# : Can not run startup nomount

After an upgrade from 10.2.0.2 to 10.2.0.3 for a client, I encountered with the following error :

SQL> startup nomount
ORA-00940: invalid ALTER command

Interestingly, after removing spfile and creating a pfile with simple option, the issue was resolved.
It turned out to be because of a hidden parameter which disable/enable a bug in 10.2.0.2 database which does not exist in 10.2.0.3. Parameter was _fix_control=5385629:on which turns on the fix for bug 5385629 in 10.2.0.2 but since this bug does not exist in 10.2.0.3, having this parameter in spfile caused a problem as Oracle can not enable this bug in 10.2.0.3.

Please be advised to make sure spfile is good and healthy and it has not this parameter if you get this error.

TIP # 68 : Export hangs, Shutdown immediate hangs !!!

One of my client is running Oracle 9i (9.2.0.7) and Oracle 10g (10.2.0.3) On Solaris 64 bit in the same box.Oracle 10g is mainly used for recovery catalog and Grid repository.
All my efforts to take export of recovery catalog schema faild.With tradition export tool (exp), export hangs in exporting "Cluster Definition". Data pump export/import (expdp and impdp) runs forever.
Data pump sessions were waiting on "wait for unread message on broadcast channel" which is idle wait.Export sessions were waiting on cursor: pin S wait on X.In order to fix the issue, I just turned off mutex by seting hidden parameter. (I will explain in next post about mutex in 10g database).
To make this change effective, database should be bounced.
Surprisingly, shutdown immediate also hung, alert log reported Oracle was waiting for DBWx (Database Writer) to be shut down !!!!.
I ended up to shutdown abort database and startup it again though bouncing database sis not fix the situation.
The first export after bouncing database failed with ORA-0600 in alert log and with the following error lines in exporting cluster definitions.

EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00000: Export terminated unsuccessfully

All of the next runs of exp and expdp hung same as before bounce.
Eventually, I found that problem was because of improper setting of LD_LIBRARY_PATH and LIBPATH.This parameter needs to be set to point to $ORACLE_HOME/lib in 10g as first reference. Setting those parameter properly fixed the situation including shutdown problem and exp/expdp problem. (For more info, please refer to metalink note : 351650.1).

TIP # 66: IPC error after applying patch or relinking Oracle

One of my client faced with a strange error after applying security patch and relinking Oracle.
Client could not startup database and got the following error.

SQL> startup nomount;

ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 59
ORA-27301: OS failure message: Message too long
ORA-27302: failure occurred at: sskgxpsnd1

SQL>

Oracle was not able to allocate memory, Alert log had nothing regarding to this issue.
After some research in metalink, I found metalink note (Note : 300956.1) which recommends to bump up the following parameters.

# no -o tcp_sendspace=262144
# no -o tcp_recvspace=262144
# no -o udp_sendspace=65536
# no -o udp_recvspace=262144
no -o rfc1323=1

Increasing those values did not fix the issue however, more details were shown.
This time after startup,the following message was shown.

SQL> startup nomount;
ORA-29702: error occurred in Cluster Group Service operation

Also alert log had more info :

Cluster communication is configured to use the following interface(s) for this instance
165.123.81.30
Fri Dec 7 10:35:10 2007
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
PMON started with pid=2, OS id=630922
DIAG started with pid=3, OS id=651416
PSP0 started with pid=4, OS id=360672
LMON started with pid=5, OS id=647294
LMD0 started with pid=6, OS id=163956
MMAN started with pid=7, OS id=581878
DBW0 started with pid=8, OS id=614592
LGWR started with pid=9, OS id=626764
CKPT started with pid=10, OS id=643140
SMON started with pid=11, OS id=499806
RECO started with pid=12, OS id=659522
CJQ0 started with pid=13, OS id=663620
MMON started with pid=14, OS id=569480
MMNL started with pid=15, OS id=671816
Fri Dec 7 10:35:12 2007
USER: terminating instance due to error 29702
Instance terminated by USER, pid = 618564

It seems that for some reasons after relinking Oracle, Oracle considered binary installation as RAC install.
As the result, I turned off RAC option with running the following command :

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle

This resolved the issue and I was able to startup database without any problem.

TIP #65 : 10g listener.

Today, a client came up with a question on whether or not password protected listener is safer than non-password protected listener in 10g.
To answer, In Oracle 10g, listener is secure by itself and there is no need to set a password for listener as in older version to protect listener.
By default, listener uses local OS authentication which means that only the user who owned listener can admin it. This feature is enabled by default.
If you run lsnrctl status in 10g, you should see any line like the following in output:

STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File

In above example, If listener is started as Oracle user and user X attempts to admin it or Oracle user from a different node attempts to admin it, the following error will appear.

TNS-01190: The user is not authorized to execute the requested

On the other hand, if a password is set for 10g listener, all users who know the password can admin listener.For the password protected listener in 10g, the result of lsnrctl status would be something like this :

STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Password or Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File

To conclude, if only user who started listener is allowed to admin listener, you do not need to set password for 10g listener.Listener by itself is protected and the only user who can admin the listener is listener owner.To me, it seems to be more restricted.
However, if you want other users to admin listener, you still need to have password protected listener. All users who knows password can run admin command for listener.
To me, it seems less restricted.

TIP # 64 : How to determine bind variable value and type from trace file.

In TIP 48 , I explained on how to make more sense of trace which is generated by event 10046.
One of our reader asked a question on how to identify data type of bind variable, As the result I dedicated this post to answer.

For finding more information about bind variable, user should be able to locate BINDS keyword in trace file.
This part is something like this :

BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"

dty determines bind variable type and value shows bind variable value at the time of execution.
Different values can be assigned to dty which presents different data type.


Typical dty value is :

1 VARCHAR2 or NVARCHAR2
2 NUMBER
8 LONG
11 ROWID
12 DATE
23 RAW
24 LONG RAW
96 CHAR
112 CLOB or NCLOB
113 BLOB
114 BFILE


To demosnstrate, I ran some queries.

------ Query #1 : Number Bind variable


SQL> alter session set statistics_level=ALL;

Session altered.

SQL> alter session set max_dump_file_size=UNLIMITED;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> variable b number;
SQL> declare
2 cnt number;
3 begin
4 :b:=1;
5 select count(*) into cnt from dba_objects where object_id=:b;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

Session altered.


Checking trace file ......


PARSING IN CURSOR #2 len=52 dep=1 uid=0 oct=3 lid=0 tim=2797155817 hv=1220784193 ad='130a9d4c'
SELECT count(*) from dba_objects where object_id=:b1
END OF STMT
PARSE #2:c=15625,e=24270,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=0,tim=2797155809
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=24 offset=0
bfp=030a7b2c bln=22 avl=02 flg=05
value=1
EXEC #2:c=0,e=2062,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2797159524
FETCH #2:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2797159885



------ Query #2 : Varhchar2 Bind variable


SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> variable b varchar2(30);
SQL> declare
2 cnt number;
3 begin
4 :b:='A';
5 select count(*) into cnt from dba_objects where object_name=:b;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';



Checking trace file .....



PARSING IN CURSOR #2 len=54 dep=1 uid=0 oct=3 lid=0 tim=3007778755 hv=2029951970 ad='12ff2c80'
SELECT count(*) from dba_objects where object_name=:b1
END OF STMT
PARSE #2:c=15625,e=12991,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,tim=3007778748
BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"
EXEC #2:c=0,e=2167,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3007782524
WAIT #2: nam='db file sequential read' ela= 9458 p1=1 p2=89 p3=1
WAIT #2: nam='db file sequential read' ela= 6606 p1=1 p2=26791 p3=1

TIP # 63 : DIRECT Load and redo log generation

I had a client who uses "direct load" for its nightly job on productiton database.Suprisingly many archive logs were generated during job run.The whole purpose of "Direct load" is to improve performance by gnerating less archive logs.
However, in this case does not see any difference.
I am trying to explain on how Direct load would help.

Some facts :
1.The undo would normally be used to un-insert the rows in the event of a
failure or rollback - with DIRECT LOAD, undo is not necessary since the new rows are added entirely above the high water mark for the table.
2. When "Direct load" (/*+ append */) is used, Oracle can skip undo generation for the TABLE data - but not on the indexes.
3. In archivelog mode , REDO is normally generated with "Direct load" (/*+ APPEND */), it is UNDO that is skipped and then only for the table itself.
4.If table is placed into "nologging" mode (or use nologging in insert
command), then redo for the table as well as undo can be skipped..Again, only for the table - not for any indexes on the table itself.
5. Small redos which are generated in nologgin/DIRECT LOAD is used to protect the data dictionary.
6. To prevent archivelog generation in "Direct load", database and tablespace should not be in "Force logging mode". (Check v$database and dba_tablespaces).

Considering above facts, my first answer to a client was: "Archives are generated because of indexes on tables".
To demonstrate, Please follow below simple steps :

========== Table without index in Direct load ===========

SQL> create table tbl2 as select * from dba_objects where 1=2;

Table created.


--- Checking redo and undo before direct load


select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
0 redo size

select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------------------------
19.78125

---- Direct load


insert /*+ append */ into tbl2 nologging select * from dba_objects;

select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
7068580 redo size

select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------------------------
25.78125



Generated redo : 7068580 bytes
Generated undo : 6 MB


========== Table with index in Direct load ===========



SQL> create table tbl1 as select * from dba_objects where 1=2;

Table created.

SQL> create index idx1 on tbl1(object_name);

Index created.

SQL> create index idx2 on tbl1(owner);

Index created.


---- redo log before direct load


select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
0 redo size

select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------
25.78125


------- Direct load



SQL> insert /*+ append */ into tbl1 nologging select * from dba_objects;

62908 rows created.


------ Redo log and Undo after direct load.



select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
28251320 redo size

select sum(undoblks)*8192/1024/1024 from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------------------------
42.5078125




Generated redo : 28251320 bytes
Generated undo : 17 MB

More archives and more UNDOs in direct load on table with indexes.


Sum up :
=========
To use direct load and to take advantage of less archivelog generation and better performance, always consider the followings :
1. Disable indexes during direct load.
2. Make sure to use both /*+ append */ with nologging at the same time.
3. Make sure database and tablespace are not in nologging mode.

TIP #56 : Invalid username/password; logon denied

I faced with this issue when I was trying to login to database remotely with sys user.
First of all, I was suspicious to the password of sys user.
Checking with client, sys password was right but remote login to DB was not possible.
I found that REMOTE_LOGIN_PASSWORDFILE is NONE in database which means priviliged users can not login remotely to database.
In order to give permission to sys use to access to database remotely, this parameter should not be NONE.
For doing that, the following steps is recommended.

- Shutdown database.
- Move database password file (In Unix on $ORACLE_HOME/dbs) and create a new password file.
orapwd file= password= entries=
- Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE
- Startup database

Other possible reasons of this error are :

- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started

TIP 54# : "Cannot open or remove a file" warning when applying patch

I faced with strange warning yesterday when I tried to upgrade Oracle application server from 10.1.2.0.2 to 10.1.2.2. Oracle Universal Installer, all of sudden stopped and showed me the following error :

Error in writing to file $ORACLE_HOME/lib32/libnjssl10.so
Cannot open or remove a file containing a running program


Patch was running on AIX platform and as Oracle recommendation before starting patchset, I ran /usr/sbin/slibclean as root which is supposed to clean box from program which got file descriptor.
This experience showed me that slibclean is not always perfect and there may be some odd situations which other remedies are required.
Here is what I did to get over the situation.
- cp $ORACLE_HOME/lib32/libnjssl10.so $ORACLE_HOME/lib32/libnjssl10.so.org
- cp $ORACLE_HOME/lib32/libnjssl10.so.org $ORACLE_HOME/lib32/libnjssl10.so
Copy did not fix the situation

- mv $ORACLE_HOME/lib32/libnjssl10.so $ORACLE_HOME/lib32/libnjssl10.so.org
-mv $ORACLE_HOME/lib32/libnjssl10.so.org $ORACLE_HOME/lib32/libnjssl10.so
Move fixed the situation

As the result :
- On AIX , first try /usr/sbin/slibclean as root.
- On all platforms, if file exists try to move it to new file and then move it back to original.

TIP 53# : Exclude option in data pump on Window.

As all know, filtering option in datapump (10g version of traditional export/import) is very powerful.
I just wanted to use this option and exclude a table from whole schema export per client `s request.A client is running 10g database on Windows.
Regarding to Oracle documentation and expdp help=y, it is supposed to be as simple as adding exclude=table:table_name.

However, I got the following errors when I ran it.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE_NAME_EXPR filter is badly formed.
ORA-00920: invalid relational operator



I tried option with the following syntax :

exclude=TABLE:"= 'TABLE_NAME'"
exclude=TABLE:TABLE_NAME
exclude=TABLE:" IN ('TABLE_NAME') "

No luck. Got the same error.
Finally, I figured out that window can not parse " and need to add escape seuqence.
So exclude option on windows should have syntax like this :


exclude=TABLE:\"='TABLE_NAME'\"

TIP 52# : Tablespace map

Sometime for solving tablespace fragmentation issue, it is better off to have a better picture of tablespace allocated and free area.
In this post, I wrote a PL/SQL script which shows mapping of free space and used space in tablespace.This script helped me out to resolve tablespace fragmentation issue for a client.Analyzing the result of script would end up to detecting offending objects and relocating them.

Script details
------------------

CREATE OR REPLACE PROCEDURE mapts (target_ts VARCHAR2,min_extents NUMBER DEFAULT 8) IS
 cur_block_id NUMBER;
 prev_block_id NUMBER;
 cur_blocks NUMBER;
 prev_blocks NUMBER;
 map_str CLOB;
 alloc_str CLOB;
 tmp NUMBER;
 file_id_v NUMBER;
 CURSOR mycur IS select segment_name,blocks,block_id,EXTENT_ID from dba_extents where TABLESPACE_NAME=Upper(target_ts) AND file_id=file_id_v order by block_id,EXTENT_ID;
 myvar mycur%ROWTYPE;
BEGIN
 FOR rec IN (SELECT file_id FROM dba_data_files WHERE tablespace_name=Upper(target_ts)) LOOP
  file_id_v:=rec.file_id;
  cur_block_id:=1;
  prev_block_id:=1;
  prev_blocks:=0;
  OPEN mycur;
  LOOP
   FETCH mycur INTO myvar;
   EXIT WHEN mycur%NOTFOUND;
   cur_block_id:=myvar.block_id;
   cur_blocks:=myvar.blocks;
   IF(prev_block_id+ prev_blocks= cur_block_id) THEN
    FOR i IN 1..round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/min_extents);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   ELSE
    FOR i IN 1..Round((cur_block_id-prev_block_id-prev_blocks)/min_extents) LOOP
     map_str:=map_str||'-'|| '*';
     alloc_str:=alloc_str||'-'|| '*';
    END LOOP;
    FOR i IN 1..Round(myvar.blocks/min_extents) LOOP
     alloc_str:=alloc_str||'-'|| '+';
    END LOOP;
    tmp:= trunc(myvar.blocks/8);
    map_str:=map_str||'-'|| myvar.segment_name;
    map_str:=map_str||'('||To_Char(tmp)||')';
    prev_block_id:=cur_block_id;
    prev_blocks:=cur_blocks;
   END IF;
   --Dbms_Output.put_line(map_str);
  END LOOP;
  INSERT INTO object_place_in_ts values(target_ts,file_id_v,alloc_str,map_str,sysdate);
  COMMIT;
  alloc_str:=NULL;
  map_str:=NULL;
 END LOOP;
 CLOSE mycur;
END;
/

Script needs the following table to be existed in database.

Table : object_place_in_ts
Table description:

Name Null? Type
----------------------------------------
TABLESPACE_NAME VARCHAR2(30)
ALLOC_PATTERN CLOB
OBJ_PATTERN CLOB
CRE_TIME DATE

Alloc_pattern : String pattern which allocated extents are shown with + and free extents are shown with *
Obj_patten : String pattern which allocated extents have name of object and free extents are shown with *.

Example :

tablespace_name allocated_pattern object_pattern
------------------------------------------------------------------------------
users ++*+++ -obj1(2)-*-objx(1)-objy(1)-objx(1)

In this example, two first extents have been allocated, third extent is free and fourth and fifth and sixth extents have been allocated . (Check allocated_pattern).
First two extents have been allocated by obj1, fourth and sixth extents have been allocated by objx.fifth extent has been allocated by objy.

I found this result and analysis very handy to resolve tablespace fragmentation.

TIP #50: Opatch failed after uncompleted/interrupted patch

I faced with a problem today during my AS 10g upgrade which involves applying different patches sequentially.
Opatch failed with the following message :

Problems with the lock file
Lock file exists, details are:
Interim Patch is holding the lock from xxxxxxx,
probably due to previous unsuccessful operation

ERROR: OPatch failed during pre-reqs check

Opatch creates patch_locked in $ORACLE_HOME/.patch_storage during running of patch.If for some reason opatch run was interrupted or canceled , this file may not be removed.As the result, further opatch run can find this patch and will fail because of this file. Solution is to delete this file.

TIP # 48 : Reading 10046 trace.

As I mentioned in TIP #38 post, there are ways to reading and formatting 10046 trace files.However, in some situation DBAs may choose to read details in trace file by themselves.Besides this may be more true if translator software has some bugs.

The followings is a guideline about the meaning of different section in 10046 trace file.

----------------------------------------------------------------------------
APPNAME mod='%s' mh=%lu act='%s' ah=%lu
---------------------------------------------------------------------------- APPNAME Application name setting. This only applies to Oracle 7.2
and above. This can be set by using the DBMS_APPLICATION_INFO
package. See Note 30366.1.

mod Module name.
mh Module hash value.
act Action.
ah Action hash value.

----------------------------------------------------------------------------
PARSING IN CURSOR # len=X dep=X uid=X oct=X lid=X tim=X hv=X ad='X'

END OF STMT
---------------------------------------------------------------------------- Cursor number.

len Length of SQL statement.
dep Recursive depth of the cursor.
uid Schema user id of parsing user.
oct Oracle command type.
lid Privilege user id.
tim Timestamp.
Pre-Oracle9i, the times recorded by Oracle only have a resolution
of 1/100th of a second (10mS). As of Oracle9i some times are
available to microsecond accuracy (1/1,000,000th of a second).
The timestamp can be used to determine times between points
in the trace file.
The value is the value in V$TIMER when the line was written.
If there are TIMESTAMPS in the file you can use the difference
between 'tim' values to determine an absolute time.
hv Hash id.
ad SQLTEXT address (see and ).

The actual SQL statement being parsed.

----------------------------------------------------------------------------
PARSE ERROR #%d:len=%ld dep=%d uid=%ld oct=%d lid=%ld tim=%lu err=%d
...
----------------------------------------------------------------------------
PARSE ERROR In Oracle 7.2+ parse errors are reported to the trace file.

len Length of SQL statement.
dep Recursive depth of the statement
uid User id.
oct Oracle command type (if known).
lid Privilege user id.
tim Timestamp.
err Oracle error code (e.g. ORA-XXXXX) reported

The SQL statement that errored. If this contains a password,
the statement is truncated as indicated by '...' at the end.

----------------------------------------------------------------------------
PARSE #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
FETCH #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
UNMAP #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
----------------------------------------------------------------------------- OPERATIONS:

PARSE Parse a statement.
EXEC Execute a pre-parsed statement.
FETCH Fetch rows from a cursor.
UNMAP If the cursor uses a temporary table, when the cursor is
closed you see an UNMAP when we free up the temporary table
locks.(Ie: free the lock, delete the state object, free the
temp segment)
In tkprof, UNMAP stats get added to the EXECUTE statistics.
SORT UNMAP
As above, but for OS file sorts or TEMP table segments.

c CPU time (100th's of a second in Oracle7 ,8 and 9).
e Elapsed time (100th's of a second Oracle7, 8
Microseconds in Oracle 9 onwards).
p Number of physical reads.
cr Number of buffers retrieved for CR reads.
cu Number of buffers retrieved in current mode.
mis Cursor missed in the cache.
r Number of rows processed.
dep Recursive call depth (0 = user SQL, >0 = recursive).
og Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose
tim Timestamp (large number in 100ths of a second). Use this to
determine the time between any 2 operations.

----------------------------------------------------------------------------
ERROR #%d:err=%d tim=%lu
---------------------------------------------------------------------------- SQL Error shown after an execution or fetch error.

err Oracle error code (e.g. ORA-XXXXX) at the top of the stack.
tim Timestamp.

----------------------------------------------------------------------------
STAT # id=N cnt=0 [pid=0 pos=0 obj=0 op='SORT AGGREGATE ']
----------------------------------------------------------------------------
STAT Lines report explain plan statistics for the numbered .

Cursor which the statistics apply to.

id Line of the explain plan which the row count applies to (starts
at line 1). This is effectively the row source row count
for all row sources in the execution tree.
cnt Number of rows for this row source.

As of 7.3.3 the items in '[...]' are also reported:

pid Parent id of this row source.
pos Position in explain plan.
obj Object id of row source (if this is a base object).
op='...' The row source access operation.

These let you know the 'run time' explain plan.

----------------------------------------------------------------------------
XCTEND rlbk=%d rd_only=%d
---------------------------------------------------------------------------- XCTEND A transaction end marker.

rlbk 1 if a rollback was performed, 0 if no rollback (commit).
rd_only 1 if transaction was read only, 0 if changes occurred.

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

======================================================================
The items below are only output if WAITS or BINDS are being traced.
These can be enabled with the DBMS_SUPPORT package.
======================================================================

----------------------------------------------------------------------------
BINDS #%d:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=24 offset=0
bfp=02fedb44 bln=22 avl=00 flg=05
value=10
----------------------------------------------------------------------------
BIND Variables bound to a cursor.

bind N The bind position being bound.
dty Data type (see ).
mxl Maximum length of the bind variable (private max len in paren).
mal Array length.
scl Scale.
pre Precision.
oacflg Special flag indicating bind options
oacflg2 Continuation of oacflg
size Amount of memory to be allocated for this chunk
offset Offset into this chunk for this bind buffer

bfp Bind address.
bln Bind buffer length.
avl Actual value length (array length too).
flg Special flag indicating bind status
value The actual value of the bind variable.
Numbers show the numeric value, strings show the string etc...

It is also possible to see "bind 6: (No oacdef for this bind)", if no
separate bind buffer exists.

----------------------------------------------------------------------------
WAIT #: nam="" ela=0 p1=0 p2=0 p3=0
----------------------------------------------------------------------------
WAIT An event that we waited for.

nam What was being waited for .
The wait events here are the same as are seen in
. For any Oracle release a full list of
wait events and the values in P1, P2 and P3 below can be seen
in
ela Elapsed time for the operation.
p1 P1 for the given wait event.
p2 P2 for the given wait event.
p3 P3 for the given wait event.

Example (Full Table Scan):
WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25

WAITing under CURSOR no 1
for "db file scattered read"
We waited 0.05 seconds
For a read of: File 4, start block 1435, for 25 Oracle blocks

Example (Index Scan):
WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1

WAITing under CURSOR no 1
for "db file sequential read"
We waited 0.04 seconds for a single block read (p3=1)
from file 4, block 1224

TIP # 47 :Consideration in granting SELECT to public

I posted this log because of the situation that I faced with for a client last week.I created a user in 10g database (10gR2) and granted only connect privilege.I was suprised when I Logged in with a new user and checked out all tables which are accessible by user (select * from user_tables) . Users had privileges to select from some tables in other schema while user had only connect privilege.

After digging up, I found out that these privileges are because of select privileges which have been granted to PUBLIC user. The lesson is that any privilege which is granted to public, is granted to all users.As the result, in order to prevent security hole, granting privileges to PUBLIC should be restricted.

Above all, a known bug or it is better to say a big security hole may exists in DBs which select privilege has been granted to PUBLIC. In Database without latest CPU patch, users in database can run any DML commands on tables which only select provilege has been granted to public . (Security Hole !!!) .

The following shows the sample :

create user test identified by test;
create user test1 identified by test1;
grant resource,connect to test;
grant connect to test1;

--------- Create a table in test user and grant select to publi
cconnect test/test
create table test_sec (id number primary key,id2 number);
grant select on test_sec to public;

--------- Connect to second user .
connect test1/test1

desc test.test_sec
Name Null? Type
---------------------------
ID NOT NULL NUMBER
ID2 NUMBER

select * from test.test_sec; <===== user can select because select was granted to user

insert into test.test_sec values(1,1);

ORA-01031: insufficient privileges <======= User can not insert because insert was not granted

===========Bug ============== test1 can insert into test.test_sec if using the following syntax :

insert into (select a.id,a.id2 from (select * from test.test_sec)a inner join (select * from test.test_sec )b on (a.id=b.id) ) values (1,2);

1 row inserted. <========== User can insert while it has not any privilege to do that

select * from test.test_sec;

ID ID2
------------------
1 2

Bottom line is that avoid granting privileges to public users or have a monitoring of granted privileges to public users to prevent from unexpected permission.

TIP #45 : Performance tuning PL/SQL with profiler.

Oracle provides dbms_profiler for tracing PL/SQL code and finding any performace bottlenecks.
This post shows how to use it.
For using dbms_profiler, first it should be installed.

Install dbms_profile and populate tables.

- @?/rdbms/admin/profload.sql (Install dbms_profiler)
- @?/rdbms/admin/proftab.sql (Populate profiler tables).

How to trace :

- exec dbms_profiler.start_profiler('Sample1');
- Run PL/SQL code (it would be better to be proc/func/package instead of anonymous code.
- exec dbms_profiler.stop_profiler;
- exec DBMS_PROFILER.FLUSH_DATA;

How to see the result :

- Which runid is for current trace
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;

- Total elapsed time of each PL/SQL code.


SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = &1
ORDER BY u.unit_number, d.line#;

FYI : Also putting dbms_utility.get_time(); in PL/SQL code would show exact run time execution of each PL/SQL pieces.