Posted in Database, ORACLE

datapatch fails with ORA-04063 : package body “SYS.DBMS_SQLPATCH” has errors

Recently when i was applying Critical patch Update for October 2017 on my Database Server.

Patch applied process with OPatch utility completed successfully  but there was issue with datapatch utility :

TESTING:oracle $ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Thu Nov 16 04:33:57 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.

Log file for this invocation: /ora00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27492_2017_11_16_04_33_57/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done

DBD::Oracle::db selectrow_array failed: ORA-04063: package body "SYS.DBMS_SQLPATCH" has errors (DBD ERROR: OCIStmtExecute) [for Statement "SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual"] at /ora00/app/oracle/product/12.2.0/sqlpatch/sqlpatch.pm line 4524, <LOGFILE> line 21.




Please refer to MOS Note 1609718.1 and/or the invocation log
/ora00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_27492_2017_11_16_04_33_57/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Thu Nov 16 04:33:57 2017

 

There was lot of issue mentioned on MOS Note 1609718.1 but above mentioned package issue was not present.

Latter i tried below mentioned steps to recreate those package body :

1)Finding current status of package :

TESTING:oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 05:04:24 2017

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




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual;
SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual
 *
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_SQLPATCH" has errors

 

2) As we can see in above mentioned command package body was displaying error so i tried to look-out for script which create that package by running below mentioned command inside $ORACLE_HOME/rdbms/admin directory.

TESTING:oracle $ find . | xargs grep "dbms_sqlpatch"
grep: .: Is a directory
./e1201000.sql:Rem surman 08/05/13 - 17005047: Add dbms_sqlpatch
./catpdbms.sql:Rem surman 08/02/13 - 17005047: Add dbms_sqlpatch
./catdwgrd.sql:Rem dbms_sqlpatch package, as the package may not be valid
./dbmssqlpatch.sql:Rem surman 08/18/14 - Always reload dbms_sqlpatch
./dbmssqlpatch.sql:CREATE OR REPLACE PACKAGE dbms_sqlpatch AS
./dbmssqlpatch.sql:END dbms_sqlpatch;
./dbmssqlpatch.sql:CREATE OR REPLACE PUBLIC SYNONYM dbms_sqlpatch FOR sys.dbms_sqlpatch;
./dbmssqlpatch.sql:GRANT EXECUTE ON dbms_sqlpatch TO execute_catalog_role;
grep: ./cdb_cloud: Is a directory
grep: ./cdb_cloud/sql: Is a directory
grep: ./cdb_cloud/dbt: Is a directory
grep: ./cdb_cloud/dbt/test: Is a directory
grep: ./cdb_cloud/rsp: Is a directory
grep: ./cdb_cloud/apex_install: Is a directory
grep: ./cdb_cloud/apex_install/ords: Is a directory
./prvtsqlpatch.plb: EXECUTE IMMEDIATE 'DROP TABLE dbms_sqlpatch_state';
./prvtsqlpatch.plb:CREATE TABLE dbms_sqlpatch_state (
./prvtsqlpatch.plb:CREATE OR REPLACE PACKAGE BODY dbms_sqlpatch wrapped
./catpprvt.sql:Rem surman 08/03/13 - 17005047: Add dbms_sqlpatch
./catpprvt.sql:-- 20772435: Queryable dbms_sqlpatch package body is now created in catxrd
./catxrd.sql:Rem dbms_sqlpatch package
./xrde121.sql:DROP PACKAGE dbms_sqlpatch;
./xrde121.sql:DROP PUBLIC SYNONYM dbms_sqlpatch;

From above mentioned output  we can see that prvtsqlpatch.plb script is creating this package body.So ran this script to recreate dbms_sqlpatch package.

3)Running  prvtsqlpatch.plb to recreate dbms_sqlpatch package and checking status of this package;

TESTING:oracle $ export ORACLE_SID=confdb

TESTING:oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 16 05:04:24 2017

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




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @prvtsqlpatch.plb

Session altered.




PL/SQL procedure successfully completed.




Table created.




Package body created.

No errors.

Session altered.

SQL> SELECT dbms_sqlpatch.verify_queryable_inventory FROM dual;

VERIFY_QUERYABLE_INVENTORY
--------------------------------------------------------------------------------

OK

 

4)Once above mentioned package is recreated we can apply patch on database with the help of datapatch utility:

TESTING:oracle $ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Thu Nov 16 05:05:10 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.

Log file for this invocation: /ora00/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_5382_2017_11_16_05_05_10/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
 ID 171017 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
 Nothing to roll back
 The following patches will be applied:
 26710464 (DATABASE RELEASE UPDATE 12.2.0.1.171017)

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 26710464 apply: SUCCESS
 logfile: /ora00/app/oracle/cfgtoollogs/sqlpatch/26710464/21632407/26710464_apply_CONFDB_2017Nov16_05_05_18.log (no errors)
SQL Patching tool complete on Thu Nov 16 05:06:11 2017

 

 

 

 

 

Advertisements
Posted in Database, ORACLE

OPatch failed with error code 2

Before applying Critical Patch Update for October 2017, I wanted to confirm Current patch details which are applied but ran into below mentioned issues.

TESTING:oracle $ /ora00/app/oracle/product/12.2.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /ora00/app/oracle/product/12.2.0
Central Inventory : /ora00/app/oraInventory
 from : /ora00/app/oracle/product/12.2.0/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /ora00/app/oracle/product/12.2.0/cfgtoollogs/opatch/opatch2017-11-16_03-29-39AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"
List of Homes on this system:

Prereq "checkConflictAgainstOHWithDetail" is not executed.

The details are:
Exception occured : RawInventory gets null OracleHomeInfo
Summary of Conflict Analysis:

There are no patches that can be applied now.

OPatch failed with error code 2

 

The above error occurs when Opatch was not able to find the database HOME in inventory xml file present inside Oracle Inventory Directory.

Solution is to have the Oracle Home attach to the Central Inventory of the server

We can re-attach the oracle home by using script present at $ORACLE_HOME/oui/bin “attachHome.sh” location.Do crosscheck value of Oracle_Home present inside attachHome.sh script.

TESTING:oracle $ ./attachHome.sh
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 1921 MB Passed
The inventory pointer is located at /etc/oraInst.loc
Please execute the '/ora00/app/oraInventory/orainstRoot.sh' script at the end of the session.
'AttachHome' was successful.

 

Once that is done we can run above mentioned command without any errors:

TESTING:oracle $ /ora00/app/oracle/product/12.2.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2017, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /ora00/app/oracle/product/12.2.0
Central Inventory : /ora00/app/oraInventory
 from : /ora00/app/oracle/product/12.2.0/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /ora00/app/oracle/product/12.2.0/cfgtoollogs/opatch/opatch2017-11-16_03-38-25AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

 

Posted in Database, ORACLE

ORA-39181: Only partial table data may be exported due to fine grain access control

Data pump expdp log shows following error for some tables during the export:

EXP ORA-39181: Only partial table data may be exported due to fine grain access control on "ADMIN"."XCF"
EXP . . exported "ADMIN"."XCF" 0 KB 0 rows
EXP ORA-39181: Only partial table data may be exported due to fine grain access control on "ADMIN"."XDF"
EXP . . exported "ADMIN"."XDF" 0 KB 0 rows

Also VPD is not enabled to show this error.

Solution:

Provide the below privilege to the schema which you are trying to export:

SQL> GRANT EXEMPT ACCESS POLICY to <SCHEMA_NAME>;
Posted in LINUX, OS

Setting udev rules for Oracle ASM on Linux

1) Use lsblk command to know current disk allocated.

2) Use below mentioned command to know disk id.

/sbin/scsi_id -g -u -d /dev/sdr

3)Once you know all the id of associated disk to be used for ASM data storage.

You can put below mentioned line in vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="sd?", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$name", RESULT=="36000c2966eff055daa2454a0d522523e", NAME="arcRedo", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd?", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$name", RESULT=="36000c29e693d7a0dfbd895da40a985f1", NAME="at01", OWNER="grid", GROUP="asmadmin", MODE="0660"

4)Once you have set the rule you can now reload udev rules with below mentioned command.After rules are implemented we can test the desired disk.

/sbin/udevadm control --reload-rules

/sbin/start_udev

udevadm test /dev/arcRedo

5)Verify by below mentioned command.

[root@TESTING~]# ls -l /dev/arcRedo
brw-rw---- 1 grid asmadmin 8, 32 Aug 19 05:43 /dev/arcRedo
Posted in Database, ORACLE

Applying Oracle Critical Patch Update of April 2017 on Oracle 12.1.0.2.0

 

1) Finding Current Opatch Version.

[grid@TESTING ora00]$ /ora00/app/grid/product/12.1.0/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.

2) Download Updated Opatch patch from Oracle Metalink.

TESTING:oracle $ cd patchopatch/
TESTING:oracle $ ls
p21142429_121010_Linux-x86-64.zip

3) Applying Opatch patch on current Oracle Home and Grid Home.

Oracle Home:-

TESTING:oracle $ unzip -o -qq p21142429_121010_Linux-x86-64.zip -d /ora00/app/oracle/product/12.1.0
TESTING:oracle $ /ora00/app/oracle/product/12.1.0/OPatch/opatch version
OPatch Version: 12.1.0.1.10

OPatch succeeded.

Grid Home:–

[grid@TESTING patchopatch]$ unzip -o -qq p21142429_121010_Linux-x86-64.zip -d /ora00/app/grid/product/12.1.0
[grid@TESTING patchopatch]$ /ora00/app/grid/product/12.1.0/OPatch/opatch version
OPatch Version: 12.1.0.1.10

OPatch succeeded.

4) Stop Grid and Database Services.

5) Analyze Current Patch with opatchauto utility.

[root@TESTING ora00]# /ora00/app/grid/product/12.1.0/OPatch/opatchauto apply /ora00/25434003 -analyze -oh /ora00/app/grid/product/12.1.0
OPatch Automation Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 12.1.0.1.10
OUI Version : 12.1.0.2.0
Running from : /ora00/app/grid/product/12.1.0

opatchauto log file: /ora00/app/grid/product/12.1.0/cfgtoollogs/opatchauto/25434003/opatch_gi_2017-06-22_05-45-15_analyze.log

NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system.

Parameter Validation: Successful

Configuration Validation: Successful

Patch Location: /ora00/25434003
Grid Infrastructure Patch(es): 21436941 25171037 25363740 25363750
DB Patch(es): 25171037 25363740

Patch Validation: Successful
User specified following Grid Infrastructure home:
/ora00/app/grid/product/12.1.0




Analyzing patch(es) on "/ora00/app/grid/product/12.1.0" ...
Patch "/ora00/25434003/21436941" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.
Patch "/ora00/25434003/25171037" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.
Patch "/ora00/25434003/25363740" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.
Patch "/ora00/25434003/25363750" successfully analyzed on "/ora00/app/grid/product/12.1.0" for apply.

Apply Summary:
Following patch(es) are successfully analyzed:
GI Home: /ora00/app/grid/product/12.1.0: 21436941,25171037,25363740,25363750

opatchauto succeeded.

6) Once above steps is completed successfully we will generate ocm.rsp file , If you have latter Opatch Version this rsp file is not needed

export ORACLE_HOME=/ora00/app/grid/product/12.1.0
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /ora00/ocm.rsp

Applying Opatch on Grid Home

[root@TESTING bin]# /ora00/app/grid/product/12.1.0/OPatch/opatchauto apply /ora00/25434003 -oh /ora00/app/grid/product/12.1.0 -ocmrf /ora00/app/oracle/ocm.rsp
OPatch Automation Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 12.1.0.1.10
OUI Version : 12.1.0.2.0
Running from : /ora00/app/grid/product/12.1.0

opatchauto log file: /ora00/app/grid/product/12.1.0/cfgtoollogs/opatchauto/25434003/opatch_gi_2017-06-22_06-07-58_deploy.log

Parameter Validation: Successful

Configuration Validation: Successful

Patch Location: /ora00/25434003
Grid Infrastructure Patch(es): 21436941 25171037 25363740 25363750
DB Patch(es): 25171037 25363740

Patch Validation: Successful
User specified following Grid Infrastructure home:
/ora00/app/grid/product/12.1.0




Performing prepatch operations on SIHA Home... Successful

Applying patch(es) to "/ora00/app/grid/product/12.1.0" ...
Patch "/ora00/25434003/21436941" successfully applied to "/ora00/app/grid/product/12.1.0".
Patch "/ora00/25434003/25171037" successfully applied to "/ora00/app/grid/product/12.1.0".
Patch "/ora00/25434003/25363740" successfully applied to "/ora00/app/grid/product/12.1.0".
Patch "/ora00/25434003/25363750" successfully applied to "/ora00/app/grid/product/12.1.0".

Performing postpatch operations on SIHA Home... Successful

Apply Summary:
Following patch(es) are successfully installed:
GI Home: /ora00/app/grid/product/12.1.0: 21436941,25171037,25363740,25363750

opatchauto succeeded.

7) Repeat the same steps for Oracle Home:

[root@TESTING ~]# /ora00/app/oracle/product/12.1.0/OPatch/opatchauto apply /ora00/25434003 -oh /ora00/app/oracle/product/12.1.0/ -ocmrf /ora00/app/oracle/ocm.rsp

 

Posted in OS, Solaris

Applying Critical Patch Update of April 2017 on Solaris 11.3.

Applying Oracle Solaris 11.3.20.5.0  on Solaris 11.3.

For downloading the Critical Patch Update Use below mentioned link:-

Critical Patch Update

Once necessary Zip file is downloaded copy it to primary Server and install repository:

Patch apply

On primary Server :

root@SEP02WTR-3612:/softrepo/repo113full# ./install-repo.ksh -d /softrepo/repo113full -c -v -I
Using p25977008_1100_SOLARIS64 files for sol-11_3_20_5_0-incr-repo download.

Comparing digests of downloaded files...done. Digests match.

Uncompressing p25977008_1100_SOLARIS64_1of4.zip...done.
Uncompressing p25977008_1100_SOLARIS64_2of4.zip...done.
Uncompressing p25977008_1100_SOLARIS64_3of4.zip...done.
Uncompressing p25977008_1100_SOLARIS64_4of4.zip...done.
Repository can be found in /softrepo/repo113full.
Initiating repository verification.
Building ISO image...done.
ISO image can be found at:
/softrepo/repo113full/sol-11_3_20_5_0-incr-repo.iso
Instructions for using the ISO image can be found at:
/softrepo/repo113full/README-repo-iso.txt

 

Share the full directory of the repository:

share -F nfs /softrepo/repo113full/

 

ON THE TARGET LDOM APPLY DESIRED CRITICAL PATCH UPDATE:
Solaris-2:root@TESTING:~# pkg unset-publisher solaris
Updating package cache 1/1
solaris-2:root@TESTING:~# mount -F nfs 153.71.78.20:/softrepo/repo113full/ /mnt_11_3
solaris-2:root@TESTING:~# mount -F hsfs /mnt_11_3/sol-11_3_20_5_0-incr-repo.iso /mnt
solaris-2:root@TESTING:~# pkg set-publisher -g file:///mnt/repo solaris
solaris-2:root@TESTING:~# pkg update -nv
 Packages to update: 2
 Estimated space available: 9.03 GB
Estimated space to be consumed: 63.22 MB
 Create boot environment: No
Create backup boot environment: Yes
 Rebuild boot archive: No

Changed packages:
solaris
 consolidation/ddt/ddt-incorporation
 8.9.15.9.11,5.11:20150916T171410Z -> 8.15.17.3.10,0.5.11-0.175.3.19.0.2.0:20170328T025129Z
 support/explorer
 8.9.15.9.11,5.11:20150916T171411Z -> 8.15.17.3.10,0.5.11-0.175.3.19.0.2.0:20170328T025130Z

solaris-2:root@TESTING:~# pkg update
 Packages to update: 2
 Create boot environment: No
Create backup boot environment: Yes

DOWNLOAD PKGS FILES XFER (MB) SPEED
Completed 2/2 1365/1365 8.3/8.3 0B/s




PHASE ITEMS
Removing old actions 11/11
Installing new actions 26/26
Updating modified actions 1346/1346
Updating package state database Done
Updating package cache 2/2
Updating image state Done
Creating fast lookup database Done
Updating package cache 1/1

---------------------------------------------------------------------------

 

 

 

 

 

Posted in Database, ORACLE

addnode.sh hangs during copying files to other node.

 

Recently i was trying to add one node to cluster and after running addnode.sh it was hanging in below mentioned line

 

$ ./addnode.sh -silent "CLUSTER_NEW_NODES={SEP02PVVM335}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={SEP02PVVM335-VIP}"
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 40619 MB Passed
Checking swap space: must be greater than 150 MB. Actual 49143 MB Passed
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
 CAUSE: Some of the optional prerequisites are not met. See logs for details. /ora00/app/oraInventory/logs/addNodeActions2017-06-02_06-50-36AM.log
 ACTION: Identify the list of failed prerequisite checks from the log: /ora00/app/oraInventory/logs/addNodeActions2017-06-02_06-50-36AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.

Prepare Configuration in progress.

Prepare Configuration successful.
.................................................. 8% Done.
You can find the log of this install session at:
 /ora00/app/oraInventory/logs/addNodeActions2017-06-02_06-50-36AM.log

Instantiate files in progress.

Instantiate files successful.
.................................................. 14% Done.

Copying files to node in progress.

 

First of all GRID_HOME should be owned by grid by default it will be root.

After searching on metalink find out sometime this issue may be because of below mentioned BUG:

Bug 12318325 – Addnode.sh takes longer due to audit files in GRID_HOME/rdbms/audit. (Doc ID 12318325.8)

 

Once i cleaned up those audit files i was able to run addnode.sh successfully.

 

$ ./addnode.sh -silent "CLUSTER_NEW_NODES={SEP02PVVM335}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={SEP02PVVM335-VIP}"
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 40543 MB Passed
Checking swap space: must be greater than 150 MB. Actual 49143 MB Passed

Prepare Configuration in progress.

Prepare Configuration successful.
.................................................. 8% Done.
You can find the log of this install session at:
/ora00/app/oraInventory/logs/addNodeActions2017-06-02_09-56-37AM.log

Instantiate files in progress.

Instantiate files successful.
.................................................. 14% Done.

Copying files to node in progress.

Copying files to node successful.
.................................................. 73% Done.

Saving cluster inventory in progress.
.................................................. 80% Done.

Saving cluster inventory successful.
The Cluster Node Addition of /ora00/grid/product/12.1.0 was successful.
Please check '/tmp/silentInstall.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
.................................................. 88% Done.

As a root user, execute the following script(s):
 1. /ora00/app/oraInventory/orainstRoot.sh
 2. /ora00/grid/product/12.1.0/root.sh

Execute /ora00/app/oraInventory/orainstRoot.sh on the following nodes:
[SEP02PVVM335]
Execute /ora00/grid/product/12.1.0/root.sh on the following nodes:
[SEP02PVVM335]

The scripts can be executed in parallel on all the nodes.

..........
Update Inventory in progress.
.................................................. 100% Done.

Update Inventory successful.
Successfully Setup Software.
$