#36: How to change an Oracle database to noarchivelog mode

Solved!
What? Can't alter while sessions are open? Can't alter modes while database is closed? Problems I've encountered:

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01126: database must be mounted EXCLUSIVE and not open for this operation

SQL> alter database close;
alter database close
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

What to do?

Restart database unmounted and alter.

47
Become the oracle user. Then:

% sqlplus /nolog

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SP2-0640: Not connected
SQL> connect / as sysdba;
Connected.
SQL> shutdown immediate;
Database closed.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2141162968 bytes
Fixed Size 744920 bytes
Variable Size 1375731712 bytes
Database Buffers 738197504 bytes
Redo Buffers 26488832 bytes
SQL> alter database mount;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.




.... hurrah. it's generally a good idea to also make sure there are no other sessions running.

Comments

  1. Take database in no archivelog mode must in mount mode cause this write to datafile.

    Rori on February 26, 2013, 06:44 AM UTC
  2. connect as sysdba
    shutdown immediate;
    startup mount;
    alter database noarchivelog;
    shutdown immdediate;
    startup;

    Tapoban Raha on April 14, 2014, 02:21 PM UTC
  3. connect as sysdba
    shutdown immediate;
    startup mount;
    alter database noarchivelog;
    alter database open;

    ZloyBambr on November 14, 2014, 09:26 AM UTC
  4. sir g how to switch database from mount mode to open mode without shutdown the database?

    faiqkhan on November 25, 2014, 04:45 AM UTC
  5. ALTER database OPEN;

    Vishwas on July 07, 2016, 07:46 AM UTC

Think you've got a better solution? Help 3bd1a9d59a360e10be3d1a9f98b3c48b_small jo out by posting your solution