Oracle DBA Place

Last week I needed to connect a database from remote using VPN. The VPN is connected to a secured network, so I couldn’t connect directly to the database. Since I am using Toad for Oracle which is based on oracle client – I needed a transparent solution for that connection. At the past I wrote a post about Using ssh X11 tunnel through a bastion host to connect to a database server  , at that post I described how to forward SSH and X11. Now I will describe a way to connect to the database using SQL*Net (usually port 1521) on Windows. Download plink from https://www.putty.org , you can download putty package or just standalone plink. Run the following command line: plink -N -L localport:dbserver:dbport getwayuser@getwayserver Do this on both machines (REMOTE PC Windows machine and the server you have access to - Gateway) to chain the SSH tunnels. Example: Gateway server (assuming Linux): ssh -N -L1521:dbserver:1521 dbserveruser@dbserver Your PC: plink -N...

Hi, I succeeded running Oracle dbca, xclock & virt-viewer using Bastion over VPN (with root) 😊 Theoretical Steps: First, connect to machine B and forward [localPort] to C:22 through B A$ ssh -L [localPort]:C:22 B Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11 A$ ssh -X -p [localPort] localhost Now we can run X11 programs on C and have them display on A C$ xclock [localPort] can be any port that you are not already listening to on A, I often use 2222 for simplicity. X11Forwarding should be enabled on server C AllowTcpForwarding should be enabled on server B Actual Steps First, connect to machine B and forward [localPort] to C:22 through B A$ ssh -L [localPort]:C:22 B Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11 From A using putty to localhost using [localPort] enable X11 forwarding to localhost:0.0 Now we can run X11 programs on C and have them display on A ...

Hi, I managed to install Oracle Linux on kvm guest without a need to X console (no GUI, vnc or X11) 1. We can see the PXE menu 2. We can select image to install 3. We need to add console=ttyS0 to the PXE (cobbler is supporting this feature the same way as grub enable you to add parameter to the boot) Disclaimer: I tested this on Oracle Linux 7.5 using libvirt 1.5 # virt-install --hvm --connect qemu:///system --network=bridge:virbr0 --pxe --graphics none --name Oracle_Linux_7.4-x86_64 --ram=756 --vcpus=1 --os-type=linux --os-variant=rhel7 --disk path=/tmp/rhel7-machine.img,size=5 --console pty,target_type=serial --boot 'menu=on,useserial=on' Starting install... Allocating 'rhel7-machine.img'                                                                                ...

It is a while I am following the blog of Mariami Kupatadze , it seems that our background and daily work is around the same area and interest as mine. This week she posted a simple Linux adjustment ( sqlplus backspace – ^H, delete – [[D^, add command history ) for better way of using sqlplus. Since I am also a geek of environment improvements that can ease daily work, I want to add a nice variation. An example of basic rlwrap usage: rlwrap sqlplus / as sysdba or use sqlplus as alias with rlwrap: alias sqlplus='rlwrap sqlplus' After some usage you will notice that default history file is created here ~/.sqlplus_history I usually copy this file to ~/sqlplus.wordlist and edit it with wanted completion word. My personal file looks this way: abort active affirm all_roles alter alternate applied applied_seq# applied_thread# apply apply_finish apply_lag archived archived_seq# archived_thread# archiver async_blocks attribute availability begin bindi...

Previously, you could only update Global Temp Tables on Active Data Guard standby database. From Oracle  19c , you can also update regular tables. Updates on Active Data Guard: Update will be redirected to the primary Primary makes update, generates & sends redo for that update to all standbys Active Data Guard session sees the update in redo apply and resumes Preserves ACID properties for ADG session Redirected update only visible to session before commit; visible to all sessions after commit For “Mostly Read, Occasional Updates ” applications – for example, recording user logins for auditing purposes Enabled by ADG_REDIRECT_DML at system or session level The following setup is based on Active Data guard configuration is MaxAvailability, but the behavior is the same when configuring MaxPerformance A step by step guide to demonstrate this feature: Current configuration Basic setup  The setup is primary database sends its redo logfiles through a f...

Hi, I was asked by our development team to provide the best way to identify database parameters from database host, I was surprised to find so many options. Identifying all instances on the current machine Option 1:  $ ps -ef |grep smon | grep -v grep oracle    3025  1  0  2016 ?   00:00:48 asm_smon_+ASM oracle    11459  1  0 17:24 ?  00:00:00 ora_smon_ fdb oracle SID is  fdb and process id is  11459 Option 2:  $ pgrep  -lf _pmon_ 3025 asm_pmon_+asm 11459 ora_pmon_ fdb oracle SID is  fdb  and process id is  11459 Option 3:  cleaner way for sid: $ ps -ef |grep 'ora_smon_.*$' | grep -v grep | awk -F_ '/ora_smon/{print $NF}' fdb oracle SID is  fdb  Option 4:  When we already know ASM home (grid infrastructure) we can use the cluster commands: $ /oracle/product/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"|awk '...

Putting it In my previous blog ( Enterprise Manager Cloud Control, SNMP, and Plug-Ins – The Background ), I shared my frustration in trying to create a plug-in in OEM. Here are the technical steps for a very simple plug-in. Preparing Plug-in To build your own plug-in, you need to download a development kit: To download EDK using UI, from the Setup menu, select Extensibility, and then select Development Kit. OR To download EDK using EMCLI, run the following steps: Log in to OEM: $ /oracle/em/middleware/bin/emcli login -username=sysman Enter password : Login successful Download the EDK zip archive: $ emcli get_ext_dev_kit Downloading edk.zip File saved as edk.zip File(s) downloaded successfully Unpack the downloaded EDK ZIP archive to a directory on your local system. For example: $ unzip 13.3.0.0.0_edk_partner.zip There are several restrictions and guidelines you should know before you create your plug-in. Most of them can be found ...

Hi, I have been using Oracle Enterprise Manager (OEM or Cloud Control) since it was a client application. (I’m not sure whether it was in the era of Oracle 7 or Oracle 8). In the early stages, OEM was compared to other central monitoring systems (at the time, it was HP OpenView, CA Unicenter, and the like). As a DBA who was responsible for dozens of Oracle databases, I needed a main system like this. Since we had the OEM for "free" as part of a site license, we used it heavily. The problem was that the main IT monitoring tool in our company was another system that already monitored appliances, networks, and applications. So, we decided to keep the OEM to the DBAs and send notifications to this system. (It was monitored 24/7 by a control center.) Like today, we could send alerts by email, run an external script, or send an SNMP trap. We chose the SNMP trap since it is in the core of most monitoring systems and simple to define. OEM needed a lot of attention and maintenan...

In this document I demonstrate how to add local DNS server to be added additionally to an existing DNS, I had this need for creating some additional IP addresses in the DNS for Real Application Cluster (VIP and SCAN) My Testing Environment My Private DNS Server IP Address: 10.20.2.100 Host Name: localdns OS: Oracle Linux Server release 7.5 Client Machine to use DNS IP Address: 10.33.1.120 Host Name: dbhost OS: Oracle Linux Server release 7.4 Current Domain DNS Primary: 192.168.1.10 Secondary: 192.168.1.1 1 DNS entry to add IP Address: 10.33.1.123 Host Name: dbhost-scan Verify that the above IP  address is not being used via ping command $ ping 10.33.1.123 -c1 -w 1 >/dev/null 2>&1 ; if [ $? -eq 1 ]; then echo "This IP is Not used"; else echo "This IP is used"; fi This IP is Not used Install required packages $ sudo yum install bind* -y Define zone files in master configuration ‘named.conf‘ file $ sudo vim /etc/named.conf Change 127.0.01 to a...

Hi, The main subject in this conference was Autonomous Database, it's engine is used by another buzzword called: Machine Learning.  Oracle  claim  they are investing in autonomous to prevent data theft, but it seems as convenience for handling only one version in the cloud. They will use the same "Machine Learning" engine that they used for the optimizer and memory management decisions (This engine is well known for bad decisions). 18c is an alias for 12.2.0 .2  and planned to be available: Autonomous Database for  Data Warehouses -  by December 2017 Autonomous Database for  OLTP  – by June 2018 A companion solution for the "Machine Learning" will be used via the OEM (Cloud Control) to gather: Infrastructure logs: Network, Server, Storage, VM, OS Platform logs: Database, Java, Analytics, etc. Application logs: ERP, CX, HCM, Custom, etc. The logs will be gathered to detect and connect anomalo...

General information In this setup we are: Installing docker Creating Non-root user (ynixon) with sudo and docker privileges ASM device: /dev/sdb1 Enabling sqlnet + ssh to the container Default ASM port is 1521 ssh port 2222 Passwords for root + grid os users in the container are “ynixon” Password for sys ASM user is “ynixon” Grid software is 12.2 without any patches Container Operating system is Oracle Linux 7.5 Within the container, there is no use of UDEV / ASMLIB or ASMFD – the asm_diskstring='/dev/asm*' ,'/dev/*' All test done on regular Ubuntu 14.04 There is a crontab job to keep 15 days of trace files + remove audit files. Prepare host for ASM device Make sure the device has permissions of the same container ids by applying UDEV rules $ vi /etc/udev/rules.d/100-asm.rules KERNEL=="sdb1", NAME="ASM_DISK", OWNER="54421", GROUP="54421", MODE="0660" udevadm trigger --sysname-match=sdb1 --ver...

In the past I wrote a post about  Bypassing the listener and connecting to ASM without a password from Python code Now, I've been asked to prove that it can be done in Java. The solution is almost the same. Java sample code using OCI: $ vi OracleCon.java import java.sql.*; class OracleCon {     public static void main(String args[]) {         try {             Class.forName("oracle.jdbc.driver.OracleDriver");             Connection con = DriverManager.getConnection(                 "jdbc:oracle:oci:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=BEQ) (PROGRAM=oracle) (ARGV0=oracle+ASM) (ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') (ENVS = 'ORACLE_HOME=/oracle/product/12.1.0.2/grid,ORACLE_SID=+ASM'))) (CONNECT_DATA= (SID=+ASM)))", "sys as sysdba", "change on install");             Statement...

Hi, Checking if the Far Sync instance is configured to restart automatically after reboot / shutdown of the host / cluster: $ crsctl stat res ora.fdb1.db -f |grep AUTO_START AUTO_START= restore "restore" means that after restart, the database will remain at the previous state it was before. Changing the cluster to startup the database in any case. $ crsctl stat res ora.fdb1.db -f |grep AUTO_START AUTO_START= always Changing database to start always after reboot $ crsctl modify resource ora.fdb1.db -attr  AUTO_START= always -unsupported Modify the startup state: $ srvctl modify database -o mount View modified configuration $ srvctl config database -d fdb1 -all Database unique name: fdb1 Database name: fdb1 Oracle home: /oracle/product/12.1.0.2/dbhome_1 Oracle user: oracle Spfile: Password file: Domain: Start options: mount Stop options: immediate Database role: FAR_SYNC Management policy: AUTOMATIC Disk Groups: DATA Services: Database is enab...