Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

📚 preface

When the Oracle database is started, the parameter file is required in the nomount state.

Pfile is an Initialization Parameters file.

☀ ️ pfile is introduced

Initsid. ora is a text file.

Prior to Oracle 9i, Oracle used pfile to store initialization parameter Settings. Modification of the parameter file was manual. These parameters were read at instance startup, and modification of pfile required restart of the instance to take effect.

The following is an example of a standalone database in Linux:

You can view the contents of the file using the strings command:

[oracle @ former: / u01 / app/oracle/product / 12.2.0 / db/DBS] $strings initorcl. Ora corp __data_transfer_cache_size = 0 orcl.__db_cache_size=457179136 orcl.__inmemory_ext_roarea=0 orcl.__inmemory_ext_rwarea=0 orcl.__java_pool_size=4194304 orcl.__large_pool_size=8388608 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=180355072
orcl.__sga_target=713031680
orcl.__shared_io_pool_size=25165824
orcl.__shared_pool_size=201326592
orcl.__streams_pool_size=0
*._optimizer_cartesian_enabled=FALSE
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='12.2.0'
*.control_files='/oradata/orcl/control01.ctl'.'/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.db_name='orcl'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.event='10949 trace name context forever:28401 trace name context forever,level 1:10849 trace name context forever, level 1:19823 trace name context forever, level 90'
*.local_listener='LISTENER_ORCL'
*.log_archive_dest_1='LOCATION=/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=170m
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=0
*.sga_target=679m
*.undo_tablespace='UNDOTBS1'
Copy the code

Db_name, version of the database, location of control files, memory allocation, path of some system files, character set, number of sessions, etc., some basic database information.

If you need to modify the pfile file, because it is a text file, you can directly open the file to modify.

The initsid. ora file is used for database RMAN backup and restoration.

📢 Note: If a database is started using pfile and the parameters are changed online using ALTER system and ALTER SESSION, they will only be saved to the memory and become invalid after restart. If the restart still takes effect, manually modify the pfile parameter file.

⭐️ Parameter file location

The pfile parameter file usually exists in the following directories:

  • Windows： $ORACLE_HOME/database
  • Linux: $ORACLE_HOME/DBS
  • The pfile file format is as follows:initSID.ora

🌛 Example Explanation

① After the pfile is used to start the database and modify the parameters, the parameters become invalid after the database restarts

If the pfile file is used to start the database, will the parameters still take effect after the database is restarted after being changed by alter system?

Here’s another experiment:

1, confirm that the database environment is pfile file startup:

sqlplus / as sysdba
show parameter spfile
Copy the code

2. Modify parameters online:

sqlplus / as sysdba
alter system set undo_retention=1000;
show parameter undo_retention
Copy the code

3. Restart the database and check whether the parameters take effect.

sqlplus / as sysdba
shutdown immediate
startup
show parameter undo_rentention
Copy the code

Through the above demonstration, it is found that pfile starts the database, online change of dynamic parameters will only take effect in memory, but will be invalid upon restart.

SQL > alter initsid. ora;

cd $ORACLE_HOME/dbs
vi initorcl.ora
Copy the code

Add the parameters you want to change in the last line, as shown in the figure above.

5. Restart the database to take effect:

sqlplus / as sysdba
shutdown immediate
startup
Copy the code

Using the pfile parameter to start the database, each change in the parameter requires a restart of the database for permanent effect, so it is extremely troublesome, so it is not recommended, except in special circumstances.

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.

❤️ technical exchange can follow the public number: Lucifer think twice before you do ❤️