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

Limits

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

Prerequisites

  • 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 for 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 for PPAS database tables is created in the Oracle database.
  • The PostgreSQL client is uploaded to the Oracle database server.

Procedure

Note The following example demonstrates how to migrate data from an ApsaraDB RDS for 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 for PPAS instance in the ECS instance.
    [root@oraclexe ~]# vim ~/.pgpass
     [root@oraclexe ~]# cat ~/.pgpass 
     rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com:3433:ora:myadmin:xxxxxxx
     //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 for PPAS instance.
    [root@oraclexe ~]# psql -h rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com -p 3433 -U myadmin ora
     psql.bin (9.3.1.3, server 9.3.13.37)
     Enter "help" to obtain help information.
     ora=>

    If you can log on to the ApsaraDB RDS for 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 ppas_exp_all_tables_to_csv.sh file.
       vi ppas_exp_all_tables_to_csv.sh
    2. Insert the following text into the ppas_exp_all_tables_to_csv.sh script:
      # ppas_exp_all_tables_to_csv.sh <hostname> <port> <username> <database>
       # Author: Xiao Shaocong (Scott Siu)
       # E-Mail: shaocong.xsc@alibaba-inc.com
       TMP_PATH="/tmp/ppas_tables_$1_$2_$3_$4"
       mkdir $TMP_PATH
       if [ $? -ne 0 ]
       then
           exit 1;
       fi
       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 ppas_exp_all_tables_to_csv.sh script.
     [root@oraclexe ~]# chmod 0755 ppas_exp_all_tables_to_csv.sh
  6. Execute the data export script in the ECS instance.
    [root@oraclexe ~]# ./ppas_exp_all_tables_to_csv.sh rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com 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/*
     deptno,dname,loc
     10,ACCOUNTING,NEW YORK
     20,RESEARCH,DALLAS
     30,SALES,CHICAGO
     40,OPERATIONS,BOSTON
     empno,ename,job,mgr,hiredate,sal,comm,deptno
     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
     empno,startdate,enddate,job,sal,comm,deptno,chgdesc
     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.

Troubleshooting

Error

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

[root@oraclexe ~]# ./ppas_exp_all_tables_to_csv.sh rm-2ze466l5u1k657yyn.ppas.rds.aliyuncs.com 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