TSPITR using auxiliary destination

Unfortunately, After some time, we noted a logical corruption of data within an application schema  which could not be eliminated by scripts :/

Schema used by the application is only a small part of  data warehouses. Scheme had to be restored to a certain point in time.
After a moment's thought decided to use the new functionality of restore tablespaces using auxiliary destination with automatic instance.

Fortunately application had separated twelve tablespaces , so there was concern that violate the data from other systems.

We need to restore 400GB of 7TB database.

We created a separate directory for the external instance and run command:

run {
    SET AUXILIARY INSTANCE PARAMETER FILE TO '/oracle/app/11.2/dbs/aux_rman_instance.ora';
    recover TABLESPACE APP_DATA1,...,APP_DATA12 auxiliary destination '/oradb/aux';
}

After restoring tablespaces RMAN performs all the steps, which in previous versions you had to do manually. Now, all these steps are performed automatically. Cool, but can cause big problems and instead of saving time, it will bring a lot of work.

First problem:
The default memory settings for the automatic instance is too low and need to set  shared_pool_size = 200M and sga_target=400M. In this way, after about 12 hours restored is stopped and external instance, along with the files was removed after  got the error ORA-04031.

So, once again :)

The second problem, after several hours, again a error. This time has exceeded the number of processes (probably due  to run export process)   and  we change a parameter processes=150.

This time, however, despite the error, the auxiliary instance is not removed and datafiles can be used.
Further steps I done manually, since I could not wait for next 12 hours :)

One more thing. In parameter file for auxiliary instance should be specified db_name. Default db_name is random and thus easier to connect to it and export metadata for datafiles.

Oracle DB 11.2 - password_life_time

Oracle database 11.2 - password life time

After migrating a database from 10g to 11g version, I noticed that in the default profile was changed parameter password_life_time = 180 days which can cause problems for applications


Solution is change default profile or create a special profile "application"

alter profile default limit password_life_time unlimited;

Oracle and PHP - change password

PHP and change expired password on the Oracle database.

For databases with more users with expiring  passwords if not all users have sqlplus client installed is the problem with changing passwords. Unfortunately, few tools support this feature.

The easiest way is to write a simple interface PHP that uses a very useful function oci_password_change:

public function change_and_connect($user, $old_pw, $new_pw, $database)
    {
          $c = oci_password_change($database, $user, $old_pw, $new_pw);
        if (!$c) {
            $m = oci_error();
            $_SESSION['err_message'] =$m["message"];
            return($m['code']);
        }
        else {
            return(true);
        }
       
    }