How to remove an oracle database instance from a RAC cluster

1. Pre-steps before deinstall:

I have a standby database server and I have dbfs instance as well so some steps are to
take care of that standby database as well.

Login as oracle

a- check some status  and save outputs.

oracle@testa.one.com:$ srvctl status database -d DBFS031

Instance DBFS0311 is running on node testa

Instance DBFS0312 is running on node testb

Instance DBFS0313 is running on node testc

b-oracle@testa.one.com:$ srvctl config database -d DBFS031

Database unique name: DBFS031

Database name:

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile: +DATA/DBFS031/spfileDBFS031.ora

Password file:

Domain: one.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: dba

Database instances: DBFS0311,DBFS0312,DBFS0313

Configured nodes: testa,testb,testc

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

c- save cluster health.

cd /u01/app/12.2.0.1/grid/bin/

./crsctl stat res -t >  /tmp/clusterhealth

d- save a copy of spfile before the delete.

SQL> create pfile='/gg_nas1/oracle/standby/dbfs030/pfile-dbfs030-before-delete-DBFS0303-dgpldb30-instance.ora' from spfile;

File created.

e- check redo and standby log threads.

Apparently this standby built was not completed so i am fine to proceed worrying about standby
but i am saving the steps still for a standby day.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

no rows selected

SQL> select group#,thread#,bytes/1048576 as MB from v$log;

   GROUP#    THREAD#      MB

---------- ---------- ----------

        1  1 200

        2  1 200

        3  2 200

        4  2 200

        5  3 200

        6  3 200

6 rows selected.

f-  Perform a few switch logs:

alter system archive log current;

alter system archive log current;

alter system archive log current;

g-  Now check the lag and sequence from primary and standby:

Primary:

select thread#, max(sequence#),status

from v$archived_log where status = 'A'

group by thread#, status

order by thread#;

Standby:

select max(sequence#), thread#, status, applied from gv$archived_log

where applied = 'YES' and status = 'A'

group by thread#, status, applied order by thread#;  

h- Save output of data guard broker.

dgmgrl /

show configuration

show database verbose <primary>

show database verbose <standby>

2- Steps for deinstall:

This is a good time we can remove the instance from the RAC cluster:

a- check which dba

oracle@testa.one.com:$ which dba

/usr/bin/which: no dba in
(/u01/app/oracle/product/12.2.0.1/dbhome_1/bin:/usr/local/bin:/bin:/usr/bin:/usr/kerberos/bin
:/usr/X11R6/bin:/home/oracle:/home/oracle/sean)

b- now lets run the dbca command.

oracle@testa.one.com:$
dbca -silent -deleteInstance -nodeList testc -gdbName DBFS031
-instanceName DBFS0313 -sysDBAUserName sys -sysDBAPassword exa01_Db1

Deleting instance

1% complete

2% complete

6% complete

13% complete

20% complete

26% complete

33% complete

40% complete

46% complete

53% complete

60% complete

66% complete

Completing instance management.

100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/DBFS031.log" for further details.

3- Post-steps after deinstall:

a- check database health

oracle@testa.one.com:$ srvctl status database -d DBFS031

Instance DBFS0311 is running on node testa

Instance DBFS0312 is running on node testb

b- check database configuration.

oracle@testa.one.com:$ srvctl config database -d DBFS031

Database unique name: DBFS031

Database name:

Oracle home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Oracle user: oracle

Spfile: +DATA/DBFS031/spfileDBFS031.ora

Password file:

Domain: one.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: dba

Database instances: DBFS0311,DBFS0312

Configured nodes: testa,testb

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

c- check cluster resources showing instance3 as offline.

/u01/app/12.2.0.1/grid/bin/crsctl stat res -t > /tmp/clusterhealthafter

i do see in clusterhealthafter that mount_dbfs as offline on node 3 which is what i wanted to see.

d- create a pfile after delete instance.

SQL> create pfile='/gg_nas1/oracle/standby/dbfs030/pfile-dbfs031-after-delete-DBFS0313-dgpldb30-instance.ora' from spfile;

File created.

e- drop standby logs.

in my case standby was not configured but in case you are removing a standby instance
just remove the redo log threads.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

no rows selected

alter database drop standby logfile group 57;

alter database drop standby logfile group 58;

alter database drop standby logfile group 59;

NOTE: In case you hit the below.

ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

alter database recover managed standby database cancel;

alter system set standby_file_management='MANUAL';

Try dropping again.

f-  Now check the lag and sequence from primary and standby:

Primary:

select thread#, max(sequence#),status

from v$archived_log where status = 'A'

group by thread#, status

order by thread#;

Standby:

select max(sequence#), thread#, status, applied from gv$archived_log

where applied = 'YES' and status = 'A'

group by thread#, status, applied order by thread#;  

g- check output of data guard broker.

dgmgrl /

show configuration

show database verbose <primary>

show database verbose <standby>

That is it you have successfully removed the database instance.

How to remove a node from RAC cluster?