• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2194
  • Last Modified:

Create a new control file

Hello! I am trying to copy a hotbackup from the production DB and restore it to test server.
I've moved over all of backup fiels(.dbf,archlogs,etc..)
I was trying to create a new control file by issuing the below command


STARTUP NOMOUNT
 CREATE CONTROLFILE DATABASE  "RATEST" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 16
    MAXLOGHISTORY 1815
LOGFILE
  GROUP 1 (
    'E:\RATEST\RATEST\LOG1A.DBF',
    'E:\RATEST\RATEST\LOG1B.DBF'
  ) SIZE 10M,
  GROUP 2 (
    'E:\RATEST\RATEST\LOG2A.DBF',
    'E:\RATEST\RATEST\LOG2B.DBF'
  ) SIZE 10M,
  GROUP 3 (
    'E:\RATEST\RATEST\LOG3A.DBF',
    'E:\RATEST\RATEST\LOG3B.DBF'
  ) SIZE 10M,
  GROUP 4 (
    'E:\RATEST\RATEST\LOG4A.DBF',
    'E:\RATEST\RATEST\LOG4B.DBF'
  ) SIZE 10M,
  GROUP 5 (
    'E:\RATEST\RATEST\LOG5A.DBF',
    'E:\RATEST\RATEST\LOG5B.DBF'
  ) SIZE 10M,
  GROUP 6 (
    'E:\RATEST\RATEST\LOG6A.DBF',
    'E:\RATEST\RATEST\LOG6B.DBF'
  ) SIZE 10M,
  GROUP 7 (
    'E:\RATEST\RATEST\LOG7A.DBF',
    'E:\RATEST\RATEST\LOG7B.DBF'
  ) SIZE 10M,
  GROUP 8 (
    'E:\RATEST\RATEST\LOG8A.DBF',
    'E:\RATEST\RATEST\LOG8B.DBF'
  ) SIZE 10M
-- STANDBY LOGFILE

DATAFILE
  'E:\RATEST\RATEST\SYST.DBF',
  'E:\RATEST\RATEST\UNDOTBS01.DBF',
  'E:\RATEST\RATEST\DBA1.DBF',
  'E:\RATEST\RATEST\GTABLES1.DBF',
  'E:\RATEST\RATEST\RA1.DBF',
  'E:\RATEST\RATEST\RA2.DBF',
  'E:\RATEST\RATEST\RA3.DBF',
  'E:\RATEST\RATEST\RA4.DBF',
  'E:\RATEST\RATEST\RA5.DBF',
  'E:\RATEST\RATEST\DBA2.DBF',
  'E:\RATEST\RATEST\RA6.DBF',
  'E:\RATEST\RATEST\RA7.DBF'
CHARACTER SET US7ASCII
;


But i am getting the below erros.. What am i doing wrong here?

SQL> DATAFILE
SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SQL>   'E:\RATEST\RATEST\SYST.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\UNDOTBS01.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\DBA1.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>   'E:\RATEST\RATEST\GTABLES1.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\RA1.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\RA2.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\RA3.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>   'E:\RATEST\RATEST\RA4.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\RA5.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\DBA2.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL>   'E:\RATEST\RATEST\RA6.DBF',
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>   'E:\RATEST\RATEST\RA7.DBF'
SP2-0734: unknown command beginning "'E:\RATEST..." - rest of line ignored.
SQL> CHARACTER SET US7ASCII
SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.
SQL> ;
0
jung1975
Asked:
jung1975
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
remove the empty lines between  the following 2 lines.
-- STANDBY LOGFILE

DATAFILE

sql*plus will actually ignore the preceeding, and thinks that with DATAFILE you start a new command.
0
 
jung1975Author Commented:
thanks..
now i am getting
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files - wrong database id
ORA-01110: data file 3: 'E:\RATEST\RATEST\DBA1.DBF'


Basically, I am trying to resotre the backupfiles from RA  to RATEST ..
I guess the system file doesn't recognize tablespaces and data files.. How can I fix this problem?
0
 
gvsbnarayanaCommented:
Hi,
  You may try this:
CREATE CONTROLFILE set DATABASE  "RATEST" RESETLOGS  ARCHIVELOG

HTH
Regards,
Badri.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I guess the system file doesn't recognize tablespaces and data files..
this means that not all the files you specify  (ie at least   'E:\RATEST\RATEST\DBA1.DBF', ) were part of the same database previously than  'E:\RATEST\RATEST\SYST.DBF' and   'E:\RATEST\RATEST\UNDOTBS01.DBF'...
you must have mixed some files...

0
 
jung1975Author Commented:
thanks angellll, so how can i fix this?
0
 
schwertnerCommented:
here is something wrong with the file names.
'SYST.DBF' possibly should be 'SYSTEM.DBF'
and so on.
Check the names of all files and correct the statement.
0
 
jung1975Author Commented:
do i have to copy over the system data file as well?

One in the production(RA) is called syst.dbf

and One that I am trying to restore it to(RATEST) is called system01.dbf..



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>do i have to copy over the system data file as well?
yes, and I would even say: of course!

>and One that I am trying to restore it to(RATEST) is called system01.dbf.
that should not be a problem.
0
 
jung1975Author Commented:
thanks..
What kind of information is included in  system.dbf ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's all the information of the database structure (tables, users, procedures etc).
0
 
jung1975Author Commented:
ok... I was be able to creat a new control file..now I am trying to recover databse ..but i am getting the below error.. I have a  F:\ARCH\ARC33095.001, so i am not sure why iam getting this error??

SQL> recover database using backup controlfile
ORA-00279: change 100428801 generated at 03/08/2007 01:30:15 needed for thread
1
ORA-00289: suggestion : F:\ARCH\ARC33095.001
ORA-00280: change 100428801 for thread 1 is in sequence #33095


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log 'F:\ARCH\ARC33095.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
was the source database closed when you copied the files?
if note, you will need the suggested archivelog file in the folder it looks for it...
0
 
jung1975Author Commented:
I did user hotbackup..
alter tablespace begin backup..
copy  data file
alter tablespace end backup


and I moved the copied data files and archive log files to the test server..

I have the suggested archived log file in F:Arch folder in the test server ..
0
 
jung1975Author Commented:
actually.. the file i have is ARC33095.arc not 001.. does this matter? if so, how can i make Oracle look for the archived files with .arc extension?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it does matter, but assuming its the correct file, just rename it to the requested name.
0
 
jung1975Author Commented:
thanks.. but i am still getting the error .. there are 100 of archived log files...

SQL> recover database using backup controlfile
ORA-00279: change 100428801 generated at 03/08/2007 01:30:15 needed for thread
1
ORA-00289: suggestion : F:\ARCH\ARC33095.001
ORA-00280: change 100428801 for thread 1 is in sequence #33095


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
0
 
jung1975Author Commented:
ok.. i've fixed the 001 issue..
when I did recover database until cancel using backup controlfile


SQL> recover database until cancel using backup controlfile
ORA-00279: change 100444701 generated at 03/08/2007 09:05:21
1
ORA-00289: suggestion : F:\ARCH\ARC33101.ARC
ORA-00280: change 100444701 for thread 1 is in sequence #331


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 100446259 generated at 03/08/2007 09:21:09
1
ORA-00289: suggestion : F:\ARCH\ARC33102.ARC
ORA-00280: change 100446259 for thread 1 is in sequence #331
ORA-00278: log file 'F:\ARCH\ARC33101.ARC' no longer needed


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

what is this mean? does this mean the archived log files is being applied?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, that is what you are asking for:
recover database using backup controlfile

if you do not want to recover all the changes, you have to specify that:
recover database using backup controlfile until cancel;

and specify CANCEL immediately when it returns with the first filename.
then, open the database with RESETLOGS to open it without any further need to recover.

if you actually want to recover all the changes, specify AUTO once, and simply ensure that all the archived files it requests are present in the folder it is looking for.
0
 
jung1975Author Commented:
thaks.. After I recover database using the back up control file, I was trying to open the databse but got the below error..  I don't have F:\ARCH\ARC33471.ARC file..   how can i fix this problem..



SQL> recover database until cancel using backup controlfile
ORA-00279: change 101950832 generated at 03/20/2007 01:57:31 needed for thread
1
ORA-00289: suggestion : F:\ARCH\ARC33471.ARC
ORA-00280: change 101950832 for thread 1 is in sequence #33471


ORA-00308: cannot open archived log 'F:\ARCH\ARC33471.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\RATEST\RATEST\SYST.DBF'
0
 
jung1975Author Commented:
does system.dbf file have archived log files information?

why is it looking for the archived log files which is not copy over to the test server?

how can i do the media recovery for system.dbf file?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>does system.dbf file have archived log files information?
not directly. it's the control file that contains that information.
the data files itself only contain the information about when the last change was in th efile itself...

>why is it looking for the archived log files which is not copy over to the test server?
because that's the way oracle works.

>how can i do the media recovery for system.dbf file?
ensure you get the archive log you are asked for.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now