oracle 清库drop,学习笔记:如何手动删除Oracle数据库实例 分析日志了解drop datab

天萃荷净

深入了解删除Oracle数据库实例drop database的详细过程与内部原理

一、通过Oracle sql操作删除Oracle实例

[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

二、查看分析Oracle 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

三、删除Oracle实例后续工作

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)

四、drop database补充说明

1、在能够使用dbca删除数据库的情况下，应该选择dbca，这个删除的更加干净

2、dbca删除数据库也需要清理部分文件(如：归档日志)

3、如果对数据库的存储结构比较了解，可以人工关闭数据库后，手工删除相关文件

4、drop database使用于10g及其以上版本

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:如何手动删除Oracle数据库实例 分析日志了解drop datab