oracle 19c pdb cdb,12c、19c 从 no-cdb升级到pdb步骤

如果把数据库从11g 升级到12c，或者在12c中创建的，就是NON CDB，那么这样的数据库就是普通的单实例，和12c 之前的数据库没有区别，但12c 的特点就是CDB 管理，所以既然上12c，还是要切换成CDB 进行管理。

所以下面的测试步骤，就是把NON CDB 切换成CDB的步骤。

1.当前环境说明

当前数据库里已经启动了2个实例： bishuo和 test。

bishuo:/home/oracle@oracle>ps -ef |grep pmon

oracle 3666 1 0 13:25 ? 00:00:00 ora_pmon_bishuo

oracle 4507 1 0 13:49 ? 00:00:00 ora_pmon_test

oracle 4693 189245 0 13:49 pts/1 00:00:00 grep pmon

其中bishuo数据库是12C的CDB数据库：

SQL> select name,CDB from v$database;

NAME CDB

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

BISHUO YES

test数据库是12C的non cdb数据库

SQL> select name,CDB from v$database;

NAME CDB

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

TEST NO

SQL> set lin 130;

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 Linux: Version 12.1.0.2.0 - Production                          0

NLSRTL Version 12.1.0.2.0 - Production                              0

2.开始切换

2.1 Cleanly shutdown 数据库并用read only 打开

SQL> select name,cdb from v$database;

NAME CDB

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

TEST NO

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup open read only;

ORACLE instance started.

Total System Global Area 6442450944 bytes

Fixed Size 4511656 bytes

Variable Size 1124075608 bytes

Database Buffers 5301600256 bytes

Redo Buffers 12263424 bytes

Database mounted.

Database opened.

SQL> select name,open_mode,cdb from v$database;

NAME OPEN_MODE CDB

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

TEST READ ONLY NO

2.2 生成xml格式的数据库描述文件

SQL> BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/test.xml');

END;

/ 2 3 4

PL/SQL procedure successfully completed.

2.3 关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2.4 连接CDB并创建PDB (这里需要有cdb数据库的，如果没有cdb数据库，那么就需要dbca建立一个cdb数据库)

bishuo:/home/oracle@oracle>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 21 14:02:06 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> select name,open_mode,cdb from v$database;

NAME OPEN_MODE CDB

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

BISHUO READ WRITE YES

创建PDB:

SQL> select con_id,dbid,name,open_mode from v$pdbs;

CON_ID DBID NAME OPEN_MODE

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

2 1534143422 PDB$SEED READ ONLY

SQL> CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml'

COPY

FILE_NAME_CONVERT = ('/home/oracle/oradata/12ctest/TEST/', '//home/oracle/oradata/bishuo/test/'); 2 3

Pluggable database created.==========================================================================这里可以直接设置一个参数

alter system set db_create_file_dest='/u01/app/oracle/oradata/';

用如下简单的方法直接进行升级

create pluggable database orcl using '/u02/noncdb/orcl.xml' copy;

===========================================================================

数据库复制成功：

test:/home/oracle/oradata/bishuo/test@oracle>pwd

/home/oracle/oradata/bishuo/test

test:/home/oracle/oradata/bishuo/test@oracle>ls

sysaux01.dbf  system01.dbf  temp01.dbf  users01.dbf

2.5 切换到PDB并执行脚本

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TEST MOUNTED

SQL> alter session set container=test;

Session altered.

--执行脚本：

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

这个脚本时间有点长，大概执行20分钟左右

2.6 启动PDB并检查状态

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 TEST MOUNTED

SQL> show con_name

CON_NAME

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

TEST

SQL> alter pluggable database test open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

3 TEST READ WRITE NO

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

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

TEST READ WRITE

1 row selected.

这是之前插入的测试数据：

SQL> select * from test;

ID NAME

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

1 shiyu

1 row selected.