Checking Conflicts Before Applying CPU Patch

Hi Friends, today I want to bring an important update regarding Jul CPU 2011 patch applying.

most of the times, we will never check any conflicts for the patches when applying any CPU. ofcourse this conflict checking command is not there even in README.html file that is distributed with patch.

Please use below command to check the conflicts aganist the oracle_home and avoid to land in problems
step 1: unzip your patch zip file
step 2: run below command
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <patch_directory>
Example:
$ unzip p9655017_10204_linux.zip
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir 9655017
The other day, when I am doing patching on a RAC database, after executing the above conflict command, got below error
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
6600051, 8836683
Whenever you get this type of error message, plz contact oracle support by raising a service request(SR)
In my case, Oracle support suggested to apply a merge patch 9347333 before applying Jul CPU 2011. Once done with applying merge patch, without any further issues I successfully applied CPU patch
Sometimes apart from above message you may see below warning messages which you can ignore
Summary of Conflict Analysis:
Patches that can be applied now without any conflicts are :
10013975, 10014009, 10014012, 10014015, 10325878, 10325885, 11787762, 11787763, 11787765, 11787766, 12419249, 12566121, 12566124, 12566126, 12566129, 12566131, 12566134, 12566136, 12566137, 12566139, 12566141, 12566142, 12566143, 7155248, 7155249, 7155250, 7155251, 7155252, 7155253, 7155254, 7197583, 7375611, 7375613, 7375617, 7609057, 8309592, 8309632, 8568395, 8568397, 8568398, 8568402, 8568404, 8836667, 8836671, 8836675, 8836677, 8836678, 8836683, 8836684, 8836686, 9173244, 9173253, 9442328, 9442331, 9442339, 9678690, 9678695, 9678697
Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list :
10249540, 8836681, 8568405
Following patches will be rolled back from Oracle Home on application of the patches in the given list :
10013975, 10014009, 10014012, 10014015, 10325878, 10249540, 8836681, 8568405, 7155248, 7155249, 7155250, 7155251, 7155252, 7197583, 7375611, 7375613, 7375617, 7609057, 8309592, 8309632, 8568395, 8568397, 8568398, 8568402, 8568404, 8836667, 8836671, 8836675, 8836677, 8836678, 8836683, 8836684, 8836686, 9173244, 9173253, 9442328, 9442331, 9442339, 9678690, 9678695

SYSDBA AND SYSOPER

 SYSDBA:

The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA. Connecting as a SYSDBA user invokes the SYSDBA privilege.
 Sysdba has all rights to do any thing on the database.

SYSOPER:
 Sysoper doesn't have some privileges , they are
 1. Change character set
 2.Create database
 3.Drop database
 4.ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as        UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)

SYSTEM:
This account can perform all administrative functions except the following:
1.Backup and recovery
2.Database upgrade

When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.





  

FAL_SERVER & FAL_CLIENT IN DATAGUARD

FAL_CLIENT and FAL_SERVER are initialization parameters used to configure log gap detection and resolution at the standby database side of a physical database configuration. This functionality is provided by log apply services and is used by the physical standby database to manage the detection and resolution of archived redo logs.

FAL_CLIENT and FAL_SERVER only need to be defined in the initialization parameter file for the standby database(s). It is possible; however, to define these two parameters in the initialization parameter for the primary database server to ease the amount of work that would need to be performed if the primary database were required to transition its role.

FAL_CLIENT specifies the TNS network services name for the standby database (which is sent to the FAL server process by log apply services) that should be used by the FAL server process to connect to the standby database. The syntax would be: 

FAL_CLIENT=<net_service_name_of_standby_database>

· FAL_SERVER specifies the TNS network service name that the standby database should use to connect to the FAL server process. The syntax would be:

FAL_SERVER=<net_service_name_of_primary_database>

Oracle 11g Case Sensitive Passwords

Oracle 11g by default force case sensitivity of user passwords. The users have to provide passwords in the same case (upper, lower or mixed) they created the password with. Lets observe this behavior.

Case sensitive user passwords in Oracle 11g

SQL> create user u1 identified by U1;

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> conn u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

We have created a user named u1 with a password U1. Now this user has to provide the password as U1 but not u1. This behavior is controlled with an initialization parameter SEC_CASE_SENSITIVE_LOGON. By default it has a value TRUE.

SQL> conn / as sysdba
Connected.
SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> alter system set sec_case_sensitive_logon=false scope=both;

System altered.

SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE

SQL> conn u1/u1
Connected.

Now that sec_case_sensitive_logon is set to false, the case of the password doesn't matter.
Remember this password "U1" is stored in the case it was created with, so as soon as you switch the case sensitive password to on again, it will start forcing the case of the password.

SQL> alter system set sec_case_sensitive_logon=true scope=both;

System altered.

SQL> conn u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> conn u1/U1
Connected.
SQL> 

As oracle releases before 11g didn't have case sensitivity on password, so when you import your users from a pre-11g database to 11g database their password is still case insensitive. The passwords for these users will become case sensitive when they change their passwords after they are imported to 11g database, if the password case sensitivity is enabled. So after importing users to 11g database pro pre 11g releases e.g. 10g ask the users to change their passwords immediately for better password security.
There is a new column in DBA_USERS view which shows the history of the user passwords.

SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
SCOTT                          10G 11G
HR                             10G 11G
ZAHID                          10G 11G
U1                             11G
KAREEM                         10G

The users having password_version=10g 11g, means they are imported from 10g and they have modified their passwords after being imported to 11g.
The users having password_version=11g, means these users were created in 11g database.
The users having password_version=10g, means they were imported from 10g and haven't yet changed their password after being imported to 11g.

Case sensitive password in Password File in Oracle 11g

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwora11g entries=30 password=System  ignorecase=n

-- This will create a password file with a password "System" and ignorecase=n
-- ignorecase=n is the default.

$ sqlplus sys/system@ora11g as sysdba

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

-- When I provide the password as "system" the logon was denied.

$ sqlplus sys/System@ora11g as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 06:11:41 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

-- I am successfully logged in when I used the password "System".

ignorecase=n is the default with the orapwd command in oracle 11g i.e. you mention it or not it will force the password to be case sensitive when users log in as SYSDBA remotely.
To turn off password case sensitivity in password file we need to explicitly mention ignorecase=y while creating the password file.

$ cd $ORACLE_HOME/dbs

$ rm orapwora11g

$ orapwd file=orapwora11g entries=30 password=System ignorecase=y

$ sqlplus sys/system@ora11g as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 06:19:24 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

This time I created a password file with ignorecase=y and I can login with a password "system".
If password file was created with ignorecase=n and you have imported a SYSDBA or SYSOPER user from previous oracle release, their passwords will be included in the password file but they will be able to use case insensitive passwords unless they modify their passwords. So ask users to modify their passwords once they are shifted to 11g for better security.

Db Links Passwords Case Sensitivity in Oracle 11g

There could be three scenarios of db link connections.
1). A user connecting from a pre-11g database to 11g database

If the password case sensitivity is switched on in the 11g database then recreate the db link with the password in uppercase before the user can connect to 11g database. As db link passwords are always created in uppercase no matter what case was used at the time of db link creation.

2). A user connecting from 11g database to 11g database.

If the user is connecting from a 11g database to 11g database and password case sensitivity is enabled, the user must enter the password using the case in which it was created.

3). A user connecting from 11g database to a pre-11g database.

If the user is connecting from a 11g database to pre-11g database then the case of the password doesn't matter. As there is no password case sensitivity in the pre-11g database.