oracle 从入门到删库,oracle之手工建库和删库

手工健库过程中，可以参照官方文档：

一、手工建库

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/，如需转载，请注明出处，否则将追究法律责任。