Ramin Hossaini

Turning Archive-log mode on and off

Feb
16

You can check what mode the database is in with:

1
SELECT LOG_MODE FROM SYS.V$DATABASE;

If you do not specify an archive-log location, it will end up going to a directory like $ORACLE_HOME/dbs – which can be quite a mess.
Check where it’s going:

1
SHOW PARAMETER log_archive_dest;

It would be good practice to set the location if it doesn’t show one.
To turn archive-log-mode on or off:

shutdown database:

1
2
3
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
SHUTDOWN

Mount the database:

1
ALTER DATABASE MOUNT;

Then issue one of the following:

1
2
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE ARCHIVELOG;

If the location hasn’t been set yet:

1
ALTER SYSTEM SET LOG_ARCHIVE_DEST = "/ARC_LOCATION";

Then open the database:

1
ALTER DATABASE OPEN;

There might also be archivelog related entries in your PFILE, which you should also make changes to.

One Response to Turning Archive-log mode on and off

  1. Pingback: Backing up an Oracle database without RMAN | Ramin Hossaini (blog)

Leave a Reply

Your email address will not be published. Required fields are marked *