Oracle 12c relink resets oracle group to oinstall, while 19c sets asmadmin

Problem:

In a mixed environment when you have 19c GI, but several versions of Oracle RDBMS home (19c, 12c), initially during 12c database restore you may encounter the following error:

ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete

There are several reasons for the above error and one of them is when the $ORACLE_HOME/bin/oracle binary under rdbms home does not have group asmadmin. After identifying it, you try to change permissions to oracle:asmadmin and but after a while, you may notice that the group is reset to oinstall. It can happen after patching or after running relink manually. So let’s see test case:

[oracle@rac1 lib]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle asmadmin 323613256 Feb 19 17:00 /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

Run relink:

[oracle@rac1 lib]$ make -f ins_rdbms.mk ioracle

Check permissions again:

[oracle@rac1 lib]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 323613256 Feb 19 17:03 /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

If you do the same for 19c home, group is still asmadmin.

Reason:

ins_rdbms.mk script is different in 19c and 12c, comparing ioracle section:

12c:

ioracle: preinstall $(ORACLE)
        -$(NOT_EXIST) $(ORACLE_HOME)/bin/oracle ||\
           mv -f $(ORACLE_HOME)/bin/oracle $(ORACLE_HOME)/bin/oracleO
        -mv $(ORACLE_HOME)/rdbms/lib/oracle $(ORACLE_HOME)/bin/oracle
        -chmod 6751 $(ORACLE_HOME)/bin/oracle

19c:

ioracle: preinstall $(ORACLE)
        -$(RMF) $(ORACLE_HOME)/bin/oracle
        -mv $(ORACLE_HOME)/rdbms/lib/oracle $(ORACLE_HOME)/bin/oracle
        -chmod 6751 $(ORACLE_HOME)/bin/oracle

        -(if [ ! -f $(ORACLE_HOME)/bin/crsd.bin ]; then \
            getcrshome="$(ORACLE_HOME)/srvm/admin/getcrshome" ; \
            if [ -f "$$getcrshome" ]; then \
                crshome="`$$getcrshome`"; \
                if [ -n "$$crshome" ]; then \
                    if [ $$crshome != $(ORACLE_HOME) ]; then \
                        oracle="$(ORACLE_HOME)/bin/oracle"; \
                        $$crshome/bin/setasmgidwrap oracle_binary_path=$$oracle; \
                    fi \
                fi \
            fi \
        fi\
        );

As you see ioracle section in 12c does not contain the last IF statement which sets oracle binary group to asmadmin (using setasmgidwrap)

Solution:

Unfortunately, I cannot recommend adding that if statement in 12c ins_rdbms.mk although I did it in my test environment and works as expected.
After running make -f ins_rdbms.mk ioracle for 12c the group is set to asmadmin.

It is better to ask Oracle about this change and do only after you get an approval from them.

The only non-harmful recommendation from my side would be to have permission change script and run after every patch or relink:

# chown oracle:asmadmin  /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
​# chmod 6751  /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle
# ll   /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

If you still decide to add IF statement as I did, please make sure there is TAB instead of spaces before IF clause. Otherwise, you will get the following error and you can easily understand which line should be corrected.

[oracle@rac1 lib]$ make -f ins_rdbms.mk ioracle
ins_rdbms.mk:120: *** missing separator (did you mean TAB instead of 8 spaces?).  Stop.

Upgrade the database time zone file using the DBMS_DST package in 19c

After upgrading database from 12c to 19c, you may need to upgrade database time zone file version. This step is not always mandatory, but it is recommended by pre-upgrade checker.

Useful info about its necessity from Oracle site: https://oracle-base.com/articles/misc/update-database-time-zone-file

From Oracle 11gR2 onward, new time zone files are shipped with upgrades and patches, but they are not automatically applied to the database.

Applying a change to the database time zone file not only affects the way new data is handled, but potentially alters data stored in TIMESTAMP WITH TIME ZONE columns, so you need to consider the impact of this before upgrading the time zone file.

Remember, if you only deal with dates in your country, and your country has not altered its time zone or daylight saving time policy, this upgrade may not be necessary.”

Now let’s do time zone file upgrade:

1. Check current settings:

SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat 	     26 	 0

2. Startup database in upgrade mode:

SQL> shutdown immediate;
SQL> startup upgrade;

3. Start upgrade window:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  l_tz_version PLS_INTEGER;
BEGIN
  l_tz_version := DBMS_DST.get_latest_timezone_version;

  DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
  DBMS_DST.begin_upgrade(l_tz_version);
END;
/


l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

4. Check primary and secondary time zone versions:

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 

PROPERTY_NAME		 VALUE
------------------------ ----------
DST_PRIMARY_TT_VERSION	 32
DST_SECONDARY_TT_VERSION 26
DST_UPGRADE_STATE	 UPGRADE

5. Startup database in normal mode:

SQL> shut immediate;
SQL> startup;

6. Do the upgrade:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  l_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  DBMS_DST.end_upgrade(l_failures);
  DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/


Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

7. Check new settings:

SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat 	     32 	 0


SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A20

SQL> SELECT property_name, property_value
FROM   database_properties
WHERE  property_name LIKE 'DST_%'
ORDER BY property_name;

PROPERTY_NAME		       PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION	       32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE	       NONE

PRCD-1229 : An attempt to access configuration of database orcl was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0

Problem:

After a manual database upgrade from 12c to 19c, I was not able to start database instance using srvctl.

[oracle@rac1 ~]$ srvctl start instance -db orcl -n rac1

PRCD-1027 : Failed to retrieve database orcl
PRCD-1229 : An attempt to access configuration of database orcl was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.2.0/dbhome_1.

Solution:

Use srvctl upgrade from new home:

$ /u01/app/oracle/product/19.3.0/dbhome_1/bin/srvctl upgrade database -d orcl -oraclehome /u01/app/oracle/product/19.3.0/dbhome_1

Try to start again:

$ srvctl start instance -db orcl -n rac1

How to delete MGMTDB?

Introduction

MGMTDB is a repository database that saves Cluster Health Monitor (CHM) data. In Oracle 11g this information was stored in Berkley database ( .bdb files) but starting from Oracle database 12c it is configured as an Oracle single instance database.

In Oracle 12.1.0.1 – GIMR is optional. Whereas in Oracle 12.1.0.2 – it’s mandatory and it’s not supported to be turned off with the exception of Exadata.
I’ve searched a lot to find out why it is not supported to be turned off, but I still do not have that answer. I only know that TFA collects some information from MGMTDB and if we turn it off, it means TFA will not be able to retrieve that information. In 19c GIMR is optional again.

The reason why I want to turn it off is that there are several bugs related to MGMTDB. We have noticed that several customers had performance-related issues because of MGMTDB. The repository database was able to use almost 100% of CPU resources. In addition to this, one customer noticed that MGMTDB increased up to 60GB and exhausted GRID diskgroup where OCR and voting files were located (this size is not normal for 3-node cluster).

More information about Grid Infrastructure Management Repository (GIMR) can be found at 1568402.1

Steps

Please consider that for 12.1.0.2 deleting it is not supported by Oracle, which is not clear why, but it is better to ask Oracle support before doing this.

Instead of deleting a repository, it is better to apply all bug fixes that are related to it. And try to use its intelligence to proactively tune your database. But if you still want to delete it, or at least know how to delete it – then let’s do that, it is not harmful.

1. Update the dependency

CHA has a dependency on mgmtdb:

[root@rac1 ~]# crsctl stat res ora.chad -p | grep mgmt
START_DEPENDENCIES=hard(global:ora.mgmtdb) pullup(global:ora.mgmtdb)
STOP_DEPENDENCIES=hard(global:intermediate:ora.mgmtdb)

If you try to delete mgmtdb without updating the dependency you will get:

image

Clear the dependency by setting the following attributes:

[root@rac1 ~]# crsctl modify resource ora.chad -attr "START_DEPENDENCIES='', STOP_DEPENDENCIES=''" -unsupported

Verify the status:

[grid@rac1 ~]$ srvctl status cha
Oracle Cluster Health Analysis Service is enabled
Oracle Cluster Health Analysis Service is running on nodes: rac1, rac2

2. Delete MGMTDB using dbca

 Identify the node where mgmtdb is running:

[grid@rac1 ~]$ srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node rac1

Connect rac1 and run:

[grid@rac1 ~]$ dbca -silent -deleteDatabase -sourceDB -MGMTDB
[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
35% complete
39% complete
42% complete
45% complete
48% complete
52% complete
65% complete
Updating network configuration files
68% complete
Deleting instance and datafiles
84% complete
100% complete
Database deletion completed.

Make sure that MGMTDB was deleted

[grid@rac1 ~]$ srvctl status mgmtdb
PRCD-1120 : The resource for database _mgmtdb could not be found.
PRCR-1001 : Resource ora.mgmtdb does not exist

3. Delete the listener called MGMTLSNR 

[grid@rac1 ~]$ srvctl stop mgmtlsnr

[grid@rac1 ~]$ srvctl remove mgmtlsnr

What is a Flex ASM and how to check if it is enabled?

In versions prior to 12c, the ASM instance needed to be run on each of the nodes of the cluster. In case ASM was not able to start, the database instance located on the same node was not able to come up also. There were a hard dependency between database and ASM instances.

With Oracle Flex ASM, databases are able to connect remote ASM using network connection(ASM network). In case of ASM instance fails, the database instance will reconnect to another ASM instance on another node. This feature is called Oracle Flex ASM.

Check if you are using such a great feature using the following command:

[grid@rac1 ~]$ asmcmd
ASMCMD> showclustermode
ASM cluster : Flex mode enabled

 

EM 12c installation: libwebcache.a(wxsmdms.o): undefined reference to symbol ‘yodSensorInvalidate’

Problem:

Installation of Enterprise Manager Cloud Control 12c fails at 44% and showing the following message on popup window:

POPUP WARNING:Error in invoking target ‘install’ of makefile ‘/u01/app/oracle/middleware/Oracle_WT/webcache/lib/ins_calypso.mk’. See ‘/u01/app/oraInventory/logs/cloneActions2018-12-25_09-02-12-PM.log

Troubleshooting:

The mentioned logfile cloneActions2018-12-25_09-02-12-PM.log contains the following additional information:

INFO: 12/25/18 9:03:32 PM UTC: T/lib -lm `cat /u01/app/oracle/middleware/Oracle_WT/lib/sysliblist` -lrt -ldl -lm -L/u01/app/oracle/middleware/Oracle_WT/lib

INFO: 12/25/18 9:03:32 PM UTC: /usr/bin/ld: libwebcache.a(wxsmdms.o): undefined reference to symbol ‘yodSensorInvalidate’
/u01/app/oracle/middleware/Oracle_WT/lib/libdms2.so: error adding symbols: DSO missing from command line

Solution:

1. Save original file

$ cp -p /u01/app/oracle/Middleware/oms/lib/sysliblist /u01/app/oracle/Middleware/oms/lib/sysliblist.orig

2. Modify sysliblist file by adding -ldms2 at the end:

$ cat /u01/app/oracle/Middleware/oms/lib/sysliblist 
-ldl -lm -lpthread -lnsl -lirc -lipgo -ldms2

After fixing the above error click retry.

Note: 

Do not try to change this line before EM fails, otherwise, you will receive a different error. On some stage(before 44%) EM needs to have syslibslist without -ldms2. So you should change this line after installation fails and retry the installation.

 

Convert Oracle SE to EE

Upgrading Oracle database from Standard Edition to Enterprise Edition is very simple.

For example, we have running Oracle SE in ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

Let’s start converting it…

  1. Install new home in /u01/app/oracle/product/12.1.0/dbhome_2 just indicate EE during installation(not SE).
    Response file entries(db_install.rsp):

    ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
    oracle.install.db.InstallEdition=EE
  2. Shutdown database and listener from old home.
    . oraenv
    ORACLE_SID = [ORCL] ?
    
    lsnrctl stop
    sqlplus / as sysdba
    shutdown immediate;
  3. Change ORACLE_HOME in oratab and .bash_profile
    cat /etc/oratab
    ORCL:/u01/app/oracle/product/12.1.0/dbhome_2:Y
    cat ~/.bash_profile
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    export PATH=$PATH:$ORACLE_HOME/bin
    export ORACLE_SID=EYC
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export EDITOR=vi
  4. Copy listener.ora, tnsnames.ora, sqlnet.ora, spfileORCL.oraorapwORCL to new home
    cp /u01/app/oracle/product/12.1.0/dbhome_1/network/*.ora /u01/app/oracle/product/12.1.0/dbhome_2/network/
    cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora /u01/app/oracle/product/12.1.0/dbhome_2/dbs/
    cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL /u01/app/oracle/product/12.1.0/dbhome_2/dbs/

    Please verify that you don’t have old ORACLE_HOME indicated anywhere in these files.

  5. Renew environment variables
    . oraenv
    ORACLE_SID = [ORCL] ? 
    
    which sqlplus
    /u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus
    
    sqlplus / as sysdba
    startup;
  6. Check that you have EE
    SQL> select banner from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    PL/SQL Release 12.1.0.1.0 - Production
    CORE    12.1.0.1.0      Production
    TNS for Linux: Version 12.1.0.1.0 - Production
    NLSRTL Version 12.1.0.1.0 - Production