oracle 19c 非 cdb模式转cdb模式数据库

oracle 19c 非 cdb模式转cdb模式数据库。其他版本的非CDB模式也可以参考步骤。

如有疑问，请留言，欢迎互相学些。

1、dbca安装CDB

$ dbca
1. Create a database
2. Advanced configuration
3. Database type: Oracle Single Instance database
   Select a template for your database : General Purpose or Transaction Processing
4. Global database name : MARSCDB
         SID : MARSCDB
         Tick : Create as Container database
         Tick : Use Local Undo tablespace for PDBs
         choose : Create as empty Container database
5. choose : Use following for the database storage attributes
   Database files storage type : File System
   Database files location : /u01/app/oradata/
   Tick : Use Oracle-Managed Files(OMF)
6. Tick : Specify Fast Recovery Area
   Recovery files storage type : File System
   Fast Recovery Area : /u01/app/oradbs
   Fast Recovery Area size : 20 GB
   Tick : Enable archiving
7. Next
8. Next
9. Memory : Use Automatic Shared Memory Management
                                                SGA size : 1102 MB
                                                PGA	size : 368 MB
         Sizing : Processes 320
         Charactor sets : choose from the list of charactor sets
         									Database charactor set : AL32UTF8 - Unicode UTF-8 Universal charactor set
         									National charactor set : AL16UTF16 - Unicode UTF-16 Universal charactor set
         									Default language :  American
         									Default territory : United States
         Connection mode : Dedicated server mode
10. No tick -> Next
11. Use the same administrative password for all accounts
12. Tick : Create database -> Next
13. Finish
14. Wait a time
15. Close

2、登录noncdb的数据库生成PDB的xml文件

export ORACLE_SID=noncdb
--   Set the ORACLE_SID variable to noncdb.
--   Connect to the noncdb instance.
sqlplus / as sysdba
--  If the instance is up, shut it down first.
shutdown immediate
--  Start up the database in mount exclusive mode.
startup mount exclusive
-- Open the database in read-only mode.
alter database open read only;
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/orcl_non_to_pdb.xml');
END;
/

3、检查xml文件

4、关闭noncdb数据库

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

5、连接CDB并转换PDB

[oracle@19ctarget ~]$ echo $ORACLE_SID
orclcdb
[oracle@19ctarget ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 19 17:40:08 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3103783744 bytes
Fixed Size		    9141056 bytes
Variable Size		  721420288 bytes
Database Buffers	 2365587456 bytes
Redo Buffers		    7634944 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb from v$database;

NAME	  OPEN_MODE	       CDB
--------- -------------------- ---
ORCLCDB   READ WRITE	       YES

6、创建PDB

6.1 创建目录(忽略)

mv /oradata /oradata.old.org
mkdir /oradata
chown oracle.oinstall /oradata
chmod 755 /oradata

6.2登录cdb

$ export ORACLE_SID=orclcdb
$ echo $ORACLE_SID
orclcdb

6.3 start create pdb

create pluggable database orcl using '/home/oracle/orcl_non_to_pdb.xml' copy;

SQL> create pluggable database orcl using '/home/oracle/orcl_non_to_pdb.xml' copy;
create pluggable database orcl using '/home/oracle/orcl_non_to_pdb.xml' copy
*
ERROR at line 1:
ORA-19505: failed to identify file "/oradata/orcl/system01.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

--solution
[root@19ctarget /]# rm -rf /oradata
[root@19ctarget /]# mv /oradata.old.org/ /oradata
[root@19ctarget /]# ls -ld /oradata
drwxr-xr-x 3 oracle oinstall 39 Jan  6 18:47 /oradata

SQL> create pluggable database orcl using '/home/oracle/orcl_non_to_pdb.xml' copy;

Pluggable database created.

SQL> 
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 ORCL 			  MOUNTED
SQL> 
SQL> alter session set container=ORCL;

Session altered.

7、nonpdb to pdb的脚本

[oracle@19ctarget ~]$ cd $ORACLE_HOME
[oracle@19ctarget db]$ cd rdbms/admin/
[oracle@19ctarget admin]$ ls -l noncdb_to_pdb.sql 
-rw-r--r-- 1 oracle oinstall 20630 Dec  8  2018 noncdb_to_pdb.sql

8、启动pdb

19:21:49 SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 ORCL                           MOUNTED
19:22:41 SQL> show con_name

CON_NAME
------------------------------
ORCL
19:23:08 SQL> alter pluggable database open;

Pluggable database altered.

Elapsed: 00:00:10.23
19:24:27 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 ORCL                           READ WRITE NO

DB已经成功的从NON CDB 切换到了PDB。

9、验证数据以及组件状态，编译失效对象

19:25:38 SQL> select count(*) from dba_invalid_objects;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.17

19:26:54 SQL> select  COMP_ID,VERSION, STATUS  from dba_registry ;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ ----------------------
CATALOG                        19.0.0.0.0                     VALID
CATPROC                        19.0.0.0.0                     VALID
JAVAVM                         19.0.0.0.0                     VALID
XML                            19.0.0.0.0                     VALID
CATJAVA                        19.0.0.0.0                     VALID
RAC                            19.0.0.0.0                     OPTION OFF
APS                            19.0.0.0.0                     VALID
OWM                            19.0.0.0.0                     VALID
CONTEXT                        19.0.0.0.0                     VALID
XDB                            19.0.0.0.0                     VALID
ORDIM                          19.0.0.0.0                     VALID
SDO                            19.0.0.0.0                     VALID
XOQ                            19.0.0.0.0                     VALID
APEX                           3.2.1.00.12                    VALID

14 rows selected.

10、查看补丁号

col DESCRIPTION for a50
set line 200
SELECT s.con_id, s.patch_id, s.patch_uid, s.description FROM cdb_registry_sqlpatch s order by 1,2;
SELECT s.con_id, s.patch_id, s.patch_uid, s.description FROM cdb_registry_sqlpatch s order by 1,2;

    CON_ID   PATCH_ID  PATCH_UID DESCRIPTION
---------- ---------- ---------- --------------------------------------------------------------------------------
	 1   29517242	22862832 Database Release Update : 19.3.0.0.190416 (29517242)
	 4   29517242	22862832 Database Release Update : 19.3.0.0.190416 (29517242)

11、监听配置

[oracle@19ctarget admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/19.3.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orclcdb)
    )
  )

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.113)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

[oracle@19ctarget admin]$ cat listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/19.3.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orclcdb)
      (ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db)
      (SID_NAME = orclcdb)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 19ctarget)(PORT = 1521))
  )

ADR_BASE_LISTENER = /oracle/app/oracle


[oracle@19ctarget admin]$ tnsping orclpdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-JAN-2021 21:24:06

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.4.113)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@19ctarget admin]$ sqlplus sys/Shiji#123@orclpdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 19 21:24:13 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 4 ORCL 			  READ WRITE NO
	

12、创建新的PDB

--specify datafile path
create pluggable database netdb3 admin user netdb3admin identified by oracle19c create_file_dest='/oradatapdb';
Or
alter session set db_create_file_dest='/oradatapdb';
create pluggable database netdb2 admin user pdbadmin identified by oracle19c; 
alter  pluggable database netdb2 open ; 

SQL> create pluggable database netdb admin user pdbadmin identified by oracle19c;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 NETDB			  MOUNTED
	 4 ORCL 			  READ WRITE NO
SQL> alter  pluggable database netdb open ; 

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 NETDB			  READ WRITE NO
	 4 ORCL 			  READ WRITE NO
SQL> 


alter pluggable database netdb save state;
col con_name for a30
select con_name, state from dba_pdb_saved_states;


SQL> alter pluggable database netdb save state;

Pluggable database altered.

SQL> col con_name for a30
select con_name, state from dba_pdb_saved_states;

SQL> 
CON_NAME		       STATE
------------------------------ --------------
NETDB			       OPEN