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     :-)