cancel
Showing results for 
Search instead for 
Did you mean: 

copying .dbf files

SOLVED
Go to solution
Vanja
Frequent Advisor

copying .dbf files

Hi,

I'm trying to migrate some oracle filesystems from an sc10 to lvol on xp512. I'm seeing some unexpected behavior when I use the copy command. Note that the database is down at this time. The /nise3 filesystem is sitting on an SC10 and the /nise3_bak filesystem is sitting ldev from an xp512 array.

# cp -p -r /nise3/* /nise3_bak
nistdb2 /nise3/wfm>ll /nise3/wfm
total 6385968
-rw-r----- 1 oracle dba 52436992 Jan 17 22:19 cwmlite01.dbf
-rw-r----- 1 oracle dba 52436992 Jan 17 22:19 dysys01.dbf
-rw-r----- 1 oracle dba 52436992 Jan 17 22:19 example01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 17 22:19 system01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 17 22:19 temp01.dbf
-rw-r----- 1 oracle dba 3145736192 Apr 27 2005 temporary01.dbf
-rw-r----- 1 oracle dba 20979712 Jan 17 22:19 tools.dbf
-rw-r----- 1 oracle dba 20979712 Jan 17 22:19 users.dbf

nistdb2 /nise3/wfm>ll /nise3_bak/wfm
total 8581264
-rw-r----- 1 oracle dba 52436992 Jan 17 22:19 cwmlite01.dbf
-rw-r----- 1 oracle dba 52436992 Jan 17 22:19 dysys01.dbf
-rw-r----- 1 oracle dba 52436992 Jan 17 22:19 example01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 17 22:19 system01.dbf
-rw-r----- 1 oracle dba 524296192 Jan 17 22:19 temp01.dbf
-rw-r----- 1 oracle dba 3145736192 Apr 27 2005 temporary01.dbf
-rw-r----- 1 oracle dba 20979712 Jan 17 22:19 tools.dbf
-rw-r----- 1 oracle dba 20979712 Jan 17 22:19 users.dbf

nistdb2 /nise3/wfm>du -ks /nise3/wfm/*
51208 /nise3/wfm/cwmlite01.dbf
51208 /nise3/wfm/dysys01.dbf
51208 /nise3/wfm/example01.dbf
512016 /nise3/wfm/system01.dbf
512016 /nise3/wfm/temp01.dbf
1974352 /nise3/wfm/temporary01.dbf
20488 /nise3/wfm/tools.dbf
20488 /nise3/wfm/users.dbf

nistdb2 /nise3/wfm>du -ks /nise3_bak/wfm/*
51208 /nise3_bak/wfm/cwmlite01.dbf
51208 /nise3_bak/wfm/dysys01.dbf
51208 /nise3_bak/wfm/example01.dbf
512008 /nise3_bak/wfm/system01.dbf
512008 /nise3_bak/wfm/temp01.dbf
3072016 /nise3_bak/wfm/temporary01.dbf
20488 /nise3_bak/wfm/tools.dbf
20488 /nise3_bak/wfm/users.dbf
nistdb2 /nise3/wfm>

I'M ALSO SEEING THIS BEHAVIOR ON ANOTHER FS THAT I'M TRYING TO COPY - NAMELY /nise19 to /nise19_bak.

nistdb2 />ll /nise19/dbfiles/lns
total 40960256
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP34.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP35.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP36.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP37.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP38.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP39.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP40.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP41.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP42.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP43.d
bf
-rw-r----- 1 oracle dba 2147491840 Nov 3 2003 dynidx33.dbf
-rw-r----- 1 oracle dba 2147491840 Nov 3 2003 dynidx34.dbf
-rw-r----- 1 oracle dba 2097160192 Nov 3 2003 staidx02.dbf
nistdb2 />ll /nise19_bak/dbfiles/lns
total 53444960
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP34.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP35.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP36.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP37.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP38.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP39.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP40.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP41.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP42.d
bf
-rw-r----- 1 oracle dba 2097160192 Dec 12 13:11 CITY_DATA_TBLSP43.d
bf
-rw-r----- 1 oracle dba 2147491840 Nov 3 2003 dynidx33.dbf
-rw-r----- 1 oracle dba 2147491840 Nov 3 2003 dynidx34.dbf
-rw-r----- 1 oracle dba 2097160192 Nov 3 2003 staidx02.dbf
nistdb2 />du -ks /nise19_bak/dbfiles/lns/*
2048016 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP34.dbf
2048016 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP35.dbf
2048016 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP36.dbf
2048008 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP37.dbf
2048008 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP38.dbf
2048016 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP39.dbf
2048016 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP40.dbf
2048008 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP41.dbf
2048008 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP42.dbf
2048016 /nise19_bak/dbfiles/lns/CITY_DATA_TBLSP43.dbf
2097168 /nise19_bak/dbfiles/lns/dynidx33.dbf
2097168 /nise19_bak/dbfiles/lns/dynidx34.dbf
2048016 /nise19_bak/dbfiles/lns/staidx02.dbf
nistdb2 />du -ks /nise19/dbfiles/lns/*
2048016 /nise19/dbfiles/lns/CITY_DATA_TBLSP34.dbf
2048016 /nise19/dbfiles/lns/CITY_DATA_TBLSP35.dbf
2048016 /nise19/dbfiles/lns/CITY_DATA_TBLSP36.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP37.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP38.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP39.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP40.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP41.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP42.dbf
2048008 /nise19/dbfiles/lns/CITY_DATA_TBLSP43.dbf
8 /nise19/dbfiles/lns/dynidx33.dbf
8 /nise19/dbfiles/lns/dynidx34.dbf
8 /nise19/dbfiles/lns/staidx02.dbf
nistdb2 />


DOES SOMEONE KNOW WHY THE LONG LISTING SHOWS THE SAME FILE SIZE - BUT WHEN I DO A "DU" I SEE A HUGE DIFFERENCE BETWEEN THE SIZE OF THE /nise3/wfm/temporary01.dbf and /nise3_bak/wfm/temporary01.dbf files?????

AM I DOING SOMETHING WRONG? I'M NOT A DBA, BUT JUST TRYING TO RECREATE THE FILESYSTEMS ON A SEPARATE SET OF DEVICES.

ANY IMPUT WILL BE GREATLY APPRECIATED!

THANKS!
13 REPLIES
Joseph Loo
Honored Contributor

Re: copying .dbf files

hi,

if your question is why du size is different from list directory size, refer to this kb:

http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&docId=200000080063060

regards.
what you do not see does not mean you should not believe
Yogeeraj_1
Honored Contributor

Re: copying .dbf files

hi,

i think the above replies clarifies everything. However, on top of that verification when copying the files, i would also check the datafile size invidually. A bit of paranoia wont harm when dealing with such critical manipulations.

Dont forget to do a full backup before everything...

hope this helps too!

kind regards
yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sivakumar TS
Honored Contributor

Re: copying .dbf files


HI,

The du command gives the number of 512-byte blocks and ll gives size in bytes.

With Regards,
SIva
Nothing is Impossible !
Vanja
Frequent Advisor

Re: copying .dbf files

Thanks for the replies!

I was using "du -ks" command with the "-k".

So I guess 2 remaining questions: Is it OK to use the "cp -p -r" command when copying over the *.dbf files given that the Database is down? Does the DBA need to do any manipulations after the filesystem move given that the mountpoints remain the same? Namely the mountpoints on the new devices will be the same as for the old. The old ones will be unmounted of course.

Thanks Again!
Vanja
Patti Johnson
Respected Contributor
Solution

Re: copying .dbf files

Once the moves are complete and if the full filename is exactly the same, then the database should open as if nothing happened. The DBA will want to check the file status on all files of course, but there should be no problem if all files have the same name, ownership and permissions after the move as before.

Patti
Simon Wickham_6
Regular Advisor

Re: copying .dbf files

Vanja,

Before copying the database to a new location, it is necessary to perform
a full cold backup of the database, whilst the database is shutdown.

In order to move the database, it is necessary to create a script containing information about the files of the database. Issue a 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;'
so that this will create a trace file in the trace file directory.

Copy all parameter files, controlfiles, and all files to their new location taking care to preserve ownership and permissions.

In order to establish the new database in the new location, the CREATE CONTROLFILE command.

Simon


Steven E. Protter
Exalted Contributor

Re: copying .dbf files

Shalom,

Of course you know that the database must be down for this to work. No alternative.

You should not be seeing temporary files.

Before doing this, take a tape backup in case the data gets corrupted.

Good Luck,

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Tom Danzig
Honored Contributor

Re: copying .dbf files

I cannot argue with the idea of a cold backup, however, keep this in mind:
After you copy all the data to the new mounts on the XP, just unmount the SC10 file systems and remount the XP's as the SC10 names.

The original DBF files will still be there on the SC10 if there are any problems. Just unmount the XP and remount the SC10 to be right back where you started.


TwoProc
Honored Contributor

Re: copying .dbf files

Simple, temporary files are what I call "sparse" datafiles ( I say "I" at this point b/c I'm not sure if that is the corect term). Their size may not be what they seem.

Try this test, go into oracle and create a new 2G datafile for a TEMP tablspace. The go out to your Unix shell, and do the same, an "ll" and a "du" and compare the difference. It will be huge. This is normal for TEMPORARY data files, until the database decides to actually use all of those extents in the datafile, it will remain 'smaller' than it appears, for lack of a better description.

Doesn't matter, when you move temp tablespace files, you really should RECREATE all temporary tablespace files from scratch, in order to avoid problems later on (during a production day for example). This should be done as a matter of course, as it is quite simple and painless.
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: copying .dbf files

hi,

to know more about the temporary files that are in use by your database, you can run the following SQL:

select * from dba_temp_files;

note that i you decide to create new temporary tablespaces (other that what you had previously), you will have to modify the user profiles accordingly.

hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Dave Walley
Frequent Advisor

Re: copying .dbf files

I believe the reason that the file shows as a smaller size is due to the fact that oracle temp files are only used when there is a query run that requires a sort. The files exist in sparse format and will use the true disk space only after the database is used and sorting occurs. To prove this have a dba run a query that involves a sort and watch the size of the file increase from a block usage point of view. Use "ll -s file_name" before and after the query is run. Dave
why do i do this to myself
TwoProc
Honored Contributor

Re: copying .dbf files

Right Dave,

but keep in mind that they don't shrink back down. Eventually, they will probably "fill out" to full size.
We are the people our parents warned us about --Jimmy Buffett
Vanja
Frequent Advisor

Re: copying .dbf files

Thanks to all for your information!