Oracle 12c Non CDB 数据库切换成 CDB 测试

小咪咪 2022-06-05 07:23 230阅读 0赞

如果把数据库从11g 升级到12c，或者在12c中创建的，就是NON CDB，那么这样的数据库就是普通的单实例，和12c 之前的数据库没有区别，但12c 的特点就是CDB 管理，所以既然上12c，还是要切换成CDB 进行管理。  
  
所以下面的测试步骤，就是把NON CDB 切换成CDB的步骤。

1.当前环境说明

当前数据库里已经启动了2个实例： bishuo和 test。

bishuo:/home/oracle@oracle>ps -ef |grep pmon
    oracle     3666      1  0 13:25 ?        00:00:00 ora_pmon_bishuo
    oracle     4507      1  0 13:49 ?        00:00:00 ora_pmon_test
    oracle     4693 189245  0 13:49 pts/1    00:00:00 grep pmon

其中bishuo数据库是12C的CDB数据库：

SQL> select name,CDB from v$database;
    
    NAME	  CDB
    --------- ---
    BISHUO	  YES

test数据库是12C的non cdb数据库

SQL> select name,CDB from v$database;
    
    NAME	  CDB
    --------- ---
    TEST	  NO
    
    SQL> set lin 130;
    SQL> select * from v$version;
    
    BANNER                                             CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production          0
    PL/SQL Release 12.1.0.2.0 - Production                              0
    CORE    12.1.0.2.0    Production                              0
    TNS for Linux: Version 12.1.0.2.0 - Production                          0
    NLSRTL Version 12.1.0.2.0 - Production                              0

2.开始切换  
2.1 Cleanly shutdown 数据库并用read only 打开

SQL> select name,cdb from v$database;
    
    NAME	  CDB
    --------- ---
    TEST	  NO
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    SQL> startup open read only;
    ORACLE instance started.
    
    Total System Global Area 6442450944 bytes
    Fixed Size		    4511656 bytes
    Variable Size		 1124075608 bytes
    Database Buffers	 5301600256 bytes
    Redo Buffers		   12263424 bytes
    Database mounted.
    Database opened.
    
    SQL> select name,open_mode,cdb from v$database;
    
    NAME	  OPEN_MODE	       CDB
    --------- -------------------- ---
    TEST	  READ ONLY	       NO

2.2 生成xml格式的数据库描述文件

SQL> BEGIN
      DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/test.xml');
    END;
    /  2    3    4  
    
    PL/SQL procedure successfully completed.

2.3 关闭数据库

SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

2.4 连接CDB并创建PDB

bishuo:/home/oracle@oracle>sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 21 14:02:06 2017
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> select name,open_mode,cdb from v$database;
    
    NAME	  OPEN_MODE	       CDB
    --------- -------------------- ---
    BISHUO	  READ WRITE	       YES
    
    
    创建PDB:
    
    SQL> select con_id,dbid,name,open_mode from v$pdbs;
    
        CON_ID	 DBID NAME			     OPEN_MODE
    ---------- ---------- ------------------------------ ----------
    	 2 1534143422 PDB$SEED			     READ ONLY
    
    SQL> CREATE PLUGGABLE DATABASE test USING '/tmp/test.xml'
    COPY
    FILE_NAME_CONVERT = ('/home/oracle/oradata/12ctest/TEST/', '//home/oracle/oradata/bishuo/test/');  2    3  
    
    Pluggable database created.
    数据库复制成功：
    test:/home/oracle/oradata/bishuo/test@oracle>pwd
    /home/oracle/oradata/bishuo/test
    test:/home/oracle/oradata/bishuo/test@oracle>ls
    sysaux01.dbf  system01.dbf  temp01.dbf  users01.dbf

2.5 切换到PDB并执行脚本

SQL> show pdbs
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 2 PDB$SEED			  READ ONLY  NO
    	 3 TEST 			  MOUNTED
    SQL> alter session set container=test;
    Session altered.
    
    --执行脚本：
    sql> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
    
    这个脚本时间有点长，大概执行20分钟左右

2.6 启动PDB并检查状态

SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             3 TEST                           MOUNTED
    SQL> show con_name 
    
    CON_NAME
    ------------------------------
    TEST
    SQL> alter pluggable database test open;
    
    Pluggable database altered.
    
    SQL> show pdbs
    
        CON_ID CON_NAME                       OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
             3 TEST                           READ WRITE NO
    SQL> SELECT name, open_mode FROM v$pdbs;
    
    NAME                           OPEN_MODE
    ------------------------------ ----------
    TEST                           READ WRITE
    
    1 row selected.
    
    这是之前插入的测试数据：
    SQL> select * from test;
    
            ID NAME
    ---------- ----------
             1 shiyu
    
    1 row selected.