This is what oracle says how to remove the CRS after a failed
CRS installation:-
How to Clean Up After a Failed 10g or 11.1 Oracle Clusterware Installation.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.8 - Release: 10.1 to 11.1
Generic UNIX
Purpose
The purpose of this document is to help DBA's and support analysts understand how
to clean up a failed CRS (Cluster Ready Services) install for 10g and 11.1 RAC.
For 11.2, see Note: 942166.1 How to Proceed from Failed 11gR2 Grid Infrastructure CRS) Installation
Scope and Application
DBA's and Support Analysts
How to Clean Up After a Failed 10g or 11.1 Oracle Clusterware Installation
10g and 11.1 RAC: How to Clean Up After a Failed CRS Install
------------------------------------------------------------
Not cleaning up a failed CRS install can cause problems like node reboots.
Follow these steps to clean up a failed CRS install:
1. Run the rootdelete.sh script then the rootdeinstall.sh script from the
$ORA_CRS_HOME/install directory on any nodes you are removing CRS from. Running
these scripts should be sufficent to clean up your CRS install. Rootdelete.sh
accepts options like nosharedvar/sharedvar, and nosharedhome/sharedhome. Make
yourself familiar with these options by reading the Oracle Clusterware and
Oracle Real Application Clusters Administration and Deployment Guide.
If you have any problems with these scripts please open a service request.
If for some reason you have to manually remove the install due to problems
with the scripts, continue to step 2:
2. Stop the Nodeapps on all nodes:
srvctl stop nodeapps -n
3. Prevent CRS from starting when the node boots. To do this issue the following
as root:
Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
Linux:
rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot
the node:
ps -ef | grep crs
kill
ps -ef | grep evm
kill
ps -ef | grep css
kill
Do not kill any OS processes, for example icssvr_daemon process !
5. If there is no other Oracle software running (like listeners, DB's, etc...),
you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:
rm -f /var/tmp/.oracle/*
or
rm -f /tmp/.oracle/*
6. Remove the ocr.loc
Usually the ocr.loc can be found at /etc/oracle
7. De-install the CRS home in the Oracle Universal Installer
8. Remove the CRS install location.
9. Clean out the OCR and Voting Files with dd commands. Example:
dd if=/dev/zero of=/dev/rdsk/V1064_vote_01_20m.dbf bs=1M count=256
dd if=/dev/zero of=/dev/rdsk/ocrV1064_100m.ora bs=1M count=256
See the Clusterware Installation Guide for sizing requirements...
If you placed the OCR and voting disk on a shared filesystem, remove them.
If you are removing the RDBMS installation, also clean out any ASM disks if
they have already been used.
10. The /tmp/CVU* dir should be cleaned also to avoid the cluvfy misreporting.
11. It is good practice to reboot the node before starting the next install.
12.If you would like to re-install CRS, follow the steps in the RAC Installation manual.
Reference:-
How to Proceed From a Failed 10g or 11.1 Oracle Clusterware (CRS) Installation [ID 239998.1]
***************************************************************************
11gR2
NOTE:942166.1 - How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation
GI Standalone Deconfigure and Reconfigure (Oracle Restart):
To deconfigure:
As root, execute "$GRID_HOME/crs/install/roothas.pl -deconfig -force -verbose"
If it fails, please disable GI, reboot the node and try the same command:
As root, execute "$GRID_HOME/bin/crsctl disable has"
As root, reboot the node; once the node comes backup, execute above deconfigure command again.
To reconfigure:
As root, execute "$GRID_HOME/root.sh
C. GI Cluster Deconfigure and Reconfigure
Identify cause of root.sh failure by reviewing logs in $GRID_HOME/cfgtoollogs/crsconfig and $GRID_HOME/log, once cause is identified and problem is fixed, deconfigure and reconfigure with steps below - keep in mind that you will need wait till each step finishes successfully before move to next one:
Step 0: For 11.2.0.2 and above, root.sh is restartable.
Once cause is identified and the problem is fixed, root.sh can be executed again on the failed node. If it succeeds, continue with your planned installation procedure; otherwise as root sequentially execute "$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force" and $GRID_HOME/root.sh on local node, if it succeeds, continue with your planned installation procedure, otherwise proceed to next step (Step 1) of the note.
Step 1: As root, run "$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force" on all nodes, except the last one.
Step 2: As root, run "$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode" on last node. This command will zero out OCR, Voting Disk and the ASM diskgroup for OCR and Voting Disk
-------------------------------------------------------------
Note:
a. Step1 and 2 can be skipped on node(s) where root.sh haven't been executed this time.
b. Step1 and 2 should remove checkpoint file. To verify:
ls -l $ORACLE_BASE/Clusterware/ckptGridHA_.xml
If it's still there, please remove it manually with "rm" command on all nodes
c. If GPNP profile is different between nodes/setup, clean it up on all nodes as grid user
$ rm -rf $GRID_HOME/gpnp/*
$ mkdir -p $GRID_HOME/gpnp/profiles/peer $GRID_HOME/gpnp/wallets/peer $GRID_HOME/gpnp/wallets/prdr $GRID_HOME/gpnp/wallets/pa $GRID_HOME/gpnp/wallets/root
The profile needs to be cleaned up:
c1. If root.sh is executed concurrently - one should not execute root.sh on any other nodes before it finishes on first node.
c2. If network info, location of OCR or Voting Disk etc changed after Grid is installed - rare
--------------------------------------------------------------------------------
Step 3: As root, run $GRID_HOME/root.sh on first node
Step 4: As root, run $GRID_HOME/root.sh on all other node(s), except last one.
Step 5: As root, run $GRID_HOME/root.sh on last node.
******************************************************************************
Sunday, June 26, 2011
Tuesday, June 21, 2011
Difference between V$sql and V$sqlarea
This is the explanaion given by Tom on difference between V$sql and V$sqlarea views, very helpful topic...
v$sql the details -- if you have multiple copies of the query:
"select * from T"
in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.
v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
It is not clear to me how you are joing v$session to v$sql to get more then one row. If you wish to see the queries a session has open (maybe open, we cache cursors so you might see some queries that are closed) use v$open_cursor by sid.
v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
-------------------------------------------------------------------------
There are many reasons there could be more than one sql statement.
the version used depends on the environment of the user running the sql statement. consider:
set linesize 1000
column PLAN_TABLE_OUTPUT format a80 truncate
column sql_text format a50
create table t ( x varchar2(30) primary key, y int );
Table created.
> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks=>100000
);
PL/SQL procedure successfully completed.
> alter system flush shared_pool;
System altered.
>select sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME
%B1_';
no rows selected
there is our setup, table T - very simple table, and now a very simple plsql block that will execute the same query 4 times:
> declare
l_x_number number;
l_x_string varchar2(30);
begin
execute immediate 'alter session set optimizer_mode=all_rows';
for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
execute immediate 'alter session set optimizer_mode=first_rows';
for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
end;
/
PL/SQL procedure successfully completed.
>
> column sql_id new_val sql_id
> select sql_id, sql_text from v$sql where upper(sql_text) like 'SELECT % T
LOOK_FOR_ME %B1_';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Now, there are four copies - they are IDENTICAL - but they are different. Because of the way I did my code, the cursors were parsed with entirely different environments
cursor 1) I used ALL_ROWS, and bound a number datatype
cursor 2) I used ALL_ROWS, and bound a varchar2 datatype
cursor 3) I used FIRST_ROWS - with a number
cursor 4) I used FIRST_ROWS - with a varchar2
Let's look at the plans for cursors 1 and 2:
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 0 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 0 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 0 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 0
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30891 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 292K| 30891 (2)| 00:02:27 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=:B1)
18 rows selected.
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 1 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 1 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 1 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 1
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 3817779948
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0023438 | 1 | | 1 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=:B1)
19 rows selected.
they are very different - all because of the binds. when you compare a string to a number there is an implicit to_number() placed on the string as shown above - we have not indexed to_number(x) therefore, we full scan - the other did not have to...
Now, looking at cursors 3 and 4 (2 and 3 - Oracle numbers from 0)...
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 2 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 2 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 2 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 2
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30891 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 292K| 30891 (2)| 00:02:27 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=:B1)
18 rows selected.
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 3 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 3 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 3 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 3
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 3817779948
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0023438 | 1 | | 1 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=:B1)
19 rows selected.
they look the same - but that is just a coincidence - they could have been different from the first two - these were optimized with first_rows - for initial response time - not all_rows as the first two were. Therefore, since the optimizer mode was different - we have a different optimizer environment and hence a different child cursor - we can see what makes these "different" via v$sql_shared_cursor:
> select child_number, bind_mismatch, optimizer_mode_mismatch
from v$sql_shared_cursor
where sql_id = '&SQL_ID'
/
old 3: where sql_id = '&SQL_ID'
new 3: where sql_id = '1qqtru155tyz8'
CHILD_NUMBER B O
------------ - -
0 N N
1 Y N
2 N Y
3 Y Y
That shows that cursor 0 and cursor 1 (the first two) differed in a bind mismatch (the Y value) and the next two differed from the previous because of optimizer mode mistmatches (and the last from the prior due to bind mismatch again as well)
All four are 'active'. If someone logs in and runs that query, binding a string, with first rows optimization - they'll use the last child cursor. If someone logs in with all_rows optimization and binds a number, they'll use the first child cursor.
One Readers Question on removing one single cursor from shared pool:-
You Asked
Hey Tom,
We recently had a query (using bind variables) whose plan flipped after our nightly stats gathering process due to bind variable peeking. We were hoping that by restoring stats to the previous day (on all the tables included in the query) that Oracle would re-examine possible execution plans when the next instance of that query was executed by the application, but that didn't appear to be the case when querying v$sql_plan. Were we missing something here? Thanks for your time.
-Dave
and we said...
well, if it were caused by bind peeking and peeking at the wrong "initial" binds, you would not resort to restoring old statistics (if the wrong bind was peeked again, the same plan would likely result). You wanted to have the "right" bind value peeked the first time.
the import schema stats has a no_invalidate parameter. this controls whether and how affected SQL is invalidated from the shared pool
it defaults to:
> select dbms_stats.get_param('NO_INVALIDATE') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE')
-------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
auto_invalidate will invalidate the cursors - slowly - over time - so as to not basdically "flush" the entire shared pool and cause a massive hard parse storm.
SQL_PROFILES:-
See the below for sql_profile example.
http://it.toolbox.com/blogs/living-happy-oracle/oracle-need-a-hint-use-sql-profiles-instead-of-stored-outlines-29638
v$sql the details -- if you have multiple copies of the query:
"select * from T"
in your shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.
v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
It is not clear to me how you are joing v$session to v$sql to get more then one row. If you wish to see the queries a session has open (maybe open, we cache cursors so you might see some queries that are closed) use v$open_cursor by sid.
v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
-------------------------------------------------------------------------
There are many reasons there could be more than one sql statement.
the version used depends on the environment of the user running the sql statement. consider:
set linesize 1000
column PLAN_TABLE_OUTPUT format a80 truncate
column sql_text format a50
create table t ( x varchar2(30) primary key, y int );
Table created.
> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks=>100000
);
PL/SQL procedure successfully completed.
> alter system flush shared_pool;
System altered.
>select sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME
%B1_';
no rows selected
there is our setup, table T - very simple table, and now a very simple plsql block that will execute the same query 4 times:
> declare
l_x_number number;
l_x_string varchar2(30);
begin
execute immediate 'alter session set optimizer_mode=all_rows';
for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
execute immediate 'alter session set optimizer_mode=first_rows';
for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
end;
/
PL/SQL procedure successfully completed.
>
> column sql_id new_val sql_id
> select sql_id, sql_text from v$sql where upper(sql_text) like 'SELECT % T
LOOK_FOR_ME %B1_';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Now, there are four copies - they are IDENTICAL - but they are different. Because of the way I did my code, the cursors were parsed with entirely different environments
cursor 1) I used ALL_ROWS, and bound a number datatype
cursor 2) I used ALL_ROWS, and bound a varchar2 datatype
cursor 3) I used FIRST_ROWS - with a number
cursor 4) I used FIRST_ROWS - with a varchar2
Let's look at the plans for cursors 1 and 2:
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 0 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 0 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 0 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 0
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30891 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 292K| 30891 (2)| 00:02:27 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=:B1)
18 rows selected.
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 1 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 1 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 1 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 1
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 3817779948
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0023438 | 1 | | 1 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=:B1)
19 rows selected.
they are very different - all because of the binds. when you compare a string to a number there is an implicit to_number() placed on the string as shown above - we have not indexed to_number(x) therefore, we full scan - the other did not have to...
Now, looking at cursors 3 and 4 (2 and 3 - Oracle numbers from 0)...
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 2 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 2 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 2 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 2
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30891 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 292K| 30891 (2)| 00:02:27 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=:B1)
18 rows selected.
> select * from table( dbms_xplan.display_cursor( '&SQL_ID', 3 ) );
old 1: select * from table( dbms_xplan.display_cursor( '&SQL_ID', 3 ) )
new 1: select * from table( dbms_xplan.display_cursor( '1qqtru155tyz8', 3 ) )
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1qqtru155tyz8, child number 3
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
Plan hash value: 3817779948
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)
|* 2 | INDEX UNIQUE SCAN | SYS_C0023438 | 1 | | 1 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=:B1)
19 rows selected.
they look the same - but that is just a coincidence - they could have been different from the first two - these were optimized with first_rows - for initial response time - not all_rows as the first two were. Therefore, since the optimizer mode was different - we have a different optimizer environment and hence a different child cursor - we can see what makes these "different" via v$sql_shared_cursor:
> select child_number, bind_mismatch, optimizer_mode_mismatch
from v$sql_shared_cursor
where sql_id = '&SQL_ID'
/
old 3: where sql_id = '&SQL_ID'
new 3: where sql_id = '1qqtru155tyz8'
CHILD_NUMBER B O
------------ - -
0 N N
1 Y N
2 N Y
3 Y Y
That shows that cursor 0 and cursor 1 (the first two) differed in a bind mismatch (the Y value) and the next two differed from the previous because of optimizer mode mistmatches (and the last from the prior due to bind mismatch again as well)
All four are 'active'. If someone logs in and runs that query, binding a string, with first rows optimization - they'll use the last child cursor. If someone logs in with all_rows optimization and binds a number, they'll use the first child cursor.
One Readers Question on removing one single cursor from shared pool:-
You Asked
Hey Tom,
We recently had a query (using bind variables) whose plan flipped after our nightly stats gathering process due to bind variable peeking. We were hoping that by restoring stats to the previous day (on all the tables included in the query) that Oracle would re-examine possible execution plans when the next instance of that query was executed by the application, but that didn't appear to be the case when querying v$sql_plan. Were we missing something here? Thanks for your time.
-Dave
and we said...
well, if it were caused by bind peeking and peeking at the wrong "initial" binds, you would not resort to restoring old statistics (if the wrong bind was peeked again, the same plan would likely result). You wanted to have the "right" bind value peeked the first time.
the import schema stats has a no_invalidate parameter. this controls whether and how affected SQL is invalidated from the shared pool
it defaults to:
> select dbms_stats.get_param('NO_INVALIDATE') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE')
-------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
auto_invalidate will invalidate the cursors - slowly - over time - so as to not basdically "flush" the entire shared pool and cause a massive hard parse storm.
SQL_PROFILES:-
See the below for sql_profile example.
http://it.toolbox.com/blogs/living-happy-oracle/oracle-need-a-hint-use-sql-profiles-instead-of-stored-outlines-29638
Wednesday, June 8, 2011
Privileges Required to Generate AWR Report
Below are the Privileges required to generate AWR Report:-
SELECT ON SYS.V_$DATABASE
SELECT ON SYS.V_$INSTANCE
EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY
SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE
SELECT ON SYS.DBA_HIST_SNAPSHOT
ADVISOR
AWR Report can be generated using:-
1. awrrpt.sql (This sql file is under rdbms/admin folder).
2. Using OEM
3. Using dbms_workload_repository package.
SELECT ON SYS.V_$DATABASE
SELECT ON SYS.V_$INSTANCE
EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY
SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE
SELECT ON SYS.DBA_HIST_SNAPSHOT
ADVISOR
AWR Report can be generated using:-
1. awrrpt.sql (This sql file is under rdbms/admin folder).
2. Using OEM
3. Using dbms_workload_repository package.
How to Pass '&' as argument's value
SQL> create or replace procedure test_this_man as
2 a varchar2(100);
3 begin
4 dbms_output.put_line ('Test');
5 a := 'AAA&DDDD';
6 dbms_output.put_line (a);
7* end;
SQL> /
Enter value for dddd: dd
old 5: a := 'AAA&DDDD';
new 5: a := 'AAAdd';
Warning: Procedure created with compilation errors.
To avoid this Error set the scan to off:-
sql> set scan off;
SQL> create or replace procedure test_this_man as
2 a varchar2(100);
3 begin
4 dbms_output.put_line ('Test');
5 a := 'AAA&DDDD';
6 dbms_output.put_line (a);
7* end;
SQL> /
Procedure created.
SQL> set serveroutput on;
SQL> exec test_This_man
Test
AAA&DDDD
2 a varchar2(100);
3 begin
4 dbms_output.put_line ('Test');
5 a := 'AAA&DDDD';
6 dbms_output.put_line (a);
7* end;
SQL> /
Enter value for dddd: dd
old 5: a := 'AAA&DDDD';
new 5: a := 'AAAdd';
Warning: Procedure created with compilation errors.
To avoid this Error set the scan to off:-
sql> set scan off;
SQL> create or replace procedure test_this_man as
2 a varchar2(100);
3 begin
4 dbms_output.put_line ('Test');
5 a := 'AAA&DDDD';
6 dbms_output.put_line (a);
7* end;
SQL> /
Procedure created.
SQL> set serveroutput on;
SQL> exec test_This_man
Test
AAA&DDDD
Tuesday, June 7, 2011
Client / Server Support
Watch out this page for Client / Server / Interoperability Support
between Different Oracle Versions...
http://www.myoraclesupports.com/content/client-server-interoperability-support-between-different-oracle-versions
between Different Oracle Versions...
http://www.myoraclesupports.com/content/client-server-interoperability-support-between-different-oracle-versions
Wednesday, June 1, 2011
Storage Values
Storage Capacity.
8 bits = 1 byte
Bytes: 1024 bytes = 1 KB (1 to 3 digits)
Kilobytes: 1024 KB = 1 MB (4 to 6 digits)
Megabytes: 1024 MB = 1 GB (7 to 9 digits)
Gigabytes: 1024 GB = 1 TB (10 to 12 digits)
Terabytes: 1024 TB = 1 PB (13 to 15 digits)
Petabytes: 1024 PB = 1 EB (16 to 18 digits)
Exabytes: 1024 EB = 1 ZB (19 to 21 digits)
Zettabytes: 1024 ZB = 1 YB (22 to 24 digits)
Yottabytes: more than enough... (25 to 27 digits)
8 bits = 1 byte
Bytes: 1024 bytes = 1 KB (1 to 3 digits)
Kilobytes: 1024 KB = 1 MB (4 to 6 digits)
Megabytes: 1024 MB = 1 GB (7 to 9 digits)
Gigabytes: 1024 GB = 1 TB (10 to 12 digits)
Terabytes: 1024 TB = 1 PB (13 to 15 digits)
Petabytes: 1024 PB = 1 EB (16 to 18 digits)
Exabytes: 1024 EB = 1 ZB (19 to 21 digits)
Zettabytes: 1024 ZB = 1 YB (22 to 24 digits)
Yottabytes: more than enough... (25 to 27 digits)
Applying Bug Fix Patches
Applying Bug fixes patches on oracle libraries using opatch utility:-
Necessary Steps:-
1) Download the Bug fix patch from metalink.
2) Change the owner of the patch file to oracle user.
# chown oracle:oinstall p7330434_111060_LINUX.zip
3) Set the PATH variable to locate the opatch utility.
$ export PATH=$PATH:$ORACLE_HOME/OPatch
4) unzip the patch and go the unzipped directory.
$ unzip p7330434_111060_LINUX.zip
$ cd 7330434
5) Check whether this patch can be applied online or its an offline patch
[oracle@linux11g 7330434]$ opatch query -all
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-06-02_09-06-52AM.log
--------------------------------------------------------------------------------
Patch created on 28 Oct 2009, 12:58:04 hrs PST8PDT
Need to shutdown Oracle instances: true
Patch is roll-backable: true
Patch is a rolling patch: true
Patch has sql related actions: false
Patch is a online patch: false
Patch is a portal patch: false
List of platforms supported:
46: Linux Intel
List of bugs to be fixed:
7330434: DBMS_MVIEW.REFRESH COMPLETE CAUSES ORA-600 [17147], ORA-600 [17114]
List of executables affected:
ORACLE_HOME/bin/oracle
List of optional components:
oracle.rdbms: 11.1.0.6.0
List of optional actions:
Update /u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a with /kkzf.o
Update /u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a with /qsmqutl.o
cd /u01/app/oracle/product/11.1.0/db_1/rdbms/lib
; make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
Possible XML representation of the patch:
7330434
--------------------------------------------------------------------------------
OPatch succeeded.
6) Review the above query, its states that the oracle instance has to be shutdown, hence shutdown the database.
7) Shutdown the database.
[oracle@linux11g software]$ export ORACLE_SID=test11g
[oracle@linux11g software]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jun 2 09:04:03 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux11g software]$ lsnrctl stop
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 02-JUN-2011 09:04:47
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
8) Apply the patch
[oracle@linux11g 7330434]$ opatch apply
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-06-02_09-08-09AM.log
ApplySession applying interim patch '7330434' to OH '/u01/app/oracle/product/11.1.0/db_1'
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.1.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '7330434' for restore. This might take a while...
Backing up files affected by the patch '7330434' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.6.0...
Updating archive file "/u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/kkzf.o"
Updating archive file "/u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/qsmqutl.o"
Running make for target ioracle
ApplySession adding interim patch '7330434' to inventory
Verifying the update...
Inventory check OK: Patch ID 7330434 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7330434 are present in Oracle Home.
The local system has been patched and can be restarted.
OPatch succeeded.
9) Check the status
[oracle@linux11g 7330434]$ opatch lsinventory
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-06-02_09-09-29AM.log
Lsinventory Output file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-06-02_09-09-29AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.1.0.6.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 7330434 : applied on Thu Jun 02 09:09:17 IST 2011
Created on 28 Oct 2009, 12:58:04 hrs PST8PDT
Bugs fixed:
7330434
--------------------------------------------------------------------------------
OPatch succeeded.
10) Start the oracle Instance and listener
[oracle@linux11g 7330434]$ export ORACLE_SID=test11g
[oracle@linux11g 7330434]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jun 2 09:09:55 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 348129408 bytes
Database Buffers 67108864 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
SQL> !lsnrctl start
SQL> exit
Necessary Steps:-
1) Download the Bug fix patch from metalink.
2) Change the owner of the patch file to oracle user.
# chown oracle:oinstall p7330434_111060_LINUX.zip
3) Set the PATH variable to locate the opatch utility.
$ export PATH=$PATH:$ORACLE_HOME/OPatch
4) unzip the patch and go the unzipped directory.
$ unzip p7330434_111060_LINUX.zip
$ cd 7330434
5) Check whether this patch can be applied online or its an offline patch
[oracle@linux11g 7330434]$ opatch query -all
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-06-02_09-06-52AM.log
--------------------------------------------------------------------------------
Patch created on 28 Oct 2009, 12:58:04 hrs PST8PDT
Need to shutdown Oracle instances: true
Patch is roll-backable: true
Patch is a rolling patch: true
Patch has sql related actions: false
Patch is a online patch: false
Patch is a portal patch: false
List of platforms supported:
46: Linux Intel
List of bugs to be fixed:
7330434: DBMS_MVIEW.REFRESH COMPLETE CAUSES ORA-600 [17147], ORA-600 [17114]
List of executables affected:
ORACLE_HOME/bin/oracle
List of optional components:
oracle.rdbms: 11.1.0.6.0
List of optional actions:
Update /u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a with /kkzf.o
Update /u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a with /qsmqutl.o
cd /u01/app/oracle/product/11.1.0/db_1/rdbms/lib
; make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
Possible XML representation of the patch:
--------------------------------------------------------------------------------
OPatch succeeded.
6) Review the above query, its states that the oracle instance has to be shutdown, hence shutdown the database.
7) Shutdown the database.
[oracle@linux11g software]$ export ORACLE_SID=test11g
[oracle@linux11g software]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jun 2 09:04:03 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux11g software]$ lsnrctl stop
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 02-JUN-2011 09:04:47
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
8) Apply the patch
[oracle@linux11g 7330434]$ opatch apply
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-06-02_09-08-09AM.log
ApplySession applying interim patch '7330434' to OH '/u01/app/oracle/product/11.1.0/db_1'
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.1.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '7330434' for restore. This might take a while...
Backing up files affected by the patch '7330434' for rollback. This might take a while...
Patching component oracle.rdbms, 11.1.0.6.0...
Updating archive file "/u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/kkzf.o"
Updating archive file "/u01/app/oracle/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/qsmqutl.o"
Running make for target ioracle
ApplySession adding interim patch '7330434' to inventory
Verifying the update...
Inventory check OK: Patch ID 7330434 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7330434 are present in Oracle Home.
The local system has been patched and can be restarted.
OPatch succeeded.
9) Check the status
[oracle@linux11g 7330434]$ opatch lsinventory
Invoking OPatch 11.1.0.6.0
Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.1.0/db_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.0
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_1/oui
Log file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-06-02_09-09-29AM.log
Lsinventory Output file location : /u01/app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-06-02_09-09-29AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.1.0.6.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 7330434 : applied on Thu Jun 02 09:09:17 IST 2011
Created on 28 Oct 2009, 12:58:04 hrs PST8PDT
Bugs fixed:
7330434
--------------------------------------------------------------------------------
OPatch succeeded.
10) Start the oracle Instance and listener
[oracle@linux11g 7330434]$ export ORACLE_SID=test11g
[oracle@linux11g 7330434]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jun 2 09:09:55 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 348129408 bytes
Database Buffers 67108864 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
SQL> !lsnrctl start
SQL> exit
Subscribe to:
Posts (Atom)