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.
- Register the database to the cluster.
- Create SPFILE and password file to the correct destination.
- 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:
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 \
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.
If this is a standby database, you should also change the following 3 items:
- Database Role
- DB Unique Name
- Startup Option
Change the database role into PHYSICAL_STANDBY.
Choose a different database unique name for data synchronization (data guard) later.
You may like to startup the standby RAC database to MOUNT automatically if you don't have the license to use active data guard.
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.
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
[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
Password file: +DATA/ORCLCDB/pwfile
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Mount point paths:
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, because ASM simply don't know which instance gonna use this password file. Remember? we have configured the password file while we adding the database to the cluster
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.
Of course, you can also use orapwd to create a password file for your database. Just aviod errors like OPW-00010.
[oracle@primary01 ~]$ srvctl modify database -d orclcdb -pwfile
[oracle@primary01 ~]$ orapwd dbuniquename=ORCLCDB file='+DATA/ORCLCDB/pwfile' entries=10
Enter password for SYS:
[oracle@primary01 ~]$ orapwd describe file='+DATA/ORCLCDB/pwfile'
Password file Description : format=12.2
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. The absolute path should be like this.
[oracle@primary01 ~]$ mkdir -p /u01/app/oracle/admin/ORCLCDB/adump
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';
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.
Then we use the PFILE to create SPFILE.
SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/tmp/pfile';
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
[oracle@primary02 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
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
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
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.
SQL> alter database mount;
Since ALTER DATABASE MOUNT can work only on the current instance, we need to do the same action on other nodes.
SQL> alter database mount;
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.