Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to create PDB in Oracle 12c, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand.

PDB database creation can copy data files from existing databases, including seed containers, pluggable databases, non-CDB databases, and can be created using CREATE PLUGGABLE, RMAN, and EM.

In version 12.1, when creating PDB, SOURCE PDB must be in read only state, and in version 12.2, because of the introduction of new features of undo local mode, when creating PDB, SOURCE PDB is in read write state and can still be created.

In addition, Oracle introduced the refresh PDB feature in version 12.2, which has the function of incremental synchronization to SOURCE PDB.

Use the CREATE PLUGGABLE command to create a PDB using the following resources

1.CDB seed (PDB$SEED)

two。 Clone an existing PDB

Local PDB

Remote PDB

3.non-CDB database

4. Unplugged PDB

Using DBCA, you can create a PDB using the following resources

1.CDB seed (PDB$SEED)

2.RMAN backup

3. Unplugged PDB

Example: (here only demonstrates using the create pluggable database command to create a PDB)

Create a PDB using CDB seed

1. Enter a CREATE PLUGGABLE DATABASE statement in SQL*Plus

(here create a pdb that is not ypdb1, and the administrative user is ypdb1)

CREATE PLUGGABLE DATABASE ypdb1 ADMIN USER ypdb1 IDENTIFIED BY oracle

STORAGE (MAXSIZE 2G)

DEFAULT TABLESPACE ypdb1

DATAFILE'/ u01 SIZE SIZE oradat 12c Universe ypdb1andypdb01.dbf' oradata

PATH_PREFIX ='/ u01Accord oradataoradata, ora12c, ypdb1amp'

FILE_NAME_CONVERT = ('/ u01 scarp oradatamax oradataUniplicateora12c picturepdbdatabases,'/ u01ActionApplicationoradataUniplicoradataUniplicoradata12cUnip ypdb1')

ADMIN USER

Local users used to perform administrative tasks

STORAGE (MAXSIZE 2G)

Specifies the maximum space available for PDB

DEFAULT TABLESPACE

Specify the default Tablespace for PDB

PATH_PREFIX

Used to restrict the directory where directory objects/Oracle XML/Create pfile/Oracle wallets is located

FILE_NAME_CONVERT

Set the location of child containers and copies of data files

two。 After the statement is executed, check the created PDB:

Sys. Ora12c > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 YPDB1 MOUNTED

Sys. Ora12c >

Clone an existing PDB

This approach can be created as a PDB by using other PDB in the current CDB, replicating the PDB in the remote CDB, and replicating the non-CDB database.

Clone a Local PDB

There are a few points to pay attention to when you PDB locally in Clone:

1. The user must have the permission of 'CREATE PLUGGABLE DATABASE' (the sys user is used in the test)

two。 Source PDB cannot be closed

3. If CDB is shared undo,PDB, it must be READ-ONLY status

4. If CDB is not in archive mode, then PDB must be READ-ONLY state

PS: if the Oracle Database version is 12.1, then PDB can only be in READ-ONLY state, because in 12.1 undo mode, only shared undo can be selected.

Sys. Ora12c > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 YPDB1 READ WRITE NO

Sys. Ora12c >

Here, YPDB1 is used as the source PDB, and the PDB created is YPDB2.

(1) check whether it is in shared undo mode

COL PROPERTY_NAME FOR A30

COL PROPERTY_VALUE FOR A30

SELECT property_name, property_value

FROM database_properties

WHERE property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

LOCAL_UNDO_ENABLED TRUE

(2) check whether there is no archivelog mode.

Sys. Ora12c > ARCHIVE LOG LIST

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / u01/app/oracle/recovery/ora12c/arch

Oldest online log sequence 21

Next log sequence to archive 23

Current log sequence 23

Sys. Ora12c >

(3) create verification data

Yangyuhang. Ypdb1 > CONN / AS SYSDBA

Connected.

Sys. Ora12c > conn yangyuhang/yangyuhang@ypdb1

Connected.

Yangyuhang. Ypdb1 > SELECT COUNT (*) FROM t

COUNT (*)

-

22198

Elapsed: 00:00:00.01

Yangyuhang. Ypdb1 >

(4) execute the CREATE PLUGGABLE DATABASE statement (here the sys user is used to connect to the root container)

CREATE PLUGGABLE DATABASE ypdb2 FROM ypdb1

PATH_PREFIX ='/ u01Accord oradataora12c _

FILE_NAME_CONVERT = ('/ u01 scarp oracleplicasoradata'/ u01qqaphora oradata','/ u01gamma','/ u01gamma','/ u01max,','/ u01,',

SERVICE_NAME_CONVERT = ('ypdb1t','ypdb2t')

(5) View the created YPDB2

Sys. Ora12c > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 YPDB1 READ WRITE NO

4 YPDB2 MOUNTED

Sys. Ora12c > SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

--

2 PDB$SEED 1453953285 NORMAL 213 2

3 YPDB1 2376019304 NORMAL 697945 3

4 YPDB2 3908707960 NEW 707501 4

Elapsed: 00:00:00.02

Sys. Ora12c >

(6) check the data file

Sys. Ora12c > ALTER PLUGGABLE DATABASE YPDB2 OPEN

Pluggable database altered.

Elapsed: 00:00:14.02

Sys. Ora12c > SELECT con_id, tablespace_name, file_name FROM cdb_data_files WHERE con_id=4

CON_ID TABLESP FILE_NAME

-

4 SYSTEM / u01/app/oracle/oradata/ora12c/ypdb2/system01.dbf

4 SYSAUX / u01/app/oracle/oradata/ora12c/ypdb2/sysaux01.dbf

4 DEFTBS / u01/app/oracle/oradata/ora12c/ypdb2/deftbs01.dbf

4 USERTBS / u01/app/oracle/oradata/ora12c/ypdb2/usertbs01.dbf

4 UNDO_1 / u01/app/oracle/oradata/ora12c/ypdb2/system01_i1_undo.dbf

4 YPDB1 / u01/app/oracle/oradata/ora12c/ypdb2/ypdb01.dbf

6 rows selected.

Elapsed: 00:00:00.01

Sys. Ora12c >

(7) check service_name

Sys. Ora12c > SELECT service_id, name, network_name, enabled, pdb, con_id FROM cdb_services

SERVICE_ID NAME NETWORK_NAME ENA PDB CON_ID

-

1 SYS$BACKGROUND NO CDB$ROOT 1

2 SYS$USERS NO CDB$ROOT 1

3 ora12c.linux.com ora12c.linux.com NO CDB$ROOT 1

6 ypdb1.linux.com ypdb1.linux.com NO YPDB1 3

1 ypdb1t ypdb1t NO YPDB1 3

1 ypdb2t ypdb2t NO YPDB2 4

2 ypdb2.linux.com ypdb2.linux.com NO YPDB2 4

7 rows selected.

Elapsed: 00:00:00.00

Sys. Ora12c >

The service_name in the source PDB has been changed to the specified service_name.

(8) check verification data

Sys. Ora12c > conn yangyuhang/yangyuhang@ypdb2

Connected.

Yangyuhang. Ypdb2 > SELECT COUNT (*) FROM t

COUNT (*)

-

22198

Elapsed: 00:00:00.11

Yangyuhang. Ypdb2 >

Creating a PDB by Cloning a Remote PDB

When Clone remote PDB, you need to pay attention to the following:

1. If the character set of the CDB to which the PDB is Clone is not AL32UTF8, then the source and target character sets must be compatible.

two。 The byte order of the source side and the destination side must be the same.

3. The connected user must have permission to 'CREATE PLUGGABLE DATABASE'' in CDB

4. Source PDB cannot be closed

5. If the remote CDB is shared undo, the source PDB must be in READ-ONLY state

6. If the remote CDB is not in archive mode, the source PDB must be in READ-ONLY state

Here the test replicates the PDB (ORA12CWPDB) from the win platform ORA12CW to the linux, and the target PDB is PDB2.

(1) View the character sets of the source and destination

Col parameter for a30

Col value for a30

Select * from nls_database_parameters where parameter='NLS_CHARACTERSET'

Or parameter='NLS_LANGUAGE' or parameter='NLS_NCHAR_CHARACTERSET'

(2) check the byte order of the source side and the destination side

Col platform_name for a40

SELECT d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name FROM v$transportable_platform t, gv$database d

WHERE t.platform_name = d.platform_name

(3) View the source archiving mode

Archive log list

(4) View the source-side undo mode

SELECT property_name, property_value

FROM database_properties

WHERE property_name='LOCAL_UNDO_ENABLED'

(5) create a dblink on the destination side

CREATE DATABASE LINK ora12cw CONNECT TO system IDENTIFIED BY oracle USING 'ORA12CW'

(6) run CREATE PLUGGABLE DATABASE statement to copy PDB

CREATE PLUGGABLE DATABASE pdb2 FROM ORA12CWPDB@ora12cw

PATH_PREFIX ='/ u01scarp appAccord oradataUniverse ora12clplplicationpdb2Actionoradata

FILE_NAME_CONVERT = ('D:\ U01\ APP\ ORACLE\ ORADATA\ ORA12CW\ ORA12CWPDB\,','/ u01Accordhand oradatahand ora12clandpdb2scarp')

(7) after the creation is completed, the destination end views the PDB2.

Sys. Ora12cl > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

4 PDB2 MOUNTED

5 PDB1 MOUNTED

Sys. Ora12cl > SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

--

2 PDB$SEED 3409233005 NORMAL 1408788 2

4 PDB2 2218727525 NEW 1824437 4

5 PDB1 1058019921 NORMAL 1535445 5

Elapsed: 00:00:00.00

Sys. Ora12cl >

(8) Target view data file

Sys. Ora12cl > alter pluggable database pdb2 open

Pluggable database altered.

Elapsed: 00:00:14.02

Sys. Ora12cl > SELECT con_id, tablespace_name, file_name FROM cdb_data_files WHERE con_id=4

CON_ID TABLESPACE_NAME FILE_NAME

-

4 SYSTEM / u01/app/oracle/oradata/ora12cl/pdb2/SYSTEM01.DBF

4 SYSAUX / u01/app/oracle/oradata/ora12cl/pdb2/SYSAUX01.DBF

4 UNDOTBS1 / u01/app/oracle/oradata/ora12cl/pdb2/UNDOTBS01.DBF

4 USERS / u01/app/oracle/oradata/ora12cl/pdb2/USERS01.DBF

Elapsed: 00:00:00.00

Sys. Ora12cl >

The remote PDB was cloned successfully.

Creating a PDB by Cloning a Non-CDB

You need to pay attention to the following points when using non-CDB at the remote end of Clone:

1. The connected user must have permission to 'CREATE PLUGGABLE DATABASE'

2.non-CDB cannot be closed

4. If the remote non-CDB is not in archive mode, then the non-CDB must be READ-ONLY state

5. The byte order of the source side and the destination side must be the same.

6. If the PDB you created is from non-CDB, the version of non-CDB must be after 12.1.0.2.

7. If the PDB created is from non-CDB, when the PDB is created, log in to PDB to run the noncdb_to_pdb.sql script with SYSDBA privileges user, and set the

Here, copy the source-side non-CDB database orcl12 to ora12cl and name it PDB3.

(1) View the version of non-CDB database

SELECT * FROM v$version

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

(2) View the remote non-CDB database schema

SELECT dbid, name, open_mode, cdb FROM v$database

DBID NAME OPEN_MODE CDB

3544959965 ORCL12 READ WRITE NO

(3) check whether the non-CDB database is in archive mode.

ARCHIVE LOG LIST

(4) check the byte order of source side and destination side.

Col platform_name for a40

SELECT d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name FROM v$transportable_platform t, gv$database d

WHERE t.platform_name = d.platform_name

(5) create a dblink on the destination side

CREATE DATABASE LINK orcl12 CONNECT TO system IDENTIFIED BY oracle USING 'ORCL12'

(6) execute CREATE PLUGGABLE DATABASE statement

CREATE PLUGGABLE DATABASE pdb3 FROM orcl12@orcl12

PATH_PREFIX ='/ u01scarp appAccord oradataUniverse ora12clplplicationpdb3Actionoradata

FILE_NAME_CONVERT = ('/ u01 scarp oracleplicasoradata','/ u01 scarp oraclehand oradata','/ u01 scarp oraclehand oradata prune oradata prune ora12clle oradata')

(7) View PDB after PDB is created successfully

Sys. Ora12cl > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 PDB3 MOUNTED

4 PDB2 READ WRITE NO

5 PDB1 MOUNTED

Sys. Ora12cl > SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

--

3 PDB3 3564415452 NEW 3014651 3

2 PDB$SEED 3409233005 NORMAL 1408788 2

4 PDB2 2218727525 NORMAL 1824437 4

5 PDB1 1058019921 NORMAL 1535445 5

Elapsed: 00:00:00.00

(8) run the noncdb_to_pdb.sql script (must be executed in PDB with a user with SYSDBA privileges)

$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

(9) View data files

Sys. Ora12cl > alter pluggable database pdb3 open

Pluggable database altered.

Elapsed: 00:00:52.88

Sys. Ora12cl > SELECT con_id, tablespace_name, file_name FROM cdb_data_files WHERE con_id=3

CON_ID TABLESPACE_NAME FILE_NAME

-

3 SYSTEM / u01/app/oracle/oradata/ora12cl/pdb3/system01.dbf

3 SYSAUX / u01/app/oracle/oradata/ora12cl/pdb3/sysaux01.dbf

3 UNDOTBS1 / u01/app/oracle/oradata/ora12cl/pdb3/undotbs01.dbf

3 USERS / u01/app/oracle/oradata/ora12cl/pdb3/users01.dbf

Elapsed: 00:00:00.00

Sys. Ora12cl >

Cloning a PDB Without Cloning Its Data

When cloning an existing PDB or non CDB, we can also add the NO DATA clause to the clone statement. When using the NO DATA clause, we will only clone the model definition in the source PDB, not the data in the PDB. Objects under schemas that are automatically created in the Oracle database will be cloned successfully, while objects under schemas created by the user will only clone the defined structure of the object.

1. Use the no data clause when testing cloning a local PDB

Here, WOQUPDB is cloned into PDBTEST.

(1) Source PDB:

Sys. Woqu > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 WOQUPDB READ WRITE NO

Sys. Woqu > COL PROPERTY_NAME FOR A30

Sys. Woqu > COL PROPERTY_VALUE FOR A30

Sys. Woqu >

(2) check UNDO mode and archiving mode

Sys. Woqu > SELECT property_name, property_value

2 FROM database_properties

3 WHERE property_name='LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

LOCAL_UNDO_ENABLED TRUE

Elapsed: 00:00:00.04

Sys. Woqu > sys. Woqu > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

Sys. Woqu >

(3) create the test data table t. Note that the t table here is under the sys user:

05:51:10 sys. Woqupdb > create table t as select * from dba_objects where rownumselect count (*) from t

COUNT (*)

-

ten

Elapsed: 00:00:00.01

05:52:50 sys. Woqupdb >

There are 10 pieces of data in table t.

(4) create the test data table t_tab. Note that the t table here is under the yangyuhang user:

05:51:46 yangyuhang. Woqupdb > create table t_tab as select * from dba_objects where rownumselect count (*) from t_tab

COUNT (*)

-

nine hundred and ninety nine

Elapsed: 00:00:00.01

05:52:19 yangyuhang. Woqupdb >

(5) create a pdb:pdbtest using the create pluggable database + NO DATA clause:

Sys. Woqu > CREATE PLUGGABLE DATABASE pdbtest FROM woqupdb NO DATA

2 PATH_PREFIX ='/ u01qqappActionoradataUniplementoradataUnigram pdbtest`

3 FILE_NAME_CONVERT = ('/ u01 *

Pluggable database created.

Elapsed: 00:00:09.35

Sys. Woqu >

(6) check the cloned pdbtest:

Sys. Woqu > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

--

2 PDB$SEED READ ONLY NO

3 WOQUPDB READ WRITE NO

5 PDBTEST MOUNTED

Sys. Woqu > SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

--

3 WOQUPDB 1167267009 NORMAL 1443334 3

2 PDB$SEED 399989944 NORMAL 1408751 2

5 PDBTEST 3356573055 NEW 1956581 5

Elapsed: 00:00:00.05

Sys. Woqu > alter pluggable database pdbtest open

Pluggable database altered.

Elapsed: 00:00:07.09

Sys. Woqu > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

--

2 PDB$SEED READ ONLY NO

3 WOQUPDB READ WRITE NO

5 PDBTEST READ WRITE NO

Sys. Woqu > SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

--

3 WOQUPDB 1167267009 NORMAL 1443334 3

2 PDB$SEED 399989944 NORMAL 1408751 2

5 PDBTEST 3356573055 NORMAL 1956581 5

Elapsed: 00:00:00.02

Sys. Woqu >

(7) from the test data

Sys. Woqu > alter session set container=pdbtest

Session altered.

Elapsed: 00:00:00.07

Sys. Woqu > show user con_id

USER is "SYS"

CON_ID

-

five

Sys. Woqu > select count (*) from t

COUNT (*)

-

ten

Elapsed: 00:00:00.00

Sys. Woqu > select count (*) from yangyuhang.t_tab

COUNT (*)

-

0

Elapsed: 00:00:00.02

Sys. Woqu >

Through the test, we can see that the data in the object under the sys is still cloned successfully, while the data in the object under the self-built user is not followed by a clone.

two。 Use the no data clause when testing cloning a remote PDB

Here, clone the remote PDB:PDBTEST locally and name it ORA12CPDB2:

(1) create a DBLINK

Sys. Ora12c > CREATE DATABASE LINK woqu CONNECT TO system IDENTIFIED BY oracle USING 'WOQU'

Database link created.

Elapsed: 00:00:00.15

Sys. Ora12c >

(2) Clone PDBTEST

Sys. Ora12c > CREATE PLUGGABLE DATABASE ORA12CPDB2 FROM PDBTEST@WOQU NO DATA

2 PATH_PREFIX ='/ u01Accord oradataUniverse ora12c Universe ora12cpdb2'

3 FILE_NAME_CONVERT = ('/ u01 *

Pluggable database created.

Elapsed: 00:00:10.44

Sys. Ora12c >

(3) check the ORA12CPDB2 after the clone is completed

Sys. Ora12c > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

--

2 PDB$SEED READ ONLY NO

3 ORA12CPDB READ WRITE NO

5 ORA12CPDB2 MOUNTED

Sys. Ora12c > SELECT pdb_id, pdb_name, con_uid, status, creation_scn, con_id FROM cdb_pdbs

PDB_ID PDB_NAME CON_UID STATUS CREATION_SCN CON_ID

--

3 ORA12CPDB 1788418398 NORMAL 1442734 3

2 PDB$SEED 2375660446 NORMAL 1408749 2

5 ORA12CPDB2 2003741801 NEW 1959473 5

Elapsed: 00:00:00.01

Sys. Ora12c >

(4) validate data

Sys. Ora12c > alter pluggable database ora12cpdb2 open

Pluggable database altered.

Elapsed: 00:00:05.24

Sys. Ora12c > alter session set container=ora12cpdb2

Session altered.

Elapsed: 00:00:00.04

Sys. Ora12c > select count (*) from t

COUNT (*)

-

ten

Elapsed: 00:00:00.01

Sys. Ora12c > select count (*) from yangyuhang.t_tab

COUNT (*)

-

0

Elapsed: 00:00:00.00

Sys. Ora12c >

Thank you for reading this article carefully. I hope the article "how to create PDB in Oracle 12c" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.