Friday, May 29, 2020

Create a Read Only EM Account on OEM13c Can Create AWR Report And View Performance Pages

Creating a read only EM account along with viewing databases performance pages and generate AWR and ASH reports looks a piece of cake task, but this took me long time to figure it out, so I thought to write this post about it.

Login to your OEM console: i.e. https://xxx:7803/em
Login as admin account i.e. sysman
Go to Setup -> Security -> Administrators


 -> Click on Create

(Enter the username and password)

-> Next (leave the default roles EM_USER, Public)

-> Next 
Check the following:
   Connect to any viewable target
    Monitor Enterprise Manager
    View any Target


   Go down the page to "Target Privileges" section:
   Click Add to add all databases the user will need to access
A new window will popup, check all the databases the user will need to access then click Select

   Then Check the databases and under the tab "Manage Target Privilege Grants" click the pen

Then Select: [Of course you will need to follow all the pages to check all the privileges you need]
   Connect Target, View Database Actions, View Database ADDM, View Database Advanced Queues, View Database Alert Logs, Manage Database ASH Reports, View Database ASH Reports and Analytics, Manage Database AWR Settings, View Database AWR Reports, View Database Backup, View Database Clients, View Database Links, View Database Dimensions, View Database Directory Objects, View Database Feature Usage, View High Availability Console, View Database indexes, View Database In Memory Setting, View Database Memory Usage, View Database Modules, View Database Materialized Views, View Database Packages and Package Bodies, View Database Performance Home Page, View Database Performance Privilege Group, View Database Optimizer Statistics, View Database Procedures and Functions, View Database Redo Logs, View Database Resources, View Database Roles, View Database Scheduler, View Database Schema Privilege Group, View Database Segments, View Database Sequences, View Database Services, View Database Sessions, View Database SQL Performance Analyzer, View Database SQL Monitor, View Database SQL Plan Control, Use Database SQL Tuning Advisor, View Database SQL Tuning Sets, View Database SQLs, View Database Storage Privilege Group, View Database Synonyms, View Database Table Data, View Database Tables, View Database Tablespaces, View Database Text Indexes, View Database Top Activity, View Database triggers, View Database Types, View Database Users, View Database Workspaces, View XML Database

  Click Continue
  This will allow the user to have a Read Only privilege on the selected DB along with generating AWR & ASH reports.


-> Next
-> Next
-> Finish

In case you need to create a similar EM user with similar privileges like the one you already created, you don't have to go through this daunting task again, just go to Setup -> Security -> Administrators


Check the user you want to create like and Click on "Create Like" button

-> Enter Name & Password


-> Click Review at the most right side of the page
 

-> Finish
That one was easy!

OEM 13c Agent installation failed with: plugins.txt not found. The Management Agent installation failed. The plug-in oracle.sysman.oh may not be present in the Management Agent software

Problem:
At the end of an OEM 13c agent manual installation on one of the servers I got this message:

Finished post install
Plugin txt:
Inside if , is empty
/u01/oracle/agent13c/plugins.txt not found. The Management Agent installation failed. The plug-in oracle.sysman.oh may not be present in the Management Agent software. Ensure that the Management Agent software has the oracle.sysman.oh monitoring and discovery plug-in. 


Reason:
When I copied the agent binary from the OMS server to target server I copied the one located under:
/u01/oracle/13cmiddleware/sysman/agent/13.4.0.0.0_AgentCore_226.zip

This agent was outdated because there were minor changes happened on the OMS since OMS was initially installed.

Fix:
You have to re-generate a new agent from OMS and use it on the target server to install the agent:

On the OMS server: Generate a new agent:

Create a temporary directory that will hold the new generated agent:
# mkdir /tmp/emcli

Login to OMS using emcli with sysman:
# cd /u01/oracle/13cmiddleware/bin
# ./emcli login -username=sysman -password=xxxx
Login successful
# ./emcli sync
Synchronized successfully

Get the platform details:
# ./emcli get_supported_platforms
-----------------------------------------------
Version = 13.4.0.0.0
Platform = Linux x86-64
-----------------------------------------------
Platforms list displayed successfully.


Generate the agent providing the temp location along with the platform and version you got in the  previous command:

# ./emcli get_agentimage -destination=/tmp/emcli -platform="Linux x86-64" -version=13.4.0.0.0
=== Partition Detail ===
Space free : 4 GB
Space required : 1 GB
Check the logs at /u01/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/get_agentimage_2020-05-27_13-55-05-PM.log
Downloading /tmp/emcli/13.4.0.0.0_AgentCore_226.zip
File saved as /tmp/emcli/13.4.0.0.0_AgentCore_226.zip
Downloading /tmp/emcli/13.4.0.0.0_Plugins_226.zip
File saved as /tmp/emcli/13.4.0.0.0_Plugins_226.zip
Downloading /tmp/emcli/unzip
File saved as /tmp/emcli/unzip
Executing command: /tmp/emcli/unzip /tmp/emcli/13.4.0.0.0_Plugins_226.zip -d /tmp/emcli
Exit status is:0
Agent Image Download completed successfully.


Now copy the newly generated agent to the target server and use it for installing the agent:
# scp /tmp/emcli/13.4.0.0.0_AgentCore_226.zip oracle@SRV1:/u01/oracle/agent13c

On the Target server install the copied agent software:

# mkdir /backup/tmp
# cd /u01/oracle/agent13c
# unzip 13.4.0.0.0_AgentCore_226.zip
# /u01/oracle/agent13c/agentDeploy.sh AGENT_BASE_DIR=/u01/oracle/agent13c \
-force \
-ignorePrereqs \
-invPtrLoc /etc/oraInst.loc  \
AGENT_PORT=3872 \
EM_UPLOAD_PORT=4903 \
OMS_HOST=OMSSRV \
ORACLE_HOSTNAME=SRV1 \
AGENT_INSTANCE_HOME=/u01/oracle/agent13c/agent_inst \
AGENT_REGISTRATION_PASSWORD=xxxx \
SCRATCHPATH=/backup/tmp


At the end of installation run this script: [By root]
# /u01/oracle/agent13c/agent_13.4.0.0.0/root.sh           

Make sure the agent is running:
# /u01/oracle/agent13c/agent_13.4.0.0.0/bin/emctl status agent

Conclusion:
As a rule of thumb, always generate a fresh agent on the OMS server to use for installing the EM agent on the target server.

Wednesday, May 20, 2020

Duplicate of a Standby DB fails with ORA-19845

While creating a Standby database on a RAC environment from another RAC database using duplicate method I was getting this weird error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/20/2020 14:20:14
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19845: error in backupControlFile while communicating with remote database server
ORA-17628: Oracle error 1580 returned by remote Oracle server
ORA-01580: error creating control backup file
ORA-19660: some files in the backup set could not be verified
ORA-19661: datafile 0 could not be verified
ORA-19845: error in backupControlFile while communicating with remote database server
ORA-17628: Oracle error 1580 returned by remote Oracle server
ORA-01580: error creating control backup file


I was running the duplicate command from the primary DB (Node1)

After long investigation I figured out that the Snapshot controlfile location is not shared between both RAC instances (on the primary side):

RMAN> show SNAPSHOT CONTROLFILE NAME;

RMAN configuration parameters for database with db_unique_name SPRINTS are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cloudfs/backup/sprint/snapcf_sprint.f';


That location "/cloudfs/backup/sprint" was only available on Node1 and not available on Node2

I've simply gone to Node2 and created the full location path and the duplicate succeeded!
On Node2:

# mkdir -p /cloudfs/backup/sprint
# chown oracle /cloudfs/ -R


Conclusion:
As a rule of thumb, the SNAPSHOT CONTROLFILE location should be available on all RAC nodes. Even if it's not shared between all nodes, it just needs to be exist!