ORA-59303: The attribute compatible.asm (11.2.0.0.0) of the diskgroup being mounted should be 11.2.0.2.0 or higher.

Well if you get such error message, it means that the diskgroup was created with a too old version.

+ASM:localhost:/home/oracle $ asmcmd
ASMCMD> mount FLASH
ORA-15032: NOT ALL alterations performed
ORA-59303: The attribute compatible.asm (11.2.0.0.0) OF the diskgroup being mounted should be 11.2.0.2.0 OR higher. (DBD ERROR: OCIStmtExecute)
ASMCMD> exit
+ASM:localhost:/home/oracle $ sqlplus / AS sysasm
SQL*Plus: Release 12.2.0.1.0 Production ON Mon Nov 27 16:43:04 2017
Copyright (c) 1982, 2016, Oracle.  ALL rights reserved.
Connected TO:
Oracle DATABASE 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> ALTER diskgroup FLASH mount restricted;
Diskgroup altered.
SQL> ALTER diskgroup FLASH SET ATTRIBUTE 'compatible.asm'='11.2.0.4';
Diskgroup altered.
SQL> ALTER diskgroup FLASH dismount;
Diskgroup altered.
SQL> ALTER diskgroup DATA mount restricted;
Diskgroup altered.
SQL> ALTER diskgroup DATA SET ATTRIBUTE 'compatible.asm'='11.2.0.4';
Diskgroup altered.
SQL> ALTER diskgroup DATA dismount;
Diskgroup altered.
SQL> Disconnected FROM Oracle DATABASE 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
+ASM:localhost:/home/oracle $ asmcmd
ASMCMD> mount DATA
ASMCMD> mount FLASH
ASMCMD> exit

Upgrade Oracle from 10.2.0.4 to 11.2.0.4

One of the task dbas have to do is to quickly upgrade Oracle database and binaries, this post will list you the short path to upgrade an Oracle database from 10g to 11.2.

  • Do a normal install of Oracle 11.2.0.4 with latest PSU applied. ( don’t touch the 10g version as you need it to upgrade the databases )
  • On the 10g database run the script utlu112i.sql this script is shipped with the 11.2.0.4 you just have installed. Fix all problems the script showed up!
  • On the 10g, connected as sysdba do the following cleaning tasks:
  • purge dba_recyclebin;
    exec dbms_stats.gather_dictionary_stats;
    shutdown immediate
  • On the 11.2 binaries, start the DB and upgrade it:
    cd $ORACLE_HOME/rdbms/admin
    $ sqlplus / AS sysdba
    SQL> startup UPGRADE
    SQL> SET echo ON
    SQL> SPOOL upgrade.log
    SQL> @catupgrd.sql
    $ sqlplus / AS sysdba
    SQL> STARTUP
    SQL> @utlu112s.sql
    SQL> @utlrp.sql
    SQL> @catuppst.sql
    SQL> @utlrp.sql
    SQL> shutdown
    SQL> startup
    SQL> ALTER system SET compatible='11.2.0.4' scope=spfile;
    SQL> shutdown immediate
    SQL> startup
    SQL> exit
  • Your DB is now upgraded to 11.2.0.4, congrats!
  • If utlu112i.sql let you know that you should upgrade the timezone definition, then follow the next few steps:
cd $ORACLE_HOME/rdbms/admin $
sqlplus / AS sysdba
shutdown immediate;
 
startup upgrade;
SET serveroutput ON
-- check if previous prepare window is ended
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
-- output should be
-- PROPERTY_NAME VALUE
-- —————————- ——————————
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
ALTER SESSION SET "_with_subquery"=materialize;
ALTER SESSION SET "_simple_view_merging"=TRUE;
EXEC DBMS_DST.BEGIN_UPGRADE(14);
-- check if this select
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
-- gives this output:
-- PROPERTY_NAME VALUE
-- ————————— ——————————
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE
shutdown immediate
startup
ALTER SESSION SET "_with_subquery"=materialize;
ALTER SESSION SET "_simple_view_merging"=TRUE;
SET serveroutput ON
VAR numfail NUMBER
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
-- ouput of this will be a list of tables like:
-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- if there where no failures then end the upgrade.
 
VAR fail NUMBER
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
-- output that will be seen:
-- An upgrade window has been successfully ended.
-- Failures:0
 
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- needed output:
-- PROPERTY_NAME VALUE
-- —————————- ——————————
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
SELECT * FROM v$timezone_file;
-- needed output:
-- FILENAME                VERSION
-- ——————– ———-
-- timezlrg_14.dat              14
 
SELECT TZ_VERSION FROM registry$database;
UPDATE registry$database SET TZ_VERSION = (SELECT version FROM v$timezone_file);
commit;

Continue reading “Upgrade Oracle from 10.2.0.4 to 11.2.0.4”