Pages

Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Monday, January 23, 2017

OEM 13c: The Next-Gen OPATCH version 13.9 and issues on Solaris 10 - "opatch: test: unknown operator =="

To those running OEM 13c - you may have noticed the bug "ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'" causing the SQL Monitoring page to fail.

Rejoice - the fix is now available "Oracle® Management Service 13c Release 2 (13.2.0.0.0) System Patch Patch for Bug # 25197714 - Enterprise Manager for OMS Plug-ins 13.2.0.0.0"

Readme --> HERE

This post is to do mostly with "Pre-requisite #5":
-----
Ensure that you have the latest version of OPatch 13.9.0.0.0 and OMSPatcher 13.8.0.0.1 on all OMS instance platform homes.
If you are not sure about the OPatch version you have, or to download the latest version, follow the instructions outlined in the My Oracle Support note 224346.1 available at:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=224346.1

If you are not sure about the OMSPatcher version you have, or to download the latest version, follow the instructions outlined in the My Oracle Support note 2203007.1 available at:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2203007.1
----

OMSPatcher upgrade is simple - download --> unzip --> replace folder [the usual (but now historical) procedure used to upgrade OPatch].

Hang on, what do you mean by "historical" procedure? What's new with OPatch upgrade? 

OPatch now comes bundled in a 'jar' file that needs to be installed using at least JRE 1.7. Luckily, OMS 13c installation comes built in with this version of JAVA.

Command is as below:
[oracle->ogcoms]/u01/software/6880880]$OMS_HOME/oracle_common/jdk/bin/java -jar -d64 opatch_generic.jar -silent oracle_home=$OMS_HOME

(-d64 parameter is required if you are installing OPatch in a 64 bit setup)

Should work like a charm for any other OS, except Solaris (as of the version 13.9).

Issue:
Upgrade was successful without any errors. But, "opatch" commands were failing:

[oracle->ogcoms]/u01/app/oracle/middleware13c/OPatch]./opatch 
./opatch: test: unknown operator == 
[oracle->ogcoms]/u01/app/oracle/middleware13c/OPatch]./opatch version 
./opatch: test: unknown operator == 

This is a bug! (yes, yet another bug!)

Bug 16393828 [ - OPATCH.SH FAILS ON SOLARIS
Bug 16393904 [ - OPATCH-NEXTGEN: OPATCH NOT WORKING IN SOLARIS SPARC 10

opatch is an ascii file, so looking for the text "==" in the file gives you the faulty line:

[oracle->ogcoms]/home/oracle/OPatch]grep == opatch
#    ==> yes: ready to 'callOPatch'
#    ==> no: go to #2
#    ==> yes: ready to 'callOpatch'
#    ==> no: go to #3
#    ==> yes: ready to 'callOpatch'
#    ==> no: Error and Exit
   if [ "$PLATFORM" == "Linux" -a "$ARCH" == "x86_64" ];then  
# JDK 8:  MaxPermSize <=== replaced by ===> MaxMetaspaceSize:  default = 128M
   # if JDK8 and has flag 'MaxPermSize', replace MaxPermSize <==> MaxMetaspaceSize

Faulty line is:
   if [ "$PLATFORM" == "Linux" -a "$ARCH" == "x86_64" ];then

Solution:
Issue with Solaris server is that "==" will not work, therefore I modified it to "=" in the opatch and opatch.pl files:

   if [ "$PLATFORM" = "Linux" -a "$ARCH" = "x86_64" ];then

I tried opatch command again and voola - we have a perfectly working OPatch!

[oracle->ogcoms]/u01/app/oracle/middleware13c/OPatch]./opatch version 
OPatch Version: 13.9.1.0.0 

OPatch succeeded. 

I miss the "non-buggy", simple unzip and replace OPatch directory method!

Wednesday, November 16, 2016

EM 13c: ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'


[UPDATE: Bugfix Bundle Patch is out: OMS Patch 25197714 ]



Enterprise Manager 13c Installation... Check!
Agents pushed to target servers... Check!
Cluster and Database targets discovered and configured... Check!

Aaah! EM 13c looks and feels brilliant... 

Starting to carry out the usual daily health checks on the core-banking database...
Performance page looks great...
No Locking/Blocking either...
Heading on to the SQL Monitoring page.... WHAAAAT ??? 
ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST'


After a successful deployment of Oracle Enterprise Manager 13.2, I've encountered a serious BUG in the 1st hour of my EM 13c experience! 

DBAs usually depend on the "SQL Monitoring" page of the OEM to communicate the long running active queries to the App Support Teams and take necessary actions. And, the graphs always make a better impact...

So naturally, on searching for a solution on Oracle Support, I came across the below document:

EM13c: Accessing SQL Monitoring Raises ORA-06553: PLS-306: wrong number or types of arguments in call to 'REPORT_SQL_MONITOR_LIST' (Doc ID 2199723.1)

Apparently, if you are accessing a database with a lower version that 12c, you are most likely to hit this bug!
I then raised a ticket with the support, and the assigned engineer informed me that the fix is expected in the next bundle patch releasing anytime between last week of Nov and 1st week of Dec 2016. 

Stay tuned for more...


Tuesday, November 15, 2016

EM 13c: What happens when you don't run root.sh after the Agent installation?

Admit it, there are times when you skip execution of the little "root.sh" script, especially when you don't have direct or indirect root access. Also, you may skip it when it was already executed for a previous installation on the same server.

Obviously, root.sh is "the most" important part of a RAC setup as it configures the CRS services and brings them up, but why in OEM 13c? Hmmm...

As a DBA, it's always a best practice to check what's different, or what's new in the root.sh files when installing a newer version of any Oracle based software. That's what I did when I implemented the OEM 13c's Oracle Management Server (OMS) and deployed agents across numerous Unix, Solaris and Windows servers that were hosting Oracle Databases.

We all know that the Enterprise Manager is no longer restricted to just monitoring databases. Especially from 12c, the Enterprise Manager has been Cloudified (oooo). It can monitor and manage almost any infrastructure hardware or software (if configured). It's a complete ENTERPRISE Cloud Management Solution! Why do I say this now? Wait for it...

So, after pushing Agents on the numerous servers, I skipped executing the root.sh, as I wanted to find out if there were any impact on any of the proceeding steps.

Everything was smooth, I discovered cluster and database targets, and added them to the EM. I was able to login into these targets and perform tasks as normal. I left for home, watched the Walking Dead, and when I returned in the morning, I noticed something interesting:

The Database Targets were stuck in "PENDING STATE"!

Initially, I thought maybe a network issue causing the Upload to pause. I then did the usual things to try and fix that problem:

/u01/app/oracle/agent13c/agent_inst/bin/emctl stop agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl clearstate agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl start agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl upload agent
/u01/app/oracle/agent13c/agent_inst/bin/emctl status agent

But it didn't work. I put on my Oracle Support socks and Google hat, and started searching for an answer. Looked at similar problems and solutions provided by my favorite Oracle Blogs - Pythian and DBAKevlar

Nada! didn't solve my problem.

Last resort - I applied the root.sh on one of the servers, and voola! In a few minutes the database targets of that server started showing Up status!

Ohhh ! I always wanted to know what happens if I don't run the root.sh!




Later, I checked if I can get the same information from somewhere else. So I headed to on of the Agent's page and guess what I found?




ERROR: NMO not setuid-root (Unix-only). What does that mean? And why does it require root.sh to be executed?

NMO is an executable file in the sbin folder of the Agent's Home. Some of the executables in this folder need to be owned by root in order for the "Enterprise" manager to be able to work for the whole "Enterprise". Clearly, the "oracle" user ownership and permissions are nearly not enough.

Before root.sh:

oracle@xxxxx /u01/app/oracle/agent13c/agent_13.2.0.0.0/sbin> ls -l
total 68594
-rwx--x--x   1 oracle   dba        22840 Sep 30 23:29 nmb.0
-rwx--x--x   1 oracle   dba       114768 Sep 30 23:29 nmgsshe.0
-rwx--x--x   1 oracle   dba       100528 Sep 30 23:31 nmhs.0
-rwx--x--x   1 oracle   dba      8695880 Sep 30 23:29 nmo.0
-rwx--x--x   1 oracle   dba      8608224 Sep 30 23:29 nmoconf
-rwx--x--x   1 oracle   dba      8617024 Sep 30 23:31 nmopdpx.0
-rwx--x--x   1 oracle   dba      8617024 Sep 30 23:31 nmosudo.0
-rwx------   1 oracle   dba        87776 Sep 30 23:31 nmr.0
-rw-r-----   1 oracle   dba         9615 Aug  1 15:37 nmr_macro_list
-rwx------   1 oracle   dba        14976 Sep 30 23:31 nmrconf

After root.sh:

oracle@xxxxx /u01/app/oracle/agent13c/agent_13.2.0.0.0/sbin> ls -l
total 203890
-rwsr-x---   1 root       dba          87224 Nov 14 10:30 nmb
-rwx--x--x   1 oracle     dba          87224 Oct  1 06:53 nmb.0
-rwxr-xr-x   1 root       dba          78656 Nov 14 10:30 nmgsshe
-rwx--x--x   1 oracle     dba          78656 Oct  1 06:53 nmgsshe.0
-rwsr-x---   1 root       dba          99680 Nov 14 10:30 nmhs
-rwx--x--x   1 oracle     dba          99680 Oct  1 06:59 nmhs.0
-rwsr-x---   1 root       dba        13002232 Nov 14 10:30 nmo
-rwx--x--x   1 oracle     dba        13002232 Oct  1 06:53 nmo.0
-rwx------   1 root       sys        13002232 Nov 14 10:30 nmo.new.bak
-rw-r-----   1 root       dba            188 Nov 14 10:30 nmo_public_key.txt
-rwx--x--x   1 oracle     dba        12857336 Oct  1 06:53 nmoconf
-rwxr-xr-x   1 root       dba        12862728 Nov 14 10:30 nmopdpx
-rwx--x--x   1 oracle     dba        12862728 Oct  1 06:59 nmopdpx.0
-rwxr-xr-x   1 root       dba        12862728 Nov 14 10:30 nmosudo
-rwx--x--x   1 oracle     dba        12862728 Oct  1 06:59 nmosudo.0
-rwsr-x---   1 root       dba         148312 Nov 14 10:30 nmr
-rwx------   1 oracle     dba         148312 Oct  1 06:59 nmr.0
-rwx------   1 root       sys         148312 Nov 14 10:30 nmr.new.bak
-rw-r-----   1 root       dba           9615 Aug  1 22:37 nmr_macro_list
-rwx------   1 oracle     dba          80648 Oct  1 06:59 nmrconf


I hope you see the difference in permissions and ownership of the files.

Once I applied the root.sh on the remaining servers, all the cluster and database targets were up and running.

Let me know if you have any questions or a conflict of thoughts :) !

Cheers,

Thursday, September 17, 2015

RMAN BACKUP AS COPY failing with ORA-00600 [kfioTranslateIO03] [17090]

Recently, I migrating a standalone database 'rnwphoto' from file system to ASM. I decided to go with the BACKUP AS COPY strategy, to save on downtime. But I was stuck for a long time trying to make the copy, but was being haunted by its failure.

Let's get to the background information. 

Database Standalone Home: /u01/app/oracle/product/11.2.0/dbhome_1 --> This is the Oracle Home used by rnwphoto database.

To convert the database to ASM based RAC, we installed GI in the GRID_HOME (/u01/app/11.2.0/grid) and RAC DB Home (/u01/app/oracle/product/11.2.0/dbhome_2)

Issue:

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA_DG'; 

Starting backup at 09-SEP-15 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: SID=8 device type=DISK 
allocated channel: ORA_DISK_2 
channel ORA_DISK_2: SID=69 device type=DISK 
allocated channel: ORA_DISK_3 
channel ORA_DISK_3: SID=132 device type=DISK 
allocated channel: ORA_DISK_4 
channel ORA_DISK_4: SID=72 device type=DISK 
channel ORA_DISK_1: starting datafile copy 
input datafile file number=00001 name=/u01/app/oracle/rnwphoto/system01.dbf 
channel ORA_DISK_2: starting datafile copy 
input datafile file number=00002 name=/u01/app/oracle/rnwphoto/sysaux01.dbf 
channel ORA_DISK_3: starting datafile copy 
input datafile file number=00005 name=/u01/app/oracle/rnwphoto/example01.dbf 
channel ORA_DISK_4: starting datafile copy 
input datafile file number=00003 name=/u01/app/oracle/rnwphoto/undotbs01.dbf 
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/09/2015 11:12:22 
RMAN-10038: database session for channel ORA_DISK_1 terminated unexpectedly 
channel ORA_DISK_1 disabled, job failed on it will be run on another channel 
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 09/09/2015 11:12:22 
RMAN-10038: database session for channel ORA_DISK_2 terminated unexpectedly 
channel ORA_DISK_2 disabled, job failed on it will be run on another channel 
RMAN-03009: failure of backup command on ORA_DISK_3 channel at 09/09/2015 11:12:22 
RMAN-10038: database session for channel ORA_DISK_3 terminated unexpectedly 
channel ORA_DISK_3 disabled, job failed on it will be run on another channel 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== 
RMAN-00571: =========================================================== 
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 09/09/2015 11:12:22 
RMAN-10038: database session for channel ORA_DISK_4 terminated unexpectedly 

ASM alertlog didnt show any errors or warnings.
In the database alertlog you will see that each time the RMAN command fails with  --> ORA-00600 [kfioTranslateIO03] [17090] 

A similar issue and it's resolution is shown in the Doc ID 1336846.1 
As per this document, this issue occurs due to multiple reasons and one of them is a permission issue... 

1. Check the group owner required on 'oracle' binary file to access ASM successfully:

[grid@node1-riz lib]$ cat $ORACLE_HOME/lib/config.c | grep ASM 
#define SS_ASM_GRP "asmadmin"     >>>> 
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP}; 

2. Let's check the group owner of all ORACLE_HOME/bin/oracle binary:

Grid Home:
[grid@node1-riz ~]$ ls -latr $GRID_HOME/bin/oracle 
-rwsr-s--x 1 grid oinstall 169802036 Sep 3 12:22 /u01/app/11.2.0/grid/bin/oracle 

RAC Database Home:
[oracle@node1-riz ~]$ ls -lart /u01/app/oracle/product/11.2.0/dbhome_2/bin/oracle -rwsr-s--x 1 oracle asmadmin 192296439 Sep 3 13:30 /u01/app/oracle/product/11.2.0/dbhome_2/bin/oracle

rnwphoto Standalone Database Home: 
[oracle@node1-riz ~]$ ls -lart $ORACLE_HOME/bin/oracle 
-rwsr-s--x 1 oracle oinstall 192296337 Sep 2 19:41 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle 
>>>>> *****************************************************  <<<<<

Do you see it? If not, read ahead...

Point #1 states that 'asmadmin' should be the group owner of the $ORACLE_HOME/bin/oracle

Let's ignore the Grid Home findings - as that's not a part of the problem. It's just for your information.

RAC Database Home works fine with the ASM -- as the group owner of the 'oracle' binary is 'asmadmin'

Standalone Database Home was created much before there was a Grid Home, so it still has 'oinstall' as the group owner of the 'oracle' binary file. 

Solution:
In the Standalone Oracle Home, change the ownership of the 'oracle' binary from oracle:oinstall to oracle:asmadmin --- and WOOOLA !!! 

WARNING: You need a downtime to make this change. If you change the ownership without shutting the database(s), then expect all the databases running on that ORACLE_HOME to CRASH!!!

Thursday, September 8, 2011

Oracle 11g: OER 7451 in Load Indicator

I installed Oracle 11g R1 on my laptop running Windows 7 Enterprise Edition (x64 bit).
As I was working on it, i felt like there was something wrong - it was too slow.
I shutdown the database, and shot the startup command, but got stuck on "Mounted".
Although I was able to get it started a minute later by stopping and starting the Services (services.msc).
I checked the alert log for any errors and found the following was filling up my alert log:

Thu Sep 08 21:00:41 2011
Errors in file e:\oracle11g\app\diag\rdbms\rizwan\rizwan\trace\rizwan_j008_8896.trc:
ORA-12012: error on auto execute of job 11689
ORA-01403: no data found
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:00:47 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:07 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:09 2011
ALTER TABLESPACE example READ WRITE
Completed: ALTER TABLESPACE example READ WRITE
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
Thu Sep 08 21:01:17 2011
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !

After checking a document on MOS, I found out what the mistake was. I was running a 32-bit Oracle Software on my 64-bit OS.

I fixed it by uninstalling the 32-bit software and installing the 64-bit software.