Brian Cryer

  

Oracle Parallel Server How To...

Create a new partition

A new partition can be used as a ‘data-file’, either for a new tablespace or to extend an existing tablespace. Adding a new partition to the Oracle database should be regarded as a maintenance activity as it requires the Oracle database to be shutdown. Directions to create a new data file:

  1. Shutdown both (or all) Oracle instances.

    On both instances:

    Svrmgr30
    Connect internal
    shutdown

  2. Create a new partition. This should be performed on ONE server only. Run disk-administrator. Create a new partition and unassign the drive letter.
  3. Reboot the second server. This allows the second server to pick up the changed disk configuration information. NT can get confused if a disk changes when it has not administered the change.
  4. Unassign the drive letter on the second server. Do this by running disk administrator on the second server.

    Note: The disk numbers for the shared disks may appear different between the two servers. This is not a problem.

  5. On the first server add the partition information to ‘oralink1.tbl’. Edit the file ‘C:\Orant\Ops\Oralink1.tbl’ with notepad. Add an entry to the end of the file. This entry consists of a logical name and the description of the partition. The logical name will be used by Oracle to refer to the partition. If the partition is to be used to extend an existing table space then the logical name should be the same as the other logical name(s) for the tablespace, but with the appended number incremented.

    Note: There should be a carriage return after the last line in this file, otherwise the ‘setlinks’ program will fail to process the last line.

  6. Run setlinks. Start a command session and enter:

    cd Orant\Ops
    setlinks /f:oralink1.tbl

    To check that the link has been created:

    Setlinks /d

    If the link does not exist then check the Oralink1.tbl file.

  7. Edit the file ‘Oralink1.tbl’ file on the second instance. Repeat the process of editing the ‘Oralink1.tbl’ file on the second instance. Be aware that whilst partition numbers will be the same for both instances, the disk number may be different.
  8. Run ‘setlinks’ on the second instance. Repeat the process of running ‘setlinks’ as described above, but on the second instance.
  9. Start up both Oracle instances.
  10. Create a new tablespace or expand an existing one. This should only to be performed on one instance. Use svrmgr30 or ‘SQL-worksheet’. To create a new tablespace, eg:

    Create tablespace <tablespace-name> Datafile '\\.\<logical-name>' size <size> M;

    For example:

    Create tablespace Tools
    Datafile ‘\\.\OPS_SYS_TOOLS01’ 100M;

    To add the new partition to an existing tablespace:

    Alter tablespace <tablespace-name> Add datafile '\\.\<logical-name>' size <size> M;

    For example:

    Alter tablespace Tools Add datafile '\\.\OPS_SYS_TOOLS02' size 100M;

Note:

  • Not all of the space in the raw partition is available for use. Strictly, the data file size should be at least 2 blocks (eg. 2 x 8K ) less than the partition size. However, it may be easier to simply define the datafile as being 1M less than the raw partition size.
  • If the datafile is added to a table space using Oracle storage-manager, be aware that this tool seems to impose a limit of 4032MB on the size of the datafile.
  • The maximum size of any file is 4,194,303 blocks. With a block size of 2K this is 8191.998MB, with a block size of 4K this works out as 16383.99MB, and for a block size of 8K this works out at 32767.99MB.

These notes have been tested with Oracle Parallel Server 8.0.5 running under NT 4.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.