G+ Collections: Its Killer Feature Is Not What You Think

When Google+ Collections were announced a few days ago, I pretty much could hardly contain the resounding "meh" with which I greeted it.  Wohoo for grouping posts by topic as "Collections".

But when playing around with them recently, I realized that this enables the killer feature I've wanted on social media platforms such as G+ and Facebook for years!  Finally, there is hope that I might one day be able to filter out much of the noise I do not care about from those I do want to follow.  Allow me to briefly explain...

Perhaps you're following me, but not interested in my posts about Milwaukee, dislike my Politics, or could give a rat's posterior about what's happening in Information Technology.  Well guess what — you no longer have to be subjected to these in your feed!

And it's ridiculously simple to implement.

In my case, for example, you would visit my Collections page (obviously still a work in progress), and click Following/Follow to change your preference for each.  And while this removes these topics from your feed, the beauty of this is that the posts will still be visible to you should you visit my page directly.

Of course, this does depend on those you follow to create and utilize Collections.  But I'm optimistic that this will see wider adoption as words spreads about the benefits!

I Shall Return... to Postgres Open

Last week, I had the opportunity to attend my first Postgres conference, that being +Postgres Open 2014.  Two days packed with a ridiculous amount of useful information and fantastic Postgres community interaction.  And now that the conference & sponsor t-shirts are washed, many new social media connections have been made, and the backlog of work email has been addressed —

Many Thanks

First off, the program committee deserves a veritable heap of congratulations and gratitude piled upon them from the attendees.  From the quality of the speakers to the well planned (and orchestrated) schedule, and even to the menu — everything was top notch. And therefore, I am calling you out by name, +Kris Pennella, +Gabrielle R, +Cindy Wise, +Stephen Frost, +Jonathan Katz, & +Selena Deckelmann.  Thanks for your time and effort!

The Talks

As with any (great) conference, the difficult part is the inability to attend multiple presentations at the same time, and thus being forced to choose.  The schedule this year offered no shortage of these situations.  But when your choices include community figures of the likes of +Bruce Momjian presenting on "Explaining the Postgres Query Optimizer" and +Simon Riggs with "How VACUUM works, and what to do when it doesn't", you simply can't go wrong.  Nonetheless, I wish I hadn't had to miss out on the talks given by +denish patel , +Álvaro Hernández Tortosa, +Gurjeet Singh, and  +Vibhor Kumar, to name but a few.

Fortunately, the talks were recorded and will be uploaded to the Postgres Open YouTube channel in about a month or so.  In the meantime, the presenters' slides are available on the Postgres Open wiki.

Not Just for DBAs

I particularly want to call +Dimitri Fontaine's talk, "PostgreSQL for developers" to the attention of developer crowd and as being an excellent example of why these conferences are not just for DBAs.  As a consultant, on virtually every job I see examples of poorly written SQL as being the root cause of a poorly performing database (even after proper tuning of config parameters).  And therefore, Dimitri's admonition that you must treat SQL just as you would any other language in which you write code rings particularly true with me.  I tweeted it once, and I'll say it again — this talk could lead to peace and love between developers and DBAs.  (Not to mention that the techniques he teaches in the examples walked through will blow your mind.)

Milwaukee's Best

On a related note, I'd like to point out that +pgMKE, Milwaukee's Postgres User Group, was well represented in Chicago.  +Jeff Amiel's talk on "Row-Estimation Revelation and the Monolithic Query" was described as being worth the cost of attending the conference.  And with myself and +Phil Vacca in attendance, we had a sizable portion of our merry band of less than 10 members present.  Not bad for a user group that's less than a few months old!

Postgres People

As I said at the outset, this was my first Postgres conference.  And I have to add that the people I met were the highlight of the whole affair.  It was a great group, many of whom I'd either never met, or only knew online.  It was readily apparent that the accelerating success of Postgres on many fronts is largely due to the efforts and energy of this community.  It was great to experience it first hand and in action.  It may be stating the obvious, but I'm already looking forward to next year.

Colorizing The psql Prompt — A Guide

The shell can be a bland, boring thing to stare at day after day.

So we shell dwellers spend an inordinate amount of time customizing our text based world. And there is no shortage of examples of impressively pimped out shell prompts that abound on the Internet. Just take a stroll through this wiki on color bash prompts at Arch Linux, for example. Inspiring, isn't it! (If to you it's not, you might as well abandon reading this article now.)

Quite some time ago, I came across +Craig Kerstiens' outstanding intro (which I highly recommend) to customizing your psql prompt, Getting more out of psql (The PostgreSQL CLI). I promptly (no pun intended) applied pretty much all of his customizations to my .psqlrc file, but never quite understood the arcane language of symbols that are responsible for the colorized prompt. So I set about to deconstruct it that I might build my own. The following is a summary of the process used to arrive at the example shown on the left.

---

So, let's begin with the final product and break it down from there. (My format here is essentially an adaptation of a connection URI in libpq.)

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

---

Yep.  Complete gibberish. Let's make things a bit clearer:

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

What we've done here is to obscure what are referred to as 'terminal control characters', leaving only a combination of

• actual characters that will be printed at the prompt ( ➤, psql://, @, \n , etc.)
• psql specific commands and variables (\set PROMPT1)
• psql substitution strings (%n, %#, etc.)

---

Taking it down just one more notch:

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

And you'll find that we are left with sets of matching %[ ... ]% sequences. As described in the docs, these allow the use of terminal control characters that can, among other things, colorize our prompt. And that's what we're after. So let's turn this on it's head and look at what's within those brackets.

---

Inverting our highlighting somewhat:

\set PROMPT1 '\n%[%033[1;31;40m%] ➤ %[%033[32m%]psql:%[%033[36m%]//%n%[%033[34m%]@%[%033[36m%]%`hostname`:%>%[%033[33m%]/%/ %[%033[K%]%[%033[0m%]\n\n%[%033[1;33m%]%#%[%033[0m%] '

So what do we have here? These, my friends, are terminal control codes. And they are the secret sauce we're looking for.

Isolated like this, it's easy to quickly pick out a pattern. Everything begins with %033[. (This is one of the things that was killing me initially. I kept trying to pick out a closing ]. Well, there isn't one!) And everything (except that K, which I will address last) ends with m.  Lets decipher that…

---

There's no end to the references you can find for terminal control codes if you Google for them. Here's a handy one that I find clearly laid out and understandable:

http://www.termsys.demon.co.uk/vtansi.htm

Scrolling to the bottom, we have the display codes.  I've pasted them here for convenience:

Set Attribute Mode
<ESC>[{attr1};...;{attrn}m

Display Attribute 0 Reset ALL 1 Bright 2 Dim 4 Underscore 5 Blink 7 Reverse 8 Hidden

Foreground Colors 30 Black 31 Red 32 Green 33 Yellow 34 Blue 35 Magenta 36 Cyan 37 White

Background Colors 40 Black 41 Red 42 Green 43 Yellow 44 Blue 45 Magenta 46 Cyan 47 White

And based on this format, now we can see that %033 is an escape sequence equivalent to <ESC> and m is, well... m!

---

So the way this works is that when we set a display attribute, foreground color, and background color; they will remain in effect until we change them or reset everything. Stepping through my example prompt (and using a very light gray background in lieu of white for readbility's sake), we have —

 1;31;40 > bold red foreground on black background 

   32    > green foreground 

   36    > cyan foreground 

   34    > blue foreground 

   36    > cyan foreground 

   33    > yellow foreground 

 K       > ...erase to the end of the current line      

 0       > reset all attributes 

 1;33    > bold yellow foreground 

 0       > reset all attributes 

Or, more specifically...

 ➤ psql://database_user@hostname:port/database         

 # 

---

Okay, almost there... But you might be scratching your head at this point. These colors look not even remotely similar to those in my screenshot posted above.

Well, that's where the terminal emulator you're using and the color scheme (theme) you've chosen come in. Your theme will determine what colors the emulator you're using will substitute for the standard black, blue, red, green, and so on.

In my case, I am using iTerm2 on OS X with the 'arthur' color scheme.  You might prefer something such as Solarized or Tango.  But that's is where the fun and creativity of customizing something to your personal taste comes in.  And I hope you now have enough of an understanding of how colors are applied to the psql prompt to go forth and roll your own.

Enjoy!

Taming Your postgresql.conf Changes With Includes

A few weeks ago, my comrade +Douglas Hunley and I were working on a small project together for a customer that involved a fair number of changes to parameters in the Postgres database configuration file, postgresql.conf.  And being the ridiculously anal admin type that I am when it comes to the organization of and commentary throughout my config files for any service, I was beginning to have fits with the havoc this was wreaking upon the relative beauty that is postgresql.conf.  (I blame my years in design engineering working with engineering change requests for this particular trait.)

postgresql.conf is a masterpiece of a configuration file, being ridiculously well documented throughout with a plethora of commentary to boot and parameters grouped by category and functionality, rather than just a straight alphabetical listing.  The numerous edits being made, plus my penchant for thorough commentary on each change was breaking up the flow of the file.  The result was not making for easy reading.  And the more I tried to address that issue, the less the changes that were being made stood out.

And then I recalled those wonderful includes in the Apache config files I used to know and love, wondering if there was any chance Postgres might have a similar capability.  Praise be to the Postgres Docs, it does.  Just throw them at the end of the file and they'll override any previous settings!

Okay, so why do this?  Consider the elegant simplicity of organization includes provide...

---

Easily Set Standard Configs For Related Parameters

Say, for example, your organization has a standard logging config you want running on every server.  You might consider having a standard postgresql.conf file with these parameters set.  But what if there are physical differences between the servers that affect other parameters, such as work_mem, effective_shared_cache, etc.?  Or your WAL settings differ?  Or autovacuum?  You can easily see where this is going.

Organization That Self Documents

Whether you work with many databases or just one, you're eventually going to return to one after enough time has passed for you to have forgotten everything you (or someone else, for that matter) you'd set and/or why it was set that way.  Let's say your organization comes up with a standard naming convention for these files.  As I work for +EnterpriseDB (EDB), I might use this to name my files:

    edb_logging.conf
    edb_tuning.conf
    edb_vacuuming.conf

and so on.  Now, when you look at the directory listing it becomes readily apparent fairly quickly where to look for any custom settings I or my colleagues may have made, doesn't it.

More Thoroughly Documented Changes

Being a huge advocate of not only clear and thorough documentation within configuration files, but also maintaining a record within of previous settings, dates of and reasons for changes and so on; I find this method allows much clearer and more readable information.  Some night consider this overkill.  But if I'm tasked with troubleshooting why sorts & merges on disk have recently dramatically increased, and I take a quick stroll through a file that might be named edb_memory, finding an entry akin to the following:

# change date:      2013-08-01
# previous value:   20MB
# new value:        5MB
# change by:        jgraber@edb
# reason:           let's see what happens!

    work_mem = 5MB

I'm going to be torn between buying this jgraber guy a beer for great documentation of changes, and smashing the bottle over his head for monkeying with this for no apparent reason.  But at least I've potentially saved a tremendous amount of time and frustration wondering what happened.

(Yes, you could do this in postgresql.conf, no question.  But imagine what that already heavily commented file is going to become over time as these changes are made.)

---

Okay then, go include some stuff... and things!

Book Review — Instant PostgreSQL Starter

Following up on the positive experience I had recently with another of Packt Publishing's "Instant" titles for Postgres, I picked up a copy of Instant PostgreSQL Starter and dove right in.  (And I must say I'm enjoying these short format books.)

The first major section of the book seems targeted at someone with absolutely no experience, or perhaps minimal experience, with databases whatsoever.  Yet I still found it useful, as I'm coming to Postgres from an Oracle direction.  Simply having to walk through the installation process, perform the basic table creation, inserts, updates, queries, etc. that you will be walked through provided an easy way of becoming more familiar with the nuances of the database and its GUI admin tool, pgAdmin3.

As a brief aside — I think the author's choice of going down a fairly platform agnostic path by utilizing +EnterpriseDB's installer and interacting with the database through pgAdmin3, rather than psql in a shell, was a good one.  It provides a one size fits all approach for anyone to get up and running without having to delve into the nuances of shells on different systems, various installation methods, and so on.

What I think was the most valuable portion of the book for me was "Top 9 features you need to know about", which gives an overview of such topics as hashing passwords for storage, XML in Postgres, and full-text search, to name a few.  For someone coming to Postgres from another database, it's learning about these kinds of features that truly help you get up to speed a bit faster.  And I appreciate being able to get a brief overview of these topics in order to simply know about them and how they work, without becoming an expert in any (just yet!).

Book Review — Instant PostgreSQL Backup and Restore How-to

I recently was fortunate to receive an ePub copy of “Instant PostgreSQL Backup and Restore How-to” from Packt Publishing for review. The topic obviously high on the list of skills any DBA must master. And given my still relatively recent foray into the realm of Postgres from Oracle, quickly getting up to speed on this is certainly a top priority for me.

From the outset, the book does not disappoint. Like any of you, I'm sure, I have a ridiculous number of tech books in my library. This, however, is my first publication from Packt. And I must say, I definitely enjoyed the short, directly to the point format. I'll likely be taking a look at other Packt titles if for no other reason than that.

Author Shaun Thomas had my immediate gratitude and admiration for providing an extremely quick, concise method for creating a reasonably large database to work through the methods presented. Again, as a relative newcomer to Postgres, with no Postgres instances in use at my organization, I found this invaluable.

And without any delay, you will roll right into the basics of backup of your databases. Full, partial, data only, schema info, specific tables... it's all there within the first few pages. In only a few minutes after beginning the book, you have enough information to have a backup of your database underway. Well done.

At the time of this writing, I have not yet made it to the following chapters on more advanced topics. But there is no question I'm looking forward to them given my experience so far.

(And I would add that backup and recovery of Postgres seems immensely easier that what I've experienced with Oracle. The command line tools are outstanding, from what I've seen so far. And as usual, they a much more "Unix-like", for lack of a better term, than what Oracle provides. That is a huge plus, in my opinion.)

I'd highly recommend this book. Its concise coverage of the backup and recovery of Postgres will have you up and running in no time. And who has time to waste when it comes to developing proficiency in anything data related these days.

Big Cats & The Elephant

In my experience, it has been an exceedingly rare thing to be able to get a database up and running quickly and easily for the sole purpose of having one to play with.  Oracle is a beast to install, for example, and SQL Server loves to run you through endless wizards.  MongoDB was the notable exception to this, and this was therefore of the reasons that I first jumped on board so eagerly to give it a test drive.

But even the simplicity of downloading the MongoDB binaries for OS X and starting it up with the right flags on the command line falls short of the mind-numbingly incredible ease with which you can get PostgreSQL up and running on a Mac with Postgres.app!

What were are talking about here is

• download
• unzip
• move to Applications
• run it

No joke.  You now have PostgreSQL running on the default port of 5432.

The documentation will open by default.  I suggest adding the path to the Postgres binaries to your own PATH, and this information is provided in brief doc that will open.  This is not absolutely necessary, however, as one can start the psql shell (analogous to SQL*Plus) from the menu that will appear if you click on the small elephant that will appear in your menu bar.

While I plan to go through the steps to get Postgres started on CentOS Linux at a later date for more complex, multiserver configurations; I can't overstate how highly I recommend this for its elegant simplicity.  Many, MANY thanks to Mattt Thompson and Heroku Postgres for making this available to the masses!

Herding Elephants

It may be readily apparent that from its outset, this blog has lacked focus. And as it has sputtered along and been neglected at times, I've wondered where to take it and what I should focus upon. And I think that question has now been answered.

Over the past few months I've become quite enamored of the PostgreSQL database. Obsessed you might say. So much so, in fact, that I've decided to make it the new focal point of my career after many years as an Oracle DBA. I think the future for Postgres is very bright. It's the only open source database that I would put in the same league as Oracle, and also has many features that would make even a lover of the non-relational, NoSQL movement a happy camper. (And with the ridiculous pricing structure for Oracle these days...)

My hope is that by making a commitment to myself to use this blog as a record of my successes and failures in attempting to transition (translate?) my knowledge of the Oracle database into some sort of mastery of PostgreSQL someday, I will not only be motivating myself to keep the process rolling along, but also leaving a trail of breadcrumbs for other Oracle DBAs who find themselves among the elephants.

Google+ As Your Central Hub

The Need For A Central Hub

You might ask yourself, why do I need a primary presence online?  Can't I just use tools like HootSuite, Streamified, etc. to post to multiple sites/streams?

Absolutely, you could.  And tools like these will remain invaluable even if you do centralize to Google+. But what you will likely find (if you have not experienced it already) is that as the frequency of your interactions online increases, and you become more prolific in posting your own original material or sharing other's is the following:

1. It becomes increasingly difficult to adequately maintain a presence on all of these platforms.
   
   This is especially true when it comes to interacting with your followers, peers, individuals and organizations you follow, and so on.  And without a great deal of discipline, you will find yourself going through periods (of increasing frequency and length) in which you are completely neglecting your online persona.
    
2. Maintaining any kind of synchronization of your "digital life" (contact info, calendars, files, etc.) becomes extremely tedious and time consuming.
   
   Did I get that event I signed up for on LinkedIn into my calendar?  What was the contact info for that person I interacted with on Twitter?  How will I share your presentation from last night's user group meeting?  What can I do to simplify keeping my calendar and guest list in sync for a live, online event I want to plan?
    
3. It's not unlikely you'll eventually need one or more separate page "entities" representing a group, product, etc..
   
   If you are managing a user group, for example, it's best to have a separate page of some sort for this purpose.  Direct integration between this page and the professional persona account you use regularly use becomes invaluable when you are trying to remain organized and on top of everything!

The next post in this series will delve into the why Google+ is your first, best option to serve as your central hub when stacked up feature for feature with several major social platforms.  Stop by again soon!

---

Google+ For Your Professional Persona

A few weeks ago, I made a brief post on Google+ that attempted to summarize how and why I've seen my G+ usage steadily increasing over the last few months.  That trend has continued to a point where I have found myself in not a few conversations not regarding G+ as a replacement for my personal Facebook account (even though it has been), but rather focused upon the use of Google+ as a central hub and social glue for my professional persona online.

We all have our motivations for what is common referred to as "building your personal brand" online.  And by and large, there are many different paths you can take in this pursuit.  Invariably, however, we all seem to have all or some combination of the following in common:

• Twitter feed
• LinkedIn profile
• professional blog
• Facebook profile
• and ever more increasingly... a Google+ account

And this presents a challenge.  What will you use as your primary presence?

What follows will be a series of blog posts on using Google+ as the hub for your professional life online.  To get started, in my next post I'll be focusing on why utilizing G+ as your hub to leverage the power of the entire Google ecosystem is the most practical answer to this question.

---

MongoDB In The Brew City

Change is the one constant!

When I started this blog a few months ago, my intention had been to be (yet another) primarily Oracle focused blog.  But as I had described in my previous post regarding my new found Mongo-curiosity, I had become very interested in MongoDB, a non-relational database that seems to be exploding in popularity.

Why?  I think the best answer to that that I have read to this point was posted on MongoLab's blog.

"Why is MongoDB wildly popular? It’s a data structure thing."

For the past couple of months, I've been immersing myself in all things MongoDB - webinars, tutorials, eBooks... And I must say, I really like this technology.  And I find myself chomping at the bit to learn everything I can, and then some, about the deployment and administration of MongoDB databases - possibly even picking up a little development knowledge along the way.  The challenge lies in that I've been unable to develop a use case for it at work that will allow me to develop the knowledge and skills I'd like to have.

So I formed a user group!  BrewMUG - Milwaukee's MongoDB User Group

With BrewMUG, I hope to create a community of MongoDB developers and DBA's to

• foster interest in MongoDB in a social atmosphere
• provide a forum for learning the in and outs of working with MongoDB
• network with other professionals interested in deploying MongoDB
• enjoy pizza and drinks with IT geeks ;)

Whether you've never touched MongoDB or you've deployed it in production and you're located in southeastern Wisconsin, please join our Meetup.com group!  We are also active on Google+ and post to Twitter with updates and important FYI's.

We hope to see you at the inaugural meetup!

Mongo-curious

Hi.  My name is John... and I'm Mongo-curious.

I think it's safe to say that you can't possibly be involved in databases today and not have heard the terms Big Data and NoSQL, usually in the same conversation.  Coming from a relational database background, as I expect most of us are, I've found the idea of non-relational databases intriguing to say the very least.  And I've been trying to become more familiar with the concepts involved.

The problem, as you may be well aware, is that there are no universal definitions as to what NoSQL is nor what Big Data means.

As I mentioned on Google+ today, I was reading an article on GigaOM entitled "MongoDB or MySQL?  Why Not Both?"  And that prompted me to spend a little time looking into MongoDB (which I've been curious about if for no other reason that the name is cool and I've heard it works well with Python).  Which brings me to the reason for this post...

I came across a free eBook — "The Little MongoDB Book" — that is an outstanding, brief introduction to MongoDB, by Karl Seguin.  I read it (in iBooks on my iPad) in less than an evening!  The chapter on MapReduce alone made the time spent worthwhile!

I'm looking forward to diving into the examples provided in the book over the weekend.  But I'd like to thank Karl for providing such an excellent and free resource to us all!

Renaming Your Oracle Instance, Part 2

Alrighty then...  Let's finish this thing up, shall we?

As I had mentioned in part 1 of this 2 part post, I don't feel that a database rename is complete until all of the associated file system structures also reflect the name change.  I mean, how'd you like to be the next DBA to be working on this database, now named 'JGDB', and not be able to quickly locate the data files because they are still under $ORACLE_BASE/oradata/orcl rather than $ORACLE_BASE/oradata/jgdb, as you'd expect?

---

First, let's shut down the database and put the init.ora file where it belongs and name it such that we don't have to define the PFILE location when starting the instance.

SQL> shutdown

SQL> exit

$ mv /home/oracle/jgdb_init.ora $ORACLE_HOME/dbs/initjgdb.ora

Another piece of housekeeping to attend to, if you haven't done so already, is to change your environment variables.  Mine are being set in ~/.bashrc

export ORACLE_SID=jgdb
export ORACLE_UNQNAME=jgdb # set for 'emctl start dbconsole'

---

Now we start back up again, without designating the PFILE location, as it should be found automatically now that we have renamed it and placed in in the proper location.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 23 11:57:43 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

---

My steps below are based upon the following Oracle docs:

Creating Additional Copies, Renaming, and Relocating Control Files
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#i1106242 

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#i1006277

Relocating and Renaming Redo Log Members
http://docs.oracle.com/cd/E11882_01/server.112/e25494/onlineredo004.htm#i1006447

---

We have three major items to move: data files, redo logs, and control files.

I like to avoid any work that the system can do for me.  (I like to call this 'efficiency'!)  So, I will use SQL to create the SQL needed to update the database with the location to which I will be moving the redo logs and data files.  I saved the following as create_rename_script.sql

set linesize 200
set pagesize 100
set heading off

spool rename_files.sql

select
    'ALTER DATABASE RENAME FILE ''' ||
    file_name ||
    ''' TO ''' ||
    replace( file_name, 'orcl', 'jgdb') || ''';'
from
    dba_data_files
/

select
    'ALTER DATABASE RENAME FILE ''' ||
    member ||
    ''' TO ''' ||
    replace( file_name, 'orcl', 'jgdb') || ''';'
from
    v$logfile
/

spool off

You should now have a file named rename_files.sql in the directory in which you ran the script.

Shut down the database.  It's time to move stuff... 'n things...

SQL > shutdown

SQL > exit

---

Taking a look at initjgdb.ora, you will note that there are a couple of parameters that refer to OS file system locations with 'orcl' in them.  They are listed below.  I have replaced 'orcl' with 'jgdb' as we did in part 1.

*audit._file_dest='home/oracle/app/oracle/admin/jgdb/adump'

*.control_files='/home/oracle/app/oracle/oradata/jgdb/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/jgdb/control02.ctl'

Moving the redo logs, data files, control files, and adump destination...

$ mv $ORACLE_BASE/oradata/orcl $ORACLE_BASE/oradata/jgdb

$ mv $ORACLE_BASE/flash_recovery_area/orcl $ORACLE_BASE/flash_recovery_area/jgdb

Because we've updated initjgdb.ora with the location of the control files and adump, we can start the database up again and mount it.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 23 12:15:32 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Now we can update the data file and redo log locations using our script rename_files.sql that we created earlier and open the database for business...

SQL> @rename_files.sql
Database altered.
.
.
.
Database altered.

SQL> alter database open;
Database altered.

Done!

---

If you have any feedback whatsoever on the steps I followed to rename my Oracle database, I'd be grateful for your input!

---

Scratching The Surface of Apex

---

I've always had a big streak of developer/analyst in me, especially when it comes to developing web applications that interact with data.  And as a result, I've been itching to start working with Oracle Application Express (Apex).  What's Apex?  As described by Oracle, it's...

...a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle APEX is a fully supported, no cost option of the Oracle database.

Perfect!  Exactly what I like.  Something simple, yet powerful. Something I can rapidly prototype, but can add seriously heavy duty features to in the future if needed (such as Active Directory authentication, for example).  So yesterday, I got started with the 2 Day + Application Express Developer's Guide tutorial.  All I have to say so far is WOW.  I'm LOVING this product.

That said, I highly recommend watching the brief, 3 minute flash video on Oracle's site for a great overview of Apex.  And I'll leave you with something I found very interesting about Apex that impressed me greatly with regard to Application Express' capabilities — Oracle's Support site, Metalink, was developed in Apex!

---

Renaming Your Oracle Instance, Part 1

For DBA's, there's nothing better or more useful than having your own database to play in. How else are you going to learn, practice, and try new features?

So with that in mind, I recently set up an Oracle Linux 6 virtual machine running on Oracle VirtualBox, downloaded Oracle Database 11g R2, and installed it with DBCA. Not a very difficult process, and I now had an up and running instance with the default name of ORCL.

And that presented a problem, in that even though this is going to be playground of sorts for me, I do intend to have it on the network. The default, generic SID of orcl is likely going to be a problem. I needed to rename my database. I'll be renaming it to jgdb.

While I could have just started over, I thought it would be an worthwhile exercise to try renaming the database and modifying it's various components to reflect the new SID, as it's not generally the kind of this you're doing every day at work. The following is the process I went though, starting with the DBNEWID utility.

---

To get started, I used the documentation on DBNEWID for 11.2 found here.

1) If you using a binary SPFILE (the default when your database has been created by DBCA, as mine was), you'll want to back it up to a text init.ora file.  This is needed to start the instance after DBNEWID does its magic.

SQL> connect / as sysdba
Connected.

SQL> create pfile='/home/oracle/jgdb_init.ora' from spfile;
File created.

---

2) Run DBNEWID from the shell.  (I'm on Linux, running the bash shell.)

$ nid TARGET=SYS DBNAME=jgdb SETNAME=YES

DBNEWID: Release 11.2.0.1.0 - Production on Thu Apr 19 10:08:32 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Password: 
Connected to database ORCL (DBID=1308307467)

Connected to server version 11.2.0

Control Files in database:
    /home/oracle/app/oracle/oradata/orcl/control01.ctl
    /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ORCL to JGDB? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to JGDB
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name
    Instance shut down

Database name changed to JGDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

---

3)  As you can see above, DBNEWID shut down the instance.  So now we edit the text init.ora file to reflect the new database name.  Below, is jgdb_init.ora that we exported earlier.  What I have done is boldface everywhere that I replaced 'orcl' with 'jgdb'.

What you may notice, is that I have NOT changed the various file system paths in which 'orcl' appears.  Now's not the time, nor is it strictly necessary.

Also, I did change the name of the local listener.  This change was not actually required.  However, my goal here is not just a database rename.  I feel strongly that, if possible, everything that referenced the old SID should be modified for the sake of consistency.

jgdb.__db_cache_size=209715200
jgdb.__java_pool_size=4194304
jgdb.__large_pool_size=4194304
jgdb.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
jgdb.__pga_aggregate_target=293601280
jgdb.__sga_target=545259520
jgdb.__shared_io_pool_size=0
jgdb.__shared_pool_size=314572800
jgdb.__streams_pool_size=4194304
*.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl'
    '/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='gotoracle.com'
*.db_name='jgdb'
*.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jgdbXDB)'
*.local_listener='LISTENER_JGDB'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1

---

4)  Having changed the name of the local listener, we now need to modify the tnsnames.ora file to reflect the change.

# tnsnames.ora Network Configuration File:
#    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_JGDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


JGDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jgdb.gotoracle.com)
    )
  )

---

5)  At this point we should be able to start the database using our PFILE that was exported and modified earlier.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 20 11:33:25 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup pfile='/home/oracle/jgdb_init.ora'
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size      2217952 bytes
Variable Size    620759072 bytes
Database Buffers   209715200 bytes
Redo Buffers      2412544 bytes
Database mounted.
Database opened.

---

Great! We're up and running. But we are not completely done. In my next post, we will be relocating the control file(s), redo logs, and data files from $ORACLE_BASE/oradata/orcl to $ORACLE_BASE/oradata/jgdb for consistency.

---

On to Part 2...