I’ve just created a new database on an HP-UX server. The database is Oracle 9i (yes, I know, I know!) and no matter what I do, I can’t connect via the listener without getting the dreaded ORA-12505: TNS:listener could not resolve SID given in connect descriptor error message. I’ve done this lots of times in the past, but for some reason, I can’t get it to work today. It’s driving me mad!

In part 6 of this mini-series, I left you with a backed up database, having using RMAN to take a hot backup. This episode looks at restoring and recovering from hot backups. The joy of this is that most of the time you don’t need to have everyone off of the database twiddling their thumbs while you restore and recover, just anyone in those areas affected. Restoration and recovery is full in that the database will be completely recovered right up to date after the restore and recovery is finished.

Due to a document not being supplied to me recently, I built a few 11g databases with the wrong SID. I needed to change them all. I found a few web pages, Oracle and others, on using the nid utility to do just that, however, they were incomplete. The full process is described here. The following has been tested fully on an 11.2.0.2 Oracle database. The nid utility used also exists in Oracle 10.

In the past few instalments, I’ve looked mainly at databases running in NOARCHIVELOG mode. It’s probably not the best way to run a production system but it is valid to do so. For the safest systems and the ability to recover from numerous disasters without losing any data, the database should be run in ARCHIVELOG mode. The remainder of the mini-series concentrates on the things you can do with RMAN when the database is running in ARCHIVELOG mode.

Previously in this mini-series, I managed to dump and recover a database from a cold backup. Before I move on to similar practices with hot backups, a few terms and observations may well be in order. Terms and Conditions A restore is the action of copying files back from a backup, prior to recovery. Recovery is carried out after restoring the files and allows the database to be recovered in full or to a specific point in time, using backups of archived logs, and/or the current archived logs - which may not have been backed up yet.

Frits Hoogland’s post on how to clone your Oracle Home rather than installing from scratch. Nice! One thing to beware of, if you have already applied a PSU, then you must ensure that you also include the .patch_contents hidden directory, if you don’t, you will not be able to apply any further PSUs to the cloned home(s) created from the tarball.

So far I have managed to dump and recover a database running in ARCHIVELOG mode. That is the most sensible mode for a production database and will be the case for the rest of this small RMAN guide. However, what if your databases are not running in ARCHIVELOG mode? What can RMAN do for you? Running a Database in NOARCHIVELOG Mode The database is shutdown at the moment, so we will MOUNT it using RMAN and create a brand new full cold backup in the FRA.

At the end of Oracle RMAN for Beginners – Part 2 I had created a few backups of the ant12 database. In this part, I explore how to trash a database and recover from that trashing using the cold backups taken. Bear in mind that any work done after the cold backup was taken will be lost. With a cold backup, you cannot roll forward to re-apply archived logs etc because when the backup was taken, the database was in a consistent state.

At the end of Oracle RMAN for Beginners - Part 1 I was ready to begin a backup of the ant12 database. Read on … Cold Backup - Backupset Type There are two different types of cold backup. The first uses RMAN’s own internal format for the dump files - a backupset - the other uses image copies of the individual database files. In this part I’ll concentrate on a backuset type of cold backup.

Introduction One of the things a DBA needs to be aware of, is RMAN. This has been around since Oracle 8 (or was it 8i?) and has been improving since then. It’s almost pretty good at 11.2! One of the things that I, as a DBA, need to get to grips with is RMAN. Most of the work I’ve been doing for the last few years have not involved very much in the way of RMAN usage.

I knew you could do this: 1 2 3 4 5 SQL> select 1234567890 as abc from dual; ABC ---------- 1234567890 or 1 2 3 4 5 SQL> select 1234567890 abc from dual; ABC ---------- 1234567890 But I didn’t know that this worked as well – there’s no space between the value and the alias name: 1 2 3 4 5 SQL> select 1234567890abc from dual; ABC ---------- 1234567890 So I did a bit of playing and discovered that there is a difference if the alias is D or F but no other single character:

You know the feeling, your 10g or 11g database displays a warning message about your use of deprecated parameters at startup, but it doesn’t say which parameters are deprecated? You could look through the manuals to find the list of all deprecated parameters then go hunting in your spfile/pfile for those and remove them, or, you could simply look in the alert.log. 1 2 3 4 5 6 7 8 .

If you install 10g and 11g on the same server, which one do you wish to supply the executables for “oraenv”? If you install 10g first and allow 11g to overwrite the files in /usr/local/bin when you run “root.sh” then when you eventually call oraenv to set a 10g environment, you get a warning that “$ORACLE_HOME/bin/orabase” cannot be found. If you allow the 10g files to overwrite the 11g ones, you don’t set ORACLE_BASE.