Use OEM to run a multi-Linux-server query and in multiple Oracle databases
We’re often required to return information from multiple Oracle databases, whether it’s for audit purposes, check a configuration, view log messages, etc. And it’s a total waste of time logging into each Linux server, set up environment variables for each database, run a SQL script, capture the result to a file, copy the file out of the Linux server, and concatenate all of the results. It would be ideal to have a script that can be run in all Linux servers or in all Oracle databases, whose output can be reviewed at the end in a single step.
You can find a previous tip on this in the following link using PuTTY or Posh-SSH: https://www.garudax.id/posts/pablo-echeverria_oracle-linux-multiabrserverabrquery-activity-6600903630570704896-1vuN
In this tip we provide another way that takes advantage of Oracle Enterprise Manager, you must have it already installed but if you don’t, here is the official documentation on how to install version 12c which is the one we used: https://docs.oracle.com/cd/E25054_01/install.1111/e22624/install_em_exist_db.htm
You will also need a bit of knowledge of Python, Java, and JSON to create your scripts.
First, you need to login into OEM using the URL of the application, with username SYSMAN and your password, so you can download the Command Line Interface by going to Setup > Command Line Interface > EM CLI with Scripting mode (emcliadvancedkit.jar). Follow the instructions to install it in your environment (any OS that is Java enabled), in my case it was downloaded and installed to a Windows Server with Java 1.7 at C:\EMCLI\ directory (Note: version 1.8 doesn’t work due to an incompatibility with SSL certificates):
SET JAVA_HOME=C:\Program Files\Java\jdk1.7.0_80 "%JAVA_HOME%"\bin\java -jar C:\emcliadvancedkit.jar -install_dir=C:\EMCLI\
Once installed, go to the directory you specified since there are two commands you need to run to synchronize with your OEM installation (it will prompt for the password):
cd C:\EMCLI\ emcli sync -url=https://oem.example.com:7802/em -username=SYSMAN -trustall -novalidate emcli setup -url=https://oem.example.com:7802/em -username=SYSMAN -trustall
Now you’re able to enter interactive mode simply executing emcli to enter the prompt:
emcli emcli>
First you need to log in with username SYSMAN (it will prompt for the password) and specify you want JSON output:
emcli>login(username='sysman')
emcli>set_client_property('EMCLI_OUTPUT_TYPE','JSON')
Then you can execute an OS command in all hosts by running the following instructions:
myobj=list(resource='Targets',search='TARGET_TYPE=\'host\'',columns='TARGET_NAME,TARGET_TYPE').out()['data']
mylist=''
for i in myobj:
mydict=dict(i)
mylist=mylist+mydict['TARGET_NAME']+':'+mydict['TARGET_TYPE']+';'
myresult=execute_hostcmd(cmd='hostname',targets=mylist,username='yourusername',password='yourpassword')
f=open('c:\EMCLI\out.txt','w')
f.write(myresult.out())
f.close()
The first line gets all hosts registered in OEM (you can include multiple statements in the ‘search’ parameter to filter it even more), and lines 2-5 converts it to name:type format. Line 7 executes the command ‘hostname’ in the targets, and note you need an OS username and password to authenticate, or you can have the credentials stored in OEM. Lines 8-10 store the result in a local text file. The output is similar to this:
************************************************************************** * Target: linuxserver1:host * Execution Status: Succeeded ************************************************************************** linuxserver1 ************************************************************************** * Target: linuxserver2:host * Execution Status: Succeeded ************************************************************************** linuxserver2
And we can do something similar to execute a SQL command in all Oracle databases by running the following instructions:
myobj=list(resource='Targets',search='TARGET_TYPE=\'oracle_database\'',columns='TARGET_NAME,TARGET_TYPE').out()['data']
mylist=''
for i in myobj:
mydict=dict(i)
mylist=mylist+mydict['TARGET_NAME']+':'+mydict['TARGET_TYPE']+';'
myresult=execute_sql(sql='SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE',targets=mylist,db_username='yourusername',db_password='yourpassword',db_role='normal',host_username='yourusername',host_password='yourpassword')
f=open('c:\EMCLI\out2.txt','w')
f.write(myresult.out())
f.close()
Note you need an OS username, password, database username, password, and role to authenticate, or you can have the credentials stored in OEM. The output is similar to this:
************************************************************************** * Target: ORCL1:oracle_database * Execution Status: Succeeded ************************************************************************** INSTANCE_NAME STATUS DATABASE_STATUS ---------------- ------------ ----------------- ORCL1 OPEN ACTIVE ************************************************************************** * Target: ORCL2:oracle_database * Execution Status: Succeeded ************************************************************************** INSTANCE_NAME STATUS DATABASE_STATUS ---------------- ------------ ----------------- ORCL2 OPEN ACTIVE
It still needs a bit of parsing, but simplified a lot of manual work by logging into every individual server and database.
In case there are errors, they will be stored in myresult.error(). In my case I got an error saying “ERROR: NMO not setuid-root (Unix-only)”, you can find instructions on how to resolve it in the following link: http://dbaworkshop.blogspot.com/2014/02/How-to-solve-the-error-ERROR-NMO-Not-Setuid-root-Unix-only-on-Oracle-Enterprise-Manager-Cloud-Control-while-contacting-the-EM-Agent.html
Finally, emcli allows you to easily manage your environment, for example manage blackouts, run scheduled jobs from a code repository, patching, migration, new database creation, manage agent responsibility during VCS failover, and a whole lot more, you can find the list of commands in the following link: https://docs.oracle.com/cd/E24628_01/em.121/e17786/cli_verb_ref.htm#EMCLI200