Oracle DBLink creation and maintenance and ORA-02085 Solutions

Oracle DBLink creation and maintenance and ORA-02085 Solutions

Today's business needs to query Oracle data across databases, so we thought of dblink. Let's take a closer look at Oracle DBLink.

First, Oracle DBLink is divided into the following three types:

Private	The user who created the database link owns the database link.	The database link created under a specific schema of the local database. Only sessions that create the schema of the database link can use this database link to access a remote database. At the same time, only the Owner can delete its own private database link.
Public	The Owner is PUBLIC.	Public database links are database-level. All users in the local database with database access permissions or pl/SQL programs can use this database link to access the corresponding remote database.
Global	The Owner is PUBLIC.	Global database links are network-level. When an Oracle network uses a directory server, the directory server automatically creates and manages global database links (as net service names) for every Oracle Database in the network. users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. the use of an Oracle Names server has been deprecated. in this document, global database links refer to the use of net service names from the directory server.

Create dblink:

1. Create a public database link instance:

Create public database link todb151

Connect to hezi identified by manager

Using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.4.151) (PORT = 18000 ))

)

(CONNECT_DATA =

(SERVICE_NAME = newzqdb)

)

)';
Note the following:
1. The above hezi user is the user in the remote database you want to connect to (that is, the user on 4.151 ).

2. SQL> select * from pd_zh_cn.tb_goods @ todb151; cross-database query 4.151 through todb151, with the query permission of hezi. As long as the local user has the database access permission, do not create a public database connection, or create a large-Permission user hezi, so as not to allow local users with small permissions, go to remote 4.151 to view the information you did not see.

3. The content in the quotation marks after using actually contains the connection information of the corresponding database in the local tnsname. ora. Therefore, you can directly rewrite it:
Create public database link todb151

Connect to hezi identified by manager

Using 'db151 ';

The premise is that the local tnsname. ora file contains:

Db151 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.4.151) (PORT = 18000 ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = newzqdb)

)

)

SERVICE_NAME is generally the red part below,

SQL> show parameter service_names

NAME TYPE VALUE

-----------------------------------------------------------------------------

Service_names string newzqdb

Make sure that the service name of the listener is the same as that of the listener. This clearly tells you that When configuring static listening registration, you need to enter the global database name (GLOBAL_DBNAME) to input everything, as long as the listerner is guaranteed. GLOBAL_DBNAME and tnsnames in ora. SERVICE_NAME in ora must be consistent. Therefore, if it is a static listener, it should also be consistent when dblink is created. service_name = listerner. GLOBAL_DBNAME in ora (it is not necessarily the same when dblink is created)

[Oracle @ rac1 ~] $ Lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0-Production on 01-JUL-2015 21:39:53

Copyright (c) 1991,200 9, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = newzq.com.cn) (PORT = 18000 )))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0-Production
Start Date 01-JUL-2015 20:18:41
Uptime 0 days 1 hr. 21 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File/u01/oracle/products/database/11.2.0.1/network/admin/listener. ora
Listener Log File/u01/oracle/products/diag/tnslsnr/newzq/listener/alert/log. xml
Listening Endpoints Summary...
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = newzq.com.cn) (PORT = 18000 )))
Services Summary...
Service "newzqdb" has 1 instance (s ).
Instance "newzqdb", status UNKNOWN, has 1 handler (s) for this service...
The command completed successfully

2. Create a private dblink instance: note that there are no private words.

Create database link dblink

Connect to liuwenhe identified by liuwenhe

Using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.4.151) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = newzqdb)

)

)';

Manage dblink:

To view all the dblinks in the current database:

1. select * from dba_db_links;

Delete a specified DBLINK in the current database:

1. Delete public link

Drop public database link dblink;

2. Delete private link

Drop database link dblink;

ORA-02085 Solution:

// * Cause: a database link connected to a database with a different name.
// The connection is rejected.
// * Action: create a database link with the same name as the database it
// Connects to, or set global_names = false.
//

2) the cause and solution of the problem can be obtained from the error message description.
Error cause: the dblink name used is different from the name of the database to be connected.
Solution: There are two solutions
Solution 1: Create a dblink with the same name as the other database.
Solution 2: Adjust the value of the database parameter global_names to false to cancel this restriction.
 

Be sure to understand how to operate

Global_names and global_name seem very similar. The role of global_names is to determine whether to use global_name of the remote database when creating the db link. If

If global_names = true, the db link name must be global_name of the remote database; otherwise, the db link cannot be together after creation,

Suppose it is local: it is not remote, it is different from your own thinking.

SQL> show parameter global_names

NAME TYPE VALUE

-----------------------------------------------------------------------------

Global_names boolean TRUE

In this way, if you want to create a dblink locally, the name of the dblink must be global_name of the remote end 4.151, as shown below:

SQL> select * from global_name;

GLOBAL_NAME

--------------------------------------------------------------------------------

NEWZQDB

That is, it can only be named newzqdb, all the error ORA-02085

Create public database link newzqdb

Connect to system identified by manager

Using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.4.151) (PORT = 18000 ))

)

(CONNECT_DATA =

(SERVICE_NAME = newzqdb)

)

)';

You can cancel this restriction locally by using alter system set global_names = FALSE;. It must be noted that it is not a remote node.

That is to say, if the local global_names parameter is TRUE, only one available dblink can be created here.