Saturday, December 31, 2016

OEM Cloud Control Job Suspended - Target is not ready

In my OEM 12c, my RMAN backup job for one target database started failing, and next execution status was marked as "Suspended".







When I opened the job, it further showed the status, "Target is not Ready".














I suddenly realized that I refreshed this database a couple of days back, and this might have caused this problem. So I opened my this database target, and I saw that OEM was showing "Metric Collection Error" for this target.




















To solve this issue, next step was to re-configure this target. So I went to Target Setup -> Monitoring Configuration.
















And in the target's monitoring configuration page, just clicked Next. For you, you can also double check connection by clicking "Test Connection" button, and then click Next.


















On next page, I clicked "Submit".














And my database target got reconfigured, and I saw target status "up" again.













Immediately after this, the status of backup job automatically turned to "Scheduled".

Wednesday, October 26, 2016

DUPLICATE Database using Backup of a Database

If you want to create a duplicate database from a live/active database, you can follow this article. But if you want to DUPLICATE a database using backup of another database, you can use following steps. I am creating new database with a different name using DUPLICATE command, or you may want to retain same name for the database while duplicating.

Source database name is TEST with all files under +DATA/TEST directory.
Destination database name is TESTT, and to have all files under +DATA/TESTT directory.

On a new server where you want to duplicate the database, create inittestt.ora file under $ORACLE_HOME/dbs directory. You can copy and then modify parameter file of TEST database to create pfile for TESTT. For this example, I copied backup of TEST database under /u02/backup directory and this backup will be used to build TESTT database.

Add following lines in inittestt.ora file 
Db_file_name_convert=’+DATA/TEST/’,’+DATA/TESTT’
Log_file_name_convert= ‘+RECO/TEST’,’+RECO/TESTT’
Control_files=’+DATA/TESTT/control01.ctl’,’+DATA/TESTT/control02.ctl’

Start TESTT in nomount mode
$export ORACLE_SID=testt
SQL> startup nomount

Connect to RMAN and do duplicate using backup of TEST

rman auxiliary /
RMAN> duplicate database to testt backup location '/u02/backup/';

Starting Duplicate Db at 26-AUG-16

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1937457152 bytes

Fixed Size                     2254464 bytes
Variable Size                536873344 bytes
Database Buffers            1392508928 bytes
Redo Buffers                   5820416 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TEST'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''TESTT'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u02/backup/control.bak';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''TESTT'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    1937457152 bytes

Fixed Size                     2254464 bytes
Variable Size                536873344 bytes
Database Buffers            1392508928 bytes
Redo Buffers                   5820416 bytes

Starting restore at 26-AUG-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testt/control01.ctl
output file name=+DATA/testt/control02.ctl
Finished restore at 26-AUG-16

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK

contents of Memory Script:
{
   set until scn  932969;
   set newname for datafile  1 to
 "+DATA/testt/system01.dbf";
   set newname for datafile  2 to
 "+DATA/testt/sysaux01.dbf";
   set newname for datafile  3 to
 "+DATA/testt/undotbs01.dbf";
   set newname for datafile  4 to
 "+DATA/testt/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-AUG-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA/testt/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA/testt/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA/testt/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA/testt/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/test_04re259h_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u02/backup/test_04re259h_1_1.bak tag=TAG20160824T114448
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 26-AUG-16

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=920891413 file name=+DATA/testt/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=920891413 file name=+DATA/testt/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=920891413 file name=+DATA/testt/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=920891413 file name=+DATA/testt/users01.dbf

contents of Memory Script:
{
   set until scn  932969;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-AUG-16
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/test_06re25a2_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u02/backup/test_06re25a2_1_1.bak tag=TAG20160824T114506
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_9_920718405.dbf thread=1 sequence=9
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_9_920718405.dbf RECID=1 STAMP=920891413
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-AUG-16
Oracle instance started

Total System Global Area    1937457152 bytes

Fixed Size                     2254464 bytes
Variable Size                536873344 bytes
Database Buffers            1392508928 bytes
Redo Buffers                   5820416 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TESTT'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''TESTT'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1937457152 bytes

Fixed Size                     2254464 bytes
Variable Size                536873344 bytes
Database Buffers            1392508928 bytes
Redo Buffers                   5820416 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTT" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+DATA/testt/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+DATA/testt/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+DATA/testt/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA/testt/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA/testt/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/testt/sysaux01.dbf",
 "+DATA/testt/undotbs01.dbf",
 "+DATA/testt/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA/testt/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=+DATA/testt/sysaux01.dbf RECID=1 STAMP=920891427
cataloged datafile copy
datafile copy file name=+DATA/testt/undotbs01.dbf RECID=2 STAMP=920891427
cataloged datafile copy
datafile copy file name=+DATA/testt/users01.dbf RECID=3 STAMP=920891427

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=920891427 file name=+DATA/testt/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=920891427 file name=+DATA/testt/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=920891427 file name=+DATA/testt/users01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 26-AUG-16

RMAN> exit






Friday, October 14, 2016

Configuring OEM Express Control

Starting 12c, OEM Database Control has been replaced by OEM Database Express. OEM Express configuration is very simple as compared to previous OEM Database Control. Following is the method to configure OEM Express. OEM Express Database is configured separately for container database and each pluggable database. Method is same; you just need to set current container to pluggable database for which you want to configure the OEM, and execute the steps as explained bellow.

Container database
Check dispatcher configuration in the database
SQL> show parameter dispatcher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=salman12XDB)
max_dispatchers                      integer

Check current port set for XML DB. If not set, set the port
SQL> SELECT dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
           0

SQL>  exec dbms_xdb_config.sethttpsport(5000)

PL/SQL procedure successfully completed.

SQL> SELECT dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
        5000

If you check listener status, you can see the port.
C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 04-DEC-2015 16:33:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SALMQURE-SG)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                04-DEC-2015 08:53:23
Uptime                    0 days 7 hr. 39 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\salmqure\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\salmqure\product\12.1.0\dbhome_1\log\diag\tnslsnr\SALMQURE-SG\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SALMQURE-SG)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=SALMQURE--SG)(PORT=5000))(Security=(my_wallet_directory=C:\APP\SALMQURE\admin\salman12\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "pdb1.sg.oracle.com" has 1 instance(s).
  Instance "salman12", status READY, has 1 handler(s) for this service...
Service "pdb2.sg.oracle.com" has 1 instance(s).
  Instance "salman12", status READY, has 1 handler(s) for this service...
Service "salman12.sg.oracle.com" has 1 instance(s).
  Instance "salman12", status READY, has 1 handler(s) for this service...
Service "salman12XDB.sg.oracle.com" has 1 instance(s).
  Instance "salman12", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\>



Start using OEM Database Express using URL similar to the following
https://<host_name>:port_number

For example




For Pluggable Database
SQL> alter session set container=pdb1;

Session altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB1                           READ WRITE

SQL> SELECT dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
           0

SQL>  exec dbms_xdb_config.sethttpsport(5001)

PL/SQL procedure successfully completed.

SQL> SELECT dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
        5001

Now use following URL to access OEM for pluggable database


Tuesday, October 4, 2016

Calculating ASM diskgroup Usable Size

Sometimes sizing information of an ASM diskgroup returned by “lsdg” command from “asmcmd” prompt (similar information is also available under v$asm_diskgroup view) leaves DBAs perplexed. Sizing information and usable space for diskgroups with EXTERNAL REDUNDANCY is quite easy to understand, but when it comes to NORMAL or HIGH redundancy diskgroups, it is not very straightforward. Here I would explain ASM diskgroup space calculation in detail to make it easier to understand. Before I explain how to calculate usable space in an ASM diskgroup, some useful information to note is as follows
  • Diskgroups with EXTERNAL REDUNDANCY don’t mirror any data at all
  • Diskgroups with NORMAL REDUNDANCY keep 2 copies of each extant (ASM extent, which is made up of one or more ASM Allocation Units) on 2 different ASM failure groups in that diskgroup.  
  • Diskgroups with HIGH REDUNDANCY keep 3 copies of each extant (ASM extent, which is made up of one or more ASM Allocation Units) on 3 different ASM failure groups in that diskgroup.  
  • By default each ASM disk is in its own FAILURE GROUP, unless disks are put in a specific failure group during diskgroup creation.
  • Disks which are prone to become unavailable together; are usually put in a single failure group. For example, if we have 2 disk controllers with each controller having 10 disks in it, all disks from each controller should be in a single failure group because in case of a controller failure, all 10 disks of that controller would become unavailable. In ASM mirroring, ASM would always keep copies of an extent on the disks in different failure groups; thus keeping a copy of data always available in case a controller failure makes all 10 disks unavailable in the failure group.
  • If we use lsdg command, or query sizing details of a diskgroup from v$asm_diskgroup we find following columns with sizing details
TOTAL_MB: Total size of a diskgroup in MB (adding up space of all disks in the diskgroup)


FREE_MB: Current free space available in the diskgroup. In case of external redundancy, full space listed under this column is available for the usage. In case of normal redundancy, divide this value by 2 to get actual amount of space that can be utilized since 2 copies of each extent have to be stored. In case of high redundancy, this value should be divided by 3 because total 3 copies of data are to be stored in this free space.

REQUIRED_MIRROR_FREE_MB: Value under this column shows the amount of space required to re-mirror or “restore redundancy” of the data in case of a worst failure that can be tolerated by this diskgroup. In case of normal redundancy, a diskgroup can tolerate loss of one failure group and in case of high redundancy; diskgroup can tolerate loss of 2 failure groups. After the loss, this much space would be required to re-mirror (or restore redundancy of) the diskgroup. After a failure, rebalance will happen and redundancy will be restored, and then value under this column would be updated to a new value to show current requirement to restore redundancy in case of a failure.

USABLE_FILE_MB: Value under this column shows the amount of space that can be used/allocated from the diskgroup. This value can be negative (as you will see in bellow examples) which would mean that there is not enough space available in the diskgroup to re-mirror (restore redundancy) after the worst failure this diskgroup can tolerate.
Here the point to note is that value under REQUIRED_MIRROR_FREE_MB does not mean that this amount of space has been reserved and cannot be used, but this value only tells us that if we want to restore redundancy (2 way redundancy for normal and 3 way for high redundancy) after a failure, we should keep this much space available (never use it) – and negative value under USABLE_FILE_MB actually tells us that we have started utilizing space which was reserved for restoring redundancy.

Formula to calculate USABLE_FILE_MB
For External redundancy
USABLE_FILE_MB = FREE_MB
For Normal Redundancy
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 2
For High Redundancy
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 3
Following examples would explain how disk space is allocated and used in an ASM diskgroup. I have total 6 disks with 4G size each to work with during these examples.

External Redundancy
Let’s create a disk group with EXTERNAL REDUNDANCY.
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK 'ORCL:DISK1','ORCL:DISK2','ORCL:DISK3', 'ORCL:DISK4', 'ORCL:DISK5', 'ORCL:DISK6';

lsdg command shows that TOTAL_MB and USABLE_FILE_MB is 24G; as full disk space is available and will be utilized to store the data - no mirroring would be done. Alternatively you can query same information from v$asm_sidkgroup as you can see below.
ASMCMD> lsdg data
State    Type    Rebal      Sector  Block       AU       Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N    512     4096  1048576     24564   24504                0                       24504                  0                       N           DATA/


SQL>  select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where group_number=2;

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564             24505                0                                                               24504

After creating a tablespace of 6g size, space usage of diskgroup can be seen.
SQL> create tablespace mytbs datafile '+data' size 6g;
SQL>  select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where group_number=2;

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564             18357                0                                                               18357

Normal Redundancy
Now we create a diskgroup using these 6 disks, with Normal Redundancy.

SQL> CREATE DISKGROUP data NORMAL REDUNDANCY DISK 'ORCL:DISK1','ORCL:DISK2','ORCL:DISK3', 'ORCL:DISK4','ORCL:DISK5','ORCL:DISK6';

Diskgroup created.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564             24405                4094                                                          10155











REQUIRED_MIRROR_FREE_MB is 4094 which is equal to the size of biggest failure group (each disk is in its own failure group here) so that in case of one disk/failuregroup failure, the mirroring can be re-done using this free space.
Now we create a tablespace with 6G size in +DATA diskgroup and check size.

SQL> create tablespace mytbs datafile '+data' size 6g;

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564             12109                4094                                                          4007
As you see that FREE_MB has decreased from 24405 MB to 12109 (almost half). This shows that although 6G space was used by newly added tablespace, but actually 12G was consumed because of 2 way mirroring. USABLE_FILE_MB space reduced from 10155 MB to 4007 MB i.e. (12109 – 4094) /2 => 4007.
Let’s add another 4000 MB in mytbs tablespace
SQL> alter tablespace mytbs add datafile '+data' size 4000m;

Tablespace altered.

SQL> select sum(bytes/1024/1024) from dba_data_files where tablespace_name='MYTBS';

SUM(BYTES/1024/1024)
--------------------
               10144

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564             4104                  4094                                                            5

USABLE_FILE_MB has now been reduced from 4007 MB to 5 MB only, after consuming further 4000 MB. FREE_MB is now 4104 MB because 4000 MB addition in the tablespace actually consumed 8000 MB because of 2 way mirroring.

High Redundancy
Now let’s create a high redundancy diskgroup and see how space usage works.
SQL> drop tablespace mytbs;

Tablespace dropped.

ASM Instance
SQL> drop diskgroup data;

Diskgroup dropped.

SQL> CREATE DISKGROUP data HIGH REDUNDANCY DISK 'ORCL:DISK1','ORCL:DISK2','ORCL:DISK3', 'ORCL:DISK4', 'ORCL:DISK5','ORCL:DISK6';

Diskgroup created.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564             24405                 8188                                                          5405

REQUIRED_MIRROR_FREE_MB is 8188 which is equal to the size of 2 biggest failure groups (each disk is in its own failure group here) so that in case of 2 disks/failuregroups failure, the mirroring can be re-done using this free space and 3 copies of data exist after re-mirroring.
Now we create a tablespace with 6G size in +DATA diskgroup and check the size.
SQL> create tablespace mytbs datafile '+data' size 6g;

Tablespace created.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564              5964                 8188                                                          -741


After adding 6G (6144 MB) tablespace, FREE_MB has reduced to 5964 i.e. 24405 – (6144 x 3). USABLE_FILE_MB is now in negative which means that there is not enough space available in the diskgroup to restore 3-way redundancy if 2 disks/gailuregroups are gone. At this point if one disk/failuregroup fails, ASM can still restore redundancy because FREE_MB value is more than size of a single disk/failuregroup which is 4G. 
Now I am adding a new file the tablespace to increase size of tablespace from 6144 MM to 8124 MB
SQL>  alter tablespace mytbs add datafile '+data' size 1980m;

Tablespace altered.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB       REQUIRED_MIRROR_FREE_MB         USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ----------------------------------------   ---------------------------
DATA                                24564              18                    8188                                                          -2723

FREE_MB is only 18 MB and if I drop (in order to simulate a disk failure) a disk now, ASM would not be able to restore redundancy even after a disk failure/drop as you can see in the following.
SQL> alter diskgroup data drop disk disk1;
alter diskgroup data drop disk disk1
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15250: insufficient diskgroup space for rebalance completion

Now If I drop a newly added datafile and then try to drop a disk, I would be able to do this because ASM would be able to restore redundancy because sufficient space is available under FREE_MB to perform re-mirroring

SQL> alter tablespace mytbs drop datafile '+DATA/ORCL/DATAFILE/mytbs.257.886505679';

Tablespace altered.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- --------------   ----------------   -----------------------
DATA                                24564              5964           8188                                                  -741

As you see, FREE_MB is now 5964 M which is sufficient to re-mirror the 4G (maximum) data from a disk if a disk is dropped.

SQL> alter diskgroup data drop disk disk1;

Diskgroup altered.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB      REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------   --------------------------------------------
DATA                               20470             1870                  8188                                                    -2106

Now we add disk back again and then reduce tablespace size to 5G. by doing this, FREE_MB would be 9036 and this would mean that now ASM can restore a full 3 way redundancy even if we drop di sisks/failuregroups.
SQL>  alter diskgroup data add disk 'ORCL:DISK1';

Diskgroup altered.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB      REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------   --------------------------------------------
DATA                               20470             5964                  8188                                                    -741


Reduce the file size to 5G and see if we can now drop 2 disks. As long as FREE_MB more than REQUIRED_MIRROR_FREE_MB, we can now drop 2 diks

SQL> alter database datafile '+DATA/ORCL/DATAFILE/mytbs.256.886519815' resize 5g;

Database altered.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB    FREE_MB      REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------   --------------------------------------------
DATA                               20470              9036                  8188                                                    282

SQL> alter diskgroup data drop disk disk1;

Diskgroup altered.

SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB          FREE_MB         REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- --------------   ---------------------------------------------------
DATA                                20470                   4942                    8188                                                 -1082

SQL> alter diskgroup data drop disk disk2;

Diskgroup altered.

SQL>  select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';

NAME                             TOTAL_MB           FREE_MB         REQUIRED_MIRROR_FREE_MB   USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- -------------------------------------------  --------------------------
DATA                                16376                    848                     8188                                                    -2446