Bug 1328386

Summary: resource agent has trouble creating monitoring user when starting multi-tenant container DB "ORA-65140: invalid common profile name"
Product: Red Hat Enterprise Linux 7 Reporter: Josef Zimek <pzimek>
Component: resource-agentsAssignee: Oyvind Albrigtsen <oalbrigt>
Status: CLOSED ERRATA QA Contact: cluster-qe <cluster-qe>
Severity: high Docs Contact:
Priority: high    
Version: 7.1CC: agk, cluster-maint, fdinitto, lmiksik, mnovacek
Target Milestone: rc   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: resource-agents-3.9.5-81.el7 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
: 1374655 (view as bug list) Environment:
Last Closed: 2016-11-04 00:02:56 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On: 1358895    
Bug Blocks: 1374655    

Description Josef Zimek 2016-04-19 09:35:53 UTC 
Description of problem:

If Oracle 12c DB is configured as multi-tenant container database, resource agent has trouble creating monitoring user when starting DB

Feb 24 13:13:28 rhelnode oracle(oracle12_baza)[5032]: ERROR: could not create OCFMONPROFILE oracle profile
Feb 24 13:13:28 rhelnode oracle(oracle12_baza)[5032]: ERROR: sqlplus output: create profile OCFMONPROFILE limit FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED#012               *#012ERROR at line 1:#012ORA-65140: invalid common profile name

When trying to create profile by hand directly in database command that resource agent script uses fails.

SQL> create profile OCFMONPROFILE limit FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED;
create profile OCFMONPROFILE limit FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED
               *
ERROR at line 1:
ORA-65140: invalid common profile name

But when command is adjusted to comply with container DB it passes.

SQL> create profile C##OCFMONPROFILE limit FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED;

Profile created.


When Oracle12c DB is not configured as container DB starts ok. So problem seems to be that resource agent doesn't check if the DB is multi-tenant and thus doesn't use the "C##" prefix in case it is multi-tenant container database.



Version-Release number of selected component (if applicable):

RHEL 7.1 
resource-agents-3.9.5-54.el7_2.1.x86_64


How reproducible:
always




Additional info:

https://www.oraexcel.com/oracle-12cR1-ORA-65140
Comment 2 Oyvind Albrigtsen 2016-04-19 12:41:34 UTC 
https://github.com/ClusterLabs/resource-agents/pull/790
Comment 6 Oyvind Albrigtsen 2016-08-31 07:20:19 UTC 
New build with the following 2 patches that solves the issue:
https://github.com/ClusterLabs/resource-agents/pull/843
https://github.com/ClusterLabs/resource-agents/pull/844
Comment 7 michal novacek 2016-08-31 09:37:51 UTC 
I have verified that it is possible to start oracle resource agent with prepended C## to monprofile and monuser parameter in resource-agents-3.9.5-81.el7.x86_64

----

common setup:

 * install oracle container database with cdb
    (https://www.redhat.com/en/files/resources/en-rhel-deploy-oracle-rac-database-12c-rhel-7.pdf,
    section 4.1.4 (page 73)

 * create resource agent and disable it

[root@kiff-03 ~]# pcs resource show oracle-cdb
 Resource: oracle-cdb (class=ocf provider=heartbeat type=oracle)
  Attributes: sid=cdb monprofile=mprofile monuser=OCFMON
  Operations: start interval=0s timeout=120 (oracle-cdb-start-interval-0s)
              stop interval=0s timeout=120 (oracle-cdb-stop-interval-0s)
              monitor interval=120 timeout=30 (oracle-cdb-monitor-interval-120)

 * check that oracle user can connect

[oracle@kiff-03 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@kiff-03 ~]$ export ORACLE_SID=cdb
[oracle@kiff-03 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 29 16:10:16 2016

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 3.0400E+10 bytes
Fixed Size                  7660704 bytes
Variable Size            4764732256 bytes
Database Buffers         2.5568E+10 bytes
Redo Buffers               59445248 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

before the fix (resource-agents-3.9.5-81.el7.x86_64)
====================================================

>> with profile not prepended with C## resource agent will not start (no useful
>> message given), incorrect sql command output
[root@kiff-03 ~]# pcs resource enable oracle-cdb

[root@kiff-03 ~]# ps axf | grep cdb
24447 pts/3    S+     0:00          \_ grep --color=auto cdb

[root@kiff-03 ~]# pcs resource | grep oracle-cdb
 oracle-cdb     (ocf::heartbeat:oracle):        Stopped

[root@kiff-03 ~]# grep oracle-cdb /var/log/messages | tail -n 50
...
 Aug 30 10:59:20 kiff-03 pengine[2045]:  notice: Start   oracle-cdb#011(kiff-03.cluster-qe.lab.eng.brq.redhat.com)
Aug 30 10:59:20 kiff-03 crmd[2046]:  notice: Initiating monitor operation oracle-cdb_monitor_0 locally on kiff-03.cluster-qe.lab.eng.brq.redhat.com | action 9
Aug 30 10:59:20 kiff-03 crmd[2046]:  notice: Initiating monitor operation oracle-cdb_monitor_0 on kiff-01.cluster-qe.lab.eng.brq.redhat.com | action 8
Aug 30 10:59:20 kiff-03 oracle(oracle-cdb)[29646]: INFO: oracle process not running
Aug 30 10:59:20 kiff-03 crmd[2046]:  notice: Result of probe operation for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: 7 (not running) | call=2262 key=oracle-cdb_monitor_0 confirmed=true cib-update=4501
Aug 30 10:59:20 kiff-03 crmd[2046]:  notice: Initiating start operation oracle-cdb_start_0 locally on kiff-03.cluster-qe.lab.eng.brq.redhat.com | action 26
>>> Aug 30 10:59:32 kiff-03 oracle(oracle-cdb)[29686]: ERROR: could not create OCFMONPROFILE oracle profile
>>> Aug 30 10:59:32 kiff-03 oracle(oracle-cdb)[29686]: ERROR: sqlplus output: create profile OCFMONPROFILE limit FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED#012               *#012ERROR at line 1:#012ORA-65140: invalid common profile name
Aug 30 10:59:32 kiff-03 crmd[2046]:  notice: Result of start operation for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: 1 (unknown error) | call=2263 key=oracle-cdb_start_0 confirmed=true cib-update=4502
Aug 30 10:59:32 kiff-03 crmd[2046]: warning: Action 26 (oracle-cdb_start_0) on kiff-03.cluster-qe.lab.eng.brq.redhat.com failed (target: 0 vs. rc: 1): Error
Aug 30 10:59:32 kiff-03 crmd[2046]:  notice: Transition aborted by operation oracle-cdb_start_0 'modify' on kiff-03.cluster-qe.lab.eng.brq.redhat.com: Event failed | magic=0:1;26:2233:0:a939e526-581d-4744-9f8e-93e3e0ff6fd4 cib=0.121.4 source=match_graph_event:310 complete=false
Aug 30 10:59:32 kiff-03 crmd[2046]: warning: Action 26 (oracle-cdb_start_0) on kiff-03.cluster-qe.lab.eng.brq.redhat.com failed (target: 0 vs. rc: 1): Error
Aug 30 10:59:32 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 10:59:32 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 10:59:32 kiff-03 pengine[2045]:  notice: Recover oracle-cdb#011(Started kiff-03.cluster-qe.lab.eng.brq.redhat.com)
Aug 30 10:59:32 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 10:59:32 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 10:59:32 kiff-03 pengine[2045]: warning: Forcing oracle-cdb away from kiff-03.cluster-qe.lab.eng.brq.redhat.com after 1000000 failures (max=1000000)
Aug 30 10:59:32 kiff-03 pengine[2045]:  notice: Stop    oracle-cdb#011(kiff-03.cluster-qe.lab.eng.brq.redhat.com)
Aug 30 10:59:32 kiff-03 crmd[2046]:  notice: Initiating stop operation oracle-cdb_stop_0 locally on kiff-03.cluster-qe.lab.eng.brq.redhat.com | action 6
Aug 30 10:59:39 kiff-03 oracle(oracle-cdb)[30516]: INFO: Oracle instance cdb stopped: ORACLE instance shut down.
Aug 30 10:59:40 kiff-03 oracle(oracle-cdb)[30516]: INFO: Cleaning up for cdb
Aug 30 10:59:40 kiff-03 crmd[2046]:  notice: Result of stop operation for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: 0 (ok) | call=2264 key=oracle-cdb_stop_0 confirmed=true cib-update=4506

>> correct the user the way oracle likes it (prepend C##), the resource does
>> not start anyway
[root@kiff-03 ~]# pcs resource update oracle-cdb monprofile=C##mprofile
[root@kiff-03 ~]# pcs resource | grep oracle-cdb
oracle-cdb     (ocf::heartbeat:oracle):        Stopped

corrected version (resource-agents-3.9.5-80.el7.x86_64)
=======================================================

>> with profile not prepended with C## resource agent will not start but it
>> gives you clear reason why
[root@kiff-03 ~]# pcs resource enable oracle-cdb

[root@kiff-03 ~]# ps axf | grep cdb
24447 pts/3    S+     0:00          \_ grep --color=auto cdb

[root@kiff-03 ~]# pcs resource | grep oracle-cdb
 oracle-cdb     (ocf::heartbeat:oracle):        Stopped

[root@kiff-03 ~]# grep oracle-cdb /var/log/messages | tail -n 50
...
Aug 30 09:40:04 kiff-03 pengine[2045]:  notice: Start   oracle-cdb#011(kiff-03.cluster-qe.lab.eng.brq.redhat.com)
Aug 30 09:40:04 kiff-03 crmd[2046]:  notice: Initiating monitor operation oracle-cdb_monitor_0 locally on kiff-03.cluster-qe.lab.eng.brq.redhat.com | action 9
Aug 30 09:40:04 kiff-03 crmd[2046]:  notice: Initiating monitor operation oracle-cdb_monitor_0 on kiff-01.cluster-qe.lab.eng.brq.redhat.com | action 8
Aug 30 09:40:04 kiff-03 oracle(oracle-cdb)[28584]: INFO: oracle process not running
Aug 30 09:40:04 kiff-03 crmd[2046]:  notice: Result of probe operation for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: 7 (not running) | call=2241 key=oracle-cdb_monitor_0 confirmed=true cib-update=4453
Aug 30 09:40:04 kiff-03 crmd[2046]:  notice: Initiating start operation oracle-cdb_start_0 locally on kiff-03.cluster-qe.lab.eng.brq.redhat.com | action 26
>>> Aug 30 09:40:15 kiff-03 oracle(oracle-cdb)[28633]: ERROR: monprofile must start with C## for container databases
>>> Aug 30 09:40:15 kiff-03 lrmd[2043]:  notice: oracle-cdb_start_0:28633:stderr [ ocf-exit-reason:monprofile must start with C## for container databases ]
Aug 30 09:40:15 kiff-03 crmd[2046]:  notice: Result of start operation for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: 1 (unknown error) | call=2242 key=oracle-cdb_start_0 confirmed=true cib-update=4454
Aug 30 09:40:15 kiff-03 crmd[2046]:  notice: kiff-03.cluster-qe.lab.eng.brq.redhat.com-oracle-cdb_start_0:2242 [ ocf-exit-reason:monprofile must start with C## for container databases\n ]
Aug 30 09:40:15 kiff-03 crmd[2046]: warning: Action 26 (oracle-cdb_start_0) on kiff-03.cluster-qe.lab.eng.brq.redhat.com failed (target: 0 vs. rc: 1): Error
Aug 30 09:40:15 kiff-03 crmd[2046]:  notice: Transition aborted by operation oracle-cdb_start_0 'modify' on kiff-03.cluster-qe.lab.eng.brq.redhat.com: Event failed | magic=0:1;26:2217:0:a939e526-581d-4744-9f8e-93e3e0ff6fd4 cib=0.114.4 source=match_graph_event:310 complete=false
Aug 30 09:40:15 kiff-03 crmd[2046]: warning: Action 26 (oracle-cdb_start_0) on kiff-03.cluster-qe.lab.eng.brq.redhat.com failed (target: 0 vs. rc: 1): Error
Aug 30 09:40:15 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 09:40:15 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 09:40:15 kiff-03 pengine[2045]:  notice: Recover oracle-cdb#011(Started kiff-03.cluster-qe.lab.eng.brq.redhat.com)
Aug 30 09:40:15 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 09:40:15 kiff-03 pengine[2045]: warning: Processing failed op start for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: unknown error (1)
Aug 30 09:40:15 kiff-03 pengine[2045]: warning: Forcing oracle-cdb away from kiff-03.cluster-qe.lab.eng.brq.redhat.com after 1000000 failures (max=1000000)
Aug 30 09:40:15 kiff-03 pengine[2045]:  notice: Stop    oracle-cdb#011(kiff-03.cluster-qe.lab.eng.brq.redhat.com)
Aug 30 09:40:15 kiff-03 crmd[2046]:  notice: Initiating stop operation oracle-cdb_stop_0 locally on kiff-03.cluster-qe.lab.eng.brq.redhat.com | action 6
Aug 30 09:40:22 kiff-03 oracle(oracle-cdb)[29501]: INFO: Oracle instance cdb stopped: ORACLE instance shut down.
Aug 30 09:40:23 kiff-03 oracle(oracle-cdb)[29501]: INFO: Cleaning up for cdb
Aug 30 09:40:23 kiff-03 crmd[2046]:  notice: Result of stop operation for oracle-cdb on kiff-03.cluster-qe.lab.eng.brq.redhat.com: 0 (ok) | call=2243 key=oracle-cdb_stop_0 confirmed=true cib-update=4458


>> correct the user the way oracle likes it
[root@kiff-03 ~]# pcs resource update oracle-cdb monprofile=C##mprofile
[root@kiff-03 ~]# pcs resource | grep oracle-cdb
oracle-cdb     (ocf::heartbeat:oracle):        Started kiff-03.cluster-qe.lab.eng.brq.redhat.com
[root@kiff-03 ~]# ps -u oracle | grep cdb | wc -l
167

[root@kiff-03 ~]# su - oracle
Last login: Út srp 30 09:48:24 CEST 2016
[oracle@kiff-03 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@kiff-03 ~]$ export ORACLE_SID=cdb
[oracle@kiff-03 ~]$ $ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 30 09:48:54 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> 


=====================================================
Comment 9 errata-xmlrpc 2016-11-04 00:02:56 UTC 
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://rhn.redhat.com/errata/RHBA-2016-2174.html