Operating System - HP-UX
1748195 Members
2688 Online
108759 Solutions
New Discussion юеВ

Re: Oracle control files: How to recreate them?

 
SOLVED
Go to solution
Martin Johnson
Honored Contributor

Oracle control files: How to recreate them?

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
5 REPLIES 5
Volker Borowski
Honored Contributor
Solution

Re: Oracle control files: How to recreate them?

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
Volker Borowski
Honored Contributor

Re: Oracle control files: How to recreate them?

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
;
Stan_17
Valued Contributor

Re: Oracle control files: How to recreate them?

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

Martin Johnson
Honored Contributor

Re: Oracle control files: How to recreate them?

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
Indira Aramandla
Honored Contributor

Re: Oracle control files: How to recreate them?

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



Never give up, Keep Trying