Repository database contains all the target details and metric information . So best way is to run query against the repository database.

Here we have provided steps for generating database consolidation report

ENTERPRISE -> REPORT -> INFORMATION PUBLISHER REPORT

Click on CREATE:

ON GENERAL TAB:

CATEGORY – Compliance

SUBCATEGORY – Database Targets

TARGETS –   Select the repository database

PRIVILEGES – Run with SYSMAN privilege

ELEMENTS TAB:

Select ADD :

SELECT TABLE FROM SQL:

Here paste the sql query,

COMPLETE QUERY:

 SELECT A.HOST_NAME,
        A.DATABASE_NAME,
		TO_CHAR(A.STARTUP_TIME,'DD-MON-YYYY') "STARTUP_DATE" ,
        TO_CHAR(A.creation_date,'DD-MON-YYYY') "CREATION_DATE",
        A.LOG_MODE,A.CHARACTERSET,A.DBVERSION,
        AVAILABILITY_STATUS,E.SGASIZE,
		G.HOME_LOCATION "ORACLE_HOME",
		F.property_value "PORT",
		C.cpu_count,
		C.CPU_CORE_COUNT,
		SUBSTR(D.OS_SUMMARY,1,40) "OS PLATFORM" 
		FROM  SYSMAN.MGMT$DB_DBNINSTANCEINFO A, 
				SYSMAN.MGMT$AVAILABILITY_CURRENT B,
				SYSMAN.MGMT$DB_CPU_USAGE C ,  
				sysman.mgmt$os_hw_summary D,
				sysman.mgmt$db_sga_all E ,
				SYSMAN.MGMT$TARGET_PROPERTIES F  , 
				SYSMAN.MGMT$ORACLE_SW_ENT_TARGETS G 
	WHERE B.TARGET_TYPE='oracle_database' and
	A.TARGET_NAME=B.TARGET_NAME AND
	A.TARGET_NAME=C.TARGET_NAME AND 
	A.HOST_NAME=D.HOST_NAME and 
	a.target_name=E.target_name  AND 
	E.SGANAME='Total SGA (MB)'  AND
   A.TARGET_NAME=F.TARGET_NAME AND
   a.target_name=G.TARGET_NAME and  
   F.PROPERTY_NAME='Port' 
ORDER BY host_name desc;

This query you can customize as per your requirement:

Now schedule it:

You can preview the report also. Once the preview is fine, you can save it. HTML formatted report will be sent the the mentioned email addresses.

The post Generate custom report from OEM cloud control appeared first on DBACLASS.

Below are the steps for applying patch using OEM Cloud control.

ENTERPRISE – > PATCHES & UPDATES -> RECOMMENDED PATCH

Select the respective platform and database.

Select the Patch which you are planning to apply

Create the patch plan:

Give a new to the PATCH PLAN:

Select the database targets. You can select multiple databases at a time.

This is screen is very important.

There are two types of patching mode.

– Out of place – In this option, A new Oracle binary will be created and the patch will be applied on top of that. And database will started from the new Oracle home. This is recommended method, because, it reduced DB downtime and also during patching to binary if some issue happens, then also the old ORACLE_HOME is intact.

– In place – Here the database and listeners running from that home will be shut down , Then patch will be applied to that ORACLE_HOME, once done database will be started with post patch.

Here I have selected In place.

Set the ORACLE_HOME credentials , If not then use override and set the database server host username and password.

Now run the validation, to check the conflicts. This validation shouldn’t show any errors.

Now the validation is successful, Now go for DEPLOYMENT.

With this patching has been completed. Now you can log in to the database and check the patch registry.

SQL> SET LINES 200
SQL> select patch_Id,status,description from dba_registry_sqlpatch;

  PATCH_ID STATUS          DESCRIPTION
---------- --------------- -------------------------------------------------------------------------------------------------
  24917972 SUCCESS         Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)
  24732082 SUCCESS         DATABASE PATCH SET UPDATE 12.1.0.2.170117

The post Apply database patch from OEM Cloud control appeared first on DBACLASS.

Here i have listed few useful commands.

1. Login to syman

./emcli login -username=SYSMAN

2. SYNC OMS REPOSITORY:

./emcli login -username=SYSMAN
./emcli sync

3. Get THE LIST OF TARGETS

 ./emcli get_targets

For listing only the oracle_database

 ./emcli get_targets -targets="oracle_database"

4. Delete a target from OEM cloud control:

 ./emcli delete_target -name="B2BOMT2" -type="oracle_database"
Target "B2BOMT2:oracle_database" deleted successfully

5. Run autodiscovery on a target host

You can configure autodiscover from GUI also, after a target host is added. Below is for doing using EMCLI

Create a property file:

 ./emcli describe_job_type -job_type=RunAutoDiscovery > inputs.prop

Update the property file:( It will look as below)

# Description: (Optional) The user specified name of the job
name=AUTO_DISC

# Description: (Optional) The job type for this job
type=RunAutoDiscovery

# Description: (Optional) The user specified description of the job
description=

# Description: The job owner. The job owner is the user who creates the job.
# Default: the logged in user
# The job owner information displayed here is for documentation only and user is not expected to change it.
owner=

# Description: (Optional) The kind of job
# Legal Values: active, library
kind=


# Fill in the target list before submitting.
# For Example:
#     target_list=MyTarget:cluster
target_list=172.20.294.137:host


# Description: The type of targets to use for this job
targetType=host


# Description: (Optional) Notify the job owner when a selected state occurs
# Allowed Values:  SCHEDULED, RUNNING, ACTION_REQUIRED, SUSPENDED, SUCCEEDED, PROBLEMS
notification=

# Description: (Optional) The run time properties for the job
properties=

Create a job:

 ./emcli create_job -input_file=property_file:inputs.prop

Creation of job "AUTO_DISC" was successful.
The following properties were not set to any value and were ignored: description, owner, kind, notification, properties

Get the job status:

emcli get_jobs -name=AUTO_DISC

Name       Type              Job ID                            Execution ID                      Scheduled            Completed  TZ Offset  Status   Status ID  Owner   Target Type  Target Name
AUTO_DISC  RunAutoDiscovery  4A115DB150B74968E0540010E01F328C  4A115DB150BA4968E0540010E01F328C  2017-03-06 17:03:55  none       GMT+03:00  Running  2          SYSMAN  host         172.20.294.137

Alternatively we can see the execution detail as :

 ./emcli get_job_execution_detail -execution=4A115DB150BA4968E0540010E01F328C -xml -showOutput


   
      
   
   
      
         
      
   

emcli get_jobs -name=AUTO_DISC
Name       Type              Job ID                            Execution ID                      Scheduled            Completed            TZ Offset  Status     Status ID  Owner   Target Type  Target Name
AUTO_DISC  RunAutoDiscovery  4A115DB150B74968E0540010E01F328C  4A115DB150BA4968E0540010E01F328C  2017-03-06 17:03:55  2017-03-06 17:05:22  GMT+03:00  Succeeded  5          SYSMAN  host         172.20.204.137

6 . Add target database to OEM cloud:

SYNTAX:

./emcli add_target -name=”ORACLE_SID” -type=”oracle_database” -host=”$oemhost” -credentials=”UserName:dbsnmp;password:dbsnmp_password;Role:Normal” -properties=”SID:ORACLE_SID;Port:1521;OracleHome:;MachineName:\”

EXAMPLE:

 ./emcli add_target -name="CRMB2RT" -type="oracle_database" -host="172.29.284.137" -credentials="UserName:dbsnmp;password:oracle4u;Role:Normal" -properties="SID:CRMB2RT;Port:1543;OracleHome:/oracle/app/oracle/product/12.1.0.2_B2R/dbhome_1;MachineName:dbac61-1"

Target "CRMB2RT:oracle_database" added successfully

7. Get list of plugins installed on OMS server:

./emcli list_plugins_on_server

OMS name is dbadev64.dba.com.sa:4889_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Audit Vault                           oracle.em.soav                12.1.0.4.0
Oracle GoldenGate                            oracle.fmw.gg                 13.1.1.0.0
Oracle Cloud Framework                       oracle.sysman.cfw             13.2.1.0.0
Oracle Database                              oracle.sysman.db              13.2.1.0.0
Oracle Fusion Middleware                     oracle.sysman.emas            13.2.1.0.0
Oracle Fusion Applications                   oracle.sysman.emfa            13.2.1.0.0
Oracle Siebel                                oracle.sysman.empa            13.1.1.0.0
Oracle ORAchk Healthchecks                   oracle.sysman.orhc            13.1.1.0.0
Systems Infrastructure                       oracle.sysman.si              13.2.1.0.0
Oracle Exadata                               oracle.sysman.xa              13.2.1.0.0

8 . Get list of plugins installed on a Management Agent:

./emcli list_plugins_on_agent -agent_names="172.25.224.2:3872"
The Agent URL is https://172.20.204.2:3872/emd/main/ -
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Home                                  oracle.sysman.oh              13.2.0.0.0
Oracle Database                              oracle.sysman.db              13.2.1.0.0

9 . Create BLACKOUT:

Creating blackout for single database
DBNAME – DBATEST
BLACKOUT DUATION – 30 MIN
SCHEDULE- START IMMEDIATELY

./emcli create_blackout -name="db_blackout_phase1" -reason="patching" -add_targets="DBATEST:oracle_database" -schedule="duration:30"
Blackout "db_blackout_phase1" created successfully

For multiple databases:
DB NAME – DBATEST1,DBATEST2

emcli create_blackout -name="DBACLASS_MULTDB_BLACKOUT" -reason="psu patchcing" -add_targets="DBATEST1:oracle_database;DBATEST2:oracle_database" -schedule="duration:30"

Create blackout for a host(target agent) and all this services/database runnin on that server

TARGET AGENT HOST – localhost.dbaclass.com
DURATIO – 30 MIN
START IMMEDIATELY

 ./emcli create_blackout -name="DBACLASS_HOST_BLACKOUT" -reason="OS PATCHING OUTAGE" -add_targets="localhost.dbaclass.com:host" -schedule="duration:60" -propagate_targets
Blackout "DBACLASS_HOST_BLACKOUT" created successfully

Create blackout for RAC database:

emcli create_blackout -name="DBACLASS_RAC_BLACKOUT" -reason="Mainteneance" -add_targets="instance_name:oracle_database;cluster_name:rac_database" -schedule="duration:60"

Get status of a blackout:

SYNTAX

./emcli get_blackout_details  -name="BLACK_OUT_NAME"

EXAMPLE:

./emcli get_blackout_details  -name="db_blackout_phase1"

Stop a BLACKOUT:

 ./emcli stop_blackout -name="db_blackout_phase1"
Blackout "db_blackout_phase1" stopped successfully

Delete a blackout:

./emcli delete_blackout -name="db_blackout_phase1"
Blackout "db_blackout_phase1" deleted successfully

10 . Decommission an agent and delete the respective targets:

./emcli delete_target -name="dbahost-local:3872"  -type="oracle_emd" -delete_monitored_targets

Target "dbahost-local:3872:oracle_emd" deleted successfully

11. Stop and start an agent from OMS server

STOP AN AGENT:

/data/app/oracle/oms13c/bin$ ./emcli stop_agent -agent_name="dbahost-local:3872" -host_username=oracle
Host User password:
The Shut Down operation is in progress for the Agent: dbahost-local:3872
The Agent "dbahost-local:3872" has been stopped successfully.
---------------------
Operation Output
---------------------
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.Stopping agent ... stopped.

START AN AGENT:

./emcli start_agent -agent_name="dbahost-local:3872" -host_username=oracle
Host User password:
The Start Up operation is in progress for the Agent: dbahost-local:3872
The Agent "dbahost-local:3872" has been started successfully.
---------------------
Operation Output
---------------------
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.Starting agent ..................................... started.

12. Change password for dbsnmp user of target database

This will prompt for old password and new password.

./emcli update_db_password -target_name="B2CCRMT2" -target_type="oracle_database" -user_name="dbsnmp" -change_at_target=yes

Enter value for old_password :
Enter value for new_password :
Enter value for retype_new_password :
Successfully submitted a job to change the password in Enterprise Manager and on the target database: "B2CCRMT2"
Execute "emcli get_jobs -job_id=50A66C2EF0EB6E7AE0540010E01F328C" to check the status of the job.
Search for job name "CHANGE_PWD_JOB_1496046676449" on the Jobs home page to check job execution details.

The post EMCLI command – OEM 12C appeared first on DBACLASS.

The command need to be run from OMS home.

SYNTAX:

emcli login -username=sysman -password=

emcli update_db_password -target_name="" -target_type="oracle_database" -user_name="dbsnmp" -change_at_target=yes -old_password="" -new_password="" -retype_new_password=""

For standalone database:(TESTDB)

1. login to sysman user in OMS

cd $OMS_HOME/bin

./emcli login -username=sysman -password=oracle1234
Login successful

2. Check the target staus:

./emcli get_targets -target="oracle_database" | grep TESTDB
1       Up               oracle_database       TESTDB

3. Update the dbsnmp password

./emcli update_db_password -target_name="TESTDB" -target_type="oracle_database" -user_name="dbsnmp" -change_at_target=yes -old_password="dbsnmp" -new_password="oracle" -retype_new_password="oracle"

Successfully submitted a job to change the password in Enterprise Manager and on the target database: "TESTDB"
Execute "emcli get_jobs -job_id=49A8A308BB280CAFE0540010E01F328C" to check the status of the job.
Search for job name "CHANGE_PWD_JOB_1488359226974" on the Jobs home page to check job execution details.

4. Check the job status:

./emcli get_jobs -job_id=49A8A308BB280CAFE0540010E01F328C
Name                          Type                Job ID                            Execution ID                      Scheduled            Completed            TZ Offset  Status     Status ID  Owner   Target Type      Target Name
CHANGE_PWD_JOB_1488359226974  dbUpdateDBPassword  49A8A308BB280CAFE0540010E01F328C  49A8A308BB2B0CAFE0540010E01F328C  2017-03-01 12:07:07  2017-03-01 12:07:11  GMT+03:00  Succeeded  5          SYSMAN  oracle_database  TESTDB

FOR RAC DATABASE USE BELOW SYNTAX:

emcli update_db_password -target_name="" -user_name= -target_type=rac_database -change_all_references=yes -change_at_target=yes -old_password= -new_password= -retype_new_password=

emcli set_monitoring_credential -target_type="rac_database" -target_name="" -set_name="DBCredsMonitoring" -cred_type="DBCreds" -attributes="DBUserName:;DBPassword:;DBRole:"

The post Change dbsnmp password for target db in oem 12c appeared first on DBACLASS.

Here I will show how to upgrade database from 12.1.0.1 to 12.1.0.2 . 

Note – Make sure that 12.1.0.2 ORACLE_HOME is installed. 

SOURCE ORACLE_HOME(12.1.0.1) – /oracle/app/oracle/product/12.1.0

TARGET ORACLE_HOME(12.1.0.2)  – /oracle/app/oracle/product/12.1.0.2_CRM/dbhome_1

Enable Flashback:

Before starting the upgrade, Enable flashback in the database, so that we can create a restore point during the upgrade, And if upgrade fails, we will be able to restore the database to before upgrade stage.

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest=’/archive/oradata/B2BCRMT3/arch/FRA’ scope=both;

System altered.

SQL> alter database flashback on;

Database altered

Go to the HOMEPAGE of the database, which need to be upgraded.

ORACLE DATABASE -> PROVISIONING – > UPGRADE DATABASE

Provide sys password and select the NEW ORACLE_HOME:

This step will do the precheck . Please take necessary action for the Validation results and retry the precheck.

As precheck is cleared, lets proceed with upgrade settings

1. DEGREE OF UPGRADE PARALLELISM – 4

2. DIAGNOSTIC DESTINATION – /oracle/app/oraclec

3. AUDIT_FILE_DEST=/oracle/app/oracle/admin/B2BCRMD1/adump

4. In Backup Restore section, select Use of guarantee restore point

5. De-select compilation of invalid object , as we will run utlrp.sql, once upgrade is completed.

We can start the upgrade Immediately or schedule it for Later. 

Submit the upgrade job

Monitor the upgrade job :

Upgrade has been completed succcessfully.

Now run utlrp.sql script to compile all the object

@$ORACLE_HOME/rdbms/admin/utlrp.sql

SEE ALSO:

Upgrade database from 11g to 12c manually

The post Upgrade database using OEM 12C cloud control appeared first on DBACLASS.

Shutdown the Agent:

SETUP – > MANAGE CLOUD CONTROL -> AGENTS 

Select the HOST and click on SHUT DOWN

Decommission Agent:

Go to the Home page of the target host

AGENT  – > TARGET SETUP – > AGENT DECOMMISSION

Agent has been removed successfully.

The post Deinstall Management Agents Oracle 12c cloud control appeared first on DBACLASS.

If you haven’t deployed Agents yet, click here

First we need to configure Auto Discover the target host.

SETUP -> ADD TARGET -> CONFIGURE AUTO DISCOVERY

Click on the host,where the database resides and click on DISCOVER NOW

Now Auto Discover is configured.  Lets check the results.

SETUP – > ADD TARGET -> AUTO DISCOVERY RESULTS

This will list down the list of databases and listeners, that can be promoted to cloud for monitoring.

Select the database and click on PROMOTE

Pass the dbsnmp password and click on TEST CONNECTION, to check whether you are able to ping to database or not.

Click on Next to confirm

Now the database has been added to cloud for monitoring.

The post Add database as target oracle 12c cloud control appeared first on DBACLASS.

ORACLE DATABASE – > MONITORING – > BLACKOUTS

Select the databases or services  which want to include in blackout.

You can select whether we need to initiate the blackout immediately or schedule it for future.

Removing BLACKOUT:

Once target maintenance is done, we need to remove the targets from Blackout, to enable the incident and notification services.

ENTERPRISE – > MONITORING – > BLACKOUTS

The post Blackout targets in OEM 12c cloud control appeared first on DBACLASS.

Oracle enterprise Manager Cloud control 12c is used for managing and monitoring multiple oracle database and related targets from a single console.Although Enterprise Manager Cloud Control is viewed as single entity, It has below components.

Oracle Management Agent:

Oracle Management Agent (Management Agent)  component is deployed on each monitored host. It is responsible for monitoring all of the targets running on those hosts, communicating that information to the middle-tier Oracle Management Service, and managing and maintaining the hosts and its targets.

Oracle Management Service:

Oracle Management Service is a Web-based application that is connected with the Management Agents and the Management Plug-ins to discover targets, monitor and manage those targets, and dump the collected information in a repository . Oracle Management Service also  provide the user interface for Enterprise Manager Cloud Control.

Oracle Management Repository:

The Oracle Management Repository is a storage location where all of the information collected by the Management Agent is stored. It is basically a database, Where Oracle Management Service uploads the monitoring data it receives from the Management Agents to the Management Repository.Oracle Management service retrieves the data from this repository database and display it in  the Enterprise Manager Cloud Control console.

Management Console:

The OEM Cloud Control console is the web-based user interface for Enterprise Manager Cloud Control. With the help of the console, we can monitor and administer all the systems and services like databases, hosts, middleware application servers, listeners, and so on, are easily managed from one central location.

Below is the simple Architectural Diagram:

Required Softwares:

1. Oracle database 12.1.0.2

2. Oracle Enterprise Manager Cloud control 12C

3.Database Template for OEM

Installing OEM 12C cloud Control:

Before Installing OEM cloud control, We need to do below 2 steps.

Installing oracle database software 12c

Install the oracle database software 12.1.0.2 on the server, where the cloud will be installed. This ORACLE_HOME will be used for creating the repository database.

ORACLE_HOME : /oracle/app/oracle/product/12.1.0/dbhome_1

Creating Repository database

Now create a repository database by using dbca utility. Make sure to use template while creating db. Once database is created, add below entry in the oratab.

 BSSCCNTL:/oracle/app/oracle/product/12.1.0/dbhome_1:N

Now Unzip the cloud  software and execute runInstaller.

cd /data/software/12cloud$ cd /data/software/12cloud

unzip /data/software/12cloud$ unzip em12105_sol64_disk1.zip

unzip /data/software/12cloud$ unzip em12105_sol64_disk2.zip

unzip /data/software/12cloud$ unzip em12105_sol64_disk3.zip

./runInstaller

Here, provide the REPOSITORY DATABASE details.

Run the above allroot.sh script as root user. And press ok.

With this cloud installation has been completed. Now it will generate below url to access the cloud console.

https://172.20.204.67:7799/em

SEE ALSO:

How to deploy Management Agents in target host:

How to restart Agent in oracle 12c cloud control:

add database as target in oracle 12c cloud control:

Blackout targets in oracle 12c cloud control:

The post Install oracle enterprise manager cloud control 12c appeared first on DBACLASS.

1. update the passwordn using emctl config

  syntax –  ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys password>  -new_pwd < new sysman password> 

oracle@bttstdev64:...ta/app/oracle/oms12cr5/oms/bin$ ./emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd oracle1234 -new_pwd oracle1234
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.

Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.

2. Stop the OMS

oracle@bttstdev64:...ta/app/oracle/oms12cr5/oms/bin$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down

oracle@bttstdev64:...ta/app/oracle/oms12cr5/oms/bin$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up

oracle@bttstdev64:...ta/app/oracle/oms12cr5/oms/bin$
oracle@bttstdev64:...ta/app/oracle/oms12cr5/oms/bin$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
WebTier is Up

The post How to change sysman password for oracle 12c cloud control appeared first on DBACLASS.