Skip to content

Posts from the ‘Uncategorized’ Category

ORA-12514: TNS:listener does not currently know of service requested in connect

it can be one of these

  • check database status for single instance and CDB and PDB status for multitenant
  • srvctl status database -d dbname or show pdbs from sqlplus
  • check services status with – srvctl status service -d dbname
  • check OUD or TNS entry for correct service name
  • check remote_listener parameter – show parameter remote_listener
  • check PDB name

Single quotes in strings – oracle SQL query

Apostrophe/single quote in a concatenated string

If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Today' || '''' || 's Date: ' || sysdate FROM dual;

Today’s Date: 09-DEC-20

select 'PDB Name is : ' || name || ':' from v$pdbs;

PDB Name is : ABC:

SELECT 'Hello' || '''' || 's World' FROM dual;

Hello’s World

Apostrophe/single quote at start of string

When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT '''Hello World' FROM dual;

would return

'Hello World

Apostrophe/single quote in the middle of a string

When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

SELECT 'She''s Here' FROM dual;

would return

She's Here

Apostrophe/single quote at the end of a string

When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Worlds''' FROM dual;

would return

Worlds'

CRS-4995 CRS-2510 CRS-2514

Problem

Error duing cluster upgrade dry run and also when you try to remove resource that is not exists

CLSRSC-729: Checking whether CRS entities are ready for upgrade, cluster upgrade will not be attempted now. This operation may take a few minutes.
PRCR-1129 : Failed to upgrade resource type ora.service.type
PRCR-1071 : Failed to register or update resource ora.abc.abcsvc.svc
CRS-2510: Resource ‘ora.abc.db’ used in dependency ‘hard’ does not exist or is not registered.
CRS-2514: Dependency attribute specification ‘hard’ is invalid in resource ‘ora.abc.abcsvc.svc’
CLSRSC-180: An error occurred while executing the command ‘/opt/oracle/crs/grid19c/bin/srvctl upgrade model -s 12.2.0.1.0 -d 19.0.0.0.0 -p first’
CLSRSC-694: failed to validate CRS entities for upgrade, aborting the upgrade
CLSRSC-362: The pre-upgrade checks failed, aborting the upgrade
Died at /opt/oracle/crs/grid19c/crs/install/crsupgrade.pm line 3779.

Solution:

Remove Resource using CRSCTL depends if exists or not use one of this command

crsctl delete resource ora.abc.abcsvc.svc

(if it throws error CRS-4995 then use below command)

crsctl delete resource ora.abc.abcsvc.svc -unsupported

CRS-4995: The command ‘Delete resource’ is invalid in crsctl. Use srvctl for this command

$ crsctl delete resource ora.abc.abcsvc.svc

CRS-4995: The command ‘Delete resource’ is invalid in crsctl. Use srvctl for this command.

Add “-unsupported” option to the command. It should apply to “crsctl modify resource” as well.

crsctl delete resource ora.abc.abcsvc.svc -unsupported

ORA-12008 ORA-06512 ORA-01722

Error:

Refreshing or creating view gives ORA-01722: invalid number

Executing the query “call emp.rfrsh_mv()” failed with the following error: “ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2952 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2370 ORA-01722: invalid number ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 85 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 245 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2352 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2908 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3191 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3221 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 15 ORA-06512: at “emp.rfrsh_mv”, line 35 ORA-06512: at “emp.rfrsh_mv”, line 35″. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Solution:

Try to run select query from materialized view outside and see if error is data related. in my case select query itself was returning ORA-01722: invalid number.

The error was that there was a conditional (case) statement that called the TO_NUMBER function against a column with a potential non numeric value ​. when it hit this non-numeric value, it threw the Oracle error. It had not before encountered this value so it had always worked. it was fixed by separating that specific conditional ( it was the second part of a conditional using the OR predicate) into 2 conditional statements so that the TO_NUMBER function would not be called against it if it had that particular non numeric value

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.

%d bloggers like this: