Month: February 2019

I recently stepped in for another Database Base Administrator who was no longer with the group. The project being supported was in the middle of migrating from a non-RAC environment to a 4 node RAC environment. The move was designed to be accomplished via Expdp, FTP and then an Impdp. I typically perform this by separating the schemas from the data as this allows me a moment to check that all the schema objects migrated successfully before performing the usually larger data migration.

In this particular case, the schemas imported successfully. When I started the data import I got the below errors:

Import: Release 11.2.0.4.0 - Production on Mon Feb 25 12:49:46 2019

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
Master table "SYS"."DEAN_IMPDP_DATA" successfully loaded/unloaded
Starting "SYS"."DEAN_IMPDP_DATA":  "/******** AS SYSDBA" parfile=dean_impdp_data.par 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-31693: Table data object "TABLE_OWNER"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-31640: unable to open dump file "/data_pump/dean_expdp_data.dmp" for read
ORA-19505: failed to identify file "/data_pump/dean_expdp_data.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

What was further misleading was that some of the tables reported the above error while others loaded.

The file /data_pump/dean_expdp_data.dmp existed and had the required permissions.

The root cause of the error turned out to be that the data was exported from a single instance server and the import was being run on a RAC. The import operations for some of the tables were being tasked to the other nodes of the RAC that did not have access to the /data_pump/dean_expdp_data.dmp file and hence the “ORA-27037: unable to obtain file status“ errors.

I changed the import’s par file to add “CLUSTER=NO” and the issue was resolved.

I had to create a database link on behalf of another user whose password I did not know. This was performed as the “SYS” user in an Oracle 11.2.0.4.0 environment.

The requirements were as follows:
a. User test_user would connect to test_db
b. Execute an SQL over a db link as uat_user on uat_db
c. Database link should be named test_2_uat
d. uat_users password on uat_db was known. Assume it is mypassword
e. the tnsnames file has an entry for the uat_db named uat_db and is verified by
tnsping uat_db

Steps:
01. Log on to test_db as SYS user

02. Create the below procedure:

CREATE or replace PROCEDURE test_user.DEAN_DB_LINK AS
BEGIN
  EXECUTE IMMEDIATE 'CREATE DATABASE LINK test_2_uat '
       ||'CONNECT TO UAT_USER IDENTIFIED BY "mypassword" '
       ||'USING ''uat_db''';
END DEAN_DB_LINK;
/

03. After the above is created grant privilege to test_user to create a link

GRANT CREATE DATABASE LINK TO TEST_USER;

04. Execute the above procedure (as SYS)

EXECUTE TEST_USER.DEAN_DB_LINK;

05. Check that the link was created

SELECT * FROM DBA_DB_LINKS;

06. Revoke the privilege from test_user

REVOKE CREATE DATABASE LINK  FROM TEST_USER;

Update – March 21, 2019

A friend reminded me to cleanup the procedure:

drop PROCEDURE test_user.DEAN_DB_LINK;

This is based on the information at:
How to Create a Database Link in Another User’s Schema