Oracle RAC 如何增加節點 Step by Step How to add a node in Oracle RAC

主要步驟不難，但是處理 database 的細結很多，在這裡記錄一下步驟，

	node 1	node 2
Hostname	test-odb1	test-odb2
SID	odb1	odb2
Unique Name	testdb	tested

Add Cluster

1. Config Key-Based Authentication by grid

[grid@test-odb1 ~]$ $ORACLE_HOME/oui/prov/resources/scripts/sshUserSetup.sh -user grid -hosts "test-odb1 test-odb2" -advanced -noPromptPassphrase

2. Check $ORACLE_HOME folder & make folder on node 2

[grid@test-odb1 ~]$ echo $ORACLE_HOME
/u01/app/19.0.0/grid

[grid@test-odb2 ~]$ mkdir /u01/app/19.0.0/grid

3. Check compatible (option)

[grid@test-odb1 ~]$ cd $ORACLE_HOME/bin
[grid@test-odb1 ~]$ cluvfy comp peer -refnode test-odb1 -n node_list test-odb2 -verbose

4. Add Cluster Node

[grid@test-odb1 ~]$ $ORACLE_HOME/addnode/addnode.sh -silent -ignoreSysPrereqs -ignorePrereqFailure "CLUSTER_NEW_NODES={test-odb2}" "CLUSTER_NEW_PRIVATE_NODE_NAMES={test-odb2-priv}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={test-odb2-vip}"

5. Follow execute shell on node 2 by root when step 4 add cluster node is finished

As a root user, execute the following script(s):
1. /u01/app/oraInventory/orainstRoot.sh
2. /u01/app/19.0.0/grid/root.sh

6. Check cluster

[grid@test-odb1 ~]$ cluvfy stage -post nodeadd -n test-odb2

 

Add Oracle Software

1. Config Key-Based Authentication by oracle

[oracle@test-odb1 ~]$ $ORACLE_HOME/oui/prov/resources/scripts/sshUserSetup.sh -user oracle -hosts "test-odb1 test-odb2" -advanced -noPromptPassphrase

2. Check (option)

[oracle@test-odb1 ~]$ cluvfy stage -post nodeadd -n test-odb2

3. Copy Software to Node 2 & add node 2

[oracle@test-odb1 ~]$ $ORACLE_HOME/addnode/addnode.sh -silent -ignoreSysPrereqs -ignorePrereqFailure "CLUSTER_NEW_NODES={test-odb2}"

4. Follow execute shell on node 2 by root when step 3 add node is finished

As a root user, execute the following script(s):
1. /u01/app/oracle/product/19.0.0/dbhome_1/root.sh

 

Add Instance

1. Check Node 1 database status must be running on read/write or read only

[oracle@test-odb1 ~]$ sqlplus / as sysdba
SQL> select status, instance_name, database_role, open_mode from v$database, v$Instance;

2. Check spfile & password file on share storage 
(Look Oracle move spfile and passwordfile from filesystem to asm https://dotblogs.com.tw/SlowToLife_FastToIT/2021/04/22/152834)

[oracle@test-odb1 ~]$ srvctl config database -d testdb
...
Spfile: +DATADG/TESTDB/PARAMETERFILE/spfiletestdb.ora
Password file: +DATADG/TESTDB/PASSWORD/pwdtestdb
...

3. Add Instance to Node 2  (the sysdbapassword para can remove, if password is difficult or not be show)

[oracle@test-odb1 ~]$ dbca -ignorePreReqs -ignorePrereqFailure -silent -addInstance -nodeName test-odb2 -gdbName testdb -instanceName odb2 -sysDBAUserName sys -sysDBAPassword password

[oracle@test-odb1 ~]$ dbca -ignorePreReqs -ignorePrereqFailure -silent -addInstance -nodeName test-odb2 -gdbName testdb -instanceName odb2 -sysDBAUserName sys

4. Try Start Instance on Node 2

[oracle@test-odb1 ~]$ srvctl start instance -d testdb -n test-odb2
PRCC-1015 : testdb was already running on test-odb2
PRCR-1004 : Resource ora.testdb.db is already running

5. Check local_listener on Node 2 must be running at Node 2

[oracle@test-odb2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 18-MAY-2021 16:14:29
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                11-MAY-2021 14:34:56
Uptime                    0 days 1 hr. 39 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/test-odb2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.12)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_CRSDG" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DATADG" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_FRADG" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@test-odb2 ~]$ sqlplus / as sysdba
SQL> show parameter local_listener;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=                                                 10.10.10.10)(PORT=1521))
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.11)(PORT=1521))' sid='testdb2';

6. Config tnsnames.ora

TESTDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = test-odb1.nextbank.net)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = test-odb2.nextbank.net)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = testdb)
  )
)

7. Restart Instance both node

[oracle@test-odb1 ~]$ srvctl stop database -d testdb
[oracle@test-odb1 ~]$ srvctl start database -d testdb
[oracle@test-odb1 ~]$ srvctl status database -d testdb

 

Add Node On Standby Dataguard Environment

1. Check dgbroker config path & modify config file path to share storage

[oracle@test-odb1 ~]$ sqlplus / as sysdba
SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1testdbstby.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2testdbstby.dat
dg_broker_start                      boolean     TRUE

SQL> alter system set dg_broker_config_file1= '+DATADG/TESTDB/PARAMETERFILE/dr1testdbstby.dat' scope=spfile sid='*';

System altered.

SQL> alter system set dg_broker_config_file2= '+DATADG/TESTDB/PARAMETERFILE/dr2testdbstby.dat' scope=spfile sid='*';

2. Stop Database & move dgbroker config file to share storage

[oracle@test-odb1 ~]$ srvctl stop database -d testdbstby
[oracle@test-odb1 ~]$ su - gird
[grid@test-odb1 ~]$ asmcmd
 
ASMCMD> cp /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr1testdbstby.dat +DATADG/TESTDB/PARAMETERFILE/dr1testdbstby.dat
ASMCMD> cp /u01/app/oracle/product/19.0.0/dbhome_1/dbs/dr2testdbstby.dat +DATADG/TESTDB/PARAMETERFILE/dr2testdbstby.dat

3. Config listener.ora by grid & reload config

[grid@test-odb2 ~]$ cd $ORACLE_HOME/network/admin
[grid@test-odb2 admin]$ cat listener.ora
...
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = testdbstby_DGMGRL)
    (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
    (SID_NAME = testdb2)
  )
)

[grid@test-odb2 ~]$ lsnrctl reload

4. Check Database Status & Restart Database

[oracle@test-odb2 ~]$ srvctl status database -d testdbstby
Instance testdb1 is not running on node test-odb11
Instance testdb2 is not running on node test-odb12
[oracle@test-odb2 ~]$ srvctl start database -d testdbstby

5. Check New Node UNDOTBS Tablespace

[oracle@test-odb2 ~]$ sqlplus / as sysdba
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS3

--if undotbs2 exists to modify else need to create undo tablespace
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

6. Check DG Broker Status

[oracle@test-odb2 admin]$ dgmgrl /
DGMGRL> show configuration

Configuration - dgtestdb

  Protection Mode: MaxPerformance
  Members:
  testdb     - Primary database
    testdbstby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

DGMGRL> show database testdbstby

Database - testdbstby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 103.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    testdb1 (apply instance)
    testdb2

Database Status:
SUCCESS