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

One of my client is running Oracle 9i ( and Oracle 10g ( 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 #67 : Most recent asked questions

As you may notice, I opened a new section in my blog couple weeks ago which allows readers to ask any technical question in Oracle database administration or Oracle application server administration. This open up gate for reader to ask me what they want to see in this blog.
I should say that I got good reception from readers which encourage me to keep this section. In this post I gathered 10 latest questions plus answers.
To mention that answers are mostly prepared by myself and a friend of mine who is senior Oracle DBA.
Feel free to send me questions or post comments on questions/answers.

Question 10

How to reset report server password


1. Check httpd.conf in %ORACLE_HOME%\Apache\Apache\conf
Find if you have something like the followings :

AuthUserFile {path}\pwfile
Require user {username}

2. Take a copy of file in AuthUserFile.
3. Change password with running the following command :

%ORACLE_HOME%/apache/apache/bin/htpasswd -c pwfile username???
(username??? is value {username} from step 1

4. Restart Apache. New password should be effective.

This works if report server is not using OID for its authentication.

Question 9

How to find out who created (I mean OS user) a trigger in oracle database.?


ctime in sys.obj$ shows creation time of trigger.
With having object creation time, you have the following options to find OS user :

1. Using Audit file/table if Audit has been enabled in database.
2. Using listener.log to find out who connected to database at that time. (This worked if user connected to database via listener).
3. Check system log/message( May be SA can help you) to figure out who logged in during that period.
4. If session is still active and shared pool has not aged out 'create trigger' statement, you can use v$sqlarea and v$session to find out OS user. (However, I put it as low chance)

I ran comprehensive test of Log miner and for sure with logmining archive log file at trigger creation time, you can see changes in sys.source$ but you can not get any changes in v$session or its fixed table (X$KSUSE). Since changes on this table goes to memory.As the result, I do not see any viable solution except above options. Later, you may need to think of enabling Audit or having logon trigger if you `d like to catch this kind of activities.

Question 8

How can I reset the HWM in 9i without truncating the table or exp/imp? I have HP openview message history tables that become quite large during a data surge but drop off to very few records later.


I suggest to use DBMS_REDEFINITION. Here is simple sample to demonstrate on how to reset HWM on scott.tbl1.

create table tbl2 asselect * from tbl1 where 1=2;
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'tbl1', 'tbl2', NULL, 2);----- Copy dependenciesDeclareblk_out pls_integer ;
blk_out := 0 ;

Question 7

We have 1 master site and 2 replication site.I try to recreate whole materialize view at one site and didnt touch other site. Both sites were and are in FAST refresh mode.The one which was NOT recreated now giving me error ORA-12034 materialized view log on %S% %T%younger than last refresh.I tried complete refresh on the other site (which was not recreted )it was successful but could not able to perform fast refresh.


I asked my friend who is Senior DBA and has experience in replication to answer this.Here is an excerpts from his thought :
"It seems that during the recreation of the first mv site (perhaps through replication managergui), the materialized view log on the master site is recreated and as a result its creation time is later than the mtimeof the MVs in the second site. If this is the problem,it should be resolved after a complete refresh on the second site and should not persist any more. If you are not able to do the fast refresh even after a complete refresh, please provide me some more information so I could simply recreate scenario ".

Also I would suggest to make sure that certain DDLs have not been run on master site. Certain DDL can modify master tables but can not log in materialized view. (alter partition, drop partition).

Question 6

sqlplus /nolog
conn and /as sysdba
this gets me in to the sys account without knowing its password. Any way to force me to always use a password?


If OS user is part of Oracle group, your OS user is allowed to login to database without any password.Two options to change this situation :

1. Change OS group of your OS user to be different from primary group of Oracle software installation.
2. Change $ORACLE_HOME/rdbms/lib/config.[cs] file and define new DBA group which is different from your OS user group. (example : define SS_DBA_GRP "mygroup"). You need to relink Oracle executable to take this change into account. (See metalink note : 18089.1)

Question 5

How can I see the parameters getting passed to a procedure, which is called from the application using trace files?


You basically have many options. Here are some:

1. If values are passed as a literal, you can check v$sqlarea to find out SQL text.This way you are able to see full SQL statement with value of parameters. (Example : select SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%PROC1%'; -- Replace PROC1 with procedure name)

2. If values are passed as bind variables, you could enable 10064 event to trace session.

3. You could also write a wrapper on top of procedure to print out/save parameter values and then call main procedure.

Question 4

How to call a executable file (i.e) *.exe from a oracle stored procedure is that possible ?


Yes, it is possible. However I should confess that it is not as easy as normal PL/SQL code.
You can achieve that either with External Procedure call and C language or with Java language.
Long story short, you need to have a Java code to execute .exe file with exec and you need to have a PL/SQL wrapper function for this Java code. After granting different permission to database user, user would be able to execute .exe (executable) from PL/SQL code.
Hope this answers your question.

Question 3

I was wondering, though, about matching sql to index used. Not sure what tables to query. This would be helpful when examining index performance. Currently I find current sql in memory and check the where clause.


You need to check an execution plan of SQL statement to see whether or not it uses any index.If database is 10.2 onwards, the simplest way is to just run SQL statements and get the plan with the followings :

SQL> set linesize 150
SQL> set pagesize 2000
SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

If database is not 10.2 onwards, the simplest way is to explain sql statement :

explain plan for {sql_statement}

Obviously, there are some other ways to check this situation, please let me know if you are interested for more details.

Question 2

I need to display,fetch whole data from one of my column which is in CLOB.,i tried but it displays only some characters only .,Not full data., wht should i do ?


Just run the following. (Text?? should be replaced with CLOB column and table? should be replaced with table name)

buffer varchar2(32000);
buffer_size integer := 32000;
offset integer := 1;
length number(8);
myvar mycur%ROWTYPE;
OPEN mycur;
FETCH mycur INTO myvar;
length := dbms_lob.getlength(myvar.TEXT);
while offset <>
dbms_lob.read(myvar.TEXT, buffer_size, offset, buffer);
offset := offset + buffer_size;
end loop;
CLOSE mycur;

Question 1

I need query assist :I want to fetch records (for exp.15th jun 2007 to 15th nov 2007) from oracle 10g DB date wise records...???


Assumptions :
table_name : tbl1
data_field : f1_date with DATE type

select * from tbl1 where trunc(f1_date,'DD')