Oracle8i Enterprise Edition Release 8.1.7.0.0
Dokumentasi ini dibuat oleh Zainal Abidin
pada tanggal 28/04/2005 jam 3:59 Subuh
SQL-script yang digunakan ada pada akhir.
Skenario:Ukuran REDOLOG dari 10MB akan dibesarkan menjadi 11MB
SQL> @e:\myscripts\cek_redolog_status.sql;
********************
CHECK REDOLOG STATUS
********************
scripted by zj-he On 28/04/2005 at 2:00
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG ACTIVE 10
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG INACTIVE 10
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG CURRENT 10
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2
2 ('E:\ORACLE\ORADATA\DB8I\REDO02.LOG') size 11m
3* reuse;
Database altered.
SQL> @e:\myscripts\cek_redolog_status.sql;
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG ACTIVE 10
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG UNUSED 11
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG CURRENT 10
SQL> alter system switch logfile;
System altered.
SQL> @e:\myscripts\cek_redolog_status.sql;
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG INACTIVE 10
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG CURRENT 11
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG ACTIVE 10
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1
2 ('E:\ORACLE\ORADATA\DB8I\REDO01.LOG') SIZE 11m
3 REUSE;
Database altered.
SQL> @e:\myscripts\cek_redolog_status.sql;
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG UNUSED 11
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG CURRENT 11
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG ACTIVE 10
SQL> alter system switch logfile;
System altered.
SQL> @e:\myscripts\cek_redolog_status.sql;
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG CURRENT 11
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG INACTIVE 11
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG INACTIVE 10
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3
2 ('E:\ORACLE\ORADATA\DB8I\REDO03.LOG') SIZE 11m
3 REUSE;
Database altered.
SQL> @e:\myscripts\cek_redolog_status.sql;
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG CURRENT 11
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG INACTIVE 11
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG UNUSED 11
SQL> alter system switch logfile;
System altered.
SQL> @e:\myscripts\cek_redolog_status.sql;
********************
CHECK REDOLOG STATUS
********************
scripted by zj-he On 28/04/2005 at 2:00
GROUP# MEMBER STATUS SIZE (MB)
---------- ----------------------------------- ---------- ----------
1 E:\ORACLE\ORADATA\DB8I\REDO01.LOG INACTIVE 11
2 E:\ORACLE\ORADATA\DB8I\REDO02.LOG INACTIVE 11
3 E:\ORACLE\ORADATA\DB8I\REDO03.LOG CURRENT 11
SQL>
# cek_redolog_status.sql ################
prompt
prompt ********************
prompt CHECK REDOLOG STATUS
prompt ********************
prompt scripted by zj-he On 28/04/2005 at 2:00
prompt
col member format a40
SELECT A.GROUP#, A.MEMBER, B.STATUS, B.BYTES/(1024*1024) as "SIZE (MB)"
FROM V$LOGFILE A, V$LOG B
WHERE A.GROUP#=B.GROUP#
ORDER BY A.GROUP#;
# cek_redolog_status.sql ################
