에너쓰오라클

Oracle 12c 부터 RAW device가 desupport 됩니다. 

Oracle 12c, Desupport for Raw Storage Devices

어찌어찌 해서 raw device를 사용한채로 upgrade를 한다해도 raw device를 direct로 사용하면 오류 발생할 거라네요. 

12c로 upgrade 할땐 ASM으로의 migration 까지도 염두해 둬야 되겠네요.. OTL

언듯 raw device 별로 diskgroup을 만들어 tablespace를 생성하는 꼼수가 머리를 스치는 군요 .. ㅋ 

Starting with Oracle Database version 12.1 (release date TBD), support for storing data files, spfiles, controlfiles, online redo logfiles, OCRs and voting files on raw devices directly will end. This means commands such as the following will report an error while attempting to use raw devices directly in Oracle Database Version 12.1:

SQL> create tablespace ABC DATAFILE '/dev/raw/raw1' size 2GB;

Note that while the direct use of raw devices will be de-supported for Oracle Database 12.1, customers can choose to create Oracle ASM diskgroups on top of raw devices. While it is recommended to store all shared files on ASM diskgroups, storing those files on NFS or certified cluster file systems remains supported.

The following SQL commands will not return an error while attempting to use raw devices. Reason: the raw devices in the example below are used indirectly via Oracel ASM (no direct use of raw devices here):

SQL>alter diskgroup MYDG add disk '/dev/raw/ABC1.dbf';

 OR

SQL>create diskgroup MYDB EXTERNAL REDUNDANCY disk 'dev/raw/ABC1.dbf'

Then use the following command to create the tablespace

SQL> create tablespace ABC DATAFILE '+MYDG' size 2GB;

If raw devices are not being used directly in the current release then no further actions need to be taken. However, if raw devices are being used directly currently, then planning should be performed to migrate respective files off raw devices. There are many choices currently to replace raw devices, including Oracle ASM, NFS, and supported cluster file systems.

참고 : Announcement of De-Support of using RAW devices in Oracle Database Version 12.1 (Doc ID 578455.1)

DataPump를 이용한 간단한 마이그레이션 절차입니다. 

1. schema level export 

# expdp \'/ as sysdba\' directory=mig dumpfile=scott.dmp log=scott_export.log schemas=scott

Export: Release 11.2.0.3.0 - Production on Wed Jan 21 18:24:16 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=scott_export.log" Location: Command Line, Replaced with: "logfile=scott_export.log"

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=mig dumpfile=scott.dmp logfile=scott_export.log schemas=scott reuse_dumpfiles=true

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 384 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."TEST_NLS"                          5.320 KB       1 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP64"  6.710 KB       1 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP67"  6.734 KB       2 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP72"  6.710 KB       1 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP76"  6.710 KB       1 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP61"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP62"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP63"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP65"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP66"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP68"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP69"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP70"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP71"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP73"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP74"      0 KB       0 rows

. . exported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP75"      0 KB       0 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/pump/scott.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:24:32

2. ddl 추출 

# impdp \'/ as sysdba\' directory=mig dumpfile=scott.dmp sqlfile=cr_scott.sql

Import: Release 11.2.0.3.0 - Production on Wed Jan 21 19:27:32 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=mig dumpfile=scott.dmp sqlfile=cr_scott.sql

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 19:27:36

3. DDL 수행

SQL> @cr_scott

4. import 

# impdp \'/ as sysdba\' directory=mig dumpfile=scott.dmp logfile=scott_imp.log ignore=y

Import: Release 11.2.0.3.0 - Production on Wed Jan 21 19:29:22 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=mig dumpfile=scott.dmp logfile=scott_imp.log table_exists_action=append

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTT" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Table "SCOTT"."SALES_RANGE_HASH" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Table "SCOTT"."TEST_NLS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TEST_NLS"                          5.320 KB       1 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP76"  6.710 KB       1 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP61"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP62"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP63"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP65"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP66"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP68"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP69"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP70"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP71"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP73"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP74"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q4"."SYS_SUBP75"      0 KB       0 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q1"."SYS_SUBP64"  6.710 KB       1 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q2"."SYS_SUBP67"  6.734 KB       2 rows

. . imported "SCOTT"."SALES_RANGE_HASH":"SALES_Q3"."SYS_SUBP72"  6.710 KB       1 rows

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:29:28

Oracle 12c 부터 session-specific GTT(Global Temporary table)에 대해 세션 개별의 통계정보 수집이 가능합니다. 

GTT의 경우 성능이슈로 permanent table을 만들어 drop 하는 등의 작업을 많이 했는데 

12c의 경우 session-private statistic 수집으로 이러한 이슈들이 해결되지 않을까 기대해 봅니다. 

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH', tabname=>'TEMP_GTT');

but, Parallel DML (update, delete, merge)에 대한 제약은 여전히 11g와 동일하네요..

Session-Private Statistics for Global Temporary Tables

Traditionally, global temporary tables had only one set of statistics that were shared among all sessions even though the table could contain different data in different sessions. In Oracle Database 12c Release 1 (12.1), global temporary tables now have session-private statistics. That is a different set of statistics for each session. Queries issued against the global temporary table use the statistics from their own session.

Session-private statistics for global temporary tables improves the performance and manageability of temporary tables. Users no longer need to manually set statistics for the global temporary table on a per session basis or rely on dynamic sampling. This reduces the possibility of errors in the cardinality estimates for global temporary tables and ensures that the optimizer has the data to identify optimal execution plans.

Oracle 12c에 Exadata를 위한 System Statistics 수집 기능 추가되었습니다. 근데 이건 11gR2(exadata X2)에서도 해줘야 됬던 기능인거 같은뎅..  

참고 : http://kerryosborne.oracle-guy.com/2013/09/system-statistics-exadata-mode/

암튼 system statistic 수집은 (workload statistics mode) 아래의 system 성능 정보를 수집하며 SQL optimizer가 SQL Plan 수립에 이 system 성능정보를 참고하므로 꼭 한번은, 그리고 system hardware 변경시에 수행해야 합니다..

• Single and multiblock read times
• mbrc
• CPU speed (cpuspeed)
• Maximum system throughput
• Average slave throughput

system statistics 수집 방법 

-- 특정 주기동안 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start') 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop') 

-- 특정 interval(분단위) 동안 수집 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N)

-- exadata 성능 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('exadata')

-- noworkload statistics 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS()

Enhancements to System Statistics

System statistics allow the optimizer to account for the hardware on which the database system is running. With the introduction of smart storage, such as Exadata storage, the optimizer needs additional system statistics in order to account for all of the smart storage capabilities.

The introduction of the new system statistics gathering method allows the optimizer to more accurately account for the performance characteristics of smart storage, such as Exadata storage.

Tom Kyte has picked his top 12 features of Oracle Database 12c and put them into a presentation. Here are his picks:

Tom Kyte, Vice President of Oracle, shares the top 12 features of 12c Databases at the Oracle Database 12c Launch 2013.

1. Even better PL/SQL from SQL

2. Improved defaults

3. Increased size limits for some datatypes

4. Easy top-n and pagination queries

5. Row pattern matching

6. Partitioning improvements

7. Adaptive execution plans

8. Enhanced statistics

9. Temporary undo

10. Data optimization capabilities

11. Application Continuity and Transaction Guard

12. Pluggable databases

On Oracle Database 12c, Part 1

On Oracle Database 12c, Part 2

Parallel Query로 수행되는 SQL에 문제가 있을경우

QC session과 PQ session 모두 tracing 하는 방법입니다. 

PQ session 찾아 헤메일 필요 없이 !! 

1. 현재 세션에 ID 부여 (PQ1) 

SQL> exec dbms_session.set_identifier(client_id => 'PQ1');

PL/SQL procedure successfully completed.

2. PQ1으로 정의한 현재 세션에 SQL_TRACE 설정 

SQL> exec dbms_monitor.client_id_trace_enable(client_id => 'PQ1', waits => true, binds => false);

PL/SQL procedure successfully completed.

3. Parallel query 수행 

SQL> select /*+ parallel(a,10) */ count(*) from customers a ..

4. SQL_TRACE 설정 제거 

SQL> exec dbms_monitor.client_id_trace_disable(client_id => 'PQ1');

PL/SQL procedure successfully completed. 

Oracle 12c부터는 DBMS_QOPATCh package를 통해서 sqlplus 상에서 patch 정보를 확인할 수 있답니다..

Database node가 많은 RAC 환경에서는 편할 수 있겠네요.. 

Queryable Patch Inventory

Using DBMS_QOPATCH, Oracle Database 12c provides a PL/SQL or SQL interface to view the database patches that are installed. The interface provides all the patch information available as part of the OPatch lsinventory -xml command. The package accesses the Oracle Universal Installer (OUI) patch inventory in real time to provide patch and patch meta information.

Using this feature, users can:

• Query what patches are installed from SQL*Plus.

• Write wrapper programs to create reports and do validation checks across multiple environments.

• Check patches installed on Oracle RAC nodes from a single location instead of having to log onto each one in turn.

참고 : 

http://oracle-randolf.blogspot.kr/2014/05/12c-hybrid-hash-distribution-with-skew.html

http://www.oaktable.net/content/12c-hybrid-hash-distribution-skew-detection-handling-failing

Oracle 12c RMAN의 new feature 중 하나인 table recovery 

아래 table recovery 방법을 보니 AUXILIARY Database를 만들어 거기서 dump datafile을 뽑아내는 모양새. 

과거에 삭제된 table 복구를 위해 수행되었던 복구 절차를 명령어 한줄로 만들어 놓았네요. ㅎ

Oracle 12c에서 많이 밀고 있는 Multitenant, ADO 등의 New feature 외에 소소한 몇몇 feature 들... 

어디다가 쓸진 모르겠지만.. 

이로써 invisible 기능으로 index, row (12c new feature - valid time temporal), column을 숨길수 있게됬군요.. ㅋ

view를 table로 export 할 수 있는 기능. 

업무에선 어떻게 쓰일수 있을지 모르겠지만.. 

DB 성능관련 view 들도 table로 간단히 뽑아낼 수 있다면 성능 history 구축하긴 쉽겠네요. 

Question: Can DD, TAR or other OS tools be used to back up a database on storage managed by ASM?

Answer: DD, Tar and other OS tools are not supported with ASM. The answer is use Rman with ASM. Matrix for Supported backup or clongin Options:

-----------------------X
|  DD           |  No  |
-----------------------| 
|  Atomic Snaps | Yes  |
-----------------------|
|  TAR          |  No  |
-----------------------|
|  RMAN         | YES! |
-----------------------X

Automic snaps are split mirror technologies which support atomis splits across several LUN as a consistant point in time copy. Products like TimeFinder or MirrorView. Database must be placed in HOT BACKUP mode for this type of backup for the duration of the split. This is because the split can not capture a block-consistent view of all datafiles while the database is open for write. The split is typically a few seconds so the performance impact is minimal.

Oracle 12c에 Oracle Multitenant라는 새로운 개념이 등장했습니다. 

Database가 Container DB와 Pluggable DB로 나뉘어 Cloud 환경에 적합한 모양으로 구성 가능합니다. 

자세한 내용은 아래 링크 참조하시면 될 것 같고..

http://www.oracle.com/technetwork/database/multitenant/overview/index.html

Oracle 12c Multitenant라는 기능을 보다 보니까 내가 현재 어떤 DB에 접속하고 있는지 헤깔리기 쉽겠더군요..

PDB (Pluggable DB)를 내리려다 잘못해 Container DB를 내리면 해당 Container DB에 있는 PDB까지 다 내려갈 수 있으니 (걱정도 팔자임), 이는 큰일이 아닐 수 없습니다. ㅋ

해서 SQLPLUS의 prompt에 현재 어떤 DB에 접속하고 있는지 표시하도록 간단히 맹글어 보았습니다. 

모두 아시는 $ORACLE_HOME/sqlplus/admin/glogin.sql에 아래 내용을 넣으시면 됩니다. 

아래처럼 container DB에 접속하던 Pluggable DB에 접속하던 prompt 상에서 보여주게 됩니다. 

그러나 DB 가 내려가 있는 상태에서 접속하면 'NOT CONNECTED'로 나오는 부분은 수정 필요 --; 

Oracle 11g 때부터 raw device를 지원한다, 안한다 이야기가 많더니만,

드디어 Oracle 12c에서는 지원하지 않는다는 문장이 메뉴얼에 등장했네요. 

8.1.10.1 About Upgrading Oracle Database Release 10.2 or 11.1 and OCFS and RAW Devices

If you are upgrading an Oracle Database release 10.2.0.5 or release 11.1.0.7 environment that stores Oracle Clusterware files on OCFS on Windows or RAW devices, then you cannot directly upgrade to Oracle Database 12c. You must first perform an interim upgrade to Oracle Database release 11.2 and migrate the Oracle Clusterware files to Oracle Automatic Storage Management (Oracle ASM). Then you can upgrade from release 11.2 to Oracle Database 12c.

8.1.12 Desupport for Raw Storage Devices

Starting with Oracle Database 12c, block file storage on raw devices is not supported. You must migrate any data files stored on raw devices to Oracle ASM, a cluster file system, or Network File System (NFS).

This also affects the OCR and voting files for Oracle Clusterware. You cannot store the OCR or voting files on raw devices. Oracle Clusterware files must be moved to Oracle ASM before upgrading.

출처 : Oracle® Database Upgrade Guide 12c Release 1 (12.1)

(http://docs.oracle.com/cd/E16655_01/server.121/e17642/deprecated.htm#UPGRD60124)

이제 ASM을 공부해야 할 시간 ... 

Oracle ASM Strategic Best Practices

SQL 관련 문제 발생시 문제 분석이나 Oracle SR 진행시 가장 어려운 부분은 문제 재현입니다. 

이미 분석되어 널리 알려지거나 오류 등에 의한 trace, dump 등이 있다면 문제 재현없이 진행해 볼수 있겠으나, 

그렇지 않은 경우 SR에서 권고하는 각종 diag patch 등을 적용해 다음 문제 발생때 까지 기다려야 할 수 도 있습니다. 

또 문제가 재현된다고 해도 이걸 잘 포장해서 넘기는 것도 일입니다.

다른 서버에서 재현 실패해서 직접 재현해 놓은 서버에 접속해 테스트와 분석이 진행되기도 합니다. 

Oracle 11g에서 이러한 SQL 관련 문제 발생시 SQL 중심으로 object 상태 정보를 수집할 수 있는 diag tool을 하나를 끼여 넣었는데, 그게 SQL Test Case Builder 입니다. 이 SQL Test Case Builde(TCB)는 EM이나 sqlplus에서 Oracle package 형태로 수행되며 분석에 필요한 다음의 두가지 형태의 정보를 자동으로 수집합니다. 

사용방법은 간단히 directory 생성및 권한 부여 이후 package로 간단히 수행가능합니다.  

자세한 예제나 설명은 아래 오라클 블로그에서 읽어보세요. 

Oracle keeps closing my TAR because I cannot provide a testcase, can you help?