Wednesday, 3 June 2020

RAC - Online Relocation |Cold Failover of One node RAC database

Scenario Preview:

We have One node RAC 12c R1 grid setup, We will do cold failover from instance1 (RAC1) to instance 2 (RAC2). With waiting time of relocastion is -w 5minute (it will wait for 5min for transaction and then disconnect it and connection will failover to another instance).
  • Check the status of one node RAC database (online relocation is inactive)
oracle@srv1:/root> srvctl status database -d rac
Instance RAC1 is running on node srv1
Online relocation: INACTIVE
oracle@srv1:/root> su root
Password:
srv1:~ # . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/grid
srv1:~ # srvctl status database -d rac
Instance RAC1 is running on node srv1
Online relocation: INACTIVE
srv1:~ #
  • RAC database configuration status 
srv1:~ # srvctl config database -d rac
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/PARAMETERFILE/spfile.274.1038413121
Password file: +DATA/RAC/PASSWORD/pwdrac.262.1038412435
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services: staf
Type: RACOneNode
Online relocation timeout: 5
Instance name prefix: RAC2
Candidate servers: srv1
OSDBA group: dba
OSOPER group: oper
Database instances:
Database is administrator managed
srv1:~ #
  • Status of grid services
srv1:~ # crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.FRA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.asm
               ONLINE  ONLINE       srv1                     Started,STABLE
               ONLINE  ONLINE       srv2                     Started,STABLE
ora.net1.network
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.ons
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       srv1                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       srv1                     169.254.52.81 192.16
                                                             8.10.1,STABLE
ora.cvu
      1        ONLINE  ONLINE       srv2                     STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       srv1                     Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       srv2                     STABLE
ora.rac.db
      1        ONLINE  ONLINE       srv1                     Open,STABLE
ora.rac.staf.svc
      1        ONLINE  ONLINE       srv1                     STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.srv1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.srv2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
srv1:~ #

  • Here we can see on srv2 server only ASM2 is service is running not instance we will relocate to node 2 

srv2:~ # ps -ef |grep pmon
grid      3150     1  0 13:20 ?        00:00:00 asm_pmon_+ASM2
root      7638  2954  0 20:03 pts/0    00:00:00 grep --color=auto pmon
You have new mail in /var/mail/root
srv2:~ # ps -ef |grep lsnr
grid      4000     1  0 13:21 ?        00:00:00 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
grid      4028     1  0 13:21 ?        00:00:00 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
grid      4042     1  0 13:21 ?        00:00:00 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
root      7803  2954  0 20:04 pts/0    00:00:00 grep --color=auto lsnr
srv2:~ #

  • In srv1 server database instance is running on one node RAC
srv1:~ # ps -ef |grep lsnr
grid      5243     1  0 13:22 ?        00:00:00 /u01/app/12.1.0/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
grid     15142     1  0 13:24 ?        00:00:00 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid     15258     1  0 13:25 ?        00:00:00 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit
root     24750 23899  0 20:04 pts/0    00:00:00 grep --color=auto lsnr
srv1:~ # ps -ef |grep pmon
grid      3113     1  0 13:20 ?        00:00:01 asm_pmon_+ASM1
grid      9429     1  0 13:23 ?        00:00:00 mdb_pmon_-MGMTDB
oracle   17409     1  0 13:26 ?        00:00:01 ora_pmon_RAC1
root     24816 23899  0 20:04 pts/0    00:00:00 grep --color=auto pmon
srv1:~ #


  • Relocate Instance1 to instance 2 with waiting time 5 minute, We can also increase waiting time for further transaction time.

oracle@srv1:~> srvctl relocate database -d RAC -n srv2 -w 5 -v
Added target node srv2
Configuration updated to two instances
Instance RAC2_2 started
Services relocated
Waiting for up to 5 minutes for instance RAC1 to stop ...
Instance RAC1 stopped
Configuration updated to one instance
oracle@srv1:~>

  • Check the status after relocation
oracle@srv1:~> srvctl status database -d RAC
Instance RAC2_2 is running on node srv2
Online relocation: INACTIVE
oracle@srv1:~> srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/PARAMETERFILE/spfile.274.1038413121
Password file: +DATA/RAC/PASSWORD/pwdrac.262.1038412435
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services: staf
Type: RACOneNode
Online relocation timeout: 5
Instance name prefix: RAC2
Candidate servers: srv1,srv2
OSDBA group: dba
OSOPER group: oper
Database instances:
Database is administrator managed
oracle@srv1:~>

  • Alert log details of srv1 instance 1
Archived Log entry 105 added for thread 2 sequence 53 ID 0x9c8b7f56 dest 1:
Tue Jun 02 20:12:12 2020
Reconfiguration started (old inc 6, new inc 8)
List of instances (total 2) :
 1 2
New instances (total 1) :
 2
My inst 1
 Global Resource Directory frozen
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Tue Jun 02 20:12:13 2020
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Tue Jun 02 20:12:13 2020
Reconfiguration complete (total time 0.6 secs)
Tue Jun 02 20:12:47 2020
Shutting down instance (transactional local)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process MMNL
Tue Jun 02 20:12:48 2020
Stopping background process CJQ0
Stopping background process MMON
Local transactions complete. Performing immediate shutdown
License high water mark = 8
Tue Jun 02 20:12:51 2020
All dispatchers and shared servers shutdown
Tue Jun 02 20:12:53 2020
ALTER SYSTEM SET _shutdown_completion_timeout_mins=5 SCOPE=MEMORY;
ALTER DATABASE CLOSE NORMAL /* db agent *//* {1:55949:2272} */
Tue Jun 02 20:12:54 2020
SMON: disabling tx recovery
Tue Jun 02 20:12:54 2020
Stopping Emon pool
Stopping background process AQPC
Stopping background process RCBG
Tue Jun 02 20:13:05 2020
Stopping Emon pool
Tue Jun 02 20:13:05 2020
SMON: disabling cache recovery
Tue Jun 02 20:13:05 2020
Redo thread 1 internally disabled at seq 59 (LGWR)
Shutting down archive processes
Archiving is disabled
Tue Jun 02 20:13:05 2020
ARCH shutting down
ARC3: Archival stopped
Tue Jun 02 20:13:05 2020
ARCH shutting down
ARC2: Archival stopped
Tue Jun 02 20:13:05 2020
ARCH shutting down
ARC1: Archival stopped
Tue Jun 02 20:13:05 2020
ARC0: Archiving disabled thread 1 sequence 59
Tue Jun 02 20:13:06 2020
Archived Log entry 106 added for thread 1 sequence 59 ID 0x9c8b7f56 dest 1:
ARCH shutting down
ARC0: Archival stopped
Tue Jun 02 20:13:06 2020
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 25
Thread 1 closed at log sequence 59
Successful close of redo thread 1
Tue Jun 02 20:13:06 2020
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 13
Completed: ALTER DATABASE CLOSE NORMAL /* db agent *//* {1:55949:2272} */
ALTER DATABASE DISMOUNT /* db agent *//* {1:55949:2272} */
Shutting down archive processes
Archiving is disabled
Tue Jun 02 20:13:06 2020
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 2
Completed: ALTER DATABASE DISMOUNT /* db agent *//* {1:55949:2272} */
Tue Jun 02 20:13:07 2020
NOTE: Deferred communication with ASM instance
Tue Jun 02 20:13:08 2020
Instance stopped by oraagent
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Tue Jun 02 20:13:08 2020
NOTE: force a map free for map id 2
Tue Jun 02 20:13:09 2020
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Tue Jun 02 20:13:09 2020
Stopping background process VKTM
Archiving is disabled
Tue Jun 02 20:13:09 2020
NOTE: Shutting down MARK background process
Tue Jun 02 20:13:12 2020
NOTE: ASMB releasing group resources due to shutdown
NOTE: ASMB clearing idle groups before exit
Stopping background process RBAL
Tue Jun 02 20:13:14 2020
freeing rdom 0
Tue Jun 02 20:13:16 2020
Instance shutdown complete
  • Check the status on srv2 server 

srv2:~ # ps -ef |grep pmon
grid      3150     1  0 13:20 ?        00:00:01 asm_pmon_+ASM2
oracle   10086     1  0 20:12 ?        00:00:00 ora_pmon_RAC2_2
root     11524 10664  0 20:16 pts/1    00:00:00 grep --color=auto pmon
srv2:~ # . oraenv
ORACLE_SID = [root] ? +ASM2
The Oracle base has been set to /u01/app/grid
srv2:~ # crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.FRA.dg
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.asm
               ONLINE  ONLINE       srv1                     Started,STABLE
               ONLINE  ONLINE       srv2                     Started,STABLE
ora.net1.network
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
ora.ons
               ONLINE  ONLINE       srv1                     STABLE
               ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       srv1                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       srv2                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       srv1                     169.254.52.81 192.16
                                                             8.10.1,STABLE
ora.cvu
      1        ONLINE  ONLINE       srv2                     STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       srv1                     Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       srv2                     STABLE
ora.rac.db
      2        ONLINE  ONLINE       srv2                     Open,STABLE
ora.rac.staf.svc
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       srv2                     STABLE
ora.srv1.vip
      1        ONLINE  ONLINE       srv1                     STABLE
ora.srv2.vip
      1        ONLINE  ONLINE       srv2                     STABLE
--------------------------------------------------------------------------------
srv2:~ #

  • Check connecting to instance 2
oracle@srv2:/root> . oraenv
ORACLE_SID = [RAC2] ? RAC
The Oracle base has been changed from /u01/app/grid to /u01/app/oracle
oracle@srv2:/root> sqlplus sys/system123@rac as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 2 20:17:46 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
RAC2_2           OPEN

SQL>

oracle@srv1:~> srvctl status database -d RAC
Instance RAC2_2 is running on node srv2
Online relocation: INACTIVE
oracle@srv1:~>

Posted by at
Labels:

1 comment:

  1. KBC Lottery 202022 June 2020 at 13:47

    After looking at a number of the articles on your blog, I truly appreciate your technique of writing a blog. I saved as a favorite it to my bookmark site list and will be checking back soon. Take a look at my website as well and tell me what you think about KBC Lottery Number Check Online procedure?

    ReplyDelete

Subscribe to: Post Comments (Atom)