Friday, January 24, 2014

Predicate N = N-1 returns rows?

Following a tweet from  "Quiz Q for the day: "select * from T where N = N - 1;" returns rows - how?", I did a small setup to try to answer the question.

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--number

SQL> drop table tt
  2  ;

Table dropped.

SQL> create table tt as select 1/0f N from dual;

Table created.

SQL> desc tt;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 N                                                  BINARY_FLOAT

SQL> select * from tt where N=N-1;

         N
----------
       Inf

SQL>

Tuesday, January 7, 2014

EM12c - Deploy the Latest Database Plugin to the OMS in offline mode

As of the date this was written, the latest EM12c database plugin is 12.1.0.5.0.

If you are a fan of command line utilities over the GUI interfaces (like me), you will love emcli which will help accomplish many operations in EM12c.

The steps would be:
1.       Aquire the latest database plug-in software using Self Update
2.       Deploy the plugin on the management server aka OMS

We will now synchronize emcli with the OMS
[oracle@em12c bin]$ ./emcli sync
Error: Session expired. Run emcli login to establish a session.

Let’s check the status:

[oracle@em12c bin]$ ./emcli status
Oracle Enterprise Manager 12c EMCLI12.1.0.3.0.
Copyright (c) 1996, 2013 Oracle Corporation and/or its affiliates. All rights reserved.

Instance Home          : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli
Verb Jars Home         : null
Status                 : Configured
EMCLI Home             : /u01/app/oracle/Middleware2/oms/bin/.
EMCLI Version          : 12.1.0.3.0
Java Home              : /u01/app/oracle/Middleware2/jdk16/jdk/jre
Java Version           : 1.6.0_43
Log file               : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/.emcli.log
EM URL                 : https://em12c.localdomain:7801/em
EM user                : SYSMAN
Auto login             : false
Trust all certificates : true

[oracle@em12c bin]$ ./emcli login -username=SYSMAN -password=***
Login successful


[oracle@em12c bin]$ ./emcli sync
Synchronized successfully

1)      Download the latest updates catalog in offline mode
Login to EM12c -> Setup -> Extensibility -> Self Update
In the page, select Plugins and Click “Check Updates”

A popup with the link to download will appear:
https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

Now we need to import the master catalog with is: Patch 9348486: master catalog

[oracle@em12c bin]$ ./emcli import_update_catalog -file=/home/oracle/p9348486_112000_Generic.zip -omslocal
Processing catalog for Middleware Profiles and Gold Images
Processing catalog for Agent Software
Processing catalog for Management Connector
Processing catalog for Extensibility Development Kit
Processing update: Extensibility Development Kit - 'Extensibility Development Kit (EDK) is a standalone tool designed to help development of Enterprise Manager extensions (For example: Plug-ins)'
Processing catalog for Plug-in
Processing update: Plug-in - Enterprise Manager for Oracle Utilities provides management and monitoring for the Oracle Utilities area.
Processing update: Plug-in - Enterprise Manager for Oracle Enterprise Taxation and Policy Management provides management and monitoring for the Oracle Enterprise Taxation and Policy Management area.
Processing update: Plug-in - Enterprise Manager for Oracle Exadata provides comprehensive management for Oracle Exadata and related targets such as Database Machine,  etc.
Processing update: Plug-in - Enables cloud self service framework and portal in Oracle Enterprise Manager.
Processing update: Plug-in -
        Enterprise Manager Storage Management Framework provides Snap Clone functionality by leveraging the snapshot and Copy-on-Write (CoW) features of the underlying storage.
 
Processing update: Plug-in - Enterprise Manager for Oracle Siebel consists of monitoring and management functionalities for Oracle Siebel.
Processing update: Plug-in - Enterprise Manager for Fusion Apps consists of monitoring and management for Oracle Fusion and diagnostics in fusion application area.
Processing update: Plug-in - Enterprise Manager for Oracle Consolidation Planning and Chargeback provides metering, chargeback and consolidation planning for various Enterprise Manager targets.
Processing update: Plug-in - Enterprise Manager for Fusion Middleware consists of monitoring and management for Oracle Fusion Middleware and diagnostics in middleware area.
Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.
Processing update: Plug-in - Enterprise Manager for Big Data Appliance  provides comprehensive management for Oracle Big Data Appliance and related targets of Hadoop
Processing update: Plug-in - Enterprise Management Plug-in for Oracle ZFS Storage Appliances
Processing update: Plug-in - Enterprise Manager for Oracle Instant Messaging Server is used for monitoring/managing the Oracle Instant Messaging Server
Processing update: Plug-in - Enterprise Manager for Oracle Virtual Networking
Processing update: Plug-in - Enterprise Manager for Ops Center Infrastructure stack
Processing update: Plug-in - Enterprise Manager for Engineered System Healthcheck provides proactive healthcheck alerts for Oracle Engineered Systems
Processing update: Plug-in - Enterprise Manager for Oracle Audit Vault and Database Firewall (AVDF) provides monitoring and management of AVDF system.
Processing update: Plug-in - Enterprise Manager for Oracle Communications enables management of Oracle Communications products
Processing update: Plug-in - Oracle Application Management Pack for Oracle E-Business Suite consists of System Management and Change Management Feature Sets
Processing catalog for Test Data Management templates
Processing catalog for Oracle Virtual Templates and Oracle Virtual Assemblies
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Functional Version 12.3.0.1.400
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Version 12.3.0.1.376
Processing update: Oracle Virtual Templates and Oracle Virtual Assemblies - Oracle Application Testing Suite Agent Version 12.3.0.1.376
Processing catalog for Informational
Processing catalog for Provisioning Bundle

Successfully uploaded the Self Update catalog to Enterprise Manager. Use the Self Update Console to view and manage updates.
[oracle@em12c bin]$

Now Click on Plug-In Updates -> choose Oracle Database (it will show Available) -> Download
You will be prompted with a message like the below.


You shall be downloading it from the URL provided.
Now you can import it to Enterprise Manager using the below command:

 [oracle@em12c bin]$ ./emcli import_update -omslocal -file=/home/oracle/p17628065_112000_Generic.zip
Processing update: Plug-in - Enterprise Manager for Oracle Database provides comprehensive management for Oracle Database and related targets such as Real Application Clusters, Automatic Storage Management (ASM) etc.
Successfully uploaded the update to Enterprise Manager. Use the Self Update Console to manage this update.

Let’s now check the existing plugins:

[oracle@em12c bin]$ ./emcli list_plugins_on_server
OMS name is em12c.localdomain:4890_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Engineered System Healthchecks        oracle.em.sehc                12.1.0.3.0
Oracle Database                              oracle.sysman.db              12.1.0.4.0
Oracle Fusion Middleware                     oracle.sysman.emas            12.1.0.4.0
Oracle Consolidation Planning and Chargeback oracle.sysman.emct            12.1.0.4.0
Oracle MOS (My Oracle Support)               oracle.sysman.mos             12.1.0.5.0
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.2.0
Oracle Cloud Application                     oracle.sysman.ssa             12.1.0.6.0
Oracle Virtualization                        oracle.sysman.vt              12.1.0.5.0
Oracle Exadata                               oracle.sysman.xa              12.1.0.4.0
Oracle Utilities                             oracle.tugbu.outl             12.1.0.2.0




2)      Deployment
Login to EM12c -> Setup -> Extensibility -> Go to Plugins

Expand the option Databases -> Oracle Databases -> Deploy on -> Choose Management Server
 Input the password for SYS (on the repository database of course)


Wait for <5 minutes for the prerequisites to complete:


Check the box which asks if you have backed up the repository and configuration of first management server and click Deploy:

 As OMS will be taken down during deployment of plug-in, you can monitor the progress using

./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 3 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em12c.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is unlocked.
OMS Console is unlocked.
Active CA ID: 1
Console URL: https://em12c.localdomain:7801/em
Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em12c.localdomain
Admin Server HTTPS Port: 7102

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: em12c.localdomain
WebTier is Down

Unable to deploy or undeploy the plug-in. Oracle Management Server is down possibly because the following plug-ins are being deployed or undeployed from it. Try again after some time.
Plug-in Deployment/Undeployment Status

Destination          : Management Server - em12c.localdomain:4890_Management_Service
Plug-in Name         : Oracle Database                        
Version              : 12.1.0.5.0                             
ID                   : oracle.sysman.db                       
Content              : Plug-in                                 
Action               : Deployment                             
Status               : Deploying                              
Steps Info:
---------------------------------------- ------------------------- ------------------------- ----------
Step                                     Start Time                End Time                  Status    
---------------------------------------- ------------------------- ------------------------- ----------
Submit job for deployment                1/7/14 7:20:00 AM EST     1/7/14 7:20:00 AM EST     Success   

Initialize                               1/7/14 7:20:08 AM EST     1/7/14 7:20:26 AM EST     Success   

Install software                         1/7/14 7:20:28 AM EST     1/7/14 7:20:30 AM EST     Success   

Validate plug-in home                    1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Perform custom preconfiguration          1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Check mandatory patches                  1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Generate metadata SQL                    1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Preconfigure Management Repository       1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Preregister DLF                          1/7/14 7:20:34 AM EST     1/7/14 7:20:34 AM EST     Success   

Stop management server                   1/7/14 7:20:39 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------

What interest us are the actions following the stop of management server. We need to rerun the above command; we cannot see the progress as we do with tail –f for example.


Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     N/A                       Running   

Configure middle tier                    1/7/14 7:22:23 AM EST     N/A                       Running   

Register DLF                             1/7/14 7:22:23 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------

…More details on the progress…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     N/A                       Running   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

---------------------------------------- ------------------------- ------------------------- ----------

…More details on the progress…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     1/7/14 7:46:15 AM EST     Success   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

OPSS jazn policy migration               1/7/14 7:31:30 AM EST     1/7/14 7:31:55 AM EST     Success    

Register metadata                        1/7/14 7:46:16 AM EST     1/7/14 7:49:53 AM EST     Success   

Perform custom postconfiguration         1/7/14 7:49:53 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------

…And the last step of starting management server…

Stop management server                   1/7/14 7:20:39 AM EST     1/7/14 7:22:19 AM EST     Success   

Configure Management Repository          1/7/14 7:22:21 AM EST     1/7/14 7:46:15 AM EST     Success   

Configure middle tier                    1/7/14 7:22:23 AM EST     1/7/14 7:28:33 AM EST     Success   

Register DLF                             1/7/14 7:22:23 AM EST     1/7/14 7:29:25 AM EST     Success   

OPSS jazn policy migration               1/7/14 7:31:30 AM EST     1/7/14 7:31:55 AM EST     Success   

Register metadata                        1/7/14 7:46:16 AM EST     1/7/14 7:49:53 AM EST     Success   

Perform custom postconfiguration         1/7/14 7:49:53 AM EST     1/7/14 7:49:55 AM EST     Success   

Update inventory                         1/7/14 7:49:55 AM EST     1/7/14 7:49:59 AM EST     Success   

Start management server                  1/7/14 7:49:59 AM EST     N/A                       Running   

---------------------------------------- ------------------------- ------------------------- ----------
The deployment completes after approximately 40 minutes. The OMS is up again.

[oracle@em12c bin]$ ./emctl status oms -details
Oracle Enterprise Manager Cloud Control 12c Release 3 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em12c.localdomain
HTTP Console Port          : 7789
HTTPS Console Port         : 7801
HTTP Upload Port           : 4890
HTTPS Upload Port          : 4901
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is unlocked.
OMS Console is unlocked.
Active CA ID: 1
Console URL: https://em12c.localdomain:7801/em
Upload URL: https://em12c.localdomain:4901/empbs/upload

WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em12c.localdomain
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Managed Server Information
Managed Server Instance Name: EMGC_OMS1
Managed Server Instance Host: em12c.localdomain
WebTier is Up
Oracle Management Server is Up

We now list the plugins on server

[oracle@em12c bin]$ ./emcli list_plugins_on_server
OMS name is em12c.localdomain:4890_Management_Service
Plug-in Name                                 Plugin-id                     Version [revision]

Oracle Engineered System Healthchecks        oracle.em.sehc                12.1.0.3.0
Oracle Database                              oracle.sysman.db              12.1.0.5.0
Oracle Fusion Middleware                     oracle.sysman.emas            12.1.0.4.0
Oracle Consolidation Planning and Chargeback oracle.sysman.emct            12.1.0.4.0
Oracle MOS (My Oracle Support)               oracle.sysman.mos             12.1.0.5.0
Oracle Storage Management Framework          oracle.sysman.smf             12.1.0.2.0
Oracle Cloud Application                     oracle.sysman.ssa             12.1.0.6.0
Oracle Virtualization                        oracle.sysman.vt              12.1.0.5.0
Oracle Exadata                               oracle.sysman.xa              12.1.0.4.0
Oracle Utilities                             oracle.tugbu.outl             12.1.0.2.0

We can see that Oracle Database shows correctly it is at version 12.1.0.5

In EM, the same information is visible


Reference:
How to Deploy the Latest Database Plugin to the OMS and the Agent in 12C Cloud Control (Doc ID 1388143.1)