Archive log

Switching from noarchive to archivelog mode

The database is currently in noarchivelog mode:

Command:-

SQL> select log_mode from v$database;

LOG_MODE

------------

NOARCHIVELOG

SQL> show parameter log_archive_start

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_start boolean FALSE

Now, trying to put the db in archivelog: (This command will throw error)

Command:-

SQL> alter database archivelog;

alter database archivelog

*

ERROR at line 1:

ORA-01126: database must be mounted EXCLUSIVE and not open for this operation

Now perform Following Steps to put Database in Archivelog mode.

1.Connect to database as an sysdba

Command:-

sqlplus "/ as sysdba"

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

2.If the database is open, shut it down:

Command:-

SQL> SHUTDOWN

3. Mount the database:

Command:-

SQL> STARTUP MOUNT

4. Enter the following command:

SQL> ARCHIVE LOG LIST

The following output indicates the database is not in archive mode:

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination %RDBMS%\

Oldest online log sequence 34

Current log sequence 37

5. Change the archive mode to ARCHIVELOG:

Command:-

SQL> ALTER DATABASE ARCHIVELOG;

6. Enter the following command:

SQL> ARCHIVE LOG LIST

The following output indicates the database is now in archive mode:

Database log mode Archive Mode

Automatic archival Disabled

Archive destination %RDBMS%\

Oldest online log sequence 34

Current log sequence 37

7. Open the database:

Command:-

SQL> ALTER DATABASE OPEN;

8. Switch logfile to verfify archiving is happening at Archive destination.

Command:-

SQL> ALTER SYSTEM SWITCH LOGFILE;

Enable Automatic Archiving

Enable Automatic Archiving at the time of database creation or later by specifing the initial parameters.

1. Open the ORACLE_BASE\ADMIN\ db_name\pfile\init.ora file.

2. Find the following three initialization parameters and update as follows:

#LOG_ARCHIVE_START = true

#LOG_ARCHIVE_DEST_1 = %ORACLE_HOME%\database\archive

#LOG_ARCHIVE_FORMAT = "%%ORACLE_SID%%T%TS%S.ARC"

3. Remove the # sign from in front of each.

4. Edit the LOG_ARCHIVE_DEST_ n value to identify an existing drive and

directory in which to archive your filled redo logs.

5. Edit the LOG_ARCHIVE_FORMAT value to indicate the appropriate archiving format

6. Save your changes.

7. Exit the file.

8. Shut down the database:

SQL> SHUTDOWN

9. Restart the database

SQL> STARTUP

10. Archiving is enable in automatic mode

Command:-

SQL> ARCHIVE LOG LIST

********************************************************

Archive Log Format Description Example

%%ORACLE_SID%%%T.ARC Specifies the thread number.

This number is padded to the left by zeroes.

The default value is one with a range of up to three characters.

SID0001.ARC

%%ORACLE_SID%%%S.ARC Specifies the log sequence number.

This number is padded to the left by zeroes.

The default value is one with a range of up to five characters.

SID0001.ARC

%%ORACLE_SID%%%t.ARC Specifies the thread number.

The number is not padded.

The default value is one with no range limit on characters.

SID1.ARC

%%ORACLE_SID%%%sARC Specifies the log sequence number.

The number is not padded.

The default value is one with no range limit on characters.

SID1.ARC