oracle12.2将共享undo转为localundo的方法

SYS@cdbtest1(CDB$ROOT)> select banner   from v$version where rownum=1;

BANNER

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

Oracle Database 12c Enterprise Edition   Release 12.2.0.1.0 - 64bit Production

SYS@cdbtest1(CDB$ROOT)> col   PROPERTY_NAME for a25

SYS@cdbtest1(CDB$ROOT)> col   PROPERTY_VALUE for a30

SYS@cdbtest1(CDB$ROOT)> select   PROPERTY_NAME,PROPERTY_VALUE from database_properties where   property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED        FALSE

SYS@cdbtest1(CDB$ROOT)> set line   200        

SYS@cdbtest1(CDB$ROOT)> col FILE_NAME   for a50

SYS@cdbtest1(CDB$ROOT)> select   a.con_id,a.tablespace_name,b.file_name from cdb_tablespaces a,CDB_DATA_FILES   b where    a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and   a.CONTENTS='UNDO';

      CON_ID TABLESPACE_NAME                  FILE_NAME

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

           1 UNDOTBS1                         /oradata/cdb/CDBTEST1/datafile/o1_mf_undotbs1_f7nz

                                            8sl3_.dbf

1.关闭cdb实例

2.打开pdb到upgrade模式，startup upgrade

3.在CDB$ROOT中执行：ALTER DATABASE LOCAL UNDO ON;

4.关闭cdb实例，然后重启

SYS@cdbtest1(CDB$ROOT)> shutdown   immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@cdbtest1(CDB$ROOT)> startup   upgrade

ORACLE instance started.

Total System Global Area 2214592512 bytes

Fixed Size                  8795040 bytes

Variable Size            1845496928 bytes

Database Buffers          352321536 bytes

Redo Buffers                7979008 bytes

Database mounted.

Database opened.

SYS@cdbtest1(CDB$ROOT)> sho pdbs

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

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

           2 PDB$SEED                         MIGRATE    YES

           3 CLONEMING                        MOUNTED

           4 MING                             MOUNTED

SYS@cdbtest1(CDB$ROOT)> alter database local undo on;

Database altered.

SYS@cdbtest1(CDB$ROOT)> shutdown   immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@cdbtest1(CDB$ROOT)> startup

ORACLE instance started.

Total System Global Area 2214592512 bytes

Fixed Size                  8795040 bytes

Variable Size            1845496928 bytes

Database Buffers          352321536 bytes

Redo Buffers                7979008 bytes

Database mounted.

Database opened.

SYS@cdbtest1(CDB$ROOT)> col   PROPERTY_NAME for a25

SYS@cdbtest1(CDB$ROOT)> col   PROPERTY_VALUE for a30

SYS@cdbtest1(CDB$ROOT)>  select PROPERTY_NAME,PROPERTY_VALUE from   database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME             PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED        TRUE

SYS@cdbtest1(CDB$ROOT)> select   a.con_id,a.tablespace_name,b.file_name,b.bytes/1024/1024 M from   cdb_tablespaces a,CDB_DATA_FILES b where    a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';

      CON_ID TABLESPACE_NAME                  FILE_NAME                                                     M

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

           1 UNDOTBS1                         /oradata/cdb/CDBTEST1/datafile/o1_mf_undotbs1_f7nz        835

                                            8sl3_.dbf

           3 UNDO_1                           /oradata/cdb/CDBTEST1/64B070B959B22CC9E053023DA8C0        300

                                            935F/datafile/o1_mf_undo_1_fxv9cmnl_.dbf

           4 UNDO_1                           /oradata/cdb/CDBTEST1/64AE1A20BB9C1B5FE053023DA8C0        300

                                            6963/datafile/o1_mf_undo_1_fxv9cvrd_.dbf

alter session set   "_oracle_script"=true;  --修改隐含参数，就可以避免ORA-65017:   seed pluggable database may not be dropped or altered

ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE ;

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

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

           2 PDB$SEED                         READ WRITE NO

ALTER SESSION SET CONTAINER=PDB$SEED;

alter database datafile   '/oradata/cdb/CDBTEST1/6498843CCFAD2D1FE053023DA8C0AC32/datafile/o1_mf_undotbs1_f7nz8x0z_.dbf'   resize 300m;

SYS@cdbtest1(CDB$ROOT)> alter session   set "_oracle_script"=true;

Session altered.

SYS@cdbtest1(CDB$ROOT)> alter   pluggable database "pdb$seed" close immediate;

Pluggable database altered.

SYS@cdbtest1(CDB$ROOT)> sho pdbs

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

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

           2 PDB$SEED                         MOUNTED

           3 CLONEMING                        READ WRITE NO

           4 MING                             READ WRITE NO

SYS@cdbtest1(CDB$ROOT)> alter   pluggable database "pdb$seed" open read only;

Pluggable database altered.

SYS@cdbtest1(CDB$ROOT)> sho pdbs

      CON_ID CON_NAME                         OPEN MODE  RESTRICTED

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

           2 PDB$SEED                         READ ONLY  NO

           3 CLONEMING                        READ WRITE NO

           4 MING                           READ WRITE NO