ORA-29548 release of java system classes in the database (12.2.0.2.160419.1.6 ) does not match that of oracle executable (12.1.0.2.170418 1.6)

~ easyoradba ~ Leave a comment

This error is mostly due to a mismatch in the Java system classes. It happened to us when we cloned a 12c Database which was on a lower home version to a 12c home on a higher version. It requires data patch to be run and rollback the old patch and apply the new JVM patch.

 

If it is a RAC database, put it in single instance mode by disabling cluster_database parameter and mount the database in upgrade exclusive mode.

 

SQL> show parameter cluster

 

NAME     TYPE VALUE
———————————— ———– ——————————
cluster_database     boolean TRUE
cluster_database_instances     integer 2
cluster_interconnects     string
SQL>

SQL>

SQL> alter system set cluster_database=false scope=spfile sid=’*’;

 

 

System altered.

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.

 

Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup upgrade exclusive;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
nessy1::12c–> cd /u03/app/oracle/product/12.1.0.2/dbhome_1/OPatch

nessy1::12c–> ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Sat Aug 12 14:47:54 2017Copyright (c) 2012, 2017, Oracle.  All rights reserved.
Log file for this invocation: /u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_125122_2017_08_12_14_47_54/sqlpatch_invocation.log
Connecting to database…OKBootstrapping registry and package to current versions…doneDetermining current state…done
Current state of SQL patches:Patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY):  Installed in the SQL registry onlyPatch 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)):  Installed in the SQL registry onlyPatch 25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017)):  Installed in the binary registry onlyBundle series DBBP:  ID 170418 in the binary registry and ID 160419 in the SQL registry
Adding patches to installation queue and performing prereq checks…Installation queue:  The following patches will be rolled back:    22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016))    21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)  The following patches will be applied:    25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017))    25397136 (DATABASE BUNDLE PATCH 12.1.0.2.170418)
Installing patches…Patch installation complete.  Total patches installed: 4
Validating logfiles…Patch 22674709 rollback: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/22674709/20057886/22674709_rollback_NESSY_2017Aug12_14_48_20.log (no errors)Patch 21171382 rollback: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/21171382/19741104/21171382_rollback_NESSY_2017Aug12_14_49_09.log (no errors)Patch 25437695 apply: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25437695/21057393/25437695_apply_NESSY_2017Aug12_14_49_17.log (no errors)Patch 25397136 apply: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25397136/21145057/25397136_apply_NESSY_2017Aug12_14_49_17.log (no errors)SQL Patching tool complete on Sat Aug 12 14:49:54 2017nessy1::12c–>nessy1::12c–>nessy1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 12 14:50:05 2017

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.
Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> set lines 9990
SQL>

SQL>

 

SQLselect * from dba_registry;

 

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
APEX       Oracle Application Express       4.2.5.00.08      VALID  02-JUL-2016 16:08:09 SERVER       SYS APEX_040200 VALIDATE_APEX       FLOWS_FILES
OLS       Oracle Label Security       12.1.0.2.0      VALID  02-JUL-2016 16:08:08 SERVER       SYS LBACSYS VALIDATE_OLS
DV       Oracle Database Vault       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS DVSYS VALIDATE_DV       DVF
ORDIM       Oracle Multimedia       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS ORDSYS VALIDATE_ORDIM       MDSYS,ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA
CONTEXT       Oracle Text       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS CTXSYS VALIDATE_CONTEXT
OWM       Oracle Workspace Manager       12.1.0.2.0      VALID  02-JUL-2016 16:08:01 SERVER       SYS WMSYS VALIDATE_OWM
SDO       Spatial       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS MDSYS VALIDATE_SDO       MDDATA,MDSYS,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR
XDB       Oracle XML Database       12.1.0.2.0      VALID  02-JUL-2016 16:07:57 SERVER       SYS XDB DBMS_REGXDB.VALIDATEXDB       ANONYMOUS,XS$NULL
CATALOG       Oracle Database Catalog Views       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC       Oracle Database Packages and Types       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC       APPQOSSYS,DBSNMP,DIP,GSMADMIN_INTERNAL,ORACLE_OCM,OUTLN,SYSTEM
JAVAVM       JServer JAVA Virtual Machine       12.1.0.2.0      VALID  12-AUG-2017 14:49:17 SERVER       SYS SYS INITJVMAUX.VALIDATE_JAVAVM

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
XML       Oracle XDK       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS XMLVALIDATE
CATJAVA       Oracle Database Java Packages       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
APS       OLAP Analytic Workspace       12.1.0.2.0      VALID  02-JUL-2016 16:08:06 SERVER       SYS SYS APS_VALIDATE
XOQ       Oracle OLAP API       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS SYS XOQ_VALIDATE
RAC       Oracle Real Application Clusters       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS SYS DBMS_CLUSTDB.VALIDATE

 

16 rows selected.

 

SQL>

SQL>

SQL>
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

ORA-39071: Value for EXCLUDE is badly formed.

~ easyoradba ~ Leave a comment

Oracle Database 101

Quick post more for myself as I seem to continually forget how to construct a proper EXCLUDE on the command line (no parameter file) on Linux with DataPump so here it is .

"exclude=SCHEMA:"IN('SCHEMA1','SCHEMA2')""

And yes the quotes before exclude and at the very end are required. This of course isn’t the only way but hopefully one I can remember.

View original post

Script to SET NEWNAME for DATAFILES in RMAN for Cloning

~ easyoradba ~ Leave a comment

— If doing on Source Server —

::12c–>sqlplus “/as sysdba”

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘set newname for datafile ‘ || file_id || ‘ to ”/u01/oradata/’ ||
substr(file_name,instr(file_name,’/’,-1)+1) || ”’;’
from dba_data_files ;
spool off
exit;

— If doing on Target Server —

[oracle@testorcl ~]$ sqlplus “/as sysdba”

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘set newname for datafile ‘ || file# || ‘ to ”/u01/oradata/’ ||
substr(name,instr(name,’/’,-1)+1) || ”’;’
from v$datafile ;
spool off
exit;

Rman backup from production and restore in test machine

~ easyoradba ~ Leave a comment

ORACLE RAC DBA

We want to make a test database from production database. And our production database is on ASM and our test machine was linux file sysetm.
see the steps how we restored it.

1) Start the test database in nomount mode

sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Oct 2 08:30:29 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 385876488 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14680064 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Restore controlfile from backup

[oracle@test rman]$ rmant target / Recovery Manager: Release 10.2.0.5.0 - Production on Tue Oct 2 08:31:07 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: prod (not…

View original post 1,039 more words

Shell Script to Ping Hostnames and Send SMS if Host is not Available

~ easyoradba ~ Leave a comment

We use the below procedure to ping hosts and based on availability send SMS to our team. The SMS is send by calling a web service from the BASH Shell script using curl 

The hosts are defined in a file called hosts.txt

— Shell Script —

#!/bin/bash
current_time=$(date “+%Y%m%d%H%M%S%3N”)
#### Above Date Format is in MilliSeconds Accuracy ###
cd /home/online
rm -rf /home/online/SMSHOST.TXT
date
#### Below Code Will Loop the Hosts.TXT file for the Ping Probes ####
cat /home/online/hosts.txt | while read output
do
ping -w 30 -c 1 $output > /dev/null
if [ $? -ne 0 ]
then
SMSTEXT=’The+Host+’$output’+is+not+reachable.+Please+Contact+the+Database+Team.’
echo “Host $output is Down”
curl “http://192.168.10.100:8080/SMSConnect/SendServlet?application=http_gw2&password=http_gw2&content=$SMSTEXT&destination=9998887190&source=97249&mask=COMPANY”
else
echo “Host $output is Up”
fi
done

 

— hosts.txt file containing list of hostnames —

proddb01

proddb02

proddb03

drdb01

drdb02

drdb03

Shell Script to Add Date to Filenames

~ easyoradba ~ Leave a comment

Below Shell Script will append Date to all FileNames in Directory with extension .TXT. Also it will create a folder with the Date and move the files to it.

#!/bin/bash
FILE=”$(date +%Y%m%d)”
cd /bi_dataload
mkdir -p /bi_dataload/BI_$FILE
for f in *.TXT
do
mv -n “$f” /bi_dataload/BI_$FILE/”${f%%.*}”_”$FILE”.TXT
done

Rebuild All Indexes on a Schema in Oracle

~ easyoradba ~ Leave a comment

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off