Re create INST_TOP

Re create INST_TOP

Run AutoConfig using: "perl $AD_TOP/bin/adconfig.pl contextfile=<CONTEXT_FILE>" command and it will create the $INST_TOP for you.

Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]

============================

Find Oracle Applications Login URL from database

Find Oracle Applications Login URL from database

SQL> SELECT home_url FROM icx_parameters;

SQL> SELECT profile_option_value FROM fnd_profile_option_values WHERE profile_option_id= (SELECT profile_option_id FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT') AND level_value = 0;

Oracle R12 Forms Not Opening In Browser

Oracle R12 Forms Not Opening In Browser

Issue:

When trying to open forms in R12, following error is coming


 Solution:


1. Go to Start >  Control Panel


2. Click on Java Control Panel and select tab "security" and make Security Level "Medium"

 3. Also, add URL in the Exception Site List. For e.g http://xyz.erp.com:8000 and apply it

How to Find Sessions Generating Lots of Redo or Archive logs


How to Find Sessions Generating Lots of Redo or Archive logs (Doc ID 167492.1)

TO CHECK SESSIONS

SQL> SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;

TO CHECK TRANSACTIONS

SQL> SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4

TO CHECK EXACT QUERY

SELECT address ,SUBSTR(sql_text,1,1000) Text, buffer_gets, executions,
buffer_gets/executions AVG
FROM v$sqlarea a, v$session s
WHERE sid = <SID>
AND s.sql_address = a.address
AND executions > 0
ORDER BY 5;


===============================

Empty the linux buffer cache

Empty the Linux buffer cache

sync && echo 3 > /proc/sys/vm/drop_caches


Change Oracle password temporarily

Change Oracle password temporarily

SQL> SELECT a.username, b.password FROM dba_users a, sys.user$ b WHERE a.username = 'AHMED' AND a.user_id = b.user#


USERNAME PASSWORD
-------- ----------------
AHMED 3KD55KGFST529KT5


SQL> alter user HRPAY identified by newpassword
After completing your work , now you can change the password back by using an undocumented feature called "by values"

SQL>alter user AHMED identified by values '3KD55KGFST529KT5';

Attach Concurrent Program / Request Set to Request Group


Attach Concurrent Program / Request Set to Request Group


1. Find the Request Group of the Responsibility
Responsibility: System Administrator
Navigation: Security > Responsibility > Define



Search for the Responsibility with which the Concurrent Program needs to be run. Copy the request group name.



2. Attach the Concurrent Program/Request Set to the Request Group
Responsibility: System Administrator
Navigation: Security > Responsibility > Request

Search the request Group name for attaching the concurrent Program



Attach the Concurrent Program or Request Set to this Request Group.



Once attached, the Concurrent Program/Request Set can be run from given responsibility from the SRS.

Query

The main query joins Concurrent Program table or Request Set table and Request Group table with the help of Request Group Units as the intermediate table. The Request Group table is finally joined with the Responsibility table. Below are the main tables used:

FND_REQUEST_GROUPS
FND_REQUEST_GROUP_UNITS
FND_CONCURRENT_PROGRAMS (For Concurrent Program)
FND_CONCURRENT_PROGRAMS_TL (For Concurrent Program)
FND_REQUEST_SETS_TL (For Request Set)
FND_REQUEST_SETS (For Request Set)
FND_RESPONSIBILITY_TL
FND_RESPONSIBILITY

The main queries take the final shape as below:

Concurrent Program
select fcpt.user_concurrent_program_name ,fcp.concurrent_program_name program_short_name ,frt.responsibility_name ,frg.request_group_name from fnd_request_groups frg ,fnd_request_group_units frgu ,fnd_concurrent_programs fcp ,fnd_concurrent_programs_tl fcpt ,fnd_responsibility_tl frt ,fnd_responsibility frs where frgu.unit_application_id = fcp.application_id and frgu.request_unit_id = fcp.concurrent_program_id and frg.request_group_id = frgu.request_group_id and frg.application_id = frgu.application_id and fcpt.language='US' and fcp.application_id = fcpt.application_id and fcp.concurrent_program_id = fcpt.concurrent_program_id and frs.application_id = frt.application_id and frs.responsibility_id = frt.responsibility_id and frt.language='US' and frs.request_group_id = frg.request_group_id and frs.application_id = frg.application_id and fcp.concurrent_program_name = nvl(:concurrent_program_name,fcp.concurrent_program_name) and fcpt.user_concurrent_program_name =nvl(:user_concurrent_program_name ,fcpt.user_concurrent_program_name) order by fcpt.user_concurrent_program_name;


Request Set
select frst.user_request_set_name ,frt.responsibility_name ,frg.request_group_name from apps.fnd_request_sets_tl frst ,apps.fnd_request_sets frs ,fnd_request_group_units frgu ,fnd_request_groups frg ,fnd_responsibility_tl frt ,fnd_responsibility fr where 1=1 and fr.application_id = frt.application_id and fr.responsibility_id = frt.responsibility_id and frt.language = 'US' and fr.request_group_id = frg.request_group_id and fr.application_id = frg.application_id and frg.request_group_id = frgu.request_group_id and frg.application_id = frgu.application_id and frgu.unit_application_id = frs.application_id and frgu.request_unit_id=frs.request_set_id and frs.request_set_id=frst.request_set_id and frst.language='US' and frst.user_request_set_name= :user_request_set_name order by frst.user_request_set_name;

How to create Oracle AWR report for a single instance Oracle database


How to create Oracle AWR report for a single instance Oracle database


To create the AWR report for a single instance Oracle database (use awrrpt.sql) as:

1. Find the script
2. Login as sysdba
3. Execute the script. While executing supply the following:

a. format of the report (html/text)

b. for number of days (don’t enter anything if you want to generate for specific snapshots). press enter

c. enter starting snapshot id from the displayed list

d. enter ending snapshot id from the displayed list

e. give a name for the report including file extension (txt/html/htm)

4. Once report is generated you exit sql*plus and view the report in the server or
5. transfer the report to your local machine and view it.

Here are the steps:

[oracle@DEVSERV ~]$ ls -l /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrr*
-rw-r–r– 1 oracle oinstall 7575 Apr 18  2005 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpti.sql
-rw-r–r– 1 oracle oinstall 1999 Oct 24  2003 /u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql
[oracle@DEVSERV ~]$
[[oracle@DEVSERV ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Apr 20 15:45:45 2011

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

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

SQL> set pages 999 line 300
SQL>
SQL>
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance
———– ———— ——– ————
2330100236 DEVDB               1 DEVDB

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter ‘html’ for an HTML report, or ‘text’ for plain text
Defaults to ‘html’
Enter value for report_type: text

Type Specified:  text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host
———— ——– ———— ———— ————
* 2330100236        1 DEVDB        DEVDB        DEVSERV

Using 2330100236 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days:

Listing all Completed Snapshots

Snap
Instance     DB Name        Snap Id    Snap Started    Level
———— ———— ——— —————— —–
DEVDB        DEVDB            24129 07 Apr 2011 01:00      1
24130 07 Apr 2011 02:00      1
24131 07 Apr 2011 03:00      1
24132 07 Apr 2011 04:00      1
24133 07 Apr 2011 05:00      1
24134 07 Apr 2011 06:00      1
24135 07 Apr 2011 07:00      1
24136 07 Apr 2011 08:00      1
24137 07 Apr 2011 09:00      1
24138 07 Apr 2011 10:00      1
24139 07 Apr 2011 11:00      1
24140 07 Apr 2011 12:00      1
24141 07 Apr 2011 13:00      1
24142 07 Apr 2011 14:00      1
24143 07 Apr 2011 15:00      1
24144 07 Apr 2011 16:00      1
24145 08 Apr 2011 18:00      1
24146 08 Nov 2011 18:00      1
24147 08 Feb 2012 13:56      1
24148 12 Oct 2013 18:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 24146
Begin Snapshot Id specified: 24146

Enter value for end_snap: 24148
End   Snapshot Id specified: 24148

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_24146_24148.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: new_awr_report_24146_24148.txt

Using the report name new_awr_report_24146_24148.txt

Report will be generated now and scroll through the screen. Wait until it completes.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
———— ———– ———— ——– ————— ———– —
DEVDB         2330100236 DEVDB               1 11-Mar-11 12:42 11.1.0.7.0  NO

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
—————- ——————————– —- —– ——- ———-
DEVSERV          Linux x86 64-bit                    8     8       4      31.29

Snap Id      Snap Time      Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap:     24146 08-Nov-11 18:00:59       125       1.0
End Snap:     24148 12-Oct-13 18:00:50       123        .9
Elapsed:        1,013,759.86 (mins)
DB Time:        7,337,318.40 (mins)

——————–

——————–

——————–

——————–

——————–

End of Report
Report written to new_awr_report_24146_24148.txt
SQL>

How to purge e-mail notifications from the Workflow queue


How to purge e-mail notifications from the Workflow queue


Some times due to large number of e-mail notifications to accumulated in the queue Workflow mailer will not start,To fix this issue we need purge the notifications from the Workflow queue.


The below outlines the steps, Please take proper backup before performing the below.

1) You need to update the notifications you do not want sent, in the WF_NOTIFICATIONS table.

2) Check the WF_NOTIFICATIONS table as below. Records where status = 'OPEN' and mail_status = 'MAIL' are notifications that will have an e-mail notification sent.

SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

3) This should show you which notifications are waiting to be e-mailed.

4) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The mailer will think the e-mail has already been sent and it will not send it again.

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

-> This will update all notifications waiting to be sent by the mailer.

5) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent.Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.

SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

6) Now you can start your WF Containers and then Mailer


Reference : How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent (Doc ID 372933.1)

Copy data from one database / table to another database / table in oracle using TOAD


Copy data from one database / table to another database / table in oracle using TOAD


A. Create a copy of the table in destination DB
1.In source DB in a schema browser window click on the table you want to copy, select "script" tab in the right part of the window: you will find the script to re-create your table; copy this script
2.Paste the script in a new SQL editor window in destination DB and run it. This should create the new table

B. Copy data in new table

1. In a schema browser window right click on table name in source DB
2. Select "Export Data" from context menu
3. Write "where" statement of your export query (leave it blank if you want to copy the entire table)

4. Select destination: clipboard
5. Click "ok" (now insert statements are stored in your clipboard)
6. Paste insert statements in a new SQL editor window in destination DB
7. Run statements as script (shortcut F5)

Through database link ( dblink )


INSERT INTO tool_service_mapping SELECT * FROM tool_service_mapping@<<db link to source database>>

How to Apply patch on Oracle RAC

How to Apply patch on Oracle RAC
 

Prerequisite:-
 

>> Check Oracle service status
[oracle@rac-node1 ~]$crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE dbsr...ode1
ora....E1.lsnr application ONLINE ONLINE dbsr...ode1
ora....de1.gsd application ONLINE ONLINE dbsr...ode1
ora....de1.ons application ONLINE ONLINE dbsr...ode1
ora....de1.vip application ONLINE ONLINE dbsr...ode1
ora....SM2.asm application ONLINE ONLINE dbsr...ode2
ora....E2.lsnr application ONLINE ONLINE dbsr...ode2
ora....de2.gsd application ONLINE ONLINE dbsr...ode2
ora....de2.ons application ONLINE ONLINE dbsr...ode2
ora....de2.vip application ONLINE ONLINE dbsr...ode2
ora.prod.db application ONLINE ONLINE dbsr...ode1
ora....b1.inst application ONLINE ONLINE dbsr...ode1
ora....b2.inst application ONLINE ONLINE dbsr...ode2
[oracle@rac-node1 ~]$


>> Stop database instance one by one

[oracle@rac-node1 ~]$ srvctl stop instance -i prod1 -d prod
[oracle@rac-node1 ~]$ srvctl stop instance -i prod2 -d prod
 

>>  Stop ASM instance on each node one by one
[oracle@rac-node1 ~]$ srvctl stop asm -n rac-node1
[oracle@rac-node1 ~]$ srvctl stop asm -n rac-node2
 

>> Stop node applications one by one on each node
[oracle@rac-node1 ~]$ srvctl stop nodeapps -n rac-node1
[oracle@rac-node1 ~]$ srvctl stop nodeapps -n rac-node2
 

>> Check status again
[oracle@rac-node1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application OFFLINE OFFLINE
ora....E1.lsnr application OFFLINE OFFLINE
ora....de1.gsd application OFFLINE OFFLINE
ora....de1.ons application OFFLINE OFFLINE
ora....de1.vip application OFFLINE OFFLINE
ora....SM2.asm application OFFLINE OFFLINE
ora....E2.lsnr application OFFLINE OFFLINE
ora....de2.gsd application OFFLINE OFFLINE
ora....de2.ons application OFFLINE OFFLINE
ora....de2.vip application OFFLINE OFFLINE
ora.prod.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
[oracle@rac-node1 ~]
 

>> Download Patch from Metalink.

>> Unzip patch folder after that read readme file.


>> Go to Opatch top.

[oracle@rac-node1 rman]$ cd 9949948
OPatch should be in your path as follows
Set Oracle_Home.
[oracle@rac-node1 9949948]$ export PATH=$PATH:/u01/app/oracle/product/10.2.0 /db_1/OPatch
 

>> Start apply Opatch
[oracle@rac-node1 9949948]$ opatch apply
Invoking OPatch 10.2.0.4.9
Oracle Interim Patch Installer version 10.2.0.4.9
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/10.2.0/db_1
Central Inventory : /u01/app/oracle/OraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.5.0
OUI location : /u01/app/oracle/product/10.2.0/db_1/oui
Log file location : /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2012-09-20_23-38-29PM.log
Patch history file: /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
ApplySession applying interim patch '9949948' to OH '/u01/app/oracle/product/10.2.0/db_1'
Running prerequisite checks...
OPatch detected the node list and the local node from the inventory. OPatch will patch the local system then propagate This node is part of an Oracle Real Application Cluster.
Remote nodes: 'rac-node2'
Local node: 'rac-node1'
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')
Is the local system ready for patching? [y|n]
Y ==> I put yes
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '9949948' for restore. This might take a while...
Backing up files affected by the patch '9949948' for rollback. This might take a while...
Patching component oracle.rdbms, 10.2.0.5.0...
Updating archive file "/u01/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/ksfd.o"
Running make for target ioracle
ApplySession adding interim patch '9949948' to inventory
Verifying the update...
Inventory check OK: Patch ID 9949948 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9949948 are present in Oracle Home.
The local system has been patched. You can restart Oracle instances on it.
Patching in rolling mode.
The node 'rac-node2' will be patched next.
Please shutdown Oracle instances running out of this ORACLE_HOME on 'rac-node2'.
(Oracle Home = '/u01/app/oracle/product/10.2.0/db_1')
Is the node ready for patching? [y|n]
y ==> I put yes for node2
User Responded with: Y
Updating nodes 'rac-node2'
Apply-related files are:
FP = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_files.txt"
DP = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_dirs.txt"
MP = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/make_cmds.txt"
RC = "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/remote_cmds.txt"
Instantiating the file "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/copy_Propagating directories to remote nodes...
Instantiating the file "/u01/app/oracle/product/10.2.0/db_1/.patch_storage/9949948_Aug_13_2010_06_35_20/rac/make_Running command on remote node 'rac-node2':
cd /u01/app/oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/The node 'rac-node2' has been patched. You can restart Oracle instances on it.
There were relinks on remote nodes. Remember to check the binary size and timestamp on the nodes 'rac-node2' .
The following make commands were invoked on remote nodes:
'cd /u01/app/oracle/product/10.2.0/db_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/'
OPatch succeeded.
Start Oracle services on both nodes
[oracle@rac-node1 9949948]$ srvctl start nodeapps -n rac-node1
[oracle@rac-node1 9949948]$ srvctl start nodeapps -n rac-node2
[oracle@rac-node1 9949948]$ srvctl start asm -n rac-node1
[oracle@rac-node1 9949948]$ srvctl start asm -n rac-node2
[oracle@rac-node1 9949948]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE dbsr...ode1
ora....E1.lsnr application ONLINE ONLINE dbsr...ode1
ora....de1.gsd application ONLINE ONLINE dbsr...ode1
ora....de1.ons application ONLINE ONLINE dbsr...ode1
ora....de1.vip application ONLINE ONLINE dbsr...ode1
ora....SM2.asm application ONLINE ONLINE dbsr...ode2
ora....E2.lsnr application ONLINE ONLINE dbsr...ode2
ora....de2.gsd application ONLINE ONLINE dbsr...ode2
ora....de2.ons application ONLINE ONLINE dbsr...ode2
ora....de2.vip application ONLINE ONLINE dbsr...ode2
ora.prod.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
 

>> Start database instances on both nodes:-
[oracle@rac-node1 9949948]$ srvctl start instance -i prod1 -d prod
[oracle@rac-node1 9949948]$ srvctl start instance -i prod1 -d prod
[oracle@rac-node1 9949948]$ crs-stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE dbsr...ode1
ora....E1.lsnr application ONLINE ONLINE dbsr...ode1
ora....de1.gsd application ONLINE ONLINE dbsr...ode1
ora....de1.ons application ONLINE ONLINE dbsr...ode1
ora....de1.vip application ONLINE ONLINE dbsr...ode1
ora....SM2.asm application ONLINE ONLINE dbsr...ode2
ora....E2.lsnr application ONLINE ONLINE dbsr...ode2
ora....de2.gsd application ONLINE ONLINE dbsr...ode2
ora....de2.ons application ONLINE ONLINE dbsr...ode2
ora....de2.vip application ONLINE ONLINE dbsr...ode2
ora.prod.db application ONLINE ONLINE dbsr...ode2
ora....b1.inst application ONLINE ONLINE dbsr...ode1
ora....b2.inst application ONLINE ONLINE dbsr...ode2
 

[oracle@rac-node1 script]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 – Production on Thu Sep 20 23:44:01 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
 

SQL>conn /as sysdba
Connected.
SQL>

Retrieve old versions of procedures with Flashback Query


Retrieve old versions of procedures with Flashback Query


SQL> select text from dba_source as of timestamp systimestamp - interval '30' minute where name='XXXX' order by line;

Configuring Unix / Linux File and Directory Access Rights

Configuring Unix / Linux File and Directory Access Rights
 

Below are some other examples of how to use the chmod command:

>> full access for everybody:
chmod 777 filename
>> full access for owner and group privileges but other users can only read and execute:
chmod 775 filename
>> full access for owner, but restricting group and other user privileges to only read and execute for files in the directory:
chmod 755 dirname
>> full access for the owner with no access rights or privileges for anyone else:
chmod 700 filename
chmod 700 -r /path/to/folder
>> no access to files in directory for group or other users and owner restricted to read and execute privileges to prevent the accidental deletion or modification of

files in the directory:
chmod 500 dirname
>> allowing the owner and group read and write access to a file, allowing others in the group to edit or delete the file as well as the owner, but with no access for

other users:
chmod 660 filename

===============================

APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing


APPS_TS_TX_DATA and APPS_TS_TX_IDX size keep on increasing

Step1: Truncate these tables:-


SQL>truncate table xla.XLA_DIAG_SOURCES;

SQL>truncate table xla.XLA_DIAG_EVENTS;

Step2: Turn OFF the “SLA: Diagnostics Enabled” profile option at all levels.

Step3: Check the tables, tablespace and archive logs status

===============================

How to set the path and environment variables in Windows


How to set the path and environment variables in Windows

Windows 8 users
From the Desktop, right-click the very bottom left corner of the screen to get thePower User Task Menu.
From the Power User Task Menu, click System.
Click the Advanced System Settings link in the left column.
In the System Properties window, click on the Advanced tab, then click theEnvironment Variables button near the bottom of that tab.
In the Environment Variables window (pictured below), highlight the Path variable in the "System variables" section and click the Edit button. Add or modify the path lines with the paths you want the computer to access. Each different directory is separated with a semicolon as shown below.

C:\Program Files;C:\Winnt;C:\Winnt\System32



Note: You can edit other environment variables by highlighting the variable in the "System variables" section and clicking Edit. If you need to create a new environment variable, click New and enter the Variable name and Variable value.

To view and set the path in the Windows command line, use the path command.
Windows Vista and Windows 7 users
From the Desktop, right-click the Computer icon and select Properties. If you don't have a Computer icon on your desktop, click the Start button, right-click theComputer option in the Start menu, and select Properties.
Click the Advanced System Settings link in the left column.
In the System Properties window, click on the Advanced tab, then click theEnvironment Variables button near the bottom of that tab.
In the Environment Variables window (pictured below), highlight the Path variable in the "System variables" section and click the Edit button. Add or modify the path lines with the paths you want the computer to access. Each different directory is separated with a semicolon as shown below.

C:\Program Files;C:\Winnt;C:\Winnt\System32



Note: You can edit other environment variables by highlighting the variable in the "System variables" section and clicking Edit. If you need to create a new environment variable, click New and enter the Variable name and Variable value.

To view and set the path in the Windows command line, use the path command.
Windows 2000 and Windows XP users

The path is now managed by Windows 2000 and Windows XP and not the autoexec.bat or autoexec.nt files as was done with earlier versions of Windows. To change the system environment variables, follow the steps below.
From the Desktop, right-click My Computer and click Properties. If you don't have a My Computer icon on your desktop, click the Start button, right-click the My Computer option in the Start menu, and select Properties.
In the System Properties window, click on the Advanced tab.
In the "Advanced" section, click the Environment Variables button.
Finally, in the Environment Variables window (as shown below), highlight the Pathvariable in the Systems Variable section and click the Edit button. Add or modify the path lines with the paths you want the computer to access. Each different directory is separated with a semicolon as shown below.

C:\Program Files;C:\Winnt;C:\Winnt\System32



Note: You can edit other environment variables by highlighting the variable in the "System variables" section and clicking Edit. If you need to create a new environment variable, click New and enter the Variable name and Variable value.

To view and set the path in the Windows command line, use the path command.
What is the default Windows Environment Path?

The path is based on programs installed on the computer, so there is no "default path". However, the Windows minimum path is typically the path below.

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

Note: Keep in mind that as you install programs, the path is updated with the paths for the newly installed programs. So, if you have erased your path after installing other programs, those programs may be affected.

Script for trimming alert log to 1 day and taking backup of previous day

Script for trimming alert log to 1 day and taking backup of prev day

#!/usr/bin/ksh

############################################################################
##  Program :   save_alert_log.sh                                          #
##                                                                         #
##  Purpose :   The alert logs on many Oracle databases can grow to a very #
##              large size over time.  This can often impede the maintenace#
##              of the system – because the DBA will need to sometimes scan#
##              through many days or months of data when researching an    #
##              issue.  This script tries to avoid that by ensuring that   #
##              the log file can be “refreshed” on a daily basis, meaning  #
##              that only the current day’s data will be kept in the log,  #
##              while the previous day’s data will be saved to another file#
##              in a backup area.                                          #
##                                                                         #
##              This script should be run from Oracle’s crontab at midnight#
##              every night, so that the database will always have a new   #
##              alert log file each day.  An example crontab entry could be#
##              0 00 * * * /oracle/product/local/scripts/save_alert.sh 2>&1#
##                                                                         #
##  Date    :   19 May 2006.                                               #
##  Author  :   Basil S. Mullings                                          #
############################################################################
##  Modified:                                                              #
##                                                                         #
##                                                                         #
#  Modification History:                                                   #
#  DATE       WHO      DESC                                                #
#  ——–   —–    —————————————————-#
#  05/29/06   Basil    Add an extra variable LOG_KEEP_DAYS to hold the     #
#                      number of days that the log files should be kept on #
##                     the server before being deleted.                    #
##                                                                         #
##                                                                         #
############################################################################

##Setup some needed variables.
BKUP=bkup   ##The backup directory to store the logs…
ORATAB=”/etc/oratab”
LOG_KEEP_DAYS=365   ##Keep this many days of log files on the server.
TMPFILE=/var/tmp/OracleAlertLog   ##Just a temp scratch work area.
SQLUSER=”/ as sysdba”
GEN_ORA_ERROR=”ORA\-[0-9][0-9]*”
PATH=”$HOME:$HOME/bin:/usr/contrib/bin:/usr/local/bin:/usr/bin:/bin:/etc:.”
export PATH

## Now, parse the oratab file for all databases on the system.
## Then use the ORACLE_SID that is found in the oratab file
## to log onto that database, and retrieve the directory where
## the alter log file is stored (.ie. retrieve the path to the
## bdump directory.
##
#for sidEntry in `cat $ORATAB | grep -v “^#”`
for sidEntry in `cat $ORATAB | awk -F: ‘{print $1}’ | grep -v “^#”`
do
## Get date and time
CURR_DATE=`date ‘+%a_%m%d%H%M’`    ##Example Fri_05191256   for Friday May 19th @1256 PM.

#ORACLE_SID=`echo  $sidEntry | cut -f 1 -d :`
ORACLE_SID=$sidEntry
echo “Oracle Sid is $ORACLE_SID”

export ORACLE_SID
## Set the Oracle environment for this SID.
ORAENV_ASK=NO
. /usr/local/bin/oraenv
rm -f $TMPFILE > /dev/null 2>&1

##Now, let’s log onto the DB, and try to
##retrieve the bdump directory path.
sqlplus -s /nolog << EOF > $TMPFILE
connect $SQLUSER
set heading off;
set echo off;
set feedback off;

select ‘BACKGROUND_DUMP_DEST=’ ||value
from   v\$parameter
where  name=’background_dump_dest’;
exit;
EOF

##Ok, we had a problem talking to the database.
if [ `grep -c $GEN_ORA_ERROR $TMPFILE` -ne 0 ]
then
echo “ERROR: Unable to find the path to the alert log for DB $ORACLE_SID”
rm -f $TMPFILE > /dev/null 2>&1

else  ##Ok, we can log into the DB, now let’s go find our bdump directory.

bdump=`grep BACKGROUND_DUMP_DEST $TMPFILE | awk -F “=” ‘{print $2}’`
#echo “BDUMP is $bdump”
bkupDir=$bdump/$BKUP

##Make sure our backup directory exists.
if [ ! -d $bkupDir ]
then
mkdir $bkupDir  > /dev/null 2>&1
fi

##Now, move the alert log.
#echo “now moving $bdump/alert_${ORACLE_SID}.log to $bkupDir/alert_${ORACLE_SID}.$CURR_DATE”
mv $bdump/alert_${ORACLE_SID}.log  $bkupDir/alert_${ORACLE_SID}.$CURR_DATE

#Procedure to shrink the log to 365 days
##Keep only the last 365 days worth of logs…delete all logs older than 365 days.
#echo “Now shrinking the logs in dir $bkupDir …”
find $bkupDir  -name “*.*” -mtime +${LOG_KEEP_DAYS} -exec rm -f {} \;
fi

done

Top 99 Responsibilities of a DBA


Top 99 Responsibilities of a DBA

Database Architecture Duties

1. Planning for the database's future storage requirements
2. Defining database availability and fault management architecture
3. Defining and creating environments for development and new release installation
4. Creating physical database storage structures after developers have designed an application
5. Constructing the database
6. Determining and setting the size and physical locations of datafiles
7. Evaluating new hardware and software purchase
8. Researching, testing, and recommending tools for Oracle development, modeling, database administration, and backup and recovery implementation, as well as planning for the future
9. Providing database design and implementation
10. Understanding and employing the optimal flexible architecture to ease administration, allow flexibility in managing I/O, and to increase the capability to scale the system
11. Working with application developers to determine and define proper partitioning

Backup and Recovery


12. Determining and implementing the backup/recovery plan for each database while in development and as the application moves through test and onto production
13. Establishing and maintaining sound backup and recovery policies and procedures
14. Having knowledge and practice of Oracle backup and recovery scenarios
15. Performing Oracle cold backups when the database is shut down to ensure consistency of the data
16. Performing Oracle hot backups while the database is operational
17. Performing Oracle import/export as a method of recovering data or individual objects
18. Providing retention of data to satisfy legal responsibilities of the company
19. Restoring database services for disaster recovery
20. Recovering the database in the event of a hardware or software failure
21. Using partitioning and transportable tablespaces to reduce downtime, when appropriate

Maintenance and Daily Tasks

22. Providing adjustment and configuration management of INIT.ORA
23. Adjusting extent size of rapidly growing tables and indexes
24. Administering database-management software and related utilities
25. Automating database startup and shutdown
26. Automating repetitive operations
27. Determining and setting critical thresholds for disk, tablespaces, extents, and fragmentation
28. Enrolling new users while maintaining system security
29. Filtering database alarm and alert information
30. Installing, configuring, and upgrading Oracle server software and related products installation
31. Logging Technical Action Reports (TARs); applying patches
32. Maintaining the "Database Administrator's Handbook"
33. Maintaining an ongoing configuration for database links to other databases
34. Maintaining archived Oracle data
35. Managing contractual agreements with providers of database-management software
36. Managing service level agreements with Oracle consultants or vendors
37. Monitoring and advising management on licensing issues while ensuring compliance with Oracle license agreements
38. Monitoring and coordinating the update of the database recovery plan with the site's disaster recovery plan
39. Monitoring and optimizing the performance of the database
40. Monitoring rollback segment and temporary tablespace use
41. Monitoring the status of database instances
42. Performing housekeeping tasks as required; purging old files from the Oracle database
43. Performing database troubleshooting
44. Performing modifications of the database structure from information provided by application developers
45. Performing monthly and annual performance reports for trend analysis and capacity planning
46. Installing new and maintaining existing client configurations
47. Performing ongoing configuration management
48. Performing ongoing Oracle security management
49. Performing routine audits of user and developer accounts
50. Performing translation of developer modeled designs for managing data into physical implementation
51. Performing correlation of database errors, alerts, and events
52. Planning and coordinating the testing of the new database, software, and application releases
53. Providing a focal point on calls to Oracle for technical support
54. Working as part of a team and providing 24x7 support when required

Methodology and Business Process


55. Coordinating and executing database upgrades
56. Coordinating upgrades of system software products to resolve any Oracle/operating system issues/conflicts
57. Creating error and alert processes and procedures
58. Creating standard entry formats for SQLNet files
59. Creating processes and procedures for functional and stress testing of database applications
60. Creating processes and procedures of application transport from DEV, to TEST, to PROD
61. Defining and maintaining database standards for the organization to ensure consistency in database creation
62. Defining database standards and procedures to cover the instance parameters, object sizing, storage, and naming. The procedures define the process for install/upgrade, corporate database requirements, security, backup/recovery, applications environment, source code control, change control, naming conventions, and table/index creation.
63. Defining the database service levels necessary for application availability
64. Defining methodology tasks for database software integration
65. Defining a methodology for developing and improving business applications
66. Creating a process to determine whether a new release is "stable" enough to be placed on the development system
67. Developing data-conversion processes for customization, testing, and production
68. Developing database test plans
69. Developing database administration procedures and responsibilities for production systems
70. Developing production migration procedures
71. Establishing and providing schema definitions, as well as tablespace, table, constraint, trigger, package, procedure, and index naming conventions
72. Facilitating design sessions for requirements gathering and defining system requirements
73. Providing database problem reporting, management, and resolution
74. Providing final approval for all technical architecture components that manage and exchange data, including database management software, serve hardware, data distribution management software, server hardware, data distribution management software, transaction processing monitors, and connecting client applications software
75. Providing processes for the setup of new database environments
76. Providing risk and impact analysis of maintenance or new releases of code
77. Providing standards and methods for database software purchasing
78. Providing standards and naming conventions
79. Handling multiple projects and deadlines

Education and Training

80. Attending training classes and user group conferences
81. Evaluating Oracle features and Oracle-related products
82. Understanding the Oracle database, related utilities, and tools
83. Understanding the underlying operating system as well as the design of the physical database
84. Understanding Oracle data integrity
85. Knowing the organization's applications and how they map to the business requirements
86. Knowing how Oracle acquires and manages resources
87. Knowing enough about the Oracle tool's normal functional behavior to be able to determine whether a problem lies with the tool or the database
88. Processing sound knowledge in database and system performance tuning
89. Providing in-house technical consulting and training
90. Staying abreast of the most current release of Oracle software and compatibility issues
91. Subscribing to database trade journals and web sources

Communication

92. Interfacing with vendors
93. Disseminating Oracle information to the developers, users, and staff
94. Training application developers to understand and use Oracle concepts, techniques, and tools that model and access managed data
95. Assisting developers with database design issues and problem resolutions, including how to run and understand the output from both TKProf and the Explain Plan utilities
96. Training interim DBAs and junior-level DBAs

Documentation

97. Creating and maintaining a database operations handbook for frequently performed tasks
98. Defining standards for database documentation
99. Creating documentation of the database environment

Source :  Link

LDT Commands FNDLOAD Scripts

  • Messages :

> Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
XX_MESSAGE_NAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’ICX’ MESSAGE_NAME=’XX_MESSAGE_NAME’

To download all the messages within an application

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
XX_MESSAGE_NAME.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’ICX’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_MESSAGE_NAME.ldt


  • Lookups:

> Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_LKP_NAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=’ICX’ LOOKUP_TYPE=’XX_LKP_NAME’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_LKP_NAME.ldt

  • Value Set :

> Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_VALUE_SET_NAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=’XX_VALUE_SET_NAME’

If value set has values then,

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_VALUE_SET_NAME.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=’XX_VALUE_SET_NAME’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_VALUE_SET_NAME.ldt

  • Form Function:

> Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FORM_FUNCTION_NAME.ldt FUNCTION FUNCTION_NAME=’XX_FORM_FUNCTION_NAME’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_FORM_FUNCTION_NAME.ldt

  • Menu:

> Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_MENU_NAME.ldt MENU MENU_NAME=’XX_MENU_NAME’

> Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_MENU_NAME.ldt

  • Responsibility:

> Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESP_KEY.ldt FND_RESPONSIBILITY RESP_KEY=’XX_RESP_KEY’

> Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESP_KEY.ldt
Profile Option Definition:
> Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_PROFILE_NAME.ldt PROFILE PROFILE_NAME=’XX_PROFILE_NAME’ APPLICATION_SHORT_NAME=’ICX’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_PROFILE_NAME.ldt

  • Request Group :

> Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=’XX_REPORT_GROUP_NAME’ APPLICATION_SHORT_NAME=’PO’

> Upload

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REPORT_GROUP_NAME.ldt

  • Concurrent program:

> Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME=’PO’ CONCURRENT_PROGRAM_NAME=’XX_CONC_PRG_NAME’

> Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CONC_PRG_NAME.ldt

  • Request Sets :

> Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME.ldt REQ_SET REQUEST_SET_NAME=’XX_REQ_SET_NAME’

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME=’XX_REQ_SET_NAME’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME.ldt
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_REQ_SET_NAME_LINK.ldt

  • Alert :

> Download
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT_NAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=’PO’ ALERT_NAME=’XX_ALERT_NAME’

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_ALERT_NAME.ldt CUSTOM_MODE=FORCE

  • Folders :

> Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct XX_FOLDER_NAME.ldt FND_FOLDERS NAME=’XX_FOLDER_NAME’

Or can download all the folders

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct XX_ALL_FOLDER.ldt FND_FOLDERS

> Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/fndfold.lct XX_FOLDER_NAME.ldt CUSTOM_MODE=FORCE

Customize Oracle E-Business Suite R12 Login Page

Customize Oracle E-Business Suite R12 Login Page

There are 5 areas on the login page that can be customized :-


FRONT SCREEN




Image No.
Image Name
Actual Image File Name
Directory
Actual Image Size
New Image Size / Type
1
Image (Oracle logo)
FNDSSCORP.gif
$OA_MEDIA
155 X 20  Pixels
Same
2
Image (people image)
people.jpg
$OA_MEDIA
417 X 54  Pixels
Same
3
Image (above globe)
topLines.gif
$OA_MEDIA
352 X 54  Pixels
Same
4
Image (globe)
globalTop.jpg
$OA_MEDIA
351 X 180 Pixels
Same
5
Image (below globe)
global.jpg
$OA_MEDIA
352 X 79  Pixels
Same


Login to Applications as System Administrator.
Select: System Administrator > Profile - System.
Query profile "Corporate Branding Image for Oracle Applications".
Change the SITE level value to the name of the custom image file (e.g. my_company_logo.gif).
Save the change.
The second action is performed on the Application server.


To customize the above regions, login to Oracle Applications as SYSADMIN
Switch to Functional Administrator responsibility then navigate to "Personalization" tab, and in the "Document Path" field enter:
'/oracle/apps/fnd/sso/login/webui/MainLoginPG' then click "Go".

In the results table, click "Personalize Page" icon, ensure the "Site" checkbox is selected then Apply.
In the "Personalization Structure" page region, click "Expand All".
The page contains the list of objects marked in the screen grab above:
1. Image (Oracle logo) /OA_MEDIA/FNDSSCORP.gif
2. Image (people image) people.jpg
3. Image (above globe) topLines.gif
4. Image (globe) globalTop.jpg
5. Image (below globe) global.jpg

Above images are located in $OA_MEDIA directory.

> To customize the login page, create own versions of the images, and save them in $OA_MEDIA directory with file names prefixed with client's initials, e.g. JAGFNDSSCORP.gif 
> To customize the regions, click the pencil icon in the corresponding region the "Personalize" column for the image.  Locate the "Image URI" attribute, then type the new image name in the "Site" column then Apply.  

Repeat the process for the remaining images.


Remove the following cache
rm -Rf $OA_HTML/_pages/*
rm -Rf $COMMON_TOP/_pages/*
rm -Rf $IAS_ORACLE_HOME/Apache/modplsql/cache/*

Disable Load Balancer Cache/Web acceleration if applicable
Compile all the JSPs

$FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

Start Services

MOS: 579917.1 - How to Personalize Login page in R12?
MOS: 741459.1 - Tips For Personalizing The E-Business Suite r12 Login Page (MainLoginPG)
MOS: 473539.1 - How to Replace the Globe and People Images of the Release 12 Login page
MOS: 468971.1 - Tips For Personalizing The E-Business Suite 11i Login Page (AppsLocalLogin)