Skip to content

Posts from the ‘ERROR’ Category

Feb 4

ORA-22924 snapshot too old Error

Problem:

Ora-22924 snapshot too old error occurs in Oracle Database when a query tries to access a row that has been modified or deleted by another transaction before the query started.

Solution:

To resolve this issue, you can increase the undo retention time so that the older versions of the data required by the query are preserved. This can be done by setting the UNDO_RETENTION parameter in the initialization parameter file. Additionally, you can use the COMMIT option in the query to explicitly commit the transaction, thereby freeing up the undo space used by the transaction. Another solution is to use the FLASHBACK query feature to retrieve the older version of the data required by the query.

Example

Here’s an example to help explain the “ora-22924 snapshot too old” error and how to resolve it.

Suppose you have a table named “customers” in your Oracle Database with the following data:IDNAMEADDRESS1AliceNew York2BobLondon

Now, assume that you start a transaction and execute the following query to retrieve the data for customer with ID 1:

SELECT * FROM customers WHERE ID = 1;

While the transaction is still active, another transaction updates the data for customer with ID 1:

UPDATE customers SET NAME = ‘Charlie’ WHERE ID = 1;

If you try to commit the first transaction now, you will get the “ora-22924 snapshot too old” error, because the query in the first transaction tried to access a row that has been modified by another transaction.

To resolve this issue, you can increase the undo retention time so that the older versions of the data required by the query are preserved. For example, you can add the following line to the initialization parameter file:

UNDO_RETENTION = 1800;

This sets the undo retention time to 1800 seconds (30 minutes).

Alternatively, you can use the COMMIT option in the query to explicitly commit the transaction, freeing up the undo space used by the transaction. For example:

SELECT /*+ COMMIT */ * FROM customers WHERE ID = 1;

Another solution is to use the FLASHBACK query feature to retrieve the older version of the data required by the query. For example:

SELECT * FROM customers AS OF TIMESTAMP (SYSTIMESTAMP – NUMTODSINTERVAL(30,’MINUTE’) WHERE ID = 1;

This retrieves the data for customer with ID 1 as it was 30 minutes ago.

Dec 3

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP01

Problem:

TEMP tablespace was getting filled when running sql statement.

i increased the temp table size from 25g, 32g, 64 gb. in all cases it throws error.

Solution:

I ran sql tuning advisor on sql and it gave recommendation of profile, index and gather stats.

i created profile, index and gather stats. it did magic sql completed under 5 seconds and did not throw any error.

Sep 7

RCONFIG – Cannot get SQLEngine

Problem: convert to rac from single instance using rconfig gives error

$ORACLE_HOME/bin/rconfig ConvertToRAC_AdminManaged.xml

oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: Cannot get SQLEngine
Operation Failed. Refer logs at /opt/oracle/app/itdoracle/12c/cfgtoollogs/rconfig/rconfig_09_07_21_15_55_33.log for more details.

Cause: ORACLE_HOME was not set properly in xml file

          <n:SourceDBHome>/opt/oracle/ora12201</n:SourceDBHome>

          <n:TargetDBHome>/opt/oracle/ora12c</n:TargetDBHome>

Solution: source and target were on the same server so set it accordingly to new server

          <n:SourceDBHome>/opt/oracle/ora12201</n:SourceDBHome>

          <n:TargetDBHome>/opt/oracle/ora12201</n:TargetDBHome>

Aug 26

STARTUP Database failed ORA-38760 to turn on Flashback Database

SYMPTOMS

This is not RAC specific and could happen in Single Instances as well
if Flashback logs required are not available, as for example FLB logs was deleted using OS commands.


The following example shows symptoms seen in a RAC environment.

One of the RAC Instances does not come up, its looking for a Flash back log which has been accidently deleted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

——–From alert log of the thread 2 ——————

Thu May 16 05:17:45 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=33, OS id=7292
Thu May 16 05:17:48 2013
Errors in file /<path>/udump/<SID>_ora_7244.trc:
ORA-38701: Flashback database log 25184 seq 14006 thread 1: “+<Disk>/<db unique name>/flashback/log_25184.25633.809806337”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_25184.25633.809806337
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_25184.25633.809806337’ does not exist
Thu May 16 05:17:48 2013
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Thu May 16 05:17:48 2013
ALTER DATABASE OPEN
ORA-38760 signalled during: ALTER DATABASE OPEN…                                                                   <<<< Error on opening database

Oracle is trying to turn on flashback database on startup automatically and failing with ORA-38760 as flashback log is missing.

CHANGES

Current flashback log deleted accidently from OS level.

CAUSE

When Current flashback log is deleted then database open will fail with following error:

Starting ORACLE instance (normal)
…….

Thu May 16 05:17:45 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=33, OS id=7292
Thu May 16 05:17:48 2013
Errors in file /<path>/udump/<sid>_ora_7244.trc:
ORA-38701: Flashback database log 25184 seq 14006 thread 1: “+<Disk>/<db unique name>/flashback/log_25184.25633.809806337”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_25184.25633.809806337
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_25184.25633.809806337’ does not exist
Thu May 16 05:17:48 2013
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Thu May 16 05:17:48 2013
ALTER DATABASE OPEN
ORA-38760 signalled during: ALTER DATABASE OPEN…

OR Instance may crash with following error:ORA-38701: Flashback database log 101 seq 101 thread 1: “/<path>/flashback/o1_mf_15fb13pw_.flb”
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sat Apr 9 03:13:52 2005
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5725

SOLUTION

There are 2 possible solutions when a flashback log file was deleted manually:

(1) Turn off Flashback:SQL> alter database flashback off;

The Alert log will have the following entries when you turn off flashback:Thu May 16 05:18:01 2013
alter database flashback off
Thu May 16 05:18:01 2013
Flashback Database Disabled
…….
Completed: alter database flashback off                                                                              <<<<  Flashback disabled
…..

After this turn the flashback on again if required.SQL> alter database flashback on;

Now you can open the database.

(2) In cases where “Guaranteed Restore Point” is defined, RVWR will still try to write to flashback log even though you have disable it and it will fail again with same error:Completed: alter database flashback off                                                                              <<<<  Flashback disabled
…..

Thu May 16 05:19:05 2013
Shutting down instance: further logons disabled
Thu May 16 05:19:05 2013
Stopping background process CJQ0
……

Shutting down instance (immediate)
……..
Thu May 16 05:24:49 2013
Starting ORACLE instance (normal)
…….
Thu May 16 05:25:04 2013
Allocated 15937344 bytes in shared pool for flashback generation buffer
Starting background process RVWR
RVWR started with pid=33, OS id=19851
Thu May 16 05:25:05 2013
Errors in file /<path>/udump/<SID>_ora_19796.trc:                                                  
ORA-38701: Flashback database log 25184 seq 14006 thread 1: “+<Disk>/<db unique name>/flashback/log_25184.25633.809806337”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_25184.25633.809806337
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_25184.25633.809806337’ does not exist                                <<<< Still looking for deleted flashback log
Thu May 16 05:25:05 2013
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE   MOUNT
Thu May 16 05:25:05 2013
ALTER DATABASE OPEN
ORA-38760 signalled during: ALTER DATABASE OPEN…                                                                   <<<< Again failed with ORA-38760

Check if flashback database is disable or not. When checked the flashback status after disabling flashback, it shows ‘RESTORE POINT ONLY’ instead of ‘NO’.

SQL>  select flashback_on from v$database;

FLASHBACK_ON
——————
RESTORE POINT ONLYFLASHBACK_ON – possible values are as follows:

    YES                – Flashback is on
    NO                 – Flashback is off
    RESTORE POINT ONLY – Flashback is on but one can only flashback to guaranteed restore points

 
So the cause of the issue is Guaranteed Restore Point created on database.
The database would still try to write flashback data to the current flashback log because the database still has at least one Guaranteed Restore Point declared in the controlfile.

Find out the name of Guaranteed Restore Point and delete so that database would not try to write to flashback log on startup:

Now we have 3 options to know the restore point name:

2.1) Check the name from v$restore_point view but that would also fail with same error:SQL> select * from v$restore_point;
select * from v$restore_point
*
ERROR at line 1:
ORA-38701: Flashback database log 2 seq 2 thread 1: “+<Disk>/<db unique name>/flashback/log_2.2286.801367563”
ORA-17503: ksfdopn:2 Failed to open file +<Disk>/<db unique name>/flashback/log_2.2286.801367563
ORA-15012: ASM file ‘+<Disk>/<db unique name>/flashback/log_2.2286.801367563’ does not exist

Here we are not able to query v$restore _point to find out the name.

2.2) Search for restore point name in alert log. In this case customer was purging alert log every year starting so could not find name for the Restore Point.

2.3) Dump the controlfile to get the restore point name:SQL> oradebug setmypid
SQL> alter session set events ‘immediate trace name controlf level 9’;
SQL> oradebug tracefile_name


From trace file of controlfile dump, we could see below information:***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
 (size = 212, compat size = 212, section max = 2048, section in-use = 1,
  last-recid= 1, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 240, numrecs = 2048)
RESTORE POINT #1:
 restore point name: <restore point test> guarantee flag: 1 incarnation: 2next record 0                            <<<< Name of restore point
 restore point scn: 0x0000.fbff3d87 12/07/2012 02:16:32

Now we have name of Guaranteed Restore Point:

SQL> Drop restore point <restore point test>;

SQL> alter database open;

Database AlteredNote:  This error may also occur if the FRA is full and thus a flashback log cannot be created.  Check V$flash_recovery_area_usage and/or V$recovery_file_dest regarding space availability in FRA

Aug 9

Backup of backupset failed with RMAN-6500 skipping backup set key %s; already backed up %s time(s)

SYMPTOMS

Backup of backupset skipped with below messages :

RMAN> backup backupset from tag ‘<tag_name>’ format ‘/<path>/%U’;

Starting backup at 22-AUG-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
skipping backup set key 1362; already backed up 1 time(s)
skipping backup set key 1363; already backed up 1 time(s)

CAUSE

Initially  backup of database was taken on disk using below command :Rman> Backup as backupset tag ‘<tag_name>’ database;


Now the requirement is  to take backup of above backupset to different location on disk :

Rman> Backup backupset from tag ‘<tag_name>’ format ‘/<path>/%U’;



Backup optimization is ON ,therefore backup set was not copied because it is already backed up on the device type requested.
 

SOLUTION

Below are two options to resolve this issue :

1.) Turn off backup optimization :RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;


2.)Take backup of backupset using force option which will override backup optimization :RMAN> Backup force backupset from tag ‘<tag_name>’  format ‘/<path>/%U’;

Jun 30

ORA-16857: member disconnected from redo source for longer than specified threshold

Problem:

Data Guard configuration shows error at standby site

ORA-16857: member disconnected from redo source for longer than specified threshold

Solution:

Disable standby database in dgmgrl and Enable again. it solves problem after few minutes when log archive gap filled.

Demo:

-bash-4.2$ dgmgrl
DGMGRL> connect sys/a@db1
Connected to "db1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - dg_db1_config

  Protection Mode: MaxPerformance
  Members:
  db1    - Primary database
    Error: ORA-16778: redo transport error for one or more members

    stadby_db - Physical standby database
      Warning: ORA-16857: member disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 2 seconds ago)

DGMGRL> show database db1

Database - db1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    db1_1
    db1_2
      Error: ORA-16737: the redo transport service for member "stadby_db" has an error

Database Status:
ERROR

DGMGRL> show database stadby_db

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 175714 seconds ago)
  Apply Lag:          0 seconds (computed 175714 seconds ago)
  Average Apply Rate: 188.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

  Database Warning(s):
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

DGMGRL> disable database stadby_db;
Disabled.
DGMGRL> enable database stadby_db;
Enabled.
DGMGRL> show database stadby_db;

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      1 day(s) 14 hours 47 minutes 10 seconds (computed 0 seconds ago)
  Apply Lag:          1 day(s) 18 hours 16 minutes 17 seconds (computed 0 seconds ago)
  Average Apply Rate: 189.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold
    ORA-16855: transport lag has exceeded specified threshold

Database Status:
WARNING


DGMGRL> show database stadby_db;

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 254.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - dg_db1_config

  Protection Mode: MaxPerformance
  Members:
  db1    - Primary database
    stadby_db - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 52 seconds ago)

DGMGRL>

Jun 8

Oracle Database 19c Upgrade Error

Getting an error on Oracle Multimedia component after upgrade of database to 19c


Oracle Multimedia
ORA-04067: not executed, package body “MDSYS.MDPRVT_IDX” does not exist ORA-
06512: at line 206
ORA-06508: PL/SQL: could not find program unit being called:
“MDSYS.MDPRVT_IDX”

CAUSE

This behavior is the result of a software defect that Oracle Development has identified as Bug 29782284.
 

SOLUTION

Prior to the upgrade download and apply the patch for bug 29782284.

If the upgrade has been completed, run utlrp.sql and dba_registry will have Multimedia removed.


 

Jun 8

19c:SRVCTL status service fails with Java.lang.NullPointerException

Oracle Database – Enterprise Edition – Version 19.3.0.0.0 and later
Information in this document applies to any platform.

SYMPTOMS

The following errors occur while running srvctl status service command.

[oracle@<db_name> ~]$ srvctl status service -d <dbservice_name>
Exception in thread “main” java.lang.NullPointerException
at oracle.ops.opsctl.StatusAction.internalExecuteService(StatusAction.java:2038)
at oracle.ops.opsctl.StatusAction.executeSIHAService(StatusAction.java:1652)
at oracle.ops.opsctl.Action.executeSIHA(Action.java:443)
at oracle.ops.opsctl.OPSCTLDriver.execute(OPSCTLDriver.java:537)
at oracle.ops.opsctl.OPSCTLDriver.main(OPSCTLDriver.java:25) 

[oracle@<db_name> ~]$ srvctl status database -d <db_name>
Database is running.

[oracle@<db_name> ~]$ srvctl config database -d <db_name>
Database unique name: <db_name>
Database name: <db_name>
Oracle home: <rdbms_home>/product/12.2.0.3/db_1
Oracle user: oracle
Spfile: +<diskgroup_name>/<db_name>/spfile<dba_name>.ora
Password file:
Domain: <domain_name>
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: +<diskgroup_name1>,+<diskgroup_name2>
Services:<db_service>
OSDBA group:
OSOPER group:
Database instance: <db_name>

[oracle@<db_name> ~]$ srvctl config service -d <dbservice_name>
Service name:<dbservice_name>
Cardinality: SINGLETON
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Service uses Java: false

 srvm.trc[main] [ 2019-05-22 11:17:58.926 EDT ] [EntityOperations.isEntityRegisteredCRSD:367] entity: ora.<db_name>.<service_name>.svc, type: 1, registered: true
[main] [ 2019-05-22 11:17:58.926 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.<service_name>.svc, type 1, node: null
[main] [ 2019-05-22 11:17:58.926 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [SERVER_POOLS, CARDINALITY, USR_ORA_DISCONNECT, ROLE, MANAGEMENT_POLICY, DTP, AQ_HA_NOTIFICATION, COMMIT_OUTCOME, FAILOVER_TYPE, FAILOVER_METHOD, FAILOVER_RETRIES, FAILOVER_RESTORE, TAF_FAILOVER_DELAY, CLB_GOAL, RLB_GOAL, GLOBAL, MAX_LAG_TIME, RETENTION, REPLAY_INITIATION_TIME, SESSION_STATE_CONSISTENCY, SERVICE_NAME_PQ, SERVICE_TYPE, GSM_FLAGS, TABLE_FAMILY_ID, DRAIN_TIMEOUT, STOP_OPTION, CSS_CRITICAL, TAF_POLICY, EDITION, PLUGGABLE_DATABASE, HUB_SERVICE, MAX_LAG_TIME, SQL_TRANSLATION_PROFILE, ACTIONS, ACTIVE_PLACEMENT]
[main] [ 2019-05-22 11:17:58.989 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<ACTIONS:isJavaService,group:”dba”,user:”oracle” rf_action,group:”dba”,user:”oracle”>, <ACTIVE_PLACEMENT:0>, <AQ_HA_NOTIFICATION:0>, <CARDINALITY:1>, <CLB_GOAL:LONG>, <COMMIT_OUTCOME:0>, <CSS_CRITICAL:no>, <DRAIN_TIMEOUT:>, <DTP:0>, <EDITION:>, <FAILOVER_METHOD:>, <FAILOVER_RESTORE:NONE>, <FAILOVER_RETRIES:>, <FAILOVER_TYPE:>, <GLOBAL:false>, <GSM_FLAGS:0>, <HUB_SERVICE:>, <MANAGEMENT_POLICY:AUTOMATIC>, <MAX_LAG_TIME:ANY>, <PLUGGABLE_DATABASE:>, <REPLAY_INITIATION_TIME:300>, <RETENTION:86400>, <RLB_GOAL:NONE>, <ROLE:PRIMARY>, <SERVER_POOLS:>, <SERVICE_NAME_PQ:>, <SERVICE_TYPE:MAIN>, <SESSION_STATE_CONSISTENCY:DYNAMIC>, <SQL_TRANSLATION_PROFILE:>, <STOP_OPTION:>, <TABLE_FAMILY_ID:>, <TAF_FAILOVER_DELAY:>, <TAF_POLICY:NONE>, <USR_ORA_DISCONNECT:false>]
[main] [ 2019-05-22 11:17:58.990 EDT ] [ServiceArgs.setServiceType:230] set serviceType to MAIN
[main] [ 2019-05-22 11:17:58.990 EDT ] [ServiceArgs.setServiceType:230] set serviceType to MAIN
[main] [ 2019-05-22 11:17:58.990 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.<service_name>.svc, type 1, node: null
[main] [ 2019-05-22 11:17:58.990 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [SERVER_POOLS]
[main] [ 2019-05-22 11:17:59.007 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<SERVER_POOLS:>]
[main] [ 2019-05-22 11:17:59.007 EDT ] [ServiceImpl.getServiceCardinality:3425] value of SERVER_POOLS attr :
[main] [ 2019-05-22 11:17:59.007 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.<service_name>.svc, type 1, node: null
[main] [ 2019-05-22 11:17:59.007 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [CARDINALITY]
[main] [ 2019-05-22 11:17:59.027 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<CARDINALITY:1>]
[main] [ 2019-05-22 11:17:59.027 EDT ] [ServiceImpl.getServiceCardinality:3435] value of CARDINALITY attr : 1
[main] [ 2019-05-22 11:17:59.027 EDT ] [ServiceImpl.getServiceCardinality:3462] this is a SIHA service
[main] [ 2019-05-22 11:17:59.029 EDT ] [CRSCache.getAttributesFromCache:229] CRS cache: ora.<db_name>.db [<DATABASE_TYPE:SINGLE>]
[main] [ 2019-05-22 11:17:59.029 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.db, type 1, node: null
[main] [ 2019-05-22 11:17:59.029 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [HOSTING_MEMBERS]
[main] [ 2019-05-22 11:17:59.046 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<HOSTING_MEMBERS:>]
[main] [ 2019-05-22 11:17:59.046 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.db, type 1, node: null
[main] [ 2019-05-22 11:17:59.046 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [SERVER_POOLS]
[main] [ 2019-05-22 11:17:59.068 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<SERVER_POOLS:>]
Exception in thread “main”
java.lang.NullPointerException
at oracle.ops.opsctl.StatusAction.internalExecuteService(StatusAction.java:2038)
at oracle.ops.opsctl.StatusAction.executeSIHAService(StatusAction.java:1652)
at oracle.ops.opsctl.Action.executeSIHA(Action.java:443)
at oracle.ops.opsctl.OPSCTLDriver.execute(OPSCTLDriver.java:537)
at oracle.ops.opsctl.OPSCTLDriver.main(OPSCTLDriver.java:252)
[Thread-1] [ 2019-05-22 11:17:59.069 EDT ] [InnerInterruptHandler.run:116] Running the shutdown hook
[Thread-1] [ 2019-05-22 11:17:59.069 EDT ] [InnerInterruptHandler.run:120] Calling Interrupt handler….ctrlCHandler
[Thread-1] [ 2019-05-22 11:17:59.069 EDT ] [CommandLineParser.<init>:506] Is this a cluster? : false
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [OPSCTLDriver.ctrlCHandler:306] Stopped srvctl execution
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [OPSCTLDriver.ctrlCHandler:307] Invoking interrupt handler
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [CmdOptVals.isOptionSet:1076] Calling isOptionSet for verbose
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [OptVal.isSet:155] m_value is null
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [SRVCTLHandler.getMethodName:122] getMethodName: Method Name = status_service_Handler
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [SRVCTLHandler.invokeCtrlCHandler:230] Interrupt handler method status_service_Handler
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [SRVCTLHandler.invokeCtrlCHandler:231] Interrupt handler class oracle.ops.opsctl.StatusAction
[Thread-1] [ 2019-05-22 11:17:59.071 EDT ] [SRVCTLHandler.invokeCtrlCHandler:244] oracle.ops.opsctl.StatusAction.status_service_Handler()
java.lang.Class.getMethod(Class.java:1786)
oracle.ops.opsctl.SRVCTLHandler.invokeCtrlCHandler(SRVCTLHandler.java:234)
oracle.ops.opsctl.OPSCTLDriver.ctrlCHandler(OPSCTLDriver.java:308)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
oracle.cluster.common.InnerInterruptHandler.run(InterruptHandler.java:123)
[Thread-1] [ 2019-05-22 11:17:59.086 EDT ] [OPSCTLDriver.ctrlCHandler:311] PRCG-1036 : Internal error.
oracle.ops.opsctl.SRVCTLHandler.invokeCtrlCHandler(SRVCTLHandler.java:245)
oracle.ops.opsctl.OPSCTLDriver.ctrlCHandler(OPSCTLDriver.java:308)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
oracle.cluster.common.InnerInterruptHandler.run(InterruptHandler.java:123)

CAUSE

Due to bug 29839229
 

SOLUTION

As workaround,

srvctl status service -d <dbname> -s <service_name>

Bug 29839229 fixed in a 20.1 version, request/apply patch 29839229 for your platform/version

Jun 4

Remote_listener Getting Reset To Blank After Cluster Startup

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 to 19.3.0.0.0 [Release 12.2 to 18]
Information in this document applies to any platform.

SYMPTOMS

users get error while connecting to database

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


remote_listener value got reset to blank after db node patching on all databases of the cluster causing databases not able to connect from outside, and databases are not getting registered with scan_listeners.
crsd_oraagent_oracle.trc inside <oracle base>/diag/crs/<HOSTNAME>/crs/trace location indicate following messages2019-05-11 19:41:49.764 : USRTHRD:3059689216: {2:49138:41584} LsnrRegThd::setRemoteListener 391 tmpSet endp:<scan name>:<PORT>
2019-05-11 19:41:49.764 : USRTHRD:3059689216: {2:49138:41584} LsnrRegThd::setRemoteListener 440 skip remote listener registration m_runRegRLUpdate:0 isForceRemoteListenerSet:0 isRemoteListenerSet:1
2019-05-11 19:41:49.774 : USRTHRD:3059689216: {2:49138:41584} ORA-01405: fetched column value is NULL

CHANGES

CAUSE

This is due to unpublished Bug 29637215 – FAEOPS: SHELL DB DOESN’T HAVE REMOTE_LISTENER VALUE WITH GRID 19.1.0.3
 

SOLUTION

Bug 29637215 fixed in future release. Apply interim fix 29637215, if available for your platform and Oracle version.

If no patch exists for your version, please contact Oracle Support for a backport request

As a workaround,

Manually update the remote_listener values every time bounce the instances from this cluster.

alter system set remote_listener=’racscan.com:1521′ scope=both sid=’*’;


 

Jun 3

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor after cluster upgrade to 19c

Problem

users get error while connecting to database

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

Cause:

Cluster upgrade from 12.2 to 19c

Solution:

set remote_listener parameter

alter system set remote_listener=’racscan.com:1521′ scope=both sid=’*’;

« Older Entries