Be careful when migrating – Difference between oracle 10g and 11g

All of DBAs know about new features of Oracle Database 11g, but here will expose of the possible problems to migrate.

This note apply to Oracle Database 11g Release 2.

New Features:
ASM Fast Mirror Resync, a snapshot standby database is a fully updatable standby, Data Recover Advisor automatically diagnoses data failures, Improved Block Corruption Detection, Database Replay allows you to test the impact of system change by replaying real-world workload on the test system before it is exposed to a production.

Possibles issues to migrate oracle database 10g to 11g :

1. Problems to send mail or access HTTP/TCP connection.
Oracle Error: ORA-24247: network access denied by access control list (ACL)
The 11g database version only allows access to external functions (UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, UTL_INADDR, DBMS_LDAP) through one special explicit grant to the owner.

Solution:
1.1. Create de ACL – access control list :

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('acl_list.xml','List of special access', 'SYS', TRUE, 'connect');
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('acl_list.xml','*');
END;
/
COMMIT;

1.2. Grant the resolve privilege for users that need use the special functions:

1.2.1. Run select to see all of users :

SELECT OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS') order by OWNER;

Return example:

JAVAUTIL,UTL_HTTP SYSCLI,UTL_SMTP

1.2.2. Grant to user JAVAUTIL and SYSCLI (remember to commit) :

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
       acl => 'acl_list.xml',
       principal => 'JAVAUTIL',
       is_grant => true,
       privilege => 'connect');
END;
/
commit;

BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
       acl => 'acl_list.xml',
       principal => 'SYSCLI',
       is_grant => true,
       privilege => 'connect');
END;
/
commit;

2. Errors using java class in database.
Oracle Error: The class is not compiled
In 11g database the JDBC driver has changed, and classes that use JDBC driver must import the new.

Solution:
2.1. Change the import class:

From: import oracle.jdbc.driver*; To: import oracle.jdbc.*;

3. Ambiguous column in the query.
Oracle Error: ORA-00918: column ambiguously defined
According to Oracle this is an expected behavior and 11g from the need to qualify ambiguous names in select list (DOC ID
835701.1)

Solution:
3.1. Change the query specifying the columns ambiguous.

Example of query with ambiguous column:

SELECT PROJECT_ID , PROJ_VERSION_NO, NAME , DESCRIPTION , LAST_CHANGED_DATE
FROM leeh.PW2_PROJECT
JOIN
(SELECT PROJECT_ID AS MAX_ID, MAX(PROJ_VERSION_NO) AS MAX_VERSION
FROM leeh.PW2_PROJECT
GROUP BY PROJECT_ID
) T
ON PROJECT_ID = MAX_ID AND PROJ_VERSION_NO = MAX_VERSION
WHERE PROJ_VERSION_NO > 0;

4. Error in procedure or function when change user password.
Oracle Error: ORA-0600
In 11g database, the password column on view sys.dba_users, no longer contains the user’s password.

Solution:
4.1. Change the queries to use sys.user$ view.

5. Implicit data conversion is no longer allowed in some times
Oracle Error: ORA-06502: PL/SQL: numeric or value error
Implicit and explicit conversion for numeric values may also suffer from the analogous problem, as the conversion result may depend on the session parameter NLS_NUMERIC_CHARACTERS. This parameter defines the decimal and group separator characters. If the decimal separator is defined to be the quotation mark or the double quotation mark, some potential for SQL injection emerges.

Solution:
5.1. Check NLS_NUMERIC_CHARACTERS or change the queries to use quotes.

6. User login error, because password case sensitivity
Oracle Error: ORA-01017: invalid username/password
When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords, set the SEC_CASE_SENSITIVE_LOGON initialization parameter. Only users who have the ALTER SYSTEM privilege can set the SEC_CASE_SENSITIVE_LOGON parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.

Solution:
6.1. Set init parameter “sec_case_sensitive_logon” to false :

*.sec_case_sensitive_logon=false

7. Error using cursor with DBMS_SQL
Oracle Error: ORA-29471: DBMS_SQL access denied
In 11g database, oracle has introduced some security changes to the DBMS_SQL package to prevent cursor injection. The better solution is create the cursors using explicitly security level, for example:

curs = dbms_sql.open_cursor(level);

The level 1 requires the executing/binding and parsing user IDs to be the same. Level 2 is more strict and requires id and roles are the same for all operations like binds, describes, executes, fetches etc.

Workaround to ignore the security cursor:
7.1. Set init parameter “_dbms_sql_security_level” to zero:

*._dbms_sql_security_level=0

Good Luck !

4 Comments to "Be careful when migrating – Difference between oracle 10g and 11g"

  1. 09/30/2011 - 8:39 PM | Permalink

    Thank you for these kinds of a good blog. Exactly where else could one get such details written in such an incite full way? I have a presentation that I am just now working on, and I have been searching for this sort of information.

  2. Sabari's Gravatar Sabari
    08/24/2013 - 2:55 AM | Permalink

    It actually very good information

Leave a Reply