LATEST TOPICS

Rollback or Restore DDL in Oracle database

Introduction:

There could be situations, where you as a DBA might have executed a wrong DDL for a particular database object or the developer might have written a wrong piece of code updating a database code incorrectly and want to revert the changes and restore the original definition for the particular object.

There also could be possibilities, where mistakenly someone has dropped a database object and you want to restore it.

There are different ways to restore the piece of objects to its original state, Like RMAN, DATAPUMP (Provided you have the backup available)

Here I would be using Oracle’s flashback query feature to restore a database object to its original state.

Prerequisites : Oracle Flashback Transaction Query must be enabled for the database

 

Demonstration:

Let’s create a trigger to demonstrate the scenario.

 SQL:labcp >create or replace trigger ST_PDBS after startup
 2 on database
 3 begin
 4 execute immediate 'alter pluggable database LABP open';
 5 end;
 6 /

Trigger created.

 

Now let us update the created trigger with a new definition ( Note: No backup was taken before updating the definition)

 SQL:labcp >create or replace trigger ST_PDBS after startup
 2 on database
 3 begin
 4 --- changed the execute statement
 5 execute immediate 'alter pluggable database all open';
 6 end;
 7 /

Trigger created.

 

Here, you realized that the new definition is not the correct one, and you want to restore the original definition.

 

Get the TIMESTAMP of the new definition for the object in question.

 SQL:labcp >select CREATED,LAST_DDL_TIME from dba_objects 
 2 where owner='SYS' and object_name='ST_PDBS';

CREATED              LAST_DDL_TIME
-------------------- --------------------
18-AUG-2014 23:00:51 18-AUG-2014 23:06:37

 

LAST_DDL_TIME is the time when the definition was last updated, which means we need to restore the object before the LAST_DDL_TIME

Now query DBA_SOURCE to obtain the definition for the required TIMESTAMP.

 SQL:labcp >select TEXT from dba_source
 2 as of timestamp
 3 to_timestamp('18-AUG-2014 23:05:00','DD-MON-YYYY HH24:MI:SS')
 4 where owner='SYS' and name='ST_PDBS' and type='TRIGGER';

TEXT
--------------------------------------------------------------------------------
trigger ST_PDBS after startup
on database
begin
execute immediate 'alter pluggable database LABP open';
end;

 

Now we can copy the definition and execute it to restore the object definition to it’s original state. (You need to append the CREATE or REPLACE word to the TEXT obtained from DBA_SOURCE)

 SQL:labcp >create or replace
 2 trigger ST_PDBS after startup
 3 on database
 4 begin
 5 execute immediate 'alter pluggable database LABP open';
 6 end;
 7 /

Trigger created.

 

Now let’s simulate dropping off the object and restoring it with flashback query.

 

SQL:labcp >drop trigger SYS.ST_PDBS;

Trigger dropped.

SQL:labcp >select CREATED,LAST_DDL_TIME from dba_objects 
 2 where owner='SYS' and object_name='ST_PDBS';

no rows selected

 

Now, assuming that I know the object was dropped from the database on 19-AUG-2014 20:20:00

Let’s query DBA_SOURCE to obtain the object definition for the dropped object.

 

SQL:labcp >select TEXT from dba_source
 2 as of timestamp
 3 to_timestamp('19-AUG-2014 20:18:00','DD-MON-YYYY HH24:MI:SS')
 4 where owner='SYS' and name='ST_PDBS' and type='TRIGGER';

TEXT
--------------------------------------------------------------------------------
trigger ST_PDBS after startup
on database
begin
execute immediate 'alter pluggable database LABP open';
end;

 

We can now recreate the dropped object with above definition

 

SQL:labcp >create or replace
 2 trigger ST_PDBS after startup
 3 on database
 4 begin
 5 execute immediate 'alter pluggable database LABP open';
 6 end;
 7 /

Trigger created.


SQL:labcp >select CREATED,LAST_DDL_TIME from dba_objects 
 2 where owner='SYS' and object_name='ST_PDBS';

CREATED              LAST_DDL_TIME
-------------------- --------------------
19-AUG-2014 20:24:10 19-AUG-2014 20:24:10

 

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed