I had to resize my Online Redo Logs, but to do that I had to drop old Redo Logs after adding new ones
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance testDB (thread 1)
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo01/testDB/redo_1a.log'
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo02/testDB/redo_1b.log'
I was not able to drop the old Redo Logs as all were showing up as Active, log switches didnt help
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 31 536870912 512 2 YES
ACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
2 1 29 536870912 512 2 YES
ACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
3 1 30 536870912 512 2 YES
ACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
4 1 32 1073741824 512 2 YES
ACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
5 1 33 1073741824 512 2 NO
CURRENT 1.5060E+13 10-JUN-17 2.8147E+14
6 1 28 1073741824 512 2 YES
ACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
6 rows selected.
To mark them inactive, do a checkpoint on the database
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 31 536870912 512 2 YES
INACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
2 1 29 536870912 512 2 YES
INACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
3 1 30 536870912 512 2 YES
INACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
4 1 32 1073741824 512 2 YES
INACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
5 1 33 1073741824 512 2 NO
CURRENT 1.5060E+13 10-JUN-17 2.8147E+14
6 1 28 1073741824 512 2 YES
INACTIVE 1.5060E+13 10-JUN-17 1.5060E+13 10-JUN-17
6 rows selected.
Now we can drop the old redo logs
SQL> alter database drop logfile group 1;
Database altered.
No comments:
Post a Comment