Archive in Oracle by Example. How to Change Archive destination, Change Archive mode.

============================================
Non Archive DB - view current status
============================================
SQL> SELECT name, log_mode FROM V$DATABASE;

NAME      LOG_MODE
--------- ------------
IGT       NOARCHIVELOG


SQL> show parameter log_archive
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      location=/oracle_db/db2/db_igt/arch
log_archive_duplex_dest              string
log_archive_format                   string      arch%T_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle_db/db2/db_igt/arch
Oldest online log sequence     15528
Current log sequence           15530


SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

============================================
Switch to Archive Log Destination
============================================
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle_db/db2/db_igt/arch' SCOPE=BOTH;

============================================
copy pfile
============================================

============================================
Switch to Archive mode
============================================
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;  [ALTER DATABASE NOARCHIVELOG;]
ALTER DATABASE OPEN;

============================================
Archive DB - view current status
============================================
SELECT name, log_mode FROM V$DATABASE;

NAME      LOG_MODE
--------- ------------
IGT       ARCHIVELOG

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      location=/oracle_db/db2/db_igt/arch
log_archive_duplex_dest              string
log_archive_format                   string      arch%T_%s_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_db/db2/db_igt/arch
Oldest online log sequence     56240
Next log sequence to archive   56242
Current log sequence           56242

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0


============================================
Additional Options - Change Archive Destination
============================================

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl/arch_old
Oldest online log sequence     25

Current log sequence           27

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/oracle_db/db2/db_igt/arch_new' scope=both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl/arch_new
Oldest online log sequence     25
Current log sequence           27

============================================
Additional Parameters
============================================

log_archive_local_first

possible values: true/false

Default is log_archive_local_first=true, which meant that Oracle MUST finish writing the redo log before it can be transmitted to a remote server using log transport services. (this feature does not apply to Streams where it can read the online redo logs...)

Prior to 10g, Oracle would synchronously write redo log (the ACRn background process) while Oracle was simultaneously transmitting (FTP) the redo logs to a remote server.  

To re-establish the synchronous redo log transmission, set log_archive_local_first=false.

Deprecated since Oracle 11

log_archive_start

Deprecated in Oracle 11

Default is TRUE

When set to false, there is no automatic archiving of redo files, and need to handle that manually.