How to Import data via a network link in Oracle

Today , I will tell you the process to Import your data via a network link between 2 Database, located remotely

In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.

The first step is to create a “database link object” to identify the source database and then provide the login credentials.

For example, a source database located in INDIA might be identified by the Oracle network service name ‘GOA’.

A user in that instance, ‘ADMIN1’ logs in using the password ‘ADMIN1’ and has the correct privileges to access the data to be imported.

Create a Database link using CREATE DATABASE LINK command, then, could be used to define the source database as follows :

SQL> CREATE DATABASE LINK INDIA

CONNECT TO admin1 IDENTIFIED BY admin1

USING ‘GOA’;

Now, The Data Pump import command, “impdp”, can now use this database link to directly access remote data.

The parameter NETWORK_LINK  points to the source database via its database link.

On the local database instance located in USA, user ‘ADMIN2’ executes the following command (all one line):

$ impdp admin2/admin2 TABLES= customers,sales,employees DIRECTORY=dpump1

NETWORK_LINK= INDIA

Tip :

You can get the information about database links in the view  :  DBA_DB_LINKS

Example :

SQL> DESC DBA_DB_LINKS

Click here to Join Our Blog and Stay Updated

How to Find out , Who / What is using your UNDO space?

Below are some views that show information related to undo activity..

* V$UNDOSTAT:                   This shows statistics for 10-minute intervals.

* V$TRANSACTION:             This provides information on current transactions.

* V$SESSTAT:                        Provides individual session statistics, which includes one for undo usage.

V$UNDOSTAT (This provides a hint regarding “who did the thing”)

This  records the longest running query for that 10-interval, through the “MAXQUERYID” column which may be linked to V$SQL and use columns “PARSING_USER_ID” or “PARSING_SCHEMA_NAME” the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions.

Check the below query :

SELECT  a.sid, a.username, b.used_urec, b.used_ublk

FROM v$session a, v$transaction b

WHERE a.saddr = b.ses_addr

ORDER BY b.used_ublk DESC

V$SESSTAT (Tells a hint “who uses the undo”)

Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value

FROM v$sesstat a, v$statname b

WHERE a.statistic# = b.statistic#

AND a.statistic# = 255    <-- Which stands for undo change vector size

ORDER BY a.value DESC

Click here to Join Our Blog and Stay Updated

Pre-check for DataGuard switchover Process

Please perform these pre-requisite checks before undertaking a switchover to primary.

Ø   ON STANDBY SITE:

The database_role must be “PHYSICAL STANDBY”  . . . check this using below query

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------------

PHYSICAL STANDBY

Note: If you receive a reply like below

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------------

PRIMARY

Then do not proceed if this message is received …in this case you are most probably on the primary site..

Next Check, Again on STANDBY SITE:

While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alertlog file,.. but make sure it is set to ENABLE before starting a switchover.

The below command can be issued to convert it to ENABLE

sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

Next Check :

If any of the datafiles are in RECOVER status, DO NOT PROCEED with the switchover…

SQL> select distinct status from v$datafile;

STATUS

------------

ONLINE

ONLINE

SYSTEM

Next Check:

If the STANDBY database has been opened in READ ONLY mode, then do NOT proceed.

If the database is in mount mode, then only proceed.

(This is to ensure that the logs are being shipped and more importantly the LAST LOG archived has been applied…)

QL> select distinct open_mode from v$database;

OPEN_MODE

-------------------

MOUNTED

Now,.. On PRIMARY site

SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED

——————

25

And  on STANDBY site.

SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED

—————-

25

Both the values are same, so you can Proceed for the Switchover.

Click here to Join Our Blog and Stay Updated

Quick Troubleshooting Steps for Daily Activities

How to detect locking issues in Database ?

-During this,, you need to find Locking sessions and Kill 1Session among those

Sql>select a.SID "Blocking Session." ,b.SID "Blocked Session" from v$lock a, v$lock b 

       where a.SID != b.SID and a.ID1 = b.ID1  and a.ID2 = b.ID2  and b.request > 0  and a.block = 1;

                 

   Blocking Session.      Blocked Session

                    -----------------            ---------------

                  56                             83

Process for Killing the session

Sql>select sid,serial# from v$session where sid=56;

Sql> alter system kill session '56,83';

How to Relink the oracle binaries

-Run the below command

$ORACLE_HOME/bin/relink all

How to gather Instance hang analysis data

-Use the Oradebug utility

$sqlplus –prelim / as sysdba

Sql>oradebug setmypid

Sql>oradebug unlimit

Sql>oradebug hanganalyze 3

Open another terminal

$sqlplus –prelim / as sysdba

Sql>oradebug setmypid

Sql>oradebug unlimit

Sql>oradebug dump systemstate 256

What will you do, If sqlplus executable is corrupted then

-Run these below commands

$cd $ORACLE_HOME/sqlplus/lib

$make –f ins_sqlplus.mk install

What will you do, If lsnrctl utility is corrupted then

$ORACLE_HOME/network/lib

$make -f ins_net_server.mk install

IF oracle, exp, imp, sqlldr, tkprof utilites are corrupted then

-run these below command

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk install

How to check the detailed error message

-using the ‘oerr’ Utility

$oerr ora 0600

What is ADR (Automatic Diagnostic Repository) ?

In 11g user_dump_dest,core_dump_dest,background_dump_dest are replace by diagnostic_dest

Sub-dirs under diagnostic_dest 

       

  Alert rdbms incidents

  The Alert log file are in two format

  Log.xml

  Alert_<SID>.log

  adrci Utility is used to see the content of alert-log or incident details

 -below are the commands 

  $ adrci

 

  Adrci>show alert

  Adrci>help

  Adrci>show incident

Follow us on Facebook, and stay updated

See Also Health Check – When the Application Users complaints for an issue, what to do ? 

Server Maintenance Tasks for a DBA

General Tips to Prepare for an Oracle DBA Job Interview  *HOT*