Thursday 27 November 2008

enabling/disabling ARCHIVELOG in Oracle 10g

to check your current mode:
SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

to enable:
SHUTDOWN;
STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

to disable:
SHUTDOWN;
STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

If you receive this error message:
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled

It means flashback database is set, we need to disable flashback first
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG