Oracle12c Non-CDB转成PDB

1. 说明

前面写了一篇文章,将Oracle 12c版本之前的数据库升级到了12.1.0.2,参考文章:Oracle 12c升级指南
多租户是Oracle 12c中新特性,Oracle也在不停地宣传,那如何将升级起来的数据库转成一个PDB呢,本篇文章将解决这个问题。

2. 转换过程

2.1 当前环境

SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled',
  2 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
NAME      Multitenant Option         OPEN_MODE            CON_ID
--------- -------------------------- -------------------- ----------
TEST      Regular 12c Database:      READ WRITE           0

当前是个Oracle 12c的非多租户环境

SQL> set linesize 140
SQL> column action_time format a30
SQL> column id format a10
SQL> column action format a10
SQL> column version format a10
SQL> column bundle format a10
SQL> column comments format a20
SQL> 
SQL> select substr(action_time,1,30)action_time,
  2  substr(id,1,10) id,
  3  substr(action,1,10) action,
  4  substr(version,1,8) version,
  5  substr(BUNDLE_SERIES,1,6)bundle,
substr(comments,1,20) comments
from registry$history;
  6    7  
ACTION_TIME                    ID         ACTION     VERSION    BUNDLE     COMMENTS
------------------------------ ---------- ---------- ---------- ---------- --------------------
24-AUG-13 12.03.45.119862 PM   0          APPLY      11.2.0.4   PSU        Patchset 11.2.0.2.0
06-FEB-17 11.01.09.145439 AM   0          APPLY      11.2.0.4   PSU        Patchset 11.2.0.2.0
                                          BOOTSTRAP  12.1.0.2              RDBMS_12.1.0.2.0DBPS
07-FEB-17 03.52.25.472189 PM   8289601    VIEW INVAL                       view invalidation
07-FEB-17 03.53.39.501342 PM              UPGRADE    12.1.0.2              Upgraded from 11.2.0

从PSU的情况看,当前库是从11.2.0.4升级上来的。其实这个库就是我写Oracle12c升级指南时使用的测试库。

2.2 停止数据库并起动到readonly状态

SQL> shutdown immediate
SQL> startup open read only
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY

2.3 生成数据库的描述XML文件

SQL> BEGIN
  2 DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/test.xml');
  3 END;
  4 /
PL/SQL procedure successfully completed.

然后停止数据库

SQL> shutdown immedaite

2.4 创建CDB数据库

使用DBCA创建一个空的CDB,比较简单,创建过程如下




这里创建一个空的容器库









2.5 检查升级后的数据库是否适合以PDB的方式插入到CDB中

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2  compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/test.xml')
  3  WHEN TRUE THEN 'YES'
  4  ELSE 'NO'
  5  END;
  6  BEGIN
  7  DBMS_OUTPUT.PUT_LINE(compatible);
  8  END;
  9  /
NO

PL/SQL procedure successfully completed.

如果检查有问题,可以查看PDB_PLUG_IN_VIOLATIONS视图,查看问题原因。

SQL> set linesize 180
SQL> col cause for a20
SQL> col name for a20
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TEST';

NAME                 CAUSE                TYPE      MESSAGE                             STATUS
-------------------- -------------------- --------- ----------------------------------- ---------
TEST                 Non-CDB to PDB       WARNING   PDB plugged in is a non-CDB,        PENDING
                                                    requires noncdb_to_pdb.sql be run.

TEST                 OPTION               WARNING   Database option DV mismatch: PDB    PENDING
                                                    installed version NULL. CDB
                                                    installed version 12.1.0.2.0.

TEST                 OPTION               WARNING   Database option OLS mismatch: PDB   PENDING
                                                    installed version NULL. CDB
                                                    installed version 12.1.0.2.0.

TEST                 Parameter            WARNING   CDB parameter sga_target mismatch:  PENDING
                                                    Previous 1920M Current 3G

TEST                 Parameter            WARNING   CDB parameter compatible mismatch:  PENDING
                                                    Previous '11.2.0.4.0' Current
                                                    '12.1.0.2.0'

TEST                 Parameter            WARNING   CDB parameter pga_aggregate_target  PENDING
                                                    mismatch: Previous 478M Current 1G

TEST                 SQL Patch            ERROR     PSU bundle patch 170117 (DATABASE   PENDING
                                                    PATCH SET UPDATE 12.1.0.2.170117):
                                                    Installed in the PDB but not in
                                                    the CDB.


7 rows selected.

关于ERROR PSU bundle patch 1 (PSU Patch 12345): Installed in the CDB but not in the PDB.错误,参考:
Multitenant Unplug/Plug Best Practices (文档 ID 1935365.1)
这个错误的原因是:要插入的库已经打了SQL补丁170117,而现在的CDB没有打这个补丁,检查当前的CDB PSU信息:

SQL> alter session set container = CDB$ROOT;

Session altered.
SQL> set linesize 140
SQL> column action_time format a30
SQL> column id format a10
SQL> column action format a10
SQL> column version format a10
SQL> column bundle format a10
SQL> column comments format a20
SQL> 
SQL> select substr(action_time,1,30)action_time,
  2  substr(id,1,10) id,
  3  substr(action,1,10) action,
  4  substr(version,1,8) version,
  5  substr(BUNDLE_SERIES,1,6)bundle,
  6  substr(comments,1,20) comments
  7  from registry$history;

no rows selected

确实没有打这个SQL PSU,应用SQL patch如下:

% sqlplus /nolog
SQL> Connect / as sysdba
SQL> startup
SQL> alter pluggable database all open;
SQL> quit
% cd $ORACLE_HOME/OPatch
% ./datapatch -verbose

datapatch执行日志

$ ./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Wed Feb  8 09:14:14 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /data/oracle/cfgtoollogs/sqlpatch/sqlpatch_14698_2017_02_08_09_14_14/sqlpatch_invocation.log

Connecting to database...OK
Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series PSU:
  ID 170117 in the binary registry and not installed in any PDB

Adding patches to installation queue and performing prereq checks...
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    Nothing to roll back
    The following patches will be applied:
      24732082 (DATABASE PATCH SET UPDATE 12.1.0.2.170117)

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 24732082 apply (pdb CDB$ROOT): SUCCESS
  logfile: /data/oracle/cfgtoollogs/sqlpatch/24732082/20904347/24732082_apply_CTEST_CDBROOT_2017Feb08_09_14_43.log (no errors)
Patch 24732082 apply (pdb PDB$SEED): SUCCESS
  logfile: /data/oracle/cfgtoollogs/sqlpatch/24732082/20904347/24732082_apply_CTEST_PDBSEED_2017Feb08_09_15_07.log (no errors)
SQL Patching tool complete on Wed Feb  8 09:15:30 2017

sql patch后的PSU信息

SQL> set linesize 180
SQL> column action_time format a30
SQL> column id format a10
SQL> column action format a10
SQL> column version format a10
SQL> column bundle format a10
SQL> column description format a45
SQL> select substr(action_time,1,30) action_time,
  2  substr(patch_id,1,10) id,
  3  substr(action,1,10) action,
  4  substr(version,1,8) version,
  5  substr(BUNDLE_SERIES,1,6)bundle,
  6  description
  7  from dba_registry_sqlpatch;
ACTION_TIME                    ID         ACTION     VERSION    BUNDLE     DESCRIPTION
------------------------------ ---------- ---------- ---------- ---------- ---------------------------------------------
07-FEB-17 04.01.40.509804 PM   24732082   APPLY      12.1.0.2   PSU        DATABASE PATCH SET UPDATE 12.1.0.2.170117
1 row selected.

再次检查,就可以插入了

SQL> DECLARE
  2  compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/test.xml')
  3  WHEN TRUE THEN 'YES'
  4  ELSE 'NO'
  5  END;
  6  BEGIN
  7  DBMS_OUTPUT.PUT_LINE(compatible);
  8  END;
  9  /
YES

PL/SQL procedure successfully completed.

2.6 正式插入

当前的PDB信息

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
 
    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 2835804653 PDB$SEED                       READ ONLY

插入过程

SQL> CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml' NOCOPY;
CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml' NOCOPY
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file
'/data/oracle/oradata/test/temp01.dbf'

要插入的数据库有临时文件,需要使用tempfile reuse,或者把temp文件删掉

SQL> CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml' NOCOPY tempfile reuse;

Pluggable database created.

注意:插入完不要打开数据库,插入的alert日志如下:

CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml' NOCOPY
Wed Feb 08 09:43:36 2017
**************************************************************
Pluggable Database TEST with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
**************************************************************
Database Characterset for TEST is ZHS16GBK
Deleting old file#1 from file$ 
Deleting old file#2 from file$ 
Deleting old file#3 from file$ 
Deleting old file#4 from file$ 
Deleting old file#5 from file$ 
Adding new file#11 to file$(old file#1) 
Adding new file#12 to file$(old file#2) 
Adding new file#13 to file$(old file#4) 
Marking tablespace #2 invalid since it is not present               in the describe file 
Successfully created internal service test at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
**************************************************************
Post plug operations are now complete.
Pluggable database TEST with pdb id - 3 is now marked as NEW.
**************************************************************
Completed: CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml' NOCOPY

查看插入后的PDB信息

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST                           MOUNTED

2.7 完成插入

SQL> ALTER SESSION SET CONTAINER=TEST;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

注意:noncdb_to_pdb.sql脚本必须要插入的库第一次打开之前运行,
参考:Creating a PDB Using a Non-CDB
noncdb_to_pdb.sql执行完就可以打开数据库了

SQL> ALTER SESSION SET CONTAINER=TEST;
 
Session altered.
 
SQL> ALTER PLUGGABLE DATABASE OPEN;
 
Pluggable database altered.

3. 其它

noncdb_to_pdb.sql运行可能会碰到的一些问题:
NONCDB_TO_PDB.SQL SCRIPT HANGS DURING UPGRADE (文档 ID 2015965.1)
Views Invalid After Running “noncdb_to_pdb.sql” (文档 ID 2030039.1)
ORA-01405 while running “noncdb_to_pdb.sql” (文档 ID 2000882.1)
ORA-600 [16201] Running “noncdb_to_pdb.sql” Script (文档 ID 2039530.1)
Bug 18189497 – ORA-4068 during noncdb_to_pdb.sql (文档 ID 18189497.8)

关于紫砂壶

感悟技术人生
此条目发表在Oracle基础知识分类目录,贴了标签。将固定链接加入收藏夹。