Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

Oracle手动删库

02 Jan 2019 » Oracle

Step 1.获得相关文件的信息

1)控制文件

SYS@oradb> col status format a10;
SYS@oradb> col name format a60;
SYS@oradb> select status,name from v$controlfile;

STATUS     NAME
---------- ------------------------------------------------------------
           /u01/app/oradata/oradb/control01.ctl
           /u01/app/fast_recovery_area/oradb/control02.ctl

2)数据文件

SYS@oradb> select * from v$dbfile;

     FILE# NAME
---------- ------------------------------------------------------------
         4 /u01/app/oradata/oradb/users01.dbf
         3 /u01/app/oradata/oradb/undotbs01.dbf
         2 /u01/app/oradata/oradb/sysaux01.dbf
         1 /u01/app/oradata/oradb/system01.dbf

3)日志文件

SYS@oradb> col member format a40;
SYS@oradb> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE  /u01/app/oradata/oradb/redo03.log        NO
         2         ONLINE  /u01/app/oradata/oradb/redo02.log        NO
         1         ONLINE  /u01/app/oradata/oradb/redo01.log        NO

Step 2.停止监听服务

[oracle@db ~]$ lsnrctl stop

Step 3.关闭数据库实例

SYS@oradb> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4:启动到数据库实例到mount状态

SYS@oradb> startup mount exclusive restrict;

Step 5: 修改参数为允许受限的会话模式

SQL> alter system enable restricted session;

Step 6:使用drop database命令来清除数据库

SYS@oradb> drop database;

查看alert日志的情况,如下:

[oracle@db oradata]$ alert
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
USER (ospid: 7403): terminating the instance
Instance terminated by USER, pid = 7403
Deleted file /u01/app/oradata/oradb/control01.ctl
Deleted file /u01/app/fast_recovery_area/oradb/control02.ctl
Completed: drop database
Shutting down instance (abort)
License high water mark = 1
Sat Dec 29 15:27:41 2018
Instance shutdown complete

备注:关于drop database命令的解释 deletes all database files, both ASM and non-ASM

When you issue this statement, Oracle Database drops the database and deletes all control files and datafiles listed in the control file. If the database used a server parameter file (spfile), it is also deleted.

Archived logs and backups are not removed, but you can use Recovery Manager (RMAN) to remove them. If the database is on raw disks, this statement does not delete the actual raw disk special files.

DROP DATABASE

The DROP DATABASE command deletes these files from operating system.

* Datafiles

* Online Redo Log Files

* Controlfiles

* SPFILE (if it exists)

The DROP DATABASE command does not delete the following files:

* init.ora (text version of the Oracle initialization file)

* password file

* entries in listener files

* entries in oratab file 

enjoy!just for fun