Skip navigation.

Hemant K Chitale

Syndicate content
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 6 hours 42 min ago

RAC Commands : 2 -- Updating Configuration for Services

Tue, 2014-07-15 08:30
NOTE : This is in a Policy Managed configuration

Adding a database service

[root@node1 ~]# su - oracle
-sh-3.2$ srvctl add service -d RACDB -s NEW_SVC -g RACSP -c SINGLETON
-sh-3.2$ srvctl config service -d RACDB -s NEW_SVC
Service name: NEW_SVC
Service is enabled
Server pool: RACSP
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:
-sh-3.2$
-sh-3.2$ srvctl start service -d RACDB -s NEW_SVC
-sh-3.2$

Since this is Policy Managed database in the RACSP Server Pool, I added a service with the appropriate parameters. The SINGLETON cardinality means that it will run on only one instance.  (See the previous post for the service MY_RAC_SVC with the cardinliaty UNIFORM).

Let's verify the alert.log entry.
-sh-3.2$ cd /u01/app/oracle/diag/rdbms/racdb/RACDB_1
-sh-3.2$ cd trace
-sh-3.2$ tail alert_RACDB_1.log
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Jul 15 22:01:20 2014
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Jul 15 22:06:45 2014
db_recovery_file_dest_size of 4000 MB is 22.25% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jul 15 22:11:10 2014
ALTER SYSTEM SET service_names='MY_RAC_SVC','NEW_SVC' SCOPE=MEMORY SID='RACDB_1';
-sh-3.2$

The SCOPE specification of the ALTER SYSTEM limits the service to only this instance. (Note : MY_RAC_SVC had already been added to RACDB_2 earlier).


Removing a database service

Now, let's remove a database service

-sh-3.2$ srvctl config service -d RACDB -s MY_RAC_SVC
Service name: MY_RAC_SVC
Service is enabled
Server pool: RACSP
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:
-sh-3.2$ srvctl remove service -d RACDB -s MY_RAC_SVC
PRCR-1025 : Resource ora.racdb.my_rac_svc.svc is still running

I need to first stop the service.

-sh-3.2$ srvctl stop service -d RACDB -s MY_RAC_SVC
-sh-3.2$
-sh-3.2$ srvctl config service -d RACDB -s MY_RAC_SVC
Service name: MY_RAC_SVC
Service is enabled
Server pool: RACSP
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:
-sh-3.2$ tail alert_RACDB_1.log
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Jul 15 22:06:45 2014
db_recovery_file_dest_size of 4000 MB is 22.25% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jul 15 22:11:10 2014
ALTER SYSTEM SET service_names='MY_RAC_SVC','NEW_SVC' SCOPE=MEMORY SID='RACDB_1';
Tue Jul 15 22:17:48 2014
ALTER SYSTEM SET service_names='NEW_SVC' SCOPE=MEMORY SID='RACDB_1';
-sh-3.2$

I can now remove the service.

-sh-3.2$ srvctl remove service -d RACDB -s MY_RAC_SVC
-sh-3.2$ srvctl config service -d RACDB
Service name: NEW_SVC
Service is enabled
Server pool: RACSP
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:
-sh-3.2$

Now, only the new service is listed.

Modifying the cardinality of a service

-sh-3.2$ srvctl modify service -d RACDB -s NEW_SVC -c UNIFORM
-sh-3.2$ srvctl config service -d RACDB -s NEW_SVC
Service name: NEW_SVC
Service is enabled
Server pool: RACSP
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:
-sh-3.2$
-sh-3.2$ srvctl start service -d RACDB -s NEW_SVC

The service has been modified from SINGLETON (single instance) to UNIFORM (all instances).
Verifying this on node 2.

-sh-3.2$ pwd
/u01/app/oracle/diag/rdbms/racdb/RACDB_2/trace
-sh-3.2$ tail -2 alert_RACDB_2.log
Tue Jul 15 22:27:36 2014
ALTER SYSTEM SET service_names='NEW_SVC' SCOPE=MEMORY SID='RACDB_2';
-sh-3.2$

The service has been enabled on RACDB_2 as well now.

.
.
.

Categories: DBA Blogs

RAC Commands : 1 -- Viewing Configuration

Sun, 2014-07-13 05:58
Viewing the configuration of a RAC database

[root@node1 ~]# su - oracle
-sh-3.2$ srvctl config database -d RACDB
Database unique name: RACDB
Database name: RACDB
Oracle home: /u01/app/oracle/rdbms/11.2.0
Oracle user: oracle
Spfile: +DATA1/RACDB/spfileRACDB.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACSP
Database instances:
Disk Groups: DATA1,FRA,DATA2
Mount point paths:
Services: MY_RAC_SVC
Type: RAC
Database is policy managed
-sh-3.2$

So, we see that :
a) The database name is RACDB
b) It is a Policy Managed database (not Administrator Managed)
c) It is dependent on 3 ASM Disk Groups DATA1, DATA2, FRA
d) There is one service called MY_RAC_SVC configured
e) The database is in the  RACSP server pool
f) The database is configured to be Auto-started when Grid Infrastructure starts


Viewing the configuration of a RAC service

-sh-3.2$ srvctl config service -d RACDB -s MY_RAC_SVC
Service name: MY_RAC_SVC
Service is enabled
Server pool: RACSP
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Service is enabled on nodes:
Service is disabled on nodes:
-sh-3.2$

So, we see that :
a) The service name is MY_RAC_SVC
b) The UNIFORM cardinality means that it is to run on all active nodes in the server pool
c) The server-side connection load balancing goal is LONG (for long running sessions)


Viewing the configuration of Server Pools

-sh-3.2$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Candidate server names:
Server pool name: RACSP
Importance: 0, Min: 0, Max: 2
Candidate server names:
-sh-3.2$

So we see that :
a) The RACSP server pool is the only created (named) server pool
b) This server pool has a max of 2 nodes

Categories: DBA Blogs

Installing OEL 6 and Database 12c

Thu, 2014-07-10 08:25
Here is a collection of posts on installing (a) Virtual Box (b) Oracle Enterprise Linux 6 (c) 12c Grid Infrastructure (Standalone, non-Clustered) and ASM (d) 12c Database with CDB and PDB.
.
.
.

Categories: DBA Blogs

Passed the 11g RAC and Grid Expert Exam

Tue, 2014-07-08 09:08
I passed the 11g RAC and Grid Expert Exam yesterday.
.
For those who are interested :

You must absolutely read the documentation on ASM, Grid Infrastructure and RAC. 

I also recommend 3 books 
1) Pro Oracle Database 11g RAC on Linux -- by Steve Shaw and Martin Bach [Apress Publishing]
2) Oracle 11g R1/R2 Real Application Clusters Essentials -- by Ben Prusinsky and Syed Jaffer Hussain [Packt Publishing] 
OR 
2) Oracle 11g R1/R2 Real Application Clusters Handbook -- by Ben Prusinsky, Guenad Jilveski and Syed Jaffer Husssain [Packt Publishing] 
3) Oracle Database 11g Release 2 High Availability -- by Scott Jesse, Bill Burton and Bryan Vongray [Oracle Press] 

The 11gR2 Grid and RAC Accelerated training at Oracle University is also recommended but expensive.
.
.
.
Categories: DBA Blogs

Gather Statistics Enhancements in 12c

Fri, 2014-06-13 01:32
Here are 5 posts that I did on Gather Statistics Enhancements in 12c :

1.  During a CTAS

2.  In a Direct Path INSERT

3.   Reports on Statistics

4.  Does not COMMIT a GTT

5.  Report on COL_USAGE
.
.
.


Categories: DBA Blogs

Getting your Transaction ID

Fri, 2014-06-13 00:42
You can get the Transaction ID for a session by joining V$SESSION.TADDR to V$TRANSACTION.ADDR.

A Transaction ID consists of the Undo Segment #, the Slot # and the SEQ #.

For example :
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
6.3.9463

SQL>

Thus, my current transaction is in Undo Segment 6, Slot 3, Sequence 9463.
SQL>    select count(*) from v$transaction;

COUNT(*)
----------
1

SQL> col username format a12
SQL> l
1 select s.username, s.sid, s.serial#,
2 t.xidusn, t.xidslot, t.xidsqn
3 from v$session s, v$transaction t
4* where s.taddr=t.addr
SQL> /

USERNAME SID SERIAL# XIDUSN XIDSLOT XIDSQN
------------ ---------- ---------- ---------- ---------- ----------
HEMANT 38 23 6 3 9463

SQL>

As soon as I commit or rollback or issue a DDL or a CONNECT, the transaction ends.
SQL> rollback;

Rollback complete.

SQL> select count(*) from v$transaction;

COUNT(*)
----------
0

SQL>

Remember that an Undo segment can hold multiple transactions. That is why the Undo Segment Header has multiple "slots", one for each active transaction.  Once a transaction completes (and likely after the undo_retention period), a slot can be reused with an incremented seq#.
.
.
.
Categories: DBA Blogs

Guenadi Jilevski's posts on building RAC Clusters on VM Virtual Box

Mon, 2014-06-02 02:44
Guenadi Jilevski has a few posts on building Oracle RAC Clusters on VM Virtual Box

1.  11gR2 RAC co-existing with 10gR2

2.  11gR2 RAC using GNS

3.  12c RAC

Note : Unfortunately, I haven't had the time and resources to build and test clusters using these instructions.

.
.
.


Categories: DBA Blogs

Oracle Diagnostics Presentations

Mon, 2014-05-26 09:33
I've uploaded my Oracle Diagnostics Presentations from the years 2010-2011 into slideshare.

I hope that they are useful.

.
.
.
Categories: DBA Blogs

Partitions and Segments and Data Objects

Mon, 2014-05-19 07:47
Have you ever wondered about how Partitions are mapped to Segments ?  How  does Oracle identify the "data object" (as different from the logical object) that maps to the Segment for an Object ?

[Why does Oracle differentiate between "object_id" and "data_object_id" ?  An object may be created without a Segment.  An existing Segment for an object (e.g. a Table) may be recreated (e.g. by an ALTER TABLE tablename MOVE command) thus changing it's "data_object_id" without changing its "object_id")].

For a Partitioned Table, every Partition is an Object.  But (beginning with 11.2.0.2 and "deferred_segment_creation" behaviour), the Segment is created only when the Partition is populated with one or more rows.  What happens when a Partition is SPLIT ?

Here is a simple demonstration with some notes :


SQL> drop table test_partitioned_table purge;

Table dropped.

SQL>
SQL> -- create test table with 5+1 partitions
SQL> create table test_partitioned_table
2 (
3 id_column number,
4 data_column varchar2(15)
5 )
6 partition by range (id_column)
7 (
8 partition p_100 values less than (101),
9 partition p_200 values less than (201),
10 partition p_300 values less than (301),
11 partition p_400 values less than (401),
12 partition p_500 values less than (501),
13 partition p_max values less than (MAXVALUE)
14 )
15 /

Table created.

SQL>
SQL> -- populate the first 4 partitions
SQL> insert into test_partitioned_table
2 select rownum, to_char(rownum)
3 from dual
4 connect by level < 379
5 /

378 rows created.

SQL>
SQL> -- identify the segments that did get created
SQL> -- note : Use DBA_SEGMENTS as HEADER_% information is not available in USER_SEGMENTS
SQL> select partition_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6 order by partition_name
7 /

PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P_100 11 34449
P_200 11 35473
P_300 11 36497
P_400 11 38417

SQL>
SQL> -- identify the objects
SQL> -- use the DBA_OBJECTS view for consistency with previous query on DBA_SEGMENTS
SQL> select subobject_name, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name = 'TEST_PARTITIONED_TABLE'
5 and object_type = 'TABLE PARTITION'
6 order by subobject_name
7 /

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
P_100 114541 114541
P_200 114542 114542
P_300 114543 114543
P_400 114544 114544
P_500 114545 114545
P_MAX 114546 114546

6 rows selected.

SQL>


Notice how, although there are 6 Partitions and 6 Objects, there are only 4 Segments. Only the first 4 Partitions that have rows in them now have Segments associated with them.  Pay attention to the (HEADER_FILE, HEADER_BLOCK) and DATA_OJECT_ID values as I proceed to manipulate the Partitions.


SQL> -- split the first partition
SQL> alter table test_partitioned_table
2 split partition p_100
3 at (51)
4 into (partition p_50, partition p_100)
5 /

Table altered.

SQL>
SQL> -- identify the segments
SQL> select partition_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6 and partition_name in ('P_50','P_100')
7 order by partition_name
8 /

PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P_100 11 40465
P_50 11 39441

SQL>
SQL> -- identify the objects
SQL> select subobject_name, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name = 'TEST_PARTITIONED_TABLE'
5 and object_type = 'TABLE PARTITION'
6 and subobject_name in ('P_50','P_100')
7 order by subobject_name
8 /

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
P_100 114541 114548
P_50 114547 114547

SQL>


Notice how Partition P_50, with a new OBJECT_ID and DATA_OBJECT_ID (above the highest then-existent value). But did you notice that the (HEADER_FILE, HEADER_BLOCK) pair and the DATA_OBJECT_ID for Partition P_100 are completely new values ? Oracle has created a *new* Segment for Partition P_100 and discarded the old segment. The SPLIT operation has created two *new* Segments and removed the old Segment for Partition P_100. What does this also mean ?  Oracle had to actually rewrite all 100 rows in that Partition in the process of creating two new Segments.  Let me say that again : Oracle had to rewrite all 100 rows.

Let me proceed with another test.


SQL> -- insert one row into the 5th partition
SQL> insert into test_partitioned_table
2 select 450, to_char(450) from dual
3 /

1 row created.

SQL>
SQL> -- identify the segment
SQL> select partition_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6 and partition_name = 'P_500'
7 order by partition_name
8 /

PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P_500 11 34449

SQL>
SQL> -- identify the object
SQL> select subobject_name, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name = 'TEST_PARTITIONED_TABLE'
5 and object_type = 'TABLE PARTITION'
6 and subobject_name = 'P_500'
7 order by subobject_name
8 /

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
P_500 114545 114545

SQL>


Inserting a row into Partition P_500 has created a Segment (which did not exist earlier).  [Coincidentally, it has actually "reused" the one extent that earlier belonged to Partition P_100 -- look at the (HEADER_FILE, HEADER_BLOCK) pair --  and had become a free extent  for a while.  I say "coincidentally" because if there had been some other extent allocation for this Table or any other Table or Index in the same tablespace, that free extent could have been reused by another Partition / Table / Index].  The DATA_OBJECT_ID had already been allocated to the Partition when the Table was created, so this doesn't change.
I have deliberately inserted  a "boundary" value of 450 in this Partition.  This will be the maxvalue for Partition P_450.  I will now proceed to split the Partition along this boundary.


SQL> -- split the 5th partition
SQL> -- now p_450 will have the 1 row and p_500 no rows
SQL> alter table test_partitioned_table
2 split partition p_500
3 at (451)
4 into (partition p_450, partition p_500)
5 /

Table altered.

SQL>
SQL> -- identify the segments
SQL> select partition_name, header_file, header_block
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6 and partition_name in ('P_450','P_500')
7 order by partition_name
8 /

PARTITION_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
P_450 11 34449
P_500 11 41489

SQL>
SQL> -- identify the objects
SQL> select subobject_name, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name = 'TEST_PARTITIONED_TABLE'
5 and object_type = 'TABLE PARTITION'
6 and subobject_name in ('P_450','P_500')
7 order by subobject_name
8 /

SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
P_450 114549 114545
P_500 114545 114550

SQL>


Now, isn't that interesting ? Partition P_450 has "inherited" the (HEADER_FILE, HEADER_BLOCK) and DATA_OBJECT_ID of what was Partition P_500 earlier. What has happened is that the Segment for Partition P_500 has now become the Segment for Partition P_450 while a *new* Segment (and DATA_OBJECT_ID) has been created for Partition P_500. Effectively, the physical entity (Segment and Data_Object) for Partition P_500 has been "reused" for Partition P_450 while Partition P_500 has been "born again" in a new incarnation. This SPLIT (unlike the earlier SPLIT) resulted in only 1 new Segment (and Data_Object).  The existing row remained in the existing Segment without being rewritten.  The new Segment is created for any "empty" Partition.

For further reading, I suggest that you read on "fast split" operations under "Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations".

SQL> select * from test_partitioned_table partition (p_450);

ID_COLUMN DATA_COLUMN
---------- ---------------
450 450

SQL> select * from test_partitioned_table partition (p_500);

no rows selected

SQL>

There you can see that the row is in Partition P_450 whose physical extent is the same as before.


Note : In my demonstration, each Segment is only 1 Extent.

SQL> l
1 select partition_name, extents
2 from dba_segments
3 where owner = 'HEMANT'
4 and segment_name = 'TEST_PARTITIONED_TABLE'
5 and segment_type = 'TABLE PARTITION'
6* order by partition_name
SQL> /

PARTITION_NAME EXTENTS
------------------------------ ----------
P_100 1
P_200 1
P_300 1
P_400 1
P_450 1
P_50 1
P_500 1

7 rows selected.

SQL>

You may have to be dealing with Segments with multiple Extents.

Another takeaway from the query on DBA_SEGMENTS is that (OWNER, SEGMENT_NAME) is not the identifying Key for a Segment.  In fact for a Partitioned table there is *NO* Segment for the Table itself.  There exist Segments for the Table Partitions.  The query on DBA_SEGMENTS must be on (OWNER, SEGMENT_NAME, PARTITION_NAME) by SEGMENT_TYPE = 'TABLE PARTITION'.

.
.
.

Categories: DBA Blogs

(Slightly Off Topic) Spurious Correlations

Wed, 2014-05-14 09:33
During the course of the job, we find, discover and analyze "data" and come up with "information".  Sometimes we find correlations and "discover" causes.  We say "Event 'A'  caused Result 'X'".   However, it can  so happen that some "discovered" correlations are not "causal correlations" --- i.e. "Event 'A' has NOT really caused Result 'X'".  The mathematical correlation ("coefficient of correlation") may be high but there really is no logical or physical association between the two.

Here are some examples of Spurious Correlations.

The next time you say "I find a high correlation between the two", stop and think.  For a study of common biases and fallacies, I recommend "the art of thinking clearly" by rolf dobelli.

,
,
,



Categories: DBA Blogs

PageView Count

Sat, 2014-04-26 08:41
PageView count of this blog, Top 10 Countries

Country              PageViews
United States      250,986India              116,730United Kingdom      37,784Germany             29,809France              20,570Canada              15,333Russia              13,650Brazil               5,884Australia            4,840Singapore            4,647
...
Categories: DBA Blogs

Upgrading Certification to 12c

Thu, 2014-04-24 09:01
Here's an Oracle Webinar on upgrading certification to 12c
.
Here are notes by Matthew Morris on the upgrade for 9i/10g/11g OCAs : A Lifeline for 9i and 10g OCAs
.
.
.
Categories: DBA Blogs