Shutdown Modes

Shutdown modes are progressively more accommodating of current activity in this order:

• ABORT: Performs the least amount of work before shutting down. Because this
requires recovery before startup, use this only when necessary. This is typically used
when no other form of shutdown works, when there are problems when starting the
instance, or when you need to shut down immediately because of an impending
situation, such as notice of a power outage within seconds.

• IMMEDIATE: Is the most typically used option. Uncommitted transactions are rolled
back.

• TRANSACTIONAL: Allows transactions to finish

• NORMAL: Waits for sessions to disconnect
If you consider the amount of time that it takes to perform the shutdown, you find that
ABORT is the fastest and NORMAL is the slowest.

SHUTDOWN Options

SHUTDOWN NORMAL

Normal is the default shutdown mode. A normal database shutdown proceeds with the
following conditions:
• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated and the SGA is removed from memory.
• The Oracle server closes and dismounts the database before shutting down the
instance.
• The next startup does not require an instance recovery.

SHUTDOWN TRANSACTIONAL

A transactional shutdown prevents clients from losing data, including the results from their
current activity. A transactional database shutdown proceeds with the following
conditions:
• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have been completed, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:
• Current SQL statements being processed by the Oracle database are not completed.
• The Oracle server does not wait for the users who are currently connected to the
database to disconnect.
• The Oracle server rolls back active transactions and disconnects all connected users.
• The Oracle server closes and dismounts the database before shutting down the
instance.
• The next startup does not require an instance recovery.

oracle startup modes and functions

Starting Up an Oracle Database Instance: NOMOUNT

When starting the database instance, select the state in which it starts. The following
scenarios describe different stages of starting up an instance.
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.
Starting an instance includes the following tasks:

• Searching <oracle_home>/dbs for a file of a particular name in this order:
- spfile<SID>.ora
- If not found, spfile.ora
- If not found, init<SID>.ora

This is the file that contains initialization parameters for the instance.
Specifying the PFILE parameter with STARTUP overrides the default
behavior.
• Allocating the SGA
• Starting the background processes
• Opening the alert<SID>.log file and the trace files

Starting Up an Oracle Database Instance: MOUNT

Mounting a database includes the following tasks:

• Associating a database with a previously started instance
• Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and statuses of the data files and online
redo log files. However, no checks are performed to verify the existence of the data
files and online redo log files at this time.
To perform specific maintenance operations, start an instance and mount a database, but
do not open the database.
For example, the database must be mounted but must not be opened during the following
tasks:
• Renaming data files (Data files for an offline tablespace can be renamed when the
database is open.)
• Enabling and disabling online redo log file archiving options
• Performing full database recovery
Note: A database may be left in MOUNT mode even though an OPEN request has been
made. This may be because the database needs to be recovered in some way.

Starting Up an Oracle Database Instance: OPEN

A normal database operation means that an instance is started and the database is mounted
and opened. With a normal database operation, any valid user can connect to the database
and perform typical data access operations.

Opening the database includes the following tasks:
• Opening the online data files
• Opening the online redo log files
If any of the data files or online redo log files are not present when you attempt to open the
database, then the Oracle server returns an error.

During this final stage, the Oracle server verifies that all the data files and online redo log
files can be opened and checks the consistency of the database. If necessary, the System
Monitor (SMON) background process initiates instance recovery.

You can start up a database instance in restricted mode so that it is available to users with
administrative privileges only. To start an instance in restricted mode, select the “Restrict
access to database” option on the Advanced Startup Options page.

Oracle database physical files

The files that constitute an Oracle database are organized into the following:

• Control files: Contain data about the database itself (that is, physical database structure
information). These files are critical to the database. Without them, you cannot open data
files to access the data within the database.

• Data files: Contain the user or application data of the database

• Online redo log files: Allow for instance recovery of the database. If the database crashes
and does not lose any data files, then the instance can recover the database with the
information in these files.

The following additional files are important to the successful running of the database:

• Parameter file: Is used to define how the instance is configured when it starts up

• Password file: Allows users to connect remotely to the database and perform
administrative tasks

• Backup files: Are used for database recovery. You typically restore a backup file when a
media failure or user error has damaged or deleted the original file.

• Archive log files: Contain an ongoing history of the data changes (redo) that are generated
by the instance. Using these files and a backup of the database, you can recover a lost data
file. That is, archive logs enable the recovery of restored data files.

• Trace files: Each server and background process can write to an associated trace file.
When an internal error is detected by a process, the process dumps information about the
error to its trace file. Some of the information written to a trace file is intended for the
database administrator, whereas other information is for Oracle Support Services.

• Alert log files: These are special trace files. They are also known as alert logs. The alert
log of a database is a chronological log of messages and errors. Oracle recommends that
you review these files.

oracle background process

An Oracle database server consists of an Oracle database and an Oracle instance. An Oracle
instance is made up of memory structures, known as the System Global Area (SGA), and
background processes that handle much of the behind-the-scenes work involved in running an
instance. The most common background processes are the following:

• System Monitor (SMON): Performs crash recovery when the instance is started following
a failure

• Process Monitor (PMON): Performs process cleanup when a user process fails

• Database Writer (DBWn): Writes modified blocks from the database buffer cache to the
data files on the disk

• Checkpoint (CKPT): Updates all the data files and control files of the database to indicate
the most recent checkpoint

• LogWriter (LGWR): Writes redo log entries to the disk

• Archiver (ARCn): Copies redo log files to the archival storage when a log switch occurs

Server Process and Database Buffer Cache

When a query is processed, the Oracle server process looks in the database buffer cache for any
blocks that it needs. If the block is not found in the database buffer cache, the server process
reads the block from the data file and places a copy in the database buffer cache. Because
subsequent requests for the same block may find the block in memory, the requests may not
require physical reads. The Oracle server uses the least recently used algorithm to age out buffers
that have not been accessed recently to make room for new blocks in the database buffer cache.
Buffers in the buffer cache can be in one of the following four states:

• Pinned: Multiple sessions are kept from writing to the same block at the same time. Other
sessions wait to access the block.

• Clean: The buffer is now unpinned and is a candidate for immediate aging out, if the
current contents (data block) are not referenced again. Either the contents are in sync with
the block contents stored on the disk or the buffer contains a consistent read (CR) snapshot
of a block.

• Free or unused: The buffer is empty because the instance has just started. This state is
very similar to the clean state, except that the buffer has not been used.

• Dirty: The buffer is no longer pinned but the contents (data block) have changed and must
be flushed to the disk by DBWn before it can be aged out.

Oracle Memory Structures

The basic memory structures associated with an Oracle instance include the following:
• System Global Area (SGA): Shared by all server and background processes.

• Program Global Area (PGA): Private to each server and background process. There is
one PGA for each process.

The SGA is a memory area that contains data and control information for the instance.
The SGA includes the following data structures:

• Database buffer cache: Caches blocks of data retrieved from the database

• Redo log buffer: Caches redo information (used for instance recovery) until it can be
written to the physical redo log files stored on the disk

• Shared pool: Caches various constructs that can be shared among users

• Large pool: Is an optional area that provides large memory allocations for certain large
processes, such as Oracle backup and recovery operations, and I/O server processes

• Java pool: Is used for all session-specific Java code and data within the Java Virtual
Machine (JVM)

• Streams pool: Is used by Oracle Streams

When you start the instance by using Enterprise Manager or SQL*Plus, the amount of memory
allocated for the SGA is displayed.

Program Global Area (PGA) is a memory region that contains data and control information for
each server process. An Oracle server process services a client’s requests. Each server process
has its own private PGA that is created when the server process is started. Access to the PGA is
exclusive to that server process, and the PGA is read and written only by the Oracle code acting
on its behalf.

With the dynamic SGA infrastructure, the size of the database buffer cache, the shared pool, the
large pool, the Java pool, and the Streams pool changes without shutting down the instance.
The Oracle database uses initialization parameters to create and configure memory structures.
For example, the SGA_TARGET parameter specifies the total size of the SGA. If you set
SGA_TARGET to 0, Automatic Shared Memory Management is disabled.

How to drop oracle database

In this post we will see how to drop the oracle database.

1.connect sqlplus and shutdown the database.

 Shut immediate;

2.Once db down mount the database with restricted mode.

startup mount restrict;

3.Post that issue drop database command to drop the database.it will remove all db related files and clear the space for that database.

DROP DATABASE;


after drop the database remove the tns entries and listener entries in oracle home.

$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/tnsnames.ora

oracle user and privilege backup before refresh.

While you  refresh dev database from prod the existing schema user password and privileges will be over write with prod data.So you will lose existing user password and privileges. In this post i will show how to take backup of those user password and privileges.

Before doing refresh run the below commands.it will give the ddl statements of user password and their privileges.

1.Take the user password ddl commands using below command.

select 'alter user'||' '|| name ||' '|| 'identified by values' ||' '|| ''''|| password ||''''|| ';' from user$
where name in ('SAMPLE','TEST'); 

2.Take the privileges ddl commands using below commands.


set pages 999;
select 'grant'||' '||PRIVILEGE||' to ' ||grantee||';' from dba_sys_privs where grantee in ('SAMPLE','TEST'); 

select 'grant'||' '||PRIVILEGE||' on ' ||owner||'.'||TABLE_NAME||' to '||grantee||';' from dba_tab_privs where grantee in ('SAMPLE','TEST');

select 'grant'||' '||GRANTED_ROLE||' to ' ||grantee||';' from dba_role_privs where grantee in ('SAMPLE','TEST');

3.Post refresh run those DDL statements to retrieve the old user passwords and privileges.

How to invoke vnc session

Vnc server software is used for invoke GUI display for linux\unix.

Once Vnc server installed on your server i will show how to connect vnc session form your windows using vnc viewer.

1.Login to the server where vnc got installed.

2.Set the vnc password using below command.

  [oracle@TEST1 ~]$ vncpasswd
  Password:
  Verify:

Once you gave vncpasswd command it will ask password.

3.Run the below command for start new vnc session.

[oracle@TEST1 ~]$ vncserver

New 'TEST1:3 (oracle)' desktop is TEST1:3

Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/TEST1:3.log

4.Check the display id using below command.It will show the active seesions for vnc server.

[oracle@TEST1 ~]$ vncserver -list

TigerVNC server sessions:

X DISPLAY #     PROCESS ID
:3              46056
:2              45738

Here we can two vnc sessions are running.Now i will show how to invoke GUI on display id 2.
download vnc viewer software.Once you installed click on vnc viewer.

connection string is servername:displayid.

5.click connect icon it will ask vnc password give the vnc password before you set.

6.Once you provided password it will show your vnc session.

ORA-27300 ORA-27301 ORA-27302 ORA-27157 Database Crash

Database instance crashed due to following errors:

ORA-27300: OS system dependent operation:semctl failed with status: 22
ORA-27301: OS failure message: Invalid argument
ORA-27302: failure occurred at: sskgpwrm1
ORA-27157: OS post/wait facility removed
ORA-27300: OS system dependent operation:semop failed with status: 36
ORA-27301: OS failure message: Identifier removed
ORA-27302: failure occurred at: sskgpwwait1

Cause:

The semaphores used by Oracle have been inadvertendly removed

The errors are signalling that something happened at the OS level with shared memory and/or semaphores. The semaphore sets could be removed manually, or they could be dying for some reason due to a hardware error.

Either when remounting the /dev/shm or You may want to check for any possibility of a user dba using the "ipcrm" command to kill the semaphores (accidentally) since the error ora-27301 (OS failure message: Identifier removed) suggests that. Also, it could have been a bad memory stick or something else at the OS level. Someone could also have removed the shared memory segments at the OS level for some specific reason, or by accident. Most likely something had removed the shared memory and semaphore sets in use by 'oracle'. This can only be done by a root-level user or 'oracle' itself who owns the resources. If someone logged in as root and removed all IPC resources, Oracle would crash when it lost the allocated shared memory/semaphores.

Solution :

In  RHEL7.2 operating system setting RemoveIPC=YES crashes the database.The default value for RemoveIPC in RHEL7.2 is YES.

1) Set RemoveIPC=no in /etc/systemd/logind.conf if it is not in that file

      2) Reboot the server or restart systemd-logind as follows:
       # systemctl daemon-reload
       # systemctl restart systemd-logind