Hi

I am currently on Oracle 10.2 and AIX 5.3
I have imported into the oracle under one datafile.
Now I find out that the file is too big (30 GB) to tar to tape.
Please advise me how to split this one datafile to many small datafiles. I heard I should save it to 2G for each file. Is this the maximum?

thanks and regards

It is preferrable to split the data and indexes into small manageble files. yes. you can split the file to 2Gb chunks and backup. However, how do you keep the consistency and recovery model is always to recover that 10Gb file.

If you prefer and have time, I would recommend to split the files in to 2GB chunks, so that you can use oracle hot backup to maintain a clean recovery model. Nothing stopping you to keep the entire 10Gb file in backup mode for the duration of the backup. You may generate excessive redo logs during that period (Oracle Gurus: please confirm).

To split a file: you can cat <file name> | split -b2000m filename. This will do the trick. But I recommend the first option.

Thanks

The easiest way to go to smaller datafiles:
- create new tablespaces with smaller datafiles
- make a script for each table and index:

alter table baan.x move online storage tablespace y;
alter index baan.x$idx1 rebuild online tablespace y;

- check, double check and test if original tablespace is empty:

select owner, table_name from dba_tables where tablespace_name = 'X';
select owner, index_name from dba_indexes where tablespace_name = 'X';

- remove old tablespace

The fastest way to backup large tablespace is to make use of Oracle's RMAN fucntionality. As this tool is faster it has more than one advantage.

As I had no time to test it completely I encourage you to do so.

It is preferrable to split the data and indexes into small manageble files. Wyy split data and indexes? Just spread the load on the filesystem you have.

Kind regards,
Dick

I agree with the suggesstion.

Actually tar has a historical limit of 2GB on some platforms. To fix this, you can either use datafiles below 2GB, which is a practical solution for small installations, or:

a) use a decent backup tool
b) use the GNU version of tar (gtar), which has no such limits.

Regards