Oracle手動建庫常見問題

lhrbest發表於2016-07-13
Oracle

Oracle手動建庫常見問題


  1. BLOG文件結構圖


  1. 前言部分


  1. 導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 10G和11g手動建庫（重點）

② 各種元件安裝

③ 建立Sample Schemas資料

④ 手動建庫中常用指令碼的解釋

⑤ sqlplus中的幫助命令


Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/

④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

  1. 本文簡介

上一篇（http://blog.itpub.net/26736162/viewspace-2121930/）中最後差了手動建庫的部分，今天把這個部分的內容加上。本來手動建庫很早就學習過了，只是一直沒有時間來整理釋出，今天就趁這個機會正好整理一下，分享給大家。

小麥苗學習手動建庫的動力源於之前幫網友採用dbca建庫的時候報錯，由於java環境的問題，dbca一直沒有辦法使用，無論介面還是靜默都用到java，折騰了2個小時還是把java沒有修復好，dbca不能用，最後想到了create database手動建庫，雖然工作中很少採用但還是有一定的用途的。


  1. 手動建庫簡介

有時候因為環境的緣故不能使用圖形介面或者不能使用dbca的靜默方式來建立一個新庫，那麼這個時候可以考慮使用CREATE DATABASE SQL命令列來建立資料庫，該方式是一種手動建庫方式，使用此種命令列手動建立資料庫的優點是：可以用指令碼來建立資料庫。 另外OCM的考試中要求我們用CREATE DATABASE來建立資料庫。當然在使用指令碼建立資料庫時，在建立資料字典檢視和安裝標準的PL/SQL程式包時，必須先建立一個可以操作的資料庫。

  1. 手動建庫基本步驟

官方文件的步驟：

Step 1: Specify an Instance Identifier (SID)

Step 2: Ensure That the Required Environment Variables Are Set

Step 3: Choose a Database Administrator Authentication Method

Step 4: Create the Initialization Parameter File

Step 5: (Windows Only) Create an Instance

Step 6: Connect to the Instance

Step 7: Create a Server Parameter File

Step 8: Start the Instance

Step 9: Issue the CREATE DATABASE Statement

Step 10: Create Additional Tablespaces

Step 11: Run Scripts to Build Data Dictionary Views

Step 12: (Optional) Run Scripts to Install Additional Options

Step 13: Back Up the Database.

Step 14: (Optional) Enable Automatic Instance Startup


具體可以參考：

我的blog：http://blog.itpub.net/26736162/viewspace-2098211/


  1. 直接給出指令碼

我們直接給出手動建庫用到的指令碼，至於過程小麥苗就不演示了。

  1. 11G


  1. ORACLE使用者執行 資料檔案在檔案系統 單例項DB

------------ 1、 確保環境變數正確

export ORACLE_SID=lhrdb
env|grep ORACLE

ORACLE_SID=lhrdb
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1




------------ 2、 建立密碼檔案



orapwd file=$ORACLE_HOME/dbs/orapwlhrdb password=lhr force=y



------------ 3、 建立初始化引數檔案和相關路徑


$ORACLE_HOME/dbs/initlhrdb.ora
db_name='lhrdb'
memory_target=400437056
processes = 150
audit_file_dest='/u01/app/oracle/admin/lhrdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=MYNEWDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u01/app/oracle/oradata/lhrdb/control01.ctl','/u01/app/oracle/flash_recovery_area/lhrdb/control02.ctl'
compatible ='11.2.0'



mkdir -p /u01/app/oracle/admin/lhrdb/adump
mkdir -p /u01/app/oracle/flash_recovery_area/lhrdb/
mkdir -p /u01/app/oracle/oradata/lhrdb/





------------ 4、 建立spfile，啟動到nomount狀態


sqlplus / as sysdba

create spfile from pfile;

startup nomount

! ps -ef|grep lhrdb




------------ 5、 建立DB

CREATE DATABASE lhrdb
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('/u01/app/oracle/oradata/lhrdb/redo01a.log','/u01/app/oracle/oradata/lhrdb/redo01b.log') SIZE 50M BLOCKSIZE 512,
    GROUP 2('/u01/app/oracle/oradata/lhrdb/redo02a.log','/u01/app/oracle/oradata/lhrdb/redo02b.log') SIZE 50M blocksize 512,
    GROUP 3('/u01/app/oracle/oradata/lhrdb/redo03a.log','/u01/app/oracle/oradata/lhrdb/redo03b.log') SIZE 50M BLOCKSIZE 512
DATAFILE '/u01/app/oracle/oradata/lhrdb/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '/u01/app/oracle/oradata/lhrdb/sysaux01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/lhrdb/temp01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/oradata/lhrdb/undotbs01.dbf'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/lhrdb/users01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
/





SPOOL /tmp/dictionary_tmp.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/caths.sql
@?/rdbms/admin/owminst.plb
@?/sqlplus/admin/plustrce.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlsampl.sql
conn system/lhr
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
SPOOL off





------ 單例項資料庫新增到srvctl中

srvctl add database -d lhrdb -c single -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilelhrdb.ora' -r primary -n lhrdb -x ZFXDESKDB2


srvctl config database -d lhrdb -a


srvctl status database -d lhrdb
srvctl start database -d lhrdb


crsctl stat res -t



------ drop database

alter database close;
alter system enable restricted session;
drop database;





  1. ORACLE使用者執行 資料檔案在ASM中 單例項DB

------------ 1、 確保環境變數正確

export ORACLE_SID=lhrasm
env|grep ORACLE


ORACLE_SID=lhrasm
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1



------------ 2、 建立密碼檔案



orapwd file=$ORACLE_HOME/dbs/orapwlhrasm password=lhr force=y



------------ 3、 建立初始化引數檔案和相關路徑


$ORACLE_HOME/dbs/initlhrasm.ora


db_name='lhrasm'
memory_target=400437056
processes = 150
audit_file_dest='/u01/app/oracle/admin/lhrasm/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_create_file_dest='+DATA'
db_recovery_file_dest='+FRA'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=MYNEWDBXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '+DATA/lhrasm/controlfile/control01.ctl','+FRA/lhrasm/controlfile/control02.ctl'
compatible ='11.2.0'



mkdir -p /u01/app/oracle/admin/lhrasm/adump
mkdir -p /u01/app/oracle/flash_recovery_area/lhrasm/
mkdir -p /u01/app/oracle/oradata/lhrasm/




------------ 4、 建立spfile，啟動到nomount狀態


sqlplus / as sysdba
create spfile from pfile;
startup nomount
! ps -ef|grep lhrasm



------------ 5、 建立DB

CREATE DATABASE lhrasm
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('+DATA','+DATA') SIZE 50M BLOCKSIZE 512,
    GROUP 2('+DATA','+DATA') SIZE 50M blocksize 512,
    GROUP 3('+DATA','+DATA') SIZE 50M BLOCKSIZE 512
DATAFILE '+DATA' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '+DATA' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SET DEFAULT bigfile TABLESPACE
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
;




/* --------- BIGFILE

CREATE DATABASE lhrasm

USER SYS IDENTIFIED BY lhr

USER SYSTEM IDENTIFIED BY lhr

CONTROLFILE REUSE

EXTENT MANAGEMENT LOCAL

SET DEFAULT bigfile TABLESPACE

DEFAULT TEMPORARY TABLESPACE TEMP

UNDO TABLESPACE UNDOTBS1

DEFAULT TABLESPACE USERS

;

*/



SPOOL /tmp/dictionary_tmp.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/caths.sql
@?/rdbms/admin/owminst.plb
@?/sqlplus/admin/plustrce.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlsampl.sql
conn system/lhr
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
SPOOL off






------ 單例項資料庫新增到srvctl中

srvctl add database -d lhrasm -c single -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilelhrasm.ora' -r primary -n lhrasm -x ZFXDESKDB2


srvctl config database -d lhrasm -a


srvctl status database -d lhrasm
srvctl start database -d lhrasm


crsctl stat res -t



------ drop database

alter database close;
alter system enable restricted session;
drop database;




  1. 11G rac asm

---- 思路：先建立單例項DB然後再轉換為RAC DB


export ORACLE_SID=raclhr1
env|grep ORACLE


ORACLE_SID=raclhr
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1



------------ 2、 2個節點都 建立密碼檔案

orapwd file=$ORACLE_HOME/dbs/orapwraclhr1 password=lhr force=y
orapwd file=$ORACLE_HOME/dbs/orapwraclhr2 password=lhr force=y



------------ 3、 建立初始化引數檔案和相關路徑


--- 節點一配置：

$ORACLE_HOME/dbs/initraclhr1.ora


*.db_name='raclhr'
*.memory_target=400437056
*.processes = 150
*.open_cursors=300
*.audit_file_dest='/u01/app/oracle/admin/raclhr/adump'
*.audit_trail ='db'
*.db_block_size=8192
*.db_domain=''
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=2G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYNEWDBXDB)'
*.control_files = '+DATA/raclhr/controlfile/control01.ctl','+FRA/raclhr/controlfile/control02.ctl'
*.remote_login_passwordfile='EXCLUSIVE'



---2個節點都建立路徑

mkdir -p /u01/app/oracle/admin/raclhr/adump
mkdir -p /u01/app/oracle/flash_recovery_area/raclhr/
mkdir -p /u01/app/oracle/oradata/raclhr/

--- 節點一執行

su - grid
asmcmd
cd +DATA
mkdir raclhr
cd raclhr
mkdir PARAMETERFILE


su - oracle
sqlplus / as sysdba
create spfile='+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora' from pfile;


---2個節點都執行 建立初始化引數檔案執行ASM磁碟裡的SPFILE

cp $ORACLE_HOME/dbs/initraclhr1.ora $ORACLE_HOME/dbs/initraclhr1.ora_bk


echo "spfile='+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora'" > $ORACLE_HOME/dbs/initraclhr1.ora
echo "spfile='+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora'" > $ORACLE_HOME/dbs/initraclhr2.ora


------------ 4、節點一啟動到nomount狀態

startup nomount
! ps -ef|grep raclhr
show parameter spfile


------------ 5、 建立DB


CREATE DATABASE raclhr
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('+DATA','+DATA') SIZE 50M BLOCKSIZE 512,
    GROUP 2('+DATA','+DATA') SIZE 50M blocksize 512,
    GROUP 3('+DATA','+DATA') SIZE 50M BLOCKSIZE 512
DATAFILE '+DATA' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '+DATA' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SET DEFAULT bigfile TABLESPACE
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
;




/* --------- BIGFILE

CREATE DATABASE raclhr

USER SYS IDENTIFIED BY lhr

USER SYSTEM IDENTIFIED BY lhr

CONTROLFILE REUSE

EXTENT MANAGEMENT LOCAL

SET DEFAULT bigfile TABLESPACE

DEFAULT TEMPORARY TABLESPACE TEMP

UNDO TABLESPACE UNDOTBS1

DEFAULT TABLESPACE USERS

;

*/


------------ 6、 修改rac需要的內容

create undo tablespace UNDOTBS2 datafile '+DATA' size 50M reuse autoextend off;
alter database add logfile thread 2 group 4 ('+DATA','+FRA') SIZE 50M BLOCKSIZE 512;
alter database add logfile thread 2 group 5 ('+DATA','+FRA') SIZE 50M BLOCKSIZE 512;
alter database add logfile thread 2 group 6 ('+DATA','+FRA') SIZE 50M BLOCKSIZE 512;


select * from v$log;



ALTER SYSTEM SET cluster_database=true scope=spfile sid='*';
ALTER SYSTEM SET instance_number=1 scope=spfile sid='raclhr1';
ALTER SYSTEM SET instance_number=2 scope=spfile sid='raclhr2';
ALTER SYSTEM SET thread=1 scope=spfile sid='raclhr1';
ALTER SYSTEM SET thread=2 scope=spfile sid='raclhr2';
ALTER SYSTEM SET undo_tablespace='UNDOTBS1' scope=spfile sid='raclhr1';
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' scope=spfile sid='raclhr2';
alter database enable public thread 2;


shutdown immediate


------------ 7、 啟動2個節點


------ rac 資料庫新增到srvctl中

srvctl add database -d raclhr -c rac -o /oracle/app/oracle/product/11.2.0/db -p '+DATA/raclhr/PARAMETERFILE/spfileraclhr.ora' -r primary -n raclhr
srvctl config database -d raclhr -a


srvctl add instance -d raclhr -i raclhr1 -n ZFXDESKDB1
srvctl add instance -d raclhr -i raclhr2 -n ZFXDESKDB2




srvctl status database -d raclhr
srvctl stop db -d raclhr
srvctl start db -d raclhr
srvctl status database -d raclhr


crsctl stat res -t



------------ 8、 編譯資料字典指令碼


SPOOL /tmp/dictionary_tmp.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/caths.sql
@?/rdbms/admin/owminst.plb
@?/sqlplus/admin/plustrce.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlsampl.sql
conn system/lhr
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
SPOOL off


------ drop database

alter system set cluster_database=false scope=spfile;
! srvctl stop db -d raclhr
startup force mount restrict;
drop database;


  1. 10G


export ORACLE_SID=lhrdb

orapwd file=$ORACLE_HOME/dbs/orapwlhrdb password=lhr force=y


vi $ORACLE_HOME/dbs/initlhrdb.ora


db_name=lhrdb
processes=150
max_dump_file_size=10240
global_names=TRUE
control_files=('/u01/app/oracle/oradata/lhrdb/control01.ora','/u01/app/oracle/oradata/lhrdb/control02.ora')
sga_target=400m
undo_management='AUTO'
undo_tablespace='UNDOTBS1'






mkdir -p $ORACLE_BASE/oradata/lhrdb
mkdir -p $ORACLE_BASE/lhrdb/adump
mkdir -p $ORACLE_BASE/lhrdb/bdump
mkdir -p $ORACLE_BASE/lhrdb/cdump
mkdir -p $ORACLE_BASE/lhrdb/ddump
mkdir -p $ORACLE_BASE/lhrdb/udump






sqlplus / as sysdba
create spfile from pfile;




startup nomount;
CREATE DATABASE lhrdb
USER SYS IDENTIFIED BY lhr
USER SYSTEM IDENTIFIED BY lhr
CONTROLFILE REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 24 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 12
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/lhrdb/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/lhrdb/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/lhrdb/redo03.log') SIZE 50M
DATAFILE '/u01/app/oracle/oradata/lhrdb/system01.dbf' SIZE 300M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/lhrdb/sysaux01.dbf' SIZE 100M REUSE
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/lhrdb/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/lhrdb/undotbs01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G ;








conn / as sysdba
SPOOL /tmp/dictionary_tmp.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/caths.sql
@?/rdbms/admin/owminst.plb
@?/sqlplus/admin/plustrce.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlsampl.sql
conn system/lhr
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
SPOOL off


  1. 建立bigfile的db報錯

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01501: CREATE DATABASE failed

ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 20

ORA-00604: error occurred at recursive SQL level 1

ORA-32772: BIGFILE is invalid option for this type of tablespace

Process ID: 12451948

Session ID: 156 Serial number: 3


--------- 解決辦法：SET DEFAULT bigfile TABLESPACE 位置不對，應該如下：


/* ---------BIGFILE

CREATE DATABASE lhrasm

USER SYS IDENTIFIED BY lhr

USER SYSTEM IDENTIFIED BY lhr

CONTROLFILE REUSE

CONTROLFILE REUSE

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

ARCHIVELOG

MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32

LOGFILE GROUP 1('+DATA','+DATA') SIZE 50M BLOCKSIZE 512,

    GROUP 2('+DATA','+DATA') SIZE 50M blocksize 512,

    GROUP 3('+DATA','+DATA') SIZE 50M BLOCKSIZE 512

DATAFILE '+DATA' SIZE 300M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

SYSAUX DATAFILE '+DATA' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

SET DEFAULT bigfile TABLESPACE

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 20M REUSE AUTOEXTEND OFF

UNDO TABLESPACE UNDOTBS1 DATAFILE '+DATA'SIZE 50M REUSE AUTOEXTEND OFF

DEFAULT TABLESPACE USERS DATAFILE '+DATA' SIZE 500M REUSE AUTOEXTEND OFF

;



CREATE DATABASE lhrasm

USER SYS IDENTIFIED BY lhr

USER SYSTEM IDENTIFIED BY lhr

CONTROLFILE REUSE

EXTENT MANAGEMENT LOCAL

SET DEFAULT bigfile TABLESPACE

DEFAULT TEMPORARY TABLESPACE TEMP

UNDO TABLESPACE UNDOTBS1

DEFAULT TABLESPACE USERS

;

*/


  1. 手動建庫中的元件安裝

------ 安裝JVM

@?/javavm/install/initjvm.sql;

@?/xdk/admin/initxml.sql;

@?/xdk/admin/xmlja.sql;

@?/rdbms/admin/catjava.sql;



-- 安裝XMLDB

@?/rdbms/admin/catqm.sql oracle SYSAUX TEMP YES

@?/rdbms/admin/catxdbj.sql;



其它元件安裝可以參考：http://blog.itpub.net/26736162/viewspace-1562441/


  1. 如何安裝Sample Schemas

dbca靜默安裝中有個引數是sampleSchema我們若設定為true，則安裝後資料庫中有EXAMPLE表空間，有HR,OE,PM,SH,IX使用者，大約佔用350M的空間，若設定為false，則後續可以根據以下文件來安裝。

【OH】 Database Sample Schemas -- Installation and Descriptions ：http://blog.itpub.net/26736162/viewspace-2098222/



  1. 手動建庫中常用指令碼的解釋

更多的資料字典指令碼說明可以參考：【OH】常用資料字典指令碼說明 SQL Scripts ：http://blog.itpub.net/26736162/viewspace-2098205/


Script Name

Needed For

Run By

Description

catalog.sql

All databases

SYS

Creates the data dictionary and public synonyms for many of its views
Grants PUBLIC access to the synonyms

catproc.sql

All databases

SYS

Runs all scripts required for, or used with, PL/SQL

catclust.sql

Real Application Clusters

SYS

Creates Real Application Clusters data dictionary views

catblock.sql

Performance management

SYS

Creates views that can dynamically display lock dependency graphs

dbmspool.sql

Performance management

SYS or SYSDBA

Enables DBA to lock PL/SQL packages, SQL statements, and triggers into the shared pool

caths.sql

Heterogeneous Services

SYS

Installs packages for administering heterogeneous services

@?/rdbms/admin/owminst.plb

 

sys

建立WMSYS使用者

@?/sqlplus/admin/pupbld.sql

 

system

解決PRODUCT_USER_PROFILE問題

@?/sqlplus/admin/plustrce.sql

 

sys

普通使用者set autot on的許可權

@?/sqlplus/admin/help/hlpbld.sql helpus.sql

 

system

sqlplus的幫助文件

  1. 關於sqlplus的幫助命令

手動建庫最後有個指令碼：@?/sqlplus/admin/help/hlpbld.sql helpus.sql是用來生成sqlpuls的幫助命令的，我們演示如下：





About Me

..........................................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新
本文地址：http://blog.itpub.net/26736162/viewspace-2121981/
本文pdf版： （提取碼：ed9b） 
小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/
聯絡我請加QQ好友(642808185)，註明新增緣由
於 2016-07-13 09:00~ 2016-07-13 17:00 在中行完成
【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】
..........................................................................................................................................................................................................

拿起手機掃描下邊的圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr,學習最實用的資料庫技術。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2121981/，如需轉載，請註明出處，否則將追究法律責任。

相關文章