SQL> set pages 200
SQL> col instance_name for a20
SQL> col host_name for a20
SQL> col status for a15
SQL> select instance_name, host_name, archiver, thread#, status from gv$instance;
INSTANCE_NAME HOST_NAME ARCHIVE THREAD# STATUS
-------------------- -------------------- ------- ---------- ---------------
racdb1 rac1.gmail.com STARTED 1 OPEN
racdb2 rac2.gmail.com STARTED 2 OPEN
SQL> show sga
Total System Global Area 726540288 bytes
Fixed Size 2216904 bytes
Variable Size 574622776 bytes
Database Buffers 142606336 bytes
Redo Buffers 7094272 bytes
SQL>
SQL> set lines 200
SQL> set pages 200
SQL> col FILE_NAME for a50
SQL> select file_name, bytes/1024/1024 from dba_data_files;
FILE_NAME BYTES/1024/1024
-------------------------------------------------- ---------------
+DATA/racdb/datafile/system.260.901145703 700
+DATA/racdb/datafile/sysaux.261.901145751 600
+DATA/racdb/datafile/undotbs1.262.901145787 595
+DATA/racdb/datafile/undotbs2.264.901145829 200
+DATA/racdb/datafile/users.265.901145839 5
SQL>
SQL> select group#, type, member, is_recovery_dest_file
2 from v$logfile
3 order by group#
4 /
GROUP# TYPE MEMBER IS_
------ ------- --------------------------------------------------- ---
1 ONLINE +ORADATA/esxrac/onlinelog/group_1.257.620732695 NO
1 ONLINE +FLASH_RECO_AREA/esxrac/onlinelog/group_1.257.620732699 YES
2 ONLINE +ORADATA/esxrac/onlinelog/group_2.258.620732703 NO
2 ONLINE +FLASH_RECO_AREA/esxrac/onlinelog/group_2.258.620732707 YES
3 ONLINE +ORADATA/esxrac/onlinelog/group_3.266.620737527 NO
3 ONLINE +FLASH_RECO_AREA/esxrac/onlinelog/group_3.259.620737533 YES
4 ONLINE +ORADATA/esxrac/onlinelog/group_4.267.620737535 NO
4 ONLINE +FLASH_RECO_AREA/esxrac/onlinelog/group_4.260.620737539 YES
Querying the v$asm_diskgroup view...
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 FLASH_RECO_AREA 1048576 CONNECTED EXTERN 10236 2781
2 ORADATA 1048576 CONNECTED NORMAL 20472 8132
Querying v$asm_disk for our volumes (remember the ones we created first on OS level with the asmlib) :
select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb from v$asm_disk; NAME PATH HEADER_STATU FREE_MB READ_MB WRITE_MB ----- ---------- ------------ ---------- ---------- ---------- VOL1 ORCL:VOL1 MEMBER 10236 39617 15816 VOL2 ORCL:VOL2 MEMBER 10236 7424 15816 VOL3 ORCL:VOL3 MEMBER 10236 1123 13059
All datafiles in one go:
SQL> select name from v$datafile 2 union 3 select name from v$controlfile 4 union 5 select name from v$tempfile 6 union 7 select member from v$logfile 8 / NAME -------------------------------------------------------------------------------- +FLASH_RECO_AREA/esxrac/controlfile/current.256.620732691 +FLASH_RECO_AREA/esxrac/onlinelog/group_1.257.620732699 +FLASH_RECO_AREA/esxrac/onlinelog/group_2.258.620732707 +FLASH_RECO_AREA/esxrac/onlinelog/group_3.259.620737533 +FLASH_RECO_AREA/esxrac/onlinelog/group_4.260.620737539 +ORADATA/esxrac/controlfile/current.256.620732689 +ORADATA/esxrac/datafile/example.263.620732791 +ORADATA/esxrac/datafile/sysaux.261.620732767 +ORADATA/esxrac/datafile/system.259.620732719 +ORADATA/esxrac/datafile/undotbs1.260.620732753 +ORADATA/esxrac/datafile/undotbs2.264.620732801 +ORADATA/esxrac/datafile/users.265.620732817 +ORADATA/esxrac/onlinelog/group_1.257.620732695 +ORADATA/esxrac/onlinelog/group_2.258.620732703 +ORADATA/esxrac/onlinelog/group_3.266.620737527 +ORADATA/esxrac/onlinelog/group_4.267.620737535 +ORADATA/esxrac/tempfile/temp.262.620732779 17 rows selected.
Listing all the tablespaces...
SQL> select tablespace_name, file_name 2 from dba_data_files 3 union 4 select tablespace_name, file_name 5 from dba_temp_files 6 / TABLESPACE_NAME FILE_NAME ------------------ -------------------------------------------------------------------------------- EXAMPLE +ORADATA/esxrac/datafile/example.263.620732791 SYSAUX +ORADATA/esxrac/datafile/sysaux.261.620732767 SYSTEM +ORADATA/esxrac/datafile/system.259.620732719 TEMP +ORADATA/esxrac/tempfile/temp.262.620732779 UNDOTBS1 ` +ORADATA/esxrac/datafile/undotbs1.260.620732753 UNDOTBS2 +ORADATA/esxrac/datafile/undotbs2.264.620732801 USERS +ORADATA/esxrac/datafile/users.265.620732817 7 rows selected.
This script will give you information of the +ASM1 instance files:
SQL> select group_number, file_number, bytes/1024/1024/1024 GB, type, striped, modification_date
2 from v$asm_file
3 where TYPE != 'ARCHIVELOG'
4 /
GRP_NUM FILE_NUM GB TYPE STRIPE MODIFICAT
------- -------- -------- --------------- ------ ---------
1 256 .01 CONTROLFILE FINE 04-MAY-07
1 257 .05 ONLINELOG FINE 25-MAY-07
1 258 .05 ONLINELOG FINE 24-MAY-07
1 259 .05 ONLINELOG FINE 24-MAY-07
1 260 .05 ONLINELOG FINE 25-MAY-07
1 261 .00 PARAMETERFILE COARSE 24-MAY-07
2 256 .01 CONTROLFILE FINE 04-MAY-07
2 257 .05 ONLINELOG FINE 25-MAY-07
2 258 .05 ONLINELOG FINE 24-MAY-07
2 259 .49 DATAFILE COARSE 04-MAY-07
2 260 .20 DATAFILE COARSE 04-MAY-07
2 261 .65 DATAFILE COARSE 23-MAY-07
2 262 .03 TEMPFILE COARSE 04-MAY-07
2 263 .15 DATAFILE COARSE 04-MAY-07
2 264 .20 DATAFILE COARSE 04-MAY-07
2 265 .00 DATAFILE COARSE 04-MAY-07
2 266 .05 ONLINELOG FINE 24-MAY-07
2 267 .05 ONLINELOG FINE 25-MAY-07
18 rows selected.
More detailed information:
SQL> select group_number, file_number, compound_index, incarnation, block_size, bytes/1024/1024/1024 GB, type, striped,
2 creation_date, modification_date
3 from v$asm_file
4 where TYPE != 'ARCHIVELOG'
5 /
GRP_NUM FILE_NUM COMPOUND_INDEX INCARNATION BLOCK_SIZE GB TYPE STRIPE CREATION_ MODIFICAT
------- -------- -------------- ----------- ---------- -------- --------------- ------ --------- ---------
1 256 16777472 620732691 16384 .01 CONTROLFILE FINE 24-APR-07 04-MAY-07
1 257 16777473 620732699 512 .05 ONLINELOG FINE 24-APR-07 25-MAY-07
1 258 16777474 620732707 512 .05 ONLINELOG FINE 24-APR-07 24-MAY-07
1 259 16777475 620737533 512 .05 ONLINELOG FINE 24-APR-07 24-MAY-07
1 260 16777476 620737539 512 .05 ONLINELOG FINE 24-APR-07 25-MAY-07
1 261 16777477 620737547 512 .00 PARAMETERFILE COARSE 24-APR-07 24-MAY-07
2 256 33554688 620732689 16384 .01 CONTROLFILE FINE 24-APR-07 04-MAY-07
2 257 33554689 620732695 512 .05 ONLINELOG FINE 24-APR-07 25-MAY-07
2 258 33554690 620732703 512 .05 ONLINELOG FINE 24-APR-07 24-MAY-07
2 259 33554691 620732719 8192 .49 DATAFILE COARSE 24-APR-07 04-MAY-07
2 260 33554692 620732753 8192 .20 DATAFILE COARSE 24-APR-07 04-MAY-07
2 261 33554693 620732767 8192 .65 DATAFILE COARSE 24-APR-07 23-MAY-07
2 262 33554694 620732779 8192 .03 TEMPFILE COARSE 24-APR-07 04-MAY-07
2 263 33554695 620732791 8192 .15 DATAFILE COARSE 24-APR-07 04-MAY-07
2 264 33554696 620732801 8192 .20 DATAFILE COARSE 24-APR-07 04-MAY-07
2 265 33554697 620732817 8192 .00 DATAFILE COARSE 24-APR-07 04-MAY-07
2 266 33554698 620737527 512 .05 ONLINELOG FINE 24-APR-07 24-MAY-07
2 267 33554699 620737535 512 .05 ONLINELOG FINE 24-APR-07 25-MAY-07
No comments:
Post a Comment