Database/Oracle
Non-CDB를 PDB로 변환 하기
동기(Sync)
2022. 11. 14. 09:00
반응형
사전 상태 확인
[oracle@ol7 ~]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 ~]$ env | grep SID
ORACLE_SID=TST
[oracle@ol7 ~]$
[oracle@ol7 ~]$ ps -ef | grep pmon
oracle 2648 1 0 10:27 ? 00:00:00 ora_pmon_CDBTST
oracle 3044 1 0 10:27 ? 00:00:00 ora_pmon_TST
oracle 3530 2589 0 10:29 pts/0 00:00:00 grep --color=auto pmon
[oracle@ol7 ~]$
[oracle@ol7 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 5 09:38:22 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> set linesize 150
SQL> col name for a20
SQL> SELECT dbid, name, db_unique_name, created, log_mode, open_mode, cdb, con_id FROM v$database;
DBID NAME DB_UNIQUE_NAME CREATED LOG_MODE OPEN_MODE CDB CON_ID
---------- --------- ------------------------------ --------- ------------ -------------------- --- ----------
2257768950 TST TST 28-SEP-22 ARCHIVELOG READ WRITE NO 0
SQL>
SQL> select con_id, name, open_mode, dbid, con_uid, guid from v$containers;
CON_ID NAME OPEN_MODE DBID CON_UID GUID
---------- -------------------- ---------- ---------- ---------- --------------------------------
0 TST READ WRITE 2257768950 0 E9E0BFB3A630405FE0536538A8C0FB1E
SQL>
SQL> select CON_ID, DBID, GUID, NAME, OPEN_MODE from v$pdbs;
no rows selected
SQL>
SQL> set linesize 150
SQL> col file_name for a50
SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /oradata/system/sytem01.dbf
SYSAUX /oradata/system/sysaux01.dbf
UNDOTBS1 /oradata/system/unotodb01.dbf
USERS /oradata/system/user01.dbf
TS_DATA /oradata/data/ts_data_01.dbf
TS_IDX /oradata/idx/ts_indx_01.dbf
6 rows selected.
SQL>
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
TEMP /oradata/temp/temp01.dbf
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 ~]$
[oracle@ol7 ~]$ export ORACLE_SID=CDBTST
[oracle@ol7 ~]$ export ORACLE_UNQNAME=CDBTST
[oracle@ol7 ~]$
[oracle@ol7 ~]$ env | grep ORACLE_SID
ORACLE_SID=CDBTST
[oracle@ol7 ~]$ env | grep ORACLE_UNQNAME
ORACLE_UNQNAME=CDBTST
[oracle@ol7 ~]$
[oracle@ol7 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 6 11:21:57 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL>
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
SQL> set linesize 150
SQL> col name for a20
SQL> SELECT dbid, name, db_unique_name, created, log_mode, open_mode, cdb, con_id FROM v$database;
DBID NAME DB_UNIQUE_NAME CREATED LOG_MODE OPEN_MODE CDB CON_ID
---------- -------------------- ------------------------------ --------- ------------ -------------------- --- ----------
1215054330 CDBTST CDBTST 06-OCT-22 NOARCHIVELOG READ WRITE YES 0
SQL> select CON_ID, DBID, GUID, NAME, OPEN_MODE from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
---------- ---------- -------------------------------- -------------------- ----------
2 3663278307 EA535EAA92860935E0536538A8C09DEC PDB$SEED READ ONLY
SQL>
SQL> col file_name for a50
SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /oracdbtst/CDBTST/system01.dbf
SYSAUX /oracdbtst/CDBTST/sysaux01.dbf
UNDOTBS1 /oracdbtst/CDBTST/undotbs01.dbf
USERS /oracdbtst/CDBTST/users01.dbf
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
TEMP /oracdbtst/CDBTST/temp01.dbf
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 ~]$non-CDB를 PDB로 변환 하기
[oracle@ol7 DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@ol7 DATASYNCXML]$ ls
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ env | grep SID
ORACLE_SID=TST
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 5 09:41:54 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --Read Only로 Open
SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.
Total System Global Area 1660940992 bytes
Fixed Size 8897216 bytes
Variable Size 1090519040 bytes
Database Buffers 553648128 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> BEGIN
2 DBMS_PDB.DESCRIBE(
3 pdb_descr_file => '/home/oracle/DATASYNCXML/tst.xml');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> !pwd
/home/oracle/DATASYNCXML
SQL> !ls
tst.xml
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 DATASYNCXML]$ exit
logout
[root@ol7 /]# id
uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[root@ol7 /]# pwd
/
[root@ol7 /]# mkdir -p /orapdbtst/system /orapdbtst/temp /orapdbtst/data /orapdbtst/idx
[root@ol7 /]# chown -R oracle:dba /orapdbtst
[root@ol7 /]#
[root@ol7 /]# su - oracle
Last login: Wed Oct 5 09:33:30 KST 2022 on pts/0
[oracle@ol7 ~]$ cd DATASYNCXML/
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 5 11:02:13 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ export ORACLE_SID=CDBTST
[oracle@ol7 DATASYNCXML]$ export ORACLE_UNQNAME=CDBTST
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ env | grep ORACLE_SID
ORACLE_SID=CDBTST
[oracle@ol7 DATASYNCXML]$ env | grep ORACLE_UNQNAME
ORACLE_UNQNAME=CDBTST
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 6 11:28:04 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/DATASYNCXML/tst.xml')
3 WHEN TRUE THEN 'YES'
4 ELSE 'NO'
5 END;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(compatible);
8 END;
9 /
YES
PL/SQL procedure successfully completed.
SQL>
SQL> col cause for a20
SQL> col name for a20
SQL> col message for a35 word_wrapped
SQL> set linesize 150
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TST';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- -------------------- --------- ----------------------------------- ---------
TST Non-CDB to PDB WARNING PDB plugged in is a non-CDB, PENDING
requires noncdb_to_pdb.sql be run.
TST Parameter WARNING CDB parameter processes mismatch: PENDING
Previous 150 Current 300
SQL>
SQL> CREATE PLUGGABLE DATABASE PDBTST USING '/home/oracle/DATASYNCXML/tst.xml'
2 COPY
3 FILE_NAME_CONVERT = ('/oradata/system/','/orapdbtst/system/',
4 '/oradata/data/','/orapdbtst/data/',
5 '/oradata/idx/','/orapdbtst/idx/',
6 '/oradata/temp/','/orapdbtst/temp/');
Pluggable database created.
SQL>
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBTST MOUNTED
SQL>
SQL> ALTER SESSION SET CONTAINER=PDBTST;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDBTST
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET FEEDBACK 1
~~~~~
PL/SQL procedure successfully completed.
SQL> alter PLUGGABLE database PDBTST close;
Pluggable database altered.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDBTST MOUNTED
SQL>
SQL>
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDBTST READ WRITE NO
SQL>
SQL> col name for a20
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
-------------------- ----------
PDBTST READ WRITE
1 row selected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 ~]$PDB 전환 확인
[oracle@ol7 ~]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 ~]$ env | grep SID
ORACLE_SID=CDBTST
[oracle@ol7 ~]$
[oracle@ol7 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 6 14:02:59 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBTST READ WRITE NO
SQL>
SQL> select USERNAME from dba_users where username='SCOTT';
no rows selected
SQL>
SQL> ALTER SESSION SET CONTAINER=PDBTST;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDBTST
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDBTST READ WRITE NO
SQL> select USERNAME from dba_users where username='SCOTT';
USERNAME
--------------------------------------------------------------------------------
SCOTT
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@ol7 ~]$참고문헌
How to Convert Non-CDB to PDB Database on same local host machine in 12c - Testcase (문서 ID 2012448.1)
반응형