Link to home
Start Free TrialLog in
Avatar of masaimara
masaimara

asked on

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
Avatar of oleggold
oleggold
Flag of United States of America image
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
;
Avatar of seazodiac
seazodiac
Flag of United States of America image
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.

Avatar of masaimara
masaimara

ASKER

I'm sorry I did not mention that. I got the errors after I corrected the path to '/u3/oracle/devdata/log01a.dbf'.
Avatar of seazodiac
seazodiac
Flag of United States of America image
@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..

Avatar of masaimara
masaimara

ASKER

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
Avatar of masaimara
masaimara

ASKER

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
Avatar of seazodiac
seazodiac
Flag of United States of America image
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....
Avatar of seazodiac
seazodiac
Flag of United States of America image
also a correction to oleggold's note:

YOu cannot have a name for controlfile in that clause.
Avatar of masaimara
masaimara

ASKER

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.
Avatar of masaimara
masaimara

ASKER

Also, I set ORACLE_HOME, ORACLE_SID and the PATH before startup nomount.
Avatar of seazodiac
seazodiac
Flag of United States of America image
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?
Avatar of masaimara
masaimara

ASKER

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
SOLUTION
Avatar of baonguyen1
baonguyen1
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of seazodiac
seazodiac
Flag of United States of America image
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/
Avatar of seazodiac
seazodiac
Flag of United States of America image
baonguyen is right about the controlfile. you don't need the old ones.
Avatar of baonguyen1
baonguyen1
Can you check to ensure the db_block_size  of the two db (prod and test) are the same ?
Avatar of masaimara
masaimara

ASKER

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...
Avatar of baonguyen1
baonguyen1
and how you copy the files over the server ?
Avatar of seazodiac
seazodiac
Flag of United States of America image
@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 ....
Avatar of masaimara
masaimara

ASKER

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
Avatar of masaimara
masaimara

ASKER

seazodiac,

So how do I go about fixing this issue ?

masaimara
Avatar of baonguyen1
baonguyen1
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
Avatar of seazodiac
seazodiac
Flag of United States of America image
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
Avatar of masaimara
masaimara

ASKER

I overlayed DEV's datafiles with PROD's datafiles. What do I do ?
Avatar of baonguyen1
baonguyen1
"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
Avatar of seazodiac
seazodiac
Flag of United States of America image
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.
Avatar of baonguyen1
baonguyen1
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
Avatar of masaimara
masaimara

ASKER

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
Avatar of baonguyen1
baonguyen1
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.
Avatar of seazodiac
seazodiac
Flag of United States of America image
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)...

Avatar of masaimara
masaimara

ASKER

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.
Avatar of seazodiac
seazodiac
Flag of United States of America image
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 ....
Avatar of masaimara
masaimara

ASKER

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 ?
Avatar of masaimara
masaimara

ASKER

And, can I use the copied home to run the OUI ?
Avatar of seazodiac
seazodiac
Flag of United States of America image
what's OS? if you are linux 7.x, 8.x, 9.0 you have to use 32 bits.
Avatar of seazodiac
seazodiac
Flag of United States of America image
No, better not, you have to run it from the OUI on the disk...
Avatar of masaimara
masaimara

ASKER

Sun Solaris 5.8
Avatar of seazodiac
seazodiac
Flag of United States of America image
64 bits
Avatar of seazodiac
seazodiac
Flag of United States of America image
Good luck on that
Avatar of masaimara
masaimara

ASKER

Is there a command to check 64 or 32 bit ?

How do I run the OUI from the disk on SUN ?
Avatar of masaimara
masaimara

ASKER

I will be downloading the 817 release from technet onto the SUN OS. I can run the OUI from there right ?
ASKER CERTIFIED SOLUTION
Avatar of seazodiac
seazodiac
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of seazodiac
seazodiac
Flag of United States of America image
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..
Avatar of masaimara
masaimara

ASKER

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
Avatar of seazodiac
seazodiac
Flag of United States of America image
yes, that 's correct.
Avatar of masaimara
masaimara

ASKER

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