Table 을 purge 를 이용하여 드롭하였을 경우 플래시백 테이블 명령을 통해 복구할 수 없다. 우연찮게도 필요 없을 줄 알고 깨끗하게 지우기 위해 drop table tablename purge 를 사용하였는데, 갑자기 현업에서 그 테이블의 데이터가 필요하다고 요청이 오면 적지 않이 당황할 수 있다.
‘오라클’ 태그가 지정된 글
태그:오라클, partition, partition split, split
1. 테스트 파티션 테이블 생성
1 CREATE TABLE SPLIT_TEST (EMPNO NUMBER(10), NAME VARCHAR2(30)) 2 PARTITION BY RANGE(EMPNO) 3 (PARTITION SPLIT_TEST_P1 VALUES LESS THAN (10), 4 PARTITION SPLIT_TEST_P2 VALUES LESS THAN (20), 5 PARTITION SPLIT_TEST_P3 VALUES LESS THAN (30), 6* PARTITION SPLIT_TEST_MX VALUES LESS THAN (MAXVALUE)) SQL> / Table created.
CASE 1. Two-Phase Commit 인터럽트
ORA-02050: TRX ID rolled back, some remote dbs may be in-doubt
ORA-02051: TRX ID committed, some remote dbs may be in-doubt
ORA-02054: TRX ID in-doubt
프로그램 사용중에 위 에러중 하나가 발견되면 그 트랜젝션에 대한 정보가 자동으로 저장되어 이 정보는 후에 분산 트랜젝션에 대한 Manual Recovery 에 사용될 수 있다.
DBA는 특별한 Action 을 취할 필요가 없다. RECO(Recovery) process 가 Session tree 의 모든 노드에서 같은 결과가 발생되도록 In-doubt 트랜젝션을 자동으로 Recovery 한다(All commit or All rollback). 그러나 장기적인 Power Failure 의 경우 Lock 된 리소르를 해제하기 위해 강제적으로 Commit 이나 Rollback 을 시켜준다.
SQL> COMMIT FORCE ‘SALES.ACME.COM.55dic563.193.29’; <OR>
SQL> COMMIT FORCE ‘global_id’,228444991
SQL> ROLLBACK FORCE ‘2.9.4’;
CASE 2. Lock 에 의해 데이터 접근을 금지시키는 Fail
ORA-02049: time-out : distributed transaction waiting for lock
로컬의 DML 문장이 원격의 다른 트랜젝션에 의해 Lock 이 되어있는 데이터를 수정 혹은 삭제를 시도하려 할 때 Time-out 이 발생하고 해당 명령은 Rollback 된다.
SQL>update dept set deptno = 10;
SQL> update dept set deptno = 10;
SQL> update dept@REMOTE set deptno=10;
ORA-02049 : time-out
로컬에는 Update 가 발생하므로 항상 Rollback 을 시켜 주어야 하나, 원격은 변경할 수 없으므로 아무런 조치는 취하지 않아도 된다. 위 상황에서 Time-out 의 간격은 DISTRIBUTED_LOCK_TIMEOUT 파라미터로 시간을 조정할 수 있다.
ORA-01591 : Lock held by in-doubt distributed transaction ID
In-doubt 트랜젝션이 락을 잡고 있는 리소스에 대하여 접근을 시도시 발생하는 에러.
SQL> update dept@remote set dname=’AA’;
— Remote Database down
ORA-02054 : transaction 2.1.207 is in-doubt transaction
SQL> select * from dept@remote;
ORA-01591 : Lock held by in-doubt distributed transaction id 2.1.207
로컬에서 DBA_2PC_PENDING 뷰를 통해 in-doubt 트랜젝션 정보를 확인할 수 있다.
이런 경우 SQL 문장은 Rollback 되어야 한다. Lock 이 계속해서 결려 있을 경우 Rollback 을 수행해 주어야 한다. Network 혹은 System Failure 가 빠르게 복구 될 경우 이 문제는 자동으로 해결 된다.
CASE 3. 두 Node 사이에 Connection 이 만들어 진 이후 Network Failure.
Forms 등을 통해 화면에서 데이터를 업데이트를 수행하면 네트워크와 원격 DB가 정상적일 경우 Commit 이 되나, 네트워크가 Fail 될 경우 화면이 Holding 상태로 멈춤.
네트워크가 복구되면 자동으로 Commit. 하지만 네트워크 Failure 가 장기화 될 경우 강제로 Abort 시켜주어야 한다. 강제 Abort 시 자동으로 Rollback 되므로 더이상의 조치는 필요치 않다.
Oracle Client / Server Interoperability Support
Use this note to understand Oracle client versions are supported to work with which versions of the Oracle Database.
This note gives a summary of the support for interoperability between Oracle client and server versions. This includes support for connections over database links between Oracle versions. Note that this relates to support for investigation of defects / ability to obtain bug fixes.Note that this is a general guide for interoperability only – certain products or utilities may impose additional restrictions on supported combinations specific to the product / utility. eg: Precompilers, Export / Import utilities etc..
For a summary of the support status of each Oracle release see Note:161818.1
Oracle’s general policy is to test and support each new Oracle release for compatibility with older releases thus:
- Test the NEW client to each OLDER server release where the OLDER release is still covered by Premier Support (formerly Primary Error Correction support) at the time that the NEW version is released.
- Test OLDER clients to the NEW server release where the OLDER release is still covered by Premier Support (formerly Primary Error Correction support) OR is in the first two years of Extended Support(formerly Extended Maintenance support).
- Tests between the NEW version and other OLDER releases may be added where Oracle deems it sensible to support interoperability between those releases.
Current Interoperability Support Situation
The matrix below summarizes client and server combinations that are supported.New interoperability problems will only be investigated if BOTH releases involved are covered by a valid support contract at the time that the issue is reported .
A 10.1.0 client to an 11.1.0 server issue requires the customer to have a valid Extended Support contract for the 10.1.0 client in order for Oracle to investigate it.
Server Version Client Version 11.2.0 11.1.0 10.2.0 10.1.0 9.2.0 9.0.1 8.1.7 8.1.6 8.1.5 8.0.6 8.0.5 7.3.4 11.2.0 Yes Yes ES #7 No LES #5 No #3 No #3 No #3 No #3 No #3 No #3 No #3 11.1.0 Yes Yes ES #7 ES #6 LES #5 No #3 No #3 No #3 No #3 No #3 No #3 No #3 10.2.0 ES #7 ES #7 ES ES LES #5 No Was No #3 No #3 No #3 No #3 No #3 10.1.0(#4) ES #6 ES #6 ES ES LES Was Was #2 No #3 No #3 No #3 No #3 No #3 9.2.0 LES #5 LES #5 LES #5 LES LES Was Was No No Was No No #1 9.0.1 No No No Was Was Was Was Was No Was No Was 8.1.7 No No Was Was Was Was Was Was Was Was Was Was 8.1.6 No No No No No Was Was Was Was Was Was Was 8.1.5 No No No No No No Was Was Was Was Was Was 8.0.6 No No No No Was Was Was Was Was Was Was Was 8.0.5 No No No No No No Was Was Was Was Was Was 7.3.4 No No No No Was Was Was Was Was Was Was Was
Yes Supported LES Supported but fixes only possible for severity 1 issues for customers with Extended Support . Limitations apply – see click here for policy details for 9.2. ES Supported but fixes only possible for customers with Extended Support . Was Was a supported combination but one of the releases is no longer covered by any of Premier Support , Primary Error Correct support , Extended Support nor Extended Maintenance Support so fixes are no longer possible. No Has never been Supported
- #1 – See Note:207319.1
- #2 – An ORA-3134 error is incorrectly reported if a 10g client tries to connect to an 188.8.131.52 or lower server. See Note:3437884.8 .
- #3 – An ORA-3134 error is correctly reported when attempting to connect to this version.
- #4 – There are problems connecting from a 10g client to 8i/9i where one is EBCDIC based. See Note:3564573.8
- #5 – For connections between 10.2 (or higher) and 9.2 the 9.2 end MUST be at 184.108.40.206 or higher. Connections between 10.2 (or higher) and 220.127.116.11, 18.104.22.168 or 22.214.171.124 are not supported.
- #6 – For connections between 11.1 (or higher) database server and 10.1 / 10.2 database server across a database link the 10g end MUST be at 10.1.0.5 / 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. (This does not apply to normal 11g clients to 10g database server only server to server and a few very specific client products, such as Oracle Forms). See Note:4511371.8for more details.
- #7 – For database link connections between 11.1 (or higher) and 10.2 the 10g end MUST be at 10.2.0.2 (or higher) in order to use PLSQL between those versions. See Note:4511371.8 for more details.
- For database links between different Oracle versions connections must be supported in BOTH directions in the matrix above.
eg: As 11.2 -> 10.1 is not supported then database links between these version are not supported in either direction.
- Unsupported combinations may appear to work but can encounter errors for particular operations. The fact that they appear to work should not be relied upon – issues on unsupported combinations will not be investigated.
- Since new database servers are compatible with a limited set of older OCI clients, it may not be necessary to upgrade the client software when upgrading the database. However, some new features may not work without upgrading the client software. So, for example, an Oracle 10.2 client is able to connect to an 11.2 database, but is not able to take advantage of newer features such as Client Result Cache (introduced in 11.1).
- Oracle Applications , or other Oracle products, may have supported configurations not listed in the matrix above.
- The matrix above also applies between different platforms and between 32/64 bit releases of Oracle client / server except where any Oracle platform desupport notice indicates otherwise .
- Unix BEQUEATH (BEQ) connections are NOT supported between different releases. eg: Client 10.2 is not supported to make an Oracle Net connection to a 11.2 server using the BEQ protocol adapter regardless of the interoperability support listed above. See Note:364252.1 for more details.
See the “Terminology” section of Note:161818.1 for a description of Premier Support, Extended Support, Primary Error Correction Support and Extended Maintenence Support
- JDBC, JDK, and Oracle Database Certification Note:401934.1
For JDBC clients information in Note:401934.1 takes priority over information in the above matrix.
- Support Status of each Oracle Server (RDBMS) Release Note:161818.1
- For Precompiler interoperability support also see:
“Pro*C/C++ Programmer’s Guide 11g Release 2 (11.2)” [Part Number E10825-01]
-> Frequently Asked Questions
-> Can I Use Any Release of Pro*C/C++ with Any Version of the Oracle Server?
- Export / Import Compatibility Note:132904.1