Skip to main content

How to Use ALTERNATE archive destination

How to Use ALTERNATE archive destination

We can define an archive destination with value ‘ALTERNATE’, which will take over if primary destination is full. As per Oracle documentation “An archiving destination can have a maximum of one alternate destination specified. An alternate destination is used when the transmission of an online redo log from the primary site to the standby site fails”. In fact this is the concept of data guard, but can also be applied on standalone system.

SQL> show parameter recovery
NAME TYPE VALUE
------------------------- ----------- ------
db_recovery_file_dest string +fradg
SQL> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------- ----------- ------------------------------
log_archive_dest_1 string location=use_db_recovery_file_dest

SQL> show parameter log_archive_dest_state_1
NAME TYPE VALUE
-------------------------- ----------- -------
log_archive_dest_state_1 string enable

SQL> alter system set log_archive_dest_3=
'location=+testarch' scope=both;
SQL> alter system set log_archive_dest_state_3=
'ALTERNATE' scope=both;

Now change the primary location to reflect ‘ALTERNATE’ setting:
SQL> alter system set log_archive_dest_1=
'location=use_db_recovery_file_dest
noreopen alternate=log_archive_dest_3' scope=both;

Here we have to add ‘NOREOPEN’; otherwise it will not spill over to ‘ALTERNATE’ location. As per Oracle documentation – If archiving fails and the REOPEN attribute is specified with a value of zero (0), or NOREOPEN is specified, the Oracle database server attempts to archive online redo logs to the alternate destination on the next archival operation.
When archive logs are written to primary location

SQL>select dest_id, dest_name, status from
v$archive_dest_status where status <> 'INACTIVE';
DEST_ID DEST_NAME STATUS
---------- --------------------- ---------
 1 LOG_ARCHIVE_DEST_1 VALID
 2 LOG_ARCHIVE_DEST_3 UNKNOWN
When Primary location is full and archiver cannot write to it, first time it will throw following error stack but archiving request will to ‘ALTERNATE’ location. At this point of time LOG_ARCHIVE_DEST_1 will be ‘DISABLED’.

alter system archive log current
ERROR at line 1:
ORA-16038: log 2 sequence# 324 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'+DG1/primary/onlinelog/group_2.384.684585247'
ORA-00312: online log 2 thread 1:
'+RECODG/primary/onlinelog/ group_2.384.684585247'

SQL>select dest_id, dest_name, status from
v$archive_dest_status where status <> 'INACTIVE';

 DEST_ID DEST_NAME STATUS
 --------- ------------------ ---------
 1 LOG_ARCHIVE_DEST_1 DISABLED
 3 LOG_ARCHIVE_DEST_3 VALID

Once the space issue is resolved & we are ready to fallback to PRIMARY location

SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set log_archive_dest_state_3=alternate;
System altered.

SQL>select dest_id, dest_name, status from
v$archive_dest_status where status <> 'INACTIVE';
DEST_ID DEST_NAME STATUS
---------- --------------------- ---------
 1 LOG_ARCHIVE_DEST_1 VALID

 2 LOG_ARCHIVE_DEST_3 UNKNOWN

Comments

Post a Comment

Popular posts from this blog

Tablespace Management using Toad

You can view all of your tablespace information using toad tablespace screen from Database –> Administer –> Tablespaces Here each table describes different detailed information across all the tablespaces in database. If you open this screen from the toad DBA module then you are also able to see the “space history” and “IO history” tab along with Files, Free space, object, Fragmentation. With these tab you are able to able perform space monitoring and planning steps. These tab permits you check graphically space and IO usage over time. Through this way DBA are also able to estimate ‘Object size’ based on this estimation. From the below “alter tablespace” tab you can §          Modify the tablespace and datafile size. §          Add new datafile to the tablespace.
Post a Comment
Read more

Import Excel Data into Oracle using Oracle form Button

Import Excel Data into Oracle using Oracle form Button 1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger. BEGIN IF :System.Cursor_Block<>'GL_DAILY_COMPOUND_HEADER' THEN   Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE'); END IF; IF :System.Mode = 'NORMAL' AND :System.Record_Status IN ('NEW','INSERT') THEN   IMPORT_EXCEL_PROC;   ---Form procedure ELSE  MESSAGE('Import allowed only for new entry!!!');  MESSAGE('Import allowed only for new entry!!!'); END IF; END; 2. Now write the procedure "IMPORT_EXCEL_PROC" into the program unit. Don't forget to create required table and folder for procedure IMPORT_EXCEL_PROC PROCEDURE IMPORT_EXCEL_PROC IS application    OLE2.Obj_Type; workbooks      OLE2.Obj_Type; workbook       OLE2.Obj_Type; worksheets      OLE2.Obj_Type; worksheet       OLE2.Obj_Type; ce...
Post a Comment
Read more

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.  In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB. Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g). You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.  $sqlplus /nolog SQL> CONNECT / AS SYSDBA SQL> Select property_...
Post a Comment
Read more