How to troubleshoot Oracle remote database connection

June 7, 2013 by 28 Comments 

Many Oracle DBAs, developers and just end-users often encounter an issue when they can not connect remotely to an Oracle database. There can be different reasons of the connection problems. I give below a short cookbook on resolving those database connection issues. But first let’s explore a bit Oracle connectivity concepts and terminology.

Oracle NET Client Listener tnsnames

Oracle NET Client Server configuration

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. The service name is included in the connect data part of the connect descriptor. To connect to a database service, clients use a connect descriptor that provides the location of the database and the name of the database service. The address portion of the connect descriptor is actually the protocol address of the listener. To connect to a database service, clients first contact a listener process that typically resides on the database server. The listener receives incoming client connection requests and hands these requests to the database server. Once the connection is established, the client and database server communicate directly. Read more »

How to create Sample Schemas in Oracle 11g database – ORA-19613

May 10, 2013 by 3 Comments 

The Oracle Database Sample Schemas provide a common platform for examples in each release of Oracle Database. They are easy to use for testing and training purposes. All Oracle Database documentation and training materials refer to the Sample Schemas environment.

Following are Oracle Sample Schemas

– OE (Order Entry schema) – useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including non-scalar data types.

– HR (Human Resources schema) – useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

– PM (Product Media schema) is dedicated to multimedia data types.

– IX (Information Exchange main schema) includes a set of schemas for demonstrate Oracle Advanced Queuing capabilities

– SH (Sales History schema) is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

– SCOTT – old-fashioned famous schema with its two prominent tables EMP and DEPT that is used by Oracle for many years.

– BI – includes only synonyms on SH schema

With this simplicity I personally found difficulties in creating Sample Schemas in Oracle 11g database. In fact I could not do it in a standard way with Oracle installation binaries before 11.2.0.3. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually. Read more »

Oracle database tablespace report – SQL script

May 1, 2013 by 14 Comments 

In this article I’ll share a SQL script that I probably use most often. The SQL will show Oracle database tablespace information. Many Oracle DBAs and developers use GUI tools nowadays and me also, but sometimes you need to see some extras. The SQL script will list Oracle database tablespaces including tablespace status and type, counts of files and segments per tablespace and the most important – display proper tablespace sizes.

UPDATE [Jun-2016]: the script is entirely rewritten to version 2.0

– New [2.0]: Tablespace (TS) type is extended to display UNIFORM / System Extend Management and ASSM
– New [2.0]: Display actual used space in UNDO and TEMP tablespaces (not HWM as before)
– New [2.0]: Runtime is considerably reduced
– New [2.0]: Compatible with OEM CC 12c/13c output
– Tested on Oracle database 10g, 11g, 12c
– Column: “Max Size” – maximum possible size of a tablespace as a result of Autoextention of database files
– Column: “TS Type” (Tablespace type):
-> LM/DM – Local/Dictionary Managed
-> SYS/UNI – SYStem/UNIform Extent Management (LM only)
-> ASSM/MSSM – Automatic/Manual Segment Space Management (ASSM -> LM only)
Read more »

Oracle RMAN full backup script to disk with compression

April 12, 2013 by 9 Comments 

In this article I’ll share an RMAN script to backup the entire Oracle database including archived redo logs to disk using compression. Before looking into the script see my notes below:

– Read about Oracle Recovery Manager (RMAN) concepts
– Script should work for Oracle 10g and 11g, 12c
Oracle database name = ORCL
– I use 4 RMAN channels to speed up the backup
– I backup intentionally control file manually and automatically
– I limit the size of backup sets by number of files per set and size of backup sets
– I use backup compression in RMAN script (Oracle Advanced Compression license is required)
– The average compression factor can be different but to give you an idea it can be around 7.
– This script is independent from RMAN configuration parameters
Read more »

Oracle RMAN full backup script for EMC NetWorker

April 6, 2013 by 11 Comments 

In this article I’ll share an RMAN script to backup the entire Oracle database including archived redo logs to tape library using EMC NetWorker. Before looking into the script see my notes below:

– Read about Oracle Recovery Manager (RMAN) concepts
– Script should work for Oracle database 10g and 11g
Oracle database name = ORCL
– I use 2 RMAN channels to speed up the backup
– I backup intentionally control file manually and automatically
– I limit the size of backup sets by number of files per set and size of a backup set
– PARMS section includes instructions for EMC NetWorker server
– After the backup I allocate a maintenance channel crosschecking the backups
– I do not use backup compression in RMAN script, since it’s done on NetWorker server
– This script is independent from RMAN configuration parameters
– The script can be scheduled by EMC NetWorker server daily
Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email