Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to import an ORACLE DB in another instance?

SOLVED
Go to solution
Enrico Venturi
Super Advisor

How to import an ORACLE DB in another instance?

Hello collegues,
I've a question:
I want to export all the data files and control files of an ORACLE instance, namely dbsnml,
then I want to import them in another instance,
namely dbsnml1 ...
the import gives an error because the control file that I want to import control01.ctl doesn't contain the proper identifier, i.e. dbsnml1
How can adjust them?

thanks a lot
Enrico
12 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: How to import an ORACLE DB in another instance?

Do you mean renaming the instance ?

JL
fiat lux
Enrico Venturi
Super Advisor

Re: How to import an ORACLE DB in another instance?

Yes, I mean to rename the imported instance ...
i.e. to change the instance name inside the data and control files (where needed)

Actually I get an error during the database startup "ORA-01103: database name 'DBSNML' in controlfile is not 'DBSNML1'

Therefore I should change the controlfile content before importing it .
Jean-Luc Oudart
Honored Contributor
Solution

Re: How to import an ORACLE DB in another instance?

1) in your source environment :
run the command :
alter database backup controlfile to trace

=> this shoul give you the skeleton of your clonng script
cf. attachment

2) shutdown database
3) copy file accross to target system (or backup/restore)
4) remove existing control files !
5) adapt the cloning script for datafile location (cf.attachment)
6) svrmgrl
connect / as sysdba
@cloneDB.sql
exit

Rgds,
Jean-Luc
fiat lux
Hari Kumar
Trusted Contributor

Re: How to import an ORACLE DB in another instance?

Login to the old database as a dba user, and create a control file trace using the command alter database backup controlfile to trace;.
Shutdown the old instance. Do not shutdown abort or immediate as these will require instance recovery.
Take a cold backup of the control files, datafiles, redo logs - if you want but not really required, initSID.ora file.
Create an environment on the new server to receive the various dumps files etc. Create users, directories, edit oratab, tnsnames.ora and listener.ora files as required.
Copy (via tape, ftp etc) the dump files, control files, redo, initSID.ora and the control file trace over to the new environment.
Edit the control file trace to :
Remove all the comments from the top and bottom of the script.
Remove the startup nomount and recover database commands from the script.
Change create controlfile reuse database "old_name" noresetlogs ... to create controlfile reuse set database "new_name" resetlogs ....
Change the paths and file names for the datafiles and logfiles to match the new locations.
Save the file with a meaningful name - controlfile.sql for example.
Edit the initSID.ora file to :
Change the db_name parameter to match the new name given in the controlfile.sql script created above.
Change the paths to the control file(s).
Change the paths to user_dump_dest, background_dump_dest and core_dump_dest locations as appropriate, also log_archive_dest if the database is in archive log mode.
On the new server, set $ORACLE_HOME and $ORACLE_SID as required.
If you changed the filenames in the control file script and/or the control file names in the initSID.ora file, now is a good time to rename the actual datafiles and/or control file to match.
Run svrmgrl and connect internal or / as sysdba according to the version of Oracle in use.
Startup nomount the instance and run the controlfile.sql script created above.
Alter database open resetlogs; will then open the database. If you didn't copy the redo files over, or if you changed their names in the control file script, then new files will be created for you.

Thanks,
Information is Wealth ; Knowledge is Power
Jean-Luc Oudart
Honored Contributor

Re: How to import an ORACLE DB in another instance?

Don't forget to change you ORACLE_SID before you run the cloing script !

the trace file will be located in your udump directory.
remove the 1st few lines (down to STARTUP)
change REUSE DATABASE to SET DATABASE. That's where you give the new database name.

change file location (DATAFILE)

comment out RECOVER DATABASE
change ALTER DATABASE OPEN to ALTER DATABASE OPEN RESETLOGS;

Rgds,
JL
PS : if the target database is to be on same server, you have to shutdown source db before you run the cloning script

fiat lux
Yogeeraj_1
Honored Contributor

Re: How to import an ORACLE DB in another instance?

hi,
*
You can also try RMAN and duplicate the database.
*
much much easier.
*
i can post the procedure, if you wish.
*
let me know.
*
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: How to import an ORACLE DB in another instance?

When I run "ALTER DATABASE OPEN RESETLOGS;"
the error code is:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/usr/Systems/1354RM_1/databases/dbsnml/data/system01.dbf'


I'm not abel to recover anything ...!!!
Steve Bear_1
Frequent Advisor

Re: How to import an ORACLE DB in another instance?

why resetlogs?

did u shutdown the database cleanly?

did u re-create control file with resetlogs option?
Enrico Venturi
Super Advisor

Re: How to import an ORACLE DB in another instance?

I did both ...
see the commands to create the control file and to open the database


CREATE CONTROLFILE REUSE SET DATABASE "DBSNML1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 899
LOGFILE
GROUP 1 (
'/usr/snml/databases/dbsnml/data/redo01.log',
'/mirror/snml/mirror/redo01.log'
) SIZE 500K,
GROUP 2 (
'/usr/snml/databases/dbsnml/data/redo02.log',
'/mirror/snml/mirror/redo02.log'
) SIZE 500K,
GROUP 3 (
'/usr/snml/databases/dbsnml/data/redo03.log',
'/mirror/snml/mirror/redo03.log'
) SIZE 500K
DATAFILE
'/usr/snml/databases/dbsnml/data/system01.dbf',
'/usr/snml/databases/dbsnml/data/rbs01.dbf',
'/usr/snml/databases/dbsnml/data/temp01.dbf',
'/usr/snml/databases/dbsnml/data/data01.dbf',
'/usr/snml/databases/dbsnml/data/index_t01.dbf',
'/usr/snml/databases/dbsnml/data/pm_hist_data01.dbf',
'/usr/snml/databases/dbsnml/data/pm_hist_index01.dbf'
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
#RECOVER DATABASE
# All logs need archiving and a log switch is needed.
#ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN RESETLOGS;
# No tempfile entries found to add.
Hari Kumar
Trusted Contributor

Re: How to import an ORACLE DB in another instance?

Try to mount the database,
Try now the Backup Control file to trace;
Shutdown,
Now restore your backup
Take the template from trace file and follow the steps now.

Thanks,
Information is Wealth ; Knowledge is Power
Jean-Luc Oudart
Honored Contributor

Re: How to import an ORACLE DB in another instance?

Enrico

1) When you take the backup ensuire the database was properly shut down

2) CREATE CONTROLFILE REUSE SET DATABASE "DBSNML1" RESETLOGS ARCHIVELOG
should read
CREATE CONTROLFILE SET DATABASE "DBSNML1" RESETLOGS NOARCHIVELOG
If yuo need archiving, I suggest yuo set up archiving after the db has been cloned sucessfully.
remove the control files before you create them.

Rgds,
Jean-Luc
fiat lux
Hari Kumar
Trusted Contributor

Re: How to import an ORACLE DB in another instance?

If you are not trying on a production machine i would suggest this ---

1.Think u have already created DBSNML1, which is not properly recovered.
2.Now shutdown completely DBSNML1
3.Mount this DBSNML1(this is with ONLY your new control file created with set DBSNML1 e.t.c)
4.Now issue backup control file to trace..
5.Shutdown immediateley (DBSNML1)
6.Now restore your Datafiles,redologs at DBSNML1 as the path u find in tracefile of DBSNML1 which we generated earlier
7. Now follow as the Template we got in tracefile for create control file of DBSNML1

This i have tested here, Hope u were clear

Thanks,
Information is Wealth ; Knowledge is Power