Thursday, March 24, 2011

Oracle Stops All Software Development For Intel Itanium Microprocessor

REDWOOD SHORES, Calif., March 22, 2011

After multiple conversations with Intel senior management Oracle has decided to discontinue all software development on the Intel Itanium microprocessor. Intel management made it clear that their strategic focus is on their x86 microprocessor and that Itanium was nearing the end of its life.

Both Microsoft and RedHat have already stopped developing software for Itanium. HP CEO Leo Apotheker made no mention of Itanium in his long and detailed presentation on the future strategic direction of HP.

Oracle will continue to provide customers with support for existing versions of Oracle software products that already run on Itanium.

The following url is the respone from HP.

http://www.hp.com/hpinfo/newsroom/press/2011/110323c.html?mtxs=rss-corp-news

Thursday, March 17, 2011

INACTIVE TOAD

We have a new policy regrading 3rd party GUI tools do to resource usage. Here it the policy note:


EFFECTIVE IMMEDIATELY: All users who are using TOAD or some other tool to query the warehouse, if your session is NOT active, please LOG OFF. Do not let your session or sessions sit idol.

Over the last few days we’ve been running all kinds of stats to try to better understand how we can tune our system, to support the enhanced volume pumping thru. We’ve learned that although your session may be idol and not doing anything, system resources are not being released and are staying engaged.

Please turn this process into a habit.

We can use ALL the system resources we can get.


Thanks in advance.

Tuesday, March 15, 2011

Bug 7581347

I currently support 44 Oracle instances on 40 different servers and that number is growing as new projects come along. The majority of these are 10gR2. A few are 11gR1 and one is 11gR2. We will be moving most of them if not all of them to 11g this calendar year. Yesterday one of the 10.2.0.4 instances residing on 64bit Red Hat Linux apparently hit a combination that opened a can of worms that I identified as Bug 7581347.

Here is the summary of the event:

Enduser was attempting to create a temp table in a process.

Step 15: SQL> create table tmp_somename_tb_12345 as select ...
Step 15: create table tmp_somename_tb_12345 as select Step 15: * Step 15: ERROR at line 1:
Step 15: ORA-00955: name is already used by an existing object

ERROR at line 1:
Step 15: ORA-38301: can not perform DDL/DML over objects in Recycle Bin

First I had them try this statement:

drop table purge;

Logged in as the table owner in question and of course replacing the with the table_name that was dropped and it now generating the error.

Result: Same errors generated.

Then I had them have the table_owner issue a:

purge user_recyclebin;

Note (That if this is issued all the tables that are in that users recycle_bin will be purged and therefore no longer useable for undo).

After purging the user recycle bin and reissuing the statements they got the following errors:

ORA-00600: internal error code, arguments: [ktagetg0-1], [], [], [], [], [], [], []

This ora600 is pointing (as you might expect) toward: (space/spcmgmt support for DML locks and temporary tables associated with table access). And it stands for “Kernel Transaction Access Get Generic DML lock.

This is a bug (7581347). It is fixed in 10.2.0.5. The only workaround given by Oracle was: “Do not drop objects whilst DML is using them.” Not to impressed with that one.

The row cache entry for the object seems to have been corrupted. There were only two things short of an upgrade that I could think of at that time:

1) Flushing the shared pool. (Note: that there may be somewhat of a temporary negative impact on performance when you flush all the SQL from the shared pool).

And if that does not work then:

2) Restart the database.

The idea here, being to try and clear out the row cache.

We ended up having to bounce the instance. And, keeping our fingers crossed, so far haven’t seen it again… yet.

Thx

Friday, March 11, 2011

A GREAT VIEW

The V$PWFILE_USERS view lists all users for an instance who have been granted the SYSDBA and/or the SYSOPER privileges. This information is derived from the password file. The views contents are:
Column Datatype Description
USERNAME VARCHAR2(30) Name of the user that is contained in the password file
SYSDBA VARCHAR2(5) Indicates whether the user can connect with SYSDBA privileges
(TRUE) or not (FALSE)
SYSOPER VARCHAR2(5) Indicates whether the user can connect with SYSOPER privileges
(TRUE) or not (FALSE)
SYSASM VARCHAR2(5) Indicates whether the user can connect with SYSASM privileges
(TRUE) or not (FALSE)

If you want to grant a user the sysdba privilege the following items need to be in place:
1) The remote_login_password parameter must be set to either “shared” or “exclusive”. (“shared” then it can be used by other instances; “exclusive” then it can only be used by that single instance). You can check the parameter setting by issuing the following statement:
SQL> show parameter password
NAME TYPE VALUE
------------------------------------ ----------- ----------remote_login_passwordfile string EXCLUSIVE

If it is not set or set to “shared” or “exclusive” you can add/change the parameter in the init.ora file:
remote_login_passwordfile=EXCLUSIVE
2) You must have or create an orapwd password file using the orapwd utility:
$ orapwd file= password= nosysdba= entries= force= ignorecase=

The file is as you would expect the name of the file that will hold the password information. Unless you path it, the file location will default to your current directory.

The password is the password for the SYS user.

The nosysdba is to identify whether or not to shut out the sysdba loging.

The entries are the maximum number of users that can be granted the SYSDBA or SYSOPER privilege.

The force identifies whether or not to overwrite the existing password file.

The ignorecase identifies if the passwords are case sensitive or not.

Note: The contents of the orapwd file are encrypted and are therefore unreadable. Also, if there is no orapwd password file and you attempt to grant sysdba to a user it will fail and generate an ORA-01994 error (GRANT failed: cannot add user to public password file). And an important change to be aware of is that until 11g passwords were not case sensitive; however, since the introduction of 11g passwords can be either case or non-case sensitive. The values are either required or optional as follows: file=required; password=optional; nosysdba=optional; entries=required; force=optional; ignorecase=optional. It is also important to remember that there can be no spaces on either side of the ‘=’ (equals) sign.

One last thing is that you issue an orapwd help=y to get the usage:
Usage: orapwd file= password= entries= force=
ignorecase= nosysdba=

3) Grant sysdba to ; Grant succeeded.

Thursday, March 10, 2011

HISTORY – SYSDBA

In life it is part of the natural process for things to come and go; to age out and change over time. And so it is with “connect internal” (I kind of miss that one), and so shall it be with SYSDBA at least in regard to ASM. Beginning with Oracle 11g for ASM there is a new sheriff in town, his name is SYSASM. This new role is a specialized version of SYSDBA for all things ASM instance.

A couple of things to keep in mind are, one: you can still use SYSDBA for starting up the ASM instance, however when you do you will generate a warning message in the alert-log that says:

WARNING: Deprecated privilege SYSDBA for command ‘STARTUP’

Two: you cannot use the SYSASM role on a RDBMS instance. If you do you will generate an ORA-01031 Insufficient Privileges error message.

Well for now you can still get away with using the SYSDBA role for ASM startup but in future versions it will go the way of ‘connect internal’.

Wednesday, March 09, 2011

ALL OR NONE

The night before last night we encountered the following on one of our instances (Oracle 11.1.0.7 on RedHat Linux 64bit OS):

sqlplus / as sysdba resulted in the following error message:
ORA-01031: insufficient privileges

After some troubleshooting I found that I could connect as follows:
sqlplus /nolog
Connect sys/ as sysdba

I brought down the rdbms instance and then I brought down the ASM instance and the server was rebooted. Post reboot I brought up the ASM instance with no issues and then proceeded to attempt to startup the rdbms instance. However, this failed with the following errors:

ORA-00210: cannot open the specified control file
ORA-00202: control file: '+DGRP_RAID5_01_0000/control03.ctl'
ORA-17503: ksfdopn:2 Failed to open file +DGRP_RAID5_01_0000/control03.ctl
ORA-15001: diskgroup "DGRP_RAID5_01_0000" does not exist or is not mounted
ORA-15055: unable to connect to ASM instance
ORA-01031: insufficient privileges

I connected to the ASM instance and confirmed that the diskgroup was mounted and that the controlfiles were really there:

select group_number,name,state from v$asm_diskgroup;

GROUP_NUMBER NAME STATE
------------------ ------------------------------ -----------
1 DGRP_RAID5_01_0000 MOUNTED

select a.name FILE_ALIAS, b.name DISKGROUP_NAME
from v$asm_alias a, v$asm_diskgroup b
where a.group_number=b.group_number
order by a.name;

FILE_ALIAS DISKGROUP_NAME
---------------------------------------------------------------------- ------------------------
CONTROLFILE DGRP_RAID5_01_0000
CONTROLFILE DGRP_RAID5_01_0000
CONTROLFILE DGRP_RAID5_01_0000


I then traced it down to an entry in the sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES= (NONE)

I changed this to:
SQLNET.AUTHENTICATION_SERVICES= (ALL)

I was then able to login to sqlplus / as sysdba as expected and start the rdbms instance with no issues.

Tuesday, March 08, 2011

QUIESCE

Apparently this morning after, as part of a rebuild of the OEM Repository (11.1.0.7.0 on RedHat Linux 64bit) during the dropping of a queue table (…DROP_QUEUE_TABLE(queue_table => 'SYSMAN.MGMT_NOTIFY_QTABLE',force => TRUE);) so that the sysman account and management objects could be dropped and rebuilt the base went into a quiesce restricted mode and then came out as expected; however, for some reason the alert log shows that it went back into the quiesce state:

Tue Mar 08 08:59:13 2011
Database in quiesce mode
Database out of quiesce mode
Tue Mar 08 09:14:51 2011
Database in quiesce mode

This of course caused all sqlplus session connection attempts to hang. After issuing an (ALTER SYSTEM UNQUIESCE;) The base went back to normal as expected.

The (ALTER SYSTEM QUIESCE RESTRICTED) SQL statement to is used to quiesce the database. While the instance is in this mode of operation only the users SYS can connect as sysdba. All others users connections will hang and wait. If a SQLPlus session is already connected it will wait (hang) as well until the instance is unquiesce. Therefore no other users besides SYS and SYSTEM can start any new transactions, or new queries, etc. Only the SYS and SYSTEM users can execute the ALTER SYSTEM QUIESCE RESTRICTED SQL statement.

Also while the instance is in the quiesced state, no changes to the Resource Manager plan(s) can be made for the instance. And as noted earlier, the ALTER SYSTEM UNQUIESCE SQL statement can be issued to put the instance back into its normal mode of operation; thusly releasing all blocked activities.

A handy view to be aware of is the v$blocking_quiesce view. You can use it to help determine which sessions are blocking a quiesce from finishing.

The following link is to an Oracle document that gives more detail about quiesce:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/start004.htm

Thursday, March 03, 2011

Oracle Client Character MisMatch

One of my endusers just installed an Oracle 10g R2 client on their laptop and got the following error when trying to connect to one of my instances (Oracle 11.1.0.7.0 running on Red Hat Linux - the base has a UTF8 Charter Set to support the meta-data repository for the Unica Campaign tool): “Database character set (UTF8) and Client character set (WE8MSWIN1252) are different. Charter set conversion may cause unexpected results.”
So I told her to do the following:

Setting NLS_LANG
You can set the NLS_LANG environment variable to control globalization features.
Example 12-1 Configuring UTF8 Support in SQL*Plus on Windows
1. Ensure you have exited your current SQL*Plus session.
2. Open System from Start > Settings > Control Panel.
3. Click the Advanced tab and select Environment Variables.
4. Create a new environment variable, NLS_LANG, with a value of AMERICAN_AMERICA.UTF8.
5. You may need to restart Windows for this setting to take effect.
Thx