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?
#36: How to change an Oracle database to noarchivelog mode
Solved!on April 16, 2009, 07:09 PM UTC — from 
jo ( 38,178 views )
jo ( 38,178 views )
Restart database unmounted and alter.
- jo on April 16, 2009, 07:10 PM UTC
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.
% 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
Take database in no archivelog mode must in mount mode cause this write to datafile.
— Rori on February 26, 2013, 06:44 AM UTCconnect as sysdba
— Tapoban Raha on April 14, 2014, 02:21 PM UTCshutdown immediate;
startup mount;
alter database noarchivelog;
shutdown immdediate;
startup;
connect as sysdba
— ZloyBambr on November 14, 2014, 09:26 AM UTCshutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;
sir g how to switch database from mount mode to open mode without shutdown the database?
— faiqkhan on November 25, 2014, 04:45 AM UTCALTER database OPEN;
— Vishwas on July 07, 2016, 07:46 AM UTC