ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

--- Though it is a fuzzy thing and can be done easily in minutes, yet thought to record it here for a reference. I was to simulate a quick recovery scenario and aborted my database instance just to change my database mode to ARCHIVELOG.


DB Details:
------------

C:\app\oracle\product\12.1.0\dbhome_3\BIN>sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 31 00:17:52 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, open_mode, database_role, cdb from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
PROD1     READ WRITE           PRIMARY          YES

Archiving is disabled:
------------------------

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     209
Current log sequence           211

Shutdown abort:
-----------------

SQL> shut abort
ORACLE instance shut down.

<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>

Wed Jan 31 00:18:22 2018
Shutting down instance (abort)
License high water mark = 8
Wed Jan 31 00:18:22 2018
USER (ospid: 11960): terminating the instance
Wed Jan 31 00:18:31 2018
Instance terminated by USER, pid = 11960
Wed Jan 31 00:18:31 2018
Instance shutdown complete

<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>

Now started  the database in MOUNT state to change the database to archivelog mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1006632960 bytes
Fixed Size                  3053104 bytes
Variable Size             905972176 bytes
Database Buffers           92274688 bytes
Redo Buffers                5332992 bytes
Database mounted.




SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Above we hit the error saying that database instance requires recovery.

Reason : As we did shutdown abort of our database instance or if we do STARTUP MOUNT FORCE then database instance gets crashed which requires media recovery to bring the database changes to a consistent state.

Resolution : Well, to resolve it, we would have to open our database to initiate instance recovery and shut it down normally or with immediate option.

SQL> alter database open;
Database altered.




When you open database, crash recovery starts as you can see in the alert log of the database instance as following:

<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Wed Jan 31 00:19:16 2018
alter database archivelog
ORA-265 signalled during: alter database archivelog...
alter database open
Wed Jan 31 00:21:49 2018
Ping without log force is disabled
.
Wed Jan 31 00:21:49 2018
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Wed Jan 31 00:21:49 2018
Started redo scan
Wed Jan 31 00:21:49 2018
Completed redo scan
 read 927 KB redo, 265 data blocks need recovery
Wed Jan 31 00:21:49 2018
Started redo application at
 Thread 1: logseq 211, block 66010
Wed Jan 31 00:21:50 2018
Recovery of Online Redo Log: Thread 1 Group 1 Seq 211 Reading mem 0
  Mem# 0: C:\APP\ORACLE\ORADATA\PROD1\REDO01.LOG
Wed Jan 31 00:21:50 2018
Completed redo application of 0.77MB
Wed Jan 31 00:21:50 2018
Completed crash recovery at
 Thread 1: logseq 211, block 67865, scn 6044843
 265 data blocks read, 265 data blocks written, 927 redo k-bytes read
Starting background process TMON
Wed Jan 31 00:21:50 2018
TMON started with pid=28, OS id=2456
Wed Jan 31 00:21:50 2018
Thread 1 advanced to log sequence 212 (thread open)
Thread 1 opened at log sequence 212
  Current log# 2 seq# 212 mem# 0: C:\APP\ORACLE\ORADATA\PROD1\REDO02.LOG
Successful open of redo thread 1
Wed Jan 31 00:21:50 2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jan 31 00:21:50 2018
SMON: enabling cache recovery
Wed Jan 31 00:21:50 2018
[564] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:137835812 end:137835984 diff:172 ms (0.2 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Wed Jan 31 00:21:50 2018
SMON: enabling tx recovery
Starting background process SMCO

You would want to save the current state of PDBs so that it starts automatically in read-write mode at next startup of CDB [click here]

Now, do a clean shutdown of your database instance:

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now, start the database instance in MOUNT state:

SQL> startup mount
ORACLE instance started.

Total System Global Area  845348864 bytes
Fixed Size                  1339796 bytes
Variable Size             499125868 bytes
Database Buffers          339738624 bytes
Redo Buffers                5144576 bytes
Database mounted.




Change the database mode to ARCHIVELOG:

SQL> alter database archivelog;
Database altered.




Hope it would help someone...!!