Solved

Create a new control file

Posted on 2007-03-21
21
1,985 Views
Last Modified: 2008-01-09
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
Comment
Question by:jung1975
21 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18764736
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
 

Author Comment

by:jung1975
ID: 18764851
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
 
LVL 8

Assisted Solution

by:gvsbnarayana
gvsbnarayana earned 50 total points
ID: 18764896
Hi,
  You may try this:
CREATE CONTROLFILE set DATABASE  "RATEST" RESETLOGS  ARCHIVELOG

HTH
Regards,
Badri.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18764898
>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
 

Author Comment

by:jung1975
ID: 18765073
thanks angellll, so how can i fix this?
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 50 total points
ID: 18765137
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
 

Author Comment

by:jung1975
ID: 18765944
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766004
>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
 

Author Comment

by:jung1975
ID: 18766024
thanks..
What kind of information is included in  system.dbf ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766112
it's all the information of the database structure (tables, users, procedures etc).
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:jung1975
ID: 18766122
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766136
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
 

Author Comment

by:jung1975
ID: 18766176
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
 

Author Comment

by:jung1975
ID: 18766192
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766207
it does matter, but assuming its the correct file, just rename it to the requested name.
0
 

Author Comment

by:jung1975
ID: 18766306
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
 

Author Comment

by:jung1975
ID: 18766456
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18766524
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
 

Author Comment

by:jung1975
ID: 18766869
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
 

Author Comment

by:jung1975
ID: 18768353
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 18769331
>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

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

705 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

16 Experts available now in Live!

Get 1:1 Help Now