Recover a table from accidently drop using flash back

 There are different technique to recover a table from accidently dropped. We have to choose the best possible option based on situation and DB configuration. However recover a table from Recycle bin in always a preferable option.

Here are some example:

1. Recover table from Recycle Bin

2. Recover Table using Flashback Database feature

3. Recover table from Undo tablespace  using FlashBack  table .

4.Recover the table by using Database Point in time recovery.

5.Recover the table by clone the  source database to a auxiliary(Test) database and Export/Import the table to Source database.

6. Using Log Mining technology

However in  below we will discussed only Procedure   3.Recover table from Undo tablespace  using FlashBack  table

The FLASHBACK TABLE statement enables users to recover a table to a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application.

Restores all data in a specified table to a previous point in time described by a timestamp or SCN. An exclusive DML lock is held on a table while it is being  restored.

Automatically restores all of the table attributes, such as indexes, triggers, and  the likes that are necessary for an application to function with the flashed back  table.

Maintains any remote state in a distributed environment. For example, all of the  table modifications required by replication if a replicated table is flashed back.

Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK

TABLE statement.

Even after a flashback, the data in the original table is not lost. You can later revert to the original state.

Prerequisite to Use FlashBack Table feature:

1. Automatic undo management must be enable to  use the

flashback table feature. It is based on undo information stored in an

undo tablespace.

2. To use the FLASHBACK TABLE statement you must have been granted the

FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object

privilege on the table.

3.You must have SELECT, INSERT, DELETE, and

UPDATE privileges on the table.

4.The table that you are performing the flashback

operation on must have row movement enabled.

Performs the restore operation online:

Change the undo tablespace to retention gurantee immediately after you encountered the issue so

the unexpired segment are not  modified by any existing DML

SQL>alter tablespace UNDOTBS1 retention guarantee;

SQL>select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME                RETENTION

------------------------------ -----------

SYSTEM                         NOT APPLY

UNDOTBS1                       GUARANTEE

SYSAUX                         NOT APPLY

TEMP                           NOT APPLY

USERS                          NOT APPLY

7 rows selected.

SQL> ALTER TABLE Test_table enable row movement;

Table altered.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

         1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

SQL> select current_scn from v$database;

CURRENT_SCN

----------------

332348

SQL> connect scott/tiger

Connected.

SQL> insert into Test_table values(5,'DINESH');

1 row created.

SQL> insert into Test_table values(6,'JAI');

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba

Connected.

SQL> select current_scn from v$database;

CURRENT_SCN

----------------

332376

SQL> connect scott/tiger

Connected.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

         1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

         5 Dinesh

         6 Jai

6 rows selected.

Now Flash back the table to old SCN or timestamp

SQL> flashback table Test_table to scn 332348;

Flashback complete.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

         1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

SQL> flashback table Test_table to scn 332376;

Flashback complete.

SQL> select * from Test_table;

     EMPNO EMPNAME

---------- ------------------------------

          1 Bhaskar

         2 Naveen

         3 Rajesh

         4 Suresh

         5 Dinesh

         6 Jai

6 rows selected.

ORA-01591: lock held by in-doubt distributed transaction

This Error not allow application sql query to run on the table that hold lock through distributed transaction.

Application team may hit below error while try to execute any sql on lock table.

 DB00153):  Get row failed for db statement.
 (DB10032):  Failed executing SQL.
 (DB10032):  Oracle Error: ORA-01591: lock held by in-doubt distributed transaction 9.6.339635
 (DB10032):  SQL: select ltrim(rtrim(V.ACCOUNT_NBR)) as ACCOUNT_NBR ,V.ACCOUNT_ID,V.OTHER_PAYMENT_AMOUNT,V.BANK_ACCOUNT_ID,V.PAYMENT_AMOUNT_TYPE_ID,V.BANK_ACCOUNT_TYPE_CD,V.BANK_ACCOUNT_NBR,V.BANK_ROUTING_NBR from v_autopay V inner join purgatory_autopay_enrollment PAE on V.account_id = PAE.account_id where PAE.AUTOPAY_ENROLLMENT_STATUS = '2' and to_char(V.NEXT_CYCLE_DT,'YYYYMMDD') between (to_char((SYSDATE -5), 'YYYYMMDD')) and (to_char((SYSDATE-1 ), 'YYYYMMDD'))
 (DB10032):  Parse Error Offset: 0
 (DB10034):  ORA-01591: lock held by in-doubt distributed transaction 9.6.339635
 (*):        Database Package Version 3-2-6-2-e11-1

Before moving to solution what is distributed transaction: 

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, assume the database configuration depicted in Figure :

Figure  : Distributed System

The following distributed transaction executed by scott updates the local sales database, the remote hq database, and the remote maint database:

UPDATE scott.dept@hq.us.acme.com

  SET loc = 'REDWOOD SHORES'

  WHERE deptno = 10;

UPDATE scott.emp

  SET deptno = 11

  WHERE deptno = 10;

UPDATE scott.bldg@maint.us.acme.com

  SET room = 1225

  WHERE room = 1163;

COMMIT;

You can execute DML and DDL statements in parallel, and INSERT direct load statements serially, but note the following restrictions:

•           All remote operations must be SELECT statements.

•           These statements must not be clauses in another distributed transaction.

•           If the table referenced in the table_expression_clause of an INSERT, UPDATE, or DELETE     statement is remote, then execution is serial rather than parallel.

•           You cannot perform remote operations after issuing parallel DML/DDL or direct load INSERT.

•           If the transaction begins using XA or OCI, it executes serially.

•           No loopback operations can be performed on the transaction originating the parallel operation. For example, you cannot reference a remote object that is actually a synonym for a local object.

•           If you perform a distributed operation other than a SELECT in the transaction, no DML is parallelized.

In-Doubt Transactions :

The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if any of the three phases fails because of a system or network error? The transaction becomes in-doubt.

Distributed transactions can become in-doubt in the following ways:

•           A server machine running Oracle software crashes

•           A network connection between two or more Oracle databases involved in distributed processing is disconnected

•           An unhandled software error occurs

•           If RECO process is not running or terminated

The RECO process automatically resolves in-doubt transactions when the machine, network, or software problem is resolved. Until RECO can resolve the transaction, the data is locked for both reads and writes. Oracle blocks reads because it cannot determine which version of the data to display for a query.

Manual Resolution of In-Doubt Transactions :

You should only need to resolve an in-doubt transaction in the following cases:

•           The in-doubt transaction has locks on critical data or rollback segments.

•           The cause of the machine, network, or software failure cannot be repaired quickly.

Resolution of in-doubt transactions can be complicated. The procedure requires that you do the following:

•           Identify the transaction identification number for the in-doubt transaction.

•           Query the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views to determine whether the databases involved in the transaction have committed.

•           If necessary, force a commit using the COMMIT FORCE statement or a rollback using the ROLLBACK FORCEstatement.

To find out more details use below views: 

select * from dba_2pc_pending;

select * from dba_2pc_neighbors;

select * from sys.pending_trans$;

select * from sys.pending_sessions$;

select * from sys.pending_sub_sessions$;

select * from dba_pending_transactions;

Here are some codes to help you through the process:

This one brings in-doubt transactions details:

select * from DBA_2PC_PENDING where state='prepared';

 Below  one prepares the rollback script for the transactions:

select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

 At last run the  below script to purge the force /rollback  transaction with the transaction id:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<LOCAL_TRAN_ID');

Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txns001.htm#ADMIN12213 

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode During RMAN COLD BACKUP

Complete error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c1 channel at 11/21/2016 17:30:42
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Cause: Database was shut down with shut down abort option or due to internal/External error or any user error or hardware error.

There are datafile file that are not in consistent state..
A COLD backup for a database running in NOARCHIVELOG mode has to be a CONSISTENT backup as it cannot be recovered after restore.


To Confirm :

 SQL> select distinct checkpoint_change# from v$datafile_header;

If this will return >1 row, indicating that files are at different points in time.

or we can also check which datafile file need more recovery.

     Select * from v$recover_file ;

  no rows selected ,means no file is waiting for recovery. If any files shown in output need to go for standard datafile recovery. 

Check for   if the datafiles are in Fuzzy status

What is Fuzzy:

FUZZY bit in datafile header means that there may have been writes into a
datafile after the last checkpoint. E.g. there may be changes written to
datafile with higher SCN than checkpoint_change# stored in datafile header
(seen from v$datafile_header.checkpoint_change#).

So, whenever a datafile is opened it is checked for fuzzy bits.
If it is fuzzy then the database checks the checkpoint_change$
column to search the SCN from which the redo logs are to be applied.

Select status,file#,fuzzy,checkpoint_change# from v$datafile_header;

 STATUS       FILE# FUZ CHECKPOINT_CHANGE#
------- ---------- --- ------------------
ONLINE           1 YES            10234
ONLINE           2 YES            10234
ONLINE           3 YES            10234
ONLINE           4 YES            10234

YES value indicates files are fuzzy and need recovery.

check  redo log files(Current/Active shown q

uery 1) are available or not.

SQL>Select b.member,a.group#,a.sequence# from v$log a ,v$logfile b where a.group#=b.group#

If  redo logs are available go with recovery.

SQL>Recover database ;

     

Note:  No need to open the database with resetlogs option after recovery.

References: 

COLD RMAN backup fails: Ora-19602 even after SHUTDOWN IMMEDIATE and STARTUP MOUNT (Doc ID 373828.1)

 http://www.parnassusdata.com/en/node/569