12,673
回編集
差分
ナビゲーションに移動
検索に移動
ページの作成:「==Oracle 10g データベースの起動と停止== [Oracle Database10g] {{amazon|479810910X}} http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/serve…」
==Oracle 10g データベースの起動と停止==
[Oracle Database10g]
{{amazon|479810910X}}
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19224-02/start.htm#178896
===起動===
====概要====
[[File:0832_ora_start.jpg]]
=====コマンド=====
start [ open pfile=PFILE名] [nomount | mount ]
*デフォルトは open
*pfileを省略すると、以下の優先順にて初期化パラメータファイルが読み込まれる
<blockquote>1. spfile[SID].ora</blockquote>
<blockquote>2. spfile.ora</blockquote>
<blockquote>3. init[SID].ora</blockquote>
*初期化パラメータファイルが格納されているデフォルトディレクトリは $ORACLE_HOME/dbs
====アラートログの場所を調べる====
SQL> select name,value from v$parameter where name = 'background_dump_dest';
NAME VALUE
------------------------ --------------------------------------
background_dump_dest /opt/oracle/app/admin/oradb1/bdump
====NOMOUNT====
=====SQL Plus =====
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
=====アラートログ=====
Sat Feb 7 08:22:22 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 75497472
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
nls_language = JAPANESE
nls_territory = JAPAN
sga_target = 167772160
control_files = /opt/oracle/app/oradata/oradb1/control01.ctl, /opt/oracle/app/oradat
a/oradb1/control02.ctl, /opt/oracle/app/oradata/oradb1/control03.ctl
db_block_size = 8192
__db_cache_size = 79691776
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = /opt/oracle/app/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain = vboxfedora10.typea.info
dispatchers = (PROTOCOL=TCP) (SERVICE=oradb1XDB)
job_queue_processes = 10
background_dump_dest = /opt/oracle/app/admin/oradb1/bdump
user_dump_dest = /opt/oracle/app/admin/oradb1/udump
core_dump_dest = /opt/oracle/app/admin/oradb1/cdump
audit_file_dest = /opt/oracle/app/admin/oradb1/adump
db_name = oradb1
open_cursors = 300
pga_aggregate_target = 16777216
PMON started with pid=2, OS id=6024
PSP0 started with pid=3, OS id=6026
MMAN started with pid=4, OS id=6028
DBW0 started with pid=5, OS id=6030
LGWR started with pid=6, OS id=6032
CKPT started with pid=7, OS id=6034
====MOUNT====
=====SQL Plus=====
SQL> alter database mount;
Database altered.
=====アラートログ=====
Sat Feb 7 08:22:24 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=6044
Sat Feb 7 08:22:24 2009
starting up 1 shared server(s) ...
Sat Feb 7 09:11:23 2009
alter database mount
Sat Feb 7 09:11:27 2009
Setting recovery target incarnation to 2
Sat Feb 7 09:11:27 2009
Successful mount of redo thread 1, with mount id 2023396219
Sat Feb 7 09:11:27 2009
Database mounted in Exclusive Mode
Completed: alter database mount
====OPEN====
=====SQL Plus=====
SQL> alter database open;
Database altered.
=====アラートログ=====
Sat Feb 7 09:12:55 2009
alter database open
Sat Feb 7 09:12:55 2009
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=7423
Sat Feb 7 09:12:55 2009
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=7425
Sat Feb 7 09:12:55 2009
ARC0: STARTING ARCH PROCESSES
Sat Feb 7 09:12:55 2009
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Sat Feb 7 09:12:55 2009
Thread 1 opened at log sequence 33
Current log# 2 seq# 33 mem# 0: /opt/oracle/app/oradata/oradb1/redo02.log
Successful open of redo thread 1
Sat Feb 7 09:12:55 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Feb 7 09:12:55 2009
SMON: enabling cache recovery
Sat Feb 7 09:12:55 2009
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=7427
Sat Feb 7 09:12:57 2009
Successfully onlined Undo Tablespace 1.
Sat Feb 7 09:12:57 2009
SMON: enabling tx recovery
Sat Feb 7 09:12:57 2009
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=7443
Sat Feb 7 09:13:05 2009
db_recovery_file_dest_size of 2048 MB is 2.43% 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.
Sat Feb 7 09:13:06 2009
Completed: alter database open
(END)
====アクセスを制限した起動====
*インスタンスの使用を管理担当者にのみ許可し、一般データベース・ユーザーの使用を禁止
=====RESTRICT=====
startup restrict [ open nomount | mount ]
=====SQL Plus=====
SQL> startup restrict;
ORACLE instance started.
=====アラートログ=====
Sat Feb 7 20:13:27 2009
Starting ORACLE instance (restrict)
:
=====他ユーザでログイン=====
SQL> conn oratest/****
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
===停止===
====SHUTDOWN====
shutdown [ abort normal | transactional | immediate ]
*データベースとインスタンスを停止するには、最初にSYSOPERまたはSYSDBAとして接続する必要
*一部の停止モードでは、実際にデータベースを停止する前に、特定のイベント（トランザクションの完了またはユーザーによる切断など）の発生を待機します。これらのイベントに対するタイムアウト間隔は1時間
**停止をブロックしているイベントすべてが1時間以内に発生しない場合、停止コマンドは次のメッセージを表示して取り消されます。ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました。
=====停止モード=====
*デフォルトの停止モードはNORMAL
{|class="wikitable"
!停止モード
!内容
!新規接続
!セッション
!トランザクション
!次回インスタンス・リカバリ
|-
|NORMAL
|通常の停止
|不可
|切断待機
|完了待機
|不要
|-
|TRANSACTIONAL
|アクティブトランザクションを完了し、予定どおりにインスタンスを停止
|不可
|切断
|完了待機
|不要
|-
|IMMEDIATE
|即時停止
|不可
|切断
|暗黙的にロールバック
|不要
|-
|ABORT
|強制終了
|不可
|強制終了
|強制終了
|必要
|-
|}
=====ABORTからインスタンス・リカバリ=====
*SQL Plus から SQLを発行
SQL> delete from httpd_access_log where seq = 7956;
1 row deleted.
*コミット前に、SHUTDOWN ABORT(アラートログ)
Sun Feb 8 02:24:06 2009
Shutting down instance (abort)
License high water mark = 12
Instance terminated by USER, pid = 32707
*SQL Plusからコミット(失敗する)
SQL> commit;
commit
*
ERROR at line 1:
ORA-03135: connection lost contact
*再起動(STARTUP アラートログ リカバリが実行されている)
:
ALTER DATABASE OPEN
Sun Feb 8 02:25:23 2009
Beginning crash recovery of 1 threads
Sun Feb 8 02:25:23 2009
Started redo scan
Sun Feb 8 02:25:23 2009
Completed redo scan
1339 redo blocks read, 169 data blocks need recovery
Sun Feb 8 02:25:24 2009
Started redo application at
Thread 1: logseq 33, block 65010
Sun Feb 8 02:25:24 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 33 Reading mem 0
Mem# 0 errs 0: /opt/oracle/app/oradata/oradb1/redo02.log
Sun Feb 8 02:25:24 2009
Completed redo application
Sun Feb 8 02:25:24 2009
Completed crash recovery at
Thread 1: logseq 33, block 66349, scn 2049378
169 data blocks read, 169 data blocks written, 1339 redo blocks read
:
Completed: ALTER DATABASE OPEN
{{include_html banner_html, "!Oracle"}}
[Oracle Database10g]
{{amazon|479810910X}}
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19224-02/start.htm#178896
===起動===
====概要====
[[File:0832_ora_start.jpg]]
=====コマンド=====
start [ open pfile=PFILE名] [nomount | mount ]
*デフォルトは open
*pfileを省略すると、以下の優先順にて初期化パラメータファイルが読み込まれる
<blockquote>1. spfile[SID].ora</blockquote>
<blockquote>2. spfile.ora</blockquote>
<blockquote>3. init[SID].ora</blockquote>
*初期化パラメータファイルが格納されているデフォルトディレクトリは $ORACLE_HOME/dbs
====アラートログの場所を調べる====
SQL> select name,value from v$parameter where name = 'background_dump_dest';
NAME VALUE
------------------------ --------------------------------------
background_dump_dest /opt/oracle/app/admin/oradb1/bdump
====NOMOUNT====
=====SQL Plus =====
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
=====アラートログ=====
Sat Feb 7 08:22:22 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
__shared_pool_size = 75497472
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
nls_language = JAPANESE
nls_territory = JAPAN
sga_target = 167772160
control_files = /opt/oracle/app/oradata/oradb1/control01.ctl, /opt/oracle/app/oradat
a/oradb1/control02.ctl, /opt/oracle/app/oradata/oradb1/control03.ctl
db_block_size = 8192
__db_cache_size = 79691776
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = /opt/oracle/app/flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain = vboxfedora10.typea.info
dispatchers = (PROTOCOL=TCP) (SERVICE=oradb1XDB)
job_queue_processes = 10
background_dump_dest = /opt/oracle/app/admin/oradb1/bdump
user_dump_dest = /opt/oracle/app/admin/oradb1/udump
core_dump_dest = /opt/oracle/app/admin/oradb1/cdump
audit_file_dest = /opt/oracle/app/admin/oradb1/adump
db_name = oradb1
open_cursors = 300
pga_aggregate_target = 16777216
PMON started with pid=2, OS id=6024
PSP0 started with pid=3, OS id=6026
MMAN started with pid=4, OS id=6028
DBW0 started with pid=5, OS id=6030
LGWR started with pid=6, OS id=6032
CKPT started with pid=7, OS id=6034
====MOUNT====
=====SQL Plus=====
SQL> alter database mount;
Database altered.
=====アラートログ=====
Sat Feb 7 08:22:24 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=6044
Sat Feb 7 08:22:24 2009
starting up 1 shared server(s) ...
Sat Feb 7 09:11:23 2009
alter database mount
Sat Feb 7 09:11:27 2009
Setting recovery target incarnation to 2
Sat Feb 7 09:11:27 2009
Successful mount of redo thread 1, with mount id 2023396219
Sat Feb 7 09:11:27 2009
Database mounted in Exclusive Mode
Completed: alter database mount
====OPEN====
=====SQL Plus=====
SQL> alter database open;
Database altered.
=====アラートログ=====
Sat Feb 7 09:12:55 2009
alter database open
Sat Feb 7 09:12:55 2009
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=7423
Sat Feb 7 09:12:55 2009
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=7425
Sat Feb 7 09:12:55 2009
ARC0: STARTING ARCH PROCESSES
Sat Feb 7 09:12:55 2009
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Sat Feb 7 09:12:55 2009
Thread 1 opened at log sequence 33
Current log# 2 seq# 33 mem# 0: /opt/oracle/app/oradata/oradb1/redo02.log
Successful open of redo thread 1
Sat Feb 7 09:12:55 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Feb 7 09:12:55 2009
SMON: enabling cache recovery
Sat Feb 7 09:12:55 2009
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=7427
Sat Feb 7 09:12:57 2009
Successfully onlined Undo Tablespace 1.
Sat Feb 7 09:12:57 2009
SMON: enabling tx recovery
Sat Feb 7 09:12:57 2009
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=19, OS id=7443
Sat Feb 7 09:13:05 2009
db_recovery_file_dest_size of 2048 MB is 2.43% 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.
Sat Feb 7 09:13:06 2009
Completed: alter database open
(END)
====アクセスを制限した起動====
*インスタンスの使用を管理担当者にのみ許可し、一般データベース・ユーザーの使用を禁止
=====RESTRICT=====
startup restrict [ open nomount | mount ]
=====SQL Plus=====
SQL> startup restrict;
ORACLE instance started.
=====アラートログ=====
Sat Feb 7 20:13:27 2009
Starting ORACLE instance (restrict)
:
=====他ユーザでログイン=====
SQL> conn oratest/****
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
===停止===
====SHUTDOWN====
shutdown [ abort normal | transactional | immediate ]
*データベースとインスタンスを停止するには、最初にSYSOPERまたはSYSDBAとして接続する必要
*一部の停止モードでは、実際にデータベースを停止する前に、特定のイベント（トランザクションの完了またはユーザーによる切断など）の発生を待機します。これらのイベントに対するタイムアウト間隔は1時間
**停止をブロックしているイベントすべてが1時間以内に発生しない場合、停止コマンドは次のメッセージを表示して取り消されます。ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました。
=====停止モード=====
*デフォルトの停止モードはNORMAL
{|class="wikitable"
!停止モード
!内容
!新規接続
!セッション
!トランザクション
!次回インスタンス・リカバリ
|-
|NORMAL
|通常の停止
|不可
|切断待機
|完了待機
|不要
|-
|TRANSACTIONAL
|アクティブトランザクションを完了し、予定どおりにインスタンスを停止
|不可
|切断
|完了待機
|不要
|-
|IMMEDIATE
|即時停止
|不可
|切断
|暗黙的にロールバック
|不要
|-
|ABORT
|強制終了
|不可
|強制終了
|強制終了
|必要
|-
|}
=====ABORTからインスタンス・リカバリ=====
*SQL Plus から SQLを発行
SQL> delete from httpd_access_log where seq = 7956;
1 row deleted.
*コミット前に、SHUTDOWN ABORT(アラートログ)
Sun Feb 8 02:24:06 2009
Shutting down instance (abort)
License high water mark = 12
Instance terminated by USER, pid = 32707
*SQL Plusからコミット(失敗する)
SQL> commit;
commit
*
ERROR at line 1:
ORA-03135: connection lost contact
*再起動(STARTUP アラートログ リカバリが実行されている)
:
ALTER DATABASE OPEN
Sun Feb 8 02:25:23 2009
Beginning crash recovery of 1 threads
Sun Feb 8 02:25:23 2009
Started redo scan
Sun Feb 8 02:25:23 2009
Completed redo scan
1339 redo blocks read, 169 data blocks need recovery
Sun Feb 8 02:25:24 2009
Started redo application at
Thread 1: logseq 33, block 65010
Sun Feb 8 02:25:24 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 33 Reading mem 0
Mem# 0 errs 0: /opt/oracle/app/oradata/oradb1/redo02.log
Sun Feb 8 02:25:24 2009
Completed redo application
Sun Feb 8 02:25:24 2009
Completed crash recovery at
Thread 1: logseq 33, block 66349, scn 2049378
169 data blocks read, 169 data blocks written, 1339 redo blocks read
:
Completed: ALTER DATABASE OPEN
{{include_html banner_html, "!Oracle"}}
© 2006 矢木浩人
