Want to know a way to find which file system is use by an oracle SID

[Sebastien Masson]

I,

I try to find a way to get all file system use by a specific oracle SID. Those a kind of readable file existing in Oracle that can tell me this information ?

The problem is I have a server that have a lot of file system, a lot of SID and I want to do a dynamic mapping.

Thanks

[Hein van den Heuvel]

The only true place to get this data is through Oracle.
You may well have a careful naming convention in place with which you can identify likely instance-related files, and you may be able to tell by dates, ownerships and sizes, but the the true source is oracle.

For each SID you'll have to connect an run script to query the tables holding the filenames. Dozens, no hundreds, of such scripts exist already. I'll include the one I wrote below.
Depending on the file / directory naming that output may be enough, or you may need to run it to a mastre script to
- walk all known sids
- do a df or somesuch for each file reported.

Good Luck,
Hein.

column status format a8
column member format a55
column file_name format a45
column Tablespace format a15
column segment_name format a15
column extent_name format a15
column owner format a10
column file format a45
column id format 99

set pages 9999
set heading off
set FEEDBACK off
select 'redo_'||l.group# "Tablespace", l.group# "Id", l.bytes/(1024*1024) "MB",
MEMBER "File" from v$logfile f, v$log l where l.group# = f.group#
union
select tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_data_files
union
select tablespace_name "Tablespace", FILE_ID "Id", bytes/(1024*1024) "MB",
file_name "File" from dba_temp_files
union
select 'Control_file' "Tablespace", rownum "Id", 0 "MB",
name "File" from v$controlfile
order by 1
/

[Indira Aramandla]

Hi Sebastien,

Hein has explained in detail.

Generally an oracle database comprises of the datafiles, redo-logs, control files, initialisation parameter file, logs (archive logs, alter log, userdump log, audit logs) and any configuration files.

You can get all the files used by a specific oracle SID, by quering the views (v$controlfile, v$datafile, v$lofile, v$parameter).

v$controlfile à gives the controlfile location and name.
Eg:- SQL> select name from v$controlfile;
If you have three control files then this will display
NAME
/ora01/oradata/sid/ctr_sid01.ctl
/ora01/oradata/sid/ctr_sid02.ctl
/ora01/oradata/sid/ctr_sid03.ctl

V$datafile à gives the datafile location and name. Each tablespace in the database will be accoiated with one or more datafiles. You can see in Heins script the relationship between the tablespace, file_id and file_name.
Eg:- SQL> select name from v$datafile;
Will display the pathname and the datafilename.
NAME
/ora01/oradata/sid/sid_system01.dbf
/ora01/oradata/sid/sid_users01.dbf
/ora01/oradata/sid/sid_tools01.dbf
/ora01/oradata/sid/sid_rbs01.dbf
/ora01/oradata/sid/sid_temp01.dbf
/ora01/oradata/sid/sid_data01.dbf
--------
--------
v$logfile à gives the redo-log file location and name. If you had 2 groups with 2 memebers in wach group then this will return the 4 members.
Eg:-
SQL> select member from v$logfile;
MEMBER
/ora01/oradata/sid/log1a_sid.rdo
/ora01/oradata/sid/log1b_sid.rdo
/ora01/oradata/sid/log2a_sid.rdo
/ora01/oradata/sid/log2b_sid.rdo

v$parameter àgives the name and value )location of the logs (archive, alter, user dump, audit, trace) and also the control files names and location.
Eg:- SQL>select name , value from v$paraemter
NAME VALUE
audit_file_dest /opt/oracle/rdbms/audit
background_dump_dest /opt/oracle/admin/sid/bdump
core_dump_dest /opt/oracle/admin/sid/cdump
user_dump_dest /opt/oracle/admin/sid/udump
log_archive_dest /ora01/oraarch/sid/
oracle_trace_collection_p /opt/oracle/admin/sid/otrace/cdf
utl_file_dir /ora02/tmp/

control_files /ora01/oradata/sid/ctr_sid01.ctl, /ora01/oradata/sid/ ctr_sid02.ctl, /ora01/oradata/sid/ctr_pmis03.ctl

And you might have any configuration files and oratab file.

[Sebastien Masson]

Thank you for the information but like I said, I'm not a DBA and I dont think I have an account in sqlplus to run those query. Is there a kind of anonymous userid that I can use to do those query in view?

Thanks

Sebastien

[Indira Aramandla]

Hi Sebastien,

In that case do you have somebody as a DBA there, who can run the queires for you.

Or if any one from the systems administrators team can logon to the server as oracle user which belongs to the DBA group then they can logon to the database using sqlplus and connect as sysdba.

The above views are stored in the database dictionary. You cannot see them without loggin into the database. The parameter file init
Indira A


IA

[Sebastien Masson]

Ok, the idea is I want to run a script to get this information. I don't want a DBA run the query for me and send me the output. But thanks for the information

[Yogeeraj_1]

hi,

one additional note.

you still need to get a full list of existing SIDs, so that you can export the ORACLE_SID to be able to run sqlplus as the Oracle Users on the OS level.

Each time, make sure that you are on the right database instance:
select name from v$database;

Also, if you are abiding by the OFA recommendations, this should be less painfull...

regards
yogeeraj

[TwoProc]

If you don't have DBA access - you can run "lsof" on the running Oracle processes to see what files it has open. Ditto for using Glance. Not nearly as easy as running the sqlplus commands provided earlier to you by other forum memberts though, using lsof of on each process is a bit more messy.

You could also run the "fuser -u" command on each file system and grep it to see if the user "oracle" (or whichever user owns the process) has an open file there.

fuser -u /dev/vg003/lvol2 | grep -i oracle

would tell you if the user oracle has an open file in the file system contained in that lvol.