EM Express versus EM Cloud Control

Recently I was asked the question “What is the real difference between EM Cloud Control [NOTE: I’ll refer to this as EM12c through the rest of this post] and EM Database Express in 12c?” It was (for me) a pretty easy question to answer, but I wanted to provide the questioner with a link to the place in the Enterprise Manager documentation where it covers that in detail. Somewhat to my surprise, I wasn’t able to find such a link – well, not quickly anyway. I think the reason for that is the documentation for EM Express (as it’s more commonly abbreviated to) falls under the database documentation which is owned by a different group in Oracle than the Enterprise Manager documentation. Well, that’s my speculation anyway. It may just be there in the documentation and I couldn’t find it in my quick search. :) Be that as it may, if I couldn’t find it in a quick search then I suspect others would have the same issue, so I thought it was worthwhile to bash out a quick post on the subject. And of course, what was originally going to be a quick post turned into something much bigger, so if you want, use the links below to move through the post:

What is EM Express?
What is EM12c?
What are the Differences Between the Two?


What is EM Express?

Before I start on what the differences are, it’s probably worthwhile to spend a little time explaining what EM Express actually is. The first thing to note is what it replaces, and that’s the Database Control product. Database Control is no longer available as of Oracle Database 12c. In its place, the tool to monitor a single database is EM Express. EM Express – or to give it its full name, Oracle Enterprise Manager Database Express 12c (now you can see why it’s normally called just EM Express!) – is an integrated GUI management tool for database administration of a single Oracle Database 12c database. Let’s explain that in a bit more detail.

Integrated Installation

EM Express is preconfigured and installed when the database kernel is installed, so when you install your ORACLE_HOME, you have also installed EM Express. If you have used DBCA to create a database, one of the questions you will be asked is whether you want to manage this database with EM Express or EM12c. You can actually select both checkboxes, though I found the last time I did that (admittedly quite some time ago when Oracle Database 12c was relatively new) that the installer got a tad confused by selecting both and it was better to choose one and then set up the other later. EM Express runs inside the database and has no extra middleware components (this is one area of differentiation between EM Express and the earlier Database Control product). EM Express leverages the XDB server inside the database. It support both single instance and Real Application Clusters (RAC) databases.

Small Footprint

EM Express has a fairly small footprint in terms of disk storage, memory and CPU usage. The disk storage added to the kernel by EM Express is only around 20 Mb. It requires minimal CPU and memory – in fact, none until you actually connect to it. All the UI rendering is done in the browser being used, reducing the footprint even more, and the database server is only used to run the SQL needed to retrieve the information being displayed in the GUI.

Comprehensive Administration

OK, comprehensive may be optimistic. :) This is one area where the tool doesn’t quite measure up as yet compared to Database Control, in that EM Express provides only basic administration support for configuration management, storage management, and security management. No doubt as new releases of the tool come out, this coverage will expand. Currently, the tool provides the following functionality:

  • Configuration Management – initialization parameters, memory, database feature usage, and current database properties
  • Storage Management – tablespaces, undo management, redo log groups, archive logs, and control files
  • Security Management – users, roles, and profiles
  • Performance Management – Performance Hub and SQL Tuning Advisor

One area where EM Express far outstrips Database Control (and in fact, even surpasses similar areas in EM12c) is the performance side. EM Express has quite advanced performance diagnostics and tuning capabilities. However, you do need to be licensed for the Diagnostics and Tuning packs to use these pages.

You can find a little bit more on managing Oracle Database 12c with EM Express in my presentation from the APAC OTN Tour back in 2013, located here.


What is Enterprise Manager Cloud Control 12c?

OK, so that’s what EM Express handles. Now let’s have a quick overview of the EM12c product.

To start with, EM12c is more of a product line or product family than a single product, but it still allows you to manage your entire Oracle infrastructure from a single pane of glass, from application to disk. It even allows you to manage your non-Oracle infrastructure, with a variety of plugins and connectors to manage everything from non-Oracle databases (like SQL Server and DB2) to non-Oracle middleware (like WebSphere, Microsoft Active Directory and JBoss Application Server) and so on. And if there’s something it doesn’t yet have a plugin or connector for, you can use the Extensibility capabilities to write your own.

If you look at the key capabilities of EM12c, it really boils down to three main areas:

  • Cloud Lifecycle Management – EM12c helps enterprises discover their IT assets and plan their cloud by providing consolidation recommendations, and has a guided cloud setup wizard to help you define services such as Infrastructure as a Service, Middleware as a Service, and Database as a Service. With later releases, you also have Schema as a Service, Pluggable Database as a Service, Testing as a Service, and Snap Clone functionality. It also includes an out of the box self service portal capability, along with API’s that can be used to create your own self service portal. Finally, it has chargeback capability that allows you to provide business users a comprehensive report of how much computing resources they are using, and even to invoice them for the costs of those resources if you want to.
  • Complete Application to Disk Management – EM12c can be used to monitor and manage every layer of your infrastructure from the application all the way down to the storage, including:
    • Application Management – EM12c provides application management solution for packaged, third party and custom applications, including E-Business Suite, Siebel, PeopeSoft, JD Edwards, Fusion Apps, and Java or SOA based custom and third party apps.
    • Middleware Management – EM12c also manages the many Fusion Middleware components, such as WebLogic Server, SOA Suits, Coherence, Identity Management, Business Intelligence, Glass Fish, Jolt and WebCenter
    • Databases Management – this is the area EM12c has been well known for over many years. Indeed, the first version of Enterprise Manager I ever saw, the 0.76 beta release, already contained a lot of database management capabilities. Obviously, those have all expanded over the years as the database capabilities also have, but now includes additional functionality on top of the native database capabilities in database lifecycle management areas like automating mass database upgrades and patching.
    • Hardware and Virtualization Management – Traditionally, Enterprise Manager has been focused on software management. EM12c expands that to provide management capabilities like provisioning, patching, monitoring, administration and configuration management for both hardware and virtualization layers too. This is particularly true in the case of Oracle’s Sun hardware line.
    • Heterogeneous Management – Even though Oracle’s footprint has been expanding into so many different areas over the years, there are still going to be some non-Oracle products at most customer sites that need managing as well. Oracle has a number of plugins and management connectors that can be used to monitor and manage these third-party products. EM12c also includes an Extensibility Development Kit (EDK) to allow you to write your own management plugins. Many of these are available for other customers to use via the Extensibility Exchange.
    • Engineered Systems Management – EM12c is tightly integrated with a range of Oracle’s Engineered Systems, such as Exadata, Exalogic, Exalytics, SuperCluster, Virtual Compute Appliance (VCA), Big Data Appliance, and Oracle Database Appliance (ODA). That list just keeps expanding as more engineered systems are released, so customers can take advantage of integrated hardware, software and management capabilities that are engineered together to form a complete solution.
    • Application Quality Management – EM’s AQM products provide an end-to-end testing solution, including Application Testing Suite (OATS) which has test management, functional testing and load testing capabilities, Application Replay and Real Application Testing to provide production-scale testing of the application and database infrastructure, and test data management capabilities like data masking and subsetting
    • Application Performance Management – APM delivers business driven application management with end to end monitoring that includes user experience management (which includes both real user monitoring via RUEI and synthetic transaction monitoring via SLM beacons), business transaction management (which allows you to monitor and trace transactions from a business perspective), Java and database monitoring and diagnostics, and application performance analytics.
  • Enterprise-grade management – Not only is it important that you have the ability to manage your Oracle infrastructure with EM12c, but it’s also important that EM12c itself can support mission critical workloads. Way back when it really didn’t matter most of the time if your management tool went down every so often, but now people are realizing it’s as important to have mission critical management for your mission critical workloads. It’s not unusual to find Enterprise Manager installations protected by RAC databases with Data Guard as well, along with software load balancers and the whole enchilada. EM12c has been thoroughly tested for scalability (see here for one of my very early posts on this, and just recently we saw that very same environment hit 2 million targets!). EM12c’s Self Update framework ensures you can easily incorporate management of the latest and greatest products from Oracle. And in addition, the security model introduced with EM12c allows tightly controlled access to the EM12c product itself. So all of these points allow you to be sure EM12c can manage the needs of even the largest environments


So What are the Differences Between the Two?

Now you know what the two products are capable of, you can probably pick out some of the differences. :) But let’s be 100% clear on these.

The most obvious difference is that EM Express is a management tool that manages a single database, while EM12c manages your entire Oracle (and non-Oracle) data center. If all you are needing to manage is a few Oracle databases, then EM Express will be more than capable of meeting your needs. You will have to connect to each database separately to manage it though. However, as soon as you need to manage more than a few databases, or you want to manage more than just databases (i.e. you want to manage middleware, apps and so on), EM12c is your tool of choice. We have customers with literally thousands of database targets they need to manage. Try doing that with EM Express! :)

The second difference is that EM Express is an agentless architecture. Everything you need to connect to EM Express is built into the database kernel. You just need to point a browser to the correct page, and away you go. EM12c, on the other hand, uses an agent to both discover and then manage targets. For each host that you add to an EM12c environment, you add an agent to that host and it then discovers and promotes all the targets (such as databases, listeners, WLS environments and so on) that are on that host. When you need to perform a task on those targets (such as shutting down a database), EM12c communicates with the agent on the host containing that database and it sends the message to the database to shut down. If you think about it, using the agents like this is really the only way to ensure a scalable solution in large environments.

The third difference is functionality. As I mentioned above, EM Express provides a specific set of functionality, and if you want more you need to step outside of EM Express to either a tool like SQL Developer (I haven’t touched on that here because the article would be even longer, but if you want more on that go and visit Jeff Smith’s site for more details) or EM12c. Just a couple of examples – metric extensions, Data Guard management, and chargeback are all only available from EM12c rather than EM Express.

The fourth difference is security. To log in to EM Express, you need to be a user that has the DBA, EM_EXPRESS_BASIC or EM_EXPRESS_ALL roles. The EM_EXPRESS_BASIC role provides read-only access to EM Express, so users with that role can view the UI but not make any changes. This role also includes the SELECT_CATALOG_ROLE role. The EM_EXPRESS_ALL role obviously grants a lot more access. :) It has full read / write access to all EM Express features, and includes the EM_EXPRESS_BASIC role. Compare that to the security model for EM12c, where there are quite a number of roles and over 100 fine-grained privileges, and you can see that EM12c has a much more granular security model.

There are probably other more minor differences between the products that I haven’t covered here, but to my mind these are the main differences. Hope that helps explain the differences between the two!

APEX 5.0 Rollout

apexLast month there was a frenzy of activity when APEX 5.0 was released. I had been having a dabble with the Early Adopter for a while, but I felt the need to do a local install.

The only slight issue I had was with static files and that was down to me not RTFMing properly. :) Patrick Wolf wrote about this issue recently here.

Having not had any problems while I was playing with APEX 5.0, I started the task of upgrading all the installations at work. We don’t do any major development, just basic CRUD screens and interactive reports, so it wasn’t too high profile a task. Anyway, the upgrades went smoothly and everything is running on APEX 5.0 now. Happy days! :)

Of course, if you are doing some complicated stuff that is pivotal to your business, you probably need to be a bit more meticulous about your planning and testing than I was, but it’s pretty good news that of the 20+ installations, none had any upgrade problems. :)

I’ve played around with ORDS 3.0 before the GA release.

We currently use the Oracle HTTP Server to front our stuff for historic reasons. I guess the next move will be to implement ORDS, but I’m not sure when that will happen…

Cheers

Tim…

 


APEX 5.0 Rollout was first posted on May 22, 2015 at 8:09 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Writing Tips : How do I publicise my writing?

writingThis is quite a touchy subject for me and I’m maybe not the best person to ask.

On the one hand, I feel you should grow an audience in an organic manner. If your content is good, they will find you.

On the other hand, I’ve been doing this for so long it is relatively easy for me to get heard. If I was starting today and producing the same type of content, would you have even heard about me or would I be lost in the noise of a billion bloggers?

Here are some things I would suggest:

  • Make the title relevant. People will see it in their RSS feed or on social media and they will make a decision about whether to read it based on only that. Catchy is good, but relevant is more important!
  • Find out the blog aggregators that are available for your subject matter (OraNA for Oracle) and submit your RSS feed to them.
  • Make sure links to your RSS feeds are visible and working. RSS is not anywhere near as popular as it was, but every little helps.
  • When you write something new, post a link to it on social media. You will often have different followers on different platforms, so don’t worry about posting the same link on Twitter, Facebook and Google Plus etc. If someone decides to follow you on all networks, then it’s their fault if they feel spammed. :)
  • Put some form of share buttons on your blog. Companies like ShareThis and AddThis make it really easy. Let fans of your work publicise it for you.
  • Ignore Search Engine Optimisation (SEO) companies. The search engines change their indexing criteria constantly. What SEO companies advise today will be detrimental to your search placing tomorrow. I’ve been doing this for 15 years. Believe me, SEO is a scam! Write good content and people will find you!

Above all, be patient. It takes time and consistency to build an audience. Two blog posts are not going to make you famous, unless you’ve done something really interesting or really naughty! :)

Cheers

Tim…


Writing Tips : How do I publicise my writing? was first posted on May 22, 2015 at 6:43 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

EM12c : Login to GUI with the correct password causes authentication failure

So the other day I was trying to log in to my EM12c R4 environment with the SSA_ADMINISTRATOR user, and I got the error:

“Authentication failed. If problem persists, contact your system administrator”

I was quite sure that the password that I had was correct, so I tried with the SYSMAN user and had the same error. I still wanted to verify that I had the correct password , so I tried with the SYSMAN user to log in to the repository database, and was successful, so I know something was wrong there.


SQL> connect sysman/
Enter password:
Connected.

So I went to the<gc_inst>/em/EMGC_OMS1/sysman/log/emoms.log and saw the following error


2015-05-18 21:22:06,103 [[ACTIVE] ExecuteThread: '15' for queue: 'weblogic.kernel.Default (self-tuning)'] ERROR audit.AuditManager auditLog.368 - Could not Log audit data, Error:java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
ORA-06512: at &quot;SYSMAN.MGMT_AUDIT&quot;, line 492
ORA-06512: at &quot;SYSMAN.MGMT_AUDIT&quot;, line 406
ORA-06512: at line 1

Which led me to believe that the JOB_QUEUE_PROCESSES was set to 0, but it wasn’t the case, since it was set to 50. Though, this is actually an incorrect limit, so I bumped it up to 1000 and tried to rerun the EM12c repository DBMS Scheduler jobs as per the documentation in 1498456.1:


SQL&gt; show parameter JOB_QUEUE_PROCESSES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 50
SQL&gt; alter system set JOB_QUEUE_PROCESSES=1000 scope = both;

System altered.

SQL&gt; show parameter both
SQL&gt; show parameter job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL&gt; connect / as sysdba
Connected.
SQL&gt; alter system set job_queue_processes = 0;

System altered.

SQL&gt; connect sysman/alyarog1605
Connected.
SQL&gt; exec emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL&gt; exec gc_interval_partition_mgr.partition_maintenance;

PL/SQL procedure successfully completed.

SQL&gt; @$OMS_HOME/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql SYSMAN
old 11: AND owner = upper('&amp;RECOMPILE_REPOS_USER')
new 11: AND owner = upper('SYSMAN')
old 26: dbms_utility.compile_schema(upper('&amp;RECOMPILE_REPOS_USER'),FALSE);
new 26: dbms_utility.compile_schema(upper('SYSMAN'),FALSE);
old 41: WHERE owner = upper('&amp;RECOMPILE_REPOS_USER')
new 41: WHERE owner = upper('SYSMAN')
old 84: AND owner = upper('&amp;RECOMPILE_REPOS_USER')
new 84: AND owner = upper('SYSMAN')
old 104: AND ds.table_owner = upper('&amp;RECOMPILE_REPOS_USER')
new 104: AND ds.table_owner = upper('SYSMAN')

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

SQL&gt; connect / as sysdba
Connected.
SQL&gt; alter system set job_queue_processes = 1000;

System altered.

SQL&gt; connect sysman/
Enter password:
Connected.
SQL&gt; exec emd_maintenance.submit_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

After this I bounced the OMS, but still kept getting the same error. And though it fixed the scheduler jobs, I was now seeing the following error in the emoms.log:


2015-05-18 22:29:09,573 [[ACTIVE] ExecuteThread: '15' for queue: 'weblogic.kernel.Default (self-tuning)'] WARN auth.EMRepLoginFilter doFilter.450 - InvalidEMUserException caught in EMRepLoginFilter: Failed to login using repository authentication for user: SSA_ADMIN
oracle.sysman.emSDK.sec.auth.InvalidEMUserException: Failed to login using repository authentication for user: SSA_ADMIN

So what I did was an update to the SYSMAN.MGMT_AUDIT_MASTER table and ran the procedure MGMT_AUDIT_ADMIN.ADD_AUDIT_PARTITION as was stated in document id 1493151.1:


oracle $ sqlplus

&amp;nbsp;

Enter user-name: sysman
Enter password:

SQL&gt; update mgmt_audit_master set prepopulate_days=5 where prepopulate_days is null;

1 rows updated.

SQL&gt; select count(1) from mgmt_audit_master where prepopulate_days is null;

COUNT(1)
----------
0

SQL&gt; exec mgmt_audit_admin.add_audit_partition;

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

Once I did this, I was able to login with all my EM12c administrators without any issues:


oracle@em12cr4.localdomain [emrep] /home/oracle
oracle $ emcli login -username=ssa_admin
Enter password

Login successful

Conclusion

Even though the JOB_QUEUE_PROCESSES were not set to 0, it was the cause that it was failing, as it was a low value for this parameter. Thus, be careful when setting up this parameter, be sure to follow the latest installation guidelines.

Note– This was originally published on rene-ace.com

Fixing Super LOV in Universal Theme

When you migrate to APEX 5.0 and the Universal Theme you might see that some plugins are not behaving correctly anymore. In this post I'll discuss the Enkitec Modal LOV plugin.

When I ran the plugin in my APEX 5.0 app with the Universal Theme it looked like this:


There's too much space in the search bar and the close button is not showing up with an icon.

Here're the steps I did to fix it. First you need to download the css file of the style you use and the js file from the plugin in Shared Components. I use the smoothness.css style most of the times, so I'll use that as an example.

To fix the close icon, add !important to the png:

.ek-ml .ui-state-default .ui-icon {
background-image: url(ui-icons_888888_256x240.png) !important;
}

Note: you can do that for all those png on line 467 till 489.

To fix the height, add following css to smoothness.css:

.superlov-button-container {
  height:50px;
}

And finally in enkitec_modal_lov.min.js change the height of the searchContainer from a dynamic height (r) to 0px:

$searchContainer.css("height","0px")

Next upload those files again to the plugin.

When you run the plugin it should give you this result:


Now the bigger question is; do we still need that plugin? In APEX 5.0 there're native Modal Pages, so you could create an Interactive Report and set the page as a Modal Page. Next you can hook that up to a button or link and you've just build your own Modal LOV.

I still like to use the plugin at the moment (as it's just one item on the page), but it could use a refresh to make it look nicer and more inline with Universal Theme.

Wonder what you think - would you build your own Modal LOV in APEX 5.0 or would you still prefer to use a plugin? 

Understanding SQL

From time to time someone publishes a query on the OTN database forum and asks how to make it go faster, and you look at it and think it’s a nice example to explain a couple of principles because it’s short, easy to understand, obvious what sort of things might be wrong, and easy to fix. Then, after you’ve made a couple of suggestions and explained a couple of ideas the provider simply fades into the distance and doesn’t tell you any more about the query, or whether they’ve taken advantage of your advice, or found some other way to address the problem.

Such a query, with its execution plan, appeared a couple of weeks ago:

UPDATE FACETS_CUSTOM.MMR_DTL
SET
	CAPITN_PRCS_IND = 2,
	FIL_RUN_DT = Current_fil_run_dt,
	ROW_UPDT_DT = dta_cltn_end_dttm
WHERE
	CAPITN_PRCS_IND = 5
AND	HSPC_IND ='Y'
AND	EXISTS (
		SELECT	1
		FROM	FACETS_STAGE.CRME_FUND_DTL_STG STG_CRME
		WHERE	STG_CRME.MBR_CK = MMR_DTL.MBRSHP_CK
		AND	MMR_DTL.PMT_MSA_STRT_DT BETWEEN STG_CRME.ERN_FROM_DT AND STG_CRME.ERN_THRU_DT
		AND	STG_CRME.FUND_ID IN ('AAB1', '1AA2', '1BA2', 'AAB2', '1AA3', '1BA3', '1B80', '1A80')
	)
AND	EXISTS (
		SELECT	1
		FROM	FACETS_CUSTOM.FCTS_TMS_MBRID_XWLK XWLK
		WHERE	XWLK.MBR_CK = MMR_DTL.MBRSHP_CK
		AND	MMR_DTL.PMT_MSA_STRT_DT BETWEEN XWLK.HSPC_EVNT_EFF_DT AND XWLK.HSPC_EVNT_TERM_DT
	)
;

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                       |     1 |   148 | 12431   (2)| 00:02:30 |
|   1 |  UPDATE                       | MMR_DTL               |       |       |            |          |
|   2 |   NESTED LOOPS SEMI           |                       |     1 |   148 | 12431   (2)| 00:02:30 |
|*  3 |    HASH JOIN RIGHT SEMI       |                       |    49 |  5488 | 12375   (2)| 00:02:29 |
|   4 |     TABLE ACCESS FULL         | FCTS_TMS_MBRID_XWLK   |  6494 | 64940 |    24   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL         | MMR_DTL               |   304K|    29M| 12347   (2)| 00:02:29 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| CRME_FUND_DTL_STG     |     1 |    36 |     5   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | IE1_CRME_FUND_DTL_STG |     8 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("XWLK"."MBR_CK"="MMR_DTL"."MBRSHP_CK")
       filter("XWLK"."HSPC_EVNT_EFF_DT"<=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT") AND
              "XWLK"."HSPC_EVNT_TERM_DT">=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT"))
   5 - filter("CAPITN_PRCS_IND"=5 AND "HSPC_IND"='Y')
   6 - filter(("STG_CRME"."FUND_ID"='1A80' OR "STG_CRME"."FUND_ID"='1AA2' OR
              "STG_CRME"."FUND_ID"='1AA3' OR "STG_CRME"."FUND_ID"='1B80' OR "STG_CRME"."FUND_ID"='1BA2' OR
              "STG_CRME"."FUND_ID"='1BA3' OR "STG_CRME"."FUND_ID"='AAB1' OR "STG_CRME"."FUND_ID"='AAB2') AND
              "STG_CRME"."ERN_FROM_DT"<=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT") AND
              "STG_CRME"."ERN_THRU_DT">=INTERNAL_FUNCTION("MMR_DTL"."PMT_MSA_STRT_DT"))
   7 - access("STG_CRME"."MBR_CK"="MMR_DTL"."MBRSHP_CK")

The most informative bit of narrative that went with this query said:

“The table MMR_DTL doesnt have index on these columns CAPITN_PRCS_IND , HSPC_IND .. Since this is an update stmt which will update 85k records, In a dilema whether to index these columns or not .. And this table MMR_DTL is an ever growing table. Worried about the update performance. “

This was in response an observation that there was a full tablescan on MMR_DTL at operation 5 despite the predicate “CAPITN_PRCS_IND”=5 AND “HSPC_IND”=’Y’. You’ll note that the predicted cardinality for that scan is 304K and the update statement is going to change CAPITN_PRCS_IND from the value 5 to the value 2 – so it’s not entirely unreasonable to be worried about the impact of creating an index that included the column capitn_prcs_ind.

What more can we say about this query, given the limited information. Lots – unfortunately the owner of the query isn’t giving anything else away.

I’m going to leave this note unfinished to give people a little chance to think about the clues in the request, the questions they might ask, reasons why there might be a performance problem, and strategies they might investigate, then I’ll update the posting with a few ideas some time in the next 24 hours.

 

 


Irrecoverable part III : a fix

After part I:the problem, Part II: two reports here is part III.

We have backed up archivelog during a full and deleted them before the full completed.


RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
15 4.00K  DISK 00:00:00   13:31:08
    BP Key: 15   Status: AVAILABLE
    Piece Name: /FULL/0fq7gc0s_1_1

  List of Archived Logs in backup set 15
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  15  355533  13:29:55 355777  13:31:08

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
25 4.00K  DISK 00:00:00   13:31:26
    BP Key: 25   Status: AVAILABLE
    Piece Name: /ARCH/0pq7gc1e_1_1

  List of Archived Logs in backup set 25
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  15  355533  13:29:55 355777  13:31:08

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
26 3.00K  DISK 00:00:00 13:31:26
  BP Key: 26   Status: AVAILABLE
  Piece Name: /ARCH/0qq7gc1e_1_1

  List of Archived Logs in backup set 26
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  16  355777  13:31:08 355827  13:31:22

BS Size   Type Elapsed  Completion
-- ------ ---- -------- ----------
28 2.50K  DISK 00:00:00 13:31:28
    BP Key: 28   Status: AVAILABLE
    Piece Name: /FULL/0sq7gc1g_1_1

  List of Archived Logs in backup set 28
  Seq LowSCN  LowTime  NextSCN NextTime
  --- ------- -------- ------- --------
  17  355827  13:31:22 355863  13:31:28

Sequence 16 is missing in /FULL/ and is available in /ARCH/

Ok, let’s copy the missing pieces from ARCH to FULL


$ cd /
$ cp -p ARCH/* FULL/

Now let’s try. I save my directory on an offline location (tape/usb/cdrom) and dropped my database including backups.


cd /
tar cvf /tmp/FULL.tar FULL

Let’s drop (it’s just a test)


RMAN> startup force mount dba 
RMAN> drop database including backups;
database dropped

Ok let’s get this USB stick back from the moon to my datacenter and see if I can restore …


cd /
tar xvf /tmp/FULL.tar 

Let’s do this !


RMAN> startup force nomount
RMAN> restore controlfile from '/FULL/c-2414975447-20150521-01';
RMAN> alter database mount;

Now I want RMAN to look for other files in FULL and also marked expired stuff. The start with does the magic.

RMAN> catalog start with '/FULL/' noprompt;
RMAN> crosscheck backup;
RMAN> crosscheck copy;
RMAN> crosscheck archivelog all;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

As @dbastorage said on twitter:
“if it was easy, we would not need DBAs! ”

UKOUG Systems Event 2015

ukougI have a bone to pick with UKOUG. I’m coming from Birmingham and I’m a lazy git, so why the heck am I on in the first slot? :) It was a 04:30 start to the day, for a train journey starting at 05:50. I could have got a later train, but it would have been busier and more importantly, more expensive… After putting the latest of my writing tips posts live and lying in the bath (sorry for the bad mental image) for a while psyching myself up for the day, it was a quick taxi ride to the station, then on to the train. The train was reasonably busy, but I had reserved a seat with a table and power socket, so I was able to use the time to write up a blog post about yesterday’s Oracle Midlands Event #9 and to do some last minute run throughs of my talk.

Since I was up in the first slot I missed the opening greeting to set up. The event was very Exadata-heavy and I was doing a virtualization talk, so I wasn’t really expecting anyone to come, but I had a pretty good crowd, which was nice. Many of the people in the room were already working with virtualization, some as users of the services, others as administrators. I don’t think there would be a lot that was particularly new to this audience, but I’m hoping they found at least something useful in there. After the session, I stood outside the room chatting to one of the guys about the issues he was currently going throught relating to Oracle licenses on VMware. It can be difficult, especially when there seem to be a lot of people out there actively trying to throw a you bum steer. My statement on this  matter is always, only ever deal with Oracle Licensing Management Services directly, not sales people, which working this stuff out and make sure you have everything down on paper before you start!

I only managed to catch the tail end of “Oracle Exadata Meets Elastic Configurations” by Svetoslav Gyurov so I will have to assume it was totally awesome! I love you Sve! :)

redstacktechDuring the session changeover, I got to speak to some of the folks on the Red Stack Technology booth. I popped by to say thank you for taking over the sponsorship of the Oracle Midlands events. They were also a sponsor of the UKOUG Systems Event itself, so a double thank you was in order. :)

Next up was “Oracle Exadata & Database Memory” by Frits Hoogland. Exadata is “posh-RAC”. Lots of stuff about RAC databases are also common to singe instance Oracle databases. As a result, this sesison was actually relevant to single instance, RAC and Exadata. Frits is one of those deep-dive guys, but the session had enough top-level and deep-dive content to appeal to all tastes. I really enjoyed it.

Next up was Roger MacNicol speaking about “Oracle Big Data SQL”. I had seen a bunch of this information last year at Oracle OpenWorld, but it also included some architectural information that was new to me. I was unaware of the architectural similarities between implementing smart scans in Exadata storage cells and getting Oracle to talk to Hive and HDFS. Very interesting!

I went to see “Migrating To Exadata The Easy Way” by Martin Bach because I wanted to watch him present. I don’t see working on Exadata in my future, so that aspect of the talk was not my focus. As with Frits’ session earlier, most of the content was relevant to Non-Exadata and Non-RAC environments too, so there was something for everyone here. Migration to new hardware and operating systems, no matter what the kit, comes with a similar set of issues and constraints. It also sparked some interesting discussions after the session, which meant I missed Jason Arneil‘s session on “Engineering DBaaS At Large Scale”. :( Hopefully I’ll get to see that at UKOUG Tech15?

There was a panel session at end the day. It is fatal to ask me to get involved in these things as I have an opinion on everything and find it impossible to keep my gob shut! I did warn them! :)

I spent much of the social event talking to @DBASushi about Enterprise Manager Cloud Control. I made some comments about Cloud Control in the panel session, which he wanted me to clarify. I’ve said before, I think all organisations should use Cloud Control, but there are some things about it I find infuriating, totally unintuitive and bloaty. During the discussion it became aparrent that some of my issues are because I need to RTFM a bit, but others are (in my opinion) problems with the user experience (UX) of the product. No offence to anyone involved, but some parts feel beautifully crafted, while other parts feel slapped together.

From there is was a quick goodbye, then off to Euston to catch my train home. Once again, a reserved seat with a table and power socket. Happy days!

Thanks to UKOUG for letting me come out to play, as well as all the attendees, presenters and UKOUG staff I talked to. Also thanks to the Oracle ACE Program for continuing to let me fly the flag.

Cheers

Tim…


UKOUG Systems Event 2015 was first posted on May 21, 2015 at 8:37 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Writing Tips : Should I write off-topic posts?

writingI’m a Jedi master at writing off-topic posts! This blog started life as an Oracle blog, but now I just write reviews of Twilight and 50 Shades of Grey. :)

The simple answer is you should do what you are happy with. I had been writing on my website for 5+ years before I started this blog. When I started the blog, I thought it would be technical. It quickly became apparent that the Oracle content was better suited to my website and the blog became a series of opinions, rants, movie reviews and book reviews. That’s why I changed the tag line to, “Oracle related rants (and lots of off-topic stuff)”.

I know in the early days that kind-of frustrated a lot of people. I used to receive negative emails and comments about it all the time. That doesn’t happen much these days because most readers seem to have become accustomed to my stance of “Oracle content goes on the website. Bullshit goes on the blog.” I’m sure some people would say bullshit goes on both. :)

Blogging platforms allow you to categorise and tag your content. I would suggest you make use of that so if someone is really irritated by off topic posts, they can avoid them.

Speaking for myself, I like to read the off-topic posts written by people I follow. It gives me some insight into the person and makes me feel connected. If I actually meet them in person, I feel almost like I know them already. It’s kind-of freaky! :)

If you find yourself consistently writing about a completely different subject, you might want to consider starting up a new blog. I’ve written some MySQL and SQL Server articles and put them on oracle-base.com, but if I ever start doing more of them, I would probably branch them off into a separate blog. It’s not necessary, but would seem a little cleaner to me.

Cheers

Tim…


Writing Tips : Should I write off-topic posts? was first posted on May 21, 2015 at 6:45 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Log Buffer #423: A Carnival of the Vanities for DBAs

This Log Buffer edition covers Oracle, SQL Server and MySQL blog posts from all over the blogosphere!


Oracle:

Hey DBAs:  You know you can  install and run Oracle Database 12c on different platforms, but if you install it on an Oracle Solaris 11 zone, you can take additional advantages.

Here is a video with Oracle VP of Global Hardware Systems Harish Venkat talking with Aaron De Los Reyes, Deputy Director at Cognizant about his company’s explosive growth & how they managed business functions, applications, and supporting infrastructure for success.

Oracle Unified Directory is an all-in-one directory solution with storage, proxy, synchronization and virtualization capabilities. While unifying the approach, it provides all the services required for high-performance enterprise and carrier-grade environments. Oracle Unified Directory ensures scalability to billions of entries. It is designed for ease of installation, elastic deployments, enterprise manageability, and effective monitoring.

Understanding Flash: Summary – NAND Flash Is A Royal Pain In The …

Extracting Oracle data & Generating JSON data file using ROracle.

SQL Server:

It is no good doing some or most of the aspects of SQL Server security right. You have to get them all right, because any effective penetration of your security is likely to spell disaster. If you fail in any of the ways that Robert Sheldon lists and describes, then you can’t assume that your data is secure, and things are likely to go horribly wrong.

How does a column store index compare to a (traditional )row store index with regards to performance?

Learn how to use the TOP clause in conjunction with the UPDATE, INSERT and DELETE statements.

Did you know that scalar-valued, user-defined functions can be used in DEFAULT/CHECK CONSTRAINTs and computed columns?

Tim Smith blogs as how to measure a behavioral streak with SQL Server, an important skill for determining ROI and extrapolating trends.

Pilip Horan lets us know as How to run SSIS Project as a SQL Job.

MySQL:

Encryption is important component of secure environments. While being intangible, property security doesn’t get enough attention when it comes to describing various systems. “Encryption support” is often the most of details what you can get asking how secure the system is. Other important details are often omitted, but the devil in details as we know. In this post I will describe how we secure backup copies in TwinDB.

The fsfreeze command, is used to suspend and resume access to a file system. This allows consistent snapshots to be taken of the filesystem. fsfreeze supports Ext3/4, ReiserFS, JFS and XFS.

Shinguz: Controlling worldwide manufacturing plants with MySQL.

MySQL 5.7.7 was recently released (it is the latest MySQL 5.7, and is the first “RC” or “Release Candidate” release of 5.7), and is available for download

Upgrading Directly From MySQL 5.0 to 5.6 With mysqldump.

One of the cool new features in 5.7 Release Candidate is Multi Source Replication.

 

Learn more about Pythian’s expertise in Oracle , SQL Server and MySQL.