I wanted to do a small post on using the DOS command line tool ADRCI to quickly locate problems from the alert log and acting on them to fix the issue(s) when using Oracle on Windows. It should not be much different on Linux. This is a 100 level post.
The call: Timeouts
Another normal day was quickly broken up by an innocent email stating that connections were timing out from a certain application connecting to one of the Oracle databases.
Timeout bei Verbindungsanforderung,Timeout bei Verbindungsanforderung at OracleInternal.CP.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch) at OracleInternal.CP.OPM.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch) at OracleInternal.CP.OCD`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword) at Oracle.ManagedDataAccess.Client.OracleConnection.Open() at Common.DB.Data.DataConnection.Open(String connectString) =========== end of inner exception =========== at Common.DB.Data.DataConnection.Open(String connectString) at Common.DB.Data.DataConnection.Open(DatabaseDriver driver, String connectString, String alias) at Common.DB.Database.OpenAndInitialize(Action openCall) at MyApp.Data.MyAppDatabase.Open(String userid, String pwd, String host, Int32 port, String service) at MyApp.UI.MyAppApplicationLogic.OpenDatabase() at MyApp.UI.MyAppApplicationLogic.get_Db() at MyAppModuleWin.Program.Main(String args) Could you please help us here? Cheers, UserName
Basically, it was just timing out.
The real cause: Inquire Alert Log using ADRCI
I poked around on the server from SQL*Plus and commands just froze. They would neither return with results nor error out. It would just spin (when using Toad from a client PC) and just hang from SQL*Plus even on the server itself.
In DOS, I quickly fired up the CLI for ADRCI to look at the alert log for problems. It is part of the diagnosability infrastructure and among other things helps you look at alert log quickly without having to know file-locations. If you cannot remember all that is available via ADRCI, just type “help”. This shows the options and clearly, we want SHOW ALERT from the list.
Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\MyAdminID>adrci ADRCI: Release 18.104.22.168.0 - Production on Fri Jul 28 18:48:38 2017 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. ADR base = "D:\ORACLE" adrci> help HELP [topic] Available Topics: CREATE REPORT ECHO EXIT HELP HOST IPS PURGE RUN SET BASE SET BROWSER SET CONTROL SET ECHO SET EDITOR SET HOMES | HOME | HOMEPATH SET TERMOUT SHOW ALERT SHOW BASE SHOW CONTROL SHOW HM_RUN SHOW HOMES | HOME | HOMEPATH SHOW INCDIR SHOW INCIDENT SHOW LOG SHOW PROBLEM SHOW REPORT SHOW TRACEFILE SPOOL SELECT There are other commands intended to be used directly by Oracle, type "HELP EXTENDED" to see the list adrci> show alert Choose the home from which to view the alert log: 1: diag\clients\user_SomeID\host_954374472_80 2: diag\clients\user_SomeID\host_954374472_82 3: diag\clients\user_SomeOthID\host_954374472_82 4: diag\clients\user_YetAnoID\host_954374472_80 5: diag\clients\user_SYSTEM\host_954374472_80 6: diag\clients\user_SYSTEM\host_954374472_82 7: diag\clients\user_UNKNOWN\host_954374472_80 8: diag\clients\user_Admin2ID\host_954374472_82 9: diag\rdbms\OraInst03\OraInst03 10: diag\rdbms\OraInst03\OraInst04 11: diag\rdbms\OraInst04\OraInst04 12: diag\tnslsnr\OraHostName\listener Q: to quit Please select option: 11 Output the results to file: C:\Users\MyAdminID\AppData\Local\Temp\10\alert_9836_12 132_OraInst04_2.ado
From the list, I knew that I had to pick option 11 because that is the home for which I needed to view the alert log.
Out of space!
Reviewing the alert log contents showed me that the we were running out of space on D: drive.
Errors in file D:\ORACLE\diag\rdbms\OraInst04\OraInst04\trace\OraInst04_ora_9304.trc: ORA-19504: failed to create file "D:\ORACLE\FRA\OraInst04\ARCHIVELOG\2017_07_28\O1_MF_1_1541_%U_.ARC" ORA-27044: unable to write the header block of file OSD-04008: WriteFile() failure, unable to write to file O/S-Error: (OS 112) There is not enough space on the disk.
I quickly shutdown the two Oracle instances on the box (both used D: drive for data files) to avoid possible corruption. As I discussed what we could do with my co-DBA, we decided that the best option was to move some data files from D: drive to E: drive to free-up space as there was nothing else other than Data files to clear-up. Otherwise, you could have used the PURGE command for ADRCI above to clear alert, trace and dump files in an attempt to clear space!
The fix – Move files to another drive
Update 2018/11/19: In 12c, you can now do this operation online. Please check out the “ALTER DATABASE MOVE DATAFILE” command which moves the file without you having to copy. Check it out here.
If you have another drive on which you had space available, the easiest fix for the problem is to clear space on the drive with no space by moving data files over to the drive that has space!
Basically, I just followed the basic steps outlined in the Oracle community forum.
You may have to set your SID as below before connecting with SQL*Plus (in DOS)
Restating from the forum as is, the steps are below.
here is a procedure to move (rename) a datafile
1. Login to SQLPlus.
2. Connect as SYS DBA with CONNECT / AS SYSDBA command.
3. Shutdown the database instance with SHUTDOWN command.
4. Rename or/and move the datafiles at operating system level.
5. Start Oracle database in mount state with STARTUP MOUNT command.
6. Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘FullPathWithinSingleQuote’ TO ‘FullPathWithinSingleQuote’;
7. Open Oracle database instance completely with ALTER DATABASE OPEN command.
There is not really much more to the problem than that. I did the above and everyone was happy!