RMAN List Backup Summary command

one of the useful RMAN commands is:

RMAN> list backup summary;

list_summary_columns.jpg

list_summary2

so what are these columns represent ?

* LV column lists the following:

0 = incremental level 0 backup

1 = incremental level 1 backup

A = archivelogs

f = full – backup database command

* S is the status:

A = available

U = unavailable

As mentioned above F can represent either “FULL” backup or “Control File” Backup. If your RMAN configuration for controlfile auto-backup is set to “ON” , the control file backup will be taken automatically each time RMAN backup runs as shown in the above picture.

The RMAN configuration is:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

 

 

 

 

 

 

 

 

 

 

ArcGIS 10.6.1 error after Oracle 18c upgrade

In ESRI ArcGIS Geodatabase environment its very important to maintain your product life-cycle support. ArcGIS 10.6.1 is compatible with Oracle 18c ….however when you upgrade both environments (ArcSDE & Oracle)  and attempt to connect through ArcGIS Desktop , the following error will be thrown:

 

DBMS error[Unkown errorNo extended error]

arcgis_error

This is a known bug by ESRI, the solution is to apply the following patch on your ArcGIS Desktop software:

https://support.esri.com/en/download/7675

 

 

Oracle oraversion

Since Oracle 18c after you successfully apply the patches on the database binaries you will notice that when you invoke sqlplus after that…the version is immediately changed/reflected:

In 19c, after patching the database binaries sqlplus is referencing 19.4 version number

Picture1

This is due to changes being reflected in a file called “oraversion” under $ORACLE_HOME/bin directory

Picture2

For example:

$ORACLE_HOME/bin/oraversion -baseVersion

Picture3

$ORACLE_HOME/bin/oraversion -compositeVersion

Picture4

 

 

 

 

 

 

 

 

 

 

 

 

 

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;