Check status of Redo logs.
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 313 209715200 512 1 NO INACTIVE 18070233 10-NOV-24 18101766 16-NOV-24 0
2 1 314 209715200 512 1 NO INACTIVE 18101766 16-NOV-24 18220074 16-NOV-24 0
3 1 315 209715200 512 1 NO CURRENT 18220074 16-NOV-24 1.8447E+19 0 0
set lines 300 pages 300
col member for a40
select a.group#,a.member,b.bytes/1024/1024 MB,b.status from v$logfile a,v$log b where a.group#=b.group# order by 1;
GROUP# MEMBER MB STATUS
---------- ---------------------------------------- ---------- ----------------
1 C:\ORACLE\APP\ORADATA\ORCL\REDO01.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO02.LOG 200 INACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO03.LOG 200 CURRENT
Add the redo member to existing group
Use below command to add redo member to each group.SQL> ALTER DATABASE ADD LOGFILE MEMBER 'C:\ORACLE\APP\ORADATA\ORCL\REDO04.LOG' to GROUP 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'C:\ORACLE\APP\ORADATA\ORCL\REDO05.LOG' to GROUP 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG' to GROUP 3;
Database altered.
Check Status :
GROUP# MEMBER MB STATUS
---------- ---------------------------------------- ---------- ----------------
1 C:\ORACLE\APP\ORADATA\ORCL\REDO01.LOG 200 INACTIVE
1 C:\ORACLE\APP\ORADATA\ORCL\REDO04.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO05.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO02.LOG 200 INACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG 200 CURRENT
3 C:\ORACLE\APP\ORADATA\ORCL\REDO03.LOG 200 CURRENT
6 rows selected.
DROP the redo member from existing group.
Let's delete log file member 4 which status is not current.
SQL> alter database drop logfile member 'C:\ORACLE\APP\ORADATA\ORCL\REDO04.log';
Database altered.
SQL> select a.group#,a.member,b.bytes/1024/1024 MB,b.status from v$logfile a,v$log b where a.group#=b.group# order by 1;
GROUP# MEMBER MB STATUS
---------- ---------------------------------------- ---------- ----------------
1 C:\ORACLE\APP\ORADATA\ORCL\REDO01.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO02.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO05.LOG 200 INACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG 200 CURRENT
3 C:\ORACLE\APP\ORADATA\ORCL\REDO03.LOG 200 CURRENT
NOTE : You cannot drop member id logfile group status is CURRENT.
SQL> alter database drop logfile member 'C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG';
alter database drop logfile member 'C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: 'C:\ORACLE\APP\ORADATA\ORCL\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG'
To Change the status Active, Current or Inactive redo logs.
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,a.member,b.bytes/1024/1024 MB,b.status from v$logfile a,v$log b where a.group#=b.group# order by 1;
GROUP# MEMBER MB STATUS
---------- ---------------------------------------- ---------- ----------------
1 C:\ORACLE\APP\ORADATA\ORCL\REDO01.LOG 200 CURRENT
2 C:\ORACLE\APP\ORADATA\ORCL\REDO02.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO05.LOG 200 INACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG 200 ACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO03.LOG 200 ACTIVE
-->To clear Active status of redo logs.
SQL> alter system checkpoint;
System altered.
SQL>
SQL> select a.group#,a.member,b.bytes/1024/1024 MB,b.status from v$logfile a,v$log b where a.group#=b.group# order by 1;
GROUP# MEMBER MB STATUS
---------- ---------------------------------------- ---------- ----------------
1 C:\ORACLE\APP\ORADATA\ORCL\REDO01.LOG 200 CURRENT
2 C:\ORACLE\APP\ORADATA\ORCL\REDO02.LOG 200 INACTIVE
2 C:\ORACLE\APP\ORADATA\ORCL\REDO05.LOG 200 INACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO06.LOG 200 INACTIVE
3 C:\ORACLE\APP\ORADATA\ORCL\REDO03.LOG 200 INACTIVE
No comments:
Post a Comment