|
"If you want to
make an apple pie from scratch, you must first create the
universe." - Carl Sagan |
Read
first then play the video:
DBA006(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:.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'
/
|