Recently I wanted to create a policy managed database in my 12.1.0.2 flex cluster setup, but free servers are not available. Hence I had to unregister an administrator managed RAC database (amdbh12) to make the servers available. Having completed my testing, I dropped the policy managed database and then wanted to drop the database amdbh12 using DBCA. It was then that I realized that:
- When a database is unregistered from srvctl,
- The database is no longer listed in DBCA.
- Database configuration information stored in OCR is removed.
- Automatically-generated entries for the database in
/etc/oratabare deleted. - The Server pool for administrator-managed database is automatically deleted.
- In order to drop the database using DBCA, I needed to register it with srvctl.
- SPfiles for various databases in the cluster may be located on the file system / shared storage depending upon the type of storage (Filesystem / Shared) used by the database.
- Password files for various databases in the cluster may be located on the file system / shared storage depending upon the type of database (Single instance / RAC / RAC One Node).
- To find out names of unregistered databases in the cluster, we can:
- Look at the contents of the backup of OCR taken when the database was still registered with srvctl
- Locate SPfile
- For single instance databases using file system storage: Across all database homes on all the nodes (if defaults have not been overridden).
- For RAC / RAC one Node databases (using ASM storage): On ASM diskgroup(s)
- Locate Password file
- For single instance databases : Across all database homes on all the nodes
- For RAC / RAC one Node databases (using ASM storage): on ASM diskgroup(s)
- While issuing a
srvctl add database ..command, Oracle Home should be specified without a “/” at the end, or the database will not be listed in DBCA. - From Oracle Database 12c onwards, it is mandatory to specify a password file (if stored on shared storage) when issuing
srvctl add database ..command to enable remote connection to the database. - There are different clauses of the
srvctl add database ..command to be specified for different types of databases in a cluster. - On re-registering a database, its entry in
/etc/oratabon a node is created only when the respective instance is started.
So, I decided to write an article about:
- Location / Naming conventions of various configuration files associated with different types of databases in a cluster.
- Re-registration of following types of databases in a cluster with srvctl.
- Single instance database with filesystem storage
- Single instance database with ASM storage
- Administrator managed RAC database
- Policy managed RAC database
- Administrator managed RAC One Node database
- Policy managed RAC One Node database
Environment:
- Operating System: RHEL 5.6
- Oracle 12.1.0.2c Flex Cluster
- Hub Nodes : host01, host02, host03
- Database version: 12.1.0.2c
- Database Home: /u01/app/oracle/product/12.1.0/dbhome_1/
These are the details of various types of databases and their related files in this cluster:
Click to view in new window.
It can be seen that:
- /etc/oratab
- Has an entry for the database on the nodes where instances are running / configured to run
- For a policy managed RAC ONE NODE database, the entry is initially created only on the node where instance is running. Later on, as the instance executes on other nodes in the cluster, the entry is created automatically
- Pfile
- Default location of Pfile = $ORACLE_HOME/dbs
- A Pfile is not created automatically for a single instance non-ASM database.
- The default format of name of Pfile for various types of databases is as follows:
- Single instance ASM database – init<SID>.ora
- Administrator managed RAC database – init<SID>n.ora where n = instance number on the node
- Policy managed RAC / RAC ONE NODE database – init<SID>_n.ora where n = instance number on the node
- SPfile
- Default location/name of SPfile for:
- Single instance database with file system storage: $ORACLE_HOME/dbs/ spfile<SID>.ora
- RAC / Single Instance / RAC One Node database with ASM storage: +DIskgroup/<SID>/ PARAMETERFILE/spfile.nnn.nnnnnnnnn
- Default location/name of SPfile for:
- Password file
- Default location/name of Password file for:
- Single instance database (file system / shared storage) : $ORACLE_HOME/dbs/orapw<SID>
- RAC / RAC One Node database (ASM storage): +DIskgroup/<SID>/PASSWORD/pwd<SID>.nnn.nnnnnnnnn
- Default location/name of Password file for:
Now tnsnames.ora has an entry for the database on the nodes where instances are running / configured to run.
Let us now see how various database types in the cluster can be re-registered with srvctl.
The complete syntax of srvctl add command can be seen here as:
|
1 2 3 4 5 6 7 8 9 10 |
srvctl add database -db db_unique_name [-eval] -oraclehome oracle_home [-node node_name] [-domain domain_name] [-spfile spfile] [-pwfile password_file_path] [-dbtype {RACONENODE | RAC | SINGLE} [-server "server_list"] [-instance instance_name] [-timeout timeout]] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY"] [-startoption start_options] [-stopoption stop_options] [-dbname db_name] [-acfspath "acfs_path_list"] [-policy {AUTOMATIC | MANUAL | NORESTART}] [-serverpool "server_pool_list" [-pqpool "pq_pool_list"]] [-diskgroup "disk_group_list"] [-verbose] |
While adding a database to srvctl, we need to specify:
- Oracle Home
- Spfile – If the file:
- Is not in the default location (ORACLE_HOME/dbs) and/or
- Doesn’t have the default name (spfile<SID>.ora)
- Password file (Oracle Database 12c onwards for RAC / RAC One node database)
- Database type-specific options
I have highlighted database type-specific options / actions in red.
- Re-register nasmh03 – Single instance database with file system storage on host03
- Spfile: On file system
- Password file: On file system
- Database type-specific option
- -node host03: To specify hosting server
|
1 2 3 4 |
[oracle@host02 admin]$ srvctl add database -d nasmh03 -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1/ <span style="color: red;">-node host03</span> -spfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilenasmh03.ora -pwfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwnasmh03 |
- Re-register singleh02 – Single instance ASM database on host02
- Spfile: On ASM
- Password file: On filesystem
- Database type-specific option
- -node host02: To specify hosting server
|
1 2 3 4 |
[oracle@host02 admin]$ srvctl add database -d singleh02 -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 <span style="color: red;">-node host02</span> -spfile +DATA/singleh02/PARAMETERFILE/spfile.322.928598455 -pwfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwsingleh02 |
- Re-register pmdbc3 – Policy managed RAC database on server pool pmpool
- Spfile: On ASM
- Password file: On ASM
- Database type specific options
- -serverpool “pmpool” : To specify hosting server pool
- -dbtype RAC: To specify RAC database type
|
1 2 3 4 |
[oracle@host02 ~]$ srvctl add database -d pmdbc3 -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 <span style="color: red;">-dbtype RAC</span> -spfile +DATA/PMDBC3/PARAMETERFILE/spfile.330.928678483 -pwfile +DATA/PMDBC3/PASSWORD/pwdpmdbc3.276.928677299 <span style="color: red;">-serverpool "pmpool"</span> |
- Re-register amdbh12 – Administrator-managed RAC database having instances on host01, host02
- Spfile: On ASM
- Password file: On ASM
- Database type-specific options / actions
- -dbtype RAC: To specify RAC database type
- Add instances separately using srvctl add instance
|
1 2 3 4 5 6 7 |
[oracle@host02 root]$ srvctl add database -d amdbh12 -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 <span style="color: red;">-dbtype RAC</span> -spfile +DATA/AMDBH12/PARAMETERFILE/spfile.301.928600475 -pwfile +DATA/AMDBH12/PASSWORD/pwdamdbh12.291.928599609 [oracle@host02 root]$ <span style="color: red;">srvctl add instance -i amdbh121 -n host01 -d amdbh12</span> [oracle@host02 root]$ <span style="color: red;">srvctl add instance -i amdbh122 -n host02 -d amdbh12</span> |
- Re-register r1asmh1 – Administrator managed RAC one node database with ASM storage having instance on host01 and instance prefix as r1asm
- Spfile: on ASM
- Password file: on ASM
- Database type specific option
- -dbtype RACONENODE: To specify RAC One Node database type
- -server host01: To specify hosting server
- -instance r1asm: To specify instance prefix
|
1 2 3 4 |
[oracle@host01 trace]$ srvctl add database -d r1asmh1 -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 <span style="color: red;">-dbtype RACONENODE</span> -spfile +DATA/R1ASMH1/PARAMETERFILE/spfile.285.928758295 -pwfile +DATA/R1ASMH1/PASSWORD/pwdr1asmh1.327.928757217 <span style="color: red;">-server host01 -instance r1asm</span> |
- Re-register r1pmpmpool – Policy-managed RAC One Node database with ASM storage on serverpool pmpool
- Spfile: on ASM
- Password file: on ASM
- Database type specific option
- -dbtype RACONENODE: To specify RAC One Node database type
- -serverpool pmpool: To specify hosting server pool
|
1 2 3 4 |
[oracle@host03 dbs]$ srvctl add database -d r1pmpmpool -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 <span style="color: red;">-dbtype RACONENODE</span> -spfile +DATA/R1PMPMPOOL/PARAMETERFILE/spfile.307.928845459 -pwfile +DATA/R1PMPMPOOL/PASSWORD/pwdr1pmpmpool.295.928844521 <span style="color: red;">-serverpool pmpool</span> |
Summary:
- When a database is unregistered from srvctl,
- The database is no longer listed in DBCA.
- Database configuration information stored in OCR is removed.
- The automatically-generated entry for the database in /etc/oratab is automatically deleted.
- The server pool for an administrator-managed database is automatically deleted.
- In order to drop a database using DBCA, the database has to be registered with srvctl.
- When issuing a
srvctl add database ..command, Oracle Home should be specified without a ‘/’ at the end, otherwise the database will not be listed in DBCA. - From Oracle Database 12c onwards, it is mandatory to specify the password file (if stored on shared storage) while issuing a
srvctl add database ..command to enable remote connection to the database. - For easy retrieval of configuration information related to the databases unregistered from srvctl, it is advisable that before unregistering the database you:
- Back up the OCR
- Save the configuration information retrieved using:
srvctl config …
orcrsctl stat res … -f
Load comments