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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. RMAN: SET NEWNAME Command Using SQL
  2. Unable to instantiate disk “ASM_DISK” – error on running ‘oracleasm scandisks’ command
  3. How To Add New Disk to An Existing Diskgroup on RAC Cluster or Standalone ASM Configuration
  4. Oracle Database – How to recover from a lost datafile with no backup
  5. Oracle RAC : understanding split brain
  6. How To Setup UDEV Rules For RAC OCR And Voting Devices on Partitions
  7. Oracle Database – How to Recover from a Lost or Deleted Datafile with Different Scenarios
  8. Oracle Database 18c new feature – Scalable Sequences
  9. How to Find OCR Master Node
  10. What is the Search Order for TNS files – listener.ora, sqlnet.ora, tnsnames.ora

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary