Memory Management


SQL> show sga
“sga_max_size” parameter defınes the max size of the system global area. *_area_size parameter cannot exceed this value
db_cache_size         ‐‐db_keep_cache_size , db_recycle_cache_size
log_buffer                ‐‐redo log buffer
shared_pool_size
large_pool_size        ‐‐parallel_automatic_tuning=TRUE (init.ora)
                                   ‐‐effects export and import performance
java_pool_size         ‐‐for java stored procedures
alter system set db_cache_advice=[ON|OFF];
select * from v$db_cache_advice;
-- memory advisories
select * from dba_views where view_name like '%ADVICE%'
v$java_pool_advice;
v$shared_pool_advice;
v$sga_target_advice;
v$pga_target_advice;
dba_hist_java_pool_advice
dba_hist_shared_pool_advice
dba_hist_sga_target_advice
dba_hist_pga_target_advice
select name,CEIL(value/1024/1024) MB from v$parameter where name in
('db_cache_size','shared_pool_size','large_pool_size', 'java_pool_size','log_buffer');
alter system set sga_max_size=1024M;
alter system set db_cache_size=512M;
alter system set shared_pool_size=256M;
alter system set large_pool_size=64M;
alter system set java_pool_size=64M;
-- directly effects pga usage
alter system set open_cursors = 50;
-- automatically manage private global area for user sessions
alter system set work_area_size_policy = AUTO;
alter system set pga_aggregate_target = 1500M;
After setting work_area_size_policy to auto all *_area_size parameters dont take effect:
sort_area_size, hash_area_size, bitmap_merge_area_size, create_bitmap_area_size
related views:
v$sga
v$sgastat
v$pgastat
Connect as sysdba
# sqlplus /nolog
SQL> connect / as sysdba
# orapwd file=”filename” password=”password” entries=”user_count”
SQL> alter system set remote_login_passwordfile=[EXCLUSIVE|SHARED|NONE];
desc v$PWFILE_USERS;
grant sysdba to <user_name>;
grant sysoper to <user_name>;
-- database operations with sysdba
SQL> alter database <db_name> mount | open | open readonly | readwrite |
open exclusive;
alter system enable restricted session;
alter system kill session ‘integer1,integer2’ [IMMEDIATE];
-- int1:SID, int2:SERIAL#
-- select SID, SESSION# from v$session;
shutdown abort | immediate | transactional | normal;
show parameter db_cache_size;
col <col_name> format a30;
show parameter shared_pool_size;
alter system set shared_pool_size=256M scope= spfile | memory | both;
SQL> alter system enable restricted session;
startup restrict;
select logins from v$instance;
grant restricted session to <user_name>;
Control file management
-- moving control files to a new location
-- moving with spfile
SQL> alter system set control_files=’/$home/oradata/u01/control01.ctl’,
‘/$home/oradata/u02/control02.ctl’ scope=spfile;
shutdown immediate;
!cp $home/oradata/u01/control01.ctl $home/oradata/u02/control02.ctl
Startup;
-- moving with pfile
SQL> shutdown immediate;
!cp $home/oradata/u01/control01.ctl $home/oradata/u02/control02.ctl
--Open the initSID.ora file and update the control_files variable
Startup;
-- backup controlfile
SQL> alter database backup controlfile to ‘<backup_controlfile_name>’;
alter database backup controlfile to trace;
alter database backup controlfile to trace as '<os_path>';
related views:
v$controlfile
Redo log file
SQL> Alter system set log_checkpoints_to_alert = TRUE;
--each checkpoint will be recorded in the alertSID.log file
alter system switch log file;
alter system checkpoint;
alter system set fast_start_mttr_target=600;
alter database add logfile group 3
(‘/data1/u01/redo3_1.log’,‘/data2/u01/redo3_2.log’)size = 100M;
alter database add logfile member ‘...’ to group 3;
alter database drop logfile group 3;
alter database drop logfile member ‘...’;
shutdown immediate;
alter database clear logfile ‘...’;
!cp ;
alter database rename file ‘..’ to ‘..’;
related views:
v$log
v$loghist
v$logfile
v$log_history
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: