• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle database – How to create pfile or spfile using the current parameters

by admin

In Oracle Database 11g and onwards, the FROM MEMORY clause creates a pfile or spfile using the current systemwide parameter settings. In a RAC environment, the created file contains the parameter settings from each instance.

During instance startup, all parameter settings are logged to the alert.log file. As of Oracle Database 11g, the alert.log parameter dump text is written in valid parameter syntax. This facilitates cutting and pasting of parameters into a separate file and then using as a pfile for a subsequent instance.

The name of the pfile or spfile is written to the alert.log at instance startup time. In cases when an unknown client-side pfile is used, the alert log indicates this as well.

To support this additional functionality, the COMPATIBLE initialization parameter must be set to 11.0.0.0 or higher.

The FROM MEMORY clause allows the creation of current systemwide parameter settings. Below is syntax of using this feature:

CREATE PFILE [= 'pfile_name' ] FROM { { SPFILE [= 'spfile_name'] } | MEMORY };
CREATE SPFILE [= 'spfile_name' ] FROM { { PFILE [= 'pfile_name' ] } | MEMORY } ;

Example:

SQL>create pfile='$ORACLE_HOME/dbs/initdb11g.txt' from memory; 

File created.

Conclusion

For RAC setup, this method does not take into consideration parameters prefixed with *., thus extra steps will be needed including capturing the text-based parameter file for each instance, merging the 2 and adjusting all the instance-specific parameter settings to include the instance_name for each instance. Using the merged file you would then need to create spfile from this merged text-based pfile.

Oracle database : Basics about pfile and spfile

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to load SELinux Module For Oracleasm
  2. How To Automate The Opening Of Pluggable Databases After The CDB Starts Up in Oracle 12c
  3. Simple Steps to use LogMiner for finding high redo log generation
  4. SQL: Difference between delete and truncate
  5. Oracle SQLT (SQLTXPLAIN) Interview Questions
  6. Beginners Guide to Oracle Database Vault
  7. Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
  8. How to Audit Alter Tablespace in Oracle 12c database
  9. Beginners Guide to Oracle Temporary Tablespace Groups
  10. How to Modify an Existing ASM Spfile in a RAC Environment

You May Also Like

Primary Sidebar

Recent Posts

  • powertop Command Examples in Linux
  • powertop: command not found
  • powerstat: command not found
  • powerstat Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright