Session Blocking

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;


Identifying the locked row:
select do.object_name,
    row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
    from v$session s, dba_objects do
    where sid=pid_del_que_ha_sido_bloqueado
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

CONSISTENT GET and DB BLOCK GET

Oracle accesses blocks in one of two modes, current or consistent.


A db block get/Current block get is a current mode get. That is, it's the most up-to-date copy of the data in that block, as it is right now, or currently. There can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does a db block get, and does not take a lock. That is, when it does a full table scan or fast full index scan, Oracle will read the segment header in current mode (multiple times, the number varies based on Oracle version).

A consistent get/Consistent Get/CR block get is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism. When blocks are fetched in order to satisfy a query result set, they are fetched in consistent mode. If no block in the buffer cache is consistent to the correct point in time, Oracle will (attempt to) reconstruct that block using the information in the rollback
segments. If it fails to do so, that's when a query errors out with the much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".

Log file sync WAITS

Log Writer works independently of user sessions so that theoretically it could have written out the redo before you issue a commit. The 'log file sync' wait occurs when you issue a commit and the redo hasn't been flushed yet so you post Log Writer and wait. One of the conditions under which the Log Writer kicks in is when the Log Buffer is 1/3 full which will happen less often if the Log Buffer is large. Therefore by making it smaller, Log Writer will work more proactively to flush it ideally before you issue a commit and thus you eliminate the wait entirely. See in my report where I say that the frequency of 'log file sync' is roughly equal to the statistic 'user commits' (i.e. every commit is waiting). So, making it smaller is designed to reduce the 'frequency' of waits on 'log file sync'.

o get historical information on what SQL statements are incurring the most disk reads

SELECT * FROM ( SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,

sum(disk_reads_total) disk_reads_total, sum(executions_delta) execs_delta, sum(executions_total) execs_total

FROM dba_hist_sqlstat GROUP BY sql_id ORDER BY 2 desc)

WHERE rownum <= 5;

top five recent SQL statements that performed the highest disk I/O

SELECT sql_text, disk_reads FROM (SELECT sql_text, buffer_gets, disk_reads, sorts,

cpu_time/1000000 cpu, rows_processed, elapsed_time FROM v$sqlstats ORDER BY disk_reads DESC)

WHERE rownum <= 5;

If you join V$SQLSTATS to V$SQL, you can see information for particular users. See the following example:

SELECT schema, sql_text, disk_reads, round(cpu,2)

FROM (SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,

t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time

FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)

WHERE parsing_schema_name = 'SCOTT' ORDER BY disk_reads DESC)

WHERE rownum <= 5;

The V$SQL_MONITOR view contains currently running SQL statements, as well as recently run SQL statements.

If you wanted to see the top five most CPU-consuming queries in your database, you could issue the following query:

SELECT * FROM ( SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds F

ROM v$sql_monitor ORDER BY cpu_time desc)

WHERE rownum <= 5;

Identifying Who Is Using the Temporary Tablespace

select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr

and o.sqladdr = h.address (+)

and o.tablespace = t.tablespace_name

order by s.sid;

mapping an operating system process back to a database process

ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head

SELECT

'USERNAME : ' || s.username || CHR(10) ||

'SCHEMA : ' || s.schemaname || CHR(10) ||

'OSUSER : ' || s.osuser || CHR(10) ||

'PROGRAM : ' || s.program || CHR(10) ||

'SPID : ' || p.spid || CHR(10) ||

'SID : ' || s.sid || CHR(10) ||

'SERIAL# : ' || s.serial# || CHR(10) ||

'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||

'MACHINE : ' || s.machine || CHR(10) ||

'TYPE : ' || s.type || CHR(10) ||

'TERMINAL : ' || s.terminal || CHR(10) ||

'SQL ID : ' || q.sql_id || CHR(10) ||

'SQL TEXT : ' || q.sql_text

FROM v$session s

,v$process p

,v$sql q

WHERE s.paddr = p.addr

AND p.spid = '&PID_FROM_OS'

AND s.sql_id = q.sql_id(+);

In this example, since the process is running a SQL statement, further details about the query can be extracted by generating an execution plan:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id')));

In case you need to KILL the process:

alter system kill session '1177,38583';

To view objects that are waiting for I/O resources

SELECT

object_name

,object_type

,owner

FROM v$session a

,dba_objects b

WHERE a.event LIKE 'db file%read'

AND b.data_object_id = a.row_wait_obj#

To determine which sessions are currently waiting for I/O resources, query V$SESSION

SELECT

username

,program

,machine

,sql_id

FROM v$session

WHERE event LIKE 'db file%read';

Which SQL are using excesive i/O

SELECT *

FROM

(SELECT parsing_schema_name, direct_writes, SUBSTR(sql_text,1,75), disk_reads

FROM v$sql

ORDER BY disk_reads DESC)

WHERE rownum < 20;

Gathering Statistics

Gathering stats doesn't lock the table/index, only locks the object statistics.

As of 11g, the gathering of object statistics is integrated into the automated tasks scheduled with the Scheduler:

SELECT task_name, status

FROM dba_autotask_task

WHERE client_name = 'auto optimizer stats collection';

Statistics history ,Display current value:

SELECT dbms_stats.get_stats_history_retention() AS retention from DUAL;

Set retention period to 14 days:

dbms_stats.alter_stats_history_retention(retention => 14);

When statistics were modified for a given table:

SELECT stats_update_time FROM dba_tab_stats_history

WHERE owner = 'SYS' and table_name = 'TAB$';

Restore object statistics of the schema SH to the values that were in use 1 day ago:

dbms_stats.restore_schema_stats(ownname => 'SH',as_of_timestamp => systimestamp – 1,force => TRUE)

dbms_stats log information about their execution:

SELECT operation, start_time,2 (end_time-start_time) DAY(1) TO SECOND(0) AS duration

FROM dba_optstat_operations ORDER BY start_time DESC;

Beautifying SqlPlus

SET LINESIZE 200

SET PAGESIZE 30

SET PAUSE ON

SET PAUSE 'MUCH HARDER'

COLUMN C1 FORMAT A20

COLUMN C2 NOPRINT

define_editor='vi'

steps to convert a RAC database to single instance

1) Stop database using srvctl

srvctl stop database -d prod

2) Remove the database entry from crs

bash-3.00$ srvctl remove instance -d prod -i prod1

Remove instance prod1 from the database prod? (y/[n]) y

bash-3.00$ srvctl remove instance -d prod -i prod2

Remove instance prod2 from the database prod? (y/[n]) y

bash-3.00$ srvctl remove database -d prod

Remove the database prod? (y/[n]) y

bash-3.00$

3) Start the database on first instance

SQL> startup

ORACLE instance started.

Total System Global Area 838860800 bytes

Fixed Size 1222168 bytes

Variable Size 188746216 bytes

Database Buffers 645922816 bytes

Redo Buffers 2969600 bytes

Database mounted.

Database opened.

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> alter system set cluster_database_instances=1 scope=spfile;

System altered.

SQL> alter database disable thread 2;

Database altered.

4) Delete the unwanted thread and redo logfiles

SQL> select thread#, group# from v$log;

THREAD# GROUP#

---------- ----------

1 1

1 2

1 3

2 4

2 5

2 6

6 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

5) Drop the unwanted undo tablespace

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

6) Create pfile from spfile

SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

7) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

Dedicated vs Shared Servers

I'll just summarise, because I can see some rather iffy advice being offered in this thread.

If you are running in 9i or above, there are two parameters (not one) which govern whether or not you are capable of running in shared server mode.

DISPATCHERS governs whether a job dispatcher runs. You have to have at least one of them configured before shared server is possible. However, the mere fact that a dispatcher runs does NOT mean your database is running in shared server mode. There also have to be shared server processes capable of handling the work dispatched by the dispatcher(s), and those are configured with the SHARED_SERVERS parameter. If that's set to any number greater than 1, you have shared server processes running on your instance. But that STILL doesn't mean you're running in shared server mode! If you have SHARED_SERVERS=57 and no dispatcher, you simply have 57 processes sitting around doing nothing whatsoever (and incapable of doing useful work!)

In short, you have to have DISPATCHERS and SHARED_SERVERS set.

Note, for example, that 10g configures a single dispatcher for all databases by default (if they're created with DBCA and you don't get in there to stop it happeneing), but it does NOT configure SHARED_SERVERS, so by default a 10g database does not run in shared server mode.

The other thing I'd clarify is that a database doesn't really run in shared server mode anyway! The fact that your instance has a dispatcher and shared server processes running doesn't necessarily mean your users will end up connected to the dispatcher and having shared server processes handling their job requests. They will by default, but if the tnsnames.ora they use to connect (or its centralised equivalent) contains the line SERVER=DEDICATED, then they will get to use dedicated server processes, no matter what the dispatcher or shared server processes might think about it!

With dispatchers and shared server processes configured, in other words, an instance can "support shared server connection requests". That's rather different than "running in shared server mode". The distinction is important because privileged actions (startup, shutdown, backup and recover commands) cannot be processed by a shared server process, so it's important for an instance that is configured for normal users to use shared server processes to still support the connection to dedicated server processes by suitably credentialled users.

If a user does end up connected to a shared server process, there is usually a performance penalty to pay compared to using a dedicated server process. A user submits a query and instead of it being immediately processed by a server process, it gets submitted to a dispatcher ...which promptly sticks it on a job queue! You then have to wait for a shared server process to become free and decide to pick your job off the queue. That's inevitably slower than doing it the dedicated way.

People use shared server as the first line of scaling up their databases... and you're right that it primarily depends on the number of users connected to the server concurrently. In dedicated server mode, a new connection means a new process gets spawned (or a new thread on Windows) and a new connection socket is opened. Servers can only handle so many connection sockets, processes or threads before they start to keel over under the strain. Shared server, as the name suggest, means that new connections do not cause new server processes to be spawned. So 300 users can be processed with, maybe, 30 or 40 processes in total. If your box would normally keel over handling 300 dedicated connections, then clearly with that sort of sharing ratio, you'd be able to scale to nearer 3000 users before it starts wilting by using shared processes.

But it's also a bit subtler than that: a data warehouse would be daft to implement shared server, even if it did have 300+ concurrent users. That's because the users of such systems typically run queries that run for hours... and a shared process that is nabbed to perform one job for hours on end isn't really a shared process any more, is it?! So the rule of thumb as to when to implement shared server is yes, (a) when your concurrent user count starts reaching levels that your server just doesn't seem able to sustain any more AND (b) when you can be sure that the users tend to issue short, sharp queries -say, about 3 seconds or so to process, max.

Again, there are mixed states to get through, too. You might have lots of OLTP-type sub-3-second transactions on the same database on which one or two users regularly run big reports. In that case, you make sure the reporters have a tnsnames.ora that says SERVER=DEDICATED and the OLTP-type people use one that has SERVER=SHARED in it; configure the DISPATCHERS and SHARED_SERVER parameters for the instance and then those that can benefit from shared servers can do so and those that wouldn't won't be stealing shared processes from those that can!

The alternative approach for those with more cash is to go and buy better server hardware that can cope with the user community numbers! Shared Server configuration, however, comes free. You pays your money and you takes your choices!

example:

alter system set max_shared_servers=100 scope=both;

alter system set max_dispatchers=150 scope=both;

alter system set dispatchers='(protocol=TCP) (SERVICE=gendb) (disp=5)' scope=both;

alter system set shared_servers=40 scope=both;

recall to amend the tnsnames.ora file on the client: (SERVER=SHARED)

Flash Recovery Area Space Consumption

Flash Recovery Area Space Consumption

SELECT * FROM V$RECOVERY_FILE_DEST;  

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES 

--------------  ----------- ---------- ----------------- --------------- 

/mydisk/rcva     5368709120 109240320             256000              28

SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;  

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES 

------------ ------------------ ------------------------- --------------- 

CONTROLFILE                   0                         0               0 

ONLINELOG                     2                         0              22 

ARCHIVELOG                 4.05                      2.01              31 

BACKUPPIECE                3.94                      3.86               8 

IMAGECOPY                 15.64                     10.43              66 

FLASHBACKLOG                .08                         0               1

Que pasa si perdemos un control file en ASM

antes de nada, para simular una perdida de este calibre en ASM, usamos asmcmd y desde alli borramos uno de los control file.

El modo de recuperarlo en un system sin ASM seria haciendo una copia desde unos de los control file que no han sido afectados. Al introducir ASM se complica la cosa asi que la forma de hacerlo es desde RMAN:

Shutdown immediate
Startup nomount
exit

Connect to database through rman
rman target /
restore controlfile from ‘+U04/DEVEL1/controlfile/control01.ctl’; # este seria uno de los control file que NO han sido afectados
alter database open;

Analyzing AWR/StatsPack top 5 events

From Oracle documentation:
“Wait events are statistics that are incremented by a server process or thread to indicate that it had to wait for an event to complete before being able to continue processing.”

For example, wait events can be related to I/O, locks, memory allocation, etc.

The top events tell us what the server processes wait for the most. Eliminating these wait events will definately reduce execution time, since server processes will not have to wait this time for other operations to complete.

There are many wait event in Oracle databases, this is a list of some important and common wait events:

• buffer busy waits
• free buffer waits
• db file scattered read
• db file sequential read
• enqueue waits
• log buffer space
• log file sync

Cloning a database using SnapView/clones on the same server

Using SnapView/clones for database cloning:

prerequisites:

cloning on the same server

11gR2

No ASM [Automatic Storage Management]

No FRA [Flash Back Recovery Area]

steps:

1. configure the network: Adding new service on listener.ora file and tnsnames.ora so that there is connectivity to the cloned database:

Add new entry on listener.ora file (in red):

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = kloned)

(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

)

)

Add new entry on tnsnames.ora file (in red):

KLONED =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oracle1.ehugox.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = kloned.ehugox.com)

)

)

... and check new service can be pinged:

$ tnsping kloned

2.Create a password file for the new database (under $ORACLE_HOME/dbs)

$ orapwd file=orapwkloned password=

3.Create an init.ora file from the source database.

Create the pfile using create pfile from spfile from the source database, then

edit it changing all occurrences of old database name to new name.

4.Create the new admin directory for the new database.

5.Start replication using Snapview clone/snapshot. Start a consistent session of the LUNs holding the database files. It not necessary to ALTER DATABASE BEGIN BACKUP because this EMC solution is storage-based consistency replication.

6. Stop replication using Snapview clone consistent fracture once all the LUNs involved are in a Sync and/or Consistent state.

7. Initiate a single Snapview consistent clone fracture.

8. Archive all unarchived redo logs>

sqlplus /nolog

SQL> connect / as sysdba

SQL> alter system archive log current;

9. export ORACLE_SID=kloned

10. startup nomount

11. Create new control file:

create controlfile set database "kloned" resetlogs

logfile

group 1 '/u01/app/oracle/oradata/kdbsingle1/redo01.log' size 50M,

group 2 '/u01/app/oracle/oradata/kdbsingle1/redo02.log' size 50M,

group 3 '/u01/app/oracle/oradata/kdbsingle1/redo03.log' size 50M

datafile

'/u01/app/oracle/oradata/kdbsingle1/example01.dbf',

'/u01/app/oracle/oradata/kdbsingle1/sysaux01.dbf',

'/u01/app/oracle/oradata/kdbsingle1/system01.dbf',

'/u01/app/oracle/oradata/kdbsingle1/users01.dbf',

'/u01/app/oracle/oradata/kdbsingle1/undotbs01.dbf';

12. Recover database and if everything goes fine open it:

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1197392 generated at 07/20/2011 18:02:22 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/DBSINGLE1/onlinelog/1_4_753801762.dbf

ORA-00280: change 1197392 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open resetlogs;

y eso es todo.....

Control File Waits Occur

Why Control File Waits Occur
The performance around reads and writes against control files is often an indication of misplaced control files that share the same I/O access path or are on devices that are heavily used. It is interesting to note that Oracle has always defaulted the creation of control files in a single directory. You can check where your control files reside on disk with this simple query.

SELECT name FROM v$controlfile;

If you wanted to check the amount of total system waits that have occurred for control file reads and write you could do so by querying the V$SYSTEM_EVENT view. This will give you the total number of waits, timeouts, and accumulated time for this event.

SELECT * 
FROM v$system_event
WHERE event IN (SELECT name 
FROM V$EVENT_NAME
WHERE name LIKE '%control%');

Likewise you could query the V$SESSION_WAIT view to see which sessions are experiencing control file waits in real time.

SELECT event, wait_time, p1, p2, p3
FROM v$session_wait 
WHERE event LIKE '%control%';

Here WAIT_TIME is the elapsed time for reads or writes. P1, P2, & P3 is either file#, block#, and blocks for 'control file sequential read' and 'control file single write' but is files, blocks, and requests for 'control file parallel write' Since there are no Oracle internal views for looking at control file layout like there is for 'normal' data and temporary files you can only accomplish a look into the control files by generating a dump. You can do this through the following ALTER SESSION command where is typically from 1 to 3 and represents dumping the file header, database & checkpoint records, and circular reuse record types. It is here in the trace file, which is generated in user_dump_dest, you can see that control file(s) have a file# of 0.

ALTER SESSION set events 'immediate trace name control level ';

Making Improvements
So how can I reduce the time spent for control file reads and writes? There are two distinct answers to this problem.

First, you can ensure that you have placed your control files on disks that are not under excessive heavy loads. When trying to determine how many control files to have, it is best to keep in mind that the more control files you have, the more I/O and time will be needed to write to all those copies. If is often better to have the O/S mirror the control files and reduce Oracle overhead.

Second, since the number of reads and writes are dictated by the type of activity within the database, it is often a good idea to revisit application logic and processing steps to ensure that excessive activities are not causing excessive reads and writes to the control files. For instance, I have seen code that produces excessive commits and even log switches. Since DBA activity is typically concentrated on modifying structures in the database, we need to be careful when performing batch runs of administrative scripts that could conflict with normal application processing. So be on the lookout for high activity levels such as log switching, checkpointing, backups taking place, and structural changes.

Summary
The control files are vital to database operations. Since they hold such vital information about the database it is imperative that they are safeguarded against disk I/O contention while making sure they are protected against corruption or loss. Balancing these two is often difficult but the common practice is to have multiple copies of the control files, keep them on separate physical disks, not putting them on heavily accessed disks. Also consider using operating system mirroring instead of Oracle multiplexing of the control files, taking and verify your control file backups daily, and create a new backup control file whenever a structural change is made. By taking control of your control files you can eliminate I/O contention and steer clear of becoming another war story on the net about yet another lost control file and unrecoverable database system.