oracle之手工建库和删库

手工健库过程中，可以参照官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN11074

一、手工建库
Step 1: Specify an Instance Identifier (SID)
在oracle用户的.bash_profile添加ORACLE_SID参数
export ORACLE_SID=PROD2

Step 2: Ensure That the Required Environment Variables Are Set
在oracle用户的.bash_profile添加以下参数
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
根据参数建立所需目录
[oracle@odd-PROD2 ~]$ mkdir -p /u01/app/oracle/oradata/PROD2
[oracle@odd-PROD2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD2
[oracle@odd-PROD2 ~]$ mkdir -p /u01/app/oracle/admin/PROD2/{a,dp}dump

Step 3: Choose a Database Administrator Authentication Method
[oracle@odd-PROD2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwPROD2 password=oracle entries=5

Step 4: Create the Initialization Parameter File
oracle软件装完后，在/u01/app/oracle/product/11.2.0/dbhome_1/dbs目录下，有init.ora，初始化参数文件。
[oracle@odd-PROD2 dbs]$ cat init.ora  
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest=''
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
通过操作系统复制该文件，并改变其内容
[oracle@odd-PROD2 dbs]$ cp init.ora initPROD2.ora 
[oracle@odd-PROD2 dbs]$ vi initPROD2.ora 
db_name='PROD2'
memory_target=450M
processes = 150
audit_file_dest='/u01/app/oracle/admin/PROD2/adump'
audit_trail ='db'
db_block_size=8192
db_domain='oracle.com'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace=''
# You may want to ensure that control files are created on separate physical
# devices
control_files = ('/u01/app/oracle/oradata/PROD2/control1.ctl','/u01/app/oracle/fast_recovery_area//PROD2/control02.ctl')
compatible ='11.2.0'

Step 5: (Windows Only) Create an Instance
因为我是在windown下运行，所以这部直接跳过

Step 6: Connect to the Instance
sqlplus /nolog
connect / as sysdba
Connected to an idle instance.

Step 7: Create a Server Parameter File
通过静态参数文件创建动态参数文件
SQL> create spfile from pfile;

File created.

Step 8: Start the Instance 
SQL> startup nomount
ORACLE instance started.

Total System Global Area  472887296 bytes
Fixed Size                  1345716 bytes
Variable Size             289408844 bytes
Database Buffers          176160768 bytes
Redo Buffers                5971968 bytes

Step 9: Issue the CREATE DATABASE Statement
创建脚本并运行（该脚本用于创建数据库日志文件和数据文件）
[oracle@odd- ~]$ vi /home/oracle/createDB.sql
vi /home/oracle/createDB.sql
-- Add the following lines
create database PROD2
user sys identified by oracle
user system identified by oracle
logfile group 1 ('/u01/app/oracle/oradata/PROD2/redo01a.log') size 100m blocksize 512 reuse,
group 2 ('/u01/app/oracle/oradata/PROD2/redo02a.log') size 100m blocksize 512 reuse,
group 3 ('/u01/app/oracle/oradata/PROD2/redo03a.log') size 100m blocksize 512 reuse
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 100
character set al32utf8
national character set al16utf16
extent management local
datafile '/u01/app/oracle/oradata/PROD2/system01.dbf'
size 325m reuse autoextend on maxsize unlimited
sysaux datafile '/u01/app/oracle/oradata/PROD2/sysaux01.dbf'
size 325m reuse autoextend on maxsize unlimited
default tablespace users
datafile '/u01/app/oracle/oradata/PROD2/users01.dbf'
size 500m reuse autoextend on maxsize unlimited
default temporary tablespace temp
tempfile '/u01/app/oracle/oradata/PROD2/temp01.dbf'
size 20m reuse
undo tablespace undotbs
datafile '/u01/app/oracle/oradata/PROD2/undotbs01.dbf'
size 200m reuse autoextend on maxsize unlimited;

SQL> @/home/oracle/createDB.sql

Database created.
Step 10: (Optional) Create Additional Tablespaces
添加一些额外的表空间，是可选项，此处不做

Step 11: Run Scripts to Build Data Dictionary Views
通过执行脚本创建oracle的数据字典视图
$ sqlplus / as sysdba
@?/rdbms/admin/catalog.sql 
@?/rdbms/admin/catproc.sql 
@?/rdbms/admin/utlrp.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql
到此其实oracle的实例已将创建完毕，并且自动打开了
剩下的都是些可选项和对数据库的备份

Step 12: (Optional) Run Scripts to Install Additional Options
运行脚本安装oracle1额外的功能，此处非是可不做项

Step 13: Back Up the Database
备份数据库，备份专题日后再议，此处省略

Step 14: (Optional) Enable Automatic Instance Startup
设置oracle自动启动，为可选项
官方文档说，在windowns环境下，可以通过以下语句达到效果
ORADIM -EDIT -SID sid -STARTMODE AUTO -SRVCSTART SYSTEM [-SPFILE]
而在linux中，可以通过在/etc/oratab加入以下语句
PROD2:/u01/app/oracle/product/11.2.0/dbhome_1:Y
其中最后的Y表示自动启动
注意：手工建库后必须加入这条语句后dbca才会发现该库


二、手工删库
删除 DB
[oracle@odd-PROD2 dbs]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 25 11:09:29 2016


Copyright (c) 1982, 2011, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  472887296 bytes
Fixed Size                  1345716 bytes
Variable Size             318768972 bytes
Database Buffers          146800640 bytes
Redo Buffers                5971968 bytes
Database mounted.
SQL> alter system enable restricted session;


System altered.


SQL> drop database;


Database dropped.


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> quit
删除相关文件夹
[oracle@odd-PROD2 dbs]$ rm -rf /u01/app/oracle/oradata/PROD2
[oracle@odd-PROD2 dbs]$ rm -rf /u01/app/oracle/fast_recovery_area/PROD2
[oracle@odd-PROD2 dbs]$ rm -rf /u01/app/oracle/admin/PROD2
[oracle@odd-PROD2 dbs]$ rm -rf /u02/logs/PROD2
删除相关文件（ initPROD2.ora, lkPROD2, orapwPROD2, hc_PROD2.dat 等）
[oracle@odd-PROD2 dbs]$ rm -f $ORACLE_HOME/dbs/*PROD2*
[oracle@odd-PROD2 dbs]$ rm -f /home/oracle/createDB.sql
 删除手工添加的 DB
[oracle@odd-PROD2 dbs]$ vi /etc/oratab

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31386161/viewspace-2127348/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31386161/viewspace-2127348/