Writing About Our Experiences With Oracle Databases
Yesterday, I found one very useful article at OTN “Creating Oracle Extended RAC” on completely virtual environment using Oracle VM. As Virtualization is becoming popular day by day and is very cost effective, one must know how to use this to simulate actual environments. Click here for details on Oracle Extended RAC on Oracle VM.
Data in the database is secured by using authentication, authorization and auditing mechanism, but the information stored in the datafiles can be read using any hex-editor tools or other OS level utilities, which is causing threat to sensitive data. To overcome this limitation TDE is used to secure sensitive data, in database columns, stored in the datafiles. TDE is key-based access control mechanism. For each table, having encrypted column in it, an encryption key is generated which is again encrypted by database server’s master key. The database server’s master key is generated at the time when Wallet is ceated. The master key is stored outside oracle database in a Wallet and the keys for all tables containing encrypted columns are stored in dictionary tables in database.
How to setup TDE:
To use TDE one must have “ALTER SYSTEM” privilege and a valid password for oracle Wallet. 1. Make sure that listener and database is up and running and compatibility level of database is set to 10.2 . Enabling Transparent Data Encryption. Issue following command:
SQL > alter system set encryption key identified by "password";
If the password is not given in “” then it will be taken in capital letters. This command will do the following things: - Create a Wallet file (ewallet.p12) - Open the Wallet. - Generate databse server’s master encryption key. By default, above command will create ewallet.p12 file under location $ORACLE_BASE/admin/SID/WALLET directory/folder. So it is mandatory to create a directory/folder with name as “WALLET” under $ORACLE_BASE/admin/SID, if it is not created then following error will occur while creating Wallet file:
SQL> alter system set encryption key identified by "oracle"; * ERROR at line 1: ORA-28368: cannot auto-create wallet
The default location is helpful if someone wants different Wallets for different instances running under same ORACLE_HOME. We can manually specify location of Wallet file by modifying $ORACLE_HOME/network/admin/sqlnet.ora file as:
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=C:\Oracle\product\10.2.0\db_1\wallet)))
Any valid existing directory can be specified here.
Why Wallet is needed?
The encryption keys are stored in the wallet.Database needs to load master key and column encryption keys into memory from the Wallet before it can start encryption/decryption of columns. Wallet remains open until we shutdown the database. It will be closed after the database is shutdown and needs to be started again after database startup as:
SQL > alter system set encryption wallet open identified by "password";
If the wallet remains closed, we will not be able to access the table which contains encrypted columns and will get following error:
ORA-28365: wallet is not open
– Now TDE can be used for this database.
Example:
SQL > conn scott/tiger
SQL > SQL> l 1 create table sood( 2 first_name varchar2(10), 3 last_name varchar2(10), 4 empid number, 5* salary number ENCRYPT); SQL> create user saurabh identified by oracle; User created. SQL> grant create session to saurabh; Grant succeeded.
Insert data into scott.sood as:
SQL> insert into sood values ('jonh','wallter',1,30000);
1 row created.
SQL> insert into sood values ('Michael','Clark',2,20000);
1 row created.
SQL> insert into sood values ('Ricky','Ponting',3,10000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sood;
FIRST_NAME LAST_NAME EMPID SALARY
---------- ---------- ---------- ----------
jonh wallter 1 30000
Michael Clark 2 20000
Ricky Ponting 3 10000
SQL> show user
USER is “SCOTT”
SQL> grant select on sood to saurabh;
Grant succeeded.
As user saurabh has select privilige on sood(table containing encrypted column), it will be able to retrive (decrypt) data without performing any additional step.
SQL> conn saurabh/oracle
Connected.
SQL> select * from scott.sood;
FIRST_NAME LAST_NAME EMPID SALARY ---------- ---------- ---------- ---------- jonh wallter 1 30000 Michael Clark 2 20000 Ricky Ponting 3 10000
If we mine the redo logs, the logminer will show Salary column as “Unsupported Type” under SQL_REDO, because the data was encrypted just before it was written to disk.
Export/Import and TDE:
1. exp/imp will not work with tables having encrypted columns, following error will be given:
------------------------------------------------------------
C:\Documents and Settings\sausood>exp scott/tiger tables=sood
Export: Release 10.2.0.1.0 - Production on Fri Dec 19 13:05:55 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column SALARY in table SCOTT.SOOD is not supported. The table will not be exported.
Export terminated successfully with warnings.
--------------------------------------------------------------
TDE is only compatible with DATAPUMP export/import.
2. Backing up tables containing encrypted columns:
SQL> select * from DBA_ENCRYPTED_COLUMNS;
OWNER TABLE_NAM COLUMN_NAM ENCRYPTION_ALG SAL
—— ——— ———- —————————– —
SCOTT SOOD SALARY AES 192 bits key YES
Use the following command to export TDE enabled tables.
$ expdp scott/tiger directory=dir dumpfile=exptde.dmp tables=sood encryption_password="oracle"
Parameter ENCRYPTION_PASSWORD allows encrypted columns to be encrypted while exporting these.The password has nothing to deal with encryption keys, it is to used while importing the tables. If the ENCRYPTION_PASSWORD is not used the the data is exported in clear text and a warning message is displayed at the time of datapump export:
"ORA-39173: Encrypted data has been stored unencrypted in dump file set. "
While importing it is mandatory to have the same wallet file for database, otherwise following error will occur:
"ORA-28362: master key not found"
Transparent Data Encryption FAQ
People planning to appear for 10g OCA Exam will be startled to find that they need to pass additional exam to get the certificate. Starting from December 1, 2008, in addition to the existing exam (Oracle Database 10g: Administration I (1Z0-042)), candidates are also required to pass any one of the four following Oracle SQL exams to earn the certification:
Introduction to Oracle: SQL® and PL/SQL™ (1Z0-001) or,
Introduction to Oracle9i: SQL (1Z0-007) or,
Oracle Database 11g: SQL Fundamentals I (1Z1-051) or,
Oracle Database SQL Expert (1Z0-047)
People who have cleared the IZ0-042 exam before 30 Nov 2008 are not required to give the exam. Below is snapshot from Oracle website detailing new Certification Path. You can find the details here
There is a common misconception that Index access is the best access method for query execution. This approach leads to people concentrate on removing Full Table Scans (FTS) from the Query execution plan. I recently worked on a performance tuning issue where I found such scenario. Basically customer was trying to generate report for 3 years and query was running for more then 8 hours without producing any results. We were asked to look into this problem.
Following diagnostic data was collected to diagnose the issue.
1) 10046 trace at level 12
2) Execution plan from V$SQL_PLAN (Database version is 9.2)
3) Explain plan for offending query
From 10046 trace and V$session_wait, I found out query was waiting on “db file sequential read” wait event for table SCOTT_BILL. This indicated that we were reading this table using index rowid’s. Please find below explain plan for the problematic query.
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 | 66436 |
| 1 | SORT ORDER BY | | 1 | 161 | 66433 |
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | 1 | 161 | 66424 |
| 4 | NESTED LOOPS | | 1 | 141 | 66421 |
| 5 | NESTED LOOPS | | 1 | 126 | 66419 |
| 6 | NESTED LOOPS | | 391 | 27761 | 65246 |
|* 7 | TABLE ACCESS FULL | SCOTT_PO | 21716 | 254K| 98 |
| 8 | TABLE ACCESS BY INDEX ROWID| SCOTT_POIC | 1 | 59 | 3 |
|* 9 | INDEX RANGE SCAN | SCOTT_POIC_MK3 | 1 | | 2 |
|* 10 | TABLE ACCESS BY INDEX ROWID | SCOTT_BILL | 1 | 55 | 3 |
|* 11 | INDEX RANGE SCAN | SCOTT_BILL_FK7 | 1 | | 2 |
|* 12 | TABLE ACCESS BY INDEX ROWID | SCOTT_CUST | 1 | 15 | 2 |
|* 13 | INDEX RANGE SCAN | SCOTT_CUST_FK1 | 1 | | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | SCOTT_DSST | 1 | 20 | 3 |
|* 15 | INDEX UNIQUE SCAN | SCOTT_DSST_PK | 1 | | 2 |
| 16 | SORT AGGREGATE | | 1 | 17 | |
| 17 | FIRST ROW | | 1 | 17 | 4 |
|* 18 | INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK | 166M| | 4 |
| 19 | SORT AGGREGATE | | 1 | 36 | |
|* 20 | INDEX RANGE SCAN | SCOTT_POIC_UK1 | 1 | 36 | 3 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO")
FROM "MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2
AND "B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
7 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
9 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
"SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
filter("SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
10 - filter("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-11-30
23:59:59', 'yyyy-mm-dd hh24:mi:ss') AND NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01
' AND "SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND
"SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS NULL AND
NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
"SYS_ALIAS_6"."BILL_INER_CODE" IS NULL AND
"SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
"SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO")
11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
))
12 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
"SCOTT_CUST"."CUST_INER_CODE" IS NULL)
13 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
15 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND
"C"."DSST_PROC_GROUP"=1 AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"=
(SELECT /*+ */ MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE
"D"."DSST_PROCESS"='PSRV' AND "D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
18 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
"D"."DSST_PROCESS"='PSRV')
20 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
"B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
"B"."POIC_CTRT_TYPE"='BAS')
filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')
Looking at explain plan (and plan from V$SQL_PLAN) confirmed that we were using index SCOTT_BILL_FK7. Plan shows that CBO estimates only 1 row for this operation. But since we have been on this wait even for close to 8 hours, it was clear that we are accessing more then 1 row.Normally this query was run for small range(date column) and used to complete in 10 mins. e.g Below is explain plan for the same query with 4 day’s range.
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 | 28876 |
| 1 | SORT ORDER BY | | 1 | 161 | 28873 |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | SCOTT_CUST | 1 | 15 | 2 |
| 4 | NESTED LOOPS | | 1 | 161 | 28864 |
| 5 | NESTED LOOPS | | 1 | 146 | 28862 |
| 6 | NESTED LOOPS | | 1 | 134 | 28861 |
| 7 | NESTED LOOPS | | 56 | 4200 | 28581 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SCOTT_BILL | 2123 | 114K| 22212 |
|* 9 | INDEX RANGE SCAN | SCOTT_BILL_MK10 | 26081 | | 85 |
| 10 | TABLE ACCESS BY INDEX ROWID | SCOTT_DSST | 1 | 20 | 3 |
|* 11 | INDEX UNIQUE SCAN | SCOTT_DSST_PK | 1 | | 2 |
| 12 | SORT AGGREGATE | | 1 | 17 | |
| 13 | FIRST ROW | | 1 | 17 | 4 |
|* 14 | INDEX RANGE SCAN (MIN/MAX)| SCOTT_DSST_PK | 166M| | 4 |
|* 15 | TABLE ACCESS BY INDEX ROWID | SCOTT_POIC | 1 | 59 | 5 |
|* 16 | INDEX RANGE SCAN | SCOTT_POIC_MK3 | 1 | | 2 |
|* 17 | TABLE ACCESS BY INDEX ROWID | SCOTT_PO | 1 | 12 | 1 |
|* 18 | INDEX UNIQUE SCAN | SCOTT_PO_PK | 1 | | |
|* 19 | INDEX RANGE SCAN | SCOTT_CUST_FK1 | 1 | | 1 |
| 20 | SORT AGGREGATE | | 1 | 36 | |
|* 21 | INDEX RANGE SCAN | SCOTT_POIC_UK1 | 1 | 36 | 3 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SYS_ALIAS_5"."POIC_SEQ_NO"= (SELECT /*+ */ MAX("B"."POIC_SEQ_NO") FROM
"MAT"."SCOTT_POIC" "B" WHERE "B"."POIC_PO_SEQ_NO"=:B1 AND "B"."POIC_PO_NO"=:B2 AND
"B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B5,0) AND "B"."POIC_CTRT_TYPE"='BAS'))
3 - filter(NVL("SCOTT_CUST"."CUST_COMP_CODE",'01')='01' AND
"SCOTT_CUST"."CUST_INER_CODE" IS NULL)
8 - filter(NVL("SYS_ALIAS_6"."BILL_COMP_CODE",'01')='01' AND
"SYS_ALIAS_6"."BILL_SRV_TYPE"='S' AND "SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG" IS
NULL AND NVL("SYS_ALIAS_6"."BILL_EXCISE_FORM_SERIES_FLAG",'X')<>'N' AND
"SYS_ALIAS_6"."BILL_INER_CODE" IS NULL)
9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
11 - access("SYS_ALIAS_6"."BILL_SRV_NO"="C"."DSST_SRV_NO" AND "C"."DSST_PROC_GROUP"=1
AND "C"."DSST_PROCESS"='PSRV' AND "C"."DSST_SEQ_NO"= (SELECT /*+ */
MAX("D"."DSST_SEQ_NO") FROM "MAT"."SCOTT_DSST" "D" WHERE "D"."DSST_PROCESS"='PSRV' AND
"D"."DSST_PROC_GROUP"=1 AND "D"."DSST_SRV_NO"=:B1))
14 - access("D"."DSST_SRV_NO"=:B1 AND "D"."DSST_PROC_GROUP"=1 AND
"D"."DSST_PROCESS"='PSRV')
15 - filter("SYS_ALIAS_6"."BILL_PITM_SEQ_NO"="SYS_ALIAS_5"."POIC_PITM_SEQ_NO" AND
NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0))
16 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO" AND
"SYS_ALIAS_6"."BILL_ITEM_CODE"="SYS_ALIAS_5"."POIC_ITEM_CODE" AND
"SYS_ALIAS_5"."POIC_CTRT_TYPE"='BAS')
17 - filter(NVL("SCOTT_PO"."PO_COMP_CODE",'01')='01')
18 - access("SCOTT_PO"."PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SCOTT_PO"."PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
19 - access("SCOTT_CUST"."CUST_SRV_NO"="SYS_ALIAS_6"."BILL_SRV_NO")
21 - access("B"."POIC_PO_NO"=:B1 AND "B"."POIC_PO_SEQ_NO"=:B2 AND
"B"."POIC_ITEM_CODE"=:B3 AND "B"."POIC_PITM_SEQ_NO"=:B4 AND "B"."POIC_CTRT_TYPE"='BAS')
filter("B"."POIC_ITEM_CODE"=:B1 AND "B"."POIC_PITM_SEQ_NO"=:B2 AND
NVL("B"."POIC_RQMT_NO",0)=NVL(:B3,0) AND "B"."POIC_CTRT_TYPE"='BAS')
Note: cpu costing is off
We can see that we used SCOTT_BILL_MK10 index here which was on the date column. This is also clear from predicate information available from explain plan.
9 - access("SYS_ALIAS_6"."BILL_ASRV_DATE">=TO_DATE('2008-04-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "SYS_ALIAS_6"."BILL_ASRV_DATE"<=TO_DATE('2008-04-05 23:59:59',
'yyyy-mm-dd hh24:mi:ss'))
Coming back to our case, we see that index SCOTT_BILL_FK7 is being used here.This index is created on BILL_PO_NO, BILL_PO_SEQ_NO and BILL_RQMT_NO columns.
From explain plan, we see that following condition is used due to which we access the index
11 - access("SYS_ALIAS_6"."BILL_PO_NO"="SYS_ALIAS_5"."POIC_PO_NO" AND
"SYS_ALIAS_6"."BILL_PO_SEQ_NO"="SYS_ALIAS_5"."POIC_PO_SEQ_NO")
filter(NVL("SYS_ALIAS_6"."BILL_RQMT_NO",0)=NVL("SYS_ALIAS_5"."POIC_RQMT_NO",0
))
Checking the column values, we see following
COLUMN_NAME NUM_DISTINCT NUM_NULLS ------------------------------ ------------ ---------- BILL_PO_NO 31841 636 BILL_PO_SEQ_NO 4 1296 BILL_RQMT_NO 437 27333280
I believe lot of null records present in the table along with NVL function usage is causing this issue. We can see from above data, that column BILL_RQMT_NO has 27 million null records. As B-Tree index does not index Null columns(B-tree indexes index null columns too if index is a concatenated index and the other column has non-null value. Refer to this article from Richard Foote). CBO assumed that only 1 row will be returned and it choose index MMAT_DOMS_FK7 as best access method. But because of NVL function, this returns more rows instead of just one row. This is my hypothesis and not sure , if its correct 
As a quick fix , I suggested using FULL hint on SCOTT_BILL table so that we can perform a Full table Scan (FTS). This helped and query completed in 28 mins. If we look at long term solution, having a default value of 0 for this column could also be looked at. But all these things need to be decided in consideration with application design.
This is also a reminder to a fact that index access is not always the best access method. It is suitable if we are looking for very small data set and could become problem if data set increases.Whereas Full table Scan (FTS) perform better in such cases as we perform multiblock I/O.
Oracle 11g introduces Case-sensitive passwords for database authentication. Along with this if you wish to change the password (temporarily) and reset it back to old , you will find that password field in dba_users is empty. Prior to 11g we could use following technique to change/restore password
SQL> create user amit identified by amit; User created. SQL> grant create session to amit; Grant succeeded. SQL> conn sys as sysdba Enter password: Connected. SQL> select username,password from dba_users where username='AMIT'; USERNAME PASSWORD ------------------------------ ------------------------------ AMIT 9DEC0D889E8E9A6B SQL> alter user amit identified by abc; User altered. SQL> conn amit/abc Connected. SQL> conn sys as sysdba Enter password: Connected. SQL> alter user amit identified by values '9DEC0D889E8E9A6B'; User altered. SQL> conn amit/amit Connected.
In 11g if you query password field, it will return NULL.
SQL> select username,password from dba_users where username='AMIT'; USERNAME PASSWORD ------------------------------ ------------------------------ AMIT
Let’s first see Case-sensitive password feature in 11g and then steps to change/restore passwords
SQL> create user amit identified by AMIT; User created. SQL> grant connect,resource to amit; Grant succeeded. SQL> conn amit/amit ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn amit/AMIT Connected.
This behavior is controlled by “sec_case_sensitive_logon” initialization paramter. If the value is true then it will enforce case sensitive passwords
SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon'; NAME VALUE ---------------------------------------- -------------------- sec_case_sensitive_logon TRUE SQL> conn / as sysdba Connected. SQL> alter system set sec_case_sensitive_logon=false; System altered. SQL> conn amit/amit Connected. SQL> conn / as sysdba Connected. SQL> alter system set sec_case_sensitive_logon=true; System altered. SQL> conn amit/amit ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn amit/AMIT Connected.
Now to reset the password in 11g, we need to query spare4 column in user$ table
SQL> select spare4 from user$ where name='AMIT'; SPARE4 -------------------------------------------------------------------------------- S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB SQL> alter user amit identified by abc12; User altered. SQL> conn amit/abc12 Connected. SQL> conn / as sysdba Connected. SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB'; User altered. SQL> conn amit/abc12 ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn amit/AMIT Connected.
As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:
decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL) for example: SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5; USERNAME PASSWORD ------------------------------ -------- SYS 10G 11G SYSTEM 10G 11G OUTLN 10G 11G DIP 10G 11G
In this case it means both old and new-style hash values are available for the users, 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.
SQL> create user test identified by test;
User created.
SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in (’AMIT’,'TEST’);
USERNAME PASSWORD
—————————— ——–
AMIT 11G
TEST 10G 11G
As I had reset password using only spare4 string, password will be case -sensitive irrespective of setting for sec_case_sensitive_logon parameter value. i.e why we see value of “11G” for user Amit.
Update
When resetting the password, we need to also query password column from user$ column if we wish to use case-insensitive feature in future. i.e In my above example I used only spare4 column value to reset the password. Now if I set sec_case_sensitive_logon=false , I will not be able to connect.
SQL> alter system set sec_case_sensitive_logon=false; System altered. SQL> conn amit/amit ERROR: ORA-01017: invalid username/password; logon denied
In case we wish to use both, we need to set identified by values ‘S:spare4;password’. As I didnot use password field while resetting, I find that password field in user$ is empty. To correct it, I had to change the password again.
SQL> select password,spare4 from user$ where name='AMIT';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> alter user amit identified by AMIT;
User altered.
SQL> select password,spare4 from user$ where name='AMIT';
PASSWORD SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE
So to reset the password, following needs to be used.
SQL> select password,spare4 from user$ where name='AMIT'; PASSWORD SPARE4 ------------------------------ ---------------------------------------------------------------------- 9DEC0D889E8E9A6B S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE SQL> alter user amit identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B'; User altered.
Thanks to Laurent for pointing this. You can see his article for more information.
All India Oracle user Group (AIOUG) has arranged for Seminar by Tom Kyte on 18th December 2008.
Venue- The Good Shepherd Auditorium
Residency Road- Museum Road Junction
Opp. St. Joseph’s P.U. College
Bangalore - 560 025
SEMINAR TOPICS
If you register before 20th November, you can save Rs 1000 You can find details here
I came across useful link which contains 10g RAC Tuning tips from Joel Goodman (Oracle). It discusses RAC Instance Recovery concepts and key things to look out for while tuning RAC (Real Application Clusters) Databases. Must watch for anyone who is learning RAC.
You can find the recorded webcast here. You can also become member of oracleracsig to find many such webcasts/resources on RAC.
AskDba.org Forum AskDba Non-Oracle Blog| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| « Dec | ||||||
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 | |
