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