Display the message in the status bar - Oracle Forms

Display the message in the status bar - Oracle Forms

To display the message in the status bar in Oracle Forms below mentioned command can be used,
Message('Message you want to display',NO_ACKNOWLEDGE);

Excel Unmerge Cells and copy the values to each cells

This macro will search every cell in the active sheet to see if they are merged. If they are, it stores the range of the merged cells in a temp. range variable, unmerges the cells, then fills the range with the value of the first cell in the unmerged range

Sub UnMergeFill()
Dim cell As Range, joinedCells As Range
Application.ScreenUpdating = False
For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next

Application.ScreenUpdating = True
End Sub

Resizing or Recreating Online Redo Log Files

Online redo logs are sized too small causing database performance problems.

I had 11MB logfiles in each group.

I decided to increase the redo log file size to 100MB

The redo logs must be dropped and recreated for changing the redo log size. It can be done without shutdown the database. Redo group status must be INACTIVE before dropping.

There is a basic requirement in Oracle database that there should always be a minimum 2 redo log groups available with the database. So we can't drop any redo groups if there  are only 2 groups.

First we will see the current redo log files status
$sqlplus / as sysdba

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 CURRENT
         3   10485760 ACTIVE

To convert an group status to inactive , execute the checkpoint

SQL> alter system checkpoint;

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 CURRENT
         3   10485760 INACTIVE

The group 3 is inactive
Drop the 3rd group file using the following command

SQL> alter database drop logfile group 3;
Database altered

Re-Create dropped online redo log group

SQL> alter database add logfile group 3 (
'/u01/app/oracle/PROD/db/app_sts/PROD/log3a.dbf','/u01/app/oracle/PROD/db/app_sts/PROD/log3b.dbf''/u01/app/oracle/PROD/db/app_sts/PROD/log3c.dbf') size 512M
SQL> select group#,bytes,status from v$log;

 GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 ACTIVE
         3   104857600 UNUSED

After adding the new log file, it status should become current. using the following command
SQL> alter system switch logfile;

SQL> select group#,bytes,status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- ----------------
         1   10485760 ACTIVE
         2   10485760 ACTIVE
         3   104857600 CURRENT



Repeat the same steps for other 2 log files.

Revoke DBMS_JAVA permissions

Revoke dbms_java permission

Query the dba_java_policy table

$sqlplus / as sysdba
SQL>select * from  dba_java_policy

KING      GRANTEE TYPE SCHEMA TYPE NAME                NAME ACTION ENABLED    SEQ

GRANT XXPMS    SYS                java.io.FilePermission   *   read ENABLED 178
GRANT XXPMS   SYS                java.io.FilePermission   /-   read,write  ENABLED 181
GRANT XXPMS   SYS                java.io.FilePermission       /bin/ls   execute ENABLED 180



Using the SEQ value, you can run the following statement to revoke the granted java permissions

eg: suppose you want to revoke SEQ 178

begin
  DBMS_JAVA.disable_permission(178);
  DBMS_JAVA.delete_permission(178);
end;

ERROR: While GATHER_TABLE_STATS: ***ORA-20005: object statistics are locked (stattype = ALL)***

ERROR: While GATHER_TABLE_STATS: ***ORA-20005: object statistics are locked (stattype = ALL)***

Connect as a dba user and use the following statement to unlock the statistics:

sqlplus / as sysdba or sqlplus system/****

SQL>EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('SCOTT');

SQL>exec dbms_stats.gather_schema_stats('SCOTT',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

Error APP-FF-33980: Internal error attempting to generate package

Error "APP-FF-33980: Internal error attempting to generate package". While saving the fast formula.

How to diagnosis:
Compile the fast formula using FFXBCP command
go to
$cd $FF_TOP/bin
$FFXBCP apps/apps 0 Y -k %% 'ENTER_THE_NAME_OF_THE_FAST_FORMUALA'

it will generate 2 files one .log and .out

Please check the log file to see the package name
Connect using
$sqlplus apps/apps
SQL> alter package packagename compile body;
SQL> show error

fix the error and compile the fast formula

Calculating Employee Service years

Below script return Number of years, Months and days of an employee

Select ppos.person_id,
trunc(months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start )/12) ||' Years '||
       TO_CHAR (FLOOR(MOD (months_between(coalesce(ppos.projected_termination_date,sysdate),ppos.date_start ), 12)) ) ||' Months '||
       (To_Date (coalesce(ppos.projected_termination_date,sysdate))-Add_Months (ppos.date_start,Trunc (Months_Between (coalesce(ppos.projected_termination_date,sysdate), ppos.date_start)) ) )      ||' Days ' yearOfService
      
From   per_periods_of_service ppos

Flashback version Between query in oracle

Flashback version between query used to track a table row in a specified time period

Syntax: VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound];

eg:
SELECT * FROM wf_notifications
VERSIONS  BETWEEN TIMESTAMP TO_TIMESTAMP('15-AUG-2012 08:13:07')
                                      AND TO_TIMESTAMP('15-AUG-2012 12:01:17')

        Where message_type = 'XXXX'

                                 

HR: Create Person Extra infromation using API

Following HR (11i) api can be used for creating person extra information, below example show you how to insert a employee passport details into extra information tables using api.

Procedure create_pp_details(p_pp_country In Varchar2 -- passport issuing country
                            ,p_pp_number  In Varchar2 -- passport number
                            ,p_pp_issue_date In date -- passport issue date
                            ,p_pp_expiry_date In Date -- passport expiry date
                            ,p_pp_issue_place In Varchar2 -- passport issue place
                            ,p_person_id In Number
                            )

Is
  l_person_extra_info_id  Number;
   l_object_version_number Number;
 

Begin
 

 hr_person_extra_info_api.create_person_extra_info
    (p_validate                    => false
    ,p_person_id                   => p_person_id     -- employee person id
    ,p_information_type            => 'PASSPORT_DETAILS' -- this is same as your extra person information flex filed code.
    ,p_pei_attribute_category      => null
    ,p_pei_information_category    => 'PASSPORT_DETAILS' -- this is same as your extra person information flex filed code.
    ,p_pei_information1            =>  p_pp_country
    ,p_pei_information2            => p_pp_number
    ,p_pei_information3            => to_char(p_pp_issue_date,'RRRR/MM/DD HH:MI:SS')
    ,p_pei_information4            => to_char(p_pp_expiry_date,'RRRR/MM/DD HH:MI:SS')
    ,p_pei_information5            => p_pp_issue_place
    ,p_person_extra_info_id        => l_person_extra_info_id  -- out values
    ,p_object_version_number       => l_object_version_number -- out values

    );
 commit;
end;

ORA-01927: cannot REVOKE privileges you did not grant

Means you didn't grant to the user.

use the follwing dba table to find out details of grant privleges

dba_tab_privs

Insert %rowtype variable directly into a table

Can I insert a %rowtype variable directly into table without specifying each column?
Ans: Yes

 Normally we are putting open and closed brackets after values. Remove these brackets in the case %rowtype variable.

Syntax:
Insert into << values >><<%rowtype variable>>

eg: 

Procedure create_emp(p_emp_rec In emp%rowtype)
Is
Begin
insert into emp values p_emp_rec;
End create_emp;

Oracle RMAN Database Backup Failed: Error: "error writing input to command"

Oracle RMAN Database Backup Failed: Error: "error writing input to command"


Solution:

Set the permission to 6750 (nmo & nmb) 
$chmod 6750 nmo
$chmod 6750 nmb

Oracle RMAN Database Backup Failed: "ERROR: NMO not setuid-root"

Oracle RMAN Database Backup Failed:   "ERROR: NMO not setuid-root"

Solution:
Change the ownership of the following files to root.
nmo & nmb
$cd $ORACLE_HOME/bin
$chown root nmo
$chown root nmb 

Unable to connect to the Discoverer Server: null..

Unable to connect to the Discoverer Server (10g or Higher):


Could not initialize class

oracle.disco.model.xml.ModelElementToClassMapper

Please contact your Oracle Application Server administrator
 
Solution:
Clear the temporary internet files and cookies and java cache


Steps to clear java cache:



1.Go to Start > Settings > Control Panel and click the Java icon.

2. Click on 'Settings'
3. Delete files

4. Close all browsers

5.Open new browser and test it
 
OR 
close all browsers and delete all files from "C:\Users\<>\AppData\LocalLow\Sun\Java\Deployment\cache".
 
 
(<> replace this with your windows login name)

DIP_GEN_AUTHENTICATION_FAILURE

DIP_GEN_AUTHENTICATION_FAILURE (Third party ldap is using Microsft ADS)

Profile:Error in Mapping EngineODIException: DIP_GEN_AUTHENTICATION_FAILURE


javax.naming.AuthenticationException: [LDAP: error code 49 - 80090308: LdapErr: DSID-0C090334, comment: AcceptSecurityContext error, data 532, vece^@]

at com.sun.jndi.ldap.LdapCtx.mapErrorCode(LdapCtx.java:2988)

at com.sun.jndi.ldap.LdapCtx.processReturnCode(LdapCtx.java:2934)

at com.sun.jndi.ldap.LdapCtx.processReturnCode(LdapCtx.java:2735)

at com.sun.jndi.ldap.LdapCtx.connect(LdapCtx.java:2649)

at com.sun.jndi.ldap.LdapCtx.(LdapCtx.java:290)

at com.sun.jndi.ldap.LdapCtxFactory.getUsingURL(LdapCtxFactory.java:175)

at com.sun.jndi.ldap.LdapCtxFactory.getUsingURLs(LdapCtxFactory.java:193)

at com.sun.jndi.ldap.LdapCtxFactory.getLdapCtxInstance(LdapCtxFactory.java:136)

at com.sun.jndi.ldap.LdapCtxFactory.getInitialContext(LdapCtxFactory.java:66)

at javax.naming.spi.NamingManager.getInitialContext(NamingManager.java:662)

at javax.naming.InitialContext.getDefaultInitCtx(InitialContext.java:243)

at javax.naming.InitialContext.init(InitialContext.java:219)

at javax.naming.ldap.InitialLdapContext.(InitialLdapContext.java:133)

at oracle.ldap.odip.gsi.LDAPConnector.connectLdap(LDAPConnector.java:318)

at oracle.ldap.odip.gsi.ActiveChgReader.initialise(ActiveChgReader.java:209)

at oracle.ldap.odip.engine.AgentThread.readerInitialise(AgentThread.java:461)

at oracle.ldap.odip.engine.AgentThread.mapInitialise(AgentThread.java:510)

at oracle.ldap.odip.engine.AgentThread.execMapping(AgentThread.java:376)

at oracle.ldap.odip.engine.AgentThread.run(AgentThread.java:238)

Solution:
Unlock the microsoft Active directory sever referenced username and verify the synchronization is working or not.

Unusable State of Index

Unusable State of Index


Unusable state of indexes will cause poor performance of database.

How to find Unusable Index:
select Index_name from dba_indexes where status='UNUSABLE'

How to make index Usable:
alter index  <index name> rebuild

How to create above alter statement dynamically:
select  'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE'

Verify checksum on R12 Media

Verify checksum on R12 Media

Linux:
md5sum utility already installed on your machine , if not available please install it.
go to the staging folder
eg: $cd /prodtmp/Stage12
      $md5sum --check md5sum_Linux32.txt > md5sum_result.txt

When the md5sum command failed error: "Failed open or read" try to make it a unix file using:
$dos2unix <checksum file> <cheksum file>

Oracle R12 media check sum txt file can be downloaded from metalink link

Oracle EBusiness Suite R12.1.x Rapid Installmd5 Checksums[802195.1]

How to kill an oracle session

How to kill an oracle session

conn as / sysdba or any dba user.

SQL> Alter system kill session  'sid,serial#' immediate;

OS level:


Windows:
orakill ORACLE_SID spid


The UNIX /Linux :
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.

find out running oracle process
$ps -ef | grep ora

$kill -9 unix/linux process id

How to find a locked Objects in database

How to find a locked Objects in database

Run the following query from dba user

select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id

adpreclone 0% completed Error

adpreclone 0% completed Error

Below error is started after upgrading to 11.2.0.3 database

perl adpreclone.pl dbTier
Running Rapid Clone with command...
perl /u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/bin/adclone.pl java=/u01/app/oracle/PROD/db/tech_st/11.2.0/jdk mode=stage stage=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/PROD_bhmanapp09r12.xml showProgress
Beginning database tier Stage - Mon Apr 23 10:20:23 2012
APPS Password : apps
Log file located at /u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/log/PROD_bhmanapp09r12/StageDBTier_04231020.log
- 0% completed
ERROR while running Stage...
ERROR while running perl /u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/bin/adclone.pl java=/u01/app/oracle/PROD/db/tech_st/11.2.0/jdk mode=stage stage=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/clone component=dbTier method=CUSTOM dbctx=/u01/app/oracle/PROD/db/tech_st/11.2.0/appsutil/PROD_bhmanapp09r12.xml showProgress ...

There was no error meessage in log file.

Solution:

Set the following debug parameters  (Linux environment)
TIMEDPROCESS_DEBUG=YES
export TIMEDPROCESS_DEBUG

Re-run the adpreclone. now you can view the error message in log file.

Zip 3.0 is used (delivered with 11g) instead of the 2.3 which has been delivered with 10g.

modify the PATH and so that zip2.3 was in the first.

to make this chnage permenant , edit the $ORACLE_HOME/.env file PATH
Re-run perl adpreclone dbTier.

Linux Network bonding

Linux Network bonding

Linux network Bonding is creation of a single bonded interface by combining 2 or more Ethernet interfaces. This helps in high availability of your network interface and offers performance improvement. Bonding is same as port trunking or teaming.

Steps for bonding in Redhat Enterprise Linux are as follows.. 


Step 1.

Create the file ifcfg-bond0 with the IP address, netmask and gateway. Shown below is my test bonding config file.

$ cat /etc/sysconfig/network-scripts/ifcfg-bond0

DEVICE=bond0
IPADDR=10.183.90.86
GATEWAY=10.183.90.1
NETMASK=255.255.255.0
NETWROK=10.183.90.0
USERCTL=no
BOOTPROTO=none
ONBOOT=yes




Step 2.

Modify eth0 and eth1  configuration as shown below. Comment out, or remove the ip address, netmask, gateway and hardware address from each one of these files, since settings should only come from the ifcfg-bond0 file above. Make sure you add the MASTER and SLAVE configuration in these files.

$ cat /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE=eth0
BOOTPROTO=none
HWADDR=00:1C:C4:96:21:60
ONBOOT=yes
# setting for Bond
MASTER=bond0
SLAVE=yes


$ cat /etc/sysconfig/network-scripts/ifcfg-eth1

DEVICE=eth1
BOOTPROTO=none
HWADDR=00:1C:C4:96:21:62
ONBOOT=yes
MASTER=bond0
SLAVE=yes

Step 3.

Set the parameters for bond0 bonding kernel module. Select the network bonding mode based on you need, documented at http://unixfoo.blogspot.com/2008/02/network-bonding-part-ii-modes-of.html. The modes are

·       mode=0 (Balance Round Robin)

·       mode=1 (Active backup)

·       mode=2 (Balance XOR)

·       mode=3 (Broadcast)

·       mode=4 (802.3ad)

·       mode=5 (Balance TLB)

·       mode=6 (Balance ALB)

Add the following lines to /etc/modprobe.conf

# bonding commands
alias bond0 bonding
options bond0 mode=1 miimon=100 


Step 4.

Load the bond driver module from the command prompt.

$ modprobe bonding


Step 5.

Restart the network, or restart the computer.

$ service network restart # Or restart computer

When the machine boots up check the proc settings.

$ cat /proc/net/bonding/bond0 

Look at ifconfig -a and check that your bond0 interface is active. 

To verify whether the failover bonding works.. 

•   Do an ifdown eth0 and check /proc/net/bonding/bond0 and check the “Current Active slave”. 
•    Do a continuous ping to the bond0 ipaddress from a different machine and do a ifdown the active interface. The ping should not break.

 

Number of files in a folder

Number of files in a folder

find . -type  f| wc -l

Number of Subfolders in a folder
find . -type d | wc -l

Number of folders in current folder then

find . -maxdepth 1 -type d | wc -l

Rename a linux mount point

Unmount the existing mount point (that you want to rename) :
#umount /prodapp




Change the label on the filesystem to the new mount point name:
#tune2fs -L /u01 /dev/sdb2

add the entries to /etc/fstab:
replace the entries of /prodapp with new mount point

Create the mount point named /u01
mkdir /u01

mount the new folder /u01:
mount  /u01

remove the folder /prodapp:
rmdir -rf /prodapp

Update etc_fstab file while linux started with repair mode

Use following command to mount the filesystem with writable permission:

Repair filesystem # mount -w -o remount /

After this you can go and change /etc/fstab file. Restart your computer and that’s it.

The Macro Cannot be Found or Has Been Disabled (BI Publsiher)

The Macro Cannot be Found or Has Been Disabled (BI Publsiher)

 Error BI Publisher macros in Word : "The macro cannot be found or has been disabled because of your macro security settings"


Solution:
Close word application
Find out the file named MSComctlLib.exd
Backup the existing MSComctlLib.exd file
This file can be regenerated when you re-open the word.

Mount Remote Windows Folder with RHEL 5 Linux

Login into linux using root user

Create the required mount point in linux
#mkdir -p /mnt/win_folder   
mount the remote folder
#mount -t cifs //windows_machine_ip_or_name//shared_folder -o username=test,password=test123 /mnt/win_folder

-t cifs - file system type to be mount
-o : are options passed to mount command, here i have passed 2 options username and password
//windows_machine_ip_or_name//shared_folder - windows/shared folder
 /mnt/win_folder - linux mount point

Configure the system to automount while starting

add entry into /etc/fstab
Open file /etc/fstab using vi text editor:# vi /etc/fstabAppend line
//windows_machine_ip_or_name//shared_folder /mnt/win_folder cifs username=test,password=test123 0 0

How to Create Oracle Worflow Business Events

How to Create Oracle Worflow Business Events

Purpose: Below I am going to show you how we can use a business event to execute custom plsql function.

Go to system Administrator Responsiblity => Worflow Administrator

Select Business Events Tab

Click Create Event Button



    Enter the required values.
    Click Apply
Create Event Subscription by clicking Event Subscription

       

Click on Create Subscription button

System will be the name of your enviroment instance , selection available

Action type custom

Click Next

Create a PL/SQL function  that will be execute when the event is raised

Function ts_cancel_event(p_subscription_guid In RAW

,p_event In Out NoCopy Wf_Event_t
)
Return Varchar2
Is
l_doc_id Number;
Begin
l_doc_id := p_event.getValueForParameter('XXPM_DOC_ID');
-- your own logic example like inserting data to table or some action
----
----
 RETURN 'SUCCESS';
End ts_cancel_event;

getValueForParameter is used to retrieve parameter value from event.
Attach the procedure to Event Action window

Enter all required values and Click Apply

Next step is how to raise the event:

Sample code is below

Procedure raise_approval_cancel_event(p_doc_id In Number)

Is
l_event_param_list wf_parameter_list_t;
l_param_table wf_parameter_t;
l_index Number;
Begin
l_event_param_list := wf_parameter_list_t();
-- lets add the value to event parameters
l_param_table := wf_parameter_t(Null,Null);
l_event_param_list.Extend;
l_param_table .setName('XXPM_DOC_ID');
l_param_table .setValue(p_doc_id);
l_index := coalesce(l_index ,0) + 1;
l_event_param_list (l_index ) := l_param_table ;
wf_event.RAISE(p_event_name => 'sat.oracle.apps.xxpm.ts.cancelApprovalProcess'
,p_event_key => 'XXPMSTS'||to_char(p_doc_id)
,p_parameters => l_event_param_list
 );
ENd raise_approval_cancel_event;