convert nocdb to cdb using dbms_pdb
本文介绍将nocdb转换为cdb
环境介绍:
ORACLE_HOME: /u01/app/oracle/product/12.1.0
TNS Listener port: 1521
Container databases:
SID: cdbtest(cdb)
SID: cdb2(nocdb)
在scott用户下增加测试数据(进行迁移后的验证)
SYS@cdb2> startup
ORACLE instance started.
Total System Global Area 438423552 bytes
Fixed Size 2289304 bytes
Variable Size 255852904 bytes
Database Buffers 176160768 bytes
Redo Buffers 4120576 bytes
Database mounted.
Database opened.
SYS@cdb2> conn scott/tiger
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SYS@cdb2> alter user scott identified by tiger account unlock ;
SP2-0640: Not connected
SYS@cdb2> conn / as sysdba
Connected.
SYS@cdb2> alter user scott identified by tiger account unlock ;
User altered.
SYS@cdb2> conn scott/tiger
Connected.
SCOTT@cdb2> create table ziyoo (a int ,b varchar2(10)) ;
Table created.
SCOTT@cdb2> insert into ziyoo values (1,'1') ;
1 row created.
SCOTT@cdb2> commit ;
Commit complete.
设置cdb2为read only 模式
SCOTT@cdb2> conn / as sysdba
Connected.
SYS@cdb2> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@cdb2> startup mount exclusive ;
ORACLE instance started.
Total System Global Area 438423552 bytes
Fixed Size 2289304 bytes
Variable Size 255852904 bytes
Database Buffers 176160768 bytes
Redo Buffers 4120576 bytes
Database mounted.
SYS@cdb2> alter database open read only ;
Database altered.
生成 manifest file并关闭cdb2
SYS@cdb2> exec dbms_pdb.describe(pdb_descr_file=>'/u01/app/oracle/oradata/noncdb/noncdb1.xml');
PL/SQL procedure successfully completed.
SYS@cdb2> shutdown immediate ;
Database closed.
Database dismounted.
exit
ORACLE instance shut down.
SYS@cdb2> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
在cdbtest数据库创建pdb使用上面生成的 manifest file 文件
$export ORACLE_SID=cdbtest
$sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 15 21:43:00 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@cdbtest> ed
Wrote file afiedt.buf
create pluggable database exnoncdb1
as clone
file_name_convert=('/u02/oradata/cdb2/','/u02/oradata/noncdb1/')
copy
/
Pluggable database created.
SYS@cdbtest> alter pluggable database exnoncdb1 open ;
Warning: PDB altered with errors.
SYS@cdbtest> alter pluggable database exnoncdb1 close ;
Pluggable database altered.
SYS@cdbtest> alter pluggable database exnoncdb1 open ;
Warning: PDB altered with errors.
SYS@cdbtest> select name ,con_id from v$active_services order by 1 ;
NAME CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND 1
SYS$USERS 1
cdbtest.oracle.com 1
cdbtestXDB 1
exnoncdb.oracle.com 6
exnoncdb1.oracle.com 7
pdb1.oracle.com 3
pdb3_his.oracle.com 4
pdb56.oracle.com 5
9 rows selected.
SYS@cdbtest> select con_id,dbid,name ,open_mode from v$pdbs ;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4087438519 PDB$SEED READ ONLY
3 3352592044 PDB1 READ WRITE
4 3960920495 PDB3_HIS READ WRITE
5 3170747834 PDB56 READ WRITE
6 1921111138 EXNONCDB MOUNTED
7 4022373748 EXNONCDB1 READ WRITE
6 rows selected.
转换pdb data dictionary (可选,生产环境建议做)
$sqlplus sys/password@localhost:1521/exnoncdb1.oracle.com as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 15 22:15:40 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@localhost:1521/exnoncdb1.oracle.com> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
输出过程省略,执行需要较长时间。。。。
scoot用户验证数据:
SYS@localhost:1521/exnoncdb1.oracle.com> conn scott/tiger@localhost:1521/exnoncdb1.oracle.com
Connected.
SCOTT@localhost:1521/exnoncdb1.oracle.com> select * from tab ;
TNAME
--------------------------------------------------------------------------------
TABTYPE CLUSTERID
------- ----------
DEPT
TABLE
EMP
TABLE
BONUS
TABLE
SALGRADE
TABLE
ZIYOO
TABLE
5 rows selected.
SCOTT@localhost:1521/exnoncdb1.oracle.com> select * from ziyoo ;
A B
---------- ----------
1 1
1 row selected.
重置环境
SCOTT@localhost:1521/exnoncdb1.oracle.com> conn / as sysdba
Connected.
SYS@cdbtest> alter pluggable database exnoncdb close immediate ;
Pluggable database altered.
SYS@cdbtest> drop pluggable database exnoncdb ;
Pluggable database dropped.
SYS@cdbtest> alter pluggable database exnoncdb1 close immediate ;
Pluggable database altered.
SYS@cdbtest> drop pluggable database exnoncdb1 including datafiles ;
Pluggable database dropped.
SYS@cdbtest> select name ,open_mode from v$pdbs ;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB3_HIS READ WRITE
PDB56 READ WRITE
4 rows selected.