Wednesday, November 29, 2006

Size of ASM files

Whilst testing compression of archivelogs on ASM I was looking for ways to find the size of an archivelog stored on ASM

You can

• Use the bytes column in v$asm_file.
• Use the asmcmd line utility and use the du command there in
• Use an API called dbms_diskgroup as shown below

SQL> select thread#,blocks,compressed from v$archived_log where thread#=2 and SEQUENCE#=19


THREAD# SEQUENCE# BLOCKS COM
---------- ---------- ---------- ---
2 19 3794 YES

[oracle@dbrac2 ~]$ asmcmd
ASMCMD> cd ARCHIVELOG
ASMCMD> ls
2006_11_28/
ASMCMD> cd 2006_11_28
ASMCMD> ls
thread_1_seq_10.289.607731765
thread_1_seq_11.293.607732151
thread_1_seq_12.296.607732301
thread_1_seq_8.286.607731393
thread_1_seq_9.287.607731413
thread_2_seq_13.288.607731413
thread_2_seq_14.290.607731771
thread_2_seq_15.291.607732073
thread_2_seq_16.292.607732151
thread_2_seq_17.294.607732277
thread_2_seq_18.295.607732301
thread_2_seq_19.297.607732423

set serveroutput on
declare
vfilename varchar2(4000);
vfilesize number;
vlbks number;
vfiletype number;
begin
dbms_output.enable(5000);
vfilename := '&file_name';
dbms_diskgroup.getfileattr(vfilename,vfiletype,vfilesize,vlbks);
dbms_output.put_line('File: '||vfilename); dbms_output.new_line;
dbms_output.put_line('Size (Logical Block Size): '||vfilesize); dbms_output.new_line;
dbms_output.put_line('Logical Block Size: '||vlbks); dbms_output.new_line;
dbms_output.put_line('File type: '||vfiletype); dbms_output.new_line;
end;
/


Enter value for file_name: +ORADATA1/TEST/ARCHIVELOG/2006_11_28/thread_2_seq_19.297.607732423
old 9: vfilename := '&file_name';
new 9: vfilename := '+ORADATA1/TEST/ARCHIVELOG/2006_11_28/thread_2_seq_19.297.607732423';
File: +ORADATA1/TEST/ARCHIVELOG/2006_11_28/thread_2_seq_19.297.607732423
Size (Logical Block Size): 3794
Logical Block Size: 512
File type: 4

PL/SQL procedure successfully completed.

2 comments:

Tonguç said...

Dear sir, how can we setup this supllied package? I searched in the 10gR2 documentation and couldn't find.

Also "vfiletype" in your code is not declared, is this normal?

Best regards,
Thank you for your efforts on this blog :)

Fairlie Rego said...

Thanks much
This package is not documented in 10G. Hopefully it will be documented in 11
I have not gotten around to decode the file types but will mention
them once I do.

Regards
Fairlie