Feed aggregator

system tablespace is full with audit files

Tom Kyte - Fri, 2018-08-31 14:26
<code></code>hi there, i have a problem here,my system tablespace is getting full these are current values for my system tablespace TABLESPACE_NAME FILE_NAME ALLOCATED_SIZE_IN_MB FREE_SIZE_IN_MB -------------...
Categories: DBA Blogs

mysql 8: installation on ubuntu

Dietrich Schroff - Fri, 2018-08-31 14:01
Today i tried to install mysql 8 on my ubuntu:
First i visited the site https://dev.mysql.com/downloads/repo/apt/ which says:
So i downloaded this file (https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb) and here we go:

# dpkg -i mysql-apt-config_0.8.10-1_all.deb 

Vormals nicht ausgewähltes Paket mysql-apt-config wird gewählt.

(Lese Datenbank ... 414911 Dateien und Verzeichnisse sind derzeit installiert.)

Vorbereitung zum Entpacken von mysql-apt-config_0.8.10-1_all.deb ...

Entpacken von mysql-apt-config (0.8.10-1) ...

mysql-apt-config (0.8.10-1) wird eingerichtet ...

Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)

OK
with:
root@zerberus:~/Downloads# apt-get update

OK:1 http://de.archive.ubuntu.com/ubuntu bionic InRelease

Holen:2 http://de.archive.ubuntu.com/ubuntu bionic-updates InRelease [88,7 kB]

Holen:3 http://security.ubuntu.com/ubuntu bionic-security InRelease [83,2 kB]

Holen:4 http://repo.mysql.com/apt/ubuntu bionic InRelease [16,9 kB]

Holen:5 http://de.archive.ubuntu.com/ubuntu bionic-backports InRelease [74,6 kB]               

Holen:6 http://de.archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [290 kB]

Holen:7 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 Sources [898 B]

Holen:8 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [147 kB]                    

Holen:9 http://de.archive.ubuntu.com/ubuntu bionic-updates/main i386 Packages [262 kB]                                            

Holen:10 http://de.archive.ubuntu.com/ubuntu bionic-updates/main Translation-en [111 kB]                                                  

Holen:11 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config amd64 Packages [568 B]                              

Holen:12 http://de.archive.ubuntu.com/ubuntu bionic-updates/main amd64 DEP-11 Metadata [138 kB]                               

Holen:13 http://de.archive.ubuntu.com/ubuntu bionic-updates/main DEP-11 48x48 Icons [31,4 kB]                           

Holen:14 http://de.archive.ubuntu.com/ubuntu bionic-updates/main DEP-11 64x64 Icons [53,7 kB]                        

Holen:15 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [161 kB]                         

Holen:16 http://security.ubuntu.com/ubuntu bionic-security/main i386 Packages [119 kB]                           

Holen:17 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe i386 Packages [161 kB]                      

Holen:18 http://security.ubuntu.com/ubuntu bionic-security/main Translation-en [57,4 kB]                                    

Holen:19 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config i386 Packages [568 B]                                           

Holen:20 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe Translation-en [73,6 kB]                                     

Holen:21 http://security.ubuntu.com/ubuntu bionic-security/main amd64 DEP-11 Metadata [204 B]                                            

Holen:22 http://security.ubuntu.com/ubuntu bionic-security/universe i386 Packages [50,8 kB]  

Holen:23 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 DEP-11 Metadata [152 kB]                      

Holen:24 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [50,9 kB]                                 

Holen:25 http://security.ubuntu.com/ubuntu bionic-security/universe Translation-en [29,4 kB]                                 

Holen:26 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 DEP-11 Metadata [5.792 B]                                

Holen:27 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe DEP-11 48x48 Icons [153 kB]                                    

Holen:28 http://security.ubuntu.com/ubuntu bionic-security/universe DEP-11 48x48 Icons [6.962 B]                                

Holen:29 http://security.ubuntu.com/ubuntu bionic-security/multiverse i386 Packages [1.608 B]                               

Holen:30 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 Packages [1.444 B]                           

Holen:31 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe DEP-11 64x64 Icons [262 kB]                   

Holen:32 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 i386 Packages [7.004 B]           

Holen:33 http://de.archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 Packages [3.772 B]      

Holen:34 http://de.archive.ubuntu.com/ubuntu bionic-updates/multiverse i386 Packages [3.928 B]

Holen:35 http://de.archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 DEP-11 Metadata [2.468 B]

Holen:36 http://de.archive.ubuntu.com/ubuntu bionic-backports/universe amd64 DEP-11 Metadata [5.100 B]

Holen:37 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 Packages [7.002 B]          

Holen:38 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 Packages [2.519 B]

Holen:39 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools i386 Packages [1.882 B]

Es wurden 2.617 kB in 1 s geholt (2.002 kB/s).

Paketlisten werden gelesen... Fertig

root@zerberus:~/Downloads# apt-get install mysql-server

Paketlisten werden gelesen... Fertig

Abhängigkeitsbaum wird aufgebaut.      

Statusinformationen werden eingelesen.... Fertig

Die folgenden Pakete wurden automatisch installiert und werden nicht mehr benötigt:

  btrfs-tools esound-common gnome-dictionary gnome-icon-theme-symbolic libarmadillo7 libaudiofile1 libboost-date-time1.62.0 libboost-filesystem1.62.0

  libboost-iostreams1.62.0 libboost-random1.62.0 libboost-regex1.62.0 libboost-serialization1.62.0 libboost-system1.62.0 libboost-thread1.62.0

  libcapnp-0.5.3 libcaribou-gtk-module libcaribou-gtk3-module libdevhelp-3-4 libesd0 libfabric1 libfolks-telepathy25 libgeos-3.5.1 libgit2-24 libgl2ps1

  libgl2ps1.4 libgnome-games-support-1-2 libhdf5-openmpi-100 libhttp-parser2.1 libhwloc-plugins libhwloc5 libical2 libiso9660-8 libjsoncpp1

  libjsonrpc-glib-1.0-0 liblept5 liblivemedia58 libllvm5.0 liblouis12 liblouisutdml7 liblttng-ust-ctl2 libmirclient-dev libmircommon-dev libmircookie-dev

  libmircookie2 libmircore-dev libnetcdf-c++4 libnetcdf11 libntfs-3g872 libopencv-core3.1 libopencv-flann3.1 libopencv-imgproc3.1 libopencv-ml3.1

  libopencv-photo3.1 libopencv-shape3.1 libopencv-video3.1 libopencv-viz3.1 libopenmpi2 liborcus-0.12-0 libpoppler68 libprocess-cpp3 libprotobuf-dev

  libpsm-infinipath1 libqpdf18 librpm3 librpmbuild3 librpmio3 librpmsign3 libruby2.3 libsodium18 libsrtp0 libsuitesparseconfig4 libtbb2 libtesseract-data

  libtesseract3 libunity-api0 libva-drm1 libva-wayland1 libva-x11-1 libva1 libvlccore8 libvpx4 libvtk6.3 libx264-148 libx265-130 libxerces-c3.1

  libxkbcommon-dev libzmqpp4 openmpi-bin openmpi-common python-gi python3-libarchive-c python3-libnacl ruby2.3 snapd-login-service

Verwenden Sie »apt autoremove«, um sie zu entfernen.

Die folgenden zusätzlichen Pakete werden installiert:

  libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server

  mysql-community-server-core

Die folgenden NEUEN Pakete werden installiert:

  libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client mysql-community-client mysql-community-client-core mysql-community-server

  mysql-community-server-core mysql-server

Die folgenden Pakete werden aktualisiert (Upgrade):

  mysql-common

1 aktualisiert, 10 neu installiert, 0 zu entfernen und 36 nicht aktualisiert.

Es müssen 54,2 MB an Archiven heruntergeladen werden.

Nach dieser Operation werden 414 MB Plattenplatz zusätzlich benutzt.

Möchten Sie fortfahren? [J/n]

Holen:1 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 libmecab2 amd64 0.996-5 [257 kB]

Holen:2 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-common amd64 8.0.12-1ubuntu18.04 [78,6 kB]

Holen:3 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 mecab-utils amd64 0.996-5 [4.856 B]

Holen:4 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 mecab-ipadic all 2.7.0-20070801+main-1 [12,1 MB]

Holen:5 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-client-core amd64 8.0.12-1ubuntu18.04 [1.435 kB]

Holen:6 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 mecab-ipadic-utf8 all 2.7.0-20070801+main-1 [3.522 B]

Holen:7 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-client amd64 8.0.12-1ubuntu18.04 [2.292 kB]

Holen:8 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-client amd64 8.0.12-1ubuntu18.04 [75,7 kB]

Holen:9 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-server-core amd64 8.0.12-1ubuntu18.04 [16,9 MB]

Holen:10 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-server amd64 8.0.12-1ubuntu18.04 [21,0 MB]

Holen:11 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-server amd64 8.0.12-1ubuntu18.04 [75,7 kB]                                          

Es wurden 54,2 MB in 10 s geholt (5.396 kB/s).                                                                                                             

Vorkonfiguration der Pakete ...

(Lese Datenbank ... 414916 Dateien und Verzeichnisse sind derzeit installiert.)

Vorbereitung zum Entpacken von .../00-mysql-common_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-common (8.0.12-1ubuntu18.04) über (5.8+1.0.4) ...

Vormals nicht ausgewähltes Paket mysql-community-client-core wird gewählt.

Vorbereitung zum Entpacken von .../01-mysql-community-client-core_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-client-core (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mysql-community-client wird gewählt.

Vorbereitung zum Entpacken von .../02-mysql-community-client_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-client (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mysql-client wird gewählt.

Vorbereitung zum Entpacken von .../03-mysql-client_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-client (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket libmecab2:amd64 wird gewählt.

Vorbereitung zum Entpacken von .../04-libmecab2_0.996-5_amd64.deb ...

Entpacken von libmecab2:amd64 (0.996-5) ...

Vormals nicht ausgewähltes Paket mysql-community-server-core wird gewählt.

Vorbereitung zum Entpacken von .../05-mysql-community-server-core_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-server-core (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mysql-community-server wird gewählt.

Vorbereitung zum Entpacken von .../06-mysql-community-server_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-server (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mecab-utils wird gewählt.

Vorbereitung zum Entpacken von .../07-mecab-utils_0.996-5_amd64.deb ...

Entpacken von mecab-utils (0.996-5) ...

Vormals nicht ausgewähltes Paket mecab-ipadic wird gewählt.

Vorbereitung zum Entpacken von .../08-mecab-ipadic_2.7.0-20070801+main-1_all.deb ...

Entpacken von mecab-ipadic (2.7.0-20070801+main-1) ...

Vormals nicht ausgewähltes Paket mecab-ipadic-utf8 wird gewählt.

Vorbereitung zum Entpacken von .../09-mecab-ipadic-utf8_2.7.0-20070801+main-1_all.deb ...

Entpacken von mecab-ipadic-utf8 (2.7.0-20070801+main-1) ...

Vormals nicht ausgewähltes Paket mysql-server wird gewählt.

Vorbereitung zum Entpacken von .../10-mysql-server_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-server (8.0.12-1ubuntu18.04) ...

Trigger für ureadahead (0.100.0-20) werden verarbeitet ...

mysql-common (8.0.12-1ubuntu18.04) wird eingerichtet ...

Neue Version der Konfigurationsdatei /etc/mysql/conf.d/mysql.cnf wird installiert ...

Neue Version der Konfigurationsdatei /etc/mysql/my.cnf.fallback wird installiert ...

libmecab2:amd64 (0.996-5) wird eingerichtet ...

mysql-community-client-core (8.0.12-1ubuntu18.04) wird eingerichtet ...

mysql-community-server-core (8.0.12-1ubuntu18.04) wird eingerichtet ...

Trigger für libc-bin (2.27-3ubuntu1) werden verarbeitet ...

Trigger für systemd (237-3ubuntu10.3) werden verarbeitet ...

Trigger für man-db (2.8.3-2) werden verarbeitet ...

mecab-utils (0.996-5) wird eingerichtet ...

mysql-community-client (8.0.12-1ubuntu18.04) wird eingerichtet ...

mecab-ipadic (2.7.0-20070801+main-1) wird eingerichtet ...

Compiling IPA dictionary for Mecab.  This takes long time...

reading /usr/share/mecab/dic/ipadic/unk.def ... 40

emitting double-array: 100% |###########################################|

/usr/share/mecab/dic/ipadic/model.def is not found. skipped.

reading /usr/share/mecab/dic/ipadic/Noun.adverbal.csv ... 795

reading /usr/share/mecab/dic/ipadic/Adverb.csv ... 3032

reading /usr/share/mecab/dic/ipadic/Postp.csv ... 146

reading /usr/share/mecab/dic/ipadic/Suffix.csv ... 1393

reading /usr/share/mecab/dic/ipadic/Verb.csv ... 130750

reading /usr/share/mecab/dic/ipadic/Adnominal.csv ... 135

reading /usr/share/mecab/dic/ipadic/Prefix.csv ... 221

reading /usr/share/mecab/dic/ipadic/Noun.demonst.csv ... 120

reading /usr/share/mecab/dic/ipadic/Noun.csv ... 60477

reading /usr/share/mecab/dic/ipadic/Conjunction.csv ... 171

reading /usr/share/mecab/dic/ipadic/Noun.verbal.csv ... 12146

reading /usr/share/mecab/dic/ipadic/Noun.proper.csv ... 27327

reading /usr/share/mecab/dic/ipadic/Others.csv ... 2

reading /usr/share/mecab/dic/ipadic/Filler.csv ... 19

reading /usr/share/mecab/dic/ipadic/Noun.number.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.org.csv ... 16668

reading /usr/share/mecab/dic/ipadic/Symbol.csv ... 208

reading /usr/share/mecab/dic/ipadic/Noun.nai.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.adjv.csv ... 3328

reading /usr/share/mecab/dic/ipadic/Noun.place.csv ... 72999

reading /usr/share/mecab/dic/ipadic/Interjection.csv ... 252

reading /usr/share/mecab/dic/ipadic/Noun.name.csv ... 34202

reading /usr/share/mecab/dic/ipadic/Postp-col.csv ... 91

reading /usr/share/mecab/dic/ipadic/Noun.others.csv ... 151

reading /usr/share/mecab/dic/ipadic/Adj.csv ... 27210

reading /usr/share/mecab/dic/ipadic/Auxil.csv ... 199

emitting double-array: 100% |###########################################|

reading /usr/share/mecab/dic/ipadic/matrix.def ... 1316x1316

emitting matrix      : 100% |###########################################|



done!

update-alternatives: /var/lib/mecab/dic/ipadic wird verwendet, um /var/lib/mecab/dic/debian (mecab-dictionary) im automatischen Modus bereitzustellen

mysql-client (8.0.12-1ubuntu18.04) wird eingerichtet ...

mecab-ipadic-utf8 (2.7.0-20070801+main-1) wird eingerichtet ...

Compiling IPA dictionary for Mecab.  This takes long time...

reading /usr/share/mecab/dic/ipadic/unk.def ... 40

emitting double-array: 100% |###########################################|

/usr/share/mecab/dic/ipadic/model.def is not found. skipped.

reading /usr/share/mecab/dic/ipadic/Noun.adverbal.csv ... 795

reading /usr/share/mecab/dic/ipadic/Adverb.csv ... 3032

reading /usr/share/mecab/dic/ipadic/Postp.csv ... 146

reading /usr/share/mecab/dic/ipadic/Suffix.csv ... 1393

reading /usr/share/mecab/dic/ipadic/Verb.csv ... 130750

reading /usr/share/mecab/dic/ipadic/Adnominal.csv ... 135

reading /usr/share/mecab/dic/ipadic/Prefix.csv ... 221

reading /usr/share/mecab/dic/ipadic/Noun.demonst.csv ... 120

reading /usr/share/mecab/dic/ipadic/Noun.csv ... 60477

reading /usr/share/mecab/dic/ipadic/Conjunction.csv ... 171

reading /usr/share/mecab/dic/ipadic/Noun.verbal.csv ... 12146

reading /usr/share/mecab/dic/ipadic/Noun.proper.csv ... 27327

reading /usr/share/mecab/dic/ipadic/Others.csv ... 2

reading /usr/share/mecab/dic/ipadic/Filler.csv ... 19

reading /usr/share/mecab/dic/ipadic/Noun.number.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.org.csv ... 16668

reading /usr/share/mecab/dic/ipadic/Symbol.csv ... 208

reading /usr/share/mecab/dic/ipadic/Noun.nai.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.adjv.csv ... 3328

reading /usr/share/mecab/dic/ipadic/Noun.place.csv ... 72999

reading /usr/share/mecab/dic/ipadic/Interjection.csv ... 252

reading /usr/share/mecab/dic/ipadic/Noun.name.csv ... 34202

reading /usr/share/mecab/dic/ipadic/Postp-col.csv ... 91

reading /usr/share/mecab/dic/ipadic/Noun.others.csv ... 151

reading /usr/share/mecab/dic/ipadic/Adj.csv ... 27210

reading /usr/share/mecab/dic/ipadic/Auxil.csv ... 199

emitting double-array: 100% |###########################################|

reading /usr/share/mecab/dic/ipadic/matrix.def ... 1316x1316

emitting matrix      : 100% |###########################################|



done!

update-alternatives: /var/lib/mecab/dic/ipadic-utf8 wird verwendet, um /var/lib/mecab/dic/debian (mecab-dictionary) im automatischen Modus bereitzustellen

mysql-community-server (8.0.12-1ubuntu18.04) wird eingerichtet ...

update-alternatives: /etc/mysql/mysql.cnf wird verwendet, um /etc/mysql/my.cnf (my.cnf) im automatischen Modus bereitzustellen

Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.

mysql-server (8.0.12-1ubuntu18.04) wird eingerichtet ...

Trigger für systemd (237-3ubuntu10.3) werden verarbeitet ...

Trigger für ureadahead (0.100.0-20) werden verarbeitet ...
And everything is ready:
mysql -u root -pmysupersecretpassword

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 8.0.12 MySQL Community Server - GPL



Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.



mysql>

SQL Server Tips: How many different datetime are in my column and what the delta?

Yann Neuhaus - Fri, 2018-08-31 08:27

Few months ago, a customer asks me to find in a column, how many I have of the same date & time in a column and what is the delta between these dates & times.
The column is based on the function CURRENT_TIMESTAMP and used as key.
I know, it’s not good to use it as a key but some developers are not competent to develop SQL correctly (no need to comments that!)…

This usage indicates a lot of duplicate keys and the customer want to know how many and the delta between each date &time.

To perform this task, I create a little example with a temporary table with one column with a datetime format:

CREATE TABLE [#tmp_time_count] (dt datetime not null)

I insert the CURRENT_TIMESTAMP in the table a thousand times to have data to play with:
INSERT INTO [#tmp_time_count] SELECT CURRENT_TIMESTAMP
Go 1000

To see how many different datetime I have, I need just to use DISCTINCT in a COUNT:

SELECT COUNT(DISTINCT dt) as [number_of_time_diff] from [#tmp_time_count]

datetime_diff_01
In my test, I find 36 different times for 1000 rows.
The question now is to know how many I have on the same date & time…
To have this information, I try a lot of thing but finally, I write this query with a LEFT JOIN on the same table and a DATEPART on the datetime’s column.

SELECT DISTINCT [current].dt as [Date&Time], DATEPART(MILLISECOND,ISNULL([next].dt,0) –[current].dt) as [time_diff] FROM [#tmp_time_count] as [current] LEFT JOIN [#tmp_time_count] as [next] on [next].dt = (SELECT MIN(dt) FROM [#tmp_time_count] WHERE dt >[current].dt)

datetime_diff_02
Don’t forget at the end to drop the table….

DROP TABLE [#tmp_time_count];

Et voila! I hope this little query can help you in a similar situation…

 

Cet article SQL Server Tips: How many different datetime are in my column and what the delta? est apparu en premier sur Blog dbi services.

Some Statistics on this Blog

Hemant K Chitale - Fri, 2018-08-31 00:04
This blog now has 630 posts (including this one), 1000 comments and 1.82million pageviews to date.



Categories: DBA Blogs

Group by displaying location according to custom sort

Tom Kyte - Thu, 2018-08-30 20:06
i have a table id name location <code> 1 aaa home 1 aaa village 1 aaa office 2. bbb village 2. bbb office 3. ccc home 3. ccc office</code> when i write a query i should get single record for each having precendence home>office>villag...
Categories: DBA Blogs

Find Existing Oracle Password Encryption

Tom Kyte - Thu, 2018-08-30 20:06
We currently run Oracle 10g. We have WebDB applications. The Webpage where users can change their passwords to access the WebDB applications no longer works. The only access I have to modify or unlock user access is the Oracle Enterprise Managemen...
Categories: DBA Blogs

utl_http work with 11g and not with 12c

Tom Kyte - Thu, 2018-08-30 20:06
Hi Team Is there any reason why unsecured connection with basic authentication (http://user:password@server_address) is working with 11g and not with 12c (12.2.0.1.0)? I am using utl_http package to connect from database servers to the same external...
Categories: DBA Blogs

[Oracle Text] Score(1) and Contains

Tom Kyte - Thu, 2018-08-30 20:06
Hey AskTom, the documentation for Oracle Text is very poor so I have a question: Is it best to use the CONTAINS() clause to find a certain result, or should we use score(1) for this operator? I mean such a case where we are interested in score ...
Categories: DBA Blogs

Question with 2 partition conntain 6 datafiles

Tom Kyte - Thu, 2018-08-30 20:06
HI Tom ? ? I have a Database of more than 250 tables. I worked 3 Tablespaces (TB1, TB2, TB3). TB1: Used to store 5GB data tables TB2: Used to store basic key index indexes for general tables and has a size of 2 GB TB3: Used to store fields cont...
Categories: DBA Blogs

The Partner Experience Oracle OpenWorld 2018 #OOW18

At Oracle OpenWorld you will be surrounded by the new, the cutting-edge, and the sometime unfamiliar. From today’s most-trusted products to tomorrow’s technologies, you will leave infused...

We share our skills to maximize your revenue!
Categories: DBA Blogs

[VLOG] IDAM ( OAM & OIM ) Installation Highlevel Overview

Online Apps DBA - Thu, 2018-08-30 07:30

Oracle Identity and Access Manager Administrators covers the complete day-to-day task of installing, configuring, and managing Oracle Access Manager and Oracle Identity Manager. This blog covers Highlevel Installation of IDAM (OAM/OIM) for an administrator. Do you want to learn more about OAM & IDAM Installation? Visit: https://k21academy.com/oam27 to learn more. Oracle Identity and Access Manager […]

The post [VLOG] IDAM ( OAM & OIM ) Installation Highlevel Overview appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Need a query to identify the number of selects, inserts, updates and deletes from an application table

Tom Kyte - Thu, 2018-08-30 01:46
Hi, One of our customer wants to know how to get the DDL DML count happened on application tables with timestamp details before 60 or 90 days. Please provide some Query to get these details. They dont want to enable Auditing, is that possib...
Categories: DBA Blogs

Mapping Key Value Pairs onto JSON Objects in Oracle Integration Cloud

Antony Reynolds - Wed, 2018-08-29 23:24

We were recently doing some work on a system, actually a Robotic Process Automation (RPA) endpoint, that generated unique JSON messages for each type of request.  So a single interface would expect different objects, depending on the request.  The target system actually required a small orchestration to submit a single request and so ideally a single integration would abstract the interface to that service.

To help reify things, here is an example:

Request One Create Order

This is the request to be sent to the generic service

{ "session" : "ABC123", "operation" : "createOrder", "data" : { "Customer" : "Antony", "Item" : "Stuffed Spinach Pizza" } }

Note that the data has named fields.

Request Two Get Order

This is another request to be sent to the same service, but a different operation results in a different payload.

{ "session" : "ABC123", "operation" : "getOrder", "data" : { "OrderID" : "112358", "FetchAllFields" : "True" } }

Note that the operation has changed and as a result the named fields in data are now different.

So even though the endpoint is the same, and the call preparation and tear down are the same, it appears we will have to have a unique integration for each type of request.  That sounds like a lot fo duplicate work :-(

The Problem

We want to have a generic interface to our target, but because it takes different data formats for different operations that means custom coding for each operation.  Within OIC we need to define the data shape so that when we map data we know what fields we are mapping.

Generic Interface Solution

Ideally we would like to have a single interface with a generic interface, something like the one below:

{ "session" : "ABC123", "operation" : "createOrder", "dictionary" : [ { "key" : "Customer", "value" : "Antony" }, { "key" : "Item", "value" : "Stuffed Spinach Pizza" } ] }

Note the use of key value pairs that allow us to pass arbitrary data into the integration.  The only problem is how do we map the data.  We need to create entries in the target data object corresponding to the keys in the source dictionary and then set those entries in the data object to the corresponding value from the dictionary.

Partial Solution

If we knew the target fields then we could use array indexing to select the correct value corresponding to the name as shown below:

Note that this is using the new JET based mapper which will shortly be available in Oracle Integration Cloud, it is currently in controlled availability.

Here we select the dictionary item whose "key" is Customer and puts its value into the Customer field.  This doesn't work if we don't know the field names as is the case for us!

This works if we know the target names when we build the integration and using the map above we can transform data as shown below

Source Target {    "session" : "ABC123",    "operation" : "createOrder",    "dictionary" : [      {        "key" : "Customer",        "value" : "Antony"      },      {        "key" : "Item",        "value" : "Stuffed Spinach Pizza"      }    ] } {     "session": "ABC123",     "operation": "createOrder",     "data": {         "Customer": "Antony"     } }

Unfortunately we don't always know the names ahead of time in which case this solution doesn't work.

A Generic Solution

So lets look at a more generic solution.  There is an XSL <element> tag that can be used to create an arbitrary element.  Unfortunately we have to use this in a hand crafted XSL as the mapper does not support the <element> tag - yet.

The process is:

  1. Map as much as you can using the mapper, including a single array lookup similar to the one above.
  2. Export your integration
  3. Unzip the integration
  4. Find your XSL map in the unzipped package (it will be in the folder icspackage/project/YOUR_INTEGRATION_NAME_VERSION/resources/processor_XX/resourcegroup_YY where XX and YY are arbitrary numbers)
  5. Edit the XSL map replacing the array mapping with the following:
    • Create a for loop over the base array element, the dictionary in our example
    • Create an element in the for-loop with name from key element and value from value element
      • <xsl:element name="{nsmpr0:key}"><xsl:value-of select="nsmpr0:value"/></xsl:element>
  6. Import the XSL into the integration

The mapping before and after looks like this

Before After <nsmpr0:response-wrapper xml:id="id_16">    <nsmpr0:session xml:id="id_17">       <xsl:value-of select="/nstrgmpr:execute/nsmpr0:request-wrapper/nsmpr0:session" xml:id="id_18"/>    </nsmpr0:session>    <nsmpr0:operation xml:id="id_19">       <xsl:value-of select="/nstrgmpr:execute/nsmpr0:request-wrapper/nsmpr0:operation" xml:id="id_20"/>    </nsmpr0:operation>    <nsmpr0:data xml:id="id_24">      <nsmpr0:Customer xml:id="id_25">         <xsl:value-of xml:id="id_26" select="/nstrgmpr:execute/nsmpr0:request-wrapper/nsmpr0:dictionary[nsmpr0:key = &quot;Customer&quot;]/nsmpr0:value"/>      </nsmpr0:Customer>   </nsmpr0:data> </nsmpr0:response-wrapper> <nsmpr0:response-wrapper xml:id="id_16">   <nsmpr0:session xml:id="id_17">      <xsl:value-of select="/nstrgmpr:execute/nsmpr0:request-wrapper/nsmpr0:session" xml:id="id_18"/>   </nsmpr0:session>   <nsmpr0:operation xml:id="id_19">      <xsl:value-of select="/nstrgmpr:execute/nsmpr0:request-wrapper/nsmpr0:operation" xml:id="id_20"/>   </nsmpr0:operation> <nsmpr0:data xml:id="id_24"> <xsl:for-each select="/nstrgmpr:execute/nsmpr0:request-wrapper/nsmpr0:dictionary">         <xsl:element name="{nsmpr0:key}">             <xsl:value-of select="nsmpr0:value"/>          </xsl:element>      </xsl:for-each>   </nsmpr0:data> </nsmpr0:response-wrapper>

Note that once imported into the integration you cannot edit it using the mapper in OIC.

JSon Note

The above example works fine because it is generating JSON and the XML/REST conversion in OIC does not pay attention to namespaces because there is no such construct in JSON.  If we wanted to do the same with XML output then we would nee to be more respectful of namespaces, although the element tag does support namespace specification.

Summary

We can deal with JSON data types that are unkown at design time by using Key Value pairs to dynamically construct the correct JSON objects.  This can be done in OIC and allows us to create generic integration wrappers to services that dynamically generate data types.

Dbvisit 8 Standby Daemon on Windows

Yann Neuhaus - Wed, 2018-08-29 13:55

In this previous blog, we have installed Dbvisit standby for Windows on both servers. We suppose that the database is created and that the standby is configured (see this blog). The steps are the same that on Linux except that the command is launched on a traditional DOS terminal or a PowerShell. We will not describe these steps (see here for more details).
Dbvisit by default will neither send nor apply the archived log files. With Windows we can use the Windows Scheduler or since Dbvisit 8 the Daemon.
To use Windows Scheduler we just remind that the command to be scheduled is

 dbvctl.exe –d PROD 

–dbvctl.exe is located in the Dbvisit install directory
–PROD is the name of the database

Since Dbvisit 8, it is no longer needed to use the Windows scheduler to send and to apply archived logs. We can use the new background process option where you can run Dbvisit Standby for each DDC (database) in the background. We can manage this via the Central Console.
When using Windows, the background process will run as a Windows Service. A Windows Service will be created on the primary and standby server for each database. Below an example of how to create a service for the database PROD
img1
And choose DATABASE ACTIONS TAB
img2
Choose Daemon Actions
img3
After when we select a host we will see the status of the daemon on this host. We are to create the Daemon using the INSTALL button.
img4
We provide the credentials of the user dbvisit. This user will be the owner of the service which be created for the Daemon. And let’s submit
img5
Everything is OK. If we click again on DAEMON ACTIONS and select the host winserver1 we will have
img6
And we can start the Daemon.
img7
The same steps have to be done on both servers.
We then should have a Windows service configured with an automatic startup.
img8
And now Dbvisit will send and apply archive logs as soon as they are generated.

 

Cet article Dbvisit 8 Standby Daemon on Windows est apparu en premier sur Blog dbi services.

Install Dbvisit 8 on Windows

Yann Neuhaus - Wed, 2018-08-29 13:52

In a previous blog we described how to install dbvisit standby in a Linux box. In this article I am going to describe the installation on a Windows machine. We are using dbvisit 8 and windows server 2016. The name of my servers are winserver1 and winserver2.
The first thing you will have to do is to download dbvisit standby package here . A trial key will be sent to you. Before starting the installation we create a user named dbvisit (feel free to change) with following properties:
img1
The user dbvisit need also privilege to logon as a service.
img2
The installation is very easy, just log with a privileged user and run the executable. Below is the installation on the server winserver2. Note that dbvisit standby need to be installed on both servers. Note that we also have turned off the WIndows User Account Control (UAC).

Dbvnet,Dbvagent and Standby Cli components install

img3
Click on Next
img4
Click on I Agree (anyway we don’t have the choice)
img5
Choose the components to install. Note that on a first time the central console is not installed. We will install it later. Note that it is recommended to install the console on a separate server (this can be a VM on Windows or Linux)
img6
Click on Next
img7
Here we give the user we created at the beginning
img8
We provide the password
img9
And then the installation starts
img10
And then the final step is to answer to some configuration questions


-----------------------------------------------------------
About to configure DBVISIT DBVNET
-----------------------------------------------------------

>>> Please specify the Dbvnet Passphrase to be used for secure connections.

The passphrase provided must be the same in both the local and remote
Dbvnet installations. It is used to establish a secure (encrypted)
Dbvnet connections

Enter a custom value:
> XXXXXXXXXXXXXXXXX

>>> Please specify the Local host name to be used by Dbvnet on this server.

Dbvnet will be listening on the local IP Address on this server which
resolve to the host name specified here.
If using a cluster or virtual IP make sure the host name or alias
specified here resolve to the IP address local to where dbvnet is
installed. The host name should resolve to IPv4 address, if not
you can use an IPv4 IP address instead of host name.

Enter a custom value or press ENTER to accept default [winserver2]:
>

>>> Please specify the Local Dbvnet PORT to be used.

Dbvnet will be listening on the specified port for incoming connections
from remote dbvnet connections. Please make sure that this port is not
already in use or blocked by any firewall. You may choose any value
between 1024 and 65535, however the default of 7890 is recommended.

Enter a custom value or press ENTER to accept default [7890]:
>

>>> Please specify the Remote host name to be used by Dbvnet.

By default Dbvnet will use this remote hostname for any remote
connections. Dbvnet must be installed and configured on the specified
remote host. If using a cluster or virtual IP make sure the host name
or alias specified here resolve to the IP address local to where dbvnet
is installed.
If you are unsure about the remote host name during installation, use
the default value which will be the current local hostname.
The host name should resolve to IPv4 address, if not
you can use an IPv4 IP address instead of host name.

Enter a custom value or press ENTER to accept default [winserver2]:
> winserver1

>>> Please specify the Remote Dbvnet PORT to be used.

Dbvnet will connect to the remote server on this specified port.
On the remote host Dbvnet will be listening on the specified port for
incoming connections. Please make sure that this port is not already in
use or blocked by any firewall. You may choose any value between 1024
and 65535, however the default of 7890 is recommended.

Enter a custom value or press ENTER to accept default [7890]:
>

-----------------------------------------------------------
Summary of the Dbvisit DBVNET configuration
-----------------------------------------------------------
DBVISIT_BASE C:\Program Files\Dbvisit
DBVNET_PASSPHRASE XXXXXXXXXXXX
DBVNET_LOCAL_HOST winserver2
DBVNET_LOCAL_PORT 7890
DBVNET_REMOTE_HOST winserver1
DBVNET_REMOTE_PORT 7890

Press ENTER to continue

-----------------------------------------------------------
About to configure DBVISIT DBVAGENT
-----------------------------------------------------------

>>> Please specify the host name to be used for the Dbvisit Agent.

The Dbvisit Agent (Dbvagent) will be listening on this local address.
If you are using the Dbvserver (GUI) - connections from the GUI will be
established to the Dbvisit Agent. The Dbvisit Agent address must be
visible from the Dbvserver (GUI) installation.
If using a cluster or virtual IP make sure the host name or alias
specified here resolve to the IP address local to where dbvnet is
installed.
The host name should resolve to IPv4 address, if not you can use
an IPv4 IP address instead of host name.

Enter a custom value or press ENTER to accept default [winserver2]:
>

>>> Please specify the listening PORT number for Dbvagent.

The Dbvisit Agent (Dbvagent) will listening on the specified port for
incoming requests from the GUI (Dbvserver). Please make sure that this
port is not already in use or blocked by any firewall. You may choose
any value between 1024 and 65535, however the default of 7891 is
recommended.

Enter a custom value or press ENTER to accept default [7891]:
>

>>> Please specify passphrase for Dbvagent

Each Dbvisit Agent must have a passpharse specified. This passphrase
does not have to match between all the servers. It will be used to
establish a secure connection between the GUI (Dbvserver) and the
Dbvisit Agent.

Enter a custom value:
> XXXXXXXXXXXXXXXXXXXX

-----------------------------------------------------------
Summary of the Dbvisit DBVAGENT configuration
-----------------------------------------------------------
DBVISIT_BASE C:\Program Files\Dbvisit
DBVAGENT_LOCAL_HOST winserver2
DBVAGENT_LOCAL_PORT 7891
DBVAGENT_PASSPHRASE XXXXXXXXXXXXXXXXXXX

Press ENTER to continue

No need to configure standby, skipped.
Copied file C:\Program Files\Dbvisit\dbvnet\conf\dbvnetd.conf to C:\Program Files\Dbvisit\dbvnet\conf\dbvnetd.conf.201808260218
DBVNET config file updated
Copied file C:\Program Files\Dbvisit\dbvagent\conf\dbvagent.conf to C:\Program Files\Dbvisit\dbvagent\conf\dbvagent.conf.201808260218
DBVAGENT config file updated

-----------------------------------------------------------
Component Installed Version
-----------------------------------------------------------
standby 8.0.22_36_gb602000a
dbvnet 8.0.22_36_gb602000a
dbvagent 8.0.22_36_gb602000a
dbvserver not installed

-----------------------------------------------------------

-----------------------------------------------------------
About to start service DBVISIT DBVNET
-----------------------------------------------------------
Successfully started service DBVISIT DBVNET

-----------------------------------------------------------
About to start service DBVISIT DBVAGENT
-----------------------------------------------------------
Successfully started service DBVISIT DBVAGENT

>>> Installation completed
Install log C:\Users\dbvisit\AppData\Local\Temp\dbvisit_install.log.201808260214.

Press ENTER to continue

Once then we can finish the installation
img11
dbserver console install

The installation of the dbserver (central console) is done is the same way. We will not show pictures but only the questions we have to reply. In our case it is installed on the primary server winserver1.

-----------------------------------------------------------
Welcome to the Dbvisit software installer.
-----------------------------------------------------------

Installing dbvserver...

-----------------------------------------------------------
About to configure DBVISIT DBVSERVER
-----------------------------------------------------------

>>> Please specify the host name to be used for Dbvserver

The Dbvisit Web Server (Dbvserver) will be listening on this local
address. If using a cluster or virtual IP make sure the host name or
alias specified here resolve to the IP address local to where Dbvserver
is installed.
If you are unsure about the remote host name during installation, use
the default value which will be the current local hostname.
The host name should resolve to IPv4 address, if not you can use
an IPv4 IP address instead of host name.

Enter a custom value or press ENTER to accept default [winserver1]:
>
>>> Please specify the listening port number for Dbvserver on the local server

You may choose any value between 1024 and 65535. The default recommended
value is 4433.

Note: if you can not access this port after the installation has
finished, then please double-check your server firewall settings
to ensure the selected port is open.
Enter a custom value or press ENTER to accept default [4433]:
>
>>> Please specify the host name (or IPv4 address) to be used for Dbvserver public interface

In most cases this will be the same as the listener address, if not sure
use the same value as the listener address.

The Dbvisit Web Server (Dbvserver) will be listening on the local
listener address. The public address can be set to an external IP
example a firewall address in case the Central Console (Dbvserver)
and agents (Primary and Standby Database servers) have a firewall
inbetween them. The public interface address will be passed to
the agents during communication for sending information back.
If you are unsure about the public host address, use
the default value which will be the current local hostname.
The host name should resolve to IPv4 address, if not you can use
an IPv4 IP address instead of host name.
Enter a custom value or press ENTER to accept default [winserver1]:
>

-----------------------------------------------------------
Summary of the Dbvisit DBVSERVER configuration
-----------------------------------------------------------
DBVISIT_BASE C:\Program Files\Dbvisit
DBVSERVER_LOCAL_HOST winserver1
DBVSERVER_LOCAL_PORT 4433
DBVSERVER_PUBLIC_HOST winserver1

Press ENTER to continue

-----------------------------------------------------------
Summary of the Dbvisit DBVSERVER configuration
-----------------------------------------------------------
DBVISIT_BASE C:\Program Files\Dbvisit
DBVSERVER_LOCAL_HOST winserver1
DBVSERVER_LOCAL_PORT 4433
DBVSERVER_PUBLIC_HOST winserver1

Press ENTER to continue

Once the console installed we can log into using the following URL

https://winserver1:4433

with the default credentials admin/admin (Note that you have to change it once logged)

Conclusion : In this blog we have shown how to install dbvisit in a Windows server. In a coming blog we will see how to create a standby database on a Windows server and how to schedule log shipping and log apply.

 

Cet article Install Dbvisit 8 on Windows est apparu en premier sur Blog dbi services.

How I Simplified Oracle Database 18c (Standard Edition 2) Installation on Red Hat Linux Entreprise 7.5 with Oracle Preinstallation RPM

Pierre Forstmann Oracle Database blog - Wed, 2018-08-29 12:43

Up to Oracle 12.2.0.1 it was not possible to use directly Oracle Database preinstallation RPM on Red Hat Linux Enterprise LInux (RHEL) because this RPM was replacing the Red Hat Linux kernel by the Unbreakable Entreprise Kernel(UEK): not sure this was intented if you decided to use RHEL instead of Oracle Linux.

It was still possible to modify this RPM to remove the dependency on UEK and to install it as described in this blog post .

Things have changed with Oracle 18c: it is now possible to use directlty Oracle Preinstallation on RHEL as documented in Oracle Database Installation Guide 18c for Linux.

In this blog post I detail how I have installed Oracle 18c on RHEL 7.5 using the official Redhat distribution that I am again able to download from Red Hat using Red Hat Developer free license valid for one year.

Operating system installation

For this blog post I have created a VirtualBox virtual machine (VM) named rh7defs0 that has:

  • 4 GB of RAM
  • one 40 GB hard disk attached to SATA controller
  • 2 network interfaces (the first one attached to NAT and the second one attached to Host-Only Adapter).
  • When installing Red Hat 7 I have chosen in following order:

    1. English as installation process language

    2. Paris, France as time zone

    3. French as keyboard layout

    4. default disk partitioning with 4GB of swap

    5. rh7defs0.localdomain as hostname

    I have not modified software selection set to “minimal install” and I have also set root password.

    In this post # prompt means root account session and $ prompt means oracle account session.

    After the minimal operating system installation and machine reboot I have used system console to configure public network interface with:

    # nmcli connection add type ethernet con-name enp0s8 ifname enp0s8 ip4 192.168.56.14/24.
    

    At this step I have connected with ssh to configure the NAT network interface with:

    # nmcli connection add type ethernet con-name enp0s3 ifname enp0s3 ip4 10.0.2.15/8 gw4 10.0.2.2
    Warning: There is another connection with the name 'enp0s3'. Reference the connection by its uuid 'f43b8229-4939-4952-b794-da103e5168be'
    # ip addr
    1: lo:  mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
        link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
        inet 127.0.0.1/8 scope host lo
           valid_lft forever preferred_lft forever
        inet6 ::1/128 scope host 
           valid_lft forever preferred_lft forever
    2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 08:00:27:06:20:85 brd ff:ff:ff:ff:ff:ff
        inet 10.0.2.15/8 brd 10.255.255.255 scope global noprefixroute enp0s3
           valid_lft forever preferred_lft forever
        inet6 fe80::5b8e:9d1e:7d2e:5a42/64 scope link noprefixroute 
           valid_lft forever preferred_lft forever
    3: enp0s8:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
        link/ether 08:00:27:00:04:75 brd ff:ff:ff:ff:ff:ff
        inet 192.168.56.14/24 brd 192.168.56.255 scope global noprefixroute enp0s8
           valid_lft forever preferred_lft forever
        inet6 fe80::7075:e445:f59f:87ad/64 scope link noprefixroute 
           valid_lft forever preferred_lft forever
    

    I have also added my Internet Box IP address to /etc/resolv.conf and checked DNS:

    # cat /etc/resolv.conf
    search localdomain
    nameserver 192.168.1.254
    
    # ping www.redhat.com
    PING e3396.dscx.akamaiedge.net (104.124.219.53) 56(84) bytes of data.
    64 bytes from a104-124-219-53.deploy.static.akamaitechnologies.com (104.124.219.53): icmp_seq=1 ttl=63 time=39.2 ms
    64 bytes from a104-124-219-53.deploy.static.akamaitechnologies.com (104.124.219.53): icmp_seq=2 ttl=63 time=37.7 ms
    64 bytes from a104-124-219-53.deploy.static.akamaitechnologies.com (104.124.219.53): icmp_seq=3 ttl=63 time=38.6 ms
    64 bytes from a104-124-219-53.deploy.static.akamaitechnologies.com (104.124.219.53): icmp_seq=4 ttl=63 time=37.1 ms
    64 bytes from a104-124-219-53.deploy.static.akamaitechnologies.com (104.124.219.53): icmp_seq=5 ttl=63 time=36.4 ms
    ^C
    --- e3396.dscx.akamaiedge.net ping statistics ---
    5 packets transmitted, 5 received, 0% packet loss, time 4009ms
    rtt min/avg/max/mdev = 36.438/37.852/39.266/1.042 ms
    

    I have run following steps to connect this VM to public Red Hat Satellite:

    # subscription-manager register
    # subscription-manager attach --auto
    

    I have checked that the VM has the right subscription status:

    # cat /etc/redhat-release 
    Red Hat Enterprise Linux Server release 7.5 (Maipo)
    # subscription-manager status       
    +-------------------------------------------+
       System Status Details
    +-------------------------------------------+
    Overall Status: Current
    
    # yum repolist
    Loaded plugins: product-id, search-disabled-repos, subscription-manager
    rhel-7-server-rpms                                       | 3.5 kB     00:00     
    (1/3): rhel-7-server-rpms/7Server/x86_64/group             | 855 kB   00:02     
    (2/3): rhel-7-server-rpms/7Server/x86_64/updateinfo        | 2.9 MB   00:05     
    (3/3): rhel-7-server-rpms/7Server/x86_64/primary_db        |  55 MB   00:42     
    repo id                           repo name                               status
    rhel-7-server-rpms/7Server/x86_64 Red Hat Enterprise Linux 7 Server (RPMs 20 908
    repolist: 20 908
    
    Oracle Preinstallation RPM installation

    I have downloaded this RPM with:

    # yum -y install wget
    Loaded plugins: product-id, search-disabled-repos, subscription-manager
    Resolving Dependencies
    --> Running transaction check
    ---> Package wget.x86_64 0:1.14-15.el7_4.1 will be installed
    --> Finished Dependency Resolution
    
    Dependencies Resolved
    
    ===========================================================================================================================
     Package               Arch                    Version                           Repository                           Size
    ===========================================================================================================================
    Installing:
     wget                  x86_64                  1.14-15.el7_4.1                   rhel-7-server-rpms                  547 k
    
    Transaction Summary
    ===========================================================================================================================
    Install  1 Package
    
    Total download size: 547 k
    Installed size: 2.0 M
    Downloading packages:
    warning: /var/cache/yum/x86_64/7Server/rhel-7-server-rpms/packages/wget-1.14-15.el7_4.1.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID fd431d51: NOKEY
    Public key for wget-1.14-15.el7_4.1.x86_64.rpm is not installed
    wget-1.14-15.el7_4.1.x86_64.rpm                                                                     | 547 kB  00:00:01     
    Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
    Importing GPG key 0xFD431D51:
     Userid     : "Red Hat, Inc. (release key 2) "
     Fingerprint: 567e 347a d004 4ade 55ba 8a5f 199e 2f91 fd43 1d51
     Package    : redhat-release-server-7.5-8.el7.x86_64 (@anaconda/7.5)
     From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
    Importing GPG key 0x2FA658E0:
     Userid     : "Red Hat, Inc. (auxiliary key) "
     Fingerprint: 43a6 e49c 4a38 f4be 9abf 2a53 4568 9c88 2fa6 58e0
     Package    : redhat-release-server-7.5-8.el7.x86_64 (@anaconda/7.5)
     From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release
    Running transaction check
    Running transaction test
    Transaction test succeeded
    Running transaction
      Installing : wget-1.14-15.el7_4.1.x86_64                                                                             1/1 
    rhel-7-server-rpms/7Server/x86_64/productid                                                         | 2.1 kB  00:00:00     
      Verifying  : wget-1.14-15.el7_4.1.x86_64                                                                             1/1 
    
    Installed:
      wget.x86_64 0:1.14-15.el7_4.1                                                                                            
    
    Complete!
    # wget http://public-yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
    --2018-08-24 19:45:18--  http://public-yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
    Resolving public-yum.oracle.com (public-yum.oracle.com)... 23.205.82.159
    Connecting to public-yum.oracle.com (public-yum.oracle.com)|23.205.82.159|:80... connected.
    HTTP request sent, awaiting response... 200 OK
    Length: 18244 (18K) [application/x-rpm]
    Saving to: 'oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm'
    
    100%[=================================================================================>] 18 244      --.-K/s   in 0,02s   
    
    2018-08-24 19:45:21 (1,04 MB/s) - 'oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm' saved [18244/18244]
    

    To avoid following error when trying to install this RPM:

    Error: Package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 (/oracle-database-preinstall-18c-1.0-1.el7.x86_64)
               Requires: compat-libstdc++-33
    **********************************************************************
    yum can be configured to try to resolve such errors by temporarily enabling
    disabled repos and searching for missing dependencies.
    To enable this functionality please set 'notify_only=0' in /etc/yum/pluginconf.d/search-disabled-repos.conf
    **********************************************************************
    
    Error: Package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 (/oracle-database-preinstall-18c-1.0-1.el7.x86_64)
               Requires: compat-libstdc++-33
     You could try using --skip-broken to work around the problem
     You could try running: rpm -Va --nofiles --nodigest
    

    I have modified /etc/yum/pluginconf.d/search-disabled-repos.conf as recommended by yum:

    # grep notify_only /etc/yum/pluginconf.d/search-disabled-repos.conf
    # With notify_only=1 this plugin does not modify yum's behaviour.
    # Setting notify_only to 0 will enable yum to try to automatically resolve
    notify_only=0
    

    I have restarted RPM installation with:

    # yum -y install oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm 
    

    This has taken several minutes because YUM was searching for compat-libstdc++-33 in a lot of YUM repositories:

    Error: Package: oracle-database-preinstall-18c-1.0-1.el7.x86_64 (/oracle-database-preinstall-18c-1.0-1.el7.x86_64)
               Requires: compat-libstdc++-33
    **********************************************************************
    Dependency resolving failed due to missing dependencies.
    Some repositories on your system are disabled, but yum can enable them
    and search for missing dependencies. This will require downloading
    metadata for disabled repositories and may take some time and traffic.
    **********************************************************************
    

    Eventually YUM has found it and managed to install it. You can find yum install log here.

    Oracle 18c Standard Edition 2 silent installation

    I have run following steps to install Oracle Database 18c Standard Edition 2 in silent mode:

    # mkdir -p /u01/db18c
    # mkdir -p /u01/base 
    # mkdir /u01/orainv
    # mkdir /stage
    # chown oracle:dba -R /u01
    # chown oracle:dba -R /stage
    

    I have switched to oracle account:

    $ cd /u01/db18c
    $ unzip /stage/LINUX.X64_180000_db_home.zip 
    ./runInstaller \
    -silent \
    -responseFile /u01/db18c/install/response/db_install.rsp \
       oracle.install.option=INSTALL_DB_SWONLY             \
       UNIX_GROUP_NAME=oinstall                            \
       INVENTORY_LOCATION=/u01/orainv                      \
       SELECTED_LANGUAGES=en                               \
       ORACLE_HOME=/u01/db18c                              \
       ORACLE_BASE=/u01/base                               \
       oracle.install.db.InstallEdition=SE2                \
       oracle.install.db.isCustomInstall=false             \
       oracle.install.db.OSDBA_GROUP=dba                   \
       oracle.install.db.OSBACKUPDBA_GROUP=dba             \
       oracle.install.db.OSDGDBA_GROUP=dba                 \
       oracle.install.db.OSKMDBA_GROUP=dba                 \
       oracle.install.db.OSRACDBA_GROUP=dba                \
       SECURITY_UPDATES_VIA_MYORACLESUPPORT=false          \
       DECLINE_SECURITY_UPDATES=true
    

    This has generated following output:

    Launching Oracle Database Setup Wizard...
    
    [WARNING] [INS-13014] Target environment does not meet some optional requirements.
       CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2018-08-24_08-14-14PM.log
       ACTION: Identify the list of failed prerequisite checks from the log: installActions2018-08-24_08-14-14PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
    The response file for this session can be found at:
     /u01/db18c/install/response/db_2018-08-24_08-14-14PM.rsp
    
    You can find the log of this install session at:
     /tmp/InstallActions2018-08-24_08-14-14PM/installActions2018-08-24_08-14-14PM.log
    
    As a root user, execute the following script(s):
    	1. /u01/orainv/orainstRoot.sh
    	2. /u01/db18c/root.sh
    
    Execute /u01/orainv/orainstRoot.sh on the following nodes: 
    [rh7defs0]
    Execute /u01/db18c/root.sh on the following nodes: 
    [rh7defs0]
    
    
    Successfully Setup Software with warning(s).
    Moved the install session logs to:
     /u01/orainv/logs/InstallActions2018-08-24_08-14-14PM
    

    I have ignored following failed prerequisites:

    INFO:  [Aug 24, 2018 8:14:43 PM] ------------------List of failed Tasks------------------
    INFO:  [Aug 24, 2018 8:14:43 PM] *********************************************
    INFO:  [Aug 24, 2018 8:14:43 PM] Physical Memory: This is a prerequisite condition to test whether the system has at least 8GB (8388608.0KB) of total physical memory.
    INFO:  [Aug 24, 2018 8:14:43 PM] Severity:IGNORABLE
    INFO:  [Aug 24, 2018 8:14:43 PM] OverallStatus:VERIFICATION_FAILED
    INFO:  [Aug 24, 2018 8:14:43 PM] *********************************************
    INFO:  [Aug 24, 2018 8:14:43 PM] Package: gcc-c++-4.8.2: This is a prerequisite condition to test whether the package "gcc-c++-4.8.2" is available on the system.
    INFO:  [Aug 24, 2018 8:14:43 PM] Severity:IGNORABLE
    INFO:  [Aug 24, 2018 8:14:43 PM] OverallStatus:VERIFICATION_FAILED
    INFO:  [Aug 24, 2018 8:14:43 PM] -----------------End of failed Tasks List----------------
    

    I have run as root:

    # /u01/orainv/orainstRoot.sh
    Changing permissions of /u01/orainv.
    Adding read,write permissions for group.
    Removing read,write,execute permissions for world.
    
    Changing groupname of /u01/orainv to oinstall.
    The execution of the script is complete.
    # /u01/db18c/root.sh
    # cat /u01/db18c/install/root_rh7defs0.localdomain_2018-08-24_20-17-18-230575231.log
    Performing root user operation.
    
    The following environment variables are set as:
        ORACLE_OWNER= oracle
        ORACLE_HOME=  /u01/db18c
       Copying dbhome to /usr/local/bin ...
       Copying oraenv to /usr/local/bin ...
       Copying coraenv to /usr/local/bin ...
    
    
    Creating /etc/oratab file...
    Entries will be added to the /etc/oratab file as needed by
    Database Configuration Assistant when a database is created
    Finished running generic part of root script.
    Now product-specific root actions will be performed.
    Oracle Trace File Analyzer (TFA) is available at : /u01/db18c/suptools/tfa/release/tfa_home/bin/tfactl
    

    I have checked Oracle inventory with:

    $ export ORACLE_HOME=/u01/db18c
    $ PATH=$ORACLE_HOME/bin:$PATH
    $ $ORACLE_HOME/OPatch/opatch lsinv
    Oracle Interim Patch Installer version 12.2.0.1.14
    Copyright (c) 2018, Oracle Corporation.  All rights reserved.
    
    
    Oracle Home       : /u01/db18c
    Central Inventory : /u01/orainv
       from           : /u01/db18c/oraInst.loc
    OPatch version    : 12.2.0.1.14
    OUI version       : 12.2.0.4.0
    Log file location : /u01/db18c/cfgtoollogs/opatch/opatch2018-08-24_20-20-09PM_1.log
    
    Lsinventory Output file location : /u01/db18c/cfgtoollogs/opatch/lsinv/lsinventory2018-08-24_20-20-09PM.txt
    
    --------------------------------------------------------------------------------
    Local Machine Information::
    Hostname: localhost
    ARU platform id: 226
    ARU platform description:: Linux x86-64
    
    Installed Top-level Products (1): 
    
    Oracle Database 18c                                                  18.0.0.0.0
    There are 1 products installed in this Oracle Home.
    
    
    Interim patches (4) :
    
    Patch  27908644     : applied on Wed Jul 18 19:44:11 CEST 2018
    Unique Patch ID:  22153180
    Patch description:  "UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171"
       Created on 4 May 2018, 01:21:02 hrs PST8PDT
       Bugs fixed:
         27908644
    
    Patch  27923415     : applied on Wed Jul 18 19:41:38 CEST 2018
    Unique Patch ID:  22239273
    Patch description:  "OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)"
       Created on 15 Jul 2018, 10:33:22 hrs PST8PDT
       Bugs fixed:
         27304131, 27539876, 27952586, 27642235, 27636900, 27461740
    
    Patch  28090553     : applied on Wed Jul 18 19:40:01 CEST 2018
    Unique Patch ID:  22256940
    Patch description:  "OCW RELEASE UPDATE 18.3.0.0.0 (28090553)"
       Created on 11 Jul 2018, 19:20:31 hrs PST8PDT
       Bugs fixed:
         12816839, 18701017, 22734786, 23698980, 23840305, 25709124, 25724089
         26299684, 26313403, 26433972, 26527054, 26586174, 26587652, 26647619
         26827699, 26860285, 26882126, 26882316, 26943660, 26996813, 27012915
         27018734, 27032726, 27034318, 27040560, 27080748, 27086406, 27092991
         27098733, 27106915, 27114112, 27121566, 27133637, 27144533, 27153755
         27166715, 27174938, 27174948, 27177551, 27177852, 27182006, 27182064
         27184253, 27204476, 27212837, 27213140, 27220610, 27222423, 27222938
         27238077, 27238258, 27249544, 27252023, 27257509, 27263677, 27265816
         27267992, 27271876, 27274143, 27285557, 27299455, 27300007, 27302415
         27309182, 27314512, 27315159, 27320985, 27334353, 27338838, 27346984
         27358232, 27362190, 27370933, 27377219, 27378959, 27379846, 27379956
         27393421, 27398223, 27399499, 27399762, 27399985, 27401618, 27403244
         27404599, 27426277, 27428790, 27430219, 27430254, 27433163, 27452897
         27458829, 27465480, 27475272, 27481406, 27481765, 27492916, 27496806
         27503318, 27503413, 27508936, 27508984, 27513114, 27519708, 27526362
         27528204, 27532009, 27534289, 27560562, 27560735, 27573154, 27573408
         27574335, 27577122, 27579969, 27581484, 27593587, 27595801, 27600706
         27609819, 27625010, 27625050, 27627992, 27654039, 27657467, 27657920
         27668379, 27682288, 27691717, 27702244, 27703242, 27708711, 27714373
         27725967, 27731346, 27734470, 27735534, 27739957, 27740854, 27747407
         27748321, 27757979, 27766679, 27768034, 27778433, 27782464, 27783059
         27786669, 27786699, 27801774, 27811439, 27839732, 27850736, 27862636
         27864737, 27865439, 27889841, 27896388, 27897639, 27906509, 27931506
         27935826, 27941514, 27957892, 27978668, 27984314, 27993298, 28023410
         28025398, 28032758, 28039471, 28039953, 28045209, 28099592, 28109698
         28174926, 28182503, 28204423, 28240153
    
    Patch  28090523     : applied on Wed Jul 18 19:39:24 CEST 2018
    Unique Patch ID:  22329768
    Patch description:  "Database Release Update : 18.3.0.0.180717 (28090523)"
       Created on 14 Jul 2018, 00:03:50 hrs PST8PDT
       Bugs fixed:
         9062315, 13554903, 21547051, 21766220, 21806121, 23003564, 23310101
         24489904, 24689376, 24737581, 24925863, 25035594, 25035599, 25287072
         25348956, 25634405, 25726981, 25743479, 25824236, 25943740, 26226953
         26336101, 26423085, 26427905, 26450454, 26476244, 26598422, 26615291
         26646549, 26654411, 26731697, 26785169, 26792891, 26818960, 26822620
         26843558, 26843664, 26846077, 26894737, 26898279, 26928317, 26933599
         26956033, 26961415, 26966120, 26986173, 26992964, 27005278, 27026401
         27028251, 27030974, 27036408, 27038986, 27041253, 27044575, 27047831
         27053044, 27058530, 27060167, 27060859, 27061736, 27066451, 27066519
         27073066, 27086821, 27090765, 27101527, 27101652, 27110878, 27112686
         27119621, 27126666, 27128580, 27135647, 27143756, 27143882, 27147979
         27153641, 27155549, 27156355, 27163928, 27169796, 27181521, 27181537
         27189611, 27190851, 27193810, 27199245, 27208953, 27210038, 27210872
         27214085, 27215007, 27216224, 27221900, 27222121, 27222626, 27224987
         27226913, 27232983, 27233563, 27236052, 27236110, 27240246, 27240570
         27241221, 27241247, 27244337, 27244785, 27249215, 27250547, 27254851
         27258578, 27259386, 27259983, 27262650, 27262945, 27263276, 27263996
         27270197, 27274456, 27274536, 27275136, 27275776, 27282707, 27283029
         27283960, 27284499, 27285244, 27288230, 27292213, 27294480, 27301308
         27301568, 27302594, 27302681, 27302695, 27302711, 27302730, 27302777
         27302800, 27302960, 27304410, 27304936, 27305318, 27307868, 27310092
         27313687, 27314206, 27314390, 27318869, 27321179, 27321834, 27326204
         27329812, 27330158, 27330161, 27333658, 27333664, 27333693, 27334316
         27334648, 27335682, 27338912, 27338946, 27339115, 27339396, 27339483
         27339495, 27341036, 27345190, 27345231, 27345450, 27345498, 27346329
         27346644, 27346709, 27346949, 27347126, 27348081, 27348707, 27349393
         27352600, 27354783, 27356373, 27357773, 27358241, 27359178, 27359368
         27360126, 27364891, 27364916, 27364947, 27365139, 27365702, 27365993
         27367194, 27368850, 27372756, 27375260, 27375542, 27376871, 27378103
         27379233, 27381383, 27381656, 27384222, 27389352, 27392187, 27395404
         27395416, 27395794, 27396357, 27396365, 27396377, 27396624, 27396666
         27396672, 27396813, 27398080, 27398660, 27401637, 27405242, 27405696
         27410300, 27410595, 27412805, 27417186, 27420715, 27421101, 27422874
         27423251, 27425507, 27425622, 27426363, 27427805, 27430802, 27432338
         27432355, 27433870, 27434050, 27434193, 27434486, 27434974, 27435537
         27439835, 27441326, 27442041, 27444727, 27445330, 27445462, 27447452
         27447687, 27448162, 27450355, 27450400, 27450783, 27451049, 27451182
         27451187, 27451531, 27452760, 27453225, 27457666, 27457891, 27458164
         27459909, 27460675, 27467543, 27469329, 27471876, 27472969, 27473800
         27479358, 27483974, 27484556, 27486253, 27487795, 27489719, 27496224
         27496308, 27497950, 27498477, 27501327, 27501413, 27501465, 27502420
         27504190, 27505603, 27506774, 27508985, 27511196, 27512439, 27517818
         27518227, 27518310, 27520070, 27520900, 27522245, 27523368, 27523800
         27525909, 27532375, 27533819, 27534509, 27537472, 27544030, 27545630
         27547732, 27550341, 27551855, 27558557, 27558559, 27558861, 27560702
         27563629, 27563767, 27570318, 27577758, 27579353, 27580996, 27585755
         27585800, 27586810, 27586895, 27587672, 27591842, 27592466, 27593389
         27595973, 27599689, 27602091, 27602488, 27603841, 27604293, 27607805
         27608669, 27610269, 27613080, 27613247, 27615608, 27616657, 27617522
         27625274, 27625620, 27631506, 27634676, 27635508, 27644757, 27649707
         27652302, 27663370, 27664702, 27679488, 27679664, 27679806, 27679961
         27680162, 27680509, 27682151, 27688099, 27688692, 27690578, 27691809
         27692215, 27693713, 27697092, 27701795, 27705761, 27707544, 27709046
         27718914, 27719187, 27723002, 27726269, 27726780, 27732323, 27739006
         27740844, 27744211, 27745220, 27747869, 27748954, 27751006, 27753336
         27757567, 27772815, 27773602, 27774320, 27774539, 27779886, 27780562
         27782339, 27783289, 27786772, 27791223, 27797290, 27803665, 27807441
         27812560, 27812593, 27813267, 27815347, 27818871, 27832643, 27833369
         27834984, 27840386, 27847259, 27851757, 27861909, 27869339, 27873643
         27882176, 27892488, 27924147, 27926113, 27930478, 27934468, 27941896
         27945870, 27950708, 27952762, 27961746, 27964051, 27970265, 27971575
         27984028, 27989849, 27993289, 27994333, 27997875, 27999597, 28021205
         28022847, 28033429, 28057267, 28059199, 28072130, 28098865, 28106402
         28132287, 28169711, 28174827, 28184554, 28188330, 25929650, 28264172
    
    
    
    --------------------------------------------------------------------------------
    
    OPatch succeeded.
    $ $ORACLE_HOME/OPatch/opatch lspatches
    27908644;UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171
    27923415;OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)
    28090553;OCW RELEASE UPDATE 18.3.0.0.0 (28090553)
    28090523;Database Release Update : 18.3.0.0.180717 (28090523)
    
    

    I have created a database with following steps:

    # mkdir /u01/oradata
    # chown oracle:dba /u01/oradata
    # mkdir /u01/fra
    # chown oracle:dba /u01/fra
    

    and:

    $ dbca -silent \
    -createDatabase \
    -templateName General_Purpose.dbc \
    -gdbName CDB \
    -sid CDB \
    -createAsContainerDatabase true \
    -numberOfPdbs 1 \
    -pdbName pdb \
    -pdbadminUsername pdba \
    -pdbadminPassword oracle \
    -SysPassword oracle \
    -SystemPassword oracle \
    -emConfiguration NONE \
    -storageType FS \
    -datafileDestination /u01/oradata \
    -recoveryAreaDestination /u01/fra \
    -recoveryAreaSize 3200  \
    -characterSet AL32UTF8 \
    -memoryPercentage 40 \
    -enableArchive true \
    -redoLogFileSize 100
    [WARNING] [DBT-06801] Specified Fast Recovery Area size (3,200 MB) is less than the recommended value.
       CAUSE: Fast Recovery Area size should at least be three times the database size (2,446 MB).
       ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
    [WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
       CAUSE: 
    a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
    b.The password entered is a keyword that Oracle does not recommend to be used as password
       ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
    [WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
       CAUSE: 
    a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
    b.The password entered is a keyword that Oracle does not recommend to be used as password
       ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
    [WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
       CAUSE: 
    a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
    b.The password entered is a keyword that Oracle does not recommend to be used as password
       ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
    [WARNING] [DBT-06801] Specified Fast Recovery Area size (3,200 MB) is less than the recommended value.
       CAUSE: Fast Recovery Area size should at least be three times the database size (3,309 MB).
       ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
    Prepare for db operation
    8% complete
    Copying database files
    31% complete
    Creating and starting Oracle instance
    32% complete
    36% complete
    40% complete
    43% complete
    46% complete
    Completing Database Creation
    51% complete
    53% complete
    54% complete
    Creating Pluggable Databases
    58% complete
    77% complete
    Executing Post Configuration Actions
    100% complete
    Database creation complete. For details check the logfiles at:
     /u01/base/cfgtoollogs/dbca/CDB.
    Database Information:
    Global Database Name:CDB
    System Identifier(SID):CDB
    Look at the log file "/u01/base/cfgtoollogs/dbca/CDB/CDB.log" for further details.
    

    I have checked created database with:

    $ . oraenv
    ORACLE_SID = [oracle] ? CDB
    The Oracle base has been set to /u01/base
    $ sqlplus / as sysdba
    
    SQL*Plus: Release 18.0.0.0.0 - Production on Fri Aug 24 20:47:52 2018
    Version 18.3.0.0.0
    
    Copyright (c) 1982, 2018, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
    Version 18.3.0.0.0
    
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    BANNER_FULL
    --------------------------------------------------------------------------------
    BANNER_LEGACY
    --------------------------------------------------------------------------------
        CON_ID
    ----------
    Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
    Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
    Version 18.3.0.0.0
    Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
    	 0
    
    BANNER
    --------------------------------------------------------------------------------
    BANNER_FULL
    --------------------------------------------------------------------------------
    BANNER_LEGACY
    --------------------------------------------------------------------------------
        CON_ID
    ----------
    
    SQL> select name, cdb, log_mode from v$database;
    
    NAME	  CDB LOG_MODE
    --------- --- ------------
    CDB	  YES ARCHIVELOG
    
    SQL> show pdbs
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 2 PDB$SEED			  READ ONLY  NO
    	 3 PDB				  READ WRITE NO
    SQL> 
    
    SQL> select comp_name, status, version from dba_registry order by 1;
    
    COMP_NAME				 STATUS     VERSION
    ---------------------------------------- ---------- ------------------------------
    JServer JAVA Virtual Machine		 VALID	    18.0.0.0.0
    OLAP Analytic Workspace 		 OPTION OFF 18.0.0.0.0
    Oracle Database Catalog Views		 VALID	    18.0.0.0.0
    Oracle Database Java Packages		 VALID	    18.0.0.0.0
    Oracle Database Packages and Types	 VALID	    18.0.0.0.0
    Oracle Database Vault			 OPTION OFF 18.0.0.0.0
    Oracle Label Security			 OPTION OFF 18.0.0.0.0
    Oracle Multimedia			 VALID	    18.0.0.0.0
    Oracle OLAP API 			 OPTION OFF 18.0.0.0.0
    Oracle Real Application Clusters	 OPTION OFF 18.0.0.0.0
    Oracle Text				 VALID	    18.0.0.0.0
    
    COMP_NAME				 STATUS     VERSION
    ---------------------------------------- ---------- ------------------------------
    Oracle Workspace Manager		 VALID	    18.0.0.0.0
    Oracle XDK				 VALID	    18.0.0.0.0
    Oracle XML Database			 VALID	    18.0.0.0.0
    Spatial 				 OPTION OFF 18.0.0.0.0
    
    15 rows selected.
    
    

    I have added host name to /etc/hosts for Oracle Net with:

    # vi /etc/hosts
    # grep rh /etc/hosts
    192.168.56.14 rh7defs0 rh7defs0.localdomain
    # 
    

    I have started Oracle Net listener and waited about 1 minute for automatic registration of database instance:

    $ . oraenv
    ORACLE_SID = [oracle] ? CDB
    The Oracle base has been set to /u01/base
    $ lsnrctl start
    
    LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 27-AUG-2018 21:22:04
    
    Copyright (c) 1991, 2018, Oracle.  All rights reserved.
    
    Starting /u01/db18c/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 18.0.0.0.0 - Production
    Log messages written to /u01/base/diag/tnslsnr/rh7defs0/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh7defs0)(PORT=1521)))
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
    Start Date                27-AUG-2018 21:22:04
    Uptime                    0 days 0 hr. 0 min. 0 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Log File         /u01/base/diag/tnslsnr/rh7defs0/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh7defs0)(PORT=1521)))
    The listener supports no services
    The command completed successfully
    $ lsnrctl status
    
    LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 27-AUG-2018 21:22:50
    
    Copyright (c) 1991, 2018, Oracle.  All rights reserved.
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
    Start Date                27-AUG-2018 21:22:04
    Uptime                    0 days 0 hr. 0 min. 46 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Log File         /u01/base/diag/tnslsnr/rh7defs0/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh7defs0)(PORT=1521)))
    Services Summary...
    Service "7433c9568a2d6255e055000000000001" has 1 instance(s).
      Instance "CDB", status READY, has 1 handler(s) for this service...
    Service "CDB" has 1 instance(s).
      Instance "CDB", status READY, has 1 handler(s) for this service...
    Service "CDBXDB" has 1 instance(s).
      Instance "CDB", status READY, has 1 handler(s) for this service...
    Service "pdb" has 1 instance(s).
      Instance "CDB", status READY, has 1 handler(s) for this service...
    The command completed successfully
    

    I have checked Oracle Net connection with:

    $ sqlplus system/oracle@rh7defs0:1521/CDB 
    
    SQL*Plus: Release 18.0.0.0.0 - Production on Mon Aug 27 21:23:31 2018
    Version 18.3.0.0.0
    
    Copyright (c) 1982, 2018, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production
    Version 18.3.0.0.0
    
    SYSTEM@rh7defs0:1521/CDB>select host_name, instance_name from v$instance;
    
    HOST_NAME
    ----------------------------------------------------------------
    INSTANCE_NAME
    ----------------
    rh7defs0.localdomain
    CDB
    
    
    SYSTEM@rh7defs0:1521/CDB>
    

    Everything looks OK.

    Categories: DBA Blogs

    SQL Server Tips: Drop a database-user attached to a service…

    Yann Neuhaus - Wed, 2018-08-29 08:19

    Few weeks ago, I have a little issue when I try to drop a database-user without login

    Unfortunately, I do a little mistake at the beginning…
    I receive like every morning a report if all AD logins (computers, groups, users) registered on SQL server instances are in the AD with the useful command sp_validatelogins
    This report indicates that a computer name dbi\server_name$ was no more in the AD.
    I drop the login without problem and without verifying the binding with database-users (this was my mistake…). :-?

    The day after, I receive another alert that I have an orphan database-user on the SCOM database OperationManager12.

    My reaction was to connect to the instance and go dropping the user like usual when I become this alert.
    error_drop_server01
    As you can see, I receive the error message:
    Msg 15284, Level 16, State 1, Line 15
    The database principal has granted or denied permissions to objects in the database and cannot be dropped.

    I “google” the error and found some explanations.
    The user is owner of services in the service broker and I use this query to find the message:

    select * from sys.database_permissions where grantor_principal_id = user_id('dbi\server_name$')

    error_drop_server02
    The user is linked to a service number 65536. I search now the service linked to this number.
    error_drop_server03
    With the name of the service, I can revoke the SEND permission from this user.
    error_drop_server04
    And I receive this error:
    Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
    With the Google’s help, I re-try with the EXECUTE AS command with the server name as user:

    EXECUTE AS USER= 'dbi\server_name$'
    REVOKE SEND ON SERVICE::Service_mid10_39_40_55_pid4288_adid2_r479087710 FROM [dbi\server_name$]
    REVERT

    error_drop_server05
    As excepted, I receive a new error:
    Msg 15404, Level 16, State 11, Line 37
    Could not obtain information about Windows NT group/user ‘BISAD\WBNSS55$’, error code 0x534

    The login does not more exist, then it’s normal to have this error.
    And now, what to do?
    The only workaround that I found, is to drop the service, drop the user an recreate the service with dbo as owner(before dropping, create the create service statement before):

    DROP SERVICE [Service_mid10_39_40_55_pid4288_adid2_r479087710]
    GO

    error_drop_server06

    USE [OperationsManager12]
    GO
    DROP USER [dbi\server_name$]
    GO

    error_drop_server07

    CREATE SERVICE [Service_mid10_39_40_55_pid4288_adid2_r479087710]  ON QUEUE [dbo].[Queue_mid10_39_40_55_pid4288_adid2_r479087710] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
    GO

    error_drop_server08
    Et voila! It was a little bit tricky to find out a solution but this one works! 8-)

     

    Cet article SQL Server Tips: Drop a database-user attached to a service… est apparu en premier sur Blog dbi services.

    expdp several tables in join query

    Tom Kyte - Wed, 2018-08-29 07:26
    I need to expdp the 5 tables data which satisfy the below query,I had read some documents on MOS and found that the doc 'Export/Import DataPump Parameter QUERY - How to Specify a Query (Doc ID 277010.1)',which show the use of QUERY parameter,but the ...
    Categories: DBA Blogs

    check the 2nd line of a file

    Tom Kyte - Wed, 2018-08-29 07:26
    Hello, I need to check if a file content is written on a single line or it is slit in multiple line. For example, I want to make difference between the following 2 structures: 123'456'789' vs. 123' 456' 789' My only idea is to check...
    Categories: DBA Blogs

    to_date(varchar2) compared to datetime

    Tom Kyte - Wed, 2018-08-29 07:26
    I have VARCHAR2 DateTime string. I want to convert it into DateTime and compare it with DateTime in the where clause. to_Date(VARCHAR2 field, 'MM/DD/YYYY HH:MI:SS AM')>= '09/10/2008 08:31:10 AM' If I just do SELECT to_Date(VARCHAR2 field, 'MM...
    Categories: DBA Blogs

    Pages

    Subscribe to Oracle FAQ aggregator