Solved

Error when creating a db using backup controlfile - urgent

Posted on 2004-04-28
46
2,241 Views
Last Modified: 2011-10-03
Hi,
I get an error when I try to create db/controlfile using

STARTUP NOMOUNT pfile=/u3/oracle/devdb/8.1.7/dbs/initTEST.ora
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 1815
LOGFILE
  GROUP 1 (
    '/u3/oracle/devdata/log01a.dbf',
    '/u3/oracle/devdata/log01b.dbf'
  ) SIZE 20979200,
  GROUP 2 (
    '/u3/oracle/devdata/log02a.dbf',
    '/u3/oracle/devdata/log02b.dbf'
  ) SIZE 20979200,
  GROUP 3 (
    '/u3/oracle/devdata/log03a.dbf',
    '/u3/oracle/devdata/log03b.dbf'
  ) SIZE 20979200,
  GROUP 4 (
    '/u3/oracle/devdata/log04a.dbf',
    '/u3/oracle/devdata/log04b.dbf'
  ) SIZE 20979200,
  GROUP 5 (
    '/u3/oracle/devdata/log05a.dbf',
    '/u3/oracle/devdata/log05b.dbf'
  ) SIZE 20979200
DATAFILE
  '/u3/oracle/devdata/system01.dbf',
  '/u3/oracle/devdata/system02.dbf',
  '/u3/oracle/devdata/system03.dbf',
  '/u3/oracle/devdata/system04.dbf',
  '/u3/oracle/devdata/system05.dbf',
  '/u5/oracle/devdata/abmd01.dbf',
  '/u5/oracle/devdata/abmx01.dbf',
  ...
  ...
CHARACTER SET WE8ISO8859P1
;

This is the error:

CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log1a.dbf'

I tried removing REUSE keyword and running as well:

SQL> @initDEV.sql
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log1a.dbf'

However, when I do "file log1a.dbf "
this is what I see
log1a.dbf:     data

First, when the log files were copied from the original location, they showed up as symbolic links pointing to hidden files. For instance,
"ls -l log01a.dbf"
was showing up as
lrwxrwxrwx   1 root     dba           23 Apr 26 17:01 log01a.dbf -> .log01a.dbf::cdev:vxfs:

Then I did "mv .log01a.dbf log01a.dbf". This removed the link and retained the physical data file.

My question:

How do I fix this create controlfile error and create the db ?

Your help is greatly appreciated.
masaimara
0
Comment
Question by:masaimara
  • 19
  • 19
  • 7
  • +1
46 Comments
 
LVL 21

Expert Comment

by:oleggold
ID: 10941315
Everything seems OK,try to supply a full path for Your control file,like:
CREATE CONTROLFILE
  '/u3/oracle/devdata/newct.ctl',
 SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 1815
LOGFILE
  GROUP 1 (
    '/u3/oracle/devdata/log01a.dbf',
    '/u3/oracle/devdata/log01b.dbf'
  ) SIZE 20979200,
  GROUP 2 (
    '/u3/oracle/devdata/log02a.dbf',
    '/u3/oracle/devdata/log02b.dbf'
  ) SIZE 20979200,
  GROUP 3 (
    '/u3/oracle/devdata/log03a.dbf',
    '/u3/oracle/devdata/log03b.dbf'
  ) SIZE 20979200,
  GROUP 4 (
    '/u3/oracle/devdata/log04a.dbf',
    '/u3/oracle/devdata/log04b.dbf'
  ) SIZE 20979200,
  GROUP 5 (
    '/u3/oracle/devdata/log05a.dbf',
    '/u3/oracle/devdata/log05b.dbf'
  ) SIZE 20979200
DATAFILE
  '/u3/oracle/devdata/system01.dbf',
  '/u3/oracle/devdata/system02.dbf',
  '/u3/oracle/devdata/system03.dbf',
  '/u3/oracle/devdata/system04.dbf',
  '/u3/oracle/devdata/system05.dbf',
  '/u5/oracle/devdata/abmd01.dbf',
  '/u5/oracle/devdata/abmx01.dbf',
  ...
  ...
CHARACTER SET WE8ISO8859P1
;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941321
I am not sure that I understand you ...
can you clarify this?

the file that control file is complaining about is called "'/u1/oradev/data/log1a.dbf'


but in your controlfile creation statement:
the closest file is


'/u3/oracle/devdata/log01a.dbf',



the file path is completely off.

0
 

Author Comment

by:masaimara
ID: 10941349
I'm sorry I did not mention that. I got the errors after I corrected the path to '/u3/oracle/devdata/log01a.dbf'.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941384
@masaimara:

Can you do this as an alternative to create a controlfile.?

SQL>alter database backup controlfile to trace;

you can find this in udump folder..

0
 

Author Comment

by:masaimara
ID: 10941449
seazodiac,
I did the backup controlfile to trace and edited the controlfile to create DEV db. Do you want to see the trc file created in udump directory ?

oleggold,
I will let you know as soon as I try your procedure.

Thanks,
masaimara
0
 

Author Comment

by:masaimara
ID: 10941577
oleggold,
This is what I did

CREATE CONTROLFILE
  '/u3/oracle/devdata/cntrl01.dbf'
 SET DATABASE "DEV" RESETLOGS NOARCHIVELOG

and this is the error:

SQL> @startup_db.sql
ORACLE instance started.

Total System Global Area  365293728 bytes
Fixed Size                    73888 bytes
Variable Size             323194880 bytes
Database Buffers           40960000 bytes
Redo Buffers                1064960 bytes
SQL> @initDEV.sql
'/u3/oracle/devdata/cntrl01.dbf'
*
ERROR at line 2:
ORA-01967: invalid option for CREATE CONTROLFILE

masaimara
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941645
Are you trying to clone a database or rename the database name?

Can you outline the procedures you have done?

and be more specific when you can....
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941654
also a correction to oleggold's note:

YOu cannot have a name for controlfile in that clause.
0
 

Author Comment

by:masaimara
ID: 10941701
I am trying to clone a db and this is what I did:

1. I copied the Oracle Home and all the data files (PROD) required in the backup controlfile onto the DEV server.
2. I edited the backup controlfile to suit DEV directories and did a startup nomount before running CREATE CONTROLFILE script.
0
 

Author Comment

by:masaimara
ID: 10941712
Also, I set ORACLE_HOME, ORACLE_SID and the PATH before startup nomount.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941736
Ok,

Can you tell me exactly what error mesg are as of now when you run @initDEV.sql before you put the controlfile name in?
0
 

Author Comment

by:masaimara
ID: 10941873
Here is the error:

SQL> @initDEV.sql
CREATE CONTROLFILE
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u3/oracle/devdata/log01a.dbf'

and the here is how the create script looks like :

CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 1815
LOGFILE
  GROUP 1 (
    '/u3/oracle/devdata/log01a.dbf',
    '/u3/oracle/devdata/log01b.dbf'
    .....
    .....

One other thing I remembered was the control files. I did not copy them from PROD to DEV. Do you think this might be the problem ?

masaimara
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 200 total points
ID: 10941907
No you dont need to copy the control file over. And you are trying to create a new one
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941913
Can you post up these two things?

In your initTEST.Ora file , what are the values for control_files? are the file locations changed to the DEV SERVER according?

Secondly, show the output of this command:


$ls -alt /u3/oracle/devdata/
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10941923
baonguyen is right about the controlfile. you don't need the old ones.
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10941935
Can you check to ensure the db_block_size  of the two db (prod and test) are the same ?
0
 

Author Comment

by:masaimara
ID: 10941955
No. The controlfiles mentioned in the init.ora file exist. If I remove them then I cannot do the startup.

DEV was 8.1.6 and PROD is 8.1.7. This is why I copied the ORACLE_HOME and all the data files over. However, DEV's (8.1.6) controlfiles are currently used to do the startup...
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10941970
and how you copy the files over the server ?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942014
@masaimara:

uh-huh, this is the problem.

------------>DEV was 8.1.6 and PROD is 8.1.7. This is why I copied the ORACLE_HOME and all the data files over. However, DEV's (8.1.6)



You CANNOT do that. You cannot copy ORACLE_HOME over and expect the Oracle software will work ...


you have to install Oracle software (server) in the dev server machine instead.

The only thing you can copy over is the Database data file provided that the hardware are compatible and OS are the same.



that's why the Oracle software does not recognize the file ....
0
 

Author Comment

by:masaimara
ID: 10942025
seazodiac,

Here's the partial output. DO you want the complete output (around 300 files) ?

$ ls -alt /u3/oracle/devdata/ | more
total 36521816
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log05b.dbf
drwxr-xr-x   2 oradev   dba        20480 Apr 28 07:56 .
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log05a.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log04b.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log04a.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log03b.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log03a.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log02b.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log02a.dbf
-rw-r-----   1 oradev   dba      20979712 Apr 28 07:56 log01b.dbf
-rw-r--r--   1 oradev   dba      20979712 Apr 28 07:56 log01a.dbf
-rwxr-xr-x   1 oradev   dba      8380416 Apr 27 19:18 cntrl01.dbf
-rwxr-xr-x   1 oradev   dba      8380416 Apr 27 19:18 cntrl03.dbf
-rwxr-xr-x   1 oradev   dba      52436992 Apr 24 02:18 xtrx01.dbf
....
....

baonguyen1,
My manager did the copy. I will find out how he did it.

Thanks,
masaimara
0
 

Author Comment

by:masaimara
ID: 10942042
seazodiac,

So how do I go about fixing this issue ?

masaimara
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10942043
seazodiac is right and I want to add more, you can have multi ORACLE_HOME on the same server running different Oracle version (say 8.1.6 and 8.1.7) but you have to install, not just copy
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942060
as I told you in my immediate preceding post:

you have to install the Oracle Server on the dev machine where you want to clone the database
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:masaimara
ID: 10942068
I overlayed DEV's datafiles with PROD's datafiles. What do I do ?
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10942079
"DEV was 8.1.6 and PROD is 8.1.7. This is why I copied the ORACLE_HOME and all the data files over. However, DEV's (8.1.6) controlfiles are currently used to do the startup..."

As previous posts, you have to install 8.1.7 on DEV server fisrt, using differnt ORACLE_HOME than 8.1.6 first, copy the files again and run create control file ... as you did before
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942084
I suppose the dev data is not critical, so you can go without it since you are going to clone the production database to the dev database anyway.
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10942093
YOu said you want to clone the database. If it is the case you can copy the  file again or just you the copied files
0
 

Author Comment

by:masaimara
ID: 10942104
I am confused. Can you tell me how to install 8.1.7 on DEV server ? Can you give me a step by step procedure ?

Thanks,
masaimara
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10942141
You can either using another OS account that belong to dba group or use the existing Oracle account. Then:

1. set the ORACLE_HOME to the new home
2. Set ORACLE_SID to new SID (say test)
3.Set ORACLE_BASE to new path
...

Then now you can start the install from the 8.1.7 CD.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942154
You are confused?

Masaimara, no offense, you are not a DBA, I guess.

I think you should ask your DBA to do this.

cloning a database is straightforward, but it might be a daunting task for novice...


with that being said,

in order to install Oracle8.1.7 server, you just run Oracle universal installer (runInstaller script)...

0
 

Author Comment

by:masaimara
ID: 10942207
No we don't have a dba, and this is why I need your help. Can I run Oracle universal installer from 8.1.7 home now ? This is the copied home from PROD.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942230
masaimara:

Once you popped in the Oracle install disks , and configure a bunch of environment variables (only apply to UNIX install),
then you can run OUI...


for details, don't forget our friends : GOOGLE (just search with keywords install Oracle in UNIX or LINUX or whatever)...

you will see inundated amount of installation notes ....
0
 

Author Comment

by:masaimara
ID: 10942288
I would like to download 8.1.7 from technet. How do I determine if I have to download 64 bit or 32 bit 8.1.7 ?
0
 

Author Comment

by:masaimara
ID: 10942312
And, can I use the copied home to run the OUI ?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942314
what's OS? if you are linux 7.x, 8.x, 9.0 you have to use 32 bits.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942322
No, better not, you have to run it from the OUI on the disk...
0
 

Author Comment

by:masaimara
ID: 10942336
Sun Solaris 5.8
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942342
64 bits
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942343
Good luck on that
0
 

Author Comment

by:masaimara
ID: 10942354
Is there a command to check 64 or 32 bit ?

How do I run the OUI from the disk on SUN ?
0
 

Author Comment

by:masaimara
ID: 10942378
I will be downloading the 817 release from technet onto the SUN OS. I can run the OUI from there right ?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 300 total points
ID: 10942386
masaimara:

like I said, do a search on the google, you will find more detailed documents about how to install oracle in solaris  than given here by posts here by questioning one by one...


so you are not a SYSTEM Admin either?  :-(


to find out the bits;

$/usr/bin/isainfo -b



0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10942431
I will be downloading the 817 release from technet onto the SUN OS. I can run the OUI from there right ?


---->that's correct... simple as that..
0
 

Author Comment

by:masaimara
ID: 10942455
seazodiac,
Thanks for all your posts, and sorry for posting questions one after the other. I am doing the research on google and will install 817. From the previous posts I assume that once I install 817 I should follow the same procedure of creating controlfile.

masaimara
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10943234
yes, that 's correct.
0
 

Author Comment

by:masaimara
ID: 10979084
seazodiac, baonguyen1,

Thank you both for your inputs. However, your comments were not right :

seazodiac: " You CANNOT do that. You cannot copy ORACLE_HOME over and expect the Oracle software will work ... "

baonguyen1: " As previous posts, you have to install 8.1.7 on DEV server fisrt, using differnt ORACLE_HOME than 8.1.6 first, copy the files again and run create control file ... as you did before "

One of the logfiles was put under Datafiles section of the Create Controlfile script - copy and paste error. I could create the controlfile once I got rid of that. So you don't necessarily have to install 8.1.7 server. All said and done, I have learnt something from your inputs and I'm happy about that.

Best Regards,
masaimara
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now