手动创建数据库
?
手動創建數據庫步驟:
1、準備工作(創建相關目錄、設置ORACLE_SID)
2、生成參數文件
3、啟動實例
3、腳本創建數據庫
4、腳本創建數據字典
5、打開數據庫
一: 準備工作
1、創建相關目錄
wyncrac為要創建的數據名,所以創建同名目錄用來集中存儲相關文件
[oracle@localhost wyncrac]$ ll
total 36
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 09:57 archivelog --歸檔日志,
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 11:03 controlfile --控制文件
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 12:13 dump
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 09:58 pwdfile --密碼文件
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 11:03 redolog --重做日志文件
drwxrwxrwx. 2 oracle root 4096 Jun 24 11:02 scripts --常用腳本
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 11:04 sysdatafile --系統數據文件
drwxr-xr-x. 2 oracle oinstall 4096 Jun 24 09:58 userdatafile --用戶數據文件
以上文件在create database腳本中會用到
2、設置當前ORACLE_SID
[oracle@localhost ~]$ export ORACLE_SID=wyncrac
二: 生成參數文件
兩種類型:
二進制類型參數文件(無法直編輯) -> spfileSID.ora
修改 -> alter system set paraname='' 同步應用到參數文件
啟動 -> startup nomount ?默認將尋找該類型參數文件
9i以后默認
文本類型參數文件(可直接編輯) -> pfileSID.ora
修改 -> alter system set paraname='' 不會同步應用到參數文件,要手動調整文件對
啟動 -> startup nomount pfile=...
startup nomount階段讀取參數文件,得到db_name、SGA參數、控制文件位置等信息
默認位置 -> $ORACLE_HOME/dbs/
查找順序 -> $ORACLE_HOME/dbs/spfileSID.ora -> spfile.ora-> initSID.ora
相互生成
pfile ->spfile: create spfile=$ORACLE_HOME/dbs/spfilewyncrac.ora from pfile=$ORACLE_HOME/dbs/pfile.ora
spfile ->pfile: 與以上相反即可
1、從現有的參數文件中另存一份作為新實例的參數文件
[oracle@localhost dbs]$ cp initora.ora initwyncrac.ora
2、編輯文件做調整
將control_files路徑設為上面創建的controlfile -> /ora/ora10g/wyncrac/controlfile
dump相關文件路徑設為dump -> /ora/ora10g/wyncrac/dump
[oracle@localhost dbs]$ vim initwyncrac.ora
wyncrac.__db_cache_size=188743680
wyncrac.__java_pool_size=4194304
wyncrac.__large_pool_size=4194304
wyncrac.__shared_pool_size=83886080
wyncrac.__streams_pool_size=0
*.audit_file_dest='/ora/ora10g/wyncrac/dump'
*.background_dump_dest='/ora/ora10g/wyncrac/dump'
*.compatible='10.2.0.1.0'
*.control_files='/ora/ora10g/wyncrac/controlfile/control01.ctl','/ora/ora10g/wyncrac/controlfile/control02.ctl','/ora/ora10g/
wyncrac/controlfile/control03.ctl'
*.core_dump_dest='/ora/ora10g/wyncrac/dump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='wyncrac'
*.db_recovery_file_dest='/ora/ora10g/product/10.2.0/db_1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wyncracXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=284164096
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora/ora10g/wyncrac/dump'
3、由pfile生成spfile
SQL> create spfile='/ora/ora10g/product/10.2.0/db_1/dbs/spfilewyncrac.ora'
2 from pfile='/ora/ora10g/product/10.2.0/db_1/dbs/initwyncrac.ora';
File created.
三: 啟動實例
1、創建、還原數據庫等必須啟動實例后到nomount進行，該階段只會啟動實例(分配SGA與后臺進程)
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
2、執行創建數據庫腳本
a、在上面創建的scripts目錄下創建createdb.sql文件如下:
create database wyncrac
LOGFILE
GROUP 1 ('/ora/ora10g/wyncrac/redolog/redo01.log','/ora/ora10g/wyncrac/redolog/redo01_1.log') size 10m reuse,
GROUP 2 ('/ora/ora10g/wyncrac/redolog/redo02.log','/ora/ora10g/wyncrac/redolog/redo02_1.log') size 10m reuse,
GROUP 3 ('/ora/ora10g/wyncrac/redolog/redo03.log','/ora/ora10g/wyncrac/redolog/redo03_1.log') size 10m reuse
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXLOGHISTORY 200
MAXDATAFILES 500
MAXINSTANCES 5
ARCHIVELOG
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
DATAFILE '/ora/ora10g/wyncrac/sysdatafile/system01.dbf' SIZE 100M reuse autoextend on next 10m maxsize unlimited extent
management local
SYSAUX DATAFILE '/ora/ora10g/wyncrac/sysdatafile/sysaux01.dbf' SIZE 100M reuse autoextend on next 10m maxsize unlimited
UNDO TABLESPACE UNDOTBS1 DATAFILE '/ora/ora10g/wyncrac/sysdatafile/undo.dbf' SIZE 50M reuse autoextend on next 5M maxsize
unlimited
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/ora/ora10g/wyncrac/sysdatafile/temp.dbf' SIZE 50M reuse autoextend on next 5M
maxsize unlimited;
b、數據庫創建腳本執行完后，可看到重做日志、系統數據表路徑、警告日志等文件會創建在上面定義的目錄中
relog -> /ora/ora10g/wyncrac/redolog/
sysdatafile -> /ora/ora10g/wyncrac/sysdatafile/
alertlog -> /ora/ora10g/wyncrac/dump/
controlfile -> /ora/ora10g/wyncrac/controlfile/
默認位置:
relog、datafile、controlfile -> $ORACLE_HOME/oradata/SID/
alertlog -> $ORACLE_HOME/admin/SID/
c、打開數據庫
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
四: 相關視圖
1、v$log -> 日志信息
SQL> select group#,thread#,sequence#,bytes,members,archived,status
2 from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 10 10485760 2 YES INACTIVE
2 1 11 10485760 2 NO CURRENT
3 1 9 10485760 2 YES INACTIVE
STATUS:
INACTIVE -> 已歸檔
ACTIVE -> 歸檔中
CURRENT -> 當前正在寫
2、v$logfile -> 聯機重做日志路徑
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/ora/ora10g/wyncrac/redolog/redo01.log
/ora/ora10g/wyncrac/redolog/redo01_1.log
/ora/ora10g/wyncrac/redolog/redo02.log
/ora/ora10g/wyncrac/redolog/redo02_1.log
/ora/ora10g/wyncrac/redolog/redo03.log
/ora/ora10g/wyncrac/redolog/redo03_1.log
6 rows selected.
3、當前數據庫是否歸檔模式:
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
或
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
啟動步驟:
startup nomount -> mount ->open 直接startup時將從nomount逐步到open
startup nomount:
1: 僅讀取參數文件分配SGA結構
2: 啟動后臺進程
3: 生成或修改警告或跟蹤文件,默認位置在$ORACLE_HOME/admin/SID/alert<SID>.log與<SID>_*.trc
startup mount:
1: 按參文件位置,鎖定并讀取控制文件獲取重做日志與數據文件信息,但并不打開這些信息
控制文件損壞就無法啟動，但數據文件丟失對此步驟沒影響
startup open
1: 打開數據文件
2: 打開聯機重做日志
3: 后臺進程會檢測控制文件、數據文件、聯機日志文件的一致性，即SCN是否一致
4: 如一致性檢查失敗，由SMON進行實例恢復,通過歸檔與重做日志將未寫入datafile的應用redolog重做,已寫入datafile但未提交的應用undo回滾
5: 如數據文件、聯機重做日志丟失或異常，啟動將終止，需要進行介質恢復
?
?
轉載于:https://www.cnblogs.com/doclaim/p/3155871.html
總結
- 上一篇： （部分转载，部分原创）java大数类（2
- 下一篇： c# 关于WebBrowser的模拟提交