How to Change Default Port of Oracle Database Listener

How to Change Default Port of Oracle Database Listener       

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle Database 11gR2

           

Description:

I want to change the default port of Oracle Database Listener.

Solution:

            To change the default port of Oracle Database Listener do the following steps:

1.    Stop Oracle Database Listener

#   lsnrctl stop

2.    Open $ORACLE_HOME/network/admin/listener.ora file and change the port from default 1521 to 1530 or as you wish

3.    Open $ORACLE_HOME/network/admin/tnsnames.ora file and change the port from default 1521 to 1530 or as you wish to change

4.    Re-start the listener

# lsnrctl start

5.    Login to Database as sysdba

# sqlplus / as sysdba

SQL> alter system set local_listener=”(ADDRESS=(PROTOCOL=TCP) (HOST=localhost) (PORT=1530))”;

SQL> alter system register;

Exit;

6.    lsnrctl stop

7.    lsnrctl start

Oracle Database Listener Port has been changed from 1521 to 1530

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

ORA-39213 Metadata processing is not available

ORA-39213 Metadata processing is not available

           

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle Database 11gR2

           

Description:

Expdp utility going to failed with following error:

ORA-39213: Metadata processing is not available

After some research I found that it is caused by the XSL stylesheets not setup/load properly.

Solution:

To fix the problem do the following steps:

Connect AS SYSDBA and execute the following to reload the stylesheets:

SQL> execute dbms_metadata_util.load_stylesheets;

Re-test the expdp, now it is working fine.

           

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

APPS Login Failed with error HTTP 404

APPS Login Failed with error HTTP 404

           

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle EBS R12.1

           

Description:

Oracle EBS Login page going to fail with error “The webpage cannot be found HTTP 404”. I have restart the services but the problem is same. All services started successfully.

Error Log shows the following: $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/log.xml which reports:
Internal error raised trying to instantiate web-application: html defined in web site OC4J 10g (10.1.3) Default Web Site.

Error compiling :$COMMON_TOP/webapps/oacore/html: Error instantiating compiler: Web service artifact generation failed:java.lang.InstantiationException: IO Error reading WEB-INF/wsdl/wsrp_service.wsdl in $COMMON_TOP/webapps/oacore/html: $ORA_CONFIG_HOME/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/server-wsdl/wsrp_service.wsdl (Permission denied)

Solution:

The problem occurs because the file wsrp_service.wsdl is owned by the ‘root’ user. To fix the problem change the ownership of above file from root to <APPLMGR_USER>

·         Login from root user to change the ownership

·         Chown <APPLMGR_USER>:oinstall $ORA_CONFIG_HOME/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/server-wsdl/wsrp_service.wsdl

·        Login to user <APPLMGR_USER> and start the Middle-Tier services

·        Test the login again it will successful

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

Configure and Send EMAIL from Oracle Database

Configure and Send EMAIL from Oracle Database

           

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle Database 11gR2

           

Description:

            How to configure EMAIL from Oracle Database and Send Email from PL/SQL.

Solution:

Following the simple steps to configure EMAIL from Oracle Database:

1.    Connect Oracle Database as sysdba

2.    Install the following two packages:

SQL> @ORACLE_HOME/rdbms/admin/utlmail.sql;

SQL> @ORACLE_HOME/rdbms/admin/prvtmail.plb;

3.    To set SMTP parameter:

SQL> show parameter SMTP_OUT_SERVER;

SQL> Alter system set SMTP_OUT_SERVER=<enter your smtp server address>;

SQL> shutdown immediate;

SQL> startup;

SQL> show parameter SMTP_OUT_SERVER;

If the SMTP_OUT_SERVER parameter is set then you go to next step.

4.    Connect Oracle Database as sysdba

5.    SQL>grant execute on UTL_TCP TO <SCHEMA_NAME>;
SQL> grant execute on UTL_SMTP TO <SCHEMA_NAME>;
SQL> grant execute on UTL_MAIL TO <SCHEMA_NAME>;
SQL> grant execute on DBMS_NETWORK_ACL_ADMIN TO <SCHEMA_NAME>;

6.    Connect to schema which is granted

SQL> connect <SCHEMA_NAME>

7.    Begin
UTL_MAIL.SEND (
sender      =>  ‘dba.admin@pakistan.com’,
recipients  =>    ‘abdul.wahid@pakistan.com’,
subject    =>    ‘TEST EMAIL’,
message     =>    ‘Salam, This EMAIL is sent from Oracle Database’);
end;

/

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

Unable to Open Oracle EBS forms in Internet Explorer 11 on Windows 10

Oracle EBS Open in Internet Explorer 11 on Windows 10

           

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle EBS R12.1.3

           

Description:

            After upgrade Windows 7 to Windows 10 on client end we face the problem,                Oracle EBS login successfully but unable to open the forms.  

Solution:

            Following the steps to configure Internet Explorer 11 for Oracle EBS:

·       Open the Internet Explorer 11 and Navigate to Tools à Compatibility View Settings.  

Then add Domain name in the URL (ex: oraebs.com in vision.oraebs.com) using Add this website option.

·       Now navigate to Tools à Internet Options à Security. Select Internet and click on Custom Level button. Scroll down till end of the options and Disable the option Enable XSS filter.

·       Navigate to Tools à Internet Options à Security. Select Local Intranet and click on Sites button and then click on Advanced button. Enter the EBS URL address and click on Add button.

·       Install Java (JRE) jre-6u30-windows-x64 on client end, as it is compatible for my EBS environment and Java (JDK) version at Server end.

This solution worked for me and I am able to open EBS R12 forms in Internet Explorer version 11 on Windows 10.

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

Configure YUM Repository from installation DVD

Configure YUM Repository from installation DVD

           

Applies to:

            Oracle Enterprise Linux – Version: 7

           

Description:

            Configuring YUM on OEL 7 from installation DVD

Solution:

Following the steps to configure YUM from installation DVD:

After installation of Oracle Linux 7 configure YUM repository from installation DVD to install other RPMs.

Insert installation DVD of OEL 7 into CD/DVD ROM.

# umount /cdrom

# mount /dev/sr0 /mnt

# cd /etc/yum.repos.d/

Create a new file:

# vi rhel7dvd.repo

Add the following details:

[cdrom]

name=CDROM Repo

baseurl=file:///mnt

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Save the above file

Clear the related caches by yum clean all and subscription-manager:

# yum clean all

# subscription-manager clean

Check whether you can get the packages list from the DVD repo:

# yum list – noplugin

If no problem, you will update:

# yum update – noplugin

# yum-config-manager --disable ol7_UEKR3

# yum-config-manager --disable ol7_latest

# yum update – noplugin

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ

Monitor all DDL Statements at Database Level

Monitor All DDL Statements at Database Level

Applies to:

            Oracle Enterprise Linux – Version: 7

            Oracle Database 11gR2

Description:

            I want to track all DDL statements at Database level.

Solution:

Following the steps to track all DDL statements at Database level:

1-      First to create a Table that store DDL statements

CREATE TABLE DDL_MONITOR_DB

(

  DATE_TIME  DATE,

  DDL_EVENT  VARCHAR2(500),

  OS_USER    VARCHAR2(50),

  MACHINE    VARCHAR2(100)

)

2-      Create a Trigger at Database level to catches all DDL statements

CREATE OR REPLACE TRIGGER DDL_MONITOR

AFTER CREATE OR DROP OR TRUNCATE OR ALTER ON DATABASE

DECLARE

   V_OSUSER   V$SESSION.OSUSER%TYPE;

   V_MACHINE  V$SESSION.MACHINE%TYPE;

   V_IP   VARCHAR2(20);

   V_DATABASE V$PARAMETER.VALUE%TYPE;

BEGIN

      SELECT VALUE INTO V_DATABASE FROM v$parameter WHERE NAME = 'db_name';

                 V_OSUSER  :=SYS_CONTEXT('USERENV','OS_USER');

                 V_MACHINE :=SYS_CONTEXT('USERENV','TERMINAL');

                 V_IP      := SYS_CONTEXT('USERENV','IP_ADDRESS');

INSERT INTO DDL_MONITOR_DB (date_time, ddl_event, os_user, machine)

VALUES (SYSDATE, 'Username: (' || ora_login_user || ')   ' || 'Action: (' || ora_sysevent || ')   ' ||

'Object: (' || ora_dict_obj_owner||'.'||ora_dict_obj_name || ')   ' ||

'Type: (' || ora_dict_obj_type || ')', V_OSUSER, V_IP || ' (' || V_MACHINE || ')');

EXCEPTION WHEN OTHERS THEN NULL;

END;

/

Your comments, especially which will help us improve the functionality, will be greatly appreciatedJ