Can I use expdp through dblink to export data from 9i Database ?, Dblinkexpdp

Can I use expdp through dblink to export data from 9i Database ?, Dblinkexpdp

Expdp/impdp is a 10g import and export tool, which is not available in 9i. Someone suggested using 10g expdp to export 9i data through dblink, but this is not the case. Let's explain this experiment.

1. log on to a 10g/11g server, and first create a link to the 9i Database TEST tlink:

SQL> create database link tlink connect to test identified by test using 'TEST';Database link created.

2. Edit the configuration file exported by expdp:

Userid = user/pwd-expdp user directory of the local database used = expdp_dir-directory path dumpfile = testtkt. dumplogfile = testtkt. logtables = test. t_r-The 9i Database Table to be exported t_rnetwork_link = tlink-The 9i Database dblink linked

3. Execute expdp:

[oracle11g@Node1 expdp_dir]$ expdp parfile=tlink.parExport: Release 11.2.0.1.0 - Production on Mon May 18 13:35:57 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-39006: internal errorORA-39065: unexpected master process exception in DISPATCHORA-00904: "SYS"."KUPM$MCP"."GET_ENDIANNESS": invalid identifierORA-39097: Data Pump job encountered unexpected error -904

A series of ORA errors are prompted.

Unlike exp, expdp converts an export task into an Oracle job. It can be proved that the export process is interrupted by pressing ctrl + c during exp export, if you use expdp to export data with ctrl + c, the export process is not interrupted because it has been changed to an Oracle task and has nothing to do with the client. After exiting the interactive mode, will enter the export command line mode, including status, start, stop and other commands can be used.

From the above error we can see the ORA-00904: "SYS". "KUPM $ MCP". "GET_ENDIANNESS": invalid identifier, error description about 904:

[oracle11g@Node1 expdp_dir]$ oerr ora 90400904, 00000, "%s: invalid identifier"// *Cause:// *Action:

The corresponding identifier is not found. Let's see what SYS. KUPM $ MCP is like:

SQL> desc sys.KUPM$MCP;PROCEDURE CLOSE_JOBFUNCTION DISPATCH RETURNS KUPC$_MESSAGE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- REQUEST                        KUPC$_MESSAGE           INPROCEDURE FILE_LOG_MESSAGE Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- ERRCODE                        NUMBER                  IN PARAMETER1                     VARCHAR2                IN     DEFAULT PARAMETER2                     VARCHAR2                IN     DEFAULT PARAMETER3                     VARCHAR2                IN     DEFAULT PARAMETER4                     VARCHAR2                IN     DEFAULT PARAMETER5                     VARCHAR2                IN     DEFAULT PARAMETER6                     VARCHAR2                IN     DEFAULT PARAMETER7                     VARCHAR2                IN     DEFAULT PARAMETER8                     VARCHAR2                IN     DEFAULTPROCEDURE FILE_TO_WORKER Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- MESSAGE                        KUPC$_MESSAGE           INFUNCTION GET_ENDIANNESS RETURNS NUMBERPROCEDURE MAIN Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- JOB_NAME                       VARCHAR2                IN JOB_OWNER                      VARCHAR2                IN RESTART_JOB                    BINARY_INTEGER          IN DEBUG_INFO                     BINARY_INTEGER          INPROCEDURE MAINLOOPPROCEDURE SET_DEBUG Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- DEBUG_FLAGS                    BINARY_INTEGER          INFUNCTION VALIDATE_EXPRESSION RETURNS NUMBER Argument Name                  Type                    In/Out Default? ------------------------------ ----------------------- ------ -------- EXPR_VALUE                     VARCHAR2                IN

In fact, it contains several functions. GET_ENDIANNESS is one of them (from the perspective of name, it seems to be obtaining "size end information"). Obviously, the corresponding function is not found, I forgot to see the description of this function from somewhere (if a high person remembers it, he can reply to it). It does not exist in 9i, it can be proved that the expdp export process needs to call this function, but this function is not defined in 9i. Therefore, expdp cannot be used in 9i.

4. Now let's look at the database of 10 Gb or above exported by dblink.

SQL> create database link ldplink connect to puser identified by puser_pwd using 'link_10g';Database link created.

5. Edit the expdp configuration file:

[oracle11g@Node1 expdp_dir]$ vi link_10g.par
userid=user/pwd
directory=expdp_dirdumpfile=link_10g.dumplogfile=link_10g.logtables=puser.l_rnetwork_link=link_10g

6. Execute expdp:

[oracle11g@Node1 expdp_dir]$ expdp parfile=link_10g.par Export: Release 11.2.0.1.0 - Production on Mon May 18 13:40:45 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-31631: privileges are requiredORA-39149: cannot link privileged user to non-privileged user

This time again prompts two errors: ORA-31631 and ORA-39149.

[oracle11g@Node1 expdp_dir]$ oerr ora 3163131631, 00000, "privileges are required" // *Cause:  The necessary privileges are not available for operations such//          as: restarting a job on behalf of another owner, using a device//          as a member of the dump file set, or ommiting a directory//          object associated with any of the various output files.  //          Refer to any following error messages for additional information.// *Action: Select a different job to restart, try a different operation, or//          contact a database administrator to acquire the needed privileges.

The prompt is that the permission is missing.

[oracle11g@Node1 expdp_dir]$ oerr ora 3914939149, 00000, "cannot link privileged user to non-privileged user"// *Cause:  A Data Pump job initiated be a user with //          EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE roles specified a//          network link that did not correspond to a user with//          equivalent roles on the remote database.// *Action: Specify a network link that maps users to identically privileged //          users in the remote database.

This error message is more explicit. It prompts the execution users who use dblink and have the EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE role. The remote users do not have the corresponding role permissions. It is a bit difficult. In short, the database user who uses expdp has the DBA permission. Therefore, it has the EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE role permission, but the remote database user puser is only a common user, this error message is returned because you do not have this permission.

Solution 1:

Remote database settings:

SQL> grant exp_full_database to puser;Grant succeeded.

Execute expdp again:

[oracle11g@Node1 expdp_dir]$ expdp parfile=link_10g.par Export: Release 11.2.0.1.0 - Production on Mon May 18 13:51:37 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "USER"."SYS_EXPORT_TABLE_01":  user/******** parfile=link_10g.par Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 64 KBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "PUSER"."L_R"                          20.49 KB      28 rowsMaster table "BISAL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for USER.SYS_EXPORT_TABLE_01 is:  /home/oracle11g/expdp_dir/link_10g.dumpJob "USER"."SYS_EXPORT_TABLE_01" successfully completed at 13:51:56

Solution 2:

This blog by elders Yang (http://blog.itpub.net/4227/viewspace-448665/) illustrates the problem. To solve this problem, you can create a local user and do not grant the EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE role.

Summary:

1. 9i does not support expdp. Even if you use dblink to connect a 10g expdp to 9i, this is not acceptable. From the experiment above, we can see that, some expdp functions are not defined in 9i.

2. to export expdp using dblink, either the local user does not have the EXPORT_FULL_DATABASE/IMPORT_FULL_DATABASE role or the local user has the two roles. However, the remote user must have the EXPORT_FULL_DATABASE role, otherwise the ORA-31631 and ORA-39149 errors will be prompted.