Oracle 11g Admin - 2 Creating and Configuring an Oracle Database

- Planning for Database Creation

• Plan the database tables and indexes and estimate the amount of space they will require.
• distribute I/O in several ways
• place redo log files on separate disks or use striping
• situate data files to reduce contention
• control data density (number of rows to a data block)
• use different storage device from data files to Fast Recovery Area
• use OMF and ASM to create and manage the operating system files
• DB_NAME and DB_DOMAIN initialization parameters
• server parameter file (spfile)
• database character set (AL32UTF8) recommended
• Time zone
• DB_BLOCK_SIZE initialization parameter 
• Redo Log Files Block Size
• SYSAUX tablespace size
• non-SYSTEM users Default tablespace
• undo data tablespace 
• multiplexing control file
• duplexing online and archived redo logs
• starting up and shutting down an instance and mounting and opening a database.

- Complete the following steps to create a database with the CREATE DATABASE statement.

Step 1: Specify an Instance Identifier (SID)

$ export ORACLE_SID=orcl;

Step 2: Ensure That the Required Environment Variables Are Set

 $ export ORACLE_BASE=/u01/app/oracle;
$  export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1;

Step 3: Choose a Database Administrator Authentication Method

• password file
• OS

Step 4: Create the Initialization Parameter File (pfile, spfile)

- Recommended Minimum Initialization Parameters
Parameter Name | Mandatory
---------------------              ------------
DB_NAME | Yes
CONTROL_FILES | No
MEMORY_TARGET | No

### create initialization parameter file using sample file in $ORACLE_HOME/dbs 

Step 5: (Windows Only) Create an Instance

Step 6: Connect to the Instance

[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 29 03:37:10 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.

Step 7: Create a Server Parameter File

### use sample file "init.ora" to create pfile "initorcl.ora"
$ cd /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/
$ cp -v init.ora initorcl.ora
[oracle@localhost dbs]$ ls -la
-rw-r--r--  1 oracle oracle 2851 May 15  2009 init.ora
-rw-rw-r--  1 oracle oracle 1118 Sep 21 06:19 initorcl.ora
-rw-r-----  1 oracle oracle 3584 Sep 29 03:33 spfileorcl.ora
SQL> create spfile from pfile;

Step 8: Start the Instance

[oracle@localhost dbs]$ sqlplus /nolog
SQL> connect / as sysdba
Enter password:
Connected.
SQL> start nomount

Step 9: Issue the CREATE DATABASE Statement

Syntax

create_database::=

Description of the illustration create_database.gif

(database_logging_clauses::=, tablespace_clauses::=, set_time_zone_clause ::=)

database_logging_clauses::=

Description of the illustration database_logging_clauses.gif

(file_specification::=)

tablespace_clauses::=

Description of the illustration tablespace_clauses.gif

(file_specification::=, default_tablespace ::=, default_temp_tablespace ::=, undo_tablespace ::=)

default_tablespace ::=

Description of the illustration default_tablespace.gif

default_temp_tablespace ::=

Description of the illustration default_temp_tablespace.gif

(file_specification::=)

extent_management_clause::=

Description of the illustration extent_management_clause.gif

(size_clause::=)

undo_tablespace ::=

Description of the illustration undo_tablespace.gif

(file_specification::=)

set_time_zone_clause ::=

Description of the illustration set_time_zone_clause.gif

SQL> CREATE DATABASE orcl

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY oracle

   LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,

           GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,

           GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE

   SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE users

      DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'

      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs

      DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 10: Create Additional Tablespaces

Step 11: Run Scripts to Build Data Dictionary Views

Step 12: (Optional) Run Scripts to Install Additional Options

Step 13: Back Up the Database.

Step 14: (Optional) Enable Automatic Instance Startup

- Creating a Database with DBCA

• http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-5.php
• http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/2day_dba/install/install.htm

- Install Oracle Database 11gR2 in Enterprise Linux 5.5 x86_64

- Lab Software

- CentOS 7 Physical Machine 10.0.5.1 rhel7 64 bit
- Oracle Enterprise Linux 5.5 Virtual Machine 10.0.5.9 db112srv1.localdomain 64 bit
- VirtualBox 4.3.16
- Oracle Database 11.2.0 64 bit 

### for windows use MobaXterm to remote login and copy database files to virtual machine

### login to virtual machine as root

### change database server host name

# vim /etc/hosts

10.0.5.9        db112srv1.localdomain   db112srv1

# hostname db112srv1.localdomain

# vim /etc/sysconfig/network

  NETWORKING=yes
  NETWORKING_IPV6=no
  HOSTNAME=db112srv1.localdomain

### change database server ip address

# vim /etc/sysconfig/network-scripts/ifcfg-eth0

  TYPE=Ethernet
  DEVICE=eth0
  BOOTPROTO=none
  ONBOOT=yes
  IPADDR=10.0.5.9
  NETMASK=255.255.255.0
  GATEWAY=10.0.5.2
  USERCTL=no
  IPV6INIT=no

# /etc/init.d/network restart

# ifconfig
[root@db112srv1 ~]# ifconfig

inet addr:10.0.5.9  Bcast:10.0.5.255  Mask:255.255.255.0

# hostname

db112srv1.localdomain

### useful tool to easy use and get bash history in sqlplus and rman

# rpm -Uvh ~/Desktop/rlwrap-0.41-1.el5.x86_64.rpm

# vim /home/oracle/.bash_profile

  # oracle settings
  alias sqlplus='rlwrap sqlplus'
  alias rman='rlwrap rman'

### Create user "oracle" and it's groups

# groupadd oinstall
# groupadd dba
# groupadd oper
# groupadd asmadmin
# useradd -g oinstall -G dba,oper,asmadmin oracle
# passwd oracle

### from physical machine

$ ssh-copy-id -i ~/.ssh/id_rsa.pub oracle@10.0.5.9

$ cd /data/software/oracle/database/db_112_lnx64/

[oracle@rhel7 db_112_lnx64]$ ls -la

-rw-------. 1 oracle oracle 1239269270 Jan 19  2013 linux.x64_11gR2_database_1of2.zip
-rw-------. 1 oracle oracle 1111416131 Jan 19  2013 linux.x64_11gR2_database_2of2.zip

### Copy database files from my physical machine to virtual using SCP command tool

$ scp ./linux.x64_11gR2_database_* oracle@10.0.5.9:/tmp/

### Login to virtual machine

$ ssh -Y oracle@10.0.5.9

### login as root

$ su

### Change Kernel parameters
# vim /etc/sysctl.conf

  # oracle settings
  fs.suid_dumpable = 1
  fs.aio-max-nr = 1048576
  fs.file-max = 6815744
  kernel.shmall = 2097152
  kernel.shmmax = 536870912
  kernel.shmmni = 4096
  # semaphores: semmsl, semmns, semopm, semmni
  kernel.sem = 250 32000 100 128
  net.ipv4.ip_local_port_range = 9000 65500
  net.core.rmem_default=262144
  net.core.rmem_max=4194304
  net.core.wmem_default=262144
  net.core.wmem_max=1048586

# /sbin/sysctl -p

### Change Kernel limits
# vim /etc/security/limits.conf

# oracle settings
  oracle              soft    nproc   2047
  oracle              hard    nproc   16384
  oracle              soft    nofile  4096
  oracle              hard    nofile  65536
  oracle              soft    stack   10240

### Install the following packages if they are not already present.

# From Oracle Linux 5 DVD
# cd /media/cdrom/Server
# rpm -Uvh binutils-2.*
# rpm -Uvh compat-libstdc++-33*
# rpm -Uvh compat-libstdc++-33*.i386.rpm
# rpm -Uvh elfutils-libelf*
# rpm -Uvh gcc-4.*
# rpm -Uvh gcc-c++-4.*
# rpm -Uvh glibc-2.*
# rpm -Uvh glibc-common-2.*
# rpm -Uvh glibc-devel-2.*
# rpm -Uvh glibc-headers-2.*
# rpm -Uvh ksh*
# rpm -Uvh libaio-0.*
# rpm -Uvh libaio-devel-0.*
# rpm -Uvh libgomp-4.*
# rpm -Uvh libgcc-4.*
# rpm -Uvh libstdc++-4.*
# rpm -Uvh libstdc++-devel-4.*
# rpm -Uvh make-3.*
# rpm -Uvh sysstat-7.*
# rpm -Uvh unixODBC-2.*
# rpm -Uvh unixODBC-devel-2.*
# rpm -Uvh numactl-devel-*
# cd /
# eject

### change SELinux status to premissive

# vim /etc/selinux/config

  SELINUX=permissive

# getenforce

permissive

# Create the directories in which the Oracle software will be installed.

  # mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
  # chown -R oracle:oinstall /u01
  # chmod -R 775 /u01

# exit - exit from user "root" come to user "oracle"

# vim ~/.bash_profile
  # Oracle Settings

  alias sqlplus='rlwrap sqlplus';
  alias rman='rlwrap rman';
  export ORACLE_BASE=/u01/app/oracle;
  export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1;
  export ORACLE_SID=orcl;
  export PATH=$ORACLE_HOME/bin:$PATH;
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
  export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

$ cd /tmp/

$ unzip linux.x64_11gR2_database_1of2.zip
$ unzip linux.x64_11gR2_database_2of2.zip

$ cd /tmp/database/

$ ./runInstaller

snapshot01>  uncheck Oracle support unless if you have one

snapshot02> yes to continue, Next

snapshot03 > select install software only, create database comes later, Next

snapshot04> select single instance, Next

snapshot05> next

snapshot06> select Enterprise Edition, Next

snapshot07>  set database base, database software location, Next

snapshot08> inventory path, Next

snapshot09> select OS groups, Next

snapshot10> prerequisites check, Next 

snapshot11> summary, Next

snapshot12> installation progress

snapshot13>  run scripts as "root" from new terminal login as root to virtual machine
$ ssh -Y root@10.0.5.9
# /u01/app/oraInventory/orainstRoot.sh
# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh > Enter key

snapshot14>  install done, close

- Create a listener to make oracle Net Services work and listen to database music

$ ssh -Y oracle@10.0.5.9

$ netca

- select Listener configuration

- Add, Next 

 - set listener name or default LISTENER, Next

- select listener protocols TCP, Next  

 - set  listener port or let default 1521, Next

- Next

- Done, Next

- Finish

### Create database "orcl"

$ dbca


- welcome, Next

- select create a database, Next

- just defaults, Next

- set database global  name if have a domain add it like "orcl.example.com" to be accessed through domain , database SID

- Set Enterprise manager configuration, Next

- set database account password

- confirmation about using one password (bad practice) , yes, Next

- select managing database files method, Next

- set flash back recovery , archiving mode, Next

- create samples schemas HR, OE, SCOTT, Next

- i just change character set for fun, you cna go on without, Next

- create installation as scripts, Next

- installation summary , Ok

- installation progress

- select password management

- set password  and unlock accounts SYS, SYSTEM, HR, SCOTT, OE for training and testing

- confirmation about using one password for all database accounts, yes 

- login summary

- confirm every thing working fine

$ lsnrctl start
$ sqlplus / as sysdba
SQL>  startup
SQL>  select instance_name, status, database_status, version from v$instance;
INSTANCE_NAME    STATUS       DATABASE_STATUS   VERSION
---------------- ------------ ----------------- -----------------
orcl             OPEN         ACTIVE            11.2.0.2.0

SQL> exit

$ emctl start dbconsole

- setting the restart flag for each instance to 'Y'.

$ vim /etc/oratab

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

- Shutting down Oracle Database Server

$ emctl stop dbconsole
$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> exit
$ lsnrctl stop
$ su
# poweroff

- Starting Oracle Database Server

$ lsnrctl stop
$ sqlplus / as sysdba
SQL> startup ;
SQL> exit
$ emctl start dbconsole 

- Creating a Database with Noninteractive/Silent DBCA

• http://hugepang.wordpress.com/2011/06/21/oracle-11gr2-silent-installation/
• http://gruffdba.wordpress.com/2013/01/10/silent-install-of-oracle-11-2-0-3-on-redhat-6-0/
• http://www.oracle-base.com/articles/misc/oui-silent-installations.php
• http://www.redbooks.ibm.com/redpapers/pdfs/redp9131.pdf [PDF]
• http://dbaora.com/install-oracle-in-silent-mode-11g-release-2-11-2/

- install oracle database software 

### all steps in graphical mode installation required to accomplish before start here
### login as user "oracle"

$ cd /tmp/

$ unzip linux.x64_11gR2_database_1of2.zip
$ unzip linux.x64_11gR2_database_2of2.zip

$ cd /tmp/database/

$ $ mkdir -p /u01/download/

$ cp -fv  /tmp/database/response/db_install.rsp /u01/download/db11ginstall.rsp

$ vim /u01/download/db11ginstall.rsp

oracle.install.option=INSTALL_DB_SWONLY

UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/OraInventory
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
DECLINE_SECURITY_UPDATES=true

$ ./runInstaller -silent -noconfig -responseFile /u01/download/db11ginstall.rsp

-- silent installation output 

/u01/app/oracle/OraInventory/logs/installActions2014-09-25_01-27-18PM.log
The following configuration scripts need to be executed as the "root" user. 
 #!/bin/sh 
 #Root scripts to run

To execute the configuration scripts:
         1. Open a terminal window 
         2. Log in as "root" 
         3. Run the scripts 
         4. Return to this window and hit "Enter" key to continue 


Successfully Setup Software.

- run root scripts

# /u01/app/oracle/OraInventory/orainstRoot.sh
# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh
[oracle@db112srv1 database]$ source .bash_profile

- watch logging to silent database installation, you will have a log file 

$ watch cat  /u01/app/oracle/OraInventory/logs/installActions2014-09-25_01-27-18PM.log

- Configure silent Listener you can copy response file and modify it

[oracle@db112srv1 database]$ find /u01 -name netca.rsp
/u01/app/oracle/product/11.2.0/dbhome_1/inventory/response/netca.rsp
[oracle@db112srv1 database]$ source .bash_profile
[oracle@db112srv1 ~]$ netca -silent -responsefile $ORACLE_HOME/inventory/response/netca.rsp 

[oracle@db112srv1 ~]$ lsnrctl status

- Create a database with dbca with silent mode

[oracle@db112srv1 ~]$ dbca -silent -createDatabase -characterSet AL32UTF8 -continueOnNonFatalErrors false -disableSecurityConfiguration ALL -emConfiguration NONE -gdbName orcl.localdomain -listeners LISTENER -memoryPercentage 40 -sid orcl -SysPassword oracle -SystemPassword oracle -sampleSchema true -templateName General_Purpose.dbc
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
57% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.

- verify that database work fine

$ sqlplus / as sysdba

SQL> show user
USER is "SYS"
SQL> select instance_name,status,database_status,archiver,version from v$instance;

INSTANCE_NAME  STATUS        DATABASE_STATUS ARCHIVE VERSION
---------------- -------------------- ----------------- ------- ---------------
orcl   OPEN        ACTIVE  STOPPED 11.2.0.1.0

SQL> col product format a40;
SQL> col version format a15;
SQL> col status format a20;

SQL> select * from product_component_version;

PRODUCT      VERSION  STATUS
---------------------------------------- --------------- --------------------
NLSRTL      11.2.0.1.0  Production
Oracle Database 11g Enterprise Edition  11.2.0.1.0  64bit Production
PL/SQL      11.2.0.1.0  Production
TNS for Linux:     11.2.0.1.0  Production

#### Create Database Manually

&

- after software installation we create database manually

http://rafioracledba.blogspot.com/2012/08/creating-11g-database-manually-in-11.html

- Database Service Data Dictionary Views

You can find service information in the following service-specific views:

• DBA_SERVICES
• ALL_SERVICES or V$SERVICES
• V$ACTIVE_SERVICES
• V$SERVICE_STATS
• V$SERVICE_EVENT
• V$SERVICE_WAIT_CLASSES
• V$SERV_MOD_ACT_STATS
• V$SERVICE_METRICS
• V$SERVICE_METRICS_HISTORY

The following additional views also contain some information about services:

• V$SESSION
• V$ACTIVE_SESSION_HISTORY
• DBA_RSRC_GROUP_MAPPINGS
• DBA_SCHEDULER_JOB_CLASSES
• DBA_THRESHOLDS

- Cloning a Database with CloneDB

http://www.oracle-base.com/articles/11g/clonedb-11gr2.php

http://www.dbagalaxy.com/clonedb/

Database Data Dictionary Views

View	Description
DATABASE_PROPERTIES	Displays permanent database properties
GLOBAL_NAME	Displays the global database name
V$DATABASE	Contains database information from the control file

- To drop a database:
### Dropping a database involves removing its data files, redo log files, control files, and initialization parameter files.

### database must be mounted in exclusive and restricted mode

http://www.oraclenerd.com/2011/09/drop-database.html

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount restrict;
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

wish you happy oracle ...