Removing a Standby Database From a Data Guard Configuration With RedoRoutes

There is an earlier post which shows how to remove a standby database from a data guard configuration when redo routes are not used. This post shows steps for removing standby databases when redo routes are in use.
The existing data guard setup is as follows.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2  - Primary database
    db1  - Physical standby database
    db3  - Physical standby database
      db4  - Physical standby database (receiving current redo)
    fs1  - Far sync instance
      db5  - Physical standby database
      db6  - Physical standby database
      db7  - Physical standby database
      db8  - Physical standby database
      db9  - Physical standby database
      db10 - Physical standby database

  Members Not Receiving Redo:
  fs2  - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

It was decided to remove standby databases db7-db10 and far sync instance fs2 from the data guard configuration. Simply running the remove database command will result in an error due to references in the redoroutes for the standby databases that are being removed.

DGMGRL> remove database db10;
Error: ORA-16691: cannot remove a configuration member that is specified in a RedoRoutes property

Failed.

If the redoroute property of the databases that will remain the data guard configuration is to be checked then references to databases being removed could be found.

DGMGRL> show database db2 redoroutes
  RedoRoutes = '(LOCAL : ( db1 SYNC), ( db3 SYNC PRIORITY=1, db4 SYNC PRIORITY=2),( fs1 SYNC PRIORITY=1, fs2 SYNC PRIORITY=2, db5 ASYNC, db6 ASYNC, db7 ASYNC, db8 ASYNC, db9 ASYNC, db10 ASYNC))'

Therefore before the standby databases are removed update the redoroutes of the databases that will remain in the data guard, by removing any references to those standby databsaes that are being removed. One exmaple redoroute update for above data guard configuration is given below.

edit database  db1 set property redoroutes='(LOCAL : ( db2 SYNC), ( db3 SYNC PRIORITY=1,  db4 SYNC PRIORITY=2),( fs1 SYNC PRIORITY=1,  fs2 SYNC PRIORITY=2,  db5 ASYNC, db6 ASYNC))( db3 :  db2 ASYNC)( db4 :  db2 ASYNC)';

Once all the redo routes are updated the data guard configuratoin may show error state while it revalidate, however, the error state could be ignored and could proceed with the removal of the standby databases.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2  - Primary database
    db1  - Physical standby database
    db3  - Physical standby database
      db4  - Physical standby database (receiving current redo)
    fs1  - Far sync instance
      db5  - Physical standby database
      db6  - Physical standby database

  Members Not Receiving Redo:
  db7  - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  db8  - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  db9  - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  db10 - Physical standby database
    Error: ORA-16810: multiple errors or warnings detected for the member

  fs2  - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 71 seconds ago)

DGMGRL> remove database db10;
Removed database " db10" from the configuration
DGMGRL> remove database db9;
Removed database " db9" from the configuration
DGMGRL> remove database db8;
Removed database " db8" from the configuration
DGMGRL> remove database db7
Removed database " db7" from the configuration

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2 - Primary database
    db1 - Physical standby database
    db3 - Physical standby database
      db4 - Physical standby database (receiving current redo)
    fs1 - Far sync instance
      db5 - Physical standby database
      db6 - Physical standby database

  Members Not Receiving Redo:
  fs2 - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

Similarly once all references to far sync instance (fs2) is removed from existing redoroutes that too could be removed from the data guard configuraiton. Redo route update without any reference to fs2 on one of the databases is given below.

edit database  db1 set property redoroutes='(LOCAL : ( db2 SYNC), ( db3 SYNC PRIORITY=1,  db4 SYNC PRIORITY=2),( fs1 SYNC PRIORITY=1,  db5 ASYNC, db6 ASYNC))( db3 :  db2 ASYNC)( db4 :  db2 ASYNC)';

Execute remove far_sync to remove the far sync instance.

DGMGRL> remove far_sync fs2;
Removed far sync instance " fs2" from the configuration
DGMGRL>  show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2 - Primary database
    db1 - Physical standby database
    db3 - Physical standby database
      db4 - Physical standby database (receiving current redo)
    fs1 - Far sync instance
      db5 - Physical standby database
      db6 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 64 seconds ago)

Related Post
Removing a Standby Database From a Data Guard Configuration

Removing a Failed Standby Database From a Data Guard Configuration

A previous post explained steps for removing a standby instance from a data guard configuration. This post explains steps for the same but when the standby being removed has failed and cannot be reached (or connect into).
In a standby configuration with multiple standby databases once instance is unreachable due to hardware failure. The issue is irrecoverable and only option is to rebuild the node and the standby instance. In mean time the existing standby configuration will give an error state due to the unavailability of the failed instance.

DGMGRL> show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)
  ppdb8  - Physical standby database
    Error: ORA-12170: TNS:Connect timeout occurred

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 96 seconds ago)

As the first step remove any references to the failed instance on RedoRoutes.

Then issue the remove command which will succeed with a warning.

DGMGRL> remove database ppdb8;
Warning: ORA-16620: one or more members could not be reached for a remove operation

Removed database "ppdb8" from the configuration

The warning is due to broker being unable to connect to the failed instance to execute the clean up commands. The dataguard broke log shows this.

2020-07-29T12:33:36.403+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Metadata Resync failed. Status = ORA-12170
2020-07-29T12:33:48.691+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               fc_pp_dg                       Warning  ORA-16607: one or more members have failed
  Primary Database            ppdb1                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb2                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb3                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb4                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb5                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb6                          Success  ORA-0: normal, successful completion
  Far Sync Instance           ppfs1                          Success  ORA-0: normal, successful completion
  Far Sync Instance           ppfs2                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb8                            Error  ORA-12170: TNS:Connect timeout occurred
  Physical Standby Database   ppdb9                          Success  ORA-0: normal, successful completion
  Physical Standby Database   ppdb10                         Success  ORA-0: normal, successful completion
2020-07-29T12:34:00.979+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:05.646+00:00
REMOVE DATABASE ppdb8
2020-07-29T12:34:17.939+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
Database ppdb8 (0x0a001000) could not be contacted for database removal, status = ORA-12170
2020-07-29T12:34:31.571+00:00
Failed to connect to remote database ppdb8. Error is ORA-12170
Failed to send message to member ppdb8. Error code is ORA-12170.
2020-07-29T12:34:33.297+00:00
Database ppdb8 removal completed with warning ORA-16620
REMOVE DATABASE  completed with warning ORA-16620

However, all the other databases that are part of the dataguard configuration would have had their log_archive_config parameter updated by removing any reference to the failed database.

NAME                           VALUE
------------------------------ -----------------------------------------
log_archive_config             dg_config=(ppdb1,ppdb2,ppdb3,ppdb4,ppfs1,
                               ppdb5,ppdb6,ppdb9,ppdb10,ppfs2)

Once the failed instance is removed the dataguard broke shows status success.

DGMGRL>  show configuration

Configuration - fc_pp_dg

  Protection Mode: MaxAvailability
  Members:
  ppdb1  - Primary database
    ppdb2  - Physical standby database
    ppdb3  - Physical standby database
      ppdb4  - Physical standby database (receiving current redo)
    ppfs1  - Far sync instance
      ppdb5  - Physical standby database
      ppdb6  - Physical standby database
      ppdb9  - Physical standby database
      ppdb10 - Physical standby database

  Members Not Receiving Redo:
  ppfs2  - Far sync instance (alternate of ppfs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

Related Posts
Removing a Standby Database From a Data Guard Configuration
Adding a New Physical Standby to Exiting Data Guard Setup

Removing a Standby Database From a Data Guard Configuration

The post shows the steps for removing a standby database from a data guard configuration and make it available for read/write access. The data guard configuration consists of two standby databases.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb  - Primary database
    stbycdb  - Physical standby database
    stby2cdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 17 seconds ago)

The standby database named "stby2cdb" is to be removed from the configuration. This is achieved by the use of "remove database" command in the data guard broker. If "preserve destination" clause is not used then this command remove all reference to the database being removed from other databases that will remain the data guard configuration. The default behaviour is to remove all references to the removed database so simply run the remove command with the database name

DGMGRL> remove database stby2cdb;
Removed database "stby2cdb" from the configuration

The data guard configuration reflect the change.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 26 seconds ago)

On the alert log of the primary (prodcdb) it could be seen that log_archive_config and log_archive_destination parameter being modified. The commands to update these parameters are issue by the data guard broker.

2018-08-06T12:05:04.410188+01:00
Destination LOG_ARCHIVE_DEST_3 no longer supports SYNCHRONIZATION
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
2018-08-06T12:05:04.433444+01:00
ALTER SYSTEM SET log_archive_dest_3='' SCOPE=BOTH;
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(prodcdb,stbycdb)']
2018-08-06T12:05:04.476398+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(prodcdb,stbycdb)' SCOPE=BOTH;
2018-08-06T12:05:06.540027+01:00
ALTER SYSTEM SWITCH ALL LOGFILE start (prodcdb)

On the alert log of the remaining standby (stbycdb) it could be seen that log_archive_config parameter is updated to reflect only the databases that remain on the data guard configuration.

2018-08-06T13:38:40.813124+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(stbycdb,prodcdb)']
2018-08-06T13:38:40.886959+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(stbycdb,prodcdb)' SCOPE=BOTH;

On the alert log of the database that was removed (stby2cdb), the log_archive_config and fal_server parameter values were updated by removing any references to the database in the DG configuration.

2018-08-06T14:31:27.917074+01:00
ALTER SYSTEM SET log_archive_config='nodg_config' SCOPE=BOTH;
2018-08-06T14:31:27.927364+01:00
ALTER SYSTEM SET fal_server='' SCOPE=BOTH;

Though these DG related parameters were updated and reset all databases, the db/log file name convert related value remain the same Following output is from production DB.

DGMGRL> show database prodcdb  LogFileNameConvert
  LogFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'

DGMGRL>  show database prodcdb DbFileNameConvert
  DbFileNameConvert = '/stbycdb/,/prodcdb/,/stby2cdb/,/prodcdb/'

Update DbFileNameConvert and LogFileNameConvert values on all databases that remain in DG configuration by removing any references to the removed database. The changes require a restart of the database to take effect.

DGMGRL> edit database prodcdb  set property 'LogFileNameConvert'='/stbycdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "LogFileNameConvert" updated

DGMGRL>  edit database prodcdb  set property 'DbFileNameConvert'='/stbycdb/,/prodcdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "DbFileNameConvert" updated

DGMGRL>  edit database stbycdb set property 'DbFileNameConvert'='/prodcdb/,/stbycdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "DbFileNameConvert" updated

DGMGRL>  edit database stbycdb set property 'LogFileNameConvert'='/prodcdb/,/stbycdb/';
Warning: ORA-16675: database instance restart required for property value modification to take effect

Property "LogFileNameConvert" updated

If possible carry out a switchover to test the DG configuration.

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  prodcdb - Primary database
    stbycdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

DGMGRL> switchover to stbycdb
Performing switchover NOW, please wait...
Operation requires a connection to database "stbycdb"
Connecting ...
Connected to "stbycdb"
Connected as SYSDBA.
New primary database "stbycdb" is opening...
Oracle Clusterware is restarting database "prodcdb" ...
Switchover succeeded, new primary is "stbycdb"

DGMGRL> show configuration

Configuration - dg12c2

  Protection Mode: MaxAvailability
  Members:
  stbycdb - Primary database
    prodcdb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

This conclude the removing of a standby database from a DG configuration.



The physical standby that was removed from the DG configuration could be converted to an active database open for read/write. If the DG configuration was in maximum availability or maximum protection mode then lower the protection mode of the database to maximum performance. If not opening the database will fail with LGWR complaining the following.

LGWR: Primary database is in MAXIMUM AVAILABILITY mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 LGWR standby database required
2018-08-06T15:01:23.198018+01:00
Errors in file /opt/app/oracle/diag/rdbms/stby2cdb/stby2cdb/trace/stby2cdb_lgwr_28545.trc:
ORA-16072: a minimum of one standby database destination is required

Run the following to lower the protection mode

SQL>  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  UNPROTECTED

Activate the physical standby for read/write.

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Reset db/log file name convert parameters. This requires restart of the database to take affect.

alter system reset db_file_name_convert scope=spfile;
alter system reset log_file_name_convert scope=spfile;

Set DG broker auto start to false

alter system set dg_broker_start=false scope=both;

If archive log deletion policy was set to DG specific policy such as "CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY" then reset it to none

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

The standby was managed by clusterware as part of Oracle restart configuration. The database configuration information would still be listed as physical standby and mount or read only open mode (whichever was set for standby).

srvctl config database -d stby2cdb
Database unique name: stby2cdb
...
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY

Modify the database to reflect its new role and start up mode

srvctl modify database -d stby2cdb -role primary -startoption open

srvctl config database -d stby2cdb
Database unique name: stby2cdb
...
Start options: open
Stop options: immediate
Database role: PRIMARY

Modify any database services that were created to start when the database was in physical standby role.

srvctl config service -d stby2cdb -s abc
Service name: abc
Cardinality: SINGLETON
Service role: PHYSICAL_STANDBY

Modify to the service configuration so it starts when the database is in primary role (if this is applicable to business needs).

srvctl modify  service -d stby2cdb -s abc -l primary

srvctl config service -d stby2cdb -s abc
Service name: abc
Cardinality: SINGLETON
Service role: PRIMARY

Shutdown the database and start it using srvctl to validate the configuration.

srvctl start database -d stby2cdb

Removing a Failed Node From the Cluster

This post list steps for removing a failed node from a cluster. The steps differs from steps in the previous node deletion posts (11gR1,11gR2 and 12c) such that one node has suffered a catastrophic failure and is not available for any kind of command or script executions. Therefore all the activities involved in removing the failed node are executed from a surviving node.
The environment used in this case is a two node RAC with role separation(11.2.0.4). Under normal operation it has the following resources and status. (formatted status)

Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m2
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m2
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m2
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m2
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m2
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m2
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m2
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m2
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m2
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.rhel6m2.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m2
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m2
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m2
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m2
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m2
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m1

After the node 2 (rhel6m2 node in this case) suffers a catastrophic failure, resources and status is as below. There are offline and failed over (vip) resources from rhel6m2.

Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m1
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.rhel6m2.vip                     ora.cluster_vip_net1.type ONLINE             INTERMEDIATE       rhel6m1
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             OFFLINE
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             OFFLINE
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             OFFLINE
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m1

Removing of resources of the failed node begins at database resource level. There are two services running and they both have the DB instance on the failed node as a preferred instance (output is condensed)

srvctl config service -d std11g2
Service name: myservice
Service is enabled
Server pool: std11g2_myservice
Cardinality: 2
...
Preferred instances: std11g21,std11g22
Available instances:
Service name: abx.domain.net
Service is enabled
Server pool: std11g2_abx.domain.net
Cardinality: 2
...
Preferred instances: std11g21,std11g22
Available instances:

Modify the service configuration so that only the surviving instances are set as preferred instances.

$ srvctl modify service -s myservice -d std11g2 -n -i std11g21 -f
$ srvctl modify service -s abx.domain.net -d std11g2 -n -i std11g21 -f

$ srvctl config service -d std11g2
Service name: myservice
Service is enabled
Server pool: std11g2_myservice
Cardinality: 1
..
Preferred instances: std11g21
Available instances:
Service name: abx.domain.net
Service is enabled
Server pool: std11g2_abx.domain.net
Cardinality: 1
..
Preferred instances: std11g21
Available instances:

$ srvctl status service -d std11g2
Service myservice is running on instance(s) std11g21
Service abx.domain.net is running on instance(s) std11g21

Remove the database instance on the failed node

srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
...
Database instances: std11g21,std11g22
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,abx.domain.net
Type: RAC
Database is administrator managed

This is done using DBCA's instance management option. If the listener has a non-default name and port then accessing the DB will fail with below message.

To fix this create a default listener (name listener and port 1521). Also if VNCR is used then remove the failed node from the registration list. Proceed to instance deletion by selecting the inactive instance on the failed node.

As node 2 is not available following warning will be issued. Click continue and proceed. During the execution various other warning will appear such as unable to remove /etc/oratab etc all of these could be ignored.

However DBCA didn't run till end, at 67% (observed through repeated runs on this 11.2.0.4 environment) following dialog box appeared. As seen on the screenshot it has no message, just an OK button. Clicking it doesn't end the DBCA session but goes to the beginning and exit the DBCA clicking cancel afterwards.

However this doesn't appear to be a failure on the DBCA to remove the instance. In fact instance is removed as subsequent instance operation only list the instance on the surviving node.

Querying the database also shows that instance 2 (std11g22 in this case) related undo tablespace and redo logs have been removed and only surviving instance related undo tablespace and redo logs are available.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEST

7 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       1598   52428800        512          2 NO  CURRENT               68471125 07-JUL-16   2.8147E+14
         2          1       1597   52428800        512          2 YES INACTIVE              68467762 07-JUL-16     68471125 07-JUL-16

srvctl config database -d std11g2
Database unique name: std11g2
Database name: std11g2
...
Database instances: std11g21
Disk Groups: DATA,FLASH
Mount point paths:
Services: myservice,abx.domain.net
Type: RAC
Database is administrator managed

Once the database resources are removed next step is to remove the Oracle database home entry for the failed node from the inventory.



As the node is unavailable, there's no un-installation involved. Run the inventory update command with surviving nodes. Inventory content for the Oracle home before the failed node is removed.

<HOME NAME="OraDb11g_home2" LOC="/opt/app/oracle/product/11.2.0/dbhome_4" TYPE="O" IDX="4">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
      <NODE NAME="rhel6m2"/>
   </NODE_LIST>
</HOME>

After the inventory update

 ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rhel6m1}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4095 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'UpdateNodeList' was successful.

<HOME NAME="OraDb11g_home2" LOC="/opt/app/oracle/product/11.2.0/dbhome_4" TYPE="O" IDX="4">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
   </NODE_LIST>
</HOME>

Next step is to remove the cluster resources and the node itself. If any of the node is in pin stat, unpin them. In this case both nodes are unpinned

olsnodes -s -t
rhel6m1 Active  Unpinned
rhel6m2 Inactive        Unpinned

Stop and remove the VIP resource of the failed node

# srvctl stop  vip -i rhel6m2-vip -f
# srvctl remove vip -i rhel6m2-vip -f

Remove the failed node from the cluster configuration

#  crsctl delete node -n rhel6m2
CRS-4661: Node rhel6m2 successfully deleted.

Finally remove the grid home for the failed node from the inventory. Before inventory update

<HOME NAME="Ora11g_gridinfrahome2" LOC="/opt/app/11.2.0/grid4" TYPE="O" IDX="3" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
      <NODE NAME="rhel6m2"/>
   </NODE_LIST>
</HOME>

After inventory update

./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME "CLUSTER_NODES={rhel6m1}" CRS=TRUE
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4095 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'UpdateNodeList' was successful.

<HOME NAME="Ora11g_gridinfrahome2" LOC="/opt/app/11.2.0/grid4" TYPE="O" IDX="3" CRS="true">
   <NODE_LIST>
      <NODE NAME="rhel6m1"/>
   </NODE_LIST>
</HOME>

Validate the node removal with cluvfy

cluvfy stage -post  nodedel -n rhel6m2

Performing post-checks for node removal
Checking CRS integrity...
Clusterware version consistency passed
CRS integrity check passed
Node removal check passed
Post-check for node removal was successful.

Remove the default listener if one was created during instance remove step. The final status of resource is as below.

Resource Name                       Type                      Target             State              Host
-------------                       ------                    -------            --------           ----------
ora.CLUSTER_DG.dg                   ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.DATA.dg                         ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.FLASH.dg                        ora.diskgroup.type        ONLINE             ONLINE             rhel6m1
ora.MYLISTENER.lsnr                 ora.listener.type         ONLINE             ONLINE             rhel6m1
ora.MYLISTENER_SCAN1.lsnr           ora.scan_listener.type    ONLINE             ONLINE             rhel6m1
ora.asm                             ora.asm.type              ONLINE             ONLINE             rhel6m1
ora.cvu                             ora.cvu.type              ONLINE             ONLINE             rhel6m1
ora.gsd                             ora.gsd.type              OFFLINE            OFFLINE
ora.net1.network                    ora.network.type          ONLINE             ONLINE             rhel6m1
ora.oc4j                            ora.oc4j.type             ONLINE             ONLINE             rhel6m1
ora.ons                             ora.ons.type              ONLINE             ONLINE             rhel6m1
ora.registry.acfs                   ora.registry.acfs.type    ONLINE             ONLINE             rhel6m1
ora.rhel6m1.vip                     ora.cluster_vip_net1.type ONLINE             ONLINE             rhel6m1
ora.scan1.vip                       ora.scan_vip.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.db                      ora.database.type         ONLINE             ONLINE             rhel6m1
ora.std11g2.myservice.svc           ora.service.type          ONLINE             ONLINE             rhel6m1
ora.std11g2.abx.domain.net.svc      ora.service.type          ONLINE             ONLINE             rhel6m1

Useful metalink notes
How to remove/delete a node from Grid Infrastructure Clusterware when the node has failed [ID 1262925.1]
Steps to Remove Node from Cluster When the Node Crashes Due to OS/Hardware Failure and cannot boot up [ID 466975.1]
RAC on Windows: How to Remove a Node from a Cluster When the Node Crashes Due to OS/Hardware Failure and Cannot Boot [ID 832054.1]

Related Post
Deleting a Node From 12cR1 RAC
Deleting a Node From 11gR2 RAC
Deleting a 11gR1 RAC Node

Removing Oracle Components From Standared Edition Database

Oracle database components that are not supported on standard edition will get installed if the database was created using a template (e.g OLTP template). This can results in warnings during upgrades and at times issues with other oracle utilities such as export/import data pumps.
It may be helpful and could speed up the upgrade if these unsupported and unwanted (by the application DB is used for) components are removed before the upgrade. This post list steps for removing some of these components. It's best to take a full database backup before running these components removal scripts.
The database used is a 11.2.0.3 SE database created with the OLTP template. Currently all the components have the status valid.

--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID

But if ultrp was run some of the unsupported components will become invalid.
1. Removing Application Express

@?/apex/apxremov.sql

2. Removing Multimeida (interMedia)

@?/ord/im/admin/imremov.sql

3. Removing Spatial

sqlplus "/as sysdba"
set pagesize 0
set feed off
spool dropsyn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS';
spool off;
@dropsyn.sql
set feedback on

drop user mddata cascade;
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade;
drop user MDSYS cascade;

4.Removing Expression Filter

@$ORACLE_HOME/rdbms/admin/catnoexf.sql

5. Removing OLAP components (also refer 1362752.1, 565773.1)

drop user olapsys cascade;
@?/rdbms/admin/utlrp
spool olapdrop.sql
select 'drop public synonym ' || synonym_name || ';' from dba_synonyms where owner='PUBLIC' and table_owner='OLAPSYS';
spool off
@olapdrop.sql
select * from sys.exppkgact$ where package = 'DBMS_AW_EXP' and  schema= 'SYS';
delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';
commit;

select * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
create table exppkgact$_bck as select * from exppkgact$;
delete from exppkgact$ where package = 'DBMS_CUBE_EXP' and schema = 'SYS';
commit;

 drop package CWM2_OLAP_INSTALLER;
 @?/rdbms/admin/utlrp

At this stage Oracle database packages and types may be invalid. To fix this start the DB in restrict mode and run

 @?/rdbms/admin/catalog.sql
 @?/rdbms/admin/catproc.sql
 @?/rdbms/admin/utlrp.sql

6. Removing XDK for Java

 drop public synonym xslprocessor;
 @?/xdk/admin/rmxml.sql

7. Removing Java packages

@?/rdbms/admin/catnojav.sql

8. Removing JServer

@?/javavm/install/rmjvm.sql

9. Removing Oracle Warehouse Builder (OWB)

@?/owb/UnifiedRepos/clean_owbsys.sql

These are the only components removed as the application still uses wm_concat workspace manager could not be removed. But if required following script could be used to remove workspace manager

@$ORACLE_HOME/rdbms/admin/owmuinst.plb

Removing Oracle text may have consequences for expdp/impdp (not tested) so it was left. But if required this too could be removed with

 @?/ctx/admin/catnoctx.sql
 drop procedure sys.validate_context;

At this stage the OLAP components (only two will left in the dba_registry listing) may have status invalid. Other components will have status valid or removed. This doesn't affect the upgrade from 11.2.0.3 to 11.2.0.4. Once upgraded the OLAP components will have the status option_off.

 COMP_NAME                                STATUS
---------------------------------------- -------------
Oracle Enterprise Manager                VALID
Oracle XML Database                      VALID
Oracle Text                              VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             REMOVED
Oracle XDK                               REMOVED
Oracle Database Java Packages            REMOVED
OLAP Analytic Workspace                  OPTION OFF
Oracle OLAP API                          OPTION OFF

Useful metalink note
Information On Installed Database Components and Schemas [ID 472937.1]

Deinstalling Cluster base Grid Control Agents

To remove grid control agents from a clustered configuration or standalone database use

/opt/app/oracle/grid_agent/agent11g/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/opt/app/oracle/grid_agent/agent11g}" -removeallfiles
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-09_12-46-19PM. Please wait ...[oracle@rac1 bin]$ Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Thursday, February 9, 2012 12:47:08 PM GMT)
Configuration assistant "Agent Deinstall Assistant" succeeded
Configuration assistant "Oracle Configuration Manager Deinstall" succeeded
............................................................... 100% Done.

Deinstall successful
Removing Cluster Oracle homes

This would remove agent installations from all the nodes in the cluster.

Removing Datafiles from a Tablespace

There are serveral ways to remove a datafile, one is with an offline drop and other is using alter tablespace. The offline drop is to be used inconjunction with drop tablespace, that is is expected the tablespace will also be dropped. If the intention is just to drop one or more datafiles that is part of a tablespace then alter tablespace drop datafile must be used which was introduced with 10gR2. There are several constraints to dropping a datafile they are (from Oracle documentation)
The following are restrictions for dropping datafiles and tempfiles:
The database must be open.

If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.

You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.

You cannot drop datafiles in a read-only tablespace.

You cannot drop datafiles in the SYSTEM tablespace.

If a datafile in a locally managed tablespace is offline, it cannot be dropped..

Situation is that there are segment(s) where extents are spread across multiple datafiles and now it is decided to move all the extents into single datafile.

SELECT x.*,  COUNT(*) FROM
  (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
    segment_name ,    segment_type ,    tablespace_name
  FROM dba_extents  WHERE tablespace_name='TEST'  ) x
group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          19
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULT     TABLE    TEST           1
ASANGA +FLASH/rac11g1/datafile/test.265.758295919  FULINDX  INDEX    TEST          12

For testing this could be created with step 1-4 in an earlier post.

To drop the datafile in the flash diskgroup move the segments (table and indexes) to another tablespace either through alter table .. move (there are other ways too) and alter index rebuild .. tablespace (just one way to move an index). Depending on the number of segments in the datafile that is in flash diskgroup this could take some time and if logging is used could generate lot of redo.

Once all the extents are moved out drop the datafile from the tablespace. If the datafile is not empty following error will be shown

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';
alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919'
*
ERROR at line 1:
ORA-03262: the file is non-empty

To move segments

SQL> alter table fult move tablespace users;

Table altered.

SQL> alter index fulindx rebuild online tablespace users;

Index altered.

After the move verify no extents are on the datafiles in question

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

no rows selected

Drop the datafile

SQL> alter tablespace test drop datafile '+FLASH/rac11g1/datafile/test.265.758295919';

Tablespace altered.

Move the segments back into the tablespace which now has only the desired datafile(s).

SQL> alter table fult move tablespace test;

Table altered.

SQL> alter index fulindx rebuild online tablespace test;

Index altered.

SQL> SELECT x.*,  COUNT(*) FROM
  2    (SELECT owner, (SELECT name FROM v$datafile WHERE file#=file_id ) AS filename ,
  3      segment_name ,    segment_type ,    tablespace_name
  4    FROM dba_extents  WHERE tablespace_name='TEST'  ) x
  5  group by owner,filename,segment_name,segment_type,tablespace_name ORDER BY 1,4 DESC,  3;

OWNER  FILENAME                                    SEGMENT_ SEGMENT_ TABLE   COUNT(*)
------ ------------------------------------------- -------- -------- ----- ----------
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULT     TABLE    TEST          20
ASANGA +DATA/rac11g1/datafile/test.372.758295243   FULINDX  INDEX    TEST          12

Removing Grid Control Agent from One Node Only in Silent Mode

This blog is for situation where grid control agent is removed from one node in the cluster.

1. Stop the agent on the remote host

2. Run the following on the node on which agent is being removed

/opt/app/oracle/grid_agent/agent11g/oui/bin/runInstaller -updateNodeList ORACLE_HOME=/opt/app/oracle/grid_agent/agent11g "CLUSTER_NODES={rac2}" -local
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oracle/oraInventory

3. Deinstall agent on the node oracle doc says to use -forceDeinstall but installer complains about this.

oui/bin/runInstaller -silent  "REMOVE_HOMES={/opt/app/oracle/grid_agent/agent11g}" -deinstall -waitForCompletion -removeallfiles -local -forceDeinstall
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-12-15_12-28-38PM. Please wait ...Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

The command line arguments '-forceDeinstall' are not valid options. Type 'runInstaller  -help' at the command line for instructions on appropriate command line usage.
SEVERE:The command line arguments '-forceDeinstall' are not valid options. Type 'runInstaller  -help' at the command line for instructions on appropriate command line usage.

Run without it and command execute without an error

oui/bin/runInstaller -silent  "REMOVE_HOMES={/opt/app/oracle/grid_agent/agent11g}" -deinstall -waitForCompletion -removeallfiles -local
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-12-15_12-29-00PM. Please wait ...Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Wednesday, December 15, 2010 12:29:05 PM GMT)
Configuration assistant "Agent Deinstall Assistant" succeeded
Configuration assistant "Oracle Configuration Manager Deinstall" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Wednesday, December 15, 2010 12:30:20 PM GMT)
End of deinstallations
Please check '/opt/app/oracle/oraInventory/logs/silentInstall2010-12-15_12-29-00PM.log' for more details.

4. On each of the remaining nodes run installer with remaining node list.

./runInstaller -updateNodeList ORACLE_HOME=/opt/app/oracle/grid_agent/agent11g "CLUSTER_NODES={rac1}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oracle/oraInventory
'UpdateNodeList' was successful.

5. Remove all targets on that node from the grid control target page.