Oracle PatchSet 11.2.0.4 upgrade issues ORA- PLS-

Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset 11.2.0.4.
The upgrade mostly done:
– from 11.2.0.3.6-8
– to 11.2.0.4.1 (including Patchset Update PSU 1)
– on platforms: HP-UX Itanium, Linux

ORA-01979: missing or invalid password for role

Problem: Roles with passwords are failing after Oracle database Patchset upgrade

Solution: Recreate a role with a new password or without it.
Example:
alter role REPORTING_RW not identified;

ORA-01791: not a SELECTed expression

Problem: SQL having different number of columns in DISTINCT and ORDER BY clauses failing after Oracle database upgrade but it was working on previous release before last Patchset 11.2.0.4 since developers happily used an Oracle Bug that was fixed in 11.2.0.4. So the correct behavior is on 11.2.0.4 and not on older versions.
SQL> select distinct sal, empno from scott.emp order by deptno;
select distinct sal, empno from scott.emp order by deptno
*
ERROR at line 1:
ORA-01791: not a SELECTed expression

Solution: include all ORDER BY columns into Read more »

Enable Oracle database to send emails via SMTP server

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

2. Check SMTP access of database Linux server on mail server

– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

$ telnet smtp_server 25

If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:

helo
mail from: my_email@my_company.com # you should see "Sender OK'
rcpt to: my_email@my_company.com   # you should see "Recipient OK"
data                               # Start mail input
test email via SMTP and orcl DB
[Enter]
.                                  # mail should be sent
[Enter]
quit

3. Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example: Read more »

How to troubleshoot Oracle remote database connection

Many Oracle DBAs, developers and just end-users often encounter an issue when they can not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. But first let’s explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames

Oracle NET Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and hands these requests to the database server. Once the connection is established, the client and database server communicate directly. Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email