Write DMP files to aws rds Oracle using Python scripts

Source: Internet
Author: User

Write DMP files to aws rds Oracle using Python scripts

RDS (Oracle) does not directly support SSH or FTP (based on security factors). Therefore, the dmp files from the original system expdp or exp cannot be directly imported into RDS. In the official documentation, it is recommended to migrate data from the original database to RDS through networklink or dblink. This method has been ignored for the moment considering network and security factors.

You can use the Oracle UTL_FILE package to write dmp files to the RDS folder and read the local files in EC2 using the Python script, the function of locally uploading data from EC2 to RDS.

The Code is as follows:

Def cpy_to_rds ():
File_name = 'test4. dmp'
Src_name = 'C:/testdata/test4.dmp'
# Create or overwrite an empty file under RDS and DATA_PUMP_DIR
UTL_FILE_NEW_FILE = "DECLARE fi UTL_FILE.FILE_TYPE; BEGIN fi: = UTL_FILE.fopen ('data _ PUMP_DIR ',' {0} ', 'wb', 32766); UTL_FILE.fclose (fi); END ;"
# Writing data to RDS
UTL_FILE_CREATE_FILE = "DECLARE "\
"Fi UTL_FILE.FILE_TYPE ;"\
"V_buffer RAW (32760 );"\
"BEGIN "\
"V_buffer: = hextoraw ('{1 }');"\
"Fi: = UTL_FILE.fopen ('data _ PUMP_DIR ',' {0} ',' AB ');"\
"UTL_FILE.put_raw (fi, v_buffer );"\
"UTL_FILE.fclose (fi );"\
"END ;"

Con = cx_Oracle.connect ('user/Password @ Sid ')
Cur = con. cursor ()
# Create or overwrite an empty file under RDS and DATA_PUMP_DIR
Cur.exe cute (UTL_FILE_NEW_FILE.format (file_name ))
Chunk = 3000
F = open (src_name, 'rb ')
Line = f. read (chunk)
# Writing data to RDS
Cur.exe cute (UTL_FILE_CREATE_FILE.format (file_name, line. hex ()))
While (len (line)> 0:
Line = f. read (chunk)
# Writing data to RDS
Cur.exe cute (UTL_FILE_CREATE_FILE.format (file_name, line. hex ()))
Cur. close ()
Con. close ()

Note that the chunk setting cannot be 32760.

The UTL_FILE_CREATE_FILE statement can also be converted to a Function in RDS:

Create or replace function gen_dmp (I _name IN VARCHAR2, I _buffer IN RAW) return varchar2 is
Begin
Declare
V_file utl_file.file_type;
Begin
V_file: = utl_file.fopen ('data _ PUMP_DIR ', I _name,' AB ');
Utl_file.put_raw (v_file, I _buffer );
Utl_file.fclose (v_file );
Return 'OK ';
End;
End;

Call this function in Python so that the chunk can be set to the maximum value of 32767.

Def cpy_to_rds_func ():
File_name = 'tes4. dmp'
Src_name = 'C:/testdata/test4.dmp'
UTL_FILE_NEW_FILE = "DECLARE fi UTL_FILE.FILE_TYPE; BEGIN fi: = UTL_FILE.fopen ('data _ PUMP_DIR ',' {0} ', 'wb', 32766); UTL_FILE.fclose (fi); END ;"
Con = cx_Oracle.connect ('user/Password @ Sid ')
Cur = con. cursor ()
Cur.exe cute (UTL_FILE_NEW_FILE.format (file_name ))
Chunk = 32760
F = open (src_name, 'rb ')
Line = f. read (chunk)
Cur. callfunc ('gen _ dmp ', cx_Oracle.STRING, (file_name, line ))
While (len (line)> 0:
Line = f. read (chunk)
Cur. callfunc ('gen _ dmp ', cx_Oracle.STRING, (file_name, line ))
Cur. close ()
Con. close ()

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151365.htm

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.