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...

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, In an earlier version of Data Guard, when the broker had problems, one of the automatic answers I got from support was to recreate the broker configuration. In other words: Drop the configuration.  Create the configuration. If your broker is simple, this is not a huge request. Actually, you could follow Step By Step How to Recreate Data Guard Broker Configuration (Doc ID 808783.1) . But in my case, using Active Data Guard with Far Sync, with many fine-tuned configurations, it was not so convenient. I had a number of ways I tried to be efficient: I could write down all broker commands - to be able to run them again next time. For versions 11.2.0.4 and 12.1.0.2, I found a way to query the broker and re-create a current configuration creation script ( Extract Data Guard Commands ). I also had some issues trying to Drop/remove Far-Sync Configurations from broker 12.1 . On version 12.2.0.1, I found out that the broker metadata was changed, so I had to update my previo...

In Oracle Restart, there are two confusing commands for setting up restart behavior. 1. SRVCTL command. srvctl add/modify database -y {AUTOMATIC | MANUAL} Management policy for the database. If AUTOMATIC (the default), the database is automatically restored to its previous running condition (started or stopped) upon restart of the database host computer. If MANUAL, the database is never automatically restarted upon restart of the database host computer. A MANUAL setting does not prevent Oracle Restart from monitoring the database while it is running and restarting it if a failure occurs. https://docs.oracle.com/cd/E11882_01/server.112/e25494/restart.htm#BABHHAHI 2. CRSCTL command. crsctl modify resource ora.pdb.db -attr AUTO_START=restore -unsupported (The -unsupported syntax is needed for oracle 12c) AUTO_START Indicates whether Oracle Clusterware automatically starts a resource after a cluster server restart. Valid AUTO_START values are: - always: Restarts the resource...

Prior to Oracle 12.2 we had to find some homemade tricks to run the Observer at the background this way: $ nohup dgmgrl -silent sys/P@$$w0rd@prmy_db "start observer" & Finally, From Oracle 12.2 the Observer can run in the background using wallet authentication to connect to primary & standby databases and the far sync instance. For configuring the wallet use the steps described in my post: " Creating a wallet - secure external password store " Starting the observer at the background using the wallet credentials: DGMGRL> START OBSERVER myobserver IN BACKGROUND FILE IS observer.dat LOGFILE IS observer.log CONNECT IDENTIFIER IS prmy_db Submitted command "START OBSERVER" using connect identifier "prmy_db"  You can follow the progress of the observer looking at the logfile: $ tail observer.log Connected to "prmy_db" Connected as SYSDBA. [W000 11/10 21:14:08.47] FSFO target standby is stndby_db [W000 11/10 21:...

Creating a wallet - secure external password store Oracle wallet enables autologin feature without supplying a password. It is no longer a part of Oracle Advanced Security and available in all licensed editions of all supported releases of the Oracle database. Prepare a secured folder for the wallet $ mkdir -p $ORACLE_HOME/owm/wallets/oracle $ chmod -R 700 $ORACLE_HOME/owm/wallets Create the wallet $ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle/ -create Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again: Edit sqlnet.ora and add the wallet location created in the previous step $ vi $ORACLE_HOME/network/admin/sqlnet.ora SQLNET.WALLET_OVERRIDE = TRUE WALLET_LOCATION=( SOURCE=(METHOD=FILE) (METHOD_DATA=(DIRECTORY=/oracle/product/12.2.0.1/dbhome_1/owm/wallets/oracle/)) ) Add credentials $ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle/ -createC...

The bottom line: Oracle are inventing aggressively on PaaS and starting to neglect the IaaS.  They are doing it by: - Reducing price of the PaaS. - More automation to reduce dramatically the TCO (migration to cloud, patches, upgrades, tuning)  - Higher performance.  - Simpler pricing and contract (a pool of cloud cedits you can use for anything)  - New autonomous database will be commercial soon. - You can bring your own license.  And again Oracle claim that their cloud is  faster than Amazon   for database   with much lower price.