STARTUP STAGES, PFILE vs, SPFILE and DYNAMIC MEMORY ...

feastcanadianSoftware and s/w Development

Dec 14, 2013 (3 years and 6 months ago)

97 views

STARTUP STAGES, PFILE vs, SPFILE and DYNAMIC MEMORY MANAGEMENT


After login via “ssh” session to our “zenit.senecac.on.ca” server and switching to “oracle” user


oracle@localhost:~>
pwd

/home/oracle


oracle@localhost:~>
cd


/opt/oracle

oracle@localhost
:/opt/oracle>
ls
-
l

total 28

drwxr
-
xr
-
x 74 oracle oinstall 4096 2010
-
12
-
24 08:05 11.2.0.0.0

drwxr
-
x
---

3 oracle oinstall 4096 2010
-
06
-
24 13:47 admin

drwxr
-
x
---

5 oracle oinstall 4096 2010
-
06
-
24 13:46 cfgtoollogs

drwxr
-
xr
-
x 2 oracle oinstall 4096 2010
-
06
-
24 11:49 checkpoints

drwxrwxr
-
x 4 oracle oinstall 4096 2010
-
06
-
24 13:48 diag

drwxr
-
x
---

3 oracle oinstall 4096 2010
-
06
-
24 13:47 oradata

drwxr
-
xr
-
x 2 oracle oinstall 4096 2010
-
06
-
24 11:23 oraInventory

oracle@localhost:/opt/oracle>
cd

admin/dborcl

oracl
e@localhost:/opt/oracle/admin/dborcl>
ls
-
l

total 20

drwxr
-
x
---

2 oracle oinstall 8192 2011
-
01
-
27 13:20 adump

drwxr
-
x
---

2 oracle oinstall 4096 2010
-
06
-
24 13:50 dpdump

drwxr
-
x
---

2 oracle oinstall 4096 2010
-
06
-
24 13:52 pfile

drwxr
-
x
---

2 oracle oinstall 40
96 2010
-
06
-
24 13:47 scripts

oracle@localhost:/opt/oracle/admin/dborcl>
cd

pfile

oracle@localhost:/opt/oracle/admin/dborcl/pfile>
ls
-
l

total 4


-
rw
-
r
-----

1 oracle oinstall 1669 2010
-
06
-
24 13:48 init.ora.5242010135221



this is our SEED

P
FILE

oracle@localhost:/opt/oracle/admin/dborcl/pfile>
vi

initdborcl.ora


OPEN an EMPTY parameter file and then PASTE the content from the “initdborcl.ora” that
you can download from my Web Page

oracle@localhost:/opt/oracle/admin/dborcl/pfile>
cat

initdbor
cl.ora

##############################################################################

# Copyright (c) 1991, 2001, 2002 by Oracle Corporation

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192


###########################################

# Cursors and Library Cache

###########################################

open_cursors=300


###########################################

# Database Identification

###########################################

db_domain=""

db_name=dborcl


###########################################

# File Configuration

###########################################

co
ntrol_files=("/opt/oracle/oradata/dborcl/control01.ctl", "/opt/oracle/oradata/dborcl/control02.ctl")


###########################################

# Miscellaneous

###########################################

compatible=11.2.0.0.0

diagnostic_dest=/opt/oracle

#memory_target=632291328


###########################################

# Processes and Sessions

###########################################

processes=150


###########################################

# Security and Auditing

###########################################

audit_file_dest=/opt/oracle/admin/dborcl/adump

audit_trail=db

remote_login_passwordfile=EXCLUSIVE


###########################################

# Shared Server

###########################################

#dispatch
ers="(PROTOCOL=TCP) (SERVICE=dborclXDB)"


###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_tablespace=UNDOTBS1


###################################

# Manual Memory Mana
gement

sga_
max
_size
=400M

sga_target=0

shared_pool_size=128M

db_cache_size=128M

large_pool_size=16M

java_pool_size=64M

pga_aggregate_target=96M


oracle@localhost:/opt/oracle/admin/dborcl/pfile>

sqlplus


/nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue

Feb 1 13:26:42 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL>
connect sys as sysdba

Enter password:

Connected.

SQL>
SET PAGESIZE 200

SQL>
SHOW PARAMETER SPFILE

NAME TYPE VALUE

------------------------------------

-----------

------------------------------
---------------------

spfile string /opt/oracle/11.2.0.0.0/dbs/spfiledborcl.ora


VALUE is NOT blank


䥮獴慮捥awa猠st慲a敤⁢礠畳y湧⁓偆I


SQL>
SHOW

PARAMETER

POOL

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

buffer_pool_keep string

buffer_pool_recycle string

glo
bal_context_pool_size string

java_pool_size big integer 0

large_pool_size big integer 0

olap_page_pool_size big integer 0

shared_pool_reserved_size big integer 12163481

shar
ed_pool_size big integer 0

streams_pool_size big integer 0

SQL>
SHOW PARAMETER DB_CACHE

NAME TYPE VALUE

------------------------------------

-----------

-------------------------
-----

db_cache_advice string ON

db_cache_size big integer 0

SQL>

SQL>
SHOW PARAMETER BUFFER

NAME TYPE VALUE

------------------------------------

-----------

----------
--------------------

buffer_pool_keep string

buffer_pool_recycle string

db_block_buffers integer 0

log_buffer integer 5435392



this buffer is not involved

in the ASMM

use_indirect_data_buffers boolean FALSE

SQL>
SHOW PARAMETER MEMORY

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

hi_shared_memory_address

integer 0

memory_max_target big integer 604M

memory_target big integer 604M

shared_memory_address integer 0

SQL>
SHOW PARAMETER SGA

NAME TYPE

VALUE

------------------------------------

-----------

------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 604M

sga_t
arget big integer 0

SQL>
SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>
STARTUP PFILE=initdborcl.ora;



we are using now
our new
PFILE

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1336932 bytes

Variable Size 276826524 bytes

Database Buffers 134217728 bytes

Redo Buffers 6103040 bytes

Database mounted.

Database opened.

SQL>
SHOW
PARAMETER SGA

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

lock_sga boolean FALSE

pre_p
age_sga
boolean FALSE

sga
_m
ax_size
big integer 400M

sga_t
arget
big integer 0



we

are using now MANUAL MEMORY managem
e
nt

SQL> SHOW PARAMETER SPFILE

NAME TYPE VALUE

--------------------------
----------

-----------

------------------------------

spfile string


VALUE is
blank


I湳n慮a攠w慳⁳t慲t敤⁢礠e
獩湧n
偆䥌I

SQL>
SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>
STARTUP NOMOUNT PFILE=initdborcl.ora;

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1336932 bytes

Variable Size 276826524 bytes

Database Buffers 134217728 bytes

Redo Buffers

6103040 bytes

SQL>
SELECT status FROM V$INSTANCE;

STATUS

------------

STARTED


SQL>
SELECT open_mode FROM v$database;

SELECT open_mode FROM v$database


*

ERROR at line 1:

ORA
-
01507: database not mounted

SQL>
ALTER DATABASE MOUNT;

Database altered.

SQL>
SELECT status FROM V$INSTANCE;

STATUS

------------

MOUNTED

SQL>
SELECT open_mode FROM v$database;

OPEN_MODE

--------------------

MOUNTED

SQL>
DESC dba_objects;

ERROR:

ORA
-
04043: object dba_objects does not exist

SQL>
ALTER DATABASE O
PEN;

Database altered.

SQL>
SELECT status FROM V$INSTANCE;

STATUS

------------

OPEN

SQL>
SELECT open_mode FROM v$database;

OPEN_MODE

--------------------

READ WRITE

SQL>
DESC dba_objects;


Name Null? Type


-----------------------------------------

--------

----------------------------


OWNER VARCHAR2(30)


OBJECT_NAME VARCHAR2(128)


SUBOBJECT_NAME

VARCHAR2(30)


OBJECT_ID NUMBER


DATA_OBJECT_ID NUMBER


OBJECT_TYPE VARCHAR2(19)


CREATED
DATE


LAST_DDL_TIME DATE


TIMESTAMP VARCHAR2(19)


STATUS VARCHAR2(7)


TEMPORARY VARCHAR2(1)


GENERATED VARCHAR2(1)


SECONDARY VARCHAR2(1)


NAMESPACE NUMBER

SQL>
ALTER SYSTEM SET DB_CACHE_SIZE=102M;

System altered.

SQL>
SHOW P
ARAMETER DB_CACHE

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

db_cache_advice string ON

db_cache_size big integer 10
4M

SQL>
ALTER SYSTEM SET JAVA_POOL_SIZE=200M;

ALTER SYSTEM SET JAVA_POOL_SIZE=200M

*

ERROR at line 1:

ORA
-
02097: parameter cannot be modified because specified value is invalid

ORA
-
04033: Insufficient memory to grow pool

SQL>
ALTER

SYSTEM SET SHARED_POOL_SIZE=144M;


ALTER SYSTEM SET SHARED_POOL_SIZE=144M

*

ERROR at line 1:

ORA
-
02097: parameter cannot be modified because specified value is invalid

ORA
-
04033: Insufficient memory to grow pool

SQL>
SHOW PARAMETER JAVA

NAME

TYPE VALUE

------------------------------------

-----------

------------------------------

java_jit_enabled boolean TRUE

java_max_sessionspace_size integer 0

java_pool_size

big integer 144M

java_soft_sessionspace_limit integer 0

SQL>
ALTER SYSTEM SET JAVA_POOL_SIZE=64M;

System altered.

SQL>
ALTER SYSTEM SET SHARED_POOL_SIZE=160M;

System altered.

SQL>
ALTER SYSTEM SET JAVA_POOL_SIZE=80M SCOPE=BOTH;

ALTER SYSTEM
SET JAVA_POOL_SIZE=80M SCOPE=BOTH

*

ERROR at line 1:

ORA
-
32001: write to SPFILE requested but no SPFILE is in use


Database Instance: dborcl

>

Advisor Central

>


Logged in As SYS


Memory Advisors

Page Refreshed


February 1, 2011 1:47:29 PM EST












When Automatic Memory Management is enabled, the database will automatically set the
optimal distribution of memory. The distribution of memory will

change from time to time to
acc
omodate

changes in the workload.


Automatic Memory Management

Disabled
















SGA






PGA









The System Global Area (SGA) is a group of shared memory structures that contains data and
control information for one Oracle database. The SGA is allocated in memory when an Oracle
database instance is started.



Automatic Shared Memory Management

Disabled



Shared
Pool




Buffer
Cache




Large
Pool




Java
Pool




Other


7

(MB)



Total


SGA
351
(MB)






Shared Pool
(45.6%)






Buffer
Cache
(29.6%)






Large Pool
(4.6%)






Java Pool
(18.2%)






Other (2%)












Maximum SGA Size



The Maximum SGA Size specifies the maximum
memory that the database may allocate. If
you specify the Maximum SGA Size, you can later dynamically change SGA component
sizes (provided the total SGA size does not exceed the Maximum SGA Size).


Maximum SGA
Size


(MB)



The database must be
restarted before any changes to this value take
effect.
















SGA






PGA


















SQL>
SHUTDOWN


IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

STARTUP



we are using now SPFILE

again

ORACLE instance started.

Total System Global Area 631914496 bytes

Fixed Size 1338364 bytes

Variable Size 473957380 bytes

Database Buffers 150994944 bytes

Redo Buffers 5623808 bytes

Database mounted.

Database opened.

SQL>
SHOW

PARAMETER

SPFILE;

NAME TYPE VALUE

------------------------------------

-----------

-----------------------------
--------------------------
-

spfile string /opt/oracle/11.2.0.0.0/dbs/
spfiledborcl.ora

SQL>
SHOW PARAMETER MEMORY

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

hi_shared_memory_address integer 0

memory_max_target



big integer 604M

memory_target


big integer 604M

shared_memory_address integer 0

SQL>
SHOW PARAMETER SGA

NAME TYPE VALUE

-------------------------------
-----

-----------

------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 604M

sga_target big in
teger 0

SQL>
ALTER

SYSTEM

SET

MEMORY_TARGET=0

SCOPE=MEMORY;


System altered.



to promptly TURN OFF the AMM feature (not persistently)

SQL>
SHOW PARAMETER MEMORY

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

hi_shared_memory_address integer 0

memory_max_target big integer 604M

memory_target big integer 0

shared_memory_addres
s integer 0


SQL>
SHOW

PARAMETER

SGA

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

lock_sga boolean FALSE

pre_pag
e_sga boolean FALSE

sga_max_size big integer 604M

sga_target

big integer 396M



it shows now the target value for SGA

SQL> SHOW

PARAMETER

PGA

NAME

TYPE VALUE

------------------------------------

-----------

------------------------------

pga_aggregate_target big integer 208M

SQL>
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=128M;

System altered.

SQL>
ALTER SYSTEM SET
SGA_TARGET=0;




System altered.



let’s turn off the ASMM feature

promptly and persistently

SQL>
SHOW

PARAMETER

POOL


NAME TYPE VALUE

------------------------------------

-----------

------------------------------

_shared_io_pool_size big integer 0

buffer_pool_keep string

buffer_pool_recycle string

global_context_pool_size string

java_pool_size

big integer 4M

large_pool_size big integer 4M

olap_page_pool_size big integer 0

shared_pool_reserved_size big integer 12163481

shared_pool_size big integer 232M

str
eams_pool_size big integer 4M

SQL>
SHOW

PARAMETER


DB_CACHE;

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

db_cache_advice str
ing ON

db_cache_size big integer

144M

SQL>
ALTER SYSTEM SET SGA_MAX_SIZE=480M;

ALTER SYSTEM SET SGA_MAX_SIZE=480M


*

ERROR at line 1:

ORA
-
02095: specified initializati
on parameter cannot be modified

SQL>
ALTER
SYSTEM SET SGA_MAX_SIZE=480M SCOPE=SPFILE;

System altered.

SQL>
ALTER SYSTEM SET SHARED_POOL_SIZE=144M;

System altered.

SQL>
ALTER SYSTEM SET LARGE_POOL_SIZE=8M;

System altered.

SQL>
ALTER SYSTEM SET JAVA_POOL_SIZE=80M;

System altered.

SQL>
SHOW PARAMETER
POOL

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

_shared_io_pool_size big integer 0

buffer_pool_keep string

buffer_pool_recycle

string

global_context_pool_size string

java_pool_size big integer 80M

large_pool_size big integer 8M

olap_page_pool_size big integer 0

shared_pool_reserved_size

big integer 12163481

shared_pool_size big integer 144M

streams_pool_size big integer 4M

SQL>
SHOW PARAMETER DB_CACHE

NAME TYPE VALUE

------------------------------------

--
---------

------------------------------

db_cache_advice string ON

db_cache_size big integer 144M

SQL>
SHOW PARAMETER SGA

NAME TYPE VALUE

------------------------------
------

-----------

------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 604M

sga_target big i
nteger 0

SQL>

SQL>
SHOW PARAMETER MEMORY

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

hi_shared_memory_address integer 0

memory_max_target

big integer 604M

memory_target big integer 0

shared_memory_address integer 0

SQL>
SHOW PARAMETER PGA

NAME TYPE VALUE

------------------------------------

-----------

------------------------------

pga_aggregate_target big integer 128M

SQL>
ALTER SYSTEM SET MEMORY_TARGET=0 SCOPE=SPFILE;

System altered.



to persistently TURN OFF the AMM feature


SQL>
SHUTDOWN IMMEDIATE;

Database closed.

D
atabase dismounted.

ORACLE instance shut down.