skip to main |
skip to sidebar
I've been reading about new features and one of them are the "Case Sensitive Passwords" as a default feature of newly oracle 11g version.
The SEC_CASE_SENSITIVE_LOGON initialization parameter gives us the control over case sensitive passwords. We can use the ALTER SYSTEM command to turn off this functionality.
SQL> show parameter sec_case_sensitive_logon
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL>
Now lets try it using some examples and creating a new user with a mixed case password.
SQL> create user demo1 identified by demo1;
User created.
SQL> conn demo1/demo1
Connected.
SQL>
SQL> show user
USER is "DEMO1"
SQL> SQL> conn demo1/Demo1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn demo1/deMo1
ERROR:
ORA-01017: invalid username/password; logon denied
SQL>
Ok, now let me switch the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE and we are able to connect using both variations of the password. Lets try.
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.
SQL> conn demo1/demo1
Connected.
SQL> conn demo1/Demo1
Connected.
SQL> conn demo1/DEMO1
Connected.
SQL>
Just keep in mind that the original case sensitive password is retained, so it can be used if case sensitivity is subsequently enabled.
The DBA_USERS view includes a PASSWORD_VERSIONS column that indicates the database release in which the password was created or last modified.
SQL> desc DBA_USERS
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
SQL>
SQL> select username, password, password_versions from dba_users;
USERNAME PASSWORD PASSWORD
------------------------------ ------------------------------ --------
SYS 10G 11G
DBSNMP 10G 11G
DEMO1 10G 11G
SYSTEM 10G 11G
OUTLN 10G 11G
WKSYS 10G 11G
OLAPSYS 10G 11G
SI_INFORMTN_SCHEMA 10G 11G
OWBSYS 10G 11G
FLOWS_030000 10G 11G
ORDPLUGINS 10G 11G
WKPROXY 10G 11G
XDB 10G 11G
ANONYMOUS
.
.
.
.
SQL>
In this case it means both old and new-style hash values ar available for user DEMO1, the new hash value is stored in the USER$.SPARE4 column, as long as this remains NULL it means the password has not been changed since the migration and the user will have the old case insensitive password.
We can note above that the hash value is not visible anymore like in old Oracle versions. But we still can check this value using dictionary table named SYS.USER$.
SQL> select name,password from sys.user$ where name='DEMO1';
NAME PASSWORD
------------------------------ ------------------------------
DEMO1 DD9FD1922A0AFC29
SQL>
In addition one more interesting thing that we have in Oracle 11g is a new dictionary view named DBA_USERS_WITH_DEFPWD. This view help us to find users using "default" passwords and we must change them as soon as possible.
SQL> select * from dba_users_with_defpwd;
USERNAME
------------------------------
DIP
MDSYS
SPATIAL_WFS_ADMIN_USR
TSMSYS
WK_TEST
CTXSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM
.
.
.
.
SQL>
Lets see how to apply the CPUJAN2010 on my database version 11.1.0.7 running on my Virtual Machine. The VM is running Enterprise Linux Enterprise Linux AS release 4 (October Update 4).
First step is download the Patch "p9114072_111070_Linux-x86.zip" from Metalink as shown below and unzip the file into some temp directory on the server.
Lets see how to do this:
vmlinux3-oracle-XXXX-/oracle/app/dev/dba/orapatch >ls -ltr
total 868
drwxrwxr-x 20 oracle dba 4096 Jan 6 09:14 9114072
-rw-r--r-- 1 oracle dba 877256 Feb 21 11:47 p9114072_111070_Linux-x86.zip
vmlinux3-oracle-XXXX-/oracle/app/dev/dba/orapatch >rm p9114072_111070_Linux-x86.zip
vmlinux3-oracle-XXXX-/oracle/app/dev/dba/orapatch >lt
total 4
drwxrwxr-x 20 oracle dba 4096 Jan 6 09:14 9114072
Use the opatch to apply the patch as the following example:
vmlinux3-oracle-XXXX-/oracle/app/dev/dba/orapatch >cd 9114072/
vmlinux3-oracle-XXXX-/oracle/app/dev/dba/orapatch/9114072 >opatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.2.0.1.1
Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /oracle/app/oracle/product/11.1.0.7
Central Inventory : /oracle/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.1.0.7.0
OUI location : /oracle/app/oracle/product/11.1.0.7/oui
Log file location : /oracle/app/oracle/product/11.1.0.7/cfgtoollogs/opatch/opatc
Patch history file: /oracle/app/oracle/product/11.1.0.7/cfgtoollogs/opatch/opatc
Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home...
OPatch continues with these patches: 8306934 8342506 8563941 8563942 8563943 8563944 8563946 8563947 8563948 8855553 8855559 8855565 8855570 8855575 8855577 9114072 9118620 9118622
Do you want to proceed? [y|n]
y
User Responded with: Y
Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/oracle/product/11.1.0.7')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...
Execution of 'sh /oracle/app/dev/dba/orapatch/9114072/8342506/custom/scripts/pre -apply 8342506 ':
Return Code = 0
Execution of 'sh /oracle/app/dev/dba/orapatch/9114072/9114072/custom/scripts/pre -apply 9114072 ':
Return Code = 0
Applying patch 8306934...
ApplySession applying interim patch '8306934' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8306934' for rollback. This might take a while...
Patching component oracle.network.listener, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglgr.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglro.o"
ApplySession adding interim patch '8306934' to inventory
Verifying the update...
Inventory check OK: Patch ID 8306934 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8306934 are present in Oracle Home.
Applying patch 8342506...
ApplySession applying interim patch '8342506' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8342506' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/view_recompile/recompile_precheck_jan2008cpu.sql"
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/view_recompile/view_recompile_jan2008cpu.sql"
ApplySession adding interim patch '8342506' to inventory
Verifying the update...
Inventory check OK: Patch ID 8342506 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8342506 are present in Oracle Home.
Applying patch 8563941...
ApplySession applying interim patch '8563941' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563941' for rollback. This might take a while...
Patching component oracle.network.listener, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsgcs.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglsn.o"
ApplySession adding interim patch '8563941' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563941 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563941 are present in Oracle Home.
Applying patch 8563942...
ApplySession applying interim patch '8563942' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563942' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Copying file to "/oracle/app/oracle/product/11.1.0.7/rdbms/admin/catmetx.sql"
ApplySession adding interim patch '8563942' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563942 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563942 are present in Oracle Home.
Applying patch 8563943...
ApplySession applying interim patch '8563943' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563943' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/krvg.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/knld.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/prsc.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/prsg.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/prssz.o"
ApplySession adding interim patch '8563943' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563943 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563943 are present in Oracle Home.
Applying patch 8563944...
ApplySession applying interim patch '8563944' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563944' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/kzrt.o"
ApplySession adding interim patch '8563944' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563944 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563944 are present in Oracle Home.
Applying patch 8563946...
ApplySession applying interim patch '8563946' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563946' for rollback. This might take a while...
Patching component oracle.rdbms.rsf, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libcommon11.a" with "lib/libcommon11.a/ttcpip.o"
Patching component oracle.rdbms, 11.1.0.7.0...
ApplySession adding interim patch '8563946' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563946 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563946 are present in Oracle Home.
Applying patch 8563947...
ApplySession applying interim patch '8563947' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563947' for rollback. This might take a while...
Patching component oracle.sysman.console.db, 11.1.0.7.0...
Updating jar file "/oracle/app/oracle/product/11.1.0.7/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/config/compare/CompareWizSecondConfigBean.class"
ApplySession adding interim patch '8563947' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563947 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563947 are present in Oracle Home.
Applying patch 8563948...
ApplySession applying interim patch '8563948' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8563948' for rollback. This might take a while...
Patching component oracle.sysman.console.db, 11.1.0.7.0...
Updating jar file "/oracle/app/oracle/product/11.1.0.7/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistoryBean.class"
Updating jar file "/oracle/app/oracle/product/11.1.0.7/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistoryController.class"
Updating jar file "/oracle/app/oracle/product/11.1.0.7/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistorySearch.class"
ApplySession adding interim patch '8563948' to inventory
Verifying the update...
Inventory check OK: Patch ID 8563948 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8563948 are present in Oracle Home.
Applying patch 8855553...
ApplySession applying interim patch '8855553' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8855553' for rollback. This might take a while...
Patching component oracle.sdo.locator, 11.1.0.7.0...
Copying file to "/oracle/app/oracle/product/11.1.0.7/md/admin/sdomigrt.sql"
ApplySession adding interim patch '8855553' to inventory
Verifying the update...
Inventory check OK: Patch ID 8855553 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8855553 are present in Oracle Home.
Applying patch 8855559...
ApplySession applying interim patch '8855559' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8855559' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/kzia.o"
ApplySession adding interim patch '8855559' to inventory
Verifying the update...
Inventory check OK: Patch ID 8855559 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8855559 are present in Oracle Home.
Applying patch 8855565...
ApplySession applying interim patch '8855565' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8855565' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating jar file "/oracle/app/oracle/product/11.1.0.7/rdbms/jlib/CDC.jar" with "/rdbms/jlib/CDC.jar/oracle/CDC/Purge.class"
Updating jar file "/oracle/app/oracle/product/11.1.0.7/rdbms/jlib/CDC.jar" with "/rdbms/jlib/CDC.jar/oracle/CDC/PublishApi.class"
ApplySession adding interim patch '8855565' to inventory
Verifying the update...
Inventory check OK: Patch ID 8855565 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8855565 are present in Oracle Home.
Applying patch 8855570...
ApplySession applying interim patch '8855570' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8855570' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Copying file to "/oracle/app/oracle/product/11.1.0.7/rdbms/admin/prvtaqds.plb"
Copying file to "/oracle/app/oracle/product/11.1.0.7/rdbms/admin/dbmsaqds.plb"
Copying file to "/oracle/app/oracle/product/11.1.0.7/rdbms/admin/prvtaqiu.plb"
ApplySession adding interim patch '8855570' to inventory
Verifying the update...
Inventory check OK: Patch ID 8855570 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8855570 are present in Oracle Home.
Applying patch 8855575...
ApplySession applying interim patch '8855575' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8855575' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/kkxs.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/kza.o"
ApplySession adding interim patch '8855575' to inventory
Verifying the update...
Inventory check OK: Patch ID 8855575 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8855575 are present in Oracle Home.
Applying patch 8855577...
ApplySession applying interim patch '8855577' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '8855577' for rollback. This might take a while...
Patching component oracle.rdbms.rsf, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libpls11.a" with "lib/libpls11.a/pkwrap.o"
Patching component oracle.rdbms, 11.1.0.7.0...
ApplySession adding interim patch '8855577' to inventory
Verifying the update...
Inventory check OK: Patch ID 8855577 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8855577 are present in Oracle Home.
Applying patch 9114072...
ApplySession applying interim patch '9114072' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '9114072' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/CPUJan2010/catcpu.sql"
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/CPUJan2010/catcpu_rollback.sql"
Copying file to "/oracle/app/oracle/product/11.1.0.7/rdbms/admin/bundledata_CPU.xml"
Copying file to "/oracle/app/oracle/product/11.1.0.7/rdbms/admin/catbundle.sql"
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/scripts/sdo_oct2009.sql"
Patching component oracle.rdbms.rsf, 11.1.0.7.0...
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/CPUJan2010/rollback_all.lst"
Copying file to "/oracle/app/oracle/product/11.1.0.7/cpu/CPUJan2010/rollback_new.lst"
ApplySession adding interim patch '9114072' to inventory
Verifying the update...
Inventory check OK: Patch ID 9114072 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9114072 are present in Oracle Home.
Applying patch 9118620...
ApplySession applying interim patch '9118620' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '9118620' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libserver11.a" with "lib/libserver11.a/kzld.o"
ApplySession adding interim patch '9118620' to inventory
Verifying the update...
Inventory check OK: Patch ID 9118620 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9118620 are present in Oracle Home.
Applying patch 9118622...
ApplySession applying interim patch '9118622' to OH '/oracle/app/oracle/product/11.1.0.7'
Backing up files affected by the patch '9118622' for rollback. This might take a while...
Patching component oracle.network.rsf, 11.1.0.7.0...
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libnro11.a" with "lib/libnro11.a/ncrfgp.o"
Updating archive file "/oracle/app/oracle/product/11.1.0.7/lib/libnro11.a" with "lib/libnro11.a/ncrfidsa.o"
Patching component oracle.rdbms, 11.1.0.7.0...
ApplySession adding interim patch '9118622' to inventory
Verifying the update...
Inventory check OK: Patch ID 9118622 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9118622 are present in Oracle Home.
Running make for target itnslsnr
Running make for target ioracle
Running make for target client_sharedlib
Running make for target client_sharedlib
Running make for target iwrap
--------------------------------------------------------------------------------
********************************************************************************
********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Execution of 'sh /oracle/app/dev/dba/orapatch/9114072/9114072/custom/scripts/post -apply 9114072 ':
Return Code = 0
The local system has been patched and can be restarted.
UtilSession: N-Apply done.
OPatch succeeded.
vmlinux3-oracle-XXXX-/oracle/app/dev/dba/orapatch/9114072 >
Please refer to the "3.3.2 Post Installation Instructions" section in order to check if any more steps are requered.
Checking the installation:
vmlinux3-oracle-ATLASDEV-/oracle/app/oracle/product/11.1.0.7/cpu/view_recompile >opatch lsinventory
Invoking OPatch 11.2.0.1.1
Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/oracle/product/11.1.0.7
Central Inventory : /oracle/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.1.0.7.0
OUI location : /oracle/app/oracle/product/11.1.0.7/oui
Log file location : /oracle/app/oracle/product/11.1.0.7/cfgtoollogs/opatch/opatch2010-02-21_14-21-51PM.log
Patch history file: /oracle/app/oracle/product/11.1.0.7/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /oracle/app/oracle/product/11.1.0.7/cfgtoollogs/opatch/lsinv/lsinventory2010-02-21_14-21-51PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (3):
Oracle Database 11g 11.1.0.6.0
Oracle Database 11g Examples 11.1.0.6.0
Oracle Database 11g Patch Set 1 11.1.0.7.0
There are 3 products installed in this Oracle Home.
Interim patches (18) :
Patch 9118622 : applied on Sun Feb 21 14:00:43 BRST 2010
Unique Patch ID: 12054869
Created on 23 Dec 2009, 08:14:47 hrs PST8PDT
Bugs fixed:
9118622
Patch 9118620 : applied on Sun Feb 21 14:00:39 BRST 2010
Unique Patch ID: 12054869
Created on 23 Dec 2009, 08:18:01 hrs PST8PDT
Bugs fixed:
9118620
Patch 9114072 : applied on Sun Feb 21 14:00:34 BRST 2010
Unique Patch ID: 12054869
Created on 23 Dec 2009, 12:13:30 hrs PST8PDT
Bugs fixed:
9114072, 8836375, 8290478, 8534338
Patch 8855577 : applied on Sun Feb 21 14:00:29 BRST 2010
Unique Patch ID: 12054869
Created on 9 Sep 2009, 04:46:33 hrs PST8PDT
Bugs fixed:
8855577
Patch 8855575 : applied on Sun Feb 21 14:00:24 BRST 2010
Unique Patch ID: 12054869
Created on 9 Sep 2009, 04:50:05 hrs PST8PDT
Bugs fixed:
8855575
Patch 8855570 : applied on Sun Feb 21 14:00:11 BRST 2010
Unique Patch ID: 12054869
Created on 2 Sep 2009, 08:23:21 hrs PST8PDT
Bugs fixed:
8306933, 8855570
Patch 8855565 : applied on Sun Feb 21 14:00:07 BRST 2010
Unique Patch ID: 12054869
Created on 2 Sep 2009, 08:16:23 hrs PST8PDT
Bugs fixed:
8855565
Patch 8855559 : applied on Sun Feb 21 14:00:01 BRST 2010
Unique Patch ID: 12054869
Created on 9 Sep 2009, 04:48:57 hrs PST8PDT
Bugs fixed:
8855559, 8563945
Patch 8855553 : applied on Sun Feb 21 13:59:55 BRST 2010
Unique Patch ID: 12054869
Created on 9 Sep 2009, 04:30:24 hrs PST8PDT
Bugs fixed:
8855553
Patch 8563948 : applied on Sun Feb 21 13:59:52 BRST 2010
Unique Patch ID: 12054869
Created on 4 Jun 2009, 01:51:05 hrs PST8PDT
Bugs fixed:
8563948
Patch 8563947 : applied on Sun Feb 21 13:59:31 BRST 2010
Unique Patch ID: 12054869
Created on 4 Jun 2009, 01:52:36 hrs PST8PDT
Bugs fixed:
8563947
Patch 8563946 : applied on Sun Feb 21 13:58:46 BRST 2010
Unique Patch ID: 12054869
Created on 3 Jun 2009, 23:09:22 hrs PST8PDT
Bugs fixed:
8563946
Patch 8563944 : applied on Sun Feb 21 13:58:40 BRST 2010
Unique Patch ID: 12054869
Created on 10 Jun 2009, 06:20:58 hrs PST8PDT
Bugs fixed:
6870937, 8563944
Patch 8563943 : applied on Sun Feb 21 13:58:30 BRST 2010
Unique Patch ID: 12054869
Created on 3 Jun 2009, 08:31:45 hrs PST8PDT
Bugs fixed:
8563943
Patch 8563942 : applied on Sun Feb 21 13:57:56 BRST 2010
Unique Patch ID: 12054869
Created on 3 Jun 2009, 23:33:55 hrs PST8PDT
Bugs fixed:
8563942
Patch 8563941 : applied on Sun Feb 21 13:57:52 BRST 2010
Unique Patch ID: 12054869
Created on 3 Jun 2009, 06:48:40 hrs PST8PDT
Bugs fixed:
8563941
Patch 8342506 : applied on Sun Feb 21 13:57:48 BRST 2010
Unique Patch ID: 12054869
Created on 18 Mar 2009, 04:38:31 hrs PST8PDT
Bugs fixed:
8342506
Patch 8306934 : applied on Sun Feb 21 13:57:43 BRST 2010
Unique Patch ID: 12054869
Created on 13 Mar 2009, 04:56:47 hrs PST8PDT
Bugs fixed:
8306934
--------------------------------------------------------------------------------
OPatch succeeded.
vmlinux3-oracle-ATLASDEV-/oracle/app/oracle/product/11.1.0.7/cpu/view_recompile >
Lets check my installation results using SQLPlus.
SQL>
SQL>
SQL>
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL>
SQL> select * from registry$history;
ACTION_TIME ACTION NAMESPAC VERSION ID COMMENTS BUNDLE_SERIES
------------------------------ ---------- -------- -------- ---------- ---------------------------------------- -------------
21-FEB-10 01.09.04.839873 PM APPLY SERVER 11.1.0.7 4 CPUJan2010 CPU
21-FEB-10 01.14.26.308370 PM CPU 6452863 view recompilation
SQL>
And we are done!!
Today I'm going to write about oracle PFILE and SPFILE parameters. Let's have some definitions first:
- PFILE is statics text file that must be updated with a standard text editor like "notepad" or "vi". This file normally resides on the server and DBA's commomly refer to this file as INIT.ora file.
- SPFILE or Server Parameter File is as persistent binary file that can only be modified with the "ALTER SYSTEM SET" command. Editing an SPFILE will corrupit it and you will not be able to start your database.
Well, now how do I know if my database is using a PFILE or SPFILE? Let's try executing below query into database:
SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "File Type" FROM sys.v_$parameter WHERE name = 'spfile';
File T
------
SPFILE
SQL>
You can also use the v$spparameter view to check if you are using a PFILE or not.
SQL> desc v$spparameter
Name Null? Type
----------------------------------------- -------- ----------------------------
SID VARCHAR2(80)
NAME VARCHAR2(80)
VALUE VARCHAR2(255)
DISPLAY_VALUE VARCHAR2(255)
ISSPECIFIED VARCHAR2(6)
ORDINAL NUMBER
UPDATE_COMMENT VARCHAR2(255)
SQL> select NAME from v$spparameter where name like '%pfile%';
NAME
--------------------------------------------------------------------------------
spfile
SQL>
If the column value is NULL for all parameters, you are using a PFILE. I'll update this post with more tips soon!!!
Regards,
Marcello Travassos.
Hello!
STATSPACK is a performance diagnosis tool, available since Oracle8i. For everybody looking for a good place to start....check this out!!
Oracle Statspack Survival Guide
Don't forget to have a look at Oracle Docs also!
Oracle9i Database Performance Tuning Guide and Reference - Release 2 (9.2)
Jonathan Lewis has good examples also!
Good Reading!!
Regards,
Marcello Travassos.
Hi,
Below I'm going to show some good commands for DBAs under Unix environment.
Basic File Navigation
The "pwd" command displays the current directory:
$> pwd
/u01/app/oracle/product/9.2.0.1.0
The "ls" command lists all files and directories in the specified directory. If no location is defined it acts on the current directory:
$> ls
$> ls /u01
$> ls -al
The "-a" flag lists hidden "." files.
The "-l" flag lists file details.
The "cd" command is used to change directories:
$> cd /u01/app/oracle
The "touch" command is used to create a new empty file with the default permissions:
$> touch my.log
The "rm" command is used to delete files and directories:
$> rm my.log
$> rm -R /archive
The "-R" flag tells the command to recurse through subdirectories.
The "mv" command is used to move or rename files and directories:
$> mv (from) (to)
$> mv my.log my1.log
$> mv * /archive
$> mv /archive/* .
The "." represents the current directory
The "cp" command is used to copy files and directories:
$> cp [from] [to]
$> cp my.log my1.log
$> cp * /archive
$> cp /archive/* .
The "mkdir" command is used to create new directories:
$> mkdir archive
The "rmdir" command is used to delete directories:
$> rmdir archive
The "find" command can be used to find the location of specific files:
$> find / -name dbmspool.sql
$> find / -print | grep -i dbmspool.sql
The "/" flag represents the staring directory for the search.
Wildcards such as "dbms*" can be used for the filename.
The "which" command can be used to find the location of an executable you are using:
oracle> which sqlplus
The "which" command searches your PATH setting for occurences of the specified executable.
File Permissions
The "umask" command can be used to read or set default file permissions for the current user:
$> umask 022
The umask value is subtracted from the default permissions (666) to give the final permission:
666 : Default permission
022 : - umask value
644 : final permission
The "chmod" command is used to alter file permissions after the file has been created:
$> chmod 777 *.log
Character eqivalents can be used in the chmod command:
$> chmod o+rwx *.log
$> chmod g+r *.log
$> chmod -Rx *.log
The "chown" command is used to reset the ownership of files after creation:
$> chown -R oinstall.dba *
The "-R" flag causes the command ro recurse through any subdirectories.
OS Users Management
The "useradd" command is used to add OS users:
$> useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user
The "-G" flag specifies the primary group.
The "-g" flag specifies the secondary group.
The "-d" flag specifies the default directory.
The "-m" flag creates the default directory.
The "-s" flag specifies the default shell.
The "usermod" command is used to modify the user settings after a user has been created:
$> usermod -s /bin/csh my_user
The "userdel" command is used to delete existing users:
$> userdel -r my_user
The "-r" flag removes the default directory.
The "passwd" command is used to set, or reset, the users login password:
$> passwd my_user
The "who" command can be used to list all users who have OS connections:
$> who
$> who | head -5
$> who | tail -5
$> who | grep -i ora
$> who | wc -l
The "head -5" command restricts the output to the first 5 lines of the who command.
The "tail -5" command restricts the output to the last 5 lines of the who command.
The "grep -i ora" command restricts the output to lines containing "ora".
The "wc -l" command returns the number of lines from "who", and hence the number of connected users.
Process Management
The "ps" command lists current process information:
$> ps
$> ps -ef | grep -i ora
Specific processes can be killed by specifying the process id in the kill command:
$> kill -9 12345
Uname and Hostname
The "uname" and "hostname" commands can be used to get information about the host:
$> uname -a
Linux vmlinux3.example.com 2.6.9-42.0.0.0.1.ELsmp #1 SMP Sun Oct 15 14:02:40 PDT 2006 i686 i686 i386 GNU/Linux
$> uname -a | awk '{ print $2 }'
vmlinux3.example.com
$> hostname
vmlinux3.example.com
Error Lines in Files
You can return the error lines in a file using:
$> cat alert_ORATEST10g.log | grep -i ORA-
The "grep -i ORA-" command limits the output to lines containing "ORA-". The "-i" flag makes the comparison case insensitive. A count of the error lines can be returned using the "wc" command. This normally give a word count, but the "-l" flag alteres it to give a line count:
$> cat alert_ORATEST10g.log | grep -i ORA- | wc -l
File Exists Check
The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:
#!/bin/ksh
if test -s /backup/daily_backup.log
then
DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
mv /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log
fi
Remove Old Files
The find command can be used to supply a list of files to the rm command:
find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;
Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:
sed -e 's/^M$//' filename > tempfile
The newly created tempfile should have the ^M character removed.
Run Commands As Oracle User From $
The following scripts shows how a number of commands can be run as the "oracle" user the "$" user:
#!/bin/ksh
su - oracle <
ORACLE_SID=LIN1; export ORACLE_SID
rman catalog=rman/rman@w2k1 target=/ cmdfile=my_cmdfile log=my_logfile append
EOF
This is often necessary where CRON jobs are run from the $ user rather than the oracle user.
Compress Files
In order to save space on the filesystem you may wish to compress files such as archived redo logs. This can be using either the gzip or the compress commands. The gzip command results in a compressed copy of the original file with a ".gz" extension. The gunzip command reverses this process:
gzip myfile
gunzip myfile.gz
The compress command results in a compressed copy of the original file with a ".Z" extension. The uncompress command reverses this process:
compress myfile
uncompress myfile
General Performance
$ vmstat 5 3
$ sar -u 10 8
$ mpstat 10 2
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:
SELECT a.username,
a.osuser,
a.program,
spid,
sid,
a.serial#
FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND spid = '&pid';
Hope it helps!
Regards,
Marcello Travassos.
