TIP 112# : Quick tips on Weblogic

Very often I login to a customer site and I end up to search in document for couple things :

• How to setup NodeManager so the customer could use Admin console for Weblogic startup
• How to make username/password encrypted so the command line does not ask for username/password at time of startup
• Quick and easy guideline for patching
• How to check Weblogic version

Above items are nothing new but I thought to post details here for my quick access.This post is a quick reminder for myself which is copied from Oracle document.

To enable startup scripts and start Node Manager:

1.   Navigate to the following directory

2. MW_HOME/oracle_common/common/bin

MW_HOME is the directory where Oracle Fusion Middleware is installed.

3.   Run the setNMProps.sh script to set the StartScriptEnabled property to true before starting Node Manager:

4. ./setNMProps.sh

This is a one-time action. After you run this script, you can skip this step before starting Node Manager again.

5.   Start Node Manager with the startNodeManager script.

UNIX script: WL_HOME/server/bin/startNodeManager.sh

Windows script: WL_HOME\server\bin\startNodeManager.cmd

To avoid username/password at startup:

To avoid prompts for a user name and password on startup after you start a Managed Server the first time, you can create a boot.properties file in the domain-home/servers/server-name/security/ directory. This file would include the following lines:
username=USERNAME

password=PASSWORD

The boot.properties file will be encrypted the first time that the Managed Server is started.

Patching mechanism on Weblogic :

Basically bsu is the tool to apply the patches on Weblogic install. The following shows an example on how patch is applied and how it is verified.

To apply
copy patch to $MW_HOME/utils/bsu/cache_dir
unzip patch
remove patch zip file (rm p1......zip)
./bsu.sh -install -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=FCX7 -prod_dir=$WL_HOME
To verify
./bsu.sh -view -status=applied -prod_dir=$WL_HOME

Checking Weblogic version:

cd $DOMAIN_HOME/bin
[oracle@oracle2qa bin]$ . ./setDomainEnv.sh
[oracle@oracle2qa APEXDomain]$ java weblogic.version

TIP 97#: Apache2 in AIX

I was working for a client to upgrade the existing AS10g to Fusion 11g. As part of upgrade, Apache is upgraded to version >2. Interesting enough, starting Apache under port 1024 threw the following error although the regular permission change was applied.


[2012-05-15T10:27:53.6378-04:00] [OHS] [INCIDENT_ERROR:20] [OHS-9999] [mod_onsint.c] [host_id: comet.isc-seo.upenn.edu] [host
_addr: 165.123.81.34] [pid: 5308510] [tid: 1] [user: root] [VirtualHost: main] (22)Invalid argument: setgid: unable to set group id to Group 4294967295


This turned out to be due to the fact Apache under port 1024 can only be started as a named user/group other than nobody.In other words, checkout httpd.conf and make sure user and group is set first of all and it is set to anything but nobody.As I mentioned, Apache in AS10g can still be started without this setting but not anymore in Fusion11g!

I have not tested if this is the same case on other platforms, please feel free to chime in.

TIP 82# : Browser craches when Oracle Forms is accessed

If you use internet explorer (Any version) and it crashes at time of accessing Oracle Forms, the following step could resolve the issue.


- Disable 3rd party browser extension (Tools -> Internet Options -> Advanced -> Browsing)
- Deinstall all Jinit versions. (Optional but preferred)
- Cleanup IE cache
- Close IE broswer
- Open IE and access URL


I have tested this with AS 10R2 (10.1.2.3) and IE 6 and IE7 SP2.

TIP 81# : Oracle Forms and new Verisign certificate

After renewing Verisign certificate, you may see an issue with Oracle Forms when it is accessed. I have seen it couple weeks ago after a client renewed its Verisign certificate for Oracle AS 10.1.2.3 : Client could access web server via SSL (e.g. https://servername:port/ was reachable) while accessing the Form server ended with Java exception and SSL handshake failed error message.(e.g. https//servername:port/forms/frmservlet?config=jpi)
I was noticed that Verisign has introduced a two-tier CA hierarchy for Standard SSL Certificates (Called chained cetrtificate sometimes) which changed the old way of having only a root certificate. With this method, Verisign provides Root certificate and also intermediate certiificate.It is interesting to know that Verisign has not been issued any ceritificate since Oct2008 in the old fashion.

Unfortunately, the latest Oracle Jinitiator (despite metalink 456658.1) can not handle new Verisign fashion and if Forms server uses Jinitiator, you may see Java exception and Handshake failure when Forms is accessed. Jinitiator 1.3.1.29 and later (at time of writing this blog, the latest is 1.3.1.30) can not handle the latest intermediate since Verisign keeps changing the intermediate certificate and as Jinitiator support is ended by Jan 31th,2010 (https://support.oracle.com/CSP/main/article?cmd=show&id=761159.1&type=NOT), it does not seem Oracle tries to catch up with the Verisign change.

Based on the environment and diversity of clients, I do recommend the following options :

Option 1 : 
  Migrate from Jinitiator to Java Plug-in (1.5)
OR
Option 2 : 
  Migrate to at least Jinitiator 1.3.1.29 
  Copy intermediate file to cretdb.txt on each client box




(File is located on {Jinit install folder}\security\lib. (Please be informed that only upgrading jinitiator to the latest version may not work).

TIP 80 : AS10g maintenance/upgrade experience

If you are using Oracle application server 10gR2, you may notice that oracle does not provide any CPU patch for the default 10gR2 release until AS environment is patched up to higher version.
In other words, no CPU patch is realesed for 10.1.2.0.2 application server (default AS 10g R2 release). Regarding to metalink note 420061.1 , all application server should be patched up to 10.1.2.2 which all new CPU patches are based on.
As the result, I went throght upgrading application server from 10.1.2.0.2 to 10.1.2.2 for a client.

Here is my experience in this rocky road.

Client AS topology
=====================
A client is using 10gR2 application server on AIX 64bit.
Metarepository database is on 10gR2 database (created by Repca) and is on the same box of infrastructure tier.
Mid tier is on separate box with BI-Form full install and SSL-Enabled.

Patches
===============
Regarding to metalink note 415222.1, the following is the roadmap of upgrade to new 10.1.2.2 which would be more or less valid for similar AS environment



1. Apply 4960210 to Mid tier. (Software update)
2. Apply 4960210 to Infra tier. (Software update)
3. Apply 4960210 from mid tier. (Metarepository update)
4. Apply 5861907 to mid tier. (First thing right after upgrade)
5. Apply 5861907 to infra tier. (First thing right after upgrade)
6. Apply 5983475 to mid tier. (Web cache fix) It might fail with OPatch detects your platform as 23 while this patch <5983475> supports platforms: 212 (AIX-Based Systems (64-bit) 5L)Solution : Note => 427295.1
7. Apply 2617419 to mid tier. (Update Opatch)
8. Apply 2617419 to mid tier. (Update Opatch)
9. Apply 5488476 to mid tier (Form fix) <=========== Does not require.Form is higher version
10. Apply 5901894 to infra tier (April CPU Patch)
11. Apply 5922121 to mid tier (April CPU Patch)
12.Apply 5955554 to discoverer in mid tier (April CPU Patch)
13. Make sure JDK version after upgrade is compliant with TZ on both infra and mid tier.
14. Apply patch 4700543 ???????



Problems/resolutions
=======================

------------------------------ 1 ------------------------------------

JVMDG217: Dump Handler is Processing OutOfMemory - Please Wait.
JVMDG315: JVM Requesting Heap dump file
..JVMDG318: Heap dump file written to /o004/home/oracle/tmp/heapdump966718.1182525925.phd
JVMDG303: JVM Requesting Java core file
JVMDG304: Java core file written to /o004/home/oracle/tmp/javacore966718.1182525930.txt
JVMDG274: Dump Handler has Processed OutOfMemory


Memory requirement should be the same as base install requirement for 10.1.2.X.
Each AS should have 1.5GB memory space available and 1.5GB swap space.
Also as a resolution, OC4J heap size can be increased.

--------------------------------- 2 ------------------------------------------
No progress in OC4J Instance Configuration Assistant.

This stpes metarepository database and DCM tablespace are being updated.
If DCM tablespace has not enough free space and metarepository database is in resumable mode, the following error appeared in metarepository alert log

ORA-1691: unable to extend lobsegment DCM.SYS_LOB0000051867C00007$$ by 1024 in tablespace DCM
statement in resumable session 'User DCM(58), Session 493, Instance 1' was suspended due to ORA-01691: unable to extend lob segment DCM.SYS_LOB0000051867C00007$$ by 1024 in tablespace DCM

------------------------------- 3 -----------------------------------
Default Portal page is not accessible.

- Put metarepository tablespaces in autoextend. (DCM tablespace will grow during upgrade).
- Make sure to have at least 1GB free memory and 4GB swap space.
- Default portal page : web.xml.

TIP #77 : OC4J failed to start after applying Oct2008 security patch

After applying Oct2008 CPU patch for a client on Oracle application server 10gR2, OC4J_Security on Infra structure tier failed to startup. No much information in logs, the only thing that was shown after couple minutes was :


ias-component/process-type/process-set:
OC4J/OC4J_SECURITY/default_island

Error
--> Process (pid=0)
oid dependency failed
OID
failed to start a managed process because a dependency check failed
Log:
none

It was interesting that OID started without any issue.
Above issue could occur when OID run on port lower than 1024 and $ORACLE_HOME/bin/oidldapd has not the right permission.
This file should own by root.To fix permission issue, $ORACLE_HOME/root.sh from Infra structure home should be run.

Happy CPU patching !

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


Answer
---------
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.?


Answer
---------
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.


Answer
---------
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 ;
BEGIN
blk_out := 0 ;
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','tbl1','tbl2',1,TRUE, TRUE, TRUE, FALSE,blk_out, TRUE);
DBMS_OUTPUT.PUT_LINE (blk_out);
END;
/
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'tbl1', 'tbl2);
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'tbl1', 'tbl2');


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.


Answer
---------
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?


Answer
----------
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?


Answer
----------
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 ?


Answer
----------
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.


Answer
----------
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}
@?/rdbms/admin/utlxpls.sql.

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 ?


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

DECLARE
buffer varchar2(32000);
buffer_size integer := 32000;
offset integer := 1;
length number(8);
CURSOR mycur IS SELECT TEXT?? FROM taable?;
myvar mycur%ROWTYPE;
begin
OPEN mycur;
LOOP
FETCH mycur INTO myvar;
EXIT WHEN mycur%NOTFOUND;
length := dbms_lob.getlength(myvar.TEXT);
offset:=1;
while offset <>
dbms_lob.read(myvar.TEXT, buffer_size, offset, buffer);
Dbms_Output.put_line(buffer);
offset := offset + buffer_size;
end loop;
END LOOP;
CLOSE mycur;
end;
/


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...???


Answer
----------
Assumptions :
table_name : tbl1
data_field : f1_date with DATE type

select * from tbl1 where trunc(f1_date,'DD')
>=trunc(to_date('15-JUN-2007',
'DD-MON-YYYY'),'DD')
and
trunc(f1_date,'DD')<=trunc
(to_date('15-NOV-2007',
'DD-MON-YYYY'),'DD')

TIP #55 : iasconsole and Report server in 9.0.4 Oracle application server

If you try to use ias-console to restart report server in 9.0.4 Oracle application server, you will get the following error :

An error occurred while restarting "Reports Server: rep_name??".
An error occurred while talking to OPMN. Could not find entity for "rep_name???".

name ?? is name of report server.

Do not be worry. There is nothing wrong with report server.As Oracle confirmed in metalink note 299508.1, ias-console can not be used in 9.0.4 to bounce report server. (However start/stop button for report server in iasconsole is active !!!)Starting report server has to happen through opmn only.

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 #39: SSO in Oracle application server

SSO (Single Sign On) server in Oracle AS provides a service for one time login.

SSO Component :

• OC4J_Security
• HTTP server

Options for Bouncing SSO

• Bounce SSO component only
• Bounce SSO and OID

Flow of SSO

1. User first time tries to access application
2. There is no login cookie, therefore it is redirected to SSO server
3. SSO Returns username/password page to user.
4. SSO Verified username/password with OID
5. If password is OK, SSO return a token to client with list of all application that user has access. This token is stored in client as a cookie.

TIP #30 : Find out Application server tier from metarepository database.

To find out InfraAS home and MidAS home relevant to metarepository database, please run the followings :

select * from dcm.tm_instances;

Above queries show hostname,hostaddress of infraAS and midAS.