Oracle Schema Creation

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes.

  
$ sqlplus /nolog

SQL> connect / as sysdba
Connected

Creating a New Schema
SQL> create user ctechz identified by ctehz11;
User created

Grant Permissions to user
SQL> grant create session, create table, create trigger, create sequence, create procedure, create view to ctechz;
Grant succeeded

Connecting to Schema

SQL> connect ctechz/ctechz11;

Connected 

Granting table space to the schema
SQL> grant unlimited tablespace to ctechz;
Grant succeeded

Oracle DB startup Scripts

                             

Starting oracle listener

$ lsnrctl start

Start the database

$ dbstart

 Starting oracle Enterprise manager
$ emctl start dbconsole

 Shutting down the database
$ dbshut

 Stopping the listener
$ lsnrctl stop

 Stopping oracle Enterprise manager
$ emctl stop dbconsole

 Starts sqlplus without logging in to a database
# su ­ oracle
$ sqlplus /nolog

 Logging into default oracle db
$ sqlplus system/oracle11@pfdb

The automatic startup and shutdown of the Oracle database can be achieved with the files dbstart and dbshut both provided by Oracle. These files rely on the existance of the file /etc/oratab to work

$ cat /etc/oratab
orcl:/u01/app/oracle/product/10.2.0/db_1:Y
 
 The first field is the name of my database (orcl), the second one is my home directory
(/u01/app/oracle/product/10.2.0/db_1), and the third indicates to the dbstart utility whether the database should, or should not be brough up at the system boot time with "Y" or "N" parameters respectively. As root user, we'll change last field of this line from "N" to "Y" to let dbstart utility start this database when it runs.

How to install Oracle DB Client

Installing Oracle database Client to access DB

Either you can use sqldeveloper as oracle client or you can execute it from the command line using # sqlplus

 We can access oracle server graphically by using a package called sqldeveloper. 

sqldeveloper-2.1.1.64.45-1.noarch.rpm

# rpm -ivh sqldeveloper-2.1.1.64.45-1.noarch.rpm 

if the front end not getting install the jdk package and try...

# or install jdk on root and put the package in user account and run the ./sqldeveloper.sh from user account. if it shows any permission problem give 777 permission for .sh file and install and tell the jdk installed path while asking. Also included path in /etc/profile and if needed add it in .bashrc file

From the console we can use a Client called sqlplus

$ sqlplus /nolog  ----- It starts sqlplus without logging in to a database.

$ connect / as sysdba   ------ Connecting as an administrative user,No password is required. This is equivalent to the desupported "connect internal" method.

$ sqlplus -s /nolog ----------- Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands. 

 $ SQLPLUS [ { username[/passward][@connect_identifier] | / } [ AS { SYSDBA | SYSOPER } ] | /NOLOG ]

  /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora ( In this path we are defining the connect_identifier, Using connect

using identifier we can access the db and tables of another system. To do so define the hostname,port,connect_identifier etc in tnsnames.ora file(copy the contents and edit the files)

$ sqlplus system/oracle@pfdb (connect_identifier)

we can connect to remote machine using this OR We can give the remote machine details in

tnsnames.ora, else connect using ipaddress and port/ We can give any connect_identifier name.

$ sqlplus system/oracle@orcl ------ Give the connection details in tnsnames.ora

Oracle Startpup script

Oracle Startup script used for Automatic start up of oracle db after rebooting the machine. I have already explained how to install oracle 11g in previous posts and just note how to add a startup script with respect to that settings

For automatically restart the oracle after system reboot  create a file called oracle ( any name ) in /etc/init.d/ and give executable permission and paste the code into it. And make a link to /etc/rc5.d/  for that

# cd /etc/rc5.d
# ll
# ln -s ../init.d/oracle S86oracle

# vim /etc/init.d/oracle ----- and paste the below script into it. and then make the above link into it. 

# Oracle Startpup script

ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
ORA_OWNER=oracle
  case "$1" in
      'start') #If the system is starting, then...
      echo -e "\e[34mStarting ORACLE...\e[0m"

#Start the listener
     su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl start 2>&1> /dev/null"

#Start the database

     su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbstart 2>&1 > /dev/null"
#  su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole 2>&1 > /dev/null" #Start OEM
  ;;
 'stop')   #If the system stops, that is we're shutting down our OS, then...
  echo -e "\e[34mStopping ORACLE...\e[0m"
#  su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole 2>&1 > /dev/null"
  su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbshut 2>&1 > /dev/null"
  su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop 2>&1 > /dev/null"
  ;;
esac

OR

 # Oracle Startpup script

ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
 ORA_OWNER=oracle
 case "$1" in
  'start') #If the system is starting, then...
   echo -e "e[34mStarting ORACLE...e[0m"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl start 2>&1> /dev/null"
#Start the listener
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbstart 2>&1 > /dev/null"
#Start the database
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole 2>&1 >
/dev/null" #Start
OEM
   ;;
  'stop') #If the system is stops, that is we're shutting down our OS,
then...
   echo Oracle startup script
 ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
 ORA_OWNER=oracle
 case "$1" in
  'start') #If the system is starting, then...
   echo -e "e[34mStarting ORACLE...e[0m"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl start 2>&1> /dev/null"
#Start the listener
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbstart 2>&1 > /dev/null"
#Start the database
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole 2>&1 >
/dev/null" #Start
OEM
   ;;
  'stop') #If the system is stops, that is we're shutting down our OS,
then...
   echo -e "e[34mStopping ORACLE...e[0m"
su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole 2>&1 >
/dev/null"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbshut 2>&1 > /dev/null"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop 2>&1 > /dev/null"
   ;;
 esac
-e "e[34mStopping ORACLE...e[0m"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole 2>&1 >
/dev/null"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbshut 2>&1 > /dev/null"
   su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop 2>&1 > /dev/null"
   ;;
 esac

Change the path according to your path

Save the file as oracle in /etc/init.d directory and make it executable. This script starts the listener, database and enterprise manager.

# vim /etc/init.d/oracle
# chmod 755 /etc/init.d/oracle

How to setup Oracle 11g DB

To install Oracle 11g DB,  just install the following packages at the startup and continue

# yum install binutils gcc glibc glibc-devel cpp make setarch pdksh libaio libaio-devel compat-gcc-34 compat-gcc-34-c compat-libstdc-296 compat-libstdc-33 sysstat -y

Creating groups and user for Oracle Installation:

# groupadd oinstall
# groupadd dba
# useradd -m -g oinstall -G dba -d /home/oracle -s /bin/bash -c  "Oracle Software Owner" oracle
# passwd oracle

Installing Oracle DB:

# mv 11g package to /home/oracle
# cd /home/oracle
# chown oracle *
# cd

Now as oracle user execute the installation script

# xhost +
# su - oracle
unzip both the packages, after unzip it, a folder called "database" is created.

# cd database
# ./runInstaller

a graphical window will appear, Let me tell what all options to select in each window

1st window:-

Next

2nd Window:-

select Create and configure a database

3rd Window:-

select Server Class

4th Window:-

Single Instance database Installation

5th Window:-

Advanced Install

6th Window:-

select the language to install(english)

7th Window:-

enterprise edition

8th Window:-

Oracle Base:-
/home/oracle/app/oracle

Software Location:-
/home/oracle/app/oracle/product/11.2.0/dbhome_1

9th Window:-

Inventory Directory:-
/home/oracle/app/oraInventory

Inventory Group name:-
oinstall

10th Window:-

General Purpose

11th Window:-

Global Database Name:-
orcl

SID
orcl

12th Window:-

select all default characters

13th Window:-

default option

14th Window:-

File System: /home/oracle/app/oracle/oradata

15th Window:-

Do not enable Automated Backup

16th Window:-

use same password for all accounts: oracle

17th Window:-

DataBase Administrator group:- dba
DataBase Operator group:- oinstall

18th Window:-

if any error comes run the script and if packages error comes install those packages using yum.

 fix and check again (tab) / and install packages needed using yum

[
Setting Kernel Parameters...
fs.file-max = 65536
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_max = 1048576
net.core.rmem_max = 4194304
net.core.wmem_max = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
]

19th Window:-

finish, installation start

20th Window:-

installing

21th Window:-

close

Connecting to the Database, Now reboot server and login as oracle user. To login to our database, we run sqlplus. But before it, set Environment Variables. Add the below lines to .bash_profile file in the /home/oracle directory.

# su – oracle  [ login as oracle user ]

Then export Oracle Path:- ( for 11g )

vim .bash_profile
vim /etc/profile

Then export Oracle Path:-

export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:$PATH

This will Complete the basic steps in Oracle DB setup.

 Now you can start the database.

$ lsnrctl start

The above command starts the database listener. Below command starts the database

$ dbstart

[ /etc/oratab 

orcl:/home/oracle/oracle/product/10.2.0/db_1:Y ( change N to Y ) ]

To start the Oracle Enterprise Manager execute the below command.

$ emctl start dbconsole

Now open a new terminal and access the newly created database using
sqlplus as oracle user.

[ # su – oracle ]

$ sqlplus system/oracle@orcl

NOTE : oracle is the password set for the newly created database during installation