Share the content if you found it is useful (You can share using 300 community websites) click "share" at the end of the post.

You are encouraged to leave a comment.








Friday, November 19, 2010

Flashback - 10g or Later

Today i need to post on flashback feature on oracle database (10g or later)

Version: 10.2.0.1

One of my friend needed step by step configuration of Flashback. Here we go..


Pre-Requisites:
1. Database should be on archivelog mode

Startup the database with mount option:
(Simple Example of the procedure)

connect as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 19 14:40:28 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 184553284 bytes
Database Buffers 411041792 bytes
Redo Buffers 7163904 bytes
Database mounted.

Enable the Flashback:

SQL> alter database flashback on;

Database altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1G;

System altered.

SQL> alter database open;


Now connect as a normal user who has default tablespace as someone other than SYSTEM

SQL> conn balaji/balaji
connected

SQL> create table test (id number(2), name varchar(30));

Table created.

SQL> insert into table test values (3, 'INDIA');
1 row inserted

SQL> insert into table test values(4,'SRILANKA');
1 row inserted

SQL> select * from test;

ID NAME
---------- --------------------
3 INDIA
4 SRILANKA

SQL> select tablespace_name from dba_segments where segment_name like 'TEST';

TABLESPACE_NAME
------------------------------
USERS

Note the user balaji has the default tablespace as USERS.

Drop the table test:

SQL> drop table test;

Table dropped.


SQL> select * from test;
ERROR at line 1:
ORA-00942: table or view does not exist


Using Flashback to get the table from recycle bin:

SQL> flashback table "TEST" to before drop;

Flashback complete.

SQL> select * from test;

ID NAME
---------- --------------------
3 INDIA
4 SRILANKA

SQL>

Points to remember:
Flashback option will not work for SYSTEM tablespace objects.

You will receive:
SQL> flashback table "TEST" to before drop;
flashback table "TEST" to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

This is not about SYS objects, this is about SYSTEM tablespace... I can create a sys.table (logged as sys) on USER tablespace and then recover it through flashback.

Recycle functionality is available only for non-system locally-managed tablespaces.

Hope it helps... Cheers!!!

No comments:

Related Posts Plugin for WordPress, Blogger...

Let us be Friends...

Share |

Popular Posts

Labels

11.5.1. to 11.5.5 Cloning Procedure. 11g 11i 11i and R12 11i Autoconfig 11i Cloning 12.1.1 SSL 2 Node RAC Adconfig.sh errors out adgennls.pl adop Advanced Replication Apache version Apex Apex for Oracle database Apex Installation Apex Installation on Oracle E Business Suite. 11i with Apex Apex on 11i Apex on Oracle 11i Apex402 apexins.sql APPL_TOP Character Application Express Application server Issue APPLSYS password Apps Password Asynchronous Replication Autoconfig Autoconfig Managed Beehive Bit of operating system Block block media corrupt BPEL Central Inventory Character Set Conversion Cloning of 11i Cloning of Oracle Applications Clusterware Concurrent CPU 2011 CPU 2012 April CPU 2012 January. CPU 2012 October CRS custom top Custom Top Creation Database . Database Query Dataguard DBMS_REPAIR Demantra . Domain Name E Business Suite Migration EBS 12.2 EPM exp/imp Oracle Applications Flashback table flows_020200 FND_TOP/resource FNDCPASS FNDCPUCF Forms server upgrade Forms startup FRM-92101 Fusion Middleware Hostname Hot Backup Hot cloning HotBackup Hotbackup Cloning of Oracle Apps hrglobal.drv. NLS Saudi Arabian HRMS hyperion Import and Export Oracle Applications Install Oracle Installation of R12 Installation Steps integration Oracle EBS Issues Database issues Oracle EBS Issues with Upgrade 12.1.1 Java Mission Control Java Upgrade java/sql/SavePoint JInitiator Junk Characters Language Translation Linux Linux Migration listener trace Listner version Local Inventory Login Page Master-Master Replication Memory Tuning Multiple Language NLS Non-Autoconfig Enabled OBIEE 11g Installation OBIEE Issues OIM Opatch version OpenSSL ora-01031 Oracle oracle 11g Oracle Announcement Oracle Application servers Oracle Application services Oracle Applications Oracle Applications 11i oracle applications 12.2 Oracle Applications Cloning Oracle Applications Upgrade Oracle Applications. Oracle Apps cloning Oracle BPEL oracle certifications oracle database Oracle Database RMAN Oracle Database upgradation Oracle DB oracle E Business suite 12.2 Oracle EBS oracle EPM Oracle Forms Logo oracle Fusion Applications Oracle Inventory oracle news Oracle OUI Oracle R12 oracle Sun Solaris Oracle Tuning Oracle Virtual Box Oracle Webgate Oracle WMA configuration orcladmin password for Oracle Applications Pasta Configuration pasta.cfg patch PDF Performance Physical Standby Platform migration Printer Configuration Profile Option R12 RAC Rapid Install Rapidwiz Real Application Clusters 10g Recovery RMAN RMAN Recovery SA Gosi Service Oriented Architecture SOA SQLTXPLAIN SSL Configuration step-by-step upgrade Sun solaris Swap Tablespace Trace Enable Trace file Translation Synchronization Patch transport tablespace tuning Tuning SGA uifont.ali Underscore Universal Content Management updates.oracle.com Upgradation of 12.0.5 to 12.1.1 Upgrade Upgrade 11g Upgrade Apps Upgrade DB Upgrade to 11g UTF8 version of oracle applications versions Web server upgrade weblogic windows workflow version XML Publisher version