Monday, July 19, 2010

Shell script to get the information from multiple Oracle databases

I will demonstrate in the following example, how to get the informaiton from the many oracle databases quickly and easly using the Unix/Linux shell script.

Example 1: You want to get the information about the version of the oracle database for many(100s of oracle databases in very quick and efficient manner using shell script, assuming that use have common user id with same passwor in all the databases i.e. scott/tiger.
You will need i. DB list: which will be input to your shell script ii. SQL file containing oracle query iii. Shell script ,iv. Log file, which is output of the execution of the shell script.
Input file 1: db_list.txt: which will contain list of the databses i.e
$cat db_list.txt

Input file 2: db_version.sql: which will contain SQL query i.e.
$cat db_version.sql
set feedback off
set line 200
set pagesize 0
set echo off
set heading off

select d.global_name, v.versionfrom global_name d, product_component_version v where product like 'Oracle Database%';

File 3: Shell - Main Korn shell script i.e
LOG=Db_version_info_`date +"%m%d%y%H%M%S"`.log
echo "Log File Name->"${PWD}${LOG} >$LOG
cat $DB_INPUT while read line do
#echo $line >>$LOG
sqlplus -s
mailto:gems_read/gems_passw0rd@$line <<"EOC">> ./$LOG
echo $?

Now lets execute the shell script, -x option is to run the script with debug option.
$>ksh -x
will generate the following output show in File4:

File 4: O/P or Log file: Will give you the list of database with oracle version, when you execute the shell script.
$>cat Db_version_info_071910175552.log
Log File Name->/balvant/exp/Db_version_info_071910175552.log

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: