Difference between normal and standby database duplication

RMAN is a wonderful tool and has gone distances from being just another backup and recovery option. It simplifies a lot of things and has a lot of role to play in technologies like RAC, Data Guard etc. 

To setup a data guard environment the first thing you need is the another copy of your production database. RMAN duplication is the best way to get this done.

However normal duplication is not what you require. As you are using duplication for a standby database which means that your end database will still be dependent on your production database. This requires changes in how database is structured. As database structure will be different, so will be the control file. You can create a copy of control file for standby, backup it and register with RMAN so that RMAN can use it using one single command.

RMAN> backup current controlfile for standby;

Once you are done with that you can do all other things like creating pfile, instantiating instance and creating directories etc like you would do for normal duplication. Your target database will be your primary instance and auxiliary database will be your standby instance. Connect to both of them and issue the following command to start the duplication process.

RMAN> duplicate target database for standby dorecover;

The above command will duplicate database, recover it to the latest SCN and then will bring it up in mounnt state. It will not open the database and neither it is required because nature of database will be standby. 

Create pfile/spfile from memory in oracle 11g

Starting from version 11g of database, oracle now allows you to create pfile or spfile from memory. As you know both of these parameter files serve the similar purpose of instantiating the database instance. It was possible to create one file if you had the other one available. But now you can create either of them even if you don't have any of them but you have database up and running.

The simple command to do so will be:

SQL> create pfile from memory;

for spfile:

SQL> create spfile from memory;

The file created this way will list the values of 150 essential parameters. It is not necessary that you had provided an explicit value for any of them or not, oracle will create file with those 150 parameter values along with their values either one you provided or the default ones. 

DBCA could not detect the database

If you have created the database manually or you have duplicated the database using RMAN duplication, you may have encountered this error. Apparently there won't be any errors anyway. You will be able to connect to your database using SQL*PLUS or any other client tool and perform all the normal operations. But DBCA and other graphical tools like DBUA won't be able to detect that database exists.

Another problem caused by this is that you won't be able to configure database for EM control. Not appearing in DBCA also means that you cannot delete the database completely in one go and configure several other options. 

Enough problems....

But the reason and solution is simple. Every database not created using DBCA will not be registered in "oratab" file. The file is located in "/etc" folder and is used by database startup and shutdown scripts. But it is also used by GUI tools to locate what database services are running. So just add an entry for your database in the file and save it. Run the DBCA again and all will be OK.

Typical entry in /etc/oratab entry will look like the following.

ORCL:/u01/app/oracle/product/11.2.0/dbhome_1:N

RMAN, creating recovery catalog

Recovery catalog is oracle's recommendation to use with rman. If you don't use recovery catalog then all database backup/recovery related information will be stored inside the control file of your database. However it is strongly recommended that you use recovery catalog and take regular backups of catalog as well. Here is how you can create recovery catalog. The example assumes that you are creating recovery catalog schema inside your target database.

The process is simple. You will create a tablespace to hold catalog information, a user who will own the recovery catalog and then create the catalog while logging into that schema through rman client.

Here is the process in practice.  


SQL> create tablespace ts_catalog
  2  datafile '/u01/app/oracle/oradata/VSTDB01/catalog.dbf'
  3  size 100M
  4  autoextend on
  5  maxsize 400M;

Tablespace created.

SQL> create user rman identified by rman
  2  default tablespace ts_catalog;

User created.

SQL> grant connect,resource,recovery_catalog_owner to rman;

Grant succeeded.

$ rman catalog rman/rman@VSTDB01

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 20 13:57:58 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace ts_catalog;

recovery catalog created

RMAN> connect target /

connected to target database: VSTDB01 (DBID=2542636510)

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN connect to Auxiliary Fails With ORA-01031: INSUFFICIENT PRIVILEGES

If you are getting this error when you are trying to connect like this

$ rman auxiliary /

then it may be because your OS user is not added to either DBA or OINSTALL system groups. This is easy to resolve. But if you are getting this error while doing the below

$ rman auxiliary sys/oracle@AUX1

and you know that you have configured everything like Listener and TNSNAMES etc then the resolution is a bit tricky. The auxiliary database is mostly used when you are duplicating a database or something. There are situations where you must connect to your auxiliary database using service name like when you are duplicating from active database without any backup.

In these situations you can't change your password file using SQL*PLUS because database will be in no-mount state. Here is the two step solution.

1. Copy your target database's password file and rename it so that the instance names are replaced.
2. Make sure that instance and service names are same in the following locations and files. They must be same and at the same time all of them must be in UPPER case

Here are the locations where you need to verify and check the instance and service names.

• The db_name parameter in parameter file (init.ora)
• Instance name in Password file's name
• SID_NAME in Sid description section of Listener.ora
• Service name in tnsnames.ora entry

$ rman auxiliary sys/oracle@AUX1 

will work now.

Instantiating the ASM disk failed error

IT Problems can be very tricky sometimes. While installing ASM on Linux today I was having an issue with instantiating the disks. Everything seemed OK and the following command was giving me an all OK status

$ oracleasm status

But when I tried to mark the disks as ASM disks I continued to get the message that "Instantiating the disk failed". The following command was failing.

$ oracleasm createdisk DATAVOL /dev/sdb1

For two hours I was stuck with Google but unable to resolve the issue. The simple solution that came out was that SELinux was stopping this. I wasn't aware that SELinux on my host was enabled. I disabled the Firewall while installing the operating system but forgot about SELinux. I disabled and it worked like a charm.

This is where most of the IT problem originate from. When you disable the Firewall and SELinux on a production system, the System Admin may want to kill you. But if you don't you will be hard pressed to find a solution that is suitable for both DBA and System Admin. 

Fortunately mine was a personal test system and was not required to run away from any System Admin. 

Installing Linux packages for oracle ASM installation

I have been playing around with ASM quite often now a day. The thing that confused quite a lot while installing ASM was the pre installations tasks. You need several Linux packages before you can start the Universal Installer.

What needs to be understood is that when you use ASM as your storage type for database, the operating system does not have any control over the disks that are being used. The packages which are required to be installed basically help Linux mark those disks as Oracle ASM disks. You can generally relate this to a File System for better understanding and packages help to build that file system. So here is what you need to install and how you can do it using Yum.

• oracleasm
• oracleasm-support
• oracleasm-lib

You are not required to install the first package if you are using the latest version of oracle linux. It is built into the Linux Kernel. To install the second one you can use the following command.

yum install oracleasm-support

The third package is not yet available in Yum repositories and you will have to download this from oracle's website separately. You can download it from here and save it to disk. Once saved, navigate to the directory where you have saved and issue the following command.

rpm -Uvh oracleasmlib-2.0.4-1.el6.i686.rpm