ORA-02019 error handling

The ORA-02019 error message is a description of the remote database connection not found, typically occurring in the local database cannot connect to the remote database. There are many causes for this problem, such as network connection, connection mode (tnsnames), and dblinkc creation. The fault described below is strange.

I. Error

SQL> select * From scanfilename@dss.m85; </P> <p> select * From scanfilename@dss.m85 </P> <p> ORA-02019: remote database connection not found description </P> <p> SQL> select * From xdoc_file@DSS.M85; </P> <p> select * From xdoc_file@DSS.M85 </P> <p> ORA-02019: Remote database connection not found <br/>

Ii. Analysis and Solution
1. Current Database Version

SQL> select * from V $ version; </P> <p> banner <br/> production <br/> Oracle8i Enterprise Edition Release 8.1.7.4.1-production <br/> PL/SQL release 8.1.7.4.0-production <br/> core8.1.7.2.1production <br/> TNS for 32-bit windows: version 8.1.7.4.0-production <br/> nlsrtl version 3.4.1.0.0-production <br/>

2. The currently created database link

SQL> select * From dba_db_links; </P> <p> owner db_link username host created <br/> certificate ---------- ------------- ----------- <br/> dims DSS. m85 viewpic PIC 2007-11-8 1 </P> <p> SQL> select * from V $ dblink; --> currently, no database link is in use </P> <p> db_link owner_id has been used protocol has in_transaction update_sent enabled <br/> ---------- --------- has -------- ------------ ------------- has <br />

3. The following stored procedure uses a distributed database

SQL> select text from dba_source where name = 'P _ process_pic_valid '; </P> <p> text <br/> processing <br/> procedure "p_process_pic_valid" <br/> (processdate in date -- processing date. If this parameter is not input, it indicates the current day <br/>) as <br/> reccount int; <br/> picturename nvarchar2 (50); <br/> mntnopermask varchar2 (10 ); -- maintain the account prefix of the Operations Department <br/> unzip nvarchar2 (50); <br/> picid nvarchar2 (50); <br/> filmid nvarchar2 (50 ); <br/> v_maxid number; -- 20100720 <br/> begin <br/> limit 20100720 <br/> -- the largest ID of the imported data <br/> select max (ID) into v_maxid from t_scanfilename_m85; <br/> -- import new data <br/> execute immediate 'insert into t_scanfilename_m85 <br/> select * From scanfilename@dss.m85 </P> <p> text <br/> explain -------------------------------------------------------------------------------- <br/> where ID>: col_1 '<br/> using v_maxid; <br/> commit; <br/> ----------------------------------------------- 20100720 <br/> mntnopermask: = 'Gl '; <br/> -- import valid piece data from the Graph System <br/> insert into t_pic_xdoc_file (CPH, Cl, wldd, FX, wlrq, wlxm, lrsj, struct, picture_name <br/> select t1.cph, t1.cl, t1.wldd, t1.fx, t1.wlrq, t1.wlxm, t1.lrsj, t1.20., t2.file <br/>/* From xdoc_file@DSS.M85 T1, scanfilename@DSS.M85 T2 */-- 20100720 <br/> from xdoc_file@DSS.M85 T1, t_scanfilename_m85 t2 <br/> where t1.xh = t2.xh <br/> and t2.id> 15717858 <br/> and not exists (select picture_name from t_pic_xdoc_file where PICT <br/> and substr (t1.20., 1, 2) = mntnopermask <br/> and (t1.lrsj> = sysdate-3); <br/> -- Take the effective piece data from the graphic system, save the analysis to the statistical table. <br/> -- Process valid slices. <br/> for C in (select XF. *, rowid from t_pic_xdoc_file XF where status = 1) <br/> loop </P> <p> SQL> select table_name, owner from dba_tables where table_name in ('scanfilename ', 'xdoc _ file '); </P> <p> table_name owner <br/> ------------------------------------------------------ <br/> scanfilename dims </P> <p> SQL> select * From dims. scanfilename where rownum <5; </P> <p> ID filename XH <br/> --------------------------------------- average ------------ <br/> 8606178 bytes <br/> 8605441 bytes <br/> 8605445 bytes <br/> 8605449 vswitch00000002101002120070614095523011900027.jpg <br/>

4. Try to execute the operation

SQL> select * From scanfilename@dss.m85; </P> <p> select * From scanfilename@dss.m85 </P> <p> ORA-02019: remote database connection not found description </P> <p> SQL> select * From xdoc_file@DSS.M85; </P> <p> select * From xdoc_file@DSS.M85 </P> <p> ORA-02019: Remote database connection not found </P> <p> SQL> show user; <br/> User is "sys" </P> <p> SQL> conn dims/dims_12345 @ dmsdb <br/> connected to Oracle8i Enterprise Edition Release 8.1.7.4.1 <br/> connected as dims </P> <p> SQL> select * From dba_db_links; </P> <p> owner db_link username host created <br/> ----------------- ------------- --------------- ----------- <br/> dims DSS. m85 viewpic PIC 2007-11-8 1 </P> <p> SQL> select * From user_db_links; </P> <p> db_link Username Password host created <br/> ----------------- ---------------------------------------- ------------- ----------- <br/> DSS. m85 viewpic PIC 2007-11-8 1 <br/>

5. Switch the account and use the distributed database, prompting that the listener is unavailable.

SQL> select * From xdoc_file@DSS.M85 </P> <p> ORA-12514: TNS: the listener process cannot parse the SERVICE_NAME given in the connection descriptor </P> <p> SQL> select * From scanfilename@DSS.M85; </P> <p> select * From scanfilename@DSS.M85 </P> <p> ORA-12514: TNS: the listener process cannot parse the SERVICE_NAME provided in the connection descriptor </P> <p> PIC = <br/> (description = <br/> (address_list = <br/> (Address = (Protocol = TCP) (host = 100.100.1.31) (Port = 1521) <br/> (CONNECT_DATA = <br/> (Server = dedicated) <br/> (SERVICE_NAME = DSS) <br/>

6. Use tnsping for testing

C: \> tnsping PIC </P> <p> TNS Ping utility for 32-bit windows: version 8.1.7.4.0-production on 26-7 month-2 <br/> 011 15:32:35 <br/> (c) copyright 1997 Oracle Corporation. all rights reserved. <br/> attempting to contact (address = (Protocol = TCP) (host = 100.100.1.31) (Port = 1521) <br/> OK (20 ms) </P> <p> C: \> sqlplus viewpic/viewpic @ pic --> sqlplus cannot be logged on </P> <p> SQL * Plus: release 8.1.7.0.0-production on Tuesday July 26 15:33:15 2011 </P> <p> (c) Copyright 2000 Oracle Corporation. all rights reserved. </P> <p> error: <br/> ORA-12514: TNS: the listener process cannot parse the SERVICE_NAME given in the connection descriptor <br/>

7. view the local tnsnames. ora

PIC = <br/> (description = <br/> (address_list = <br/> (address = (Protocol = TCP) (host = 100.100.1.31) (Port = 1521 )) <br/>) <br/> (CONNECT_DATA = <br/> (Server = dedicated) <br/> (SID = DSS) --> (originally SERVICE_NAME, change it to Sid) <br/>) </P> <p> C: \> sqlplus viewpic/viewpic @ pic --> Use sqlplus again to log in normally </P> <p> SQL * Plus: release 8.1.7.0.0-production on Wednesday July 27 09:49:57 2011 <br/> (c) Copyright 2000 Oracle Corporation. all rights reserved. </P> <p> connection: <br/> Oracle8i Enterprise Edition Release 8.1.7.0.0-64bit production <br/> with the partitioning and Parallel Server options <br/> jserver release 8.1.7.0.0-64bit production </P> <p> SQL> select * from V $ version; </P> <p> banner <br/> supervisor <br/> Oracle8i Enterprise Edition Release 8.1.7.0.0-64bit production <br/> PL/SQL release 8.1.7.0.0-production <br/> core 8.1.7.0.0 production <br/> TNS for IBM/aix risc system/6000: version 8.1.7.0.0-developmen <br/> nlsrtl version 3.4.1.0.0-production </P> <p> SQL> select * From scanfilename@DSS.M85 where rownum <5; </P> <p> ID filename XH <br/> ---------- certificate ------------------ <br/> 8606178 bytes <br/> 8605441 v0000000000000002101002120070614095523011900023.jpg <br/> 8605445 bytes <br/> br/>

8. Why does it cause SERVICE_NAME to be changed to Sid? Previously, SERVICE_NAME was used normally. Since DSS. m85 was heavy before the fault
No changes were made. SERVICE_NAME is recommended after Oracle 8, but SERVICE_NAME can be used normally in Oracle 8.

Iii. More references

For performance optimization, see

Oracle hard parsing and soft Parsing

Shared Pool Tuning)

Buffer cache adjustment and optimization (1)

Use of Oracle table cache (caching table)

 

For the Oracle architecture, see

Oracle tablespace and data files

Oracle Password File

Oracle parameter file

Oracle online redo log file)

Oracle Control File)

Oracle archiving logs

Oracle rollback and undo)

Oracle database instance startup and Shutdown Process

Automated Management of Oracle 10g SGA

Oracle instances and Oracle databases (Oracle Architecture)

 

For more information about the flash back feature, see

Flashback Database)

Flashback drop & recyclebin)

Oracle flash back features (flashback query, flashback table)

Oracle flash back feature (flashback version, flashback transaction)

 

For more information about user-managed backup and recovery, see

Oracle cold backup

Oracle Hot Backup

Concept of Oracle backup recovery

Oracle instance recovery

Oracle recovery based on user management (describes media recovery and processing in detail)

System tablespace management and Backup Recovery

Sysaux tablespace management and recovery

 

For information on RMAN backup recovery and management, see

RMAN overview and architecture

RMAN configuration, Monitoring and Management

Detailed description of RMAN backup

RMAN restoration and recovery

Create and use RMAN catalog

Create RMAN storage script based on catalog

Catalog-based RMAN backup and recovery

Use RMAN to migrate a file system database to ASM

RMAN backup path confusion (when using plus archivelog)

 

For Oracle faults, see

Error Handling for ORA-32004

ORA-01658 error.

CRS-0215 error handling

ORA-00119, ORA-00132 error handling

Another spfile setting error causes the database to fail to start.

Misunderstanding and setting of the parameter fast_start_mttr_target = 0

Spfile error causing database startup failure (ORA-01565)

 

For more information about ASM, see

Create an ASM instance and an ASM Database

Management of ASM disks and directories

Use asmcmd to manage the ASM directory and files

 

For more information about SQL and PLSQL, see

Common sqlplus commands

Replace variables with SQL * Plus Environment Settings

SQL plus paging using uniread

SQL Basics--> SELECT query

SQL Basics--> Use of new_value

SQL Basics--> Set operation (Union and Union all)

SQL Basics--> Common functions

SQL Basics--> View (create view)

SQL Basics--> Create and manage tables

SQL Basics--> Multi-Table query

SQL Basics--> Filtering and sorting

SQL Basics--> Subquery

SQL Basics--> Grouping and grouping Functions

SQL Basics--> Hierarchical query (start by... connect by prior)

SQL Basics--> Rollup and cube operators implement data aggregation

PL/SQL--> Cursor

PL/SQL--> Exception Handling)

PL/SQL--> Language basics

PL/SQL--> Process Control

PL/SQL--> PL/SQL records

PL/SQL--> Create and manage packages

PL/SQL--> Implicit cursor (SQL % found)

PL/SQL--> Package overloading and initialization

PL/SQL--> Use of dbms_ddl package

PL/SQL--> DML triggers

PL/SQL--> Instead of trigger

PL/SQL--> Stored Procedure

PL/SQL--> Function

PL/SQL--> Dynamic SQL

PL/SQL--> Common Errors of dynamic SQL

 

Other Oracle features

Common Oracle directory structure (10 Gb)

Use OEM, SQL * Plus, and iSQL * Plus to manage Oracle instances

Logging mode (logging, force logging, nologging)

Logging and nologging on table and index segments

Detailed description of oralce OMF Functions

Oracle users, object permissions, and system Permissions

Oracle role and configuration file

Oracle Partition Table

Oracle External table

Use external tables to manage Oracle alarm logs (alaert _ $ Sid. Log)

Cluster table and cluster Table Management (index clustered tables)

Use of Data Pump expdp export tool

Use of Data Pump impdp import tool

Import and Export Oracle Partition Table Data

SQL * loader usage

Enable User Process Tracking

Configure dynamic service registration for non-default ports

Configure the Oracle client to connect to the database

Differences between system sys and sysoper sysdba

Oracle_sid, db_name, instance_name, db_domian, global_name

Complete Oracle patches (Oracle 9i 10g 11g path)

Upgrade Oracle 10.2.0.1 to 10.2.0.4

Oracle kill session