Refreshable PDB

[felix0102]

前置条件 Prerequisites for the Open Mode of the Source PDB

• The source PDB must not be closed.
• If the remote CDB is not in local undo mode, then the source PDB must be open in read-only mode.
• If the remote CDB is not in ARCHIVELOG mode, then the source PDB must be open in read-only mode.
• If you are creating a refreshable PDB, then the source PDB must be in ARCHIVELOG mode and local undo mode.

Prerequisites for the Database Link

The following prerequisites must be met:

• A database link must enable a connection from the destination CDB (the CDB to which the PDB is being cloned) to the PDB in the source CDB.
• The database link can connect as a common user to the root of the source CDB, or as a common or local user to the source PDB. The source PDB can be either a standard PDB or application PDB.
• The user account specified in the database link must have either of the following privileges:
• The CREATE PLUGGABLE DATABASE privilege, granted either commonly or locally, on the source PDB
• The SYSOPER privilege
• In an Oracle Data Guard environment, if you are performing a remote clone of a PDB into a primary CDB, then on the standby CDB set the STANDBY_PDB_SOURCE_FILE_DBLINK initialization parameter. This parameter specifies the name of the database link used in CREATE PLUGGABLE DATABASE ... FROM dblink. The standby CDB attempts to copy the data files from the source PDB referenced in the database link, but only if the source PDB is open in read-only mode. Otherwise, you must copy data files to the Oracle Managed Files location on the standby CDB.

To clone a remote PDB:

• In SQL*Plus, ensure that the current container is the root of the target CDB or the application root of the target application container.
• Run the CREATE PLUGGABLE DATABASE statement, and specify the source PDB in the FROM clause. Specify other clauses when required.
• After you create the PDB, it is in mounted mode, and its status is NEW. You can view the open mode of a PDB by querying the OPEN_MODE column in the V$PDBS view. You can view the status of a PDB by querying the STATUS column of the CDB_PDBS or DBA_PDBS view.
• A new default service is created for the PDB. The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service.
• Note:If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check the PDB state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.
  Open the new PDB in read/write mode.
• You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.

配置refreshable pdb

• 在源目标数据库添加用户

SQL> create user cloneuser identified by cloneuser ;
SQL> grant dba to cloneuser ;
SQL> grant create pluggable database to cloneuser

• 在目标数据库CDB中创建指向源PBD的dblink

CREATE public DATABASE LINK ocipdb1
    CONNECT TO Admin IDENTIFIED BY P6Test_123
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=158.101.78.182)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=pdb1.sub12260703470.vcna.oraclevcn.com))
            )';

• 在目标数据库CDB执行语句来克隆可刷新的PDB

create pluggable database pdb2 from pdb1@ocipdb1 refresh mode every 10 minutes;

• 查看pdb的刷新状态

select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;

• 修改pdb数据库的刷新时间

ALTER PLUGGABLE DATABASE pdb2 REFRESH MODE EVERY 1 MINUTES;

• 修改pdb数据库为手动刷新

ALTERPLUGGABLE DATABASE pdb2 REFRESH MODE manual;

 alter pluggable database pdb2 close immediate; 
 alter pluggable database pdb2 refresh;
 alter pluggable database pdb2  read only;

sql command

show con_name;  -- 查看当前所在容器位置
show pdbs;      -- 查看所有的PDB
alter pluggable database all open;   -- 打开所有PDB
alter pluggable database pdb2 open;   -- 打开PDB2
alter pluggable database pdb2 open read only; -- 打开PDB2 read only 
alter pluggable database pdb2 close immediate; -- 关闭pdb2数据库

Oracle clone pdb doc
tencent clone pdb

[felix0102]

修改是否是归档模式

在实际应用中，我们需要实现对数据的备份，其实现方式主要有冷备份和热备份两种。现在我们主要讨论热备份的具体操作。热备份也称为联机备份，在数据库的存档模式下进行备份。oracel数据库默认存档模式为关闭状态，要实现数据的热备份，需要改变数据库的存档模式，将其打开。并且需要注意的是数据库的存档模式的操作需要在MOUNT实例中进行，且数据库不能处于OPEN状态。下面将进行详细介绍：

1. 打开存档模式：

    1.1 首先使用sqlplus登录到sys用户，使用SQL：archive log list 查看当前数据库的存档模式。


    此时可以看出当前数据库为默认的非存档模式。

    1.2 使用SQL：alter system set log_archive_start=true  scope=spfile;   --修改系统的日志方式为存档模式--

                           shutdown immediate;   --关闭数据库，因为不能在open状态下进行操作--

                            startup mount;   --启动mount实例，但不是启动数据库--

                            alter database archivelog;    --更改数据库为存档模式--


        1.3  此时我们使用SQL：archive log list 进行查看，已经更改为存档模式。


        1.4 最后别忘了使用SQL：alter database open;打开数据库。

        

2. 关闭存档模式：

前面已经提到，数据的存档模式的操作必须要在mount实例中进行，且数据库要处于非open状态。所有关闭存档模式前，我们需要查看当前数据库的状态，并且关闭数据库，启动mount实例，才可进行操作。其具体操作为：

    2.1 使用SQL：select status from v$instance;查看当前数据库的状态，正常情况下一般都处于open状态。

    

   2.2 使用SQL：shutdown immediate;   --关闭数据库，因为数据库当前状态为open--

                          startup mount;   --启动mount实例--

                          alter database noarchivelog;   --改变数据库存档模式为非存档模式--

  

 2.3 此时使用SQL：archive log list 查看，存档模式已经变更为非存档模式。

    

2.4 再次使用SQL：select status from v$instance;进行查看数据库的当前状态，当前状态为mount状态。

2.5 最后别忘记使用SQL：alter database open;打开数据库。

2.6 再次使用SQL：select status from v$instance;查看数据库状态，为open状态。

[felix0102]

修改是否是 Local UNDO

查看 local undo 是否开启

①：查看 local undo 是否开启：（默认是开启 local undo，因为在安装的时候默认是勾选这个选项的）
SQL> col PROPERTY_NAME for a25;
SQL> col PROPERTY_VALUE for a25;
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name= 'LOCAL_UNDO_ENABLED' ;
PROPERTY_NAME PROPERTY_VALUE

---

LOCAL_UNDO_ENABLED TRUE
注意：在Oracle12.1版本中 undo只支持Global Shared Undo模式，不支持 local undo选项

关闭 local UNDO

②：重启数据库，以upgrade方式启动：
SQL> shutdown immediate
SQL> startup upgrade
SQL> alter database local undo off; ---改变 local undo的模式，数据库必须以upgrade方式启动。
SQL> shutdown immediate
SQL> startup
SQL> col PROPERTY_NAME for a25;
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name= 'LOCAL_UNDO_ENABLED' ;
PROPERTY_NAME PROPERTY_VALUE

---

LOCAL_UNDO_ENABLED FALSE
---注意，从上一步查询得知，local_undo 已经关闭了；

启用 local UNDO

SQL> shutdown immediate
SQL> startup upgrade ---必须以upgrade启动数据库
SQL> alter database local undo on; ----开启 local undo模式
SQL> shutdown immediate
SQL> startup
SQL> col PROPERTY_NAME for a25;
SQL> col PROPERTY_VALUE for a25;
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name= 'LOCAL_UNDO_ENABLED' ;

PROPERTY_NAME PROPERTY_VALUE

---

LOCAL_UNDO_ENABLED TRUE

SQL> alter pluggable database testpdb open ;
SQL> alter session set container=testpdb;
SQL> select name from v $datafile where name like '%undo%' ;

[felix0102]

Create Database link

• 语法

CREATE DATABASE LINK dblink 
    CONNECT TO remote_user IDENTIFIED BY password
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.example.com)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=service_name))
            )';

• 例子

CREATE public DATABASE LINK ocipdb1
    CONNECT TO Admin IDENTIFIED BY P6Test_123
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=158.101.78.182)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=pdb1.sub12260703470.vcna.oraclevcn.com))
            )';

list database links

select * from DBA_DB_LINKS

[felix0102]

set PDB_FILE_NAME_CONVERT

ALTER SESSION SET PDB_FILE_NAME_CONVERT='/home/opc/OracleBase/oradata/ORCL/pdb1','/home/oracle/OracleBase/oradata/P6A/pdb2';

show PDB_FILE_NAME_CONVERT

show parameter pdb_file_name_convert