一、修改system用户密码、创建dblink
SQL> alter user system identified by oracle;SQL> create public database link to_prod4 connect to system identified by oracle using 'PROD4';Database link created.SQL> select status from v$instance@to_prod4;STATUS------------OPENSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/PROD4/datafile/o1_mf_system_kkxp16yn_.dbf/u01/app/oracle/oradata/PROD4/datafile/o1_mf_sysaux_kkxp2b7o_.dbf/u01/app/oracle/oradata/PROD4/datafile/o1_mf_undotbs1_kkxp33fj_.dbf/u01/app/oracle/oradata/PROD4/datafile/o1_mf_users_kkxp34lm_.dbfSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/PRODCDB/system01.dbf/u01/app/oracle/oradata/PRODCDB/sysaux01.dbf/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf/u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf/u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf/u01/app/oracle/oradata/PRODCDB/users01.dbf/u01/app/oracle/oradata/PRODCDB/pdbseed/undotbs01.dbf/u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf/u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf/u01/app/oracle/oradata/PRODCDB/PDBPROD1/undotbs01.dbf/u01/app/oracle/oradata/PRODCDB/PDBPROD1/users01.dbf
二、创建pdb
SQL> create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging;create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging*ERROR at line 1:ORA-17628: Oracle error 1031 returned by remote Oracle serverORA-01031: insufficient privileges看来system用户权限不够源库授予权限SQL> grant create pluggable database to system;Grant succeeded.SQL> create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging;create pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologgingERROR at line 1:ORA-01276: Cannot add file/u01/app/oracle/oradata/PRODCDB/PDBPROD4/o1_mf_system_kkxp16yn_.dbf. File has an Oracle Managed Files file name.启用了OMF，还是不行
三、测试重命名数据文件
SQL> alter database move datafile '/u01/app/oracle/oradata/PROD4/datafile/o1_mf_system_kkxp16yn_.dbf' to '/u01/app/oracle/oradata/PROD4/datafile/system01.dbf';Database altered.SQL> SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/PROD4/datafile/system01.dbf/u01/app/oracle/oradata/PROD4/datafile/o1_mf_sysaux_kkxp2b7o_.dbf/u01/app/oracle/oradata/PROD4/datafile/o1_mf_undotbs1_kkxp33fj_.dbf/u01/app/oracle/oradata/PROD4/datafile/o1_mf_users_kkxp34lm_.dbfSQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/PROD4/datafile/system01.dbf/u01/app/oracle/oradata/PROD4/datafile/sysaux.dbf/u01/app/oracle/oradata/PROD4/datafile/undotbs1.dbf/u01/app/oracle/oradata/PROD4/datafile/user01.dbfselect x.ksppinm name, y.kspftctxvl value, y.kspftctxdf isdefault, decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE') isadj from sys.x$ksppi x, sys.x$ksppcv2 y where x.inst_id = userenv('Instance')and y.inst_id = userenv('Instance') and x.indx+1 = y.kspftctxpn and x.ksppinm like '%omf%' ;NAME VALUE ISDEFA ISMOD ISADJ------------------------- ---------- ------ ---------- -----_omf enabled TRUE FALSE FALSEcreate pluggable database pdbprod4 from non$cdb@to_prod4 file_name_convert=('/u01/app/oracle/oradata/PROD4/datafile/','/u01/app/oracle/oradata/PRODCDB/PDBPROD4/') nologging;grant create pluggable database to system;ERROR at line 1:ORA-01276: Cannot add file/u01/app/oracle/oradata/PRODCDB/PDBPROD4/o1_mf_system_kkxp16yn_.dbf. File has an Oracle Managed Files file name.看来是目标库的问题
四、修改目标库参数
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/PRODCDB/PDBPROD4';System altered.SQL> create pluggable database pdbprod4 from non$cdb@to_prod4;Pluggable database created.SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBPROD1 MOUNTED 5 PDBPROD4 MOUNTEDSQL> alter pluggable database pdbprod4 open;Warning: PDB altered with errors.SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBPROD1 MOUNTED 5 PDBPROD4 READ WRITE YES修改完参数就不用file_name_convert
五、执行脚本
SQL> alter session set container=pdbprod4;Session altered.SQL> @?/rdbms/admin/noncdb_to_pdb.sqlSQL> SET FEEDBACK 1SQL> SET NUMWIDTH 10SQL> SET LINESIZE 80SQL> SET TRIMSPOOL ONSQL> SET TAB OFFSQL> SET PAGESIZE 100SQL> SET VERIFY OFFSQL>SQL> -- save settingsSQL> STORE SET ncdb2pdb.settings.sql REPLACEWrote file ncdb2pdb.settings.sqlSQL>SQL> SET TIME ON16:13:31 SQL> SET TIMING ON16:13:31 SQL>16:13:31 SQL> WHENEVER SQLERROR EXIT;16:13:31 SQL>16:13:31 SQL> DOC16:13:31 DOC>#######################################################################16:13:31 DOC>#######################################################################16:13:31 DOC> The following statement will cause an "ORA-01403: no data found"16:13:31 DOC> error if we're not in a PDB.16:13:31 DOC> This script is intended to be run right after plugin of a PDB,16:13:31 DOC> while inside the PDB.16:13:31 DOC>########################################################SQL> alter session set container=cdb$root;Session altered.SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBPROD1 MOUNTED 5 PDBPROD4 READ WRITE YESSQL> alter pluggable database pdbprod4 close;Pluggable database altered.SQL> alter pluggable database pdbprod4 open;Pluggable database altered.SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBPROD1 MOUNTED 5 PDBPROD4 READ WRITE NO
