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