Some of the init.ora parameters are really critical in the performance of a database. Here we are discussing about the top 10 init.ora parameters. These are the important parameters and should be taken care while creating or working on databases. DB_NAME DB_DOMAIN CONTROL_FILES DB_BLOCK_SIZE DB_BLOCK_BUFFERS LOG_BUFFER SHARED_POOL_SIZE SORT_AREA_SIZE PROCESSES ROLLBACK_SEGMENTS
DB_NAME : This parameter specifies the local name of the database. This is an optional parameter but Oracle recommends to set this parameter before you create the database. it must be set as text string up to eight characters. The value which is provided to this parameter will be recorded in control file, datafiles and redo log files during the database creation. Default value for this parameter is NULL.
For Example: DB_NAME= prod
prod is the name of the database.
DB_DOMAIN: DB_DOMAIN Specifies the logical location ( Domain) with in the network. The combination of DB_NAME and DB_DOMAIN parameters should be unique with in the network. This parameter is important when you are going to use distributed database system.
For Example:
DB_DOMAIN=test.com
test.com is the domain name and global database name can recognize by prod.test.com where prod is the database name.
CONTROL_FILES: This parameter specifies the name of the control files. when database is created, Oracle creates control file according to the path which specifies in the init.ora file. If no value is assigned to this parameter then Oracle create this parameter in the default location. Eight different files can be assigned to this parameter but it is recommended to have three different control files on different disks.
DB_BLOCK_SIZE: This parameter specifies the data block size. The size of the block should be multiple of the block size of OS. For example it can be 2k, 4k up to 32k in Oracle 8i but the maximum value is OS- Dependent.
DB_BLOCK_BUFFERS: This is very critical performance parameter that determines the number of buffers in the buffer cache in the System Global Area. Importance of this parameter is more because , data block size cannot be changed after database is created. In that case this parameter can be used to tune the size of data buffer. Buffer cache size can be calculated by the following formula.
Data buffer Cache size=DB_BLOCK_SIZE x DB_BLOCK_BUFFERS
LOG_BUFFER: This parameter specifies the size of redo log buffer. It is buffer for uncommitted transactions in the memory. The default setting for this parameter is four times the maximum data block size for the host Operating System.
SHARED_POOL_SIZE: This parameter specifies the size of shared memory for the instance. This is important parameter for memory tuning and can be altered after database creation
SORT_AREA_SIZE: This specifies the size of memory used for sorting and merging of data. This represents the area that can be used by each user process to perform sorting and merging of data.
PROCESSES: This parameter determines the maximum number of OS process that can be connected to database at the same time. The value for this parameter must include 5 for background process. i.e. if you want to have 20 users then you must have it 25.
ROLLBACK_SEGMENTS: This parameter specifies the list of rollback segments for an Oracle Instance. Performance is also gets affected by the size of Rollback Segment. This should be larger enough to hold the rollback entries of the transaction
How to find Oracle Hidden Parameters ?
Oracle has many hidden parameters. You will not find them in V$PARAMETER or see them with SHOW PARAMETERS command as these are hidden. All these parameter start with _ (Underscore). Like _system_trig_enabled.
These parameters are undocumented. You won t find them in Oracle documentation. These parameters are for � specific purpose only. Some of them are OS specific and used in unusual recovery situations. Some are also used to enable and disable new feature. You should be very much careful while using them. Please check with Oracle Support before using them.
Here is a query that you can use to find these parameters.
SELECT X.KSPPINM NAME, DECODE(BITAND(KSPPIFLG/256, 1), 1, 'TRUE', 'FALSE') SESMOD,
DECODE( BITAND(KSPPIFLG/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE' ) SYSMOD, KSPPDESC DESCRIPTION FROM SYS.X_$KSPPI X WHERE X.INST_ID = USERENV('INSTANCE') AND
TRANSLATE(KSPPINM,'_','#') LIKE '#%' ORDER BY 1 ; < /P > STEPS TO CREATE DATABASE MANUALLY ON LINUX
This article shows you steps to create a database manually on Linux.
Step 1:
First create all the necessary directories. Followings are my directories: testdb1]$ ls
admin backup archive admin]$ ls
Step 2:
Next prepare the database creation script. Following is my script "testdb1.sql" CREATE DATABASE "testdb1"
MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 292 LOGFILE
GROUP 1 '/d02/monish/testdb1/redo1.log' SIZE 10M, GROUP 2 '/d02/monish/testdb1/redo2.log' SIZE 10M, GROUP 3 '/d02/monish/testdb1/redo3.log' SIZE 10M DATAFILE
'/d02/monish/testdb1/system.dbf' size 100m, '/d02/monish/testdb1/usr04.dbf' size 10m
sysaux datafile '/d02/monish/testdb1/sysaux.dbf' size 100m undo tablespace undotbs
datafile '/d02/monish/testdb1/undo.dbf' size 50m CHARACTER SET US7ASCII
; Step 3:
Prepare the init file. Like this one [inittestdb1.ora] *.audit_file_dest='/d02/monish/testdb1/admin/adump' *.background_dump_dest='/d02/monish/testdb1/admin/bdump' *.compatible='10.2.0.3.0' *.control_files='/d02/monish/testdb1/control01.ctl', '/d02/monish/testdb1/control02.ctl','/d02/monish/testdb1/control03.ctl' *.core_dump_dest='/d02/monish/testdb1/admin/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='testdb1' *.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=/d02/monish/testdb1/archive' *.log_archive_format='%t_%s_%r.dbf' *.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='/d02/monish/testdb1/admin/udump' *.db_recovery_file_dest='/d02/monish/testdb1/backup' *.db_recovery_file_dest_size=2147483648 Step 4:
Now perform the following steps: $ export ORACLE_SID=testdb1
$ 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 pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittestdb1.ora nomount 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> @testdb1.sql
Database created. Step 5:
So your database is create. Now just run the catalog.sql and catproc.sql scripts. You will find the in $ cd $ORACLE_HOME/rdbms/admin
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql SQL> select name from v$database;
NAME --- TESTDB1