Managing Archived Redo Logs

Managing Archived Redo Logs

Obtaining Information about Archive Log
-- in SQL*Plus
ARCHIVE LOG LIST
-- to know archivelog mode of the database
select LOG_MODE from V$DATABASE

-- historical archived log information from the control file
select
RECID,
NAME,
DEST_ID,
THREAD#,
SEQUENCE#,
RESETLOGS_CHANGE#,
RESETLOGS_TIME,
RESETLOGS_ID,
FIRST_CHANGE#,
FIRST_TIME,
NEXT_CHANGE#,
NEXT_TIME,
BLOCKS * BLOCK_SIZE/1024/1024 MB ,
CREATOR,
ARCHIVED,
DELETED,
STATUS,
COMPLETION_TIME,
Page 78 Oracle DBA Code Examples
END_OF_REDO,
BACKUP_COUNT,
ARCHIVAL_THREAD#,
IS_RECOVERY_DEST_FILE,
COMPRESSED,
FAL,
BACKED_BY_VSS
from V$ARCHIVED_LOG;

-- information about archive log destinations and their status
select * from V$ARCHIVE_DEST
-- information about ARCn processes
select * from V$ARCHIVE_PROCESSES
-- information about any backup made on archived log files
select * from V$BACKUP_REDOLOG
-- online groups and which one to be archived
select * from V$LOG
-- log history information
select * from V$LOG_HISTORY

Changing the Database Archiving Mode
select log_mode from v$database ;
CONN / AS SYSDBA
SHUTDOWN
Back up the database
see Specifying Archive Destinations ( next section )
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG; -- or NOARCHIVELOG
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE
Back up the database

Specifying Archive Destinations and their Options
-- to local destinations
LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/archive'
LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- default is OPTIONAL, REOPEN in seconds (default 300)
-- if REOPEN is omitted, ARCn will never open a destination after a failure
LOG_ARCHIVE_DEST_2 = 'LOCATION=/disk2/archive MANDATORY REOPEN=600'
LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/archive OPTIONAL'
-- to a standby db
LOG_ARCHIVE_DEST_4 = 'SERVICE = standby1'#

-- control file format
LOG_ARCHIVE_FORMAT=t%t_s%s_r%r.arc
Specifying the Minimum Number of Successful Destinations
alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=1
Controlling Archiving to a Destination
alter system set LOG_ARCHIVE_DEST_STATE_2 = DEFER
alter system set LOG_ARCHIVE_DEST_STATE_2 = ENABLE

Controlling Trace Output Generated by the Archivelog Process
LOG_ARCHIVE_TRACE takes combination of:
0 Disable archivelog tracing. This is the default.
1 Track archival of redo log file.
2 Track archival status for each archivelog destination.
4 Track archival operational phase.
8 Track archivelog destination activity.
16 Track detailed archivelog destination activity.
32 Track archivelog destination parameter modifications.
64 Track ARCn process state activity.
128 Track FAL (fetch archived log) server related activities.
256 Supported in a future release.
512 Tracks asynchronous LGWR activity.
1024 RFS physical client tracking.
2048 ARCn/RFS heartbeat tracking.
4096 Track real-time apply

-- LOG_ARCHIVE_TRACE defaults to 0
select value from v$parameter where upper(name)='LOG_ARCHIVE_TRACE';
-- database must be mounted but not open.
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=12;