Oracle Applications Implementation Consultant

Useful scripts for Oracle Applications DBA

2012-01-15T00:23:00.000-08:00

Query to find the request group of a concurrent program
select distinct request_group_name from apps.FND_REQUEST_GROUPS FRG,apps.FND_REQUEST_GROUP_UNITS FRGU,apps.FND_CONCURRENT_PROGRAMS_VL FCP
where FCP.USER_CONCURRENT_PROGRAM_NAME like '&Conc_Program_Name' and FCP.CONCURRENT_PROGRAM_ID=FRGU.REQUEST_UNIT_ID
and FRGU.REQUEST_GROUP_ID=FRG.REQUEST_GROUP_ID

Query to check the middle tier sessions
select machine,
sum(decode(s.status,'ACTIVE',1,0)) totact,
sum(decode(s.status,'INACTIVE',1,0)) totinact,
count(*) totsess,
min(to_char(s.logon_time,'mm/dd hh24:mi')) earliest_logon,
max(to_char(s.logon_time,'mm/dd hh24:mi')) latest_logon
from v$session s
where s.type = 'USER'
group by s.machine
order by 4,1
/

FND_NODES query
select
node_id,
platform_code,
support_db D,
support_cp C,
support_admin A,
support_forms F,
support_web W,
node_name,
server_id,
server_address,
domain,
webhost,
virtual_ip
from
fnd_nodes
order by node_id;

Query to know what changes a patch has made on the instance
Select
J.PATCh_NAME,
H.APPLICATIONS_SYSTEM_NAME Instance_Name,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME appl_top,
D.SUBDIR,
D.FILENAME,
max(F.VERSION) latest,
E.ACTION_CODE action
from
AD_BUGS A,
AD_PATCH_RUN_BUGS B,
AD_PATCH_RUN_BUG_ACTIONS C,
AD_FILES D,
AD_PATCH_COMMON_ACTIONS E,
AD_FILE_VERSIONS F,
AD_PATCH_RUNS G,
AD_APPL_TOPS H,
AD_PATCH_DRIVERS I,
AD_APPLIED_PATCHES J
where
A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and
C.FILE_ID = D.FILE_ID and
E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and
D.FILE_ID = F.FILE_ID and
G.APPL_TOP_ID = H.APPL_TOP_ID and
G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and
B.PATCH_RUN_ID = G.PATCH_RUN_ID and
C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = '&Patch_Number'))
GROUP BY
J.PATCH_NAME,
H.APPLICATIONS_SYSTEM_NAME,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME,
D.SUBDIR,
D.FILENAME,
E.ACTION_CODE;

Query to know patch details in the instance.
select
D.PATCH_NAME,
B.APPLICATIONS_SYSTEM_NAME,
-- B.INSTANCE_NAME,
B.NAME,
C.DRIVER_FILE_NAME,
A.PATCH_DRIVER_ID,
A.PATCH_RUN_ID,
A.SESSION_ID,
A.PATCH_TOP,
A.START_DATE,
A.END_DATE,
A.SUCCESS_FLAG,
A.FAILURE_COMMENTS
from
AD_PATCH_RUNS A,
AD_APPL_TOPS B,
AD_PATCH_DRIVERS C,
AD_APPLIED_PATCHES D
where
A.APPL_TOP_ID = B.APPL_TOP_ID AND
A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID AND
C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID AND
A.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID
in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = '&patch_number'))
ORDER BY 3;

Query to show the applications users currently connected based on the sid.
select to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
u.user_name,
u.description ,
s.sid,
p.spid,
u.description,
a.user_form_name
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and
((upper(a.user_form_name) like upper('%&trgtform%')) or (upper('&trgtform') = 'ALL'))
and l.start_time = (select max
(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by
u.user_name, u.description,a.time,a.user_form_name, s.sid,p.spid
order by to_char(max(l.start_time),'mm/dd/yy
hh:mi:ssAM'),a.time;

Query to see the top10 jobs based on total runtime for today
select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name
' (' to_char
(c.concurrent_program_id) ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl
(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl
(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
from APPLSYS.fnd_Concurrent_requests
a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id =
c.concurrent_program_id
and a.program_application_id = c.application_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and
ctl.language = 'US'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and
actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name ' (' to_char
(c.concurrent_program_id) ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11; Query to get the application user name based on the SID. select /*+ rule */ to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat, a.time, floor (s.last_call_et/3600)':' floor(mod(s.last_call_et,3600)/60)':' mod(mod(s.last_call_et,3600),60) "LastCallET", u.user_name, u.description , s.module ' - ' a.user_form_name forminfo from applsys.fnd_logins l, applsys.fnd_user u, apps.fnd_signon_audit_view a, v$process p, v$session s where s.sid = &trgtsid and s.paddr = p.addr and p.pid = l.pid and l.end_time is null and l.spid = s.process and l.start_time is not null -- and l.start_time = u.last_logon_date -- and l.session_number = u.session_number and l.user_id = u.user_id and u.user_id = a.user_id and p.pid = a.pid and l.start_time = (select max(l2.start_time) from applsys.fnd_logins l2 where l2.pid = l.pid) group by to_char (s.logon_time,'mm/dd/yy hh:mi:ssAM'), floor(s.last_call_et/3600)':' floor(mod (s.last_call_et,3600)/60)':' mod(mod(s.last_call_et,3600),60), u.user_name, u.description,a.time,s.module ' - ' a.user_form_name order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time; Query to get the server tiers active sessions select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid, to_char(s.logon_time,'mm/dd hh24:mi') logonat, floor(last_call_et/3600)':' floor(mod(last_call_et,3600)/60)':' mod(mod(last_call_et,3600),60) "LastCallET", s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid, decode(h.sid,null,'None',h.name) holdinglatch from v$session s, v$session_wait w, v$process p, v$latchholder h where type = 'USER' and status = 'ACTIVE' and s.sid = w.sid and s.paddr = p.addr and s.osuser is not null and s.sid = h.sid (+) order by 1,4

Script to generate an Automatic workload repository report.
SQL> @E:\R12\11.1.0\RDBMS\ADMIN\awrrpt.sql

Current Instance DB Id DBName Instance
----------- ----------------
402873117 KLONE klone

Enter value for report_type: <---here you can enter 'html' or 'text' based on the output type you want.

Enter value for num_days:<---here enter the number of days will result in the most recent days of snapshots being listed.
Example if we enter 2 here will show two days of snapshots that are automatically stored in the database.

Enter value for begin_snap:<---here enter the begin snapshot number

Enter value for end_snap:<---here enter the end snapshot number

Enter value for report_name:<---awrrpt_1_2579_2580.html

This will generate the html report output for the performance analysis.

How to identify and remove locks on the table
Login to database as SYS or SYSTEM user.
Find out the SESSION_ID by running the following
SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = Table Name;

Use this session id to find SERIAL# by using following

SELECT statment SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = Table Name)

Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

Query to check database growth in previous months
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 creation_time

Query to see the top 10 io datafiles for write activity
select * from (
select substr(f.name,8,(instr(f.name,'/',9,1) -8)) fsmtpt,
substr(f.name,(instr(f.name,'/',-1,1)+1)) fname,
s.phyrds, s.phywrts,s.lstiotim,s.avgiotim
from v$datafile f, v$filestat s
where f.file# = s.file#
order by 4 desc)
where rownum < 11;

Don't hesitate to contact me if you need any technical assistance for Oracle Applications DBA related work. samiora@gmail.com.

All about Oracle Concurrent Manager

2011-12-05T06:08:00.000-08:00

The concurrent manager in Oracle EBS is a very important service. It manages the concurrentrequests submitted by users and does batch processing and report generation.

This blog article will help administrators to get insight of the concurrent manager and also control over its functionality.

Master Concurrent Managers
There are many concurrent managers in Oracle EBS application like Output Post Processor (related to XML publisher reports), Workflow Agent Listener Service (related to workflow notifications), Workflow Mailer Service (related to workflow notification mailer setup), Workflow Document Web Services Service, etc. But among all these concurrent managers are the three super concurrent managers and they are,

Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one of them has a failure.

Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.

Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.

Enable Tracing for Concurrent Programs
For troubleshooting purposes if you want to enable tracing for a concurrent program then goto System Administrator-->Concurrent-->Program-->Define-->Here tick the checkbox 'Enable Trace' for the specific concurrent program that you want to generate trace for.

Now let's see as an Apps DBA, how to monitor and Tune the concurrent manager;

Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.

PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.

Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.

Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.

Purging Concurrent Requests
It is recommended to run this program 'Purge concurrent request/manager data' once every week as a meintenance task. Sometimes some report output files doesn't get deleted so it is required to Set the system profile option "Concurrent: Force Local Output File Mode" to "Yes". You need to apply patch 7530490 for R12 (or) 7834670 for 11i to get this profile.

Refer Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance

Note:- The profile option "Concurrent: Force Local Output File Mode" is set to "No" by default. After applying the patch, set the profile option to YES will
cause FNDCPPUR to always access files on the local file system, hence FNDCPPUR will remove the OS files faster.To enable this feature, All Concurrent Manager nodes must be able to access the output file location via the local filesystem.
Ensure that the log/out files are removed from the locations shown below as you run "Purge Concurrent Request and/or Manager Data program".
$APPLCSF/$APPLLOG==>P:\oracle\inst\apps\proderp_prodapp\logs\appl\conc\log $APPLCSF/$APPLOUT==>P:\oracle\inst\apps\proderp_prodapp\logs\appl\conc\out

Truncate the reports.log file in log directory.
Refer Note.844976.1 for more details.
Truncation of file "reports.log" is a regular maintenance work of Application DBA. Make sure that reports log file size should not increase to its maximum limit of 2 GB. There is no purge program to truncate file "reports.log". This maintenance needs to be done manually and regularly depending on number of concurrent program which uses "reports.log". You can safely truncate "reports.log". The "reports.log" file can be located under $APPLCSF/$APPLLOG.

Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.

Analyzing Oracle Apps Dictionary Tables for High Performance

It is also very important to run the request Gather Table Statistics on these tables:
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace. A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications. Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace. In the first picture given above you can check the box Enable Trace.

Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.

Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.

When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column (check second picture on this page).

Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:

update fnd_concurrent_requests set status_code='X', phase_code='C'where status_code='T';

Changing Dispatching Priority within the Concurrent Manager
(Check first picture on this page) If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.

Using data Dictionary Scripts with the Concurrent Manager
Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.

Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:

afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.

afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql Displays the requests that are pending, held, and scheduled.

afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.

afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:

SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcr.program_application_id = fcp.application_id
and
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.program_application_id = fcpt.application_id
and
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;

Note that this script prompts you for the number of minutes.

Conclusion
The Oracle Concurrent Managers remain one of Oracle most important components for the Oracle Applications eBusiness suite and they perform an important TP monitor function. It is only by properly understand the functions and tuning of the Concurrent Managers that the Oracle Apps DBA can be successful in keep their sophisticated Applications optimizer for high-performance.

References: Oracle Metalink and sir Burlesons site.

If you have any question please don't hesitate to email me at samiora@gmail.com

How to apply patch when adpatch is running

2011-11-01T23:41:00.000-07:00

How to apply patch when adpatch is currently running?

Some times you might apply a patch and during the process the patch failed at some workers, you search on metalink and find out that you need to apply patch in order to solve this failure, here in this case you have two options either to cancel the current patch and apply the fixing patch then reapply the patch again or you can do the following (apply the fixing patch without the need to reapply the first patch and its very helpful when you applying a huge patch).

Steps to do that are:
1. Using the adctrl utility, shutdown the workers.
2. Backup the FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables.
3. Backup the .rf9 files located in $APPL_TOP/admin//restart directory.
4. Drop the FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables.
5. Apply the new patch. Once this new patch is appiled successfully, then restart the previous failed patch by the following steps.
6. Restore the backed up .rf9 files.
7. Restore the FND_INSTALL_PROCESSES table.
8. Restore the AD_DEFERRED_JOBS table.
9. Re-create synonyms for APPLSYS.AD_DEFERRED_JOBS and APPLSYS.FND_INSTALL_PROCESSES.
10. Start adpatch, it will resume where it stopped previously.

If you need any assistance please don't hesitate to email me [samiora@gmail.com]

Linux Logical Volume Manager LVM

2011-10-16T01:33:00.000-07:00

PARTITION--->PHYSICAL VOLUME ---> VOLUME GROUP ---> LOGICAL VOLUME

-> LVM is a method of allocating hard drive space into logical volumes that can be easily resized.
-> With LVM, the hard drive or set of hard drives is allocated to one or more Physical Volumes.
-> The Physical volume PV are then combined into Volume Groups VG.

Steps for creating normal volumes

1. Create partition
# fdisk /dev/sda
:l <- this gives all ID's i.e. list all known partion types
:t <- to change partition's system ID
:9 <- example partion number to be created
:8e <- 'linux LVM' system partion type code
:p <- print the partition table
:w <- write talbe to disk and exit

The above steps create a new partition with 'linux LVM' type.

2. Check and create physical volumes

[root@dregpodb /]# pvdisplay

--- Physical volume ---
PV Name /dev/dm-8
VG Name vg01
PV Size 150.00 GB / not usable 4.00 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 38399
Free PE 0
Allocated PE 38399
PV UUID kuEpoH-xXL0-c7LU-Vf6Q-7Uj7-xxKn-MmmZLV

--- Physical volume ---
PV Name /dev/cciss/c0d0p2
VG Name vg00
PV Size 136.20 GB / not usable 14.56 MB
Allocatable yes
PE Size (KByte) 32768
Total PE 4358
Free PE 102
Allocated PE 4256
PV UUID 12osfI-bJBr-NtUy-0mo7-j3ZD-Aeqx-9m0N3P

[root@dregpodb /]#pvcreate /dev/dm-9

[root@dregpodb /]# pvdisplay
--- Physical volume ---
PV Name /dev/dm-9
VG Name vg02
PV Size 100.00 GB / not usable 4.00 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 25599
Free PE 0
Allocated PE 25599
PV UUID 6Mjkim-P13j-Afu2-pUE7-E4l2-VfyB-dZ68VE

--- Physical volume ---
PV Name /dev/dm-8
VG Name vg01
PV Size 150.00 GB / not usable 4.00 MB
Allocatable yes (but full)
PE Size (KByte) 4096
Total PE 38399
Free PE 0
Allocated PE 38399
PV UUID kuEpoH-xXL0-c7LU-Vf6Q-7Uj7-xxKn-MmmZLV

--- Physical volume ---
PV Name /dev/cciss/c0d0p2
VG Name vg00
PV Size 136.20 GB / not usable 14.56 MB
Allocatable yes
PE Size (KByte) 32768
Total PE 4358
Free PE 102
Allocated PE 4256
PV UUID 12osfI-bJBr-NtUy-0mo7-j3ZD-Aeqx-9m0N3P

3. Volume Group creation
#vgcreate vg02 /dev/dm-9

[root@dregpodb /]# vgdisplay
--- Volume group ---
VG Name vg02
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 1
Act PV 1
VG Size 100.00 GB
PE Size 4.00 MB
Total PE 25599
Alloc PE / Size 25599 / 100.00 GB
Free PE / Size 0 / 0
VG UUID LdtcHj-g26p-BSRu-g9Wi-vcqj-dQki-nGoM2e

--- Volume group ---
VG Name vg01
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 2
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 1
Act PV 1
VG Size 150.00 GB
PE Size 4.00 MB
Total PE 38399
Alloc PE / Size 38399 / 150.00 GB
Free PE / Size 0 / 0
VG UUID dhdeBn-kdIP-frWp-gODa-9X6f-0dW7-rOAmzQ

--- Volume group ---
VG Name vg00
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 9
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 8
Open LV 8
Max PV 0
Cur PV 1
Act PV 1
VG Size 136.19 GB
PE Size 32.00 MB
Total PE 4358
Alloc PE / Size 4256 / 133.00 GB
Free PE / Size 102 / 3.19 GB
VG UUID JYPuCP-9ezo-hg8v-pvE9-1GAL-8o4F-UhezPC

4. Logical Volume Creation

# lvcreate -L 100g /dev/vg02 -n /dev/vg02/lvol01

[root@dregpodb /]# lvdisplay -v
Finding all logical volumes
--- Logical volume ---
LV Name /dev/vg02/lvol01
VG Name vg02
LV UUID T5EIW5-OiIS-WhvA-Wp0d-X0mL-4tKF-GxXY4u
LV Write Access read/write
LV Status available
# open 2
LV Size 100.00 GB
Current LE 25599
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 253:10

5. Format and mount logical volumes

#mkfs.ext3 /dev/vg02/lvol01
#mkdir /u01
#mount /dev/vg02/lvol01 /u01
#df -h
[root@dregpodb /]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg00-lvol01
9.7G 435M 8.8G 5% /
/dev/mapper/vg00-lvol04
12G 3.4G 7.7G 31% /usr
/dev/mapper/vg00-lvol03
9.7G 157M 9.1G 2% /home
/dev/mapper/vg00-lvol05
9.7G 336M 8.9G 4% /var
/dev/mapper/vg00-lvol08
49G 42G 4.1G 92% /u03
/dev/mapper/vg00-lvol07
9.7G 187M 9.1G 2% /tmp
/dev/mapper/vg00-lvol06
15G 320M 14G 3% /opt
/dev/cciss/c0d0p1 487M 21M 441M 5% /boot
tmpfs 3.9G 0 3.9G 0% /dev/shm
/dev/mapper/vg01-lvol0
148G 33G 108G 24% /u02
/dev/mapper/vg02-lvol01
99G 42G 52G 45% /u01

6. Resizing the logical volume

#lvresize -L +50g /dev/vg02/lvol01 <-This will add 50g to existing 100g size
#resize2fs /dev/vg02/lvol01 <-This will update the kernel with changes

7. Extending size of a volume group

#vgdisplay
#vgextend /dev/vg02 /dev/sda12
#vgdisplay

To check graphically about volumes use the following tool,
#system-config-lvm &

--------Additional commands------------
eject <-- To eject cd-rom device
eject -t <-- To insert the cd-rom device
init 6 <-- to reboot server

hwbrowser &<-- To view available free space of a disk
df -h <-- To view free space in a partition
du -sh <--- To view total amount of used space in a partition directory

e2label /dev/sda8 sami <-- To add a lable
e2label /dev/sda8 "" <-- To remove a lable

mkfs.etc3 L sami /dev/sda8 <-- format the partition and add a lable

Oracle EBS r12 troubleshooting & useful notes

2011-09-11T09:11:00.000-07:00

1. Canceling of concurrent Request Gives “the concurrent manager process that was running this request has exited abnormally “
Some times we get a Request from customers that they are unable to cancel the request because of this error

“the concurrent manager process that was running this request has exited abnormally .The ICM will mark this request as completed with error.”"

How to navigate :
=-=-==-=-=-=-=-=-=-==
Tried to Cancel a concurrent request. Used the “Cancel Request” button from the Administer > Concurrent > Manager form.

Got the following message:

Request xxxxxx can no longer be cancelled. The Concurrent Manager Process that was running this request has exited abnormally. The ICM will mark this request as completed with error.

Solution
======
This can be safely done while managers are up and running with little care

1) Backup fnd_concurrent_requests table

2) SQL> UPDATE fnd_concurrent_requests
SET phase_code = ‘C’, status_code = ‘X’
WHERE Request_id=&request_id

Here Request id is the request which we want to cancel….

3) Commit

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

2. Oracle EBS r12 Diagnostic Tests.

To execute a diagnostic test for,
Log File Test
DBC Parameter Test
Class Path Test
Java Run-Time Environment Version Test
Java System Property Report test

Go to 'system administrator' responsibility--->Under 'System Administration: Diagnostics'--->Click 'Diagnostic Test'-->Here run all tests under application "HTML Platform".

In the Report output under 'View Reports' tab you can see the result of the above tests. For example it includes, what the current guest username is set to and if it is successfully authenticated (this is displayed in the value "Diagnostic Test Executed By" in the report output).
-------------------------------------------------------------------------------------------

3. what is the usage & Contents of .dbc file in 11i/R12

dbc is the database connect descriptor file that stores database connection information used by application tier to connect to the database.
The file is found in $FND_TOP/secure directory also called as FND_SECURE.

Location of .dbc file in 11i is: $FND_TOP/SECURE OR $FND_SECURE

Location of .dbc file in R12 is: $INST_TOP/appl/fnd/12.0.0/secure

If we interpret .dbc file it contains the following,

GUEST_USER_PWD – Guest username & password
APPL_SERVER_ID – Used to Identify the Databse by Server ID, this ID is also stored in SERVER_ID column of FND_NODES table.
GWYUID – APPLSYSPUB user name and password
DBhostname - DB server name
dbport - Database Listener Port number

Other than these, the following are some other parameters with their default values, these are related to the Performance,

FND_JDBC_BUFFER_DECAY_INTERVAL=300
FND_JDBC_BUFFER_MIN=1
FND_JDBC_BUFFER_MAX=5
FND_JDBC_STMT_CACHE_FREE_MEM=TRUE
FND_JDBC_STMT_CACHE_SIZE=200
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=APPS
FND_JDBC_PLSQL_RESET=false
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

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

4. How to drop/delete an application user

We can only deactivate or end-date the application user in Oracle EBS using the application front end. While we cannot delete an application user, but if you try to delete it from the database then it might crash the system and more over it is not supported by oracle.

To deactivate or end-date the application user you can use any one of the following methods,

Method I:
—————
Login to Oracle Applications – Goto System Administrator responsibility – Under 'Security' – User – Define – Search user name – then you can give end date in the Effectives Date To column.

Method II:
=======

It's always recommended to use the above method to end-date/deactivate application user.
Even though the following method is not supported by oracle support, End-dating the user from backend database is also possible with the following steps, This is also mentioned in Note 1039608.6

Login as 'APPS' database user. Then take a backup of fnd_user table just in case if any issue comes then you can restore this table instead of the whole database restore.

SQL>CREATE TABLE BKUP_FND_USER AS SELECT * FROM FND_USER;

SQL>update fnd_user set end_date='27-JUL-2011' where user_name like ‘%%’;

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

5. How to know the Oracle Ebusiness Suite 11i / r12 application URL?

select home_url from apps.icx_parameters;

or

grep -i login $APPL_TOP/admin/Context_name.xml

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

6. fnd_install_processes & ad_deferred_jobs tables

When a patch is applied using adpatch it will ask number of workers. These workers are used to run some task like executing some perl scripts, running sql scripts, etc.

If u check in the patch log file before assigning any job to workers the two tables fnd_install_processes and ad_deferred_jobs are created.
After the job is finished the tables are dropped automatically. The following is the purpose of these tables,

fnd_install_processes – This table is used to store the information about the job given to the worker. It will insert a row for each worker when it assigned a job.

ad_deferred_jobs – this table is used to store the information about the deferred jobs (jobs failed to run).

---------------------------------------------
7. Steps to manually stage r12 software

make a directory stage
in that (mkdir stage)

copy the start cd (mkdir startCD) -> startCD
copy the Tools-Disk1(mkdir oraiAS) ->oraiAS
copy the RDBMS Disk1 (mkdir oraDB) -> oraDB
copy the Databases disk1,disk2,disk3,disk4 (mkdir oraAppDB) ->oraAppDB
copy the APPL_TOP dis1 and 2 to (mkdir oraApps) -> oraApps

The automatic staging command is,

perl d:\mnt\cdrom\Disk1\rapidwiz\adautostg.pl
The stage script message informs you that you may either stage all the Rapid Installcomponents, or selected components only.
--------------------------------------------

8. Oracle Applications Current Patchsets Comparison Utility (Script to Analyze) Patchsets.sh

The program (Patchsets.sh) compares the currently installed patchsets and family packs to the most recently available ones generated by Oracle Development.

NOTE: The patchsets.sh program is frequently updated by the Oracle development team with the latest patchsets data in it.
So, this means that you need to get the latest version to get an up-to-date report.

*Download the latest Patchsets.sh script from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh
*Also refer to metalink note ID 139684.1 for latest updates and how to run this program, the steps to run this program is compiled in short form.

This program utilizes direct database connectivity or the applptch.txt file for 10.7-11.0. For 11i and R12, it utilizes a combination of tables such as AD_BUGS and AD_APPLIED_PATCHES to create the installed patch list. If the 11i release does not use these AD tables (11.5.4 or lower and have not applied 11i.AD.E or higher) it still supports using applptch.txt for 11i.

Setup Requirements:
Must have connectivity to EBS database via sql*net with $TWO_TASK set
Source $APPL_TOP/APPSxx.env file or just setup database env such as ORACLE_HOME and TWO_TASK

Running the Patchsets Comparison Tool:

•11i - $ . $APPL_TOP/APPSORA.env
•R12 - $ . $APPL_TOP/APPS{sid}_{machine}.env
Note: As long as you can connect with sqlplus and $TWO_TASK, the script should have the access it needs.

Usage: $ patchsets.sh [connect=11i_login/pwapplptch=/path/applptch.txt] [-h] [silent=y] default is n, [htmlout=file]

Example Help:
$ patchsets.sh -h

Example Html Output in Silent Mode:
$ ./patchsets.sh applptch=/applptch_11i.txt htmlout=Report.html silent=y

$ ./patchsets.sh connect=apps/apps_password_here htmlout=xxReport.html

Columns in The Output of script:
Baseline Version: This is the baseline release version that come with EBS release
Running Version: This is The Current High Level Code running on instance..or currrent patch level
Latest AvailableStatus: THis Is the Latest Patches Available from The ORACLE for that module…

Platform Specific Issues:
TO RUN UNDER NT with MKS:
--> Get to the drive where the appl_top is.
C:> D:
D:\>
Make a tmp directory on that drives root.
D:> mkdir tmp (this is where the patchset lists get created)

--> cd to the APPL_TOP path and then execute the envshell to setup the env variables.
D:\> cd D:\apps\visappl
D:\apps\visappl> envshell
A new window spawns with the correct env settings, use it for next step

-->Start the MKS SHELL,
D:\apps\visappl> sh
$

--> Run the patchsets program from the APPL_TOP/admin/ directory
$ cd /admin/VIS
$ ./patchsets.sh applptch=applptch.txt

NOTE: Download the Patchsets.sh script from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

NOTE: Also you can get this script from metalink note : Oracle Applications Current Patchset Comparison Utility – patchsets.sh [ID 139684.1]

Patch Application Assistant PAA admsi.pl

2011-08-15T23:05:00.000-07:00

PAA (Patch Application Assistant) admsi.pl is tool/Perl script to generate customized installation instructions for a patch in Oracle Applications R12 that helps user to track and perform manual steps during patching.

1. admsi.pl is under $AD_TOP/bin
2. You can run admsi.pl (Patch Application Assistant) in two mode
i) GUI (Graphical User Interface)
ii) CLI (Command Line Interface)
3. Once you complete manual steps as instructed in PAA report, you can record that step as completed.

How to Run PAA (Patch Application Assistant) in R12

1. Source environment file i.e. $ORACLE_R12_BASE/apps/apps_st/appl/APPS[SID]_[hostname].env

2. Run PAA using admsi.pl in either CLI or GUI

2.1 CLI mode
perl admsi.pl -patch_top=[patch-top-directory]-appspass=[apps-password]

For example to generate PAA report for 12.0.6 patch (6728000) run it as
perl $AD_TOP/bin/admsi.pl -patch_top=/stage/oracle/patches/r12/6728000 -appspass=apps
Gathering Information..
Logfile for this session is located at admsi.log
Generating installation instructions for patch 6728000..
Updating database….
install_6728000.html generated successfully
install_6728000.txt generated successfully

Now view report (text or html format) containing manual steps for 12.0.6

2.2 GUI
Invoke PAA in GUI by running admsi.pl as shown below

i. Source environment file
ii. Then run the command 'admsi.pl' without any arguments
iii. Next step is to select from one of three options, I selected option 1 i.e. to find instance specific instructions for 12.0.6 patch (6728000), then complete the wizard steps by selecting appropriate values and the location of the patch folder.
iv. Once you get list of manual steps, follow instructions in report
v. Apply apps patches using AUTOPATCH utility (adpatch)

For any further assistance or doubts please don't hesitate to email me at samiora@gmail.com

Oracle R12 & 11i Patchsets Comparison Utility

2011-07-26T22:05:00.000-07:00

Oracle Applications Current Patchsets Comparison Utility (Script to Analyze) Patchsets.sh

The program (Patchsets.sh) compares the currently installed patchsets and family packs to the most recently available ones generated by Oracle Development.

NOTE: The patchsets.sh program is frequently updated by the Oracle development team with the latest patchsets data in it. So, this means that you need to get the latest version to get an up-to-date report.

*Download the latest Patchsets.sh script from:

ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

--> Also refer to metalink note ID 139684.1 for latest updates and how to run this program, the steps to run this program is compiled in short form.

This program utilizes direct database connectivity or the applptch.txt file for 10.7-11.0. For 11i and R12, it utilizes a combination of tables such as AD_BUGS and AD_APPLIED_PATCHES to create the installed patch list. If the 11i release does not use these AD tables (11.5.4 or lower and have not applied 11i.AD.E or higher) it still supports using applptch.txt for 11i.

1. Setup Requirements:
Must have connectivity to EBS database via sql*net with $TWO_TASK set
Source $APPL_TOP/APPSxx.env file or just setup database env such as ORACLE_HOME and TWO_TASK

2. Running the Patchsets Comparison Tool:

•11i - $ . $APPL_TOP/APPSORA.env
•R12 - $ . $APPL_TOP/APPS{sid}_{machine}.env
Note: As long as you can connect with sqlplus and $TWO_TASK, the script should have the access it needs.

Usage: $ patchsets.sh [connect=11i_login/pwapplptch=/path/applptch.txt] [-h] [silent=y] default is n, [htmlout=file]

Example Help:
$ patchsets.sh -h

Example Html Output in Silent Mode:
$ ./patchsets.sh applptch=/applptch_11i.txt htmlout=Report.html silent=y

$ ./patchsets.sh connect=apps/apps_password_here htmlout=xxReport.html

3. Columns in The Output of script:
Baseline Version: This is the baseline release version that come with EBS release
Running Version: This is The Current High Level Code running on instance..or currrent patch level
Latest AvailableStatus: THis Is the Latest Patches Available from The ORACLE for that module…

4. Platform Specific Issues:
TO RUN UNDER NT with MKS:
--> Get to the drive where the appl_top is.
F:\>
Make a tmp directory on that drives root.
F:>mkdir tmp (this is where the patchset lists get created)

--> cd to the APPL_TOP path and then execute the envshell to setup the env variables.
F:\> cd F:\oracle\apps\apps_st\appl
F:\oracle\apps\apps_st\appl> dhrms_devapps.cmd
A new window spawns with the correct env settings, use it for next step

--> Run the patchsets program from F:\ where the tmp directory was created in earlier step
F:\> patchsets.sh connect=apps/apps_password_here htmlout=xxReport.html
(or)
F:\> patchsets.sh applptch=applptch_11i.txt htmlout=Report.html silent=y

NOTE: Download the latest Patchsets.sh script from: ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh

Reference: Also you can get this script from metalink note : Oracle Applications Current Patchset Comparison Utility – patchsets.sh [ID 139684.1]

For any help on running this script or to analyze the output result, please don't hesitate to email me at samiora@gmail.com

SQL Injection via CTXSYS.DRILOAD in 8i and 9i

2011-06-11T11:19:00.001-07:00

SQL Injection via CTXSYS.DRILOAD in Oracle 8i and 9i databases

Systems Affected: are Oracle 8.i and Oracle9i on all platforms
Severity: High Risk
Category: SQL Injection

Details: Any valid database user can become DBA (if CTXSYS is installed) by executing the package DRILOAD by submitting a specially crafted parameter.

Oracle 10g and 11g are NOT affected.

Workarounds: Drop user CTXSYS (if not needed) or 'revoke public grant from CTXSYS.DRILOAD'.

Example: sqlplus scott/tiger@tnsname (or every other unprivileged user)

SQL> exec ctxsys.driload.validate_stmt('grant dba to scott');

BEGIN ctxsys.driload.validate_stmt('grant dba to scott'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRILOAD", line 42
ORA-01003: no statement parsed
ORA-06512: at line 1

The above ORA- error is displayed but actually the dba role got granted to scott user.

Fix: On 8i and 9i database there is a patch available to fix this security vulnarability, Please see MetaLink document ID 281189.1 for the patch download procedures and for the Patch Availability Matrix for this Oracle Security Alert.

Installing Oracle workflow builder on windows 7

2011-05-03T22:02:00.000-07:00

Installing Oracle workflow builder on windows 7 /
Installing Oracle database client 10g 11g on windows 7

We just upgraded all clients to windows 7 and faced few challenges, one of them was to install few backward compatible oracle client softwares on windows 7.

After the upgrade to windows 7, Attempting to install Oracle Workflow Builder 2.6.3 and oracle client 11gR2/10gR2 on a Windows 7 Client gives compatibility error.

SOLUTION 1:
To configure the compatibility mode for an application, just locate the installation directory and right click on the .exe, selecting Properties from the menu and change the compatibility mode to 'Windows XP service pack 3'. This is applicable for both Workflow builder executables and also for oracle 11g/10g clients.

1. Navigate to the WF Builder installer (setup.exe)

2. Right-Click on the setup.exe then go to Properties.

3. Check Compatibility Mode and Select Windows XP (Service Pack 3).

4. Now restart the Installer for the Workflow Builder. After installation if the builder doesn't start then follow steps 1 to 3 for WFBLDR.EXE file.

Note: This same behaviour is applicable on Vista.

For screenshots with similar solution follow,
http://www.howtogeek.com/howto/windows-vista/using-windows-vista-compatibility-mode/
http://www.howtogeek.com/howto/windows-7/using-windows-vista-compatibility-mode/

==================
ANOTHER GOOD WORK AROUND:

SOLUTION 2:
Shortly after initiating installation of Oracle Client 11g (11.1.0.6.0) installation stopped on prerequisite checks with error:

Checking operating system requirements ...
Expected result: One of 5.0,5.1,5.2,6.0
Actual Result: 6.1
Check complete. The overall result of this check is: Failed <<<< Problem: Oracle Database 11g is not certified on the current operating system. Recommendation: Make sure you are installing the software on the correct platform. To workaround this problem edit refhost.xml file adding entry for Windows 7.

Location of refhost.xml file on media is,
D:\win32_11gR1_client\client\stage\prereq\client\refhost.xml

For windows 7 OS, add the following to the end of the refhost.xml file content:

<CERTIFIED_SYSTEMS>

<OPERATING_SYSTEM>
<VERSION VALUE="6.1">
</OPERATING_SYSTEM>
</CERTIFIED_SYSTEMS>

With new refhost.xml file all checks will pass and the installation will complete successfully.

For any futher help please don't hesitate to contact me on samiora@gmail.com or call me at 0097433164959.

Download Oracle database 11g R2 11.2.0.2

2011-04-27T00:06:00.000-07:00

I have got a task to install 11.2.0.2 on Redhat Linux x86_64bit and upgrade db from 10.2.0.3 for one of the applications, so the following is the information regarding the software.
While it's worth to note that, if you are at 11.2.0.1 then moving to 11.2.0.2 is not a patchset installation but a full database upgrade.

To download and install 11gR2, get full Oracle database 11gR2 11.2.0.2 software from patch 10098816

Note that, Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).

To Download 11.2.0.2 rdbms software go to My Oracle support: patch 10098816

https://updates.oracle.com/download/10098816.html

Please select your platform before downloading (Click on "Platform or Language" drop down menu).
From here you can download total 7 disks and the following is the description for each one,

Note that Oracle Database (includes Oracle Database and Oracle RAC)
You must download both the following zip files to install Oracle Database.
p10098816_112020_platform_1of7.zip & p10098816_112020_platform_2of7.zip

Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart)
p10098816_112020_platform_3of7.zip

Oracle Database Client
p10098816_112020_platform_4of7.zip

Oracle Gateways
p10098816_112020_platform_5of7.zip

Oracle Examples
p10098816_112020_platform_6of7.zip

Deinstall
p10098816_112020_platform_7of7.zip

For help in installation and upgrades please don't hesitate to contact me at samiora@gmail.com or call me on my cell 0097433164959.

Oracle IAM the best Identity Management Product

2011-03-31T01:15:00.000-07:00

Comparision of Identity Management Tools from ORACLE, IBM, CA, MICROSOFT and QUEST

After taking Proof of Concept POC from various vendors for their Identity and Access management IAM product the following is the comparision analysis report,

What is Identity Management?
Securing your IT assets from within
•Management of digital user identities through their complete lifecycle Employee hire to promotion to departure
•Securing access to applications and information
•Authentication: proving you are who you say you are
•Authorization: what you have access to, when, where
•Profile: attributes about you

How Can IAM Suite Help any Organization?
Establish Enterprise Identity, Roles and Single Sign On
•Enforce strong password policies via synchronization or single sign-on (SSO)
•Consolidate multiple, complex identity environments to a single enterprise identity source
•Automate linkage of employee records with user accounts
•Establish enterprise roles for automation, compliance and business continuity
•Eliminate rogue and orphaned accounts •Automate detection of fraudulent activities based on policies
•Implement strong authentication and risk based authorization for critical apps
•Enforce minimal access rights based on roles, attributes, and requests
•Implement automated attestation for entitlements, roles, policies, workflows, etc.

Log file location for Oracle EBS R12

2011-02-16T22:01:00.000-08:00

- Autoconfig log file:
Apps:
$INST_TOP/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME//adconfig.log $ORACLE_HOME/appsutil/log/$CONTEXT_NAME//NetServiceHandler.log

- Startup/Shutdown Log files:
$INST_TOP/logs/appl/admin/log

- Apache, OC4J and OPMN:
$LOG_HOME/ora/10.1.3/Apache $LOG_HOME/ora/10.1.3/j2ee $LOG_HOME/ora/10.1.3/opmn

- Patch log:
$APPL_TOP/admin/$SID/log/

- Concurrent log:
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log

- Clone log:
Preclone log files in source instance

Apps:
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ (StageAppsTier_MMDDHHMM.log)

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)

Clone log files in target instance
Apps :
$INST_TOP/apps/$CONTEXT_NAME/admin/log/ApplyAppsTier_.log

Db:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log

- Alert Log File:
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Reference:
How to find location of Install, Autoconfig, Patching , Clone and other logs in R12 [ID 804603.1]
Oracle Application Server Diagnostic Tools and Log Files in Applications Release 12 [ID 454178.1]
Oracle Applications System Administrator’s Guide

my 2011 projects

2011-01-22T22:04:00.000-08:00

Wish you all a happy new year 2011.

For sure I will be very busy this year as I have the following projects to implement in my company in coordination with the team and the consulting companies,

1. Oracle Identity and Access Management IAM
2. iRecruitment in Oracle R12.1.1 ebs HRMS
3. iLearning in Oracle R12.1.1 ebs HRMS
4. Completion of Disaster Recovery site preparation for all the business critical applications.
5. Microsoft BizTalk Middle ware project.

Among the above five major projects the most important and critical is the IAM for me as I have been appointed as a Project leader in this Oracle IAM Project. I will be posting from here on, work related to IAM tasks that I have done during the project implementation phases.

Views Pertinent to DataGuard Configurations

2010-12-06T01:07:00.000-08:00

The following are the views that are pertinent to DataGuard Configurations that are categorised for logical and/or physical standby databases.

*Views for LOGICAL DATABASE ONLY

DBA_LOGSTDBY_EVENTS
Logical only
Contains information about the activity of a logical standby database. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to a logical standby database.

DBA_LOGSTDBY_HISTORY
Logical only
Displays the history of switchovers and failovers for logical standby databases in a Data Guard configuration. It does this by showing the complete sequence of redo log streams processed or created on the local system, across all role transitions. (After a role transition, a new log stream is started and the log stream sequence number is incremented by the new primary database.)

DBA_LOGSTDBY_LOG
Logical only
Shows the log files registered for logical standby databases.

DBA_LOGSTDBY_NOT_UNIQUE
Logical only
Identifies tables that have no primary and no non-null unique indexes.

DBA_LOGSTDBY_PARAMETERS
Logical only
Contains the list of parameters used by SQL Apply.

DBA_LOGSTDBY_SKIP
Logical only
Lists the tables that will be skipped by SQL Apply.

DBA_LOGSTDBY_SKIP_TRANSACTION
Logical only
Lists the skip settings chosen.

DBA_LOGSTDBY_UNSUPPORTED
Logical only
Identifies the schemas and tables (and columns in those tables) that contain unsupported data types. Use this view when you are preparing to create a logical standby database.

V$LOGSTDBY_PROCESS
Logical only
Provides dynamic information about what is happening with SQL Apply. This view is very helpful when you are diagnosing performance problems during SQL Apply on the logical standby database, and it can be helpful for other problems.

V$LOGSTDBY_PROGRESS
Logical only
Displays the progress of SQL Apply on the logical standby database.

V$LOGSTDBY_STATE
Logical only
Consolidates information from the V$LOGSTDBY_PROCESS and V$LOGSTDBY_STATS views about the running state of SQL Apply and the logical standby database.

V$LOGSTDBY_STATS
Logical only
Displays LogMiner statistics, current state, and status information for a logical standby database during SQL Apply. If SQL Apply is not running, the values for the statistics are cleared.

V$LOGSTDBY_TRANSACTION
Logical only
Displays information about all active transactions being processed by SQL Apply on the logical standby database.

*Views for PHYSICAL DATABASE ONLY

V$MANAGED_STANDBY
Physical only
Displays current status information for Oracle database processes related to physical standby databases.
Note: The information in this view does not persist across an instance shutdown.

*Views for PRIMARY, PHYSICAL AND LOGICAL DATABASES

V$ARCHIVE_DEST
Primary, physical, and logical
Describes all of the destinations in the Data Guard configuration, including each destination’s current value, mode, and status.
Note: The information in this view does not persist across an instance shutdown.

V$ARCHIVE_DEST_STATUS
Primary, physical, and logical
Displays runtime and configuration information for the archived redo log destinations.
Note: The information in this view does not persist across an instance shutdown.

V$ARCHIVE_GAP
Physical and logical
Displays information to help you identify a gap in the archived redo log files.

V$ARCHIVED_LOG
Primary, physical, and logical
Displays archive redo log information from the control file, including names of the archived redo log files.

V$DATABASE
Primary, physical, and logical
Provides database information from the control file. Includes information about fast-start failover (available only with the Data Guard broker).

V$DATABASE_INCARNATION
Primary, physical, and logical
Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGS option. Records about the current and the previous incarnation are also contained in the V$DATABASE view.

V$DATAFILE
Primary, physical, and logical
Provides datafile information from the control file.

V$DATAGUARD_CONFIG
Primary, physical, and logical
Lists the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIG initialization parameters.

V$DATAGUARD_STATS
Primary, physical, and logical
Displays how much redo data generated by the primary database is not yet available on the standby database, showing how much redo data could be lost if the primary database were to crash at the time you queried this view. You can query this view on any instance of a standby database in a Data Guard configuration. If you query this view on a primary database, then the column values are cleared.

V$DATAGUARD_STATUS
Primary, physical, and logical
Displays and records events that would typically be triggered by any message to the alert log or server process trace files.

V$LOG
Primary, physical, and logical
Contains log file information from the online redo log files.

V$LOGFILE
Primary, physical, and logical
Contains information about the online redo log files and standby redo log files.

V$LOG_HISTORY
Primary, physical, and logical
Contains log history information from the control file.

V$STANDBY_LOG
Physical and logical
Contains log file information from the standby redo log files.

References: Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
B14239-05

For any queries please don't hesitate to contact me on samiora@gmail.com or call me on 0097433164959.

Upgrade Oracle Database 9i to 10g 10204

2010-11-22T23:07:00.000-08:00

Upgraded JDEdwards oracle database from 9i to 10g using the standard database upgrade procedure,

1. SRPSFTENT --> Microsoft Windows 2003 Server Enterprise Edition SP2. 8GB RAM.

2. On SRPSFTENT: Stopped and made OracleOraHome92HTTPServer &
OracleMTSRecoveryService services manual,
most important stop JDE service 'JDE E812'.

On SRPSFTDEP: Stopped OracleMTSRecoveryService service

On SRPSFTWEB: Stopped Oracle-oc4jadminProcessManager

3. Delete OracleOraHome92TNSListenerlistener_10g service through the add+remove services.exe tool.

4. Exported Oracle Registry as backup to flash\jdedbupgraderough directory.

5. Made startmode=manual for database in registry

6. Start database and check invalid objects
SQL> select objecT_name,Object_type,owner from dba_objects where status='INVALID
';

OBJECT_NAME OBJECT_TYPE OWNER
------------------ ------------------------------
ODCIARGDESC TYPE SYS
ODCICOLINFO TYPE SYS
ODCICOST TYPE SYS
ODCIINDEXINFO TYPE SYS
ODCIINDEXINFO TYPE SYS
CATALOG_TYP TYPE OE
CATEGORY_TYP TYPE OE
COMPOSITE_CATEGORY_TYP TYPE OE
LEAF_CATEGORY_TYP TYPE OE
BSREPORT PROCEDURE TESTDTA
PKG_MULTIRESULTSET PACKAGE BODY TESTDTA
11 rows selected.

7. Shutdown immediate;

8. Before 10g install note the path parameter.
C:\>set path
Path=E:\oracle\ora92\bin;C:\Program Files\Microsoft Visual Studio 8\VC\bin;E:\JD
Edwards\E812\DDP\Verity\winx86\_nti40\bin;E:\oracle\ora92\jre\1.4.2\bin\client;E
:\oracle\ora92\jre\1.4.2\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Fi
les\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOWS\system32;C:\WINDOWS;
C:\WINDOWS\System32\Wbem;C:\Program Files\System Center Operations Manager 2007\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH

9. Take backup of Oracle inventory. Backup c:\program files\oracle folder which contains Inventory directory.

10. Install Oracle 10g Release 2 in a new Oracle Home i.e, Install 10201+companion(select Oracle Database 10g Products 10.2.0.1+10204patchset in e:\oracle\ora10g

11. environment variable check
C:\>set oracle
ORACLE_HOME=e:\oracle\ora92

C:\>set path
Path=e:\oracle\ora10g\bin;E:\oracle\ora92\bin;C:\Program Files\Microsoft Visual
Studio 8\VC\bin;E:\JDEdwards\E812\DDP\Verity\winx86\_nti40\bin;E:\oracle\ora92\j
re\1.4.2\bin\client;E:\oracle\ora92\jre\1.4.2\bin;C:\Program Files\Oracle\jre\1.
3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOWS
\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\System Center Ope
rations Manager 2007\

Update the path entry and remove 10ghome that is in begining of PATH variable so that we can connect to 9i database using 9i home.

After update in path parameter the new value is,

C:\Documents and Settings\jde.QIA>set path
Path=E:\oracle\ora92\bin;C:\Program Files\Microsoft Visual Studio 8\VC\bin;E:\JD
Edwards\E812\DDP\Verity\winx86\_nti40\bin;E:\oracle\ora92\jre\1.4.2\bin\client;E
:\oracle\ora92\jre\1.4.2\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Fi
les\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOWS\system32;C:\WINDOWS;
C:\WINDOWS\System32\Wbem;C:\Program Files\System Center Operations Manager 2007\

12. Start the 9i database in normal mode (Must) from 9i sqlplus tool,

E:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 29 08:19:23 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> exit
Disconnected

E:\>set oracle
ORACLE_HOME=e:\oracle\ora92

E:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 29 08:19:34 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1469131144 bytes
Fixed Size 457096 bytes
Variable Size 696254464 bytes
Database Buffers 771751936 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

13. copy the following files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the e:\upgradetemp directory on your system:

ORACLE_HOME/rdbms/admin/utlu102i.sql
ORACLE_HOME/rdbms/admin/utltzuv2.sql

Make a note of the new location of these files.

14. Change to the temporary directory that you copied files in previous step.

Start SQL*Plus and connect to the database instance you are intending to upgrade (running from the original old ORACLE_HOME) as a user with SYSDBA privileges. Then run and spool the utlu102i.sql file.
sqlplus '/as sysdba'

E:\upgradetemp>dir
Volume in drive E is New Volume
Volume Serial Number is 78AD-5ABC

Directory of E:\upgradetemp

10/29/2010 08:10 AM .
10/29/2010 08:10 AM ..
02/07/2007 01:23 PM 18,183 utltzuv2.sql
10/08/2007 12:53 PM 136,217 utlu102i.sql
2 File(s) 154,400 bytes
2 Dir(s) 224,538,529,792 bytes free

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

Please note: The instance can be running in normal mode and does not need to be running in a restricted (migrate / upgrade) mode to run the script. Also, the instance must not be running in read-only mode. A few registry$ tables may be created, if they do not already exist, and some rows may be inserted into existing Upgrade tables.
Then, check the spool file and examine the output of the upgrade information tool. The sections describe the output of the Upgrade Information Tool (utlu102i.sql).

*************************
SPOOL OUTPUT FILE CONTENT
*************************
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 10-29-2010 08:23:58
.
**********************************************************************
Database:
**********************************************************************
--> name: JDEDB
--> version: 9.2.0.8.0
--> compatible: 9.2.0.0.0
--> blocksize: 8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 730 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
--> CWMLITE tablespace is adequate for the upgrade.
.... minimum required size: 19 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 33 MB
.... AUTOEXTEND additional space required: 13 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 151 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 49 MB
.... AUTOEXTEND additional space required: 2 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "hash_join_enabled"
--> "log_archive_start"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] UPGRADED
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] UPGRADED
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
--> Oracle Ultra Search [upgrade] VALID
... To successfully upgrade Ultra Search, install it from
... the 10g Companion CD.
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... XDB
.... WMSYS
.... ODM
.... OLAPSYS
.... MDSYS
.... WKSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER OE has 4 INVALID objects.
.... USER SYS has 5 INVALID objects.
.... USER TESTDTA has 2 INVALID objects.
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

SQL> spool off;

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

13. Increased the size of system tablespace from 1000MB to 2048MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\SYSTEM01.DBF' RESIZE 2048M;

UNDOTBS1 is already 3635MB and has all free space except 15MB used.

Increased the size of TEMP tablespace from 1500MB to 2048MB
ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\JDEDB\TEMP01.DBF' RESIZE 2048M

Increased the size of CWMLITE tablespace from 20MB to 128MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\CWMLITE01.DBF' RESIZE 128M

Increased the size of DRSYS01 tablespace from 20MB to 128MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\DRSYS01.DBF' RESIZE 128M;

EXAMPLES already 247 MB and has 60 free space i.e, only 148 MB used.

ODM already 20 MB and has 10 free space i.e, only 10 MB used.

Increased the size of XDB tablespace from 47MB to 128MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\XDB01.DBF' RESIZE 128M

14. Current initJDEDB.ora file content before any update.

*.aq_tm_processes=1
*.background_dump_dest='E:\oracle\admin\JDEDB\bdump'
*.compatible='9.2.0.0.0'
*.control_files='E:\oracle\oradata\JDEDB\CONTROL01.CTL','E:\oracle\oradata\JDEDB\CONTROL02.CTL','E:\oracle\oradata\JDEDB\CONTROL03.CTL'
*.core_dump_dest='E:\oracle\admin\JDEDB\cdump'
*.db_block_size=8192
*.db_cache_size=771658240
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='JDEDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JDEDBXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='JDEDB'
*.java_pool_size=67108864
*.job_queue_processes=10
*.large_pool_size=67108864
*.open_cursors=300
*.pga_aggregate_target=451658240
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=536870912
*.sort_area_size=2097152
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\admin\JDEDB\udump'
*.log_archive_start=TRUE
*.log_archive_dest=E:\fra

Added *.streams_pool_size=50331648 (but commented as it is new 10g parameter), *.session_max_open_files=20 as required
Updated *.db_cache_size=771658240 to *.db_cache_size=371658240
Updated *.java_pool_size=67108864 to *.java_pool_size=167108864
Updated *.large_pool_size=67108864 to *.large_pool_size=157108864
Updated *.pga_aggregate_target=451658240 to 251658240
Updated *.shared_pool_size=536870912 to 236870912

so the final initJDEDB.ora file content is,

*.aq_tm_processes=1
*.background_dump_dest='E:\oracle\admin\JDEDB\bdump'
*.compatible='9.2.0.0.0'
*.control_files='E:\oracle\oradata\JDEDB\CONTROL01.CTL','E:\oracle\oradata\JDEDB\CONTROL02.CTL','E:\oracle\oradata\JDEDB\CONTROL03.CTL'
*.core_dump_dest='E:\oracle\admin\JDEDB\cdump'
*.db_block_size=8192
*.db_cache_size=371658240
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='JDEDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JDEDBXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='JDEDB'
*.java_pool_size=167108864
*.job_queue_processes=10
*.large_pool_size=157108864
*.open_cursors=300
*.pga_aggregate_target=251658240
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=236870912
*.sort_area_size=2097152
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\admin\JDEDB\udump'
*.log_archive_start=TRUE
*.log_archive_dest=E:\fra
*.session_max_open_files=20
#*.streams_pool_size=50331648

Shutdown immediate

Startup;

15. After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SQL> SELECT grantee FROM dba_role_privs
2 WHERE granted_role = 'CONNECT' and
3 grantee NOT IN (
4 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
5 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
6 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
7 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
8 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
9 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
10 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

GRANTEE
------------------------------
HR
OE
PM
QS
SH
PSFT
RMAN
DV810
QS_CB
QS_CS
QS_ES
QS_OS
QS_WS
SCOTT
CRPCTL
CRPDTA
JDEDBA
QS_ADM
AUDITOR
PRODCTL
PRODDTA
TESTCTL
TESTDTA
QS_CBADM
24 rows selected.

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS
2 WHERE GRANTEE='CONNECT'
3 ;

GRANTEE PRIVILEGE
------------------------------ --------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK
8 rows selected.

NOTE: AFTER UPGRADE GRANT THE ABOVE SYSTEM PRIVILEGES TO ALL THE USERS LIST IN PREVIOUS SCRIPT.

16. Check for dblinks and to recreate them after the upgrade is done,

SQL> SELECT
2 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
3 ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
4 ||'connect to ' || L.USERID || ' identified by '''
5 ||L.PASSWORD||''' using ''' || L.host || ''''
6 ||chr(10)||';' TEXT
7 FROM sys.link$ L,
8 sys.user$ U
9 WHERE L.OWNER# = U.USER# ;

no rows selected

17. If you are upgrading from Oracle9iR2 (9.2), verify that the view dba_registry contains data. If the view is empty, run the following scripts from the 9.2 home:
% sqlplus '/as sysdba'
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
and verify that the dba_registry view now contains data.

18. I ignored this step,
But if you want as per the standard, check for corruption in the dictionary, use the following commands in sqlplus connected as sys:
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
from dba_clusters
where owner='SYS'
union
Select 'Analyze table "'||table_name||'" validate structure cascade;'
from dba_tables
where owner='SYS' and partitioned='NO' and (iot_type='IOT' or iot_type is NULL)
union
Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
from dba_tables
where owner='SYS' and partitioned='YES';

spool off

This creates a script called analyze.sql.
Now execute the following steps.

$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

19. Since we don't have replication in database so I ignored the following step,

Ensure that all Materialized views / Snapshot refreshes are successfully completed, and that replication must be stopped (ie: quiesced).

$ sqlplus '/ as sysdba'
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
You can also use:
SQL> select distinct owner, name mview, master_owner master_owner, last_refresh from dba_mview_refresh_times;

20. Stop the listener for the database:

$ lsnrctl
LSNRCTL> stop

Ensure no files need media recovery:

SQL> select * from v$recover_file;

no rows selected

21. Ensure no files are in backup mode:

SQL> select * from v$backup where status!='NOT ACTIVE';
no rows selected

This should return no rows.

22. Resolve any outstanding unresolved distributed transaction:

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> select local_tran_id from dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;

23. Disable all batch and cron jobs.

24. Ensure the users sys and system have 'system' as their default tablespace.

SQL> select username, default_tablespace from dba_users where username in ('SYS','SYSTEM');

USERNAME DEFAULT_TABLESPACE
------------------------------ -------------------------
SYSTEM SYSTEM
SYS SYSTEM

To modify use:

SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM;

25. stop listnere so that no body is connecting to ur database.

26. Ensure that the aud$ is in the system tablespace when auditing is enabled.

SQL> select tablespace_name from dba_tables where table_name='AUD$';

TABLESPACE_NAME
------------------------------
SYSTEM

If the AUD$ table exists, and is in use, upgrade performance can be effected depending on the number of records in the table.
Please refer to the following note for information on exporting and truncating the AUD$ table:
Note.979942.1 Ext/Pub Database upgrade appears to have halted at SYS.AUD$ Table

27. Note down where all control files are located.

SQL> select * from v$controlfile;

STATUS
-------
NAME
-------------------------------------
E:\ORACLE\ORADATA\JDEDB\CONTROL01.CTL
E:\ORACLE\ORADATA\JDEDB\CONTROL02.CTL
E:\ORACLE\ORADATA\JDEDB\CONTROL03.CTL

28. If table XDB.MIGR9202STATUS exists in the database, drop it before upgrading the database (to avoid the issue described in Note:356082.1)

29. Shutdown the database

$ sqlplus '/as sysdba'
SQL> shutdown immediate;

30. Perform a full cold backup (or an online backup using RMAN)

You can either do this by manually copying the files or sign on to RMAN

$ rman "target / nocatalog"

31. Make a backup of the old init.ora file

Copy it from the old (pre-10.2) ORACLE_HOME to the new (10.2) ORACLE_HOME
Copy from E:\oracle\ora92\database to E:\oracle\ora10g\database.

Comment out any obsoleted parameters and Change all deprecated parameters

I commeted #*.log_archive_start=TRUE while added *.streams_pool_size=50331648 parameters.

- Set the COMPATIBLE initialization parameter to an appropriate value. If you are
upgrading from 8.1.7.4 then set the COMPATIBLE parameter to 9.2.0 until after the
upgrade has been completed successfully.
If you are upgrading from 9.2.0 or 10.1.0
then leave the COMPATIBLE parameter set to it's current value until the upgrade
has been completed successfully. This will avoid any unnecessary ORA-942 errors
from being reported in SMON trace files during the upgrade (because the upgrade
is looking for 10.2 objects that have not yet been created)

SO LEAVE COMPATIBLE PARAMETER AS IT IS FOR US.

32. If you have the parameter NLS_LENGTH_SEMANTICS currently set to CHAR, change the value
to BYTE during the upgrade (to avoid the issue described in Note:4638550.8)

SQL> SHOW PARAMETER NLS_LENGTH

NAME TYPE VALUE
------------------------------------ ----------- -----
nls_length_semantics string BYTE

33. Deleted oracle listener Oraclelistner9i using the add/delete services tool.

34. Copy the tnsnames.ora, listener.ora, sqlnet.ora files and put it in new oracle_home/network/admin directory and update accordingly to the new 10g.

35. Create the service---> oradim -delete -sid jdedb

36. update environment variables to point to 10g.
SET PATH=E:\ORACLE\ORA10G\BIN;%PATH%
SET ORACLE_SID=JDEDB
SET TNS_ADMIN=E:\ORACLE\ORA10G\NETWORK\ADMIN

Now create the new database service ---> oradim -new -sid jdedb -startmode auto

37. sqlplus '/ as sysdba'

SQL> STARTUP UPGRADE;

SELECT STATUS FROM V$INSTANCE;

OPEN MIGRATE

38. CREATE TABLESPACE SYSAUX DATAFILE 'e:\oracle\oradata\JDEDB\sysaux01.dbf' SIZE 2048m REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

39. Now run the following script to upgrade.

spool upgrade10g.txt
@E:\oracle\ora10g\RDBMS\ADMIN\catupgrd.sql
spool off
shutdown immediate;
startup;
spool invalids.txt
@E:\oracle\ora10g\RDBMS\ADMIN\utlrp.sql
spool off;

40. Now grant the system privileges that were missing due to connect role.

GRANT CREATE VIEW TO HR;
GRANT CREATE TABLE TO HR;
GRANT ALTER SESSION TO HR;
GRANT CREATE CLUSTER TO HR;
GRANT CREATE SESSION TO HR;
GRANT CREATE SYNONYM TO HR;
GRANT CREATE SEQUENCE TO HR;
GRANT CREATE DATABASE LINK TO HR;

GRANT CREATE VIEW TO OE;
GRANT CREATE TABLE TO OE;
GRANT ALTER SESSION TO OE;
GRANT CREATE CLUSTER TO OE;
GRANT CREATE SESSION TO OE;
GRANT CREATE SYNONYM TO OE;
GRANT CREATE SEQUENCE TO OE;
GRANT CREATE DATABASE LINK TO OE;

GRANT CREATE VIEW TO PM;
GRANT CREATE TABLE TO PM;
GRANT ALTER SESSION TO PM;
GRANT CREATE CLUSTER TO PM;
GRANT CREATE SESSION TO PM;
GRANT CREATE SYNONYM TO PM;
GRANT CREATE SEQUENCE TO PM;
GRANT CREATE DATABASE LINK TO PM;

GRANT CREATE VIEW TO QS;
GRANT CREATE TABLE TO QS;
GRANT ALTER SESSION TO QS;
GRANT CREATE CLUSTER TO QS;
GRANT CREATE SESSION TO QS;
GRANT CREATE SYNONYM TO QS;
GRANT CREATE SEQUENCE TO QS;
GRANT CREATE DATABASE LINK TO QS;

GRANT CREATE VIEW TO SH;
GRANT CREATE TABLE TO SH;
GRANT ALTER SESSION TO SH;
GRANT CREATE CLUSTER TO SH;
GRANT CREATE SESSION TO SH;
GRANT CREATE SYNONYM TO SH;
GRANT CREATE SEQUENCE TO SH;
GRANT CREATE DATABASE LINK TO SH;

GRANT CREATE VIEW TO PSFT;
GRANT CREATE TABLE TO PSFT;
GRANT ALTER SESSION TO PSFT;
GRANT CREATE CLUSTER TO PSFT;
GRANT CREATE SESSION TO PSFT;
GRANT CREATE SYNONYM TO PSFT;
GRANT CREATE SEQUENCE TO PSFT;
GRANT CREATE DATABASE LINK TO PSFT;

GRANT CREATE VIEW TO RMAN;
GRANT CREATE TABLE TO RMAN;
GRANT ALTER SESSION TO RMAN;
GRANT CREATE CLUSTER TO RMAN;
GRANT CREATE SESSION TO RMAN;
GRANT CREATE SYNONYM TO RMAN;
GRANT CREATE SEQUENCE TO RMAN;
GRANT CREATE DATABASE LINK TO RMAN;

GRANT CREATE VIEW TO DV810;
GRANT CREATE TABLE TO DV810;
GRANT ALTER SESSION TO DV810;
GRANT CREATE CLUSTER TO DV810;
GRANT CREATE SESSION TO DV810;
GRANT CREATE SYNONYM TO DV810;
GRANT CREATE SEQUENCE TO DV810;
GRANT CREATE DATABASE LINK TO DV810;

GRANT CREATE VIEW TO QS_CB;
GRANT CREATE TABLE TO QS_CB;
GRANT ALTER SESSION TO QS_CB;
GRANT CREATE CLUSTER TO QS_CB;
GRANT CREATE SESSION TO QS_CB;
GRANT CREATE SYNONYM TO QS_CB;
GRANT CREATE SEQUENCE TO QS_CB;
GRANT CREATE DATABASE LINK TO QS_CB;

GRANT CREATE VIEW TO QS_CS;
GRANT CREATE TABLE TO QS_CS;
GRANT ALTER SESSION TO QS_CS;
GRANT CREATE CLUSTER TO QS_CS;
GRANT CREATE SESSION TO QS_CS;
GRANT CREATE SYNONYM TO QS_CS;
GRANT CREATE SEQUENCE TO QS_CS;
GRANT CREATE DATABASE LINK TO QS_CS;

GRANT CREATE VIEW TO QS_ES;
GRANT CREATE TABLE TO QS_ES;
GRANT ALTER SESSION TO QS_ES;
GRANT CREATE CLUSTER TO QS_ES;
GRANT CREATE SESSION TO QS_ES;
GRANT CREATE SYNONYM TO QS_ES;
GRANT CREATE SEQUENCE TO QS_ES;
GRANT CREATE DATABASE LINK TO QS_ES;

GRANT CREATE VIEW TO QS_OS;
GRANT CREATE TABLE TO QS_OS;
GRANT ALTER SESSION TO QS_OS;
GRANT CREATE CLUSTER TO QS_OS;
GRANT CREATE SESSION TO QS_OS;
GRANT CREATE SYNONYM TO QS_OS;
GRANT CREATE SEQUENCE TO QS_OS;
GRANT CREATE DATABASE LINK TO QS_OS;

GRANT CREATE VIEW TO QS_WS;
GRANT CREATE TABLE TO QS_WS;
GRANT ALTER SESSION TO QS_WS;
GRANT CREATE CLUSTER TO QS_WS;
GRANT CREATE SESSION TO QS_WS;
GRANT CREATE SYNONYM TO QS_WS;
GRANT CREATE SEQUENCE TO QS_WS;
GRANT CREATE DATABASE LINK TO QS_WS;

GRANT CREATE VIEW TO SCOTT;
GRANT CREATE TABLE TO SCOTT;
GRANT ALTER SESSION TO SCOTT;
GRANT CREATE CLUSTER TO SCOTT;
GRANT CREATE SESSION TO SCOTT;
GRANT CREATE SYNONYM TO SCOTT;
GRANT CREATE SEQUENCE TO SCOTT;
GRANT CREATE DATABASE LINK TO SCOTT;

GRANT CREATE VIEW TO CRPCTL;
GRANT CREATE TABLE TO CRPCTL;
GRANT ALTER SESSION TO CRPCTL;
GRANT CREATE CLUSTER TO CRPCTL;
GRANT CREATE SESSION TO CRPCTL;
GRANT CREATE SYNONYM TO CRPCTL;
GRANT CREATE SEQUENCE TO CRPCTL;
GRANT CREATE DATABASE LINK TO CRPCTL;

GRANT CREATE VIEW TO CRPDTA;
GRANT CREATE TABLE TO CRPDTA;
GRANT ALTER SESSION TO CRPDTA;
GRANT CREATE CLUSTER TO CRPDTA;
GRANT CREATE SESSION TO CRPDTA;
GRANT CREATE SYNONYM TO CRPDTA;
GRANT CREATE SEQUENCE TO CRPDTA;
GRANT CREATE DATABASE LINK TO CRPDTA;

GRANT CREATE VIEW TO JDEDBA;
GRANT CREATE TABLE TO JDEDBA;
GRANT ALTER SESSION TO JDEDBA;
GRANT CREATE CLUSTER TO JDEDBA;
GRANT CREATE SESSION TO JDEDBA;
GRANT CREATE SYNONYM TO JDEDBA;
GRANT CREATE SEQUENCE TO JDEDBA;
GRANT CREATE DATABASE LINK TO JDEDBA;

GRANT CREATE VIEW TO QS_ADM;
GRANT CREATE TABLE TO QS_ADM;
GRANT ALTER SESSION TO QS_ADM;
GRANT CREATE CLUSTER TO QS_ADM;
GRANT CREATE SESSION TO QS_ADM;
GRANT CREATE SYNONYM TO QS_ADM;
GRANT CREATE SEQUENCE TO QS_ADM;
GRANT CREATE DATABASE LINK TO QS_ADM;

GRANT CREATE VIEW TO AUDITOR;
GRANT CREATE TABLE TO AUDITOR;
GRANT ALTER SESSION TO AUDITOR;
GRANT CREATE CLUSTER TO AUDITOR;
GRANT CREATE SESSION TO AUDITOR;
GRANT CREATE SYNONYM TO AUDITOR;
GRANT CREATE SEQUENCE TO AUDITOR;
GRANT CREATE DATABASE LINK TO AUDITOR;

GRANT CREATE VIEW TO PRODCTL;
GRANT CREATE TABLE TO PRODCTL;
GRANT ALTER SESSION TO PRODCTL;
GRANT CREATE CLUSTER TO PRODCTL;
GRANT CREATE SESSION TO PRODCTL;
GRANT CREATE SYNONYM TO PRODCTL;
GRANT CREATE SEQUENCE TO PRODCTL;
GRANT CREATE DATABASE LINK TO PRODCTL;

GRANT CREATE VIEW TO PRODDTA;
GRANT CREATE TABLE TO PRODDTA;
GRANT ALTER SESSION TO PRODDTA;
GRANT CREATE CLUSTER TO PRODDTA;
GRANT CREATE SESSION TO PRODDTA;
GRANT CREATE SYNONYM TO PRODDTA;
GRANT CREATE SEQUENCE TO PRODDTA;
GRANT CREATE DATABASE LINK TO PRODDTA;

GRANT CREATE VIEW TO TESTCTL;
GRANT CREATE TABLE TO TESTCTL;
GRANT ALTER SESSION TO TESTCTL;
GRANT CREATE CLUSTER TO TESTCTL;
GRANT CREATE SESSION TO TESTCTL;
GRANT CREATE SYNONYM TO TESTCTL;
GRANT CREATE SEQUENCE TO TESTCTL;
GRANT CREATE DATABASE LINK TO TESTCTL;

GRANT CREATE VIEW TO TESTDTA;
GRANT CREATE TABLE TO TESTDTA;
GRANT ALTER SESSION TO TESTDTA;
GRANT CREATE CLUSTER TO TESTDTA;
GRANT CREATE SESSION TO TESTDTA;
GRANT CREATE SYNONYM TO TESTDTA;
GRANT CREATE SEQUENCE TO TESTDTA;
GRANT CREATE DATABASE LINK TO TESTDTA;

GRANT CREATE VIEW TO QS_CBADM;
GRANT CREATE TABLE TO QS_CBADM;
GRANT ALTER SESSION TO QS_CBADM;
GRANT CREATE CLUSTER TO QS_CBADM;
GRANT CREATE SESSION TO QS_CBADM;
GRANT CREATE SYNONYM TO QS_CBADM;
GRANT CREATE SEQUENCE TO QS_CBADM;
GRANT CREATE DATABASE LINK TO QS_CBADM;

41. ADD FLASH RECOVERY AREA

42. UPDATE ALL THE SCHEDULED BACKUPS ACCORDINGLY TO NEW UPGRADED ORACLE DATABASE.

You can refer to the following metalink document if you need further assistance or email me at samiora@gmail.com

Note 316889.1 Complete Checklist for Manual Upgrades to 10gR2.docx

Physical Standby database using Dataguard

2010-10-10T23:34:00.000-07:00

Oracle 10g Data Guard is a great tool to ensure high availability, data protection and disaster recovery for enterprise data. I have been working on Data Guard/Standby databases using both Grid control and SQL command line from 2005 while I was working with Oracle Corporation then, and till now using Data guard technology while now I am working with a government sector as Senior Applications DBA.

My latest experience with Data Guard was manually creating a Physical Standby Database in a Disaster Recovery DR project. I am maintaining it daily and it works well. I would like to share my experience with the other DBAs.

In this example the database version is 10.2.0.1. The Primary database and Standby database are located on different machines at different sites. The Primary database is called CELL and the Standby database is called STAN. I use Flash Recovery Area, and OMF.

I. Before you get started:

1. Make sure the operating system and platform architecture on the primary and standby systems are the same.

2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.

3. Test the Standby Database creation on a test environment first before working on the Production database.

II. On the Primary Database Side:

1. Enable forced logging on your primary database:

SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn’t exist.

i) To check if a password file already exists, run the following command:

SQL> SELECT * FROM V$PWFILE_USERS;

ii) If it doesn’t exist, use the following command to create one:

- On Windows:
$cd %ORACLE_HOME%\database

$orapwd file=pwdCELL.ora password=***** force=y

(Note: Replace with the actual Oracle home path, and ***** with the password for the SYS user.)

- On UNIX:
$cd $ORACLE_HOME/dbs

$Orapwd file=pwdCELL.ora password=***** force=y

(Note: Replace ***** with your actual password for the SYS user.)

3. Configure a Standby Redo log.

i) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:

SQL> select bytes/1024/1024 MB from v$log;

MB
----
50
50
50

ii) Use the following command to determine your current log file groups:

SQL> select group#, member from v$logfile;

iii) Create standby Redo log groups.

My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:

IF OMF (Oracle Managed Files)
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

If non OMF (Manually managed files)

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('C:\oracle\product\10.2.0\oradata\cell\REDO04.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('C:\oracle\product\10.2.0\oradata\cell\REDO05.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('C:\oracle\product\10.2.0\oradata\cell\REDO06.LOG') SIZE 50M;

4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log; (or)
SQL> SELECT GROUP#,DBID,BYTES/1024/1024 MB FROM V$STANDBY_LOG;

GROUP# DBID MB
---------- ---------------------------
4 UNASSIGNED 50
5 UNASSIGNED 50
6 UNASSIGNED 50

4. Enable Archiving on Primary.

If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\oracle\product\10.2.0\flash_recovery_area\CELL\ARCHIVELOG
Oldest online log sequence 92
Next log sequence to archive 94
Current log sequence 94

5. Set Primary Database Initialization Parameters

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

i) Create pfile from spfile for the primary database:

- On Windows:
SQL>create pfile=’c:\pfileCELL.ora’ from spfile;

- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;

ii) Edit this pfile to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

cell.__db_cache_size=490733568
cell.__java_pool_size=4194304
cell.__large_pool_size=4194304
cell.__shared_pool_size=104857600
cell.__streams_pool_size=0
*.audit_file_dest='c:\oracle\product\10.2.0\admin\cell\adump'
*.background_dump_dest='c:\oracle\product\10.2.0\admin\cell\bdump'
*.compatible='10.2.0.1.0'
*.control_files='c:\oracle\product\10.2.0\oradata\cell\control01.ctl','c:\oracle\product\10.2.0\oradata\cell\control02.ctl','c:\oracle\product\10.2.0\oradata\cell\control03.ctl'
*.core_dump_dest='c:\oracle\product\10.2.0\admin\cell\cdump'*.db_block_size=8192
*.db_cache_size=490733568
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\stan','c:\oracle\product\10.2.0\oradata\cell'
*.db_name='cell'
*.db_recovery_file_dest='c:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='cell'
*.FAL_CLIENT='cell'
*.FAL_SERVER='stan'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cell,stan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\oracle\product\10.2.0\flash_recovery_area\CELL\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cell'
*.LOG_ARCHIVE_DEST_2='SERVICE=stan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stan'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\stan','c:\oracle\product\10.2.0\oradata\cell'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.shared_pool_size=104857600
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.streams_pool_size=0
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='c:\oracle\product\10.2.0/admin/cell/udump'

(Note: For DB_FILE_NAME_CONVERT -Specify the location of the standby DB datafiles followed by the primary location;
For LOG_FILE_NAME_CONVERT - Specify the location of the standby DB online redo log files followed by the primary location.)

6. Create spfile from pfile, and restart primary database using the new spfile.

Data Guard must use SPFILE. Create the SPFILE and restart database.

- On windows:

SQL> shutdown immediate;
SQL> startup nomount pfile=’c:\pfileCELL.ora’;
SQL>create spfile from pfile=’c:\pfileCELL.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

- On Unix:

SQL> shutdown immediate;
SQL> startup nomount pfile=’/u01/app/oraclepfileCELL.ora’;
SQL>create spfile from pfile=’/u01/app/oracle/pfileCELL.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

On the Standby Database Site:

1. Create a copy of Primary database data files on the Standby Server:

On Primary DB:
SQL>shutdown immediate;
On Standby Server (While the Primary database is shut down):
i) Create directory for data files, for example, on windows,
c:\oracle\product\10.2.0\oradata\stan

ii) Copy the data files and temp files over.

iii) Create directory for online logs, for example, on Windows, c:\oracle\product\10.2.0\oradata\stan

iv) Copy the online logs over.

2. Create a Control File for the standby database:

On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;

File will be created in %ORACLE_HOME%\database directory.

SQL>ALTER DATABASE OPEN;

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

i) Copy pfileCELL.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.

ii) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

stan.__db_cache_size=490733568
stan.__java_pool_size=4194304
stan.__large_pool_size=4194304
stan.__shared_pool_size=104857600
stan.__streams_pool_size=0
*.audit_file_dest='c:\oracle\product\10.2.0\admin\stan\adump'
*.background_dump_dest='c:\oracle\product\10.2.0\admin\stan\bdump'
*.compatible='10.2.0.1.0'
*.control_files='c:\oracle\product\10.2.0\oradata\stan\stan.ctl','c:\oracle\stan.ctl'
*.core_dump_dest='c:\oracle\product\10.2.0\admin\stan\cdump'
*.db_block_size=8192
*.db_cache_size=490733568
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\cell','c:\oracle\product\10.2.0\oradata\stan'
*.db_name='cell'
*.db_recovery_file_dest='c:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='stan'
*.FAL_CLIENT='stan'
*.FAL_SERVER='cell'
*.java_pool_size=4194304
*.job_queue_processes=10
*.large_pool_size=4194304
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cell,stan)'
*.LOG_ARCHIVE_DEST_1='LOCATION=c:\oracle\product\10.2.0\flash_recovery_area\stan\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stan'
*.LOG_ARCHIVE_DEST_2='SERVICE=cell LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cell'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='c:\oracle\product\10.2.0\oradata\cell','c:\oracle\product\10.2.0\oradata\stan'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=612368384
*.shared_pool_size=104857600
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='c:\oracle\product\10.2.0\admin\stan\udump'

4) On Standby server, create all required directories for dump and archived log destination:

Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.
'c:\oracle\product\10.2.0\admin\stan\adump'
'c:\oracle\product\10.2.0\admin\stan\bdump'
'c:\oracle\product\10.2.0\admin\stan\cdump'
'c:\oracle\product\10.2.0\admin\stan\udump'
c:\oracle\product\10.2.0\flash_recovery_area\stan\ARCHIVELOG

5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations
c:\oracle\STAN.CTL
c:\oracle\product\10.2.0\oradata\stan\STAN.CTL

6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.

On Windows copy it to %ORACLE_HOME%\database folder, and on UNIX copy it to $ORACLE_HOME/dbs directory. And then rename the password file.

7. For Windows, create a Windows-based services (optional):

$oradim –NEW –SID STAN –STARTMODE manual

8. Configure listeners for the primary and standby databases.

i) On Primary system:
use Oracle Net Manager to configure a listener for PRIM and STAN services.
Then restart the listener.

$lsnrctl stop
$lsnrctl start

Content of LISTENER.ORA file on PRIMARY Server is,

# listener.ora Network Configuration File: c:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = AbdulsamiPC.qic.local)(PORT = 1521))
)
)

ii) On Standby server: use Net Manager to configure a listener for PRIM and STAN services. Then restart the listener.

$lsnrctl stop
$lsnrctl start

9. Create Oracle Net service names.

i) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN.
Check tnsping to both services:

$tnsping PRIM
$tnsping STAN

ii) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN.
Check tnsping to both services:

$tnsping PRIM
$tnsping STAN

Content of TNSNAMES.ORA File on both PRIMARY and STANDBY Server is,

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
CELL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = abdulsamipc)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cell)
)
)
STAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = asamipc)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stan)
)
)

10. On Standby server, setup the environment variables to point to the Standby database.

Set up ORACLE_HOME and ORACLE_SID.

11. Start up nomount the standby database and generate a spfile.

- On Windows:
SQL>startup nomount pfile=’c:\pfileSTAN.ora’;
SQL>create spfile from pfile=’c:\pfileSTAN.ora’;

-- Restart the Standby database using the newly created SPFILE.

SQL>shutdown immediate;
SQL>startup mount;

- On Unix:
SQL>startup nomount pfile=’$ORACLE_HOME/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’$ORACLE_HOME/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).

12. Start Redo apply

i) On the standby database, to start redo apply:

SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;

13. Verify the standby database is performing properly:

i) On Standby perform a query:

SQL>select sequence#, first_time, next_time from v$archived_log;

ii) On Primary, force a logfile switch:

SQL>alter system switch logfile;

iii) On Standby, verify the archived redo log files were applied:

SQL>select sequence#, applied from v$archived_log order by sequence#;

14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

Maintenance:

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.

$rman target 'sys/*****@STAN';

Rman>backup archivelog all delete input;

3. Password management

The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server. If the password files are not in sync then update/recreate password file for the Standby database.

Reference:
Oracle Data Guard Concepts and Administration 10g Release 2 (10.2), B14239-04

For any queries please don't hesitate to contact me, Sami Malik (samiora@gmail.com)
[Senior Oracle Applicaitons DBA at Government Sector in Middle East]
Cell: 0097433164959

Disaster Recovery Project Guidelines

2010-09-29T02:54:00.000-07:00

Kickoff for Disaster Recovery project is tomorrow (30Sept2010). I will be very busy during this project as we are preparing the DR for all the business critical applications at the local DR site in the same country-Qa. While from January2011 we will be implementing another DR site in Europe. I will keep this blog updated with the updates on the DR project that we are starting tomorrow. Please wish me good luck and thanks for all your support and for visiting this blog. For any queries please don't hesitate to contact me on my email masamimalik@yahoo.com, samiora@gmail.com.

Disaster recovery is becoming increasingly important for businesses aware of the threat of both man-made and natural disasters. Having a disaster recovery plan will not only protect organization’s essential data from destruction, it will help refine business processes and enable business to recover its operations in the event of a disaster. Though each organization has unique knowledge and assets to maintain, general principles can be applied to disaster recovery. This set of planning guidelines can assist organization in moving forward with an IT disaster recovery project.

Accountability and endorsement
A key factor in the success of your disaster recovery plan will be holding someone on the executive management team accountable. It could be the CIO, CTO, COO, or, if your company is small, the IT director. Whether this person manages the disaster recovery preparations or delegates them to someone else, it will be necessary for the entire organization to know that the disaster recovery preparations are deemed essential by executive management in order to obtain the cooperation you’ll need from all the staff involved. Without endorsement from higher management, collecting all the information you’ll need to make the disaster recovery project a success will be much more difficult. Even if the disaster recovery project is managed by someone who has had the task delegated, upper management needs to convey to the entire organization the importance and essentiality of the project.

Identify and organize your data
One of the first steps in putting together a disaster recovery plan is to identify your mission-critical data. Doing so will help you understand what data you need to back up for off-site storage. It will also prompt you to document why you need this data and plan how to put it back where it belongs in the event of a recovery operation.

Next, instruct your users to assist you in organizing the data in intuitive directories on a central server. Even if you plan to just back up everything, knowing which files are where is a key part of the recovery process. If, for example, when disaster strikes you have customer data spread all over your network on different users' hard drives, finding the data will not be easy. Restoring all the data from backup media is only half the battle. Once the data is restored, you don't want to be walking around the office saying, "Does anyone know where we keep the XYZ contract?" The data must be organized before you back it up.

Some data types that you should take into consideration for organization on a central repository are as follows:

Key customer files: contracts, agreements, contact information, proposals
User login data: profiles, UNIX .dot files, Config.sys files, Autoexec.bat files
Network infrastructure files: DNS, WINS, DHCP, router tables
User directories
Application data: databases, Web site files
Security configuration files: ACLs, firewall rules, IDS configuration files, UNIX password/shadow files, Microsoft Windows SAM database, VPN configuration files, RADIUS configuration files
Messaging files: key configuration files, user mailboxes, system accounts
Engineer files: source code, release engineering code
Financial and company files: general ledger, insurance policies, accounts payable and accounts receivable files, incorporation registration, employee resource planning (ERP) data
License files for applications

Asset inventory
Aside from the data itself, your company needs to have an up-to-date hardware and software asset inventory list on hand at all times. The hardware list should include the equipment make, model, and serial number, and a description of what each particular piece of equipment is being used for. The software inventory should be similar, with the vendor name, version number, patch number, license information, and what the software is being used for. The information for each piece of equipment and software on the list should be mapped to the corresponding devices on the company network map. Be sure to include all cables and connectors, as well as peripheral devices such as printers, fax machines, and scanners.You might want to submit the asset inventory list to your insurance company once a year.

Restoration and recovery procedures
Imagine that a disaster has occurred. You have the data, now what should you do with it? If you don’t have any restoration and recovery procedures, your data won’t be nearly as useful to you. With the data in hand, you need to be able to re-create your entire business from brand-new systems. You’re going to need procedures for rebuilding systems and networks. System recovery and restoration procedures are typically best written by the people that currently administer and maintain the systems. Each system should have recovery procedures that indicate which versions of software and patches should be installed on which types of hardware platforms. It's also important to indicate which configuration files should be restored into which directories. A good procedure will include low-level file execution instructions, such as what commands to type and in what order to type them.

Document decision-making processes
Recovering your data, systems, and networks is one thing, but when you lose staff, recovering the knowledge they held is quite different. You will never be able to recover that knowledge completely. However, you can mitigate this loss by documenting decision-making processes in flowcharts. To do this, have each of your staff identify decisions that they make and then create flowcharts for their thought processes. Sample decisions could be:
How much do you charge for a new service?
How do you know if taking on a particular new project is worth the return?
How do you evaluate new business?
How do you decide whom you should partner with?
How do you decide who your sales prospects are?
How do you decide who your suppliers are?
When a call comes in to your help desk, how does it get routed?
What are your QA procedures for your product?

It's impossible to document every decision your staff is capable of making. To get started, don't ask your staff to document every possible decision-making scenario. Ask them to document the three most important decision-making processes that they use on a consistent basis. You can add new processes to your disaster recovery plan in the future, and you may want to have employees write three new decision-making flowcharts each year at the time of their annual reviews.

Backups are key
As an IT or network administrator, you need to bring all your key data, processes, and procedures together through a backup system that is reliable and easy to replicate. Your IT director's most important job is to ensure that all systems are being backed up on a reliable schedule. This process, though it seems obvious, is often not realized. Assigning backup responsibilities to an administrator is not enough. The IT department needs to have a written schedule that describes which systems get backed up when and whether the backups are full or incremental. You also need to have the backup process fully documented. Finally, test your backup process to make sure it works. Can you restore lost databases? Can you restore lost source code? Can you restore key system files?Finally, you need to store your backup media off-site, preferably in a location at least 50 miles from your present office. Numerous off-site storage vendors offer safe media storage.

Disaster strikes
Let’s say for a moment that the worst occurs and your business is devastated by a disaster, to the point where you need to rebuild your business from scratch. Here are some of the key steps you should take to recover your operations:
1. Notify your insurance company immediately.
2. Identify a recovery site where you will bring your business back up.
3. Obtain your asset inventory list and reorder all lost items.
4. Distribute a network map and asset inventory list to your recovery team.
5. As the new hardware comes in, have your recovery team connect the pieces.
6. Restore your network infrastructure servers first (DNS, routers, etc.).
7. Restore your application servers second.
8. Restore your user data third.
9. Perform any necessary configuration tweaks according to your guidelines.
10. Test all applications for functionality.
11. Test all user logins.
12. Put a notice on your Web site stating that your business was affected by a disaster.

Summary recommendations
It’s likely that in the event of a real disaster, not everything will be recoverable. Your goal should be to recover enough data, processes, and procedures so that your business can be up and running as quickly as possible, once you’re in a new office.Testing your plan is key to ensuring its success. A good way to test your plan is in a lab setting. With uninstalled systems that aren’t connected to the network, see how fast you can install your systems, configure them, and restore essential data. The best test is to use a recovery staff other than the everyday staff that uses and administers the systems. By using staff that aren’t familiar with everyday usage of your systems and applications, you’ll uncover deficiencies in the processes and procedures you’ve documented. Time your recovery scenario and see if you can improve the time it takes for recovery each time you hold a practice drill.

Conclusion
A disaster recovery plan is essential to your company’s long-term success. Even if you never have to use the plan, the process of putting it together will by its very nature increase the security of your assets and improve your overall business efficiency. The preparation of a disaster recovery plan will teach you what data is important and will necessitate that you understand how your business works from a decision-making standpoint. Disaster recovery can be more easily achieved if you follow this simple outline:
>Hold someone accountable for disaster recovery.
>Identify mission-critical data.
>Organize data on a central repository.
>Create procedures for recovering mission-critical servers.
>Create knowledge-based decision-making flowcharts.
>Back up your data on a regular schedule.
>Store your data off-site.
>Test your recovery plan.

For 'Download these guidelines for developing a disaster recovery plan' and more go to the following link,
http://articles.techrepublic.com.com/5100-10878_11-1060886.html?tag=rbxccnbtr1

Discoverer 11g certified on new platforms

2010-08-30T01:30:00.001-07:00

Oracle Business Intelligence Discoverer is an ad-hoc query, reporting, analysis, and Web-publishing tool that allows end-users to work directly with Oracle E-Business Suite OLTP data.

Oracle has certified Discoverer 11g version 11.1.1.3 (Patchset 2) with the E-Business Suite on six platforms. Those certifications included Oracle Enterprise Linux, Red Hat Enterprise Linux, and SUSE Linux Enterprise Server.

As a follow-on to that original certification announcement, Oracle Fusion Middleware 11g Release 1 Discoverer version 11.1.1.3 is now certified with Oracle E-Business Suite Release 11i and R12 (12.0.x, 12.1.x) on a dozen additional platforms.

New platforms certified for EBS 11i

* HP-UX PA-RISC (64-bit) (11.23, 11.31)
* IBM AIX on Power Systems (64-bit) (5.3, 6.1)
* Microsoft Windows Server (32-bit) (2003)
* Oracle Solaris on SPARC (64-bit) (9, 10)

New platforms certified for EBS 12.0.x and 12.1.x

* Linux x86-64 (Oracle Enterprise Linux 4, 5)
* Linux x86-64 (RHEL 4, 5)
* Linux x86-64 (SLES 10)
* HP-UX Itanium (11.23, 11.31)
* HP-UX PA-RISC (64-bit) (11.23, 11.31)
* IBM AIX on Power Systems (64-bit) (5.3, 6.1)
* Microsoft Windows Server (32-bit) (2003, 2008)
* Oracle Solaris on SPARC (64-bit) (9, 10)

References

* Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12 (Note 1074326.1)
* Using Discoverer 11.1.1 with Oracle E-Business Suite Release 11i (Note 1073963.1)

Linux Cron job examples

2010-07-07T03:43:00.000-07:00

MIN HOUR DOM MON DOW CMD > /tmp/output.log

Crontab Fields and Allowed Ranges (Linux Crontab Syntax)

Field Description AllowedValue
MIN Minute field 0 to 59
HOUR Hour field 0 to 23
DOM Day of Month 1-31
MON Month field 1-12
DOW Day Of Week 0-6
CMD Command Any command to be executed

/tmp/output.log is the command output log file.

1. Scheduling a Job For a Specific Time Every Day
The basic usage of cron is to execute a job in a specific time as shown below. This will execute the Full backup shell script (full-backup) on 10th June 08:30 AM.

Please note that the time field uses 24 hours format. So, for 8 AM use 8, and for 8 PM use 20.

30 08 10 06 * /home/samimalik/full-backup30 – 30th Minute
08 – 08 AM
10 – 10th Day
06 – 6th Month (June)
* – Every day of the week

2. Schedule a Job For More Than One Instance (e.g. Twice a Day)The following script take a incremental backup twice a day every day.

This example executes the specified incremental backup shell script (incremental-backup) at 11:00 and 16:00 on every day. The comma separated value in a field specifies that the command needs to be executed in all the mentioned time.

00 11,16 * * * /home/samimalik/bin/incremental-backup00 – 0th Minute (Top of the hour)
11,16 – 11 AM and 4 PM
* – Every day
* – Every month
* – Every day of the week

3. Schedule a Job for Specific Range of Time (e.g. Only on Weekdays)
If you wanted a job to be scheduled for every hour with in a specific range of time then use the following.

Cron Job everyday during working hours
This example checks the status of the database everyday (including weekends) during the working hours 9 a.m – 6 p.m

00 09-18 * * * /home/samimalik/bin/check-db-status00 – 0th Minute (Top of the hour)
09-18 – 9 am, 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
* – Every day
* – Every month
* – Every day of the week
Cron Job every weekday during working hours
This example checks the status of the database every weekday (i.e excluding Sat and Sun) during the working hours 9 a.m – 6 p.m.

00 09-18 * * 1-5 /home/samimalik/bin/check-db-status00 – 0th Minute (Top of the hour)
09-18 – 9 am, 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
* – Every day
* – Every month
1-5 -Mon, Tue, Wed, Thu and Fri (Every Weekday)

4. How to View Crontab Entries?
View Current Logged-In User’s Crontab entries
To view your crontab entries type crontab -l from your unix account as shown below.

samimalik@dev-db$ crontab -l
@yearly /home/samimalik/annual-maintenance
*/10 * * * * /home/samimalik/check-disk-space

[Note: This displays crontab of the current logged in user]

View Root Crontab entries
Login as root user (su – root) and do crontab -l as shown below.

root@dev-db# crontab -l

no crontab for rootCrontab HowTo: View Other Linux User’s Crontabs entries
To view crontab entries of other Linux users, login to root and use -u {username} -l as shown below.

root@dev-db# crontab -u sathiya -l
@monthly /home/sathiya/monthly-backup
00 09-18 * * * /home/sathiya/check-db-status

5. How to Edit Crontab Entries?
Edit Current Logged-In User’s Crontab entries
To edit a crontab entries, use crontab -e as shown below. By default this will edit the current logged-in users crontab.

samimalik@dev-db$ crontab -e
@yearly /home/samimalik/centos/bin/annual-maintenance
*/10 * * * * /home/samimalik/debian/bin/check-disk-space
~
"/tmp/crontab.XXXXyjWkHw" 2L, 83C

[Note: This will open the crontab file in Vim editor for editing.
Please note cron created a temporary /tmp/crontab.XX... ]When you save the above temporary file with :wq, it will save the crontab and display the following message indicating the crontab is successfully modified.

~
"crontab.XXXXyjWkHw" 2L, 83C written
crontab: installing new crontabEdit Root Crontab entries
Login as root user (su – root) and do crontab -e as shown below.

root@dev-db# crontab -eEdit Other Linux User’s Crontab File entries
To edit crontab entries of other Linux users, login to root and use -u {username} -e as shown below.

root@dev-db# crontab -u sathiya -e
@monthly /home/sathiya/fedora/bin/monthly-backup
00 09-18 * * * /home/sathiya/ubuntu/bin/check-db-status
~
~
~
"/tmp/crontab.XXXXyjWkHw" 2L, 83C

6. Schedule a Job for Every Minute Using Cron.
Ideally you may not have a requirement to schedule a job every minute. But understanding this example will will help you understand the other examples mentioned below in this article.

* * * * * CMDThe * means all the possible unit — i.e every minute of every hour through out the year. More than using this * directly, you will find it very useful in the following cases.

When you specify */5 in minute field means every 5 minutes.
When you specify 0-10/2 in minute field mean every 2 minutes in the first 10 minute.
Thus the above convention can be used for all the other 4 fields.

7. Schedule a Background Cron Job For Every 10 Minutes.
Use the following, if you want to check the disk space every 10 minutes.

*/10 * * * * /home/samimalik/check-disk-spaceIt executes the specified command check-disk-space every 10 minutes through out the year. But you may have a requirement of executing the command only during office hours or vice versa. The above examples shows how to do those things.

Instead of specifying values in the 5 fields, we can specify it using a single keyword as mentioned below.

There are special cases in which instead of the above 5 fields you can use @ followed by a keyword — such as reboot, midnight, yearly, hourly.

Table: Cron special keywords and its meaning Keyword Equivalent
@yearly 0 0 1 1 *
@daily 0 0 * * *
@hourly 0 * * * *
@reboot Run at startup.

8. Schedule a Job For First Minute of Every Year using @yearly
If you want a job to be executed on the first minute of every year, then you can use the @yearly cron keyword as shown below.

This will execute the system annual maintenance using annual-maintenance shell script at 00:00 on Jan 1st for every year.

@yearly /home/samimalik/red-hat/bin/annual-maintenance

9. Schedule a Cron Job Beginning of Every Month using @monthly
It is as similar as the @yearly as above. But executes the command monthly once using @monthly cron keyword.

This will execute the shell script tape-backup at 00:00 on 1st of every month.

@monthly /home/samimalik/suse/bin/tape-backup

10. Schedule a Background Job Every Day using @daily
Using the @daily cron keyword, this will do a daily log file cleanup using cleanup-logs shell scriptat 00:00 on every day.

@daily /home/samimalik/arch-linux/bin/cleanup-logs "day started"

11. How to Execute a Linux Command After Every Reboot using @reboot?
Using the @reboot cron keyword, this will execute the specified command once after the machine got booted every time.

@reboot CMD

12. How to Disable/Redirect the Crontab Mail Output using MAIL keyword?
By default crontab sends the job output to the user who scheduled the job. If you want to redirect the output to a specific user, add or update the MAIL variable in the crontab as shown below.

samimalik@dev-db$ crontab -l
MAIL="samimalik"

@yearly /home/samimalik/annual-maintenance
*/10 * * * * /home/samimalik/check-disk-space

[Note: Crontab of the current logged in user with MAIL variable]
If you wanted the mail not to be sent to anywhere, i.e to stop the crontab output to be emailed, add or update the MAIL variable in the crontab as shown below.

MAIL=""

13. How to Execute a Linux Cron Jobs Every Second Using Crontab.
You cannot schedule a every-second cronjob. Because in cron the minimum unit you can specify is minute. In a typical scenario, there is no reason for most of us to run any job every second in the system.

14. Specify PATH Variable in the Crontab
All the above examples we specified absolute path of the Linux command or the shell-script that needs to be executed.

For example, instead of specifying /home/samimalik/tape-backup, if you want to just specify tape-backup, then add the path /home/samimalik to the PATH variable in the crontab as shown below.

samimalik@dev-db$ crontab -l

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/home/samimalik

@yearly annual-maintenance
*/10 * * * * check-disk-space

[Note: Crontab of the current logged in user with PATH variable]15. Installing Crontab From a Cron File
Instead of directly editing the crontab file, you can also add all the entries to a cron-file first. Once you have all thoese entries in the file, you can upload or install them to the cron as shown below.

samimalik@dev-db$ crontab -l
no crontab for samimalik

$ cat cron-file.txt
@yearly /home/samimalik/annual-maintenance
*/10 * * * * /home/samimalik/check-disk-space

samimalik@dev-db$ crontab cron-file.txt

samimalik@dev-db$ crontab -l
@yearly /home/samimalik/annual-maintenance
*/10 * * * * /home/samimalik/check-disk-spaceNote: This will install the cron-file.txt to your crontab, which will also remove your old cron entries. So, please be careful while uploading cron entries from a cron-file.txt.

Upgrade steps from 11i to r12 with db 11g - rough

2010-06-21T01:24:00.000-07:00

Hi Folks, Last month I was busy in upgrade project. Upgrade EBS 11i (11.5.10.2) to release 12 (12.1.1) with 64bit oracle 11g (11.1.0.7) database. The following were the steps that were performed. The steps are just a rough run but if you want to see the exact phases and tasks then please refer to my earlier post 'Tasks and Phases to Upgrade Oracle Applications 11i to r12 (12.1.1)'. For any queries please email me at masamimalik@yahoo.com or call me on my cell 0097433164959.

Also refer to the oracle material from the following link that gives the details on how to upgrade.

http://download.oracle.com/docs/cd/B53825_05/current/acrobat/121upgrade.pdf

1) clone the apps tier and the db tier from PROD to TEST (perform this on TEST DB)

2) Apply the ATG RUP 6
Apply these patches on 11.5.10.2 using 9i database
4252319 - 06:48 PM - 06:50 (total 2 min)
5903765 - 06:52 PM - 08:30 PM
starts with 315 jobs
there will be 14000 invali objects
while running autoconfig , there were file locks .. so press ctrl +c and then reboot the server + then restart the adpatch session
5903765_AR
4583125
7705743
before upgrade and running the utlu111i.sql copy the timezone files from the patch or from the oacore directory of any existing 9.2.0 oracle home under f:
3)Upgrade the database to 11gR1 on the same TEST DB server
4)convert to multiorg
5) Install the filesystem pointing to the same driver
6) Apply these patches
6767273
7461070
7) The upgrade American english driver patch starts with 119322 jobs

workers will fail at

Time is: Sat May 15 2010 02:36:41

1) There are now 119259 jobs remaining (current phase=A0):
4 running, 60 ready to run and 119195 waiting.

FAILED: file funmochk.sql on worker 2 for product fun username FUN

Reason:

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\fun\12.0.0\patch\115\sql\funmochk.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.DECLARE
*
ERROR at line 1:
ORA-20000: Multi-Org architecture is not enabled. Please convert the target
instance to enable Multi-Org before upgrading to Release 12
ORA-06512: at line 19

Solution - enable multiorg

2) Time is: Mon May 17 2010 12:08:34

There are now 38169 jobs remaining (current phase=A144):
0 running, 0 ready to run and 38169 waiting.

ATTENTION: All workers either have failed or are waiting:

FAILED: file MSDODPCODE.sql on worker 1.

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\msd\12.0.0\patch\115\sql\MSDODPCODE.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.begin
*
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 984
ORA-06512: at "SYS.DBMS_AW", line 937
ORA-33262: Analytic workspace ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 908
ORA-06512: at "APPS.MSD_AW_LOADER", line 10
ORA-06512: at line 3
FAILED: file pqbgtasd.sql on worker 4.

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\pqh\12.0.0\patch\115\sql\pqbgtasd.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.
PL/SQL procedure successfully completed.

Commit complete.

API User Hook Pre-Processor One Module Report
=============================================
Created on 2010/05/17 12:08:31 (YYYY/MM/DD HH:MM:SS)

CREATE_BUDGET(Business Process API) successful.

Module Name: UPDATE_BUDGET
Module Type: Business Process API

Hook Package: PQH_BUDGETS_BK2
-----------------------------

Hook: Before Process
--------------------
(XX_RETURN_FROM_LEAVE_PKG.LEAVE_DEL) There is a parameter to the call procedure
which is not available at this hook. Check your call procedure parameters. Code
to carry out this hook call has not been created.

DELETE_BUDGET(Business Process API) successful.
BEGIN hr_api_user_hooks_utility.clear_hook_report; END;

Reason : hookup
solution delete it .

3) Time is: Tue May 18 2010 01:20:39

ATTENTION: All workers either have failed or are waiting:

FAILED: file czhist.sql on worker 1.
Start time for file is: Tue May 18 2010 01:11:38

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\cz\12.0.0\patch\115\sql\czhist.sql &un_cz

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.
PL/SQL procedure successfully completed.

BEGIN FND_STATS.GATHER_COLUMN_STATS('CZ','CZ_DEVL_PROJECTS','DELETED_FLAG'); END;

*
ERROR at line 1:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old. Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "APPS.FND_STATS", line 664
ORA-06512: at "APPS.FND_STATS", line 2497
ORA-06512: at line 1

4)

Time is: Sun May 16 2010 03:04:48

ATTENTION: All workers either have failed or are waiting:

FAILED: file csppl.odf on worker 1.
FAILED: file csprecm.odf on worker 2.
FAILED: file csdcsdv.odf on worker 3.
FAILED: file csmcsma.odf on worker 4.

Time is: Sun May 16 2010 09:24:04
There are now 81692 jobs remaining (current phase=A70):
3 running, 115 ready to run and 81574 waiting.

ATTENTION: All workers either have failed or are waiting:

FAILED: file csppl.odf on worker 1.
FAILED: file csprecm.odf on worker 2.
FAILED: file csdcsdv.odf on worker 3.
FAILED: file csmcsma.odf on worker 4.

ATTENTION: All workers either have failed or are waiting:

FAILED: file bivdelmenu.sql on worker 1.
FAILED: file BIVMEDE71.sql on worker 2.
FAILED: file amwmndel.sql on worker 3.
FAILED: file arhdelmn.sql on worker 4.

Reason :Start time for file is: Sun May 16 2010 15:52:28

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\amw\12.0.0\patch\115\sql\amwmndel.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.BEGIN
*
ERROR at line 1:
ORA-04063: package body "APPS.FND_FUNCTION_SECURITY" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"APPS.FND_FUNCTION_SECURITY"
ORA-06512: at line 2

ATTENTION: All workers either have failed or are waiting:

FAILED: file bivdelmenu.sql on worker 1.
FAILED: file BIVMEDE71.sql on worker 2.
FAILED: file amwmndel.sql on worker 3.
FAILED: file arhdelmn.sql on worker 4.

Time is: Sun May 16 2010 22:52:18

ATTENTION: All workers either have failed or are waiting:

FAILED: file IGSPR4700925.sql on worker 1.

Reason :
sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\igs\12.0.0\patch\115\sql\IGSPR4700925.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system. WHERE CURRENT OF c_pr_inst_sta_ref;
*
ERROR at line 29:
ORA-06550: line 29, column 26:
PLS-00404: cursor 'C_PR_INST_STA_REF' must be declared with FOR UPDATE to use
with CURRENT OF

solution : invalid objects and disable and enable the events parameter in the init.ora file

Time is: Sat May 15 2010 21:34:01

FAILED: file jtf_diagnostic_report_s.xdf on worker 3 for product fnd username APPS.

Time is: Sun May 16 2010 00:52:53

FAILED: file PO_VENDORS_OBS_F1.xdf on worker 2 for product po username APPS.

Time is: Sun May 16 2010 00:52:54

FAILED: file PO_VENDOR_CONTACTS_OBS_N4.xdf on worker 3 for product po username APPS.

Time is: Sun May 16 2010 00:53:40

FAILED: file jtfiaitn.sql on worker 4 for product jtf username JTF.

Reason :
Start time for file is: Sun May 16 2010 00:53:31

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\jtf\12.0.0\patch\115\sql\jtfiaitn.sql &un_jtf &pw_jtf

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.Connected.
DECLARE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_SQL", line 1053
ORA-06512: at line 35

Time is: Sun May 16 2010 02:03:29

FAILED: file eamasso.odf on worker 3 for product eam username EAM.
Time is: Sun May 16 2010 02:03:48

FAILED: file dppobjects.odf on worker 4 for product dpp username DPP.

Time is: Sun May 16 2010 02:05:22

FAILED: file egov083.odf on worker 3 for product ego username EGO.

Start time for statement below is: Sun May 16 2010 02:02:04

D Worker error:
The following ORACLE error:

ORA-00600: internal error code, arguments: [kksfbc-reparse-infinite-loop], [0x549E78EC], [], [], [], [], [], [], [], [], [], []

occurred while executing the SQL statement:

CREATE OR REPLACE FORCE VIEW APPS.CSM_REQ_LINES_V ("ACCESS_ID",
"REQUIREMENT_LINE_ID", "REQUIREMENT_HEADER_ID", "INVENTORY_ITEM_ID",
"ITEM_NAME", "ITEM_DESCRIPTION", "UOM_CODE", "REQUIRED_QUANTITY",
"SHIP_COMPLETE_FLAG", "REVISION", "SOURCE_ORGANIZATION_ID",
"SOURCE_SUBINVENTORY", "ORDERED_QUANTITY", "ORDER_LINE_ID",
"RESERVATION_ID", "LOCAL_RESERVATION_ID", "ORDER_BY_DATE", "ARRIVAL_DATE",
"SHIPPING_METHOD_CODE", "LAST_UPDATE_DATE", "ORDER_NUMBER",
"SOURCED_FROM", "PO_REQUISITION_NUMBER", "ORDER_STATUS",
"ATTRIBUTE_CATEGORY", "ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3",
"ATTRIBUTE4", "ATTRIBUTE5", "ATTRIBUTE6", "ATTRIBUTE7", "ATTRIBUTE8",
"ATTRIBUTE9", "ATTRIBUTE10", "ATTRIBUTE11", "ATTRIBUTE12", "ATTRIBUTE13",
"ATTRIBUTE14", "ATTRIBUTE15", "LIKELIHOOD", "ITEM_SCRATCHPAD",
"SCHEDULE_ARRIVAL_DATE", "WAYBILL_NUMBER", "ACTUAL_SHIPMENT_DATE",
"SHIPPING_METHOD_MEANING", "SOURCED_FROM_DISP", "SOURCE_ID", "CREATED_BY",
"LAST_UPDATED_BY") AS SELECT acc.access_id , lines.requirement_line_id ,
lines.requirement_header_id , lines.inventory_item_id ,
csm_util_pkg.item_name(msi.concatenated_segments) AS item_name,
msi.description AS item_description, lines.uom_code ,
lines.required_quantity , lines.ship_complete_flag , lines.revision ,
lines.source_organization_id , lines.source_subinventory ,
lines.ordered_quantity , lines.order_line_id , lines.reservation_id ,
lines.local_reservation_id , lines.order_by_date ,
nvl(oe_lines.actual_arrival_date,oe_lines.schedule_arrival_date)
arrival_date, oe_lines.shipping_method_code, lines.last_update_date,
oe_headers.order_number, lines.sourced_from, null po_requisition_number,
CSP_PICK_UTILS.get_order_status (oe_lines.LINE_ID,
oe_lines.FLOW_STATUS_CODE) order_status, lines.attribute_category,
lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4,
lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8,
lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12,
lines.attribute13, lines.attribute14, lines.attribute15 ,
lines.LIKELIHOOD, lines.ITEM_SCRATCHPAD,oe_lines.schedule_arrival_date,
csp_pick_utils.get_attribute_value('WAYBILL') waybill_number,
oe_lines.actual_shipment_date, flv.meaning shipping_method_meaning,
fl.meaning SOURCED_FROM_DISP, rld.source_id,
CSM_UTIL_PKG.GET_NUMBER(lines.created_by) AS CREATED_BY,
CSM_UTIL_PKG.GET_NUMBER(lines.last_updated_by) AS LAST_UPDATED_BY FROM
csm_req_lines_acc acc, csp_requirement_lines lines, oe_order_lines_all
oe_lines, oe_order_headers_all oe_headers, mtl_system_items_b_kfv msi,
csp_req_line_details rld , fnd_lookup_values flv, fnd_lookups fl WHERE
acc.user_id = asg_base.get_user_id AND acc.requirement_line_id =
lines.requirement_line_id AND (lines.sourced_from = 'INVENTORY' OR
lines.sourced_from is null) AND lines.requirement_line_id =
rld.requirement_line_id(+) AND rld.source_id = oe_lines.line_id(+) AND
oe_lines.header_id = oe_headers.header_id(+) AND msi.inventory_item_id =
lines.inventory_item_id AND msi.organization_id =
nvl(lines.source_organization_id,
csm_profile_pkg.get_organization_id(acc.user_id)) and flv.LOOKUP_TYPE(+) =
'SHIP_METHOD' and flv.LANGUAGE(+) = asg_base.get_language and

Resetting events to create views with compilation ...

AD Worker error:
The following ORACLE error:

ORA-01012: not logged on

occurred while executing the SQL statement:

alter session set events '10520 trace name context off'

Solution : execute the view creation manually

sqlplus -s APPS/***** @F:\R12\apps\apps_st\appl\igs\12.0.0\patch\115\sql\IGSAD4287260A.sql

Error:
Program exited with status 1

Cause: The program terminated, returning status code 1.

Action: Check your installation manual for the meaning of this code on this operating system.DECLARE
*
ERROR at line 1:
ORA-20100: Error due to ORA-06502: PL/SQL: numeric or value error: character to
number conversion error
ORA-06512: at line 146

After resolving the above issue did the database upgrade to 11g with database migration to 64bit.
============
custom 6i forms and reports files should be migrated to the new 10g forms reports format using the following script,

frmcmp userid=apps/password@prodsid module=XXPER_ENTITLEMENT.fmb batch=yes module_type=form

::compile_forms.bat
cls
Echo compiling Forms....
for %%f IN (*.fmb) do frmcmp userid=apps/password@testerp module=%%f batch=yes module_type=form compile_all=yes window_state=minimize
ECHO FINISHED COMPILING

REM WINDOWS COMPILE REPORT
::compile_report.bat
cls
Echo compiling Report .....
for %%f IN (*.rdf) do rwconverter userid=apps/password@testerp batch=yes source=%%f stype=rdffile DTYPE=rdffile compile_all=yes OVERWRITE=yes logfile=replog.txt
ECHO FINISHED COMPILING
=============
STEPS FOR DATABASE UPGRADE--ROUGH

oracle home installation for 11.1.0.7 -
Database binary installation -
started at 07:15 PM -07:32 PM

example installation -
started at 07:34 - 07:38 PM

Oracle Database 11.1 Post-Upgrade Status Tool 05-12-2010 05:32:04
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.7.0 01:04:22
JServer JAVA Virtual Machine
. VALID 11.1.0.7.0 00:15:13
Oracle Real Application Clusters
. INVALID 11.1.0.7.0 00:00:04
OLAP Analytic Workspace
. VALID 11.1.0.7.0 00:00:41
OLAP Catalog
. VALID 11.1.0.7.0 00:01:59
Oracle OLAP API
. VALID 11.1.0.7.0 00:00:44
Oracle XDK
. VALID 11.1.0.7.0 00:03:19
Oracle Text
. VALID 11.1.0.7.0 00:02:40
Oracle XML Database
. VALID 11.1.0.7.0 00:04:36
Oracle Database Java Packages
. VALID 11.1.0.7.0 00:00:46
Oracle Multimedia
. VALID 11.1.0.7.0 00:05:41
Spatial
. VALID 11.1.0.7.0 00:08:17
Gathering Statistics
. 00:21:59
Total Upgrade Time: 02:10:29

the job was started completed at COMP_TIMESTAMP UPGRD__BGN 2010-05-12 03:14:13 and completed at 05:32 AM

so the total time is - 2 1/2 hours

2 nd job script ran from 05:32 to 08:15

- 2 1/2 hours

so total 5 hours for this job .

====================
IMPORTANT FOR PROD ATG UPGRADE ---> ROUGH

6502082 / AR
5989593 / AR post step adgrants
4252319
3218526 / AR
3854951 / AR
3761838 / AR
4206794 / AR
./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps
5903765 /AR
4619025
4583125

Run Autoconfig

CREATE TABLESPACE APPS_TS_TOOLS DATAFILE
'datafile_name.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Copy the following font files from $FND_TOP/resource to the /lib/fonts directory (under OA_JRE_TOP and JRE_TOP) on all Web and concurrent nodes.
If $FND_TOP/resource does not contain the font files, apply patch 3639533: Albany (Display) Font 4.02 Release.
The font files are:
ALBANYWT.ttf - "single-byte" characters only
ALBANWTJ.ttf - Unicode font with Japanese Han Ideographs
ALBANWTK.ttf - Unicode font with Korean Han Ideographs
ALBANWTS.ttf - Unicode font with Simplified Chinese Han Ideographs
ALBANWTT.ttf - Unicode font with Traditional Chinese Han Ideographs

select bug_number,CREATION_DATE from ad_bugs where bug_number in ('6502082','5989593','4252319','3218526','3854951','3761838','4206794','5903765','4619025','4583125');

BUG_NUMBER CREATION_
------------------------------ ---------

4252319 10-MAY-10
4583125 11-MAY-10
5903765 10-MAY-10

select bug_number,CREATION_DATE from ad_bugs where bug_number in ('3639533');
===========********===========
Post upgrade tasks has many steps while one of them is as follows,

Human Resources
Important: Customers using Oracle iRecruitment and running database
11gR1 version 11.1.0.7 must add the following line to their database
initialization file:
event="31151 trace name context forever, level 0x100"
Setting this event provides a fix for the issue reported in bug 6952870.
===============================
If you face any issues in upgrade please don't hesitate to contact me at masamimalik@yahoo.com or call me on my cell 009743164959

Tasks and Phases to Upgrade Oracle Applications 11i to r12 (12.1.1)

2010-06-21T01:13:00.000-07:00

Last month I was busy in upgrading Oracle Applications 11i (11.5.10.2) to release 12 (12.1.1) with 64 bit 11g (11.1.0.7) database. The following were the main tasks and phases with which the upgrade project was done. For rough detailed steps for the upgrade please refer my post 'rough upgrade steps from 11i to r12 with db 11g'
TASK and Phases
Phase -1
Applying ATG RUP 6 patch and Converting to Multiorg
Baseline backup of the existing 9i database and Oracle 11i Application file system including windows registries
Disabling Batch jobs or any scheduled Database jobs .
Apply Pre-reqs patch 4252319
Apply ATG RUP 6 Patch 5903765
Apply ATG RUP 6 Transaltion Patch 5903765
Apply Post Upgrade patch 4583125
Perform the Post upgrade steps
Converting to mutliorg
Apply the TUMS utility Patch 7705743

In case of failure time for restoration will be 3 hr

Phase -2
Oracle Database upgrade to 11gR1
Baseline backup of the existing 9i database and Oracle 11i Application file system including windows registries
Applying Oracle Database upgrade and baseline Pre-reqs on the Windows Server 2003
Performing Pre-upgrade Tasks on the existing 9i Database .
Copy the timezone dat file from the patch 5845928 and run utlu111i.sql
Disabling Batch jobs or any scheduled Database jobs .
Starting the Oracle Database upgrade from 9i to 11gR1 (11.1.0.7) .
Performing Post-upgrade Tasks on the upgraded 11gR1 Database .
enabling autoconfig standardize configuration management tasks in an Oracle Applications environment
Final Oracle 11gR1 Database backup .

In case of failure time for restoration will be 3 hr

Phase -3
R12 Upgrade
Baseline backup of the existing 11gR1 database and Oracle 11i Application file system including windows registries
Install and integrate the R12 filsystem with 11gr1 database
Apply the pre-reqs patches
Run the Upgrade Driver for R12
Apply the NLS upgrade driver
Migrate the customization

Phase -4
Database migration to 64 bit
Baseline backup of the existing 11gR1 database
Installation of Microsoft windows 64 bit
Migrate the Database to 64 Bit
Enable Autoconfig on New RDBMS home on 64 bit
Recreate the Grants and synonyms
Compile flexfields

In case of failure time for restoration will be 3 hr

Forgot Linux root and/or Oracle sys password?

2010-04-06T03:38:00.000-07:00

RESET LINUX root PASSWORD

METHOD 1
1. Boot the computer with Linux Installation CD.
2. Enter into Linux Rescue mode by enering the following command in boot screen. boot: linux rescue
3. When the sh prompt is reached, enter the following commands to change root password.

#chroot /mnt/sysimage
#passwd

METHOD 2
1. When u reached at the grub screen press 'e' on linux which contains the word 'kernell' press 'e'
2. Go to end of that line and write 'linux single'
3. Then press 'esc' twice and press 'b' to reboot the system
4. Now wait for the prompt and type the following command to reset the password for root

#passwd root

The above steps will prompt you to enter your new password.

RESET ORACLE sys USER PWD

Your password file should be under \database\PWD.ora.
Delete it and run the Oracle password utility from the command prompt as;

#orapwd file=pwdSID.ora password=newpassword entries=enterAnumber

The is your new sys password. After you log in as sys you can change it and create new passwords for system etc.

Oracle Apps 11i 11.5.10.2 configuration on DMZ

2010-03-21T03:42:00.000-07:00

You can refer 380490.1 for Oracle E-Business Suite Release 12 Configuration in a DMZ, which is almost similar to the 11i done below,

1. Following patches required,

Required Patch: 3240000
Description: 11.5.10 Oracle EBS Consolidated Update 1Comments: This patch is not required if Upgraded your EBS instance to 11.5.10. Oracle EBS consolidated update 2 (3460000)

Required Patch: 4204335Description: WTI LITE PATCHComments: This patch is not required if Upgraded your EBS instance to 11.5.10. Oracle EBS consolidated update 2 (3460000)

Required Patch: 3942483Description: Autoconfig support for reserver proxy, URL firewall and DMZ configuration (Phase 1)Comments: This patch is not required if your EBS instance have been upgraded to 11.5.10 CU2 or later and also it has been upgraded to TXK.M (4709948) or later.

SQL>SELECT PATCH_LEVEL FROM FND_PRODUCT_INSTALLATIONS WHERE PATCH_LEVEL LIKE '%TXK%';

Required Patch: 5478710Description: TXK (FND & ADX) Autoconfig Rollup Patch O (Dec 2006)

Run the following script to check if already the patches are applied or not.

SQL>select * from ad_bugs where bug_number in ('3240000','3460000','4204335','4125550','3942483','4709948','5478710');

Note: Apply the above patches in the order shown.

2. Ask System administrator to update the external DNS entry and add the correct domain name on the DMZ server, Also update the \etc\hosts file. On DMZ server update \etc\hosts file and enter the ipaddress and the hostname for the database server, since you will be accessing it.
eg: 192.168.2.67 testdb

3. Install all the pre-requisit softwares Visual Studio 6, vc++, MKS toolkit, xmlsdk.

Eg: Install MKS toolkit to c:\prog\mks directory
create prog and mks directories before installing and select the correct path while installing.

Install VC++ in c:\VC98 directory, create VC98 directory before installing and select the correct path while installing

Copy gnumake.exe to c:\VC98\bin and C:\prog\mksnt directories

Install xmlsdk.msi in default path c:\Program files\Microsoft XML Parser SDK.

4. Now you MUST stop all application services (web apache, forms, reports, conc manager, 806 listener)
recompile apps schema using adadmin (optional)
run cmclean.sql script (optional)

5. On internal application server,
perl adpreclone.pl appsTier

Copy appltop,commontop,oratop and iastop from Internal to External DMZ server.

6. Now run config clone on DMZ External server,

SET path=e:\oracle\qatestora\iAS\Apache\perl\5.00503\bin\MSWin32-x86\;%PATH%
then rerun adcfgclone.pl

%common_top%\clone\bin>perl adcfgclone.pl appsTier

7. Now if Internal is giving error on page while logging and external is working so after the DEP for IE Explorer and installing Jinitiator and running autoconfig on database and internal appication tier servers will resolve the issue.
HERE I RAN AUTOCONFIG ON INTERNAL APPS TIER THEN STARTED ONLY FORMS,806 AND APACHE TO START APPLICATION AND UPDATE THE FOLLOWING PROFILES,

8. There are a number of user profiles that are used to construct various URLs in an E-Business Suite 11i environment. These user profiles are as follows:
Application developer responsibility---->profile--->FOLLOWING ALL TO BE SET TO SECURITY(HIERARCHY TYPE).

User Profile Name Internal Name
------------------------------------------------------------------------------
1. Applications Web Agent APPS_WEB_AGENT
2. Applications Servlet Agent APPS_SERVLET_AGENT
3. Applications JSP Agent APPS_JSP_AGENT
4. Applications Framework Agent APPS_FRAMEWORK_AGENT
5. ICX:Forms Launcher ICX_FORMS_LAUNCHER
6. ICX: Oracle Discoverer Launcher ICX_DISCOVERER_LAUNCHER
7. ICX: Oracle Discoverer Viewer Launcher ICX_DISCOVERER_VIEWER_LAUNCHER
8. Applications Help Web Agent HELP_WEB_AGENT
9. Applications Portal APPS_PORTAL
10. BOM:Configurator URL of UI Manager CZ_UIMGR_URL
11. ASO : Configurator URL ASO_CONFIGURATOR_URL
12. QP: Pricing Engine URL QP_PRICING_ENGINE_URL
13. TCF:HOST TCF:HOST

9. Stop all services.
cmclean and adadmin recompile apps schema.

Now run the following script,
The configuration of the EBS environment for DMZ requires these profile options hierarchy type to be set to SERVRESP.
To change the profile options hierarchy type values to SERVRESP, execute the following SQL script,

Better run from DB Tier after sourcing appsora,

SQLPLUS> CONN APPS/PWDSQL
SQL> SPOOL C:\spoolfile.TXT
SQL> @/patch/115/sql/txkChangeProfH.sql SERVRESP

Now check the log file spoolfile.TXT for any errors. If no error then proceed.

10. Make copy of NET80 tnsnames.ora, listener.ora and sqlnet.ora files before autoconfig.

After the above script in step 9 successfully completed run AUTOCONFIG on the application nodes including the database server (i.e. 3 servers).
[Firstly on TESTDB then on External DMZ server and lastly on Internal Applicaiton Server]

11. Start db server services (conc after invalid objects checking and cmclean)
>>start internal app server
>>check application before starting external
>>Now Update node trust level profile value for responsibilities that has to be exposed ouside.

12. Ask the netowork administrator to publish the DMZ URL to a public IP. Test the applicaiton access from internet.

Upgrade from Jinitiator 1.3 to Java Plugin 1.6.0.x

2010-02-06T09:51:00.000-08:00

Upgrade from Jinitiator 1.3 to Java Plugin 1.6.0.x

Lately Oracle announced the end of Error Correction Support for Jinitiator 1.3 for E-Business Suite 11i, effective July 2009 (ML Note: 552692.1).

This is the sign it’s about time to upgrade to the native Java Plug-in… :-)

Among other things, one of the main advantages of upgrading from Jinitiator to the native Java Plug-in is the prevention of conflicts between them.

This upgrade is great news to all are working with Oracle Discoverer Plus (with Java plug-in) and Oracle EBS 11i (with Jinitiator) and experiencing those conflicts.

I’ll skip all the others advantages and disadvantages of upgrading to Java Plug-in as they are well described in Steven Chan’s post - Jinitiator 1.1.8 To Be Desupported for Apps 11i on blogs.oracle.com and in Metalink Note: 290807.1 - Upgrading Sun JRE with Oracle Applications 11i.

So I will focus on the upgrade process itself - step by step.

I tested the upgrade on the following test environment:

EBS 11.5.10.2
Database 10.2.0.3
ATG Rollup 5
Developer 6i patchset 18
OS RHEL4.

Be aware that before upgrading to Java Plug-in you must upgrade to Developer 6i patchset 18 or later (currently the latest patchset is 19).

1. Download JRE plug-in Oracle E-Business Suite interoperability patch - 6863618

2. Download the Sun JRE Plug-in from http://java.sun.com/javase/downloads/index.jsp

>Select Java Runtime Environment (JRE) 6 Update X (select the latest available update, currently it’s 5)
>Select Windows offline installation, multi-language

3. Rename the downloaded installation file jre-6_uX-windows-i586-p.exe to j2se1600x.exe
In my case rename jre-6_u5-windows-i586-p.exe to ==>> j2se16005.exe

4. Copy the j2se1605.exe file to $COMMON_TOP/util/jinitiator on the Apps Tier node

5. If you are on Developer 6i patchset 18 you should apply forms patches 6195758 & 5884875.
** Skip this step if you are on Developer 6i patchset 19.

>Download Patches 6195758 & 5884875

>>Apply patch 6195758
-Stop all applications processes by adstpall.sh
-Unzip p6195758_60827_GENERIC.zip
-cd 6195758
-cp -r $ORACLE_HOME/forms60/java/oracle/forms/
handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/
handler/UICommon.class.PRE_BUG6195758
-cp -r $ORACLE_HOME/forms60/java/ oracle/forms
/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/
handler/ComponentItem.class.PRE_BUG6195758
-cp oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/
handler/UICommon.class
-cp oracle/forms/handler/ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/
handler/ComponentItem.class

>>Apply Patch 5884875
-Unzip p5884875_60827_GENERIC.zip
-cd 5884875
-cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
$ORACLE_HOME/forms60/java/oracle/forms/
engine/Main.class.PRE_BUG5884875
-cp -r $ORACLE_HOME/forms60/java/ oracle/forms/
handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/
handler/AlertDialog.class.PRE_BUG5884875
-cp oracle/forms/engine/Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
-cp oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/
engine/AlertDialog.class
-Run adadmin -> Generate Applications Files menu -> Generate product JAR files

6. Apply the Interoperability patch 6863618
>Make sure all applications processes are down
>Enable maintenance mode:
>Execute adadmin -> Change Maintenance Mode (5) -> Enable Maintenance Mode (1)
>Unzip p6863618_11i_GENERIC.zip
>Cd 6863618
>Run adpatch to apply patch driver u6863618.drv
>cd [PATCH_NUMBER]/fnd/bin
>Execute the following command where X represent the update number:
$ txkSetPlugin.sh 1600X
In my case:
$ txkSetPlugin.sh 16005
>Disable maintenance mode:
Execute adadmin -> Change Maintenance Mode (5) -> Disable Maintenance Mode (2)

7. Start all applications processes by adstrtall.sh

8. Verify installation by sign in Oracle EBS and select a forms based responsibility.

For those who worry about performance, take a look at this benchmark:
Benchmark comparison test with Jinitiator 1.3.1.23 and Java Plug-in 1.5.0_05 – performance whitepaper (http://www.oracle.com/apps_benchmark/doc/jinitiator-1-3-1-23-java-plug-in-1-5-0-05-benchmark.pdf

For more information - Note: 290807.1 - Upgrading Sun JRE with Oracle Applications 11i

Useful information - Upgrade applications 11i to r12

2010-01-06T09:28:00.000-08:00

Useful information Upgrade applications 11i to r12.

Note: To understand this page, you should have prior knowledge or background of APPS 11i

Main points about Installer:
1) config.txt is now configSID.txt , for adding node, you can use configSID.txt or get details from database directly using host.domain:port:sid format
2) Install types: Standard and Express
3) Shared APPL_TOP, COMN_TOP and tech stack as well, but not for Windows
4) Easy load balancing of CP and Web communications
5) Technology Stack Components : Oracle 10g R2 Database home, Oracle Developer 10i (forms, reports) and Oracle 10g Application Server 10.1.2 (http server)
6) Java Development Kit (JDK) 5.0 is automatically installed by Rapid Install
7) Disk Space : Applications node 28 GB , Fresh DB 45 GB, Vision DB 133 GB, Stage for fresh install 33 GB, TEMP 500 Mb
8) Create Stage : CD's are in DVD Format, and run adautostg.pl to create dir structure, which requires perl 5.0053 in PATH, and creates subdirectories startCD, oraApps, oraDB, oraAS, and oraAppDB under stage12
9) Want to install on virtual hostname, use -servername as command line parameter with rapidwiz. There are 2 more command line parameters, -restart to restart any failed install, and -techstack to install only technology stack.
10) Incase of multiuser installation, start installer using root account
11) For additional language, you must use OAM (oracle applications manager)
12) There is new concept of INST_TOP which mainly stores instance specific files including runtime files, log files and configuration files
13) In R12 there are Services concept instead of nodes (forms/web/concurrent). Following is the list of services in R12 :
* Root Service Group which supports • Oracle Process Manager (OPMN)
* Web Entry Point Services which supports • HTTP Server
* Web Application Services which supports • OACORE OC4J • Forms OC4J • OAFM OC4J
Batch Processing Services which supports • Applications TNS Listener • Concurrent Managers • Fulfillment Server
Other Service Group which supports • Oracle Forms Services • Oracle MWA Service
* : Thses services must be installed on same / one machine (which is nothing but Web node, according to 11i )
14) Regardless of type of services confugured on perticular server, all files (forms, reports, jsp) are stored in APPL_TOP (Unified), basically to have pure 3 tier arch
15) Installer gives option to configure OCM (Oracle configuration manager) where in OCM keeps track of key Oracle and OS stats. This collected data is sent to oracle support via https for better understanding of issues and quick resulations to any issues reported

Main points about Upgrade:
1) You can only upgrade to R12 from 11i, if you are at older version (like 10.7 or 11.0.3 etc), you must upgrade first to 11i, and then upgrade to R12
2) High level R12 Upgrade process :
• Run rapid installer first time to layout new file structure and tech stack
• Migrate or Upgrade database to 10g R2
• Run Autopatch to run database driver to bring DB to R12 level
• Run rapid installer second time to configure and start services

Admin Scripts:

adautocfg.sh - run autoconfig
adstpall.sh - stop all services
adstrtal.sh - start all services
adapcctl.sh - start/stop/status Apache only
adformsctl.sh - start/stop/status OC4J Forms
adformsrvctl.sh - start/stop/status Forms server in socket mode
adoacorectl.sh - start/stop/status OC4J oacore
adoafmctl.sh - start/stpp/status OC4J oafm
adopmnctl.sh - start/stop/status opmn
adalnctl.sh - start/stop RPC listeners (FNDFS/FNDSM)
adcmctl.sh - start/stop Concurrent Manager
gsmstart.sh - start/stop FNDSM
jtffmctl.sh - start/stop Fulfillment Server
adpreclone.pl - Cloning preparation script
adexecsql.pl - Execute sql scripts that update the profiles in an AutoConfig run
java.sh - Call java executable with additional args, (used by opmn, Conc. Mgr)

Install Symantec Tape Backup Exec Agent on Linux

2009-12-23T22:59:00.000-08:00

The following are the steps of installing Symantec Tape Backup Exec Agent on Redhat Linux operating system after which the Backup Exec application can start taking backup on Tape drives from the Redhat Linux OS file system.

1. Copy the agent gnuzipped file to the server in /tmp/symantec folder, then extract the contents,

# gunzip BEWS_12.5.2213_LINUX-UNIX-MAC-SAP_AGENTS.tar.gz
# tar -xvf BEWS_12.5.2213_LINUX-UNIX-MAC-SAP_AGENTS.tar

[root@srdodb symantec]# ls
BEWS_12.5.2213_LINUX-UNIX-MAC-SAP_AGENTS.tar
installrams.pl
RALUS64
tools uninstallrmalDOCS installrmal ralusinst.conf uninstallralus uninstallrmal.plinstallralus installrmal.pl RALUSx86 uninstallralus.cmd VxIFinstallralus.cmd messages ramsinst.conf uninstallralus.pl winntinstallralus.pl perl rmalinst.conf uninstallramsinstallrams pkgs scripts uninstallrams.pl

2. Now update the hosts file and add the tape backup server entry in it,

[root@srdodb symantec]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost::1 localhost6.localdomain6
192.168.1.82 srdodb
192.168.2.60 tapebkupsrv

3. Shutdown the database and/or other applicaitons that are not requrired at this time, and start installing the agent now

[root@srdodb symantec]# ./installralus

Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers 12.5.2213

Enter the system names separated by spaces on which to install RALUS: (srdodb.domain.local)
Checking system communication:
Checking OS version on srdodb.domain.local ....................................................... Linux 2.6.18-128.el5
Checking system support for srdodb.domain.local ............................... Linux 2.6.18-128.el5 supported by RALUS

Initial system check completed successfully.

Press [Return] to continue:
Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers 12.5.2213
installralus will install the following RALUS packages on Linux target system: srdodb.domain.local

VRTSralus Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers
Press [Return] to continue:
Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers 12.5.2213
Checking system installation requirements:
Checking RALUS installation requirements on Linux target systems: srdodb.domain.local

Checking RALUS installation requirements on srdodb.domain.local:
Checking file system space ............................................................. required space is available
Installation requirements checks completed successfully.
Press [Return] to continue: Checking for prerequistes .................... Done
Checking for Port 10000 ...................................................................... Done

Remote Agent Media Server Configuration

To display the Remote Agent as a selection in a media server's backup selection tree, and to be able to specify a local
network for use between the Remote Agent and a media server, enter the names or IP addresses of the media servers that you
want the Remote Agent to communicate with.

An IP Address: XXX.XXX.XXX.XXX A Host Name: COMPUTERNAME
Enter a directory host:192.168.2.60
Do you want to add another name or address for this agent? [y, n] (n)n

Remote Agent User Group Setup

To perform backups, you must have a 'beoper' user group. This user group can be created only if you are not using NIS server.
Your system will be scanned to detect a NIS server, group and membership.

Press [Return] to continue:
Checking if system uses NIS server: ............................................................................ No
The installer will now check your system for the 'beoper' user group and root membership.
Press [Return] to continue: Checking for 'beoper' user group: ........................................................................ Not Found

Checking for 'root' user membership in 'beoper' user group: .............................................. Not Found
You can create 'beoper' user group manually or you can choose to have it created automatically.

Do you want installer to create 'beoper' user group? [y, n] (y)

Do you want to use specific group ID when creating 'beoper' user group? [y, n] (n)

Creating group 'beoper': ...................................................................................... Done

Do you want to add the 'root' user to 'beoper' user group? [y, n] (y)
Adding 'root' user to 'beoper' user group: ................................................................... Done

Press [Return] to continue:
Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers 12.5.2213

Checking Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers on srdodb.domain.local:
Checking VRTSralus package ........................................................................... not installed

Press [Return] to continue:

Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers 12.5.2213
Installing Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers on srdodb.domain.local:

Installing VRTSralus 12.5.2213 on egdodb.qia.local ............................................... done 1 of 1 steps
Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers installation completed successfully.
Press [Return] to continue: Copying new initialization scripts .................................. Done
Creating RALUS linkedname in rc2.d ............................................................................ Done
Creating RALUS linkedname in rc3.d ............................................................................ Done
Creating RALUS linkedname in rc5.d ............................................................................ Done
Updating RALUS files for beoper .................................................................................... Done

Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers 12.5.2213

Configuring Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers:

Creating configuration files ........................................................................................ Done

Symantec Backup Exec for Windows Servers Remote Agent for Linux/Unix Servers configured successfully.

Press [Return] to continue:
The response file is saved at:
/var/tmp/vxif/installralus1223014409/installralus1223014409.response
The installralus log is saved at:
/var/tmp/vxif/installralus1223014409/installralus.log

4. To start, stop or restart the agent use the following command,

[root@egdodb init.d]# cd /etc/init.d

[root@egdodb init.d]# ls VR*

VRTSralus.init

[root@egdodb init.d]# ./VRTSralus.init status

Symantec Backup Exec Remote Agent for Linux/Unix Servers
Usage: VRTSralus.init { start stop restart }

[root@egdodb init.d]# ./VRTSralus.init stop
Stopping Symantec Backup Exec Remote Agent ...............Stopping
Symantec Backup Exec Remote Agent: [ OK ]

[root@egdodb init.d]# ./VRTSralus.init start
Starting Symantec Backup Exec Remote Agent ......Starting
Symantec Backup Exec Remote Agent: [ OK ]

5. Now finally from the Tape backup server i.e, from Symantec Backup Exec application test a new backup job by picking some files/folders to include in the tape backup. Also test the restore on linux and windows server.

Good...Great...I found the similar setup on Symantec site...follow the link,

http://seer.entsupport.symantec.com/docs/282010.htm

For any queries please don't hesitate to contact me.
email: masamimalik@yahoo.com
Tel: +9743164959.

How to know if a patch requires downtime?

2009-11-16T02:22:00.001-08:00

1. Download the particular patch file
2. Check the file ..\\etc\config\inventory
3. If the following entry is present
(instance_shutdown) true (/instance_shutdown)

then it requires downtime.

Note: OPatch means rdbms patch.
For rdbms patch go to OPatch dorectory and select /etc/config/inventory

Copy files using ssh

2009-11-01T01:33:00.000-07:00

you can't copy using the 'ssh' program specifically, but you can with it's associated programs: sftp or scp

sftp is (and works) similar to ftp

scp is a neat little program:

copy from a remote machine to my machine:
scp asami@192.168.1.100:/home/remote_user/Desktop/file.txt /home/me/Desktop/file.txt

copy from my machine to a remote machine:
scp /home/me/Desktop/file.txt asami@192.168.1.100:/home/remote_user/Desktop/file.txt

copy all file*.txt from a remote machine to my machine (file01.txt, file02.txt, etc.; note the quotation marks:
scp "asami@192.168.1.100:/home/remote_user/Desktop/file*.txt" /home/me/Desktop/file.txt

copy a directory from a remote machien to my machine:
scp -r asami@192.168.1.100:/home/remote_user/Desktop/files /home/me/Desktop/.

see 'man scp' or 'man sftp' for more ...

emctl upload - disk full error

2009-10-21T00:52:00.001-07:00

Today when I tried to run the command 'emctl upload' I got the following error,

-bash-$ emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload was successful but collections currently disabled - disk full

#From Metalink note 317242.1

#Cause
The EMD disk system shows used percent at 99%.
The agent default for space required for upload files is 98%.
The collections will stop when the space on the disk is beyond that.

#Solution
To implement the solution, please execute the following steps:
1. Update the AGENT OH/sysman/config/emd.properties with the parameter:uploadMaxDiscUsedPct=99uploadMaxDiskUsedPctFloor=99
2. Stop the agent: emctl stop agent
3. Start the agent: emctl start agent
4. Upload to the OMS: emctl upload

This resolved the issue.

Samba server configuration

2009-10-13T03:49:00.000-07:00

I am writing this as I was doing Samba share configuration between linux and windows machines.

Samba Overview:
---------------
1. Uses Session Message Block (SMB) protocol.
2. The most typical reason to use Samba is to allow windows machine and linux machine to interact.
3. Simple to Complex configurations.
4. Easy to manage as there is one main Samba Configuration file /etc/samba/smb.conf

We are going to setup a Linux box so that certain directories and files are shared and we can control how they are shared weather it should be read only or read write. Then from the windows machine we can access that linux share and vice versa. You can also interact between two linux machines using Samba protocol.

Smb.conf layout:
----------------
There are 4 special sections in Samba configuration files as given below,

[global] Global Configuration Settings
[homes] Shares users home directories -> Any setting in home section will override the global settings.
[printers] Define shared printer access
[userdefined] This will be a user defined section

Example of a user defined section is as given below,

[myshare]
path = /u01/app/oracle/oradata/orcl <--directory to share
writable = true <--people can write to this directory
valid users = samiora <-- only samiora can access this directory
browsable = yes <-- other users can read this directory but cannot write to it
create mode = 0700 <-- this is chmod for this directory for the owner ie. here it is rwx for the owner.

There are zillion (exaggerating) more parameters used for different settings like require passwords, limit by host, limit by time of day etc.

Login in as root and query if the following rpm's are installed or not if the following packages are not available then firstly install them using rpm command,

#rpm -ivh

[root@egdodb samba]# rpm -qa | grep xinetd
xinetd-2.3.14-10.el5

[root@egdodb samba]# rpm -qa | grep samba
samba-common-3.0.33-3.7.el5
samba-3.0.33-3.7.el5
samba-common-3.0.33-3.7.el5
samba-client-3.0.33-3.7.el5
system-config-samba-1.2.41-3.el5

Configuring Samba:
------------------
You can configure samba by various ways, like manually updating the smb.conf file, using yast utility on suse linux, using SWAT Samba Web Admin Tool and using 'webmin' web based tool. The following is the manual way of updating the smb.conf file,

Update the smb.conf file manually and then run the 'testparm' command to validate the smb.conf file entries,

[root@egdodb samba]# testparm smb.conf

Load smb config files from smb.conf
Processing section "[homes]"
Processing section "[printers]"
Loaded services file OK.
Server role: ROLE_STANDALONE
Press enter to see a dump of your service definitions

[global]
workgroup = MYGROUP
server string = Samba Server Version %v
passdb backend = tdbsam
cups options = raw

[homes]
comment = Home Directories
read only = No
browseable = No

[printers]
comment = All Printers
path = /var/spool/samba
printable = Yes
browseable = No

[myshare]
workgroup =

To check the status of samba server service running or not issue the following command,

[root@egdodb samba]# service smb status
smbd is stopped
nmbd is stopped

Useful SQL Scripts for Oracle Applications

2009-08-17T01:44:00.000-07:00

Users Connected through application
Purpose	To get the list of users connected.
Description	Query gives the list of users connected to the application
Parameters	None
Query	SELECT user_name username, description name, to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect, to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect FROM apps.fnd_user a, (SELECT MIN (first_connect) first_connect, MAX (last_connect) last_connect, last_updated_by user_id FROM apps.icx_sessions GROUP BY last_updated_by) b WHERE a.user_id = b.user_id AND last_connect>SYSDATE-3/12 ORDER BY 4 DESC /

Responsibilities Listing
Purpose	To get list of responsibilities.
Description	Query useful when user wants to get application wise responsibility list
Parameters	None
Query	SELECT (SELECT application_short_name FROM fnd_application fa WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name FROM apps.fnd_responsibility_tl frt;

Menus Listing
Purpose	To get Menus Associated with responsibility
Description	User to check menu attached with a reponsilblity
Parameters	responsibility_id Which user can get from query of section Responsibilities Listing
Query	SELECT DISTINCT a.responsibility_name, c.user_menu_name FROM apps.fnd_responsibility_tl a, apps.fnd_responsibility b, apps.fnd_menus_tl c, apps.fnd_menus d, apps.fnd_application_tl e, apps.fnd_application f WHERE a.responsibility_id(+) = b.responsibility_id AND a.responsibility_id = ‘20538’ AND b.menu_id = c.menu_id AND b.menu_id = d.menu_id AND e.application_id = f.application_id AND f.application_id = b.application_id AND a.LANGUAGE = 'US';

Submenu And Function Listing
Purpose	To get submenus and Function attached to this Main menu.
Description	By using this query user can check function and submenus attached to that specific menu
Parameters	User_menu_name Which user can get from query of section Menu Listing
Query	SELECT c.prompt, c.description FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';

User And Assigned Responsibility Listing
Purpose	To get assigned responsibility to a user.
Description	User wants to check responsibility attached to a specific user
Parameters	None
Query	SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60);

Responsibility And Assigned Request Group Listing
Purpose	To get responsibility and attached request groups.
Description	Every responsibility contains a request group(request group is basis of submitting requests)
Parameters	None
Query	SELECT responsibility_name responsibility, request_group_name, frg.description FROM fnd_request_groups frg, fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id ORDER BY responsibility_name

Profile Option With Modification Date and User
Purpose	To get modified profile options.
Description	Query used for audit point of view i.e. when a profile is changed and by whom user
Parameters	None
Query	SELECT t.user_profile_option_name, profile_option_value, v.creation_date, v.last_update_date, v.creation_date - v.last_update_date "Change Date", (SELECT UNIQUE user_name FROM fnd_user WHERE user_id = v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o, fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id = v.profile_option_id AND o.application_id = v.application_id AND start_date_active <= SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B' UNION SELECT nls_language FROM fnd_languages WHERE installed_flag = 'B') ORDER BY user_profile_option_name;

Forms Personalization Listing
Purpose	To get modified profile options.
Description	Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
Parameters	None
Query	SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE, ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event, ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query, (SELECT user_name FROM fnd_user fu WHERE fu.user_id = ffcr.created_by) "Created By " FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft WHERE ffcr.ID = ffft.function_id ORDER BY 1;

Patch Level Listing
Purpose	To get Patch Level.
Description	Query used to view the patch level status of all modules
Parameters	None
Query	SELECT a.application_name, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id;

Function Listing
Purpose	To get all Functions
Description	Complete forms and functions
Parameters	None
Query	SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl order by order by user_function_name;

Request Attached To Responsibility Listing
Purpose	To get all Request attached to a responsibility
Description	View all request who have attached to a reponsiblity
Parameters	None
Query	SELECT responsibility_name , frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = 'P' AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name;

Request Listing Application Wise
Purpose	To get all request with application
Description	View all types of request Application wise
Parameters	None
Query	SELECT fa.application_short_name, fcpv.user_concurrent_program_name, description, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQLPlus', 'H', 'Host', 'L', 'SQLLoader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, output_file_type, program_type, printer_name, minimum_width, minimum_length, concurrent_program_name, concurrent_program_id FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id ORDER BY description

Count Module Wise Reports
Purpose	To Count Module Wise Report
Description	Application wise request counting
Parameters	None
Query	SELECT fa.application_short_name, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQLPlus', 'H', 'Host', 'L', 'SQLLoader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY fa.application_short_name, fcpv.execution_method_code ORDER BY 1;

Request Status Listing
Purpose	To calculate request time
Description	This query will shows report processing time
Parameters	None
Query	SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , f.actual_start_date actual_start_date , f.actual_completion_date actual_completion_date, floor(((f.actual_completion_date-f.actual_start_date)246060)/3600) \|\| ' HOURS ' \|\| floor((((f.actual_completion_date-f.actual_start_date)246060) - floor(((f.actual_completion_date-f.actual_start_date)246060)/3600)3600)/60) \|\| ' MINUTES ' \|\| round((((f.actual_completion_date-f.actual_start_date)246060) - floor(((f.actual_completion_date-f.actual_start_date)246060)/3600)3600 - (floor((((f.actual_completion_date-f.actual_start_date)246060) - floor(((f.actual_completion_date-f.actual_start_date)246060)/3600)3600)/60)*60) )) \|\| ' SECS ' time_difference , DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name\|\|'['\|\|f.description\|\|']',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 ORDER by f.actual_completion_date-f.actual_start_date desc;

User And Responsibility Listing
Purpose	Check responsibility assigned to a specific USER
Description
Parameters	None
Query	SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and a.application_name = 'Purchasing' ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60)

Applied Patch Listing
Purpose	Check Current Applied Patch
Description	This shows the applied patch list.
Parameters	None
Query	SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC

How to Change/Reset ias_admin Password

2009-07-12T01:30:00.000-07:00

— ias_admin is user required to use Enterprise Manager Web (Application Server Console - iASConsole) Site.

— ias_admin password is set during Installation of Oracle Application Server (902, 904, 10.1.2.X) or Oracle Identity Management (Infra Tier 10.1.4.X)—ias_admin account is NOT stored in OID (Oracle Internet Directory), It is stored in XML file (JAZN-XML - Java AuthoriZatioN)

How to Reset / Change ias_admin Password

You can reset/change ias_admin password in following ways

1. Using Enterprise Manager (Application Server Control) Web Site
–Login to Instance Home Page
–Click on Preferences on top right
–In new screen, click on “Change Password” on left menu
–Enter current password and New Password

2. Using Command line tool

emctl set password oldpassword new password

like

emctl set password welcome1 welcome2 (Here welcome1 is current ias_admin password and welcome2 is new password which you wish to reset)

If you don’t know current ias_admin password then change it in configuration file

3. Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for xml entry like below

< user >
< name > ias_admin < / >
< credentials >{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu < / credentials >
< /user >

and Update new password (welcome2 like )

< user >< name > ias_admin < / >
< credentials > !welcome2 / credentials >
< /user >

Note ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

Renaming/Dropping and recreating online redo logs and Standby logfiles

2009-07-08T23:02:00.000-07:00

If you are in process of testing Dataguard with Physical standby and After created Primary and Standby DB's you realize that u haven't put file names as per your standard. So I would recommend you to do the following,
rename both, online redo logs and standby logs.
Follow these steps to do it,

1. Following is the script to identify all log files we have
SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2. Make sure protection mode is MAXPERFORMANCE:
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE

First we start working on Standby database
3. STOP redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. Set STANDBY_FILE_MANAGEMENT to MANUAL.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
5. Gather current status of all member.
SQL> select member from v$logfile;
6. Prepare command to drop standby redo logfiles.
select 'alter database drop standby logfile group ' group# ';' from v$standby_log;
7. Prepare command to drop online redo logfiles.
select 'alter database drop logfile group ' group# ';' from v$log;
8. Check the status of online redolog group.
SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
We can not drop CLEARING_CURRENT status logs, for that you have sync with Primary (Easy way is, leave them for a while and move ahead with droping Primary Database redo logs, and come back to Standby and you will be able to do it).
9. Clear the log group first before drop.
ALTER DATABASE CLEAR LOGFILE GROUP 1;
10. Now drop online redo log.
alter database drop logfile group 1;
Follow the same process for rest of the groups.
11. Now drop standby redo logs, leave the one which refuses to drop.
alter database drop standby logfile group 5;
12. Now create new online redo logs.
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
13. Now we will crate standby redo logs.
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
14. Again check the current status of member.
select member from v$logfile;

Now you will see latest updated names for all files. The one which left we can do them after We are done with Primary DB.

Now on Primary Database:
15. Follow step 5,6,7 to know current status and prepare commands.
16. Drop Standby redo logs and recreate them
alter database drop standby logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
17. Drop online Redo logs, recrate, switch and drop next inactive one.
alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
ALTER SYSTEM SWITCH LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;
18. Check the new status.
select member from v$logfile;

Now come back to Standby Database back and drop recreate left over online and standby redo logs.
19. Set STANDBY_FILE_MANAGEMENT to AUTO.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
20. start redo apply.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
21. Check the status of applied log again.
select sequence#, applied from v$archived_log;

SS Cannot open user default database

2009-07-05T23:33:00.001-07:00

Sql Server 2005: Cannot open user default database: Login Failed

Last month I attended training for Microsoft SQL Server 2008 Administration. It was a nice experience and something new for me to learn. During the training I was thanking god that I took a right direction in taking oracle as my career path than microsoft technology. :-)

Immediately after joining back in office I was given a task to move the entire sql server database from c-drive to f-drive for a reporting/auditing application called SCOM. It was quite easy to DETACH and ATTACH the database from c-drive to f-drive. But I faced a problem when I detached a database that was a default database. It was throwing following error when I tried to start the management studio,

"Cannot open user default database, login fails"

After some links on the web, I could resolve the issue,

I used the sqlcmd utility to change the default database in SQL Server 2005 to master database.
To do this, I follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

• If the SQL Server login uses Microsoft Windows authentication to connect to the instance which my instance was using, i typed the following at the command prompt, and then pressed ENTER:

sqlcmd –E -d master
ALTER LOGIN [QIA\OpsMgrAdmin] WITH DEFAULT_DATABASE=master
GO

• If the SQL Server login used SQL Server authentication to connect to the instance, then I would have typed the following at the command prompt, and then pressed ENTER:

sqlcmd -S InstanceName -d master -U SQLLogin -P Password
ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
GO

sqlcmd -S InstanceName -d master -U sa -P Password
ALTER LOGIN sa WITH DEFAULT_DATABASE = master
GO

Anyway it was nice experience and the task was completed after following the above commands to troubleshoot the issue. Thanks to the training to understand Microsoft Sql Server 2008 Database architecture.

How many Archive files got generated today?

2009-05-20T00:48:00.000-07:00

To find how many archive log files that got generated everyday on hourly basis, Here is the script:

/* archlogs.sql
find the number of archivelogs each hour of the day
*/
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1

The output would look like this,
LOGDATE MidN 1AM 2AM 3AM 4AM 5AM 6AM 7AM 8AM 9AM 10AM 11AM Noon 1PM 2PM 3PM 4PM 5PM 6PM 7PM 8PM 9PM 10PM 11PM
05/07/09 0 0 0 0 0 0 0 0 0 0 1 4 1 1 2 2 40 2 1 2 1 2 1 2
05/08/09 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1 1 1 2 1 1 1
05/09/09 2 1 1 1 2 1 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1
05/10/09 1 1 2 1 1 1 2 1 1 2 2 1 2 1 1 1 2 1 1 1 2 1 1 1
05/11/09 1 2 1 1 1 2 1 1 1 2 1 1 2 1 1 2 1 1 1 2 1 1 1 2
05/12/09 1 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1
05/13/09 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1
05/14/09 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2
05/15/09 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1 1 1
05/16/09 2 1 1 1 2 1 1 1 2 1 1 1 1 2 1 1 1 2 1 1 1 2 1 1
05/17/09 1 2 1 1 1 2 1 1 1 2 2 1 3 2 1 1 2 1 1 1 2 1 1 1
05/18/09 1 2 1 1 1 2 1 1 1 2 1 2 2 1 2 1 1 1 2 1 1 1 2 1
05/19/09 1 1 1 2 1 1 1 2 1 1 1 2 1 2 1 2 1 1 1 2 1 1 1 1
05/20/09 2 1 1 1 2 1 1 1 2 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Installing Apps 11i on a Laptop for practicing

2009-05-10T02:00:00.000-07:00

Hardware used.

--------------------------------------------------------------------------------
Windows XP professional SP2 laptop with 1 GB RAM
External hard drive with 250 GB space
-- DO NOT USE FAT32 FILE SYSTEM. USE ONLY NTFS.
-- installation files downloaded from edelivery.oracle.com is 25 GB.
-- staging area needs 19.7 GB.
-- base installation with Vision database needs about 110 GB.

Pre-requisite software requirements.
--------------------------------------------------------------------------------
1. Microsoft VC++ - http://msdn.microsoft.com/vstudio/express/visualc/download/
2. CYGWIN - http://cygwin.com/

NOTE - There is no need for perl or to run the staging script adautostg.pl. You can just use winzip to unzip the files, there is no difference.

Software installation files downloaded from edelivery.oracle.com.
--------------------------------------------------------------------------------
NOTE - There is no need to download all the files from edelivery.oracle.com. Just download the following files, rest are optional. Also, the forms, reports, discoverer, etc. tools are included in the following files (although there are separate files in the media pack set on edelivery.oracle.com).

1. Oracle® Applications 11i Release 11.5.10.2 Rapid Install Start Here - Disk 1
2. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install RDBMS - Disk 1
3. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Databases - Disk 1
4. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Databases - Disk 2
5. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Databases - Disk 3
6. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install Tools - Disk 1
7. Oracle® Applications 11i Release 11.5.10.2 for Microsoft Windows Rapid Install APPL_TOP - Disk 1

Installation steps.
--------------------------------------------------------------------------------
1. Add the following to your c:\windows\system32\drivers\etc\hosts file. You can change domainname.com to any name that you prefer. This should be used as the domain while installing Oracle Applications.

127.0.0.1 localhost
127.0.0.1 localhost.domainname.com

2. Go to Control Panel -> Systems -> Computer Name Tab -> Click on Change -> Click on More -> Enter Primary DNS Suffix as 'domainname.com'.

3. Go to your network connection, right click and select properties. Select Internet Connection TCP/IP (last in the list) and click properties. Click on Advanced button and go to DNS tab. Enter DNS suffix for this connection as
'domainname.com'.

4. Restart the PC.

5. Install Microsoft VC++
-- Remember to change the installation folder to C:\MSVC or something similar so that it does not have a space in the folder name. By default it takes Program Files, which will/may not work.

6. Restart the PC.

7. Download CYGWIN setup file and run it.
-- Install it in C:\cgwin folder, with no spaces in the folder name.
-- There are some important steps while installing CYGWIN detailed in metalink note 302859.1. If you do not have access to metalink, refer this link.
http://forums.oracle.com/forums/thread.jspa?threadID=435071

8. Restart the PC.

9. Unzip the files downloaded from edelivery.oracle.com to the folder E:\Stage11i, where E is the drive where you have sufficient space (about 120 GB for Vision sample DB and about 60GB -- not sure -- for without data) and Stage11i is a folder that you have to create.

10. Check that Stage11i has the following sub directories.
oraAppDB, oraApps, oraDB, oraiAS, startCD. The total space occupied by Stage11i folder is about 19.7 GB. This is when you do not unzip any optional files (see details above in the s/w installation files section).

11. Start rapidwiz by double clicking the file E:\Stage11i\startCD\Disk1\rapidwiz\RapidWiz.cmd.

12. Follow the wizard, remember to put the path for MKS Toolkit as C:\cygwin\bin and not just C:\cygwin. Select the path for VC++ as C:\MSVC (or where ever you installed it - remember NO SPACES in directory name). Select domain name as what you gave in your hosts file (domainname.com).

13. Refer the Oracle Apps installation guide for details on the wizard.

14. The installation (rapidwiz) takes about three hours.

15. Good luck.

Upgrading discoverer 4i to 10g for 11i

2009-04-28T21:37:00.000-07:00

Upgrading oracle discoverer 4i to Oracle Application server middle-tier 10g discoverer Component for oracle applications 11i

As you all know oracle has de-supported oracle discoverer 4i on applications 11i, therefore all DBA's will have at some point to upgrade their discoverer from 4i to 10g.

Refer to note 313418.1 in order to do that upgrade, my post will just give you a direct hint on what to do.

Steps are as follow based on note 313418.1:
-Install Oracle Business Intelligence Server 10g Release 2 (10.1.2.0.2). This installation type does not require installation and association to Oracle Application Server 10g Release 2 (10.1.2.0.2) Infrastructure. (You can choose this option if you are not planning to use features Discoverer Connection Management, Discoverer Portlet Provider, and Oracle SSO).

- Create secure directory under $Disco_ORACLE_HOME/discoverer
cd /u07/discoverer_home/discoverer
(mkdir secure)

-cp the $FND_SECURE/.dbc , $FND_SECURE/_.dbc TO your $Disco_ORACLE_HOME/discoverer/secure directory.

-Update tnsnames.ora file in $Disco_ORACLE_HOME to include connect to your Oracle E-Business database Use the same entry as exists in the tnsnames.ora file on your Oracle E-Business application tier server node.

-Migrate your existing Discoverer End User Layers to Discoverer 10.1.2.
$Disco_ORACLE_HOME/bin/eulapi -CONNECT /@ -AUTO_UPGRADE

-Set Applications Profile Options for Discoverer using AutoConfig
-open your xml file and specify variables as listed in the table below:
s_disco_standalone=true
s_disco_machine=for example: srpora03.us.oracle.com
s_disco_port=for example: 7778
s_disco_protocol=http https
s_disco_ver_comma=10,1,2
s_disco_eul_prefix=EUL

After that you have to run autoconfig

-Verify Applications profile options in Oracle Applications
Log into Release 11i with the System Administrator responsibility
Navigate to the Profile > System form
Query the %Discoverer% profile options.
Verify the following ICX profile options in Oracle Applications

ICX_DISCOVERER_LAUNCHER=http://:/discoverer/plus?Connect=[APPS_SECURE]
ICX_DISCOVERER_VIEWER_LAUNCHER=http://:/discoverer/viewer?Connect=[APPS_SECURE]
ICX_DISCOVERER_RELEASE=10
ICX_DISCOVERER_USE_VIEWR=Y N
ICX_DEFAULT_EUL=EUL
ICX_DISCOVERER_EUL_LANGUAGE=US
ICX_DISCOVERER_PARAMETERS=exitURL=http://:/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

Note:
When you open the discoverer EUL using the Discoverer 10g Desktop/Admin it will ask you if you want to upgrade your EUL and you have to choose yes.

To start/stop the new discoverer do the following:
$Disco_ORACLE_HOME/opmn/bin/opmnctl stopall (to stop).
$Disco_ORACLE_HOME/opmn/bin/opmnctl startall (to start).

Empty emoms.properties file - Solution

2009-04-25T23:21:00.000-07:00

When Oracle Management Service (OMS) filesystem is 100% full, you may loose /sysman/config/emoms.properties
Actually the file will remain empty with size of zero bytes.
A small file that can cause Grid Control not to function.

emctl start oms
.
.
.
Could not find Console Server Port in /software/oracle/OEM10gR2/oms10g/sysman/config/emoms.properties

Oracle claims that you have to install a new OMS instead of the old one. (Notes: 418453.1, 418159.1)

After some struggles looking for the undocumented syntax of the file,
I managed to make OMS working again.

The lesson
1. Always BACKUP
2. Monitor the filesystem of OMS itself
3. Create some jobs for deleting old logs and not needed archives.

Anyway, This is the file to be created with the following content,

emoms.properties
#Sun Apr 26 7:00:00 2009
oracle.sysman.emSDK.svlt.ConsoleServerName=oragridctl
oracle.sysman.eml.mntr.emdRepPwd= write here an unencrypted password
emdrep.ping.pingCommand=/bin/ping
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=oem
oracle.sysman.eml.mntr.emdRepPwdSeed= write here an unencrypted password
oracle.sysman.emRep.dbConn.statementCacheSize=30
(ADDRESS\=(PROTOCOL\=TCP)(HOST\=jigles)(PORT=1521)))(CONNECT_DATA\=(SERVICE_NAME\=oem)))
oracle.sysman.db.isqlplusUrl=http\://oragridctl\:4889/isqlplus/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerPort=4889
oracle.sysman.eml.mntr.emdRepRAC=FALSE
oracle.sysman.emSDK.emd.rt.useMonitoringCred=true
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
oracle.sysman.db.isqlplusWebDBAUrl=http\://oragridctl\:4889/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=oragridctl
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=1156
oracle.sysman.eml.mntr.emdRepSID=oem
oracle.sysman.eml.mntr.emdRepServer=oragridctl
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.eml.mntr.emdRepConnectDescriptor=
oracle.sysman.emkeyfile=/software/oracle/OEM10gR2/oms10g/sysman/config/emkey.ora
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.db.adm.conn.statementCacheSize=2
oracle.sysman.db.perf.conn.statementCacheSize=30

Manually Clone Database using RMAN

2009-04-09T02:41:00.000-07:00

1) Connect to the target database using rman backup the database on node 1

rman target /

backup database;

2) Move the following files to the NODE 2:
- backup pieces
- controlfile backup piece
- the parameter file i.e init.ora file to the default location i.e $ORACLE_HOME/dbs

3) Modify PFILE accordingly, invoke Rman on the node 2 after setting the Oracle environment variables and start the database in nomount mode:
export ORACLE_HOME, ORACLE_SID, PATH

$rman target /

RMAN> startup nomount;

4) Restore the controlfile from the backup piece.
RMAN> restore controlfile from ‘/node2/database/backup/c-3932056136-20070213-02′;

5) Mount the database
RMAN > alter database mount

6) Now catalog the backup pieces that was moved from NODE 1

RMAN> catalog backuppiece ‘/node2/database/backup/o1_mf_annn_TAG20070213T002925_2×21m6ty_.bkp’;

7) Now restore the datafiles
RMAN> run
{
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
}

also refer Note:419137.1

Move controlfiles from the Filesystem to ASM

2009-03-29T21:43:00.000-07:00

1. Identify the location of the current controlfile:

SQL> select name from v$controfile;
NAME
——————————————————————————–
/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’

2. Shutdown the database and start the instance:
SQL> shutdown normal
SQL> startup nomount

3. Use RMAN to move the controlfile to ASM :

rman target /

RMAN> restore controlfile to ‘+DATA’ from ‘/opt/app/oracle/product/11/db_1/dbs/standby_control.ctl’;

Starting restore at 30-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=539 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 30-MAR-09

RMAN> exit

We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File).
Use ASMCMD or sqlplus to identify the name assigned to the controlfile

4. On the ASM instance, identify the name of the controlfile:

Using ASMCMD:
$ asmcmd
ASMCMD> pwd
+data/standby/controlfile
ASMCMD> ls
current.1384.681888623
current.1385.681888685

This is just the name assigned for the identical copy of the current controlfile.

5. On the database side:

* Modify init.ora or spfile, replacing the new path to the init parameter control_files.
* if using init.ora, just modify the control_files parameter and restart the database.

6. start the instance.

Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.

7. SQL> show parameter control_file

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string +DATA/standby/controlfile/curr
ent.1384.681888623, +DATA/standby/controlfile/current.1385.681888685

NOTE : Steps 3 to 5 Could be repeated to multiplex the contolfiles to more than one on each diskgroup .

Copy ASM Files with DBMS_FILE_TRANSFER

2009-03-28T23:51:00.000-07:00

You can copy ASM Files using DBMS_FILE_TRANSFER pl/sql package. Lets see how to copy an archive log file from ASM storage to local disk.

RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile

RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=129 instance=rdba1 devtype=DISKvalidation succeeded for archived logarchive log filename=+FRA/rdba/archivelog/2009_03_29/thread_1_seq_2197.485.682769833 recid=490 stamp=682769833Crosschecked 1 objects

RMAN> exit
Recovery Manager complete.

C:\>sqlplus / as sysdba

SQL> create directory XXARCsrc as '+FRA/rdba/archivelog/2009_03_29';
Directory created.

SQL> create directory XXARCdest as 'C:\';
Directory created.

SQL> grant read, write on directory XXARCsrc to public;
Grant succeeded.

SQL> grant read,write on directory XXARCdest to public;
Grant succeeded.

SQL> execute dbms_file_transfer.copy_file('XXARCsrc','thread_1_seq_2197.485.682769833','XXARCdest','arc2197');

Now look for the archive file arc2197 in local disk in c-drive.

Note: This way you can copy any file from ASM to the disk.

To view the files on ASM use the ASMCMD tool,

SET oracle_home=C:\oracle\product\10.2.0\asm_1
SET oracle_sid=+ASM1

C:\>asmcmd
ASMCMD> ls
/BKP
/DATA
/FRA

ASMCMD> cd data
ASMCMD> ls
RDBA/

ASMCMD> cd rdba

C:\>set oracle_home=C:\oracle\product\10.2.0\db_1

C:\>set oracle_sid=+ASM1

C:\>asmcmd
ASMCMD> ls
BKP/
DATA/
FRA/

ASMCMD> cd data

ASMCMD> ls
DB_UNKNOWN/
RDBA/

ASMCMD> cd db_unknown
ASMCMD> ls
PARAMETERFILE/

ASMCMD> cd parameterfile

ASMCMD> ls
SPFILE.268.665669211

ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> cd rdba

ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
spfilemol.ora

ASMCMD> cd datafile

ASMCMD> ls
ARCHDATA.279.667315727
AUTOSCAN.278.667315457
COMPANY.275.667315065
COMPANY_IDX.273.667314429
EMPLOYEE.271.667314047
EMP_IDX.284.667316721
EXAMPLE.264.663930173
INDX.290.667317683
INDX_MOL1.269.667313539
INDX_MOL2.281.667316185
MAIN_MOL.277.667315275
MAIN_MOL.282.667316309
MOL_DNRD.289.667317613
MOL_DNRD_INDEX.288.667317545
OTHERS.280.667315911
OTHER_IDX.276.667315173
SYSAUX.257.663930059
SYSTEM.256.663930057
TEMPORARAY.287.667317499
TNT.272.667314321
TNT_IDX.274.667314579
TNT_IDX1.291.667317753
TOOLS.285.667317351
TUNE_INDEX.270.667313981
UNDOTBS1.258.663930059
UNDOTBS2.265.663930307
USERS.259.663930059
USR.286.667317429
VISA_CANCEL_CHNG.283.667316489

Apps 11i Technical Interview Question/Answers

2009-03-16T02:20:00.000-07:00

How to know if a specific patch is installed or not?
SELECT * FROM AD_BUGS WHERE BUG_NUMBER=x;
Here substitute x with patch number, and if the query returns a value means the patch is already applied.

What is US directory in $AD_TOP or under various product TOP’s . US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

What are main concurrent Manager types.
ICM - Internal Concurrent Manager which manages concurrent Managers
Standard Managers - Which Manage processesing of requests.
CRM - Conflict Resolution Managers , resolve conflicts in case of incompatibility.

What is difference between ICM, Standard Managers & CRM in Concurrent Manager?
ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.
Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.
CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

Where is Concurrent Manager log file location.
By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

What is multi node system?
Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.
ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.
Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.
CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

Where is Concurrent Manager log file location.
By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

What is multi node system?
Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

What is .dbc file, where its stored, what use of .dbc file?
dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

How to determine Oracle Apps 11i Version?
Select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
———————–
11.5.9 or 11.5.10.2

Whats is TWO_TASK in Oracle Database?
TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don’t want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

What is GWYUID ?
GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB
Where GWYUID defined & what is its used in Oracle Applications?
GWYUID is defined in dbc i.e. Database Connect Descriptor file. It is used to connect to database by think clients

How to check number of forms users at any time?
Forms Connections initiate f60webmx connections so you can use
ps -ef grep f60webmx wc -l

What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD?
0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.
‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

If your system has more than one Jinitiator, how will the system know, which one to pick. ?
When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used.

What are .ldt & .lct files which you see in apps patch or with FNDLOAD?
ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

What is dev60cgi & ifcgi60?
cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/ifcgi60

What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms?
Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file. This confuses version control and build tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this.

What is ps -ef or ps command in Unix ?
ps is unix/linux utility or executable to find status of process. Used mainly to find if services/process is running or not.

Oracle Clusterware - Voting Disk

2009-03-09T02:04:00.000-07:00

What is a voting disk?
A voting disk is a file that manages information about node membership.

What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks

How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.

Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name

What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with aminimum block size of 4KB.

How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
where,
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk

How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path

How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.

How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force

How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force

How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force

What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk

When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration.

Oracle Accounts Recievables - Tables

2009-03-08T21:40:00.000-07:00

Useful AR Tables Informations

Following are commonly used AR ( receivables )tables and their usage. There are many other tables also in AR but here i am putting only few commonly used tables.

1- RA_CUSTOMER_TRX_ALL

2- RA_CUSTOMER_TRX_LINES_ALL

3- RA_CUST_TRX_LINE_GL_DIST_ALL

4- AR_PAYMENT_SCHEDULES_ALL

5- AR_RECEIVABLES_TRX_ALL

6- AR_RECEIVABLE_APPLICATIONS_ALL

RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commitment, and credit memo header information. Each row includes general invoice information such as customer, transaction type, and printing instructions. You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables. Invoices, debit memos, credit memos, and commitments are all distinguished by their transaction types stored in RA_CUST_TRX_TYPES_ALL. If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID stores the customer transaction identifier of the invoice you credited. In the case of on account credits, which are not related to any invoice at creation, PREVIOUS_CUSTOMER_TRX_ID is null. If you created an invoice against a commitment, Oracle Receivables stores the customer transaction identifier of the commitment in INITIAL_CUSTOMER_TRX_ID, otherwise it is null. COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No to indicate if your invoice is complete. When you complete an invoice, Oracle Receivables creates your payment schedules and updates any commitments against this invoice. Before an invoice can be completed, it must have at least one invoice line, revenue records must exist for each line and add up to the line amount, and a sales tax record must exist for each line.
Required Columns:
SOLD_TO_CUSTOMER_ID,
SOLD_TO_SITE_USE_ID,
BILL_TO_CUSTOMER_ID,
BILL_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID,
PRINTING_OPTION,
PRINTING_PENDING,
TERM_ID,
REMIT_TO_ADDRESS_ID,
PRIMARY_SALES_REP_ID, and
INVOICE_CURRENCY_CODE
are required even though they are null allowed. The primary key for this table is CUSTOMER_TRX_ID.

RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit memo, credit memo, and commitment lines. For example, an invoice can have one line for Product A and another line for Product B. You need one row for each line. Invoice, debit memo, credit memo, and commitment lines are distinguished by the transaction type of the corresponding RA_CUSTOMER_TRX_ALL record.Also, credit memos are required to have a value in PREVIOUS_CUSTOMER_TRX_LINE_ID, except on account credits which are not related to specific invoices/invoice lines at creation time, will not have values in this column. QUANTITY_ORDERED stores the amount of product ordered. QUANTITY_INVOICED stores the amount of product invoiced. For invoices entered through the window, QUANTITY_ORDERED and QUANTITY_INVOICED must be the same. For invoices imported through AutoInvoice, QUANTITY_ORDERED and QUANTITY_INVOICED can be different. If you enter a credit memo, QUANTITY_CREDITED stores the amount of product credited. UOM_CODE stores the unit of measure code as defined in MTL_UNITS_OF_MEASURE. UNIT_STANDARD_PRICE stores the list price per unit for this transaction line. UNIT_SELLING_PRICE stores the selling price per unit for this transaction line. For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE can be different. DESCRIPTION, TAXING_RULE, QUANTITY_ORDERED, UNIT_STANDARD_PRICE,UOM_CODE, and UNIT_SELLING_PRICE are required even though they are null allowed. LINE_TYPE differentiates between the different types of lines that are stored in this table. LINE points to regular invoice lines that normally refer to an item. TAX signifies that this is a tax line. The column LINK_TO_CUST_TRX_LINE_ID references another row in this table that is the invoice line associated with the row of type TAX. FREIGHT works the same way as TAX but there you can have at most one FREIGHT type l ine per invoice line of type LINE. You can also have one line of type FREIGHT that has a null LINK_TO_CUST_TRX_LINE_ID (and this is referred to as header level freight). CHARGES works just like the LINE type. A line_type of ’CB’ is created for a Chargeback line. For every row in this table that belongs to a complete transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y), there must be at least one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting information), even for non–postable transactions. The primary key for this table is CUSTOMER_TRX_LINE_ID.

RA_CUST_TRX_LINE_GL_DIST_ALL
This table stores the accounting records for revenue, unearned revenue and unbilled receivables for each invoice or credit memo line. Each row includes the GL account and the amount of the accounting entry. The AMOUNT column in this table is required even though it is null allowed. You need one row for each accounting distribution. You must have at least one (but you can have multiple) accounting distributions for each invoice or credit memo line. Oracle Receivables uses this information to post the proper amounts to your general ledger. If your invoice or credit memo has a transaction type where Post to GL is set to No, Oracle Receivables assigns Null to GL_DATE. If your AutoAccounting is unable to complete your general ledger default accounts using the AutoAccounting rules you define, incomplete general ledger accounts are stored in CONCATENATED_SEGMENTS. If you are importing a transaction through AutoInvoice and the general ledger date of your transaction is in a closed accounting period, AutoInvoice uses the general ledger date of the first open accounting period and stores the original general ledger date in ORIGINAL_GL_DATE. ACCOUNT_CLASS defines which type of distribution row you are on. The ACCOUNT_CLASS REC represents the receivable account and is for the total amount of the invoice. There can be at most two REC rows. One that has a ACCOUNT_SET_FLAG set to Y and the other has ACCOUNT_SET_FLAG set to N. Use LATEST_REC_FLAG to join to the later of the two rows. ACCOUNT_SET_FLAG is Y if this row is part of an account set. An account set is a set of rows that represent a model distribution. Account sets are used for invoices with rules. The rows represent how the actual distribution rows should be created and what percentage of the actual distribution should be allocated to each account. For invoices with rules, the distributions are not created when the invoice is initially created. Instead, the invoices are created when the Revenue Recognition program is run. The primary key for this table is CUST_TRX_LINE_GL_DIST_ID.
AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on account credit, or receipt. Oracle Receivables groups different transactions bythe column CLASS. These classes include invoice (INV), debit memos(DM), guarantees (GUAR), credit memos (CM), deposits (DEP),chargebacks (CB), and receipts (PMT). Transaction classes determine which columns in this table Oracle Receivables updates when a transaction occurs, and whether a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALLtable. AR_PAYMENT_SCHEDULES_ALL joins to the RA_CUSTOMER_TRX_ALL table for non–payment transaction entries such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. AR_PAYMENT_SCHEDULES_ALL uses the foreign key CUSTOMER_TRX_ID to join to the RA_CUSTOMER_TRX_ALL table for these transactions. AR_PAYMENT_SCHEDULES_ALL joins to the AR_CASH_RECEIPTS_ALL table for invoice–related payment transactions using the foreign key CASH_RECEIPT_ID. When a receiptis applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS and AMOUNT_DUE_REMAINING. STATUS changes from ’OP’ to ’CL’for any transaction that has an AMOUNT_DUE_REMAINING value of 0(Zero). ACTUAL_DATE_CLOSED and GL_DATE_CLOSED are populated with the date of the latest transaction. For a receipt, the amount due remaining includes on account and unapplied amounts. Oracle Receivables stores debit items such as invoices, debit memos, chargebacks, deposits, and guarantees as positive numbers in the AMOUNT_DUE_REMAINING and AMOUNT_DUE_ORIGINAL columns. Credit items such as credit memos and receipts are stored as negative numbers. In Release 10, receipts can be confirmed or not confirmed as designated by the CONFIRMED_FLAG column. The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. If this amount is negative, then this column indicates the credit balance amount currently available for this customer. For invoices with split terms, one record is created in RA_CUSTOMER_TRX_ALL and one record is stored in AR_PAYMENT_SCHEDULES_ALL for each installment. In AR_PAYMENT_SCHEDULES_ALL, DUE_DATE and AMOUNT_DUE_REMAINING can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column.
If you create a debit memo reversal when you reverse a receipt, Oracle Receivables creates a new payment schedule record for the debit memo and fills in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was reversed. Oracle Receivables creates a new payment schedule record when you create a chargeback in the Receipts window. ASSOCIATED_CASH_RECEIPT_ID is the cash receipt of the payment you entered when you created the chargeback in this window. GL_DATE_CLOSED indicates the general ledger date on which your transaction was closed.

This column identifies which transactions Oracle Receivables selects when it displays current and overdue debit items in the aging reports. The aging reports also utilize the current balances in AMOUNT_DUE_REMAINING to display outstanding amounts for current and overdue debit items. ACTUAL_DATE_CLOSED gives the date on which you applied a payment or credit to an open transaction that set AMOUNT_DUE_REMAINING to 0 for that transaction. Oracle Receivables uses ACTUAL_DATE_CLOSED to determine which transactions to include when you print statements. The primary key for this table is PAYMENT_SCHEDULE_ID, which identifies the transaction that created the row.

RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commitment, and credit memo header information. Each row includes general invoice information such as customer, transaction type, and printing instructions. You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables. Invoices, debit memos, credit memos, and commitments are all distinguished by their transaction types stored in RA_CUST_TRX_TYPES_ALL. If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID stores the customer transaction identifier of the invoice you credited. In the case of on account credits, which are not related to any invoice at creation, PREVIOUS_CUSTOMER_TRX_ID is null. If you created an invoice against a commitment, Oracle Receivables stores the customer transaction identifier of the commitment in INITIAL_CUSTOMER_TRX_ID, otherwise it is null. COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No to indicate if your invoice is complete. When you complete an invoice, Oracle Receivables creates your payment schedules and updates any commitments against this invoice. Before an invoice can be completed, it must have at least one invoice line, revenue records must exist for each line and add up to the line amount, and a sales tax record must exist for each line.
Required Columns:
SOLD_TO_CUSTOMER_ID,
SOLD_TO_SITE_USE_ID,
BILL_TO_CUSTOMER_ID,
BILL_TO_SITE_USE_ID,
SHIP_TO_SITE_USE_ID,
PRINTING_OPTION,
PRINTING_PENDING,
TERM_ID,
REMIT_TO_ADDRESS_ID,
PRIMARY_SALES_REP_ID, and
INVOICE_CURRENCY_CODE
are required even though they are null allowed. The primary key for this table is CUSTOMER_TRX_ID.
RA_CUSTOMER_TRX_LINES_ALL

This table stores information about invoice, debit memo, credit memo, and commitment lines. For example, an invoice can have one line for Product A and another line for Product B. You need one row for each line. Invoice, debit memo, credit memo, and commitment lines are distinguished by the transaction type of the corresponding RA_CUSTOMER_TRX_ALL record.Also, credit memos are required to have a value in PREVIOUS_CUSTOMER_TRX_LINE_ID, except on account credits which are not related to specific invoices/invoice lines at creation time, will not have values in this column. QUANTITY_ORDERED stores the amount of product ordered. QUANTITY_INVOICED stores the amount of product invoiced. For invoices entered through the window, QUANTITY_ORDERED and QUANTITY_INVOICED must be the same. For invoices imported through AutoInvoice, QUANTITY_ORDERED and QUANTITY_INVOICED can be different. If you enter a credit memo, QUANTITY_CREDITED stores the amount of product credited. UOM_CODE stores the unit of measure code as defined in MTL_UNITS_OF_MEASURE. UNIT_STANDARD_PRICE stores the list price per unit for this transaction line. UNIT_SELLING_PRICE stores the selling price per unit for this transaction line. For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE can be different. DESCRIPTION, TAXING_RULE, QUANTITY_ORDERED, UNIT_STANDARD_PRICE,UOM_CODE, and UNIT_SELLING_PRICE are required even though they are null allowed. LINE_TYPE differentiates between the different types of lines that are stored in this table. LINE points to regular invoice lines that normally refer to an item. TAX signifies that this is a tax line. The column LINK_TO_CUST_TRX_LINE_ID references another row in this table that is the invoice line associated with the row of type TAX. FREIGHT works the same way as TAX but there you can have at most one FREIGHT type l ine per invoice line of type LINE. You can also have one line of type FREIGHT that has a null LINK_TO_CUST_TRX_LINE_ID (and this is referred to as header level freight). CHARGES works just like the LINE type. A line_type of ’CB’ is created for a Chargeback line. For every row in this table that belongs to a complete transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y), there must be at least one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting information), even for non–postable transactions. The primary key for this table is CUSTOMER_TRX_LINE_ID.

RA_CUST_TRX_LINE_GL_DIST_ALL
This table stores the accounting records for revenue, unearned revenue and unbilled receivables for each invoice or credit memo line. Each row includes the GL account and the amount of the accounting entry. The AMOUNT column in this table is required even though it is null allowed. You need one row for each accounting distribution. You must have at least one (but you can have multiple) accounting distributions for each invoice or credit memo line. Oracle Receivables uses this information to post the proper amounts to your general ledger. If your invoice or credit memo has a transaction type where Post to GL is set to No, Oracle Receivables assigns Null to GL_DATE. If your AutoAccounting is unable to complete your general ledger default accounts using the AutoAccounting rules you define, incomplete general ledger accounts are stored in CONCATENATED_SEGMENTS. If you are importing a transaction through AutoInvoice and the general ledger date of your transaction is in a closed accounting period, AutoInvoice uses the general ledger date of the first open accounting period and stores the original general ledger date in ORIGINAL_GL_DATE. ACCOUNT_CLASS defines which type of distribution row you are on. The ACCOUNT_CLASS REC represents the receivable account and is for the total amount of the invoice. There can be at most two REC rows. One that has a ACCOUNT_SET_FLAG set to Y and the other has ACCOUNT_SET_FLAG set to N. Use LATEST_REC_FLAG to join to the later of the two rows. ACCOUNT_SET_FLAG is Y if this row is part of an account set. An account set is a set of rows that represent a model distribution. Account sets are used for invoices with rules. The rows represent how the actual distribution rows should be created and what percentage of the actual distribution should be allocated to each account. For invoices with rules, the distributions are not created when the invoice is initially created. Instead, the invoices are created when the Revenue Recognition program is run. The primary key for this table is CUST_TRX_LINE_GL_DIST_ID.

AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on account credit, or receipt. Oracle Receivables groups different transactions bythe column CLASS. These classes include invoice (INV), debit memos(DM), guarantees (GUAR), credit memos (CM), deposits (DEP),chargebacks (CB), and receipts (PMT). Transaction classes determine which columns in this table Oracle Receivables updates when a transaction occurs, and whether a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALLtable. AR_PAYMENT_SCHEDULES_ALL joins to the RA_CUSTOMER_TRX_ALL table for non–payment transaction entries such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. AR_PAYMENT_SCHEDULES_ALL uses the foreign key CUSTOMER_TRX_ID to join to the RA_CUSTOMER_TRX_ALL table for these transactions. AR_PAYMENT_SCHEDULES_ALL joins to the AR_CASH_RECEIPTS_ALL table for invoice–related payment transactions using the foreign key CASH_RECEIPT_ID. When a receiptis applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS and AMOUNT_DUE_REMAINING. STATUS changes from ’OP’ to ’CL’for any transaction that has an AMOUNT_DUE_REMAINING value of 0(Zero). ACTUAL_DATE_CLOSED and GL_DATE_CLOSED are populated with the date of the latest transaction. For a receipt, the amount due remaining includes on account and unapplied amounts. Oracle Receivables stores debit items such as invoices, debit memos, chargebacks, deposits, and guarantees as positive numbers in the AMOUNT_DUE_REMAINING and AMOUNT_DUE_ORIGINAL columns. Credit items such as credit memos and receipts are stored as negative numbers. In Release 10, receipts can be confirmed or not confirmed as designated by the CONFIRMED_FLAG column. The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. If this amount is negative, then this column indicates the credit balance amount currently available for this customer. For invoices with split terms, one record is created in RA_CUSTOMER_TRX_ALL and one record is stored in AR_PAYMENT_SCHEDULES_ALL for each installment. In AR_PAYMENT_SCHEDULES_ALL, DUE_DATE and AMOUNT_DUE_REMAINING can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column.
If you create a debit memo reversal when you reverse a receipt, Oracle Receivables creates a new payment schedule record for the debit memo and fills in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was reversed. Oracle Receivables creates a new payment schedule record when you create a chargeback in the Receipts window. ASSOCIATED_CASH_RECEIPT_ID is the cash receipt of the payment you entered when you created the chargeback in this window. GL_DATE_CLOSED indicates the general ledger date on which your transaction was closed.

This column identifies which transactions Oracle Receivables selects when it displays current and overdue debit items in the aging reports. The aging reports also utilize the current balances in AMOUNT_DUE_REMAINING to display outstanding amounts for current and overdue debit items. ACTUAL_DATE_CLOSED gives the date on which you applied a payment or credit to an open transaction that set AMOUNT_DUE_REMAINING to 0 for that transaction. Oracle Receivables uses ACTUAL_DATE_CLOSED to determine which transactions to include when you print statements. The primary key for this table is PAYMENT_SCHEDULE_ID, which identifies the transaction that created the row.

AR_RECEIVABLES_TRX_ALL
This table links accounting information with your Receivables Activities. Possible types of activities include Adjustment, Miscellaneous Cash, and Finance Charges. If your type is Miscellaneous Cash, you can associate either a distribution set or a standard accounting flexfield to your Receivables Activity. Oracle Receivables uses one row for each activity. You use your receivables activities to speed receipt entry and generate finance charges. The other types of activities that were valid in release 9 and no longer valid in Release 10 were converted (as part of the upgrade) such that the actual accounting flexfield CODE_COMBINATION_ID is stored in the table instead of the RECEIVABLES_TRX_ID. In Release 9, all of these references were in AR_BATCH_SOURCES; they are now in AR_RECEIPT_METHOD_ACCOUNTS_ALL. The primary key for this table is RECEIVABLES_TRX_ID.
AR_RECEIVABLE_APPLICATIONS_ALL
This table stores all accounting entries for your cash and credit memo applications. Each row includes the amount applied, status, and accounting flexfield information. Possible statuses of your applications include APP, UNAPP, ACC, and UNID. You use this information to determine the applications of your payments or credit memos. CONFIRMED_FLAG is a denormalization from AR_CASH_RECEIPTS_ALL.If the cash receipt is not confirmed, the applications of that receipt are not reflected in the payment schedule of the transaction it is applied against. There are two kinds of applications: CASH and CM (for credit memo applications). This is stored in the column APPLICATION_TYPE.
CASH applications represent applications of a cash receipt. When a cash receipt is initially created, a row is created in this table that has a status of UNAPP for the amount of the cash receipt. Each subsequent application creates two rows – one with a status of APP for the amount being applied to the invoice and one with status UNAPP for the negative of the amount being applied. Ifyou reverse a cash application, a row with status APP with the inverse amount of the original application (i.e. the negative of the original application amount) is created. The corresponding UNAPP rows is alsocreated which will have a positive amount (the same amount as the application being reversed). For example: UNAPP 100 creation of a$100 cash receipt APP 60 application of $60 of this cash receipt UNAPP –60 this row takes away (debits) unapplied APP –60 reversal of the $60 application UNAPP 60 this rows puts back(credits) unapplied The sum of the AMOUNT_APPLIED column for CASH applications should always equal the amount of the cash receipt. CM applications, on the other hand, do not have rows of status UNAPP. They only use rows with a status of APP. CASH_RECEIPT_ID stores the cash receipt identifier of the receipt you entered. Oracle Receivables concurrently creates a record of this receipt in the AR_CASH_RECEIPTS_ALL table.

This column is null for a credit memo application. CODE_COMBINATION_ID stores valid Accounting Flexfield segment value combinations that will be credited in the General Ledger when this application is posted. A negative value in AMOUNT_APPLIED becomes a debit. The STATUS of a receivable application determines which flexfield account Oracle Receivables uses. For example, if you enter a cash receipt of $500 as Unidentified, Oracle Receivables creates a record in theAR_RECEIVABLE_APPLICATIONS_ALL table with AMOUNT_APPLIED = 500 and STATUS = ’UNID’. Oracle Receivables uses the foreign key CODE_COMBINATION_ID to associate this payment with the Unidentified flexfield account. CUSTOMER_TRX_ID, CASH_RECEIPT_ID, and PAYMENT_SCHEDULE_ID identify the transaction that you are actually applying. APPLIED_CUSTOMER_TRX_ID and APPLIED_PAYMENT_SCHEDULE_ID identify the invoice or credit memo that receives the application. For example, if you apply a receipt against an invoice, Oracle Receivables creates a record in the AR_RECEIVABLE_APPLICATIONS_ALL table. The CASH_RECEIPT_ID and the PAYMENT_SCHEDULE_ID of this record identify the receipt you are applying. APPLIED_PAYMENT_SCHEDULE_ID and APPLIED_CUSTOMER_TRX_ID for this record belong to the invoice that is receiving the application. If you apply a credit memo against the invoice, Oracle Receivables creates a record in the AR_RECEIVABLE_APPLICATIONS_ALL table that has theCUSTOMER_TRX_ID and the PAYMENT_SCHEDULE_ID of the credit memo you are applying. The APPLIED_PAYMENT_SCHEDULE_ID and the APPLIED_CUSTOMER_TRX_ID of this record belong to the invoice that is receiving the application. If you combine an on account credit and a receipt, Oracle Receivables creates a record in the AR_RECEIVABLE_APPLICATIONS_ALL table.

The PAYMENT_SCHEDULE_ID and the CASH_RECEIPT_ID of this record identify the receipt. The APPLIED_PAYMENT_SCHEDULE_ID and the APPLIED_CUSTOMER_TRX_ID of this record identify the on account credit that you are combining with the receipt. The primary key for this table is RECEIVABLE_APPLICATION_ID, which uniquely identifies the transaction that created the row.

Oracle General Ledger - Tables

2009-03-08T21:38:00.000-07:00

Oracle General Ledger - Tables

Following are commonly used GL tables and their usage. There are many other tables also in GL but here i am putting only few commonly used tables.

1- GL_SETS_OF_BOOKS

2- GL_IMPORT_REFERENCES

3- GL_DAILY_RATES

4- GL_JE_LINES

5- GL_PERIODS

6- GL_JE_HEADERS

7- GL_JE_BATCHES

8- GL_BALANCES

9- GL_CODE_COMBINATIONS

GL_SETS_OF_BOOKS
Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES
Stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES
Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table.

GL_JE_LINES
Stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_PERIODS
Stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows inthis table.

GL_JE_HEADERS
Stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_BATCHES
Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_BALANCES
Stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.
GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.
An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.
For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance. Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is notused.

GL_CODE_COMBINATIONS
Stores valid account combinations foreach Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting ordetail budgeting is allowed, and others. Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order. The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL