Search

OakieTags

Who's online

There are currently 0 users and 36 guests online.

Recent comments

Affiliations

Creating Controlfile From Scratch when No Backup is Available

You have lost the controlfile, the catalog and the backup to the controlfile too; so restoring the controlfile from a previous backup is not an option. How can you recover the database? By creating the controlfile from scratch. Interested in learning how? Read on.

Here is a final thread to the blog posts I had posted in the last three days, about interesting situations faced by John the DBA at Acme Bank. In the first post, you saw how John restored a controlfile when the autobackup was not being done. In the second post you learned how John discovered the DBID when someone forgot to record it somewhere. In the final installment you will see what John does when the controlfile backup simply does not exist, or exists somewhere but simply can't be found, thus rendering the previous tips useless.

This time, John had to recreate the controlfile from scratch. Let me reiterate, he had to recreate the controlfile, using SQL; not restore it from somewhere. How did he do it? Following his own "best practices", honed by years and years of managing Oracle databases, wise ol' John always takes a backup of the controlfile to trace using this command:

 alter database backup controlfile to trace as '/tmp/cont.sql' reuse;

This command produces a text file named cont.sql, which is invaluable in creating the controlfile. John puts the command as a cron job (in Unix; as a auto job on Windows) on database servers so that this command gets excuted every day creating the text file. The "reuse" option at the end ensures the command overwrites the existing file which means the text file contains fresh data from the database when it is opened. Here is an except from the beginning of the generated file.

 -- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
... output removed for brevity...

It is a very long file. John scrolls down to the section that shows the following information:

 -- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.

-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

CREATE CONTROLFILE REUSE DATABASE "PROQA3" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 4
    MAXDATAFILES 800
    MAXINSTANCES 8
    MAXLOGHISTORY 10225
LOGFILE
  GROUP 3 (
    '+PROQA3REDOA/PROQA3/PROQA3_redo103a.rdo',
    '+PROQA3REDOB/PROQA3/PROQA3_redo103b.rdo'
  ) SIZE 2048M BLOCKSIZE 512,
  GROUP 4 (
    '+PROQA3REDOA/PROQA3/PROQA3_redo104a.rdo',
  GROUP 4 (
    '+PROQA3REDOA/PROQA3/PROQA3_redo104a.rdo',
    '+PROQA3REDOB/PROQA3/PROQA3_redo104b.rdo'
  ) SIZE 2048M BLOCKSIZE 512,
  GROUP 5 (
    '+PROQA3REDOA/PROQA3/PROQA3_redo105a.rdo',
    '+PROQA3REDOB/PROQA3/PROQA3_redo105b.rdo'
  ) SIZE 2048M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+PROQA3DATA1/PROQA3/PROQA1_system_01.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_sysaux_01.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_undo1_01.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_users_data01_01.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_xdb_tbs_01.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_abcdefg_small_data1_03.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_abcdefg_large_data1_01.dbf',

... output removed for brevity ...


  '+PROQA3DATA1/PROQA3/PROQA1_undo1_02.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_undo1_03.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_abcdefg_large_data1_09.dbf',
  '+PROQA3DATA1/PROQA3/PROQA1_sysaux_03.dbf'
CHARACTER SET AL32UTF8
;

As you can see, this file contains a complete syntax for creating the controlfile using CREATE CONTROLFILE command. But more important, the command contains all the data files and online redo logs of the database. This is invaluable information to create the controlfile. John creates a SQL script file called create_controlfile.sqlwhere he puts the CREATE CONTROLFILE SQL command. It's one long command with several lines. Here is how the file looks like (with lines removed in between for brevity). Remember, this is just one command; so, there is just one semicolon at the end for the execution:

 CREATE CONTROLFILE REUSE DATABASE "PROQA3" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 32

... output removed for brevity ...

  '+PROQA3DATA1/PROQA3/PROQA1_sysaux_03.dbf'
CHARACTER SET AL32UTF8
;

Then John extracts the following commands immediately following the CREATE CONTROLFILE command from that above mentioned file and puts them on another file named create_temp_tablespaces.sql:

 -- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_01.dbf'
     SIZE 31744M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_02.dbf'
     SIZE 30720M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_03.dbf'
     SIZE 30720M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_04.dbf'
     SIZE 30720M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_05.dbf'
     SIZE 30720M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_06.dbf'
     SIZE 31744M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+PROQA3DATA1/PROQA3/PROQA1_temp1_07.dbf'
     SIZE 31744M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

With the preparations completed, John proceeds to next steps. First, he starts up the instance with NOMOUNT option. He has to use NOMOUNT anyway since the controlfile is missing:

 startup nomount

This command brings up the instance only. Next, John creates the controlfile by executing the file he created earlier--create_controlfile.sql. When the comamnd succeeds, he gets the following message:

 Control file created.

Voila! The controlfile is now created from scratch. With that the database is mounted automatically. However, this newly created controlfile is empty; it does not have any information on the database, sequence numbers, etc. It reads the information from the datafile headers; but the data files may have been checkpointed at points in the past. John has to bring them up as much forward as possible. He has to perform a recovery on the datafiles. From the SQL*Plus prompt, he issues this statement:

SQL> recover database using backup controlfile;

ORA-00279: change 7822685456060 generated at 04/25/2014 17:11:38 needed for thread 1
ORA-00289: suggestion : +PROQA3ARCH1
ORA-00280: change 7822685456060 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

It's important that John uses "using backup controlfile" option. This controlfile is not the current one; so the recovery process must know that.  John carefully notes the SCN# of the archived log being asked for--7,822,685,456,060. He has to provide an archived log that contains changes with this SCN. To know that, he opens up another SQL*Plus window, connects as sysdba and gathers the archived log information:

 col first_change# head "First SCN# in Archive" format 999,999,999,999,999
col name format a80
select first_change#, name
from v$archived_log
order by 1
/

Here is the output:

 First SCN# in Archive NAME
--------------------- -----------------------------------------------------------------------
    7,822,681,948,348 +PROQA3ARCH1/PROQA3/archivelog/2014_04_12/thread_1_seq_1.285.844655135
    7,822,681,949,237 +PROQA3ARCH1/PROQA3/archivelog/2014_04_12/thread_1_seq_2.286.844655143
    7,822,681,950,115 +PROQA3ARCH1/PROQA3/archivelog/2014_04_12/thread_1_seq_3.287.844664447

... output removed for brevity ...

    7,822,685,451,799 +PROQA3ARCH1/PROQA3/archivelog/2014_04_25/thread_1_seq_1.328.845828911
    7,822,685,453,816 +PROQA3ARCH1/PROQA3/archivelog/2014_04_25/thread_1_seq_2.330.845829419

Referring to this output, he sees that the latest archived log has the starting SCN# of 7,822,685,453,816, which is less than the SCN# being asked for. Therefore this archived log may or may not contain the changes being asked by the recovery process. He decided to give that archived log anyway. So he pastes the entire path of the archived log at the prompt:

+PROQA3ARCH1/PROQA3/archivelog/2014_04_25/thread_1_seq_2.330.845829419

Oracle immediately responds with:

 ORA-00310: archived log contains sequence 2; sequence 3 required
ORA-00334: archived log: '+PROQA3ARCH1/PROQA3/archivelog/2014_04_25/thread_1_seq_2.330.845829419'

Clearly, the archived log John supplied is not something the the recovery process was looking for. But that was the latest archived log; there is nothing after that. Remember, the data could also be there on the online redo log which have not been archived yet. John has to make a decision here. If the online redo logs are not available, he needs to end the recovery here by typing:

 cancel

Oracle responds by:

 Media Recovery canceled

After that, John opens the database:

 alter database open resetlogs;

On the other hand, if the online redo logs are intact and available, he will need to just pass it to the recovery process. He gathers the details on the online redo logs from the other SQL*Plus window:

 select sequence#, member
from v$log l, v$logfile f
where f.group# = l.group#
order by 1;

 SEQUENCE# MEMBER
-------- -------------------------------------------
       1 +PROQA3REDOA/PROQA3/PROQA3_redo103a.rdo
         1 +PROQA3REDOB/PROQA3/PROQA3_redo103b.rdo
         2 +PROQA3REDOA/PROQA3/PROQA3_redo104a.rdo
         2 +PROQA3REDOB/PROQA3/PROQA3_redo104b.rdo
         3 +PROQA3REDOA/PROQA3/PROQA3_redo105a.rdo
         3 +PROQA3REDOB/PROQA3/PROQA3_redo105b.rdo

From the first SQL*Plus window, John starts the recovery process again (the recovery process ends when it does not get the file it expects) and this time he supplies the name of the online redo log file:

 SQL> recover database using backup controlfile;

ORA-00279: change 7822685456060 generated at 04/25/2014 17:11:38 needed for thread 1
ORA-00289: suggestion : +PROQA3ARCH1
ORA-00280: change 7822685456060 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
+PROQA3REDOA/PROQA3/PROQA3_redo105a.rdo

Oracle responds by:

 Log applied.
Media recovery complete.

Voila! The database does not need any other recovery. Since the online logfile contains the last known change, Oracle knows that there is no further recovery required and hence it stops asking for any more changes. John has just recovered all the changes made to the database; nothing was lost. He proceeds to opening the database.

 alter database open resetlogs

Resetlogs is necessary here because John used a controlfile that he created. Remember, this is a complete recovery (nothing was lost); but the database must be opened with resetlogs. This starts the log sequence at 1 again. From a different window, John opens up the alert log of the database and checks for the output:

 ... previous output removed for brevity ...

alter database open resetlogs
RESETLOGS after complete recovery through change 7822685456061
SUCCESS: diskgroup PROQA3REDOB was mounted
Fri Apr 25 17:47:12 2014
NOTE: dependency between database PROQA3 and diskgroup resourceora.PROQA3REDOB.dg is established
Archived Log entry 47 added for thread 1 sequence 1 ID 0xffffffff983ca615 dest 1:
Archived Log entry 48 added for thread 1 sequence 2 ID 0xffffffff983ca615 dest 1:
Archived Log entry 49 added for thread 1 sequence 3 ID 0xffffffff983ca615 dest 1:
Clearing online redo logfile 3 +PROQA3REDOA/PROQA3/PROQA3_redo103a.rdo
 Clearing online log 3 of thread 1 sequence number 1
Fri Apr 25 17:47:24 2014
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +PROQA3REDOA/PROQA3/PROQA3_redo104a.rdo
Clearing online log 4 of thread 1 sequence number 2
Fri Apr 25 17:47:37 2014
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 +PROQA3REDOA/PROQA3/PROQA3_redo105a.rdo
Clearing online log 5 of thread 1 sequence number 3
Fri Apr 25 17:47:47 2014
Clearing online redo logfile 5 complete
Resetting resetlogs activation ID 2554111509 (0x983ca615)
Online log +PROQA3REDOA/PROQA3/PROQA3_redo103a.rdo: Thread 1 Group 3 was previously cleared
Online log +PROQA3REDOB/PROQA3/PROQA3_redo103b.rdo: Thread 1 Group 3 was previously cleared
 Online log +PROQA3REDOA/PROQA3/PROQA3_redo104a.rdo: Thread 1 Group 4 was previously cleared
Online log +PROQA3REDOB/PROQA3/PROQA3_redo104b.rdo: Thread 1 Group 4 was previously cleared
Online log +PROQA3REDOA/PROQA3/PROQA3_redo105a.rdo: Thread 1 Group 5 was previously cleared
Online log +PROQA3REDOB/PROQA3/PROQA3_redo105b.rdo: Thread 1 Group 5 was previously cleared
Fri Apr 25 17:47:47 2014
Setting recovery target incarnation to 4
Using SCN growth rate of 16384 per second
  Fri Apr 25 17:47:48 2014
Assigning activation ID 2554183704 (0x983dc018)
LGWR: STARTING ARCH PROCESSES

... output removed for brevity ...

The output shows that the online redo logs started at sequence #1. With the recovery now complete, John creates the temporary tablespaces using the script he had created earlier--create_temp_tablespaces.sql. Then he passes the database to the users for normal processing.

Creation of Controlfile from a Different Database

What if John had not created the controlfile trace? The recovery would still be possible but he would had to remember the names of all the datafiles and redo log files. In that case, he would have to create the create controlfile command from any other database, edit that file to put the names of the PROQA3 database objects he was trying to recover, and then create the controlfile. It's possible; but difficult.

Takeaways

What did you learn from this story and John? Here is a summary:

  1. Always use a recovery catalog. This post assumes that you lost that catalog as well; but now you see how difficult it is without the catalog.
  2. Always set the controlfile to autobackup. From the RMAN command prompt, issue configure controlfile autobackup on. The default if off.
  3. Always backup the RMAN logfile to the tape or other location where it would be available even after the main sever with the database itself is inaccessible.
  4. Always backup the controlfile to trace with a cron job that executes once a day and updates the existing file.
  5. If the controlfile backup is missing, check for the controlfile backup in the following possible locations:
    • snapshot controlfile
    • backup taken in some location
  6. Look for possible controlfile backups from RMAN log files.
  7. If no backup of controlfile is available, create the controlfile from the trace you have presumably created.
  8. While recovering the database after creating the controlfile, always try giving the most recent online redo logs as archived log names to achieve a complete recovery.

Thank you for reading. As always, I will appreciate your feedback. Tweet me at @ArupNanda, or just post a comment here.