DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Converting SQL*Plus calls in shell scripts to ORDS calls

Posted on August 31, 2018 by Ludovico

I develop a lot of shell scripts. I would not define myself an old dinosaur that keeps avoiding python or other modern languages. It is just that most of my scripts automate OS commands that I would normally run interactively in an interactive shell… tar, cp, expdp, rman, dgmgrl, etc… and of course, some SQL*Plus executions.

For database calls, the shell is not appropriate: no drivers, no connection, no statement, no resultset… that’s why I need to make SQL*Plus executions (with some hacks to make them work correctly), and that’s also why I normally use python or perl for data-related tasks.

Using SQL*Plus in shell scripts

For SQL*Plus executions within a shell scripts there are some hacks, as I have said, that allow to get the data correctly.

As example, let’s use this table (that you might have found in my recent posts):

SQL> desc OH_GOLDEN_IMAGES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(50)
 OH_TYPE                                            VARCHAR2(10)
 VERSION                                            VARCHAR2(10)
 FULLPATH                                           VARCHAR2(200)
 CREATED                                            TIMESTAMP(6)
 DESCRIPTION                                        VARCHAR2(2000)

SQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb1', 'RDBMS', '18.3.0', '/test/path/18_3_0_cerndb1.zip', sysdate-10, 'First version 18.3.0');

1 row created.

SQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb2', 'RDBMS', '18.3.0', '/test/path/18_3_0_cerndb2.zip', sysdate-1, '18_3_0_cerndb1 + Patch XXX');

1 row created.

SQL> commit;

Commit complete.

SQL> desc OH_GOLDEN_IMAGES

Name Null? Type

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

NAME NOT NULL VARCHAR2(50)

OH_TYPE VARCHAR2(10)

VERSION VARCHAR2(10)

FULLPATH VARCHAR2(200)

CREATED TIMESTAMP(6)

DESCRIPTION VARCHAR2(2000)

SQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb1', 'RDBMS', '18.3.0', '/test/path/18_3_0_cerndb1.zip', sysdate-10, 'First version 18.3.0');

1 row created.

SQL> insert into OH_GOLDEN_IMAGES values ('18_3_0_cerndb2', 'RDBMS', '18.3.0', '/test/path/18_3_0_cerndb2.zip', sysdate-1, '18_3_0_cerndb1 + Patch XXX');

1 row created.

SQL> commit;

Commit complete.

In order to get, as example, the result of this query:

SELECT name, version, fullpath, TO_CHAR(created,'YYYY-MM-DD') as created
FROM oh_golden_images WHERE oh_type='RDBMS' order by created

1 2	SELECT name, version, fullpath, TO_CHAR(created,'YYYY-MM-DD') as created FROM oh_golden_images WHERE oh_type='RDBMS' order by created

and assign the values to some variables (in a shell loop), it is common to do something like this:

REPO_CREDENTIALS='scott/tiger@orcl'
RESULT=`$ORACLE_HOME/bin/sqlplus -s $REPO_CREDENTIALS 2>&1 <<EOF | grep ";"
        set line 200 pages 1000
        set echo off feedback off heading off
        alter session set nls_timestamp_format='YYYY-MM-DD';
        SELECT name || ';' ||version || ';' || fullpath || ';' || created
          FROM oh_golden_images
        WHERE oh_type='RDBMS'
           order by created;
        exit;
EOF
`

for line in $RESULT ; do
        L_GI_Name=`echo $line | awk -F\; '{print $1}'`
        L_GI_Version=`echo $line | awk -F\; '{print $2}'`
        L_GI_Path=`echo $line | awk -F\; '{print $3}'`
        L_GI_Date=`echo $line | awk -F\; '{print $4}'`
        echo "doing something with variables $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version"
done

REPO_CREDENTIALS='scott/tiger@orcl'

RESULT=`$ORACLE_HOME/bin/sqlplus -s $REPO_CREDENTIALS 2>&1 <<EOF | grep ";"

set line 200 pages 1000

set echo off feedback off heading off

alter session set nls_timestamp_format='YYYY-MM-DD';

SELECT name || ';' ||version || ';' || fullpath || ';' || created

FROM oh_golden_images

WHERE oh_type='RDBMS'

order by created;

exit;

EOF

for line in $RESULT ; do

L_GI_Name=`echo $line | awk -F\; '{print $1}'`

L_GI_Version=`echo $line | awk -F\; '{print $2}'`

L_GI_Path=`echo $line | awk -F\; '{print $3}'`

L_GI_Date=`echo $line | awk -F\; '{print $4}'`

echo "doing something with variables $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version"

done

As you can see, there are several hacks:

The credentials must be defined somewhere (I recommend putting them in a wallet)
All the output goes in a variable (or looping directly)
SQL*Plus formatting can be a problem (both sqlplus settings and concatenating fields)
Loop and get, for each line, the variables (using awk in my case)

It is not rock solid (unexpected data might compromise the results) and there are dependencies (sqlplus binary, credentials, etc.). But for many simple tasks, that’s more than enough.

Here’s the output:

$ sh sqlplus_test.sh
doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/18_3_0_cerndb1.zip 18.3.0
doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/18_3_0_cerndb2.zip 18.3.0

$ sh sqlplus_test.sh

doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/18_3_0_cerndb1.zip 18.3.0

doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/18_3_0_cerndb2.zip 18.3.0

Using ORDS instead

Recently I have come across a situation where I had no Oracle binaries but needed to get some data from a table. That is often a situation where I use python or perl, but even in these cases, I need compatible software and drivers!

So I used ORDS instead (that by chance, was already configured for the databases I wanted to query), and used curl and jq to get the data in the shell script.

First, I have defined the service in the database:

BEGIN
  ORDS.DEFINE_SERVICE(
    p_module_name    => 'ohctl',
    p_base_path      => 'ohctl/',
    p_pattern        => 'list/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT name, version, fullpath, TO_CHAR(created,''YYYY-MM-DD'') as created FROM oh_golden_images WHERE oh_type=''RDBMS'' order by created',
    p_items_per_page => 0);
	COMMIT;
END;
/

BEGIN

ORDS.DEFINE_SERVICE(

p_module_name => 'ohctl',

p_base_path => 'ohctl/',

p_pattern => 'list/',

p_method => 'GET',

p_source_type => ORDS.source_type_collection_feed,

p_source => 'SELECT name, version, fullpath, TO_CHAR(created,''YYYY-MM-DD'') as created FROM oh_golden_images WHERE oh_type=''RDBMS'' order by created',

p_items_per_page => 0);

COMMIT;

END;

At this point, a direct call gives this:

$ curl $rest_ep/ohctl/list/
{"items":[{"name":"18_3_0_cerndb1","version":"18.3.0","fullpath":"/test/path/18_3_0_cerndb1.zip","created":"2018-08-19"},{"name":"18_3_0_cerndb2","version":"18.3.0","fullpath":"/test/path/18_3_0_cerndb2.zip","created":"2018-08-28"}],"hasMore":false,"limit":0,"offset":0,"count":2,"links":[{"rel":"self","href":"https://rest_endpoint/ohctl/list/"},{"rel":"describedby","href":"https://rest_endpoint/metadata-catalog/ohctl/list/"}]}

$ curl $rest_ep/ohctl/list/

{"items":[{"name":"18_3_0_cerndb1","version":"18.3.0","fullpath":"/test/path/18_3_0_cerndb1.zip","created":"2018-08-19"},{"name":"18_3_0_cerndb2","version":"18.3.0","fullpath":"/test/path/18_3_0_cerndb2.zip","created":"2018-08-28"}],"hasMore":false,"limit":0,"offset":0,"count":2,"links":[{"rel":"self","href":"https://rest_endpoint/ohctl/list/"},{"rel":"describedby","href":"https://rest_endpoint/metadata-catalog/ohctl/list/"}]}

How to parse the data?

jq is a command-line JSON processor that can be used in a pipeline.

I can get the items:

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[]'
{
  "created": "2018-08-19",
  "fullpath": "/test/path/18_3_0_cerndb1.zip",
  "version": "18.3.0",
  "name": "18_3_0_cerndb1"
}
{
  "created": "2018-08-28",
  "fullpath": "/test/path/18_3_0_cerndb2.zip",
  "version": "18.3.0",
  "name": "18_3_0_cerndb2"
}

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output '.items[]'

{

"created": "2018-08-19",

"fullpath": "/test/path/18_3_0_cerndb1.zip",

"version": "18.3.0",

"name": "18_3_0_cerndb1"

}

{

"created": "2018-08-28",

"fullpath": "/test/path/18_3_0_cerndb2.zip",

"version": "18.3.0",

"name": "18_3_0_cerndb2"

}

And I can produce a csv output:

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[] | @csv "\([.created]),\([.fullpath]),\([.version]),\([.name])"'
"2018-08-19","/test/path/18_3_0_cerndb1.zip","18.3.0","18_3_0_cerndb1"
"2018-08-28","/test/path/18_3_0_cerndb2.zip","18.3.0","18_3_0_cerndb2"

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output '.items[] | @csv "\([.created]),\([.fullpath]),\([.version]),\([.name])"'

"2018-08-19","/test/path/18_3_0_cerndb1.zip","18.3.0","18_3_0_cerndb1"

"2018-08-28","/test/path/18_3_0_cerndb2.zip","18.3.0","18_3_0_cerndb2"

But the best, is the shell formatter, that returns strings properly escaped for usage in shell commands:

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[] | @sh "L_GI_Date=\([.created]); L_GI_Path=\([.fullpath]); L_GI_Version=\([.version]); L_GI_Name=\([.name])"'
L_GI_Date='2018-08-19'; L_GI_Path='/test/path/18_3_0_cerndb1.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb1'
L_GI_Date='2018-08-28'; L_GI_Path='/test/path/18_3_0_cerndb2.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb2'

$ curl -s $rest_ep/ohctl/list/ | jq --raw-output '.items[] | @sh "L_GI_Date=\([.created]); L_GI_Path=\([.fullpath]); L_GI_Version=\([.version]); L_GI_Name=\([.name])"'

L_GI_Date='2018-08-19'; L_GI_Path='/test/path/18_3_0_cerndb1.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb1'

L_GI_Date='2018-08-28'; L_GI_Path='/test/path/18_3_0_cerndb2.zip'; L_GI_Version='18.3.0'; L_GI_Name='18_3_0_cerndb2'

At this point, the call to eval is a natural step 🙂

IFS="
"
for line in `curl -s $rest_ep/ohctl/list/ | jq --raw-output  '.items[] | @sh "L_GI_Date=\([.created]); L_GI_Path=\([.fullpath]); L_GI_Version=\([.version]); L_GI_Name=\([.name])"'` ; do
        eval $line
        echo "doing something with values $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version"
done

IFS="

for line in `curl -s $rest_ep/ohctl/list/ | jq --raw-output '.items[] | @sh "L_GI_Date=\([.created]); L_GI_Path=\([.fullpath]); L_GI_Version=\([.version]); L_GI_Name=\([.name])"'` ; do

eval $line

echo "doing something with values $L_GI_Name $L_GI_Date $L_GI_Path $L_GI_Version"

done

The output:

$ sh ords_test.sh
doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/18_3_0_cerndb1.zip 18.3.0
doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/18_3_0_cerndb2.zip 18.3.0

$ sh ords_test.sh

doing something with values 18_3_0_cerndb1 2018-08-19 /test/path/18_3_0_cerndb1.zip 18.3.0

doing something with values 18_3_0_cerndb2 2018-08-28 /test/path/18_3_0_cerndb2.zip 18.3.0

😉

—

Ludovico

Oracle Home Management – part 7: Putting all together

Posted on May 30, 2018 by Ludovico

Last part of the blog series… let’s see how to put everything together and have a single script that creates and provisions Oracle Home golden images:

Review of the points

The scripts will:

let create a golden image based on the current Oracle Home
save the golden image metadata into a repository (an Oracle schema somewhere)
list the avilable golden images and display whether they are already deployed on the current host
let provision an image locally (pull, not push), either with the default name or a new name

Todo:

Run as root in order to run root.sh automatically (or let specify the sudo command or a root password)
Manage Grid Infrastructure homes

Assumptions

There is an available Oracle schema where the golden image metadata will be stored
There is an available NFS share that contains the working copies and golden images
Some variables must be set accordingly to the environment in the script
The function setoh is defined in the environment (it might be copied inside the script)
The Instant Client is installed and “setoh ic” correctly sets its environment. This is required because there might be no sqlplus binaries available at the very first deploy
Oracle Home name and path’s basename are equal for all the Oracle Homes

Repository table

First we need a metadata table. Let’s keep it as simple as possible:

CREATE TABLE "OH_GOLDEN_IMAGES"  (
     NAME VARCHAR2(50 BYTE)
   , FULLPATH VARCHAR2(200 BYTE)
   , CREATED TIMESTAMP (6)
   , CONSTRAINT PK_OH_GOLDEN_IMAGES PRIMARY KEY (NAME)
);

CREATE TABLE "OH_GOLDEN_IMAGES" (

NAME VARCHAR2(50 BYTE)

, FULLPATH VARCHAR2(200 BYTE)

, CREATED TIMESTAMP (6)

, CONSTRAINT PK_OH_GOLDEN_IMAGES PRIMARY KEY (NAME)

);

Helpers

The script has some functions that check stuff inside the central inventory.

e.g.

F_OH_Installed () {
        CENTRAL_ORAINV=`grep ^inventory_loc /etc/oraInst.loc | awk -F= '{print $2}'`
        grep "<HOME NAME=\"$1\"" $CENTRAL_ORAINV/ContentsXML/inventory.xml | grep -v "REMOVED=\"T\"" >/dev/null
        if [ $? -eq 0 ] ; then
                echo -e "${colgrn}Installed${colrst}"
        else
                echo "Not installed"
        fi
}

F_OH_Installed () {

CENTRAL_ORAINV=`grep ^inventory_loc /etc/oraInst.loc | awk -F= '{print $2}'`

grep "<HOME NAME=\"$1\"" $CENTRAL_ORAINV/ContentsXML/inventory.xml | grep -v "REMOVED=\"T\"" >/dev/null

if [ $? -eq 0 ] ; then

echo -e "${colgrn}Installed${colrst}"

else

echo "Not installed"

}

checks if a specific Oracle Home (name) is present in the central inventory. It is helpful to check, for every golden image in the matadata repository, if it is already provisioned or not:

F_list_OH () {
 
        F_colordef
        echo
        echo "Listing existing golden images:"
 
        RESULT=`$SQLPLUS -S -L ${REPO_CREDENTIALS} <<EOF  | grep ";"
        set line 200 pages 1000
        set feed off head off
        col name format a32
        alter session set nls_timestamp_format='YYYY-MM-DD';
        select name||';'||created||';'||fullpath from oh_golden_images order by created desc;
EOF
`
        echo
        printf "%-35s %-10s %-18s\n" "OH_Name" "Created" "Installed locally?"
        echo "----------------------------------- ---------- ------------------"
 
        for line in $RESULT ; do
                L_GI_Name=`echo $line | awk -F\; '{print $1}'`
                L_GI_Date=`echo $line | awk -F\; '{print $2}'`
                L_GI_Path=`echo $line | awk -F\; '{print $3}'`
                L_Installed=`F_OH_Installed "$L_GI_Name"`
                printf "%-35s %-10s %-18s\n" "$L_GI_Name" "$L_GI_Date" "$L_Installed"
        done
}

F_list_OH () {

F_colordef

echo

echo "Listing existing golden images:"

RESULT=`$SQLPLUS -S -L ${REPO_CREDENTIALS} <<EOF | grep ";"

set line 200 pages 1000

set feed off head off

col name format a32

alter session set nls_timestamp_format='YYYY-MM-DD';

select name||';'||created||';'||fullpath from oh_golden_images order by created desc;

EOF

echo

printf "%-35s %-10s %-18s\n" "OH_Name" "Created" "Installed locally?"

echo "----------------------------------- ---------- ------------------"

for line in $RESULT ; do

L_GI_Name=`echo $line | awk -F\; '{print $1}'`

L_GI_Date=`echo $line | awk -F\; '{print $2}'`

L_GI_Path=`echo $line | awk -F\; '{print $3}'`

L_Installed=`F_OH_Installed "$L_GI_Name"`

printf "%-35s %-10s %-18s\n" "$L_GI_Name" "$L_GI_Date" "$L_Installed"

done

}

Variables

Some variables must be changed, but in general you might want to adapt the whole script to fit your needs.

REPO_OWNER=scott
REPO_PWD=tiger
REPO_CONN="//localhost:1521/ORCL"
REPO_CREDENTIALS=${REPO_OWNER}/${REPO_PWD}@${REPO_CONN}
 
PRODUCT_INSTALL_PATH="/u01/app/oracle/product"
GOLDEN_IMAGE_DEST="/share/oracle/oh_repository/golden_images"
WORKING_COPY_DEST="/share/oracle/oh_repository/working_copies"

REPO_OWNER=scott

REPO_PWD=tiger

REPO_CONN="//localhost:1521/ORCL"

REPO_CREDENTIALS=${REPO_OWNER}/${REPO_PWD}@${REPO_CONN}

PRODUCT_INSTALL_PATH="/u01/app/oracle/product"

GOLDEN_IMAGE_DEST="/share/oracle/oh_repository/golden_images"

WORKING_COPY_DEST="/share/oracle/oh_repository/working_copies"

Image creation

The image creation would be as easy as creating a zip file, but there are some files that we do not want to include in the golden image, therefore we need to create a staging directory (working copy) to clean up everything:

        # Copy to NFS working copy
        echo "Cleaning previous working copy"
        WC=$WORKING_COPY_DEST/$L_New_Name
        [ -d $WC ] && rm -rf $WC
 
        echo "Copying the OH to the working copy"
        mkdir -p  $WC
        cp -rp $ORACLE_HOME/* $WC/ 2>/tmp/ohctl.err
 
        # Cleanup files
        echo "Cleansing files in Working Copy"
        rm -rf $WC/log/$HOSTNAME
        rm -rf $WC/log/diag/rdbms/*
        rm -rf $WC/gpnp/$HOSTNAME
        find $WC/gpnp -type f -exec rm {} \; 2>/dev/null
        rm -rf $WC/cfgtoollogs/*
        rm -rf $WC/crs/init/*
        rm -rf $WC/cdata/*
        rm -rf $WC/crf/*
        rm -rf $WC/admin/*
        rm -rf $WC/network/admin/*.ora
        rm -rf $WC/crs/install/crsconfig_params
        find $WC -name '*.ouibak' -exec rm {} \; 2>/dev/null
        find $WC -name '*.ouibak.1' -exec rm {} \; 2>/dev/null
        # rm -rf $WC/root.sh
        find $WC/rdbms/audit -name '*.aud' -exec rm {} \; 2>/dev/null
        rm -rf $WC/rdbms/log/*
        rm -rf $WC/inventory/backup/*
        rm -rf $WC/dbs/*
 
        # create zip
        echo "Creating the Golden Image zip file"
        [ -f $GOLDEN_IMAGE_DEST/$L_New_Name.zip ] && rm $GOLDEN_IMAGE_DEST/$L_New_Name.zip
        pushd $WC
        zip -r $GOLDEN_IMAGE_DEST/$L_New_Name.zip . >/dev/null
        popd $OLDPWD

# Copy to NFS working copy

echo "Cleaning previous working copy"

WC=$WORKING_COPY_DEST/$L_New_Name

[ -d $WC ] && rm -rf $WC

echo "Copying the OH to the working copy"

mkdir -p $WC

cp -rp $ORACLE_HOME/* $WC/ 2>/tmp/ohctl.err

# Cleanup files

echo "Cleansing files in Working Copy"

rm -rf $WC/log/$HOSTNAME

rm -rf $WC/log/diag/rdbms/*

rm -rf $WC/gpnp/$HOSTNAME

find $WC/gpnp -type f -exec rm {} \; 2>/dev/null

rm -rf $WC/cfgtoollogs/*

rm -rf $WC/crs/init/*

rm -rf $WC/cdata/*

rm -rf $WC/crf/*

rm -rf $WC/admin/*

rm -rf $WC/network/admin/*.ora

rm -rf $WC/crs/install/crsconfig_params

find $WC -name '*.ouibak' -exec rm {} \; 2>/dev/null

find $WC -name '*.ouibak.1' -exec rm {} \; 2>/dev/null

# rm -rf $WC/root.sh

find $WC/rdbms/audit -name '*.aud' -exec rm {} \; 2>/dev/null

rm -rf $WC/rdbms/log/*

rm -rf $WC/inventory/backup/*

rm -rf $WC/dbs/*

# create zip

echo "Creating the Golden Image zip file"

[ -f $GOLDEN_IMAGE_DEST/$L_New_Name.zip ] && rm $GOLDEN_IMAGE_DEST/$L_New_Name.zip

pushd $WC

zip -r $GOLDEN_IMAGE_DEST/$L_New_Name.zip . >/dev/null

popd $OLDPWD

Home provisioning

Home provisioning requires, beside some checks, a runInstaller -clone command, eventually a relink, eventually a setasmgid, eventually some other tasks, but definitely run root.sh. This last task is not automated yet in my deployment script.

        # ... some checks ...
        # if no new OH name specified, get the golden image name
        ...
        # - check if image to install exists
        ...
        # - check if OH name to install is not already installed
        ...
        # - check if the zip exists
        ...
        # - check if the destination directory exist
        ...

        L_Clone_Command="$RUNINST -clone -waitForCompletion -silent ORACLE_HOME=$ORACLE_HOME ORACLE_BASE=$ORACLE_BASE ORACLE_HOME_NAME=$L_New_Name"
 
        echo $L_Clone_Command
        $L_Clone_Command
 
        if [ $? -eq 0 ] ; then
                echo "Clone command completed successfully."
        else
                echo "There was a problem during the clone command. The script will exit."
                exit 1
        fi
 
        if [ "${L_Link_RAC}" == "yes" ] ; then
                pushd $ORACLE_HOME/rdbms/lib
                make -f ins_rdbms.mk rac_on
                make -f ins_rdbms.mk ioracle
                popd
        fi
 
        # - run setasmgid
        if [ -x /etc/oracle/setasmgid ] ; then
                echo "setasmgid found: running it on Oracle binary"
                /etc/oracle/setasmgid oracle_binary_path=$ORACLE_HOME/bin/oracle
        else
                echo "setasmgid not found: ignoring"
        fi
 
        # - create symlinks for ldap, sqlnet and tnsnames.ora
        TNS_ADMIN=${TNS_ADMIN:-/var/opt/oracle}
        ln -s $TNS_ADMIN/sqlnet.ora   $ORACLE_HOME/network/admin/sqlnet.ora
        ln -s $TNS_ADMIN/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora
        ln -s $TNS_ADMIN/ldap.ora     $ORACLE_HOME/network/admin/ldap.ora
 
# ... other checks ...

# ... some checks ...

# if no new OH name specified, get the golden image name

...

# - check if image to install exists

...

# - check if OH name to install is not already installed

...

# - check if the zip exists

...

# - check if the destination directory exist

...

L_Clone_Command="$RUNINST -clone -waitForCompletion -silent ORACLE_HOME=$ORACLE_HOME ORACLE_BASE=$ORACLE_BASE ORACLE_HOME_NAME=$L_New_Name"

echo $L_Clone_Command

$L_Clone_Command

if [ $? -eq 0 ] ; then

echo "Clone command completed successfully."

else

echo "There was a problem during the clone command. The script will exit."

exit 1

if [ "${L_Link_RAC}" == "yes" ] ; then

pushd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_on

make -f ins_rdbms.mk ioracle

popd

# - run setasmgid

if [ -x /etc/oracle/setasmgid ] ; then

echo "setasmgid found: running it on Oracle binary"

/etc/oracle/setasmgid oracle_binary_path=$ORACLE_HOME/bin/oracle

else

echo "setasmgid not found: ignoring"

# - create symlinks for ldap, sqlnet and tnsnames.ora

TNS_ADMIN=${TNS_ADMIN:-/var/opt/oracle}

ln -s $TNS_ADMIN/sqlnet.ora $ORACLE_HOME/network/admin/sqlnet.ora

ln -s $TNS_ADMIN/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora

ln -s $TNS_ADMIN/ldap.ora $ORACLE_HOME/network/admin/ldap.ora

# ... other checks ...

Usage

Purpose : Management of Golden Images (Oracle Homes)
 
        Usage   : To list the available images:
                    ohctl -l
                  To install an image on the localhost:
                    ohctl -i goldenimage [-n newname] [-r]
                  To create an image based on the current OH:
                    ohctl -c [-n newname] [ -f ]
                  To remove a golden image from the repository:
                    ohctl -d goldenimage [ -f ]
 
        Options : -l                    List the available Oracle Homes in the golden image repository
                  -i goldenimage        Installs locally the specified golden image. (If already deployed, an error is thrown)
                                        if the option -l is given, the list action has the priority over the deploy.
                  -n newname            Specify a new name for the Oracle Home: use it in case you need to patch
                                        and create a new Golden Image from it or if you want to change the Golden Image name
                                        for the current Oracle Home you are converting to Image.
                                        When creating a new Image (-c), it takes the basename of the OH by default, and not the
                                        OHname inside the inventory.
                  -c                    Creates a new Golden Image from the current Oracle Home.
                  -d goldenimage        Removes the golden image from the repository
                  -f                    If the Golden Image to be created exists, force the overwrite.
                  -r                    Relink with RAC option (install only)
 
        Example : ohctl -i DB12_1_0_2_BP170718_home1 -n DB12_1_0_2_BP171018_home1
                                        installs the Oracle Home DB12_1_0_2_BP170718_home1 with new name DB12_1_0_2_BP171018_home1
                                        in order to apply the Bundle Patch 171018 on it
 
                  ohctl -i DB12_1_0_2_BP170718_home1
                                        installs the Oracle Home DB12_1_0_2_BP170718_home1 for normal usage
 
                  ohctl -c -n DB12_1_0_2_BP180116
                                        Creates a new Golden Image named DB12_1_0_2_BP180116 from the current ORACLE_HOME
 
                  ohctl -c -f
                                        Creates a new Golden Image with the name of the current OH basename, overwriting
                                        the eventual existing image.
                                        E.g. if the current OH is /ccv/app/oracle/product/DB12_1_0_2_BP180116, the new GI name
                                         will be "DB12_1_0_2_BP180116"

Purpose : Management of Golden Images (Oracle Homes)

Usage : To list the available images:

ohctl -l

To install an image on the localhost:

ohctl -i goldenimage [-n newname] [-r]

To create an image based on the current OH:

ohctl -c [-n newname] [ -f ]

To remove a golden image from the repository:

ohctl -d goldenimage [ -f ]

Options : -l List the available Oracle Homes in the golden image repository

-i goldenimage Installs locally the specified golden image. (If already deployed, an error is thrown)

if the option -l is given, the list action has the priority over the deploy.

-n newname Specify a new name for the Oracle Home: use it in case you need to patch

and create a new Golden Image from it or if you want to change the Golden Image name

for the current Oracle Home you are converting to Image.

When creating a new Image (-c), it takes the basename of the OH by default, and not the

OHname inside the inventory.

-c Creates a new Golden Image from the current Oracle Home.

-d goldenimage Removes the golden image from the repository

-f If the Golden Image to be created exists, force the overwrite.

-r Relink with RAC option (install only)

Example : ohctl -i DB12_1_0_2_BP170718_home1 -n DB12_1_0_2_BP171018_home1

installs the Oracle Home DB12_1_0_2_BP170718_home1 with new name DB12_1_0_2_BP171018_home1

in order to apply the Bundle Patch 171018 on it

ohctl -i DB12_1_0_2_BP170718_home1

installs the Oracle Home DB12_1_0_2_BP170718_home1 for normal usage

ohctl -c -n DB12_1_0_2_BP180116

Creates a new Golden Image named DB12_1_0_2_BP180116 from the current ORACLE_HOME

ohctl -c -f

Creates a new Golden Image with the name of the current OH basename, overwriting

the eventual existing image.

E.g. if the current OH is /ccv/app/oracle/product/DB12_1_0_2_BP180116, the new GI name

will be "DB12_1_0_2_BP180116"

Examples

List installed homes:

# [ oracle@myserver:/u01/app/oracle/scripts [17:43:04] [12.1.0.2.0 SID=GRID] 0 ] #
# lsoh

HOME                        LOCATION                                                VERSION      EDITION
--------------------------- ------------------------------------------------------- ------------ ---------
OraGI12Home1                /u01/app/grid/product/grid                              12.1.0.2.0   GRID
OraDB12Home1                /u01/app/oracle/product/12.1.0.2                        12.1.0.2.0   DBMS EE
agent12c1                   /u01/app/oracle/product/agent12c/core/12.1.0.5.0        12.1.0.5.0   AGT
OraDb11g_home1              /u01/app/oracle/product/11.2.0.4                        11.2.0.4.0   DBMS EE
OraDB12Home2                /u01/app/oracle/product/12.1.0.2_BP170718               12.1.0.2.0   DBMS EE

# [ oracle@myserver:/u01/app/oracle/scripts [17:43:04] [12.1.0.2.0 SID=GRID] 0 ] #

# lsoh

HOME LOCATION VERSION EDITION

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

OraGI12Home1 /u01/app/grid/product/grid 12.1.0.2.0 GRID

OraDB12Home1 /u01/app/oracle/product/12.1.0.2 12.1.0.2.0 DBMS EE

agent12c1 /u01/app/oracle/product/agent12c/core/12.1.0.5.0 12.1.0.5.0 AGT

OraDb11g_home1 /u01/app/oracle/product/11.2.0.4 11.2.0.4.0 DBMS EE

OraDB12Home2 /u01/app/oracle/product/12.1.0.2_BP170718 12.1.0.2.0 DBMS EE

Create a golden image 12_1_0_2_BP170718 from the Oracle Home named OraDB12Home2 (tha latter having been installed manually without naming convention):

# [ oracle@myserver:/u01/app/oracle/scripts [17:43:07] [12.1.0.2.0 SID=GRID] 0 ] #
# setoh OraDB12Home2

# [ oracle@myserver:/u01/app/oracle/scripts [17:43:04] [12.1.0.2.0 SID=GRID] 0 ] #
# ohctl -c -n 12_1_0_2_BP170718 -f
Image 12_1_0_2_BP170718 already exists but -f specified. The script will continue.


Creating the new Golden Image 12_1_0_2_BP170718

Cleaning previous working copy
Copying the OH to the working copy
Cleansing files in Working Copy
Creating the Golden Image zip file

# [ oracle@myserver:/u01/app/oracle/scripts [17:52:09] [12.1.0.2.0 SID=GRID] 0 ] #
#

# [ oracle@myserver:/u01/app/oracle/scripts [17:43:07] [12.1.0.2.0 SID=GRID] 0 ] #

# setoh OraDB12Home2

# [ oracle@myserver:/u01/app/oracle/scripts [17:43:04] [12.1.0.2.0 SID=GRID] 0 ] #

# ohctl -c -n 12_1_0_2_BP170718 -f

Image 12_1_0_2_BP170718 already exists but -f specified. The script will continue.

Creating the new Golden Image 12_1_0_2_BP170718

Cleaning previous working copy

Copying the OH to the working copy

Cleansing files in Working Copy

Creating the Golden Image zip file

# [ oracle@myserver:/u01/app/oracle/scripts [17:52:09] [12.1.0.2.0 SID=GRID] 0 ] #

List the new golden image from the metadata repository:

# [ oracle@myserver:/u01/app/oracle/scripts [17:57:46] [12.1.0.2.0 SID=GRID] 0 ] #
# ohctl -l

Listing existing golden images:

OH_Name                             Created    Installed locally?
----------------------------------- ---------- ------------------
12_1_0_2_BP170718                   2018-02-06 Not installed

# [ oracle@myserver:/u01/app/oracle/scripts [17:57:46] [12.1.0.2.0 SID=GRID] 0 ] #

# ohctl -l

Listing existing golden images:

OH_Name Created Installed locally?

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

12_1_0_2_BP170718 2018-02-06 Not installed

Reinstalling the same home with the new naming convention:

# ohctl -i 12_1_0_2_BP170718
OK, the image exists.
Zip file exists.
The unzip completed successfully.
/u01/app/oracle/product/12_1_0_2_BP170718/oui/bin/runInstaller -clone -waitForCompletion -silent ORACLE_HOME=/u01/app/oracle/product/12_1_0_2_BP170718 ORACLE_BASE=/u01/app/oracle ORACLE_HOME_NAME=12_1_0_2_BP170718
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-02-06_06-04-33PM. Please wait ...Oracle Universal Installer, Version 12.1.0.2.0 Production
Copyright (C) 1999, 2014, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oracle/oraInventory/logs/cloneActions2018-02-06_06-04-33PM.log
.................................................................................................... 100% Done.



Installation in progress (Tuesday, February 6, 2018 6:04:41 PM CET)
................................................................................                                                80% Done.
Install successful

Linking in progress (Tuesday, February 6, 2018 6:04:44 PM CET)
.                                                                81% Done.
Link successful

Setup in progress (Tuesday, February 6, 2018 6:05:01 PM CET)
..........                                                      100% Done.
Setup successful

Saving inventory (Tuesday, February 6, 2018 6:05:01 PM CET)
Saving inventory complete
Configuration complete

End of install phases.(Tuesday, February 6, 2018 6:05:22 PM CET)
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u01/app/oracle/product/12_1_0_2_BP170718/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The cloning of 12_1_0_2_BP170718 was successful.
Please check '/u01/app/oracle/oraInventory/logs/cloneActions2018-02-06_06-04-33PM.log' for more details.
Clone command completed successfully.
setasmgid found: running it on Oracle binary
The image 12_1_0_2_BP170718 has been installed and exists in the inventory.

Installation completed. Please run /u01/app/oracle/product/12_1_0_2_BP170718/root.sh as root before using the new home.

# [ oracle@myserver:/u01/app/oracle/scripts [18:05:24] [12.1.0.2.0 SID=GRID] 0 ] #
#

# and manually...
-bash-4.2$ sudo /u01/app/oracle/product/12_1_0_2_BP170718/root.sh
Check /u01/app/oracle/product/12_1_0_2_BP170718/install/root_myserver_2018-02-06_18-06-07.log for the output of root script

# ohctl -i 12_1_0_2_BP170718

OK, the image exists.

Zip file exists.

The unzip completed successfully.

/u01/app/oracle/product/12_1_0_2_BP170718/oui/bin/runInstaller -clone -waitForCompletion -silent ORACLE_HOME=/u01/app/oracle/product/12_1_0_2_BP170718 ORACLE_BASE=/u01/app/oracle ORACLE_HOME_NAME=12_1_0_2_BP170718

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 16383 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-02-06_06-04-33PM. Please wait ...Oracle Universal Installer, Version 12.1.0.2.0 Production

You can find the log of this install session at:

/u01/app/oracle/oraInventory/logs/cloneActions2018-02-06_06-04-33PM.log

.................................................................................................... 100% Done.

Installation in progress (Tuesday, February 6, 2018 6:04:41 PM CET)

................................................................................ 80% Done.

Install successful

Linking in progress (Tuesday, February 6, 2018 6:04:44 PM CET)

. 81% Done.

Link successful

Setup in progress (Tuesday, February 6, 2018 6:05:01 PM CET)

.......... 100% Done.

Setup successful

Saving inventory (Tuesday, February 6, 2018 6:05:01 PM CET)

Saving inventory complete

Configuration complete

End of install phases.(Tuesday, February 6, 2018 6:05:22 PM CET)

WARNING:

The following configuration scripts need to be executed as the "root" user.

/u01/app/oracle/product/12_1_0_2_BP170718/root.sh

To execute the configuration scripts:

1. Open a terminal window

2. Log in as "root"

3. Run the scripts

The cloning of 12_1_0_2_BP170718 was successful.

Please check '/u01/app/oracle/oraInventory/logs/cloneActions2018-02-06_06-04-33PM.log' for more details.

Clone command completed successfully.

setasmgid found: running it on Oracle binary

The image 12_1_0_2_BP170718 has been installed and exists in the inventory.

Installation completed. Please run /u01/app/oracle/product/12_1_0_2_BP170718/root.sh as root before using the new home.

# [ oracle@myserver:/u01/app/oracle/scripts [18:05:24] [12.1.0.2.0 SID=GRID] 0 ] #

# and manually...

-bash-4.2$ sudo /u01/app/oracle/product/12_1_0_2_BP170718/root.sh

Check /u01/app/oracle/product/12_1_0_2_BP170718/install/root_myserver_2018-02-06_18-06-07.log for the output of root script

Installing the same home in a new path for manual patching from 170718 to 180116:

# [ oracle@myserver:/u01/app/oracle/scripts [12:48:36] [12.1.0.2.0 SID=GRID] 0 ] #
# ohctl -i 12_1_0_2_BP170718 -n 12_1_0_2_BP180116
OK, the image exists.
Zip file exists.
The unzip completed successfully.
/u01/app/oracle/product/12_1_0_2_BP180116/oui/bin/runInstaller -clone -waitForCompletion -silent ORACLE_HOME=/u01/a                                           pp/oracle/product/12_1_0_2_BP180116 ORACLE_BASE=/u01/app/oracle ORACLE_HOME_NAME=12_1_0_2_BP180116
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-02-07_12-49-50PM. Please wait ...Oracle Univers                                           al Installer, Version 12.1.0.2.0 Production
Copyright (C) 1999, 2014, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oracle/oraInventory/logs/cloneActions2018-02-07_12-49-50PM.log
.................................................................................................... 100% Done.



Installation in progress (Wednesday, February 7, 2018 12:49:58 PM CET)
................................................................................                                                                                           80% Done.
Install successful

Linking in progress (Wednesday, February 7, 2018 12:50:00 PM CET)
.                                                                81% Done.
Link successful

Setup in progress (Wednesday, February 7, 2018 12:50:17 PM CET)
..........                                                      100% Done.
Setup successful

Saving inventory (Wednesday, February 7, 2018 12:50:17 PM CET)
Saving inventory complete
Configuration complete

End of install phases.(Wednesday, February 7, 2018 12:50:38 PM CET)
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u01/app/oracle/product/12_1_0_2_BP180116/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The cloning of 12_1_0_2_BP180116 was successful.
Please check '/u01/app/oracle/oraInventory/logs/cloneActions2018-02-07_12-49-50PM.log' for more details.
Clone command completed successfully.
setasmgid found: running it on Oracle binary
The image 12_1_0_2_BP180116 has been installed and exists in the inventory.

Installation completed. Please run /u01/app/oracle/product/12_1_0_2_BP180116/root.sh as root before using the new home.

# [ oracle@myserver:/u01/app/oracle/scripts [12:48:36] [12.1.0.2.0 SID=GRID] 0 ] #

# ohctl -i 12_1_0_2_BP170718 -n 12_1_0_2_BP180116

OK, the image exists.

Zip file exists.

The unzip completed successfully.

/u01/app/oracle/product/12_1_0_2_BP180116/oui/bin/runInstaller -clone -waitForCompletion -silent ORACLE_HOME=/u01/a pp/oracle/product/12_1_0_2_BP180116 ORACLE_BASE=/u01/app/oracle ORACLE_HOME_NAME=12_1_0_2_BP180116

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 16383 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-02-07_12-49-50PM. Please wait ...Oracle Univers al Installer, Version 12.1.0.2.0 Production

You can find the log of this install session at:

/u01/app/oracle/oraInventory/logs/cloneActions2018-02-07_12-49-50PM.log

.................................................................................................... 100% Done.

Installation in progress (Wednesday, February 7, 2018 12:49:58 PM CET)

................................................................................ 80% Done.

Install successful

Linking in progress (Wednesday, February 7, 2018 12:50:00 PM CET)

. 81% Done.

Link successful

Setup in progress (Wednesday, February 7, 2018 12:50:17 PM CET)

.......... 100% Done.

Setup successful

Saving inventory (Wednesday, February 7, 2018 12:50:17 PM CET)

Saving inventory complete

Configuration complete

End of install phases.(Wednesday, February 7, 2018 12:50:38 PM CET)

WARNING:

The following configuration scripts need to be executed as the "root" user.

/u01/app/oracle/product/12_1_0_2_BP180116/root.sh

To execute the configuration scripts:

1. Open a terminal window

2. Log in as "root"

3. Run the scripts

The cloning of 12_1_0_2_BP180116 was successful.

Please check '/u01/app/oracle/oraInventory/logs/cloneActions2018-02-07_12-49-50PM.log' for more details.

Clone command completed successfully.

setasmgid found: running it on Oracle binary

The image 12_1_0_2_BP180116 has been installed and exists in the inventory.

Installation completed. Please run /u01/app/oracle/product/12_1_0_2_BP180116/root.sh as root before using the new home.

New home situation:

# [ oracle@myserver:/u01/app/oracle/scripts [12:50:41] [12.1.0.2.0 SID=GRID] 0 ] #
# lsoh

HOME                        LOCATION                                                VERSION      EDITION                                                          
--------------------------- ------------------------------------------------------- ------------ --------                                                         -
OraGI12Home1                /u01/app/grid/product/grid                              12.1.0.2.0   GRID                                                             
OraDB12Home1                /u01/app/oracle/product/12.1.0.2                        12.1.0.2.0   DBMS EE                                                          
agent12c1                   /u01/app/oracle/product/agent12c/core/12.1.0.5.0        12.1.0.5.0   AGT                                                              
OraDb11g_home1              /u01/app/oracle/product/11.2.0.4                        11.2.0.4.0   DBMS EE                                                          
OraDB12Home2                /u01/app/oracle/product/12.1.0.2_BP170718               12.1.0.2.0   DBMS EE
12_1_0_2_BP170718           /u01/app/oracle/product/12_1_0_2_BP170718               12.1.0.2.0   DBMS EE                                                          
12_1_0_2_BP180116           /u01/app/oracle/product/12_1_0_2_BP180116               12.1.0.2.0   DBMS EE

# [ oracle@myserver:/u01/app/oracle/scripts [12:50:41] [12.1.0.2.0 SID=GRID] 0 ] #

# lsoh

HOME LOCATION VERSION EDITION

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

OraGI12Home1 /u01/app/grid/product/grid 12.1.0.2.0 GRID

OraDB12Home1 /u01/app/oracle/product/12.1.0.2 12.1.0.2.0 DBMS EE

agent12c1 /u01/app/oracle/product/agent12c/core/12.1.0.5.0 12.1.0.5.0 AGT

OraDb11g_home1 /u01/app/oracle/product/11.2.0.4 11.2.0.4.0 DBMS EE

OraDB12Home2 /u01/app/oracle/product/12.1.0.2_BP170718 12.1.0.2.0 DBMS EE

12_1_0_2_BP170718 /u01/app/oracle/product/12_1_0_2_BP170718 12.1.0.2.0 DBMS EE

12_1_0_2_BP180116 /u01/app/oracle/product/12_1_0_2_BP180116 12.1.0.2.0 DBMS EE

Patch manually the home named 12_1_0_2_BP180116 with the January bundle patch:

# [ oracle@myserver:/u01/app/oracle/scripts [18:07:00] [12.1.0.2.0 SID=GRID] 0 ] #
# setoh 12_1_0_2_BP170718

# [ oracle@myserver:/share/oracle/database/patches/12c/12.1.0.2.BP180116/27010930/26925263 [12:55:58] [12.1.0.2.0 SID=GRID] 0 ] #
# opatch apply
Oracle Interim Patch Installer version 12.2.0.1.12
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12_1_0_2_BP180116
Central Inventory : /u01/app/oracle/oraInventory
   from           : /u01/app/oracle/product/12_1_0_2_BP180116/oraInst.loc
OPatch version    : 12.2.0.1.12
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12_1_0_2_BP180116/cfgtoollogs/opatch/opatch2018-02-07_12-                                                         54-50PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   26609798  26717470  26925263

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12_1_0_2_BP180116')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '26609798' to OH '/u01/app/oracle/product/12_1_0_2_BP180116'

Patching component oracle.oracore.rsf, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...
Applying sub-patch '26717470' to OH '/u01/app/oracle/product/12_1_0_2_BP180116'
ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ] , [ oracle.has.crs, 12.1.0.2.0 ]  n                                                         ot present in the Oracle Home or a higher version is found.

Patching component oracle.ldap.client, 12.1.0.2.0...

Patching component oracle.rdbms.crs, 12.1.0.2.0...

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.xdk, 12.1.0.2.0...

Patching component oracle.tfa, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.nlsrtl.rsf, 12.1.0.2.0...

Patching component oracle.xdk.parser.java, 12.1.0.2.0...

Patching component oracle.xdk.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rman, 12.1.0.2.0...

Patching component oracle.rdbms.rman, 12.1.0.2.0...

Patching component oracle.has.deconfig, 12.1.0.2.0...
Applying sub-patch '26925263' to OH '/u01/app/oracle/product/12_1_0_2_BP180116'
ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ]  not present in the Oracle Home or a h                                                         igher version is found.

Patching component oracle.network.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.crs, 12.1.0.2.0...

Patching component oracle.rdbms.util, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rman, 12.1.0.2.0...
Composite patch 26925263 successfully applied.
Sub-set patch [22652097] has become inactive due to the application of a super-set patch [26925263].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/12_1_0_2_BP180116/cfgtoollogs/opatch/opatch2018-02-07_12-5                                                         4-50PM_1.log

OPatch succeeded.

# [ oracle@myserver:/share/oracle/database/patches/12c/12.1.0.2.BP180116/27010930/26925263 [12:55:47] [12.1.0.2.0 SID=GRID] 0 ] #
# opatch lspatches
26925263;Database Bundle Patch : 12.1.0.2.180116 (26925263)
22243983;

OPatch succeeded.

# [ oracle@myserver:/u01/app/oracle/scripts [18:07:00] [12.1.0.2.0 SID=GRID] 0 ] #

# setoh 12_1_0_2_BP170718

# [ oracle@myserver:/share/oracle/database/patches/12c/12.1.0.2.BP180116/27010930/26925263 [12:55:58] [12.1.0.2.0 SID=GRID] 0 ] #

# opatch apply

Oracle Interim Patch Installer version 12.2.0.1.12

Oracle Home : /u01/app/oracle/product/12_1_0_2_BP180116

Central Inventory : /u01/app/oracle/oraInventory

from : /u01/app/oracle/product/12_1_0_2_BP180116/oraInst.loc

OPatch version : 12.2.0.1.12

OUI version : 12.1.0.2.0

Log file location : /u01/app/oracle/product/12_1_0_2_BP180116/cfgtoollogs/opatch/opatch2018-02-07_12- 54-50PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 26609798 26717470 26925263

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/12_1_0_2_BP180116')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying sub-patch '26609798' to OH '/u01/app/oracle/product/12_1_0_2_BP180116'

Patching component oracle.oracore.rsf, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Applying sub-patch '26717470' to OH '/u01/app/oracle/product/12_1_0_2_BP180116'

ApplySession: Optional component(s) [ oracle.oid.client, 12.1.0.2.0 ] , [ oracle.has.crs, 12.1.0.2.0 ] n ot present in the Oracle Home or a higher version is found.

Patching component oracle.ldap.client, 12.1.0.2.0...

Patching component oracle.rdbms.crs, 12.1.0.2.0...

Patching component oracle.rdbms.deconfig, 12.1.0.2.0...

Patching component oracle.xdk, 12.1.0.2.0...

Patching component oracle.tfa, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.nlsrtl.rsf, 12.1.0.2.0...

Patching component oracle.xdk.parser.java, 12.1.0.2.0...

Patching component oracle.xdk.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rman, 12.1.0.2.0...

Patching component oracle.has.deconfig, 12.1.0.2.0...

Applying sub-patch '26925263' to OH '/u01/app/oracle/product/12_1_0_2_BP180116'

ApplySession: Optional component(s) [ oracle.has.crs, 12.1.0.2.0 ] not present in the Oracle Home or a h igher version is found.

Patching component oracle.network.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.crs, 12.1.0.2.0...

Patching component oracle.rdbms.util, 12.1.0.2.0...

Patching component oracle.rdbms, 12.1.0.2.0...

Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...

Patching component oracle.rdbms.rsf, 12.1.0.2.0...

Patching component oracle.rdbms.rman, 12.1.0.2.0...

Composite patch 26925263 successfully applied.

Sub-set patch [22652097] has become inactive due to the application of a super-set patch [26925263].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /u01/app/oracle/product/12_1_0_2_BP180116/cfgtoollogs/opatch/opatch2018-02-07_12-5 4-50PM_1.log

OPatch succeeded.

# [ oracle@myserver:/share/oracle/database/patches/12c/12.1.0.2.BP180116/27010930/26925263 [12:55:47] [12.1.0.2.0 SID=GRID] 0 ] #

# opatch lspatches

26925263;Database Bundle Patch : 12.1.0.2.180116 (26925263)

22243983;

OPatch succeeded.

Create the new golden image from the home patched with January bundle patch:

# [ oracle@myserver:/u01/app/oracle/scripts [18:07:00] [12.1.0.2.0 SID=GRID] 0 ] #
# setoh 12_1_0_2_BP180116

# [ oracle@myserver:/u01/app/oracle/scripts [12:57:24] [12.1.0.2.0 SID=GRID] 1 ] #
# ohctl -c -f
Creating the new Golden Image 12_1_0_2_BP180116

Cleaning previous working copy
Copying the OH to the working copy
Cleansing files in Working Copy
Creating the Golden Image zip file


# [ oracle@myserver:/u01/app/oracle/scripts [13:04:57] [12.1.0.2.0 SID=GRID] 0 ] #
# ohctl -l
Listing existing golden images:

OH_Name                             Created    Installed locally?
----------------------------------- ---------- ------------------
12_1_0_2_BP180116                   2018-02-07 Installed
12_1_0_2_BP170718                   2018-02-06 Installed

# [ oracle@myserver:/u01/app/oracle/scripts [18:07:00] [12.1.0.2.0 SID=GRID] 0 ] #

# setoh 12_1_0_2_BP180116

# [ oracle@myserver:/u01/app/oracle/scripts [12:57:24] [12.1.0.2.0 SID=GRID] 1 ] #

# ohctl -c -f

Creating the new Golden Image 12_1_0_2_BP180116

Cleaning previous working copy

Copying the OH to the working copy

Cleansing files in Working Copy

Creating the Golden Image zip file

# [ oracle@myserver:/u01/app/oracle/scripts [13:04:57] [12.1.0.2.0 SID=GRID] 0 ] #

# ohctl -l

Listing existing golden images:

OH_Name Created Installed locally?

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

12_1_0_2_BP180116 2018-02-07 Installed

12_1_0_2_BP170718 2018-02-06 Installed

Full source code

Full source code of ohctl

I hope you find it useful! The cool thing is that once you have the golden images ready in the golden image repository, then the provisioning to all the servers is striaghtforward and requires just a couple of minutes, from nothing to a full working and patched Oracle Home.

Why applying the patch manually?

If you read everything carefully, I automated the golden image creation and provisioning, but the patching is still done manually.

The aim of this framework is not to patch all the Oracle Homes with the same patch, but to install the patch ONCE and then deploy the patched home everywhere. Because each patch has different conflicts, bugs, etc, it might be convenient to install it manually the first time and then forget it. At least this is my opinion 🙂

Of course, patch download, conflict detection, etc. can also be automated (and it is a good idea, if you have the time to implement it carefully and bullet-proof).

In the addendum blog post, I will show some scripts made by Hutchison Austria and why I find them really useful in this context.

Blog posts in this series:

Oracle Home Management – part 6: Simple Golden Image Blueprint

Posted on May 28, 2018 by Ludovico

As I explained in the previous blog posts, from a manageability perspective, you should not change the patch level of a deployed Oracle Home, but rather install and patch a new Oracle Home.

With the same principle, Oracle Homes deployed on different hosts should have an identical patch level for the same name. For example, an Oracle Home /u01/app/oracle/product/EE12_1_0_2_BP171018 should have the same patch level on all the servers.

To guarantee the same binaries and patch levels everywhere, the simple solution that I am shoing in this series is to store copies of the Oracle Homes somewhere and use them as golden images. (Another approach, really different and cool, is used by Ilmar Kerm: he explains it here https://ilmarkerm.eu/blog/2018/05/oracle-home-management-using-ansible/ )

For this, we will use a Golden Image store (that could be a NFS share mounted on the Oracle Database servers, or a remote host accessible with scp, or other) and a metadata store.

When all the software is deployed from golden images, there is the guarantee that all the Homes are equal; therefore the information about patches and bugfixes might be centralized in one place (golden image metadata).

A typical Oracle Home lifecycle:

Install the software manually the first time
Create automatically a golden image from the Oracle Home
Deploy automatically the golden image on the other servers

When a new patch is needed:

Deploy automatically the golden image to a new Oracle Home
Patch manually (or automatically!) the new Oracle Home
Create automatically the new golden image with the new name
Deploy automatically the new golden image to the other servers

The script that automates this lifecycle does just two main actions:

Automates the creation of a new golden image
Deploys an existing image to an Oracle Home (either with a new path or the default one)
(optional: uninstall an existing Home)

Let’s make a graphical example of the previously described steps:

Here, the script ohctl takes two actions: -c (creates a Golden Image) and -i (installs a Golden Image)).

The create action does the following steps:

Copies the content to a working directory
Cleans up logs, audits, etc.
Creates the zip file
Stores the zip file in a shared NFS repository
Inserts the metadata of the new golden image in a repository

The install action does the following steps:

Checks if the image is already deployed (plus other security checks)
Creates the new path based on the name of the image or the new name passed as argument
Unzips the content in the new Oracle Home
Runs the runInstaller –clone to attach the home in the central inventory and (optionally) set a new Home name
(optionally) Relinks the oracle binary with the RAC option
Run setasmgid if found
Other environment-specific tasks (e.g. dealing with TNS_ADMIN links)

By following this pattern, Oracle Home names and paths are clean and the same everywhere. This facilitates the deployment and the patching.

You can find the Oracle Home cloning steps in the Oracle Database documentation:

Cloning an Oracle Home

In the next blog post I will explain parts of the ohctl source code and give some examples of how I use it (and publish a link to the full source code 🙂 )

Oracle Home Management – part 5: Oracle Home Inventory and Naming Conventions

Posted on May 20, 2018 by Ludovico

Having the capability of managing multiple Oracle Homes is fundamental for the following reasons:

Out-of-place patching: cloning and patching a new Oracle Home usually takes less downtime than stopping the DBs and patching in-place
Better control of downtime windows: if the databases are consolidated on a single server, having multiple Oracle Homes allows moving and patching one database at a time instead of stopping everything and doing a “big bang” patch.

Make sure that you have a good set of scripts that help you to switch correctly from one environment to the other one. Personally, I recommend TVD-BasEnv, as it is very powerful and supports OFA and non-OFA environments, but for this blog series I will show my personal approach.

Get your Home information from the Inventory!

I wrote a blog post sometimes ago that shows how to get the Oracle Homes from the Central Inventory (Using Bash, OK, not the right tool to query XML files, but you get the idea):

Getting the Oracle Homes in a server from the oraInventory

With the same approach, you can have a script to SET your environment:

setoh ()
{
    SEARCH=${1:-"_foo_"};
    if [ $SEARCH == "ic" ]; then
		# ic is a shortcut for the Instant Client...
        OH=/u01/app/oracle/sbin/instantclient_12_2
        export VERSION=12.2.0.1
        export ORACLE_HOME=$OH
        export LD_LIBRARY_PATH=$ORACLE_HOME
        export OH_NAME=instantclient_12_2
        export ORACLE_VERSION=$VERSION
        export PATH=$ORACLE_HOME:$DEFAULT_PATH
        echo ORACLE_SID = $ORACLE_SID
        echo ORACLE_VERSION = $ORACLE_VERSION
        echo ORACLE_HOME = $ORACLE_HOME
    else
        CENTRAL_ORAINV=`grep ^inventory_loc /etc/oraInst.loc | awk -F= '{print $2}'`;
        IFS='
';
        found=0;
        for line in `grep "<HOME NAME=" ${CENTRAL_ORAINV}/ContentsXML/inventory.xml 2>/dev/null`;
        do
            if [ $found -eq 1 ]; then
                continue;
            fi;
            unset ORACLE_VERSION;
            unset ORAEDITION;
            OH=`echo $line | tr ' ' '\n' | grep ^LOC= | awk -F\" '{print $2}'`;
            OH_NAME=`echo $line | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;
            if [ "$SEARCH" == "$OH_NAME" ]; then
                found=1;
                comp_file=$OH/inventory/ContentsXML/comps.xml;
                comp_xml=`grep "COMP NAME" $comp_file | head -1`;
                comp_name=`echo $comp_xml | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;
                comp_vers=`echo $comp_xml | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;
                case $comp_name in
                    "oracle.crs")
                        ORACLE_VERSION=$comp_vers;
                        ORAEDITION=GRID
                    ;;
                    "oracle.sysman.top.agent")
                        ORACLE_VERSION=$comp_vers;
                        ORAEDITION=AGT
                    ;;
                    "oracle.server")
                        ORACLE_VERSION=`grep "PATCH NAME=\"oracle.server\"" $comp_file 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;
                        ORAEDITION="DBMS";
                        if [ -z "$ORACLE_VERSION" ]; then
                            ORACLE_VERSION=$comp_vers;
                        fi;
                        ORAMAJOR=`echo $ORACLE_VERSION |  cut -d . -f 1`;
                        case $ORAMAJOR in
                            11 | 12)
                                ORAEDITION="DBMS "`grep "oracle_install_db_InstallType" $OH/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`
                            ;;
                            10)
                                ORAEDITION="DBMS "`grep "s_serverInstallType" $OH/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`
                            ;;
                        esac
                    ;;
                esac;
                export VERSION=$ORACLE_VERSION;
                export ORACLE_HOME=$OH;
                export LD_LIBRARY_PATH=$ORACLE_HOME/lib;
                export OH_NAME;
                export ORACLE_VERSION;
                export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$DEFAULT_PATH;
                echo ORACLE_SID = $ORACLE_SID;
                echo ORACLE_VERSION = $ORACLE_VERSION;
                echo ORACLE_HOME = $ORACLE_HOME;
                continue;
            fi;
        done;
        if [ $found -eq 0 ]; then
            echo "cannot find Oracle Home $1";
            false;
        else
            true;
        fi;
    fi
}

setoh ()

{

SEARCH=${1:-"_foo_"};

if [ $SEARCH == "ic" ]; then

# ic is a shortcut for the Instant Client...

OH=/u01/app/oracle/sbin/instantclient_12_2

export VERSION=12.2.0.1

export ORACLE_HOME=$OH

export LD_LIBRARY_PATH=$ORACLE_HOME

export OH_NAME=instantclient_12_2

export ORACLE_VERSION=$VERSION

export PATH=$ORACLE_HOME:$DEFAULT_PATH

echo ORACLE_SID = $ORACLE_SID

echo ORACLE_VERSION = $ORACLE_VERSION

echo ORACLE_HOME = $ORACLE_HOME

else

CENTRAL_ORAINV=`grep ^inventory_loc /etc/oraInst.loc | awk -F= '{print $2}'`;

IFS='

found=0;

for line in `grep "<HOME NAME=" ${CENTRAL_ORAINV}/ContentsXML/inventory.xml 2>/dev/null`;

if [ $found -eq 1 ]; then

continue;

fi;

unset ORACLE_VERSION;

unset ORAEDITION;

OH=`echo $line | tr ' ' '\n' | grep ^LOC= | awk -F\" '{print $2}'`;

OH_NAME=`echo $line | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;

if [ "$SEARCH" == "$OH_NAME" ]; then

found=1;

comp_file=$OH/inventory/ContentsXML/comps.xml;

comp_xml=`grep "COMP NAME" $comp_file | head -1`;

comp_name=`echo $comp_xml | tr ' ' '\n' | grep ^NAME= | awk -F\" '{print $2}'`;

comp_vers=`echo $comp_xml | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;

case $comp_name in

"oracle.crs")

ORACLE_VERSION=$comp_vers;

ORAEDITION=GRID

;;

"oracle.sysman.top.agent")

ORACLE_VERSION=$comp_vers;

ORAEDITION=AGT

;;

"oracle.server")

ORACLE_VERSION=`grep "PATCH NAME=\"oracle.server\"" $comp_file 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;

ORAEDITION="DBMS";

if [ -z "$ORACLE_VERSION" ]; then

ORACLE_VERSION=$comp_vers;

fi;

ORAMAJOR=`echo $ORACLE_VERSION | cut -d . -f 1`;

case $ORAMAJOR in

11 | 12)

ORAEDITION="DBMS "`grep "oracle_install_db_InstallType" $OH/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`

;;

10)

ORAEDITION="DBMS "`grep "s_serverInstallType" $OH/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`

;;

esac

;;

esac;

export VERSION=$ORACLE_VERSION;

export ORACLE_HOME=$OH;

export LD_LIBRARY_PATH=$ORACLE_HOME/lib;

export OH_NAME;

export ORACLE_VERSION;

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$DEFAULT_PATH;

echo ORACLE_SID = $ORACLE_SID;

echo ORACLE_VERSION = $ORACLE_VERSION;

echo ORACLE_HOME = $ORACLE_HOME;

continue;

fi;

done;

if [ $found -eq 0 ]; then

echo "cannot find Oracle Home $1";

false;

else

true;

fi;

}

It uses a different approach from the oraenv script privided by Oracle, where you set the environment based on the ORACLE_SID variable and getting the information from the oratab. My setoh function gets the Oracle Home name as input. Although you can convert it easily to set the environment for a specific ORACLE_SID, there are some reason why I like it:

You can set the environment for an Oracle Home that it is not associated to any database (yet)
You can set the environment for an upgrade to a new release without changing (yet) the oratab
It works for OMS, Grid and Agent homes as well…
Most important, it will let you specify correctly the environment when you need to use a fresh install (for patching it as well)

So, this is how it works:

# [ oracle@myserver:/u01/app/oracle [11:23:18] [12.1.0.2.0 SID="not set"] 0 ] #
# lsoh

HOME                        LOCATION                                                VERSION      EDITION
--------------------------- ------------------------------------------------------- ------------ ---------
OraGI12Home1                /u01/app/grid/product/grid                              12.1.0.2.0   GRID
agent12c1                   /u01/app/oracle/product/agent12c/core/12.1.0.5.0        12.1.0.5.0   AGT
OraDb11g_home1              /u01/app/oracle/product/11.2.0.4                        11.2.0.4.0   DBMS EE
OraDB12Home1                /u01/app/oracle/product/12.1.0.2                        12.1.0.2.0   DBMS EE
12_1_0_2_BP170718_RON       /u01/app/oracle/product/12_1_0_2_BP170718_RON           12.1.0.2.0   DBMS EE
12_1_0_2_BP180116_OCW       /u01/app/oracle/product/12_1_0_2_BP180116_OCW           12.1.0.2.0   DBMS EE

# [ oracle@myserver:/u01/app/oracle [11:23:22] [12.1.0.2.0 SID="not set"] 0 ] #
# setoh 12_1_0_2_BP180116_OCW
ORACLE_SID =
ORACLE_VERSION = 12.1.0.2.0
ORACLE_HOME = /u01/app/oracle/product/12_1_0_2_BP180116_OCW

# [ oracle@myserver:/u01/app/oracle [11:23:25] [12.1.0.2.0 SID="not set"] 0 ] #
# opatch lspatches
26925218;OCW Patch Set Update : 12.1.0.2.180116 (26925218)
26925263;Database Bundle Patch : 12.1.0.2.180116 (26925263)
22243983;

OPatch succeeded.

# [ oracle@myserver:/u01/app/oracle [11:23:18] [12.1.0.2.0 SID="not set"] 0 ] #

# lsoh

HOME LOCATION VERSION EDITION

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

OraGI12Home1 /u01/app/grid/product/grid 12.1.0.2.0 GRID

agent12c1 /u01/app/oracle/product/agent12c/core/12.1.0.5.0 12.1.0.5.0 AGT

OraDb11g_home1 /u01/app/oracle/product/11.2.0.4 11.2.0.4.0 DBMS EE

OraDB12Home1 /u01/app/oracle/product/12.1.0.2 12.1.0.2.0 DBMS EE

12_1_0_2_BP170718_RON /u01/app/oracle/product/12_1_0_2_BP170718_RON 12.1.0.2.0 DBMS EE

12_1_0_2_BP180116_OCW /u01/app/oracle/product/12_1_0_2_BP180116_OCW 12.1.0.2.0 DBMS EE

# [ oracle@myserver:/u01/app/oracle [11:23:22] [12.1.0.2.0 SID="not set"] 0 ] #

# setoh 12_1_0_2_BP180116_OCW

ORACLE_SID =

ORACLE_VERSION = 12.1.0.2.0

ORACLE_HOME = /u01/app/oracle/product/12_1_0_2_BP180116_OCW

# [ oracle@myserver:/u01/app/oracle [11:23:25] [12.1.0.2.0 SID="not set"] 0 ] #

# opatch lspatches

26925218;OCW Patch Set Update : 12.1.0.2.180116 (26925218)

26925263;Database Bundle Patch : 12.1.0.2.180116 (26925263)

22243983;

OPatch succeeded.

In the previous example, there are two Database homes that have been installed without a specific naming convention (OraDb11g_home1, OraDB12Home1) and two that follow a specific one (12_1_0_2_BP170718_RON, 12_1_0_2_BP180116_OCW).

Naming conventions play an important role

If you want to achieve an effective Oracle Home management, it is important that you have everywhere the same ORACLE_HOME paths, names and patch levels.

The Oracle Home path should not include only the release number:

/u01/app/oracle/product/12.1.0.2

1	/u01/app/oracle/product/12.1.0.2

If we have many Oracle Homes with the same release, how shall we call the other ones? There are several variables that might influence the naming convention:

Edition (EE, SE), RAC Option or other options, the patch type (formerly PSU, BP: now RU and RUR), eventual additional one-off patches.

Some ideas might be:

/u01/app/oracle/product/EE12.1.0.2
/u01/app/oracle/product/EE12.1.0.2_BP171019
/u01/app/oracle/product/EE12.1.0.2_BP171019_v2

/u01/app/oracle/product/EE12.1.0.2

/u01/app/oracle/product/EE12.1.0.2_BP171019

/u01/app/oracle/product/EE12.1.0.2_BP171019_v2

The new release model will facilitate a lot the definition of a naming convention as we will have names like:

/u01/app/oracle/product/EE18.1.0
/u01/app/oracle/product/EE18.2.1
/u01/app/oracle/product/EE18.2.1_v2

/u01/app/oracle/product/EE18.1.0

/u01/app/oracle/product/EE18.2.1

/u01/app/oracle/product/EE18.2.1_v2

Of course, the naming convention is not universal and can be adapted depending on the customer (e.g., if you have only Enterprise Editions you might omit this information).

Replacing dots with underscores?

You will see, at the end of the series, that I use Oracle Home paths with underscores instead of dots:

/u01/app/oracle/product/EE12_1_0_2
/u01/app/oracle/product/EE12_1_0_2_BP171019
/u01/app/oracle/product/EE12_1_0_2_BP171019_v2

/u01/app/oracle/product/EE12_1_0_2

/u01/app/oracle/product/EE12_1_0_2_BP171019

/u01/app/oracle/product/EE12_1_0_2_BP171019_v2

Why?

From a naming perspective, there is no need to have the Home that corresponds to the release number. Release, version and product information can be collected through the inventory.

What is really important is to have good naming conventions and good manageability. In my ideal world, the Oracle Home name inside the central inventory and the basename of the Oracle Home path are the same: this facilitates tremendously the scripting of the Oracle Home provisioning.

Sadly, the Oracle Home name cannot contain dots, it is a limitation of the Oracle Inventory, here’s why I replaced them with underscores.

In the next blog post, I will show how to plan a framework for automated Oracle Home provisioning.

Oracle Home Management – part 1: “Patch soon, patch often” vs. reality

Posted on May 1, 2018 by Ludovico

With this post, I am starting a new blog series about Oracle Database home management, provisioning, patching… Best (and worst) practices, common practices and blueprints from my point of view as consultant and, sometimes, as operational DBA.

I hope to find the time to continue (and finish) it 🙂

How often should you upgrade/patch?

Database patching and upgrading is not an easy task, but it is really important.

Many companies do not have a clear patching strategy, for several reasons.

Patching is time consuming
It is complex
It introduces some risks
It is not always really necesary
It leads to human errors

Oracle, of course, recommends to apply the patches quarterly, as soon as they are released. But the reality is that it is (still) very common to find customers that do not apply patches regularly.

Look at this:

$ opatch lspatches
26925218;OCW Patch Set Update : 12.1.0.2.180116 (26925218)
26925263;Database Bundle Patch : 12.1.0.2.180116 (26925263)
22243983;

OPatch succeeded.

$ cd $ORACLE_HOME/inventory
$ grep -r "bug description" * |  wc -l
1883
$ grep -r "bug description" * | grep -i "wrong result" | wc -l
56

$ opatch lspatches

26925218;OCW Patch Set Update : 12.1.0.2.180116 (26925218)

26925263;Database Bundle Patch : 12.1.0.2.180116 (26925263)

22243983;

OPatch succeeded.

$ cd $ORACLE_HOME/inventory

$ grep -r "bug description" * | wc -l

1883

$ grep -r "bug description" * | grep -i "wrong result" | wc -l

With January 2018 Bundle Patch, you can fix 1883 bugs, including 56 “wrong results” bugs! I hope I will talk more about this kind of bugs, but for now consider that if you are not patching often, you are taking serious risks, including putting at risk your data consistency.

I will not talk about bugs, upgrade procedures, new releases here… For this, I recommend to follow Mike Dietrich’s blog: Upgrade your Database – NOW!

I would like rather to talk, as the title of this blog series states, about the approaches of maintaining the Oracle Homes across your Oracle server farm.

Common worst practices in maintaining homes

Maintaining a plethora of Oracle Homes across different servers requires thoughtful planning. This is a non-exhaustive list of bad practices that I see from time to time.

Installing by hand every new Oracle Home
Applying different patch levels on Oracle Homes with the same path
Not tracking the installed patches
Having Oracle Home paths hard-coded in the operational scripts
Not minding about Oracle Home path naming convention
Not minding about Oracle Home internal names
Copying Oracle Homes without minding about the Central Inventory

All these worst practices lead to what I like to call “patching madness”… that monster that makes regular patching very difficult / impossible.

THIS IS A SITUATION THAT YOU NEED TO AVOID:

Server A
/u01/app/oracle/product/12.1.0            -> Home "OraHOme12C", contains clean 12.1.0.2

Server B
/u01/app/oracle/product/12.1.0.2          -> Home "OraHome1",   contains 12.1.0.2.PSU161018
/u01/app/oracle/product/12.1.0.2.BP170117 -> Home "OraHome2",   contains 12.1.0.2.BP170117

Server C
/u01/app/oracle/product/12.1.0            -> Home "OraHome1",   contains clean 12.1.0.1
/u01/app/oracle/product/12.1.0.2          -> Home "DBHome_1",   contains 12.1.0.2.BP170117

Server A

/u01/app/oracle/product/12.1.0 -> Home "OraHOme12C", contains clean 12.1.0.2

Server B

/u01/app/oracle/product/12.1.0.2 -> Home "OraHome1", contains 12.1.0.2.PSU161018

/u01/app/oracle/product/12.1.0.2.BP170117 -> Home "OraHome2", contains 12.1.0.2.BP170117

Server C

/u01/app/oracle/product/12.1.0 -> Home "OraHome1", contains clean 12.1.0.1

/u01/app/oracle/product/12.1.0.2 -> Home "DBHome_1", contains 12.1.0.2.BP170117

A better approach, would be starting having some naming conventions, e.g.:

Server A
/u01/app/oracle/product/12.1.0.2           -> Home "Ora12cR2",           contains clean 12.1.0.2

Server B
/u01/app/oracle/product/12.1.0.2.PSU161018 -> Home "Ora12cR2_PSU161018", contains 12.1.0.2.PSU161018
/u01/app/oracle/product/12.1.0.2.BP170117  -> Home "Ora12cR2_BP170117",  contains 12.1.0.2.BP170117

Server C
/u01/app/oracle/product/12.1.0.1           -> Home "Ora12cR1",           contains clean 12.1.0.1
/u01/app/oracle/product/12.1.0.2.BP170117  -> Home "Ora12cR2_BP170117",  contains 12.1.0.2.BP170117

Server A

/u01/app/oracle/product/12.1.0.2 -> Home "Ora12cR2", contains clean 12.1.0.2

Server B

/u01/app/oracle/product/12.1.0.2.PSU161018 -> Home "Ora12cR2_PSU161018", contains 12.1.0.2.PSU161018

/u01/app/oracle/product/12.1.0.2.BP170117 -> Home "Ora12cR2_BP170117", contains 12.1.0.2.BP170117

Server C

/u01/app/oracle/product/12.1.0.1 -> Home "Ora12cR1", contains clean 12.1.0.1

/u01/app/oracle/product/12.1.0.2.BP170117 -> Home "Ora12cR2_BP170117", contains 12.1.0.2.BP170117

In the next blog post, I will talk about common patching patterns and their pitfalls.

Basic Vagrantfile for multiple groups of VMs

Posted on April 5, 2018 by Ludovico

In case you want to prepare multiple sets of machines quickly using Vagrant, ready for different setups, this might be something for you:

## -*- mode: ruby -*-
## vi: set ft=ruby :

require 'ipaddr'

###############################
# CUSTOM CONFIGURATION START
###############################

# lab_name is the name of the lab where all the files will be organized.
lab_name = "lab_bigdata"

# here is where you download your software, so it will be available to the VMs.
sw_path  = "C:\\Users\\ludov\\Downloads\\Software"

# cluster(s) definition
clusters = [
  {
  :prefix  => "hadoop", 				# prefix: VMs will be named prefix01, prefix02, etc
  :domain  => "ludovicocaldara.net",	# domain name
  :box     => "ludodba/ol7.3-base",		# base box, either "ludodba/ol7.3-base" or "ludodba/ubu1604"
  :nodes   => 3,						# number of nodes for this cluster
  :cpu     => 1,
  :mem     => 2048,
  :publan  => IPAddr.new("192.168.56.0/24"), 	# public lan for the cluster
  :publan_start => 121							# starting IP, each VM will increment it by one
  },
  {
  :prefix  => "kafka",							# eventually, continue with another cluster!
  :domain  => "ludovicocaldara.net",
  :box     => "ludodba/ol7.3-base",
  :nodes   => 1,
  :cpu     => 1,
  :mem     => 2048,
  :publan  => IPAddr.new("192.168.56.0/24"),
  :publan_start => 131
  },
  {
  :prefix  => "postgres",
  :domain  => "ludovicocaldara.net",
  :box     => "ludodba/ubu1604",
  :nodes   => 1,
  :cpu     => 1,
  :mem     => 2048,
  :publan  => IPAddr.new("192.168.56.0/24"),
  :publan_start => 141
  }
]

###############################
# CUSTOM CONFIGURATION END
###############################

######################################################
# Extending Class IPAddr to add the CIDR to the lan
class IPAddr
  def to_cidr_s
    if @addr
      mask = @mask_addr.to_s(2).count('1')
      "#{to_s}/#{mask}"
    else
      nil
    end
  end
end # extend class IPAddr

########
# MAIN #
########

Vagrant.configure(2) do |config|
  config.ssh.username = "root"  	# my boxes are password based for simplicity
  config.ssh.password = "vagrant"
  config.vm.graceful_halt_timeout = 360	# in case you install grid infra... do not force shutdown after a few seconds

  if File.directory?(sw_path)
    # our shared folder for oracle 12c installation files (uid 54320 is grid, uid 54321 is oracle)
    config.vm.synced_folder sw_path, "/media/sw", :mount_options => ["dmode=775","fmode=775","uid=54322","gid=54328"]
  end

  # looping through each cluster
  (0..(clusters.length-1)).each do |cluid|

    # assign variable clu to current cluster, for convenience
    clu = clusters[cluid]
      
    # looping through each node in the cluster
    (1..(clu[:nodes])).each do |nid|

      # let's start from the last node (see RAC Attack automation for the reason) :-)
      nid = clu[:nodes]+1-nid
      config.vm.define vm_name = "#{clu[:prefix]}%02d" % nid do |cnf|
	  
		# set the right box for the VM
		cnf.vm.box = clu[:box]
		if (clu[:box_version]) then
			cnf.vm.box_version = clu[:box_version]
		end #if
		
		# the new vm name
        vm_name = "#{clu[:prefix]}%02d" % nid
        fqdn = "#{vm_name}.#{clu[:domain]}"
        cnf.vm.hostname = "#{fqdn}"

		# incrementing public ip for the cluster
        pubip = clu[:publan].|(clu[:publan_start]+nid-1).to_s

        cnf.vm.provider :virtualbox do |vb|
          #vb.linked_clone = true  # in case you want thin provisioning. read the vagrant doc before setting it
          vb.name = vm_name
          vb.gui = false
          vb.customize ["modifyvm", :id, "--memory", clu[:mem]]
          vb.customize ["modifyvm", :id, "--cpus",   clu[:cpu]]
          vb.customize ["modifyvm", :id, "--groups", "/#{lab_name}/#{clu[:prefix]}"]
        end #config.vm.provider
		
        # Configuring virtualbox network for #{pubip}
        cnf.vm.network :private_network, ip: pubip

      end #config.vm.define
    end #loop nodes
  end  #loop clusters
end #Vagrant.configure

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

## -*- mode: ruby -*-

## vi: set ft=ruby :

require 'ipaddr'

###############################

# CUSTOM CONFIGURATION START

###############################

# lab_name is the name of the lab where all the files will be organized.

lab_name = "lab_bigdata"

# here is where you download your software, so it will be available to the VMs.

sw_path = "C:\\Users\\ludov\\Downloads\\Software"

# cluster(s) definition

clusters = [

{

:prefix => "hadoop", # prefix: VMs will be named prefix01, prefix02, etc

:domain => "ludovicocaldara.net", # domain name

:box => "ludodba/ol7.3-base", # base box, either "ludodba/ol7.3-base" or "ludodba/ubu1604"

:nodes => 3, # number of nodes for this cluster

:cpu => 1,

:mem => 2048,

:publan => IPAddr.new("192.168.56.0/24"), # public lan for the cluster

:publan_start => 121 # starting IP, each VM will increment it by one

{

:prefix => "kafka", # eventually, continue with another cluster!

:domain => "ludovicocaldara.net",

:box => "ludodba/ol7.3-base",

:nodes => 1,

:cpu => 1,

:mem => 2048,

:publan => IPAddr.new("192.168.56.0/24"),

:publan_start => 131

{

:prefix => "postgres",

:domain => "ludovicocaldara.net",

:box => "ludodba/ubu1604",

:nodes => 1,

:cpu => 1,

:mem => 2048,

:publan => IPAddr.new("192.168.56.0/24"),

:publan_start => 141

}

]

###############################

# CUSTOM CONFIGURATION END

###############################

######################################################

# Extending Class IPAddr to add the CIDR to the lan

class IPAddr

def to_cidr_s

if @addr

mask = @mask_addr.to_s(2).count('1')

"#{to_s}/#{mask}"

else

nil

end

end # extend class IPAddr

########

# MAIN #

########

Vagrant.configure(2) do |config|

config.ssh.username = "root" # my boxes are password based for simplicity

config.ssh.password = "vagrant"

config.vm.graceful_halt_timeout = 360 # in case you install grid infra... do not force shutdown after a few seconds

if File.directory?(sw_path)

# our shared folder for oracle 12c installation files (uid 54320 is grid, uid 54321 is oracle)

config.vm.synced_folder sw_path, "/media/sw", :mount_options => ["dmode=775","fmode=775","uid=54322","gid=54328"]

end

# looping through each cluster

(0..(clusters.length-1)).each do |cluid|

# assign variable clu to current cluster, for convenience

clu = clusters[cluid]

# looping through each node in the cluster

(1..(clu[:nodes])).each do |nid|

# let's start from the last node (see RAC Attack automation for the reason) :-)

nid = clu[:nodes]+1-nid

config.vm.define vm_name = "#{clu[:prefix]}%02d" % nid do |cnf|

# set the right box for the VM

cnf.vm.box = clu[:box]

if (clu[:box_version]) then

cnf.vm.box_version = clu[:box_version]

end #if

# the new vm name

vm_name = "#{clu[:prefix]}%02d" % nid

fqdn = "#{vm_name}.#{clu[:domain]}"

cnf.vm.hostname = "#{fqdn}"

# incrementing public ip for the cluster

pubip = clu[:publan].|(clu[:publan_start]+nid-1).to_s

cnf.vm.provider :virtualbox do |vb|

#vb.linked_clone = true # in case you want thin provisioning. read the vagrant doc before setting it

vb.name = vm_name

vb.gui = false

vb.customize ["modifyvm", :id, "--memory", clu[:mem]]

vb.customize ["modifyvm", :id, "--cpus", clu[:cpu]]

vb.customize ["modifyvm", :id, "--groups", "/#{lab_name}/#{clu[:prefix]}"]

end #config.vm.provider

# Configuring virtualbox network for #{pubip}

cnf.vm.network :private_network, ip: pubip

end #config.vm.define

end #loop nodes

end #loop clusters

end #Vagrant.configure

The nice thing, (beside speeding up the creation and basic configuration) is the organization of the directories. The configuration at the beginning of the script will result in 5 virtual machines:

your VM directory
        |- lab_bigdata 
                |- hadoop
                        |- hadoop01  (ol7)
                        |- hadoop02  (ol7)
                        |- hadoop03  (ol7)
                |- kafka
                        |- kafka01   (ol7)
                |- postgres
                        |- postgres01  (ubuntu 16.04)

your VM directory

|- lab_bigdata

|- hadoop

|- hadoop01 (ol7)

|- hadoop02 (ol7)

|- hadoop03 (ol7)

|- kafka

|- kafka01 (ol7)

|- postgres

|- postgres01 (ubuntu 16.04)

It is based, in part (but modified and simplified a lot), from the RAC Attack automation scripts by Alvaro Miranda.

I have a more complex version that automates all the tasks for a full multi-cluster RAC environment, but if this is your requirement, I would rather check oravirt scripts on github (https://github.com/oravirt) . They are much more powerful and complete (and complex…) than my Vagrantfile. 🙂

Cheers

Removing passwords from Oracle scripts: Wallets and Proxy Users

Posted on February 3, 2014 by Ludovico

Very often I encounter customers that include Oracle account passwords in their scripts in order to connect to the database.
For DBAs, sometimes things are
easier when they run scripts locally using the oracle account, since the “connect / as sysdba” usually do the job, even with some security concerns. But what if we need other users or we need to connect remotely?
Since longtime Oracle supplies secure wallets and the proxy authentication. Let’s see what they are and how to use them.

Secure Wallet
Secure wallets are managed through the tool mkstore. They allow to store a username and password in a secure wallet accessible only by its owner. The wallet is then accessed by the Oracle Client to connect to a remote database, meaning that you DON’T HAVE to specify any username and password!

Let’s see how to implement this the quick way:

Create a directory that will contain your wallet:

$ mkdir .wlt

1	$ mkdir .wlt

Create the wallet, use an arbitrary complex password to protect it:

$ mkstore -wrl /home/ludovico/.wlt -create
Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Enter password:

Enter password again:

$ ls -lia .wlt

total 16
1973369 drwxrwxr-x 2 ludovico ludovico 4096 Feb 2 22:33 .
1973368 drwx------ 3 ludovico ludovico 4096 Feb 2 22:33 ..
1973377 -rw------- 1 ludovico ludovico 2901 Feb 2 22:33 cwallet.sso
1973376 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 cwallet.sso.lck
1973375 -rw------- 1 ludovico ludovico 2856 Feb 2 22:33 ewallet.p12
1973373 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 ewallet.p12.lck

$ mkstore -wrl /home/ludovico/.wlt -create

Oracle Secret Store Tool : Version 12.1.0.1

Enter password:

Enter password again:

$ ls -lia .wlt

total 16

1973369 drwxrwxr-x 2 ludovico ludovico 4096 Feb 2 22:33 .

1973368 drwx------ 3 ludovico ludovico 4096 Feb 2 22:33 ..

1973377 -rw------- 1 ludovico ludovico 2901 Feb 2 22:33 cwallet.sso

1973376 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 cwallet.sso.lck

1973375 -rw------- 1 ludovico ludovico 2856 Feb 2 22:33 ewallet.p12

1973373 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 ewallet.p12.lck

Immagine that you’ve a user created with a very complex password:

SQL > create user batch identified by sDGVA_NNZBNsdRiW2h9mSyzbqBYJo;

User created.

SQL > create user batch identified by sDGVA_NNZBNsdRiW2h9mSyzbqBYJo;

User created.

Then you need to insert these credentials, including the connect string, into the wallet.

$ mkstore -wrl /home/ludovico/.wlt -createCredential PROD batch

1	$ mkstore -wrl /home/ludovico/.wlt -createCredential PROD batch

Oracle Secret Store Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password: 

Re-enter your secret/Password: 

Enter wallet password:

Create credential oracle.security.client.connect_string1

Oracle Secret Store Tool : Version 12.1.0.1

Your secret/Password is missing in the command line

Enter your secret/Password:

Re-enter your secret/Password:

Enter wallet password:

Create credential oracle.security.client.connect_string1

Keep in mind that you can have multiple credentials in the wallet for different remote descriptors (connect strings), but if you want many credentials for the very same connect string you need to create different wallets in different directories.

Now you need to tell your Oracle Client to use that wallet by using the wallet_location parameter in your sqlnet.ora, so you need to have a private TNS_ADMIN:

$ ls -l $TNS_ADMIN
total 8
-rw-r--r-- 1 ludovico ludovico 169 Feb 2 22:47 sqlnet.ora
-rw-r----- 1 ludovico ludovico 1751 Feb 2 22:45 tnsnames.ora

$ cat  $TNS_ADMIN/sqlnet.ora
WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=/home/ludovico/.wlt)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

$ ls -l $TNS_ADMIN

total 8

-rw-r--r-- 1 ludovico ludovico 169 Feb 2 22:47 sqlnet.ora

-rw-r----- 1 ludovico ludovico 1751 Feb 2 22:45 tnsnames.ora

$ cat $TNS_ADMIN/sqlnet.ora

WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=/home/ludovico/.wlt)))

SQLNET.WALLET_OVERRIDE = TRUE

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

If everything’s alright, you should be able to connect to the database PROD as the batch user, without specifying any username or password.

$ sqlplus /@PROD

SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 22:50:49 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "BATCH"

$ sqlplus /@PROD

SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 22:50:49 2014

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user

USER is "BATCH"

Attention: when mkstore modifies the wallet, only the clients with the same or above versions will be able to access the wallet, so if you access your wallet with a 11g client you shouldn’t modify the wallet with a 12c version of mkstore. This is not documented by Oracle, but you can infer it from different “not a bug” entries on MOS 🙂

Proxy Users
You can push things a little farther, and hook your wallet with a proxy user, in order to connect to arbitrary users. That’s it, a proxy user is entitled to connect to the database on behalf of other users. In this example, we’ll see how, through the batch account, we can connect as OE, SH or HR:

SYSTEM@PROD > alter user OE grant connect through BATCH;

User altered.

SYSTEM@PROD > alter user HR grant connect through BATCH;

User altered.

SYSTEM@PROD > alter user SH grant connect through BATCH;

User altered.

SYSTEM@PROD > select proxy, client from dba_proxies;

PROXY CLIENT
---------- --------------------
BATCH HR
BATCH SH
BATCH OE

SYSTEM@PROD > alter user OE grant connect through BATCH;

User altered.

SYSTEM@PROD > alter user HR grant connect through BATCH;

User altered.

SYSTEM@PROD > alter user SH grant connect through BATCH;

User altered.

SYSTEM@PROD > select proxy, client from dba_proxies;

PROXY CLIENT

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

BATCH HR

BATCH SH

BATCH OE

Now I can specify with which user I want to work on the DB, connect to it through the batch account, without specifying the password thanks to the wallet:

$ sqlplus [SH]@PROD

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL > show user

USER is "SH"
SQL > connect [HR]@PROD
Connected.

SQL > show user
USER is "HR"
SQL> connect [OE]/@PROD
Connected.

SQL> show user

USER is "OE"
OE@PROD > select sys_context('USERENV','PROXY_USER') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
----------------------------------------------------------------
BATCH

$ sqlplus [SH]@PROD

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL > show user

USER is "SH"

SQL > connect [HR]@PROD

Connected.

SQL > show user

USER is "HR"

SQL> connect [OE]/@PROD

Connected.

SQL> show user

USER is "OE"

OE@PROD > select sys_context('USERENV','PROXY_USER') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')

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

BATCH

See how it’s easy? But don’t forget to keep your wallet secure using unix/windows permissions!

Oracle Database 12c: sequence.nextval as default and identity columns

Posted on June 27, 2013 by Ludovico

Finally! I can count how many times I’ve seen questions like this one.

The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:

SQL> create sequence foo_seq;

Sequence created.

SQL> create table foo (
2 id number default foo_seq.nextval,
3 bar varchar2(50) not null,
4 constraint foo_pk primary key (id)
5 );

Table created.

SQL> insert into foo (bar) values ('baz');

1 row created.

SQL> insert into foo (bar) values ('test');

1 row created.

SQL> insert into foo (bar) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

ID BAR
---------- --------------------------------------------------
1 baz
2 test
3 whoo

SQL> create sequence foo_seq;

Sequence created.

SQL> create table foo (

2 id number default foo_seq.nextval,

3 bar varchar2(50) not null,

4 constraint foo_pk primary key (id)

5 );

Table created.

SQL> insert into foo (bar) values ('baz');

1 row created.

SQL> insert into foo (bar) values ('test');

1 row created.

SQL> insert into foo (bar) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

ID BAR

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

1 baz

2 test

3 whoo

But Oracle has fixed this twice, in the new release it’s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:

SQL> create table bar (
2 id number generated as identity,
3 foo varchar2(50) not null,
4 constraint bar_pk primary key (id)
5 );

Table created.

SQL> insert into bar (foo) values ('baz');

1 row created.

SQL> insert into bar (foo) values ('test');

1 row created.

SQL> insert into bar (foo) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bar;

ID FOO
---------- --------------------------------------------------
1 baz
2 test
3 whoo

SQL> create table bar (

2 id number generated as identity,

3 foo varchar2(50) not null,

4 constraint bar_pk primary key (id)

5 );

Table created.

SQL> insert into bar (foo) values ('baz');

1 row created.

SQL> insert into bar (foo) values ('test');

1 row created.

SQL> insert into bar (foo) values ('whoo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bar;

ID FOO

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

1 baz

2 test

3 whoo

I’ve said “explicitly” because actually a sequence is created with a system-generated name, so you’ll still need to deal with sequences.

TABLE_NAME TABLE_TYPE
------------------------------- -----------
ISEQ$$_23657 SEQUENCE

TABLE_NAME TABLE_TYPE

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

ISEQ$$_23657 SEQUENCE

cheers

Ludo