Re: How can I allocate database files to another hard disks

"Ursula Lee" <ursula.lee_at_thales-is.com> wrote in message news:br6ir9$klc82_at_imsp212.netvigator.com...
> Thanks. But I still have couple of questions:
>
> 1. How should I name the mount point and path and calculate the size
> for data files store in the new hard disks? Should I use symbolic link?
> The current dbf files distribution is listed below. (see below : First
> part show distribution of dbf files, second part is the bdf of our
> current harddisk )
>
> 2. I have to physically move the datafiles to the new location or I can
> just type : alter tablespace <tablespace_name>...... in sqlplus?
> Is it required to delete the original .dbf files?
>
> 3. Do I have to recreate the tablespace again, as I have the CreateDB
> file scripts?
> 4. For existing Oracle instance, like rollback segment, any changes
> required? Does that mean I just need to alter the database files, and
> nothing else?
> 5. Should I move all the database files to the new hard disks, or just
> some of them?
> 6. If I need to mirror the controlfiles and multiplex the redo logs,
> how should I handle this? I can see our CreateDB.sql script contains
> the setup on 'redo log', should I handle this here? I need to recreate
> the log files?
>
> total 4686400
> -rw-r----- 1 oracle orainventor26222592 Nov 7 17:17
indx1_tkedbms.dbf
> -rw-r----- 1 oracle orainventor304095232 Nov 7 18:03
> system_tkedbms.dbf
> -rw-r----- 1 oracle orainventor613425152 Nov 7 17:17 ts_tkedbms.dbf
> -rw-r----- 1 oracle orainventor104865792 Nov 7 17:17
> ts_tkedbms_rbs1.dbf
> -rw-r----- 1 oracle orainventor53485568 Nov 7 16:33
> ts_tkedbms_temp1.dbf
> -rw-r----- 1 oracle orainventor10493952 Nov 7 17:17
> ts_tkedbms_tools.dbf
> -rw-r----- 1 oracle orainventor1247813632 Nov 7 18:03
> ts_tkedbms_undo1.dbf
> -rw-r----- 1 oracle orainventor39985152 Nov 7 17:17
> ts_tkedbms_xdb1.dbf
>
> Filesystem kbyte used avail % used Mounted on
> /dev/vg00/lvol3 143360 31178 105194 23% /
> /dev/vg00/lvol1 83733 36894 38465 49% /stand
> /dev/vg00/lvol8 1142784 951684 180516 84% /var
> /dev/vg00/lvol7 1216512 645059 535780 55% /usr
> /dev/vg01/lv_users 3072000 231397 2663823 8% /users
> /dev/vg00/lvol4 73728 1863 68076 3% /tmp
> /dev/vg00/lvol6 4096000 3988152 101166 98% /opt
> /dev/vg00/lvol5 53248 1117 48880 2% /home
> /dev/vg01/lv_extopt 786432 484905 282713 63% /extopt
> /dev/vg01/lv_bases 5017600 5010742 6536 100% /bases
>
>
>
> R.B wrote:
> > "Ursula Lee" <ursula.lee_at_thales-is.com> wrote in message
> > news:br45bj$htp21_at_imsp212.netvigator.com...
> >
> >>Hi All,
> >>
> >>We are currently running out of disk space in our existing hard drives.
> >> The Oracle instances always take up to 100% of disk space usage. We
> >>are now ordering the extra 2 hard disks 18 G each (36 G total), is it
> >>possible to allocate those data files in the extra hard disks, but
> >>keeping the Oracle instance in the existing one?
> >>
> >>As I am not a DBA, any idea how to do that?
> >>
> >>One of the example in creating DB Files is:
> >>
> >>CREATE TABLESPACE "INDX" LOGGING DATAFILE
> >>'/bases/data/oradata/TKEDBMS//bdoracle/indx1_tkedbms.dbf' SIZE 25M REUSE
> >>AUTOEXTEND
> >>ON NEXT 1280K MAXSIZE UNLIMITED,
> >>'/opt/oracle/product/oracle9i/oradata/TKEDBMS/.dbf' SIZE 5M EXTENT
> >>MANAGEMENT LOCAL SEGMENT
> >>SPACE MANAGEMENT AUTO ;
> >>
> >>Appreciate your help!
> >>Ursula
> >>
> >
> > Ursula,
> > If your tablespace is running out of space add a datafile to it on
another
> > disk with a modified version of the statment below:
> >
> > SQL> alter tablespace tablespace_name add datafile
'/path/to/datafile.dbf'
> > size ??m;
> >
> > change it for your own -
> > tablespace_name
> > '/path/to/datafile.dbf'
> > ??m - this can be K,M etc
> >
> > hope this helps
> >
> >
>

Ursula,
What my reply suggested was to create additional datafiles on a new disk/volume to increase the size of your tablespaces. This will not reduce the amount of space currently used under /bases but should prevent the tablespace running out of space by providing extra space on an alternative disk. If what you what to do is move data off the /dbases mount point because the datafiles on it have 'autoextend on' which will try to autoextend beyond the capacity of the /bases volume then this is a different issue. You appear to have to options:

1. Increase the space available for the tablespace(s) running out of space. This can be achieved by adding additional datafiles on a new disk which has space.

or

b. If it is a problem that /bases has run out of space and the datafiles can autoextend, you can stop them from autoextending and leave /bases full (which should not be a problem) or leave them to autoextend but move them onto a new disk with space.

Having looked at the information you have provided it appears that you might be running Veritas volume manager or it is an AIX box. So when you get the new disks add them to the volume group vg01 and increase the size of the logical volume lv_bases by the new disk sizes and then grow the /bases file system - this should hopefully should solve the problem if not let me know Received on Wed Dec 10 2003 - 08:44:56 CST