Operating System - HP-UX
1748235 Members
3403 Online
108759 Solutions
New Discussion юеВ

restoration of DB prod to test

 
SOLVED
Go to solution
Allan Palma Faylona
Contributor

restoration of DB prod to test


hello there

SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=/opt/db1/initIMXtest.ora;
ORACLE instance started.
Total System Global Area 26722464 bytes
Fixed Size 73888 bytes
Variable Size 26148864 bytes
Database Buffers 409600 bytes
Redo Buffers 90112 bytes
SVRMGR> @/opt/db1/ccfbackIMXtest.sql;
CREATE CONTROLFILE REUSE set DATABASE "IMXtest" RESETLOGS NOARCHIVELOG
*
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name IMX in file header does not match given name of IMXDEV
ORA-01110: data file 8: '/opt/IMXtest/dat01.dbf'


same error i got.
4 REPLIES 4
twang
Honored Contributor
Solution

Re: restoration of DB prod to test

CREATE CONTROLFILE set DATABASE "IMXtest" NORESETLOGS NOARCHIVELOG

After running the above sql, then issue:
SQL> alter database open resetlogs;
Eric Antunes
Honored Contributor

Re: restoration of DB prod to test

Hi Allan,

I'm not sure to understand your issue but it seems you want to clone your Production to a Test database. If so see the following thread: http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=595995
Each and every day is a good day to learn.
Sanjay Kumar Suri
Honored Contributor

Re: restoration of DB prod to test

Extract from the link below:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/control.htm#630

New Control Files
You can create a new control file for a database using the CREATE CONTROLFILE statement. This is necessary in the following situations:

All control files for the database have been permanently damaged and you do not have a control file backup.

You want to change one of the permanent database settings originally specified in the CREATE DATABASE statement, including the database's name, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

For example, you might need to change a database's name if it conflicts with another database's name in a distributed environment. Or, as another example, you might need to change one of the previously mentioned parameters if the original setting is too low.


The following statement creates a new control file for the PROD database (formerly a database that used a different database name):

CREATE CONTROLFILE
SET DATABASE prod
LOGFILE GROUP 1 ('logfile1A', 'logfile1B') SIZE 50K,
GROUP 2 ('logfile2A', 'logfile2B') SIZE 50K
NORESETLOGS
DATAFILE 'datafile1' SIZE 3M, 'datafile2' SIZE 5M
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 6
ARCHIVELOG;

WARNING: The CREATE CONTROLFILE statement can potentially damage specified datafiles and online redo log files; omitting a filename can cause loss of the data in that file, or loss of access to the entire database. Employ caution when using this statement and be sure to follow the steps in the next section.

sks

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Indira Aramandla
Honored Contributor

Re: restoration of DB prod to test

Hi,

When you say restoring DB from prod to DEV,
I presume you wanted to create the dev database with a different name. So you must had copied the data files and the redo logs and then trying to create the control files. This problem with the above is you do not specify "REUSE"

Noe there are two ways. If you wanted to have the the DB name same then as it was on prod, then all you need to do is :

Shutdown the prod database and copy the files (datafiles, control files and the redo logs) to the dev server/location. And make sure you edit the init ora file to chekc the location and names of the control files in the dev environment. Check the init.ora file to makesure the controlfile names and locationsmatch that of dev environment. And then startup the database on the dev server. Or if you are restoring from a cold backup from the tape then leave prod database as it is and restore the datafiles, redo logs and the control files and startup the database.

If you want the database copied with a different name then copy the datafiles and redo logs and recreate the control files as:

Eg:-

cp /path/system_prod.dbf /path/system_dev.dbf
cp /path/tools_prod.dbf /path/tools_dev.dbf
.
.
.
.
Then recreate the control files as
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DEV_NAME" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 1
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
) SIZE 25M,
GROUP 2 (

) SIZE 25M,
DATAFILE
'file_dev_names,
.
.
.
.
ALTER DATABASE OPEN RESETLOGS;

this will open the database with the new names.

I Hope this helps.
Never give up, Keep Trying