Install Oracle 11G Release 2 (11.2) on Centos Linux 7

This article presents how to install Oracle 11G on Centos Linux 7.

Read following article how to install Centos Linux 7: Install Centos Linux 7 (for comfort set 2G memory for your virtual machine before proceeding with Oracle software installation).

Installation software is available on OTN, edelivery version 11.2.0.1 or Oracle support(old metalink) 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.

Database software – 11.2.0.4

p13390677_112040_Linux-x86-64_1of7.zip 
p13390677_112040_Linux-x86-64_2of7.zip

Continue reading

PL/SQL Hierarchical Profiler Oracle Database 11G release 2 (11.2)

With 11g release 1 Oracle has introduced new package DBMS_HPROF. It’s very useful for DBAs and developers to analyze execution of PL/SQL code. It enables to gather, store and organize a lot of information about executed code.

Informations gathered by this package are supported by Oracle sqldeveloper which is another great news :).

The PL/SQL hierarchical profiler features:

  • easy to install and use
  • collect execution times for SQL and PL/SQL code
  • keeps results in database tables
  • Provides subprogram-level execution summary information, such as:
    • Number of calls to the subprogram
    • Time spent in the subprogram itself
    • Time spent in the subprogram itself and in its descendent’s subprograms
    • Detailed parent-children information, for example:
      • All callers of a given subprogram (parents)
      • All subprograms that a given subprogram called (children)
      • How much time was spent in subprogram x when called from y
      • How many calls to subprogram x were from y

Continue reading

Dynamic SQL changes in PL/SQL 11G – Oracle Database 11G release 2 (11.2)

New enhancements are added in 11g for dynamic SQL in PL/SQL :

  • SQL statements that are larger than 32 KB are now allowed in native dynamic SQL
  • CLOB can be used for DBMS_SQL.PARSE
  • DBMS_SQL cursor can be converted to REF CURSOR and vice versa
  • Mored data types(including object types, collections) are supported in DBMS_SQL

Continue reading

Install Oracle 11G Release 2 (11.2) on Fedora 20

This article presents how to install Oracle 11G on Fedora 20.

Read following article how to install Fedora 20 Linux: Install Fedora 20 (for comfort set 2G memory for your virtual machine before proceeding with Oracle software installation).

Installation software is available on OTN version 11.2.0.1 or metalink 11.2.0.4. In this installation I’m presenting installation for 11.2.0.4 but for previous version 11.2.0.X it shouldn’t be different.

Check article for new release Install Oracle 12C Release 1 (12.1) on Fedora 20

Oracle software which I verified

release 11.2.0.4

p13390677_112040_Linux-x86-64_1of7.zip 
p13390677_112040_Linux-x86-64_2of7.zip

Continue reading

New parallel parameters and options – Oracle Database 11G release 2 (11.2)

New parameters and parallel behaviour are introduced in Oracle 11g that simplifies management of parallel DOP calculations just before execution of a query in database.

New parameters

PARALLEL_MIN_TIME_THRESHOLD - any number > 0, AUTO(default)
PARALLEL_DEGREE_POLICY - MANUAL(default),LIMITED,AUTO
PARALLEL_DEGREE_LIMIT - any number > 1, CPU(default), IO, AUTO
PARALLEL_FORCE_LOCAL - TRUE,FALSE(default)

Changed parameters:

PARALLEL_IO_CAP_ENABLED  - is deprecated and remapped 
to PARALLEL_DEGREE_LIMIT set to IO
PARALLEL_THREADS_PER_CPU - is now hidden parameter
 _PARALLEL_THREADS_PER_CPU
PARALLEL_AUTOMATIC_TUNING - is deprecated and should not 
be used. It means should have default value FALSE.

Continue reading

Configure SCAN DNS for RAC 11G RAC 12C using dnsmasq in OEL5, OEL6, OEL7

Configuration of network for virtualbox and RAC is always making a lot of trouble and raises many questions. In this article I’ll show how to setup network for RAC 11G, 12C using dnsmasq.

Objectives to complete:

  • full network setup for RAC
    • public network
    • private network
    • SCAN settings resolved by dnsmasq
  • full access to internet from each RAC hosts
    • internet network
  • configure dnsmasq

Continue reading

Install Oracle 11G Release 2 (11.2) on Windows 7, 8

This article presents how to quickly install Oracle Database 11G Release 2 (11.2.0.3) and as Oracle Grid 11G Release 2(11.2.0.3) on Windows.

Check article for new release Install Oracle 12C Release 1 (12.1) on Windows 7, 8

Software used:

  • Oracle Database 11G Release 2(11.2.0.3) – 64 bit for Windows
  • Oracle Grid 11G Release 2(11.2.0.3) – 64 bit for Windows
  • Windows 8 Pro 64 bit

You can download Oracle Database 11G Release 2(11.2.0.3) and Oracle Grid 11G Release 2(11.2.0.3). You can download them on Oracle support formerly MetaLink.

p10404530_112030_MSWIN-x86-64_1of7.zip - database software
p10404530_112030_MSWIN-x86-64_2of7.zip - database software
p10404530_112030_MSWIN-x86-64_1of7.zip - grid software

Hardware and software requirements for database and grid software:

  • at least 4GB ram as minimum,  20G space for binaries and 40G for ASM devices
  • Windows Server 2003 – all x64 editions
  • Windows Server 2003 R2 – all x64 editions
  • Windows XP Professional x64 Edition
  • Windows Vista x64 – Business, Enterprise, and Ultimate editions
  • Windows Server 2008 x64 and Windows Server 2008 R2 x64 – Standard, Enterprise, Datacenter, Web, and Foundation editions
  • Windows 7 x64 – Professional, Enterprise, and Ultimate editions

Windows 8 Support

Starting with Oracle Database 11g Release 2 (11.2.0.4), Oracle Database server and client are supported on Windows 8. Oracle RAC is not supported on Windows 8.

In case you are not planning to install grid 2GB ram is enough.

This installation was done for 11.2.0.3 on Windows 7 but with release 11.2.0.4 it’s possible on Windows 8.

Continue reading

Flashback Data Archive 11g

This article presents new feature of 11g – Flashback Data Archive.

This functionality allows to save all transactions executed on a table for longer period than just UNDO_RETENTION parameter and UNDO tablespace. In 11g you can create dedicated space to keep changed data(DML operations, DDL operations) for longer specified retention period.

Historical records can be reviewed using standard flashback sql queries feature.

Continue reading