Friday, September 27, 2013

Patch and Provision with EM12c: #3 Database Provisioning - Using Profiles

With my previous post on creating Profiles with OEM12c completed, I wanted to use them for actual deployments. If you recall, Mr. Green wants to make sure that his deployments are consistent across each environment.

What I'd to achieve today
  • Provision RDBMS software and Create a Database using a procreated profile
Let's Begin
1. Find your custom profiles by navigating to the Database Provisioning homepage.
NewImage
2. Highlight the Profile you'd like to use and click "Provision".
NewImage
3. On the next page, the profile you selected in the previous screen is already populated. Click the "Add" button, and select the correct host. Note, that you can select multiple hosts, but unfortunately I only have a single one to test with.
NewImage
Configuration
4. On the next screen, as we have seen with earlier posts, the Configuration screens require a step-through of the Host, Software, Database, and Compliance areas.
4.1 Click on Setup Hosts
NewImage
4.2 Select the correct values from the next couple of screens.
NewImage
4.3 Click on "Deploy Software".
NewImage
4.4 Enter the Oracle Base and Home directories.
NewImage
4.5 Time for "Create Database".
NewImage
4.6 Configure Database settings accordingly.
NewImage
4.7 I usually just skip the Compliance setup. Clicked through it to get ahead.
NewImage
NewImage
4.8 Getting close to the end now!
NewImage
4.9 Submit the task!
NewImage
Progress
5. The procedure activity screen displays periodically refreshed status updates.
5.1 Let's start with the first one.
NewImage
5.2 Bingo, we're done!
NewImage

6. Upon searching for the container database, I find it.
NewImage
Conclusion
The story doesn't end here, I could go a few steps further to provision the GI, RDBMS, and Database with a single profile. I'd like to save that for another time :)

I do understand, that in a test/lab environment the results aren't as critical as in a production one. The aim of these exercises is to show you that it is possible to leverage profiles to provision software plus database quite easily.

Cheers!







continue reading "Patch and Provision with EM12c: #3 Database Provisioning - Using Profiles"

Thursday, September 26, 2013

How long did your Database Upgrade actually take?

This might be old news, but it became new news for me after I discovered it last week. As far as I know, there's no way to determine the length of a database upgrade. There are, however, ways to influence the length by gathering dictionary stats, truncate AUD$ and FGA_LOG$. At the end of the upgrade process, either by DBUA, or via Manually I'd want to know the actual time taken by the scripts.

With a recent upgrade from 11.1.0.7 t o 11.2.0.3 on Solaris, I ran into this issue that disrupted the upgrade process and my (inadequate mental) timer. After reviewing the scripts called internally by catupgd.sql, I found this one that I thought was quite helpful.

SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           09-20-2013 14:53:05
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.3.0  00:15:13
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.3.0  00:03:39
Oracle Real Application Clusters
.                                         VALID      11.2.0.3.0  00:00:00
Oracle Workspace Manager
.                                         VALID      11.2.0.3.0  00:00:50
OLAP Analytic Workspace
.                                         VALID      11.2.0.3.0  00:00:23
OLAP Catalog
.                                         VALID      11.2.0.3.0  00:00:59
Oracle OLAP API
.                                         VALID      11.2.0.3.0  00:00:25
Oracle Enterprise Manager
.                                         VALID      11.2.0.3.0  00:04:56
Oracle XDK
.                                         VALID      11.2.0.3.0  00:00:44
Oracle Text
.                                         VALID      11.2.0.3.0  00:00:46
Oracle XML Database
.                                         VALID      11.2.0.3.0  00:04:48
Oracle Database Java Packages
.                                         VALID      11.2.0.3.0  00:00:26
Oracle Multimedia
.                                         VALID      11.2.0.3.0  00:10:05
Spatial
.                                         VALID      11.2.0.3.0  00:05:34
Oracle Expression Filter
.                                         VALID      11.2.0.3.0  00:00:10
Oracle Rules Manager
.                                         VALID      11.2.0.3.0  00:00:09
Gathering Statistics
.                                                                00:05:56
Total Upgrade Time: 00:55:14 <--

PL/SQL procedure successfully completed.

As it turns out, Tim Hall has blogged about it within his DB12c upgrade steps. The script in 12c is simply called utlu121s.sql.


Cheers!
continue reading "How long did your Database Upgrade actually take?"

Saturday, September 21, 2013

ORA-00119 During a Database Upgrade

I consider myself a reasonably detail oriented person – reasonably… Well, every now and then, more often than not, I tend to overlook documentation. Either that, or my dyslexia kicks in.

Before I go into my story, let me explain the configuration, which led me to the setup on which I had a problem.
  •     11.1.0.7.0 CRS & ASM upgraded to 11.2.0.3.0 GI. 
  •     11.2.0.3.0 RDBMS Software Installed.

During an upgrade of a few 11.1.0.7.0 Databases to 11.2.0.3.0 on Solaris 10 deployed on a two node RAC, I encountered the error below.

CRS-2674: Start of 'ora.freedom.db' on 'jellybean1' failed
CRS-2632: There are no more servers to try to place resource 'ora.freedom.db' on that would satisfy its placement policy
CRS-5017: The resouce action "orac.freedom.db start" encountered the following error
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA000132: syntax error inresolved network name 'LISTENER_FREEDOM1'

CRS-2674: Start of 'ora.freedom.db' on 'jellybean2' failed
CRS-2632: There are no more servers to try to place resource 'ora.freedom.db' on that would satisfy its placement policy
CRS-5017: The resouce action "orac.freedom.db start" encountered the following error
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA000132: syntax error inresolved network name 'LISTENER_FREEDOM2'

The error to note is the ORA-00119. It’s basically telling me the issue, CRS is unable to start the resource!

I found a MOS note which talks about my situation. “DBUA failed to start instance (ORA-00119) when upgrading from pre 11.2 database to 11.2 (Doc ID 1552530.1)”.

It simply said, to set the LOCAL_LISTENER parameters to the actual address prior to running the DBUA.

For example (excuse my plagiarism from MOS, I’m too lazy to change the names).
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-vip)(PORT = 1521))' scope=spfile sid='xxx1';
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-vip)(PORT = 1521))' scope=spfile sid='xxx2';

Again, unfortunately I did not create flashback restore point, but fortunately the error only happens with the Post Upgrade Steps. My fix, for the currently database, was to startup the database and set the parameter correctly. That “seemed” to fix the issue.

For the next database, I set the parameter before the upgrade. But to my surprise, the LOCAL_LISTENER parameter was reset to LISTENER_<SID>!!

Okay, at this point, I had to find out what was going on. On my third instance, I actually set the parameter as per the recommendation above and bounced it. Voila, the parameter is set because the instance dynamically registers it. But, where is it even getting the value??

After a little creative digging around, it turns out that I missed a step after installing the RDBMS software. I will outline the steps for the upgrade process and follow up with what actually happened.

  1. Shutdown Database (from 11.1 RDBMS home)
  2.  Startup Upgrade one instance on (from 11.2 RDBMS home)
  3. Upgrade Database Components
  4. Post Upgrade

a.     Shutdown instance
b.     Startup Database from 11.2 GI
c.      Error with ORA-00119

It took me a few minutes to put the pieces together. I was able to start the database via server control (srvctl) on the 11.1 home, but not from srvctl on 11.2 home. What was missing? Wait, where is the LOCAL_LISTENER parameter getting its default value? Think man think! Oh shoot, the TNSNAMES.ORA file!



After I copied the TNSNAMES.ORA file to the new 11.2 RDBMS home, the next upgrade worked without a glitch.

So, if you’ve found your way to this post, I sincerely hope it helps you.

Cheers!





continue reading "ORA-00119 During a Database Upgrade"

Wednesday, September 18, 2013

DB12c: ORA-19504: failed to create file "+DATA" While Creating Pluggable Database Manually

In my previous post on DB12c, while manually creating a pluggable database (on ASM), I hit the above error. No matter which way I tried to splice it, the error still occurred.

SQL> CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY beans
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE heinz_data
    DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON;
CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY beans
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

I should mention, that the container database was also created manually, and I found that there’s a missing script in the binaries!

SQL> @?/rdbms/admin/catcdb.sql
SP2-0310: unable to open file "/u01/app/oracle/product/12.1.0.1/dbhome_1/rdbms/admin/catcdb.sql"

I’d opened up an SR, and asked them look into it. They came back earlier in the week with what I thought they’d say.

Since, the missing catcdb.sql is a known issue, he recommends that we create the container using the DBCA GUI. Following that, I should be able to create the pluggable databases manually. For reference, the patch request bug ID is 17418714.

To validate that, I had a preexisting CDB (RAC), which I created via the DBCA GUI. From it, I was able to successfully create the PDB (heinz_pdb). Below excerpt is from my blog post mentioned above.

SQL> select name, open_mode from v$database;

NAME    OPEN_MODE
--------- --------------------
RAC     READ WRITE

SQL> CREATE PLUGGABLE DATABASE heinz_pdb ADMIN USER heinz_admin IDENTIFIED BY beans
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE heinz_data
    DATAFILE '+DATA' SIZE 250M AUTOEXTEND ON;

Pluggable database created.

So, what I’m confused with is: Does creating the CDB using DBCA actually run the catcdb.sql? That wouldn’t make sense because the script needs to be present in the first place!

My gut feeling, is either
  • There’s a missing script “piece” while creating the CDB manually
  • Or, there’s a bug within the code which is hit while creating the PDB on ASM.


Either way, I’m curious to try out the same exercise after the aforementioned patch set release. Perhaps Oracle is looking to fix that in DB 12.1.0.2.0?


Cheers!
continue reading "DB12c: ORA-19504: failed to create file "+DATA" While Creating Pluggable Database Manually"

Tuesday, September 17, 2013

Oracle Open World 2013 Schedule

NewImage

So, two weeks before OOW, when I had resigned myself for nothing being able to go I found out that I'm actually going! Now, I'd been following the twitter feed on the happenings out here so, it didn't take me long to get my schedule together.

Saturday:

I'm taking the last flight out to SFO, and fortunately for the time change arrive late in the evening :)

Sunday:

I found out about the Open World Bridge Run earlier on, and couldn't resist going. Jeff Smith (@thatjeffsmith) has promised a ribbon for the first 50 people out there. Not that its my only motivation, but I'd like to increase my ribbon collection this year :) Afterwards, there are a handful of User Group sessions which I will attend. After Larry's keynote, the dinner venue is up for debate at this time!

NewImage

Monday:

Chet Justice (@oraclenerd) posted this, and again I couldn't resist! Oracle Open World San Francisco Bay Swim - Part II at the Dolphin Swimming and Boating Club is going to be a fun way to kick off the "working" week at OOW. If I'm not mistaken, over 50 folks have signed up. I just hope someone brings hot drinks after the cold pacific water swim. 

NewImage

After the keynote later on in the afternoon, is my presentation with Oracle Partner Exchange: Developing a Cloud Services Practice with Oracle Technology [CON9843].

Post presentation, I'm hoping to hit some Oak Table presentations along with a few Big Data ones from OOW. I'm suck a slacker that I haven't even done my schedule yet :) Dinner plans for Monday are iffy, I'm sure that I will tag along with whomever is unlucky enough to bear my company!

Tuesday:

So, the highlight of the day is RACAttack! I have the honor of being one of the esteemed Ninja's for this attack session :) Come and join us to build a DB12c RAC Cluster, and at the same time network and make friends with your peers. Outside of that, again Oak Table, and OOW presentations wherever I can fit them. The day will be finished off with a bowling event, and hopefully some sleep!

Wednesday:

Early morning flight back home. Either I will be wide away on the plane, or I'll be blogging about my wonderful experience.

In any case, the short of it is, I'm looking forward to meeting existing friends, and to make new ones!

Please do ping on twitter @maaz_anjum up if you're out there, as I'd love to meet up! :)

Note: Another events that I'd recommend is the Bloggers Meet Up on Wednesday.

Cheers!

continue reading "Oracle Open World 2013 Schedule"

Saturday, September 14, 2013

Patch and Provision with EM12c: #2 Database Provisioning - Profiles

Now that I've recovered from a straight 10 hour session I did to write the blog in the previous topic in this series (Provision me a Database!), I wanted to touch on a few of the "softer" pieces in the "Database Provisioning" functionality.

Provisioning Profiles
To put the concept into perspective, lets start with a story.

A certain Mr. Green (DBA Manager) is responsible for dozens (maybe more) servers, which host many databases. Now, for a growing organization, there's a need to provision database servers for upcoming projects. Most likely these projects will go through a Development Lifecycle, i.e. Development, Pre-Prod, and eventually Production. Mr. Green's DBA's are busy people preoccupied with internal projects. Much to his dismay, previous server build cycles were inconsistent.

He wishes there were a way to streamline it. A way to standardize software, and database provisioning.
NewImage

Database Provisioning, specifically Provisioning Profiles offer a robust and more importantly, a repeatable approach to Mr. Green's problem. Not only would he be able to standardize his deployments, configurations from a Pre-Prod environment can easily be replicated to Production.

Okay, maybe I tend to over-simplify things. Oracle Documentation provides good examples on how to configure a Database Profile, however I'd like to explore the concept further and discuss The three types of profiles template options.
Straight from Docs:

"Provisioning Profile is an entity which contains software bits and configuration. When a provisioning profile is created from an existing installation, it provides the flexibility to clone either Grid Infrastructure (with software or configuration) and Oracle Database (with software or configuration). You can create database templates using provisioning profiles. A designer or administrator can create a database provisioning profile as a one-time activity; which can be used by operators for mass deployment. Using provisioning profile enables standardization in deployments and reduces need for rescheduling deployments by avoiding errors while configuring deployment procedures."

To set some expectations, in the post I will create profiles for
  • GI Home
  • RDBMS Home
  • Database Template
Si Comincia!

1. Find your way to the Database Provisioning link.

NewImage

2. Click "Create".
 
NewImage

Grid Infrastructure Profile
 
3.  Chose a reference Target, and pick the GI Home.
 
3.1 Chose the appropriate Credential Type, and subsequent Credential Names. Notice the "Target Type" drop down list only contains 3 types of values.
 
NewImage
 
3.2 I left the default values of the "Profile Location". To get an idea of your options, please check the pop out. The only change I made was the "Profile Name". Make sure you chose the correct Software Library Storage, in case you created multiple locations.

NewImage

3.3 Review the setting and click "Submit".

NewImage

3.4 We are redirected to the Procedure Activity page. Under the hood, the procedure is actually creating a clone of the GI Home. Then, upload it to the SWLIB. Pretty neat!!

NewImage

3.5 After a few minutes, the Activity is complete!

NewImage

3.7 Go back to the "Database Provisioning" home page. There it is!

NewImage

RDBMS Profile

4. Back on the "Database Provisioning" home page.

4.1 Click on "Create".

NewImage

4.2 Again, all I changed is the "Profile Name". Click "Next".

NewImage

4.3 Review the settings and click "Submit".

NewImage

4.4 As with the prior Target Type, the Procedure Activity (among many other tasks) will clone the Oracle Home.

NewImage

4.5 Back on the "Database Provisioning" home, we'll see the new profile.

NewImage

Database Profile

5. Let's create a Database Profile now! It's important to mention what this profile actually contains:
  • RDBMS Software Image
  • Database Template
Therefore, it's a better option if you're interesting in cloning both from the existing server.
 
5.1 As mentioned in 3.1, click on "Create" and select the correct Target Type.

NewImage

5.2 On this page, as previously stated, I only changed the "Profile Name".

NewImage

5.3 Review and click "Submit".

NewImage

5.4 Progress is always good :)

NewImage

5.5 After taking a while, the process finally completed.

NewImage

5.6 And as before, the new profile is now part of the library of profile under the "Database Provisioning" home page.

NewImage

Downloading Profiles

6. In case, one doesn't want to create their own profiles, and download them from a different source (OTN), there's a neat feature to do that. On the "Database Provisioning" home page, click on "Download Profile…". This actually takes you to the "Self Update" page, but as you can see, there aren't any new profiles available for download. I would imagine, in the situation where the Oracle Database plugin is updated to the latest 12.1.0.4.0 one which supports 12c Databases, the profiles would be automatically imported. That's only a theory at this point!
NewImage

Conclusion

There's potential here, no doubt about that. You've probably noticed, that I don't talk about licensing during this exercise. There is a management pack you would need to purchase for the Database - unless I am mistaken, its the Lifecycle Management Pack. In any case, if you were Mr. Green, the benefits of this feature would be quite apparent, and well worth the cost.

I hope you enjoyed reading, as much as I did composing this blog. I think, next time, I will likely explore further on Deployment Procedures.

Cheers!

Next in Series: Using Profiles
continue reading "Patch and Provision with EM12c: #2 Database Provisioning - Profiles"