In this blog post we will learn Startup Modes in Oracle Database
There are 3 states in which Oracle instance can be started i.e nomount, mount and open. Remember you can go directory to open from scratch i.e when database is shutdown and you can also go directly to mount and as always you can go to mount from nomount or you can go to open from mount as well. Its like climbing stairs in order.
1) First mode is nomount, in this mode only spfile/parameter is read and Oracle instance SGA memory is allocated :- Lets see :
Verify database instance is down :
[oracle@node2 ~]$ ps -ef | grep smon
oracle 3597 2134 0 11:49 pts/0 00:00:00 grep --color=auto smon
Now lets start instance in nomount mode first as it is the first stage,
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Sun May 28 11:49:10 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073740680 bytes
Fixed Size 9694088 bytes
Variable Size 469762048 bytes
Database Buffers 591396864 bytes
Redo Buffers 2887680 bytes
SQL> select status from v$instance;
STATUS
------------
STARTED
#Here started means nomount mode
2) Now lets go to mount mode :
In this mode controlfile of Oracle is read but physical existence of datafiles is not checked.
SQL> alter database mount;
Database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
3) In open mode datafiles existence is also verified and if they need recovery these things are checked during mount to open phase.
SQL> alter database open;
Database altered.
SQL> SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
Now note this is read write mode, means application can do writes and reads a typical mode of Oracle primady database, you can also open in read only mode the instance instead of read write. Lets see how we can do but note you need to shutdown first the database in order to come back to read only. Lets see if we can try and do it :
SQL> SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01531: a database already open by the instance
# Lets shutdown now :
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
# Startup now to mount mode , because you cannot alter database from nomount to open mode, lets see :
ORACLE instance shut down.
SQL> SQL>
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073740680 bytes
Fixed Size 9694088 bytes
Variable Size 469762048 bytes
Database Buffers 591396864 bytes
Redo Buffers 2887680 bytes
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ ONLY
# Also with a single short cut startup force you can shutdown abort and startup in read write mode in a single shot , whichever mode you are currently in , lets see we can do it :
SQL> startup force
ORACLE instance started.
Total System Global Area 1073740680 bytes
Fixed Size 9694088 bytes
Variable Size 469762048 bytes
Database Buffers 591396864 bytes
Redo Buffers 2887680 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
Alert log contents :
SQL> select value from v$diag_info;
VALUE
--------------------------------------------------------------------------------
TRUE
/u01/app/oracle
/u01/app/oracle/diag/rdbms/orcl/orcl
/u01/app/oracle/diag/rdbms/orcl/orcl/trace
/u01/app/oracle/diag/rdbms/orcl/orcl/alert
/u01/app/oracle/diag/rdbms/orcl/orcl/incident
/u01/app/oracle/diag/rdbms/orcl/orcl/cdump
/u01/app/oracle/diag/rdbms/orcl/orcl/hm
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4693.trc
0
0
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/21.3.0
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/attention_orcl.log
13 rows selected.
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@node2 ~]$ tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
2023-05-28T12:10:24.990363+05:30
CJQ0 started with pid=66, OS id=4991
Completed: ALTER DATABASE OPEN
2023-05-28T12:10:28.316040+05:30
Setting Resource Manager plan SCHEDULER[0x52C4]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
PDB1(3):Setting Resource Manager plan SCHEDULER[0x52C1]:DEFAULT_MAINTENANCE_PLAN via scheduler window
PDB1(3):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2023-05-28T12:10:43.793248+05:30
Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c4] because plan is already set.
^C
[oracle@node2 ~]$ tail -200f /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
2023-05-28T12:09:01.079236+05:30
ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCL'
Starting background process PMON
2023-05-28T12:09:01.087835+05:30
PMON started with pid=2, OS id=4579
Starting background process CLMN
2023-05-28T12:09:01.096427+05:30
CLMN started with pid=3, OS id=4583
Starting background process PSP0
2023-05-28T12:09:01.105050+05:30
PSP0 started with pid=4, OS id=4587
Starting background process VKTM
2023-05-28T12:09:01.114083+05:30
VKTM started with pid=5, OS id=4591 at elevated (RT) priority
2023-05-28T12:09:01.114836+05:30
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
2023-05-28T12:09:01.122772+05:30
GEN0 started with pid=6, OS id=4597
Starting background process MMAN
2023-05-28T12:09:01.131288+05:30
MMAN started with pid=7, OS id=4601
Starting background process GEN1
Starting background process GEN2
Starting background process VOSD
Starting background process DIAG
Starting background process OFSD
Starting background process DBRM
Starting background process VKRM
Starting background process SVCB
Starting background process PMAN
Starting background process DIA0
Starting background process DBW0
Starting background process LGWR
Starting background process CKPT
Starting background process SMON
Starting background process SMCO
Starting background process RECO
Starting background process LREG
Starting background process PXMN
Starting background process FENC
Starting background process MMON
Starting background process MMNL
2023-05-28T12:09:01.189362+05:30
GEN1 started with pid=9, OS id=4607_4609
2023-05-28T12:09:01.234103+05:30
GEN2 started with pid=11, OS id=4610
2023-05-28T12:09:01.260151+05:30
DIAG started with pid=13, OS id=4617
2023-05-28T12:09:01.262894+05:30
VOSD started with pid=12, OS id=4613
2023-05-28T12:09:01.304813+05:30
OFSD started with pid=14, OS id=4620_4622
2023-05-28T12:09:01.306459+05:30
Oracle running with ofslib:'Oracle File Server Library' version=2 inc=1
2023-05-28T12:09:01.349021+05:30
VKRM started with pid=17, OS id=4626
2023-05-28T12:09:01.350636+05:30
DBRM started with pid=16, OS id=4623
2023-05-28T12:09:01.377093+05:30
SVCB started with pid=18, OS id=4629
2023-05-28T12:09:01.435357+05:30
DIA0 started with pid=20, OS id=4634
2023-05-28T12:09:01.439370+05:30
PMAN started with pid=19, OS id=4631
2023-05-28T12:09:01.464040+05:30
DBW0 started with pid=21, OS id=4637
2023-05-28T12:09:01.479582+05:30
CKPT started with pid=23, OS id=4644
2023-05-28T12:09:01.481077+05:30
LGWR started with pid=22, OS id=4640 at elevated (RT) priority
2023-05-28T12:09:01.489370+05:30
SMCO started with pid=25, OS id=4654
2023-05-28T12:09:01.489751+05:30
SMON started with pid=24, OS id=4649
2023-05-28T12:09:01.497269+05:30
RECO started with pid=26, OS id=4657
2023-05-28T12:09:01.510576+05:30
PXMN started with pid=28, OS id=4662
2023-05-28T12:09:01.512162+05:30
LREG started with pid=27, OS id=4659
2023-05-28T12:09:01.526890+05:30
MMON started with pid=30, OS id=4668
2023-05-28T12:09:01.528532+05:30
FENC started with pid=29, OS id=4665
2023-05-28T12:09:01.542807+05:30
MMNL started with pid=31, OS id=4671
2023-05-28T12:09:01.543530+05:30
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
Starting background process TMON
2023-05-28T12:09:01.605236+05:30
TMON started with pid=42, OS id=4691
RACD notifier running level=0
ORACLE_BASE from environment = /u01/app/oracle
2023-05-28T12:09:01.652008+05:30
ALTER DATABASE MOUNT
2023-05-28T12:09:01.666269+05:30
Successful mount of redo thread 1, with mount id 1665802461
2023-05-28T12:09:01.666479+05:30
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
2023-05-28T12:09:01.725771+05:30
ALTER DATABASE OPEN
Smart fusion block transfer is disabled:
instance mounted in exclusive mode.
2023-05-28T12:09:01.729878+05:30
Crash Recovery excluding pdb 2 which was cleanly closed.
2023-05-28T12:09:01.729920+05:30
Crash Recovery excluding pdb 3 which was cleanly closed.
Endian type of dictionary set to little
Starting background process ARC0
2023-05-28T12:09:01.740515+05:30
ARC0 started with pid=44, OS id=4695
2023-05-28T12:09:01.752033+05:30
Redo log for group 3, sequence 21 is not located on DAX storage
Thread 1 opened at log sequence 21
Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ORCL/redo03.log
Successful open of redo thread 1
2023-05-28T12:09:01.755723+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Starting background process ARC1
2023-05-28T12:09:01.838258+05:30
ARC1 started with pid=48, OS id=4703
Starting background process ARC2
Undo initialization recovery: Parallel FPTR complete: start:2097012 end:2097013 diff:1 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 2097012 end: 2097013 diff: 1 ms (0.0 seconds)
2023-05-28T12:09:01.873465+05:30
ARC2 started with pid=49, OS id=4705
Starting background process ARC3
2023-05-28T12:09:01.917247+05:30
ARC3 started with pid=50, OS id=4707
[4693] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 2097013 end: 2097094 diff: 81 ms (0.1 seconds)
Undo initialization finished serial:0 start:2097012 end:2097099 diff:87 ms (0.1 seconds)
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process RCBG
2023-05-28T12:09:02.307179+05:30
RCBG started with pid=52, OS id=4711
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
2023-05-28T12:09:02.522697+05:30
AQPC started with pid=53, OS id=4713
PDB$SEED(2):Pluggable database PDB$SEED opening in read only
PDB$SEED(2):Autotune of undo retention is turned on.
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:2097868 end:2097868 diff:0 ms (0.0 seconds)
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2023-05-28T12:09:03.056673+05:30
PDB$SEED(2):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)
PDB$SEED(2):Opening pdb with no Resource Manager plan active
PDB1(3):Pluggable database PDB1 opening in read write
PDB1(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):Undo initialization recovery: Parallel FPTR complete: start:2098719 end:2098722 diff:3 ms (0.0 seconds)
PDB1(3):Undo initialization recovery: err:0 start: 2098719 end: 2098723 diff: 4 ms (0.0 seconds)
PDB1(3):[4693] Successfully onlined Undo Tablespace 2.
PDB1(3):Undo initialization online undo segments: err:0 start: 2098723 end: 2098868 diff: 145 ms (0.1 seconds)
PDB1(3):Undo initialization finished serial:0 start:2098719 end:2098874 diff:155 ms (0.2 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2023-05-28T12:09:04.092265+05:30
PDB1(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2023-05-28T12:09:04.107703+05:30
:
QPI: opatch file present, opatch
:
QPI: qopiprep.bat file present
2023-05-28T12:09:04.357949+05:30
Using default pga_aggregate_limit of 2048 MB
2023-05-28T12:09:04.601138+05:30
PDB1(3):Opening pdb with no Resource Manager plan active
Pluggable database PDB1 opened read write
2023-05-28T12:09:06.842646+05:30
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2023-05-28T12:09:07.447556+05:30
db_recovery_file_dest_size of 13896 MB is 34.33% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2023-05-28T12:10:24.982897+05:30
Starting background process CJQ0
2023-05-28T12:10:24.990363+05:30
CJQ0 started with pid=66, OS id=4991
Completed: ALTER DATABASE OPEN
2023-05-28T12:10:28.316040+05:30
Setting Resource Manager plan SCHEDULER[0x52C4]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
PDB1(3):Setting Resource Manager plan SCHEDULER[0x52C1]:DEFAULT_MAINTENANCE_PLAN via scheduler window
PDB1(3):Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2023-05-28T12:10:43.793248+05:30
Skipping local setting of Resource Manager plan DEFAULT_MAINTENANCE_PLAN via scheduler window [52c4] because plan is already set.
# You learnt the 3 modes of Oracle in this post.
No comments:
Post a Comment