Tag Archives: Oracle

Oracle: How to use errorstack to track the ora error of the client

Oracle uses errorstack to track the ora error of the client

preface

Recently, a business module reported an error ora-00942: table or view doors not exist. I want to help find out which SQL is causing it.

Generally, if the ora error of the client is simply caused by the client, such ora error will not be recorded in the database server.

For example, if an error is reported when the table or view does not exist, if the client deliberately checks a non-existent table and performs a large number of operations, considering concurrency, if the server records logs, a large number of logs will be generated.

Or when the client executes an SQL with syntax errors, such as ora-00936: missing expression, the same is true.

However, you can use the errorstack switch to turn on the monitoring of an ora error number and turn it off after obtaining enough information.

experiment

Take the ora-00942 error report as an example, use the sys user to turn on the monitoring switch with error number 942.

15:23:27 [email protected](1218)> alter system set events '942 trace name errorstack forever, level 3';

System altered.

Elapsed: 00:00:00.00

Warning log at this time:

Thu Sep 30 15:25:47 2021
OS Pid: 21259 executed alter system set events '942 trace name errorstack forever, level 3'

Then ordinary users log in and execute a query to query a non-existent table.

15:23:17 [email protected](432)> select * from no;
select * from no
              *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:04.16

Warning log at this time:

Thu Sep 30 15:27:47 2021
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_21173.trc:
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_21173.trc:
ORA-00942: table or view does not exist
Thu Sep 30 15:27:49 2021
Dumping diagnostic data in directory=[cdmp_20210930152749], requested by (instance=1, osid=21173), summary=[abnormal process termination].
Dumping diagnostic data in directory=[cdmp_20210930152752], requested by (instance=1, osid=21173), summary=[abnormal process termination].

Turn off the switch in time:

15:30:45 [email protected](1218)> alter system set events '942 trace name errorstack off';

System altered.

Elapsed: 00:00:00.00

To view the trace file:

Trace file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_21173.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      testdb
Release:        5.4.17-2011.6.2.el7uek.x86_64
Version:        #2 SMP Thu Sep 3 14:09:14 PDT 2020
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 129
Unix process pid: 21173, image: [email protected] (TNS V1-V3)


*** 2021-09-30 15:27:47.079
*** SESSION ID:(432.4017) 2021-09-30 15:27:47.079
*** CLIENT ID:() 2021-09-30 15:27:47.079
*** SERVICE NAME:(SYS$USERS) 2021-09-30 15:27:47.079
*** MODULE NAME:(SQL*Plus) 2021-09-30 15:27:47.079
*** ACTION NAME:() 2021-09-30 15:27:47.079

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=2611xga5f8w95) -----
select * from no

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (?means dubious value)
-------------------- -------- -------------------- ----------------------------

*** 2021-09-30 15:27:47.306
skdstdst()+41        call     kgdsdst()            000000000 ?000000000 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
ksedst1()+103        call     skdstdst()           000000000 ?000000000 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
ksedst()+39          call     ksedst1()            000000000 ?000000001 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
dbkedDefDump()+2746  call     ksedst()             000000000 ?000000001 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
ksedmp()+41          call     dbkedDefDump()       000000003 ?000000000 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
dbkdaKsdActDriver()  call     ksedmp()             000000003 ?000000000 ?
+1960                                              7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
dbgdaExecuteAction(  call     dbkdaKsdActDriver()  7F0DE7A1F730 ?7FFDE0D33D00 ?
)+1065                                             7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
...
Starting SQL statement dump
SQL Information
user_id=703 user_name=ZKM module=SQL*Plus action=
sql_id=2611xga5f8w95 plan_hash_value=0 problem_type=0
----- Current SQL Statement for this session (sql_id=2611xga5f8w95) -----
select * from no
sql_text_length=17
sql=select * from no
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true 
parallel_query_forced_dop           = 0
...

You can see that there is enough information.

Oracle executes SQL script file with command

When there are too many SQL commands (the SQL file is too large), the execution of PLSQL is slow and easy to timeout. At this time, you can directly execute the SQL script file with sqlplus command, as follows:

1. Sqlplus login

> sqlplus username/ [email protected]

2. Execute SQL file

If the SQL file is in the current directory, you can directly execute:

>@ demo.sql

If the SQL file is not in the current directory, you can use the absolute path:

>@ D:/demo.sql

Oracle trigger error: ORA-04098 [How to Solve]

When Oracle writes a trigger, an error is reported during execution, and the error prompt information is shown in the figure above. Similar trigger statements generally have syntax errors. Re audit the statement and execute it again

If you are using PL/SQL developer, you can check the triggers in the object bar under the current user to find the newly written trigger. If there is an error, there is a small red X in the icon in front of the corresponding trigger name

 

In Oracle, an error is reported: ora-00904

For Oracle database, when using PowerDesigner tool (or Navicat tool) to write database script, sometimes the column name is quoted in double quotation marks when creating a table

java an error will be reported when connecting ora-00904 : the identifier is invalid; If you remove the double quotation marks, you won’t report an error

For example:

Original table:

1 CREATE TABLE ”my_test“(
2   “id” NUMBER(10) NOT NULL, 
3   ”name“ VARCHAR2(20) NOT NULL, 
4   “password” VARCHAR2(10 CHAR) NOT NULL, 
5   PRIMARY KEY (ID)
6 )

After modification:

1 CREATE TABLE my_test(
2   id NUMBER(10,0) NOT NULL ENABLE, 
3   name VARCHAR2(20 CHAR) NOT NULL ENABLE, 
4   password VARCHAR2(10 CHAR) NOT NULL ENABLE, 
5   PRIMARY KEY (ID)
6 )

 

Oracle error: not a group by expression [How to Solve]

Error: not a group by expression

Instance: Select   sum(hwjz),rq from   JcChargeInfo where   1=1    group   by   rq order   by   jcchargecode desc

Reason:

1. When group by and order by are used together, order by should follow group by

2. The field of order by must be followed by group by

3. The fields selected in the select statement to be queried must appear in the group by clause

 

Oracle startup error ora-03113 solution

Environment: rhel6.4 + Oracle 11.2.0.4

Step summary:
1. Start the error ora-03113
2. Check the alert log to find the cause
3. Take reasonable measures according to the actual situation. Here, we first increase the size of the flash back area and start the library
4. Formulate the corresponding Archive log deletion strategy

1. Startup error ora-03113

$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Stauday Feb 28 13:56:44 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            2365590928 bytes
Database Buffers          822083584 bytes
Redo Buffers               16904192 bytes
Database mounted.
SQL> alter database open; 
alter database open
*
ERROR at line 1:
ORA-03113: End of file for communication channel
Process ID: 13501
Session ID: 853 Sequence number: 5


SQL>

2. Check the alert log to find the reason

Sat Feb 28 13:56:52 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 8
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: non-NUMA system
    cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
    Grp 0: 
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/app/oracle/product/11.2.0.4/dbhome_1
System name:    Linux
Node name:      JJFAB01
Release:        2.6.32-358.el6.x86_64
Version:        #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine:        x86_64
VM name:        Xen Version: 4.1 (PVM)
Using parameter settings in server-side spfile /opt/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJJFAB.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 2272
  memory_target            = 3G
  control_files            = "/opt/oradata/gxwj/control01.ctl"
  control_files            = "/opt/app/oracle/fast_recovery_area/gxwj/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/app/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=gxwjXDB)"
  audit_file_dest          = "/opt/app/oracle/admin/gxwj/adump"
  audit_trail              = "DB"
  db_name                  = "JJFAB"
  open_cursors             = 300
  diagnostic_dest          = "/opt/app/oracle"
Sat Feb 28 13:56:54 2015
PMON started with pid=2, OS id=13461 
Sat Feb 28 13:56:54 2015
PSP0 started with pid=3, OS id=13463 
Sat Feb 28 13:56:55 2015
VKTM started with pid=4, OS id=13465 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sat Feb 28 13:56:55 2015
GEN0 started with pid=5, OS id=13469 
Sat Feb 28 13:56:55 2015
DIAG started with pid=6, OS id=13471 
Sat Feb 28 13:56:55 2015
DBRM started with pid=7, OS id=13473 
Sat Feb 28 13:56:55 2015
DIA0 started with pid=8, OS id=13475 
Sat Feb 28 13:56:55 2015
MMAN started with pid=9, OS id=13477 
Sat Feb 28 13:56:55 2015
DBW0 started with pid=10, OS id=13479 
Sat Feb 28 13:56:55 2015
LGWR started with pid=11, OS id=13481 
Sat Feb 28 13:56:55 2015
CKPT started with pid=12, OS id=13483 
Sat Feb 28 13:56:55 2015
SMON started with pid=13, OS id=13485 
Sat Feb 28 13:56:55 2015
RECO started with pid=14, OS id=13487 
Sat Feb 28 13:56:55 2015
MMON started with pid=15, OS id=13489 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Feb 28 13:56:55 2015
MMNL started with pid=16, OS id=13491 
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/app/oracle
Sat Feb 28 13:56:56 2015
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 3498004520
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Sat Feb 28 13:57:48 2015
alter database open
Sat Feb 28 13:57:48 2015
LGWR: STARTING ARCH PROCESSES
Sat Feb 28 13:57:48 2015
ARC0 started with pid=20, OS id=13553 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sat Feb 28 13:57:49 2015
ARC1 started with pid=21, OS id=13555 
Sat Feb 28 13:57:49 2015
ARC2 started with pid=22, OS id=13557 
Errors in file /opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_ora_13501.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4322230272 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/opt/app/oracle/fast_recovery_area/JJFAB/archivelog/2015_02_28/o1_mf_1_130_%u_.arc'
Errors in file /opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_ora_13501.trc:
ORA-16038: Log 1 sequence# 130 could not be archived
ORA-19809: Exceeded recovery file count limit
ORA-00312: Online log 1 thread 1: '/opt/oradata/gxwj/redo01.log'
Sat Feb 28 13:57:49 2015
ARC3 started with pid=23, OS id=13559 
USER (ospid: 13501): terminating the instance due to error 16038
Sat Feb 28 13:57:50 2015
System state dump requested by (instance=1, osid=13501), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_diag_13471_20150228135750.trc
Dumping diagnostic data in directory=[cdmp_20150228135750], requested by (instance=1, osid=13501), summary=[abnormal instance termination].
Instance terminated by USER, pid = 13501

 

It is found that the reasons and suggestions have been clearly given in the alarm log

3. Take reasonable measures according to the actual situation. Here, we first increase the size of the flashback area and start the library

SQL> startup mount
ORA-24324: Service handle not initialized
ORA-01041: Internal error, hostdef extension does not exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期六 2月 28 14:05:59 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            2365590928 bytes
Database Buffers          822083584 bytes
Redo Buffers               16904192 bytes
Database mounted.
SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4122M
SQL> !
$ df -h /opt/
File System Capacity Used Available Used %% Mount Point
/dev/mapper/vg_extend-lv_opt
                      493G  423G   45G  91% /opt
$ exit
exit

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

4. Formulate corresponding archive log deletion strategy

 

SQL> set linesize 120
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      41.54                         0             133
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.
You can rman directly delete archives that are no longer needed, for example, delete archives that are 1 day old
RMAN> delete noprompt archivelog until time 'sysdate-1';
The output of the deleted archive is omitted here...
RMAN> exit

Recovery Manager complete.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                        .35                         0               1
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

Of course, you can also set crontab to delete archives 1 day ago every day:

$ crontab -l
00 04 * * * /opt/shell/del_arch.sh
$ more /opt/shell/del_arch.sh
#!/bin/bash
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/11.2.0.4/dbhome_1 
export ORACLE_SID=JJFAB
export NLS_LANG="simplified chinese_china.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY -MM-DD HH24:Mi:SS"
export PATH=.:/opt/app/oracle/product/11.2.0.4/dbhome_1:/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

rman target/log=/opt/backup/del_arch.log <<EOF
list archivelog all;
delete noprompt archivelog until time 'sysdate-1';
show all;
list archivelog all;
EOF

 

Error handling of PLSQL initial login to Oracle

Oracle client and PLSQL are installed on Server1, but after logging in, database and connect as are not displayed

After manually entering the correct user name and database, an error is reported as follows

Therefore, cancel the login and directly enter the PLSQL interface tools connection to check Oracle home and OCI library. It is found that the path of OCI library is wrong

Client2 is the wrong address, and client32 is on the machine

So it was changed to client32

After applying the changes, close the PLSQL interface and restart it. You will see that PLSQL automatically reads all TNS information

Log in with user password OK

 

Navicat even Oracle reports an error [no matching authentication protocol]

Error Description:

             An error is reported when navicatk connects to Oracle database (all connection settings are normal)

             ORA-28040:No matching authentication protocol

Error reason:

             Missing OCI corresponding to Oracle or missing OCI corresponding to Oracle version

             OCI is a file in the instant client, which is a compact version of ORALCE

Problem solving:

       1. Download the version of instance client according to different Oracle versions

              Oracle 9i or later requires instance client 11; Oracle, 8i need instance client 10

              32-bit download address: http://www.oracle.com/technetwork/topics/winsoft-085727.html

              Note: Navicat only supports 32-bit clients, regardless of whether Navicat itself is 32-bit or 64 bit

                    In addition, to download the basic version, the non basic Lite version does not support Chinese

        2. Place the downloaded instance client in the corresponding file directory in Navicat

            ( (available through Navicat tools – Options – environment – OCI environment)

        3. Select the OCI file in the instant client just added in Navicat tools – Options – environment – OCI environment

File connection (instantclient)_11_2):

            https://share.weiyun.com/5fwQp5t Password: drgtk2

Oracle error: not a single group grouping function

Error: not a single group grouping function

Instance: Select   sum(HWJZ)  , rq from    JcChargeInfo

 

Reason:

1. If a grouping function is used in the program, there are two situations:

Group by exists in the program, and the grouping conditions are specified, so the grouping conditions can be queried together  

Replace with:

select   sum(HWJZ)  , rq from    JcChargeInfo   group   by   rq

If you do not use grouped activities, you can only use grouped functions alone

Replace with:

select   sum(HWJZ)    from    JcChargeInfo

2,   When using grouping functions, fields other than grouping conditions cannot appear

Summary: the fields selected in the select statement to be queried must appear in the group by clause

Oracle Error: ORA-01843: not a valid month [Solved]

Client: select * from sys.nls_session_parameters;
server: select * from sys.nls_database_parameters;

Error in executing script: ora-01843: not a valid month
confirmation problem: client NLS_date_Language is American
solution: modify NLS_date_The language parameter is Chinese. Data such as’ November may – 18 10.56.50.139000 am ‘is successfully imported
— this command is only useful for the current session
alter session set NLS_date_language=’SIMPLIFIED CHINESE’;