nearly full disk

Windows Services and Oracle Processes

I use an XE version of Oracle 11.2 on a Windows laptop as a sandbox database. It’s convenient for things like exploring new features, trying out development ideas in SQL and PL/SQL, and isolating schemas and data objects for research. Plus, it’s a free download and pain-free installation. But all of my admin experience with Oracle lies within a Unix/Linux context. Since the XE database pretty much runs itself for months and years, having to do some emergency DBA config work can come as a surprise and require research.

Start DB button results

windows services for oracle DB

trying to stop the listener

extended services tool

manual versus automatic

Runaway Trace Files and Countermeasures

sprawling oracle trace files

              DB + Software + Data: 4GB

                               Trace Files: 95GB

The quantity of files, including folders, which had been generated were more than 2.6 million! All while thinking the database was quietly off.

The entire tracing architecture changed with Oracle 11g. It’s been incorporated into a monitoring and issue-reporting utility called ADR (Automatic Diagnostic Repository) which generates many kinds of trace information for both system and user actions, including TNS diagnostics. The adrci tool is a command-line interface for inspecting these numerous trace incidents and also for packaging them up for sending to Oracle Support. Locations and database parameters related to tracing have also been revised. The long familiar BACKGROUND_DUMP_DEST, USER_DUMP_DEST and CORE_DUMP_DEST have been collapsed into the single 11g parameter DIAGNOSTIC_DEST. The default location for this parameter is the /diag directory, which is placed under $ORACLE_HOME. It is still possible (not clear for how much longer though) to disable ADR-style extended tracing and revert back to the old style via the DIAG_ADR_ENABLED parameter. Note: this is not a standard database parameter, but rather a SQL*Net setting. By default in 11g, ADR is running, so to unset it and revert to older tracing, you must explicitly place the following entry in your $ORACLE_HOME/network/admin/sqlnet.ora config file:

        DIAG_ADR_ENABLED = OFF

To see which kinds of tracing are currently being routed where, you can consult the view V$DIAG_INFO. Here’s an Oracle documentation overview about the new diagnostics subsystem.

Automating Trace Cleanup

If you’re thinking cleaning up this mess is simple and quick, it’s either because you speak Windows-Batch, or like me, you’ve been lulled into complacency by a couple of decades of exposure to Unix shell tools. Writing and testing some Windows batch commands to eliminate unwanted aged files and folders proved time-consuming. I know that people exist who are fluent in this stuff, but geez, (BEGINNING OF RELIGIOUS RANT) what an ugly mishmash of flags, hyphens, quotes, and idiosyncratic arbitrariness Windows batch syntax is (END OF RELIGIOUS RANT)! Once you do arrive at something which works, you are in for a lengthy wait while it executes. (This is likely made worse because only sparse and fragmented free disk space may remain.)

After it finishes, it will be necessary to run a CHKDSK and defrag the disk. Finally, you’ll want to create a scheduled batch executable which repeats this cleanup process at regular intervals, say every 2 or 4 weeks, so you can happily forget about it. So – here for posterity are the steps for problem resolution and the Windows batch command for cleanup, which uses the forfiles command:

1) Ensure the Windows Oracle XE database is shutdown
2) Use Task Manager to halt any leftover Oracle services
3) Force all Startup settings to ‘Manual’ for Oracle services
        (in case Windows bounces)
4) Locate /diag folder and check extent of space utilization
5) Execute BATCH code to delete unwanted .TRM, .TRC and folders
        (use desired aging variable; example is 15 days)
6) CHKDSK and defrag entire disk
7) Optional: Turn off ADR using SQLNET.ORA
8) Optional: Schedule regular BATCH job cleanup in Windows (Step #5)

~RS