Showing posts from 2017

Bypassing the listener and connecting to ASM without a password from Python code

Requirement I was asked to have smart code that can connect to a local ASM instance without the need for any prior preparations like setting up the listener or any other changes in the database. And even do this without a password!
Problems Listener If there is already a listener that uses the default port (i.e. 1521), the ASM can register itself dynamically to the listener with no need to configure anything. But on systems that use ports other than 1521, the listener is not registered automatically. We could set the parameter LOCAL_LISTENER like this:
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST='; But, I was asked to do no prior configuration.
Password Using SQL*Plus we have the option to connect to the database locally Using Password File Authentication with no need to setup a listener and without using a password in the connection string.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba

SQL*Plus: Release Productio…

Change Listener Ports in RAC Environment

Look at the current port configured for the scan_listener $ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Solaris: Version - Production on 01-JAN-20** 21:26:16

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

Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Solaris: Version - Production
Start Date                01-JAN-20** 16:08:18
Uptime                    0 days 5 hr. 17 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/
Listener Log File         /oracle/product/…

Log mining a Redo log file / Archive log file on a remote database

At the source site   Enable the database to write to a directory you choose, in this example I use /tmp alter system set utl_file_dir='/tmp' scope=spfile;  Restart of the database is required in order for the parameter utl_file_dir be active
Extract the LogMiner dictionary to a flat file called dictionary.ora
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/tmp', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);  Copy redo log file from source to target:Copy redo log file from ASM to the operating system asmcmd cp '+DATA/PDB/ONLINELOG/group_1.259.919359545' /tmp Copy the the redo log file to the target host  scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp Copy the dictionary file from source to target  scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp  On target db  alter system set utl_file_dir='/tmp' scope=spfile;  Restart of the database is required in order for the parameter utl_file_dir be active
Add the first redo lo…

Resolve tns alias from the database (DBMS_TNS.RESOLVE_TNSNAME)

When a given tns alias is not working, via SQL*Plus, database link or within data guard, traditionally I troubleshoot connectivity via tnsping utility, at first just to understand the target host, port and service name.

In oracle 12.2 we have a new function called DBMS_TNS.RESOLVE_TNSNAME that enables to query the return string as we receive it from tnsping.
Using oracle utility tnsping from the command line $ tnsping stndby_db

TNS Ping Utility for Linux: Version - Production on 13-NOV-2017 14:45:39

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stndby_host)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stndby_db)))
OK (0 msec) Using dbms_tns SQL> select dbms_tns.resolve_tnsname ('stndby_db') as TNS_ALIAS from dual;

Starting the Oracle Data Guard Broker OBSERVER in the BACKGROUND

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:
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:14:11.45] Observer …

Setting up an Observer

Setting up an Observer
Observer is an automatic data guard broker, it can be installed with either the Oracle Client Administrator software or the full Oracle Database software.
It is preferable that the observer would run on a different host than the primary and standby databases, using the same network as any end-user client or application.
The host should be located In a third, independent location or at least isolated as much as possible from the standby database.
Make the observer highly available by configuring auto restart on the same server or on an alternate host.
These are the steps for setting up the observer:

Enable flashback on Primary & Standby:
sqlplus> alter database flashback on ; Standby:
alter database flashback on ;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Update db_lost_write_protect on Primary & Standby:
SQL> alter system set db_lost_write_protect='TYPICAL' s…

Creating a wallet - secure external password store

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
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights

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
) Add credentials $ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle/ -createCredential prmy_db
sys P@$$w0rd
Oracle Se…

List RMAN backups size

SQL script for calculating the total size of RMAN backups

col TYPE for a20 Heading "Backup Type"
col completion_time Heading "Completion Time"
col MB for 99999999999  Heading "Size (MB)"
col BCPTIME for 99999999999 Heading "Backup Time (minutes)"

SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time
       , TYPE
       , ROUND (SUM (bytes) / 1048576)          MB
       , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME
                    WHEN s.backup_type = 'L' THEN 'Archive Log'
--                    WHEN s.controlfile_included = 'YES' THEN 'Control File'
                 WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'
                    WHEN s.backup_type = 'I' THEN 'Incemental (Level ' || s.incremental_level || ')'
                    ELSE s.backup_type

Data Guard / Far Sync Best Practice - control protection levels

I have just came across a meta parameter called DB_ULTRA_SAFE that sets the values for all of mentioned parameters in Far Sync Best Practice Recommendations (for Oracle 12.1).



  •When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.

Oracle open world 2017 short summary

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 and planned to be available: Autonomous Database for Data Warehouses - by December 2017Autonomous 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, OSPlatform logs: Database, Java, Analytics, etc.Application logs: ERP, CX, HCM, Custom, etc. The logs will be gathered to detect and connect anomalous events. SLA Guarantees 99.995% reliability and availability (less than 30 minute…

Oracle 18c - New Features for Active Data Guard


At Oracle Open World 2017 I collected the main improvements and changes
 are going to be implemented in Oracle Database 18c:

-Refresh a standby from primary/backup using single RECOVER command
Data Guard
-In Memory Column store works with multi-instance redo apply
-Multi-instance redo apply support with Block Change Tracking (BCT)
-Global Temporary Table support from Standby.
-DML operations on Standby – Redirection to Primary without ACID compromise.
-Preserve buffer cache during role-change.
-No logging enhancement - Performance mode and Availability mode.

Yossi Nixon

Oracle Cloud Announcement‏ (September 2017)

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 Amazonfor databasewith much lower price.

Creating Far Sync instance step by step

These are the steps of creating Far Sync instance in the command line:
Create 12.1 primary db
Create 12.1 physical standby
Create regular standby DG broker configuration
modify network file (on primary and standby) and add entries for the far_sync instance, add to tnsnames.ora on Primary hosts and standby hosts:
FarSyncInst =
      (ADDRESS = (PROTOCOL = TCP)(HOST = FarSyncHost)(PORT = 1521))
      (SERVICE_NAME = FarSyncInst)
  )    Note: (UR=A) is needed to connect to a BLOCKED unmounted database instance.
Copy tnsnames.ora to new farsync server – ensure all hosts mentioned in tnsnames are written also in /etc/hosts
Add to listener.ora in far sync server
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))

Oracle connection manager

Connection manager is working as oracle SQL*NET proxy and firewall.
In this example, I am using Connection Manager of Oracle Database 12c Release 2 running on Linux x86_64

Footprint Storage: 1.7 G
Memory: 5~28.4 MB

Requires: Installation of Oracle Client in a separated folder than the database software
Register the connection manager in the grid infrastructure as a cluster resource

Configure and install on the intermediate host Download Oracle Database 12c Release 2 Client
unzip Change response file /client/response/client_install.rsp:
oracle.install.client.installType=Custom Start silent installation:
cd /client
./runInstaller -silent -responseFile /client/response/client_install.rsp -ignoreSysPrereqs -ignorePrereq -noconfig -waitforcompletion -force -showProgress run
sudo /oracle/product/
copy a…

Far Sync Best Practice Recommendations (for Oracle 12.1)

This post is a continuation of my post, Using Standby as an Alternate for Far Sync (12c): Limitations and Considerations. It is for database administrators who want to set up Far Sync for the first time.

Creating a Far Sync instance requires a number of manual steps and there are many parameters to consider or set. I recently ran numerous scenarios using Oracle™ Active Data Guard with a Far Sync instance.

Our goals were to:
• Check Far Sync functionality and reliability
• Validate that Far Sync has a low footprint under heavy load
After running a large number of benchmark scenarios, where we changed one parameter at a time, here are my recommendations for using Active Data Guard with Far Sync:
The primary database, the standby database, and the Far Sync instance should all be using the same software version and patch level; if not, upgrade the database(s) to the latest version with the latest patch sets.Consider changing the following database initialization parameters to FULL: DB…

Data Guard - Changing IP Addresses

When changing IP of a host we should update/recheck the following places: /etc/hosts or DNSlistener.ora tnsnames.oraDatabase parameters (local_listener, remote_listener)Data Guard configuration This document is also relevant when changing the IP address of the connection between the hosts, other than the original IP addresses we used during the installation. When installation is done using the hostname and not the IP address, most of the changes are not relevant except for /etc/hosts.
In this document, I will describe how to change the Data Guard Broker configuration. Dataguard configuration
Show the environment DGMGRL> show configuration

Configuration - dr

  Protection Mode: MaxAvailability
  pdb7 - Primary database
    fdb7 - Far sync instance
      sdb7 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 351 seconds ago) Show detailed information of the Primary
DGMGRL> show database pdb7 StaticConnect…

Extracting AWR data and loading it in another system

As a DBA, I find myself trying to support customers remotely, who frequently ask for another Automatic Workload Repository (AWR) or a special query. I’ve found a cool feature that enables customer's DBAs to extract the AWR and then load it locally.
Extracting 1. Login as SYS and at the SQL prompt, enter: SQL> @?/rdbms/admin/awrextr.sql 2. Enter your database id or press <Enter> to use the current default. Enter value for dbid:
Using 3342354369 for Database ID 3. Specify the number of days for which you want to list snapshot Ids. Enter value for num_days: 14 4. After the list displays, enter the beginning and ending snapshot ID. Enter value for begin_snap: 2670
Enter value for end_snap: 2672 5. A list of directory objects is displayed. Specify the directory object pointing to the directory where the export dump file will be stored. Enter value for directory_name: DATA_PUMP_DIR 6. Specify the prefix for the default dump file name Using the dump file prefix: awrdat_2670_267…