How to tune a SQL statement

Please follow the below steps for tuning a SQL statement.

Take the sql_id before going to start.

1. Create Tuning Task:

SQL> set serveroutput on
declare
sql_tune_task_id  varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => '03h94xbcu25c3',            //give your sql_id
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tune_03h',
                          description => 'tuning task for statement 03h94xbcu25c3');
  dbms_output.put_line('sql_tune_task_id: ' || sql_tune_task_id);
end;
/

2. Execute Tuning Task:
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_03h');

3. TO GET SUMMARY INFORMATION:

SQL> set long 100000
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('sql_tune_03h') as recommendations from dual;

The above query result gives the recommendations if you want to follow you can. After completion of your work drop the tuning task which you created earlier.

4. Drop the tuning task:

SQL> begin
DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tune_03h');
end;
 /

To know the status of the tuning task
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = '<OWNER_NAME>';

You can tune the sql statement by using the below script provided by the Oracle
script location: ORACLE_HOME/rdbms/admin/sqltrpt.sql
It will prompt for the sql_id, provide the sql_id.

Happy Learning :)

Standby Creation step by step using RMAN Active database duplicate

Standby creation for prod by RMAN duplicate concept
Details of servers are given below.
Primary server details:-
Server name: oracleserver01.prod.com
IP Address: 192.168.1.67
SID: prod

Standby server details:-
Server name : oracleserver02.prod-standby.com
IP Address: 192.168.1.68

Note:
******
Before performing standby creation hash out cron for archive deletion from production.
Steps to be followed to create standby for prod.

1. Add standby logfile in production database server(online logfile + 1)

SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b where a.GROUP#=b.GROUP#;
    GROUP# MEMBER                                                             A.BYTES/1024/1024    THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
6 rows selected.

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL

SQL> alter system set standby_file_management='AUTO' scope=both;
System altered.

SQL> show parameter standby
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

SQL> show parameter db_create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string      +FLASH
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter system set db_create_online_log_dest_2='';
System altered.

SQL> show parameter db_create
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
db_create_online_log_dest_1          string      +DATA
db_create_online_log_dest_2          string
db_create_online_log_dest_3          string
db_create_online_log_dest_4          string
db_create_online_log_dest_5          string

SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.
SQL> alter database add standby logfile size 50m;
Database altered.

SQL> select b.GROUP#,b.MEMBER,a.BYTES/1024/1024,a.THREAD# from v$log a,v$logfile b;
    GROUP# MEMBER                                                             A.BYTES/1024/1024    THREAD#
---------- ------------------------------------------------------------------ ----------------- ----------
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +DATA/prod/ONLINELOG/group_3.381.881928479                                 50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         3 +FLASH/prod/ONLINELOG/group_3.2693.881928479                               50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +DATA/prod/ONLINELOG/group_2.382.881928479                                 50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         2 +FLASH/prod/ONLINELOG/group_2.2692.881928479                               50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +DATA/prod/ONLINELOG/group_1.383.881928477                                 50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         1 +FLASH/prod/ONLINELOG/group_1.2691.881928477                               50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         4 +DATA/prod/ONLINELOG/group_4.430.881932753                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         5 +DATA/prod/ONLINELOG/group_5.431.881932867                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         6 +DATA/prod/ONLINELOG/group_6.432.881932901                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1
         7 +DATA/prod/ONLINELOG/group_7.433.881932905                                 50          1

30 rows selected.

2. Enable force logging on production.
SQL> alter database force logging;

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

3. In the standby database home, create and start a listener that offers a static SID entry for the standby database.
[oracle@oracleserver02 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
          (SID_LIST =
            (SID_DESC =
              (GLOBAL_NAME = standby_dgmgrl)
              (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
              (SID_NAME = standby)
            )
          (SID_DESC =
              (GLOBAL_NAME = standby)
              (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
              (SID_NAME = standby)
            )
          )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Tnsnames.ora
***********
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_prod =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))


standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

LISTENER_standby =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))

4. In the database home on the primary node, create an Oracle Net alias to connect to the listener created in the above step.
prod listener:
**************
[oracle@oracleserver01 admin]$ more /u01/app/12.1.0.2/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = prod)
      (ORACLE_HOME =/u01/app/oracle/product/12.1.0.2/db_1)
    )
  )
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

Tnsnames.ora:
************
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )
LISTENER_standby =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver02.prod-standby.com)(PORT = 1521))
prod =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
LISTENER_prod =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver01.prod.com)(PORT = 1521))

5. Register the new listener with new grid home(12.1*)

$ srvctl modify listener –l listener –o /u01/app/12.1.0.2/grid

[oracle@oracleserver02 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:43
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
The command completed successfully

[oracle@oracleserver02 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-JUN-2015 13:54:53
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                16-MAY-2015 03:22:03
Uptime                    24 days 10 hr. 32 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracleserver02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver02.prod-standby.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "standby_DGB" has 1 instance(s).
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 2 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standbyXDB" has 1 instance(s).
  Instance "standby", status READY, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Check for network connectivity from primary to standby database.

6. Create a password file on the standby host that where the auxiliary instance will be started. Copy password file from primary to standby in $ORACLE_HOME/dbs location.
[oracle@oracleserver02 dbs]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/dbs
[oracle@oracleserver02 dbs]$ orapwd file=orapwstandby password=yas_3

7.  On the standby host in the ORACLE_HOME/dbs directory create a pfile (initstandby.ora) with the following parameters:
[oracle@oracleserver02 dbs]$ more initstandby.ora
db_name=prod
db_unique_name=standby
sga_target=2g
db_create_online_log_dest_1='+DATA'
db_create_file_dest='+DATA'
db_recovery_file_dest_size=5g
db_recovery_file_dest='+FLASH'
control_files='+DATA'
compitable=12.1.0.2

8.create audit directory on for standby database on standby server
mkdir –p /u01/app/oracle/admin/standby/adump

9. On the standby host set the SID to the same as standby and startup nomount the standby/auxiliary instance. And add the the sid in oratab file too.

export ORACLE_SID=standby
sqlplus / as sysdba
startup nomount
[oracle@oracleserver02 admin]$cat /etc/oratab
standby: /u01/app/oracle/product/12.1.0.2/db_1/:N

10. On the primary host run an rman script that duplicates the primary database using the “duplicate target database for standby from active database” command

[oracle@oracleserver01 dba]$ more rman_standby_standby.ksh
!/bin/ksh

export ORAENV_ASK="NO"
export ORACLE_SID=prod
. oraenv
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

rman target sys/yas_3@prod auxiliary sys/yas_3@standby debug trace=rman_trace.log log=standby_attempt.log<<EOF
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stdby1 type disk;
allocate auxiliary channel stdby2 type disk;

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET DB_UNIQUE_NAME='standby'
set db_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','prod','standby'
set log_file_name_convert='+FLASH/prod/','+FLASH/standby/','+DATA/prod','+DATA/standby','+DMNO_REDO/prod','+DMNO_REDO/standby','prod','standby'
set control_files='+DATA/standby/controlfile/control01.ctl','+FLASH/standby/controlfile/control02.ctl'
set  DB_CREATE_FILE_DEST='+DATA'
set db_recovery_file_dest='+FLASH'
set db_recovery_file_dest_size='200G'
set audit_file_dest='/u01/app/oracle/admin/standby/adump'
Nofilenamecheck;
release channel prmy1;
release channel prmy2;
release channel stdby1;
release channel stdby2;
}
EOF

A brief explanation of the individual clauses is shown below.

• FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
• FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
• DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
• SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
• NOFILENAMECHECK: Destination file locations are not checked.

10. create Softlink for network files.

ln -s /u01/app/12.1.0.2/grid/network/admin/listener.ora listener.ora
ln -s /u01/app/12.1.0.2/grid/network/admin/tnsnames.ora tnsnames.ora

12. Edit the parameter log_archive_dest_2 in primary database.
Primary:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.

SQL> alter system set log_archive_dest_2= ‘service="standby" ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30, valid_for=(online_logfile,all_roles)’;

13. Set below parameters on standby.

Standby:
*******
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=prod;
System altered.
SQL> alter system set log_archive_config='dg_config=(prod,standby)';
System altered.

13. The standby database will get mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;

DGMGRL Configuration:
*********************
1. Login into primary database (prod) server and standby database (standby) server.
2. Check if both the production and standby database are in sync.
On PRIMARY:
? Select max(sequence#) from v$archived_log;
On STANDBY:
? Select max(sequence#) from v$log_history;
3. Check if output of both these matches with each other. If not kindly bring the database in sync, check if there are any errors transfering archives on standby database.
4. Check ‘ERROR’ column in v$managed_standby for any issue related to log transfer
? Select dest_id,error from v$managed_standby;
5. If everything is fine, then proceed with dataguard broker configuration.
6. Start the Dataguard broker DMON process.
      On Primary and Standby,

SQL> alter system set dg_broker_start=true sid='*';
SQL> alter system set dg_broker_config_file1=’<DIR_PATH/file_name.dat>’
SQL> alter system set dg_broker_config_file2=’<DIR_PATH/file_name.dat>’

7. Connect to dataguard broker.
  Connect to DGMGRL

  Primary,
$dgmgrl
DGMGRL>connect /

8. Create configuration.
DGMGRL> CREATE CONFIGURATION 'DG-PRIM' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS prod;

Before performing the above kindly make sure no log_archive_dest_n is set on both Primary and Standby database.
9. Verify configuration.
DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  boston - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Add Standby
DGMGRL> add database standby as connect identifier is standby;

DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  prod  - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

10. Enable Configuration
DGMGRL> enable configuration;

11. Verify configuration
DGMGRL> show configuration;

Configuration – DG-PRIM

  Protection Mode: MaxPerformance
  Databases:
  prod  - Primary database
    standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
DGMGRL> show database prod;

Database – prod

  Role:              PRIMARY
  Intended State:    TRANSPORT-ON
  Instance(s):
    prod

Database Status:
SUCCESS

DGMGRL>  show database standby;

Database – standby

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     0 seconds (computed 1 second ago)
  Apply Lag:         0 seconds (computed 1 second ago)
  Apply Rate:        2.54 MByte/s
  Real Time Query:   ON
  Instance(s):
    standby
Database Status:
SUCCESS

Note:
******
You can find above steps in Oracle DOC ID: 1583588.1
For prerequisites follow oracle doc ID:  1305019.1

Metrics Average Users Waiting Count is at x for event class Application

If you receive this alert check the sessions which are in waiting state and what is reason behind for the waiting state. The below query will give the list of sessions which are in waiting state. So based on the output you can easily find out.

col LOGONDATE for a15 trunc
col MODULE for a30 trunc
col OSUSER for a8
col USERNAME for a8
set pages 10000
col sql_cost for 9999999 trunc
col sid for 99999999
col sql_CNT for 9999999
col machine for a10
col lc_et for 999999
col spid form a6
col app_id form a6
col event for a30
col wait_mins for 99999 trunc
select /*a.sid,a.serial#,*/ b.spid,a.sql_id,(select OPTIMIZER_COST from v$sqlarea where sql_id=a.sql_id) sql_cost,
(select count(1) from v$session where SQL_HASH_VALUE=a.sql_hash_value) sql_CNT,
a.PROCESS App_ID, a.username,
to_char(a.logon_time,'DD-MM HH24:MI:SS') LogonDate, a.event, a.osuser, a.module,a.machine,round(a.last_call_et/60) wait_mins
from v$session a, v$process b where a.paddr = b.addr
and a.status = 'ACTIVE' and a.username is not null and a.sql_id is not null
order by 8,3,11 desc;

Happy Learning :)

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 :)