Script:找出ASM中的Spfile参数文件

以下脚本可以用于找出ASM存储中的Spfile参数文件,因为使用asmcmd去查找很不方便,而spfile丢失又是很头大的事情, 所以有一个脚本代劳可以省不少功夫呢!

 

 

--- listspfiles.sql
--- Purpose: Sample script to list spfiles kept in ASM instance
--- Usage: This should be run against an ASM instance,
--- not a database instance.
---
--- cut here --%<----%<----%<----%<----%<----%<--

--list all spfiles

set lines 120
col full_path for a110
SELECT full_path, dir, sys
FROM
(SELECT
CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT g.name gname,
a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%SPFILE%'
/

Sample output:

FULL_PATH                                                                                                      D S
-------------------------------------------------------------------------------------------------------------- - -
+DATA/Aspfile.ora                                                                                              N N
+DATA/VPROD/PARAMETERFILE/spfile.273.766620265                                                                 N Y
+DATA/VPROD/PARAMETERFILE/spfile.365.773976489                                                                 N Y
+DATA/VPROD/spfileVPROD.ora                                                                                    N N

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

Comments

  1. derek he says:

    在我的机器上执行结果如下,但我在手工启动数据库实例时,始终在文件系统$ORACLE_HOME/dbs目录下去找参数文件,为何没有在ASM磁盘组中去找SPFILE?
    +DISKDG3/YKORACLE/PARAMETERFILE/spfile.256.796390845 N Y
    +DISKDG3/YKORACLE/spfileykoracle.ora N N

    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
    PL/SQL Release 11.2.0.1.0 – Production
    CORE 11.2.0.1.0 Production
    TNS for Linux: Version 11.2.0.1.0 – Production
    NLSRTL Version 11.2.0.1.0 – Production

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569