Clean Grid Infrastructure Environment

The purpose of this document is to describe steps to clean clusterware (GI) environment where deinstall fails to work or cannot complete.

Cleaning Steps

1. Remove the current GI home from central inventory
As grid home owner:

$ORACLE_HOME/oui/bin/runInstaller -detachHome -silent -local ORACLE_HOME=$ORACLE_HOME

2. Disable oracle ohasd service on Linux as root user

# /usr/bin/systemctl stop oracle-ohasd.service
# /usr/bin/systemctl disable oracle-ohasd.service

If following appears just ignore and go ahead

# /usr/bin/systemctl stop oracle-ohasd.service
Failed to stop oracle-ohasd.service: Unit oracle-ohasd.service not loaded.
# /usr/bin/systemctl disable oracle-ohasd.service
Failed to disable unit: Unit file oracle-ohasd.service does not exist.

3. Stop ohasd service as super user (‘root’)

# systemctl stop ohasd.service

4. Remove files under ‘/etc/oracle’ as root user:

# rm -Rf /etc/oracle

5. Remove daemon configuration files

# rm /etc/init.d/init.evmd /etc/init.d/init.crsd \
/etc/init.d/init.cssd /etc/init.d/init.crs /etc/init.d/init.ohasd

6. Remove ‘ohas’ related files from /etc

# find /etc -name ‘*ohas*’

Example

# find /etc -name ‘*ohas*’
/etc/systemd/system/oracle-ohasd.service.d
/etc/rc.d/init.d/ohasd

Remove found files related to Grid Infrastructure by ‘rm’ command.

Note: Do not remove OS files。

7. Check any ‘crs’ related file under /etc and remove

# find /etc -name ‘*crs*’

If confirmed to be relevant you can remove by

# find /etc -name ‘*crs*’ |xargs rm -rf

8. [Optional] Remove TFA service files

# systemctl stop oracle-tfa.service

# systemctl disable oracle-tfa.service

Example output

# systemctl disable oracle-tfa.service
Removed /etc/systemd/system/multi-user.target.wants/oracle-tfa.service.
Removed /etc/systemd/system/graphical.target.wants/oracle-tfa.service.

9. [Optional] Remove other TFA related file

# find /etc -name ‘*tfa*’

Remove the found file(s) accordingly.

10. Remove files under grid home

# rm -Rf $ORACLE_HOME/*
# rm -Rf $ORACLE_HOME/.opatchauto_storage
# rm -Rf $ORACLE_HOME/.patch_storage

11. Remove grid base

# rm -Rf <Oracle Base>

12. Remove socket files

# rm -rf /var/tmp/.oracle
# rm -rf /tmp/.oracle

13. Remove temporary files

cd /tmp
rm -rf CVU_xxxx_grid logs Logs OraInstall* deinstall*

14. Remove central inventory directory and /etc/oraInst.loc if there is no need to reserve them.

15. Unset ORACLE_HOME and other environmental variables.

16. Modify /etc/passwd and /etc/group to remove GI related user(s) and group(s).

17. remove all data on OCR disk header.

# dd if=/dev/zero of=/dev/mapper/ocr_disk1 bs=1024k count=1

“runcluvfy.sh Stage -pre crsinst -n node1,node2” Hangs

The following command hangs :

$cd /u01/app/19.0.0/grid/
$./runcluvfy.sh Stage -pre crsinst -n node1,node2 -verbose

SOLUTION

$ cat /etc/redhat-release
Red Hat Enterprise Linux release 8.6 (Ootpa)

As workaround:
         $export CV_ASSUME_DISTID=OEL7.8

$./runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

Or

Use the latest Cluster Verification Utility (CVU) (Doc ID 2731675.1)

 Patch 30839369

How to Switch or Recreate Undo Tablespace at PDB Level

From 12.2.0.1 onwards, local undo tablespace is introduced in PDB level. In many cases, the automatically created undo tablespace in PDB is not complying with naming standard. For example:

SQL>  select inst_id,name, con_id, value 
from gv$system_parameter 
where name='undo_tablespace' and con_id=3 
order by 1;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               3 UNDO_3

SQL> SELECT property_name, property_value 
FROM database_properties 
WHERE property_name = 'LOCAL_UNDO_ENABLED';  

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

RESOLUTION

Rename undo tablespace

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL>  select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
UNDO_3

SQL> alter tablespace undo_3 rename to UNDOTBS3;

Tablespace altered.

SQL> select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
UNDOTBS3

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO

SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
undo_tablespace  
                    string      UNDO_3

SQL> ALTER SYSTEM set  UNDO_TABLESPACE='UNDOTBS3' scope=both;

System altered.

SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
undo_tablespace                      string      UNDOTBS3

Close PDB1 and start PDB1 to check further whether UNDOTBS3 assigned to instance 3.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
undo_tablespace                      string      UNDOTBS3

-- checked in spfile for PDB1??
SQL> create pfile='/tmp/x.x' from spfile;

File created.

SQL> ! cat /tmp/x.x
*.undo_tablespace='UNDOTBS3'

Check parameter UNDO_TABLESPACE in CDB and PDB, all looks good.

SQL>  select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

Finally reset undo_tablespace parameter in PDB , and bounce PDB on instance 3:

SQL> alter session set container=pdb1;

Session altered.

SQL>  alter system reset undo_tablespace;

System altered.

SQL> create pfile='/tmp/x.x' from spfile;

File created.

SQL> ! cat /tmp/x.x

SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               3 UNDOTBS3

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;

   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 MOUNTED

6 rows selected.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.


SQL>  show parameter undo_tablespace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS3
SQL>
SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;


   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

Recreate a New Undo Tablespace

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDB1                               MOUNTED

SQL> alter session set container=PDB1;


SQL> alter pluggable database PDB1 open restricted;

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        3 PDB1                           READ WRITE YES


SQL> CREATE UNDO TABLESPACE UNDOTBS3 datafile '+DATA2'  SIZE 100M AUTOEXTEND ON ;

Tablespace created.

--
-- can NOT drop UNDO_3 now
--
SQL> DROP tablespace UNDO_3 including contents and datafiles;
DROP tablespace UNDO_3 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO_3' is currently in use

--
-- Here the following NO need scope=spfile 
--
SQL> ALTER SYSTEM set  UNDO_TABLESPACE='UNDOTBS3';

 System altered.

SQL> DROP tablespace UNDO_3 including contents and datafiles;

Tablespace dropped.


SQL> select inst_id,name, con_id, value from gv$system_parameter where name='undo_tablespace' and con_id in (0,3) order by INST_ID,CON_ID;


   INST_ID NAME                     CON_ID VALUE
---------- -------------------- ---------- --------------------
         1 undo_tablespace               0 UNDOTBS1
         1 undo_tablespace               3 UNDOTBS1
         2 undo_tablespace               0 UNDOTBS2
         2 undo_tablespace               3 UNDOTBS2
         3 undo_tablespace               0 UNDOTBS3
         3 undo_tablespace               3 UNDOTBS3

6 rows selected.

SQL> alter pluggable database PDB1 close;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

Local and Shared Undo Tablespace Modes

Oracle introduced Local Undo since 12cR2. Before 12cR2, there is no local undo concept, the Undo tablespace used to be shared or global across all the PDBs in a container.

How to check the current Undo mode

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        TRUE       true if local undo is enabled

How to convert CDB from Local to Shared Undo Mode

When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

SQL> startup upgrade

SQL> sho con_name 

CON_NAME
-----------------
CDB$ROOT

SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.

 
SQL> shutdown immediate

SQL> startup

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_V DESCRIPTION
------------------------- ---------- ------------------------------
LOCAL_UNDO_ENABLED        FALSE       true if local undo is enabled

-- Check current undo tablespaces for CDB and all PDBS.
--
SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

 
--
-- Drop the Undo tablespace from the PDBs
--
SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.

How to convert CDB from Shared to Local Undo Mode

When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

SQL> startup upgrade

SQL>  alter database local undo on;

SQL> startup force;

-- Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace
--
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

SQL> CREATE UNDO TABLESPACE seed_undo datafile '+DATA1'......

SQL> alter system set  UNDO_TABLESPACE='SEED_UNDO' scope=both;

SQL> show parameter undo_tablespace
undo_tablespace                      string      SEED_UNDO


SQL>  ALTER SESSION SET CONTAINER=CDB$ROOT; 

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE;

-- Open the PDB is read write mode

SQL> ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE;

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
-------------------------------------------------------------------
         3 UNDO_1

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf  << Undo tablespace is created automatically.

          1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

-- From the PDB
--

SQL> ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
---------------------------------------------------------------

         3 UNDO_1 << Undo tablespace is created automatically.

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

Oracle RAC Database Private Thread

One of our RAC database has a private thread for some reason by a DBA playing around.

SQL> select thread#, enabled from v$thread;

  THREAD# ENABLED
---------- --------
         1 PUBLIC
         2 PUBLIC
         3 PRIVATE

SOLUTION

Check spfile with thread 3 included.
Add redo log groups for thread 3.
Add undo tablespace for threda 3.
CREATE SMALLFILE UNDO TABLESPACE UNDOTBS3 DATAFILE ‘+DATA2’ SIZE 512M AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M;
Disable thread 3.

SQL> Alter database disable public thread 3;
Enable thread 3.

SQL> Alter database enable public thread 3;
Check thread 3 changes to PUBLIC.

SQL> select thread#, enabled from v$thread;

THREAD# ENABLED
———- ——–
1 PUBLIC
2 PUBLIC
3 PUBLIC