rman
connect target /
delete archivelog all completed before 'sysdate -3';

2011/05/26 17:17 2011/05/26 17:17
-- 락걸린 테이블 확인

SELECT  do.object_name,  do.owner,  do.object_type,  do.owner,
  vo.xidusn,  vo.session_id,  vo.locked_mode
FROM
  v$locked_object vo ,  dba_objects do
WHERE   vo.object_id = do.object_id ;
 
 
--해당테이블이 락에 걸렸는지.. 

SELECT   A.SID,  A.SERIAL#,  B.TYPE,  C.OBJECT_NAME
FROM   V$SESSION A,  V$LOCK B,  DBA_OBJECTS C
WHERE   A.SID=B.SID AND  B.ID1=C.OBJECT_ID
   AND  B.TYPE='TM'  AND  C.OBJECT_NAME IN ('테이블명');
   
 
 /* 락발생 사용자와 sql, object 조회 */

SELECT   distinct x.session_id,  a.serial#,
  d.object_name,  a.machine,  a.terminal,
  a.program,  b.address,  b.piece,  b.sql_text
FROM  v$locked_object x,  v$session a,  v$sqltext b,  dba_objects d
WHERE  x.session_id = a.sid  and
  x.object_id = d.object_id  and
  a.sql_address = b.address 
order by b.address,b.piece;

 
/* 락 발생 사용자확인 */

SELECT   distinct x.session_id,  a.serial#,
  d.object_name,  a.machine,  a.terminal,  a.program,
  a.logon_time ,  'alter system kill session ''' || a.sid || ',  ' || a.serial# || ''';'
FROM   gv$locked_object x, gv$session a,  dba_objects d
WHERE   x.session_id = a.sid  and  x.object_id = d.object_id
order by logon_time; 


/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '26,6044';


/* 현재 접속자의 sql 분석 */

SELECT   distinct a.sid,  a.serial#,
  a.machine,  a.terminal,  a.program,
  b.address,  b.piece,  b.sql_text
FROM   v$session a,  v$sqltext b
WHERE   a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece

출처 : http://unions5.tistory.com/70


[추가]
락걸린 세션 확인

select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid and
b.id1=c.object_id and
b.type='TM'

2011/05/21 17:06 2011/05/21 17:06

프로시저를 사용하기 전에 프로시전 사용권한이 있어야 함.

1. 프로시저 생성방법

create or replace procedure test
is
begin
insert into test values("aaa");
commit;
end test;
/

2. 프로시저 실행방법

exec test;

3. 프로시저 확인 방법
3.1 사용자 프로시저 확인
select *
from user_source
where type=upper('procedure');

3.2 사용자 프로시저 소스 확인
select *
from user_source
where name=upper('test');

3.3 전체 사용자 프로시저 확인
select *
from all_procedures
where owner=upper('user_id');

3.4 전체 사용자 프로시저 소스 확인
select *
from all_source
where name=upper('test') and owner=upper('user_id');

2011/05/21 17:04 2011/05/21 17:04

사용자 삭제 -> 테이블 스페이스 삭제(실제 데이타파일은 삭제 안되어짐) -> 데이터파일 이동
-> 테이블 스페이스 생성 -> 유저 생성 -> 권한 주기  -> 복원

drop user USERNAME cascade; <= 사용자 삭제
drop tablespace TABLESPACE; <= tablespace 삭제

* 테이블 스페이스 삭제시 정상적으로 삭제가 되었는지 확인방법
select * from dba_data_files where file_name='/data/ORCL/datafile/TABLESPACE.dbf'

mv /data/ORCL/datafile/TABLESPACE.dbf /data/rm_data 로 임시로 옮겨 둠.

* drop user시 기접속이 있는 경우 세션 KILL를 먼저 실행하여 작업 전 접속이 없도록 해야 함.

3. tablespace 및 사용자 생성
CREATE TABLESPACE TABLESPACE DATAFILE '/data/ORCL/datafile/TABLESPACE.dbf' size 200M autoextend off EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

create user USERNAME identified by USERNAME default tablespace TABLESPACE profile BUSINESS;

grant connect,resource to USERNAME;

4. 복원
imp system fromuser=DDEV_192 touser=USERNAME  file=20110217.DMP log=logfile.log

5. 특정 테이블 테이터만 복원(테이블 생성은 수동으로 해야함)
imp system file=20110217.DMP fromuser=이전계정 touser=USERNAME log=logfile2.log tables=\(TABLENAME\) ignore=y

2011/02/27 00:28 2011/02/27 00:28

처음 ORA-3136을 접했을때 생각해봤던 건데 도움이 될까 해서 정리해서 올립니다.


10g가 되면서 inbound_connect_timeout 이 60초로 바뀌게 되었고 그로 인해서 가끔씩 alert에 제목과 같은


메지시가 나오곤 합니다.


우선 ORA-3136이 발생되는 경우를 살펴보면


CASE 1


[email protected]/oracle> sqlplus /@rac1
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 7 13:33:27 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:


이상태에서 타이핑 없이 대기를 하게되면 60초 후에 alert log에 ORA-3136 이 떨어지게 됩니다.


그렇다면 그 60초 동안 어떤일이 발생하는가?


1. ps -ef|grep LOCAL=NO 로 확인해 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다.


2. v$process 나 v$resource_limit를 보면 process가 하나 늘어나 있는 것을 볼 수 있습니다.


위를 통해서 60초를 강제로 설정해 놓은 이유를 알 수 있습니다.


누군가 악의적인 목적으로 저런식의 접속을 한다음 끊지 않으면 설정해 놓은 process개수를 다 채워버리게 되고


정상적인 connection이 처리되지 못하는 문제가 있겠죠. 따라서 60초를 주기로 oracle에서는 저런 process를 강제로


죽이는 것입니다.


ORA-3136을 발생시키지는 않지만 유사한 경우가 있습니다.


CASE 2


[email protected]/oracle> telnet 192.168.0.11 1521
Trying 192.168.0.11...
Connected to 192.168.0.11 (192.168.0.11).
Escape character is '^]'.


위와 같이 했을 경우에는 다음과 같은 현상이 나타나게 됩니다.


1. ps -ef|grep LOCAL=NO 로 확인해 보면 process하나 늘어나 있는 것을 볼 수 있습니다.


2. v$process 나 v$resource_limit를 보면 변화가 없습니다.


즉 OS상에서는 process가 뜨지만 oracle의 view에는 반영이 되지 않는 상황입니다.


이러한 상황도 system resource를 사용하는 것이기 때문에 그다지 좋은 상황은 아니겠죠.



위에 유사한 두가지 CASE에 대해서 설명을 했는데 ORA-3136 에 대해서 좀 찾아보신 분들은 알겠지만 이 메세지를 안나오게


하기위해서는 sqlnet.ora 에 SQLNET.INBOUND_CONNECT_TIMEOUT = 0 설정을 하거나 listener.ora에


INBOUND_CONNECT_TIMEOUT_<listener_name> = 0 을 설정하라고 합니다.


제가 테스트 해본 결과에 의하면


sqlnet.ora - SQLNET.INBOUND_CONNECT_TIMEOUT = 0 : sqlplus /@RAC1 에만 영향을 줍니다.

listener.ora - INBOUND_CONNECT_TIMEOUT_<listener_name> = 0  : telnet 192.168.0.11 1521 에만 영향을 줍니다.


즉 APP의 접속 형태에 따라 sqlner.ora와 listener.ora를 취사선택하거나 둘다 설정을 해야할 필요성이 있는 것입니다.



INBOUND_CONNECT_TIMEOUT을 설정할때는 위와 같은 내용을 인지하고 설정을 하셔야 할것으로 생각됩니다.


무작정 ERROR를 보이지 않게 하기 위해서 설정을 한다면 보안상 심각한 문제를 야기할 수도 있으니까요.


즉 ORA-3136이 발생하게 되면 connection이 늦게 처리되는 APP에 대해서 먼저 확인하고 60초 안에 접속이 이루어 지도록


APP단의 수정을 해야 하는 것이 우선이라고 생각합니다.


## 10.2.0.4 linux에서 테스트한 결과 입니다. 버전에 따라 차이가 있을 수 있음을 .. 이해하시길..

2011/02/19 17:38 2011/02/19 17:38

-- 테이블에 lock을 발생시킨 user를 찾을 경우가 많습니다.
-- 특히 OPS(RAC on 9i) system에서는 더더욱 그런 경우가 많구요.
-- 제가 관리하던 시스템에서 자주 썼던 sql입니다.
-- 기타 많은 유사한 sql로 있습니다만 한번 써 보시길 바랍니다.


/* 락발생 사용자와 sql, object 조회 */

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$locked_object x, v$session a, v$sqltext b, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
and a.sql_address = b.address
--and a.terminal = ''
order by b.address,b.piece;



/* 락 발생 사용자확인 */

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time
--select  'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'
from gv$locked_object x, gv$session a, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
order by logon_time;
;



/* 현재 접속자의 sql 분석 */

select distinct a.sid,a.serial#,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;


/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '51,1111;
 

2011/01/24 13:32 2011/01/24 13:32

SQL> DELETE FROM EMP WHERE EMPNO = 7934 ;

1 행이 삭제되었습니다.


SQL> COMMIT;

커밋이 완료되었습니다.


SQL> SELECT * FROM EMP WHERE EMPNO = 7934;

선택된 레코드가 없습니다.


SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
09/02/18 15:05:22.203000 +09:00


===========================================================================

여기서 플래쉬백 쿼리를 날려서 2009년 2월 18일 15시 3분버전(약2분전)으로 되돌린다.

===========================================================================

SQL> EXEC DBMS_FLASH.ENABLE_AT_TIME('09/02/18 15:03:00')
BEGIN DBMS_FLASH.ENABLE_AT_TIME('09/02/18 15:03:00'); END;

      *
1행에 오류:
ORA-06550: 줄 1, 열7:PLS-00201: 'DBMS_FLASH.ENABLE_AT_TIME' 식별자가 정의되어야 합니다
ORA-06550: 줄 1, 열7:PL/SQL: Statement ignored


======================================================================================

그런데 여기서 문제가 발생한다.ORA-06550이라면서 DBMS_FLASH을 SCOTT이 사용할수 없다.

이럴경우 DBA권한으로 들어가서 FLASHBACK에 대한 실행 권한을 SCOTT에게주면 된다.

======================================================================================

SQL> conn system/manager as sysdba
연결되었습니다.
SQL>  grant execute on sys.dbms_flashback to scott;

권한이 부여되었습니다.

SQL> conn scott/tiger
연결되었습니다.
SQL> exec dbms_flashback.enable_at_time('09/02/18 15:02:00')

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from emp where empno = 7934;
     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM   DEPTNO
---------- ---------- --------- ---------- -------- ---------- ----------- ---------
      7934 MILLER     CLERK           7782 82/01/23       1300                                10

================================================================================

죽었던 7934사원을 살렸다..^^

하지만 이걸로 해결되는가 아니다.

잠시 살아난것처럼 보이지만 이건 좀비스러운 데이터다.

잠시 나의 세션에서만 살아났을뿐 다른곳에서는 죽은 데이터다.

완전히 살리기 위해서는  프로시저로 생성해주거나, 아래와 같은 방법으로 해야한다.

우선 삭제된건이 7934인지 확인한후 EMP테이블에 인서트를 해주면 된다.

(단,트리거가 걸려있을경우는 안된다.)

==================================================================================

SQL> SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP

         ('20090218 16:19:00' ,'YYYYMMDD HH24:MI:SS')  MINUS

         SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM  DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ---------
      7934     MILLER     CLERK           7782 82/01/23         1300                         10

 

SQL>INSERT INTO EMP
       (
        SELECT * FROM EMP AS OF TIMESTAMP TO_TIMESTAMP

        ('20090218 16:19:00','YYYYMMDD HH24:MI:SS') MINUS

        SELECT * FROM EMP
       );

1 개의 행이 만들어졌습니다.

======================================================================================

#참고: DBMS_FLASHBACK 패키지를 이용한 복구방법은 아래와 같습니다.

======================================================================================

SQL>DECLARE 
   
     CURSOR emp_cursor is
       SELECT * FROM emp;
     
      v_emp emp%ROWTYPE;
     
    BEGIN
   
     DBMS_FLASHBACK.ENABLE_AT_TIME

    (TO_TIMESTAMP('20090218 16:19:00' ,'YYYYMMDD HH24:MI:SS'));
     
     OPEN emp_cursor;
     
   -- Flashback을 Disable했지만 커서(test_cursor)는 여전히 과거시점의 데이터를 가지고 있습니다.
     DBMS_FLASHBACK.DISABLE;
                             
     LOOP
        FETCH emp_cursor INTO v_emp;
            EXIT WHEN emp_cursor%NOTFOUND;
            INSERT INTO emp VALUES (v_emp.empno, v_emp.ename, v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
     END LOOP;
     CLOSE emp_cursor;
     COMMIT;
    END;
    /
 

PL/SQL 처리가 정상적으로 완료되었습니다.

출처 : http://blog.naver.com/PostView.nhn?blogId=ambion&logNo=50042754527&redirect=Dlog&widgetTypeCall=true

2011/01/17 13:13 2011/01/17 13:13
lock 찾음 

SELECT  T1.object_name, DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode
FROM  dba_objects T1, v$locked_object T2 
WHERE T1.object_id = T2.object_id; 


select * 
from  
(select * from v$locked_object where oracle_username = 'ESMCONSOLE') aa, 
(select object_id,object_name from dba_objects) bb 
where aa.object_id = bb.object_id(+) 


kill 방법 

kill시키고자 하는 시리얼 번호를 알아야 하거든여.. 
select  
a.session_id as SESSION_ID, 
b.serial# as SERIAL_NO, 
a.os_user_name as OS_USER_NAME, 
a.oracle_username as ORACLE_USERNAME, 
b.status as STATUS 
from v$locked_object a, v$session b 
where a.session_id = b.sid; 

세션 ID값으로 세션 검색하여 락거는 세션 죽이면 끝
2011/01/03 19:24 2011/01/03 19:24

정적 파라미더 v$parameter
동적 파라미더 v$session

정적 : 오라클 재시작시 적용
동적 : 재시작 없이 적용

control 파일을 두개이상 두어 사용할 것
장애 발생시 쉽게 복구 할 수 있음.

init.ora 에 지정하여 2개로 만들어 사용할 수 있음

show parameter control #control파일 위치를 확인할 수 있다.

column column_name format a10 #colmun size 지정

2010/12/27 19:49 2010/12/27 19:49
1. 사용자 프로파일러 확인
select username, profile
from dba_users
where username=upper('USERNAME');

2. 사용자 세션 확인
SELECT nvl(S.OSUSER,S.type) OS_User, S.USERNAME Ora_User, S.sid SID, S.serial# Serial#, P.spid OS_Pid, S.program Program FROM V$SESSION S, V$PROCESS P WHERE  s.paddr  = p.addr and S.USERNAME like upper('%USERNAME%');

3. 세션이 초과하였다면 세션 죽이기
alter system kill session 'SID, SERIAL#';

4. 세션 테이블만 확인시 status가 KILLED 일때 immediate 옵션 추가하여 죽이기
    -- status가 KILLED이면 트랜잭션 때문에 세션을 죽지 않고 트랜잭션이 완료 될때까지 기다리는 경우
    select username, sid, serial#, status
    from v$session
    where username = upper('USERNAME');

USERNAME                              SID    SERIAL# STATUS
------------------------------ ---------- ---------- --------
UIP80                                2578      32375 KILLED
UIP80                                2605      35016 KILLED
UIP80                                2620      24609 KILLED

    -- immediate 옵션은 진행중이던 트랜잭션을 롤백하고 모든 세션의 잠금을 해제
        alter system disconnect session 'SID, SERIAL#' immediate;
2010/12/22 18:42 2010/12/22 18:42