topic Re: Oracle control files: How to recreate them? in Operating System - HP-UX

Oracle control files: How to recreate them?

Martin Johnson — Tue, 25 Feb 2003 18:11:18 GMT

The oracle control files:

/var/opt/OV/oracle/openview/control0?.ctl

were open when the backup of the system was done and they were not included in the backup. How do I recreate them?

Thanks for your help,
Marty

Re: Oracle control files: How to recreate them?

Volker Borowski — Tue, 25 Feb 2003 18:22:20 GMT

Hi Marty,

the easiest way would be, if you have another Oracle DB of the same release running.

On this DB you do a
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
You will find a *.trc file in the userdump destination which contains a quite good template of your statement.

You will need the archive logs as well, if the backup was taken online. Since you have no binary copy of you controlfile, you might need to recover like this:

startup nomount
create controlfile ...
recover database using backup controlfile until cancel;
... provide archive logs as needed.
alter database open resetlogs;

Good luck
Volker

Re: Oracle control files: How to recreate them?

Volker Borowski — Tue, 25 Feb 2003 18:45:56 GMT

Here is a template:

May be "CHARACTER SET" applies to 8.1.x only (not really sure if it was already needed with 8.0.6).

Volker

-- Watch it
-- REUSE only, if old controlfiles exists
-- [NO]RESETLOGS ?
-- [NO]ARCHIVELOG ?

CREATE CONTROLFILE REUSE DATABASE "SID" NORESETLOGS ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 254
MAXINSTANCES 50
MAXLOGHISTORY 1134
LOGFILE
GROUP 11 (
'filename_of_group11_first_member',
'filename_of_group11_second_member'
) SIZE 10M,
GROUP 12 (
'filename_of_group12_first_member',
'filename_of_group12_second_member'
) SIZE 10M
DATAFILE
'filename_of_first_datafile',
'filename_of_second_datafile',
..... repeat as needed ......
'filename_of_last_datafile'
CHARACTER SET WE8DEC
;

Re: Oracle control files: How to recreate them?

Stan_17 — Tue, 25 Feb 2003 20:04:35 GMT

Hi marty,

Is the database down ? if not then you could use 'alter database backup controlfile to trace' -- ascii version of controlfile which has the entire structure of the database. if not follow the instruction given by other poster.

also, add 'alter database backup controlfile to 'location_to_backup_controlfile';' to you hotbackup script -- this makes a backup of binary version of the controlfile.

Stan

Re: Oracle control files: How to recreate them?

Martin Johnson — Tue, 25 Feb 2003 21:14:02 GMT

As part of the backup procedure the script ovbackup.ovpl was run prior to doing the system backup.

I was able to do a ovrestore.ovpl to recover the control files.

Marty

Re: Oracle control files: How to recreate them?

Indira Aramandla — Wed, 26 Feb 2003 06:49:23 GMT

Hi Martin,

If you have recovered all the datafiles and redologs from a COLD backup and do not have the control files, then you can create them the following script.

Eg: the script is called cr_ctrl_file.sql
Then do this
set your oracle sid.
$>svrmgrl
svrmgrl>connect internal
svrmgrl>startup nomount
SVRMGRL>@cr_ctrl_file.sql

The cr_ctrl_file.sql will contain the following statements.

CREATE CONTROLFILE REUSE DATABASE "dbname" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'path & name of the on-line redo logs' ) SIZE 25M,
GROUP 2 (
'path & name of the on-line redo logs' ) SIZE 25M,
DATAFILE
'path and name of the data file???,
'path and name of the data file???;

RECOVER DATABASE
ALTER DATABASE OPEN;

In the above script the dbname will be your database name. You have to make sure all the datafiles with extension .dbf are included in the DATAFILE listing in the above script. All the redo logs are also included in the LOGFILE listing in the above script. You can list the files in the directory for the correct name and path of the file locations.

When the database is opened, then the database is as on the date the COLD backup was taken. Now shutdown normal and take a full backup immediately.

On the other hand if you have restored from a on-line (HOT) backup, then you will need the archive logs to recover to a particular point in time.

Check you backup script to see if the backup is a COLD or a HOT backup. For COLD backup the database whill be shutdown for backup, for backup the database will not be shutdown as it is on-line backup. And also for HOT backup you should find the statements "Alter tablespace BEGIN backup /END backup".