How to check and change Oracle ArchiveLog Mode

Level: Beginner (DBA)

Question: How to check database ArchiveLog mode?

Answer:

To check the database archive log mode you need to
Step 1: Connect as SYSDBA

ex: connect sys/sysdbapass@server as sysdba

Step 2: Execute one of the following commands:
a) ARCHIVE LOG LIST

--In SQL*PLus
SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

b) Select from v$database

SQL> SELECT log_mode
  2  FROM sys.v$database
  3  /

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

Question: How to enable ArchiveLog mode?

You can specify the initial archiving mode in the CREATE DATABASE statement. Normally, there is no need to use this option as you can always change the mode later.

Answer:

Step 1: Connect as sysdba user

Step 2: Assuming that you are using spfile for parameter initialization, we need to configure the archive log destination. We will use a simple single destination option. All available options are described in the Oracle documentation.

SQL> ALTER SYSTEM SET log_archive_dest='F:\OraArchivedLogs' SCOPE=spfile;

System altered.

REM Note that this change will only work if db_recovery_file_dest is set to a blank, so you can execute another command just in case:
SQL> ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=spfile;

System altered.

Step 3: Shutdown the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4: Backup the database (Cold backup)
Step 5: Start a new instance and mount the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.

Step 6: Change the database archiving mode

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

Step 7: Open the database.

SQL> ALTER DATABASE OPEN;

Database altered.

Step 8: Shut down the database (see Step 3 above)

Step 9: Backup the database (see Step 4 above)

Step 10: Open the database:

SQL> STARTUP
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Next log sequence to archive   36300
Current log sequence           36300

Question: How to disable ArchiveLog mode?

Answer:

Step 1: Connect as sysdba user
Step 2: Shutdown the database
Step 3: Backup the database (Cold backup)
Step 4: Start a new instance and mount the database.
Step 5: Change the archivelog mode:

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

Step 6: Open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300
Advertisements

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