Home » Posts tagged 'v$log'
Tag Archives: v$log
How can we calculate archivelog size each day?
How can we calculate archivelog size each day/hour?
This is not a difficult idea for someone who work as DBA, I just wish more idea for discussion and sharing . Some Idea using V$LOG.BYTES and V$ARCHIVED_LOG.*
but I believe V$ARCHIVED_LOG view be able to help:
Archivelog size each day:
SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;
TIME SIZE_MB
———- ———-
2011-02-12 71797.87
2011-02-13 75880.52
2011-02-14 73569.37
2011-02-15 76776.81
2011-02-16 73959.86
2011-02-17 69969.71
2011-02-18 74677.10
2011-02-19 75474.95
2011-02-20 77967.07
2011-02-21 67802.70
Archivelog size each hour:
SQL> alter session set nls_date_format = ‘YYYY-MM-DD HH24’;
Session altered. (more…)
Resize redo log groups in Oracle
Here is the procedure to resize the redo log group in SAP/Oracle
To check all Redolog files Run command:
Select * from v$log;
Now add a group, To add a log group
Alter database add logfile group 5 (‘/oracle/SID/origlogA/log_g15m1.dbf’,’/oracle/SID/mirrlogA/log_g15m2.dbf’) size 200M;
alter database add logfile group 6 (‘/oracle/SID/origlogB/log_g16m1.dbf’,’/oracle/SID/mirrlogB/log_g16m2.dbf’) size 200M;
alter database add logfile group 7 (‘/oracle/SID/origlogA/log_g17m1.dbf’,’/oracle/SID/mirrlogA/log_g17m2.dbf’) size 200M;
alter database add logfile group 8 (‘/oracle/SID/origlogB/log_g18m1.dbf’,’/oracle/SID/mirrlogB/log_g18m2.dbf’) size 200M;
After these commands, Force System for log switch
Alter system switch logfile;
Drop Existing Log groups files (Make sure no group has status active or current)
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;
Alter database drop logfile group 4;
Remove these files from OS level
Now again add same Group
alter database add logfile group 1 (‘/oracle/SID/origlogA/log_g11m1.dbf’,’/oracle/SID/mirrlogA/log_g11m2.dbf’) size 200M;
alter database add logfile group 2 (‘/oracle/SID/origlogB/log_g12m1.dbf’,’/oracle/SID/mirrlogB/log_g12m2.dbf’) size 200M;
alter database add logfile group 3 (‘/oracle/SID/origlogA/log_g13m1.dbf’,’/oracle/SID/mirrlogA/log_g13m2.dbf’) size 200M;
alter database add logfile group 4 (‘/oracle/SID/origlogB/log_g14m1.dbf’,’/oracle/SID/mirrlogB/log_g14m2.dbf’) size 200M;
We can drop Log group 5,6,7,8
In case any of these groups are Active or current stage use “Alter system switch logfile”
Alter database drop logfile group 5;
Alter database drop logfile group 6;
Alter database drop logfile group 7;
Alter database drop logfile group 8;