Find the current SQL in the database

select username, sql_text from v$session s, v$sqlarea sql where s.sql_address = sql.address and s.sql_hash_value = sql.hash_value;

Enabling flashback on Oracle

Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes), set recovery size up to 2 GB in file “/recovery/flashback”:

shutdown immediate;
startup mount;
alter database archivelog;
alter system set DB_FLASHBACK_RETENTION_TARGET=600;
alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;
alter system set DB_RECOVERY_FILE_DEST=’/recovery/flashback’;
alter database flashback on;
alter database open;

ARCHIVE LOG START is DEPRECATED in 11g

In 11g database ,”alter system archive log start;” this statement is not working because from the alert logs archive log start is deprecated . So ALTER DATABASE ARCHIVELOG is enough to start archive process .

ARCHIVE LOG START has been deprecated – see ALTER DATABASE ARCHIVELOG

PL/SQL Native Compilation Without Needing a Third-Party C Compiler

PL/SQL Native Compilation Without Needing a Third-Party C Compiler

The benefit of PL/SQL native compilation is automatically available with Oracle Database 11g. No third-party software (neither a C compiler nor a DLL loader) is needed.

Little crunch on plsql_native_library_dir parameter for HP-UX Itanium platform  pointing to some existing  directory , PL/SQL native compilation utilise the directory for
compilation .Note : It has point to existing directory otherwise you can see so many failure (HP-UX Itanium specific)

Insight

“6.1 Location of the Memory Mapped Files”
——————————————
When a PL/SQL unit is compiled using PLSQL_CODE_TYPE=NATIVE in Oracle
Database 11g, the resulting machine code is loaded into an operating system
shared memory object before it is copied to the database catalog.
Additionally, when the native code is fetched from the catalog for execution,
it is first loaded into a shared memory object. On HP, these shared memory
objects are memory mapped files, which are staged in a directory
automatically created by Oracle under the dbs subdirectory of the directory
specified by the environment variable ORACLE_HOME. If you need to change the
location of the staging directory, contact Oracle support.

How to find blocking session and kill the session from database

During adpatch ,deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates.

* To find blocking session jobs below query will useful. It will return two rows.
select process,sid, blocking_session from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365

* Second step to find the serial number for the Blocking Session to kill
select SERIAL# from v$session where SID=<SID number>
E.g.
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130

* Final step to kill the blocking session
alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ‘365,130’;
System altered.

Database Upgrade from 10.2.0.2 to 10.2.0.3 – R12

Oracle Apps certified the 10.2.0.3 database with R12.Metalink Note 454750.1

Here I will explain my upgrade experience.

As a normal practice before doing upgrade please backup your system. Download the required patches.

Operating system	Unix Users	Windows users
Patch set	5337014	5337014
Patch	6319846	6344567

1. Before Upgrade , check the invalid count in the database

2. Unzip the patch 5337014 .

3. Invoke the run installer for unix and setup.exe for windows users.

4. As a part of post install step

· Start the database in upgrade mode

· @?/rdbms/admin/catupgrd.sql

· After completed upgrade ,Shutdown the database and startup in normal mode.

· @?/rdbms/admin/utlrp.sql

· Apply the additional patches(6319846 and 6344567)

CPU patch and Opatch

Today I am going to give little insight about Opatch and CPU patch.Opatch is a tool is used to patch all oracle homes for oracle applications. CPU is stands for Critical patch updates, in other way its a security patches for Apps. Is Apps DBA responsibility to keep CPU patches up-to-date to avoid security vulnerabilities.

In Apps we need to use Opatch and adpatch for patching oracle apps. Why we need to use two utility to patch oracle apps.opatch is used to patch all oracle homes (for R12..10.1.2,10.1.3 and 10.2.0.2 oracle homes). Apps patches we need to use to adpatch.

How to use Opatch?

Opatch is one of the easiest and safest for patching oracle apps because you can rollback opatch if you find any issues applying the patches

1. From the opatch read me make sure which oracle home you want to patch
2. Shutdown the instance related to particular oracle home.
3. Set the oracle home in the env .Set the opatch in the path.
4. Follow the readme whether opatch needs up gradation.
5. cd to the patch area and opatch apply
6. Proceed the post install steps
7. Run cpu_root.sh to give some permissions to the executables.
8. For rollback use opath rollback –id
9. “opatch lsinventory “ is used to list all the patches
10. “opatch lsinventory –details” is used to find the version belongs to particular oracle home.

CPU patch

CPU patch has to keep uptodate. Use opatch to apply CPU patches. Follow the opatch steps to apply the CPU patch. As part of post installation step run catcpu.sql and utlrp.sql

Issues will face

1. When you are applying CPU patch you will get conflict with some patches .Report to Oracle Corporation they will provide a new merged patch if its actually a conflict.