Not clear how to pass SID for connection string

[dmaidaniuk]

Hi All,

I'm trying to execute data migration to Oracle with the next preparation script:

CREATE SERVER oracle FOREIGN DATA WRAPPER oracle_fdw
   OPTIONS (dbserver '//oracle.server.domain:1521/SID');

GRANT USAGE ON FOREIGN SERVER oracle TO migrator;

CREATE USER MAPPING FOR migrator SERVER oracle
   OPTIONS (user 'orauser', password 'orapwd');

Once I'm starting the command:

SELECT db_migrate(
   plugin => 'ora_migrator',
   server => 'oracle',
   only_schemas => '{IRBIS_ISAOD}'
);

the next error throws:

NOTICE: Creating staging schemas "fdw_stage" and "pgsql_stage" ... NOTICE: Creating foreign metadata views in schema "fdw_stage" ... ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptorconnection for foreign table "columns" cannot be established

ERROR: connection for foreign table "columns" cannot be established SQL state: HV00N Detail: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Context: SQL statement "SELECT schema, table_name, column_name, position, type_name, length, precision, scale, nullable, default_value FROM columns WHERE only_schemas IS NULL OR schema =ANY (only_schemas)" PL/pgSQL function public.db_migrate_refresh(name,name,name,name[]) line 63 at OPEN PL/pgSQL function public.db_migrate_prepare(name,name,name,name,name[],jsonb) line 327 at RETURN PL/pgSQL function public.db_migrate(name,name,name,name,name[],jsonb,boolean) line 18 at assignment

As I understand, ora_migrator tried to interpret Oracle's DB SID as Service Name. How can I pass SID for the connection string properly?

[laurenz]

If you are using the easy naming method, you have to supply the service name rather than the SID.

If you want to use the SID, use a connect string:

ALTER SERVER oracle OPTIONS
   (SET dbserver '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.server.domain)(PORT=1521))(CONNECT_DATA=(SID=SID)))');

[dmaidaniuk]

Thanks, it works.