Tips, information and some real-time experiences in Oracle, UNIX

To add up column values in a file - UNIX

noreply@blogger.com (kaspi) — Thu, 22 Aug 2013 03:03:00 +0000

To all up all the values in a particular column of a file in unix use below command

Ex: sample.txt contains below data
10 20 30
40 50 60
70 80 90

Now I want to add all values in column two of above file sample.txt

$ cat sample.txt | awk -F' ' '{print $2}' | awk '{total = total +$1}END{print total}'

Output: 150

How to rename or unzip or untar and vice-versa all files in a directory with same file extension - UNIX

noreply@blogger.com (kaspi) — Thu, 22 Aug 2013 02:55:00 +0000

To rename or unzip or untar and vice-versa all files in a directory use below commands

Command:
$ for i in `ls -latr *.txt.2013 | awk '{print $9}'`
do
mv $i `basename $i .2013`
done

The above command will rename all files in a directory for example test01.txt.2013, test02.txt.2013, test03.txt.2013 to test01.txt, test02.txt, test03.txt

Note: All lines after $ symbol line together form the command.

A similar command can be used to unzip or untar all files in a directory

Command:

$ for i in `ls -latr *.txt.gz | awk '{print $9}'`
do
gunzip $i
done

The above command will unizp all files in a directory for example test01.txt.gz, test02.txt.gz, test03.txt.gz to test01.txt, test02.txt, test03.txt

Note: All lines after $ symbol line together form the command.

How to print or trim end characters of a string/line in UNIX

noreply@blogger.com (kaspi) — Thu, 22 Aug 2013 02:43:00 +0000

The commands below help in printing end characters of a line or a string. I assume that ORACLE_SID is set to "TESTDB" for examples below.

Print last one character from ORACLE_SID:

$ echo $ORACLE_SID | sed -e "s/^.*$.$$/1/"
Output: B

Print last two characters:
$ echo $ORACLE_SID | sed -e "s/^.*$..$$/1/"
Output: DB

Print last three characters:
$ echo $ORACLE_SID | sed -e "s/^.*$...$$/1/"
Output: TDB

The commands below help in trimming end characters of a line or a string. I assume that ORACLE_SID is set to "TESTDB" for examples below.

Trims last one character:
$ echo $ORACLE_SID | sed 's/.$//'
Output: TESTD

Trims last two characters:
$ echo $ORACLE_SID | sed 's/..$//'
Output: TEST

Trims last three characters:
$ echo $ORACLE_SID | sed 's/...$//'
Output: TES

SQL Query to find top SQL's with two specific AWR Snapshots

noreply@blogger.com (kaspi) — Thu, 22 Aug 2013 02:27:00 +0000

SQL:

WITH subq as
(SELECT *
FROM (SELECT s.sql_id,
SUM(nvl(s.executions_delta, 0)) execs,
SUM(round(s.elapsed_time_delta / 1000000, 3)) exec_time,
SUM(round(s.buffer_gets_delta)) io_logical,
SUM(round(s.cpu_time_delta / 1000000, 3)) cpu_time,
SUM(nvl(s.parse_calls_delta, 0)) parse_calls,
SUM(round(s.iowait_delta / 1000000, 3)) io_wait,
SUM(nvl(s.disk_reads_delta, 0)) disk_reads
FROM dba_hist_sqlstat s, dba_hist_snapshot ss
WHERE s.snap_id BETWEEN start_snap_id AND end_snap_id
AND ss.snap_id = s.snap_id
AND ss.instance_number = s.instance_number
AND s.executions_delta > 0
GROUP BY s.sql_id
ORDER BY exec_time DESC)
WHERE rownum < num_of_sqls)
SELECT subq.*, substr(sql_text, 1, 8000) sqltext
FROM dba_hist_sqltext st, subq
WHERE st.sql_id=subq.sql_id
ORDER BY 3 desc

Things to replace in above sql before executing:

start_snap_id: replace this with starting AWR snapshot number of your database from your peak day snapshots for the period which you want collect the top SQL's. For example: Starting snapshot of database TEST on wednesday at 9AM is 12345.

end_snap_id: replace this with ending AWR snapshot number of your database from your peak day snapshots for the period which you want collect the top SQL's. For example: Ending snapshot of database TEST on wednesday at 9AM is 12355.

num_of_sqls: Give the number of how many top SQL's you want to collect using the query for example if you want to collect top 50 SQL's replace num_of_sqls with 50

Undo Latch Contention

noreply@blogger.com (kaspi) — Thu, 14 Jul 2011 19:12:00 +0000

Here is the SQL to find sessions causing UNDO Latch Contention:

select

swh.seq#,

sess.sid,

sess.username username,

swh.event event,

swh.p1,

swh.p2

from

v$session sess,

v$session_wait_history swh

where

sess.sid = 798

and

sess.sid = swh.sid

order by

swh.seq#;

Sample Output:

     SEQ#     SID      USERNAME        EVENT                                                  P1                      P2
---------- -------    --------------- ------------------------------------   --------------       ----------
         1        798     ORCL               latch free                                              5.0440E+17        127
         2        798     ORCL               latch free                                              5.0440E+17        127
         3        798     ORCL               latch free                                              5.0440E+17        127
         4        798     ORCL               latch free                                              5.0440E+17        127
         5        798     ORCL               latch: cache buffers chains           5.0440E+17        122


5 rows selected.

To find list of objects accessed by a database session

noreply@blogger.com (kaspi) — Thu, 14 Jul 2011 19:06:00 +0000

Here is a simple query that shows all the objects accessed by a database:

SELECT sid,
       owner,
       TYPE,
       object
FROM v$access
WHERE sid = 'session_id';

Ex:

SELECT sid,

owner,

TYPE,

object

FROM v$access

WHERE sid = '769';

Temporary Tablespace Space Monitoring

noreply@blogger.com (kaspi) — Mon, 30 Aug 2010 17:21:00 +0000

Here are some quick SQL's to monitor the usage of TEMP Tablespace:

Space Usage of TEMP Tablespace:

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

Temp Space Usage by Session:

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

Temp Space Usage by Statement:

SELECT S.sid || ',' || S.serial# sid_serial, S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address (+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;

Waits Related to Initialization Parameters

noreply@blogger.com (kaspi) — Fri, 27 Aug 2010 04:04:00 +0000

Wait Problem: Potential Fix
Free buffer: Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code
Buffer busy: Segment Header — Add freelists or freelist groups or use ASSM
Buffer busy: Data Block — Separate hot data; use reverse key indexes; small block sizes
Buffer busy: Data Block — Increase initrans and/or maxtrans
Buffer busy: Undo Header —Use automatic undo management
Buffer busy: Undo Block — Commit more; use automatic undo management
Latch free: Investigate the detail (listing in next table of this chapter for fixes)
Log buffer space: Increase the log buffer; use faster disks for the redo logs
Scattered read: Indicates many full table scans — tune the code; cache small tables
Sequential read: Indicates many index reads — tune the code (especially joins)
Write complete waits: Adds database writers; checkpoint more often; buffer cache too small

Latch Problem: Potential Fix

Library cache: Use bind variables; adjust the shared_pool_size
Shared pool: Use bind variables; adjust the shared_pool_size
Row cache objects: Increase the shared pool. This is not a common problem.
Cache buffers chain: If you get this latch wait, it means you need to reduce logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved. High I/O rates could be a sign of a hot block (meaning
a block highly accessed). Cache buffer lru chain latch contention can be resolved by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache. You should adjust DB_BLOCK_BUFFERS, and possible DB_BLOCK_SIZE. Multiple buffer pools can help
reduce contention on this latch. You can create additional cache buffer lru chain latches by adjusting the configuration parameter DB_BLOCK_LRU_LATCHES. You may be able to reduce the load on the cache buffer chain latches by increasing the configuration parameter. _DB_BLOCK_HASH_BUCKETS may need to be increased or set to a prime number (in pre-9i versions).

Library Cache SQL

noreply@blogger.com (kaspi) — Fri, 27 Aug 2010 03:55:00 +0000

The following query uses the V$LIBRARYCACHE view to examine the reload ratio in the library cache:

select Sum(Pins) "Hits",

Sum(Reloads) "Misses",

((Sum(Reloads) / Sum(Pins)) * 100)"Reload %"

from V$LibraryCache;

Data Dictionary Cache Hit Ratio SQL

noreply@blogger.com (kaspi) — Fri, 27 Aug 2010 03:53:00 +0000

SQL:

select ((1 - (Sum(GetMisses) / (Sum(Gets) + Sum(GetMisses)))) * 100) "Hit Rate"

from V$RowCache

where Gets + GetMisses <> 0;

Session or System Modifiable Parameter SQL

noreply@blogger.com (kaspi) — Fri, 27 Aug 2010 02:57:00 +0000

There are two key fields in the V$PARAMETER view:
■ ISSES_MODIFIABLE Indicates if a user with the ALTER SESSION privilege can modify
this initialization parameter for their session.

■ ISSYS_MODIFIABLE Indicates if someone with ALTER SYSTEM privilege can modify
this particular parameter.

This query displays the initialization parameters that can be modified with an ALTER SYSTEM or ALTER SESSION command (partial result displayed):

select name, value, isdefault, isses_modifiable, issys_modifiable

from v$parameter

where issys_modifiable <> 'FALSE'

or isses_modifiable <> 'FALSE'

order by name;

Data Cache Hit Ratio SQL

noreply@blogger.com (kaspi) — Fri, 27 Aug 2010 02:55:00 +0000

Query to view the data cache hit ratio:

column phys format 999,999,999 heading 'Physical Reads'

column gets format 999,999,999 heading ' DB Block Gets'

column con_gets format 999,999,999 heading 'Consistent Gets'

column hitratio format 999.99 heading ' Hit Ratio '

select sum(decode(name,'physical reads',value,0)) phys,

sum(decode(name,'db block gets',value,0)) gets,

sum(decode(name,'consistent gets', value,0)) con_gets,

(1 - (sum(decode(name,'physical reads',value,0)) /

(sum(decode(name,'db block gets',value,0)) +

sum(decode(name,'consistent gets',value,0))))) * 100 hitratio

from v$sysstat;

Cardinality in Oracle Table Joins

noreply@blogger.com (kaspi) — Tue, 24 Aug 2010 19:36:00 +0000

Cardinatly: This is the number of rows that Oracle expects to process or expects to be the number rows that will be returned after joining the tables from the join condition.

More to follow...............

Check RMAN Backup Status

noreply@blogger.com (kaspi) — Tue, 24 Aug 2010 19:23:00 +0000

SQL to report on all backups i.e., Full, Incremental and Archive log backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SQL to report on all Full and Incremental backup but not Archive log backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

where input_type='DB INCR'

order by session_key;

To check progress of RMAN Backup:

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,

sysdate + TIME_REMAINING/3600/24 end_at

from v$session_longops

where totalwork > sofar

AND opname NOT LIKE '%aggregate%'

AND opname like 'RMAN%';

Resize/Add/Move/Rename/Drop datafile and temporary datafile in Oracle

noreply@blogger.com (kaspi) — Mon, 12 Apr 2010 13:55:00 +0000

Resize a datafile and tempfile:

SQL> alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' resize 50M;

SQL> alter database tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.01.dbf' resize 50M;

Add a datafile:

SQL> alter tablespace tst_tbspace1 add datafile '/u10/oradata/TEST/test.tst_tbspace.data.02.dbf' size 2000M;

SQL> alter tablespace tmp_tbspace add tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.02.dbf' size 2000M;

Steps to move and/or rename datafile in oracle:

1. Shutdown the database and exit out of the SQL prompt - Database level step

Ex: SQL> shutdown

2. At the unix prompt copy or move the datafile from current location to new location using the OS copy (cp) command - OS level step

Ex: $ mv /u10/oradata/TEST/test.tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.data.01.dbf

you change the location and also rename the file at a time if you need to.

Ex: $ mv /u10/oradata/TEST/test.tst_tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.datafile.02.dbf

3. Open a SQL session and startup mount the database - Database level step

Ex: SQL> startup mount

4. Rename the datafile using oracle rename command - Database level step

Ex: SQL> alter database rename file '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf'
                 to '/u01/oradata/TEST/test.tbspace.data.01.dbf';

5. Open the database - Database level step

Ex: SQL> alter database open;

                       (or)

      SQL> shutdown

      SQL> startup

Drop datafile:

SQL>  alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' offline drop;

(or)

SQL> alter tablespace tst_tbspace drop datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf';

Rename/Copy a Table

noreply@blogger.com (kaspi) — Mon, 12 Apr 2010 13:24:00 +0000

A table in oracle can be renamed using the following syntax:

alter table table_name rename to new_table_name;

Ex: SQL> alter table dept rename to hist_dept;

Renaming a table doesn't update the oracle objects such as HTML DB, PL/SQL, Views some of which might be invalid based on their dependancy on the renamed object.

A copy of the original table can be created using the following syntax:

create table new_table_name as select * from table_name;

Ex: SQL> create hist_dept as select * from dept;

we need not necessarily need to include all the columns from the original table we can create a copy based on our choice of columns that we want to be included in the new table. The new table can be created either from one single table or multiple tables. The advantage of using CREATE TABLE....AS SELECT..(CTAS) it will create the new table with datatypes similar to old table(s).

A copy of the original table created including only the columns of our choice from a single table:

create table new_table_name as select column1, column2 from table_name;

Ex: SQL> create hist_dept as select dept_id, dept_name from dept;

A copy of the original table created including only the columns of our choice from a two tables:

create table new_table_name as select t1.column1, t2.column1, t2.column2 from table_name1 t1, table_name2 t2 where t1.column1 = t2.column1;

Ex: SQL> create hist_dept as select d.dept_id, e.emp_id, e.emp_anem from dept d, employee e where
d.dept_id = e.dept_id;

Oracle Background Processes

noreply@blogger.com (kaspi) — Thu, 08 Apr 2010 14:15:00 +0000

Oracle Background Porcesses: To maximize performance and accommodate many users, a multiprocess Oracle Database system uses background processes. Background processes consolidate functions that would otherwise be handled by multiple database programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.

Basic Oracle Background Processes:

Database writer (DBWn): The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes. The database selects an appropriate default setting for this initialization parameter or adjusts a user-specified setting based on the number of CPUs and the number of processor groups.

Log writer (LGWR): The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files.

Checkpoint (CKPT): At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.

System monitor (SMON): The system monitor performs recovery when a failed instance starts up again. In a Real Application Clusters database, the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.

Process monitor (PMON): The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on the dispatcher processes (described later in this table) and server processes and restarts them if they have failed.

Archiver (ARCn): One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs.

Recoverer (RECO): The recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.

Dispatcher (Dnnn): Dispatchers are optional background processes, present only when the shared server configuration is used.

Global Cache Service (LMS): In a Real Application Clusters environment, this process manages resources and provides inter-instance resource control.

Source: Oracle Documentation

Oracle Dedicated and Shared Server Processes

noreply@blogger.com (kaspi) — Thu, 08 Apr 2010 13:12:00 +0000

Oracle creates server processes whenever a user tries to connect to a database for handling all user requestss. Below are the two ways a server process can be:

A Dedicated Server Process, which serves only one user or one user connection
A Shared Server Process, which server multiple users or multiple user connections

Dedicated Server Processes:

Oracle databases are by default configured for dedicated server process and if you need to enable Shared server process you need to configure it manually by setting one or more initialization parameters. However a Dedicated server process is always required for:

To submit a batch job (for example, when a job can allow little or no idle time for the server process)
To use Recovery Manager (RMAN) to back up, restore, or recover a database

To request a dedicated server connection when Oracle Database is configured for shared server, users must connect using a net service name that is configured to use a dedicated server. Specifically, the net service name value should include the SERVER=DEDICATED clause in the connect descriptor.

Figure 1: Oracle Database Dedicated Server Processes

Shared Server Process:

Figure 2: Oracle Database Shared Server Processes

In Shared server configuration a client user process connect a dispatcher and the dispatcher then communicates with the oracle database for client request processing once the processing is done oracle then sends back the output or solution back to the dispatcher which in turn communicates it to the client user process. One dispatcher can serve multiple client user processes by connection pooling which can be enabled for shared server process. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.

Initialization Parameters for Shared Server:

The following initialization parameters control shared server operation:

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
DISPATCHERS: Configures dispatcher processes in the shared server architecture.
MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.

Enabling Shared Server:

Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured.

Determining a Value for SHARED_SERVERS and others:

The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.
You can limit the number of shared servers that can be created by setting MAX_SHARED_SERVERS parameter specifies the maximum number of shared servers that can be automatically created by PMON. After you set the SHARED_SERVERS parameter oracle decides the number of shares servers that needs to be created based on how busy the system is.

The SHARED_SERVER_SESSIONS initialization parameter specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers.
Configuring Dispatchers:

Ex: This is a typical example of setting the DISPATCHERS initialization parameter.

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"

You can use multiple different protocols for different dispatchers within the same initialization parameter.

Ex: DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'

Monitor the following views to determine the load on the dispatcher processes:

V$QUEUE
V$DISPATCHER
V$DISPATCHER_RATE

Shutting down specific Dispatches process:
To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.

SQL> SELECT NAME, NETWORK FROM V$DISPATCHER;

Each dispatcher is uniquely identified by a name of the form Dnnn.To shut down dispatcher D002, issue the following statement:

SQL>ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

Monitoring Shared Server:
The following views are useful for obtaining information about your shared server configuration and for monitoring performance.

V$DISPATCHER: Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.
V$DISPATCHER_CONFIG: Provides configuration information about the dispatchers.
V$DISPATCHER_RATE: Provides rate statistics for the dispatcher processes.
V$QUEUE: Contains information on the shared server message queues.
V$SHARED_SERVER: Contains information on the shared servers.
V$CIRCUIT: Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
V$SHARED_SERVER_MONITOR: Contains information for tuning shared server.
V$SGA: Contains size information about various system global area (SGA) groups. May be useful when tuning shared server.
V$SGASTAT: Contains detailed statistical information about the SGA, useful for tuning.
V$SHARED_POOL_RESERVED: Lists statistics to help tune the reserved pool and space within the shared pool.

Source: Oracle Documentation

All about startup migrate and startup upgrade

noreply@blogger.com (kaspi) — Thu, 08 Apr 2010 13:00:00 +0000

startup migrate:

=> Used to upgrade a database till 9i.
See the step 11 from the 9.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009472

=> Used to downgrade a database till 9i.
See the step 5 from the 9.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/downgrad.htm#248958

=> Used to downgrade a database since 10g.
See the step 18 from the 10.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#sthref415

startup upgrade
=> Used to upgrade a database since 10g.
See the step 7 from the 10.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/upgrade.htm#CACGGHJC

Recompiling Invalid Schema Objects

noreply@blogger.com (kaspi) — Tue, 06 Apr 2010 14:35:00 +0000

Objects in a database tend to get invalid due to operation such as patches, DDL changes and upgrades etc. The invalid objects are to compiled to ensure proper health of the database and user's access to them. I will provide here the ways you can compile objects in Oracle.

Identifying Invalid Objects:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                            OBJECT_TYPE,
                            OWNER,
                            STATUS
           FROM DBA_OBJECTS
           WHERE STATUS='INVALID'
           ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

to get invalid objects list for a particular schema below is the SQL:

col format OBJECT_NAME a40

SQL> SELECT OBJECT_NAME,
                          OBJECT_TYPE,
                          OWNER,
                          STATUS
           FROM DBA_OBJECTS
          WHERE OWNER='DEPT' AND STATUS='INVALID'
          ORDER BY OBJECT_NAME, OBJECT_TYPE, OWNER;

DBMS_UTILITY.COMPILE_SCHEMA:
DBMS_UTILITY package proides COMPILE_SCHEMA procedure to compile all the objects in a schema:

SQL> EXECUTE DBMS_UTILITY.COMPILE_SCHEMA ('SCHEMA_NAME');

UTLRP and UTLPRP:
UTLRP and UTLPRP are the oracle provided scripts to compile all invalid objects in oracle database.

Ex: SQL> ?/rdbms/admin/utlrp

Manual Approach:
Invalid Objects can be compiled individually after you have the list of invalid objects in your oracle database. Below are some of the examples:

SQL> ALTER PACKAGE package_name COMPILE;

SQL> ALTER PACKAGE package_name COMPILE BODY;

SQL> ALTER PROCEDURE procedure_name COMPILE;

SQL> ALTER FUNCTION function_name COMPILE;

SQL> ALTER TRIGGER trigger_name COMPILE;

SQL> ALTER VIEW view_name COMPILE;

An alternative approach is to use the DBMS_DDL package to perform the recompilations:

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'SCHEMA_NAME', 'PACKAGE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('PROCEDURE', 'SCHEMA_NAME', 'PROCEDURE_NAME');

SQL> EXEC DBMS_DDL.alter_compile('FUNCTION', 'SCHEMA_NAME', 'FUNCTION_NAME');

SQL> EXEC DBMS_DDL.alter_compile('TRIGGER', 'SCHEMA_NAME', 'TRIGGER_NAME');

In addition to the above approaches you can write your own script to get the invalid objects and recompile them using the manual approach to recompile them all included in the script.

How to get DDL for an object: TABLE, INDEX, PACKAGE....

noreply@blogger.com (kaspi) — Mon, 05 Apr 2010 15:55:00 +0000

DBMS_METADATA is a package that can be used to get DDL for TABLE, INDEXES etc. Below is a quick view of how it works:

SQL> select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

The output would be:

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

To get the create table definition without the storage clause you could do as follows:

SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

The output should be PL/SQL procedure successfully completed.

And then if you run

set long 100000
select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

would return

CREATE TABLE "SCOTT"."IDX3_TAB"
( "NAME" VARCHAR2(30),
"ID" NUMBER,
"ADDR" VARCHAR2(100),
"PHONE" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

But the above package wasn't useful when I tried to get DDL information for a package body based on my experience and DBA_SOURCE from into help for that. Below is an example:

SQL> SELECT TEXT FROM DBA_SOURCE WHERE NAME='PKG_DEPT_PROCESS';

CURSOR_SHARING Parameter in Oracle

noreply@blogger.com (kaspi) — Thu, 01 Apr 2010 20:51:00 +0000

CURSOR_SHARING parameter is the one that oracle uses to control whether it will auto-bind a SQL statement or not.

For example:
SQL> select * from dept where dept_id = 'literal_value';

Oracle takes the above statement and replaces the predicate with something as below:

SQL> select * from dept where dept_id=:"SYS_B_0";

And now the oracle compares the above sql statement to the plans that it already has in the memory to see if the plan can be reused or not, perhaps leading to a better utilization of shared_pool and reduction in number hard parses performed by the system.

The cursor_sharing parameter can be set to three values:

EXACT: This is the default setting. With this value in place, the query is not rewritten to use bind variables.
FORCE: This setting rewrites the query, replacing all literals with bind values and setting up a one-size-fits-all plan—a single plan for the rewritten query. I'll demonstrate what that implies in a moment.
SIMILAR: This setting also rewrites the query, replacing the literals with bind variables, but can set up different plans for different bind variable combinations. This last point is why CURSOR_SHARING=SIMILAR might reduce the number of plans generated. Because multiple plans may be generated, the setting of SIMILAR may or may not reduce the number of actual plans you observe in the shared pool.

An example to show how CURSOR_SHARING works with setting equal to EXACT, FORCE and SIMILAR:

SQL> alter session set cursor_sharing=EXACT;

Session altered.

SQL> select * from dual CS_EXACT where dummy='A';

no rows selected

SQL> select * from dual CS_EXACT where dummy='B';

no rows selected

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> select * from dual CS_FORCE where dummy='A';

no rows selected

SQL> select * from dual CS_FORCE where dummy='B';

no rows selected

SQL> alter session set cursor_sharing=SIMILAR;

Session altered.

SQL> select * from dual CS_SIMILAR where dummy='A';

no rows selected

SQL> select * from dual CS_SIMILAR where dummy='B';

no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from dual CS% where dummy=%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------------------------
select * from dual CS_EXACT where dummy='A'
select * from dual CS_EXACT where dummy='B'
select * from dual CS_FORCE where dummy=:"SYS_B_0"
select * from dual CS_SIMILAR where dummy=:"SYS_B_0"

CURSOR_SHARING=EXACT: From the above example as we can see oracle uses different plans for each of the select statements which include the word "CS_EXACT". With this setting every SQL statement you excute will be new and a new plan is generated for every query that we execute and the plans are not shared. And a new entry is created in V$SQL as you can see from above for every SQL Statement we execute.

CURSOR_SHARING=FORCE: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed.

CURSOR_SHARING=SIMILAR: When this is the setting for the parameter then for each statement that I have executed above which include the word "CS_FORCE" the literal values 'A' and 'B' are replaced by "SYS_B_0". The oracle uses the same plan for each of the SQL whether the predicate is either 'A' or 'B' since rest of the statement is similar and thus we see just one entry in V$SQL for the two SQL that we have executed. So, the settings FORCE and SIMILAR looks similar huh? But there is one difference between both i.e., when the setting is SIMILAR oracle not only checks for similar looking statements but also compares the plans. For example when I executed the SELECT statement with the literal value 'A' it generates a plan which will say a FULL TABLE SCAN for the statement. Now when I executed the same SELECT statement with the literal value 'B' the it also generates a plan for the statement and compares to the plan that is already stored i.e., when literal value is 'A'. If the explain plan for the SELECT statement when literal is 'A' a FULL TABLE SCAN and also a FULL TABLE SCAN (lets assume for now) when the literal value is 'B' then we see only one entry in the dynamic view V$SQL. If both the plans are different say a FULL TABLE SCAN when literal value is 'A' and an INDEX SCAN when literal value is 'B' which is not in this case as per our assumption then we will see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR" and you would see something as below (which is not with our example above): The results displayed shown below are with respect to an imaginary table "t" which is a big table of about atleast 100 rows and has an index on it and the data is skewed

SQL> alter session set cursor_sharing=FORCE;

Session altered.

SQL> select * from t CS_FORCE where t_id=1;

1 row selected.

SQL> select * from t CS_FORCE where t_id='99';

1 row selected.

SQL> alter session set cursor_sharing=SIMILAR;

Session altered.

SQL> select * from t CS_SIMILAR where t_id='1';

1 row selected.

SQL> select * from t CS_SIMILAR where t_id='99';

1 row selected.

SQL> select sql_text

2 from v$sql
3 where sql_text like 'select * from t CS% where t_id=%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------------------------
select * from t CS_FORCE where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"
select * from t CS_SIMILAR where t_id=:"SYS_B_0"

From the above example we can see that the plans are different say a FULL TABLE SCAN when literal value is '1' and an INDEX SCAN when literal value is '99' we see two different entries in V$SQL for the SQL statements which contain the word "CS_SIMILAR".

Database Structures: Logical Structures

noreply@blogger.com (kaspi) — Thu, 01 Apr 2010 13:37:00 +0000

Oracle database logical structures mainly comprise of Tablespaces, Segements, Extents and Oracle Datablocks.

I will present the information starting with the finest logical structure Oracle Datablocks.

Oracle Datablocks: Oracle datablocks are at the finest level granularity, all of the oracle datatbase data is stored in oracle datablocks. One oracle datablock corresponds to specific number of bytes which occupy the same number of bytes on the physical disk space. The size of a datablock is determined by the initialization parameter DB_BLOCK_SIZE. In addition to the one specified already you can specify upto 5 additional datablock sizes

Extent: Extent is the next level of oracle logical database space. A extent is comprised specific number of contiguous datablocks, obtained in a single allocation, used to store a specific type of information.

Segment: A segment is a set of extents allocated for a certain logical structure. The segments can be of one of following type data segment,index segment,temporary segment,rollback segment.

Tablespace: Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Database Structures: Physical Structures

noreply@blogger.com (kaspi) — Thu, 01 Apr 2010 13:33:00 +0000

A database consists of Physical Structures and Logical Structures in this post I will post information regarding the physical structures in simple terms:

Datafiles: Contain all of the database data; logical structures , such as tables, indexes, packages, procedures, functions, triggers and etc.

Redo Log Files: Hold records of all changes made to the database for recovery purposes.

Control Files: Record the physical structure and status of the database

Parameter File: Contain startup values for database parameters (often referred as the init.ora file )

Note: The explanation provided here is for understanding only.

Oracle Resource Manager

noreply@blogger.com (kaspi) — Wed, 31 Mar 2010 19:21:00 +0000

Oracle Resource Manager (ORM)
Goal of ORM:
To give Oracle Database Server more control over resource management decisions, thus preventing problems resulting from inefficient operating system management.

Problems addressed by ORM:• Excessive overhead
• Inefficient scheduling
• Inappropriate allocation of resources
• Inability to manage database-specific resources, such as parallel execution servers and active sessions

How above problems can be addressed:
• Guarantee specific users with certain amount of processing resources irrespective of the load on the system and the no. of users

Ex: HR_BATCH_GROUP – 50% CPU will be allocated for Batch processing jobs irrespective of the load on the system

• Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs.

Ex: CPU Resources distribution for HR:
HR_BATCH_GROUP – 50% CPU
HR_WF_REPT_AUD_METADATA_GROUP – 20% CPU
HR_ADHOC_HRLINK_WF_GROUP – 15% CPU
HR_ADHOC_BUS_USER_GROUP – 5% CPU
HR_STEAM_DBLINK_USERS_GROUP – 5% CPU
OTHER_GROUPS – 5% CPU

• Limit to 10 concurrent sessions for HR_ADHOC_BUS_USER_GROUP so that members of the group performing any operation are limited to maximum of 10 sessions within the group. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate. Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit
Ex: The parameter setting for this is ACTIVE_SESS_POOL_P1 => 10
Ex: The parameter setting for this is QUEUEING_P1 => 60
Ex: The parameter setting for this is MAX_EST_EXEC_TIME => 2700 (45 minutes)

• (not doing in below example) Allow automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.

• (not doing in below example) Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.

• (not doing in below example) Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.

• (not doing in below example) Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.

• (not doing in below example) Allow the cancellation of long-running SQL statements and the termination of long-running sessions.

Terminology:
Resource Plan: A resource plan is a way to group a set of resource consumer groups together and specify how resources should be divided among them

Resource consumer groups: Allow the administrator to group user sessions together by resource requirements

Resource allocation methods: determine what policy to use when allocating for any particular resource. Resource allocation methods are used by resource plans and resource consumer groups
Resource plan directives: are a means of assigning consumer groups to particular plans and partitioning resources among consumer groups by specifying parameters for each resource allocation method

Subplans: allow further subdivision of resources among different users of an application

Levels: provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified

Steps for implementing ORM:
1. Execute the HR_PLAN.sql and check for any errors. HR_PLAN.sql is an example of how an ORM Plan can be created.

2. Do the following once ORM has been installed succesfully without any errors
$ sqlplus "/ as sysdba"

a. SQL> 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='HR_PLAN';

b. Set the init parameter RESOURCE_MANAGER_PLAN = HR_PLAN in database init parameter file"

Note: Both the above steps are required to make sure the created plan is active both dynamically and permanent
to ensure that if database is bounced the plan is still active in case database cannot be bounced now

HR_PLAN:

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'HR_PLAN', COMMENT => 'HR ORM Plan');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_BATCH_GROUP', COMMENT => 'HR Batch group');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_WF_REPT_AUD_METADATA_GROUP', COMMENT => 'Reporting group');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_ADHOC_HRLINK_WF_GROUP', COMMENT => 'Ad-hoc WF HRLink group');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_ADHOC_BUS_USER_GROUP', COMMENT => 'Ad-hoc Business User group');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_STEAM_DBLINK_USERS_GROUP', COMMENT => 'Steam DBLink User group');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_BATCH_GROUP', COMMENT => 'Batch Process', CPU_P1 => 50);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_WF_REPT_AUD_METADATA_GROUP', COMMENT => 'Reporting Process', CPU_P1 =
> 20);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_ADHOC_HRLINK_WF_GROUP', COMMENT => 'Ad-hoc WF HRLink group', CPU_P1 =
> 15);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_ADHOC_BUS_USER_GROUP', COMMENT => 'Ad-hoc Business User group', CPU_P
1 => 5, MAX_EST_EXEC_TIME => 2700 , ACTIVE_SESS_POOL_P1 => 10, QUEUEING_P1 => 60);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_STEAM_DBLINK_USERS_GROUP', COMMENT => 'Steam User group', CPU_P1 => 5
);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN',GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P1 => 5);

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SYSADM', 'HR_BATCH_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SYSADM', 'HR_BATCH_GROUP');

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('WFLINK', 'HR_ADHOC_HRLINK_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('WFLINK', 'HR_ADHOC_HRLINK_WF_GROUP');

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('BUS_AH32366', 'HR_ADHOC_BUS_USER_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('BUS_AH32366', 'HR_ADHOC_BUS_USER_GROUP');

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('HR_LINK_APAC','HR_ST_DBLINK_USERS_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('HR_LINK_APAC','HR_STEAM_DBLINK_USERS_GROUP');

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('CSBOUTA1','HR_REPT_AUD_METADATA_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('CSBOUTA1','HR_WF_REPT_AUD_METADATA_GROUP');

END;
/

Steps for enabling ORM:
There are two ways for enabling ORM after you create your custom ORM Plan in the database. Below are the steps:
1. Enable it dynamically from a SQLPLUS Session
Ex: SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=’HR_PLAN’;
2. Enable it permanently in init.ora if you are using pfile
Ex: In init file add the parameter – RESOURCE_MANAGER_PLAN=’HR_PLAN’;

Both the above steps are recommended as database needs to be bounced if you update init file and if that cannot be done immediately Step 1 will take care of enabling the Resource Manager immediately and even if the database is bounced Step 2 will take care of enabling it Resource Manager automatically.

Steps for disabling ORM:There are two ways for enabling ORM after you create your custom ORM Plan in the database. Below are the steps:

1. Disable it dynamically from a SQLPLUS Session
Ex: SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=’ ’; (space in between)
2. Disable it permanently in init.ora if you are using pfile
Ex: In init file comment out or remove the parameter – RESOURCE_MANAGER_PLAN=’HR_PLAN’;

ORM FAQ's:
Q: How does the Resource Manager work in a multi-instance server?

A: The resource manager cannot manage resources across instances, only within an instance. It would not be useful in multi-instance on a single server situation. If you are having more than one instance, you must use OS tools such as taking a big honking Sun E10k and setting up separate domains (so all of a sudden you don't have more than one instance per "server")

Q: Overhead of using "Oracle Resource Manager"?

A: The implementation of a resource plan is going to take some resources itself (of course). You do not quantify at all what "non-insignificant" is - so I will not address that (everyone has a different definition for that...). so it is not really possible to comment.
Note: The above question has been taken from www.asktom.oracle.com website

Q: When does "Oracle Resource Manager" (ORM) kick in?

A: Resource manager basically does nothing until a resource is totally maxed out (you cannot put CPU in the bank and use it later). So, even if you say "100% to group A", unless group A actually needs to consume 100%. Resource manager is not a "fence", but rather an attempt to assure you that if group A makes a demand for X%, it will be given to them.

Q: Will there be any degradation in response time or execution time of jobs than the regular taken time?

A: Yes, when the server is busy and is at 100% CPU or more the jobs will take a long time to finish than what they might be taking regularly. But the response time will almost be the same.

Q. What is the overhead after implementing ORM?

A: There was no specific answer for what is the overhead of using ORM resource regulation. So, I did a test to find out the results of which are presented here but there is minimal to no impact while the server is not 100% busy and there might very little overhead when the server is 100% which hasn't been quantified yet but I did some estimation based on some tests.

Reference: Based on information read from Oracle Documentation and other online websites