Export full database using data pump expdp

Create a directory.

• cd /home/oracle
• mkdir -p datapump/fullexport

Connect database using SYS user

• sqlplus / as sysdba

Create a directory object

• create a directory orcl_full as '/home/oracle/datapump/fullexport';

Grant read, write privileges on directory object and a rule 'datapump_exp_full_database' to hr user

• grant read,write on directory orcl_full to hr;
• grant datapump_exp_full_database to hr;

Date-Manipulation Functions

MONTHS_BETWEEN: gives number of months between two dates
ADD_MONTHS: adds calendar months to date
NEXT_DAY: gives next day of the date specified
LAST_DAY: gives last day of the month
ROUND: round date
TRUNC: truncate date

Drop database using RMAN

For this, we, first need to connect to the target database and mount (NOT OPEN), enable restricted mode. Following are the rman commands:

RMAN> startup force mount;
RMAN> sql 'alter system enable restricted session';
RMAN> drop database including backups noprompt;

This will remove all datafiles, online redo logs, archived redo logs and control files, backup sets, image copies without any prompts.

Change an Oracle database to noarchivelog mode

Here, we will alter our database to no archive log mode.

For this,

• shutdown our database if it is running
• take our database to mount mode
• alter database to noarchivelog
• open database
• verify

Resolving lock conflict with SQL in Oracle (Killing locked session)

In this topic, we will demonstrate how row is locked when one user is updating and at the same time other user fire update sql for the same row.

For this, we will

• create two application users user1 and user2
• create role hr_employee to create session and select, update on hr.employees table.
• grant hr_employee to user1 and user2
• user1 will fire update sql for a particular row and user1 will not commit. (i.e., row will be locked)
• At the same time, user2 will fire update sql for the same row. (user2 will not be prompted any update success message)
• dba will identify the locking conflict and resolve the locking conflict by killing user1 session

Oracle Restricted Session

Sometimes we need to work on a database without any other users being logged in. In this scenario, it is possible to restrict the database session. When the database is altered to restricted mode, only users with restricted session privileges can get access to the database. Technically it is in open mode.

If database is running, alter system command is used to enable and disable restricted session as follows. We can see, select logins from v$instance will verify this:

Shutting Down an Oracle Database Instance

There are four modes of shutting down an Oracle Instance

1. NORMAL
2. TRANSACTIONAL
3. IMMEDIATE
4. ABORT

Starting up an Oracle Database Instance

When starting the database instance, we need to select the state in which it starts.

1. NOMOUNT
2. MOUNT
3. OPEN

1. NOMOUNT: An instance is typically started only in NOMOUNT mode during database creation, during re-creation of control files, or during certain backup and recovery scenarios.

How to configure Oracle 11g enterprise manager (OEM DBConsole) for an existing database?

1. cd $ORACLE_HOME/bin/
2. Command to drop the existing configuration
• emca -deconfig dbcontrol db -repos drop
3. Command to create
• emca -config dbcontrol db -repos create

What does i, g and c in oracle 8i, 9i, 10g, 11g and 12c stand for?

The i in oracle 8i and 9i stands for INTERNET and the g in 10g and 11g stands for GRID because from 10g onwards oracle supports grid architecture.

A relational database management system (DBMS) from Oracle, which runs on more than 80 platforms. Introduced in the late 1970s, Oracle was the first database product to run on a variety of platforms from micro to mainframe. The Oracle database is Oracle's flagship product, and version 11g was introduced in 2007.