Create Database

We shall create a database in 3 different ways.

1.dbca (Database Configuration Assistant )
2.CREATE DATABASE
3.Automatic creation.

1.dbca

Start Database Configuration Assistant by executing the dbca command on RHEL or OEL machine.

[oracle@localhost ~]$ dbca

• Click on next in welcome page.
• On the Operations page (Step -1), Select create database and then click on next.
• On the Database template page (Step -2), Select General purpose and Transaction Processing.
• On the Database identification Page(Step -3), Enter the database name and SID and the click on next.
• On the Management Options page(Step – 4), Accept the default settings and then click on Next.
• On the Database Credentials page(Step -5), Set the user and password for the database and then click on Next.
• On the Database File Locations page(Step -6), Select “File System” for the Storage Type, Select “Use Database File Locations from Template” for the Storage Locations and then click on Next.
• On the Recovery Configuration page(Step -7), Accept the default settings, and then click on Next.
• On the Database Content page(Step -8), Accept the default settings, and then click on Next.
• On the Initialization Parameters page(Step -9), Select the following options, and then click on Next.
• Memory tab: Select Typical and Use automatic storage management (Provide the Memory Size).
  1.Sizing tab: Accept the default settings.
  2.Character Sets tab: Accept the default settings.
  3.Connection Mode tab: Select Dedicated Server mode. 
  4.On the Database Storage page(Step -10), Accept the default settings and then click on Next.
• On the Creation Options page(Step -11), Select Create Database and then click on Finish.

A Confirmation dialog box opens, review the configuration options and then click on OK.

Click on Exit in the message box that opens when Oracle finishes creating the database.

Connect to Databases

[oracle@localhost ~]$ sqlplus oracleuser@My_New_DB 

2. CREATE DATABASE

1. Specify an Instance Identifier (SID)
2. Ensure That the Required Environment Variables Are Set
3. Choose a Database Administrator Authentication Method
4. Create the Initialization Parameter File
5. Connect to the Instance
6. Create a Server Parameter File
7. Start the Instance
8. Issue the CREATE DATABASE Statement
9. Create Additional Tablespaces
10. Run Scripts to Build Data Dictionary Views Run Scripts to Install Additional Options (Optional)
11. Back Up the Database.
12. (Optional) Enable Automatic Instance Startup

Startup and Shutdown of Oracle Database

STARTING ORACLE DATABASE

      Only DBA users can start Oracle Database Server. The default DBA users are SYS and SYSTEM.

SYS user: This user is owner of Oracle DB

SYSTEM user: This user is general Administrator.

Note:- generally SYS user only starts or stops Oracle DB.

Privileges used by SYS user for DB connection:–

Two Privileges or permissions are used by SYS user

1. sysoper (System operator)
2. It allows to perform start/stop of DB with some maintenance operations such as backup.
3. When connected with this privilege user is treated as public user.
4. sysdba (System DB administrator)

      It allows to perform all admin operation including DB startup/shutdown.

Selecting a specific Database:-

      On one Oracle DB server there will be several DB’s. All the Databases that are created on server are listed in a file “/etc/oratab”file.

Setting a specific DB Name for working:-

export ORACLE_SID=db_name

      Initial DB server machine after server boots the server machine no database is ready for working. So it is required to start the database whichever is needed.

 
Idle instance indicates Oracle DB is not available. Because it is not started.

When Oracle DB is started. It performs 3 operations

1. Oracle Instance Started
2. Database Mounted
3. Database opened

DISPLAYING ORACLE DB SERVER WORKING ENVIRONMENT

Displaying Name of the Database:-

Displaying version of Oracle Database:-

Displaying Instance Name which operates Oracle Database:-

Displaying the Name of the server machine:-

Displaying status of the Database:-

Displaying of Operating System of the server machine:-

UNDERSTANDING STAGES OF ORACLE DB STARTUP

Database startup is having 3 stages

1. Instance Started (nomount stage)
2. Database Mounted (Mount Stage)
3. Open stage

Starting oracle instance

Changing the DB stage from nomount to mount stage:-

Changing the DB state from mount to open state:-

 
 
 
 
 
CLOSING ORACLE DATABASE
 
Oracle Database can be closed in 3 steps.

1. Closing Database (files)
2. Dismounting Database (disconnects DB from Instance)
3. Instance is shutdown (Removes Instance from DB server)

Types of Database Shutdown

Oracle Database can be closed in 4 types

1. Normal Shutdown
2. Transactional Shutdown
3. Immediate Shutdown
4. Abort Shutdown

Normal Shutdown:-

      In this process Oracle database is closed in the above 3 steps. But this is performed if ever no user is connected to the database. If ever any user is connected to the Database the DB server keeps the DB in shutdown mode. So the all connected users can perform as usual database operations at the same time no new DB connections are allowed.
 
      Finally the DB is closed once all the connected users are disconnected from the Database server.

Transactional Shutdown:-

      This type of shutdown closes the DB if ever no user Transaction is going on. If ever any transaction is going on the shutdown is possible once the transaction finalized with commit or rollback.

Immediate shutdown:-

      In this irrespective of user connected to the database or transactions are going on DB server closes the DB immediately by cancelling the running operations.

Shutdown Abort:-

      In this case instance is closed without closing the database without dismounting the database.

      In this case the database is said to be crashed. But it is automatically repaired and open when the DB is started for the next time.

Introduction

      It is very large and multi user Database Management System. Oracle Database is a collection of data treated as a unit. The purpose of a database is to store data and retrieve related information. 

      Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. It is the excellent database server choice for client/computing. Oracle supports in all major operating system.

   The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

                             

      i – internet

      g – grid

      c – cloud

 

History:-

1977 Software Development Laboratories, the precursor to Oracle, is founded by Larry Ellison, Bob Miner, and Ed Oates.

1978 Oracle Version 1, written in assembly language, runs on PDP-11 under RSX, in 128K of memory. Implementation separates Oracle code and user code.Oracle V1 is never officially released.

1979 Oracle Version 2, the first commercial SQL relational database management system, is released. The company changes its name to Relational Software Inc. (RSI).

1981 RSI Started developing tools for Oracle.

1982 RSI Renamed as Oracle Corporation.

1983 Oracle Released version 3.0 (rewritten in C language and multiple Platforms).

1984/1985 Oracle Released version 4.0 ( Concurrency control , consistency etc)

1986 Oracle Released client/server relational database.

1989 Oracle Released version 6.0.

1997 Oracle Released version 8.0 (object relational).

1999 Oracle Released version 8i (internet) (Java Virtual Machine).

2000 Oracle Released Application Server.

2001 Oracle Released  9i (internet) Database server.

2003/2004  Oracle Released 10g (Grid) Database server.

2009  Oracle Released  Oracle version 11g (Grid) Database server.

2014  Oracle Released  Oracle 12c (Cloud) Database server.

Installation of 11g

Step 1 : switch user to root

Step 2: Create oracle user and set password.

Step 3: Create directories for oracle base to install oracle products.

[sree@localhost ~]$ su - root
Password: 
[root@localhost ~]# 
[root@localhost ~]# useradd oracle
[root@localhost ~]# passwd oracle
Changing password for user oracle.
New password: 
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@localhost ~]# 
[root@localhost ~]# groupadd oinstall
[root@localhost ~]# 
[root@localhost ~]# mkdir -p /u01/app/oracle
[root@localhost ~]# 
[root@localhost ~]# chown -R oracle:oinstall /u01
[root@localhost ~]# 
[root@localhost ~]# chmod -R 777 /u01

Step 4: login as oracle user and copy Oracle11g software to the desktop.

[oracle@localhost Desktop]$ cd
[oracle@localhost ~]$ 
[oracle@localhost ~]$ chmod -R 777 /home/oracle/Desktop/Oracle11g/
[oracle@localhost ~]$ 
[oracle@localhost ~]$ export ORACLE_BASE=/u01/app/oracle/
[oracle@localhost ~]$ 
[oracle@localhost ~]$ /home/oracle/Desktop/Oracle11g/runInstaller -ignoresysprereqs
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 27279 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3999 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

 

Uncheck the checkbox and click on next.

Here you can choose installation option.

Select System class either Desktop class or server class.

Global database name is by default “orcl”. You can change database name. Set password to database.

Check the ignore all box and click on next.

Wait for until installation gets completed.

Click on password management to unlock users.

Unlocked scott user and provided the password. Click on ok

execute the above scripts.

[root@localhost ~]# 
[root@localhost ~]# sh /u01/app/oraInventory/orainstRoot.sh 
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oracle.
The execution of the script is complete.
[root@localhost ~]# 
[root@localhost ~]# sh /u01/app/ora
oracle/       oraInventory/ 
[root@localhost ~]# sh /u01/app/oracle/product/11.2.0/dbhome_1/root.sh 
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
[root@localhost ~]# 
[root@localhost ~]#

Click on close.

Finally, edit .bash_profile and add below paths.

[oracle@localhost ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin

~                                                                                                                                                                      
~   

:wq

[oracle@localhost ~]$ . .bash_profile  
[oracle@localhost ~]$ exit