Featured Posts
Latest Posts

Tuesday, September 27, 2011

Oracle DBCA seed database and dbms_backup_restore

It’s more than a month as I was busy with my tasks and work. As today I was moving from Bangalore to Hyderabad (Home town) and not getting sleep, just picked up my laptop and thought to post across about the Oracle database DBCA. The question stands how does the database gets created from the oracle supplied default “General Purpose (or) Transaction Processing”. As most of us know the things around as I would like to present the picture in different view.

Let’s see further how oracle works while creating an database using DBCA. I will try to create an “test” database and let’s try to generate templates and scripts which can utilized for creating similar type of database which can used for future reference.

Please find the list of scripts which was generated by oracle.

  1. testdb.bat
  2. testdb.sql
  3. CloneRmanRestore.sql
  4. cloneDBCreation.sql
  5. postScripts.sql
  6. lockAccount.sql
  7. postDBCreation.sql

Further,let’s try to dig up more and understand the things across, the usage of scripts and execution.

refer to link :https://sites.google.com/site/oraclepracticals/backup-recovery/oracle-rman/dbms_backup_restore-seed-db

read more...

Sunday, May 22, 2011

Tablespace Metadata Size

After looking at the tag (or) title name you find that, I am discussing the same old concept. If that’s correct, then certainly it would depend on the things, how we look at. Okay, let me jump to the practical's, we all know and studied across the tablespace's (extent management and segment space management). I am not going to discuss the things from internal, just laying the path to study and understand when certain things created what things and size it does take internally.

So, Extent Management can be of Auto-allocate (Default) or Uniform. Let’s see a demo,

   1: C:\Windows\system32>sqlplus /@test as sysdba
   2:  
   3: SQL*Plus: Release 11.2.0.1.0 Production on Sun May 22 15:11:43 2011
   4:  
   5: Copyright (c) 1982, 2010, Oracle.  All rights reserved.
   6:  
   7:  
   8: Connected to:
   9: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  10: With the Partitioning, OLAP, Data Mining and Real Application Testing options
  11:  
  12: SQL> create tablespace lmt_demo_uni1 datafile 'D:\lmt_uni_datafile2.dbf' size 10M
  13:   2  extent management local;
  14:  
  15: Tablespace created.
  16:  
  17: SQL> create tablespace lmt_demo_uni3 datafile 'D:\lmt_uni_datafile4.dbf' size 10M
  18:   2  extent management local uniform size 30k;
  19: create tablespace lmt_demo_uni3 datafile 'D:\lmt_uni_datafile4.dbf' size 10M
  20: *
  21: ERROR at line 1:
  22: ORA-03249: Uniform size for auto segment space managed tablespace should have
  23: atleast 5 blocks

If you check across the snippet the we came across the issue “ORA-03249: Uniform size for auto segment space managed tablespace should have atleast 5 blocks


So, we can create an tablespace of size 5 * db_block_size (minimum) with out any issues – It’s size of metadata (bitmaps – default segment type is system managed). Let’s create and check across..


Current db_block_size 8192



   1: SQL> create tablespace lmt_demo_uni3 datafile 'D:\lmt_uni_datafile4.dbf' size 10M
   2:   2  extent management local uniform size 35k;
   3:  
   4: Tablespace created.
   5:  
   6: SQL> select * from dba_free_space
   7:   2  where tablespace_name='LMT_DEMO_UNI1';
   8:  
   9: TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
  10: ------------------------------ ---------- ---------- ---------- ----------
  11: RELATIVE_FNO
  12: ------------
  13: LMT_DEMO_UNI1                           8        128    9437184       1152
  14:            8
  15:  
  16:  
  17: SQL> select * from dba_free_space
  18:   2  where tablespace_name='LMT_DEMO_UNI3';
  19:  
  20: TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
  21: ------------------------------ ---------- ---------- ---------- ----------
  22: RELATIVE_FNO
  23: ------------
  24: LMT_DEMO_UNI3                          10          8   10403840       1270
  25:           10

If you check across further details, you can find some differences in size (bytes) got allocated.



   1: SQL> select 10485760-9437184 diff from dual;
   2:  
   3:       DIFF
   4: ----------
   5:    1048576
   6:  
   7: SQL> select 1048576/1024/1024 diff_mb from dual;
   8:  
   9:    DIFF_MB
  10: ----------
  11:          1
  12:  
  13: SQL> select 10485760-10403840 diff from dual;
  14:  
  15:       DIFF
  16: ----------
  17:      81920
  18:  
  19: SQL> select 81920/1024/1024 diff_mb from dual;
  20:  
  21:    DIFF_MB
  22: ----------
  23:    .078125
  24:  
  25: SQL> select 81920/1024 diff_kb from dual;
  26:  
  27:    DIFF_KB
  28: ----------
  29:         80

If you check across the differences from above snippet, be default 1MB – metadata allocation is maintained else worst case we can allocate (minimize the allocation).


Oracle Doc say’s



•If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.


•If you set extent management to LOCAL AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.


But – certain questions stands – what could be the reason that Oracle allocates default 1MB of size – instead of 5 Blocks of memory for metadata for each tablespace.


Further – in you check the sum of size of v$type_size – which gives the information about internal structure of Oracle block level details.


Oracle doc says



V$TYPE_SIZE displays the sizes of various database components for use in estimating data block capacity.


It’s total sum of type_size – 1132.


Still we need to check across the things and reasons of metadata size – whether does it extends as the size of tablespace increases (in terms of data files).


I hope you people already knows things prior to me, as I came across some thing about this and posted across and hope it’s find useful in learning curve of every other individual.

read more...

Saturday, May 21, 2011

_in_memory_tbs_search

While carrying out the research on some of the parameters how they would impact the Oracle database functioning, I came across an important undocumented parameter which is enabled by default on Oracle 11g Release 2 versions.

Oracle undocumented parameter

_in_memory_tbs_search                    TRUE            FALSE - disable fast path for
                                                         alter tablespace read only

I am just working with different scenarios how this parameter internally carrying out the fast path access. Only the best way we can get the things is the steps which it carry out while performing  things from dba_lock_internal (lock_type – provides the different types of locks and transactions executions carried while carrying out the task)

When I compared to previous versions of Oracle the parameter is set to NULL.  we might to test on huge databases in order to test /compare the performance during conversions.

Hope it’s find useful..

read more...

Wait Event : unbound tx

While I was revising oracle documents “waits events” I came across this wait event. I was trying to create scenario in different ways in order to understand, how to troubleshoot this type of wait events.

Oracle doc say’s

The session waits to see if there are any transactions that have been started but do not have a Rollback Segment associated with them.

I was trying to understand that session waits (current session which fires/executes sql’s) check for existence concurrent sessions prior to start of current session (SCN), that have been started but does not have rollback segments – perhaps forcing the current session to wait. That’s interesting.!!

In order to understand how to troubleshoot this type of wait events, I have provided demo on Oracle Practical's blog. You can even try in different ways in order to replicate the scenario – for your experience,such type of issues for your learning purpose.

https://sites.google.com/site/oraclepracticals/oracle-wait-events/unbound-tx

I hope you find interesting..!!

read more...