Connect SQL Developer 18.3 with Oracle Autonomous Data Warehouse Cloud

Download the client credentials from your Oracle Cloud account for your Autonomous Data Warehouse Instance. Make sure you note down the username (ADMIN) & password, this will be required later during the connection process

1. Open SQL Developer

create new connection > connection type : cloud wallet

Screen Shot 2018-11-11 at 1.16.21 AM

2. Download JCE files from https://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html
Unzip the files and it will create a folder called “UnlimitedJCEPolicyJDK8” with below files

US_export_policy.jar
local_policy.jar
README.txt

3. Find path of your JDK 8 and the policy folder

On my Mac it was at : /Library/Java/JavaVirtualMachines/jdk1.8.0_152.jdk

The policy folder which contains jce files is in ../Contents/Home/jre/lib/security

cd /Library/Java/JavaVirtualMachines/jdk1.8.0_152.jdk/Contents/Home/jre/lib/security

4. Copy unlimited access jce files to the policy folder in jdk directory on your machine

sudo cp -p *.jar /Library/Java/JavaVirtualMachines/jdk1.8.0_152.jdk/Contents/Home/jre/lib/security

5. Restart SQL Developer and if everything goes well you can see the configuration file, this is the place where you will give the path for the client credentials

Screen Shot 2018-11-11 at 1.15.26 AM

Configuration file : wallet_DB201811102318.zip

Connection Name : Test Autonomous DB Warehouse

Username : ADMIN

Password : ***********

Do a test and if it is a success, you can save the connection. After you connect, you can create tables, Pl/SQL packages as you would do normally in SQL Developer

Screen Shot 2018-11-11 at 1.23.48 AM

 

 

Oracle Cloud : Introduction

Oracle has been an incumbent to the cloud space having lost a lot of ground to Amazon and Microsoft.But that hasn’t stopped them to go all guns blazing at the cloud market. Playing on it’s strength of offering Enterprise Applications in a heavily crowded SaaS space along with PaaS and IaaS offerings. Oracle has been pushing aggressively to put their Enterprise on-premise customers to the cloud, their initial offering in this space was nothing to talk about. But the game has changed in the last year with the launch of the Autonomous Data warehouse and Transaction processing cloud .

So after months of trial & error I finally decided to get an Oracle Cloud account. I was not to sure about the general availability of Oracle cloud in Australia, but lucky for me it is available for trial in Australia, though the regions it shows available does not include a Australian availability zone

Screen Shot 2018-11-11 at 12.02.01 AM.png

To get an Oracle Cloud trial account (with 400$ AUD free credit for 3o days) all you need is an email address and a credit card. The credit card is not charged but only a hold of 1$ is put on the card and returned after 3-5 business days.

Head over to https://cloud.oracle.com/home to begin your trial

Once you are done with all the formalities it sends you a temporary password to the email address used for registration. Upon first login you are asked to reset the password.

And there you are, the Oracle Cloud Dashboard page

Screen Shot 2018-11-11 at 12.07.10 AM

This is going to be series and in the next few posts i will be looking at creating an autonomous data warehouse and a transaction processing cloud database.

Install Oracle Database 12cR2 Silent Mode on CENT OS 7 & Oracle Linux 7

If you are going to work on Oracle Database in cloud or in environments where X11 client or server is not available. It is always good to learn how to do a silent installation of Oracle. Specially in todays CI/CD devops model where dba’s have to provision DB servers using Ansible or other such tools. The base of doing such automation will require you to install Oracle from cli. In the below article we will do below 3 steps without any graphical user interface like X11, gnome, kde,vnc etc

1. Install Oracle 12.2.0.1 Database Software using a Response File

2. Create a Database with Response File

3. Create a Listener and Register the Database

I did the below setup on a cloud hosted server, running CENT OS 7, which came with bare minimum packages. It is always good to add different repositories which host all the important softwares for Linux

Now lets get started….

1. Add Groups

# groupadd oinstall
# groupadd dba
# groupadd oper
# groupadd backupdba
# groupadd dgdba
# groupadd kmdba
# groupadd asmdba
# groupadd asmoper
# groupadd asmadmin
# groupadd racdba

2. Add Oracle User

# useradd oracle -g oinstall -G dba,oper,backupdba,dgdba,kmdba

# passwd oracle

3. Check for missing packages

rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' binutils \
compat-libcap1 \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
gcc \
gcc-c++ \
glibc \
glibc-common \
glibc-devel \
glibc-headers \
ksh \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
libXext \
libXtst \
libX11 \
libXau \
libxcb \
libXi \
sysstat \
unixODBC \
unixODBC-devel

 

4. Install missing packages with Yum
eg:
# rpm -Uvh compat-libcap1

 

5. Update Kernel Parameters

Open sysctl.conf file and add the kernel parameters

# vi /etc/sysctl.conf

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

 

6. Add Limits for Oracle user

Open file limits.conf and add below parameters

# vi /etc/security/limits.conf

 

–shell limits for users oracle 12gR1

oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768

 

7. Create Bash Profile for Oracle user

 

# mkdir -p /u01/app/oracle/product/12.2.0/dbhome_1

# mkdir -p /u01/app/oracle/product/12.2.0/dbhome_1/network/admin

# mkdir -p /u01/app/oraInventory

# chown oracle:oinstall -R /u01

# vi .bash_profile

 

export TMP=/tmp

export ORACLE_HOSTNAME=easyoradba.com
export ORACLE_UNQNAME=ORA12C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
export ORACLE_SID=ORA12C

PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

alias ob=’cd $ORACLE_BASE’
alias oh=’cd $ORACLE_HOME’
alias tns=’cd $ORACLE_HOME/network/admin’
alias envo=’env | grep ORACLE’
umask 022
envo

# envo

8. Disable SELINUX and IPTABLES (Not a good thing to do on a production server, only for test !!!)

# vi /etc/selinux/config

set SELINUX=disabled

# service iptables stop
# chkconfig iptables off

 

9. Download Oracle Database binary to home directory and prepare the staging area

# su – oracle

# cd /home/oracle

# unzip Oracle_Database_12CR2_linux_x64_12201.zip

This creates a directory ‘database’ with the installation files in it

 

10. Create Response File and Install Database Software in Silent Mode

Create a copy of the response file and add parameters for installation

# cd /home/oracle/database/response

# ls -ltrh

-rwxrwxr-x 1 oracle oinstall 6.1K Jun 20 2016 netca.rsp
-rwxrwxr-x 1 oracle oinstall 25K Jan 5 2017 dbca.rsp
-rw-rw-r– 1 oracle oinstall 23K Jan 26 2017 db_install.rsp

# cp db_install.rsp ../

# vi db_install.rsp

Now add below values for the parameters as per the directory structure you created

 

oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true

Make sure the above parameters are not duplicated if you will copy and paste the above values 🙂

 

# cd /home/oracle/database

# ./runInstaller -silent -responseFile /home/oracle/database/db_install.rsp

if you get error Oracle runInstaller – CreateOUIProcess(): 13 : Permission denied

Then change temporary directory to home directory and run installer again

# mkdir -p $HOME/tmp
# export TMP=$HOME/tmp

Run the installer again

# ./runInstaller -silent -responseFile /home/oracle/database/db_install.rsp

 

11. Run root scripts and complete install

If all goes well you will get below message

Starting Oracle Universal Installer…

Checking Temp space: must be greater than 500 MB. Actual 68933 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3958 MB Passed
Preparing to launch Oracle Universal Installer from /home/oracle/tmp/OraInstall2018-07-05_04-21-40AM. Please wait …[oracle@vps database]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2018-07-05_04-21-40AM.log
The installation of Oracle Database 12c was successful.
Please check ‘/u01/app/oraInventory/logs/silentInstall2018-07-05_04-21-40AM.log’ for more details.

As a root user, execute the following script(s):
1. /u01/app/oracle/product/12.2.0/dbhome_1/root.sh

 

Run the script by opening another session with root user and setup will complete successfully

Successfully Setup Software.

 

12. Create Database in Silent Mode

find the file dbca.rsp and make a copy of it and update below parameters

# cd $ORACLE_HOME/assistants/dbca/

# cp dbca.rsp $ORACLE_HOME

# vi dbca.rsp

gdbName=ora12c
sid=ora12c
databaseConfigType=SI
createAsContainerDatabase=false
templateName=General_Purpose.dbc
sysPassword=sys123
systemPassword=sys123
emConfiguration=NONE
datafileDestination=/u01/oradata
recoveryAreaDestination=/u01/fra
storageType=FS
characterSet=AL32UTF8
sampleSchema=TRUE
memoryPercentage=40

ora12c is global database name

# dbca -silent -createDatabase -responseFile dbca.rsp

53% complete
54% complete
55% complete
58% complete
Completing Database Creation
59% complete
60% complete
61% complete
64% complete
68% complete
69% complete
Executing Post Configuration Actions
100% complete

 

13. Create Listener and Register Database

# cd $ORACLE_HOME/network/admin

# vi listener.ora

LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

# lsnrctl start

# sqlplus “/as sysdba”

SQL> alter database register;

# lsnrctl status

Services Summary…
Service “ora12c” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
Service “ora12cXDB” has 1 instance(s).
Instance “ora12c”, status READY, has 1 handler(s) for this service…
The command completed successfully

 

 

Change SYSMAN Password in Cloud Control 12c

Change OEM SYSMAN password

Incase you have forgotten the sysman password

1. Login to Cloud Control 12c database

SQL> alter user sysman identified by abc123;

2. Stop OMS

$OMS_HOME/bin/emctl stop oms

( DO not use -all postfix)

3. Change password in Repository

$OMS_HOME/bin/emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys123
-new_pwd abc123

changing passwords in backend …
Passwords changed in backend successfully.
Updating repository password in Credential Store…
Successfully updated Repository password in Credential Store.
Restart all the OMSs using ’emctl stop oms -all’ and ’emctl start oms’.
Successfully changed repository password.

4. Stop and Start OMS completely

$OMS_HOME/bin/emctl stop oms -all

$OMS_HOME/bin/emctl start oms