Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2530
  • Last Modified:

Error when creating a db using backup controlfile - urgent

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
masaimara
Asked:
masaimara
  • 19
  • 19
  • 7
  • +1
2 Solutions
 
oleggoldCommented:
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
 
seazodiacCommented:
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
 
masaimaraAuthor Commented:
I'm sorry I did not mention that. I got the errors after I corrected the path to '/u3/oracle/devdata/log01a.dbf'.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
seazodiacCommented:
@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
 
masaimaraAuthor Commented:
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
 
masaimaraAuthor Commented:
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
 
seazodiacCommented:
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
 
seazodiacCommented:
also a correction to oleggold's note:

YOu cannot have a name for controlfile in that clause.
0
 
masaimaraAuthor Commented:
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
 
masaimaraAuthor Commented:
Also, I set ORACLE_HOME, ORACLE_SID and the PATH before startup nomount.
0
 
seazodiacCommented:
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
 
masaimaraAuthor Commented:
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
 
baonguyen1Commented:
No you dont need to copy the control file over. And you are trying to create a new one
0
 
seazodiacCommented:
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
 
seazodiacCommented:
baonguyen is right about the controlfile. you don't need the old ones.
0
 
baonguyen1Commented:
Can you check to ensure the db_block_size  of the two db (prod and test) are the same ?
0
 
masaimaraAuthor Commented:
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
 
baonguyen1Commented:
and how you copy the files over the server ?
0
 
seazodiacCommented:
@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
 
masaimaraAuthor Commented:
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
 
masaimaraAuthor Commented:
seazodiac,

So how do I go about fixing this issue ?

masaimara
0
 
baonguyen1Commented:
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
 
seazodiacCommented:
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
 
masaimaraAuthor Commented:
I overlayed DEV's datafiles with PROD's datafiles. What do I do ?
0
 
baonguyen1Commented:
"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
 
seazodiacCommented:
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
 
baonguyen1Commented:
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
 
masaimaraAuthor Commented:
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
 
baonguyen1Commented:
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
 
seazodiacCommented:
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
 
masaimaraAuthor Commented:
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
 
seazodiacCommented:
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
 
masaimaraAuthor Commented:
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
 
masaimaraAuthor Commented:
And, can I use the copied home to run the OUI ?
0
 
seazodiacCommented:
what's OS? if you are linux 7.x, 8.x, 9.0 you have to use 32 bits.
0
 
seazodiacCommented:
No, better not, you have to run it from the OUI on the disk...
0
 
masaimaraAuthor Commented:
Sun Solaris 5.8
0
 
seazodiacCommented:
64 bits
0
 
seazodiacCommented:
Good luck on that
0
 
masaimaraAuthor Commented:
Is there a command to check 64 or 32 bit ?

How do I run the OUI from the disk on SUN ?
0
 
masaimaraAuthor Commented:
I will be downloading the 817 release from technet onto the SUN OS. I can run the OUI from there right ?
0
 
seazodiacCommented:
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
 
seazodiacCommented:
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
 
masaimaraAuthor Commented:
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
 
seazodiacCommented:
yes, that 's correct.
0
 
masaimaraAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 19
  • 19
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now