How To Get DDL From a Diskgroup

This week I had to rebuild several diskgroups asm for a new environment that I have mounted, the issue is that I did not find a “DBMS_METADATA” or a script on the internet that would serve me for what I wanted to do, on the other hand it is a simple thing and can that being something relatively easy nobody has published something similar.

Here I put the little script that I put in if it can help someone.

 

SET SERVEROUTPUT ON SIZE 100000
SET LINES 1000
SET PAGES 1000SET VER OFF 

DECLARE
IS_FIRST_TIME  BOOLEAN := TRUE;
LINEA VARCHAR2 (100);
BEGIN
DBMS_OUTPUT.PUT_LINE ('==========================================================');
/* Create command */
select 'CREATE DISKGROUP ' || '&1' INTO LINEA FROM DUAL;
DBMS_OUTPUT.PUT_LINE (LINEA);
/* Redundancy type */
select decode ( type, 'EXTERN','EXTERNAL', type)  || ' REDUNDANCY DISK' INTO LINEA from v$asm_diskgroup where name =upper ( '&&1' );
DBMS_OUTPUT.PUT_LINE (LINEA);
/* Disks associated to the DG */
FOR i IN  (
SELECT PATH  FROM V$ASM_DISK JOIN  V$ASM_DISKGROUP ADG USING (GROUP_NUMBER)
WHERE ADG.name =   upper ( '&&1' )
)  LOOP
/* Put comma if multirows are returned */
IF NOT IS_FIRST_TIME THEN
DBMS_OUTPUT.PUT (',');
END IF;
DBMS_OUTPUT.PUT_LINE (i.path);
IS_FIRST_TIME := FALSE;
END LOOP i;
/* Allocation unit */
SELECT 'ATTRIBUTE ''au_size''=''' || ALLOCATION_UNIT_SIZE/1048576 || 'M'','  INTO LINEA from v$asm_diskgroup where name =  upper ( '&&1' );
DBMS_OUTPUT.PUT_LINE (LINEA);
/* Compatible ASM */
SELECT  '''compatible.asm''=''' || VALUE  || ''',' INTO LINEA  FROM V$ASM_ATTRIBUTE A JOIN  V$ASM_DISKGROUP ADG USING (GROUP_NUMBER)  WHERE ADG.name =  upper ( '&&1' )
AND A.NAME = 'compatible.asm';
DBMS_OUTPUT.PUT_LINE (LINEA);
/* Compatible RDBMS */
SELECT  '''compatible.rdbms''=''' || VALUE  || ''';'  INTO LINEA  FROM V$ASM_ATTRIBUTE A JOIN  V$ASM_DISKGROUP ADG USING (GROUP_NUMBER)  WHERE ADG.name =  upper ( '&&1' )
AND A.NAME = 'compatible.rdbms';
DBMS_OUTPUT.PUT_LINE (LINEA);
END;
/

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s