Topics: Server Parameter
File-SPFILE
|
More Resources by
Google: |
|
|
|
|
Hands-On 06 (Server
Parameter File-SPFILE)
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 06 (Server Parameter File-SPFILE)
-- Preparation
set echo on
connect system/manager@school as sysdba
shutdown immediate
connect system/manager@school as sysdba
STARTUP PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10
pause
--Start
CLEAR SCR
-- In this exercise you will learn how to create and use
-- the Server Parameter File (SPFILE).
-- Connect to SQL*Plus as the system/manager user.
pause
CONNECT system/manager@school AS SYSDBA
pause
CLEAR SCR
-- Set the RESOURCE_MANAGER_PLAN parameter to SYSTEM_PLAN
-- dynamically into the Server Parameter File (SPFILE).
pause
ALTER SYSTEM SER resource_manager_plan='SYSTEM_PLAN' SCOPE=SPFILE
/
-- Notice that you are not able to change any parameter dynamically.
pause
CLEAR SCR
-- The Server Parameter File (SPFILE) enables you to relieve
-- yourself of the burden of constantly updating your parameter
-- file (init.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.
pause
pause
CLEAR SCR
-- Let's create a SPFILE from the database parameter file (PFILE).
pause
CREATE SPFILE FROM PFILE='%ORACLE_HOME%\admin\school\pfile\init.ora'
/
-- Notice that the default location of SPFILE is %ORACLE_HOME%\database.
pause
CLEAR SCR
-- Create a SPFILE in the c:\spfile directory using the default Parameter
-- file (PFILE).
pause
HOST MKDIR c:\spfile
CREATE SPFILE='c:\spfile\spfileschool.ora' FROM PFILE
/
pause
CLEAR SCR
-- Shutdown and startup the database with the Server Parameter
-- File. Remember that the default file is the Server Parameter
-- File (SPFILE).
-- Remember that 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.
pause
SHUTDOWN IMMEDIATE
STARTUP
pause
CLEAR SCR
-- Change the RESOURCE_MANAGER_PLAN parameter to SYSTEM_PLAN
-- dynamically into the Server Parameter FILE (SPFILE).
pause
ALTER SYSTEM SER resource_manager_plan='SYSTEM_PLAN' SCOPE=SPFILE
/
-- This time you were able to change the parameter.
-- Notice that 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.
pause
CLEAR SCR
-- Change the SHARED_POOL_SIZE to 20 megabytes dynamically in the
-- memory.
pause
ALTER SYSTEM SET shared_pool_size = 20000000 SCOPE=MEMORY
/
pause
CLEAR SCR
-- Query the SHARED_POOL_SIZE parameter information.
pause
SHOW PARAMETER shared_pool_size
-- Notice that the SHARED_POOL_SIZE parameter was changed.
pause
CLEAR SCR
-- Now, you should practice this Hands-On exercise.
-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.
-- Good luck.
--
pause
pause
|