Thursday, November 20, 2014

OUI-10020: A write lock cannot be obtained

Once I faced issue with OUI that, it was failing with following message,

OUI-10020:The target area /etc/oraInventory is being used by another session. A write lock cannot be obtained.

Cause:


Some process that holds
 lock on the /etc/oraInventory file. Until that software release lock OUI continues to display message OUI-10020.

Solution:


1.Go to the oraInventory directory.
$cd /etc/oraInventory

2. See the contents of it.

$ls /etc/oraInventory

You will see the locks directory here.

3.Go to the lock directory and delete the read lock under the directory.


$cd locks
$ rm 
/etc/oraInventory/locks/reader0.lock

4.Now run Oracle Universal Installer again. It should work.

Monday, November 17, 2014

Fix for "Internet explorer has closed this webpage to help protect your computer"

Many users face issue running Oracle forms on Microsoft Internet Explorer 8 (IE8) which causes the page to redirect to following url
res://ieframe.dll/acr_depnx_error.htm#<domain>,http://<server>:<port>/forms/frmservlet?config=<config>

It displays following error.
Internet explorer has closed this webpage to help protect your computer
A malfunctioning or malicious add-on has caused Internet Explorer to close this webpage.




Solution:
Go to Internet Explorer -> Tools -> Internet Options -> Advanced -> Scroll down to Security -> Uncheck “Enable memory protection to help mitigate online attacks*”
Close all browser windows and restart the browser. The issue should have been fixed :-)



Edit: Alternatively you can use either of following 2 alternate solutions.

Alternate 2:

Open registry using start->Run->regedit->ok
Go to HKEY_LOCAL_MACHINESOFTWAREMicrosoftInternet ExplorerMain
On the right hand side you will see a DWORD key called DEPOff. IF this is set to value 0, just change it to 1 and close registry editor.



Restart Internet Explorer and check if the problem has been fixed or not.

Alternate 3:

Right click on My Computer->Properties (or start->Run->sysdm.cpl->ok)
Click on “Advanced” Tab. Click on “Settings” button next to Performance.
Click on last tab “Data Execution Prevention
Select Second option and then select “Internet Explorer” from the bottom pan.
(If “Internet Explorer” is not already present there then just click on “Add” and then select “c:Program FilesInternet Exploreriexplore.exe”)




Click OK and restart internet explorer. The problem should have been fixed.

Wednesday, November 12, 2014

Mulitinode EBS R12 Installation on VirtualBox (Step by Step)

Here I'm uploading a step by step approach document to Install Mulitinode EBS R12 (12.1.1) on VirtualBox (Step by Step) followed by 12.1.3 Upgrade. (it was quite old but forgot to upload)



Further to this, it was upgraded from R12.1.1 to R12.1.3. Below is the link in which some known/unknown issues were mentioned/reported during upgrade process.

http://manishnashikkar.blogspot.in/2013/10/some-issues-faced-during-upgrade-to-r12.html

Please let me know, if you want this document on your mail id. plus your suggestions and feedback are welcome.

- Manish Nashikkar


Thursday, November 6, 2014

Exadata, DBFS Filesystem and ORA-64007: invalid store specified

The Activity was to resize /dbfsmnt mount point from 2 TB to 1 TB due to space crunch in DATA Diskgroup started showing in -ve (Negative) Value. Our DBFS Database had 2TB Datafile/Tablespace exist in DATA Diskgroup.

NAME    STATE Usable Total DG Space (GB) Usable Free DG Space (GB) OFFLINE_DISKS
-------------------- ----------- -------------------------- ------------------------- -------------
DATA      MOUNTED      13248   -52.699219  0
DBFS      MOUNTED   135.1875   134.539063  0
RECO      MOUNTED 3312.65625    1744.9082  0

Steps I carried out to remove existing DBFS File System,

Stop the dbfs_mount service in clusterware using the oracle account.

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :crsctl stop resource dbfs_mount
CRS-2673: Attempting to stop 'dbfs_mount' on 'inmumdcdbadm02'
CRS-2673: Attempting to stop 'dbfs_mount' on 'inmumdcdbadm01'
CRS-2677: Stop of 'dbfs_mount' on 'inmumdcdbadm02' succeeded
CRS-2677: Stop of 'dbfs_mount' on 'inmumdcdbadm01' succeeded

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :crsctl stat resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
               OFFLINE OFFLINE      inmumdcdbadm01
               OFFLINE OFFLINE      inmumdcdbadm02

Drop DBFS File System

[oracle@inmumdcdbadm01 oradbfs]$ cat /home/oracle/dbs_group
inmumdcdbadm01
inmumdcdbadm02

[inmumdcdbadm01.tajhotels.com -> root:/root] :dcli -g /home/oracle/dbs_group -l root rmdir /dbfsmnt

[inmumdcdbadm01.tajhotels.com -> oracle://home/oracle] :. ./DBFS1.env
[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/admin] :sqlplus dbfs_user/******

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 21 18:16:32 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @dbfs_drop_filesystem oradbfs
No errors.
--------
UNMOUNT STORE:
begin dbms_dbfs_content.unmountStore(store_name=>'FS_ORADBFS',
store_mount=>'oradbfs'); end;
--------
UNREGISTER STORE:
begin dbms_dbfs_content.unregisterStore(store_name=> 'FS_ORADBFS'); end;
--------
DROP STORE:
begin dbms_dbfs_sfs.dropFilesystem(store_name => 'FS_ORADBFS'); end;
No errors.

Drop DBFS User

[inmumdcdbadm01.tajhotels.com -> oracle:/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/admin] :sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 21 18:22:28 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
DBFS

SQL> show user
USER is "SYS"

SQL> drop user DBFS_USER cascade;

User dropped.

SQL>  alter database datafile '+DATA_DC/dbfs/datafile/dbfsts.274.828974767' resize 1024G;

Database altered.

Recreate DBFS User

[inmumdcdbadm01.tajhotels.com -> root:/root] :dcli -g /home/oracle/dbs_group -l root mkdir /dbfsmnt

SQL> create user dbfs_user identified by ****** default tablespace dbfsts quota unlimited on dbfsts;

User created.

SQL> grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;

Grant succeeded.

Recreate DBFS Filesystem

[oracle@inmumdcdbadm01 ~]$ . ./DBFS1.env
[oracle@inmumdcdbadm01 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@inmumdcdbadm01 admin]$ sqlplus dbfs_user/*****

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 21 19:33:03 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @dbfs_create_filesystem.sql dbfsts oradbfs

No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_ORADBFS', tbl_name =>
'T_ORADBFS', tbl_tbs => 'dbfsts', lob_tbs => 'dbfsts', do_partition => false,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
ERROR: -64007 msg: ORA-64007: invalid store specified
ORA-06512: at
"SYS.DBMS_DBFS_SFS_ADMIN", line 1378
ORA-00001: unique constraint
(SYS.SYS_C003273) violated
declare
*
ERROR at line 1:
ORA-64007: invalid store specified
ORA-06512: at "SYS.DBMS_DBFS_SFS_ADMIN", line 1378
ORA-00001: unique constraint (SYS.SYS_C003273) violated
ORA-06512: at line 63

No errors.

Solution for above issue

[oracle@inmumdcdbadm01 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 5 20:31:22 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select owner, table_name from dba_tables where table_name like '%T_ORADBFS%';

no rows selected

SQL> select tabid from sys.dbfs_sfs$_tab where table_name = 'T_ORADBFS';

     TABID
----------
         1

SQL> delete from sys.dbfs_sfs$_vol where tabid = 1;

1 row deleted.

SQL> delete from sys.dbfs_sfs$_tab where tabid = 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> conn dbfs_user
Enter password:
Connected.
SQL> !pwd
/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/admin

SQL> !ls -l dbfs_create_filesystem.sql
-rw-r--r-- 1 oracle oinstall 974 Jun  1  2010 dbfs_create_filesystem.sql

SQL> @dbfs_create_filesystem.sql dbfsts oradbfs
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_ORADBFS', tbl_name =>
'T_ORADBFS', tbl_tbs => 'dbfsts', lob_tbs => 'dbfsts', do_partition => false,
partition_key => 1, do_compress => false, compression => '', do_dedup => false,
do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_ORADBFS', provider_name
=> 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_ORADBFS',
store_mount=>'oradbfs'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/oradbfs', 16895); end;
No errors.
SQL>

[oracle@inmumdcdbadm01 admin]$ cd
[oracle@inmumdcdbadm01 ~]$ . ASM.env

[oracle@inmumdcdbadm01 ~]$ sh ./add-dbfs-resource.sh
CRS-2539: A resource with the name 'dbfs_mount' is already registered
CRS-4000: Command Add failed, or completed with errors.

[oracle@inmumdcdbadm01 ~]$ crsctl stat resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
               OFFLINE OFFLINE      inmumdcdbadm01
               OFFLINE OFFLINE      inmumdcdbadm02

[inmumdcdbadm01.tajhotels.com -> oracle:/home/oracle] :crsctl start resource dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'inmumdcdbadm02'
CRS-2672: Attempting to start 'dbfs_mount' on 'inmumdcdbadm01'
CRS-2676: Start of 'dbfs_mount' on 'inmumdcdbadm02' succeeded
CRS-2676: Start of 'dbfs_mount' on 'inmumdcdbadm01' succeeded

[inmumdcdbadm01.tajhotels.com -> oracle:/dbfsmnt] :df -k /dbfsmnt
Filesystem           1K-blocks      Used Available Use% Mounted on
dbfs-dbfs_user@:/    524285952       120 524285832   1% /dbfsmnt

[inmumdcdbadm01.tajhotels.com -> oracle:/dbfsmnt] :ssh oracle@inmumdcdbadm02 df -k /dbfsmnt
Filesystem           1K-blocks      Used Available Use% Mounted on
dbfs-dbfs_user@:/    524285952       120 524285832   1% /dbfsmnt

Sometime it is required to enable trace to diagnose/troubleshoot issue related to DBFS Mount Point / File System w.r.t Cluster Services, hence do the following,

vi $GI_HOME/crs/script/mount-dbfs.sh
MOUNT_OPTIONS=trace_level=1,trace_file=/tmp/dbfs_client_trace.$$.log,trace_size=100

Now start the resource one more time to get the log file generated.

[oracle@inmumdcdbadm01 ~]$ dbfs_client dbfs_user@ -o allow_other,direct_io,trace_level=1,trace_file=/tmp/dbfs_client_trace.$$.log /dbfsmnt
Password:
Fail to connect to database server.

[oracle@inmumdcdbadm01 ~]$ tail /tmp/dbfs_client_trace.11083.log.0

[66529940 11/05/14 21:01:44.529499 ./LcdfThreadPool.h:276     ] DEBUG: Retrying after some time .110. 1415201504 542132553
[6fd3c940 11/05/14 21:01:48.634994 LcdfDBPool.cpp:189         ] ERROR: Failed to create session pool ret:-1
[6fd3c940 11/05/14 21:01:48.635069 LcdfDBPool.cpp:399         ] ERROR: ERROR 15000 - ORA-15000: command disallowed by current instance type

[6fd3c940 11/05/14 21:01:48.635090 LcdfDBPool.cpp:251         ] DEBUG: Clean up OCI session pool...
[6fd3c940 11/05/14 21:01:48.635315 LcdfDBPool.cpp:399         ] ERROR: ERROR 24416 - ORA-24416: Invalid session Poolname was specified.

[6fd3c940 11/05/14 21:01:48.635671 LcdfDBPool.cpp:444         ] CRIT : Fail to set up database connection.

----------------------------------------------------------------------------------------

tail /tmp/dbfs_client_trace.100641.log.0

 [43b6c940 03/12/14 11:15:01.577723 LcdfDBPool.cpp:189         ] ERROR: Failed to create session pool ret:-1
 [43b6c940 03/12/14 11:15:01.577753 LcdfDBPool.cpp:399         ] ERROR: ERROR 28001 - ORA-28001: the password has expired

[43b6c940 03/12/14 11:15:01.577766 LcdfDBPool.cpp:251         ] DEBUG: Clean up OCI session pool...
 [43b6c940 03/12/14 11:15:01.577805 LcdfDBPool.cpp:399         ] ERROR: ERROR 24416 - ORA-24416: Invalid session Poolname was specified.

[43b6c940 03/12/14 11:15:01.577844 LcdfDBPool.cpp:444         ] CRIT : Fail to set up database connection.

Useful Links which I Referred,

http://fritshoogland.wordpress.com/2010/07/17/dbfs-and-ora-64007-invalid-store-specified/

http://sve.to/2014/03/13/troubleshooting-oracle-dbfs-mount-issues/

By doing this Activity, I could able to accumulate approx 960 GB Free Space in DATA Diskgroup. 

NAME    STATE Usable Total DG Space (GB) Usable Free DG Space (GB) OFFLINE_DISKS
-------------------- ----------- -------------------------- ------------------------- -------------
DATA      MOUNTED      13248   959.773438  0
DBFS      MOUNTED   135.1875   134.539063  0
RECO      MOUNTED 3312.65625   1708.78516  0


I hope this might be helpful to someone. :)

Thanks,
Manish

Monday, November 3, 2014

Issues while Upgrading IAS to 10.1.3.5 for R12.1.3


Always remember to set $INST_TOP/ora/10.1.3/{SID_hostname}.env before starting IAS Upgrade.

Issue 1) While doing IAS Upgrade to 10.1.3.5, the following error occurred,



The runInstaller cannot found the opmn port number in opmn_port.
File 10.1.3_ORACLE_HOME/install/opmn_port is null.

To resolve above issue, followed "IAS Upgrade - OPMN Port Information is Unavailable (Doc ID 1545145.1)"

1. Check for correct opmn port of current instance:
$ADMIN_SCRIPTS_HOME/adopmnctl.sh status -port

2. Add it to file 10.1.3_ORACLE_HOME/install/opmn_port:
opmn_port = <the value returned from step1>

3. Click 'OK' in runinstaller to continue the patch application.


Issue 2) oc4jadmin password could become an issue when installing 10.1.3.5 Techstack software in Oracle 12.1.3 EBS. Its important to know that we are providing right oc4jadmin password.

I didn't know password for oc4jadmin, as I also tried to check my luck with some standard passwords like welcome1, welcome, oafm, secret with below command, but not worked 

How to verify:

java -jar $IAS_ORACLE_HOME/j2ee/home/jazn.jar -checkpasswd jazn.com oc4jadmin -pw *****
Unsuccessful verification of user/password pair.

***** = oc4jadmin password


The message should be successful. If not the password can be changed in system-jazn-data.xml file, for which I used "How to reset "oc4jadmin" password for standalone OC4J version 10.1.3 (Doc ID 360130.1)" 

1. Shutdown all opmn services :

cd $ADMIN_SCRIPTS_HOME
adstpall.sh / adopmnctl.sh stopall

2. cd $ORACLE_HOME/j2ee/home/config

Here ORACLE_HOME= Oracle Application server home, ie, 10.1.3 HOME
Take a backup of system-jazn-data.xml and edit the below content:

<user>
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<guid>93E5A2505D1511DEBF8E89BC12E10097</guid>
<description>OC4J Administrator</description>
<credentials>!mynewpassword</credentials>
</user>

Marked in bold is the details to be changed and that is the password. Note an ! mark has to prefixed with the password.

3. Start all opmn services.

Whenever autoconfig is run after this activity, the password changes to encrypted format.


To Verify Again,

java -jar $IAS_ORACLE_HOME/j2ee/home/jazn.jar -checkpasswd jazn.com oc4jadmin -pw *****
Successful verification of user/password pair.

Issue 3) In Post-Installation, while Configuration Assistants run to apply One-Off Patches, I had faced several issues w.r.t. opatch rollback because of bug conflict. (you may or may not face this issue)

Hence checked in /etc/OraInventory logs. Rolled-back some opatches manually, also renamed/moved some files (eg. like  which were creating problem while rolling back some patches. After that applied required patches manually OR clicked on Retry button, and completed installation successful.

[cbos -> appebs:/etc/oraInventory/logs] :tail -100f installActions2014-11-15_11-29-40PM.log
The following files had problems with being restored:
1.      /d04_r12prodapp/oracle/apps/tech_st/10.1.3/lib32/libnnz10.so

Replying 'Y' will terminate the patch roll-back immediately. It WILL NOT restore any updates that have been performed to this point. It WILL NOT update the inventory.
Replying 'N' will update the inventory showing the patch has been removed.

Do you want to STOP?
Please respond Y|N >
 Y (auto-answered by -silent)

ERROR: OPatch failed during patching, possibly due to missing files.
File Back-up Errors!

OPatch did not complete successfully...
------------------------------------------------------------------------------------------------------------
Restore Jar File
OPatch encounters the following file roll-back issues:
The following files had problems with being restored:
1.      /d04_r12prodapp/oracle/apps/tech_st/10.1.3/lib32/libnnz10.so

Replying 'Y' will terminate the patch roll-back immediately. It WILL NOT restore any updates that have been performed to this point. It WILL NOT update the inventory.
Replying 'N' will update the inventory showing the patch has been removed.

Do you want to STOP?
Please respond Y|N >
 Y (auto-answered by -silent)

File Back-up Errors!
ERROR: OPatch failed during patching, possibly due to missing files.

Workaround: mv /d04_r12prodapp/oracle/apps/tech_st/10.1.3/lib32/libnnz10.so /d04_r12prodapp/oracle/apps/tech_st/10.1.3/lib32/libnnz10.so.orig.151114


I hope this might be useful to you.

Thanks,
Manish

Self Service (html/jsp) Pages turn to complete Dark Blue / Messed in R12


After Patching HRMS RUP7 & Upgrading JRE/JDK 7 w.r.t. OracleAS 10.1.2 found issue with login page which was showing in Dark Blue, futher other OAF Pages too were improper due to which difficulty in reading some of the text






For above issue, followed below Action Plan from Oracle Support and Issue is resolved,


1. cd $OA_HTML 


2. mv ./cabo/styles ./cabo/styles.bak 


3. unzip marlin_html.zip in $OA_HTML (version 84 from rup7) 


4. copy to $OA_HTML/cabo/styles the files from $OA_HTML/cabo/styles.bak like the following: 

bistyles.xss
custom.xss 
custom.xss.pre_R12
mainMenuTree.css
OAFSlideoutMenu.css
OAFSlideoutMenu_rtl.css
oa.xss

5. Shutdown all application services 


6. Take backup of all files in following directories from application tier . 


$OA_HTML/cabo/images/cache 

$OA_HTML/cabo/styles/cache 

7. Remove all files in above 2 directories 


8. Restart services. (Bounce the Apache + OACORE OC4J processes)


9. Clear the client side browser cache, java cache, restart the browser and test and check in multiple desktops


Useful Documentation:


Look And Feel Of Self Service Pages Are Messed After R12 Upgrade ( Doc ID 1556590.1) 


Self Service Pages turn to complete Dark Blue in Release 12 (ID 1054332.1)


How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite? (ID 742107.1)


- Manish

REP-0069: Internal error java.lang.UnsatisfiedLinkError: nio (Not found in com.ibm.oti.vm.bootstrap.library.path)

This error can be seen after 12.1.1, 12.1.3 Upgrade, also after JRE, JDK 7 Upgrade on IBM AIX 7.1 OS.

Issue:
All Concurrent Requests were going into the error with following error in logfile,
REP-0069: Internal error

Cause:
If you see the error message, java.lang.UnsatisfiedLinkError: net, it actually refers libnet.so here.. java.lang.UnsatisfiedLinkError: nio (Not found in com.ibm.oti.vm.bootstrap.library.path) libnet.so is in java directory.. exactly in this subdirectory --> jre/lib/ppc

Resolution / Workaround:

Check LD_LIBRARY_PATH & LIBPATH in Context File. It should contain jre/lib/ppc absolute path. In my EBS Environment, it was missing hence I added and ran autoconfig.

<LD_LIBRARY_PATH oa_var="s_tools_ldlib" osd="IBM_AIX">/u01/prod/oracle/apps/tech_st/10.1.2/lib32:/u01/prod/oracle/apps/tech_st/10.1.2/lib:/u01/prod/oracle/apps/tech_st/10.1.2/jdk/jre/lib/ppc:/usr/dt/lib:/u01/prod/oracle/apps/tech_st/10.1.2/jdk/jre/bin:/u01/prod/oracle/apps/tech_st/10.1.2/jdk/jre/bin/classic:/u01/prod/oracle/apps/apps_st/appl/sht/12.0.0/lib</LD_LIBRARY_PATH>


This action was fixed my problem.

Thanks,
Manish

Saturday, September 27, 2014

Workaround for PASSWORD EXPIRED or EXPIRE(GRACE)

Had a Challenge that wanted to keep Same Password to keep Application Running because even application team didn't know at how many places this password was used. When I checked the password it was in EXPIRE (GRACE) Status, 

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='WYSE';


USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE

------------------------------ -------------------------------- --------- --------- ------------------------------
WYSE                           EXPIRED(GRACE)                                                 DEFAULT

While "ALTER USER WYSE PASSWORD EXPIRE" exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle.


The password expiration mechanism is a method to provide security. Using this mechanism, users are automatically prompted to change their password after a certain period, defined in the profile the user is assigned. This way, compromised passwords can only be used for a certain period, afterwards the password needs to be changed.


The method described here should only be used when there is no way that you can change passwords or need to keep an application running. (Here I had to keep same password because this password had been hard coded at application level)


Workaround


For this workaround, I used the “ALTER USER ... IDENTIFIED BY VALUES” statement. 


Log in as a user that was granted the DBA privilege. Verify that the account_status of the user is EXPIRED orEXPIRE(GRACE):


SQL> SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='WYSE';


'ALTERUSER'||NAME||'IDENTIFIEDBYVALUES'''||SPARE4||';'||PASSWORD||''';'

-------------------------------------------------------------------------------------------------------------
ALTER USER WYSE IDENTIFIED BY VALUES 'S:AC4C23B91E11B3129D237FDF0EDB9166549E978408BC9795C5B3EA97ADF8;E6077E624E866DEA';

Now, use this dynamic SQL query to get the encrypted password for the user “WYSE” (from sys.user$) and construct the ALTER USER statement to reset the password:

SQL> ALTER USER WYSE IDENTIFIED BY VALUES 'S:AC4C23B91E11B3129D237FDF0EDB9166549E978408BC9795C5B3EA97ADF8;E6077E624E866DEA';

User altered.

This will change the account status back to OPEN and I should be able to log in using your old password:

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,PROFILE from dba_users where username='WYSE';

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA PROFILE
------------------------------ -------------------------------- --------- --------- --------------------------
WYSE                           OPEN                                                 DEFAULT

SQL> conn wyse
Enter password:
Connected.

SQL> show user
USER is "WYSE"

This I found Interesting and useful at quite sometime, hence Thought to Share with You - Manish

Tuesday, September 16, 2014

Oracle Exadata Patching

Recently, I've got a chance to perform Exadata Patching Activity on our X3-2 Quarter Exadata Box (2 Compute Nodes + 3 Cell Nodes) in co-ordination with Oracle, which consist of,

1) Upgrade Image of DB Servers from 11.2.3.2.1 to 11.2.3.3.1 in rolling fashion, which is most up-to-date at this moment.

2) Apply Bundle Patch 24 (JUL 2014 - 11.2.0.3.24) for QCPE & QDPE On RAC Oracle Homes

Patch description:  "QUARTERLY CRS PATCH FOR EXADATA (JUL 2014 - 11.2.0.3.24) (18906063)"
Patch description:  "QUARTERLY DATABASE PATCH FOR EXADATA (JUL 2014 - 11.2.0.3.24) : (18707883)"

3) Run Catbundle in above patch applied RAC Databases.

4) Upgrade Image of Cell (Storage) Servers from 11.2.3.2.1 to 11.2.3.3.1 in rolling fashion

5) Apply patch for Infiniband (IB) Switch

Obviously above these 5 steps took lot of planning & pre-requisites checks.
The reason, we went for Image Upgrade because we hit on following bug which is resolved in this 11.2.3.3.1 Image.

Our one of the disks was showing below status,

Issue:

CellCLI> LIST PHYSICALDISK WHERE DISKTYPE=harddisk
         20:0    R7K4ND  normal
         20:1    R7LPXD  normal
         20:2    R7P2ND  normal
         20:3    R7ESGD  normal
         20:4    R7H27D  warning - poor performance
         20:5    R7PK9D  normal
         20:6    R7GWJD  normal
         20:7    R7PL2D  normal
         20:8    R7DN1D  normal
         20:9    R7EASD  normal
         20:10   R748SD  normal
         20:11   R6X83D  normal

[root@inblrdrceladm01 ~]# cellcli -e list griddisk attributes name,status,asmmodestatus,asmdeactivationoutcome
         DATA_DR_CD_00_inblrdrceladm01   active                  ONLINE          Yes
         DATA_DR_CD_01_inblrdrceladm01   active                  ONLINE          Yes
         DATA_DR_CD_02_inblrdrceladm01   active                  ONLINE          Yes
         DATA_DR_CD_03_inblrdrceladm01   active                  DROPPED         Yes 
         DATA_DR_CD_04_inblrdrceladm01   proactive failure       DROPPED         Yes
         DATA_DR_CD_05_inblrdrceladm01   active                  ONLINE          Yes
         DBFS_DG_CD_02_inblrdrceladm01   active                  ONLINE          Yes
         DBFS_DG_CD_03_inblrdrceladm01   active                  DROPPED         Yes
         DBFS_DG_CD_04_inblrdrceladm01   proactive failure       DROPPED         Yes
         DBFS_DG_CD_05_inblrdrceladm01   active                  ONLINE          Yes
         RECO_DR_CD_00_inblrdrceladm01   active                  ONLINE          Yes
         RECO_DR_CD_01_inblrdrceladm01   active                  ONLINE          Yes
         RECO_DR_CD_02_inblrdrceladm01   active                  ONLINE          Yes
         RECO_DR_CD_03_inblrdrceladm01   active                  DROPPED         Yes
         RECO_DR_CD_04_inblrdrceladm01   proactive failure       DROPPED         Yes
         RECO_DR_CD_05_inblrdrceladm01   active                  ONLINE          Yes

CellCLI> list cell detail

         cellsrvStatus:          stopped
         msStatus:               running
         rsStatus:               running

cellsrvStatus was stopping automatically on 1st node due to this reason.

Cause, we found it after raising a SR:

We were hitting Bug:17021128 : NIWOT "CHIP PAUSED" CAUSES HIGH SERVICE TIME ON ALL DRIVES 

Storage Servers where LSI MegaRaid firmware is below 12.12.0-0178. This has been observed primarily on systems running Exadata Storage Software 11.2.3.2.0, 11.2.3.2.1 where LSI MegaRaid firmware is 12.1.2.0-0140. I have identified that you are on this version of firmware. 

Further evidence of this are the "Chip 0 Pause" messages in the MegaCli firmware log which I found during my investigation: 

06/26/14 8:34:06: [9e]= 1 [a0]= f [a2]= 9 ^M 
06/26/14 8:37:59: DM: Chip 0 Paused^M 
06/26/14 8:37:59: Chip <0> Slots: Cur=[133]^M 
06/26/14 8:37:59: [87]= 3 [89]= b [8b]= f [8e]= f [90]=14 ^M 
06/26/14 8:41:11: DM: Chip 0 Paused^M 
06/26/14 8:41:11: Chip <0> Slots: Cur=[69]^M 
06/26/14 8:41:11: [47]= 1 [4a]= c [4c]=17 [4e]= e [50]= e ^M 
06/26/14 8:41:16: DM: Chip 0 Paused^M 
06/26/14 8:41:16: Chip <0> Slots: Cur=[74]^M 
06/26/14 8:41:16: [4c]= 1 [4e]= e [50]= e ^M 
06/26/14 8:43:23: DM: Chip 0 Paused^M 
06/26/14 8:43:23: Chip <0> Slots: Cur=[201]^M 

To resolve this issue, the following is recommended: 

Install LSI MegaRaid firmware version 12.12.0-0178 which is included in 11.2.3.2.2 or 11.2.3.3.0

Solution: (Remember this is Rolling method, so no need to down any EBS, Siebel, Hyperion or any other Applications)

1) Upgrade Image of DB Servers from 11.2.3.2.1 to 11.2.3.3.1 in rolling fashion

./dbnodeupdate.sh -u -l /u01/patches/YUM/p18876946_112331_Linux-x86-64.zip -v

./dbnodeupdate.sh -u -l /u01/patches/YUM/p18876946_112331_Linux-x86-64.zip -b (for backup)

./dbnodeupdate.sh -u -l /u01/patches/YUM/p18876946_112331_Linux-x86-64.zip -n (for execution)

./dbnodeupdate.sh -c (after patching and node reboot)

2) Apply Bundle Patch 24 (JUL 2014 - 11.2.0.3.24) On RAC Oracle Homes (manual method)

On ASM/Grid Home first, followed by on rest RDBMS RAC Homes. (We have 11 RDBMS Homes for different Applications)

Stop agents (if running)

Check & Rollback for conflicting patches, if any

Run preprepatch script

Apply QUARTERLY CRS PATCH FOR EXADATA (JUL 2014 - 11.2.0.3.24) (18906063) & QUARTERLY DATABASE PATCH FOR EXADATA (JUL 2014 - 11.2.0.3.24) : (18707883 - this require only on DB Home and not on Grid Home) 

Run Postpatch Script

Apply conflicting patch

start CRS with rootcrs.pl -patch

crsctl check crs

Start Both EM Agents

crsctl stat res -t 

3) Run Catbundle in above patch applied RAC Databases.

su - oracle
. oraenv 
export ORACLE_SID=<instance_name>
cd $ORACLE_HOME
sqlplus "/ as sysdba"

Check for invalid objects
======================================
column comp_name format a40
column version format a12
column status format a15
select comp_name,version,status from dba_registry;
---------------------------------------------------
column owner format a15
column object_name format a40
column object_type format a20
select owner, object_name, object_type from dba_objects where status='INVALID' order by object_type,owner,object_name;

If there are a lot of invalids, this next command will list only the invalids containing SYS in the owner
-----------------------------------------------------------------------------------------------------------
select owner, object_name, object_type from dba_objects where status='INVALID' and owner like '%SYS%' order by object_type,owner,object_name;

---------------------------------------------------
@?/rdbms/admin/utlprp.sql 16
select comp_name,version,status from dba_registry;

------------------------------------------------
select capture_name from dba_capture where capture_name not like 'OGG$%';

select apply_name from dba_apply where apply_name not like  'OGG$%';

-------------------------------------------------------
select capture_name from dba_capture where capture_name not like 'OGG$%';
exec dbms_capture_adm.stop_capture('capture_name');

select apply_name from dba_apply where apply_name not like  'OGG$%';
exec dbms_apply_adm.stop_apply('apply_name');
-------------------------------------------------------------
@?/rdbms/admin/catbundle.sql exa apply

@?/rdbms/admin/utlprp.sql 16

-------------------------------------------------------------
set lines 200
column owner format a15
column object_name format a40
column object_type format a20
col comp_name for a60
select comp_name,version,status from dba_registry;

select owner, object_name, object_type from dba_objects where status='INVALID' order by object_type,owner,object_name;

If there are a lot of invalids, this next command will list only the invalids containing SYS in the owner

select owner, object_name, object_type from dba_objects where status='INVALID' and owner like '%SYS%' order by object_type,owner,object_name;

-----------------------------------------------------------
select capture_name from dba_capture where capture_name not like 'OGG$%';
exec dbms_capture_adm.start_capture('capture_name');

select apply_name from dba_apply where apply_name not like  'OGG$%';
exec dbms_apply_adm.start_apply('apply_name');

--------------------------------------------------------------
Check that the apply finished successfully

set lines 200
col ACTION_TIME for a40
col COMMENTS for a40
select * from dba_registry_history;

4) Upgrade Image of Cell (Storage) Servers from 11.2.3.2.1 to 11.2.3.3.1 in rolling fashion

cd /u01/patches/CELL/patch_11.2.3.3.1.140708
#./patchmgr -cells cell_group -patch_check_prereq -rolling

The output should be cleaned w.r.t above command for each Cell Node.

Check repair times for all mounted disk groups in the Oracle ASM instance and adjust if needed
========================================================================
su - oracle
. oraenv <<EOF 
+ASM1
EOF
sqlplus / as sysasm
select dg.name,a.value from v$asm_diskgroup dg, v$asm_attribute a where dg.group_number=a.group_number and a.name='disk_repair_time';

If the repair time is not 3.6 hours then note the value and the diskgroup names. Replace <diskgroup_name> in the following statement to adjust.

alter diskgroup '<diskgroup_name>' set attribute 'disk_repair_time'='3.6h';  ### Set it to Higher Side

Repeat the above statement for each diskgroup

2) Increase ASM Power with Limit asm_power_limit parameter
3) Check no V$asm_operation is currently going before starting Cell Patching Activity

Cell Patching in Rolling Upgrade  (Initiate from DB Node, root user)
========================================================================

[[oracle@inblrdrdbadm01 patch_11.2.3.3.1.140708]$ pwd
/u01/patches/CELL/patch_11.2.3.3.1.140708

[oracle@inblrdrdbadm01 patch_11.2.3.3.1.140708]$ cat cell_group
inblrdrceladm01
inblrdrceladm02
inblrdrceladm03

[oracle@inblrdrdbadm01 patch_11.2.3.3.1.140708]$ cat dbs_group
inblrdrdbadm01
inblrdrdbadm02

[oracle@inblrdrdbadm01 patch_11.2.3.3.1.140708]$ cat inblrdrceladm01
inblrdrceladm01

[oracle@inblrdrdbadm01 patch_11.2.3.3.1.140708]$ cat inblrdrceladm02
inblrdrceladm02

[oracle@inblrdrdbadm01 patch_11.2.3.3.1.140708]$ cat inblrdrceladm03
inblrdrceladm03

Cleanup space from any previous runs
==================================================
the -reset_force command is only done the first time the cells are patched to this release. 
It is not necessary to use the command for subsequent cell patching, even after rolling back the patch.

#./patchmgr -cells cell_group -reset_force   (cell_group consist of cell servers hostname, or you can give single hostname file name)

OR

#./patchmgr -cells inblrdrceladm01 -reset_force  (Same way inblrdrceladm02 /inblrdrceladm03 file)

-------------------------------------------------------------------------------------------------
Always use the -cleanup option before retrying a failed or halted run of the patchmgr utility.

#./patchmgr -cells cell_group -cleanup

OR

#./patchmgr -cells inblrdrceladm01 -cleanup    (Same way inblrdrceladm02 /inblrdrceladm03 file)

Run prerequisites check   (The output Should be Clean)
=================================================================
cd /u01/patches/CELL/patch_11.2.3.3.1.140708
#./patchmgr -cells cell_group -patch_check_prereq -rolling

OR

#./patchmgr -cells inblrdrceladm01 -patch_check_prereq -rolling    (Same way inblrdrceladm02 /inblrdrceladm03 file)

Patch the cell nodes (in rolling upgrade)
===========================================

# nohup ./patchmgr -cells  inblrdrceladm01 -patch -rolling &  [Same way for inblrdrceladm02 /inblrdrceladm03 file, only after checking #cellcli -e list griddisk attributes name,status,asmmodestatus,asmdeactivationoutcome should be ONLINE (Not Resyncing) on Cell Node]

SUCCESS: DONE: Execute plugin check for Patch Check Prereq.
1 of 5 :Working: DO: Initiate patch on cells. Cells will remain up. Up to 5 minutes ...
2 of 5 :Working: DO: Waiting to finish pre-reboot patch actions. Cells will remain up. Up to 45 minutes
3-5 of 5 :Working: DO: Finalize patch and check final status on cells. Cells will reboot.

Monitor the patch progress
===================================
cd /u01/patches/CELL/patch_11.2.3.3.1.140708
tail -f nohup.out

Cleanup space
==================
#./patchmgr -cells cell_group -cleanup

OR

#./patchmgr -cells inblrdrceladm01 -cleanup    (Same way inblrdrceladm02 /inblrdrceladm03 file)

Post Checks
=================
#imageinfo -version       
#imageinfo -status       
#uname -r     
#imagehistory
#uptime

#dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e list griddisk attributes name,status,asmmodestatus,asmdeactivationoutcome|more

(The next 5 lines are all one command and should not return any output. If output is returned then disks are still resyncing.)

dcli -g cell_group -l root \
"cat /root/attempted_deactivated_by_patch_griddisks.txt | grep -v \
ACTIVATE | while read line; do str=\`cellcli -e list griddisk where \
name = \$line attributes name, status, asmmodestatus\`; echo \$str | \
grep -v \"active ONLINE\"; done" 

so Don't Start another Cell Patching in case if you see Disks are Resyncing, All Disks should be ONLINE)

Change disk_repair_time back to original value 
==================================================================
su - oracle
. oraenv <<EOF 
+ASM1
EOF
sqlplus / as sysasm
select dg.name,a.value from v$asm_diskgroup dg, v$asm_attribute a where dg.group_number=a.group_number and a.name='disk_repair_time';
If the repair time is not 3.6 hours then note the value and the diskgroup names. Replace <diskgroup_name> in the following statement to adjust.
alter diskgroup '<diskgroup_name>' set attribute 'disk_repair_time'='<original value>';
Repeat the above statement for each diskgroup
exit

5) Apply patch for Infiniband (IB) Switch

cd /u01/patches/CELL/patch_11.2.3.3.1.140708

vi ibswitches.lst

One switch per line. Spine switch listed first as below:
inblrdrsw-iba0
inblrdrsw-ibb0

./patchmgr -ibswitches ibswitches.lst -upgrade -ibswitch_precheck (Pre-requisites Check)

./patchmgr -ibswitches ibswitches.lst -upgrade (Actual Upgrade)


The output should show SUCCESS . If there are errors, then correct the errors and run the upgrade command again.

############################ Post Activities and Checks #############################

CellCLI> ALTER PHYSICALDISK 20:4 reenable force; 
Physical disk 20:4 was reenabled. 

CellCLI> LIST PHYSICALDISK WHERE DISKTYPE=harddisk 
20:0 R7K4ND normal 
20:1 R7LPXD normal 
20:2 R7P2ND normal 
20:3 R7ESGD normal 
20:4 R7H27D normal    --- issue resolved
20:5 R7PK9D normal 
20:6 R7GWJD normal 
20:7 R7PL2D normal 
20:8 R7DN1D normal 
20:9 R7EASD normal 
20:10 R748SD normal 
20:11 R6X83D normal 

Run the below command on ASM1 

SQL> alter diskgroup DATA_DR add disk 'o/192.168.10.3/DATA_DR_CD_04_inblrdrceladm01' force,'o/192.168.10.3/DATA_DR_CD_03_inblrdrceladm01' force; 

Run the below command on ASM2 

SQL> alter diskgroup DBFS_DG add disk 'o/192.168.10.3/DBFS_DG_CD_04_inblrdrceladm01' force,'o/192.168.10.3/DBFS_DG_CD_03_inblrdrceladm01' force; 
SQL> alter diskgroup RECO_DR add disk 'o/192.168.10.3/RECO_DR_CD_03_inblrdrceladm01' force,'o/192.168.10.3/RECO_DR_CD_04_inblrdrceladm01' force; 

[oracle@inblrdrdbadm01 ~]$ . ASM.env
[oracle@inblrdrdbadm01 ~]$
[oracle@inblrdrdbadm01 ~]$ sqlplus "/as sysasm"

SQL> alter diskgroup DATA_DR add disk 'o/192.168.10.3/DATA_DR_CD_04_inblrdrceladm01' force,'o/192.168.10.3/DATA_DR_CD_03_inblrdrceladm01' force;

Diskgroup altered.

SQL> exit

[oracle@inblrdrdbadm01 ~]$ ssh inblrdrdbadm02
Last login: Thu Aug 14 11:13:52 2014 from inblrdrdbadm01.tajhotels.com
[oracle@inblrdrdbadm02 ~]$ . ASM.env
[oracle@inblrdrdbadm02 ~]$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 14 19:24:13 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup DBFS_DG add disk 'o/192.168.10.3/DBFS_DG_CD_04_inblrdrceladm01' force,'o/192.168.10.3/DBFS_DG_CD_03_inblrdrceladm01' force;

Diskgroup altered.

SQL> alter diskgroup RECO_DR add disk 'o/192.168.10.3/RECO_DR_CD_03_inblrdrceladm01' force,'o/192.168.10.3/RECO_DR_CD_04_inblrdrceladm01' force;

Diskgroup altered.

[root@inblrdrceladm01 ~]# cellcli -e list griddisk attributes name,status,asmmodestatus,asmdeactivationoutcome
         DATA_DR_CD_00_inblrdrceladm01   active  ONLINE  Yes
         DATA_DR_CD_01_inblrdrceladm01   active  ONLINE  Yes
         DATA_DR_CD_02_inblrdrceladm01   active  ONLINE  Yes
         DATA_DR_CD_03_inblrdrceladm01   active  ONLINE  Yes
         DATA_DR_CD_04_inblrdrceladm01   active  ONLINE  Yes
         DATA_DR_CD_05_inblrdrceladm01   active  ONLINE  Yes
         DBFS_DG_CD_02_inblrdrceladm01   active  ONLINE  Yes
         DBFS_DG_CD_03_inblrdrceladm01   active  ONLINE  Yes
         DBFS_DG_CD_04_inblrdrceladm01   active  ONLINE  Yes
         DBFS_DG_CD_05_inblrdrceladm01   active  ONLINE  Yes
         RECO_DR_CD_00_inblrdrceladm01   active  ONLINE  Yes
         RECO_DR_CD_01_inblrdrceladm01   active  ONLINE  Yes
         RECO_DR_CD_02_inblrdrceladm01   active  ONLINE  Yes
         RECO_DR_CD_03_inblrdrceladm01   active  ONLINE  Yes
         RECO_DR_CD_04_inblrdrceladm01   active  ONLINE  Yes
         RECO_DR_CD_05_inblrdrceladm01   active  ONLINE  Yes

# imageinfo

Kernel version: 2.6.39-400.128.17.el5uek #1 SMP Tue May 27 13:20:24 PDT 2014 x86_64
Cell version: OSS_11.2.3.3.1_LINUX.X64_140708
Cell rpm version: cell-11.2.3.3.1_LINUX.X64_140708-1

Active image version: 11.2.3.3.1.140708
Active image activated: 2014-08-14 13:03:50 +0530
Active image status: success
Active system partition on device: /dev/md6
Active software partition on device: /dev/md8

# imagehistory
Version                              : 11.2.3.2.1.130109
Image activation date                : 2013-09-24 13:49:36 +0530
Imaging mode                         : fresh
Imaging status                       : success

Version                              : 11.2.3.3.1.140708
Image activation date                : 2014-08-14 13:03:50 +0530
Imaging mode                         : out of partition upgrade
Imaging status                       : success


Finally, This Marathon Activity got Successfully Completed in approx. 18.5 Hours due to Rolling Fashion & had 11 RDBMS Home for BP24 Patching followed by catbundle to run OR Else in Non-Rolling Fashion it would have taken 1/3rd i.e. 6 Hours approx. but again at the cost of downtime, which wasn't possible. :)

Thanks & Have a Happy Reading - Manish