This discussion is archived
9 Replies Latest reply: Dec 3, 2012 9:16 AM by Mark Malakanov (user11181920) RSS

create a database in multiple directory

936666 Newbie
Currently Being Moderated
hi
can we create a database in multiple directory (mean to say in windows OS)

will it boost the database performance ?

what will be the syntax to create a database in different directory

Thanks!
  • 1. Re: create a database in multiple directory
    Sunny kichloo Expert
    Currently Being Moderated
    Better to create datafiles in one directory.

    Also you can take backup if you want to prevent your system from any disaster.


    You can do multiplexing of Control files if you want.
  • 2. Re: create a database in multiple directory
    Justin Cave Oracle ACE
    Currently Being Moderated
    I'm not sure that I understand the question you are asking.

    You can certainly create a tablespace in a database that has data files in multiple different directories. If that means that you are spreading the I/O load across more physical devices, that can certainly improve performance. If the data files are on the same physical drives or there are other files on the other drives, however, you probably won't see much of a performance boost. On a modern server, you would generally be using some sort of SAN/ NAS that takes care of spreading the I/O across all the available drives.

    If you are asking how to add data files to a tablespace that are in a different directory, you would generally just specify the different paths in the ALTER TABLESPACE command.

    Justin
  • 3. Re: create a database in multiple directory
    Samuel G. Cristobal Newbie
    Currently Being Moderated
    Ok, is a recommendation.

    syntax example for db with sid: dbcat

    CREATE DATABASE dbcat
    LOGFILE group 1 ('/u01/app/oracle/product/10.2.0/oradata/dbcat/redolog1a.log',
    '/u01/app/oracle/product/10.2.0/oradata/dbcat/redolog1b.log') SIZE 10M,
    group 2 ('/u01/app/oracle/product/10.2.0/oradata/dbcat/redolog2a.log',
    '/u01/app/oracle/product/10.2.0/oradata/dbcat/redolog2b.log' ) SIZE 10M,
    group 3 ('/u01/app/oracle/product/10.2.0/oradata/dbcat/redolog3a.log',
    '/u01/app/oracle/product/10.2.0/oradata/dbcat/redolog3b.log' ) SIZE 10M
    DATAFILE '/u01/app/oracle/product/10.2.0/oradata/dbcat/system01.dbf' SIZE 400M
    autoextend on next 16M maxsize unlimited
    CHARACTER SET WE8ISO8859P1
    national character set utf8
    EXTENT MANAGEMENT LOCAL
    sysaux datafile '/u02/oradata/test/sysaux01.dbf' size 300M autoextend on next 16M maxsize unlimited
    undo tablespace ts_undo
    datafile '/u01/app/oracle/product/10.2.0/oradata/dbcat/undo01.dbf' size 50M autoextend on next 16M maxsize unlimited
    default temporary tablespace rman_temp
    tempfile '/u01/app/oracle/product/10.2.0/oradata/dbcat/temp01.dbf' size 50M autoextend on next 50M maxsize 300M
  • 4. Re: create a database in multiple directory
    936666 Newbie
    Currently Being Moderated
    Yes i mean to say different datafiles in different directory so that if i keep different data files the retrieval of data will be faster right that's why i asked.
    Say if i am having two tablespace in each tablespace i'm having 6 files and having 5 directory(1 OS and 4 remaining) can i have use 4 directory to save the data via datafile.

    Thanks!
  • 5. Re: create a database in multiple directory
    Fran Guru
    Currently Being Moderated
    Oracle_base and Oracle_home are diretories defined by OFA.
    Oracle_base directory is a top-level directory for Oracle software installations.
    Oracle_home is a directory where oracle database is installed.

    You can't set ORACLE_BASE/ORACLE_HOME in two different directories. You can create a database and set datafiles/controlfile/archivelogs in other paths.
  • 6. Re: create a database in multiple directory
    Samuel G. Cristobal Newbie
    Currently Being Moderated
    can i have use 4 directory to save the data via datafile.
    yes!
  • 7. Re: create a database in multiple directory
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are your directories on different physical drives? Is there a NAS/ SAN/ something else that is already balancing load across multiple drives in the server? Or do you really need to manually balance the load?

    Justin
  • 8. Re: create a database in multiple directory
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    933663 wrote:
    Yes i mean to say different datafiles in different directory so that if i keep different data files the retrieval of data will be faster right that's why i asked.
    Say if i am having two tablespace in each tablespace i'm having 6 files and having 5 directory(1 OS and 4 remaining) can i have use 4 directory to save the data via datafile.

    Thanks!
    You can create Datafiles on multi Directory
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles002.htm
  • 9. Re: create a database in multiple directory
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    >
    Yes i mean to say different datafiles in different directory so that if i keep different data files the retrieval of data will be faster right that's why i asked.
    >

    Not necessarily. Unless these directories are placed on different physical HDDs the speed will be same.

    I emphasize on physical, because if you place datafiles on different partitions of same HDD then access will be even slower because HDD head will have to constantly move here and there, and it (the seek) is a slowest sub-operation in HDD mechanics.

    >
    Say if i am having two tablespace in each tablespace i'm having 6 files and having 5 directory(1 OS and 4 remaining) can i have use 4 directory to save the data via datafile.
    >
    Yes. But as it said above, it has little sense.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points