!!!Oracle 10g データベースの起動と停止 [Oracle Database10g] {{amazon 479810910X}} http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19224-02/start.htm#178896 !!起動 !概要 {{ref_image ora_start.jpg}} ::コマンド start [pfile=PFILE名] [nomount | mount | open] *デフォルトは open *pfileを省略すると、以下の優先順にて初期化パラメータファイルが読み込まれる ""1. spfile[SID].ora ""2. spfile.ora ""3. init[SID].ora *初期化パラメータファイルが格納されているデフォルトディレクトリは $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 [nomount | mount | open] ::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 [normal | transactional | immediate | abort ] *データベースとインスタンスを停止するには、最初にSYSOPERまたはSYSDBAとして接続する必要 *一部の停止モードでは、実際にデータベースを停止する前に、特定のイベント（トランザクションの完了またはユーザーによる切断など）の発生を待機します。これらのイベントに対するタイムアウト間隔は1時間 **停止をブロックしているイベントすべてが1時間以内に発生しない場合、停止コマンドは次のメッセージを表示して取り消されます。ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました。 ::停止モード *デフォルトの停止モードはNORMAL ,停止モード,内容,新規接続,セッション,トランザクション,次回インスタンス・リカバリ ,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"}}