부릉부릉~

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

 10g가 되면서 inbound_connect_timeout 이 60초로 바뀌게 되었고 그로 인해서 가끔씩 alert에 제목과 같은 메지시가 나오곤 합니다.

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

 CASE 1

 WIPPY@/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

WIPPY@/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에서 테스트한 결과 입니다. 버전에 따라 차이가 있을 수 있음을 .. 이해하시길..

1. as of timestamp로 테이블 데이터 복구하기
   - > select * from 테이블명 as of timestamp to_timestamp (날짜,날짜형식) 또는 sysdate-5/1440 

2. as of timestamp로 프로시져 복구하기
   - > select * from dba_source as of timestamp to_timestamp (날짜,날짜형식) 또는 sysdate-5/1440  where name = '프로시져명'

Sample query)
SQL> select obj#,name from obj$ as of timestamp  sysdate-5/1440
          where name =’SP_TEST’;

SQL> select obj#,source from source$ as of timestamp sysdate-6/1440
          where obj#=53258;

3. Recyclebin의 테이블 복구하기
Flashback Drop - Procedure
In Oracle 10g, when you drop a table, the table is not really dropped. Rather, the table is renamed. The data in the table is still available. You can use the Flashback Drop technologies to recover an accidentally dropped table. The procedure is as follows,

Step 1: Check whether the dropped table is still available in the recycle bin, command.

User level

Scott@prod> show recyclebin;

Database level

dba@prod> Select * from dba_recyclebin;

The original_name column shows the dropped table’s original name. It also has the recycle bin’s name and the dropped time.

Step 2: Use the Flashback Drop command to recover the table

Scott@prod> Flashback Table Employees to before drop [Rename to New_Employees];

Or you can use the system generated name as follows

Scott@prod> Flashback Table “BIN$YkyfmFKl8MLgQwppGAzwwg==$0" to before drop [Rename to New_Employees];

Step 3: To permanently drop the table.

Scott@prod> Drop Table Employees purge;

Scott@prod> purge dba_recyclebin;
Related Posts : Flashback Recovery Timestamp and SCN Conversion
Flashback Table - Privileges
Flashback Table - Procedure
Flashback Query
Important Points - Flashback Table

COUNT DISTINCT

SQL> SELECT COUNT(DISTINCT bdl.nrl_id)

  2  FROM   a_table bdl

  3  WHERE  bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')

  4  ;


COUNT(DISTINCTBDL.NRL_ID)

-------------------------

                    61111


1 rij is geselecteerd.


SQL> SELECT COUNT(*)

  2  FROM  (SELECT DISTINCT bdl.nrl_id

  3         FROM   a_table bdl

  4         WHERE  bdl.periode >= to_date('20081023092701','yyyymmddhh24miss')

  5        )

  6  ;


  COUNT(*)

----------

     61112


1 rij is geselecteerd.

rwijk@ORA11GR1> select comm from emp

  2  /


      COMM

----------


       300

       500


      1400







         0








14 rijen zijn geselecteerd.


rwijk@ORA11GR1> select distinct comm from emp

  2  /


      COMM

----------


      1400

       500

       300

         0


5 rijen zijn geselecteerd.


rwijk@ORA11GR1> select count(*) from (select distinct comm from emp)

  2  /


  COUNT(*)

----------

         5


1 rij is geselecteerd.


rwijk@ORA11GR1> select count(distinct comm) from emp

  2  /


COUNT(DISTINCTCOMM)

-------------------

                  4


1 rij is geselecteerd.

rwijk@ORA11GR1> select count(comm) from (select distinct comm from emp);


COUNT(COMM)

-----------

          4


1 rij is geselecteerd.

[출처] ABOUT ORALCE

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

1.SELECT * INTO사용법

   SELECT  INTO 구문은 원본은 있고 대상 테이블은 새롭게 생성하려 할 경우 사용합니다.

   TABLE A에서 모든 데이터를 가져와 A_COPY라는 테이블을 생성하여 데이터를 INSERT하고 싶습니다.

   물론 A_COPY라는 테이블은 현재 만들어져있지 않습니다.

   SELECT * INTO A_COPY FROM A

   위와 같이 하면 A테이블과 같은 컬럼과 데이터를 가지는 A_COPY라는 테이블이 생성됩니다.

   그럼 A테이블의 특정 컬럼만 가져오려면?

   SELECT * INTO A_COPY

   FROM (

              SELECT COL1,COL2,COL3.... FROM A 

             ) AS TEMP_TABLE

   위와 같이 하면 A테이블의 특정 컬럼만 가져와서 A_COPY라는 테이블을 생성하여 데이터를 INSERT합니다.

2.INSERT INTO SELECT 사용법

   INSERT INTO 구문은 원본과 대상테이블이 모두 있을 경우 사용합니다.

   TABLE A에서 모든 데이터를 가져와 B라는 테이블에 INSERT 하고 싶습니다.

   INSERT INTO B SELECT * FROM A

   위에서 TABLE A와 TABLE B는 스키마가 동일해야 합니다.

   만일 A보다 컬럼수가 적을 경우에는

   INSERT INTO B SELECT COL1,COL2,COL3 FROM A

   와 같이 사용할 수 있습니다.