Wash my mouth out - Oracle!
-
I've just been asked about Oracle backup/copy of a database. If I wanted to create a new copy of a database in SQL Server, I'd back it up then restore it to a new location with a new name.
Does the same apply to Oracle? Is backing up Oracle databases as straight forward as SQL? Does it take longer that SQL Server?
The database referred to is a heritage database which will not be with us in a few years time so is not being developed.
Madame Artois
-
You can look for the option of import/export kind of backup in ORACLE is you are a novice there. They are easy to implement where as other kind involve few overheads.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I'm not up to date, but a couple of versions back it was a complicated business creating a database clone in Oracle. 9i is meant to have improved things, but I wouldn't bet on it being as easy as SQL Server.
-
This version is Oracle 7 I think; I did say it was a heritage database!
Madame Artois
-
I am the main SQL Server DBA here and dabble a little in our Oracle dbs. It is NOT as easy at all as SQL Server. You have to have an RMAN repository to create a db backup and then clone the other db from that backup... if U are on 9i that is. If you have both dbs created and just want to move the schema then an export/import is the quickest way.
-
The easiest wat is to create a database using the enterprise manager available with 9i and then import/export the data. If you want to learn more about ORACLE then go for a cold backup an then restore but it involves a little omplexity i have exp of ORACLE and have playes with these it is not as simple as SQL.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I have been an Oracle DBA on Unix for the last 8 years. In the past 6 months I have started a new job as a SQL Server DBA and Oracle DBA on Windows. Depending on the database size, you can perform an export of the legacy system. I found the below batch script for a windows environment to perform a full database export. You can modify it for your use.
Exports can take a long time for very large databases. If you decide to do a cold backup instead, the best way is to bring all of the Oracle Services down, then copy all of the data files and the controll files. Data files usually end in .dbf and controlfiles usually end in .ctl. To make sure you get all of the files, you can look in Oracle Enterprise Manager under the storage tab.
I am assuming you are performing this on a windows server. If you are using UNIX, I can supply that information as well.
Good Luck.
@echo off
rem -----------------------------------------------------------------------
rem Filename: DBExp.bat
rem Purpose: Export contents of an Oracle database
rem Date: 05-Mar-2002
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------
rem -- Set Database SID --
set ORACLE_SID=sgnt
rem -- Keep 3 versions of the export log file --
if exist %ORACLE_SID%_2.log copy %ORACLE_SID%.log %ORACLE_SID%_3.log >NUL:
if exist %ORACLE_SID%_1.log copy %ORACLE_SID%.log %ORACLE_SID%_2.log >NUL:
if exist %ORACLE_SID%.log copy %ORACLE_SID%.log %ORACLE_SID%_1.log >NUL:
rem -- Keep 3 versions of the export dump file --
if exist %ORACLE_SID%_2.dmp copy %ORACLE_SID%.log %ORACLE_SID%_3.dmp >NUL:
if exist %ORACLE_SID%_1.dmp copy %ORACLE_SID%.log %ORACLE_SID%_2.dmp >NUL:
if exist %ORACLE_SID%.dmp copy %ORACLE_SID%.log %ORACLE_SID%_1.dmp >NUL:
rem -- Do the export --
exp 'sys/orcl as sysdba' full=yes file=%ORACLE_SID%.dmp log=%ORACLE_SID%.log
rem -- Add timestamp to top of export log file --
date /T >timestamp.tmp
copy timestamp.tmp + %ORACLE_SID%.log %ORACLE_SID%.tmp
copy %ORACLE_SID%.tmp %ORACLE_SID%.log
rem -- Cleanup temporary files --
del %ORACLE_SID%.tmp
del timestamp.tmp
-
Just as in SQL Server, where I detach a database and then quickly recover or save it via its MDF and LDF files, so in Oracle I do something analogous with its equivalent of data files. Thus installing 9i with a standard instance of ORCL would get you all the data in c:\oracle\oradata\orcl with files such as .CTL, .LOG and .DBF. There are some other files that you need to capture:
c:\oracle\ora92\database\spfileorcl.ora and pwdorcl.ora
c:\oracle\admin\orcl\pfile\init.ora.*
You then simply stop the Oracle service and copy/zip its datafiles + special files. Doing this with some simple scripts lets you manage this fairly easily thus taking offline and bringing online a database very quickly.
Of course, this approach doesn't work very well if you are into creating lots of Oracle instances and lots of table spaces and lots of table owners simlutaneously. But if you keep it simple, you reap enormous benefits from dealing with Oracle's data files in this manner.
-
Everyone here is describing how to copy an oracle INSTANCE. Which would be similar to copying a SQL Server installation/INSTANCE. An Oracle SCHEMA is much more like a SQL Server database.
Use exp/imp to copy a schema. However, you have to create the schema before you try to import into it. Since a schema in Oracle is just a user that can create objects, copy a user, and change it's name.
As for storage, that is handled in oracle with tablespaces. Think of them as filegroups, but unlike SQL Server, you create them BEFORE you create the "Database" (or in oracle import the schema).
FYI, I was an Oracle DBA for over 10 years, and have been using SQL Server/Sybase for about 2
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply