Oracle Database 12 on Cloud

1-click AWS Deployment 1-click Azure Deployment 1-click Google Deployment

Overview

Oracle Server:

A server is a collection of database units and it provides comprehensive integrated approach to info management
It consists of an “Instance & Database ”
Oracle Instance:

It means to access an oracle database
It always open one & only one database
It consists of two types :

– Memory Structure

– Back Ground Process

Memory Structure:

System Global Area (SGA)
Program Global Area (PGA)
(I)System Global Area

Once The Instance is started it allocated memory to SGA
It is a basic component of oracle instance its size depends on RAM
The oracle 10g parameter of SGA and PGA sga_target , sga_max_size , pga_aggregate_target
It consists of

-Shared Pool

– Database Buffer Cache

– Redolog Buffer Cache

– Large pool

– Stream pool

– Java pool

here we can go to see each components in details

(1 )Shared Pool:

– It’s parameter is shared_pool_size

– It’s consists of Library cache and Data Dictionary Cache

I) Library Cache:

– It stores information about recently used sql and Pl-sql Statements

– Here it checks some of the following

1)Semantic checking – it checks the privilege issued commands by user

2)Syntax checking – it checks the syntax of user issued commands

3)Soft parse – Already Executed Sql statements command

4)Hard parse – New Sql Statements

II)Data Dictionary Cache:

– It stores the collection of most recently used definitions in the databases includes dbfiles,tables,indexes ,columns etc

– It has the information about database and its read only

(2)Database Buffer Cache:

– It stores copies of data block that have been retrieved from the database datafiles

– It’s parameter is

show parameter db_block_size =8kb is default size,
show parameter db_cache_size
(3)Redo log Buffer Cache or Recovery Mechanism:

– It’s maintains records of modification database blocks

– Primary purpose is recovery

Show parameter log_file
(4)Large Pool:

– Parallel execution allocates buffers out of the large pool only when sga_traget

– It works to release the burden the shared pool

show parameter parallel_automatic_tuning
(5) Java Pool:

– Parsing requirement of java commands

– Requires installation of java based projects

Show parameter java_pool_size
(6)Stream Pool:

– It’s Cache “Oracle Stream” Objects

– Oracle Stream means to allow data multiplication between on oracle databases or oracle and non-oracle databases,It can be used for Replication,Message Queuing,Loading data into a Data Warehouse,Event Notification,Data Protection Automatic Shared Memory Management (ASMM ) was introduced in Oracle 10g.

its taking care by oracle and allocates SGA components size ASMM taking care of

1)Shared pool

2)Library cache

3)Database buffer cache

4)Large pool

5)Java Pool

6)Stream Pool

(II)Program Global Area

– It reserved memory for each user process connecting to an oracle database

– Allocates memory when a process is created

– De-allocates memory when a process is terminated

Process Structure :

1)USER PROCESS:

– A program that request interaction with oracle server

– It’s must first establish a connection

– It does not interact directly with oracle server

2)SERVER PROCESS:

– It directly interacts with oracle server

– It can be a dedicated or shared server

– It always responds to user requests

3)BACKGROUND PROCESS:

– It enforces the relationship between memory structure and database

– To view all background process

!ps -ef | grep databasename
It has some of components are

1)DBWR

2)LGWR

3)SMON

4)PMON

5)CHPKT

Let us we can see each components are

1)DBWR:

– Time Out Error

– Tablespace offline

– Tablespace Read only

– Tablespace Drop or Truncate in above situations, Data ‘ll be flushed from database buffer cache into data files

2)LGWR:

– At commit

– Every 3 sec

– When there is full 1MB reached

– Redolog Buffer reached one-third full

– Before DBWR writes In above situations, redolog writes through LGWR from redo log buffer

3)SMON:

– Monitoring the system is called system monitor

– Instance recovery

– Rolls forward changes into redologs

– Open database for user access

– Rolls back uncommitted transactions

4)PMON:

– Taking Care of All background Process

– Cleaned up after failed process

– Rolling Back

5)CHKPT:

– Updating the control file with checkpoint information.

– It’s a process of writing by DBWR ,all modified buffers in SGA cache into Data files

Alter system checkpoint;
Database :

The Database is a collection of data which contains data files ,control files ,redolog files

1) Data file:

– It is a portion of an oracle database ,it stores the data which includes user data and undo data

– It’s extension “.dbf”

– The default location is ” $ORACLE_BASE/oradata”

– To view the location in database use this command

Select name from V$datafile;
2) Control file:

– It’s heart of the database

– It holds the information of data file ,redo log file locations and backup information starting time and ending time

– It’s extension “.ctl”

Show parameter control_files
– By default oracle has copied the control files into flash_recovery_area

3) Redo log File:

– It’s part of an oracle database

– It’s the main purpose is to recover the database

– It’s extension “.log”

– When transaction is committed that details in redo log buffer are written to a redo log file

select * from V$log; or Select * from V$logfile ;
4) Archive log

– It’s a group of redo log files to one or more offline destinations, known collectively as the archived redo log

– Its Default location is Flash_recovery_area

– Must enable archive log mode in the database then only ll be saved on archive log folder other wise the log buffer overwrites on redo log files through Lgwr.

INSTALLING ORACLE DATABASE 11G

The Oracle Universal Installer steps for installing the Oracle Data base 11g release software are similar to the steps for the Oracle 10g release.There are a few changes,however, which we’ll high light when we show the installation steps in this section. You use the run Installer executable to invoke the GUI-based Ora cle Universal Installer.If you’ve down loaded the server soft ware from the Oracle web site,you must first uncompress the down loaded file.This will create a directory named data base, under which you’ll find the run Installer script.Start the installation process by moving to the database directory and typing the following:

$ ./runInstaller
If you’re installing from a DVD, invoke the installer by supplying the full path for the database directory on the DVD:

$ /<directory_path>/runInstaller
If you pass the minimal operating system requirements,the Oracle Universal Installer will open.Once the Oracle Uni versal Installer GUI shows up,the following are the steps in the installation process:

1.On the Select Installation Method page,you can select either Basic Inst- allation or Advanced Installation. Select Advanced Installation,and click Next.

2..Select Installation Type.You’re given three choices—Enter prise Edition, Standard Edition,and Custom.Choose Enterprise Edition, and click Next.
3.On the Install Location page,specify the path for the Oracle base and Oracle home locations, which is where the Oracle Uni versal Installer will install the data base files.Click Next.
4.On the Product-Specific Prerequisite Checks page, the Oracle Universal Installer will verify that your environment meets the minimum requi rements for installing the various products you want to install.hese checks include the kernel parameters,swap space requirements,validation of the Oracle base location,and network configuration requirements.It’s a good idea to go ahead and fix any warnings produced by the Oracle Universal Installer at this stage, say by up dating the kernel on a Linux system,although you can get away with not doing so in most cases since the Oracle Universal Installer offers you the choice of continuing despite a warning.Once you pass the requirement checks,click Next.
5.Select Configuration Option.You can choose to create a data base,configure ASM, or just install the Oracle 11g binaries.For the last option, choose Install Software Only, and click Next.
6.You’ll see the Privileged Operating System Groups page next, as shown in Figure This step is new in Oracle Data base 11g.In addition to the sys dba and sysoper privileges you’re familiar with, Oracle now recommends you create the new system privilege called sysasm for enabling the management of ASM. Oracle also recommends you create a new Unix/Linux group now, called osasm, for ASM administrators.

The Privileged Operating System Groups page

7.On the Summary page,the Oracle Universal Installer summarizes the installation,including the names of all the components it will install. Click Next after reviewing the summary.
8.On the Install page, you’ll see the progress of the installation. Once the installation finishes successfully,exit the Oracle Universal Installer by first clicking Exit and then clicking Yes.

If you choose to create a new starter data base(step 5),the Oracle Universal Installer invokes the Data base Configuration Assistant(DBCA)to create the database.We discuss creating a new data base with the DBCA in the following section.You’ll see the new features we discuss there (such as specifying the automatic memory configuration details) if you choose to create a new data base during installation itself by choosing the Create a Data base option here.by default, Oracle includes the new Secure Configu ration option,which configures the data base with auditing options as well as pass word policy and expiration settings.If you want, you can disable the new enhanced security controls during the installation.

Real Application Testing in Oracle 11G:

Adopting new technologies is too often a double-edged sword,making you more efficient and thus providing a competitive advan tage while simultan eously introducing uncertainty and potential instability into key production systems.

Change assurance,which involves making sure major changes such as new soft ware releases and data base upgrades don’t negatively impact perfo rma nce,has always been a prime concern for Oracle application developers and data base administrators.Even if you can simulate real production work loads,the effort is just that—a simulation,not the real deal.

In a world that is technologi cally advancing at a mind numbing pace,you need to know which of the techno logies have the poten tial to benefit you;there fore,you need to perform real testing with real data in real conditions.

Oracle Data base 11g places considerable emphasis on the pro active testing of changes by making change assurance one of the cornerstones of the new release.It does this through the Real Application Testing feature;this feature encompasses two components,Data base Replay and the SQL Performance Analyzer,that dramati cally reduce the risks inherent in adopting changes by offering you a realistic method of testing changes using real-life work loads. The tools unearth problems and give you the opportunity to fix them before you actually introduce the changes into your production systems.Here’s a brief summary of the two key components of Real Application Testing in Oracle Data base 11g:

DatabaseReplay: You can use the Data base Replay feature to test the impact of the data base up grade on the production work load by capturing and replaying the production work load on a test system before you actually perform the up grade on your production data base.Using the Data base Replay reports from a test server,you can fix poten tial problems before they occur on the production database.

SQL Performance Analyzer (SPA): You can use the SQL Performance Analyzer to predict the impact of any change,such as an up grade to a new release,on the SQL workload as captured by a SQL tuning set.By knowing ahead of the actual up grade about any adverse impact on performance and the root cause for it,you can prevent it from actually occurring in a production data base after a database upgrade.We discuss the SPA later in this chapter.
In addition to the Data base Replay and the SQL Performance Analyzer fea tures,there’s also a third new feature pertaining to change management, called SQL Plan Management, which replaces the stored outlines feature in earlier releases.The SQL Plan Mana gement feature relies on the use of SQL plan baselines,which represent efficient execution plans.When you adopt SQL Plan Management pursuant to a database upgrade, only those SQL plans are used that don’t result in a performance regression.

Database Replay

One of the major problems you face during an Oracle server software up grade process or an application up grade is the difficulty in simulating the actual production work load on the test data bases. This is also true when you’re moving to a totally new database configuration, say from a regular operating system file system to automatic storage management.

Even if you use sophisticated testing suite software, it’s not easy to accurately reproduce the true workload of a production data base.Consequ ently, you are forced to test in an unrealistic setting and take your chances when you move to the new release of the server software or the application. It’s not at all uncommon for DBAs and developers to bemoan the fact that the testing folks couldn’t adequately “stress test” the changes before they were approved for the switch to production.

Database Replay

The Data base Replay feature provides a solution to the vexing problem of reproducing production conditions in the testing and migration environments. By making it significantly easier to test potential data base changes,Oracle Data base 11g lowers the cost of data base up grades as well as other major changes such as operating system and storage system upgrades.

Testing that usually would take months when done by scripts and tradi tional load simulation tools can be done at dazzlingly fast speeds now with the Data base Replay feature. Data base Replay lets you capture the actual work load on a production system and analyze it the same way by replaying it on a test system.

The goal is to replicate the production environment in toto on a testsystem.Since the characteristics of the original work load such as concurrency and timing are maintained during the replay,you’re essentially working with the same type of resource contention and other characteristics.This lets you easily identify any negatives that’ll be potentially introduced by making the application,system,or software changes.

The goal is to make sure the change you’re making,such as a data base up grade,gets you only desirable results.Note that your test system must be run ning on the same or a newer version of the Oracle Data base compared to the production system.

The key fact you must understand here is that Data base Replay captures only the work load at the database level and ignores all client,application, and middle-tier interactions.

The replay will capture the impact of any system changes that affect the data base,such as an up grade of the data base itself,an upgrade of the oper ating system,or a switch to a new disk storage system.The production data base is backed up and restored on a test system with identical configuration and environment.Your goal is to replicate the production system with the same application state as the original.

You can use Database Replay for testing the following types of changes:

Database upgrades.
Operating system upgrades.
Storage system changes.
Configuration changes such as switching to an Oracle Real Application Cluster.
Using data base Replay to replay a production workload consists of four steps:

Workload Capture records the production database workload.
Workload Preprocessing makes the captured workload replayable by converting it into replay files.
Workload Replay replays the production workload in the test data base with actual timings,following the changes you want to test.
Analysis and Reporting deals with error reporting as well as reports on data divergence and performance divergence bet ween the production and test environments.You can also enlist the ADDM for a deeper performance analysis.

DATABASE SOFTWARE PATCHING – ORACLE 11G

Once the Oracle Database 11g server software is installed,you might want to up grade your data bases to the new release.Before you go live on your 11g production database environment,check for any available patch set release. Patch sets don’t provide additional functionality, but they provide bug fixes and don’t need certification for installation on the system.Similarly,check for any critical path updates,which are the security updates made available by Oracle on a quarterly basis.

Applying critical path updates secures your new data base from any significant security vulnerabilities that were discovered by Oracle.Just as important,you should make sure to check the bug data base and carefully consider critical bugs and pertinent bugs that may cause database outage situations.

New Features in Database Control for Patching

A software patch is a software update to fix defects in the software (bug fixes).A patch is thus supposed to fix bugs but doesn’t involve any new functionality as such.Periodically Oracle releases maintenance releases,which are known as patch sets.Patch sets are a set of integrated product fixes.

For example,if you’re using Oracle Database 11g Release 11.1.0.1 software,a new patch set might be termed 11.1.0.3.In Oracle Database 10g,Data base Control and Grid Control provided the crucial patch advisory.However,the Patch Prerequisite Check feature wasn’t available in Database Control.

In Oracle Database 11g,Database Control is enhanced by providing the Parch Prerequisite Check feature.In addition,Data base Control now has the Software Library feature as well.You can stage a patch once in the Software Library and use it for multiple deployments.

Enterprise Manager now searches proactively for patches that are relevant to a specific customer’s environment.Your installation and your database feature usage determine the search for patches.A daily patch job will run to correlate with the patch metadata on MetaLink.

When the patch job finds relevant patches for your environment,it sends you an alert and enables you to apply that patch.As soon as a new one-off patch becomes available,you will be alerted by the proactive MetaLink patch advisory if the patch is relevant to your database environment.Thus,you can count on reliable deployment with this automation of patching.

The Provisioning Pack lets you automatically deploy software, patches, and applications.You can do the following things with the Provisioning Pack:

—Bare-metal provisioning of operating systems and software images.
—Cloning installations and software images, for example, RAC or CRS.
Patching.
—Using the Deployment Procedure Manager.
—OEM Database Control simplifies the staging and application of new patches and patch sets.Data base Control can automatically stage a new patch set by proactively searching for it and down loading it from Oracle MetaLink to your server directories.Data base Control supports all types of patches,including critical patch updates (CPUs),interim patches, and patch sets

Here are some of the key features concerning patching in Oracle Database 11g:

–Live update of MetaLink best practices
–Support for sudo
–Support for Pluggable Authentication Modules (PAM)–based authentication
In Oracle Data base 10g,Database Control had a limited amount of patch management capabilities.In the Oracle Data base 10g release,you could do only two patch-related activitiesthrough Data base Control:

–Applying a patch.
–Viewing a patch cache.

In Oracle Database 11g, you still have the two links that are similar to the patching links in Oracle Database 11g.The Stage Patch link corresponds to the Apply Patch link.The View Patch Cache link is the same as in the previous release. However,the Data base Software Patching page in Oracle Database 11g is much more detailed and offers more tools. On the Data base Control home page,click Soft ware and Support to reach the Data base Software Patching page.You can go to the following pages from that page.

Patch Advisor page: The Patch Advisor page shows you the currently applicable patches to your installation.The page contains patch advisories in two sections.
Critical security patches: Critical security patches (also called critical patch updates) are periodic patch releases by Oracle to fix major security holes.
Patch recommendations by feature:Lists all patch recommendations based on the data base feature.
Patch Cache page: Any patches you download from the Oracle Meta Link to the Enterprise Manager are saved in the Enterprise Manager repository.You can view the current patches in the repository by clicking the View Patch Cach link,which takes you to the Patch Cache page.This page shows,in a tabular format,all the patches you downloaded from MetaLink. The advantage of the using the patch cache is that you need to down load a patch only once and stage it to multiple destinations.If you haven’t down loaded a patch,that’s OK too,since Enterprise Manager will automatically down load the necessary path from Meta Link when the patch job runs.

Oracle Patch Prerequisite Checker page: Click Path Prerequisites in the Database Software Patching section to get to the Oracle Patch Prerequisite Checker page.Here,you can select the software up dates from Meta Link or the Software Library.You can stage the updates to a staging location and run prerequisite checks on those up dates.
Stage Patch page: This page lets you select patches from MetaLink,based on search criteria you provide.
Apply Patch page: This page lets you select the patches to apply.You can select patches from Meta Link for the Software Library.
Emergency Hot Patching (Online Database Patching)

You can use the online database patching feature in Oracle Data base 11g to apply emergency,one-off patches to the data base server software, while the database is online.This means you can now patch Oracle executables with no downtime.This feature is especially useful for diagnostic patches.Some one-off patches can be done online now.

The new online patching capability is integrated with OPatch.Besidesinstalling a new patch,you can uninstall a patch without bringing the database offline.Similarly, you can also enable and disable one-off patches online.You can use the online patching capability to patch many one-off patches online.

There is also a sub set of online up gradeable patches for Real Application Cluster environments.Oracle plans eventually to enable the online patching of its periodically released critical path up dates.

Data base Change Management Pack

The Change Management Pack offers you the capability to compare data base object definitions before and after changes in the data bases.

The pack enables you to capture and compare data base object definitions from different points in time.Using the Change Management Pack,you can easily associate application modules with the database schema objects.After an appli cation up grade,for example,
you can assess the impact of the changes on the dependent data base objects so you can modify the application modules in accordance with the changes in the data base schema.In addition,you can track changes to initialization parameters as well as authorization and storage settings.

You can use the following types of sources to compare the database objects:

Two databases.
Two baselines.
A database and a baseline.
Software and Database Cloning

Oracle Data base 11g offers gold-image cloning(gold images are tested and approved software images)of software on the same server or cluster.Note the following points about software cloning:

You can clone multiple Oracle homes in parallel.
You can pre-patch images to a level you select.
You have a choice of the Software Library or the host itself to get the source image.
You can optionally perform configuration tasks following the cloning job,such as creating a database.In Oracle Database 10g, Data base Control offered you only the following two sources for cloning a data base:

A running database instance in archivelog or noarchivelog mode.
A saved working directory from a previous cloning operation.
In Oracle Data base 11g,Data base Control provides you with vastly enhanced cloning capabilities.You have the following four source types you can choose from now:

Copy an online data base files over Oracle Net,without any prior backups.
Copy an online database’s files via staging areas.
Use RMAN whole-database backups.
Use a special backup made by a previous database cloning operation.

Partitioning in Oracle 11g:

Oracle partitioning provides the ideal environment for implementing infor mation lifecycle management (ILM) solutions.By maintaining and implementing partitions on separate tablespaces,DBAs can place older data on less expensive storage tiers.New data continues to be stored in tier 1 storage, while the older data can be migrated to less expensive tier 2,3,and 4 storage.In addition,the older data can be compressed, thus further reducing storage costs.

Partitioning also provides to DBAs the flexibility to make tablespaces read-only. By implementing the skip readonly tablespace Syntax to the RMAN backup architecture, significantly less space will be required for backups performed to disk and tape.

Many companies do not implement partitioning because of the additional licensing cost associated with it.If DBAs, backup administrators, developers, and senior management carefully analyze their application to look for ways to implement partitioning with read-only tablespaces,the Oracle partitioning option pays for itself.For large companies that have storage in the terabyte or even petabyte range,the Oracle partitioning option can save them money in storage and backups.

Oracle offers four main partitioning mechanisms:

Composite partitioning
Hash partitioning
List partitioning
Range partitioning

Based on the business requirement,each of the partitioning methods has its appropriate justified need in today’s corporations.Oracle Database 11g improves partitioning options and provides new options to meet today’s demanding requirements.

New to Oracle Database 11g are numerous techniques for partitioning table data to increase the performance and organization of your corporate data.These partitioning techniques include the following:

Reference that allows tables with a parent-child relationship to be logically equipartitioned by inheriting the partition key from the parent table without duplicating the key columns
Interval that automatically creates maintenance partitions for range partitions
Extended composite that allows data to be partitioned along two dimensions
Virtual columns that allow virtual columns to be defined as partition key columns
Partition Advisor

Partitioning advice is available within the SQL Access Advisor as part of Enterprise Manager or the command-line interface.In Oracle Database 11g,the SQL Access Advisor will also recommend partitions.

In addition to the normal recommendations to create or drop indexes, materialized views,and materialized view logs,Oracle will recommend to partition existing tables and indexes to improve performance.

The Partition Advisor provides the appropriate SQL syntax to create atemporary partitioned table,copy data,and rename the table to the original table name. Furthermore,the Partition Advisor will show the potential performance improvements from implementing the recommended partitions. The Partition Advisor is integrated with the SQL Access Advisor and is licensed under Oracle’s Tuning Pack.

In the initial SQL Access Advisor screen,available as an option on the Advisor Central screen, you can click the option to recommend new access structures, as shown below:

Click the Next button,and you will be routed to the Workload Source screen.Here, you can select the source of the workload to analyze and select any filtering criteria.For demonstration purposes, keep the defaults on this screen.Click the Next button,and you will continue with the SQL Access Advisor to the Recommendation Options screen. On this screen, you can choose to select advice about indexes, materialized views,and partitions,as shown in Figure.

Figure SQL Access Advisor Recommendation Options screen

Click the Next button, and you will be routed to the Scheduler screen.Click the Next button one more time to be routed to the Review Screen. Finally, click the Submit button to create a Scheduler job.

Once the job completes successfully, you can view the results of the SQL Access Advisor. Once again from the Advisor Central screen, locate the completed SQL Access Advisor result, as shown in Figure.

Figure SQL Access Advisor Results screen

Click the respective row of the SQL Access Advisor, and click the View Result button.You will be redirected to the Advisor Central Summary page, as shown in Figure.

Figure SQL Access Advisor Summary screen

The two graphs on this screen show incredible potential in performance improvements. The workload IO cost is reduced from 6473 to 138.The query improvement is expected to improve by a factor of ten times.

Click the Recommendations tab and notice the ID column on the bottom of the screen regarding recommendations for implementation.Please notice that there are four action items and action types with the color-coded squares shown in Figure.

Figure Select Recommendations for Implementation screen

Click the ID URL,and you will drill down to the detailed recommendation screen for the specific SQL Access Advisor result,as shown in Figure.

Figure SQL Access Advisor recommendation details

Notice the PARTITION TABLE action for the CUST table.Click PARTITION TABLE to review the SQL script to convert the CUST table into a partitioned table.

You will notice the following sections of the partitioning advice:

Create new partitioned table
Gather table statistics
Copy constraints to the new partitioned table
Copy referential constraints to the new partitioned table
Populate new partitioned table with data from original table
Rename tables to give new partitioned table the original table name

Reference Partitions

Reference partitions are partitions based on the partitioning method of the parent table referenced by a foreign key.Reference partitioning relies on existing parent-child relation ships and is enforced by an active primary key and foreign key constraint.

The child table automatically inherits the partitioning key from the parent table without duplicating the key columns.The child table also inherits the maintenance operations from the parent table.

This reduces the amount of human error introduced by manually duplicating efforts on the child table.Prior to Oracle Database 11g,you had to allocate redundant storage for the partitioned key for both the master and child tables.In addition,you had to perform double maintenance.Oracle Database 11g saves storage and maintenance by providing the reference partitions.

Interval Partitioning

Interval partitioning is an answered prayer for many DBAs who have to add new partitions to their data warehouse environment on a weekly, monthly,or quarterly basis manually.Having to create new partitions at the end of the week/month/quarter is a cumbersome task that can consume weekend time for DBAs.On the flip side,some DBAs preallocate partitions for the next year or several years to avoid partition maintenance tasks.This luxury is afforded to DBAs who have a lot of storage available to them.Oracle Database 11g introduces the new interval partitioning that fully automates the creation of range partitions based on an interval threshold.Interval partitioning is an extension of range partitions.

Extended Composite Partitioning

Extended composite partitioning enhancement is a long-awaited feature for many DBAs and developers. Extended composite partitioning capabilities have been increased to address common business problems for companies in the 21stcentury. Prior to Oracle Database 11g,the only composite partitioning schemes were range-list and range-hash partitioning. Oracle Database 11g provides the complete composite partitioning options.

In Oracle Database 11g,you now have the means to partition by two relevant date ranges.For example,you can partition a table by ORDER_DATE and SHIP_DATE or by HIRE_DATE and COMMISSION_DATE.You now have the flexibility to create a partition based on two relevant lists. For example,you can create a composite partition based on state and city or by region and product group.There are four new extensions to the composite partitioning paradigm,discussed next.

Composite List-Hash Partitioning

You can create a list partition subpartitioned by hash partitions.This enables the granularity for partition-wise joins.This new partition mechanism can increase performance and fulfills business requirements for partitioning.

Composite List-List Partitioning

Many multiterabyte partitioning implementations have the requirements to partition by list and subpartition by list.You can see that the composite list-list partitions will be one of the favorites for developers and DBAs who have relatively static data sets for partitioning keys. For this example,let’s take our documents table again.

Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database.

System Partitioning

Oracle Database 11g complements Oracle’s partitioning infrastructure with the concept of system partitioning.System partitioning enables you to create a single table that is composed of multiple physical partitions.The fascinating concept behind system partitioning is that it does not use partition keys. Because there are no partitioning keys,system partitions have no bounds for ranges or lists. At the same time,because there are no partitioning keys,you must explicitly specify the rows to the target table partition using partition-aware syntax.The mapping of a row must be specified to the partition level.The benefit of system partitioning is that the application controls the partitioning for tables and indexes.The application controls the data placement and how it is retrieved.The database becomes the repository that provides the mechanism to break down an object into partitions without data-partitioning rules.

Enhanced Partition Pruning Capabilities

Another feature that you get out of the box.Starting inOracle Database 11g, partition pruning uses the bloom filtering and is auto matically active for all joins with a partitioned object.In the previous release,the subquery pruning method was triggered on the cost-based decision model.The subquery pruning consumed internal recursive resources. he new bloom filtering is activated all the time without consuming additional resources.

An Oracle database server consists of a database and at least one database instance, commonly referred to as simply an instance. Because an instance and a database are so closely connected, the term Oracle database is sometimes used to refer to both instance and database. In the strictest sense the terms have the following meanings:

Database: A database is a set of files, located on disk, that store data. These files can exist independently of a database instance.
Database instance

Cognosys provides hardened images of Oracle DB on all public cloud AWS , Azure and Google Marketplace.

Features

Oracle 11g has come with new feature that helps database administration to large extent.Below we have mentioned each feature:

1.Database Replay:
A exclusive tool that records the SQL statements and let you replay them at your will on same or different database server. It acts like DVR within the database. Using this approach this tool captures all the database activities beneath the SQL level in binary format and then you can replay those captured activities any time you wish.This tool reduces the risk involved in making any sort major or minor changes to your production databases. The minor changes can be like altering initialization parameters or major like upgrading systems or applying patches. This is changes are always the biggest ever concern in any of the organizations.Even though there are many third party tools for the same purpose but Oracle Database Replay is most reliable, efficient and robust amongst them.

2.Database Health Monitor:
It comes to production database servers, it very essential that everything is smoothly humming. There are no time-consuming, resource consuming or error-prone processes that harm overall performance. Oracle 11g introduces Automatic Health Monitor that monitors database components automatically and records the statistics in the repository called Automatic Diagnostic Repository. It really simplifies the tasks of database administrators of monitoring various components for failures It monitors database for various components like data files and dictionaries, to make sure that they are not logically and physically corrupted. When anything goes wrong the information is recorded and also fed into various recovery advisory. Along with this there is Packing Service that creates packages when an error occurs or anything goes wrong and sent it to the Oracle support.

Various checks that Automatic Health monitor does are:

Database structure checks.
Data Block integrity checks.
Resonance
Redo Integrity checks.
Undo segment integrity checks.
Transaction integrity checks.
Dictionary integrity checks, etc..

3.Performance Analyzer and Real-Time Monitor for SQL:
If you are interested in knowing how an execution plans for you SQL statement is and what impact it has on initialization parameters, know about the optimizer statistics refreshes etc then Oracle 11g provides more sophisticated tool that serves this purpose known as SQL Performance Analyzer (SPA). With SPA you can play out some specific SQL queries and even your entire workload against various types of changes that have impact on overall performance of database like initialization parameters and compare the reports to help improve their impact on database. With database replay you can only capture the database activities but with SPA you can know about your SQL and impact that various changes in parameters and SQL plan that has on database.

4.New Features in Manageability:

One thing that oracle promises with every database release is ease in manageability of database at various levels, Oracle 11g is no exception. Oracle 11g goes much more ahead in the case of manageability. It takes the responsibility of memory management, which is real big deal in production environment. Also various areas where it eases manageability is applying patches. Yes applying patches. It grabs available patches only for those features that an organization uses. Further it gathers the optimizer statistics and examines them before publishing and many more.

5.Backup and Recovery Advisory:
New functionality provides advice on data recovery in database and parallel backups for same files etc. Oracle 11g has made RMAN more powerful with new features like virtual catalogs for security, duplicate databases from backups and secure backups to cloud and many more.

6.ASM New Features in Oracle 11g:
With introduction of Oracle 10g ASM there has been blur between database administrators and system administrators in terms of storage allocation functions. Various storage related functions till release of Oracle 11g were performed by DBA’s with SYSDBA role. But now Oracle 11g has brought new role for admin who manage ASM instances know as SYSASM role. This role provides better controls for ASM administration.

7.Another added storage related features include:

Variable extent size allocation that reduces the shared pool usage.
Ability of instance to from specific disk of diskgroup.

8.SecureFiles New Concept of Large Object:
SecureFiles can be simply explained as extended version of LOB’s that offers the best features of both the external files and database LOB’s to store unstructured data. SecureFiles also allows encryption, deduplication and compression along with storage of such unstructured data.

9.Enhanced Caching and Pooling:
Caching is the process of storing data in memory instead of disk because accessing memory is much quicker than accessing disks. Oracle 11g has enhanced the performance of database by using new and very strong caches like SQL Result cache, PL/SQL function cache, Client Side cache and Database Resident Connection Pooling.

10.Schema Management:
Oracle 11g new functionality that manage database object efficiently and execute common operations fast and in simple way. Various schema management functionality that this release provides:

DDL wait Option
Virtual Column
Invisible indexes
Online Segment Shrink
Read-only tables, etc..

Major Features of Oracle DB

Oracle Move is cost effective. The same flexibility that lets you directly migrate your Oracle Database to the Oracle Cloud is applied to finding the most cost effective solution for the purpose and duration of the migration.
Oracle Move is highly available and scalable. The tight integration of all migration tools with the Oracle Database lets you maintain control and gain better efficiency when moving your databases into the Oracle Cloud.
Oracle Move offers simplicity and efficiency. Oracle automated tools make it seamless to move your database to the Oracle Cloud with virtually no downtime. Using the same technology and standards on-premises and in the Oracle Cloud, you can facilitate the same products and skills to manage your cloud-based Oracle Databases as you would on any other platform.
Oracle Move is flexible. You can directly migrate your Oracle Database to the Oracle Cloud from various source databases into different target cloud deployments depending on your requirements and business needs. Oracle Move provides a well-defined set of tools, giving you the flexibility to choose the method that best applies to your needs