Intermittent TNS-12516 or TNS-12519 or TNS-12520 errors when connecting via Oracle Net (Listener) ( Doc ID 240710.1 ) 

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

CAUSE 

By way of instance registration, PMON is responsible for updating the listener 

with information about a particular instance such as load and dispatcher 

information. Maximum load for dedicated connections is determined by the 

PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE 

information varies according to the workload of the instance. The maximum 

interval between these service updates is 10 minutes. 

The listener counts the number of connections it has established to the instance 

but does not immediately get information about connections that have terminated. 

Only when PMON updates the listener via SERVICE_UPDATE is the listener 

informed of current load. Since this can take as long as 10 minutes, there can be 

a difference between the current instance load according to the listener 

and the actual instance load. 

When the listener believes the current number of connections has reached maximum 

load, it may set the state of the service handler for an instance to "blocked" 

and begin refusing incoming client connections with either of the following 

errors: 

TNS-12516 TNS:listener could not find instance with matching protocol stack 

TNS-12519 TNS:no appropriate service handler found 

Additionally, an ORA-12520 error may appear in the listener log. 

The output of the LSNRCTL services command will likely show that the service handler is "blocked". 

e.g. '"DEDICATED" established:1 refused:0 state:blocked' 

SOLUTION 

Increase the pfile or spfile setting for PROCESSES. 

일반적으로 PROCESSES 설정에 하나의 User Process 에서 파생되는 Parallel 작업이나, 

Recursive SQL을 수행하기 위한 session fork 는 감안되어 있지 않습니다. 

따라서, 숫자상으로 정확히 맞아 떨어 지지는 않습니다. 

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams169.htm#REFRN10175 

PROCESSES 

http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2050.htm#REFRN30206 

V$RESOURCE_LIMIT 

관련 문서 및 노트를 확인해 보았을때, 현재로써는 PROCESSES를 증가를 검토 하여 주시기 바랍니다. 

유휴 상태 세션 찾기

select sid, serial#, username, trumc(last_call_et/3600,2)||'hr'last_call_et

from v$session

where last_call_et>7200 and username is not null

- last_call_et 는 "초" 단위이므로 7200 이면 2시간이된다.

유휴 상태 세션 죽이기

alter system kill '<sid>,<seril#>'

TNS-12520: TNS:listener could not find available handler for requested type of server

TNS-12520: TNS:listener could not find available handler for requested type of server

Cause: None of the known and available service handlers for requested type of server (dedicated or shared) are appropriate for the client connection.

Action: Run "lsnrctl services" to ensure that the instance(s) have registered with the listener and that the appropriate handlers are accepting connections.

...

14-JAN-2016 13:45:03 * nsevwait_error * 0

14-JAN-2016 13:45:03 * nsevwait_error * 0

....

MS SQL to Oracle using SQL Developer

By. Louis Kim.

1. 목적

- MS SQL 의 데이터를 Oracle 로 Migration

- 10g 및 11g 테스트 진행 / MS SQL Server 2008 R2

2. 테스트 장비 및 Tool Version

- 10.2.0.5 / sqldeveloper64-3.0.04.34-no-jre

- 11.2.0.4 / sqldeveloper-4.1.3

3. SQL Developer 환경셋팅 (모든 SQL developer 버전 동일)

- SQL Developer 에서 MS SQL 로 접속하기 위해서 JDBC 드라이버 추가 필요

- jtds-1.3.1-dist.zip (http://sourceforge.net/projects/jtds/) 파일을 다운 받아서 압축풀기

- sql developer -> 도구 -> 환경설정 -> 데이터베이스-> 타사 JDBC 드라이버 -> 항목 추가 (압축 푼 Jar 파일 등록)

4. Oracle 접속 및 MS SQL 접속

※ Oracle 권한은 가급적 DBA 권한을 부여하여 진행 (Migration 작업 시 오류 발생하며, 제대로 작업이 진행되지 않음)

- 2개의 DB에 모두 동시에 접속이 가능

- MS SQL Server 접속

- Oracle 접속

- 접속 확인

5. 저장소 생성(Repository)

- Migration 하기 전에 저장소를 반드시 생성

- 저장소는 메타 데이터를 수집 및 변화를 해주는 작업을 해주는 곳

The Migrations Repository is a database schema that stores the meta_data collected and transformed in the migration process.

- 저장소 생성(저장소는 Oracle 에서 생성)

 저장소를 생성할 신규 유저(DBA권한을 보유한 유저)로 진행

CREATE USER MIG_USER IDENTIFIED BY MIG_USER DEFAULT TABLESPACE USERS;

GRANT CONNECT, RESOURCE, DBA TO MIG_USER;

 생성한 유저(MIG_USER) 로 접속한 후 저장소 생성

 도구[탭] -> 이전 -> 저장소 관리 -> 저장소 생성

 저장소 생성 시 해당 접속 정보(11g_MIG_USER)로 생성

6. Migration 진행

6-1. MS Server To Oracle 11g

A. Migration 하려는 MS sql 데이터 베이스를 우클릭 하여 [Oracle 이전]을 선택

B. [이전 마법사] 대로 진행

 저장소의 경우 앞에서 진행하면서 생성한 저장소를 선택

 이름 및 폴더 디렉토리를 지정(Log가 저장)

 로그 폴더에 Migration script가 생성 및 저장

 온라인으로 진행 시 현재 MSSQL DB의 내용을 가져오게 되며, 오프라인으로 진행 시 Dump파일을 이용하여 MSSQL DB의 내용을 가져오게 된다. (MSSQL 의 dump는 *.ocp 파일을 선택)

 데이터 Type을 정할 때 기준으로 삼는 데이터베이스 선택

 Data type을 확인 후 변경이 필요할 경우 변경

참고) 블로그1

DATETIME -> TIMESTAMP(6) 에서 DATETIME -> DATE 로

BIGINT -> NUMBER(10,0) 에서 BIGINT -> NUMBER(19,0)

 변환할 Object들을 선택

 온라인으로 하는 경우 Oracle에 스크립트가 생성

 오프라인으로 설정할 경우 생성 스크립트가 설정한 디렉토리에 저장

 온라인으로 진행하는 경우 Oracle에 데이터가 자동으로 저장

 오프라인으로 하는 경우 저장소에서 만든 경로에 스크립트가 생성

 마지막으로 확인 및 진행

 완료

 확인(User 생성 여부)

 Table 및 객체 생성 확인

 - Migration 이 완료 되면 우측과 같이 저장소에 해당 내역이 저장되어 있는 것을 확인 가능

 - Oracle 에서는 Mig 라는 유저가 생성되어 생성된 유저로 MS SQL 의 정보들이 migration 이 된 것을 확인

6-2. MS Server To Oracle 10g

 10g와 동일한 방법으로 진행 하되, 다음과 같은 버전으로 진행(하위버전) sqldeveloper64-3.0.04.34-no-jre

7. 비고

A. 설정 시 문제 사항

요청한 작업을 수행하는 중 오류 발생:

I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

위의 jtds-1.3.1-dist.zip 을 푼 파일에서 \jtds-1.3.1-dist\x64\SSO 내의 ntlmauth.dll 를 C:\Windows\System32 내에 붙여 넣은 후 종료 후 다시 실행하여 진행

SQL Developer 실행 시 "Unable to create an instance of the Java Virtual Machine error" 오류 발생 : "SQL Developer 설치 폴더"\ide\bin 폴더 내의 ide.conf 파일을 편집기로 열어서 다음과 같이 수정

AddVMOption -Xmx256M 

SQL Developer 실행 후 브라우저 창이 안 보이는 경우 : 윈도우 탐색기 옵션에서 "보호된 운영 체제 파일 숨기기"을 해제해야 하고 "숨김 파일 및 폴더 표시"을 설정

"C:\Users\사용자 계정명\AppData\Roaming" 폴더 내의 "SQL Developer" 폴더 삭제 후 다시 SQL Developer 실행

B. 추가 시 작성

Block

- Block 단위로 작업 (Defualt 8K)

- DB_BLOCK_SIZE 파라미터는 Create Database 하면서 지정이 되며, Database를 재생성하기 전까지는 변경이 불가능(Standard block size)

- 혹시라도 DB_BLOCK_SIZE 외의 size로 지정하기 위해서는 Buffer Cache 에도 해당 Block size 만큼 공간을 미리 할당해 두어야 함(아니면 에러가 발생)

  ex) SQL> create tablespace test_4k datafile 'D:\ora_data\orcl\test_4k.dbf' size 5m blocksize 4k;

ERROR at line 1:

ORA-29339: tablespace block size 4096 does not match configured block sizes

SQL> alter system set db_4k_cache_size=10m; -> DB Cache에 미리 공간을 할당

Data block 구조

-- Block 정보에 대한 size 와 type 확인(Default)

select component, type, description, type_size 

from v$type_size

where component in ('KCB', 'KTB');

- Block Header

1. Transaction layer

A. Transaction layer-fixed

 - Block Type

 - 마지막으로 수행된 Block cleanout 시간

 - ITL Entries 정보

 - Free list link 정보

 - Free space lock 정보

  B. Transaction layer-variable

    - 24byte를 가짐

    - Block 에 있는 Row 를 변경하기를 원하는 실제 ITL 관련 정보 저장

      - ITL (Interested Transaction) 특정 Block 에 데이터를 변경해야 할 경우 해당 Block에 사용자 명단에 자신의 정보(TxID)를 적고 변경

        (사용하는 대기자 명단)

      - 가변성 으로써 기본으로 Table에 대한 1개 / Index에 대한 2개의 ITL 정보

      - 테이블이나 인덱스를 생성할 때 INITRANS 파라미터의 값을 변경하면 그 기본값을 변경할 수 있음

2. Cache layer

  - Data Block Address(DBA)

  - Block Type (ex- Table, Index, Undo..)

  - Block Format(v6, v7, v8...)

  - System Change Number(SCN: 복구 작업 등에 사용됨)

PCTFREE 와 PCTUSED

PCTFREE (Block의 Defualt 10%)

  - Block에 입력되어 있는 데이터들이 Update될 경우를 대비해 예약을 해 두는 공간(오직 Update)

  - update 했을 때 행의 크기 증가에 대비해 여유 공간을 만듦

PCTUSED (Block의 Defualt 40%)

- Block에서 row가 삭제되는 경우 Dirty List에 등록된(Free Block 에서 Dirty Block이 된) Block이, 

 Free Block 되는 상태까지 남은 용량이 얼마나 되는 지 결정하는 값

- Dirty Block이(block 들이 모여서) Free Block 의 상태까지 될 수 있는 값

  - PCTUSED 에 설정된 퍼센트 값만큼 남아서 Free space가 확보 되면 이 Block을 Free Block 으로 설정되어 Insert가 가능

※ PCTREE를 많이 주면 Update 가 많이 발생할 경우 좋지만 update가 자주 발생 안되면 비효율적인 공간이 됨

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

기본 pctfree 10 -> 5로 변경 (update 문이 적을 경우 괜찮을 듯..)

--기존 Table들에 대한 정보

select

   table_name, owner, pct_free, pct_used, num_rows

from

   dba_tables

where 

avg_row_len > 1

and 

avg_row_len < .5*&blksz

and

table_name not in

 (select table_name from dba_tab_columns b

   where

 data_type in ('RAW','LONG RAW','BLOB','CLOB','NCLOB')

 )

and owner not like '%SYS%'

order by 

   owner, 

   table_name

;

rem pctused.sql

set heading off;

set pages 9999;

set feedback off;

--보통 8k 기본(8192)

column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name='db_block_size';

define spare_rows = 2;

select

   ' alter table '||owner||'.'||table_name||

   ' pctused '||least(round(100-((&spare_rows*avg_row_len)/(&blksz/10))),95)||

   ' '||

   ' pctfree '||greatest(round((&spare_rows*avg_row_len)/(&blksz/10)),5)||

   ';'

from

   dba_tables

where 

avg_row_len > 1

and 

avg_row_len < .5*&blksz

and

table_name not in

 (select table_name from dba_tab_columns b

   where

 data_type in ('RAW','LONG RAW','BLOB','CLOB','NCLOB')

 )

and owner not like '%SYS%'  --SYS 관련 계정 제외

order by 

   owner, 

   table_name

;

Row

  1. Row Header(Overhead)

    - Row에 동시에 Transaction 을 일으키는 것을 막기 위한 Lock 정보 기록

    - Row piece에 들어있는 Column Data의 개수 기록

  2. Column Data

    - 실제로  Data가 저장되는 곳

Row Chaining

  - Block의 Column Data 부분에 모두 기록할 수 없을 경우, Row를 다른 Block 에 연결해서 저장

  - Row chaining 이 많이 일어나는 경우 Block 의 크기를 크게 해 주면 되지만 wait 현상이 많이 발생(Write complete waits, Buffer Busy Waits 등) 성능 저하 가능성

Row migration

  - 특정 Block에 위치하던 Row 가 update 등의 이유로 해당 Block 의 공간이 부족해서 다른 공간으로 이사를 가는 것을 의미

  - 해당 Block 에 빈 공간이 부족해서 일어나는 현상이므로 PCTFREE 값을 많이 주거나 테이블 재생성(Reorg) 작업을 통해 줄일 수 있음 

  - pctfree 가 큰 경우 update 가 많치 않을 경우 공간 비효율성 발생

※ I/O가 더 많이 일어나는 장점

Description

Trace files may be seen with warning of the form:

 WARNING:Could not lower the asynch I/O limit to 160 for SQL direct I/O. 

 It is set to -1 

This bug is specifically for the case of messages showing "It is set

to -1" in the message.

Workaround

Ignore the message - it is spurious and of no value.

The fix only hides the message so that it does not produce "noise"

 Asynchronous I/O는 processes들이 write를 한 후에 기다리지 않고 바로 다음 작업을 수행할 수 있도록 해주는    Input/Output mechanism입니다.

   Asynchronous I/O는 불필요하게 낭비되는 idle time을 최소화해서 system 성능을 향상시켜 줍니다.

   DBWR는 각각의 I/O에 방해받지 않습니다.

[출처] http://shadou.tistory.com/entry/ASYNCIO%EC%97%90-%EB%8C%80%ED%95%9C-%EA%B8%B0%EB%B3%B8%EC%A0%81%EC%9D%B8-%EC%A7%88%EB%AC%B8%EB%93%A4

1. Database 로 진행

- Tablespace 및 Datafile 확인

- DB shutdown 후 Mount 로 startup 진행

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  2079480 bytes

Variable Size             176162056 bytes

Database Buffers          427819008 bytes

Redo Buffers                6307840 bytes

Database mounted.

- 해당 Datafile 를 이동 후 rename 진행

SQL> alter database rename file 'E:\MASDBDEV\HG1.dbf' to 'E:\HG1.dbf';

- Database open 진행

SQL> alter database open;

Database altered.

- 확인

SQL> select file_name, tablespace_name from dba_data_files order by 2;

FILE_NAME                                TABLESPACE_NAME

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

E:\MASDBDEV\HG2.DBF                      HG

E:\HG1.DBF                               HG

E:\MASDBDEV\IMSI01.DBF                   IMSI

E:\MASDBDEV\SYSAUX01.DBF                 SYSAUX

E:\MASDBDEV\SYSTEM01.DBF                 SYSTEM

E:\MASDBDEV\TEST01.DBF                   TEST

E:\MASDBDEV\UNDOTBS01.DBF                UNDOTBS1

E:\MASDBDEV\USERS01.DBF                  USERS

2. Tablespace 로 진행

- Tablespace 및 Datafile 확인

- 옮기고자 하는 Datafile 의 Tablespace 를 offline 진행

SQL> alter tablespace HG offline;

- Offline 이 후 해당 Datafile 를 이동 후 rename 진행

SQL> alter tablespace HG rename datafile 'E:\HG1.dbf' to 'E:\MASDBDEV\HG1.dbf';

- Rename 까지 완료 되면 다시 online 진행

SQL> alter tablespace HG online;

- 옮겨 졌는지 확인 후 Test로 Talbe 를 Select 해 보자.

ERROR at line 1:

ORA-00376: file 7 cannot be read at this time

ORA-01110: data file 7: 'E:\HG1.DBF'

또한, SYSTEM, Rollback segment혹은 Temporary segment를 포함하는 테이블스페이스는 안되므로 이때는 Database로 진행

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

expdp system/oracle directory=dp dumpfile=user_151120.dmp logfile=user_log.log tables=user.table1, user.tabl2....

요청 받은 table 들은 truncate 로 삭제 진행

impdp system/oracle directory=dp dumpfile=user_151120.dmp logfile=user_log_imp.log content=data_only

ORA-31693: Table data object "유저명"."테이블명" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

ORA-39786: Number of columns does not match between export and import database

The errors occur when the ACCESS_METHOD is EXTERNAL_TABLE. This method is chosen if at least one of these conditions is met:

There is an active trigger

The table is partitioned

FGAC (Fine Grained Accesses Control) in insert mode

Referential integrity constraint exists

Unique index exists

Enabled constraint is present on pre-existing table

If any of these conditions apply, then you need to do import using DIRECT_PATH access method to solve the problem.

Follow either of the below workarounds to solve the issue:

o  Either drop the constraints/unique indexes on table before impdp is started and add access_method=direct_path to command line

- OR -

o  Rename the table before impdp, do the import and transfer the data per INSERT/SELECT to renamed table. Finally drop the imported table and rename table to old name. 

In 10g, DataPump chooses external tables for the access method to import data into a table with an unique index. On the other hand, in 11g the DataPump chooses the direct path access method even in this case. 

This issue specified in the Bug 4383811 Which has been closed as is not feasible to fix in 10g.

The ORA-39786 error is introduced (in 10.2.0.3.0) to avoid an ORA-600 error when importing data into the pre-existing table using the external tables access method.

OPatch failed with error code = 74

Distributed Transaction Coordinator

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Detail

This error is fairly self explanatory - we cannot get enough space for a temporary segment. 

The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

NOTE:

A "temp segment" is not necessarily a SORT segment in a temporary tablespace. It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.

eg: When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data. This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation. It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend it report ORA-1652 rather than an INDEX related space error.

-- Used_block 과 Total_block 이 같으면 temp 가 부족한 것을 확인 가능

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; 

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

SELECT * FROM DBA_TEMP_FILES;

When gathering GLOBAL statistics a large amount of TEMP space is needed in order to collect the necessary information.

해결방법

Gather the statististics at the partition level rather than the table level and derive the table level statistics from the partitions.

To do this gather using granularity => 'PARTITION'.

참고로 10g 이라서 아래도 참조할 부분이다.

This new value exists in 10.2.0.5 and for 10.2.0.4 after Patch 6526370 applied (see more in Note 6526370.8 Bug 6526370 - New GRANULARITY option for DBMS_STATS statistics on the table).

1. tempfiles을 추가, 현재 파일 사이즈를 증가하거나 혹은 auto extend를 활성화

2. 쿼리/구문을 튜닝하여 sort 작업들이 디스크가 아니라 메모리에서 수행(가장중요)

1. Temp 를 하나 더 추가 

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORADATA/TEMP02.DBF' SIZE 1000M AUTOEXTEND ON;

2.기본 Temp resize 

ALTER DATABASE TEMPFILE ''/ORADATA/TEMP01.DBF'' RESIZE 2000M;