Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Oracle RMAN Backups

Oracle RMAN Backups

How to redesign the setup and scheduling of Oracle RMAN backups the “easy” way, using Oracle Enterprise Manager

The day has come when DBA teams can no longer afford to ignore Oracle Enterprise Manager Grid Control (current release being 10g Release 5 at the time of this writing). It is not practical to do so, due to the immense complexity of today’s Oracle enterprise database software. The core database facilities are becoming more and more sophisticated, and it is extremely tedious to use only the command line to manage these complex options. Some DBAs may write and maintain scripts to do so; others may buy scripts from the market, and such scripts may not be guaranteed to work for new releases of the database. Thus, they have to be endlessly tested and maintained.

Furthermore, Oracle strongly recommends the use of Oracle Grid Control for managing sophisticated options like its fully active-active clustered database option, Oracle Real Application Clusters (Oracle RAC). This is so especially in the area of analyzing cluster performance, which is greatly aided by Oracle Grid Control.

In this article, you will learn the traditional use of UNIX shell scripts and crontab to set up and schedule Oracle Recovery Manager (Oracle RMAN) backups, including a technical explanation of the supplied scripts (in Part I). You will also see how the same end result of setting up and scheduling Oracle RMAN backups can be achieved seamlessly and easily via Oracle Grid Control, without using any UNIX shell scripts or cron (in Part II). The demonstration presented in this article is drawn from the real-life experiences of a DBA working at a large corporate site, who used both approaches in his career history.

Background

When the earliest Oracle databases were being backed up, they were offline (“cold”) backups. The DBA manually performed the backup as an OS file copy of all database files after shutting down the database. Some enterprising administrators started writing UNIX shell scripts to do the work of shutting down the database, copying the files using OS commands, and then starting the database again. The traditional UNIX scheduler cron was used to call the script at the appropriate time. So there was some level of automation, if one discounted the manual effort of writing, implementing, and testing the scripts. They also had to maintain the scripts for changes—the database name might change, or more databases might appear on the same server that would need to be backed up, so the scripts would need to be modified.

In Oracle Database Version 6, online (“hot”) backups were introduced. This meant that the database could stay open and process transactions at the same time as the backup was being performed. But the caveat was that each tablespace had to be placed in a special backup mode before the OS file copy could take place. So, the DBAs modified their scripts to connect to the database, get a list of tablespaces, put each tablespace in backup mode, perform the OS file copy backup, and finally take the tablespaces off backup mode. The scripts were becoming slightly more complex.

Eventually in Oracle Database Version 8, there was a groundbreaking event—Oracle Recovery Manager (RMAN) was introduced as the recommended backup method for Oracle databases. However, even though it was technically superior to the older backup mode online backups, there was some resistance to Oracle RMAN due to Oracle’s initial requirement for a separate catalog database to hold a history of the backups. Many DBAs thought it was illogical to have a second database to back up the first database.

Oracle realized this and soon made it possible to use the control file of the database itself to hold the history of the backups. This reduced the reliance on a separate catalog database. In Oracle Database Versions 8i, 9i, and 10g, the result was that the control file method of Oracle RMAN backups started being accepted by mainstream DBAs, and Oracle RMAN started being used to a greater extent on production databases.

However, because UNIX shell scripts and cron had been used in the past to execute and schedule offline and online database backups, Oracle RMAN inherited this history. The older UNIX shell scripts were modified to connect to Oracle RMAN and perform the backup. There was no need any longer to put the tablespaces in backup mode, but  everything else remained the same.

So, for a number of years, the traditional approach to Oracle’s RMAN utility was exactly the same as before: the use of UNIX shell scripts and cron.

In this age of sophisticated database management tools such as Oracle Grid Control, this may seem archaic, but there are a large number of companies who still use a shell script to call Oracle RMAN, and there are some who use layer upon layer of shell scripts.

Oracle purposely simplified the Oracle RMAN syntax to make the job of backup and recovery much easier, but the purpose is defeated if the Oracle RMAN commands are hidden under sublayers of OS shell scripts. Even experienced DBAs who walk into these large companies find it difficult to understand the customized shell scripts, although they know how to use Oracle RMAN.

Thus productivity in such companies is reduced, and the act of debugging errors is made much more difficult due to the script layers. And if the scripts are to be modified in the future to cater to changed requirements, there are high maintenance costs.

Next, on to the comparative approaches.

Part I: The Traditional Approach

For the purpose of our demonstration, the production server is raclinux1 and the database on this server is RACDB, a production Oracle database with the company’s financial information. The DBA has been asked to set up and schedule Oracle RMAN backups for this database. The DBA writes the shell scripts and places them on the server following the setup steps below.

These steps must be followed for every database server requiring Oracle RMAN backups for its databases. If the databases are on an active-passive cluster—for example, a SUN HA cluster or any such technology—then the steps would be followed for each server.

  1. As  the root UNIX user add the line “oracle” to /etc/cron.d/cron.allow in order to allow the oracle UNIX user to use the cron utility in UNIX.
  2. As the oracle UNIX user, add the following to the crontab:
30 21 * * * dba/scripts/rman_backup_db.sh RACDB

As per crontabl syntax, this calls the rman_backup_db.sh script at 21:30 hours on each day. The script is asked to execute against the RACDB database by specifying this database name as the first and only argument.

  1. Log on as a DBA to database RACDB in SQL*Plus and create an externally identified user :
create user ops$oracle identified externally;

This has the effect that the Oracle UNIX user can log on to SQL*Plus as the Oracle database user without specifying the password—in other words, the user is identified externally. Note that SYSDBA database privileges are needed for taking RMAN backups, so the Oracle UNIX user should be a member of the dba UNIX group.

This is the technique used most often with scripts that log in to the database, especially those that require DBA rights for database-level backups. It is not a good idea to hard-code database passwords in scripts, because the scripts are UNIX files and may be readable by anyone on the computer (unless the file permissions are locked down). So as a safety precaution, an externally identified Oracle user can be used to log in as the DBA and perform the backup. This circumvents the need for specifying the database password in the script.

  1. In this approach you would use a Filer volume for the Oracle RMAN backups. Database standards for the corporate environment maintain that the backup volume must be mounted as /U01 at the server level.

In /etc/vfstab, make sure that there is an entry for the backup mount point:

ausmelb-corp-netappsfiler-tier3:/vol/vol1/dbbackup – /U01 nfs –  yes

hard,vers=3,intr,suid,proto=udp,rsize=32768,wsize=32768

NetApp documentation should be consulted on the appropriate mount options, because these may differ as per the UNIX flavor of the host, the version of the filer, and the technology in use.

If there is no such entry, consult with the storage department in corporate IT and ask them to allocate a volume to the server and add it to this file. The mount point will now be mounted every time the server is rebooted, or it can be mounted manually using the mount command as the root UNIX user:

mkdir /U01

mount /U01

  1. As the root UNIX user, execute the following at the UNIX command prompt:
chown –R oracle:dba  /U01

This is to make sure that the /U01 backup mount point and all subdirectories (as specified by the –R argument) under this mount point are owned by the oracle UNIX user and dba UNIX group, so Oracle RMAN is able to create backup pieces under this mount point.

  1. Assuming that /home/oracle is the Oracle UNIX user’s home directory, perform the following steps as the Oracle UNIX user:
mkdir /home/oracle/dba

mkdir /home/oracle/dba/scripts

mkdir /home/oracle/dba/logs

mkdir /home/oracle/dba/work

mkdir /U01/RACDB

mkdir /U01/RACDB/rmancmd

mkdir /U01/RACDB/log

These commands create the UNIX subdirectories into which the UNIX script is placed and to which the logs are written, as well as creating temporary working directories and the directories for the generated Oracle RMAN command script and Oracle RMAN runtime log file.

  1. Under the subdirectory /home/oracle/dba/scripts, create the file rman_backup_db.sh using vi or any UNIX editor. The script, when executed every day at the specified time set in cron, will generate the following Oracle RMAN command file /U01/RACDB/rmancmd/rman_RACDB.cmd:
#

#

# Configure RMAN settings

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE  DISK TO ‘$BACKUPDIR/cf_%F’;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEVICE TYPE disk PARALLELISM 3;

CONFIGURE DEFAULT DEVICE TYPE TO disk;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT  ‘$BACKUPDIR/b_%U’;

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT  ‘$BACKUPDIR/b_%U’;

CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT  ‘$BACKUPDIR/b_%U’;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO  ‘$BACKUPDIR/snapcf_${DBNAME}.f’;

# Perform backup of database and archivelogs, deleting  backed up archivelogs

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

# Maintainance commands for crosschecks and deleting  expired backups

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

CROSSCHECK BACKUP;

DELETE NOPROMPT EXPIRED BACKUP;

DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;

CROSSCHECK  ARCHIVELOG ALL;

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

# End of RMAN command script

As can be seen from the Oracle RMAN commands, a recovery window of three days is specified as the Oracle RMAN retention policy, which means that all backups necessary to recover up to the last three days of data will be retained. Obviously this setting may need to be changed, depending on the size of the database and the free space available in the backup mount point /U01 at any point. The DBA needs to closely monitor this space and change the recovery window if necessary.

Control file autobackup is also configured, so the control file will be automatically backed up along with every database or archive log backup. This is especially important because we are using the nocatalog mode of Oracle RMAN, so all the history of our backups is only in the control file.

A parallelism setting of 3 is used, which means that three Oracle RMAN channels will be used to actually create the backup pieces. This will increase the speed of the backup, depending on the structure of the disk subsystem.

A full backup of the database and the archive logs is then performed, and the archive logs that are backed up are deleted immediately by Oracle RMAN due to the delete input clause.

After the backups are completed, maintenance jobs are performed that cross-check the existing backups and archive logs and make sure that they are present on-disk. All expired (not found) and obsolete (as per the retention policy) database, archive log, and control file backup entries are deleted from the control file records. The maintenance commands also ensure that the obsolete database backups and control file autobackups are physically deleted from the disk.

  1. Make the rman_backup_db.sh script executable at the UNIX level by setting the user-executable file permission:
cd /home/oracle/dba/scripts

chmod u+x rman_backup_db.sh

  1. Test out the rman_backup_db.sh script by calling the script from the UNIX command prompt manually, instead of waiting for the cron scheduled execution to take place:
cd /home/oracle/dba/scripts

./rman_backup_db.sh RACDB

This should execute the Oracle RMAN command script on the RACDB database and take a complete database and archive log backup. After the backup is completed, inspect the Oracle RMAN log file generated to make sure that there are no errors and that the backup has completed successfully with Oracle RMAN backup pieces created in the backup directory /U01/RACDB.

  1. On the next day, make sure that the scheduled Oracle RMAN backup in the UNIX cron has been started and completed successfully. If not, correct the cron entry.

Conclusions

As you can see, there are a number of manual steps in the traditional approach. The DBA using this approach estimates that two to three hours will be required to set up and test the Oracle RMAN backup in this manner, on every new provisioned computer.

Of course a rush job can be performed, but because rushing is more prone to human error, management takes two hours as the final estimate. This is the time consideration.

If there are a number of Oracle RMAN deployments happening on projects every week in a large corporate site, the time quickly adds up to a number of expensive DBA man-days.

The other consideration is the maintenance aspect of the UNIX shell scripts. Management must retain DBAs who are familiar with UNIX shell scripting. In some cases, the initial scripts have been written in Perl. So there would be a need for familiarity with that language. Because the choice of scripting language is open, there may be sites using other scripting languages as well. Also, scripts may be written in totally different ways to do the same job, because there are no scripting standards in place. The code may be totally uncommented or sparsely commented, and there may be missing or incomplete documentation. Everything is up to the initial script writer. New DBAs inherit the scripts and must try their own hand at fixing things, spending many hours understanding the scripts, experimenting with the code, and making any future maintenance changes. These are very common scenarios in the real world.

Oracle Enterprise Manager Grid Control aims to eliminate most of these issues. Next, we will see how to set up and schedule Oracle RMAN backups in a straightforward, no-nonsense way using the vast capabilities of Oracle Enterprise Manager Grid Control.

Part II: The Oracle Enterprise Manager Grid Control Approach

As noted previously, the major benefit of this approach is the time savings achieved in the tasks of setting up, testing, and day-to-day maintenance of the Oracle RMAN backups. Removing the dependence on UNIX-level scripts eliminates the necessity of hiring and retaining shell script specialists. Using Oracle Grid Control results in greatly simplified debugging of errors, because there are no shell scripting issues—the DBA can concentrate solely on the Oracle RMAN commands. Future maintenance and changes to the Oracle RMAN backups will be easier.

This full comparison of both approaches, when demonstrated in the real world,  has already convinced many major companies and financial institutions to consider the use of Oracle Grid Control for Oracle RMAN backups and other database management tasks.

For the purpose of this demonstration, the production database is RACDB—a production Oracle database containing the company’s financial data. The DBA’s task is to set up and schedule Oracle RMAN backups for the database.

The DBA has access to a central Oracle Grid Control site for managing and monitoring the multiple database targets for which his/her team has responsibility. The presumption is that the Oracle Grid Control site has been deployed using best practices and scalable architecture, so it can handle multiple database management tasks such as Oracle RMAN backups, Oracle Data Guard setup and monitoring, database cloning, and so on.

It is always preferable to use the latest version of Oracle Grid Control at the central site. To find the exact version of Oracle Grid Control installed, go to the Oracle Grid Control home page and select the About Oracle Enterprise Manager link at the bottom of the page. In the case of this demonstration, the version is Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0 which was the latest version available at the time of installing the demonstration.

Often security is cited as a concern for a central management site; what if one group of DBAs gains access to production databases for which they are not responsible? Oracle Grid Control, however, is sophisticated enough to allow the creation of multiple administrator logins who have access to specific groups of targets. This allows multiple database teams in the corporate environment to log in and only have management access or view access to their own group of targets—whether it be database, application server, host server, or listener.

Thus potentially any of the DBA team members responsible for the RACDB database could log in to the Oracle Grid Control console with their administrator login and would have Oracle Grid Control management rights to the RACDB database, host, and associated listener targets.

Preinstall Tasks

Initially, there are a few preinstall tasks required to set up the environment, as follows:

  1. A separate Filer volume will be used for the Oracle RMAN backups. As per corporate database standards, the backup volume must be mounted as /U01 at the server level. The entry for the backup mount point should be present  in /etc/vfstab, and the mount point should appear in the list when the df –k (disk free in Kilobytes) command is executed at the UNIX prompt. If not, follow the UNIX-level steps as the root UNIX user, to add the entry in /etc/vfstab, and then mount the volume as /U01. Also change the ownership via this command:
chown –R oracle:dba   /U01
  1. This is to ensure that the /U01 backup mount point and all subdirectories (as specified by the –R argument) under this mount point are owned by the Oracle UNIX user and DBA UNIX group, so that Oracle RMAN is able to create backup pieces under this mount point.
  2. The Oracle Enterprise Manager Agent is preinstalled on each database server that is to be accessed via Oracle Grid Control. The most popular method to perform this install is via the pull method, which uses an AgentDownload script and the wget utility to pull the Agent install files from the central site directly onto the server. Other common methods are the push method, as well as the normal GUI installation using the CD or software directly on the target server.

Ideally, if the Provisioning Pack of Oracle Grid Control is used, it is possible to keep a “Gold Copy” of Oracle installations, in this case the Agent can already be preinstalled in the gold copy. The gold copy is then provisioned onto new servers.

The Agent is to be installed in its own Agent home on the server, for which at least 2GB of space should have been allocated. Once installed and configured, the Agent starts to upload XML files to the central Oracle Management Service (OMS) server, and metrics about the targets on this database server begin to be stored in the central Oracle Grid Control repository.

  1. In Oracle Grid Control, a collection of associated targets is called a target group. This is normally used for grouping together targets to be associated with a particular corporate department, application, or DBA team.

Such a target group, FINGRP, has been created by the Oracle Grid Control owner SYSMAN (password-controlled by the super-administrator of the central Oracle Grid Control site, normally a DBA of the central database team). This target group contains the RACDB database, its listener, and also the host the database runs on.

A new Oracle Grid Control administrator, FINADM, has also been created. This administrator is assigned full management privileges over the target group FINGRP.

The following steps must now be followed for the database requiring Oracle RMAN backups to be set up and scheduled. If the database is on an active-passive cluster—for example, a SUN HA cluster or any such technology—then these steps would be followed for each server, because the database could potentially run on either of the nodes in this active-passive technology and have a different target name for each node in Oracle Grid Control. In the case of Oracle RAC there is no such issue, because there is only one database shared by both or multiple nodes, and it can be backed up easily and efficiently by Oracle RMAN.

Setup Steps

  1. Log in to the Oracle Grid Control console using the FINADM login. You are able to see only the targets for which your team is responsible. Move to the Targets tab, click Databases, and then select the RACDB database. This now displays the RACDB database home page.

The name of the user logged on to the console is clearly visible in the topmost bar of the browser as Oracle Enterprise Manager (FINADM) – Database Instance RACDB.

  1. Select the Maintenance tab. On this tab, under High Availability, the options visible are Backup/Recovery, Backup/Recovery Settings, Oracle Secure Backup, and Data Guard. Select Backup Settings under Backup/Recovery Settings. At this point, the Oracle Grid Control login FINADM is asked to log in to the actual database itself.

Log in as a user with DBA privileges, to set up and schedule the Oracle RMAN backup. It is not required to log in with SYSDBA or SYSOPER rights unless the intention is to start up, shut down, perform complete or incomplete recovery, change the archive log mode, or perform other such database administrative work. Connect as NORMAL instead.

Do not check Save as Preferred Credential,because that would mean the login would be available without a password to any DBA who has logged on to the console as FINADM.

Note that this procedure signifies that there are two levels of security in Oracle Grid Control—the first level to log in to the console and the second to log in to the database itself.

  1. Backup Settings is now displayed as three tabs: Device, Backup Set, and Policy. Under Device, enter 3 as the Parallelism (concurrent streams to disk drives). This means that three Oracle RMAN channels will be used to actually create the backup pieces. This will increase the speed of the backup, depending on the structure of the disk subsystem.

The Disk Backup Location is not specified, so it uses the flash recovery area set up at the database level for the purpose of the backup. In this case, we presume that the flash recovery area of the database has been set up as /U01 as per the corporate standard.

  1. The Disk Backup Type has three radio buttons. You can ask for either a Backup Set, a Compressed Backup Set, or an Image Copy. Compressed Oracle RMAN backups were first made available in Oracle Database 10g. Select this option for the most optimal use of backup space.
  2. Enter the host credentials on the lower portion of this tab, and check Save as Preferred Credential. For a database based on a UNIX host, use the Oracle UNIX user. For a windows host, use <hostname>\<username> to log on.

The logon credentials in Windows occasionally do not work, even though \ is used. The solution is to add the Log on as a Batch Job privilege to the user in the Windows Control Panel -> Administrative Tools -> Local Security Policy -> Local policies -> User rights assignment -> Log on as a Batch Job, and add the Windows user that is being used in the host credentials.

Note that the menu path mentioned here pertains to Windows XP Professional and may be slightly different for other Windows versions. This solution is as per Oracle MetaLink Note 109188.1.

  1. To make sure that the host credentials work correctly, click Test Disk Backup at the upper corner of the screen. This runs a physical backup test using Oracle RMAN and verifies that the actual backup can be physically created at the disk backup location, which is the flash recovery area—/U01 in this case. The backup test runs and, when completed successfully, displays the message Disk Backup Test Successful! at the top of the Backup Settings -> Device tab.
  2. Move to the Backup Set tab. At this point, nothing is to be changed on this tab, which specifies the maximum backup piece (file) size and certain tape settings such as copies of datafile and archive log backups. These settings are kept at the default.
  3. Move to the Policy tab. This is where the backup policy is specified, and it is particularly important for the control of Oracle RMAN backups.

First check the box to Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change.

This is the control file and SPFILE autobackup. You can specify the location of the autobackup or default to the flash recovery area /U01. Checking this option is highly recommended so that the control file and SPFILE will be automatically backed up along with every database or archive log backup. This is especially important because we are using the nocatalog mode of Oracle RMAN, so the history of our backups is in the control file. This is also another reason that the binary SPFILE is recommended for every database instead of the earlier text PFILE, because the SPFILE can easily be backed up by Oracle RMAN in this way.

Check Optimize the whole database backup by skipping unchanged files such as read-only and offline datafiles that have been backed up.This is known as backup optimization.

Check Enable block change tracking for faster incremental backups. This is a new feature inOracle RMAN 10g that keeps track of all changed blocks in a small file (approximately 11MB) so that incremental backups do not need to scan the whole database for block changes, as was the case in Oracle9i Database. This technique results in a much faster incremental backup, making a compelling reason to upgrade to the latest versions of the database.

For the block change tracking file, specify the location and filename as D:\ORADATA\RACDB\BLKCHGTRCK.DBF. This must be specified because the database area is not set for the RACDB database.

Under Retention Policy, select Retain at least the specified number of full backups for each datafileand specify 1 as the number of backups to retain. This means that the retention policy is based on redundancy instead of a recovery window.

The redundancy is specified in terms of the number of backups to keep at any one time. If a new backup is made, then the previous backup is marked as obsolete and can be deleted. The recovery window is specified in terms of the number of days; backups are retained so that recovery is possible up to this many days in the past. For example, if a recovery window of 3 days is specified as the Oracle RMAN retention policy, it means that all backups necessary to recover up to the last three days’ worth of data will be retained. Obviously this setting may need to be changed depending on the size of the database and the free space available in the backup mount point /U01 at any time. The DBA needs to closely monitor this space and change the recovery window if necessary.

  1. Click OK; this saves the backup settings. The settings are actually saved in the control file of the database. This can be confirmed by logging in to Oracle RMAN at the command prompt and looking at the configuration settings via the show all command:
— Set the Oracle Sid in Windows

C:\>set %ORACLE_SID%=RACDB

— Move to the 11g Oracle Home

C:\>cd C:\app\GJH\product\11.1.0\db_1\bin

— Start RMAN in the nocatalog mode connecting to the setOracle Sid

C:\app\GJH\product\11.1.0\db_1\BIN> rman target=/nocatalog

Recovery Manager: Release 11.1.0.6.0 – Production on SatJun 14 22:01:47 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: RACDB (DBID=3660855775)

using target database control file instead of recoverycatalog

RMAN> show all;

RMAN configuration parameters for database withdb_unique_name RACDB are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPEDISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSEDBACKUPSET PARALLELISM 3;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;# default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘BZIP2’; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO’C:\APP\GJH\PRODUCT\11.1.0\DB_1\DATABASE\SNCFRACDB.ORA’; # default

It is obvious that the changes in the Oracle Grid Control Backup Settings screen have filtered down to the configuration settings in Oracle RMAN, which are stored in the control file of the RACDB database. The settings without the # default comment are the ones that have changed. For example, the backup optimization is shown as ON, the control file autobackup is shown as ON, and parallelism is set to 3 as a compressed backup set—all settings that were changed in Oracle Grid Control.

  1. Now click Schedule Backup on the Maintenance tab of the RACDB database. Under Customized Backup, select Whole Database and click Schedule Customized Backup.

It is also possible to back up individual tablespaces, datafiles, or only the archive logs when selecting the Customized Backups. Another option is to back up All Recovery Files on Disk; this would back up the flash recovery area itself to tape.

Oracle’s visionary approach is to back up the database to disk—in other words, the flash recovery area—to enable fast disk-based recovery for the client and to minimize business downtime. This is the main purpose of the flash recovery area.

Once a week or at any other interval of time, the disk-based database backup—in other words, the flash recovery area—can be backed up to tape using this option.

  1. On the Scheduled Customized Backup: Optionspage, select Full Backup, and Online Backup as the backup mode.

Under Advanced,check the following options:

    • Also back up all archived logs on disk
    • Delete all archived logs from disk after they are successfully backed up
    • Delete obsolete backups

The first two options enable the backup of all archive logs along with the full database backup, and the deletion of  the archive logs from disk after they are backed up. The Delete obsolete backups enables the deletion of backups that are marked as obsolete—those backups that are no longer required to satisfy the retention policy.

  1. Click the Encryption expandable icon at the end of the page. This expands the encryption fields that are new in Oracle RMAN 11g.

To create encrypted backups on-disk with Oracle RMAN, the database must have the Advanced Security database option installed. This is a licensable option; an extra license fee is payable to Oracle in addition to the Enterprise Edition license.

For creating encrypted Oracle RMAN backups directly onto tape, Oracle Secure Backup  is known to be the only supported interface. This is another licensable option.

Select Secure the backup using Recovery Manager encryptionand specify a password for the user-supplied password option. Backups will be encrypted using this password. It is also possible to use the Oracle encryption wallet to encrypt the backup, and it is advised to use both encryption approaches for flexibility in using either the wallet or the password when restoring the Oracle RMAN backup.

Note that AES256 has been selected as the encryption algorithm.

  1. On the next page confirm that the Disk Backup Location is the flash recovery area and proceed to the following page.
  2. The Scheduling page appears, where it is possible to enter the time zone of your location, the start date and time of the backup, the repeat frequency, and the end date (or if the schedule is to continue indefinitely).

The default suggested setting is 2 a.m. the following morning. The default is accepted so that the backup will start at this time.

The interval is selected as Frequency 1 Day, because as per the corporate standard a full database backup should be performed each day.

Then select Repeat until Indefinite, so that the schedule is continued indefinitely.

  1. Click Next. The Review screen now appears, on which the following settings are displayed:

Destination  Disk
Backup Type  Full Backup
Backup Mode  Online Backup
Encryption Algorithm  AES256
Encryption Mode  Oracle Encryption Wallet, Password
Flash Recovery Area  /U01
Disk Parallelism  3

The Oracle RMAN script is generated by Oracle Grid Control and displayed on the screen:

set encryption on for all tablespaces  algorithm ‘AES256’ identified by ‘%PASSWORD’;

backup device type disk tag ‘%TAG’  database;

backup device type disk tag ‘%TAG’  archivelog all not backed up delete all input;

allocate channel for maintenance  type disk;

delete noprompt obsolete device type  disk;

release channel;

These commands perform the actions selected in the Oracle Grid Control screens, such as turning on encryption when performing a full database and archive log backup, deleting archive logs after backup, and then deleting obsolete backups.

It is possible to click Edit RMAN Scriptand modify the script before submitting; however, this prevents a return to the previous screens in this wizard. Nevertheless, the Oracle RMAN script is modified manually as follows—with some extra maintenance commands added to cross-check backups and archive logs and to delete expired backups and archive logs:

set encryption on for all  tablespaces algorithm ‘aes256’ identified by ‘%password’;

backup device type disk tag ‘%tag’  database;

backup device type disk tag ‘%tag’  archivelog all not backed up delete all input;

allocate  channel for maintenance device type disk;

crosscheck backup;

delete  noprompt expired backup;

delete  noprompt obsolete device type disk;

crosscheck  archivelog all;

delete  noprompt expired archivelog all;

release channel;

  1. Click Submit Job. Once the job is successfully submitted, click View Job to see its progress. The job now appears under the Jobstab of Oracle Grid Control, under Job Activity, as a Scheduled job.

At this point, it is possible to click Backup Script -> Show to display the backup script used. This shows the Oracle RMAN script wrapped in Perl:

$rman_script=”set encryption on for all tablespaces algorithm ‘aes256’ identified by ‘%password’;

backup device type disk tag ‘%tag’ database;

backup device type disk tag ‘%tag’ archivelog all not backed up delete all input;

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete device type disk;

crosscheck archivelog all;

delete noprompt expired archivelog all;

release channel;

“;

&br_save_agent_env();

&br_prebackup($l_db_connect_string, $l_is_cold_backup, $l_use_rcvcat, $l_db_10_or_higher, $l_backup_strategy, “FALSE”);

my $result = &br_backup();

exit($result);

  1. It is possible, if so desired, to edit the scheduled job and change the schedule, the credentials, or the access. In the latter case, different access levels of View orFullcan be allocated to different Oracle Grid Control administrators. FINADM is seen as the owner of the job on this page.
  2. To set the Preferred Credentials for the database and host, click Preferences at the top of the Oracle Grid Control console and then click the Preferred Credentials link. This allows setting the logon name and password for both the database and the host. The Test button can be used to verify the username and password. After verification, click Apply.
  3. After setting the Preferred Credentials, click the Jobs tab of the Oracle Grid Control console. This shows the scheduled Oracle RMAN backup job.
  1. For the purpose of a backup test, change the schedule of the job to make itrun immediately. When this is done and the job is submitted, the job appearswith status Running. Press F5every few seconds to refresh the browser and watch the progress of the job.
  2. The job disappears from the JobActivity screen. Change the status to All and click Go.This shows all jobs, including jobs with a problem. The RACDB backup job appears as a problem.

Click the Problem link. The steps of the job appear, showing exactly where the job has had a problem.

The steps for Prebackup and Postbackup appear to have succeeded. The step for Backup appears to have failed. Click this step, and the entire Oracle RMAN output is now displayed. An examination of the output reveals the error immediately.

Output Log

Recovery Manager: Release 11.1.0.6.0 – Production on SatJun 14 23:29:03 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN>

connected to target database: RACDB (DBID=3660855775)

using target database control file instead of recoverycatalog

RMAN>

echo set on

RMAN> set encryption on for all tablespaces algorithm’aes256′ identified by *;

executing command: SET encryption

RMAN> backup device type disk tag ‘%tag’ database;

Starting backup at 14-JUN-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 device

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=123 device

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=122 device

channel ORA_DISK_1: starting compressed full datafilebackup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001name=D:\ORADATA\RACDB\SYSTEM01.DBF

channel ORA_DISK_1: starting piece 1 at 14-JUN-08

channel ORA_DISK_2: starting compressed full datafilebackup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00002name=D:\ORADATA\RACDB\SYSAUX01.DBF

input datafile file number=00004name=D:\ORADATA\RACDB\USERS01.DBF

channel ORA_DISK_2: starting piece 1 at 14-JUN-08

channel ORA_DISK_3: starting compressed full datafilebackup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00005name=D:\ORADATA\RACDB\EXAMPLE01.DBF

input datafile file number=00003name=D:\ORADATA\RACDB\UNDOTBS01.DBF

channel ORA_DISK_3: starting piece 1 at 14-JUN-08

RMAN-03009: failure of backup command on ORA_DISK_1channel at 06/14/2008 23:29:35

ORA-19914: unable toencrypt backup

ORA-28365: wallet isnot open

continuing other job steps, job failed will not be re-run

RMAN-03009: failure of backupcommand on ORA_DISK_2 channel at 06/14/2008 23:29:35

ORA-19914: unable toencrypt backup

ORA-28365: wallet isnot open

continuing other job steps, job failed will not be re-run

RMAN-00571:=================================================

RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ==========

RMAN-00571:=================================================

RMAN-03009: failure of backupcommand on ORA_DISK_3 channel at 06/14/2008 23:29:37

ORA-19914: unable toencrypt backup

ORA-28365: wallet isnot open

This error has occurred because the Oracle wallet is not open or has not been created for the database. To create the encryption wallet for the RACDB database, the following lines are first added to the sqlnet.ora file on the database server:

ENCRYPTION_WALLET_LOCATION=

(SOURCE=(METHOD=FILE)(METHOD_DATA=

(DIRECTORY=C:\app\GJH\product\11.1.0\db_1)))

The listener for the database is then restarted so that these changes take effect.

To create a new master key and begin using encrypted RMAN backups as well as Transparent Data Encryption (TDE) for the data, issue the following command:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “<pwd>”;

Click the Retry button on the Job page for the failed job. The backup job now executes again, and this time it completes successfully.

Examine the Oracle RMAN output by clicking the Step Backup link on this screen. This shows that the backup of the database has indeed completed successfully. Observe each step of the output in detail.

The Oracle RMAN output is seen to first set on encryption using the AES256 algorithm. This is followed by creation of a compressed full backup set for the database, and a control file and SPFILE autobackup. After this, the archive logs are backed up as a compressed archive log backup set, and the archive logs are then deleted from disk by Oracle RMAN due to the delete input clause.

Whenever Oracle RMAN wants to perform recovery, it is able to go to the archive log backup sets and extract the archive log it requires. This is one of the great benefits of Oracle RMAN. It contrasts with the manual recovery that DBAs performed in the past, extracting the archive logs from backup archives (UNIX tape archives or “tars”) and placing them where the recovery mechanism of Oracle could find them and use them.

The Oracle RMAN backup pieces for the database and archive logs, and the control file and SPFILE autobackups, are created in their respective directories under the flash recovery area (/U01 in this case):

<Flash Recovery Area>\RACDB\BACKUPSET\2008_06_15\

<Flash Recovery Area>\RACDB\AUTOBACKUP\2008_06_15\

The creation of the subdirectories for the database name, backup set, autobackup, and the date the backups have taken place are all automatically performed by Oracle due to the fact that the database is using the flash recovery area.

The archive log backup is followed by another control file and SPFILE backup. This happens because autobackup of these important files occurs after any backup, whether it’s a backup of the database or of the archive logs. This is then followed by the maintenance actions. These cross-check the existing backups and archive logs and make sure that they are present on-disk. All expired (not found) and obsolete (as per the retention policy) database, archive log, and control file backup entries are deleted from the control file records. The maintenance commands also ensure that the obsolete database backups and control file autobackups are physically deleted from the disk.

An important point to note is that if the database is restarted at any time in the future, the wallet must be reopened again. Otherwise, the Oracle RMAN backup fails with these errors:

ORA-19914: unable to encrypt backup

ORA-28365: wallet is not open

This is resolved by opening the wallet again using the following command:

alter system set wallet open  identified by “<pwd>”;
  1. The Oracle RMAN output has been examined in detail, and the backup job has been verified as fully successful. At the Oracle Grid Control console, move to the Jobs tab. Select status All and click Go to find the verified job.

If you edit this job again, you are only able to change the access, not the schedule. So you need to re-create the job using the same steps, but this time use the schedule of the daily early morning run, repeated each day, indefinitely.

When the job is scheduled thus, it appears in the Jobs tab of the Oracle Grid Control console with the status of Scheduled.

When the job is thus set up as a scheduled execution, it runs each day and the Oracle Grid Control repository stores the Oracle RMAN log of each run, whether successful or not. The DBA is able to examine the status of all Oracle RMAN backup jobs for all databases from the central Oracle Grid Control console and can take corrective action if necessary. It is also possible to receive e-mail notifications from Oracle Grid Control on the failure of any scheduled jobs.

Conclusion

Although at first glance it seems that the Oracle Grid Control approach to setting up Oracle RMAN backups has a far greater number of steps than the traditional UNIX shell script and cron approach, in the practical sense these steps can be performed well within 10 to 15 minutes, because they are wizard driven and autogenerate the necessary scripts. This does not include the setup of the Oracle Enterprise Manager Agent on each server. The Agent is not just for Oracle RMAN backup purposes but is the foundation of all Oracle Grid Control management and monitoring activities for that database server.

Thus there are considerable time savings in this approach, as compared to the two or more hours required for the manual setup and testing of Oracle RMAN UNIX-scripted backups that were discussed in Part I. This leads to savings on expensive DBA man-days, especially if there are a large number of projects requiring databases to be provisioned. Backup strategy has always been a very important aspect of Oracle Database, and it is essential that backups are easily set up in a consistent manner for each production and test database.

Because the Oracle RMAN backup setup is largely wizard driven, the chances for human error are minimal. Scripts have been generated by Oracle Grid Control and scheduled by its own job scheduling mechanism, thus eliminating the need to hire and retain rare skills in shell script maintenance. There is no question of using any other script language, because Oracle Grid Control does all the script generation. Debugging is also greatly simplified. For any new requirement that may arise—for example, incremental backups or image copies—a new backup job can be easily created using the Oracle Grid Control console and the old job deleted. So maintenance and future enhancements are also made a great deal easier.

One other great advantage of the Oracle Grid Control approach is that the latest advances in database technology are available as options in the Wizards, thus leading to a new awareness of the possibilities. In the real world, it is often seen that old Oracle RMAN  scripts used in Oracle8i Database are still being used unchanged in Oracle9i Database and 10g, mainly because of the lack of knowledge in the DBAs maintaining the scripts. This results in new Oracle RMAN features not being used, such as compression of backups, or block change tracking for incremental backups, or encryption. When the Oracle Grid Control approach is used instead, it results in the DBAs being made aware of the new Oracle RMAN possibilities so that these can be used to the fullest benefit.

When Oracle Grid Control is showcased to management and DBAs through the demonstration in this article,  there is no need for further proof—the manifold benefits of this approach are visible beyond any doubt. More and more companies will necessarily set up and schedule Oracle RMAN backups using the vast capabilities of Oracle Enterprise Manager Grid Control. There will be no stopping this immensely beneficial juggernaut.

November 20, 2008 Posted by | oracle | 1 Comment

Oracle 10g RAC databases on Linux Red Hat 3.0 single server

Oracle 10g RAC databases on Linux Red Hat 3.0 single server

Discover easy and very affordable way to install and configure a fully functional Oracle 10g RAC database on one only un-expensive single node running Linux Red Hat.

by Guenadi Nedkov Jilevski (gjilevski@gmail.com)

If you are serious experienced Oracle DBA interested in setting an High Availability  Oracle 10g RAC database testing environment than this paper shall give you the insides to accomplish it step by step using only a single Red Hat 3.0 server without additional investment in hardware and time to setup and maintain a second server to form a cluster and expensive shared storage.

Background / Overview

With the evolution of the Oracle High Availability solutions, now-a-days becoming the key to provide the desired zero downtime and nearzero time to repair functionality essential for the business continuity, and their relatively high cost and demand for a high end hardware platforms face the Seniors DBA toward the greatest challenge to look for an inexpensive solutions to practice, test and prototype their architecture and solutions. This article addresses the needs by providing a working Oracle 10g RAC database architecture based entirely on a single server with four Network Interface Cards (NIC)  running a Linux Red Hat 3.0. The article shall cover in great details the prerequisites such as preliminary   Linux Red Hat 3.0 setup, required by Oracle preliminary kernel parameters setup, preliminary NIC configuration for RAC setup. The paper shall place an emphasis on the installation of the Cluster Ready Services and Oracle RDBMS at a screen level and last but not least shall finish with the database creation with dbca and manual adjustments required for the second, third, n-th instance. The following article is a step-by-step guide (Cookbook) with tips and information for installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3. The primary objective of this article in it’s current version is to demonstrate a quick installation of Oracle 10g with RAC on RH AS 3.0 using a single server.

Step 1: Preliminary Linux Red Hat 3.0 Setup

Here I’ll describe the process of installation and configuration of the Linux Red Hat server on a PC. The configuration used is RAM 1024MB, two 120GB disks and  four (4) Netwok Interface Cards (NIC).  Install Red Hat 3.0 on the server using the Install Everything option to ensure that you have all of the necessary components. Install the kernel-unsupported-2.4.21-4.EL.i686.rpm from the Red Hat Disk3 on the server having device support for many nonstandard devices. Add the following lines to the /etc/modules.conf on the server.

options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180

options sbp2 sbp2_exclusive_login=0

post-install sbp2 insmod sd_mod

post-remove sbp2 rmmod sd_mod

Create Linux group dba and users for the Oracle Cluster Ready Services and Oracle RDBMS respectively oracle10crs and oracle10 members of the dba group as in the /etc/passwd and /etc/group files. Set user equivalence for the users in /etc/hosts.equiv or .rhosts as specified in the sample /etc/hosts.equiv. Create /var/opt/oracle directory with read write permissions for the Oracle users. Create the directories for the $ORACLE_BASE with respective permissions.  Setup the profiles for the users as in the example of  ~oracle10crs/.bash_profile and ~oracle10/.bash_profile to include the variables for the $ORACLE_SID, $ORACLE_HOME, $ORACLE_BASE and $PATH to include the $ORACLE_HOME/bin.

Step 2: Preliminary kernel parameter setup

Set the minimal required kernel parameters in the /etc/sysctl.conf as in the sample provided. Reboot the server for the completion of the preliminary setup of the Linux and Kernel to take effect.

Step 3: Network Configuration

To configure the network interfaces (in this example eth0, eth1, eth2 and eth3), run the following command on each node as root.

$ redhat-config-network

Do not configure the network alias names for the public VIP. This will be done by Oracle’s Virtual Internet Protocol Configuration Assistant (VIPCA). When the network configuration is done, it is important to make sure that the name of the public RAC nodes is displayed when you execute the following command:

$ hostname

pub1

You can verify the new configured NICs by running the command:

/sbin/ifconfig -a

Look at the sample /etc/hosts file.

Step 4: Cluster Ready Services Installation

To install CRS, insert the “Cluster Ready Services (CRS) R1 (10.1.0.2)” CD and as root issue the following commands:

mount /mnt/cdrom

xhost +

Login as oracle10 and execute runInstaller:

oracle$ /mnt/cdrom/runInstaller

– Welcome Screen:     Click Next

– Inventory directory and credentials:

Click Next

– Unix Group Name:    Use “dba”.

– Root Script Window: Open another window, login as root, and run /tmp/orainstRoot.sh

on the node where you launched runInstaller.

After you’ve run the script, click Continue.

– File Locations:     I used the recommended default values:

Destination Name: OraCr10g_home1

Destination Path: /a02/app/oracle/product/10.1.0/crs_1

Click Next

– Language Selection: Click Next

– Cluster Configuration:

Cluster Name: crs

Cluster Nodes: Public Node Name: pub1   Private Node Name: priv1

Click Next

– Private Interconnect Enforcement:

Interface Name: eth0  Subnet: 192.168.1.113  Interface Type: Public

Interface Name: eth1  Subnet: 192.168.2.112  Interface Type: Public

Interface Name: eth2  Subnet: 192.168.1.111  Interface Type: Private

Interface Name: eth3  Subnet: 192.168.2.110  Interface Type: Private

NOTE:  See the output of the ifconfig –a

Click Next

– Oracle Cluster Registry:

OCR Location: /u02/oradata/ OCRFile

Click Next

– Voting Disk:        Voting disk file name: /u02/oradata/ CSSFile

Click Next

– Root Script Window:

Open another window, login as root, and execute

/u01/app/oracle/oraInventory/orainstRoot.sh on ALL RAC Nodes!

NOTE: For any reason Oracle does not create the log directory

“/a02/app/oracle/product/10.1.0/crs_1/log”. If there are problems with

CRS, it will create log files in this directory, but only if it exists.

Therefore make sure to create this directory as oracle:

oracle$ mkdir /a02/app/oracle/product/10.1.0/crs_1/log

After you’ve run the script, click Continue.

– Setup Privileges Script Window:

Open another window, login as root, and execute

/a02/app/oracle/product/10.1.0/crs_1/root.sh on the server !

Note that his can take a while. On the last RAC node, the output of the

script was as follows:

CSS is active on these nodes.

Pub1

CSS is active on all nodes.

Oracle CRS stack installed and running under init(1M)

Click OK

– Summary:            Click Install

– When installation is completed, click Exit.

One way to verify the CRS installation is to display all the nodes where CRS was installed:

oracle$ /a02/app/oracle/product/10.1.0/crs_1/bin/olsnodes -n

pub 1

Step 5: Installing Oracle Database 10g Software R1 (10.1.0.2) with Real Application Clusters (RAC)

To install the RAC Database software, insert the Oracle Database 10g R1 (10.1.0.2) CD “), and mount it on pub1:

mount /mnt/cdrom

xhost +

Login as oracle10 and execute runInstaller:

oracle$ /mnt/cdrom/runInstaller

– Welcome Screen:   Click Next

– File Locations:   I used the default values:

Destination Name: raDb10g_home1

Destination Path: /a02/app/oracle/product/10.1.0/db_2

Click Next.

– Hardware Cluster Installation Mode:

Select “Cluster Installation”

Click “Select All” to select all servers: pub1

Click Next

– Installation Type:

I selected “Enterprise Edition”.

Click Next.

– Product-specific Prerequisite Checks:

Make sure that the status of each Check is set to “Succeeded”.

Click Next

– Database Configuration:

I selected “Do not create a starter database” since we have to create the

database with dbca. Oracle Database 10g R1 (10.1) OUI will not be able to

discover disks that are marked as Linux ASMLib. For more information, see

http://otn.oracle.com/tech/linux/asmlib/install.html#10gr1

Click Next

– Summary:          Click Install

– Setup Privileges Window:

Open another window, login as root, and execute

/a02/app/oracle/product/10.1.0/db_1/root.sh the server !

NOTE: Make also sure that X is  relinked to your local desktop since this

script will launch the “VIP Configuration Assistant” tool which is a

GUI based utility!

VIP Configuration Assistant Tool:

(This Assistant tool will come up only once when root.sh is executed the

first time in your RAC cluster)

– Welcome              Click Next

– Network Interfaces:  I selected both interfaces, eth0 and eth1.

Click Next

– Virtual IPs for cluster notes:

(for the alias names and IP address, see Setting Up the /etc/hosts File)

Node Name:     pub1

IP Alias Name: vip1

IP address:    192.168.1.13

Subnet Mask:   255.255.255.0

Click Next

– Summary:             Click Finish

– Configuration Assistant Progress Dialog:

Click OK after configuration is complete.

– Configuration Results:

Click Exit

Click OK to close the Setup Privilege Window.

– End of Installation:

Click Exit

Step 6: Installing Oracle Database 10g with Real Application Cluster (RAC)

To install the RAC database and the instances on the server run runInstaller on pub1.

Login as oracle10 and before you execute dbca, make sure that $ORACLE_HOME and $PATH are set:

oracle10$ . ~oracle10/.bash_profile

oracle10$ dbca

– Welcome Screen:     Select “Oracle Real Application Clusters database”

Click Next

– Operations:  Select “Create Database”

Click Next

– Node Selection:     Click “Select All”. Make sure pub1 is selected!

Click Next

– Database Templates: I selected “General Purpose”.

Click Next

– Database Identification:

Global Database Name: orcl

SID Prefix: orcl

Click Next

– Management Option:  Selected “Use Database Control for Database Management”.

Click Next

– Database Credentials:

I selected “Use the Same Password for All Accounts”. Enter the password and

make sure the password does not start with a digit number.

Click Next

– Storage Options:    I selected “Cluster File System

Click Next

– Database File Location: Select “ Use common Location for All Database Files”

Enter the Database File Location “/a02/oradata”

Click Next

– Recovery Configuration:

Select the “Specify Flash Recovery Area” with defaults and “Enable Archiving”.

Click Next

– Database Content:   Select Sample Schemas but not Custom Scripts.

Click Next

– Database Services:  Click “Add” and enter a Service Name: I entered “orcltest”.

I selected TAF Policy “Basic”.

Click Next

– Initialization Parameters:

Change settings as needed.

Click Next

– Database Storage:   Change settings as needed.

Click Next

– Creation Options:   Check “Create Database”

Click Finish

– Summary:            Click OK

Now the database is being created.
Your RAC cluster should now be up and running. To verify, try to connect to each instance from one of the RAC nodes:

$ sqlplus system@orcl1

After you connected to an instance, enter the following SQL command to verify your connection:

SQL> select instance_name from v$instance;

Step 7: Create a second, third, n-th instance

Create a parameter file from the service parameter file and add the parameters above. Modify the parameter file to include the parameters for second instance. For second instance add

orcl2.instance_number=2

orcl2.thread=2

orcl2.undo_tablespace=’UNDOTBS2′

Restart orcl1 instance with the above parameter file. Create an undo tablespace UNDOTBS2 with the same specifications as UNDOTBS1. Create a redo log thread 2 and enable it using again instance orcl1.

create undo tablespace undotbs2 datafile ‘/a02/oradata/orcl/undotbs02.dbf’ size 31465472;

alter database add logfile thread 2

‘/a02/oradata/orcl/redo11.log’ size 10486272, ‘/a02/oradata/orcl/redo12.log’ size 10486272,

‘/a02/oradata/orcl/redo13.log’ size 10486272;

alter database enable thread 2;

Create a second telnet session loged in as oracle10 and set ORACLE__SID to orcl2. Copy the password file from the first instance to password file for the second instance. Startup the orcl2 instance. Create srvctl configuration or the second instance.

srvctl add instance -d orcl -i orcl2 -n pub1

srvctl status database -d orcl

srvctl start instance -d orcl -i orcl1

srvctl status database -d orcl

Use same approach for a third, fourth , n-th instance on the same server.

Step 8: Transparent Application Failover

Sample script testtaf.sql is used to test the Transparent application Failover. The NET tnsnames.ora and listener.ra are also in the content section. Connect using the orcltaf tnsnames alias and run he script testtaf.sql. From another telnet session kill the pmon of the database where the previous session is connected to and observe the output a select statement application failover below.

[oracle10@pub1 admin]$ sqlplus system/sys1@orcltaf

SQL*Plus: Release 10.1.0.2.0 – Production on Fri Sep 24 18:59:01 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> @/tmp/testtaf

SID   SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER

—- ——— ————- ————— ———–

250        74 SELECT        BASIC           NO

INSTANCE_NAME

—————-

orcl1

COUNT(*)

———-

292188

SID   SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER

—- ——— ————- ————— ———–

251         9 SELECT        BASIC           YES

INSTANCE_NAME

—————-

orcl2

COUNT(*)

———-

292188

Conclusion

Having implemented the solution described above the reader probably have noticed that has under his/hers belt a fully functional very cost effective High Availability Oracle 10g RAC database architecture for testing, prototyping and prove of concept. The article s extremely suitable for in-house training, consultants sand desk labs and class trainings which can not afford expensive clustered hardware solutions. Once a solution is piloted and configurations tested next essential step will be to build a real test and production environments resulting in considerable reduction of the time, cost solution reusability and much faster learning curve and plenty of hands on knowledge in the course of rolling over into production versus a full fledged prototyping requiring the pairs of servers for clusters and shared disk storage.

At the end of the file, after the text of the article, please supply the following:

  • Content of the /etc/sysctl.conf, /etc/hotsts, /etc/hosts.equiv, /etc/passwd, /etc/group .bash_profile, listener.ora tnsnames.ora, testtaf.sql output from ifconfig files for the root, oracle10crs and oracle10 users.

Initoracl.ora

*.background_dump_dest=’/a02/app/oracle/admin/orcl/bdump’

*.cluster_database_instances=2

*.cluster_database=true

*.compatible=’10.1.0.2.0′

*.control_files=’/a02/oradata/orcl/control01.ctl’,’/a02/oradata/orcl/control02.ctl’,’/a02/oradata/orcl/control03.ctl’

*.core_dump_dest=’/a02/app/oracle/admin/orcl/cdump’

*.db_block_size=8192

*.db_cache_size=25165824

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’orcl’

*.db_recovery_file_dest=’/a02/app/oracle/flash_recovery_area’

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’

orcl1.instance_number=1

orcl2.instance_number=2

*.java_pool_size=50331648

*.job_queue_processes=10

*.large_pool_size=8388608

*.log_archive_format=’%t_%s_%r.dbf’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=250

*.remote_listener=’LISTENERS_ORCL’

*.remote_login_passwordfile=’exclusive’

*.shared_pool_size=99614720

*.sort_area_size=65536

orcl1.thread=1

orcl2.thread=2

*.undo_management=’AUTO’

orcl1.undo_tablespace=’UNDOTBS1′

orcl2.undo_tablespace=’UNDOTBS2′

*.user_dump_dest=’/a02/app/oracle/admin/orcl/udump’

/etc/sysctl.conf

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=262144

net.core.wmem_max=262144

/etc/hosts

127.0.0.1       localhost.localdomain localhost

192.168.1.113   pub1    zeus

192.168.1.112   pub2

192.168.1.111   priv1

192.168.1.110   priv2

192.168.1.13    vip1

192.168.1.12    vip2

ipconfig -a

eth0      Link encap:Ethernet  HWaddr 00:C0:4F:36:E1:E7

inet addr:192.168.1.113  Bcast:192.168.1.255  Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:0 errors:1428 dropped:0 overruns:1 frame:2079

TX packets:16 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:0 (0.0 b)  TX bytes:960 (960.0 b)

Interrupt:11 Base address:0xcc00

eth1      Link encap:Ethernet  HWaddr 00:30:BD:71:EE:4A

inet addr:192.168.1.112  Bcast:192.168.1.255  Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:5 errors:0 dropped:0 overruns:0 frame:0

TX packets:4 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:300 (300.0 b)  TX bytes:240 (240.0 b)

Interrupt:9 Base address:0x9c00

eth2      Link encap:Ethernet  HWaddr 00:30:BD:72:04:B8

inet addr:192.168.1.111  Bcast:192.168.1.255  Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:38 errors:0 dropped:0 overruns:0 frame:0

TX packets:4 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:3549 (3.4 Kb)  TX bytes:240 (240.0 b)

Interrupt:11 Base address:0xb800

eth3      Link encap:Ethernet  HWaddr 00:30:BD:72:04:0F

inet addr:192.168.1.110  Bcast:192.168.1.255  Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

RX packets:23 errors:0 dropped:0 overruns:0 frame:0

TX packets:16 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:2649 (2.5 Kb)  TX bytes:960 (960.0 b)

Interrupt:9 Base address:0xd400

lo        Link encap:Local Loopback

inet addr:127.0.0.1  Mask:255.0.0.0

UP LOOPBACK RUNNING  MTU:16436  Metric:1

RX packets:2903 errors:0 dropped:0 overruns:0 frame:0

TX packets:2903 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:154137 (150.5 Kb)  TX bytes:154137 (150.5 Kb)

/etc/hosts.equiv

+pub1 oracle10

+pub2 oracle10

+priv1 oracle10

+priv2 oracle10

+pub1 oracle10crs

+pub2 oracle10crs

+priv1 oracle10crs

+priv2 oracle10crs

/etc/passwd

radvd:x:75:75:radvd user:/:/sbin/nologin

oracle10crs:x:500:501::/home/oracle10crs:/bin/bash

oracle10:x:501:501::/home/oracle10:/bin/bashswd

/etc/group

oracle10crs:x:500:

dba:x:501:oracle10crs,oracle10

/etc/modules.conf

alias eth0 3c59x

alias eth1 8139too

alias eth2 8139too

alias eth3 8139too

alias usb-controller usb-uhci

options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180

options sbp2 sbp2_exclusive_login=0

post-install sbp2 insmod sd_mod

post-remove sbp2 rmmod sd_mod

root’s .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

BASH_ENV=$HOME/.bashrc

USERNAME=”root”

export LD_ASSUME_KERNEL=2.4.19

export USERNAME BASH_ENV PATH

oracle10crs’s .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export ORACLE_BASE=/a02/app/oracle

export ORACLE_SID=orcl1

export ORACLE_HOME=/a02/app/oracle/product/10.1.0/crs_2

PATH=$PATH:$HOME/bin

export LD_ASSUME_KERNEL=2.4.19

export PATH=$PATH:$ORACLE_HOME/bin

unset USERNAME

oracle10’s  .bash_oprofile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export ORACLE_BASE=/a02/app/oracle

export ORACLE_SID=orcl1

export ORACLE_HOME=/a02/app/oracle/product/10.1.0/db_2

export LD_ASSUME_KERNEL=2.4.19

PATH=$PATH:$HOME/bin

export PATH=$PATH:$ORACLE_HOME/bin

unset USERNAME

[oracle10@pub1 oracle10]$

Testtaf.sql

col sid format 999

col serial# format 99999999

col failover_type format a13

col failover_method format a15

col failed_over format a11

select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;

select instance_name from v$instance;

select count(*) from

(

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source

);

col sid format 999

col serial# format 99999999

col failover_type format a13

col failover_method format a15

col failed_over format a11

select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;

select instance_name from v$instance;

select count(*) from

(

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source union

select * from dba_source

);

Listener.ora

# listener.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER_PUB1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

)

)

SID_LIST_PUB1 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u00/app/oracle/product/9.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)

(SID_NAME = orcl1)

)

(SID_DESC =

(SDU = 32768)

(TDU = 32768)

(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)

(SID_NAME = orcl2)

)

)

tnsnames.ora

# tnsnames.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

LISTENERS_ORCL =

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

ORCL1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

(INSTANCE_NAME = orcl1)

)

)

ORCL2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

(INSTANCE_NAME = orcl1)

)

)

ORCLTEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcltest)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(RETRIES = 180)

(DELAY = 5)

)

)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

ORCLTAF =

(DESCRIPTION =

(ENABLE=BROKEN)

(ADDRESS_LIST =

(LOAD_BALANCE = OFF)

(FAILOVER = ON)

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORCL)

(GLOBAL_NAME = ORCL)

(FAILOVER_MODE =

(TYPE=SELECT)

(METHOD=BASIC)

(BACKUP=ORCL)

)

)

)

  • Links to more information about the technology at otn.oracle.com
  • Guenadi Nedkov Jilevski (gjilevski@gmail.com) is a [Senior Infrastructure ] at Perot Systems, (http://www.ps.com) a [Lake Success, NY] based [global provider of technology-based business solutions].

November 13, 2008 Posted by | oracle | Leave a comment