Increasing the Redo Log size:
Increase the current redo logs from 50MB to 1GB
Redo Log status:
CURRENT
Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE
Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
INACTIVE
Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
1. Check Current Redo Logs
SQL> column group# format 99999;
SQL> column status format a10;
SQL> column mb format 99999;
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
1 CURRENT 50
2 INACTIVE 50
3 INACTIVE 50
2. Add 3 Groups with New Size (1GB)
SQL> alter database add logfile
group 4 ('/u01/app/oracle/oradata/ORCL/redo04.log') size 1g,
group 5 ('/u01/app/oracle/oradata/ORCL/redo05.log') size 1g,
group 6 ('/u01/app/oracle/oradata/ORCL/redo06.log') size 1g;
Database altered.
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
1 CURRENT 50
2 INACTIVE 50
3 INACTIVE 50
4 UNUSED 1024
5 UNUSED 1024
6 UNUSED 1024
6 rows selected.
3. Switch Logfile to New Groups
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
1 ACTIVE 50
2 INACTIVE 50
3 INACTIVE 50
4 CURRENT 1024
5 UNUSED 1024
6 UNUSED 1024
6 rows selected.
4. Force a CheckPoint
SQL> alter system checkpoint;
System altered.
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
1 INACTIVE 50
2 INACTIVE 50
3 INACTIVE 50
4 CURRENT 1024
5 UNUSED 1024
6 UNUSED 1024
6 rows selected.
5. Drop Group 1, 2, 3
SQL> alter database drop logfile group 1, group 2, group 3;
Database altered.
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
4 CURRENT 1024
5 UNUSED 1024
6 UNUSED 1024
6. Remove Redo Log Files
[oracle@test ~]$ rm -i /u01/app/oracle/oradata/ORCL/redo0[1-3].log
rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo01.log'? y
rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo02.log'? y
rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo03.log'? y
7. Add Group 1, 2, 3 with New Size (1GB)
SQL> alter database add logfile
group 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') size 1g,
group 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') size 1g,
group 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') size 1g;
Database altered.
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
1 UNUSED 1024
2 UNUSED 1024
3 UNUSED 1024
4 CURRENT 1024
5 UNUSED 1024
6 UNUSED 1024
6 rows selected.
8. Switch Logfile Several Times
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
9. Check Status of All Redo Logs
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
1 ACTIVE 1024
2 ACTIVE 1024
3 ACTIVE 1024
4 ACTIVE 1024
5 ACTIVE 1024
6 CURRENT 1024
6 rows selected.
SQL> column member format a40;
SQL> select group#, member from v$logfile;
GROUP# MEMBER
------ ----------------------------------------
1 /u01/app/oracle/oradata/ORCL/redo01.log
2 /u01/app/oracle/oradata/ORCL/redo02.log
3 /u01/app/oracle/oradata/ORCL/redo03.log
4 /u01/app/oracle/oradata/ORCL/redo04.log
5 /u01/app/oracle/oradata/ORCL/redo05.log
6 /u01/app/oracle/oradata/ORCL/redo06.log
6 rows selected.
Regards,
Mallik
No comments:
Post a Comment