tkprof

Step 1: find session details 

select sid, serial from v$session where username= 'ABMITRA';

SID  SERIAL#

---- --------

1484 59049

Step 2: Enable trace for the session 

begin

dbms_monitor.session_trace_enable (

session_id => 1484,

serial num => 59049,

waits =>true,

binds  =>true,

plan stat => 'all executions');

end;

/

Step 3: Disable trace

EXECUTE dbms_monitor.session_trace_disable (1484, 59049);

Step 4: get trace file name

select

r.value '/diag/rdbms/' ||

sys_context ('USERENV','DB NAME') || '/' ||

sys_context('USERENV', 'INSTANCE_NAME') || '/trace/' ||

sys_context('USERENV', 'DB_NAME') || '_ora_' || p.spid||'.trc'

as tracefile_name

from v$session s, v$parameter r, vsprocess p

where r.name='diagnostic_dest'

and s.sid=&1

and p.addr= s.paddr;

/u02/app/oracle/diag/rdbms/DBQA/DBQA3/trace/DBQA_ora_294796.trc

/u02/app/oracle/diag/rdbms/DBQA/DBQA1/trace/DBQA_ora_302882.trc

/u02/app/oracle/diag/rdbms/DBQA/DBQA1/trace

Step 5: go to location and run tkprof

tkprof DBQA1_ora_302882.trc MV_script_test.txt sys=no waits=yes aggregate=no width=180

Gateway

 Setting up new databases for Gateway server 
===================================================
hostvm01
export ORACLE_HOME-/apps/oracle/product/19.3.0/tghome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export TNS ADMIN=$ORACLE_HOME/network/admin
export ODBCINI=/apps/oracle/product/19.3.0/tghome_1/hs/admin/odbc.ini
For SQL Server
================
cd /apps/oracle/product/19.3.0/tghome_1/hs/admin
==create initORA file for the database you wish to connect :
[oracle@hostvm01 admin]$ cat initIMS.ora
#This is a sample agent init file that contains the HS parameters that are 
#needed for the Database Gateway for ODBC
#HS init parameters
HS_FDS_CONNECT_INFO = LMS
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so 
#ODBC specific environment variables
set ODBCINI=/apps/oracle/product/19.3.0/tghome_1/hs/admin/odbc.ini
[oracle@hostvm01 admin]$
============created entry in odbc.ini file for the database.
[oracle@hostvm01 admin]$ cat odbc.ini
[ODBC Data Sources]
LMS = ODBC Driver 18 for SQL Server
[LMS]
Driver=/opt/microsoft/msodbcsq118/lib64/libmsodbcsql-18.3.so.1.1 
Description=Microsoft ODBC Driver 18 for SQL
Database= LMS
Server=TCP: qwawsqlsvrdb01.rl-core.rliaig.net, 1433
QuotedId= Yes
AnsiNPW= Yes
TrustServerCertificate=yes
[ODBC]
Trace = 0
TraceFile= /opt/microsoft/msodbcsql/odbctrace.out
[oracle@hostvm01 admin]$
-Now in network_admin location add db entry to the listener file-
[oracle@hostvm01 admin]$ pwd
/apps/oracle/product/19.3.0/tghome_1/network/admin
[oracle@hostvm01 admin]$ cat listener.ora
#listener.ora  Network Configuration File:/apps/oracle/product/19.3.0/tghome_1/network/admin/listener.ora 
#Generated by Oracle configuration tools.
LISTENER_GTW
 (DESCRIPTION =
  (ADDRESS (PROTOCOL = TCP) (HOST=hostvm01.ri-core.ri.aig.net) (PORT = 1535))
 )
SID_LIST_LISTENER_GTW =
(SID_LIST =
(SID_DESC = 
(SID_NAME = LMS)
(ORACLE_HOME = /apps/oracle/product/19.3.0/tghome_1)
(ENV="LD_LIBRARY_PATH=/opt/microsoft/msodbcsql18/lib64: /usr/lib64: /apps/oracle/product/19.3.0/tghome_1/lib")
(PROGRAM dg4odbc)
)
)
LISTENER SAP =
(DESCRIPTION=
 (ADDRESS (PROTOCOL = TCP) (HOST hostvm01.rl-core.r1.aig.net) (PORT = 1526))
SID_LIST_LISTENER_SAP=
(SID_LIST=
  (SID_DESC=

session count

 set lines 200 pages 999
col OSUSER for a20
col USERNAME for a20
col MACHINE for a30
col STATUS for a9
col MIN_DATE for a20
col MAX DATE for a20
col CNT for 999
break on status skip 1
compute sum of CNT on status
SELECT
inst_id,
OSUSER,
USERNAME,
MACHINE,
STATUS,
to_char(min(logon_time), 'dd-MON-YYYY: HH24:mi:ss') MIN_DATE, 
to_char(max(logon_time), 'dd-MON-YYYY:HH24:mi:ss') MAX_DATE,
/* rpad (COUNT(*),3,'') "CNT" */
COUNT(*) "CNT"
FROM GVSSESSION
GROUP BY inst_id,OSUSER, USERNAME, STATUS, MACHINE
ORDER BY STATUS, to_char (min (logon_time), 'dd-MON-YYYY: HH24: mi:ss'), to_char (max(logon_time), 'dd-MON-YYYY:HH24:mi:ss')
/

Rman duplicate script

 [oracle@hostvm01 ORAQA]$ cat ORAQA_dup.sh
#!/bin/ksh
export ORACLE_SID=ORAQA1
export ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_3
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH
rman log "rman_dup_ORAQA_20210807.log" <<EOF 
connect target "dbadmin/Use_1t_wisely@oraprd" 
connect auxiliary "dbadmin/Use_1t_wisely@ORAQAr"
run
{
allocate channel c1 device type disk; 
allocate channel c2 device type disk; 
:
:
allocate channel c16 device type disk;
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK; 
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK; 
:
:
ALLOCATE AUXILIARY CHANNEL aux15 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux16 DEVICE TYPE DISK;
 set newname for datafile 1 to "+DATAC1/ORAQA/datafile/system1.dbf"; 
 set newname for datafile 2 to "+DATAC1/ORAQA/datafile/sysaux1_dbf";
 :
 :
 set newname for datafile 1676 to "+DATAC1/ORAQA/datafile/orastg_data136_dbf";
 set newname for datafile 1677 to "+DATAC1/ORAQA/datafile/orastg_data137_dbf"; 
 set newname for tempfile 1 to "+RECOC1/ORAQA/tempfile/temp_1.dbf"; 
 set newname for tempfile 2 to "+RECOC1/ORAQA/tempfile/temp_2.dbf";
 :
 set newname for tempfile 61 to "+RECOC1/ORAQA/tempfile/temp3_34.dbf";
 set newname for tempfile 62 to "+RECOC1/ORAQA/tempfile/temp3_35.dbf";
 duplicate target database to ORAQA from active database nofilenamecheck;
}
EOF