Feed aggregator

Spool the file including headers

Tom Kyte - Wed, 2017-08-23 13:06
Hi, While spooling the fie how can I display which objects got spooled. for example, in my spooled file I want to display as below, spool file /u01/oracle/spool_test.sql # table creation script for emp_table table created. how can ...
Categories: DBA Blogs

Supplemental logging levels

Tom Kyte - Wed, 2017-08-23 13:06
Hi Ask Tom team, We are building a CDC from our Oracle OLTP systems, using Golden Gate or related technology. The developers, including myself, wanted to enable supplemental logging on all columns for us to build streaming analytics solutions, whi...
Categories: DBA Blogs

Reading deadlock trace files

Tom Kyte - Wed, 2017-08-23 13:06
Hi Tom ! How can I understand which two rows where involved in the following deadlock, which appears in this excerpt of a tracefile ? Especially in the last two lines, Oracle seems to convey this information, but I was not able to locate the o...
Categories: DBA Blogs

Alternative for GROUP_CONCAT() and FIND_IN_SET() in mysql for selecting values from a column with comma separated values.

Tom Kyte - Wed, 2017-08-23 13:06
Hi Team, I wanted to share a LiveSQL link but unfortunately I created the test case and somehow I lost the link. Here is my test case in documented format. - I have two tables - create table employee(empid number, empname varchar2(20), de...
Categories: DBA Blogs

processes parameter in oracle

Tom Kyte - Wed, 2017-08-23 13:06
Dear Tom, I have load testing on DR database now i have 4000 processes (show parameter processes), now i want to increase it on DR site and DR Database in Snapshot standby mode only. Doubts- 1. Can you explain what is the use of processes in ...
Categories: DBA Blogs

R and the Oracle database: Using dplyr / dbplyr with ROracle on Windows 10

Amis Blog - Wed, 2017-08-23 10:14

R uses data extensively. Data often resides in a database. In this blog I will describe installing and using dplyr, dbplyr and ROracle on Windows 10 to access data from an Oracle database and use it in R.

Accessing the Oracle database from R

dplyr makes the most common data manipulation tasks in R easier. dplyr can use dbplyr. dbplyr provides a transformation from the dplyr verbs to SQL queries. dbplyr 1.1.0 is released 2017-06-27. See here. It uses the DBI (R Database Interface). This interface is implemented by various drivers such as ROracle. ROracle is an Oracle driver based on OCI (Oracle Call Interface) which is a high performance native C interface to connect to the Oracle Database.

Installing ROracle on Windows 10

I encountered several errors when installing ROracle in Windows 10 on R 3.3.3. The steps to take to do this right in one go are the following:

  • Determine your R platform architecture. 32 bit or 64 bit. For me this was 64 bit
  • Download and install the oracle instant client with the corresponding architecture (here). Download the basic and SDK files. Put the sdk file from the sdk zip in a subdirectory of the extracted basic zip (at the same level as vc14)
  • Download and install RTools (here)
  • Set the OCI_LIB64 or OCI_LIB32 variables to the instant client path
  • Set the PATH variable to include the location of oci.dll
  • Install ROracle (install.packages(“ROracle”) in R)
Encountered errors

Warning in install.packages :
 package ‘ROracle_1.3-1.zip’ is not available (for R version 3.3.3)

You probably tried to install the ROracle package which Oracle provides on an R version which is too new (see here). This will not work on R 3.3.3. You can compile ROracle on your own or use the (older) R version Oracle supports.


Package which is only available in source form, and may need compilation of C/C++/Fortran: ‘ROracle’ These will not be installed

This can be fixed by installing RTools (here). This will install all the tools required to compile sources on a Windows machine.

Next you will get the following question:


Package which is only available in source form, and may need compilation of C/C++/Fortran: ‘ROracle’
Do you want to attempt to install these from sources?
y/n:

If you say y, you will get the following error:


installing the source package ‘ROracle’

trying URL 'https://cran.rstudio.com/src/contrib/ROracle_1.3-1.tar.gz'
Content type 'application/x-gzip' length 308252 bytes (301 KB)
downloaded 301 KB

* installing *source* package 'ROracle' ...
** package 'ROracle' successfully unpacked and MD5 sums checked
ERROR: cannot find Oracle Client.
 Please set OCI_LIB64 to specify its location.

In order to fix this, you can download and install the Oracle Instant Client (the basic and SDK downloads).

Mind that when running a 64 bit version of R, you also need a 64 bit version of the instant client. You can check with the R version command. In my case: Platform: x86_64-w64-mingw32/x64 (64-bit). Next you have to set the OCI_LIB64 variable (for 64 bit else OCI_LIB32) to the specified path. After that you will get the error as specified below:

Next it will fail with something like:


Error in inDL(x, as.logical(local), as.logical(now), ...) :
 unable to load shared object 'ROracle.dll':
 LoadLibrary failure: The specified module could not be found.

This is caused when oci.dll from the instant client is not in the path environment variable. Add it and it will work! (at least it did on my machine). The INSTALL file from the ROracle package contains a lot of information about different errors which can occur during installation. If you encounter any other errors, be sure to check it.

How a successful 64 bit compilation looks
> install.packages("ROracle")
Installing package into ‘C:/Users/maart_000/Documents/R/win-library/3.3’
(as ‘lib’ is unspecified)
Package which is only available in source form, and may need compilation of C/C++/Fortran: ‘ROracle’
Do you want to attempt to install these from sources?
y/n: y
installing the source package ‘ROracle’

trying URL 'https://cran.rstudio.com/src/contrib/ROracle_1.3-1.tar.gz'
Content type 'application/x-gzip' length 308252 bytes (301 KB)
downloaded 301 KB

* installing *source* package 'ROracle' ...
** package 'ROracle' successfully unpacked and MD5 sums checked
Oracle Client Shared Library 64-bit - 12.2.0.1.0 Operating in Instant Client mode.
found Instant Client C:\Users\maart_000\Desktop\instantclient_12_2
found Instant Client SDK C:\Users\maart_000\Desktop\instantclient_12_2/sdk/include
copying from C:\Users\maart_000\Desktop\instantclient_12_2/sdk/include
** libs
Warning: this package has a non-empty 'configure.win' file,
so building only the main architecture

c:/Rtools/mingw_64/bin/gcc  -I"C:/PROGRA~1/R/R-33~1.3/include" -DNDEBUG -I./oci    -I"d:/Compiler/gcc-4.9.3/local330/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c rodbi.c -o rodbi.o
c:/Rtools/mingw_64/bin/gcc  -I"C:/PROGRA~1/R/R-33~1.3/include" -DNDEBUG -I./oci    -I"d:/Compiler/gcc-4.9.3/local330/include"     -O2 -Wall  -std=gnu99 -mtune=core2 -c rooci.c -o rooci.o
c:/Rtools/mingw_64/bin/gcc -shared -s -static-libgcc -o ROracle.dll tmp.def rodbi.o rooci.o C:\Users\maart_000\Desktop\instantclient_12_2/oci.dll -Ld:/Compiler/gcc-4.9.3/local330/lib/x64 -Ld:/Compiler/gcc-4.9.3/local330/lib -LC:/PROGRA~1/R/R-33~1.3/bin/x64 -lR
installing to C:/Users/maart_000/Documents/R/win-library/3.3/ROracle/libs/x64
** R
** inst
** preparing package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded
* DONE (ROracle)
Testing ROracle

You can read the ROracle documentation here. Oracle has been so kind as to provide developer VM’s to play around with the database. You can download them here. I used ‘Database App Development VM’.

After installation of ROracle you can connect to the database and for example fetch employees from the EMP table. See for example below (make sure you also have DBI installed).

library("DBI")
library("ROracle")
drv <- dbDriver("Oracle")
host <- "localhost"
port <- "1521"
sid <- "orcl12c"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SID=", sid, ")))", sep = "")

con <- dbConnect(drv, username = "system", password = "oracle", dbname = connect.string, prefetch = FALSE,
bulk_read = 1000L, stmt_cache = 0L, external_credentials = FALSE,
sysdba = FALSE)

dbReadTable(con, "EMP")

This will yield the data in the EMP table.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 NA 30
2 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 NA 20
3 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 NA 20
4 7902 FORD ANALYST 7566 1981-12-02 23:00:00 3000 NA 20
5 7369 SMITH CLERK 7902 1980-12-16 23:00:00 800 NA 20
6 7499 ALLEN SALESMAN 7698 1981-02-19 23:00:00 1600 300 30
7 7521 WARD SALESMAN 7698 1981-02-21 23:00:00 1250 500 30
8 7654 MARTIN SALESMAN 7698 1981-09-27 23:00:00 1250 1400 30
9 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
10 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 NA 20
11 7900 JAMES CLERK 7698 1981-12-02 23:00:00 950 NA 30
Using dplyr

dplyr uses dbplyr and it makes working with database data a lot easier. You can see an example here.

Installing dplyr and dbplyr in R is easy:

install.packages("dplyr")
install.packages("dbplyr")

Various functions are provides to work with data.frames, a popular R datatype in combination with data from the database. Also dplyr uses an abstraction above SQL which makes coding SQL for non-SQL coders more easy. You can compare it in some ways with Hibernate which makes working with databases from the Java object world more easy.

Some functions dplyr provides:

  • filter() to select cases based on their values.
  • arrange() to reorder the cases.
  • select() and rename() to select variables based on their names.
  • mutate() and transmute() to add new variables that are functions of existing variables.
  • summarise() to condense multiple values to a single value.
  • sample_n() and sample_frac() to take random samples.

I’ll use the same example data as with the above sample which uses plain ROracle

library("DBI")
library("ROracle")
library("dplyr")

#below are required to make the translation done by dbplyr to SQL produce working Oracle SQL
sql_translate_env.OraConnection <- dbplyr:::sql_translate_env.Oracle
sql_select.OraConnection <- dbplyr:::sql_select.Oracle
sql_subquery.OraConnection <- dbplyr:::sql_subquery.Oracle

drv <- dbDriver("Oracle")
host <- "localhost"
port <- "1521"
sid <- "orcl12c"
connect.string <- paste(
"(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SID=", sid, ")))", sep = "")

con <- dbConnect(drv, username = "system", password = "oracle", dbname = connect.string, prefetch = FALSE,
bulk_read = 1000L, stmt_cache = 0L, external_credentials = FALSE,
sysdba = FALSE)

emp_db <- tbl(con, "EMP")
emp_db

The output is something like:

# Source: table<EMP> [?? x 8]
# Database: OraConnection
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
<int> <chr> <chr> <int> <dttm> <dbl> <dbl> <int>
1 7839 KING PRESIDENT NA 1981-11-16 23:00:00 5000 NA 10
2 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 NA 30
3 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 NA 10
4 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 NA 20
5 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 NA 20
6 7902 FORD ANALYST 7566 1981-12-02 23:00:00 3000 NA 20
7 7369 SMITH CLERK 7902 1980-12-16 23:00:00 800 NA 20
8 7499 ALLEN SALESMAN 7698 1981-02-19 23:00:00 1600 300 30
9 7521 WARD SALESMAN 7698 1981-02-21 23:00:00 1250 500 30
10 7654 MARTIN SALESMAN 7698 1981-09-27 23:00:00 1250 1400 30
# ... with more rows

If I now want to select specific records, I can do something like:

emp_db %>% filter(DEPTNO == "10")

Which will yield

# Source: lazy query [?? x 8]
# Database: OraConnection
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
<int> <chr> <chr> <int> <dttm> <dbl> <dbl> <int>
1 7839 KING PRESIDENT NA 1981-11-16 23:00:00 5000 NA 10
2 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 NA 10
3 7934 MILLER CLERK 7782 1982-01-22 23:00:00 1300 NA 10

A slightly more complex query:

emp_db %>%
group_by(DEPTNO) %>%
summarise(EMPLOYEES = count())

Will result in the number of employees per department:

# Source: lazy query [?? x 2]
# Database: OraConnection
DEPTNO EMPLOYEES
<int> <dbl>
1 30 6
2 20 5
3 10 3

You can see the generated query by:

emp_db %>%
group_by(DEPTNO) %>%
summarise(EMPLOYEES = count()) %>% show_query()

Will result in

<SQL>
SELECT "DEPTNO", COUNT(*) AS "EMPLOYEES"
FROM ("EMP")
GROUP BY "DEPTNO"

If I want to take a random sample from the dataset to perform analyses on, I can do:

sample_n(as_data_frame(emp_db), 10)

Which could result in something like:

# A tibble: 10 x 8
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
<int> <chr> <chr> <int> <dttm> <dbl> <dbl> <int>
1 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
2 7499 ALLEN SALESMAN 7698 1981-02-19 23:00:00 1600 300 30
3 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 NA 20
4 7654 MARTIN SALESMAN 7698 1981-09-27 23:00:00 1250 1400 30
5 7369 SMITH CLERK 7902 1980-12-16 23:00:00 800 NA 20
6 7902 FORD ANALYST 7566 1981-12-02 23:00:00 3000 NA 20
7 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 NA 30
8 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 NA 20
9 7934 MILLER CLERK 7782 1982-01-22 23:00:00 1300 NA 10
10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 NA 10

Executing the same command again will result in a different sample.

Finally

There are multiple ways to get data to and from the Oracle database and perform actions on them. Oracle provides Oracle R Enterprise. Oracle R Enterprise is a component of the Oracle Advanced Analytics Option of Oracle Database Enterprise Edition. You can create R proxy objects in your R session from database-resident data. This allows you to work on database data in R while the database does most of the computations. Another feature of Oracle R Enterprise is an R script repository in the database and there is also a feature to allow execution of R scripts from within the database (embedded), even within SQL statements. As you can imagine this is quite powerful. More on this in a later blog!

The post R and the Oracle database: Using dplyr / dbplyr with ROracle on Windows 10 appeared first on AMIS Oracle and Java Blog.

Partner Webcast - Storage-oriented Bundles: Exadata with Recovery Appliance (ZDLRA)

Join us to learn more about Oracle Systems, Engineered Systems and Storage. Learn what we understand by “Cloud Insurance” through our Systems Webcast Series for EMEA...

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

Video: Oracle Mobile Cloud Service Integration Options

OTN TechBlog - Wed, 2017-08-23 06:00

Oracle Mobile Cloud Service (OMCS) might not be the first product you think of when thinking about integration, according to Oracle ACE Associate Maarten Smeets. "But it does have many useful options for integration."

As Maarten explains in this 2 Minute Tech Tip, for authentication OMCS can use third party SAML and JWT tokens, Facebook logins, Oracle Cloud accounts, or OAuth and basic authentication. OMCS can also deal with Beacon package formats including AltBeacon, iBeacon, and Eddystone. It can use Google Cloud Messaging, Firebase Cloud Messaging, Syniverse and other messaging services, "and it comes with client SDKs for Android, iOS, Windows Mobile, and plain JavaScript," Maarten says.

Maarten packs a lot more information into his short video. So check it out!

Additional Recources

Video: Oracle Mobile Cloud Service Integration Options

OTN TechBlog - Wed, 2017-08-23 06:00

Oracle Mobile Cloud Service (OMCS) might not be the first product you think of when thinking about integration, according to Oracle ACE Associate Maarten Smeets. "But it does have many useful options for integration."

As Maarten explains in this 2 Minute Tech Tip, for authentication OMCS can use third party SAML and JWT tokens, Facebook logins, Oracle Cloud accounts, or OAuth and basic authentication. OMCS can also deal with Beacon package formats including AltBeacon, iBeacon, and Eddystone. It can use Google Cloud Messaging, Firebase Cloud Messaging, Syniverse and other messaging services, "and it comes with client SDKs for Android, iOS, Windows Mobile, and plain JavaScript," Maarten says.

Maarten packs a lot more information into his short video. So check it out!

Additional Recources

Bequeath connect to PDB: set container in logon trigger?

Yann Neuhaus - Wed, 2017-08-23 00:54

There are little changes when you go to multitenant architecture and one of them is that you must connect with a service name. You cannot connect directly to a PDB with a beaqueath (aka local) connection. This post is about a workaround you may have in mind: create a common user and set a logon trigger to ‘set container’. I do not recommend it and you should really connect with a service. Here is an example.

Imagine that I have a user connecting with bequeath connection to a non-CDB, using user/password without a connection string, the database being determined by the ORACLE_SID. And I want to migrate to CDB without changing anything on the client connection configuration side. The best idea would be to use a service, explicitly or implicitly with TWO_TASK or LOCAL. But let’s imagine that you don’t want to change anything on the client side.

As we can connect only the the CDB$ROOT with a bequeath connection, we have to create a common user. Because the idea is not to change anything on client configuration, and there’s a very little chance that the user starts with C## I’ll start to remove the mandatory prefix for common users.


SQL> show parameter common_user_prefix
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix string
 
SQL> alter system set common_user_prefix='' scope=spfile;
System altered.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
...

Then I create my common user:

SQL> create user MYOLDUSER identified by covfefe container=all;
User created.

This user must be able to connect to the CDB:

SQL> grant create session to MYOLDUSER container=current;
Grant succeeded.

And then I want it to switch immediately to PDB1 using a logon trigger:

SQL> create or replace trigger SET_CONTAINER_AT_LOGON after logon on database
2 when (user in ('MYOLDUSER'))
3 begin
4 execute immediate 'alter session set container=PDB1';
5 end;
6 /
Trigger created.

Once on PDB1 this user will have some privileges, and for the example I will grant him a default role:

SQL> alter session set container=PDB1;
Session altered.
 
SQL> create role MYROLE;
Role created.
 
SQL> grant MYROLE to MYOLDUSER container=current;
Grant succeeded.

The documentation says that When you grant a role to a user, the role is granted as a default role for that user and is therefore enabled immediately upon logon so I don’t need to:

SQL> alter user MYOLDUSER default role MYROLE;
User altered.

But the doc say ‘logon’ and technically I do not logon to PDB1. I just set container. However, if you test it you will see that default roles are set also on ‘set container’. And anyway, we cannot set a role in a procedure, neither with ‘set role’ nor with dbms_session.set_role:

ORA-06565: cannot execute SET ROLE from within stored procedure

Then, I can now connect locally to the CDB$ROOT with this user:

SQL> connect MYOLDUSER/covfefe
Connected.

And I’m automatically switched to the PDB1:

SQL> show con_name
 
CON_NAME
------------------------------
PDB1

Issue #1: default roles

However the default roles are not set:

SQL> select * from session_roles;
 
no rows selected

I have to set the role once connected:

SQL> set role all;
Role set.
 
SQL> select * from session_roles;
 
ROLE
--------------------------------------------------------------------------------
MYROLE

This is probably not what we want when we cannot change anything on the application side. This is considered as a bug (Bug 25081564 : ALTER SESSION SET CONTAINER IN “ON LOGON TRIGGER” IS NOT WORKING) fixed in 18.1 (expected in Q1 2018) and there’s a patch for 12.1 and 12.2 https://updates.oracle.com/download/25081564.html

Issue #2: core dump

There’s another issue. If you run the same with SQLcl you have a core dump in the client library libclntsh.so on kpuSetContainerNfy

SQLcl: Release 17.2.0 Production on Tue Aug 22 22:00:52 2017
 
Copyright (c) 1982, 2017, Oracle. All rights reserved.
 
SQL> connect MYOLDUSER/covfefe
#
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x00007fcaa172faf6, pid=31242, tid=140510230116096
#
# JRE version: Java(TM) SE Runtime Environment (8.0_91-b14) (build 1.8.0_91-b14)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.91-b14 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [libclntsh.so.12.1+0x11d8af6] kpuSetContainerNfy+0x66
#
# Core dump written. Default location: /media/sf_share/122/blogs/core or core.31242

There’s a SR opened for that. This is not a no-go because the context being no change to the client part, then sqlplus will probably be used. However, that’s another point which shows that ‘set container’ in a logon trigger may have some implementation problems.

Issue #3: security

In my opinion, there is a bigger problem here. With sqlplus (or with sqlcl not using local connection) I can connect to the CDB$ROOT and switch to PDB1. But look at all the commands above… where did I grant the ‘set container’ privilege for MYOLDUSER on the PDB1 container? Nowhere. MYOLDUSER has no create session and no set container privileges, but is able to connect to PDB1 thanks to the logon trigger. Of course , the logon trigger is defined by a DBA who knows what he does. But in my opinion, it is not a good idea to bypass the privilege checking.

So what?

With no default role, connecting without the right privilege, the security model is biased here. And disabling the common user prefix will raise other issues one day with plugging operations. Then, in my opinion, this is not a solution to workaround the need to connect with a service. Especially in the context where we run legacy application with no possibility to change the way it connects: you just postpone the problems to bigger ones later.

The real solution is to connect to a service (and that’s not difficult even when you can’t change the code, with TWO_TASK environment variable).

 

Cet article Bequeath connect to PDB: set container in logon trigger? est apparu en premier sur Blog dbi services.

Performance issues

Tom Kyte - Tue, 2017-08-22 18:46
I'm getting performance issues while running the below query. Its taking 7 minutes to give results Table - V_DM_test_VISIT contains 25 million records table - V_DM_PLANNED_VISIT contains 60 K records I'm using analytical function to find o...
Categories: DBA Blogs

User-Defined Aggregate function with more than 1 argument

Tom Kyte - Tue, 2017-08-22 18:46
Is it possible to create a user-defined aggregate function with more than 1 argument? Let say, I would like to create TOP_NTH(number, integer) function that takes a number (for a column) and integer indicating the ranking of the top value. Such funct...
Categories: DBA Blogs

Unable to set pctversion to 0

Tom Kyte - Tue, 2017-08-22 18:46
Hello Tom, I am trying below code to reset table pctversion to 0 from default 10. but on Oracle 12c 12.1.0.2 it won't work, but the same command works on Oracle 12c 12.2.0.1 and also works on 11gR2. <code> alter table ev_log modify lob (event_...
Categories: DBA Blogs

Performance of querying CHAR columns

Tom Kyte - Tue, 2017-08-22 18:46
I wasn't able to parameterize our queries because our database schema uses a lot of CHAR types on key fields, but was told to use the LIKE operator instead of =. This did allow the parameterized queries to work, but I'm concerned how this will effect...
Categories: DBA Blogs

Remove redundant entries from table

Tom Kyte - Tue, 2017-08-22 18:46
Hi Experts, Please find below table & data. <code>create table orders ( order_id varchar2(10),quantity varchar2(10), price varchar2(10), id varchar2(10)); insert into orders values ('O1','2','100','id1'); insert into orders values ('O1','3'...
Categories: DBA Blogs

Load XML File (Physical file) using SQL Loader into a XMLType Column

Tom Kyte - Tue, 2017-08-22 18:46
Hello Tom I need to load a XML File, which is available on the oracle server using a Host Program in Oracle Applications. I have done the below 1. Created a Host Concurrent Program 2. Call the SQL Loader Control File from the PROG File. But...
Categories: DBA Blogs

ORA-00972: identifier is too long

Tom Kyte - Tue, 2017-08-22 18:46
I have a question about "Identifier too long" error. I understand if I am trying to create a column name that is too long and oracle complains on it. but if I do a select and alais it my own text, why is this a problem ? Here is a sample: <co...
Categories: DBA Blogs

Customizing a CoreOS image/iso: Ignition

Dietrich Schroff - Tue, 2017-08-22 14:48
After the first steps with coreos (virtualbox installation) and a successful ssh login, it is clear, that the coreos image has to be customized. If not, every reboot will clean up every configuration provided after reboot.

CoreOS provides a coreos-install script to do this.
coreos-install -help
Usage: ./coreos-install [-C channel] -d /dev/device
Options:
    -d DEVICE   Install Container Linux to the given device.
    -V VERSION  Version to install (e.g. current) [default: current]
    -B BOARD    Container Linux board to use [default: amd64-usr]
    -C CHANNEL  Release channel to use (e.g. beta) [default: stable]
    -o OEM      OEM type to install (e.g. ami) [default: (none)]
    -c CLOUD    Insert a cloud-init config to be executed on boot.
    -i IGNITION Insert an Ignition config to be executed on boot.
    -b BASEURL  URL to the image mirror (overrides BOARD)
    -k KEYFILE  Override default GPG key for verifying image signature
    -f IMAGE    Install unverified local image file to disk instead of fetching
    -n          Copy generated network units to the root partition.
    -v          Super verbose, for debugging.
    -h          This ;-)

This tool installs CoreOS Container Linux on a block device. If you PXE booted
Container Linux on a machine then use this tool to make a permanent install.
First you have to add a 8GB partition to your core os installation:

 

Then enable ssh login (please refer to this posting). And transfer the coreos-install script and the following ingnition.json file:
{
  "ignition": {
    "version": "2.0.0",
    "config": {}
  },
  "storage": {},
  "systemd": {},
  "networkd": {},
  "passwd": {
    "users": [
      {
        "name": "core",
        "sshAuthorizedKeys": [
          "ssh-rsa AAAAB3NzaC....  "
        ]
      }
    ]
  }
}
Please use this validator to check your ignition.json!
And here we go:
core@localhost ~ $ ./coreos-install -d /dev/sda -i ignition.json
./coreos-install: Target block device (/dev/sda) is not writable (are you root?)Ok.
sudo bash
bash-4.3#./coreos-install -d /dev/sda -i ignition.json
Downloading the signature for https://stable.release.core-os.net/amd64-usr/1409.6.0/coreos_production_image.bin.bz2...
wget --no-verbose -O /tmp/coreos-install.JROwzOsi1W/coreos_production_image.bin.bz2.sig https://stable.release.core-os.net/amd64-usr/1409.6.0/coreos_production_image.bin.bz2.sig
2017-08-22 19:01:28 URL:https://stable.release.core-os.net/amd64-usr/1409.6.0/coreos_production_image.bin.bz2.sig [543/543] -> "/tmp/coreos-install.JROwzOsi1W/coreos_production_image.bin.bz2.sig" [1]
Downloading, writing and verifying coreos_production_image.bin.bz2...
2017-08-22 19:02:31 URL:https://stable.release.core-os.net/amd64-usr/1409.6.0/coreos_production_image.bin.bz2 [288249718/288249718] -> "-" [1]
gpg: Signature made Thu Jul  6 01:34:01 2017 UTC
gpg:                using RSA key 07FA9ED31CB5FA26
gpg: key 50E0885593D2DCB4 marked as ultimately trusted
gpg: checking the trustdb
gpg: marginals needed: 3  completes needed: 1  trust model: pgp
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
gpg: Good signature from "CoreOS Buildbot (Offical Builds) " [ultimate]
Installing Ignition config ignition.json...
Success! CoreOS Container Linux stable 1409.6.0 is installed on /dev/sda
Then umount the cd drive from your coreos virtual machine:



and reboot:
But this does not work:

Failed to start Ignition (disks)and
See 'systemctl status ignition-disks.service'

Hmmm... I found nothing with google, so i file an issue at github:
https://github.com/coreos/bugs/issues/2118
And got a the solution within 6 minutes!!!

I missed the closing quite in line 14:
"ssh-rsa AAAAB3NzaC....  " And now it worked:


Really cool...

If you are interested in more details please check this site:

CoreOS: structure of the ISO image

Dietrich Schroff - Tue, 2017-08-22 14:40
After some first steps with CoreOS ("First step to micorservices: CoreOS on virtualbox") my problem is, how to customize CoreOS. After each reboot all changes are gone (tmpfs / squashfs).

There are some tutorials which recommend the usage of etcd, but even this has to be configured into the CoreOS linux...

The first idea: what about customizing the ISO image...

mount -o loop coreos_production_iso_image.iso /media/isoInside this:
$ ls
coreos  isolinux  syslinux
du -sh *
276M    coreos
1,9M    isolinux
2,5K    syslinuxand inside the coreos directory:
ls -l coreos
insgesamt 282349
-r--r--r-- 1 root root 253738975 Jul  6 03:47 cpio.gz
-r--r--r-- 1 root root  35385632 Jul  6 03:47 vmlinuzHmmm.
$ gunzip cpio.gz
$ cat cpio | cpio -idmv
.
usr.squashfs
etcThe directory etc is empty. And usr.sqashfs can be mounted:
mount usr.squashfs /media/sqashfs and here we go:
du -sh *
221M    bin
34M    boot
512    lib
240M    lib64
18M    libexec
2,5K    local
0    lost+found
512    postinst
29M    sbin
69M    share
512    tmpI think modifying these files is not really helpful. Although there is no home directory to install my authorized_keys file.

CoreOS itself states:
But i hope there is another way without ignition to modify the coreos. Stay tuned ;-)

Enable Windows Instance access via RDP on Oracle Compute Cloud Service

Oracle Compute Cloud Service optimized for Oracle Public Cloud Machine (OPCM) provides Infrastructure as a Service (IaaS) and also acts as the base on which the other cloud...

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

Pages

Subscribe to Oracle FAQ aggregator