Working with Capacity plan in Oracle DBA

 

Capacity plan  

[to decrease DSA]

no.of rows per day = 12000 [for second time it may increase]

avg-row-len             = 400

no. of days in a month =22

frequency =4 months.

Size of 1 st extent required = 12000 x 400 x 22 x 4

     

Storage parameters  [dictionary management]

Change	Parameter name	DBA value	ORACLE value
NO	Initial	100k	10k
Yes	Next	100k	10k
NO	Minext	1	1
Yes	Maxext	20	121
Yes	Pot increase	20%	50%

  SQL> create table t1 (sno number, ………) storage [initial 100K

next 100k

minext 1

maxext 20

pot increase 50 ]

     

UP to ORACLE 8i only DM [dictionary management]  

1. When a user defines storage parameters at the time of segment [table/index] creation ORACLE offers the same, provided he is still under QUOTA limit
2. If the user is not aware of storage parameters and submitted only create table part then ORACLE offers whatever the DBA has defined as default storage parameters defined at tablespace creation.
3. If neither user nor DBA defined storage parameters, default storage parameters respectively then ORACLE offers its own default storage parameters at the tablespace level which would be inherited for those segments which are having no storage parameters.

         

For static tables, DSA [dynamic space allocation] doesn’t suit therefore for static tables DSA not recommended. Before going to allocate storage parameter to any tables, as a DBA we should know the nature 9static, dynamic) of the tables.    

1. ORACLE introduced LMTS [locally managed table space] from version 8i [it used to be optional and became default from version 9i]
2. LMTS is further divided into

A] uniform [where all the segments are the same in size]

B] auto [where all the sizes of extents are controlled by ORACLE]

1. Uniform [LMTS] is usually meant for static, semi-dynamic tables.
2. The beauty of LMTS uniform is every hole [created by dropping segments] becomes best – fit thus there is no fragmentation at all.
3. we can have multiple LMTS uniform tablespaces of the same or different extent sizes.

Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore Oracle DBA Training

   

Freelist:

The blocks which are free i.e. pct free is less than 40% will be maintained in the free list. ORACLE will first search for a free block in the free list and then the data is inserted into that block.

Note:

segment space management is manual.  

In DMTS

• If the size of the block is 2K then the initial value is 10K
• if the size of the block is 8k then the initial value is 40k

 

In DMTS free list information is maintained by base tables in LMTS free list information is maintained by. Block itself

DMTS    [free list info. maintained by “base tables”]  SSM=manual

Default values in 2k block size		Default values in 8k block size
10k	Initial	40k
10k	Next	40k
1	Minex tents	1
121	Maxex tents	505
50%	Pot increase	5-%

  LMTS  Free list info. Maintained by data file only

SYS > select segment.name, extent_id,file_id.block.id, bytes, blocks from da_extents where table space_name=’LMTS’ order by extent_id, block id;

SYS > save dba_ext

SYS > select segment_name, tablespace_name, initial_extent, next_extent,min_extents,pct_increase from dba_segments where tablesapace_name= ‘LMTS’”

SYS >  save dba_seg

SYS >  select tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase, segment_space_managment OS SSM from dba_tablespaces where tablespace_name =’DMTS’ ;

SYS >  save dba_ts;    

DMTS   [SSM manual]

SYS >  create tablespace dmts datafile ‘/disk3/oradata/pal/dmts.dbf’ size 20m extent management dictionary;

SYS >  @dba_ts

SYS >  create tablespace dmts1 datafile ‘disk3/oradata/pal/dmts1/dbf’ size 20m extent management dictionary default storage ( initial 80K next 80k minextents 1 maxextents 100 loctincrease 50);

SYS >  @ dba_ts

SYS >  create user dmts identified by dmts default tablespace dmts;

SYS > grant connect, resource to dmts;

SYS > conn dmts/dmts

Dmts > @$ORACLE_HOME/demobld

Dmts > select * from tab;

Dmts > create table ex1 (dd number) storage (initial 16k next 16k minextents 1 maxextents 10 pctincrease 50);

SYS> @ dba_seg

SYS> edba_ext

Dmts> insert into emp select * from emp;

Dmts>  / [up to some hundreds of records]

SYS> @ dba_ext

Dmts> create table test (sno number) pot pree 20 pot user 40;

Dmts> select pot free, pot_used, table_name from user _ table;

Since SSM is manaual, we can change only pct_free, pct_used by specifying.  

LMTS [SSM auto]

In LMTS auto SSM, we can give storage parameters, but it takes default of ORACLE’s Storage parameters.

SYS> create table space lmts datafile ‘disk3/oradata/pal/lmts.dbf’ size 10m;

SYS> @ dba_ts

SYS> create tablespace lmts1 datafile ‘/disks/oradata/pal/lmts1/dbf’ size 20m default storage 9initial 40k next 40k minextents 40)

SYS> @ dba_ts

………. Even though we specifie storage parameters, oracle considers default values in LMTS auto.

SYS> create user. Lmts identified by lmts default table space lmts;

SYS> grant connect, resource to lmts;

SYS> conn lmts/lmts

Lmts > @$ORACLE_HOME/demobld

SYS> @ bda _ seg

SYS> @ dba _ ext

Lmts > insert into emp select * from emp;

Lmts> select table_name, pct_used, pct_free from user_tables;

Lmts> save user_tab

Lmts> create table test (info number) pct free 20 pot used 50;

Lmts> @ user_ tab

In lmts auto, we can change only pot free, we can’t change the pot used

…. To change pot used in lmts auto

SYS> create tablespace lmts 2 datafile ‘/disk3/oradata/pal/lmts2.dbf’ size 20m segment space management manual;

SYS> @ dba-ts

LMTS [uniform SSM]

SYS> create table space lmts_u data file ‘/disk3 / oradata/pal/lmts_u.dbf’ size 20m extent management local uniform;

SYS> @dbo_ts

SYS> conn lmts/ lmts

Hnts> create table testu (tno number) storage (initial 40k next 40k);

SYS> @ dba_seg.

In lmts [uniform ssm] the initial demand i.e initial 40k only can be supported by ORACLE.

{for additional notes on lmts turn page to 90}    

UNDO & FB-qry & FB table & FB-version Qry 

AUM [automatic UNDO management]   form ORACLE9i

• Prior to ORACLE 9i in order to support DML transactions, DBA had to create RBS [rollback segments]
• Issues with RBS
• DBA has to create them
• Depending on the number of concurrent transactions DBA has to decide the number of RBS required
• To offer the right storage parameters for RBS again the DBA has to depend on no.of Txs.
• The DBA needs to specify which RBS has to be made available with intt.ora

 The solution: for all the above issues is new UNDO segments.

Aum

1. 1. This is started from ORACLE 9i reliving the DBA from day to day transaction management.
   2. DBA simply has to create undo tablespace, ORACLE in-turn creates 10 Undo segments on its own.
   3. Its ORACLE’s responsibility for taking care of ‘extents’ sizes.
   4. No more monitoring is required by DBA.
   5. We can have multiple Undo tablespaces [if required.]
   6. But, only one undo tablespace can be active at any point in time.
   7. We can switch from one undo tablespace to another.
   8. In one database, we can maintain traditional RBS & new undo segments.

But either of these can be brought online.

Different Undo segments

200 mb undo_small_+ts [inday]

2 gb undo_big_ts [in the nights]

[since huge transactions like  procedures, reports, etc.]  

   

The greatest advantage of new undo is compared to old RBS is, if one undo segment wants to grow but not enough free space is found in the ts, then ORACLE starts transferring extents from another undo segment which belongs to a done transaction.

In order to go with AUM

Dba12~] $ cd $ ORACLE_HOME/dbs

Dba12 dbs]$ vi initrajpar.ora

Undo_managment= auto [for undo segments]

manual [for traditional RBS]

Undo_tablespace= undo_small-ts

undo_big_ts

Undo_retention= time

:wq

Flashback

   

RULES IN ORDER TO FLASHBACK

• DBA must have enabled undo tablespace with the retention period.
• The user must have realized his mistake before the retention period.
• Users should not exit from the session.
• Even the front end must be enabled with the flashback option [package].

ORACLE 10g offers guaranteed retention, where in ORACLE 9i never there.  

DEMO

 

Undo segment

Undo_retention=90 [15min]

Unexpired blocks

The blocks which are in the retention period

Expired blocks The blocks which are in out of retention period, which is ready for new transactions.

Active blocks         The blocks which contain ongoing transactions [not committed Txs]

Free blocks             at the time of creation all are empty blocks

SNAPSHOT too error 1555

Dba12 ~] $ cd $ ORACLE_HOME/dbs

Dba12 dbs]$ vi initrajpar.ora

Undo_tablespace=undotbs.dbf

undo_retention=900

:wq

Dba12 dbs] $ exit

Dba12 sys > create tablespace sse datafile ‘/disk3/oradata/pal/sse.dbf’ size 20m;

sys > create user ss identified by ss default table space sse;

sys > grant connect, resource to ss;

sys > conn ss/ss

ss> !demobld ss/ss

ss> select * from tab;

ss> select * from emp;

ss> insert into emp select * from emp;

ss> /

ss>/

ss>/ (114688 records approx)

ss> commit ; [manual log switch]  

from another session

dba12 ~] $ sqlplus ‘/as sysdba’

dba12 SYS > conn ss/ss

ss> ed                       file

begin

loop

update emp set sal=sal+100 where empno = n7788;

commit;

end loop;

end;

:wq

ss> @ file                 ss> select * from emp;

[from ession2]      [from session1]  

---:simultaneously

--:fails

--:success         ORA :1555

snapshot too old error

(ORACLE not able to find consistent value]Solution:

1. No frequent commits from users.
2. Increase the size of the Undo segment.

Retention guarantee

In ORACLE 10^g the unexpired blocks are not overwritten even though the blocks are filled up.

(the commit statements will be restricted]

Dba12 SYS>             create undo tablespace undo9 data file ‘/disk3 /oradata/pa;/undo9.dbf’ size 20m retention guarantee;

SYS>            select tablespace_name, retention from dba_tablespaces;

SYS>            alter system set undo_tablespace=undo9;

(dynamically)

(for the particular session only)

[no need to mention in init.ora]

Session 1                                                                             session2

SS> select * from emp                                                   ss>@file

Simultaneously --:succeeds:--                                                                    --:fails:--

Error

ORA – 3003

     

If any active transaction is there in small/big undo, it is not possible to switch from one undo to another.

The transaction which is under active [not yet commit or rolled back] one is called in pending offline stage.

SYS> select a.usn, a.name, b.xacts, b.status from v$rollstat b, v$ rollaname a where a.usn=b.usn;

SYS> save poff

User 2> insert into emp select * from emp;

SYS > @ poff

User2>commit;

SYS >@ poff

User2> delete from salgrade;

SYS> @ poff

User 2 > commit;

SYS > @ poff

SYS > alter system set undo_table space = undotbs;

SYS > @ poff  

For an in-depth knowledge, click on below

• Oracle DBA Interview Questions
• Introduction to oracle 10g dba