Archive for June, 2014

Recently I tried to create an incident package for Oracle support and find it very easy and convenient to use, so thought of sharing…. Basically through ADRCI we can combine all incident related files to one and send to oracle support. Apart from this it can be used for viewing diagnostic data with in automatic diagnostic repository (ADR). Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more. Below are steps that I used for creating two incident packages.

First, go to adrci prompt and check the reported incidents. In my case, I have two databases GLDB & ACCDB running from same oracle home, so it will show incidents for both databases. When I tried to create package I got below error:

adrci> IPS CREATE PACKAGE INCIDENT 156129
DIA-48448: This command does not support multiple ADR homes

Reason for this is that we need to set the home right from which we need to create incident

adrci> show homes
ADR Homes:
diag/rdbms/gldb/GLDB
diag/rdbms/testdb1/TESTDB1
adrci> set home diag/rdbms/gldb/GLDB

Below is full text for commands that are used to create a package.

/u01/app/oracle/product/11.2.0.3 =>adrci

ADRCI: Release 11.2.0.3.0 - Production on Sun Mar 30 09:34:48 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle/admin"
adrci>
adrci>
adrci>
adrci> show incident

ADR Home = /u01/app/oracle/admin/diag/rdbms/gldb/GLDB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
108209               ORA 445                                                     2014-02-13 01:17:01.410000 +01:00
108210               ORA 445                                                     2014-02-13 12:02:03.713000 +01:00
108211               ORA 445                                                     2014-02-14 07:02:22.391000 +01:00
156129               ORA 445                                                     2014-03-30 03:02:50.743000 +02:00
156130               ORA 445                                                     2014-03-30 04:40:54.699000 +02:00
156217               ORA 445                                                     2014-03-30 05:59:43.103000 +02:00
156218               ORA 445                                                     2014-03-30 06:01:48.879000 +02:00
156219               ORA 445                                                     2014-03-30 06:03:57.081000 +02:00
156220               ORA 445                                                     2014-03-30 06:06:00.327000 +02:00
156221               ORA 445                                                     2014-03-30 06:08:03.929000 +02:00

ADR Home = /u01/app/oracle/admin/diag/rdbms/accdb/ACCDB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
67665                ORA 445                                                     2014-02-13 11:49:06.173000 +01:00
67425                ORA 445                                                     2014-02-13 11:51:06.115000 +01:00
2 rows fetched

adrci> IPS CREATE PACKAGE INCIDENT 156129
DIA-48448: This command does not support multiple ADR homes

adrci> show homes
ADR Homes:
diag/rdbms/gldb/GLDB
diag/rdbms/testdb1/TESTDB1
adrci> set home diag/rdbms/gldb/GLDB
adrci> IPS CREATE PACKAGE INCIDENT 156129
Created package 1 based on incident id 156129, correlation level typical
adrci> IPS ADD INCIDENT 156129 PACKAGE 1
Added incident 156129 to package 1
adrci> IPS GENERATE PACKAGE 1 IN /u01/app/oracle/admin
Generated package 1 in file /u01/app/oracle/admin/ORA445_20140330094558_COM_1.zip, mode complete
adrci>

adrci> adrci>
adrci> IPS CREATE PACKAGE INCIDENT 156130
Created package 2 based on incident id 156130, correlation level typical
adrci> IPS ADD INCIDENT 156130 PACKAGE 2
Added incident 156130 to package 2
adrci> IPS GENERATE PACKAGE 2 IN /u01/app/oracle/admin
Generated package 2 in file /u01/app/oracle/admin/ORA445_20140330095106_COM_1.zip, mode complete
adrci> exit
/u01/app/oracle/product/11.2.0.3 =>

Once package is created it’s basically a zip file containing all the required files for the particular incident like alert.log file, trc & trm files. I just unzipped the one of the files generated from packaging incident and below is directory structure for this.

ADRCI zip file directory structure

ADRCI zip file directory structure

Also I have posted a poll to know, how many people are using this feature for oracle support. Please give your inputs on this.

Today i seen that “create user identified by values” command was not working. Below is the error message:

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";
CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"
*
ERROR at line 1:
ORA-02153: invalid VALUES password string

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 20 06:36:55 2014

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create user myuser identified by myuser default tablespace user_data temporary tablespace temp;

User created.

SQL> set long 100000
SQL> set line 1000
SQL>
SQL> select dbms_metadata.get_ddl('USER','MYUSER') from dual;

DBMS_METADATA.GET_DDL('USER','MYUSER')
--------------------------------------------------------------------------------

   CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A
009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3'
      DEFAULT TABLESPACE "USER_DATA"
      TEMPORARY TABLESPACE "TEMP"


SQL> drop user myuser cascade;

User dropped.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> show parameter case

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

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.
SQL>

Now, what to do for running this in 10g database and here comes the new thing in password.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G 11G  S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

See in above query there are two types of passwords in above query which are taken from user$ table. Our old 10g format password is saved in column PASSWORD and new 11g format password is stored in column SPARE4 in user$ table. Just to verify the above thing, if we closely monitor the user creation script then there is “;” in password seperating the 10g & 11g version od password. See the below code:

'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3'

The shorter password is for 10g and longer one if for 11g only. Below code created user successfully in 10g database while the user creation script is captured from 11g database in above steps.

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 20 06:40:46 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";
CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD;1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP"
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES '1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> conn myuser/myuser
ERROR:
ORA-01045: user MYUSER lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
Connected.

When the password version field contains both values like “10G 11G” then your password will contains both the 10g & 11g encrypted password and “sec_case_sensitive_logon” parameter is false then you can use case insensitive passwords, like I have logged in successfully with password “myuser” and “MYUSER”.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G 11G  S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

SQL> show parameter case

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

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.
SQL>

Now, say you created user with only 11g encrypted password (longer version of string) then you must set the “sec_case_sensitive_logon” parameter to true. Then only you will be able to login with old password as shown in below code. Notice that password_version field only contains 11G now and only spare4 column has encrypted value not the password column as in case of “10G 11G” password.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> grant create session to myuser;

Grant succeeded.

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


Warning: You are no longer connected to ORACLE.
SQL> conn myuser/MYUSER
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=TRUE scope=both;

System altered.

SQL> show parameter case

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER                                    11G      S:284BFB4F5F6B668496D761BCC685786A009006D4B05D96ED92377BBDF1FD

Now, time to create use with 10g encrypted password only. Below is code having details of same. Now, password column has encrypted password while spare4 column is empty. Also, with 10g encrypted password it doesn’t matter what value of parameter “sec_case_sensitive_logon” you set, user will be able to login as in below code.

SQL> CREATE USER "MYUSER" IDENTIFIED BY VALUES '1205E0F44EE803D3' DEFAULT TABLESPACE "USER_DATA" TEMPORARY TABLESPACE "TEMP";

User created.

SQL> grant create session to myuser;

Grant succeeded.

SQL> conn myuser/myuser
Connected.
SQL>
SQL> conn myuser/MYUSER
Connected.

SQL> conn / as sysdba
Connected.
SQL>
SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G

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

System altered.

SQL> conn myuser/myuser
Connected.
SQL> conn myuser/MYUSER
Connected.

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL>
SQL> select d.username,u.password,d.password_versions,u.spare4 from dba_users d,user$ u where d.username=u.name and d.username='MYUSER';

USERNAME   PASSWORD                       PASSWORD SPARE4
---------- ------------------------------ -------- ------------------------------------------------------------------------------------------
MYUSER     1205E0F44EE803D3               10G

Hope this will help 🙂