9 Replies Latest reply: Dec 3, 2012 11:16 AM by Mark Malakanov (user11181920) RSS

    create a database in multiple directory

    936666
      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
          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
            JustinCave
            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
              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
                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
                  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
                    can i have use 4 directory to save the data via datafile.
                    yes!
                    • 7. Re: create a database in multiple directory
                      JustinCave
                      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
                        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)
                          >
                          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.