Saturday, June 16, 2012

Relationship between sessions, processes and transaction in oracle 10g


Relationship between sessions, processes and transaction in oracle 10g

For increasing sessions parameter you should consider increasing processes and transactions parameter as well.
Below formula can be used to determine their values.
processes=x
sessions=x*1.1+5
transactions=sessions*1.1

E.g.
processes=100
sessions=115
transactions=127

Saturday, March 24, 2012

What happens in Hot Backup of oracle database


alter tablespace ts_name begin backup;

When the command is issued, at this point in time a checkpoint is performed against the target tablespace; then the datafile header is frozen, so no more updates are allowed on it (the datafile header), this is for the database to know which was the last time the tablespace had a consistent image of the data.

The datafiles with the backup in progress will still allow read/write operations just as a regular datafile, I/O activity is not frozen.

Each time a row is modified, not only the row, but the complete block is recorded to the redo log file, this will only happen the first time the block is modified, subsequent transactions on the block will only record the transaction just as normal.

During the user managed backup process the "fractured block" event may be present. Let's remember that the oracle block is the minimum IO unit, and an oracle block is made out of several OS blocks; let's assume a block size of 8K and an OS block of 512b, this will give 16 OS blocks. If during the backup process of a block there is a write operation on the block then the backup will contain a before image and an after image of the oracle block, the complete block in the backup media will be corrupt. This is normal, consistency is not guaranteed on the backup, that is why the header must be frozen to mark the point where the recovery process will have to start, and that is why oracle record a complete block image on the redo log file.

At the time the alter tablespace ts_name end backup; command is issued then the backup process is finished and the datafile header resumes its regular IO activity.

Tuesday, March 6, 2012

How to get/view nodes /server information in RAC


How to get/view nodes /server information in RAC

1) member nodes number/name
olsnodes -n

2)local node/server name
olsnodes -l

3)activates logging
olsnodes -g

RAC administration


Cluster Ready Services (CRS): is Oracle's clusterware software

CRS starts automatically when the server starts/rebooted planned/unplanned, it can be stopped this in the following conditions

Applying a patch set to CRS_HOME
O/S maintenance ( requies reboot)
Troubleshooting CRS problems

1) Starting CRS in 10gR2
$CRS_HOME/bin/crsctl start crs


2)Stopping CRS using Oracle 10g R2
$CRS_HOME/bin/crsctl stop crs

## Stopping CRS using Oracle 10g R1
srvctl stop -d database <database>
srvctl stop asm -n <node>
srvctl stop nodeapps -n <node>
/etc/init.d/init.crs stop

3)disabling/enabling CRS in 10g R2
$CRS_HOME/bin/crsctl [disable|enable] crs

## Oracle 10g R1
/etc/init.d/init.crs [disable|enable]

4)CRS status
$CRS_HOME/bin/crsctl check crs
$CRS_HOME/bin/crsctl check evmd
$CRS_HOME/bin/crsctl check cssd
$CRS_HOME/bin/crsctl check crsd
$CRS_HOME/bin/crsctl check install -wait 600

$CRS_HOME/bin/crs_stat
$CRS_HOME/bin/crs_stat -t
$CRS_HOME/bin/crs_stat -ls
$CRS_HOME/bin/crs_stat -p

Note:
-t more readable display
-ls permission listing
-p parameters


5)create profile for CRS
$CRS_HOME/bin/crs_profile

6)register/unregister application

$CRS_HOME/bin/crs_register
$CRS_HOME/bin/crs_unregister

7)Start/Stop an application
$CRS_HOME/bin/crs_start
$CRS_HOME/bin/crs_stop


8)Resource permissions
$CRS_HOME/bin/crs_getparam
$CRS_HOME/bin/crs_setparam

9)Relocate a resource
$CRS_HOME/bin/crs_relocate

Tuesday, February 21, 2012

Manual Database creation in 10g



The steps to create a database manually on Linux.


Step 1:
First of all create the  directories as per requirement as below.
linux1]$ mkdir -p /opt/scripts/
mkdir -p /opt/prod/datafiles/
mkdir -p /opt/prod/datafiles/admin/adump
mkdir -p /opt/prod/datafiles/admin/bdump
mkdir -p /opt/prod/datafiles/admin/cdump
mkdir -p /opt/prod/datafiles/archive
mkdir -p /opt/prod/datafiles/admin/udump

Step 2:
 database creation script. Location /opt/scripts/db_cr.sql
CREATE DATABASE "prod"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/prod/datafiles/redo1.log' SIZE 10M,
GROUP 2 '/opt/prod/datafiles/redo2.log' SIZE 10M,
GROUP 3 '/opt/prod/datafiles/redo3.log' SIZE 10M
DATAFILE
'/opt/prod/datafiles/system01.dbf' size 100m,
'/opt/prod/datafiles/usr04.dbf' size 10m
sysaux datafile '/opt/prod/datafiles/sysaux01.dbf' size 100m
undo tablespace undotbs
datafile '/opt/prod/datafiles/undo01.dbf' size 50m
CHARACTER SET US7ASCII
;
Step 3:
Prepare the init file.

 vi  $ORACLE_HOME/dbs/initprod.ora
*.audit_file_dest='/opt/prod/datafiles/admin/adump'
*.background_dump_dest='/opt/prod/datafiles/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/prod/datafiles/control01.ctl',
'/opt/prod/datafiles/control02.ctl','/opt/prod/datafiles/control03.ctl'
*.core_dump_dest='/opt/prod/datafiles/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/opt/prod/datafiles/archive'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/opt/prod/datafiles/admin/udump'

Step 4:
Now perform the following steps:
$ export ORACLE_SID=prod
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initprod.ora'
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1263176 bytes
Variable Size 167774648 bytes
Database Buffers 427819008 bytes
Redo Buffers 7122944 bytes
SQL> @/opt/scripts/db_cr.sql
Database created.
Step 5:
 run the catalog.sql and catproc.sql scripts.
from $ORACLE_HOME/rdbms/admin
SQL> @/?/rdbms/admin/catalog.sql
SQL> @/?/rdbms/admin/catproc.sql
SQL> select name from v$database;
NAME
---------
prod

Now create the listener and tns entries for your database

Friday, February 10, 2012

Upgrade 10204 RAC ( 2 node RAC on LINUX EL4 ) to 11g R2 RAC

Important Note : I will be publishing the Steps may be in next couple of days for installation steps of RAC on linux and then it will be upgraded to 11g R2 RAC.

My current setup is having 10204CRS and 10204 DB version. It is 2 node RAC running on EL4