ASM - Online ASM disk migration 12.1.0.2.0 on 2 node RAC

 ASM - Online ASM disk migration 12.1.0.2.0 on 2 node RAC

• Login grid user to particular node and check current disk status

SQL> select path from v$asm_disk;

PATH

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

/dev/oracleasm/disks/CRSNEW

/dev/oracleasm/disks/DATANEW

/dev/oracleasm/disks/BACKUPNEW

/dev/oracleasm/disks/FRANEW

/dev/oracleasm/disks/FRA

/dev/oracleasm/disks/DATA

/dev/oracleasm/disks/CRS

/dev/oracleasm/disks/BACKUP

8 rows selected.

SQL> set pages 40000 lines 120

col PATH for a30

select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,

PATH FROM V$ASM_DISK;SQL> SQL>   2

DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH

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

          0 CLOSED  FORMER       ONLINE  NORMAL   /dev/oracleasm/disks/CRSNEW

          1 CLOSED  FORMER       ONLINE  NORMAL   /dev/oracleasm/disks/DATANEW

          2 CLOSED  FORMER       ONLINE  NORMAL   /dev/oracleasm/disks/BACKUPNEW

          3 CLOSED  FORMER       ONLINE  NORMAL   /dev/oracleasm/disks/FRANEW

          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/FRA

          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/DATA

          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/CRS

          0 CACHED  MEMBER       ONLINE  NORMAL   /dev/oracleasm/disks/BACKUP

8 rows selected.

• Add new diskgroup DATA  and  check rebalance status. By default asm power limit is 1

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATANEW';

Diskgroup altered.

SQL> Select operation, state, est_work, est_minutes from v$asm_operation;

OPERA STAT   EST_WORK EST_MINUTES

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

REBAL RUN        1137           0

REBAL WAIT          0           0

SQL> Select operation, state, est_work, est_minutes from v$asm_operation;

OPERA STAT   EST_WORK EST_MINUTES

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

REBAL RUN        1137           0

REBAL WAIT          0           0

SQL> /

OPERA STAT   EST_WORK EST_MINUTES

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

REBAL RUN        1137           0

REBAL WAIT          0           0

SQL> /

OPERA STAT   EST_WORK EST_MINUTES

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

REBAL RUN        1137           0

REBAL WAIT          0           0

SQL> /

OPERA STAT   EST_WORK EST_MINUTES

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

REBAL DONE       1137           0

REBAL RUN           0           0

SQL> /

no rows selected

SQL>

• Check asm alert log details :

[root@srv2 trace]# tail -1000f alert_+ASM2.log

Wed Mar 31 16:51:02 2021

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATANEW'

Wed Mar 31 16:51:02 2021

NOTE: GroupBlock outside rolling migration privileged region

NOTE: Assigning number (6,1) to disk (/dev/oracleasm/disks/DATANEW)

NOTE: requesting all-instance membership refresh for group=6

NOTE: Disk 1 in group 6 is assigned fgnum=2

NOTE: discarding redo for group 6 disk 1

NOTE: initializing header (replicated) on grp 6 disk DATA_0001

NOTE: initializing header on grp 6 disk DATA_0001

NOTE: requesting all-instance disk validation for group=6

Wed Mar 31 16:51:03 2021

NOTE: skipping rediscovery for group 6/0xb6b1c3c8 (DATA) on local instance.

Wed Mar 31 16:51:03 2021

NOTE: requesting all-instance disk validation for group=6

Wed Mar 31 16:51:03 2021

NOTE: skipping rediscovery for group 6/0xb6b1c3c8 (DATA) on local instance.

Wed Mar 31 16:51:03 2021

GMON updating for reconfiguration, group 6 at 33 for pid 30, osid 23425

Wed Mar 31 16:51:03 2021

NOTE: group 6 PST updated.

Wed Mar 31 16:51:03 2021

NOTE: membership refresh pending for group 6/0xb6b1c3c8 (DATA)

Wed Mar 31 16:51:03 2021

GMON querying group 6 at 34 for pid 22, osid 9992

NOTE: cache opening disk 1 of grp 6: DATA_0001 path:/dev/oracleasm/disks/DATANEW

Wed Mar 31 16:51:04 2021

NOTE: Attempting voting file refresh on diskgroup DATA

NOTE: Refresh completed on diskgroup DATA. No voting file found.

Wed Mar 31 16:51:04 2021

GMON querying group 6 at 35 for pid 22, osid 9992

Wed Mar 31 16:51:04 2021

SUCCESS: refreshed membership for 6/0xb6b1c3c8 (DATA)

Wed Mar 31 16:51:04 2021

SUCCESS: alter diskgroup DATA add disk '/dev/oracleasm/disks/DATANEW'

Wed Mar 31 16:51:04 2021

NOTE: starting rebalance of group 6/0xb6b1c3c8 (DATA) at power 1

Starting background process ARB0

Wed Mar 31 16:51:04 2021

ARB0 started with pid=34, OS id=11500

NOTE: assigning ARB0 to group 6/0xb6b1c3c8 (DATA) with 1 parallel I/O

Wed Mar 31 16:51:05 2021

NOTE: header on disk 0 advanced to format #2 using fcn 0.752

Wed Mar 31 16:51:10 2021

NOTE: Attempting voting file refresh on diskgroup DATA

NOTE: Refresh completed on diskgroup DATA. No voting file found.

Wed Mar 31 16:52:02 2021

NOTE: GroupBlock outside rolling migration privileged region

NOTE: requesting all-instance membership refresh for group=6

Wed Mar 31 16:52:02 2021

NOTE: membership refresh pending for group 6/0xb6b1c3c8 (DATA)

Wed Mar 31 16:52:02 2021

GMON querying group 6 at 36 for pid 22, osid 9992

Wed Mar 31 16:52:02 2021

SUCCESS: refreshed membership for 6/0xb6b1c3c8 (DATA)

NOTE: Attempting voting file refresh on diskgroup DATA

NOTE: Refresh completed on diskgroup DATA. No voting file found.

Wed Mar 31 16:52:49 2021

NOTE: stopping process ARB0

Wed Mar 31 16:52:50 2021

SUCCESS: rebalance completed for group 6/0xb6b1c3c8 (DATA)

• Login grid user and check the diskgroup user as we will delete diskgroup data

SQL> set lines 999;

col diskgroup for a10

col diskname for a12

col path for a30

select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,

b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

order by b.group_number,b.name;SQL> SQL> SQL> SQL>   2    3    4    5

DISKGROUP  DISKNAME       TOTAL_MB    USED_MB    FREE_MB PATH                           HEADER_STATU

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

                                 0          0          0 /dev/oracleasm/disks/CRSNEW    FORMER

                                 0          0          0 /dev/oracleasm/disks/BACKUPNEW FORMER

                                 0          0          0 /dev/oracleasm/disks/FRANEW    FORMER

BACKUP     BACKUP_0000       12284         96      12188 /dev/oracleasm/disks/BACKUP    MEMBER

CRS        CRS_0000          12284       4472       7812 /dev/oracleasm/disks/CRS       MEMBER

DATA       DATA_0000         20473       1144      19329 /dev/oracleasm/disks/DATA      MEMBER

DATA       DATA_0001         20473       1140      19333 /dev/oracleasm/disks/DATANEW   MEMBER

FRA        FRA_0000          20473        580      19893 /dev/oracleasm/disks/FRA       MEMBER

8 rows selected.

SQL> alter diskgroup DATA drop disk 'DATA_0000';

Diskgroup altered.

• Query to check ASM rebalance status

SQL> Select operation, state, est_work, est_minutes from v$asm_operation;

OPERA STAT   EST_WORK EST_MINUTES

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

REBAL RUN        1141           0

REBAL WAIT          0           0

SQL> /

no rows selected

SQL>

• ASM alert log details below:

[root@srv2 trace]# tail -1000f /u01/app/grid/diag/asm/+asm/+ASM2/trace/alert_+ASM2.log

Wed Mar 31 17:02:30 2021

SUCCESS: alter diskgroup DATA drop disk 'DATA_0000'

Wed Mar 31 17:02:30 2021

NOTE: starting rebalance of group 6/0xb6b1c3c8 (DATA) at power 1

Starting background process ARB0

Wed Mar 31 17:02:30 2021

ARB0 started with pid=34, OS id=15571

NOTE: assigning ARB0 to group 6/0xb6b1c3c8 (DATA) with 1 parallel I/O

Wed Mar 31 17:02:30 2021

NOTE: F1X0 on disk 0 (fmt 2) relocated at fcn 0.11628: AU 10 -> AU 0

NOTE: header on disk 1 advanced to format #2 using fcn 0.0

NOTE: F1X0 on disk 1 (fmt 2) relocated at fcn 0.11628: AU 0 -> AU 1140

NOTE: 03/31/21 17:02:30 DATA.F1X0 copy 1 relocating from 0:10 to 1:1140 at FCN 0.11628

Wed Mar 31 17:02:36 2021

NOTE: Attempting voting file refresh on diskgroup DATA

NOTE: Refresh completed on diskgroup DATA. No voting file found.

Wed Mar 31 17:03:25 2021

NOTE: stopping process ARB0

Wed Mar 31 17:03:25 2021

NOTE: GroupBlock outside rolling migration privileged region

NOTE: requesting all-instance membership refresh for group=6

Wed Mar 31 17:03:25 2021

SUCCESS: rebalance completed for group 6/0xb6b1c3c8 (DATA)

Wed Mar 31 17:03:25 2021

GMON updating for reconfiguration, group 6 at 38 for pid 36, osid 15785

Wed Mar 31 17:03:25 2021

NOTE: group 6 PST updated.

SUCCESS: grp 6 disk DATA_0000 emptied

NOTE: erasing header (replicated) on grp 6 disk DATA_0000

NOTE: erasing header on grp 6 disk DATA_0000

NOTE: process _x000_+asm2 (15785) initiating offline of disk 0.3940627264 (DATA_0000) with mask 0x7e in group 6 (DATA) without client assisting

NOTE: initiating PST update: grp 6 (DATA), dsk = 0/0xeae13340, mask = 0x6a, op = clear

Wed Mar 31 17:03:26 2021

GMON updating disk modes for group 6 at 39 for pid 36, osid 15785

NOTE: group DATA: updated PST location: disk 0001 (PST copy 0)

Wed Mar 31 17:03:26 2021

NOTE: PST update grp = 6 completed successfully

NOTE: initiating PST update: grp 6 (DATA), dsk = 0/0xeae13340, mask = 0x7e, op = clear

Wed Mar 31 17:03:26 2021

GMON updating disk modes for group 6 at 40 for pid 36, osid 15785

Wed Mar 31 17:03:26 2021

NOTE: cache closing disk 0 of grp 6: DATA_0000

Wed Mar 31 17:03:26 2021

NOTE: PST update grp = 6 completed successfully

Wed Mar 31 17:03:26 2021

GMON updating for reconfiguration, group 6 at 41 for pid 36, osid 15785

Wed Mar 31 17:03:26 2021

NOTE: cache closing disk 0 of grp 6: (not open) DATA_0000

Wed Mar 31 17:03:26 2021

NOTE: group 6 PST updated.

Wed Mar 31 17:03:26 2021

NOTE: membership refresh pending for group 6/0xb6b1c3c8 (DATA)

Wed Mar 31 17:03:26 2021

GMON querying group 6 at 42 for pid 22, osid 9992

GMON querying group 6 at 43 for pid 22, osid 9992

Wed Mar 31 17:03:26 2021

NOTE: Disk DATA_0000 in mode 0x0 marked for de-assignment

SUCCESS: refreshed membership for 6/0xb6b1c3c8 (DATA)

NOTE: Attempting voting file refresh on diskgroup DATA

NOTE: Refresh completed on diskgroup DATA. No voting file found.

• Query to check ASM disk group status

SQL> set lines 999;

col diskgroup for a10

SQL> SQL> col diskname for a12

col path for a30

select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,

b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

order by b.group_number,b.name;SQL> SQL>   2    3    4    5

DISKGROUP  DISKNAME       TOTAL_MB    USED_MB    FREE_MB PATH                           HEADER_STATU

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

                                 0          0          0 /dev/oracleasm/disks/DATA      FORMER

                                 0          0          0 /dev/oracleasm/disks/CRSNEW    FORMER

                                 0          0          0 /dev/oracleasm/disks/BACKUPNEW FORMER

                                 0          0          0 /dev/oracleasm/disks/FRANEW    FORMER

BACKUP     BACKUP_0000       12284         96      12188 /dev/oracleasm/disks/BACKUP    MEMBER

CRS        CRS_0000          12284       4472       7812 /dev/oracleasm/disks/CRS       MEMBER

DATA       DATA_0001         20473       2281      18192 /dev/oracleasm/disks/DATANEW   MEMBER

FRA        FRA_0000          20473        580      19893 /dev/oracleasm/disks/FRA       MEMBER

8 rows selected.

SQL>

• Delete disk at OS level using root user

[root@srv2 ~]# oracleasm deletedisk DATA

Disk "DATA" defines an unmarked device

Dropping disk: done

[root@srv2 ~]# oracleasm listdisks

BACKUP

BACKUPNEW

CRS

CRSNEW

DATANEW

FRA

FRANEW

[root@srv2 ~]#

• oracleasm scandisks on remote node to check the status

[root@srv1 ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "DATA"

Scanning system for ASM disks...

[root@srv1 ~]# oracleasm listdisks

BACKUP

BACKUPNEW

CRS

CRSNEW

DATANEW

FRA

FRANEW

ASM drop diskgroup ORA-15032: not all alterations performed

 

Hi all we recently wants to perform ASM LUN migration activity we face some error,  Although this error not related the ASM LUN migration.  When we try to delete asm diskgroup name is NEWBACKUP using asmca we got error as well is in command prompt. Earlier in another scenario we deleted the same but do not encountered any issue. After troubleshooting  we dismount the disk group from another instance and deleted the same. 

Error:

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15250: insufficient diskgroup space for rebalance completion

ORA-15039: diskgroup not dropped

ORA-15073: diskgroup BACKUPNEW is mounted by another ASM instance

Dropping disks from disk group BACKUPNEW failed with the following message:

ORA-15032: not all alterations performed

ORA-15250: insufficient diskgroup space for rebalance completion

Multitenant - Triggers 12.1.0.2.0

Hi all in 12.1.0.2.0 we use trigger and for 12.2 we use savepoint . 

See link: 12.2.0.1.0 Savepoint

We tested in both standalone or RAC grid database when start again CDB get started but PDB remains mounted. So in order to avoid this we user trigger in 12.1 and in 12.2 we use save point.

Below example we execute trigger so on next time when we start CDB automatically all pdb will be in read write.  We can modify triggers as per requirement just like save point.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 NEWSMRUPGR10                   READ WRITE NO

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 NEWSMRUPGR10                   READ WRITE NO

SQL> CREATE OR REPLACE TRIGGER open_pdbs

  AFTER STARTUP ON DATABASE

BEGIN

   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';

END open_pdbs;

/  2    3    4    5    6

Trigger created.

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRCDB    SMRCDB           SMRCDB                         READ WRITE           PRIMARY          NO                 0

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

oracle@TEST:~> tnsping SMRCDB

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 24-FEB-2021 08:56:06

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SMRCDB)))

OK (0 msec)

oracle@TEST:~> sqlplus sys/----@SMRCDB as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 08:56:16 2021

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

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

SMRCDB    SMRCDB           SMRCDB                         READ WRITE           PRIMARY          NO                 0

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Warning: You are no longer connected to ORACLE.

SQL> exit

oracle@TEST:~> . oraenv

ORACLE_SID = [SMRCDB] ?

The Oracle base remains unchanged with value /u01/app/oracle

oracle@TEST:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 08:56:56 2021

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size             654313592 bytes

Database Buffers         1476395008 bytes

Redo Buffers               13062144 bytes

Database mounted.

Database opened.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 NEWSMRUPGR10                   READ WRITE NO

SQL>