Oracle On Windows – Simple Timeout To Crisis To Moving Data Files to Another Drive

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.


ADRCI: Release - 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:
        SET BASE
        SET ECHO
        SET EDITOR
        SHOW ALERT
        SHOW BASE
        SHOW HM_RUN
        SHOW LOG

 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

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)

SET oracle_sid=MyOraInstName

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!


One thought on “Oracle On Windows – Simple Timeout To Crisis To Moving Data Files to Another Drive

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s