Below is a list of issues (ORA- and PLS- errors) I encountered after upgrading Oracle 11gR2 Database Enterprise Servers to the latest Patchset 220.127.116.11.
The upgrade mostly done:
– from 18.104.22.168.6-8
– to 22.214.171.124.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.
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 126.96.36.199 since developers happily used an Oracle Bug that was fixed in 188.8.131.52. So the correct behavior is on 184.108.40.206 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 »
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 »
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.
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 »