Frits Hoogland Weblog

A recent thread on the oracle-l maillist (http://www.freelists.org/list/oracle-l) called “REDO LOG Concepts” caught my attention. Someone asked about the transaction state of the (oracle) database when the online redologfiles have been deleted prior to a commit.

Most people know that on linux and unixes a file keeps on living (=available) in the context of a process as long as the file is kept open by the process, despite deletion by another process. This file is gone for all processes that try to access it after the deletion, but the deleted files will only get really disposed once all processes that have the file opened close the the connection (file descriptor) to it.

I guess everybody who is doing/has done linux/unix administration has encountered the situation at least once where a filesystem gets filled with some kind of logfile or tracefile which grows rapidly and keeps on growing at a very fast pace, and deleting it does not release any space (because the process which is filling the file is not stopped, so the file is held open, so the file still exists, despite deletion)

Some suggestions where made for recovery and protecting against it (mirroring online redologfiles). I guess most people know recovery fundamentals, so I will not dive further into that matter.

A fellow oracle-l member (stefan knecht) however suggested:

I’ve run into this situation before as well. It happens, people accidentally delete files.

What would be interesting to research, is if you still can somehow recover those files. As they’re still there, and locked by the process accessing them (in your case LGWR), there might be a way to “steal” the filehandle from that process and just write the file to another location before it’s being closed. Though you would have to intercept the syscall closing the file.

That made me think: linux has the proc filesystem, which gives system information, but also process information. This process information is quite extensive. One of the sections (directory actually) is a list of all the file descriptors for a process. These file descriptors are shown as symlinks:


[oracle@centos50-oracle10203 bin]$ ./sqlplus '/as sysdba'

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 130024296 bytes
Database Buffers 33554432 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@centos50-oracle10203 bin]$ ps -ef | grep pmon
oracle 8634 1 0 16:24 ? 00:00:00 ora_pmon_frits
oracle 8680 2642 0 16:24 pts/1 00:00:00 grep pmon
[oracle@centos50-oracle10203 bin]$ cd /proc/8634/fd
[oracle@centos50-oracle10203 fd]$ ls -ls
total 0
0 lr-x------ 1 oracle oracle 64 May 17 16:24 0 -> /dev/null
0 lr-x------ 1 oracle oracle 64 May 17 16:24 1 -> /dev/null
0 lrwx------ 1 oracle oracle 64 May 17 16:24 10 -> /oracle/db/10.2.0.3/dbs/hc_frits.dat
0 lrwx------ 1 oracle oracle 64 May 17 16:24 11 -> /oracle/db/10.2.0.3/admin/frits/adump/ora_8632.aud
0 lr-x------ 1 oracle oracle 64 May 17 16:24 12 -> /dev/zero
0 lr-x------ 1 oracle oracle 64 May 17 16:24 13 -> /dev/zero
0 lr-x------ 1 oracle oracle 64 May 17 16:24 14 -> /oracle/db/10.2.0.3/rdbms/mesg/oraus.msb
0 lrwx------ 1 oracle oracle 64 May 17 16:24 15 -> /oracle/db/10.2.0.3/dbs/hc_frits.dat
0 lrwx------ 1 oracle oracle 64 May 17 16:24 16 -> socket:[33865]
0 lrwx------ 1 oracle oracle 64 May 17 16:24 17 -> /oracle/db/10.2.0.3/dbs/lkFRITS
0 lr-x------ 1 oracle oracle 64 May 17 16:24 18 -> /oracle/db/10.2.0.3/rdbms/mesg/oraus.msb
0 lr-x------ 1 oracle oracle 64 May 17 16:24 2 -> /dev/null
0 lr-x------ 1 oracle oracle 64 May 17 16:24 3 -> /dev/null
0 lr-x------ 1 oracle oracle 64 May 17 16:24 4 -> /dev/null
0 l-wx------ 1 oracle oracle 64 May 17 16:24 5 -> /oracle/db/10.2.0.3/admin/frits/udump/frits_ora_8632.trc
0 l-wx------ 1 oracle oracle 64 May 17 16:24 6 -> /oracle/db/10.2.0.3/admin/frits/bdump/alert_frits.log
0 lrwx------ 1 oracle oracle 64 May 17 16:24 7 -> /oracle/db/10.2.0.3/dbs/lkinstfrits (deleted)
0 l-wx------ 1 oracle oracle 64 May 17 16:24 8 -> /oracle/db/10.2.0.3/admin/frits/bdump/alert_frits.log
0 l-wx------ 1 oracle oracle 64 May 17 16:24 9 -> pipe:[11466]

As you can see, these are the open files of the pmon process of an oracle 10.2.0.3 database just after startup.
(if you want to use ltrace, strace or gdb on a process, this is the place to find the mapping of file descriptor to a file, socket or pipe!)

Can I use a file descriptor to get data out of the file? Probably, because it’s a symlink which is perfectly normal to use. Well then, back to the suggestion made:

“As they’re still there, and locked by the process accessing them (in your case LGWR), there might be a way to “steal” the filehandle from that process and just write the file to another location before it’s being closed.”

A quick test on a test system (centos 4.4, oracle 10.2.0.3):


SQL> select * from v$logfile;

SQL>


Let’s see which log group is the current one:


SQL> select * from v$log;

SQL>


Log group 3 is the current one. Well, lets delete one of the files of group 1 (I have two members in every log group, lets pretend we have only one member):


[oracle@filetest bin]$ rm /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log


Now look at the file descriptors of the logwriter:


...
1 lrwx------ 1 root root 64 May 17 17:07 21 -> /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log (deleted)
...


And lets make a copy of the file using the file descriptor:


[root@filetest ~]# cat /proc/4703/fd/21 > o1_mf_1_31l7knbs_.log
[root@filetest ~]# ls -ls o1_mf_1_31l7knbs_.log
51260 -rw-r--r-- 1 root root 52429312 May 17 17:11 o1_mf_1_31l7knbs_.log


It looks like we’ve made a copy of a deleted file, using the file descriptor in the proc filesystem. Now let’s shutdown the database, and start it up. This will go wrong, because a member of the online redolog group 1 can not be identified:


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

SQL>


Excuse me? Shouldn’t we get an error message saying an online logfile could not be identified? Let’s investigate further:


SQL> select * from v$logfile;

SQL>


Well, apparently not….I recall situations where a lost online redologfile would cause the startup to fail, and needing to copy an existing one in the group to the missing one. I guess this is altered in some version of the database. Anybody know which version?

Well, it makes sense. The status of the file is set to ‘INVALID’ and the surviving file(s) are used. A message is printed in the alert.log:


Thu May 17 17:43:13 2007
Errors in file /oracle/db/10.2.0.3/admin/frits/bdump/frits_lgwr_13960.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu May 17 17:43:13 2007
Errors in file /oracle/db/10.2.0.3/admin/frits/bdump/frits_lgwr_13960.trc:
ORA-00321: log 1 of thread 1, cannot update log file header
ORA-00312: online log 1 thread 1: '/oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log'


Now, let’s replace the missing one with our copy from the file descriptor:


[root@filetest ~]# cat o1_mf_1_31l7knbs_.log > /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log
[root@filetest ~]# chown oracle:oinstall /oracle/frits/data/FRITS/onlinelog/o1_mf_1_31l7knbs_.log


Now switch from logfile until we reach the log group in which the invalid file resides (log group 1)…


SQL> select * from v$logfile;

SQL>


Look! The file is valid again!

Once a logfile is invalid for whatever reason, oracle continues to work as long as there are valid members in the group. During a logswitch, the files in the log group where oracle switches to are identified, even if they are invalid, and reset to a valid state if they are usable again.

Also, it is possible to make a copy of an already deleted file (as root on centos 4.4 (centos 4.4 is a free version of RHEL4) and as the owner of the file or root on centos 5 (RHEL5)) using the filedescriptor in proc.

In the 10.2.0.1 version of the oracle database a new heap checking mechanism, together with a new messaging system is introduced. This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory.

In certain situations it can be handy to know if large allocations are being done in the sga heap (shared pool), but only to troubleshoot memory allocation problems (which eventually will appear as the infamous ORA-4031). For normal day to day work, I doubt the effectivity of these messages.

The default limit is set at 2048K. It appears normal usage can result in messages from the memory manager. During my last installation (with all options set default, all options installed), this resulted in 125 messages from the memory manager.

Oracle gives a solution on metalink in note 330239.1:
Set _kgl_large_heap_warning_threshold to a “reasonable high” value. This parameter is set in bytes, and oracle recommends the value 8388608 (8192K).

Oracle recently shipped the production version of Oracle XE, the free version of Oracle’s 10g release 2 database. This is an investigation from a DBA’s point of view. Is it comparable to a ‘normal’ 10g database, or is it something completely different?

Oracle Technet Link for more information and free download: Oracle XE Home

For linux, oracle ships the default gzipped cpio for the normal database, but a rpm (in the beta version), and a rpm and a deb (!!) file for the XE production version. I use ubuntu on my testing machine, so I am very pleased I can use a deb file (because ubuntu uses the debian package manager), and do not have to use ‘alien’ to convert the rpm to deb.

Each package manager checks the environment to see if it meets the requirements. the oracle XE package checks the amount of swapspace quite drastic. I have 512M, and a swapspace of 1G, but it needed 1008 M. I have gotten around this by adding some filesystem swap:


root@bedrock:~# dd if=/dev/zero of=/extraswap bs=1k count=1024000
root@bedrock:~# mkswap /extraswap
root@bedrock:~# swapon /extraswap
root@bedrock:~# swapon -s
Filename Type Size Used Priority
/dev/mapper/vg00-swap partition 1048568 138096 -1
/extraswap file 1023992 0 -4


Well, with this in mind, the install should work:


root@bedrock:~# dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb
(Reading database ... 70694 files and directories currently installed.)
Unpacking oracle-xe (from oracle-xe_10.2.0.1-1.0_i386.deb) ...
Setting up oracle-xe (10.2.0.1-1.0) ...
Executing Post-install steps...

root@bedrock:~#


This is easy! The software is installed, we only need to configure the database now, in order to use it!


root@bedrock:~# /etc/init.d/oracle-xe configure

Starting Oracle Net Listener…Done
Configuring Database…Done
Starting Oracle Database 10g Express Edition Instance…Done
Installation Completed Successfully.
To access the Database Home Page go to “http://127.0.0.1:8080/apex”


My first question is: what do we have installed now?

Well, it appears we have a running database, together with listener. Just that! The application (formerly known as ‘HTMLDB’, now it is called ‘application express’) runs entirely in the database:


oracle@bedrock:~$ ps -ef | grep oracle
oracle 20226 20225 0 13:13 pts/6 00:00:00 -su
oracle 22384 1 0 14:51 ? 00:00:00 /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr LISTENER -inherit
oracle 22515 1 0 14:53 ? 00:00:00 xe_pmon_XE
oracle 22517 1 0 14:53 ? 00:00:00 xe_psp0_XE
oracle 22519 1 0 14:53 ? 00:00:00 xe_mman_XE
oracle 22521 1 0 14:53 ? 00:00:00 xe_dbw0_XE
oracle 22523 1 0 14:53 ? 00:00:00 xe_lgwr_XE
oracle 22525 1 0 14:53 ? 00:00:00 xe_ckpt_XE
oracle 22527 1 0 14:53 ? 00:00:00 xe_smon_XE
oracle 22529 1 0 14:53 ? 00:00:00 xe_reco_XE
oracle 22531 1 0 14:53 ? 00:00:00 xe_cjq0_XE
oracle 22533 1 0 14:53 ? 00:00:00 xe_mmon_XE
oracle 22535 1 0 14:53 ? 00:00:00 xe_mmnl_XE
oracle 22537 1 0 14:53 ? 00:00:00 xe_d000_XE
oracle 22539 1 3 14:53 ? 00:00:05 xe_s000_XE
oracle 22541 1 1 14:53 ? 00:00:02 xe_s001_XE
oracle 22543 1 0 14:53 ? 00:00:01 xe_s002_XE
oracle 22545 1 0 14:53 ? 00:00:00 xe_s003_XE
oracle 22550 1 0 14:53 ? 00:00:00 xe_qmnc_XE
oracle 22579 1 0 14:54 ? 00:00:00 xe_q000_XE
oracle 22581 1 0 14:54 ? 00:00:00 xe_q001_XE
oracle 22622 20226 0 14:56 pts/6 00:00:00 ps -ef
oracle 22623 20226 0 14:56 pts/6 00:00:00 grep oracle


Where is the software installed?

If we examine the startup scripts, or list the contents of the oracle XE package, we see the software gets installed in /usr/lib/oracle, just as many other debian packages. That’s the debian default. I can live with that.

What’s the oracle home?

The ORACLE_HOME is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server. That’s hidden quite far away from the root.

And how about the database of oracle XE?

The oracle XE database is called ‘XE’. That’s the db_name and the instance_name. The database is startup with a spfile, which is located in $ORACLE_HOME/dbs. The controlfile is located in the directory with the datafiles, which is: /usr/lib/oracle/xe/oradata/XE. The online redologfiles are located in: /usr/lib/oracle/xe/app/flash_recovery_area/XE/onlinelog and are called o1_mf_20onfoz2_.log and o1_mf_20onfnc0_.log.

Besides the fact that only one database can be run with XE (starting up a second instance results in: ORA-44410: XE edition single instance violation error), I still would love to see all files related to an oracle instance be kept in a single place. This eases administration, makes transportability easy, and makes backup and restore very much easier. Now the files are quite scattered among the oracle tree.

It is a bad idea to have the spfile (or init.ora file) (which is critical for starting up a database, it *must* be present) in the oracle software tree. I strongly advise to have a symlink in the dbs directory, instead of the real file.

The online redologfiles are reasonable sized for small installations without any batch ever. For normal database use (yes, I know, this is express, and NOT a normal database) I would start at a size of 200M. Also the number of redolog groups is quite small (2). Online redologfiles are *critical* to database transaction performance, and therefore need to be easily accessible for a DBA and need to have a reasonable names. The current names (o1_mf_20onfoz2_.log and o1_mf_20onfnc0_.log) are weird, and the place is odd to (somewhere in the flash_recovery_area). I would suggest placing the online redologfiles somewhere close to the datafiles, but on a separate filesystem. The online redologfile names are randomly choosen during the install.

How is the default database sized?


oracle@bedrock:~$ export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
oracle@bedrock:~$ export PATH=$PATH:$ORACLE_HOME/bin
oracle@bedrock:~$ export ORACLE_SID=XE
oracle@bedrock:~$ sqlplus / as sysdba

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 41216K


Well, it is small, but the memory usage can be altered to 1G memory, according to the documentation.

Can we break it?

In essence, XE is a normal 10gr2 database, just limited. So all means to break it is also applicable to non-XE oracle databases! I very easily could get an unstartable database. Look at the following activity:


oracle@bedrock:~$ sqlplus / as sysdba

SQL> shut
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00439: feature not enabled: Real Application Clusters


This completely deadlocks the normal way to alter the spfile. Even a startup nomount can not be issued, because the database can not get to the nomount stage. The workaround is quite simple, though:


oracle@bedrock:~$ cd $ORACLE_HOME/dbs
oracle@bedrock:~/app/oracle/product/10.2.0/server/dbs$ strings spfileXE.ora | grep -v cluster_database > initXE.ora
oracle@bedrock:~/app/oracle/product/10.2.0/server/dbs$ rm spfileXE.ora
oracle@bedrock:~/app/oracle/product/10.2.0/server/dbs$ sqlplus / as sysdba

SQL>