Write DMP files to aws rds oracle using Python scripts, pythonaws

Write DMP files to aws rds oracle using Python scripts, pythonaws

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 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; "# Write 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 cur.exe cute (UTL_FILE_NEW_FILE.format (file_name) chunk = 3000 f = open (src_name, 'rb') line = f. read (chunk) # Write RDS cur.exe cute (UTL_FILE_CREATE_FILE.format (file_name, line. hex () while (len (line)> 0: line = f. read (chunk) # Write 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 isbegindeclarev_file  utl_file.file_type;beginv_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.execute(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()

 

Reference link:

Https://stackoverflow.com/questions/23486198/how-to-import-oracle-dump-from-ec2-instance-to-rds-in-aws

Https://github.com/quentinhardy/odat/blob/master/UtlFile.py