Skip to content

Posts from the ‘Uncategorized’ Category

Upgrade to 18c is Failing With ORA-29521: referenced name oracle/xdb/XMLType could not be found

  • Errors during upgrade to 18c:
    • ORA-29521: referenced name oracle/xdb/XMLType could not be found
    • You may also see errors similar to ORA-29532: Java call terminated by uncaught Java exception: oracle.aurora.server.tools.loadjava.ToolsError:Error during loadjava: Failures occurred during processing.
  • XDK is not installed prior to the upgrade

CHANGES

CAUSE

  •  This is caused by unpublished <Bug 27916323 – XML COMPONENT NEEDS TO UPDATE UPGRADE/DOWNGRADE SCRIPTS FOR VERSION 18c>

SOLUTION

  • If you have XDK installed prior to the upgrade to 18c, this error will not take place.
    • Install XDK 10.2 or Later.
    • It should be noted that a full JVM install will install XDK which can be accomplished using  Safe repair/reinstall of the JVM Component in 11.2 and up>

OR

  • Apply <Patch 27916323 – XML COMPONENT NEEDS TO UPDATE UPGRADE/DOWNGRADE SCRIPTS FOR VERSION 18c>. If the patch is not available for your platform please log a support with the XML DB(XDB) team with your list of your one patches (opatch lsinventory -detail >inventory.txt).

RMAN commands

 

LIST BACKUP OF database COMPLETED BETWEEN ’01-APR-2020′ and ’02-APR-2020′;
LIST BACKUP SUMMARY COMPLETED BETWEEN ’01-APR-2020′ and ’02-APR-2020′;

Distributed Transaction In MTS Using Shared Dblink Fails With Ora-00022

SYMPTOMS

When using shared dblinks to do a distributed transaction from
MTS to MTS the client might see errors of the form

ORA-02050: transaction 840.21.16 rolled back, some remote DBs may be in-doubt
ORA-02068: following severe error from …
ORA-00022: invalid session ID; access denied

CAUSE

Shared servers closed down there by closing the dblink

SOLUTION

Set shared_servers = max_shared_servers in the remote instance

ORA-00022 While Accessing Shared DB Link

SYMPTOMS

Application team created a shared public database link, while accessing the link , they are getting below errors.

ERROR at line 1:
ORA-00022: invalid session ID; access denied
ORA-02063: preceding line from <DBLINK-NAME>
Process ID: 59963
Session ID: 291 Serial number: 12343

 

CHANGES

 

CAUSE

BUG 18759589

SOLUTION

Please apply the below patch and  let me know in case of any issues..

Patch 18759589: ESSC: ORA-22 AFTER UPGRADE FROM 11.2.0.2 TO 11.2.0.4

Release Oracle 11.2.0.4.0

Platform Linux x86-64

ASAP Periodically Raises ORA-00020 in diag files

SYMPTOMS

Following error is generated periodically in ASAP diag file:

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

CAUSE

This problem can happen for one of the following reasons in ASAP:

1) An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES is used up. When this maximum number of process is reached, no more requests will be processed.

2) If this problem happens after adding more servers (Example, NEP servers) or NE in ASAP system or after increasing work load and problem happens during pick hour then reason and remedy will be the same as item # 1 (above)

3) If problem happens after certain interval (For example, every 3 or 4 days interval) it may cause due to connection leak and which is normally in custom cartridge code or class -A cartridge code.

SOLUTION

Regarding the scenario # 1 and # 2, please consult with your DBA and increase the value of Oracle DB parameter called ‘processes’.

You can follow the steps below to increase the parameter ‘processes’:

1. Stop ASAP
2. Stop DB
3. Increase the Oracle DB configuration parameter ‘processes’
4. Restart the DB
5. Restart ASAP

Solution for scenario # 3:

The cartridge code needs to be reviewed to make sure all the connections from NEP/JNEP to the Oracle DB server are closing properly from the cartridge code.

ORA-00020 Reported When GES_PROCS Used Up

SYMPTOMS

ORA-20 Occurred when current process number is still far from parameter processes setting, and ges_procs used up in v$resource_limit

select * from v$resource_limit;

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
—————————— ——————- ————— ——————– ——————–
processes 19766 20472 30000 30000 <============= max utilization of processes is only 20472, which is far from the max limit of 30000
sessions 19795 20499 45072 45072 <===========
enqueue_locks 21128 21234 515026 515026
enqueue_resources 1652 2169 201336 UNLIMITED
ges_procs 19761 19761 19761 19761 <================= ges_procs is used up
ges_ress 370233 1000559 799194 UNLIMITED

CAUSE

The same issue is investigated in

Bug 20734725 – ORA-20 ERROR REPORTED DUE TO GES_PROCS USED UP.

Which is closed as “Not a bug” but a configuration issue – _ksmg_granule_size is set too low, in this case granule size should be 512M, however it is currently set to 32MB manually.

SOLUTION

Set a larger _ksmg_granule_size, eg 512MB when SGA_MAX_SIZE (or memory_max_target) is larger than 128GB.

SQL> alter system set “_ksmg_granule_size”=536870912 scope=spfile;

restart database is needed.

ORA-16579: bad network state detected

Problem

DGMGRL> show configuration;

Configuration – dg_prmydb_config

Protection Mode: MaxPerformance
Members:
stbydb – Primary database
Error: ORA-16579: bad network state detected

prmydb – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 0 seconds ago)

DGMGRL> show database stbydb

Database – stbydb

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prmydb_1
prmydb_2

Database Status:
DGM-17016: failed to retrieve status for database “stbydb”
ORA-16662: network timeout when contacting a member

 

Solution:

Check alert log file of respective database.

in my case alertlog file shows ASM mount point space exhausted

ORA-19504: failed to create file “+DATA1”
ORA-17502: ksfdcre:4 Failed to create file +DATA1
ORA-15041: diskgroup “DATA1” space exhausted
ARC2: Error 19504 Creating archive log file to ‘+DATA1’

I cleared some space by backup and cleaning up archive log file.

 

 

 

 

 

 

 

 

 

Oracle INTO clause Example

Examples

The following example demonstrates using the SELECT INTO statement to query a single value into a PL/SQL variable, entire columns into PL/SQL collections, or entire rows into a PL/SQL collection of records:

DECLARE
   howmany NUMBER;
   some_first employees.first_name%TYPE;
   some_last employees.last_name%TYPE;
   some_employee employees%ROWTYPE;
   TYPE first_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   TYPE last_typ IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
   first_names first_typ;
   last_names last_typ;
   CURSOR c1 IS SELECT first_name, last_name FROM employees;
   TYPE name_typ IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
   all_names name_typ;
   TYPE emp_typ IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   all_employees emp_typ;
BEGIN
-- Query a single value and store it in a variable.
   SELECT COUNT(*) INTO howmany FROM user_tables;
   dbms_output.put_line('This schema owns ' || howmany || ' tables.');

-- Query multiple columns from one row, and store them in variables.
   SELECT first_name, last_name INTO some_first, some_last
      FROM employees WHERE ROWNUM < 2;
   dbms_output.put_line('Random employee: ' || some_first ||
      ' ' || some_last);

-- Query a single row and store it in a record.
   SELECT * INTO some_employee FROM employees WHERE ROWNUM < 2;

-- Query multiple columns from multiple rows, and store them in a collection
-- of records.
   SELECT first_name, last_name BULK COLLECT INTO all_names FROM EMPLOYEES;

-- Query multiple columns from multiple rows, and store them in separate
-- collections. (Generally less useful than a single collection of records.)
   SELECT first_name, last_name
      BULK COLLECT INTO first_names, last_names
      FROM EMPLOYEES;

-- Query an entire (small!) table and store the rows
-- in a collection of records. Now you can manipulate the data
-- in-memory without any more I/O.
   SELECT * BULK COLLECT INTO all_employees FROM employees;
END;
/

Sql Tune to improve buffer gets

Before buffer get tune. SQL plan was doing index fast  full scan. here is you can see that

buffer_get_1

buffer_get_2

After running SQL tuning adviser that suggest to implement new profile that took index rang scan and buffer get improved dramatically also CPU cost.

buffer_get_3

buffer_get_4

as you can see above buffer get reduced from 22K to 615 and CPU cost reduced from 772 to 1. huge improvement.

 

 

 

 

 

Scripts

Generate grants statements

select ‘grant select,insert,delete,update on ‘ || owner || ‘.’ || table_name || ‘ to app_role;’ from dba_tables where owner=’XYZ’ ;

Generate synonyms

select ‘create or replace public synonym ‘ || table_name || ‘ for ‘ || owner || ‘.’ || table_name || ‘ ;’ from dba_tables where owner=’XYZ’ ;

 

%d bloggers like this: