Initialization Parameter Handling for Pluggable Databases in #Oracle 12c
In a Multitenant Database, the handling of initialization parameters has changed. This post shows some major differences. The playing field:
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 14 21:44:10 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID ------------------------------ ---------- ---------- PDB$SEED READ ONLY 2 PDB1 MOUNTED 3
Apart from the mandatory pluggable seed database, there is one pluggable database which is presently not opened. Can I modify initialization parameters for pdb1 now?
SQL> alter session set container=pdb1;
Session altered.
SQL> alter system set ddl_lock_timeout=30;
alter system set ddl_lock_timeout=30
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter system set ddl_lock_timeout=30 scope=spfile;
System altered.
In spite of the syntax, this did not modify the spfile:
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/spfilecdb1.ora
SQL> host strings /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilecdb1.ora
cdb1.__data_transfer_cache_size=0
cdb1.__db_cache_size=251658240
cdb1.__java_pool_size=4194304
cdb1.__large_pool_size=4194304
cdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cdb1.__pga_aggregate_target=167772160
cdb1.__sga_target=503316480
cdb1.__shared_io_pool_size=20971520
cdb1.__shared_pool_size=209715200
cdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cdb1/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracl
e/oradata/cdb1/control01.ctl','/u01/app/oracle/fast_recovery_area/cdb1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cdb1'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdb1XDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=160m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=480m
*.undo_tablesp
ace='UNDOTBS1'
Initialization parameters for pluggable databases are not kept in the spfile – if they are not inherited from the container database, that is. Instead, they are stored in a dictionary table of the container database. The documented view V$SYSTEM_PARAMETER displays them. But only if the pluggable database is opened:
SQL> connect / as sysdba Connected. SQL> select name,value,con_id from v$system_parameter where name='ddl_lock_timeout'; NAME VALUE CON_ID ------------------------------ ---------------------------------------- ------ ddl_lock_timeout 0 0 SQL> select a.name,value$,con_id from pdb_spfile$ a join v$pdbs b on (a.pdb_uid=b.con_uid); NAME VALUE$ CON_ID ------------------------------ ---------------------------------------- ------ ddl_lock_timeout 30 3
The undocumented table PDB_SPFILE$ is internally queried and the parameters are set accordingly when the pluggable database is opened:
SQL> alter pluggable database pdb1 open; Pluggable database altered. SQL> select name,value,con_id from v$system_parameter where name='ddl_lock_timeout'; NAME VALUE CON_ID ------------------------------ ---------------------------------------- ------ ddl_lock_timeout 0 0 ddl_lock_timeout 30 3
Attention, show parameter displays different results, depending on the current container now:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show parameter ddl_lock_timeout NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 SQL> alter session set container=pdb1; Session altered. SQL> show parameter ddl_lock_timeout NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 30
Upon unplugging the pluggable database, an xml file is generated, containing the description of the pluggable database. I thought that this file contains also the modified initialization parameters of that pluggable database, but that doesn’t seem to be the case. I checked it for the parameters DDL_LOCK_TIMEOUT and RESUMABLE_TIMEOUT and both do not show in the xml file. Instead, they got stored in the system tablespace of the unplugged database! Again, that is not documented and may change in the future.
Isn’t that fun to play with this exciting new stuff? At least I enjoyed it, and I hope you found it useful 🙂
#Oracle Database whoami for Multitenant
As an enhancement to the Oracle Database whoami for versions before 12c, this also shows the Container Name to which the session is connected:
[oracle@linuxbox ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 8 12:34:04 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> @whoami USER: SYS SESSION ID: 253 CURRENT_SCHEMA: SYS INSTANCE NAME: cdb1 CDB NAME: cdb1 CONTAINER NAME: CDB$ROOT DATABASE ROLE: PRIMARY OS USER: oracle CLIENT IP ADDRESS: SERVER HOSTNAME: linuxbox CLIENT HOSTNAME: linuxbox PL/SQL procedure successfully completed. SQL> connect system/oracle_4U@pdb1 Connected. SQL> @whoami USER: SYSTEM SESSION ID: 253 CURRENT_SCHEMA: SYSTEM INSTANCE NAME: cdb1 CDB NAME: cdb1 CONTAINER NAME: PDB1 DATABASE ROLE: PRIMARY OS USER: oracle CLIENT IP ADDRESS: 555.555.5.555 SERVER HOSTNAME: linuxbox CLIENT HOSTNAME: linuxbox PL/SQL procedure successfully completed.
The content of whoami.sql:
set serveroutput on
begin
dbms_output.put_line('USER: '||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID: '||sys_context('userenv','sid'));
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv','current_schema'));
dbms_output.put_line('INSTANCE NAME: '||sys_context('userenv','instance_name'));
dbms_output.put_line('CDB NAME: '||sys_context('userenv','cdb_name'));
dbms_output.put_line('CONTAINER NAME: '||sys_context('userenv','con_name'));
dbms_output.put_line('DATABASE ROLE: '||sys_context('userenv','database_role'));
dbms_output.put_line('OS USER: '||sys_context('userenv','os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv','ip_address'));
dbms_output.put_line('SERVER HOSTNAME: '||sys_context('userenv','server_host'));
dbms_output.put_line('CLIENT HOSTNAME: '||sys_context('userenv','host'));
end;
/
Shortcut to get the name of the current container is:
SQL> show con_name CON_NAME ------------------------------ PDB1
You may find that useful in a multitenant environment with many Pluggable Databases within one Container Database 🙂
Happy New Year 2014 to all of you!
My best wishes go out to you and your families – may the new year be a great one for you!
Special thanks to all the visitors of The Oracle Instructor – WordPress has crafted this Annual Report for 2013, if you’re interested 🙂
Pickleball spielen auf YouTube
Pickleball spielen 