Oracle DBA Place

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=192.168.3.40)(PORT=1521))'; 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 12.2.0.1.0

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 11.2.0.4.0 - Production on 01-JAN-20** 21:26:16 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) STATUS of the LISTENER ------------------------ Alias                     LISTENER_SCAN1 Version                   TNSLSNR for Solaris: Version 11.2.0.4.0 - 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/11.2.0.4/grid/network/admin/listener.ora Listener Log File   

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

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 12.2.0.1.0 - Production on 13-NOV-2017 14:45:39 Copyright (c) 1997, 2016, Oracle.  All rights reserved. Used parameter files: /oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora 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;

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:

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: Primary: sqlplus> alter database flashback on ; Standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 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=

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

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     FROM (SELECT CASE                     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              

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 DB_ULTRA_SAFE=DATA_ONLY   • DB_BLOCK_CHECKING => MEDIUM.   • DB_LOST_WRITE_PROTECT => TYPICAL.   • DB_BLOCK_CHECKSUM => FULL. When DB_ULTRA_SAFE=DATA_AND_INDEX   • DB_BLOCK_CHECKING => FULL.   • DB_LOST_WRITE_PROTECT => TYPICAL.   • DB_BLOCK_CHECKSUM => FULL. When DB_ULTRA_SAFE=OFF   • When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.

Hi, At Oracle Open World 2017 I collected the main improvements and changes  are going to be implemented in Oracle Database 18c: RMAN - 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

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.

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 =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = FarSyncHost)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = FarSyncInst)       (UR=A)     )   )     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 LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0 )(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))     )   ) SID_LIST_LISTENER=

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 linuxx64_12201_client.zip Change response file /client/response/client_install.rsp: UNIX_GROUP_NAME= INVENTORY_LOCATION= ORACLE_HOME= ORACLE_BASE= oracle.install.client.installType=Custom oracle.install.client.customComponents=oracle.network.cman:12.2.0.1.0 Start silent installation: cd /client ./runInstaller -silent -responseFile /client/response/client_install.rsp -ignoreSysPrereqs -ignorePrereq -noconfig -waitforcompletion -force -showProgress run root.sh sudo /oracle/prod

Hi, 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

When changing IP of a host we should update/recheck the following places: /etc/hosts or DNS listener.ora tnsnames.ora Database 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   Members:   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

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