Mart 2016 - Kamil TÜRKYILMAZ | Oracle BlogKamil TÜRKYILMAZ

11 Mart 2016 by Kamil TURKYILMAZ

Oracle 12c New Features : DBMS_PRIVILEGE_CAPTURE Package

Database’ in güvenliğinin sağlanması için belirli periyotlar arasında şirketlerin güvenlik birimleri aracılığıyla (özellikle bankalar bu konuda en hassas olan kurumlardır) database’ deki kullanıcıların yetkileri sorgulanır ve fazla olduğu düşünülen yetkilerin revoke edilmesi istenir. Ancak production sistemlerde yetki almak kimi aman çokda kolay olmamaktadır. Yetki revoke edildiğinde uygulamanın bu durumdan etkilenip etkilenmeyeceği, ilgili user tarafından bu yetkinin kullanılıp kullanılmadığı tam olarak kestirilemediği durumlar olabiliyor. Hal böyle oluncada yetkiyi revoke etmeye cesaret edilemediği durumlar yaşanmıyor değil 

Oracle 12c ile birlikte aslında bu soruna cevap bulunmuş oldu. Dbms_privilege_capture paketi ile kullanılan user hangi yetkinin kullanıldığını capture eden faydalı bir package’ dir. Privilege kullanıldığı zaman capture edildiğinden dolayı bu paket aracılığıyla toplanan yetkiler kullanılan yetkiler olarak yorumlanmalıdır.

Şimdi bu işlemi nasıl yapabileceğimiz den ve dbms_capture_privilege paketinin kullanımından bahsedelim.

DBMS_PRIVILEGE_CAPTURE paketinin spec’ ine baktığınız da 5 adet procedureden oluştuğunu görebilirsiniz.

CREATE OR REPLACE PACKAGE SYS.dbms_privilege_capture AS
 -- Capture Types
  g_database            CONSTANT NUMBER := 1;
  g_role                CONSTANT NUMBER := 2;
  g_context             CONSTANT NUMBER := 3;
  g_role_and_context    CONSTANT NUMBER := 4;
  PROCEDURE create_capture(
    name            IN  VARCHAR2,
    description     IN  VARCHAR2 DEFAULT NULL,
    type            IN  NUMBER DEFAULT G_DATABASE,
    roles           IN  role_name_list DEFAULT role_name_list(),
    condition       IN  VARCHAR2   DEFAULT NULL);
  PROCEDURE drop_capture(name  IN VARCHAR2);
  PROCEDURE enable_capture(name  IN VARCHAR2);
  PROCEDURE disable_capture(name IN VARCHAR2);
  PROCEDURE generate_result(name IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE SYS.dbms_privilege_capture AS

-- Capture Types

g_database CONSTANT NUMBER := 1;

g_role CONSTANT NUMBER := 2;

g_context CONSTANT NUMBER := 3;

g_role_and_context CONSTANT NUMBER := 4;

PROCEDURE create_capture(

name IN VARCHAR2,

description IN VARCHAR2 DEFAULT NULL,

type IN NUMBER DEFAULT G_DATABASE,

roles IN role_name_list DEFAULT role_name_list(),

condition IN VARCHAR2 DEFAULT NULL);

PROCEDURE drop_capture(name IN VARCHAR2);

PROCEDURE enable_capture(name IN VARCHAR2);

PROCEDURE disable_capture(name IN VARCHAR2);

PROCEDURE generate_result(name IN VARCHAR2);

END;

Continue reading →

11 Mart 2016 by Kamil TURKYILMAZ

ORA-65096: invalid common user or role name

Oracle 12c ile bilirkte container database kurulumu yaptıysanız artık yeni yapıda daha önceki versiyonlarda almadığınız bir takım hatalar alabilirsiniz. Örneğin 11g ve öncesinde çok basit bir user create işlemi olan aşağıdaki komut artık 12c ile birlikte aşağıdaki hatayı verir oldu.

12c ile birlikte yapı değiştiğinden dolayı artık create etmeye çalışılan user hangi pluggable database altına create edilecekse öncesinde bunu set etmemiz gerekiyor. Aslında bu hatanın bikaç tane çözüm yolu var öncesinde en optimum çözümden bahsedelim;

[oracle@Redhat70 ~]$ sqlplus / as sysdba 
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 09:32:55 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user kamil identified by kamil ; 
create user kamil identified by kamil
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

[oracle@Redhat70 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 09:32:55 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user kamil identified by kamil ;

create user kamil identified by kamil

ERROR at line 1:

ORA-65096: invalid common user or role name

Continue reading →

11 Mart 2016 by Kamil TURKYILMAZ

ORA-39183: internal error -19 ocurred during decompression phase 2

11gR2’ de olan bir oracle database’ inden 32 parallellik verilerek NFS olarak share edilmiş olan bir diske alınmış olan bir export backup’ı başka bir sistem üzerine import etmeye çalıştığımız da aşağıdaki gibi bir hata almıştık. Araştırdığımız da sorunun dmp file ‘ lerden birinde oluşan bir corruptiondan dolayı olduğunu gördük.

Çalışmaya ait logları ve ilgili hataya ait metalink notunu da aşağıda bulabilirsiniz.

nohup impdp \"/ as sysdba\"  DIRECTORY=EXPORT DUMPFILE=expdp_tpdb_full_%u.dmp logfile=impdp_full_tpdb.log full=Y parfile=exclude.par parallel=30  &

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":  "/******** AS SYSDBA"? DIRECTORY=EXPORT DUMPFILE=expdp_tpdb_full_%u.dmp logfile=impdp_full_tpdb.log full=Y parfile=exclude.par parallel=30 
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA [SELECT process_order, flags, xml_clob, NVL(dump_fileid, :1), NVL(dump_position, :2), dump_length, dump_allocation, NVL(value_n, 0), grantor, object_row, object_schema, object_long_name, partition_name, subpartition_name, processing_status, processing_state, base_object_type, base_object_schema, base_object_name, base_process_order, property, size_estimate, in_progress, original_object_schema, original_object_name, creation_level, object_int_oid FROM "SYS"."SYS_IMPORT_FULL_02" WHERE  process_order between :3 AND :4 AND duplicate = 0 AND processing_state NOT IN (:5, :6, :7) ORDER BY process_order] 
ORA-39183: internal error -19 ocurred during decompression phase 2
<!--more-->


ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1d9f806568     20462  package body SYS.KUPW$WORKER
0x1d9f806568      9028  package body SYS.KUPW$WORKER
0x1d9f806568      4185  package body SYS.KUPW$WORKER
0x1d9f806568      9725  package body SYS.KUPW$WORKER
0x1d9f806568      1775  package body SYS.KUPW$WORKER
0x1dbf528bb0         2  anonymous block

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA [SELECT process_order, flags, xml_clob, NVL(dump_fileid, :1), NVL(dump_position, :2), dump_length, dump_allocation, NVL(value_n, 0), grantor, object_row, object_schema, object_long_name, partition_name, subpartition_name, processing_status, processing_state, base_object_type, base_object_schema, base_object_name, base_process_order, property, size_estimate, in_progress, original_object_schema, original_object_name, creation_level, object_int_oid FROM "SYS"."SYS_IMPORT_FULL_02" WHERE  process_order between :3 AND :4 AND duplicate = 0 AND processing_state NOT IN (:5, :6, :7) ORDER BY process_order] 
ORA-39183: internal error -19 ocurred during decompression phase 2

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1d9f806568     20462  package body SYS.KUPW$WORKER
0x1d9f806568      9028  package body SYS.KUPW$WORKER
0x1d9f806568      4185  package body SYS.KUPW$WORKER
0x1d9f806568      9725  package body SYS.KUPW$WORKER
0x1d9f806568      1775  package body SYS.KUPW$WORKER
0x1dbf528bb0         2  anonymous block

Job "SYS"."SYS_IMPORT_FULL_02" stopped due to fatal error at 08:42:57

nohup impdp \"/ as sysdba\" DIRECTORY=EXPORT DUMPFILE=expdp_tpdb_full_%u.dmp logfile=impdp_full_tpdb.log full=Y parfile=exclude.par parallel=30 &

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Starting "SYS"."SYS_IMPORT_FULL_02": "/******** AS SYSDBA"? DIRECTORY=EXPORT DUMPFILE=expdp_tpdb_full_%u.dmp logfile=impdp_full_tpdb.log full=Y parfile=exclude.par parallel=30

Processing object type DATABASE_EXPORT/TABLESPACE

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA [SELECT process_order, flags, xml_clob, NVL(dump_fileid, :1), NVL(dump_position, :2), dump_length, dump_allocation, NVL(value_n, 0), grantor, object_row, object_schema, object_long_name, partition_name, subpartition_name, processing_status, processing_state, base_object_type, base_object_schema, base_object_name, base_process_order, property, size_estimate, in_progress, original_object_schema, original_object_name, creation_level, object_int_oid FROM "SYS"."SYS_IMPORT_FULL_02" WHERE process_order between :3 AND :4 AND duplicate = 0 AND processing_state NOT IN (:5, :6, :7) ORDER BY process_order]

ORA-39183: internal error -19 ocurred during decompression phase 2

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----

object line object

handle number name

0x1d9f806568 20462 package body SYS.KUPW$WORKER

0x1d9f806568 9028 package body SYS.KUPW$WORKER

0x1d9f806568 4185 package body SYS.KUPW$WORKER

0x1d9f806568 9725 package body SYS.KUPW$WORKER

0x1d9f806568 1775 package body SYS.KUPW$WORKER

0x1dbf528bb0 2 anonymous block

ORA-39183: internal error -19 ocurred during decompression phase 2

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPW$WORKER", line 9001

----- PL/SQL Call Stack -----

object line object

handle number name

0x1d9f806568 20462 package body SYS.KUPW$WORKER

0x1d9f806568 9028 package body SYS.KUPW$WORKER

0x1d9f806568 4185 package body SYS.KUPW$WORKER

0x1d9f806568 9725 package body SYS.KUPW$WORKER

0x1d9f806568 1775 package body SYS.KUPW$WORKER

0x1dbf528bb0 2 anonymous block

Job "SYS"."SYS_IMPORT_FULL_02" stopped due to fatal error at 08:42:57

Çözüm olarak import edilmeye çalışılan dmp file’ lerin ftp ile binary mode’ da import edilecek olan sunucuya tekrar taşınması ve import işleminin tekrar denenmesi gerekmektedir.

Reference;
DataPump Import Errors ORA-39183 Internal Error -19 Ocurred During Decompression Phase 2 ORA-6512 (Doc ID 1524577.1)

11 Mart 2016 by Kamil TURKYILMAZ

ORA-25226 dequeue failed queue is not enabled for dequeue

Alertlog file’ de aşağıdaki gibi queue ile ilgili hatalar alırsanız nasıl çözüm üretebileceğiniz ile ilgili scriptleri ve açıklamalarını aşağıda bulabilirsiniz ;

Alertlog’ a düşen hatalar ;

Tue May 12 10:52:38 2015
Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/pdtest/trace/pdtest_j002_28773.trc:
ORA-12012: error on auto execute of job "PDU"."FTS_MONITOR"
ORA-20000: ORA-25226: dequeue failed, queue PDU.OTS_FORWARD2 is not enabled for dequeue
ORA-06512: at "SYS.DBMS_AQ", line 335
ORA-06512: at "PDU.PACK_QUEUE", line 148
ORA-06512: at "PDU.PACK_QUEUE", line 382
ORA-06512: at "PDU.PACK_QUEUE", line 594
ORA-06512: at "PDU.PACK_QUEUE", line 604
ORA-06512: at line 1
Tue May 12 10:52:38 2015
Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/
pdtest/trace/pdtest_j000_30214.trc:
ORA-12012: error on auto execute of job "PDU2"."FTS_MONITOR"
ORA-20000: ORA-25226: dequeue failed, queue PDU2.OTS_FORWARD is not enabled for dequeue
ORA-06512: at "SYS.DBMS_AQ", line 335
ORA-06512: at "PDU2.PACK_QUEUE", line 148
ORA-06512: at "PDU2.PACK_QUEUE", line 534
ORA-06512: at "PDU2.PACK_QUEUE", line 597
ORA-06512: at "PDU2.PACK_QUEUE", line 604
ORA-06512: at line 1
Tue May 12 10:53:08 2015
Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/pdtest/trace/pdtest_j000_30214.trc:
ORA-12012: error on auto execute of job "PDU"."FTS_MONITOR"
ORA-20000: ORA-25226: dequeue failed, queue PDU.OTS_FORWARD2 is not enabled for dequeue
ORA-06512: at "SYS.DBMS_AQ", line 335
ORA-06512: at "PDU.PACK_QUEUE", line 148
ORA-06512: at "PDU.PACK_QUEUE", line 382
ORA-06512: at "PDU.PACK_QUEUE", line 594
ORA-06512: at "PDU.PACK_QUEUE", line 604
ORA-06512: at line 1
Tue May 12 10:53:08 2015
Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/pdtest/trace/pdtest_j002_28773.trc:
ORA-12012: error on auto execute of job "PDU2"."FTS_MONITOR"
ORA-20000: ORA-25226: dequeue failed, queue PDU2.OTS_FORWARD is not enabled for dequeue
ORA-06512: at "SYS.DBMS_AQ", line 335
ORA-06512: at "PDU2.PACK_QUEUE", line 148
ORA-06512: at "PDU2.PACK_QUEUE", line 534
ORA-06512: at "PDU2.PACK_QUEUE", line 597
ORA-06512: at "PDU2.PACK_QUEUE", line 604
ORA-06512: at line 1

Tue May 12 10:52:38 2015

Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/pdtest/trace/pdtest_j002_28773.trc:

ORA-12012: error on auto execute of job "PDU"."FTS_MONITOR"

ORA-20000: ORA-25226: dequeue failed, queue PDU.OTS_FORWARD2 is not enabled for dequeue

ORA-06512: at "SYS.DBMS_AQ", line 335

ORA-06512: at "PDU.PACK_QUEUE", line 148

ORA-06512: at "PDU.PACK_QUEUE", line 382

ORA-06512: at "PDU.PACK_QUEUE", line 594

ORA-06512: at "PDU.PACK_QUEUE", line 604

ORA-06512: at line 1

Tue May 12 10:52:38 2015

Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/

pdtest/trace/pdtest_j000_30214.trc:

ORA-12012: error on auto execute of job "PDU2"."FTS_MONITOR"

ORA-20000: ORA-25226: dequeue failed, queue PDU2.OTS_FORWARD is not enabled for dequeue

ORA-06512: at "SYS.DBMS_AQ", line 335

ORA-06512: at "PDU2.PACK_QUEUE", line 148

ORA-06512: at "PDU2.PACK_QUEUE", line 534

ORA-06512: at "PDU2.PACK_QUEUE", line 597

ORA-06512: at "PDU2.PACK_QUEUE", line 604

ORA-06512: at line 1

Tue May 12 10:53:08 2015

Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/pdtest/trace/pdtest_j000_30214.trc:

ORA-12012: error on auto execute of job "PDU"."FTS_MONITOR"

ORA-20000: ORA-25226: dequeue failed, queue PDU.OTS_FORWARD2 is not enabled for dequeue

ORA-06512: at "SYS.DBMS_AQ", line 335

ORA-06512: at "PDU.PACK_QUEUE", line 148

ORA-06512: at "PDU.PACK_QUEUE", line 382

ORA-06512: at "PDU.PACK_QUEUE", line 594

ORA-06512: at "PDU.PACK_QUEUE", line 604

ORA-06512: at line 1

Tue May 12 10:53:08 2015

Errors in file /u01/ora_5/oracle/diag/rdbms/pdtest/pdtest/trace/pdtest_j002_28773.trc:

ORA-12012: error on auto execute of job "PDU2"."FTS_MONITOR"

ORA-20000: ORA-25226: dequeue failed, queue PDU2.OTS_FORWARD is not enabled for dequeue

ORA-06512: at "SYS.DBMS_AQ", line 335

ORA-06512: at "PDU2.PACK_QUEUE", line 148

ORA-06512: at "PDU2.PACK_QUEUE", line 534

ORA-06512: at "PDU2.PACK_QUEUE", line 597

ORA-06512: at "PDU2.PACK_QUEUE", line 604

ORA-06512: at line 1

Continue reading →

11 Mart 2016 by Kamil TURKYILMAZ

ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter

Dataguard kurulumları sonrasında switchover veya 12c ile birlikte gelen verify komutunu çalıştırdığınız da aşağıdaki gibi bir hata alırsanız bunun nedenini ve nasıl çözüleceğinden bahsediyor olacağım.

Primary database tarafında log_archive_dest2 yi “SERVICE=con_stby LgWR SYNC AFFIRM” şekilde set etmeniz durumunda da dataguard çalışır. Bunun için dataguardınızın hengi modda çalıştığının bir önemi yoktur.

Ancak bu şekilde set ettiğiniz de switchover yapmak istediğiniz de (primary database üzerinde)

SQL> ALTER DATABASE SWITCHOVER TO CON_STBY;
ALTER DATABASE SWITCHOVER TO CON_STBY 
*
ERROR at line 1:
ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter

SQL> ALTER DATABASE SWITCHOVER TO CON_STBY;

ALTER DATABASE SWITCHOVER TO CON_STBY

ERROR at line 1:

ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter

Hatasını alırsınız. Bu hatayı gidermek için (aslında hatanın açıklamasında yer aldığı üzere) primary database’ de dest2 parametresine db_unique_name kısmını da eklemek gerekmektedir , aşağıdaki şekilde tekrar alter edelim ;
Continue reading →