Even Better Script: Map ASM Disks to Physical Devices

Enjoyed last week post?

Cool, because looking further on the subject I found this pretty similar post, by Mohammad Nazmul Huda.

The additional script there is actually not working in my server, but the idea is great. So, I did just some small adjustments and it’s working pretty fine now:

# asm_report.sh (Adjusted by Matheus):

printf "\n%-15s %-14s %-11s %-7s\n" "ASM disk" "based on" "Minor,Major" "Size (MB)"
printf "%-15s %-14s %-11s %-7s\n" "===============" "=============" "===========" "========="
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome1
for i in `/usr/sbin/oracleasm listdisks`
do
v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'| sed 's/\"//g'`
v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | awk -v v_minor="$v_minor," -v v_major=$v_major '{if ( $5==v_minor ) { if ( $6==v_major ) { print $10}}}'`
v_size_bt=`blockdev --getsize64 /dev/${v_device}`
v_size=`expr $v_size_bt / 1024 / 1024`
Total_size=`expr $Total_size + $v_size`
Formated_size=`echo $v_size | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
printf "%-15s %-14s %-11s %-7s\n" $v_asmdisk "/dev/$v_device" "[$v_minor $v_major]" $Formated_size
done
Formated_Total_size=`echo $Total_size | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
printf "\nTotal (MB): %43s\n\n" $Formated_Total_size

Ok, and how it works?
[root@greporasrv ~]# sh asm_report.sh

ASM disk        based on      Minor,Major Size (MB)
=============== ============= =========== =========
DATA01          /dev/sdg1     [8 97]       255,999
DATA02          /dev/sdh1     [8 113]      255,999
DATA03          /dev/sdi1     [8 129]      255,999
DATA04          /dev/sdj1     [8 145]      255,999
FRA01           /dev/sdk1     [8 161]      307,199

Total (MB): 1,331,195

Even better, right?

Cheers!

CRS-10051: CVU found following errors with Clusterware setup : PRCW-1015 : Wallet % does not exist

Hello all!
So, recently I found this error in a CRS alert log from a client environment. Interesting error…

2018-03-26 16:33:53.277 [SRVM(9624)]CRS-10051: CVU found following errors with Clusterware setup : PRCW-1015 : Wallet greporadb does not exist. 
CLSW-9: The cluster wallet to be operated on does not exist. :[1015]PRCW-1015 : Wallet greporadb does not exist.

I found this also related to the error:

PRVG-1512 : Failed to retrieve current selection of public and private network classifications

So, it was mapped to known Bug 18234669, as per described in CRS-10051: CVU Found Following Errors With Clusterware Setup :PRCW-1015 : Wallet ora603ut does not exist (Doc ID 2008466.1).

Ok, but what to do?

1) In case you have the wallet, you can simply add it to the database:

crsctl add wallet -type CVUDB -name [dbname]

2) In case you haven’t, you can simply disable the resource ora.cvu, that is the one checking this:

> Checking Status

[root@grepora-srv ~]# crsctl stat res ora.cvu -p | grep CHECK_RESULT
CHECK_RESULTS=PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015]

> Disable CVU:

oracle:grepora-srv: srvctl disable cvu
oracle:grepora-srv:
oracle:grepora-srv: crsctl stat res ora.cvu -p | grep ENABLED
ENABLED=0
oracle:grepora-srv: srvctl status cvu                       
CVU is disabled

Hope it helps!

OEM: The number of hanging transactions are hang_trans is %

Hi all!
So, today is quickie one, just to make the links. Seems this message from OEM is not clear enough for some people, specially regarding non-specialists in Oracle: This means something is in lock in your database!

If this is the case, contact a DBA.

If you ARE a DBA, you may want to read this post about easy locating and solving locks: Solving Simple Locks Through @lock2s and @killlocker.

Also, if the session if from DBLink, is always useful to read this: Lock by DBLink – How to locate the remote session?

There is also some additional/specific material about some issues and bugs in this regard here: Tag: LOCK.

I hope it helps!
Cheers!

Script: Map ASM Disks to Physical Devices

Hey all!
So, I had to map a couple ASM disks to physical devices. But it’s not direct, which causes some manual work.

To save me from this, I found this great post by Alejandro Vargas, with a very nice script to make this mapping easier.

I found however, it was done for RHEL/OEL 6 and older, and I’m in OEL7. So I did some small changes to adapt it.

Anyway, decided to share as this is a great script to have handy. 🙂

# Alejandro’s script (RHEL/OEL 6 and older):

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk  '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major]"
done

# Adjustments by Matheus (RHEL/OEL7):

#!/bin/ksh
for i in `/usr/sbin/oracleasm listdisks`
do
v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'`
v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done

# Example of execution:

[root@greporasrv]$ for i in `/usr/sbin/oracleasm listdisks`
> do
> v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'`
> v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
> v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
> v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
> echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
> done
ASM disk "DATA01" based on /dev/sdg1 [8 97]
ASM disk "DATA02" based on /dev/sdh1 [8 113]
ASM disk "DATA03" based on /dev/sdi1 [8 129]
ASM disk "DATA04" based on /dev/sdj1 [8 145]
ASM disk "FRA01" based on /dev/sdk1 [8 161]

Hope you enjoy it like I did.
Cheers!

Oracle Virtual Columns

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Virtual Columns

Since 11g is possible to create columns based on functions, not physically stored on database. See the example below:

create table sales
    (
       sales_id      number,
       cust_id       number,
       sales_amt     number,
       sale_category varchar2(6)
       generated always as
       (
          case
            when sales_amt <= 10000 then 'LOW' 
            when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM' 
            when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
            else 'ULTRA'
          end
       ) virtual
   );

It’s also interesting to raise that starting on Release 2, virtual columns can be used as foreign keys. Should be used as FK, not sure… but can….

Cheers!

ERROR: ´Failed to push ldap config data to libOvd for service instance “idstore.ldap” in JPS context “default”` in OSB 12C

The error below occurs on OSB server and AdminServer of the domain.

ERROR:

#### <> <> <> <1539087463845> <[severity-value: 4] [partition-id: 0] [partition-name: DOMAIN] > ailed. Reason:

There are 1 nested errors:

oracle.security.jps.JpsException: JPS-02592: Failed to push ldap config data to libOvd for service instance “idstore.ldap” in JPS context “default”, cause: org.xml.sax.SAXException: Error Parsing at line #2: 1.
org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.security.jps.internal.config.OpssCommonStartup.start(OpssCommonStartup.java:209)
at oracle.security.jps.wls.JpsWlsStartup.start(JpsWlsStartup.java:96)
at oracle.security.jps.JpsStartup.start(JpsStartup.java:215)
at oracle.security.jps.wls.JpsDefaultService.start(JpsDefaultService.java:74)
at weblogic.server.AbstractServerService.postConstruct(AbstractServerService.java:76)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.glassfish.hk2.utilities.reflection.ReflectionHelper.invoke(ReflectionHelper.java:1262)
at org.jvnet.hk2.internal.ClazzCreator.postConstructMe(ClazzCreator.java:332)
at org.jvnet.hk2.internal.ClazzCreator.create(ClazzCreator.java:374)
at org.jvnet.hk2.internal.SystemDescriptor.create(SystemDescriptor.java:471)
at org.glassfish.hk2.runlevel.internal.AsyncRunLevelContext.findOrCreate(AsyncRunLevelContext.java:228)
at org.glassfish.hk2.runlevel.RunLevelContext.findOrCreate(RunLevelContext.java:85)
at org.jvnet.hk2.internal.Utilities.createService(Utilities.java:2072)
at org.jvnet.hk2.internal.ServiceHandleImpl.getService(ServiceHandleImpl.java:114)
at org.jvnet.hk2.internal.ServiceHandleImpl.getService(ServiceHandleImpl.java:88)
at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.oneJob(CurrentTaskFuture.java:1213)
at org.glassfish.hk2.runlevel.internal.CurrentTaskFuture$QueueRunner.run(CurrentTaskFuture.java:1144)
at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:643)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:348)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:333)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:54)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:617)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:397)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:346)
Caused by: oracle.security.jps.service.idstore.IdentityStoreException: JPS-02592: Failed to push ldap config data to libOvd for service instance “idstore.ldap” in JPS context “default”, cause: org.xml.sax.SAXException: Error Parsing at line #2: 1.
org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.security.jps.internal.idstore.util.LibOvdUtil$LibOvdDataPusherImpl.pushLdapDataToLibOvdIntenal(LibOvdUtil.java:641)
at oracle.security.jps.internal.idstore.util.LibOvdUtil$LibOvdDataPusherImpl.pushLdapDataToLibOvd(LibOvdUtil.java:593)
at oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider$NoLibOvd.getInstance(LdapIdentityStoreProvider.java:274)
at oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider.getInstance(LdapIdentityStoreProvider.java:118)
at oracle.security.jps.internal.idstore.ldap.LdapIdentityStoreProvider.getInstance(LdapIdentityStoreProvider.java:125)
at oracle.security.opss.internal.runtime.ServiceContextManagerImpl.createContextInternal(ServiceContextManagerImpl.java:636)
at oracle.security.opss.internal.runtime.ServiceContextManagerImpl.createDefaultContext(ServiceContextManagerImpl.java:319)
at oracle.security.opss.internal.runtime.ServiceContextManagerImpl.initialize(ServiceContextManagerImpl.java:241)
at oracle.security.jps.internal.config.OpssCommonStartup$1.run(OpssCommonStartup.java:150)
at java.security.AccessController.doPrivileged(Native Method)
at oracle.security.jps.internal.config.OpssCommonStartup.start(OpssCommonStartup.java:134)
… 26 more
Caused by: org.xml.sax.SAXException: Error Parsing at line #2: 1.
org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.ods.virtualization.engine.config.parsers.ConfigErrorHandler.fatalError(ConfigErrorHandler.java:103)
at oracle.xml.parser.v2.XMLError.flushErrorHandler(XMLError.java:435)
at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:290)
at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:476)
at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:417)
at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:246)
at oracle.xml.jaxp.JXDocumentBuilder.parse(JXDocumentBuilder.java:175)
at oracle.xml.jaxp.JXDocumentBuilder.parse(JXDocumentBuilder.java:131)
at oracle.ods.virtualization.engine.config.parsers.ConfigParser.parseConfiguration(ConfigParser.java:335)
at oracle.ods.virtualization.engine.config.parsers.ConfigParser.parseConfiguration(ConfigParser.java:307)
at oracle.ods.virtualization.engine.config.parsers.ConfigParser.parseConfiguration(ConfigParser.java:292)
at oracle.ods.virtualization.config.ConfigureAdapters.doConfigure(ConfigureAdapters.java:950)
at oracle.ods.virtualization.config.ConfigureAdapters.configure(ConfigureAdapters.java:911)
at oracle.security.jps.internal.idstore.util.LibOvdUtil$LibOvdDataPusherImpl.pushLdapDataToLibOvdIntenal(LibOvdUtil.java:632)
… 36 more
Caused by: org.xml.sax.SAXParseException; lineNumber: 2; columnNumber: 1; : XML-20108: (Fatal Error) Start of root element expected.
at oracle.xml.parser.v2.XMLError.flushErrorHandler(XMLError.java:425)
… 48 more

Probably Corrupted the file adapters.os_xml and file was empty.

SOLUTION:

1) Take a backup of $DOMAIN_HOME/config/fmwconfig/ovd/default/adapters.os_xml
2) Delete corrupted adapters.os_xml
3) Copy adapters.os_xml from $MW_HOME/oracle_common/modules/oracle.ovd/templates/ to $DOMAIN_HOME/config/fmwconfig/ovd/default/
4) Restart the server.

That’s all for today.

Reduce Exadata Core Count

Ok, so I was preparing for a DC services migration with a client and this would involve resizing the CPU count of Exadatas for better attending those services. This way, one of the steps will require reduce CPU counts in one of the sites to be aligned with the license terms.

Checking for the steps to accomplish that, I found references to change CPU and core count, but always described in the case of increasing allocation. As per 2.7 Increasing the Number of Active Cores on Database Servers. But not so much about reducing, as this seems to be unusual…

Also considering that the planned change would be within the minimum number requirement: 2.1 Restrictions for Capacity-On-Demand on Oracle Exadata Database Machine.

Reviewing on MOS, we found the When Attempting to Change the Number of Cores, Errors With: DBM-10004 – Decreasing the Number of Active Cores is not Supported ( Doc ID 2177634.1 ), pointing to use the clause “FORCE” on “ALTER DBSERVER pendingCoreCount =x” command.

And this worked. I just disabled the iaasMode to play safe. Have a look:

[root@grepora01~]# dbmcli
DBMCLI: Release  - Production on Mon Jan 05 01:10:12 EEST 2019
Copyright (c) 2007, 2014, Oracle.  All rights reserved.
DBMCLI> LIST DBSERVER attributes coreCount
	 36/44
DBMCLI> ALTER DBSERVER pendingCoreCount = 24 force
DBM-10022: At least 26 physical cores need to be active in order to support IaaS.
DBMCLI> ALTER DBSERVER iaasMode = "off"
DBServer exadb01 successfully altered
DBMCLI> ALTER DBSERVER pendingCoreCount = 24 force
DBServer grepora01 successfully altered. Please reboot the system to make the new pendingCoreCount effective.
DBMCLI> LIST DBSERVER attributes pendingCoreCount
24/44

–> Restart the server
After restarting, it should look like:

DBMCLI> LIST DBSERVER attributes coreCount
	 24/44
DBMCLI> LIST DBSERVER attributes pendingCoreCount

Hope this helps!

Oracle: Easily Decoding ROWID

Hi all,
Recently I needed to decode the rowid so I could find some information about it, I found this bit of code that I thought useful:

SET SERVEROUTPUT ON
DECLARE
v_rid VARCHAR2(20) ;
v_type NUMBER;
v_obj NUMBER;
v_rfno NUMBER;
v_bno NUMBER;
v_rno NUMBER;
BEGIN
v_rid := 'AAAAASAABAAAADxAAb';
dbms_output.put_line('Row_ID = "'||v_rid||'"');
dbms_rowid.rowid_info(CHARTOROWID(v_rid), v_type, v_obj, v_rfno, v_bno, v_rno);
IF v_type = 0 THEN
dbms_output.put_line('RowID Type -> Restricted');
ELSE dbms_output.put_line('RowID Type -> Extended');
END IF;
dbms_output.put_line('Object ID = "'||v_obj||'"');
dbms_output.put_line('Relative File Number = "'||v_rfno||'"');
dbms_output.put_line('Block Number = "'||v_bno||'"');
dbms_output.put_line('Row Number = "'||v_rno||'"');
END;
/

Note that I have hard-coded the rowid but it is relatively easy to either edit this or indeed to incorporate this into a procedure.

Here’s the sample output

Row_ID = "AAAAASAABAAAADxAAb"
RowID Type -> Extended
Object ID = "18"
Relative File Number = "1"
Block Number = "241"
Row Number = "27"

Hope this helps!
Cheers!

11g Improvements: New Grants

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about some of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes, today actually 2 small things:

Execute Grant on Directories

In 10g was possible to grant READ and/or WRITE, but this also allowed executing the ORACLE_LOADER access driver. In 11g, only a user that has been given EXECUTE access to the directory object is allowed to run programs in it. This should be granted using:

grant EXECUTE on DIR_EXAMPLE;

DATABASE_ROLE constant for SYS_CONTEXT

In 11g the context procedure can also give DATABASE_ROLE, as it might be used as check for certain procedures to run only on standbys or to avoid it, for example.

Between values are: PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY and SNAPSHOT STANDBY. This can be executed this way:

SELECT sys_context('USERENV', 'DATABASE_ROLE') FROM dual;

You probably know about that, right?
Anyway, always good to remember!

Cheers!

OEM Information Reports: ORA-00600 [kpndbcon-svchpnotNULL]

Having this error from an Information Report?

ORA-00600 [kpndbcon-svchpnotNULL]
ORA-00600: internal error code, arguments: [kpndbcon-svchpnotNULL], [], [], [], [], [], [], [], [], [], [], []

Don’t worry… Basically this is not an Oracle direct issue , the cause of this error is that while the report is running (it takes 2 or 3 minutes) one of the following happens:

  • The Database Session in the OEM Repository (Database Repository) is killed.
  • The Database Session in the Target Database (where OEM has to connect and get the data) is killed.
  • There is network issues between OEM Repository and the Target database causing “time outs” or that the session finishes erroneously. .
  • High workload in one database causes “time out” making the session finished erroneously.
  • So basically this is a communication problem, between the OEM Repository and the database from where the data is being gotten.
  • To keep reports like this running with database links is something that Oracle doesn’t support at all because of any network issue can cause that the report gets errors, you can read the following notes:

Some reference about it:

  • ORA-00600 [kpndbcon-svchpnotNULL] Errors (Doc ID 1615517.1)
  • ORA-00600 [kpndbcon-svchpnotNULL] query through dblink (Doc ID 1490700.1)
  • Information Publisher Report fails with Error Rendering Element. Exception: ORA-00600 [kpndbcon-svchpnotNULL] (Doc ID 1930280.1)


So what’s the solution
?
The solution here is easy, just re-run it.

Hope it helps. Cheers!