Step by step installing Oracle 11gR2 On Redhat 6

Installing Oracle 11gR2 On RHEL6

During my practies on Oracle i decided to install Oracle 11gR2 On OEL 6.4,
So follow steps and enjoy it :)

The first Step is Download the zip of Linux on oracle site

Oracle’s Technical Network  and downloaded the two Linux x86-64 files for 11.2.0.1.0.
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

The second Step Prerequisites

Update /etc/sysctl.conf 

Open sysctl with command "vi" and scroll to the bottom write the necessaries

vi /etc/sysctl.conf


kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.ipv4.ip_local_port_range = 9000 65500
fs.file-max = 6815744
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576

Update /etc/security//limits.conf

vi /etc/security/limits.conf

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

Add users and groups

On Oracle-base you can see some others group whichs are for grid control

The following needed to be added:
groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
useradd -u 502 -g oinstall -G dba,oper oracle -p oracle

Create directories and grant permissions

I used my oow directories however you can follow differnts or set like oracle doc.

mkdir /app/oracle
mkdir /u01
chown -R oracle:oinstall /u01
chmod -R 775 /u01
chown -R oracle:oinstall /app/oracle
chmod -R 775 /app/oracle
Switch to the bkup directorywhich is i create before and setup the permissions there: which is i create before
cd /bkup
chown -R oracle:oinstall database
chmod -R 775 database

Set up the oracle user environment

su oracle
vi /home/oracle/.bash_profile


export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=************
export ORACLE_UNQNAME=********
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=********
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

AND VERIFY BASH PROFILE WITH FOLLOWING COMMAND

. .bash_profile

Verify and/or install specific dependencies

Oracle has a list of dependencies which have to be verified.  yum are noted with (*):
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3 (*)
compat-libstdc++-33.3.2.3 (32 bit) (*)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125 (*)
gcc-4.1.2
gcc-c++-4.1.2 (*)
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214 (*)
libaio-0.3.106
libaio-0.3.106 (32 bit) (*)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit) (*)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libgomp-4.1.2
libstdc++-4.1.2
libstdc++-4.1.2 (32 bit)
libstdc++-devel-4.1.2
make-3.81
numactl-devel-0.9.8.i386 (*)
sysstat-7.0.2 (*)

Then proceded with the other dependencies:
yum install unixODBC.x86_64
yum install unixODBC-devel.x86_64
yum install unixODBC.i686
yum install unixODBC-devel.i686

Create a recovery area for backups

I create a recovery area (see the screen shots below) on my backup drive with the following:
cd /bkup
mkdir -p oracle/recovery_area
chown -R oracle:oinstall oracle

The third Step is insall oracle

cd /bkup/database
./runInstaller
The following are screen shots of all the steps I’ve taken with the graphical installer:

If you wanna to take support from Oracle you have to give your mail address which is you already registered on Oracle Support site.

 so I don't fill down Oracle support for this.

 

At this step we see tree installation options so i wanna create with a configuring database, because i would like to show you how to auto create a database after installation, Also i will show you next post Creating A database using DBCA and manually creating a database.

 Here we go, if you are using desktop choose the first, But i'm installing on REDHAT 6 and 40gb and 2gb RAM on Virtualbox :)

 

So, if you have RAC system you can follow the second option, But im just installing a single instances.

Typical is the basic optison so i rather go on Advanced install

 Choose your own language

 

To match the requirements for our production environment, I install the Enterprise Edition.

 

These steps are show you Oracle base path whichs are you wrote in Bash_profile..

This also a directory for files is OraInventory

If you are on Datawarehouse so go on that to create a database.but i used General

I'm always on name ORCL

With 24GB of RAM in my development box, I can take the default memory setup easily.

This is where I choose the UTF8 character set

Here we go again, the important thing is security whatever i will ignore :)

So i dont need any database with sample schemas but i recomend to you use it :)

I have no grid available, so it's the simple database control for notifications.

Our file is system on OS is /u01 

Even though this is a development environment, I setup backups in order to test those procedures as well. This is where I point to the recovery area I setup above.

 Oracle is using, my weak password but it is a strong password none the less (according to many other password checkers).

I use the groups suggested as setup earlier.

 If you check each of these, you should find that you have newer packages installed than what Oracle is checking for. Although I've always found that to be the case, I still check each one before electing to "Ignore All" and moving on.

Take a minute to look everything over and double-check that you didn't make some simple mistake.

Make note of the URL for the Enterprise Manager and move on…..almost done!

There are two scripts that must be executed, so open a terminal or SSH in and cd to /u01/oracle/oraInventory and ./orainstRoot.sh, then cd /opt/oracle/product/11.2.0/db_1 and ./root.sh. Once these have been executed, click Ok.

Congratulations! Time to "get back to work!" :) 

Oracle Data Pump (expdp and impdp)

At this Article I will present Datapump (Export/ Import) tools.

Datapump help us to migrate Oracle data for copying to a new database after an upgrade or creating a backup.

Lets start step by step example

First of all the user have some privileges to use datapump tools.

Now create a backup location

SQL> create or replace directory dp_vey_dir as '/u01/backups/datapump';

Give privilege

SQL> grant read, write on directory dp_vey_dir to veysi;

Now use the tools

Full Export

For full export the user must have

EXP_FULL_DATABASE privileges

$ expdp user/password DIRECTORY=backup DUMPFILE=full_backup.dmp FULL=y LOGFILE=full_export.log

For example

$ expdp veysi/veysi DIRECTORY=dp_vey_dir DUMPFILE=Full_vey1.dmp FULL=y LOGFILE=full_vey1.log

Schema Export

$ expdp user /password DIRECTORY=backup DUMPFILE=schema_backup.dmp SCHEMAS=schema1,schema2

For example:

$  expdp veysi/veysi schemas=veysi DIRECTORY=dp_vey_dir DUMPFILE=Vey1.dmp LOGFILE=expdpvey1.log

Table Export

  

For example

$  expdp veysi/veysi tables=tbl_v_students DIRECTORY=dp_vey_dir DUMPFILE=Vey.dmp LOGFILE=expdpvey.log

Full Import

After Exporting we need to import. For that the user need IMP_FULL_DATABASE privileges.

$ impdp user/password DIRECTORY=backup DUMPFILE=full_backup.dmp FULL=y LOGFILE=full_import.log

For example

$ impdp veysi/veysi DIRECTORY=dp_vey_dir DUMPFILE=Full_vey1.dmp FULL=y LOGFILE=full_vey1.log

Schema Import

$  impdp veysi/veysi schemas=veysi DIRECTORY=dp_vey_dir DUMPFILE=Vey1.dmp LOGFILE=impdpvey1.log

Table Import

$  impdp veysi/veysi tables=v_tbl_student DIRECTORY=dp_vey_dir DUMPFILE=Vey.dmp LOGFILE=impdpvey.log

Oracle dblink

Using Dblink to connect to other database

Dblink, is bridge to connected databases.

Creating:

CREATE [PUBLIC] [PRIVATE][SHARED]DATABASE LINK dblink_name

Connect to target_user_name,

İdentified by Target_user_password

USING ’tns_name’;

For example:

CREATE SHARED PUBLIC DATABASE LINK veysi_lnk
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY sec_usr IDENTIFIED BY sec_pass
USING 'DB_ORCL';

If we want to create dblink we have to have some privileges such as:

  CREATE DATABASE LINK ,  CREATE PUBLIC DATABASE LINK

Also we can create dblink by Current user . Current user, should be the same with Remote database user and dblink user. That’s why user should be global

CREATE PUBLIC DATABASE LINK remote_connect

CONNECT TO CURRENT_USER

USING ‘DB_ORCL’;

For example:

CREATE PRIVATE DATABASE LINK remote_connect

CONNECT  TO veysi IDENTIFIED BY veysi

USING ‘DB_ORCL’;

Check your tnsname.ora :

DB_ORCL = (DESCRIPTION = (ADDRESS =
             (PROTOCOL = TCP) (Host = dborcl.localdomain) (Port = 1521))
             (CONNECT_DATA = (SID = db_orcl) (GLOBAL_NAME = db_orcl.localdomain.com)
             (SERVER = DEDICATED)))

When creating Dblink is requisite GLOBAL_NAME

So check your global name

SQL>SELECT GLOBAL_NAME FROM GLOBAL_NAME;

GLOBAL_NAME
-----------
db_orcl.localdomain

Now creating by global_name dblink;

CREATE PUBLIC DATABASE LINK db_orcl.localdomain

CONNECT  TO VEYSI IDENTIFIED BY  VEYSI

USING ‘DB_ORCL’;

Creating a view;

CREATE VIEW vw_emp_orcl

AS

SELECT * FROM employees@db_orcl.localdomain.com;

Or creating a table;

CREATE TABLE tbl_vey_emp_orcl

AS

SELECT * FROM employees@db_orcl.localdomain.com;

Updating

UPDATE employees@remote_connect

   SET salary=salary*1.1

   WHERE last_name = 'AYSE';

INSERTING

Kullanımı:

INSERT INTO employees@remote_connect(salary)

VALUES(salary*1.1);

   WHERE last_name = 'AYSE';

DELETING 

DELETE FROM employees@remote_connect ;

   WHERE employee_id = 150;

SELECT:

SELECT * from  employees@remote_connect ;

Looking at DBLINKS

SELECT * FROM all_db_links ;

or

SELECT * FROM dba_db_links; 

Dropping

DROP DATABASE LINK dblink_name

To close Dblink

ALTER SESSION CLOSE DATABASE LINK remote_connect;

Oracle Rename User

This article present easy way to change/rename User name and password.

Select FOR your users

SQL> select user#,name from user$;

Update user name

SQL>update user$ set name=’TESTUSER’ where name=’VEYSI’;

SQL>commit;

Close your database

SQL>shutdown immediate;

SQL>startup;

SQL> alter user TESTUSER identified by TESTUSER;

Oracle rename data file tips

This article present easy way to change/rename datafile_name or Tablespace name_name.

First select your dba_datafile_name and choose a file for renaming.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/test01.dbf

Now close your database;

SQL>shutdown immediate;

Now change the name of file

linux> mv /u01/app/oracle/oradata/orcl/test01.dbf ' '/u01/app/oracle/oradata/orcl/veysi01.dbf

Mount your database

SQL> Startup  mount;

Rename datafile

SQL>ALTER DATABASE   
RENAME file      
   '/u01/app/oracle/oradata/orcl/test01.dbf' 
TO 
   '/u01/app/oracle/oradata/veysi01.dbf'

sql>alter database open;

Renaming Tablespace

ALTER TABLESPACE TEST RENAME TO INTERBASE;

Travelling America USA

At my second degree i decided to go to USA summer vacation, Actually as a student it was a little difficulty Finally i got Visa and My first airport station  was Parish and i was More excited in there i just felt like I'm flying with plane :) At last i saw my self at America, i got many experiences there and a lot of pictures.

New york Time square

New Orleans Jazz Festival

Washington DC

BROOKLYN BRIDGE

From Empire City New york

Looking New york From top on empire city

Niagara Falls

Apple Store internview with Steve Jobs :)

I met with Iroman :) 

Create Oracle database listener Using Netca and manually

This article presents creating a simple Oracle Listener service. DBA's can create Listener manually however Oracle Net Configuration Assistant provide us simple way.

First Method Manually Create the listener

Manually create the listener.ora file and start the listener from command line.

LISTENER_TEST =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST =orcl.localdomain )(PORT = 1522))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

    )

  )

ADR_BASE_LISTENER_ORACLEDB1 = /u01/app/oracle

[oracle@orcl admin]$ lsnrctl stop LISTENER_TEST

[oracle@orcl admin]$ lsnrctl status LISTENER_TEST

 Second method Start the Oracle Net Configuration Assistant (NETCA)

Choose Listener configuration And Click Next

 Here Add (to add new listener) Reconfigure(exist listener), delete(exist) Rename(exist).
Choose Add And click next

So, Give new listener Name  For example Listener_test

The important thing you must Select TCP here. Click next

The exist port is 1521 however user for different listener different port for example 1522 

 Asking for another listener to configure Choose No and click Next

Choose new listener to be started and click next

Almost its done! Click next

Click Finish

After all you we'll see the script of new listener.

AS You see there are two listener script my first and second listener.
Have a nice job! :)