V$ARCHIVE_DEST: Archived Redo Log Instance Destinations State



Share this article :

V$ARCHIVE_DEST view describes, for the current instance, all the archived redo log destinations, their current value, mode, and status.

DEST_ID

Log archive destination parameter identifier (1 to 10)

SQL> select DEST_ID from V$ARCHIVE_DEST;

   DEST_ID
----------
         1
         2
         3

3 rows selected.

DEST_NAME

Log archive destination parameter name

SQL> select DEST_NAME from V$ARCHIVE_DEST;

DEST_NAME
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_3

3 rows selected.

STATUS

Identifies the current status of the destination:

• VALID – Initialized and available

• INACTIVE – No destination information

• DEFERRED – Manually disabled by the user

• ERROR – Error during open or copy

• DISABLED – Disabled after error

• BAD PARAM – Parameter has errors

• ALTERNATE – Destination is in an alternate state

• FULL – Exceeded quota size for the destination

SQL> select STATUS from v$ARCHIVE_DEST;

STATUS
---------
VALID
INACTIVE
INACTIVE

3 rows selected.

BINDING

Specifies how failure will affect the archival operation:

• MANDATORY – Successful archival is required

• OPTIONAL – Successful archival is not required (depends on LOG_ARCHIVE_MIN_SUCCEED_DEST)

SQL> select BINDING from V$ARCHIVE_DEST;

BINDING
---------
MANDATORY
OPTIONAL
OPTIONAL

3 rows selected.

NAME_SPACE

Identifies the scope of parameter setting:

• SYSTEM – System definition

• SESSION – Session definition

SQL> select NAME_SPACE from V$ARCHIVE_DEST;

NAME_SP
-------
SYSTEM
SYSTEM
SYSTEM

3 rows selected.

TARGET

Specifies whether the archive destination is local or remote to the primary database:

• PRIMARY – local

• STANDBY – remote

SQL> select TARGET from V$ARCHIVE_DEST;

TARGET
-------
PRIMARY
PRIMARY
PRIMARY

3 rows selected.

ARCHIVER

Identifies the archiver process relative to the database where the query is issued:

• ARCn

• FOREGROUND

• LGWR

• RFS

SQL> select ARCHIVER from V$ARCHIVE_DEST;

ARCHIVER
----------
ARCH
ARCH
ARCH

3 rows selected.

SCHEDULE

Indicates whether the archival of this destination is INACTIVE, PENDING, ACTIVE, or LATENT

SQL> select SCHEDULE from V$ARCHIVE_DEST;

SCHEDULE
--------
ACTIVE
INACTIVE
INACTIVE

3 rows selected.

DESTINATION

Specifies the location where the archived redo logs are to be archived

SQL> select DESTINATION from V$ARCHIVE_DEST;

DESTINATION
--------------------------------------------------------------------------------
/oracle/SID/oraarch/SIDarch

3 rows selected.

LOG_SEQUENCE

Identifies the sequence number of the last archived redo log to be archived

SQL> select LOG_SEQUENCE from V$ARCHIVE_DEST;

LOG_SEQUENCE
------------
       16947
           0
           0

3 rows selected.

REOPEN_SECS

Identifies the retry time (in seconds) after error

SQL> select REOPEN_SECS from V$ARCHIVE_DEST;

REOPEN_SECS
-----------
          0
          0
          0

3 rows selected.

DELAY_MINS

Identifies the delay interval (in minutes) before the archived redo log is automatically applied to a standby database

SQL> select DELAY_MINS from V$ARCHIVE_DEST;

DELAY_MINS
----------
         0
         0
         0

3 rows selected.

NET_TIMEOUT

Number of seconds the log writer process will wait for status from the network server of a network operation issued by the log writer process

SQL> select NET_TIMEOUT from V$ARCHIVE_DEST;

NET_TIMEOUT
-----------
          0
          0
          0

3 rows selected.

PROCESS

Identifies the archiver process relative to the primary database, even if the query is issued on the standby database:

• ARCn

• FOREGROUND

• LGWR

SQL> select PROCESS from V$ARCHIVE_DEST;

PROCESS
----------
ARCH
ARCH
ARCH

3 rows selected.

REGISTER

Indicates whether the archived redo log is registered in the remote destination control file (YES) or not (NO). If the archived redo log is registered, it is available to log apply services.

SQL> select REGISTER from V$ARCHIVE_DEST;

REG
---
NO
NO
NO

3 rows selected.

FAIL_DATE

Date and time of last error

SQL> select FAIL_DATE from V$ARCHIVE_DEST;

FAIL_DATE
---------------

3 rows selected.

FAIL_SEQUENCE

Sequence number of the archived redo log being archived when the last error occurred

SQL> select FAIL_SEQUENCE from V$ARCHIVE_DEST;

FAIL_SEQUENCE
-------------
            0
            0
            0

3 rows selected.

FAIL_BLOCK

Block number of the archived redo log being archived when the last error occurred

FAILURE_COUNT

Current number of contiguous archival operation failures that have occurred for the destination

MAX_FAILURE

Allows you to control the number of times log transport services will attempt to reestablish communication and resume archival operations with a failed destination

ERROR

Displays the error text

ALTERNATE

Alternate destination, if any

DEPENDENCY

Dependent archive destination, if any

REMOTE_TEMPLATE

Specifies the template to be used to derive the location to be recorded

QUOTA_SIZE

Destination quotas, expressed in bytes

QUOTA_USED

Size of all the archived redo logs currently residing on the specified destination

MOUNTID

Instance mount identifier

TRANSMIT_MODE

Specifies network transmission mode:

• SYNC=PARALLEL

• SYNC=NOPARALLEL

• ASYNC

ASYNC_BLOCKS

Number of blocks specified for the ASYNC attribute

AFFIRM

Specifies disk I/O mode

TYPE

Indicates whether the archived log destination definition is PUBLIC or PRIVATE. Only PUBLIC destinations can be modified at runtime using the ALTER SYSTEM SET or ALTER SESSION SET statements. By default, all archived log destinations are PUBLIC.

VALID_NOW

Indicates whether the destination is valid right now for archival operations:

• YES – Redo log type and database role for this destination are valid for the current database

• WRONG VALID_TYPE – Redo log type specified for this destination is not valid for the current database role. For example, WRONG VALID_TYPE would be returned if a destination specified with the VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) attribute is running in the standby database role but does not have standby redo logs implemented.

• WRONG VALID_ROLE – Database role specified for this destination is not the role in which the database is currently running. For example, the WRONG VALID_ROLE would be returned when a destination defined with the VALID_FOR=(ONLINE_LOGFILE,STANDBY_ROLE) attribute is running in the primary database role.

• INACTIVE – Destination is inactive, probably due to an error

VALID_TYPE

Redo log type or types that are valid for the destination:

• ONLINE_LOGFILE

• STANDBY_LOGFILE

• ALL_LOGFILES

VALID_ROLE

Database role or roles that are valid for the destination:

• PRIMARY_ROLE

• STANDBY_ROLE

• ALL_ROLES

DB_UNIQUE_NAME

Unique database name

VERIFY

– Indicates whether the value of the VERIFY attribute on the LOG_ARCHIVE_DEST_n parameter is verified (YES) or not verified (NO)

Related posts

Oracle Shutdown Command Modes
ORA-32001: write to SPFILE requested but no SPFILE is in use
ORA-16038: log xx sequence# xxxxx cannot be archived
Query All Tables in Oracle
© 2017 ITsiti. All Rights Reserved
Powered by KEEM