drop database操作

发表于 2011 年 11 月 15 日由惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：drop database操作

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、sql操作

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 15:00:15 2011

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             285216048 bytes
Database Buffers          121634816 bytes
Redo Buffers                8466432 bytes
Database mounted.
SQL> drop database;
drop database
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


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 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

二、alert日志内容

Tue Nov 15 15:00:18 2011
Adjusting the default value of parameter parallel_max_servers
from 320 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
ORACLE_HOME = /opt/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      node1.srtcloud.com
Release:        2.6.18-238.19.1.el5
Version:        #1 SMP Fri Jul 15 07:31:24 EDT 2011
Machine:        x86_64
Using parameter settings in server-side spfile /opt/oracle/product/11.2.0/db_1/dbs/spfilet1.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 400M
  control_files            = "/opt/oracle/oradata/t1/control01.ctl"
  control_files            = "/opt/oracle/fast_recovery_area/t1/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=t1XDB)"
  local_listener           = "LISTENER_T1"
  audit_file_dest          = "/opt/oracle/admin/t1/adump"
  audit_trail              = "DB"
  db_name                  = "t1"
  open_cursors             = 300
  diagnostic_dest          = "/opt/oracle"
Tue Nov 15 15:00:22 2011
PMON started with pid=2, OS id=26704 
Tue Nov 15 15:00:22 2011
PSP0 started with pid=3, OS id=26706 
Tue Nov 15 15:00:23 2011
VKTM started with pid=4, OS id=26708 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Tue Nov 15 15:00:23 2011
GEN0 started with pid=5, OS id=26712 
Tue Nov 15 15:00:23 2011
DIAG started with pid=6, OS id=26714 
Tue Nov 15 15:00:23 2011
DBRM started with pid=7, OS id=26716 
Tue Nov 15 15:00:23 2011
DIA0 started with pid=8, OS id=26718 
Tue Nov 15 15:00:23 2011
MMAN started with pid=9, OS id=26720 
Tue Nov 15 15:00:23 2011
DBW0 started with pid=10, OS id=26722 
Tue Nov 15 15:00:23 2011
LGWR started with pid=11, OS id=26724 
Tue Nov 15 15:00:23 2011
CKPT started with pid=12, OS id=26726 
Tue Nov 15 15:00:23 2011
SMON started with pid=13, OS id=26728 
Tue Nov 15 15:00:23 2011
RECO started with pid=14, OS id=26730 
Tue Nov 15 15:00:23 2011
MMON started with pid=15, OS id=26732 
Tue Nov 15 15:00:23 2011
MMNL started with pid=16, OS id=26734 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/oracle
Tue Nov 15 15:00:23 2011
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 2578048199
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Nov 15 15:00:33 2011
drop database
ORA-12719 signalled during: drop database...
Tue Nov 15 15:00:47 2011
Stopping background process MMNL
Stopping background process MMON
Starting background process MMON
Tue Nov 15 15:00:49 2011
MMON started with pid=15, OS id=26788 
Starting background process MMNL
Tue Nov 15 15:00:49 2011
MMNL started with pid=16, OS id=26790 
ALTER SYSTEM enable restricted session;
Tue Nov 15 15:01:06 2011
drop database
Deleted file /opt/oracle/oradata/t1/system01.dbf
Deleted file /opt/oracle/oradata/t1/sysaux01.dbf
Deleted file /opt/oracle/oradata/t1/undotbs01.dbf
Deleted file /opt/oracle/oradata/t1/users01.dbf
Deleted file /opt/oracle/oradata/t1/redo01.log
Deleted file /opt/oracle/oradata/t1/redo02.log
Deleted file /opt/oracle/oradata/t1/redo03.log
Deleted file /opt/oracle/oradata/t1/temp01.dbf
Deleted file /opt/oracle/product/11.2.0/db_1/dbs/snapcf_t1.f
Shutting down archive processes
Archiving is disabled
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
USER (ospid: 26761): terminating the instance
Instance terminated by USER, pid = 26761
Tue Nov 15 15:01:18 2011
Deleted file /opt/oracle/oradata/t1/control01.ctl
Deleted file /opt/oracle/fast_recovery_area/t1/control02.ctl
Completed: drop database
Shutting down instance (abort)
License high water mark = 1
Tue Nov 15 15:01:32 2011
Instance shutdown complete

三、后续工作
1、清除相关日志trace文件$ORACLE_BASE/diag
2、删除fast_recovery_area文件$ORACLE_BASE/fast_recovery_area
3、删除归档日志(根据配置)
4、删除/etc/oratab中关于该数据库的记录(t1:/opt/oracle/product/11.2.0/db_1:N)

四、补充说明
1、在能够使用dbca删除数据库的情况下，应该选择dbca，这个删除的更加干净
2、dbca删除数据库也需要清理部分文件(如：归档日志)
3、如果对数据库的存储结构比较了解，可以人工关闭数据库后，手工删除相关文件
4、drop database使用于10g及其以上版本

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

评论功能已关闭。

17813235971