As you probably know, database must be started before any other RMAN commands are issued. So… how to solve this issue if you have 100% that your database is open and working? Take a look at my case and my environment variables:

ORACLE_SID=dw
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/
ORACLE_BASE=/u01/app/oracle/
[oracle@db env]$ rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Oct 8 15:08:53 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database (not started)
RMAN>

But I am 100% sure that my database is online

So what’s happening here? Take a look at my ORACLE_HOME… Do you find anything strange on it’s path?

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/

You’re right… I have an extra slash “/” at the end of the path… RMAN strangely thinks my database is not online if the ORACLE_HOME ends with “/” … It works with lsnrctl, emctl and other oracle utils but with RMAN and SQLPLUS won’t work. So the error was a typo… In the bash a command like “cd /u01//app” works, but in this case RMAN and SQLPLUS got confused.

[oracle@db env]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1/
[oracle@db env]$ rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Oct 8 15:19:10 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)
RMAN> exit
Recovery Manager complete.

[oracle@db env]$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@db env]$ rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Thu Oct 8 15:19:15 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: DW (DBID=3334851943)
RMAN>

Sometimes, Oracle patchset silent installation is done with a “Response File” (patchset.rsp) supplied with the patchset software. The process is documented in the README for each patchset.

What is sometimes unknown is that the same silent patchset installation can be done directly from the command prompt without response file, by using a minimum set of variables (from response file) as arguments to the OUI (i.e “runInstaller” or “setup.exe”).

For example on Linux and on patchet 10.2.0.4, following command can be used to install Oracle Database Patchset software in silent mode:

[root@app ~]# su – oracle
[oracle@app ~]$ cd /soft/db10g/patch10204/Disk1
[oracle@app Disk1]$
[oracle@app Disk1]$ ./runInstaller -silent -force -debug \
FROM_LOCATION=”/soft/db10g/patch10204/Disk1/stage/products.xml” \
ORACLE_HOME=”/opt/oracle/product/10.2.0/db_1″ \
ORACLE_HOME_NAME=”OraDb10g_home1″ \
TOPLEVEL_COMPONENT=’{“oracle.patchset.db”,”10.2.0.4.0″}’

We just need to specify the products.xml location from the patchset directory, the ORACLE_HOME path and the ORACLE_HOME name.

We can also do it on windows like this:

C:> cd c:\soft\db10g\patch10204\Disk1
C:> setup.exe -silent -force -debug FROM_LOCATION=”c:\soft\db10g\patch10204\Disk1\stage\products.xml” ORACLE_HOME=”C:\app\oracle\product\10.2.0\db_1″ ORACLE_HOME_NAME=”OraDb10g_home1″ TOPLEVEL_COMPONENT={“oracle.patchset.db”,”10.2.0.4.0″}

Hope it helps someone with problems running the patchset GUI installer…

As most of you probably know,  an AMI (Amazon Machine Image) is a pre-built package of software which can be used to create or instantiate a virtual machine within the Amazon Elastic Compute Cloud (EC2).

Amazon EC2 allows anyone with few bucks on the credit card to rent computers on which to run their own applications. EC2 provides scalable deployment of applications by using a web services interface through which we can create virtual machines on which we can load any software of our choice.

Oracle released, about 7 months ago, AMI’s in the US Amazon regions (US based data centers), that come with pre-installed Oracle Linux and Oracle Database 11g. Unfortunately in Europe we didn’t have those images available and most of us had to rely on Centos or Redhat AMI’s and then download and install Oracle software and create the database.

So, it seems these last days, quietly, Oracle released Oracle AMI’s for Europe region (London based data center) . Now it’s much faster to create Oracle Instances with 11g up and running…

The AMI’s ID’s are:

32 bit:

ami-087a517c
Oracle 11.1.0.7 Enterprise Edition
oracle-corporation-eu/database-ami/32-bit/oracle_11107_EE_32Bit-image.manifest.xml

ami-0a7a517e
Oracle 11.1.0.7 Standard Edition / SE One
oracle-corporation-eu/database-ami/32-bit/oracle_11107_SE_SE1_32Bit-image.manifest.xml
64 bit:

ami-587b502c
Oracle 11.1.0.7 Standard Edition / SE One
oracle-corporation-eu/database-ami/64-bit/oracle_11107_SE_SE1_64Bit-image.manifest.xml

ami-5e7b502a
Oracle 11.1.0.7 Enterprise Edition
oracle-corporation-eu/database-ami/64-bit/oracle_11107_EE_64Bit-image.manifest.xml

We can create the type of instance that we want, depending on our processing power needs we can choose different types of instances… The most common are:

Small Instance – The small instance (default) equates to a system with 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), 160 GB of instance storage, 32-bit platform

Large Instance – The large instance represents a system with 7.5 GB of memory, 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each), 850 GB of instance storage, 64-bit platform

Extra Large Instance – The extra large instance offers the equivalent of a system with 15 GB of memory, 8 EC2 Compute Units (4 virtual cores with 2 EC2 Compute Units each), 1690 GB of instance storage, 64-bit platform.

You have also High CPU instances that have proportionally more CPU resources than memory (RAM) and address compute-intensive applications.

Happy instantiation

An Amazon Machine Image (AMI) is a pre-built package of software which can be used to create or instantiate a virtual machine within the Amazon Elastic Compute Cloud.

An AMI forms the basic unit of deployment for services delivered using EC2 and typically includes the operating system (for example Linux, UNIX, or Windows) and additional software necessary to deliver a service.

A Quick intro… Take a look at the following issues:

Symptom : Oracle Database Configuration Assistant (DBCA) stalled while creating the database
Symptom : Oracle IAS 10G  stalled during software installation with message “Please wait, this will take a moment”
Symptom : error while loading shared libraries: $ORACLE_HOME/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

If you ever had any of the previous symptons while trying to install Oracle Software,  create a instance / database with DBCA, create a listener or even run sqlplus… then you probably have SELinux mode set to “Enforcing”.

Security-Enhanced Linux (SELinux) is a Linux feature that provides a variety of security policies, including U.S. Department of Defense style mandatory access controls, through the use of Linux Security Modules (LSM) in the Linux kernel. It is not a Linux distribution, but rather a set of modifications that can be applied to Unix-like operating systems, such as Linux and BSD.

Under Oracle Enterprise Linux or Redhat Enterprise Linux, if needed we can switch  SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode by running following commands as root user:

[root@server~]# getenforce
Enforcing
[root@server~]# setenforce 0
[root@server~]# getenforce
Permissive

The previous commandes changed the default mode to “permissive” and allows SELinux to continue running, and logging denial messages, but will not deny any operations. If you would like to return to Enforcing mode just run as root:

[root@server~]# setenforce 1
[root@server~]# getenforce
Enforcing

Another way to temporarily disable (0) or enable (1) SELinux is to run one of the following commands:

[root@server~]# echo 0 > /selinux/enforce
[root@server~]# echo 1 > /selinux/enforce
The previous commands are immediate, and will remain in effect until the next reboot. If you want to make “Permissive” mode permanent you must add “enforcing=0″ to the kernel boot line that usually is at /etc/grub.conf file. For instance:

# grub.conf generated by anaconda
#
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title Enterprise Linux (2.6.18-92.el5PAE)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.18-92.el5PAE ro root=LABEL=/ enforcing=0
        initrd /boot/initrd-2.6.18-92.el5PAE.img
.
.

Instead of editing grub we can configure SELinux by editing the file /etc/selinux/config and choose any of the following modes:

SELINUX=enforcing
or
SELINUX=permissive
or
SELINUX=disabled
After the next reboot the SELinux will comply to the permanent settings that we have choosen above.

Every Oracle Database must have a way of maintaining information that is used to rollback changes to the database. This information consists of records of the actions of transactions, primarily before they are committed. All these records are called undo.

When an user or application issue a rollback statement, undo records are used to undo changes that were made to the database by the  transaction that were not commited.

When we have a large database, most of the long batch transactions that demand lot of undo work or purge operations will increase a lot the undo tablespace size. Depending on the size of these operations your database can have an undo tablespace from few MB to dozens GB.

To save space from an once required large undo tablespace, we may resize it to make it smaller. In my databases sometimes the size of my undo tablespace achieves 20GB.

We can resize the database datafiles with:

SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M;

Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. We can check the most high used block to check the minimum size that we can resize a particular datafile. For that we can query the dba_free_space dictionary view.

Another way to set our undo tablespace to the size that we want is to create another undo tablespace, set it the default one, take offline the old and then just drop the big old tablespace.

To check your undo tablespace info issue the following statement:

SQL> SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,'undo_tablespace’);

NAME VALUE
------------------------
undo_management AUTO
undo_tablespace UNDOTBS01

SQL>
SQL> CREATE UNDO TABLESPACE undotbs02
DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ SIZE 1024M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;

SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02′;
SQL> ALTER TABLESPACE undotbs01 OFFLINE;
SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
SQL>

With these steps we created a new undo tablespace, set it as the system default undo tablespace and drop the old tablespace including the datafiles.

If you ever tried to start the Enterprise Manager with the command ‘emctl start dbconsole’ and you got stuck with the error:

OC4J Configuration issue. <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<YOURHOST>_<YOURDB> not found.

.

for instance:

OC4J Configuration issue. /opt/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_db.neoface.pt_ORCL not found.
.

Then the cause may be that Database Control is not configured or misconfigured… This can be verified by checking if the following directories exist in your ORACLE_HOME:

<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<HOSTNAME>_<DBNAME>
<ORACLE_HOME>/<HOSTNAME>_<DBNAME>
.

If those directories do not exist, Database Control has not been configured for the database.

SOLUTION

To configure the enterprise manager database control for your database, issue the following commands as oracle user:

[oracle@neoface oracle]$ export ORACLE_HOME=<ORACLE_HOME>
[oracle@neoface oracle]$ export ORACLE_SID=<ORACLE_SID>
[oracle@neoface bin]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./emca -config dbcontrol db -repos create

Now just follow the instructions on the screen… you’ll need the SYS, DBSNMP and SYSAUX passwords.

Sometimes you already have the SYSMAN, DBSNMP and SYSAUX accounts, and the previous EMCA command can fail to create the DBConsole repository the error ORA-20001 occurs.

ORA-20001 Sysman Already Exists While Running EMCA
.

The Solution is simple… Drop the existing repository

[oracle@neoface oracle]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./emca -deconfig dbcontrol db -repos drop

After removing the repository try to created it with the commands I explained previously. After you install the EM repositiry, the DBConsole should be started and you should be able to access the DBConsole using the URL provided at the installation screen.

(UPDATE)

If the previous drop procedure fails and by checking the logs we see an ORA-01031 insufficient privileges, this may be because we don’t have a password file created. Just go to $ORACLE_HOME/dbs and check if we have a file called pwd<sid>.ora. If not, create it…

[oracle@neoface oracle]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5

Hope it helps!

Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN. Imagine that you have a database on one node and you want to copy it to another node without shuting down your database and move your datafiles to a different directory structure… This will be demonstrated here by using RMAN.

ASSUMPTIONS

Source Database

• 10.2.0.4 database online (sid neo) at server1 (app)
• archivelog mode is enabled
• db datafiles are in the directory /opt/oracle/oradata/neo2
• database will be backed up online with RMAN to /u01/backup

Destiny Database

• 10.2.0.4 Oracle Home installed without any database running at server2 (mynode2.com)
• db datafiles must be created / moved to different directory: /opt/oracle/oradata/neo
• only the manual backup created at server1 will be moved to server2

AT SERVER1

Logon as oracle user software owner at server1 and set your environment variables. Then open RMAN and backup the source database we want to copy /move / clone.

[oracle@neoface oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@neoface oracle]$ export ORACLE_SID=neo
[oracle@neoface oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@neoface oracle]$ rman target /
RMAN> backup database plus archivelog;

The RMAN backup created the following files at /u01/backup:

cf_NEO_c-1689570411-20090106-00 (control file backup)
back_NEO_675389594_736_1
back_NEO_675389780_737_1
back_NEO_675390018_738_1
back_NEO_675390293_739_1

Copy those 5 backup files to server2

[oracle@neoface oracle]$ scp /u01/backup/back_NEO* root@mynode2.com:/u01/backup/

Create an initialization file (pfile) from the current spfile. Then copy it to the server2.

[oracle@neoface oracle]$ sqlplus “/ as sysdba”
SQL> create pfile from spfile;
SQL> exit;
[oracle@neoface oracle]$ scp /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora oracle@mynode2.com:/opt/oracle/product/10.2.0/db_1/dbs/initneo.ora/

AT SERVER2

Logon at server2 to do the following steps:

• create the OS directories to hold the datafiles and the admin log files and pfile:
• edit the pfile to modify the instance name in parameters like bdump, udump, etc
• change the onwership of pfile to belong to oracle user
• connect to RMAN and startup the database  in nomount mode
• restore the control file from the backup
• mount the database
• validate catalog by crosschecking and cataloging the 4 backups pieces we copied
• rename the datafiles and redolog files and restoring the database

Switch to oracle user and create datafiles directories :

[root@mynode2 root] su – oracle
[oracle@mynode2 oracle]$ mkdir /opt/oracle/admin/neo -p
[oracle@mynode2 oracle]$ cd /opt/oracle/admin/neo
[oracle@mynode2 oracle]$ mkdir cdump udump bdump pfile

[oracle@mynode2 oracle]$ mkdir /opt/oracle/oradata/neo -p

Edit your pfile accordingly your new directory structure:

[oracle@mynode2 oracle]$ vi /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora

Set environment variables and start working on RMAN:

[oracle@mynode2 oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@mynode2 oracle]$ export ORACLE_SID=neo
[oracle@mynode2 oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@mynode2 oracle]$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from ‘/u01/backup/cf_NEO_c-1689570411-20090106-00′;
RMAN> alter database mount ;
RMAN> exit

Now that the database is mounted, we’ll check the correct database SCN from the current log that we’ll use later to recover the database. Take note of your current SCN.

[oracle@mynode2 oracle]$ sqlplus “/ as sysdba”
SQL> select group#, first_change#, status, archived from v$log;

    GROUP# FIRST_CHANGE# STATUS           ARC
---------- ------------- ---------------- ---
     1     336565140     ACTIVE           YES
     2     336415067     CURRENT          NO
     3     336523814     INACTIVE         YES

SQL> exit;

[oracle@mynode2 oracle]$ rman target /

As we only copied to this server the backup we created at the beggining and we did not copy all the backups we had on server1 we must crosscheck the catalog against the OS files. Run the following commands at RMAN prompt :

RMAN> CROSSCHECK backup;
RMAN> CROSSCHECK copy;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

Now let’s catalog the 4 backup pieces that we copy to this server2:

RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389594_736_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389780_737_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390018_738_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390293_739_1′;

Next, as we changed the directory of our datafiles we must rename the redologs:
RMAN> ALTER DATABASE  rename file ‘/opt/oracle/oradata/neo2/redo01.log’ to ‘/opt/oracle/oradata/neo/redo01.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo02.log’ to ‘/opt/oracle/oradata/neo/redo02.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo03.log’ to ‘/opt/oracle/oradata/neo/redo03.log’;

If you use BLOCK CHANGE TRACKING to allow fast incremental backups,  and if you want to move the datafiles to different directory you must disable this feature and enabling it by specifying the new dir:

RMAN> ALTER DATABASE disable block change tracking;
RMAN> ALTER DATABASE enable block change tracking using file ‘/opt/oracle/oradata/neo/block_change_tracking.f’;

This will avoid errors like ORA-19751 and ORA-19750

Now let’s run the script that will restore our database, renaming the datafiles and recovering until the archivelog with SCN 336415067, the current one.

RMAN> run {
set newname for datafile 1 to “/opt/oracle/oradata/neo/system01.dbf”;
set newname for datafile 2 to “/opt/oracle/oradata/neo/undotbs01.dbf”;
set newname for datafile 3 to “/opt/oracle/oradata/neo/sysaux01.dbf”;
set newname for datafile 4 to “/opt/oracle/oradata/neo/data01.dbf”;
set newname for datafile 5 to “/opt/oracle/oradata/neo/index01.dbf”;
set newname for datafile 6 to “/opt/oracle/oradata/neo/users01.dbf”;
set newname for datafile 7 to “/opt/oracle/oradata/neo/streams.dbf”;
set newname for datafile 8 to “/opt/oracle/oradata/neo/data01brig.dbf”;
set newname for datafile 9 to “/opt/oracle/oradata/neo/index02.dbf”;
restore database;
switch datafile all;
recover database until scn 336415067;
}

RMAN> ALTER DATABASE open resetlogs;

I didn’t manage to avoid errors like ORA-01110 and  ORA-01180 at RMAN without using the  “until” clause in the “recover database” sentence instead, like most people use it, as the first instruction after the run command.

Later I’ll upload a video for you easily see it running flawlessly

Btw, HAPPY NEW YEAR!!!!

Less than 1 week to go until UKOUG 2008 in Birmingham ICC… My scheduled sessions are:

Monday
Advanced SQL for PL/SQL Programmers
Oracle Application Express Now and in the Future
Oracle Forms: Features and Future
Oracle 11g/10g Developers: What You Need to Know

Tuesday
Web 2.0 in Buisness or Education . Which is the better marriage?
Application Express Best Practices
Change Change Change
Oracle Forms – The New World
Weird PL/SQL
Database Development with Oracle SQL Developer: An Overview

Wednesday
Oracle SQL Developer: Focusing on a Few Advanced Features
Obtaining and Interpretting Execution Plans using DBMS_XPLAN
Migrating Oracle Forms to SOA and J2EE (Oracle ADF)
Advanced Oracle Application Express Tips and Techniques
Bringing Oracle Designer and CASE repositories back to life – a return on your investment
Visual Data Modeling with Oracle SQL Developer

Thursday
Mastering charts in Oracle Application Express (APEX)
Being Steven Feuerstein
Designing PL/SQL applications
Consolidate your Desktop Databases to Oracle 11g
(Re)Developing a logistic application in APEX in the real world
Securing APEX an application on a Microsoft platform
Performance Tuning ApEx Applications

Friday
Become a Fusion Developer with no Java: A JDeveloper 11g Masterclass (FTMS)
Advanced SQL Features

Hope to see any of you guys there…

Backing up terabyte size databases can be time consuming. When RMAN performs an incremental backup, Oracle must scan all database / tablespace to identify which blocks have been updated since last backup. This, of course, is very time and resource consuming. Fortunatly Oracle 10g brings the possibility to track changed blocks using a change tracking file. Enabling database change tracking adds a small overhead, but it largely improves the performance of incremental backups.

In the next sample code I’ll check the current change tracking status, alter database do enable this feature and add a file to record the changed database blocks

SELECT status FROM v$block_change_tracking;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/mydb/rman_change_tracking.ora' REUSE;

After this, when block is changed in the database, Oracle will record the fact in the rman_change_tracking.ora file. During incremental backup, RMAN checks this file to see which blocks need the backup instead of checking all the blocks of a tablespace / database. This dramatically reduces CPU cycles and speeds up incremental backup in the process.

Our tracking file will start with 10MB and grows in 10MB increments.

Block Change tracking can be disabled at any time with:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

We can also rename or move the tracking file at any time issuing the command ALTER DATABASE RENAME FILE.

By reducing the backup time we can achieve the following objectives too:

• Increase the frequency of incremental backups
• Reduce recovery time and mean time to recover

In a distributed database system, each database must have a unique name… a global database name. Global database names uniquely identify a database in the system.

To query a database’s global name issue this instruction:

SQL> SELECT * FROM global_name;

GLOBAL_NAME
-----------
db1.world
.

A global database name is formed by two components: a database name and a domain, as I said before, uniquely identifies it from any other database. An example global name might be mydb.beyondoracle.com. There are 2 parameters that define the global database name. The DB_NAME (no more than 8 chars) and the DB_DOMAIN (Must follow Internet conventions).

To change our database global name, do this:

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO db1.beyondoracle.com

When we change / set the database to use global names (GLOBAL_NAMES parameter set to TRUE), all database links must be valid global names. When you create a database link with global names set to true, the database link will be formed by the database name plus the domain. In this environment Oracle enforces the requirement that the database.domain  portion of the database link name must match the complete global name of the remote database. For instance, if your remote database is called db1.beyondoracle.com then the database link you create to access this remote database, must have the same name… db1.beyondoracle.com!

When using or enabling global names, when we didn’t create the database link with the previous rules I talked about (db_name.db_domain), it’s common getting this error: ORA-02085: database link string connects to string.

When you create a database link we must mention the connect_string that you want to connect to. This connect_string is the tnsnames entry that you have on your database server tnsnames.ora file. For instance when you create this db link:

SQL> CREATE DATABASE LINK db1.beyondoracle.com
CONNECT TO myuser IDENTIFIED BY mypass USING ‘remotedb’;

Database Link Created

Then you must ensure that your database tnsnames.ora has the entry remotedb such as:

# connect string for database
remotedb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.beyondoracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db1.beyondoracle.com)
   )
  )
# end of connect string

This tnsnames.ora connect string (remotedb) has nothing common with the database link name, but very often the same name is used. You can call your connect string anything you want!

Anytime you can check your database links by querying user_db_links or all_db_links.

SQL> SELECT * FROM user_db_links;

DB_LINK              USERNAME  PASSWORD  HOST                 CREATED
-----------          --------  --------  -------              -------------
db1.beyondoracle.com myuser              db1.beyondoracle.com 15-09-2008 15:22:56
.

To finish, remember that almost every oracle distributed environment using replication uses global names. There’s only some custom replication processes that don’t need it, but Oracle Replication technology, like Streams for instance, needs global names database compliance.

My prayers and thoughts go to Carl Backstrom and his family. Wherever you are, rest in peace!