If you were using VMWare workstation/server so that you can run a test RAC environment, well then your prayers have been answered (unless you already paid for VMWare). In my opinion, you no longer need VMWare since VirtualBox is the complete solution. For more details please visit virtualbox.org, and for specific instructions on how to set this up, please go here and look at the section on sharable hard disks.

Run the following query for current metrics. This shows what thresholds Alerts will be triggered for (Alerts not email/pager notification – that is part of notification rules).

SELECT a.target_name, a.target_type, a.metric_name, a.metric_column,
       DECODE(TRIM(a.warning_threshold), '',
       DECODE(TRIM(a.critical_threshold), '', 'DISABLED', 'ENABLED'), 'ENABLED') STATUS ,
       a.warning_operator, a.warning_threshold, a.critical_operator,
       a.critical_threshold,
       REPLACE(REGEXP_SUBSTR(b.schedule_ex, '"[^"]*"'), '"', '') FREQUENCY,
       NVL(REPLACE(REPLACE(REGEXP_SUBSTR(b.schedule_ex, 'T="[^"]*"'), 'T=', ''),'"', ''), 'Minutes') FREQUENCY_UNIT,
       c.frequency_code, a.occurence_count, a.warning_count,
       a.critical_count, c.is_enabled
FROM MGMT$METRIC_COLLECTION a, SYSMAN.MGMT_METRIC_COLLECTIONS b, sysman.MGMT$TARGET_METRIC_COLLECTIONS c
WHERE a.target_guid = b.target_guid
  and a.target_guid = c.target_guid
  and b.metric_guid = c.metric_guid
  and a.metric_name = c.metric_name
ORDER BY target_name, target_type, a.metric_name;

Next, Notification Methods need to be queried to determine which alerts Oracle sends our emails for.

SELECT RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID,
                 METRIC_NAME, METRIC_COLUMN, KEY_VALUE, KEY_PART_1, KEY_PART_2,
                 KEY_PART_3, KEY_PART_4, KEY_PART_5, WANT_CLEARS, WANT_WARNINGS,
                 WANT_CRITICAL_ALERTS, WANT_TARGET_UP, WANT_TARGET_DOWN,
                 WANT_TARGET_UNREACHABLE_START, WANT_TARGET_UNREACHABLE_END,
                 WANT_TARGET_METRIC_ERR_START, WANT_TARGET_METRIC_ERR_END,
                 WANT_TARGET_BLACKOUT_START, WANT_TARGET_BLACKOUT_END,
                 WANT_POLICY_CLEARS, WANT_POLICY_VIOLATIONS,
                 WANT_WARNING_JOB_SUCCEEDED, WANT_WARNING_JOB_PROBLEMS,
                 WANT_CRITICAL_JOB_SUCCEEDED, WANT_CRITICAL_JOB_PROBLEMS,
                 WANT_POLICY_JOB_SUCCEEDED, WANT_POLICY_JOB_PROBLEMS, IGNORE_RCA
FROM SYSMAN.MGMT_NOTIFY_RULE_CONFIGS;

This final Query will combine the 2 to show alerts and if they will be notified on.

SELECT DISTINCT a.target_name,
           a.target_type,
           a.metric_name,
           a.metric_column,
           DECODE (
              TRIM (a.warning_threshold),
              '',
              DECODE (TRIM (a.critical_threshold), '', 'DISABLED', 'ENABLED'),
              'ENABLED'
           )
              STATUS,
           NVL (d.rule_name, 'NO RULE') rule_name,
           a.warning_operator,
           a.warning_threshold,
           a.critical_operator,
           a.critical_threshold,
           REPLACE (REGEXP_SUBSTR (b.schedule_ex, '"[^"]*"'), '"', '')
              FREQUENCY,
           NVL (
              REPLACE (
                 REPLACE (REGEXP_SUBSTR (b.schedule_ex, 'T="[^"]*"'), 'T=', ''),
                 '"',
                 ''
              ),
              'Minutes'
           )
              FREQUENCY_UNIT,
           c.frequency_code,
           a.occurence_count,
           a.warning_count,
           a.critical_count,
           c.is_enabled
    FROM   MGMT$METRIC_COLLECTION a,
           SYSMAN.MGMT_METRIC_COLLECTIONS b,
           sysman.MGMT$TARGET_METRIC_COLLECTIONS c,
           (SELECT DISTINCT DECODE (TRIM (y.target_name),
                             '%', x.target_name,
                             y.target_name) target_name,
                     y.rule_name,
                     y.metric_name,
                     y.metric_column
              FROM   SYSMAN.MGMT$METRIC_COLLECTION x,
                     SYSMAN.MGMT_NOTIFY_RULE_CONFIGS y
             WHERE   (x.target_name = y.target_name
                      OR TRIM (y.target_name) = '%')
                     AND x.metric_name = y.metric_name
                     AND x.metric_column = y.metric_column) d
   WHERE       a.target_guid = b.target_guid
           AND a.target_guid = c.target_guid
           AND b.metric_guid = c.metric_guid
           AND a.metric_name = c.metric_name
           AND a.target_name = d.target_name(+)
           AND a.metric_name = d.metric_name(+)
           AND a.metric_column = d.metric_column(+)
ORDER BY   target_name, target_type, a.metric_name;

If you see any errors above, then please let me know so I can fix them!

Lastly, we run a script against the Grid Control repository to pull out availability metrics such as database uptime %, Avg Host CPU Util %, Avg Host memory Util %, and ASM Disk Group total space and free space. Hopefully if will give you a pretty good idea of where the information is so you can modify it for your own use with your own metrics of interest.

Basically SQLPLUS spools it out to an Excel file and then the script emails it out.

First, the shell script

#!/bin/bash

export CYM=$(date '+%Y%m')
export MCY=$(date '+%B %Y')
export SQLD=$HOME/scripts
export ORACLE_SID=emrep
export ORACLE_HOME=/u001/app/oracle/product/db10g

$ORACLE_HOME/bin/sqlplus -s "sysman/sysman_password" @$SQLD/metrics.sql

cp /home/oracle/scripts/spool/oracle_metrics.xls /home/oracle/scripts/spool/oracle_metrics_$CYM.xls

echo "Oracle Metrics Collection - ${MCY}" | /usr/bin/mutt -a /home/oracle/scripts/spool/oracle_metrics_${CYM}.xls -s "Oracle Metrics Collection - ${MCY}" email_address@domain.com
exit 0

Really you could email with sendmail or mailx, but you will need uuencode to encode the attachment and then pipe that into your mail program.

Next is the SQL script to use. You will need to identify you ASM targets (Chances are you only want one of them). You will also have to put a filter. If you read the script you will see what you have to put in. I will do some tweaking to see if I can’t make it more universal without editing needed.

SET MARKUP HTML ON HEAD "<title>Production Metrics Report</title> - <meta http-equiv='Content-Type' content='application/vnd.ms-excel;'> <style type='text/css'> <!-- BODY {background: red} --> </style>"
set pagesize 50000
set trimspool on
set feedback off
set echo off
set term off
spool /home/oracle/scripts/spool/oracle_metrics.xls
select
  distinct a.target_name "Target Name",
  nvl(b.uptime,100) "Database Uptime (%)"
from
  sysman.MGMT$AVAILABILITY_HISTORY a,
  (select target_name,
     round((100-(sum(trunc(86400*(end_timestamp-start_timestamp)))/trunc(86400*(add_months(trunc(sysdate,'MM'),-0)-add_months(trunc(sysdate,'MM'),-1))))),5) uptime
   from
     sysman.MGMT$AVAILABILITY_HISTORY
   where
     target_type in ('oracle_database','rac_database')
     and target_name like '%somefilter%'
     and target_name not like 'st%'
     and target_name not like 'dg%'
     and target_name not like 'meta%'
     and target_name not like 'emrep%'
     and start_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
     and availability_status = 'Target Down'
   group by target_name) b
where
  a.target_name = b.target_name(+)
  and a.target_type in ('oracle_database','rac_database')
  and a.target_name like '%somefilter%'
  and a.target_name not like 'st%'
  and a.target_name not like 'dg%'
  and a.target_name not like 'meta%'
  and a.target_name not like 'emrep%'
  order by a.target_name;

select t.target_name "Target Name", TO_CHAR(round(avg(value_average),2)) "Avg Host CPU Util (%)"
from mgmt_metrics_1hour h, mgmt_targets t where
h.target_guid in (select target_guid from mgmt_targets t where
target_type = 'host' and
target_name in (select target_name from mgmt_targets where target_type='host' and target_name like '%somefilter'
                                 )) and
h.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'host' and
                          m.metric_name = 'Load' and
                          m.metric_column = 'cpuUtil'and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
h.target_guid = t.target_guid and
--rollup_timestamp > sysdate-31
rollup_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
group by t.target_name;

select t.target_name "Target Name", TO_CHAR(round(avg(value_average),2)) "Avg Host Memory Util (%)"
from mgmt_metrics_1hour h, mgmt_targets t where
h.target_guid in (select target_guid from mgmt_targets t where
target_type = 'host' and
target_name in (select target_name from mgmt_targets where target_type='host' and target_name like '%somefilter_such as domain name'
                                 )) and
h.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'host' and
                          m.metric_name = 'Load' and
                          m.metric_column = 'memUsedPct'and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
h.target_guid = t.target_guid and
--rollup_timestamp > sysdate-31
rollup_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
group by t.target_name;

select decode(t.target_name,'YOURASM_TARGET','FRIENDLY NAME','YOURDRASM_TARGET','FRIENDLY NAME') "Target Name", TO_CHAR(round(max(b.value_average)/1024,0)) "Total Space (GB)",
TO_CHAR(round(max(a.value_average)/1024,0)) "Total Free Space (GB)"
from mgmt_metrics_1hour a, mgmt_metrics_1hour b, mgmt_targets t
where a.target_guid in (select target_guid from mgmt_targets t where
target_type = 'osm_instance' and
target_name in (select target_name from mgmt_targets where target_type='osm_instance' and target_name like '+ASM1%'
and target_name not like 'st%'))
and
a.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'osm_instance' and
                          m.metric_name = 'DiskGroup_Usage' and
                          m.metric_column = 'free_mb' and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' '))and
b.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'osm_instance' and
                          m.metric_name = 'DiskGroup_Usage' and
                          m.metric_column = 'total_mb' and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
a.target_guid = t.target_guid and
b.target_guid=t.target_guid and
--a.rollup_timestamp > sysdate-31
a.rollup_timestamp between add_months(trunc(sysdate,'MM'),-1) and add_months(trunc(sysdate,'MM'),0)
group by t.target_name;

spool off
exit

And that’s all I have for now, but please leave feedback on any errors or if there are some scripts you like to run against the repository.

If you want to run a job at noon every day. That is pretty simple. If you want to run a job 5 minutes after noon, that is very simple. If you want it to happen on any day of the month, again that works. If you want it on every Wed, again simple.

So what do you do if you need to apply some logic? Let’s say you want a job to only run on the first Saturday of the month, but only after following the first Sunday of the month? So if the first was a Saturday, you would not want the job to run? You might think that setting the day of the month as [2-8] and the Day as a Saturday might work? Wrong, it will do an ‘OR’ operation, not an ‘AND’. Basically it will fire on the 2nd, 3rd, 4th, all the way to the 8th. It will also fire on every Saturday. That is clearly not what you want.

The solution is actually quite simple. You can use the if/then operator of the command line. This is how I accomplished it and it works quite well (and I thought I would share).

0 12 2,3,4,5,6,7,8 * * if [ `date +\%a` = "Sat" ]; then sh /u01/app/oracle/scripts/somescript.sh 1 > /u01/app/oracle/scripts//u01/app/oracle/scripts/logs/somescript.log 2>&1 ; fi

So in the above example, I run this in the 2-8 day frame. It executes every single one of those days at 12 o’clock. I then check to see if the day is a Saturday. If it is, then execute the encapsulated command, if not then don’t run it. So while the job technically still executes everyday, I added some extra logic to enable it to do what I want, which is to only execute the script if that day is a Saturday.

If anyone has any other tips or ideas then please share them.

There is no Oracle release for Windows 7, so instead I grabbed the Windows 2008 one. The install is pretty straight forward. The only "gotcha" is that if you go to the command prompt, you need to run it as the Administrator, so that all of the Oracle commands will work (lsnrctl, dbca, netca etc…) Really there is not that much to it as it was pretty seemless. So, if you download Windows 7, then you can also download Oracle 11g and start playing with it today!

If you run into any issues, let me know and I’ll see if I can help you out.

I found this post from November 18th, 2008 http://samiora.blogspot.com/2008/11/rman-veritas-netbackup-integration.html

Which is word for word of one of my posts (including hyperlinks), which I posted in May 24th, 2007.

http://www.oraclerant.com/?p=7

I’m not really mad, more annoyed that someone would just outright steal content word for word without giving any reference to the original author.

Oh well, I guess they say imitation is the ultimate form of flattery, so I wonder what cut and paste is then ?

1. Archive your current Oracle installation via zip or tar. I tend to archive the directory of the actual home and all files and subdirectories
2. Have your server setup with the required packages for your Oracle version. It also has to be the same OS. A Linux Oracle install will not work for cloning to Solaris.
3. Have the dba and oinstall group created and then the oracle user will need to be created and assigned to both groups. Have oracle own the install directory as well.
4. Create your profile and environment variables. The main ones are ORACLE_BASE and ORACLE_HOME. The Inventory will be created off of your ORACLE_BASE, so set this beforehand if you don’t have an Oracle Inventory.
5. Upload and extract your Oracle archive.
6. Use the Oracle installer to link and setup your Oracle installation.
7. Run orainstRoot.sh and root.sh if required. It will tell you at the end what scripts to run.

Let me give you an overview of step 6 below. Everything else should be self explanatory.

[oracle@server ~]$ $ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="Ora10gR2Client"
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /u001/OraInstall2009-01-29_06-54-41PM. Please wait ...[oracle@server ~]$ Oracle Universal Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

You can find a log of this install session at:
 /u001/app/oracle/oraInventory/logs/cloneActions2009-01-29_06-54-41PM.log
.................................................................................................... 100% Done.

Installation in progress (Thu Jan 29 18:54:50 GMT 2009)
........................................................................                                                        71% Done.
Install successful

Linking in progress (Thu Jan 29 18:54:53 GMT 2009)
.                                                                72% Done.
Link successful

Setup in progress (Thu Jan 29 18:55:08 GMT 2009)
..................                                              100% Done.
Setup successful

End of install phases.(Thu Jan 29 18:55:10 GMT 2009)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script '/u001/app/oracle/oraInventory/orainstRoot.sh' with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.

The following configuration scripts
/u001/app/oracle/product/10.2/client/root.sh
need to be executed as root for configuring the system.

The cloning of Ora10gR2Client was successful.
Please check '/u001/app/oracle/oraInventory/logs/cloneActions2009-01-29_06-54-41PM.log' for more details.

[oracle@server ~]$

As you can see, it tells you what scripts to run as root. Also, make sure to update your tnsnames.ora if you need something different that what you archived with.

Next up, Tomatoes! You can scoop the seeds out and make room for more memory and storage

See more below…

Amsterdam, Netherlands – Hackers from the LinuxOnAnything.nl Web site successfully installed Linux on a potato. It’s the first time the operating system has been successfully installed on a root vegetable

“A potato doesn’t have a CPU, memory or storage space, so it was quite a challenge,” said Johan Piest of the Linux on Anything (LOA) group. “Obviously we couldn’t use a large distribution like Fedora or Ubuntu, so we went with Damn Small Linux.”

After weeks of trying the group got a Linux kernel specially modified for a potato loaded, and were able to edit a small text file in vi. Linux was loaded onto the potato using a USB thumb drive and commands were sent in binary to the potato using a set of red and black wires.

The LOA group is a part of a growing group of hackers attempting to get Linux loaded on anything. It started on electronic devices like Gameboys and iPods, but recently groups have taken on tougher challenges like light bulbs and puppies.

The LOA group was in a race with another hacker group, the Stuttering Monarchs, to be the first to bag the potato. “The potato has been the vegetable that everyone has been gunning for, because it’s so versatile like Linux itself. You can boil ‘em, mash ‘em, stick ‘em in a stew,” said Piest. “You’d think we’d get some sort of reward for this, but it’s all about bragging rights for us.”

LOA was the group that first installed Linux on a Shetland pony in 2003, but growing competition from other hacker groups have shut them out in the past five years.

“We were close to being the first with Linux on a cracker, but those jerks from Norway beat us out,” said Piest.

The first vegetable Linux install was on a head of iceberg lettuce by a group of hackers in Turkey.

Well, a fellow Oracle DBA who runs a blog at http://oraclue.wordpress.com put up a post about something called OraSRP. It is very detailed, and I thought I would pass it on to any DBA who would like to look at profiling Oracle via 10046 event trace data and get some great analysis on their trace files.

You can even create Google Charts for it. Very cool!

Best of all, It’s FREE!

Enjoy : )

The company is backed by $33 million in venture capital. They claim that they are indexing more than 120 billion web pages which is greater than Google’s 8.2 million (even though Google doesn’t publish how many they scan). Anyways, enough of the marketing muscle. I wanted to give it a try.

I did some comparative search results between Google and Cuil. I found that Google blows cuil away; however, your comparisons may differ.

Some examples:

I searched for an Oracle 600 error. Google found information, Cuil said no results found.

I decided something basic, so I searched for ".NET". Google brought up a list of results for Microsoft. Cuil said, results not found.

So I did a search of "cuil" on Cuil and guess what? It’s not self-aware! It can’t even find itself, so what makes me think it is capable of finding what I am searching for? This is not even a Google Killer. It’s a Cuil killer. It’s pretty bad when it kills itself.

So I tried a simple search. I searched for trees. BINGO! Cuil has plenty of information about trees. The problem with cuil is I can’t really use it, at least not yet. It has a long way to go to catch up with even Microsoft Live Search, much less, Google.

They spent $33 Million to build this? Alta-Vista was better back in the day. The thing with Google, is it was working well before I ever heard of it. It spread via word-of-mouth, and it is synonymous with web search. Cuil would have been better off waiting until the search technology was well refined.

Have you tried Cuil.com? What do you think?

Inquiring mind(s) want to know : )

A couple of things to note. I only have 1 NetBackup policy of Oracle type assigned to this server by my Veritas NetBackup admin. Also, that policy is set to infinite retention so that RMAN controls when it wants to delete expired / obsolete backups by allocating a channel "for maintenance device type ‘SBT_TAPE’. A good practice is working with your NetBackup admin in order to configure your policy. You could also define multiple policies so he can more easily identify which backups to send offsite. The only problem with this approach, and it’s very minor at that, is that you will have to explicitly define your Oracle type NetBackup Policy when you do your backups.

To define them, you could do several things such as:

send 'NB_ORA_POLICY=some_policy';

or 

allocate channel t1 type 'sbt_tape' parms='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64.1, ENV=(NB_ORA_POLICY=<your_oracle_policy_name>, NB_ORA_SERV=<your_master_server_name>)';

or

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NB_ORA_POLICY=<your_oracle_policy_name>, NB_ORA_SERV=<your_master_server_name>';

Now, if you did the last one, issue a "show all", it should be updated.

To more fully understand how NetBackup / Oracle integration worked, we turned on some tracing on the NetBackup side. What appears to happen is that Oracle engages the local NetBackup client via the libobk.so library and informs it that it wants to begin a backup. NetBackup then calls back to your server to connect and the backup begins. All NetBackup knows is it is backing up a file, which is the backupset. All the Oracle files are within the backup set. The backupset names should be unique. When you do a restore, Oracle then communicates with NetBackup and informs it that I need this "file". The file name can also be discovered by issuing the "list backup" command, which proves they are unique. Since there is only 1 filename, all NetBackup does is identify which tape the file(s) are on, loads the tape, finds the file, and sends them over the network back to RMAN which then opens the backup set and retrieves the file(s) it needs.

I created a test database in archive log mode. I then configured the default RMAN properties for that database to use ‘SBT_TAPE’ instead of DISK and enabled autobackup. I then backed up the database which then went to tape. Upon the successfull completion of the backup, I proceeded to use the ASMCMD command to delete this test database out of ASM. You can also do this restore without ASM or vice versa be renaming the datafile as you do the restore, however I just chose to use ASM for the database and will restore to it because there are a couple of added steps and I wanted this example to "scale" well : ). With no database, controlfiles, redologs, tempfiles, and spfile on disk, I tried a full restore of the database from tape.

Assuming you have a database in place, this is how you would configure RMAN and back it up via NetBackup.

First, we need to connect to our database and the recovery catalog. (If you are not using a recovery catalog, then you can skip this step).

testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> export ORACLE_SID=rmantest
testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> rman target / rcvcat rman/password@rmanrepository      

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jul 27 00:29:25 2008      

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

connected to target database: RMANTEST (DBID=3675070410)
connected to recovery catalog database     

RMAN> register database;      

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Next, we will configure the RMAN defaults.

RMAN> show all;      

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_rmantest.f'; # default     

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';      

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete      

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;      

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete  

This next step is not necessary but makes it easier to identify when you issue a "list backup" command in RMAN.

RMAN> configure controlfile autobackup format for device type SBT_TAPE TO 'cf_%F';      

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO 'cf_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete  

Finally, we verify everything.

RMAN> show all;      

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO 'cf_%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_rmantest.f'; # default  

Now let’s run a backup. I will include the archivelogs in the database backup. I will also include the spfile just in case even though it and the controlfile will be automatically backed up.

RMAN> backup database archivelog all delete input spfile;      

Starting backup at 27-Jul-2008-08:42:32
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting full datafile backupset
channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DG1/rmantest/datafile/system.407.661134077
input datafile fno=00003 name=+DG1/rmantest/datafile/sysaux.406.661134077
input datafile fno=00005 name=+DG1/rmantest/datafile/example.403.661134169
input datafile fno=00002 name=+DG1/rmantest/datafile/undotbs1.396.661134077
input datafile fno=00004 name=+DG1/rmantest/datafile/users.398.661134077
channel ORA_SBT_TAPE_1: starting piece 1 at 27-Jul-2008-08:42:36
channel ORA_SBT_TAPE_1: finished piece 1 at 27-Jul-2008-08:45:21
piece handle=03jmh43s_1_1 tag=TAG20080727T084234 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:02:45
channel ORA_SBT_TAPE_1: starting archive log backupset
channel ORA_SBT_TAPE_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=661134995
input archive log thread=1 sequence=3 recid=2 stamp=661135027
input archive log thread=1 sequence=4 recid=3 stamp=661135039
input archive log thread=1 sequence=5 recid=4 stamp=661135108
input archive log thread=1 sequence=6 recid=5 stamp=661135189
channel ORA_SBT_TAPE_1: starting piece 1 at 27-Jul-2008-08:45:22
channel ORA_SBT_TAPE_1: finished piece 1 at 27-Jul-2008-08:46:08
piece handle=04jmh491_1_1 tag=TAG20080727T084234 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:47
channel ORA_SBT_TAPE_1: deleting archive log(s)
archive log filename=+DG1/rmantest/archivelog/2008_07_27/thread_1_seq_2.400.661134995 recid=1 stamp=661134995
archive log filename=+DG1/rmantest/archivelog/2008_07_27/thread_1_seq_3.399.661135027 recid=2 stamp=661135027
archive log filename=+DG1/rmantest/archivelog/2008_07_27/thread_1_seq_4.408.661135039 recid=3 stamp=661135039
archive log filename=+DG1/rmantest/archivelog/2008_07_27/thread_1_seq_5.416.661135109 recid=4 stamp=661135108
archive log filename=+DG1/rmantest/archivelog/2008_07_27/thread_1_seq_6.415.661135189 recid=5 stamp=661135189
channel ORA_SBT_TAPE_1: starting full datafile backupset
channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_SBT_TAPE_1: starting piece 1 at 27-Jul-2008-08:46:09
channel ORA_SBT_TAPE_1: finished piece 1 at 27-Jul-2008-08:46:54
piece handle=05jmh4ag_1_1 tag=TAG20080727T084234 comment=API Version 2.0,MMS Version 5.0.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:46
Finished backup at 27-Jul-2008-08:46:54      

Starting Control File and SPFILE Autobackup at 27-Jul-2008-08:46:54
piece handle=cf_c-3675070410-20080727-01 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 27-Jul-2008-08:48:01      

RMAN> exit

Now we will annihilate the database on the server. We begin by shutting the DB down. (NOTE: you could have also entered this command within the RMAN console)

testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> echo $ORACLE_SID
rmantest
testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> sqlplus / as sysdba      

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jul 27 09:01:30 2008      

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.      

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options      

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

We now need to delete the database files / folders. The instructions for ASM are below. If a filesystem, it should be very simple to delete.

testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> export ORACLE_SID=+ASM
testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> asmcmd -p      

ASMCMD [+] > cd DG1
ASMCMD [+DG1] > ls
rmantest/
ASMCMD [+DG1/rmantest] > rm -r rmantest
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DG1] > cd +DG2
ASMCMD [+DG2] > rm -r rmantest
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DG2/] > exit

Now let’s assume that you had rebuilt ASM and installed the ORACLE 10g binaries and patched to the same version or greater than where your DB compatibility mode was, or you can assume that the DB just crashed and you are recovering it. I am assuming that ASM and the binaries are fine and all the prereqs have been done but that my directory for my database in ASM is not there.

I will connect to ASM and mkdir for my database in the first diskgroup so that I can restore my spfile or I will get an error that the rmantest directory does not exist.Our $ORACLE_SID already equals +ASM (It would be +ASM1 etc… in a RAC environment)

testserver:/export/home/oracle> asmcmd -p
ASMCMD [+] > cd DG1
ASMCMD [+DG1] > mkdir rmantest
ASMCMD [+DG1] > exit

Next, we need to connect to RMAN and start the database in nomount mode. You will need a dummy pfile to initially get the processes started. Here is the example of mine below. Remember, all you care about is getting the instance started in nomount mode so you can restore your original spfile to ASM. Once you do that, you will shutdown the instance and restart with the correct spfile in nomount mode.

*.aq_tm_processes=0
*.background_dump_dest='/u01/app/oracle/admin/rmantesting/bdump'
*.compatible='10.2.0.4.0'
*.control_files='+DG1/rmantesting/controlfile/current.320.644796453','+DG2/rmantesting/controlfile/current.257.644796179'
*.core_dump_dest='/u01/app/oracle/admin/rmantesting/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='rmantest'
*.db_recovery_file_dest_size=4G
*.db_recovery_file_dest='+DG2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rmantestingXDB)'
*.job_queue_processes=0
*.log_archive_format='%t_%s_%r.dbf'
*.nls_length_semantics='BYTE'
*.open_cursors=300
*.optimizer_dynamic_sampling=3
*.parallel_max_servers=100
*.pga_aggregate_target=838860800
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.session_cached_cursors=100
*.sessions=105
*.sga_max_size=800M
*.sga_target=400M
*.star_transformation_enabled='TRUE'
*.undo_management='AUTO'
*.undo_retention=900
*.user_dump_dest='/u01/app/oracle/admin/rmantesting/udump'

Now we need to find our DBID from the RMAN catalog by directly logging into sqlplus and executing the following query. (NOTE: you will see all the DB’s registered in your recovery catalog)

testserver:/export/home/oracle> sqlplus rman/password@rmanrepository      

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jul 27 09:02:50 2008      

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.      

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options      

SQL> select distinct db_id, db_name
from db a, dbinc b
where a.db_key=b.db_key;
  2    3
     DB_ID DB_NAME
---------- --------
3675070410 RMANTEST      

1 row selected.      

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

We now have all the information we need to do a restore, so we now execute RMAN.

testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> export ORACLE_SID=rmantest
testserver:/u01/app/oracle/product/10.2.0/agent10g/bin> rman target / rcvcat rman/password@rmanrepository      

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jul 27 09:29:51 2008      

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

connected to target database (not started)
connected to recovery catalog database     

RMAN> set dbid=3675070410      

executing command: SET DBID
database name is "RMANTEST" and DBID is 3675070410      

RMAN> startup pfile='/export/home/oracle/dummypfile.ora' nomount;      

Oracle instance started      

Total System Global Area     838860800 bytes      

Fixed Size                     2044328 bytes
Variable Size                566234712 bytes
Database Buffers             264241152 bytes
Redo Buffers                   6340608 bytes      

RMAN>

Now we will restore our spfile, shutdown the instance, and restart with the correct spfile in nomount mode. We will then restore our controfile and mount the database.

RMAN> restore spfile;      

Starting restore at 27-Jul-2008-09:51:58
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1      

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: restoring SPFILE
output filename=+DG1/rmantest/spfilermantest.ora
channel ORA_SBT_TAPE_1: reading from backup piece cf_c-3675070410-20080727-01
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=cf_c-3675070410-20080727-01 tag=TAG20080727T084705
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:27
Finished restore at 27-Jul-2008-09:54:33      

RMAN> shutdown immediate;      

Oracle instance shut down      

RMAN> startup nomount      

connected to target database (not started)
Oracle instance started      

Total System Global Area     838860800 bytes      

Fixed Size                     2044264 bytes
Variable Size                213913240 bytes
Database Buffers             616562688 bytes
Redo Buffers                   6340608 bytes      

RMAN> restore controlfile;      

Starting restore at 27-Jul-2008-09:55:30
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=156 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 5.1 (2006110403)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK      

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: reading from backup piece cf_c-3675070410-20080727-01
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=cf_c-3675070410-20080727-01 tag=TAG20080727T084705
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:53
output filename=+DG1/rmantest/controlfile/current.397.661168569
output filename=+DG2/rmantest/controlfile/current.377.661168573
Finished restore at 27-Jul-2008-09:56:33      

RMAN> alter database mount;      

database mounted
released channel: ORA_SBT_TAPE_1
released channel: ORA_DISK_1

We need to list our backups and find the greatest SCN # we have available (which will only be on tape in this example).

RMAN> list backup;      

List of Backup Sets
===================      

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1911719 Full    624.00M    SBT_TAPE    00:47:11     27-Jul-2008-01:24:13
        BP Key: 1911720   Status: AVAILABLE  Compressed: NO  Tag: TAG20080727T003701
        Handle: 01jmg7le_1_1   Media:
  List of Datafiles in backup set 1911719
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1       Full 578112     27-Jul-2008-00:37:02 +DG1/rmantest/datafile/system.407.661134077
  2       Full 578112     27-Jul-2008-00:37:02 +DG1/rmantest/datafile/undotbs1.396.661134077
  3       Full 578112     27-Jul-2008-00:37:02 +DG1/rmantest/datafile/sysaux.406.661134077
  4       Full 578112     27-Jul-2008-00:37:02 +DG1/rmantest/datafile/users.398.661134077
  5       Full 578112     27-Jul-2008-00:37:02 +DG1/rmantest/datafile/example.403.661134169      

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1913346 Full    628.25M    SBT_TAPE    00:02:43     27-Jul-2008-08:45:19
        BP Key: 1913354   Status: AVAILABLE  Compressed: NO  Tag: TAG20080727T084234
        Handle: 03jmh43s_1_1   Media:
  List of Datafiles in backup set 1913346
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1       Full 592007     27-Jul-2008-08:42:36 +DG1/rmantest/datafile/system.407.661134077
  2       Full 592007     27-Jul-2008-08:42:36 +DG1/rmantest/datafile/undotbs1.396.661134077
  3       Full 592007     27-Jul-2008-08:42:36 +DG1/rmantest/datafile/sysaux.406.661134077
  4       Full 592007     27-Jul-2008-08:42:36 +DG1/rmantest/datafile/users.398.661134077
  5       Full 592007     27-Jul-2008-08:42:36 +DG1/rmantest/datafile/example.403.661134169      

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1913347 10.25M     SBT_TAPE    00:00:43     27-Jul-2008-08:46:04
        BP Key: 1913355   Status: AVAILABLE  Compressed: NO  Tag: TAG20080727T084234
        Handle: 04jmh491_1_1   Media:      

  List of Archived Logs in backup set 1913347
  Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
  ---- ------- ---------- -------------------- ---------- ---------
  1    2       574463     27-Jul-2008-00:24:19 578093     27-Jul-2008-00:36:34
  1    3       578093     27-Jul-2008-00:36:34 578116     27-Jul-2008-00:37:07
  1    4       578116     27-Jul-2008-00:37:07 578121     27-Jul-2008-00:37:19
  1    5       578121     27-Jul-2008-00:37:19 578145     27-Jul-2008-00:38:28
  1    6       578145     27-Jul-2008-00:38:28 578181     27-Jul-2008-00:39:49      

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1913348 Full    256.00K    SBT_TAPE    00:00:41     27-Jul-2008-08:46:49
        BP Key: 1913356   Status: AVAILABLE  Compressed: NO  Tag: TAG20080727T084234
        Handle: 05jmh4ag_1_1   Media:
  SPFILE Included: Modification time: 27-Jul-2008-00:25:29      

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
1913374 Full    7.00M      SBT_TAPE    00:00:47     27-Jul-2008-08:47:52
        BP Key: 1913376   Status: AVAILABLE  Compressed: NO  Tag: TAG20080727T084705
        Handle: cf_c-3675070410-20080727-01   Media:
  Control File Included: Ckp SCN: 592110       Ckp time: 27-Jul-2008-08:47:05
  SPFILE Included: Modification time: 27-Jul-2008-00:25:29

We see an SCN of 592007 in backup set 1913346. All of our archivelogs appear to be less than this, so this is the number we will use.

RMAN> restore database until scn 592007;      

Starting restore at 27-Jul-2008-09:59:50
Starting implicit crosscheck backup at 27-Jul-2008-09:59:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Finished implicit crosscheck backup at 27-Jul-2008-09:59:52      

Starting implicit crosscheck copy at 27-Jul-2008-09:59:52
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-Jul-2008-09:59:52      

searching for all files in the recovery area
cataloging files...
no files cataloged      

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=156 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 5.1 (2006110403)
using channel ORA_DISK_1      

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DG1/rmantest/datafile/system.407.661134077
restoring datafile 00002 to +DG1/rmantest/datafile/undotbs1.396.661134077
restoring datafile 00003 to +DG1/rmantest/datafile/sysaux.406.661134077
restoring datafile 00004 to +DG1/rmantest/datafile/users.398.661134077
restoring datafile 00005 to +DG1/rmantest/datafile/example.403.661134169
channel ORA_SBT_TAPE_1: reading from backup piece 03jmh43s_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=03jmh43s_1_1 tag=TAG20080727T084234
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:26
Finished restore at 27-Jul-2008-10:02:34      

RMAN> recover database;      

Starting recover at 27-Jul-2008-10:07:17
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1      

starting media recovery      

archive log thread 1 sequence 7 is already on disk as file +DG2/rmantest/onlinelog/group_1.873.661134159
archive log filename=+DG2/rmantest/onlinelog/group_1.873.661134159 thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-Jul-2008-10:07:37     

RMAN>

Now we must open with the resetlogs option. This will create a new incarnation of the database and the controlfile and recovery catalog will be updated and resynced.

RMAN> alter database open resetlogs;      

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete      

RMAN> exit

Now let’s verify we can login to the database.

testserver:/export/home/oracle> export ORACLE_SID=rmantest
testserver:/export/home/oracle> sqlplus / as sysdba      

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jul 27 10:09:06 2008      

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.      

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options      

SQL>

Now just verify via ASMCMD that your TEMPFILE and UNDO Tablespaces are there. Mine were.