Export Backup exclude table in Data Pump Error: ORA-39071

Error :

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character


Solution:

[oracle@saidrasel]$ expdp system/password schemas= "exclude=TABLE:\"IN ('OUTWDCLR','INWARD_IMAGE')\"" directory=DATA_P dumpfile=MICR-20160512.dmp logfile=MICR-20160512.log EXCLUDE=STATISTICS CONTENT=ALL

EXPDP in Oracle 12c (ORA-39002 ORA-39070 ORA-39087)

DROP DIRECTORY DUMP_DATA;

CREATE OR REPLACE DIRECTORY
DUMP_DATA AS
'G:\InHouse_DB_Backup';



GRANT EXECUTE, READ, WRITE ON DIRECTORY DUMP_DATA TO system WITH GRANT OPTION;

impdp system/sys123 schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

C:\Users\Administrator>impdp system/sys123 schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

Import: Release 12.1.0.2.0 - Production on Tue Aug 25 14:51:00 2015

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
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_DATA is invalid

Note: In Oracle 12 c during expdp and impdp pluggable Database name must be mentioned

impdp system/sys123@orcl_pdb schemas=ORBHRM,ORBWEB,UTILITY directory=DUMP_DATA dumpfile="HRDBRQDB-20150824.dmp" logfile=test.log

ORA-31693 ORA-02354 ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_933907484$" too small in expdp

Problem: Processing object type DATABASE_EXPORT/AUDIT
ORA-31693: Table data object "MICR"."OUTWDCLR" failed to load/unload and is being skipped due to error:ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_933907484$" too small

select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER='MICR' and TABLE_NAME='OUTWDCLR';

COLUMN_NAME SECUREFILE PCTVERSION RETENTION

IMAGE_FRONT NO 10
IMAGE_REAR NO 10


SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>


SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
            3291

SQL>

We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION,
but the maxquery length is 3291 seconds.


When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.

Solution:::

 1. Modify the current UNDO_RETENTION for the database:

SQL>ALTER SYSTEM SET UNDO_RETENTION = 4500 scope=both sid='*';

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note:563470.1

SQL> alter table MICR.OUTWDCLR modify lob(IMAGE_FRONT) (retention);
Table altered.

SQL> alter table MICR.OUTWDCLR modify lob(IMAGE_REAR) (retention);
Table altered.


3. Query the lob retention again to verify that the change has taken hold:
SQL> select COLUMN_NAME,SECUREFILE,PCTVERSION,RETENTION from dba_lobs where OWNER=upper('&OWNER') and TABLE_NAME=upper('&TABLE_NAME') ;

COLUMN_NAME                    SEC PCTVERSION  RETENTION
------------------------------ --- ---------- ----------
IMAGE_FRONT                NO                  4500
IMAGE_REAR                 NO                  4500

4. Perform the export again.

Import (IMPDP) Oracle Database backup from network location

Scenario:

Suppose we want to restore backup into destination database server (192.168.10.6) and backup resides in source database server (192.168.10.32)

Solution:

Step1: We need to keep the backup dump file into source database server (192.168.10.32)

Step2: We need to create a database link into source database server (192.168.10.32)

CREATE PUBLIC DATABASE LINK DPUMP
 CONNECT TO SYSTEM
 IDENTIFIED BY
 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.6)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';

Step3: Now we need to create a directory where database backup will reside 

DROP DIRECTORY data_dump;
CREATE OR REPLACE DIRECTORY data_dump AS '/back/exp/';

GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.data_dump TO SYSTEM WITH GRANT OPTION;

Step4: Now we need to execute import (impdp) command from source database server (192.168.10.32) to restore
       backup into destination database server (192.168.10.6)

Step5: Put the schemas value which schema you want to import

impdp system/sys123 schemas=scott,hr,test network_link=DPUMP directory=data_dump dumpfile=orcl_full.dmp logfile=orcl_full.log    


  
Step6: Now the data values that you want to import..

Cheers.....