Online Partition Move fails with ORA-00932 – 12.1.0.2

Oracle 12c introduced feature to move table partitions and sub-partitions as online operations.So, this blog is related to this feature and the issue I faced.

The database was upgraded from 11.2.0.4 to 12.1.0.2 and this issue is seen in upgraded databases only.

Lets start!!!

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2015','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2015','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2015','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2015','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2015','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2015','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2015','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2015','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2015','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2015','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2015','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2016','dd-mm-yyyy'))
   ) 
   TABLESPACE USERS;

SQL> insert into sales_part
    select
    'Oracle Enterprise Edition' as product,
     mod(rownum,5) as channel_id,
     mod(rownum,1000) as cust_id ,
     5000 as amount_sold,
     to_date
     ('01.' || lpad(to_char(mod(rownum,6)+4),2,'0') || '.2015' ,'dd.mm.yyyy')
     as time_id
     from dual connect by level commit;

Commit complete.

SQL> create index idx_sale_local on sales_part (cust_id,time_id)local tablespace users;

Index created.

SQL> create index idx_sale_global on sales_part (channel_id)  tablespace users;

Index created.

SQL>                        
SQL> exec dbms_stats.gather_table_stats('ANAND','SALES_PART', cascade => true);

PL/SQL procedure successfully completed.

SQL>

Table and Index details

SQL>@partition_table 

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                                                                            LAST_ANALYZED      TABLESPACE_NAM   NUM_ROWS
--------------- ------------------------- ------------------------------------------------------------------------------------- ------------------ -------------- ----------
SALES_PART      SALE_JAN                  TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_FEB                  TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_MAR                  TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_APR                  TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_MAY                  TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33334
SALES_PART      SALE_JUN                  TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33334
SALES_PART      SALE_JUL                  TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_AUG                  TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_SEP                  TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_OCT                  TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_NOV                  TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_DEC                  TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0

12 rows selected.

SQL> @index
Enter value for owner: ANAND
Enter value for table_name: SALES_PART

TABLE_OWNER     TABLE_NAME            INDEX_OWNER     INDEX_NAME            TABLESPACE_NAME   NUM_ROWS      CLUST STATUS                   INDEX_TYPE
--------------- --------------------- --------------- --------------------- --------------- ---------- ---------- ------------------------ ------------
ANAND           SALES_PART            ANAND           IDX_SALE_GLOBAL       USERS               200000       6084 VALID                    NORMAL
ANAND           SALES_PART            ANAND           IDX_SALE_LOCAL                            200000     199784 N/A                      NORMAL

SQL> @partition_index
Enter value for index_name: IDX_SALE_LOCAL
Enter value for owner: ANAND

Index                                                                                                                   Number
Name                  PARTITION_NA STATUS                   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE    of Rows
--------------------- ------------ ------------------------ --------------- -------------- ----------- ------------ ----------
IDX_SALE_LOCAL        SALE_JAN     USABLE                   USERS                                                            0
                      SALE_FEB     USABLE                   USERS                                                            0
                      SALE_MAR     USABLE                   USERS                                                            0
                      SALE_APR     USABLE                   USERS                    65536     1048576                   33333
                      SALE_MAY     USABLE                   USERS                    65536     1048576                   33334
                      SALE_JUN     USABLE                   USERS                    65536     1048576                   33334
                      SALE_JUL     USABLE                   USERS                    65536     1048576                   33333
                      SALE_AUG     USABLE                   USERS                    65536     1048576                   33333
                      SALE_SEP     USABLE                   USERS                    65536     1048576                   33333
                      SALE_OCT     USABLE                   USERS                                                            0
                      SALE_NOV     USABLE                   USERS                                                            0
                      SALE_DEC     USABLE                   USERS                                                            0

Now I plan to move Partition SALE_JUN to tablespace DEMO.

SQL> alter table anand.sales_part move partition sale_jun online tablespace demo
  2  update indexes (
  3  anand.idx_sale_local (partition sale_jun tablespace demo)
  4  );
alter table anand.sales_part move partition sale_jun online tablespace demo
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

After searching for the issue in MOS and reviewing DOC Id 2028583.1, I applied patch 15894842 and 20123899

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.

List of Bugs fixed by Installed Patches:

Bug        Fixed by  Installed at                   Description
            Patch
---        --------  ------------                   -----------
20123899   20123899  Fri Oct 02 10:32:54 UTC 2015   INSERT IN TO STAT TABLE FAILS WITH ORA-932
                                                    INCONSISTENT DATATYPES
15894842   15894842  Fri Oct 02 10:31:52 UTC 2015   INSERT IN TO STAT TABLE FAILS WITH ORA-00932
                                                    INCONSISTENT DATATYPES

Even after applying the above patches, the online partition move failed with the same error. This time I traced the session –

SQL> alter session set events '932 trace name errorstack level 3';

Session altered.

SQL>alter table anand.sales_part move partition sale_jun online tablespace demo
    update indexes (
     anand.idx_sale_local (partition sale_jun tablespace demo)
     );
 alter table anand.sales_part move partition sale_jun online tablespace demo
                   *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

The trace file showed

*** 2015-10-02 10:56:40.306
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
----- Current SQL Statement for this session (sql_id=8qwf072kqbt3g) -----
insert into histgrm$ select :1, col#, row#, bucket, endpoint, intcol#, epvalue, ep_repeat_count, epvalue_raw, spare1, spare2 from histgrm$ where obj# = :2

----- Call Stack Trace -----

So the issue is related to HISTGRM$ table. After spending few minutes searching for issue in MOS, I found the below useful documents:-

Bug 20703000 : INSERT INTO STAT TABLE HISTGRM$ FAILS WITH ORA-00932: INCONSISTENT DATATYPES
Moving Table Partition With CLOB Creates ORA-604 , ORA-932 (Doc ID 2040742.1)

After applying the patch 20703000, online partition move worked fine and below is the changed sql on histgrm$

PARSING IN CURSOR #140348537646088 len=257 dep=1 uid=0 oct=2 lid=0 tim=41782225111 hv=216722248 ad='8635af20' sqlid='66x6dxn6fpuu8'
insert into histgrm$ (obj#, col#, row#, bucket, endpoint, intcol#, epvalue,  ep_repeat_count, epvalue_raw, spare1, spare2 ) 
select :1, col#, row#, bucket, endpoint, intcol#, epvalue, ep_repeat_count, epvalue_raw, spare1, spare2 from histgrm$ where obj# = :2
END OF STMT

Hope this helps!!

PDB Saved state – 12.1.0.2

Prior to 12.1.0.2 version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from 12.1.0.2, this can be done with PDB save state feature

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			     READ ONLY  NO
	 3 ALPDB			     READ WRITE NO

Lets create a new PDB

SQL> 
SQL> create pluggable database hydb admin user anand identified by anand123 role = (dba)
  2  file_name_convert =('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/hydb/')
  3  storage (maxsize 500M);
create pluggable database hydb admin user anand identified by anand123 role = (dba)
*
ERROR at line 1:
ORA-65113: value of MAX_PDB_STORAGE property for the PDB is too low

SQL> create pluggable database hydb admin user anand identified by anand123 role = (dba)
    file_name_convert =('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/hydb/')
    storage (maxsize 1G);
  2    3

Pluggable database created.  

Check status

SQL> @cdb_pdbs

    PDB_ID	 DBID PDB_NAME	     STATUS
---------- ---------- -------------- ----------
	 3 2221989451 ALPDB	     NORMAL
	 2  385653993 PDB$SEED	     NORMAL
	 4 4008421982 HYDB	     NEW

SQL> show pdbs

    CON_ID CON_NAME		  OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ALPDB			  READ WRITE NO
	 4 HYDB 			  MOUNTED
SQL>  

SQL> alter pluggable database hydb open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME	      OPEN MODE  RESTRICTED
---------- ------------   ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ALPDB			  READ WRITE NO
	 4 HYDB 			  READ WRITE NO     
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;

no rows selected

DBA_PDB_SAVED_STATES can be used to check PDBs in saved state. We have none PDBs in saved state as per the above output.

SQL> alter pluggable database hydb save state;

Pluggable database altered.

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;

NAME				 STATE
------------------------------- --------------
HYDB				 OPEN

Now lets, restart the container

SQL> shu abort
ORACLE instance shut down.

SQL> SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  562039536 bytes
Database Buffers	  268435456 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
	 2 PDB$SEED			       READ ONLY  NO
	 3 ALPDB			       MOUNTED    
	 4 HYDB 			       READ WRITE NO
SQL> 

To discard the save state

SQL> alter pluggable database HYDB discard state;

Pluggable database altered.

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;

no rows selected

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  562039536 bytes
Database Buffers	  268435456 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			       READ ONLY  NO
	 3 ALPDB			       MOUNTED    
	 4 HYDB 			       MOUNTED
SQL> 

latch: cache buffers chains and rollback

I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event

load average: 258.52, 244.27, 226.15

select username,sql_id,event,count(*) from v$session where event like 'latch%' group by username,sql_id,event order by 4;
 
USERNAME                  SQL_ID        EVENT                            COUNT(*)
------------------------- ------------- ------------------------------ ----------
USER_EAS4                5j9xrj07xqysz latch free                              1
USER_EAS7                4f6dfhuxbz18y latch free                              1
USER_EAS7                a1wwm6tpukyh8 latch free                              1
USER_EAS7                c1sa78t1rvxkq latch free                              1
USER_EAS7                gc4xfk544n98v latch free                              2
USER_EAS7                bmhmdqvt9c2qz latch free                              2
USER_EAS7                4ycfn45y3vj5p latch free                              8
USER_EAS7                4ycfn45y3vj5p latch: cache buffers chains            23
USER_EAS7                9cg18w6mfqphn latch: cache buffers chains            51
USER_EAS7                9cg18w6mfqphn latch free                            144

sql monitor report of one session for sqlid 9cg18w6mfqphn

Global Stats
=======================================================
| Elapsed |   Cpu   | Concurrency |  Other   | Buffer |
| Time(s) | Time(s) |  Waits(s)   | Waits(s) |  Gets  |
=======================================================
|    2243 |    2132 |        0.00 |      111 |   260M |
=======================================================
 
SQL Plan Monitoring Details (Plan Hash Value=2956480150)
=============================================================================================================================================================
| Id   |               Operation               |         Name         |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
|      |                                       |                      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
=============================================================================================================================================================
|    0 | SELECT STATEMENT                      |                      |         |      |           |        |     1 |          |          |                 |
|    1 |   FILTER                              |                      |         |      |           |        |     1 |          |          |                 |
|    2 |    PARTITION RANGE SINGLE             |                      |      18 |  133 |           |        |     1 |          |          |                 |
|    3 |     TABLE ACCESS BY LOCAL INDEX ROWID | MESSAGE_DEMO_CTC     |      18 |  133 |           |        |     1 |          |          |                 |
| -> 4 |      INDEX RANGE SCAN                 | MESSAGE_DEMO_CTC_ID6 |      18 |  133 |      2246 |     +1 |     1 |        0 |   100.00 | Cpu (2194)      |
=============================================================================================================================================================

AWR performance trend for the sqlid for past 5 days:-

@awr_sqlid_perf_trend 9cg18w6mfqphn 5 .25
 
TIME                 EXECUTIONS   ET_S_1EXEC CPU_TIME_S_1EXEC IOWAIT_S_1EXEC CLWAIT_S_1EXEC APWAIT_S_1EXEC CCWAIT_S_1EXEC ROWS_PROCESSED_1EXEC BUFFER_GETS_1EXEC  DISK_READS_1EXEC
------------------- ----------- ------------ ---------------- -------------- -------------- -------------- -------------- -------------------- ----------------- -----------------
23.09.2015 00:15:00          12      117.923          117.667           .001           .000           .000           .000                 .000      23049569.333              .083
23.09.2015 00:30:00          14     1284.493         1280.963           .002           .000           .000           .000                 .000     243817703.571             1.214
23.09.2015 00:45:00          26     1579.532         1574.306           .016           .000           .000           .000                 .000              .000             5.962
23.09.2015 01:00:00          41     1729.918         1718.699           .002           .000           .000           .000                 .000      26916357.415              .317
23.09.2015 01:15:00          26     3850.767         3739.324           .011           .000           .000           .000                 .000              .000             1.731
23.09.2015 01:30:00          27     4565.527         3868.424           .017           .000           .000           .000                 .000      82060590.481             4.407
 

At the first glance it seemed to me as the sql was newly introduced to the database as AWR showed it from the same day, even though I had asked from 5 days snapshots.

The buffer gets per execution for the sql was too high and with multiple sessions with the same sqlid caused latch cbc and in turn high load. So, had to find what was causing this high buffer gets.

The execution plan for sql looked fine. The stats were fine and the index picked by the optimizer was fine too.At this point came in “latchprofx” by Tanel Poder:)

SELECT * FROM (
        SELECT
            event
          , TRIM(TO_CHAR(p1, 'XXXXXXXXXXXXXXXX')) latch_addr
          , TRIM(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1))||'%' PCT
          , COUNT(*)
        FROM
            v$active_session_history
        WHERE
           event = 'latch: cache buffers chains'
       AND session_state = 'WAITING'
       GROUP BY
           event
        , p1
       ORDER BY
           COUNT(*) DESC
   )
   WHERE ROWNUM <= 10
  /  

EVENT                                  LATCH_ADDR        PCT              COUNT(*)
-----------------------------------  ----------------- ---------------- ----------
latch: cache buffers chains           7B9A57DB50        92.2%                 448
latch: cache buffers chains           7AE4EBCD68        2.5%                   12
latch: cache buffers chains           7B682705B8        1%                      5
latch: cache buffers chains           7B6FB0AFD0        .8%                     4
latch: cache buffers chains           7ADA39D6B8        .4%                     2
latch: cache buffers chains           7AE4D206F8        .2%                     1
latch: cache buffers chains           7B4A1A04B0        .2%                     1
latch: cache buffers chains           7B475726C0        .2%                     1
latch: cache buffers chains           7ACB819888        .2%                     1
latch: cache buffers chains           7AC6958BA8        .2%                     1

@latchprofx sid,name,sqlid,object % 7B9A57DB50 100000
 
-- LatchProfX 1.21 by Tanel Poder ( http://www.tanelpoder.com )
 
  SID NAME                                SQLID                      OBJECT       Held       Gets  Held %     Held ms Avg hold ms
----- ----------------------------------- --------------- ----------------- ---------- ---------- ------- ----------- -----------
 7155 cache buffers chains                9cg18w6mfqphn            1ECA461B          4          4     .00       2.025        .506
 1696 cache buffers chains                4ycfn45y3vj5p            1ECA461B          3          3     .00       1.519        .506
 8689 cache buffers chains                9cg18w6mfqphn            1ECA461B          3          3     .00       1.519        .506
 3809 cache buffers chains                9cg18w6mfqphn            1ECA461B          3          3     .00       1.519        .506
 3969 cache buffers chains                9cg18w6mfqphn            1ECA461B          3          3     .00       1.519        .506
 2742 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506
 2482 cache buffers chains                4ycfn45y3vj5p            1ECA461B          2          2     .00       1.013        .506
  439 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506
 1514 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506
 6894 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506
 6524 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506
 6101 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506
 3545 cache buffers chains                9cg18w6mfqphn            1ECA461B          2          2     .00       1.013        .506

All rows point to object “1ECA461B”

@dba 1ECA461B
 
    RFILE#     BLOCK# DUMP_CMD
---------- ---------- ------------------------------------------------
       123     673307 -- alter system dump datafile 123 block 673307

select name from v$datafile where file#=123;
 
NAME
---------------------------------------------------------------
+DATA01/usrprd04/datafile/undotbs1.1134.818027637

Ok, so something related to undo!!!!.

The alert log showed ORA-01555 for an Insert SQL in the same table

Wed Sep 23 02:44:32 2015
ORA-01555 caused by SQL statement below (SQL ID: 72pwjk9bxyygw, Query Duration=13521 sec, SCN: 0x0891.8fff8587):
 
                        INSERT INTO message_demo_ctc (.....)  select ........
						
Wed Sep 23 02:45:26 2015
Completed checkpoint up to RBA [0x2a16.2.10], SCN: 9421305667060

User/Pid                   Megs Used MINUTES_ACTIVE MODULE                                             Recs upd/del
-------------------- --------------- -------------- -------------------------------------------------- ------------
USER_EAS7/(3858)             392.82            264 cli@mesagae_demo_ctc.php                              3,945,675

Once the rollback completed, eventhing was back to normal. The buffer gets for sqlid ‘9cg18w6mfqphn’ was down to 24,950 from ~652617k. We had waited for almost 30-40mins for the rollback, could have killed the SID and SMON would have made rollback using parallel processes, which could have been faster.

ORDIM – Oracle Multimedia – Invalid after upgrade

ORDIM – The Oracle Multimedia component might be in INVALID state after the database upgrade to 12c. While I was testing the upgrade on test server, received below errors after the catctl.pl completion.

Serial   Phase #:69 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at catctl.pl line 1500.
    Time: 49s

------------------------------------------------------
Phases [0-73]         End Time:[2015_08_08 08:06:45]
------------------------------------------------------

Grand Total Time: 4848s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

 1. Evaluate the errors found in the upgrade logs
    and determine the proper action.
 2. Execute the post upgrade script as described in Chapter 3
    of the Database Upgrade Guide.


REASON:
catuppst.sql unable to run in Database: TESTSX Id: 0
        ERRORS FOUND: during upgrade CATCTL ERROR COUNT=7
------------------------------------------------------
Identifier ORDIM 15-08-08 07:56:26 Script = /oracle/app/product/12.1.0.2/rdbms/admin
ERROR = [ORA-06550: line 76, column 17: PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 76, column 5:
PL/SQL: SQL Statement ignored
]

Multiple insert and update sqls failed for tables in MDSYS schema.At this point, dba_registry showed

SQL> @dba_registry

COMP_ID  SCHEMA       STATUS       VERSION      COMP_NAME
-------- ------------ ------------ ------------ -----------------------------------
APS      SYS          REMOVED      11.1.0.7.0   OLAP Analytic Workspace
CATALOG  SYS          UPGRADED     12.1.0.2.0   Oracle Database Catalog Views
CATJAVA  SYS          VALID        12.1.0.2.0   Oracle Database Java Packages
CATPROC  SYS          INVALID      12.1.0.2.0   Oracle Database Packages and Types
CONTEXT  CTXSYS       VALID        12.1.0.2.0   Oracle Text
JAVAVM   SYS          VALID        12.1.0.2.0   JServer JAVA Virtual Machine
ORDIM    ORDSYS       INVALID      12.1.0.2.0   Oracle Multimedia
OWM      WMSYS        VALID        12.1.0.2.0   Oracle Workspace Manager
RAC      SYS          OPTION OFF   12.1.0.2.0   Oracle Real Application Clusters
SDO      MDSYS        REMOVED      11.1.0.7.0   Spatial
XDB      XDB          VALID        12.1.0.2.0   Oracle XML Database
XML      SYS          VALID        12.1.0.2.0   Oracle XDK
XOQ      SYS          REMOVED      11.1.0.7.0   Oracle OLAP API

Running UTLRP.sql would show below in the end :-

Locator INVALID OBJECTS: CS_SRS_TRIGGER – INVALID – TRIGGER
Locator INVALID OBJECTS: OGIS_CRS_DELETE_TRIGGER – INVALID – TRIGGER
Locator INVALID OBJECTS: OGIS_CRS_INSERT_TRIGGER – INVALID – TRIGGER

The reason for this is the missing VIEW “MDSYS”.”SDO_COORD_REF_SYSTEM”.

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW MDSYS.SDO_COORD_REF_SYSTEM ("SRID", "COORD_REF_SYS_NAME",
"COORD_REF_SYS_KIND", "COORD_SYS_ID", "DATUM_ID", "GEOG_CRS_DATUM_ID", "SOURCE_GEOG_SRID", "PROJECTION_CONV_ID",
"CMPD_HORIZ_SRID","CMPD_VERT_SRID", "INFORMATION_SOURCE", "DATA_SOURCE", "IS_LEGACY", "LEGACY_CODE", "LEGACY_WKTEXT",
"LEGACY_CS_BOUNDS", "IS_VALID", "SUPPORTS_SDO_GEOMETRY") AS SELECT SRID,COORD_REF_SYS_NAME,COORD_REF_SYS_KIND,COORD_SYS_ID,
DATUM_ID,GEOG_CRS_DATUM_ID,SOURCE_GEOG_SRID,PROJECTION_CONV_ID,CMPD_HORIZ_SRID,CMPD_VERT_SRID,INFORMATION_SOURCE,DATA_SOURCE,
IS_LEGACY,LEGACY_CODE,LEGACY_WKTEXT,LEGACY_CS_BOUNDS,IS_VALID,SUPPORTS_SDO_GEOMETRY FROM MDSYS.SDO_COORD_REF_SYS;

After creating the view, recompile the triggers and run utlrp.

grant SELECT on MDSYS.SDO_COORD_REF_SYSTEM to PUBLIC;

ALTER TRIGGER MDSYS.CS_SRS_TRIGGER COMPILE;
show errors
ALTER TRIGGER MDSYS.OGIS_CRS_DELETE_TRIGGER COMPILE;
show errors
ALTER TRIGGER MDSYS.OGIS_CRS_INSERT_TRIGGER COMPILE;
show errors

On production, I created the view as part of pre-task and then upgraded the database.The only change in view ddl was I had to remove “NONEDITIONABLE” keyword to create it as compatible parameter was set to 11.2.0.3.

On Production

SQL> @find_obj
Enter value for object_name: SDO_COORD_REF_SYSTEM
old   1: select owner,object_name,object_id,object_type,status,created,last_ddl_time from dba_objects where object_name=upper('&object_name')
new   1: select owner,object_name,object_id,object_type,status,created,last_ddl_time from dba_objects where object_name=upper('SDO_COORD_REF_SYSTEM')

OWNER        OBJECT_NAME                OBJECT_ID OBJECT_TYPE        STATUS  CREATED                   LAST_DDL_TIME
------------ ------------------------- ---------- ------------------ ------- ------------------------- -------------------------
PUBLIC       SDO_COORD_REF_SYSTEM        39533538 SYNONYM            VALID   18-08-15 01:30:26         18-08-15 01:30:26
MDSYS        SDO_COORD_REF_SYSTEM        39500022 VIEW               VALID   17-08-15 18:28:03         18-08-15 01:30:26

PRCA-1057 : Failed to retrieve the password file location used by ASM

After the Oracle Restart (Grid Infrastructure) upgrade from 11.2. to 12.1.0.2 , you might encounter the below lines in the ASM alert log file

Tue Jun 23 01:16:01 2015
WARNING: unknown state for ASM password file location resource, Return Value: 3
WARNING: unknown state for ASM password file location resource, Return Value: 3

After the upgrade, below command can get the error

[grid@oracle1 ~]$ srvctl config asm
ASM home: <crs home=''>
PRCA-1057 : Failed to retrieve the password file location used by ASM asm
PRCR-1097 : Resource attribute not found: PWFILE
[grid@oracle1 ~]$

This issue is unpublished bug 19449701.From 12.1 onwards ASM instance has a new attribute called PWFILE.

So , the solution is

[grid@oracle1 ~]$
[grid@oracle1 ~]$ crsctl stop resource -all
[grid@oracle1 ~]$ crsctl stat res -t
[grid@oracle1 ~]$ srvctl remove asm -force
[grid@oracle1 ~]$ srvctl add asm -listener LISTENER -spfile "+DATA/asm/asmparameterfile/registry.201.883099876" -pwfile "/oracle/grid/product/12.1/dbs/orapw+ASM" -diskstring "/dev/mapper/*p1"
[grid@oracle1 ~]$ crsctl start resource -all
[grid@oracle1 ~]$ srvctl config asm
ASM home: 
Password file: /oracle/grid/product/12.1/dbs/orapw+ASM
ASM listener: LISTENER
Spfile: +DATA/asm/asmparameterfile/registry.201.883099876
ASM diskgroup discovery string: /dev/mapper/*p1

Reference :- Oracle Restart: WARNING: unknown state for ASM password file location resource, Return Value: 3 (Doc ID 1935891.1)