How to recover a dropped or overriden PL/SQL using Flashback.

Well, things happen. Just recently I've mistakenly run an older script that does "CREATE OR REPLACE PACKAGE ...". :(
And that happen the latest changes I've made using SQL*Developer have gone also. :(

So, what to do?
I could restore the entire DB to the point in time before the change.
But, I just was too lazy to do it. Also, in some cases such a global approach can eliminate other recent works. It was not mine case though, but you have to consider it.

Fortunately Oracle has a Flashback!

So I decided to try this. My plan was to execute following

SELECT * FROM DBA_SOURCE AS OF TIMESTAMP
TO_TIMESTAMP('2010-01-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE TEXT LIKE '%1.0.1.38%';

Here 1.0.1.38 is a version number in a comment line that I usually embed into my code.
And the older version of my PL/SQL, that had been written over is 1.0.1.37.
So, I needed my 1.0.1.38 back.
However the result returned by the above query always was the current package code with a version 1.0.1.37, which was not my target.

Then I've decided to try with querying the table directly. The table is SYS.SOURCE$.

SELECT * FROM SYS.SOURCE$ AS OF TIMESTAMP
TO_TIMESTAMP('2010-01-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE TEXT LIKE '%1.0.1.38%';

Voila! It was there!

Then I just took the proper OBJ# and spooled all rows of that object into a text file.
Ta-daa!

However, to be successful with this method, you have to be really quick.
Otherwise you can receive following error if some significant time has gone from the moment of overriding.

ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1192467665$" too small
01555. 00000 - "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause: rollback records needed by a reader for consistent read are
overwritten by other writers
*Action: If in Automatic Undo Management mode, increase undo_retention
setting. Otherwise, use larger rollback segments

In this case you have to resort to traditional recovery methods, like a point-in-time DB recovery or an import, or a Log Miner if archived logs for that time have not been removed.

Oracle Audit Vault 10g against 11g source.

When Oracle Audit Vault is being configured to collect audit data from Oracle 11g database, it can throw Java SQL exception originated from ORA-27452 when you add REDO Collector.
In a log file you can find the call of dbms_src_streams_collector.setup_collector(...av_dbname => 'AV.REGRESS.RDBMS.DEV.US.ORACLE.COM' ...)
The default global name of AV Server database (AV.REGRESS.RDBMS.DEV.US.ORACLE.COM) is longer than 30 characters, this probably causes an error ORA-27452 during adding of REDO Collector.
To work this issue around I had to rename the global name of AV Server DB to something shorter.

How to call Oracle stored procedure from MS SQL

It is really a pain if you need to call Oracle stored procedure from MSSQL.
MSSQL throws an error 7212 if remote procedure does not return any recordset. Oracle procedures usually don't return recordset.
One way was to create a dummy table, create trigger on it, from where stored proc is called. Then to update this table from MSSQL.

Matt Meleski has published really excellent way of calling oracle stored procedures from MS SQL! It does not require the trigger at all!
He's placed quite thorow example on his BLOG http://objectsharp.com/cs/blogs/matt/archive/2005/06/13/2221.aspx

By the way, the example can be simplified to very short code. Main thing - the Oracle stored procedure must have at least one output parameter type of indexed PL/SQL table. Indexed - it is important. Instead of creating a custom type in PL/SQL package, one can use readily available DBMS_OUTPUT.CHARARR.

Here is an example.

Make stored proc in Oracle:

create or replace procedure SCOTT.PROC4MS(OUTPUT OUT dbms_output.chararr) is
begin
-- you do not have to assign anything to OUTPUT if you don't need it.
OUTPUT(1):='one';
OUTPUT(2):='two';
OUTPUT(3):='three';
end;
/

Then call it from MS SQL. And here is the trick - use curly braces and a magic "resultset 25" parameter.

select * from openquery(MYORADB, '{CALL SCOTT.PROC4MS({resultset 25, OUTPUT})}');

Enjoy!

Other helpful links:
- How To Retrieve a Recordset from an Oracle Stored Procedure Using ADO on ASP http://support.microsoft.com/kb/229919

SSIS 2005. Fast load into non-MS OLE DB destination

or

SSIS 2005. How to avoid Autocommit when load into OLE DB destination.

An example with using of Oracle OLE DB driver on a destination side.

I have heard from my friend that he is experiencing very slow load into Oracle database. He used SSIS 2005. Which is funny, because Oracle Warehouse Builder is available. Anyway.
The cause of slowness of SSIS, when it loads into non-Microsoft OLE DB target, appeared to be the commit after every insert. Since the ‘Autocommit’ is set to be ‘ON’ by OLE DB standard, a driver propagates this mode to the database. The only way to turn it off, is to switch OLE DB datasource into a Transactional mode by issuing a BeginTransaction() call.

SSIS does issue this call when Batch Size option is specified for MS SQL OLE DB Native Sqlserver driver. But it does not do it for any other OLE DB driver like Oracle’s or even for MSSQL .Net OLE DB.

As a result, SSIS demonstrates very poor performance when loading into Oracle OLEDB Destination.

What to do?

I’ve suggested to start transaction in Destination OLE DB session before any insert comes, then do a Commit and start transaction again after some number of rows inserted in order to simulate “Batch size”.

How to do?

I would be curious to know if anybody have found a more elegant and efficient way to fix this situation, however my way is by using Script Component.

1. Create Any kind of Data Source, i.e. an OLE DB Source. It should have fields that can be easily put into a target table.
2. Add Script Component as a Destination and feed it from data source above.
3. Double-click on it to open Script Transformation Editor
   Select input columns

Select Connection Manager form where add New connection manager.

Rename new “Connection” to “oraxeConnection” just for distinction.

In Connection Manager Column choose “New Connection” item

Select “ADO.NET” type

In a dialog “Configure OLE DB Connection Manager” click New.

Choose Provider. In my simple case “.Net Providers for OleDb\Microsoft OLE DB Provider for SQL Server”

Test and click OK

Click OK

Choose newly created Connection Manager in “Connection Manager” column

Select “Script” item in left list

Click [Design Script…] button

Create following script

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.OleDb
Imports System.Data.Common

Public Class ScriptMain
Inherits UserComponent

Dim rowcnt As Int64
Dim out_batch_sz As Int64

Dim connMgr As IDTSConnectionManager90
Dim oledbconn As OleDbConnection
Dim oledbtran As OleDbTransaction
Dim oledbCmd As OleDbCommand
Dim oledbParam As OleDbParameter

Public Overrides Sub PreExecute()
out_batch_sz = 8 * 1024
rowcnt = 0
'MsgBox("PreExecute")

oledbCmd = New OleDbCommand("INSERT INTO MM_TST_B2(C, D) VALUES(?, ?)", oledbconn)
oledbParam = New OleDbParameter("@C", OleDbType.VarChar, 7)
oledbCmd.Parameters.Add(oledbParam)
oledbParam = New OleDbParameter("@D", OleDbType.VarChar, 7)
oledbCmd.Parameters.Add(oledbParam)

oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
'MsgBox("First Transaction began")
MyBase.PreExecute()
End Sub

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.oraxeConnection
oledbconn = CType(connMgr.AcquireConnection(Nothing), OleDb.OleDbConnection)
'MsgBox(oledbconn.Provider & " " & oledbconn.ConnectionString)
End Sub


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
With oledbCmd
.Parameters("@C").Value = Row.C
.Parameters("@D").Value = Row.D
.ExecuteNonQuery()
End With

rowcnt = rowcnt + 1
If (rowcnt Mod out_batch_sz) = 0 Then
MsgBox("Batch Commit " & rowcnt)
oledbtran.Commit()
oledbtran = oledbconn.BeginTransaction()
oledbCmd.Transaction = oledbtran
'MsgBox("Batch Transaction began")
End If
End Sub

Public Overrides Sub PostExecute()
'MsgBox("PostExecute")
MyBase.PostExecute()
End Sub

Public Overrides Sub ReleaseConnections()
'MsgBox("ReleaseConnections")
oledbtran.Commit()
MyBase.ReleaseConnections()
End Sub

Save and exit.
Run

Comparison of this Script Destination method with the basic OLE DB Destination, where both work via the same Oracle OLE DB Driver, shows that the Script method is times faster.

Restoration of RPM database.

This article may help you, if your RPM database has corrupted, and that’s what happened to mine. In fact, I tried to use `rpm --rebulddb' command on it in an attempt to fix it. Unfortunately, most of package’s meta-information had gone. However, all, or so I hoped, files remained on the filesystem. I have decided that this could be a my source of restoration of the RPM database.
Fortunately( to me :) ) other people have encountered this problem already, and some solutions exist. Here is the URL for this document: http://www.informatimago.com/linux/rpm-rebuilddb.html. This one actually worked for me as a starting point. It is based on comparison of actual files and rpm'ed files by names, deriving an rpm name where filenames match. It could have been a good solution, if there would be only one version of rpm packages. But in real life we have many. I have downloaded all packages from http://download.fedoralegacy.org/redhat/9/os/i386/ and http://download.fedoralegacy.org/redhat/9/updates/i386/. As a result, there were multiple versions of packages. Which one to apply? I have decided to use some more information about the file that will uniquely identify it. Sure it was MD5 check sum. The idea was to create two lists, speaking relation database language, tables, and then to join them.
Table DISK_CONTENT ( filename, bytesize, md5sum )
Table RPM_CONTENT ( rpmfullname, filename, bytesize, md5sum )

Then I planned to load the data into these tables for joining using SQL language. When joining, ideally, a package suggested to be previously installed if all of its files exist on the filesystem, and the sizes and MD5 checksums are identical. But, some files, like config files, can be modified during or after installation, and the size/md5 will not match. Thus I decided to assign a merit 0 (zero) to files that md5 match, merit 1 where just size matches, merit 3 where only name matches, and merit 4 where file exist in a package but does not exist on the filesystem.

So, I started from gathering the metadata of actual files

1. On a machine where RPM database has been severely damaged I gather information about all files, including absolute path, byte size and md5 checksum

find / -fstype ext3 -type f -printf '%s' -exec md5sum {} \; >/tmp/disk-contents

note: I have only ext3 filesystems, thus I specified -fstype ext3 to exclude /proc and network mounted filesystems. Ideally /tmp, /opt and /home should be excluded as well, but I didn't bother.

The format of a disk-contents file is "filenamesizemd5sumfilename", because md5sum just types this way. I removed the last filename using sed.

sed 's/ \//\\//' /tmp/disk-contents >/tmp/disk-contents-n

2. On a machine where I have all rpms downloaded I gathered meta-information about all files that sit inside rpms, including the rpm file name, absolute path, byte size and md5 checksum
Note: it can be the same machine, but due to space constraints I made it on a separate one. It can sound funny, but I've done it on a Windows machine using Cygwin's utilities, bash and rpm. (yes we can have rpm in windows!)

RPMDIR=g:/download/Linux/RH9/os cd $RPMDIR rm /tmp/rpm-contents
for f in *.rpm ; do rpm -q --nopgp --queryformat='[%{FILENAMES}%{FILESIZES}%{FILEMD5S}'$f'\n]' -p $f >> /tmp/rpm-contents done

I decided to introduce one more table that keeps some info about the whole RPM. Particularly, the fields NAME, PROVIDES, VERSION. That will allow me to group by NAME to group together packages of different versions like lvm-1.0.3-12.i386.rpm and lvm-1.0.3-12.1.legacy.i386.rpm. Why do this? The idea is to check all the files for packages. The package that is most closely presented by the filesystem files will win. (Later I found that the fields PROVIDES and VERSION are not necessery)

RPMDIR=g:/download/Linux/RH9/os cd $RPMDIR rm /tmp/rpm-info
for f in *.rpm ; do rpm -q --nopgp --queryformat=$f'%{NAME}%{VERSION}%{PROVIDES}%{PROVIDENAME}%{PROVIDEVERSION}\n' -p $f >> /tmp/rpm-info
done

3. Load to RDBMS. I decided to go with MySQL(5.0), although I prefer Oracle, I thought that MySQL example can be more helpful to linux people, because of its simplicity and availability.

drop table DISK_CONTENT;

create table DISK_CONTENT (bytes decimal, md5sum varchar(64), filename varchar(2000) );

load data infile 'E:/cygwin/tmp/disk-contents-n' into table DISK_CONTENT fields terminated by '';

create index DISK_CONTENT_IX01 on DISK_CONTENT( filename );

drop table RPM_CONTENT;

create table RPM_CONTENT (filename varchar(2000), bytes decimal, md5sum varchar(64), rpmfilename varchar(128));

load data infile 'E:/cygwin/tmp/rpm-contents' into table RPM_CONTENT fields terminated by '';

alter table RPM_CONTENT drop index RPM_CONTENT_IX01;

alter table RPM_CONTENT add index RPM_CONTENT_IX01 ( filename ); alter table RPM_CONTENT drop index RPM_CONTENT_IX02;

alter table RPM_CONTENT add index RPM_CONTENT_IX02 ( rpmfilename );

drop table RPM_INFO;

create table RPM_INFO (rpmfilename varchar(128), name varchar(128), version varchar(128), provides varchar(128), providename varchar(128), provideversion varchar(128));

load data infile 'E:/cygwin/tmp/rpm-info' into table RPM_INFO fields terminated by '';

alter table RPM_INFO add index RPM_INFO_IX01 ( rpmfilename );

Good. All files metadata has been loaded successfully.

4. Deriving of RPMs. Now I need to find out the RPMs that are supposedly have been previously installed. I wanted to do that in a single query:

select distinct rpmfilename from ( select rpmfilename, case when name=@N then @R:=@R+1 else @R:=1 end Rank, @N:=name r from (select @R:=1, @N:='') r, (select name, rpmfilename, sum(proximity) sumprox from (select I.name, R.filename R_filename, D.filename D_filename, case when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum = R.md5sum) then 0 when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum != R.md5sum) then 1 when (D.filename is not NULL and D.bytes=R.bytes and R.md5sum is NULL ) then 2 when (D.filename is not NULL and D.bytes!=R.bytes) then 3 when (D.filename is NULL) then 4 end proximity, R.rpmfilename, D.md5sum D_md5sum, R.md5sum R_md5sum from RPM_INFO I inner join RPM_CONTENT R on I.rpmfilename=R.rpmfilename left outer join DISK_CONTENT D on D.filename=R.filename /*where R.filename like '/sbin/%'*/ /*order by 1, 4*/ ) v group by name, rpmfilename having count(D_filename)>0 order by 1, 3) v2 ) v3 where Rank=1;

This query works but it requires enormous amount of space for temporary results, gigabytes. I had just 2.5 Gb of free space in my %TEMP% disk, so it has failed.
To resolve this, I’ve split this query to be run sequentially, implicitly storing temporary results in a table rpm_files_tmp.

drop table rpm_files_tmp;

/* place the join into a temp table*/
create table rpm_files_tmp as
select I.name, R.rpmfilename, D.filename D_filename, /*R.filename R_filename, */
case
when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum = R.md5sum) then 0
when (D.filename is not NULL and D.bytes=R.bytes and D.md5sum != R.md5sum) then 1
when (D.filename is not NULL and D.bytes=R.bytes and R.md5sum is NULL ) then 2
when (D.filename is not NULL and D.bytes!=R.bytes) then 3 when (D.filename is NULL) then 4
end proximity
from RPM_INFO I
inner join RPM_CONTENT R on I.rpmfilename=R.rpmfilename
left outer join DISK_CONTENT D on D.filename=R.filename;

/* here I rank packages by a composite merit - sum(proximity), which ideally equals 0. But may be greater, when some files don't match exactly or absent. I selected packages with a best possible match.*/
select distinct rpmfilename into outfile 'E:/cygwin/tmp/rpm-to-install.lst' lines terminated by '\n'
from ( select rpmfilename, case when name=@N then @R:=@R+1 else @R:=1 end Rank, @N:=name r
from (select @R:=1, @N:='') r,
(select name, rpmfilename, sum(proximity) sumprox
from rpm_files_tmp v
group by name, rpmfilename
having count(D_filename)>0
order by 1, 3) v2 ) v3
where Rank=1;

drop table rpm_files_tmp;

5. Install rpms from the list produced in previous step.

# Reinitialize a new rpm database.
mv /var/lib/rpm /var/lib/rpm-damaged-beyond-hope mkdir /var/lib/rpm rpm --initdb

# install GPG keys rpm --import http://www.redhat.com/security/db42a60e.txt rpm --import http://www.fedoralegacy.org/FEDORA-LEGACY-GPG-KEY

# install packages into RPM database only, do not touch files on filesystems.
rpm -i -v --justdb --nodeps --noscripts --notriggers `cat ~/rpm/rpm-to-install.lst`

# check
rpm -qa

Done!

Oracle ANSI join security bug. Bad news again.

Trying to figure out a workaround, I have found that these DMLs that break authorization, also can not be AUDIT TRAILED.

Followup: Oracle block size limit on different platforms

There was a long and annoying dispute on one of the forums about why a maximum block size on Windows (and Linux) platform is 16K, while on "serious" platforms like HP, and Sun, it is 32K. I had a wild suggestion, a bold guess, that the limit has been set based on some business strategy, that should push users to migrate from "minor" platforms towards "serious" ones. My assumption was (and still is) based on a fact, that couple of years ago Oracle had different prices for Intel and RISC CPUs, and having a 32Kb block size could be one more reason for users to migrate on more expensive RISC platforms.I never believed in arguments of other people that the 16K limitation had been caused by some technical deficiencies of "minor" platforms. Sure, it is clear that in Windows, in Linux and even in DOS, it is possible to do I/O by 32Kb chunks. However, nobody could neither confirm nor disprove that the idea of 16Kb limit was a business driven decision. Bill S, has kindly contacted Tom Kyte and the OakTable members. But, to my understanding, nobody could remember the reasoning (http://dizwell.com/forum/index.php?topic=253.30). So, I have decided to contact Oracle Support. I have created a "Request for Improvement". My idea was: Support people may point out for me a technical incapability of "minor" platforms, if there is such.Oracle had accepted this RFI and registered as a Bug(!) #5196815. Funny, they call requests for improvements "bugs". May be because people are bugging them by their requests? :)))
BLOCK SIZE LIMIT THE SAME ON ALL PLATFORMS Doc ID 5196815
https://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=BUG&p_id=5196815

However, they have not answered why it had been decided to have 16K on Windows and Linux. That indirectly confirms my suggestion about a "business driven decision".