Thank you for all those attended my sessions during Collaborate 2010. Two of the sessions I presented were not mine; but that of Riyaj Shamsuddin. Riyaj was stuck in Denmark thanks to the ash cloud scenario in Europe and asked if I could present his. I agreed to and, with a lot of trepidation, I did. I hope I did justice to the sessions. For questions on those sessions, please reach out to Riyaj directly.
Going to my own sessions, here is where you can download the presentations. For the sessions I wanted to show live demos; but in a short span of 30 minutes for Quick Tips, it was impossible. You can download the scripts here so that you can check them out yourself. The slides show which scripts to execute.
RAC Performance Tuning, part of RAC Bootcamp (Recorded)
Stats with Intelligence (Recorded)
Publish Stats after Checking, part of Manageability Bootcamp (Recorded and shown via Webcast)
Once again, your patronage by attending is highly appreciated. A speaker is nothing without attendees. I sincerely hope that you got some value from the sessions. As always, I am looking forward to hearing from you – not just that you liked; but things you didn't.
Tuesday, April 20, 2010
Sunday, April 04, 2010
Online Materialized View Complete Refresh With Partitions
The other day I was posed with an interesting problem regarding the refresh of the materialized views. First, a little background. MVs are nothing new in Oracle; they have been around since Oracle 7 (called snapshots then). Essentially they are similar to tables (as they actually store data) but populated by running a query on some source tables. This query is the defining query of the MV. So, the user can select from the MV instead of executing the defining query – reducing the execution time significantly.
However, the MV and the query would both give the same result if the source tables have not changed. If the data has changed, the MV will not have known about it and will give a stale result. Therefore, you need to refresh the MV from time to time. Usually the MVs are refreshed by the following method:
begin
dbms_mview.refresh(‘’);
end;
So, what’s the problem? The problem is, during complete refreshes, the MV remains unavailable to the users. This duration of refresh depends on the size of the MV, the execution time of the underlying query and so on; and the unavailability is a nuisance for most applications. In some cases the applications are designed to timeout after some wait; and they do so quite consistently in this case – making it more than just a nuisance.
So, I was asked to devise a solution to make the MVs available during the complete refreshes. I accomplished that with a not-so-common use of partitions. Here is how I did it.
Prebuilt Table
First, as an architectural standard, I advocate the use of prebuilt tables in MVs. Here is how an MV is usually created:
create materialized view mv_bookings_01
refresh complete on demand
enable query rewrite
as
select hotel_id, sum(book_txn) tot_txn
from bookings
group by hotel_id
/
This creates the segment mv_bookings_01 during this process. Instead of this approach, I recommend first creating and populating a regular table and then converting that to an MV. In this revised approach, you would first create the table:
create table mv_bookings_01
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/
Then, you would create the MV using the prebuilt table option:
create materialized view mv_bookings_01
on prebuilt table
refresh complete on demand
enable query rewrite
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/
This does not do anything to the functionality of the MV itself. The MV feels, looks and smells like it was before; but with two significant advantages:
(1) You can easily alter the columns
(2) You can manipulate the table
I blogged about it at http://arup.blogspot.com/2009/01/ultra-fast-mv-alteration-using-prebuilt.html. Anyway, back to the issue at hand. Using the prebuilt trick, I can create a partitioned MV as well. In this specific case, I will add a column to partition on. Remember, this column does not actually participate in the application; it simply creates an anchor point for the partition.
create table mv_bookings_01
(
ref_type number,
hotel_id number(3),
tot_txn number
)
partition by list (ref_type)
(
partition p0 values (0),
partition p1 values (1)
)
/
After the table is created, I can insert data into it:
insert /*+ append */ into mv_bookings_01
select 0, hotel_id, sum(book_txn) tot_txn
from bookings
group by 0, hotel_id
/
Of course I could have selected the data from the source tables directly while creating the prebuilt table; but I just wanted to show a different approach of data loading. The column REF_TYPE is not part of the application; but I have added it to divide the table along a value by list partitioning. The column can hold only two values – 0 and 1, and hence the table has only two partitions.
Once the table is created, I can use the prebuilt table option to create the MV as shown above. However, the presence of the new column makes it a little different. My MV creation script now looks like this:
create materialized view mv_bookings_01
on prebuilt table
enable query rewrite
as
select 0 as ref_type, HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by 0, HOTEL_ID
/
Note that I have used “0 as ref_type” in the select clause, i.e. the ref_type will always be 0. This is not going to a problem as you can see later. When the MV is first created, the value of ref_type is 0; hence the partition p0 is the one is that is populated; not the partition p1. We can confirm that:
SQL> select count(1) from mv_bookings_01 partition (p0);
COUNT(1)
----------
2
SQL> select count(1) from mv_bookings_01 partition (p1);
COUNT(1)
----------
0
Now that the partitions are in place, let’s see how we refresh this MV. We will no longer use the dbms_mview approach. Rather, we will use this:
(1) Create a temporary table
(2) Create all the indexes
(3) Exchange the partition that is not used with this temporary table
Step 1: Temporary table
First we create a temporary table that is identical to the prebuilt table of the MV in structure; but just not partitioned. We will use the value of ref_type column as 0 or 1 based on the value already in the table, in fact exactly opposite of what is in the table. Since we have 0 in the table, we will use 1 in the temporary table. This temporary table will contain the data that we need refreshed as.
create table tmp1
as
select 1 as ref_type, hotel_id, sum(book_txn) tot_txn
from bookings
group by 1, hotel_id
/
We also need to create another temporary table with no data.
create table tmp0
(
ref_type number,
hotel_id number(3),
tot_txn number
)
/
Step 2: Create Indexes
In this case we don’t have any indexes on the MV; but if we had, we would have created them here on the TMP0 and TMP1 tables.
Step 3: Exchange Partitions
When the temporary table is ready, we can exchange the inactive partition with this.
alter table mv_bookings_01
exchange partition p1
with table tmp1
including indexes
without validation
update global indexes
/
alter table mv_bookings_01
exchange partition p0
with table tmp0
including indexes
without validation
update global indexes
/
This SQL performs a switch: the segment that was called partition P1 in the table mv_bookings_01 is now called TMP1 and former segment TMP1 is now called partition P1 in the table mv_bookings_01. This occurs at the data dictionary level; no actual data is ever transferred from one segment to the other. As a result this is very quick. A lock is required on the table only for that instance is such an insignificant amount of time that it may not be even noticeable. Similarly the segment used to be called P0 partition is now known as TMP0 table and TMP0 is called P0.
There are several key things to note here. Note the clause “without validation”, which tells Oracle not to bother checking inside the TMP0 table that it will confirm to the partition specification. This is critical for the performance. Since we built the table with “1” as a hardcoded value, we know that the REF_TYPE column will definitely contain 1, satisfying the partition requirement for P1. A further checking is not required.
The “including indexes” clause switched the local index partitions as well. If there is a global index on the MV’s prebuilt table, that would have been invalidated and needed rebuilding afterwards. But, we avoided that invalidation by using a “update global indexes” clause in the alter statement.
Once the switchover is complete, we can check the MV to see if the data has been visible.
SQL> select count(1) from mv_bookings_01 partition (p0)
2 /
COUNT(1)
----------
0
SQL> select count(1) from mv_bookings_01 partition (p1)
2 /
COUNT(1)
----------
2
This completes the refreshing of the MV and the data is visible to end users. Let’s see the timeline
(1) Building of temporary table
(2) Building of indexes on the temporary table
(3) Exchange partitions
Steps 1 and 2 take most of the time; but that is done offline, without affecting the MV itself. So, long time there does not affect the availability of the MV itself. The step 3 is where the availability is impacted; but that is miniscule.
The table TMP0 now has the rows from partition P0 of the MV. To confirm that:
SQL> select count(1) from tmp0;
COUNT(1)
----------
2
You can drop the table by “drop table tmp0 purge”. You should also drop TMP1 since that table will contain the previous contents of the partition P1, which is useless now.
The next time this MV needs refreshing, you have to repeat the process; but with a very important distinction – the partition P0 needs exchange now. I used a script that checks the value of REF_TYPE in the MV now and use the other value passed to an SQL script with a positional parameter that accepts 0 or 1. Using this approach you can refresh a MV very quickly.
Oracle 11g Variation
In Oracle 11g, there is a new feature – Virtual Column. You can define a column as virtual in the table, which is not actually stored in the table; but calculated during runtime. What’s more, this virtual column can also be used as a partition key. You can define the REF_TYPE as a virtual column in that case.
Takeaways
(1) Just because there is an Materialized View, it does not have to be refreshed by the usual approach, i.e. dbms_mview.refresh. There are other options as well.
(2) Partitioning is not just for performance or purge reasons; it can be used in clever ways such as MV refreshes.
(3) In this specific case, we didn’t have a column that could have been used as a partitioning key; so we had to resort to adding a column. In many cases, the table may already have a column for that purpose, making the process even simpler.
However, the MV and the query would both give the same result if the source tables have not changed. If the data has changed, the MV will not have known about it and will give a stale result. Therefore, you need to refresh the MV from time to time. Usually the MVs are refreshed by the following method:
begin
dbms_mview.refresh(‘
end;
So, what’s the problem? The problem is, during complete refreshes, the MV remains unavailable to the users. This duration of refresh depends on the size of the MV, the execution time of the underlying query and so on; and the unavailability is a nuisance for most applications. In some cases the applications are designed to timeout after some wait; and they do so quite consistently in this case – making it more than just a nuisance.
So, I was asked to devise a solution to make the MVs available during the complete refreshes. I accomplished that with a not-so-common use of partitions. Here is how I did it.
Prebuilt Table
First, as an architectural standard, I advocate the use of prebuilt tables in MVs. Here is how an MV is usually created:
create materialized view mv_bookings_01
refresh complete on demand
enable query rewrite
as
select hotel_id, sum(book_txn) tot_txn
from bookings
group by hotel_id
/
This creates the segment mv_bookings_01 during this process. Instead of this approach, I recommend first creating and populating a regular table and then converting that to an MV. In this revised approach, you would first create the table:
create table mv_bookings_01
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/
Then, you would create the MV using the prebuilt table option:
create materialized view mv_bookings_01
on prebuilt table
refresh complete on demand
enable query rewrite
as
select HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by HOTEL_ID
/
This does not do anything to the functionality of the MV itself. The MV feels, looks and smells like it was before; but with two significant advantages:
(1) You can easily alter the columns
(2) You can manipulate the table
I blogged about it at http://arup.blogspot.com/2009/01/ultra-fast-mv-alteration-using-prebuilt.html. Anyway, back to the issue at hand. Using the prebuilt trick, I can create a partitioned MV as well. In this specific case, I will add a column to partition on. Remember, this column does not actually participate in the application; it simply creates an anchor point for the partition.
create table mv_bookings_01
(
ref_type number,
hotel_id number(3),
tot_txn number
)
partition by list (ref_type)
(
partition p0 values (0),
partition p1 values (1)
)
/
After the table is created, I can insert data into it:
insert /*+ append */ into mv_bookings_01
select 0, hotel_id, sum(book_txn) tot_txn
from bookings
group by 0, hotel_id
/
Of course I could have selected the data from the source tables directly while creating the prebuilt table; but I just wanted to show a different approach of data loading. The column REF_TYPE is not part of the application; but I have added it to divide the table along a value by list partitioning. The column can hold only two values – 0 and 1, and hence the table has only two partitions.
Once the table is created, I can use the prebuilt table option to create the MV as shown above. However, the presence of the new column makes it a little different. My MV creation script now looks like this:
create materialized view mv_bookings_01
on prebuilt table
enable query rewrite
as
select 0 as ref_type, HOTEL_ID, sum(book_txn) tot_txn
from bookings
group by 0, HOTEL_ID
/
Note that I have used “0 as ref_type” in the select clause, i.e. the ref_type will always be 0. This is not going to a problem as you can see later. When the MV is first created, the value of ref_type is 0; hence the partition p0 is the one is that is populated; not the partition p1. We can confirm that:
SQL> select count(1) from mv_bookings_01 partition (p0);
COUNT(1)
----------
2
SQL> select count(1) from mv_bookings_01 partition (p1);
COUNT(1)
----------
0
Now that the partitions are in place, let’s see how we refresh this MV. We will no longer use the dbms_mview approach. Rather, we will use this:
(1) Create a temporary table
(2) Create all the indexes
(3) Exchange the partition that is not used with this temporary table
Step 1: Temporary table
First we create a temporary table that is identical to the prebuilt table of the MV in structure; but just not partitioned. We will use the value of ref_type column as 0 or 1 based on the value already in the table, in fact exactly opposite of what is in the table. Since we have 0 in the table, we will use 1 in the temporary table. This temporary table will contain the data that we need refreshed as.
create table tmp1
as
select 1 as ref_type, hotel_id, sum(book_txn) tot_txn
from bookings
group by 1, hotel_id
/
We also need to create another temporary table with no data.
create table tmp0
(
ref_type number,
hotel_id number(3),
tot_txn number
)
/
Step 2: Create Indexes
In this case we don’t have any indexes on the MV; but if we had, we would have created them here on the TMP0 and TMP1 tables.
Step 3: Exchange Partitions
When the temporary table is ready, we can exchange the inactive partition with this.
alter table mv_bookings_01
exchange partition p1
with table tmp1
including indexes
without validation
update global indexes
/
alter table mv_bookings_01
exchange partition p0
with table tmp0
including indexes
without validation
update global indexes
/
This SQL performs a switch: the segment that was called partition P1 in the table mv_bookings_01 is now called TMP1 and former segment TMP1 is now called partition P1 in the table mv_bookings_01. This occurs at the data dictionary level; no actual data is ever transferred from one segment to the other. As a result this is very quick. A lock is required on the table only for that instance is such an insignificant amount of time that it may not be even noticeable. Similarly the segment used to be called P0 partition is now known as TMP0 table and TMP0 is called P0.
There are several key things to note here. Note the clause “without validation”, which tells Oracle not to bother checking inside the TMP0 table that it will confirm to the partition specification. This is critical for the performance. Since we built the table with “1” as a hardcoded value, we know that the REF_TYPE column will definitely contain 1, satisfying the partition requirement for P1. A further checking is not required.
The “including indexes” clause switched the local index partitions as well. If there is a global index on the MV’s prebuilt table, that would have been invalidated and needed rebuilding afterwards. But, we avoided that invalidation by using a “update global indexes” clause in the alter statement.
Once the switchover is complete, we can check the MV to see if the data has been visible.
SQL> select count(1) from mv_bookings_01 partition (p0)
2 /
COUNT(1)
----------
0
SQL> select count(1) from mv_bookings_01 partition (p1)
2 /
COUNT(1)
----------
2
This completes the refreshing of the MV and the data is visible to end users. Let’s see the timeline
(1) Building of temporary table
(2) Building of indexes on the temporary table
(3) Exchange partitions
Steps 1 and 2 take most of the time; but that is done offline, without affecting the MV itself. So, long time there does not affect the availability of the MV itself. The step 3 is where the availability is impacted; but that is miniscule.
The table TMP0 now has the rows from partition P0 of the MV. To confirm that:
SQL> select count(1) from tmp0;
COUNT(1)
----------
2
You can drop the table by “drop table tmp0 purge”. You should also drop TMP1 since that table will contain the previous contents of the partition P1, which is useless now.
The next time this MV needs refreshing, you have to repeat the process; but with a very important distinction – the partition P0 needs exchange now. I used a script that checks the value of REF_TYPE in the MV now and use the other value passed to an SQL script with a positional parameter that accepts 0 or 1. Using this approach you can refresh a MV very quickly.
Oracle 11g Variation
In Oracle 11g, there is a new feature – Virtual Column. You can define a column as virtual in the table, which is not actually stored in the table; but calculated during runtime. What’s more, this virtual column can also be used as a partition key. You can define the REF_TYPE as a virtual column in that case.
Takeaways
(1) Just because there is an Materialized View, it does not have to be refreshed by the usual approach, i.e. dbms_mview.refresh. There are other options as well.
(2) Partitioning is not just for performance or purge reasons; it can be used in clever ways such as MV refreshes.
(3) In this specific case, we didn’t have a column that could have been used as a partitioning key; so we had to resort to adding a column. In many cases, the table may already have a column for that purpose, making the process even simpler.
Friday, March 26, 2010
Difference between Session and Process
Someone asked me the other day the difference between process and session in Oracle. Are they the same thing? Well, why then there are two parameters in the initialization parameter file – sessions and processes? Is it possible that a process does not have a corresponding session? Conversely, is it possible to have a session without a process?
And, furthermore, the processes parameter is larger than the sessions. So, are there more processes than sessions? To answer that, she got the count from v$session and v$process:
SQL> select count(1) from v$process;
COUNT(1)
----------
23
SQL> select count(1) from v$session;
COUNT(1)
----------
20
At least the answer was pretty clear – there are more processes than sessions (23 versus 20). But then, someone pointed to the view V$RESOURCE_LIMIT, which records the current usage of the various definable limits. Checking the limits for two resources – sessions and processes:
select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.
This contradicts what we saw earlier. The processes usage matches – 23 processes seen from the resource limit view and count from v$process; but the session count differs. From the resource limit, we see that there are 26 sessions; but v$session shows only 20. Why there is a difference?
Processes without Sessions
Let’s see the first one – processes without sessions. We can easily find them:
select pid, spid, addr, program
from v$process
where addr not in (
select paddr from v$session
)
PID SPID ADDR PROGRAM
---------- ---------- -------- ------------------------------
1 4116A2B8 PSEUDO
18 23904 411758AC oracle@oradba1 (D000)
19 23906 41176360 oracle@oradba1 (S000)
Other than the first one, these are real processes. The processes refer to the dispatcher process and the shared server processes. These are not background processes; so they don’t appear in V$BGPROCESS.
Sessions without Processes
Now what about the second case – the sessions without processes? That one is harder to visualize. A session in Oracle is a connection to the database. As we know from the two-task architecture, a client process of a session kicks off a server process which in turn does the heavy lifting from the Oracle database. Keeping that in mind, it might be difficult to envision a session without a process. With a twist of the same concept, is it possible for two sessions to share a single process? Once again, that defies understanding as well. A session is an exclusive conduit to the database; how can that be shared? Even in case of share servers, each shared server session has its own process.
It is possible, under some special circumstances. They are used by Recursive Database Sessions, persistent for a very small amount of time; but they are sessions. When you create a table, alter an index or perform other tasks, Oracle must perform certain tasks on your behalf, which are called recursive actions. One example may make it clear. When you create a table, Oracle must determine that you have sufficient privileges to do that and the determination is made through a recursive session. Let’s see a demonstration of the sessions in action.
First connect to the database and get your own SID:
select sid, paddr, spid
from v$session s, v$process p
where s.audsid = userenv('SESSIONID')
and p.addr = s.paddr
/
SID PADDR SPID
---------- -------- ------------------------
17 4117F938 24042
This shows that my own session has the SID 17, the OS process id of 24042 and the address of the server process is 4117F938. I know, I know; this is a 32-bit system, so the process address is small.
If you look at v$process, you can get the details on that process as well, using the paddr value. Conversely, if you select the sessions with that paddr value:
SQL> select sid from v$session where paddr = '4117F938';
SID
---
17
There is exactly one session. If you check it for any process paddr, you will see exactly one SID. But, that’s where the mystery lies. The v$session view does not tell the whole story; it does not show all the sessions. Here is the definition of the view:
SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SESSION';
VIEW_DEFINITION
----------------------------------------------------------------------------------
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetr
n),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIP
ED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'), s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ksusemnm,s.ksusetid,
s.ksusepnm, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_numbe
r(null), s.ksusesch), s.ksusesesta, decode(s.ksuseseid, 0, to_number(null), s.ksuseseid), s.ksusepsq, s.ksusepha, s.ksusepsi, decode(s.ksusepch, 65535,
to_number(null), s.ksusepch), s.ksusepesta, decode(s.ksusepeid, 0, to_number(null), s.ksusepeid), decode(s.ksusepeo,0,to_number(null),s.ksusepeo), decod
e(s.ksusepeo,0,to_number(null),s.ksusepes), decode(s.ksusepco,0,to_number(null),s.ksusepco), decode(s.ksusepco,0,to_number(null),s.ksusepcs), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxo
pt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),dec
ode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),decode(bitand(s.ks
usepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'
DISABLED','ENABLED')), s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLD
ER', 4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292
,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_numb
er(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 65535)),w.kslwtseq,w.kslwtevt,e.kslednam, e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w.kslwtin
wait, 0,decode(bitand(w.kslwtflags,256), 0,-2, decode(round(w.kslwtstime/10000), 0,-1,
round(w.kslwtstime/10000))), 0), decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000), round(w.kslwtstime/1000000)), de
code(w.kslwtinwait,1,'WAITING', decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME', decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME', 'WAIT
ED KNOWN TIME'))),w.kslwtstime, decode(w.kslwtinwait,0,to_number(null), decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), , decode(
bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE'),decode(b
itand(s.ksuseflg2,65536) + bitand(s.ksuseflg2,131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC'),s.ksuudsae,s.ksusecre,s.ksusecsn from x$ksuse s, x$ksled e, x$kslwt w where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.indx=w.kslwtsid and w.kslwtevt=e.indx
Note the section shown in red. The ksuseflg column in x$ksuse contains a lot of information, which the v$session view extracts and presents in a readable manner. Let’s use exactly the same SQL directly from the prompt. Noting that the ksusepro and indx and ksuudsna columns show the process address (paddr), the SID and the username respectively, we can extract the information:
select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/
INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
It shows the same information that v$session would have shown; but with a big difference. Note the view definition again – there is a join condition at the end with two other views: x$ksled and x$kslwt. Not all the session information is available on the other views. So the join filters out some rows from x$ksuse. To examine when it does that and that sessions get created automatically, you have to conduct an experiment.
First you have to look at a view called V$RESOURCE_LIMIT, which shows the various definable limits, how much is being used and – most important – how much has been the high water mark usage of the limit. We are interested in only 2 limits – sessions and processes.
select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49
This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.
In this experiment, keep the above session connected to SYS. On a different OS prompt, connect to database using SQL*Plus as user ARUP. At the SQL> prompt, type host, which will show an OS prompt. From that shell, connect to the database again as ARUP. From the prompt go to host prompt and connect using SQL*Plus. Do it over and over so that the number of sessions builds up as shown below:
SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 24 21:54:56 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup
.....
and so on ....
After some time you will receive an error like this:
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
From session 1 (where you are connected as sysdba), check the resource limits:
select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 39 40 40
sessions 45 47 49
This makes perfect sense. Note the current number of processes – 39, and the limit is 40. The very last session tried to create a session; and it failed, since it would have pushed the limit above 40.
Now, in the session of ARUP, execute a PL/SQL code that creates and drops a lot of objects in succession.
begin
for i in 1..1000 loop
execute immediate 'drop table t';
execute immediate 'create table t as select * from all_objects where 1=2';
end loop;
end;
/
This simply creates and drops the table called t in succession, 1000 times. While this code is running, check for the session from the x$ksuse view directly. You already know the process address:
select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/
INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
10 RECURSIVE SYS
This is interesting. There are two sessions – SIDs 7 and 10 against the same process with address 41182408. Why is that? SID 7 is the actual session which you connected. If you look at the decoded value, it shows RECURSIVE for the SID 10, which shows that for the user SYS. This session was kicked off by Oracle for the recursive actions, which pushed the session count up without a real user session. This is a situation where a single process serves 2 sessions.
Another interesting point is to know what this recursive session’s waiting on? To know that you have to check the view X$KSLWT. First set the null display to “?” so that null values show up as “?”:
SQL> set null ?
Now execute the query to get the wait event:
select s.indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name, w.kslwtevt
from x$ksuse s, x$kslwt w
where ksusepro = '41182408'
and s.indx=w.kslwtsid (+)
/
INDX DECODE(BIT USER_NAME KSLWTEVT
---------- ---------- ------------------------------ ----------
4 RECURSIVE SYS ?
7 USER ARUP 72
Note, I used the outer join. Why? It’s because the session will not be found in the wait events view, which is confirmed by the null output. As you can glean from the above output, the wait event is not recorded for the recursive sessions. This is why a corresponding row was not found in the view V$SESSION which joins x$ksuse and x$kslwt without an outer join.
Now you must be curious about other such sessions where they share the same process. Let’s check:
SQL> select ksusepro, count(1)
2 from x$ksuse
3 group by ksusepro
4 having count(1) > 1;
KSUSEPRO COUNT(1)
-------- ----------
00 2
4117AE4C 2
41174344 2
41178E30 2
4117EE84 2
4117D91C 2
41174DF8 2
4117B900 2
4117837C 2
4117CE68 2
4116CD88 2
4117A398 2
We got several processes with addresses. Next, let’s find out the exact program names.
SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select v.addr, v.program
9 from proc_tab p, v$process v
10 where v.addr = p.addr;
ADDR PROGRAM
-------- ------------------------------------------------
4117AE4C oracle@oradba1 (FBDA)
41174344 oracle@oradba1 (MMON)
41178E30 oracle@oradba1 (ARC1)
4117EE84 oracle@oradba1 (W000)
4117D91C oracle@oradba1 (CJQ0)
41174DF8 oracle@oradba1 (MMNL)
4117B900 oracle@oradba1 (QMNC)
4117837C oracle@oradba1 (ARC0)
4117CE68 oracle@oradba1 (q001)
4116CD88 oracle@oradba1 (DBRM)
4117A398 oracle@oradba1 (ARC3)
These are the sessions related to the background processes – Flashback Data Archiver, Memory Monitor and so on. Let’s dive down even further and look into the sessions these processes serve:
SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select p.addr, indx sid, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
9 ksuudsna user_name
10 from x$ksuse x, proc_tab p
11 where ksusepro = p.addr
12 order by 1,2;
ADDR SID DECODE(BIT USER_NAME
-------- ---------- ---------- ------------------------------
00 1 RECURSIVE SYS
00 10 RECURSIVE SYS
4116CD88 23 RECURSIVE SYS
4116CD88 45 BACKGROUND SYS
41174344 30 RECURSIVE SYS
41174344 34 BACKGROUND SYS
41174DF8 20 RECURSIVE SYS
41174DF8 33 BACKGROUND SYS
4117837C 3 RECURSIVE SYS
4117837C 29 BACKGROUND SYS
41178E30 2 RECURSIVE SYS
41178E30 28 BACKGROUND SYS
4117A398 25 RECURSIVE SYS
4117A398 26 BACKGROUND SYS
4117AE4C 21 RECURSIVE SYS
4117AE4C 22 BACKGROUND SYS
4117B900 19 RECURSIVE SYS
4117B900 24 BACKGROUND SYS
4117CE68 15 RECURSIVE SYS
Note, for each of the background processes has a corresponding recursive session, which is counted towards the total session count in V$RESOURCE_LIMIT, even if you don’t actually create user sessions. These recursive sessions are not visible in V$SESSION.
Takeaways
1. The first lesson to learn is a process not always ≠ a session. In many cases there is a one-to-one relationship; but not always. When Oracle has to run a recursive SQL as a result of some user commands, it creates the session objects behind the scenes to fulfill that request.
2. Second, these recursive sessions count towards the overall sessions limit. To know the current sessions connected, use the V$RESOURCE_LIMIT view instead of selecting the sum from V$SESSION.
And, furthermore, the processes parameter is larger than the sessions. So, are there more processes than sessions? To answer that, she got the count from v$session and v$process:
SQL> select count(1) from v$process;
COUNT(1)
----------
23
SQL> select count(1) from v$session;
COUNT(1)
----------
20
At least the answer was pretty clear – there are more processes than sessions (23 versus 20). But then, someone pointed to the view V$RESOURCE_LIMIT, which records the current usage of the various definable limits. Checking the limits for two resources – sessions and processes:
select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.
This contradicts what we saw earlier. The processes usage matches – 23 processes seen from the resource limit view and count from v$process; but the session count differs. From the resource limit, we see that there are 26 sessions; but v$session shows only 20. Why there is a difference?
Processes without Sessions
Let’s see the first one – processes without sessions. We can easily find them:
select pid, spid, addr, program
from v$process
where addr not in (
select paddr from v$session
)
PID SPID ADDR PROGRAM
---------- ---------- -------- ------------------------------
1 4116A2B8 PSEUDO
18 23904 411758AC oracle@oradba1 (D000)
19 23906 41176360 oracle@oradba1 (S000)
Other than the first one, these are real processes. The processes refer to the dispatcher process and the shared server processes. These are not background processes; so they don’t appear in V$BGPROCESS.
Sessions without Processes
Now what about the second case – the sessions without processes? That one is harder to visualize. A session in Oracle is a connection to the database. As we know from the two-task architecture, a client process of a session kicks off a server process which in turn does the heavy lifting from the Oracle database. Keeping that in mind, it might be difficult to envision a session without a process. With a twist of the same concept, is it possible for two sessions to share a single process? Once again, that defies understanding as well. A session is an exclusive conduit to the database; how can that be shared? Even in case of share servers, each shared server session has its own process.
It is possible, under some special circumstances. They are used by Recursive Database Sessions, persistent for a very small amount of time; but they are sessions. When you create a table, alter an index or perform other tasks, Oracle must perform certain tasks on your behalf, which are called recursive actions. One example may make it clear. When you create a table, Oracle must determine that you have sufficient privileges to do that and the determination is made through a recursive session. Let’s see a demonstration of the sessions in action.
First connect to the database and get your own SID:
select sid, paddr, spid
from v$session s, v$process p
where s.audsid = userenv('SESSIONID')
and p.addr = s.paddr
/
SID PADDR SPID
---------- -------- ------------------------
17 4117F938 24042
This shows that my own session has the SID 17, the OS process id of 24042 and the address of the server process is 4117F938. I know, I know; this is a 32-bit system, so the process address is small.
If you look at v$process, you can get the details on that process as well, using the paddr value. Conversely, if you select the sessions with that paddr value:
SQL> select sid from v$session where paddr = '4117F938';
SID
---
17
There is exactly one session. If you check it for any process paddr, you will see exactly one SID. But, that’s where the mystery lies. The v$session view does not tell the whole story; it does not show all the sessions. Here is the definition of the view:
SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$SESSION';
VIEW_DEFINITION
----------------------------------------------------------------------------------
select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct,s.ksusesow, decode(s.ksusetrn,hextoraw('00'),null,s.ksusetr
n),decode(s.ksqpswat,hextoraw('00'),null,s.ksqpswat),decode(bitand(s.ksuseidl,11),1,'ACTIVE',0,decode(bitand(s.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIP
ED',3,'SNIPED', 'KILLED'),decode(s.ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE'), s.ksuudsid,s.ksuudsna,s.ksuseunm,s.ksusepid,s.ksusemnm,s.ksusetid,
s.ksusepnm, decode(bitand(s.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'), s.ksusesql, s.ksusesqh, s.ksusesqi, decode(s.ksusesch, 65535, to_numbe
r(null), s.ksusesch), s.ksusesesta, decode(s.ksuseseid, 0, to_number(null), s.ksuseseid), s.ksusepsq, s.ksusepha, s.ksusepsi, decode(s.ksusepch, 65535,
to_number(null), s.ksusepch), s.ksusepesta, decode(s.ksusepeid, 0, to_number(null), s.ksusepeid), decode(s.ksusepeo,0,to_number(null),s.ksusepeo), decod
e(s.ksusepeo,0,to_number(null),s.ksusepes), decode(s.ksusepco,0,to_number(null),s.ksusepco), decode(s.ksusepco,0,to_number(null),s.ksusepcs), s.ksuseapp,
s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj, s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,decode(bitand(s.ksusepxo
pt, 12),0,'NO','YES'),decode(s.ksuseft, 2,'SESSION', 4,'SELECT',8,'TRANSACTIONAL','NONE'),decode(s.ksusefm,1,'BASIC',2,'PRECONNECT',4,'PREPARSE','NONE'),dec
ode(s.ksusefs, 1, 'YES', 'NO'),s.ksusegrp,decode(bitand(s.ksusepxopt,4),4,'ENABLED',decode(bitand(s.ksusepxopt,8),8,'FORCED','DISABLED')),decode(bitand(s.ks
usepxopt,2),2,'FORCED',decode(bitand(s.ksusepxopt,1),1,'DISABLED','ENABLED')),decode(bitand(s.ksusepxopt,32),32,'FORCED',decode(bitand(s.ksusepxopt,16),16,'
DISABLED','ENABLED')), s.ksusecqd, s.ksuseclid, decode(s.ksuseblocker,4294967295,'UNKNOWN', 4294967294, 'UNKNOWN',4294967293,'UNKNOWN',4294967292,'NO HOLD
ER', 4294967291,'NOT IN WAIT','VALID'),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_number(null), 4294967293,to_number(null), 4294967292
,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 2147418112)/65536),decode(s.ksuseblocker, 4294967295,to_number(null),4294967294,to_numb
er(null), 4294967293,to_number(null), 4294967292,to_number(null),4294967291, to_number(null),bitand(s.ksuseblocker, 65535)),w.kslwtseq,w.kslwtevt,e.kslednam, e.ksledp1,w.kslwtp1,w.kslwtp1r, e.ksledp2,w.kslwtp2,w.kslwtp2r,e.ksledp3,w.kslwtp3,w.kslwtp3r, e.ksledclassid,e.ksledclass#,e.ksledclass, decode(w.kslwtin
wait, 0,decode(bitand(w.kslwtflags,256), 0,-2, decode(round(w.kslwtstime/10000), 0,-1,
round(w.kslwtstime/10000))), 0), decode(w.kslwtinwait,0,round((w.kslwtstime+w.kslwtltime)/1000000), round(w.kslwtstime/1000000)), de
code(w.kslwtinwait,1,'WAITING', decode(bitand(w.kslwtflags,256),0,'WAITED UNKNOWN TIME', decode(round(w.kslwtstime/10000),0,'WAITED SHORT TIME', 'WAIT
ED KNOWN TIME'))),w.kslwtstime, decode(w.kslwtinwait,0,to_number(null), decode(bitand(w.kslwtflags,64),64,0,w.kslwttrem)), , decode(
bitand(s.ksuseflg2,32),32,'ENABLED','DISABLED'),decode(bitand(s.ksuseflg2,64),64,'TRUE','FALSE'),decode(bitand(s.ksuseflg2,128),128,'TRUE','FALSE'),decode(b
itand(s.ksuseflg2,65536) + bitand(s.ksuseflg2,131072),65536,'ALL EXEC',131072,'NEVER',0,'FIRST EXEC'),s.ksuudsae,s.ksusecre,s.ksusecsn from x$ksuse s, x$ksled e, x$kslwt w where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.indx=w.kslwtsid and w.kslwtevt=e.indx
Note the section shown in red. The ksuseflg column in x$ksuse contains a lot of information, which the v$session view extracts and presents in a readable manner. Let’s use exactly the same SQL directly from the prompt. Noting that the ksusepro and indx and ksuudsna columns show the process address (paddr), the SID and the username respectively, we can extract the information:
select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/
INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
It shows the same information that v$session would have shown; but with a big difference. Note the view definition again – there is a join condition at the end with two other views: x$ksled and x$kslwt. Not all the session information is available on the other views. So the join filters out some rows from x$ksuse. To examine when it does that and that sessions get created automatically, you have to conduct an experiment.
First you have to look at a view called V$RESOURCE_LIMIT, which shows the various definable limits, how much is being used and – most important – how much has been the high water mark usage of the limit. We are interested in only 2 limits – sessions and processes.
select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 23 23 40
sessions 26 26 49
This shows that the highest possible numbers for sessions and processes are 40 and 49 respectively. [Note, I have set the processes parameter in init.ora to 49 to reduce the limit artificially]. It shows that currently there are 26 sessions and 23 processes. Finally it shows the sessions and processes have touched a high number of 26 and 23 respectively.
In this experiment, keep the above session connected to SYS. On a different OS prompt, connect to database using SQL*Plus as user ARUP. At the SQL> prompt, type host, which will show an OS prompt. From that shell, connect to the database again as ARUP. From the prompt go to host prompt and connect using SQL*Plus. Do it over and over so that the number of sessions builds up as shown below:
SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 24 21:54:56 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> host
oracle@oradba1 ~/arup# sqlplus arup/arup
.....
and so on ....
After some time you will receive an error like this:
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
From session 1 (where you are connected as sysdba), check the resource limits:
select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name in ( 'sessions', 'processes')
/
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes 39 40 40
sessions 45 47 49
This makes perfect sense. Note the current number of processes – 39, and the limit is 40. The very last session tried to create a session; and it failed, since it would have pushed the limit above 40.
Now, in the session of ARUP, execute a PL/SQL code that creates and drops a lot of objects in succession.
begin
for i in 1..1000 loop
execute immediate 'drop table t';
execute immediate 'create table t as select * from all_objects where 1=2';
end loop;
end;
/
This simply creates and drops the table called t in succession, 1000 times. While this code is running, check for the session from the x$ksuse view directly. You already know the process address:
select indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name
from x$ksuse
where ksusepro = '41182408'
/
INDX DECODE(BIT USER_NAME
---------- ---------- ------------------------------
7 USER ARUP
10 RECURSIVE SYS
This is interesting. There are two sessions – SIDs 7 and 10 against the same process with address 41182408. Why is that? SID 7 is the actual session which you connected. If you look at the decoded value, it shows RECURSIVE for the SID 10, which shows that for the user SYS. This session was kicked off by Oracle for the recursive actions, which pushed the session count up without a real user session. This is a situation where a single process serves 2 sessions.
Another interesting point is to know what this recursive session’s waiting on? To know that you have to check the view X$KSLWT. First set the null display to “?” so that null values show up as “?”:
SQL> set null ?
Now execute the query to get the wait event:
select s.indx, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
ksuudsna user_name, w.kslwtevt
from x$ksuse s, x$kslwt w
where ksusepro = '41182408'
and s.indx=w.kslwtsid (+)
/
INDX DECODE(BIT USER_NAME KSLWTEVT
---------- ---------- ------------------------------ ----------
4 RECURSIVE SYS ?
7 USER ARUP 72
Note, I used the outer join. Why? It’s because the session will not be found in the wait events view, which is confirmed by the null output. As you can glean from the above output, the wait event is not recorded for the recursive sessions. This is why a corresponding row was not found in the view V$SESSION which joins x$ksuse and x$kslwt without an outer join.
Now you must be curious about other such sessions where they share the same process. Let’s check:
SQL> select ksusepro, count(1)
2 from x$ksuse
3 group by ksusepro
4 having count(1) > 1;
KSUSEPRO COUNT(1)
-------- ----------
00 2
4117AE4C 2
41174344 2
41178E30 2
4117EE84 2
4117D91C 2
41174DF8 2
4117B900 2
4117837C 2
4117CE68 2
4116CD88 2
4117A398 2
We got several processes with addresses. Next, let’s find out the exact program names.
SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select v.addr, v.program
9 from proc_tab p, v$process v
10 where v.addr = p.addr;
ADDR PROGRAM
-------- ------------------------------------------------
4117AE4C oracle@oradba1 (FBDA)
41174344 oracle@oradba1 (MMON)
41178E30 oracle@oradba1 (ARC1)
4117EE84 oracle@oradba1 (W000)
4117D91C oracle@oradba1 (CJQ0)
41174DF8 oracle@oradba1 (MMNL)
4117B900 oracle@oradba1 (QMNC)
4117837C oracle@oradba1 (ARC0)
4117CE68 oracle@oradba1 (q001)
4116CD88 oracle@oradba1 (DBRM)
4117A398 oracle@oradba1 (ARC3)
These are the sessions related to the background processes – Flashback Data Archiver, Memory Monitor and so on. Let’s dive down even further and look into the sessions these processes serve:
SQL> with proc_tab as
2 (
3 select ksusepro addr
4 from x$ksuse
5 group by ksusepro
6 having count(1) > 1
7 )
8 select p.addr, indx sid, decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','UNKNOWN'),
9 ksuudsna user_name
10 from x$ksuse x, proc_tab p
11 where ksusepro = p.addr
12 order by 1,2;
ADDR SID DECODE(BIT USER_NAME
-------- ---------- ---------- ------------------------------
00 1 RECURSIVE SYS
00 10 RECURSIVE SYS
4116CD88 23 RECURSIVE SYS
4116CD88 45 BACKGROUND SYS
41174344 30 RECURSIVE SYS
41174344 34 BACKGROUND SYS
41174DF8 20 RECURSIVE SYS
41174DF8 33 BACKGROUND SYS
4117837C 3 RECURSIVE SYS
4117837C 29 BACKGROUND SYS
41178E30 2 RECURSIVE SYS
41178E30 28 BACKGROUND SYS
4117A398 25 RECURSIVE SYS
4117A398 26 BACKGROUND SYS
4117AE4C 21 RECURSIVE SYS
4117AE4C 22 BACKGROUND SYS
4117B900 19 RECURSIVE SYS
4117B900 24 BACKGROUND SYS
4117CE68 15 RECURSIVE SYS
Note, for each of the background processes has a corresponding recursive session, which is counted towards the total session count in V$RESOURCE_LIMIT, even if you don’t actually create user sessions. These recursive sessions are not visible in V$SESSION.
Takeaways
1. The first lesson to learn is a process not always ≠ a session. In many cases there is a one-to-one relationship; but not always. When Oracle has to run a recursive SQL as a result of some user commands, it creates the session objects behind the scenes to fulfill that request.
2. Second, these recursive sessions count towards the overall sessions limit. To know the current sessions connected, use the V$RESOURCE_LIMIT view instead of selecting the sum from V$SESSION.
Wednesday, March 10, 2010
Thank you for all those who attended my webcast today on SQL Plan Management for All India Oracle User Group. It was a privilege to present before you and to be able to address your questions. I am sorry I couldn;t read all the questions properly; since it was extremely difficult to see the questions scrolling up in the tiny chat window. Also, as the webcast was designed, I couldn't hear anything the attendees were saying.
The presentation and the associated SQL scripts are available here. The article I referred to can be found here.
If you have a question regarding that specific webcast, please post a comment here and I will address it here. Please, limit your questions to the material discussed in the webcast only.
The presentation and the associated SQL scripts are available here. The article I referred to can be found here.
If you have a question regarding that specific webcast, please post a comment here and I will address it here. Please, limit your questions to the material discussed in the webcast only.
Tuesday, December 29, 2009
Instance_Number is busy Message during Standby Instance Startup
Recently I encountered a situation that demonstrates how, in an Oracle database, an error may stem from a very unrelated cause. A DBA was building a physical standby database for an upcominmg training. The two servers he was using were part of a RAC cluster; so the Oracle binaries were already there. He decided to use the same ORACLE_HOME for the new databases as well - a quite logical decision to savbe on space and administration issues. He created a primary database on server n1 and a standby database on the server n2. Follooing the usual manual procedure in building the standby database, he copied the pfile from primary database, modified the parameters and brought up the standby instance in nomount mode on the server n2.
SQL> startup nomount pfile=initSTBY.ora
But it refused to come up, with the following error:
ORA-00304: requested INSTANCE_NUMBER is busy
Alert log showed:
USER (ospid: 14210): terminating the instance due to error 304
Instance terminated by USER, pid = 14210
This was highly unusual. The primary and standby both were non-RAC; there was no instance number concept in a non-RAC database. By the way, the RAC instance on that server (or on the server n1) was not running; so there was no question of any conflict with the RAC instances either. The primary database was called PRIM while the standby was called STBY - eliminating the possibility of an instance name clash as well. And this error came while merely trying to start the instance, not even while mounting - eliminating the standby controlfile as a cause.
The error 304 showed:
00304, 00000, "requested INSTANCE_NUMBER is busy"
// *Cause: An instance tried to start by using a value of the
// initialization parameter INSTANCE_NUMBER that is already in use.
// *Action: Either
// a) specify another INSTANCE_NUMBER,
// b) shut down the running instance with this number
// c) wait for instance recovery to complete on the instance with
// this number.
Needless to say, being for a non-RAC database there was no "instance_number" parameter in the initialization parameter file of primary or the standby. So, the suggestions for the resolution seemed odd. MetaLink provided no help. All the ORA-304 errors were related to RAC with the instance_number mismatch.
As it always happens, it fell on my lap at this time. With just days to go live, I had to find a solution quickly. Long hours of troubleshooting, tracing the processes and examination of occasional trace files did not yield any clue. All the clue seemed to point to RAC, which this database was not. The Oracle Home was a RAC home, which meant the oracle binary was linked with the "rac" option.
So, the next logical step was to install a new Oracle Home without the rac option. After doing so, I tried to bring up the instance, using the new ORACLE_HOME and LD_LIBRARY_PATH variable; but, alas, the same error popped up.
Just to eliminate the possibility of some unknown bug, I decided to put an instance_number parameter, setting it to "1", from the default "0". The same error. I changed it to "2", again, the result was the same error.
Although this didn't help, at least it gave a clue that the error was not related to instance_number. The error message was clearly wrong. With this in mind, I went back to the basics. I went through the alert log with a fine toothed comb, scanning and analyzing each line.
The following line drew my attention:
DB_UNQIUE_NAME STBY is not in the Data Guard configuration
This was odd; the db_unique_name STBY was not defined in DG configuration. [BTW, note the spelling of "unique" in the message above. That is not what I typed; it was a copy and paste from the actual message in the alert log. Someone in Oracle Development should really pay atytention to typos in messages. This is clearly more than a nuisance; what if some process scans for db_unique_name related errors? It will not find the message at all!]
Checking the dg configuration, I found that the DBA has correctly defined the primary and standby names. In any case, Data Guard has not been started yet; this is merely at the instance startup - why is it complaining for data guard configuration at this time.
Perplexed, I resorted to a different approach. I renamed the pfile and all other relevant files. Then I built the standby myself, from scratch - using the same names - PRIM and STBY. And this time, everything worked fine. The instance STBY did come up.
While this solvbed the urgency problem, everyone, inclduing myself, wanted to know what the issue was in the earlier case where the DBA had failed to bring up the instance. To get the answer, I compared the files I created with the DBA created when tried and failed. Voila! The cause was immediately clear - the DBA forgot to put a vital parameter in the pfile of the standby instance:
db_unique_name = 'STBY'
This parameter was absent; so it took the default value as the db_name, which was "PRIM". This caused the instance to fail with a seemingly unrelated message - "ORA-304 Instance_number is busy"!
Learning Points
SQL> startup nomount pfile=initSTBY.ora
But it refused to come up, with the following error:
ORA-00304: requested INSTANCE_NUMBER is busy
Alert log showed:
USER (ospid: 14210): terminating the instance due to error 304
Instance terminated by USER, pid = 14210
This was highly unusual. The primary and standby both were non-RAC; there was no instance number concept in a non-RAC database. By the way, the RAC instance on that server (or on the server n1) was not running; so there was no question of any conflict with the RAC instances either. The primary database was called PRIM while the standby was called STBY - eliminating the possibility of an instance name clash as well. And this error came while merely trying to start the instance, not even while mounting - eliminating the standby controlfile as a cause.
The error 304 showed:
00304, 00000, "requested INSTANCE_NUMBER is busy"
// *Cause: An instance tried to start by using a value of the
// initialization parameter INSTANCE_NUMBER that is already in use.
// *Action: Either
// a) specify another INSTANCE_NUMBER,
// b) shut down the running instance with this number
// c) wait for instance recovery to complete on the instance with
// this number.
Needless to say, being for a non-RAC database there was no "instance_number" parameter in the initialization parameter file of primary or the standby. So, the suggestions for the resolution seemed odd. MetaLink provided no help. All the ORA-304 errors were related to RAC with the instance_number mismatch.
As it always happens, it fell on my lap at this time. With just days to go live, I had to find a solution quickly. Long hours of troubleshooting, tracing the processes and examination of occasional trace files did not yield any clue. All the clue seemed to point to RAC, which this database was not. The Oracle Home was a RAC home, which meant the oracle binary was linked with the "rac" option.
So, the next logical step was to install a new Oracle Home without the rac option. After doing so, I tried to bring up the instance, using the new ORACLE_HOME and LD_LIBRARY_PATH variable; but, alas, the same error popped up.
Just to eliminate the possibility of some unknown bug, I decided to put an instance_number parameter, setting it to "1", from the default "0". The same error. I changed it to "2", again, the result was the same error.
Although this didn't help, at least it gave a clue that the error was not related to instance_number. The error message was clearly wrong. With this in mind, I went back to the basics. I went through the alert log with a fine toothed comb, scanning and analyzing each line.
The following line drew my attention:
DB_UNQIUE_NAME STBY is not in the Data Guard configuration
This was odd; the db_unique_name STBY was not defined in DG configuration. [BTW, note the spelling of "unique" in the message above. That is not what I typed; it was a copy and paste from the actual message in the alert log. Someone in Oracle Development should really pay atytention to typos in messages. This is clearly more than a nuisance; what if some process scans for db_unique_name related errors? It will not find the message at all!]
Checking the dg configuration, I found that the DBA has correctly defined the primary and standby names. In any case, Data Guard has not been started yet; this is merely at the instance startup - why is it complaining for data guard configuration at this time.
Perplexed, I resorted to a different approach. I renamed the pfile and all other relevant files. Then I built the standby myself, from scratch - using the same names - PRIM and STBY. And this time, everything worked fine. The instance STBY did come up.
While this solvbed the urgency problem, everyone, inclduing myself, wanted to know what the issue was in the earlier case where the DBA had failed to bring up the instance. To get the answer, I compared the files I created with the DBA created when tried and failed. Voila! The cause was immediately clear - the DBA forgot to put a vital parameter in the pfile of the standby instance:
db_unique_name = 'STBY'
This parameter was absent; so it took the default value as the db_name, which was "PRIM". This caused the instance to fail with a seemingly unrelated message - "ORA-304 Instance_number is busy"!
Learning Points
- In Oracle, most errors are obvious; but some are not. So, do not assume the error message is accurate. If all logic fails, assume the error messsage is wrong, or at least inaccurate.
- Instead of over-analyzing the process already followed, it may make sense to take a breather, wipe out everything and start fropm scratch. This is evben mor effective when someone else does it, offering a fresh approach and possibly not repeating the same mistakes.
- Finally, the issue at hand: if you do not define db_unique_name parameter in the standnby instance, you will receive ORA-304 during instance startup.
Hope this was helpful. Happy New Year, everybody.
Friday, December 18, 2009
Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckup
Recently, we had a glitch on a Data Guard (physical standby database) on infrastructure. This is not a critical database; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either. In any case, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discovered only when the customer complained. This standby database is usually opened for read only access from time to time.This time, however, the customer saw that the data was significantly out of sync with primary and raised a red flag. Unfortunately, at this time it had become a rather political issue.
Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary, there are two parts of the problem:
(1) What happened
(2) How to fix it
What Happened
Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases. On the primary:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447102
On the standby:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1301571
Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:
SQL> select scn_to_timestamp(1447102) from dual;
SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM
I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):
SQL> select scn_to_timestamp(1301571) from dual;
SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM
This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.
The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:
…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
……
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…
Going back in the alert log, I found these lines:
Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”. This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.
The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now. The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either. This has caused the standby to lag behind since that time.
So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert log of the primary instance. The following lines were of interest:
…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…
These lines showed everything clearly. The issue was:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.
Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the FAL did not get that log was finally cleared.
Solution
Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?
One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].
1. [Standby] Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
2. [Standby] Shutdown the standby database
3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:
RMAN> run {
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb';
3> backup incremental from scn 1301571 database;
4> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK
Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
… …
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1
4. [Primary] On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';
Database altered.
5. [Primary] Copy these files to standby host:
oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04
6. [Standby] Bring up the instance in nomount mode:
SQL> startup nomount
7. [Standby] Check the location of the controlfile:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl
8. [Standby] Replace the controlfile with the one you just created in primary.
9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl
10.[Standby] Mount the standby database:
SQL> alter database mount standby database;
11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:
$ rman target=/
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback
List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
12.Recover these files:
RMAN> recover database;
Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...…
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
… …
13. After some time, the recovery fails with the message:
archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'
This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.
14.At this point exit RMAN and start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
15.Check the SCN’s in primary and standby:
[Standby] SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447474
[Primary] SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447478
Now they are very close to each other. The standby has now caught up.
Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how it was resolved. In summary, there are two parts of the problem:
(1) What happened
(2) How to fix it
What Happened
Let’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbers of the primary and standby databases. On the primary:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447102
On the standby:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1301571
Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock. To know that I used the scn_to_timestamp function to translate the SCN to a timestamp:
SQL> select scn_to_timestamp(1447102) from dual;
SCN_TO_TIMESTAMP(1447102)
-------------------------------
18-DEC-09 08.54.28.000000000 AM
I ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran it on the primary database, though; since it will fail in the standby in a mounted mode):
SQL> select scn_to_timestamp(1301571) from dual;
SCN_TO_TIMESTAMP(1301571)
-------------------------------
15-DEC-09 07.19.27.000000000 PM
This shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.
The next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL server should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetch archived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I found these lines that showed the issue clearly:
…
Fri Dec 18 06:12:26 2009
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 1 sequence 700
Fetching gap sequence in thread 1, gap sequence 700-700
…
Fri Dec 18 06:13:27 2009
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 700-700
DBID 846390698 branch 697108460
FAL[client]: All defined FAL servers have been attempted.
…
Going back in the alert log, I found these lines:
Tue Dec 15 17:16:15 2009
Fetching gap sequence in thread 1, gap sequence 700-700
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:15 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Tue Dec 15 17:16:45 2009
Error 12514 received logging on to the standby
FAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequence
Tue Dec 15 17:16:45 2009
Errors in file /opt/oracle/admin/DEL2/bdump/del2_mrp0_18308.trc:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an error while receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currently know of service requested in connect descriptor”. This is usually the case when the TNS connect string is incorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.
The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; so there connect string is fine - now. The standby is receiving log information from the primary. There must have been some temporary hiccups causing that specific archived log not to travel to the standby. If that log was somehow skipped (could be an intermittent problem), then it should have been picked by the FAL process later on; but that never happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on – were applied either. This has caused the standby to lag behind since that time.
So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To get to that, I looked into the alert log of the primary instance. The following lines were of interest:
…
Tue Dec 15 19:19:58 2009
Thread 1 advanced to log sequence 701 (LGWR switch)
Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1_mf_2_5bhbkg92_.log
Tue Dec 15 19:20:29 2009Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Tue Dec 15 19:20:29 2009
FAL[server, ARC1]: FAL archive failed, see trace file.
Tue Dec 15 19:20:29 2009
Errors in file /opt/oracle/product/10gR2/db1/admin/DEL1/bdump/del1_arc1_14469.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed.
Archiver continuing
Tue Dec 15 19:20:29 2009
ORACLE Instance DEL1 - Archival Error. Archiver continuing.
…
These lines showed everything clearly. The issue was:
ORA-00308: cannot open archived log '/u01/oraback/1_700_697108460.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
The archived log simply was not available. The process could not see the file and couldn’t get it across to the standby site.
Upon further investigation I found that the DBA actually removed the archived logs to make some room in the filesystem without realizing that his action has removed the most current one which was yet to be transmitted to the remote site. The mystery surrounding why the FAL did not get that log was finally cleared.
Solution
Now that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available on the primary, I could have easily copied it over to the standby, registered the log file and let the managed recovery process pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile was applied, the recovery will not move forward. So, what are my options?
One option is of course to recreate the standby - possible one but not technically feasible considering the time required. The other option is to apply the incremental backup of primary from that SCN number. That’s the key – the backup must be from a specific SCN number. I have described the process since it is not very obvious. The following shows the step by step approach for resolving this problem. I have shown where the actions must be performed – [Standby] or [Primary].
1. [Standby] Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
2. [Standby] Shutdown the standby database
3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:
RMAN> run {
2> allocate channel c1 type disk format '/u01/oraback/%U.rmb';
3> backup incremental from scn 1301571 database;
4> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=139 devtype=DISK
Starting backup at 18-DEC-09
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oradata/DEL1/datafile/o1_mf_system_5bhbh59c_.dbf
…
piece handle=/u01/oraback/06l16u1q_1_1.rmb tag=TAG20091218T083619 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:06
Finished backup at 18-DEC-09
released channel: c1
4. [Primary] On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as '/u01/oraback/DEL1_standby.ctl';
Database altered.
5. [Primary] Copy these files to standby host:
oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/oraback
oracle@oradba2's password:
06l16u1q_1_1.rmb 100% 43MB 10.7MB/s 00:04
DEL1_standby.ctl 100% 43MB 10.7MB/s 00:04
6. [Standby] Bring up the instance in nomount mode:
SQL> startup nomount
7. [Standby] Check the location of the controlfile:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/oradata/standby_cntfile.ctl
8. [Standby] Replace the controlfile with the one you just created in primary.
9. $ cp /u01/oraback/DEL1_standby.ctl /u01/oradata/standby_cntfile.ctl
10.[Standby] Mount the standby database:
SQL> alter database mount standby database;
11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:
$ rman target=/
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DEL1 (DBID=846390698, not open)
RMAN> catalog start with '/u01/oraback';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/oraback
List of Files Unknown to the Database
=====================================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/oraback/DEL1_standby.ctl
File Name: /u01/oraback/06l16u1q_1_1.rmb
12.Recover these files:
RMAN> recover database;
Starting recover at 18-DEC-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/DEL2/datafile/o1_mf_system_5lptww3f_.dbf
...
channel ORA_DISK_1: reading from backup piece /u01/oraback/05l16u03_1_1.rmb
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oraback/05l16u03_1_1.rmb tag=TAG20091218T083619
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
archive log thread 1 sequence 8012 is already on disk as file /u01/oradata/1_8012_697108460.dbf
archive log thread 1 sequence 8013 is already on disk as file /u01/oradata/1_8013_697108460.dbf
…
13. After some time, the recovery fails with the message:
archive log filename=/u01/oradata/1_8008_697108460.dbf thread=1 sequence=8009
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2009 06:53:02
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/u01/oradata/1_8008_697108460.dbf'
ORA-00310: archived log contains sequence 8008; sequence 8009 required
ORA-00334: archived log: '/u01/oradata/1_8008_697108460.dbf'
This happens because we have come to the last of the archived logs. The expected archived log with sequence# 8008 has not been generated yet.
14.At this point exit RMAN and start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
15.Check the SCN’s in primary and standby:
[Standby] SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447474
[Primary] SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1447478
Now they are very close to each other. The standby has now caught up.
Tuesday, December 08, 2009
Sepaking at New York Oracle Users Group
I had the opportunity to speak at the New York Oracle Users Group (http://nyoug.org/info/metro_area_user_group/Metro_Area_User_Group_Meeting_Sep_2009_Agenda.pdf) today at Hotel New Yorker. I spoke about reucing the risk during the 10g to 11g upgrade using various tools and techniques available to us as a DBA. Here is the slide deck, if you are iterested.
Some of the technologies I have described during the talk have been explained in detail on various articles I have written. Here they are:
Database Replay: http://www.oracle.com/technology/oramag/oracle/08-jan/o18dbasereplay.html
SQL Performance Analyzer (SPA):
http://www.oracle.com/technology/oramag/oracle/08-mar/o28sqlperf.html
SQL Plan Management: http://www.oracle.com/technology/oramag/oracle/09-mar/o29spm.html
As always, I highly appreciate folks coming to me after the talk and provide the feedback. Please keep the feedbacks coming, via emails, phone calls, telepathy, whatever. I really look forward to them.
Some of the technologies I have described during the talk have been explained in detail on various articles I have written. Here they are:
Database Replay: http://www.oracle.com/technology/oramag/oracle/08-jan/o18dbasereplay.html
SQL Performance Analyzer (SPA):
http://www.oracle.com/technology/oramag/oracle/08-mar/o28sqlperf.html
SQL Plan Management: http://www.oracle.com/technology/oramag/oracle/09-mar/o29spm.html
As always, I highly appreciate folks coming to me after the talk and provide the feedback. Please keep the feedbacks coming, via emails, phone calls, telepathy, whatever. I really look forward to them.
Wednesday, November 18, 2009
RAC Perf Tuning Seminar in Istanbul, Turkey
For those who attended my 2-day training event in Istanbul, I wish to express my sincere thanks for the participation. For me, or any speaker, the privilege of having your attention for 2 days away from your work and family, means a lot. Some folks came all the way from Ankara and had to go back to work the following day. I hope you all got to learn something worthwhile your time.
The scripts are located in: http://www.proligence.com/racperf_istanbul/scripts.zip The password and userid is the ones I gave you in the class.
I also want to reiterate my earlier request to send me your detailed and honest feedback at arup@proligence.com. You may want to write it in Turkish, if that is more convenient. The important thing is to provide the feedback; I can always translate using Google.
Thanks, Hande and Madalina from Oracle University for arranging it. Much appreciated. Now I am off to Estonia.
The scripts are located in: http://www.proligence.com/racperf_istanbul/scripts.zip The password and userid is the ones I gave you in the class.
I also want to reiterate my earlier request to send me your detailed and honest feedback at arup@proligence.com. You may want to write it in Turkish, if that is more convenient. The important thing is to provide the feedback; I can always translate using Google.
Thanks, Hande and Madalina from Oracle University for arranging it. Much appreciated. Now I am off to Estonia.
Thursday, October 15, 2009
OOW09 Session#4 DBA 11g New Features
For all those who came to my last of my four sessions - 11g New Features for DBAs - I appreciate your taking the time. It was a pleasant surprise to see about 500 people showing up at a lunch time slot on the last day of the conference.
Here is the presentation link. I hope you enjoyed the session and found it useful.
Here is the presentation link. I hope you enjoyed the session and found it useful.
OOW09 Session# 3
I just finished the third of my four presentations - SQL Plan Management. Considering it was at 9 AM on the last day of the conference, right after the big Aerosmith concert, I was expecting a lot less crowd. But, to my pleasant surprise about 150 brave souls turned up. Thank you all. I hope you found it useful.
Here is the presentation material. While you are there, feel free to browse around. And, of course, I will highly appreciate if you could send me your comments, either here or via email - whatever you are comfortable with.
Here is the presentation material. While you are there, feel free to browse around. And, of course, I will highly appreciate if you could send me your comments, either here or via email - whatever you are comfortable with.
Wednesday, October 14, 2009
OOW09 My Session#2
Today I delivered the second of my four sessions - "Upgrade Case Study: Database Replay, Snapshot Standby and Plan Baselines".
For those you attended, I thank you very much. Here is the presentation.
For those you attended, I thank you very much. Here is the presentation.
Tuesday, October 13, 2009
OOW Day2
Why do you come to Open World? I'm sure we will get all kinds of reasons, as many as there are stars in the sky. Some predominant themes are - getting to know more about the Oracle (or related) technologies by attending sessions, reconnecting with old friends and building networking. Of course, getting freebies from the Exhibit Halls, I'm sure, can't be far behind as a motivator.
I come to OOW for all those reasons as well. But high up in my list is the visit to the Exhibit Halls. No; not for the tee-shirts that do not fit me and graphics I don't really dig. I visit the demogrounds and exhibit halls to know about the products and tools that I should be aware of. Where else would you find 1000+ companies advertising the products at one place? Sure, I can call them and find out; but ho do I find them? OOW exhibit halls are prime "hunting" grounds to look for new ideas and tools that I should be interested in; or at least be aware of. I can not only look at the tools; I can actually get some relevant technical facts in 5 minutes which might take weeks of scheduling and hours of marketing talk. And, if I decide the product is not relevant; I can always walk away. I have the privilege of walking away; they don't. If I call them to my office, "they" have that option; not me :) If I find something attractive, I can always follow up and get to know more.
Oracle demogrounds are even better. Not only I can meet Oracle PMs there; but the people who never come out to the public world - developers, development managers, architects and so on. These unsung heroes are mostly the reason why Oracle is what it is now. I meet the known faces, get to know new ones and establish new relationships. They hear from me what customers want and I learn the innards of some features I am curious about.
So, I spent almost the whole day yesterday navigating through demo grounds and exhibit halls. I could cover only a small fraction. In between I had to attend some meetings at work. Going to OOW is never "going away". I wish it was.
I come to OOW for all those reasons as well. But high up in my list is the visit to the Exhibit Halls. No; not for the tee-shirts that do not fit me and graphics I don't really dig. I visit the demogrounds and exhibit halls to know about the products and tools that I should be aware of. Where else would you find 1000+ companies advertising the products at one place? Sure, I can call them and find out; but ho do I find them? OOW exhibit halls are prime "hunting" grounds to look for new ideas and tools that I should be interested in; or at least be aware of. I can not only look at the tools; I can actually get some relevant technical facts in 5 minutes which might take weeks of scheduling and hours of marketing talk. And, if I decide the product is not relevant; I can always walk away. I have the privilege of walking away; they don't. If I call them to my office, "they" have that option; not me :) If I find something attractive, I can always follow up and get to know more.
Oracle demogrounds are even better. Not only I can meet Oracle PMs there; but the people who never come out to the public world - developers, development managers, architects and so on. These unsung heroes are mostly the reason why Oracle is what it is now. I meet the known faces, get to know new ones and establish new relationships. They hear from me what customers want and I learn the innards of some features I am curious about.
So, I spent almost the whole day yesterday navigating through demo grounds and exhibit halls. I could cover only a small fraction. In between I had to attend some meetings at work. Going to OOW is never "going away". I wish it was.
Sunday, October 11, 2009
OOW09 - RAC Performance Tuning
For all those who came to my session - many, many thanks. There is no better sight for a presenter than to see a roomful of attendees, especially with people standing near the walls. The fire marshal was not amused probably; but I was grateful. The harrowing incident of a blue screen of death on my PC - not just once but twice - just before the presentation was about to start was enough to throw me into a panic mode; but the third time was a charm. It worked. Phew!
You can download the presentation here. And while you are there, look around and download some more of my sessions as well.
Thanks a lot once again. I'm off the keynote now.
You can download the presentation here. And while you are there, look around and download some more of my sessions as well.
Thanks a lot once again. I'm off the keynote now.
ACE Directors Product Briefing '09
One of the most valuable benefits of being an Oracle ACE Director is the briefings by Oracle Product Managers at the Oracle HQ. This year the briefing was on Friday Oct 9th at Oracle conference center rather than the customary Hilton Hotel.
While I was a little disappointed at the coverage of the database topics, I quickly recovered from the alphabet soup that makes up the netherworld of middleware and tools. However, a surprise visit by Thomas Kurian to address questions from the audience about the various product roadmaps was testimonial that Oracle is dead serious about the ACE Program. That proves the commitment Oracle has made for the user community - very heartening.
As always, Vikky Lira and Lillian Buziak did a wonderful job of organizing the event. Considering about 100 ACE Directors from 20+ countries, that is no small task. Perhaps the highlight of the organization was the detailed briefing sheets Lillian prepared for each one individually, down to what car service one takes and when - simply superb! No amount of thanks will be enough. From the bottom of my heart, thank you, Vikky and Lillian. And, thank you Justin Kestelyn - for kicking off and running the event year after year.
While I was a little disappointed at the coverage of the database topics, I quickly recovered from the alphabet soup that makes up the netherworld of middleware and tools. However, a surprise visit by Thomas Kurian to address questions from the audience about the various product roadmaps was testimonial that Oracle is dead serious about the ACE Program. That proves the commitment Oracle has made for the user community - very heartening.
As always, Vikky Lira and Lillian Buziak did a wonderful job of organizing the event. Considering about 100 ACE Directors from 20+ countries, that is no small task. Perhaps the highlight of the organization was the detailed briefing sheets Lillian prepared for each one individually, down to what car service one takes and when - simply superb! No amount of thanks will be enough. From the bottom of my heart, thank you, Vikky and Lillian. And, thank you Justin Kestelyn - for kicking off and running the event year after year.
Open World 09 Starts
Oracle Open World 2009 has officially started with the User Group sessions today. I am presenting a session today. I started off by registering and getting my cool Blogger badge holder, hanging off the even cooler ACE Director lanyard.
I went off to the first session of today on the IOUG bucket - Workload Management by Alex Gorbachev. Alex is one of those people who know their stuff; so there is always something to be learned from there. Alex successfully demonstrated the difference between Connection Load Balancing and Server Side Listener Load Balancing, with pmon trace to show how the sessions are balanced. It sheds light on the question - why Oracle is not balancing the workload.
If you didn't attend this, you should definitely download the presentation and check it out later.
I went off to the first session of today on the IOUG bucket - Workload Management by Alex Gorbachev. Alex is one of those people who know their stuff; so there is always something to be learned from there. Alex successfully demonstrated the difference between Connection Load Balancing and Server Side Listener Load Balancing, with pmon trace to show how the sessions are balanced. It sheds light on the question - why Oracle is not balancing the workload.
If you didn't attend this, you should definitely download the presentation and check it out later.
Thursday, September 03, 2009
ASM Dynamic Volume Manager and ASM Clustered File System
Two of the top features in 11gR2 are the ASM Dynamic Volume Manager (ADVM) and ASM Clustered File System (ACFS). What is the big deal about these two?
ADVM allows you to create a volume from an ASM diskgroup. Here is an example where we created a volume called asm_vol1 of 100 MB on a diskgroup called DATA:
Internally it issues the command
Now you enable the volume you just created:
Internally it issues:
You can perform other commands like resize, delete, disable; but more on that later on a full length article.
Now that the volume is created, what can you do with it. Well, like all volumes, you can create a filesystem on it. Here is an example of creating a FS called acfs1:
Register MountPoint Command:
If you get an error, use the force option:
Now you mount the the filesystem:
Now if you will check the filesystem, you will notice a new one - /acfs1
This new filesystem is actually carved out of the ASM diskspace. This can be used as a regular filesystem:
So, what't the big deal about it? Plenty.
First, this is part of the ASM management; so all the bells and whistles of ASM - such as asynch i/o, etc. - applies to this filesystem.
Second, this is now a "cluster" filesystem; it is visible across a cluster. So, now you have a fully functional generic filesystem visible across the cluster.
Third, this is now protected by the Grid infrastructure, if you have installed it. Remember from my earlier posts that in 11gR2 you can now have a restartable grid infrastructure even on a single instance.
More on ASM Dynamic Volume Manager later in a full length article. But I hope this makes you really interested.
ADVM allows you to create a volume from an ASM diskgroup. Here is an example where we created a volume called asm_vol1 of 100 MB on a diskgroup called DATA:
ASMCMD [+] > volcreate -G DATA -s 100M asm_vol1
Internally it issues the command
alter diskgroup DATA add volume 'asm_vol1' size 100M;
Now you enable the volume you just created:
ASMCMD [+] > volenable -G DATA asm_vol1
Internally it issues:
alter diskgroup DATA enable volume 'asm_vol1';
You can perform other commands like resize, delete, disable; but more on that later on a full length article.
Now that the volume is created, what can you do with it. Well, like all volumes, you can create a filesystem on it. Here is an example of creating a FS called acfs1:
[root@oradba2 ~]# mkdir /acfs1
[root@oradba2 ~]# /sbin/mkfs -t acfs /dev/asm/asm_vol1-207
mkfs.acfs: version = 11.2.0.1.0.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/asm_vol1-207
mkfs.acfs: volume size = 268435456
Register MountPoint Command:
[root@oradba2 ~]# /sbin/acfsutil registry -a -f /dev/asm/asm_vol1-207 /acfs1
acfsutil registry: mount point /acfs1 successfully added to Oracle Registry
If you get an error, use the force option:
[root@oradba2 /]# /sbin/mkfs.acfs -f /dev/asm/asm_vol1-207
mkfs.acfs: version = 11.2.0.1.0.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/asm_vol1-207
mkfs.acfs: volume size = 268435456
mkfs.acfs: Format complete.
Now you mount the the filesystem:
[root@oradba2 /]# /bin/mount -t acfs /dev/asm/asm_vol1-207 /acfs1
Now if you will check the filesystem, you will notice a new one - /acfs1
[root@oradba2 /]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
149313708 22429752 119176924 16% /
/dev/sda1 101086 11765 84102 13% /boot
tmpfs 907628 591640 315988 66% /dev/shm
/dev/asm/asm_vol1-207
262144 37632 224512 15% /acfs1
This new filesystem is actually carved out of the ASM diskspace. This can be used as a regular filesystem:
[root@oradba2 /]# cd /acfs1
[root@oradba2 acfs1]# ls
lost+found
[root@oradba2 acfs1]# touch 1
So, what't the big deal about it? Plenty.
First, this is part of the ASM management; so all the bells and whistles of ASM - such as asynch i/o, etc. - applies to this filesystem.
Second, this is now a "cluster" filesystem; it is visible across a cluster. So, now you have a fully functional generic filesystem visible across the cluster.
Third, this is now protected by the Grid infrastructure, if you have installed it. Remember from my earlier posts that in 11gR2 you can now have a restartable grid infrastructure even on a single instance.
More on ASM Dynamic Volume Manager later in a full length article. But I hope this makes you really interested.
Wednesday, September 02, 2009
Oracle 11g R2 Features
Continuing on the previous posts, here is another gee-whiz feature of 11gR2 - the "deinstall" feature. Yes, that's right the deinstall one. Sometimes installations fail; sometimes you have to deinstall something to clean out the server for other use. Sometimes, I did, you have to clean out beta code to install production code. A deinstall utility stops all the processes, removes all the relevant software and components (such as diskgroups), updates all config files and make all necessary modifications to the other files. All these are done without you ever bothering about remnants that may cause issues later.
You have to download the deinstall software from 11gR2 download from OTN. Choose "see all" to get to that software.
Here is the demonstration of the deinstall utility:
After some initial question and answer it shows a summary of activities and prompts you for a confirmation:
After you press "y", it starts the operation of a clean deinstallation. The output continues as shown below:
At some point you will be asked to shutdown cssd, etc. which need root privileges. The deinstall utility shows a comamnd string you can run as root to accomplish this task:
Running the command on a different terminal as root:
Now going back to the original terminal where deinstall was called from, press Enter. The output continues:
The components are cleanly deinstalled now. The directories have been cleaned up by this tool.
This was available in 11gR1 as well; but R2 just makes it very user friendly.
More on this later.
You have to download the deinstall software from 11gR2 download from OTN. Choose "see all" to get to that software.
Here is the demonstration of the deinstall utility:
[oracle@oradba2 deinstall]$ ./deinstall -home /opt/oracle/product/11.2/grid1
ORACLE_HOME = /opt/oracle/product/11.2/grid1
Location of logs /opt/oracle/oraInventory/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################## CHECK OPERATION START ########################
Install check configuration START
Checking for existence of the Oracle home location /opt/oracle/product/11.2/grid
1
Oracle Home type selected for de-install is: SIHA
Oracle Base selected for de-install is: /opt/oracle
Checking for existence of central inventory location /opt/oracle/oraInventory
Checking for existence of the Oracle Grid Infrastructure home /opt/oracle/produc
t/11.2/grid1
Install check configuration END
Traces log file: /opt/oracle/oraInventory/logs//crsdc.log
Network Configuration check config START
Network de-configuration trace file location: /opt/oracle/oraInventory/logs/netd
c_check22387.log
Specify all Oracle Restart enabled listeners that are to be de-configured [LISTE
NER]:
Network Configuration check config END
Asm Check Configuration START
ASM de-configuration trace file location: /opt/oracle/oraInventory/logs/asmcadc_
check22388.log
Automatic Storage Management (ASM) instance is detected in this Oracle home /opt/oracle/product/
11.2/grid1.
ASM Diagnostic Destination : /opt/oracle
ASM Diskgroups : +DATA1,+FRA1
Diskgroups will be dropped
De-configuring ASM will drop all the diskgroups and it's contents at cleanup time. This will aff
ect all of the databases and ACFS that use this ASM instance(s).
After some initial question and answer it shows a summary of activities and prompts you for a confirmation:
####################### CHECK OPERATION SUMMARY #######################
Oracle Grid Infrastructure Home is: /opt/oracle/product/11.2/grid1
The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated by ",", eg: node1,node2,...)null
Oracle Home selected for de-install is: /opt/oracle/product/11.2/grid1
Inventory Location where the Oracle home registered is: /opt/oracle/oraInventory
Following Oracle Restart enabled listener(s) will be de-configured: LISTENER
ASM instance will be de-configured from this Oracle home
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/opt/oracle/oraInventory/logs/deinstall_deconfig2009-09-02_02-12-22-PM.out'
Any error messages from this session will be written to: '/opt/oracle/oraInventory/logs/deinstall_deconfig2009-09-02_02-12-22-PM.err'
After you press "y", it starts the operation of a clean deinstallation. The output continues as shown below:
######################## CLEAN OPERATION START ########################
ASM de-configuration trace file location: /opt/oracle/oraInventory/logs/asmcadc_clean22389.log
ASM Clean Configuration START
ASM deletion in progress. This operation may take few minutes.
ASM Clean Configuration END
Network Configuration clean config START
Network de-configuration trace file location: /opt/oracle/oraInventory/logs/netdc_clean22390.log
De-configuring Oracle Restart enabled listener(s): LISTENER
De-configuring listener: LISTENER
Stopping listener: LISTENER
Listener stopped successfully.
Unregistering listener: LISTENER
Listener unregistered successfully.
Deleting listener: LISTENER
Listener deleted successfully.
Listener de-configured successfully.
De-configuring Listener configuration file...
Listener configuration file de-configured successfully.
De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
---------------------------------------->
At some point you will be asked to shutdown cssd, etc. which need root privileges. The deinstall utility shows a comamnd string you can run as root to accomplish this task:
Run the following command as the root user or the administrator on node "oradba2".
/opt/oracle/software/11gR2/deinstall/perl/bin/perl -I/opt/oracle/software/11gR2/deinstall/perl/lib -I/opt/oracle/software/11gR2/deinstall/crs/install /opt/oracle/software/11gR2/deinstall/crs/install/roothas.pl -force -delete -paramfile /opt/oracle/software/11gR2/deinstall/response/deinstall_Ora11g_gridinfrahome1.rsp
Press Enter after you finish running the above commands
Running the command on a different terminal as root:
[root@oradba2 ~]# /opt/oracle/software/11gR2/deinstall/perl/bin/perl -I/opt/oracle/software/11gR2/deinstall/perl/lib -I/opt/oracle/software/11gR2/deinstall/crs/install /opt/oracle/software/11gR2/deinstall/crs/install/roothas.pl -force -delete -paramfile /opt/oracle/software/11gR2/deinstall/response/deinstall_Ora11g_gridinfrahome1.rsp
2009-09-02 14:20:57: Checking for super user privileges
2009-09-02 14:20:57: User has super user privileges
2009-09-02 14:20:57: Parsing the host name
Using configuration parameter file: /opt/oracle/software/11gR2/deinstall/response/deinstall_Ora11g_gridinfrahome1.rsp
CRS-2673: Attempting to stop 'ora.cssd' on 'oradba2'
CRS-2677: Stop of 'ora.cssd' on 'oradba2' succeeded
CRS-4549: Stopping resources.
CRS-2673: Attempting to stop 'ora.diskmon' on 'oradba2'
CRS-2677: Stop of 'ora.diskmon' on 'oradba2' succeeded
CRS-4133: Oracle High Availability Services has been stopped.
ACFS-9200: Supported
Successfully deconfigured Oracle Restart stack
Now going back to the original terminal where deinstall was called from, press Enter. The output continues:
Oracle Universal Installer clean START
Detach Oracle home '/opt/oracle/product/11.2/grid1' from the central inventory on the local node : Done
Delete directory '/opt/oracle/product/11.2/grid1' on the local node : Done
The Oracle Base directory '/opt/oracle' will not be removed on local node. The directory is in use by Oracle Home '/opt/oracle/product/10.2/db1'.
The Oracle Base directory '/opt/oracle' will not be removed on local node. The directory is in use by central inventory.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
Oracle install clean START
Clean install operation removing temporary directory '/tmp/install' on node 'oradba2'
Oracle install clean END
Moved default properties file /opt/oracle/software/11gR2/deinstall/response/deinstall_Ora11g_gridinfrahome1.rsp as /opt/oracle/software/11gR2/deinstall/response/deinstall_Ora11g_gridinfrahome1.rsp1
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
ASM instance was de-configured successfully from the Oracle home
Following Oracle Restart enabled listener(s) were de-configured successfully: LISTENER
Oracle Restart was already stopped and de-configured on node "oradba2"
Oracle Restart is stopped and de-configured successfully.
Successfully detached Oracle home '/opt/oracle/product/11.2/grid1' from the central inventory on the local node.
Successfully deleted directory '/opt/oracle/product/11.2/grid1' on the local node.
Oracle Universal Installer cleanup was successful.
Oracle install successfully cleaned up the temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############
The components are cleanly deinstalled now. The directories have been cleaned up by this tool.
This was available in 11gR1 as well; but R2 just makes it very user friendly.
Tuesday, September 01, 2009
Oracle 11g Release 2 is Finally Out
Finally, it's that time again - the birth of a new versionof Oracle - 11g Release 2. Being Release 2, it does not have as much bells and whistles as the 11g.
I downloaded it immediately and started installation. Some of the gee-whiz features of this release are:
(1) Editions
(2) ASM Filesystem
(3) Oracle Restart
(5) Columnar Compression
I have been beta testing this for some time; so I had seen previews of the release. Continuing the previous serieses, I will write the new features series for 11gR2 on OTN as well - it will be a 11 part series.
A little bit about Oracle Restart. It adds a lightweight clusterware functionality to a single instance database. If the instance crashes, OR brings it up, monitors it ans so on. And by the way, this is called "Grid Infrastructure". So you have to install two Oracle Homes - one each for grid and the rdbms.
When there is Grid, there is srvctl, of course. The grid infrastructure comes with srvctl. Here is how you check what is running from a specific Oracle Home:
oracle@oradba1 ~# srvctl status home -o /opt/oracle/product/11gR2/db1 -s state.txt
Database d112d1 is running on node oradba1
The above command create a file called state.txt.
oracle@oradba1 ~# cat state.txt
db-d112d1
It shows the database name - D112D1.
This is done on a single instance Oracle database; not a cluster. But the grid infrastructure looks and feels like a cluster. Here are some more commands to check status:
oracle@oradba1 ~# srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): oradba1
oracle@oradba1 ~# srvctl status asm -a
ASM is running on oradba1
ASM is enabled.
A bunch of new processes suppor this grid infrastructure:
Let's see what happens when you kill the instance.
This will, of course, crash the instance. Let's chck after some time:
Where did the pmon come from? Didn't the instance just crash?
The instance was restarted by Oracle Restart.
What if you want to just keep the instance down, e.g. during a maintenance. Well, just shutdown normally; the instance will stay down. When you are ready, start the instance using either SQL*Plus or srvctl:
oracle@oradba1 ~# srvctl start database -d d112d1
Remember, D112D1 is a single instance database.
More on this later, on OTN.
I downloaded it immediately and started installation. Some of the gee-whiz features of this release are:
(1) Editions
(2) ASM Filesystem
(3) Oracle Restart
(5) Columnar Compression
I have been beta testing this for some time; so I had seen previews of the release. Continuing the previous serieses, I will write the new features series for 11gR2 on OTN as well - it will be a 11 part series.
A little bit about Oracle Restart. It adds a lightweight clusterware functionality to a single instance database. If the instance crashes, OR brings it up, monitors it ans so on. And by the way, this is called "Grid Infrastructure". So you have to install two Oracle Homes - one each for grid and the rdbms.
When there is Grid, there is srvctl, of course. The grid infrastructure comes with srvctl. Here is how you check what is running from a specific Oracle Home:
oracle@oradba1 ~# srvctl status home -o /opt/oracle/product/11gR2/db1 -s state.txt
Database d112d1 is running on node oradba1
The above command create a file called state.txt.
oracle@oradba1 ~# cat state.txt
db-d112d1
It shows the database name - D112D1.
This is done on a single instance Oracle database; not a cluster. But the grid infrastructure looks and feels like a cluster. Here are some more commands to check status:
oracle@oradba1 ~# srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): oradba1
oracle@oradba1 ~# srvctl status asm -a
ASM is running on oradba1
ASM is enabled.
A bunch of new processes suppor this grid infrastructure:
oracle 19046 1 0 18:13 ? 00:00:03 /opt/oracle/product/11gR2/grid1/bin/ohasd.bin reboot
oracle 19487 1 0 18:15 ? 00:01:14 /opt/oracle/product/11gR2/grid1/bin/oraagent.bin
oracle 19502 1 0 18:15 ? 00:00:01 /opt/oracle/product/11gR2/grid1/bin/tnslsnr LISTENER -inherit
oracle 19656 1 0 18:15 ? 00:00:01 /opt/oracle/product/11gR2/grid1/bin/cssdagent
oracle 19658 1 0 18:15 ? 00:00:02 /opt/oracle/product/11gR2/grid1/bin/orarootagent.bin
oracle 19674 1 0 18:15 ? 00:00:01 /opt/oracle/product/11gR2/grid1/bin/ocssd.bin
oracle 19687 1 0 18:15 ? 00:00:00 /opt/oracle/product/11gR2/grid1/bin/diskmon.bin -d -f
Let's see what happens when you kill the instance.
oracle@oradba1 ~# ps -aefgrep pmon
oracle 14225 13768 0 23:15 pts/7 00:00:00 grep pmon
oracle 19866 1 0 18:16 ? 00:00:00 asm_pmon_+ASM
oracle 26965 1 0 20:53 ? 00:00:00 ora_pmon_D112D1
oracle@oradba1 ~# kill -9 26965
This will, of course, crash the instance. Let's chck after some time:
oracle@oradba1 ~# ps -aef|grep pmon
oracle 14315 1 0 23:15 ? 00:00:00 ora_pmon_D112D1
oracle 14686 11492 0 23:17 pts/2 00:00:00 grep pmon
oracle 19866 1 0 18:16 ? 00:00:00 asm_pmon_+ASM
Where did the pmon come from? Didn't the instance just crash?
The instance was restarted by Oracle Restart.
What if you want to just keep the instance down, e.g. during a maintenance. Well, just shutdown normally; the instance will stay down. When you are ready, start the instance using either SQL*Plus or srvctl:
oracle@oradba1 ~# srvctl start database -d d112d1
Remember, D112D1 is a single instance database.
More on this later, on OTN.
Monday, May 11, 2009
Collaborate 09 Ends
I wrapped up a hectic three days at Collaborate this year. As if to echo the sentiments on the economy in general, and Oracle database technology related in particular, the attendance was way down this year. The vast expanse of Orange County Convention Center didn't make it easy either; it made it appear even smaller! Well, it was Orlando; the weather and traffic cooperated and I'm pretty sure some attendees picked up a few well deserved resting points either alone or with visiting family.
I presented three sessions:
(1) RAC Performance Tuning
(2) Real World Best Practices for DBAs (with Webcast)
(3) All About Deadlocks
I also participated in the RAC Experts' Panel as a Panelist.
As always, I used to opportunity to meet old friends and acquintances. Collaborate is all about knowledge sharing; I didn't lose when it came to getting some myself. I attended some highly useful sessions:
(1) Database Capacity Planning by Ashish Rege
(2) Partitioning for DW by Vincent
(3) Hints on Hints by Jonathan Lewis
(4) 11g Performance Tuning by Rich Niemic
(5) HA Directions from Oracle by Ashish Ray
(6) Storage Performance Diagnosis by Gaja
(7) Two sessions - on Database Replay and Breaking Oracle by Jeremiah Wilton
and some more. It has been a worthy conference.
I presented three sessions:
(1) RAC Performance Tuning
(2) Real World Best Practices for DBAs (with Webcast)
(3) All About Deadlocks
I also participated in the RAC Experts' Panel as a Panelist.
As always, I used to opportunity to meet old friends and acquintances. Collaborate is all about knowledge sharing; I didn't lose when it came to getting some myself. I attended some highly useful sessions:
(1) Database Capacity Planning by Ashish Rege
(2) Partitioning for DW by Vincent
(3) Hints on Hints by Jonathan Lewis
(4) 11g Performance Tuning by Rich Niemic
(5) HA Directions from Oracle by Ashish Ray
(6) Storage Performance Diagnosis by Gaja
(7) Two sessions - on Database Replay and Breaking Oracle by Jeremiah Wilton
and some more. It has been a worthy conference.
Friday, March 13, 2009
Excellent Information on Exadata
If you are interested to know more about the technology (not the marketing hype) behind Exadata - the datawarehouse appliance from Oracle, I just discovered a treasure trove of information on this blog:
http://www.pythian.com/news/1267/interview-kevin-closson-on-the-oracle-exadata-storage-server#comment-350433
Kevin Closson, one the lead architects of the Exadata machine has been interviewed by Christo Kutovsky and Paul Paul Vallée of Phythian. Kevin, in his usual detailed style explains the innards of the beast, aided by the excellent leading questioning by Christo.
I believe this is best writing I have seen on Exadata - all good stuff, no fluff. Thank you, Kevin.
http://www.pythian.com/news/1267/interview-kevin-closson-on-the-oracle-exadata-storage-server#comment-350433
Kevin Closson, one the lead architects of the Exadata machine has been interviewed by Christo Kutovsky and Paul Paul Vallée of Phythian. Kevin, in his usual detailed style explains the innards of the beast, aided by the excellent leading questioning by Christo.
I believe this is best writing I have seen on Exadata - all good stuff, no fluff. Thank you, Kevin.
Subscribe to:
Posts (Atom)