Tag: Oracle

OEM Agent Plug-in Testing

Today I want to write about Oracle Enterprise Manager (OEM) 12c plug-ins and how to test them. 

OEM is divided into two logical parts, the EM Platform and the EM Plug-ins. The EM Platform basically consists of the UI console, job system, metrics, EM agent, etc. The EM Plug-ins are modules that you can attach to you EM Platform to extend the monitoring functionality. As a result, we have plug-ins for Oracle Databases, Fusion Middleware, even hardware or 3rd-party vendors like MS SQL Server.

The plug-ins have a set of Perl scripts that the agent uses to compute status and metrics for the targets. You can find these scripts under “/plugins//scripts”.
Let us see an example:

Fusion Middleware plug-in version 12.1.0.7:
$ cd  /plugins/oracle.sysman.emas.agent.plugin_12.1.0.7.0/scripts

In there you can see tons of Perl scripts. These scripts are being used by the agent to compute response and metrics for the targets registered in the agent.

Now let’s say you have a Web Cache target that shows an incorrect status in OEM and you want to know how the agent computes the response status of this target.

 $ ls webcache*.pl
webcacheesm.pl  webcacheIsStandalone.pl  webcacheresource.pl  webcacheresponse.pl

You can see that there’s a response script available. 
Now the question is, how to test it?

Based on the MOS id 1534087.1, you can create an env.sh script to load the required variables to your shell. I did create the env.sh file and source it:

$ . env.sh

Now I’m able to test the webcacheresponse.pl:

$ perl webcacheresponse.pl
em_result=1

Looks like having 1 as the result means that the Web Cache is up per the script’s logic. This result is passed to the agent and finally to the OMS.

Hope this is helpful for you while trying to investigate status related issues with your targets.
Thanks,

Alfredo

How to upgrade the RMAN recovery catalog to 12c?

Some days back I faced an issue while trying to upgrade the RMAN recovery catalog to support 12c databases.


RMAN> upgrade catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-07539: insufficient privileges to create or upgrade the catalog schema

The problem is that for 12c the catalog owner requires additional privileges. The solution is to run the dbmsrmansys.sql script that comes with the 12c binaries.

You have to copy this file from your 12c home “$ORACLE_HOME/rdbms/admin” directory to where your RMAN recovery catalog database is and execute it.

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql
The script is going to complain for the lack of 2 other scripts, but the upgrade runs just fine.
$ rman CATALOG rman@catalog
recovery catalog database Password:
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
RMAN> EXIT;
Thanks,

Alfredo

ALTER SYSTEM in a RAC environment

Few weeks ago I had to modify an instance parameter to a different value. In this case, I just wanted to modify it for only one instance and leave the other instance as it was.
I thought that if I didn’t specify the instance name using the SID clause, the default is going to be just that instance. I was really wrong about that and the command ended modifying both instances.
After looking at Oracle’s documentation here’s what I’ve found.
SID Clause
·         Specify SID = ‘*’ if you want Oracle Database to change the value of the parameter for all instances.
·         Specify SID = ‘sid’ if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = ‘*’.

If you do not specify this clause:

·         If the instance was started up with a pfile (client-side initialization parameter file), then Oracle Database assumes the SID of the current instance.
·         If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID = ‘*’.

If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.

So now I know that in future I need to specify the SID in a RAC instance using spfile.

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_2013.htm

Thanks,

Alfredo

Why You Should Attend GLOC 2016?

This year’s Great Lakes Oracle Conference (GLOC) is going to be huge! I had the privilege to attend the GLOC 2014 and present in the GLOC 2015. The conference continues to bring most of the best Oracle experts to the Midwest, helping professionals and organizations to keep up to-date on products plus a great networking opportunity.
Keynote presentations are in charge of Maria Colgan and Bryn Llewellyn, both from Oracle. If you are a performance enthusiast, you should know or use Snapper, well, Tanel Poder is in da’ house with “Connecting Hadoop and Oracle” and showing us “Modern Linux Tools for Oracle Performance Diagnosis”. Want more? Tim Gorman is going to present on “Troubleshooting Using ASH” and Kellyn Pot’Vin-Gorman with “OEM 13c – Empowering The DBA With Advanced Features”.
If you like to know why your execution plan changed or your SQL performance is not optimal, we have Carlos Sierra explaining how-to “Find An Execution Plan The CBO hides” and Mauro Pagano will show how-to use “SQLd360, SQL Tuning Diagnosis Made Easy”.
If what you like is Oracle development, Jeff Smith from Oracle is going to present “More Than Just Tips & Tricks: SQL Developer & How-To Turbo Charge Your Oracle Experience”. GLOC 2016 features more than 50 sessions for DBA’s, developers and application administrators plus the pre-conference workshops, including “RAC Attack”!
Don’t miss the opportunity and be part of the biggest conference in the Great Lakes Region!
Uhhh, I almost forgot! The early registration ends April 11, so hurry up!
https://www.neooug.org/gloc/
Thanks,

Alfredo

Compression Advisor killed my database!

Over the weekend one of the databases hung due to the flash recovery area was 100% full. I noticed one J001 process consuming significant CPU and I/O resources. Turns out this process was the automatic segment advisor job that runs on the weekend maintenance window.
The SQL executed was something like:

CREATE TABLE .dbms_tabcomp_temp_uncmp
TABLESPACE NOLOGGING
AS
SELECT /*+ FULL(.

) */
*
FROM .

…
After reading Oracle note Id 13463481.8 and confirming this with an SR, this is related to a bug for 11.2.0.3 version and fixed in 11.2.0.4. This bug generates excessive amount of redo when running the compression advisor on a table with a LOB column in a database running in ARCHIVELOG mode.

As we can’t just apply the required patch to the ORACLE_HOME right away, we decided to perform the workaround of disabling the automatic segment advisor task. The compression advisor is part of the segment advisor and is not possible just to disable one or the other.

To disable the segment advisor:

SQL> BEGIN
dbms_auto_task_admin.disable(
client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/  2    3    4    5    6    7
PL/SQL procedure successfully completed.
After executing the procedure, verify that the “auto space advisor” is disabled.

SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME                                                      STATUS
—————————————————————- ——–
auto optimizer stats collection                                  ENABLED
auto space advisor                                               DISABLED
sql tuning advisor                                               ENABLED
Although the advisor will not automatically run, you can always run it manually on the segments or indexes you want to be analyzed.
Thanks,

Alfredo

How do I open the pluggable database (PDB) when I start the container database (CDB)?

If you’re running pluggable databases (multitenant) in Oracle 12c, you may wonder; how do I open the pluggable database (PDB) when I start the container database (CDB)?
Well, really it depends on the 12c version you are running on. I will show you the options you have depending the version you are running.
If you are running 12.1.0.1, then you have these options:

a)    Create a trigger that opens all the PDBs in the CDB.
CREATE OR REPLACE TRIGGER
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END;
/

b)    Create a trigger that opens only one PDB in the container.
CREATE OR REPLACE TRIGGER
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE OPEN READ WRITE’;
END;
/

c)    If you are running RAC, then you can use the CRS to open the PDB.

srvctl add service -db -service -pdb -preferred “Container_Instance1,Container_Instance2”
Now, if you are running 12.1.0.2 version, there’s a neat new feature that saves the current state of the PDBs when the CDB restarts. In other words, if you save the state of the “pdb1” when is open, next time you start the container the “pdb1” will open automatically.

In order to do this, you can execute:

ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;
If you want to save the state of all the PDBs in the container.

ALTER PLUGGABLE DATABASE ALL SAVE STATE;
To discard the state of the PDBs.

ALTER PLUGGABLE DATABASE salespdb DISCARD STATE;
Want more?
http://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN14251
Thanks,


Alfredo

Using OMS DEBUG mode to troubleshoot OEM 12c problems

This time, I want to show you how to troubleshoot OEM problems by enabling DEBUG mode in the OMS. The virtual machine (VM) running my sandbox installation of OEM 12c 12.1.0.4 crashed during the night. After restarting the VM and all the OEM components, I wasn’t able to login using the SYSMAN account. The error from the console was not very explicit, just, “Authentication failed. If problem persists, contact your system administrator.”

In order to get more details about the error, I decided to enable DEBUG mode for the OMS and reproduce the error. This is what I did to enable DEBUG mode.

$ cd /u01/app/oracle/oms/oms/bin
$ ./emctl set property -name log4j.rootCategory -value “DEBUG, emlogAppender, emtrcAppender” -module logging
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value DEBUG, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value
After enabling DEBUG mode, I reproduced the error several times using the console. I also wrote down the approximate time of the error, just to easy the search in the log file. Searching in the emoms.trc file located under /em/EMGC_OMS1/sysman/log/, found an ORA-14400 error. The MOS note 1493151.1, explains how to fix the issue by adding a new audit partition.

$ cd /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/
$ view emoms.trc
java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
The final step is to disable the DEBUG mode for your OMS, otherwise the log files can grow real big and the performance could be affected.

$ ./emctl set property -name log4j.rootCategory -module LOGGING -value “WARN, emlogAppender, emtrcAppender”
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property log4j.rootCategory has been set to value WARN, emlogAppender, emtrcAppender for all Management Servers
OMS restart is not required to reflect the new property value
I hope this information is useful to you next time you are troubleshooting an OEM 12c issue.
Thanks,

Alfredo

OEM 12c very slow after upgrade to 12.1.0.4

I noticed that OEM 12c console was very slow a few hours after the upgrade to 12.1.0.4 version. 

Looking at the repository DB, found several OMS sessions consuming significant CPU resources.
Bug 19199023 explains that some SQL queries executed against the repository consume high CPU on the servers. This bug affects the DB plug-in 12.1.0.6 and the patch 19176910 should be applied to the plug-ins.

More information available on MOS note, 12.1.0.4 OEM: High CPU utilization on Repository Database due to SYSMAN query WITH TARGETGUID AS (SELECT target_guid, host_name FROM mgmt_targets (Doc ID 1912172.1)
Thanks,

Alfredo

Cannot start ASM – ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DATA”

Today, I faced and issue with an ASM instance. After bouncing the server, CRS went up along with the ASM instance, but the diskgroups were offline.
$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  OFFLINE      hosta
ora.FRA.dg
               ONLINE  OFFLINE      hosta
ora.LISTENER.lsnr
               ONLINE  ONLINE       hosta
ora.LISTENER_1.lsnr
               ONLINE  ONLINE       hosta
ora.asm
               ONLINE  ONLINE       hosta                 Started
ora.ons
               OFFLINE OFFLINE      hosta
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
      1        ONLINE  ONLINE       hosta
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       hosta
ora.database.db
      1        ONLINE  OFFLINE                               Instance Shutdown
ora.database1.db
      1        OFFLINE OFFLINE                               Instance Shutdown
I tried to start ora.DATA.dg resource, but it failed.

$ crsctl start resource ora.DATA.dg
CRS-2672: Attempting to start ‘ora.DATA.dg’ on ‘hosta’
CRS-5017: The resource action “ora.DATA.dg start” encountered the following error:
ORA-15032: not all alterations performed
ORA-15017: diskgroup “DATA” cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DATA”
. For details refer to “(:CLSN00107:)” in “/u01/oracle/grid/log/hosta/agent/ohasd/oraagent_oracle/oraagent_oracle.log”.
CRS-2674: Start of ‘ora.DATA.dg’ on ‘hosta’ failed
CRS-2679: Attempting to clean ‘ora.DATA.dg’ on ‘hosta’
CRS-2681: Clean of ‘ora.DATA.dg’ on ‘hosta’ succeeded
CRS-4000: Command Start failed, or completed with errors.
After checking the RAW devices on the host, everything appeared to be properly configured. Then I checked the configuration of the ASM instance, finding the ASM_DISKSTRING empty.
$ crsctl stat resource ora.asm -f
NAME=ora.asm
TYPE=ora.asm.type
STATE=OFFLINE
TARGET=OFFLINE
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r–
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALIAS_NAME=
ASM_DISKSTRING=
AUTO_START=restore
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
CREATION_SEED=11
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=asm) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DEGREE=1
DESCRIPTION=Oracle ASM resource
ENABLED=1
GEN_USR_ORA_INST_NAME=+ASM
ID=ora.asm
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SPFILE=+DATA/asm/asmparameterfile/registry.123.785123625
START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)
START_TIMEOUT=900
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.cssd)
STOP_TIMEOUT=600
TYPE_VERSION=1.2
UPTIME_THRESHOLD=1d
USR_ORA_ENV=
USR_ORA_INST_NAME=+ASM
USR_ORA_OPEN_MODE=mount
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.3.0
I updated ASM_DISKSTRING with the discovery path of the disks and then bounced ASM instance.
$ srvctl modify asm -d ‘/dev/sd*’
$ srvctl stop asm
$ srvctl start asm
After this the ASM instance came up cleanly and the diskgroups were mounted.

$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  ONLINE      hosta
ora.FRA.dg
               ONLINE  ONLINE      hosta
ora.LISTENER.lsnr
               ONLINE  ONLINE       hosta
ora.LISTENER_1.lsnr
               ONLINE  ONLINE       hosta
ora.asm
               ONLINE  ONLINE       hosta                 Started
ora.ons
               OFFLINE OFFLINE      hosta
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
      1        ONLINE  ONLINE       hosta
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       hosta
ora.database.db
      1        ONLINE  ONLINE                               Open
ora.database1.db
      1        OFFLINE ONLINE                               Open
Hope this help you to troubleshoot and fix the issue on your ASM, when is not able to find the disks.

Thanks,

Alfredo