{"id":758,"date":"2023-11-23T21:16:40","date_gmt":"2023-11-23T21:16:40","guid":{"rendered":"https:\/\/test.samplewebsitelink.com\/ordba\/?p=758"},"modified":"2023-11-23T21:16:43","modified_gmt":"2023-11-23T21:16:43","slug":"oracle-architecture","status":"publish","type":"post","link":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/","title":{"rendered":"ORACLE ARCHITECTURE"},"content":{"rendered":"\n
\"\"\/<\/figure>\n\n\n\n

INSTALLING ORACLE 11.2.0.4 ON X86 64 BIT LINUX RHEL – REQUIREMENTS? (FROM AND BY ORACLE)<\/p>\n\n\n\n

APPLIES TO:<\/p>\n\n\n\n

Oracle Database – Standard Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database – Enterprise Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Linux x86-64<\/p>\n\n\n\n

PURPOSE
This note explains the requirements that need to be met for a successful installation of Oracle 11gR2 RDBMS release 11.2.0.4 on Red Hat Enterprise Linux 7.0 (or higher 7.x version), 64-bit (x86-64). These guidelines apply to cluster (RAC) or standalone \/ single instances.<\/p>\n\n\n\n

It is NOT the purpose of this NOTE to repeat every “how-to” step that is presented in the 11gR2 Installation Guide manual. For example this NOTE does not include how to create the Linux OS account named “oracle”, nor does it cover how to set environment variables. Both are adequately covered in Chapter 2 “Oracle Database Pre-installation Requirements” of the 11gR2 Installation Guide manual.<\/p>\n\n\n\n

You can download Oracle 11.2.0.4 software from My Oracle Support (patch 13390677)<\/p>\n\n\n\n

SCOPE
This procedure is meant for those planning\/installing Oracle 11gR2 RDBMS release 11.2.0.4.0 (or higher 11.2.0.x version) on Red Hat Enterprise Linux 7.0 (or higher 7.x version) on the 64-bit (x86-64) platform. Since it is the expressed goal to keep Oracle Linux (OL) functionally IDENTICAL to RHEL, this NOTE is also completely applicable to 64-bit (x86-64) OL 7.0 (or higher 7.x version).<\/p>\n\n\n\n

This procedure is not meant for those planning\/installing Grid Infrastructure (GI) or any other Oracle products.<\/p>\n\n\n\n

DETAILS
Requirements for installing Oracle 11gR2 RDBMS release 11.2.0.4 64-bit on RHEL7 or OL7 64-bit (x86_64)<\/p>\n\n\n\n

I. Hardware:
===========
1. Minimum Hardware Requirements
a.) At least 1.0 GB (1024MB) of physical RAM
b.) Swap disk space proportional to the system’s physical memory as follows:<\/p>\n\n\n\n

RAM<\/p>\n\n\n\n

Swap Space<\/p>\n\n\n\n

Between 1 GB and 2 GB<\/p>\n\n\n\n

1.5 times the size of RAM<\/p>\n\n\n\n

Between 2 GB and 16 GB<\/p>\n\n\n\n

Equal to the size of RAM<\/p>\n\n\n\n

More than 16 GB<\/p>\n\n\n\n

16 GB<\/p>\n\n\n\n

NOTE: The above recommendations (from the 11.2 Database installation guide) are MINIMUM recommendations for installations. Further RAM and swap space may be required to tune\/improve RDBMS performance.<\/p>\n\n\n\n

c.) 1.0 GB (1024MB) of disk space (and less than 2TB of disk space) in the \/tmp directory.
d.) approximately 4.4 GB of local disk space for the database software.
e.) approximately 1.7 GB of disk space for a preconfigured database that uses file system storage (optional).<\/p>\n\n\n\n

2. Oracle Database is supported on ext2, ext3, and ext4 file systems (see Note:236826.1 for further information)<\/p>\n\n\n\n

II. Software:
============
1. As is specified in section 1.3.2 of the Oracle Database Installation Guide for 11gR2 on Linux (part number E24321-02), Oracle recommends that you install the Linux operating system with the default software packages (RPMs) and do not customize the RPMs during installation. For additional information on “default-RPMs”, please see Note 376183.1, “Defining a “default RPMs” installation of the RHEL OS” or Note 401167.1, “Defining a “default RPMs” installation of the Oracle Enterprise Linux (OEL) OS”.<\/p>\n\n\n\n

2. Red Hat Enterprise Linux Server 7.0 (or higher 7.x version) or Oracle Linux 7.0 (or higher 7.x version)<\/p>\n\n\n\n

NOTE: RHEL7 servers must be running Red Hat kernel 3.10.0-54.0.1.el7 (x86_64) or higher or 3.8.13-33.el7uek (x86_64) or higher with UEK kernel. OL7 servers must also be running kernel 3.8.13-33.el7uek (x86_64) or higher version. The product RHEL does not deliver UEK Kernel. Only in OL 7 UEK and RHCK Kernel is included.<\/p>\n\n\n\n

3. Required OS Components (per Release Notes, and Install Guide)<\/p>\n\n\n\n

a.) The exact version number details of this list are based upon 64-bit (x86_64) RHEL 7.0. When a higher “update” level is used, the RPM release numbers (such as 4.4.4-13) may be slightly different. Since updates of RHEL 7 are certified, this is fine so long as you are still using 64-bit Linux (x86_64) RHEL 7 RPMs.
b.) Some of the Install Guide requirements will already be present from the “default-RPMs” foundation of Linux that you started with:<\/p>\n\n\n\n

compat-libstdc++-33-3.2.3
binutils-2.23.52.0.1-12.el7.x86_64
compat-libcap1-1.10-3.el7.x86_64
gcc-4.8.2-3.el7.x86_64
gcc-c++-4.8.2-3.el7.x86_64
glibc-2.17-36.el7.i686
glibc-2.17-36.el7.x86_64
glibc-devel-2.17-36.el7.i686
glibc-devel-2.17-36.el7.x86_64
ksh
libaio-0.3.109-9.el7.i686
libaio-0.3.109-9.el7.x86_64
libaio-devel-0.3.109-9.el7.i686
libaio-devel-0.3.109-9.el7.x86_64
libgcc-4.8.2-3.el7.i686
libgcc-4.8.2-3.el7.x86_64
libstdc++-4.8.2-3.el7.i686
libstdc++-4.8.2-3.el7.x86_64
libstdc++-devel-4.8.2-3.el7.i686
libstdc++-devel-4.8.2-3.el7.x86_64
libXi-1.7.2-1.el7.i686
libXi-1.7.2-1.el7.x86_64
libXtst-1.2.2-1.el7.i686
libXtst-1.2.2-1.el7.x86_64
make-3.82-19.el7.x86_64
sysstat-10.1.5-1.el7.x86_64<\/p>\n\n\n\n

4. Additional Required OS Components (per the runInstaller OUI)
a.) intentionally blank<\/p>\n\n\n\n

5. Additional Required OS Components (per this NOTE)
a.) Please do not rush, skip, or minimize this critical step. This list is based upon a “default-RPMs” installation of 64-bit (x86_64) RHEL 6. Additional RPMs (beyond anything known to Oracle) may be needed if a “less-than-default-RPMs” installation of 64-bit (x86_64) RHEL Server 6 is performed. For more information, please refer to Note 376183.1, “Defining a “default RPMs” installation of the RHEL OS” or Note 401167.1, “Defining a “default RPMs” installation of the Oracle Enterprise Linux (OEL) OS”.
b.) Several RPMs will be required as prerequisites to those listed in section II.3.c:<\/p>\n\n\n\n

cpp-4.8.2-16.el7.x86_64
glibc-headers-2.17-55.el7.x86_64
mpfr-3.1.1-4.el7.x86_64<\/p>\n\n\n\n

6. Oracle Global Customer Support has noticed a recent trend with install problems that originates from installing too many RPMs. For example:
a.) installing your own JDK version (prior to execute the Oracle Software runInstaller) is not needed on Linux, and is not recommended on Linux. A pre-existing JDK often interferes with the correct JDK that the Linux Oracle Software runInstaller will place and use.
b.) installing more than the required version of the gcc \/ g++ RPMs often leads to accidentally using (aka enabling or activating) the incorrect one. If you have multiple RDBMS versions installed on the same Linux machine, then you will likely have to manage multiple versions of gcc \/g++ . For more information, please see Note 444084.1, “Multiple gcc \/ g++ Versions in Linux”<\/p>\n\n\n\n

7. All of the RPMs in section II. are on the Red Hat Enterprise Linux 7 64-bit (x86_64) distribution media.<\/p>\n\n\n\n

III. Environment:
================
1. Modify your kernel settings in \/etc\/sysctl.conf (RedHat) as follows. If the current value for any parameter is higher than the value listed in this table, do not change the value of that parameter. Range values (such as net.ipv4.ip_local_port_range) must match exactly.<\/p>\n\n\n\n

kernel.shmall = physical RAM size \/ pagesize For most systems, this will be the value 2097152. See Note 301830.1 for more information.
kernel.shmmax = 1\/2 of physical RAM. This would be the value 2147483648 for a system with 4GB of physical RAM. See Note:567506.1 for more information.
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 6815744 for 13312 processes)
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576<\/p>\n\n\n\n

2. To activate these new settings into the running kernel space, run the “sysctl -p” command as root.<\/p>\n\n\n\n

3. Set Shell Limits for the oracle User. Assuming that the “oracle” Unix user will perform the installation, do the following:<\/p>\n\n\n\n

a.) Add the following settings to \/etc\/security\/limits.conf<\/p>\n\n\n\n

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240<\/p>\n\n\n\n

b.) Verify the latest version of PAM is loaded, then add or edit the following line in the \/etc\/pam.d\/login file, if it does not already exist:<\/p>\n\n\n\n

session required pam_limits.so<\/p>\n\n\n\n

c.) Verify the current ulimits, and raise if needed. This can be done many ways…adding the following lines to \/etc\/profile is the recommended method:<\/p>\n\n\n\n

if [ $USER = “oracle” ]; then
if [ $SHELL = “\/bin\/ksh” ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi<\/p>\n\n\n\n

4. The gcc-4.4.4 and gcc-c++-4.4.4 RPM items above will ensure that the correct gcc \/ g++ versions are installed. It is also required that you ensure that these correct gcc \/ g++ versions are active, and in-use. Ensure that the commands “gcc –version” and “g++ –version” each return “4.8.2”.<\/p>\n\n\n\n

5. The hostname command should return the fully qualified hostname as shown below:<\/p>\n\n\n\n

% hostname
hostname.domainname<\/p>\n\n\n\n

6. If any Java packages are installed on the system, unset the Java environment variables, for example JAVA_HOME.<\/p>\n\n\n\n

7. The oracle account that is used to install Oracle 11.2.0.4 should not have the Oracle install related variables set by default. For example setting ORACLE_HOME, PATH, LD_LIBRARY_PATH to include Oracle binaries in .profile, .login file and \/etc\/profile.d should be completely avoided.
a.) Setting $ORACLE_BASE (not $ORACLE_HOME) is recommended, since it eases a few prompts in the OUI runInstaller tool.
b.) Following the successful install, it is recommended to set $ORACLE_HOME, and to set $PATH to include $ORACLE_HOME\/bin at the beginning of the $PATH string.<\/p>\n\n\n\n

8. By default, RHEL 7 x86_64 Linux is installed with SELinux as “enforcing”. This is fine for the 11gR2 installation process. However, to subsequently run “sqlplus”, switch SELinux to the “Permissive” mode. See NOTE 454196.1, “.\/sqlplus: error on libnnz11.so: cannot restore segment prot after reloc” for more details.<\/p>\n\n\n\n

UPDATE: Internal testing suggests that there is no problem running “sqlplus” with SELinux in “enforcing” mode on RHEL7\/OL7. The problem only affects RHEL5\/OL5.<\/p>\n\n\n\n

9. Log in as Oracle user and start the installation as follows:<\/p>\n\n\n\n

$ .\/runInstaller -ignorePrereq<\/p>\n\n\n\n

a.) It is best practice not to use any form of “su” to start the runInstaller, in order to avoid potential display-related problems.
b.) When performing the 11.2.0.4 installation, make sure to use the “runInstaller” version that comes with 11.2.0.4 software.
c.) When performing any subsequent 11.2.0.x patchset, make sure to use the “runInstaller” version that comes with the patchset.<\/p>\n\n\n\n

Known Issue :<\/p>\n\n\n\n

01) The installer needs to be launched with “-ignorePrereq” option due to unpublished bug 19947777. This issue occurs since Oracle Linux 7 was not released when Oracle database 11.2.0.4 was made available and hence was not certified. However, Oracle 11.2.0.4 is now certified on OL7. Refer Note 1962046.1 for details.<\/p>\n\n\n\n

02) Compilation fails for target ‘relink_exe’ fails with “undefined reference to symbol<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

ORACLE ARCHITECTURE<\/p>\n\n\n

[accordian class=”” id=””]<\/p>\n

[toggle title=”Raid” open=”no”]<\/p>\n

Bookmark Fixed font Go to End<\/p>\n

Doc ID: Note:38281.1
Subject: RAID and Oracle – 20 Common Questions and Answers
Type: FAQ
Status: PUBLISHED
Content Type: TEXT\/PLAIN
Creation Date: 26-FEB-1998
Last Revision Date: 26-MAR-2000<\/p>\n

RAID and Oracle – 20 Common Questions and Answers
==========================================<\/p>\n

1. What is RAID?<\/p>\n

RAID is an acronym for Redundant Array of Independent Disks. A RAID system consists of an enclosure containing a number of disk volumes, connected to each other and to one or more computers by a fast interconnect. Six levels of RAID are defined: RAID-0 simply consists
of several disks, and RAID-1 is a mirrored set of two or more disks.The only other widely-used level is RAID-5, which is the subject of this article. Other RAID levels exist, but tend to be vendor-specific, and there is no generally accepted standard for features included.<\/p>\n

2. What platforms is RAID available for?<\/p>\n

Third-party vendors supply RAID systems for most of the popular UNIX platforms and for Windows NT. Hardware vendors often provide their own RAID option.<\/p>\n

3. What does RAID do?<\/p>\n

The main feature of RAID-5 is prevention of data loss. If a disk is lost because of a head crash, for example, the contents of that disk can be reconstituted using the information stored on other disks in
the array. In RAID-5, redundancy is provided by error-correcting codes (ECCs) with parity information (to check on data integrity) stored with the data, thus striped across several physical disks. (The intervening RAID levels between 1 and 5 work in a similar way,
but with differences in the way the ECCs are stored.)<\/p>\n

4. What are the performance implications of using RAID-5?<\/p>\n

Depending on the application, performance may be better or worse. The basic principle of RAID-5 is that files are not stored on a single disk, but are divided into sections, which are stored on a number of different disk drives. This means that the effective disk spindle speed is increased, which makes reads faster. However, the involvment of more disks and the more complex nature of a write operation means that writes will be slower. So applications where the majority of transactions are reads are likely to give better response times, whereas write-intensive applications may show worse
performance.<\/p>\n

Only hardware-based striping should be used on Windows NT. Software striping, from Disk Administrator, gives very poor performance.<\/p>\n

5. How does RAID-5 differ from RAID-1?<\/p>\n

RAID-1 (mirroring) is a strategy that aims to prevent downtime due to loss of a disk, whereas RAID-5 in effect divides a file into chunks and places each on a separate disk. RAID-1 maintains a copy of the contents of a disk on another disk, referred to a mirrored disk. Writes to a mirrored disk may be a little slower as more than one physical disk is involved, but reads should be faster as there is a choice of disks (and hence head positions) to seek the required location.<\/p>\n

6. How do I decide between RAID-5 and RAID-1?<\/p>\n

RAID-1 is indicated for systems where complete redundancy of data is considered essential and disk space is not an issue. RAID-1 may not be practical if disk space is not plentiful. On a system where uptime must be maximized, Oracle recommends mirroring at least the control files, and preferably the redo log files.<\/p>\n

RAID-5 is indicated in situations where avoiding downtime due to disk problems is important or when better read performance is needed and mirroring is not in use.<\/p>\n

6a. Do all drives used for RAID-5 have to be identical?<\/p>\n

Most UNIX systems allow a failed disk to be replaced with one of the same size or larger. This is highly implementation-specific, so the vendor should be consulted.<\/p>\n

7. Is RAID-5 enough to provide full fault-tolerance?<\/p>\n

No. A truly fault-tolerant system will need to have a separate power supply for each disk to allow for swapping of one disk without having to power down the others in the array. A fully fault-tolerant system has to be purpose-designed.<\/p>\n

8. What is hot swapping?<\/p>\n

This refers to the ability to replace a failed drive without having to power down the whole disk array, and is now considered an essential feature of RAID-5. An extension of this is to have a hot standby disk that eliminates the time taken to swap a replacement disk in – it is already present in the disk array, but not used unless there is a problem.<\/p>\n

9. What is a logical drive, and how does it relate to a physical drive?<\/p>\n

A logical drive is a virtual disk constructed from one or (usually) more than one physical disks. It is the RAID-5 equivalent of a UNIX logical volume; the latter is a software device, whereas RAID-5 uses additional hardware.<\/p>\n

10. What are the disadvantages of RAID-5?<\/p>\n

The need to tune an application via placement of ‘hot’ (i.e. heavily accessed) files on different disks is reduced by using RAID-5. However, if this is still desired, it is less easy to accomplish as the file has already been divided up and distributed across disk drives. Some vendors, for example EMC, allow striping in their RAID systems, but this generally has to be set up by the vendor. There is an additional consideration for Oracle, in that if a database file needs recovery several physical disks may be involved in the case of a striped file, whereas only one would be involved in the case of a normal file. This is a side-effect of the capability of RAID-5 to withstand the loss of a single disk.<\/p>\n

11. What variables can affect the performance of a RAID-5 device?<\/p>\n

The major ones are:
– Access speed of constituent disks
– Capacity of internal and external buses
– Number of buses
– Size of caches
– Number of caches
– The Nature of the algorithms used for determining how reads and writes
are done.<\/p>\n

12. What types of files are suitable for placement on RAID-5 devices?<\/p>\n

Placement of data files on RAID-5 devices is likely to give the best performance benefits, as these are usually accessed randomly. More benefits will be seen in situations where reads predominate over writes. Rollback segments and redo logs are accessed sequentially (usually for writes) and therefore are not suitable candidates for being placed on a RAID-5 device. Also, datafiles belonging to temporary tablespaces are not suitable for placement on a RAID-5 device.<\/p>\n

Another reason redo logs should not be placed on RAID-5 devices is related to the type of caching (if any) being done by the RAID system. Given the critical nature of the contents of the redo logs, catastrophic loss of data could ensue if the contents of the cache were not written to disk, e.g. because of a power failure, when Oracle was notified they had been written. This is particularly true of write-back caching, where the write is regarded as having been written to disk when it has only been written to the cache. Write-through caching, where the write is only regarded as having completed when it has reached the disk, is much safer, but still not recommended for redo logs for the reason mentioned earlier.<\/p>\n

13. What about using multiple DBWRs as an alternative to RAID-5?<\/p>\n

Using at least as many Database Writer processes (DBWR) as you have database disks will maximize synchronous write capability, by avoiding one disk having to wait for a DBWR process, which is busy writing to another disk. However, this is not an alternative to RAID-5, because it improves write efficiency. And RAID-5 usually results in writes being slower.<\/p>\n

14. What about other strategies?<\/p>\n

Two strategies that can be used as alternatives to RAID-5, or in addition to it, are Asynchronous I\/O (aio) and List I\/O (listio).<\/p>\n

15. What is Asynchronous I\/O?<\/p>\n

Asynchronous I\/O (aio) is a means by which a process can proceed with the next operation without having to wait for a write to complete. For example, after starting a write operation, the DBWR process blocks (waits) until the write has been completed. If aio is used, DBWR can continue almost straight away. aio is activated by the relevant “init.ora” parameter, which will either be ASYNC_WRITE or USE_ASYNC_IO, depending on the platform. If aio is used, there is no need to have multiple DBWRs.<\/p>\n

Asynchronous I\/O is optional on many UNIX platforms. It is used by default on Windows NT.<\/p>\n

16. What are the advantages and disadvantages of aio?<\/p>\n

In the above DBWR example, the idle time is eliminated, resulting in more efficient DBWR operation. However, aio availability and configuration is very platform-dependent; while many UNIX versions support it, some do not. Raw devices must be used to store the files so the use of aio adds some complexity to the system administrator’s job. Also, the applications must be able to utilize aio.<\/p>\n

17. What is List I\/O?<\/p>\n

List I\/O is a feature found on many SVR4 UNIX variants. As the name implies, it allows a number of I\/O requests to be batched into a “list”, which is then read or written in a single operation. It does not exist on Windows NT.<\/p>\n

18. What are its advantages and disadvantages?<\/p>\n

I\/O should be much more efficient when done in this manner. You also get the benefits of aio, so this is not needed if listio is available. However, listio is only available on some UNIX systems, and as in the case of aio, the system administrator needs to set it up and make sure key applications are configured to use it.<\/p>\n

19. How do Logical Volume Managers (LVMs) affect use of RAID-5?<\/p>\n

Many UNIX vendors now include support for an LVM in their standard product. Under AIX, all filesystems must reside on logical volumes. Performance of a UNIX system using logical volumes can be very good compared with standard UNIX filesystems, particularly if the stripe size (size the chunks files are divided into) is small. Performance will not be as good as RAID-5 given that the latter uses dedicated hardware with fast interconnects. In practice, many small and medium-sized systems will find that the use of logical volumes (with a suitable stripe size for the type of application) performs just as good as RAID-5. This particularly applies to systems where there is no I\/O problem. Larger systems, though, are more likely to need the extra performance benefits of RAID-5.<\/p>\n

20. How can I tell if my strategy to improve I\/O performance is working?<\/p>\n

At the UNIX level, there are several commands that can tell you if a disk device is contributing to I\/O problems. On SVR4, use the ‘sar’ command with the appropriate flag, usually ‘-d’. On BSD, use the ‘iostat’ command. You are looking for disks whose request queue average length is short, ideally zero. Disks with more than a few entries in the queue may need attention. Also check the percent busy value, as a disk might have a short average queue length yet be very active.
On Windows NT, the Performance Monitor allows I\/O statistics to be monitored easily and in a graphical manner.
It is essential to obtain baseline figures for normal system operation, so you will know when a performance problem develops and when your corrective action has restored (or improved upon) the<\/p>\n

[\/toggle]
[toggle title=”Norms” open=”no”]<\/p>\n

Normalization and De-normalization<\/p>\n

Normalization means decomposition of a relational schema that has many attributes into several schemas with fewer attributes. Careless decomposition leads to bad design and high cost access of data.
Normalization is the process of putting things right, making them normal. In Latin the word ‘norma’ means the square used by the carpenter, used to assure a right angle. In geometry, when a line is in at a right angle to another line, it is said “normal” to it. In RDBMS it has a specific mathematical meaning having to do with separating elements of data into affinity groups and defining the normal or right relationship between them.<\/p>\n

A key is an attribute in the entity (Table), which identifies the entire row. Keys are different types. They are intelligent keys and non-intelligent keys. An intelligent key is based on data values such as date, a last name or a combination of values. A non-intelligent key is completely arbitrary, having no function or meaning other than identifying the row uniquely. The relations are established between the entities between \/ among these keys.<\/p>\n

Relationships<\/p>\n

The relations can be four types:
(1) One to One<\/p>\n

In Table A one row is associated with one row in Table B and one row in Table B is associated with one row in Table A. This relationship is addressed to as one-to-one relationship.<\/p>\n

If the two tables are related to at one to one level and the values in either table or entity are repetitive and redundant, these tables they are called in one-to-one relationship. These relations are very rare for the simple reason such relation between the entities can be reduced to a single entity instead of having two entities. This one-to-one relationship can be expressed simply as one-to-many relationship using primary and foreign key relations as seen in Oracle Demo Tables DEPT and EMP tables where DEPTNO in the DEPT table is the parent of deptno column in EMP table.<\/p>\n

(2) One to Many<\/p>\n

If in table A one row is associated with more than one row in Table B the relationship between these two tables is called one-to-many relationship.<\/p>\n

This relationship can be seen between DEPT and EMP tables where one DEPT row is related to many rows in EMP table.<\/p>\n

(3) Many to One<\/p>\n

If in Table A more than one row is associated with one row in Table B, this relationship is called one to many relationship.<\/p>\n

This relationship can be seen between DEPT and EMP tables given in Oracle demo Scott schema. Many employees of department number 10 in Table EMP are related to one row in DEPT Table, i.e., department number 10.<\/p>\n

(4) Many to Many<\/p>\n

If one row in Table A is related to many rows in Table B and a row in Table B is related to many rows in Table A the relation ship between these two tables is many-to-many.<\/p>\n

Say that we have two tables, Customer and Account. One customer may have more than one account, say one checking account, one savings account, one credit card account and one loan account. This is one-to-many relationship. Now, more than one customer can own an account. Those customers that have joint accounts and individual accounts are also seen in the customer table as well as account table. In such circumstances the relation ship between the Customer table and Account table is many-to-many. In such circumstances neither account number nor the customer name can be unique in these two tables. Hence we need another table like, Account Type and Customer and Account Tables have ‘account type’ attribute in them these two tables can be related to basing on a composite key like customer name and account type and account number and account type. Essentially it is to be understood that we need three tables or entities to establish many-to-many relationship.<\/p>\n

First Normal Form<\/p>\n

Every table should have a primary key, and a set of repeating groups should appear in its own table. If this is satisfied, the first normal form is satisfied.<\/p>\n

Second Normal Form<\/p>\n

When a column value is dependent on the value of one column in another table, the tables are in parent and child relationship eliminating redundancy and this is the second normal form.<\/p>\n


Boyce-Codd Normal Form<\/p>\n

(This is also treated as 2nd Normal Form)<\/p>\n


A design is BCNF if each member of the set of relational schemas that constitute the design is BCNF.<\/p>\n

The following example taken from Database System Concepts explains the BCNF very clearly.<\/p>\n

Customer-schema = (customer-name, customer-street, customer-city)
Customer-name -> customer-street customer-city
Brach-schema = (branch-name, assets, branch-city)
Branch-name -> assets branch-city
Loan-info-schema = (branch-name, customer-name, loan-number, amount)
Loan-number -> amount branch-name<\/p>\n

Candidate key for customer-schema is customer-name, for branch-schema is branch-name and for loan-info-schema is loan-number. The functional dependency of the schema attributes is non-trivial in the case of customer-schema and branch-schema. But it is not so in the case of loan-info-schema. It is not in BCNF. Loan number is not a super key or a candidate key as the names may be repeated and if the data is accessed basing on the name of the loan account there cannot be loss-less join. it is due to the fact that a single loan is made to two people. But the functional dependency is nontrivial. Hence loan-info-schema is to be decomposed into two schemas.<\/p>\n

Loan-schema = (branch-name, loan-number, amount)
Barrower-schema = (customer-name, loan-number)<\/p>\n

This decomposition of the loan-info-schema results in loss-less join of the data and now can be said in BCNF.<\/p>\n

Third Normal Form<\/p>\n

Third Normal Forms Requires no non-key column depend on another non-key column.
When all the columns in a table describe and depend upon the primary key, the table is said to have satisfied third normal form.<\/p>\n

Fourth Normal Form<\/p>\n

Fourth Normal form forbids independent one-to-may relationships between primary key columns and non-key columns.<\/p>\n

Fifth Normal Form<\/p>\n

Fifth normal form breaks tables into the smallest possible pieces in order to eliminate all redundancy within a table.
If the design is in 3rd Normal Form the design achieves many of the set goals of normalization and functional advantages of relational database systems (RDBMS)<\/p>\n

De-normalization:<\/p>\n

It is the opposite of Normalization of entities or tables or schemas. Normalization is helps normalize the data with relational designs. This results in joining many tables together to access data as required by an application. As the number of tables increase in generating the data the usage of resource goes up and it impacts the performance of the application. Hence while designing the database, the purpose of the database is also to be understood and the designs are to be made to meet such requirements. For instance, the application is to collect data from many sources and the data so collected is to be processed, made available to the end users of data who have different purposes. In such circumstances we cannot stick to a particular design. A single database does not serve the purpose and we need multiple databases. A database that is writes intensive means a collecting database or a data-staging database (OLTP database), a database that is that processes the data (OLAP- online analytical processing, database). This database has more updates and deletes and inserts and contains all the business logic requirements and processes. This is nothing but a stage for processing the data collected from various sources. Then comes the database that hosts the processed data that is used by end users. The End-Users access the database through reports. This type of databases may be called data marts, data warehouses and decision supporting systems, depending upon the requirements.<\/p>\n

The data warehouses, data marts and decision supporting systems hold large amounts of data and the tables which are relationally joined may takes all the time on earth to access the data required by the users. Hence the design of the databases cannot be insisted upon to be in 2nd, 3rd normal forms and hold up the performance of the database. Then there is requirement of de-normalization of the design. The tables may hold redundant data in them. Still the performance of the database is very high as we need not to join multiple tables.<\/p>\n

The design of the databases is dependent on<\/p>\n

(1) Establishing relations,
(2) Data accuracy and integrity,
(3) Easy accessibility and,
(4) Performance<\/p>\n

Say an application is to collect the data from various sources and then process the data to meet the business logics and make it available to for access by end users of various types.<\/p>\n

In this case, multiple database designs are necessary. A staging database to collect the data from various data sources. A processing database to process the data and a warehouse or mart or decision support system database for the end users.<\/p>\n

De-normalization is to be considered where performance is important. If the normalization is followed where staging the data, the same design with no constraints (referential) and additional summary tables may be used for the OLAP and then the same design for DWH, DSS and DM.<\/p>\n

[\/toggle]
[toggle title=”Database Structural ER Diagram” open=”no”]<\/p>\n

(defines relations between database, instance, schemas and other structures of database)
Srinivasacharyulu Maddali<\/p>\n

The relations can be defined as under:<\/p>\n

[\/toggle]
[\/accordian]<\/p>\n","protected":false},"excerpt":{"rendered":"

INSTALLING ORACLE 11.2.0.4 ON X86 64 BIT LINUX RHEL – REQUIREMENTS? (FROM AND BY ORACLE) APPLIES TO: Oracle Database – Standard Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Oracle Database – Enterprise Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Linux x86-64 PURPOSEThis note explains the requirements that need to be met for a successful installation …<\/p>\n

ORACLE ARCHITECTURE<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":504,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"footnotes":""},"categories":[10],"tags":[],"class_list":["post-758","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle"],"yoast_head":"\nORACLE ARCHITECTURE - Ordba<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ORACLE ARCHITECTURE - Ordba\" \/>\n<meta property=\"og:description\" content=\"INSTALLING ORACLE 11.2.0.4 ON X86 64 BIT LINUX RHEL – REQUIREMENTS? (FROM AND BY ORACLE) APPLIES TO: Oracle Database – Standard Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Oracle Database – Enterprise Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Linux x86-64 PURPOSEThis note explains the requirements that need to be met for a successful installation … ORACLE ARCHITECTURE Read More »\" \/>\n<meta property=\"og:url\" content=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/\" \/>\n<meta property=\"og:site_name\" content=\"Ordba\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-23T21:16:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-23T21:16:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/test.samplewebsitelink.com\/ordba\/wp-content\/uploads\/2023\/11\/article-3-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"700\" \/>\n\t<meta property=\"og:image:height\" content=\"700\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"ordba\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ordba\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"23 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/\",\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/\",\"name\":\"ORACLE ARCHITECTURE - Ordba\",\"isPartOf\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#website\"},\"datePublished\":\"2023-11-23T21:16:40+00:00\",\"dateModified\":\"2023-11-23T21:16:43+00:00\",\"author\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f\"},\"breadcrumb\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/test.samplewebsitelink.com\/ordba\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ORACLE ARCHITECTURE\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#website\",\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/\",\"name\":\"Ordba\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/test.samplewebsitelink.com\/ordba\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f\",\"name\":\"ordba\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g\",\"caption\":\"ordba\"},\"sameAs\":[\"https:\/\/test.samplewebsitelink.com\/ordba\"],\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/author\/ordba\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"ORACLE ARCHITECTURE - Ordba","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/","og_locale":"en_US","og_type":"article","og_title":"ORACLE ARCHITECTURE - Ordba","og_description":"INSTALLING ORACLE 11.2.0.4 ON X86 64 BIT LINUX RHEL – REQUIREMENTS? (FROM AND BY ORACLE) APPLIES TO: Oracle Database – Standard Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Oracle Database – Enterprise Edition – Version 11.2.0.4 to 11.2.0.4 [Release 11.2]Linux x86-64 PURPOSEThis note explains the requirements that need to be met for a successful installation … ORACLE ARCHITECTURE Read More »","og_url":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/","og_site_name":"Ordba","article_published_time":"2023-11-23T21:16:40+00:00","article_modified_time":"2023-11-23T21:16:43+00:00","og_image":[{"width":700,"height":700,"url":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-content\/uploads\/2023\/11\/article-3-1.jpg","type":"image\/jpeg"}],"author":"ordba","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ordba","Est. reading time":"23 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/","url":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/","name":"ORACLE ARCHITECTURE - Ordba","isPartOf":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#website"},"datePublished":"2023-11-23T21:16:40+00:00","dateModified":"2023-11-23T21:16:43+00:00","author":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f"},"breadcrumb":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/23\/oracle-architecture\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/test.samplewebsitelink.com\/ordba\/"},{"@type":"ListItem","position":2,"name":"ORACLE ARCHITECTURE"}]},{"@type":"WebSite","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#website","url":"https:\/\/test.samplewebsitelink.com\/ordba\/","name":"Ordba","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/test.samplewebsitelink.com\/ordba\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f","name":"ordba","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g","caption":"ordba"},"sameAs":["https:\/\/test.samplewebsitelink.com\/ordba"],"url":"https:\/\/test.samplewebsitelink.com\/ordba\/author\/ordba\/"}]}},"_links":{"self":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/758","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/comments?post=758"}],"version-history":[{"count":1,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/758\/revisions"}],"predecessor-version":[{"id":759,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/758\/revisions\/759"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/media\/504"}],"wp:attachment":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/media?parent=758"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/categories?post=758"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/tags?post=758"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}