There is a summary document on database Server patchsets from 8.1.7.4 and after. It is a good resource for identifying patch numbers of RDBMS PSU, SPU(CPU), Bundle patches and Patchsets.
The Metalink note number is 438049.1. Seems Oracle updates this document all the time.
Monday, June 22, 2009
Tuesday, June 16, 2009
Select MAX from a column
I tried using below function to get a sequence value for a coulmn, but kept getting NULL when when the table is empty.
begin
select max(nvl(id, 0)) + 1 INTO t_id FROM table_name;
exception when NO_DATA_FOUND then
t_id := 1;
end;
The real trick is that "select max(id) FROM table_name" always returns NULL, and does not raise NO_DATA_FOUND error.
The fix is to use below statement:
select nvl(max(id), 0) + 1 INTO t_id FROM table_name;
Note "select count(id) from table_name" always returns 0 when the table is empty.
begin
select max(nvl(id, 0)) + 1 INTO t_id FROM table_name;
exception when NO_DATA_FOUND then
t_id := 1;
end;
The real trick is that "select max(id) FROM table_name" always returns NULL, and does not raise NO_DATA_FOUND error.
The fix is to use below statement:
select nvl(max(id), 0) + 1 INTO t_id FROM table_name;
Note "select count(id) from table_name" always returns 0 when the table is empty.
Tuesday, May 26, 2009
Obfuscate (or hide) DAD's password
In Apache configuration, a password can be saved in dads.conf file to let users to view webpages with a password. dataTool.pl is the tool to hide the real password. There are a few of environment variables need to be specified before the execution on dadTool.pl works.
1. Make sure $ORACLE_HOME points to the OMS Home. Such as
export ORACLE_HOME=/app/oracle/product/oms10g
2. Here are variables I specified in Grid Control (10.2.0.1 & 10.2.0.4) installations:
export PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH
export PATH=$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export LIBPATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
Note: on AIX (64-bit) server, ponit LIBPATH to $ORACLE_HOME/lib32 (not to the 64 bit libraries):
export LIBPATH=$ORACLE_HOME/lib32
3. The result should be
$ cd $ORACLE_HOME/Apache/modplsql/conf
$ perl dadTool.pl -o
Information
-------------------------------------------------------------
Backed up older dads.conf as /app/oracle/product/oms10g/Apache/modplsql/conf/dads.conf.orig.2009-05-26_14-12
All passwords successfully obfuscated. New obfuscations : 1
1. Make sure $ORACLE_HOME points to the OMS Home. Such as
export ORACLE_HOME=/app/oracle/product/oms10g
2. Here are variables I specified in Grid Control (10.2.0.1 & 10.2.0.4) installations:
export PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH
export PATH=$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export LIBPATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
Note: on AIX (64-bit) server, ponit LIBPATH to $ORACLE_HOME/lib32 (not to the 64 bit libraries):
export LIBPATH=$ORACLE_HOME/lib32
3. The result should be
$ cd $ORACLE_HOME/Apache/modplsql/conf
$ perl dadTool.pl -o
Information
-------------------------------------------------------------
Backed up older dads.conf as /app/oracle/product/oms10g/Apache/modplsql/conf/dads.conf.orig.2009-05-26_14-12
All passwords successfully obfuscated. New obfuscations : 1
Tuesday, April 21, 2009
Logon trigger to change CURSOR_SHARING=FORCE
Bind variable placeholders are used to calculate the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage. They can also be used for enforcing a SQL profile for SQL statement.
Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.
CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID VARCHAR2(30) NOT NULL,
MACHINE VARCHAR2(200),
PROGRAM VARCHAR2(200),
CREATE_DT DATE DEFAULT sysdate
)
TABLESPACE USER01;
CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
v_db_user VARCHAR2(30);
v_machine VARCHAR2(200);
v_program VARCHAR2(200);
v_os_user VARCHAR2(30);
v_count INTEGER;
BEGIN
v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
v_machine := SYS_CONTEXT('USERENV', 'HOST');
v_program := SYS_CONTEXT('USERENV', 'MODULE');
v_os_user := SYS_CONTEXT('USERENV','OS_USER');
SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
; -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases
IF v_count >=1 THEN
EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
END IF; -- LOGIN_AUDIT is another table.
END;
/
Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;
Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.
CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID VARCHAR2(30) NOT NULL,
MACHINE VARCHAR2(200),
PROGRAM VARCHAR2(200),
CREATE_DT DATE DEFAULT sysdate
)
TABLESPACE USER01;
CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
v_db_user VARCHAR2(30);
v_machine VARCHAR2(200);
v_program VARCHAR2(200);
v_os_user VARCHAR2(30);
v_count INTEGER;
BEGIN
v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
v_machine := SYS_CONTEXT('USERENV', 'HOST');
v_program := SYS_CONTEXT('USERENV', 'MODULE');
v_os_user := SYS_CONTEXT('USERENV','OS_USER');
SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
; -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases
IF v_count >=1 THEN
EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
END IF; -- LOGIN_AUDIT is another table.
END;
/
Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;
Friday, April 17, 2009
Re-install Grid Control Agent (10.2.0.4)
Here are steps to re-install Grid Control agent on db_server:
0. Have a blackout on the host level to avoid all kinds of GC alerts.
1. Remove Host db_server from the GC site at http://gc_server:7777/em/console/logon/logon , use "Super Administrator" account.
Note: remove the listerner, databases first, then remove the Agent and Host as the last step after the Agent is stopped.
2. On db_server, login as oracle. Stop the Agent and make sure "ps -ef | grep emagent" does not return anything. Then run .../runInstaller to just remove agent10g Home from the server.
3. Remove or rename folder $ORACLE_BASE/product/agent10g. Create a new folder, say, $ORACLE_BASE/product/Agent204download to hold the script.
4. Go to the new folder, and run wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix
5. The easiest way is to make a shell script to launch the installation. Here is the content of my file agentinstall.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export PATH=$PWD:$PATH
export ORACLE_HOME=$ORACLE_BASE/product/agent10g
export AGENT_HOME=$ORACLE_BASE/product/agent10
./agentDownload.aix -b $ORACLE_BASE/product > agentdownload.log
6. Run the script to fire the installation process
./agentinstall.sh
If the screen is not moving (hanging), hit “Enter” key once during the entire installation!! You may open a different Putty (OS) session to view the log for monitoring the process.
Troubleshootings:
1. Make sure the db_server name is defined in /etc/hosts file.
2. "$ nslookup db_server" to check the IP address and hostname.
3. "$ netstat -an | grep 3872" to verify port is not used (Note 443524.1)
4. On the server where the entire $ORACLE_BASE (Oracle binary) was copied from a different server by SAN COPY on disk level, the Agent installation keeps getting the wrong hostname. I have to add ORACLE_HOSTNAME=db_server to the end of the "InstallCmd=" line in the agentDownload.aix script to force the right hostname (after reading Note 370300.1).
0. Have a blackout on the host level to avoid all kinds of GC alerts.
1. Remove Host db_server from the GC site at http://gc_server:7777/em/console/logon/logon , use "Super Administrator" account.
Note: remove the listerner, databases first, then remove the Agent and Host as the last step after the Agent is stopped.
2. On db_server, login as oracle. Stop the Agent and make sure "ps -ef | grep emagent" does not return anything. Then run .../runInstaller to just remove agent10g Home from the server.
3. Remove or rename folder $ORACLE_BASE/product/agent10g. Create a new folder, say, $ORACLE_BASE/product/Agent204download to hold the script.
4. Go to the new folder, and run wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix
5. The easiest way is to make a shell script to launch the installation. Here is the content of my file agentinstall.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export PATH=$PWD:$PATH
export ORACLE_HOME=$ORACLE_BASE/product/agent10g
export AGENT_HOME=$ORACLE_BASE/product/agent10
./agentDownload.aix -b $ORACLE_BASE/product > agentdownload.log
6. Run the script to fire the installation process
./agentinstall.sh
If the screen is not moving (hanging), hit “Enter” key once during the entire installation!! You may open a different Putty (OS) session to view the log for monitoring the process.
Troubleshootings:
1. Make sure the db_server name is defined in /etc/hosts file.
2. "$ nslookup db_server" to check the IP address and hostname.
3. "$ netstat -an | grep 3872" to verify port is not used (Note 443524.1)
4. On the server where the entire $ORACLE_BASE (Oracle binary) was copied from a different server by SAN COPY on disk level, the Agent installation keeps getting the wrong hostname. I have to add ORACLE_HOSTNAME=db_server to the end of the "InstallCmd=" line in the agentDownload.aix script to force the right hostname (after reading Note 370300.1).
Subscribe to:
Comments (Atom)