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
[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>
[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/