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.