Archive log mode and how to enable it?

Archive log mode:-

Incase of disk failure also if we cannot afford to lose any data in your database then use Archive log mode.

Running a database in noarchive log mode:-

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

Running a database in archive log mode:-

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

·         A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

·         If you keep an archived log, you can use a backup taken while the database is open and in normal system use.

·         You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best.

SQL> archive log list          (it will show db log mode and sequence)

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     132

Current log sequence           133

Here the database is in no archivelog mode.we need to enable it.

Archive log mode takes the snap of redologfiles and those redolog files are kept into destination.

steps:-

1)First shut down the database.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Pr                                                                                     oduction.With the Partitioning, OLAP, Data Mining and Real Application Testing options.

2) [subbu@subbuapps ~]$ mkdir -p /u01/app/subbu/arch

created a folder arch to archive log files.

3)open pfile in location  cd $ORACLE_HOME/dbs

[subbu@subbuapps dbs]$ vi initsubbu.ora

add

log_archive_dest=/u01/app/subbu/arch

:wq

4)startup the database in mount mode

SQL> startup mount

ORACLE instance started.

Total System Global Area  732352512 bytes

Fixed Size                  1367144 bytes

Variable Size             369099672 bytes

Database Buffers          356515840 bytes

Redo Buffers                5369856 bytes

Database mounted.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     132

Current log sequence           133

SQL> alter database archive log;

alter database archive log

                       *

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     132

Next log sequence to archive   133

Current log sequence           133

SQL> select open_mode from v$database;

OPEN_MODE

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

MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE