System & Database Related Solutions

Sunday, 5 October 2025

Move OCR , Vote File , ASM SPILE to new Diskgroup (Oracle 19c RAC)

root@misdg:/d01/soft# /d01/app/19c/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 9501fcc2dc854f80bff978f053afd973 (/dev/rdsk/c0t6006016084734D00B615DA687385B576d0s0) [OCRVOTE]
Located 1 voting disk(s).
root@misdg:/d01/soft#

----Alert log-----
CREATE DISKGROUP OCRVOTE1 EXTERNAL REDUNDANCY DISK '/dev/rdsk/c0t6006016084734D00116EDB6817C70198d0s0' SIZE 511829M
ATTRIBUTE 'compatible.asm'='19.0.0.0','compatible.advm'='19.0.0.0','au_size'='4M'

/d01/app/19c/grid/bin/crsctl replace votedisk +OCRVOTE1

root@misdg:/d01/soft# /d01/app/19c/grid/bin/crsctl replace votedisk +OCRVOTE1
Successful addition of voting disk 2e091a5ac02d4f4ebfb44511c10221ec.
Successful deletion of voting disk 9501fcc2dc854f80bff978f053afd973.
Successfully replaced voting disk group with +OCRVOTE1.
CRS-4266: Voting file(s) successfully replaced

root@misdg:/d01/soft#
root@misdg:/d01/soft# /d01/app/19c/grid/bin/crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'misdg'
CRS-2673: Attempting to stop 'ora.crsd' on 'misdg'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'misdg'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'misdg'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'misdg'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'misdg'
CRS-33673: Attempting to stop resource group 'ora.asmgroup' on server 'misdg'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'misdg'
CRS-2673: Attempting to stop 'ora.OCRVOTE.dg' on 'misdg'
CRS-2673: Attempting to stop 'ora.OCRVOTE1.dg' on 'misdg'
CRS-2673: Attempting to stop 'ora.REDO01.dg' on 'misdg'
CRS-2673: Attempting to stop 'ora.REDO02.dg' on 'misdg'
CRS-2673: Attempting to stop 'ora.REDO03.dg' on 'misdg'
CRS-2673: Attempting to stop 'ora.cvu' on 'misdg'
CRS-2677: Stop of 'ora.cvu' on 'misdg' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'misdg' succeeded
CRS-2677: Stop of 'ora.OCRVOTE.dg' on 'misdg' succeeded
CRS-2677: Stop of 'ora.REDO01.dg' on 'misdg' succeeded
CRS-2677: Stop of 'ora.OCRVOTE1.dg' on 'misdg' succeeded
CRS-2677: Stop of 'ora.REDO03.dg' on 'misdg' succeeded
CRS-2677: Stop of 'ora.REDO02.dg' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'misdg'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'misdg'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.misdg.vip' on 'misdg'
CRS-2677: Stop of 'ora.scan1.vip' on 'misdg' succeeded
CRS-2677: Stop of 'ora.asm' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'misdg'
CRS-2677: Stop of 'ora.misdg.vip' on 'misdg' succeeded
CRS-2677: Stop of 'ora.qosmserver' on 'misdg' succeeded
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'misdg'
CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'misdg' succeeded
CRS-33677: Stop of resource group 'ora.asmgroup' on server 'misdg' succeeded.
CRS-2673: Attempting to stop 'ora.ons' on 'misdg'
CRS-2677: Stop of 'ora.ons' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'misdg'
CRS-2677: Stop of 'ora.net1.network' on 'misdg' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'misdg' has completed
CRS-2677: Stop of 'ora.crsd' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.storage' on 'misdg'
CRS-2673: Attempting to stop 'ora.crf' on 'misdg'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'misdg'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'misdg'
CRS-2677: Stop of 'ora.drivers.acfs' on 'misdg' succeeded
CRS-2677: Stop of 'ora.crf' on 'misdg' succeeded
CRS-2677: Stop of 'ora.storage' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'misdg'
CRS-2677: Stop of 'ora.mdnsd' on 'misdg' succeeded
CRS-2677: Stop of 'ora.asm' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'misdg'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'misdg'
CRS-2673: Attempting to stop 'ora.evmd' on 'misdg'
CRS-2677: Stop of 'ora.ctssd' on 'misdg' succeeded
CRS-2677: Stop of 'ora.evmd' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'misdg'
CRS-2677: Stop of 'ora.cssd' on 'misdg' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'misdg'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'misdg'
CRS-2677: Stop of 'ora.gipcd' on 'misdg' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'misdg' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'misdg' has completed
CRS-4133: Oracle High Availability Services has been stopped.

root@misdg:/d01/soft# /d01/app/19c/grid/bin/crsctl start has
CRS-4123: Oracle High Availability Services has been started.
root@misdg:/d01/soft#

root@misdg:/d01/soft# /d01/app/19c/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 2e091a5ac02d4f4ebfb44511c10221ec (/dev/rdsk/c0t6006016084734D00116EDB6817C70198d0s0) [OCRVOTE1]
Located 1 voting disk(s).
root@misdg:/d01/soft#

root@misdg:/d01/soft# /d01/app/19c/grid/bin/crsctl check cluster -all
**************************************************************
misdg:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
root@misdg:/d01/soft#

root@misdg:/d01/soft# /d01/app/19c/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 84292
Available space (kbytes) : 407392
ID : 1626978053
Device/File Name : +OCRVOTE
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
root@misdg:/d01/soft#

----------Move the OCR to new diskgroup.
/d01/soft# /d01/app/19c/grid/bin/ocrconfig -add +OCRVOTE1
root@misdg:/d01/soft# /d01/app/19c/grid/bin/ocrconfig -add +OCRVOTE1
root@misdg:/d01/soft# /d01/app/19c/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 84292
Available space (kbytes) : 407392
ID : 1626978053
Device/File Name : +OCRVOTE
Device/File integrity check succeeded
Device/File Name : +OCRVOTE1
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
root@misdg:/d01/soft#
--Connect with SQLPLUS ASM instance
col path for a30
col name for a20
col header_status for a20
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +OCRVOTE/misdg-cluster/ASMPARAMETERFILE/registry.253.1213202967
SQL>

asmcmd spmove '+OCRVOTE/misdg-cluster/ASMPARAMETERFILE/registry.253.1213202967' '+OCRVOTE1/misdg-cluster/spfileASM.ora'
-bash-5.2$ asmcmd spmove '+OCRVOTE/misdg-cluster/ASMPARAMETERFILE/registry.253.1213202967' '+OCRVOTE1/misdg-cluster/spfileASM.ora'
ORA-15032: not all alterations performed
ORA-15028: ASM file '+OCRVOTE/misdg-cluster/ASMPARAMETERFILE/registry.253.1213202967' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
-bash-5.2$
--Delete the old diskgroup
/d01/soft# /d01/app/19c/grid/bin/ocrconfig -delete +OCRVOTE
root@misdg:/d01/soft# /d01/app/19c/grid/bin/ocrconfig -delete +OCRVOTE
root@misdg:/d01/soft#
root@misdg:/d01/soft# /d01/app/19c/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 491684
Used space (kbytes) : 84292
Available space (kbytes) : 407392
ID : 1626978053
Device/File Name : +OCRVOTE1
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
root@misdg:/d01/soft#
---- Verify Move the SPFILE to newdiskgroup.
-bash-5.2$ asmcmd spget
+OCRVOTE1/misdg-cluster/spfileASM.ora
-bash-5.2$
reboot server
root@misdg:~# /d01/app/19c/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE misdg STABLE
ora.net1.network
ONLINE ONLINE misdg STABLE
ora.ons
ONLINE ONLINE misdg STABLE
ora.proxy_advm
OFFLINE OFFLINE misdg STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 ONLINE OFFLINE STABLE
3 ONLINE OFFLINE STABLE
ora.FRA.dg(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE misdg STABLE
ora.OCRVOTE1.dg(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.REDO01.dg(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.REDO02.dg(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.REDO03.dg(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE misdg Started,STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE misdg STABLE
2 OFFLINE OFFLINE STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE misdg STABLE
ora.misdg.vip
1 ONLINE ONLINE misdg STABLE
ora.qosmserver
1 ONLINE ONLINE misdg STABLE
ora.scan1.vip
1 ONLINE ONLINE misdg STABLE
--------------------------------------------------------------------------------
root@misdg:~#

Oracle 19c RAC Database Drop LUN from Liunx OS (2 Node RAC)

---Drop Disk From ASM----

ALTER DISKGROUP RECO DROP DISK
RECO_0006
REBALANCE POWER 11;

ALTER DISKGROUP RECO DROP DISK
RECO_0005
REBALANCE POWER 11;

ALTER DISKGROUP RECO DROP DISK
RECO_0004
REBALANCE POWER 11

--------Check ASM and Disks--------

SQL> set linesize 132

set pagesize 100

col OS_DISK for a60SQL> SQL>

SQL> select path "OS_DISK", OS_MB, total_mb, create_date, mount_date, mount_status, header_status "DISK TYPE" from v$asm_disk;

OS_DISK OS_MB TOTAL_MB CREATE_DA MOUNT_DAT MOUNT_S DISK TYPE

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

/dev/oracleasm/disks/RECO07 512000 0 04-JUL-24 10-MAR-25 CLOSED FORMER

/dev/oracleasm/disks/RECO06 512000 0 04-JUL-24 10-MAR-25 CLOSED FORMER

/dev/oracleasm/disks/RECO05 512000 0 04-JUL-24 10-MAR-25 CLOSED FORMER

/dev/oracleasm/disks/DATA20 512000 512000 10-FEB-25 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA19 512000 512000 06-FEB-25 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA16 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/RECO04 512000 512000 04-JUL-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA13 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA04 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA03 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA15 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA05 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA06 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA14 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA11 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA17 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA08 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA10 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA02 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA07 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA18 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA09 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA01 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA12 512000 512000 18-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/RECO02 512000 512000 13-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/OCRVOTING 204800 204800 12-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/RECO03 512000 512000 13-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/RECO01 512000 512000 13-FEB-24 10-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA21 512000 512000 15-MAR-25 15-MAR-25 CACHED MEMBER

/dev/oracleasm/disks/DATA22 512000 512000 26-MAY-25 26-MAY-25 CACHED MEMBER

30 rows selected.

SQL> /

[grid@baprdg01 ~]$ asmcmd lsdsk -p -G RECO

Group_Num Disk_Num Incarn Mount_Stat Header_Stat Mode_Stat State Path

3 0 4041325915 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO01

3 1 4041325914 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO02

3 2 4041325913 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO03

3 3 4041325912 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO04

[grid@baprdg01 ~]$

[grid@baprdg02 ~]$ asmcmd lsdsk -p -G RECO

Group_Num Disk_Num Incarn Mount_Stat Header_Stat Mode_Stat State Path

3 0 4042146510 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO01

3 1 4042146508 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO02

3 2 4042146509 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO03

3 3 4042146507 CACHED MEMBER ONLINE NORMAL /dev/oracleasm/disks/RECO04

[grid@baprdg02 ~]$

[root@baprdg02 ~]# blkid | grep RECO07

/dev/sdav: LABEL="RECO07" TYPE="oracleasm"

/dev/sdba: LABEL="RECO07" TYPE="oracleasm"

/dev/mapper/mpathy: LABEL="RECO07" TYPE="oracleasm"

/dev/sdcg: LABEL="RECO07" TYPE="oracleasm"

/dev/sddi: LABEL="RECO07" TYPE="oracleasm"

[root@baprdg02 ~]#

[root@baprdg02 ~]# blkid | grep RECO07

/dev/sdav: LABEL="RECO07" TYPE="oracleasm"

/dev/sdba: LABEL="RECO07" TYPE="oracleasm"

/dev/mapper/mpathy: LABEL="RECO07" TYPE="oracleasm"

/dev/sdcg: LABEL="RECO07" TYPE="oracleasm"

/dev/sddi: LABEL="RECO07" TYPE="oracleasm"

[root@baprdg02 ~]# multipath -ll | grep mpathy

mpathy (36006016082734d006b398666a7a5f433) dm-19 DGC,VRAID

[root@baprdg02 ~]# blkid | grep RECO06

/dev/sdax: LABEL="RECO06" TYPE="oracleasm"

/dev/sdbc: LABEL="RECO06" TYPE="oracleasm"

/dev/mapper/mpathz: LABEL="RECO06" TYPE="oracleasm"

/dev/sdci: LABEL="RECO06" TYPE="oracleasm"

/dev/sddj: LABEL="RECO06" TYPE="oracleasm"

[root@baprdg02 ~]# multipath -ll | grep mpathz

mpathz (36006016082734d006b398666b750b455) dm-23 DGC,VRAID

[root@baprdg02 ~]# blkid | grep RECO05

/dev/sdaz: LABEL="RECO05" TYPE="oracleasm"

/dev/sdbe: LABEL="RECO05" TYPE="oracleasm"

/dev/mapper/mpathaa: LABEL="RECO05" TYPE="oracleasm"

/dev/sdck: LABEL="RECO05" TYPE="oracleasm"

/dev/sddk: LABEL="RECO05" TYPE="oracleasm"

[root@baprdg02 ~]# multipath -ll | grep mpathaa

mpathaa (36006016082734d006c398666fb34c371) dm-18 DGC,VRAID

[root@baprdg02 ~]#

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

[root@baprdg02 ~]# blkid | grep RECO07

/dev/sdav: LABEL="RECO07" TYPE="oracleasm"

/dev/sdba: LABEL="RECO07" TYPE="oracleasm"

/dev/mapper/mpathy: LABEL="RECO07" TYPE="oracleasm"

/dev/sdcg: LABEL="RECO07" TYPE="oracleasm"

/dev/sddi: LABEL="RECO07" TYPE="oracleasm"

[root@baprdg02 ~]#

[root@baprdg01 ~]# dd if=/dev/zero of=/dev/mapper/mpathcz bs=1024 count=100

100+0 records in

100+0 records out

102400 bytes (102 kB, 100 KiB) copied, 0.00628071 s, 16.3 MB/s

[root@baprdg01 ~]# partprobe /dev/mapper/mpathcz

[root@baprdg01 ~]# oracleasm deletedisk -v RECO07

Disk "RECO07" defines an unmarked device

Dropping disk: done

[root@baprdg01 ~]#

[root@baprdg01 ~]# blkid | grep RECO07

/dev/sdax: LABEL="RECO07" TYPE="oracleasm"

/dev/sday: LABEL="RECO07" TYPE="oracleasm"

/dev/sdci: LABEL="RECO07" TYPE="oracleasm"

/dev/sddi: LABEL="RECO07" TYPE="oracleasm"

[root@baprdg01 ~]#

---------Node-2----------

[grid@baprdg02 ~]$ blkid | grep RECO07

/dev/sdav: LABEL="RECO07" TYPE="oracleasm"

/dev/sdba: LABEL="RECO07" TYPE="oracleasm"

/dev/mapper/mpathy: LABEL="RECO07" TYPE="oracleasm"

/dev/sdcg: LABEL="RECO07" TYPE="oracleasm"

/dev/sddi: LABEL="RECO07" TYPE="oracleasm"

[grid@baprdg02 ~]$

[root@baprdg02 ~]# blkid | grep RECO06

/dev/sdax: LABEL="RECO06" TYPE="oracleasm"

/dev/sdbc: LABEL="RECO06" TYPE="oracleasm"

/dev/mapper/mpathz: LABEL="RECO06" TYPE="oracleasm"

/dev/sdci: LABEL="RECO06" TYPE="oracleasm"

/dev/sddj: LABEL="RECO06" TYPE="oracleasm"

[root@baprdg02 ~]#

[root@baprdg02 ~]# multipath -ll | grep mpathz

mpathz (36006016082734d006b398666b750b455) dm-23 DGC,VRAID

[root@baprdg02 ~]#

dd if=/dev/zero of=/dev/mapper/mpathz bs=1024 count=100

[root@baprdg02 ~]# dd if=/dev/zero of=/dev/mapper/mpathz bs=1024 count=100

100+0 records in

100+0 records out

102400 bytes (102 kB, 100 KiB) copied, 0.00748744 s, 13.7 MB/s

[root@baprdg02 ~]#

partprobe /dev/mapper/mpathz

[root@baprdg02 ~]# partprobe /dev/mapper/mpathz

[root@baprdg02 ~]#

oracleasm deletedisk -v RECO07

[root@baprdg02 ~]# oracleasm deletedisk -v RECO07

Disk "RECO07" defines an unmarked device

Dropping disk: done

[root@baprdg02 ~]#

[root@baprdg02 ~]# oracleasm deletedisk -v RECO06

Disk "RECO06" defines an unmarked device

Dropping disk: done

[root@baprdg02 ~]#

[root@baprdg02 ~]# blkid | grep RECO06

/dev/sdax: LABEL="RECO06" TYPE="oracleasm"

/dev/sdbc: LABEL="RECO06" TYPE="oracleasm"

/dev/sdci: LABEL="RECO06" TYPE="oracleasm"

/dev/sddj: LABEL="RECO06" TYPE="oracleasm"

[root@baprdg02 ~]

[root@baprdg02 ~]# blkid | grep RECO05

/dev/sdaz: LABEL="RECO05" TYPE="oracleasm"

/dev/sdbe: LABEL="RECO05" TYPE="oracleasm"

/dev/mapper/mpathaa: LABEL="RECO05" TYPE="oracleasm"

/dev/sdck: LABEL="RECO05" TYPE="oracleasm"

/dev/sddk: LABEL="RECO05" TYPE="oracleasm"

[root@baprdg02 ~]#

[root@baprdg02 ~]# multipath -ll | grep mpathaa

mpathaa (36006016082734d006c398666fb34c371) dm-18 DGC,VRAID

[root@baprdg02 ~]#

dd if=/dev/zero of=/dev/mapper/mpathaa bs=1024 count=100

partprobe /dev/mapper/mpathaa

oracleasm deletedisk -v RECO05

[root@baprdg02 ~]# dd if=/dev/zero of=/dev/mapper/mpathaa bs=1024 count=100

100+0 records in

100+0 records out

102400 bytes (102 kB, 100 KiB) copied, 0.00554351 s, 18.5 MB/s

[root@baprdg02 ~]# partprobe /dev/mapper/mpathaa

[root@baprdg02 ~]# oracleasm deletedisk -v RECO05

Disk "RECO05" defines an unmarked device

Dropping disk: done

[root@baprdg02 ~]#

Wednesday, 9 October 2024

step by step Oracle 12c Data Guard Switchover

Step:1 In Primary database check the database role and open_mode

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
BACHDB READ WRITE PRIMARY

SQL>

Step:2 In standby database check the database role and open_mode

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
BACHDB READ ONLY WITH APPLY PHYSICAL STANDBY

Step:2 Check archive log gap sequence

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Step 3: Check switchover status in primary database

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL>

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

Step 4: On Primary database:-

SQL> alter database commit to switchover to standby;

Step 5 On old Primary database will become now standby

shutdown immediate

startup nomount

alter database mount standby database

Step 6:-select name,open_mode,database_role from v$database;

Remove Instance from InnoDb cluster in MYSQL

[root@nikash-db2 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
15.68.13.86        nikash-db1
15.68.13.87        nikash-db2
15.68.13.88        nikash-db3
15.68.13.89     nikash-db4
[root@nikash-db2 ~]#

mysql> select member_host,member_role from performance_schema.replication_group_members;
+-------------+-------------+
| member_host | member_role |
+-------------+-------------+
| nikash-db2 | SECONDARY   |
| nikash-db4 | PRIMARY     |
| nikash-db3 | SECONDARY   |
+-------------+-------------+
3 rows in set (0.00 sec)

mysql>

[root@nikash-db2 ~]# mysql --user root --password=Bankasia#2020 --host 10.88.13.87 --port 4407 nikash_np_eft
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 112997
Server version: 8.0.31-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| nikash-db2 |
+------------+
1 row in set (0.01 sec)

[root@nikash-db2 ~]# mysqlsh
MySQL Shell 8.0.31

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > root@nikash-db2
SyntaxError: Invalid or unexpected token
MySQL JS > \c root@nikash-db2
Creating a session to 'root@nikash-db2'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 111803 (X protocol)
Server version: 8.0.31-cluster MySQL Cluster Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL nikash-db2:33060+ ssl JS > c.status()
ReferenceError: c is not defined
MySQL nikash-db2:33060+ ssl JS > var c=dba.getCluster()
MySQL nikash-db2:33060+ ssl JS > c.status()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "nikash-db4:4407",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "nikash-db2:4407": {
                "address": "nikash-db2:4407",
                "instanceErrors": [
                    "WARNING: Instance is NOT a PRIMARY but super_read_only option is OFF."
                ],
                "memberRole": "SECONDARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            },
            "nikash-db3:4407": {
                "address": "nikash-db3:4407",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            },
            "nikash-db4:4407": {
                "address": "nikash-db4:4407",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "nikash-db4:4407"
}
MySQL nikash-db2:33060+ ssl JS >

MySQL nikash-db2:33060+ ssl JS > c.describe()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "nikash-db2:4407",
                "label": "nikash-db2:4407",
                "role": "HA"
            },
            {
                "address": "nikash-db3:4407",
                "label": "nikash-db3:4407",
                "role": "HA"
            },
            {
                "address": "nikash-db4:4407",
                "label": "nikash-db4:4407",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}
MySQL nikash-db2:33060+ ssl JS >

MySQL nikash-db2:33060+ ssl JS > c.listRouters();
{
    "clusterName": "nikash_db_cluster",
    "routers": {
        "nikash-db2::system": {
            "hostname": "nikash-db2",
            "lastCheckIn": "2024-10-09 14:25:48",
            "roPort": "6447",
            "roXPort": "6449",
            "rwPort": "6446",
            "rwXPort": "6448",
            "version": "8.0.31"
        }
    }
}
MySQL nikash-db2:33060+ ssl JS >

----------------------Remove INSTANCE from InnoDB cluster------------------

MySQL nikash-db2:33060+ ssl JS > c.removeInstance('nikash-db4:4407')
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

* Waiting for instance 'nikash-db4:4407' to synchronize with the primary...
** Transactions replicated ############################################################ 100%

* Instance 'nikash-db4:4407' is attempting to leave the cluster...

The instance 'nikash-db4:4407' was successfully removed from the cluster.

MySQL nikash-db2:33060+ ssl JS >

--------------------Now check Cluster Group Member-------------

mysql> select member_host,member_role from performance_schema.replication_group_members;
+-------------+-------------+
| member_host | member_role |
+-------------+-------------+
| nikash-db2 | PRIMARY     |
| nikash-db3 | SECONDARY   |
+-------------+-------------+
2 rows in set (0.00 sec)

mysql>

MySQL nikash-db2:33060+ ssl JS > c.removeInstance('nikash-db3:4407')
The instance will be removed from the InnoDB cluster. Depending on the instance
being the Seed or not, the Metadata session might become invalid. If so, please
start a new session to the Metadata Storage R/W instance.

* Waiting for instance 'nikash-db3:4407' to synchronize with the primary...
** Transactions replicated ############################################################ 100%

* Instance 'nikash-db3:4407' is attempting to leave the cluster...

The instance 'nikash-db3:4407' was successfully removed from the cluster.

MySQL nikash-db2:33060+ ssl JS >

mysql> select member_host,member_role from performance_schema.replication_group_members;
+-------------+-------------+
| member_host | member_role |
+-------------+-------------+
| nikash-db2 | PRIMARY     |
+-------------+-------------+
1 row in set (0.00 sec)

mysql>

MySQL nikash-db2:33060+ ssl JS > c.status()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "nikash-db2:4407",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures.",
        "topology": {
            "nikash-db2:4407": {
                "address": "nikash-db2:4407",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.31"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "nikash-db2:4407"
}
MySQL nikash-db2:33060+ ssl JS >

MySQL nikash-db2:33060+ ssl JS > c.describe()
{
    "clusterName": "nikash_db_cluster",
    "defaultReplicaSet": {
        "name": "default",
        "topology": [
            {
                "address": "nikash-db2:4407",
                "label": "nikash-db2:4407",
                "role": "HA"
            }
        ],
        "topologyMode": "Single-Primary"
    }
}
MySQL nikash-db2:33060+ ssl JS >

Tuesday, 8 October 2024

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 32.

Import: Release 19.0.0.0.0 - Production on Wed Oct 9 09:11:46 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 41 into a target database with TSTZ version 32.

-bash-4.2$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 9 09:17:49 2024
Version 19.21.0.0.0

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat          32      0

SQL>

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2    3    4

PROPERTY_NAME
--------------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
32

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE

SQL>

-bash-4.2$ ls -lrt
total 0
drwxr-xr-x 4 oracle oinstall 48 Oct 9 09:28 35099667
-bash-4.2$ cd 35099667/
-bash-4.2$ pwd
/u01/soft/patch/35099667
-bash-4.2$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.43
Copyright (c) 2024, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.43
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-10-09_09-30-29AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
-bash-4.2$

-bash-4.2$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.43
Copyright (c) 2024, Oracle Corporation. All rights reserved.

Oracle Home       : /u01/app/oracle/product/19.0.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.43
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-10-09_09-34-45AM_1.log

Verifying environment and performing prerequisite checks...

Conflicts/Supersets for each patch are:

Patch : 35099667

    Bug SubSet of 35643107
    Subset bugs are:
    35099667

OPatch found that the following patch(es) are not required.
They are either subset of the patches in Oracle Home (or) subset of the patches in the given list:
[ 35099667 ]

Log file location: /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-10-09_09-34-45AM_1.log

OPatch succeeded.
-bash-4.2$

The normal process to upgrade timezone fixes this
@?/rdbms/admin/utltz_countstats.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_42.dat          42      0

1 row selected.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY NO
     3 PDB                  MOUNTED
     4 STLBAS              MOUNTED
SQL> alter pluggable database STLBAS open;

Pluggable database altered.

SQL>

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY NO
     3 PDB                  MOUNTED
     4 STLBAS              READ WRITE NO

SQL> alter session set container=stlbas;

Session altered.

SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat          32      0

The normal process to upgrade timezone fixes this
@?/rdbms/admin/utltz_countstats.sql
@?/rdbms/admin/utltz_upg_check.sql
@?/rdbms/admin/utltz_upg_apply.sql

SQL> @?/rdbms/admin/utltz_countstats.sql

Session altered.

.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
.
.

SQL> @?/rdbms/admin/utltz_upg_check.sql

Session altered.

INFO: Starting with RDBMS DST update preparation.
.
.
.
.

SQL> @?/rdbms/admin/utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv42 .
INFO: This database
.
.
.

Session altered.

SQL> show pdbs

    CON_ID CON_NAME              OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
     4 STLBAS              READ WRITE NO
SQL> select * from v$timezone_file;

FILENAME        VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_42.dat          42      0

1 row selected.

SQL>

Monday, 3 June 2024

ORA-01422: exact fetch returns more than requested number of rows in Oracle OEM 13

We are getting the following error when we want to add exadata database instances in Oracle OEM 13
Changes not saved.
bacoredb_STLBAS : ORA-01422: exact fetch returns more than requested number of rows

The error is seen due to duplicate targets that were existing in the repository.
Run the following query as SYSMAN user on the repository database to confirm if duplicate targets are present in the repository:

SQL> ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;
SQL> column ENTITY_TYPE format a20
SQL> column ENTITY_NAME format a37
SQL> set lin 200
SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database      bacoredb_bacoredb1                    2          3 01-NOV-23
oracle_pdb         bacoredb_CDBROOT                       2          3 01-NOV-23
oracle_pdb         bacoredb_EMOB                       2          3 01-NOV-23
oracle_pdb         bacoredb_PDB1                       2          3 01-NOV-23
oracle_pdb         bacoredb_STLBAS                       2          3 01-NOV-23
oracle_pdb         bacoredb_HIKMAH                       2          1 29-DEC-23
oracle_pdb         bacoredb_ETESORO                       2          1 02-JAN-24
oracle_dbsys         bacoredb_sys                       2          3 01-NOV-23
oracle_database      bacoredb_bacoredb2_1                   1          1 09-NOV-23
rac_database         bacoredb_1                        1          1 09-NOV-23
oracle_database      bacoredb_bacoredb2                    2          3 02-NOV-23

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
rac_database         bacoredb                           2          3 01-NOV-23

12 rows selected.

SQL>

From OMS server,

$OMS_HOME/bin/emcli login -username=SYSMAN
$OMS_HOME/bin/emcli sync
$OMS_HOME/bin/emcli delete_target -name="bacoredb_STLBAS" -type="oracle_database" -delete_monitored_targets

[oracle@em13c ~]$ /u01/app/oem/bin/emcli login -username=SYSMAN
Enter password :

Login successful
[oracle@em13c ~]$ /u01/app/oem/bin/emcli sync
Synchronized successfully
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_STLBAS" -type="oracle_database" -delete_monitored_targets
Error: Target does not exist
[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_bacoredb2_1" -type="oracle_database" -delete_monitored_targets
Target "bacoredb_bacoredb2_1:oracle_database" deleted successfully
[oracle@em13c ~]$

SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database      bacoredb_bacoredb1                    2          3 01-NOV-23
oracle_pdb         bacoredb_CDBROOT                       2          3 01-NOV-23
oracle_pdb         bacoredb_EMOB                       2          3 01-NOV-23
oracle_pdb         bacoredb_PDB1                       2          3 01-NOV-23
oracle_pdb         bacoredb_STLBAS                       2          3 01-NOV-23
oracle_pdb         bacoredb_HIKMAH                       2          1 29-DEC-23
oracle_pdb         bacoredb_ETESORO                       2          1 02-JAN-24
oracle_dbsys         bacoredb_sys                       2          3 01-NOV-23
rac_database         bacoredb_1                        1          1 09-NOV-23
oracle_database      bacoredb_bacoredb2                    2          3 02-NOV-23
rac_database         bacoredb                           2          3 01-NOV-23

11 rows selected.

SQL>

[oracle@em13c ~]$ /u01/app/oem/bin/emcli delete_target -name="bacoredb_1" -type="rac_database" -delete_monitored_targets
Target "bacoredb_1:rac_database" deleted successfully
[oracle@em13c ~]$

SQL> select entity_type,entity_name,manage_status,promote_status,LAST_UPDATED_TIME from EM_MANAGEABLE_ENTITIES where entity_name like '%bacoredb%';

ENTITY_TYPE         ENTITY_NAME               MANAGE_STATUS PROMOTE_STATUS LAST_UPDA
-------------------- ------------------------------------- ------------- -------------- ---------
oracle_database      bacoredb_bacoredb1                    2          3 01-NOV-23
oracle_pdb         bacoredb_CDBROOT                       2          3 01-NOV-23
oracle_pdb         bacoredb_EMOB                       2          3 01-NOV-23
oracle_pdb         bacoredb_PDB1                       2          3 01-NOV-23
oracle_pdb         bacoredb_STLBAS                       2          3 01-NOV-23
oracle_pdb         bacoredb_HIKMAH                       2          1 29-DEC-23
oracle_pdb         bacoredb_ETESORO                       2          1 02-JAN-24
oracle_dbsys         bacoredb_sys                       2          3 01-NOV-23
oracle_database      bacoredb_bacoredb2                    2          3 02-NOV-23
rac_database         bacoredb                           2          3 01-NOV-23

10 rows selected.

SQL>

Ref
EM12c, EM13c: How to Delete a Target in Enterprise Manager Cloud Control ( Doc ID 1905181.1 )

Monday, 13 May 2024

Reread (file 327, block 339794) found same corrupt data (no logical check) ---Fixing Corrupt Blocks

Fixing Corrupt Blocks with the FIX_CORRUPT_BLOCKS Procedure

------------Error From Alert Log--------------

Hex dump of (file 327, block 339794) in trace file /u01/app/oracle/diag/rdbms/ncbsstd/baappcdb/trace/baappcdb_ora_75038.trc

Corrupt block relative dba: 0x51c52f52 (file 327, block 339794)
Fractured block found during multiblock buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x51c52f52
last change scn: 0x1eaf.7467dde7 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x1adc0601
check value in block header: 0x3201
computed block checksum: 0x6b71

Reading datafile '/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf' for corruption at rdba: 0x51c52f52 (file 327, block 339794)
Reread (file 327, block 339794) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 327, block# 339794)
Automatic block media recovery failed for (file# 327, block# 339794)
[request flood controlled]
Mon May 13 15:00:07 2024
Corrupt Block Found
         CONT = 7, TSN = 4, TSNAME = IBANKTBS
         RFN = 327, BLK = 339794, RDBA = 1371877202
         OBJN = 92301, OBJD = 92301, OBJECT = STMAILID, SUBOBJECT =
         SEGMENT OWNER = IBANKING, SEGMENT TYPE = Table Segment
Mon May 13 15:00:08 2024
Hex dump of (file 327, block 339794) in trace file /u01/app/oracle/diag/rdbms/ncbsstd/baappcdb/trace/baappcdb_ora_178931.trc

Corrupt block relative dba: 0x51c52f52 (file 327, block 339794)
Fractured block found during multiblock buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x51c52f52
last change scn: 0x1eaf.7467dde7 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x1adc0601
check value in block header: 0x3201
computed block checksum: 0x6b71

------------Diagnosis-----------

SQL> create table IBANKING.STMAILID_13052024 as select * from IBANKING.STMAILID;
create table IBANKING.STMAILID_13052024 as select * from IBANKING.STMAILID
                                                                  *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 327, block # 339794)
ORA-01110: data file 327:
'/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf'

SQL> ALTER TABLE IBANKING.STMAILID
DROP PRIMARY KEY CASCADE; 2

Table altered.

ALTER TABLE IBANKING.STMAILID ADD (
CONSTRAINT PK_STMAILID
PRIMARY KEY
(MAILID)
USING INDEX IBANKING.PK_STMAILID
ENABLE VALIDATE);
SQL>   2    3    4    5    6 ALTER TABLE IBANKING.STMAILID ADD (
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 327, block # 339794)
ORA-01110: data file 327:
'/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf'

SQL> analyze table IBANKING.STMAILID validate structure cascade online;
analyze table IBANKING.STMAILID validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 327, block # 339794)
ORA-01110: data file 327:
'/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf'

-----------Execute RMAN command to check corruption-----------

[oracle@ncbsstd ~]$ rman target/

Recovery Manager: Release 12.1.0.2.0 - Production on Mon May 13 14:49:50 2024

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: BAAPPCDB (DBID=4236564156)

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
83022      HIGH     OPEN      13-MAY-24     Datafile 327: '/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf' contains one or more corrupt blocks

RMAN>

RMAN>

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
83022      HIGH     OPEN      13-MAY-24     Datafile 327: '/u17/app/oracle/oradata/baappcdb/BAIBNKDB/ibankdata04.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10462 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. No backup of block 339794 in file 327 was found. Drop and re-create the associated object (if possible), or use the DBMS_REPAIR package to repair the block corruption
2. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
1. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. Then perform a Data Guard role change (failover). Available standbys: ncbsdg.

Automated Repair Options
========================
no automatic repair options available

RMAN>

SQL>
SQL>
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
     TABLE_NAME => 'REPAIR_TABLE',
     TABLE_TYPE => dbms_repair.repair_table,
     ACTION     => dbms_repair.create_action,
     TABLESPACE => 'USERS');
END;
/
2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
DECLARE num_fix INT;
SQL>   2 BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
     SCHEMA_NAME => 'IBANKING',
     OBJECT_NAME=> 'STMAILID',
     OBJECT_TYPE => dbms_repair.table_object,
     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
     FIX_COUNT=> num_fix);
3    4    5    6    7    8    9   10 DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
END;
/ 11   12
num fix: 0

PL/SQL procedure successfully completed.

SQL> SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
     FROM REPAIR_TABLE; 2

no rows selected

SQL> CREATE UNIQUE INDEX IBANKING.PK_STMAILID ON IBANKING.STMAILID
2 (MAILID)
NOLOGGING
TABLESPACE IBANKTBS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
PARALLEL ( DEGREE 63 INSTANCES 1 ); 3    4    5    6    7    8    9   10   11   12   13   14   15   16

Index created.

SQL> ALTER TABLE IBANKING.STMAILID ADD (
CONSTRAINT PK_STMAILID
PRIMARY KEY
(MAILID)
USING INDEX IBANKING.PK_STMAILID
ENABLE VALIDATE); 2    3    4    5    6

Table altered.

SQL> create table IBANKING.STMAILID_13052024 as select * from IBANKING.STMAILID;

Table created.

SQL> select count(1) from IBANKING.STMAILID;

COUNT(1)
----------
    164784

SQL> select count(1) from IBANKING.STMAILID_13052024;

COUNT(1)
----------
    164784

SQL>

Reference: https://docs.oracle.com/cd/B13789_01/server.101/b10739/repair.htm

Wednesday, 10 January 2024

Step by step how to relocate an ACFS filesystem to another Diskgroup in Exadata

Step 1) Backup all the files and directories contains on ACFS

Step 2) Obtain the information about the current ACFS filesystem and ADVM volume:

opc@said-dbvm01 ~]$ sudo su -
Last login: Thu Jan 11 08:31:39 +06 2024 on pts/1
Last login: Thu Jan 11 08:46:30 +06 2024 on pts/0
[root@said-dbvm01 ~]# su - grid
Last login: Thu Jan 11 08:43:44 +06 2024
Last login: Thu Jan 11 08:46:33 +06 2024 on pts/0
[grid@said-dbvm01 ~]$ asmcmd
ASMCMD> volinfo --all
Diskgroup Name: DATAC1

         Volume Name: ACFSVOL01
         Volume Device: /dev/asm/acfsvol01-390
         State: ENABLED
         Size (MB): 1150976
         Resize Unit (MB): 64
         Redundancy: HIGH
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /acfs01

ASMCMD>

[grid@said-dbvm01 ~]$ df -m /acfs01/
Filesystem             1M-blocks Used Available Use% Mounted on
/dev/asm/acfsvol01-390   1150976 8427   1142550   1% /acfs01
[grid@said-dbvm01 ~]$

3) Stop and dismount the associated filesystem clusterwide as follows (as root user):

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.ACFSVOL01.advm
               ONLINE ONLINE       dr01v-dbvm01             STABLE
               ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.LISTENER.lsnr
               ONLINE ONLINE       dr01v-dbvm01             STABLE
               ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.chad
               ONLINE ONLINE       dr01v-dbvm01             STABLE
               ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.datac1.acfsvol01.acfs
               ONLINE ONLINE       dr01v-dbvm01             mounted on /acfs01,S
                                                             TABLE
               ONLINE ONLINE       dr01v-dbvm02             mounted on /acfs01,S
                                                             TABLE
ora.net1.network
               ONLINE ONLINE       dr01v-dbvm01             STABLE
               ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.ons
               ONLINE ONLINE       dr01v-dbvm01             STABLE
               ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.proxy_advm
               ONLINE ONLINE       dr01v-dbvm01             STABLE
               ONLINE ONLINE       dr01v-dbvm02             STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
      2        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.DATAC1.dg(ora.asmgroup)
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
      2        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
ora.RECOC1.dg(ora.asmgroup)
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
      2        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.asm(ora.asmgroup)
      1        ONLINE ONLINE       dr01v-dbvm01             Started,STABLE
      2        ONLINE ONLINE       dr01v-dbvm02             Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
      2        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.badgcc.bacoredb_pdb1.paas.oracle.com.svc
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
ora.badgcc.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.cvu
      1        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.dr01v-dbvm01.vip
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
ora.dr01v-dbvm02.vip
      1        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.qosmserver
      1        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.scan1.vip
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
ora.scan2.vip
      1        ONLINE ONLINE       dr01v-dbvm02             STABLE
ora.scan3.vip
      1        ONLINE ONLINE       dr01v-dbvm01             STABLE
--------------------------------------------------------------------------------
[root@dr01v-dbvm01 ~]#

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/srvctl status filesystem -d /dev/asm/acfsvol01-390
ACFS file system /acfs01 is mounted on nodes dr01v-dbvm01,dr01v-dbvm02
[root@said-dbvm01 ~]#

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/srvctl stop filesystem -d /dev/asm/acfsvol01-390

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/srvctl status filesystem -d /dev/asm/acfsvol01-390
ACFS file system /acfs01 is not mounted
[root@said-dbvm01 ~]#

Step 4) Then disable the associated volume in every node:

[grid@said-dbvm01 ~]$ asmcmd voldisable -G DATAC1 ACFSVOL01
[grid@said-dbvm01 ~]$

[opc@said-dbvm02 ~]$ sudo su -
Last login: Thu Jan 11 08:49:21 +06 2024
Last login: Thu Jan 11 08:56:44 +06 2024 on pts/0
[root@said-dbvm02 ~]# su - grid
Last login: Thu Jan 11 08:49:21 +06 2024
Last login: Thu Jan 11 08:56:48 +06 2024 on pts/0
[grid@said-dbvm02 ~]$ asmcmd voldisable -G DATAC1 ACFSVOL01
[grid@said-dbvm02 ~]$

Step 5) After the volume is disabled in every node, it can be deleted from node #1 as follows:

[grid@said-dbvm01 ~]$ asmcmd voldelete -G DATAC1 ACFSVOL01
[grid@said-dbvm01 ~]$

[grid@said-dbvm02 ~]$ asmcmd voldelete -G DATAC1 ACFSVOL01
ORA-15032: not all alterations performed
ORA-15466: volume 'ACFSVOL01' in disk group 'DATAC1' does not exist (DBD ERROR: OCIStmtExecute)
[grid@said-dbvm02 ~]$

Step 6) Remove the associated ACFS and ADVM CRS resources as follows (as root user from node #1):

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/srvctl remove filesystem -d /dev/asm/acfsvol01-390 -force
PRCT-1011 : Failed to run "advmutil". Detailed error: advmutil: ADVM-03168: Internal error: clscrs_res_get_attr(). attrName=CANONICAL_VOLUME_DEVICE 4,advmutil: ADVM-03180: Unable to obtain ASM volume device information for '/dev/asm/acfsvol01-390'

Step 7) Recreate the ADVM volume in the new diskgroup (e.g. ACFSDG):

SQL> ALTER DISKGROUP ACFSDG ADD VOLUME ACFSVOL SIZE 600G;

[grid@said-dbvm01 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 11 09:03:25 2024
Version 19.20.0.0.0

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

Enter user-name: /as sysasm

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0

SQL> ALTER DISKGROUP RECOC1 ADD VOLUME ACFSVOL01 SIZE 600G;

Diskgroup altered.

SQL>

[grid@said-dbvm01 ~]$ asmcmd volinfo --all
Diskgroup Name: RECOC1

         Volume Name: ACFSVOL01
         Volume Device: /dev/asm/acfsvol01-178
         State: ENABLED
         Size (MB): 614400
         Resize Unit (MB): 64
         Redundancy: HIGH
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage:
         Mountpath:

[grid@said-dbvm01 ~]$

Step 9) Create an ACFS filesystem in the new volume:

$> /sbin/mkfs -t acfs /dev/asm/acfsvol01-178

[root@dr01v-dbvm01 ~]# /sbin/mkfs -t acfs /dev/asm/acfsvol01-178
mkfs.acfs: version                   = 19.0.0.0.0
mkfs.acfs: on-disk version           = 46.0
mkfs.acfs: volume                    = /dev/asm/acfsvol01-178
mkfs.acfs: volume size               = 644245094400 ( 600.00 GB )
mkfs.acfs: Format complete.
[root@dr01v-dbvm01 ~]#

Step 10) Create the CRS resource associated to the new ACFS filesystem as follows (as root user from node #1):

# srvctl add filesystem -d /dev/asm/acfsvol-286 -g 'ACFSDG' -v ACFSVOL -m /goldengate -u grid

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/srvctl add filesystem -d /dev/asm/acfsvol01-178 -g 'RECOC1' -v ACFSVOL01 -m /acfs01 -u grid
[root@dr01v-dbvm01 ~]#

Step 11) Finally, start and mount the filesystem as follows (as root user from node #1):

# srvctl start filesystem -d /dev/asm/acfsvol-286

[root@said-dbvm01 ~]# /u01/app/19.0.0.0/grid/bin/srvctl start filesystem -d /dev/asm/acfsvol01-178
[root@said-dbvm01 ~]#

Step 12) Verify the new ACFS filesystem is mounted on all the nodes:

[root@said-dbvm01 ~]# df -m /acfs01/
Filesystem             1M-blocks Used Available Use% Mounted on
/dev/asm/acfsvol01-178    614400 1832    612569   1% /acfs01
[root@said-dbvm01 ~]#

[grid@said-dbvm02 ~]$ df -m /acfs01/
Filesystem             1M-blocks Used Available Use% Mounted on
/dev/asm/acfsvol01-178    614400 1832    612569   1% /acfs01
[grid@said-dbvm02 ~]$ df -h
Filesystem                                                Size Used Avail Use% Mounted on
devtmpfs                                                  252G     0 252G   0% /dev
tmpfs                                                     504G 209M 504G   1% /dev/shm
tmpfs                                                     252G   11M 252G   1% /run
tmpfs                                                     252G     0 252G   0% /sys/fs/cgroup
/dev/mapper/VGExaDb-LVDbSys1                               15G 7.7G 7.4G 51% /
/dev/sda1                                                 509M 118M 391M 24% /boot
/dev/mapper/VGExaDb-LVDbKdump                              20G   33M   20G   1% /crashfiles
/dev/mapper/VGExaDbDisk.u01.20.img-LVDBDisk                20G 1.9G   19G 10% /u01
/dev/mapper/VGExaDbDisk.grid19.0.0.0.230718.img-LVDBDisk   50G   12G   39G 23% /u01/app/19.0.0.0/grid
/dev/mapper/VGExaDb-LVDbVar1                              5.0G 1.8G 3.3G 36% /var
/dev/mapper/VGExaDbDisk.u02_extra.img-LVDBDisk             57G   15G   40G 27% /u02
/dev/mapper/VGExaDb-LVDbHome                              4.0G   45M 4.0G   2% /home
/dev/mapper/VGExaDb-LVDbTmp                               3.0G   33M 3.0G   2% /tmp
/dev/mapper/VGExaDb-LVDbVarLog                             18G 466M   18G   3% /var/log
/dev/mapper/VGExaDb-LVDbVarLogAudit                       3.0G 161M 2.9G   6% /var/log/audit
tmpfs                                                      51G     0   51G   0% /run/user/1001
tmpfs                                                      51G     0   51G   0% /run/user/1000
tmpfs                                                      51G     0   51G   0% /run/user/0
tmpfs                                                      51G     0   51G   0% /run/user/2000
/dev/asm/acfsvol01-178                                    600G 1.8G 599G   1% /acfs01
[grid@said-dbvm02 ~]$

13) Copy back the files and directories from your backup (transient filesystem) to your new ACFS filesystem (“/acfs01”).

Pages

Sunday, 5 October 2025

Wednesday, 9 October 2024

Tuesday, 8 October 2024

Monday, 3 June 2024

Monday, 13 May 2024

Wednesday, 10 January 2024