Archive for March, 2012

Oracle Performance Tuning

March 14, 2012 Comments off

Oracle Performance Tuning Tips

Basic system administration is essential to resolve Oracle performance tuning issues. Otherwise, issue would be something else and you would waste your efforts in wrong investigation at database side. So many issues can impact slowness of Oracle server. If any third party application is running which is not part of Oracle database but it can consume more resources at server side. Means we cannot say every time problem of Oracle database. In various issues, Oracle database doesn’t involve in resource contention but overall server’s resource contention is impacting Oracle database performance.

Therefore, we can say that, we need some basic knowledge of system administration during examining resource contention of database level. In Windows system, you would not get different kind of utilities for server monitoring but in Linux and Unix you can various options. During server monitoring, you can exact idea of resource contention, it is either database side or operating system side. This examination is always point out the bootstrap of performance bottleneck of Oracle server.

Task manager of Windows system, would give you exact idea of different applications including Oracle instance. You would obtain disk usage, physical I/O, memory usage, paging activity from Windows Task Manager. You can analyze issue using trace files of Oracle for problem solving of performance. Windows System Event Viewer is also offering you some kind of facility to see resource contention at server side.

Thus, we can say that for troubleshooting of performance problems of Oracle databases, we need some fundamental knowledge of server. You can get more details and performance tuning tips from other resources. Every time same scenario doesn’t occur and we cannot say it would not occur. But most of the cases, problem solving becomes easy while examine system level in Oracle tuning. Oracle DBA blog would help you to obtain special tips.


To Whom granted DBA Role in Oracle

March 6, 2012 Comments off

Who gets the DBA role in Oracle Database?

The DBA role conveys considerable privilege – essentially all of them. For that reason, this role and most of the associated privileges should never be given to any user other than a person who is to have full authority within the database. Restrictions of granting the DBA role should be strictly enforced. There is never any reason to grant developers the DBA role, even though many of them tend to ask for that privilege. In all cases, instruct those who request the DBA role to list the specific privileges needed for their particular application development, along with an explanation of why each individual privilege is needed. The DBA and appropriate managers can then arrange to meet the request assuming that the needs are determine to be appropriate and justified. Often, a developer will demand quite forcibly the DBA role because he has not properly identified what specific privileges are really needed for this application.

Why is it so risky to grant DBA role to a developer? When a developer is granted the DBA role and then moves his application into production, will you want to grant the production account the DBA role? Of course not. The potential for compromise of a production system through the DBA role would be far too great. Therefore, imposing the “no DBA role for non-DBAs” rule in development will force the developer to do the necessary up-front work to ensure that only the truly necessary privileges for the application move to production. After that it is very tough to manage in production database support.

All too often, the development application is moved into production without a complete examination of the roles assigned to users. The users who have been created in development, for whatever reason, get carried along in an import and are not removed.

Database DBA and Oracle DBA Blog always recommends not to grant Oracle DBA Role to any application schema, development schema or any testing user. It is purely risky to provide this role. Instead of granting this role, you need to grant system privileges separately.

What is OS_AUTHENT_PREFIX and OPS$ in Oracle

March 2, 2012 Comments off

How to configure OS_AUTHENT_PREFIX and OPS$ in Oracle for providing operating system login accessibility.

Within the database initialization file init.ora or spfile, the OS_AUTHENT_PREFIX parameter can be set to the prefix, if any, that will be used to identify a database that will rely on operating system verification of the user alone. As delivered, the Oracle RDBMS default value for OS_AUTHENT_PREFIX is set to OPS$. Therefore, the convention used to identify an account that is able to log on the database without using a username and password is to precede the username with the value OPS$. For example, if the operating system account name is james, the database username would be ops$james, and the CREATE command for the database user creation would be as following.

SQL>Create user ops@james identified by james123;

Note the use of a password assignment in the above command. The password is required by the syntax of the statement, but because this is an OPS$ account, the user will not have to supply the assigned password at login. By default, and account created with operating system prefix can be used to access account with the matching name (remove OPS$ prefix). Therefore, if the DBA wants and account that can also be used from a client machine to SQL*Net to the database, and can be used from the operating system without using a username and password, this convention will work very nicely. When the user connects using SQL*Net, he types the username OPS$JAMES and places his password in the requested <password> area. The user is logged directly in to the operating system account, he uses just “/” as his username and password.

For example, from a client machine, the user james would log on using

Username: ops$james

Password: james123

From operating system, the user would log on using

Sqlplus /

It means if you want to create operating system login account then you should need to create OPS$JAMES if parameter OS_AUTHENT_PREFIX is “OPS$”

SQ> show parameter OS_AUTHENT_PREFIX


If you create operating system login account using only JAMES then you should need to change parameter of OS_AUTHENT_PREFIX with value null as following.


After following changes in parameter (either init.ora or spfile.ora), you can create Oracle user account for operating system login account access as following.

SQL> create user JAMES identified by externally;

To let Oracle know that the operating system prefix was to be blank and that the default for authentication should be just the user’s name without the “OPS$” prefix. By creating the user this way and declaring the operating system prefix to be blank, the database DBA eliminates the ability to assign a password to the account since, if a password is assigned to the account, the ability to connect to the database and rely on the operating system authentication is negated. In all cases where an account is allowed to connect without a password, the syntax for connecting to the database is simple sqlplus with “/” sign.

If the account is set up as identified externally, no password can be assigned and the user will not be able to use the account in a dual manner. He will not be able to use the account both as a SQL*Net account with password and as an operating system account without a username and password.