Cloning is copying a source PDB from a CDB and plugging the copy into the same CDB or another CDB
Must be connected to a CDB and the current container must be the root
Must have the CREATE PLUGGABLE DATABASE system privilege
The CDB in which the PBD is being created must be in READ WRITE mode
option) It is better you can put the PDB being cloned into READ ONLY mode before you can clone it
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- -------------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB READ WRITE
4 PDB1 READ WRITE
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> select username, DEFAULT_TABLESPACE, common from dba_users order by common;
USERNAME DEFAULT_TABLESPACE COMMON
-------------------- ------------------------------ ----------
ABC USERS NO
HR SYSAUX NO
PDBADMIN USERS NO
LBACSYS SYSTEM YES
OUTLN SYSTEM YES
DBSNMP SYSAUX YES
APPQOSSYS SYSAUX YES
...
SQL> select owner, table_name, tablespace_name from dba_tables where OWNER='HR';
OWNER TABLE_NAME TABLESPACE_NAME
---------- -------------------- --------------------
HR REGIONS SYSAUX
HR LOCATIONS SYSAUX
HR DEPARTMENTS SYSAUX
HR JOBS SYSAUX
HR EMPLOYEES SYSAUX
HR JOB_HISTORY SYSAUX
HR COUNTRIES
7 rows selected.
SQL> select con_id,file#, name from V$DATAFILE;
CON_ID FILE# NAME
---------- ---------- --------------------------------------------------
3 9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
3 10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
3 11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
3 12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf
SQL> select * from V$TABLESPACE;
TS# NAME INC BIG FLA ENC CON_ID
---------- -------------------- --- --- --- --- ----------
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
5 USERS YES NO YES 3
- NOW LET US CLONE ORCLPDB TO pdb5
-- go to vbox
-- login as oracle user
-- mkdir /u01/app/oracle/oradata/ORCL/pdb5
-- login to orclpdb and do some uncommited tans.
-- update hr.employees set salary=salary +1 where employee_id=100;
-- select employee_id, salary from hr.employees where employee_id=100;
[oracle@test ~]$ mkdir /u01/app/oracle/oradata/ORCL/pdb5
[oracle@test ~]$ cd /u01/app/oracle/oradata/ORCL/
[oracle@test ORCL]$ ll
합계 2604224
-rw-r----- 1 oracle oinstall 18726912 11월 27 13:30 control01.ctl
-rw-r----- 1 oracle oinstall 18726912 11월 27 13:30 control02.ctl
drwxr-x--- 2 oracle oinstall 104 11월 11 01:16 orclpdb
drwxr-xr-x 2 oracle oinstall 130 11월 24 15:37 pdb1
drwxr-xr-x 2 oracle oinstall 6 11월 27 13:30 pdb5
drwxr-x--- 2 oracle oinstall 111 11월 11 01:06 pdbseed
-rw-r----- 1 oracle oinstall 209715712 11월 27 13:30 redo01.log
-rw-r----- 1 oracle oinstall 209715712 11월 24 12:03 redo02.log
-rw-r----- 1 oracle oinstall 209715712 11월 26 20:49 redo03.log
-rw-r----- 1 oracle oinstall 681582592 11월 27 13:30 sysaux01.dbf
-rw-r----- 1 oracle oinstall 954212352 11월 27 13:30 system01.dbf
-rw-r----- 1 oracle oinstall 33562624 11월 26 22:31 temp01.dbf
-rw-r----- 1 oracle oinstall 356524032 11월 27 13:30 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 11월 26 20:54 users01.dbf
SQL> alter session set container=orclpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> update hr.employees set salary=salary +1 where employee_id=100;
1 row updated.
SQL> select employee_id, salary from hr.employees where employee_id=100;
EMPLOYEE_ID SALARY
----------- ----------
100 24001
SQL> alter session set container=cdb$root;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
# If connected with a new session, the following error will not occur
# (assuming the update session and database replication session are different).
# It allows testing the data difference between the original and replicated databases.
SQL> CREATE PLUGGABLE DATABASE pdb5 from orclpdb
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/orclpdb/',
'/u01/app/oracle/oradata/ORCL/pdb5/');
CREATE PLUGGABLE DATABASE pdb5 from orclpdb
*
ERROR at line 1:
ORA-65023: active transaction exists in container ORCLPDB
SQL> alter session set container=orclpdb;
Session altered.
SQL> commit;
Commit complete.
# only root user allowed to do
SQL> CREATE PLUGGABLE DATABASE pdb5 from orclpdb
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/orclpdb/',
'/u01/app/oracle/oradata/ORCL/pdb5/');
CREATE PLUGGABLE DATABASE pdb5 from orclpdb
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> alter session set container=cdb$root;
Session altered.
SQL> CREATE PLUGGABLE DATABASE pdb5 from orclpdb
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/orclpdb/',
'/u01/app/oracle/oradata/ORCL/pdb5/');
Pluggable database created.
[oracle@test ORCL]$ cd pdb5
[oracle@test pdb5]$ pwd
/u01/app/oracle/oradata/ORCL/pdb5
[oracle@test pdb5]$ ll
합계 742492
-rw-r----- 1 oracle oinstall 356524032 11월 27 13:53 sysaux01.dbf
-rw-r----- 1 oracle oinstall 293609472 11월 27 13:53 system01.dbf
-rw-r----- 1 oracle oinstall 134225920 11월 27 13:53 temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 11월 27 13:53 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 11월 27 13:53 users01.dbf
[oracle@test pdb5]$
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
---------- -------------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB READ WRITE
4 PDB1 READ WRITE
5 PDB5 MOUNTED
SQL> alter session set container=pdb5;
Session altered.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PDB5 READ WRITE NO
SQL> select employee_id, salary from hr.employees where employee_id=100;
EMPLOYEE_ID SALARY
----------- ----------
100 24001
SQL> select username,DEFAULT_TABLESPACE,common from dba_users order by common;
USERNAME DEFAULT_TABLESPACE COMMON
-------------------- ------------------------------ ----------
ABC USERS NO
HR SYSAUX NO
PDBADMIN USERS NO
LBACSYS SYSTEM YES
OUTLN SYSTEM YES
DBSNMP SYSAUX YES
APPQOSSYS SYSAUX YES
DBSFWUSER SYSAUX YES
GGSYS SYSAUX YES
ANONYMOUS SYSAUX YES
CTXSYS SYSAUX YES
...
SQL> select owner, table_name, tablespace_name from dba_tables where OWNER='HR';
OWNER TABLE_NAME TABLESPACE_NAME
---------- -------------------- --------------------
HR REGIONS SYSAUX
HR LOCATIONS SYSAUX
HR DEPARTMENTS SYSAUX
HR JOBS SYSAUX
HR EMPLOYEES SYSAUX
HR JOB_HISTORY SYSAUX
HR COUNTRIES
7 rows selected.
SQL> select con_id,file#, name from V$DATAFILE;
CON_ID FILE# NAME
---------- ---------- --------------------------------------------------
5 26 /u01/app/oracle/oradata/ORCL/pdb5/system01.dbf
5 27 /u01/app/oracle/oradata/ORCL/pdb5/sysaux01.dbf
5 28 /u01/app/oracle/oradata/ORCL/pdb5/undotbs01.dbf
5 29 /u01/app/oracle/oradata/ORCL/pdb5/users01.dbf
SQL> select * from V$TABLESPACE;
TS# NAME INC BIG FLA ENC CON_ID
---------- -------------------- --- --- --- --- ----------
0 SYSTEM YES NO YES 5
1 SYSAUX YES NO YES 5
2 UNDOTBS1 YES NO YES 5
3 TEMP NO NO YES 5
5 USERS YES NO YES 5
'Database > Oracle' 카테고리의 다른 글
| 52 - [Oracle 19C] Unplugging & Plugging part2 (0) | 2023.11.28 |
|---|---|
| 51 - [Oracle 19C] Unplugging & Plugging part1 (0) | 2023.11.28 |
| 49 - [Oracle 19C] Dropping PDB using DBCA (0) | 2023.11.27 |
| 48 - [Oracle 19C] Dropping PDB using SQL (0) | 2023.11.27 |
| 47 - [Oracle 19C] To create PDB from seed PBD(DBCA) (0) | 2023.11.27 |
