Jun 02

ESXi Web UI

I run mainly on Macs, which means OS X. Unfortunately that means I don’t have access to the GUI of vmware. As of 6.0 U2, vmware has a very slick web UI built into it. Just look at the following article, all I had to do was ssh to my ESXi server and run:

esxcli software vib install -v http://download3.vmware.com/software/vmw-tools/esxui/esxui_signed.vib

 

Web Interface for ESXi without vCenter

Mar 29

ASM du -s

one of my biggest complaints about ASM is the lack of the ability to do the equivalent of “du -hs *” to view the size of all the directories. I found a little piece of shell script that does just this. It is extremely slow but it is very helpful!

dg=RECO; for dir in $(asmcmd ls $dg); do echo $dg/$dir; asmcmd du $dg/$dir; done

Just change the first argment “dg=RECO” to the actual disc group you want to examine.

Mar 08

HOTSOS In Texas!

I am currently in Texas at the HOTSOS Symposium. I look forward to this conference every year. There are some brilliant people here, and it is a great place to learn new things. Kerry Osborn did a great live presentation yesterday, can’t wait to get home and try some of this stuff out. Also saw a great presentation by Tanel Poder.

The food down here is amazing too! We went to the Gas Monkey Bar and Grill last night and the food was amazing. I love traveling for work. I mean it is more fun to travel just for fun, but when I can learn something new, and have someone else pickup the majority of the cost, that is pretty amazing!

Feb 08

DBSNMP By Way Of EMCLI

Currently I am changing passwords in several hundred databases, not that uncommon of a task for any DBA. Unfortunately it is time to rotate the dbsnmp password. Usually I would log into OEM after changing the password and manually update the password in the monitoring configuration for each and every database. This is fine if there are only a few passwords, but a huge pain when you are dealing with hundreds. The documentation oracle provides isn’t completely clear [1592390.1], at least it wasn’t to me at first.

First I would connect to the database as the sys user and change the password for dbsnmp

alter user dbsnmp identified by MyFancyPassword;

Then you connect to EM through emcli and update the monitoring credentials. For this post lets assume my Oracle RAC cluster has the name “MyRAC” which contains two nodes “MyRAC1” and “MyRAC2”. We don’t really need to know the individual instance names since the dbsnmp user is shared between both nodes.

Connect to the repository

$ emcli login -username=sysman
Enter password :

Login successful
oracle@dcaxoem1:/home/oracle
$ emcli sync
Synchronized successfully

Now you can change the password via emcli

emcli set_monitoring_credential -target_type="rac_database" -target_name="MyRAC" -set_name="DBCredsMonitoring" -cred_type="DBCreds" -attributes="DBUserName:dbsnmp;DBPassword:MyFancyPassword;DBRole:Normal"

Where I ran into trouble was the last argument, if you look at the monitoring configuration page for a database, it becomes clear what is being asked for

editdbsmp

The role is the same role you would select on the monitoring setup page, Normal or SYSDBA.

The syntax is slightly different if you are doing a single instance, but basically the same idea

emcli set_monitoring_credential -target_type=oracle_database -target_name="ora1" -set_name="DBMonCred" -cred_type="DBCreds" -attributes=”DBUserName:dbsnmp;DBPassword:MyFancyPassword;DBRole:Noraml”

So I am going to modify the change password proceedure to spool out these commands and just run them against OEM when I have completed changing the passwords on the databases.

Nov 17

Where Are My Nodes?

I should mention the current setup is a 4 node RAC database with a dataguard managed standby comprised of a 3 node RAC database cluster. The current database version is 11.2.0.4

While writing a shell script today, I needed to know from connecting to a database in a cluster what all the related nodes were. This isn’t a big deal, I could simply query gv$instance to pull a list of the host names, and the associated instances.

SELECT host_name, instance_name, instance_number
    FROM gv$instance
ORDER BY instance_number
/

HOST_NAME       INSTANCE_NAME   INSTANCE_NUMBER
--------------- --------------- ---------------
homeOELdb01     orapdb1                       1
homeOELdb02     orapdb2                       2
homeOELdb03     orapdb3                       3
homeOELdb04     orapdb4                       4

But what about the standby nodes? I can query v$archive_dest_status

SELECT destination
  FROM V$ARCHIVE_DEST_STATUS
 WHERE status = 'VALID' AND type = 'PHYSICAL'
/

DESTINATION
--------------------
orasdb

Ok, great, that is the name of my standby database cluster, but there are three nodes! I want to see what is going where! I could use dgmgrl,

DGMGRL> show configuration

Configuration - orapdb_dgconf

  Protection Mode: MaxPerformance
  Databases:
    orapdb - Primary database
    orasdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database 'orasdb'

Database - orasdb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      21.80 MByte/s
  Real Time Query: OFF
  Instance(s):
    orasdb1 (apply instance)
    orasdb2
    orasdb3

Database Status:
SUCCESS

So that gives us the databases in the standby, but parsing this out would be a pain. I just want to generate a report, and it is much easier to be able to query the database to generate my output. However I couldn’t find a place to do this!

Enter the X$DRC view. I could find very little about this view itself. With any X$ table, the view is rather cryptic, but I was able to get enough information out of it to solve my problem.

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 OBJECT_ID                                          NUMBER
 ATTRIBUTE                                          VARCHAR2(30)
 VALUE                                              VARCHAR2(512)
 PARENT_ID                                          VARCHAR2(15)
 STATUS                                             VARCHAR2(30)
 MESSAGE                                            VARCHAR2(256)
 ERRNUM                                             NUMBER
 VALUE_RAW                                          RAW(512)

Because there isn’t a lot of information on this table I had to make some assumptions, based on the data

  SELECT inst_id,
         object_id,
         parent_id,
         attribute,
         VALUE
    FROM X$DRC
ORDER BY inst_id, object_id, parent_id
/

   INST_ID  OBJECT_ID PARENT_ID       ATTRIBUTE                      VALUE
---------- ---------- --------------- ------------------------------ --------------------
         1       4096                 DRC                            orapdb_dgconf
         1       4096                 intended_state                 ONLINE
         1       4096                 protection_mode                MaxPerformance
         1       4096                 enabled                        YES
         1       4096                 fast_start_failover            DISABLED
         1   50331648 4096            SITE                           orapdb
         1   50397184 50331648        DATABASE                       orapdb
         1   50397184                 intended_state                 READ-WRITE-XPTON
         1   50397184                 role                           PRIMARY
         1   50397184                 enabled                        YES
         1   50397184                 RAC                            YES
         1   50397184                 connect_string                 orapdb
         1   50397185 50331648        INSTANCE                       orapdb4
         1   50397185                 host                           homeOELdb04
         1   50397186 50331648        INSTANCE                       orapdb2
         1   50397186                 host                           homeOELdb02
         1   50397187 50331648        INSTANCE                       orapdb1
         1   50397187                 host                           homeOELdb01
         1   50397188 50331648        INSTANCE                       orapdb3
         1   50397188                 host                           homeOELdb03
         1   67108864 4096            SITE                           orasdb
         1   67174400 67108864        DATABASE                       orasdb
         1   67174400                 connect_string                 orasdb
         1   67174400                 intended_state                 PHYSICAL-APPLY-ON
         1   67174400                 RAC                            YES
         1   67174400                 role                           PHYSICAL
         1   67174400                 enabled                        YES
         1   67174401 67108864        INSTANCE                       orasdb1
         1   67174401                 host                           homeOELdb01s
         1   67174402 67108864        INSTANCE                       orasdb2
         1   67174402                 host                           homeOELdb02s
         1   67174403 67108864        INSTANCE                       orasdb3
         1   67174403                 host                           homeOELdb03s

You can see the primary instances and their hosts listed above. To pull the standby databases out, I used the following query. I am guessing this could be made a lot cleaner, but I ran out of brain power while trying to optimize it, so here is the current query.

SELECT b.VALUE as HOST, a.VALUE as INSTANCE
  FROM X$DRC a, X$DRC b
 WHERE     a.parent_id IN (SELECT object_id
                             FROM X$DRC
                            WHERE     VALUE NOT IN (SELECT db_unique_name
                                                      FROM v$database)
                                  AND attribute = 'SITE')
       AND a.attribute = 'INSTANCE'
       AND a.object_id = b.object_id
       AND b.ATTRIBUTE = 'host'
/

HOST                       INSTANCE
-------------------------- ----------
homeOELdb01s               orasdb1
homeOELdb02s               orasdb2
homeOELdb03s               orasdb3

I now have the information I want to include in my report. You could easily modify this to get all the nodes in the primary and the standby, but I would prefer to use gv$instance to get the primary nodes.

1/14/2016 Update

I didn’t notice this until much much later, if data guard is not enabled, The query on X$DRC will fail.

select * from X$DRC
              *
ERROR at line 1:
ORA-16525: The Oracle Data Guard broker is not yet available.

I was using this to get my primary and standby nodes, and union them together, this was returning an empty set to me. I was spooling this information out to a text file to be used with a shell script, so my solution was to just break into two pieces, first spool out any RAC nodes, then check to see if there are any standby destinations enabled, and if so then spool out standby databases. This seems to work fine.

This is why it isn’t safe to use X$ tables, they aren’t documented and you may not get expected behavior out of them. I would have assumed I would just back zero rows, but that would be to assume the X$ table is like a V$ table. Not true, the X$ tables are used for reading portions of the oracle memory, mostly, but can also bull information like control file information into the memory to be read. So if the memory segment the data guard, or possibly the data guard broker filed don’t exist, this table cannot function.

To learn more about X$ tables, I suggest these three sites, they are my go to for X$ information

Tanner Poder: Oracle X$ tables – Part 1 – Where do they get their data from?

Morgan’s Library: Oracle X$ Structures

ORAFAQ: X$ Table naming structures

 

Sep 18

Formatting SQL in Oracle

I was looking for a way to format the output from EXPAND_SQL_TEXT and came across this post. I would really rather have something in the database that I could do this with. I would prefer not to spool anything out, but it works very well.

Just another SQL beautifier

Aug 10

Xlib: No protocol specified

While trying to connect to an AIX box, and start dbca, I got the following error

Xlib: connection to "192.168.0.100:0.0" refused by server
Xlib: No protocol specified

This is an issue with Xming security. I changed the shortcut on my windows desktop to:

"C:\Program Files (x86)\Xming\Xming.exe" :2 -clipboard -multiwindow -ac

I was immediately able to fire up dbca.

the -ac option disable access control restrictions, so you should never do this over a public network, it could open up a massive hole in your computer and allow anyone to connect.

Jun 11

ORA-00904: “UTL_RAW”.”CAST_FROM_NUMBER”: invalid identifier

I have been migrating single instance databases to RAC databases and had several databases that filled up the diag directory spewing out alerts. There is a bug that can cause some of these, but in my case it was much simpler. Simple configuration mistakes

The first error was due to the existance of a spfile on the file system that was being read in.

the spfile was create in ASM, and $ORACLE_HOME/dbs/initdp0db#.ora was created on both nodes

However on node1, we still had $ORACLE_HOME/dbs/spfiledp0db1.ora

checking the spfile parameter in the database confirmed that this was the file being read in. The alert log wasn’t very helpful just spewing out the following.

Emon ping encountered error 12801
Errors in file /u01/app/oracle/diag/rdbms/dp0db/dp0db1/trace/dp0db1_q00p_617988.trc:
ORA-12801: error signaled in parallel query server PZ99, instance node2:dp0db2 (2)
ORA-00904: "UTL_RAW"."CAST_FROM_NUMBER": invalid identifier
Thu Jun 11 11:54:19 2015

The second issue I ran into that also filled up the diag directory was due to bad permissions on one of the nodes on the audit directory. This generated the exact same error as above in the log file.

If you see this error check the following

  • on each node check the value of the spfile in the databse, they should both be pointing to the ASM spfile
  • check the location of the audit directory and make sure it exists and oracle can write to it

These were simple mistakes that caused a huge headache.

Mar 03

HOTSOS 2015

I am currently at the HOTSOS Symposium. This is my favorite conference of the year. The people here are incredibly smart, the presentations are very technical, and it is just a good time. I look forward to this conference all year. I have learned some great new things that I am looking forward to trying out when I get home.

Nov 14

Why Didn’t Oracle Export That?

Ever wonder where Oracle keeps track of objects that won’t be exported?

select * from sys.ku_noexp_tab;

This table contains a list of objects that will be ignored during an export.

Older posts «

» Newer posts