Oracle Database 12c: Are you ready?

What is 12c Database?

Oracle Database 12c is the next release of Oracle’s flagship database product, expected to be launched in calendar Q1, 2013. Because it is  not yet officially launched to general availability , information about it, is limited. following are the information I could manage to gather from various sources, 100% authenticity is not guaranteed until the product is released officially.

ORA 1775 looping chain of synonyms

There can be multiple reasons for this error.

1- Through a series of CREATE synonym statements, a synonym was defined that referred
to itself.

Recompiling Invalid Objects

Operations such as upgrades, patches and DDL changes can invalidate schema objects. 
These objects are re-validated by on-demand automatic recompilation if they don't have compilation errors. But it may take a significant time, so it is always better to make them compiled before they are called.

Import fails with IMP0098: Checking integrity of dump file

After the export is finished one should always check if the export dump file is corrupted on thesource machine using imp with "show=y". If getting the errors then the file is corrupted on the source machine.

RMAN: Incomplete Recovery from TAPE to Single Instance

Environment: Windows 2008R2 64bit, backup located at Netbackup (Tape media), DB is RAC one with two nodes on ASM.

Installing TimesTen 11gR2 - Windows

On UNIX, you can install multiple instances of TimesTen. On Windows, you can
install only one instance of any major TimesTen release where a major release is indicated by the first three parts of the release number, such as 11.2.2. For example, you can install both 11.2.1.9.0 and 11.2.2.3.0 on the same Windows computer, but you cannot install both 11.2.2.0.0 and 11.2.2.3.0.

Oracle TimesTen - Intro

TimesTen - What is it? 
 TimesTen is a memory-optimized, relational database management system with persistence and recover ability. Unlike traditional disk-optimized relational databases, all data within a TimesTen database is located in physical memory (RAM), which means no disk I/O is required for any data operation.

Subqueries: Nested & Corelated

Nested Subquery: A subquery is a query with in a query. It is nested in the where or having clause of another query. Subquery is executed first and then its results are provided to the main clause of the main query. 

Installing 11gR2 RAC on Linux

Environment: OS Oracle Linux 5.4 32bit, Oracle 11gR2 (11.2.0.1) 32bit
See the overview

All the installation process already explained in the overview as mentioned above. Here all JPGs are being provided for the Linux which are self explanatory. Wherever any additional information would be required , it will be provided.

11gR2 on Linux: Create Database

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: RDBMS Software Install

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: Oracle Grid Infrastructure Install

Main Post: Installing 11gR2 RAC on Linux

Start the RAC1 and RAC2 virtual machines, login to RAC1 as the oracle user and start the Oracle installer.

11gR2 on Linux: Preparing second VM

Main Post: Installing 11gR2 RAC on Linux 

After fulfilling all the pre-reqs now we will shutdown the node1 (RAC1) and make its clone for Node2 (RAC2)

11gR2 on Linux: Prepare the shared storage/Prereq

Main Post: Installing 11gR2 RAC on Linux 

After installing the basic installation of linux there are certain requirments which need to be fulfilled for successful installation of RAC 11g.

11gR2 on Linux: Installing/configuring vmware tools

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: Installing OS (Linux)

Main Post: Installing 11gR2 RAC on Linux

11gR2 on Linux: Prepare the Virtual Machines

Main Post: Installing 11gR2 RAC on Linux

Two virtual machines will be prepared, first VM "RAC1" will be prepared , OS will be installed on it and then its copy ("RAC2")will be made. After copy the first VM , necessary modification for parameters will be made.

ORA-29270 Too Many Open HTTP Requests

Today one of the developer got the below error, i thought to write the details for him.

Incomplete Recovery by RMAN from TAPE (ASM) to Single Instance.

-- Incomplete Recovery by RMAN from TAPE (ASM) to Single Instance.
Env. Windows 2008R2 64bit, backup is located at Netbackup (Tape media)
1-  first set ORACLE_SID and the DBID of the source DB
C:\> set ORACLE_SID=HOMEDB
RMAN> set dbid=1547250382

executing command: SET DBID

Adding service to single instance database

Database services (services) are logical abstractions for managing workloads in Oracle Database. Each service represents a workload with common attributes, service-level thresholds, and priorities.

In Real Application Clusters (RAC), a service can span one or more instances and facilitate real workload balancing based on real transaction performance.   RAC also enables you to manage a number of service features with Enterprise Manager, the DBCA, and the Server Control utility (SRVCTL).

ORA-00304: requested INSTANCE_NUMBER is busy

Environment: 2 nodes Oracle RAC 11gR2, Linux 5
Scenario: Node1 has a single instance database named testdb, same testdb was restored to the Node2 (as single also) , while starting the instance on node2 for testdb (single instance) gave the error below:
ORA-00304: requested INSTANCE_NUMBER is busy

Setting up an NFS share

NFS (Network File System) is a protocol used by UNIX/Linux computers to share disks across a network. Similar to the Common Internet File Services (CIFS) protocol used by Windows, NFS is older and more light-weight, and performs much more efficiently on UNIX and Linux systems.

Adding vip resource (11gR2 Linux)

Enviroment: Oracle Linux 5 update 5 64bit, Oracle RAC 11gR2

While installing  RDBMS software on Oracle 11gR2 cluster following error was encountered.

ORA-12631: Username retrieval failed

Case:
While attempting to connect to the database the following error occurs.

c:\temp\dig>sqlplus /@qanew as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 2 09:09:07 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12631: Username retrieval failed
 

Configuring Transparent Application Failover

Transparent Application Failover (TAF) is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance. Notifications are used by the server to trigger TAF callbacks on the client-side.

RAC on Windows: Oracle Clusterware Node Evictions a.k.a. Why do we get a Blue Screen (BSOD) Caused By Orafencedrv.sys? [ID 337784.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Microsoft Windows Itanium (64-bit)
Microsoft Windows x64 (64-bit)
Microsoft Windows 2000Microsoft Windows XPMicrosoft Windows Server 2003 (64-bit Itanium)Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)Microsoft Windows Server 2003 R2 (32-bit)
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7

Symptoms

While running RAC a blue screen is shown and a reboot takes place. Windows creates a coredump that shows that orafencedrv.sys is involved.

Configuring Ignite for Oracle Connections

For Ignite to connect to Oracle databases that are specified by a name (tnsnames or LDAP), the following Oracle Network configuration (.ora)  files are required
    tnsnames.ora (required for name TNSName resolution)
    ldap.ora (required for LDAP name resolution)
    sqlnet.ora (optional)

Oracle Data Guard Configuration (DGMGRL) (11gR2 Windows 2008R2)

Brief:

Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. DG broker does not have the ability to create standby and is used for managing the dataguard configuration.

Task:  Create  physical standby database for an existing primary database. Both primary and standby would be on same physical machine.

PRM = primary db 192.168.26.11

STBL = local Standby 192.168.26.11

How to Restrict User from Connecting to Database Through Specific IP Address

Some of the DBA asked me to restrict the connection to DB from specific IPs. Its simple and you can use the logon trigger for this purpose.

Enabling/Disable Archive Log mode in RAC (11gR2)

Whether a single instance or clustered database, Oracle tracks (logs) all changes to database blocks in online redolog files. In an Oracle RAC environment, each instance will have its own set of online redolog files known as a thread. Each Oracle instance will use its set (group) of online redologs in a circular manner. Once an online redolog fills, Oracle moves to the next one.

cluvfy Healthcheck

Normally we run cluvfy in preparation of the installation of Grid Infrastructure or a set of RAC binaries to ensure everything is ready for the next step in the RAC install process. Beginning with 11.2.0.3, there is another option  "healthcheck" 

Part of the “comp” checks, it takes the following options:

Datapump import gets ORA-600 [kpudpxcs_ctxConvertStream_ref_1] while importing Spatial data

Working for one of our client , just got the below error in impdp log for some tables, one example below.

ORA-31693: Table data object "GDB"."STREETS" failed to load/unload and is being
skipped due to error:
ORA-02354: error in exporting/importing data
ORA-00600: internal error code, arguments: [kpudpxcs_ctxConvertStream_ref_1],
 [SYS_TYPEID("SHAPE")], [], [], [], [], [], [], [], [], [], []

Send Message to Users by OS Command using PL/SQL

From one of my client ,it was required to send the message to application users (on Windows) for specific events by the database server. I used  the simple msg (Win command) for this purpose , example given below :

How To Connect database in sqlplus without tnsnames.ora

Many times we have requirement of connecting database to execute a sql script but we sometimes don't find the tnsnames.ora file and sometimes we don't have permission to edit (one of my client faced issue on Windows 2008 Server).

OPatch failure issue while patching RAC (11.2.0.3)

While patching the RAC/DB you may face different issues on Windows because of the .dll files being used. Here is the process how I got rid of such failure.
While performing the acfsroot install, it failed as below.

Applying Patch 6 on RAC 11gR2 (11.2.0.3)

Environment: 11gRAC/DB (11.2.0.3) , Windows Server 2008 R2 x64 Edition
Patch:13965211 - 11.2.0.3 Patch 6 for Windows 64 bit, download patch from metalink.
This patch is RAC rolling upgradable for GRID, and RDBMS homes.
You should administer one Oracle product, one Oracle home at a time, for its particular Bundle Patch Update.

CRS-4535: Cannot communicate with Cluster Ready Services

We have SCOM configured for Oracle Servers to know if some service crashes, today I got the following alert for one of our RAC node.
The OracleASMService+ASM1 service terminated unexpectedly.  
I started investigations and tried to get the status by crsctl as below 

SP2-1503: Unable to initialize Oracle call interface

Oracle Error :: SP2-1503

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Cause

Indicates a library used by SQL*Plus to communicate with the database failed to initialize correctly.

ORA-01002: fetch out of sequence

"ORA-01002: fetch out of sequence" usually means that a SQL fetch has been issued after a prior commit has closed a server side cursor.

The current default behaviour of the Oracle JDBC driver is to autocommit SQL Statements. So it is possible that a commit has been issued which you did not expect. Eg if you are performing update statements inside the fetch loop, it is possible that an automatic commit has been issued after the update which has closed the fetch loop's cursor.

Enable block change tracking in oracle 11g

The block change tracking (BCT) feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a block change tracking file. This file is a small binary file called block change tracking (BCT) file stored in the database area. RMAN tracks changed blocks as redo is generated.

RMAN backup fails with Ora-00245 And Rman-08132

Symptoms

RMAN backups report errors like :

ORA-00245: control file backup operation failed

RMAN-08132: WARNING: cannot update recovery area reclaimable file list

Cause

Incorrect specification for the Snapshot Controlfile

ORA-19815: WARNING: DB_RECOVERY_FILE_DEST_SIZE 100.00% USED

Symptoms

ALERT LOG
-----------
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
ARC0: Error 19809 Creating archive log file to '+DBFLASH'

How To Create a Connection With SQL Server In SQL*DEVELOPER

Ensure that the source database is accessible by the Microsoft SQL Server user and the system from where you are connecting that is used by SQL Developer for the source connection.You can test by creating the ODBC datasource to the SQLServer DB.

Access Control List for External Network Services (11g)

Starting with Oracle 11gR1 (11.1.0.6) so called "fine-grained access" was implemented to limit usage of packages like UTL_SMTP, UTL_HTTP connecting over the network to other services like mail server etc.

Log Files in RAC Environment

The Cluster Ready Services Daemon (crsd) Log Files

Log files for the CRSD process (crsd) can be found in the following directories:

ORA_CRS_HOME/log/hostname/crsd

the crsd.log file is archived every 10MB as crsd.101, crsd.102 ...
ORACLE_CRS_HOME/log//alert.log

Oracle Event Tracing

Oracle provides various diagnostic tools for debugging the RDBMS. Certain events can be turned on to dump diagnstic information to trace files. Next, some INIT.ORA parameters are available that can be used while diagnosing memory and disk corruptions. There parameters are not set during normal operation of the database because they affect the performance. But in some circumstances it becomes necessary to turn them on to diagnose the issues with RDBMS, you can think it as extended SQL trace.

Health Check - RAC/DB

Running the following OS commands you can get the Health Check for your cluster and Database, all the output is redirected to one file to analyze.You can run it in a .bat file or separately on the command prompt.

RMAN Duplicate Database from RAC ASM to RAC ASM (11gR2)

Duplicating RAC database is very simple, first duplicate RAC Database to a single instance using RMAN and convert the single instance into a RAC cluster.
Testing Environment:
- Two nodes RAC 11gR2 (11.2.0.3) on Windows 2008R2
- Source RAC Database: TESTRAC (testrac1,testrac2)
- Target RAC Database: DUPDB (dupdb1,dupdb2) on the same hosts where TESTRAC is running
- ASM instances with three diskgroups (DGDUP for dupdb)
- Backup took already (on Netbackup) for Source Database, We CONFIGURE DEFAULT DEVICE TYPE TO SBT_TAPE; for our environment

The clock on host is not synchronous with the mean cluster time (RAC 11g)

Symptoms
Cluster alert log on one of the nodes shows the following message

[ctssd(804)]CRS-2409:The clock on host or-21 is not synchronous with the mean cluster time. No action has been taken as the Cluster Time Synchronization Service is running in observer mode.

OR

[ctssd(9534)]CRS-2409:The clock on host   is not synchronous with the mean cluster time. No action has been taken as the Cluster Time Synchronization Service is running in observer mode.

DG4MSQL - Where clause does not return data

Problem:
One of the customer was facing issue while querying to SQL Server Database using dblink through DG4MSQL.  They have a view on SQL Server side, when they were querying this view with where clause having string in arabic,it did not return data. Although data was available and it could be seen when using query without where clause.
Customer is using 11g (11.2.0.3) RAC - Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production runs on Microsoft Windows 2008, Gateway is running on same  Database machine.

MSQL to MSQL Replication using GG

Prerequisite
Before attempting this post please review the other related GoldenGate posts on the page Other Cateogories for understanding GoldenGate concepts.
Goal
Testing the Oracle Golden Gate for SQLServer to SQLServer real time replication.

Gather Statistics

On SYS Objects
If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics.  The collection of statistics on SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects.

Simple Network Tests

Some times there is slowness in network and end users face it but complaining about the database or application. We can use some initial testing for such network slowness.

Using the SQL TUNING ADVISOR (STA)

SQL Tuning Advisor analyzes candidate SQL statements, and execute a complete 
analysis of the statement including: 
 - Determining stale or missing statistics
 - Determining better execution plan
 - Detecting better access paths and objects required to satisfy them (indexes, materialized views)
 - Restructuring SQL

How to use the Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor (ADDM) is an integral part of the Oracle RDBMS capable of gathering performance statistics and advising on changes to solve any existing performance issues measured. For this it uses the Automatic Workload Repository (AWR), a repository defined in the database to store database wide usage statistics at fixed size intervals (60 minutes).

ORA-20000 on executing DBMS_STATS.GATHER_TABLE_STATS on table

Problem:
DBMS_STATS.GATHER_TABLE_STATS('HOME','T_temp')

ORA-20000: Unable to analyze TABLE "HOME"."T_TEMP", insufficient
privileges or does not exist

Exadata Database Machine - Intro

Oracle Exadata is a database appliance with support for both OLTP and OLAP workloads. It can be said as “cloud in a box” composed of database servers, Oracle Exadata Storage Servers, an InfiniBand fabric for storage networking and all the other components required to host an Oracle Database.

ORACLE EXALYTICS - Intro

Background
We live in the age of information and information is power. In the business enterprise, it is imperative that everyone has the information they need in order to accurately and effectively fulfill their business obligations.

Oracle DBConsole service failed to start on Windows

If you have a database control of any database version on WINDOWS Operating System, some times OracleDBConsole Service failed to start with the following error, cause is not determined .

CRSCTL Utility/Other Cluster Commands Reference (11g)

CRSCTL is an interface between you and Oracle Clusterware, parsing and calling Oracle Clusterware APIs for Oracle Clusterware objects.  You  can perform check, start, and stop operations on the cluster with CRSCTL. This utility is located in the Grid_home/bin directory.

Oracle Database Firewall

Oracle Database Firewall is the first line of defense for databases, providing real-time monitoring of database activity on the network. Highly accurate SQL grammar-based technology blocks unauthorized transactions, helping prevent internal and external attacks from reaching the database. Oracle Database Firewall is easy to deploy, requiring no changes to existing applications or databases.

Case Sensitive Passwords in 11g

Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.

Disable Oracle's password expiry (11g)

 Unlike older releases, Oracle 11g sets password expiry by default. That's really annoying

• for SYS and SYSTEM: nobody wants to regularly change (and forget) them.

Slow TNSPING time

TNSping's only function is to send a Connect Packet (NSPTCN) to the listener; the listener replies with a Refuse Packet (NSPTRF) and a round trip time is computed. TNSping should never be used to test network performance.

ORA-01017 May Cause ORA-3136 WARNING : Inbound Connection Timed Out in Alert Log

Goal
How to reproduce the error ORA-3136 WARNING : inbound connection timed out ?
This is the most common reason when you notice the warning message in the alert log.

DG4MSQL Installation/Configuration - 11g RAC

Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts and increasing the mobility of the application.

Installing 11g RAC on Windows 2008

Overview of new concepts in 11gR2 Grid Infrastructure
The single client access name (SCAN) is the address used by all clients connecting to the cluster. The SCAN
name is a domain name registered to three IP addresses, either in the domain name service (DNS) or the Grid
Naming Service (GNS). The SCAN name eliminates the need to change clients when nodes are added to or

Create the database - RAC 11g

Run ASMCA to create diskgroups
Prior to creating a database on the cluster, the ASM diskgroups that will house the database must be created.
In an earlier chapter, the ASM disks for the database diskgroups were stamped for ASM usage. We will now

RDBMS Software Install -RAC 11g

Prior to installing the Database Software (RDBMS) it is highly recommended to run the cluster verification
utility (CLUVFY) to verify that Grid Infrastructure has been properly installed and the cluster nodes have

Oracle Grid Infrastructure Install

Basic Grid Infrastructure Install (without GNS and IPMI)

• Shutdown all Oracle Processes running on all nodes (not necessary if performing the install on new servers)

Prepare the shared storage for Oracle RAC

This post describes how to prepare the shared storage for Oracle RAC
1. Shared Disk Layout
2. Enable Automounting of disks on Windows
3. Clean the Shared Disks
4. Create Logical partitions inside Extended partitions
5. Drive Letters

Prepare the cluster nodes for Oracle RAC

Before installation of RAC 11g R2 , you have to prepare the cluster nodes. Following must be performed.
User Accounts
The installation should be performed as the Local Administrator, the Local Administrator username and

SINGLE CLIENT ACCESS NAME (SCAN)

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access Oracle Databases running in a cluster. The benefit is that the client’s connect information does not need to change if you add or remove nodes in the cluster.

Direct printing to a default client printer in Oracle developer 10g

Purpose:  Steps how to print on a default client printer directly without previewing or displaying the report. (Oracle 10g)

Files Attached: The following files are required and can be downloaded from below.
orarrp.zip      orarrp folder that includes the printing tool.

Oracle Hints

Note: The demos on this page are intended to show valid syntax but it is far easier to use a hint to make a mess of things than it is to improve things. So many, if not most, of the demos will increase the cost.

Join methods:

How to grant on v$ views

If you face with an error when try to give permission on a v$view
SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views