How to Create an Empty RAC Database without Using DBCA

  • by

Let me ask you a question, why would you like to create an empty RAC database? In my experience, there're two use cases that can apply the purpose.

  • Building a testing RAC database.
  • Building a standby RAC database.

Normally, we create an empty RAC database by DBCA on the target server for further database duplication. But the problem is, the auxiliary database don't need the whole database structure and data files. Everything except SPFILE and password file can be derived from the source database. Besides, DBCA takes a pretty longer time to complete the creation.

In this post, I will show you how to create a really empty database for duplication without using DBCA, which includes two major parts.

  1. Register the database to the cluster.
  2. Create SPFILE and password file to the correct destination.
  3. Restore Controlfile and Mount the Database.

Before proceeding, I have to assume that you have prepared a cluster environment. The first one is the grid infrastructure and necessary disk groups have been installed. The second one is Oracle software (Oracle Home) have been installed.

A. Register a Database to the Cluster

1. Check Current Status

You have to make sure all services and resources are online by this:

[grid@primary01 ~]$ crsctl check crs [grid@primary01 ~]$ crsctl status res -t

2. Add Database to Cluster

Now we add the database to the cluster using srvctl add database. The plain and copy-able code would be:

Duplicated DB

srvctl add database \
-db ORCLCDB \
-dbname ORCLCDB \
-oraclehome /u01/app/oracle/product/19.0.0/db_1 \
-spfile +DATA/ORCLCDB/spfile \
-pwfile +DATA/ORCLCDB/pwfile \
-startoption OPEN \
-stopoption IMMEDIATE \
-role PRIMARY \
-policy AUTOMATIC \
-diskgroup DATA \
-dbtype RAC

Please note that, -db is for database unique name and -dbname is for database name. For your reference, there're more parameters of srvctl add database for Oracle database 19c.

We execute the command as below.

[oracle@primary01 ~]$ srvctl add database \
> -db ORCLCDB \
> -dbname ORCLCDB \
> -oraclehome /u01/app/oracle/product/19.0.0/db_1 \
> -spfile +DATA/ORCLCDB/spfile \
> -pwfile +DATA/ORCLCDB/pwfile \
> -startoption OPEN \
> -stopoption IMMEDIATE \
> -role PRIMARY \
> -policy AUTOMATIC \
> -diskgroup DATA \
> -dbtype RAC

It returns no message if successful.

Standby DB

If this is a standby database, you should also change the following items:

srvctl add database \
-db ORCLSTB \
-dbname ORCLCDB \
-oraclehome /u01/app/oracle/product/19.0.0/db_1 \
-spfile +DATA/ORCLCDB/spfile \
-pwfile +DATA/ORCLCDB/pwfile \
-startoption MOUNT \
-stopoption IMMEDIATE \
-role PHYSICAL_STANDBY \
-policy MANUAL \
-diskgroup DATA \
-dbtype RAC

3. Add Instances to the Database

Then we add two instances to the database.

[oracle@primary01 ~]$ srvctl add instance -db ORCLCDB -instance ORCLCDB1 -node primary01
[oracle@primary01 ~]$ srvctl add instance -db ORCLCDB -instance ORCLCDB2 -node primary02

Please note that, you have to use upper-cased instance names to do srvctl add instance. Otherwise, you will get ORA-29760: instance_number parameter not specified when you try to startup the RAC database. (Is it a bug or something?)

Let's make sure the configuration of the database is correct.

[oracle@primary01 ~]$ srvctl config database
ORCLCDB
[oracle@primary01 ~]$ srvctl config database -d orclcdb
Database unique name: ORCLCDB
Database name: ORCLCDB
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATA/ORCLCDB/spfile
Password file: +DATA/ORCLCDB/pwfile

Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORCLCDB1,ORCLCDB2
Configured nodes: primary01,primary02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

It looks fine.

B. Create SPFILE and Password File

As you have noticed, I set two files in the database configuration. One is SPFILE, the other is the password file. You have to know how to copy files out of ASM, then send them to the correct destination.

Let's see what we have from the production database.

[oracle@primary01 ~]$ ll /tmp/*file
-rwxrwxrwx 1 root root 2048 Nov  3 10:54 /tmp/pwfile
-rwxrwxrwx 1 root root 4608 Nov  3 10:54 /tmp/spfile

1. Copy Password File

Copying a password to the correct destination is rather easy than copying SPFILE. So we do it first.

[grid@primary01 ~]$ asmcmd
ASMCMD> mkdir +DATA/ORCLCDB/
ASMCMD> pwcopy --dbuniquename ORCLCDB /tmp/pwfile +DATA/ORCLCDB/pwfile -f
ASMCMD-9453: failed to register password file as a CRS resource
copying /tmp/pwfile -> +DATA/ORCLCDB/pwfile
ASMCMD-9453: failed to register password file as a CRS resource

Error ASMCMD-9453 is ignorable in this case. If you do care the error, you can find out more in How to Resolve ASMCMD-9453: failed to register password file as a CRS resource.

In the above, we use pwcopy with providing DB_UNIQUE_NAME to copy password file to the destination, therefore, ASM knows how to handle this file. More specifically, ASM will put the file to the correct directory which is DB_UNIQUE_NAME based. In other words, the file will fall into DB_UNKNOWN directory if you don't specify --dbuniquename option.

2. Create SPFILE

Our goal in this step is to create SPFILE to the correct destination. Please be patient, the approach is a little complicated. There's a reason for doing this.

Create audit directory

First of all, create the audit directory for the instance on both nodes, because we're going to connect to the database by OS authentication, which will trigger an auditing. Otherwise, you may see ORA-09925: Unable to create audit trail file.

The absolute path should be like this.

[oracle@primary01 ~]$ mkdir -p /u01/app/oracle/admin/ORCLCDB/adump

Create PFILE

We need a parameter file for later startup.

[oracle@primary01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile='/tmp/pfile' from spfile='/tmp/spfile';

File created.

Startup NOMOUNT

We use the PFILE to startup the database to NOMOUNT. Don't worry about the automatic restart of the RAC database, because there's no SPFILE presently.

SQL> startup nomount pfile='/tmp/pfile';
ORACLE instance started.
...

Create SPFILE

Then we use the PFILE to create SPFILE.

SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/tmp/pfile';

File created.

Then we shutdown the instance.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

Optionally, you can point the location of SPFILE in PFILE on both nodes.

[oracle@primary01 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
spfile='+DATA/ORCLCDB/spfile'
[oracle@primary02 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
spfile='+DATA/ORCLCDB/spfile'

3. Check Files

Let's see whether the two files are correctly set or not.

ASMCMD> ls -l +DATA/ORCLCDB
Type           Redund  Striped  Time             Sys  Name
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
PASSWORD       UNPROT  COARSE   NOV 03 11:00:00  N    pwfile => +DATA/ORCLCDB/PASSWORD/pwdorclcdb.257.1055504867
PARAMETERFILE  UNPROT  COARSE   NOV 03 11:00:00  N    spfile => +DATA/ORCLCDB/PARAMETERFILE/spfile.256.1055503837

Did you notice the highlighted text above? If you found ASM put their entities in UNKNOWN or DB_UNKNOWN directory, then you copied the files in the wrong way, even though the database is able to startup normally. So far, they are all in the correct path.

Further reading: How to Resolve SPFILE in DB_UNKNOWN.

4. Startup NOMOUNT

In this step, we'll try to startup the database to NOMOUNT by SRVCTL.

[oracle@primary01 ~]$ srvctl start database -d orclcdb -o nomount
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02
[grid@primary01 ~]$ crsctl status res ora.orclcdb.db
NAME=ora.orclcdb.db
TYPE=ora.database.type
TARGET=ONLINE             , ONLINE
STATE=ONLINE on primary01, ONLINE on primary02

C. Restore Controlfile and Mount the Database

1. Restore Controlfile

If we startup the database to NOMOUNT without problem, then we can restore the control files. I assume that you have a copy of current control file from the source database, no matter the copy is for standby or not.

[oracle@primary01 ~]$ rman target /
...
connected to target database: ORCLCDB (not mounted)

RMAN> restore controlfile from '/home/oracle/control01.ctl';

Starting restore at 03-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=ORCLCDB1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCLCDB/control01.ctl
output file name=+DATA/ORCLCDB/control02.ctl

Finished restore at 03-NOV-20

Except for copying files by ASMCMD, you may also get the copy by the following posts.

2. MOUNT the Database

We mount the database on both nodes.

Node 1

SQL> alter database mount;

Database altered.

Since ALTER DATABASE MOUNT can work only on the current instance, we need to do the same action on other nodes.

Node 2

SQL> alter database mount;

Database altered.

3. Check current status.

SQL> select inst_id, name, open_mode from gv$database;

   INST_ID NAME      OPEN_MODE
---------- --------- --------------------
         2 ORCLCDB   MOUNTED
         1 ORCLCDB   MOUNTED

We are ready to duplicate the database. As for /etc/oratab, it doesn't matter for a cluster database, except that you use oraenv to set environment variables very often. You can add an entry or not.

Leave a Reply

Your email address will not be published. Required fields are marked *