How to import an ORACLE DB in another instance?

[Enrico Venturi]

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

[Jean-Luc Oudart]

Do you mean renaming the instance ?

JL

[Enrico Venturi]

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 · Accepted solution]

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

[Hari Kumar]

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,

[Jean-Luc Oudart]

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

[Yogeeraj_1]

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

[Enrico Venturi]

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]

why resetlogs?

did u shutdown the database cleanly?

did u re-create control file with resetlogs option?

[Enrico Venturi]

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.