Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************Got the opportunity to pen a Book on Database Cloud Services.......More details to follow.***************

Title : Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2020

**********************************************************************************


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Azure Certified Architect

Azure Certified Administrator

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Wednesday, April 20, 2011

ORA-01567: dropping log

recently my dev which was a standalone got refreshed from prod which was 3 node RAC. When I try to drop the redo log groups I was getting below error:

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
6 2 UNUSED
7 3 ACTIVE
8 3 UNUSED
9 3 UNUSED
10 3 UNUSED


dropped couple of log groups:

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
7 3 ACTIVE
8 3 UNUSED

Now since this is a standalone db I don't need threads 2 & 3 so when I tried to drop group from thread 3:

SQL> alter database drop logfile group 8;
alter database drop logfile group 8
*
ERROR at line 1:
ORA-01567: dropping log 8 would leave less than 2 log files for instance i3
(thread 3)
ORA-00312: online log 8 thread 3:
'+ICMDEV_FRA_01/icmdev/onlinelog/group_8.261.748372139'
ORA-00312: online log 8 thread 3:
'+ICMDEV_DATA_01/icmdev/onlinelog/group_8.373.748372177'


Hmm Crazy...

I need to first disable the thread before deleting the logs.


SQL> alter database disable thread 3;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED
7 3 INACTIVE
8 3 UNUSED

6 rows selected.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> select group#,thread#,status from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT
4 2 ACTIVE
5 2 UNUSED



Also if the group you want to delete is current and you disabled the thread# of that group.

say.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
3 3 INACTIVE
3 4 CURRENT
1 11 INACTIVE
1 12 CURRENT


Here thread 3 and group 4 is current and now I without archiving I have disabled the thread# 3.



SQL> alter database disable thread 3;

Database altered.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
3 3 INACTIVE
3 4 INACTIVE
1 11 INACTIVE
1 12 CURRENT

6 rows selected.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance UNNAMED_INSTANCE_3 (thread 3) needs to be archived
ORA-00312: online log 4 thread 3:
'+DBNAME_REDO_01/dbname/onlinelog/group_4.263.762210781'
ORA-00312: online log 4 thread 3:
'+DBNAME_REDO_02/dbname/onlinelog/group_4.264.762210783'

Here it comes, since group4 was not archived I cannot just drop the group here.
So how do we overcome this.
SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#,group#,status from v$log;

THREAD# GROUP# STATUS
---------- ---------- ----------------
2 1 CURRENT
2 2 INACTIVE
1 11 INACTIVE
1 12 CURRENT



All is well now the thread# 3 is dropped.

3 comments:

Anonymous said...

Excellent!
Very good tips.
Thanks for helpme to resolve a problem when I was copy database from RAC to stand alone.

Chandra Cheedella said...

Cool, thanks for the solutions. Very useful.

ANURAG KUMAR PANDEY said...

GOOD TIPS THANK'S