Report ASM using sysman oen grid control schema

We want  to  have  a report  of  all  ASM  target  register  in  grid  control.
There  is  the   query  connecting  first  to  sysman  Schema

set  lines  200
set  pages  200
col "who"   for a35
select
                sYS_CONTEXT('USERENV','CURRENT_USER')||'_'||
                sYS_CONTEXT('USERENV','INSTANCE_NAME')||'_'||
                to_char(sysdate,'DDMMYYYYHH24MISS') "who"
        from  dual;
col  diskgroup  for a40
col  target_name  for  a20
col  value   for a55
col REDUNDANCY  for a11
col PERCENT_USED  for 999999999
col NO_OF_DISK  for a11
col  REBAL_PENDING  for a5
col  IMBALANCE  for a5
col  FREE_MB   for a8
SELECT substr(target_name,2) target_name,
         diskgroup,
        -- disk_path,
         MAX (DECODE (seq, 7, VALUE)) REDUNDANCY,
        to_number( MAX (DECODE (seq, 4, VALUE))) PERCENT_USED,
         --MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB,
         MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB,
         --MAX (DECODE (seq, 3, ceil(VALUE/1024),0)) FREE_GB,
         MAX (DECODE (seq, 3, VALUE)) FREE_MB,
         MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB,
         MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK,
         ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, VALUE)))) LUN_SIZE,
         MAX (DECODE (seq, 5, decode(VALUE,'No','',value))) REBAL_PENDING,
         MAX (DECODE (seq, 1, VALUE)) IMBALANCE
    FROM (SELECT target_name,
                 key_value diskgroup,
                 VALUE,
                 metric_column,
                 ROW_NUMBER ()
                 OVER (PARTITION BY target_name, key_value
                    ORDER BY metric_column)
                    AS seq
            FROM MGMT$METRIC_CURRENT
          WHERE        target_type in ('osm_instance','osm_cluster')
                   AND metric_column IN
                          ('rebalInProgress',
                           'free_mb',
                           'usable_file_mb',
                           'type',
                           'computedImbalance',
                           'usable_total_mb',
                           'percent_used','diskCnt')
                           ---'disk_path')
                OR (    metric_column = 'total_mb'
                    AND metric_name = 'DiskGroup_Usage'))
GROUP BY target_name, diskgroup
order by 4,1,2;

The  output :

TARGET_NAME          DISKGROUP                                REDUNDANCY  PERCENT_USED USABLE_TOTAL_GB FREE_MB  USABLE_FREE_GB NO_OF_DISK    LUN_SIZE REBAL IMBAL                                      

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

ASM_pprlkcrs         FRA                                      EXTERN                 0             256 261845              256 1                  256       0                                          

ASM_prdlkcrs         FRA                                      EXTERN                 0             257 262997              257 1                  257       0                                          

ASM_dev-mtlunt119    TMPMBRDXVAL                              EXTERN                 0              64 65407                64 1                   64       0                                          

ASM_qas-mtlunt89     ADOPTFRA                                 EXTERN                 0              64 65422                64 1                   64       0                                          

ASM_dev-mtlunt85     FRA                                      EXTERN                 0              64 65468                64 1                   64       0                                           

ASM_dev-mtlunt105    TEMPRAD                                  EXTERN                 0              64 65360                64 1                   64       0                                          

ASM_dev-mtlunt115    TEMPRAD                                  EXTERN                 0              64 65376                64 1                   64       0                                          

ASM_dev-mtlunt119    TEMPRAD                                  EXTERN                 0              64 65364                64 1                   64       0                                          

ASM_dev-mtlunt105    RDXOCR                                   EXTERN                 0              32 32657                32 1                   32       0                                          

ASM_dev-mtlunt85     TBXOCR                                   EXTERN                 0              32 32688                32 1                   32       0                                           

ASM_val45            PIREL_ARCH                               EXTERN                 0              32 32656                32 1                   32       0                                          

ASM_dev-mtlunt119    OCRD119                                  EXTERN                 0              32 32632                32 1                   32       0                                          

ASM_mtlunt45         PIREL_ARCH                               EXTERN                 0              32 32672                32 1                   32       0                                          

ASM_ADOPTCRS         ADOPT_ARCH                               EXTERN                 0              33 32670                32 1                   33       0                                          

ASM_dev-mtlunt115    OCR                                      EXTERN                 0              16 16237                16 1                   16       0                                           

ASM_dev-mtlunt115    FRA                                      EXTERN                 0             528 538510              526 2                  264       .711                                       

ASM_dev-mtlunt105    FRA                                      EXTERN                 1             256 259551              254 1                  256       0                                          

ASM_drprdrdx         DROCR                                    EXTERN                 1              33 32375                32 1                   33       0                                          

ASM_pprrdx           OCR                                      EXTERN                 1              33 32375                32 1                   33       0                                          

ASM_PRDRDX           FRA                                      EXTERN                 1             384 387404              379 2                  192       3.773                                      

ASM_drprdrdx         FRA                                      EXTERN                 2             256 257208              252 2                  128       .082                                       

ASM_tbxcls           TBXFRA                                   EXTERN                 3              64 63007                62 1                   64       0                                          

ASM_dev-mtlunt85     REDO                                     EXTERN                 4              32 31454                31 1                   32       0                                          

ASM_val45            PIREL_DATA                               EXTERN                 5              96 93439                92 3                   32       .173                                        

ASM_ADOPTCRS         OCRDG                                    EXTERN                 9              33 29928                30 1                   33       0                                          

ASM_drptbxcls        TBXFRA                                   EXTERN                11              64 57730                57 1                   64       0                                          

ASM_dev-mtlunt119    REDORA                                   EXTERN                13              64 56920                56 1                   64       0                                          

ASM_prdlkcrs         OCR                                      EXTERN                14              33 29096                29 1                   33       0                                          

ASM_pprlkcrs         OCR                                      EXTERN                14              33 28998                29 1                   33       0                                           

ASM_pprrdx           FRA                                      EXTERN                17             256 216205              212 2                  128       .039                                       

ASM_mtlunt45         PIREL_DATA                               EXTERN                23              64 50329                50 1                   64       0                                          

ASM_tbxcls           TBXREDO                                  EXTERN                24              32 24388                24 1                   32       0                                          

ASM_dev-mtlunt119    FRAMBRDX                                 EXTERN                28              64 47071                46 1                   64       0                                          

ASM_pprrdx           REDO_RAD                                 EXTERN                29              64 46744                46 1                   64       0                                          

ASM_prdlkcrs         TEMPRAD                                  EXTERN                30              65 46585                46 1                   65       0                                          

ASM_pprlkcrs         TEMPRAD                                  EXTERN                31              65 45903                45 1                   65       0                                          

ASM_PRDRDX           REDO_RAD                                 EXTERN                34              64 43260                43 1                   64       0                                          

ASM_dev-mtlunt85     OTANOTDATA                               EXTERN                36              64 41624                41 1                   64       0                                           

ASM_dev-mtlunt105    DATARDXVAL_OCR                           EXTERN                38              64 40760                40 1                   64       0                                          

ASM_val45            PIREL_REDO                               EXTERN                38               1 617                   1 1                    1       0                                          

ASM_drptbxcls        TBXDATA                                  EXTERN                39             512 318164              311 2                  256       .004                                       

ASM_PRDRDX           OCR                                      EXTERN                40               1 594                   1 1                    1       0                                          

ASM_tbxcls           TBXOCR                                   EXTERN                40               1 594                   1 1                    1       0                                           

ASM_drptbxcls        TBXOCR                                   EXTERN                40               1 589                   1 1                    1       0                                          

ASM_tbxcls           TBXDATA                                  EXTERN                42             512 305256              299 2                  256       .015                                       

ASM_drprdrdx         REDO_RAD                                 EXTERN                42              64 38152                38 1                   64       0                                          

ASM_mtlunt45         PIREL_REDO                               EXTERN                47               1 523                   1 1                    1       0                                          

ASM_drptbxcls        TBXREDO                                  EXTERN                48              32 16729                17 1                   32       0                                          

ASM_prdlkcrs         RDXREDO                                  EXTERN                49              65 33585                33 1                   65       0                                          

ASM_pprlkcrs         RDXREDO                                  EXTERN                50              65 33559                33 1                   65       0                                          

ASM_prdlkcrs         DATARAD_OCR                              EXTERN                50             130 67048                66 2                   65       .072                                       

ASM_dev-mtlunt85     OTADEVDATA                               EXTERN                55              64 29652                29 1                   64       0                                           

ASM_ADOPTCRS         ELEVA_DG                                 EXTERN                59              64 27116                27 1                   64       0                                          

ASM_dev-mtlunt115    DATARDXQA                                EXTERN                60             792 322788              316 3                  264       .022                                       

ASM_dev-mtlunt119    DATAMBRDXVAL                             EXTERN                62             512 198216              194 2                  256       .01                                        

ASM_PRDRDX           DATARAD_OCR                              EXTERN                63              64 24432                24 1                   64       0                                          

ASM_drprdrdx         DATARAD_OCR                              EXTERN                63              64 24432                24 1                   64       0                                           

ASM_PRDRDX           TEMPRAD                                  EXTERN                63              64 24192                24 1                   64       0                                           

ORACLE12c SAP special parameters

SAP 12c
==============================================
_ktb_debug_flags = 8
_securefiles_concurrency_estimate = 50
_suppress_identifiers_on_dupkey = TRUE
_mutex_wait_time = 10
_mutex_wait_scheme = 1
_fix_control = 5099019:ON, 5705630:ON, 6055658:OFF, 6120483:OFF, 6399597:ON, 6430500:ON, 6440977:ON, 6626018:ON, 6972291:ON, 7168184:OFF, 7658097:ON, 8937971:ON, 9196440:ON, 9495669:ON, 13077335:ON, 1
3627489:ON, 14255600:ON, 14595273:ON, 18405517:2

_optimizer_extended_cursor_sharing_rel = NONE
_optimizer_adaptive_cursor_sharing = FALSE
_optim_peek_user_binds = FALSE
_optimizer_aggr_groupby_elim = FALSE
_optimizer_use_feedback = FALSE



_optimizer_batch_table_access_by_rowid  add with value "FALSE" ==>pour  fonctionner  comme  en 11
_advanced_index_compression_options     add with value "16"
_fix_control (20355502)                 add with value "20355502:8" ALTER SYSTEM SET "_FIX_CONTROL" ='20355502:8'  reduxtion des parse  pour query OR
_awr_mmon_deep_purge_all_expired        add with value "TRUE"  ==>purge AWR
_use_single_log_writer                  add with value "TRUE"  ==>bug  sur  AIX

Rename DG

Le but est de passer  le diskgroup POCDB à DATARAD et POCREDO à REDO_RAD.

On a une bd qui s appuie sur ces diskgroups.

De preference pratiquer sur de la non prod voir un poc afin d’etre à l aise.

La lecture des note oracle : Doc ID 948040.1, Doc ID 1335975.1 serait un plus.

exemple

Ci-dessous c’est un exemple bien sur à adapter.

#actions	commande	user
1	# srvctl config database -d POCRAD	oracle os
2	select 'ALTER DATABASE RENAME FILE '''||MEMBER||''' TO '''||REPLACE(MEMBER,'+POCREDO','+REDO_RAD')||'''; ' FROM v$logfile;	oracle sqlplus
3	Select 'ALTER DATABASE RENAME FILE '''||NAME||''' TO '''||REPLACE(NAME,'+POCDB','+DATARAD')||'''; ' from  V$tempfile;	oracle sqlplus
4	Select 'ALTER DATABASE RENAME FILE '''||NAME||''' TO '''||REPLACE(NAME,'+POCDB','+DATARAD')||'''; ' from  V$datafile;	oracle sqlplus
5	alter database disable block change tracking;	oracle sqlplus
	Attention au controlfiles
6	alter database flashback off;	oracle sqlplus
7	create pfile from spfile; modifier le  spfile  pour les  nouveau diskgroupe	oracle sqlplus
8	srvctl stop database -d POCRAD	oracle os
9	ASMCMD> lsdg lsdsk  -k  -G  <diskgroup>	grid  asmcmd
10	ASMCMD> umount POCDB	grid  asmcmd
11	ASMCMD> umount POCREDO	grid  asmcmd
12	#renamedg phase=both dgname=POCREDO newdgname=REDO_RAD verbose=true asm_diskstring='/dev/oracleasm/disks/*	grid  os
13	#renamedg phase=both dgname=POCDB newdgname=DATARAD verbose=true asm_diskstring='/dev/oracleasm/disks/*	grid  os
14	ASMCMD> lsdg	grid  asmcmd
15	ASMCMD> mount DATARAD	grid  asmcmd
16	ASMCMD> mount  REDO_RAD	grid  asmcmd
17	crsctl stat res -w 'TYPE = ora.diskgroup.type' –t	grid os
18	startup nomount pfile='/u01/oracle/product/11.2.0/db_1/dbs/initorcl.ora.bkp'	oracle sqlplus
19	alter database mount;	oracle sqlplus
20	Rejouer les scripts issus des etapes #2,#3,#4	oracle sqlplus
21	alter database mount;	oracle sqlplus
22	alter database open;	oracle sqlplus
23	create spfile from pfile=’xxxxxx’;	oracle sqlplus
24	srvctl modify database -d POCRAD -p '+REDO_RAD/POCRAD/PARAMETERFILE/spfilePOCRAD.ora'	oracle os
25	modifier le fichier initSID.ora pour ajouter le nouveau spfile (sur chaque noeud)	oracle os
26	srvctl modify database -d POCRAD -a "POCARCH,REDO_RAD,DATARAD"	oracle os
28	shutdown immediate	oracle sqlplus
29	srvctl start database -d POCRAD	oracle os
30	srvctl remove diskgroup -g POCREDO -f	oracle os
31	srvctl remove diskgroup -g POCDB -f	oracle os

Etape #9 : output  du  lsdsk

ASMCMD> lsdsk  -k  -G  datarad

Total_MB  Free_MB   OS_MB  Name          Failgroup     Failgroup_Type  Library  Label  UDID  Product  Redund   Path

  262064    23752  262064  DATARAD_0006  DATARAD_0006  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d14s0

  262064    23752  262064  DATARAD_0001  DATARAD_0001  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d18s0

  262064    23752  262064  DATARAD_0007  DATARAD_0007  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d19s0

ASMCMD> lsdsk  -k  -G redo_rad

Total_MB  Free_MB  OS_MB  Name           Failgroup      Failgroup_Type  Library  Label  UDID  Product  Redund   Path

   65448    56920  65448  REDO_RAD_0001  REDO_RAD_0001  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d16s0

Etape #12 :  outout  renamdg

[dev-mtlunt119:grid::/home/grid/mg]#renamedg phase=both dgname=REDO_RAD newdgname=REDORA verbose=true asm_diskstring='/dev/rdsk/*'

Parsing parameters..

Parameters in effect:

         Old DG name       : REDO_RAD

         New DG name          : REDORA

         Phases               :

                 Phase 1

                 Phase 2

         Discovery str        : /dev/rdsk/*

         Clean              : TRUE

         Raw only           : TRUE

renamedg operation: phase=both dgname=REDO_RAD newdgname=REDORA verbose=true asm_diskstring=/dev/rdsk/*

Executing phase 1

Discovering the group

Performing discovery with string:/dev/rdsk/*

Identified disk UFS:/dev/rdsk/c1d16s0 with disk number:1 and timestamp (33036270 -1617638400)

Checking for hearbeat...

Re-discovering the group

Performing discovery with string:/dev/rdsk/*

Identified disk UFS:/dev/rdsk/c1d16s0 with disk number:1 and timestamp (33036270 -1617638400)

Checking if the diskgroup is mounted or used by CSS

Checking disk number:1

Generating configuration file..

Completed phase 1

Executing phase 2

Looking for /dev/rdsk/c1d16s0

Modifying the header

Completed phase 2

Terminating kgfd context 1001bb480

Output   du lsdsk  post  rename:

ASMCMD> lsdsk  -k  -G datara

Total_MB  Free_MB   OS_MB  Name          Failgroup     Failgroup_Type  Library  Label  UDID  Product  Redund   Path

  262064    23752  262064  DATARAD_0006  DATARAD_0006  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d14s0

  262064    23752  262064  DATARAD_0001  DATARAD_0001  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d18s0

  262064    23752  262064  DATARAD_0007  DATARAD_0007  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d19s0

ASMCMD> lsdsk  -k -g redora

ASMCMD> lsdsk  -k  -G redora

Total_MB  Free_MB  OS_MB  Name           Failgroup      Failgroup_Type  Library  Label  UDID  Product  Redund   Path

   65448    56920  65448  REDO_RAD_0001  REDO_RAD_0001  REGULAR         System                         UNKNOWN  /dev/rdsk/c1d16s0

Erreur

Si on a  cette  erreur  dans les logs  cluster c est lie a golden gate :

2016-05-26 13:54:17.296: [   CRSPE][1177860416] {2:48629:58091} Register Resource Opora.DATARAD.dg] has failed:CRS-2566:

 User 'grid' does not have sufficient permissions to operate on resource 'pocrac-gg', which is part of the dependency specification.

Faire executer  par un root user  cette  commande :

crsctl  setperm resource  pocrac-gg -u user:grid:rwx