Search This Blog

Total Pageviews

Monday, 1 November 2010

Oracle RAC file information on Sql prompt

set linesize 200

select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile
/

NAME
--------------------------------------------------------------------------------
+DATA/rac/controlfile/current.256.733574031
+DATA/rac/datafile/sysaux.261.733574211
+DATA/rac/datafile/system.259.733574119
+DATA/rac/datafile/undotbs1.260.733574185
+DATA/rac/datafile/undotbs2.263.733574285
+DATA/rac/datafile/users.264.733574311
+DATA/rac/onlinelog/group_1.257.733574057
+DATA/rac/onlinelog/group_2.258.733574085
+DATA/rac/onlinelog/group_3.265.733578267
+DATA/rac/onlinelog/group_4.266.733578281
+DATA/rac/tempfile/temp.262.733574229

11 rows selected.



set linesize 200

select group_number, name,allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb
from v$asm_diskgroup

GROUP_NUMBER NAME ALLOC_UNIT_SIZE STATE TYPE TOTAL_MB USABLE_FILE_MB
------------ -------------------- --------------- ----------- ------ ---------- --------------
1 DATA 1048576 CONNECTED NORMAL 3057 -264



col file_name format a50
select file_name, bytes/1024/1024 from dba_data_files

FILE_NAME BYTES/1024/1024
-------------------------------------------------- ---------------
+DATA/rac/datafile/system.259.733574119 410
+DATA/rac/datafile/undotbs1.260.733574185 110
+DATA/rac/datafile/sysaux.261.733574211 230
+DATA/rac/datafile/undotbs2.263.733574285 100
+DATA/rac/datafile/users.264.733574311 5



col HOST_NAME format a20
select instance_name, host_name, archiver, thread#, status from gv$instance

INSTANCE_NAME HOST_NAME ARCHIVE THREAD# STATUS
---------------- -------------------- ------- ---------- ------------
rac1 rac1 STOPPED 1 OPEN
rac2 rac2 STOPPED 2 OPEN

col MEMBER format a50
select group#, type, member, is_recovery_dest_file from v$logfile
order by group#

GROUP# TYPE MEMBER IS_
---------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/rac/onlinelog/group_1.257.733574057 NO
2 ONLINE +DATA/rac/onlinelog/group_2.258.733574085 NO
3 ONLINE +DATA/rac/onlinelog/group_3.265.733578267 NO
4 ONLINE +DATA/rac/onlinelog/group_4.266.733578281 NO



select group_number, file_number, compound_index, incarnation, block_size, bytes/1024/1024/1024 GB, type, striped,
creation_date, modification_date
from v$asm_file
where TYPE != 'ARCHIVELOG'
/

no rows selected


SQL>
select group_number, file_number, bytes/1024/1024/1024 GB, type, striped, modification_date
from v$asm_file
where TYPE != 'ARCHIVELOG' ;


no rows selected

No comments:

Oracle DBA

anuj blog Archive