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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

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


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

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