How to check whether Oracle instance is started using pfile or spfile?
This shows database was started by spfile
SQL> show parameter spfile;
NAME TYPE VALUE
---- ---- ----------------------------------------------------------
spfile string /root/apps/oracle/10g/dbs/spfile<DB_NAME>.ora
No values returned means that its started by pfile
.
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';