[Oracle] Setting ArchiveLog mode

Posted by cikul | Posted in Oracle, SQL | Posted on 25-09-2012-05-2008

2

What Is Archive Log?

As a DBA, you are responsible to recover any failures to a point of failure and also to perform your backup while online. Your organization is a 24×7 shop and you are not able to shutdown the database. Therefore, the database has to be in an archive mode.

For example you have severe corruption or a database crash that required you to restore some datafiles from several hours ago. If you have all the archived logs from that pint in time up until the moment of the crash, you can roll forward all the changes contained in those archived logs against the restored datafiles.

This process will replays all the changes in the database over the past hours. After recovering the last archived log, Oracle will then look to roll forward even more by using the online redo logs. If those online redo logs contain changes necessary, Oracle will apply those changes also.

Basically, you can recover from a serious error all the way up to just before the error occurred. Minimal data loss is the advantage here. you can’t do this when you’re not in archivelog mode, because all the changes over the past hours are lost because the redo logs just keep overwriting themselves and all the changes are lost between the time of your last backup and the time of the crash.

So if you don’t care if your database loses data, then run in noarchivelog mode. If you care about your data and don’t want to lose it, then run the database in archivelog mode.

Setting up ArchiveLog :

To see your database in ArchiveLog mode or not


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2702
Current log sequence           2704

From the result we can see that database set in NoArchiveLog mode, and now we want to set the database to ArchiveMode.

Please be aware for using USE_DB_RECOVERY_FILE_DEST as Archive destination, we suggest you never use this area for archive log file , system will hang if destination full.

to check DB_RECOVERY_FILE_DEST :


SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/FRA
db_recovery_file_dest_size           big integer 50G

To change destination :


SQL> alter system set log_archive_dest ='/u02/oracle/admin/arch';
alter system set log_archive_dest ='/u02/oracle/admin/arch'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

If you receive the error, it because DB_RECOVERY_FILE_DEST must be unset before you change archivelog destination :


SQL> alter system set DB_RECOVERY_FILE_DEST='';

System altered.

SQL> alter system set log_archive_dest ='/u02/oracle/admin/arch' scope=both;

System altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/oracle/admin/arch
Oldest online log sequence     2702
Current log sequence           2704

SQL> alter system switch logfile;

System altered.

From the result we have successfully changed archive destination, and now we can start to activate ArchiveLog, to start, we must shutdown the instance :

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> startup mount
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2026256 bytes
Variable Size             419431664 bytes
Database Buffers         3858759680 bytes
Redo Buffers               14749696 bytes
Database mounted.
SQL>
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>  archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/admin/arch
Oldest online log sequence     2703
Next log sequence to archive   2705
Current log sequence           2705

Now we have activate ArchiveLog mode, now we can start online backup.

If your Automatic Archival status Disabled, you can try this step


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /u1/oracle/9i/crmdbm/dbs/arch
Oldest online log sequence     15430
Next log sequence to archive   15431
Current log sequence           15432
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u1/oracle/9i/crmdbm/dbs/arch
Oldest online log sequence     15430
Next log sequence to archive   15432
Current log sequence           15432
SQL>

To Disable archivelog mode :


SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2026256 bytes
Variable Size             335545584 bytes
Database Buffers         3942645760 bytes
Redo Buffers               14749696 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/oracle/admin/arch
Oldest online log sequence     2785
Current log sequence           2787
SQL>

Share and Enjoy

Comments posted (2)

[…] function wpsrload(){ for(var i=0;i When I try to set archivelog in another machine using this guidance. I’ve got this error message […]

thanks, sedikit membantu coding yang saya lagi stuck :D

Write a comment

*