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 …
if you are working on written requirements there is a need to exactly define the meaning of words. The The Internet Engineering Task Force (IETF) already did this for some keywords:
1. MUST This word, or the terms "REQUIRED" or "SHALL", mean that the definition is an absolute requirement of the specification. 2. MUST NOT This phrase, or the phrase "SHALL NOT", mean that the definition is an absolute prohibition of the specification. 3. SHOULD This word, or the adjective "RECOMMENDED", mean that there may exist valid reasons in particular circumstances to ignore a particular item, but the full implications must be understood and carefully weighed before choosing a different course. 4. SHOULD NOT This phrase, or the phrase "NOT RECOMMENDED" mean that there may exist valid reasons in particular circumstances when the particular behavior is acceptable or even useful, but the full implications should be understood and the case carefully weighed before implementing any behavior described with this label. 5. MAY This word, or the adjective "OPTIONAL", mean that an item is truly optional. One vendor may choose to include the item because a particular marketplace requires it or because the vendor feels that it enhances the product while another vendor may omit the same item. An implementation which does not include a particular option MUST be prepared to interoperate with another implementation which does include the option, though perhaps with reduced functionality. In the same vein an implementation which does include a particular option MUST be prepared to interoperate with another implementation which does not include the option (except, of course, for the feature the option provides.)
The whole document can be found here.
there is a nice link on planet.postgres.org to a picture showing the oracle architecture in a funny way
Thanks to Dinesh Kumar for spending work on this…
similar to tree there is a command to show all the mounts in a nice tree like output: findmnt
findmnt
TARGET SOURCE
/ /dev/disk/by-uuid/cb51d0c9-6cde-49c9-ae17-2ca37ecc2f7f
├─/sys sysfs
│ ├─/sys/fs/cgroup
│ │ └─/sys/fs/cgroup/systemd systemd
│ ├─/sys/fs/fuse/connections
│ ├─/sys/kernel/debug
│ ├─/sys/kernel/security
│ └─/sys/fs/pstore
├─/proc proc
│ └─/proc/sys/fs/binfmt_misc binfmt_misc
├─/dev udev
│ └─/dev/pts devpts
└─/run tmpfs
├─/run/lock
├─/run/shm
└─/run/user
└─/run/user/1000/gvfs gvfsd-fuse
Note: I omitted the last to columns to allow the output to fit on screen.
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
ever wanted to quickly display the directory structure and files in there? use tree:
~/VirtualBox VMs $ tree . ├── centOS_latest │ ├── centOS_latest.vbox │ ├── centOS_latest.vbox-prev │ ├── centOS_latest.vdi │ ├── Logs │ │ ├── VBox.log │ │ ├── VBox.log.1 │ │ ├── VBox.log.2 │ │ └── VBox.log.3 │ └── Snapshots │ └── 2013-12-13T09-27-06-012274000Z.sav ├── nohup.out ├── oel_latest │ ├── Logs │ │ ├── VBox.log │ │ ├── VBox.log.1 │ │ ├── VBox.log.2 │ │ └── VBox.log.3 │ ├── oel_latest.vbox │ ├── oel_latest.vbox-prev │ └── Snapshots │ └── 2013-08-19T13-58-33-388114000Z.sav ...
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 