Administration
Instance Discovery
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Friday, 16 March 2012 11:12
-
Last Updated on Tuesday, 04 September 2012 13:59
-
Published on Friday, 16 March 2012 11:12
-
Written by Guy Lambregts
-
Hits: 4575
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;