Oracle Database Links

-
What are Database Link:
=======================

A database link is one way method of accesing a remote database from other database provided the link should be existing in the dictionary of the local database.
We usually create a DB link based on the DB global database name.

eg:
==

A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote
database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A,
then they must define a link that is stored in the data dictionary of database B.

A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have
a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.


Types of DB links:
================
Conencted user link - A local user accessing a database link in which no fixed username and password have been specified
Fixed user Link - A user whose username/password is part of the link definition. If a link includes a fixed user, the fixed user's username and password are used
                   to connect to the remote database.
Current user Link  -> In this case we should have same user account created in the remote database as your local database


Names for Database Link:
=======================
A DB link by default will have a naming convention of DBNAME.DOMAIN NAME and this is being maintained by parameter called GLOBAL_NAMES. If it is set to false then
we can use any name


DB LINK TYPES:
=============

Private: Creates link in a specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link
          to access database objects in the corresponding remote database.

Public: Creates a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.

Global: Creates a network-wide link. When an Oracle network uses a directory server, the directory server automatically create and manages global database links
       (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in
   the corresponding remote database.




Commands:
=========
CREATE DATABASE LINK sales.us.americas.acme_auto.com USING 'sales_us'; -> connected
CREATE DATABASE LINK foo CONNECT TO CURRENT_USER USING 'am_sls'; -> current_user
CREATE DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger USING 'sales_us'; -> fixed user
CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY tiger USING 'rev';


Comments

Post a Comment

Popular posts from this blog

Understanding Terraform

-
 Terraform is a tool used  for building, changing, and versioning infrastructure safely and efficiently. A Terraform can manage multiple cloud services using a provider plugin  Terraform work flow? Terraform work flow will be comprised of validate -> plan -> apply and finally destroy. what is the important file of terraform which records the actions of Terraform apply? For every Terraform execution the action will be recorded in a file called Terraform state file .  The terraform init command is used to initialise a working directory containing Terraform configuration files. During init, the configuration is searched for module blocks, and the source code for referenced modules is retrieved from the locations given in  their source arguments. The terraform plan command is used to create an execution plan. It will not modify things in infrastructure. The terraform apply command is used to apply the changes required to reach the desired state of the configur...
Read more

How to make CRS and ASM not to restart after server reboot

-
I have noticed that server rebooting is taking lot of time on a VM machine for every reboot, so I have decided to auto start of the crs and ASM service During this phase I have noticed an attribute called auto_start when I try to execute the below command crsctl stat res -p This AUTO_START attribute is there for several resources like ora.ASM ,ora.crs,ora.DATA_DG it has got 2 options restore(0) , never(2) and always (1) always (1)  Causes the resource to restart when the node restarts regardless of the resource’s state when the node stopped -restore (0)  Does not start the resource at restart time if it was in an offline state, such as STATE=OFFLINE, TARGET=OFFLINE, when the node stopped. The resource is restored to its state when the node went down. The resource is started only if it was online before and not otherwise. -never (2)  Oracle Clusterware never restarts the resource regardless of the resource’s state when the node is stopped. So I have dec...
Read more

How to repair ASM disk header

-
Image
What is ASM disk header corruption? ASM disk header corruption is a loss or damage to the disk header which contains the metadata essential for the operation and availability of an ASM disk group in Oracle. Imagine that you have an ASM disk corruption in any of the ASM diskgroups that have critical business data and the diskgroup has been configured as "EXTERNAL" redundancy, meaning that if one disk is inaccessible the whole diskgroup will be unavailable. Now what? Is your only option to restore it from backup by recreating the diskgroup or are there any other faster ways to recover the data? Here you need to find out whether it’s a complete loss of data in the ASM disk or is it just a header corruption. Hopefully for you, this is just a header corruption. Because, yes, there is a way out!  Below find the steps to restore from the ASM header corruption. Most of the data in the ASM disk header is of interest to that disk only. However, some information in the ASM disk header ...
Read more