Working with the Oracle Flashback option we can restore the old data using flashback queries. Introduced in Oracle Version 9.2 the “AS OF” clause can be used to:

• find the old data
• restore the old data

Mistakenly set the salary of Manager King to a wrong value:

SQL> update emp set sal=4500 where ename=’KING’;
1 row updated.
SQL> commit;
Commit complete.

SQL> select ename, sal from emp where ename=’KING’;
ENAME             SAL
———- ———-
KING             4500

We can query the old value again using the “AS OF” clause and specifying a time interval:

Issuing this query will tell us the correct value as it was existing 15 minutes before.

SELECT ename, sal FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ’15’ MINUTE)
WHERE ename = ‘KING’;

ENAME             SAL
———- ———-
KING             5000

Now we can use flashback query as a sub query in an update statement to restore back to the old value. The command is as follows:

update emp
set sal = (
SELECT sal FROM emp
AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ’15’ MINUTE) where ename=’KING’)
where ename=’KING’;

In case you are going to use this for testing purposes, you might find it usefully to record the referencing SCN and use flashback query in combination with the recorded SCN.

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
————————
                 4312233

Now changing a value on purpose:

SQL> update emp set sal=4300 where ename=’KING’;
1 row updated.

SQL> commit;
Commit complete.

SQL> select ename, sal from emp where ename=’KING’;
ENAME             SAL
———- ———-
KING             4300

Then you might not know the previous salary of MILLER but you do know the SCN when it refered to the old value and the original value can be updated and restored with flashback query by using the SCN noted prior to the update statement.

Here is the required query:

SELECT ename, sal FROM emp
AS OF SCN 4312233
where ename=’KING’;

ENAME             SAL
———- ———-
KING             4300

And you can restore the old value by updating the row or better you directly restore the old value using the following update with flashback sub query:

update emp
set sal = (
SELECT sal FROM emp
AS OF SCN 4312233 where ename=’KING’)
where ename=’KING’;

Oracle’s BI Publisher which is part of the Oracle Business Intelligence Suit has at least within Enterprise Computing environment established as a reliable and preferable choice when it comes to replace the somewhat aged Oracle Reports.

Once being familiar with it you will ask yourself about why switching tool when it comes to the need of creating reports in a normal web design world mostly using MySQL Databases within a “LAMP” environment.

Written as an universal reporting tool BI Publishes does offer us a more or less universal data interface based on JDBC connections. And being able to act itself as a callable web service offering WEBDAV services based on XML data exchange it anyway seems to be an excellent choice for this world.

Already Oracle’s installation instructions does give you a quick idea about setting up BI Publisher working against some MySQL Database. But acting  in a Web environment you might find it cool integration the BI Publisher within an already existing Apache Tomcat environment and for exactly this environment I will give you a short explanation about getting things together and ready for work.

First we will pick the BI Publisher software itself by copying the xmlpserver.war file from the distribution DVD and throwing it into the webapps folder of our Apache Tomcat. Waiting a few seconds until the self-deploying mechanism of the Apache Tomcat has extracted the files for us, we need to specify the access path to our project and development repository within the file xmlp-server-config.xml.

Having done that, we need to add the MySQL JDBC driver to our system and making it known within the environment of the Apache Tomcat. (The MySQL JDBC driver can be downloaded from: http://www.mysql.com/products/connector/j/.)

So with the downloaded fileready, we have to extract it to any place we like onto our disk. Just I would recommend you to put it somewhere close to your MySQL installation for you being able to later on remember it again. Finally extracted and ready to use, we have to add the information about the mysql-connector-java- -bin.jar file to the Java Classpath of the Apache Tomcat and finally restarting it:

Having this set, as a last step we have to invoke the BI Publisher itself, in which we then will configure the new JDBC data source within the Admin region of it:

And now with this tweaking ready, MySQL DB should connect, open and work for you like a charm.

When you want to use SQL*Plus or exp/imp on the same server on which you have installed your database you might get errors like these:

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

or

Error 6 initializing SQL*Plus
Message file sp1us.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Syntax:
$ sqlplus username/password

You get this error only when you execute ‘sqlplus’ as a user outside of the dba or Oracle group, because in Oracle Database 10g Release 2 the Oracle Home was locked down by setting umask to 007 prior to installation, so that the files that sqlplus needs to execute do not have read/execute permissions for group “others” and some directories under $ORACLE_HOME have no world read nor execute permission (rwxr-x—)

Warning , don’t use the following command to lower the permissions, this may corrupt your Oracle-Installation:
chmod -R 755 $ORACLE_HOME/

The better way is to use an script which is provided by oracle:
$ORACLE_HOME/install/changePerm.sh

This script will relax the permissions on most files and directories in your Oracle Home, and allow “others” to execute them.