In this article we are going to create a pluggable database from an existing PDB on another remote server belonging so some other container database.
- Local Machine: oravm19c.practice.com
- Remote Machine: oravm19c-2.practice.com
- Container database on both VMs: CDB1
- Pluggable database on remote machine under CDB1: PDB1
Few guidelines before we perform this operation:
- DB User performing clone must have CREATE PLUGGABLE DATABASE privilege in the root container.
- The remote PDB to be copied must be open in read-only mode.
- A DB link would be needed to connect to remote Database, either CDB or PDB via a common user.
- That DB user in remote database also must have CREATE PLUGGABLE DATABASE privilege.
- The local and remote databases must have the same endianness, options installed and character sets.
- If Transparent data encryption is configured, local database must adapt the same.
Lets' begin.
1) Connect to Remote PDB and create a user. Grant necessary privileges to it.
[oracle@oravm19c-2 admin]$ sqlplus sys@cdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 12 06:14:35 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1124073368 bytes
Fixed Size 9133976 bytes
Variable Size 301989888 bytes
Database Buffers 805306368 bytes
Redo Buffers 7643136 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
SQL> alter session set container = pdb1;
Session altered.
SQL>
SQL> create user cl_user identified by pdbcluser;
User created.
SQL> grant create session, create pluggable database to cl_user;
Grant succeeded.
2) Change PDB1 to read only mode.
SQL> alter pluggable database pdb1 close;
Pluggable database altered.
SQL> alter pluggable database pdb1 open read only;
Pluggable database altered.
3) Add TNS entry on local server to connect to PDB on remote server.
[oracle@oravm19c admin]$ cd $ORACLE_HOME/network/admin
[oracle@oravm19c admin]$ vi tnsnames.ora
[oracle@oravm19c admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c.practice.com)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c.practice.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oravm19c-2.practice.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1.practice.com)
)
)
[oracle@oravm19c admin]$
4) Create pluggable database now on local server using DB link.
Note:
- Please use a different name for this PDB or you might get into trouble if same file structure is used both sides.
- Also, you don't need to use FILE_NAME_CONVERT if Oracle Managed Files (OMF) is used.
SQL> create pluggable database pdb2 from pdb1@dblink_pdb_clone FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB1/pdb1','/u01/app/oracle/oradata/CDB1/pdb2');
Pluggable database created.
5) Check PDB and verify it's access.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 PDB2 MOUNTED
SQL>
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL>
SQL> alter session set container = pdb2;
Session altered.
SQL> set lines 200 pages 200
SQL> col file_name for a50;
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/CDB1/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/undotbs01.dbf
/u01/app/oracle/oradata/CDB1/pdb2/users01.dbf
PDB2 is successfully cloned using PDB1 from a remote server.
I hope this helps !!
No comments:
Post a Comment