Oracle Database 12c Parameter Files ( Spfile & Pfile )

Mehmet Salih Deveci March 19, 2019 13 Comments

Hi,

In this article, I will explain the parameter files that are very important for Oracle database. This parameter is created when opening the Oracle instance and then we can change the contents of these parameter files either dynamically or statically.Oracle In our database there are 2 identical parameter files, which are named SPFILE and PFILE. I will examine both of them separately below.

SPFILE (Server Parameter File): It is a physical file with .ora extension in binary format where the parameters required for configuring the instance are stored when opening Oracle instance. This file is typically located under the $ ORACLE_HOME/dbs directory and spfile<ORACLE_SID>.ora format. We can say that this file is Oracle Instance configuration file that all parameters are kept for startup of Oracle instance

You can also find the location of this file from the database as follows.

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 1 14:26:31 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/12.1.2/db/dbs/
                                                 spfileDEVECI.ora
SQL>

This file is used in the nomount step when starting the database, where the following parameters are read by Oracle Instance. The instance cannot be started without a spfile or pfile file because the controlfile’s address, which is the brain of the database, is contained in this parameter file. Because this file is in binary format, we cannot open it directly, but we can convert it to user-understandable form using strings as follows. To describe the importance of the Spfile, I will list the parameters in this parameter file as follows.

[oracle@MehmetSalih ~]$ strings /oracle/product/12.1.2/db/dbs/spfileDEVECI.ora
DEVECIDB.__data_transfer_cache_size=0
DEVECI.__data_transfer_cache_size=0
DEVECIDB.__db_cache_size=2818572288
DEVECI.__db_cache_size=2332033024
DEVECIDB.__inmemory_ext_roarea=0
DEVECI.__inmemory_ext_roarea=0
DEVECIDB.__inmemory_ext_rwarea=0
DEVECI.__inmemory_ext_rwarea=0
DEVECIDB.__java_pool_size=16777216
DEVECI.__java_pool_size=16777216
DEVECIDB.__large_pool_size=33554432
DEVECI.__large_pool_size=33554432
DEVECIDB.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
DEVECI.__oracle_base='/oracle/product'#ORACLE_
BASE set from environment
DEVECIDB.__pga_aggregate_target=1191182336
DEVECI.__pga_aggregate_target=1191182336
DEVECIDB.__sga_target=3774873600
DEVECI.__sga_target=3774873600
DEVECIDB.__shared_io_pool_size=201326592
DEVECI.__shared_io_pool_size=201326592
DEVECIDB.__shared_pool_size=687865856
DEVECI.__shared_pool_size=1174405120
DEVECIDB.__streams_pool_size=0
DEVECI.__streams_pool_size=0
*.audit_file_dest='/oracle/product/admin/DEVECIDB/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/oradata/DEVECIDB/control01.ctl','/oracle/product/fast_recovery_area/DEVECIDB/control02.ctl'
*.db_block_size=8192
*.db_name='DEVECIDB'
*.db_recovery_file_dest='/oracle/product/fast_recovery_area/DEVECIDB'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVECIDBXDB)'
*.local_listener='LISTENER_DEVECIDB'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1121m
*.processes=300
emote_login_passwordfile='EXCLUSIVE'
*.sga_target=3600m
*.undo_tablespace='UNDOTBS1'
[oracle@MehmetSalih ~]$

I have specified some of the very important parameters above in bold letters, without reading these parameters Oracle cannot start instance. When the database is first started, we can query the parameters that should and should be very important from the database as follows.

SQL> select name from v$parameter where isbasic='TRUE';

NAME
--------------------------------------------------------------------------------
processes
sessions
nls_language
nls_territory
sga_target
control_files
db_block_size
compatible
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1

NAME
--------------------------------------------------------------------------------
log_archive_dest_state_2
cluster_database
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest
db_recovery_file_dest_size
undo_tablespace
instance_number
ldap_directory_sysauth
remote_login_passwordfile

NAME
--------------------------------------------------------------------------------
db_domain
shared_servers
remote_listener
db_name
db_unique_name
open_cursors
star_transformation_enabled
pga_aggregate_target

PFILE (Parameter File): The Pfile parameter file is the same as Spfile in its content and role, but the pfile is kept in normal character format on the operating system, not in binary. The PFile file is created with the name init <ORACLE_SID>.ora on the operating system under $ORACLE_HOME/dbs directory. PFILE is not require for database if spfile is exists. Spfile file is created when the database is installed and the pfile file is not created automatically. The pfile file is created from the spfile because it is sometimes easier to change a parameter at the pfile because the Pfile file is textual. The pfile file from the spfile file is created as follows.

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 14:39:55 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ~]$ ls -l /oracle/product/12.1.2/db/dbs/init*
-rw-r--r--. 1 oracle oinstall 3079 May 15  2015 /oracle/product/12.1.2/db/dbs/init.ora
-rw-r--r--. 1 oracle oinstall 1549 Mar 19 14:39 /oracle/product/12.1.2/db/dbs/initDEVECI.ora
[oracle@MehmetSalih ~]$

You can see content of pfile with linux cat command like following.

  [oracle@MehmetSalih ~]$ cat /oracle/product/12.1.2/db/dbs/initDEVECI.ora
DEVECIDB.__data_transfer_cache_size=0
DEVECI.__data_transfer_cache_size=0
DEVECIDB.__db_cache_size=2818572288
DEVECI.__db_cache_size=2332033024
DEVECIDB.__inmemory_ext_roarea=0
DEVECI.__inmemory_ext_roarea=0
DEVECIDB.__inmemory_ext_rwarea=0
DEVECI.__inmemory_ext_rwarea=0
DEVECIDB.__java_pool_size=16777216
DEVECI.__java_pool_size=16777216
DEVECIDB.__large_pool_size=33554432
DEVECI.__large_pool_size=33554432
DEVECIDB.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
DEVECI.__oracle_base='/oracle/product'#ORACLE_BASE set from environment
DEVECIDB.__pga_aggregate_target=1191182336
DEVECI.__pga_aggregate_target=1191182336
DEVECIDB.__sga_target=3774873600
DEVECI.__sga_target=3774873600
DEVECIDB.__shared_io_pool_size=201326592
DEVECI.__shared_io_pool_size=201326592
DEVECIDB.__shared_pool_size=687865856
DEVECI.__shared_pool_size=1174405120
DEVECIDB.__streams_pool_size=0
DEVECI.__streams_pool_size=0
*.audit_file_dest='/oracle/product/admin/DEVECIDB/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/oradata/DEVECIDB/control01.ctl','/oracle/product/fast_recovery_area/DEVECIDB/control02.ctl'
*.db_block_size=8192
*.db_name='DEVECIDB'
*.db_recovery_file_dest='/oracle/product/fast_recovery_area/DEVECIDB'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEVECIDBXDB)'
*.local_listener='LISTENER_DEVECIDB'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1121m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3600m
*.undo_tablespace='UNDOTBS1'
[oracle@MehmetSalih ~]$

As I mentioned above, one of them ( spfile or pfile ) is enough to start the Oracle instance, but if both files are present when starting the instance, Oracle is started using the spfile automatically.

Since our Spfile file is very important, it is necessary to take backup of the spfile continuously with the full backups . In order for the Spfile file to be backed up automatically, the database must be in autobackup mode. We can do this on the RMAN tool as below.

[oracle@MehmetSalih ~]$  rman target /

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

If, as above, the backup file is automatically retrieved, a backup copy of the Spfile is copied to the path where Autobackup is set.

[oracle@MehmetSalih ~]$ rman target / 

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEVECI are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/recovery_area/ctrl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO 4 G;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/12.1.0/db/dbs/snapcf_TESTDB.f';

RMAN>

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

13 comments

Nick Kocik
March 24, 2019 at 11:45 am
Keep up the superb work , I read few blog posts on this site and I think that your blog is really interesting and has bands of good information.
Kareen Saechao
May 19, 2019 at 2:13 am
It’s a pity you don’t have a donate button! I’d without a doubt donate to this excellent blog! I guess for now i’ll settle for bookmarking and adding your RSS feed to my Google account. I look forward to new updates and will share this blog with my Facebook group. Chat soon!|
- Mehmet Salih Deveci
  May 20, 2019 at 8:22 am
  Thank you so much for your nice comments
Ava Castrovinci
May 19, 2019 at 9:25 pm
That is a really good tip especially to those fresh to the blogosphere. Short but very accurate info… Many thanks for sharing this one. A must read article!|
- Mehmet Salih Deveci
  May 19, 2019 at 11:53 pm
  Thank you..
Kerri Engberg
May 20, 2019 at 2:56 am
Nice blog here! Also your web site loads up very fast! What web host are you using? Can I get your affiliate link to your host? I wish my website loaded up as fast as yours lol|
- Mehmet Salih Deveci
  May 20, 2019 at 8:11 am
  Thank you, turhost.com this is myhosting company.
Tayna Mazer
May 20, 2019 at 6:33 am
I blog often and I genuinely appreciate your information. This article has really peaked my interest. I will book mark your website and keep checking for new details about once per week. I opted in for your RSS feed too.|
- Mehmet Salih Deveci
  May 20, 2019 at 8:07 am
  Thank you
Barney Kriese
May 20, 2019 at 7:42 am
Hi there, I enjoy reading through your post. I like to write a little comment to support you.|
- Mehmet Salih Deveci
  May 20, 2019 at 8:06 am
  Thank you
Davut Keskin
May 21, 2019 at 2:18 pm
You write very useful articles. I congratulate you
- Mehmet Salih Deveci
  May 21, 2019 at 2:52 pm
  Thank you

IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial

Oracle Database 12c Parameter Files ( Spfile & Pfile )

Do you want to learn Oracle Database for Beginners, then read the following articles.

About Mehmet Salih Deveci

13 comments

Leave a Reply Cancel reply