Tag Archives: Oracle

Oracle ORA-12162: TNS:net service name is incorrectly specified

Recently, when I was combing the environment, I found that the environment was not generally complex and the configuration was not generally messy. When I was just combing the environment, I found that a library could not be connected through conn/as SYSDBA. The specific process is as follows:

1.Error message

[oracle@ ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 24 09:48:26 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn /as sysdba;
ERROR:
ORA-12162: TNS:net service name is incorrectly specified


SQL> exit

2.Cause of error

Generally, this kind of error is caused by the configuration of environment variables, or the correct Oracle is not configured_ SID、ORACLE_ Home, or listen to configuration environment variables and. Bash_ Profile environment variable configuration is inconsistent

Here, it is found that the operating system environment variable is not configured with Oracle_ SID

[oracle@ ~]$ env |grep ORA
ORACLE_BASE=/data/oracle
ORACLE_HOME=/data/oracle/product/11.2.0/dbhome_1

3.Solutions

Add Oracle user to. Bash_ Add specific Oracle to profile_ Sid and application

[oracle@ ~]$ vi ~/.bash_profile 
ORACLE_BASE=/data/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
ORACLE_SID=orcl
export ORACLE_SID ORACLE_BASE ORACLE_HOME
[oracle@ ~]$ source ~/.bash_profile 

[oracle@ ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 24 10:00:40 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> conn /as sysdba;
Connected.
SQL>

Oracle start monitoring error tns-12547: TNS: lost contact

Background:

The database has been running very well, but today there is a fatal blow. When the company’s application program is about 10:30, all the sites can’t be accessed, and the page only prompts that the site can’t be found. At first, I thought there was a problem with the network or VIP. After checking for a long time, I didn’t get any message. Then I went to Weblogic and found that the pool status of the connection with the database is invalid, Check Oracle 10g DB immediately

questions :

First, check whether the monitor program of DB works normally


  
  
  
  
  
  
  
 
  
 
  
 
   
   
lsnrctlstatus

Directly stuck, if the database crash, it is the tragedy of the tragedy! After checking the database, it runs normally. If you check alter.log, you will only be prompted with false_ Client, indecisive, I used kill


  
  
  
  
  
  
  
 
  
 
  
 
   
   
ps-ef|grepLISTENER

  
  
  
  
  
 
  
 
  
 
   
   
oracle254781006:47?00:00:00/home/oracle/OraBase/OraHome1/bin/tnslsnrLISTENER-inherit

  
  
  
  
  
 
  
 
  
 
   
   
root2593225786008:40pts/100:00:00grepLISTENER

  
  
  
  
  
 
  
 
  
 
   
   
kil-925478

Restart monitoring report the following error:

lsnrctl start
LSNRCTL for Linux: Version 9.2.0.4.0 – Production on 06-JUL-2009 14:37:59
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /opt/oracle/product/9ir2/bin/tnslsnr: please wait…
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

solution:

This is only with the help of Google. After searching on the Internet, we found that the reasons are as follows:

The operating system of the database server is red hat AS4, which is a 32-bit operating system. The maximum text file size cannot exceed 2G. When $Oracle_ When the size of listener.log in home/network/log is 2G, you cannot continue to write to the log. The listening service will stop automatically. When you start or stop the listener, you will report an error:

1. Often manually clear the content of listener.log file, execute the following command in the database server


  
  
  
  
  
  
  
 
  
 
  
 
   
   
cd$ORACLE_HOME/network/log

  
  
  
  
  
 
  
 
  
 
   
   
cat</dev/null>listener.log

2. Let the command lsnrctl not log, and execute the following command on the database server


  
  
  
  
  
  
  
 
  
 
  
 
   
   
lsnrctl

  
  
  
  
  
 
  
 
  
 
   
   
setlog_statusoff

  
  
  
  
  
 
  
 
  
 
   
   
quit

You can also add logging in the listener. Ora file_ Listener = off to close the listening log

When I run lsnrctl status hopefully to start monitoring, an error is reported:

Starting /home/oracle/OraBase/OraHome1/bin/tnslsnr: please wait…
/home/oracle/OraBase/OraHome1/bin/tnslsnr: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Err or: 32: Broken pipe

Can’t find the file, it must be that the path is not set right. At this time, I suddenly thought of the command I used to run with root, switched to Oracle user to run again, this time succeeded! Although the database is OK, the application still can’t work. At this time, restart Weblogic

I find that no experience can scare people to death in this business

This article comes from “take small steps, never stop!” Blog, please keep this source http://badboy6515.blog.51cto.com/1320302/628669

Solve the problem of unable to connect sqlstate = 08004 when ODBC connects to Oracle database

Today, when using ODBC to connect to Oracle database, I reported such an error as “unable to connect sqlstate = 08004 Oracle ODBC ora-12154”. After checking on the Internet for a long time, I said that the powerdesign installation path has special characters, but I still reported this error after removing the spaces and brackets in my path. Simply remove powerdesign and connect directly with ODBC, but still report an error. At this time, I realize that it is not the problem of powerdesign installation path. Take a closer look at TNS service name, it is all garbled

The database version I use is Oracle 11g 64 bit server, and the 32-bit client is installed. The 64 bit ODBC TNS service name will be garbled. As shown in the picture above

solution:

add system environment variable

Variable name: TNS_ ADMIN

Variable value: C: app, administrator, product, 11.2.0, client_ 2\network\admin （ORACLE_ Network under home (admin)

Restart or log off after adding

　　　　

How to find Oracle EBS Weblogic Server Admin Port and URL

How to find Oracle EBS Weblogic Server Admin Port and URL

Weblogic admin port




Method 1

Open the Application tier context file

vi $CONTEXTFILE

Check the value of ‘WLS Admin Server Port’ from”s_wls_adminport” parameter


Method 2

Open the EBS domain config file

vi $EBS_DOMAIN_HOME/config/config.xml

Check the ‘listen-port’ value of the ‘AdminServer’


Weblogic console URL



http://<server name&>. <domain name&> : <weblogic Admin Port&>/console

Ex: http://oracle.test.com:7002/console

Login User: weblogic

Password: As per environment

Usage of within group in Oracle

The within group statement and group by are not related by half a cent. They are only auxiliary keywords of some single valued aggregate functions. Take the rank function as an example.

Rank is divided into aggregate function and analysis function in Oracle. This paper does not introduce analysis function, but refers to analysis function.
The rank syntax of aggregate function is: rank (expr1, expr2) within group (order by expr3, expr4 [desc/ASC] nulls [first/last])
it means to calculate the sort number of records with expr3 = expr1 and expr4 = expr2 in the result set with expr3 and expr4 as sort conditions, and return it as numeric type. The sorting is non compact, that is, if the first two records are the same, then the sorting number is 1, 1, 3… When using, it is not necessary to match with group by.

As long as you know the meaning of within group in the rank, you can’t learn to use it with other functions.
The functions that can use the within group keyword are rank and deny_ rank,PERCENT_ RANK,PERCENTILE_ CONT,PERCENTILE_ Disc, etc

You can’t specify target table for update….

This article mainly introduces the MySQL you can’t specify target table for update in from clause error resolution, need friends can refer to

The error of you can’t specify target table for update in from clause in MySQL means that you can’t select some values in the same table and then update the table (in the same statement). For example, the following SQL:

delete from tbl where id in 
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)&>1
        )
        group by tac
)

Just rewrite it as follows:

delete from tbl where id in 
(
    select a.id from 
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)&>1
        )
        group by tac
    ) a
)

That is to say, select the result through the middle table again, so as to avoid the error. Note that this problem only occurs in mysql, and will not occur in MSSQL and Oracle.