Chinaunix首页 | 论坛 | 博客

情系天下的ChinaUnix博客

首页　| 　博文目录　| 　关于我

qxtx2012

  • 博客访问： 25225
  • 博文数量： 18
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 180
  • 用 户 组： 普通用户
  • 注册时间： 2015-11-02 14:00
文章分类

全部博文（18）

文章存档

2016年（4）

2015年（14）

我的朋友
最近访客
推荐博文
相关博文
NONCDB_TO_PDB

分类： Oracle

2015-11-04 17:15:52

将non-cdb库转换为pdb库实验

一、环境说明

NON-CDB库 ： test 
CDB库         ： cdbtest
操作系统     ： red hat linux 6.5 
数据库版本  :   oracle 12.1.0.2

二、转换步骤

2.1  以sysdba用户使用sqlplus连接数据库

点击(此处)折叠或打开
  1. [oracle@test ~]$ export ORACLE_SID=test
  2. [oracle@test ~]$ ps -ef|grep pmon
  3. oracle 23464 1 0 16:36 ? 00:00:01 ora_pmon_cdbtest
  4. oracle 27360 1 0 19:20 ? 00:00:00 ora_pmon_test
  5. oracle 29194 29156 0 19:40 pts/2 00:00:00 grep pmon
  6. [oracle@test ~]$ export ORACLE_SID=test
  7. [oracle@test ~]$ sqlplus / as sysdba

  9. SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 3 19:40:44 2015

  11. Copyright (c) 1982, 2014, Oracle. All rights reserved.


  14. Connected to:
  15. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  16. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  18. SQL> select name,cdb from v$database;

  20. NAME     CDB
  21. --------- ---
  22. TEST     NO

2.2 关闭non-cdb库，并以open read only模式打开

点击(此处)折叠或打开
  1. SQL> shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SQL> startup mount
  6. ORACLE instance started.

  8. Total System Global Area 1073741824 bytes
  9. Fixed Size         2932632 bytes
  10. Variable Size         436207720 bytes
  11. Database Buffers     629145600 bytes
  12. Redo Buffers         5455872 bytes
  13. Database mounted.
  14. SQL> alter database open read only;

  16. Database altered.

2.3 生成转换成pdb库的xml文件

点击(此处)折叠或打开

  1. SQL> exec dbms_pdb.describe(pdb_descr_file=>'/soft/test.xml');

  3. PL/SQL procedure successfully completed.

2.4 关闭non-cdb库

点击(此处)折叠或打开

  1. SQL> shutdown immediate;
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.

2.5 连接到cdb容器库

点击(此处)折叠或打开

  1. [oracle@test ~]$ export ORACLE_SID=cdbtest
  2. [oracle@test ~]$ sqlplus / as sysdba

  4. SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 3 19:59:20 2015

  6. Copyright (c) 1982, 2014, Oracle. All rights reserved.


  9. Connected to:
  10. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  11. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  13. SQL> select name,cdb from v$database;

  15. NAME     CDB
  16. --------- ---
  17. CDBTEST YES
  18. SQL> show pdbs        
  19. CON_ID CON_NAME                       OPEN MODE  RESTRICTED
  20. ---------- ------------------------------ ---------- ----------
  21. 2 PDB$SEED                       READ ONLY  NO
  22. 3 PDB                            READ WRITE NO

2.6  使用生成好的xml文件创建pdb库

点击(此处)折叠或打开

  1. SQL> create pluggable database test using '/soft/test.xml' nocopy tempfile reuse;

  3. Pluggable database created.

  5. SQL> show pdbs

  7.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  8. ---------- ------------------------------ ---------- ----------
  9.      2 PDB$SEED             READ ONLY NO
  10.      3 PDB                 READ WRITE NO
  11.      4 TEST              MOUNTED

2.7 将新增加的pdb库test打开

点击(此处)折叠或打开

  1. SQL> alter pluggable database test open;

  3. Warning: PDB altered with errors.
此时pdb打开时会报一个警告。原因是没有执行noncdb_to_pdb.sql脚本，也可以查询PDB_PLUG_IN_VIOLATIONS视图有详细警告信息

点击(此处)折叠或打开

  1. SQL> set linesize 200
  2. SQL> col time for a35
  3. SQL> col name for a10
  4. SQL> col cause for a25
  5. SQL> select time,name,cause,status from PDB_PLUG_IN_VIOLATIONS;

  7. TIME                 NAME CAUSE             STATUS
  8. ----------------------------------- ---------- ------------------------- ---------
  9. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  10. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  11. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  12. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  13. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  14. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  15. 03-NOV-15 04.39.40.398903 PM     PDB Parameter         RESOLVED
  16. 03-NOV-15 04.44.23.213235 PM     PDB Database CHARACTER SET     RESOLVED
  17. 03-NOV-15 08.06.46.273088 PM     TEST Parameter         RESOLVED
  18. 03-NOV-15 08.06.46.273088 PM     TEST Service Name Conflict     RESOLVED
  19. 03-NOV-15 08.06.46.273088 PM     TEST Parameter         RESOLVED

  21. TIME                 NAME CAUSE             STATUS
  22. ----------------------------------- ---------- ------------------------- ---------
  23. 03-NOV-15 08.06.46.281358 PM     TEST Non-CDB to PDB         PENDING
  24. 03-NOV-15 08.06.46.440296 PM     TEST SQL Patch         PENDING
  26. 13 rows selected.

2.8  连接新转换的pdb库test实例 执行noncdb_to_pdb转换脚本

点击(此处)折叠或打开

  1. SQL> conn sys/oracle@192.168.2.240:1521/test as sysdba
  2. Connected.
  3. SQL> exit
  4. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  5. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  6. [oracle@test ~]$ sqlplus /nolog

  8. SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 3 20:33:55 2015

  10. Copyright (c) 1982, 2014, Oracle. All rights reserved.

  12. SQL> conn sys/oracle@192.168.2.240:1521/test as sysdba
  13. Connected.
  14. SQL> @?/rdbms/admin/noncdb_to_pdb.sql
这个脚本执行时间比较长，大约需30分钟左右。

2.9 启动pdb并检查状态

点击(此处)折叠或打开

  1. [oracle@test ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 3 21:00:51 2015

  5. Copyright (c) 1982, 2014, Oracle. All rights reserved.


  8. Connected to:
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  12. SQL> show pdbs

  14.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  15. ---------- ------------------------------ ---------- ----------
  16.      2 PDB$SEED             READ ONLY NO
  17.      3 PDB                 READ WRITE NO
  18.      4 TEST              READ WRITE YES
  19. SQL> alter pluggable database test close;

  21. Pluggable database altered.

  23. SQL> show pdbs

  25.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  26. ---------- ------------------------------ ---------- ----------
  27.      2 PDB$SEED             READ ONLY NO
  28.      3 PDB                 READ WRITE NO
  29.      4 TEST              MOUNTED
  30. SQL> alter pluggable database test open;

  32. Warning: PDB altered with errors.
至此test库已由non-cdb转换为pdb库，打开test库的时候有警告错误信息。

2.10 分析最后的警告信息，查看PDB_PLUG_IN_VIOLATIONS视图的错误信息

点击(此处)折叠或打开

  1. ***************************************************************
  2. WARNING: Pluggable Database TEST with pdb id - 4 is
  3.          altered with errors or warnings. Please look into
  4.          PDB_PLUG_IN_VIOLATIONS view for more details.
  5. ***************************************************************

点击(此处)折叠或打开

  1. SQL> select time,name,cause,message ,status from PDB_PLUG_IN_VIOLATIONS where status='PENDING';
  2. TIME                 NAME CAUSE         MESSAGE         STATUS
  3. ----------------------------------- ---------- -------------------- -------------------------------------------------- ----------
  4. 03-NOV-15 09.01.35.208293 PM     TEST SQL Patch     PSU bundle patch 5 (Database Patch Set Update : 12 PENDING
  5.                                  .1.0.2.5 (21359755)): Installed in the CDB but not
  6.                                  in the PDB.

2.11 升级psu补丁

点击(此处)折叠或打开

  1. [oracle@test OPatch]$ datapatch -verbose
  2. SQL Patching tool version 12.1.0.2.0 on Tue Nov 3 21:30:12 2015
  3. Copyright (c) 2015, Oracle. All rights reserved.

  5. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_31165_2015_11_03_21_30_12/sqlpatch_invocation.log

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

  15. Current state of SQL patches:
  16. Bundle series PSU:
  17.   ID 5 in the binary registry and ID 5 in PDB CDB$ROOT, ID 5 in PDB PDB$SEED, ID 5 in PDB PDB

  19. Adding patches to installation queue and performing prereq checks...
  20. Installation queue:
  21.   For the following PDBs: CDB$ROOT PDB$SEED PDB
  22.     Nothing to roll back
  23.     Nothing to apply
  24.   For the following PDBs: TEST
  25.     Nothing to roll back
  26.     The following patches will be applied:
  27.       21359755 (Database Patch Set Update : 12.1.0.2.5 (21359755))

  29. Installing patches...
  30. Patch installation complete. Total patches installed: 1

  32. Validating logfiles...
  33. Patch 21359755 apply (pdb TEST): SUCCESS
  34.   logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/21359755_apply_CDBTEST_TEST_2015Nov03_21_37_44.log (no errors)
  35. SQL Patching tool complete on Tue Nov 3 21:37:50 2015

2.12 将test库关闭并重新打开，确认是否还有警告信息

点击(此处)折叠或打开

  1. SQL> show pdbs

  3.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  4. ---------- ------------------------------ ---------- ----------
  5.      2 PDB$SEED             READ ONLY NO
  6.      3 PDB                 READ WRITE NO
  7.      4 TEST              READ WRITE YES
  8. SQL> alter pluggable database test close immediate;

  10. Pluggable database altered.

  12. SQL> alter pluggable database test open;

  14. Pluggable database altered.

  16. SQL> select status,action from PDB_PLUG_IN_VIOLATIONS;

  18. STATUS     ACTION
  19. --------- -------------------------------------------------------
  20. RESOLVED Please check the parameter in the current CDB
  21. RESOLVED Please check the parameter in the current CDB
  22. RESOLVED Please check the parameter in the current CDB
  23. RESOLVED Please check the parameter in the current CDB
  24. RESOLVED Please check the parameter in the current CDB
  25. RESOLVED Please check the parameter in the current CDB
  26. RESOLVED Please check the parameter in the current CDB
  27. RESOLVED Convert the character set of the PDB to match the CDB o
  28.      r plug the PDB in a CDB with compatible character set

  30. RESOLVED Please check the parameter in the current CDB

  32. STATUS     ACTION
  33. --------- -------------------------------------------------------
  34. RESOLVED Drop the service and recreate it with an appropriate na
  35.      me.

  37. RESOLVED Please check the parameter in the current CDB
  38. RESOLVED Run noncdb_to_pdb.sql.
  39. RESOLVED Call datapatch to install in the PDB or the CDB

  41. 13 rows selected.
从PDB_PLUG_IN_VIOLATIONS查询的结果，可以看出， Call datapatch to install in the PDB or the CDB 状态已变为 RESOLVED

至此noncdb-to--pdb已转换完毕









阅读(679) | 评论(0) | 转发(0) |
0

上一篇：没有了

下一篇：将一个pdb库从cdb容器库unplug

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6