Home » Infrastructure » Windows » copy / past database
copy / past database [message #139473] Wed, 28 September 2005 03:44 Go to next message
oracle_mastic
Messages: 9
Registered: June 2005
Junior Member
Hi I've got one big problem with the database.
I have copy of the database made in for example total commander and I want to create database from this copy on another computer.
I got all files , but I don't know how to restore it.
There was not used any dump just simple copy files.
Can somebody help me ?
Re: copy / past database [message #139890 is a reply to message #139473] Fri, 30 September 2005 00:26 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


Read this ,


Super fast Database Copying

February, 24, 2004
Don Burleson
 
 

This procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter system backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS 
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
 

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS 

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" NORESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 6: Re-names of the data files names that have changed. 

Save as db_create_controlfile.sql.

Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile


STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile


STEP 9: Start the new database

startup nomount;
@db_create_controlfile.sql

 


The above procedure explains in terms of Unix but you can easily change it for windows just by changing the path.
Re: copy / past database [message #142135 is a reply to message #139890] Thu, 13 October 2005 09:57 Go to previous messageGo to next message
bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta
Junior Member
Hi Dear,

I am trying to make clone DB in Win-NT4.0 for Oracle 8.1.6, but at
step 1: alter system backup controlfile to trace ... getting one error as "ORA-02065 illegal option for ALTER SYSTEM"

From command prompt I am not able to login by SVRMGR30 so logged in with c:>sqlplus /nologging

Plz help

Regards
Bonny

[Updated on: Thu, 13 October 2005 09:58]

Report message to a moderator

Re: copy / past database [message #142312 is a reply to message #142135] Fri, 14 October 2005 02:17 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


hi ,
sorry for that . I copied it from an online doc . But that command syntax is wrong . It is

SQL> ALTER SYSTEM BACKUP CONTROLFILE TO TRACE;
ALTER SYSTEM BACKUP CONTROLFILE TO TRACE
             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

Re: copy / past database [message #142588 is a reply to message #142312] Sun, 16 October 2005 08:51 Go to previous messageGo to next message
bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta
Junior Member
Hi Tarun,

i am using this article for cloning DB:
http://www.idevelopment.info/data/Oracle/DBA_tips/Backup_and_Recovery/BandR_2.shtml

if you just have a look into this article, you will be able to help me out. i am at step 3. SID has been created, > restarted >svrmgrl > connect internal. No problem till there.
but when going to run the control file getting error
SVRMGR>@cr_control;
MGR-01507 :unable to open file "cr_control.sql"
i renamed this file as per above article.

plz help.
Bonny
Re: copy / past database [message #142628 is a reply to message #142588] Mon, 17 October 2005 00:15 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Probably a small mistake , you forgot to specify the path for control file.
How Oracle will find out the location of control file if you specify like this

Quote:


SVRMGR> @cr_control




try like this

 SVRMGR>@'YOUR_FILE_PATH/cr_control'
 


Hope it should work for you.





Re: copy / past database [message #142714 is a reply to message #142628] Mon, 17 October 2005 06:49 Go to previous messageGo to next message
bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta
Junior Member
Hi Tarun,

I tried with full path also but same error. just see this
-------------------------------------------------------------
A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH).
-------------------------------------------------------------
is this could be the problem, then how/where to set it.

even i tried as : moved to udump directory at DOS prompt and then connect to oracle with svrmgrl, but no use.

why you mentioned the / (fwd slash), i am using oracle 8.1.6 on Win-NT 4.

Plz help
Bonny
Re: copy / past database [message #142717 is a reply to message #142714] Mon, 17 October 2005 06:58 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Oops , extremely sorry for the forward slash .

You are using Windows so Backward slash " \ " will work.

 SVRMGR>@'YOUR_FILE_PATH\cr_control'
 


--> A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH).
-------------------------------------------------------------
is this could be the problem, then how/where to set it.

This is not a problem if you specify full path .

now try again with the Backward slash " \ ".
Re: copy / past database [message #143655 is a reply to message #142717] Fri, 21 October 2005 06:38 Go to previous messageGo to next message
bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta
Junior Member
Hey Tarun,

Wow, i don't it. thanks for your quick helps, thanks a lot.
take care,
Bonny
Re: copy / past database [message #143772 is a reply to message #142717] Sat, 22 October 2005 05:15 Go to previous messageGo to next message
bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta
Junior Member
Dear Tarun,

how can keep update my database means daily at the end-of-day i wants to update my clonned database with production database.

regards,
Bonny
Re: copy / past database [message #143883 is a reply to message #143772] Sun, 23 October 2005 23:51 Go to previous message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Read

Standby Databases

from the docs.
Previous Topic: "Batch" process between VB and Oracle
Next Topic: Oracle Error - ORA-01401: inserted value too large for column -- while processing OCI function OEXEC
Goto Forum:
  


Current Time: Thu Mar 28 04:48:44 CDT 2024