Oracle : Database Links - Create, Use and Drop

A database link is an important schema object in the oracle database, we should know details about it in order to implement it in real-time applications,

Assume you have two different applications with two different databases, and while generating reports, you need to fetch and merge data from both the database, it’s a good idea to use a database link, i.e. connecting 2 databases together

We will discuss more

Generally, there are 2 types of database links,

• Public 
• Private 

Public - database link, which can be accessed by all users in the databases, 

Private- database link, which can be accessed by only specific users

 The syntax for creating database links is quite similar 

 create [Public/private] database link <link name>

 Explanation with example 

 Assume below details 

	Database	Schema	Connection link
Source details	technodb	tech_owner	technodb
Target details	asgard	tech_as_owner	asgard

 We will be creating database link asgard_link in database technodb, connecting to the asgard database, and will try to fetch data from a remote database

Create a private database link

Connect to the database using userid tech_owner and execute create database link command, before executing the command remember that, you need to have to create database link privileges assigned.

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER';

 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     CREATE DATABASE LINK

You can see, tech_owner has create database link privileges

Now create a private database link 

SQL> show user

USER is "TECH_OWNER"

SQL> create database link asgard_link

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard';

 

Database link created.

 

Here,

asgard_link – is the database link name

tech_as_owner is a remote user and its password

‘asgard’ – connection string to connect remote database 

Verifying if the database link is working

Execute select query to fetch data from remote database using db link, if returns value, it working fine or in case of error, need to rectify

 SQL> show user

USER is "TECH_OWNER"

SQL> select sysdate from dual@asgard_link;

SYSDATE

---------

29-AUG-22

 

Create a public database link

Similarly, we can create a public database link using the below statements, make sure you have create public database link  privilege granted

SQL> grant create public database link to tech_owner; 

Grant succeeded.

SQL> show user

USER is "TECH_OWNER" 

SQL> create public database link asgard_link_pub

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard'; 

Database link created.

 Now try accessing it through different accounts, you will be able to fetch the data,

SQL> show user

USER is "TECH_OWNER"

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22

SQL> conn /as sysdba

Connected.

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22 

How to drop database link

to drop the private database link, you need to connect using a username account and then execute the drop command,

SQL> show user

USER is "TECH_OWNER"

SQL> drop database link ASGARD_LINK; 

Database link dropped.

 To drop the public database link, you need to mention the public identifier, like below

 SQL> drop public database link ASGARD_LINK_PUB ; 

Database link dropped.

 

Read More

pfile and spfile in oracle database

 In this short article, we will discuss the server parameter file and initialization parameter file i.e., spfile and pfile

 

pfile - parameter file, is a text-based file, readable and editable with any text editor

spfile- server parameter file, binary file, readable with only some utility and can not be edited.

 

pfile is called the initialization parameter file because this is the first file used to start (initialize) the database, spfile is created later on. 

The default extension of these files is ".ora", in an ASM file name can be with any random or fixed number i.e. spfiletechnodb.1223.343543 (technodb is database name)

The following parameters can be seen in the parameter files

 

• memory parameters such as memory target, sga, pga, shared_pool, java pool, large pool
• database and instance name (can be different)
• parameters related to different physical files such as control file, datafiles, archive files
• processes and session details
• trace file location, audit settings, and many more

 

the very first stage of the database startup is "nomount" and that’s where this parameter file will get read, it will load database memory and process settings and takes the location of control files.

 

The default location of the parameter file is based on the operating system you are using

in Linux/Unix it is located at $ORACLE_HOME/dbs location and for windows operation system it is %ORACLE_HOME%\database

 

Parameter change

Assume your database is running on pfile and you want to change the parameter of the database, then you need to perform the below steps

 

• shutdown the database
• add/change the parameter in pfile
• start the database

 

Now assume your database is running on spfile- then based on condition, you may not at all need to restart the database

• change the parameter using alter system command
• Restart the database if required

 

Read More

ORA-39161: Full database jobs require privileges

I was trying to take full export backup of the database using user from the pluggable database, the export command threw below error

 

Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

 

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:42:47 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

ORA-31631: privileges are required

ORA-39161: Full database jobs require privileges

 

I realized that SYS, SYSTEM does not need any explicit privileges to be granted while performing export and import, but while using any other user, we need to grant proper privileges to take full database export.

 

SQL> alter session set container=technopdb;

 

Session altered.

 

SQL> grant DATAPUMP_EXP_FULL_DATABASE to techno_user;

 

Grant succeeded.

 

Here I granted DATAPUMP_EXP_FULL_DATABASE privileges to user and tried taking export and it worked

 

C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

 

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 2022

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

 

 

Here we need to note that, taking export and performing import requires special privileges

Read More

Rename pluggable database

There might be a situation when you created a pluggable database with the wrong name then it's not time to worry, you can simply rename it using rename command, in another situation where the client requested to change the name of the pluggable database, then rename command will to amazing work. Here we will discuss renaming oracle PDB

 

Below are the pluggable database present in my system I want to rename technopdb3 to technopdb2 

Below are steps to perform it

The first step is to close the pluggable database 

 

SQL> alter pluggable database technopdb3 close immediate;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     MOUNTED

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

pluggable database technopdb3 has been closed, now open same pluggable database in restricted mode 

 

SQL> alter pluggable database technopdb3 open restricted;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     READ WRITE YES

         5 TECHNOPDB_NEW                  READ WRITE NO

 

now connect to pluggable database using alter session command and execute rename command as mentioned below 

 

SQL> alter session set container=technopdb3;

 

Session altered.

 

SQL> alter pluggable database technopdb3 rename global_name to technopdb2;

 

Pluggable database altered.

 

 

Now close the pluggable database and open in normal mode

 

SQL> alter pluggable database close;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB2                     READ WRITE NO

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

In this way, technopdb3 has been renamed to technopdb2

Read More

All about oracle database auditing

As your application and database grow, more and more users get connected to the database, and it becomes difficult to manage all the privileges that individual user has granted. So, there is a feature provided by the oracle called AUDITING.

Users with DBA privileges can do a lot of things with the database, it is important to make sure that users with DBA privileges should not harm the database by any means, in order to do it, the security team or database admin can enable different levels of auditing.

Before enabling any auditing, you need to consider the fact that, this is put some extra effort into the database, so try to push it on a minimal basis.

 

Auditing SYSDBA activity

Based on the parameter AUDIT_SYS_OPERATIONS value, SYSDBA activity is tracked at the operating system level audit trail file. If AUDIT_SYS_OPERATIONS is set to TRUE then every statement executed by the user connected as “as sysdba” or “as sysopr”  is audited. Location of audit trail file can be set and monitored by parameter AUDIT_FILE_DEST

 

Database Auditing

Database auditing is controlled using the parameter AUDIT_TRAIL parameter, there are different values associated with it.

 

NONE (FALSE) – database auditing is disabled

OS – auditing will be recorded at the OS level audit trail and location is controlled by audit_file_dest

DB- auditing will be recorded at database table SYS.AUD$

DB_EXTENDED – saves at database level but includes the SQL statements with bind variables

XML- auditing is done at OS level, formatted with XML tag

XML_EXTENDED- formatted as XML tags, includes SQL statements with bind variables

 

Database auditing can be configured by AUDIT commands,

For example

 SQL> audit update any table; 

Audit succeeded. 

SQL> audit select any table by session; 

Audit succeeded. 

SQL>

 Assume, that few users have “update any table” privilege granted, this can be used to harm the database as well apart from regular work.  In order to record what tables are being updated, you can simply turn on auditing for the same.

By default, auditing will generate one row for each auditing violation.

BY SESSION- one record for each session does not matter how many times it violates (DEFAULT)

BY ACCESS- one record for every violation.

 

Auditing can be enabled on specific objects as well, for example

SQL> audit select on SYS.DBA_USERS whenever successful; 

Audit succeeded. 

SQL> 

This statement generates a record for every successful insertion for the table SYS.DBA_USERS

WHENEVER SUCCESSFUL – records only when the insertion is successful

WHENEVER NOT SUCCESSFUL – records only when the insertion is failed

By default- both conditions are recorded.

 

When AUDIT_TRAIL is set as OS or XML, one can check audit records at OS level audit trail file and when AUDIT_TRAIL is set as DB or a similar one, you can fetch records using SYS.AUD$ or you can use the DBA_AUDIT_TRAIL view. There are around 50 columns available to view in the DBA_AUDIT_TRAIL view.

There are a couple of subset views of DBA_AUDIT_TRAIL and can  be used to narrow down the results

DBA_AUDIT_OBJECT,

DBA_AUDIT_STATEMENT

DBA_AUDIT_SESSION

 

Auditing WITH triggers

Auditing enabled using the audit command will only have a single record for each statement, but it will not have the exact statement used to insert the record. Sometimes you may need to watch out for statements executed,

Database triggers will help to capture the statement based on the condition defined. Assume, there is an update trigger defined on the table, you try to update the table, it will simply generate an audit record and put the row in another table defined in trigger.

 

Fine-Grained Auditing (FGA)

Till now, we have discussed auditing at database level or table level, what if you want to capture auditing for only specific rows in table or views, FGA can help you to achieve this.

FGA can be configured through package DBMS_FGA and add FGA audit policy, need to use ADD_POLICY procedure.

To view records, you need to use the DBA_FGA_AUDIT_TRAIL view, generally FGA auditing can be enabled on rows on which data is critical such as salary or budget or revenue.

DBMS_FGA has a lot of procedures and can be used to add, drop, enable policy, disable policy.

 

SQL> desc dbms_FGA

PROCEDURE ADD_POLICY

PROCEDURE DISABLE_POLICY

PROCEDURE DROP_POLICY

PROCEDURE ENABLE_POLICY 

This is all about the database auditing theory part.

Read More