ORA-00257: Archiver error

If you’re new to Oracle Analytics Cloud and haven’t got an Oracle DBA helping you out – you’ll definitely come across this one pretty quickly.

The strikes fear into the heart of most applications consultants!!! The database has effectively hung – and nothing you do will clear it!

Oracle works by storing data in multiple places. There is the “heart” which is in the datafiles, exposed to the database through tablespaces. But in addition, it also creates a “journal” of changes. On a production database, this journal is used, combined with nightly snapshot backups to enable a replay to be done to restore the database to any point in time (snapshot + journals up to required time = backup)

However – if the journals are being written, but not cleared down by the backup manager, then it grows and grows until it runs out of space. When it runs out of space – the database hangs and you get the above error!

If it is a production database, there are 2 options

  1. Increase the archive log mode space (often needed on a batch processing system)
  2. Run an RMAN backup to backup and clear down the log files.

On a non production database, it is sometimes acceptable to not have archive log mode on. The below is one way to turn it off.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2768240640 bytes
Fixed Size                  2928248 bytes
Variable Size             704643464 bytes
Database Buffers         1979711488 bytes
Redo Buffers               80957440 bytes
Database mounted.

SQL>  ALTER DATABASE FLASHBACK OFF;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s