Forgot your password?    
+ Reply to Thread
Results 1 to 5 of 5

Thread: Alteration of Modifiable and Non Modifiable parameters using Spfile/Pfile

  1. #1

    Alteration of Modifiable and Non Modifiable parameters using Spfile/Pfile

    Characteristics of the Intialization Parameter File
    • A text file
    • Naming convention is <init>sid.ora
    • Default location: $ORACLE_HOME/dbs (Linux)
    • Editable
    • Dynamic changes affect only the currently running instance.
    • Permanent changes require the pfile to be opened, edited and a restart to the database.

    Dynamic changes to the Pfile
    To begin let us say the value of the DB_CACHE_SIZE initialization parameter is some value X.
    To change the value of the DB_CACHE_SIZE initialization parameter for the currently running instance, you can issue the command:

    ALTER SYSTEM SET DB_CACHE_SIZE=new_value; -- say this new_value is represented by Y.
    This change would come into effect and would affect the currently running instance only.
    If the database is restarted, the previous value that existed (X) in the parameter file would be used for configuring the size of the database buffer cache.
    If you want to permanently change the size of the database buffer cache to Y, you would need to open the PFILE, and edit the initialization parameter to take the value Y.

    To bring this into effect you would need to restart the database.
    Problem : The parameter file consists of parameters and value and the likelihood of accidentally making an error is very high. If you accidentally make an incorrect change or have a typo or change the wrong parameter, the database will not start up properly. This problem is very effectively handled by the Persistent Parameter file (SPFILE).

    Characteristics of the Persistent (Server) Parameter File
    • A binary file
    • Naming convention is spfile<sid>.ora
    • Default location: $ORACLE_HOME/dbs (Linux)
    • Not Editable
    • Dynamic changes may affect only the currently running instance, only future instances or both currently running and future instances.

    Dynamic changes to the SPFILE
    To begin let us say the value of the DB_CACHE_SIZE initialization parameter is some value X in the SPFILE.
    When using the SPFILE you can use the SCOPE keyword. The keyword takes the following options:
    SCOPE=MEMORY => Change affects only current running instance
    SCOPE=SPFILE => Change is written into the spfile and affects only future instances (after database restart)
    SCOPE=BOTH => Change is written into the spfile and also affects the currently running instance. Because the change is made to the spfile, the changed value remains in effect after database restart.

    To change the value of the DB_CACHE_SIZE initialization parameter for the currently running instance, we can issue the command:
    ALTER SYSTEM SET DB_CACHE_SIZE=new_value SCOPE=MEMORY; -- say this new_value is represented by Y.
    This change would come into effect and would affect the currently running instance only.

    To change the value of the DB_CACHE_SIZE initialization parameter for only future instances you would issue:
    ALTER SYSTEM SET DB_CACHE_SIZE=new_value SCOPE=SPFILE;
    -- say this new_value is represented by Y.
    This change will not affect the currently running instance. However upon database restart the new value Y will be in effect. This type of a change is useful for those initialization parameter that cannot be changed dynamically.

    To change the value of the DB_CACHE_SIZE initialization parameter for the currently running instance and for future instances, you can issue the command:
    ALTER SYSTEM SET DB_CACHE_SIZE=new_value SCOPE=BOTH; -- say this new_value is represented by Y.
    This change would come into effect and would affect the currently running instance as well as future instances. When the database is restarted the new value Y will be in effect.

    When using SPFILE , if the scope is not specified , BOTH is the default scope.

    Based on the above discussion on the SPFILE you should note that the user never directly opens and modifies the file. Any change to the parameter file is made by issuing the ALTER SYSTEM command which causes Oracle to write into the file. This removes any possibility of user errors created by manually editing the file.

    Regards
    Jitendra
    Last edited by Hemant; 11-01-2011 at 07:07 PM.

  2. #2
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427
    Thanks for the informative post Jitendra !

  3. #3
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    11
    good work Jitu...Keep it up.

  4. #4
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    17
    Nice work!!

  5. #5
    Nice and informative...

+ Reply to Thread

Similar Threads

  1. RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN
    By devesh_ocp in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 04-25-2012, 10:26 AM
  2. What is difference between PFile and SPFile?
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 04-24-2012, 03:39 PM
  3. Oracle 11g create spfile from memory.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 12-22-2011, 06:10 PM
  4. Init.ora Parameters and Unix Kernel
    By Amit in forum Database Installation,Upgrades,Migration,Patching and Cloning
    Replies: 1
    Last Post: 11-04-2011, 05:54 PM
  5. DATAPUMP EXCLUDE/INCLUDE parameters
    By Amit in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 11-04-2011, 02:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics