Oracle Standard edition to Enterprise edition with same version / higher version

Hello All,

Recently I had changed one of our test DB from Standard edition to enterprise edition,

Prerequisite:

Oracle version: Same version

OS: Same  ( I tested on Linux)


Note: SQL.BSQ script is used to create database of any version ( SE/EE) hence SE /EE database will be almost same with some higher features in EE,  hence version migration (SE to EE) is not complicated.


Refer following steps:

1. Backup the database. (As per your regular full backup policy).

2. If space available, take  cold backup. (Oracle home ,Repository etc)

3. Install Enterprise edition (Software only) in different mount point called as different ORACLE_HOME.

4. In linux you can export new ORACLE_HOME  & existing ORACLE_SID.

5. Startup database;

6. At this stage database will still have SE catalogs. hence to update it to EE run following commands

"catalog.sql"
 "catproc.sql"


7.Restart the database and check DB version.


For Higher version upgradation like 11.2.0.1 of SE to 11.2.0.4 of EE

1 to 4 steps will be the same.

5. startup upgrade;

6. use catupgrd.sql or DBUA utility.

7.Restart the db and check of registry components 

We dont need to run catalog.sql or catproc.sql, because  catupgrd.sql will call these files internally as part of upgradation.

How to set trace on procedure package session level..

Hi All,

Today I will share the syntax, how to put to trace on  procedure package session level..


1. Procedure Package level:

As we all aware any proc / package start with BEGIN & end with END, hence to put a trace find following approach.

  BEGIN
execute immediate 'ALTER SESSION SET sql_trace = TRUE';-- this will enable trace
execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER ="XYZ_NAME"';-- this to identify trace file name in user dump dest.

SQL statements;


  execute immediate 'ALTER SESSION SET sql_trace=FALSE'; -- this will disable trace
END;

As you see above, when you want to enable trace put above statement withing pl/sql block as mentioned above,

Trace file will be generated in dump dest location with extention .trc file. and file name will have XYZ_NAME



2. Session level trace

If you want to generate trace  on session level refer below steps

on sql prompt initiate the trace=true

alter session set sql_trace = true;
alter session set tracefile_indentfier="XYZ_NAME";

select distinct sid from v$mystat;  -- this will give sid of your own session
select a.sid,b.spid from v$session a, v$process b where a.paddr = b.addr and a.sid = &SID;  -- this will give spid (OS process id) of your session

your sql statements/proc/package execution

alter session set sql_trace = false;


Trace file will be generated in dump dest location with extention .trc file. and file name will have XYZ_NAME


This trace file have to convert into readable format, with Oracle tkprof utility.

tkprof XYZ_NAME.trc    XYZ_NAME.txt sys=no