Copying Oracle database

[OFC_EDM]

We're migrating Oracle from 11.x to 11.5.9 this weekend.

Just found out that our DBA's don't trust cpio to make a backup copy of the DB.

I need to know the BEST method of copy an Oracle database. Either using tar / cpio or some other utility.

Also if anyone can verify there are issues with using cpio to copy and Oracle I'd like to know why. Just for my own sanity check.

[David DiBiase]

I am not that familiar with Oracle but, I asked our DBA's and they usualy use Oracle's export and import commands to copy database tables. HTH

[Rick Garland]

With the databse in backup mode or down, you could use vxdump to do the backup.

Question, if the DBAs do not trust cpio for backups, what do you use for backups?

[TwoProc]

Well, "cpio" is fine, and I don't see what the problem is, UNLESS you've got files larger than 2G.

Are you moving to another server? Same Server, and just building a new database before the upgrade?

I really like (and have used to clone Oracle databases) using:
cd /sourcedrive/oradata/OLDINSTNAME
find . | cpio pdmvu /targetrive/oradata/NEWINSTNAME

And I like it becuase it keeps file and dir permissions and ownerships correct - saving a lot of work and worry after the copy. Not so much for db files (b/c you can fix them all at once fell swoop if the dir names are organized consistently, but for ORACLE_HOMEs and APPL_TOPs, etc.


You can also use tar:

cd /sourcedrive/oradata/OLDINSTNAME
tar cvf - . | (cd /destdrive/oradata/NEWINSTNAME; tar xvf - )

The problem gets into whether or not you have files that are over 2G in size. Some tools fail with files larger than this. I'm not sure which ones b/c I don't have any that large.

[OFC_EDM]

In our normal backup scenario we do the following.

We do a snapshot of the disk to separate SAN disk.

Then we take that snapshot and mount it on a separate server. Then we bring up the copy of the database on that server. Once the database is verified we fracture the LUNs from that server and mount them to our backup server. The snapshot is then copied to tape.

This method is nice because the snapshot and verification takes 15 minutes. And then we can bring up our production environment and continue working while the snapshot is backed up to tape.

So you're probably wondering why I'm asking about cpio? The answer is that this will be our 2nd level backup. Yet another level of redundancy.

[James R. Ferguson]

Hi Kevin:

As long as the database is closed, your choices of copy are fairly broad. I have used 'cpio' with Oracle datafiles to move them from one logical volume to another without any problem. You could use 'tar' or 'fbackup' either piped from one filesystem to another or (of course) to tape.

The only "copy" mechanism that I would *not* use would be 'cp' since it will expand (pad with zeros) any sparse Oracle files.

Regards!

...JRF...

[Stuart Abramson]

I have always used "cpio" in the background to copy all types of files.

Our DBAs use RMAN to copy database files. Very high rate of data transfer with parallel streams and compression, etc.

Here is simple script for copying files:

A. On different systems:

cd $sourcedir
find . -xdev -depth | cpio -ocax | \
remsh $destcpu "cd $destdir ; cpio -icduxm"

B. On the same system:

1. cd /dir
find . -depth -xdev -print | cpio -pdum /newdir

[OFC_EDM]

I've just heard that using cpio may cause issues when restoring the Oracle backup. Due to the fact that cpio only uses checksum to verify files and may expand sparse files.

Whereas "fbackup and frecover" has more file checking.

Any comments out there on this thought?

[Raj D.]

Hi Kevin ,

You can use tar as well,

1. shutdown oracle
# /sbin/init.d/oracle stop

# cd $ORACLE_HOME
# tar -cvf /dev/rmt/0m *

Also you need to copy the other oracle configuration files as well ,
/etc/oratab etc.

I have done that many times , with Oracle Workgroup server ,and restored successfully.

Cheers,
Raj.

[Rick Garland · Accepted solution]

If you decide to use tar or cpio, make sure you get the letest versions of. These will handle files >2GB.

[OFC_EDM]

Rick,

Do you happen to have the version number for cpio/tar which handle files > 2 Gb?

[Raj D.]

Hi Kevin ,

Which version of hp-ux do you have , is it 11.00 or 11.11.

If you are having B.11.11 and tar cumulative patch installed it will support larger than 2GB file size.

You can check with this :

# what /usr/bin/tar
/usr/bin/tar:
tar.c $Date: 2003/05/19 02:55:16 $Revision: r11.11/6 PATCH_11.11 (PHCO_28992)
$Revision: @(#) all CUP11.11_BL2003_0522_1 PATCH_11.11 PHCO_28992
Thu May 22 03:24:02 PDT 2003 $

This will enable you to backup larger than 2GB and upto 8GB ,


Cheers,
Raj.

[James R. Ferguson]

Hi Kevin:

Well, as far as 'cpio' goes, it will *not* always preserve the "sparseness" of files. I guess I never evalulated the number of blocks allocated after using it and crossing mountpoints!

It turns out that if you are copying files with 'cpio' between directories that reside in the same filesystem, *and* you use the '-l' option to tell 'cpio' to relink files whenever possible, then "sparseness" is preserved. If you cross mountpoints, however, padding occurs.

If this is an issue for you, I suggest you use 'fbackup' like this:

# cd /srcdir
# fbackup -i . -f - | (cd /dstdir; frecover -Xsrf -)

Be sure to include the '-s' option of 'frecover'.

Too, 'fbackup' & 'frecover' handle files larger than 2GB.

Regards!

...JRF...

[Rick Garland]

Don't know the versions of hand, but if you goto http://gatekeep.cs.utah.edu and get the latest you are OK

[OFC_EDM]

We have 11.11 and thankfully have the current version of tar.

I think we'll stay with our current cpio method. As I discovered that early this year when they had the cpio backup fail that the backup was run at the same time as the regular tape backup.

So most likely files were open for the tape backup when the cpio > file backup was run. cpio wouldn't know files were open and would have backed up garbage right?

Of course if I discover we have files > 2Gb we'll switch to tar since we don't have the current version of cpio which supports files > 2 Gb.

[Raj D.]

Hi Kevin ,

You are having hp-ux 11.11 , with latest tar , so seems no problem.

---
Though if you feel file is larger than 8GB support required, you can try with gtar .

Although tar is supplied with HP-UX, gtar has more options and handles absolute paths as relative paths, restores the original dates of directories when extracting and also supports large file systems. The gettext and libiconv packages should be installed first, prior to installing tar.

Here is the link: [need to install 1, then 2 , then 3. ]


1. http://gatekeep.cs.utah.edu/hppd/hpux/Gnu/gettext-0.14.5/
2. http://gatekeep.cs.utah.edu/hppd/hpux/Development/Libraries/libiconv-1.10/

3. http://gatekeep.cs.utah.edu/hppd/hpux/Gnu/tar-1.15.1/


Cheers,
Raj.

[Alexey_12]

For production ORACLE databases I highly recommend using archivelog mode and RMAN utility as it provides some recovery options that are not available otherwise. Also most popular commercial backup solutions are just an interface to RMAN that is running on there behalf.

Once database instances are not running you can treat it like a set of plain files and archive it using any preferred way. Another method of backing ORACLE it putting tablespaces in backup mode one by one. Check oraback.sh on google for free tool that are using this method. However database media recovery might be tricky in that case.

[Bill Hassell]

cpio will probably never handle files larger than 2Gb. To do so will break compatibility. Later versions of tar can handle up to 8Gb but files larger than 2Gb cannot be restored on other systems that do not have this non-standard version of tar. There is GNU tar (gtar) which handles large files, but just like the HP-UX version, any file on the tape larger than 2Gb cannot be read by standard tar.

Neither cpio nor tar (or pax or dump, etc) have data integrity features. Like most classic Unix tools, they rely solely on the hardware and driver to do the right thing and they terminate if an error is returned from the driver. In that case, your DBAs are correct.

As far as sparse files, no general backup program can 'see' sparse files, much less preserve their exact layout. A sparse file is created with holes but Unix always 'fills' in the holes with zeros when the file is read serially (like a backup). Even fbackup cannot determine where a file may be sparse versus filled with zeros. The -s option in frecover simply looks for a string of zeros and lseeks over to the next non-zero record. This will approximate the original file and in all cases (expanded or sparse), the file will be the same to Oracle (and sum/cksum values).

As fr as reliability, fbackup/frecover are by far the best choice for data reliability and versatility. They will handle any sized file, creates a program-generated checksum on tape, provide re-sync ability for bad spots on a tape, handles multi-tape backups and has a central index of all files on every tape. Even when used in a pipe, fbackup is much faster than cpio/tar as it starts multiple file readers that work in parallel and store their data records in shared memory to keep fbackup running at full speed.

[renarios]

Hi Kevin,

Here's a nice article on Oracle Metalink howto clone a database on windows using RMAN:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=228257.1

In case you do not have an acount on metalink, see the attachment.

Hope that helps,

Renarios

[Luk Vandenbussche]

Kevin,

I think you make a mistake.
The highest oracle version is 10.x

[OFC_EDM]

Thanks everyone for all the responses.


Our upgrade went well and thankfully we didn't need to recover from backup.

But we did end up using cpio. With all the information everyone provided we did a few extra checks to do as much integrity checking as possible. (File counts, block counts etc.)