Oracle Database 11.2.0.4 Installation on Oracle Linux 6.5 with existing 12c database

As part of installation of Oracle 12c EM Cloud Control, we want to create a repository database on a different machine which has Oracle 12c single instance database installed. We have to install the 11g binary in order to cater for the certification requirement of 12c EM CC repository DB. See Enterprise Manager Cloud Control 12.1.0.3 – II Software Certification Matrix Check for details.

The Installation involves two steps, perform software only install and run dbca to create database.

1. Use runInstaller to install Oracle database 11.2.0.4 software only.

Check the software location and make sure it’s different from the software location of existing 12c DB.

Image

Confirm the installation details and start the installation process.

Image

2. Use DBCA to create the database which name is emcc.

Because something goes wrong with DBCA through VNC (it shows gray window so that cannot proceed with the installation), so we have to use DBCA silent mode to create database. But save the options into template using interactive DBCA if possible.

Later found the VNC gray screen problem is caused by missing fonts package, after the package install, the DBCA window could be shown successfully.

[oracle@oradb1 ~]$ dbca -help
dbca  [-silent | -progressOnly | -customCreate] {<command> <options> }  | { [<command> [options] ] -responseFile  <response file > } [-continueOnNonFatalErrors <true | false>]
Please refer to the manual for details.
You can enter one of the following command:

Create a database by specifying the following parameters:
        -createDatabase
                -templateName <name of an existing template in default location or the complete template path>
                [-cloneTemplate]
                -gdbName <global database name>
                [-sid <database system identifier>]
                [-sysPassword <SYS user password>]
                [-systemPassword <SYSTEM user password>]
                [-emConfiguration <CENTRAL|LOCAL|ALL|NONE>
                        -dbsnmpPassword <DBSNMP user password>
                        -sysmanPassword <SYSMAN user password>
                        [-hostUserName <Host user name for EM backup job>
                         -hostUserPassword <Host user password for EM backup job>
                         -backupSchedule <Daily backup schedule in the form of hh:mm>]
                        [-centralAgent <Enterprise Manager central agent home>]]
                [-disableSecurityConfiguration <ALL|AUDIT|PASSWORD_PROFILE|NONE>
                [-datafileDestination <destination directory for all database files> |  -datafileNames <a text file containing database objects such as controlfiles, tablespaces, redo log files and spfile to their corresponding raw device file names mappings in name=value format.>]
                [-redoLogFileSize <size of each redo log file in megabytes>]
                [-recoveryAreaDestination <destination directory for all recovery files>]
                [-datafileJarLocation  <location of the data file jar, used only for clone database creation>]
                [-storageType < FS | ASM >
                        [-asmsnmpPassword     <ASMSNMP password for ASM monitoring>]
                         -diskGroupName   <database area disk group name>
                         -recoveryGroupName       <recovery area disk group name>
                [-characterSet <character set for the database>]
                [-nationalCharacterSet  <national character set for the database>]
                [-registerWithDirService <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-listeners  <list of listeners to configure the database with>]
                [-variablesFile   <file name for the variable-value pair for variables in the template>]]
                [-variables  <comma separated list of name=value pairs>]
                [-initParams <comma separated list of name=value pairs>]
                [-sampleSchema  <true | false> ]
                [-memoryPercentage <percentage of physical memory for Oracle>]
                [-automaticMemoryManagement ]
                [-totalMemory <memory allocated for Oracle in MB>]
                [-databaseType <MULTIPURPOSE|DATA_WAREHOUSING|OLTP>]]

Run command to run DBCA in silent mode to create database.

dbca -silent -createDatabase -templateName emccdb_template.dbt -gdbName emcc -sysPassword oracle -systemPassword oracle

Image

the new created database has been automatically configured in the listener

[oracle@oradb1 ~]$ . oraenv
ORACLE_SID = [emcc] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oradb1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-APR-2014 16:46:27

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                22-APR-2014 16:09:41
Uptime                    0 days 0 hr. 36 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid_1/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oradb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb1.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oradb1.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "emcc" has 1 instance(s).
  Instance "emcc", status READY, has 1 handler(s) for this service...
Service "emccXDB" has 1 instance(s).
  Instance "emcc", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Switch to 11.2.0.4 DB emcc:

[oracle@oradb1 ~]$ . oraenv
ORACLE_SID = [orcl] ? emcc
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oradb1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-APR-2014 16:48:28

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                22-APR-2014 16:09:41
Uptime                    0 days 0 hr. 38 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid_1/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/oradb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb1.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oradb1.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "emcc" has 1 instance(s).
  Instance "emcc", status READY, has 1 handler(s) for this service...
Service "emccXDB" has 1 instance(s).
  Instance "emcc", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


Note: Delete Database using DBCA Silent mode

[oracle@oradb1 ~]$ dbca -silent -deleteDatabase -sourceDB testdb -sysDBAUserName sys -sysDBAPassword oracle
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/testdb.log" for further details.



 

Recursive Query in PostgreSQL DB

//Table Structure
 select * from test1 limit 1;
  id   | parentid |                   industry_name
-------+----------+---------------------------------------------------
   409 |    400   | Health and Medical - Alternative
   322 |    320   | News and Media - Community Directories and Guides
   320 |      0   | News and Media
   400 |      0   | Health and Medical
<snippet>


//Simple Recursive Query
with RECURSIVE cte as
(
select a.id,a.industry_name,a.parentid from staging.test1 a where parentid=400
union all 
select k.id,k.industry_name,k.parentid as industry_name from staging.test1 k inner join cte c on k.parentid=c.id
)select id,industry_name from cte ;

 id  |                      industry_name
-----+----------------------------------------------------------
 409 | Health and Medical - Alternative
 280 | Health and Medical - Wellbeing
 401 | Health and Medical - Pharmaceutical and Medical Products
 408 | Health and Medical - Paramedical and Ancillary
 403 | Health and Medical - Primary and Specialist
 406 | Health and Medical - Health Insurance
 410 | Health and Medical - Organisations
 404 | Health and Medical - Pharmacies
 405 | Health and Medical - Research
 402 | Health and Medical - Hospitals
 407 | Health and Medical - Information
(11 rows)

//Complicated recursive query
 with RECURSIVE cte as
(
select a.id,cast(a.industry_name as varchar(200)),a.parentid from staging.test1 a where id=400
union all 
select k.id,cast(c.industry_name||'--->'||k.industry_name as varchar(200)),k.parentid as industry_name from staging.test1 k inner join cte c on k.parentid=c.id
)select id,industry_name from cte ;


 id  |                                 industry_name
-----+--------------------------------------------------------------------------------
 400 | Health and Medical
 409 | Health and Medical--->Health and Medical - Alternative
 280 | Health and Medical--->Health and Medical - Wellbeing
 401 | Health and Medical--->Health and Medical - Pharmaceutical and Medical Products
 408 | Health and Medical--->Health and Medical - Paramedical and Ancillary
 403 | Health and Medical--->Health and Medical - Primary and Specialist
 406 | Health and Medical--->Health and Medical - Health Insurance
 410 | Health and Medical--->Health and Medical - Organisations
 404 | Health and Medical--->Health and Medical - Pharmacies
 405 | Health and Medical--->Health and Medical - Research
 402 | Health and Medical--->Health and Medical - Hospitals
 407 | Health and Medical--->Health and Medical - Information
(12 rows)

Enterprise Manager Cloud Control 12.1.0.3 – II Software Certification Matrix Check

Access the Enterprise Manager certification matrix and check:

  • OS and OMS

OS version Oracle Linux 6 x86-64  vs. OMS version 12.1.0.3:

33

 

  • Repository Database and OMS

The existing, certified Oracle Database must be one of the certified databases listed in the Enterprise Manager certification matrix,or a database instance created with a preconfigured Oracle Management Repository (Management Repository) using the database templates offered by Oracle. Note the template only work with Oracle database 11.2.0.3.

Oracle Management Service (Repository Database) has to be 11.2.0.4 or lower, 12.1.0.1 is not supported.Image

 

Image

 

  • OS and OMS repository database

Check for OS compatibility of Oracle database 11.2.0.4

9

Also note that Oracle database 11.2.0.1 is not compatible with Oracle Linux 6, see below

8

Enterprise Manager Cloud Control 12.1.0.3 – I Architecture

Architecture Diagram

oms_architecture

Components:

  •     Oracle Management Agent

The Management Agent is an integral software component that enables you to convert an unmanaged host to a managed host in the Enterprise Manager system. The Management Agent works in conjunction with the plug-ins to monitor the targets running on that managed host.

  •     Oracle Management Service

The OMS is a Web-based application that orchestrates with the Management Agents and the Plug-Ins to discover targets, monitor and manage them, and store the collected information in a repository for future reference and analysis. The OMS also renders the user interface for Enterprise Manager Cloud Control.
The OMS is deployed to the Oracle Middleware home (Middleware home), which is the parent directory that has the Oracle WebLogic Server home, the OMS home, the Management Agent home, the plug-in home, the Java Development Kit (JDK), the OMS instance base directory, the Oracle WT directory, the Oracle Common directory, and other relevant configuration files and directories.
While deploying the OMS, the Enterprise Manager Cloud Control Installation Wizard installs Oracle WebLogic Server if it does not already exist in your environment. As a result, a WebLogic Server admin console is also installed.

  •     Oracle Management Repository

The Management Repository is a storage location where all the information collected by the Management Agent gets stored. It consists of objects such as database jobs, packages, procedures, views, and tablespaces.

The OMS uploads the monitoring data it receives from the Management Agents to the Management Repository. The Management Repository then organizes the data so that it can be retrieved by the OMS and displayed in the Enterprise Manager Cloud Control console. Since data is stored in the Management Repository, it can be shared between any number of administrators accessing the Enterprise Manager Cloud Control.

At the time of installation, the Enterprise Manager Cloud Control Installation Wizard configures the Management Repository in your existing, certified database. The wizard, however, does not install a new database.

  •     Plug-Ins
  •     Enterprise Manager Cloud Control Console

 

Reference:

Oracle Enterprise Manager Cloud Control Basic Installation Guide

Index not being used with VOLATILE function

Database Version: PostgreSQL 9.3.4

Issue:  A query ran very fast (~1 second) before but run extremely slow (~40 seconds) now.

Query:

SELECT id,
       industry_name
FROM industries
WHERE id IN
    (SELECT unnest(industries) AS ind
     FROM websites
     WHERE channel = func_get_aggregated_channel('ap','google.com'));

After checking the execution plan as below, note that the Sequential Scan on table websites took most of the time. The table has index created on column channel, and the index was not used at all.

                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..305193.59 rows=100 width=34) (actual time=40155.277..40155.303 rows=2 loops=1)
   Join Filter: (industries.id = (unnest(websites.industries)))
   Rows Removed by Join Filter: 325
   ->  Seq Scan on industries  (cost=0.00..3.64 rows=164 width=34) (actual time=0.011..0.027 rows=164 loops=1)
   ->  Materialize  (cost=0.00..304944.20 rows=100 width=4) (actual time=220.628..244.849 rows=2 loops=164)
         ->  Seq Scan on websites  (cost=0.00..304942.70 rows=100 width=27) (actual time=36182.938..40155.151 rows=2 loops=1)
               Filter: ((channel)::text = func_get_aggregated_channel('ap'::text, 'google.com'::text))
               Rows Removed by Filter: 1103935
 Total runtime: 40155.358 ms
(9 rows)

Time: 40156.122 ms

If replace the function with a normal string like ‘google.com’

SELECT id,
       industry_name
FROM industries
WHERE id IN
    (SELECT unnest(industries) AS ind
     FROM websites
     WHERE channel = 'google.com');

The execution plan changes to:

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=11.19..16.38 rows=100 width=34) (actual time=0.159..0.159 rows=0 loops=1)
   Hash Cond: (industries.id = (unnest(websites.industries)))
   ->  Seq Scan on industries  (cost=0.00..3.64 rows=164 width=34) (actual time=0.069..0.069 rows=1 loops=1)
   ->  Hash  (cost=9.94..9.94 rows=100 width=4) (actual time=0.079..0.079 rows=0 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 0kB
         ->  Index Scan using idx_websites_1 on websites  (cost=0.43..8.94 rows=100 width=27) (actual time=0.079..0.079 rows=0 loops=1)
               Index Cond: ((channel)::text = 'google.com'::text)
 Total runtime: 0.203 ms
(8 rows)

Therefore, there’s something wrong with the usage of function here.

The function has been slightly modified today because the schema change. It was a stable function before, but, to “set search_path” to different schemas by taking arguments, it has to be claimed as volatile.

Quote from PostgreSQL 9.3 documentation http://www.postgresql.org/docs/9.3/static/xfunc-volatility.html

“A VOLATILE function can do anything, including modifying the database. It can return different results on successive calls with the same arguments. The optimizer makes no assumptions about the behavior of such functions. A query using a volatile function will re-evaluate the function at every row where its value is needed.

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement. This category allows the optimizer to optimize multiple calls of the function to a single call. In particular, it is safe to use an expression containing such a function in an index scan condition. (Since an index scan will evaluate the comparison value only once, not once at each row, it is not valid to use a VOLATILE function in an index scan condition.)”

This is our function definition:

Function with volatility classification as “volatile”

CREATE OR REPLACE FUNCTION func_get_aggregated_channel(i_market TEXT,i_channel TEXT)
RETURNS TEXT AS $$
DECLARE
myrec TEXT;
o_channel TEXT;
BEGIN
o_channel = i_channel;
execute 'set search_path to common,'||i_market;
...
RETURN o_channel;
END;
$$LANGUAGE plpgsql volatile;

Now, we have to claim the function stable and leave the “set search_path” part to applications.

Function with volatility classification as “stable”

CREATE OR REPLACE FUNCTION func_get_aggregated_channel(i_channel TEXT)
RETURNS TEXT AS $$
DECLARE
myrec TEXT;
o_channel TEXT;
BEGIN
o_channel = i_channel;
...
RETURN o_channel;
END;
$$LANGUAGE plpgsql stable;

Rerun the Query with new argument

SELECT id,
       industry_name
FROM industries
WHERE id IN
    (SELECT unnest(industries) AS ind
     FROM websites
     WHERE channel = func_get_aggregated_channel('google.com'));

Problem solved!

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=11.44..16.63 rows=100 width=34) (actual time=0.291..0.307 rows=2 loops=1)
   Hash Cond: (industries.id = (unnest(websites.industries)))
   ->  Seq Scan on industries  (cost=0.00..3.64 rows=164 width=34) (actual time=0.010..0.038 rows=164 loops=1)
   ->  Hash  (cost=10.19..10.19 rows=100 width=4) (actual time=0.195..0.195 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Index Scan using idx_websites_1 on websites  (cost=0.68..9.19 rows=100 width=27) (actual time=0.189..0.191 rows=2 loops=1)
               Index Cond: ((channel)::text = func_get_aggregated_channel('google.com'::text))
 Total runtime: 0.338 ms
(8 rows)

Use git to create a script repository

As a DBA, I have a lot of scripts lying on my disk and have to copy it here and there which is very inconvenient and it’s hard to keep them synced to the latest version, if there was a change.

Now, I’ve turn to GIT which is a very popular tool at the moment. Below is how to build a public repository and add your scripts. Note, if you don’t want to share them (make them public), you have to pay for the service. But I really don’t think it’s necessary to make those management scripts “private”.

  • Register a new user at Github: https://github.com/
  • Download and install GIT on your client machine.If you’re using Linux, that’s pretty simple and straighforward, just use the preferred package manager of your Linux distribution. For CentOS, RHEL, Oracle Linux, make sure you have a YUM repository configured, and run “yum install git”. for other Linux, refer to http://git-scm.com/download/linux
  • Setup GIT, create a repository, clone a repository to client machine and perform normal tasks against the repository. Details provided below.
  • Note: To be able to login using https you should set your username to the git remote, then you will be asked for your password when tries to git pushgit remote set-url origin https://yourusername@github.com/user/repo.git

Example:

#Setup git - only do it once

git config --global user.name "jasmine"
git config --global user.email "jasmine@example.com"

mkdir ~/scripts/oracle
cd ~/scripts/oracle

git init
touch README
git add README
git commit -m 'first commit'
git remote add origin https://jasmine@github.com/jasmine/repo.git
git push origin master

#Go to the Git local repository directory first:

cd ~/scripts/oracle

#clone git repository to other servers and make changes locally, then upload changed files to remote repository

git clone https://github.com/jasmine/oracle.git 
##make change on local repository
git add .
git commit -m "put some comments"
git push

#sync changs files from repository

git pull

#check status

git status

Oracle 11.2.0.4.0 patchset released – Linux/Solaris

The software can be downloaded from My Oracle support: Patch:13390677

References:

  • 11.2.0.4 Patch Set – Availability and Known Issues Note:1562139.1
  • 11.2.0.4 Patch Set – List of Bug Fixes by Problem TypeNote 1562142.1
  • Actions For DST Updates When Upgrading To Or Applying The 11.2.0.4 Patchset Note 1579838.1

For details, refer to Oracle Database Upgrade Path Reference List (Doc ID 730365.1)