Oracle Multitenant : DBCA PDB Relocate in Oracle Database 19c

最新推荐文章于 2021-04-03 15:07:49 发布

吥輕誩放棄

最新推荐文章于 2021-04-03 15:07:49 发布

阅读量312

点赞数 1

本文链接：https://blog.csdn.net/sandy9919/article/details/88716722

版权

Oracle 19c 专栏收录该内容

19 篇文章 4 订阅

订阅专栏

参考：Oracle Multitenant Administrator’s Guide

Oracle Database 12c Release 2 (12.2) 引入了使用该CREATE PLUGGABLE DATABASE命令重定位可插拔数据库（PDB）的功能。在Oracle 19c中，现在可以使用Database Configuration Assistant（DBCA）执行可插拔数据库（PDB）的重定位。

1. 预备需求

准备重新定位、连接到目标CDB

-- 登录目标库

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

在目标数据库创建用户。在这种情况下，我们将在目标PDB中使用普通用户

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;
GRANT SYSOPER TO c##remote_clone_user CONTAINER=ALL;

与常规重定位不同，我们不需要创建dblink。我们只需要提供用于创建链接的权限。DBCA完成剩下的工作。

检查目标CDB是否处于local undo mode和archivelog mode。

--- 登录目标CDB，查看状态

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

目标CDB处于local undo mode和archivelog mode，因此我们无需将目标CDB转为只读模式。

连接到源CDB并准备重新定位。

--- 登录源CBD

export ORAENV_ASK = NO
export ORACLE_SID = cdb1
.oraenv
export ORAENV_ASK = YES

sqlplus / as sysdba

检查源CDB是否处于local undo mode和archivelog mode。

--- 登录源CDB

CONN / AS SYSDBA

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SQL>


SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

2. 使用DBCA重新定位PDB

Oracle 19c中，-relocatePDB引入了DBCA 命令，允许我们将PDB重定位到不同的容器数据库（CDB）。

-relocatePDB - Command to Relocate a pluggable database.
      -remotePDBName <Name of the pluggable database to clone/relocate>
      -pdbName <Pluggable database name>
      -dbLinkUsername <Common user of a remote CDB, used by database link to connect to remote CDB.>
      -remoteDBConnString <EZCONNECT string to connect to Source database for example "host:port/servicename">
      -sourceDB <Database unique name for RAC database or SID for Single Instance database>
      [-remoteDBSYSDBAUserName <User name with SYSDBA privileges of remote database>]
      [-dbLinkUserPassword <Common user password of a remote CDB, used by database link to connect to remote CDB.>]
      [-useWalletForDBCredentials <true | false> Specify true to load database credentials from wallet]
         -dbCredentialsWalletLocation <Path of the directory containing the wallet files>
         [-dbCredentialsWalletPassword <Password to open wallet with auto login disabled>]
      [-remoteDBSYSDBAUserPassword <Password for remoteDBSYSDBAUserName user of remote database.>]
      [-sysDBAUserName <User name  with SYSDBA privileges>]
      [-sysDBAPassword <Password for sysDBAUserName user name>]

注：dbca -relocatePDB -help 查询正确用法。

确保环境配置为源实例“cdb1”，并从“cdb2”实例中重新定位PDB

--- 登录源CDB，重定位pdb2

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES


dbca -silent \
  -relocatePDB \
  -pdbName pdb2 \
  -sourceDB cdb1 \
  -remotePDBName pdb2 \
  -remoteDBConnString node2dg:1521/cdb2 \
  -remoteDBSYSDBAUserName sys \
  -remoteDBSYSDBAUserPassword SysPassword1 \
  -dbLinkUsername c##remote_clone_user \
  -dbLinkUserPassword remote_clone_user
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "pdb2" plugged successfully.
Look at the log file "/app/oracle/cfgtoollogs/dbca/cdb1/pdb2/cdb10.log" for further details.
$

连接到源CDB并检查新PDB的状态

--- 登录源CDB

COLUMN name FORMAT A30

SELECT con_id, name, open_mode FROM v$pdbs ORDER BY 1;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ WRITE

SQL>

要重新定位它，我们需要在源数据库中创建链接用户

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;
GRANT SYSOPER TO c##remote_clone_user CONTAINER=ALL;

确保环境配置为目标实例“cdb2”，并从“cdb1”实例中名为“pdb1”的本地PDB重新定位名为“pdb1”的PDB

--- 登录目标CDB

export ORACLE_SID=cdb2
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES


dbca -silent \
  -relocatePDB \
  -pdbName pdb1 \
  -sourceDB cdb2 \
  -remotePDBName pdb1 \
  -remoteDBConnString node2:1521/cdb1 \
  -remoteDBSYSDBAUserName sys \
  -remoteDBSYSDBAUserPassword SysPassword1 \
  -dbLinkUsername c##remote_clone_user \
  -dbLinkUserPassword remote_clone_user
Prepare for db operation
50% complete
Create pluggable database using relocate PDB operation
100% complete
Pluggable database "pdb1" plugged successfully.
Look at the log file "/app/oracle/cfgtoollogs/dbca/cdb2/pdb1/cdb10.log" for further details.
$

连接到目标CDB并检查新PDB的状态

--- 登录目标CDB

COLUMN name FORMAT A30

SELECT con_id, name, open_mode FROM v$pdbs ORDER BY 1;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB2                           READ WRITE
         4 PDB1                           READ WRITE

SQL>

附

# Local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "+DATA/nineteenc/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

# Remote container (cdb2) with PDB (pdb2).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb2 -sid cdb2 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb2 \
 -pdbAdminPassword PdbPassword1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "+DATA/nodetwodg/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

 

# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword SysPassword1
#dbca -silent -deleteDatabase -sourceDB cdb2 -sysDBAUserName sys -sysDBAPassword SysPassword1

数据库启用了Oracle Managed Files（OMF）并切换到archivelog模式

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '+DATA/nodetwodg';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb2 OPEN;
ALTER PLUGGABLE DATABASE pdb2 SAVE STATE;

EXIT;
EOF


export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '+DATA/nineteenc';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

EXIT;
EOF