Search This Blog

Total Pageviews

Saturday, 7 April 2018

Spfile asm in ASM 11gR2/12C

Spfile asm in ASM  11gR2/12C


How to find out spfile in +ASM
How to find out OCR in +ASM 


SQL> !olsnodes -c

olsnodes -c  <<<< To Find Out cluster Name 

set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
                  where a.group_number = b.group_number
                        and (mod(a.parent_index, power(2, 24))) = 0
                        and a.name = LOWER('&CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;

with date

alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set linesize 200
col FILES_OF_CLUSTER for a80
select concat('+'||gname, sys_connect_by_path(aname, '/')) FILES_OF_CLUSTER,CREATION_DATE  from ( select b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type,c.CREATION_DATE
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number                = b.group_number
             and a.group_number     = c.group_number(+)
             and a.file_number      = c.file_number(+)
             and a.file_incarnation = c.incarnation(+)
     ) WHERE file_type in ( 'ASMPARAMETERFILE','OCRFILE','PARAMETERFILE')
start with (mod(pindex, power(2, 24))) = 0
            and rindex in( select a.reference_index  from v$asm_alias a, v$asm_diskgroup b
            where a.group_number = b.group_number
            and (mod(a.parent_index, power(2, 24))) = 0
--          and a.name = LOWER('CLUSTER_NAME')  -------<<<<<< Cluster Name 
                )
connect by prior rindex = pindex;


ASMCMD [+] > find --type PARAMETERFILE '*' '*'
ASMCMD [+] > spget
ASMCMD> spcopy +DATA/racluster/asmparameterfile/registry.253.792630659 /tmp/spfilecopyASM.ora
ASMCMD> spbackup +DATA....   +LOGS/spfileBackASM.bak

4 comments:

Anuj Singh said...

gpnptool get -o- | xmllint --format - | grep SPFile

Anuj Singh said...

asmcmd find --type ASMPARAMETERFILE "*" "*"

Anuj Singh said...



for password file

ASMCMD [+] > pwget --asm
+DATA/orapwasm


Oracle 12c
set linesize 300
col LAST_LOGIN for a20
col USERNAME for a20
col EXTERNAL_NAME for a20
col PASSWORD_PROFILE for a20
col LAST_LOGIN for a20
select * from v$pwfile_users;

Anuj Singh said...

For control file
ASMCMD [+] > find --type CONTROLFILE +data *

Oracle DBA

anuj blog Archive