Interoperability

How to Resolve ORA-03134: Connections to this server version are no longer supported

Intermediary Database 11g - Workaround to ORA-03134

Background of ORA-03134

As I mentioned in How to Resolve ORA-28040: No matching authentication protocol. ORA-28040 is not difficult to be solved if you were trying to connect from a lower to a higher version of database, say 9i to a 12c database. But for connections from 12c to 9i, you may see ORA-03134 like this:

[oracle@ora12c ~]$ sqlplus hr/hr@ora9i

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 19:19:40 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-03134: Connections to this server version are no longer supported.

Adding SQLNET.ALLOWED_LOGON_VERSION_SERVER or SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameter just like we solved ORA-28040 in another post is not helpful to solve ORA-03134.

Workaround to ORA-03134

In fact, there’s no real solution to ORA-03134, at least I can’t find any. But I think we can work around it by connecting an intermediary database, e.g. 11g in the middle of the way from 12c to 9i.

More specifically, the intermediary database, 11g acts like an agent of the 12c database to operate the data over the database link via synonyms which represent respective remote tables of 9i database.

Conventionally speaking, two generations gap between database versions is not too big to be supported, hence 11g database is able to support connections to this server version of Oracle 9i.

That’s why we can take advantage of the feature to develop our strategy of working around ORA-03134.

Please note that, the intermediary database doesn’t have to keep any data from 9i, because it’s just a platform which delivers requests and responds between both sides.

Further reading: DB Link, How and Why

Here are the steps to solve ORA-03134 in this post:

  1. Create User in 12c
  2. Add a DB Link in 12c for 11g Database
  3. Create User in 11g
  4. Add a DB Link in 11g for 9i Database
  5. Compose CREATE SYNONYM Statements in 9i
  6. Create Synonyms in 11g
  7. Create Synonyms in 12c
  8. Check Result for ORA-03134

1. Create User in 12c

We created a user which is the same as the source schema user in 9i, say HR.

[oracle@ora12c ~]$ sqlplus / as sysdba
...
SQL> create user hr account unlock identified by hr;

User created.

Please note that, it’s not necessarily to create user with the same name as the source user in the 9i database. In some cases, you might want to choose a different username to operate with.

Let’s move on, grant some proper privileges to this user, especially CREATE DATABASE LINK and CREATE SYNONYM.

SQL> grant create session to hr;

Grant succeeded.

SQL> grant alter session to hr;

Grant succeeded.

SQL> grant create database link to hr;

Grant succeeded.

SQL> grant create synonym to hr;

Grant succeeded.

Please note that, this user HR doesn’t contain any data.

2. Add a DB Link in 12c for 11g Database

Test the connection.

SQL> conn hr/hr
Connected.

Create a database link that points to the 11g database.

SQL> create database link ora11g_hr connect to hr identified by hr using 'ORA11G';

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
--------------------------------------------------------------------------------
ORA11G_HR

3. Create User in 11g

We create this user on behalf of the 12c User to retrieve data from 9i.

[oracle@ora11g ~]$ sqlplus / as sysdba
...
SQL> create user hr account unlock identified by hr;

User created.

Grant some proper privileges to this user, especially CREATE DATABASE LINK and CREATE SYNONYM.

SQL> grant create session to hr;

Grant succeeded.

SQL> grant alter session to hr;

Grant succeeded.

SQL> grant create database link to hr;

Grant succeeded.

SQL> grant create synonym to hr;

Grant succeeded.

Please note that, this user HR doesn’t contain any data.

4. Add a DB Link in 11g for 9i Database

Theoretically, the version gap is not too big to make 11g support connections to this server version of 9i database.

Test the connection.

SQL> conn hr/hr
Connected.

Create a database link that points to the 9i database.

SQL> create database link ora9i_hr connect to hr identified by hr using 'ORA9I';

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
--------------------------------------------------------------------------------
ORA9I_HR

The above two database links created in 12c and 11g are somewhat like the couriers who shuffle between 12c and 9i to deliver requests and responses. They are the foundation in our strategy to work around ORA-03134.

Before going further, I think maybe you should know what a database link can do and its restrictions.

5. Compose CREATE SYNONYM Statements in 9i

Now we have to compose two sets of CREATE SYNONYM statements in 9i database for creating synonyms in 11g and 12c respectively. Please note that, we don’t create any synonyms in 9i.

For 11g database: SQL> select 'CREATE SYNONYM ' || table_name || ' FOR HR.' || table_name || '@ORA9I_HR;' stmt_for_11g from user_tables;

STMT_FOR_11G
--------------------------------------------------------------------------------
CREATE SYNONYM COUNTRIES FOR HR.COUNTRIES@ORA9I_HR;
CREATE SYNONYM DEPARTMENTS FOR HR.DEPARTMENTS@ORA9I_HR;
CREATE SYNONYM EMPLOYEES FOR HR.EMPLOYEES@ORA9I_HR;
CREATE SYNONYM JOBS FOR HR.JOBS@ORA9I_HR;
CREATE SYNONYM JOB_HISTORY FOR HR.JOB_HISTORY@ORA9I_HR;
CREATE SYNONYM LOCATIONS FOR HR.LOCATIONS@ORA9I_HR;
CREATE SYNONYM REGIONS FOR HR.REGIONS@ORA9I_HR;

7 rows selected.

For 12c database: SQL> select 'CREATE SYNONYM ' || table_name || ' FOR HR.' || table_name || '@ORA11G_HR;' stmt_for_12c from user_tables;

STMT_FOR_12C
--------------------------------------------------------------------------------
CREATE SYNONYM COUNTRIES FOR HR.COUNTRIES@ORA11G_HR;
CREATE SYNONYM DEPARTMENTS FOR HR.DEPARTMENTS@ORA11G_HR;
CREATE SYNONYM EMPLOYEES FOR HR.EMPLOYEES@ORA11G_HR;
CREATE SYNONYM JOBS FOR HR.JOBS@ORA11G_HR;
CREATE SYNONYM JOB_HISTORY FOR HR.JOB_HISTORY@ORA11G_HR;
CREATE SYNONYM LOCATIONS FOR HR.LOCATIONS@ORA11G_HR;
CREATE SYNONYM REGIONS FOR HR.REGIONS@ORA11G_HR;

7 rows selected.

6. Create Synonyms in 11g

We can use above statements composed earlier to create synonyms in the 11g database in order to map each table in the 9i database. Synonyms over database links are the essential elements in our strategy to ORA-03134.

[oracle@ora11g ~]$ sqlplus hr/hr
...
SQL> CREATE SYNONYM COUNTRIES FOR HR.COUNTRIES@ORA9I_HR;

Synonym created.

SQL> CREATE SYNONYM DEPARTMENTS FOR HR.DEPARTMENTS@ORA9I_HR;

Synonym created.

SQL> CREATE SYNONYM EMPLOYEES FOR HR.EMPLOYEES@ORA9I_HR;

Synonym created.

SQL> CREATE SYNONYM JOBS FOR HR.JOBS@ORA9I_HR;

Synonym created.

SQL> CREATE SYNONYM JOB_HISTORY FOR HR.JOB_HISTORY@ORA9I_HR;

Synonym created.

SQL> CREATE SYNONYM LOCATIONS FOR HR.LOCATIONS@ORA9I_HR;

Synonym created.

SQL> CREATE SYNONYM REGIONS FOR HR.REGIONS@ORA9I_HR;

Synonym created.

7. Create Synonyms in 12g

This step is not really necessary as long as you add the database link in every SQL statement. But for convenience, I created them for all remote objects.

[oracle@ora12c ~]$ sqlplus hr/hr
...
SQL> CREATE SYNONYM COUNTRIES FOR HR.COUNTRIES@ORA11G_HR;

Synonym created.

SQL> CREATE SYNONYM DEPARTMENTS FOR HR.DEPARTMENTS@ORA11G_HR;

Synonym created.

SQL> CREATE SYNONYM EMPLOYEES FOR HR.EMPLOYEES@ORA11G_HR;

Synonym created.

SQL> CREATE SYNONYM JOBS FOR HR.JOBS@ORA11G_HR;

Synonym created.

SQL> CREATE SYNONYM JOB_HISTORY FOR HR.JOB_HISTORY@ORA11G_HR;

Synonym created.

SQL> CREATE SYNONYM LOCATIONS FOR HR.LOCATIONS@ORA11G_HR;

Synonym created.

SQL> CREATE SYNONYM REGIONS FOR HR.REGIONS@ORA11G_HR;

Synonym created.

8. Check Result for ORA-03134

Let’s query something that we don’t have in 12c.

SQL> select first_name from employees where last_name = 'Chen';

FIRST_NAME
--------------------
Ed

SQL> update employees set first_name = 'Eddie' where last_name = 'Chen';

1 row updated.

SQL> commit;

Commit complete.

SQL> exit
...
[oracle@ora12c1 ~]$ sqlplus hr/hr
...
SQL> select first_name from employees where last_name = 'Chen';

FIRST_NAME
--------------------
Eddie

The above result proves that we can query and modify the data in the 9i database from the 12c database in a recursive and indirect way. That is to say, we didn’t confront ORA-03134, we just bypass it.

Now you can treat local HR of the 12c database just like you connect to HR of the 9i database. Above all, that’s how we work around ORA-03134.

Conclusions to ORA-03134

Although performance is somewhat degraded, the trick for solving ORA-03134 is very effective for daily or incremental data migration after an initial data loading from 9i database.

On the other hand, you can also choose to dump data reversely from the 9i to the 12c database by INSERT INTO remote tables over a database link.

Please note that, several restrictions are imposed on database links, only queries and DML are allowable to do over a database link, you have to know what you cannot do on a database link.

Leave a Reply

Your email address will not be published. Required fields are marked *