Flashback Drop Table

Flashback Drop Table:
When you issue the DROP TABLE command, Oracle merely renames the table and  moves it to a recycle bin.
The recycle bin is merely a data dictionary table that maintains information about dropped tables.
You can use the SELECT command to query the objects in the recycle bin. You can’t use INSERT, UPDATE, and DELETE commands on these objects.

You can view the contents of the recycle bin by using either the DBA_RECYCLEBIN or USER_RECYCLEBIN.

SQL> DROP TABLE EMP;

SQL> SELECT   OBJECT_NAME,    ORIGINAL_NAME,    OWNER,    TYPE ,    SPACE

FROM DBA_RECYCLEBIN;

OBJECT_NAME                                                             ORIGINAL_N              OWNER                      TYPE           SPACE

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

BIN$5j9tXNl6Tn+94FZ4H/ylyQ==$0               PK_EMP                     SCOTT                       INDEX            8

BIN$hJ1qy1xyRomz6c40oBqQvg==$0            EMP                          SCOTT                       TABLE           8

When we drop any table the associated object like also dropped. For example if we drop EMP table the index associated with emp also dropped.

Table are restore with the original name that reside before drop

INDEX also flashback but name not change

Constraint name different from index name

11:56:29 SQL> DROP TABLE SCOTT.E1;

Table dropped.

11:56:34 SQL> FLASHBACK TABLE SCOTT.E1 TO BEFORE DROP ;

Flashback complete.

OR

11:56:34 SQL> FLASHBACK TABLE SCOTT.E1 TO BEFORE DROP  RENAME TO E2;

Flashback complete.

Flashback Database

Flashback Database:

Flashback Database and Restore Points are two related data protection features of Oracle that provide more efficient alternatives to point-in-time recovery for reversing unwanted database changes

create a normal restore point:

SQL> CREATE RESTORE POINT before_truncate;

Restore point created.

OR
create a guaranteed restore point:

SQL> CREATE RESTORE POINT before_truncate GUARANTEE FLASHBACK DATABASE;

Restore point created.

Verify by Querying

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

Example

 A table is truncated after creation of restore point 

e.g          truncate table emp;

we will perform following step to bring table data back.

1.SHUTDOWN DATABASE

2.STARTUP MOUNT EXCLUSIVE

3.FLASHBACK DATABASE TO restore point before_truncate

To Drop Restore point:

DROP RESTORE POINT before_truncate

Enable Archive mode in oracle 10g

Noarchive Mode:

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

NOARCHIVELOG mode protects a database from instance failure but not from media failure.

In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups 

Archive Mode:

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

• A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.
• If you keep an archived log, you can use a backup taken while the database is open and in normal system use.
  
  
  Benifits:

1. We can able to take database backup ONLINE.
2. No Need to Down Oracle DB Server
3. We can better Manage our Backup Policy through RMAN
4. Limited DBA task
5. We can able to RECOVER our database point-in-time

to enable Archive mode:

1. shutdown immediate

2. startup mount 

3. alter database archivelog;

4. alter database open

optionaly you can set a destination for archiving else archive files will create in Flash_Recovery_Area

--for win   alter system set log_archive_dest_1='location=c:\youfolder';

--for linux alter system set log_archive_dest_1='location=/u01/app/archive';

you can set upto 10 destination (including flash recover area)

using parameter log_archive_dest_n       where n is from 1 to 10.

some views that can provide us with information reguarding archives, such as:

v$DATABASE

v$ARCHIVED_LOG

V$ARCHIVE_DEST

V$ARCHIVE_PROCESSES

v$BACKUP_REDOLOG

v$LOG

V$LOG_HISTORY