oracle 18c新特性 refreshable pdb switchover

概述：类似于18c基于PDB级别的ADG同步啊，来来来 赶紧测试下：

官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-pdbs-with-sql-plus.html#GUID-B505C234-FAF4-4BAB-8B59-59276E0EA128

实验：

将cdb orcl中pdb1同步至cdb cube中的pdb1_pre

SYS@AS SYSDBA@orcl> alter system set encrypt_new_tablespaces=ddl;

System altered.

SYS@AS SYSDBA@orcl> alter session set container=pdb1;

Session altered.

SYS@AS SYSDBA@orcl> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/ora/app/oradata/ORCL/73715875A2A26C84E0531238A8C0FA52/datafile/o1_mf_system_fq751snc_.dbf

/u01/ora/app/oradata/ORCL/73715875A2A26C84E0531238A8C0FA52/datafile/o1_mf_sysaux_fq751snx_.dbf

/u01/ora/app/oradata/ORCL/73715875A2A26C84E0531238A8C0FA52/datafile/o1_mf_undotbs1_fq751so0_.dbf

/u01/ora/app/oradata/ORCL/73715875A2A26C84E0531238A8C0FA52/datafile/o1_mf_users_fq752dx4_.dbf

SYS@AS SYSDBA@orcl> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SYS@AS SYSDBA@orcl> create user cube identified by cube default tablespace users;

User created.
SYS@AS SYSDBA@orcl> alter user cube  quota unlimited on users;

User altered.

SYS@AS SYSDBA@orcl> create table cube.t1 (n1 number);

Table created.

SYS@AS SYSDBA@orcl> insert into cube.t1 values (1);

1 row created.

SYS@AS SYSDBA@orcl> commit;

Commit complete.

SYS@AS SYSDBA@orcl> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO
SYS@AS SYSDBA@orcl> conn / as sysdba
Connected.
SYS@AS SYSDBA@orcl> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO
SYS@AS SYSDBA@orcl> CREATE USER c##u1 IDENTIFIED BY oracle;

User created.

SYS@AS SYSDBA@orcl> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;
GRANT SYSOPER TO c##u1 CONTAINER=ALL;

Grant succeeded.

SYS@AS SYSDBA@orcl> 
Grant succeeded.

SYS@AS SYSDBA@orcl> 
Grant succeeded.
 

SYS@AS SYSDBA@cube> CREATE USER c##u1 IDENTIFIED BY oracle;

User created.

SYS@AS SYSDBA@cube> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##u1 CONTAINER=ALL;
GRANT CREATE PLUGGABLE DATABASE TO c##u1 CONTAINER=ALL;

Grant succeeded.

SYS@AS SYSDBA@cube> GRANT SYSOPER TO c##u1 CONTAINER=ALL;
Grant succeeded.

SYS@AS SYSDBA@cube> 

Grant succeeded.

tns:

CUBE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 18c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cube)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 18c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

SYS@AS SYSDBA@cube> CREATE DATABASE LINK orcl_datalink CONNECT TO c##u1 IDENTIFIED BY oracle USING 'orcl';

Database link created.

SYS@AS SYSDBA@cube> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/cube/system01.dbf
/home/oracle/cube/sysaux01.dbf
/home/oracle/cube/undotbs01.dbf
/home/oracle/cube/pdbseed/system01.dbf
/home/oracle/cube/pdbseed/sysaux01.dbf
/home/oracle/cube/users01.dbf
/home/oracle/cube/pdbseed/undotbs01.dbf

7 rows selected.

SYS@AS SYSDBA@cube> CREATE PLUGGABLE DATABASE pdb1_ref FROM pdb1@orcl_datalink 
  2    CREATE_FILE_DEST='/home/oracle/cube/pdb1_ref/' 
  3    REFRESH MODE MANUAL;

Pluggable database created.

SYS@AS SYSDBA@cube>  alter pluggable database PDB1_REF refresh;

Pluggable database altered.

SYS@AS SYSDBA@cube> alter pluggable database PDB1_REF open read only;

Pluggable database altered.

SYS@AS SYSDBA@cube> alter session set container=PDB1_REF;

Session altered.

SYS@AS SYSDBA@cube> select * from cube.t1;

        N1
----------
         1

SYS@AS SYSDBA@cube> alter pluggable database PDB1_REF refresh mode EVERY 1 minutes;

Pluggable database altered.

SYS@AS SYSDBA@orcl> alter session set container=pdb1;

Session altered.

SYS@AS SYSDBA@orcl> insert into cube.t1 values (2);

1 row created.

SYS@AS SYSDBA@orcl> insert into cube.t1 values (3);

1 row created.
SYS@AS SYSDBA@orcl> commit;

SYS@AS SYSDBA@cube> select * from cube.t1;

        N1
----------
         1

等待一分钟后 没有同步

SYS@AS SYSDBA@cube> alter pluggable database PDB1_REF refresh;
alter pluggable database PDB1_REF refresh
*
ERROR at line 1:
ORA-65025: Pluggable database PDB1_REF is not closed on all instances.

SYS@AS SYSDBA@cube> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1_REF                       READ ONLY  NO
SYS@AS SYSDBA@cube> alter pluggable database PDB1_REF close;

Pluggable database altered.

SYS@AS SYSDBA@cube>  alter pluggable database PDB1_REF open read only;

Pluggable database altered.

SYS@AS SYSDBA@cube> alter session set container=PDB1_REF;

Session altered.

SYS@AS SYSDBA@cube> select * from cube.t1;

        N1
----------
         2
         3
         1
需要pdb在mount时同步，没有类似ADG open read only with apply 的功能

 

switchover

SYS@AS SYSDBA@cube> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1_REF                       READ ONLY NO

SYS@AS SYSDBA@orcl>  CREATE DATABASE LINK cube_datalink CONNECT TO c##u1 IDENTIFIED By oracle USING 'cube';

Database link created.

SYS@AS SYSDBA@orcl> alter session set container=pdb1;

Session altered.
SYS@AS SYSDBA@orcl> ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM PDB1_REF@cube_datalink SWITCHOVER;
ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM PDB1_REF@cube_datalink SWITCHOVER
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SYS@AS SYSDBA@orcl> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SYS@AS SYSDBA@orcl> shutdown immdeiate;
SP2-0717: illegal SHUTDOWN option
SYS@AS SYSDBA@orcl> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@AS SYSDBA@orcl> startup
ORACLE instance started.

Total System Global Area  524285264 bytes
Fixed Size                  8897872 bytes
Variable Size             394264576 bytes
Database Buffers          117440512 bytes
Redo Buffers                3682304 bytes
Database mounted.
Database opened.
SYS@AS SYSDBA@orcl> 

SYS@AS SYSDBA@orcl> ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM PDB1_REF@cube_datalink SWITCHOVER;

Pluggable database altered.

SYS@AS SYSDBA@orcl> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           MOUNTED

SYS@AS SYSDBA@cube> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1_REF                       READ WRITE NO

自此实验完成，可以正常switchover  由于 我忘了开归档 切换回来后pdb1 refresh 有问题  后续有时间再测试，基本按照官方文档来没问题；以后可以基于pdb级别进行类似dg 部署啦，不过同步需要写好脚本 进行定期的刷新