manual database creation for oracle10g on solaris10

1,配置ORACLE_SID

bash-3.00$ export ORACLE_SID=mynewdb

2,切到dbs目錄
bash-3.00$ cd $ORACLE_HOME/dbs
bash-3.00$ ls
init.ora    initdw.ora
bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 14 20:24:04 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount    
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/export/home/orainstall/oracle/product/10.2.0/db_1/dbs/initmanualdb.ora'
SQL> exit
Disconnected
bash-3.00$ pwd
/export/home/orainstall/oracle/product/10.2.0/db_1/dbs

3,生成一個pfile檔案，以初始init.ora為基礎
bash-3.00$ cp -p init.ora initmanualdb.ora  ---在init.ora檔案中新增undo_management=auto
bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 14 20:24:56 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount  ---因生成的pfile檔案此值太小，加大就可;且在此pfile末加上control_files
ORA-00371: not enough shared pool memory, should be atleast 62198988 bytes
SQL> exit
Disconnected
bash-3.00$ ls
init.ora          init.ora.bak      initdw.ora        initmanualdb.ora

SQL> startup nomount --再次nomount
ORACLE instance started.

Total System Global Area  100663296 bytes
Fixed Size                  1278688 bytes
Variable Size              88083744 bytes
Database Buffers            8388608 bytes
Redo Buffers                2912256 bytes
SQL> show parameter control

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
/export/home/orainstall/oracle
/product/10.2.0/db_1/dbs/ora_c
ontrol1, /export/home/orainsta
ll/oracle/product/10.2.0/db_1/
dbs/ora_control2, /export/home
/orainstall/control01.dbf, /ex

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
port/home/orainstall/control02
.dbf


4,根據pfile生成spfile,關閉庫

SQL> create spfile from pfile;--生成spfile

File created.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.


5,建立資料庫

    
SQL> startup  nomount
ORACLE instance started.

Total System Global Area  100663296 bytes
Fixed Size                  1278688 bytes
Variable Size              88083744 bytes
Database Buffers            8388608 bytes
Redo Buffers                2912256 bytes
SQL> create database manualdb
  2  user sys identified by sex
  3  user system identified by sex
  4  logfile group 1 '/export/home/orainstall/redo01.log' size 50m,  --注意關鍵字logfile group
  5          group 2 '/export/home/orainstall/redo02.log' size 50m
  6  maxlogfiles 5
  7  maxlogmembers 5
  8  maxloghistory 1
  9  maxdatafiles 100
 10  character set US7ASCII
 11  NATIONAL character set al16utf16
 12  datafile '/export/home/orainstall/system01.dbf' size 300m reuse
 13  extent management local
 14  sysaux datafile '/export/home/orainstall/sysaux01.dbf' size 300m  reuse
 15  default tablespace tbs_1
 16  datafile '/export/home/orainstall/tbs01.dbf' size 100m reuse  --注意：為預設表空間新增對應的datafile,不然會報相關的sql.bsq錯誤
 17  default temporary tablespace tempts1
 18  tempfile '/export/home/orainstall/temp01.dbf' size 20m reuse
 19  undo tablespace undotbs
 20  DATAFILE '/export/home/orainstall/undotbs01.dbf'
 21  SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.


6,執行catalog.sql，生成資料字典及動態效能檢視
SQL> @?/rdbms/admin/catalog.sql
DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error and terminate the SQLPLUS session if the user is not SYS.
DOC>    Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected


7,執行catproc.sql,生成plsql包相關過程
SQL> @?/rdbms/admin/catproc.sql




手工刪除資料庫

1,預備條件:以exclusive啟動資料庫到mount,且啟用restricted session

-bash-3.00$ export ORACLE_SID=manualdb
-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 15 10:09:56 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  100663296 bytes
Fixed Size                  1278688 bytes
Variable Size              88083744 bytes
Database Buffers            8388608 bytes
Redo Buffers                2912256 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.


2,drop database
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit

3,後記：只是刪除資料檔案，日誌檔案，控制檔案；對於生成的歸檔日誌沒有影響，可以透過rman操作歸檔日誌