Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”
SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;
SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;
Wszystkie redologi w grupie mają taką samą zawartość. Oracle zaleca aby w grupie były co najmniej dwa redologi.
W przypadku “cannot allocate new log” – należy dodać nowe grupy logów.
sql> alter database drop logfile group 1;
gdy grupa ma status active należy wykonać:
sql> alter system checkpoint;
sql> alter system switch logfile;
REDO LOG STATUS:
- USED. Indicates either that a log has just been added (never used), or that a RESETLOGS command has been issued.
- CURRENT. Indicates a valid log that is in use.
- ACTIVE. Indicates a valid log file that is not currently in use.
- CLEARING. Indicates a log is being re-created as an empty log due to DBA action.
- CLEARING CURRENT. Means that a current log is being cleared of a closed thread. If a log stays in this status, it could indicate there is some failure in the log switch.
- INACTIVE. Means that the log is no longer needed for instance recovery but may be needed for media recovery.
The v$logfile table has a status indicator that gives these additional codes:
- INVALID. File is inaccessible.
- STALE. File contents are incomplete (such as when an instance is shut down with SHUTDOWN ABORT or due to a system crash).
- DELETED. File is no longer used.