How to Create Oracle Dbase Spanning Multiple Hard Drives 1

[johndrake]

Hi All,

I'm new to Oracle and I am hoping that someone can help
me with this question. I currently Oracle 8.1.7 installed on
one of our hard drives (hdisk8). We are on an AIX box running AIX 4.3.3. Below is the layout:


hdisk8 000c30cd67998541 dbasevg
hdisk11 000c30cdebd9c802 None
hdisk13 000c30cd4661e24d None
hdisk14 000c30cd4661ed7a None
hdisk15 000c30cdba20fcf5 None
hdisk16 000c30cdba21077e None
hdisk17 none None
hdisk18 000c30cdebd7f601 None
hdisk19 none None
hdisk20 none None
hdisk21 000c30cd9f172a5b None
hdisk22 none None

I would like to create a database that would span these
existing hard drives. The first question is:

1. can I create a database that would span these hardrives?
2. how would I do it---just the "Oracle" stuff. I know how
to create volume groups, filesystems, etc.

Thank you,

-Joe

 

[tristan308]

I have an Oracle db spread throughout 4vg's. I create lv's with mklv -y [lv_name] -t raw [vgname] [pp's] [hdisk#]. Then chown oracle.dba /dev/r[lv_name] and chmod 600 /dev/r[lv_name]. Then sqlplus and either create tablespace or # alter tablespace [tablespace_name] add datafile '/dev/r[lv_name]' size [lv size minus 1]; Then you can do fun table stuff. Hope this helps.

 

[Chapter11]

If you know the AIX stuff, just use the LVM to distribute the LVs across the disks. Doing that more or less obviates the need for doing anything on the Oracle side of things (IANADBA).

As for the Oracle end of creating databases, I would suggest looking in the Oracle forum.

 

[KOG]

Hi joe,

The way to create the database and to ensure its datafiles span across several disks (as some datafiles are read/write intensive) is to run a script from oracle sql plus or server manager tool (svrmgr). I assume you have already installed oracle on the box? Does it comply with OFA standard i.e. certain system files should be installed in /opt/oracle etc. Oracle base directory is /home/oracle but oracle home directory is /opt/oracle etc.

The way I usually do is I create a couple of sql scripts to 'create' the database and its datafile but before I do I create a couple of filesystem (one for each disk) where DEV is the name of the oracle instance i.e.

/oracledata1/oradata/DEV
/oracledata2/oradata/DEV
/oracledata3/oradata/DEV
etc


CREATE DATABASE DEV
CONTROLFILE REUSE
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 200
DATAFILE '/oracledata1/oradata/DEV/system01.dbf' SIZE 40m REUSE AUTOEXTEND ON NEXT 1M
LOGFILE GROUP 1 ('/oracledata1/oradata/DEV/redo01a.log') SIZE 8M,
GROUP 2 ('/oracledata2/oradata/DEV/redo02a.log') SIZE 8M,
GROUP 3 ('/oracledata3/oradata/DEV/redo03a.log') SIZE 8M;


Then second script;

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/oracledata1/oradata/DEV/temp01.tmp' SIZE 512M autoextend off extent management local uniform size 128k;
CREATE TABLESPACE rbs DATAFILE '/oracledata2/oradata/DEV/rbs01.dbf' size 512m autoextend off extent management dictionary;
CREATE TABLESPACE users DATAFILE '/oracledata2/oradata/DEV/users01.dbf' size 768M autoextend off extent management local autoallocate;
CREATE TABLESPACE tools DATAFILE '/oracledata1/oradata/DEV/tools01.dbf' size 128M autoextend off extent management local autoallocate;
CREATE TABLESPACE tsindex DATAFILE '/oracledata3/oradata/DEV/tsindex01.dbf' size 500M autoextend on next 256M extent management local autoallocate;

Hope this makes sense?

This is rather a broad topic and usually a job for the DBA.

Katherine

 

[courcol]

1. Yes. You can use dbassist to friendly create your database (it makes all the stuff to create datafiles, execute right SQL scripts to build necessary views and configure Listener connexions) over any disks you have

2. a) Create a VG grouping together all of your disks: you'll be able to make use of the whole space you have without having any question, but can't make fine tuning with your Oracle database

2. b) According to me (and my poor experience), the best way to control the location of the datafiles you need to create (and then to optimize your database by distributing datas, indexes, redo logs ... across disks and controlers) is to keep disks into separate VG (1 VG for data, 1 VG for indexes ...) on separate disks and controllers. But in that case, if you don't have any idea of your database size, the odds are high that your VG won't be right sized (and finally put on different VG things that do not have anything to do together).