Saturday, 4 August 2012

Create the database - 11g R2

Hi,

My name is Mark Tiger, creator of this blog. I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly. I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks, that typically need a lot of research, before you can do them. I will try to present the information in an easy to understand way. My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.

Create the database (Linux Perspective) Log into the system as root:

· Need to logon as root, and perform several pre-installation tasks.Start the X-Server software

· Software must be installed from a X-Window system, X-terminal, X-Server.

· Start a terminal session on the target server.

First you need to check the hardware requirements, for 11.2g:Memory Requirement:

· Minimum 1 GB of RAM, recommended 2 or more GB of RAM

· # grep MemTotal /proc/meminfo

· Relationship between RAM and Swap space

Available RAM SWAP Space Required

Between 1GB and 2GB 1.5 times the size of the RAM

Between 2GB and 16GB Equal to the size of the RAM

More than 16GB 16GB

· To determine the system architecture

# uname -m

· Size if the configured swap space

# grep SwapTotal /proc/meminfo

· To determine available swap space and RAM

# free

· Automatic Memory Management 11.2G

Requires more shared memory on the server

/dev/shm

Should be sized greater than MEMORY_MAX_TARGET and MEMORY_TARGET for each instance on the Node.

· To determine the amount of shared memory:

# df -h /dev/shm/

· MEMORY_MAX_TARGET and MEMORY_TARGET cannot be used, when LOCK_SGA is enabled or with HugePages on Linux.

System Architecture

· # uname -m

· This will display the processor type. Verify that the processor architecture matches the Oracle Software Release.

Disk Space Requirements

· You need at least 1GB of space in the /tmp directory

· # df -h /tmp

· If the space is low

o Clean up the /tmp directory

o Set the TMP and TMPDIR environment variable, when setting the Oracle Users environment.

o Extend the filesystem that contains the /tmp direcotry

· Determine the amount of free disk space

o df -h

· Disk space requirements

o Installation Type Software FIles

o Enterprise Edition 4.35 GB

o Standard Edition 4.22 GB

o . Data Files

o Enterprise Edition 1.7 GB

o Standard Edition 1.5 GB

· Additional space would be required for ASM, or Fast recovery Area, or Automated Backups.

Note: This value limits concurrent outstanding requests and should be set to avoid I/O subsystem failures.

To view the parameters:

semmsl, semmns,semopm, and semmni

# /sbin/sysctl -a | grep sem

shmall, shmmax, and shmmni

# /sbin/sysctl -a | grep shm

file-max

# /sbin/sysctl -a | grep file-max

ip_local_port_range

# /sbin/sysctl -a | grep ip_local_port_range

rmem_default

# /sbin/sysctl -a | grep rmem_default

rmem_max

# /sbin/sysctl -a | grep rmem_max

wmem_default

# /sbin/sysctl -a | grep wmem_default

wmem_max

# /sbin/sysctl -a | grep wmem_max

To change the parameters, edit /etc/sysctl.conf

semmsl, semmns,

semopm, and semmni

# /sbin/sysctl -a | grep sem

This command displays the value of the semaphore

parameters in the order listed.

shmall, shmmax, and

shmmni

# /sbin/sysctl -a | grep shm

file-max # /sbin/sysctl -a | grep file-max

ip_local_port_

range

# /sbin/sysctl -a | grep ip_local_port_range

rmem_default # /sbin/sysctl -a | grep rmem_default

rmem_max # /sbin/sysctl -a | grep rmem_max

wmem_default # /sbin/sysctl -a | grep wmem_default

wmem_max # /sbin/sysctl -a | grep wmem_max

On SUSE, you must make sure that the system reads the /etc/sysctl.conf file when it boots.

# /sbin/chkconfig boot.sysctl on

Change the current kernel parameters:

# /sbin/sysctl -p

Confirm that the kernel parameters are set correctly:

# /sbin/sysctl -a

Check resource limits for the Oracle installation users:

Log in as oinstall

Check the file descriptor settings:

$ ulimit -Sn

4096

$ ulimit -Hn

65536

Check the soft and hard limits for the number of processes available to a user.

$ ulimit -Su

2047

$ ulimit -Hu

16384

Check the soft limit for the stack setting:

$ ulimit -Ss

10240

$ ulimit -Hs

32768

If indicated then update the /etc/security/limits.conf file

You may need to add the following lines(illustrative values):

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

Then just log out and log back in as the installation user, to make sure that the new values are assigned to the user.

Creating required Directories:

Oracle Base Directory, and an optional data file directory. You must allow 3GB of space for the Oracle Base Directory, and 4 GB of space for the Data file directory. Or 4GB added onto the Base directory, if you don’t configure the data file directory.

1) Display Oracle file system space. (Physical device name; total amount, used amount, available amount of disk space; mount point directory for that file system.)

# df -k

Identify suitable file systems to install oracle into.

Create the recommended subdirectories in the mount point directory that you identified. Set the appropriate, owner, group and permissions on them. For example if /u01 is the chosen mount point.

# mkdir -p /u01/app/

# chown -R oracle:oinstall /u01/app/

# chmod -R 775 /u01/app/

Do the same for the data-file directory, unless it will also be under the /u01/app directory.

Configuring the Oracle Users Environment:

Before you run OUI(Oracle universal installer) from the oracle user: You must set the default file creation mode mask(umask) to 022 in the shell startup file, and set the DISPLAY environment variable.

1. Start a new terminal session, for example an X terminal session (xterm)

2. Check that the X-Windows applications can display on this system

$ xhost somehost.us.example.com

3. Log into the system that you want to install the system on as the oracle user.

4. If you are logged in as root, then you can switch to the oracle user

$ su - oracle

5. Determine the default shell for the oracle user:

$ echo $SHELL

6. Run the shell startup script

BASH shell: $ . ./.bash_profile

Bourne or Korn shell: $ . ./.profile

C shell: % ./.login

7. If you are not installing the software on the local computer, then run the following on the remote computer, to set the DISPLAY variable. LocalHost is the address of the Local host.

Bourne, Bash, or Korn shell

$ export DISPLAY=LocalHost:0.0

C shell

% setenv DISPLAY LocalHost:0.0

Check if the SHELL and the DISPLAY variables are set up correctly, on the remote computer.

$ echo $SHELL

$ echo $DISPLAY

Now to enable the X applications, run this command on the local computer.

$ xhost + <fully qualified host name of remote computer>

To verify that the X applications is working properly, run a X11 program on the remote computer, for example:

$ /usr/X11R6/bin/xclock

The xclock display should come up on the local computer screen.

8. If you had determined that the /tmp directory has less than 1GB of free disk space, then you need to identify a suitable file system with at least 1GB of free space.

Set the TMP and TMPDIR environment variables to specify a temporary directory on this file system.

a. To determine the free space.

# df -h /tmp

b. Create the temporary directory on the file system

# sudo mkdir /mount_point/tmpcause this

# sudo chmod a+wr /mount_point/tmp

c. Set the TMP and TMPDIR enronment variables

Borne, Bash, or Korn shell:

$ TMP=/mount_point/tmp

$ TMPDIR=/mount_point/tmp

$ export TMP TMPDIR

C Shell:

% setenv TMP /mount_point/tmp

% setenv TMPDIR /mount_point/tmp

9. Set the ORACLE_SID and ORACLE_BASE environment variables

Bourne, Bash, or Korn shell:

$ ORACLE_BASE=/u01/app/oracle

$ ORACLE_SID=sales

$ export ORACLE_BASE ORACLE_SID

C shell:

% setenv ORACLE_BASE /u01/app/oracle

% setenv ORACLE_SID sales

10. In 11g, you want to set the ORACLE_BASE, and allow Oracle to build the Oracle home on top of this. SO you want to make sure that ORACLE_HOME is not set, as well as TNS_ADMIN, because these two variables could confuse the configuration, during installation. Oracle Universal Installer(OUI), will choose a default path for ORACLE_HOME, and TNS_ADMIN is typically build on top of the ORACLE_HOME path. The ORACLE_HOME path is determined by the ORACLE_BASE path; so you should spend some time determining the best possible path for ORACLE_BASE.

Borne, Bash, or Korn shell;

$ unset ORACLE_HOME

$ unset TNS_ADMIN

C shell;

% unsetenv ORACLE_HOME

% unsetenv TNS_ADMIN

Mounting the Product Disc

On most Linux systems the disc should mount automatically when you insert it into the disk drive. If it does not mount, then try this process to get it mounted;

1. Eject the disk that is currently in the disk drive.

Asianux, Oracle Linux, and Red Hat Enterprise Linux:

$ sudo eject /mnt/dvd

SUSE linux enterprise server:

# eject /media/dvd

2. Insert the product disc into the disk drive

3. Verify that the disc mounted automatically

Asianux, Oracle Linux, and Red Hat Enterprise Linux:

$ ls /mnt/dvd

SUSE linux enterprise server:

# ls /media/dvd

4. If you still can’t see the content of the disc, then you can try something like this:

Asianux, Oracle Linux, and Red Hat Enterprise Linux:

$ mount -t iso9660 /dev/dvd /mnt/dvd

SUSE linux enterprise server:

# mount -t iso9660 /dev/dvd /media/dvd

Installing the Oracle Database

Once the environment has been configured, and the product disk is mounted, you are ready to go.

1. Start OUI(Oracle Universal Installer)

$ /mount_point/db/runInstaller

If the installer does not start, then you very likely have X Windows display problems. You will need to refer to the "Oracle Installation Guide for Linux", for troubleshooting information.

Guidelines to complete the installation

If your configuration is correct, then you can just run through the installation wizard and accept all the defaults. However you may want to exercise more control over the installation.

Once the OUI is running:Configure Security Updates

You are going to want to receive security updates. So unless you are already receiving them from another installation that you completed, then you should put your email address in here. Preferably enter your Oracle Support email address. Just check the box "I wish to receive security updates via My Oracle Support "

Click next to get to the next page.

Download Software Updates

Starting with 11G R2 (11.2.0.2), you can choose to dynamically download and apply the latest updates. You can select one of the following options, before clicking "next" to move to the next screen.

1. "Use my Oracle Support credentials for download:" Select this option to download and apply the latest software updates.

a. Click on the proxy settings, to configure a proxy to use for OUI, to connect to the internet . Provide the proxy server information for your site, plus a local user that has access to the local area network through which the server is connecting. From (11.2.0.3), you can also enter the proxy realm(case-sensitive) information.

b. Don’t forget to click on the "Test Connection" button to make sure it works.

2. "Use pre-downloaded software updates:" Choose this option, if you would prefer to download the software updates and apply them.

3. Skip Software Updates: Select this option, if you do not want to apply any updates.

Apply Software Updates

If you selected one of the first two options in the previous screen, then this screen will be displayed.

If you selected Use my Oracle Support credentials for download: on the previous screen, then select Download and apply all updates.

If you selected Use pre-downloaded software updates: on the previous screen, then select Apply all updates.

When you are finished click Next.

Select Installation Option

1. Create and configure a database

2. Install Database Software only

3. Upgrade an existing database

Choose one and click Next

System Class

1. Desktop Class: Choose this if you are running a desktop or laptop. This will ensure that the appropriate amount of resources are allocated for a desktop.

2. Server Class: Choose this option if you are installing on a server class system, such as in a Production data center.

Click Next

Grid Installation Options

1. Single instance database installation: You will get the database and the listener with this option.

3. Oracle RAC One Node database installation: This will install the Oracle RAC One Node database. This configuration option is only supported with Oracle Clusterware.

Click Next

Select Install Type

Usually you could just click the typical install option.

1. Typical Install: This is selected by default. It lets you quickly install Oracle Database using minimal input.

2. Advanced Install: This installation enables you to perform more complex installations.

Click Next

Typical Install Configuration

Important information is gathered here.

1. ORACLE_BASE: The Oracle base path appears by default. Carefully consider the suggested base path, and if necessary, change it to suit your needs. If you deviate away from the default, then try to keep the fully-qualified ORACLE_BASE path as short as possible; because this will effect the length of the paths of most other things in the installation.

2. Software Location: Accept the default or define a path, in which you want to install Oracle components. Consider this carefully if you deviate from the default.

3. Storage Type: File System is the default, or you could also select ASM(Automatic Storage Management)

4. Database File Location: If you select file system as your storage type, then here is where you specify, where you will be storing your data files. Preferably on a separate file system to where you are installing the software.

5. ASMSNMP Password: If you selected ASM as your storage type, then specify a password for the ASMSNMP user.

6. Database Edition: Select the database edition to install. You would probably want to leave this at the default.

7. OSDBA Group: The OSDBA group is selected by default, however you can also selct the OSDBA group from the list.

10. Confirm Password: Here you just confirm the password for the privileged database account.

Click Next

Create Inventory

This screen is only displayed during the first installation of Oracle Products on a system.

Specify the fully qualified path of the Oracle Inventory Directory. Insure that the Operating System group selected is oinstall.

Perform Prerequisite Checks

Verify that all the checks have succeeded. It is possible to continue past this screen, with checks that have not succeeded. However for each small item that fails here, you are looking at potentially huge problems down the line.

If a check fails, then review the cause of the failure, and rectify the problem. Then rerun the check again.

There is an option to check a box next to the failed check to manually verify the requirement. This is not recommended.

There is an ignore all check box. If you check this box, then you will not know if OUI, will be able to successfully install the Oracle Database on your system.

Summary

Review the information displayed on this screen carefully before clicking Install.

From 11gR2 onwards, there is an option to save the steps into a response file by clicking Save Response File. This is useful in terms of understanding what happens in the background, as well as setting up a Silent Installation.Install Product

This screen shows the progress of the installation progress.

After the database is installed, you are prompted to execute a configuration script for new inventory as the root user.

A message is displayed at the end of the configuration process. You must click OK here.

To complete the installation, you must run the root.sh script as the root user.

Click OKFinish

When all the configuration tools are successful, this screen is shown.

Click Close.

Installing Oracle Database Examples

If you plan to use the examples for various reasons, then the following is available:

1. Oracle Database Examples

2. Oracle JDBC Development Drivers

3. Various Oracle Product Demonstrations

You should refer to the Oracle Database Examples Installation Guide

What to Do Next

You should become familiar with this release of Oracle.

· Log into the Oracle Enterprise Manager Database Control, using a Web Browser.

The default URL for Database control is:

http://host.domain:1158/em

Use the username sys, and connect as SYSDBA.

Useful tools within Database Control, will enable you to enable email notifications and automated backups.