Wednesday, August 22, 2012

Oracle Database 11.2.0.3 Install and Setup on RedHat Linux 6 x86_64

In this post we will install a new server with the latest Oracle Database 11gR2 software (as of this writing, it is version 11.2.0.3). In this example, the new machine is called opus.company.com and the new database instance is called meta.

Pre-Installation Tasks


Software Download


Start by connecting to My Oracle Support and search for patchset 10404530. There are seven files in this patch. According to the patch README, the files are :

Table 1 Installation Types and Associated Zip Files

Installation Type Zip File Oracle Database (includes Oracle Database and Oracle RAC)
Note: you must download both zip files to install Oracle Database.
p10404530_112030_platform_1of7.zip
p10404530_112030_platform_2of7.zip

Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart)
p10404530_112030_platform_3of7.zip

Oracle Database Client
p10404530_112030_platform_4of7.zip

Oracle Gateways
p10404530_112030_platform_5of7.zip

Oracle Examples
p10404530_112030_platform_6of7.zip

Deinstall
p10404530_112030_platform_7of7.zip

We only need the first two files to install the database. Since that's our goal, then download the first two files and place them into a staging NFS directory. If you don't have one, then go ahead and create one on your NFS server as this is quite handy! (Check Doc ID 1117597.1 for the NFS options).

Note that even if the files come from a patchset, they're actually a full software install. So we don't need to have version 11.2.0.1 or 11.2.0.2 installed before we install version 11.2.0.3.

mkdir /nfs/install/oracle/11.2/x86_64
mv ~/Downloads/p10404530_112030_Linux-x86-64_*.zip /nfs/install/oracle/11.2/x86_64

Create the checksum files. The exact values are listed in the patch digest page.

echo "80A78DF21976A6586FA746B1B05747230B588E34" > /nfs/install/oracle/11.2/x86_64/p10404530_112030_Linux-x86-64_1of7.zip.sha1
echo "A39BED06195681E31FBB0F6D7D393673BA938660" > /nfs/install/oracle/11.2/x86_64/p10404530_112030_Linux-x86-64_2of7.zip.sha1

Verify the SHA1 checksum of both files and compare the results with the ones listed above. The results are not case sensitive.

openssl dgst -sha1 /nfs/install/oracle/11.2/x86_64/p10404530_112030_Linux-x86-64_1of7.zip
openssl dgst -sha1 /nfs/install/oracle/11.2/x86_64/p10404530_112030_Linux-x86-64_2of7.zip

Extract both zip files. This will create a « database » directory.

cd /nfs/install/oracle/11.2/x86_64
unzip p10404530_112030_Linux-x86-64_1of7.zip
unzip p10404530_112030_Linux-x86-64_2of7.zip

Server Setup


Install a Minimal RedHat Enterprise Linux 6 x86_64 machine. I like to create seperate mount points for /u01, /u02 and /u03 as specified by the Oracle Flexible Architecture guide. In this blog, we build an RMAN server. It doesn't need quite a lot of disk space, so I'm only using local disk drives. For production databases, the /u02 and /u03 mount points are LUNs on a SAN, so I use ASM. But this will be the topic of another blog post :)

/etc/fstab


Once your server is up and running, connect to it and edit the /etc/fstab to build a bigger shared memory. Bigger is better here. But it depends on the amount of memory the machine has. See the « Oracle Database Administrator's Reference 11g Release 2 (11.2) for Linux and UNIX-Based Operating Systems Chapter C - Administering Oracle Database on Linux » guide for more info.

WARNING : don't just copy this fstab line! Just copy the line which starts with « shmfs » and paste it into your own /etc/fstab file. And adjust the size according to the amount of memory in your system. This is just an example.

sudo vi /etc/fstab
tmpfs   /dev/shm   tmpfs   size=4g        0 0

Kernel Configuration


We must change quite a few parameters in the RedHat Linux kernel to keep Oracle happy.


Don't forget that we must reboot to enable those changes. Well, that's not exactly true, as we can force the config with the help of sysctl(8) command. But rebooting now will ensure that our /etc/sysctl.conf file does not contain any errors. Imagine you reboot after the software is installed and it doesn't work. Try tracing the problem down to this file is quite a time-consuming (and frustrating) task.

sudo shutdown -r now

Security Limits


Configure shell limits for the oracle user. Here I set both the grid and the oracle user's limits. In this blog we're not using the grid user. But I like to have all my systems as identical as possible.


Package Installation


We must make sure this new Oracle machine has several RPM installed. For instance, the xorg-x11-xauth rpm is installed because we need the xauth(1) command for the X11 forwarding to work. We also install the xorg-x11-utils package in order to have access to the xdpyinfo(1) command which is used by the Orace Universal Installer (OUI) when it starts. We don't absolutely need this, as you can always ignore the system prerequisites when you start the OUI with the -ignorePrereq flag.

sudo yum -y install xorg-x11-xauth gcc gcc-c++ libaio libaio-devel compat-libstdc++-33 glibc-devel glibc-headers  libstdc++ sysstat binutils make expat compat-libcap1 ksh compat-glibc compat-glibc-headers glibc-devel.i686

Don't be surprised by the number of other packages that will be installed to satisfy dependencies.

Users and Groups


Your organisation's RedHat Kickstart should have created the users and groups required to run Oracle. But if not, then these are the ones you need. Adapt the UID and GID as you see fit. Just make sure they're unique in the entire corporate network.

We first create several groups.

sudo groupadd -g 5000 oinstall
sudo groupadd -g 5001 dba
sudo groupadd -g 5002 sysoper
sudo groupadd -g 5003 asmadmin
sudo groupadd -g 5004 asmdba
sudo groupadd -g 5005 asmoper

Then we create the oracle and grid users. Again, we don't really need the grid user in this post, but let's create it anyway. Who knows, maybe one day we will migrate to ASM?

sudo useradd -u 5001 -g oinstall -G dba,asmadmin,asmdba,asmoper -d /usr/home/grid -s /bin/bash -c
"Oracle Grid Infrastructure Owner" -m grid

sudo useradd -u 5000 -g oinstall -G dba,sysoper,asmdba -d /usr/home/oracle -s /bin/bash -c "Oracle
Database Owner" -m oracle

Edit /etc/profile to handle these new users.

sudo vi /etc/profile

SSH Keys and X11 Forwarding


We will need our new Oracle machine to accept SSH connections from our oracle and grid users. We also need it to forward X11. To do this, edit sshd's configuration. 

WARNING : make sure you understand this file, because you can lock yourself out from the server if you don't!


Restart the daemon so that he understands the changes.

sudo /etc/init.d/sshd restart

Now from your workstation, if you haven't done so already, create a pair of SSH keys and then send the public one to your new Oracle server. 

ssh-keygen -t rsa
scp ~/.ssh/id_rsa.pub opus.company.com:/tmp

Connect to the machine and place your public SSH key into the oracle user's authorized_keys. 

ssh opus.company.com
sudo su - oracle
mkdir ~/.ssh
cat /tmp/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
exit
rm /tmp/id_rsa.pub
exit

Back on your own workstation, allow the new server X11 access to the local machine and then connect to the new machine with the X11 forwarding enabled.

xhost +opus.company.com
ssh -Y -X oracle@opus.company.com

Create Directories


Let's connect to the new machine and create some directories. First the Oracle Inventory.

ssh opus.company.com
sudo mkdir -p /u01/app/oraInventory
sudo chown root:oinstall /u01/app/oraInventory
sudo chmod -R 2775 /u01/app/oraInventory

Then create the Oracle base.

sudo mkdir -p /u01/app/oracle
sudo chown -R oracle:oinstall /u01/app/oracle
sudo chmod -R 775 /u01/app/oracle

Software Installation


Connect to the new machine with X11 enabled.

ssh -YX oracle@opus.company.com

Fix OUI Bug



Because of a bug with the 11.2.0.3 OUI (see Doc ID 1454982.1), we must edit a file before we can start the OUI.

cd /nfs/install/oracle/11.2/x86_64/database/stage/cvu/cv/admin
cp cvu_config cvu_config.backup

Create a Response File


Edit a response file. The easiest way to create a response file is to run the installer in GUI mode and hit the save response file button just before you would normally start the installation. Exit from the GUI OUI and start it at the console prompt using your new response file.

Why bother with a response file? Because IMHO it's easier to follow the installation progress and you skip some problems when you can't have the X11 output (security policies) or you don't have access to an X server (a workstation running Windows on which you can't install Xming for example).

mkdir ~oracle/oui

Run the installer


./runInstaller -showProgress -ignorePrereq -ignoreSysPrereqs -silent -responseFile ~/oui/db.11.2.0.3.rsp

This will start the OUI. Give him a few minutes to get started and you will be notified of the log file at the standard output. So open a second shell window and follow both ouput simultaneously.

ssh opus.company.com
sudo su - oracle
tail -F /u01/app/oraInventory/logs/installActions*.log

A bit latter, you will see this appear in the standard output window :

As a root user, execute the following script(s):
1. /u01/app/oraInventory/orainstRoot.sh
2. /u01/app/oracle/product/11.2.0.3/dbhome_1/root.sh

It's pretty obvious what we now need to do... So from another shell do this :

sudo /u01/app/oraInventory/orainstRoot.sh
sudo /u01/app/oracle/product/11.2.0.3/dbhome_1/root.sh

The last script will display a log file name saying that's where you should look to see what the script just did. So a simple cat(1) will do the trick (because of the time stamp, the file name will be different every time you run the OUI, so use the file name the script told you). 

sudo cat /u01/app/oracle/product/11.2.0.3/dbhome_1/install/root_opus.company.com_2012-07-03_14-21-29.log

Good job, you now have Oracle RDBMS 11.2.0.3 Enterprise Edition installed! :)

Post-Installation Tasks


Backup Root Scripts


Oracle recommends to backup both root.sh scripts. So let's do this.

mkdir ~/backup
cp /u01/app/oraInventory/orainstRoot.sh ~/backup/orainstRoot.sh.`date +%Y%m%d`
cp /u01/app/oracle/product/11.2.0.3/dbhome_1/root.sh ~/backup/root.sh..`date +%Y%m%d`

Configure User Environment


.bash_profile


Let's configure the oracle user's environment. I always like to keep a copy of the environment that has nothing Oracle related configured. It's usefull when we need to run OUI again.

cp ~/.bash_profile ~/.bash_profile.oui

.aliases


Let's create some finger-friendly aliases.


Check to make sure the environment is ok?

source ~/.bash_profile
env | sort
alias

login.sql


This file is read when we start an sqlplus session. I like to change things a bit.

mkdir -p /nfs/home/oracle/scripts/

Database Creation


We're now ready to create a new empty database. In this example, the database will be called « meta ».

for DIR in adump dpdump hdump pfile scripts; do
  sudo mkdir -p /u01/app/oracle/admin/meta/$DIR
done

sudo mkdir -p /u01/oradata /u02/oradata /u03/oradata
sudo chown -R oracle:oinstall /u0*/oradata
sudo chown -R oracle:oinstall /u01/app/oracle/admin

I like to use the Oracle Database Configuration Assistant (DBCA) to setup the SQL scripts. This is because each version of Oracle has a modified DBCA which uses the latest configurations. Instead of using a database creation script I used for Oracle 10gR1 on a new 11gR2, I prefer to generate new scripts via the latest DBCA. I then save those scripts and run them manually. Running them manually gives me the opportunity to learn how it's done. I can also run those scripts manually on another machine without using DBCA again in case this machine doesn't have X11 forwarding enabled. So start DBCA and save the scripts.

sudo su - oracle
$ORACLE_HOME/bin/dbca

This will generate a bunch of files. You can download them from my dropbox account.

Execute the scripts to create the new database.

sudo su - oracle
sh /u01/app/oracle/admin/meta/scripts/meta.sh

Post-Database Installation Tasks


Edit the hosts's /etc/oratab. Do this as yourself, not as the oracle user.

sudo vi /etc/oratab

Configure SQL*Net


Switch to the oracle user and configure SQL*Net.

sudo su - oracle
vi $ORACLE_HOME/network/admin/tnsnames.ora

Configure a basic listener setup. The point here is just to start the listener. Don't forget that we already have a listener.ora template located in the $ORACLE_HOME/network/admin/samples directory.

vi $ORACLE_HOME/network/admin/listener.ora

Start the listener.

lsnrctl start

Next SQL*Net file to configure is the sqlnet.ora file.

vi $ORACLE_HOME/network/admin/sqlnet.ora

And the tnsnames.ora file.

vi $ORACLE_HOME/network/admin/tnsnames.ora

Configure Database Instance


Setup a few configuration parameters. Most of these will prevent errors from showing up in the Oracle Configuration Manager (OCM) inside My Oracle Support.

SQL> alter system set global_names=true scope=both sid='*';

SQL> alter system set smtp_out_server='127.0.0.1' scope=both sid='*';

SQL> alter system set log_checkpoints_to_alert=true scope=both sid='*';

SQL> alter system set os_authent_prefix='' scope=spfile sid='*';

You may have noticed that I use sid='*' for all the above statements. Since we're configuring a single instance database, we don't really need this. But I keep using it because this way it becomes a reflex and so when I'm working on a RAC database, I never forget it.

The following is going to push the undo retention period to way longer than the 900 default value. As it says in the Oracle Database Backup and Recovery User's Guide :
To ensure that the undo information is retained for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.
So let's crank this up to 48 hours (i.e. 172800 seconds).

SQL> alter system set undo_retention=172800 scope=both sid='*';

Next we need to boost the flashback retention period higher than the default value of 1440 minutes (i.e. 24 hours). If you have lots of disk space, then go on and set it quite high. Enough to last a long week-end (i.e. four days) or more. In this example, we will set it to 7200 minutes (i.e. 6 days).

SQL> alter system set db_flashback_retention_target=7200 scope=both sid='*';

We are now going to enable block change tracking. That's going to eat up a bit of disk space, but is going to help improve our RMAN backup times. For this to work, our block change tracking file has to be specified or the DB_CREATE_FILE_DEST parameter has to be set. In this example, we will use a file that will be placed in the same directory as the other database files. So we first check what that directory is?

SQL> select file_name from dba_data_files;

FILE_NAME

-------------------------------------------------------

/u02/oradata/meta/system01.dbf

/u02/oradata/meta/sysaux01.dbf

/u02/oradata/meta/undotbs01.dbf
/u02/oradata/meta/users01.dbf

Then create the file in the same directory.

SQL> alter database enable block change tracking using file '/u02/oradata/meta/block.change.tracking.dbf';

Check to see if it's properly configured?

SQL> select filename, status from v$block_change_tracking;

Prevent Account Expiration for Service Accounts


It’s old news, but oracle 11g expires passwords after 180 days and most DBAs don’t
like that. It’s unsecure, but for client-server-applications a locked account is not a single-user-annoyance, it’s a downtime killing SLAs, nerves and – hopefully not – DBA jobs. So just make your DEFAULT user profile less secure by allowing passwords to never expire.

SQL> select * from dba_profiles where resource_type='PASSWORD' order by resource_name;

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED;

Fix Bug 11891463

According to Metalink Doc ID 1361567.1 - Minact-Scn Master-Status: Grec-Scn Messages In Trace File, we need to fix a bug. Otherwise our diag_dest_dir directory will be filled by .trc and .trm files from the MMON process. To prevent that, issue the following command :

SQL> alter system set "_enable_minscn_cr"=false scope=spfile sid='*';

To enable this, we should bounce the instance right now. But since we still have some configuration work to do, let's wait until everything is configured before the instance restart.

Change Audit Parameters


I've already wrote on this topic in a previous blog post, but it was for RedHat 5 and we're on RedHat 6 here. So let's go over this again. We will change all of our databases audit trail to SYSLOG audit trails as it's the Oracle recommended best practices. The idea here is that logs sent to syslog will be placed inside a file to which neither the oracle user nor any of it's groups have write access. So in theory, the DBA and the Linux systems administrator are not the same person. Which means that if the DBA does something fishy, he won't be able to edit the audit trail to cover his or her actions.
We start by changing our /etc/rsyslog.conf file to send local0 messages to the Oracle audit trail. Recall that we are sending all our syslog data to a central syslog server. So we must change both our local rsyslog.conf file and the central syslog server's rsyslog.conf file. Then restart rsyslogd(8) on both machines.

Oracle RDBMS Server Syslog Configuration

We start by the configuration of rsyslogd(8) on the client machine. I say client machine because the Oracle RDBMS server is the syslog client to our central syslog server.


Create the new log file.

sudo mkdir /var/log/oracle
sudo touch /var/log/oracle/audit.log
sudo chmod go-r /var/log/oracle/audit.log

Make sure this new log file is rotated.

sudo vi /etc/logrotate.d/oracle.audit

Verify that the new logroate(8) configuration file is ok?

sudo logrotate -d /etc/logrotate.conf

Restart the rsyslogd(8) daemon to enable the changes.

sudo /etc/init.d/rsyslogd restart

Central Syslog Server Configuration

The central syslog server's configuration is quite similar as the one for the Oracle RDBMS server. In 11gR2 you can send more then one database audit logs to the same audit log file because they all log their DBID so you can differentiate them. Here I send all the the RDBMS audit logs to the same file.


Create the new log file.

sudo mkdir /var/log/oracle
sudo touch /var/log/oracle/audit.log
sudo chmod go-r /var/log/oracle/audit.log

Make sure this new log file is rotated.

sudo vi /etc/logrotate.d/oracle.audit

Verify that the new logroate(8) configuration file is ok?

sudo logrotate -d /etc/logrotate.conf

Restart the rsyslogd(8) daemon to enable the changes.

sudo /etc/init.d/rsyslogd restart

Exit from the central syslog server and go back to the Oracle RDBMS server.

exit
ssh opus.company.com
sudo su - oracle

Configure the database to send audit logs to syslog.

sqlplus '/ as sysdba'
SQL> show parameter audit;
SQL> alter system set audit_trail='OS' scope=spfile sid='*';
SQL> alter system set audit_syslog_level='local0.info' scope=spfile sid='*';
SQL> alter system set audit_sys_operations=true scope=spfile sid='*';

We just changed a lot of configuration parameters, let's make a copy of the spfile just to be safe. The first query will return the path of the spfile. The next one creates a text file copy of it.

SQL> select name, value from v$parameter where name = 'spfile';

SQL> create pfile = '/u01/app/oracle/admin/meta/pfile/pfile.ora' from spfile;

You may have noticed that the alter system audit parameters where all using the « scope=spfile ». That means we have to bounce the instance for the audit parameters to kick in. We run the show parameter audit query again after the restart to make sure our audit parameters are now online.

SQL> shutdown immediate;
SQL> startup;
SQL> show parameter audit;


NAME        TYPE     VALUE
---------------------- -------- --------------------------------
audit_file_dest        string   /u01/app/oracle/admin/meta/adump
audit_sys_operations   boolean TRUE
audit_syslog_level     string LOCAL0.INFO
audit_trail        string OS

Double check the audit trail now?

SQL> exit
tail /var/log/oracle/audit.log

You should get a « permission denied » here because you tried to access the audit trail as the oracle user. Try again as root via sudo.

exit
sudo tail /var/log/oracle/audit.log

Good!

One last thing before we're finished with the audit parameters. As Doc ID 308066.1 clearly explains, we will always get audit logs in our $ORACLE_BASE/admin/$ORACLE_SID/adump directory. So let's make sure it doesn't grow out of proportion by clearing the old audit logs. We do this in the oracle user's crontab.

sudo su - oracle

Finally, I like to use Jefferey M. Hunter's scripts. He's an Oracle ACE which has created management scripts for Oracle. They're interesting, so we install them. Grab a copy of the scripts and extract them to a central Oracle NFS directory.

mkdir -p /nfs/home/oracle/scripts/jeffrey.hunter

cd /nfs/home/oracle/scripts/jeffrey.hunter

wget http://www.idevelopment.info/data/Oracle/DBA_scripts/dba_scripts_archive_Oracle.zip

unzip /nfs/home/oracle/scripts/jeffrey.hunter/dba_scripts_archive_Oracle.zip

Then, create the ORACLE_PATH directory and extract the scripts into it.

mkdir -p $ORACLE_BASE/common/sql
cp /nfs/home/oracle/scripts/jeffrey.hunter/dba_scripts/sql/* $ORACLE_BASE/common/sql

Oracle Configuration Manager

OCM comes built-in with 11gR2, so we will configure it right now. OCM requires Java JRE to be installed. So go to the Oracle Java website and download the latest Java SE package and install it (follow the instructions on the Oracle website).

Once you have Java JRE installed, configure OCM. You will need your Oracle Support Contract number and the email you use to connect to My Oracle Support. In this example, the contract number and the email is bogus, as this is just an example...

$ORACLE_HOME/ccr/bin/setupCCR 973649 david.robillard@company.com

This will deploy OCM and print out some info about what it's doing and what are the next steps you need to execute. One of those steps is to configure the daily collection interval. The idea here is to use different collection times for the various machines on your network so that they don't connect to My Oracle Support at the exact same time.

$ORACLE_HOME/ccr/bin/emCCR set collection_interval="FREQ=DAILY; BYHOUR=6; BYMINUTE=20"

We can now check the crontab to see what was installed by the emCCR command?

crontab -l
0,15,30,45 * * * * /u01/app/oracle/product/11.2.0.3/dbhome_1/ccr/bin/emCCR -cron -silent start

For each of the database instances running on the machine, execute the post-installation script.

$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig -s meta

Test then start a collection.

$ORACLE_HOME/ccr/bin/emCCR test
$ORACLE_HOME/ccr/bin/emCCR collect

Now login to My Oracle Support and check the Systems tab, you should now see your instance listed there.

Startup Scripts


Let's create startup scripts for the listener and the database. We will include those new scripts in the RedHat startup procedure. So, as your own user, create the Oracle listener startup script.

sudo chkconfig oracle.listener on

And then the Oracle RDBMS startup script.

sudo chkconfig oracle.db on

Double check to see if the new scripts are part of the RedHat system's startup configuration?

chkconfig --list | grep oracle

Be sure to test both scripts!

Reboot your server to see if the database and the listener are up without any intervention on your part.

sudo shutdown -r now

Install Latest Patch Set


Doc ID 756671.1 - Oracle Recommended Patches -- Oracle Database
Doc ID 293369.1 - Master Note For OPatch
Doc ID 1348336.1 - 11.2.0.3 Patch Set - Availability and Known Issues
Doc ID 13923374.8 - Bug 13923374 - 11.2.0.3.3 Patch Set Update (PSU)

As of this writing, the latest patch set is 11.2.0.3.3. So download the patch set update. Get the SHA1 checksum also.

echo "34E9FA2627E06791C8D5DC84C2DCA2090F8B5256" > ~/Downloads/p13923374_112030_Linux-x86-64.zip.sha1

Compare both SHA1 values from the downloaded file and the one found on My Oracle Support.

openssl dgst -sha1 ~/Downloads/p13923374_112030_Linux-x86-64.zip
cat ~/Downloads/p13923374_112030_Linux-x86-64.zip.sha1

If the SHA1 values are the same, then move everything to the staging directory.

mv ~/Downloads/p13923374_112030_Linux-x86-64.zip* /nfs/install/oracle/linux/x86_64

Now download the latest OPatch version and check the SHA1 value.

echo "C0B9E5566DDBDFFD3076735F429587EC8CE9EF18" > ~/Downloads/p6880880_112000_Linux-x86-64.zip.sha1
openssl dgst -sha1 ~/Downloads/p6880880_112000_Linux-x86-64.zip

Move the latest OPatch to the staging directory.

mv ~/Downloads/p6880880_112000_Linux-x86-64.zip* /nfs/install/oracle/linux/11.2/x86_64/

Connect to the database server and check the status of the current OPatch.

ssh opus.company.com
sudo su - oracle
$ORACLE_HOME/OPatch/opatch version
OPatch Version: 11.2.0.1.7

Extract the latest OPatch directly into the $ORACLE_HOME.

unzip /nfs/install/oracle/linux/x86_64/p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME

Then check the new version of OPatch.

$ORACLE_HOME/OPatch/opatch version
OPatch Version: 11.2.0.3.0

Good, we moved from version 11.2.0.1.7 to version 11.2.0.3.0. We can now use this new OPatch to install the latest Patch Set Update (PSU). Let's extract this patch.

unzip /nfs/install/oracle/linux/x86_64/p13923374_112030_Linux-x86-64.zip -d /tmp

Change to the patch directory and check if it conflicts with the currently installed patches. There shouldn't be any because we just installed 11.2.

cd /tmp/13923374
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Prereq "checkConflictAgainstOHWithDetail" passed.

We are now ready to install the patch.Shutdown all instances and listeners running from that Oracle home. Of course, if this database is in production, make sure you let people know that the database will not be availalble for a short period of time.

echo "shutdown immediate;" | sqlplus '/ as sysdba'
lsnrctl stop

We can now apply the new patch.

cd /tmp/13923374
opatch apply

Once patch installation is finished, run the following scripts in each of the database instances running from this Oracle home.

rlwrap sqlplus '/ as sysdba'
SQL> startup;
SQL> @?/rdbms/admin/catbundle.sql psu apply
SQL> exit

Start the listener again.

lsnrctl start

Configure Recovery Manager (RMAN)


If your site has Oracle RDBMS systems, chances are that you already have a Recovery Manager Catalog. Let's assume you have one and configure the new database with it.

sudo su - oracle
rman target sys@meta catalog rman@rman

RMAN> register database

RMAN> configure retention policy to recovery window of 10 days;
RMAN> configure backup optimization on;
RMAN> configure controlfile autobackup on;
RMAN> configure device type disk parallelism 2 backup type to copy;
RMAN> configure compression algorithm 'basic' as of release 'default' optimize for load false;
RMAN> configure archivelog deletion policy to backed up 2 times to disk;
RMAN> configure snapshot controlfile name to '/u01/app/oracle/admin/oprod/controlfile/snapshot_cf.rman';
RMAN> show all;

RMAN configuration parameters for database with db_unique_name META are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'basic' AS OF RELEASE 'default' OPTIMIZE FOR LOAD FALSE;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/admin/meta/controlfile/snapshot_cf.rman'; # default

Once the database is configured, we can already create a first backup. This example uses Incrementally Updated Backups. See these Document ID for more info :

Doc ID 360416.1 - Oracle10g / 11g - Getting Started with Recovery Manager (RMAN)
Doc ID 351455.1 - Oracle Suggested Strategy & Backup Retention
Doc ID 303861.1 - Incrementally Updated Backup In 10G
Oracle Database Backup and Recovery User's Guide 11g Release 2 (11.2) Chapter 9 - Backing Up the Database - Making and Updating Incremental Backups
Oracle Database Backup and Recovery Reference 11g Release 2 (11.2) - BACKUP
Oracle Database Backup and Recovery Reference 11g Release 2 (11.2) - Example 9-10 Advanced Incremental Update Script

Let's create a few directories before we can perform a backup. A good procedure is to create the backup directory on an NFS share and not be local to the machine. This way if ever there was a problem with the database server, the data on the NFS share. Or if the NFS server or NAS system has a problem, you still have the database intact. The idea is not to put all your eggs in the same basket ;)

So let's assume that we already have a NAS share which is automounted on the database server on /backup/oracle, we could run this to create an incrementally updated backup of the database :

RMAN> run {
2> sql "create pfile=''/backup/oracle/meta/pfile.txt'' from spfile";
3> sql "alter database backup controlfile to trace as ''/backup/oracle/meta/controlfile.before.backup.txt'' reuse";
4> recover copy of database with tag DAILY until time 'sysdate - 1';
5> backup as compressed backupset incremental level 1 for recover of copy with tag DAILY database;
6> backup as compressed backupset archivelog all delete all input;
7> sql "alter database backup controlfile to trace as ''/backup/oracle/meta/controlfile.after.backup.txt'' reuse";
8> backup recovery area to destination '/backup/oracle/rman/meta';
9> crosscheck backup;
10> delete noprompt obsolete;
11> }

Once in RMAN, we can also check the database schema :

RMAN> report schema;

Report of database schema for database with db_unique_name META

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    612      SYSTEM               ***     /u02/oradata/meta/system01.dbf
2    666      SYSAUX               ***     /u02/oradata/meta/sysaux01.dbf
3    393      UNDOTBS1             ***     /u02/oradata/meta/undotbs01.dbf
4    5        USERS                ***     /u02/oradata/meta/users01.dbf
5    14       RMAN                 ***     /u02/oradata/meta/rman01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u02/oradata/meta/temp01.dbf

So that's it for now.

HTH,

DA+

18 comments:

  1. Thank you very much for this. it is clear and complete. I learned a lot from this documentation and it a thousand times better than the oracle docs. I made only one minor change to the db startup script and added a script to startup EM and everything is wroking great! Even though this has been around for quie a while people still use it; or at least i did.

    ReplyDelete
    Replies
    1. Hello Anonymous,

      I'm glad this post was good for you! May I ask what changes you did to the startup scripts?

      And thanks for the good words :)

      DA+

      Delete
    2. The change was pretty trivial; for the startup command pipped into sqlplus my rhel6.3 system didn't like the single quotes in '/ as sysdba'. When it balked at that I played around with it on the command line for a couple minutes figured out it was the single quotes; updated the script, kicked it and everything worked. Fyi-the same basic script as the oracle.listener i converted into an oracle.em script and it starts the dbconsole just fine, you might consider adding that (emctl start dbconsole), i assumed the run states would all be the same for it, certainly don't see a reason for it not to be on at least 3 and 5.

      I really enjoyed this; because before hand i'd never played with the chkconfig utility oh rhel; and now i know how to use and apply it generally after playing with your stuff. Not to discount the other oracle stuff and the things about the semaphore settings and environment pre-prep.

      I'm not a proper admin, i do dev and test work which means i play with all kinds of stuff and often know only just enough (or not quite just enough!) to get this kind of stuff working. Blogs like this give me an opprotunity to learn how a lot more stuff works since i generally don't get the opprotunity to take classes, etc for these kinds of things. So sincerly, thanks for sharing your knowledge.

      Delete
    3. Hi Fred,

      Ok cool, nice to know the quoting game :)

      And thanks again for the good words. I like to write about what I do and how I do it. But having people actually taking the time to say thanks is very gratifying!

      Cheers,

      DA+

      Delete
  2. Small typo: instead of "ssh ~/.ssh/id_rsa.pub opus.company.com:/tmp" should be "scp ~/.ssh/id_rsa.pub opus.company.com:/tmp"
    But the whole doc is excellent!!!
    Thanks,
    Mike

    ReplyDelete
    Replies
    1. Hi Mike,

      Thanks for the info, I'll fix the typo right away. And thanks for the good words, I'm glad you liked it :)

      Cheers,

      DA+

      Delete
  3. I would consider installing the 'oracle-rdbms-server-11gr2-preinstall' package to install the dependent packages and perform the system configuration updates/tweaks. Overall, excellent write-up. I'm going to use your steps to script a database install for my environment.

    Thanks!

    ReplyDelete
    Replies
    1. Hello Indranil Chakraborty,

      I must confess I don't know about the 'oracle-rdbms-server-11gr2-preinstall' package. Is this a MOS download? And what does it do? Did I miss anything in this blog that this package does?

      Thanks for the good words, I appreciate. Glad I could help!

      DA+

      Delete
    2. David,
      Look at the blog entry at https://blogs.oracle.com/linux/entry/oracle_rdbms_server_11gr2_pre

      It explains what the package does. In essence, by installing this package, it downloads and installs other pre-requisite packages and also makes the needed system config changes (such as ulimits, etc.). Much easier than manually having to download packages individually and installing them, or making the sys configuration changes manually.

      HTH,
      Indranil.

      Delete
    3. Hello Indranil,

      Sounds interesting. Thanks for letting me know.

      DA+

      Delete
    4. Indranil,
      As per the above link:
      "Now that the certification of the Oracle Database 11g R2 with Oracle Linux 6 and the Unbreakable Enterprise Kernel has been announced, we are glad to announce the availability of oracle-rdbms-server-11gR2-preinstall, the Oracle RDBMS Server 11gR2 Pre-install RPM package (formerly known as oracle-validated). Designed specifically for Oracle Linux 6, this RPM aids in the installation of the Oracle Database."

      So does that mean this pre installer package will work only with Oracle Linux 6 and not RHEL. I understand that Oracle Linux is almost similar to RHEL, but just wanted to confirm.

      Delete
    5. Hello gdbdba,

      I've never used neither the aforementioned rpm nor Oracle linux. So I can't say. Have you tried to read the release notes or the admin guide that usually comes with Oracle products?

      HTH,

      DA+

      Delete
    6. David,
      Yes i went through the realease notes, and it says:
      "Oracle is pleased to announce the general availability of the Oracle RDBMS Server 11gR2 Pre-Install RPM for Oracle Linux 6 x86_64 (64 Bit) architecture."
      Per Wikipedia:"Oracle Linux, formerly known as Oracle Enterprise Linux, is a Linux distribution based on Red Hat Enterprise Linux, repackaged and freely distributed by Oracle, available under the GNU General Public License (GPL) since late 2006."
      so , i was trying to check if someone has used the same package on RHEL 6.
      I will try using this Package on RHEL 6.4 and post my keep you posted.
      Regards,
      Gagan

      Delete
    7. Hi Gagan,

      Good initiative, I'm looking forward to know if it works with RHEL 6.4.

      Thanks,

      DA+

      Delete
    8. Yes , i was able to use the Package on RHEL 6.4 and it installed the pre requisite packages , set the kernel parameters. I ran into the oui error related to the pdksh package and was able to resovle it using your instructions.Also it does not make changes to the It does not add line in /etc/pam.d/login file to add pam_limits.so

      Delete
    9. Hey that's excellent news. Thanks for your help!

      Delete
  4. David,
    Thanks for sharing the detailed step by step Instructions.
    Regards,
    Gagan

    ReplyDelete
  5. This tutorial is indeed useful. I have just downloaded it. Thanks again.
    gagan enterprises

    ReplyDelete