How to Recover a Table using Flashback

 

How to Recover a Table using Flashback

FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

 

SQL>  alter system set db_recovery_file_dest_size=10g;

System altered.

SQL> alter system set db_recovery_file_dest=' /u01/app/oracle';

System altered.

SQL> alter database flashback on;

Database altered.

SQL> show parameter db_recovery_file

NAME      TYPE  VALUE

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

db_recovery_file_dest      string   /u01/app/oracle

db_recovery_file_dest_size      big integer 10G

 

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

SQL> show parameter db_flashback_retention_target;

NAME      TYPE  VALUE

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

db_flashback_retention_target      integer  1440

SQL> alter system set db_flashback_retention_target=2800;

System altered.

SQL> show parameter db_flashback_retention_target;

NAME      TYPE  VALUE

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

db_flashback_retention_target      integer  2800

SQL> create restore point before_load guarantee flashback database;

Restore point created.

 

SQL> set time on;

 

02:09:20 SQL> select * from DBATEAM;

 

ID    NAME       DEPT

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

 1    INBA         CSE

 2    SAGA        ECE

 3    SHANKAR     EEE

 4    SIVA        CSE

5    BIJU        MECH

02:09:30 SQL> drop table DBATEAM purge;

drop table DBATEAM purge

           *

ERROR at line 1:

ORA-04045: errors during recompilation/revalidation of SYS.AW_DROP_TRG

ORA-01775: looping chain of synonyms

 

02:10:30 SQL> ALTER SYSTEM SET "_system_trig_enabled" = FALSE

System altered.

 

02:10:35 SQL> drop table DBATEAM purge;

 

Table dropped.

 

02:10:39 SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

02:11:16 SQL> startup mount

ORACLE instance started.

 

Total System Global Area 641728512 bytes

Fixed Size     8623976 bytes

Variable Size   427821208 bytes

Database Buffers   201326592 bytes

Redo Buffers     3956736 bytes

Database mounted.

 

02:11:38 SQL> flashback database to restore point before_load;

Flashback complete.

 

02:12:09 SQL> alter database open read only;

Database altered.

02:12:39 SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

 

02:13:33 SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

 

 

 

 

 

02:14:07 SQL> startup mount

ORACLE instance started.

Total System Global Area  641728512 bytes

Fixed Size     8623976 bytes

Variable Size   427821208 bytes

Database Buffers   201326592 bytes

Redo Buffers     3956736 bytes

Database mounted.

02:14:21 SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

02:14:32 SQL> alter database open resetlogs;

Database altered.

 

02:15:32 SQL> select * from DBATEAM;

 

ID    NAME       DEPT

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

 1    INBA         CSE

 2    SAGA        ECE

 3    SHANKAR     EEE

 4    SIVA        CSE

5    BIJU        MECH

 

 

RMAN Backup and Restore Status in Oracle Database

If you start long operations such as RMAN Backup and Datapump Export in Oracle database, probably you want to see progress of these operations.

RMAN is designed to complete a backup or restore as quikly and efficiently as possible. But if a job takes longer than expected to complete it can be confusing trying to determine:

· What precisely is RMAN doing at any particular time?

· Is RMAN hung or just slow?

· How far into the backup/restore has it got?

· How much more work is there to go?

 

Method:1

 

SQL> col STATUS format a9SQL> col hrs format 999.99SQL> select SESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILSorder by session_key;

 

 

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29             DB FULL       RUNNING   28/09/20 10:28  28/09/20 10:28      .00

SQL> /

 

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29             DB FULL       RUNNING   28/09/20 10:28  28/09/20 10:28     .01

 

SQL> /

 

SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS

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

 29            DB FULL       COMPLETED 28/09/20 10:28  28/09/20 10:29      .03

 

 

Method:2

 

session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set line 2222;

set pages 2222;

set long 6666;

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,

sysdate+(TIME_REMAINING/60/60/24) done_by

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sofar != totalwork

and totalwork > 0

/

 

Method:3

 

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;

 

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE

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

 18        29         1          9115569    19258880   47.33

 

Channel progress – v$session_longops

 only ACTIVE channels are reported

 check that % Complete is increasing

Session progress – CURRENT wait events and time in wait

Waits on ‘RMAN backup & recovery I/O‘ events are considered normal during backup and restore operations and indicate IO to disk.

Use this query to identify excessive waits on any Oracle or Media manager related resources.only CURRENT wait events are reported.

The session with NULL CH value is the RMAN client – this is always idle when the physical backup or restore is in progress.

 if SEQ# and EVENT do not change this is the same wait, check seconds in wait.

if EVENT does not change and SEQ# has increased this is a different wait on the same event.