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.

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

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.

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;

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(‘’);

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

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.

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