本文讨论pdb之间的几种转换:
(1)从see pdb中创建pdb1
(2)在cdb内(ora12c为cdb)将pdb1克隆至pdb2.
(3)将non-cdb(名字叫noncdb),转换为pdb3,插入到cdb中。
(4) pdb改名
(1)从see pdb中创建pdb1
sys@ORA12C(172.31.8.145)> show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)> select con_id,dbid,name from v$pdbs;
CON_ID DBID NAME
---------- ---------- ------------------------------
2 4039091088 PDB$SEED
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
--先创建目录
E:ora12capporacleuseroradataora12c>cd %ORACLE_BASE%oradataora12c
E:ora12capporacleuseroradataora12c>mkdir pdb1
E:ora12capporacleuseroradataora12c>ls -l
total 6379699
-rwxrwxrwa 1 Administrators SYSTEM 17874944 Dec 4 21:10 CONTROL01.CTL
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO01.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO02.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO03.LOG
-rwxrwxrwa 1 Administrators SYSTEM 933240832 Dec 4 18:17 SYSAUX01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 828383232 Dec 4 18:17 SYSTEM01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 553656320 Dec 4 17:14 TEMP01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 770711552 Dec 4 18:17 UNDOTBS01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 5251072 Dec 4 18:17 USERS01.DBF
drwxrwxrwx 1 Administrators None 0 Dec 4 21:12 pdb1
drwxrwxrwx 1 Administrators None 0 Oct 12 23:24 pdbseed
E:ora12capporacleuseroradataora12c>
--开始创建
sys@ORA12C[CDB](172.31.8.145)> create pluggable database pdb1 admin user pdb1_admin
2 identified by oracle roles=(CONNECT)
3 file_name_convert=('E:ora12capporacleuseroradataora12cpdbseed','E:ora12capporacleuseroradataora12cpdb1')
4 /
Pluggable database created.
Elapsed: 00:00:40.26
sys@ORA12C[CDB](172.31.8.145)> select con_id, NAME, OPEN_MODE,DBID, CON_UID from V$PDBS;
CON_ID NAME OPEN_MODE DBID CON_UID
---------- ------------------------------ ---------- ---------- ----------
2 PDB$SEED READ ONLY 4039091088 4039091088
3 PDB1 MOUNTED 3308829374 3308829374
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 open restricted;
Pluggable database altered.
Elapsed: 00:00:05.10
sys@ORA12C[CDB](172.31.8.145)>
--根据listener的status,
E:ora12capporacleuseroradataora12c>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.0.2 - Beta on 04-DEC-2012 20:29:53
Copyright (c) 1991, 2012, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.0.2 - Beta
Start Date 04-DEC-2012 19:50:46
Uptime 0 days 0 hr. 39 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:ora12capporacleuserproduct12.1.0dbhome_1networkadminlistener.ora
Listener Log File E:ora12capporacleuserdiagtnslsnrHE-PClisteneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HE-PC.cn.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HE-PC.cn.oracle.com)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ora12c.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ora12cXDB.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb1.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully
E:ora12capporacleuseroradataora12c>
--配置tnsnames.ora 如下:
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1.oracleblog.org)
)
)
--以sys登录pdb1
E:ora12capporacleuseroradataora12c>sqlplus sys/oracle@pdb1 as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Dec 4 20:30:45 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
sys@PDB1[PDB](172.31.8.145)> exec dbms_pdb.sync_pdb();
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
sys@PDB1[PDB](172.31.8.145)> alter pluggable database pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.49
sys@PDB1[PDB](172.31.8.145)> alter pluggable database pdb1 open;
Pluggable database altered.
Elapsed: 00:00:03.97
sys@PDB1[PDB](172.31.8.145)>
CON_NAME
------------------------------
CDB$ROOT
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)> select con_id,dbid,name from v$pdbs;
CON_ID DBID NAME
---------- ---------- ------------------------------
2 4039091088 PDB$SEED
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
--先创建目录
E:ora12capporacleuseroradataora12c>cd %ORACLE_BASE%oradataora12c
E:ora12capporacleuseroradataora12c>mkdir pdb1
E:ora12capporacleuseroradataora12c>ls -l
total 6379699
-rwxrwxrwa 1 Administrators SYSTEM 17874944 Dec 4 21:10 CONTROL01.CTL
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO01.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO02.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO03.LOG
-rwxrwxrwa 1 Administrators SYSTEM 933240832 Dec 4 18:17 SYSAUX01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 828383232 Dec 4 18:17 SYSTEM01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 553656320 Dec 4 17:14 TEMP01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 770711552 Dec 4 18:17 UNDOTBS01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 5251072 Dec 4 18:17 USERS01.DBF
drwxrwxrwx 1 Administrators None 0 Dec 4 21:12 pdb1
drwxrwxrwx 1 Administrators None 0 Oct 12 23:24 pdbseed
E:ora12capporacleuseroradataora12c>
--开始创建
sys@ORA12C[CDB](172.31.8.145)> create pluggable database pdb1 admin user pdb1_admin
2 identified by oracle roles=(CONNECT)
3 file_name_convert=('E:ora12capporacleuseroradataora12cpdbseed','E:ora12capporacleuseroradataora12cpdb1')
4 /
Pluggable database created.
Elapsed: 00:00:40.26
sys@ORA12C[CDB](172.31.8.145)> select con_id, NAME, OPEN_MODE,DBID, CON_UID from V$PDBS;
CON_ID NAME OPEN_MODE DBID CON_UID
---------- ------------------------------ ---------- ---------- ----------
2 PDB$SEED READ ONLY 4039091088 4039091088
3 PDB1 MOUNTED 3308829374 3308829374
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 open restricted;
Pluggable database altered.
Elapsed: 00:00:05.10
sys@ORA12C[CDB](172.31.8.145)>
--根据listener的status,
E:ora12capporacleuseroradataora12c>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.0.2 - Beta on 04-DEC-2012 20:29:53
Copyright (c) 1991, 2012, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.0.2 - Beta
Start Date 04-DEC-2012 19:50:46
Uptime 0 days 0 hr. 39 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:ora12capporacleuserproduct12.1.0dbhome_1networkadminlistener.ora
Listener Log File E:ora12capporacleuserdiagtnslsnrHE-PClisteneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HE-PC.cn.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HE-PC.cn.oracle.com)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ora12c.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "ora12cXDB.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb1.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully
E:ora12capporacleuseroradataora12c>
--配置tnsnames.ora 如下:
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb1.oracleblog.org)
)
)
--以sys登录pdb1
E:ora12capporacleuseroradataora12c>sqlplus sys/oracle@pdb1 as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Dec 4 20:30:45 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
sys@PDB1[PDB](172.31.8.145)> exec dbms_pdb.sync_pdb();
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26
sys@PDB1[PDB](172.31.8.145)> alter pluggable database pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.49
sys@PDB1[PDB](172.31.8.145)> alter pluggable database pdb1 open;
Pluggable database altered.
Elapsed: 00:00:03.97
sys@PDB1[PDB](172.31.8.145)>
(2)在cdb内(ora12c为cdb)将pdb1克隆至pdb2.
--先创建目录
E:ora12capporacleuseroradataora12c>cd %ORACLE_BASE%oradataora12c
E:ora12capporacleuseroradataora12c>mkdir pdb2
E:ora12capporacleuseroradataora12c>ls -l
total 6379699
-rwxrwxrwa 1 Administrators SYSTEM 17874944 Dec 4 21:36 CONTROL01.CTL
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO01.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO02.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO03.LOG
-rwxrwxrwa 1 Administrators SYSTEM 933240832 Dec 4 21:31 SYSAUX01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 828383232 Dec 4 18:17 SYSTEM01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 553656320 Dec 4 21:31 TEMP01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 770711552 Dec 4 18:17 UNDOTBS01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 5251072 Dec 4 18:17 USERS01.DBF
drwxrwxrwx 1 Administrators None 0 Dec 4 21:16 pdb1
drwxrwxrwx 1 Administrators None 0 Dec 4 21:36 pdb2
drwxrwxrwx 1 Administrators None 0 Oct 12 23:24 pdbseed
E:ora12capporacleuseroradataora12c>
--将源库(pdb1)置于read only模式
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.52
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 open read only;
Pluggable database altered.
Elapsed: 00:00:03.19
sys@ORA12C[CDB](172.31.8.145)>
--克隆
sys@ORA12C(172.31.8.145)> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
2 file_name_convert=('E:ora12capporacleuseroradataora12cpdb1','E:ora12capporacleuseroradataora12cpdb2');
Pluggable database created.
Elapsed: 00:00:36.75
sys@ORA12C(172.31.8.145)>
--后续对pdb2的操作和之前第一个例子类似。建tnsnames,通过tnanmes连接,开启到restricted模式,dbms_pdb.sync_pdb同步,打开pdb2
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb2 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.60
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb2 open restricted;
Pluggable database altered.
Elapsed: 00:00:06.41
sys@ORA12C[CDB](172.31.8.145)> conn sys/oracle@pdb2 as sysdba
Connected.
sys@PDB2[PDB](172.31.8.145)> exec dbms_pdb.sync_pdb;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
sys@PDB2[PDB](172.31.8.145)> alter database close immediate;
alter database close immediate
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
Elapsed: 00:00:00.04
sys@PDB2[PDB](172.31.8.145)> shutdown immediate;
Pluggable Database closed.
sys@PDB2[PDB](172.31.8.145)> startup
Pluggable Database opened.
sys@PDB2[PDB](172.31.8.145)> conn /as sysdba
Connected.
sys@ORA12C[CDB](172.31.8.145)> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4039091088 PDB$SEED READ ONLY
3 3308829374 PDB1 READ ONLY
4 3867361877 PDB2 READ WRITE
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
--恢复pdb1的read write:
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.17
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 open;
Pluggable database altered.
Elapsed: 00:00:06.04
sys@ORA12C[CDB](172.31.8.145)> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4039091088 PDB$SEED READ ONLY
3 3308829374 PDB1 READ WRITE
4 3867361877 PDB2 READ WRITE
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
E:ora12capporacleuseroradataora12c>cd %ORACLE_BASE%oradataora12c
E:ora12capporacleuseroradataora12c>mkdir pdb2
E:ora12capporacleuseroradataora12c>ls -l
total 6379699
-rwxrwxrwa 1 Administrators SYSTEM 17874944 Dec 4 21:36 CONTROL01.CTL
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO01.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO02.LOG
-rwxrwxrwa 1 Administrators SYSTEM 52429312 Dec 4 18:17 REDO03.LOG
-rwxrwxrwa 1 Administrators SYSTEM 933240832 Dec 4 21:31 SYSAUX01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 828383232 Dec 4 18:17 SYSTEM01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 553656320 Dec 4 21:31 TEMP01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 770711552 Dec 4 18:17 UNDOTBS01.DBF
-rwxrwxrwa 1 Administrators SYSTEM 5251072 Dec 4 18:17 USERS01.DBF
drwxrwxrwx 1 Administrators None 0 Dec 4 21:16 pdb1
drwxrwxrwx 1 Administrators None 0 Dec 4 21:36 pdb2
drwxrwxrwx 1 Administrators None 0 Oct 12 23:24 pdbseed
E:ora12capporacleuseroradataora12c>
--将源库(pdb1)置于read only模式
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.52
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 open read only;
Pluggable database altered.
Elapsed: 00:00:03.19
sys@ORA12C[CDB](172.31.8.145)>
--克隆
sys@ORA12C(172.31.8.145)> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
2 file_name_convert=('E:ora12capporacleuseroradataora12cpdb1','E:ora12capporacleuseroradataora12cpdb2');
Pluggable database created.
Elapsed: 00:00:36.75
sys@ORA12C(172.31.8.145)>
--后续对pdb2的操作和之前第一个例子类似。建tnsnames,通过tnanmes连接,开启到restricted模式,dbms_pdb.sync_pdb同步,打开pdb2
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb2 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.60
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb2 open restricted;
Pluggable database altered.
Elapsed: 00:00:06.41
sys@ORA12C[CDB](172.31.8.145)> conn sys/oracle@pdb2 as sysdba
Connected.
sys@PDB2[PDB](172.31.8.145)> exec dbms_pdb.sync_pdb;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
sys@PDB2[PDB](172.31.8.145)> alter database close immediate;
alter database close immediate
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
Elapsed: 00:00:00.04
sys@PDB2[PDB](172.31.8.145)> shutdown immediate;
Pluggable Database closed.
sys@PDB2[PDB](172.31.8.145)> startup
Pluggable Database opened.
sys@PDB2[PDB](172.31.8.145)> conn /as sysdba
Connected.
sys@ORA12C[CDB](172.31.8.145)> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4039091088 PDB$SEED READ ONLY
3 3308829374 PDB1 READ ONLY
4 3867361877 PDB2 READ WRITE
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
sys@ORA12C[CDB](172.31.8.145)>
--恢复pdb1的read write:
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 close immediate;
Pluggable database altered.
Elapsed: 00:00:00.17
sys@ORA12C[CDB](172.31.8.145)> alter pluggable database pdb1 open;
Pluggable database altered.
Elapsed: 00:00:06.04
sys@ORA12C[CDB](172.31.8.145)> select con_id,dbid,name,open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4039091088 PDB$SEED READ ONLY
3 3308829374 PDB1 READ WRITE
4 3867361877 PDB2 READ WRITE
Elapsed: 00:00:00.00
sys@ORA12C[CDB](172.31.8.145)>
(3)将non-cdb(名字叫noncdb),转换为pdb3,插入到cdb中。
--将non cdb置于read only模式,生产xml文件
sys@NONCDB[NON-CDB](10.191.10.214)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@NONCDB[NON-CDB](10.191.10.214)>
sys@NONCDB[NON-CDB](10.191.10.214)>
sys@NONCDB[NON-CDB](10.191.10.214)> startup mount
ORACLE instance started.
Total System Global Area 2488635392 bytes
Fixed Size 2384896 bytes
Variable Size 553651200 bytes
Database Buffers 1912602624 bytes
Redo Buffers 19996672 bytes
Database mounted.
sys@NONCDB[NON-CDB](10.191.10.214)>
sys@NONCDB[NON-CDB](10.191.10.214)> alter database open read only;
Database altered.
Elapsed: 00:00:13.12
sys@NONCDB[NON-CDB](10.191.10.214)> exec dbms_pdb.describe ('xmlorcl2')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.23
sys@NONCDB[NON-CDB](10.191.10.214)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@NONCDB[NON-CDB](10.191.10.214)>
--将noncdb导入到作为pdb导入到cdb中
--注意要删除原non cdb的temp file,不然报错:
sys@ORA12C[CDB](10.191.10.214)> create pluggable database PDB_ORCL2 using 'xmlorcl2'
2 NOCOPY;
create pluggable database PDB_ORCL2 using 'xmlorcl2'
*
ERROR at line 1:
ORA-01119: error in creating database file
'E:ORA12CAPPORACLEUSERORADATANONCDBDATAFILEO1_MF_TEMP_8CR8PYFD_.TMP'
ORA-27038: created file already exists
OSD-04010: ??? <create> ??, ???????
Elapsed: 00:00:01.43
sys@ORA12C[CDB](10.191.10.214)> exit
--删除
sys@ORA12C[CDB](10.191.10.214)> host rm E:ORA12CAPPORACLEUSERORADATANONCDBDATAFILEO1_MF_TEMP_8CR8PYFD_.TMP
--再次导入
sys@ORA12C[CDB](10.191.10.214)> create pluggable database PDB_ORCL2 using 'xmlorcl2' nocopy;
Pluggable database created.
Elapsed: 00:00:13.62
sys@ORA12C[CDB](10.191.10.214)>
--创建tnsnames
pdb_orcl2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl2.oracleblog.org)
)
)
--登录pdb,修改其数据字典
E:My DropboxPrivateiDocOracle workOU document12c new feature>sqlplus sys/oracle@pdb_orcl2 as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Dec 11 00:15:55 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
>
>
>
> @?/rdbms/admin/noncdb_to_pdb
BEGIN :t0 := DBMS_Utility.Get_Time(); END;
*
ERROR at line 1:
ORA-06550: line 1, column 14:
PLS-00201: identifier 'DBMS_UTILITY.GET_TIME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
begin
*
ERROR at line 1:
ORA-65020: Pluggable database already closed
ORA-06512: at line 3
Elapsed: 00:00:00.05
Elapsed: 00:00:00.00
Elapsed: 00:00:00.15
Elapsed: 00:00:00.13
Elapsed: 00:00:00.13
Elapsed: 00:00:08.28
Elapsed: 00:00:00.03
Elapsed: 00:00:00.04
Elapsed: 00:00:00.00
Elapsed: 00:00:00.25
Elapsed: 00:00:00.00
Elapsed: 00:00:05.07
Elapsed: 00:00:00.60
Elapsed: 00:00:00.11
Elapsed: 00:00:00.08
Elapsed: 00:00:05.70
Elapsed: 00:00:00.12
Elapsed: 00:00:00.11
Elapsed: 00:00:06.12
Elapsed: 00:00:00.10
Elapsed: 00:00:00.00
Elapsed: 00:00:00.07
Elapsed: 00:00:00.00
Elapsed: 00:00:00.14
Elapsed: 00:00:00.12
Elapsed: 00:00:00.14
Elapsed: 00:00:00.59
Elapsed: 00:00:00.04
Elapsed: 00:00:00.02
……
--同步pdb
E:My DropboxPrivateiDocOracle workOU document12c new feature>sqlplus sys/oracle@localhost:1521/PDB_ORCL2.oracleblog.org as SYSDBA
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Dec 11 08:37:51 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.62
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open restricted;
Pluggable database altered.
Elapsed: 00:00:05.50
sys@PDB_ORCL2[PDB](192.168.1.37)> exec DBMS_PDB.SYNC_PDB()
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.28
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.51
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open;
Pluggable database altered.
Elapsed: 00:00:05.50
sys@PDB_ORCL2[PDB](192.168.1.37)>
sys@NONCDB[NON-CDB](10.191.10.214)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@NONCDB[NON-CDB](10.191.10.214)>
sys@NONCDB[NON-CDB](10.191.10.214)>
sys@NONCDB[NON-CDB](10.191.10.214)> startup mount
ORACLE instance started.
Total System Global Area 2488635392 bytes
Fixed Size 2384896 bytes
Variable Size 553651200 bytes
Database Buffers 1912602624 bytes
Redo Buffers 19996672 bytes
Database mounted.
sys@NONCDB[NON-CDB](10.191.10.214)>
sys@NONCDB[NON-CDB](10.191.10.214)> alter database open read only;
Database altered.
Elapsed: 00:00:13.12
sys@NONCDB[NON-CDB](10.191.10.214)> exec dbms_pdb.describe ('xmlorcl2')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.23
sys@NONCDB[NON-CDB](10.191.10.214)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@NONCDB[NON-CDB](10.191.10.214)>
--将noncdb导入到作为pdb导入到cdb中
--注意要删除原non cdb的temp file,不然报错:
sys@ORA12C[CDB](10.191.10.214)> create pluggable database PDB_ORCL2 using 'xmlorcl2'
2 NOCOPY;
create pluggable database PDB_ORCL2 using 'xmlorcl2'
*
ERROR at line 1:
ORA-01119: error in creating database file
'E:ORA12CAPPORACLEUSERORADATANONCDBDATAFILEO1_MF_TEMP_8CR8PYFD_.TMP'
ORA-27038: created file already exists
OSD-04010: ??? <create> ??, ???????
Elapsed: 00:00:01.43
sys@ORA12C[CDB](10.191.10.214)> exit
--删除
sys@ORA12C[CDB](10.191.10.214)> host rm E:ORA12CAPPORACLEUSERORADATANONCDBDATAFILEO1_MF_TEMP_8CR8PYFD_.TMP
--再次导入
sys@ORA12C[CDB](10.191.10.214)> create pluggable database PDB_ORCL2 using 'xmlorcl2' nocopy;
Pluggable database created.
Elapsed: 00:00:13.62
sys@ORA12C[CDB](10.191.10.214)>
--创建tnsnames
pdb_orcl2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl2.oracleblog.org)
)
)
--登录pdb,修改其数据字典
E:My DropboxPrivateiDocOracle workOU document12c new feature>sqlplus sys/oracle@pdb_orcl2 as sysdba
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Dec 11 00:15:55 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
>
>
>
> @?/rdbms/admin/noncdb_to_pdb
BEGIN :t0 := DBMS_Utility.Get_Time(); END;
*
ERROR at line 1:
ORA-06550: line 1, column 14:
PLS-00201: identifier 'DBMS_UTILITY.GET_TIME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Elapsed: 00:00:00.01
Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
begin
*
ERROR at line 1:
ORA-65020: Pluggable database already closed
ORA-06512: at line 3
Elapsed: 00:00:00.05
Elapsed: 00:00:00.00
Elapsed: 00:00:00.15
Elapsed: 00:00:00.13
Elapsed: 00:00:00.13
Elapsed: 00:00:08.28
Elapsed: 00:00:00.03
Elapsed: 00:00:00.04
Elapsed: 00:00:00.00
Elapsed: 00:00:00.25
Elapsed: 00:00:00.00
Elapsed: 00:00:05.07
Elapsed: 00:00:00.60
Elapsed: 00:00:00.11
Elapsed: 00:00:00.08
Elapsed: 00:00:05.70
Elapsed: 00:00:00.12
Elapsed: 00:00:00.11
Elapsed: 00:00:06.12
Elapsed: 00:00:00.10
Elapsed: 00:00:00.00
Elapsed: 00:00:00.07
Elapsed: 00:00:00.00
Elapsed: 00:00:00.14
Elapsed: 00:00:00.12
Elapsed: 00:00:00.14
Elapsed: 00:00:00.59
Elapsed: 00:00:00.04
Elapsed: 00:00:00.02
……
--同步pdb
E:My DropboxPrivateiDocOracle workOU document12c new feature>sqlplus sys/oracle@localhost:1521/PDB_ORCL2.oracleblog.org as SYSDBA
SQL*Plus: Release 12.1.0.0.2 Beta on Tue Dec 11 08:37:51 2012
Copyright (c) 1982, 2012, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.62
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open restricted;
Pluggable database altered.
Elapsed: 00:00:05.50
sys@PDB_ORCL2[PDB](192.168.1.37)> exec DBMS_PDB.SYNC_PDB()
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.28
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.51
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open;
Pluggable database altered.
Elapsed: 00:00:05.50
sys@PDB_ORCL2[PDB](192.168.1.37)>
(4) pdb改名
--将PDB_ORCL2改名为pdb3,先看看原来的pdb name
sys@PDB_ORCL2[PDB](192.168.1.37)> show con_name
CON_NAME
------------------------------
PDB_ORCL2
--切换到restrict mode
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.53
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open restricted;
Pluggable database altered.
Elapsed: 00:00:05.49
sys@PDB_ORCL2[PDB](192.168.1.37)> select CON_ID, NAME, OPEN_MODE, RESTRICTED from v$pdbs;
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PDB_ORCL2 READ WRITE YES
Elapsed: 00:00:00.02
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database RENAME GLOBAL_NAME TO pdb3;
Pluggable database altered.
Elapsed: 00:00:08.12
sys@PDB_ORCL2[PDB](192.168.1.37)> select CON_ID, NAME, OPEN_MODE, RESTRICTED from v$pdbs;
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PDB3 READ WRITE YES
Elapsed: 00:00:00.00
--改名
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.53
--重启到open模式
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open;
Pluggable database altered.
Elapsed: 00:00:06.13
sys@PDB_ORCL2[PDB](192.168.1.37)> select CON_ID, NAME, OPEN_MODE, RESTRICTED from v$pdbs;
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PDB3 READ WRITE NO
Elapsed: 00:00:00.02
sys@PDB_ORCL2[PDB](192.168.1.37)>
--注意,其service也已经改成pdb3了,需要对应的将tnsnames也修改。
C:Usersjijihe>lsnrctl status
……
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb3.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully
pdb3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb3.oracleblog.org)
)
)
--最后,打扫战场,如果你是windows的系统,还需要用oradim去除服务
> select status from v$instance;
STATUS
------------
MOUNTED
Elapsed: 00:00:00.01
> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
E:ORA12CAPPORACLEUSERORADATANONCDBCONTROLFILEO1_MF_8CR8OX4F_.CTL
E:ORA12CAPPORACLEUSERFAST_RECOVERY_AREANONCDBCONTROLFILEO1_MF_8CR8OXBR_.CTL
Elapsed: 00:00:00.01
> shutdown abort
ORACLE instance shut down.
> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
E:My DropboxPrivateiDocOracle workOU document12c new feature>rm E:ORA12CAPPORACLEUSERORADATANONCDBCONTROLFILEO1_MF_8CR8OX4F_.CTL
E:My DropboxPrivateiDocOracle workOU document12c new feature>rm E:ORA12CAPPORACLEUSERFAST_RECOVERY_AREANONCDBCONTROLFILEO1_MF_8CR8OXBR_.CTL
E:My DropboxPrivateiDocOracle workOU document12c new feature>oradim -DELETE -SID noncdb
Unable to stop service, OS Error = 1062
Instance deleted.
E:My DropboxPrivateiDocOracle workOU document12c new feature>
sys@PDB_ORCL2[PDB](192.168.1.37)> show con_name
CON_NAME
------------------------------
PDB_ORCL2
--切换到restrict mode
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.53
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open restricted;
Pluggable database altered.
Elapsed: 00:00:05.49
sys@PDB_ORCL2[PDB](192.168.1.37)> select CON_ID, NAME, OPEN_MODE, RESTRICTED from v$pdbs;
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PDB_ORCL2 READ WRITE YES
Elapsed: 00:00:00.02
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database RENAME GLOBAL_NAME TO pdb3;
Pluggable database altered.
Elapsed: 00:00:08.12
sys@PDB_ORCL2[PDB](192.168.1.37)> select CON_ID, NAME, OPEN_MODE, RESTRICTED from v$pdbs;
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PDB3 READ WRITE YES
Elapsed: 00:00:00.00
--改名
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database close immediate;
Pluggable database altered.
Elapsed: 00:00:00.53
--重启到open模式
sys@PDB_ORCL2[PDB](192.168.1.37)> alter pluggable database open;
Pluggable database altered.
Elapsed: 00:00:06.13
sys@PDB_ORCL2[PDB](192.168.1.37)> select CON_ID, NAME, OPEN_MODE, RESTRICTED from v$pdbs;
CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
5 PDB3 READ WRITE NO
Elapsed: 00:00:00.02
sys@PDB_ORCL2[PDB](192.168.1.37)>
--注意,其service也已经改成pdb3了,需要对应的将tnsnames也修改。
C:Usersjijihe>lsnrctl status
……
Instance "ora12c", status READY, has 1 handler(s) for this service...
Service "pdb3.oracleblog.org" has 1 instance(s).
Instance "ora12c", status READY, has 1 handler(s) for this service...
The command completed successfully
pdb3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb3.oracleblog.org)
)
)
--最后,打扫战场,如果你是windows的系统,还需要用oradim去除服务
> select status from v$instance;
STATUS
------------
MOUNTED
Elapsed: 00:00:00.01
> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
E:ORA12CAPPORACLEUSERORADATANONCDBCONTROLFILEO1_MF_8CR8OX4F_.CTL
E:ORA12CAPPORACLEUSERFAST_RECOVERY_AREANONCDBCONTROLFILEO1_MF_8CR8OXBR_.CTL
Elapsed: 00:00:00.01
> shutdown abort
ORACLE instance shut down.
> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
E:My DropboxPrivateiDocOracle workOU document12c new feature>rm E:ORA12CAPPORACLEUSERORADATANONCDBCONTROLFILEO1_MF_8CR8OX4F_.CTL
E:My DropboxPrivateiDocOracle workOU document12c new feature>rm E:ORA12CAPPORACLEUSERFAST_RECOVERY_AREANONCDBCONTROLFILEO1_MF_8CR8OXBR_.CTL
E:My DropboxPrivateiDocOracle workOU document12c new feature>oradim -DELETE -SID noncdb
Unable to stop service, OS Error = 1062
Instance deleted.
E:My DropboxPrivateiDocOracle workOU document12c new feature>
网友评论已有0条评论, 我也要评论