Friday 1 September 2017

Changing archivelog location:

Choose the FRA for archivelog location,

While creating database if we enabled archivelog without create FRA oracle placed archive logs in a default directory of $ORACLE_HOME/dbs

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            $ORACLE_HOME/dbs/archive
Oldest online log sequence     45
Next log sequence to archive   46
Current log sequence           46
SQL>

Suppose if we created FRA and then choose FRA for archive log location,

For example so many facing issues on arechivelog location because while executing command like below,

alter system set  log_archive_dest_1='LOCATION=DB_RECOVERY_FILE_DEST'

SQL> sho parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=DB_RECOVERY_FILE_DEST
log_archive_dest_10                  string
SQL>

It will not take FRA for archivelog location, it will use old location.

if we set location "LOCATION=USE_DB_RECOVERY_FILE_DEST" then only it will choose FRA for archivelog location.

alter system set  log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     42
Next log sequence to archive   44
Current log sequence           44


SQL> select destination from V$ARCHIVE_DEST;

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/DEV/fast_recovery_area
db_recovery_file_dest_size           big integer 6930M
recovery_parallelism                 integer     0
SQL>


If you want to change the different archivelog location:


SQL> alter system set log_archive_dest_1='LOCATION=/u20/oracle/data1';

System altered.

Best Regards

No comments:

Post a Comment