How to find database is using Spfile or Pfile in Oracle 10g

Sometimes DBA needs to find out database is running by spfile or pfile. Let us display this with an example. To demonstrate I start a database using pfile like

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='D:\oracle\product\10.2.0\db_1\database\INITstan.ORA'
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 218106756 bytes
Database Buffers 385875968 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

Command to check database is using pfile or spfie

SQL> select name,value from V$parameter where name like 'spfile' order by name;
NAME VALUE
--------------------------------------------------------------------------------
Spfile

No value is displayed because database is not using spfile it is using pfile. It means database is using pfile for startup.

Now, I startup a database with spfile.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Spfile is default file for database start up, that is the reason I did not give any parameter name at start up time.

SQL> startup
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 222301060 bytes
Database Buffers 381681664 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

Now I check with same query.

SQL> select name,value from V$parameter where name like 'spfile' order by name;
NAME VALUE
--------------------------------------------------------------------------------
Spfile D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILESTAN.ORA

It gives path of spfile file which is getting used by database for starting up. It means my database is using spfile. To know more about Spfile Commands.

1 comment: