Re: Oracle Database physical files and file system details

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 12 Oct 2012 22:38:33 +0200
Message-ID: <CALH8A90NhyrdgRVKgKMX4MtTRa8X_ouOENwZkOHytbvhJ=T1Cw_at_mail.gmail.com>

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

Received on Fri Oct 12 2012 - 22:38:33 CEST