How to Delete 2 RAC Databases step by step process on OEL 7

 Dear All,

In this post i am sharing how to delete a unused 2 node RAC Database remove from the database server.

Environment  :: 

racnode1 ::  db 1st node ( 2 databases are running - i am removing on db UATDB1) 

racnode2 ::  db 2nd node ( 2 databases are running - i am removing on db UATDB2) 

drop/delete  a 2 node rac database >>

> take backup before drop the database and keep for some time .

> check all the files 

select name from v$datafile;

select name from v$controlfile;

select member from v$logfile;

> disable any schedule jobs on OEM /Cron

[racsinfo@racnode1 dbs]$ crontab -l

no crontab for racsinfo

[racsinfo@racnode1 dbs]$

> alter system set cluster_database=FALSE scope=spfile sid='*';

> check the db cluster parameters 

srvctl status database -d uatdb

srvctl config database -d uatdb

srvctl stop database -d uatdb

sqlplus / as sysdba

startup mount exclusive restrict;

show parameter cluster 

select logins,instance_name,status from gv$instance; 

drop database;

>> remove the configuration from the cluster 

srvctl config database -d uatdb

srvctl remove database -d uatdb

srvctl config database -d uatdb

## Remove the directories if not required like archivelog directories etc.

##Cross verify if all the associated datafiles, controlfiles and online redologs 

are removed

## Remove the entry of the database from /etc/oratab

## crosscheck and cleanup all the old dumps or any log files

##Modify/drop any of the scripts used for this database in crontab

check the database instance  status 

cluster status :  ni uat db resource .

============================ ================================

extra steps if you want to remove including homes ...

srvctl stop instance -db db_unique_name -node node_name

srvctl relocate server -servers "server_name_list" -serverpool Free

srvctl status listener -l LISTENER

srvctl disable listener -l LISTENER -n racnode1

srvctl stop listener -l LISTENER -n racnode1

[grid@racnode1 ~]$ olsnodes -s -t

racnode1        Active  Unpinned

racnode2        Active  Unpinned

[grid@racnode1 ~]$

Thanks,

Srini

How to remove/delete a used disk from ASM DiskGroup Oracle 19c RAC

 Dear All,

In this post i am sharing how to remove safely a used disk from ASM DiskGroup step by step process.

How to remove/delete a used disk from ASM DiskGroup Oracle 19c RAC

without impacting the data requires careful planning to ensure data integrity. 

Notes / Points to remember :: 

>> Normal or High Redundancy ASM will automatically rebalance the data across other disks.

>> If the disk group is External Redundancy, make sure there’s enough free space.

>>  free_mb should be greater than the space occupied by the disk you plan to drop.

>> requried_mirror_feee_mb should be less than the free_mb to ensure the proper mirroring.

>> Power Limit option Controls the speed of rebalancing the data as and when remove / add disks to DiskGroup in ASM. 

ALTER DISKGROUP DATA REBALANCE POWER 1;

A value between 1-11 is recommended (higher values consume more resources).

we can use up to 0-1024 value***** its depends on your system resource.

Example :  

we have an ASM disk group named '+DATA' with multiple disks, 

and we need to drop one disk ('DS8') safely.

Before removing a disk Need to check the DiskGroup status and used/free space.

How to Check the existing disks in the DiskGroup

set lines 200

col name format a20

col path format a28

col total_mb format 99999

col free_mb format 99999

SQL> SELECT GROUP_NUMBER, NAME, PATH, TOTAL_MB, FREE_MB, HEADER_STATUS, MOUNT_STATUS, STATE FROM V$ASM_DISK WHERE GROUP_NUMBER = (SELECT GROUP_NUMBER FROM V$ASM_DISKGROUP WHERE NAME = 'DATA');

GROUP_NUMBER NAME                 PATH                         TOTAL_MB FREE_MB HEADER_STATU MOUNT_S STATE

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

           2 DS8                  /dev/oracleasm/disks/DS8         1020     848 MEMBER       CACHED  NORMAL

           2 DATA_0000            /dev/oracleasm/disks/DATADIS    20476   17432 MEMBER       CACHED  NORMAL

         

Next Check Redundancy Type & Disk Rebalancing Power

SQL> SELECT NAME, TYPE, STATE, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB FROM V$ASM_DISKGROUP WHERE NAME = 'DATA';

NAME                 TYPE   STATE       TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB

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

DATA                 EXTERN MOUNTED        21496   18280                       0          18280

 Make sure there is sufficient free space in the disk group before dropping the disk.

SQL> SELECT NAME, FREE_MB, REQUIRED_MIRROR_FREE_MB FROM V$ASM_DISKGROUP WHERE NAME = 'DATA';

NAME                 FREE_MB REQUIRED_MIRROR_FREE_MB

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

DATA                   18280                       0

Drop the Disk from ASM Disk Group : 

Run the ALTER DISKGROUP DROP DISK command with REBALANCE to ensure data is redistributed across remaining disks.

SQL> SELECT GROUP_NUMBER, NAME, PATH, TOTAL_MB, FREE_MB, HEADER_STATUS, MOUNT_STATUS, STATE FROM V$ASM_DISK WHERE GROUP_NUMBER = (SELECT GROUP_NUMBER FROM V$ASM_DISKGROUP WHERE NAME = 'DATA');

GROUP_NUMBER NAME                 PATH                         TOTAL_MB FREE_MB HEADER_STATU MOUNT_S STATE

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

           2 DATA_0000            /dev/oracleasm/disks/DATADIS    20476   17272 MEMBER       CACHED  NORMAL

                                  K

extra /alternative options : 

ALTER DISKGROUP DATA_DG DROP DISK DS1;

DROP DISKGROUP DATA_DG INCLUDING CONTENTS;

Monitor the Rebalancing Process

Rebalancing ensures that the data is safely redistributed before the disk is removed.

Parallelly Check the rebalancing status

SQL> SELECT GROUP_NUMBER, OPERATION, STATE, EST_MINUTES FROM V$ASM_OPERATION;

no rows selected

Note : we have to Wait for the 'STATE' to show 'COMPLETED'.

Cross Check the DiskGroup After Rebalancing

SQL> SQL> SELECT NAME, TOTAL_MB, FREE_MB FROM V$ASM_DISKGROUP WHERE NAME = 'DATA';

NAME                 TOTAL_MB FREE_MB

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

DATA                    20476   17272

Note: Ensure there is no data loss and the disk group is still functioning properly.

Confirm Disk Removal from ASM

After rebalancing completes, verify that the disk has been removed.

SQL> SELECT NAME, PATH FROM V$ASM_DISK WHERE GROUP_NUMBER = (SELECT GROUP_NUMBER FROM V$ASM_DISKGROUP WHERE NAME = 'DATA');

NAME                 PATH

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

DATA_0000            /dev/oracleasm/disks/DATADISK

Note: If the disk is no longer listed, it has been successfully removed.

Decommission the Physical Disk:: its an optional step 

If the disk is no longer needed for ASM, you can manually remove it.

Check Physical Disk Name : ready to use 2nd time ... 

SELECT DISK_NUMBER, NAME, PATH FROM V$ASM_DISK WHERE HEADER_STATUS = 'FORMER';

SQL> SELECT DISK_NUMBER, NAME, PATH FROM V$ASM_DISK WHERE HEADER_STATUS = 'FORMER';

DISK_NUMBER NAME                 PATH

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

          1                      /dev/oracleasm/disks/DS10

          0                      /dev/oracleasm/disks/DS8

>>  disk with HEADER_STATUS 'FORMER' >>it is no longer part of an ASM disk group.

Delete the ASM Header on the Disk if you want to use that for different tasks.

If you plan to reuse the disk for another ASM disk group, clear its ASM metadata:

dd if=/dev/zero of=/dev/sdX bs=1024 count=100 or 

asmcmd afd_label <disk> --init

Remove the Disk from ASM Configuration

oracleasm deletedisk DS8

Verify Database Functionality at Last step ::

After removing the disk, check the database performance and logs to confirm there is no impact:

SQL> SELECT * FROM V$ASM_DISKGROUP WHERE NAME = 'DATA';

GROUP_NUMBER NAME                 SECTOR_SIZE LOGICAL_SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE   TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB

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

OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V     CON_ID

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

           2 DATA                         512                 512       4096              4194304 MOUNTED     EXTERN    20476   17272     0  3204                       0          17272

            0 19.0.0.0.0                                                   10.1.0.0.0                                                   Y    0

Note:  this Lab activity tested in my lab, you can practice in your dev/poc instance before use in the real time business databases / environment.

Thanks,

Srini