ORA-00904: “ACDRROWTSINTCOL#

in Oracle 12cR2 database the following errors were thrown after upgrade:

 

ORA-00604: error occurred at recursive SQL level 1

ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier

2019-09-05T06:20:26.344787+03:00

Error 704 happened during db open, shutting down database

2019-09-05T06:20:27.345361+03:00

Errors in file /orcl/oracc01/diag/rdbms/oracc01/oracc01/trace/oracc01_ora_116397.trc (incident=2040067):

ORA-00603: ORACLE server session terminated by fatal error

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier

Incident details in: /orcl/oracc01/diag/rdbms/oracc01/oracc01/incident/incdir_2040067/oracc01_ora_116397_i2040067.trc

2019-09-05T06:20:28.519786+03:00

Non critical error ORA-48913 caught while writing to trace file “/orcl/oracc01/diag/rdbms/oracc01/oracc01/incident/incdir_2040067/oracc01_ora_116397_i2040067.trc”

 

SOLUTION:

SQL> startup upgrade;

SQL> exit;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

SQL> @?/rdbms/admin/utlrp.sql

SQL> startup force;

SQL> select comp_name,version from dba_registry where status = ‘VALID’;

Oracle Database java component not valid !

I have faced strange problem when applying July 2019 CPU patch in Oracle 12cR2 (12.2.0.1) database !

So I wanted to share it in my blog in case somebody faced a similar issue.

Symptoms:

Java component is not valid

select * from dba_registry;

when you execute utlrp for recompilation

SQL> @?/rdbms/admin/utlrp.sql

The following error is displayed:

### validate_javavm caught -29548

In the alert log file…the following error is thrown:

joxcsys: release mismatch, 12.2.0.1.190416 1.8 in database (classes.bin) vs 12.2.0.1.190716 1.8 in executable pid 28179 cid 0

SQL> select dbms_java.longname(‘TEST’) from dual;

select dbms_java.longname(‘TEST’) from dual

*

ERROR at line 1:

ORA-29548: Java system class reported: joxcsys: release mismatch,

12.2.0.1.190416 1.8 in database (classes.bin) vs 12.2.0.1.190716 1.8 in Executable

Proposed solution:

the first way: shutdown the database and listener and perform relinking

make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk javavm_refresh ioracle

The second way:

Roll-back the OJVM patch and re-apply the patch again:

SQL> shutdown immediate;

lsnrctl stop listener_db11

$ORACLE_HOME/OPatch/opatch rollback -id 29774415

SQL> startup upgrade;

cd $ORACLE_HOME/OPatch

./datapatch –verbose

After roll-back is successful….apply the patch again and execute utlrp.sql to ensure JAVA component is now valid !

I hope this blog post helps !

synchronize & upgrade Oracle standby database after primary database upgrade

This guide will illustrate the required steps to Upgrade & Synchronize physical standby database after upgrading primary oracle database. Note that physical standby is not setup based on “data guard” technology.

In this simulation…i am simulating that primary database was upgraded from Oracle 12cR1 to 12cR2 ( this is still applicable with newer releases).

1. before proceeding with upgrading primary database, put the physical standby database in read-only mode

sqlplus / as sysdba

SQL> shutdown immediate;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> ALTER DATABASE OPEN READ ONLY;

SQL> select OPEN_MODE,DATABASE_ROLE from v$database;

OPEN_MODE DATABASE_ROLE

——————– —————-

READ ONLY PHYSICAL STANDBY

2. upgrade the primary database – please refer Oracle documentation for Oracle database upgrade documentation….also you can reference my own previous articles:

https://geodatamaster.com/2019/06/01/oracle-database-new-auto-upgrade-tool-simulation/

https://geodatamaster.com/2018/08/28/upgrading-oracle-database-from-12cr2-to-18c-in-windows/

https://geodatamaster.com/2017/04/02/upgrading-oracle-database-from-12cr1-to-12cr2-in-windows-environment/

3. After successfully upgrading the primary Oracle database,move the new and patched binries from the “primary” database server to the “standby” database server

mkdir /stdby/orcl/

cd /stdby/orcl/
tar -cf oraInventory.tar -P $ORACLE_BASE/product/oraInventory

tar -cf 12cR2.tar -P $ORACLE_BASE/product/12.2

4. go to the database standby server

**** shutdown standby database

sqlplus / as sysdba

SQL> shutdown immediate;

*** update your user profile to point to the new binaries
cd

vi .profile

%s/12.1/12.2/g
*** rename old database binaries

cd /app/orcl/product

mv 12.1 12.1.old

*** refresh your user profile exit the shell and enter again

echo $ORACLE_HOME
*** untar the new binaries

cd /app/orcl/product

tar -xf /stdby/orcl/oraInventory.tar -P
tar -xf /stdby/orcl/12cR2.tar -P
*** startup the standby database in standby mode:

SQL> STARTUP NOMOUNT;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> RECOVER AUTOMATIC STANDBY DATABASE;

SQL> select open_mode,database_role from v$database;
*** Remark: keep applying the logs and monitor standby database alert log file

What does datapatch do ?

Oracle releases patches every quarter (Oracle CPU Critical Patch Updates), these patches are either PSU (Patche-Set-Updates) for 12cR1,11gR4(11.2.0.4), or RU (Release Updates) RUR (Release Update Revisions) for 12cR2,18c,19c…etc.

 

We download the patch and we follow the procedure by first applying the patches on the “database binaries”, and then we perform/execute “datapatch” when the database is up……have you ever wondered what actually is happening when you execute datapatch ?

 

The following is my personal interpretation of the internals of datapatch execution:

The datapatch will cross reference and check wither you have “applied” or “rolled” back the patches on the binaries using dbms_qopatch package….based on that the datapach will either will apply SQL modifications or Roll-Back these modifications, then the package dbms_sqlpatch will be invoked to update dba_registry_sqlpatch with patch information:

CREATE OR REPLACE PACKAGE dbms_sqlpatch AS

— Performs any initialization necessary for the given patch, including

— the initial insert to the SQL registry.  Parameters:

— patch_id/patch_uid: patch ID/universal patch id

 

USERNAME                       OPERATION

—————————— ——————————–

TIMESTAMP           TABLE_NAME                         SESSION#    SERIAL#

——————- ——————————– ———- ———-

SQL_REDO

——————————————————————————–

— flags: patch flags

— description: patch description as recorded in binary registry

— action: APPLY or ROLLBACK

— logfile: Full path to logfile

— bndl_series: For a bundle patch, the bundle series.

— bnld_xml: For a bundle patch, the full path to the bundledata.xml

 

In 18c, a new SQL registry table registry$sqlpatch_ru_info, with associated view dba_registry_sqlpatch_ru_info:

 

SQL> select * from dba_registry_sqlpatch_ru_info;

PICCCC1

In addition, datapatch will change the SQL code of Oracle built in-packages, to illustrate the following SYS packages were changed in Oracle 12cR1 (12.1.0.2) after applying Oracle CPU July 2019 patches:

The following 4 packages under SYS where changed after the patch:

DBMS_BACKUP_RESTORE

DBMS_PREUP

DBMS_RCVMAN

DBMS_REGXDB

For DBMS_BACKUP_RESTORE package is changed after patching…. The following function is newly introduced:

 

FUNCTION KRBI_CREATE_XML RETURN VARCHAR2;

PRAGMA TIMESTAMP(‘2001-10-17:13:28:00’);

PRAGMA INTERFACE (C, KRBI_CREATE_XML);

FUNCTION CREATETEMPXMLFILE  RETURN VARCHAR2 IS

FNAME VARCHAR2(1024);

BEGIN

BEGIN

CHECK_VERSION(11,1,0, ‘dbms_backup_restore’);

ICDSTART(214);

GACTION := KRBI_READ_ACTION();

FNAME := KRBI_CREATE_XML;

GTRACEENABLED := KRBITRC();

ICDFINISH;

RETURN FNAME;

EXCEPTION

WHEN OTHERS THEN ICDFINISH;

RAISE;

END;

PRAGMA TIMESTAMP(‘2001-10-17:13:28:00’);

BEGIN

CHECK_VERSION(11,1,0, ‘dbms_backup_restore’);

GACTION := KRBI_READ_ACTION();

GTRACEENABLED := KRBITRC();

For DBMS_REGXDB package is changed after patching…. The following SQL statement is added:

EXECUTE IMMEDIATE ‘grant execute on XQSequenceFromXMLType to public’;

** Also, The code before patch:

DBMS_OUTPUT.PUT_LINE(‘Warning: XDB now invalid, could not find xdbconfig‘);

SYS.DBMS_REGISTRY.INVALID(‘XDB’);

** The code After patch:

DBMS_OUTPUT.PUT_LINE(‘Warning: XDB now invalid’);

SYS.DBMS_REGISTRY.INVALID(‘XDB’);

RAISE;

 

Upgrading TimeZone error ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading

when you upgrade your database, there are post-upgrade actions….among them is upgrading the “time zone“, the following errors were thrown when i successfully upgraded database from 12cR1 to 18c:

ERROR at line 1:

ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79

ORA-06512: at “SYS.DBMS_DST”, line 1390

ORA-06512: at line 1

Solution:

SQL> ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

SQL> exec dbms_dst.unload_secondary;

 

Oracle import error ORA-56935: existing datapump jobs are using a different version of time zone data file

while performing an import datapump (impdp) to an Oracle 18c database from 12cR2 database dump…..the following errors were thrown:

ORA-39006: internal error

ORA-39065: unexpected master process exception in DISPATCH

ORA-56935: existing datapump jobs are using a different version of time zone data file

ORA-06512: at “SYS.DBMS_DST”, line 1855

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79

ORA-06512: at “SYS.DBMS_DST”, line 1818

ORA-06512: at “SYS.KUPM$MCP”, line 19693

ORA-06512: at “SYS.KUPM$MCP”, line 12495

ORA-06512: at “SYS.KUPM$MCP”, line 11882

ORA-06512: at “SYS.KUPM$MCP”, line 14507

ORA-06512: at “SYS.KUPM$MCP”, line 9794

SOLUTION:

sqlplus / as sysdba

SQL> SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value

FROM   DATABASE_PROPERTIES

WHERE  PROPERTY_NAME LIKE ‘DST_%’

ORDER  BY PROPERTY_NAME;

 

SQL> ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

SQL> exec dbms_dst.unload_secondary;

**** Important Remark: make sure DST_UPGRADE_STATE is set to “NONE“….you may need to execute the following multiple times: SQL> exec dbms_dst.unload_secondary;

 

Patching Oracle 18c database in Windows

Download the database patch from Oracle support

Also, download the latest OPatch utility for Windows OS as shown below:

Picture1

Check your environment variables:

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

set ORACLE_HOME=D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home

set PERL5LIB=

Shutdown Oracle database services:

Picture2

Stop Distributed Transaction Coordinator

net stop msdtc

Go to the directory where the patch is downloaded

cd D:\ORACLE_WINDOWS_PATCHING\29589622

D:\OPATCH_DOWNLOAD\OPatch\opatch apply

When you try to apply the patch the following  error will occur if the OPatch utility is NOT placed within the Oracle database home binary

Picture3

Error in Text: The Oracle Home D:\OPATCH is not OUI based home. Please give proper Oracle Home

after placing the OPatch utility under Oracle Home, apply the patch:

D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch\opatch apply

Picture4

To resolve this error I will rollback this patch first:

D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch\opatch rollback -id 28267731

Picture5

And attempting to re-apply the patch will succeed

Picture6

Startup the Oracle services again:

Picture7

Post-Patch activity:

sqlplus / as sysdba

SQL> startup

SQL> alter pluggable database all open;

exit;

cd D:\Oracle18c_Windows\WINDOWS.X64_180000_db_home\OPatch

datapatch -verbose

sqlplus / as sysdba

SQL> @?/rdbms/admin/utlrp.sql

SQL> select * from dba_registry_sqlpatch;

Picture8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

RMAN recovery catalog registration errors RMAN-03009 ORA-01403

In a new Oracle 12cR2 database, i was attempting to register it with the Recovery Catalog:

RMAN> register database;

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of register command at 06/24/2019 06:56:42

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03009: failure of full resync command on default channel at 06/24/2019 06:56:42

ORA-01403: no data found

In order to solve this problem I needed to re-create the control file:

SQL> alter database backup controlfile to trace as ‘/orcl/backup/CTL_DEF.txt’;

SQL> exit;

 

// A text file with control file definition is created, I will only use the NORESETLOG section and create a sql based file as shown below (this is template):

vi create_controlfile.sql

CREATE CONTROLFILE REUSE DATABASE “DBT11” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 25

MAXLOGMEMBERS 5

MAXDATAFILES 850

MAXINSTANCES 1

MAXLOGHISTORY 18697

LOGFILE

GROUP 1 (

‘/opt/DBT11/t11LOGA1.log’,

‘/opt/DBT11/t11LOGA2.log’

) SIZE 256M BLOCKSIZE 512,

GROUP 2 (

‘/opt/DBT11/t11LOGB1.log’,

‘/opt/DBT11/t11LOGB2.log’

) SIZE 256M BLOCKSIZE 512,

GROUP 3 (

‘/opt/DBT11/t11LOGC1.log’,

‘/opt/DBT11/t11LOGC2.log’

) SIZE 256M BLOCKSIZE 512

— STANDBY LOGFILE

DATAFILE

‘/data1/DBT11/SYSTEM.ORA’,

‘/data2/DBT11/SYSAUX.ORA’,

‘/data2/DBT11/UNDO010.ORA’,

‘/data3/DBT11/USERD010.ORA’,

‘/data3/DBT11/AMS_TS.DBF’,

‘/data3/DBT11/SSDPROD_TS.DBF’,

‘/data3/DBT11/USERS02.DBF’

CHARACTER SET WE8MSWIN1252

;

 

SQL> STARTUP NOMOUNT

SQL> @create_controlfile.sql

SQL> alter database open;

 

// re-create the temp tablespace as follows:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/data1/DBT11/TEMP01.DBF’

SIZE 6557M REUSE AUTOEXTEND ON ;

 

Now try registering the database again:

rman target /

RMAN> connect catalog catalog01/XXXXXX@catalogdb

RMAN> register database;

 

 

 

 

 

 

 

 

 

 

 

 

Oracle Database Migration for ESRI ArcGIS Geo-Database

Oracle database platform is a popular database to host ESRI ArcGIS Geo-Database, so I am going here to list the best approach to migrate your database for ESRI ArcGIS SDE:

From the source database, use expdp to fully export the Oracle database using SDE account (you need to temporary grant “DBA” role for such action):

expdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR FULL=Y PARALLEL=4 compression=all logfile=FULL_EXP_LOG.log

 

Now, in the target database start importing ONLY SDE schema first (SDE schema holds GIS data metadata and management scripts so its very important that this schema gets imported successfully):

impdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR SCHEMAS=SDE PARALLEL=4 logfile=SDE_IMP.log

After the import, check the log file and make sure that all objects were imported successfully…..you can compare the number of SDE schema objects by executing the following query in both source & target database:

SQL> select count(*) from dba_objects where OWNER=’SDE’;

In my case some objects were not created successfully !

One failed object is indextype ST_SPATIAL_INDEX , to view the definition of the index type from the source database:

SQL> set long 100000

SQL> set head off

SQL> set echo off

SQL> set pagesize 0

SQL> set verify off

SQL> set feedback off

SQL> SELECT dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’,’SDE’) FROM dual;

 

I will execute the definition of the indextype:

 

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR

“SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER

),

“SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER)

,

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER,

VARCHAR2),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

 

“SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

“SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)

USING “SDE”.”ST_DOMAIN_METHODS”

WITH LOCAL RANGE PARTITION;

 

The following error will be thrown:

ORA-29829: implementation type does not exist

After digging….the following two “types” are missing:

ST_DOMAIN_METHODS

ST_DOMAIN_STATS

 

Take their SQL definition from the source database and create them in the destination, and try re-creating the indextype ST_SPATIAL_INDEX..it should be created successfully.

 

Now after ensuring SDE schema is fully mimicking the source database, start importing other schemas (user geodatabase schemas) and test it using ArcGIS Desktop.