Migrate database through database link

Question

There's a database called FOO in an Oracle XE 10g server that's due to be decommissioned (old.example.com). I've been asked to migrate it to some other Oracle XE 11g server (new.example.com). Both servers are on the same LAN. I don't know anything about the database (it isn't a project of mine). There isn't a DBA in the room. My random Google searches suggest it's a terribly complicated task. I can use SQL Developer or SQL*Plus.

I've figured out that using impdp over a network link might be a reasonably straightforward method. But docs take most stuff for granted and I can't make it work.

So far:

1. I've assigned EXP_FULL_DATABASE to user FOO in source server (role DATAPUMP_EXP_FULL_DATABASE does not seem to exist in 10g).

2. I've created a temporary user called tmp_imp in target server with CREATE DATABASE LINK and DATAPUMP_IMP_FULL_DATABASE roles.

3. I'm stuck in the create database link part. I've logged into new.example.com as tmp_imp and tried all possible combinations of the CREATE DATABASE LINK statement. They either trigger ORA-00933: SQL command not properly ended or are accepted but produce a non-working link (ORA-39001: invalid argument value, ORA-39200: Link name "OLD_FOO" is invalid., ORA-12154: TNS:could not resolve the connect identifier specified).

This is one of my attempts:

CREATE DATABASE LINK OLD_FOO
CONNECT TO FOO IDENTIFIED BY 'pa$$w0rD'
USING '//old.example.com:1521/xe';

What am I doing wrong? Is there a simpler tool to migrate the database?

Answer 1

The USING clause expects a valid TNS entry or alias. Use this:

CREATE DATABASE LINK OLD_FOO
CONNECT TO FOO IDENTIFIED BY "pa$$w0rD"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = old.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))';

• Álvaro González: Thank you, that's exactly what I meant with docs taking stuff for granted. Do you have any idea why I get ORA-00933: SQL command not properly ended when I run a verbatin copy of your command? SQL*Plus reports it in the first quote of ´'pa$$w0rD'´, but removing quotes altogether doesn't fix it.
• Balazs Papp: @Álvaro González You should use double quotes for the password because of the special characters, I edited my answer.