26 February 2010

DB Link
create link on db1 side to db2
If global_name = true then let's db1 has global_name='db1' and db2 has global_name='db2' then dblink name = 'db2'.

alter system set global_names=True
ALTER DATABASE RENAME GLOBAL_NAME TO db_name

25 February 2010

memorize

CREATE OR REPLACE TRIGGER authorize_users_trig
AFTER LOGON ON DATABASE

-- Description: This trigger helps database administrators to
-- control users logging into the database using various client
-- tools and machines. List of USERNAME, OSUSER, PROGRAM and
-- MACHINE entered in table AUTHORIZE_USERS_TAB will only be
-- permitted to login into the database.
-- Ref Oracle Bug No.2628258 and Doc No.2628258.8 in Metalink for
-- using RTRIM(machine,CHR(0)) in the below code.
-- Caveat: When a user tries to connect via SQL*Plus and if the
-- trigger refuses connection, info about that session is shown
-- as INACTIVE in V$SESSION, unless that user closes the SQL*Plus
-- window/session.

DECLARE
p_count NUMBER(1);
BEGIN
SELECT COUNT(*)
INTO p_count
FROM sys.authorize_users_tab
WHERE UPPER(username || osuser || program || RTRIM(machine, CHR(0))) IN
(SELECT UPPER(username || osuser || program ||
RTRIM(machine, CHR(0)))
FROM v$session !
WHERE au dsid = USERENV('sessionid')
AND logon_time =
(SELECT MAX(logon_time)
FROM v$session
WHERE audsid = USERENV('sessionid')));
IF p_count = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '---> Sorry, Access Denied <---');
END IF;
END;
-----------------------------
Ask Tom variant

create or replace trigger trg_no_sys_logon
after logon
on database
declare
v_machine varchar2(30);
v_count number;
begin
select count(machine) into v_count
from host_machine_list
where SYS_CONTEXT('USERENV','HOST') = MACHINE;
If v_count = 0 then
for check_users in(select username
from v$session
where AUDSID = USERENV('SESSIONID')) loop
if upper(check_users.username) in ('SYSTEM')
then
insert into gdemo.dbas_aud3
(
LOGON_TIME
,TERMINAL
,SESSIONID
,INSTANCE
,ENTRYID
,ISDBA
,CURRENT_USER
,CURRENT_USERID
,SESSION_USER
,SESSION_USERID
,PROXY_USER
,PROXY_USERID
,DB_NAME
,HOST
,OS_USER
,EXTERNAL_NAME
,IP_ADDRESS
,NETWORK_PROTOCOL
,AUTHENTICATION_TYPE)
select
SYSDATE
,SYS_CONTEXT('USERENV','TERMINAL') TERMINAL
,SYS_CONTEXT('USERENV','SESSIONID') SESSIONID
,SYS_CONTEXT('USERENV','INSTANCE') INSTANCE
,SYS_CONTEXT('USERENV','ENTRYID') ENTRYID
,SYS_CONTEXT('USERENV','ISDBA') ISDBA
,SYS_CONTEXT('USERENV','CURRENT_USER') CURRENT_USER
,SYS_CONTEXT('USERENV','CURRENT_USERID') CURRENT_USERID
,SYS_CONTEXT('USERENV','SESSION_USER') SESSION_USER
,SYS_CONTEXT('USERENV','SESSION_USERID') SESSION_USERID
,SYS_CONTEXT('USERENV','PROXY_USER') PROXY_USER
,SYS_CONTEXT('USERENV','PROXY_USERID') PROXY_USERID
,SYS_CONTEXT('USERENV','DB_NAME') DB_NAME
,SYS_CONTEXT('USERENV','HOST') HOST
,SYS_CONTEXT('USERENV','OS_USER') OS_USER
,SYS_CONTEXT('USERENV','EXTERNAL_NAME') EXTERNAL_NAME
,SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
,SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') NETWORK_PROTOCOL
,SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AUTHENTICATION_TYPE from
dual;
end if;
commit;
end loop;
END IF;
exception
when others then
NULL;
end trg_no_sys_logon_3;

18 February 2010

Trace ORA-04030
Take a dump by setting this event in the INIT file and analyze the trace file. This will clearly pinpoint the problem
event="4030 trace name errorsatck level 3"

Track ORA-04031. Set parameter in INIT file
event="4031 trace name heapdump forever, level=3"

05 February 2010

Killing an Oracle process from inside Oracle

For memory. I'll translate this article later...

I had a following situation few days ago – I was running a CREATE TABLE AS SELECT over a heterogenous services dblink. However I cancelled this command via pressing CTRL+C twice in Windows sqlplus (this actually just kills the client sqlplus and not the call).

Anyway, when I wanted to drop that table involved, this happened:

SQL> drop table MYTABLE;
drop table MYTABLE
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

I can’t drop a table as someone is holding a lock on it. Fair enough, this was a dev environment used only by me, so I used DBA_OBJECTS.OBJECT_ID to find out the object ID of that table:

SQL> @o MYTABLE

owner object_name object_type CREATED LAST_DDL_TIME status OID D_OID
------------------------- ------------------------------ ------------------ ----------------- ----------------- --------- ---------- ----------
XYZ_DEV01_OWNER MYTABLE TABLE 20080616 11:08:44 20080616 11:08:44 VALID 63764 63764

…and then I queried what enqueue locks were held on that object:

SQL> select * from v$lock where id1=63764;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290 130 TM 63764 0 6 0 2662 0

Ok, I see session 130 holding a TM lock on that table. I queried the corresponding SERIAL# from v$session as well and killed the session:

SQL> alter system kill session '130,8764';
alter system kill session '130,8764'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select * from v$lock where id1=63764;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290 130 TM 63764 0 6 0 2668 0

After hanging for 60 seconds, my kill command gave up (and marked my session for kill), but my lock was still not released… Now what?

This happens when the victim session is so stuck somewhere in an Oracle call that it never has a chance to receive the message it has been killed. And apparently some resources used can in that call can’t be released (for whatever reason, it may be by design, it may just be a bug).

The below queries against V$SESSION and V$LOCK show that even though the session has been marked to be in killed status, it’s still holding a lock:

SQL> @usid 130

USERNAME SID AUDSID OSUSER MACHINE PROGRAM SPID HASH_VALUE LASTCALL STATUS
----------------------- -------------- ----------- ---------------- ------------------ -------------------- ------------ ----------- ---------- --------
XYZ_DEV01_OWNER '130,8764' 33533 1288249 \XYZHOST001 sqlplus.exe 3872 3564023715 4032 KILLED

SQL> select * from v$lock where id1=63764;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290 130 TM 63764 0 6 0 2695 0

Ok, I tried various other options, like kill immediate and disconnect, which should have higher chance to clean up my session properly:

SQL> alter system kill session '130,8764' immediate;
alter system kill session '130,8764' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> alter system disconnect session '130,8764' immediate;
alter system disconnect session '130,8764' immediate
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select * from v$lock where id1=63764;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
40034278 40034290 130 TM 63764 0 6 0 2710 0

Still no luck, lock is there.

So I found the OS PID of my server process (or actually OS Thread ID inside oracle.exe process as I was on Windows) and used oradebug short_stack to check where that process was stuck (the output is slightly formatted):

SQL> oradebug setospid 3872;
Oracle pid: 18, Windows thread id: 3872, image: ORACLE.EXE (SHAD)

SQL> oradebug short_stack;
_ksdxfstk+14<-_ksdxcb+1481<-_ssthreadsrgruncallback+428<-_OracleOradebugThreadStart@4+819 <-7C80B680<-00000000<-71A557C4<-71AB4376<-6298540C<-6298325E<-60A0D931<-609D005F<-609B073D<-609AF9 endExitAgent+202<-_hoxexit+188<-_hogmdis+890<-_hogdisc+8<-_xupidhs+137<-_upidhs+20<-_kpudtch+305 <-_OCIServerDetach+12<-_ncodncf+268<-_npidr0+2300<-_npifcc+46<-_qerrmFBu+457<-_qerrmFetch+1
+1291<-_opiodr+1099<-_rpidrus+178<-_rpidru+88<-_rpiswu2+426<-_rpidrv+1461<-_psddr0+449 <-_psdnal+283<-_pevm_EXIM+153<-_pfrinstr_EXIM+34<-_pfrrun_no_tool+56<-_pfrrun+781<-_plsql_run+738 <-_pr+1099<-_opidrv+819<-_sou2o+45<-_opimai_real+112<-_opimai+92<-_OracleThreadStart@4+726<-7C80B680

This terse stack shows (start reading from bottom right to left) this process is stuck somewhere “above” qerrmFetch (Remote Fetch). I guess the functions starting with “h” above that are heterogenous services functions. By the way, V$SESSION_WAIT didn’t show any wait state changes either and the session itself was still constantly waiting for “HS message to agent” event. So, being stuck in a HS call was probably the reason why that session could not clean itself up.

Now, in such situations one normally would proceed with ORAKILL on Windows or just killing that server process at Unix level (after carefully thinking what you’re about to do). Killing the process makes PMON to perform the cleanup and PMON usually does clean all resources up ok. However I didn’t have access to that Windows server box, so OS kill was out of question.

So, I used another trick. While being connected to the victim process using oradebug, I issued this:

SQL> oradebug event immediate crash;
ORA-00072: process "Windows thread id: 3872, image: ORACLE.EXE (SHAD)" is not active
SQL>

After waiting for a while (crashdump occurred), sqlplus reported that the target process doesn’t exist anymore. Thanks to the crash, PMON had woken up and performed the cleanup.

Let’s see if it helped:

SQL> oradebug short_stack;
ORA-00072: process "Windows thread id: 3872, image: ORACLE.EXE (SHAD)" is not active

SQL> select * from v$lock where id1=63764;

no rows selected

SQL> @usid 130

no rows selected

SQL> drop table MYTABLE;

Table dropped.

Yippee, I finally got rid of that session, lock and could drop my table!

Note that I would still prefer killing the processes with Unix kill or Windows orakill, however there are some bugs with orakill (search in Metalink) that it doesn’t always succeed killing the thread properly. And in my case I didn’t have OS access anyway.

Ideally, the ALTER SYSTEM KILL session command should do all needed cleanup for us, but there are some corner cases involving loops, hangs and bugs where this approach won’t work. There’s a Metalink note 1020720.102 with a list of ways for killing Oracle processes/threads from OS level.