oracle cdb远程克隆多pdb从理论到实战

秒速五厘米 2024-03-24 02:50 81阅读 0赞

## 一、oracle cdb远程克隆pdb理论 ##

### 1、技术背景介绍 ###

在12.2版本以前，如果要克隆pdb或non-CDB，需要先将源pdb或者non-CDB关闭或者置于read-only模式，不过12.2开始及不需要了，可以在不影响源端使用的情况下进行在线clone。

当源pdb是属于一个远端cdb时，需要通过数据链来克隆到本地。数据链必须建在本地CDB中，当在本地CDB中执行`CREATE PLUGGABLE DATABASE`语句时，必须要指定指向远端CDB的数据链。这个数据链要么连接到远端的CDB，要么指向远端的PDB。

### 2、图例 ###

下面这张图展示了如何从远端pdb克隆到本地

![4931122860b86e7a6bcd688f0d0ce832.png][]

## 二、oracle cdb远程克隆多pdb实战部分 ##

### 1、在源端192.168.53.85 创建公共用户 ###

create user c##suq identified by lygle2016 container=all;
    grant  CREATE PLUGGABLE DATABASE to c##suq container=all;
    grant create session,connect,resource,cdb_dba,sysoper to c##suq container=all;

###    2、在目标端192.168.53.116 创建连接到源端的dblink ###

create database link UATDB_LINK connect to c##suq identified by lygle2016 using 'UATDB_LINK';
    添加下面 内容到目标端192.168.53.116 tnsname中
    echo "UATDB_LINK =
      ( DESCRIPTION =
        ( ADDRESS = ( PROTOCOL = TCP )( HOST =192.168.53.85)( PORT = 1521 ))
        ( CONNECT_DATA =
          ( SERVER = DEDICATED )
          ( SERVICE_NAME = lygleuat)
        )
      ) " >> $ORACLE_HOME/network/admin/tnsnames.ora

### 3、在目标端创建多个pdb并开始克隆 ###

在目标端192.168.53.116 使用create pluggable database ... from 的命令远程创建PDB，下面两个脚本同时执行

list="
    META
    LOG
    "
    for one in $list
    do
    sqlplus -S / as sysdba <<EOF
    alter pluggable database ${one} close;  
    drop pluggable database ${one} including datafiles;
    create pluggable database ${one}  from  ${one}@UATDB_LINK FILE_NAME_CONVERT = ( '/u01/oradata/lygleuat/${one}/' , '/u01/oradata/JTFITS/${one}/' );          
    alter pluggable database ${one} open;  
    EOF
    done
    
    
    list="
    META
    LOG
    "
    for one in $list
    do
    sqlplus -S / as sysdba <<EOF
    alter pluggable database ${one} close;  
    drop pluggable database ${one} including datafiles;
    create pluggable database ${one}  from  ${one}@UATDB_LINK FILE_NAME_CONVERT = ( '/u01/oradata/lygleuat/${one}/' , '/u01/oradata/JTFITS/${one}/' );          
    alter pluggable database ${one} open;  
    EOF
    done

### 4、克隆完成后的“善后”工作 ###

#### \#修改密码密码过期时间为不限制（默认180天） ####

list="
    META
    LOG
    "
    for one in $list
    do
    sqlplus  pdbadmin/ly#admin@192.168.53.116:1521/$one<<EOF
    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
    exit
    EOF
    done

#### \#修改超时时间和sga和processer ####

ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10;
    select resource_name,resource_type,limit from dba_profiles where profile='DEFAULT' ;
    alter system set sga_target=11G scope=spfile;
    alter system set processes=3500 scope=spfile;

#### \#随数据库自启动pdb ####

sqlplus -S / as sysdba <<EOF
    CREATE OR REPLACE TRIGGER open_pdbs
    AFTER STARTUP ON DATABASE
    BEGIN
    EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
    END open_pdbs;
    /
    EOF

[4931122860b86e7a6bcd688f0d0ce832.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/aee28ed790cb4aa881df83d45aa3335e.png