LGWR SYNC AFFIRM in Oracle Data Guard is used for zero data loss. How does one ensure zero data loss? Well, the redo block generated at the primary has to reach the standby across the network (that’s where the SYNC part comes in – i.e. it is a synchronous network call), and then the block…
http://www.oracle.com/technology/obe/11gr2_db_prod/ha/dataguard/dg_broker/dg_broker.htm http://www.oracle.com/technology/obe/11gr2_db_prod/ha/dataguard/activedg/activedg.htm http://www.oracle.com/technology/obe/11gr2_db_prod/ha/dataguard/physstby/physstdby.htm
goal: Does DBMS_JOB recompute the NEXT_DATE interval after or before executing the procedure or job? fact: PL/SQL fix: The NEXT_DATE is computed AFTER the job gets executed successfully. The INTERVAL date function is evaluated immediately before a job is executed. If the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If…
Read More “Does DBMS_JOB recompute the NEXT_DATE interval after or before” »
Committing Using Only the Transaction ID The following SQL statement commits an in-doubt transaction: COMMIT FORCE ‘transaction_id’; The variable transaction_id is the identifier of the transaction as specified in either the LOCAL_TRAN_ID or GLOBAL_TRAN_ID columns of the DBA_2PC_PENDING data dictionary view. For example, assume that you query DBA_2PC_PENDING and determine that LOCAL_TRAN_ID for a distributed…
Read More “Committing distributed transaction using commit force” »
FORCE Clause Specify FORCE to manually roll back an in-doubt distributed transaction. The transaction is identified by the text containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. A ROLLBACK statement with a FORCE clause rolls back only the specified transaction. Such a statement…
Check user$ table for same.
ALTER DATABASE commit TO switchover TO physical PRIMARY WITH session shutdown nowait;
In order to perform a switchover all sessions to the database need to be disconnected. In version 901 this was a manual process. In version 9.2.0 this process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command. If SWITCHOVER_STATUS returns SESSIONS ACTIVE then…
Read More “Physical Standby switchover with session active” »
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) “% COMPLETE” FROM V$SESSION_LONGOPS WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’ AND TOTALWORK != 0 AND SOFAR TOTALWORK /
set lines 120 pages 500 col owner format a30 col segment_name format a30 col tab_sum format 999999999999999 col indx_sum format 999999999999999 col MB format 999999999 accept pvm_owner prompt ‘Enter Owner Name -> ‘ accept pvm_table prompt ‘Enter Table Name -> ‘ select tab.owner, tab.segment_name, tab.tablespace_name, (tab_sum+indx_sum)/(1024*1024) MB from (select p.owner, p.segment_name, p.tablespace_name, sum(p.bytes) tab_sum from…
