and another little new feature of oracle 12c: ON NULL for table columns definitions
SQL> !cat a.sql
CREATE TABLE T1 ( a number
, b varchar2(5) default on null 'aaaaa' not null );
insert into t1 ( a,b ) values ( 1,'aaaa');
insert into t1 ( a,b ) values ( 1,null);
select * from t1;
A B
---------- -----
1 aaaa
1 aaaaa
this is one of: after all these years I really didn’t know this
nested timers in sqlplus:
SYS@orcl> timing start a1 SYS@orcl> timing start a2 SYS@orcl> timing start a3 SYS@orcl> timing show timing for: a3 Elapsed: 00:00:03.39 SYS@orcl> timing stop timing for: a3 Elapsed: 00:00:07.37 SYS@orcl> timing show timing for: a2 Elapsed: 00:00:16.55 SYS@orcl> timing stop timing for: a2 Elapsed: 00:00:20.53 SYS@orcl> timing show timing for: a1 Elapsed: 00:00:33.45 SYS@orcl> timing stop timing for: a1 Elapsed: 00:00:36.44
good to know for complex scripts …
another nice features of oracle 12cR1: you may define functions within the sql with clause:
SQL> !cat a.sql
WITH
FUNCTION f_test ( a NUMBER) RETURN NUMBER
IS
BEGIN
return a * 2;
END;
select f_test (5) from dual;
/
SQL> @a
F_TEST(5)
----------
10
this is particulary useful when you are not allowed to created stored procedures or you are connected to a read only database.
The standard procedure for installing java in the oracle 12cR1 database is:
PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Tue Nov 12 01:25:39 2013 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> !perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initjvm /opt/oracle/product/base/12.1.0.1/javavm/install/initjvm.sql; !perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b initxml /opt/oracle/product/base/12.1.0.1/xdk/admin/initxml.sql; !perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b xmlja /opt/oracle/product/base/12.1.0.1/xdk/admin/xmlja.sql; !perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catjava /opt/oracle/product/base/12.1.0.1/rdbms/admin/catjava.sql; connect "SYS"/oracle as SYSDBA !perl /opt/oracle/product/base/12.1.0.1/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catxdbj /opt/oracle/product/base/12.1.0.1/rdbms/admin/catxdbj.sql;
having this done which jvm do we have available?
SQL> r
1* select comp_name,status,version from dba_registry
COMP_NAME STATUS VERSION
---------------------------------------- -------------- -----------
Oracle XML Database VALID 12.1.0.1.0
Oracle Database Catalog Views VALID 12.1.0.1.0
Oracle Database Packages and Types VALID 12.1.0.1.0
Oracle Real Application Clusters OPTION OFF 12.1.0.1.0
JServer JAVA Virtual Machine VALID 12.1.0.1.0
Oracle XDK VALID 12.1.0.1.0
Oracle Database Java Packages VALID 12.1.0.1.0
create or replace and compile java source named "props" as
public class props {
public static void show_props() {
System.getProperties().list(System.out);
}
}
/
show errors
create or replace procedure java_props
as language java name 'props.show_props()';
/
show errors
set serveroutput on size 10000
exec dbms_java.set_output(10000)
exec java_props
this will print the following output on my 12c instance:
-- listing properties -- oracle.aurora.ncomp.lib.permission= java.protocol.handler.pkgs=oracle.aurora.rdbms.url sun.boot.library.path=/opt/oracle/product/base/12.1.0.1/lib java.vm.version=1.6.0 oracle.aurora.ncomp.lib.component.prefix=jtc java.vm.vendor=Oracle Corporation java.vendor.url=http://www.oracle.com/java/ path.separator=: java.vm.name=JServer VM file.encoding.pkg=sun.io java.vm.specification.name=Java Virtual Machine Specification user.dir=/opt/oracle/product/base/12.1.0.1 java.awt.graphicsenv=oracle.aurora.awt.OracleGraphicsEnvir... os.arch=x86_64 java.io.tmpdir=/tmp line.separator= java.vm.specification.vendor=Sun Microsystems Inc. java.naming.factory.url.pkgs=com.sun.jndi.url os.name=Linux oracle.aurora.ncomp.file.obj.suffix=o java.library.path=/usr/lib:/opt/oracle/product/base/12.... java.specification.name=Java Platform API Specification java.class.version=50.0 java.net.preferIPv4Stack=FALSE oracle.aurora.ncomp.file.dll.suffix=so java.util.prefs.PreferencesFactory=java.util.prefs.OraclePreferencesFactory os.version=2.6.39-400.210.2.el6uek.x86_64 user.home= file.encoding=UTF-8 java.specification.version=1.6 oracle.aurora.ncomp.lib.os.prefix=lib user.name= java.class.path= oracle.aurora.rdbms.SID=dbs300 java.vm.specification.version=1.0 oracle.server.version=12.1.0.1.0 java.home=/opt/oracle/product/base/12.1.0.1/jav... java.specification.vendor=Sun Microsystems Inc. user.language=en oracle.aurora.rdbms.oracle_home=/opt/oracle/product/base/12.1.0.1 awt.toolkit=oracle.aurora.awt.OracleToolkit oracle.aurora.vm.environment.name=rdbms java.version=1.6.0 java.vendor=Oracle Corporation java.awt.headless=true file.separator=/ sqlj.runtime=sqlj.framework.ide.aurora.rdbms.Oracl... java.compiler= sun.cpu.endian=little sun.io.unicode.encoding=UnicodeLittle oracle.jserver.version=12.1.0.1.0 oracle.aurora.system_subdirectory=lib
so the instance is running a version 1.6.0 jvm. and now, new with oracle 12c, there is a way to upgrade the jvm to 1.7.0:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !perl $ORACLE_HOME/javavm/install/update_javavm_binaries.pl y Invalid version y ... Valid options are: 6 7 SQL> !perl $ORACLE_HOME/javavm/install/update_javavm_binaries.pl 7 SQL> startup ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2296576 bytes Variable Size 398460160 bytes Database Buffers 662700032 bytes Redo Buffers 5480448 bytes Database mounted. Database opened. SQL> alter pluggable database all open; Pluggable database altered. SQL> !perl -I $ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -b update_jvm $ORACLE_HOME/javavm/install/update_javavm_db.sql
did it really work?
SQL> set serveroutput on size 10000 SQL> exec dbms_java.set_output(10000); PL/SQL procedure successfully completed. SQL> exec java_props BEGIN java_props; END; * ERROR at line 1: ORA-29548: Java system class reported: release of classes.bin in the database does not match that of the oracle executable ORA-06512: at "SYS.JAVA_PROPS", line 1 ORA-06512: at line 1
ups? looking at the alertlog:
Tue Nov 12 03:32:11 2013 joxcsys: release mismatch 12.1.0.1.0 1.7 in database (classes.bin) vs 12.1.0.1.0 1.6 in executable Tue Nov 12 03:32:56 2013 joxcsys: release mismatch 12.1.0.1.0 1.7 in database (classes.bin) vs 12.1.0.1.0 1.6 in executable
so, relinking the binaries is not optional, as stated in the documenatation:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. !$ORACLE_HOME/bin/relink all writing relink log to: /opt/oracle/product/base/12.1.0.1//install/relink.log startup
here we go:
SQL> set serveroutput on size 10000 SQL> exec dbms_java.set_output(10000); PL/SQL procedure successfully completed. SQL> exec java_props -- listing properties -- oracle.aurora.ncomp.lib.permission= java.protocol.handler.pkgs=oracle.aurora.rdbms.url sun.boot.library.path=/opt/oracle/product/base/12.1.0.1/lib java.vm.version=1.7.0 oracle.aurora.ncomp.lib.component.prefix=jtc java.vm.vendor=Oracle Corporation java.vendor.url=http://www.oracle.com/java/ path.separator=: java.vm.name=JServer VM file.encoding.pkg=sun.io java.vm.specification.name=Java Virtual Machine Specification user.dir=/opt/oracle/product/base/12.1.0.1 java.awt.graphicsenv=oracle.aurora.awt.OracleGraphicsEnvir... os.arch=x86_64 java.io.tmpdir=/tmp line.separator= java.vm.specification.vendor=Sun Microsystems Inc. java.naming.factory.url.pkgs=com.sun.jndi.url os.name=Linux oracle.aurora.ncomp.file.obj.suffix=o java.library.path=/usr/lib:/opt/oracle/product/base/12.... java.specification.name=Java Platform API Specification java.class.version=51.0 java.net.preferIPv4Stack=FALSE oracle.aurora.ncomp.file.dll.suffix=so java.util.prefs.PreferencesFactory=java.util.prefs.OraclePreferencesFactory os.version=2.6.39-400.210.2.el6uek.x86_64 user.home= file.encoding=UTF-8 java.specification.version=1.7 oracle.aurora.ncomp.lib.os.prefix=lib user.name= java.class.path= oracle.aurora.rdbms.SID=dbs300 java.vm.specification.version=1.0 oracle.server.version=12.1.0.1.0 java.home=/opt/oracle/product/base/12.1.0.1/jav... java.specification.vendor=Sun Microsystems Inc. user.language=en oracle.aurora.rdbms.oracle_home=/opt/oracle/product/base/12.1.0.1 awt.toolkit=oracle.aurora.awt.OracleToolkit oracle.aurora.vm.environment.name=rdbms java.version=1.7.0 java.vendor=Oracle Corporation java.awt.headless=true file.separator=/ sqlj.runtime=sqlj.framework.ide.aurora.rdbms.Oracl... java.compiler= sun.cpu.endian=little sun.io.unicode.encoding=UnicodeLittle oracle.jserver.version=12.1.0.1.0 oracle.aurora.system_subdirectory=lib PL/SQL procedure successfully completed.
as soon as one wants to use encryption oracle needs a wallet to be created. when using orapki to create the wallet there is an option “-auto_login” that might be used to enable the database to auto open the wallet with each startup. there seems to be a misunderstanding that you might not change this after the wallet was created. let’s see:
orapki wallet create -wallet . -pwd "blabla1234" Oracle PKI Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. dbs300@oel6.lcsys.ch lcsdb:/oradata/dbs300/wallet $ ls -la total 12 drwxr-x--- 2 lcsdb lcsi 4096 11. Nov 22:54 ./ drwxr-x--- 12 lcsdb lcsi 4096 11. Nov 16:03 ../ -rw------- 1 lcsdb lcsi 2856 11. Nov 22:54 ewallet.p12 -rw------- 1 lcsdb lcsi 0 11. Nov 22:54 ewallet.p12.lck
this created the wallet without the “-auto_login” option, thus requires us to provide the wallet password with each database restart. letting orapki print its help:
oracle uses linux mintorapki wallet -h
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
Invalid command: -h
wallet:
create [-wallet [wallet]] [[-pwd ] [-auto_login|-auto_login_local]] | [-auto_login_only]
display [-wallet [wallet]] [-pwd ]
change_pwd [-wallet [wallet]] [-oldpwd ] [-newpwd ]
add [-wallet [wallet]]
<-self_signed [-validity [days]] | [-valid_from [mm/dd/yyyy] -valid_until [mm/dd/yyyy]]
[-serial_file ] | [-serial_num ]>
[-pwd ] | [-auto_login_only]
[-sign_alg ]
remove [-wallet [wallet]] [-dn [dn]] [-trusted_cert_all|-trusted_cert|-user_cert|-cert_req]
[-pwd ] | [-auto_login_only]
export [-wallet [wallet]] [-dn [dn]] [-cert [filename] | -request [filename]] [-pwd ]
export_trust_chain [-wallet [wallet]] [-certchain [filename]] [-dn [user_cert_dn]] [-pwd ]
upload [-wallet [wallet]] [-ldap [host:port]] [-user [user]] [-userpwd [userpwd]] [-pwd ]
download [-wallet [wallet]] [-ldap [host:nonsslport]] [-user [user]] [-userpwd [userpwd]] [-pwd ]
jks_to_pkcs12 [-wallet [wallet]] [-pwd ] [-keystore [keystore]] [-jkspwd [jkspwd]]
pkcs12_to_jks [-wallet [wallet]] [-pwd ] [-jksKeyStoreLoc -jksKeyStorepwd ]
[-jksTrustStoreLoc -jksTrustStorepwd ]
p11_add [-wallet [wallet]] [-p11_lib ] [-p11_tokenlabel ]
[-p11_tokenpw ] [-p11_certlabel ] [-pwd ]
p11_verify [-wallet [wallet]] [-pwd ]
help
it really seems that you can only provide the “-auto_login” option while creating the wallet but not after that point. well, this is an example of misleading syntax as you actually can change the option with “orapki wallet create” without destroying the original wallet:
orapki wallet create -wallet . -pwd "blabla1234" -auto_login Oracle PKI Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. dbs300@oel6.lcsys.ch lcsdb:/oradata/dbs300/wallet $ ls -la total 16 drwxr-x--- 2 lcsdb lcsi 4096 11. Nov 22:55 ./ drwxr-x--- 12 lcsdb lcsi 4096 11. Nov 16:03 ../ -rw------- 1 lcsdb lcsi 2901 11. Nov 22:55 cwallet.sso -rw------- 1 lcsdb lcsi 0 11. Nov 22:55 cwallet.sso.lck -rw------- 1 lcsdb lcsi 2856 11. Nov 22:54 ewallet.p12 -rw------- 1 lcsdb lcsi 0 11. Nov 22:54 ewallet.p12.lck
a better orapki help or a better syntax might not be too bad :)
mike dietrich added a counter for the end of premium support to his blog:
https://blogs.oracle.com/UPGRADE/entry/did_you_know
time to prepare …
from time to time I am testing different flavors of linux on my workstation/laptop. this time it is fedora 20 (kde edition). tried to install oracle 12c with my simple script and ups:
****** *** creating database SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 2 17:19:07 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-12547: TNS:lost contact
the script does not work anymore. what happened? the problem is with the linking phase, reproducable by:
[oracle@localhost ~]$ /opt/oracle/product/base/12.1.0.1/bin/relink all writing relink log to: /opt/oracle/product/base/12.1.0.1/install/relink.log tail -100 /opt/oracle/product/base/12.1.0.1/install/relink.log ..... /usr/bin/ld: /opt/oracle/product/base/12.1.0.1/lib//libnls12.a(lxhclrs.o): undefined reference to symbol '__tls_get_addr@@GLIBC_2.3' /usr/bin/ld: note: '__tls_get_addr@@GLIBC_2.3' is defined in DSO /lib64/ld-linux-x86-64.so.2 so try adding it to the linker command line /lib64/ld-linux-x86-64.so.2: could not read symbols: Invalid operation collect2: error: ld returned 1 exit status make: *** [/opt/oracle/product/base/12.1.0.1/rdbms/lib/rman] Error 1 Error in invoking target 'irman ioracle' of makefile '/opt/oracle/product/base/12.1.0.1/rdbms/lib/ins_rdbms.mk'. See '/opt/oracle/product/base/12.1.0.1/install/relinkActions2014-01-02_05-24-05-PM.log' for details.
asking my favorite search engine gave the right answer
so, here is an updated version of the script which works for fedora20 ( keep in mind that fedora is not supported ):
#!/bin/bash
##################################################
# CONFIGURATION SECTION #
##################################################
# ** location of the database source files
SOURCEPATH=/home/dwe/Downloads
# ** name of the first source file
SOURCE1=linuxamd64_12c_database_1of2.zip
# ** name of the second source file
SOURCE2=linuxamd64_12c_database_2of2.zip
# ** working directory for extracting the source
WORKDIR=/opt/oracle/stage
# ** the oracle top directory
ORATOPDIR=/opt/oracle
# ** the oracle inventory
ORAINVDIR=${ORATOPDIR}/oraInventory
# ** the ORACLE_BASE to use
ORACLE_BASE=${ORATOPDIR}/product/base
# ** the ORACLE_HOME to use
ORACLE_HOME=${ORACLE_BASE}/12.1.0.1
# ** base directory for the oracle database files
ORABASEDIR=/oradata
# the ORACLE_SID to use
ORACLE_SID=orcl
# ** the owner of the oracle software
ORAOWNER=oracle
# ** the primary installation group
ORAINSTGROUP=oinstall
# ** the dba group
ORADBAGROUP=dba
# ** the oper group
ORAOPERGROUP=oper
# ** the backup dba group
ORABACKUPDBA=backupdba
# ** the dataguard dba group
ORADGBAGROUP=dgdba
# ** the transparent data encryption group
ORAKMBAGROUP=kmdba
##################################################
# MAIN SECTION #
##################################################
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
# print the header
_header() {
echo "*** ---------------------------- ***"
echo "*** -- starting oracle 12c setup ***"
echo "*** ---------------------------- ***"
}
# print simple log messages to screen
_log() {
echo "****** $1 "
}
# check for the current os user
_check_user() {
if [ $(id -un) != "${1}" ]; then
_log "you must run this as ${1}"
exit 0
fi
}
# create the user and the groups
_create_user_and_groups() {
_log "*** checking for group: ${ORAINSTGROUP} "
getent group ${ORAINSTGROUP}
if [ "$?" -ne "0" ]; then
/usr/sbin/groupadd ${ORAINSTGROUP} 2> /dev/null || :
fi
_log "*** checking for group: ${ORADBAGROUP} "
getent group ${ORADBAGROUP}
if [ "$?" -ne "0" ]; then
/usr/sbin/groupadd ${ORADBAGROUP} 2> /dev/null || :
fi
_log "*** checking for group: ${ORAOPERGROUP} "
getent group ${ORAOPERGROUP}
if [ "$?" -ne "0" ]; then
/usr/sbin/groupadd ${ORAOPERGROUP} 2> /dev/null || :
fi
_log "*** checking for group: ${ORABACKUPDBA} "
getent group ${ORABACKUPDBA}
if [ "$?" -ne "0" ]; then
/usr/sbin/groupadd ${ORABACKUPDBA} 2> /dev/null || :
fi
_log "*** checking for group: ${ORADGBAGROUP} "
getent group ${ORADGBAGROUP}
if [ "$?" -ne "0" ]; then
/usr/sbin/groupadd ${ORADGBAGROUP} 2> /dev/null || :
fi
_log "*** checking for group: ${ORAKMBAGROUP} "
getent group ${ORAKMBAGROUP}
if [ "$?" -ne "0" ]; then
/usr/sbin/groupadd ${ORAKMBAGROUP} 2> /dev/null || :
fi
_log "*** checking for user: ${ORAOWNER} "
getent passwd ${ORAOWNER}
if [ "$?" -ne "0" ]; then
/usr/sbin/useradd -g ${ORAINSTGROUP} -G ${ORADBAGROUP},${ORAOPERGROUP},${ORABACKUPDBA},${ORADGBAGROUP},${ORAKMBAGROUP} \
-c "oracle software owner" -m -d /home/${ORAOWNER} -s /bin/bash ${ORAOWNER}
fi
}
# create the directories
_create_dirs() {
_log "*** creating: ${WORKDIR} "
mkdir -p ${WORKDIR}
chown ${ORAOWNER}:${ORAINSTGROUP} ${WORKDIR}
_log "*** creating: ${ORATOPDIR} "
mkdir -p ${ORATOPDIR}
chown ${ORAOWNER}:${ORAINSTGROUP} ${ORATOPDIR}
_log "*** creating: ${ORACLE_BASE} "
mkdir -p ${ORACLE_BASE}
chown ${ORAOWNER}:${ORAINSTGROUP} ${ORACLE_BASE}
_log "*** creating: ${ORACLE_HOME} "
mkdir -p ${ORACLE_HOME}
chown ${ORAOWNER}:${ORAINSTGROUP} ${ORACLE_HOME}
_log "*** creating: ${ORABASEDIR} "
mkdir -p ${ORABASEDIR}
chown ${ORAOWNER}:${ORAINSTGROUP} ${ORABASEDIR}
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID} "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}
chown ${ORAOWNER}:${ORAINSTGROUP} ${ORABASEDIR}/${ORACLE_SID}
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/rdo1 "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/rdo1
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/rdo2 "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/rdo2
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/dbf "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/dbf
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/arch "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/arch
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/admin "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/admin
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/admin/adump "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/admin/adump
_log "*** creating: ${ORABASEDIR}/${ORACLE_SID}/pdbseed "
mkdir -p ${ORABASEDIR}/${ORACLE_SID}/pdbseed
chown -R ${ORAOWNER}:${ORADBAGROUP} ${ORABASEDIR}/${ORACLE_SID}
}
# extract the source files
_extract_sources() {
cp ${SOURCEPATH}/${SOURCE1} ${WORKDIR}
cp ${SOURCEPATH}/${SOURCE2} ${WORKDIR}
chown ${ORAOWNER}:${ORAINSTGROUP} ${WORKDIR}/*
_log "*** extracting: ${SOURCE1} "
su - ${ORAOWNER} -c "unzip -d ${WORKDIR} ${WORKDIR}/${SOURCE1}"
_log "*** extracting: ${SOURCE2} "
su - ${ORAOWNER} -c "unzip -d ${WORKDIR} ${WORKDIR}/${SOURCE2}"
}
# install required software
_install_required_software() {
_log "*** installing required software "
yum install -y binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh \
libgcc libstdc++ libstdc++-devel libaio libaio-devel libXext libXtst libX11 libXau libxcb libXi make sysstat
}
# install oracle software
_install_oracle_software() {
_log "*** installing oracle software"
su - ${ORAOWNER} -c "cd ${WORKDIR}/database; ./runInstaller oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_BASE=${ORACLE_BASE} \
ORACLE_HOME=${ORACLE_HOME} \
UNIX_GROUP_NAME=${ORAINSTGROUP} \
oracle.install.db.DBA_GROUP=${ORADBAGROUP} \
oracle.install.db.OPER_GROUP=${ORAOPERGROUP} \
oracle.install.db.BACKUPDBA_GROUP=${ORABACKUPDBA} \
oracle.install.db.DGDBA_GROUP=${ORADGBAGROUP} \
oracle.install.db.KMDBA_GROUP=${ORAKMBAGROUP} \
FROM_LOCATION=../stage/products.xml \
INVENTORY_LOCATION=${ORAINVDIR} \
SELECTED_LANGUAGES=en \
oracle.install.db.InstallEdition=EE \
DECLINE_SECURITY_UPDATES=true -silent -ignoreSysPrereqs -ignorePrereq -waitForCompletion"
${ORAINVDIR}/orainstRoot.sh
${ORACLE_HOME}/root.sh
}
# create a very minimal pfile
_create_pfile() {
_log "*** creating pfile "
echo "instance_name=${ORACLE_SID}" > ${PFILE}
echo "db_name=${ORACLE_SID}" >> ${PFILE}
echo "db_block_size=8192" >> ${PFILE}
echo "control_files=${ORABASEDIR}/${ORACLE_SID}/rdo1/control01.ctl,${ORABASEDIR}/${ORACLE_SID}/rdo2/control02.ctl" >> ${PFILE}
echo "sga_max_size=512m" >> ${PFILE}
echo "sga_target=512m" >> ${PFILE}
echo "diagnostic_dest=${ORABASEDIR}/${ORACLE_SID}/admin" >> ${PFILE}
echo "audit_file_dest=${ORABASEDIR}/${ORACLE_SID}/admin/adump" >> ${PFILE}
echo "enable_pluggable_database=true" >> ${PFILE}
}
# create the database
_create_database() {
_log "*** creating database "
# escaping the dollar seems not to work in EOF
echo "alter pluggable database pdb\$seed close;" > ${ORABASEDIR}/${ORACLE_SID}/admin/seedhack.sql
echo "alter pluggable database pdb\$seed open;" >> ${ORABASEDIR}/${ORACLE_SID}/admin/seedhack.sql
su - ${ORAOWNER} -c "export ORACLE_HOME=${ORACLE_HOME};export LD_LIBRARY_PATH=${LD_LIBRARY_PATH};export PATH=${ORACLE_HOME}/bin:${PATH};export ORACLE_SID=${ORACLE_SID};export PERL5LIB=${ORACLE_HOME}/rdbms/admin; sqlplus / as sysdba <> /home/${ORAOWNER}/.bash_profile
echo "ORACLE_HOME=${ORACLE_HOME}" >> /home/${ORAOWNER}/.bash_profile
echo "ORACLE_SID=${ORACLE_SID}" >> /home/${ORAOWNER}/.bash_profile
echo "LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}" >> /home/${ORAOWNER}/.bash_profile
echo "PATH=${ORACLE_HOME}/bin:${PATH}" >> /home/${ORAOWNER}/.bash_profile
echo "export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH" >> /home/${ORAOWNER}/.bash_profile
}
# fix for fedora 20
_fix_fedora_20() {
is_20=`cat /etc/fedora-release | grep Heisenbug`
if [ $? -eq "0" ]; then
_log "running on fedora 20 -> applying fix"
rm -f $ORACLE_HOME/rdbms/lib/config.o
mv $ORACLE_HOME/lib/stubs $ORACLE_HOME/lib/stubs_bak
sed 's/LINKTTLIBS\=\$(LLIBCLNTSH) \$(ORACLETTLIBS) \$(LINKLDLIBS)/LINKTTLIBS\=\$(LLIBCLNTSH) \$(ORACLETTLIBS) \$(LINKLDLIBS) -lons/' $ORACLE_HOME/rdbms/lib/env_rdbms.mk > /tmp/env_rdbms.mk
cp /tmp/env_rdbms.mk $ORACLE_HOME/rdbms/lib/env_rdbms.mk
sed 's/LINK\=\$(FORT_CMD) \$(PURECMDS) \$(ORALD) \$(LDFLAGS) \$(COMPSOBJS)/LINK\=\$(FORT_CMD) \$(PURECMDS) \$(ORALD) \$(LDFLAGS) \$(COMPSOBJS) -Wl,--no-as-needed/' $ORACLE_HOME/rdbms/lib/env_rdbms.mk > /tmp/env_rdbms.mk
cp /tmp/env_rdbms.mk $ORACLE_HOME/rdbms/lib/env_rdbms.mk
sed 's/LINK32\=\$(FORT_CMD) \$(PURECMDS) \$(ORALD) \$(LDFLAGS32) \$(COMPSOBJS)/LINK32\=\$(FORT_CMD) \$(PURECMDS) \$(ORALD) \$(LDFLAGS32) \$(COMPSOBJS) -Wl,--no-as-needed/' $ORACLE_HOME/rdbms/lib/env_rdbms.mk > /tmp/env_rdbms.mk
cp /tmp/env_rdbms.mk $ORACLE_HOME/rdbms/lib/env_rdbms.mk
su - ${ORAOWNER} -c "export ORACLE_HOME=${ORACLE_HOME}; $ORACLE_HOME/bin/relink all"
fi
}
_header
_check_user "root"
_create_user_and_groups
_create_dirs
_install_required_software
_extract_sources
_install_oracle_software
######## fedora 20 fix
_fix_fedora_20
######################
_create_pfile
_create_database
_create_env
oracle 12c introduced the sysbackup user to further separate roles. this should allow you to make backups without seeing the actual data. a simple test case:
SQL> create user test identified by test; User created. SQL> alter user test quota unlimited on PBD1; User altered. SQL> create table test.t1 ( a number, b varchar2(5) ); Table created. SQL> insert into test.t1 values (1,'aaaaa'); 1 row created. SQL> insert into test.t1 values (2,'bbbbb'); 1 row created. SQL> commit; Commit complete.
so, one table and two columns. according to the documentation I should not be able to see this data when connected as sysbackup:
oracle@localhost trace]$ sqlplus sysbackup/admin@pdb1 as SYSBACKUP
SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 24 09:47:26 2013
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 * from test.t1;
select * from test.t1
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
ok, seems to work. but wait:
SQL> select rowid from test.t1;
ROWID
------------------
AAAEseAALAAAACFAAA
AAAEseAALAAAACFAAB
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER('AAAEseAALAAAACFAAA') from dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAESEAALAAAACFAAA')
---------------------------------------------------
133
SQL> select file_id from dba_data_files where file_name like '%pdb1_01%';
FILE_ID
----------
11
now that I know the block- and filenumber let’s see if I may dump the block:
SQL> show user USER is "SYSBACKUP" SQL> alter system dump datafile 11 block 133; System altered. SQL>
hmm. this is nothing you want a backup user to be able to do:
[oracle@localhost trace]$ cat orcl_ora_4748.trc
Trace file /oradata/orcl/admin/diag/rdbms/orcl/orcl/trace/orcl_ora_4748.trc
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_HOME = /opt/oracle/product/base/12.1.0.1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.32-358.23.2.el6.x86_64
Version: #1 SMP Wed Oct 16 18:37:12 UTC 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 4748, image: oracle@localhost.localdomain
*** 2013-11-24 09:50:47.946
*** SESSION ID:(162.25) 2013-11-24 09:50:47.946
*** CLIENT ID:() 2013-11-24 09:50:47.946
*** SERVICE NAME:(pdb1) 2013-11-24 09:50:47.946
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2013-11-24 09:50:47.946
*** ACTION NAME:() 2013-11-24 09:50:47.946
*** CONTAINER ID:(3) 2013-11-24 09:50:47.946
Start dump data blocks tsn: 3 file#:11 minblk 133 maxblk 133
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=3 rdba=46137477
BH (0x6affa418) file#: 11 rdba: 0x02c00085 (11/133) class: 1 ba: 0x6afa2000
set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
dbwrid: 0 obj: 19230 objn: 19230 tsn: [3/3] afn: 11 hint: f
hash: [0x7e56ccf8,0x7e56ccf8] lru: [0x6affa648,0x6affa3c8]
obj-flags: object_ckpt_list
ckptq: [0x7e1aad28,0x6affb958] fileq: [0x7e1aae88,0x7e1aae88]
objq: [0x75049b18,0x75049b18] objaq: [0x6affa680,0x6affa400]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.ce0cd tch: 1
flags: buffer_dirty only_sequential_access
LRBA: [0x26.214.0] LSCN: [0x0.ce0cd] HSCN: [0x0.ce0cd] HSUB: [1]
Block dump from disk:
buffer tsn: 3 rdba: 0x02c00085 (11/133)
scn: 0x0.c9093 seq: 0x01 flg: 0x04 tail: 0x90930601
frmt: 0x02 chkval: 0xb280 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6F305E6800 to 0x00007F6F305E8800
7F6F305E6800 0000A206 02C00085 000C9093 04010000 [................]
7F6F305E6810 0000B280 00000001 00004B1E 000C908F [.........K......]
7F6F305E6820 00000000 00320002 02C00080 000E0007 [......2.........]
7F6F305E6830 0000023D 0140295E 000B0065 00000002 [=...^)@.e.......]
7F6F305E6840 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F6F305E6860 00000000 00020100 0016FFFF 1F6A1F80 [..............j.]
7F6F305E6870 00001F6A 1F8C0002 00001F80 00000000 [j...............]
7F6F305E6880 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
7F6F305E87E0 00000000 0202012C 620503C1 62626262 [....,......bbbbb]
7F6F305E87F0 0202012C 610502C1 61616161 90930601 [,......aaaaa....]
Block header dump: 0x02c00085
Object id on Block? Y
seg/obj: 0x4b1e csc: 0x00.c908f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2c00080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00e.0000023d 0x0140295e.0065.0b ---- 2 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02c00085
data_block_dump,data header at 0x7f6f305e6864
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f6f305e6864
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f80
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f8c
0x14:pri[1] offs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 5] 61 61 61 61 61
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 5] 62 62 62 62 62
end_of_block_dump
End dump data blocks tsn: 3 file#: 11 minblk 133 maxblk 133
you might say that a sysbackup user does not need to have access to the server and therefore might not see the contents of the dumpfile:
SQL> show user
USER is "SYSBACKUP"
SQL> show parameter background
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /oradata/orcl/admin/diag/rdbms
/orcl/orcl/trace
SQL> create or replace directory MY_DIR as '/oradata/orcl/admin/diag/rdbms/orcl/orcl/trace/';
Directory created.
set serverout on
declare
f_in utl_file.file_type;
s_in varchar2(10000);
string varchar2(32000);
begin
f_in := utl_file.fopen('MY_DIR','orcl_ora_4748.trc','R');
loop
begin
utl_file.get_line(f_in,s_in);
dbms_output.put_line (string);
string:= string || s_in;
end;
end loop;
utl_file.fclose(f_in);
end;
/
… here we go.
oracle 12c introduced common (valid for all containers) and local (valid for just a pdb) users. one might think that creating a common user and assigning privileges to that users results in the same set of privileges in all the pdbs. this is not the case:
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> create user c##1 identified by "test" container=all; User created. SQL> grant create session to c##1; Grant succeeded. SQL> connect c##1/test Connected. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> connect c##1/test@pdb1 ERROR: ORA-01045: user C##1 lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE.
so, either you’ll have to grant the privilege in each container you want the user to be able to do a specfic task or you’ll need to grant it for all containers:
SQL> grant create session to c##1 container=all; Grant succeeded. SQL> connect c##1@pdb1 Enter password: Connected. SQL>
what happens if a new pdb is created? do we need to re-grant the privilege for the new pdb?
SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY "test"
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE pbd1
DATAFILE '/oradata/orcl/pdb1/pdb2_01.dbf' SIZE 250M AUTOEXTEND ON
PATH_PREFIX = '/oradata/orcl/pdb2'
FILE_NAME_CONVERT = ('/oradata/orcl/pdbseed/', '/oradata/orcl/pdb2');
2 3 4 5 6
Pluggable database created.
SQL> SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> connect c##1/test@pdb2
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL>
no. once granted on the root level the privilege is available on all the current pdbs and all pdbs that might get created in the future. what will happen if you unplug a pdb and plug it to a container which does not have the commen user? needs to be tested …
Daniel Westermann's Blog 