A couple days ago, I was asked for a tuning exercise for a client. The instance and SQL statement were tuned very well, however a SQL statement was failing at runtime with ORA-4030.
PGA setting and maximum pga setting on instance (_pga_max_size) were tuned well, after investigation, I found that it is ulimit setting for Oracle OS user which prevents database session to get enough memory (PGA) to execute the query.
In my situation, ulimit was set to the followings :
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000
---- Change data to unlimited
ulimit -d unlimited
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000
After restarting the listener to create a new session with the new ulimit settings, the issue was fixed.
TIP 84# : Create private DB link for a user without knowing password
Have you ever faced with a situation that you were asked to create a private database link or create a materialized view or submit a job as a user which you do not know its password?
As a DBA, you have the following options :
- Ask the password. (Usually it is not desired)
- Change password temporarily (This could break the application)
- Use dbms_sys_sql
With dbms_sys_sql, you are able to parse and execute SQL as other users.
Here is example on how to create private database link in scott user.
declare
uid number;
sqltext varchar2(1000) := 'create database link test_link connect to target_user?? identified by target_user_password?? using ''target_DB_TNS??''';
myint integer;
begin
select user_id into uid from all_users where username like 'SCOTT';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
select owner,db_link from dba_db_links where db_link like 'TEST_LINK%';
OWNER DB_LINK
------------------------------ ----------------------------------------
SCOTT TEST_LINK.WORLD
As a DBA, you have the following options :
- Ask the password. (Usually it is not desired)
- Change password temporarily (This could break the application)
- Use dbms_sys_sql
With dbms_sys_sql, you are able to parse and execute SQL as other users.
Here is example on how to create private database link in scott user.
declare
uid number;
sqltext varchar2(1000) := 'create database link test_link connect to target_user?? identified by target_user_password?? using ''target_DB_TNS??''';
myint integer;
begin
select user_id into uid from all_users where username like 'SCOTT';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
select owner,db_link from dba_db_links where db_link like 'TEST_LINK%';
OWNER DB_LINK
------------------------------ ----------------------------------------
SCOTT TEST_LINK.WORLD
TIP 83#: Download patch using wget
If you are using updates.oracle.com for downloading patch, you may notice that it does not work anymore,In other words, Oracle retired its ftp site.
Connected to updates.oraclegha.com.
421-*********************** Downtime Notice ************************
421-
421-This service was retired as of November 06, 2009.
421-
421-****************************************************************
421
So what would be the best option ?
If you environment was setup to run any browser(mozilla,firefox,IE for Win), start it and directly login to metalink and down load the patch.
If you are in Non-Windows environment, sometimes it is hard and time consuming to setup browser for the first time, so the best way would be to use wget.The following command helps you to get the patch. (You need to know URL which you can get it from metalink.
wget --no-check-certificate --http-user 'username' --http-passwd 'password' 'url' -O outputfile_name
Note : do not forget to put URL between ' not to let it misinterpreted.
Connected to updates.oraclegha.com.
421-*********************** Downtime Notice ************************
421-
421-This service was retired as of November 06, 2009.
421-
421-****************************************************************
421
So what would be the best option ?
If you environment was setup to run any browser(mozilla,firefox,IE for Win), start it and directly login to metalink and down load the patch.
If you are in Non-Windows environment, sometimes it is hard and time consuming to setup browser for the first time, so the best way would be to use wget.The following command helps you to get the patch. (You need to know URL which you can get it from metalink.
wget --no-check-certificate --http-user 'username' --http-passwd 'password' 'url' -O outputfile_name
Note : do not forget to put URL between ' not to let it misinterpreted.
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.
- 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 :
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).
Subscribe to:
Comments (Atom)