Oracle Convert Rac Active-Active - To One Node Rac Active/Passive

We recently came across  situation where application was  not supporting Rac active/active . And we have convert database to Rac Active Passive .

Below are steps for Converting Rac Active/ Active to  One Node Rac Active/Passive   

a) Check Status of RAC database
[RACDB1@Abdul-rac1 ] $srvctl status database -d RACDB
Instance RACDB1 is running on node Abdul-rac1
Instance RACDB2 is running on node Abdul-rac2

b) Keep one of the instance down before converting database to one-node RAC configuration.
[RACDB1@Abdul-rac1 ] $srvctl stop instance -d RACDB -i RACDB2


c) Keep one of the instance active in Cluster before converting database to one-node RAC configuration.

[RACDB1@Abdul-rac1 ] $srvctl convert database -d RACDB -c RACONENODE -i RACDB1
PRCD-1214 : Administrator-managed RAC database RACDB has more than one instance

The above error means you should have only 1 Instance of Database running and active for converting it to One-Node RAC.

[RACDB1@Abdul-rac1 ] $srvctl remove instance -d RACDB -i RACDB2
Remove instance from the database RACDB? (y/[n]) y



d)It is mandatory to have at-least one service active for given instance before you convert it into RACONENODE mode, else you will get error.

[RACDB1@Abdul-rac1 ] $srvctl convert database -d RACDB -c RACONENODE -i RACDB1

PRCD-1242 : Unable to convert RAC database RACDB to RAC One Node database because the database had no service added

[RACDB1@Abdul-rac1 ] $srvctl add  service -d RACDB -s TEST -preferred RACDB1



e) Use srvctl convert command to change your database from RAC mode to RACONENODE.

[RACDB1@Abdul-rac1 ] $srvctl convert database -d RACDB -c RACONENODE -i RACDB

$ srvctl -h | grep convert
Usage: srvctl convert database -d <db_unique_name> -c RAC [-n <node>]
Usage: srvctl convert database -d <db_unique_name> -c RACONENODE [-i <instname>] [-w <timeout>]



f) Verify One Node RAC configuration.

[RACDB1@Abdul-rac1 ] $srvctl config database -d RACDB
Database unique name: RACDB
Database name: RACDB
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +ASMDATA/RACDB/spfileRACDB.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACDB
Database instances:
Disk Groups: DATA, FRA
Mount point paths:
Services: TEST
Type: RACOneNode    <<<<<<< The database has successfully turned into 1-Node Configuration.
Online relocation timeout: 30
Instance name prefix: RACDB1
Candidate servers: Abdul-rac1
Database is administrator managed


g) In case you want to relocate a RAC 1-Node database from 1 node to another, you can use following syntax .  Default shutdown mode for manual relocation is shutdown transactional . 

[RACDB1@Abdul-rac1 ]srvctl relocate database -d RACDB -n Abdul-rac2 

srvctl relocate database -h

srvctl relocate database -db <db_unique_name> {[-node <target>] [-timeout <timeout>] [-stopoption <stop_option>] | -abort [-revert]} [-drain_timeout <timeout>] [-verbose]

-db <db_unique_name> Unique name of database to relocate

-node <target> Target node to which to relocate database

-timeout <timeout> Online relocation timeout in minutes (only for RAC One Node database)

-abort Abort failed online relocation (only for RAC One Node database)

-revert Remove target node of failed online relocation request from the candidate server list of administrator-managed RAC One Node database

-stopoption <stop_option> Override default shutdown option for running instance (only NORMAL allowed)

-drain_timeout <drain_timeout> Service drain timeout specified in seconds

-verbose Verbose output

-help Print usage

We will get below Undo  error which needs to be fixed by setting Undo tablespace to specific node

$ srvctl relocate database –d rontest -n node3                       <
PRCD-1222 : Online relocation of database rontest failed but database was restored to its original state
PRCD-1129 : Failed to start instance rontest_2 for database rontest
PRCR-1064 : Failed to start resource ora.rontest.db on node node3
CRS-5017: The resource action "ora.rontest.db start" encountered the following error:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
Process ID: 1587
Session ID: 35 Serial number: 1
CRS-2674: Start of 'ora.rontest.db' on 'node3' failed



SQL> select tablespace_name from dba_data_files where tablespace_name like ‘%UNDO%’;
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
So the tablespace was there, but the initialisation parameter was wrong! Let’s correct this:
SQL> alter system set undo_tablespace='UNDOTBS1' sid='rontest_1';
System altered.
SQL> alter system set undo_tablespace='UNDOTBS2' sid='rontest_2';

If we want to remove and re-add database in cluster  :

1) Stop database --> srvctl stop database -d  dbname 

2) remove database from  cluster -->   srvctl remove database -d dbname 

3)  re-add  database  to cluster using below 

srvctl add database -dbname  database_name -dbtype RACONENODE -oraclehome /u01/19c -instance database_name -spfile asm_spfile_path -pwfile password-file_path  -server server1,server2 -db database_name 

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 | 手动| NORESTART}]

     [-serverpool“ server_pool_list ”[-pqpool“ pq_pool_list ”]]

     [-diskgroup “disk_group_list” ] [-verbose]

If we  need modify Instance name : 

If we  want to  change  instance name  , best way is to remove re-add database .   We  can also  use below instance name will be hard coded to each node  . Ideally if instance  name given at time of conversion is  OBCDATA . then instance name on first node will be  OBCDATA_1. if there is server crash ,  same instance name is  moved to another node  i,e  instance will come up with same name OBCDATA_1  on node 2  .  If we do manual  relocation  instance will always come  up as OBCDATA_2 because during manual relocation we cannot  have 2 instance  with same name . 

using below  option will force instance to always have different name , even in case of  server failover ./

srvctl modify instance -d dbname -i  instance_name  -n name 

References :

https://docs.oracle.com/en/database/oracle/oracle-database/19/rilin/installing-oracle-rac-rac-one-node-software-only.html#GUID-E865878B-C328-4368-A8F5-C7B2CD81172B

Instance Name changes during RAC One Node Failover/Switchover ( Doc ID 1540629.1 )

Naming Of RAC ONE NODE Instances Across RAC Nodes ( Doc ID 2063599.1 )