how do I restore indexes by using flashback?

All posts relating to Oracle database administration.

Moderator: Tim...

how do I restore indexes by using flashback?

Postby ssadoglu » Mon Apr 29, 2013 3:11 pm

Hi Tim,

We have a table with a primary key constraints and an associated indexes. My question is : Let's say , I've dropped accidentally this table. how do I restore primary constraints and indexes by using flashback? can you please explain me with an example?

2- When using 'delete obsolete' command , do I have to use 'crosscheck' command? So Does update as automatically oracle repository itself without this command? What do you recommended?

Thank in advance,
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: how do I restore indexes by using flashback?

Postby Tim... » Mon Apr 29, 2013 4:06 pm

Hi.

Answered in reverse order:

2) Any RMAN command leaves the repository in a consistent state, so you don't need to manually crosscheck. You only need to do that when you manually delete (at the OS) an item and need to make sure RMAN knows it is gone.

1) We create a test table.

Code: Select all
CREATE TABLE t1 (
  id NUMBER PRIMARY KEY
);

INSERT INTO t1 VALUES (1);
COMMIT;

COLUMN object_name FORMAT A30

SELECT object_type, object_name
FROM   user_objects;

OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
INDEX               SYS_C0012532
TABLE               T1

2 rows selected.

test@db11g>


We drop the table.

Code: Select all
DROP TABLE t1;

Table dropped.

test@db11g>

SELECT object_type, object_name
FROM   user_objects;

no rows selected

test@db11g>

show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
T1               BIN$AgV9ycSdSXWkQp0IC7ryHw==$0 TABLE        2013-04-29:17:00:29
test@db11g>


The table and index have gone, but we are left with the table in the recycle bin.

We flashback the table.

Code: Select all
FLASHBACK TABLE t1 TO BEFORE DROP;

SELECT object_type, object_name
FROM   user_objects;

OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
INDEX               BIN$trdr0OXUSUmyya1+YhgXEg==$0
TABLE               T1

2 rows selected.

test@db11g>


The index has reappeard, but with a new name.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: how do I restore indexes by using flashback?

Postby ssadoglu » Mon Apr 29, 2013 6:45 pm

Hi Tim,

thanks you very much.

Kind Regards,
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: how do I restore indexes by using flashback?

Postby Tim... » Mon Apr 29, 2013 6:47 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 6 guests