==[ADM]==12c + Pluggable Databases Examples

● pdb$seed操作

alter session set "_oracle_script"=true;

alter pluggable database pdb$seed close;

alter pluggable database pdb$seed open;

● Create PDB

-- Connect to the container database, check CDB detail

set echo on

SET LINESIZE 200

SET PAGESIZE 10000

SET SERVEROUTPUT ON

COLUMN "DB DETAILS" FORMAT A100

SELECT

     'DB_NAME: '  ||sys_context('userenv', 'db_name')||

  ' / CDB?: '     ||(select cdb from v$database)||

  ' / AUTH_ID: '  ||sys_context('userenv', 'authenticated_identity')||

  ' / USER: '     ||sys_context('userenv', 'current_user')||

  ' / CONTAINER: '||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB')

   "DB DETAILS"

FROM DUAL

/

DB DETAILS

----------------------------------------------------------------------------------------------------

DB_NAME: MALS / CDB?: YES / AUTH_ID: oracle / USER: SYS / CONTAINER: CDB$ROOT

-- check PDB detail (how many PDBs are in the current container)

SET SERVEROUTPUT ON COLUMN "RESTRICTED" FORMAT A10

select v.name, v.open_mode, nvl(v.restricted, 'n/a') "RESTRICTED", d.status

from v$PDBs v inner join dba_pdbs d

using (GUID)

order by v.create_scn

/

NAME                           OPEN_MODE            RESTRICTED STATUS

------------------------------ -------------------- ---------- --------------------------

PDB$SEED                       READ ONLY            NO         NORMAL

-- create the pluggable database

CREATE PLUGGABLE DATABASE pdb_admin ADMIN USER pdb_admin IDENTIFIED BY pdb01

STORAGE (MAXSIZE 1G MAX_SHARED_TEMP_SIZE 100M)

DEFAULT TABLESPACE users

DATAFILE '/u01/app/oracle/oradata/users01.dbf' SIZE 25M AUTOEXTEND ON

PATH_PREFIX = '/u01/app/oracle/oradata/'

FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/pdbseed/',

'/u01/app/oracle/oradata/');

-- check tablespace and datafiles in CDB and PDB

   select con_id,tablespace_name,file_name from cdb_data_files;

● Plugging or Unplugging a PDB from a CDB

SQL> alter pluggable database PDB01 close immediate;

SQL> alter pluggable database CONT1PLUG1 unplug into '/tmp/pdb01.xml';

SQL> create pluggable database ff using '/tmp/pdb01.xml' copy

     file_name_convert=('/u01/app/oracle/oradata/pdb','/u01/app/oracle/oradata/pdb01');

--同步

    SQL> exec DBMS_PDB.SYNC_PDB();

    SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

 

    CON_ID       DBID NAME                           OPEN_MODE

---------- ---------- ------------------------------ ----------

         2 4062617853 PDB$SEED                       READ ONLY

         3 3243018292 PDB                            MOUNTED

              4 3881754933 PDB01                          READ WRITE

● Drop PDB

SQL> alter pluggable database PDB01 close immediate;
SQL> drop pluggable database PDB01 including datafiles;

● Clone PDB

--将源pdb启动至read only状态

    SQL> alter pluggable database pdb1 close immediate;

    SQL> alter pluggable database pdb1 open read only;

    SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 

         file_name_convert=('/u01/app/oracle/oradata/pdb1','/u01/app/oracle/oradata/pdb2');

    SQL> exec DBMS_PDB.SYNC_PDB();

● 将non-cdb转换为pdb3，插入cdb

--将non-cdb启动至read only模式，产生xml文件

    SQL> alter database open read only;

    SQL> exec dbms_pdb.describe ( pdb_descr_file => 'mals2.xml');

--原non-cdb关库后删除temp文件，防止ORA-01119,ORA-27038,OSD-04010报错

    SQL> shutdown immediat

    $ rm -rf /oradata/temp01.dbf

--导入

    SQL> create pluggable database PDB_mals2 using 'mals2.xml' nocopy;

    SQL> alter database open;

--登陆pdb，修改数据字典

    SQL> show con_name

    SQL> alter session set container=PDB_mals2;

    SQL> @?/rdbms/admin/noncdb_to_pdb

--同步pdb

    SQL> alter pluggable database close immediate;

    SQL> alter pluggable database open restricted;

    SQL> exec dbms_pdb.sync_pdb();

    SQL> alter pluggable database close immediate;

    SQL> alter pluggable database open;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27467503/viewspace-1408696/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27467503/viewspace-1408696/