RMAN Compressed Backups

RMAN Backups in Oracle Database 10g Standard Edition enables us to take compressed backups. "AS COMPRESSED BACKUPSET" is the addition in the default backup command:

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Well, that should be good, so I put the backup command at test for my newly migrated 10g Database. Here we go...

My Normal RMAN Backup takes an average of 30 minutes to complete, generating around 20.5 GB of Backupset for a 40GB Database.
So, I am expecting anything close to 40-50% compression (backupset size from 10-12 GB) after using the new command, and I am expecting the job to take more than 30 minutes to finish (say between 1-1:30 hrs).

And yet again, Oracle makes my day, today!!! I not only finish the job in less than 30 minutes, but guess what!, The compression is magnificent. The job finished at an average of 14 mins (superb) and the compression is almost 85-90%, i.e. backupset size was 2.5 GB (Mind Blowing). Talk about the Performance Gains and Storage Save.

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;
Starting backup at
06-MAR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
.
.
.
input datafile fno=00001 name=E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=E:\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile fno=00005 name=E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 06-MAR-10
channel ORA_DISK_1: finished piece 1 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KTL7T94E_1_1_20100306
tag=TAG20100306T153854 comment=NONE
channel ORA_DISK_1: starting piece 2 at 06-MAR-10
channel ORA_DISK_1: finished piece 2 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KTL7T94E_2_1_20100306
tag=TAG20100306T153854 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:14:01
Finished backup at 06-MAR-10
Starting Control File and SPFILE Autobackup at 06-MAR-10
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORCL\C-1235707396-20100306-16 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-10

RMAN> BACKUP AS COMPRESSED BACKUPSET archivelog all;
Starting backup at 06-MAR-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1120 recid=1107 stamp=712943759
channel ORA_DISK_1: starting piece 1 at 06-MAR-10
channel ORA_DISK_1: finished piece 1 at 06-MAR-10
piece handle=E:\ORACLE\BACKUP\RMAN\ORCL\ORCL_KVL7TA4H_1_1_20100306
tag=TAG20100306T155601 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-MAR-10
Starting Control File and SPFILE Autobackup at 06-MAR-10
piece handle=\\BLADE5\MIS\BACKUP\RMAN\ORCL\C-1235707396-20100306-17 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-10

Disaster Recovery using RMAN Backups

Every DBA has to ensure that his hours spent in planning and testing the backup-recovery strategy works, especially it should be relied upon in any unexpected scenarios occur. We DBAs are always tend to be at ease when our scheduled backup jobs are performing well and are error-free, followed by couple of recovery testings scenarios.

Nevertheless, there are always scenarios which may be left unanticipated during the B/R Testing. One such scenario was haunting me since couple of days. And it took me a while to figure it out (due to known and unknown recovery conditions).

Let's take an example. Say I have a complete RMAN Backup including archivelog backups on Disks. Let's assume that the backups are all in recoverable conditions. And then, suddenly the Production Database Server goes off. Boom!!! The only way to recover the server is to rebuild it or to have us recover the Instance from the RMAN Backups on to another Server. In this post, we will see how to recover or reconstruct a Database by only using the RMAN Backups.

2 pre-conditions to use the RMAN Backups for recovery are:

1. Any Server should be available in a state where Oracle Product can be installed.
2. Same version of Oracle Product needs to be installed with which the RMAN Backup was taken

In my case the O/S is Windows and the Oracle Database Product is 9i R2 (9.2.0.8) Standard Edition.

The following steps would outline the procedure of recovering the Database using only RMAN Backups:

1. Create the Oracle Service
2. Create the relevant folders under admin and oradata folders
3. Restore the spfile from autobackup
4. Restore the controlfile from autobackup
5. Restore the Database
6. Recover the Database
7. Open the Database resetting the logs

We create an Oracle Service using oradim without passing any parameter file

c:\>oradim -new -sid DBTEST -intpwd DBTEST

Now, we need to create the relevant folders for Oracle Database, as follows:

1. Create BDUMP, CDUMP, and UDUMP folders under 'c:\oracle\admin' folder,
2. Create DBTEST folder under 'c:\oracle\oradata' folder

We connect to RMAN and start the Recovery process. First we need to set the DBID of the Database. At times we may or maynot know the DBID of the Database. There are 2 ways to find the DBID:

1. If any RMAN Logs are maintained, the DBID can be found when the initial connection is made to the target
2. If autobackup is enabled ans has the autobackup format set, with say '%F', then the filename of the autobackup has the DBID.
   In my case the file name is 'c-1103102985-20100216-02', and the DBID is '1103102985' in the filename.

And then we need to set the autobackup location which will help us identify the location where all the RMAN files.

C:\>rman
Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)

RMAN> set dbid=1103102985;
executing command: SET DBID

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\INITDBTEST.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97590688 bytes
Fixed Size 454048 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\testdb\dbtest\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Then we have to restore the SPFILE from the autobackup file. We can either give the keyword "autobackup" or directly reference the path and filename of the autobackup in the 'from' part of the command. I did the latter.

RMAN> restore spfile to 'c:\spdbtest01.ora' from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: c:\c-1103102985-20100216-02
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-FEB-10

Now, we need to shutdown the instance and startup with the restored parameter file.

RMAN> shutdown immediate
Oracle instance shut down

Remember that we have just restored the SPFILE in a non-default location and hence we would require referring a PFILE that should point to the SPFILE so as to start the instance. We need to create a text file, 'init.ora', and then mention the path and name of the restored SPFILE in it. The initDBTEST.ora would contain the following statement:

SPFILE=C:\spdbtest01.ora

And then, we need to reference the newly created PFILE to start the instance.

Alternately, you could also restore the SPFILE to the default home location, under DATABASE directory, where Oracle automatically looks for an SPFILE and thus avoid creating the PFILE to point at the SPFIL. I have chosen the former option for the demonstration.

Start the instance in NOMOUNT state using the parameter file pointing to the spfile.

RMAN> startup force pfile=’C:\initDBTEST.ora’ nomount
Oracle instance started

Total System Global Area 907482184 bytes
Fixed Size 455752 bytes
Variable Size 276824064 bytes
Database Buffers 629145600 bytes
Redo Buffers 1056768 bytes

Now, we need to restore the controlfile to bring back the database. Again here, we can either give the keyword ‘AUTOBACKUP’ or directly reference the path and filename of the autobackup in the ‘FROM’ part of the command.

RMAN> restore controlfile from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 16-FEB-10

With the restored controlfiles, now we are in a position to restore the Database, for which we need to mount the Instance first and the fire the restore command. Once restore is complete we need to fire the recover command for applying the archivelogs.

RMAN> alter database mount;
using target database controlfile instead of recovery catalog
database mounted

RMAN> restore database;
Starting restore at 16-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBTEST\DRSYS01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBTEST\EXAMPLE01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBTEST\INDX01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBTEST\TOOLS01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\DBTEST\USERS01.DBF
.
.
.
restoring datafile 00023 to C:\ORACLE\ORADATA\DBTEST\CWMLITE01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_1_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_2_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_3_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 4
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_4_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 5
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_5_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 6
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_6_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 7
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_7_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 8
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_8_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 9
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_9_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 16-FEB-10

RMAN> recover database;
Starting recover at 17-FEB-10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_03L65IJE_1_1_20100216 tag=FULL_160210_1230 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00002.001 thread=1 sequence=2
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_05L65Q1B_1_1_20100216 tag=FULL_160210_1430 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00003.001 thread=1 sequence=3
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================
RMAN-03002: failure of recover command at 02/17/2010 10:25:28
RMAN-06054: media recovery requesting unknown log: thread 1 scn 34558001

Don't Panic when you see the above RMAN Error Message. You just need to open the database with resetlogs as the recovery is over.

RMAN> alter database open resetlogs;
database opened

RMAN>

Looks simple! Yet it is a critical test case, as this is the what your full RMAN backups should do, i.e. A Full Recovery.

How to Enable Auditing in 10g Database

Check the values of audit parameters of your 10g Database, as it is a default disabled feature.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

The AUDIT_TRAIL parameter can be used to enable auditing by setting one of the following values:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with
all audit records stored in the database audit trial (SYS.AUD$).
db,extended
- As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml-
Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also
populated.
os- Auditing is enabled, with all audit records directed to the
operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.

The Database needs to be bounced for the change to take affect.

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

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
System altered.

The SQL_BIND and SQL_TEXT columns are populated, when extended option in AUDIT_TRAIL is enabled.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail string DB

The AUDIT_SYS_OPERATIONS parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
System altered.

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

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1299288 bytes
Variable Size 335547560 bytes
Database Buffers 1266679808 bytes
Redo Buffers 7086080 bytes
Database mounted.
Database opened.

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB, EXTENDED

Modifications of the data in the audit trail can be audited using the following statement:

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Audit succeeded.

To enable auditing on a schema/user we can use the following syntaxes

For auditing DDL (CREATE, ALTER & DROP of objects) statements

AUDIT ALL BY ACCESS;

For auditing DML (INSERT UPDATE, DELETE, SELECT, EXECUTE) statements

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY BY ACCESS;

For auditing SYSTEM EVENTS (LOGON, LOGOFF etc.) statements

AUDIT EXECUTE PROCEDURE BY BY ACCESS;

To monitor the audit trail we can use the DBA_AUDIT_TRAIL view.

Updates on New Server Migration & Testing

Quick update on the server migration status. It's been long since I haven't written on this.

Well, to start with, the Server is Huge and Powerful. That's what I would say as a summarized version. We carried out a simulated migration on the new server, and it was well-planned, quite simple and successful.

1. Oracle 10g R2 base release installation was done first,
2. Oracle 10.2.0.4 patchset was applied to the 10gR2 base home,
3. A new Database was created with optimum configurations (as of the 9iR2 production database),
4. Fresh export full dump of the 9iR2 production database was taken (created using 9i exp utility),
5. The data migration/import was carried out using (10g imp utility) into the new database,
6. Data migration/import had some invalid objects, which were fixed (most of them pertaining to invalid synonyms),
7. Oracle Enterprise Manager Database Control had some configuration issues related to SSL, which were fixed.

We have been gradually testing the 10g features and how the system performs on 10g, ever since.

Oracle Enterprise Manager Database Control is awesome. It gives amazing "on the finger tip" information availability. But, kind of miss those script runs at times; probably because I am more comfortable at command prompt.

A complete checklist has been provided to the Developers for an in-depth application testing on the new server, so as to avoid any unforeseen scenarios on the day of the migration.

I will keep you posted on when and how we will be planning the server migration. For now, the entire team is carrying out the system/application testing.

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].