Oracle 12c 做pluggable db的複製 不建議開啟OMF

這是今天唯一的心得~因為開了 OMF, 所有pluggable DB都會被放在hash過後的路徑, 例如/u01/app/oracle/oradata/ORCL/E4F9FF9AF0DB3376E044002128C14234/datafile/o1_mf_example_91tsf1h3_.dbf

看起來很醜, 

如果要在OMF時, Clone pdb, 可用以下指令讓Oracle自動決定pdb的路徑:

SQL>

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

否則, 會遇到ORA-65005 錯誤:

SQL> CREATE PLUGGABLE DATABASE salespdb

  ADMIN USER salesadm IDENTIFIED BY password

  2    3    ROLES = (dba)

  4    DEFAULT TABLESPACE sales

  5      DATAFILE '/u01/app/oracle/oradata/salespdb/sales01.dbf' SIZE 20M AUTOEXTEND ON

  6      FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/ORCL/datafile//',

  7                         '/u01/app/oracle/oradata/salespdb/')

  8    STORAGE (MAXSIZE 2G)

  9    PATH_PREFIX = '/u01/app/oracle/oradata/salespdb/';

CREATE PLUGGABLE DATABASE salespdb

*

ERROR at line 1:

ORA-65005: missing or invalid file name pattern for file -

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_91tmj5hx_.dbf

=================================================================
重建了一個cdb (disable OMF) and pluggable db named plugdb1 :

SQL> ALTER PLUGGABLE DATABASE plugdb1 CLOSE IMMEDIATE;

Pluggable database altered.

--先讓pdb進到read only mode
SQL> ALTER PLUGGABLE DATABASE plugdb1 OPEN READ ONLY;

Pluggable database altered.

SQL>
用以下指令clone db
CREATE PLUGGABLE DATABASE salespdb FROM plugdb1
  FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/cdb/plugdb1/', '/u01/app/oracle/oradata/salespdb/')
  PATH_PREFIX = '/u01/app/oracle/oradata/salespdb/';

或
ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb/plugdb1/','/u01/app/oracle/oradata/salespdb/';
CREATE PLUGGABLE DATABASE salespdb from plugdb1;

Pluggable database created.


--如何修改container parameter 
SQL> ALTER SYSTEM SET OPEN_CURSORS=200 CONTAINER=salespdb;
ALTER SYSTEM SET OPEN_CURSORS=200 CONTAINER=salespdb
                                            *
ERROR at line 1:
ORA-65013: invalid CONTAINER clause

alter session set container=salespdb;
--在pdb中, 參數設的比cdb還大看起來是允許的

alter system set open_cursors=555 container=current;