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

ORA-01631: max # extents (121) reached in table

Getting the message


ORA-12096: error in materialized view log on "HARVEY"."MV_HARVEY"
ORA-01631: max # extents (121) reached in table HARVEY.MLOG$_MV_HARVEY
ORA-02063: preceding 2 lines from DB_LINK_DATA

I was getting the error message when trying to insert a record using database link of DB_LINK_DATA.


This issue was resolved by updating the max_extents to unlimited on the target database (under DB_LINK_DATA). This was done by running the following command on the database under DB_LINK_DATA

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME, PCT_FREE,PCT_USED,STATUS, MIN_EXTENTS, MAX_EXTENTS,NEXT_EXTENT from dba_tables where table_name ='MLOG$_ALL_OBJECT_ROLES';

OWNER      TABLE_NAME                     TABLE   PCT_FREE   PCT_USED STATUS   MIN_EXTENTS MAX_EXTENTS NEXT_EXTENT

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

HARVEY        MLOG$_MV_HARVEY         HARVEY           60         30 VALID              1  121      131072

alter table HARVEY.MLOG$_MV_HARVEY storage(maxextents unlimited);

SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME, PCT_FREE,PCT_USED,STATUS, MIN_EXTENTS, MAX_EXTENTS,NEXT_EXTENT from dba_tables where table_name ='MLOG$_ALL_OBJECT_ROLES';

OWNER      TABLE_NAME                     TABLE   PCT_FREE   PCT_USED STATUS   MIN_EXTENTS MAX_EXTENTS NEXT_EXTENT

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

HARVEY        MLOG$_MV_HARVEY         HARVEY           60         30 VALID              1  2147483645      131072

Adding temp datafile to default temp tablepsace

First we need to determine what is the current default temp tablespace and that can be done using


SQL> col PROPERTY_NAME for a40
SQL> col PROPERTY_VALUE for a30
SQL> col DESCRIPTION for a70
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                 DESCRIPTION
---------------------------------------- ------------------------------ ---------------------------------------
DEFAULT_TEMP_TABLESPACE                  TEMP                           Name of default temporary tablespace

SQL>

to add the datafile to temp tablespace:

alter tablespace temp add tempfile '/u01/app/oracle/oradata/new_location/tempnew.dbf' size 700M reuse  autoextend on maxsize 700M;

Connection to the repository failed. Verify that the repository connection information provided is correct

Unable to start Oracle Enterprise Manager and getting the following messages:


./Oracle/Middleware/oms11g/bin/emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>Successfully started server EMGC_OMS1 ...

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>new status of EMGC_OMS1:

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>RUNNING

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>wait until EMGC_OMS1 becomes RUNNING

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>RUNNING

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>status of node manager:

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>Currently connected to Node Manager to monitor the domain GCDomain.

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>1

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>Successfully disconnected from Node Manager.

[Thread-1] INFO  wls.OMSController run.1094 - <OUT>_END_

[Thread-2] INFO  wls.OMSController run.1094 - <ERR>_END_

[Main Thread] INFO  wls.OMSController processCmd.988 - wlst process is finished

[Main Thread] INFO  wls.OMSController processCmd.990 - wlst process exited with code 0

[Main Thread] INFO  wls.OMSController processCmd.992 - wlst outstream joined

[Main Thread] INFO  wls.OMSController processCmd.994 - wlst errstream joined

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.138 - HTTP port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.139 - HTTPS port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.155 - Using http port.

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.195 - Attempting to connect to http://hostname.domain.com:<PORT>/em/console/logon/logon

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.200 - page status code is 503

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.202 - header is 3

[Main Thread] INFO  wls.OMSController statusOMS.950 - statusOMS finished with result: 3

[Main Thread] INFO  wls.OMSController startOMS.603 - startOMS finished with result: 0

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.138 - HTTP port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.139 - HTTPS port in emgc property file: <PORT>

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.155 - Using http port.

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.195 - Attempting to connect to http://hostname.domain.com:<PORT>/em/console/logon/logon

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.200 - page status code is 503

[Main Thread] DEBUG oms.StatusOMSCmd processStatusOMS.202 - header is 3

[Main Thread] INFO  wls.OMSController statusOMS.950 - statusOMS finished with result: 3

[Main Thread] DEBUG wls.OMSController main.167 - Log location is /home/oracle/Oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log

[Main Thread] INFO  wls.OMSController stopOMS.664 - istopAll is false

[Main Thread] INFO  wls.OMSController stopOMS.665 - isForce is false

[Main Thread] INFO  wls.OMSController stopOMS.666 - is service is false

[Main Thread] INFO  wls.OMSController getEnvProps.348 - Setting trust store system properties: -Dweblogic.security.TrustKeyStore=DemoTrust -Dweblogic.security.SSL.ignoreHostnameVerification=true -Djava.security.egd=file:///dev/urandom

[Main Thread] INFO  wls.OMSController stopOMS.682 - Stopping WebTier...

[Main Thread] INFO  util.WebTierUtil execCmd.134 - Running the command: /home/oracle/Oracle/gc_inst/WebTierIH1/bin/opmnctl stopall

[Main Thread] INFO  util.WebTierUtil getProcessResult.196 - opmnctl cmd output: opmnctl stopall: stopping opmn and all managed processes...

[Main Thread] INFO  util.WebTierUtil getProcessResult.204 - opmnctl cmd error: 

[Main Thread] INFO  util.WebTierUtil getProcessResult.208 - opmnctl cmd exited with code 0

Fix of this is very simple:

You are encountering this issue because EM is not able to connect to repository and in my case the listener was not running. Once the listener is started the Enterprise Manager was up.

 ./Oracle/Middleware/oms11g/bin/emctl start oms

Oracle Enterprise Manager 11g Release 1 Grid Control  

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Starting WebTier...

WebTier Successfully Started

Starting Oracle Management Server...

Oracle Management Server Successfully Started

Oracle Management Server is Up

Understanding the working of JOINS

Understanding the Joins.

Sometimes it can be hard to understand on how to use joins, my post below may be able to help you.

SQL> select * from ant;


        ID
----------
         1
         3
         4
         5

SQL> select * from bat;

       TAG

----------

         2

         3

         7

         9

Inner Join:
Two different ways

SQL> select id, tag
  2  from ant a inner join bat b
  3  on (a.id = b.tag);

        ID        TAG
---------- ----------
         3          3

SQL>

SQL> select a.id, b.tag
  2  from ant a join bat b
  3  on (a.id = b.tag);

        ID        TAG
---------- ----------
         3          3

Left Outer Join:
SQL> select a.id, b.tag
  2  from ant a left join bat b
  3  on (a.id = b.tag)
  4  order by 1;

        ID        TAG
---------- ----------
         1
         3          3
         4
         5

Old Method:
--------------

SQL> select a.id, b.tag
  2  from ant a, bat b
  3  where a.id = b.tag (+)
  4  order by 1;

        ID        TAG
---------- ----------
         1
         3          3
         4
         5

Right Outer Join:
SQL> select a.id, b.tag
  2  from ant a right outer join bat b
  3  on (a.id = b.tag)
  4  order by 2;

        ID        TAG
---------- ----------
                    2
         3          3
                    7
                    9

Old Method:


SQL> select a.id, b.tag
  2  from ant a, bat b
  3  where a.id (+) = b.tag
  4  order by 1;

        ID        TAG
---------- ----------
         3          3
                    2
                    9
                    7

Full Outer Join:
SQL> select a.id, b.tag
  2  from ant a full outer join bat b
  3  on (a.id = b.tag)
  4  order by 1,2;

        ID        TAG
---------- ----------
         1
         3          3
         4
         5
                    2
                    7
                    9

7 rows selected.


Cross or Cartesian Join:

if you try to do a natural join and there are no common column names and data types then it will do the Cross or Cartesian by default

else Specify CROSS JOIN in between tables.


SQL> select id, tag
  2  from ant, bat
  3  order by 1,2;

        ID        TAG
---------- ----------
         1          2
         1          3
         1          7
         1          9
         3          2
         3          3
         3          7
         3          9
         4          2
         4          3
         4          7
         4          9
         5          2
         5          3
         5          7
         5          9

16 rows selected.



SQL> select id, tag
  2  from ant CROSS JOIN bat
  3  order by 1,2;

        ID        TAG
---------- ----------
         1          2
         1          3
         1          7
         1          9
         3          2
         3          3
         3          7
         3          9
         4          2
         4          3
         4          7
         4          9
         5          2
         5          3
         5          7
         5          9

16 rows selected.

Case Study:
Show the rows from ant table which are not in bat table.
SQL> select a.id
  2  from ant a left outer join bat b
  3  on (a.id = b.tag)
  4  where b.tag is null
  5  order by 1;

        ID
----------
         1
         4
         5

Show the records from bat table which are  not in ant table
SQL> select b.tag
  2  from ant a right outer join bat b
  3  on (a.id = b.tag)
  4  where a.id is null
  5  order by 1;

       TAG
----------
         2
         7
         9