เรียนรู้ Oracle Multitenant Self-Service Provisioning - BETA

ออราเคิล "Multitenant Self-Service Provisioning" เป็นแอพิเคชั่นที่พัฒนาขึ้น เพื่อให้สามารถจัดการกับ Oracle Database 12c Multitenant ได้ง่ายมากขึ้น การติดตั้งนั้นต้องการ
- Oracle Database 12c Release 1
- Oracle Application Express 4.2.3.00.08
- Oracle Application Express Listener 2.0.4
ผมจะไม่ขอยกตัวอย่างในการติดตั้ง เพราะผู้อ่านสามารถดูได้จาก readme.txt ไฟล์ และ My Post!!! ติดตั้ง PDBSS.
และสามารถดูตัวอย่าง การใช้งาน PDBSS.

APEX 4.2.3 บน CDB

ครั้งที่แล้วได้ลองทดสอบใช้ Patch set 17347169 ในการอัพเกรดฐานข้อมูลออราเคิล 12c CDB - APEX 4.2.3 - อัพเกรด APEX บนฐานข้อมูล 12c (Oracle Multitenant). วันนี้เป็นโอกาสดีในการทดสอบติดตั้ง APEX 4.2.3 บน CDB.
เริ่มต้นด้วยการใช้ apexins_con.sql ติดตั้ง APEX แบบ development บน CDB.

SQL> select CON_ID, NAME, OPEN_MODE from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB_OPUN                       READ WRITE

SQL>  select COMP_ID, VERSION, CON_ID from cdb_registry where COMP_ID='APEX';

no rows selected

SQL>
[oracle@test12c ~]$ unzip -l apex_4.2.3.zip|tail
     2256  08-22-2012 10:46   apex/apxconf.sql
     4301  08-15-2013 04:08   apex/apxdvins.sql
     1150  08-22-2012 10:46   apex/apxchpwd.sql
        5  08-22-2012 10:46   apex/apxexit.sql
     4706  03-02-2013 22:19   apex/catapx.sql
      538  03-07-2008 21:20   apex/apxe102.sql
     3271  03-02-2013 22:19   apex/apxxepwd.sql
    13889  06-18-2011 00:08   apex/apexvalidate.sql
---------                     -------
989017663                     12307 files
[oracle@test12c ~]$ ls -la apex_4.2.3.zip
-rw-r--r--. 1 oracle oinstall 162660590 Sep 18 13:15 apex_4.2.3.zip
[oracle@test12c ~]$ cd /tmp/
[oracle@test12c tmp]$ man unzip
[oracle@test12c tmp]$ unzip /home/oracle/apex_4.2.3.zip ^C
[oracle@test12c tmp]$ pwd
/tmp
[oracle@test12c tmp]$ cd apex/
[oracle@test12c apex]$ ls
apex_epg_config_con.sql    apex_rest_config.sql  apxdevrm.sql      apxexit.sql       apxremov2.sql     apxxepwd.sql  images
apex_epg_config_core.sql   apexvalidate.sql      apxdvins_con.sql  apxldimg_con.sql  apxremov_con.sql  builder       load_trans.sql
apex_epg_config.sql        appins.sql            apxdvins.sql      apxldimg.sql      apxremov.sql      catapx.sql    owa
apexins_con.sql            apxchpwd.sql          apxe101.sql       apxpatch.sql      apxrtins_con.sql  core          utilities
apexins.sql                apxconf.sql           apxe102.sql       apxprereq.sql     apxrtins.sql      coreins.sql
apex_rest_config_con.sql   apxdbmig.sql          apxe111.sql       apxrelod.sql      apxsqler.sql      devins.sql
apex_rest_config_core.sql  apxdevrm_con.sql      apxe112.sql       apxremov1.sql     apxxemig.sql      endins.sql
[oracle@test12c apex]$
[oracle@test12c apex]$ ls -l apexins_con.sql
-rw-r--r--. 1 oracle oinstall 2042 Aug 31 13:38 apexins_con.sql
[oracle@test12c apex]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 18 14:12:33 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> @apexins_con.sql SYSAUX SYSAUX TEMP /i/

PL/SQL procedure successfully completed.

Performing installation in multitenant container database in the background.
The installation progress is spooled into apexins_con*.log files.

Please wait...

Installation completed. Log files for each container can be found in:

apexins_con*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

SQL> select CON_ID, NAME, OPEN_MODE from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB_OPUN                       READ WRITE

SQL> select COMP_ID, VERSION, CON_ID from cdb_registry where COMP_ID='APEX';

COMP_ID                        VERSION                            CON_ID
------------------------------ ------------------------------ ----------
APEX                           4.2.3.00.08                             1
APEX                           4.2.3.00.08                             2
APEX                           4.2.3.00.08                             3

เนื่องจากต้องการใช้ Embedded PL/SQL Gateway เลยเซต HTTP port บน PDB (PDB_OPUN) และเปลี่ยน Password + unlock ให้กับ ANONYMOUS

SQL> alter session set container = PDB_OPUN;

Session altered.

SQL> @apxconf

      PORT
----------
      8080

Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value.


Enter a value below for the password for the Application Express ADMIN user.


Enter a password for the ADMIN user              []

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Enter a port for the XDB HTTP listener [      8080] 8000
...changing HTTP Port

PL/SQL procedure successfully completed.

SQL>
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

SQL> ALTER USER ANONYMOUS IDENTIFIED BY PASSWORD ACCOUNT UNLOCK;

User altered.

SQL> select con_id, account_status from cdb_users where username='ANONYMOUS';

    CON_ID ACCOUNT_STATUS
---------- --------------------------------
         3 OPEN
         2 EXPIRED & LOCKED
         1 OPEN

SQL>
SQL> !lsnrctl status |grep 8000
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test12c)(PORT=8000))(Presentation=HTTP)(Session=RAW))

SQL>

จากนั้นก็ได้เวลาติดตั้ง Embedded PL/SQL Gateway
- apex_epg_config_con.sql ติดตั้ง Embedded PL/SQL Gateway ให้กับ CDB$ROOT, PDB$SEED และ PDB...
- apex_epg_config.sql ใช้ติดตั้งบน PDB ต้องการ
ในตัวอย่างนี้ ผมติดตั้ง Embedded PL/SQL Gateway ใน PDB_OPUN

[oracle@test12c apex]$ ls -l apex_epg_config_con.sql
-rw-r--r--. 1 oracle oinstall 2130 Aug 31 13:38 apex_epg_config_con.sql
[oracle@test12c apex]$ ls -l apex_epg_config.sql
-rw-r--r--. 1 oracle oinstall 831 Apr  6 13:09 apex_epg_config.sql
[oracle@test12c apex]$
[oracle@test12c apex]$
[oracle@test12c apex]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 18 18:21:05 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>  alter session set container = PDB_OPUN;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB_OPUN
SQL> @apex_epg_config /tmp

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

. Loading images directory: /tmp/apex/images

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Directory dropped.

timing for: Load Images
Elapsed: 00:02:13.11

PL/SQL procedure successfully completed.


Commit complete.

SQL>

หลังจากนั้นทดสอบเรียกจาก Browser ...

ตอนนี้เราสามารถใช้ APEX 4.2.3 ใน PDB (PDB_OPUN) ผ่าน Embedded PL/SQL Gateway.
จากนั้นเราอาจอนุญาติให้ APEX_040200 นั้นสามารถติดต่อไป Network ข้างนอก

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb_opun;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB_OPUN
SQL> DECLARE
  2    ACL_PATH  VARCHAR2(4000);
  3  BEGIN
  4    -- Look for the ACL currently assigned to '*' and give APEX_040200
  5    -- the "connect" privilege if APEX_040200 does not have the privilege yet.
  6
  7    SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
  8     WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
  9
 10    IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
 11       'connect') IS NULL THEN
 12        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
 13       'APEX_040200', TRUE, 'connect');
 14    END IF;
 15
 16  EXCEPTION
 17    -- When no ACL has been assigned to '*'.
 18    WHEN NO_DATA_FOUND THEN
 19    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
 20      'ACL that lets power users to connect to everywhere',
 21      'APEX_040200', TRUE, 'connect');
 22    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
 23  END;
 24  /
COMMIT;

PL/SQL procedure successfully completed.

SQL>
Commit complete.

SQL>

 อ่านเพิ่มเติม http://docs.oracle.com/cd/E16655_01/install.121/e17958/toc.htm

APEX 4.2.3 - อัพเกรด APEX บนฐานข้อมูล 12c (Oracle Multitenant)

วันนี้ได้อ่าน Oracle Application Express 4.2.3 now available ผมได้เห็นการอัพเกรด APEX บนฐานข้อมูล 12c (Oracle Multitenant). ล่าสุดก่อนหน้านี้ผมได้ลองใช้ patch ที่มีแต่ก็ยังไม่สามารถทำการอัพเกรด APEX ทีเดียวใน CDB$ROOT, PDB$SEED และ PDB... หลังจากผมได้ดาวน์โหลด patch 17347169 ก็ได้เวลาในการทดสอบ
ขั้นตอนแรกก็คือติดตั้ง Patch Set ใน CDB.

SQL> select CON_ID, NAME, OPEN_MODE from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 TESTPDB01                      READ WRITE

SQL> select COMP_ID, VERSION, CON_ID from cdb_registry where COMP_ID='APEX';

COMP_ID                        VERSION                            CON_ID
------------------------------ ------------------------------ ----------
APEX                           4.2.0.00.27                             1
APEX                           4.2.0.00.27                             2
APEX                           4.2.0.00.27                             3

[oracle@test12c ~]$ ls -l p17347169_423_Generic.zip
-rw-r--r--. 1 oracle oinstall 77044627 Sep 17 17:25 p17347169_423_Generic.zip
[oracle@test12c ~]$ unzip -l p17347169_423_Generic.zip |tail
     1197  09-11-2013 02:16   patch/patches/4_2_3/prereq.sql
     1812  07-13-2013 01:55   patch/patches/4_2_3/syspatch.sql
   144074  09-17-2013 00:45   patch/readme.htm
   220231  09-17-2013 00:45   patch/readme.pdf
        0  09-11-2013 04:59   patch/utilities/
     1621  08-31-2013 13:38   patch/utilities/reset_image_prefix.sql
     2804  08-31-2013 13:38   patch/utilities/reset_image_prefix_con.sql
     2575  08-31-2013 13:38   patch/utilities/reset_image_prefix_core.sql
---------                     -------
184039056                     11743 files
[oracle@test12c ~]$ unzip -l p17347169_423_Generic.zip
[oracle@test12c ~]$ ls patch/
apex_rest_config_con.sql   apex_rest_config.sql  apxldimg.sql      apxpatch.sql  images   readme.htm  utilities
apex_rest_config_core.sql  apxldimg_con.sql      apxpatch_con.sql  core          patches  readme.pdf
[oracle@test12c ~]$ cd patch/
[oracle@test12c patch]$

[oracle@test12c patch]$ ls -l apxpatch_con.sql
-rw-r--r--. 1 oracle oinstall 2496 Aug 31 13:38 apxpatch_con.sql
[oracle@test12c patch]$ pwd
/home/oracle/patch
[oracle@test12c patch]$ sqlplus  /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 17 22:32:46 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> @apxpatch_con.sql
PL/SQL procedure successfully completed.

Performing installation in multitenant container database in the background.
The installation progress is spooled into *patch_con*.log files.

Please wait...

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

timing for: Complete Patch
Elapsed: 01:29:21.57

Installation completed. Log files for each container can be found in:

syspatch_con*.log
ddlpatch_con*.log
deppatch_con*.log
corepatch_con*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

SQL>
SQL> !grep ORA- *.log

SQL> !grep PLS- *.log

SQL> select COMP_ID, VERSION, CON_ID from cdb_registry where COMP_ID='APEX';

COMP_ID                        VERSION                            CON_ID
------------------------------ ------------------------------ ----------
APEX                           4.2.3.00.08                             1
APEX                           4.2.3.00.08                             3
APEX                           4.2.3.00.08                             2

หลังจากนั้นอัพเดทรูปใน CDB ถ้าใช้ Embedded PL/SQL Gateway.

SQL> @apxldimg_con.sql /home/oracle/patch

PL/SQL procedure successfully completed.

Performing installation in multitenant container database in the background.
The installation progress is spooled into apxldimg_con*.log files.

Please wait...

/bin/bash: -c: line 0: syntax error near unexpected token `&'
/bin/bash: -c: line 0: `&OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apxldimg_con apxldimg.sql --p&1'


Installation completed. Log files for each container can be found in:

apxldimg_con*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@test12c patch]$ less apxldimg_con.sql
[oracle@test12c patch]$ less  apxpatch_con.sql
[oracle@test12c patch]$ sqlplus  /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Wed Sep 18 00:08:06 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>  @apxldimg_con.sql /home/oracle/patch

PL/SQL procedure successfully completed.

Performing installation in multitenant container database in the background.
The installation progress is spooled into apxldimg_con*.log files.

Please wait...

Installation completed. Log files for each container can be found in:

apxldimg_con*.log

You can quickly scan for ORA errors or compilation errors by using a utility
like grep:

grep ORA- *.log
grep PLS- *.log

SQL>

เป็นอันสำเร็จด้วยดี