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.