11 October 2013


Yesterday I was creating a new Oracle database from a copy of datafiles and archivelogs taken from our standby. I was sure to include archivelogs from just prior to well after the span of the datafile backup time. I had created a new controlfile, gotten everything mounted and recovered all of the archivelogs I had using "recover database using backup controlfile;". The next step was to open the new database with reset logs, right? WRONG.

Here is my sqlplus session showing the end of the recovery and my attempt to open the database:

ORA-00279: change 506393850182 generated at 10/04/2013 20:54:13 needed for
thread 3
ORA-00289: suggestion :
ORA-00280: change 506393850182 for thread 3 is in sequence #36561
ORA-00278: log file
no longer needed for this recovery

ORA-00308: cannot open archived log
ORA-17503: ksfdopn:4 Failed to open file
ORA-17500: ODM err:File does not exist

SQL> alter database open resetlogs;
alter database open resetlogs
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:

10 October 2013

Oracle Restart Underscore Warning!

Bobby Curtis just posted a great post about adding a database to Oracle Restart, aka srvctl. The post is a must-read, but I do have one thing to add. In Bobby's example, he leaves out the instance_name clause (noted by the -i parameter). This won't cause a problem for him because his database's db_unique_name has no underscores and matches his ORACLE_SID (which can't contain underscores anyway).

In my environment, however, nearly every database has a db_unique_name value that contains an underscore, which we use to denote the datacenter location. For example, in our dataguard configuration for "prod", prod_ny would be in New York where as prod_sf would be in San Francisco. The fact that it is part of a dataguard configuration is irrelevant, we have this configuration with standalone databases (e.g. staging1_ny) as well just to be consistent. In the first example, both databases have a db_name and ORACLE_SID of "prod", and the second example would have an ORACLE_SID of staging1.