Well, it was bound to happen “sooner” than “later”; we hit our fist bug with a newly installed 11.2.0.2 instance. She is installed on 64bit Linux. This one is a sqlplus issue.
The bug report from Oracle is as follows:
Bug 10269193 - Wrong results with outer join and CASE expression optimization [ID 10269193.8]
Modified 27-MAY-2011 Type PATCH Status PUBLISHED
Bug 10269193 Wrong results with outer join and CASE expression optimization
This note gives a brief overview of bug 10269193.
The content was last updated on: 27-MAY-2011
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected 11.2.0.2
Platforms affected Generic (all / most platforms affected)
Wrong results are possible with outer join and case expression optimization containing a ROWID column.
Rediscovery Notes:
1. Wrong Results OR an ORA-600 in Parallel Query
2. Outer Join
3. CASE Expression
4. ROWID column is involved in CASE Expression
This bug is possible even when ROWID column or CASE expression is not explicitly present in the query, because
* A ROWID column can be added during query transformation
(eg. non-native full outer join)
* CASE and ROWID column can be added during view merge
A 10053 trace maybe needed to identify it.
Here is an example of what we saw:
If you make a query that (1) doesn’t select a case statement (2) from a left join with no matches, then the result is correct - no matches from the right table.
If you make a query that (1) selects a case statement (2) from a left join with no matches, then the result behaves as if there were actually matches in the right table.
On other, more reasonable databases:
If you write the same query (left join to a table with no matches), then you get the correct result regardless of whether or not there was a case statement in the select.
SELECT
CASE WHEN 1=1 THEN 'comment this out' END case_statement,
VAL_A should_be_A,
VAL_B should_be_null
FROM
(SELECT 'A' VAL_A from dual),
(SELECT 'B' VAL_B from dual)
WHERE
VAL_A = VAL_B(+);
SELECT
--CASE WHEN 1=1 THEN 'comment this out' END case_statement,
VAL_A should_be_A,
VAL_B should_be_null
FROM
(SELECT 'A' VAL_A from dual),
(SELECT 'B' VAL_B from dual)
WHERE
VAL_A = VAL_B(+);
The corrective patch set we applied was:
p10269193_112020_Linux-x86-64.zip
*** Since this was posted we have found that there is a workaround for this as well.
It appears that if you substitute a regular table where you have dual, it works correctly.
Thx
Monday, July 25, 2011
Wednesday, June 08, 2011
OUCH: Fixing Invalid Objects
One of our DBA’s hit an interrupt today when trying to recompile invalid object on one of the instances we support.
He said, “Please be careful when using the database upgrade assistant. After I ran:
dbua -silent -dbname $ORACLE_SID -oracleHome $ORACLE_HOME -sysDBAUserName SYS -sysDBAPassword $PASS -recompile_invalid_objects true
It put the database into RESTRICTED and UPGRADE modes, disconnecting all sessions, unfortunately. After I took the db out of restricted mode it still stayed in UPGRADE mode; the only remedy seem to be to bounce the database.” Which we did and everything went back to “normal” (FYI, “normal’ is just a setting on your clothes dryer.) and all production jobs were restarted.
Thx
He said, “Please be careful when using the database upgrade assistant. After I ran:
dbua -silent -dbname $ORACLE_SID -oracleHome $ORACLE_HOME -sysDBAUserName SYS -sysDBAPassword $PASS -recompile_invalid_objects true
It put the database into RESTRICTED and UPGRADE modes, disconnecting all sessions, unfortunately. After I took the db out of restricted mode it still stayed in UPGRADE mode; the only remedy seem to be to bounce the database.” Which we did and everything went back to “normal” (FYI, “normal’ is just a setting on your clothes dryer.) and all production jobs were restarted.
Thx
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
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.
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
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
Logged in as the table owner in question and of course replacing the
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.
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
remote_login_passwordfile=EXCLUSIVE
2) You must have or create an orapwd password file using the orapwd utility:
$ orapwd file=
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=
ignorecase=
3) Grant sysdba to
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’.
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.
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/
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
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
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
Thursday, February 24, 2011
SOME ALIAS I USE
Here are some alias' I like to setup in my .profile or my .bash_profile files:
alias oal='tail -500f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log' --Last 500 Lines from the Oracle Alert Log
alias otrace='cd $ORACLE_BASE/admin/$ORACLE_SID/bdump' --Shortcut to CD to the Oracle Trace file directory.
alias init='cd $ORACLE_BASE/admin/$ORACLE_SID/pfile' --Shortcut to CD to the pfile directory for the Oracle Initialization file.
alias dba='cd /home//dba' --Shortcut to CD to my DBA Scripts directory.
alias tns='cd $ORACLE_HOME/network/admin' --Shortcut to CD to the tnsnames.ora file directory.
alias psg='ps -ef |grep' --Shortcut to look at certain system processes. Use psg
alias ll='ls -ltarF' --List out in timestamp order (oldest to newest), including hidden files, and '/' on directories.
alias oadmin='cd $ORACLE_HOME/rdbms/admin' --Shortcut to CD to the Oracle rdbms/admin directory.
alias lsd='ls -ltr | grep drw' --List only directories. Very handy with directories containing a lot of files.
alias oal='tail -500f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log' --Last 500 Lines from the Oracle Alert Log
alias otrace='cd $ORACLE_BASE/admin/$ORACLE_SID/bdump' --Shortcut to CD to the Oracle Trace file directory.
alias init='cd $ORACLE_BASE/admin/$ORACLE_SID/pfile' --Shortcut to CD to the pfile directory for the Oracle Initialization file.
alias dba='cd /home/
alias tns='cd $ORACLE_HOME/network/admin' --Shortcut to CD to the tnsnames.ora file directory.
alias psg='ps -ef |grep' --Shortcut to look at certain system processes. Use psg
alias ll='ls -ltarF' --List out in timestamp order (oldest to newest), including hidden files, and '/' on directories.
alias oadmin='cd $ORACLE_HOME/rdbms/admin' --Shortcut to CD to the Oracle rdbms/admin directory.
alias lsd='ls -ltr | grep drw' --List only directories. Very handy with directories containing a lot of files.
Wednesday, February 23, 2011
DBUA from 10gR2 to 11gR2 Issue
One of my fellow dba's hit this snag recently upgrading from 10r2 to 11r2: "I used Xming and Putty and was able to Install everything for Oracle 11gR2, Upgrade ASM to the new Grid Infrastructure just fine. Installed Oracle 11gR2. The problem was when I started to use the DBUA to upgrade my database from 10gR2 to 11gR2 DBUA showed me all the panels until I got to the very last panel where you would actually launch the upgrade process and it stopped working. No processes where running on the server and no information on the screen to indicate the steps were processing. I let it stay this was for over an hour. Oracle (SR) said I had to use another X-windows tool.
I used my trusty Tru64 server I have at my desk and it worked fine. The progress bar started displaying within one second."
A good FYI.
Thx
I used my trusty Tru64 server I have at my desk and it worked fine. The progress bar started displaying within one second."
A good FYI.
Thx
Thursday, February 17, 2011
A HIERARCHICAL APPROACH TO FILE-SYSTEM PRIVILEGE MANAGEMENT
When ever I am setting up a new Unix or Linux server I like to take in consideration the type of users that will be utilizing the box and divided them up into their appropriate groups by type of function. Once this is complete, I then determine level or access each of these types of users groups should have in regard to each other. For example:
I have identified the following types of user categories:
Customer/Client, Testers, Production executors, Developers, Database Administrators, System Administrators and Backup Support Team Members.
Now I will break them down into a privilege hierarchical arrangement (this arrangement is managed by using Unix groups). The groups would be as follows:
1. cust = customer
2. testr = testers
3. pord = production executors
4. dev = developers
5. dba = database administrators
6. sysa = system administrators
7. bkup = backup team members
Therefore each user would receive as their primary group the group which they are a part of. That is to say, as a DBA I would have dba set as my user’s primary group.
The next is to assign the secondary groups:
1. A customer would have a primary group of cust and no secondary groups.
2. A tester would have a primary group of testr and a secondary group of cust.
3. A production executor would have a primary group of prod and secondary groups of testr and cust.
4. A developer would have a primary group of dev and secondary groups of prod, testr and cust.
5. A Database Administrator would have a primary group of dba and secondary groups of dev, prod, testr and cust.
6. A system administrator would have primary group of system and root level access.
7. A backup team member would have primary group of bkup and would have no secondary groups.
I have identified the following types of user categories:
Customer/Client, Testers, Production executors, Developers, Database Administrators, System Administrators and Backup Support Team Members.
Now I will break them down into a privilege hierarchical arrangement (this arrangement is managed by using Unix groups). The groups would be as follows:
1. cust = customer
2. testr = testers
3. pord = production executors
4. dev = developers
5. dba = database administrators
6. sysa = system administrators
7. bkup = backup team members
Therefore each user would receive as their primary group the group which they are a part of. That is to say, as a DBA I would have dba set as my user’s primary group.
The next is to assign the secondary groups:
1. A customer would have a primary group of cust and no secondary groups.
2. A tester would have a primary group of testr and a secondary group of cust.
3. A production executor would have a primary group of prod and secondary groups of testr and cust.
4. A developer would have a primary group of dev and secondary groups of prod, testr and cust.
5. A Database Administrator would have a primary group of dba and secondary groups of dev, prod, testr and cust.
6. A system administrator would have primary group of system and root level access.
7. A backup team member would have primary group of bkup and would have no secondary groups.
Wednesday, February 16, 2011
BLOCK CORRUPTION!
We have been running into a block corruption error on a 10.2.0.4 Oracle instance running on RedHat Linux (kddummy_blkchk], [129], [5850]). The other day it hit us on an insert again due to a bad block error. This was the second week in a row we encountered this for the same process. The insert had ran for 4 hours and died on the commit! After some root-cause-analysis (RCA) it seems that this issue is pointing to a table logical error. There seems to be a problem with that table not the physical structure. We were thinking that this table may have gotten created with invalid indexe or something. This problem has now occurred in 3 different environments (dev, test, prod) for the same table object. The table is inserted into every weekend after a truncate is issued. However, if we do a CTAS and then an insert using only the temp table created in the CTAS it works fine without encountering the bad block error.
We opened an SR with Oracle and they said pretty much the same thing for the workaround; here is what they said about it:
ODM Action Plan Oracle Support
=== ODM Action Plan ===
In order to recreate the table you need to disable the DB_BLOCK_CHECKINg because in other case, it will fail with the ORA-600 error. We are removing 1 check layer in order to be able to recreate the table. We know there is a block corruption, but we know as well it is not affected any data on the table, so we can do it safely.
Process should be:
1.-Disable DB_BLOCK_CHECKING
2.-Recreate the table
3.-Re-enable DB_BLOCK_CHECKING
On the other hand, the steps you can follow in order to fix the corruption are
DROP TABLE .. PURGE;
or
ALTER TABLE .. MOVE ..;
or
Create table as select(CTAS)
or
export/import,etc
You don't need to follow all steps but only one.
Since we have been using the CTAS workaround we have no longer been receiving the error. Here are the steps we are using now for this object:
Drop table tabone;
CTAS tmp_tabone;
Rename tmp_tabone to tabone;
Here is a nice little piece of code if you just want to find out what object is corrupt (replace the file and blockid):
select tablespace_name, segment_type, owner, segment_name
from dba_extents
where file_id =
and between block_id AND block_id + blocks-1;
We opened an SR with Oracle and they said pretty much the same thing for the workaround; here is what they said about it:
ODM Action Plan Oracle Support
=== ODM Action Plan ===
In order to recreate the table you need to disable the DB_BLOCK_CHECKINg because in other case, it will fail with the ORA-600 error. We are removing 1 check layer in order to be able to recreate the table. We know there is a block corruption, but we know as well it is not affected any data on the table, so we can do it safely.
Process should be:
1.-Disable DB_BLOCK_CHECKING
2.-Recreate the table
3.-Re-enable DB_BLOCK_CHECKING
On the other hand, the steps you can follow in order to fix the corruption are
DROP TABLE .. PURGE;
or
ALTER TABLE .. MOVE ..;
or
Create table as select(CTAS)
or
export/import,etc
You don't need to follow all steps but only one.
Since we have been using the CTAS workaround we have no longer been receiving the error. Here are the steps we are using now for this object:
Drop table tabone;
CTAS tmp_tabone;
Rename tmp_tabone to tabone;
Here is a nice little piece of code if you just want to find out what object is corrupt (replace the file and blockid):
select tablespace_name, segment_type, owner, segment_name
from dba_extents
where file_id =
and
Tuesday, February 15, 2011
RMAN 11G Issue: ORA-20446 and ORA-06512
Ran into this one today when trying to setup a new tablespace backup via RMAN in a newly installed Oracle 11.1.0.7 instance on RedHat Linux:
ORA-20446: The owner of the job is not registered ORA-06512: at "SYSMAN.MGMT_JOBS", line 168 ORA-06512: at "SYSMAN.MGMT_JOBS", line 86 ORA-06512: at line 1
It turns out that this is a know bug with 11G. With a simple workaround:
Login as SYSMAN and execute the following command for the users in question:
EXECUTE MGMT_USER.MAKE_EM_USER(‘’);
ORA-20446: The owner of the job is not registered ORA-06512: at "SYSMAN.MGMT_JOBS", line 168 ORA-06512: at "SYSMAN.MGMT_JOBS", line 86 ORA-06512: at line 1
It turns out that this is a know bug with 11G. With a simple workaround:
Login as SYSMAN and execute the following command for the users in question:
EXECUTE MGMT_USER.MAKE_EM_USER(‘
Wednesday, February 09, 2011
NetBackup S/W Upgrade Required for Upgrade to Oracle 11R2
Some versions of NetBackup will not run with Oracle 11gR2 unless you update it at least 6.5.6 or 7.
From Oracle ORA-07445 [Strcpy()+48] during RMAN backup or restore to tape using Oracle 11.2 [ID 959015.1]
Metalink note 959015.1
The latest version of Netbackup is not compatible with 11gR2, an upgrade to Netbackup will be provided by Veritas when they have tested and released the newer version. This issue can be seen during either a backup or restore.
Information available on Veritas website regarding this issue:
Document ID: 337527
RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
URL: http://support.veritas.com/docs/337527
From Oracle ORA-07445 [Strcpy()+48] during RMAN backup or restore to tape using Oracle 11.2 [ID 959015.1]
Metalink note 959015.1
The latest version of Netbackup is not compatible with 11gR2, an upgrade to Netbackup will be provided by Veritas when they have tested and released the newer version. This issue can be seen during either a backup or restore.
Information available on Veritas website regarding this issue:
Document ID: 337527
RMAN-10038 error when attempting to backup Oracle version 11.2 using NetBackup 6.x.
URL: http://support.veritas.com/docs/337527
Monday, February 07, 2011
GENERAL SQL PERFREED USAGE
1. If it is unnecessary to read an entire table and a suitable index is available ensure that it is being used.
a. Typically speaking a concatenated index on last-name and first-name for example is faster than separate indexes on last-name and first-name.
i. Generally the more columns which are specified in a concatenated index make it more selective and thereby tend to improve its performance. For instance you could add dob to the exampled concatenated index.
b. Oracle cannot use an index to search for NULL values; however it can use an index to find values which are NOT NULL.
c. An index which contains the columns in the ORDER BY clause and the columns in the SELECT list will provide pretty good performance for ‘first row’ and ‘all rows’. However, when an index on ORDER BY columns alone is issued, the speed will be pretty good for ‘first row’ however; it will often be slower than a full table scan when retrieving ‘all rows’.
2. When using LIKE command with a wildcard ‘%’, the placement of that wildcard can make a difference in the amount of I/O required to answer the query.
a. If (LIKE ‘TIM%’) or if (LIKE ‘%ME’) with a full table scan there is no difference in the I/O required to satisfy the query.
b. If (LIKE ‘TIM%’) is used with an index it will be much faster than a full table scan.
c. If (LIKE ‘%ME’) is used with an index it will be much faster than that using (LIKE ‘TIM%’) is used with an index.
3. When considering JOIN Performance HASH JOIN’s tend to be faster, then SORT and lastly NESTED LOOPS.
4. A JOIN is usually faster than an IN as a JOIN gives the optimizer more choices for access paths.
5. If applicable the START WITH clause is much faster than a WHERE clause in eliminating rows in a hierarchal query.
6. A sub-query with an EXISTS will beat a sub-query with an IN which will beat an equivalent JOIN.
a. When using an EXISTS with a Correlated Sub-query it is best if the sub-query can be satisfied using an index lookup only.
7. When you use COUNT(*) it performs a full table scan; if the table has an index on a NOT NULL column then you can use COUNT() to reduce the count time substantially on large tables.
a. If a NOT NULL indexed column is not available then it is better to use COUNT(*) rather than COUNT() in regard to speed.
8. You can use an index to resolve a GROUP BY if the index contains the columns in the GROUP BY list, and all the columns are aggregated in the SELECT list.
9. Using WHERE instead of HAVING (where possible of course) reduces I/O requirements by eliminating rows before they are grouped.
10. If you don’t need to get rid of duplicate rows in a UNION operation that you are performing, you should use UNION ALL instead of UNION to avoid sorts.
11. Hash Joins tend to perform much better than INTERSECT’s; therefore you should consider recoding INTERSET’s into HASH JOINs.
12. In the same fashion you should consider recoding a MINUS into an ANTI-JOIN using the HASH_AJ hint.
13. Where applicable (removing all rows) TRUNCATE TABLE is preferred over a DELETE.
14. Remember that as great as indexes can be, they also add overhead to INSERT, DELETE and some UPDATE’s. Therefore avoid over-indexing, especially on columns which are frequently changed.
15. Parallel SQL can be a utilized to achieve great performance gains. The following are types of statements that can be parallelized:
a. SQL queries (especially if they contain large full table scans).
b. Building or the rebuilding of indexes.
c. Creating Tables as SELECT (CTAS).
d. An UPDATE, DELETE or INSERT statement.
16. Trying to accomplish a lot within one complex query tends to be much slower in Oracle than breaking it down via temporary tables that are cleaned up once the result set is achieved.
17. When DELETE’s are required on large tables, if possible use a CTAS to create the table without the rows which would have been deleted, thereby greatly reducing the overall time that would have been incurred by the DELETE.
a. Typically speaking a concatenated index on last-name and first-name for example is faster than separate indexes on last-name and first-name.
i. Generally the more columns which are specified in a concatenated index make it more selective and thereby tend to improve its performance. For instance you could add dob to the exampled concatenated index.
b. Oracle cannot use an index to search for NULL values; however it can use an index to find values which are NOT NULL.
c. An index which contains the columns in the ORDER BY clause and the columns in the SELECT list will provide pretty good performance for ‘first row’ and ‘all rows’. However, when an index on ORDER BY columns alone is issued, the speed will be pretty good for ‘first row’ however; it will often be slower than a full table scan when retrieving ‘all rows’.
2. When using LIKE command with a wildcard ‘%’, the placement of that wildcard can make a difference in the amount of I/O required to answer the query.
a. If (LIKE ‘TIM%’) or if (LIKE ‘%ME’) with a full table scan there is no difference in the I/O required to satisfy the query.
b. If (LIKE ‘TIM%’) is used with an index it will be much faster than a full table scan.
c. If (LIKE ‘%ME’) is used with an index it will be much faster than that using (LIKE ‘TIM%’) is used with an index.
3. When considering JOIN Performance HASH JOIN’s tend to be faster, then SORT and lastly NESTED LOOPS.
4. A JOIN is usually faster than an IN as a JOIN gives the optimizer more choices for access paths.
5. If applicable the START WITH clause is much faster than a WHERE clause in eliminating rows in a hierarchal query.
6. A sub-query with an EXISTS will beat a sub-query with an IN which will beat an equivalent JOIN.
a. When using an EXISTS with a Correlated Sub-query it is best if the sub-query can be satisfied using an index lookup only.
7. When you use COUNT(*) it performs a full table scan; if the table has an index on a NOT NULL column then you can use COUNT(
a. If a NOT NULL indexed column is not available then it is better to use COUNT(*) rather than COUNT(
8. You can use an index to resolve a GROUP BY if the index contains the columns in the GROUP BY list, and all the columns are aggregated in the SELECT list.
9. Using WHERE instead of HAVING (where possible of course) reduces I/O requirements by eliminating rows before they are grouped.
10. If you don’t need to get rid of duplicate rows in a UNION operation that you are performing, you should use UNION ALL instead of UNION to avoid sorts.
11. Hash Joins tend to perform much better than INTERSECT’s; therefore you should consider recoding INTERSET’s into HASH JOINs.
12. In the same fashion you should consider recoding a MINUS into an ANTI-JOIN using the HASH_AJ hint.
13. Where applicable (removing all rows) TRUNCATE TABLE is preferred over a DELETE.
14. Remember that as great as indexes can be, they also add overhead to INSERT, DELETE and some UPDATE’s. Therefore avoid over-indexing, especially on columns which are frequently changed.
15. Parallel SQL can be a utilized to achieve great performance gains. The following are types of statements that can be parallelized:
a. SQL queries (especially if they contain large full table scans).
b. Building or the rebuilding of indexes.
c. Creating Tables as SELECT (CTAS).
d. An UPDATE, DELETE or INSERT statement.
16. Trying to accomplish a lot within one complex query tends to be much slower in Oracle than breaking it down via temporary tables that are cleaned up once the result set is achieved.
17. When DELETE’s are required on large tables, if possible use a CTAS to create the table without the rows which would have been deleted, thereby greatly reducing the overall time that would have been incurred by the DELETE.
Friday, February 04, 2011
OUI May Deinstall Unintended Files
One of my co-workers encountered the following issue earlier today:
This is just a heads up that Oracle OUI may deinstall unintended files. I used 11.1.0.7 OUI to deinstall Oracle 10.1 & 10.2 client and server installations today, and soon after these errors appeared –
ORA-12154: TNS:could not resolve the connect identifier specified
The deinstaller removed the tnsnames.ora, sqlnet.ora & listener.ora files in the active $ORACLE_HOME/network/admin directory even though TNS_ADMIN was set correctly. The missing files had to be extracted from the tar backup and restored to the correct location. I did not find this specific issue in My Oracle Support, but did find some similar problems reported for Oracle 11.2. Support request was opened.
Thx
This is just a heads up that Oracle OUI may deinstall unintended files. I used 11.1.0.7 OUI to deinstall Oracle 10.1 & 10.2 client and server installations today, and soon after these errors appeared –
ORA-12154: TNS:could not resolve the connect identifier specified
The deinstaller removed the tnsnames.ora, sqlnet.ora & listener.ora files in the active $ORACLE_HOME/network/admin directory even though TNS_ADMIN was set correctly. The missing files had to be extracted from the tar backup and restored to the correct location. I did not find this specific issue in My Oracle Support, but did find some similar problems reported for Oracle 11.2. Support request was opened.
Thx
Tuesday, January 25, 2011
How To Tell If My Linux O/S is 64bit or 32bit
From the command prompt issue the "uname -m" command, if the output is i386 and/or i686 then it's 32bit, if it's x86_64 then it's 64bit.
$>uname -m
i686
$>uname -m
i686
Monday, January 24, 2011
A Tar Gzip Split
The issue:
We had a 350gbs text file that was gzip’ed down to 115gbs that needed to be move from one of our Linux servers to a client’s Lunix server. However, as usual they wanted it there yesterday. So to speed this movement up I wanted to split the file so that it could be sftp’ed in parallel. Of course unzipping and splitting and re-zipping would require a lot of time not to mention space that was not available. What to do…
The answer:
Here is what I did.
First I split the gzip’ed file using the following command:
tar cfz - |split -b 100m /u1/directoryname/somefilename.gz
This split the zipped source file into 11 pieces. Allowing them to be ftp’ed in parallel; thereby greatly reducing the overall transfer time. Once the 11 files were on the target server they were put back together and then they where uncompressed.
cat x* > somefilename.gz (note: it is important to use the x* as if you try to order the reconstruction of the files the resulted gzip’ed file will be corrupt).
Then this new file must be unzipped:
gunzip somefilename.gz
I go no errors and a quick head and wc –l on the file showed everything to be fine.
Thx
We had a 350gbs text file that was gzip’ed down to 115gbs that needed to be move from one of our Linux servers to a client’s Lunix server. However, as usual they wanted it there yesterday. So to speed this movement up I wanted to split the file so that it could be sftp’ed in parallel. Of course unzipping and splitting and re-zipping would require a lot of time not to mention space that was not available. What to do…
The answer:
Here is what I did.
First I split the gzip’ed file using the following command:
tar cfz - |split -b 100m /u1/directoryname/somefilename.gz
This split the zipped source file into 11 pieces. Allowing them to be ftp’ed in parallel; thereby greatly reducing the overall transfer time. Once the 11 files were on the target server they were put back together and then they where uncompressed.
cat x* > somefilename.gz (note: it is important to use the x* as if you try to order the reconstruction of the files the resulted gzip’ed file will be corrupt).
Then this new file must be unzipped:
gunzip somefilename.gz
I go no errors and a quick head and wc –l on the file showed everything to be fine.
Thx
Subscribe to:
Posts (Atom)