Oracle PDB迁移实践

select alter user ||username|| account lock; from dba_users where account_status=OPEN;

alter pluggable database pdb1 close immediate instances=all;

alter pluggable database pdb1 open read only instances=all;

create database link clone_link connect to system identified by oracle using (description=(address=(protocol=tcp)(host=192.168.10.21)(port=1521))(connect_data=(service_name=pdb1)));

create pluggable database pdb1 from pdb1@clone_link;

This instance was first to open pluggable database PDB1 (container=3)

Database Characterset for PDB1 is ZHS16GBK

Deleting old file#319 from file$

Deleting old file#320 from file$

Deleting old file#321 from file$

Deleting old file#325 from file$

Deleting old file#326 from file$

Deleting old file#327 from file$

Deleting old file#328 from file$

Deleting old file#329 from file$

Adding new file#73 to file$(old file#319)

Adding new file#74 to file$(old file#320)

Adding new file#75 to file$(old file#321)

Adding new file#76 to file$(old file#325)

Adding new file#77 to file$(old file#326)

Adding new file#78 to file$(old file#327)

Adding new file#79 to file$(old file#328)

Adding new file#80 to file$(old file#329)

Successfully created internal service pdb1 at open

ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local

****************************************************************

Post plug operations are now complete.

Pluggable database PPDBETC with pdb id - 3 is now marked as NEW.

****************************************************************

Completed: create pluggable database pdb1 from pdb1@clone_link

SQL> show pdbs

     CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

          2 PDB$SEED                      READ ONLY  NO

           3 PDB1                           READ WRITE YES

select patch_id, patch_uid, version, status, description from dba_registry_sqlpatch;

select inst_id,name,open_mode,restricted from gv$pdbs order by 1,2;

select name,con_id,con_uid,open_mode,restricted,guid from v$pdbs order by 1,2;

select status, message, action from pdb_plug_in_violations where status !=RESOLVED;

oracle> ./datapatch -verbose -pdbs PDB1

SQL> select owner,object_name,object_type,status from dba_objects where status=INVALID and

OWNER IN (PUBLIC,SYS,SYSTEM,XDB,ORDSYS,ORDPLUGINS,ORDDATA,MDSYS,CTXSYS);

OWNER    OBJECT_NAME       OBJECT_TYPE     STATUS

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

XDB      DBMS_XDBUTIL_INT  PACKAGE BODY    INVALID

XDB      DBMS_XDBT         PACKAGE BODY    INVALID

CTXSYS   DRILOAD           PACKAGE BODY    INVALID

CTXSYS   DRVDOC            PACKAGE BODY    INVALID

MDSYS    SDO_OLS           PACKAGE BODY    INVALID

select substr(comp_name,1,30) comp_name, substr(comp_id,1,10) comp_id,substr(version,1,12) version,status from dba_registry;

SQL> select text from dba_errors where name=DBMS_XDBUTIL_INT and owner=XDB;

TEXT

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

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

10 rows selected.

SQL> select text from dba_errors where name=DBMS_XDBT and owner=XDB;

TEXT

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

PLS-00201: identifier CTX_DOC must be declared

PL/SQL: Statement ignored

2 rows selected.

SQL> select text from dba_errors where name=DRILOAD and owner=CTXSYS;

TEXT

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

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

4 rows selected.

SQL> select text from dba_errors where name=DRVDOC and owner=CTXSYS;

TEXT

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

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

PLS-00201: identifier DBMS_SQL must be declared

PL/SQL: Statement ignored

10 rows selected.

SQL> select text from dba_errors where name=SDO_OLS and owner=MDSYS;

TEXT

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

PLS-00201: identifier UTL_HTTP must be declared

PL/SQL: Item ignored

PLS-00201: identifier UTL_HTTP must be declared

PL/SQL: Item ignored

PLS-00201: identifier UTL_HTTP must be declared

PL/SQL: Statement ignored

PLS-00320: the declaration of the type of this expression is incomplete or malformed

PL/SQL: Statement ignored

10 rows selected.

SQL> grant execute on dbms_sql to XDB,CTXSYS;

SQL> grant execute on CTX_DOC to XDB;

SQL> grant execute on UTL_HTTP to MDSYS;

SQL> exec dbms_pdb.exec_as_oracle_script(alter package XDB.DBMS_XDBT compile body);

SQL> exec dbms_pdb.exec_as_oracle_script(alter package CTXSYS.DRVDOC compile body);

SQL> exec dbms_pdb.exec_as_oracle_script(alter package CTXSYS.DRILOAD compile body);

SQL> exec dbms_pdb.exec_as_oracle_script(alter package XDB.DBMS_XDBUTIL_INT compile body);

SQL> exec dbms_pdb.exec_as_oracle_script(alter package MDSYS.SDO_OLS compile body);

alter pluggable database pdb1 open read write instances=all;

select alter user ||username|| account unlock; from dba_users where account_status=LOCKED;

1.无效对象数：

select count(*)  from dba_objects where status = INVALID and owner in (

TEST1,

TEST2,

TEST3,

TEST4,

TEST5);

2.对象总数为：

select count(*)  from dba_objects where owner in (

TEST1,

TEST2,

TEST3,

TEST4,

TEST5);

3.用户下对象所使用的表空间

select distinct TABLESPACE_NAME from dba_segments where owner in (

TEST1,

TEST2,

TEST3,

TEST4,

TEST5);