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
- Increase the archive log mode space (often needed on a batch processing system)
- 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.
Matt Yorke 