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
oracle
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
How to Monitor SGA Memory on Oracle Pluggable Databases
This post describes how to monitor SGA memory usage on pluggable databases using queries against database views. Pre-requisites to using the sample code provided below are: On RAC configuration, the scripts should be run on each instance since only V$ views are used to provide an accurate memory usage for the individual database instance. The scripts must be run in SQL*Plus session since specific SQL*Plus functions are used. Scripts must be run as sysdba or database user account having … [Read more...] about How to Monitor SGA Memory on Oracle Pluggable Databases
Simple Steps to use LogMiner for finding high redo log generation
LogMiner is a tool that lets you use SQL statements to analyze events in the database log. With LogMiner, you can track transactions as they are processed or locate specific functions that result in data modifications. LogMiner was introduced with Oracle8i. LogMiner can be used, along with audit trails, to determine what has happened in your Oracle database. In this post, we will see simple steps for mining the redo logs, for instance, to troubleshoot excessive redo generation. 1. Enable … [Read more...] about Simple Steps to use LogMiner for finding high redo log generation
How To Automate The Opening Of Pluggable Databases After The CDB Starts Up in Oracle 12c
Here is a short note on how to automate the startup of PDBs after the CDB starts up in Oracle database 12c. You can start all the PDBs or selective PDBs using below steps. We will need to create a database startup trigger in order to automate the PDB startup. Follow the steps outlined below. 1. Login into the CDB as sysdba. $ sqlplus / as sysdba 2. Create a database startup trigger to open all pluggable databases. create or replace trigger open_all_pdbs after startup on … [Read more...] about How To Automate The Opening Of Pluggable Databases After The CDB Starts Up in Oracle 12c