Copying datafiles between different operating systems
Tomas A. Torres Ramirez, January 05, 2003 - 5:06 pm UTC
Thank you very much. Your answer is to us very useful, specially in this phase of the project. Your words about Oracle 9iR2 are very interesting, I'll ask you more about the support of copy between different operating systems provided by this Oracle release as soon as possible.
"endianess" -- what is it ?
Robert, January 05, 2003 - 6:04 pm UTC
>>Because of differences in the way the operating systems might represent the endianess of words.
What's "endianess", Tom ? just can't guess, didn't mean to pick...
January 05, 2003 - 6:33 pm UTC
it refers to how the machines physically dereference N bytes of memory. On intel for example -- a 4 byte long is stored "backwards" byte by byte from the way it is stored on SPARC. So, a 4 byte long on intel linux would be reversed from a 4 byte long on SPARC.
High endian
Low endian
just general "computer terms"
More about "endianess". Big/Little-Endian
Tomas A. Torres Ramirez, January 05, 2003 - 6:55 pm UTC
unlikely file compatability between OSs
Jim, January 05, 2003 - 7:41 pm UTC
It is unlikely that Oracle will provide file compatibility betweeen different operating systems. The QA testing alone would be huge and difficult. In addition to the endian problem different OS have different byte alignment. So it is quite likely that for one particular OS the byte alignment is on 4 byte bboundries, another on 8 byte boundies, and another on 2 byte boundries, and still another with no alignment(or an alignment of 0). So the physical storage of the data may be slightly different by OS. When a data block is read from disk into memory (db block buffer) the byte alignment is the same. That is the format is the same in memory as it is on disk. (Otherwise, it would have to go through machinations in translating the structure on disk to ram and visa versa, slowing the system down.)
This happened when Oracle on Netware went from 7 to 8 the byte alignment changed and so to upgrade you had to do a full export in 7, build the db in 8, and do an import.
Copying datafiles
APL, April 05, 2004 - 1:17 am UTC
Can i copy a datafile from red hatlinux to windows 2000 and make use of that file?
April 05, 2004 - 9:36 am UTC
In 10g, yes that is supported using cross platform transportable tablespaces.
In 9i and before, that is not supported.
Big and Little Endian
Richard, February 24, 2005 - 10:39 am UTC
Hi,
Does the 10g Cross-Platform Transportable Tablespaces function take endian-ness into account? e.g. can it transport AIX to Linux?
P.S. Oracle 10g is just amazing! My favourite enhancement has to be Enterprise Manager - it is a delight to use, compared to its predecessor.
February 24, 2005 - 4:57 pm UTC
yes -- you might have to use RMAN to facilitate that change but yes, endianess is taken care of
select * from v$transportable_platform order by 1;
What about export dump files?
Mark Woolever, May 19, 2005 - 10:47 am UTC
Hi, this thread was indeed informative and very helpful.
In respect to this discussion, I was curious how an export dump file is different from a datafile in that it is readable on different platforms.
May 19, 2005 - 11:35 am UTC
a dmp file is very radicially different from a datafile -- it isn't built for performance or updates or anything like that.
the dmp file was designed to get data out and in on different platforms is all. so it takes care of byte ordering and all whereas a datafile was designed to be "on that platform"
in 10g, you can transport datafiles from OS to OS now.
datafile question,
sns, May 19, 2005 - 2:24 pm UTC
I think it is not possible in Oracle, but I want you to clear my doubts.
I have a 100Gig database in 8.1.7. The database consists of two tablespaces (to store tables and indexes) spread across 6 different files.
I need to move the entire database to 10g.
Oracle 8i is on a Linux box A and the 10g database is located in different Linux box (say B)
I know there are quite a few options to move the data from 8i to 10g, but I have a dump question:
Can I ftp all the 6 datafiles from box A to B and create a new tablespace on 10g using those 6 files? This way I would have moved my entire database to 10g.
If not why?
OR
Can I copy the contents of the datafile(8i database) to the already existing tablespace on 10g?
Thanks
May 19, 2005 - 2:45 pm UTC
you won't "create a new tablespace", you'll just be moving the files -- the entire database, the whole thing (system, rollback, log, everything).
and then upgrade it.
just like a backup and restore.
and yes, you can transport an 8i tablespace(s) to 10g as well, that would be another approach -- to install 10g, create a new database, then transport the datafiles using exp/imp (doesn't actually export the data -- just the metadata)
Endian format
Yogesh, May 10, 2006 - 10:21 am UTC
what is the correct answer for
Which task would you perform to convert little endian CLOB data to the big endian format while transporting from Oracle9i database to Oracle 10g database?
A. Use the CHANGE command of the RMAN utility.
B. Use the CONVERT command of the RMAN utility.
C. Transport the data after changing the character set of the target database to big endian.
D. Transport the data after changing the character set to be an endian-independent format in the target database.
E. No action is required because Oracle RDBMS implicitly does the conversion in Oracle 10g database while accessing the data.
CLOB and Endianess
Michel Cadot, May 10, 2006 - 10:48 am UTC
Tom,
I think Yogesh refers to CLOB internal storage format in case of varying length character set.
Prior to 10g CLOB were stored in UCS2 which is endianness dependent. Starting with 10g it is in AL16UTF16 which does not depend on endianness.
There are some useful informations in Metalink notes 258114.1 (Possible action for CLOB/NCLOB storage after 10g upgrade) and 272354.1 (Handling of Clob data during cross platform tablespace transportation).
Regards
Michel
May 11, 2006 - 7:27 am UTC
Yes, but in the context of the question - it doesn't really "make sense". The byte ordering is not "opaque" to the dba.
CLOB and endianness
Michel Cadot, May 10, 2006 - 11:00 am UTC
Endian-dependent CLOBS may require separate conversion
Howard Rogers, May 10, 2006 - 6:53 pm UTC
CLOBS were represented as UCS2 before 10g. In 10g and above, they are represented as AL16UTF16. UCS2 is endian-dependent. AL16UTF16 is not. Big-endian UCS2 is the same as AL16UTF16.
So, if your data was created on a big-endian system, there is no need to convert that data: it's already in AL16UTF16 without any effort on your part. But if you are converting little-endian UCS2 CLOBS to a big-endian system, there will be a need to convert the CLOB to the big-endian AL16UTF16. That conersion **will happen entirely automatically and transparently** any time a user selects CLOB data after the transport to a big-endian platform. However, that will be a run-time conversion, and your users will know about it -and it's a bit daft to do it every time the data is accessed.
Instead, you mght want to do a one-time conversion by performing a "create table copy_table as select * from original_table". That will cause the CLOB-conversion to take place, but this time the converted data is stored permanently in a copy of the table. You then just "drop table original_table purge", and (re) "create table original_table as select * from copy_table". Finally, "drop table copy_table purge". You of course then have to worry about re-creating constraints and re-granting permissions on the newly-converted version of the table.
OS Issue
Senth., July 27, 2006 - 10:30 pm UTC
Mr.Tom.,
I like to know your advice/view....
The scenario is,
Oracle 8.1.5 with Workflow is running on Sun Solaris,
Rel. 7 5/99. OS Crahses..We have OS complete backup
for Sun OS and Oracle cold backup..
The SUN Box is the old model, the new HDD is not
supporting Sun OS Ver.7, we are not able to up the
sytem.
My question is ,
If we install the higher version of the OS, say
Solaris Ver.7 rel 11/99 or higher Solaris ver 8/9/10
with same structure(paths), and copy Oracle backups
including Oracle executables is it any possible way to
up the system??
Thanks in advance...
Good Day...
July 27, 2006 - 10:52 pm UTC
time to contact support...
for compatibility matrices - they have them all....
Any toll that converts datafiles
Michael, July 28, 2006 - 12:17 pm UTC
Hi Tom,
Is there any known tool that converts Oracle 8i datafiles (running on little-endian OS) to Oracle 8i or greater (running on big-endian OS).
Thanks,
July 28, 2006 - 8:41 pm UTC
no, not until 10g are there cross platform transports of tablespaces.
CROSS PLATFORM DATABASE
Laxman Kondal, August 31, 2007 - 12:14 pm UTC
Hi Tom,
Oracle 10g R2 Cross-platform database from Windows XP to Oracle Enterprise Linux works fine and when I tried to reverse it (from Linux to Window XP) then fails in both cases:
1. Used RMAN to convert at source
2. Copied datafiles after clean shutdown
Initially it failed at CHARACTER SET WE8ISO8859P1 which I changed it CHARACTER SET WE8MSWIN1252.
===============================
C:\>set oracle_sid=ird
C:\>set oracle_home=C:\oracle\product\10.2.0\db_1
C:\>sqlplus /nolog
SQL*Plus: Release 10.1.0.4.2 - Production on Fri Aug 31 10:29:06 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> @C:\oracle\product\10.2.0\db_1\database\transportscript.sql
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1248476 bytes
Variable Size 88081188 bytes
Database Buffers 176160768 bytes
Redo Buffers 2945024 bytes
Control file created.
ERROR:
ORA-03114: not connected to ORACLE
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ERROR:
ORA-03114: not connected to ORACLE
-------------------------------
SQL> connect /as sysdba
Connected to an idle instance.
SQL> @C:\oracle\product\10.2.0\db_1\database\transportscript.sql
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1248476 bytes
Variable Size 88081188 bytes
Database Buffers 176160768 bytes
Redo Buffers 2945024 bytes
Control file created.
ERROR:
ORA-03114: not connected to ORACLE
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
===============================
I could not find any exmple of Linux to Window little endian on metalink also.
Coluld you please help to figure out how to transport Oracle10g database from Linux to WindowXp.
Thanks and regards.
September 05, 2007 - 9:21 am UTC
what does the alert log say - the steps would basically be the "same"
CROSS PLATFORM DATABASE - Linux to WindowsXP
Laxman Kondal, September 06, 2007 - 11:00 am UTC
Hi Tom,
I cleaned up every thing and restarted with new copy of RMAN generated files.
It breaks when database is open with resetlogs option
C:\>oradim -NEW -SID IRDWIN -STARTMODE manual -PFILE "C:\oracle\product\10.2.0\db_1\database\initirdwin.ora"
Instance created.
C:\>set oracle_sid=irdwin
C:\>set oracle_home=C:\oracle\product\10.2.0\db_1
C:\>echo %oracle_sid%
irdwin
C:\>echo %oracle_home%
C:\oracle\product\10.2.0\db_1
C:\>sqlplus /nolog
SQL*Plus: Release 10.1.0.4.2 - Production on Thu Sep 6 07:47:13 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='C:\oracle\product\10.2.0\db_1\database\initirdwin.ora'
ORACLE instance started.
SQL> CREATE CONTROLFILE REUSE SET DATABASE "IRDWIN" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\oracle\product\10.2.0\oradata\irdwin\arch_D-IRDWIN_id-3129295109_S-155_T-1_A-626357861_00ir663i' SIZE 50M,
9 GROUP 2 'C:\oracle\product\10.2.0\oradata\irdwin\arch_D-IRDWIN_id-3129295109_S-156_T-1_A-626357861_00ir663i' SIZE 50M,
10 GROUP 3 'C:\oracle\product\10.2.0\oradata\irdwin\arch_D-IRDWIN_id-3129295109_S-157_T-1_A-626357861_00ir663i' SIZE 50M
11 DATAFILE
12 'C:\oracle\product\10.2.0\oradata\irdwin\system01.dbf',
13 'C:\oracle\product\10.2.0\oradata\irdwin\undotbs01.dbf',
14 'C:\oracle\product\10.2.0\oradata\irdwin\sysaux01.dbf',
15 'C:\oracle\product\10.2.0\oradata\irdwin\OPOM_DATA.dbf',
16 'C:\oracle\product\10.2.0\oradata\irdwin\OPOM_INDEXES.dbf',
17 'C:\oracle\product\10.2.0\oradata\irdwin\OPOM_LOBS.dbf',
18 'C:\oracle\product\10.2.0\oradata\irdwin\users01.dbf'
19 CHARACTER SET WE8ISO8859P1
20 ;
Control file created.
SQL> ALTER DATABASE OPEN RESETLOGS;
ERROR:
ORA-03114: not connected to ORACLE
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
============================================================
Alert log is:
Thu Sep 06 08:02:07 2007
ALTER DATABASE OPEN RESETLOGS
Thu Sep 06 08:02:07 2007
RESETLOGS after incomplete recovery UNTIL CHANGE 9176105399697
Thu Sep 06 08:02:07 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\udump\irdwin_ora_4088.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-155_T-1_A-626357861_00IR663I'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Thu Sep 06 08:02:08 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\udump\irdwin_ora_4088.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-156_T-1_A-626357861_00IR663I'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Thu Sep 06 08:02:10 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\udump\irdwin_ora_4088.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-157_T-1_A-626357861_00IR663I'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Thu Sep 06 08:02:11 2007
Setting recovery target incarnation to 2
Thu Sep 06 08:02:12 2007
Assigning activation ID 1337021494 (0x4fb15436)
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-157_T-1_A-626357861_00IR663I
Successful open of redo thread 1
Thu Sep 06 08:02:13 2007
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Sep 06 08:02:13 2007
SMON: enabling cache recovery
Thu Sep 06 08:02:13 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\udump\irdwin_ora_4088.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Thu Sep 06 08:02:13 2007
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Thu Sep 06 08:02:14 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_pmon_4284.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:14 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_reco_2816.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:14 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_smon_4312.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:14 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_ckpt_3404.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:15 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_lgwr_2300.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:15 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_dbw0_5584.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:16 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_mman_4308.trc:
ORA-00704: bootstrap process failure
Thu Sep 06 08:02:16 2007
Errors in file c:\oracle\product\10.2.0\admin\irdwin\bdump\irdwin_psp0_4288.trc:
ORA-00704: bootstrap process failure
Instance terminated by USER, pid = 4088
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...
============================================================
trc file
Dump file c:\oracle\product\10.2.0\admin\irdwin\udump\irdwin_ora_4088.trc
Thu Sep 06 08:00:56 2007
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:65M/510M, Ph+PgF:970M/2016M, VA:1652M/2047M
Instance name: irdwin
Redo thread mounted by this instance: 0 <none>
Oracle process number: 13
Windows thread id: 4088, image: ORACLE.EXE (SHAD)
*** 2007-09-06 08:00:56.124
*** SERVICE NAME:() 2007-09-06 08:00:56.108
*** SESSION ID:(159.1) 2007-09-06 08:00:56.108
kccsga_update_ckpt: num_1 = 8, num_2 = 0, num_3 = 0, lbn_2 = 0, lbn_3 = 0
Control file created with size 450 blocks
*** 2007-09-06 08:02:07.639
Prior to RESETLOGS processing...
ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE start
Database is not in archivelog mode
ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE complete
*** 2007-09-06 08:02:07.670
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-155_T-1_A-626357861_00IR663I'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-156_T-1_A-626357861_00IR663I'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D-IRDWIN_ID-3129295109_S-157_T-1_A-626357861_00IR663I'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
============================================================
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1248476 bytes
Variable Size 88081188 bytes
Database Buffers 176160768 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------------------- ---
3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D- NO IRDWIN_ID-3129295109_S-157_T-1_A-626357861_00IR663I
2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D- NO IRDWIN_ID-3129295109_S-156_T-1_A-626357861_00IR663I
1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\ARCH_D- NO IRDWIN_ID-3129295109_S-155_T-1_A-626357861_00IR663I
SQL>
============================================================
Seems Oracle can not find redo log files and looking for ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\DB_1 file which was nere created nor I could find any where on source database server.
============================================================
SQL> recover database until cancel;
ORA-00279: change 9176105399699 generated at 09/06/2007 08:02:13 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\1_1_632563327.DBF
ORA-00280: change 9176105399699 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IRDWIN\SYSTEM01.DBF'
ORA-01112: media recovery not started
SQL>
============================================================
Regards and thanks for help.
A reader, August 26, 2008 - 4:52 pm UTC
Was a solution to the last post ever given? I'm running into the exact same sequence of messages (and catch-22). I've cloned a 10.2.0.2 database onto a server with 10.2.0.4 software and performed incomplete recovery. When I attempt to OPEN RESETLOGS, I get the "ORA-01092: ORACLE instance terminated. Disconnection forced" message. If I try to STARTUP UPGRADE, I get "system01.dbf needs recovery".
August 26, 2008 - 9:34 pm UTC
please utilize support for something like this
Backup of oracle data base when oprating system not working
Kamal, February 17, 2009 - 1:30 pm UTC
Hi Tom,
I have installed Windows XP in my PC and oracle and created tabled and put data on those tables, but now my operating system is not working and I have attached my hard disk with other Computer and take backup of all data so that I can format the hard disk and reinstall operating system. Now I want to know how can I take backup of the oracle database when the operating system is not working.
Thanks
Kamal
February 17, 2009 - 2:52 pm UTC
copy the parameter, data, control, and if the database was not shutdown cleanly, the online redo logs. copy any auxillary files you setup as well (listener.ora, tnsnames.ora - whatever you customized)
then you'd need to install the oracle software, create a database instance (oradim - see documentation) and restore the files.
Backup of oracle data base when oprating system not working
Kamal, February 22, 2009 - 1:40 pm UTC
Dear Tom,
Thanks for your reply, but I am unable to understand that which file I have to copy for parameter, data & control. Oracle was installed at "C:\oracle\product\10.1.0\oradata". I have attached this hard disk with other computer but I am unable to understand that which files I have to copy so that when I install oracle again it works properly with all of its previous data and users.
Thanks & Regards,
February 22, 2009 - 5:41 pm UTC
you are saying you have no idea what a control file, parameter file, data file is or does or where you had placed them before?
Oracle was not installed in that directory, that looks more like a default location, that could easily have been overridden by you, for storing the datafiles and control files - but the stored parameter file would be elsewhere.
do you have a DBA that can help you out for five minutes? They can poke around and show you were the files are if you really haven't ever seen them.
You'll probably need their assistance figuring out what to do with oradim next. To get the services installed and all.
Backup of oracle data base when oprating system not working
Kamal, February 24, 2009 - 1:18 pm UTC
Dear Mr. Tom,
Thanks again for your prompt reply. I want to take the backup of oracle data just for my knowledge because right now I am studying and the data is not important. I just want to know the technique to copy oracle data when operating system not working. I have installed oracle 10g at its default location. Data path is "C:\oracle\product\10.1.0\oradata\orcl". Now I want to copy the data in other hard disk but I don't know which files I have to copy so that all data can be copied.
Thanks, Kamal
February 24, 2009 - 5:15 pm UTC
I said before, that is not an installation point, that mount point is where we might keep data.
I don't know what files you need either - it was your database, you created it, you configured it, you named the files (or not), you placed the files where you wanted.
find your control files
find your data files
find your redo log files
find your initialization file
those are the minimum you want (well, you can skip the redo logs if you shut the database down normally last time it was shutdown)
Migrate from Sun to Linux,
A reader, October 26, 2009 - 10:46 am UTC
Hi Tom,
I have a 15 TB 10.2.0.4 database on Sun SPARC. We are using ASM storage to store the datafiles.
Now, we need to migrate it on a X86 Linux 10.2.0.4 database. The database is going to be on a 3-node RAC. The storage in Linux is also on ASM. All the storage is XP24000.
I am aware of transportable tablespace but haven't used yet. However, for such a big database, I need a process that I can migrate it efficiently with low downtime and with very minimum chances of failure.
What would be the ideal and fastest way? Do we need any staging area to achieve this? If so, how big should it be? Can the staging area be on a file system? Should it be presented to Sun box or Linux box?
Thanks,
-Shiva
October 26, 2009 - 2:34 pm UTC
well, minimum downtime could involve replication (replicate from sparc to linux). Then to cutover you a) stop processing in sparc land, b) sync up, c) start processing linux land
If you want to do it as a data move (so that the migration of data does not affect existing in place processing capability as replication would), you would use cross platform transporatable tablespaces - no unload, no reload. More 'downtime' but no impact on production capabilities.
You would need no stage in #1, and for #2, you would just be using rman to copy the files into the new format (so you would have two copies at one point of course)
follow up,
A reader, October 26, 2009 - 3:40 pm UTC
Hi Tom,
Appreciate your quick response.
Could you please elobrate the process of migration? We can take some good amount of downtime of this database with prior notice. What would be the approximate amount of time to migrate in step 1 (using replication) for a 15 TB database?
With regard to option 2 (TTS), I first need to check whether tablespaces can be transported using TRANSPORT_SET_CHECK procedure. I have 186 tablespaces (including system,sysaux, temp and undo), should I include all the tablespaces as parameter in the procedure (by exluding the above 4) or can I execute it by specying few tablespaces at a time? After the check is done, can I start transporting the tablespaces one at a time and make it read-write before going to the next tablespace? That way I may not be needed 15 TB of staging area.
If TRANSPORT_SET_CHECK procedure says the tablespaces are not transportable, what would be our options?
Thanks,
October 26, 2009 - 4:30 pm UTC
you will not be transporting system, sysaux, temp and or undo.
You can only cross platform transport your DATA tablespaces.
And you need self contained sets - eg: if you put all of your indexes in one tablespace and your tables in 50 other tablespaces - then you would need to move all of them as a set.
You need to determine what your self contained sets are - eg: if I take tablespace1 which has tables A, B and C in it - then I need to take tablespace2 at the same time since the indexes for A, B and C are in there - however, the indexes for D, E and F are in tablespace2 as well - so I need to take tablespace3 which has D and E and tablespace4 which has F (and so on and so on)
what would be the options? it would depend on the reason the tablespaces are not transportable.
to find out "how long", you can only benchmark and evaluate. It really depends entirely on your infrastructure.
I think to set up 15tb - the answer of "pretty long" would be accurate. You might investigate offline instantiation if you go down that path.
Migration..follow up,
A reader, October 28, 2009 - 9:33 am UTC
Hi Tom,
I have written high level plans and I appreciate if you can give me your feedback.
Plans:
I will ask the Storage team to present 15 TB as file system initially to Sun box (source database). This is a temporary staging area only. This storage will be used to store the converted files from RMAN.
Using RMAN CONVERT TABLESPACE command, we will convert all the datafiles (data related only) and put them in the alloted file system.
After all the files are converted, we will unmount the file system from Sun box and mount it
to Linux box (target database). Hope this move works.
On Linux box, we will try to bring up the database by pointing it to the file system. We will open the database on Linux on file system as a single instance database.
Next, on Linux box using RMAN, we migrate the files from file system to ASM diskgroups.
Finally, the database on Linux is open on ASM diskgroups and all the other nodes are added to the database. The database on Linux is available on 3-node RAC.
Thanks,
-Shiva
October 28, 2009 - 10:41 am UTC
You cannot move system this way.
You can do this for tablespaces with your data only
You will do what you describe as far as converting, but when you get to linux land, you'll
a) create a new database
b) transport in these datafiles
c) install your code (plsql, views, sequences, etc)
Follow up,
A reader, October 28, 2009 - 10:56 am UTC
Hi Tom,
Thanks for your feedback. I am confirming again the steps on my Linux point of view. Before that, whether unmounting the file system from Sun and mounting it on Linux work?
I got your advice on Linux side. I first create a database on Linux. The datafiles for tablespaces like system, sysaux will be on ASM diskgroups. This database is on a 3-node RAC and is empty at this point of time.
On Linux, what I am going to do is:
- on RMAN prompt (connecting to database which is open on Linux), I issue CONVERT DATAFILE '/data/a1.dbf','/data/a2.dbf','/data/a3.dbf' df_file_name_convert ="/data/","+DATA1";
- After converting the datafiles to ASM diskgroup, I run impdb command:
impdp system/oracle dmpfile=expdat.dmp directory=data_pump_dir transport_datafiles =+DATA1/a1.dbf,+DATA1/a2.dbf,+DATA1/a3.dbf
- After impdb completes, I convert all the tablespaces on Linux to READ WRITE.
Does this plan works?
Thanks,
-Shiva
October 28, 2009 - 11:12 am UTC
"... Before that, whether unmounting the file system from Sun and mounting it
on Linux work?
..."
sure, fast way to move the data.
...Does this plan works?...
you will definitely let us know after you do a small scale test :)
the steps - the outline - sounds ok so far. reality and testing will find the bumps and smooth them out.
Thanks a lot,
A reader, October 28, 2009 - 12:35 pm UTC
Hi Tom,
I will definitely test taking 1 or 2 tablespaces and will let you know how it went.
Another quick question: Does the file sytem which we mount on Linux need to have READ-WRITE access on all the 3 nodes? Intially we thought to have access to any one of the nodes and RMAN will be triggered on that node.
Please let me know.
Thanks,
-Shiva
October 28, 2009 - 4:22 pm UTC
I'm not sure where the 3rd node came from - but every node that needs to write, will need read write (eg: ultimately - linux will need write)
Similar situation, moving from HPUX (64) To LINUX X86-64
Devanshi Parikh, November 12, 2010 - 1:22 pm UTC
Hi Tom,
The above thread was quite educational in terms of the moving exercise. I am facing this same, or similar (I should say) dilema at my company. I guess the trend these days is to move from large machines to smaller ones. I would be moving from HPUX (Parisc/Itanuim) single instance to 5 node RAC clusetered Linux x86-64 dell machines.
The largest database to undergo this move would be about 4TB. RMAN is not being used as a backup tool on source machines at this time and storage is traditional filesystems.
Hence looking for a good and minimum impact strategy for this move. Here are the details about the source and target databases.
Source(current) : Single instance, standalone database, not using RMAN as backup strategy. Oracle version 11.1.0.7, not using ASM instead, using traditional filesystems.
Target(future): Multi-instance 5 node RAC cluster running Linux. Oracle version 11.2.0.2, storage ASM.
Considering your response to the previous thread, would it be possible to apply similar steps to my situation also ?
Can RMAN be used perform the filesystem to ASM conversion in this scenerio ?
Regards,
Devanshi
endianess
A Reader, July 11, 2012 - 9:26 am UTC
Tom,
Thanks for your time.
The task : move 10g Dataware house to exadata 11g
Scenario/facts:
- mutil tera byte Oracle Data ware house DB 10g ( Source)
- Exadata machine 11g( Target)
- Source and target data centers are distant away ( over 100 km)
- There is OS change at Source and target
Aim :
As always , migrate the data as quicker as possible :)
Question
a) What would be the best approach to move the initial set of data... may be use of trucks would be helpful ( i.e. loading Tera bytes tapes into truck and hit the road rather than using computer network ;) )? and then possible make use of GoldenGate to re-sync the delta and so on..?
b) Does exadata facilitates some in built functions to copy(get) the data ?
c) Of course the best migration startegy would depend on the various factors.. but online redinition etc would best fit here? or convnetional TTS ?
d) how would the data flow... seeing that we have columnar compression etc etc on exadata ( target ) side -- so would these happen on fly?
e) some pointers/useful notes would be helpful...
Best Regards
July 12, 2012 - 5:17 pm UTC
distance is not as relevant as bandwidth. what kind of bandwidth do you have?
does the OS change involve endianess change? You never said??
If this is a data warehouse, why would any synchronization be necessary? Wouldn't you just move it between loads?
Do you just want to forklift it over (dump and load) or do you want to rearchitect it (taking advantage of hybrid columnar compression, dropping some indexes, rethiking your materialized views (to remove many of them) and so on.
..contd ..endianess
A Reader, July 12, 2012 - 9:00 am UTC
further to add that...
in above scenario approach is something like:
a) build the stage DB using RMAN backups from the source DB -- use tapes to load the initial data ( as disussed above)
b) using recovery ( archive files ) keep updating the stage DB from source
c) on a given day stop recovering the stage DB
d) use TTS ( cross) conver endian ( to low) and move datafiles to final target ( exadata DB)
e) get the final target (exadata DB) in sysnc with the source using the Golden Gate
f) on D-day cut off users from source .. sysnc the final chunk using GoldenGate
g) stop Goldengate replication...Switch users to final target(exadata)
your thoughts?
July 12, 2012 - 6:16 pm UTC
please address the above.
why is there a need for syncing up?