IS MY DATABASE USING PFILE OR SPFILE?
Spfile
0
QUESTIONS
How to check whether an Oracle instance is started using pfile or spfile?
REQUEST
To get a full path of an initialization parameter by querying of a database.
RELATED
SOLUTION
SET LINES 300
SET PAGES 999
COL "FILE_TYPE" FOR A9
COL "PATH" FOR A50
SELECT DECODE(VALUE, NULL, 'PFILE', 'SPFILE') "FILE_TYPE",
DECODE(VALUE, NULL, (SELECT NVL(SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '\', -1, 2) -1),
SUBSTR(FILE_SPEC, 1, INSTR(FILE_SPEC, '/', -1, 2) -1)) || '/dbs/init' || INSTANCE_NAME || '.ora'
FROM DBA_LIBRARIES, V$INSTANCE
WHERE LIBRARY_NAME = 'DBMS_SUMADV_LIB'), VALUE ) "PATH" FROM SYS.V_$PARAMETER
WHERE NAME = 'spfile';
-- PFILE
FILE_TYPE PATH
--------- --------------------------------------------------
PFILE /oracle/product/12.2.0.1/dbs/initDBPILOT.ora
Spfile can be easily created by
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP
-- SPFILE
FILE_TYPE PATH
--------- --------------------------------------------------
SPFILE /oracle/product/12.2.0.1/dbs/spfileDBPILOT.ora
Version : 10:38 19.01.2018
Database : Oracle database 10g – 12cR2
Database : Oracle database 10g – 12cR2
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum