28 | 03 | 2024
Latest Articles
Popular Articles

Administration

Instance Discovery

User Rating:  / 1
PoorBest 

Discovery of the Oracle Instance, starting and stopping the Oracle database.

 

The Oracle vocabulary differentiates the Oracle instance from the Oracle database.
The Oracle database is rather the physical layer; we can discover the physical layout easily.
The Oracle instance is memory plus background processes, we ' l discover the Oracle instance briefly here.
Note this is a summary and that a more complete and better description is available in the Concepts Manual available at http://otn.oracle.com

The instance is about

1. memory
2. background processes

The memory structures are about

1. SGA : System Global area  ( shared memory structure to cache sql cursors, object metadata and data )
2. PGA : Program Global Area ( memory for sorts, joins, session cursor states, ... )


Sizing Memory structures


With recent Oracle releases configuration of instance memory structures is automatically
 
1. With 10G the DBA can use the Automatic Shared Memory Management ( Automatic SGA )
2. With 11G the DBA can use the Automatic Memory Management ( Automatic SGA and PGA )

Traditionally this was done with instance initialization parameters we put in the server parameter file, like

1. shared_pool_size : to size the shared pool ( library cache + dictionary cache )
2. buffer_cache_size : to size the cahed data buffers
3. java_pool_size : to size the java pool
4. large_pool_size : to size the large pool ( shared server )
5. pga_aggregate_target : to size the pga

Some of you might remember very old ones, like

1. sort_area_size
2. sort_area_retained_size
3. hash_area_size
4. bitmap_merge_area_size
5. sort_direct_writes


it is time for some DBA gym now

 

SQL_11G> show release
release 1102000300

SQL_11G> show parameter sga_target

NAME                     TYPE                  VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target                 big integer              0

SQL_11G> show parameter pga_aggregate_target

NAME                     TYPE                  VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_target             big integer              0

SQL> show parameter memory_target

NAME                     TYPE                  VALUE
------------------------------------ -------------------------------- ------------------------------
memory_target                 big integer              600M

SQL_11G> column component format a30

SQL_11G> show parameter memory_target;

NAME                     TYPE                  VALUE
------------------------------------ -------------------------------- ------------------------------
memory_target                 big integer              600M

SQL_11G> select * from v$memory_dynamic_components;

COMPONENT               CURRENT_SIZE     MIN_SIZE     MAX_SIZE USER_SPECIFIED_SIZE        OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME          GRANULE_SIZE
------------------------------ ---------------- ---------------- ---------------- ------------------- ---------------- ------------- --------- ------------------ ----------------
shared pool             251658240    251658240    251658240            0             0 STATIC                  4194304
large pool                8388608      8388608      8388608            0             0 STATIC                  4194304
java pool                33554432     33554432     33554432            0             0 STATIC                  4194304
streams pool              4194304      4194304      4194304            0             0 STATIC                  4194304
SGA Target              411041792    411041792    411041792            0             0 STATIC                  4194304
DEFAULT buffer cache     88080384     88080384     88080384            0             0 INITIALIZING            4194304
KEEP buffer cache               0            0            0            0             0 STATIC                  4194304
RECYCLE buffer cache            0            0            0            0             0 STATIC                  4194304
DEFAULT 2K buffer cache  12582912     12582912     12582912     12582912             0 STATIC                  4194304
DEFAULT 4K buffer cache         0            0            0            0             0 STATIC                  4194304
DEFAULT 8K buffer cache         0            0            0            0             0 STATIC                  4194304
DEFAULT 16K buffer cache        0            0            0            0             0 STATIC                  4194304
DEFAULT 32K buffer cache        0            0            0            0             0 STATIC                  4194304
Shared IO Pool                  0            0            0            0             0 STATIC                  4194304
PGA Target              218103808    218103808    218103808            0             0 STATIC                  4194304
ASM Buffer Cache                0            0            0            0             0 STATIC                  4194304

16 rows selected.

SQL_11G> alter session set nls_date_format='DD/MM/YYYY HH24:Mi';

Session altered.

SQL_11G>  column parameter format a30

SQL_11G> select * from v$memory_resize_ops;

COMPONENT               OPER_TYPE     OPER_MODE PARAMETER              INITIAL_SIZE        TARGET_SIZE       FINAL_SIZE STATUS    START_TIME        END_TIME
------------------------------ ------------- --------- ------------------------------ ---------------- ---------------- ---------------- --------- ---------------- ----------------
shared pool              STATIC               shared_pool_size                  0          251658240        251658240 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
PGA Target               STATIC               pga_aggregate_target              0          218103808        218103808 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
java pool                STATIC               java_pool_size                    0           33554432         33554432 COMPLETE   15/03/2012 12:48 15/03/2012 12:48
streams pool             STATIC               streams_pool_size                 0            4194304          4194304 COMPLETE    15/03/2012 12:48 15/03/2012 12:48
SGA Target               STATIC               sga_target                        0          411041792        411041792 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT buffer cache     INITIALIZING         db_cache_size              88080384           88080384         88080384 COMPLETE   15/03/2012 12:48 15/03/2012 12:48
ASM Buffer Cache         STATIC               db_cache_size                     0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT buffer cache     STATIC               db_cache_size                     0           88080384         88080384 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT 2K buffer cache  STATIC               db_2k_cache_size                  0           12582912         12582912 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT 4K buffer cache  STATIC               db_4k_cache_size                  0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT 8K buffer cache  STATIC               db_8k_cache_size                  0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT 16K buffer cache STATIC               db_16k_cache_size                 0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
DEFAULT 32K buffer cache STATIC               db_32k_cache_size                 0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
KEEP buffer cache        STATIC               db_keep_cache_size                0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
RECYCLE buffer cache     STATIC               db_recycle_cache_size             0              0                    0 COMPLETE  15/03/2012 12:48 15/03/2012 12:48
large pool               STATIC               large_pool_size                   0        8388608              8388608 COMPLETE  15/03/2012 12:48 15/03/2012 12:48

16 rows selected.

 

The background processes are started when the instance is started, ie whe the database is in nomount stage.

 

SQL> select a.spid,b.name from v$process a,v$bgprocess b where a.addr = b.paddr and b.paddr != '00';

SPID   NAME
------ ------
8593   PMON
8595   PSP0
8597   VKTM
8601   GEN0
8603   DIAG
8605   DBRM
8607   DIA0
8609   MMAN
8611   DBW0
8613   LGWR
8615   CKPT
8617   SMON
8619   RECO
8621   MMON
8623   MMNL

15 rows selected.


On unix alike platforms we can easily list the Oracle processes

 

 

 

SQL 10G>show release
release 1002000400

SQL 10G>column NAME_COL_PLUS_SHOW_PARAM format a30
SQL 10G>column VALUE_COL_PLUS_SHOW_PARAM format a30

SQL 10G>show parameter sga_target

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
sga_target                     big integer 1408M

SQL 10G>show parameter pga_aggregate_target

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
pga_aggregate_target           big integer 1G

SQL 10G>show parameter shared_pool_size

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
shared_pool_size               big integer 0

SQL 10G>show parameter db_cache_size

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
db_cache_size                  big integer 0

SQL 10G>show parameter large_pool_size

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
large_pool_size                big integer 0

SQL 10G>show parameter java_pool_size

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
java_pool_size                 big integer 0

 

SQL 10G>column component format a30

SQL 10G>select * from v$sga_dynamic_components;

COMPONENT                          CURRENT_SIZE         MIN_SIZE         MAX_SIZE USER_SPECIFIED_SIZE       OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME       GRANULE_SIZE
------------------------------ ---------------- ---------------- ---------------- ------------------- ---------------- ------------- --------- ---------------- ----------------
shared pool                           452984832        452984832                0                   0             4094 SHRINK        DEFERRED  15/03/2012 12:00         16777216
large pool                             16777216         16777216                0                   0                0 STATIC                                           16777216
java pool                              16777216         16777216                0                   0                0 STATIC                                           16777216
streams pool                                  0                0                0                   0                0 STATIC                                           16777216
DEFAULT buffer cache                  973078528        905969664                0                   0             4094 GROW          DEFERRED  15/03/2012 12:00         16777216
KEEP buffer cache                             0                0                0                   0                0 STATIC                                           16777216
RECYCLE buffer cache                          0                0                0                   0                0 STATIC                                           16777216
DEFAULT 2K buffer cache                       0                0                0                   0                0 STATIC                                           16777216
DEFAULT 4K buffer cache                       0                0                0                   0                0 STATIC                                           16777216
DEFAULT 8K buffer cache                       0                0                0                   0                0 STATIC                                           16777216
DEFAULT 16K buffer cache                      0                0                0                   0                0 STATIC                                           16777216
DEFAULT 32K buffer cache                      0                0                0                   0                0 STATIC                                           16777216
ASM Buffer Cache                              0                0                0                   0                0 STATIC                                           16777216

13 rows selected.

SQL 10G>select * from v$sga_resize_ops;

DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                        1006632960       1023410176       1023410176 COMPLETE  15/03/2012 06:00 15/03/2012 06:00
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                        1023410176       1006632960       1006632960 COMPLETE  15/03/2012 06:01 15/03/2012 06:01
shared pool                    GROW          DEFERRED  shared_pool_size                      402653184        419430400        419430400 COMPLETE  15/03/2012 06:01 15/03/2012 06:01
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                        1006632960        989855744        989855744 COMPLETE  15/03/2012 06:04 15/03/2012 06:04
shared pool                    GROW          DEFERRED  shared_pool_size                      419430400        436207616        436207616 COMPLETE  15/03/2012 06:04 15/03/2012 06:04
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                         989855744        973078528        973078528 COMPLETE  15/03/2012 06:15 15/03/2012 06:15
shared pool                    GROW          DEFERRED  shared_pool_size                      436207616        452984832        452984832 COMPLETE  15/03/2012 06:15 15/03/2012 06:15
shared pool                    SHRINK        DEFERRED  shared_pool_size                      452984832        436207616        436207616 COMPLETE  15/03/2012 07:01 15/03/2012 07:01
DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                         973078528        989855744        989855744 COMPLETE  15/03/2012 07:01 15/03/2012 07:01
shared pool                    SHRINK        DEFERRED  shared_pool_size                      436207616        419430400        419430400 COMPLETE  15/03/2012 07:05 15/03/2012 07:05
DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                         989855744       1006632960       1006632960 COMPLETE  15/03/2012 07:05 15/03/2012 07:05
shared pool                    SHRINK        DEFERRED  shared_pool_size                      419430400        402653184        402653184 COMPLETE  15/03/2012 07:09 15/03/2012 07:09
DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                        1006632960       1023410176       1023410176 COMPLETE  15/03/2012 07:09 15/03/2012 07:09
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                        1023410176       1006632960       1006632960 COMPLETE  15/03/2012 07:19 15/03/2012 07:19
shared pool                    GROW          DEFERRED  shared_pool_size                      402653184        419430400        419430400 COMPLETE  15/03/2012 07:19 15/03/2012 07:19
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                        1006632960        989855744        989855744 COMPLETE  15/03/2012 07:26 15/03/2012 07:26
shared pool                    GROW          DEFERRED  shared_pool_size                      419430400        436207616        436207616 COMPLETE  15/03/2012 07:26 15/03/2012 07:26
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                         989855744        973078528        973078528 COMPLETE  15/03/2012 08:01 15/03/2012 08:01
shared pool                    GROW          DEFERRED  shared_pool_size                      436207616        452984832        452984832 COMPLETE  15/03/2012 08:01 15/03/2012 08:01
DEFAULT buffer cache           SHRINK        DEFERRED  db_cache_size                         973078528        956301312        956301312 COMPLETE  15/03/2012 10:03 15/03/2012 10:03
shared pool                    GROW          DEFERRED  shared_pool_size                      452984832        469762048        469762048 COMPLETE  15/03/2012 10:03 15/03/2012 10:03
shared pool                    SHRINK        DEFERRED  shared_pool_size                      469762048        452984832        452984832 COMPLETE  15/03/2012 12:00 15/03/2012 12:00
DEFAULT buffer cache           GROW          DEFERRED  db_cache_size                         956301312        973078528        973078528 COMPLETE  15/03/2012 12:00 15/03/2012 12:00

 

Starting the Oracle Instance

 

SQL> startup nomount;
ORACLE instance started.

Total System Global Area    626327552 bytes
Fixed Size                    2230952 bytes
Variable Size               515900760 bytes
Database Buffers            100663296 bytes
Redo Buffers                  7532544 bytes

 

Mounting the Oracle database

 

[oracle@pcguy ~]$ sqlplus / as sysdba

SQL> alter database mount;

Database altered.


Open the Oracle database

 
[oracle@pcguy ~]$ sqlplus / as sysdba

SQL> alter database open;

Database altered.

 

Database in read Only Mode versus Read Write mode

 

SQL> startup;

ORACLE instance started.

Total System Global Area    626327552 bytes
Fixed Size                    2230952 bytes
Variable Size               515900760 bytes
Database Buffers            100663296 bytes
Redo Buffers                  7532544 bytes
Database mounted.

Database opened.

SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:Mi';

Session altered.

SQL> select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          ALLOWED     NO  ACTIVE           NORMAL

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

 

SQL> shutdown immediate;

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area    626327552 bytes
Fixed Size                    2230952 bytes
Variable Size               515900760 bytes
Database Buffers            100663296 bytes
Redo Buffers                  7532544 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

 

Restricted session state

 
For maintenance operations ( Only use the below commands when you are fully aware of the business impact )


SQL> alter system enable restricted session;

System altered.

SQL> select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          RESTRICTED NO  ACTIVE           NORMAL

SQL> alter system disable restricted session;

System altered.

SQL> select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          ALLOWED     NO  ACTIVE           NORMAL

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

SQL> startup restrict;
ORACLE instance started.

Total System Global Area    626327552 bytes
Fixed Size                    2230952 bytes
Variable Size               515900760 bytes
Database Buffers            100663296 bytes
Redo Buffers                  7532544 bytes
Database mounted.
Database opened.

SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:Mi';

Session altered.

SQL> select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATUS
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 15:12 OPEN          RESTRICTED NO  ACTIVE           NORMAL

SQL> alter system disable restricted session;

System altered.

 


Suspended Database state


Suspends all the IO, for disk snapshotting purposes. ( Only use the below commands when you are fully aware of the business impact )


SQL> alter system suspend;

System altered.

SQL>  select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          ALLOWED     NO  SUSPENDED           NORMAL

SQL> alter system resume;

System altered.

SQL> select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          ALLOWED     NO  ACTIVE           NORMAL


Instance in Quiesce state


For maintenance operations ( Only use the below commands when you are fully aware of the business impact )


SQL> alter system quiesce restricted;

System altered.

SQL> select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          ALLOWED     NO  ACTIVE           QUIESCED

SQL>  alter system unquiesce;

System altered.

SQL>  select startup_time,status,logins,shutdown_pending,database_status,active_state from v$instance;

STARTUP_TIME     STATUS       LOGINS     SHU DATABASE_STATUS   ACTIVE_STATE
---------------- ------------ ---------- --- ----------------- ---------
15/03/2012 14:59 OPEN          ALLOWED     NO  ACTIVE           NORMAL

 

Sometimes the database administrator needs the below listed commands ( Only use the below commands when you are fully aware of the business impact )


alter database open;
alter database open read write; { alter database open }
alter database open read only;
alter database open upgrade;
alter database open resetlogs; ( after incomplete, cancel based recovery ) -- only use if really needed
alter database open resetlogs upgrade; ( after incomplete, cancel based recovery + open for upgrade ) -- only use if really needed
alter database open resetlogs downgrade;  ( after incomplete, cancel based recovery + open for downgrade ) -- only use if really needed

startup;

startup nomount;

startup mount;

startup restrict;

shutdown immediate;
shutdown normal;
shutdown transactional;
shutdown abort;

startup force;
startup force mount;
startup force mount exclusive restrict;

startup upgrade; ( startup mount + alter database open upgrade )

alter system enable restricted session;
alter system disable restricted session;
alter system suspend;
alter system resume;
alter system quiesce restricted;
alter system unquiesce;