how to set the memory target parameter in oracle 11g

How to set the memory target parameter in oracle 11g

SQL> show parameter memory

NAME                                 TYPE        VALUE

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

hi_shared_memory_address             integer     0

memory_max_target                    big integer 0

memory_target                        big integer 0

shared_memory_address                integer     0

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET MEMORY_TARGET=2G SCOPE=SPFILE;

System altered.

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

System altered.

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

System altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 3207790592 bytes

Fixed Size                  2092920 bytes

Variable Size            2248151176 bytes

Database Buffers          939524096 bytes

Redo Buffers               18022400 bytes

Database mounted.

Database opened.

SQL> show parameter memory

NAME                                 TYPE        VALUE

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

hi_shared_memory_address              integer     0

memory_max_target                    big integer  3G

memory_target                            big integer  2G

shared_memory_address                 integer     0

SQL>

how to change archive log location in Oracle 11g database

how to change archive log location  in Oracle 11g database

SQL> archive log list;

Database log mode            Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     512

Next log sequence to archive   515

Current log sequence              515

Db_recovery_file_dest location change:

alter system set db_recovery_file_dest='/RMANB/' scope=spfile;

log_archive_dest_1 location change:

alter system set log_archive_dest_1='/RMANB/11g/archive' scope=spfile;

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603887104 bytes

Fixed Size                    2089064 bytes

Variable Size             385884056 bytes

Database Buffers      1207959552 bytes

Redo Buffers                 7954432 bytes

Database mounted.

Database opened.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE             VALUE

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

db_recovery_file_dest              string            /RMANB/

db_recovery_file_dest_size       big integer     2G

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival                Enabled

Archive destination              /RMANB/11g

Oldest online log sequence     512

Next log sequence to archive   515

Current log sequence              515

maximum number of processes ( s) exceeded

ORA-00020: maximum number of processes (%s) exceeded

Solution:

We have to increase the value of the PROCESSES initialization parameter and we must need one db bounce.

The session and transaction parameter is depends upon the the process parameter value.

Formula for session, transaction increase steps:

Processes=x

Sessions=x*1.1+5

Transactions=sessions*1.1

sql> alter system set processes=250 scope=spfile;
sql> alter system set sessions=280 scope=spfile;
sql> alter system set transactions=308 scope=spfile;

After the db bounce pls ensure the parameter value.

sql>show parameter processes

NAME                                 TYPE                             VALUE

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

aq_tm_processes                       integer                             1

db_writer_processes                  integer                            1

job_queue_processes                 integer                          10

log_archive_max_processes        integer                           2

processes                                    integer                       250

sql>show parameter session

NAME                                 TYPE                             VALUE

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

java_max_sessionspace_size           integer                          0

java_soft_sessionspace_limit         integer                            0

license_max_sessions                  integer                               0

license_sessions_warning              integer                              0

logmnr_max_persistent_sessions       integer                          1

mts_sessions                                integer                          275

session_cached_cursors               integer                             0

session_max_open_files               integer                            10

sessions                                        integer                          280

shared_server_sessions                integer                          275

sql>show parameter transaction

NAME                                 TYPE                             VALUE

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

transaction_auditing                 boolean                          TRUE

transactions                                     integer                          308

transactions_per_rollback_segment    integer                          5

sql>select count(1) from v$process;


  COUNT(1)
----------
       146

The value should be minimum of the process parameter value.

I hope this will help you...