Swapping role of databases between the primary and replica node

By using Role reversal, the user is able to swap the role of a replica and primary for one or more databases.

NPS supported base version for Role Reversal is 11.2.1.11.

Restriction: Role Reversal is not supporting RST (Row secure table) for NPS version lower than 11.2.1.12.

Role reversal allows to:

Do role reversal of nodes of databases:
- at any time.
- without the need for taking a full backup at the new primary and restore to a new replica.
- without the need for re-configuring the databases for replication.
Migrate their read/write applications to the systems that were in replica role previously, still maintaining the DR capability.
Periodically test the readiness of the replica systems for their DR readiness.

Before you begin

Before the user can initiate the role reversal, make sure that the following conditions are met between the primary replication node and the replica replication nodes of the databases.

All the member nodes of given databases should be in active state and running in a healthy condition.
The current apply sequence at the replica node of the databases should be the same as the current capture sequence at its primary node.
Note: If the apply seq is not same, then do the following:
1. Stop generating new backups for that database at source system by stopping replication for given DB. For more information, see Starting and stopping replication for a given database.
2. Temporarily extend the replication interval for this database significantly to reduce the frequency of new backups and allow the target system adequate time to process the backlog. For more information, see nzdr update db in The nzdr command.
3. Wait for replica to catch up with the primary.
User needs to abort active transactions against the databases on the current primary node to maintain data integrity between the replication nodes. After role reversal, any read/write applications on the old primary needs to be moved to run against the new primary node.
Before role reversal, make sure that the security levels are matching on both the systems. All security descriptors on the source system must be there on target system. You can take a backup globals by using nzbackup -globals -dir command and restore it on the replica systems by using nzrestore -globals -dir command to ensure globals such as security information, user definitions are synced between primary and replica systems.
Connect to primary Netezza Performance Server system as admin user and run the following query:
```
update _t_backup_history set BATCHID=TO_CHAR(TIMESTAMP(BACKUPSET), 'YYYYMMDDHH24MISS' :: VARCHAR(16)) where BATCHID='';
```
Note: Ensure to run this query for every role reversal.
When performing role reversal for the first time on a given database on any of the replication node, run the following queries:
- On the primary system, run select query on the _t_restore_history table and check if it returns 0.
```
$ nzsql <dbname> -c "select count(*) from _t_restore_history where TO_CHAR(TIMESTAMP(BACKUPSET), 'YYYYMMDDHH24MISS' :: VARCHAR(16)) = <backupset> and type=6 and status =1"
```
- On the replica system, run select query on the _t_backup_history table and check if it returns 0.
```
$ nzsql <dbname> -c "select count(*) from _t_backup_history where TO_CHAR(TIMESTAMP(BACKUPSET), 'YYYYMMDDHH24MISS' :: VARCHAR(16)) = <backupset> and type=6 and status =1"
```
- On the primary system, run _t_object table and check if it returns non-zero.
```
$nzsql <dbname> -c "select count(*) from _t_object where objdb=current_db and RESTOREDOID != 0;"
```
  If all the conditions are met, contact IBM support before you proceed with the role reversal.
Important:
- These queries are applicable only for NRS versions less than 3.0.4.0.
- Once role reversal is performed, ensure not to use the backupsetid for any manual incremental backup as it can cause data corruption.
- You can see NRSREPL OpType in nzbackup and nzrestore history for respective database once role reversal is performed.
Note: To get BACKUPSET details, run nzdr list db. For more information, see Listing replication databases

Procedure

Run the following steps for each database whose nodes need role reversal.

Note: If all replicated databases share the same replicate node, you can simplify the role reversal process by using the keyword ALL with the --db-name option instead of specifying the list of names in the nzdr change role command.

Fetch the status of the databases from all its member nodes and make sure that it met the prerequisite conditions for the role reversal.

nzdr status node
nzdr status db --db-name <DB1>,<DB2> --details

Example:

[root@rajnrs11 ~]# nzdr status node 
+-------------+-------------+-------------+----------+
|   serial-no | node-name   | reachable   | health   |
+=============+=============+=============+==========+
|           1 | CPDS-A      | Yes         | Healthy  |
+-------------+-------------+-------------+----------+
|           2 | CPDS-B      | Yes         | Healthy  |
+-------------+-------------+-------------+----------+ 


[root@rajnrs11 ~]# nzdr status db --db-name UDB50,UDB49 --details
Databases -
1. UDB50
   Primary:
      Node: CPDS-B
      Last capture timestamp: Tue 03 Jun 2025 03:40 AM PDT
      Last capture seq no: 11
      Last pruned capture seq no: 10
      Last reported capture error: None
      Replication status: Active
   Replicas:
      Node: CPDS-A
      Last apply timestamp: Tue 03 Jun 2025 04:25 AM PDT
      Last apply seq no: 11
      Last pruned apply seq no: 11
      Last reported apply error: None
      Replication status: Active

2. UDB49
   Primary:
      Node: CPDS-B
      Last capture timestamp: Tue 03 Jun 2025 03:40 AM PDT
      Last capture seq no: 11
      Last pruned capture seq no: 10
      Last reported capture error: None
      Replication status: Active
   Replicas:
      Node: CPDS-A
      Last apply timestamp: Tue 03 Jun 2025 04:25 AM PDT
      Last apply seq no: 11
      Last pruned apply seq no: 11
      Last reported apply error: None
      Replication status: Active


Note: If node replication status is suspended then node replication status takes precedence over database replication status.

Initiate the role reversal command by using the required replica node name of the databases.

nzdr change role --db-name <DB1>,<DB2> --node-name <REPLICA NODE NAME> --role-type primary  --begin

If all the conditions met, then this command starts an on-demand capture job on the current primary.

Example:

[root@rajnrs11 ~]# nzdr change role --node-name CPDS-A --db-name UDB49,UDB50 --role-type  primary --begin 
Successfully started begin role reversal job with id 19 for the following databases: UDB49, UDB50.
You can monitor the status of this job via 
'nzdr job --node-name CPDS-B --job-id 19'
Monitor the status of these databases via command below.
'nzdr status db --details' command.
Once the Replication Status of these databases changes to 'Role Reversal (Pending)' on the Primary and Replica nodes,
you can then proceed with 'nzdr change role' command using the '--end' option to complete the change role process

Monitor the progress of the begin phase of the role reversal by checking the job status at the primary as well as via the status of the databases.

nzdr job --node-name <PRIMARY NODE> --job-id <JOB ID>
nzdr status db --db-name <DB1>,<DB2> --details

Wait for the job status to be in Completed state and replication status of the database to change from Active to Role Reversal (Pending) on all the nodes.

Example:

[root@rajnrs11 ~]# nzdr job --node-name CPDS-B --job-id 19
Job Id   : 19
Job Type : ROLE_BEGIN_JOB 

+-----------+------------------+--------------+-------------+
| db-name   |   capture-seq-no | job-status   | job-error   |
+===========+==================+==============+=============+
| UDB49     |               -1 | Running      |             |
+-----------+------------------+--------------+-------------+
| UDB50     |               -1 | Running      |             |
+-----------+------------------+--------------+-------------+ 

[root@rajnrs11 ~]# nzdr job --node-name CPDS-B --job-id 19
Job Id   : 19
Job Type : ROLE_BEGIN_JOB 

+-----------+------------------+--------------+-------------+
| db-name   |   capture-seq-no | job-status   | job-error   |
+===========+==================+==============+=============+
| UDB49     |               12 | Completed    |             |
+-----------+------------------+--------------+-------------+
| UDB50     |               12 | Completed    |             |
+-----------+------------------+--------------+-------------+

[root@rajnrs11 ~]# nzdr status db --db-name UDB50,UDB49 --details
Databases -
1. UDB50
   Primary:
      Node: CPDS-B
      Last capture timestamp: Tue 03 Jun 2025 05:43 AM PDT
      Last capture seq no: 12
      Last pruned capture seq no: 10
      Last reported capture error: None
      Replication status: Role Reversal (Pending)
   Replicas:
      Node: CPDS-A
      Last apply timestamp: Tue 03 Jun 2025 05:57 AM PDT
      Last apply seq no: 12
      Last pruned apply seq no: 11
      Last reported apply error: None
      Replication status: Role Reversal (Pending)

2. UDB49
   Primary:
      Node: CPDS-B
      Last capture timestamp: Tue 03 Jun 2025 05:43 AM PDT
      Last capture seq no: 12
      Last pruned capture seq no: 10
      Last reported capture error: None
      Replication status: Role Reversal (Pending)
   Replicas:
      Node: CPDS-A
      Last apply timestamp: Tue 03 Jun 2025 05:57 AM PDT
      Last apply seq no: 12
      Last pruned apply seq no: 11
      Last reported apply error: None
      Replication status: Role Reversal (Pending)


Note: If node replication status is suspended then node replication status takes precedence over database replication status.

Complete the role reversal process on the required databases. If the replication status of the databases at all the nodes changes to Role Reversal (Pending) then issue the following command to complete the role reversal process.

nzdr change role --db-name <DB1>,<DB2> --node-name <REPLICA NODE NAME> --role-type primary  --end

Example:


[root@rajnrs11 ~]# nzdr change role --node-name CPDS-A --db-name UDB49,UDB50 --role-type primary --end

Successfully started end role reversal job with id 50 for the following databases: 
UDB49, UDB50.

You can monitor the status of this job via the command below:
'nzdr job --node-name CPDS-A --job-id 50'

Monitor the progress of the end phase of the role reversal by checking the job status at the primary or status of the databases.

nzdr job --node-name <PRIMARY NODE> --job-id <JOB ID>
nzdr status db --db-name <DB1>,<DB2> --details

Make sure that the

Job status in Completed state.
Primary and replica section reflects the role change.
Make sure that the replica status of database is changed to Active.

Example:


[root@rajnrs11 ~]# nzdr job --node-name CPDS-A --job-id 50
Job Id   : 50
Job Type : ROLE_END_JOB 

+---------+------------------+--------------+-----------+
| db-name | capture-seq-no   | job-status   | job-error|
+=========+==================+==============+===========+
| UDB49   | -1               | Completed    |           |
+---------+------------------+--------------+-----------+
| UDB50   | -1               | Completed    |           |
+---------+------------------+--------------+-----------+

[root@rajnrs11 ~]# nzdr status db --db-name UDB50,UDB49 --details
Databases -
1. UDB50
   Primary:
      Node: CPDS-A
      Last capture timestamp: Tue 03 Jun 2025 05:57 AM PDT
      Last capture seq no: 12
      Last pruned capture seq no: -1
      Last reported capture error: None
      Replication status: Active
   Replicas:
      Node: CPDS-B
      Last apply timestamp: Tue 03 Jun 2025 05:43 AM PDT
      Last apply seq no: 12
      Last pruned apply seq no: -1
      Last reported apply error: None
      Replication status: Active

2. UDB49
   Primary:
      Node: CPDS-A
      Last capture timestamp: Tue 03 Jun 2025 05:57 AM PDT
      Last capture seq no: 12
      Last pruned capture seq no: -1
      Last reported capture error: None
      Replication status: Active
   Replicas:
      Node: CPDS-B
      Last apply timestamp: Tue 03 Jun 2025 05:43 AM PDT
      Last apply seq no: 12
      Last pruned apply seq no: -1
      Last reported apply error: None
      Replication status: Active
Note: If node replication status is suspended then node replication status takes precedence over database replication status.