探索Oracle之数据库升级七 11gR2 to 12c 升级完成后插入PDB

浏览数：20 / 时间：2015年06月12日

探索Oracle之数据库升级七
11gR2 to 12c 升级完成后插入PDB

前言：

从Oracle 12c开始，引入了容器数据库的概念，可以实现数据库插拔操作，如下图:

现在我们就来看看如何将11.2.0.4的数据库插入到12c的CDP里面去，让其成为一个PDB

数据库。

一、查看数据库信息:

SQL> col BANNER format a80
SQL> set line 300
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production
SQL> select cdb,name,dbid from v$database;
CDB NAME DBID
--- --------- ----------
NO WOO 4199532651

从这里我们可以看到实际上通过升级之后上来的数据库还是一个non-CDB，并非CDB数据库，那么这个时候我们需要在这个none-CDB库中生成用户PDB的XML文件，再创建CDB数据库进行插入进去。

二、查看表空间及数据文件信息：

SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 0
1 SYSAUX YES NO YES 0
2 UNDOTBS1 YES NO YES 0
4 USERS YES NO YES 0
3 TEMP NO NO YES 0
SQL> set line 300
SQL> col file_name format a40
SQL> col tablespace_name format a10
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE FILE_NAME
---------- ----------------------------------------
USERS /DBData/woo/woo/users01.dbf
UNDOTBS1 /DBData/woo/woo/undotbs01.dbf
SYSAUX /DBData/woo/woo/sysaux01.dbf
SYSTEM /DBData/woo/woo/system01.dbf

三、创建用于生成PDB的XML文件

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2772574208 bytes

Fixed Size 2292240 bytes

Variable Size 2533361136 bytes

Database Buffers 218103808 bytes

Redo Buffers 18817024 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> exec dbms_pdb.describe(pdb_descr_file => \‘/home/oracle/woo_ora11g.xml\‘);

PL/SQL procedure successfully completed.

SQL> host ls -rtl /home/oracle/woo*.xml

-rw-r--r-- 1 oracle oinstall 4147 Nov 27 19:17 /home/oracle/woo_ora11g.xml

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

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

SQL> set serveroutput on;
SQL> declare
2 compat boolean := FALSE;
3 begin
4 compat := dbms_pdb.check_plug_compatibility(pdb_descr_file => \‘/home/oracle/woo_ora11g.xml\‘);
5 if compat
6 then
7 dbms_output.put_line(\‘YES\‘);
8 else
9 dbms_output.put_line(\‘No\‘);
10 end if;
11 end;
12 /
No
PL/SQL procedure successfully completed.

因为是第一次插入，所以执行结果显示为NO，可以忽略继续插入。

五、创建CDB数据库
   5.1 执行dbca创建cdb数据库

    5.2 指定需要创建的cdb数据库名称

     5.3 创建CDB前检查

    5.4 Summar 点击Finish开始创建

    5.5 现在正在开始创建

5.6 至此已经创建完成，告诉我们CDB数据库的信息

   5.7 点击Close 关闭创建页面

六、查看当前pdb信息

[oracle@db01 ~]$ export ORACLE_SID=woo12c
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4107385256 PDB$SEED READ ONLY
3 3926295770 PDB01 READ WRITE

七、将non-cdb数据库插入到cdb中成为一个pdb

SQL> CREATE PLUGGABLE DATABASE woo_ora11g USING \‘/home/oracle/woo_ora11g.xml\‘;

Pluggable database created.

八、查看alert日志相关信息
将Non-CDB插入到CDB中成为一个PDB输出的Alert 日志。

CREATE PLUGGABLE DATABASE woo_ora11g USING ‘/home/oracle/woo_ora11g.xml‘
Thu Nov 27 23:45:05 2014
****************************************************************
Pluggable Database WOO_ORA11G with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
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#13 to file$(old file#1)
Adding new file#14 to file$(old file#2)
Adding new file#15 to file$(old file#4)
Marking tablespace #2 invalid since it is not present in the describe file
Marking tablespace #5 invalid since it is not present in the describe file
Successfully created internal service woo_ora11g at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=4 local
****************************************************************
Post plug operations are now complete.
Pluggable database WOO_ORA11G with pdb id - 4 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE woo_ora11g USING ‘/home/oracle/woo_ora11g.xml‘