Wednesday 1 March 2017

ALTERNATE archival destination in Oracle

Understanding Archive Destination Status

Each archive destination has the following variable characteristics that determine its status:
·         Valid/Invalid: indicates whether the disk location or service name information is specified and valid
·         Enabled/Disabled: indicates the availability state of the location and whether the database can use the destination
·         Active/Inactive: indicates whether there was a problem accessing the destination
Several combinations of these characteristics are possible. To obtain the current status and other information about each destination for an instance, query the V$ARCHIVE_DEST view.
The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 31) initialization parameter lets you control the availability state of the specified destination (n).
·         ENABLE indicates that the database can use the destination.
·         DEFER indicates that the location is temporarily disabled.
·         ALTERNATE indicates that the destination is an alternate. The availability state of an alternate destination is DEFER. If its parent destination fails, the availability state of the alternate becomes ENABLE. ALTERNATE cannot be specified for destinations LOG_ARCHIVE_DEST_11 to LOG_ARCHIVE_DEST_31.

Specifying Alternate Destinations
If you want to specify that a location be an archive destination only in the event of a failure of another destination, you can make it an alternate destination. Both local and remote destinations can be alternates. The following example makes LOG_ARCHIVE_DEST_4 an alternate for LOG_ARCHIVE_DEST_3:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_4 = 'LOCATION=/disk4/arch';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3 = 'LOCATION=/disk3/arch
   ALTERNATE=LOG_ARCHIVE_DEST_4';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ALTERNATE;


SQL> SELECT dest_name, status, destination FROM v$archive_dest;

DEST_NAME               STATUS    DESTINATION
----------------------- --------- ----------------------------------------------
LOG_ARCHIVE_DEST_1      VALID     /disk1/arch
LOG_ARCHIVE_DEST_2      VALID     +RECOVERY
LOG_ARCHIVE_DEST_3      VALID     /disk3/arch

LOG_ARCHIVE_DEST_4      ALTERNATE /disk4/arch

doubt transactions in oracle

Query to check in doubt transactions...
=====================================

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'), STATE, MIXED FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          GLOBAL_TRAN_ID                           TO_CHAR(FAIL_TIME,'DD-MON-YYY STATE            MIX
---------------------- ---------------------------------------- ----------------------------- ---------------- ---
88.33.88299            abc.a930e3e9.36.36.71299             24-jan-2017 07:56:31          collecting       no
44.77.88441            abc.a930e3e9.33.40.68441             24-jan-2017 09:27:49          collecting       no
99.99.99919            abc.a930e3e9.27.44.59919             24-jan-2017 15:39:21          collecting       no

SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_ I DATABASE
---------------------- --- - ------------------------------
01.01.11299            in  N
33.44.55441            in  N
22.44.55919            in  N
88.33.88299            out N REM2CRM_HZ_PARTIES_DBLINK
44.77.88441            out N REM2CRM_HZ_PARTIES_DBLINK
99.99.99919            out N REM2CRM_HZ_PARTIES_DBLINK

6 rows selected.

SQL>

To clear the in doubt transactions...
=====================================
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('88.33.88299');
COMMIT;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('44.77.88441');
COMMIT;