- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- How to import an ORACLE DB in another instance?
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 03:54 AM
тАО10-01-2003 03:54 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:00 AM
тАО10-01-2003 04:00 AM
Re: How to import an ORACLE DB in another instance?
JL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:02 AM
тАО10-01-2003 04:02 AM
Re: How to import an ORACLE DB in another 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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:16 AM
тАО10-01-2003 04:16 AM
Solutionrun 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:18 AM
тАО10-01-2003 04:18 AM
Re: How to import an ORACLE DB in another instance?
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:22 AM
тАО10-01-2003 04:22 AM
Re: How to import an ORACLE DB in another instance?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 07:20 AM
тАО10-01-2003 07:20 AM
Re: How to import an ORACLE DB in another instance?
*
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2003 02:06 AM
тАО10-02-2003 02:06 AM
Re: How to import an ORACLE DB in another instance?
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 ...!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2003 02:17 AM
тАО10-02-2003 02:17 AM
Re: How to import an ORACLE DB in another instance?
did u shutdown the database cleanly?
did u re-create control file with resetlogs option?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-02-2003 02:21 AM
тАО10-02-2003 02:21 AM
Re: How to import an ORACLE DB in another instance?
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.