how to verify if your Database is running archive log/ no archive log mode in Oracle


Login as sys user and run the below command to verify if database log mode is in archive mode or no archive mode.

SQL>archive log list;

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21

You might get the value as shown above and if you notice the “Database log mode” is in “No Archive Mode”, then you need to enable  it by running the below commands:

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL> alter database open;

To issue the SQL command to put the database in ARCHIVELOG mode, the database must be in MOUNTmode. If the database is currently open, you must shut it down cleanly (not abort), and then mount it.

With the database in NOARCHIVELOG mode (the default), recovery is possible only until the time of the last backup. All transactions made after that backup are lost.
In ARCHIVELOG mode, recovery is possible until the time of the last commit. Most production databases are operated in ARCHIVELOG mode.

To see the current archiving mode, query the V$DATABASE view:

SQL> select log_mode from sys.v$database;

LOG_MODE
————
ARCHIVELOG

Advertisements

Leave a comment

Filed under Oracle

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