Friday, November 2, 2012

0 Archive log mode

When database is in archive log mode. Oracle instance would make another copy of the log. The purpose for the archive log mode is to increase the availably. Noarchivelog mode is similar to ‘truncate log at check point” in Sybase or “Simple recovery mode” in MSSQL .Archivelog mode is similar to “Full recovery mode” in MSSQL.
In the archive mode, the database can be backup while it is open.
The database can be online database restore from full backup + online log + archive log ( in the open status) as long as SYSTEM table space and current undo table space are intact.

How to check the current mode for the database?

select log_mode from v$database;

image

archive log list 

image

How to enable the archive mode?

Before we enable the archive mode, we need to make sure below 3 parameters are properly set.

log_archive_format

Format for the archive log file.

 show parameter log_archive_format

image

log_archive_max_processes

Number of the Arch process working on archiving.

show parameter log_archive_max_processes

image

log_archive_dest

Define the location of the archive file store. It can be the directory path or use db_recovery_file_dest to store in the flash recovery area.

show parameter log_archive_dest

image

Below demo, set the archive file to directory path and flash recovery area

alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/oradata/orcl/archive/';
alter system set log_archive_dest_2='LOCATION=db_recovery_file_dest';

 

image

Update ( 2013/2/4): db_recovery_file_dest can not be used with log_archive_dest but can be used with log_archive_dest_n.  If we try to set the log_archive_dest  using flash back recovery area, we would get the  ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST error
.

image

 

The archive mode ONLY can be changed when database is not open.

select status from v$instance;
select log_mode from v$database;
shutdown immediate
startup mount
select status from v$instance;
select log_mode from v$database;

image

alter database archivelog;
select log_mode from v$database;
alter database open;

image

After enable the archive mode, we need to take the full database backup as the original backup under non archive mode can not be used.

Archive current log file

Below command archive the current online log ( sequence 602)

alter system archive log current;

image

 

How to disable the archive mode?

Only can change the database from archive mode to no archive mode when database is not open ( mount ).

select log_mode from v$database;
shutdown immediate
startup mount
alter database noarchivelog;
select log_mode from v$database;
alter database open;

SNAGHTML8e409ed

Since we switch the mode, the original database backup is no longer valid. We need to take the new database dump just in case.

shutdown immediate
exit

 

rman target / 
startup mount
backup database;
alter database open;
exit;

SNAGHTML8ea2ae5

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates