How to Resolve ORA-17629 Oracle Error Message

  • by

ORA-17629 ORA-17627 ORA-12514

When we tried to clone a remote PDB via a DB link, we got ORA-17629 ORA-17627 and ORA-12514 in error stack like this:

SQL> create pluggable database ORCLPDB1 from ORCLPDB@ORCLPDB_LK file_name_convert=('ORCLPDB','ORCLPDB1') parallel 20;
create pluggable database ORCLPDB1 from ORCLPDB@ORCLPDB_LK file_name_convert=('ORCLPDB','ORCLPDB1') parallel 20
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file +DATA/ORCLCDB/ORCLPDB/DATA_3_01.dbf
ORA-17627: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-17629: Cannot connect to the remote database server

ORA-17629 means that it cannot connect to the remote database because of some underlying errors occurred, mostly, it's a connectivity problem. In the error stack, the most important error message we can see is ORA-12514.

Solution to ORA-12514

As I said in another post. There're several possibilities to see ORA-12514 when using a db link to connect a remote database.

So you should test at least two things:

  1. DB Link.
  2. You may use the following statement to test it.

    SQL> select sysdate from dual@ORCLPDB_LK;

    SYSDATE
    -------------------
    2021-07-31 19:58:39

    When you saw a system date time returned, the connectivity of DB link is alright.

  3. Connect Identifier.
  4. You may use tnsping to test the reachability of the database.

    [oracle@primary01 ~]$ tnsping BOSTON_ERPPDB
    ...
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPPDB)))
    OK (20 msec)

    When you saw OK message responded, the connectivity of connect identifier is alright.

If you have checked all the above items and nothing found, you should be aware of something else. For example, there could be various scenarios in a RAC database.

RAC System

For a RAC database, it's a little different. Since you have more than one node, you have to test the remote connection not only on the node you operate, but also on ALL nodes.

For instance, it may look fine when you test the connectivity of the connect identifier on node 1, but when you turn to node 2, it tells another story.

[oracle@primary02 ~]$ tnsping BOSTON_ERPPDB
...
TNS-03505: Failed to resolve name

As I said in How to Resolve TNS-03505: Failed to resolve name, it may be because you forgot to add a connect identifier on the second node in the first place. Consequently, when the cloning performed on the node 2 because of working parallelism or something, it cannot find a valid connect identifier to connect to.

Solution to ORA-12514

So the solution is obvious, just add the same entry of connect identifier on the rest of nodes. Here I copied tnsnames.ora from node 1 to this node to make all nodes equivalent.

[oracle@primary02 ~]$ cd $ORACLE_HOME/network/admin
[oracle@primary02 admin]$ scp -p primary01:$PWD/tnsnames.ora ./
tnsnames.ora                                              100% 6891     3.4MB/s   00:00

This solves the problem.

Leave a Reply

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