Oracle EMCLI-II

In this short post, I am going to show you how to submit your Oracle jobs from the library and how to monitor your Oracle jobs from the command line. By doing that you can create your automation instead of using Oracle Enterprise Manager web interface.

Before issuing any command below, first you need to authorize Oracle OMS and sync your environment.

Submitting Oracle Jobs from the library.

gokay@ankara:~$ emcli create_job_from_library -owner=SYSMAN -lib_job_name=T24_EXA03_BACKUP -name=T24_EXA03_BACKUP_TEST

After submit the job you should get similar output if everything is OK.

Creation of job "T24_EXA03_BACKUP_TEST" was successful.

Monitor job status:

You can monitor submitted job status.

gokay@ankara:~$ emcli get_jobs -noheader -name=T24_EXA03_BACKUP_TEST

Getting Execution ID:

Sometimes even your Oracle job finished successfully, Your job may not be finished as an expected. In order to overcome this situation, we can also get the execution detail for the finished job. First thing is the getting the execution id for the submitted job. (T24_EXA03_BACKUP_TEST in our case)

emcli get_jobs -noheader -name=T24_EXA03_BACKUP_TEST | awk  '{print $4}')

Getting Execution detail:

After possession of execution id. we can get the execution detail.

gokay@ankara:~$ emcli get_job_execution_detail -execution=6E6A0527A3AA7B89E0532812200A13E8  -showOutput -xml

It prints out  all the job logs in xml format.   As my sample job is RMAN backup, it gives me similar output such this. “Recovery Manager complete”

Executing a Job on the specific target:

In order to submit job on a specific target we need to create oracle property file.

Creating a property file.

Create a file sample.job and add the line below.

target_list=t2401int:rac_database

Submitting the job specific  target:

gokay@ankara:~$ emcli create_job_from_library -owner=SYSMAN -lib_job_name=T24_EXA03_BACKUP -name=T24_EXA_BACKUP_TEST -appendtargets -input_file=property_file:sample.job

 

Finding ORA and SP2 Errors with grep

In this short post, I will share simple grep style Regular Expression to find Oracle ORA-XXXXX and SqlPlus(SP2-XXXX) errors.

Connectionless Session with sqlplus:

#!/bin/bash
#Set oracle environment variables in the .bash_profile
. ~/.bash_profile 
sqlplus -s /nolog <<EOF>test.ora
connect <username>/<password>@<connection identifier> 
SELECT ID || ' ' || NAME || ' ' || SNAME || ' ' || ' ' || PHONE || ' ' || CITY ||  ' ' FROM USERS; 
quit 
EOF

Finding ORA Errors:

grep '^.*ORA-[0-9]\{5\}:' test.ora

Finding SP2 Errors:

grep '^.*SP2-[0-9]\{4\}:' test.ora

 

Oracle RMAN backup through emcli

In previous post, I mentioned setting of the emcli and resolving of SSL problem. In this post, I will show you how we simply backup using RMAN through emcli. It is necessary to login  successfully to OMS to backup your database. And all necessary privileges should be given to user who logins to OMS.

Creating a Property file:

First step is creating property file. Property file is a file which stores all necessary configuration information related to your database environment. You can see sample property file which belongs  to my environment. You can also create template file with emcli describe_job command.

gokay@ankara:~$ emcli describe_job -name=myJob

Creating Property file manually:

In this configuration, It is used named credentials for the security reasons, instead using user name and clear text password in the file.

cat << EOF > /tmp/RMAN_BACKUP_JOB_1.prop
name=RMAN_BACKUP_JOB_1
type=RMANScript
description=Test RMAN backup
owner=SYSMAN
#kind=library
kind=active
target_list=T2421DEV:rac_database
cred.defaultDBCred.T2421DEV:rac_database=NAMED:SYSMAN:RMANBKP_TEST_ENV
cred.defaultHostCred.T2421DEV:rac_database=NAMED:SYSMAN:EXA032_T24TST
# Description: (Required) Enter RMAN commands or a fully qualified script name on the remote hosts, for example, "@script.rcv"
variable.rman_perl_script=<<MULTILINEVALUE
run {
allocate channel oem_backup_disk1 type disk format '/zfsdev/Oracle_DBbackup/rman_bkp/ExaData_TST/T2421DEV/%d_%U_%D%M%Y';
backup spfile format '/zfsdev/Oracle_DBbackup/rman_bkp/ExaData_TST/T2421DEV/%d_%U_%D%M%Y_spfile';
release channel oem_backup_disk1;
}
MULTILINEVALUE
# Description: (Optional) Notify the job owner when a selected state occurs
# Allowed Values:  SCHEDULED, RUNNING, ACTION_REQUIRED, SUSPENDED, SUCCEEDED, PROBLEMS
notification=SUCCEEDED,PROBLEMS
EOF

Instead of creating a property file you can also execute existing library job.

gokay@ankara:~$ ./emcli -owner=SYSMAN create_job_from_library -lib_job_name="RMAN_BACKUP_JOB_1" -name="RMAN_BACKUP_JOB_20180425"

Checking Job Status:

gokay@ankara:~$ ./emcli get_jobs -noheader -name=RMAN_BACKUP_JOB_20180425 | awk  '{print $1 " "  $5 " "  $6 " " $7 " " $8 " " $10}'

Note: Before executing any emcli commands, do not forget to sync the OMS with emcli. Otherwise, you will get an error. After login successfully sync with below command.

gokay@ankara:~$ /.emcli sync
Synchronized successfully

 

 

 

 

 

 

[Solved]Connection to the current OMS could not be established

Hello Folks,

The last week one of our customer wanted to automatize their Oracle backup systems. As an Oracle environment, customer has Oracle 12c. Luckily Oracle 12c designed for the Cloud. Oracle Enterprise Manager is the graphical interface for managing all of  Oracle database environment. Oracle Enterprise Manager has also command line interface which we need to automatize their systems. emcli– Enterprise Manager Command line interface is the tool which enables users to access Enterprise Manager functionality through a command-line. It has a two modes of operation. One is interactive mode and other one is Script Mode. In this post, It will be used interactive mode.

Installation of the emcli for interactive mode:

emcli is a jar package that you need to download, install and setup. You can download the jar package from your OMS.

wget --no-check-certificate
https://<your_em_host:port>/em/public_lib_download/emcli/kit/emclikit.jar

Before install it, do not forget to set JAVA_HOME environment variable.

$export JAVA_HOME=/your/Java/path
#for me
$export JAVA_HOME=/app/jdk1.8.0_162

Installation

$JAVA_HOME/bin/java -jar emclikit.jar -install_dir=<em_cli_home_dir>
#for me
$JAVA_HOME/bin/java -jar emclikit.jar -install_dir=/app/emcli

Setting up emcli

Normally Setting up emcli is very easy step. But as always Murphy on the scene :). I got an error during the setup process.

gokay@ankara:~$ ./emcli setup -url=https://10.10.18.40:7799/em -username=auto -password="pass"  -dir=/app/emcli
Oracle Enterprise Manager 12c Release 5.
Copyright (c) 1996, 2015 Oracle Corporation and/or its affiliates. All rights reserved.

Error: Connection to the current OMS could not be established. Check the log files for further details.
Log file location is : /app/emcli/.emcli/.emcli.log

/app/emcli/.emcli/.emcli.log details

Apr 16, 2018 4:31:01 PM oracle.sysman.emCLI.verb.SetupVerb execute
SEVERE: HttpConnectionException
oracle.sysman.emCLI.omsbrowser.HttpConnectionException:
        at oracle.sysman.emCLI.omsbrowser.OMSBrowser.submitCommand(OMSBrowser.java:1319)
        at oracle.sysman.emCLI.omsbrowser.OMSBrowser.getPageInternal(OMSBrowser.java:748)
        at oracle.sysman.emCLI.omsbrowser.OMSBrowser.getPage(OMSBrowser.java:666)
        at oracle.sysman.emCLI.verb.LogoutVerb.doLogout(LogoutVerb.java:263)
        at oracle.sysman.emCLI.verb.SetupVerb.execute(SetupVerb.java:810)
        at oracle.sysman.emSDK.emCLI.CLIController.execute(CLIController.java:306)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at oracle.sysman.emCLI.StandAloneLaunchHandler.invoke(StandAloneLaunchHandler.java:363)
        at oracle.sysman.emCLI.StandAloneLaunchHandler.launch(StandAloneLaunchHandler.java:262)
        at oracle.sysman.emSDK.emCLI.CLIController.launch(CLIController.java:194)
        at oracle.sysman.emSDK.emCLI.CLIController.main(CLIController.java:184)
Caused by: javax.net.ssl.SSLException: Received fatal alert: close_notify
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:208)
        at sun.security.ssl.Alerts.getSSLException(Alerts.java:154)
        at sun.security.ssl.SSLSocketImpl.recvAlert(SSLSocketImpl.java:2033)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1135)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1385)
        at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:757)
        at sun.security.ssl.AppOutputStream.write(AppOutputStream.java:123)
        at java.io.ByteArrayOutputStream.writeTo(ByteArrayOutputStream.java:167)
        at HTTPClient.HTTPConnection.sendRequest(HTTPConnection.java:3461)
        at HTTPClient.HTTPConnection.handleRequest(HTTPConnection.java:3373)
        at HTTPClient.HTTPConnection$10.run(HTTPConnection.java:3124)
        at HTTPClient.HTTPConnection$10.run(HTTPConnection.java:3115)
        at HTTPClient.HttpClientConfiguration.doAction(HttpClientConfiguration.java:729)
        at HTTPClient.HTTPConnection.doAction(HTTPConnection.java:5560)
        at HTTPClient.HTTPConnection.setupRequest(HTTPConnection.java:3115)
        at HTTPClient.HTTPConnection.Post(HTTPConnection.java:1119)
        at HTTPClient.HTTPConnection.Post(HTTPConnection.java:1073)
        at oracle.sysman.emCLI.omsbrowser.OMSBrowser.submitCommand(OMSBrowser.java:1298)

        ... 13 more

I suspected  SSL, which may be the SSL version problem. As Java 8 uses TLS1v2 as default. To  confirm that  I checked with openSSL tool.

$ openssl s_client -showcerts -connect 10.10.18.40:7799 -tls1_2 -debug

...(omitted)
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2


#I also tried with the tls1v1

openssl s_client -showcerts -connect 10.10.18.40:7799 -tls1_1 -debug

New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.1


I checked on Google; Oracle OMS uses TLSv1. To use TLS1v2 you need to patch it. As a workaround, I changed to my Java version from Java 8  to Java 7.

Set your JAVA_HOME for Java 7

$export JAVA_HOME=/app/jdk1.7.0_XXX

Installation of emcli with Java 7

/home/appadmin/jdk1.7.0_80/bin/java -jar emclikit.jar client -install_dir=/app/emcli
Oracle Enterprise Manager 12c Release 5.
Copyright (c) 2012, 2015 Oracle Corporation.  All rights reserved.

EM CLI client-side install completed successfully.
Execute "emcli help setup" from the EM CLI home (the directory where you have installed EM CLI) for further instructions.

Setting of emcli with Java 7.

Do not forget to add -novalidate option.

[appadmin@sr02lautomic01 emcli]$ ./emcli setup -url=https://10.10.18.40:7799/em -username=auto -password="pass"  -dir=/app/emcli  -novalidate
Oracle Enterprise Manager 12c Release 5.
Copyright (c) 1996, 2015 Oracle Corporation and/or its affiliates. All rights reserved.

After changed the Java version, It worked. You can ask Oracle for the patch number to use more secure version of the SSL version. Next post will be about taking a backup of the Oracle by using RMAN script with emcli tool.