Search code examples
oracleamazon-web-servicesamazon-rdsoracle12coracle-export-dump

copy an exported dump file to a target DB instance

I have 2 DB instance hosted in AWS in the same VPC:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE   12.1.0.2.0  Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

I am using DBMS_FILE_TRANSFER to copy the dump file from the source database instance to the target DB instance. 

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => 'DATA_PUMP_DIR',
source_file_name              => 'hotels.dmp',
destination_directory_object  => 'DATA_PUMP_DIR',
destination_file_name         => 'hotels_copied.dmp', 
destination_database          => 'to_rds' 
);
END;
/

where to_rds is a database link I previously created running

create database link to_rds connect to OF_HOTEL identified by OFO
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hotelsdbtestaws.cwob1oxhu1so.eu-central-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))';

but when I run the script I got this error:

Error report -
ORA-12170: TNS:Connect timeout occurred
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 60
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 168
ORA-06512: at line 2
12170. 00000 -  "TNS:Connect timeout occurred"
*Cause:    The server shut down because connection establishment or
           communication with a client failed to complete within the allotted time
           interval. This may be a result of network or system delays; or this may
           indicate that a malicious client is trying to cause a Denial of Service
           attack on the server.
*Action:   If the error occurred because of a slow network or system,
           reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,
           SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.
           If a malicious client is suspected, use the address in sqlnet.log to
           identify the source and restrict access. Note that logged addresses may
           not be reliable as they can be forged (e.g. in TCP/IP).

I tried to set the timeout:

SQLNET.INBOUND_CONNECT_TIMEOUT=600;
inbound_connect_timeout_listenername=600;

but I got an eror:

Error starting at line : 12 in command -
SQLNET.INBOUND_CONNECT_TIMEOUT=600
Error report -
Unknown Command


Error starting at line : 13 in command -
inbound_connect_timeout_listenername=0
Error report -
Unknown Command
Solution

  • You need to configure access on port 1521 SQL * NET between two instances of ORACLE. There are two ways to check access.

    1) tnsping db_alias 10
2) telnet IP_Address 1521

    If there is no access, then you need to perform the procedure described on the amazon website Adjusting Database Links for Use with DB Instances in a VPC.

    To use Oracle database links with Amazon RDS DB instances inside the same VPC or peered VPCs, the two DB instances should have a valid route between them. Verify the valid route between the DB instances by using your VPC routing tables and network access control list (ACL).

    The security group of each DB instance must allow ingress to and egress from the other DB instance. The inbound and outbound rules can refer to security groups from the same VPC or a peered VPC. For more information, see Updating Your Security Groups to Reference Peered VPC Security Groups.

    If you have configured a custom DNS server using the DHCP Option Sets in your VPC, your custom DNS server must be able to resolve the name of the database link target. For more information, see Setting Up a Custom DNS Server.