DBA Fundamentals

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:

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@yourhost 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.

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

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

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.

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'
/