Nabil Nawaz's Blog: Cloud Control

While doing an upgrade of Oracle Enterprise Manager 12c from 12.1.0.1 to 12.1.0.4 I encountered an error which caused the upgrade to fail. Please keep in mind you cannot do a direct path upgrade from EM 12.1.0.1 to 12.1.0.4 you will first need to apply Bundle Patch (BP1) to the 12.1.0.1 binaries and then you can upgrade from 12.1.0.1 BP1 --> 12.1.0.2 or 12.1.0.3 and then finally you can upgrade to 12.1.0.4.

From the upgrade logs the error stack is below.

INFO: oracle.sysman.top.oms:MDS Schema Creation status: FAILURE
SEVERE: oracle.sysman.top.oms:MDS Schema Creation is failed
INFO: oracle.sysman.top.oms:_______________________________________________________________________
INFO: oracle.sysman.top.oms:Starting opss Schema Creation.
INFO: oracle.sysman.top.oms:Failed to create OPSS schema
INFO: oracle.sysman.top.oms:_______________________________________________________________________
INFO: oracle.sysman.top.oms:Starting APM Schema Creation.
INFO: oracle.sysman.top.oms:APM Component is already created
INFO: oracle.sysman.top.oms:The plug-in MDS Schema Configuration has failed its perform method

Cannot change password for sysman_mds java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option

ERROR:MDS java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option

java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option

The Upgrade basically failed due to special character(s) in the SYSMAN password. Please make sure you do not have any special characters in the SYSMAN password such as *,#,!, etc Just use a good combination of alpha and number characters.

Since the upgrade failed I had to fallback and do the following to restart the upgrade all over again for this issue.

1.) Restore the database, inventory and OMS Middleware Home from backup.
2.) Startup database and listener
3.) Start the OMS
4.) Change the SYSMAN password that doesn't use special character(s), procedure below.

<OMS_HOME>/bin/emctl config oms -change_repos_pwd

Please note changing the SYSMAN password will change it on both the OMS and on the repository database.

Steps to rename an OEM repository Database.

This is something you may not have to do very often. I will show you how to rename your Oracle Enterprise Manager 12c repository database.

Take a full RMAN backup with archivelogs.

Shutdown OEM

$ . oraenv

ORACLE_SID = [oem] ? oem

The Oracle base for ORACLE_HOME=/opt/apps/MW/oms is /home/oracle

[oracle@dnvappoem06 ~]$ emctl stop oms

Oracle Enterprise Manager Cloud Control 12c Release 3

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Change the cluster database parameter to false

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

Backup the spfile

SQL> create pfile='/tmp/initent_oem.ora' from spfile;

File created.

Update the init file /tmp/initent_oem.ora with new parameters for the new sid and db name

Stop the database

/home/oracle>srvctl stop database -d oemsblp

In ASM make the new directories

mkdir +DATA/ent_oem/tempfile

mkdir +DATA/ent_oem/datafile

Startup mount only on one of the instances

Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify both the DBNAME and SETNAME parameters. The SETNAME parameter tells the DBNEWID utility to only alter the database name.

% /home/oracle>nid TARGET=/ DBNAME=ent_oem SETNAME=YES

DBNEWID: Release 11.2.0.3.0 - Production on Thu Feb 13 20:44:01 2014

Connected to database OEMSBLP (DBID=1189579321)

Connected to server version 11.2.0

Control Files in database:

+RECO/oemsblp/controlfile/current.261.832085049

Change database name of database OEMSBLP to ENT_OEM? (Y/[N]) => Y

Proceeding with operation

Changing database name from OEMSBLP to ENT_OEM

Control File +RECO/oemsblp/controlfile/current.261.832085049 - modified

Datafile +DATA/oemsblp/datafile/system.293.83208499 - wrote new name

Datafile +DATA/oemsblp/datafile/sysaux.275.83208499 - wrote new name

Datafile +DATA/oemsblp/datafile/undotbs1.276.83208499 - wrote new name

Datafile +DATA/oemsblp/datafile/users.290.83208499 - wrote new name

Datafile +DATA/oemsblp/datafile/undotbs2.292.83208513 - wrote new name

Datafile +DATA/oemsblp/datafile/mgmt_ecm_depot_ts.287.83232853 - wrote new name

Datafile +DATA/oemsblp/datafile/mgmt_tablespace.270.83232853 - wrote new name

Datafile +DATA/oemsblp/datafile/mgmt_ad4j_ts.264.83232853 - wrote new name

Datafile +DATA/oemsblp/tempfile/temp.291.83208507 - wrote new name

Control File +RECO/oemsblp/controlfile/current.261.832085049 - wrote new name

Instance shut down

Database name changed to ENT_OEM.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID - Completed succesfully.

/home/oracle>

Create a new init file pointer in the $ORACLE_HOME/dbs on each node

NODE1:

/opt/oracle/product/11203/db_1/dbs>cat initent_oem1.ora

SPFILE='+DATA/ent_oem/spfileent_oem.ora'

NODE2:

/opt/oracle/product/11203/db_1/dbs>cat initent_oem2.ora

SPFILE='+DATA/ent_oem/spfileent_oem.ora'

Create a new password file on each node from the $ORACLE_HOME/dbs directory.

NODE1:

cp orapwoemsblp1 orapwent_oem1

NODE2:

cp orapwoemsblp1 orapwent_oem2

create spfile='+DATA/ent_oem/spfileent_oem.ora' from pfile='/tmp/initent_oem.ora' ;

Set the new ORACLE_SID

Verify the parameters are updated correctly to the new name, note the instance_name and db_name and other parameters.

SQL> show parameter name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert string

db_name string ent_oem

db_unique_name string ent_oem

global_names boolean FALSE

instance_name string ent_oem1

lock_name_space string

log_file_name_convert string

processor_group_name string

service_names string ent_oem

SQL> show parameter pfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string +DATA/ent_oem/spfileent_oem.ora

Change the cluster_database parameter back to true

SQL> alter system set cluster_database=true scope=spfile sid='*';

DBNEWID utility makes the changes in the controlfile and datafile headers. This utility is not RAC aware. So it will not update the OCR when the database is renamed. Hence, the user needs to invoke SRVCTL to remove and again add the database information to OCR:

Before the Rename

srvctl stop database -d

srvctl remove instance -d oemsblp -i oemsblp1

srvctl remove instance -d oemsblp -i oemsblp2

(repeat above command for each instance as needed)

srvctl remove database -d oemsblp

And after the Rename

srvctl add database -d ent_oem -o /opt/oracle/product/11203/db_1

/opt/oracle/product/11203/db_1/dbs>srvctl add instance -d ent_oem -i ent_oem1 -n dnvdboem01

/opt/oracle/product/11203/db_1/dbs>srvctl add instance -d ent_oem -i ent_oem2 -n dnvdboem02

(repeat above command for each instance as needed)

Start the database

srvctl start database –d ent_oem

Save a backup copy of the controlfile of the database.

alter database backup controlfile to trace as '/tmp/ent_oem.trc';

Change the cluster_mode back to false

alter system set cluster_database=false scope=spfile sid='*';

Shutdown the database and restart in mount mode.

Copy and rename the datafiles to the new directory location via RMAN while connected to the $ORACLE_SID

copy datafile '+DATA/oemsblp/datafile/system.293.832084993' to '+data';

copy datafile '+DATA/oemsblp/datafile/sysaux.275.832084993' to '+data';

copy datafile '+DATA/oemsblp/datafile/undotbs1.276.832084993' to '+data';

copy datafile '+DATA/oemsblp/datafile/users.290.832084993' to '+data';

copy datafile '+DATA/oemsblp/datafile/undotbs2.292.832085133' to '+data';

copy datafile '+DATA/oemsblp/datafile/mgmt_ecm_depot_ts.287.832328533' to '+data';

copy datafile '+DATA/oemsblp/datafile/mgmt_tablespace.270.832328533' to '+data';

copy datafile '+DATA/oemsblp/datafile/mgmt_ad4j_ts.264.832328533' to '+data';

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 1000M;

Run the following controlfile script with the new directory locations

STARTUP NOMOUNT

CREATE CONTROLFILE reuse DATABASE "ENT_OEM" NORESETLOGS ARCHIVELOG

MAXLOGFILES 192

MAXLOGMEMBERS 3

MAXDATAFILES 1024

MAXINSTANCES 32

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '+RECO/ent_oem/onlinelog/group_1.262.832085053' SIZE 500M BLOCKSIZE 512,

GROUP 2 '+RECO/ent_oem/onlinelog/group_2.263.832085057' SIZE 500M BLOCKSIZE 512,

GROUP 3 '+RECO/ent_oem/onlinelog/group_3.265.832085239' SIZE 500M BLOCKSIZE 512,

GROUP 4 '+RECO/ent_oem/onlinelog/group_4.266.832085247' SIZE 500M BLOCKSIZE 512,

GROUP 5 '+RECO/ent_oem/onlinelog/group_5.264.832085061' SIZE 500M BLOCKSIZE 512,

GROUP 6 '+RECO/ent_oem/onlinelog/group_6.267.832085253' SIZE 500M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'+DATA/ent_oem/datafile/system.293.832084993',

'+DATA/ent_oem/datafile/sysaux.275.832084993',

'+DATA/ent_oem/datafile/undotbs1.276.832084993',

'+DATA/ent_oem/datafile/users.290.832084993',

'+DATA/ent_oem/datafile/undotbs2.292.832085133',

'+DATA/ent_oem/datafile/mgmt_ecm_depot_ts.287.832328533',

'+DATA/ent_oem/datafile/mgmt_tablespace.270.832328533',

'+DATA/ent_oem/datafile/mgmt_ad4j_ts.264.832328533'

CHARACTER SET AL32UTF8

;

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' size 1000M;

On the OMS server run the following command to change the OMS repository database name

[oracle@dnvappoem06 ~]$ emctl config oms -store_repos_details -repos_conndesc '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

(HOST=doem1dbrac-scan)(PORT=1521)))(LOAD_BALANCE=ON)

> (CONNECT_DATA=(SERVICE_NAME=ent_oem)))' -repos_user sysman

Oracle Enterprise Manager Cloud Control 12c Release 3

Enter Repository User's Password :

Successfully updated datasources and stored repository details in Credential Store.

If there are multiple OMSs in this environment, run this store_repos_details command on all of them.

And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.

[oracle@dnvappoem06 log]$ emctl config oms -list_repos_details

Oracle Enterprise Manager Cloud Control 12c Release 3

Repository Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=doem1dbrac-scan)(PORT=1521)))(LOAD_BALANCE=ON)

(CONNECT_DATA=(SERVICE_NAME=ent_oem)))

Repository User : sysman

[oracle@dnvappoem06 ~]$

[oracle@dnvappoem06 ~]$ emctl stop oms -all

Oracle Enterprise Manager Cloud Control 12c Release 3

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

AdminServer Successfully Stopped

Oracle Management Server is Down

[oracle@dnvappoem06 ~]$ emctl start oms

Oracle Enterprise Manager Cloud Control 12c Release 3

Starting Oracle Management Server...

Starting WebTier...

WebTier Successfully Started

Oracle Management Server Successfully Started

Oracle Management Server is Up

[oracle@dnvappoem06 ~]$ emctl status oms

Oracle Enterprise Manager Cloud Control 12c Release 3

WebTier is Up

Oracle Management Server is Up

Nabil Nawaz's Blog

Saturday, July 4, 2015

DBaaS: Deploying Databasess quickly from OEM @ Dallas Oracle Users Group

Monday, October 13, 2014

Enterprise Manager 12c No Special Characters for SYSMAN account

Wednesday, April 16, 2014

Steps to Rename an Oracle Enterprise Manager Repository Database

Blog Archive