Add, Remove Oracle Online/Standby Redo Log file
High level steps-->
Step
1 : Check the Status of Redo Logfile.
Step 2 : Fire Checkpoint to get the status of logfile changed to INACTIVE.
Step 3 : Drop
Redo Log File with status 'INACTIVE'
Step 4 : Create
new redo log file with new Size and/or new location.
Point to remember:
Add new Redo Logfile:
- Might face errors ORA-00301, ORA-27038
- Cannot resize the redo log files and must drop the redolog file and recreate with new size.
- A database requires atleast two groups of redo log files,regardless the number of the members.
- We cannot the drop the redo log file if its status is current or active.
- We have change the status to "inactive" then only we can drop it.
- When a redo log member is dropped from the database, the operating system file is not deleted from disk.
- Note: Above case of file not deleted from the disk/Storage level, happens only in case of drop the redo member. If we are dropping the redo group, all the member files are dropped from the storage level.
Command to check the Redo logfile information:
SQL> set lines 200 pages 200
SQL> col member for a90
SQL> select group#, member, status from v$logfile order by group#;
SQL> select group#, thread#, members , status, bytes/1024/1024 from v$log;
Online redo logfile
break on type skip pages
set colsep |
set lines 200 pages 2000
col member for a80
select l.group#, l.thread#, f.type,f.member, l.archived,l.status , sum(bytes)/1024/1024 SizeMB
from v$log l , v$logfile f
where f.group#=l.group#
group by l.group#, l.thread#, f.member, l.archived,l.status, f.type
order by 3,1,2 ;
where f.group#=l.group#
group by l.group#, l.thread#, f.member, l.archived,l.status, f.type
order by 3,1,2 ;
Standby redo logfile
break on type skip pages
set colsep |
set lines 200 pages 2000
col member for a80
select l.group#, l.thread#, f.type,f.member, l.archived,l.status , sum(bytes)/1024/1024 SizeMB
from v$standby_log l , v$logfile f
where f.group#=l.group#
group by l.group#, l.thread#, f.member, l.archived,l.status, f.type
order by 3,1,2 ;
Drop Redo Logfile:
alter database drop logfile group &GROUP_NUMBER;
Add new Redo Logfile:
In case - DB_LOG_FILE_CREATE_DEST parameter is NOT set:
alter database add logfile thread 1 group 7 ('+REDO1_MYDB','+REDO2_MYDB') size 2G;
In case - DB_LOG_FILE_CREATE_DEST parameter is SET:
alter database add logfile thread 1 group 11 size 15G;
No comments:
Post a Comment