Wednesday, March 14, 2012

How to recover a database with missing archived redo logs

Have you ever faced the situation where you need to recover a database with missing archives, and because of that Oracle won't let you open the database?

Here is an excelent article explaining what to do.


Sunday, March 11, 2012

Clone your production database to a test environment

My first post will show you the steps to prepare and run a script to automatically duplicate an Oracle Database. This is a very useful setup when you need a clone of your production database for testing purposes on a regular basis.

I'll be using as a example the following config:
-Source database: PRODDB on host PROD
-Destination database: TESTDB on host TEST
-Oracle database 10g/11g with ASM
-Backup and archived redo logs of PRODDB on a nfs location accessible from the two hosts (not needed if using FROM ACTIVE DATABASE option)
-PRODDB database uses a catalog for rman backups.
- ASM has two diskgroups: FLASH (for FLASH RECOVERY AREA) and DATA (for regular database files) on both hosts. ASM owner is the same as db owner.

1. Put a static entry in listener.ora of TEST host

    (SID_LIST =
      (SID_DESC =
         (SID_NAME = +ASM)
         (SERVICE_NAME = +ASM)
         (ORACLE_HOME = /u01/app/oracle/product/
      (SID_DESC =
         (SID_NAME = TSTDB)
         (ORACLE_HOME = /u01/app/oracle/product/

2. Put an entry in tnsnames.ora file of both hosts for the two databases

On host PROD:

    (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
      (SERVICE_NAME = testdb)
      (INSTANCE_NAME = testdb)