How to enable hot backups – complete reference for the new oracle dba





As a new oracle DBA if you want to enable hotbackups or  in other words you want to use rman to backup the database while it is online ..then you have to set the database in archive log mode. Below article explains to you on how to make your database be in archive log mode.

ARCHIVELOG Mode:

We should always be running any important Oracle database in ARCHIVELOG mode, so that we can do hotbackups, etc. Turning on ARCHIVELOG mode is actually pretty easy. Here’s how I did it, 2002/06/17:

First edit our $ORACLE_BASE/admin/ora8/pfile/initora8.ora startup file. You should have lines like this:

log_archive_start = true

log_archive_dest_1 = "location=/ora8/m01/app/oracle/admin/ora8/arch"

log_archive_format = arch_%t_%s.arc

Note that in the log_archive_dest_1 = "location=/directory/file-pattern" above, pattern includes:

  • %S – log sequence number, zero-padded
  • %s – log sequence number, not padded
  • %T – thread # (OPS), zero-padded
  • %t – thread #, not padded

Then you need to shutdown the instance, switch to ARCHIVELOG mode, and then tell Oracle to start automatically archiving logs:

$ svrmgrl

SVRMGR> connect internal

SVRMGR> archive log list

SVRMGR> select name from v$archived_log;

SVRMGR> shutdown immediate;

SVRMGR> startup mount;

SVRMGR> alter database archivelog;

SVRMGR> archive log start;

SVRMGR> archive log list

SVRMGR> select name from v$archived_log;

SVRMGR> alter database open;

SVRMGR> shutdown immediate;

SVRMGR> startup;

In theory, Oracle should be ready to go after the ‘alter database open’ above. That last shutdown and startup was just to make me feel better. You could also skip the ‘alter database open’ and just do the shutdown and startup.

Also, here are some useful queries to show current online archive log related settings:

— Show archive log settings set from init.ora:

column name   format a30

column value  format a20

select

   v.name  ,v.value

  ,v.isses_modifiable  ,v.issys_modifiable

from v$parameter v

where v.name like ‘log_archive%’

order by v.name ;

— Archive log destination info:

column destination  format a40

select  dest_id  ,status  ,reopen_secs  ,destination

from v$archive_dest ;

See also these other useful Oracle views for displaying archived redo log information

Author: admin