How to enable sql session tracing in oracle database

Enabling session tracing in oracle database


1. Identify sql client session to be traced


For example you are planning to trace the session for module ABC , identify the SPID for session e.g. 13762780

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set line 1000
col OSUSER for a10
col SID for 999
col USERNAME for a20
col MACHINE for a20
set pagesize 100
SELECT distinct
a.sid,
a.serial#,
b.spid, sq.sql_id,
b.pid,
a.username,
a.machine,
a.status,
a.module,
a.osuser,
a.LAST_CALL_ET/60 "Inac_Min",a.logon_time,c.event, sq.sql_text
FROM
v$session a,
v$process b,
v$session_wait c,
v$sqlarea sq
WHERE
a.username IS NOT NULL
AND
a.paddr=b.addr
and a.username='GENEVA_ADMIN' and a.sql_hash_value=sq.hash_value
and (upper(a.module) like '%ABC%')
and c.sid=a.sid;


2. Enable trace

Steps to create active data guard in 11g and up-gradation to 12c using manual method - I

Steps to Create 11 ADG and up-gradation to 12c using manual method

 10 steps will take you through systematic approach to create standby database/ADG (active data guard) followed by activation and up-gradation to 12c using manual method

    1.  11g dummy instance creation for ADG

     2.  Dummy instance cleanup and preparation for rman restore to create standby

     3.  RMAN level 0 restoration initiation

     4.  ADG parameter setting

     5.  Restore archives and apply to standby

     6.  Activating standby

     7.  Pre-requisites of 12c upgrade

     8.  Initiate 12c upgrade

     9.  Time zone upgrade

    10. Post upgrade steps
     A.  Issues Faced

Above approach is best suited to reduce overall downtime involved to application and DB upgrade can be performed within 6 Hrs.

Please refer the setup as below 

testlabdb005a/testlabdb005b -: New two node RAC database hosts to be used for 11g restore/ADG/Upgrade to 12c

testoldlabdb1/testoldlabdb12 -: Old two node RAC database with 11g database TESTDBPRD which acts as a primary db node

TESTDBPRD :- Old 11g Database global name , name to be preserved in ADG

NEWDBPRD :- New 11g ADG database unique name , older name will be preserved as global dbname

 1.   11g dummy instance creation for ADG

We will create a dummy database instance to get db service /ASM/ TNS configuration readily available

[oracle@testlabdb005a ~]$ . ./.bash_profile_11g

[oracle@testlabdb005a ~]$ which sqlplus

/golden/app/oracle/11.2.0.3.0/db/bin/sqlplus

[oracle@testlabdb005a ~]$  export DISPLAY=164.16.18.55:10.0

[oracle@testlabdb005a ~]$ dbca

ORA-01722: invalid number

Issue :

ORA-01722: invalid number

Cause:


I. Attempt has been made to insert a character value in integer datatype column


SQL> create table mytest ( one integer , two varchar2(10));

Table created.

CRS-4123 CRS-4124 CRS-4000

Issue:   cluster start command "crsctl start crs"  fails to start crs services

CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-4124: Oracle High Availability Services startup failed
CRS-4000: Command Start failed, or completed with errors


Cause:

Recent changes to cluster hosts ...