创建 Application Container

什么是 Oracle Managed Files(OMF)？
Using Oracle Managed Files simplifies the administration of an Oracle Database. Oracle Managed Files eliminates the need for you, the DBA, to directly manage the operating system files that comprise an Oracle Database.

With Oracle Managed Files, you specify file system directories in which the database automatically creates, names, and manages files at the database object level. For example, you need only specify that you want to create a tablespace; you do not need to specify the name and path of the tablespace’s data file with the DATAFILE clause. This feature works well with a logical volume manager (LVM).

The database internally uses standard file system interfaces to create and delete files as needed for the following database structures:

  • Tablespaces
  • Redo log files
  • Control files
  • Archived logs
  • Block change tracking files
  • Flashback logs
  • RMAN backups

The following table lists the initialization parameters that enable the use of Oracle Managed Files.

Initialization Parameter Description
DB_CREATE_FILE_DEST Defines the location of the default file system directory or Oracle ASM disk group where the database creates data files or temp files when no file specification is given in the create operation. Also used as the default location for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_``n are not specified.
DB_CREATE_ONLINE_LOG_DEST_n Defines the location of the default file system directory or Oracle ASM disk group for redo log files and control file creation when no file specification is given in the create operation. By changing n, you can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.
DB_RECOVERY_FILE_DEST Defines the location of the Fast Recovery Area, which is the default file system directory or Oracle ASM disk group where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default location for redo log and control files or multiplexed copies of redo log and control files if DB_CREATE_ONLINE_LOG_DEST_``n are not specified. When this parameter is specified, the DB_RECOVERY_FILE_DEST_SIZE initialization parameter must also be specified.

创建 Application Container(使用 OMF)：

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter system set db_create_file_dest='/u01/app/oracle';
SQL> create pluggable database appcon1 as application container admin user app_admin identified by 123;

SQL> alter pluggable database appcon1 open;
SQL> select name,open_mode,application_root,application_pdb from v$pdbs;

NAME            OPEN_MODE  APP APP
--------------- ---------- --- ---
PDB$SEED        READ ONLY  NO  NO
PDB             READ WRITE NO  NO
PDB2            READ WRITE NO  NO
APPCON1         READ WRITE YES NO

创建 PDB 数据库：

1
2
3
4
5
6
7
8
9
10
SQL> alter session set container=APPCON1;
SQL> create pluggable database apppdb1 admin user pdb_admin identified by 123;

SQL> alter pluggable database apppdb1 open;
SQL> select name,open_mode,application_root,application_pdb from v$pdbs;

NAME            OPEN_MODE  APP APP
--------------- ---------- --- ---
APPCON1         READ WRITE YES NO
APPPDB1         READ WRITE NO  YES

如果在应用根容器中存在表，并且需要同步到应用程序 PDB 中，可以通过以下方式：

1
2
SQL> alter session set container=apppdb1;
SQL> alter pluggable database application all sync;

启动新应用程序版本的安装，并为其提供一个字符串以标识改应用程序的版本，需要在应用根容器中进行：

1
2
3
4
5
6
7
SQL> alter session set container=APPCON1;
SQL> alter pluggable database application ref_app begin install '1.0';
SQL> select app_name,app_version,app_status from dba_applications where app_name='REF_APP';

APP_NAME        APP_VERSION     APP_STATUS
--------------- --------------- ------------
REF_APP                         INSTALLING

此时 PDB 处于 INSTALLING 状态，可在此期间进行操作，操作完成后结束该状态。

创建表空间和用户：

1
2
3
SQL> alter session set container=APPCON1;
SQL> create tablespace ref_app_ts datafile size 1m autoextend on next 1m;
SQL> create user imxcai identified by 123 default tablespace ref_app_ts quota unlimited on ref_app_ts container=all;

创建表并插入数据：

1
2
SQL> grant create session, create table to imxcai;
SQL> create table imxcai.reference_data sharing=data (id number, description varchar2(50), constraint t1_pk primary key (id));

sharing 的含义：
此子句仅在应用程序根目录中创建表时适用。
这种类型的表称为应用成勋公共对象，其数据可以与属于应用程序根的应用程序 PDB 共享，确定如何共享表数据，有以下共享属性：

  • METADATA：元数据链接共享表的元数据，但其数据对于每个容器都是唯一的。
  • DATA：数据链接共享表，并且其数据对于应用程序容器中的所有容器都是相同的，它的数据只存储在应用程序根目录中。
  • EXTENDED DATA：扩展数据链接共享表，应用程序根目录中的数据对于应用程序容器中的所有容器都是相同的，但是应用程序容器中的每个应用程序 PDB 都可以存储应用程序 PDB 所特有的数据。
  • NONE：不共享表。

如果省略此子句，则数据库将使用 DEFAULT_SHARING 初始化参数的值来确定表的 Sharing 属性。如果没有初始化值，则默认值为 METADATA。
创建对象表或 XMLTYPE 表时，只可以指定 METADATA 或 NONE
创建表后不能更改 Sharing 属性。

往表里填充数据：

1
2
3
4
5
6
7
8
9
10
11
SQL> insert into imxcai.reference_data select level, 'description of' || level from dual connect by level <=5;
SQL> commit;
SQL> select * from imxcai.reference_data;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 description of1
         2 description of2
         3 description of3
         4 description of4
         5 description of5

结束安装：

1
2
3
4
5
6
SQL> alter pluggable database application ref_app end install;
SQL> select app_name,app_version,app_status from dba_applications where app_name='REF_APP';

APP_NAME        APP_VERSION     APP_STATUS
--------------- --------------- ------------
REF_APP         1.0             NORMAL

同步与应用程序根容器关联的应用程序 PDB：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> alter session set container=apppdb1;
SQL> desc imxcai.reference_data;
ERROR:
ORA-04043: object imxcai.reference_data does not exist

SQL> alter pluggable database application ref_app sync;
SQL> desc imxcai.reference_data;
 Name                                      Null?    Type
 ----------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)

SQL> select * from imxcai.reference_data;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 description of1
         2 description of2
         3 description of3
         4 description of4
         5 description of5

升级应用程序：

1
2
3
4
5
6
7
SQL> alter session set container=APPCON1;
SQL> alter pluggable database application ref_app begin upgrade '1.0' to '1.1';
SQL> select app_name,app_version,app_status from dba_applications WHERE app_name='REF_APP';

APP_NAME        APP_VERSION     APP_STATUS
--------------- --------------- ------------
REF_APP         1.0             UPGRADING

修改表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> alter table imxcai.reference_data add (created_date date default sysdate);
create or replace function imxcai.get_ref_desc (p_id in reference_data.id%type)
return reference_data.description%type
as
l_desc reference_data.description%type;
begin
select description into l_desc from reference_data where id = p_id;
return l_desc;
exception
when no_data_found then
return null;
end;
/

SQL> grant execute on imxcai.get_ref_desc to public;

结束升级：

1
2
3
4
5
6
SQL> alter pluggable database application ref_app end upgrade;
SQL> select app_name,app_version,app_status from dba_applications WHERE app_name='REF_APP';

APP_NAME        APP_VERSION     APP_STATUS
--------------- --------------- ------------
REF_APP         1.1             NORMAL

进行同步：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> alter session set container=apppdb1;
SQL> desc imxcai.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)

SQL> alter pluggable database application ref_app sync;

SQL> desc imxcai.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

卸载应用程序：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> alter session set container=appcon1;
SQL> alter pluggable database application ref_app begin uninstall;
SQL> drop user imxcai cascade;
SQL> drop tablespace ref_app_ts including contents and datafiles;
SQL> alter pluggable database application ref_app end uninstall;

SQL> alter session set container=apppdb1;
SQL> desc imxcai.reference_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----
 ID                                        NOT NULL NUMBER
 DESCRIPTION                                        VARCHAR2(50)
 CREATED_DATE                                       DATE

SQL> alter pluggable database application ref_app sync;
SQL> desc imxcai.reference_data;
ERROR:
ORA-04043: object imxcai.reference_data does not exist

SQL> select app_name,app_version,app_status from dba_applications WHERE app_name='REF_APP';

APP_NAME        APP_VERSION     APP_STATUS
--------------- --------------- ------------
REF_APP         1.1             UNINSTALLED

删除 PDB：

1
2
3
SQL> alter session set container=APPCON1;
SQL> alter pluggable database apppdb1 close;
SQL> drop pluggable database apppdb1 including datafiles;