Oracle : ORA-01624: log 2 needed for crash recovery of instance

Issue while dropping redo logs

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance db12 (thread 2)
ORA-00312: online log 2 thread 2: '+DB1_REDO1/db/onlinelog/group_2.259.994893411'
ORA-00312: online log 2 thread 2: '+DB1_REDO2/db/onlinelog/group_2.259.994893411'

Check the status of the Redo Group if its ACTIVE or INACTIVE
If its CURRENT, please do some redo switches

SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;

   THREAD#     GROUP#     Mbytes STATUS
---------- ---------- ---------- ----------------
         2          2        100 CURRENT
         1         11       6000 INACTIVE
         1         12       6000 ACTIVE
         1         13       6000 ACTIVE
         1         14       6000 ACTIVE
         1         15       6000 CURRENT
         1         16       6000 UNUSED
         1         17       6000 UNUSED
         1         18       6000 UNUSED
         2         21       6000 UNUSED
         2         22       6000 UNUSED
         2         23       6000 UNUSED
         2         24       6000 UNUSED
         2         26       6000 UNUSED
         2         27       6000 UNUSED
         2         28       6000 UNUSED

SQL> alter system switch logfile;

After performing some redo switch, the status of the REDO GROUP changed to ACTIVE, still preventing me from dropping the REDO GROUP

SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;

   THREAD#     GROUP#     Mbytes STATUS
---------- ---------- ---------- ----------------
         2          2        100 ACTIVE
         1         11       6000 CURRENT
         1         12       6000 INACTIVE
         1         13       6000 INACTIVE
         1         14       6000 INACTIVE
         1         15       6000 INACTIVE
         1         16       6000 INACTIVE
         1         17       6000 INACTIVE
         1         18       6000 INACTIVE
         2         21       6000 ACTIVE
         2         22       6000 ACTIVE
         2         23       6000 ACTIVE
         2         24       6000 CURRENT
         2         26       6000 UNUSED
         2         27       6000 UNUSED
         2         28       6000 UNUSED

Solution to do this is perform checkpoint

SQL> alter system checkpoint;

System altered.

SQL> select thread#, group#, bytes/1024/1024 "Mbytes", status from v$log;

   THREAD#     GROUP#     Mbytes STATUS

---------- ---------- ---------- ----------------

         2          2        100 INACTIVE

         1         11       6000 CURRENT

         1         12       6000 INACTIVE

         1         13       6000 INACTIVE

         1         14       6000 INACTIVE

         1         15       6000 INACTIVE

         1         16       6000 INACTIVE

         1         17       6000 INACTIVE

         1         18       6000 INACTIVE

         2         21       6000 INACTIVE

         2         22       6000 INACTIVE

         2         23       6000 INACTIVE

         2         24       6000 CURRENT

         2         26       6000 UNUSED

         2         27       6000 UNUSED

         2         28       6000 UNUSED

16 rows selected.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

Hope this resolves your issue.. 

Oracle : ORA-10485: Real-Time Query cannot be enabled while applying migration redo

Issue :

After patching when starting the Standby database, MRP process fails with the below error. Observed the below err messages in alert log of standby.

All non-current ORLs have been archived.
Media Recovery Log +PRODDB1_FRA/prodDB1/archivelog/2017_10_08/thread_1_seq_68958.4014.956804163
MRP0: Background Media Recovery terminated with error 10485
Errors in file /opt/app/prodDB1/oracle/diag/rdbms/prodDB1/prodDB1/trace/prodDB1_pr00_44779.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Sun Oct 08 03:07:15 2017
Completed: alter database recover managed standby database using current logfile disconnect
Recovery interrupted!
Block change tracking service stopping.
Stopping background process CTWR
Sun Oct 08 03:07:17 2017
MRP0: Background Media Recovery process shutdown (prodDB1)

Cause :

ORA-10485: Real-Time Query cannot be enabled while applying migration redo.

The Real-Time Query feature was enabled when an attempt was made to recover through migration redo generated during primary upgrades or downgrades

Solution

Start the standby database in mount stage and start applying logs till all the logs are applied, once the logs are applied, you can start the database in read only mode and enable Real Time Apply

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1379E+11 bytes
Fixed Size                  2265864 bytes
Variable Size            5.6371E+10 bytes
Database Buffers         1.5730E+11 bytes
Redo Buffers              112762880 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session;

Database altered.


Alert Log Shows
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session
Attempt to start background Managed Standby Recovery process (prodDB1)
Sun Oct 08 03:10:56 2017
MRP0 started with pid=84, OS id=233034 
MRP0: Background Managed Standby Recovery process started (prodDB1)



Check if all logs are applied and there is no GAP between the Primary database and the Standby


    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1             68965            68965
         2             69547            69547

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


Alert Log Shows

alter database recover managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (prodDB1)
Sun Oct 08 03:14:38 2017
MRP0 started with pid=84, OS id=33618 
MRP0: Background Managed Standby Recovery process started (prodDB1)
 started logmerger process
Sun Oct 08 03:14:43 2017
Managed Standby Recovery starting Real Time Apply
Only allocated 127 recovery slaves (requested 128)
Parallel Media Recovery started with 127 slaves

I hope this helps you.. 

ORA-01624 + ORA-00312 : While dropping Online Redo Logs

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.