探索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 日志。
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NO 4 WOO_ORA11G MOUNTED 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 4 4199532651 WOO_ORA11G MOUNTED 第一次no-cdb plug cdb是mount状态,须要将其open; SQL> alter pluggable database open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTED 4 WOO_ORA11G READ WRITE YES
插入完毕之后须要运行noncdb_to_pdb脚本,修复原non-cdb 和新的pdb不兼容的问题:
十、运行noncdb_pdb脚本SQL> alter session set container=WOO_ORA11G; Session altered. SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ……… #### 遇到warning,脚本会自己主动忽略错误,继续运行。在最后脚本编译的时候会修复这些问题。
更新完之后须要同步pdb信息 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 WOO_ORA11G MOUNTED SQL> alter pluggable database open restricted; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 WOO_ORA11G READ WRITE YES SQL> exec dbms_pdb.sync_pdb(); PL/SQL procedure successfully completed. SQL> alter pluggable database close immediate; Pluggable database altered. SQL> alter pluggable database open; Pluggable database altered.
十一、至此no-cdb plug to cdb 成功
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTED 4 WOO_ORA11G READ WRITE NOSQL>
本文转自mfrbuaa博客园博客,原文链接:http://www.cnblogs.com/mfrbuaa/p/5097980.html,如需转载请自行联系原作者