How to change instance_number of RAC

背景：

        After deleting a bad node and then adding a new one ,the instnace number becomes 1 and 3,but not 1 and 2. 部署goldengate时，会去找第二个节点的归档。所以，为了方便部署goldengate，想把instance number和thread号给改成2.

 

=== ODM Question ===
How to change instance_number of RAC?

=== ODM Answer ===
Dear customer, to change the instance number, pls apply the following steps
1. change the instance number
SQL> alter system set instance_number=4 scope=spfile sid='ora10g2';

2. restart the instance whose instance number was adjusted, in my case I restarted the instance 'ora10g2'
SQL>shutdown immedate
SQL>startup

3.check the adjustment
SQL>select inst_id, instance_number, instance_name from gv$instance;

 

=== ODM Question ===
The customer want to change the thread number from 3 to 2 for instance 2 and change the value of parameter cluster_database_instances from 2 to 3, she wants the steps.

=== ODM Answer ===
Generic Note
------------------------
UPDATE
---------------
1. The parameter cluster_database_instances specify the instance number configured for cluster database. This parameter should set on all instances and have the same value.
To change the value of this parameter, the following steps need to be applied
1.1 SQL>alter system set cluster_database_instances=3 scope=spfile sid='*';
1.2 Restart all instances of the database.


2. To change the redo thread number of an instance, pls apply the following steps
2.1 Check the currently available redo thread number
SQL> select distinct THREAD# from gv$log;

2.2 add a new redo thread
SQL>alter database add logfile thread 3;

2.3 add redo log groups for the new added thread
SQL>ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 6 ('+DATA','+RECO') size 50M;

SQL>ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5 ('+DATA','+RECO') size 50M;

Comment: There should be 2 redo log groups at minimum for a thread and 1 available member for each group. In my case, asm was being used, so only the diskgroup name was needed here. If you are using
raw device, the raw device name is required during create the redo log group. For cluster file system, the file name was required.

2.4 enable the redo thread
SQL>alter database enable public thread 3;

2.5 adjust the thread parameter and reboot the instance
SQL>alter system set thread=3 scope=spfile sid='rac10g1';
SQL>shutdown immediate
SQL>startup

2.6 Check the result
SQL>show parameter thread
SQL> alter system switch logfile;
SQL> select * from gv$log;

Comment: pls adjust the commands in the update, to suit your system.

=== ODM Question ===

1.Whether change the instance_nmber,the instance thread number and cluster_database_instances have any impact to golden gate.
2.What to do on godengate befor or after changing the parameters to avoid any problem?

=== ODM Answer ===

As per the previous update, you would want to change the db2.thread from 3 to 2 and you also wanted to change the cluster_database_instance from 2 to 3.

From the above, what i see is that this is a 3 node RAC setup with Goldengate configured to replicat on a standalone database.

Since, you are changing the db2 thread from 3 to 2, you would defenetly be adding the log groups to thread 2 and then disabling the thread 3.
For this you would have to delete and add the extract again.

Here is the KM note iam putting this in the SR for your reference.
How to configure GoldenGate extract when adding or removing redo log threads in an Oracle RAC. (Doc ID 1267901.1)

After which you may also want to exclude the the thread 3 of db2 instance in the extract parameter file. Since, In a RAC environment, the Extract process uses its own thread number or index for each of the redo threads in the RAC cluster. These Extract threads are mapped to Oracle's redo threads. On startup, Extract will detect distinct thread numbers from v$log and the values in the checkpoint file will be based on the thread number obtained from v$log. When creating an Extract with the THREADOPTIONS parameter, the checkpoint file is derived based on values given with that parameter, which may not match the order in which values are retrieved from v$log. This is because Extract does not have any database connections until it is started. Even after its initialization, Extract never tries to match the order in which the threads are listed in v$log.

To determine which GoldenGate thread number corresponds to the Oracle redo threads, use the output from v$log.
Here is an example using an Extract that is created for a 3-node RAC. The Extract checkpoint file will look like the following.

GGSCI (hothpc21) 1> info extract egext, showch

Log Read Checkpoint Oracle Redo Logs
2009-06-19 06:30:34 Thread 1, Seqno 0, RBA 0
Log Read Checkpoint Oracle Redo Logs
2009-06-19 06:30:34 Thread 2, Seqno 0, RBA 0
Log Read Checkpoint Oracle Redo Logs
2009-06-19 06:30:34 Thread 3, Seqno 0, RBA 0

Current Checkpoint Detail:

Read Checkpoint #1

Oracle RAC Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 1
--
--
Read Checkpoint #2

Oracle RAC Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 2
--
--
Read Checkpoint #3

Oracle RAC Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 3

And the output from v$log is as follows

SQL> select distinct thread# from v$log;

THREAD#
----------
2
3
1

Until the Extract initialization process completes successfully, the thread number in the GoldenGate checkpoint file may not be updated with the values fetched from v$log.
In our example, the match between the GoldenGate thread number and the Oracle thread number will be as follows

Oracle threads GoldenGate Thread
2 Read Checkpoint #1
3 Read Checkpoint #2
1 Read Checkpoint #3

In this example, if you want to exclude Oracle Thread 1, then the THREADOPTIONS parameter can be defined as follows:

GGSCI> THREADOPTIONS PROCESSTHREADS EXCEPT 3

Please let us know if you have issues/concerns.

Regards,
Vivek