Physical disk space usage is full causing database hang

Situation description

One day the company small Zhang came to consult, said that the database query error; At first glance as if the database is bad fast, in order to troubleshoot more detailed error messages, decided to check the alarm log, found that the problem is that the database is full of physical disk space

Writing to the above tracefileis disabled forNow on ... Tue Jul in  -: -: +  theNon Critical Error ORA-48181Caught whileWriting to Tracefile "/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_ORA_2626.TRC"Error message:linux-x86_64 error:28 : No spaceleft on device Additional information:1Writing to the above tracefileis disabled forNow on ... Tue Jul in  -:xx:xx  theNon Critical Error ORA-48181Caught whileWriting to Tracefile "/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_J001_2703.TRC"Error message:linux-x86_64 error:28 : No spaceleft on device Additional information:1Writing to the above tracefileis disabled forNow on ... Tue Jul in  -: -: $  theNon Critical Error ORA-48113Caught whileWriting to Tracefile "/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_MMON_1642.TRC"Error message:writing to the above tracefileis disabled forNow on ... Tue Jul in  -: $: at  theNon Critical Error ORA-48181Caught whileWriting to Tracefile "/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_ORA_2405.TRC"Error message:linux-x86_64 error:28 : No spaceleft on device Additional information:1Writing to the above tracefileis disabled forNow on ...***********************************************************************Fatal NI Connect error12170. Tue Jul in  -: $: at  theNon Critical Error ORA-48181Caught whileWriting to Tracefile "/U01/APP/ORACLE/DIAG/RDBMS/ORCL/ORCL/TRACE/ORCL_ORA_2407.TRC"Error message:linux-x86_64 error:28 : No spaceleft on device Additional information:1Writing to the above tracefileis disabled forNow on ...***********************************************************************

Then start looking at the physical disk usage, the file under that directory takes up a lot of disk space (du-sh/home/), and eventually finds a large number of data files in the $oracle_home/dbs directory

Lypt-> cd/u01/app/oracle/product/11.2.0/db_1/dbs/Lypt-Lltotal18141444-RW-R-----1 Oracle Oinstall 524296192 15:50 d:dataxlcl_base_tbspc001. DBF-RW-R-----1 Oracle Oinstall 5368717312 13:05 d:dataxlcl_business_tbspc001. DBF-RW-R-----1 Oracle Oinstall 2147491840 13:05 d:dataxlcl_data_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:20 d:dataxlcl_fact2_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_fact3_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_fact_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 15:00 d:dataxlcl_gis_tbspc001. DBF-RW-R-----1 Oracle Oinstall 2147491840 13:05 d:dataxlcl_index_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_pub_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_regioninside_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 16:38 d:dataxlcl_regionuser_tbspc001. DBF-RW-R-----1 Oracle oinstall 524296192 13:05 d:dataxlcl_sta_tbspc001.dbf-rw-r----- 1 Oracle Oinstall 524296192 16:39 d:dataxlcl_sub1_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_sub2_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_sub3_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 16:39 d:dataxlcl_sub4_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_sub5_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_sub6_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_sub7_tbspc001. DBF-RW-R-----1 Oracle Oinstall 524296192 13:05 d:dataxlcl_sub8_tbspc001. Dbf-RW-RW----1Oracle Oinstall1544Jul in  -: $Hc_orcl.dat-rw-r--r--1Oracle Oinstall2851May the   theInit.ora-rw-r-----1Oracle Oinstall -Jul at  -: ALKORCL-rw-r-----1Oracle Oinstall1536Jul at  -: -ORAPWORCL-rw-r-----1Oracle Oinstall2560Jul to  the: -Spfileorcl.oralypt-

After seeing these files, I was a little confused; Create a table space under Linux, the data file name actually follow Windows down to write; After reading the code written by their developers, the original table space was created without specifying a specific absolute path, and when named, is actually using the path style under Windows. The next step is how to solve the database scraping problem first.

First, find a large, insignificant file, move to other systems first, then the database hangs the problem is resolved (as long as you make room, Oracle will automatically restore back)

Next, log in to Oracle and, depending on your business needs, properly adjust the location of some data table spaces, or simply delete some unnecessary table spaces. This depends on the specific situation;

Finally, the problem has been solved.