Wednesday, August 21, 2019

12.2: Preupgrade Utility Fails with ORA-06512: at "SYS.DBMS_PREUP"

While running 12.2 Pre-Upgrade Utility (manually or with DBUA) on 12.1 database, received following errors,
Error in preupgrade tool execution.
ERROR -
ERROR - Unable to run sqlplus due to:
ORA-06512: at "SYS.DBMS_PREUP", line 4401
ORA-06512: at "SYS.DBMS_PREUP", line 9714
ORA-06512: at line 8

declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 56

if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate permissions to the Oracle binaries owner.


This is the bug of the Pre-Upgrade Utility installed in current 12.2 home. It can be fixed by installing new version of the utility as following,

1. Download utility ZIP file from My Oracle Support following the instruction of document "How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)"
  
2. The current available ZIP file for 12.2 (target upgrade-to version) is preupgrade_12201_cbuild_22_lf.zip, it contains following files,
   dbms_registry_basic.sql         
   dbms_registry_extended.sql
   preupgrade.jar
   preupgrade_driver.sql
   preupgrade_messages.properties
   preupgrade_package.sql

3. Unzip downloaded file to directory $ORACLE_HOME/rdbms/amdin, here $ORACLE_HOME is 12.2 home.

   Note: Rename existing files before unzipping, do not override any files.

Monday, June 10, 2019

Reduce number of privileged access audit files for ASM with syslog on AIX

ASM instance audits all privileged (log in as sysasm, sysdba or sysoper) access by creating audit file for each connection under the audit file destination directories (defined by initialization parameter audit_file_dest). The directory can grow to contain a very large number of files if they are not regularly maintained, especially when initialization parameter audit_sys_operations is true which is default from 12c.

Wednesday, May 29, 2019

AIX: Failure to Apply April 2019 Grid Infrastructure Release Update on 12.2 Oracle Restart Home

Environment

  Operation System:  IBM AIX 7.2 on POWER Systems (64-bit)
  Oracle Restart Home:  12.2.0.1 with July 2018 Grid Infrastructure Release Update (GI RU 12.2.0.1.180717)
  OPatch Version: 12.2.0.1.17

Symptoms
 
Opatch reported following errors while applying sub-patch 29314424 (OCW Apr 2019 Release Update 12.2.0.1.190416)
UtilSession failed: ApplySession failed in system modification phase... 'ApplySession::apply failed: java.lang.UnsatisfiedLinkError: /u01/app/grid/product/12.2.0/grid/lib/libpatchgensh12.so (rtld: 0712-001 Symbol nzos_SetServerNameList was referenced
      from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a runtime definition
      of the symbol was not found.
rtld: 0712-001 Symbol lxkRegexpComp2 was referenced
      from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a runtime definition
      of the symbol was not found.
rtld: 0712-001 Symbol lxkRegexpLike2 was referenced
      from module /u01/app/grid/product/12.2.0/grid/lib/libhasgen12.so(), but a ru)

Workaround

Apply Oct 2018 Grid Infrastructure Release Update (GI RU) first, then apply April 2019 GI RU.

Thursday, January 31, 2019

DBCA Failed with CRS-2566: User 'oracle' does not have sufficient permissions to operate on resource 'ora.driver.afd'

DBCA 12.2.0.1 failed with following errors 

   PRCR-1006: Failed to add resource ora.corcl.db for corcl
   PRCR-1071: Failed to register or update resource ora.corcl.db
   CRS-2566 : User 'oracle' does not have sufficient permissions to operate on resource 'ora.driver.afd', which is part of the dependency specification.

The clusterware is Grid Infrastructure 18c in Standalone mode (Oracle Restart), the GI home owner is grid. Log into system as grid and check the permissions of CRS resourc 'ora.driver.afd'
[grid@host01]$ crsctl stat res ora.driver.afd -p -attr ACL
NAME=ora.driver.afd
ACL=owner:grid:rwx,pgrp:asmadmin:r-x,other::r--,user:grid:r-x

ACL shows that grid is the owner of the resource and have full control (rwx) on the resource, OS group asmadmin has read & execute(r-x), and database owner oracle is not explicitly granted permissions on this resource.  

Check if oracle is member of group asmadmin,
[grid@host01]$ id oracle
uid=104(oracle) gid=112(oinstall) groups=112(oinstall),114(asmdba),116(dba),117(oper),118(racdba),119(backupdba),120(dgdba),121(kmdba)

User oracle is not in the group asmadmin. Therefore, DBCA is telling the truth that oracle does not have permissions on the resource. It is technically easy to fix this issue, but honestly it is not expected there and should be considered as GI 18c bug.

To fix it, just add the group of which oracle is a member (I pick oinstall) to the ACL as following,
[grid@host01]$ crsctl modify resource ora.driver.afd -attr "ACL='owner:grid:rwx,pgrp:asmadmin:r-x,other::r--,group:oinstall:r-x,user:grid:r-x'" -init

Confirm that the ACL is changed,
[grid@host01]$ crsctl stat res ora.driver.afd -p -attr ACL
NAME=ora.driver.afd
ACL=owner:grid:rwx,pgrp:asmadmin:r-x,other::r--,group:oinstall:r-x,user:grid:r-x'

Now, DBCA can create database successfully.

Tuesday, January 15, 2019

Disable Transparent Hugepages on Oracle Linux / Redhat Enterprise Linux 7.5

Transparent Hugepages (THP) are similar to standard HugePages. However, while standard HugePages allocate memory at startup, Transparent Hugepages memory uses the khugepaged thread in the kernel to allocate memory dynamically during runtime, using swappable HugePages.

They may cause delays in accessing memory that can result in node restarts in Oracle RAC environments, or performance issues or delays for Oracle Database single instances. Oracle recommends to disable Transparent HugePages on Oracle database servers.

Following discussion is based on Oracle Linux / Redhat Enterprise Linux 7.5, it may be different on other Linux distributions.

Friday, December 21, 2018

Configure Python with iPython, Jupyter and MySQL on Ubuntu 18.10

If you’re thinking of learning Python, you may be struggling with where and how to start. It could be a pretty hard question before you know why you want to learn Python because Python, as a popular programming language, is used in many fields and industries such as

  . web development (create web applications on a server)
  . software development (work with software developing tools to create work flows)
  . mathematics (scientific computing, data analysis, data visualization)
  . system scripting
 
As I am asked the question by a university student and Python is wildly used for data analysis/visualization in university, I going to demonstrate how to qhickly build a Python data analysis/visualization developing environment on Ubuntu 18.10.

Sunday, November 18, 2018

Patch or Upgrade Single instance Database and Oracle Restart Using OEM 13c Fleet Maintenance

Oracle Enterprise Manager (OEM) 13c Database Fleet Maintenance is a centralized mechanism for patching/upgrading of Oracle homes and maintain consistency. It allows OEM Cloud Control administrators to patch database, Oracle Restart (Standalone Grid Infrastructure) or Grid Infrastructure with minimal downtime.

Friday, August 31, 2018

Oracle Database 18c Password Authentication with Microsoft Active Directory

Oracle database 18c introduces a new feature 'Integration of Active Directory Services with Oracle Database'. It gives us a way to authenticate and authorize users directly with Microsoft Active Directory. Oracle database users and roles can map directly to Active Directory users and groups without using Oracle Enterprise User Security (EUS) or another intermediate directory service. Here, I am going to demonstrate how to make it work step by step.
 
Environment:

  Database Server - Oracle Database 18c Enterprise Edition on Oracle Linux Server 7.5
  Microsoft Active Directory Server - Windows Server 2012 R2

Tuesday, August 14, 2018

Restrict OEM agent 13.2 to use TLS protocol TLSv1.2

Oracle Enterprise Manager 13.2 agent, by default, is configured to use TLS protocol TLSv1.0, TLSv1.1 and TLSv1.2,all versions of TLS when OEM 13.2 released. To restrict agent to use only TLSv1.2 as following,

Friday, August 3, 2018

DBUA 12.2 failed with ORA-01157 at preupgrade tool execution

DBUA got following errors at 'Prerequisite Check' (perupgrade tool execution) while upgrading Oracle database 11.2.0.4 to 12.2.0.1
Error in preupgrade tool execution. ERROR - ERROR - Unable to run sqlplus due to: ORA-06512: at "SYS.DBMS_PREUP",
line 4386 ORA-06512: at "SYS.DBMS_PREUP", line 9108 ORA-06512: at line 8 declare * ERROR at line 1: ORA-01157:
cannot identify/lock data file 202 - see DBWR trace file ORA-01110: data file 202: '+DATA' ORA-06512: at line 56
if you are executing the preupgrade tool from a standby database make sure the preupgrade tool has been executed
in the primary database If you are executing the tool from Windows, please make sure you have given the appropriate
permissions to the Oracle binaries owner. (more details)

Thursday, March 8, 2018

Manually Delete Agent and Targets Using EMCLI in OEM 13c

Although it is recommended to remove targets through OEM console interface, command line utility is more helpful/convenient in some scenarios.

For example, the target host is crashed and cannot be restored, and a new host with new name is built for replacement. Therefore,
the old host and all targets running on this host have to be remove from OEM. Depends on number of targets, it could be a pain to remove
them through console. At this time, manually running EMCLI command becomes the best way.

Following command removed the agent running on server host01 and all targets monitored by this agent,
[oracle@oms1]$ $EMCLI_HOME/bin/emcli login -username=sysman
Enter password :

Login successful
[oracle@oms1]$ $EMCLI_HOME/bin/emcli delete_target -name="host01.dbaplus.ca:3872" -type="oracle_emd" -delete_monitored_targets -async
Target "host01.dbaplus.ca:3872:oracle_emd" deleted successfully
[oracle@oms1]$


We can also run EMCLI command to remove a specific target,
$EMCLI_HOME/bin/emcli delete_target -name="<target-name>" -type="<target-type>"


To remove database (instance)  TESTDB,
$EMCLI_HOME/bin/emcli delete_target -name="TESTDB" -type="oracle_database"


To remove ASM instance target +ASM_host01.dbaplus.ca,
$EMCLI_HOME/bin/emcli delete_target -name="+ASM_host01.dbaplus.ca" -type="osm_instance"


To Oracle listener LISTENER_host01.dbaplus.ca,
$EMCLI_HOME/bin/emcli delete_target -name="LISTENER_host01.dbaplus.ca" -type="oracle_listener"

Thursday, March 1, 2018

How to delete archive log files using PL/SQL


Although RMAN is always the first choice for most DBAs to delete archive log files, PL/SQL could be easier for this scenario,

User wants to switch an existing database from NOARCHIVELOG mode to ARCHIVELOG mode in order to increase the database availability because the database backup job has to shut down database when running in NOARCHIVELOG mode.

Monday, February 19, 2018

ORA-01017 invalid username/password after 11g database upgraded to 12.2 pluggable database

Oracle database 11.2.0.3 was upgraded to 12.2.0.1 and plugged into CDB as pluggable database with 'create pluggable database ... using ...' command. The container database (CDB) was creted with 12.2 dbca and all built-in users' passwords (sys,system,dbsnmp, etc) were set to same ones as in original 11g database.

Saturday, February 10, 2018

12.2 database Alert log shows ORA-20001: Latest xml inventory is not loaded into table

While 12.2 database is being started by srvctl, the alert log shows following messages,
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 777
ORA-06512: at "SYS.DBMS_QOPATCH", line 864
ORA-06512: at "SYS.DBMS_QOPATCH", line 2222
ORA-06512: at "SYS.DBMS_QOPATCH", line 740
ORA-06512: at "SYS.DBMS_QOPATCH", line 2247
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================

Thursday, January 25, 2018

AIX Disk Only Visible to One ASM Instance of Grid Infrastructure

Oracle Grid Infrastructure 12.2.0.1
AIX 7.1


New disks are added to both nodes of Grid Infrastructure, and the disk permission is configured properly,
[grid@host01]$ ls -l /dev/*hdisk[56]
brw-------      1 root      system         21, 26 Jan 22 10:58 /dev/hdisk5
brw-------      1 root      system         21, 25 Jan 22 10:58 /dev/hdisk6
crw-rw----    1 grid     asmadmin     21, 26 Jan 24 16:07 /dev/rhdisk5
crw-rw----    1 grid     asmadmin     21, 25 Jan 24 16:06 /dev/rhdisk6

[grid@host02]$ ls -l /dev/*hdisk2[56]
brw-------      1 root      system          21, 25 Jan 22 11:08 /dev/hdisk5
brw-------      1 root      system          21, 31 Jan 22 11:08 /dev/hdisk6
crw-rw----    1 grid     asmadmin     21, 25 Jan 22 11:08 /dev/rhdisk5
crw-rw----    1 grid     asmadmin     21, 31 Jan 24 14:49 /dev/rhdisk6

Saturday, January 20, 2018

Upgrading Grid Infrastructure from 12.1 to 12.2 failed with [INS-20802]

Customer is upgrading two-node Oracle Grid Infrastructure from version 12.1.0.2 to version 12.2.0.1, and gridSetup.sh returned error [INS-20802] during Preparing for deploying Oracle Grid Infrastructure Management Repository.

Wednesday, January 17, 2018

ORA-01403: no data found on ctxposup.sql while upgrading from 11g to 12.2

From: Oracle Database - Enterprise Edition - Version 11.2.0.3 + PSU 11.2.0.3.5 (14727310)
To:   Oracle Database - Enterprise Edition - Version 12.2.0.1.0
Operation System -  Solaris 10 1/13 s10s_u11wos_24a SPARC


It failed to upgrade database from 11.2.3 to 12.2 with error 'ORA-01403: no data found', and following message is found from log file 'Oracle_Server.log',

REASON:
      ERRORS FOUND: During Upgrade
         FILENAME: /u01/app/oracle/cfgtoollogs/dbua/upgrade2018-01-17_11-42-08-AM/DB01/catupgrd0.log AT LINE NUMBER: 825076
------------------------------------------------------
Identifier CONTEXT 18-01-17 12:37:20
SCRIPT      = [/u01/app/oracle/product/12.2.0/dbhome_1/ctx/admin/ctxposup.sql] <===== STATEMENT 1
ERROR      = [ORA-01403: no data found ORA-06512: at line 28
ORA-06512: at line 8
]
STATEMENT = [declare
  l_owner#  number;
  l_errnum  number;
  l_count   number;

begin
  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
  select idx_id into l_count from dr$index where        <===== STATEMENT 2
    idx_name = 'ENT_EXT_DICT_OBJ' and          
    idx_owner# = l_owner#;                       

  /* if this object does not exist yet, create it. */  
  if (0 = l_count) then
    CTX_DDL.create_policy('CTXSYS.ENT_EXT_DICT_OBJ',    <===== STATEMENT 3
      filter        => 'CTXSYS.NULL_FILTER',
      section_group => 'CTXSYS.NULL_SECTION_GROUP',
      lexer        => 'CTXSYS.BASIC_LEXER',
      stoplist        => 'CTXSYS.EMPTY_STOPLIST',
      wordlist        => 'CTXSYS.BASIC_WORDLIST'
  );
  end if;
exception
  when others then
    l_errnum := SQLCODE;
    if (l_errnum = -20000) then
      null;
    else
      raise;
    end if;
end;]

Script ctxposup.sql (STATEMENT 1) which upgrades Oracle Text raised ORA-01403 while running PL/SQL block listed in the above. Testing individual statements in the block as following:

sys@DB01> select user#  from sys."_BASE_USER" where name='CTXSYS';

     USER#
----------
       100

sys@DB01> ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;

Session altered.

sys@DB01> select idx_id from dr$index where
  2    idx_name = 'ENT_EXT_DICT_OBJ' and
  3    idx_owner# = 100;

no rows selected

The object CTXSYS.ENT_EXT_DICT_OBJ (a policy of Oracle Text) does not exist in the database. Therefore, STATEMENT 2 (select ... into ... from) returned ORA-01403. Apparently, The purpose of STATEMENT 2 is to test whether CTXSYS.ENT_EXT_DICT_OBJ exists, but listing records is not a better choice for exist-testing than counting the number of records. Let's try to replace STATEMENT 2 in script filectxposup.sql with following code

 select count(idx_id) into l_count from dr$index where

Start the upgrade again, it succeeded!

Monday, January 15, 2018

ORA-65500 When using DUPLICATE to Create Standby Database

Oracle Database - Enterprise Edition - Version 12.2.0.1 + RU 12.2.0.1.171017
Operation System - Oracle Solaris 11.3 SPARC

When running RMAN command DUPLICATE to create physical standby database as following:

RMAN> duplicate target database 
2>    for standby 
3>    from active database 
4>    dorecover 
5>    spfile 
6>      reset control_files 
7>      set db_unique_name='DBNEW' 
8>      set fal_server='DB01' 
9>    nofilenamecheck; 

using target database control file instead of recovery catalog

Starting Duplicate Db at 15-JAN-18
current log archived
... ...

Got error ORA-65500:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/15/2018 11:46:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 01/15/2018 11:46:19
RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name='DBNEW' comment='' scope=spfile
ORA-32017: failure in updating SPFILE
ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

Oracle reported this issue as Bug 20977794 which causes the ORA-65500 to be returned with any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN attempts to make a change to db_unique_name, this error will occur.

Current system is configured with Oracle Restart (Grid Infrastructure - standalone) 12.2.0.1. Although it is supposed to be fixed in 12.2.0.1 base release, the error still happened. Anyway, we can safely try to remove database resource from crs registry as workaround,

[oracle@dbaplus]$ srvctl config database
DBNEW

The database DBNEW does exist in the crs registry, try to remove it

[oracle@dbaplus]$ srlvctl remove database -db DBNEW
Remove the database DBNEW? (y/[n]) y
[oracle@dbaplus]$

Then DUPLICATE can run successfully.

Friday, December 15, 2017

ASM instance shows 'Down' in OEM 13.2 after upgraded to 12.2

Standalone Grid Infrastruture (Oracle Restart) is upgraded to 12.2 and everything works well. However, the ASM instance shows 'Down' in Enterprise Manager 13.2.

Checked 'Monitoring Configuration' of ASM target, all information is correct and 'Test Connection' succeeds.  We can log into ASM instance page in OEM to do management tasks (creat/drop diskgroup, etc). Everything looks good except the Target Status.

Log onto the host where ASM is running on, and run following commands from OEM agent home:

1. Get the target name of ASM instance
$ emctl config agent listtargets | grep ASM
[+ASM_host01.dbaplus.ca, osm_instance]



2. Find out the 'real time' results of the 'REsponse' metric (test connection from local agent to target)
$ emctl getmetric agent +ASM_host01.dbaplus.ca,osm_instance,Response
Oracle Enterprise Manager Cloud Control 12c Release 5 
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Status,oraerr
0
,Failed to connect: java.sql.SQLException: ORA-28040: No matching authentication protocol


Wow, '0' is returned with 'ORA-28040'. I believe many DBAs saw error 28040 when upgrading their databases to 12c. OEM agent (12c Release 5) is using old Oracle client (10g) to access ASM instance which is running on 12.2.

Solution:
Add following lines to $GRID_HOME/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
 

Test the connection again:
$emctl getmetric agent +ASM_host01.dbaplus.ca,osm_instance,Response
Oracle Enterprise Manager Cloud Control 12c Release 5 
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Status,oraerr
1
,
 


'1' is returned and indicates that the ASM  is up and running. 

And the ASM target shows 'Up' in OEM. 

Saturday, October 28, 2017

Alert Log Filter Expression Editable when view-mode in Monitoring Template of OEM 13.2

OMS 13.2.0.0.171017
DB Plug-in 13.2.2.0.170930
EM Agent 13.2.0.0.170930

A customer asked for helping to apply patch 24334660 on the Enterprise Manager 13.2. Before starting patching, check the Oracle Home patching level as following,
[oracle@oms1]$ /u01/app/oracle/product/em/middleware/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 13.9.1.3.0
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/em/middleware
Central Inventory : /u01/app/oracle/oraInventory
   from           : /u01/app/oracle/product/em/middleware/oraInst.loc
OPatch version    : 13.9.1.3.0
OUI version       : 13.9.1.0.0
OUI location      : /u01/app/oracle/product/em/middleware/oui
Log file location : /u01/app/oracle/product/em/middleware/cfgtoollogs/opatch/opatch2017-10-28_10-43-03AM_1.log


OPatch detects the Middleware Home as "/u01/app/oracle/product/em/middleware"

<<Output truncated>>

Patch  26625183     : applied on Fri Oct 20 16:32:23 EDT 2017
Unique Patch ID:  21574333
Patch description:  "EMBP Patch Set Update 13.2.0.0.171017"
   Created on 8 Oct 2017, 20:17:42 hrs PST8PDT
   Bugs fixed:
     26782785, 25341210, 26090681, 26680573, 25110496, 24953203, 24459731
     26364865, 25861385, 26027847, 26542313, 20511097, 25967196, 25230688
     24701883, 25326446, 25217253, 25534065, 23506806, 25244726, 25179524
     25136456, 22143858, 25354849, 25615522, 25674055, 25219896, 25255429
     23251874, 24334660, 24944461, 23743109, 24426862, 25701178, 25268183
     23549064, 25506784, 25497731, 25497622, 24789016, 24742172, 25355038
     25163555, 25044101, 24608207, 23619694, 24753427, 24734911, 23705752
     24917276, 24614251, 22901546, 23196054, 21470098

<<Output truncated>>


Apparently, the OMS home has been applied PSU 13.2.0.0.171017, which already included the fix of the bug. Customer performs following steps to reproduce his issue,

In Monitoring Templates window, click the name of desired template and get into template details window, then click the Glasses icon in 'Edit' column besides metric 'Generic Alert Log Error'. In the new windows, the 'Alert Log Filter Expression' can be changed, but the new value cannot be saved (retained) after click OK. The value goes back to old value when come into this window again. It seems a bug/defect here. Actually, the window is a view-mode (read only) window with title 'View Advanced Settings: Generic Alert Log Error'. Yes, it gives you opportunity to type words in view-mode window, but does not change anything, a funny behaviour.

It is not a correct way to edit a metric in a monitoring template by clicking the template name. Instead, select the record (row) of the template and click 'Edit' button. In 'Edit Monitoring Template' window, the metric can be changed permanently.

Tuesday, August 15, 2017

Birthday Attacks against TLS ciphers Used by OEM 13.2 Agent

Oracle Enterprise Manager 13.2 uses following cipher suites as default value for SSL communication,
 SSL_RSA_WITH_RC4_128_MD5, SSL_RSA_WITH_RC4_128_SHA and SSL_RSA_WITH_3DES_EDE_CBC_SHA
 
Which support ciphers as listed by script CipherScan.bsh (script details can be found at the end of this post),
[oracle@host01]$ ./CipherScan.bsh host01.dbaplus.ca:3872
 Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
     ECDHE-RSA-AES128-SHA256
     ECDHE-RSA-AES128-SHA
     DHE-RSA-AES128-SHA256
     DHE-RSA-AES128-SHA
     AES128-SHA256
     AES128-SHA
     DES-CBC3-SHA

Here, cipher DES-CBC3-SHA could introduce a security threat of 'Birthday Attack against TLS ciphers with 64bit block size vulnerability'. Remote attackers can obtain cleartext data via a birthday attack against a long-duration encrypted session of all versions of SSL/TLS protocol supporting cipher suites which use DES, 3DES, IDEA or RC2 as the symmetric encryption cipher in CBC mode.

It can be disabled/removed by configuring agent's properties. Before changing the agent property, one of following method can be used to check current setting,
* Run command: <AGENT_INST_HOME>/bin/emctl getproperty agent -name SSLCipherSuites or
* Check agent property file by running:  grep -i <AGENT_INST_HOME>/sysman/config/emd.properties
 
[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites is unset; default value is SSL_RSA_WITH_RC4_128_MD5:SSL_RSA_WITH_RC4_128_SHA:SSL_RSA_WITH_3DES_EDE_CBC_SHA
[oracle@host01]$
[oracle@host01]$ grep -i 'SSLCipherSuites' ./sysman/config/emd.properties
[oracle@host01]$

The property is using default value and not set yet.
Remove weak cipher suite by setting SSLCipherSuites property of agent,
[oracle@host01]$ cd /u01/software/em/agent/agent_inst                      
[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

If the Agent is running on AIX platform, use the cipher SSL_RSA_WITH_AES_128_CBC_SHA only.
[oracle@host01]$ ./bin/emctl setproperty agent -name SSLCipherSuites -value SSL_RSA_WITH_AES_128_CBC_SHA


Check current value,
[oracle@host01]$ cd /u01/software/em/agent/agent_inst
[oracle@host01]$ ./bin/emctl getproperty agent -name SSLCipherSuites
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$
[oracle@host01]$ grep -i ./sysman/config/'SSLCipherSuites' emd.properties
SSLCipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA:TLS_RSA_WITH_AES_256_CBC_SHA:RSA_WITH_AES_256_CBC_SHA256
[oracle@host01]$

Restart agent,
[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.
[oracle@host01]$
[oracle@host01]$ /u01/software/em/agent/agent_13.2.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting agent ............... started.


Verify the supported ciphers,
[oracle@host01]$ ./CipherScan.bsh host01.dbaplus.ca:3872
 Following Cipher(s) is/are supported on server host01.dbaplus.ca:3872
     AES128-SHA

There is no threat (weak cipher) any more.
 
Appendex.  Script file CipherScan.bsh
#!/usr/bin/bash
# -----------------------------------------------
# Scan available Cipher on given server with port
# Usage:
#    CipherScan.bsh  <SERVER_IP>:<PORT>
#
# For example,
#    ./CipherScan.bsh  host01.dbaplus.ca:3872
# -----------------------------------------------
SERVER=$1
CIPHER_SUPPORTED=$(openssl ciphers 'ALL:eNULL' | sed -e 's/:/ /g')
echo -e "\n Following Cipher(s) is/are supported on server $SERVER \n"
for cipher in ${CIPHER_SUPPORTED[@]}
do
    result=$(echo -n | openssl s_client -cipher "$cipher" -connect $SERVER 2>&1)
    if [[ "$result" =~ "Connection refused" ]] ; then
        echo  $result
        break
    fi
    if ! [[ "$result" =~ ":error:" ]] ; then
          echo "     "$cipher
    fi
done

Wednesday, July 26, 2017

Configure SQL Server 2016 Always On with Core Version of Windows Server 2016 without Active Directory Domain

This is a step by step demonstration to help database administrator deploy SQL Server 2016 Always On high availability solution on core version of Windows server 2016 without Active Directory domain. The demonstration environment will be created as following,

Tuesday, February 21, 2017

Oracle Rules Manager 11.2.0.4.0 INVALID with invalid JAVA Classes

Oracle database 11.2.0.4 was created with INVALID component 'Oracle Rules Manager',  
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             INVALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID

Find out invalid objects owned by schema 'EXFSYS' which could INVALID the component Oracle Rules Manager,
system@DB01> select object_name,object_type,status
  2  from dba_objects
  3  where owner='EXFSYS' and status='INVALID';
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
/53ad31cb_TagsLocator          JAVA CLASS          INVALID
/357abd97_XPathFilter          JAVA CLASS          INVALID

Check errors of found invalid JAVA CLASSes,
system@DB01> select name,text
  2  from dba_errors
  3  where owner='EXFSYS' and name in ('/53ad31cb_TagsLocator','/357abd97_XPathFilter');
NAME                      TEXT
------------------------- ------------------------------------------------------------
/357abd97_XPathFilter     ORA-29534: referenced object EXFSYS.oracle/expfil/xpath/TagsLocator could not be resolved
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/DOMParser could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLParseException could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLDocument could not be found

Oracle Rules Manager is in INVALID status because the referenced object/names created by installing XDK cannot be resolved/found. EXFSYS (owner of Oracle Rules Manager) JAVA Classes are dependent on XDK. Therefore installing XDK will validate Oracle Rules Manager,
conn / as sysdba
@?/xdk/admin/initxml.sql
@?/rdbms/admin/utlrp.sql

Check component status,
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             VALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID


Thursday, January 26, 2017

OMS version not checked yet while emctl upload agent

After OEM managed host was bounced, the agent status shows 'DOWN' in OEM. It happened because old version agent which pointed to old OMS server was started.

Find out if OEM agent is running on target,
$ ps -ef | grep emagent
  oracle 63111344 41484628   0 14:10:15  pts/0  0:00 grep emagent
  oracle  7143666        1   0 13:55:39  pts/4  0:00 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/perl/bin/perl /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emwd.pl agent /u01/app/oracle/product/12.1.0/agent/agent_inst/sysman/log/emagent.nohup
  oracle 26280338  7143666   0 13:55:51  pts/4  0:06 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdk/bin/java -Xmx160M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=7143666 -cp /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/ucp/lib/ucp.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.http_client_11.1.1.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/xmlparserv2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/jsch.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/optic.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.dms_11.1.1/dms.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jlib/gcagent_core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain

There is an instance of OEM agent running from Oracle Home '/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0'. Test the communication between managed host and the Management Service,
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors. (OMS_DOWN)

$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl pingOMS
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: Failure connecting to https://oem.dbaplus.ca:1159/empbs/upload , err Connection refused

Command 'emctl upload agent' returned error 'OMS version not checked yet', and 'emctl pingOMS' is complaining that OMS server cannot be connected. However, current OMS server is oms.dbaplus.ca and not oem.dbaplus.ca which is retired. Apparently, bouncing host automatically started an old (obsolete) agent which worked with old OMS server.

Let's fix it,
$ cat /etc/oragchomelist

/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0:/u01/app/oracle/product/12.1.0/agent/agent_inst

/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst

The first entry in file /etc/oragchomelist makes OS start old agent (version 12.1.0.4), remove it from /etc/oragchomelist and restart host. After host is up, check agent status,
$ cat /etc/oragchomelist
/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst

$ ps -ef | grep emagent
  oracle 57082074 41484628   0 16:21:18  pts/0  0:00 grep emagent
  oracle 63766548        1   0 15:00:53  pts/0  0:00 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/perl/bin/perl /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emwd.pl agent /u01/app/oracle/product/em/agent/agent_inst/sysman/log/emagent.nohup
  oracle 29950448 63766548   0 15:01:00  pts/0  1:20 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/jdk/bin/java -Xmx183M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=63766548 -cp /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/ucp/lib/ucp.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/jsch-0.1.53.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/com.oracle.http_client.http_client_12.1.3.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.xdk_12.1.3/xmlparserv2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.dms_12.1.3/dms.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/lib/optic.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib/gcagent_core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain

$ /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

$ /u01/app/oracle/product/em/agent/agent _13.2.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.2.0.0.0
OMS Version            : 13.2.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/em/agent/agent_inst
Agent Log Directory    : /u01/app/oracle/product/em/agent/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0
Core JAR Location      : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib
Agent Process ID       : 11665618
Parent Process ID      : 13238612
Agent URL              : https://host01.dbaplus.ca:3872/emd/main/
Local Agent URL in NAT : https://host01.dbaplus.ca:3872/emd/main/
Repository URL         : https://oms.dbaplus.ca:4903/empbs/upload
Started at             : 2017-01-26 14:25:33
Started by user        : oracle
Operating System       : AIX version 7.1 (ppc64)
Number of Targets      : 23
Last Reload            : (none)
Last successful upload                       : 2017-01-26 14:27:22
Last attempted upload                        : 2017-01-26 14:27:22
Total Megabytes of XML files uploaded so far : 0.62
Number of XML files pending upload           : 734
Size of XML files pending upload(MB)         : 0.31
Available disk space on upload filesystem    : 59.62%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2017-01-26 14:26:26
Last successful heartbeat to OMS             : 2017-01-26 14:26:26
Next scheduled heartbeat to OMS              : 2017-01-26 14:27:28

---------------------------------------------------------------
Agent is Running and Ready

Agent is running on managed host, and it also shows 'UP' in OEM.

Monday, August 8, 2016

Automated SQL Tuning Task Failed with ORA-00600: [kpdbSwitchPreRestore: Txn]

Database: Oracle 12.1.0.2 RAC + Proactive Bundle Patch 12.1.0.2.160419
Operation System:  AIX 7.1


Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2016-08-07 22:00:21.020000 -05:00
 <<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc  (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].


The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.

Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                ENABLED

system@PDB01> begin
  2  dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
  3                               operation=>null,
  4                               window_name=>null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               DISABLED
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                DISABLED


Usage notes for dbms_auto_task_admin.disable,

 * If operation and window_name are both NULL, the client is disabled.
 * If operation is not NULL, window_name is ignored and the operation is disabled
 * If operation is NULL and window_name is not NULL, the client is disabled in the specified window.

Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),

SYSTEM@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               DISABLED

SYSTEM@PDB01> alter session set container=cdb$root;

Session altered.

SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS 
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               ENABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);

PL/SQL procedure successfully completed.

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS  
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               DISABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

Monday, March 21, 2016

RMAN got ORA-00245 while backing up RAC control file

When backing up control file for RAC databases, you could got ORA-00245 as following,
RMAN> backup device type sbt current controlfile ;

Starting backup at 2016-03-20 19:08:49
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1941 instance=racdb_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 03/20/2016 19:08:50
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

Friday, October 2, 2015

Invalid password while logging 12c ASM instance

Customer complains that he cannot log into 12c ASM instance because of invalid username/password, but he is pretty sure username and password are correct.
$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app
$
$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 2 10:26:38 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys as sysasm
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Try to log in through OS authentication and reset the password:
SQL> conn / as sysasm
Connected.

SQL> alter user sys identified by abc123;
alter user sys identified by abc123
                             *
ERROR at line 1:
ORA-01918: user 'INTERNAL' does not exist

SQL> select * from v$pwfile_users;

no rows selected

Looks like the password file is missing:
$ ls -l $ORACLE_HOME/dbs
total 18
-rw-rw----   1 oragrid  oracle      2180 Jul 24 13:32 ab_+ASM.dat
-rw-rw----   1 oragrid  oracle      1544 Oct  2 10:28 hc_+ASM.dat
-rw-r--r--   1 oragrid  oracle      2992 Feb  3  2012 init.ora

Ok, the password file is not there as before. And we know it can be stored in ASM diskgroup from 12c, check if it is the case:
$ srvctl config asm -a
ASM home: 
Password file:
ASM listener: LISTENER
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.885907629
ASM diskgroup discovery string:
ASM is enabled.
ASM is individually enabled on nodes:
ASM is individually disabled on nodes:
$

The entry 'Password file' is empty, it is not expected configuration if password file does not exist in $ORACLE_HOME/dbs. Let us see if there is password file in ASM:
$ asmcmd
ASMCMD> ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  EXTERN  N      FRA/
ASMCMD> cd DATA
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    ASM/
                                            Y    DB01/
PASSWORD  UNPROT  COARSE   JUL 14 09:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.885030391
ASMCMD> exit

The password file 'orapwasm' is in 'DATA' diskgroup, update Oracle Restart registry:
$ srvctl modify asm -pwfile +DATA/orapwasm
$ srvctl config asm -a
ASM home: 
Password file: +DATA/orapwasm
ASM listener: LISTENER
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.885907629
ASM diskgroup discovery string:
ASM is enabled.
ASM is individually enabled on nodes:
ASM is individually disabled on nodes:

try logging again:
$ sqlplus sys as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 16 11:16:04 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  TRUE  FALSE FALSE FALSE          0
ASMSNMP                        TRUE  FALSE FALSE FALSE FALSE FALSE          0

It works.

Monday, September 21, 2015

Using Corrective Action to capture and email database status

As DBA, were you asked questions like:
I was blocked by other database sessions last night, could you please check who blocked me and what he was doing at that time?
My query ran into an temp tablespace space error, could you please check who was using large temporary space at that moment?
They are not hard questions, but you will find that it is not easy to answer them unless you have proactive consideration.

Here, I am going to show how easy it is to do that using Corrective Action in Oracle Enterprise Manager Cloud Control 12c.

Thursday, July 2, 2015

Agent status shows 'Availability Evaluation Error' on EM 12c Console

Background
During deploying agent 12.1.0.4 to new host for EM 12.1.0.4, the local firewall was enabled by accident and blocked incoming access. The deployment is successful. However, OEM shows error:
'Unable to connect to the agent at https://host01.dbaplus.ca:3872/emd/main/ [Connection establishment timed out])'

After stopping firewall on the new host, agent status is shown 'Availability Evaluation Error' on EM 12c Console.
Diagnose
On the new host:
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.4.0
OMS Version            : 12.1.0.4.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/12.1.0/agent/agent_inst
Agent Log Directory    : /u01/app/oracle/product/12.1.0/agent/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0
Agent Process ID       : 8366
Parent Process ID      : 8280
Agent URL              : https://host01.dbaplus.ca:3872/emd/main/
Local Agent URL in NAT : https://host01.dbaplus.ca:3872/emd/main/
Repository URL         : https://omshost.dbaplus.ca:1159/empbs/upload
Started at             : 2015-07-02 11:29:46
Started by user        : oracle
Operating System       : Linux version 2.6.39-400.17.1.el6uek.x86_64 (amd64)
Last Reload            : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 21.46%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2015-07-02 11:30:49
Last successful heartbeat to OMS             : 2015-07-02 11:30:49
Next scheduled heartbeat to OMS              : 2015-07-02 11:31:49

---------------------------------------------------------------
Agent is Running and Ready

We can find that 'Last successful upload' is '(none)', it is supposed to be a valid date & time. Try to manually upload:
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

It does not help. 'Last successful upload' is still '(none)' and agent status is still shown 'Availability Evaluation Error'.
Try to re-synchronize the agent on OMS server:
$ . oraenv
ORACLE_SID = [oracle] ? OMS
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/OMS/oms
The Oracle base has been set to 
$ which emcli
/u01/app/oracle/product/12.1.0/OMS/oms/bin/emcli
$ 
$ emcli  login -username=SYSMAN
Enter password 

Login successful
$ emcli sync
Synchronized successfully
$ emcli resyncAgent -agent=host01.dbaplus.ca:3872
Resync job RESYNC_20150702142805361 successfully submitted

Now agent is shown UP on EM 12c console.

Wednesday, June 10, 2015

Failed to retrieve the password file location used by ASM asm

After Oracle Restart (Grid Infrastructure standalone) is upgraded from 11g to 12c, srvctl get errors:
$ srvctl config asm
ASM home: 
PRCA-1057 : Failed to retrieve the password file location used by ASM asm
PRCR-1097 : Resource attribute not found: PWFILE

Check environment and password file:
$ echo $ORACLE_HOME
/u01/app/grid/product/12.1.0/grid

$ ls -l /u01/app/grid/product/12.1.0/grid/dbs
total 28
-rw-rw---- 1 grid oinstall 2783 Jun  9 14:27 ab_+ASM.dat
-rw-rw---- 1 grid oinstall 1544 Jun  9 14:27 hc_+ASM.dat
-rw-r--r-- 1 grid oinstall 2992 Feb  3  2012 init.ora
-rw-r----- 1 grid oinstall 8704 Jun  9 14:10 orapw+ASM
-rw-r----- 1 grid oinstall 1536 Jun  9 14:27 spfile+ASM.ora

The password file was created and ORACLE_HOME is set correctly. Checked with DBA, the upgrade was done successfully. There was no errors/exceptions during upgrade.
Oracle Support explains that:
12c ASM instance has a attribute called PWFILE(password file), but 11g ASM does not. Looks like the upgrade utility does not create the new attribute for an ASM instance which is upgraded from 11g.
Try to manually re-create the ora.asm resource with -pwfile option:
$ crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on host01

$ srvctl stop asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg', but the force option was not specified
$ srvctl stop asm -f
$ 
$ srvctl config asm
PRCR-1001 : Resource ora.asm does not exist
$ 
$ srvctl add asm -listener LISTENER -spfile /u01/app/grid/product/12.1.0/grid/dbs/spfile+ASM.ora -pwfile /u01/app/grid/product/12.1.0/grid/dbs/orapw+ASM

Test if it is fixed:
$ srvctl sconfig asm
ASM home: 
Password file: /u01/app/grid/product/12.1.0/grid/dbs/orapw+ASM
ASM listener: LISTENER
Spfile: /u01/app/grid/product/12.1.0/grid/dbs/spfile+ASM.ora
ASM diskgroup discovery string: ++no-value-at-resource-creation--never-updated-through-ASM++

There is no error any more.

Wednesday, April 29, 2015

Insufficient archive log space caused ORA-03113

Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.

$ srvctl sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3


Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:

ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038


Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.

Backup archive log and release space by running:

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes

RMAN> backup archivelog all delete all input;

... ...


Try to open database:

RMAN> alter database open;

database opened


Database is opened successfully.

Friday, February 13, 2015

Integrating BI Publisher with Enterprise Manager using the configureBIP Script

As we know, Oracle has deprecated Information Publisher (IP) Reports for Enterprise Manager 12c and introduce new report tool BI Publisher (BIP) Enterprise Reports. Here, I am going to configure BIP after OEM 12c (12.1.0.4) has been installed and used for a while.

Configure environment and set ORACLE_HOME to OMS installation path:
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/oms12104/oms
$ export PATH=$ORACLE_HOME/bin:$PATH
$ which emctl
/u01/app/oracle/product/12.1.0/oms12104/oms/bin/emctl

Backup OEM using command: emctl exportconfig oms
$ emctl exportconfig oms -dir /u01/app/oracle/OMS_BACKUP -oms_only -keep_host
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
ExportConfig started...
Backup directory is /u01/app/oracle/OMS_BACKUP
Performing OMS backup...
Exporting emoms properties...
Exporting secure properties...

Export has determined that the OMS is not fronted
by an SLB. You have chosen to export the local
hostname. The exported data may ONLY be imported
on a host with the same hostname. Please see the
EM Advanced Configuration Guide for more details.

Warning: Could not export files for module bip: Unable to read file /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/embip.properties for exporting
Exporting configuration for pluggable modules...
Preparing archive file...
Backup has been written to file: /u01/app/oracle/OMS_BACKUP/opf_OMS_20150210_111433.bka

The export file contains sensitive data.
You must keep it secure.

ExportConfig completed successfully!

Configure BIP using script configureBIP
$ cd $ORACLE_HOME/bin
$
$ ./configureBIP
Configuring BI Publisher Version "11.1.1.7.0" to work with Enterprise Manager
Logging started at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log.
Before this command is run, a backup of Enterprise Manager should be performed using the :emctl exportconfig oms: command. Have you made a valid backup of Enterprise Manager (yes/no) [no] ? yes
Enter sysdba user name (sys):sys
Enter sysdba user password:
Enter Administration Server user password:
Configuring BI Publisher in Oracle Home located in /u01/app/oracle/product/12.1.0/oms12104/Oracle_BI1 ...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Creating Tablespaces
Validating and Creating Tablespaces
Repository Creation Utility - Create
Repository Create in progress.
Percent Complete: 0
Percent Complete: 10
Percent Complete: 30
Percent Complete: 50
Percent Complete: 50
Percent Complete: 100
Repository Creation Utility: Create - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.dbaplus.ca)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEMREP)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/emBIPLATFORM.log
Component schemas created:
Component Status Logfile
Business Intelligence Platform Success /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/biplatform.log

Repository Creation Utility - Create : Operation Completed
Successfully created SYSMAN_BIPLATFORM schema...
Enter an integer between 9701 and 49152 for the BI Publisher HTTP server port. (9701):
Enter an integer between 9702 and 49152 for the BI Publisher HTTPS server port. (9702):
Extending domain with BI Publisher. This operations can take some time. Do not interrupt this command while it is running...
Locking Enterprise Manager ...
OMS Console is locked. Access the console over HTTPS ports.
Restart OMS.
Restarting Enterprise Manager ...
Stopping Enterprise Manager, this can take some time ...
Failed to stop Enterprise Manager. Diagnostic code 1.
See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
Error extending domain
See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.

OMG, a bad luck! Check log file for what happened,
$ tail -20 /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log

[2015-02-10T11:30:51.192-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Stopping WebTier...[[

]]
[2015-02-10T11:30:55.221-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Error Occurred: Cannot run program "/u01/app/oracle/product/12.1.0/gc_inst/WebTierIH1/bin/opmnctl": error=12, Not enough space[[

]]
[2015-02-10T11:30:55.222-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Please check /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details[[

]]
[2015-02-10T11:30:55.265-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: lockAndBounceEM] Failed to stop Enterprise Manager. Diagnostic code 1.
[2015-02-10T11:30:55.273-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: extendDomainWithBIP] See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
[2015-02-10T11:30:55.274-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Error extending domain
[2015-02-10T11:30:55.275-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Fatal error:[[
java.lang.Exception: See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:2370)
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1531)

At the begining of the error, I found messages: 'opmnctl', 'Not enough space', 'Failed to stop Enterprise Manager'. What a familiar look! Yes, it is familiar. Let's try to fix it following "Got ‘Not enought space’ while ‘emctl stop oms’". Then, restart the process. Of course, I have to clean/rollback the failed configuration first before re-running configureBIP.

Clean/rollback failed configureBIP run

Stop BIP server
$ emctl stop oms -bip_only
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
BI Publisher Server is Down

Unregister BIP from Enterprise Manager
$ emcli login -username=sysman
Enter password :

Login successful
$ emcli sync
Synchronized successfully

$ emcli unregister_bipublisher -force
Warning: BI Publisher was not registered for use with Enterprise Manager, no action taken.

BIP was not registered with Enterprise Manager yet, but it does not really matter, just go ahead.
Drop schema created by script configureBIP from Enterprise Manager repository database
$ sqlplus sys@OEMREP as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Feb 13 08:39:01 2015

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options

SQL> drop user SYSMAN_BIPLATFORM cascade;

User dropped.

SQL> select comp_id,COMP_NAME,owner,version,status from system.schema_version_registry where comp_id = 'BIPLATFORM';

COMP_ID COMP_NAME OWNER VERSION STATUS
--------------- ------------------------------ ------------------------------ ------------------------------ -----------
BIPLATFORM OracleBI and EPM SYSMAN_BIPLATFORM 11.1.1.7.0 VALID

SQL> delete from system.schema_version_registry where comp_id = 'BIPLATFORM';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select comp_id,COMP_NAME,owner,version,status from system.schema_version_registry where comp_id = 'BIPLATFORM';

no rows selected

Re-run script configureBIP to configure BIP with Enterprise Manager
$ ./configureBIP
Configuring BI Publisher Version "11.1.1.7.0" to work with Enterprise Manager
Logging started at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150213084523.log.
Before this command is run, a backup of Enterprise Manager should be performed using the :emctl exportconfig oms: command. Have you made a valid backup of Enterprise Manager (yes/no) [no] ? yes
Enter sysdba user name (sys):
Enter sysdba user password:
Enter Administration Server user password:
Configuring BI Publisher in Oracle Home located in /u01/app/oracle/product/12.1.0/oms12104/Oracle_BI1 ...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Creating Tablespaces
Validating and Creating Tablespaces
Repository Creation Utility - Create
Repository Create in progress.
Percent Complete: 0
Percent Complete: 10
Percent Complete: 30
Percent Complete: 50
Percent Complete: 50
Percent Complete: 100
Repository Creation Utility: Create - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.dbaplus.ca)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEMREP)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/emBIPLATFORM.log
Component schemas created:
Component Status Logfile
Business Intelligence Platform Success /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/biplatform.log

Repository Creation Utility - Create : Operation Completed
Successfully created SYSMAN_BIPLATFORM schema...
Enter an integer between 9701 and 49152 for the BI Publisher HTTP server port. (9701):
Enter an integer between 9702 and 49152 for the BI Publisher HTTPS server port. (9702):
Extending domain with BI Publisher. This operations can take some time. Do not interrupt this command while it is running...
Locking Enterprise Manager ...
OMS Console is locked. Access the console over HTTPS ports.
Restart OMS.
Restarting Enterprise Manager ...
Stopping Enterprise Manager, this can take some time ...
Starting Enterprise Manager. This operation can take some time. Do not interrupt this command while it is running.
OMS Started Successfully
BI Publisher server named :BIP: running at https://host01.dbaplus.ca:9702/xmlpserver.
Registering BI Publisher with Enterprise Manager and deploying reports...
Performing automatic backup of Enterprise Manager using the command :emctl exportconfig oms:.
Successfully backed up Enterprise Manager. The backup file is located in the INSTANCE_HOME sysman backup directory.
Successfully setup BI Publisher with Enterprise Manager

Now, we can use BI Publisher to do report instead of Information Publisher.