Oracle DB: How To change to noarchivelog mode – ORA-01126

ORA-01126: Database Must Be Mounted

Smart Panda - DatabaseWhen trying to change an oracle database to no archive log mode you might receive an error: ORA-01126: database must be mounted  EXCLUSIVE and not open for this operation.

Turn Off Archive Logging:

The easiest way is to shutdown the database and then startup unmounted, then do the alters, you will want to do this as the main oracle administrator database user:

sqlplus / as sysdba

shutdown immediate;
startup nomount;
alter database mount;
alter database noarchivelog;
alter database open;

Smart Panda - IdeaNOTE: When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Thus, you have to shutdown the database to back it up, and when you recover the database you can only recover it to the point of the last backup. While this might be fine for a development environment, the management folks tend to frown when a weeks worth of current production data is lost forever.

Turn On Archive Logging:

To turn on archive logging, you can use the command:

alter database archivelog;

Smart Panda - IdeaNote: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common one Smart Panda Runs into is:  ORA-00257

Check Archive Logging Status:

To see the current status issue the following command:

archive log list;

When turning on archiving, if you need to use RMAN to do a backup, you need to actually have a log archive file, if you have just turned it on, there is no archive logs, so you will get an error.  You can force Oracle to generate a log archive using the following command from sql*plus:

alter system switch logfile;