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;