Changing the passwords of Database users

Hi All,

Last week a question turned up in OTN forums about changing the passwords of Database users (Schemas).

It’s very simple, just issuing ‘ALTER USER XXX IDENTIFIED BY PASSWORD YYY’. Isn’t it? Why we have to spend so much time on a strategy to change passwords?

It’s not so simple as we think. Consider a scenario where the application stores the passwords, of course in encrypted form, and tries to connect to the Database with those. If we change the passwords without making the application aware of the changes, Your entire application will become unusable.

Leave about the application users. We’ll change their passwords in a consistent way.  Can we change the passwords of users like  SYSMAN, DBSNMP through the ALTER USER… command? If your answer is YES, you are wrong. Even they’re not application users, they too have some dependencies. My intention in writing this article is to address those. BTW, I’m taking some of the commands and procedures to be followed from Metalink.

Steps to change SYSMAN password [270516.1]

Steps for Changing the SYSMAN Password in 10.x Grid Control

The below steps are applicable to 10.1.0.x to 10.2.0.4 version of Grid Control:

  1. Stop the Agent monitoring the ‘OMS and Repository’ target, to avoid the SYSMAN account in the repository database from getting locked, during the password change.

    –  Issue the query below to Find which Grid Agent is Monitoring the “OMS and Repository” Target?

              SELECT target_name, target_type, host_name, emd_url FROM mgmt_targetswhere target_type =  ‘oracle_emrep’;

–  Connect to the node where the above Agent is installed and stop the agent by doing,

                           cd <AGENT_HOME>/bin

                           emctl stop agent

2. Stop all the OMS instances that are associated with the repository. From each node where the OMS is running,

                            cd <OMS_HOME>/opmn/bin

                            opmnctl stopall

3. Change the SYSMAN password in the file emoms.properties:

–  Take a backup of the <OMS_HOME>/sysman/config/emoms.properties file.

– Edit the file emoms.properties and change the following lines

                           oracle.sysman.eml.mntr.emdRepPwd=new_password

                           oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

Note:

1. The oracle.sysman.eml.mntr.emdRepPwdSeed parameter should not be modified.
2. Do not create an additional entry for the oracle.sysman.eml.mntr.emdRepPwd / oracle.sysman.eml.mntr.emdRepPwdEncrypted, but modify the existing parameter values.
3. Do not add any extra space to the password in clear text or the value for oracle.sysman.eml.mntr.emdRepPwdEncrypted parameter.

Now we’re done with the Password changes on the application file. It’s time to change them in the Database and some other places

  1. Change the SYSMAN password in the Database hosting the repository:

– Set the $ORACLE_SID environment variable to point to the EM Repository Database.

– Logon to the Repository Database with SQL*Plus as a dba user and issue the command:

SQL> alter user sysman identified by <newpassword>;

2. Restart each of the OMS:

From each node where an OMS is running,execute:

                          cd <OMS_HOME>/opmn/bin

                          opmnctl startall

3. Change the SYSMAN password for the “OMS and Repository” target:

Restart the Agent monitoring the “OMS And Repository” target, that was stopped in Step 1:

                     cd <AGENT_HOME>/bin

                               emctl start agent

Login to the Grid Control Console and navigate to Setup ->Management Services and Repository.

From the bottom of the page in the “Overview”, click on the link “Monitoring Configuration”.
If the monitoring Agent is still down, you will get the following error message:

               Communication from the Oracle Management Service host to the Agent host failed. Refer to help for details.                 IOException in sending Request :: Connection refused 

In the Monitoring Configuration page, change the value in the field “Repository password”
Click OK

This will update the <AGENT_HOME>/sysman/emd/targets.xml of the monitoring Agent with the new SYSMAN   password.

Steps for Changing the SYSMAN Password in 10.2.0.5 and 11.1 Grid Control

When using 10.2.0.5 or 11.1 OMS, all the above steps can be performed by executing a single command:

cd <OMS_HOME>/bin

emctl config oms -change_repos_pwd -change_in_db

Example of output for OMS 10.2.0.5 running on Windows:

C:\app\oracle\oms10g>emctl config oms -change_repos_pwd -change_in_db
Oracle Enterprise Manager 10g Release 5 Grid Control
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Enter Repository User's Current Password :
Enter Repository User's New Password :

Changing password in backend ...
Password changed in backend successfully.
Changing monitoring credentials ...
Monitoring credentials changed successfully.
Updating emoms.properties....
emoms.properties updated successfully....
Please bounce OMS.
Successfully changed repository password.
If you have multiple OMS's in your environment, please run this cmd on all of them

Note:

1. The above command
– will prompt you for the current password and the new password.
– will modify the sysman password in the repository database, in the emoms.properties configuration file / WLS Credential store and the monitoring credentials for the ‘OMS and Repository’ target.
– will ask you to bounce the OMS once the command is completed successfully
– for 11G, the command will modify the password for sysman and sysman_mds users in the repository database, modify the password in the credential store and change the monitoring credentials for the ‘OMS and Repository’ target.

2. The ‘-change_in_db’ parameter is optional and is used to change the SYSMAN password in the repository database.

– If the sysman password is manually modified in the repository database, this option need not be specified.
– When this parameter is specified, the command will prompt for the current sysman password.
– If the current sysman password is lost / unknown then:

a) Log in to your Repository database as SYS and run:
SQL> ALTER USER SYSMAN IDENTIFIED BY <newpassword>;

where newpassword is the new password you want for SYSMAN.

b) From a command prompt run:
cd $OMS_HOME/bin
./emctl config oms -change_repos_pwd
                     Provide newpassword when prompted.

3. If using multiple OMS, then execute the above command on one of the OMS.
On the Additional OMS, execute:

<OMS_HOME>/bin/emctl config oms -change_repos_pwd

Reminder to update the emcli, on every monitoring host, with a new password if the sysman password is changed,

as this will allow emcli to login with the new password.
Reference:
Note:1090075.1: EM CLI: How to Install and Setup emcli (Enterprise Manager Command Line Interface)?

$> emcli setup -url=http://omsmachine.domain:port/em -username=em_user

Steps to change DBSNMP password [on multiple hosts] [886984.1]

We can change the DBSNMP password on multiple target Databases through EMCLI (Enterprise Manager Command Line interface).

Please go through the link here for the installation instructions of EMCLI: https://querycsm.wordpress.com/2012/08/13/adding-targets-in-oem-12c-in-silent-mode/

1. Using input_file option with update_password EMCLI Verb

emcli update_password
-target_type=oracle_database
-target_name=database
-credential_type=DBCreds
-key_column=DBUserName:oracle
-non_key_column=”FILE1″
-input_file=”FILE1:/tmp/user_passwd”
And the contents of the user_passwd file is
DBPassword:oracle:oracle1

2. Another way of using the files option with other password management verbs like update_db_password, update_host_password, update_target_password
You can create a chng_DB_mon_passwd (Shown below) with multiple targets to change the password. May be changing the dbsnmp password on 10 db target:
And Execute like –
emcli login -user=sysman -pass=xxxxxxxx
emcli argfile chng_DB_mon_passwd

This will help in change the password at multiple targets in one emcli execution.

Chng_DB_mon_passwd File :
update_db_password -target_name=database1 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database2 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database3 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database4 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes

Have a nice day.
CSM

Adding targets to OEM 12C System in silent mode

Hi Guys,

In this article I’m going to tell you how to add targets to the OEM 12C system.

I’ve already illustrated how to install OEM here (https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-i-basic-prerequisites/ and https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-ii-preparing-the-response-files-and-running-the-installer/) and deploying the agents (https://querycsm.wordpress.com/2012/08/06/deploying-oem-12c-agents-in-silent-mode/).

After setting up the OEM and deploying the agents into the machines which contain our targets to be monitored, the next step is to add the actual targets into the OEM system. This allows you to monitor the targets (Databases etc…) through the OEM console. This is the last but most important step in the entire process of setting up the OEM as you can’t monitor any Database (I mean to say any target but i mostly use the term ‘Database’ in the context of the word ‘target’ as my main focus is to monitor the Databases through OEM) or can’t see it in the console until you add it to the OEM System.

It’s pretty easy to add targets in GUI mode but you have to be a bit patient if you want to do any automation through silent mode 🙂

Let’s see how we could add the targets in silent mode.

The first step for adding the targets in silent mode is to install the EMCLI (Enterprise Manager Command Line Interface). This is a tool provided by Oracle to perform all the admin activities like adding targets, dropping targets etc… through the command line. You can find more about emcli @ http://docs.oracle.com/cd/B19306_01/em.102/b40004/cli.htm

  • Download emcli to your desktop by typing https://oms_host:port/em/console/emcli/download in the browser
  • Ftp this to all the machines where our targets are present (machines in which we deployed agents earlier).
  • Install emcli by issuing
    java -jar <emclikit.jar> client -install_dir=<Some directory which is considered as the EMCLI_HOME>
  • After finishing up the installation do,
    cd $EMCLI_HOME
    ./emcli setup -url=”https://oms_host:port/em&#8221; -username=sysman -password=<sysman password> -dir=$EMCLI_HOME -trustall

This will finish up the emcli installation – setup process. Do this on all the hosts which holds our targets of interest.

After the setup the next thing remained is to add the targets.

You can do that with a simple command like

cd $EMCLI_HOME

./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:<Oracle home of the target>;MachineName:<Machine where the target is present (current machine)>\”

This will cleanly add you Database to the OEM 12C and you can start to see this Database and it’s monitoring statistics in the OEM console.

refer this for further reading on emcl: http://docs.oracle.com/cd/E24628_01/doc.121/e24473/toc.htm

regards,

CSM

Deploying OEM 12C Agents in silent mode

Hi Friends, A Happy Friendship day to you all 🙂

I’ve already posted 2 articles on how to install OEM 12c in silent mode (https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-i-basic-prerequisites/  and https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-ii-preparing-the-response-files-and-running-the-installer/).

As an extension to them, I’m posting a new article on ‘Deploying OEM Agents’ . Please go through it to get an ideas of how to deploy the OEM 12C agents in silent mode.

We typically deploy agents on the machines which have our targets to be monitored.

For Eg: You want a Database called DB1, which is on machine MACH1, to be monitored. Then you need to deploy agents in that machine MACH1 which collects data from DB1 and uploads into the central OEM repository.

So you have to the agent deployment in all the machines which contain the targets you want to monitor through the OEM.

The main steps in deploying OEM 12C  agents are

  • Download OEM Agent software (latest software available is 12.1.0.1.0_AgentCore_226.zip) — One time activity
  • Copy the zip file to all the hosts in which you want to install the Agent software (The hosts which holds your targets like the Databases to be monitored) — Do this for every host in which you want to install agent software
  • Prepare a response file like we did for OEM software installation — Do this for every host in which you want to install agent software as this file contain some common attributes and some specific to the host
  • Deploy the agents — Do this for every host in which you want to install agent software

Download OEM Agent software:

On the host where you installed OMS (OEM software), launch emcli

Go to OMS_HOME/bin and give

./emcli login -username=sysman -password=XXXXXXX

Login successful

Then you need to synchronize emcli

./emcli sync

Synchronized successfully

Now check the platforms for which the Management Agent software is available on the OMS host

./emcli get_supported_platforms

Getting list of platforms …

Check the logs at /XXXXX/oms/bin/agent.log

About to access self-update code path to retrieve the platforms list..

Getting Platforms list  …

———————————————–

Version = 12.1.0.1.0

Platform = Linux x86-64

———————————————–

Platforms list displayed successfully.

Now get the Management Agent software from Oracle Software Library to the directory of your choice (on the OMS host)

./emcli get_agentimage -destination=/tmp/agent_software -platform=”Linux x86-64″ -version=”12.1.0.1.0″

Platform:Linux x86-64

Destination:/tmp/agent_software

=== Partition Detail ===

Space free : 14 GB

Space required : 1 GB

Check the logs at /tmp/agent_software/get_agentimage_2012-07-14_23-10-00-PM.log

Setting property ORACLE_HOME to:/XXXXX/oms

calling pulloneoffs with arguments:/XXXXX/oms/sysman/agent/12.1.0.1.0_AgentCore_226.zip 12.1.0.1.0 linux_x64

Check this logs for more information: /XXXXXX/oms/sysman/prov/agentpush/logs

Now go and check for the Agent software zip file under /tmp

You’ll find a file like “12.1.0.1.0_AgentCore_226.zip”; This is the Agent Software you needed.

Copy it to the machine where you want to install the agent software.

Now you go ahead preparing the response files needed for the Agent software installation

Prepare a response file

Below are the parameters you must have in the response file to start the agent software installation

EM_INSTALL_TYPE=”AGENT”
OMS_HOST=<Enter Your OMS host name (machine in which you installed the OEM software initially)>
EM_UPLOAD_PORT=7799 <This is the default port>
AGENT_REGISTRATION_PASSWORD=<Give a password of your choice>
AGENT_INSTANCE_HOME=<Absolute path of the directory in which you’re planning to install the agent software>
AGENT_PORT=1832<This is a default port>
ORACLE_HOSTNAME=<The host in which you want to install the agent software>
s_agentHomeName=”agent12gR1″ <This is the default value provided by Oracle>

After entering all the necessary values, you can move to next step i.e. launching the Agent installer.

Deploying the Agents

Unzip the 12.1.0.1.0_AgentCore_226.zip we downloaded in the first step

You will find a file like agentDeploy.sh in the unzipped files.

Now run

./agentDeploy.sh AGENT_BASE_DIR=<The directory in which you want to install the agent software> RESPONSE_FILE=<Absolute path of the agent response file we’ve prepared in the above step>

After the installation is finished, run the below script

$AGNET_HOME/core/12.1.0.1.0/root.sh

and this will windup the installation task.

You can check if the agent is running by giving

$AGNET_HOME/core/12.1.0.1.0/bin/emctl status agent

So you are done with the OEM software installation:  https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-i-basic-prerequisites/ and https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-ii-preparing-the-response-files-and-running-the-installer/

and

Agent installation: https://querycsm.wordpress.com/2012/08/06/deploying-oem-12c-agents-in-silent-mode/

Now, we’ll go ahead adding targets to be monitored to the enterprise manager which actually completes the OEM setup. Unless you add the targets, you can’t see anything about them in the OEM console.

See my next post on how to add targets(I’ll be focusing on how to add Databases, which are main point of concern for any DBA) in silent mode.

regards,

CSM

Installing OEM 12C in silent mode – Part II – Preparing the response files and running the installer

Please read Part-I @ https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-i-basic-prerequisites/ before reading this

Now we’ll see the contents and mandatory fields of the OEM installation response file…
The response file is similar to the ones required by all the Oracle silent mode installation frameworks…
Below are the minimum field required to be filled up apart from the defaults assumed by Oracle…

UNIX_GROUP_NAME= Primary group of Oracle user

ORACLE_MIDDLEWARE_HOME_LOCATION= The base installation directory under which you want to put all your installation components like OMS, Agent, Weblogic etc…

DATABASE_HOSTNAME=machine name/IP address of the Database host where your OEM repository Database is located

LISTENER_PORT=port number of the listener on which your repository database service is running

SERVICENAME_OR_SID= Your repository database service_name/SID

SYS_PASSWORD=password for the sys user

SYSMAN_PASSWORD=password for the sysman user

SYSMAN_CONFIRM_PASSWORD=confirm sysman password

ORACLE_INSTANCE_HOME_LOCATION=it’s usually MIDDLEWARE_HOME/gc_inst but you can give a directory of your choice. This is the location where OEM creates an instance for our current oms installation

MANAGEMENT_TABLESPACE_LOCATION= Location of the OEM’s management tablespace’s data file

CONFIGURATION_DATA_TABLESPACE_LOCATION=Location of the OEM’s configuration tablespace’s data file

JVM_DIAGNOSTICS_TABLESPACE_LOCATION=Location of the OEM’s diagnostics tablespace’s data file

These are like normal datafiles every database uses for its tablespaces.

Now we’re complied to all the prerequisites, prepared our response file; let’s move on to the actual installation…

Download the OEM 12C packages from

http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html

unzip the packages in some staging directory and find a file called “runInstaller”.

now issue the command

runInstaller -silent -responseFile OEM_install.rsp

There’re lot more options available to direct the installation; you can find them @ http://docs.oracle.com/cd/E24628_01/install.121/e24089/install_em_exist_db_slnt.htm

This will start the installer and complete your installation.

Check the installation logs and perform the post installation steps specified by the OEM installer and you are done…

Your OEM is ready…You can access it from https://your_installation_host:7799/em

Keep watching my next post to install agents and add targets to be monitored in silent mode…

I appreciate you feedback and comments…

regards,
CSM

Installing OEM 12C in silent mode – Part I – Basic Prerequisites

Hi Guys,

I was trying to install OEM 12C last week. Installing 12C is nothing special but I wanted to do everything in silent mode. My plan was to automate the installation of OMS, Agents deployment and addition of the targets.

Oracle’s GUI mode installation is a bit easier as Oracle corporation has improved the accessibility very much and reduced the number of screens compared to it’s predecessor! but the silent mode installation is a bit painful even now!!

I’m writing the entire procedure in separate parts as it’s not that readable for the users if I post the entire thing in one post…

Ok, let’s move on…

I assume you had already checked and made your system comply with the prerequisites specified by Oracle

For OMS – http://docs.oracle.com/cd/E25178_01/install.1111/e24089/install_em_exist_db_slnt.htm

For Agent – http://docs.oracle.com/cd/E25054_01/install.1111/e24089/install_agent_usng_rsp.htm

After that you have to install one Database, with partitioning option enabled (obviously an EE database), which will be used by the OEM for its internal storage purposes.

But don’t get panic if you want to install OEM to monitor your targets which are SE/SE1 and you don’t have a EE license. Oracle allows you to run an EE Database, even in your production systems, without any extra license, for OEM storage purposes. All you need is you have to have a proper license for all the targets monitored by the OEM and the OEM itself.

The most important thing is, the OEM installer will abort if the Repository Database already contains SYSMAN schema. And much more dangerous thing is the installer will move forward if you don’t have SYSMAN schema but have others like SYSMAN_MDS, MGMT_VIEW etc…This is dangerous because the installer will not complete all the actions properly because of the existence of these other things…

So you have to make sure you don’t have SYSMAN and other things mentioned above as well…

execute the below commands in your repository Database by logging in as sys user…

drop user SYSMAN cascade;
drop user SYSMAN_MDS cascade;
drop user MGMT_VIEW cascade;
drop user SYSMAN_RO cascade;
drop user SYSMAN_OPSS cascade;
drop user SYSMAN_APM cascade;
drop user SYSMAN_BIP;
drop user CLOUD_SWLIB_USER cascade;
drop user CLOUD_ENGINE_USER cascade;
drop role MGMT_USER;
spool synonyms.lst
select ‘drop ‘|| decode(owner,’PUBLIC’,owner||’ synonym ‘||synonym_name, ‘ synonym ‘||owner||’.’||synonym_name) ||’;’ from dba_synonyms where table_owner in (‘SYSMAN’, ‘SYSMAN_MDS’, ‘MGMT_VIEW’, ‘SYSMAN_BIP’, ‘SYSMAN_APM’, ‘SYSMAN_OPSS’, ‘SYSMAN_RO’) ;
spool off
@synonyms.lst
spool tablespaces.lst
select ‘drop tablespace ‘||tablespace_name||’ including contents and datafiles;’ from dba_tablespaces where tablespace_name like ‘MGMT%’;
spool off
@tablespaces.lst
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME=’Metadata Services’;
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME=’Authorization Policy Manager’;
delete from SCHEMA_VERSION_REGISTRY where COMP_NAME=’Oracle Platform Security Services’;
commit;

Please have a look into the part II for the response file creation and remaining steps…

Part II is @ https://querycsm.wordpress.com/2012/07/31/installing-oem-12c-in-silent-mode-part-ii-preparing-the-response-files-and-running-the-installer/

contd…