ORA-02016: Cannot use a sub-query in START WITH on a remote database

ORA- Error:

SELECT level, LPAD(ename,level*5,' ') ename
FROM employee
start with empno=100
CONNECT_BY by nocycle prior empno = mgr
/

ORA-02016: Cannot use a sub-query in START WITH on a remote database

How did we survive after infecting with covid-19 : a personal experience


It's been just a few weeks since i have written this article How to avoid getting infected corona virus , and in-spite of following all the precautions mentioned we (whole family) got trapped by Covid-19.

In following sections , will write about certain topics which will help someone who is going through the covid crisis and for others it will help to stay alert ..




  • How did we infected by covid ?
  • What are the various stages of impact on human body?
  • What medications given by doctors ?
  • Which home remedies we followed ?

Database Auto Startup in oracle 19c


Follow the Steps below to enable auto startup of DB instance , Listener and PDB's

1. Create dbora file as below with ORACLE_HOME


[root@oraclesrv ~]# cat /etc/init.d/dbora
#! /bin/sh -x
#
# chkconfig: 2345 80 05
# description: start and stop Oracle Database Enterprise Edition on Oracle Linux 5 and 6
#
# In /etc/oratab, change the autostart field from N to Y for any
# databases that you want autostarted.
#
# Create this file as /etc/init.d/dbora and execute:
#  chmod 750 /etc/init.d/dbora
#  chkconfig --add dbora
#  chkconfig dbora on
# Note: Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
# ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
ORACLE_HOME=/u01/app/oracle/db1
#
# Note: Change the value of ORACLE to the login name of the oracle owner
ORACLE=oracle
PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case $1 in
'status')
       echo -n $"Oracle Process: "
      su $ORACLE -c "ps -ef | grep pmon | grep -v grep; ps -ef | grep -i listener | grep -v grep;" &
        ;;
'start')
       echo -n $"Starting Oracle: "
      su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
        ;;
'stop')
       echo -n $"Shutting down Oracle: "
       su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
        ;;
'restart')
       echo -n $"Shutting down Oracle: "
       su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME" &
        sleep 5
      echo -n $"Starting Oracle: "
      su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &
        ;;
*)
        echo "usage: $0 {start|stop|restart}"
        exit
        ;;
esac
exit


Step by step installation of Oracle 19c database on Oracle Linux Virtual box


In this article will perform step by step installation of Oracle 19c single instance database on Oracle Linux 7 operating system hosted on virtual box.

Prior to proceeding with the steps , do refer following link to setup a LAB environment which includes Installation of Oracle Virtual Box on windows and operating system Oracle Linux 7 setup.




Oracle Linux Installation on Virtual Box



Oracle 19c installation steps will include ..


1. Setting up internet network connection from virtual machine

2. Installation of prerequisite oracle 19c

3. Configuring & downloading Oracle 19c home setup

4. Performing oracle 19c installation

5. De-installation / Setup Cleanup



Lets begin ..


1. Setting up internet network connection from virtual machine 

Internet connectivity within virtual host is must to install pre-requisite rpm for oracle 19c database installation as well few ad-hoc dependent rpm's using yum

Verify if NAT is selected for Adpater 1..



Add port forwarding for connecting the virtual machine from outside terminal ..








In windows host make sure wireless network ethernet adapter internet sharing is enabled from below path


Control Panel\Network and Internet\Network Connections





Make sure ONBOOT=yes in /etc/sysconfig/network-scripts 





Either reboot the virtual machine or use ifdown & ifup to reflect appropriate nameserver entry in resolv.conf post host entry addition 














ifconfig -a output should look like below ..






2. Installation of prerequisite oracle 19c 


Downlod & install the oracle 19c database preinstall rpm which will create the user, groups and install necessary kernal packages/rpm's required as a prerequisites ..


[root@oraclesrv ~]# yum -y install oracle-database-preinstall-19c
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-19c.









3. Configuring & downloading Oracle 19c home setup 


Create installation directory for oracle home ..


[root@oraclesrv ~]# id oracle
uid=54321(oracle)gid=54321(oinstall)groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)
 
[root@oraclesrv ~]# mkdir -p /u01/app/oracle/db1

[root@oraclesrv ~]# chown -R oracle:oinstall /u01/app/oracle/db1

[root@oraclesrv ~]# ls -ld /u01/app/oracle/db1/
drwxr-xr-x 2 oracle oinstall 6 Nov  4 12:22 /u01/app/oracle/db1/



Download oracle home setup and place it into shared location as shown in Oracle Linux Image Setup  ..


Grant permission on shared folder to oracle osuser ..


[oracle@oraclesrv db1]$ df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             471M     0  471M   0% /dev
tmpfs                488M     0  488M   0% /dev/shm
tmpfs                488M  7.4M  481M   2% /run
tmpfs                488M     0  488M   0% /sys/fs/cgroup
/dev/mapper/ol-root   23G  5.7G   18G  25% /
/dev/sda1           1014M  212M  803M  21% /boot
shared               200G   38G  162G  19% /media/sf_shared
tmpfs                 98M   12K   98M   1% /run/user/42
tmpfs                 98M     0   98M   0% /run/user/0

[oracle@oraclesrv db1]$ ls -lrt /media/sf_shared/
ls: cannot open directory /media/sf_shared/: Permission denied

[oracle@oraclesrv db1]$ exit
logout

[root@oraclesrv ~]# ls -ld /media/sf_shared/
drwxrwx--- 1 root vboxsf 0 Nov  4 12:19 /media/sf_shared/

[root@oraclesrv ~]# usermod -a -G vboxsf oracle.

[root@oraclesrv ~]# su - oracle
Last login: Wed Nov  4 12:23:07 IST 2020 on pts/0

[oracle@oraclesrv ~]$ ls -lrt /media/sf_shared/
total 2987996
-rwxrwx--- 1 root vboxsf 3059705302 Nov  4 12:18 LINUX.X64_193000_db_home.zip
 

Copy and unzip setup file in oracle home location ..


[oracle@oraclesrv ~]$ cd /media/sf_shared/
[oracle@oraclesrv sf_shared]$ ls -lrt
total 2987996
-rwxrwx--- 1 root vboxsf 3059705302 Nov  4 12:18 LINUX.X64_193000_db_home.zip
[oracle@oraclesrv sf_shared]$ cp LINUX.X64_193000_db_home.zip /u01/app/oracle//db1/
 
 
[oracle@oraclesrv sf_shared]$ cd /u01/app/oracle/db1
[oracle@oraclesrv db1]$ ls -lrt
total 2987996
-rwxr-x--- 1 oracle oinstall 3059705302 Nov  4 12:27 LINUX.X64_193000_db_home.zip
[oracle@oraclesrv db1]$ unzip LINUX.X64_193000_db_home.zip


Verify if xclock is able to launch  .. setup appropriate DISPLAY parameter & copy .Xauthority file if required 


[oracle@oraclesrv db1]$ xclock
bash: xclock: command not found...
Similar command is: 'clock'
[oracle@oraclesrv db1]$ exit
logout
[root@oraclesrv ~]# yum install xclock
Loaded plugins: langpacks, ulninfo
ol7_UEKR5                    | 2.5 kB  00:00:00
ol7_latest                   | 2.7 kB  00:00:00
(1/3):ol7_latest/x86_64/group    | 660 kB  00:00:00
(2/3):ol7_latest/x86_64/updateinfo   | 3.1 MB  00:00:01
(3/3):ol7_latest/x86_64/primary_db   |  30 MB  00:00:04
Resolving Dependencies
--> Running transaction check
---> Package xorg-x11-apps.x86_64 0:7.7-7.el7 will be installed
--> Processing Dependency: libXaw.so.7()(64bit) for package: xorg-x11-apps-7.7-7.el7.x86_64
--> Running transaction check
---> Package libXaw.x86_64 0:1.0.13-4.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=====================================================================
 Package       Arch        Version        Repository           Size
=====================================================================
Installing:
xorg-x11-apps    x86_64    7.7-7.el7      ol7_latest         307 k

Installing for dependencies:
libXaw           x86_64    1.0.13-4.el7   ol7_latest         191 k
 
Transaction Summary
=====================================================================
Install  1 Package (+1 Dependent package)
 
Total download size: 498 k
Installed size: 1.2 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): libXaw-1.0.13-4.el7.x86_64.rpm  | 191 kB  00:00:00
(2/2): xorg-x11-apps-7.7-7.el7.x86_64.rpm   | 307 kB  00:00:00
--------------------------------------------------------------------
Total                              551 kB/s | 498 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : libXaw-1.0.13-4.el7.x86_64                1/2
  Installing : xorg-x11-apps-7.7-7.el7.x86_64            2/2

  Verifying  : libXaw-1.0.13-4.el7.x86_64                1/2
  Verifying  : xorg-x11-apps-7.7-7.el7.x86_64            2/2
 
Installed:
  xorg-x11-apps.x86_64 0:7.7-7.el7
 
Dependency Installed:
  libXaw.x86_64 0:1.0.13-4.el7
 
Complete!

  





4. Performing oracle 19c installation 


Create separate disks as /u01 & /data . also add swap space as their should not be any exception to be ignored using this link 


[oracle@oraclesrv /]$ cd /u01/app/oracle/db1
[oracle@oraclesrv db1]$ ./runInstaller







































As a new feature in oracle 19c root password to be supplied to avoid manual execution of post installation scripts


Setup up bash profile for environment variables  ..


[oracle@oraclesrv ~]$ cat .bash_profile
# .bash_profile
 # Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
 
# User specific environment and startup programs
ORACLE_HOME=/u01/app/oracle/db1
export ORACLE_HOME
ORACLE_SID=LEARN
export ORACLE_SID
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin
 export PATH
[oracle@oraclesrv ~]$
[oracle@oraclesrv ~]$ . ./.bash_profile



Try accessing EM express https://<servername>:5500/em 








few commands on pdb to play ..










5. De-installation / Setup Cleanup 


[oracle@oraclesrv /]$ cd /u01/app/oracle/db1

[oracle@oraclesrv db1]$ cd deinstall/

[oracle@oraclesrv deinstall]$ ls -lrt
total 172
-rw-r----- 1 oracle oinstall   401 Feb 10  2012 readme.txt
-rwxr-x--- 1 oracle oinstall 33333 Jan  7  2015 sshUserSetup.sh
-rw-r----- 1 oracle oinstall  8479 Sep 19  2017 deinstall.xml
-rwxr-x--- 1 oracle oinstall 33171 Apr 17  2019 deinstall.pl
-rwxr-x--- 1 oracle oinstall 14927 Apr 17  2019 bootstrap.pl
-rw-r----- 1 oracle oinstall 55480 Apr 17  2019 bootstrap_files.lst
drwxr-xr-x 2 oracle oinstall    32 Apr 17  2019 response
drwxr-xr-x 2 oracle oinstall    33 Apr 17  2019 utl
drwxr-xr-x 2 oracle oinstall   102 Apr 17  2019 jlib
-rwxr-x--- 1 oracle oinstall 11223 Nov  4 13:24 deinstall

[oracle@oraclesrv deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2020-11-04_02-45-53PM/logs/
 
############ ORACLE DECONFIG TOOL START ############
######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##
 
Checking for existence of the Oracle home location /u01/app/oracle/db1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
 
## [END] Install check configuration ##
 
 
Network Configuration check config START
Network de-configuration trace file location: /tmp/deinstall2020-11-04_02-45-53PM/logs/netdc_check2020-11-04_02-47-59PM.log
 
Specify all Single Instance listeners that are to be de-configured. Enter .(dot) to deselect all. [LISTENER]:
Network Configuration check config END
 
Database Check Configuration START
Database de-configuration trace file location: /tmp/deinstall2020-11-04_02-45-53PM/logs/databasedc_check2020-11-04_02-47-59PM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home [LEARN]:
 
###### For Database 'LEARN' ######
 
Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database) [1]:
Specify the diagnostic destination location of the database [/u01/app/oracle/diag/rdbms/learn]:
Specify the storage type used by the Database ASM|FS []: FS
 
Specify the list of directories if any database files exist on a shared file system. If 'LEARN' subdirectory is found, then it will be deleted. Otherwise, the specified directory will be deleted. Alternatively, you can specify list of database files with full path [ ]:
 
Specify the fast recovery area location, if it is configured on the file system. If 'LEARN' subdirectory is found, then it will be deleted. []:
 
Specify the database spfile location [ ]:
 
Specify if database Archive Mode is Enabled. y/n [n]:
Database Check Configuration END
 
######################### DECONFIG CHECK OPERATION END #########################
 
 
####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/db1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER
The following databases were selected for de-configuration. The databases will be deleted and will not be useful upon de-configuration : LEARN
Database unique name : LEARN
Storage used : FS
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/tmp/deinstall2020-11-04_02-45-53PM/logs/deinstall_deconfig2020-11-04_02-47-35-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2020-11-04_02-45-53PM/logs/deinstall_deconfig2020-11-04_02-47-35-PM.err'
 
######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /tmp/deinstall2020-11-04_02-45-53PM/logs/databasedc_clean2020-11-04_02-47-59PM.log
Database Clean Configuration START LEARN
This operation may take few minutes.
Database Clean Configuration END LEARN
 
Network Configuration clean config START
 
Network de-configuration trace file location: /tmp/deinstall2020-11-04_02-45-53PM/logs/netdc_clean2020-11-04_02-47-59PM.log
 
De-configuring Single Instance listener(s): LISTENER
 
De-configuring listener: LISTENER
    Stopping listener: LISTENER
    Warning: Failed to stop listener. Listener may not be running.
    Deleting listener: LISTENER
    Listener deleted successfully.
Listener de-configured successfully.
 
De-configuring Naming Methods configuration file...
Naming Methods configuration file de-configured successfully.
 
De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.
 
De-configuring backup files...
Backup files de-configured successfully.
 
The network configuration has been cleaned up successfully.
 
Network Configuration clean config END
 
 
######################### DECONFIG CLEAN OPERATION END ######################### 
####################### DECONFIG CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : LEARN
Following Single Instance listener(s) were de-configured successfully: LISTENER
####################################################################### 
############# ORACLE DECONFIG TOOL END #############
 
Using properties file /tmp/deinstall2020-11-04_02-45-53PM/response/deinstall_2020-11-04_02-47-35-PM.rsp
Location of logs /tmp/deinstall2020-11-04_02-45-53PM/logs/
 
############ ORACLE DEINSTALL TOOL START ############
 
####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/tmp/deinstall2020-11-04_02-45-53PM/logs/deinstall_deconfig2020-11-04_02-47-35-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2020-11-04_02-45-53PM/logs/deinstall_deconfig2020-11-04_02-47-35-PM.err'
 
######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to oraclesrv
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2020-11-04_02-45-53PM/oraInst.loc
Setting oracle.installer.local to false
 
## [END] Preparing for Deinstall ##
 
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
 
Detach Oracle home '/u01/app/oracle/db1' from the central inventory on the local node : Done
 
Delete directory '/u01/app/oracle/db1' on the local node : Done
Delete directory '/u01/app/oraInventory' on the local node : Done
Delete directory '/u01/app/oracle' on the local node : Done
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
 
 
## [START] Oracle install clean ##
 
 
## [END] Oracle install clean ##
 
 
######################### DEINSTALL CLEAN OPERATION END #########################
####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/db1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/db1' on the local node.
Successfully deleted directory '/u01/app/oraInventory' on the local node.
Successfully deleted directory '/u01/app/oracle' on the local node.
Oracle Universal Installer cleanup was successful.
 
Run 'rm -r /etc/oraInst.loc' as root on node(s) 'oraclesrv' at the end of the session.
 
Run 'rm -r /opt/ORCLfmap' as root on node(s) 'oraclesrv' at the end of the session.
Run 'rm -r /etc/oratab' as root on node(s) 'oraclesrv' at the end of the session.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL TOOL END #############
 

Also Refer


Creating new swap and data partition disk in oracle linux on virtual box


In this article we will perform  ..

1. swap disk partition addition to virtual box Oracle linux machine 
2. data disk partition addition to virtual box Oracle linux machine 

Let's Begin  ..

1. swap disk partition addition to virtual box Oracle Linux machine