Oracle Database and Instance
The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance.
The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life.
A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.
Database Startup
For any normal user to access the database, the instance has to be started up and it should mount and open the corresponding database.
Database startup includes the following steps:
1. Start an instance.
2. Mount the database.
3. Open the database.
Note:
Database startup requires administrative (SYSDBA) privilege.
To start a database, you need to set the .bash_profile or create envfile or use following steps -
export ORACLE_HOME=/u01/app/oracle/product/11.3.0/db_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:$PATH
$ sqlplus '/as sysdba'
SQL> startup
$ sqlplus '/as sysdba'
SQL> startup pfile=$ORACLE_HOME/dbs/initorcl.ora (It means pfile name with location)
INIT files default location is : $ORACLE_HOME/dbs
Verification:
Here, you can see, in $ORACLE_HOME/dbs directory contains -
orapworcl => Password File
initorcl.ora => PFILE
spfileorcl.ora => SPFILE
Also you can differentiate those Linux command, like
ls - list directory contents
ls -ltr - list with -l long listing format, -t modification time, -r reverse order while sorting. This will not show hidden files.
ls -altr - list all with -l long listing format, -t modification time, -r reverse order while sorting. It will show hidden files also.
Nomount Stage
When you issue a 'Startup' command, this is the first stage. ie, starting up the instance. Here, Oracle will read the parameter file (spfile or pfile) in the default location or in the location specified in the startup command. It will then allocate the memory area (SGA) and starts the background processes. Together we call it as the instance. Please note that no database is associated with the instance at this point.
In above screenshot you can see that Oracle Instance have started and SGA memory is allocated.
Now, I am verifying about background processes -
[oracle@db11g ~]$ ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 Aug10 ? 00:00:03 init [5]
root 2 1 0 Aug10 ? 00:00:00 [migration/0]
root 3 1 0 Aug10 ? 00:00:00 [ksoftirqd/0]
root 4 1 0 Aug10 ? 00:00:00 [watchdog/0]
...
..
.
oracle 5809 5796 0 Aug11 ? 00:00:00 sshd: oracle@pts/1
oracle 5810 5809 0 Aug11 pts/1 00:00:00 -bash
oracle 5850 5810 0 Aug11 pts/1 00:00:00 sqlplus
root 9554 4369 0 02:16 ? 00:00:00 sshd: oracle [priv]
oracle 9561 9554 0 02:16 ? 00:00:01 sshd: oracle@pts/2
oracle 9562 9561 0 02:16 pts/2 00:00:00 -bash
oracle 10715 9562 0 02:57 pts/2 00:00:00 sqlplus
root 10745 4369 0 02:59 ? 00:00:00 sshd: oracle [priv]
oracle 10748 10745 0 02:59 ? 00:00:00 sshd: oracle@pts/3
oracle 10749 10748 0 02:59 pts/3 00:00:00 -bash
oracle 11632 1 0 03:40 ? 00:00:00 ora_pmon_orcl
oracle 11634 1 0 03:40 ? 00:00:00 ora_psp0_orcl
oracle 11637 1 0 03:40 ? 00:00:00 ora_vktm_orcl
oracle 11641 1 0 03:40 ? 00:00:00 ora_gen0_orcl
oracle 11643 1 0 03:40 ? 00:00:00 ora_diag_orcl
oracle 11645 1 0 03:40 ? 00:00:00 ora_dbrm_orcl
oracle 11647 1 0 03:40 ? 00:00:00 ora_dia0_orcl
oracle 11649 1 0 03:40 ? 00:00:00 ora_mman_orcl
oracle 11651 1 0 03:40 ? 00:00:00 ora_dbw0_orcl
oracle 11653 1 0 03:40 ? 00:00:00 ora_lgwr_orcl
oracle 11655 1 0 03:40 ? 00:00:00 ora_ckpt_orcl
oracle 11657 1 0 03:40 ? 00:00:00 ora_smon_orcl
oracle 11659 1 0 03:40 ? 00:00:00 ora_reco_orcl
oracle 11661 1 0 03:40 ? 00:00:00 ora_mmon_orcl
oracle 11663 1 0 03:40 ? 00:00:00 ora_mmnl_orcl
oracle 11665 1 0 03:40 ? 00:00:00 ora_d000_orcl
oracle 11667 1 0 03:40 ? 00:00:00 ora_s000_orcl
oracle 11746 10749 0 03:44 pts/3 00:00:00 ps -ef
[oracle@db11g ~]$ ps -ef | grep smon
oracle 11657 1 0 03:40 ? 00:00:00 ora_smon_orcl
oracle 11762 10749 0 03:44 pts/3 00:00:00 grep smon
[oracle@db11g ~]$
[oracle@db11g ~]$
[oracle@db11g ~]$ ps -ef | grep pmon
oracle 11632 1 0 03:40 ? 00:00:00 ora_pmon_orcl
oracle 11776 10749 0 03:45 pts/3 00:00:00 grep pmon
[oracle@db11g ~]$
In this stage you can't access controlfile and database.
Verification:
SQL> select * from V$DATABASE;
select * from V$DATABASE
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> select * from V$CONTROLFILE;
no rows selected
SQL>
SQL> select * from V$CONTROLFILE_RECORD_SECTION;
select * from V$CONTROLFILE_RECORD_SECTION
*
ERROR at line 1:
ORA-01507: database not mounted
Instance is started but controlfile is not accessible.
SQL> select STATUS from V$INSTANCE;
STATUS
------------
STARTED
SQL>
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
Mount Stage
In this phase the instance will mount the database. Mounting the instance means associating the started instance with a specified database. For this, the instance checks the controlfiles specified under CONTROL_FILES parameter and opens it. It then reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.
The database is still closed and only the DBA can access it.
This stage is normally used for maintenance operations like renaming datafiles, enabling and disabling archiving options. Adding, dropping, or renaming redo log files is also done in mount stage. For performing full database recovery database is opened in mount stage.
To mount a database:
$ sqlplus / as sysdba
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1770434560 bytes
Fixed Size 2229184 bytes
Variable Size 469765184 bytes
Database Buffers 1291845632 bytes
Redo Buffers 6594560 bytes
Database mounted.
SQL>
or
SQL> alter database mount; => If your instance is already started and it is in nomount mode.
Database altered.
Verification:
Controlfile is open but still database is not open for read write but dba can access the database in this mode also.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> SELECT NAME, OPEN_MODE, LOG_MODE FROM V$DATABASE;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
ORCL MOUNTED NOARCHIVELOG
SQL> SELECT * FROM V$CONTROLFILE;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl
NO 16384 594
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
NO 16384 594
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
Now that the Database is MOUNTED, Oracle can read the Control files and knows where all the datafiles are present.
Control file has pointers that provide connection b/w Datafiles, Redo Logs, Archived Logs, Memory and Last Updated SCN.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 1 NO
INACTIVE 1162828 07-AUG-15 1190079 07-AUG-15
3 1 12 52428800 512 1 NO
CURRENT 1211313 11-AUG-15 2.8147E+14
2 1 11 52428800 512 1 NO
INACTIVE 1190079 07-AUG-15 1211313 11-AUG-15
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Current log sequence 12
So from here, we can see that the Database in Mount Mode has all the required parameters initialized,
however the database itself is still not open to users.
MOUNT MODE IS WHERE MAINTENANCE WORK IS DONE.
Open Stage
This is the final stage and here Oracle opens the online datafiles and redolog files. If any of the required files are not present, media recovery is required . It also ensures the datafiles are consistent. Incase of a normal shutdown, the in-memory changes will be written to disk as part of the shutdown checkpoint. But if the instance crashed (or shutdown abort), then Oracle Database performs the instance recovery in the next startup.
If redo threads are marked open in the control file, but no live instances hold the thread enqueues corresponding to these threads, then the database requires instance recovery.
Instance recovery is performed in two steps. ie, rollforward and rollback.
To open a databse:
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> startup open;
or
SQL> startup
Verification:
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> SELECT NAME, OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
Now the database is open for use.
Oracle Database Startup Sequence in Nutshell:
There are different modes in which you can open the database eg: migrate, read only, restricted modes.
Database Shutdown
During a database shutdown we close the database and terminates the instance.
Different Modes in Database Shutdown
There are different modes to bring down the database:
Shutdown immediate
Shutdown transactional
Shutdown normal
Shutdown abort
No user session will be permitted once you issue any of these Shutdown commands.
Shutdown Immediate
Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back. Oracle then performs a checkpoint and then close the online datafiles.
Shutdown Transactional
This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down. Oracle then performs a checkpoint and then close the online datafiles.
Shutdown Normal
The database waits for all connected users to disconnect before shutting down. It waits till all the current transactions end. Oracle then performs a checkpoint and then close the online datafiles.
Shutdown Abort
Oracle Closes the datafiles without any checkpoint. This is the fastest shutdown mode. Instance recovery is required in the next startup and hence it will take time.
The syntax to shutdown is:
$ sqlplus / as sysdba
SQL> shutdown immediate
-- OR --
SQL> shutdown abort
-- OR --
SQL> shutdown transactional
Different Phases in Database Shutdown
Close the Database
Oracle writes the data in the SGA to the disk, updates the file headers and closes the online datafiles and the redo log files. But the database will still be mounted.
Dismount the Database
After the database is closed, Oracle Database unmounts the database to disassociate it from the instance. After a database is unmounted, Oracle Database closes the control files of the database. At this point, the instance remains in memory.
Shutdown the Instance
The last step is to remove the shared memory (SGA) and terminate the background processes.
Sometimes shutdown does not cleanup the SGA or background process completely. This can cause error during the next startup. In such situation we can force a instance startup
$ sqlplus / as sysdba
SQL> startup force
Best practice to bring down the database during a planned outage is
(a) Shutdown the application
(b) Stop DBConsole, if any
(c) Shutdown the database.
You can check for any long running queries before shutting down the database.
Now, if you close the database explicitly, you cannot open the database again using the same instance.
Now the only option will be to terminate the instance and startup the database again You can open only one database and only once using an instance.
Issue#1
Steps for a Clean Startup after an Instance Crash
Sometimes the instance will not start if the previous shutdown was not complete (leaving some shared memory segments or processes). In such cases you can retry a fresh startup after cleaning up the old shared memory segments / semaphores:
a. check if the background processes are running
$ ps -ef | grep $ORACLE_SID
b. If background processes are running remove the background processes:
$ kill -9 ####
c. Check if the shared memory and semaphore are running for your SID:
$ ipcs –a
d. If shared memory and semaphore are in use then remove the shared memory and semaphore:
$ ipcrm -m #### -- for shared memory
$ ipcrm -s #### -- for semaphore
e. Change directory to the "oracle_home/dbs":
$ cd $ORACLE_HOME/dbs
f. Check if the file "lk" and "sgadef.dbf".
g. If files are there, then remove the "lk
$ rm lk
A server reboot will do this cleanup except for the lk
--------xXx--------
Master Note: Overview of Database Startup and Shutdown (Doc ID 1505155.1)
No comments:
Post a Comment