ORA-00350: log 1 of instance needs to be archived
Error: ORA-00350: log 1 of instance needs to be archived.
During running the following clear or drop log-file commands. we are getting the following errors:
alter database clear logfile group 1;
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: 'E:\oracle\oradata\orcl\redo01.log'
---------------------------------------------------------------------
alter database drop logfile group 1;
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 2) needs to be archived
ORA-00312: online log 1 thread 2: 'E:\oracle\oradata\orcl\redo01.log'
Solution:
1. Check the redo log current status
select group#, thread#, sequence#, status, archived from v$log;
GROUP# THREAD# SEQUENCE# STATUS ARC ------ ------- --------- ---------- --- 1 1 20 CURRENT NO 2 1 18 ACTIVE NO 3 1 19 ACTIVE NO
2. Make redo log file 2 to INACTIVE and ARC column to YES.
Note: Check status of Redo log file Archived or not and Status
-- For checkpoint
alter system checkpoint;
-- For clear archive log
alter system archive log all;
-- Check status
select group#, thread#, sequence#, status, archived from v$log;
GROUP# THREAD# SEQUENCE# STATUS ARC ------ ------- --------- ---------------- --- 1 1 20 CURRENT NO 2 1 18 INACTIVE YES 3 1 19 INACTIVE YES
3. Then tried the command you are running above. If still getting error then clear the archive with following command:
Note: Don’t run on DR Server it unarchived the log file and take full backup after it.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
OR
alter database clear unarchived logfile 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG';
You got following message in alert log after clear unarchived log file:
alter database clear unarchived logfile group 2
2019-02-03T19:50:47.723377-06:00
Thread 1 advanced to log sequence 22043 (preparation to clear logfile)
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 02/03/2019 19:50:36 (CHANGE 664743475) CANNOT BE USED FOR RECOVERY.
