Forum Stats

  • 3,840,001 Users
  • 2,262,557 Discussions
  • 7,901,121 Comments

Discussions

Categories

dblink between 9i and 12.2

User_M8BYZ
User_M8BYZ Member Posts: 12 Red Ribbon
edited May 8, 2018 8:51AM in General Database Discussions

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

Tagged:
User_M8BYZ
· Share on TwitterShare on Facebook

Best Answer

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 7, 2018 12:56PM 
    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];

    · Share on TwitterShare on Facebook
  • User_M8BYZ
    User_M8BYZ Member Posts: 12 Red Ribbon
    edited May 7, 2018 3:15PM

    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.

    · Share on TwitterShare on Facebook
  • rcc50886
    rcc50886 Member Posts: 484 Bronze Badge
    edited May 7, 2018 3:27PM

    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

    Purpose

    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.

    Usage Notes

    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

    Values

    • 12a for Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later
    • 12 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 protocols
    • 8 for Oracle8i authentication protocol

    Default

    11

    Example

    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
    · Share on TwitterShare on Facebook
  • User_M8BYZ
    User_M8BYZ Member Posts: 12 Red Ribbon
    edited May 7, 2018 3:42PM

    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.

    · Share on TwitterShare on Facebook
  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited May 7, 2018 3:50PM

    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.

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited May 7, 2018 5:05PM 
    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?

    · Share on TwitterShare on Facebook
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 7, 2018 8:30PM

    >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.

    User_M8BYZUser_M8BYZ
    · Share on TwitterShare on Facebook
  • Hans Steijntjes
    Hans Steijntjes Member Posts: 614 Bronze Trophy
    edited May 8, 2018 2:11AM Answer ✓

    If you use basic datatypes, you can use the copy command from within SQL*Plus.

    See https://docs.oracle.com/en/database/oracle/oracle-database/18/sqpug/SQL-Plus-COPY-command.html#GUID-3565689A-6B8C-42FB-95F3-0412C707377E

    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.

    User_M8BYZUser_M8BYZ
    · Share on TwitterShare on Facebook
  • User_M8BYZ
    User_M8BYZ Member Posts: 12 Red Ribbon
    edited May 8, 2018 8:34AM

    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.

    · Share on TwitterShare on Facebook
  • User_M8BYZ
    User_M8BYZ Member Posts: 12 Red Ribbon
    edited May 8, 2018 8:51AM

    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.

    · Share on TwitterShare on Facebook
This discussion has been closed.