datapump Archives - DBACLASS https://dbaclass.com/article-tag/datapump/ database administration Tue, 30 Jan 2018 08:24:30 +0000 en-US hourly 1 https://wordpress.org/?v=6.5.5 How to use expdp to export data from physical standby database https://dbaclass.com/article/use-expdp-export-data-physical-standby-database/ https://dbaclass.com/article/use-expdp-export-data-physical-standby-database/#comments Tue, 30 Jan 2018 08:12:59 +0000 http://dbaclass.com/?post_type=article&p=3543 During peak business hours, It is not advised to export data from production .(as it might impact the performance). So if we have a physical standby database, we can export data using datapump from standby database. PRE-REQUISITES: 1. Physical standby database should be in READ-ONLY MODE (ACTIVE DATAGUARD) 2.We cannot run expdp directly on physical […]

The post How to use expdp to export data from physical standby database appeared first on DBACLASS.

]]> During peak business hours, It is not advised to export data from production .(as it might impact the performance).
So if we have a physical standby database, we can export data using datapump from standby database.

PRE-REQUISITES:

1. Physical standby database should be in READ-ONLY MODE (ACTIVE DATAGUARD)
2.We cannot run expdp directly on physical standby database. Because datapump job can be created only on a read-write database(OPEN).
So to achieve this, we will use the NETWORK_LINK parameter and run the expdp job from any non-standby database(OPEN database)

 

Refer open physical standby database in read only mode 

STEPS:

1. Check whether standby database is in read only mode:

SQL> select database_name,DATABASE_ROLE,open_mode  from v$database;

DATABASE_NAME  DATABASE_ROLE    OPEN_MODE
-------------- ---------------- --------------------
SALPROD        PHYSICAL STANDBY READ ONLY WITH APPLY

2. create a database link on non standby database.[POINTING TO STANDBY DB]

SQL>  create public database link LINK_EXPDP connect to BSSDBA identified by BSSDBA442 using 'SALdbr';

Database link created.


SQL> select sysdate from dual@LINK_EXPDP;

SYSDATE
---------
30-JAN-18

3. Create db directory on non-standby server:

create directory ERIC_DUMP as '/dumparea/dump/ERIC_DUMP'

4. Run expdp from non-standby database server:

expdp  directory=ERIC_DUMP network_link=LINK_EXPDP dumpfile=standby_schema.dmp logfile=standby_schema.log tables=EMP_USER.CHANNEL_NUMBER


Export: Release 12.1.0.2.0 - Production on Tue Jan 30 10:38:59 2018

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

Username: / as sysdba


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=ERIC_DUMP network_link=LINK_EXPDP dumpfile=standby_schema.dmp logfile=standby_schema.log tables=EMP_USER.CHANNEL_NUMBER
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "EMP_USER"."CHANNEL_NUMBER"                 171.2 KB   20000 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /dumparea/dump/ERIC_DUMP/standby_schema.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 30 10:47:54 2018 elapsed 0 00:07:14

We have successfully generated the export dump from standby database.

The post How to use expdp to export data from physical standby database appeared first on DBACLASS.

]]> https://dbaclass.com/article/use-expdp-export-data-physical-standby-database/feed/ 5 Datapump New features in Oracle 12.2 https://dbaclass.com/article/datapump-new-features-in-oracle-12-2/ https://dbaclass.com/article/datapump-new-features-in-oracle-12-2/#comments Tue, 21 Feb 2017 09:07:51 +0000 http://dbaclass.com/?post_type=article&p=1918           A lot of enhancements has been done in Oracle 12.2 Release.  In this article We will explain the new features of datapump utility in oracle 12.2 Release. 1.Parfile content  written to logfile: Till now, log file of expdp or impdp, doesn’t store the content of parfile, The log file used to […]

The post Datapump New features in Oracle 12.2 appeared first on DBACLASS.

]]>           A lot of enhancements has been done in Oracle 12.2 Release.  In this article We will explain the new features of datapump utility in oracle 12.2 Release.

1.Parfile content  written to logfile:

Till now, log file of expdp or impdp, doesn’t store the content of parfile, The log file used to look as below.

Export: Release 12.1.0.2.0 - Production on Tue Feb 21 11:50:56 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

So if the parfile is missing, From the logfile, we can’t find the details about the expdp/impdp.

With Oracle 12.2, The content of the parfile is written to logfile.

--- PARFILE CONTENT 

$cat expdp_full.par
dumpfile=test_schema.dmp
logfile=test.log
directory=TEST2
schemas=BSSTDBA

-- RUN EXPDP 

expdp parfile=expdp_full.par

Export: Release 12.2.0.1.0 - Production on Tue Feb 21 10:58:39 2017

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_full.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER



--- LOGFILE CONTENT

oracle@bttstdev64:~/TEST$ cat test.log
;;;
Export: Release 12.2.0.1.0 - Production on Tue Feb 21 10:58:39 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
;;; **************************************************************************
;;; Parfile values:
;;;  parfile:  schemas=BSSTDBA
;;;  parfile:  directory=TEST2
;;;  parfile:  logfile=test.log
;;;  parfile:  dumpfile=test_schema.dmp
;;; **************************************************************************
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_full.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

We can see that the parfile has been printed in the logfile. So no need to worry, if the parfile is missing 🙂

 

2. Parallel operation for metadata during expdp/impdp:

Till Oracle 12cR1, parallel operation doesnt work for expdp/impdp of metadatas. Even if we use more parallel option, it will take always 1 thread.

Lets try to both the 12cR1 and 12cR2 .

12CR1:

-- PARFILE CONTENT

dumpfile=test_%U.dmp
logfile=test.log
directory=TEST2
parallel=4
content=metadata_only
full=y

-- RUN EXPDP 
# expdp parfile=expdp_full.par

Export: Release 12.1.0.2.0 - Production on Tue Feb 21 11:50:56 2017

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

Username: / as sysdba

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
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
^C
Export> status

Job: SYS_EXPORT_FULL_01
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: /export/home/oracle/test_01.dmp
    bytes written: 4,096
  Dump File: /export/home/oracle/test_%u.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: OMDEVBAU
  Host name: sec60-1
  Process Name: DW00
  State: EXECUTING
  Object Schema: WMSYS
  Object Name: WM$EXP_MAP
  Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
  Completed Objects: 10
  Worker Parallelism: 1

So in Oracle 12cR1, Despite giving parallel=4 , Only one worker process was allocated.

Lets try the same Oracle 12cR2(12.2)

12CR2:

dumpfile=test_%U.dmp
logfile=test.log
directory=TEST2
parallel=4
content=metadata_only
full=y

$ expdp parfile=expdp_full.par

Export: Release 12.2.0.1.0 - Production on Tue Feb 21 10:53:46 2017

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par


Export> status

Job: SYS_EXPORT_FULL_01
  Operation: EXPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /export/home/oracle/TEST/test_%u.dmp
  Dump File: /export/home/oracle/TEST/test_01.dmp
    bytes written: 32,768
  Dump File: /export/home/oracle/TEST/test_02.dmp
    bytes written: 20,480
  Dump File: /export/home/oracle/TEST/test_03.dmp
    bytes written: 49,152
  Dump File: /export/home/oracle/TEST/test_04.dmp
    bytes written: 20,480

Worker 1 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:54:29
  Object status at: Tuesday, 21 February, 2017 10:54:56
  Process Name: DW00
  State: EXECUTING

Worker 2 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:55:04
  Object status at: Tuesday, 21 February, 2017 10:55:07
  Process Name: DW01
  State: EXECUTING

Worker 3 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:55:05
  Object status at: Tuesday, 21 February, 2017 10:55:07
  Process Name: DW02
  State: EXECUTING

Worker 4 Status:
  Instance ID: 1
  Instance name: DB12C
  Host name: bttstdev64
  Object start time: Tuesday, 21 February, 2017 10:54:23
  Object status at: Tuesday, 21 February, 2017 10:54:24
  Process Name: DW03
  State: EXECUTING

So here we used parallel=4, and 4 worker processes were created. Which is a very important enhancement in term metadata expdp performance.

3. New substitution variable for DUMPFILE parameter

Now new substitution variable like %D,%M,%T etc is available for DUMPFILE parameter, which we use to suffix or prefix the timestamp in the dumpfile.

expdp dumpfile=test_schema_%T.dmp logfile=test.log directory=TEST2 schemas=BSSTDBA

--  GENERATED DUMPFILE 
-rw-r-----   1 oracle   oinstall   12288 Feb 21 12:20 test_schema_20170221.dmp

Other available variables:

 

DUMPFILE  FORMAT DUMPFILE OUTPUT EXAMPLE
%y, %Y Specifies the year in this format: YYYY. dumpfile=test_schema_%Y.dmp -> test_schema_2017.dmp
%d, %D Specifies current date in DD dumpfile=test_schema_%D.dmp -> test_schema_21.dmp
%m, %M Specifies current month in MM dumpfile=test_schema_%M.dmp -> test_schema_02.dmp
%t, %T Specifies the current date with YYYYMMDD dumpfile=test_schema_%T.dmp -> test_schema_20170221.dmp
%l, %L Specifies a system-generated unique file name dumpfile=test_schema_%L.dmp -> test_schema_01.dmp

 

Apart from above, there are few more features in datapump, Which we will discuss soon.

 

4. Import LONG column using NETWORK_LINK

Till Now, we are not able to import table with LONG column using NETWORK_LINK.  It throws below error during impdp.

ORA-00997: illegal use of LONG datatype error

But with 12cR2, we can import LONG column through NETWORK_LINK.

 

The post Datapump New features in Oracle 12.2 appeared first on DBACLASS.

]]> https://dbaclass.com/article/datapump-new-features-in-oracle-12-2/feed/ 3 views_as_tables parameter in datapump of oracle 12c https://dbaclass.com/article/views_as_tables-parameter-in-datapump-of-oracle-12c/ https://dbaclass.com/article/views_as_tables-parameter-in-datapump-of-oracle-12c/#respond Sat, 22 Aug 2015 14:48:09 +0000 http://dbaclass.com/?post_type=article&p=286  VIEWS_AS_TABLES parameter has been introduced in datapump of 12c. With this we can export a view at source database and import as table in target database. Create a view: SQL> create view dba_view( emp_number) as select emp from dbatool; View created. SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW'; OWNER OBJECT_NAM OBJECT_TYPE ------------------ ---------- ----------------------- SYS […]

The post views_as_tables parameter in datapump of oracle 12c appeared first on DBACLASS.

]]>  VIEWS_AS_TABLES parameter has been introduced in datapump of 12c. With this we can export a view at source database and import as table in target database.

Create a view:

SQL> create view dba_view( emp_number) as select emp from dbatool;

View created.

SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';


OWNER		   OBJECT_NAM OBJECT_TYPE
------------------ ---------- -----------------------
SYS		   DBA_VIEW   VIEW

 

Take export of this view as table using views_as_tables parameter:

 

[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW

Export: Release 12.1.0.2.0 - Production on Sat Aug 22 07:07:54 2015

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

Username: sys/oracle@ORCL as sysdba

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
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."DBA_VIEW"                            5.117 KB       8 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/DUMP/view.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 22 07:08:17 2015 elapsed 0 00:00:11

 

Now drop the view and import the same from the export dump which we have taken.

 

SQL> drop view dba_view;

View dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP view_as_tables=D^C
[oracle@localhost ~]$ 
[oracle@localhost ~]$ impdp dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW

Import: Release 12.1.0.2.0 - Production on Sat Aug 22 07:09:09 2015

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

Username: sys/oracle@ORCL as sysdba

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
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP tables=DBA_VIEW 
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."DBA_VIEW"                            5.117 KB       8 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Aug 22 07:10:21 2015 elapsed 0 00:00:04

 

Now check the object_type:

 

[oracle@localhost ~]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 07:10:25 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> set pagesize 200            
SQL> set lines 200
SQL> set long 999
SQL> col owner for a9
SQL> col object_name for a10
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';

OWNER	  OBJECT_NAM OBJECT_TYPE
--------- ---------- -----------------------
SYS	  DBA_VIEW   TABLE

We can see DBA_VIEW which we exported as view has imported as table.

 

The post views_as_tables parameter in datapump of oracle 12c appeared first on DBACLASS.

]]> https://dbaclass.com/article/views_as_tables-parameter-in-datapump-of-oracle-12c/feed/ 0