Oracle in World: 2/8/09

Wednesday, February 11, 2009

Compatibility Table of Oracle Data Pump with oracle database versions

In this post I am going to make a table that will cover all compatibility aspects regarding oracle data pump up to release Oracle 11g. At first let's have a look at data pump dump file set versions. When your use data pump export client a dump file is created. Dump file set version is dependent on database compatibility. Below is the chart.

A)Database Compatibility and Dump file Set Version.

Database compatibility Setting	Dump file set version	Can be imported into 10.1.x	Can be imported into 10.2.x	Can be imported into 11.1.x
10.1.x	0.1	Yes	Yes	Yes
10.2.x	1.1	No	Yes	Yes
11.1.x	2.1	No	No	Yes

B)Overview of using VERSION parameter while data pump export
As you might know you need to use VERSION parameter while doing data pump export if you decide to import into lower compatible database from the higher compatible database parameter setting.

Source Database compatibility Setting	Need to import to compatible setting 9.2.0.x	Need to import to compatible setting 10.1.0.x	Need to import to compatible setting 10.2.0.x	Need to import to compatible setting 11.1.0.x
10.1.0.x	VERSION=9.2	N/A	N/A	N/A
10.2.0.x	VERSION=9.2	VERSION=10.1	N/A	N/A
11.1.0.x	VERSION=9.2	VERSION=10.1	VERSION=10.2	N/A

C)Overview of data pump Client/Server Compatibility Can you connect with your 11.1.0.6 client expdp/impdp into 10g database version? This part will cover those things.

expdp and impdp client version	Connecting to database 10gR1(10.1.x)	Connecting to database 10gR2(10.2.x)	Connecting to database 11gR1(11.1.x)
10.1.0.x	Supported	Supported	Supported
10.2.0.x	Not Supported	Supported	Supported
11.1.0.x	Not Supported	Not Supported	Supported

Monday, February 9, 2009

How to take data pump export dumpfile to another machine

In this example I will show you how to take data pump export so that dump file will be created in another database rather than the database from which you are doing data pump export.

In this example I will do all operations currently sitting my own client machine.
I will take a data pump export from database ORCL and dumpfile will be written to database TIGER on another machine.

Below is the steps.
Step 01: Connect to the tiger database and create database link.
C:\>sqlplus arju/a@tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 10 01:11:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create database link orcl.net using 'ORCL';

Database link created.

Note that TNS alias entry of ORCL must reside on the TIGER machine. You can't use your own TNS alias entry here. After creating database link make sure you can connect to orcl database.

SQL> select count(*) from user_tables@orcl.net;

COUNT(*)
----------
2

Without TNS entry you can still create your database link. Like below.

SQL> create database link orcl2.net connect to arju identified by a
using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST =10.4.12.233) (PORT=1521))
(connect_data=(service_name=orcl)))';

Database link created.

test by,
SQL> select count(*) from user_tables@orcl2.net;

COUNT(*)
----------
2
Step 02: After successful database link creation run expdp on the tiger machine with NETWORK_LINK parameter. Dump file will be created on tiger machine and metadata and data will be exported through database link.

In this example optionally directory=d is provided. You can create it as ,

SQL> create directory d as 'your expected location';

If no directory parameter is provided by default in the directory DATA_PUMP_DIR dump file is exported.
SQL> $expdp arju/a@tiger directory=d schemas=arju dumpfile=arju_dump_from_orcl.dmp network_link=orcl2.net

Export: Release 10.2.0.1.0 - Production on Tuesday, 10 February, 2009 1:53:34

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_SCHEMA_01": arju/********@tiger directory=d schemas=arju dumpfile=arju_dump_from_orcl.dmp network_link=orcl2.net
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 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/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."EMP" 4.921 KB 2 rows
. . exported "ARJU"."TEST" 4.914 KB 1 rows
Master table "ARJU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_SCHEMA_01 is:
D:\ARJU_DUMP_FROM_ORCL.DMP
Job "ARJU"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:54:59
Related Documents
http://arjudba.blogspot.com/2009/02/important-guideline-about-data-pump.html
http://arjudba.blogspot.com/2009/02/how-to-do-data-pump-import-from-higher.html
http://arjudba.blogspot.com/2008/04/data-pump-exportimport-how-we-can-do-it.html
http://arjudba.blogspot.com/2009/02/ude-00018-data-pump-client-is.html
http://arjudba.blogspot.com/2008/04/exportimport-datapump-parameter-query.html
http://arjudba.blogspot.com/2008/04/improve-data-pump-performance.html
http://arjudba.blogspot.com/2009/02/compatibility-table-of-oracle-data-pump.html
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html

Estimate the dumpfile size before taking dump using data pump export

Suppose you are now in a new database. You want to do a data pump export operation in order to take a dump file. But you are not aware of the dump file size. Based on the dump file size you might choose a directory location where you will put the dump file. With expdp utility the parameter ESTIMATE_ONLY might help you in this case.

If you add ESTIMATE_ONLY=y in your data pump export operation, then Export estimates the space that would be consumed, but no dump file will be generated.

Below is an output,


$expdp arju/a@san estimate_only=y

Export: Release 10.2.0.1.0 - Production on Tuesday, 10 February, 2009 0:06:09

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ARJU"."SYS_EXPORT_SCHEMA_01":  arju/********@san estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "ARJU"."OBJECTS"                               13 MB
.  estimated "ARJU"."HISTORY"                               64 KB
.  estimated "ARJU"."TEST"                                  64 KB
.  estimated "ARJU"."TEST_D"                                64 KB
Total estimation using BLOCKS method: 13.18 MB
Job "ARJU"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:06:36

So it shows your dump file can be approximately around 13.18 MB. In practical it should be less then 13.18 MB.

Alternative you can query from dba_segments in order to know about the dump file size.
The dump file size for all objects of schema ARJU can be,


SQL> select sum(bytes)/1024/1024 from dba_segments where owner='ARJU';

SUM(BYTES)/1024/1024
--------------------
            13.1875

SQL> col segment_name for a30
SQL> select bytes/1024, segment_name from dba_segments where owner='ARJU';

BYTES/1024 SEGMENT_NAME
---------- ------------------------------
       64 TEST
    13312 OBJECTS
       64 HISTORY
       64 TEST_D

Alternatively, you can query group by segment_name if you want to know the size table_wise.

An important point is that the above estimation is by block method. The parameter COMPRESSION=ALL has no effect in the size of the estimation.

So, writing
$expdp arju/a@san estimate_only=y
or
$expdp arju/a@san estimate_only=y compression=ALL
will return the same size estimation.

Maximum Oracle Database Size.

Choose an optimal Data block size in Oracle

How to determine OS block size for windows or unix

Settting maximum Size of Backup Set or Backup Piece

Compress dumpfile while data pump export

With of original export (exp) we can directly compress the dump file while exporting objects on unix machine by using named pipes (by mknod command). On windows machine for exp there is no such command. So after doing export operation use any compression utility in order to compress the dumpfile as a separate process.

With expdp (up to Oracle version 10.2) there is no such mechanism to compress the dumpfile directly. The COMPRESSION=METADATA_ONLY available on 10g merely compressed metadata being written to the dump file in compressed format. But for data compression directly there was no utility. Manually you would use any compression tool like zip, gzip, tar, compress or other after doing data pump export operation.

With the release of 11g the COMPRESSION parameter is enhanced and now you no need to use any compression utility. You can choose to compress the dumpfile directly while exporting.
The COMPRESSION parameter now supports following values.

COMPRESSION=ALL: It enables compression for the entire export operation.

COMPRESSION=DATA_ONLY: It results in all data being written to the dump file in compressed format.

COMPRESSION=METADATA_ONLY: It results in all metadata being written to the dump file in
compressed format. This is the default.

COMPRESSION=NONE: It disables compression for the entire export operation.

With adding COMPRESSION=ALL, while data pump export operation the dump file can now be reduced by 4/5 times or more than without COMPRESSION.

Use it as below to take a dump of all objects under schema arju and the dumpfile to be written in the directory location DATA_PUMP_DIR as compressed dump,
expdp userid=arju/arju compression=all

Note that the COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option.

Sunday, February 8, 2009

ORA-06508, ORA-04065, ORA-04068: existing state of packages has been discarded

Problem description
While executing a stored procedure the following error stack is returned.


ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "A.RATEDUMMY"
ORA-06508: PL/SQL: could not find program unit being called: "A.RATEDUMMY"
ORA-06512: at "A.AMT_OR_TIME", line 128

Cause of the Problem
1)The most possible cause of ORA-04068 is a dependent object on the procedure/package/function was altered by a DDL statement.

When a package is compiled, all copies of the functions/packages reside on the shared pool marked as invalid. Now if you invoke the function/package then it sees that flag is now set and need to get a new copy and instantly ORA-04068: existing state of packages has been discarded error comes out as current copy of packages reside on the shared pool must be thrown out.

2)Using DDL (Drop, ALTER or CREATE) whenever you recompile or change the package then dependent package become invalid through cascade invalidation.

3)If a package/function is dependent on another package/function where there is no body
on the dependent package/function, then while execution error will return. This error invalidates the package.

4)If a remote dependent object has been altered through a DDL statement then invoking the procedure/package can result ORA-04068: existing state of packages has been discarded .


Checks need to do
Check the package last compile time,
SQL> col object_name for a20
SQL> set linesize 140
SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
           dba_objects WHERE object_name = 'CREDIT_AMT_OR_TIME';

OBJECT_NAME          OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_TIME
-------------------- ------------------- ------------------------------ ------- ------------------
AMT_OR_TIME   FUNCTION            MAXIMSG                        VALID   09-FEB-09 12:04:08

Check the dependent object's last alteration by,


SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
           dba_objects WHERE ( object_name, object_type ) IN ( SELECT
           referenced_name, referenced_type FROM dba_dependencies WHERE name =
           'AMT_OR_TIME' );

OBJECT_NAME               OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_
------------------------- ------------------- ------------------------------ ------- ---------
DUAL                      SYNONYM             PUBLIC                         VALID   30-AUG-05
DBMS_OUTPUT               SYNONYM             PUBLIC                         VALID   30-AUG-05
RATECHART                 TABLE               MAXIMSG                        VALID   04-DEC-08
SUBSCRIBERS               TABLE               MAXIMSG                        VALID   04-DEC-08
REGISTERED_PHONES         TABLE               MAXIMSG                        VALID   04-DEC-08
RATEDUMMY                 FUNCTION            MAXIMSG                        VALID   09-FEB-09
ACCESS                    TABLE               MAXIMSG                        VALID   04-DEC-08
SPEEDDIAL                 TABLE               MAXIMSG                        VALID   04-DEC-08

Check the existing errors on package by,


SQL> SELECT name, type, text FROM dba_errors WHERE name = 'AMT_OR_TIME';

Solution of the Problem
1)Re-execute the package possibly will solve the problem.
2)Recompile the procedure/function/package and it's dependent.
ALTER PACKAGE package_name compile;
ALTER FUNCTION function_name compile;
3)If the dependent objects in other schema then note that explicitly execute permission is granted to the user.

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle