vrijdag 1 februari 2013

Oracle RAC vs NATIVE Compilation


2 years ago we performed an upgrade of our database from an Oracle 9i single instance database towards Oracle RAC 11gR2 with Oracle Unbreakable Linux as OS. 
From the moment we started running on this new platform we noticed that the filesystem /dev/shm started growing and growing until it hit 100% and the database crashed.

When looking into this directory we noticed tons of PESLD* files. These files were generated by the NATIVE compilation mode.

So for some months we used the work-around which was provided to us :


-          shutdown the database
-          remove the PESLD* files from /dev/shm directory
-          startup the database


Our customer was not to happy with this workaround since  we are hosting a 24/7 application, so we needed to find a definitive solution for this.
At first we created a script to remove all PESLD-files which were not in use by the database :

cd /dev/shm
ls -latr PESLD_proddb*>/tmp/PESLD_Remove.tmp
cat /tmp/PESLD_Remove.tmp |cut -c51-89>/tmp/PESLD_Remove.txt

###
### Start removing files
###

while read line
do
     Found="`lsof| grep $line`"
     if [[ ${Found}X == X ]]
     then
       if [[ -f /dev/shm/$line ]]
       then
         echo mv /dev/shm/$line /tmp
         mv /dev/shm/$line /tmp
       fi
     else
       echo $line found. No action will be done.
       echo $Found
     fi
done < /tmp/PESLD_Remove.txt

But this was very dangerous and it was also no final solution.

Luckily for us, there is a way to go back to INTERPRETED mode :

  
-         Take a full backup of the database or create a restore point

-         Stop the database 

[oracle]$ srvctl stop database –d PRODDB 

-         Start-up an instance on one of the nodes in upgrade mode
[oracle]$ sqlplus / as sysdba
SQL> startup nomount
SQL> alter system set cluster_database=false scope=spfilesid='PRODDB1'; 
SQL> shutdown immediate
SQL> startup upgrade


-         Make sure that the parameter plsql_code_type has the value INTERPRETED

-         Execute the script dbmsupgin.sql. This script will switch all packages/functions,… back to INTERPRETED. When this script is finished startup the database in normal mode and run the script utlrp

[oracle]$ sqlplus / as sysdba
SQL> @?/rdbms/admin/dbmsupgin.sql
SQL> shutdown immediate
SQL> startup
SQL> @?/rdbms/admin/utlrp

-        Check if all components in DBA_REGISTRY are valid.

-        Startup the RAC-database


[oracle]$ sqlplus / as sysdba
SQL> alter system set cluster_database=true scope=spfile sid='*';
SQL> shutdown immediate
SQL> startup
[oracle]$ srvctl start database –d PRODDB


 

vrijdag 18 januari 2013

logminer in Oracle 11g

Recently, some developers asked me if it was possible to find out if dml occured for a certain table. They already tried flashback query but this didn't do the trick.

I knew about the logminer-feature but I was not aware that this was so accessible in 11g. In order to make use of the Logmanager-functionality, the database has to be in archivelog-mode and supplemental logging has to be enabled.

SQL> select log_mode,supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME
------------ --------
ARCHIVELOG   YES

1 row selected.
SQL> alter database drop supplemental log data;

Database altered.

SQL> select log_mode,supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME
------------ --------
ARCHIVELOG   NO

1 row selected.
SQL> alter database add supplemental log data;

Database altered.

SQL> select log_mode,supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME
------------ --------
ARCHIVELOG   YES

1 row selected.

To make use of the logminer functionality, start up enterprise manager console and go to the Availability-tab. There you select ’View and Manage Transactions’.



Now we create a table and insert some data :


SQL> create table oracle.test_tab (a number);

Table created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> commit;

Commit complete.




In Enterprise Manager you can now retrieve all transactions or filter on schema/table/…
In this example we are only interested in the newly created table ORACLE.TEST_TAB.




If you click continue logminer will start retrieving the information :




You will get an overview of all transactions which have been executed :



You can perform a ‘drill-down’ if you click on the transaction id :