Oracle blog – NIOUG

Lot of times this question is asked that how one can be a good dba( I am looking forward to become one myself) and the answers are standard only like, train yourself constantly, look for new technologies and test/play with them, document your stuff, be proactive and so on. The answers  depend upon from person to person and the quality of them over the experience of the person. Here it doesn’t matter you are a dba of Oracle Database or DB2 or MSSQL or whatever. Pythian’s Keith talks about the same here.

http://www.pythian.com/blogs/1169/what-makes-a-good-dba

Well there are tons of things that one should do in order to become/called a good dba but I would say that if you got a tag/job title or Database Admin, you need to make sure to do one thing, take your db’s backups constantly and test them out. If you won’t your company( or you even if you own it) will end up like this and there wont be any forgiveness from anyone. Have a read of this link from Keiths’s post,

http://www.techcrunch.com/2006/06/29/couchsurfing-deletes-itself-shuts-down/

Sad but nothing else would have been expected too.

Aman….

I always thought that the creation of table is a mere data dictionary update. I mean to say that the data dictionary , dba_tables, OBJ$ and other similar ones would get updated when we create a table. The logic was that its just a definition so where else it would be going? Its correct too that its just a definition but still for couple of things, Oracle does access DataBuffer Cache too when it creates a table and (here is what I didnt even think to see) not just accesses it but also makes couple of its block dirty too. Doesn’t make a sense,well ok let the numbers,bits, flags speak then. Have a look,

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 22 00:24:26 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create table t (a number);

Table created.

SQL> select object_id ,object_name from user_objects where object_name='T';

 OBJECT_ID	OBJECT_NAME
----------	----------

     72217	T

Here comes the interesting part:

SQL> SELECT b.block#,b.class#,b.status, object_name, object_type, dirty "Dirty" FROM v$bh b,
  2   dba_objects o WHERE b.objd = o.data_object_id AND o.owner = 'AMAN' and object_name='T';

    BLOCK#     CLASS# STATUS     OBJECT_NAME          OBJECT_TYPE         D
---------- ---------- ---------- -------------------- ------------------- -
      3746          9 xcur       T                    TABLE               Y
      3745          8 xcur       T                    TABLE               Y
      3747          4 xcur       T                    TABLE               Y

SQL>

The buffers from the table T are marked Dirty and are accessed in the XCUR mode which means the blocks are accessed in the Exclusive Current mode and is representing that these blocks are going to be modified. The description of the buffer status which is shown from V$BH(or X$BH.status) from 11gr1 docs is given here,

Here is the link for the same in docs.http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_1059.htm#REFRN30029

A much better description of the same is mentioned in the 9iR2 docs,in the RAC guide. The link is mentioned below and also the description,

http://download.oracle.com/docs/cd/B10501_01/rac.920/a96597/pslkgdtl.htm#19780

Block Access Modes and Buffer States

An additional concurrency control concept is the buffer state which is the state of a buffer in the local cache of an instance. The buffer state of a block relates to the access mode of the block. For example, if a buffer state is exclusive current (XCUR), an instance owns the resource in exclusive mode.

To see a buffer’s state, query the STATUS column of the V$BH dynamic performance view. The V$BH view provides information about the block access mode and their buffer state names as follows:

• With a block access mode of NULL the buffer state name is CR–An instance can perform a consistent read of the block. That is, if the instance holds an older version of the data.
• With a block access mode of S the buffer state name is SCUR–An instance has shared access to the block and can only perform reads.
• With a block access mode of X the buffer state name is XCUR–An instance has exclusive access to the block and can modify it.
• With a block access mode of NULL the buffer state name is PI–An instance has made changes to the block but retains copies of it as past images to record its state before changes.

Only the SCUR and PI buffer states are Real Application Clusters-specific. There can be only one copy of any one block buffered in the XCUR state in the cluster database at any time. To perform modifications on a block, a process must assign an XCUR buffer state to the buffer containing the data block.

For example, if another instance requests read access to the most current version of the same block, then Oracle changes the access mode from exclusive to shared, sends a current read version of the block to the requesting instance, and keeps a PI buffer if the buffer contained a dirty block.

At this point, the first instance has the current block and the requesting instance also has the current block in shared mode. Therefore, the role of the resource becomes global. There can be multiple shared current (SCUR) versions of this block cached throughout the cluster database at any time.

So the blocks are meant for the modification and hence they are marked dirty after being modified. The same is confirmed from the query written by Jonathan Lewis,

SQL> select
         decode(bitand(flag,power(2,00)),0,'No','Yes' ) buffer_dirty,
         decode(bitand(flag,power(2,01)),0,'No','Yes' ) about_to_modify,
         decode(bitand(flag,power(2,02)),0,'No','Yes' ) mod_started,
         decode(bitand(flag,power(2,03)),0,'No','Yes' ) block_has_been_logged,
         decode(bitand(flag,power(2,04)),0,'No','Yes' ) temp_data,
         decode(bitand(flag,power(2,05)),0,'No','Yes' ) being_written,
         decode(bitand(flag,power(2,06)),0,'No','Yes' ) waiting_for_write,
         decode(bitand(flag,power(2,07)),0,'No','Yes' ) checkpoint_wanted,
         decode(bitand(flag,power(2,08)),0,'No','Yes' ) recovery_reading,
         decode(bitand(flag,power(2,09)),0,'No','Yes' ) unlink_from_lock,
         decode(bitand(flag,power(2,10)),0,'No','Yes' ) down_grade_lock,
         decode(bitand(flag,power(2,11)),0,'No','Yes' ) cross_instance_write,
         decode(bitand(flag,power(2,12)),0,'No','Yes' ) reading_as_CR,
         decode(bitand(flag,power(2,13)),0,'No','Yes' ) gotten_in_current_mode,
         decode(bitand(flag,power(2,14)),0,'No','Yes' ) stale,
         decode(bitand(flag,power(2,15)),0,'No','Yes' ) deferred_ping,
         decode(bitand(flag,power(2,16)),0,'No','Yes' ) direct_access,
         decode(bitand(flag,power(2,17)),0,'No','Yes' ) moved_to_lru_tail,
         decode(bitand(flag,power(2,18)),0,'No','Yes' ) ignore_redo,
         decode(bitand(flag,power(2,19)),0,'No','Yes' ) only_sequential_access,
         decode(bitand(flag,power(2,20)),0,'No','Yes' ) unused_0x100000,
         decode(bitand(flag,power(2,21)),0,'No','Yes' ) re_write_needed,
         decode(bitand(flag,power(2,22)),0,'No','Yes' ) flushed,
         decode(bitand(flag,power(2,23)),0,'No','Yes' ) resilvered_already,
         decode(bitand(flag,power(2,24)),0,'No','Yes' ) ckpt_writing,
         decode(bitand(flag,power(2,25)),0,'No','Yes' ) redo_since_read,
         decode(bitand(flag,power(2,26)),0,'No','Yes' ) unused_0x4000000,
         decode(bitand(flag,power(2,27)),0,'No','Yes' ) unused_0x8000000,
         decode(bitand(flag,power(2,28)),0,'No','Yes' ) unused_0x10000000,
         decode(bitand(flag,power(2,29)),0,'No','Yes' ) unused_0x20000000,
         decode(bitand(flag,power(2,30)),0,'No','Yes' ) unused_0x40000000,
         decode(bitand(flag,power(2,31)),0,'No','Yes' ) unused_0x80000000
 from x$bh where obj=72217;

BUF ABO MOD BLO TEM BEI WAI CHE REC UNL DOW CRO REA GOT STA DEF DIR MOV IGN ONL UNU RE_ FLU RES CKP RED UNU UNU UNU UNU UNU UNU
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
Yes No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  Yes No  No  No  No  No  No

Yes No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  Yes No  No  No  No  No  No

Yes No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  No  Yes No  No  No  No  No  No

So indeed the buffers are marked are as dirty only. But the buffers are supposed to be marked dirty when they are changed and with the create table,what’s going on which has made them change. Each buffer belongs to a particular classLets try to find out the class description of these buffers using their numbers.The block classes are described here*,

So according to the result that we have got, the blocks belong to the classes 4,8,9 and these numbers represent the classes(in the same order ),Header Block,1st level Bitmap Block,2nd Level Bitmap Block.

When oracle creates a table, it has to allocate a segment header block. This is done by picking up a block from a cache,formatting it,writing into it the new description of the data segment.As it is written so it is marked dirty and subsequently is written to the datafile.

The Automatic Segment Space Management allocates 3 levels of the blocks for example, Level1 , Level2 , Level3. The level 3 is the bitmap header block which resides in the segment header. This is linked to the Level 2 block. This block is used to search Level 1 which is always the first block in the extent. Level 1 marks the blocks for their respective status. So it appears , Oracle does the same what it does for the Segment header block to the Bitmap Blocks i.e. acquires two blocks from the cache , marks as Bitmap blocks, writes into them and marks them dirty. So this is the reason that we get the blocks marked as dirty even when we have not done anything by ourselves to change them. For me it was a new concept as I was not aware about it before. I would still try to dig upon more and will try to post more as and when I shall find it out. Heartiest thanks to Jonathan Lewis for helping me to understand it.

References,

http://www.juliandyke.com/Internals/BlockClasses.html

http://www.jlcomp.demon.co.uk/buf_flag.html

Aman….

Its not at all a good situation when anyone has to go face to face with ORA-00600. Well its not a nice error message to see and that too when you had least expected it. So what happens when this error comes? Actually there is no one reason for it. This error is an internal error raised by some issue occuring within the oracle’s code. There can be many reasons for this error to come and certainly I shall not cover them all here in this post itself. This post is about a specific ora-600 code that I have mentioned in the title and how did I overcome that. If you are interested than carry on reading.

Few days back we were in our lab and our machines were not in the network. We didn’t need the network too so we didn’t bother. In one of the discussions, there was a requirement that asked for network to be up. Those were Linux machines and students didn’t know how to enable network over there. Well I know a little about Linux so I did make it enable and also enabled the use of DHCP . Nothing seems to be wrong, network was up, internet also came up and Oracle was working fine. I would mention here that our /etc/hosts file was empty as the host information was not required and was picked up by DHCP. After one scenario, we needed to bounce the database.

After instance startup, we were greeted with ora-00600[keltnfy-ldmInit],[46],[1]. Well this ora-600 comes, its always suggested to contact support to find the cause and remedy for it. The problem was I couldn’t call support for me and the other solution was to re-install all the machines. Well I did read some where that all the codes of ora-00600 doesn’t mean that oracle needs to be contacted to rectify it. So I searched a bit and found that this error code that came to us is Oracle’s way of saying that it couldn’t find the information of the host on which it was sitting. Its certainly true as we had enabled DHCP and Oracle is not at all happy with DHCP enabled systems.

So what happened was that Oracle was looking for the host information from the /etc/hosts file but it was empty. Now for oracle to start, it needs a host name. As the file was empty , it couldn’t find anything about it from there. So it stopped the mounting of the database. So the solution was that we edited the /etc/hosts file and entered the host information with host name as “local host” and the IP address that we were getting at that time. As the machines wouldn’t be rebooted so it was safe to enter that value. Tried starting oracle and voila! We were back in the business!

Our database was 10201 on RHEL. This error is also removed from upgrading db to 10202 and further. So if you are on 10201 and are facing this error, you need to have the patch applied to it and upgrade to 10202.

Though I did remove this error with a little search but still the fact doesn’t change that when you hit ora-600, its high time to contact Oracle Support Services as you have hit some thing really serious.

For novices, compress parameter in export has always been a misunderstood parameter. People (in the beginning, as a total novice i too didn’t understand it correctly) relate it with compression of data in the export file or someone would deduce some other definition. So i thought about writing a small post explaining what compress is all about.

First of all from 10g documentation Utilities Guide:

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.

In, nutshell what it means to say is that if we specify COMPRESS=y during export then at the time of table creation while importing, the INITIAL extent of the table would be as large as the sum of all the extents allocated to the table in the original database.

If we specify COMPRESS=n during export then while creating table in the import, it will use the same values of INITIAL extent as in the original database.

Now lets say I have a table of 100 MB. There have been some deletions and updations and only 50 MB of actual data is there. I export the table with COMPRESS=y and recreate it in some other database. It will sum all the extents and assign as INITIAL extent while creating the table. There is only 50 MB of data in the table but it has allocated 100 MB already. In case, you have limited space this is not a very good option.

If i do with COMPRESS=N and then import the table, its INITIAL extent will be as large as INITIAL extent in the original database and then as required, new extents will be allocated. So now my table in the new database would be approximately 50 MB in size.

Lets try the same with the help of an example of a table.

First of all lets create a table T in schema scott and view the details from various views:

SQL> create table t as select * from all_objects;

Table created.

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER                          TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
SCOTT                          T                                       65536

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         7

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID         Kb
---------- ----------
         0         64
         1         64
         2         64
         3         64
         4         64
         5         64
         6         64
         7         64
         8         64
         9         64
        10         64
        11         64
        12         64
        13         64
        14         64
        15         64
        16       1024
        17       1024
        18       1024
        19       1024
        20       1024
        21       1024

22 rows selected.

SQL>

Now i delete some of the rows from the table and check the size from DBA_SEGMENTS.

SQL> delete t where rownum<31445;

31444 rows deleted.

SQL> commit;

Commit complete.

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         7

SQL>

The size is still 7 MB as Oracle has not freed the allocated extents.

Case 1: COMPRESS=y

Now in first case we export the table T with compress=y (which is the default), drop the table and import it back. Then we will check the size of the INITIAL extent, size of the table and information from DBA_SEGMENTS.

$ exp userid=scott/tiger file=T.dmp tables=T

Export: Release 10.2.0.3.0 - Production on Tue Aug 5 15:57:52 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T      31445 rows exported
Export terminated successfully without warnings.
$

SQL> drop table t;

Table dropped.

SQL>

$ imp userid=scott/tiger file=T.dmp tables=T

Import: Release 10.2.0.3.0 - Production on Tue Aug 5 15:59:04 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      31445 rows imported
Import terminated successfully without warnings.
$

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER                          TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
SCOTT                          T                                     7340032

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         7

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID         Kb
---------- ----------
         0       1024
         1       1024
         2       1024
         3       1024
         4       1024
         5       1024
         6       1024

7 rows selected.

SQL>

So here we see the INITIAL extent is of 7340032 bytes (7 MB) which is sum of all the extents allocated to the table originally. Lets now see the actual data in the table. Gather stats on the table and calculate the size of total data from avg_row_len.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');

PL/SQL procedure successfully completed.

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from dba_tables where table_name='T' and owner='SCOTT';

TABLE_NAME                     size
------------------------------ ------------------------------------------
T                              2825.14kb

SQL>

So the actual data in the table is only 3 MB but its occupying 7 MB. Now lets look at the 2nd case: COMPRESS=n

Case 2: COMPRESS=n

(Note: Here i will recreate the table T from all_objects as when we imported it in last case, it was created with INITIAL extent of 7 MB and it will be useless to experiment on that)

SQL> drop table t;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> delete t where rownum<31445;

31444 rows deleted.

SQL> commit;

Commit complete.

SQL>

$ exp userid=scott/tiger file=T.dmp tables=T compress=n

Export: Release 10.2.0.3.0 - Production on Tue Aug 5 16:10:45 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T      63486 rows exported
Export terminated successfully without warnings.
$

SQL> drop table t;

Table dropped.

SQL>

$ imp userid=scott/tiger file=T.dmp tables=T

Import: Release 10.2.0.3.0 - Production on Tue Aug 5 16:12:50 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                            "T"      63486 rows imported
Import terminated successfully without warnings.
$

SQL> select owner,table_name,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where table_name='T' and owner='SCOTT';

OWNER                          TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
SCOTT                          T                                       65536

SQL> select sum(bytes)/1024/1024 "MB" from dba_extents where segment_name='T' and owner='SCOTT';

        MB
----------
         4

SQL> select extent_id,bytes/1024 "Kb" from dba_extents where segment_name='T' and owner='SCOTT';

 EXTENT_ID         Kb
---------- ----------
         0         64
         1         64
         2         64
         3         64
         4         64
         5         64
         6         64
         7         64
         8         64
         9         64
        10         64
        11         64
        12         64
        13         64
        14         64
        15         64
        16       1024
        17       1024
        18       1024

19 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');

PL/SQL procedure successfully completed.

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
  2  from dba_tables where table_name='T' and owner='SCOTT';

TABLE_NAME                     size
------------------------------ ------------------------------------------
T                              2910.25kb

SQL>

Here we can see the difference. The size of INITIAL extent has not been altered and the space occupied is equal to the total data in the table.

Moral of the story is that if you are using locally managed tablespaces you should always export with COMPRESS=n as allocation of extents is not a big issue here as it was in dictionary managed ones.

Tom Kyte also explains the same in this post on Asktom.

Welcome to our blog. This post is not a tech post. Its about this blog and a little history of about the genesis of it. Me and Amardeep kept on talking for the thing that now a days almost all are writing an Oracle related blog. There is no question or discussion we would get into that how many are really a quality stuff and how many are just mere replicas of docs. The idea of bringing out this blog was that it should serve as a good repository for us about all the big and small things which we see, face, solve and some times about those too which remain unsolved even. Rather than searching over Google or elsewhere over the internet, it would be better to keep our things ( research if I may use the word) at one place for an easy access at a later point of time. This is the primary goal of this blog. In this process, if it serves as a help for someone who got struck in the same issue and/or concept where we struck, it would be a great thing for us. So this is the basic reason that we had thought and finally brought up this blog.

About  collective authors, well if you read the About page, Amar has already mentioned the motto of Wikipedia. So here we are, oracle admins who would try to share via this space our experiences with this so complex yet so fun-to-use product Oracle database. I hope that this blog would get updated very frequently. You are most welcome to come and read it and it would be highly appreciable if you comment( and correct ) over the writings too.

Cheers.

Aman….