Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
dblink between 9i and 12.2

Is it possible to create a DBLink between a database on Oracle9i (9.2.0.6.0) and Oracle Database 12c (12.2.0.1.0) - 64bit?
I'm creating a new database using 12c and I want to be able to get my data from the current 9i database to the 12c database.
They are on 2 different servers.
1. Do I need to create an entry in the tnsnames.ora on either server or both?
2. Using oracle client 11.2.0.4 lets me connect to both databases and I can select from tables in both. Do I need to install it on either server or both?
Thank you
Best Answer
-
If you use basic datatypes, you can use the copy command from within SQL*Plus.
The command would look something like this:
COPY FROM UN/[email protected]
TO UN/[email protected]
INSERT target_table_name
USING SELECT * from source_table_name;
The aliases V9 and V12 are to be resolved locally using your tnsnames. There is no need for a database link.
Use a 11g version of Oracle to issue the copy command.
Answers
-
3331524 wrote:Is it possible to create a DBLink between a database on Oracle9i (9.2.0.6.0) and Oracle Database 12c (12.2.0.1.0) - 64bit?I'm creating a new database using 12c and I want to be able to get my data from the current 9i database to the 12c database.They are on 2 different servers. 1. Do I need to create an entry in the tnsnames.ora on either server or both?2. Using oracle client 11.2.0.4 lets me connect to both databases and I can select from tables in both. Do I need to install it on either server or both?Thank you
use export/import
or
insert into [email protected] select * from [email protected];
-
Hi John,
Thank you for your reply. I'm not allowed to use the export/import process. I need to create a script that will go through all my tables and upload them to the new database with the same tables and log everything.
If I setup the tnsnames.ora on the 12c database server to point to the 9i database server will it see it? I'm not sure if 12c is too far ahead version wise to see a 9i database.
-
You can connect from 12c to 9i using db link, however you need to set the following parameter to 8 in your sqlnet.ora file on the 12c db server.
ALLOWED_LOGON_VERSION_CLIENT=8
https://docs.oracle.com/database/121/NETRF/sqlnet.htm#NETRF2010
SQLNET.ALLOWED_LOGON_VERSION_CLIENT
To set the minimum authentication protocol allowed for clients, and when a server is acting as a client, such as connecting over a database link, when connecting to Oracle Database instances.
The term
VERSION
in the parameter name refers to the version of the authentication protocol, not the Oracle Database release.If the version does not meet or exceed the value defined by this parameter, then authentication fails with an
ORA-28040: No matching authentication protocol
error.See Also:
Oracle Database Security Guide
12a
for Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later12
for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended)11
for Oracle Database 11g authentication protocols (default)10
for Oracle Database 10g authentication protocols8
for Oracle8i authentication protocol
11
If an Oracle Database 12c database hosts a database link to an Oracle Database 10g database, then the
SQLNET.ALLOWED_LOGON_VERSION_CLIENT
parameter should be set as follows in order for the database link connection to proceed:SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
-
Thank you rcc,
I will try this out as soon as I can and let you know what my results are.
Thank you for your help it is appreciated.
-
The combination of an oracle 12c database accessing a 9i database is a non-certified combination.
It might work, but you can also encounter strange errors.
You could create a 11g database as an intermediate database (9i references 11g, 11g references 12c).
Or perhaps you could create INSERT/UPDATE/MERGE command from 9i and apply these with a 11g client to 12c.
-
I'm not allowed to use the export/import process.
Then have someone that IS ALLOWED to use export/import create the DMP file for you.
I need to create a script that will go through all my tables and upload them to the new database with the same tables and log everything.
Sorry - but that just is NOT going to happen.
Your new database doesn't have the tables, indexes, constraints that the old database has.
Those objects need to be created too.
Using export/import will create those objects for you and will do it PROPERLY.
You will NOT be able to write a script to create all of the objects properly.
And my guess is you don't have the DDL in a version control system either do you?
-
>Using oracle client 11.2.0.4 lets me connect to both databases and I can select from tables in both.
if you are using sqlplus V11.2 & do as below
INSERT INTO [email protected] SELECT * FROM [email protected]
then no DBLINK is required only 2 entries in single tnsnames.ora file used by sqlplus.
-
If you use basic datatypes, you can use the copy command from within SQL*Plus.
The command would look something like this:
COPY FROM UN/[email protected]
TO UN/[email protected]
INSERT target_table_name
USING SELECT * from source_table_name;
The aliases V9 and V12 are to be resolved locally using your tnsnames. There is no need for a database link.
Use a 11g version of Oracle to issue the copy command.
-
rp thanks for your reply.
The issue isn't that I can't do export/import that just isn't the way the lead guy wants it done. He wants scripts of all the objects. I have already created all the scripts and ran them against the new database and now I'm working on finding a way to take the data from my 9i database to my 12c database.
I'm working with rcc50886 suggestion this morning and will update my results.
Thank you everyone for your suggestions.
-
Thank you Hans,
That does work. I just have to run it by my lead, but I hope that this will do it.
Thank you everyone for your suggestions.