World of Oracle DBA

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat. For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server. $ sqlplus apps/apps@proddb Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below. $ ps -efgrep sqlplus oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials. Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line. $ sqlplus apps@proddb Enter password: **** Or, even not to expose the username and connecting string. $ sqlplus Ente

Most of us sometimes start SQL * Plus with a password on UNIX and Linux platforms without knowing security threat. For example, an application user connects SQL * Plus by passing username and password on Unix/Linux Server. $ sqlplus apps/apps@proddb Here the sqlplus command parameters are very much available for viewing by all operating system users on the same host computer; as a result, password entered on the command line could be exposed to other users, as below. $ ps -efgrep sqlplus oracle 14490 2190 0 16:31:53 pts/5 0:00 sqlplus apps/apps@proddb oracle 14493 14491 0 16:32:01 pts/5 0:00 grep sqlplus So, there might be a chance for an intruder to know the user id and password, and can connect to the database using that credentials. Then, following is the secure and best way of connecting SQL * Plus where the password is not exposed on the command line. $ sqlplus apps@proddb Enter password: **** Or, even not to expose the username and connecting string. $ sqlplus Ente

The information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms. Terms used in this post: Source System - the system to be cloned - Production Target System - the newly created (or cloned) system – Non Production Production Database – PROD Test Database – TEST Development Database - DEV What is a Database Clone? * A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams. * Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area. * Cloning is a procedure for preparing and creating a test or development

The information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms. Terms used in this post: Source System - the system to be cloned - Production Target System - the newly created (or cloned) system – Non Production Production Database – PROD Test Database – TEST Development Database - DEV What is a Database Clone? * A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams. * Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area. * Cloning is a procedure for preparing and creating a test or development

What is ORACLE_HOME used for? * The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory. * The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database. * This directory can be used by any user who wants to use the particular database. * If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process. * Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory: ORACLE_HOME=$ORACLE_BASE/product/10.2.0. What is ORACLE_BASE used for? * The ORACLE_BASE is also an environment variable to define the base/

What is ORACLE_HOME used for? * The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory. * The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database. * This directory can be used by any user who wants to use the particular database. * If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process. * Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory: ORACLE_HOME=$ORACLE_BASE/product/10.2.0. What is ORACLE_BASE used for? * The ORACLE_BASE is also an environment variable to define the base/

When you see ORA-1555s do you just go ahead and increase UNDO_RETENTION ? Recently, I had a string of ORA-1555s occurring frequently through the day. One quick suggestion was to increase UNDO_RETENTION. Some DBAs might do that. However, that would have been jumping to a conclusion. This was a database that had been running 9.2 for more than 2 years. I had one other 9.2 database with the same schema, usage and comparable size {for another business unit} and wasn't seeing ORA-1555s there. The trace files showed me that these ORA-1555s were always for the same SQL. I knew that this SQL was a Refresh Query being executed to refresh a Materialized View in another database. Apparently, it was only this query that was reporting ORA-1555s at about 1 out of every 4 refreshes (the refresh being hourly) . However, later, a developer also reported a similar query on the same tables taking a long time (but not yet erroring out on ORA-1555s}. So, the fix was not to increase UNDO_RET

When you see ORA-1555s do you just go ahead and increase UNDO_RETENTION ? Recently, I had a string of ORA-1555s occurring frequently through the day. One quick suggestion was to increase UNDO_RETENTION. Some DBAs might do that. However, that would have been jumping to a conclusion. This was a database that had been running 9.2 for more than 2 years. I had one other 9.2 database with the same schema, usage and comparable size {for another business unit} and wasn't seeing ORA-1555s there. The trace files showed me that these ORA-1555s were always for the same SQL. I knew that this SQL was a Refresh Query being executed to refresh a Materialized View in another database. Apparently, it was only this query that was reporting ORA-1555s at about 1 out of every 4 refreshes (the refresh being hourly) . However, later, a developer also reported a similar query on the same tables taking a long time (but not yet erroring out on ORA-1555s}. So, the fix was not to increase UNDO_RET

We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below. The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem. The parameters you need to set for the shared server and their default values are: NAME VALUE ------------------------------ ---------- circuits 0 dispatchers max_dispatchers 5 max_shared_servers 20 mts_circuits 0 mts_dispatchers mts_max_dispatchers 5 shared_server_sessions 0 shared_servers 0 The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you. If we go over these

We have recently configured shared server in some of our databases. Steps to follow to enable shared server are below. The following configuration is on Enterprise Edition 9.2.0.5. The numbers used in the settings shown are only examples. We are running an OLTP system with about 2000 concurrent users with these parameters without any problem. The parameters you need to set for the shared server and their default values are: NAME VALUE ------------------------------ ---------- circuits 0 dispatchers max_dispatchers 5 max_shared_servers 20 mts_circuits 0 mts_dispatchers mts_max_dispatchers 5 shared_server_sessions 0 shared_servers 0 The ones starting with mts are for backward compatibility, setting circuits, dispatchers and max_dispatchers or mts_circuits, mts_dispatchers and mts_max_dispatchers is the same and is up to you. If we go over these

There was a question at the OTN Database-General forum today about a problem when trying to describe the view dba_tablespaces. The poster was getting an ORA-04043 error. SQL> desc dba_tablespaces ERROR: ORA-04043: object dba_tablespaces does not exist The first thing I thought about this was that the instance might have been in mount mode. I tried it on a database in mount stage and I got the error. SQL> shutdown abort ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 404723928 bytes Fixed Size 735448 bytes Variable Size 234881024 bytes Database Buffers 167772160 bytes Redo Buffers 1335296 bytes Database mounted. SQL> desc dba_tablespaces ERROR: ORA-04043: object dba_tablespaces does not exist I was expecting to be able to see the view after opening the database, but... SQL> alter database open; Database altered. SQL> desc dba_tablespaces ERROR: ORA-04043: obje

There was a question at the OTN Database-General forum today about a problem when trying to describe the view dba_tablespaces. The poster was getting an ORA-04043 error. SQL> desc dba_tablespaces ERROR: ORA-04043: object dba_tablespaces does not exist The first thing I thought about this was that the instance might have been in mount mode. I tried it on a database in mount stage and I got the error. SQL> shutdown abort ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 404723928 bytes Fixed Size 735448 bytes Variable Size 234881024 bytes Database Buffers 167772160 bytes Redo Buffers 1335296 bytes Database mounted. SQL> desc dba_tablespaces ERROR: ORA-04043: object dba_tablespaces does not exist I was expecting to be able to see the view after opening the database, but... SQL> alter database open; Database altered. SQL> desc dba_tablespaces ERROR: ORA-04043: obje

A bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically. While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G. Here is the test case Richard presents in his paper. SQL> create table t(id number,value varchar2(10)); Table created. SQL> create index t_ind on t(id); Index created. SQL> @mystat split NAME VALUE ------------------------------ ---------- leaf node splits 0 leaf node 90-10 splits 0 branch node splits 0 SQL> ed Wrote file afiedt.buf 1 begin 2 for i in 1..10000 loop 3 insert into t values(i,'test'); 4 commit; 5 end loop; 6* end; SQL> r 1 begin 2 fo

A bug in 9i about index block splits when rows are inserted in the order of the index columns. Depending on when you commit your inserts the index size changes dramatically. While I was trying to find out why a 3-column primary key index takes more space than its table I recalled that bug and it turned out that was the reason of the space issue. The related bug is 3196414 and it is fixed in 10G. Here is the test case Richard presents in his paper. SQL> create table t(id number,value varchar2(10)); Table created. SQL> create index t_ind on t(id); Index created. SQL> @mystat split NAME VALUE ------------------------------ ---------- leaf node splits 0 leaf node 90-10 splits 0 branch node splits 0 SQL> ed Wrote file afiedt.buf 1 begin 2 for i in 1..10000 loop 3 insert into t values(i,'test'); 4 commit; 5 end loop; 6* end; SQL> r 1 begin 2 fo

Two issues can be avoided if you index your foreign key columns: Slow joins (Full Table Scans) between parent/child tables Deadlocks when updating or deleting a parent key. The following script is very helpfull in locating unindexed foreign keys: column columns format a20 word_wrapped column table_name format a30 word_wrapped select decode( b.table_name, NULL, '****', 'ok' ) Status, a.table_name, a.columns, b.columns from ( select substr(a.table_name,1,30) table_name, substr(a.constraint_name,1,30) constraint_name, max(decode(position, 1, substr(column_name,1,30),NULL)) || max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || max(decode(pos

Two issues can be avoided if you index your foreign key columns: Slow joins (Full Table Scans) between parent/child tables Deadlocks when updating or deleting a parent key. The following script is very helpfull in locating unindexed foreign keys: column columns format a20 word_wrapped column table_name format a30 word_wrapped select decode( b.table_name, NULL, '****', 'ok' ) Status, a.table_name, a.columns, b.columns from ( select substr(a.table_name,1,30) table_name, substr(a.constraint_name,1,30) constraint_name, max(decode(position, 1, substr(column_name,1,30),NULL)) || max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || max(decode(pos

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance. The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects. For almost every V$ view, Oracle has a corresponding GV$ (global V$) view. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained.

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance. The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. You should access only the V$ objects, not the V_$ objects. For almost every V$ view, Oracle has a corresponding GV$ (global V$) view. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained.

$ cd $ORACLE_HOME/OPatch $ ./opatch lsinventory

$ cd $ORACLE_HOME/OPatch $ ./opatch lsinventory

Select executions, buffer_gets, sql_text from v$sqlarea where executions > 10000 order by executions desc;

Select executions, buffer_gets, sql_text from v$sqlarea where executions > 10000 order by executions desc;

When a session is killed by an ALTER SYSTEM KILL SESSION 'nnnn,nnnn' , it won’t necessarily vanish immediately. If the session has made changes to the database, they have to be undone just as if you had coded a ROLLBACK . Drastic action, such as a forced reboot of the database, may make the killed session vanish, but the rollback still has to be done. Generally it is best to leave the client program, such as TOAD or SQL*Plus, running until the rollback is complete. That way, the client will receive the ‘Your session has been killed’ error and the database session can exit cleanly. You can monitor how much work a session has still to rollback using an SQL like this : SELECT vt.used_ublk , vs.sid, vs.serial#, vs.username, vs.status, vs.schemaname, vs.osuser, vs.machine, vs.terminal, vs.program, vs.prev_hash_value, vs.sql_hash_value, vt.start_ubablk, used_urec FROM v$session vs, v$transaction vt, v$sqlarea a WHERE vs.taddr = vt.addr AND

When a session is killed by an ALTER SYSTEM KILL SESSION 'nnnn,nnnn' , it won’t necessarily vanish immediately. If the session has made changes to the database, they have to be undone just as if you had coded a ROLLBACK . Drastic action, such as a forced reboot of the database, may make the killed session vanish, but the rollback still has to be done. Generally it is best to leave the client program, such as TOAD or SQL*Plus, running until the rollback is complete. That way, the client will receive the ‘Your session has been killed’ error and the database session can exit cleanly. You can monitor how much work a session has still to rollback using an SQL like this : SELECT vt.used_ublk , vs.sid, vs.serial#, vs.username, vs.status, vs.schemaname, vs.osuser, vs.machine, vs.terminal, vs.program, vs.prev_hash_value, vs.sql_hash_value, vt.start_ubablk, used_urec FROM v$session vs, v$transaction vt, v$sqlarea a WHERE vs.taddr = vt.addr AND

At customer sites very often I can see the transaction load via the amount of redo which was generated. For best overview I like to query the amount of redo generated per hour: SELECT Start_Date, Start_Time, Num_Logs, Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2) AS Mbytes, Vdb.NAME AS Dbname FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs FROM V$log_History Vlh GROUP BY To_Char(Vlh.First_Time, 'YYYY-MM-DD'), To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist, V$log Vl, V$database Vdb WHERE Vl.Group# = 1 ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time; Sample output: START_DATE START_TIME NUM_LOGS MBYTES