Friday, March 25, 2011

Difference between Oracle Apps 11i and R12 (Technical)

Major technical difference: - 

Database:
Database Version in 11i (11.5.9 & 11.5.10) was 9i Rel 2 where as in Release 12 its 10g R2 (10.2.0.2)

Application Tier:Tech Stack in Application Tier consist of iAS(1.0.2.2.2) & Developer 6i (Forms & Reports 8.0.6) but in Applications R12 it is build on Fusion Middleware (10g Web Server and 10g Forms & Reports)

Sub component in Application TierA) HTTP Server or Web Server in R12 is Version 10.1.3 which is built on Apache version 1.3.34. In apps 11i it is Version 1.0.2.2.2 built on Apache Version 1.3.19
B) Jserv in apps 11i is replaced by OC4J (mod_jserv is replaced by mod_oc4j)
C) Forms Version 6i in Apps 11i is replaced by Forms 10.1.2.0.2 in R12
D) Reports Version 6i in Apps 11i is replaced by Reports 10.1.2.0.2 in R12
E) JDBC version is changed from version 9 in apps 11i to version 10.2.0 in Apps R12
F) modplsql or mod_pls is removed from Apps R12
G) Java processes use JDK/JRE version 1.5.0 in R12 against JDK version 1.3.1 or 1.4.2 in Apps 11i
H) For various environment variable changes check below picture : -



I) New top INSTANCE_TOP added in Release 12 for configuration and log files: -














 *****************************************************

Oracle Apps R12 File System Changes: -

new TOP (INSTANCE_TOP) in Application Tier.

If you are familiar with Oracle Apps 11i , you can easily understand picture on your right. This is file system for 11i with various TOP's , APPL_TOP, ORA_TOP, COMMON_TOP, DB_TOP and DATA_TOP. With Multiple Middle Tier configuration came shared APPL_TOP (only APPL_TOP used to be shared ) . After shared  APPL_TOP came shared Application Tier (All three TOP's on Application Tier, APPL_TOP, ORA_TOP and COMMON_TOP were shared across servers). With this came challenge of configuration files which are specific to a server mainly iAS & 806 configuration file. If you have configured Shared Application Tier (sharing ORA_TOP as well) in 11i then this problem was overcome by creating conf directory in COMMON_TOP.

In Oracle Apps R12, You can see new TOP called INSTANCE_TOP in Application Tier. This TOP will now hold any configuration files (specific to that server) , Certificates (If SSL is enabled SSL Certificates & any other certificates specific to that server) and related log files or pid files (for Apache or any other process). In diagram tops with <>ST can be shared across servers (Shared Tier) and INSTANCE_TOP will be specific to Server.


Next three pictures are taken from Ivo Dujmovic's presentation at Oracle Open World "Deep Dive: Oracle E-Business Suite Release 12 New Technology Stack". These Pictures are self explanatory & will help you in understanding Oracle Apps R12 File System.
This shows Oracle E-Business Suite Release 12 File System Layout


This shows Directory/File Structure for Instance TOP in R12


This Picture compares file system in 11i with File system in R12

 
 
 
 
Regards,
Sukhwinder Singh

Middle/Application Tier Scripts in R12

Scripts for Application Tier services in R12 are located in
"Install_base/inst/apps/$CONTEXT_NAME/admin/scripts"
where CONTEXT_NAME is of format SID_HOSTNAME

i) adstrtal.sh
Master script to start all components/services of middle tier or application tier. This script will use Service Control API to start all services which are enabled after checking them in context file (SID_HOSTNAME.xml or CONTEXT_NAME.xml)

ii) adstpall.sh
Master script to stop all components/services of middle tier or application tier.

iii) adalnctl.sh
Script to start / stop apps listener (FNDFS and FNDFS). This listener will file will be in 10.1.2 ORACLE_HOME (i.e. Forms & Reports Home)
listener.ora file will be in $INST_TOP/apps/$CONTEXT_NAME/ora/10.1.2/network/admin directory
(Mostly similar to one in 11i with only change in ORACLE_HOME i.e. from 8.0.6 to 10.1.2 )

iv) adapcctl.shScript to start/stop Web Server or Oracle HTTP Server. This script uses opmn (Oracle Process Manager and Notification Server) with syntax similar to opmnctl [startstop]proc ohslike opmnctl stopproc ohs .

(In 11i this script directly used to call apachectl executable but now calls opmnctl which in turn calls apachectl. In 11i web server oracle home was 1.0.2.2.2 but in R12 its 10.1.3)

v) adcmctl.sh
Script to start / stop concurrent manager, Similar to one in 11i. (This script in turn calls startmgr.sh )

vi) adformsctl.sh
Script to start / stop Forms OC4J from 10.1.3 Oracle_Home. This script will also use opmnctl to start/stop Forms OC4J like
opmnctl stopproc type=oc4j instancename=forms

vii) adformsrvctl.sh
This script is used only if you wish to start forms in socket mode. Default forms connect method in R12 is servlet.
If started this will start frmsrv executable from 10.1.2 Oracle_Home in Apps R12
viii) adoacorectl.sh
This script will start/stop oacore OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to adapcctl & adformsctl) to start oacore instance of OC4J like
opmnctl startproc type=oc4j instancename=oacore

ix) adoafmctl.shThis script will start/stop oafm OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to above) to start oacore instance of OC4J like
opmnctl startproc type=oc4j instancename=oafm

x) adopmnctl.sh
This script will start/stop opmn service in 10.1.3 Oracle_Home. opmn will control all services in 10.1.3 Oracle_Home like web server or various oc4j instances. If any services are stopped abnormally opmn will/should start them automatically.

xi) jtffmctl.sh
This script will be used to start/stop one to one fulfilment server.

xii) mwactl.sh
To start / stop mwa telnet server where mwa is mobile application.

Log File Location for Startup Shutdown Services in R12
----------------------------------------------------------------------
Log files for startup/shutdown scripts for application/mid tier in R12 are in $INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log(adalnctl.txt, adapcctl.txt, adcmctl.txt, adformsctl.txt, adoacorectl.txt, adoafmctl.txt, adopmnctl.txt, adstrtal.log, jtffmctl.txt )


Regards,
Sukhwinder Singh

Thursday, March 24, 2011

Queries : Memory, Free Space, Size, Temp Files, Control Files etc..

Database block size:

select to_number(value) "Block size in bytes"
from   sys.v_$parameter
where  name = 'db_block_size';


Max number of possible extents:

select to_number(value)/16-7 "MaxExtents"
from   sys.v_$parameter
where  name = 'db_block_size';



Min extent size:

select to_number(a.value) * to_number(b.value) / 1024 "Min extent size in K"
from   sys.v_$parameter a, sys.v_$parameter b
where  a.name = 'db_block_size'
and  b.name = 'db_file_multiblock_read_count';

List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM

select * from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';

Reports information about your current database context

select 'User: '|| user || ' on database ' || global_name,
' (term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT
from global_name;


Reports free memory available in the SGA

select name,
sgasize/1024/1024 "Allocated (M)",
bytes/1024 "Free (K)",
round(bytes/sgasize*100, 2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s, 
sys.v_$sgastat f
where f.name = 'free memory';
 

Measure the Buffer Cache Hit Ratio

Get initial Buffer Hit Ratio reading

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets' AND phy.name = 'physical reads';

******************************

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
AND phy.name = 'physical reads';


Database users with deadly roles assigned to them.

select grantee, granted_role, admin_option
from sys.dba_role_privs
where granted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 
'IMP_FULL_DATABASE',
'OEM_MONITOR', 
'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 
'ORDSYS',
'TIMESERIES_DBA');

Database users with deadly system privilages assigned to them.

select grantee, privilege, admin_option
from sys.dba_sys_privs
where (privilege like '% ANY %'
or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or admin_option = 'YES')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN', 
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 
'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 
'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 
'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA');
 


Allocated, Used & Free space within datafiles

SELECT   SUBSTR (df.NAME, 1, 40) file_name, 
df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;


Show Used/free space by tablespace name

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;


Total Tablespace size


SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc;


Ivestigating undo-tablespace in EBS R12

SELECT version FROM v$timezone_file;

select count(status) from dba_undo_extents where status = 'ACTIVE';

select count(status) from dba_undo_extents where status = 'UNEXPIRED';

select (sum(bytes)/(1024*1024)) "SIZE in MB",tablespace_name from dba_free_space group by tablespace_name;



Last Analyzed

select max(last_analyzed) from dba_tables;


Listing Memory Used By All Sessions

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3  ;


INDEX :-

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;  

 

SESSION WAITS

SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC; 


Find type of objects available in a tablespace

select ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments ds,dba_objects do where ds.tablespace_name='TOOLS';

Temp tablespace usage information

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

To count no. of segments in each t.s.

select tablespace_name,count(*) from dba_segments group by tablespace_name;

Database growth per month for last year

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
 
 
 
To select the username and the process status


select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b 
where a.requested_by = b.user_id and a.request_id = 677224

select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
,a.argument_text 
from fnd_concurrent_requests a,fnd_user b 
where a.requested_by = b.user_id and a.request_id = 677224
 
 
To select the username,process,status,Terminal name using SID

select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,a.osuser
,c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
To see the currently updated archive log files
SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;



To find the BDUMP,UDUMP directory

select value from v$parameter where name = 'background_dump_dest';
select value from v$parameter where name = 'user_dump_dest';
select value from v$parameter 
where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest');
 
Prompt Showing what sql statement is doing
 
select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
 
 Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, 
c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username "User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.adr and b.paddr(+) = p.addr
order by s.status,1;
  
 
Query to find the ATG rollup Patch level in your application (11i).

connect as apps:

SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc;  


To find RUP Level in R12 : -


select bug_number, creation_date
from ad_bugs
where bug_number in ('6022657','6266113','6728000')
ORDER BY 2;


To Check Maintenance Family Pack Level

select patch_level from fnd_product_installations
where application_id = 275;


Query to find the Apps Version : -

select release_name from fnd_product_groups;


Which FND_USER is locking that table

The column named "module" will tell you the name of the Form Function
or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner ,c.object_name,c.object_type,
       fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time,
      vs.module,vs.machine ,vs.osuser
      ,vlocked.oracle_username,vs.sid,vp.pid
      ,vp.spid AS os_process,vs.serial# ,vs.status ,vs.saddr
      ,vs.audsid ,vs.process

FROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked
    ,v$process vp ,v$session vs,dba_objects c

WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status ,'XX') != 'KILLED';


Security related database initialization parameters and password file users.

select name || '=' || value "PARAMTER"
from   sys.v_$parameter
where  name in ('remote_login_passwordfile', 'remote_os_authent',
                'os_authent_prefix', 'dblink_encrypt_login',
                'audit_trail', 'transaction_auditing');

List security related profile information

select profile, resource_name, limit 
from dba_profiles
where resource_name like '%PASSWORD%'
or resource_name like '%LOGIN%';
 
 

To find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

To find Database Size

The database mainly comprises of datafiles, temp files and redo log files.
The biggest portion of a database’s size comes from the datafiles.

To find out how many megabytes are allocated to all datafiles:
SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;

To get the size of all TEMP files:
SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

To get the size of the on-line redo-logs:
SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

Finally, summing up the three above, total database size can be found:
SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;
 

To find free space in temporary tablesapce:

SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

To find tablespace free space in a database

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
and a.tablespace_name='SYSTEM'
ORDER BY a.tablespace_name;


Tablespaces that have less then 90% free space

select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;
 


Problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc ;


Number of Disk Sorts vs Memory Sorts 

select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size' ;

select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)') ;



Jobs Currently running in the local database.

select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job;


Jobs that have been submitted to run in the local database job queue
select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs  ;

To find Table size in a database


select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where
SEGMENT_NAME = 'TABLE_NAME';

Note: Need to execute as owner of the table. (OR)
select sum(BYTES/1024/1024) as TOTAL_GIG from dba_segments where SEGMENT_NAME='FND_TS_MIG_CMDS';


=== Temp Usage ===

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;
 
SELECT ss.sid, sum(st.blocks)/1024*8 
FROM v$sort_usage st, v$session ss 
where ss.saddr=st.session_addr 
group by ss.sid 
having sum(st.blocks)/1024*8 > 100 order by 2;


SELECT  /*+ RULE */ s.username, s.osuser, s.sid, 
NVL(s.machine,'N/A'), 
NVL(s.module,'N/A'), NVL(s.action,'N/A'), 
NVL(s.program,'N/A'), 
s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr',
u.tablespace, u.contents, u.extents, 
round((u.blocks*8)/1024),s.sql_address, s.sql_hash_value
FROM    v$session s, v$sort_usage u
WHERE   s.saddr    = u.session_addr
AND     u.contents = 'TEMPORARY'
AND     s.audsid != USERENV('sessionid')
AND    (u.blocks*8)/1024 >= 1000
 
ORDER   BY 1,2,3,4,5 Desc;


=== High Redo ===

SELECT s.inst_id,s.sid, s.serial#, s.username, 
s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;


=== Rollback Used ===
                                 
SELECT rn.name, ROUND(rs.rssize/1024/1024),
s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
NVL(s.module,'N/A'), NVL(s.action,'N/A'), 
NVL(s.program,'N/A'),
s.sql_address, s.sql_hash_value, p.spid,
s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
round(ceil((t.used_ublk*8)/1024),1)
FROM   v$rollname rn, v$rollstat rs,
v$session s, v$transaction t, v$process p
WHERE  rn.usn = rs.usn
AND    round((t.used_ublk*8)/1024) >= 1000
AND    rs.usn = t.xidusn
AND    s.sid = p.pid (+)
AND    s.taddr = t.addr
ORDER  BY 2 desc, s.sid ,s.status;


=== Roll back segement Information ====

select tablespace_name, status segment_status, 
count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB" 
from dba_undo_extents 
where tablespace_name like '%UNDO%'
group by tablespace_name, status;


=== Shared Pool Usage ===

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB' 
FROM gv$sgastat  
WHERE name='free memory' AND pool='shared pool';


=== Archive Generation for last 5 hours ===

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,
TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)  
FROM v$log_history 
WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY') 
AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5 
GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'), 
TO_CHAR(first_time, 'HH24') ORDER BY 2;


=== High Memory ===

select s.inst_id, s.sid, name, 
round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512; 


=== Performance ===

select sql_hash_value, count(*) 
from v$session 
where event like 'enq%' 
group by sql_hash_value;


select sql_hash_value,username,osuser, count(*) 
from v$session 
where event like 'enq%' 
and SQL_HASH_VALUE='&event' 
group by sql_hash_value,username,osuser;


select sql_text 
from v$sqlarea 
where hash_value = '&hash_value';


select s1.sid,FETCHES,ROWS_PROCESSED 
from v$sql s,v$session s1 
where s.HASH_VALUE=s1.SQL_HASH_VALUE 
and s1.sid=4885;


select s.sid,s.serial#,s.status,s.username,s.module,
s.osuser,x.event 
from v$session s,v$session_wait x 
where s.paddr in ( select addr 
from v$process 
where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;



select s.sid,s.serial#,s.status,s.username,
s.module,s.osuser,x.event 
from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';



=== Other Script to update the daily report ===

select status,count(1) from gv$session group by status; 

select count(1) from dba_tables where logging='NO';

select distinct status,count(1) from dba_indexes group by status;

select count(1) from dba_objects where status='INVALID';






Regards,
Sukhwinder Singh

Queries : List of completed RMAN backups for the last 24-hours (use info from Dictionary - Control File Views)

select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#;
 
 
Regards,
sukhwinder singh

Queries : Summary of all datafiles and archlogs backed up over the last 24-hours.

Datafiles Backed up during past 24 Hours
 
SELECT dbfiles||' from '||numfiles "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from sys.v_$datafile),
(select count(*) dbfiles
from sys.v_$backup_datafile a, sys.v_$datafile b
where a.file# = b.file#
and a.completion_time > sysdate - 1),
(select count(*) cfiles from sys.v_$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from sys.v_$backup_spfile
where completion_time > sysdate - 1);


Archlog Files Backed up during past 24 Hours
 
SELECT backedup||' from '||archived "Archlog files backed up",
ondisk "Archlog files still on disk"
FROM (select count(*) archived
from sys.v_$archived_log where completion_time > sysdate - 1),
(select count(*) backedup from sys.v_$archived_log
where backup_count > 0
and completion_time > sysdate - 1),
(select count(*) ondisk from sys.v_$archived_log
where archived = 'YES' and deleted = 'NO');
RMAN Backups Still Running: 
SELECT to_char(start_time,'DD-MON-YY HH24:MI') "BACKUP STARTED",
sofar, totalwork,
elapsed_seconds/60 "ELAPSE (Min)",
round(sofar/totalwork*100,2) "Complete%"
FROM sys.v_$session_longops
WHERE opname = 'dbms_backup_restore';
 
Regards,
Sukhwinder singh

Performance Tuning Queries : -

Shows active (in progress) transactions  
select username, terminal, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr;

Display rollback segment statistics
Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks "# Shrinks", rs.Extends "# Extends"
from sys.v_$RollName rn, sys.v_$RollStat rs
where rn.usn = rs.usn;
Display database sessions using rollback segments
SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
and t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io;
Regards,
Sukhwinder Singh

Block developers from using TOAD and other tools on production databases.

CONNECT / AS SYSDBA;

CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed on PROD DB!');
END IF;
END;
/
SHOW ERRORS
 
 
Regards,
Sukhwinder Singh

Steps to resolve locks on database

Error Message:Could Not Reserve Records Due to Database Record Lock

1. Identify the Oracle serial ID, SID ID and terminate without shutting the
database down.

1.1 Make sure that the user is logged off.

1.1.1 Type ps -ef |grep

1.1.2 Kill all processes related to that user.

1.2 Identify SID, serial#

select distinct
acc.object, ses.osuser, ses.process,
ses.sid, ses.serial#
from v$access acc,
v$session ses
where (acc.owner != 'SYS'
or acc.object = 'PLAN_TABLE')
and acc.sid = ses.sid
and ses.status != 'INACTIVE'
and ses.type != 'BACKGROUND'
and acc.object not in ('V$ACCESS','V$SESSION')
and ses.audsid != userenv('SESSIONID')
order by 1,2,3
/

1.3. Double-check the identified SID and serial ID:

SELECT osuser,
username,
process,
sid,
serial#,
status,
to_char(logon_time,'DD-MON HH24:MI:SS') logon_time,
machine,
program
FROM v$session
WHERE sid = &SID_NUM
/

1.4. ALTER SYSTEM KILL SESSION '&SID_NUM,&SERIAL_NUM';
Alternatively use the following scripts to identify the blocking session:

-- check for locked tables
select a.object_id, a.session_id, substr(b.object_name, 1, 40)
from v$locked_object a,
dba_objects b
where a.object_id = b.object_id
order by b.object_name ;

--find_blocked.sql
select sid,
decode(block ,0,'NO','YES') BLOCKER,
decode(request,0,'NO','YES') WAITER
from v$lock
where request > 0 or block > 0 order by block desc
/

SELECT 'alter system kill session '''||vs.sid||','||vs.serial#||'''' ,al.
object_name, al.object_type, vs.status,
fu.user_name,vs.process,vs.osuser,vs.username,
to_char(vs.logon_time,'DD-MON HH24:MI:SS') logon_time, vs.program
FROM fnd_logins fl, fnd_user fu, all_objects al, v$lock vl, v$session vs
WHERE fl.pid = vl.sid
AND vl.id1 = al.object_id (+)
AND fl.user_id = fu.user_id
AND to_char (start_time, 'DD-MON-RR') = to_char (sysdate, 'DD-MON-RR')
and vs.sid=vl.sid
and vl.sid = &sid

'ALTERSYSTEMKILLSESSION'''||VS.SID||','||VS.SERIAL#||''''


Regards,
Sukhwinder Singh

Queries on maintenance mode


Query to know status of maintenance mode:
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

Query to ENABLE of maintenance mode:
sqlplus -s &un_apps/******** @/tcme3i/applmgr/1159/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLE

Query to DISABLE of maintenance mode:
sqlplus -s &un_apps/******** @/tcme3i/applmgr/1159/ad/11.5.0/patch/115/sql/adsetmmd.sql DISABLE  

Regards,
Sukhwinder Singh

How to find Components version in R12

Apache Version
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v


Go to reports path Then type: -

string -a APXAPRVL.rdf|grep Header

Perl Version
$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version
sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode
cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

How to find Apps Version
select release_name from apps.fnd_product_groups;

Web Server/Apache or Application Server in Apps 11i/R12
Log in as Application user, set environment variable and run below query $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
  

Forms & Report version in R12/12i
Log in as Application user, set environment variable and run below query
$ORACLE_HOME/bin/rwrun | grep Release

 Oracle Jinitiator in 11i/R12/12i
 
Log in as Application user, set environment variable and run below query
grep jinit_ver_comma $CONTEXT_FILE

(Default is Java Plug-In for R12/12i )
Oracle Java Plug-in in 11i/R12/12i
A.
Log in as Application user, set environment variable and run below query
grep plugin $CONTEXT_FILE.

File Version on file system
adident Header
or
strings | grep Header
Here adident is AD Utility (Oracle Apps) and strings is Unix utility.

Version of pld file
*.pld are source code of *.pll which are inturn source of *.plx.  *.pll is in $AU_TOP/resource and to find its version check

adident Header $AU_TOP/resource/.pll
IGSAU012.pll:
$Header IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $
or
strings $AU_TOP/resource/.pll | grep -i header
FDRCSID(’$Header: IGSAU012.pld 115.1.115100.1 2004/04/01 05:40:18 appldev ship $’);

Workflow Version with Apps
select TEXT Version from   WF_RESOURCES where  NAME = ‘WF_VERSION’;
 
Identity Management component Version/Release Number

Oracle Single Sign On
select version from orasso.wwc_version$;
Oracle Internet Directory
There are two component in OID (Software/binaries & Schema/database)
To find software/binary version
$ORACLE_HOME/bin/oidldapd -version
To find Schema Version/ database use
ldapsearch -h -p -D “cn=orcladmin” -w “” -b “” \ -s base “objectclass=*” orcldirectoryversion 
select attrval from ods.ds_attrstore where entryid = 1 and attrname = ‘orcldirectoryversion’;

 Application Server
Oracle Application Server 10g Rel 3 (10.1.3.X)
cat $ORACLE_HOME/config/ias.properties | grep Version
Version=10.1.3.0.0 
For Oracle Application Server 10.1.2 (Prior to Oracle WebLogic Server)
If application server is registered in database (Portal, Discoverer) check from database
select * from ias_versions;
or
select * from INTERNET_APPSERVER_REGISTRY.SCHEMA_VERSIONS;
AOC4J (Oracle Container for J2EE)
Set ORACLE_HOME
cd $ORACLE_HOME/j2ee/home
java -jar oc4j.jar -version 

Oracle Portal
select version from portal.wwc_version$; 

Database Component 
To find database version
select * from v$version;
or
All component version in database
$ORACLE_HOME/OPatch/opatch lsinventory -detail
Unix Operating System
Solaris -> cat /etc/release
Red Hat Linux -> cat /etc/redhat-release



Regards,
Sukhwinder Singh

Queries : Imp DBA Queries : -

To change an Oracle password temporarily

In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select username,password from dba_users where username='GEORGE';

USERNAME     PASSWORD
---------------- ----------------
GEORGE        F894844C34402B67

SQL> alter user george identified by welcome123;

Now login with the following credentials: george/welcome(old passwd)
After doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user george identified by values 'F894844C34402B67';
 

To verify OATM Migration

select migration_status,error_text from FND_TS_MIG_CMDS where migration_status ='ERROR'

select * from all_objects where object_name like 'FND_TS%' and object_type='TABLE'
select * from FND_TS_MIG_STATUS
select * from FND_TS_SIZING
select IS_NEW_TS_MODE from fnd_product_groups (o/p should be “Y”);

To get workflow using WFLOAD

WFLOAD apps/W1tty 0 Y DOWNLOAD <<(d/w file)I2PATCARD_prd.wft>> <<(original file) PATCARD>>


Display status of all the Concurrent Managers

 Select distinct Concurrent_Process_Id CpId, PID Opid,
      Os_Process_ID Osid,
      Q.Concurrent_Queue_Name Manager,
      P.process_status_code Status,
      To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
      from  Fnd_Concurrent_Processes P,
      Fnd_Concurrent_Queues Q, FND_V$Process
      where  Q.Application_Id = Queue_Application_ID
      And (Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID)
      b( Spid = Os_Process_ID )
      And  Process_Status_Code not in ('K','S')
      Order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name ;





Backup Verification Steps: -

select count(*) from v$recover_file;

select count(*) from v$recovery_log;

select count(*) from v$recovery_status;

select count(*) from v$recovery_file_status;

select name,status from v$datafile where (name like '%MISS%' or status not in ('ONLINE', 'SYSTEM'));

select count(*) from v$backup where status != 'NOT ACTIVE' ;

Select distinct checkpoint_change# from v$datafile  ;

select distinct to_char(CHECKPOINT_TIME,'DD-MON-YYYY HH24:MI:SS') from v$datafile_header;

 



 
Regards,
Sukhwinder Singh

Wednesday, March 23, 2011

Control File Autobackups

One of the many features included in rman is the ability to automatically backup your control file. What is a control file? The control file contains information about your database including its physical structure. If you are using rman without a recovery catalog then your backup information is also stored in the control file.

It is very important for (some) recovery purposes to ensure you have a recent copy of your control file, especially if it contains your recovery catalog. There is one feature of control file autobackups which alot of dba's seem to be unfamiliar with, which is why I thought i'd write up a brief article on it. If you have this feature enabled and you make a structural change to the database, the control file is automatically backed up. ie, If you add a datafile, rename a file, etc. Information which could affect your ability to recover. This backup will be placed on disk even if your regular backup goes to tape.

A quick test. First, ensure you have control file autobackups enabled and select a location on disk. By default the control file backup will be placed in your $ORACLE_HOME/dbs directory unless you specify a location.

oravis@myserver=> rman target / nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 14:48:49 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: VIS (DBID=767668735)
using target database control file instead of recovery catalog

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';y


A quick test to make sure the backup works and is on disk:


oravis@myserver=> rman target / nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 15:25:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: VIS (DBID=767668735)
using target database control file instead of recovery catalog

RMAN> backup current controlfile;

Starting backup at 16-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=414 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 16-JAN-08
channel ORA_DISK_1: finished piece 1 at 16-JAN-08
piece handle=/mnt1/app/visdb/1020_64bit/dbs/01j6adrd_1_1 tag=TAG20080116T152532 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 16-JAN-08

Starting Control File Autobackup at 16-JAN-08
piece handle=/oradata/bck/vis_backup/c-767668735-20080116-00 comment=NONE
Finished Control File Autobackup at 16-JAN-08

RMAN> exit

Recovery Manager complete.
oravis@myserver=> ls -alrt /oradata/bck/vis_backup
total 42036
drwxrwxr-x 28 oravis dba 1024 Jan 16 14:47 ..
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:25 c-767668735-20080116-00
drwxrwxr-x 2 oravis dba 512 Jan 16 15:25 .


Next we need to make a structural change to the database to see if the control file is automatically backed up. For this example i'll add a small datafile to the system tablespace.


SQL> alter tablespace SYSTEM add datafile '/oradata/dbf/visdata/system09.dbf' size 25M;

Tablespace altered.


A directory listing of the backup location should show a second control file backup:


oravis@myserver=> ls -alrt /oradata/bck/vis_backup
total 79972
drwxrwxr-x 28 oravis dba 1024 Jan 16 14:47 ..
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:25 c-767668735-20080116-00
-rw-rw---- 1 oravis dba 19398656 Jan 16 15:26 c-767668735-20080116-01
drwxrwxr-x 2 oravis dba 512 Jan 16 15:26 .




Regards,
Sukhwinder Singh

R12: Changing apps password

In 11i there were a few steps in order to change the apps password.

1. Shutdown the environment.
2. execute FNDCPASS to change the apps password.
Ex. FNDCPASS apps/[oldpassword] 0 Y system/[system password] SYSTEM APPLSYS [new apps password]

3. Manually modify files such as appsweb.cfg and wdbsvr.app
4. Startup the environment.


In R12:

1. Shutdown the environment.
2. Execute FNDCPASS as above.
3. Run Autoconfig
4. Startup the environment.

I realized the process had changed when I looked at the wdbsvr.app file and noticed the following:

;
; $Header: wdbsvr.app 120.0.12000000.2 2007/06/29 11:43:45 sbandla ship $
;
; $AutoConfig$
;
; Not required in R12. Stubbing the file from %s_fndtop%/admin/template

 
Regards,
Sukhwinder Singh

Cloning from an RMAN backup using duplicate database - R12

Cloning Oracle Applications Release 12 with Rapid Clone.

Here are the steps:
  1. Execute preclone on all tiers of the source system. This includes both the database and application tiers. (For this example, TEST is my source system.)

    For the database execute: $ORACLE_HOME/appsutil/scripts/<context>/adpreclone.pl dbTier
    Where context name is of the format <sid>_<hostname>

    For the application tier: $ADMIN_SCRIPTS_HOME/adpreclone.pl appsTier
  2. Prepare the files needed for the clone and copy them to the target server.
    • Take a FULL rman backup and copy the files to the target server and place them in the identical path. ie. if your rman backups go to /u01/backup on the source server, place them in /u01/backup on the destination server. To be safe, you may want to copy some of the archive files generated while the database was being backed up. Place them in an identical path on the target server as well.
    • Application Tier: tar up the application files and copy them to the destination server. The cloning document referenced above ask you to take a copy of the $APPL_TOP, $COMMON_TOP, $IAS_ORACLE_HOME and $ORACLE_HOME. Normally I just tar up the System Base Directory, which is the root directory for your application files.
    • Database Tier: tar up the database $ORACLE_HOME.

      ex. from a single tier system. The first tar file contains the application files and the second is the database $ORACLE_HOME

      [oratest@myserver TEST]$ pwd
      /u01/TEST
      [oratest@myserver TEST]$ ls
      apps db inst
      [oratest@myserver TEST]$ tar cvfzp TEST_apps_inst_myserver.tar.gz apps inst
      .
      .
      [oratest@myserver TEST]$ tar cvfzp TEST_dbhome_myserver.tar.gz db/tech_st

      Notice for the database $ORACLE_HOME I only added the db/tech_st directory to the archive. The reason is that the database files are under db/apps_st and we don't need those.
    • Copy the tar files to the destination server, create a directory for your new environment, for example /u01/DEV. (For the purpose of this article I will be using /u01/DEV as the system base for the target envrionment we are building and myserver is the server name.)
    • Extract each of the tar files with the command tar xvfzp

      Ex. tar xvfzp TEST_apps_inst_myserver.tar.gz
  3. Configure the target system.
    • On the database tier execute adcfgclone.pl with the dbTechStack parameter.

      For example. /u01/DEV/db/tech_st/10.2.0/appsutil/clone/bin/adcfgclone.pl dbTechStack

      By passing the dbTechStack parameter we are tell the script to configure only the necessary $ORACLE_HOME files such as the init file for the new environment, listener.ora, database environment settings file, etc. It will also start the listener.

      You will be prompted the standard post cloning questions such as the SID of the new environment, number of DATA_TOPS, Oracle Home location, port settings, etc.

      Once this is complete goto /u01/DEV/db/tech_st/10.2.0 and execute the environment settings file to make sure your environment is set correctly.

      [oradev@myserver 10.2.0] . ./DEV_myserver.env
  4. Duplicate the source database to the target.
    • In order to duplicate the source database you'll need to know the scn value to recover to. There are two wasy to do this. The first is to login to your rman catalog, find the Chk SCN of the files in the last backupset of your rman backup and add 1 to it.

      Ex. Output from a rman> List backups
      .
      .
      List of Datafiles in backup set 55729
      File LV Type Ckp SCN Ckp Time Name
      ---- -- ---- ---------- --------- ----
      7 1 Incr 5965309363843 15-JUN-09 /u02/TEST/db/apps_st/data/owad01.dbf
      .

      .
      So in this case the SCN we would be recovery to is 5965309363843 + 1 = 5965309363844.

      The other method is to login to the rman catalog via sqlplus and execute the following query:

      select max(absolute_fuzzy_change#)+1,
      max(checkpoint_change#)+1
      from rc_backup_datafile;


      Use which ever value is greater.
    • Modify the db_file_name_convert and log_file_name convert parameters in the target init file. Example:

      db_file_name_convert=('/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
      '/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')

      log_file_name_convert=(/u02/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/',
      '/u01/PROD/db/apps_st/data/', '/u02/DEV/db/apps_st/data/')
    • Verify you can connect to source system from the target as sysdba. You will need to add a tns entry to the $TNS_ADMIN/tnsnames.ora file for the source system.
    • Duplicate the database. Before we use rman to duplicate the source database we need to start the target database in nomount mode.

      Start rman:

      rman target sys/<syspass>@TEST catalog rman/rman@RMAN auxiliary /

      If there are no connection errors duplicate the database with the following script:

      run {
      set until scn 5965309363844;
      allocate auxiliary channel ch1 type disk;
      allocate auxiliary channel ch2 type disk;
      duplicate target database to DEV }


      The most common errors at this point are connection errors to the source database and rman catalog. As well, if the log_file_name_convert and db_file_name_convert parameters are not set properly you will see errors. Fix the problems, login with rman again and re-execute the script.

      When the rman duplicate has finished the database will be open and ready to proceed with the next steps.
    • Execute the library update script:

      cd $ORACLE_HOME/appsutil/install/DEV_myserver where DEV_myserver is the <context_name> of the new environment.

      sqlplus "/ as sysdba"@adupdlib.sql

      If your on linux replace with so, HPUX with sl and for windows servers leave blank.
    • Configure the target database

      cd $ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl dbconfig

      Where is $ORACLE_HOME/appsutil/DEV_myserver.xml
  5. Configure the application tier.

    cd /u01/DEV/apps/apps_st/comn/clone/bin
    perl adcfgclone.pl appsTier

    You will be prompted the standard cloning questions consisting of the system base directories, which services you want enabled, port pool, etc. Make sure you choose the same port pool as you did when configuring the database tier in step 3.

    Once that is finished, initialize your environment by executing

    . /u01/DEV/apps/apps_st/appl/APPSDEV_myserver.env

  6. Shutdown the application tier.

    cd $ADMIN_SCRIPTS_HOME
    ./adstpall.sh apps/<source apps pass>
  7. Login as apps to the database and execute:

    exec fnd_conc_clone.setup_clean;

    I don't believe this step is necessary but if you don't do this you will see references to your source environment in the FND_% tables. Every time you execute this procedure you need to run autoconfig on each of the tiers (db and application). We will get to that in a second.
  8. Change the apps password. Chances are you don't want to have the same apps password as the source database, so its best to change it now while the environment is down.

    With the apps tier environment initialized:

    FNDCPASS apps/<source apps pass> 0 Y system/<source system pass>> SYSTEM APPLSYS <new apps pass>
  9. Run autoconfig on both the db tier and application tier.

    db tier:
    cd $ORACLE_HOME/appsutil/scripts/DEV_myserver
    ./adautocfg.sh

    Application Tier
    cd $ADMIN_SCRIPTS_HOME
    ./adautocfg.sh
  10. If there are no errors with autoconfig start the application. Your already in the $ADMIN_SCRIPTS_HOME so just execute:

    ./adstrtal.sh apps/<new apps pass>
  11. Login to the application and perform any post cloning activities. You may want to override the work flow email address so that notifications goto a test/dev mailbox instead of users. We always change the colors and site_name profile options, etc. More details can be found in Section 3: Finishing tasks of the R12 cloning document referenced earlier.
Thats it, hopefully now you have successfully cloning an EBS environment using rman duplicate. 


Regards,
Sukhwinder Singh
There was an error in this gadget