ORA-01126: Database Must Be Mounted

When 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:

NOTE: 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;

Note: 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;

Performance testing in Oracle can be difficult when trying to compare results from two different environments.  One way to get a more accruate apple to apple comparison is to flush the data buffer cache. 

Note that this is for testing purposes only and should not be considered a recommendation in a live production system.

Oracle 9i and up:

alter session set events ‘immediate trace name flush_cache’;

Oracle 10g and up:

alter system flush buffer_cache;

You can also flush the shared pool of the SGA by using the command:

alter system flush shared_pool;