Setting up a logical standby database

Never having done this before, I wanted to give this a crack. Also, I was recently handed a project where we will either be using Streams or a Logical Standby. I was happy to learn that these two solutions are very similiar; one might even ask what the difference is. In the conventional sense, I think "Streams" implies a technology that is geared towards a set of objects, whereas logical standby is used for a whole database. At least, that is my impression early in the game.

To get started, I started reading the Oracle documentation about logical standby databases. This can be found in Chapter 4 of the Data Guard Concepts and Administration. Since I am using a really small and simple database (only non-system object is the demo SCOTT schema), I skipped the perquisite checks and stuff and jumped ahead to the part about setting up a Physical standby first. I got my primary all set up and used RMAN to make a physical standby. One can follow the directions from Chapter 3, but I have done a bit with RMAN duplication so I like it better, plus Paul Moen of Phythian has a great blog entry that details a simple recipe. In my example, I have the primary and the standby on the same host (remember, I said simple); for some reason, lock_name_space is deprecated in 10gR2, and I have been unable to find an alternative. So I use it anyway. One thing to keep in mind is that when using lock_name_space, you wind up with an instance_name that is not equal to your service_names (by default). TNSNAMES will have to point to the instance_name for the time being.

With a working, viable physical standby setup, I move on. The next couple steps are fairly straight-forward; stop the apply process in the standby and build the data dictionary in the primary redo logs. I am not concerned about role transitions (except transitioning the physical standby to logical, but the role does not change). The documentation makes a point about creating standby redo logs; I believe these are critical, but I still have a lot to learn about them. I created them, hence I also added a destination for local redo (as opposed to redo coming from the primary). What is disconcerting to me is the volume of redo that is generated on the standby; next time I will have to make them bigger (and possibly more of them, something to keep in mind when creating the controlfile with maxlogfiles).

The process of transitioning to a logical standby is actually pretty cool. Cool because it is so simple, yet the result is a bit complex. First, one has to convert the database:

ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;

This is where the lock_name_space and tnsname entry comes back to bite us (with the benefit of hindsight). If one is using a pfile (as I am), the in-memory name of the database will actually change, giving a warning about changing it in the pfile later on. I was impressed. However, now that the name has officially changed, make sure you remove lock_name_space, and that the name can be resolved properly by tns. Would be a good idea to actually pay attention to that warning and make the change in the pfile permament, as well. Another tricky thing about this operation is that it is expecting to read the data dictionary from the redo log. If it cannot find it, the operation will hang (forever).

After converting the standby, following the documentation, one has to shut it down to make some other changes, including the password file and a destination for local redo. With that, we are on the home stretch with the last couple commands:

ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Again, this is some slick bit of operation. As the documentation says, the standby is now essentially an equal copy, but "transactionally different". You have a fully functional database open for read-write, but still accepting redo from an external source. None of the messy overhead setting up a Streams database with various CAPTURE, PROPAGATE and APPLY handlers and various RULEs.

I did run into a bit of a problem right away, and still continue to struggle with this.

ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1041)
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
PING[ARC1]: Error 1041 when pinging standby STANDBY1.

oerr ora 1041
01041, 00000, "internal error. hostdef extension doesn't exist"
// *Cause: Pointer to hstdef extension in hstdef is null.
// *Action: Report as a bug

Nice error message, eh? Looking at Google, this error has been around in some form or fashion since Oracle 7. Yuck. For some reason, whenever my logical standby is bounced, the primary looses the handshake. I can resolve this by bouncing the primary, but of course this is far less than optimal, and I need to figure out what it is doing. Rather annoying. At first I thought it was because the listener was registering the standby instance fast enough, but after adding an entry in listener.ora, I was still getting this error.

I also played with creating a conflict on the standby by updating scott.emp in the standby, then the primary. The standy noticed the conflict right away and made a note about it, registering subsequent redo logs.

LOGSTDBY stmt: update "SCOTT"."EMP"
set
"SAL" = 810
where
"EMPNO" = 7369 and
"SAL" = 800 and
ROWID = 'AAAAAAAAEAAAAAyAAA'
LOGSTDBY status: ORA-01403: no data found
LOGSTDBY id: XID 0x000a.015.000005c1, hSCN 0x0000.000372b0, lSCN 0x0000.000372b0, Thread 1, RBA 0x00b8.0000002a.10, txnCscn 0x0000.000372b9, PID 18086, oracle@xxxxxx (P004)
LOGSTDBY Apply process P004 pid=25 OS id=18086 stopped
Wed Feb 28 11:08:39 2007
Errors in file /u01/app/oracle/admin/STANDBY1/bdump/standby1_lsp0_16624.trc:
ORA-12801: error signaled in parallel query server P004
ORA-01403: no data found
LOGSTDBY Apply process P006 pid=27 OS id=18095 stopped
LOGSTDBY Apply process P007 pid=28 OS id=18098 stopped
LOGSTDBY Apply process P005 pid=26 OS id=18092 stopped
LOGSTDBY Apply process P008 pid=29 OS id=18100 stopped
Wed Feb 28 11:08:43 2007
LOGMINER: Log Auto Delete - deleting: /u02/oradata/STANDBY1/log_STANDBY1_615653782_1_183.log
Deleted file /u02/oradata/STANDBY1/log_STANDBY1_615653782_1_183.log
LOGSTDBY Analyzer process P003 pid=24 OS id=18082 stopped
Wed Feb 28 11:10:15 2007
RFS[1]: Successfully opened standby log 3: '/u04/oradata/STANDBY1/redolog3STANDBY1.log'
Wed Feb 28 11:10:15 2007
RFS LogMiner: Client enabled and ready for notification
Wed Feb 28 11:10:15 2007
RFS LogMiner: Registered logfile [/u02/oradata/STANDBY1/log_STANDBY1_615653782_1_185.log] to LogMiner session id [1]

I still need to read about this registration process. Fearing that I would have "reinstatiate" the table (ie, like I might have to in Streams), all I did was update the table again back to what it was on the standby (no, I did not rollback because it was already committed; it was pretty much like a flashback), and restart the APPLY process. Very smooth and very intelligent.

Obviously, my early steps in Logical standbys and Streams in general are nothing new or earth-shattering for those who have already been around the block a few times. However, coming this far, I have to wonder why Streams is so difficult. For that matter, why are many other features so mind-boggling complicated and difficult to setup (LDAP or Advanced Replication just to name a couple)? Kudos to whoever worked on Streams, Logical Standbys in particular. While I still have some gripes about Streams in general, this exercise has given me some hope and a glimpse at a brighter future.

Me and my job

Passing the OCM was a major accomplishment for me, a milestone in my career path. Hence my continued excitement; I have not had this strong of a sense of achievement... since... for a long time. *grin* And I truly believe it is "merely" a chapter in my career, a leg of the journey as I sojourn this enigma I call life. Preparing for and passing this difficult exam is not an end-all-be-all. Evidenced by the fact that there are many DBAs out there who are smarter, faster and more accurate than I am. The small fact that not many people have passed this exam is probably more of a testament to its secrecy and high price tag. Which is why I emphasize that the OCM is a stage, a rung of the ladder. With that image in mind, my end goal is to be really good! I read and converse with "Oracle Experts" like Tom Kyte, Jonathan Lewis, Dave Ensor and a large number of others whom I will not list out; if possible, I hope to claim that caliber at some point in my life. I call it "Striving for Excellence".

And here comes a helping of Humble Pie. Sure, just like any other human, I want to puff up my chest of pride, I want the world to recognize how good I am. Reality check - I am not so good that the world would even notice. If there were a DBA version of American Idol, I am sure all three judges would say something like "It was ok". "Not great, dog". "Good, but not good enough".

But I yearn and strive to be really good at what I do. This blog is cast in the context of my job, an Oracle DBA; yet, in the rest of my life I strive for excellence as well. Father and husband are at the forefront, but also in serving my community and being active in local government. One of the biggest reasons for this desire goes back to my belief that there is a God (and I am not He); all my gifts and strengths I credit to my God. Without His blessing, without His favor, I am nothing. Hence, it only makes sense to use what I have to bring Him honor, to make good on His investment. All too painfully I am often aware that my life is sometimes at odds with this view; I screw up. I, like you and everyone else, make mistakes. So while my goal is to be excellent, I have to be careful in depicting the fact that I am not there, yet. It is a dance, a choreographed movement with my heavenly Partner who leads my steps.

This is why I strive for excellence, to do what I can to make the big guy upstairs look good. And believe you me, I am thankful that He allows me to continue, despite my wretched performance from time to time. So I proudly add this label, this certification, to my somewhat short list. I am happy to be an Oracle Certified Master. Very happy!

Oracle has deemed me fit to be Certified as Master

Wow, what an awesome feeling!! More later - today I am busy telling folks and planning for the future. =)

A probing question

One of my OCM proctors (himself a 9i/10g OCM) poses this question to determine how well you understand Oracle architecture:

How does a transaction get processed in a Shared Server Installation?

The idea is that if you answer in a flat 5 minutes, you are screwed. =) One should first understand how a transaction is processed in a single-threaded (easy) environment, and then know the multi-threaded architecture well enough to incorporate knowledge of how a transaction is processed. He said that the answer should take about 30 minutes or so.

I bring this to light because I am making this one of my goals. I need goals to shoot for because I often find that I need a sense of direction. I am one of those people that, when mowing a lawn, cannot walk straight unless I set my eyes on my destination. Funny, that.

OCM: wrapped up?

I still have not heard how my practicum was scored. I am hoping I passed - I think I did well enough to warrant it. I know there were a couple sections where I hit some Oracle bugs and it screwed me up a little, but I came back and didn't get sucked into the riptide.

A few things become apparent to me, as well. I really have to admit I prayed a lot during the test. =) I was reminded of St. John quoting Christ:
"I am the vine; you are the branches. If a man remains in me and I in him, he will bear much fruit; apart from me you can do nothing." (John 15:5)

Sure, I think I am hot stuff taking this prestiguous exam, but in all reality, I have to count my blessings. I have granted unique opportunities (essentially, the opportunity to be who I am). Yes, I made choices that shape me, but I truly believe that God opened up all the doors that let me come down this path, and He closed all doors to paths I have not, or could not, traverse.

Additionally, I have to consider what passing, or even taking, this practicum is all about. I wrote about this before, but I continue to think about it. I do have a burning desire to hone my skills, recognizing that I still have a long way to go. I want to be a better DBA, and I have many role models to look up. The folks at oracle-l are awesome, and I appreciate that many experienced and intelligent people have opened up their lives a little to less us mere mortals take a peek inside and glean some beneficial morsel here and there.

OCM take 2

I was very glad to hear that my OCM was rescheduled. I am finding it much easier to practice now that I know exactly what is going on. Ironically, I decided to wipe out my test Oracle installation and, lo and behold, you can no longer download Oracle 9.2.0.1 (and patch to 9.2.0.2). I tried download 9.2.0.4 but the agent is broken for Redhat Linux AS 4. That sucks. I found several helpful links from Google, but not quite the silver bullet.

Which brings me to another point. Why does Oracle

1. use such an outdated version of the rdbms (one that is buggy to boot)?
2. not provide the necessary software for downloading?
3. in general, why is support for Linux so shoddy?

The last one is a prickling one - see my other post about Oracle Linux Support.

But, just in case you were getting the idea that I was being critical and unappreciative, let me make it very clear that I am very happy and very excited to take another stab at the OCM. I have been practicing a few times over the break, and continue to do so. Should be good. What happens if there is another "technical difficulty"? *grin*

In closing, I am very curious what the 10g OCM is like. Our proctor, Jay Norton, had a primary role in writing and creating the 10g OCM, and he said it was even more difficult than the 9i practicum. Does Oracle Education offer a fee waiver or subsidy upgrade OCMs? =)

Morale at the workplace

It never ceases to amaze me how "official" our upper-admins try to be. First, we caught the wave a hiring and listening to consultants (Gartner Group comes to mind); I have never seen any benefit attributed to those exercises, except several departmental reorganizations, music chairs and the top folks getting richer. Thankfully, some small bit of sanity has returned in the last year or two, but there is still a mentality of "running a good business" that does not translate well when trickled down through the ranks. The latest "trend" is getting certified in ethics, sexual harassment awareness and security protocols. The same thing, year after year, and at a time when our elected state officials are the worse role models in the world.

But I digress...

Morale. Like a perfectionistic parent, it often seems that the message to use in the trenches leans towards "don't do this" or "do this". There is little positive reenforcement for jobs well down, and even less obvious efforts to draw the organization together; skimpy efforts at community building events, lackluster performance evaluation system that leads to general confusion about raises, and even greater confusion about priorities. Too much negative emphasis, too much micromanagement.

The people that stand out are those who lean towards workaholism, whether it be because they feel compelled to work harder or some other reason. The average joe is essentially ignored, and the "operating procedures" from on high can be seen as oppressive measures.

I love my job. I love figuring out problems, exploring new features of the RDBMS, and working with others on a project. Unfortunately, those projects seem all to often like black holes to me - hopefully, surely, somebody has a clear idea why we are investing our time in various projects. Not just "because someone wants it", but how is it progressing along our company's goals.

I love the people I work with; a diverse group in terms of culture, talent, personality and ambition. All sorts of strange conversations come up. =) But better yet, it helps to develop me as a person and DBA.

I guess it would be nice if things were more fun. We work hard, and often the rules we have to follow feel like punishment. I wonder if this is to keep the rebellious few in line, or because some talking head suggested that it was better this way. Whatever it is, it certainly makes me glad I am not running the show.