Archive for the ‘Oracle’ Category

Oracle 11g – MEMORY_MAX_TARGET and MEMORY_TARGET

May 22nd, 2019, posted in Oracle Queries
Share

Adjusting the memory_max_target based on available memory. 
This example is Linux x86-64.

If you can afford to set the memory_max_target higher then the memory_target this will give you room to grow the memory_target without restarting the database. 

SQL> show parameters memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 17920M

SQL> show parameters memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 17920M

System has 36GB physical RAM available.

SQL> !grep MemTotal /proc/meminfo
MemTotal: 36912956 kB

Server has now set aside 24GB for use with Oracle. Kernel shared memory parameter.

SQL>!df -h /dev/shm/

Filesystem Size Used Avail Use% Mounted on
tmpfs 24G 11G 14G 44% /dev/shm

Increase memory_max_target to 24GB.

SQL> ALTER SYSTEM SET memory_max_target = 24G SCOPE=SPFILE;
System altered.

Shutdown Oracle.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup Oracle.
SQL> startup
ORACLE instance started.

Total System Global Area 2.5655E+10 bytes
Fixed Size 2213776 bytes
Variable Size 2.0133E+10 bytes
Database Buffers 5368709120 bytes
Redo Buffers 151166976 bytes
Database mounted.
Database opened.

Update your pfile.

SQL> create pfile from spfile;
File created.

Verify the new settings. Max now 24GB and memory target is 17.9GB. We can now increase the memory_target if the need arises without shutting the database down.

SQL> sho parameters memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 24G

SQL> sho parameters memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 17920M
Share

Function To Convert Number To Arabic Number

April 28th, 2019, posted in Oracle Queries
Share
Function To Convert Number To Arabic Number,Arabic Number,English Arabic Number,Oracle Numbers,Oracle DBA,Oracle Apps DBA,Oracle Coding,Oracle Arabic Number Query
FUNCTION convert_to_arabic (p_input IN VARCHAR2, p_msg OUT VARCHAR2)
    RETURN VARCHAR2
    IS
    --

    l_output VARCHAR2(20) DEFAULT NULL;
    CURSOR csr_convert (cp_value VARCHAR2)

    IS

    SELECT TRANSLATE (cp_value,'0123456789',

                         UNISTR ('\0660')
                      || UNISTR ('\0661')
                      || UNISTR ('\0662')
                      || UNISTR ('\0663')
                      || UNISTR ('\0664')
                      || UNISTR ('\0665')
                      || UNISTR ('\0666')
                      || UNISTR ('\0667')
                      || UNISTR ('\0668')
                      || UNISTR ('\0669')
                    ) arabic_numerals
       FROM DUAL;

    BEGIN


    IF p_input IS NOT NULL THEN

      
        OPEN csr_convert(p_input);
        FETCH csr_convert INTO l_output;
        CLOSE csr_convert;

    END IF;

    RETURN l_output;
    EXCEPTION     
    WHEN OTHERS THEN
        IF csr_convert%ISOPEN THEN
            CLOSE csr_convert;

        END IF;
      
       p_msg := SUBSTR(SQLERRM,1,200);
       RETURN l_output;
    END convert_to_arabic;

 

Share

12c AdminServer 12.1.3 not starting and ending with error Critical WebLogicServer BEA-000386 Server subsystem failed. Reason: A MultiException has 6 exceptions

December 18th, 2018, posted in Oracle
Share

Symptom:

A MultiException has 6 exceptions. They are:

1. java.lang.AssertionError: Cannot export non clusterable object with jndiName:weblogic.jndi.internal.RootNamingNode@69f01d6c:
2. java.lang.IllegalStateException: Unable to perform operation: post construct on weblogic.jndi.internal.RemoteNamingService
3. java.lang.IllegalArgumentException: While attempting to resolve the dependencies of weblogic.management.mbeanservers.runtime.internal.RuntimeServerService errors were found
4. java.lang.IllegalStateException: Unable to perform operation: resolve on weblogic.management.mbeanservers.runtime.internal.RuntimeServerService
5. java.lang.IllegalArgumentException: While attempting to resolve the dependencies of weblogic.cacheprovider.CacheProviderServerService errors were found
6. java.lang.IllegalStateException: Unable to perform operation: resolve on weblogic.cacheprovider.CacheProviderServerService

 

Cause:

This issue is caused by entry of the following JAVA_OPTIONS parameter ‘-Djavax.xml.stream.XMLInputFactory=weblogic.xml.stax.XMLStreamInputFactory’ when deploying into WebLolgic 12c. In WebLogic 12c, the built-in WebLogic Server XML Input factory implementation class is com.ctc.wstx.stax.WstxInputFactory, and the XML Factory Input should be set to this value. Due to a documentation bug, the manual deployment guide for P6 is still referencing the 11g implementation class, causing the reported issue to occur.

 

Fix:

Edit setDomainEnv.sh

 

Locate the line:

JAVA_OPTIONS at the end (added by the installer)

 

Change it to:

export JAVA_OPTIONS=”-Djavax.xml.stream.XMLInputFactory=com.ctc.wstx.stax.WstxInputFactory -Dcom.sun.xml.namespace.QName.useCompatibleSerialVersionUID=1.0 -Doracle.jdbc.V8Compatible=true ${JAVA_OPTIONS}”

 

Reference Doc:

MOS Document ID: WebLogic 12c Managed Server Fails To Start When Passing The “-Djavax.xml.stream.XMLInputFactory=weblogic.xml.stax.XMLStreamInputFactory” Java Argument (Doc ID 2003152.1)

 

Share

How To Cancel or Restart the Cost Manager

December 18th, 2018, posted in Oracle Queries
Share

Behavior of Cost Manager:

Cost manager is not a typical concurrent manager that it finishes after is costing some transactions.

Cost manager it is rescheduling itself based on the the process interval specified in interface managers form.

Cost Manager should stay in this status : “Pending Scheduled” and will restart itself based on the process interval specified in interface managers form. It calculates the re-launch time as start time + process interval.

 

2 – Run the cmclean.sql script available from MOS Note 134007.1

3 – Restart the Cost Manager

     Inventory> Setup : Transactions> Interface Managers

     Select  ‘Cost Manager’ – choose Tools > Launch Manager

Also review << Note 304313.1>>

  1. Restart the concurrent processes.
  2. Run the below select and update scripts to update the uncosted and/or erred records in the tables indicated.
  3. Relaunch the Cost Manager. Select and Update scripts for paragraph 5. above:

SQL> select count(*) from MTL_MATERIAL_TRANSACTIONS where COSTED_FLAG = ‘E’;

SQL> select count(*) from MTL_MATERIAL_TRANSACTIONS where COSTED_FLAG = ‘N’;

SQL > Update MTL_MATERIAL_TRANSACTIONS set COSTED_FLAG = ‘N’, TRANSACTION_GROUP_ID = NULL where COSTED_FLAG = ‘E’ or COSTED_FLAG = ‘N’;

If there are unprocessed records in MTL_MATERIAL_TRANSACTIONS_TEMP, then, also run the following:

  1. To see if there are records in mtl_material_transactions_temp, run:

SQL> select * from mtl_material_transactions_temp;

  1. If rows are returned from the SQL in a. above, then run:

SQL> Update MTL_MATERIAL_TRANSACTIONS_TEMP Set PROCESS_FLAG = ‘Y’, LOCK_FLAG = ‘N’, TRANSACTION_MODE = 3, ERROR_CODE = NULL Where TRANSACTION_HEADER_ID = ;

(OR)

Resubmit all transactions using this procedure:

  • Shut down the cost manager
  • Make a backup of the table mtl_material_transactions
  • Perform the following update

SQL > update mtl_material_transactions

set costed_flag = ‘N’,

transaction_group_id = NULL,

transaction_set_id = NULL

where costed_flag = ‘E’ or costed_flag = ‘N’ ;

Commit ;

 

 

Update MTL_MATERIAL_TRANSACTIONS set COSTED_FLAG = ‘N’, TRANSACTION_GROUP_ID = NULL where COSTED_FLAG = ‘E’ or COSTED_FLAG = ‘N’;


 

Uncosted/failed transactions can be resubmitted through the application itself.
Costs -> View Transactions -> Material Transactions.
From the menu, Tools Select All: Tools Submit All

Else, use the following script to submit the records in MMT table:

    update mtl_material_transactions MMT
    set MMT.costed_flag = 'N',
    MMT.error_code = NULL,
    MMT.error_explanation = NULL,
    MMT.transaction_group_id = NULL,
    MMT.transaction_set_id = NULL
    where MMT.organization_id = &organization_id
    and MMT.costed_flag in ('N','E')
    and NOT  EXISTS ( SELECT 1
    FROM MTL_TRANSACTION_ACCOUNTS MTA
    WHERE MMT.TRANSACTION_ID = MTA.TRANSACTION_ID);

commit;

  1. Re-start the cost manager.
    Restart the cost manager via Inventory > Setup > Transactions >
    Interface Managers > Tools > Launch Manager. Set up a periodic scheduling interval of every five
    minutes.
    Note — Cost Manager should not be scheduled through request form to run Periodically.
    Cost Manger  should be scheduled through Interface managers only.
Share

Re-Enter Toad License Info

December 9th, 2018, posted in Oracle
Share

ToadReview,Toad DBA Suite for Oracle 11.6 License Key,Toad DBA Suite for Oracle 11.6 ,License Key,Toad DBA Suite for Oracle 11.6 License,Key,Toad DBA Suite,Oracle 11.6 License Key,Toad,DBA Suite for Oracle 11.6 License Key,Toad License Key,Toad DBA License Key,

Enter your license information once again. Toad will then save a file namedQSAuth11.keyinto your default application data folder, which should be:

C:\Documents and Settings\Application Data\Quest Software\Toad for Oracle\10.0 (for WinXP and earlier)

C:\Users\AppData\Roaming\QuestSoftware\Toad for Oracle\10.0 (for Vista and higher)
Share