Oracle 在从11g升级到12c的数据库中，异步全局索引无法在12c中正常工作

我们观察到以下情况。
1) 11g数据库升级到12c。将兼容参数设置为12c版本。重新启动数据库。创建了一个表，对表进行了分区，然后将数据插入到该表中。然后放下分区。这在12c中如预期的那样工作。

2) 表和分区位于11g数据库中。11g数据库升级到12c。兼容参数设置为12c版本。重新启动数据库。然后删除现有分区。那么分区下降在12c中不能像预期的那样工作。它以旧的11g方式工作。

我们要确保给出的场景point 2上述工作符合预期的12c。

升级到12c的表，数据和分区的数据库有什么问题？为什么在将兼容参数设置为12c版本后，分区删除不起作用 (这些表、分区和全局索引在11g数据库和数据库升级到12c)？为了使分区表和全局索引与12c兼容，还需要做其他事情吗？

11g版本: 11.2.0.4
12c版本: 12.1.0.2

使用DBUA完成升级

我认为您需要通过完整的测试用例与支持人员取得联系，因为我无法重现该行为。这是我的测试用例

--
-- 11.2.0.4 database
--
SQL> select sys.database_name from dual;

DATABASE_NAME
----------------------------------------------------------------------------------------------------------------------------------
DBPAR

1 row selected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
SQL>
SQL> create table t ( x int, y1 int, y2 int, z char(200) )
  2  partition by range ( x )
  3  (
  4    partition p1 values less than ( 500000 ),
  5    partition p2 values less than ( 1000000 ),
  6    partition p3 values less than ( 9999999)
  7  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rownum, rownum, rownum, rownum
  3  from
  4  ( select 1 from dual connect by level <= 2000 ),
  5  ( select 1 from dual connect by level <= 2000 )
  6  ;

4000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ix1 on t ( y1 );

Index created.

SQL>
SQL> create index ix2 on t ( y2 )
  2  global partition by range ( y2 )
  3  (
  4    partition p1 values less than ( 800000 ),
  5    partition p2 values less than ( maxvalue )
  6  );

Index created.

SQL>
SQL> conn mcdonac/*******
Connected.

SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                             3
redo size                                                               792

2 rows selected.

SQL> set timing on
SQL> alter table t drop partition p2 update global indexes;

Table altered.

Elapsed: 00:00:01.63
SQL> set timing off
SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                         25831
redo size                                                          21849936

2 rows selected.

SQL>

--
-- So you can see from above...big redo cost, and 'slow' (ie, 1.5 seconds) 
-- to drop the partition.  
--
-- Now I'll create an identical copy of that table, but not do the index maintenance
-- until we've upgrade to 12c
--

SQL> create table t1 ( x int, y1 int, y2 int, z char(200) )
  2  partition by range ( x )
  3  (
  4    partition p1 values less than ( 500000 ),
  5    partition p2 values less than ( 1000000 ),
  6    partition p3 values less than ( 9999999)
  7  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into t1
  2  select rownum, rownum, rownum, rownum
  3  from
  4  ( select 1 from dual connect by level <= 2000 ),
  5  ( select 1 from dual connect by level <= 2000 )
  6  ;

4000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ixt1 on t1 ( y1 );

Index created.

SQL>
SQL> create index ixt2 on t1 ( y2 )
  2  global partition by range ( y2 )
  3  (
  4    partition p1 values less than ( 800000 ),
  5    partition p2 values less than ( maxvalue )
  6  );

Index created.


--
-- So now I upgraded the database to 12.1.0.2 using standard dbua.  By default, the 
-- database is 12c but compatible is 11, so I need to adjust that and bounce 
-- the database to get the change
--

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE
SQL>
SQL> alter system set compatible = '12.1.0.2.0' scope=spfile;

System altered.

SQL> conn / as sysdba
Connected.

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

Total System Global Area 2097152000 bytes
Fixed Size                  3047568 bytes
Variable Size             603983728 bytes
Database Buffers         1476395008 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.

SQL> conn mcdonac/******
Connected.


SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.1.0.2.0
noncdb_compatible                    boolean     FALSE
SQL>
SQL>

--
-- So now I am an 12c version and 12c compatible.  Let's redo that same
-- test now with table T1
--

SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                             6
redo size                                                              1496

SQL>
SQL> set timing on
SQL> alter table t1 drop partition p2 update global indexes;

Table altered.

Elapsed: 00:00:00.11
SQL> set timing off
SQL>
SQL> select n.name, s.value
  2  from v$mystat s, v$statname n
  3  where s.statistic# = n.statistic#
  4  and n.name in ('redo size','db block gets');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                           137
redo size                                                             20844

正如你所看到的-最小的重做和非常快。指数维护已推迟。