This post explains how Oracle Net searches for the configuration files listener.ora, sqlnet.ora, tnsnames.ora and cman.ora. Oracle Net Services configuration files are not always located in the $ORACLE_HOME/network/admin directory. Depending on the file, Oracle Net uses a different search order to locate the file. Search order for the sqlnet.ora 1. The directory specified by the TNS_ADMIN environment variable, if set. 2. The $ORACLE_HOME/network/admin directory. 3. In RAC, the sqlnet.ora … [Read more...] about What is the Search Order for TNS files – listener.ora, sqlnet.ora, tnsnames.ora
oracle
How to Trace Dynamic Registration from PMON or LREG (Oracle 12c)
The post describes how to capture tracing for the PMON or LREG process. It is intended to help troubleshoot issues where dynamic service registration is failing or where the LSNRCTL services summary is not displaying services properly. 11g tracing option From 11.1.0.7 onwards, tracing can be enabled using the following command: alter system set events='immediate trace name listener_registration level 3'; When finished collecting pmon traces,turn this off using: alter system set … [Read more...] about How to Trace Dynamic Registration from PMON or LREG (Oracle 12c)
Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value
Description This script creates a function to convert Hexadecimal value to decimal. Example SQL> select hextodec('BABA11') from dual; HEXTODEC('BABA11') ------------------ 12237329 Starting from Oracle 8i, there is a new format model('X') for the to_number function which can be used to accomplish the same task. For the above example, you could issue the following: SQL> select to_number('BABA11','XXXXXX') from dual; … [Read more...] about Oracle SQL : Script To Convert Hexadecimal Input Into a Decimal Value
Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
This script will list the below items from each instance in the RAC Database. Report the redo log switches on an hourly basis from the all the instances on RAC Database Report the size of the archive logs generated on daily basis from all the instances on RAC Database The same can be used for a Standalone (Non-RAC) Database as well. The script Here is the actual script: /* setting up the environment */ set linesize 200 pagesize 1000 column day format a3 column total format … [Read more...] about Script To Find Redolog Switch History And Find Archivelog Size For Each Instances In Oracle RAC
How to upgrade Oracle database to 12c Pluggable database (Multitenant) using Transportable tablespace(TTS) method
Database upgrade to 12c can be done in multiple ways. Commonly used methods are DBUA (Database upgrade assistant) and Manual upgrade. Both methods will directly upgrade the database to a higher version but upgrade to 12c non-CDB. To convert the database to Multitenant architecture we require additional actions which add extra downtime. Transportable tablespace (TTS) is one of the methods commonly used to upgrade the database to higher version due to its advantage. In 12c TTS upgrade, it doesn’t … [Read more...] about How to upgrade Oracle database to 12c Pluggable database (Multitenant) using Transportable tablespace(TTS) method
Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
When cloning (duplicating) a database to a higher version, traditionally (prior to 12c), you must use restore/recover of an RMAN backup. This is because the database in the destination must be opened with UPGRADE option. Therefore, the RMAN duplicate could not be used because RMAN automatically opens the auxiliary database with resetlogs. This would fail with error 'ORA-39700: database must be opened with UPGRADE option'. In RMAN 12c, a new option is available "NOOPEN" requesting RMAN to … [Read more...] about Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
Beginners Guide to RMAN compression for backups
As the databases keep growing, so does the space occupied by their backups. Fortunately, RMAN has built-in support to compress such large-sized backups which can be handy rather than using an OS utility such as TAR or so on. The following are the 3 kinds of compression techniques used by RMAN: Null compression Unused block compression Binary compression RMAN uses block compression by default and that's why you can't set it off (or on either). Two modes of block compression are … [Read more...] about Beginners Guide to RMAN compression for backups
Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
Introduction This post points out the issues surrounding Ethernet Jumbo Frame usage for the Oracle Real Application Cluster (RAC) Interconnect. In Oracle Real Application Clusters, the Cluster Interconnect is designed to run on a dedicated, or stand-alone network. The Interconnect is designed to carry the communication between the nodes in the Cluster needed to check for the Clusters condition and to synchronize the various memory caches used by the database. Ethernet is a widely used … [Read more...] about Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
Oracle Database 18c New Feature – Memoptimized Rowstore
This post gives a summary of 18c new Memoptimized Rowstore feature. Feature Summary The memoptimized rowstore provides the capability of fast lookup of data for the tables that are mainly queried based on primary key columns. The memoptimized rowstore uses a memory area in the system global area (SGA) called the memoptimize pool that stores the hash indexes of the tables when enabled for fast lookup. Fast lookup is enabled by a hash index structure in the memoptimize pool that provides … [Read more...] about Oracle Database 18c New Feature – Memoptimized Rowstore
Oracle Database 18c : How to Merge Partitions And Subpartitions Online
This post is a short note on merging partitions and subpartitions online. The below sample demonstration shows you how to merge partitions online while there are concurrent transactions going on. For the purpose of this post, we will create a sample table and load some sample data into it to perform the merge. 1. Create A table Let's create a sample table "onlinemergepartition" as shown below. --- Creating a table CREATE TABLE onlinemergepartition ( pid number, salesdate date, price … [Read more...] about Oracle Database 18c : How to Merge Partitions And Subpartitions Online