It happens: You have a 4-node DataGuarded Oracle 11gR2 database and your System Administrators need to take the boxes down for maintenance. But they don’t know that it’s protected under DataGuard, with Fast-start failover enabled (which automatically performs (via DG Observer) the switchover from Primary to Standby, switches roles, reconfigures listeners, and tries to keep everything 99.99995% available.) So they use good old:
sqlplus “/ as sysdba”
SQL> shutdown immediate;
And now you need to start it back up. But when your admins get to their familiar old prompt it burps out:
Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database
Now there are a number of blogs which will walk you through the tedious steps of recovering this condition manually via DGMGRL and multiple control file and standby redo log restores to all the targets. But it was 1:00A local time and I didn’t want to spend the rest of the night crawling through these trying to get this beast back on its feet.
So, I returned to the never-ending exploratory world of what is contained in Oracle Enterprise Manager 12cR3 (220.127.116.11.0) And (tada sound), OEM can handle it all for you relatively automatically.
To get your Primary DB back on-line (to avoid the complaints from the users who have been disconnected)
DGMGRL> connect SYS/<pwd>@<primaryDGDBtarget>
DGMGRL> disable fast_start failover force;
Now that the Primary is back open and accessible, return to OEM OMS and visit:
Targets -> Database -> (your Primary DB)
Availability -> Data Guard Administration
There’s a text link at the bottom which reads
That’s your ticket back to green-arrows and healthy DG in 90% of the situations. It will perform health-checks throughout your configuration, re-created standby redo logs, re-sychronizing disconnected standby databases, shipping archive logs whereever they are needed, repairing disrupted communications, restoring fast-start failover observers, and whatever else you have.
The other alternative is to simply Remove the existing failed Standby database (select, then [Remove] the existing standby database; then visit its host and delete the existing datafiles, redo, archive and tempfiles (you do not have to touch the TNSNAMES or Listener configurations – that will be re-created for you); then use [Add Standby Database] on the same screen to restore functionality (this is good as an if-all-else-fails, so this method of recovery).
–Note: Mr. Ellison is CEO of Oracle Corporation, the world’s foremost database, middleware and data appliance vendor, long-known for ability to facilitate deployment of massive applications which need unheard of expertise to ensure proper operation and stability (e.g. healthcare.gov)
10) You’re thinking about buying a Ferrari. But you need 15 of them. In black carbon fiber. And are hiring NZ Kiwis to drive them for you.
9) You’ve asked your local real estate broker to price the 6 houses on either side of yours to see how much it would cost to own your entire neighborhood.
8) You’ve just applied 100 sequential Windows Updates (each mentioning something non-specifically horrible will happen to Windows if don’t apply them) to your PC, some of which you had to fix and re-write, and rebooted each time in-between and don’t see any problem with that.
7) Your burger and fries order at McDonalds has a problem, but instead of talking to a manager, you want to go online and open a Sev 2.1 Service Request, with immediate cell phone call-back requested.
6) The new iPhone App you just purchased comes delivered on 100 DVD-ROM‘s, or downloadable digital archive images, and again, you don’t see an issue with that.
5) Your car stereo only has one song playing on infinite repeat: AC/DC’s Back in Black.
4) For some reason every article of clothing you wear has your company logo on it (including your undies), and you see that as completely normal.
3) That new food processor you bought for US$2000 keeps having bizarre breakdowns, but you’re pretty sure it will be fixed in next month’s firmware upgrade.
1) Your 13 year-old daughter helped her school create a complex summer school registration system, but you just smirk and snicker that she chose to host it using Microsoft SQLServer, and mutter about read-consistency and parallel block reads.
12c) You incessantly fixate on figuring out how that family potato farmer in western North Dakota could increase his crop yield, if he only used a database.
In part 2 of this mini-series I’ll be describing the new mechanism for the simple frequency histogram and the logic of the Top-N frequency histogram. In part 3 I’ll be looking at the new hybrid histogram.
You need to know about the approximate NDV (number of distinct values) before you start examining the 12c implementation of the frequency and top-frequency histograms – but there’s a thumbnail sketch at the end of the posting if you need a quick reminder.
And most of these new commands are available via a simple right-click in the current release of Oracle’s SQLDeveloper (DBA Menu).
When everyone finally moves to Oracle pluggable databases, one thing to remember is that stopping and starting a database is not going to be as simple as it use to be. Shutting down and starting up a database is done simply with the following commands:
If we use one of these traditional shutdown methods at the container database (CDB) layer, we will bring down the CDB and the associated PDBs under it. This is something to be aware of when looking to reboot a container databases. Lets take a look at bringing down a CDB.
[oracle@oel ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.104.22.168.0 Production on Sun Jul 21 21:42:53 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real…
During a routine installation, I managed to mis-type the filesystem path for the ORACLE_BASE during execution of the Oracle Universal Installer (./runInstaller).
[nb: ORACLE_BASE by normal definition should be a standard starting filesystem mountpoint under which all other instance-specific data/files for this ORACLE_HOME would be found. Typically contains your diagnostic logs, Fast Recovery Area files, all configuration tool logs, and any Flashback checkpoint saves.
./adump # Audit data./dpdump # Data Pump related
./pfile # init.ora files generated by config tools
–end of note]
Since the service account (oracle) had filesystem permissions to do so, it went ahead and put all of the flashback recovery, diagnostics, and audit trail files (and my backup controlfiles) into the incorrectly named directory. The database itself was running, but how to correct this without starting all over again?
Start with backing up your init.ora configuration from spfile to pfile format:
Oracle has made a few changes to the database to allow organizations to reduce the cost of migrating to Oracle 12c. One of these changes is with the size limits that have been placed on the VARCHAR2, NVARCHAR2 and RAW data types. In past versions of Oracle database the maximum size for these data types were 4,000 bytes. In Oracle 12c, these data types can now be increased to 32,767 bytes.
In order to set these data types to use the larger setting, the MAX_STRING_SIZE parameter needs to be set. The only values that this parameter can take is STANDARD and EXTENDED. Once the parameter has been set to EXTENDED, you cannot go back to STANDARD.
Note: Altering MAX_STRING_SIZE will update database objects and possibly invalidate them
To increase the size of the VARCHAR2, NVARCHAR2 and RAW columns for a non-CDB database, these steps need to be followed:
Hi, I’m James Lui, a senior applications DBA working at Aramark Uniform Services. and I’m Bobby Curtis, a Solution Architect with BIAS Corporation, Oracle Platinum Partner.
[James] We just finished an Introduction to 12c New Features and Functionality. One of the reasons we wanted to bring this to the Users Group’s attention.is that out on the internet, we were Google-ing for the new Database 12c we wanted to find out, it’s not a general release right now; it’s not generally available. But there are Oracle Beta test partners out there looking at it, whacking it and trying to make it work. But what we did find was a little disturbing.There’s a lot of stuff dating back to early last year (2012) that partners that are on these Betas are blogging about features that may or may not be in the product. They’re putting SQL code out there that is wrong. That came to our attention when we were talking with other Oracle Partners out here (at Collaborate). What do we do about this? So our position on this was to try to start elevating the invisibility of what is happening out there with regard to NDA Beta Partners, like Bobby and I, who are are subject to… You can’t talk about it, or you can’t disclose stuff that isn’t real.
The feature sets in the final release are subject to what Oracle wants to try to present as a solid product that works to end-user customers. Not something that the wheels are going to fall off.just because they tried it (they put some spinners on it) and it ends up falling off and it’s a bad idea. The whole purpose for this idea is we don’t do GA on an unstable product. Let’s let experts hammer at this thing. See if it works, get an idea from a users group perspective (trying to represent as many people, as possible) what features work, what doesn’t work. and what needs to be fixed before the thing goes live.
[Bobby] That way the users understand what they’re really getting versus what the hearsay out there.. Because everything that’s been posted out there (on the blogs) violates the NDAs. and Oracle can pull things (features) out and nobody really knows it because they’re relying on older postings that were not authorized anyway. So this session was to provide the users groups with something based upon here’s what we’ve seen, and here’s what we think is going to be in there, but it’s not guaranteed.
[James] Realistically we do know that forward information is good. Telling people that the documentation that will be provided is more massive than ever before. It’s going to give you direction as a DBA, developer or manager trying to make a decision to adopt this product. There is more to read than ever (before.) There’s more room for books to be written than ever. There’s definitely more room for webinars about this product. They’re (Oracle’s) thinking about at least 500+ new feature functionalities that are really exciting stuff. And we covered maybe…
[Bobby] Like, 25 of them — the tip of an iceberg on this product.
[James] But in terms of what that final product ends up being, when Oracle decides it’s ready for market, we had the confidence as users group representatives (ODTUG, IOUG, UKOUG, Australia OUG) when we hammered it to death, these features worked, with the caveat of saying “Fix this ” then we’re (users groups) happy.
That’s the idea behind this session. Telling people that going forward, you may need to go back to the drawing board or to school to find out how to use this product in the best way.
But the exciting part is here at Collaborate, IOUG was able to give those who attended our session a preview of the future in a very substantial technical way.
[Bobby] And so we thought it would be a good idea, so okay there’s a lot of talk about it, everybody’s asking when’s it coming out, what’s going on? So from a users group perspective, let’s give everyone not so much a peek into it, but to say here’s what’s coming from what we know of, from looking at the Betas.
But, again at the same time letting everyone know that just because we say something’s coming, Oracle could pull the plug (on that feature) at some point-in-time and say, “That’s not going to be added in this release. “ So basically, this was a way to give to the users groups, who have been saying, “We’ve been talking about it for six months. But what’s really in it?” And as we alluded to earlier, we’ve only touched the “tip of the iceberg” – we only did 25 of them (the new features). And we didn’t do a Top-10 or anything like that. We just said, here are the 25 features we tested during Beta testing, and here’s what our thoughts are. So we think we gave the users groups a good base of understanding where it’s (Database 12c) coming from, and what can be potentially looked at, and what they could use. And from the reaction we got from the crowd, was actually pretty good.
[James] And Bobby represents the partner, implementer, system integrator world of the consultants that are out there who are going to be challenged with actually upgrading (systems), or doing the installations. I represent the existing customer base of Oracle, we’re an Applications e-Business shop; we want to know what does that roadmap affect for us. What does 12c give us to save money basically as an organization? So between the two of us, we actually do cover a big part of that world. and we were very heartened by the (experiences) during the Beta testing (we were Phase 3, probably the last (Beta) before General Availability), we’re pretty happy with the product. Perhaps, shockingly good?
[Bobby] This is actually by far the best release they (Oracle) have come out with on a first release. Because they’ve listened to the users groups, and took a lot of the things that we wanted, put it into it (Database 12c.)
[James] And by being part of the Beta Program, the people behind the Beta Program brought the user groups in confidence, to say you people represent key members of the user community. Hit this thing until it breaks and tell us what it’s going to need for this product to go to General (Availability).. And that’s what we felt we did. We did things that were very unexpected
[Bobby] Any time you get a (Database) developer standing behind your shoulder… because that was one of the really impressive things behind the Beta testing. You had 9 testers, and Oracle put 50 of their people in the room with us. and when we broke it, they literally pulled the code open and looked at it and said, “Here’s why it’s broken.” And that in-turn gave Oracle the insight to say, “Okay what did you guys do?” versus “How did it break?” So that way they can go fix the code and can be more responsive to what users are going to do out in the marketplace.
[James] And while it’s not a generally-accepted practice, nor a best practice idea, I as an e-Business Suite Customer ended up bringing in a live R12.1.3 version instance. Not certified. Not supposed to be certified. Certainly Oracle’s own (e-Business Suite) Development group behind Applications Technology does not certify you’re supposed to be doing (Database) 12c upgrades. But in the context of a Beta, for me this is the exciting way to do it. You take a sandbox instance, you take a copy of a live R12 instance and you try to do the upgrade. The only things that broke, was the stuff that Oracle told us would break, because they de-support those particular features. That was the guarantee that makes it whole.
[Bobby] They listened and they fixed it. So that when GA comes out, you can take it to Production if you want. I think that’s what a lot of people are going to be shocked about, because most organizations wait for Release 2, to be honest. But Release 1 of (Database) 12c, we think, from the user group perspective could be production-ready and be used.
[James] That’s better than most other software companies that try to be first to market with a new feature, and let the user community test it and find out where the bugs are. That was a different philosophy that we met (at Oracle). And we know the Beta 1 and Beta 2 release testings uncovered thousands and tens of thousands of problems that got fixed by the time we got our hands on it. The improvements that Oracle has actually made in that process That’s a good step in the right direction, and we would hope every software vendor actually does that same thing. Because IOUG being very agnostic about what the user community needs to make a business solution happen, having Oracle do that just makes their job easier.
Since it came up this week while discussing Database 12c:
“All of these strategies are different than a two-phase commit that relies on separate prepare and execute steps. In the Starbucks example, a two-phase commit would equate to waiting at the cashier with the receipt and the money on the table until the drink is finished. Then, the drink would be added to the mix. Finally the money, receipt and drink would change hands in one swoop. Neither the cashier nor the customer would be able to leave until the “transaction” is completed. Using such a two-phase-commit approach would certainly kill Starbucks’ business because the number of customers they can serve within a certain time interval would decrease dramatically. This is a good reminder that a two-phase-commit can make life a lot simpler but it can also hurt the free flow of messages (and therefore the scalability) because it has to maintain stateful transaction resources across the flow of multiple, asynchronous actions.”
(Now history…but we’re in-queue for #OOW13)
Collaborate asked ” What about Database 12c? ” @IOUG is pleased to deliver! In MHB4A 830A Th, Just before you hop on the plane/bus/car/train/buffalo (it’s Denver…) back home, get tuned in, turned on, updated, clarified, ask your what is it questions, bring clarity to the cloud, plug-in, charge-up and get what you travelled all the way here to find out the details about the database formerly-known-as-next-generation (psst…that means “12c”).
No,it’s not GA yet (General Availability). Yes, we are both knowledgable Beta participants under NDA. Yes – you need to know more than you do today based upon your experience with even 11gR2. Bring your coffee, munchies and your questions. And prepare for some answers. Finally.
How? Because Oracle teams with IOUG for a reason in this area – we know how to educate people, with the right way to do it – even for a product that isn’t here (quite yet.)
the difference between Oracle Fusion Middleware, Oracle Fusion Applications, cold fusion, and jazz fusion is? Come to this session for help! It gives a high-level overview of which tools are part of Oracle Fusion Middleware and briefly touches on what they do. This session is a lead-in for the rest of the day, in the course of which you can get more-detailed information about some of the Oracle Fusion Middleware products. Start your day at a high level, and begin your Oracle Fusion Middleware learning here.
If you Care a Little More, Things Happen. Bees can be dangerous. Always wear protective clothing when approaching or dealing with bees. Do not approach or handle bees without proper instruction and training.