RE: Oracle Database physical files and file system details
Date: Fri, 12 Oct 2012 16:27:28 -0500
Message-ID: <F077F09A0E11504D9E720358BEE994D10B0C2519_at_APSW0553EVS.ms.ds.uhc.com>
Agree with Martin. The database makes it a point not to know what is beyond the location and name of the files so that you can configure and change this without making changes inside the database.
When we wanted that information at an older job, we wrote shell scripts to pull the data file names from the database and use that information to then interrogate the file system to find out all the details. You could always have that program then insert the info into some table in the database if you want to store it that way.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
Sent: Friday, October 12, 2012 3:39 PM
To: kenneth.r.fowler_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: Oracle Database physical files and file system details
Kenneth,
sorry, the database does not cares what's 'below'. As it can open a
symlink the same way as it opens a file - there is no difference.
You will have to go the hard way to collect all the data/temp/logfiles
and then resolve them on OS-layer.
IF your databases has dNFS enabled, you can check *v$dnfs_files* and the other v$dnfs_* views for any information on NFS files. But it is quite unlikely.
sorry,
Martin
On Fri, Oct 12, 2012 at 10:13 PM, Kenneth Fowler < kenneth.r.fowler_at_gmail.com> wrote:
> Hi List,
>
>
> Can anyone tell me if there is a v$, x$ or dba_ table/view that
> contains file system details for the physical files that make up an
> oracle database?
>
>
> I recently accepted a job (Oracle DBA) at a new company and I notice
> that the standard database layout here sometimes makes use of Unix
> soft links. Lets say the database files from dba_data_files look like
> this...
>
> SQL> select version from v$instance;
>
> VERSION
> -----------------
> 11.2.0.2.0
>
> SQL> select file_name from dba_data_files;
>
> FILE_NAME
>
> ----------------------------------------------------------------------
> --------------------------------------------------------------
> /u01/oradata/dbarep/system01.dbf
> /u01/oradata/dbarep/sysaux01.dbf
> /u01/oradata/dbarep/undotbs01.dbf
> /u01/oradata/dbarep/users01.dbf
>
>
> But there are soft links involved...
>
> $ ls -ald /u01
> lrwxrwxrwx 1 oracle dba 4 Feb 4 2011 /u01 -> /mnt/nfsstore/oracle
>
>
> So the database files are stored in
> /mnt/nfsstore/oracle/oradata/dbarep which happens to be an NFS file
> system that is giving a lot of trouble lately. I have already set up
> an automated process that will collect database metadata for loading
> into a DBA repository for analysis and reporting etc. I already
> collect dba_data_files, dba_temp_files along with a lot of other stuff
> however I checked around (Google mostly) but cannot find any reference
> to v$, x$ or dba_ tables/views that can tell me what file system a
> file happens to be on and if it is local or NFS etc. I realize that I
> could get this information by using Unix commands (df for example) but
> that starts to get a bit messy so my hope was that I could somehow get
> the information from the database (sqlplus). Any ideas?
>
>
> Thanks,
> Ken.
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-l This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 12 2012 - 23:27:28 CEST