Drop Database through RMAN

Most  of the time  we  generally  use  DBCA  to  delete  the  database  because  it  is easy  and  simple . But , there are  some  scenario's  where we  have to drop  database  without using  the graphics  i,e, without DBCA . In such case  , we can  delete  or drop  the  database either by   manually  or  by using  sql*plus  or  RMAN prompt . As  compare  to  sql*plus , rman  is  much  more effective  because  it  consume  less   time  and secondly  we  can   delete  the  archivelogs  and backups  also . Starting  with Oracle  10gR1 onwards,  we  can  drop  a database  and  remove all  its records  from  the  rman catalog . 

There are basically 4 syntax available to drop the database using RMAN

1.) Drop Database : This command  deletes the datafiles, logfiles, Controlfiles and Spfile. If we do not want to delete backups then we can use this command.

2.) Drop Database Noprompt : When “NOPROMPT”  is specified RMAN does not prompt for the confirmation before deleting the database. It delete the above files as in case of drop database.

3.)DROP DATABASE INCLUDING BACKUPS : This command delete the datafiles, ogfiles,Controlfiles and Spfile plus the archivelogs and backup pieces  generated by RMAN.

4.) DROP DATABASE INCLUDING BACKUPS NOPROMPT  : When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

Let's have a look on the following steps to drop the Database using RMAN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area   285212672 bytes
Fixed Size                          1218992 bytes
Variable Size                      100664912 bytes
Database Buffers                180355072 bytes
Redo Buffers                      2973696 bytes
Database mounted.

SQL> alter system enable restricted session;
System altered.
SQL> exit
C:\> rman target /


RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "ORACLE" and DBID is 1574601275

Do you really want to drop all backups and the database (enter YES or NO)? yes   // (by defaults it prompts)

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        D:\RMAN\ORACLE_1
19      19      1   1   AVAILABLE   DISK        D:\RMAN\ARCH_ORACLE_1
deleted backup piece
backup piece handle=D:\RMAN\ORACLE_1 recid=18 stamp=711242843
deleted backup piece
backup piece handle=D:\RMAN\ARCH_ORACLE_1 recid=19 stamp=711243739
Deleted 2 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
2       A 27-DEC-09       2754635    27-DEC-09       D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
202     1    132     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1
203     1    133     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1
204     1    134     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1
205     1    135     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1
206     1    136     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1
207     1    137     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1
208     1    138     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1
209     1    139     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1
210     1    140     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1
211     1    141     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1
212     1    142     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1
213     1    143     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1
214     1    144     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1
215     1    145     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1
216     1    146     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1
217     1    147     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1
218     1    148     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1
219     1    149     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1
220     1    150     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1
221     1    151     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1
222     1    152     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1
223     1    153     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1
224     1    154     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1
225     1    155     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1
226     1    156     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1
227     1    157     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1
228     1    158     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1
229     1    159     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1
230     1    160     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1
231     1    161     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1
232     1    162     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1
233     1    163     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1
235     1    164     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1
234     1    165     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1
236     1    166     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1
237     1    167     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1
238     1    168     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1
239     1    169     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1
240     1    170     A 27-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1
241     1    171     A 28-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1
242     1    172     A 30-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1
243     1    173     A 31-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1
244     1    174     A 01-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1
245     1    175     A 03-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1
246     1    176     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1
247     1    177     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1
248     1    178     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1
249     1    179     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1

deleted control file copy
control file copy filename=D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL recid=2 stamp=706679151
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1 recid=202 stamp=708474283
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1 recid=203 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1 recid=204 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1 recid=205 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1 recid=206 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1 recid=207 stamp=708477174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1 recid=208 stamp=708477177
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1 recid=209 stamp=708477957
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1 recid=210 stamp=708478193
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1 recid=211 stamp=708523128
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1 recid=212 stamp=708523129
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1 recid=213 stamp=708523194
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1 recid=214 stamp=708527072
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1 recid=215 stamp=708527201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1 recid=216 stamp=708527278
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1 recid=217 stamp=708527459
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1 recid=218 stamp=708527641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1 recid=219 stamp=708527711
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1 recid=220 stamp=708531955
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1 recid=221 stamp=708532250
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1 recid=222 stamp=708532331
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1 recid=223 stamp=708537657
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1 recid=224 stamp=708537715
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1 recid=225 stamp=708537936
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1 recid=226 stamp=708538012
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1 recid=227 stamp=708538174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1 recid=228 stamp=708538195
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1 recid=229 stamp=708538201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1 recid=230 stamp=708539593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1 recid=231 stamp=708555515
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1 recid=232 stamp=708555516
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1 recid=233 stamp=708555536
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1 recid=235 stamp=708648593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1 recid=234 stamp=708648592
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1 recid=236 stamp=708648853
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1 recid=237 stamp=709329114
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1 recid=238 stamp=709330932
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1 recid=239 stamp=709423232
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1 recid=240 stamp=709515047
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1 recid=241 stamp=709678641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1 recid=242 stamp=709728779
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1 recid=243 stamp=709857486
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1 recid=244 stamp=710029358
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1 recid=245 stamp=710118379
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1 recid=246 stamp=710118402
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1 recid=247 stamp=710118425
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1 recid=248 stamp=710118565
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1 recid=249 stamp=710248827
Deleted 49 objects

Database name is "ORACLE" and DBID is 1574601275
Database dropped.


Enjoy      JJJ    

Automatic Archiving Stop when Archive Destination Disk is Full

The database is running in archive mode with automatic archiving is turned on . log Archive destination is in FRA (flash Recovery Area) .The DB_RECOVERY_FILE_DEST_SIZE  was set to be 2G. Once when i startup the database it goes at mount stage and throw the following error .

ORA-1034   : Oracle not available 

ORA-16014 : log 3 sequence# xx  not archived

Then i check my alert logfile and find the space related issue in fra .i.e  Automatic Archiving gets Stop when there is no space in disk. I fire the following command to resolve the issue .

SQL> startup
ORACLE instance started.
Total System Global Area       313860096 bytes
Fixed Size                               1332892 bytes
Variable Size                           281020772 bytes
Database Buffers                     25165824 bytes
Redo Buffers                           6340608 bytes
Database mounted.
ORA-1034     : Oracle not available 

SQL>alter system set log_archive_dest_1='location=<< new location>>'  ;      

( Here we may increase the fra size or change the archive destination , as  i have changed the archive destination )

SQL> alter system  archive  log  all  to  '<< new destination >>'  ;
SQL> shut immediate ( or sometimes shut abort if hangs)
SQL> startup 


Explanation : 

Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.

1) Using the command  

SQL> alter system archive log all to '<< new location >>' ; 

The   Above  command  gives  Oracle a valid  location  for  the  archive  logs.  Even after  using this the archive  log  destination  parameter  is  still  invalid  and  automatic  achive  does  not  work. We  can  also use  this  to allow  to  do  a  Shutdown  immediate  instead  of   Shutdown  abort. 

2.)  Shutdown  and restart  of  the database  resets  the  archive  log  destination  parameter  to  be  valid . Do not  forget  to  make disk  space available  before  starting  the  database. 

3.) Use  the  REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter  to determine whether and when  ARCn  attempts  to  re-archive to a failed destination following an error. REOPEN applies to all errors,  not  just  OPEN  errors. REOPEN=n  sets  the  minimum  number  of  seconds  before  ARCn should  try  to  reopen  a  failed  destination. The  default  value for  n is 300 seconds. A value of 0 is the same  as  turning  off  the  REOPEN  option, in  other  words,  ARCn  will  not  attempt  to  archive  after   a failure. If  we change   the  archive  destination  then  there  is  no  need  of  specifying  repoen  option  . 

ENJOY     :-)

Tablespaces that make use of nonstandard block sizes

A block size of 8 KB is optimal for most systems. On the other hand, OLTP systems can use smaller block sizes (such as 4K) and DSS systems can use larger block sizes (such as 16K, 32K). Tablespaces that make use of nonstandard block sizes can be determined by using the following  below script:

set echo off 
feedback off 
pagesize 50
linesize 80
spool  tblspce_with_non_std_blksize.txt
select Tablespace_Name,block_Size from DBA_TABLESPACES
where Block_Size != (select Value from V$PARAMETER where name = 'db_block_size');
spool off

Sample output of running the above script is shown below:
TABLESPACE_NAME                BLOCK_SIZE(K)
------------------------------         ----------
DRSYS                                         8192
TABLESPC4K                              4096
TABLESPC8K                              8192
INDX                                            4096
PROJECTS                                   8192
TOOLS                                        16384
USERS                                         8192

Note:  We can query the parameter db_block_size to determine the standard block size in use for a given database. The above output indicates that the nonstandard block sizes in use for the database are as follows: 

4KB   (tablespaces TABLESPC4K and INDX)
8KB   (tablespaces DRSYS, TABLESPC8K, PROJECTS,and USERS)
16KB (tablespace TOOLS)




Enjoy     :-) 

Choosing the Proper Database Block Size

Choice  of  an  appropriate  Block size for a database  is  often  one of  the  most  important  decisions  that can  affect  the  performance of  the  system. Choosing  the  proper  block size requires a very  good  understanding of the application environment .

For example , in a particular scenario , When the 16k instance runs an 850,000 row update (no where clause), it finishes in 45 minutes.  When the 4k instance runs an 850,000 row update (no where clause), it finishes in 2.2 minutes. The change in block size caused the job to run TWENTY TIMES FASTER. So choosing a proper data block size is very necessary task

The proper choice of database block size depends to a large extent on the type of data involved and the application. The following guidelines can help in this process:

1.) Operating system block size :   Good performance can be achieved by ensuring that the Oracle block size is equal to or a multiple of the operating system block size. If this is not the case, the OS may end up performing extra reads and writes during the processing of Oracle blocks, resulting in wasted CPU cycles.

2.) Size of buffer cache used :   Larger database block size means that you are using up more memory for the same number of db_bock_size . This also means that more rows are cached. If our rows are small and we use a large block size, a block fetch will result in a lot of rows being fetched (and we may not be interested in all of them). The end result is that the OS is doing more work to fetch things that we don't need. On the other hand, if the row length is large, a large block size may prevent chaining.

3.) Balancing of index branches :  Large Oracle block sizes can result in better index balancing, as there are more rows in each branch. A bigger block size means more space for key storage in the branch nodes of B-tree indexes, which reduces index height and improves the performance of indexed queries.

4.) Datafile size limits :  The  block size leads to the max size of the database and  ultimately result into database size.Below the max size of datafiles based on the database block size.

db_block_size                  Maximum Datafile Size

2kb                                         8G

4kb                                         16G

8kb                                         32G

16kb                                       64G

32 kb                                      128G

Beyond this, i found a very good post by  Charles Hooper  about the optimal size of the data block size.  

Enjoy     :-) 

Estimating & Projecting the size of a table in Oracle

There are various method of estimating and projecting the size of a table . The best way to estimate database size is to load some sample data into our tables and then calculate statistics on those tables. Query DBA_TABLES for the AVG_ROW_LEN to get an idea of the average number of bytes for each row. We can use Tom Kyte’s (asktom.com) show_space code to help us with the #of blocks evaluation or just use a plain simple technique shown below.  Many people just use the average rowlength (avg_row_len column) in order to as certain the size after doing a CTAS (Create Table AS)…however, that is not accurate as we will show below:

Example : 

SQL> create table test as select * from all_objects ;
Table created.

SQL>Exec dbms_stats.gather_table_stats(user,'TEST') ;

SQL>select num_rows ,blocks,empty_blocks.avg_row_len from user_tables where table_name='TEST';
NUM_ROWS        BLOCKS       EMPTY_BLOCKS        AVG_ROW_LEN
----------              ------------     -------------------         -------------------
183546               2694                    0                           98

So, the average rowlength is being reported as 98.  

The “right” and the only sure-shot way of calculating the size would be to calculate using these steps:


1.) We check on the existing schemas and that will give us the tables filled with representative data (say expected volume for one of the schemas for a large table is say 1 million rows – we check the # of blocks for n number of rows).
2.) Collect DBMS_STATS.
3.) Check the number of blocks.
4.) Then multiply by the multiplying factor i.e. if we estimated for say 1% of the actual requirement, multiply by 100 .
Example (Same table from above):

SQL> select extent_id, bytes, blocks  from user_extents   where segment_name = ‘TEST’  
                    and segment_type = ‘TABLE’ ;
EXTENT_ID  BYTES       BLOCKS
-------------   --------      -----------
       330      65536          8
       331      65536          8
       332      65536          8
       333      65536          8
       334      65536          8
                  -----
                  -----
       667      65536          8
       ------                    --------
Total (sumof block )      2696

SQL> select blocks, empty_blocks, avg_space, num_freelist_blocks  from user_tables  
                where table_name = ‘TEST’ ;
BLOCKS       EMPTY_BLOCKS     AVG_SPACE       NUM_FREELIST_BLOCKS
--------         ------------------       ---------------      ------------------------------
2694                   0                           0                                 0
So :
1.)  We have 2696 blocks allocated to the table TEST.
2.)  0 blocks are empty (of course – in this example that is bound to happen – not in reality though).
.3)  2694 blocks contain data (the other 2 are used by the system).
4.)  Average of 0k is free on each block used.
So,
1.) Our table TEST consumes 2694 blocks of storage in total for 183546 records.
2.) Out of this : 2694 * 8k blocksize – (2694 * 0k free) = 21552k is used for our data.

The calculation from the average row-length would have yielded : 183546 * 98 = ~17566k (see the difference ?).

Also, now that we have the calculation, if the actual table TEST needs to be sized for say 10 million records, then we use the multiplying factor for it :

183546 records take —> 21552 k

10 million will take —-> (21552k * 10 million) / 183546

That way, we will be assured that the data calculations are correct.  Likewise for the indexes.  Oracle Guru Tom Kyte has a lot of very good examples on his site that we should read before we embark on our sizing calculators for an Oracle Schema.

Reference :  http://decipherinfosys.wordpress.com

Enjoy    :-)