Skip to content

Posts from the ‘19c’ Category

Sep 23

Execute script in all PDBS and CDBS

Recompiling All Invalid Objects

Identify and recompile invalid objects on the CDB and PDBs using the catcon utility to run utlrp.sql after you install, patch, or upgrade a database.

Note:

If you upgraded using the AutoUpgrade utility, then AutoUpgrade automatically takes care of this task during the upgrade. You do not need to perform this task.

Oracle recommends that you use the catcon.pl utility to run utlrp.sql on all containers in your container database (CDB). The utlrp.sql script recompiles all invalid objects. Run the script immediately after installation, to ensure that users do not encounter invalid objects.

Change directory to Oracle_home/rdbms/admin.

For example

$ cd $ORACLE_HOME/rdbms/admin

Use the catcon.pl script in the Oracle home to run utlrp.sql.

For example:

$ORACLE_HOME/perl/bin/perl catcon.pl --n 1 --e --b utlrp --d '''.''' utlrp.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/opt/oracle/ora19/rdbms/admin/utlrp_catcon_3103472.lst]
catcon::set_log_file_base_path: catcon: See [/opt/oracle/ora19/rdbms/admin/utlrp.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/opt/oracle/ora19/rdbms/admin/utlrp_.lst] files for spool files, if any
catcon.pl: completed successfully

Note the following conditions of this use case:

--n parameter: is set to 1, so the script runs each PDB recompilation in sequence.

--e parameter: turns echo on.

--b parameter: Sets the log file base name. It is set to utlrp.

Expect a time delay for the serial recompilation of PDBs to complete. Depending on the number of PDBs that you are upgrading, the recompilation can extend significantly beyond the time required for the upgrade scripts to complete.

The utlrp.sql script automatically recompiles invalid objects in either serial or parallel recompilation, based on both the number of invalid objects, and on the number of CPUs available. CPUs are calculated using the number of CPUs (cpu_count) multiplied by the number of threads for each CPU (parallel_threads_per_cpu). On Oracle Real Application Clusters (Oracle RAC), this number is added across all Oracle RAC nodes.

Jun 8

Oracle XML Database Invalid After 19c

Problem Summary
—————————————————
Oracle XML Database invalid after 19c

Problem Description
—————————————————

COMP_NAME VERSION STATUS
———————————————— —————————— ———–
Oracle XML Database 19.0.0.0.0 INVALID
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
Oracle Database Packages and Types 19.0.0.0.0 UPGRADED

4 rows selected.

18:28:05 SQL> select owner, object_name, object_type, status
from dba_objects
where status = ‘INVALID’ and owner=’SYS’
order by owner, object_name;18:28:34 2 18:28:34 3 18:28:34 4

OWNER OBJECT_NAME OBJECT_TYPE STATUS
——————————————————————————————————————————– ——————————————————————————————————————————– ———————– ——-
SYS PREVENT_REPLTABS_CHANGE_V4 TRIGGER INVALID
SYS X_$KGLCURSOR VIEW INVALID

2 rows selected.

Elapsed: 00:00:00.02
18:28:35 SQL>

CHANGES

CAUSE

XDB related package bodies and other objects where invalid
 
XDB related package bodies and other objects where invalid

XDB                  DBMS_CLOBUTIL                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_CSX_ADMIN                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_CSX_INT2                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_JSON                      PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_JSON_INT                  PACKAGE              INVALID                                                                                                                        
XDB                  DBMS_JSON_INT                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_SODA_ADMIN                PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_SODA_DML                  PACKAGE              INVALID                                                                                                                        
XDB                  DBMS_SODA_DML                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_SODA_DOM                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_SODA_USER_ADMIN           PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_SODA_USER_DML             PACKAGE              INVALID                                                                                                                        
XDB                  DBMS_SODA_USER_DML             PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDB                       PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDBNFS                    PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDBREPOS                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDBRESOURCE               PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDBUTIL_INT               PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDBZ0                     PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDB_ADMIN                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDB_CONFIG                PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDB_CONSTANTS             PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDB_CONTENT               PACKAGE BODY         INVALID                                                                                                                                                                                                                                 
XDB                  DBMS_XDB_PRINT                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XDB_REPOS                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XEVENT                    PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XLSB                      PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLDOM                    PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLDOM_ICD                PACKAGE              INVALID                                                                                                                        
XDB                  DBMS_XMLINDEX                  PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLINDEX0                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLPARSER                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLSCHEMA_ANNOTATE        PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLSCHEMA_LSB             PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLSTORAGE_MANAGE         PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XMLTRANSLATIONS           PACKAGE BODY         INVALID                                                                                                                        
XDB                  DBMS_XSLPROCESSOR              PACKAGE BODY         INVALID                                                                                                                        
XDB                  DEPTH                          OPERATOR             INVALID                                                                                                                        
XDB                  GET_XDB_TABLESPACE             FUNCTION             INVALID                                                                                                                        
XDB                  PATH                           OPERATOR             INVALID                                                                                                                        
XDB                  PRVT_DBMS_MANAGE_XMLSTORAGE    PACKAGE BODY         INVALID                                                                                                                        
XDB                  UNDER_PATH_FUNC                FUNCTION             INVALID                                                                                                                        
XDB                  XDB_ANCOP                      PACKAGE BODY         INVALID                                                                                                                        
XDB                  XDB_DLTRIG_PKG                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  XDB_PV_TRIG                    TRIGGER              INVALID                                                                                                                        
XDB                  XDB_RVTRIG_PKG                 PACKAGE BODY         INVALID                                                                                                                        
XDB                  XDB_RV_TRIG                    TRIGGER              INVALID     
.

 

SOLUTION

COMPILE the invalid procedures and other objects manually

STEP _ 1 :-
——————

conn / as sysdba

ALTER PACKAGE XDB.DBMS_CLOBUTIL COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_CSX_ADMIN COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_CSX_INT2 COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_JSON COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_JSON_INT COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_SODA_ADMIN COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_SODA_DML COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_SODA_DOM COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_SODA_USER_ADMIN COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_SODA_USER_DML COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDBNFS COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDBREPOS COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDBRESOURCE COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDBUTIL_INT COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDBZ0 COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB_ADMIN COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB_CONFIG COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB_CONSTANTS COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB_CONTENT COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB_PRINT COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XDB_REPOS COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XEVENT COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XLSB COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLDOM COMPILE BODY ;
ALTER PACKAGE XDB.XDB_RVTRIG_PKG COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLINDEX COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLINDEX0 COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLPARSER COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLSCHEMA_ANNOTATE COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLSCHEMA_LSB COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLSTORAGE_MANAGE COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XMLTRANSLATIONS COMPILE BODY ;
ALTER PACKAGE XDB.DBMS_XSLPROCESSOR COMPILE BODY ;
ALTER PACKAGE XDB.XDB_ANCOP COMPILE BODY ;
ALTER PACKAGE XDB.XDB_DLTRIG_PKG COMPILE BODY ;
ALTER PACKAGE XDB.PRVT_DBMS_MANAGE_XMLSTORAGE COMPILE BODY ;
ALTER FUNCTION XDB.UNDER_PATH_FUNC COMPILE;
ALTER TRIGGER XDB .XDB_PV_TRIG COMPILE;
ALTER TRIGGER XDB .XDB_RV_TRIG COMPILE;
ALTER PACKAGE XDB .DBMS_JSON_INT COMPILE;
ALTER PACKAGE XDB .DBMS_XMLDOM_ICD COMPILE;
ALTER PACKAGE XDB .DBMS_SODA_USER_DML COMPILE;
ALTER PACKAGE XDB .DBMS_SODA_DML COMPILE;
ALTER OPERATOR XDB .DEPTH COMPILE;
ALTER FUNCTION XDB .GET_XDB_TABLESPACE COMPILE;
ALTER OPERATOR XDB .PATH COMPILE;

STEP _ 2 :-
——————

conn / as sysdba

EXECUTE DBMS_REGXDB.VALIDATEXDB;
SHOW ERR;

select schema,comp_name,comp_id,version,status,procedure from dba_registry WHERE COMP_ID=’XDB’;

SQL> select comp_name, version, status
 from dba_registry
 order by status, comp_name;
  2 3
COMP_NAME VERSION STATUS
————————————————————————————————————————————————————————————————————————————————————— —————————— ———–
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
Oracle XML Database 19.0.0.0.0 VALID >>>>>>>>>>>>>>>>>>

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

Database Instance 19c Crashes Intermittently

Oracle Database – Enterprise Edition – Version 19.3.0.0.0 to 19.6.0.0.0 [Release 19]
All Platforms

SYMPTOMS

+ This is an Oracle Database 19.3.0.0 on Microsoft Windows 2016 (64-bit). But this problem can occur on any platform for DB version from 19.3 to 19.6.

+ Intermittently, the instance crashes when it archives to the FRA which is configured on a SAN storage. But these are not necessary key factors either.

+ There is no good reason from the DB side for such a crash. There is no clue either from the DB logs why such crashes occurred….
2019-11-22T01:13:52.348399+00:00
ARC2 (PID:4572): Archived Log entry 1425 added for T-1.S-1438 ID 0x58ee09d8
LAD:1
2019-11-22T01:15:17.173560+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
..\trace\<sid>_vktm_796.trc
2019-11-22T01:25:33.718455+00:00
Starting ORACLE instance (normal) (OS id: 17956)
…

2019-11-25T12:00:51.401991+00:00
ARC3 (PID:18228): Archived Log entry 1704 added for T-1.S-1717 ID 0x58ee09d8
LAD:1
2019-11-25T12:16:59.974303+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
..\trace\<sid>_vktm_21108.trc
2019-11-25T12:20:47.426580+00:00
Starting ORACLE instance (normal) (OS id: 10268)
…

2019-12-10T06:45:34.380138+00:00
ARC3 (PID:24552): Archived Log entry 2918 added for T-1.S-2931 ID 0x58ee09d8
LAD:1
2019-12-10T06:52:06.575056+00:00
Resize operation completed for file# 3, old size 20869120K, new size
20879360K
2019-12-10T06:53:09.482879+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
..\trace\<sid>_vktm_24308.trc
2019-12-10T07:11:23.052682+00:00
Starting ORACLE instance (normal) (OS id: 19608)
…

2019-12-13T06:37:30.555846+00:00
ARC1 (PID:23204): Archived Log entry 3145 added for T-1.S-3158 ID 0x58ee09d8
LAD:1
2019-12-13T06:39:26.398601+00:00
<pdb_name>(3):TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION
SYS_P921 (43811) VALUES LESS THAN (TO_DATE(‘ 2019-12-14 00:00:00’,
‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
2019-12-13T06:48:17.595483+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
..\trace\<sid>_vktm_21892.trc
2019-12-13T06:56:24.219627+00:00
Starting ORACLE instance (normal) (OS id: 12156)
…

2019-12-17T11:12:04.311930+00:00
ARC1 (PID:22684): Archived Log entry 3416 added for T-1.S-3429 ID 0x58ee09d8
LAD:1
2019-12-17T11:16:07.008944+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
..\trace\<sid>_vktm_16824.trc
2019-12-17T11:18:25.927836+00:00
Starting ORACLE instance (normal) (OS id: 24632)
…

2019-12-19T16:49:17.207714+00:00
ALTER SYSTEM SET _disable_highres_ticks=FALSE SCOPE=SPFILE;
2019-12-19T16:49:17.238967+00:00
ALTER SYSTEM SET _timer_precision=500 SCOPE=SPFILE;
2019-12-19T16:49:20.817415+00:00
ALTER SYSTEM SET event=’10795 trace name context forever, level 2′
SCOPE=SPFILE;
…

2019-12-20T17:01:04.687429+00:00
NET (PID:25104): Archived Log entry 3627 added for T-1.S-3640 ID 0x58ee09d8
LAD:1
2019-12-20T17:33:01.355689+00:00
Resize operation completed for file# 3, old size 22558720K, new size
22568960K
2019-12-20T17:41:55.185792+00:00
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
..\trace\<sid>_vktm_12128.trc
2019-12-20T17:51:37.338467+00:00
Starting ORACLE instance (normal) (OS id: 9472)
…

_disable_highres_ticks = FALSE
_timer_precision = 500
event = “10795 trace name context forever, level 2”
…

2020-01-04T07:07:02.971105+00:00
ARC0 (PID:5404): Archived Log entry 4468 added for T-1.S-4481 ID 0x58ee09d8
LAD:1
2020-01-04T07:50:00.791072+00:00
Resize operation completed for file# 3, old size 24872960K, new size
24883200K
2020-01-04T08:00:13.906056+00:00
Thread 1 advanced to log sequence 4483 (LGWR switch)
Current log# 3 seq# 4483 mem# 0: ..\<SID>\REDO03A.LOG
Current log# 3 seq# 4483 mem# 1: ..\<SID>\REDO03B.LOG
2020-01-04T08:00:16.171956+00:00
ARC1 (PID:19276): Archived Log entry 4469 added for T-1.S-4482 ID 0x58ee09d8
LAD:1
2020-01-04T08:37:08.530619+00:00
Starting ORACLE instance (normal) (OS id: 19372)
…

2020-01-06T13:43:32.621640+00:00
ARC0 (PID:5500): Archived Log entry 4603 added for T-1.S-4616 ID 0x58ee09d8
LAD:1
2020-01-06T14:10:47.771835+00:00
Resize operation completed for file# 3, old size 25200640K, new size
25210880K
2020-01-06T14:50:42.867598+00:00
Starting ORACLE instance (normal) (OS id: 2988)
…

2020-01-06T17:09:17.660728+00:00
ARC1 (PID:22160): Archived Log entry 4606 added for T-1.S-4619 ID 0x58ee09d8
LAD:1
2020-01-06T17:10:07.791962+00:00
Starting ORACLE instance (normal) (OS id: 13320)
…

2020-01-10T17:01:01.363704+00:00
NET (PID:17724): Archived Log entry 4877 added for T-1.S-4890 ID 0x58ee09d8
LAD:1
2020-01-10T17:15:02.907458+00:00
Resize operation completed for file# 3, old size 25856000K, new size
25866240K
2020-01-10T18:25:26.947274+00:00
Starting ORACLE instance (normal) (OS id: 24016)
…

+ On Windows platforms, the ORACLE.exe keeps crashing with no errors in the alert log but in Windows event viewer you can always see the following exception:Exception code: 0xc0000409
Fault offset: 0x000000000fc7ff20

 + On Unix based platforms, the ORA-07445 [kwqbmspqueue] error is raised in the alert log file indicating such an overflow. Top call stack may include:.. <- kwqbmspqueue <- kwqbmspmain_switch <- kwqbmspmain <- kwqmnslv <- kwsbsmspm <- kwsbgcbkms <- ksvrdp_int <- ..

CHANGES

Upgrade to 19c

CAUSE

Bug 30777036 – 19c Database Instance Crashes Intermittently

Base Bug 29920804 Getting ora-7445 [kwqbmspqueue()+278] [sigsegv] every minute since upgraded from 18c to 19
 
This base bug affects DB version 19.3, 19.4, 19.5, 19.6 on any platform. So, it is not port specific. It can happen on any platform.

SOLUTION

Please apply the fix for Bug 29920804. This fix is included in 19.7.0.0.200414DBRU onward. For previous releases, please raise an One/Off Backport request.

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

RMAN Cross-Platform Transport of PDB into Destination CDB

Example

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Database names: SRC122, DEST122
Pluggable database name: PDB1
New container database name: DEST_DB

********



Using RMAN, Pluggable database (PDBs) can be transported and plugged in to a destination multitenant container database (CDB) which is on a different platform than the source CDB. In addition to backup of the PDB, RMAN also copies the metadata required to plug the PDB into the destination CDB. The source CDB and the destination CDB must use the same endian format.

Below steps show migration of closed PDB from source platform Solaris (Big Endian) to AIX (Big Endian)

SQL> select name,open_mode,platform_name from v$database;

Source:NAME      OPEN_MODE     PLATFORM_NAME
——— ————- ————————
SRC122    READ WRITE    Solaris[tm] OE (64-bit)

Destination:

NAME      OPEN_MODE    PLATFORM_NAME
——— ———— —————————
DEST122   READ WRITE   AIX-Based Systems (64-bit) 

SOLUTION

1. Close the source pdb:SQL> alter pluggable database PDB1 close immediate;

2. Backup the source PDB:RMAN> backup for transport
2> unplug into ‘/<path>/backup/PDB1_Metadata.xml’
3> format ‘/<path>/backup/PDB1_BKP_%U’
4> pluggable database PDB1;

3. SCP the backup-piece and PDB metadata files to destination

4. Check on destination whether the PDB can be plugged in using dbms_pdb.check_plug_compatibilityset serveroutput on
declare
c boolean;
begin
c:=dbms_pdb.check_plug_compatibility(‘/<path>/backup/PDB1_Metadata.xml’,’PDB1′);
if (c) then dbms_output.put_line(‘True’);
else dbms_output.put_line(‘False’);
end if;
end;
/

5. Restore the PDB on destination:RMAN> restore using ‘/<path>/backup/PDB1_Metadata.xml’
2> foreign pluggable database PDB1
3> format ‘/<path>/oradata/DEST_DB/%U’
4> from backupset ‘/<path>/backup/PDB1_BKP_02s9sj0u_1_1’;

6. Open the pluggable database PDB1 on destination:SQL> alter pluggable database PDB1 open;NOTE: This document covers PDB cross platform migration strategy using consistent backups i.e. the PDB is closed prior to backup. To reduce downtime, migration can also be achieved using inconsistent backups where, PDB level 0 backup is taken using clauses FOR TRANSPORT and ALLOW INCONSISTENT while the PDB is open in READ WRITE mode. Thereafter, PDB can be closed and a level 1 backup can be performed using FROM SCN clause and UNPLUG INTO (new in 12.2) clause to perform a final level 1 and also get the PDB metadata.

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=’*’;

Apr 3

OEM install warning SYSMAN schema and Public synonym

Problem:

OEM throws warning during install

oem

 

Solution:

connect with pdb with sys

Drop user sysman cascade;

spool drop.sql

SELECT ‘drop synonym ‘ || owner || ‘.’ || SYNONYM_NAME || ‘;’
FROM dba_synonyms
WHERE table_owner IN (‘SYSMAN’,
‘SYSMAN_MDS’,
‘MGMT_VIEW’,
‘SYSMAN_BIP’,
‘SYSMAN_APM’,
‘BIP’,
‘SYSMAN122130_OPSS’,
‘SYSMAN_RO’);

spool off;

@drop.sql

purge dba_recyclebin;

 

 

Mar 11

Add Single or Non RAC Database in SRVCTL or Cluster

srvctl add database -d singledbc -o /opt/oracle/ora12201 -dbtype single -node node1server.com
srvctl add service -d singledbc -pdb singledb -s singledbsvc
srvctl start service -d singledbc -s singledbsvc

srvctl stop database -d singledb -o immediate;
srvctl start database -d singledb

alter system set remote_listener=’scanlistener:1521′;

« Older Entries
%d bloggers like this: