How to drop a disk from the diskgroup

SQL> select name,group_number,state,type,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup; 
NAME                           GROUP_NUMBER STATE       TYPE   Totalspace(GB) Freespace(GB)   PCT_FREE
------------------------------ ------------ ----------- ------ -------------- ------------- ----------
PROD_RECO1                                3 MOUNTED     EXTERN           1600    1305.19922 81.5749512

SQL> Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=3;

PATH                  DISK_NUMBER NAME               MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024
--------------------- ----------- ------------------ ------- ------------ ---------- ------------- ------------
ORCL:ASM_PROD_133               0 ASM_PROD_133       CACHED  MEMBER              400           400   326.292969
ORCL:ASM_PROD_134               5 ASM_PROD_134       CACHED  MEMBER              400           400   326.273438
ORCL:ASM_PROD_135               6 ASM_PROD_135       CACHED  MEMBER              400           400     326.3125
ORCL:ASM_PROD_136               7 ASM_PROD_136       CACHED  MEMBER              400           400   326.320313

SQL> alter diskgroup PROD_RECO1 drop disk ASM_PROD_133;

Diskgroup altered.

SQL> select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           3 REBAL RUN           1          1       1046      31956       5837           5
  
SQL> select name,group_number,state,type,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup;

NAME                           GROUP_NUMBER STATE       TYPE   Totalspace(GB) Freespace(GB)   PCT_FREE
------------------------------ ------------ ----------- ------ -------------- ------------- ----------
PROD_RECO1                                3 MOUNTED     EXTERN           1200    901.734375 75.1445313
   
SQL> Select path,disk_number,name,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=3;

PATH                      DISK_NUMBER NAME           MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024
------------------------- ----------- -------------- ------- ------------ ---------- ------------- ------------
ORCL:ASM_PROD_134                   5 ASM_PROD_134   CACHED  MEMBER              400           400   301.148438
ORCL:ASM_PROD_135                   6 ASM_PROD_135   CACHED  MEMBER              400           400   301.167969
ORCL:ASM_PROD_136                   7 ASM_PROD_136   CACHED  MEMBER              400           400   301.164063


Happy Learning :)

How to move a datafile from filesystem to ASM

We can move the datafile from filesystem to ASM by bringing the corresponding tablespace offline or the only datafile which you needs to be move. We can see these 2 scenarios.

Here we have added the datafile wrongly due to that datafile is not created in a specified location.

SQL> alter tablespace TCB_TSQ01 add datafile 'CRM_DBF01' size 100m autoextend on next 20m maxsize 32g;

Tablespace altered.


 
I) Tablespace offline:
******************

1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                  TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

2. Take the corresponding tablespace offline. 

SQL> alter tablespace TCB_TSQ01 offline;

Tablespace altered.

3. Copy the datafile to the desired location using RMAN

-bash-4.1$ rman target /

RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';

Starting backup at 20-SEP-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01

output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-SEP-15

Starting Control File and SPFILE Autobackup at 20-SEP-15

piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE

Finished Control File and SPFILE Autobackup at 20-SEP-15

RMAN> exit

Recovery Manager complete.

4. Update the controfile with the new location of the datafile.

SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

Database altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01

6 rows selected.

5. Bring the tablespace to the online.

SQL> alter tablespace TCB_TSQ01 online;

Tablespace altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

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

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

II) Datafile Offline:
****************
1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------
 /u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                          TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

2. Take the datafile offline.

SQL> alter database datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' offline;

Database altered.

3. Copy the datafile to the desired location using RMAN

-bash-4.1$ rman target /

RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';

Starting backup at 20-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01
output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-SEP-15

Starting Control File and SPFILE Autobackup at 20-SEP-15
piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE
Finished Control File and SPFILE Autobackup at 20-SEP-15

RMAN> exit

Recovery Manager complete.

4. Update the controlfile with the new location of the datafile.

SQL> set pages 200 lines 200
SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

Database altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------
+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                               
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

5. Recover the datafile using RMAN.

RMAN> recover datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

6. Bring the datafile to the online.

SQL> alter database datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477' online;

Tablespace altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- -------------
+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

Note:

*****

If your oracle version is 12c you can execute the below statement is enough to move the datafile. No need to perform all the above steps.

SQL>  alter database move datafile
'/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to ' +CRM_DBF01';

Happy Learning :)

How to add ASM disk to ASM Diskgroup

1. Login to the ASM instance

-bash-3.2$ ps -ef|grep pmon

oracle    1829     1  0 Jul21 ?        00:04:58 ora_pmon_PROD1

oracle    9529  8780  0 21:18 pts/0    00:00:00 grep pmon

oracle   16319     1  0  2014 ?        00:10:46 asm_pmon_+ASM1

-bash-3.2$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

The Oracle base has been set to /u01/oracle/product

-bash-3.2$

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 24 21:18:43 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

2. Identify the disks to be added to the ASM diskgroup

SQL> select name,group_number,state,total_mb/1024 "Totalspace(GB)",free_mb/1024 "Freespace(GB)", (free_mb/total_mb)*100 "PCT_FREE" from v$asm_diskgroup;

NAME                 GROUP_NUMBER STATE       Totalspace(GB) Freespace(GB)   PCT_FREE

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

SIVA_DBF01                               8 MOUNTED               600    82.396484 13.6675571

SQL> col path for a45

Select path,group_number,disk_number,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=&n;

Enter value for n: 8

old   1: Select path,group_number,disk_number,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=&n

new   1: Select path,group_number,disk_number,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where group_number=8

PATH                                          GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024

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

ORCL:SIVA_ASM01                                         8          01 CACHED  MEMBER              200           200   28.7119141

ORCL:SIVA_ASM02                                         8          02 CACHED  MEMBER              200           200   28.7089844

ORCL:SIVA_ASM03                                         8          03 CACHED  MEMBER              200           200   28.7207031

SQL> Select path,group_number,disk_number,mount_status,header_status,os_mb/1024,total_mb/1024,free_mb/1024 from v$asm_disk where header_status <>'MEMBER';

PATH                                          GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU OS_MB/1024 TOTAL_MB/1024 FREE_MB/1024

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

ORCL:SIVA_ASM04                                         0           4 CLOSED  PROVISIONED         200             0            0

ORCL:SIVA_ASM05                                         0           5 CLOSED  PROVISIONED         200             0            0

ORCL:SIVA_ASM06                                         0           6 CLOSED  PROVISIONED         200             0            0

3. Add the disk to the Diskgroup

SQL> alter diskgroup SIVA_DBF01 add disk 'ORCL:SIVA_ASM04' name SIVA_ASM04 size 204800m;

alter diskgroup SIVA_DBF01 add disk 'ORCL:SIVA_ASM04' name SIVA_ASM04 size 204800m

*

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15260: permission denied on ASM disk group

Note:

****

For adding a disk to the diskgroup you need to login as a SYSASM not SYSDBA.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

-bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 24 21:24:33 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup SIVA_DBF01 add disk 'ORCL:SIVA_ASM04' name SIVA_ASM04 size 204800m rebalance power 4;

Diskgroup altered.

SQL>  select * from v$asm_operation;

GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE

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

           8 REBAL RUN           4          4        972     154322       4344          35

Happy Learning :)