Size of oracle Control File

Size of control files

SYS@orcl> select sum((cffsz+1)*cfbsz)/1024/1024 "Control File Size(MB)" from x$kcccf;

Control File Size(MB)
---------------------
20.203125

How to stop oracle Trace ?

Stop trace dump


1. Get the process number from trace file.

Example :
-rw-rw—- 1 dba 184034 Jan 29 17:02 test_diag_17701.trc

2. 18701 is the process number from trace file.

SQL> oradebug setospid 17701
Oracle pid: 3, Unix process pid: 17701,

SQL> oradebug close_trace;
Statement processed.

Data pump status .... is job running ?

col JOB_NAME format a20
col OPERATION format a10
col JOB_MODE format a10
col STATE format a10
Select job_name, operation, job_mode, state from user_datapump_jobs where STATE!='NOT RUNNING'


SQL> r
1* Select job_name, operation, job_mode, state from user_datapump_jobs where STATE!='NOT RUNNING'


JOB_NAME OPERATION JOB_MODE STATE

-------------------- ---------- ---------- ----------
SYS_IMPORT_TABLE_08 IMPORT TABLE EXECUTING


set lines 200 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
col date format a20
col job_name format a20
col status format a10
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;


DATE PROGRAM SID STATUS USERNAME JOB_NAME SPID SERIAL# PID

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

2010-07-13 09:44:21 udi@ln-ora-01tst (TNS V1-V3) 143 INACTIVE SYS SYS_IMPORT_TABLE_13 14130 2 23

2010-07-13 09:44:21 oracle@ln-ora-01tst (DM00) 146 ACTIVE SYS SYS_IMPORT_TABLE_13 14132 60 24

2010-07-13 09:44:21 oracle@ln-ora-01tst (DW01) 140 ACTIVE SYS SYS_IMPORT_TABLE_13 14134 3 25

2010-07-13 09:44:21 oracle@ln-ora-01tst (DW02) 137 ACTIVE SYS SYS_IMPORT_TABLE_13 14136 2 26






-bash-3.00$ impdp "'/ as sysdba'" attach=SYS_IMPORT_TABLE_08

Import: Release 10.2.0.4.0 - 64bit Production on Friday, 02 July, 2010 14:58:09

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_IMPORT_TABLE_08
Owner: SYS
Operation: IMPORT
Creator Privs: TRUE
GUID: 8A5433675F594D74E0440003BA0AE183
Start Time: Thursday, 01 July, 2010 14:10:58
Mode: TABLE
Instance: ccdb
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND '/******** AS SYSDBA' directory=datapump1 DUMPFILE=pumpuat_1405.dmp SCHEMAS=(PROD1,UAT2,ESEARCH)
IMPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND '/******** AS SYSDBA' DIRECTORY=dir DUMPFILE=pumpuat_1405.dmp REMAP_SCHEMA=prod1:test TABLES=user_sites LOGFILE=user_sites01july.log TABLE_EXISTS_ACTION=APPEND
TABLE_EXISTS_ACTION APPEND
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle/backup/pumpuat_1405.dmp

Worker 1 Status:
State: EXECUTING
Object Schema: TEST
Object Name: USER_SITES
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Completed Rows: 239,889,948
Completed Bytes: 10,016,441,128
Percent Done: 100
Worker Parallelism: 1

Oracle 10g Datapump Session Wait Information

set lines 150 pages 100 numwidth 7
col program for a30
col username for a10
col spid for a7
col date for a20
col job_name for a20
col STATUS for a10

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid, s.serial#,

s.status, s.username, d.job_name, p.spid, p.pid

from v$session s, v$process p, dba_datapump_sessions d

where p.addr=s.paddr and s.saddr=d.saddr; 2 3 4



DATE PROGRAM SID SERIAL# STATUS USERNAME JOB_NAME SPID PID

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

2010-07-02 10:24:39 oracle@xxx-cccora-01tst (DM00) 119 921 ACTIVE SYS SYS_IMPORT_TABLE_08 19832 40

2010-07-02 10:24:39 oracle@xxx-cccora-01tst (DW01) 124 4 ACTIVE SYS SYS_IMPORT_TABLE_08 19834 44




-- In SQL*Plus, activate SQL tracing with DBMS_SYSTEM and SID/SERIAL#
-- Syntax: DBMS_SYSTEM.SET_EV([SID],[SERIAL#],[EVENT],[LEVEL],'')

-- Example to SQL_TRACE Worker process with level 4 (Bind values):
execute sys.dbms_system.set_ev(119,921,10046,4,'');

-- and stop tracing:
execute sys.dbms_system.set_ev(119,921,10046,0,'');


-- Example to SQL_TRACE Master Control process with level 8 (Waits):
execute sys.dbms_system.set_ev(119,921,10046,8,'');

-- and stop tracing:
execute sys.dbms_system.set_ev(119,921,10046,0,'');

SQL> execute sys.dbms_system.set_ev(119,921,10046,12,'');
PL/SQL procedure successfully completed.
SQL> execute sys.dbms_system.set_ev(124,4,10046,12,'');
PL/SQL procedure successfully completed.


-- Activate SQL tracing database wide,
-- Be careful: all processes will be traced !


CONNECT / as sysdba
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';


- Start the Import/export Data Pump or Import Data Pump job, e.g.:

impdp system/XXXX DIRECTORY=dir DUMPFILE=pumpuat_1405.dmp REMAP_SCHEMA=scott:test TABLES=user_site LOGFILE=user_site.log


-- Unset event immediately after Data Pump job ends:

ALTER SYSTEM SET EVENTS '10046 trace name context off';


To Check session trace is on ?

SQL> select sid,serial#,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session;

SID SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
------- ------- ------------------------ --------------- ---------------
119 921 ENABLED TRUE TRUE <<<<<<<<---------
124 4 ENABLED TRUE TRUE <<<<<<<<---------
127 218 DISABLED FALSE FALSE
134 636 DISABLED FALSE FALSE
140 199 DISABLED FALSE FALSE
147 4 DISABLED FALSE FALSE

Data pump Shell Script on Oracle Solaris

#CREATE DIRECTORY datapump AS '/opt/oracle/backup/DataPump/Backup';


# !/bin/bash

TODAY=`date`

ORACLE_SID=orcl ; export ORACLE_SID

ORACLE_BASE=/opt/oracle ; export ORACLE_BASE

ORACLE_HOME=/opt/oracle/product/10.2 ; export ORACLE_HOME

PATH=${ORACLE_HOME}/bin:$PATH ; export PATH

EXPFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.dmp; export EXPFILE

ERRFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.err; export ERRFILE

LOGFILE=$ORACLE_SID.datapump.`date +%m%d%Y`.log ; export LOGFILE

SUCC=/opt/oracle/backup/DataPump/Backup/SUCC.txt ; export SUCC



# echo $EXPFILE



find /opt/oracle/backup/DataPump/Backup -name "*.err" -mtime +2 -exec rm {} \;



ps -ef | grep -i smon|grep -v grep|wc -l> oracle.exp



if [ `cat oracle.exp|grep -v grep|grep -i "1"|wc -l` -eq 1 ]; then



[ -f $ERRFILE ]; rm $ERRFILE



expdp "'/ as sysdba'" directory=datapump DUMPFILE=$EXPFILE SCHEMAS=SCOTT,XYZ logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4



fi



find /opt/oracle/backup/DataPump/Backup -name "$ORACLE_SID.export.*" -mtime +3 -exec rm {} \;

oracle trace via database logon trigger

Database trigger


-- GRANT administer DATABASE TRIGGER TO "SCOTT";


CREATE OR REPLACE TRIGGER ON_LOGON_CCCPROD AFTER LOGON ON DATABASE
WHEN ( USER = 'SCOTT' )

declare

v_trace_file_name varchar2(40);

-- LOWER(SYS_CONTEXT('userenv', 'session_user'))||to_char(sysdate,'dd.mm.yyyyhh:mi')
--

BEGIN
v_trace_file_name:= replace(replace(LOWER(SYS_CONTEXT('userenv', 'session_user')),'-', '_'), '''' , '_')||to_char(sysdate,'dd.mm.yyyyhh:mi') ;

EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER='''|| v_trace_file_name||'''';
-- execute immediate ;
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set STATISTICS_LEVEL = ALL' ;
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

Oracle patch information

opatch lsinventory -details


col COMMENTS format a25
col ACTION_TIME format a25
col NAMESPACE format a10
col VERSION format a12
col ACTION format a10
set linesize 200
select * from registry$history ;