Archives For demonstration

I could have applied the PSU earlier ( before creating the database ), but in real life this is the more likely scenario. As the patch sources are already in place:

su - oracle
cd /opt/oracle/stage
ls -la
6880880_112000_Linux-x86-64.zip
p13348650_112030_Linux-x86-64.zip
opatch_response.rsp

start with updating opatch:

unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME/

It is safe to answer “All” if the unzip utility ask to replace some files.

Extract and check the readme:

unzip p13348650_112030_LINUX.zip

The structure of the oracle patches is always the same. You will get subdirectories named with the patch number and the readme:

ls -la
drwxr-xr-x 4 oracle oinstall 4096 Jan 14 17:08 13343438
drwxr-xr-x 5 oracle oinstall 4096 Jan 14 17:08 13348650
-rw-r--r-- 1 oracle oinstall 422 Jan 14 17:08 bundle.xml
-rwxrwx--- 1 grid oinstall 621 Mar 23 11:31 opatch_response.rsp
-rwxrwx--- 1 grid oinstall 32510817 Mar 23 10:54 p6880880_112000_Linux-x86-64.zip
-rw-rw-r-- 1 oracle oinstall 107674 Jan 16 14:49 README.html
-rw-r--r-- 1 oracle oinstall 21 Jan 14 17:08 README.txt

Because this patch contains both, the database and the grid patches, change to the database patch directory ( which is 13343438 ) and apply the patchset:

cd /opt/oracle/stage/13343438
srvctl stop listener -l listener_DB112
srvctl stop database -d DB112
export PATH=$ORACLE_HOME/OPatch:$PATH
opatch lsinventory -detail -oh $ORACLE_HOME
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/oracle/stage/13343438 -oh $ORACLE_HOME
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -output /opt/oracle/stage/opatch_response.rsp -no_banner
opatch apply -ocmrf /opt/oracle/stage/opatch_response.rsp

The output should be very similar to this:

Oracle Home : /opt/oracle/product/base/11.2.0.3
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/base/11.2.0.3/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /opt/oracle/product/base/11.2.0.3/cfgtoollogs/opatch/13343438_Mar_28_2012_15_35_47/apply2012-03-28_15-35-47PM_1.log
Applying interim patch '13343438' to OH '/opt/oracle/product/base/11.2.0.3'
Verifying environment and performing prerequisite checks...
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/base/11.2.0.3')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Patching component oracle.rdbms.rsf, 11.2.0.3.0...
Patching component oracle.rdbms, 11.2.0.3.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...
Verifying the update...
Patch 13343438 successfully applied
Log file location: /opt/oracle/product/base/11.2.0.3/cfgtoollogs/opatch/13343438_Mar_28_2012_15_35_47/apply2012-03-28_15-35-47PM_1.log
OPatch succeeded.

Patch ( almost ) applied.

Perhaps you noticed that opatch creates a new directory under you ORACLE_HOME which is called .patch_storage:

ls -la /opt/oracle/product/base/11.2.0.3/.patch_storage/
total 28
drwxr-xr-x 4 oracle oinstall 4096 Mar 28 15:38 .
drwxrwx--- 75 oracle oinstall 4096 Mar 28 15:36 ..
drwxr-xr-x 4 oracle oinstall 4096 Mar 28 15:38 13343438_Jan_3_2012_02_50_27
-rw-r--r-- 1 oracle oinstall 336 Mar 28 15:36 interim_inventory.txt
-rw-r--r-- 1 oracle oinstall 92 Mar 28 15:36 LatestOPatchSession.properties
-rw-r--r-- 1 oracle oinstall 0 Mar 28 15:38 patch_free
-rw-r--r-- 1 oracle oinstall 51 Mar 28 15:36 record_inventory.txt
drwxr-xr-x 2 oracle oinstall 4096 Mar 28 15:38 verify

Under no circumstances delete this files. You will not be able apply any futures patches if you do this. If you really experience space issues, you can do a cleanup of the files which are not needed:

opatch util cleanup

One step is still missing. It it very likely that oracle modified some SQL files with this patch, so one needs to apply this, too:

srvctl start listener -l listener_DB112
srvctl start database -d DB112
sqlplus / as sysdba
@?/psu/11.2.0.3.1/catpsu.sql
select * from registry$history;

Work done.

Now there is a working Grid Infrastructure and database each patched to the ( currently ) latest release. If you did not export your Virtual Machine and did not create any snapshots I recommend to do an export now ( you need to shutdown your Virtual Machine for this ):

vboxmanage export "oracleplayground" --output oracleplayground.ovf

If you destroy the database or any parts of the grid infrastructure you are always able to bring it back in a few minutes.

Where to go from now ? This depends on you. If you are new to oracle there is a lot of literature out there, but the most important ( and at no cost ): Oracle Database Concepts . This should be your starting point.

Before I start with the database creation, there remain some steps to be performed. Because I will place the files inside the ASM instance there is no need to create separate filesystems for datafiles, redologs, control-files. But still I want a custom location for the audit logs, pfile and diagnostic destination. Let’s prepare this:

su -
mkdir /oradata/DB112
chown oracle:dba /oradata/DB112
su - oracle
mkdir -p /oradata/DB112/admin/pfile
mkdir /oradata/DB112/admin/adump

The easiest, and probably the best way for most environments is to go with the defaults. Let’s start by creating a minimal pfile…

su - oracle
echo "*.audit_file_dest='/oradata/DB112/admin/adump'
*.control_files='+DB112_DATA_DG','+DB112_ARCH_DG'
*.db_block_size=8192
*.db_name='DB112'
*.instance_name='DB112'
*.sga_max_size=256M
*.sga_target=256M
*.undo_tablespace='undotbs1'
*.diagnostic_dest=/oradata/DB112/admin" > /oradata/DB112/admin/pfile/initDB112.ora

… check that we can initially start the instance…

sqlplus / as sysdba
startup force nomount pfile=/oradata/DB112/admin/pfile/initDB112.ora

… create the server parameter file:

create spfile='/oradata/DB112/admin/pfile/spfileDB112.ora' from pfile='/oradata/DB112/admin/pfile/initDB112.ora';

… create a link to the server parameter file ( for oracle to find the file )

!ln -s /oradata/DB112/admin/pfile/spfileDB112.ora $ORACLE_HOME/dbs/spfileDB112.ora

… restart without specifying a pfile to see if the spfile gets used:

startup force nomount
show parameter pfile;

… and create the database

create database DB112
logfile
group 1 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 2 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 3 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse
character set utf8
national character set AL16UTF16
datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M extent management local
default temporary tablespace TEMP tempfile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M
sysaux datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M
default tablespace USERS datafile '+DB112_DATA_DG' size 4m reuse autoextend on next 8m maxsize 16M
undo tablespace UNDOTBS1 datafile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M;

… create the dictionary:

@?/rdbms/admin/catalog.sql

… and the plsql stuff:

@?/rdbms/admin/catproc.sql

… create the help tables for sqlplus and populate it:

@?/sqlplus/admin/help/hlpbld.sql helpus.sql
shutdown immediate;
exit;

Done. Database created. What else ? Right, register the database with the Cluster Stack and create a listener:

srvctl add database -d DB112 -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfileDB112.ora
srvctl add listener -l listener_DB112 -p 1521 -o $ORACLE_HOME
touch chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
chmod 770 /opt/oracle/product/base/11.2.0.3/
chmod 770 /opt/oracle/product/base/11.2.0.3/dbs
chmod 770 /opt/oracle/product/base/11.2.0.3/network/
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
srvctl start listener -l listener_DB112
srvctl start database -d DB112

Please notice: The chmods are dirty tricks to avoid CRS-5010 issues. The reason for the CRS-5010 messages is that the cluster stack runs under a different user ( grid in this case ) and is not able to update the files. Maybe there is a fix in the future.

Let’s check the resources and do a reboot to check if all comes up again:

su - grid
crsctl status resource -t
su -
reboot
su - grid
crsctl status resource -t

You should see that the resources are up and running, something like:

+ASM@oracleplayground.fun grid:/home/grid $ crsctl status resource -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DB112_ARCH_DG.dg
ONLINE ONLINE oracleplayground
ora.DB112_DATA_DG.dg
ONLINE ONLINE oracleplayground
ora.LISTENER_ASM.lsnr
ONLINE ONLINE oracleplayground
ora.LISTENER_DB112.lsnr
ONLINE ONLINE oracleplayground
ora.asm
ONLINE ONLINE oracleplayground Started
ora.ons
OFFLINE OFFLINE oracleplayground
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE oracleplayground
ora.db112.db
1 ONLINE ONLINE oracleplayground Open
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE oracleplayground

For your convenience, here the complete database creation script:

echo "*.audit_file_dest='/oradata/DB112/admin/adump'
*.control_files='+DB112_DATA_DG','+DB112_ARCH_DG'
*.db_block_size=8192
*.db_name='DB112'
*.instance_name='DB112'
*.sga_max_size=256M
*.sga_target=256M
*.undo_tablespace='undotbs1'
*.diagnostic_dest=/oradata/DB112/admin" > /oradata/DB112/admin/pfile/initDB112.ora
sqlplus / as sysdba
startup force nomount pfile=/oradata/DB112/admin/pfile/initDB112.ora
create spfile='/oradata/DB112/admin/pfile/spfileDB112.ora' from pfile='/oradata/DB112/admin/pfile/initDB112.ora';
!ln -s /oradata/DB112/admin/pfile/spfileDB112.ora $ORACLE_HOME/dbs/spfileDB112.ora
startup force nomount
show parameter pfile;
create database DB112
logfile
group 1 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 2 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse,
group 3 ('+DB112_DATA_DG','+DB112_ARCH_DG') size 16M reuse
character set utf8
national character set AL16UTF16
datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M extent management local
default temporary tablespace TEMP tempfile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M
sysaux datafile '+DB112_DATA_DG' size 512M reuse autoextend on next 8M maxsize 1024M
default tablespace USERS datafile '+DB112_DATA_DG' size 4m reuse autoextend on next 8m maxsize 16M
undo tablespace UNDOTBS1 datafile '+DB112_DATA_DG' size 64M reuse autoextend on next 8M maxsize 1024M;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
shutdown immediate;
exit;
srvctl add database -d DB112 -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfileDB112.ora
srvctl add listener -l listener_DB112 -p 1521 -o $ORACLE_HOME
touch chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
chmod 770 /opt/oracle/product/base/11.2.0.3/
chmod 770 /opt/oracle/product/base/11.2.0.3/dbs
chmod 770 /opt/oracle/product/base/11.2.0.3/network/
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin
chmod 770 /opt/oracle/product/base/11.2.0.3/network/admin/listener.ora
srvctl start listener -l listener_DB112
srvctl start database -d DB112

To summarize the situation until now: The operating system is installed and configured. On top of it I installed the grid infrastructure software, configured it for single node usage, patched it to the latest release and created the ASM instance including to disk-groups. Several reboot tests proved that the whole stack survives a server reboot and we do not need to create any custom scripts to manage the shutdown and startup of the resources managed by the cluster stack.

Now we are doing the database software installation ( in much se same way we installed the grid infrastructure software ). You will notice once again: If all the prerequisites have been met, the software installation is no magic at all.

If you did not transer the source files ( p10404530_112030_Linux-x86-64_1of7.zip and p10404530_112030_Linux-x86-64_2of7.zip) to your Virtual Machine, now is the time to do so ( see here for a description on how to do it ).

As the database software will be installed under the “oracle” user, establish a ssh connection to your Virtual Machine and do the installation ( I will do a software only installation here, nothing will be configured for the moment ):

su - oracle
cd /opt/oracle/stage
unzip p10404530_112030_Linux-x86-64_1of7.zip
unzip p10404530_112030_Linux-x86-64_2of7.zip
cd database
./runInstaller \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_BASE=/opt/oracle/product/base \
ORACLE_HOME=/opt/oracle/product/base/11.2.0.3 \
ORACLE_HOME_NAME=H112030 \
UNIX_GROUP_NAME=oinstall \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=oper \
FROM_LOCATION=../stage/products.xml \
INVENTORY_LOCATION=/opt/oracle/oraInventory \
SELECTED_LANGUAGES=en \
oracle.install.db.InstallEdition=EE \
DECLINE_SECURITY_UPDATES=true \
-ignoreSysPrereqs \
-ignorePrereq \
-waitforcompletion \
-silent
su -
/opt/oracle/product/base/11.2.0.3/root.sh

Again, that’s all what needs to be done.

Let’s set up some environment variables and aliases…

su - oracle
echo "ORACLE_HOME=/opt/oracle/product/base/11.2.0.3
ORACLE_SID=DB112
LD_LIBRARY_PATH=/opt/oracle/product/base/11.2.0.3/lib:$LD_LIBRARY_PATH
PATH=/opt/oracle/product/base/11.2.0.3/bin:$PATH
PS1='\[33[1;34m\]${ORACLE_SID}\[33[1;33m\]@\[33[1;32m\]`uname -n`\[33[0m\] $LOGNAME:$PWD $ '
export ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH PS1
alias sqlp='sqlplus '\''/ as sysdba'\'''
" >> ~/.bash_profile

A final little test, to prove that sqlplus is working:

su - oracle
sqlp

… you should see something like this:

DB112@oracleplayground.fun oracle:/home/oracle $ sqlp
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 26 14:42:12 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit;
Disconnected
DB112@oracleplayground.fun oracle:/home/oracle $

Congratulations, now you are prepared to set up the oracle database instance.

Again, remember to take a snapshot of your Virtual Machine if you want to save your work or if you want to revert to this point. Delete the source files if you do not want to become your Virtual Machine too big before:

su - oracle
cd /opt/oracle/stage/
rm -rf database p10404530_112030_Linux-x86-64_1of7.zip p10404530_112030_Linux-x86-64_2of7.zip

On your workstation create the snapshot:

vboxmanage snapshot oracleplayground take "after database software installation"