聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

pdb之间的相互转换

2013-06-26 23:01 浏览: 1702467 次 我要评论(0 条) 字号:

本文讨论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)>
 
--根据
listenerstatus
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, OracleAll 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, OracleAll 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)>
 
--恢复
pdb1read 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 cdbtemp 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, OracleAll 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, OracleAll 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>


网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复