This
practice shows how to create and use the SPFILE and maintain the PFILE.
Instructions
1.
Using SQLPLUS, select the non-default parameters from the v$parameter
view.
|
SQL>
l
1 select name,
value from v$parameter
2* where isdefault ='FALSE' SQL>
/ NAME
VALUE -------------------------
--------------------------------------- processes
150 timed_statistics
TRUE shared_pool_size
29360128 sga_max_size
135032864 large_pool_size
1048576 java_pool_size
0 control_files
c:\oracle9i\oradata\OEMREP\CONTROL01.CTL db_block_size
4096 db_cache_size
16777216 compatible
9.0.0 fast_start_mttr_target
300 undo_management
AUTO undo_tablespace
UNDOTBS remote_login_passwordfile
EXCLUSIVE db_domain distributed_transactions
0 instance_name
OEMREP background_dump_dest
c:\oracle9i\admin\OEMREP\bdump user_dump_dest
c:\oracle9i\admin\OEMREP\udump core_dump_dest
c:\oracle9i\admin\OEMREP\cdump sort_area_size
524288 db_name
OEMREP open_cursors
300 24
rows selected. |
2.
Using SQLPLUS, create an SPFILE from the existing PFILE in the default
directory for these files.
|
SQL>
create spfile from pfile; File
created. |
3.
Using SQLPLUS, select the non-default parameters from v$SPPARAMETER
view.
|
SQL>
l
1 select name,
isspecified, value
2 from
v$spparameter
3* where isspecified = 'TRUE' SQL>
/ NAME
ISSPEC VALUE -------------------------
------ ---------------------------------------- processes
TRUE 150 timed_statistics
TRUE TRUE shared_pool_size
TRUE 16777216 sga_max_size
TRUE 134217728 large_pool_size
TRUE 1048576 java_pool_size
TRUE 0 control_files
TRUE c:\oracle9i\oradata\OEMREP\CONTROL01.CTL control_files
TRUE c:\oracle9i\oradata\OEMREP\CONTROL02.CTL control_files
TRUE c:\oracle9i\oradata\OEMREP\CONTROL03.CTL db_block_size
TRUE 4096 db_cache_size
TRUE 16777216 compatible
TRUE 9.0.0 fast_start_mttr_target
TRUE 300 undo_management
TRUE AUTO undo_tablespace
TRUE UNDOTBS remote_login_passwordfile
TRUE EXCLUSIVE db_domain
TRUE distributed_transactions
TRUE 0 instance_name
TRUE OEMREP dispatchers
TRUE (PROTOCOL=TCP)(SER=MODOSE) dispatchers
TRUE (PROTOCOL=TCP)(PRE=oracle.aurora.server.
GiopServer) dispatchers
TRUE (PROTOCOL=TCP)(PRE=oracle.aurora.server.
SGiopServer) background_dump_dest
TRUE c:\oracle9i\admin\OEMREP\bdump user_dump_dest
TRUE
c:\oracle9i\admin\OEMREP\udump core_dump_dest
TRUE c:\oracle9i\admin\OEMREP\cdump sort_area_size
TRUE 524288 db_name
TRUE OEMREP open_cursors
TRUE 300 28
rows selected. |
4.
Using SQLPLUS, create a pfile from the existing spfile.
|
SQL>
create pfile from spfile; File
created. |
In
this practice you will find sizes of the parts of the SGA and you will also
dynamically change the sizes of the shared pool and the buffer cache.
Assumptions
·
This
practice is written for any OS.
·
The
database version for these practices is assumed to be Oracle 9.0.1.
·
These
practices assume that you have a database accounts with either DBA, SYSDBA.
·
These
practices assume that you have a dynamic spfile instead of a pfile.
Instructions
1.
Using SQLPLUS, show how big the SGA (Shared Global Area) is.
|
SQL>
show sga Total
System Global Area 135032864
bytes Fixed
Size
282656 bytes Variable
Size
117440512 bytes Database
Buffers
16777216 bytes Redo
Buffers
532480 bytes SQL> |
2. Using SQLPLUS, show memory structures of the SGA include the sizes in bytes.
|
SQL>
l
1 select name,
value from v$parameter where name in
2 ('shared_pool_size',
3 'java_pool_size',
4 'large_pool_size',
5 'db_cache_size',
6 'log_buffer')
7* order by 1 SQL>
/ NAME
VALUE -------------------------
------------------------- db_cache_size
16777216 java_pool_size
0 large_pool_size
1048576 log_buffer
524288 shared_pool_size
16777216 SQL> |
3. Using SQLPLUS, dynamically alter the size of the shared pool to 32 Meg, show the new size and dynamically alter it back to 16 Meg. Use the ALTER SYSTEM command for both operations.
|
SQL>
show parameter shared_pool_size NAME
TYPE
VALUE ------------------------------------
----------- -------------- shared_pool_size
big integer 16777216 SQL>
alter system set shared_pool_size = 32m; System
altered. SQL>
show parameter shared_pool_size NAME
TYPE
VALUE ------------------------------------
----------- -------------- shared_pool_size
big integer 33554432 SQL>
alter system set shared_pool_size = 16m; System
altered. SQL>
show parameter shared_pool_size NAME
TYPE
VALUE ------------------------------------
----------- -------------- shared_pool_size
big integer 16777216 SQL> |
4. Using SQLPLUS, Show the size of the db_buffer_cache and dynamically change it to 32 m and then back to 16m. Use the ALTER SYSTEM command for both operations.
|
SQL>
show parameter db_cache_size NAME
TYPE
VALUE ------------------------------------
----------- -------- db_cache_size
big integer 16777216 SQL>
alter system set db_cache_size=32m; System
altered. SQL>
show parameter db_cache_size NAME
TYPE
VALUE ------------------------------------
----------- -------- db_cache_size
big integer 33554432 SQL>
alter system set db_cache_size=16m; System
altered. SQL>
show parameter db_cache_size NAME
TYPE
VALUE ------------------------------------
----------- -------- db_cache_size
big integer 16777216 SQL> |
5.
Using SQLPLUS, select the current size and the previous size of the
default buffer cache.
|
SQL>
l
1 select name,
current_size, buffers, prev_size, prev_buffers
2* from v$buffer_pool SQL>
/ NAME
CURRENT_SIZE BUFFERS
PREV_SIZE PREV_BUFFERS -------------------------
------------ ---------- ---------- ------------ DEFAULT
16
3932
32
7864 |
This
practice will help you configure Oracle Managed Files (OMF).
Assumptions
·
This practice is
for the Windows platform, UNIX users will need to changed directories
accordingly.
·
This practice
uses SQLPLUS to explain OMF’s
Instructions
1.
Using SQLPLUS, select the parameters that control Oracle Managed Files
from the V$SPPARAMETER view.
|
SQL>
l
1 select name, value
2 from v$spparameter
3* where name like 'db_create%' SQL>
/ NAME
VALUE --------------------------------------------------
-------------- db_create_file_dest db_create_online_log_dest_1 db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 6
rows selected. |
2.
Using SQLPLUS, alter the system to set the destinations for Oracle
Managed Files, both datafiles and control and redo log files.
|
alter
system set db_create_file_dest = 'C:\ORACLE9I\ORADATA\OEMREP\' System
altered. SQL>
alter system set db_create_online_log_dest_1 = 'C:\ORACLE9I\ORADATA\OEMREP\'
scope=memory; System
altered. |
3.
Using SQLPLUS, select the
destinations of Oracle Managed Files from V$SPPARAMETER.
|
SQL>
l
1* select name, value from v$spparameter where name in ('db_create_file_dest','db_create_online_log_ SQL>
/ NAME
VALUE ------------------------------
---------------------------------------- db_create_file_dest
C:\ORACLE9I\ORADATA\OEMREP\ db_create_online_log_dest_1
C:\ORACLE9I\ORADATA\OEMREP\ |
This
practice will show how to startup and shutdown the Oracle database.
Assumptions
·
This practice
is for any OS.
·
This practice
will use SQLPLUS to startup the database
·
User needs the
SYSDBA privileges, either through password file or OS authentication.
Instructions
1.
After connecting to the database with SYSDBA privileges in SQLPLUS,
startup the databse in NOMOUNT state.
|
SQL>
connect / as sysdba Connected
to an idle instance. SQL>
startup nomount ORACLE
instance started. Total
System Global Area 135032864
bytes Fixed
Size
282656 bytes Variable
Size
117440512 bytes Database
Buffers
16777216 bytes Redo
Buffers
532480 bytes |
2.
Using SQLPLUS, alter the database to MOUNT state.
|
SQL>
alter database oemrep mount; Database
altered. |
3.
Using SQLPLUS, select the status of the instance from the V$INSTANCE
view.
|
1* select instance_name, status from v$instance SQL>
/ INSTANCE_NAME
STATUS ----------------
------- oemrep
MOUNTED |
4.
Using SQLPLUS, alter the database to OPEN state.
Then select the status of the database from V$INSTANCE.
|
SQL>
alter database open; Database
altered. SQL>
select instance_name, status from v$instance; INSTANCE_NAME
STATUS ----------------
------- oemrep
OPEN |
This
practice you will shutdown the database using shutdown immediate.
Assumptions
1.
Using SQLPLUS, select the status of the database from v$instance.
|
SQL>
select instance_name, status from v$instance; INSTANCE_NAME
STATUS ----------------
------- oemrep
OPEN |
2.
Using SQLPLUS, connect to the database with SYSDBA privileges.
|
SQL>
connect /@oemrep as sysdba Connected. |
3.
Using SQLPLUS, shutdown the database with a checkpoint.
|
SQL>
shutdown immediate Database
closed. Database
dismounted. ORACLE
instance shut down. |