oracle 11gR2 memory_max_target and memory_target

Understanding memory_max_target and memory_target

First of all if these parameters have got values like 

memory_max_target=0

memory_target=0

This means that you are not using Automatic Memory Management.  

There is a difference in both of these parameter. If they are set that means you are using Automatic Memory Management (AMM). I am giving an example for better understanding, if have have got 64G memory (RAM) on your database server and your systems guy is happy for database to use 20G memory. That means you have 20G RAM to play with.

In this scenario you can set memory_max_target = 20G and the setup can be done with the following commands:

alter system set memory_max_target=20G scope=spfile;
shutdown immediate
startup

By setting memory_max_target=20G you allow your instance to get hold of 20G RAM. At this stage if you set second parameter memory_target=15G that means you are instructing oracle instance to only use 15G out of 20G RAM. If you do not set memory_target parameter then oracle will use 20G or RAM.

IMPORTANT: It is important to note that when memory_max_target=20G and memory_target=15G, the remaining 5G are still occupied by oracle and are not in use, even OS can not use this until you shutdown the instance.

Why we need memory_max_target when we have memory_target?

memory_max_target is the initialization parameter. Where memory_target is a dynamic parameter. Which means that when memory_max_target is set to 20G and memory_target is set to 15G you can adjust memory_target to some different value while instance is up and running by issuing the following command.

alter system set memory_target=17G scope=both;
or 
alter system set memory_target=17G;

Having this option to adjust memory_target dynamically you can find what is the suitable memory target for the particluar database, suppose it is 12G for our example then at next outage or restart set memory_max_target=12G and leave memory_target=12G 

ORA-00604: error occurred at recursive SQL level %s

Oracle Enterprise Manager was showing that agent is not able to connect to the target database. To troubleshoot and fix this, following was done.

First of all look at the agent trace file and logs to see what is happening, in my case it is was the following message.

Thread-2506293 WARN  vpxoci: OCI Error -- ErrorCode(604): ORA-00604: error occurred at recursive SQL level %s
SQL = "                                                                   OCISessionGet"...
LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=<PORT NUMBER>))(CONNECT_DATA=(SID=<SID NAME>)))
Thread-2506293 ERROR vpxoci: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: Login 0x457f7e0 failed, error=ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 ERROR engine: [oracle_database,<SID NAME>,latest_hdm_metric_helper] : nmeegd_GetMetricData failed : ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  collector: <nmecmc.c> Error exit. Error message: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: OCI Error -- ErrorCode(604): ORA-00604: error occurred at recursive SQL level %s
SQL = "                                                                   OCISessionGet"...
LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=<PORT NUMBER>))(CONNECT_DATA=(SID=<SID NAME>)))
Thread-2506293 ERROR vpxoci: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: Login 0x457f7e0 failed, error=ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 ERROR engine: [oracle_database,<SID NAME>,latest_hdm_findings] : nmeegd_GetMetricData failed : ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  collector: <nmecmc.c> Error exit. Error message: ORA-00604: error occurred at recursive SQL level %s

After looking at the message you can see that there are some issues with the user dbsnmp not able to connect to the SID.

LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=<PORT NUMBER>))(CONNECT_DATA=(SID=<SID NAME>)))
Thread-2506293 ERROR vpxoci: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: Login 0x457f7e0 failed, error=ORA-00604: error occurred at recursive SQL level %s

Trying using SQL PLUS and connect to database using the dbsnmp

SQL> conn DBSNMP/<PASSWORD>
ERROR:ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

This means that the tablespace SYSTEM is running out of the space. Either add a datafile or extend the datafile to fix the issue.

alter database datafile '<PATH OF DATAFILE>' autoextend on maxsize 2000M;

SQL> conn dbsnmp/<PASSWORD>

 Connected.

Now you will see that under the trace file of agent the error message have gone and it issue is fixed. Oracle Enterprise manager should be able to see the database up and running.

How to rename a table

Rename a table in Oracle


alter table <Current table name> rename to <New table name>;

ORA-30009: Not enough memory for CONNECT BY operation

Getting the error ORA-30009 when trying to execute a statement.


create table t
as
select
        rownum as id
        , mod(rownum, 10) + 1 as attr1
        , rpad('x', 100) as filler
from
          dual
connect by
          level <= 1000000
;


          dual
          *
ERROR at line 8:
ORA-30009: Not enough memory for CONNECT BY operation

To fix this increase the pga_aggregate_target value.

SQL> show parameter pga_a

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 128M

SQL> show parameter workarea_s

NAME                                 TYPE        VALUE

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

workarea_size_policy                 string      AUTO

 SQL> alter system set pga_aggregate_target=200M scope=both;

System altered.

SQL> show parameter pga_a

NAME                                 TYPE        VALUE

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

pga_aggregate_target                 big integer 200M

create table t

as

select

        rownum as id

        , mod(rownum, 10) + 1 as attr1

        , rpad('x', 100) as filler

from

          dual

connect by

          level <= 1000000

;

Table created.

Create table in different tablespace

If you want to create the table under different tablespace then you need to include tablespace clause in the command, the example is as below:

Identify the default tablespace of the user

SQL> select DEFAULT_TABLESPACE, USERNAME  from dba_users where username in ('HDHILLON');

DEFAULT_TABLESPACE             USERNAME
------------------------------ ------------------------------
MICHAEL                        HDHILLON

Create table as user which will go under user's default tablespace

SQL> create table ttab as select * from dual;

Table created.

SQL>  select TABLESPACE_NAME, TABLE_NAME, OWNER from dba_tables where table_name in ('TTAB');

TABLESPACE_NAME                TABLE_NAME                     OWNER

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

MICHAEL                        TTAB                           HDHILLON

SQL> 

SQL> drop table ttab;

Table dropped.

SQL>

Create table with the tablepspace clause, now table should be create under EXAMPLE tablespace.

SQL>  create table ttab tablespace EXAMPLE  as select * from dual;

Table created.

SQL> 

SQL>  select TABLESPACE_NAME, TABLE_NAME, OWNER from dba_tables where table_name in ('TTAB');

TABLESPACE_NAME                TABLE_NAME                     OWNER

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

EXAMPLE                        TTAB                           HDHILLON

SQL> 

Enable Auditing on database

Enabling auditing on database is simple but will require an outage to the instance and space under system tablespace.


SQL> connect / as sysdba
Connected.

SQL> alter system set audit_trail=DB scope=spfile ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             390071944 bytes
Database Buffers          138412032 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
SQL>
SQL> connect dummy/dummy
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> conn / as sysdba
Connected.
SQL>

SQL> col OS_USERNAME for a15
SQL> col userhost for a30
SQL>
SQL> select os_username,
  2  username,
  3  userhost,
  4  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
  5  returncode
  6  from dba_audit_session
  7  where action_name = 'LOGON'
  8  and returncode > 0
  9  order by TIMESTAMP desc;


OS_USERNAME     USERNAME                       USERHOST                       TIMESTAMP           RETURNCODE
--------------- ------------------------------ ------------------------------ ------------------- ----------
oracle          DUMMY                          HostName                       07/09/2012 12:29:56       1017
 
The size of the audit table can be managed, we can purge the old logs as below:


 SQL> truncate table dba_audit_session;
truncate table dba_audit_session
               *
ERROR at line 1:
ORA-01702: a view is not appropriate here


SQL> select count(*) from SYS.AUD$;

  COUNT(*)
----------
    890947

SQL> select count(*) from dba_audit_session;

  COUNT(*)
----------
    890827

SQL> truncate table SYS.AUD$;

Table truncated.

SQL> select count(*) from dba_audit_session;

  COUNT(*)
----------
         0

SQL>

LRM-00112: multiple values not allowed for parameter 'userid'

Getting error while exporting


exp userid='/ as sysdba' full=y file=/dev/null log=/stage/$ORACLE_SID.log direct=y statistics=none
LRM-00112: multiple values not allowed for parameter 'userid'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully

This is a syntax error which can be fixed by replacing userid='/ as sysdba' with userid="'/ as sysdba'"


Following command will work

exp userid="'/ as sysdba'" full=y file=/dev/null log=/stage/$ORACLE_SID.log direct=y statistics=none