iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 07

"If you want to make an apple pie from scratch, you must first create the universe." - Carl Sagan

 

Read first then play the video:

   DBA-VIDEO -Server Parameter File-SPFILE

   

Server Parameter File-SPFILE

 

Introduction

As a DBA, you are responsible for changing memory size allocations while the database is on. You need to use the Server Parameter File to do so. Your jobís responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

 

Creating the Server Parameter File (SPFILE)

Using the MEMORY option

Using the SPFILE

Using the BOTH

Using the Server Parameter File (SPFILE)

Setting the RESOURCE_MANAGER_PLAN parameter

Creating the Parameter File (PFILE)

Using the Parameter File (PFILE)

Commands:

ALTER SYSTEM SUSPEND

ALTER SYSTEM RESUME

ALTER SYSTEM ENABLE RESTRICTED SESSION

ALTER SYSTEM QUIESCE RESTRICTED

ALTER SYSTEM command

 

Hands-on

In this exercise you will learn how to create and use the Server Parameter File (SPFILE).


Connect to a database
Connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@school AS SYSDBA


Set Oracle Parameter Dynamically
Let us try to change an Oracle parameter dynamically and set the RESOURCE_MANAGER_PLAN parameter to SYSTEM_PLAN dynamically into the Server Parameter File (SPFILE).
SQL> ALTER SYSTEM SET resource_manager_plan='SYSTEM_PLAN'

SCOPE=SPFILE
/
Notice that at this time, you are not able to change any parameter dynamically. You need to open your database using the Server Parameter File in order to do that.

 

Server Parameter File

The Server Parameter File (SPFILE) enables you to relieve yourself of the burden of constantly updating your parameter file (init<sid>.ora). You create the SPFILE to make it possible to change almost every initialization parameter you desire dynamically while the database is online and available for users. Remember that when you startup the database, Oracle first looks for the SPFILE. If it was not found, then it will check for the PFILE file.

 

Create a SPFILE
Let's create a SPFILE from the database parameter file (PFILE).
SQL> CREATE SPFILE

               FROM

               PFILE='%ORACLE_HOME%.ora'
/
Notice that the default location of SPFILE is %ORACLE_HOME%. You can save the SPFILE in an specific location.

Create a SPFILE in the c:directory using the default Parameter file (PFILE). Make a directory first, if you donít have that directory
SQL> HOST MKDIR c: -- Make a directory

SQL> CREATE SPFILE='c:\xxxxx\<SID>pfile.ora' FROM PFILE
/


Start a database with SPFILE
Shutdown and startup the database with the Server Parameter File. Remember that the default file is the Server Parameter File (SPFILE). Once you created the SPFILE, you donít need to specify it in your startup command. You have to shutdown and startup to activate the use of the SPFILE. To startup with the SPFILE option, it makes it possible to change almost every initialization parameter you want dynamically while the database is online and available for users.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Now, let us to change the RESOURCE_MANAGER_PLAN parameter to SYSTEM_PLAN dynamically into the Server Parameter FILE (SPFILE).
SQL> ALTER SYSTEM SET resource_manager_plan='SYSTEM_PLAN'

               SCOPE=SPFILE
/
This time you should be able to change the parameter.


SCOPE options
The scope can be MEMORY, SPFILE, or BOTH. Use the MEMORY option if you don't want to keep the changes. Use the SPFILE option if you want to be active when you reboot the next time. Use the BOTH option if you want to change it immediately and keep the changes. Notice that the default option is always BOTH.

 

Alter system parameters dynamically
Practice to change some of the Oracle parameters dynamically.

Change the SHARED_POOL_SIZE to 20 megabytes dynamically in the memory.
SQL> ALTER SYSTEM SET shared_pool_size = 20000000

SCOPE=MEMORY
/

Query the SHARED_POOL_SIZE parameter information.
SQL > SHOW PARAMETER shared_pool_size
Notice that the SHARED_POOL_SIZE parameter was changed.

 

"Argue for your limitations, and sure enough they're yours." - Richard Bach

 

Questions:

Q: Describe the Server Parameter File.

Q: Describe the MEMORY option in the ALTER SYSTEM statement.

Q: Describe the SPFILE option in the ALTER SYSTEM statement.

Q: Describe the BOTH option in the ALTER SYSTEM statement.

Q: How can you create the Server Parameter File?

Q: What are the differences between SPFILE and PFILE?

Q: How do you change a database dynamically?

Q: What does the following statement do?

SQL> CREATE SPFILE

               FROM

               PFILE='%ORACLE_HOME%.ora'
/