12.1.0.2的PDB升级到12.2.0.1的实验

1. 实验环境

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 17 10:18:00 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs;

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

SQL> alter session set container=MYPDB;
Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL12C/521F654BCCB57997E0538210A8C0647F/datafile/o1_mf
system_dn93vj7r_.dbf
/u01/app/oracle/oradata/ORCL12C/521F654BCCB57997E0538210A8C0647F/datafile/o1_mf
sysaux_dn93vj8d_.dbf
/u01/app/oracle/oradata/ORCL12C/521F654BCCB57997E0538210A8C0647F/datafile/o1_mf
users_dn93w5dv_.dbf

2. 创建测试用户和表空间

SQL> create tablespace test datafile '/u01/app/oracle/oradata/ORCL12C/521F654BCCB57997E0538210A8C0647F/datafile/test01.dbf' size 10M;

Tablespace created.

SQL> create user test identified by oracle default tablespace test;
User created.

SQL> grant connect,resource to test;
Grant succeeded.

SQL> alter user test quota unlimited on test;
User altered.

SQL> create table test.test_data tablespace users as select *from all_objects where rownum<1000;
create table test.test_data tablespace users as select *from all_objects where rownum<1000
                                                                                         *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL> alter user test quota unlimited on users;
User altered.

SQL> create table test.test_data tablespace users as select *from all_objects where rownum<1000;
Table created.

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 MYPDB                          READ WRITE NO

3. unplug pdb

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database mypdb close;

Pluggable database altered.

SQL> alter pluggable database mypdb unplug into '/tmp/mypdb.xml';

Pluggable database altered.

4. 传输文件

[oracle@oracle tmp]$ scp mypdb.xml 192.168.16.81:/tmp/

[oracle@oracle tmp]$ cd /u01/app/oracle/oradata/ORCL12C/521F654BCCB57997E0538210A8C0647F/datafile/
[oracle@oracle datafile]$ ll
total 855132
-rw-r-----. 1 oracle oinstall 597696512 Jun 17 10:32 o1_mf_sysaux_dn93vj8d_.dbf
-rw-r-----. 1 oracle oinstall 262152192 Jun 17 10:32 o1_mf_system_dn93vj7r_.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jun 17 10:10 o1_mf_temp_dn93vj8d_.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jun 17 10:32 o1_mf_users_dn93w5dv_.dbf
-rw-r-----. 1 oracle oinstall  10493952 Jun 17 10:32 test01.dbf
[oracle@oracle datafile]$ scp * 192.168.16.81:/u01/app/oracle/oradata/zylong/mypdb

5. 修改数据文件路径

[oracle@12cr2 mypdb]$ grep path /tmp/mypdb.xml
      <path>/u01/app/oracle/oradata/zylong/mypdb/o1_mf_system_dn93vj7r_.dbf</path>
      <path>/u01/app/oracle/oradata/zylong/mypdb/o1_mf_sysaux_dn93vj8d_.dbf</path>
      <path>/u01/app/oracle/oradata/zylong/mypdb/o1_mf_temp_dn93vj8d_.dbf</path>
      <path>/u01/app/oracle/oradata/zylong/mypdb/o1_mf_users_dn93w5dv_.dbf</path>
      <path>/u01/app/oracle/oradata/zylong/mypdb/test01.dbf</path>

6. 创建PDB

SQL> create pluggable database mypdb using '/tmp/mypdb.xml' nocopy;
create pluggable database mypdb using '/tmp/mypdb.xml' nocopy
*
ERROR at line 1:
ORA-65346: The PDB version is lower and components (APEX) are missing in CDB.


[oracle@12cr2 mypdb]$ vi /tmp/mypdb.xml
<APEX>4.2.5.00.08:1</APEX>

SQL> create pluggable database mypdb using '/tmp/mypdb.xml' nocopy;
create pluggable database mypdb using '/tmp/mypdb.xml' nocopy
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/zylong/mypdb/o1_mf_temp_dn93vj8d_.dbf'


SQL> create pluggable database mypdb using '/tmp/mypdb.xml' nocopy tempfile reuse;

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SEED_PDB                       MOUNTED
         4 NONCDB_PDP                     MOUNTED
         5 CLON_PDB                       MOUNTED
         6 TEST_PDB                       MOUNTED
         7 PDB4                           MOUNTED
         9 MYPDB                          MOUNTED
SQL> alter pluggable database MYPDB open;
Warning: PDB altered with errors.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SEED_PDB                       MOUNTED
         4 NONCDB_PDP                     MOUNTED
         5 CLON_PDB                       MOUNTED
         6 TEST_PDB                       MOUNTED
         7 PDB4                           MOUNTED
         9 MYPDB                          MIGRATE    YES

SQL> select MESSAGE from pdb_plug_in_violations order by time;

MESSAGE
-------------------------------------------------------------------------------
CDB parameter memory_target mismatch: Previous 1536M Current 0
PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.
Sync PDB failed with ORA-959 during 'create user c##cdbadmin identified by * def
ault tablespace users temporary tablespace temp container = all'

Database option APS mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

Database option DV mismatch: PDB installed version 12.1.0.2.0. CDB installed ver
sion 12.2.0.1.0.


MESSAGE
--------------------------------------------------------------------------------
Database option CATALOG mismatch: PDB installed version 12.1.0.2.0. CDB installe
d version 12.2.0.1.0.

Database option CATJAVA mismatch: PDB installed version 12.1.0.2.0. CDB installe
d version 12.2.0.1.0.

Database option CATPROC mismatch: PDB installed version 12.1.0.2.0. CDB installe
d version 12.2.0.1.0.

Database option CONTEXT mismatch: PDB installed version 12.1.0.2.0. CDB installe
d version 12.2.0.1.0.

CDB parameter compatible mismatch: Previous '12.1.0.2.0' Current '12.2.0'
Database option JAVAVM mismatch: PDB installed version 12.1.0.2.0. CDB installed
 version 12.2.0.1.0.

Database option OLS mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

Database option ORDIM mismatch: PDB installed version 12.1.0.2.0. CDB installed
version 12.2.0.1.0.


Database option OWM mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

Database option SDO mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

Database option XDB mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

Database option XML mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

Database option XOQ mismatch: PDB installed version 12.1.0.2.0. CDB installed ve
rsion 12.2.0.1.0.

CDB parameter memory_target mismatch: Previous 1536M Current 0
PDB's version does not match CDB's version: PDB's version 12.1.0.2.0. CDB's vers
ion 12.2.0.1.0.
20 rows selected.

7. 执行dbupgrade

$ORACLE_HOME/bin/dbupgrade -c MYPDB

8. 启动PDB

SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         9 MYPDB                          READ WRITE NO