DataPump Export (EXPDP) Creates Excessive Traces Even If It Runs Successfully (Doc ID 1154963.1)

DataPump Export (EXPDP) Creates Excessive Traces Even If It Runs Successfully (Doc ID 1154963.1) To BottomTo Bottom


APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS
A simple 11.2 DataPump export (EXPDP) at SCHEMA level (e.g. on SCOTT schema) can be shown to generate a number of DWxx (and also seen from P00x and ORAxx processes) traces in /diag/trace directory. The traces are of the following nature: *** MODULE NAME:(Data Pump Worker) *** ACTION NAME:(SYS_EXPORT_FULL_02) KUPC: Setting remote flag for this process to FALSE prvtaqis - Enter *** 2010-07-15 23:00:23.133 prvtaqis subtab_name upd prvtaqis sys table upd *** 2010-07-15 23:00:23.831 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 5 kwqberlst rqan->lascn_kwqiia > 0 block


Traces are typically < 100K in length. If the EXPDP run was successful the traces contain no error messages, if EXPDP raises an internal 600/7445 or other errors the traces might contain some equivalent information.


SOLUTION
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The first messages come from package body DBMS_PRVTAQIS which is called during EXPDP/IMPDP. ORACLE Support can confirm that in 11.2 source code the call to DBMS_PRVTAQIS is not considered to be debugging code so there is no option (event/parameter) to disable these traces. The traces will just need to be periodically deleted. It should be noted that EXPDP/IMPDP are not the only products to call DBMS_PRVTAQIS, any product that does can also create these traces and the MODULE NAME in the traces will clarify this. FIXED RELEASE ---- 11.2.0.4 PATCH FIX ---- Patch:12817425 is not available for 11.2.0.1. WORKAROUND ---- None. To purge trace files manually you could execute: adrci> purge -age {minutes} -type TRACE

RANGE - Interval Partitioning In Oracle

Range Interval Partitioning In Oracle

Interval Partitioning has been introduced in oracle 11g.
With this method, we can automate the creation of range partition .
While creating the partitioned table, we just need to define one partition.
New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t need to create the future partitions.

1. Lets create a monthly partitioned table ( with interval).

CREATE TABLE DBATEST.PART_TEST_TABLE ( CORRELATION_ID VARCHAR2(100 BYTE), ATTRIBUTE_KEY VARCHAR2(50 BYTE), ATTRIBUTE_VALUE VARCHAR2(100 BYTE), EVENT_TIMESTAMP TIMESTAMP(6) ) TABLESPACE STCDBA PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION PART_TEST_TABLE_MIN VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );

CREATE INDEX DBATEST.PART_TEST_TABLE_COR_ID_IDX ON DBATEST.PART_TEST_TABLE (CORRELATION_ID) LOCAL; Index created.

Here the date 2016-05-01 is known as TRANSIT POINT . Any data inserted beyond this transit point will led to creation of a new partition automatically.

INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point



2. Now check dba_tab_partitions

SELECT table_name,partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


3. Lets insert some data and check the dba_tab_partitions:

insert into dbatest.PART_TEST_TABLE values ('387fade4-f0f4-4bc3-9ba3-41458fad7a4c','ApplicationName','ULA','01-JUN-2016') commit; SELECT table_name,partition_name,high_value,interval  from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


We can see a new monthly partition has been created automatically .

Similarly

for weekly partition use the parameter – INTERVAL (numtodsinterval(7,’day’))
for yearly partition use the parameter – INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))



Convert Existing Range partitioned table to interval partition:
ALTER TABLE dbatest.range_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));


Restriction of Interval Partitioning:

1. Cannot be created for Index organized table(IOT)

2. The partitioning column can be only one and it must be of type NUMBER or DATE

3. Cannot use the MAXVALUE clause

Convert NON-Partitioned table into Partitioned table in oracle

Convert NON-Partitioned table into Partitioned table in oracle

This is one of the new feature of oracle 12.2 release .

Non-partitioned tables can be converted to partitioned table online without any downtime to the application , i.e no impact to the DML activities.

Till now for this activity, we were using dbms_redef methods. But in Oracle 12.2 release this has been simplified much.


See the below example:

1. Identify the non partitioned table.

desc BSSTDBA.PART_TEST_TABLE Name Type ---------------------------------------- -------- ----------------------------  ROW_ID                                    NOT NULL VARCHAR2(15 CHAR)  CREATED                                   NOT NULL DATE  CREATED_BY                                NOT NULL VARCHAR2(15 CHAR)  LAST_UPD                                  NOT NULL DATE  MODIFICATION_NUM                          NOT NULL NUMBER(10)  CONFLICT_ID                               NOT NULL VARCHAR2(15 CHAR)  ALW_PART_SHIP_FLG                         NOT NULL CHAR(1 CHAR)

SQL> col owner for a13 SQL> col table_name for a14 SQL>  set lines 299 SQL> select owner,table_name,partitioned from dba_tables where table_name='PART_TEST_TABLE'; OWNER         TABLE_NAME     PAR ------------- -------------- --- BSSTDBA       PART_TEST_TABLE      NO <br /> <br /> <br /> SQL> select count(*) from BSSTDBA.PART_TEST_TABLE;   COUNT(*) ----------     954598 SQL>create index BSSTDBA.PART_TEST_TABLE_IND1 on BSSTDBA.PART_TEST_TABLE(row_id); Index created.

SQL> create index BSSTDBA.PART_TEST_TABLE_IND2 on BSSTDBA.PART_TEST_TABLE(created); Index created.
 2. Alter table modify to partition the table.( partition key is column CREATED ) alter table BSSTDBA.PART_TEST_TABLE modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), PARTITION Created_MX VALUES LESS THAN (MAXVALUE)) ONLINE;

This activity will take some time depending upon amount of data table has.

While this alter statement is running, I have started running DML activities on the same table, To check whether it is impacting the DML activities.



SESSION 2:
  insert into BSSTDBA.PART_TEST_TABLE select * from BSSTDBA.PART_TEST_TABLE; Lets check for blocking session:        SID USERNAME             MODULE                         STATUS   EVENT                          BLOCKING_SESSION ---------- -------------------- ------------------------------ -------- ------------------------------ ----------------        490 SYS                  sqlplus@bttstdev64 (TNS V1-V3) ACTIVE   enq: TX - row lock contention                 7 SID      > 490  SQL_TEXT > alter table BSSTDBA.PART_TEST_TABLE modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN  (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), partition created_2016_p1 VALUE LESS THAN (MAXVALUE)) ONLINE SID    >  7 SQL_TEXT> insert into BSSTDBA.PART_TEST_TABLE select * from BSSTDBA.PART_TEST_TABLE;

We can see that the insert statement(SID 7), is blocking the alter table command(SID 490), not the other way around. It means during this partition conversion activity, if any DML requests are coming, then it will allow them to complete their request. This may slow down the partition conversion time, But it won’t impact the application. Once ALTER TABLE MODIFY is completed. Check the whether table was partitioned properly or not.

SQL> select partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; PARTITION_NAME          HIGH_VALUE ----------------------- -------------------------------------------------------------------------------- CREATED_2105_P10        TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P11        TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P12        TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P8         TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P9         TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_MX              MAXVALUE 20 rows selected.

But what happened to the INDEXES:
select  index_name,PARTITIONED from dba_indexes where table_name='PART_TEST_TABLE'; INDEX_NAME          PARTITIONED    ------------------- ------------ PART_TEST_TABLE_IND1      NO      PART_TEST_TABLE_IND2      YES

We can see PART_TEST_TABLE_IND1 was NON partitioned, But PART_TEST_TABLE_IND2 was partitioned.



Oracle document Says:

If no index clause is mentioned in the alter table statement, then

nonprefixed indexes(i.e index column is not a partitioned key) will be become global non-partitioned Index.
prefixed indexes(i.e index column is a partitioned key) will become local partitioned Index.


PART_TEST_TABLE_IND1 - ------------------- INDEX_SQL - >  create index BSSTDBA.PART_TEST_TABLE_IND1 on BSSTDBA.PART_TEST_TABLE(row_id);

It is an nonprefixed Index i.e index column is not a partitioned key. So it became global non partitioned Index


PART_TEST_TABLE_IND2  -
--------------------

create index BSSTDBA.PART_TEST_TABLE_IND2 on BSSTDBA.PART_TEST_TABLE(created);

It is an prefixed Index.  i.e index column in a partitione key .
So this indexes became local partitioned Index.


SQL> select index_name,PARTITION_NAME,HIGH_VALUE from dba_ind_partitions where index_name='PART_TEST_TABLE_IND2'; INDEX_NAME          PARTITION_NAME          HIGH_VALUE ------------------- ----------------------- -------------------------------------------------------------------------------- PART_TEST_TABLE_IND2      CREATED_2016_P9         TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P10        TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P11        TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P12        TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P8         TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P9         TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_MX              MAXVALUE 20 rows selected. There are lot of 12.2 New features like moving a table online, Spliting a partitioned table online etc. which we will discuss very soon.

ora-00439 feature not enabled partitioning standard edition

ora-00439 feature not enabled partitioning standard edition


select * from v$option where parameter = 'Partitioning';


When you select the “Enterprise Edition” option during Oracle installation, all the components which are licensed under “Enterprise Edition” get installed by default.

If you need additional functionality such as partitioning some time after the initial installation, you can enable (or disable) the specific component functionality at the binary level:

1. Shutdown all database instance(s)/service(s) running from the Oracle Database Home
2. Run the following relink command to disable the option at the binary level:

#######################################################
below command use for turn off partitioning
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

make -f ins_rdbms.mk option_switch ioracle

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk part_off ioracle



below command use for turn on partitioning
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk part_on
$ make -f ins_rdbms.mk ioracle


Here is the list of database options and switches:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




3. Startup the instance and check if the option is enabled:

Oracle PL/SQL

SELECT * FROM v$option WHERE parameter = 'Partitioning';

Remove the Database From an RMAN Recovery Catalog

Remove the  Database From an RMAN Recovery  Catalog
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Removing the database from catalog is nothing but unregisterring the database from the catalog, below methods will help to unregister the database.


There are multiple ways to unregister a database from an RMAN recovery catalog.


UNREGISTER DATABASE (Catalog and Database)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This option is available from Oracle 10g onward. If you still have access to the database you can start RMAN, connecting to both the target database and the catalog.

rman target=sys/password@DBNAME  catalog=rman/rman@catdb

Below are optional, if you want to perform any cleaning operations, you can use below.

RMAN> LIST BACKUP SUMMARY;
RMAN> DELETE BACKUP DEVICE TYPE SBT;
RMAN> DELETE BACKUP DEVICE TYPE DISK;

then

RMAN> UNREGISTER DATABASE;

OR

RMAN> UNREGISTER DATABASE NOPROMPT;



UNREGISTER DATABASE (Catalog Only)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This option is available from Oracle 10g onward. If you no longer have access to the target database, you can still unregister it from the catalog using the UNREGISTER DATABASE command in RMAN.

Start RMAN, connecting only to the catalog.

rman catalog=rman/rman@catdb
Unregister the database by name.

RMAN> UNREGISTER DATABASE SAMPLE NOPROMPT;



If there is more than one database in the catalog with the same name, you will need to use the DBID to identify the database to unregister. You can find this using the LIST INCARNATION command.

RMAN> LIST INCARNATION OF DATABASE SAMPLE;
Once you have the DBID, you can unregister the database using the following script.

RUN
{ 
  SET DBID 152565859654;
  UNREGISTER DATABASE SAMPLE NOPROMPT;
}



DBMS_RCVCAT (Catalog Only)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you no longer have access to the target database, you can still unregister it from the catalog using the DBMS_RCVCAT package in SQL.

Connect to the catalog database using SQL*Plus, then query the DB_KEY and DBID values as follows.

SQL> CONNECT rman/rman@catdb
Connected.

SQL> SELECT db_key, dbid, name FROM rc_database WHERE name = 'SAMPLE';

    DB_KEY       DBID NAME
---------- ---------- --------
     23085 152565859654 SAMPLE

1 row selected.


SQL>

The resulting DB_KEY and DBID can then be used to unregister the database using the DBMS_RCVCAT package.

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(23085 , 152565859654 );

PL/SQL procedure successfully completed.

SQL>