PDBめも - hogesql’s diary

●PDB起動後、以下でSTATEをSAVE--CDB起動時にPDBも同時起動

ALTER PLUGGABLE DATABASE salespdb SAVE STATE;

●CONTAINER一覧

COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

●各PDBの名前およびオープン・モードの表示

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

NAME OPEN_MODE RESTRICTED OPEN_TIME
-- -- -- --
PDB$SEED READ ONLY NO 21-MAY-12 12.19.54.465 PM
HRPDB READ WRITE NO 21-MAY-12 12.34.05.078 PM
SALESPDB MOUNTED NO 22-MAY-12 10.37.20.534 AM

●CDB内の各PDBのデータファイルの表示シードなど、CDB内のすべてのPDBの各データファイルの名前と場所を表示。

COLUMN PDB_ID FORMAT 999
COLUMN PDB_NAME FORMAT A8
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A10
COLUMN FILE_NAME FORMAT A45
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d WHERE p.PDB_ID = d.CON_ID ORDER BY p.PDB_ID;

●CDB内の一時ファイルの表示 CDB内の各一時ファイルの名前と場所、および一時ファイルを使用する表領域を表示。

COLUMN CON_ID FORMAT 999
COLUMN FILE_ID FORMAT 9999
COLUMN TABLESPACE_NAME FORMAT A15
COLUMN FILE_NAME FORMAT A45
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME FROM CDB_TEMP_FILES;

●PDBに関連付けられているサービスの表示 PDB名、ネットワーク名、およびPDBに関連付けられている各サービスのコンテナIDを表示。

COLUMN NETWORK_NAME FORMAT A30
COLUMN PDB FORMAT A15
COLUMN CON_ID FORMAT 999
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL AND CON_ID > 2;

●PDB構築時にCDBのみJVMインストールし、後日PDBにJVMを追加する例

◆j.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool J.log append
host perl /oracle/rdbms/admin/catcon.pl -n 1 -l /home/hoge -v -b initjvm -c 'PDB$SEED TESTPDB' -U "SYS"/"&&sysPassword" /oracle/javavm/install/initjvm.sql;
host perl /oracle/rdbms/admin/catcon.pl -n 1 -l /home/hoge -v -b catjava -c 'PDB$SEED TESTPDB' -U "SYS"/"&&sysPassword" /oracle/rdbms/admin/catjava.sql;
spool off

◆実行例

$ export ORACLE_HOME=/oracle
$ export ORACLE_SID=TESTCDB
$ PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
$ PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
$ sqlplus / as sysdba
SQL> alter pluggable database testpdb open;

プラガブル・データベースが変更されました。

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
--- --- --- ---
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO

SQL>@j
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
接続されました。
SQL> set echo on
SQL> spool J.log append
SQL> host perl /oracle/rdbms/admin/catcon.pl -n 1 -l /home/hoge -v -b initjvm -c 'PDB$SEED TESTPDB' -U "SYS"/"&&sysPassword" /oracle/javavm/install/initjvm.sql;
catcon: ALL catcon-related output will be written to [/home/hoge/initjvm_catcon_4162.lst]
catcon: See [/home/hoge/initjvm*.log] files for output generated by scripts
catcon: See [/home/hoge/initjvm_*.lst] files for spool files, if any
catconInit: start initializing catcon
catconInit: finished constructing connect strings
catconInit: start CDB-specific processing
catconInit: finished examining instances which can be used to run scripts/SQL statements.
catconInit: started SQL*Plus processes.
catconInit: DBMS version: 12.2.0.1.0.
catconInit: initialization completed successfully (201x-0x-07 10:47:52)
catconExec: start executing scripts/SQL statements
catconExec: finished examining scripts/SQL statements to be executed.
catconExec: run all scripts/statements against remaining 2 PDBs
force_pdb_modes: reset_pdb_modes completed successfully
catconExec_int: executing "@/oracle/javavm/install/initjvm.sql" in container PDB$SEED using process 0
catconExec_int: executing "@/oracle/javavm/install/initjvm.sql" in container TESTPDB using process 0
catconExec: finished executing scripts/SQL statements
catconWrapUp: (PID=4162) about to free up all resources
catconRevertPdbModes: reverted PDB$SEED to original mode
catconRevertPdbModes: catcon_RevertUserPdbModes was not set
catconWrapUp: done
catcon.pl: completed successfully
catconRevertPdbModes: catcon_RevertSeedPdbMode was not set
catconRevertPdbModes: catcon_RevertUserPdbModes was not set
catconRevertPdbModes: catcon_RevertSeedPdbMode was not set
catconRevertPdbModes: catcon_RevertUserPdbModes was not set

SQL> host perl /oracle/rdbms/admin/catcon.pl -n 1 -l /home/hoge -v -b catjava -c 'PDB$SEED TESTPDB' -U "SYS"/"&&sysPassword" /oracle/rdbms/admin/catjava.sql;
catcon: ALL catcon-related output will be written to [/home/hoge/catjava_catcon_4908.lst]
catcon: See [/home/hoge/catjava*.log] files for output generated by scripts
catcon: See [/home/hoge/catjava_*.lst] files for spool files, if any
catconInit: start initializing catcon
catconInit: finished constructing connect strings
catconInit: start CDB-specific processing
catconInit: finished examining instances which can be used to run scripts/SQL statements.
catconInit: started SQL*Plus processes.
catconInit: DBMS version: 12.2.0.1.0.
catconInit: initialization completed successfully (201x-0x-07 10:50:50)
catconExec: start executing scripts/SQL statements
catconExec: finished examining scripts/SQL statements to be executed.
catconExec: run all scripts/statements against remaining 2 PDBs
force_pdb_modes: reset_pdb_modes completed successfully
catconExec_int: executing "@/oracle/rdbms/admin/catjava.sql" in container PDB$SEED using process 0
catconExec_int: executing "@/oracle/rdbms/admin/catjava.sql" in container TESTPDB using process 0
catconExec: finished executing scripts/SQL statements
catconWrapUp: (PID=4908) about to free up all resources
catconRevertPdbModes: reverted PDB$SEED to original mode
catconRevertPdbModes: catcon_RevertUserPdbModes was not set
catconWrapUp: done
catcon.pl: completed successfully
catconRevertPdbModes: catcon_RevertSeedPdbMode was not set
catconRevertPdbModes: catcon_RevertUserPdbModes was not set
catconRevertPdbModes: catcon_RevertSeedPdbMode was not set
catconRevertPdbModes: catcon_RevertUserPdbModes was not set

SQL> spool off
SQL> exit

●PDB上のJVM確認方法

sqlplus user/pass@TESTPDB
SQL> sho con_name

CON_NAME
---
TESTPDB

SQL> desc dbms_java -- で見つかる。
SQL> col comp_name for a40
SQL> col status for a10
SQL> select comp_name,status from dba_registry;

COMP_NAME STATUS
--- ---
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Real Application Clusters OPTION OFF
JServer JAVA Virtual Machine VALID
Oracle Database Java Packages VALID
Oracle XML Database VALID
Oracle Workspace Manager VALID

7行が選択されました。

●PDB上のJVM確認方法(PG動作)

◆V.java

public class V {
public static String gv()
{ return System.getProperty("java.version"); }
}

$ $ORACLE_HOME/jdk/bin/javac V.java

$ loadjava -user user/pass@TNS V.java
$ sqlplus user/pass@TNS
SQL> create function gv return varchar2
2 as language java
3 name 'V.gv() return java.lang.String'; ;

ファンクションが作成されました。

SQL> select gv() from dual;
--もしくは
SQL> var s varchar2(100);
SQL> call gv() into :s;

コールが完了しました。

SQL> print s;

STR
---
1.6.0_71

$ $ORACLE_HOME/jdk/bin/java -version
java version "1.6.0_75"
Java(TM) SE Runtime Environment (build 1.6.0_75-b13)
Java HotSpot(TM) 64-Bit Server VM (build 20.75-b01, mixed mode)

●JVM追加実行エラー対策

Can't locate loadable object for module Term::ReadKey in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 . /oracle/rdbms/admin/) at /oracle/rdbms/admin//catcon.pm line 303.

が出た場合、以下を事前に設定してから再試行。

$ PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
$ PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH