12c pdb基础-CSDN博客

12c pdb基础

手工创建cdb和pdb

[@more@]

CREATE DATABASE prod

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prod/redo01a.log') SIZE 100M BLOCKSIZE 512,

GROUP 2 ('/u01/app/oracle/oradata/prod/redo02a.log') SIZE 100M BLOCKSIZE 512,

GROUP 3 ('/u01/app/oracle/oradata/prod/redo03a.log') SIZE 100M BLOCKSIZE 512

MAXLOGHISTORY 100

MAXLOGFILES 16

MAXLOGMEMBERS 5

MAXDATAFILES 1024

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/u01/app/oracle/oradata/prod/system01.dbf'

SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SYSAUX DATAFILE '/u01/app/oracle/oradata/prod/sysaux01.dbf'

SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DEFAULT TABLESPACE users

DATAFILE '/u01/app/oracle/oradata/prod/users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/app/oracle/oradata/prod/temp01.dbf'

SIZE 100M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE '/u01/app/oracle/oradata/prod/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

ENABLE PLUGGABLE DATABASE

seed

file_name_convert=('/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/seed')

USER_DATA TABLESPACE usertbs

DATAFILE '/u01/app/oracle/oradata/seed/users01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

@?/rdbms/admin/catcdb.sql、catproc.sql、catalog.sql

配置em express

exec DBMS_XDB_CONFIG.SETHTTPPORT(http_port_number);

exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);

http://database_hostname:http_port_number/em/

https://database_hostname:https_port_number/em/

创建pdb

1.create pluggable database from the seed

SQL> create pluggable database pdb5 admin user pdbadmin identified by oracle storage(maxsize 5g max_shared_temp_size 200m)

----maxsize specifies that the storage used by all tablespaces that belong to the PDB must not exceed 10 gigabytes

----max_shared_temp_size specifies that the storage used by the PDB sessions in the shared temporary tablespace must not exceed 200 megabytes.

default tablespace users datafile '/u01/app/oracle/oradata/orcl/pdb5/pdb5_user01.dbf' size 200m autoextend on

file_name_convert=('/u01/app/oracle/oradata/orcl/pdbseed','/u01/app/oracle/oradata/orcl/pdb5');

Pluggable database created.

2.create pluggable database from the local cloning

SQL> alter pluggable database pdb close;

Pluggable database altered.

SQL> alter pluggable database pdb open read only;

Pluggable database altered.

SQL> create pluggable database pdb1 from pdb file_name_convert=('/u01/app/oracle/oradata/orcl/pdb','/u01/app/oracle/oradata/orcl/pdb1');

Pluggable database created.

3.create pluggable database from the unplugged pdb

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/orcl/pdb1/pdb1.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1;

Pluggable database dropped.

[oracle@test pdb1]$ pwd

/u01/app/oracle/oradata/orcl/pdb1

[oracle@test pdb1]$ ls

pdb1.xml pdb_users01.dbf sysaux01.dbf system01.dbf

[oracle@test pdb1]$ mv * ..

SQL> create pluggable database pdb1 using '/u01/app/oracle/oradata/orcl/pdb1.xml' source_file_name_convert=('/u01/app/oracle/oradata/orcl/pdb1/','/u01/app/oracle/oradata/orcl/') file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/pdb1/') move storage(maxsize 5g max_shared_temp_size 200m);

Pluggable database created.

4.create pluggable database from the non-cdb

原数据库:

SQL> alter database open read only;

Database altered.

SQL> exec dbms_pdb.describe(PDB_DESCR_FILE=>'/u01/app/oracle/oradata/orcl/pdb2/pdb2.xml');

PL/SQL procedure successfully completed.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

新数据库：

SQL> create pluggable database prod using '/u01/app/oracle/oradata/orcl/pdb2/pdb2.xml' file_name_convert=('/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/orcl/pdb2/') copy;

Pluggable database created.

此时，新创建的pdb为mount状态，须执行以下步骤才能打开数据库

SQL> alter session set container=prod;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database prod open;

Pluggable database altered.

其他相关

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')