Control File Autobackups

One of the many features included in rman is the ability to automatically backup your control file. What is a control file? The control file contains information about your database including its physical structure. If you are using rman without a recovery catalog then your backup information is also stored in the control file. For an overview of control files take a look at Oracles Documentation.

It is very important for (some) recovery purposes to ensure you have a recent copy of your control file, especially if it contains your recovery catalog. There is one feature of control file autobackups which alot of dba's seem to be unfamiliar with, which is why I thought i'd write up a brief article on it. If you have this feature enabled and you make a structural change to the database, the control file is automatically backed up. ie, If you add a datafile, rename a file, etc. Information which could affect your ability to recover. This backup will be placed on disk even if your regular backup goes to tape.

A quick test. First, ensure you have control file autobackups enabled and select a location on disk. By default the control file backup will be placed in your $ORACLE_HOME/dbs directory unless you specify a location.

oravis@myserver=> rman target / nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 14:48:49 2008

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

connected to target database: VIS (DBID=767668735)
using target database control file instead of recovery catalog

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/bck/vis_backup/%F';y

A quick test to make sure the backup works and is on disk:

oravis@myserver=> rman target / nocatalog

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jan 16 15:25:24 2008

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

connected to target database: VIS (DBID=767668735)
using target database control file instead of recovery catalog

RMAN> backup current controlfile;

Starting backup at 16-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=414 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 16-JAN-08
channel ORA_DISK_1: finished piece 1 at 16-JAN-08
piece handle=/mnt1/app/visdb/1020_64bit/dbs/01j6adrd_1_1 tag=TAG20080116T152532 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 16-JAN-08

Starting Control File Autobackup at 16-JAN-08
piece handle=/oradata/bck/vis_backup/c-767668735-20080116-00 comment=NONE
Finished Control File Autobackup at 16-JAN-08

RMAN> exit

Recovery Manager complete.
oravis@myserver=> ls -alrt /oradata/bck/vis_backup
total 42036
drwxrwxr-x  28 oravis   dba         1024 Jan 16 14:47 ..
-rw-rw----   1 oravis   dba      19398656 Jan 16 15:25 c-767668735-20080116-00
drwxrwxr-x   2 oravis   dba          512 Jan 16 15:25 .

Next we need to make a structural change to the database to see if the control file is automatically backed up. For this example i'll add a small datafile to the system tablespace.

SQL> alter tablespace SYSTEM add datafile '/oradata/dbf/visdata/system09.dbf' size 25M;

Tablespace altered.

A directory listing of the backup location should show a second control file backup:

oravis@myserver=> ls -alrt /oradata/bck/vis_backup
total 79972
drwxrwxr-x  28 oravis   dba         1024 Jan 16 14:47 ..
-rw-rw----   1 oravis   dba      19398656 Jan 16 15:25 c-767668735-20080116-00
-rw-rw----   1 oravis   dba      19398656 Jan 16 15:26 c-767668735-20080116-01
drwxrwxr-x   2 oravis   dba          512 Jan 16 15:26 .

I promise my next post will be a bit more advanced and related to EBS.

Error creating Database Control

For our production environments we use Oracle Enterprise Manager and Grid Control to monitor our databases and applications. Not all of our dev/test environments are hooked into this for various reasons.. There is one 10g feature i'd like to try out in a sandbox but instead of hooking the environment into Grid Control I thought I would just use Database Control.

Note:278100.1, How To Drop, Create And Recreate DB Control In A 10g Database, describes the process which seems pretty straight forward. However, while running the command to drop or recreate the repository the following error graced my screen:

oravis@myserver=> ./emca -deconfig dbcontrol db

STARTED EMCA at Jan 14, 2008 11:07:40 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: MYSID 

Do you wish to continue? [yes(Y)/no(N)]: Y
UnsatisfiedLinkError exception loading native library: njni10
Exception in thread "main" java.lang.UnsatisfiedLinkError: get
        at oracle.net.common.NetGetEnv.get(Native Method)
<SNIP>

I have never encountered this error before so the first step was a quick search of metalink, which didn't turn up any useful notes or discussions. A couple seemed to be related but the solutions didn't resolve my issue.

So the next step was to do some more investigation. The njni10 libraries existed under the $ORACLE_HOME/lib and $ORACLE_HOME/lib32 directories, so that wasn't the issue. Next I used the unix command ldd which lists the dependencies for executables or shared objects.

oravis@myserver=> ldd lib32/libnjni10.so
warning: ldd: lib32/libnjni10.so: is not executable
        libclntsh.so.10.1 =>     (file not found)
        libnnz10.so =>   /mnt/app/visdb/1020_64bit/lib32/libnnz10.so
        libkstat.so.1 =>         /usr/lib/libkstat.so.1
        libnsl.so.1 =>   /usr/lib/libnsl.so.1
        libsocket.so.1 =>        /usr/lib/libsocket.so.1
        libgen.so.1 =>   /usr/lib/libgen.so.1
        libdl.so.1 =>    /usr/lib/libdl.so.1
        libsched.so.1 =>         /usr/lib/libsched.so.1
        libc.so.1 =>     /usr/lib/libc.so.1
        libaio.so.1 =>   /usr/lib/libaio.so.1
        libm.so.1 =>     /usr/lib/libm.so.1
        libmp.so.2 =>    /usr/lib/libmp.so.2
        /usr/platform/SUNW,Sun-Fire-V440/lib/libc_psr.so.1

Can you see the problem? The problem is that library libclntsh.so.10.1 doesn't exist for some reason. Since this is a cloned EBS environment I checked the source system and indeed the file is there. To make sure it wasn't a problem specific to this environment I checked some other cloned instances and most of those didn't have the file either.

This library can be recreated by running the following commands:

oravis@myserver=> cd $ORACLE_HOME/rdbms/lib
oravis@myserver=> make -f ins_rdbms.mk client_sharedlib
/mnt/app/visdb/1020_64bit/bin/genclntsh
/mnt/app/visdb/1020_64bit/bin/genoccish
/mnt/app/visdb/1020_64bit/bin/genagtsh /mnt/app/visdb/1020_64bit/lib/libagtsh.so 1.0

If you run ldd against the library again you will see that the dependency issue has been resolved and I could proceed with the directions in the note above to create database control.

oravis@myserver=> ldd lib32/libnjni10.so
warning: ldd: lib32/libnjni10.so: is not executable
        libclntsh.so.10.1 =>     /mnt/app/visdb/1020_64bit/lib32/libclntsh.so.10.1
<SNIP>

Another quick comment. When you run the command to create the database control repository, don't cut and paste your password. It doesn't like it and causes emca to fail. As to why I cut and paste the password? Our passwords are in an encrypted file and it was quicker to cut and paste it than type it in.

The next step is to verify if this is a cloning issue and if so, how to resolve it. So the next time I have to clone an environment i'll be keeping a watchful eye.

External Drives and Vista

For Xmas I bought myself a 1 Terabyte external drive. For awhile i've wanted to play around with vmware and multiple types of images but I didn't have enough space. The first item on my list was to download a copy of R12 and install it.

While staging the software tho I hit a snag. All of the files on the drive were read only. Even tho I am in the administrator group and signified my approval for all of the security popups, I could not modify files.

Based on a google search of "Vista external drive files read only" I soon realized this is a common problem. The root cause, as described in the link below, seems to be that the external drive was probably formated under Windows XP which causes permissions issues.

The article, written by Jesper Johansson provides 2 solutions. The first one didn't work for me... But the second, from an elevated command line did.

If I had known about this before I downloaded R12 and started using the drive for other tasks (about 250GB on it now), I would have reformated it first.