Archive
log destinations can be tricky when defining or viewing in Oracle Database. Stick to a few common
rules and never get lost.
Let’s begin with the basics. In the
last article, I ventured down the path of a default installation to show where
the destinations for archive logs where. In this article, as default
installations aren’t quite adequate, I'd like to progress through the various
different options available to uniquely identify these archive log destinations.
To get started, from the last
article, remember that Oracle placed archive logs in a default directory of
$ORACLE_HOME/dbs and, if flash recovery was enabled during installation, in the
USE_DB_RECOVERY_FILE_DEST, which equated to the init.ora parameter
db_recovery_file_dest. Using some very simple commands and SQL, our system
looked something like this.
From the use of the ARCHIVE LOG
LIST command:
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
From the use of the SHOW PARAMETER
command
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------ ----------- ------
db_recovery_file_dest string /opt/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
From a query against the
V$ARCHIVE_DEST view:
SQL> select dest_name,status,destination from V$ARCHIVE_DEST;
DEST_NAME STATUS DESTINATION
-------------------- --------- ----------------------------------------
LOG_ARCHIVE_DEST_1 VALID /opt/app/oracle/product/11.1.0/db_1/dbs/arch
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 VALID USE_DB_RECOVERY_FILE_DEST
10 rows selected.
To get things started, let’s first
comment out or remove the init.ora parameter for db_recovery_file_dest. This
will, if you were to look in the init.ora file, give us a system that has no
parameters defined for archive log destinations but has archive logs still going
to the default location of $ORACLE_HOME/dbs.
View article
Back to DBAsupport.com
