March 2018 – Make DBA Life Easy

SYMPTOM

The following errors occurred from 11.2.0.4 standby databases:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select * from v$rman_backup_job_details;
select * from v$rman_backup_job_details
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "SYS"."DBMS_RCVMAN"."NUM2DISPLAYSIZE": invalid identifier

CAUSE

The problem is that DBMS_RCVMAN and other packages are not initialized when database in MOUNT mode, only in OPEN mode, hence the error when using SYS.DBMS_RCVMAN

dbms_rcvman package is not loaded in memory until database is open or RMAN client is ran explicitly.

SOLUTION

This error is expected because the standby instances are in MOUNT mode and the DBMS_RCVMAN does not get initialized.

Workaround: just make a call to rman before running the query in the Standby database. This will initialize the DBMS_RCVMAN package.

SQL> select * from v$rman_backup_job_details;
select * from v$rman_backup_job_details
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "SYS"."DBMS_RCVMAN"."NUM2DISPLAYSIZE": invalid identifier


SQL> exit

$ rman target /

RMAN> exit

$ sqlplus / as sysdba

SQL> select * from v$rman_backup_job_details;
...
..
.

1) Set up global variables :

export MOS_USER=testuser@domain.com
export MOS_PASSWORD="Password"

export PROXYUSER="testuser"
export PROXYPASSWD="Password123"
export USERAGENT="Mozilla/5.0"
export use_proxy=on
export http_proxy="http://proxy.domain.com:80/"
export https_proxy="https://proxy.domain.com:80/"
export COOK=$HOME/$.cookie

2) The following command to authenticate uses HTTP/HTTPS:

$wget --proxy-user=${PROXYUSER} --proxy-password=${PROXYPASSWD} 
--http-user=${MOS_USER} --http-password=${MOS_PASSWORD} 
--save-cookies=$COOK --keep-session-cookies --no-check-certificate 
"https://updates.oracle.com/Orion/Services/download" 
-no-verbose
2018-02-26 14:35:17 URL:https://updates.oracle.com/Orion/Services/download 
[118] -> "download.1" [1]

3) Get all supported platforms and language codes:

a) Output the query result into a temp file:

$wget --proxy-user=${PROXYUSER} --proxy-password=${PROXYPASSWD} 
--no-check-certificate --load-cookies=$COOK 
"https://updates.oracle.com/Orion/SavedSearches/switch_to_simple" 
-O $HOME/output.tmp -q

$ ls -l $HOME/output.tmp
-rw-r----- 1 testuser users 4528477 Feb 26 14:41 /home/testuser/output.tmp

b) Extract the Platform and Language Code, here we are only interested in platform “226P<—>Linux x86-64″ with default English language:

$ grep -A200 "<select name=plat_lang"  /home/testuser/output.tmp |
 grep "^<option"|awk -F "[\">]" '{print $2 "<--->" $4}' |
 |grep -v "<--->selected"

537P<--->Acme Packet 1100
529P<--->Acme Packet 3820
...
..
.
541P<--->Linux ARM 64-bit
214P<--->Linux Itanium
525P<--->Linux SPARC
46P<--->Linux x86
226P<--->Linux x86-64
912P<--->Microsoft Windows (32-bit)
208P<--->Microsoft Windows Itanium (64-bit)
539P<--->Microsoft Windows Phone
233P<--->Microsoft Windows x64 (64-bit)
...
..
.
117L<--->Traditional Chinese (ZHT)
116L<--->Turkish (TR)
37L<--->UK English (GB)
39L<--->Ukrainian (UK)
43L<--->Vietnamese (VN)
999L<--->Worldwide Spanish (ESW)

4) Get URLs of patch 6880880 for Linux X86-64 platform.

$wget --proxy-user=${PROXYUSER} --proxy-password=${PROXYPASSWD} 
--no-check-certificate --load-cookies=$COOK 
"https://updates.oracle.com/Orion/SimpleSearch/process_form?search_type
=patch&patch_number=6880880&plat_lang=226P" -O $HOME/output1.tmp -q

$ ls -ltr $HOME/output1.tmp
-rw-r----- 1 tetsuser users 4544310 Feb 26 15:44 /home/testuser/output1.tmp

$ grep "Download/process_form" output1.tmp | sed 's/ //g' | sed "s/.*href=\"//g" | sed "s/\".*//g"
https://updates.oracle.com/Orion/Download/process_form/p6880880_139000_Generic.zip?aru=21939900&file_id=98828928&patch_file=p6880880_139000_Generic.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_112000_Linux-x86-64.zip?aru=21895918&file_id=64217272&patch_file=p6880880_112000_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_121010_Linux-x86-64.zip?aru=21886824&file_id=65461237&patch_file=p6880880_121010_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_122010_Linux-x86-64.zip?aru=21885985&file_id=96948775&patch_file=p6880880_122010_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_132000_Generic.zip?aru=17856597&file_id=72275045&patch_file=p6880880_132000_Generic.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_111000_Linux-x86-64.zip?aru=19416466&file_id=26541776&patch_file=p6880880_111000_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_131000_Generic.zip?aru=16531511&file_id=62900088&patch_file=p6880880_131000_Generic.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_101000_Linux-x86-64.zip?aru=13915384&file_id=42098007&patch_file=p6880880_101000_Linux-x86-64.zip&
https://updates.oracle.com/Orion/Download/process_form/p6880880_102000_Linux-x86-64.zip?aru=13116068&file_id=34545782&patch_file=p6880880_102000_Linux-x86-64.zip&

5) Download the patch by using URL from step 4):

curl:

$ curl -b $COOK -c $COOK --insecure --output p6880880_122010_Linux-x86-64.zip 
-L "https://updates.oracle.com/Orion/Download/process_form/p6880880_122010_Linux-x86-64.zip?aru=21885985&file_id=96948775&patch_file=p6880880_122010_Linux-x86-64.zip&"

  % Total % Received % Xferd Average Speed Time Time Time Current
 Dload Upload Total Spent Left Speed
100 90.8M 100 90.8M 0 0 5008k 0 0:00:18 0:00:18 --:--:-- 20.7M

wget:

$ wget --load-cookies=$COOK --save-cookies=$COOK --keep-session-cookies 
--no-check-certificate -O p6880880_122010_Linux-x86-64.zip 
"https://updates.oracle.com/Orion/Download/process_form/p6880880_122010_Linux-x86-64.zip?aru=21885985&file_id=96948775&patch_file=p6880880_122010_Linux-x86-64.zip&"
...
..
.
Proxy request sent, awaiting response... 200 OK
Length: 95262503 (91M) [application/zip]
Saving to: `p6880880_122010_Linux-x86-64.zip'

100%[=================================================================================================>] 95,262,503 21.9M/s in 16s

2018-02-26 17:57:20 (5.65 MB/s) - `p6880880_122010_Linux-x86-64.zip' saved [95262503/95262503]

6) Validate the download zip file:

OPatch patch of version 12.2.0.1.12 for Oracle software releases 12.1.0.x 
(installer) and 12.2.0.x (JAN 2018) (Patch)

p6880880_122010_Linux-x86-64.zip

90.8 MB

(95262503 bytes)

MD5	08D733176A76D99547CDC5ABF7DEF192

SHA-1

4B4EE360C1AF6515CC18F9C36B3AD06EF64B5E0D

SHA-256

5BD98A31C8E134DFF1DE833FFA0834D62C606036A1626AF6ED529854D215707F

a) “unzip -t”

$ unzip -t p6880880_122010_Linux-x86-64.zip
Archive: p6880880_122010_Linux-x86-64.zip
 testing: OPatch/ OK
 testing: OPatch/operr.bat OK
 testing: OPatch/opatch_env.sh OK
...
..
.
No errors detected in compressed data of p6880880_122010_Linux-x86-64.zip.

b) MD5 “md5sum”

$ md5sum p6880880_122010_Linux-x86-64.zip
08d733176a76d99547cdc5abf7def192 p6880880_122010_Linux-x86-64.zip

c) SHA-1 “sha1sum”

$ sha1sum p6880880_122010_Linux-x86-64.zip
4b4ee360c1af6515cc18f9c36b3ad06ef64b5e0d p6880880_122010_Linux-x86-64.zip

d)SHA-256 “sha256sum”

$ sha256sum p6880880_122010_Linux-x86-64.zip
5bd98a31c8e134dff1de833ffa0834d62c606036a1626af6ed529854d215707f p6880880_122010_Linux-x86-64.zip

Month: March 2018

ORA-00904: “SYS”.”DBMS_RCVMAN”.”NUM2DISPLAYSIZE”: invalid identifier

SYMPTOM

CAUSE

SOLUTION

CRS-4535: Cannot communicate with Cluster Ready Services

Subscribe to get access

How to Download Oracle Patches From Oracle Support By Using WGET or CURL