This topic describes how to migrate data from an ApsaraDB RDS PPAS instance to an on-premises Oracle database.


Currently, only files of common data types can be exported. Binary data types such as BLOB are not supported.


  • A server that has an Oracle database installed is available.
  • The IP address of the Oracle database server is added to the whitelist of the ApsaraDB RDS PPAS instance. For more information, see Configure a whitelist for an RDS for PPAS instance.
  • A table that has the same schema as ApsaraDB RDS PPAS database tables is created in the Oracle database.
  • The PostgreSQL client is uploaded to the Oracle database server.


Note The following example demonstrates how to migrate data from an ApsaraDB RDS PPAS instance to an Oracle database installed in an ECS instance. In this example, the operating system of the ECS instance is CentOS 6.5.
  1. Install the PostgreSQL client on the Oracle database server.
    [root@oraclexe ~]# yum install postgresql.x86_64
     [root@oraclexe ~]# /usr/bin/psql --version
     psql (PostgreSQL) 8.4.20
  2. Configure password-free logon for the ApsaraDB RDS PPAS instance in the ECS instance.
    [root@oraclexe ~]# vim ~/.pgpass
     [root@oraclexe ~]# cat ~/.pgpass
     //The parameter must be in the HOSTNAME:PORT:DATABASE:USERNAME:PASSWORD format.
     [root@oraclexe ~]# chmod 0600 ~/.pgpass
    Note The .pgpass configuration file is located in the HOME directory.
  3. Test the connectivity between the ECS instance and the ApsaraDB RDS PPAS instance.
    [root@oraclexe ~]# psql -h -p 3433 -U myadmin ora
     psql.bin (, server
     Enter "help" to obtain help information.

    If you can log on to the ApsaraDB RDS PPAS instance as the ora user, the connection has been established. After the test is complete, switch back to the root user.

    ora=> \q
     [root@oraclexe ~]#
  4. Create a data export script in the ECS instance.
    1. Create the file.
    2. Insert the following text into the script:
      # <hostname> <port> <username> <database>
       # Author: Xiao Shaocong (Scott Siu)
       # E-Mail:
       mkdir $TMP_PATH
       if [ $? -ne 0 ]
           exit 1;
       echo "select '$1 $2 $3 $4 ' || tablename || ' $TMP_PATH ' || tablename from pg_tables where tableowner='$3' and (schemaname='$3' or schemaname='public');" > /tmp/ppas_tables_$1_$2_$3_$4.sql
       psql -h $1 -p $2 -U $3 $4 -f /tmp/ppas_tables_$1_$2_$3_$4.sql | head -n -2 | tail -n +3 | awk -F " " '{printf ("psql -h %s -p %s -U %s %s -c \"\\copy %s TO '\''%s/%s'\'' CSV HEADER\"\n",$1,$2,$3,$4,$5,$6,$7)}' | sh
  5. Grant execution permission to the script.
     [root@oraclexe ~]# chmod 0755
  6. Execute the data export script in the ECS instance.
    [root@oraclexe ~]# ./ 3433 myadmin ora
  7. Verify the data exported to a CSV file.
    [root@oraclexe ~]# cat /tmp/ppas_tables_rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com_3433_myadmin_ora/*
     7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
     7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
     7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
     7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
     7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
     7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
     7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
     7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
     7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
     7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
     7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
     7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
     7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
     7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10
     7369,17-DEC-80 00:00:00,,CLERK,800.00,,20,New Hire
     7499,20-FEB-81 00:00:00,,SALESMAN,1600.00,300.00,30,New Hire
     7521,22-FEB-81 00:00:00,,SALESMAN,1250.00,500.00,30,New Hire
     7566,02-APR-81 00:00:00,,MANAGER,2975.00,,20,New Hire
     7654,28-SEP-81 00:00:00,,SALESMAN,1250.00,1400.00,30,New Hire
     7698,01-MAY-81 00:00:00,,MANAGER,2850.00,,30,New Hire
     7782,09-JUN-81 00:00:00,,MANAGER,2450.00,,10,New Hire
     7788,19-APR-87 00:00:00,12-APR-88 00:00:00,CLERK,1000.00,,20,New Hire
     7788,13-APR-88 00:00:00,04-MAY-89 00:00:00,CLERK,1040.00,,20,Raise
     7788,05-MAY-90 00:00:00,,ANALYST,3000.00,,20,Promoted to Analyst
     7839,17-NOV-81 00:00:00,,PRESIDENT,5000.00,,10,New Hire
     7844,08-SEP-81 00:00:00,,SALESMAN,1500.00,0.00,30,New Hire
     7876,23-MAY-87 00:00:00,,CLERK,1100.00,,20,New Hire
     7900,03-DEC-81 00:00:00,14-JAN-83 00:00:00,CLERK,950.00,,10,New Hire
     7900,15-JAN-83 00:00:00,,CLERK,950.00,,30,Changed to Dept 30
     7902,03-DEC-81 00:00:00,,ANALYST,3000.00,,20,New Hire
     7934,23-JAN-82 00:00:00,,CLERK,1300.00,,10,New Hire
  8. Import the CSV file to the Oracle database.



When you execute the data export script, the system prompts that a directory cannot be created, as shown below.

[root@oraclexe ~]# ./ 3433 myadmin ora
mkdir: Unable to create directory"/tmp/ppas_tables_rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com_3433_myadmin_ora": The file already exists.

Troubleshooting procedure

Delete the existing directory.

[root@oraclexe ~]# rm -rf /tmp/ppas_tables_rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com_3433_myadmin_ora