How to shutdown an oracle database?

There are four methods used to shutdown an oracle database.

  • normal (will wait for every session to close before shutdown, very slow)
  • transactional (will wait for every transaction to finish before shutdown, slow but safe)
  • immediate (will terminate every session / transaction and shuts down, fast)
  • abort ( requires instance recovery on startup, slow startup)

 

Example:


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

 

Shutting Down a database goes through three steps:

 

Step 1: Close the database

Step 2: Unmount the database

Step 3: Shutdown the instance

How to start up an oracle database?

To start up an Oracle database, you need to make sure you have necessary privileges (i.e. logged in as sysdba or sysoper) and then type:

SQL> startup

The above command is enough to start up a database, However, if you need to understand the underlying process of start up read on…

You can startup the database in several modes such as:

  • Restricted ( i.e. Use: startup restrict) which restricts access to users with sufficient privileges
  • suspended ( i.e. Use: startup suspend) which disables changes to database

Step 1 (unmounted database): start up an oracle instance

Oracle tries to find spfileSID.ora, then spfile.ora then initSID.ora. These files define the oracle instance parameters and can be modified dynamically through sqlplus.

if no files are found, starting an oracle instance will fail

To show the default parameter spfile, use the following command

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:APPPRODUCT11.2.0
                                                 DBHOME_2DATABASESPFILEORCL.
                                                 ORA
SQL>

 

Step 2: Database Mounted, Instance is associated with Database

Step 3: Database is opened and is available to users.

 

how to select, alter or drop database logfile in sqlplus?

To select the redo log files in your database, use tables v$logfile and v$log.

The logfiles are organized into groups which are active in a sequential manner. When a log file is full, it is archived and then its status is automatically set to inactive and accordingly the status of the next group will be set to current and new changes will be recorded in the current logfile.


SQL> select group#, member from v$logfile;

GROUP# MEMBER
---------- ---------------------------------------

3 C:APPORADATAORCLREDO03.LOG
2 C:APPORADATAORCLREDO02.LOG
1 C:APPORADATAORCLREDO01.LOG

SQL> select group#, sequence#, archived, status, bytes/1024/1024 "MB" from v$log;

GROUP# SEQUENCE# ARC STATUS  MB
———- ———- — —————- ———-
1               10        NO           CURRENT       50
2               8          NO           INACTIVE      50
3               9          NO           INACTIVE      50

 

For redundancy purposes, you may add new mirror logfiles to the existing logfiles using the SQL statements below.


alter database

SQL> add logfile member 'pathtoyour2ndmemberREDO01b.log' to group 1;

Database altered

SQL> add logfile member 'pathtoyour2ndmemberREDO02b.log' to group 2;

Database altered

SQL> add logfile member 'pathtoyour2ndmemberREDO03b.log' to group 3;

Database altered

SQL> alter system switch logfile;
system altered

SQL> /

system altered

SQL> /

system altered

To drop a database logfile, use the following:

alter database drop logfile group 3

How to check where my oracle database files are?

Oracle database files are stored in binary files having extension *.dbf.

In order to find where these files are stored, and what is the size of these files, you need to query the table daba_data_files.

 

SQL> select bytes/1024/1024 "MB", file_name from dba_data_files;

MB         FILE_NAME
--------- -----------------------------------------------------

5           C:APPORADATAORCLUSERS01.DBF
55          C:APPORADATAORCLUNDOTBS01.DBF
510         C:APPORADATAORCLSYSAUX01.DBF
680         C:APPORADATAORCLSYSTEM01.DBF
100         C:APPORADATAORCLEXAMPLE01.DBF


 

To check when these files were created, you may query the table v$datafile

SQL> select creation_time, name from v$datafile;

CREATION_ NAME
--------- --------------------------------------------

30-MAR-10 C:APPORADATAORCLSYSTEM01.DBF
30-MAR-10 C:APPORADATAORCLSYSAUX01.DBF
30-MAR-10 C:APPORADATAORCLUNDOTBS01.DBF
30-MAR-10 C:APPORADATAORCLUSERS01.DBF
06-NOV-11 C:APPORADATAORCLEXAMPLE01.DBF


 

Using the SQLPLUS buffer

While logged in to sqlplus, you may find the following useful:

  • use the forward slash to repeat the last executed query
  • /

  • use l or list to view the last executed SQL statement
  • l
    list

  • use the change command to modify the last executed sql statement
  • c/initial_word/ReplaceBy_word

  • Change one line of the sql statement
  • 2 order by colname3, colname4

  • Use your default editor to change the last sql statement.
  • ed

  • Save the last used sql statement
  • save myfile.sql

  • Executing an SQL file
  • @myfile

How to check my oracle connection parameters?

If you are already connected to oracle through sqlplus, type define, and you will know what is the connect_identifier, user, pervilages, oracle version….

SQL> define

You should receive something similar to this


DEFINE _DATE = "06-NOV-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.
1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1102000100" (CHAR)

How to check what is my default database?

If you have more than one database installed on your server, the default database name which sqlplus or any application trying to access a database on a local machine or a remote server is stored in an environment variable called oracle_sid.

In order to know what is the oracle_sid environment variable,

Step1: go to a windows command prompt or Linux shell

Step 2: Type the following command and press Enter

On windows type:

echo %oracle_sid%

on unix/linux type:

echo $ORACLE_SID

Oracle Identifier will be returned, for example:

orcl