Pages

Monday, August 9, 2010

create manual database

step 1
create folder on any drive (d:\mydb) as described
1.adump
2.bdump
3.cdump
4.udump
5.flash_recovery_area

6.pfile
7.redolog
8.datafile

now create a pfile (or get it from d:\oracle\product\10.2.0\admin\orcl\pfile\init.ora)
copy this pfile into d:\mydb\pfile\init.ora
********************************************
pfile must  contain  these parameters
******************************************


##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Archive
###########################################
log_archive_format=ARC%S_%R.%T
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=mydb
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=d:\mydb\bdump##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Archive
###########################################
log_archive_format=ARC%S_%R.%T
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=orcl
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=d:\mydb\bdump
core_dump_dest=d:\mydb\cdump
user_dump_dest=d:\mydb\udump
###########################################
# File Configuration
###########################################
control_files=("d:\mydb\controlfile\control01.ctl", "d:\mydb\controlfile\control02.ctl", "d:\mydb\controlfile\control03.ctl")
db_recovery_file_dest=d:\mydb\flash_recovery_area
db_recovery_file_dest_size=4G
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
###########################################
# Network Registration
###########################################
local_listener=LISTENER_ORCL
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# SGA Memory
###########################################
sga_target=609222656
###########################################
# Security and Auditing
###########################################
audit_file_dest=d:\mydb\adump
remote_login_passwordfile=SHARED -- for remote authentication
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=202375168
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1
***********************************
now save this parameter file as d:\mydb\pfile\init.ora


 
start sqlplus


 1.shutdown immediate
2.startup nomount pfile=d:\mydb\pfile\init.ora


write and execute following create database command.


create database db_name
user sys    identified by password_sys
user system identified by password_system
character set          we8iso8859p1
national character set utf8
set default smallfile tablespace
-- database logging clauses
logfile  

group 1 ('d:\mydb\redolog\redo_1a.log', 'd:\mydb\redolog\redo_1b.log') size 50m,
group 2 ('d:\mydb\redolog\redo_2a.log', 'd:\mydb\redolog\redo_2b.log') size 50m


archivelog --- for archive mode
force logging
-- Tablespace Clauses
extent management local
datafile 'd:\mydb\datafile\system01.dbf' size 500M [reuse] autoextend off
sysaux datafile 'd:\mydb\datafile\sysaux01.dbf' size 300M [reuse] autoextend off
default tablespace users datafile 'd:\mydb\datafile\data01.dbf' size 100M autoextend on extent management local
default temporary tablespace temp tempfile 'd:\mydb\datafile\temp01.dbf' size 20M
undo tablespace undotbs1 datafile 'd:\mydb\datafile\undo.dbf' size 50M

after database creation run following scripts located at
oracle_home\rdbms\admin
1.catalog.sql --- for datadictionary
2.catproc.sql --- for PLSQl Packages etc

to create spfile
sql> create spfile from pfile='d:\mydb\pfile\init.ora'

to create database service

execute following command on cmd
oradim -new -sid mydb

Restrict User Access


 
suppose you have five machines and five users ( one user per machine)
Note: these users are database users...
e.g

User name
Machine name
User1
WORKGROUP\PC-01
User2
WORKGROUP\PC-02
User3
WORKGROUP\PC-03
User4
WORKGROUP\PC-04
User5
WORKGROUP\PC-05


So according to above table ye create a table in our database as sys user

Create table user_record
(user_name               varchar2(25),
Machine_name         varhcar2(30));

now insert user info ….

Insert into user_record (user_name,machine_name)
Values (‘USER1’,’ WORKGROUP\PC-01’);

Insert into user_record (user_name,machine_name)
Values (‘USER2’,’ WORKGROUP\PC-02’);

Insert into user_record (user_name,machine_name)
Values (‘USER3’,’ WORKGROUP\PC-03’);

Insert into user_record (user_name,machine_name)
Values (‘USER4’,’ WORKGROUP\PC-04’);

Insert into user_record (user_name,machine_name)
Values (‘USER4’,’ WORKGROUP\PC-04’);

Insert into user_record (user_name,machine_name)
Values (‘USER5’,’ WORKGROUP\PC-05’);

COMMIT;


NOW CREATE AFTER LOGON ON DATABASE TRIGGER AS SYS..

 *******************************************************************************************************
CREATE OR REPLACE TRIGGER USER_CHECK

  AFTER LOGON ON DATABASE

  DECLARE
 U_NAME       VARCHAR2(20);
  M_NAME     VARCHAR2(20);

BEGIN
 SELECT USER_NAME, MACHINE_NAME INTO U_NAME,M_NAME
 FROM USER_RECORD
 WHERE (USER_NAME, MACHINE_NAME) IN
    (SELECT USERNAME, MACHINE FROM V$SESSION WHERE SID=(SELECT DISTINCT SID FROM
V$MYSTAT));

  EXCEPTION

 WHEN NO_DATA_FOUND THEN

 RAISE_APPLICATION_ERROR(-20000,’YOU ARE NOT AUTHRIZE TO LOGIN FROM THIS MACHINE’);

 END;
 *******************************************************************************************************


this will connect 


user1 from WORKGROUP\PC-01 only 
user2 from WORKGROUP\PC-02 only
user3 from WORKGROUP\PC-03 only
user4 from WORKGROUP\PC-04 only
user5 from WORKGROUP\PC-05 only



HOPE THIS WILL WORK





Friday, August 6, 2010

ORACLE 10G INSTALLATION ON ENTERPRISE LINUX 4

ORACLE 10G
INSTALLATION ON ENTERPRISE LINUX 4

STEP # 1:  SET KERNEL PARAMETERS on SYSCTL.CONF file
cd /etc
ls
gedit sysctl.conf à File open on editor mode add the following parameters

Kernel.shmall = 2097152
Kernel.shmmax = 2147483648
Kernel.shmmni = 4096
Kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Run the following command to change the kernel parameters

/sbin/sysctl –p

STEP # 2:  SET PARAMETERS on LIMITS.CONF file
Cd /etc/security
ls
gedit limits.conf à File open on editor mode add the following parameters

*          soft     nproc            2047
*          hard   nproc 16384
*          soft     nofile             1024
*          hard   nofile             65536

STEP # 3:    INSTALL REQUIRED RPM FROM LINUX CD’S 

To see which versions of these packages are installed on your system, run the following commands:
rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common

rpm –q gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio

From CD # 2
Cd /media/cdrom/Server

rpm –Uvh gcc-3*
rpm –Uvh gcc-c++-3*
rpm –Uvh libstdc++-*

From CD # 3
Cd /media/cdrom/Server

rpm –Uvh libaio-0*  à Cd 3

 STEP # 4:    Now Perform Following Steps on ROOT User

Create 2 groups:   
groupadd oinstall
groupadd dba

Create a user and assign the above groups to user
useradd –g oinstall –G dba oracle
passwd oracle

Create directories using mkdir command
mkdir –p /u01/app/oracle

Change the ownership of above directories and allow permissions
chown  –R oracle:oinstall  /u01/app
chmod  –R 775  /u01/app

  STEP # 5:              Login As Oracle User
Gedit .bash_profile

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ORCL; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/ jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi    fi

logout from Oracle user ….

  STEP # 8:              Login As Root User  

create folder @ /u01/app named “10g”
copy all files from cdrom to /u01/app/10g

chmod –R 775 /u01/app/10g
now login as oracle user and
start setup
e.g
ls cd /u01/app/10g
./runInstaller 

Install Oracle 10g Release 2 on Redhat 5 Enterprise Edition

ORACLE 10G
INSTALLATION ON LINUX REDHAT 5

LOGIN AS ROOT USER

STEP # 1:  SET KERNEL PARAMETERS on SYSCTL.CONF file

type in Terminal 
cd /etc
ls
gedit sysctl.conf à File open on editor mode add the following parameters

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144







  •  fs.file-max sets the maximum number of open files that can be handled by the Linux kernel.
  • kernel.shmall determines the total amount of shared memory to be allocated in pages.
  • kernel.shmmax controls the maximum amount of memory to be allocated for shared memory which.
  • kernel.shmmni defines the maximum number of segments system-wide.
  • net.core.rmem_default and net.core.rmem_max define the default and maximum read buffer queue for network operations (1 MB in this example)
  • net.core.wmem_default and net.core.wmem_max define the default and maximum write buffer queue for network operations (256 KB in this example)
  • net.ipv4.ip_local_port_range tells the kernel the port ranges that will be used for outbound connections.
  • kernel.sem has four parameters:
  • SEMMSL – semaphores per array
  • SEMMNS – max semaphores system-wide (SEMMNI*SEMMSL)
  • SEMOPM – max operations per semop call
  • SEMMNI – max number of semaphore arrays


Run the following command in Terminal  to change the kernel parameters

/sbin/sysctl –p

STEP # 2:  SET PARAMETERS on LIMITS.CONF file

Cd /etc/security
ls
gedit limits.conf à File open on editor mode add the following parameters

*          soft     nproc            2047
*          hard   nproc 16384
*          soft     nofile             1024
*          hard   nofile             65536


STEP # 3:    ADD PARAMETER ON LOGIN file
cd /etc/pam.d
ls
gedit login à File open on editor mode add the parameters

session         required       pam_limits.so

STEP # 4:    INSTALL REQUIRED RPM FROM LINUX CD’S 

From CD # 1
Cd /media/cdrom/Server

rpm –Uvh setarch-*
rpm –Uvh make-3*
rpm –Uvh libaio-0*
rpm –Uvh --force tcl-*
rpm –Uvh glibc-*

From CD # 2
Cd /media/cdrom/Server

rpm –Uvh libXp-1*
rpm –Uvh compat-libstdc++-33*
rpm –Uvh compat-libf2c-34-*
rpm –Uvh compat-gcc-34-*
rpm –Uvh compat-gcc-34-c++-3*
rpm –Uvh compat-libstdc++-296*
rpm –Uvh gcc-4*

From CD # 3
Cd /media/cdrom/Server

rpm –Uvh compat-db-4*
rpm –Uvh openmotif-2*

 STEP # 5:   

Create 2 groups:   
groupadd oinstall
groupadd dba

Create a user and assign the above groups to user
useradd –g oinstall –G dba oracle
passwd oracle

Create directories using mkdir command
mkdir –p /u01/app/oracle

Change the ownership of above directories and allow permissions
chown  –R oracle:oinstall  /u01/app
chmod  –R 775  /u01/app

  STEP # 6:  CHANGES ON REDHAT-RELEASE File
Cd /etc
mv redhat-release redhat-release.old
gedit redhat-release
Redhat-4

  STEP # 7:              Login As Oracle User
Gedit .bash_profile

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ORCL; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Logout from Oracle user 


  STEP # 8:              Login As Root User  
create folder @ /u01/app named “10g”
copy all files from cdrom to /u01/app/10g

chmod –R 775 /u01/app/10g

now login as oracle user and run following command 

from /u01/app/10g 
e.g
cd /u01/app/10g
./runInstaller


  STEP # 9:              Run Scripts As Root User (as appear on screen)