Oracle Producsts Knowledge Base

Oracle E-business suite logs clean up

2022-07-05T22:32:00.000+05:30

Oracle E-business suite logs clean up

#!/bin/bash

cd $EBS_DOMAIN_HOME

find $EBS_DOMAIN_HOME -type f -path "*/logs/*.log?*" -mtime +10 -exec rm {} \;

find $EBS_DOMAIN_HOME -type f -path "*/logs/*.out?*" -mtime +10 -exec rm {} \;

#add validation code to check if env is set,

cd $IAS_ORACLE_HOME/instances

find $IAS_ORACLE_HOME/instances -type f -path "*diagnostics/logs*log.*" -mtime +10 -exec rm {} \;

2021-07-19T23:32:00.007+05:30

Concurrent Request Analytics.

Find number of concurrent requests submitted month wise.

select to_char(a.requested_start_date,'yymon'), count(*) from

fnd_concurrent_requests a

--where concurrent_program_id in ('20428','20821') use to find for a particular program

GROUP BY to_char(a.requested_start_date,'yymon') order by to_char(a.requested_start_date,'yymon') desc;

Find size of files per extension

Note: change grep to include month year file or file extension

cd $APPLCSF

Size of files per month and per type

ls -ltr | grep Jul |grep RTF | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep RTF | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep xml | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep txt | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

ls -ltr | grep Jun |grep xls | awk '{SUM+=$5}END{print SUM/1024/1024/1024}'

size of file per day

ls -ltr | grep Aug |grep xls |awk '{a[$6 $7]+=$5;}END{for(i in a)print i", "a[i];}'

Find concurrent requests older than 60 days with completion duration

SELECT

f.request_id ,

pt.user_concurrent_program_name user_conc_program_name,

f.actual_start_date start_on,

f.actual_completion_date end_on,

floor(((f.actual_completion_date-f.actual_start_date)

*24*60*60)/3600)

|| ' HOURS ' ||

floor((((f.actual_completion_date-f.actual_start_date)

*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)

*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((f.actual_completion_date-f.actual_start_date)

*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)

*24*60*60)/3600)*3600 -

(floor((((f.actual_completion_date-f.actual_start_date)

*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)

*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference,

p.concurrent_program_name concurrent_program_name,

decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

f.status_code

from apps.fnd_concurrent_programs p,

apps.fnd_concurrent_programs_tl pt,

apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV('Lang')

and f.actual_start_date is not null

and f.actual_start_date > sysdate - 60

and (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) > 60

order by

f.actual_start_date desc;

Find average, min, max for concurrent program in last 60 days

SELECT

pt.user_concurrent_program_name user_conc_program_name,

decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,

count(*),

TRUNC (AVG (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

|| ' Days'

|| ' + '

|| TO_CHAR (

TRUNC (SYSDATE)

+ NUMTODSINTERVAL (

AVG ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

* 86400,

'second'),

'HH24:MI:SS')

"AVERAGE",

TRUNC (MIN (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

|| ' Days'

|| ' + '

|| TO_CHAR (

TRUNC (SYSDATE)

+ NUMTODSINTERVAL (

MIN ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

* 86400,

'second'),

'HH24:MI:SS')

"Min",

TRUNC (MAX (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

|| ' Days'

|| ' + '

|| TO_CHAR (

TRUNC (SYSDATE)

+ NUMTODSINTERVAL (

MAX ( (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE))

* 86400,

'second'),

'HH24:MI:SS')

"Max"

from apps.fnd_concurrent_programs p,

apps.fnd_concurrent_programs_tl pt,

apps.fnd_concurrent_requests f

where f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV('Lang')

and f.actual_start_date is not null

and f.actual_start_date > sysdate - 60

and (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) > 60

group by pt.user_concurrent_program_name, f.phase_code ;

2021-02-12T01:22:00.000+05:30

Dealing with Oracle jobs

Views:

dba_jobs_running - lists all jobs that are currently running in the instance.

dba_jobs - describes all jobs in the database.

To manage jobs as sys users for the jobs created by other users

use package dbms_ijobs to remove or mark other user jobs broken

To manage jobs logged in as user who submitted jobs

use package dbms_jobs

2020-08-21T05:58:00.001+05:30

FIND LOW LEVEL DEBUG ON E-BUSINESSS SUITE

PRODUCTION DATABASES SHOULD NOT HAVE DEBUG ENABLED

1. Query to find if debug is enabled in EBS by setting trace event at application user level

SELECT po.profile_option_name "NAME", po.user_profile_option_name,

DECODE (TO_CHAR (pov.level_id),

'10001', 'SITE',

'10002', 'APP',

'10003', 'RESP',

'10005', 'SERVER',

'10006', 'ORG',

'10004', 'USER',

'***'

) "LEVEL",

DECODE (TO_CHAR (pov.level_id),

'10001', '',

'10002', app.application_short_name,

'10003', rsp.responsibility_key,

'10005', svr.node_name,

'10006', org.NAME,

'10004', usr.user_name,

'***'

) "CONTEXT",

pov.profile_option_value "VALUE"

FROM apps.fnd_profile_options_vl po,

apps.fnd_profile_option_values pov,

apps.fnd_user usr,

apps.fnd_application app,

apps.fnd_responsibility rsp,

apps.fnd_nodes svr,

apps.hr_operating_units org

WHERE 1 = 1

AND pov.application_id = po.application_id

AND pov.profile_option_id = po.profile_option_id

AND usr.user_id(+) = pov.level_value

AND rsp.application_id(+) = pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+) = pov.level_value

AND svr.node_id(+) = pov.level_value

AND org.organization_id(+) = pov.level_value

and po.profile_option_name like '%FND_INIT_SQL%'

and lower(pov.profile_option_value) like '%event%'

ORDER BY "NAME";

2. FIND CONCURRENT PROGRAMS WITH TRACE ENABLED

select * from apps.fnd_concurrent_programs_vl WHERE ENABLE_TRACE='Y' ;

3. Check for debug profile options which are set to Y

SELECT po.profile_option_name "NAME", po.user_profile_option_name,

DECODE (TO_CHAR (pov.level_id),

'10001', 'SITE',

'10002', 'APP',

'10003', 'RESP',

'10005', 'SERVER',

'10006', 'ORG',

'10004', 'USER',

'***'

) "LEVEL",

DECODE (TO_CHAR (pov.level_id),

'10001', '',

'10002', app.application_short_name,

'10003', rsp.responsibility_key,

'10005', svr.node_name,

'10006', org.NAME,

'10004', usr.user_name,

'***'

) "CONTEXT",

pov.profile_option_value "VALUE"

FROM apps.fnd_profile_options_vl po,

apps.fnd_profile_option_values pov,

apps.fnd_user usr,

apps.fnd_application app,

apps.fnd_responsibility rsp,

apps.fnd_nodes svr,

apps.hr_operating_units org

WHERE 1 = 1

AND pov.application_id = po.application_id

AND pov.profile_option_id = po.profile_option_id

AND usr.user_id(+) = pov.level_value

AND rsp.application_id(+) = pov.level_value_application_id

AND rsp.responsibility_id(+) = pov.level_value

AND app.application_id(+) = pov.level_value

AND svr.node_id(+) = pov.level_value

AND org.organization_id(+) = pov.level_value

and po.profile_option_name like '%DEBUG%'

and lower(pov.profile_option_value)= 'y'

ORDER BY "NAME";

2019-11-25T22:27:00.002+05:30

Oracle e-business suite 12.2

Add/Remove Managed server from weblogic

perl $AD_TOP/patch/115/bin/adProvisionEBS.pl \
ebs-delete-managedserver -contextfile=$CONTEXT_FILE \
-managedsrvname=oacore_server2 -servicetype=oacore \
-managedsrvport=7207 -logfile=/tmp/oacore_server2.del

perl $AD_TOP/patch/115/bin/adProvisionEBS.pl \
ebs-create-managedserver -contextfile=$CONTEXT_FILE \
-managedsrvname=oacore_server2 -servicetype=oacore \
-managedsrvport=7209 -logfile=/tmp/oacore_server2.add

So if you have to change the port used by EBS instance, then you have to log into the Weblogic admin console and change port there and then sync context xml file using adSyncContext.pl. This adSyncContext.pl utility will get the current port values from Weblogic console and update the xml with new port values. Once the context xml file syncs, we have to run Autoconfig to sync other config files and database profile values to pickup new webport

There are a few My Oracle Support notes that can help you understand these utilities little more, such as 1676430.1 and 1905593.1. But understand that Autoconfig is a different ball game in Oracle E-Business Suite R12.2.

2019-06-25T02:52:00.002+05:30

DBA Scripts in GitHub

https://github.com/windwiny/Oracle-DBA-scripts

expdp impdp datapump status check using query API

2017-08-11T08:56:00.000+05:30

Hi All,

Below pl/sql code can be used to monitor datapump status.

Status of datapump is pulled from KU$_WORKERSTATUS1120, you can describe this and modify
" dbms_output.put_line( ws(ind).worker_number || ',' || ws(ind).state || ',' || ws(ind).NAME || ',' || ws(ind).object_type || ',' || ws(ind).degree || ',' || ws(ind).
percent_done || ',' ||ws(ind).COMPLETED_ROWS);" To choose the columns to monitor.

set lines 160
set feed off
set serverout on ;
DECLARE
ind NUMBER; -- Loop index
h1 NUMBER; -- Data Pump job handle
percent_done NUMBER; -- Percentage of job complete
job_state VARCHAR2(30); -- To keep track of job state
js ku$_JobStatus; -- The job status from get_status
ws ku$_WorkerStatusList; -- Worker status
sts ku$_Status; -- The status object returned by get_status
counter NUMBER; -- count executing workers
job_n dba_datapump_jobs.job_name%TYPE;
owner_n dba_datapump_jobs.owner_name%TYPE;
BEGIN
select job_name, owner_name into job_n, owner_n from sys.DBA_DATAPUMP_JOBS ;
dbms_output.put_line('Owner: '||owner_n);
dbms_output.put_line('Job_name: '||job_n);
h1 := DBMS_DATAPUMP.attach(job_n, owner_n); -- job name and owner
dbms_datapump.get_status(h1,dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip, 0, job_state, sts);
js := sts.job_status;
counter := 0;
ws := js.worker_status_list;
dbms_output.put_line('*** Job percent done = ' ||to_char(js.percent_done));
dbms_output.put_line('restarts = '||js.restart_count);
ind := ws.first;
DBMS_OUTPUT.put_line (to_char(SYSDATE,'DD-MON-YYYY:HH24:MI'));
dbms_output.put_line('Number,State,Object Name,Object Type,Parallel,Percentage Done,completed_rows');
while ind is not null loop
dbms_output.put_line( ws(ind).worker_number || ',' || ws(ind).state || ',' || ws(ind).NAME || ',' || ws(ind).object_type || ',' || ws(ind).degree || ',' || ws(ind).
percent_done || ',' ||ws(ind).COMPLETED_ROWS);
if ws(ind).state = 'EXECUTING' then
counter := counter + 1 ;
end if ;
ind := ws.next(ind);
end loop;
dbms_output.put_line ('Total Executing =' || counter );
DBMS_DATAPUMP.detach(h1);
end;
/

Regards
Manoj

2016-06-21T16:43:00.002+05:30

Configure UNIX server for oracle apps workflow mailer.

Edit the /etc/mail/sendmal.mc file
Look for this bellow line in this file
DAEMON_OPTIONS (`Port=smtp,Addr=127.0.0.1, Name=MTA')dn
sendmail configuration for workflow mail, setup test box
Add 1 line with SMTP port Number and Server IP to this above line
DAEMON_OPTIONS (`Port=25,Addr=192.168.1.61, Name=MTA')dnl
Here 25 is SMTP Port Number, 192.168.1.61 is Server IP
After adding his like file look like this
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
DAEMON_OPTIONS(`Port=25,Addr=192.168.1.61, Name=MTA')dnl

Rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc
$m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf
**NOTE: yum install sendmail-cf ( if error encountered )

yum install dovecot* (this is for imap)
/etc/init.d/dovecot restart
telnet localhost 143
a login appuser apppassword
c list "" *
create folder PROCESS and DISCARD under ~/mail

regards

2016-01-07T17:45:00.001+05:30

RMAN backup scripts

RMAN cold backup
================
run{
allocate channel d1 type disk FORMAT '/u01/xx/xxxxx//%d_db_%u_%s_%p' MAXPIECESIZE 10G;
allocate channel d2 type disk FORMAT '/u01/xx/xxxxx//%d_db_%u_%s_%p' MAXPIECESIZE 10G;
allocate channel d3 type disk FORMAT '/u01/xx/xxxxx//%d_db_%u_%s_%p' MAXPIECESIZE 10G;
allocate channel d4 type disk FORMAT '/u01/xx/xxxxx//%d_db_%u_%s_%p' MAXPIECESIZE 10G;
shutdown immediate;
startup mount;
backup tag FYR_database_cold as compressed backupset database ;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
allocate channel ar1 type disk format '/u01/xxxxx/rman_cn_%T_%U.rman';
sql "alter database backup controlfile to ''/u01/xxxxx/rman_control.rman'' reuse";
release channel ar1 ;
alter database open;
}

RMAN Hot Backup
===============
run{
allocate channel d1 type disk FORMAT '/u01/xxxxxx/%d_db_%u_%s_%p' MAXPIECESIZE 10G;
allocate channel d2 type disk FORMAT '/u01/xxxxxx/%d_db_%u_%s_%p' MAXPIECESIZE 10G;
backup tag whole_database_cold as compressed backupset database ;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
allocate channel ar1 type disk format '/u01/xxxx/rman_arc_%T_%U.rman';
BACKUP ARCHIVELOG from time 'sysdate - 2' ;
sql "alter database backup controlfile to ''/u01/xxxx/rman_control.rman'' reuse";
release channel ar1 ;
}

RESTORE (Duplicate)
===================
rman auxiliary /
RUN
{
ALLOCATE AUXILIARY CHANNEL c1 xxxICE TYPE disk ;
ALLOCATE AUXILIARY CHANNEL c2 xxxICE TYPE disk ;
ALLOCATE AUXILIARY CHANNEL c3 xxxICE TYPE disk ;
SET NEWNAME FOR DATABASE TO '/u01/xxx/oracle/data/%b';
duplicate database PROD to 'xxx' BACKUP LOCATION '/xxxxx/DB_Restore_xxx'
duplicate database PROD to 'xxx' BACKUP LOCATION '/xxxxx/DB_Restore_xxx'
LOGFILE GROUP 1 ('/u01/xxx/oracle/data/redo01.rdo') SIZE 1G REUSE, GROUP 2 ('/u01/xxx/oracle/data/redo02.rdo') SIZE 1G REUSE, GROUP 3 ('/u01/xxx/oracle/data/redo03.rdo') SIZE 1G REUSE;}

Use this in init file for RAC database "_no_recovery_through_resetlogs=TRUE"

2016-01-07T17:43:00.002+05:30

Scripts for Apps DBA for daily operations.

--------------------------------------------------------
------OPERATION SCRIPTS TO HELP IN DAILY JOBS-----------
--------------------------------------------------------
1. Unlock application user
2. Currently Logged in users
3. Datafile shrink Report
4. Check DR Gap
5. Check DR current activity
6. Find SQL executed by a concurrent request
7. Unlock FNd user
8. Free Tablespace report
9. Get DDL of an object
10. List invalids with statement to compile them
11. Responsibilities assigned to a user.

--------------------------------------------------------
1. Unlock application user

BEGIN
FND_USER_PKG.EnableUser('A.STIGTER');
END;
/
--------------------------------------------------------
2. Currently Logged in users
SELECT
fu.user_name User_Name,
fu.email_address emailid,
fu.description,
TO_CHAR(IC.FIRST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"First Accessed",
TO_CHAR(IC.LAST_CONNECT,'dd-Mon-rrrr HH24:mi:ss')"Last Accessed",
ic.nls_territory,
FVL.RESPONSIBILITY_NAME "RESPONSIBILITY NAME",
fr.RESPONSIBILITY_KEY "Responsibility Key",
ic.function_type,
ic.time_out,
fu.user_id,
fu.employee_id,
ic.responsibility_application_id,
ic.responsibility_id,
ic.org_id,
ic.counter,
fr.menu_id,
ic.disabled_flag
FROM
apps.fnd_user fu,
apps.fnd_responsibility fr,
apps.icx_sessions ic,
apps.fnd_responsibility_VL FVL
WHERE
fu.user_id = ic.user_id
AND FR.RESPONSIBILITY_KEY =FVL.RESPONSIBILITY_KEY
and fVl.responsibility_key != 'IRC_EXT_CANDIDATE'
AND fr.responsibility_id = ic.responsibility_id
AND ic.disabled_flag ='N'
AND ic.responsibility_id IS NOT NULL
AND (ic.last_connect + DECODE(apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time,apps.FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate
and counter < ic.limit_connects
ORDER BY "Last Accessed" DESC;
--------------------------------------------------------
3. Datafile shrink Report

select /*+rule*/ 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*8192)/1024/1024)||'m autoextend on maxsize '||ceil( blocks*8192/1024/1024)||'m;' stat,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) smallest,
ceil( blocks*8192/1024/1024) currsize,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
from
dba_data_files a,
(select file_id, max(block_id+blocks-1) hwm
from
dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
order by 4;
***NOTE: Autoexend on has to be enabled with relevant maxsize

--------------------------------------------------------
4. Check DR Gap

select a.*, b.*, (a."Archived" - b."Applied") "GAP"
from
(select thread#, max(sequence#) "Archived" from v$archived_log where archived='YES' group by thread# ) a,
(select thread#, max(sequence#) "Applied" from v$archived_log where applied='YES' group by thread# ) b where a.thread# = b.thread#
--------------------------------------------------------
5. Check DR current activity

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY
--------------------------------------------------------
6. Find SQL executed by a concurrent request

SELECT
B.sid,
C.sql_text ,
C.module
FROM APPS.fnd_concurrent_requests A ,
gV$SESSION B ,
gV$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = 1451703
--------------------------------------------------------
7. Unlock FNd user

BEGIN
FND_USER_PKG.EnableUser('&username');
END;
/
--------------------------------------------------------
8. Free Tablespace report
select
a.tablespace_name,
round(sum(a.bytes/1024/1024),2) TOTAL_MB,
round(sum
(
( case
when autoextensible = 'YES' then
(a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
when autoextensible = 'NO' then
nvl(b.bytes,0)
end
)/1024/1024
),2) FREE_MB,
(100 - round((round(sum(( case when autoextensible = 'YES' then (a.maxbytes - a.user_bytes) + nvl(b.bytes,0) when autoextensible = 'NO' then nvl(b.bytes,0) end)/1024/1024),2) *100) / round(sum(a.bytes/1024/1024),2))) "Percentage Used"
from
dba_data_files a,
(
select file_id,
sum(bytes) bytes
from dba_free_space
group by file_id
) b
where
a.file_id = b.file_id(+)
group by a.tablespace_name
order by Total_mb desc
--------------------------------------------------------
9. Get DDL of an object

--select dbms_metadata.get_ddl( object_type=>'&OBJECT_TYPE',name=>'&OBJECT_NAME',schema=>'&SCHEMA_NAME') FROM DUAL ;

select dbms_metadata.get_ddl(object_type, object_name, owner)
from
(
--Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
select
owner,
object_name,
decode(object_type,
'PACKAGE', 'PACKAGE_SPEC',
'PACKAGE BODY', 'PACKAGE_BODY',
'TYPE BODY', 'TYPE_BODY',
object_type
) object_type
from dba_objects
where object_name = :object_name
--These objects are included with other object types.
and object_type not in ('INDEX PARTITION','LOB','LOB PARTITION','TABLE PARTITION')
--Ignore system-generated types that support collection processing.
and not (object_type like 'TYPE' and object_name like 'SYS_PLSQL_%')
)
order by owner, object_type, object_name;
--------------------------------------------------------
10. List invalids with statement to compile them

set linesize 200
set pagesize 200
col object_name for a40
col owner for a6
set echo off
set feedback off
set verify off
set heading off
SELECT '----------------' from dual;
SELECT 'List of Invalids' from dual;
SELECT '----------------' from dual;
set heading on
select owner, objecT_name, objecT_type, status from dba_objects where status!='VALID';
set heading off
SELECT '---------------------------------' from dual;
SELECT 'Execute Below to compile invalids' from dual;
SELECT '---------------------------------' from dual;
select 'alter ', object_type, owner, '.' ,object_name, 'compile ;' from dba_objects where status!='VALID' and object_type != 'PACKAGE BODY'
union ALL
select 'alter ', decode (OBJECT_TYPE, 'PACKAGE BODY', 'PACKAGE'), owner, '.' ,object_name, 'compile BODY ;' from dba_objects where status!='VALID' and object_type = 'PACKAGE BODY' ;
--------------------------------------------------------
11. Responsibilities assigned to a user.

Select b.user_name, c.responsibility_name, a.START_DATE, a.END_DATE
from fnd_user_resp_groups_direct a, fnd_user b, fnd_responsibility_tl c
where a.user_id = b.user_id
and a.responsibility_id = c.responsibility_id
and b.user_name='&username'

2015-11-24T15:14:00.001+05:30

How to remove references of used features in oracle database
Oracle License register remove
remove used feature flag from oracle database

Shut down application
backup and restart database

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

2015-11-15T18:06:00.002+05:30

High Invalids in e-business suite ( oracle apps 12.2 )

manually granting privileges to object causes huge invalids in database, right way to grant privs to another user is to use "exec AD_ZD.GRANT_PRIVS"

Regards
Manoj

2015-11-01T16:48:00.002+05:30

How to find version for components in 12.2.x e-business suite

perl $FND_TOP/patch/115/bin/TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=$APPLTMP/Report_inventory.html

NOTE: make sure there is no 'E' in $APPLTMP

Above will give oracle home version with applied patches, this can be run against Database well.

you can query version keyword in context file as well just to get versions

grep -i version $CONTEXT_FILE

Regards

Manoj

2015-10-26T12:15:00.002+05:30

Test your Storage I/O speed latency using oracle database

use below query to test you database server I/O

SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<NUM_PHYSICAL_DISKS>, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (8, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/

2015-10-26T12:14:00.001+05:30

WebLogic Password Recovery

There are many blogs out there providing various methods to recover WebLogic password below method is the easiest of the all.

First we need to know DOMAIN_HOME directory. My OMS is located in “/u02/Middleware/oms”. You can find yours if you read “/etc/oragchomelist”. If the full path of OMS is “/u02/Middleware/oms”, my middleware home is “/u02/Middleware/”. Under my middleware home, I need to go GCDomains folder

2	oracle@db-cloud /$ cd /u02/Middleware oracle@db-cloud Middleware$ cd gc_inst/user_projects/domains/GCDomain

First let’s get the encrypted information from boot.properties file:

oracle@db-cloud GCDomain$ cat servers/EMGC_ADMINSERVER/security/boot.properties

# Generated by Configuration Wizard on Wed Jun 04 10:22:47 EEST 2014

username={AES}nPuZvKIMjH4Ot2ZiiaSVT/RKbyBA6QITJE6ox56dHvk=

password={AES}krCf4h1du93tJOQcUg0QSoKamuNYYuGcAao1tFvHxzc=

The encrypted information starts with {AES} and ends with equal (=) sign. To decrypt the username and password, we will create a simple java application:

3

4

5

6

7

8

9

10

oracle@db-cloud GCDomain$ cat recoverpassword.java

public class recoverpassword {

 public static void main(String[] args)

 {

  System.out.println(

  new weblogic.security.internal.encryption.ClearOrEncryptedService(

  weblogic.security.internal.SerializedSystemIni.getEncryptionService(args[0]

   )).decrypt(args[1]));

  }

}

Save it as “recoverpassword.java”. To be able to compile (and run) it, we need to set environment variables (we’re still in GCDomain folder). We’ll give the encrypted part as the last parameter:

1

2

3

4

5

6

oracle@db-cloud GCDomain$ . bin/setDomainEnv.sh

oracle@db-cloud GCDomain$ javac recoverpassword.java

oracle@db-cloud GCDomain$ java -cp $CLASSPATH:. recoverpassword \

$DOMAIN_HOME {AES}nPuZvKIMjH4Ot2ZiiaSVT/RKbyBA6QITJE6ox56dHvk=

oracle@db-cloud GCDomain$ java -cp $CLASSPATH:. recoverpassword \

$DOMAIN_HOME {AES}krCf4h1du93tJOQcUg0QSoKamuNYYuGcAao1tFvHxzc=

When we run the last two commands, we should see the weblogic administrator username and password in plain text. By the way, even if you use the same password with me, you may see different encrypted text because when encrypting and decrypting, weblogic uses the cypher key stored in “security/SerializedSystemIni.dat” file. So as long as the cypher key is different, you get different encrypted text for even same input.

Thanks to :http://www.gokhanatil.com/2015/03/how-to-recover-weblogic-administration-password-of-enterprise-manager.html

2015-10-20T14:28:00.000+05:30

Sending putty content in Outlook Mail, with same/proper format.

Copy Paste putty session with same format

1) Open Putty

2) Expand Windows and select "Selection"

3) enable "Paste to clipboard in RTF as well as plain Text

4) save to default or named sessions

5) copy and paste from putty to outlook or other applications

Regards
Manoj

2015-05-08T09:10:00.000+05:30

Working with Oracle Database Parallelizm Issues, specially in RAC environment

Ref:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm
http://docs.oracle.com/cd/E14072_01/server.112/e10837/parallel.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
Query to check parallel executions on instance:
----------------------------------------------
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
       px.SERVER_GROUP "Group", px.SERVER_SET "Set",
       px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
      s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
      s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;
Examples
========
ALTER TABLE table_name PARALLEL (DEGREE 8);
SELECT /*+PARALLEL(table_alias, degree, nodes)*/ *
FROM table_name ...
OR
SELECT /*+PARALLEL */ * FROM table_name/View name ...

2015-05-08T09:09:00.001+05:30

Quick Oracle Database Tunning Tips without OEM(Enterprice Manager)

http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm
http://www.pythian.com/news/584/wouldnt-automatic-sql-plan-management-be-the-11g-killer-app/

create a tunning task
---------------------
variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);
variable spa_tune_task VARCHAR2(64);
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => 'SELECT S.EMPLID ,S.EMPL_RCD , PDE.EFFDT ,INS.PER_ORG ,INS.ORIG_HIRE_DT ,PDE.SEX ,PRS.BIRTHDATE ,PRS.BIRTHPLACE ,PRS.BIRTHCOUNTRY ,PRS.BIRTHSTATE ,PRS.DT_OF_DEATH ,PDE.HIGHEST_EDUC_LVL ,PDE.FT_STUDENT ,PDE.LANG_CD ,PDE.ALTER_EMPLID ,PDE.MAR_STATUS ,ASG.HOME_HOST_CLASS ,ASG.CMPNY_SENIORITY_DT ,ASG.SERVICE_DT ,ASG.PROF_EXPERIENCE_DT ,ASG.LAST_VERIFICATN_DT ,ASG.OWN_5PERCENT_CO ,ASG.PROBATION_DT ,ASG.ORG_INSTANCE_ERN ,ASG.SENIORITY_PAY_DT FROM PS_PERSON PRS ,PS_PER_ORG_ASGN ASG ,PS_PER_ORG_INST INS ,PS_PERS_DATA_EFFDT PDE ,PS_GP_PYE_STAT_WRK S WHERE S.RUN_CNTL_ID ='ABS_JAN2012_IND' AND S.OPRID = 12345 AND S.EMPLID BETWEEN 1000 AND 19999 AND S.EMPLID = PRS.EMPLID AND S.EMPLID = PDE.EMPLID AND S.FRST_JOB_SEG_IND = 'Y' AND S.EMPLID = ASG.EMPLID AND S.EMPL_RCD = ASG.EMPL_RCD AND ASG.EMPLID = INS.EMPLID AND ASG.ORG_INSTANCE_ERN = INS.ORG_INSTANCE_ERN ORDER BY S.EMPLID, S.EMPL_RCD, PDE.EFFDT DESC ');
---or-----
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(task_name=>'hello1223',sql_id => '4qh14ksgd4dd8',scope='COMPREHENSIVE');
---or-----
DECLARE
sql_text clob;
sqlprofile_name varchar2(30);
task_name varchar2(100);
BEGIN
sql_text :=
task_name:=dbms_sqltune.create_tuning_task(sql_text,task_name=>'TASKREQ01',scope=>'COMPREHENSIVE');
END;
/
execute a tunning task
----------------------
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
display a tunning task
----------------------
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.report_tuning_task('TASK_4188') AS recommendations FROM dual
SELECT DBMS_SQLTUNE.report_tuning_task(:stmt_task) AS recommendations FROM dual
accept a recommendation sql profile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4256',task_owner => 'MD86506', replace => TRUE);
disable sql profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_013c237dab800000','STATUS','DISABLED');

view task information
--------------------
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'MD86506';

primary_gl_tb

SELECT DBMS_SQLTUNE.report_tuning_task('primary_gl_tb') AS recommendations FROM dual ;
execute dbms_sqltune.accept_sql_profile(task_name => 'primary_gl_tb',task_owner => 'MD86506', replace => TRUE);

USER_ADVISOR_TASKS <-- table which have task information
•DBA_ADVISOR_TASKS
•DBA_ADVISOR_FINDINGS
•DBA_ADVISOR_RECOMMENDATIONS
•DBA_ADVISOR_RATIONALE
•DBA_SQLTUNE_STATISTICS
•DBA_SQLTUNE_BINDS
•DBA_SQLTUNE_PLANS
•DBA_SQLSET
•DBA_SQLSET_BINDS
•DBA_SQLSET_STATEMENTS
•DBA_SQLSET_REFERENCES
•DBA_SQL_PROFILES
•V$SQL
•V$SQLAREA
•V$ACTIVE_SESSION_HISTORY
enable/dislabe/delete sql profile
=================================

SELECT *
FROM DBA_SQL_PROFILES prof,
DBMSHSXP_SQL_PROFILE_ATTR attr
WHERE prof.NAME=attr.PROFILE_NAME
ORDER BY prof.name,
attr.attr_number;
Enable/Disable
Locate the name of the SQL Profile you would like to disable and plug it in to the following statement:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
Dropping a SQL Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');

2015-05-08T09:08:00.002+05:30

SQL for Tunning task

Ref:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm
http://docs.oracle.com/cd/E14072_01/server.112/e10837/parallel.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
Query to check parallel executions on instance:
----------------------------------------------
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
       px.SERVER_GROUP "Group", px.SERVER_SET "Set",
       px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
      s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
      s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;

Examples
========
ALTER TABLE table_name PARALLEL (DEGREE 8);
SELECT /*+PARALLEL(table_alias, degree, nodes)*/ *
FROM table_name ...
OR
SELECT /*+PARALLEL */ * FROM table_name/View name ...

2015-05-08T09:06:00.002+05:30

CONSUMING WEB SERVICE FROM ORACLE DATABASE 11G USING UTL_HTTP PACKAGE

Contents

1. Create access control list and assign host

1.1 Create access control list

1.2 Create Role and assign to access control list

1.3 Add hosts

1.4 Test Access

2. Write procedure to call web service

1. Create access control list and assign host

1.1 Create access control list

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

ACL => 'STREAM_TEST.XML',

DESCRIPTION => 'PERMISSIONS TO ACCESS ESUPPORT WEBSITE',

PRINCIPAL => 'MD86506',

IS_GRANT => TRUE,

PRIVILEGE => 'CONNECT');

COMMIT;

END;

1.2 Create Role and assign to access control list

BEGIN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (

ACL => 'STREAM_TEST.XML',

PRINCIPAL => 'DBA',

IS_GRANT => TRUE,

PRIVILEGE => 'CONNECT',

POSITION => NULL);

COMMIT;

END;

1.3 Add hosts

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

ACL => 'STREAM_TEST.XML',

HOST => '*.satyam.com);

COMMIT;

END;

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

ACL => 'STREAM_TEST.XML',

HOST => '172.16.187.25');

COMMIT;

END;

1.4 Test Access

CREATE OR REPLACE PROCEDURE GETTITLE (PURL VARCHAR2)

VRESULT CLOB;

BEGIN

VRESULT := REPLACE(UTL_HTTP.REQUEST(PURL),CHR(10),' ');

VRESULT := REGEXP_REPLACE(VRESULT,'.*<TITLE> ?(.+) ?</TITLE>.*','\1',1,1,'I');

DBMS_OUTPUT.PUT_LINE (VRESULT);

END;

SET SERVEROUTPUT ON

EXECUTE GETTITLE ('HTTP://172.16.187.119:8050');

2. Write procedure to call web service

2.1 UTL_HTTP

1. UTL_HTTP.BEGIN_REQUEST : UTL_HTTP.BEGIN_REQUEST('http://172.16.187.25:8000/sap/xi/adapter_plain?bs=FILE'||'&'||'namespace=http%3A//file2file.com'||'&'||'interface=FILEHTML'||'&'||'qos=EO','POST',NULL,NULL);

Opens http request to the specified URL

2. UTL_HTTP.SET_HEADER:

UTL_HTTP.SET_HEADER (req,'Content-Type','text/xml');

UTL_HTTP.SET_HEADER (req,'Content-Length','200');

Set communication parameters for the http request

3. UTL_HTTP.WRITE_TEXT

UTL_HTTP.WRITE_TEXT (req,'<?xml version="1.0" encoding="UTF-8"?><ns0:FILEHTML xmlns:ns0="http://file2file.com"><row><EMPLID>865072</EMPLID><EMPLANAME>fromDB4</EMPLANAME></row></ns0:FILEHTML>');

Write data into http stream

4. UTL_HTTP.RESPONSE:

-resp := UTL_HTTP.GET_RESPONSE (req);

Capture response of the http stream

5. UTL_HTTP.END_RESPONSE:

Close request and response http connections

6. UTL_HTTP.END_REQUEST:

Close request http connections

2.2 UTL_DBWS

2015-05-08T08:59:00.001+05:30

Oracle Fusion Application Installation brief overview

Background
Oracle Fusion Application is best feature mix of e-business suite, jd edwards and peoplesoft integrated with Fusion Middleware products.

Installation of Fusion Application would mean installation of Fusion application components and all the fmw products.

Oracle has simplified install and configure by means of automated installation and configuration along with install validation. Oracle call this automation as provisioning.

Installation process.
So Provisioning basically means installing various oracle products, configuration them to work with each other using oracle supplied special software(framework/life cycle managment tools)

A Framework software executes the installation, configuration and validation of all the required software components.

All the required inputs for installation/configuration and gathered in step called "creating responce file", This is the first step after installation of framework/life cycle managment tools software installation.

you would invoke resonponce file creation step using provisiongWizard and supply inputs in interview phase, after which a responce file is created. This will will be supplied during Applicaiton provisioning phase.

Application provisiong phase runs in below order.

Preverify
Install
Preconfigure
Configure
Configure Secondary
Post Configure
Startup
Validation.

Regards
Manoj

shell script to automate oracle database cloning using rman active duplicate feature

2014-02-06T13:04:00.001+05:30

##########################################################
#Script to clone xxxxxx from xxxxxxxx from active database #
#Author: Manoj #
#Date: 30th Oct 2012 #
#Approximate time 1 Hour #
###########################################################
#
#Be very carefully while configuring listener
#hostname and tnsnames hostname should be a perfect match
#
#

LOG_FILE=/u01/app/oracle/diag/custom_logs/FIN1_clone.log
RMAN_LOG=/u01/app/oracle/diag/custom_logs/FIN1_RMAN.log
export LOG_FILE
export RMAN_LOG

ORACLE_SID=FIN1
export ORACLE_SID

#echo "confirm"
#echo $ORACLE_SID

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
echo "--------------------------------------" >> $LOG_FILE
echo "Start of Script at `date`" >> $LOG_FILE

#Drop database
#echo "shut immediate" | sqlplus '/ a sysdba' >> $LOG_FILE
#echo "startup nomount restrict" | sqlplus '/ as sysdba' >> $LOG_FILE
#echo "drop database" | sqlplus '/ as sysdba' >> $LOG_FILE
$ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB FINUAT -sysDBAUserName sys -sysDBAPassword xxxxxxxxx >> $LOG_FILE
#add exception handling here

#Copy password file
cp $ORACLE_HOME/dbs/orapwFIN2 $ORACLE_HOME/dbs/orapwFIN1 >> $LOG_FILE
#add exception handling here

#create password file
#orapwd file=$ORACLE_HOME/dbs/orapwFIN1 password=satyam entries=10

echo "Start of clone of FINUAT from FINPRD from active databse `date`" >> $LOG_FILE
#Copy init file
cp $ORACLE_HOME/dbs/bkp_init1.ora $ORACLE_HOME/dbs/initFIN1.ora >> $LOG_FILE
#add exception handling here

#Add entry to tnsnames.ora

echo "FINUAT = " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " (DESCRIPTION = " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1621)) " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " (CONNECT_DATA = " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " (SERVER = DEDICATED) " >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " (SERVICE_NAME = FIN1)" >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " )" >> $ORACLE_HOME/network/admin/tnsnames.ora
echo " )" >> $ORACLE_HOME/network/admin/tnsnames.ora

#Shut down if started.
#echo "shut immediate" | sqlplus '/ as sysdba'

#Start database in nomount
echo "startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/bkp_initFIN1.ora" | sqlplus '/ as sysdba' >> $LOG_FILE

#create spfile from pfile
echo "create spfile from pfile;" | sqlplus '/ as sysdba' >> $LOG_FILE

#stop DB
echo "shut immediate;" | sqlplus '/ as sysdba' >> $LOG_FILE

#Stop and start listener services
lsnrctl stop FIN1 >> $LOG_FILE
lsnrctl start FIN1 >> $LOG_FILE
#

#start DB with Pfile
echo "startup nomount;" | sqlplus '/ as sysdba' >> $LOG_FILE

#clone from active database
$ORACLE_HOME/bin/rman target sys/xxxx@FIN2 auxiliary sys/xxxxxxx@FIN2 msglog $RMAN_LOG << EOF
run
{
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
allocate auxiliary channel a3 device type disk;
allocate auxiliary channel a4 device type disk;
allocate channel a5 device type disk;
allocate channel a6 device type disk;
allocate channel a7 device type disk;
allocate channel a8 device type disk;
duplicate database to FIN1 from active database;
release channel a1 ;
release channel a2 ;
release channel a3 ;
release channel a4 ;
release channel a5 ;
release channel a6 ;
release channel a7 ;
release channel a8 ;
}
EOF
echo "Backup finished at `date`" >> $LOG_FILE
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||" >> $LOG_FILE

# add entry to /etc/oratab file
echo "FIN1:/u01/app/oracle/product/11.2.0/dbhome_1:N #added by md86506" >> /etc/oratab

echo
echo "@ /u01/app/oracle/Backup/SCRIPTS/FIN1_POST_CLONE.sql" | sqlplus '/ as sysdba' >> $LOG_FILE
exit ;

#LOCAL_LISTENER='ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1621))'
#
#FINUAT =
# (ADDRESS_LIST =
# (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCFINUAT))
# (ADDRESS= (PROTOCOL= TCP)(Host= hostname)(Port= 1621))
# )
#
#SID_LIST_FINUAT =
# (SID_LIST =
# (SID_DESC =
# (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
# (SID_NAME = FIN1)
# )
# (SID_DESC =
# (SID_NAME = PLSExtProc)
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
# (PROGRAM = extproc)
# )
# )
#

move execution plan between two oracle databases

2014-02-06T12:57:00.001+05:30

IMPORT/EXPORT execution plan from one Database to another

You use DBMS_SQLTUNE subprograms to move SQL profiles and SQL tuning sets from one system to another using a common programmatic model. In both cases, you create a staging table on the source system and populate that staging table with the relevant data. You then move that staging table to the destination system following the method of your choice (such as datapump, import/export, or database link), where it is used to reconstitute the objects in their original form. These steps are implemented by means of subprograms included in this package:

Call the CREATE_STGTAB_SQLPROF Procedure or the CREATE_STGTAB_SQLSET Procedure to create the staging table on the source system.

Call the PACK_STGTAB_SQLPROF Procedure or PACK_STGTAB_SQLSET Procedure to populate the staging table with information from the source system.

Once you have moved the staging table to the destination system, you call the UNPACK_STGTAB_SQLPROF Procedure or the UNPACK_STGTAB_SQLSET Procedure to recreate the object on the new system.

EXAMPLE:

NOTE: Do not sys schema for staging table

Create a staging table:
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_TO_PROD');

dump profile to staging table
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(profile_name => 'migrate_to_prod_1',profile_category => 'DEFAULT',staging_table_name=> 'PROFILE_TO_PROD', staging_schema_owner => 'MD86506');

export/import staging table
exp tables=PROFILE_TO_PROD file=prof01.dmp log=prof01.log
imp file=prof01.dmp log=profimp.log tables=PROFILE_TO_PROD

unpack the staging table for sql_profile to come into effect
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => FALSE, -
staging_table_name => 'PROFILE_STGTAB');

References: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqltun.htm#CHDGFHEC

exadata to non-exadata cloning

2014-02-06T12:55:00.000+05:30

clone
-----------------------
1)
Create init.ora file (example instance name: TPTST)
*._gby_hash_aggregation_enabled=FALSE
*._optimizer_cost_based_transformation='OFF'
*._optimizer_use_feedback=FALSE
*._unnest_subquery=FALSE
*.audit_sys_operations=FALSE
*.audit_trail='NONE'
*.compatible='11.2.0.2.0'
*.control_files='xxxxxxxxxxxxxxxcontrol01.ctl','xxxxxxxxxxxcontrol02.ctl'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_cache_size=750M
*.db_domain=''
*.db_files=1000
*.db_name='TPTST'#Reset to original value by RMAN
*.diagnostic_dest='xxxxxxxxx'
*.large_pool_size=250M
*.log_file_name_convert='xxxxxxx','/u03/','xxxxxxxxxxxx','/u02/'
*.open_cursors=700
*.optimizer_index_caching=99
*.optimizer_index_cost_adj=40
*.optimizer_use_sql_plan_baselines=FALSE
*.parallel_adaptive_multi_user=FALSE
*.parallel_execution_message_size=8384
*.parallel_max_servers=6
*.parallel_min_servers=2
*.pga_aggregate_target=800M
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='DEFAULT_PLAN'
*.session_cached_cursors=250
*.sga_target=2048M
*.shared_pool_size=700M
*.undo_tablespace='UNDOTBS1'

2)
source oracle_Sid

3)
start DB using init.ora file in nomount state

4)
create spfile from pfile

5)
restart DB using spfile in nomount state

6)
rman auxiliary /

7)
execute below command to create instance.
Check space usage and redirect tablespaces using "set newname for tablespace" clause.

RUN
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk ;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk ;
SET NEWNAME FOR DATABASE TO '/u04/ORADATA/HREDU/%b';
duplicate database SFNPRD to SFNPRD1 BACKUP LOCATION '/xx/xx/xx'
LOGFILE GROUP 1 ('/xx/xx/xx/redo01.log') SIZE 1G REUSE,
GROUP 2 ('/xx/xx/xx/redo02.log') SIZE 1G REUSE,
GROUP 3 ('/xx/xx/xx/redo03.log') SIZE 1G REUSE;
}

8)
mast sensitive data
remove hybrid columanar compression if zfs storage is not available
run appliation specific post clone scripts
drop unwanted undo, temp and redo's

------Active duplicate-----

NOTE:
* make sure to have local_listener parameter in spfile
* make sure listener entries are available in listener.ora in auxiliary and in tnanames.ora in both target and auxiliary

run
{
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
allocate channel a5 device type disk;
allocate channel a6 device type disk;
SET NEWNAME FOR DATABASE TO 'xxxxxxxxxxxxx/%b';
duplicate database to xxxxxxxxxxx from active database;
release channel a1 ;
release channel a2 ;
release channel a5 ;
release channel a6 ;
}

NagarjunSagar, Andhra Pradesh, INDIA. Evening sunset

2013-01-07T21:41:00.000+05:30

NagarjunSagar, Andhra Pradesh, INDIA. Evening sunset

Manoj Duvva