Where is my PL/SQL? – restore and recovery of PL/SQL code

Sometime it can happen to lose PL/SQL code from the database. What’s the reason behind is not important. The only important thing is to recover it as soon as possible.

Fortunately Oracle is storing the PL/SQL code in tables, that makes the recovery quite easy. For this I’m using two techniques, which I’m gonna cover in here.

FLASHBACK QUERY
With flashback query /AS OF/ we can query the data as it was at earlier stage. Keep in mind that SELECT AS OF is using undo data, so it will work till we have the appropriate undo data.

The whole idea is to query the all_source with AS OF clause. In this way we will be able to recover the previous version of our PL/SQL.

For example, here it is how it looks my current version of my SAVE_ME procedure:

SQL> select TEXT from all_source where NAME='SAVE_ME';

TEXT
--------------------------------------------------------------------------------
procedure save_me as
begin
--version 1
null;
end;

SQL>

Now I’ll replace the procedure and check all_source again:

SQL> create or replace procedure save_me as
begin
--version 2
null;
end;
/

Procedure created.

SQL> select TEXT from all_source where NAME='SAVE_ME';

TEXT
--------------------------------------------------------------------------------
procedure save_me as
begin
--version 2
null;
end;

SQL>

And finally I’ll review the previous version of my procedure and I’ll execute the same query form user sys, but this time with AS OF clause:

SQL> select TEXT from all_source AS OF TIMESTAMP TO_TIMESTAMP('01-06-2015 15:40:00', 'DD-MM-YYYY HH24:MI:SS') where NAME='SAVE_ME' ;

TEXT
--------------------------------------------------------------------------------
procedure save_me as
begin
--version 1
null;
end;

SQL>

Ok, that was quite simple. But what if the undo data is already gone?


LOG MINER

If we are in archivelog mode we can use log miner to retrieve the changes made on source$ table.
I know that my procedure was changed after 18:10, but before 18:15, so I need to mine the archive logs created between 18:10 and 18:15.

Mining those logs is simple, we just need to specify start and end time to the log miner:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

Session altered.

SQL> BEGIN
DBMS_LOGMNR.START_LOGMNR(
STARTTIME => '01-06-2015 18:10:00',
ENDTIME => '01-06-2015 18:15:00',
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/ 

PL/SQL procedure successfully completed.

SQL>

The next step is to query V$LOGMNR_CONTENTS or more particularity to retrieve the contents in the SQL_UNDO column.

First we need to determine the object ID of our procedure. If the procedure is just replaced we can take it directly from dba_objects:

SQL> select OBJECT_ID from dba_objects where OBJECT_NAME='SAVE_ME';

 OBJECT_ID
----------
     89885

SQL>

Otherwise we can mine it:

SQL> select sql_undo from V$LOGMNR_CONTENTS where sql_undo like '%save_me%';

SQL_UNDO
--------------------------------------------------------------------------------
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('89885','1','procedu
re save_me as
');

delete from "SYS"."SOURCE$" where "OBJ#" = '89885' and "LINE" = '1' and "SOURCE"
 = 'procedure save_me as
' and ROWID = 'AAAADgAABAAAWXjAAA';


SQL>

After we have the object ID – 89885 we can mine with an appropriate filter:

SQL> select sql_undo from V$LOGMNR_CONTENTS where sql_undo like 'insert into "SYS"."SOURCE$"%89885%';

SQL_UNDO
------------------------------------------------------------------------------------------------------------
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('89885','1','procedure save_me as
');

insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('89885','2','begin
');

insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('89885','3','--version 1
');

insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('89885','4','null;
');

insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('89885','5','end;');

SQL>

The last step is to clean the output. We can make this in many ways, for example we can create a table with the same structure, replace the “SYS”.”SOURCE$” in the output and then insert the values in our new table. After we have the source in there, we can simply select it. Anyway, as I said cleaning/formatting can be done in many ways. Last time I used Notepad++ and regexp for example.

Hope it helps,
Kovachev

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s