Tag Archives: Oracle

IO exception: socket read timed out always appears in Oracle program

Why can’t you stop buying 618?From the technical dimension to explore>>>

2016-11-05 21:32:32,068 INFO [main] NumIdle: 0
2016-11-05 21:32:32,273 ERROR [main] java.sql.SQLException:null Io exception: Socket read timed out
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:257)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:985)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1074)
at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1156)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1315)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.XXXXXX.fetchWorkOrders(XXXXX.java:366)
at com.XXXXXXXX.main(XXXXXX.java:250)

2016-11-05 21:32:32,273 ERROR [main] latestRstBean is null
2016-11-05 21:32:32,274 ERROR [main] java.sql.SQLException:null Already closed.
at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
at org.apache.commons.dbcp.PoolingDriver$PoolGuardConnectionWrapper.close(PoolingDriver.java:269)
at com.XXXXXXX.fetchWorkOrders(XXXXXXX.java:419)
at com.XXXXXXX.main(XXXXXXXXX.java:250)

Now I’m looking for the reasons. This kind of problem doesn’t happen every time

[Solved] Oracle 12505 Error: Listener refused the connection with the following error: ORA-12505,

After installing Oracle, you can use the command line to start and connect to the database, but using developer to connect with error 12505

The reason is that Sid is wrong. It should be orcl

Oracle can’t be used after login. Connected to an idle instance is displayed

1.Login Status.

[oracle@localhost ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 1 09:15:35 2019

Connected to an idle instance.

2.If you connect to an idle instance, it may be that the database listener is not started or the database is not started, start the listener first and then the database.

Check the listener status first

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2019 09:15:55

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused

3.If the listener is not started, it is started first

[oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 – Production on 01-JUL-2019 09:16:01

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.3.0 – Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Start Date 01-JUL-2019 09:16:02
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully.

4.Login to the database, if you can’t login here, check if ORACLE_SID is configured correctly in .bash_profile first

[oracle@localhost ~]$ sqlplus/as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 1 09:16:05 2019

Connected to an idle instance.

5.After login, there is still an idle instance, maybe the database is not started, start the database

SQL> startup
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2232272 bytes
Variable Size 494927920 bytes
Database Buffers 264241152 bytes
Redo Buffers 2719744 bytes
Database mounted.
Database opened.

6.Start successfully, query operation

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

7.Done!

How to Solve Oracle ORA-09925 error: Unable to create audit trail file

On the first day after the Spring Festival in 2018, I encountered a problem that the audit log could not be written in. The specific solution is as follows

I. error log

Database error log content:

Fri Feb 23 11:16:30 2018
OS Audit file could not be created; failing after 6 retries
Fri Feb 23 11:16:36 2018
OS Audit file could not be created; failing after 6 retries
Fri Feb 23 11:16:43 2018
OS Audit file could not be created; failing after 6 retries
Fri Feb 23 11:16:57 2018
OS Audit file could not be created; failing after 6 retries
Fri Feb 23 11:17:25 2018
OS Audit file could not be created; failing after 6 retries
OS Audit file could not be created; failing after 6 retries
Fri Feb 23 11:17:33 2018
OS Audit file could not be created; failing after 6 retries
Fri Feb 23 11:17:35 2018
OS Audit file could not be created; failing after 6 retries

Program error log content:

ORA-09925: Unable to create audit trail file

Linux-x86_ 64 Error: 28: No space left on device

Second, the causes of the problem

The common reasons are as follows:

1. The audit log cannot be written due to full disk space

2. Unable to write disk space due to insufficient disk directory permissions

3. Database table space full, unable to write data file

4. Too many disk audit directory files result in write failure

5. The disk is damaged, there are bad tracks, etc

3. Solutions

1. First check whether the disk space is full

2. View database audit types

3. View the directory space of audit log

4. View the number of audit log directory files

5. Check the disk for damage

6. In order to resume business as soon as possible, we should clean up adump audit directory urgently

Because of the large amount of log content, when it is not possible to clean the log by time (usually through LS *. Aud | xargs – N 10 RM – RF), you can clean it by cleaning the directory

7. Check the content and frequency of audit log to find out specific problems

After investigation, it is found that 18 audit logs are generated every minute, and the connection process numbers are different (it means short connection, continuous disconnection and reconnection). Then according to the log content, find the specific connection user and program IP, and contact the relevant programs for communication and processing

Oracle Script: How to Solve ‘unusable state index(ORA-01502)’

When a configuration tool is used, the following errors often occur ora-01502: index ‘xxx. Xxxxx’ or partition of such index is in an unusable state. The solution is to log in as DBA and run the following script

-- Please login with DBA privileges and execute
-- Repair all the indexes in UNUSABLE state, rebuild them
declare
  -- cursor to all UNUSABLE state indexes
  cursor c is 
    select index_name, owner 
    from dba_indexes 
    where status='UNUSABLE';
  
  owner dba_indexes.owner%type;
  index_name dba_indexes.index_name%type;
begin
  open c;
  loop 
       fetch c into index_name, owner; 
       exit when c%notfound;
       
       execute immediate 'alter index ' || owner || '.' || index_name || ' rebuild';
  end loop;
  close c;
end;

Thinking 1: why does normal operation cause an index to be unusable

Thinking 2: how to solve this problem without logging into DBA（ I tried it from user_ In the indexes table, you can get all the indexes of the current user, but you can’t edit them.)

How to Solve Ora-01109 Error: database not open

#copy ora files
cd /usr/oracle/app/oracle/admin/orcl/pfile
ls
cp init.ora.313202010110 /usr/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initORCL.ora

#Modify memory size
mount -o remount,size=13G /dev/shm
vim /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=13G 0 0

#Delete system processes
cd /usr/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
ls sgadef*
ls lk*
rm lk*
ipcs -s
ipcrm -s 65537
ipcrm -s ……

#Launch process
su – oracle
sqlplus “/as sysdba”

//shutdown immediate
startup

How to solve Oracle ora-00937 Error: not a single group grouping function?

Previously, I encountered this problem when writing Oracle SQL statements. Here is a record

Problem Description: ora-00937: not a single group grouping function

Cause of the problem: in the select statement, you are querying the value of a column, in which there is an aggregate function

Originally, I wrote SQL like this

--Find the receipt delivery point, delivery delivery point, and number of delivery packages for a delivery order--
select R_DELIVEPOINT_ID,S_DELIVEPOINT_ID,SUM(PACK_NUM) PACK_NUM from TMS_DELIVERY;

The aggregate function sum () is used to solve this problem

If you want to solve this problem, the SQL syntax can be changed as follows:

1) Disaggregate function

--Find the receipt delivery point, delivery delivery point, and number of delivery packages for a delivery order--
select R_DELIVEPOINT_ID,S_DELIVEPOINT_ID,PACK_NUM from TMS_DELIVERY;

2) Support aggregate function (add group by)

--Find the receipt delivery point, delivery delivery point, and number of delivery packages for a delivery order--
select R_DELIVEPOINT_ID,S_DELIVEPOINT_ID,SUM(PACK_NUM) PACK_NUM  from TMS_DELIVERY
group by R_DELIVEPOINT_ID,S_DELIVEPOINT_ID;

Solution to the error of ora-06550 pls-00103 when Django connects Oracle to run PLSQL statement

Django connects Oracle and runs PLSQL statements

The code is as follows:

def exec_db():
    sql = """
    DECLARE
       v_money BINARY_INTEGER := 10;
    BEGIN
        dbms_output.put_line('Hello World');
       UPDATE TEST1 SET USER_SALERY=USER_SALERY+v_money;
       COMMIT;
    END; """
    # sql ='DECLARE v_money BINARY_INTEGER := 10; BEGIN UPDATE TEST1 SET USER_SALERY=USER_SALERY+v_money; COMMIT; END;'
    db_conn = connections['accounting']
    cursor = None
    try:
        cursor = db_conn.cursor()
        cursor.execute(sql)
        db_conn.commit()
    except:
        logger.error(traceback.format_exc())
    finally:
        if cursor:
            cursor.close()
        db_conn.close()

This SQL has no problem in the database connection tool, but the following error is reported in the code:

django.db.utils.DatabaseError: ORA-06550: line 8, column 7:

PLS-00103: Encountered the symbol “end-of-file” when expecting one of the following: ;

The symbol “;” was substituted for “end-of-file” to continue.

There are problems in writing SQL as multiple lines and single line.

Finally found the last; After that, add a space as the end. It’s going to work.

There’s no problem with one or more lines.

It’s very weird. Record it.

How to Solve ORACLE Error: ORA-27102: out of memory

SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: 2097152

[oracle@kingdee-test ~]$ cat /etc/redhat-release
CentOS release 6.9 (Final)
[oracle@kingdee-test ~]$ uname -a
Linux kingdee-test 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Memory usage.

$ free -m
total used free shared buffers cached
Mem: 15866 337 15529 0 15 130
-/+ buffers/cache: 191 15675
Swap: 7999 0 7999

Solution:

vi/etc/sysctl.conf max kernel.shmall kernel.shmmax

kernel.shmall = 5368709120
kernel.shmmax = 12884901888

# sysctl -p

[Solved] Insufficient Oracle table space: ora-01653: unable to extend table

Background:

It is a common fault to report an error due to insufficient Oracle table space, especially for the system without regular inspection of the remaining table space

The error code is as follows:

Insufficient Oracle table space error code: ora-01653: unable to extend table

Solution:

1. View the utilization rate of table space:

 1 set linesize 220;
 2 set pagesize 500;
 3 col tbsn for a40;
 4 select total.tablespace_name TBSN,
 5         round(total.MB,3) as total_space,
 6         case when free.MB is null then 0
 7                                else round(free.MB,3)
 8          end as free_space, 
 9        case when free.MB is null then 0
10          else round(free.MB,3)/round(total.MB,3)*100  end as free_rate
11 from ( select tablespace_name, sum(bytes/1024/1024) MB from dba_data_files group by tablespace_name )  total,
12         ( select tablespace_name, sum(bytes/1024/1024) MB from dba_free_space group by tablespace_name )  free
13 where total.tablespace_name=free.tablespace_name(+)
14 order by  free_rate desc;
15 
16

The above code can query which table spaces in the database, the total space size, the remaining space size and the usage percentage, so as to make the table space usage clear at a glance

2. Check that the disk space table is too much, and put the table space in the place with more remaining space (Linux operating system)
DF – H

3. View the table space data file:
be sure to confirm the path and keep it consistent with the original file path

1 set wrap on;
2 set lines 170
3 col file_name for a80
4 select file_id,file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name=upper('&tablespace_name');

4. If it is a RAC environment, you should also check the remaining ASM disk space

1 select group_number,name,total_mb,free_mb from v$asm_diskgroup;

5. Add table space (manual condition data file)

1 alter tablespace tablespace_name add datafile '/xxxx/xxxx/xxxxx_number.dbf' size 30g autoextend off;

6. I wrote a capacity expansion SQL for automatically editing and adding data files, which can generate capacity expansion scripts in batches (no 100% success rate is guaranteed, basically no problem)

 1 set linesize 150 pagesize 900
 2 SELECT 'alter tablespace ' || TABLESPACE_NAME || ' add datafile ''' ||
 3        substr(file_name, 1, regexp_instr(file_name, '[[:digit:]]+\.') - 1) ||
 4        TO_CHAR(substr(file_name,
 5                       regexp_instr(file_name, '[[:digit:]]+\.'),
 6                       instr(file_name, '.') -
 7                       regexp_instr(file_name, '[[:digit:]]+\.')) + B.RN) ||
 8        '.dbf'' size ' || bytes/1024/1024 || 'm autoextend off;'
 9   FROM DBA_DATA_FILES,
10        (select rownum rn
11           from dba_objects
12          WHERE ROWNUM <= &number_of_datafile) B       
13  WHERE FILE_ID = (SELECT DISTINCT LAST_VALUE(FILE_ID) 
14  OVER(ORDER BY to_number(substr(file_name, regexp_instr(file_name, '[[:digit:]]+\.'), instr(file_name, '.') - regexp_instr(file_name, '[[:digit:]]+\.'))) ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
15                     FROM DBA_DATA_FILES
16                    WHERE tablespace_name = '&tablespace_name');

DebugAH

How to Solve Your Programmer Error