Donald Burelson on DBA Personality Types

I came across this article "What Type of DBA Are You?" by Donald Burelson. Found it interesting and a little hilarious, especially the last part about having more than 1 DBAs working together.
He categorizes DBAs into 3 types. Try to find what DBA Catagory you belong to.

Project Implementation Experiences

Just thought of sharing an Exerpt from a PM Article "Project Memoirs: Real Projects, Real Lessons" written by Micheal Wood on http://www.gantthead.com/, which might be of interest to Developers and DBAs too. Eventually, we would all be climbing up the ladder and may face similar decision making situations. Here it goes...

Lesson 4: Focusing on FTE efficiency can derail real progress.
One of the lessons that has paid recurring dividends over the years has been learning the difference between working lots of hours versus achieving project outcomes. Yes, I am talking about the dreaded Full Time Equivalent (FTE) utilization metric.
One project comes to mind that might illustrate why, as a project manager, I tend to focus more on project progress than labor utilization rates. It was the mid-1990s, then I lead a project to help reinvent the way Showboat did direct marketing. The goal was to develop a direct marketing patron repository that could be used to create targeted campaigns that would have predictable response rates well above industry averages, and drive revenues to new levels while reducing direct coin offers by $5 million a year.

The project needed to be completed in about six months and was being viewed as just one more of the projects in an already overburdened project portfolio. Maybe it was instinct (or maybe experience) that told me that a project this ambitious and with so much potential payback should not be treated in a traditional manner. So I fielded a dedicated team of 12 to do the project knowing that there would be times when only six or so of the team would be fully utilized. Traditionally, down time on a project is backfilled with other assignments, but not this time, not this project.
Despite extreme criticism from management, I stuck to my guns. When people had gaps in their work schedule I gave them time off, sent them to training (or anything else that didn’t put them in the critical path of another project) or stuck them into a production support role. The project cost about $400,000 to complete with a monthly processing cost of about $26,000, as the repository was housed off-site. The average productive hours worked per day were about five. From a staff utilization point of view, that project was a failure.

However, from a return on investment point of view, the project was an overwhelming success. Had the project been resourced to take six months, the lost value would have been over $400,000 a month in wasted coin costs alone (you do the math). By focusing on project outcomes and not contracting a FTE utilization rate, about $1.2 million dollars was driven to the bottom line. (Now that was a lesson worth learning!)

Most seasoned project managers have a treasure trove of lessons they have learned over the years. Some lessons came at the cost of failure, others with success. What is important is that each of us continues to learn and improve our project management talents.

Configure Enterprise Manager (Database Control) Manually

Oracle Enterprise Manager - Database Control (dbconsole) is something that usually installed and configured automatically whenever you install a new Oracle 10g database, using Database Configuration Assistant.

I was wondering, if i was to delete the service or remove Enterprise Manager - Database Control, how is it possible to bring back EM! This is what I did to figure manually install and configure the Enterprise Manager for my 10g Test Database.

First, I dropped my repository to remove the EM-Database Control and delete the EM Service.

C:\>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Nov 8, 2009 2:23:59 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: db10gee
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Nov 8, 2009 2:24:20 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
C:\oracle\product\10.2.0\db_2\cfgtoollogs\emca\DB10GEE\emca_2009-11-08_02-23-59-PM.log.
Nov 8, 2009 2:24:24 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 8, 2009 2:24:31 PM oracle.sysman.emcp.EMReposConfig stopDBMSJobs
WARNING: Error initializing SQL connection. SQL operations cannot be
performed
Nov 8, 2009 2:24:31 PM oracle.sysman.emcp.EMReposConfig invoke
WARNING: Unable to remove DBMS jobs.Nov 8, 2009 2:24:31 PM
oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Terminate batch job (Y/N)? n

C:\>emctl status agent
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights
reserved.
---------------------------------------------------------------
Agent is Not Running

C:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights
reserved.
http://zaffark:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The service name
is invalid.

More help is available by typing NET HELPMSG 2185.

C:\>NET HELPMSG 2185

The service name is invalid.

EXPLANATION

You tried to start a service that is not configured on this
system.

ACTION

Check the spelling of the service name or check the configuration
information for the service using the Services option from Server Manager.

So, the EM-Database Control has been removed and the EM Windows Service has been deleted. So, let's create the Repository and install Enterprise Manager-Database Control.

C:\>emca -repos create

STARTED EMCA at Nov 8, 2009 3:23:24 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DB10GEE
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Nov 8, 2009 3:23:41 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
C:\oracle\product\10.2.0\db_2\cfgtoollogs\emca\DB10GEE\emca_2009-11-08_03-23-24-PM.log.
Nov 8, 2009 3:23:42 PM oracle.sysman.emcp.EMReposConfig
createRepository
INFO: Creating the EM repository (this may take a while) ...
Nov 8, 2009 3:28:49 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 8, 2009 3:28:50 PM

C:\>emca -config dbcontrol db

STARTED EMCA at Nov 8, 2009 3:30:30 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: db10gee
Database Control is already configured for the database db10gee
You have chosen to configure Database Control for managing the database
db10gee
This will remove the existing configuration and the default settings and
perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings
Database ORACLE_HOME
................ C:\oracle\product\10.2.0\db_2
Database hostname
................ zaffark
Listener port number ................ 1521
Database SID ................ db10gee
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 8, 2009 3:31:18 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at
C:\oracle\product\10.2.0\db_2\cfgtoollogs\emca\DB10GEE\emca_2009-11-08_03-30-29-PM.log.
Nov 8, 2009 3:31:23 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 8, 2009 3:31:48 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 8, 2009 3:32:48 PM oracle.sysman.emcp.EMDBPostConfig
performConfiguration
INFO: Database Control started successfullyNov 8, 2009 3:32:50 PM
oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL
is http://zaffark:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 8, 2009 3:32:50 PM

C:\>

I checked my Database Control URL and the Enterprise Manager configuration was a success.

Alas! Our Systems will run on a New Server...

Yesterday, I came to know that we have received the new server. The installations & configurations will be done on Saturday, and will be made available to me to carry out the Migration. I will share more on it soon, including the Server Configurations.

Toad World PIPELINE Newsletter (July '09 Issue) has my Technical Paper

Today, I was immensely delighted to know that the Toad World PIPELINE Newsletter (July '09 Issue) is about to be released. You might question me why the emotions at display?

I had sent my Oracle Technical Paper (the one which was published in Oracle Customer Knowledge Exchange in Apr '09) to the Technical Content Editor of Quest Software, after which it was immediately considered for being published in the July '09 Issue of Toad World PIPELINE Newsletter.

Since then I was anxiously waiting for a reply from Quest Software or the July '09 Issue itself. Yesterday, I received an email stating that the newsletter is just about to go out (next day or so) and that I can see my feature before the issue.

Here is the link to Toad World PIPELINE Newsletter (July '09 Issue):
http://www.quest.com/ecard/27254/default.htm

May'09 Disaster Recovery: Production brought back to Life!

The test setup for selected users was functioning smoothly. This bought us sometime to look for the bad Disks in local Market. The IT Administrators somehow managed to get the Disks. By the en of the 3rd Day, they replaced and rebuilt the Disks in the RAID5 Configuration. The O/S Partition was formatted and server O/S was upgraded to MS Windows 2003 Server [Standard Edition].

We managed to recover most of the data on the RAID5 Disks, especially the Application. The database files were physically recovered, but I considered rebuilding the Database on the Server for the following reasons:

1. The Server O/S was upgraded
2. Oracle 9iR2 Software had to be reinstalled
3. Data had changed in the last 3 days, as people were using the Secondary Setup. So, I needed to clone the production as the Secondary Setup.

On the 4th Day, I cleared the Database Files on the Production Server and installed the Oracle 9iR2 software and patched it to 9.2.0.8. The Application was intact, so I did not have to do any thing on those lines. Now, I had 3 options to setup the Production Server.

1. Using export dump of the Secondary Instance, create the Production Oracle Database.
2. Using RMAN, clone the Production Oracle Database with the "Duplicate Target Database" command.
3. Or, simply clone the Production Oracle Database using the Cold Backup of the Secondary Database.

The 3rd option was simple and effective. With assistance from one of my colleague, we performed the Cloning using Cold Backup. And, Within an hour the Production Instance was up.

The Application Testing was carried out to check the Application were running smoothly and for the Data Validation. Once the testing was successful, we registered the database in the Recovery Catalog, and took a full database backup. We had to ensure that the Oracle Services were owned by the Domain Administrator Account and not the Local Account for registering the database and taking a Full Database RMAN Backup ensuring that the Controlfile Autobackup and SPFILE backup was on the shared location along with the RMAN Backups.
[Note: 145843.1 How to Configure RMAN to Write to Shared Drives on Windows NT/2000]

Once the backup was complete, the Temporary Setup was shutdown and we brought the Production Server online for all the users.

In the next 2 weeks, the following necessary arrangements were made for the short-comings seen in the Disaster Situation:

1. Application Backup is daily ensured to Tapes
2. Application files, as of 13th May 2009, have been backed up to DVDs. Every 15 days, Application files backup to DVDs is being ensured.
3. A Temporary Server was arranged by IT Administrators and has been cloned (using RMAN) same as that of the Production Server. Scheduled Jobs run at 3 intervals so as to Clone the Secondary database is put in place. The cloning process takes more than 2 hours to complete. In case of an unforeseen disaster, we can easily switch to the Secondary Server with a minimal Data Loss.
4. Source Code Backup and its relevant Document Control is strictly ensured prior to moving to Production.
5. After the Production Server’s Operating System Upgrade to Windows Server 2003, the RMAN backup location has been changed to SAN Storage location, which is further backed up to Tapes by the IT Administrators. The earlier RMAN backup performance issue of 13 hours has been resolved. Now, the backup completes in less than an hour. You can read about it here.
6. For the next 3 months, we will be carrying out planned monthly recovery of complete Application from the Tapes and/or the DVDs. Once the recovery simulation comfort level is attained, we can carry out the simulation every Quarter.
7. Finally, the new Server Procurement Process has been started, and which server to purchase has been finalized.

We have setup the Secondary Database on a Temporary Server. Due to our limitations on Oracle Licensing (Standard Edition), we are not able to use the Oracle Dataguard, as it is only available as part of the Enterprise Edition. Being simple and effective, I have currently opted to use RMAN Duplicate Target Database to clone the Secondary Database on the Temporary Server. Alternately, I am trying to find a solution for manual replication via own scripts, as the current cloning method adds some data loss disadvantages. The Cloning has been scripted and scheduled to run at 3 intervals in a day:

• 4 am: Clone after the Full Database RMAN Backups
• 11 am: Clone in the mid of the Working Day (after all Archivelog Backups are available up to 11 am)
• 4 pm: Clone at the end of the Working Day (after all Archivelog Backups are available up to 4 pm)

Once the Production System is migrated to a new server, the old server can be used as a Standby Database. Also, we will be migrating the production Database from Oracle 9iR2 [9.2.0.8] to Oracle Database 10g [10.2.0.4].

May'09 Disaster Recovery: Production Lost

Finally, I am able to write on the Production Server Crash that happened at 07:25 am on 14th May '09.

Our server has a 4 disk configuration for RAID5. We had encountered a disk failure couple of weeks back, which was recovered the same day. That was when our ERP Database Files had got corrupted. You can read about it here.

This time, 2 Disks of the RAID5 Configuration crashed and to survive a crash in a RAID5 setup you need minimum 3 disks running. So, everything along with the server went down. The server was hosting our Oracle Forms6i-based ERP Application as well as our Oracle ERP Database.

Initially, I was a little confident as I had my Full Database RMAN Backups available as well as last night's full database export dump file. For the disks in RAID5 Configuration, being kind of an obsolete model, we were not available to carry out the replacements. Our supplier told us that the disks may be available in the Australian Warehouse, and it would take 2-3 weeks to send it to us. As far as business continuity is concerned, there was none in place and we had to make a temporary setup available as earliest as possible. The IT Administrators immediately arranged for a Server where I could build and bring the whole ERP online. I started the Oracle Product Installation [9.2.0, and patched it to 9.2.0.8]. The Oracle Product Installation and Oracle Database creation was too slow. Literally slow, to the extent that it took almost 5-6 hours and the database creation still did not complete. The Server was a Pentium-III 1 GHz single processor, with 1 GB RAM.

Meanwhile, I found two severe concerns that would hinder the re-build/recovery of the ERP.

1. The Application Backup was supposed to be on the DDS tapes, the tape drive of which was in-built with the production server. To access the tape drives, we would have to bring back the server, as we did not have any spare tape drive.
2. The controlfile autobackup and spfile Backup related to the Full Database RMAN Backup were on the crashed server (which should have been on the backup location in te first place). This means that all my RMAN Backups were of no use, until the controlfile autobackup file was recovered from the crashed server's disks.
   Later, I found out that one of the batch jobs was resetting the controlfile autobackup location from "\\testdb\orcl" to "D:\ORACLE\ora92\DATABASE\...".

I had the last night's full export dump, and had no choice but to create a new database with the same. Because the installation activity on the alternate server was quite slow, I started the Database Creation on the Test Server in parallel, which was a Pentium IV with 2 GB RAM. The Database Creation finished in less than 1 hour. Then, I started the full database import, which took another 3-4 hours to complete. If I had the RMAN Backups available, this whole process would have finished in 3 hours or less. Nevertheless, the import activity provided some benefits to me which were only possible in the migration of the database to a new server. I will be discussing about this in the next post.

Meanwhile, I had another problem at my head. From where do I get the ERP Application as the backups were supposed to be on the tapes, and the tape drive was not functional. We had an year old application source available on one of the locations. The issue here was that in a year's span, the developers had put in a couple of new modules, reports and bug fixes. We were maintaining a Manual Source Code Control [Change Management] and were able to recover 95-96% of the ERP Application. There were 2-3 sub modules whose source code was missed due to mismanaged source code control. The only way we could have a complete recovery was if we could recover from the tapes, or repair and recover the same from the Disks.

We managed to bring the temporary setup online for a set of users per department.

By now, the management realized that a Business Continuity Plan needs to be in place. And, that our Disaster Recovery Plan needed a serious re-visit.

1. Our Backups to tapes should be made easily recoverable, even in case of a tape drive failure.
2. Our Application Backup and Source Code should be maintained in other Medias atleast at an interval of 1 month or less.
3. Our RMAN Backup needs to be made more fool proof if we were to completely rely on it.
4. Our Business Continuity Setup should be made available so as to avoid major downtime.

In the next post, I will be sharing with you more on the subject and the Complete Recovery of the Production Server.

RMAN Performance Hiccups Resolved

This is in relation with the earlier post "Investigating Hiccups in RMAN Implementation for Production Database", where I was trying to find out the severe performance issues when I carried out RMAN Backup on a network storage (SAN, available to me as NAS). Here are the findings from the earlier tests:

The backup tablespace size is 1.36 GB with one datafile.
The backup piece size in all 3 test cases is 1.1 GB each.
RMAN Backup Time at 3 location:

• Local Backup Duration (D:\oracle\backup): 04:55 minutes
• Shared Backup Duration (\\testdb\orcl): 02:25 minutes
• Shared SAN Duration (\\blade5\mis\backup\RMAN): 26:25 minutes

So, basically I was expecting a lot more better performance or atleast similar performance to the shared backup location for RMAN Backups. We (me, IT Guys and Oracle Support Guys) really were not able to figure out why it was happening.

With the crash that occured on the 11th May, we had the server completely recovered and rebuilt. In the event of rebuilding the server, the IT Administrators upgraded the O/S from Windows 2000 Server Standard Edition to Windows 2003 Server Standard Edition.

After cloning the Recovered Database, I setup RMAN Backup Jobs and did a trial backup on the Shared SAN location, and guess what!!!! My backup which used to take 8 hrs to 13 hrs to complete on the Shared SAN location, now finished in 35-40 minutes.

I guess there was something related to the O/S after all. What change resolved the backup performance issue, is still unknown, except for the fact that upgrading to Windows 2003 Server helped resolve the backup performance issue.

Major Disaster Recovered Last Week

Last week, to be more precise 14th May '09, we had a major Server Crash. More than 2 of the Disks in the RAID5 Configuration went Poof!!! And yes, We brought the Server back from the dead, even though it really needed to lay in its grave this time (server's been running for more than 7-8 years) !!!! Can't wait to share with you my last week's experience, but before I post about it I need to carry out some priority tasks.

Lot of endeavors to share related to RMAN and exp Backups, Temporary System setup, Application Recovery Failure, Tape Backup Startegy, Re-Visit on our D/R Plan, Recovery from Failed Disks, O/S Upgrade and new findings on the RMAN Backup performance issue, Standby and DG, and last but not the least procurement of a new server!!! At last, after 2 years the management realizes we need to Change the server. :D

10G Flashback-related Crash Scenario

Today, I created a crash scenario by deleting all the files (including the archives and backups) in the FLASH RECOVERY AREA (C:\oracle\flash_recovery_area) in in my test 10G database. After which, I was only able to mount the database. A "startup" or an "alter database open" gave the following error: "ORA-38760: This database instance failed to turn on flashback database".

Here, I will show how I worked around the problem, to get my database up and running.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 192
Next log sequence to archive 192
Current log sequence 194

SQL> show parameter rec
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
control_file_record_keep_time integer 7
db_recovery_file_dest string C:\oracle/flash_recovery_area
db_recovery_file_dest_size big integer 4G
db_recycle_cache_size big integer 0
ldap_directory_access string NONE
recovery_parallelism integer 0
recyclebin string on
use_indirect_data_buffers boolean FALSE

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

SQL> alter database flashback off;
Database altered.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Usually at this point database should start. In our case, we need to check for Guaranteed Restore Point, and if it exists then we need to drop it.

SQL> select NAME, SCN, GUARANTEE_FLASHBACK_DATABASE, DATABASE_INCARNATION# from v$restore_point;
NAME SCN GUA DATABASE_INCARNATION#
---------- ---------- --- ---------------------
A 4073278 YES 2

SQL> DROP RESTORE POINT A;
Restore point dropped.

SQL> select NAME, SCN, GUARANTEE_FLASHBACK_DATABASE, DATABASE_INCARNATION# from v$restore_point;
no rows selected

SQL> alter database open;
Database altered.

SQL> show sga
Total System Global Area 314572800 bytes
Fixed Size 1248768 bytes
Variable Size 79692288 bytes
Database Buffers 226492416 bytes
Redo Buffers 7139328 bytes

Documentation Index for Real Application Clusters

I was looking for notes on Oracle Data Guard Installation and Troubleshooting on Metalink (My Oracle Support), and accidentally found this great Metalink Note on Real Application Clusters. Thought of sharing this information on the blog. It will surely help us some day.

Subject: Documentation Index for Real Application Clusters

Doc ID: 188135.1                                     Type:REFERENCE 

Modified Date :01-JUN-2007          Status:PUBLISHED

Complete Recovery of Production Instance

Yesterday at 01:12 pm, the production instance crashed. We were reported of "Shared Memory Realm Does not Exist" error by the users.

I noticed in the Alertlog, that the Control File (control05.ctl) was having some problem.

Wed Apr 29 13:04:37 2009
Errors in file d:\oracle\admin\orcl0\bdump\orcl_arc2_4852.trc:
ORA-00202: controlfile: 'C:\ORACLE\ORADATA\ORCL\CONTROL05.CTL'
ORA-27091: skgfqio: unable to queue I/O
ORA-27070: skgfdisp: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 2) The system cannot find the file specified.

I brought down the instance, as it was in nomount state, and then renamed the corrupted Control File (control05.ctl) and by copying one of the other control files, I created a new Control File (control05.ctl).

I fired the startup to see the control file issue was resolved, but also got a couple of new error messages related to datafile 12.

The alert log revealed the following:

Wed Apr 29 13:17:29 2009
Errors in file d:\oracle\admin\orcl0\udump\orcl_ora_6512.trc:
ORA-00600: internal error code, arguments: [krdsod_1], [198804811], [0], [198804867], [0], [], [], []
ORA-01115: IO error reading block from file 12 (block # 334316)
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\ORCL\ORION_ISCO01.DBF'
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.

This file was part of the main production tablespace, kind of the heart of the Functional ERP System. I fired a recover on the tablespace and it still gave the same error.

SQL> alter database recover datafile 12;
alter database recover datafile 12
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01115: IO error reading block from file 12 (block # 334316)
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\ORCL\ORION_ISCO01.DBF'
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.

I was wondering how many files have been affected in this manner, to decide between full database restore or individual datafile/tablespace restore and recover.

Meanwhile, from the IT Admins, I came to know that one of the disks from the 4 RAID5 disks had a problem (defunc) at almost the same time when we noticed a Instance Crash, and that the disk needed to be fixed.

The database is in Archivelog mode and we have daily full database backups and also Archivelog backups in every 4 hours taken. Then I tried to restore the whole tablespace (datafiles 11, 12, 18, 19), and it again failed at datafile 12. Then, I renamed the existing datafile (ORION_ISCO01.DBF) and then restored the datafile 12 (ORION_ISCO01.DBF) from the backup, and eventually restored all the other 3 datafiles. And then I sucessfully recovered the tablespace (SR 7473105.994).

Now, I was able to successfully open the database.

SQL> alter database open;
Database altered.

SQL>

I checked the Systems and they were all running fine. And, then I took a full database RMAN backup with retention policy of redundacy 2. I raised a Service Request (SR 7559528.992) with Oracle Support to look into what the error messages mean and is it directly related to the RAID5 disk failure.
I would require to answer a few questions to the Management regarding this Incident, or should I say, Disaster Recovery Situation. It's high time, we need to change the database server.

Today, the users are using the system as if nothing happened. Thanks to the implementation of RMAN, I was able to bring the database back online in 3:30 hours. I can't imagine what I would have done with the full export dumps, or the ealier weekly Cold Backups (NOARCHIVELOG).

I will keep you all posted on my further findings.

Oracle Service and Oracle Instance

There was a simple question on OTN Forum today, yet the answer to it required a re-think on how to put the words right.

"Hi,
anybody can expain what is the difference between starting the Oracle Services(using SERVICES.MSC) and starting the oracle instance (using Startup commant) ?. whats happening in oracle while doing these things?"

Although most of us know the concepts behind the question, at times we do run out of words in explaining it or atleast expressing it others. I came across this blog [http://ewhalen.blogspot.com/], which had the following to say on "Oracle Services and the Windows Registry":

"A Microsoft Windows service allows you to create long-running executable applications that run in their own Windows sessions. These services can be automatically started when the computer boots, can be manually started, stopped and paused. Because they run in their own session, they do not contain a user interface, but they can take parameters or use registry parameters. Services are used for long-running functionality that does not interact with other users, thus is good for server applications such as the Oracle Database Server and Oracle Listener. In addition, services can be invoked under the user context of a domain user, local user or the system account. Thus the Oracle service can be started by a user other than the Oracle database owner, but will run under the context of the Oracle user."

So, baasically, a service is the way windows recognizes an executable and allocates and keeps reserved an amount of memory, until the service is stopped or forcefully terminated. Using this Service our Oracle Instance hangs in there for us to use the Oracle Database.

10G New Features: Data Pump Basics

Data Pump Components:

There are 3 major components of Data Pump Technology:

1. DBMS_DATAPUMP package
   In this package are the procedures used to carryout the Data Pump export and import activities.
   
2. DBMS_METADATA package
   This package is used to extract the metadata of the data dictionary objects.
   
3. expdp, impdp client utilities
   Using "expdp", you can extract data objects from the source database. And, using "impdp", you can load data objects into the target database.

Directory Objects:

The Data Pump job creates the dump files on the server. Using Directory Objects, Data Pump can write to/read from the O/S directory on the server.

You can use the DIRECTORY parameter of expdp/impdp utilities to relate to the directory.
You can also set the Environment Vairable "DATA_PUMP_DIR" and furnish on the dumpfile or logfile name.
Or, in the DUMPFILE and LOGFILE parameters, prefix the dumpfile name or the logfile name with the Directory Name followed by a ':', i.e. DIRECTORY:FILE notation.
Let's see how it works:

SQL> conn / as sysdba
Connected.

SQL> create directory dp_dir01 as 'c:\oracle\backup\dp';

Directory created.

SQL> grant read, write on directory dp_dir01 to system;

Grant succeeded.

SQL> host expdp system/db10gee full=y dumpfile=dp_dir01:exp01.dmp logfile=dp_dir01:exp01.log

;;;
Export: Release 10.2.0.1.0 - Production on Saturday, 11 April, 2009 14:46:36

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y dumpfile=dp_dir01:exp01.dmp logfile=dp_dir01:exp01.log
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 120.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
................
................
................
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  C:\ORACLE\BACKUP\DP\EXP01.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 14:51:10

Order of Precedence for file locations will be as follows:

1. DIRECTORY:FILE Notation
2. DIRECTORY Parameter
3. DATA_PUMP_DIR

10G New Features: Statistics Collection

I am reading through "OCP Oracle Database 10G: New Features for Administrators Exam Guide" by Sam R. Alapati. A must read for 9i DBAs looking to upgrade skills to Oracle 10G.

In 10G, there is a new background process called MMON [Manageability Monitor Process]. MMON keeps track of the High Water Mark Statistics and the Database Usage statistics, recording it in AWR [Automatic Workload Repository].

Database Usage Statistics:

SQL> desc dba_feature_usage_statistics
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DBID                                      NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(64)
VERSION                                   NOT NULL VARCHAR2(17)
DETECTED_USAGES                           NOT NULL NUMBER
TOTAL_SAMPLES                             NOT NULL NUMBER CURRENTLY_USED                                     VARCHAR2(5)
FIRST_USAGE_DATE                                   DATE
LAST_USAGE_DATE                                    DATE
AUX_COUNT                                          NUMBER
FEATURE_INFO                                       CLOB
LAST_SAMPLE_DATE                                   DATE
LAST_SAMPLE_PERIOD                                 NUMBER
SAMPLE_INTERVAL                                    NUMBER
DESCRIPTION                                        VARCHAR2(128)

HWM Statistics:

SQL>
SQL> desc dba_high_water_mark_statistics
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DBID                                      NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(64)
VERSION                                   NOT NULL VARCHAR2(17)
HIGHWATER                                          NUMBER
LAST_VALUE                                         NUMBER
DESCRIPTION                                        VARCHAR2(128)

SQL>
SQL> select name, highwater, description
2  from dba_high_water_mark_statistics
3  order by 3;

NAME                                                             HIGHWATER                                                          
---------------------------------------------------------------- ----------
DESCRIPTION
--------------------------------------------------------------------------------
CPU_COUNT                                                                 2
Maximum Number of CPUs
SESSIONS                                                                 18
Maximum Number of Concurrent Sessions seen in the database
DATAFILES                                                                12
Maximum Number of Datafiles

PART_INDEXES                                                              0
Maximum Number of Partitions belonging to an User Index
PART_TABLES                                                               6
Maximum Number of Partitions belonging to an User Table
SQL_NCHAR_COLUMNS                                                        11
Maximum Number of SQL NCHAR Columns

SERVICES                                                                  4
Maximum Number of Services
TABLESPACES                                                              13
Maximum Number of Tablespaces
QUERY_LENGTH                                                            139
Maximum Query Length

DB_SIZE                                                          1704001536
Maximum Size of the Database (Bytes)
USER_INDEXES                                                           2001
Number of User Indexes
USER_TABLES                                                             998
Number of User Tables

SEGMENT_SIZE                                                      183500800
Size of Largest Segment (Bytes)

13 rows selected.

SQL>

Pretty Cool.... Didn't have that in 9i.

Template Change

I really liked the template "Minima Dark", but had to change the template to "Minima Stretch", for better readability of texts, scripts and codes.

Cloning Database using RMAN

I mentioned in one of my RMAN posts earlier, that there was one feature that I really liked about RMAN. Well, today is your lucky day!!! I am going to talk about Database Cloning using RMAN.

I am sure most of you would be very well aware of RMAN''s Duplicate Command. We will see the command in action.

Before you start cloning you should fulfill the following pre-requisites:

1. Target Database must be in Archivelog mode
2. Target Database should be registered in the Recovery Catalog Database
3. A Valid Full Database Backup of Target Database should be in hand
4. A Test Machine that has Oracle Home Version same as that of Target Database

Example Considerations:

Test Machine O/S: Windows XP SP2
Oracle Version: Oracle 9iR2 (9.2.0.8) Standard Edition
Target Database: ORCL
Recover Catalog Database: RCVCAT
Auxiliary Database: DBPIMPL

On the Test Machine, create an initialization parameter file same as that of Target Database. Here I will refer my new Database to be cloned as Auxiliary Database having SID as "DBPIMPL".

1. Change the db_name and instance_name parameters to Auxiliary Database Name, i.e. "DBPIMPL".
2. Change the Target Database OFA path/location to the Auxiliary Database OFA path/location.
3. Modify the SGA size as per the availability of Resources on Test Machine.
4. Block Size of Auxiliary Database must be same as that of Target Database.

Please ensure that the following parameters are added to the PFILE:

*.lock_name_space=ORCL

This parameter ensures we don't accidently restore and recover the Target Database

*.db_file_name_convert=('D:\oracle\oradata\ORCL\', 'C:\oracle\oradata\DBPIMPL\')
*.log_file_name_convert=('D:\oracle\oradata\ORCL\', 'C:\oracle\oradata\DBPIMPL\')

Pointing to the new locaion, so that RMAN, using Target Database Backup Files, will restore at new location.

Your Auxilary Database's PFILE (initdbpimpl.ora) should look like this:

*.background_dump_dest='C:\oracle\admin\DBPIMPL\bdump'
*.compatible='9.2.0.8'
*.control_files='C:\oracle\oradata\DBPIMPL\CONTROL01.CTL','C:\oracle\oradata\DBPIMPL\CONTROL02.CTL','C:\oracle\oradata\DBPIMPL\CONTROL03.CTL'
*.core_dump_dest='C:\oracle\admin\DBPIMPL\cdump'
*.db_cache_size=33554432
*.db_block_size=4096
*.db_name='DBPIMPL'
*.instance_name='DBPIMPL'
*.java_pool_size=58720256
*.large_pool_size=10485760
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.shared_pool_size=50331648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='C:\oracle\admin\DBPIMPL\udump'
*.db_file_name_convert=('D:\oracle\oradata\ORCL\', 'C:\oracle\oradata\DBPIMPL\')
*.log_file_name_convert=('D:\oracle\oradata\ORCL\', 'C:\oracle\oradata\DBPIMPL\'
*.lock_name_space=ORCL

Next, create a service for Auxiliary Database using ORADIM:

oradim -new -sid dbpimpl -intpwd dbpimpl -pfile C:\oracle\product\9.2.0\db_1\database\initdbpimpl.ora -startmode m

In the Test Machine's TNSNAMES.ora, add a TNS Entry for the new database, i.e. Auxiliary Database. Ensure that all the 3 TNS entries are dedicated for RMAN to function smoothly.

In the Test Machine's LISTENER.ora, add the new database SID to the Listener's SID List.

Reload, or Stop and Start the to make the changes effective.

Ensure all the folders are created for the Auxiliary Database (DBPIMPL), as per Optimal Flexible Architecture. In my case, as follow:

.../admin/bdump
.../admin/cdump
.../admin/udump
.../oradata/DBPIMPL
.../oradata/DBPIMPL/archive

Open a new command window, and put the Auxiliary Database in NOMOUNT mode.

C:\>set oracle_sid=dbpimpl
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 7 09:43:42 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

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

SQL> host lsnrctl
LSNRCTL for 32-bit Windows: Version 9.2.0.8.0 - Production on 07-APR-2009 10:07:23
Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zaffark)(PORT=1521)))
The command completed successfully

LSNRCTL> exit

SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 177282424 bytes
Fixed Size 454008 bytes
Variable Size 142606336 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

SQL>
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBPIMPL

SQL>
SQL> disconn

SQL> exit

Now, we will require to connect to RMAN on Test Machine and start the cloning process:

C:\>set oracle_sid=dbpimpl

C:\>rman
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> spool log to c:\dbpimpl_dup_070409.txt

RMAN> connect catalog rman@rcvcat

recovery catalog database Password:

RMAN> connect target sys@orcl

target database Password:

RMAN> connect auxiliary /

RMAN>
RMAN> duplicate target database to DBPIMPL
2> LOGFILE
3> GROUP 1 ('C:\ORACLE\ORADATA\dbpimpl\REDO01A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO01B.RDO') SIZE 10M REUSE,
4> GROUP 2 ('C:\ORACLE\ORADATA\dbpimpl\REDO02A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO02B.RDO') SIZE 10M REUSE,
5> GROUP 3 ('C:\ORACLE\ORADATA\dbpimpl\REDO03A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO03B.RDO') SIZE 10M REUSE,
6> GROUP 4 ('C:\ORACLE\ORADATA\dbpimpl\REDO04A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO04B.RDO') SIZE 10M REUSE;

RMAN>

My Target Database Has Redo Multiplexed on 2 drives, where as my Auxiliary Database doesn't have the luxury of 2 drives. So, I will create Redo Log Group Members same as the Target Database, but point them to Test Machine's C: Drive.

I need to check the RMAN spool file for any errors. If none, my cloning is complete. So simple!!! That's why it is a beautiful feature of RMAN.

Here is what happens in the background when you fire the RMAN Duplicate command.

Spooling started in log file: c:\dbpimpl_dup_070409.txt
Recovery Manager: Release 9.2.0.8.0 - Production

RMAN> connected to recovery catalog database

RMAN> connected to target database: ORCL (DBID=1032853409)

RMAN> connected to auxiliary database: DBPIMPL (not mounted)

RMAN>
RMAN> 2> 3> 4> 5> 6>

Starting Duplicate Db at 07-APR-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=10 devtype=DISK

printing stored script:
Memory Script{
set until scn 195634017;
set newname for datafile 1 to "C:\ORACLE\ORADATA\DBPIMPL\SYSTEM01.DBF";
set newname for datafile 2 to "C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF";
set newname for datafile 3 to "C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF";
set newname for datafile 4 to "C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF";
set newname for datafile 5 to "C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF";
set newname for datafile 6 to "C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF";
.....
set newname for datafile 24 to "C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF";
restore
check readonly
clone database ;
}

executing script: Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 07-APR-09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBPIMPL\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF
......
restoring datafile 00024 to C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF

channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_1_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 2
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_2_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 3
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_3_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 4
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_4_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 5
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_5_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 6
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_6_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 7
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_7_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 8
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_8_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restored backup piece 9
piece handle=\\TESTDB\ORCL\ORCL_ARKBQBQB_9_1_20090406 tag=FULL_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restore complete

Finished restore at 07-APR-09

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBPIMPL" RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO01A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO01B.RDO' ) SIZE 10485760 REUSE,
GROUP 2 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO02A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO02B.RDO' ) SIZE 10485760 REUSE,
GROUP 3 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO03A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO03B.RDO' ) SIZE 10485760 REUSE,
GROUP 4 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO04A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO04B.RDO' ) SIZE 10485760 REUSE
DATAFILE
'C:\ORACLE\ORADATA\DBPIMPL\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

printing stored script: Memory Script
{ switch clone datafile all; }

executing script: Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=683556745 filename=C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=683556745 filename=C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=683556745 filename=C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=683556745 filename=C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=683556745
filename=C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF
......
datafile 24 switched to datafile copy
input datafilecopy recid=23 stamp=683556745 filename=C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF

printing stored script: Memory Script

{
set until scn 195634017;
recover
clone database
delete archivelog ;
}

executing script: Memory Script

executing command: SET until clause

Starting recover at 07-APR-09
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=8168
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=\\TESTDB\ORCL\ORCL_ATKBQDRG_1_1_20090406 tag=ARCH_20090406_1730 params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08168.001 thread=1 sequence=8168
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08168.001 recid=1 stamp=683556761
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=8169
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=8170
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=8171
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=\\TESTDB\ORCL\ORCL_B1KBS5S5_1_1_20090407 tag=ARCH_20090407_1000 params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08169.001 thread=1 sequence=8169
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08169.001 recid=4 stamp=683556869
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08170.001 thread=1 sequence=8170
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08170.001 recid=3 stamp=683556844
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08171.001 thread=1 sequence=8171
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\9.2.0\DB_1\RDBMS\ARC08171.001 recid=2 stamp=683556776
media recovery complete

Finished recover at 07-APR-09

printing stored script: Memory Script

{
shutdown clone;
startup clone nomount ;
}

executing script: Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 177282424 bytes

Fixed Size 454008 bytes
Variable Size 142606336 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBPIMPL" RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO01A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO01B.RDO' ) SIZE 10485760 REUSE,
GROUP 2 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO02A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO02B.RDO' ) SIZE 10485760 REUSE,
GROUP 3 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO03A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO03B.RDO' ) SIZE 10485760 REUSE,
GROUP 4 ( 'C:\ORACLE\ORADATA\dbpimpl\REDO04A.RDO', 'C:\ORACLE\ORADATA\dbpimpl\REDO04B.RDO' ) SIZE 10485760 REUSE
DATAFILE 'C:\ORACLE\ORADATA\DBPIMPL\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

printing stored script: Memory Script

{
catalog clone datafilecopy "C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF";
catalog clone datafilecopy "C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF";
......
catalog clone datafilecopy "C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF";
switch clone datafile all;
}


executing script: Memory Script

cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF recid=1 stamp=683557959
cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF recid=2 stamp=683557959
cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF recid=3 stamp=683557959
cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF recid=4 stamp=683557960
cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF recid=5 stamp=683557960
......
cataloged datafile copy
datafile copy filename=C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF recid=23 stamp=683557961
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=683557959 filename=C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=683557959 filename=C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=683557959 filename=C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=683557960 filename=C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF
datafile 6 switched to datafile copy
filename=C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF
input datafilecopy recid=5 stamp=683557960
......
datafile 24 switched to datafile copy
input datafilecopy recid=23 stamp=683557964 filename=C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF

printing stored script: Memory Script

{
Alter clone database open resetlogs;
}

executing script: Memory Script
database opened

Finished Duplicate Db at 07-APR-09

RMAN>

So, the logs says my cloniong is successfully completed. Now there are few post-cloning activities that need to be taken care of.

Firstly, you need to add tempfiles to all the temporary tablespaces that you might be having in the Auxiliary Database. Then create your spfile from pfile, and restart the database.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\oradata\DBPIMPL\TEMP01.DBF' SIZE 100M 
2  /
Tablespace altered.

SQL> ALTER TABLESPACE TEMP_STATS ADD TEMPFILE 'C:\oracle\oradata\DBPIMPL\TEMP_STATS01.DBF' SIZE 50M 
2  /
Tablespace altered.

SQL> ALTER TABLESPACE TEMP_PAY ADD TEMPFILE 'C:\oracle\oradata\DBPIMPL\TEMP_PAY01.DBF' SIZE 50M 
2  /
Tablespace altered.

SQL> SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS <> 'VALID';
  COUNT(*)
----------
         0

SQL> create spfile from pfile;
File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  177282424 bytes
Fixed Size                   454008 bytes
Variable Size             142606336 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> exit

Now you can connect to RMAN using target control file and take a full database backup.

C:\>rman

Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

RMAN> CONNECT TARGET /

connected to target database: DBPIMPL (DBID=1527816262)

RMAN> SHOW ALL;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\SNCFDBPIMPL.ORA'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'C:\oracle\BACKUP\RMAN\DBPIMPL\%d_%U_%T' MAXPIECESIZE 2 G;

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'C:\oracle\BACKUP\RMAN\DBPIMPL\%d_%U_%T' MAXPIECESIZE 2 G;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'C:\oracle\BACKUP\RMAN\DBPIMPL\%d_%U_%T' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\SNCFDBPIMPL.ORA'; # default

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Starting backup at 07-APR-09
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=1 recid=1 stamp=683558677
channel ORA_DISK_1: starting piece 1 at 07-APR-09
channel ORA_DISK_1: finished piece 1 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_01KBSHOL_1_1_20090407 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 07-APR-09

Starting backup at 07-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\DBPIMPL\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\DBPIMPL\UNDOTBS01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\DBPIMPL\TOOLS01.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\DBPIMPL\INDX01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\DBPIMPL\CWMLITE01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\DBPIMPL\DRSYS01.DBF
......
input datafile fno=00024 name=C:\ORACLE\ORADATA\DBPIMPL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-APR-09
channel ORA_DISK_1: finished piece 1 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_1_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 2 at 07-APR-09
channel ORA_DISK_1: finished piece 2 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_2_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 3 at 07-APR-09
channel ORA_DISK_1: finished piece 3 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_3_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 4 at 07-APR-09
channel ORA_DISK_1: finished piece 4 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_4_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 5 at 07-APR-09
channel ORA_DISK_1: finished piece 5 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_5_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 6 at 07-APR-09
channel ORA_DISK_1: finished piece 6 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_6_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 7 at 07-APR-09
channel ORA_DISK_1: finished piece 7 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_7_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 8 at 07-APR-09
channel ORA_DISK_1: finished piece 8 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_8_1_20090407 comment=NONE
channel ORA_DISK_1: starting piece 9 at 07-APR-09
channel ORA_DISK_1: finished piece 9 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_02KBSHOP_9_1_20090407 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:25:43
Finished backup at 07-APR-09

Starting backup at 07-APR-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=2 recid=2 stamp=683560224
channel ORA_DISK_1: starting piece 1 at 07-APR-09
channel ORA_DISK_1: finished piece 1 at 07-APR-09
piece handle=C:\ORACLE\BACKUP\RMAN\DBPIMPL\DBPIMPL_03KBSJ90_1_1_20090407 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 07-APR-09

Starting Control File Autobackup at 07-APR-09
piece handle=C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\C-1527816262-20090407-00 comment=NONE
Finished Control File Autobackup at 07-APR-09

RMAN>exit;

c:\>

This completes the cloning of Auxiliary Database, similar to that of the Target Database.

Winding-Up RMAN Implementation for Production Database

While I was taking up the RMAN Backup performance issue along with support, I also posted the query on OTN Forums (http://forums.oracle.com/forums/message.jspa?messageID=3320320#3320320).

I carried out the tablespace RMAN backup on the Database Server and then copied it to Blade5 SAN location. When I did a normal copy it took only 2 minutes as opposed to 21 minutes.

This was what one contributor had to say on OTN Forum:
"The rman backup process is not only a copy process but also a check on the integrety of the written backup piece. Might be there are a lot of context switches involved in this process which can, due to latency on the network result in bad performance.(The connection to the bad performing storage is over IP as I understand it)Regards,Tycho"

Well, in any case, there definately was no solution coming out from either sides (Oracle Support and IT Admins) to resolve the performance issue and pronouce RMAN implementation successful. Assuring that <\\testdb\orcl> location would be used as a staging location to backup the RMAN files to DDS tapes, I went ahead and scheduled the RMAN full database backups.

So now, I have a Recovery Catalog Instance through which I am taking daily RMAN Backups of Production Instance, Development Instance and Test Instance. And once all the 3 backups are completed, I am taking RMAN No catalog /target Backup of Recovery Catalog Database on shared SAN folder. It takes 25 minutes to generate 700 MB RMAN Backup files of the Recovery Catalog Database.

Here is the source of windows batch file that carries out the scheduled RMAN backups:

REM#####################################################################
REM # Name:   testdb_prod_rman_hot_backup_dsk.bat
REM # Description: ORCL
REM # Generic   Hot backup script that accepts the SID
REM # Parameters:  Instance Name
REM##################################################################
REM # Check if SID was Passed
if (%1)==() goto USAGE

REM # assign ORACLE_SID to passed SID
set ORACLE_SID=%1

REM # assign a date we can use as part of the logfile
set DTE=%date:~-4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%

REM # Take the spaces out of the date string so we can use in our file name
set DTE=%DTE: =%

REM # set the script locationREM # Set location of where you will put script
set SCRIPTLOC=C:\oracle\BACKUP\Scriptscd %SCRIPTLOC%

REM # Set the Temporary command file that we will write our rman script to
set CMDFILE=%SCRIPTLOC%\%ORACLE_SID%_rman_hot_backup_dsk.cmd

REM # Remove the temporary file if it exists
del /q %CMDFILE%

REM # Set the logfile directory
REM # Change based on where you want the backup logs to go
set LOGPATH=C:\oracle\BACKUP\logs
set LOGFILE=%ORACLE_SID%_rman_hot_backup_dsk_%DTE%.log
set LOG=%LOGPATH%\%LOGFILE%

REM # Set the path of the backup location
REM # Change based on disk path for you disk based backups (ORCL)
set BACKLOC=\\testdb\%ORACLE_SID%

REM # Set to your ORACLE_HOME location
set ORACLE_HOME=C:\oracle\product\9.2.0\db_1

# Set the rest of the Oracle Environment# based on our ORACLE_HOME
set LIBPATH=%ORACLE_HOME%/lib
set LD_LIBRARY_PATH=%ORACLE_HOME%/lib
set TNS_ADMIN=%ORACLE_HOME%/network/admin

REM Set our PATH with the ORACLE_HOME so that we have a good
REM clean environment to work with
set PATH=%ORACLE_HOME%/bin;%ORACLE_HOME%/OPatch;%PATH%

REM # Write Temporary command file for RMAN backup
echo # >> %CMDFILE%
echo # display current configurations and list of backups >> %CMDFILE%
echo SHOW ALL ; >> %CMDFILE%
echo # >> %CMDFILE%
echo LIST BACKUP SUMMARY ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # lets start clean for our hot backup by doing full crosschecks >> %CMDFILE%
echo change archivelog all crosscheck ; >> %CMDFILE%
echo crosscheck backup of database; >> %CMDFILE%
echo crosscheck backup of controlfile; >> %CMDFILE%
echo # >> %CMDFILE%
echo delete noprompt expired archivelog all ; >> %CMDFILE%
echo delete noprompt expired backup ; >> %CMDFILE%
echo delete noprompt obsolete ; >> %CMDFILE%
echo # >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '\\testdb\orcl\%%d_%%U_%%T' MAXPIECESIZE 2 G ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # Archive current log file >> %CMDFILE%
echo SQL 'alter system archive log current' ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # Backup our database >> %CMDFILE%
echo BACKUP DATABASE tag FULL_%DTE% ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # Backup our archive log files that have not been backed up  >> %CMDFILE% 
echo BACKUP ARCHIVELOG ALL DELETE INPUT tag ARCH_%DTE% ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # put controlfile to trace >> %CMDFILE%
echo sql 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE' ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # report and delete unrecoverable and obsolete >> %CMDFILE%
echo REPORT UNRECOVERABLE ; >> %CMDFILE%
echo REPORT OBSOLETE ORPHAN ; >> %CMDFILE%
echo # >> %CMDFILE%
echo DELETE NOPROMPT OBSOLETE ; >> %CMDFILE%
echo DELETE NOPROMPT EXPIRED BACKUP ; >> %CMDFILE%
echo # >> %CMDFILE%
echo CROSSCHECK BACKUPSET ; >> %CMDFILE%
echo # >> %CMDFILE%
echo # list backup details and summary >> %CMDFILE%
echo LIST BACKUP ; >> %CMDFILE%
echo LIST BACKUP SUMMARY ; >> %CMDFILE%

REM # Run the RMAN backup without using the recovery catalog
%ORACLE_HOME%\bin\rman target=sys/@ catalog rman/@ cmdfile=%CMDFILE% msglog=%LOG%

REM # Remove our temporary command file
del /q %CMDFILE%

REM # Goto end of script as we are done
goto END

:USAGE
echo "Must Pass ORACLE_SID as parameters."
goto END

:END

In addition to the daily full database backup, and as per Anwar's suggestion/comment to my 1st RMAN Implementation post on this blog, I scheduled the backup of archive logs at an interval of 3 hours during working hours.

The scheduled jobs are running perfectly fine, and I was able to perform Cloning to set up Test Instance using production RMAN backup. I will be posting more on how to Clone/Duplicate a Database using RMAN Duplicate Command.

Published my first Oracle Technical Whitepaper

Dear Friends & Colleagues,

Today, with the grace of Almighty, I have achieved one of the major milestones of my career.

I have successfully published my Oracle Technical Whitepaper titled "My Journey from 9.0.1 to 9.2.0.8" on Oracle Customer Knowledge Exchange (https://communities.oracle.com/). The paper is based on my work/accomplishments as an Oracle Database Administrator here in Integral Services Company WLL. On this blog, you know it as "Journey from 9.0.1 to 9.2.0.8: Part I-IV".

Though the subject, primarily, is based on the migration of Oracle Database from one version to another, I have tried to touch the various areas that were somehow related to the subject.

The access to the published document in Oracle Customer Knowledge Exchange would require customer logon credentials. So, you can email me at zk.oracle@gmail.com for the PDF file.

Journey from 9.0.1 to 9.2.0.8: Part-IV

The Action Plan for Oracle R1 (9.0.1) to Oracle R2 (9.2.0.8) migration was in place, and the scheduled full export dumps of the production instance were generating around 9 GB dump file. I coordinated with the IT Admins and arranged for a Test Machine, where I could carry out series of migration tests with both Manual Upgrade Process and Database Upgrade Assistant.

I had to set up an environment similar to that of the Production Box, only then could I simulate the migration. So, my Action Plan for the simulated migration/upgrade on the same server was as follows:

1. Firstly, to install Oracle 9iR1 [9.0.1] base home on the test machine,
2. Then, to create a Test Database similar to the Production Database, by using the latest export backup of the production instance,
3. Next, to install Oracle 9iR2 [9.2.0] base home in a separate location on the same test machine,
4. Then, to patch the Oracle 9iR2 [9.2.0] base home with 9.2.0.8 patchset,
5. Before beginning the upgrade process, to ensure a cold backup of Oracle 9iR1 [9.0.1] Test Database is taken,
6. Make sure the following environment variables point to the new Release directories:
   a. ORACLE_HOME
   b. PATH
   c. ORA_NLS33
   d. ORACLE_BASE
   e. LD_LIBRARY_PATH
   f. ORACLE_PATH
7. Then, using either DBUA or Manual process, to upgrade the Oracle 9iR1 [9.0.1] test database to Oracle 9iR2 [9.2.0.8],
8. If upgrade is successful, then to take a post upgrade cold backup,
9. If any major issues are encountered during the upgrade process, then to restore the cold backup prior to upgrade and point the database to the old home Oracle 9iR1 [9.0.1]. And, to Restart the Migrate/Upgrade Process.

Depending on the test results, I would have to choose between Manual Upgrade and DBUA for carrying out the upgrade on the production box.

With DBUA, you had to do very little, as you were completely relying on the Upgrade Assistant to do the job. But, if the upgrade fails in between, you would have a hard time tracing and fixing the issues and then continuing from where the upgrade failed. Whereas, with the Manual Upgrade Process, though it was tediously cumbersome method, you had complete control over your upgrade. Even if at any point you get some error, you could eventually have full control over the upgrade to fix the issue and proceed further. In addition, by the time you complete the Manual Upgrade, you would have all the technical insights to the migration from one version to another. I also came across the Metalink Note: 406037.1 to check for any issues during applying patch without shutting down the Database or Listener.

To begin with, I initiated the migration testing using Manual Upgrade Process using the Metalink Note: 159657.1 [Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)]

Please find below output related the pre-requisite checks performed on the Test database:

SQL> select max(bytes) from dba_free_space
  2  where tablespace_name='SYSTEM';



MAX(BYTES)    

----------    

242745344    



SQL> SELECT COUNT(*) FROM dba_objects;



  COUNT(*)  

----------  

     67952  



SQL> select substr(owner,1,12) owner,

  2  substr(object_name,1,30) object,

  3  substr(object_type,1,30) type,

  4  status

  5  from dba_objects

  6  where status <>'VALID';



OWNER        OBJECT                         TYPE               STATUS        

------------ ------------------------------ ------------------ -------       

- 11 Non System Objects not shown here



11 rows selected.



SQL> @C:\ORACLE\PRODUCT\9.0.1\DB_1\RDBMS\ADMIN\UTLRP



PL/SQL procedure successfully completed.



SQL> select substr(owner,1,12) owner,

  2  substr(object_name,1,30) object,

  3  substr(object_type,1,30) type,

  4  status

  5  from dba_objects

  6  where status <>'VALID';



no rows selected



SQL> SELECT * FROM V$RECOVER_FILE;



no rows selected



SQL> SELECT * FROM V$BACKUP WHERE STATUS <> 'NOT ACTIVE';



no rows selected



SQL> SELECT * FROM DBA_2PC_PENDING;



no rows selected



SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=BOTH;



System altered.



SQL>

SQL> ED

Wrote file afiedt.buf



  1  SELECT USERNAME,

  2  DEFAULT_TABLESPACE

  3  FROM DBA_USERS

  4* WHERE USERNAME IN ('SYS', 'SYSTEM')

SQL> /



USERNAME                      DEFAULT_TABLESPACE                             

------------------------------------------------------------                 

SYSTEM                        SYSTEM                                         

SYS                           SYSTEM                                         



2 rows selected.



SQL> ED

Wrote file afiedt.buf



  1  SELECT TABLESPACE_NAME

  2  FROM DBA_TABLES

  3* WHERE TABLE_NAME='AUD$'

SQL> /



TABLESPACE_NAME                                                              

------------------------------                                               

SYSTEM                                                                       



1 row selected.



SQL> SELECT * FROM V$CONTROLFILE;



STATUS                                                                       

-------                                                                      

NAME                                                                         

----------------------------------------------------------------------          

C:\ORACLE\ORADATA\DBDEV\CONTROL01.CTL                              

C:\ORACLE\ORADATA\DBDEV\CONTROL02.CTL                              

C:\ORACLE\ORADATA\DBDEV\CONTROL03.CTL                                   

3 rows selected.

SQL> SELECT * FROM V$PWFILE_USERS;



USERNAME                      SYSDBSYSOP                                     

----------------------------------------                                     

INTERNAL                      TRUE TRUE                                      

SYS                           TRUE TRUE                                      



2 rows selected.



SQL> SHOW PARAMETER MAX_ENAB



NAME                                TYPE       VALUE                         

----------------------------------------------------------------------

max_enabled_roles                   integer    148                           



SQL> SHOW PARAMETER POOL_SIZE



NAME                                TYPE       VALUE                         

----------------------------------------------------------------------

global_context_pool_size            string                                   

java_pool_size                      string     25165824                      

large_pool_size                     string     41943040                      

shared_pool_size big integer100663296


SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE;



System altered.



SQL> ALTER SYSTEM SET LARGE_POOL_SIZE=157286400 SCOPE=SPFILE;



System altered.



SQL>

SQL> SHOW PARAMETER REMOTE



NAME                                TYPE       VALUE                         

----------------------------------------------------------------------

remote_archive_enable               boolean    TRUE                          

remote_dependencies_mode            string     TIMESTAMP                     

remote_listener                     string                                   

remote_login_passwordfile           string     EXCLUSIVE                     

remote_os_authent                   boolean    FALSE                         

remote_os_roles                     boolean    FALSE                         



SQL> SHUT IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.



SQL> HOST ORADIM -DELETE -SID DBDEV



SQL> HOST ORADIM -NEW -SID DBDEV -INTPWD DBDEV -STARTMODE AUTO -PFILE=C:\oracle\admin\DBDEV\pfile\INIT.ORA



SQL> HOST ORADIM -NEW -SID DBDEV -INTPWD DBDEV -STARTMODE AUTO -PFILE C:\oracle\admin\DBDEV\pfile\INIT.ORA

Once the pre-requisite checks are satisfactory, startup the database in migrate mode.

SQL> startup migrate
ORACLE instance started.

Total System Global Area  472457268 bytes                                    
Fixed Size                   454708 bytes                                    
Variable Size             369098752 bytes                                    
Database Buffers          100663296 bytes                                    
Redo Buffers                2240512 bytes                                    
Database mounted.
Database opened.

To start the Migrate/Upgrade process, run the following script:

SQL> @C:\oracle\product\9.2.0\db_1\rdbms\admin\U0900010

SQL> SELECT COMP_NAME, VERSION, STATUS
  2  FROM DBA_REGISTRY;

COMP_NAME                           VERSION                        STATUS    
----------------------------------- ------------------------------ -----------
Oracle9i Catalog Views              9.2.0.8.0                      VALID     
Oracle9i Packages and Types         9.2.0.8.0                      VALID     
JServer JAVA Virtual Machine        9.0.1                          LOADED    
Java Packages                       9.0.1                          LOADED    
Oracle XDK for Java                 9.0.1                          LOADED    
Oracle Text                         9.0.1                          LOADED    
Oracle Workspace Manager            9.0.1.0.0                      LOADED    
Oracle interMedia                   9.0.1.0.1                      LOADED    

8 rows selected.

To Upgrade the Components, run the following script:

SQL> @C:\oracle\product\9.2.0\db_1\rdbms\admin\CMPDBMIG

SQL> SELECT COMP_NAME, VERSION, STATUS
  2  FROM DBA_REGISTRY;

COMP_NAME                           VERSION                        STATUS    
----------------------------------- ------------------------------ -----------
Oracle9i Catalog Views              9.2.0.8.0                      VALID     
Oracle9i Packages and Types         9.2.0.8.0                      VALID     
JServer JAVA Virtual Machine        9.2.0.8.0                      VALID     
Oracle9i Java Packages              9.2.0.8.0                      VALID     
Oracle XDK for Java                 9.2.0.10.0                     UPGRADED  
Oracle Text                         9.0.1                          LOADED    
Oracle Workspace Manager            9.2.0.1.0                      VALID     
Oracle interMedia                   9.0.1.0.1                      LOADED    

8 rows selected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

We need to compile the invalid objects from the Migrate/Upgrade process.

 SQL> startup restrict
ORACLE instance started.

Total System Global Area  472457268 bytes                                    
Fixed Size                   454708 bytes                                    
Variable Size             369098752 bytes                                    
Database Buffers          100663296 bytes                                    
Redo Buffers                2240512 bytes                                    
Database mounted.
Database opened.

SQL> @C:\oracle\product\9.2.0\db_1\rdbms\admin\UTLRP

PL/SQL procedure successfully completed.

Table created.

Table created.

Table created.

Index created.

Table created.

Table created.

View created.

View created.

Package created.

No errors.

Package body created.

No errors.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  472457268 bytes                                    
Fixed Size                   454708 bytes                                    
Variable Size             369098752 bytes                                    
Database Buffers          100663296 bytes                                    
Redo Buffers                2240512 bytes                                    
Database mounted.
Database opened.

SQL> SELECT COMP_NAME, VERSION, STATUS
  2  FROM DBA_REGISTRY;

COMP_NAME                           VERSION                        STATUS    
----------------------------------- ------------------------------ -----------
Oracle9i Catalog Views              9.2.0.8.0                      VALID     
Oracle9i Packages and Types         9.2.0.8.0                      VALID     
JServer JAVA Virtual Machine        9.2.0.8.0                      VALID     
Oracle9i Java Packages              9.2.0.8.0                      VALID     
Oracle XDK for Java                 9.2.0.10.0                     VALID     
Oracle Text                         9.0.1                          LOADED    
Oracle Workspace Manager            9.2.0.1.0                      VALID     
Oracle interMedia                   9.0.1.0.1                      LOADED    

8 rows selected.

To upgrade “Oracle Text” and “Oracle interMedia”, I referred the following Metalink Notes :

• 275689.1 Manual installation, deinstallation of Oracle Text 9.2, and
• 337415.1 Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia?

Now, my test database was successfully upgraded to Oracle 9iR2 (9.2.0.8). Similarly, I set up another test database and this time carried out my simulated migration using DBUA. Using the Database Upgrade Assistant for migration of the test database was really smooth. Just, Sit back and watch! But, need to watch for error messages.

Parallel to the test run activities, we received the good news that the Production Server’s RAM can now be upgraded from 1.5 GB to 4 GB, as the RAM for our server model is available. This really made the production migration activity to run smoothly. Without any further delay, we planned for an appropriate maintenance window and upgraded the Server's RAM.

Subsequently, I carried out the Oracle SGA Optimization, by appropriately sizing the SGA components to increase the SGA from 930 MB to 1.4 GB. And remaining 500-600 MB from the 2 GB process limit was configured for the user sessions’ memory. As we were running Windows 2000 Server Standard Edition, we were not in a position to enable Address Windowing Extensions [Metalink Note: 225349.1] to use. So the remaining 2 GB RAM remains unutilized.

By now, I was quite comfortable with both the upgrade processes, and I decided to carry out the production migration from Oracle 9iR1 (9.0.1) to Oracle 9iR2 (9.2.0.8) using DBUA. I planned for an ample maintenance window and, with the required technical assistance from the IT Admins, upgraded the production database using Database Upgrade Assistant. While upgrade I received the following errors:

Step Execution Information:
Oracle Database

Error ignored: ORA-00904: "TYPE": invalid identifier
Error ignored: ORA-24032: object AQ$_AQ_EVENT_TABLE_T exists, index could not be created for queue table AQ_EVENT_TABLE ORA-06512: at line 6
Error ignored: ORA-24002: QUEUE_TABLE SYS.AQ_EVENT_TABLE does not exist ORA-06512: at line 6
Error ignored: ORA-24010: QUEUE SYS.AQ_EVENT_TABLE_Q does not exist ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3676 ORA-06512: at "SYS.DBMS_AQADM", line 217 ORA-06512: at line 2
Error ignored: ORA-24032: object AQ$_AQ_SRVNTFN_TABLE_T exists, index could not be created for queue table AQ_SRVNTFN_TABLE ORA-06512: at line 6
Error ignored: ORA-24002: QUEUE_TABLE SYS.AQ_SRVNTFN_TABLE does not exist ORA-06512: at line 6
Error ignored: ORA-24010: QUEUE SYS.AQ_SRVNTFN_TABLE_Q does not exist ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3676 ORA-06512: at "SYS.DBMS_AQADM", line 217 ORA-06512: at line 2
Error ignored: ORA-24006: cannot create QUEUE, SYSTEM.AQ$_DEF$_AQCALL_E already exists ORA-06512: at line 9
Error ignored: ORA-24002: QUEUE_TABLE SYSTEM.DEF$_AQCALL does not exist ORA-06512: at line 19
Error ignored: ORA-24006: cannot create QUEUE, SYSTEM.AQ$_DEF$_AQERROR_E already exists ORA-06512: at line 9
Error ignored: ORA-24002: QUEUE_TABLE SYSTEM.DEF$_AQERROR does not exist ORA-06512: at line 19
Error ignored: ORA-04063: view "SYS.DBA_LOGSTDBY_PROGRESS" has errors

Go to top

As most of the errors where related to Streams, and we were not using any Streams, I ignored and completed the upgrade using DBUA, and checked the health of the production instance.

SQL>
SQL> select * from v$version;

BANNER                                                                       
----------------------------------------------------------------             
Oracle9i Release 9.2.0.8.0 - Production                                      
PL/SQL Release 9.2.0.8.0 - Production                                        
CORE 9.2.0.8.0  Production                                                    
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production                       
NLSRTL Version 9.2.0.8.0 - Production                                        

SQL>
SQL> select substr(comp_id,1,12) comp_id, status, substr(version,1,10) version,
  2  substr(comp_name,1,40) comp_name from dba_registry order by 1;

COMP_ID      STATUS      VERSION    COMP_NAME                                
------------ ----------- ---------- ---------------------------------------- 
AMD          OPTION OFF  9.0.1.0.0  OLAP Catalog                             
CATALOG      INVALID     9.2.0.8.0  Oracle9i Catalog Views                   
CATJAVA      VALID       9.2.0.8.0  Oracle9i Java Packages                   
CATPROC      INVALID     9.2.0.8.0  Oracle9i Packages and Types              
CONTEXT      VALID       9.2.0.8.0  Oracle Text                              
JAVAVM       VALID       9.2.0.8.0  JServer JAVA Virtual Machine             
ORDIM        VALID       9.2.0.8.0  Oracle interMedia                        
OWM          VALID       9.2.0.1.0  Oracle Workspace Manager                 
RAC          INVALID     9.2.0.8.0  Oracle9i Real Application Clusters       
SDO          VALID       9.2.0.8.0  Spatial                                  
WK           LOADED      9.0.1.0.0  Ultrasearch                              
XML          VALID       9.2.0.10.0 Oracle XDK for Java                      

12 rows selected.

SQL> SELECT SUBSTR(OWNER,1,12) OWNER, SUBSTR(OBJECT_NAME,1,30) OBJECT,
  2  SUBSTR(OBJECT_TYPE,1,30) TYPE, STATUS
  3  FROM DBA_OBJECTS
  4  WHERE STATUS <> 'VALID';

SYS          AQ$_AQ_EVENT_TABLE_E           QUEUE              INVALID
SYS          AQ_EVENT_TABLE_Q               QUEUE              INVALID
SYS          CATALOG_TYP                    TYPE               INVALID
SYS          CATALOG_TYP                    TYPE BODY          INVALID
SYS          CATINDEXMETHODS                TYPE               INVALID
SYS          DBA_LOGSTDBY_PROGRESS          VIEW               INVALID
SYS          DBMS_INTERNAL_LOGSTDBY         PACKAGE BODY       INVALID
SYS          DBMS_LOGSTDBY                  PACKAGE BODY       INVALID
SYS          RULEINDEXMETHODS               TYPE               INVALID
SYS          TEXTOPTSTATS                   TYPE               INVALID
QS_ADM       QS_APPLICATIONS                PACKAGE BODY       INVALID

11 rows selected.

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

NLS_NCHAR_CHARACTERSET         AL16UTF16

Despite all the errors, the ERP Application was running fine. I had to fix the invalid database components and the AQ invalid objects. I immediately raised Priority Service Request (7020306.994) with Oracle Support for each of the invalid components.

The invalid objects were of 2 types:

1. INVALID AQ objects
   Support first suggested dropping the AQ objects, as we don’t use them. Later, I suggested the Support to fix the object rather than deleting them (Spin off SR: 19434118.6). Then, I was told that we have a metadata corruption. To resolve the invalid AQ objects, I referred Metalink Note: 203225.1 (To Manually Cleanup Advanced Queuing Tables)
2. INVALID Log Miner related objects
   To resolve the invalid objects pertaining to Log Miner, I was re-directed to Metalink Note: 309814.1 (Invalid SYS.DBMS_INTERNAL_LOGSTDBY Package Before or After Migrate)

SQL> select substr(comp_id,1,12) comp_id, status, substr(version,1,10) version,
  2  substr(comp_name,1,40) comp_name from dba_registry order by 1;

COMP_ID      STATUS      VERSION    COMP_NAME                                
------------ ----------- ---------- ---------------------------------------- 
AMD          OPTION OFF  9.0.1.0.0  OLAP Catalog                             
CATALOG      VALID       9.2.0.8.0  Oracle9i Catalog Views                   
CATJAVA      VALID       9.2.0.8.0  Oracle9i Java Packages                   
CATPROC      VALID       9.2.0.8.0  Oracle9i Packages and Types              
CONTEXT      VALID       9.2.0.8.0  Oracle Text                              
JAVAVM       VALID       9.2.0.8.0  JServer JAVA Virtual Machine             
ORDIM        VALID       9.2.0.8.0  Oracle interMedia                        
OWM          VALID       9.2.0.1.0  Oracle Workspace Manager                 
RAC          INVALID     9.2.0.8.0  Oracle9i Real Application Clusters       
SDO          VALID       9.2.0.8.0  Spatial                                  
WK           LOADED      9.0.1.0.0  Ultrasearch                              
XML          VALID       9.2.0.10.0 Oracle XDK for Java                      

12 rows selected.

To upgrade the Ultrasearch manually, the following steps were suggested by Oracle Support:

The simplest method to make Ultra Search VALID is to manually reinstall it.
Step1: Deinstall Ultra Search.Note 177203.1 How to Manually Install and Uninstall Ultra Search 9.0.1 Server Components.
Follow, "MANUAL DEINSTALLATION OF ULTRA SEARCH DATABASE OBJECTS" from the above Doc,Step2: 
Install Ultra Search.Note 222517.1 How to Manually Install and Uninstall Ultra Search 9.2.0.x Server Components
Follow, "MANUAL INSTALL STEPS" from the above Doc.
Now check for the status and WKSYS invalid objects.select comp_id, comp_name, status, version from dba_registry;select object_name, object_type from dba_objects where status='INVALID' and owner='WKSYS';

 

To resolve the Invalid RAC component issue, the Oracle support redirected me to Metalink Note 312071.1 (RAC Option Invalid after Migration). Here, we didn’t have to do anything but leave it as it is. Why? You can refer the below RCA and Solution from the Metalink Note 312071.1:

Previous versions (8.1.7/9.0.1) didn’t use a registry for components. 
During migration to 9.2 or 10g the registry (DBA_REGISTRY) was created with information from database. 
For RAC, the entry in the registry is created if view V$PING exists in the database at migration time. 
Unfortunately this view (V$PING) was created by catparr.sql, a script which could be used although RAC hadn't been installed.
Therefore if V$PING exists during migration to 9.2, 10g or 10gR2, a entry for RAC is created in DBA_REGISTRY. Later, cmpdbmig script checks DBA_REGISTRY and tries to upgrade all components, including RAC. 
As really RAC is not installed, the component becomes invalid.
 
Solution
If you want to eliminate the line from registry you can:- downgrade to 8.1.7- remove view V$PING- upgrade to 9.2, 10g or 10gR2 againBut the best solution is to ignore this line if RAC is not installed in the database.
 

 

After resolving all the issues, I was now in a position to pronounce the production database migration from Oracle 9iR1 (9.0.1) to 9iR2 (9.2.0.8) as “Successful”.

SQL> select comp_id, comp_name, status, version from dba_registry;

COMP_ID                        COMP_NAME                               STATUS      VERSION 
------------------------------ --------------------------------------- ----------- ------------
CATALOG                        Oracle9i Catalog Views                  VALID       9.2.0.8.0
CATPROC                        Oracle9i Packages and Types             VALID       9.2.0.8.0
JAVAVM                         JServer JAVA Virtual Machine            VALID       9.2.0.8.0
CATJAVA                        Oracle9i Java Packages                  VALID       9.2.0.8.0
XML                            Oracle XDK for Java                     VALID       9.2.0.10.0
CONTEXT                        Oracle Text                             VALID       9.2.0.8.0
RAC                            Oracle9i Real Application Clusters      INVALID     9.2.0.8.0
OWM                            Oracle Workspace Manager                VALID       9.2.0.1.0
ORDIM                          Oracle interMedia                       VALID       9.2.0.8.0
SDO                            Spatial                                 VALID       9.2.0.8.0
AMD                            OLAP Catalog                            OPTION OFF  9.0.1.0.0
WK                             Oracle Ultra Search                     VALID       9.2.0.8.0

12 rows selected.

SQL> ed
Wrote file afiedt.buf

  1* select object_name, object_type from dba_objects where status='INVALID'
SQL> /

no rows selected

Now, I was in a position to pronounce the production database migration from Oracle 9iR1 (9.0.1) to 9iR2 (9.2.0.8) as “Successful”.

It’s been almost more than 10 months now, and we haven’t encountered any ORA-600 [25012] or ORA-600 [12209]. The Systems are running smooth, there are neglibible user performance complaints, and I have more control over the whole ERP Database.

Post upgrade, I was able to successfully implement Oracle RMAN Backup & Recovery. Despite the delay in procurement of new Hardware being (due to the global economic recession), we are now relatively comfortable to migrate the production database from Oracle 9i R2 (9.2.0.8) to either Oracle 10g R2 or Oracle 11g R1.

This is my “Journey from 9.0.1 to 9.2.0.8”. What I’ve learnt from this journey is that for a successful implementation you definitely need technical skills and functional skills, but as in my case, having strong people skills helps you overcome obstacles, by finding workarounds you'll never think of in a routine migration. I sincerely hope that “Journey from 9.0.1 to 9.2.0.8” will be of some use to the Oracle wayfarers, and look forward to sharing more accomplishments in the near future.