Monday, June 28, 2010

Why online redo logs should never be backed up?

1. In “NO ARCHIVELOG” mode, you can take only closed backup, which is consistent and whole DB backup and upon restore does not require recovery, hence no need to have redo log backups.
2. In “ARCHIVELOG” mode, ARCH process/s are archiving the redo logs.
3. By multiplexing the redo log file(multiple log member in a redo group) on separate disk/spindle will protect from single point of failure(SPOF)
4. Main reason not to backup redo logs is to avoid the scenario during restore, where existing online logs are overwritten by the backed up redo logs, which will create issue during complete recovery of your database.

Tuesday, June 22, 2010

Oracle datafile size

What is the limitation for oracle datafile size?

It depends on 2 factors:
i. OS and ii. Database block size (DB_BLOCK_SIZE) parameter.
In 32 bit OS, You can create datafile upto 2GB to 4GB.
Following is the impact of DB_BLOCK_SIZE parameter on datafile size limitation:
For smallfile tablespace, single datafile can hold upto 2^22 or 4 MB or 4 million blocks, it means:
with DB_BLOCK_SIZE=4k, you can have max file size= 4k*4MB =16GB
with DB_BLOCK_SIZE=8k, you can have max file size= 8k*4MB =32GB
with DB_BLOCK_SIZE=16k, you can have max file size= 16k*4MB =64GB and so on..

For Bigfile tablespace(10g feature), a single data file can hold upto 2^32 or 4GB or 4 billion blocks, it means:
with DB_BLOCK_SIZE=4k, you can have max file size= 4k*4GB =16TB
with DB_BLOCK_SIZE=8k, you can have max file size= 8k*4GB =32TB
with DB_BLOCK_SIZE=16k, you can have max file size= 16k*4GB =64TB and so on..
Other Limits you can find in following Oracle Document:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits002.htm#i287915