Drop Database through RMAN

-

Most  of the time  we  generally  use  DBCA  to  delete  the  database  because  it  is easy  and  simple . But , there are  some  scenario's  where we  have to drop  database  without using  the graphics  i,e, without DBCA . In such case  , we can  delete  or drop  the  database either by   manually  or  by using  sql*plus  or  RMAN prompt . As  compare  to  sql*plus , rman  is  much  more effective  because  it  consume  less   time  and secondly  we  can   delete  the  archivelogs  and backups  also . Starting  with Oracle  10gR1 onwards,  we  can  drop  a database  and  remove all  its records  from  the  rman catalog . 


There are basically 4 syntax available to drop the database using RMAN

1.) Drop Database : This command  deletes the datafiles, logfiles, Controlfiles and Spfile. If we do not want to delete backups then we can use this command.

2.) Drop Database Noprompt : When “NOPROMPT”  is specified RMAN does not prompt for the confirmation before deleting the database. It delete the above files as in case of drop database.

3.)DROP DATABASE INCLUDING BACKUPS : This command delete the datafiles, ogfiles,Controlfiles and Spfile plus the archivelogs and backup pieces  generated by RMAN.

4.) DROP DATABASE INCLUDING BACKUPS NOPROMPT  : When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

Let's have a look on the following steps to drop the Database using RMAN


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

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2253744 bytes
Variable Size             322964560 bytes
Database Buffers           79691776 bytes
Redo Buffers                4284416 bytes
Database mounted.


SQL> alter system enable restricted session;

System altered.



SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@primaryDB ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Sep 14 08:51:21 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORAPROD3 (DBID=1859619209, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "ORAPROD3" and DBID is 1859619209

Do you really want to drop all backups and the database (enter YES or NO)? YES



login as: root
root@192.168.161.128's password:
Last login: Thu Sep 12 22:24:00 2019 from 192.168.161.1
[root@primaryDB ~]# su - oracle
[oracle@primaryDB ~]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ORAHDFC1:/m01/app/oracle/product/11.2.0.4/db_1:N
ORAPROD3:/m01/app/oracle/product/11.2.0.4/db_1:N
[oracle@primaryDB ~]$ . oraenv
ORACLE_SID = [oracle] ? ORAPROD3
The Oracle base remains unchanged with value /m01/app/oracle
[oracle@primaryDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Sep 14 08:44:41 2019

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2253744 bytes
Variable Size             322964560 bytes
Database Buffers           79691776 bytes
Redo Buffers                4284416 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     31
Next log sequence to archive   33
Current log sequence           33
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area  409194496 bytes
Fixed Size                  2253744 bytes
Variable Size             322964560 bytes
Database Buffers           79691776 bytes
Redo Buffers                4284416 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@primaryDB ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Sep 14 08:51:21 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORAPROD3 (DBID=1859619209, not open)

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "ORAPROD3" and DBID is 1859619209

Do you really want to drop all backups and the database (enter YES or NO)? yes

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
specification does not match any backup in the repository

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORAPROD3
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    16      A 01-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_06/o1_mf_1_16_gnkxcftc_.arc

2       1    17      A 03-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_06/o1_mf_1_17_gnkxcko7_.arc

3       1    18      A 04-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_06/o1_mf_1_18_gnkxcojq_.arc

4       1    1       A 06-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_1_gnq72oqz_.arc

5       1    2       A 08-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_2_gnq79zp0_.arc

6       1    3       A 08-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_3_gnq7nd4k_.arc

7       1    4       A 08-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_4_gnq7ty1p_.arc

8       1    5       A 08-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_22/o1_mf_1_5_goxl8304_.arc

9       1    6       A 22-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_22/o1_mf_1_6_goxn04v0_.arc

10      1    7       A 22-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_22/o1_mf_1_7_goxn0mjj_.arc

11      1    8       A 22-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_23/o1_mf_1_8_goyo36jh_.arc

12      1    9       A 23-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_24/o1_mf_1_9_gp19hzj2_.arc

13      1    10      A 24-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_10_gp9684oy_.arc

14      1    11      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_11_gp9763wl_.arc

15      1    12      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_12_gp97725h_.arc

16      1    13      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_13_gp97brff_.arc

17      1    14      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_14_gp97btr1_.arc

18      1    15      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_15_gp97c0z1_.arc

19      1    16      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_16_gp97cn87_.arc

20      1    17      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_17_gp97cqd9_.arc

21      1    18      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_18_gp99tq22_.arc

22      1    19      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_19_gp99tqg6_.arc

23      1    20      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_20_gp99tr2s_.arc

24      1    21      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_21_gpbnhvgn_.arc

25      1    22      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_22_gpbnvqp4_.arc

26      1    23      A 27-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_30/o1_mf_1_23_gpjx2j1o_.arc

27      1    24      A 30-AUG-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_04/o1_mf_1_24_gpy94wdf_.arc

28      1    25      A 04-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_05/o1_mf_1_25_gq0xot71_.arc

29      1    26      A 05-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_08/o1_mf_1_26_gq8nbyvw_.arc

30      1    27      A 08-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_08/o1_mf_1_27_gq8oyo1l_.arc

31      1    28      A 08-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_11/o1_mf_1_28_gqjj5f9n_.arc

32      1    29      A 11-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_11/o1_mf_1_29_gqjkxpnt_.arc

33      1    30      A 11-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_11/o1_mf_1_30_gqlbfznp_.arc

34      1    31      A 11-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_12/o1_mf_1_31_gqny54rx_.arc

35      1    32      A 12-SEP-19
        Name: /m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_14/o1_mf_1_32_gqrpv37c_.arc

deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_06/o1_mf_1_16_gnkxcftc_.arc RECID=1 STAMP=1015578313
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_06/o1_mf_1_17_gnkxcko7_.arc RECID=2 STAMP=1015578317
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_06/o1_mf_1_18_gnkxcojq_.arc RECID=3 STAMP=1015578320
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_1_gnq72oqz_.arc RECID=4 STAMP=1015752117
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_2_gnq79zp0_.arc RECID=5 STAMP=1015752353
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_3_gnq7nd4k_.arc RECID=6 STAMP=1015752684
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_08/o1_mf_1_4_gnq7ty1p_.arc RECID=7 STAMP=1015752893
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_22/o1_mf_1_5_goxl8304_.arc RECID=8 STAMP=1017008731
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_22/o1_mf_1_6_goxn04v0_.arc RECID=9 STAMP=1017010526
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_22/o1_mf_1_7_goxn0mjj_.arc RECID=10 STAMP=1017010541
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_23/o1_mf_1_8_goyo36jh_.arc RECID=11 STAMP=1017044426
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_24/o1_mf_1_9_gp19hzj2_.arc RECID=12 STAMP=1017130839
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_10_gp9684oy_.arc RECID=13 STAMP=1017389677
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_11_gp9763wl_.arc RECID=14 STAMP=1017390620
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_12_gp97725h_.arc RECID=15 STAMP=1017390650
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_13_gp97brff_.arc RECID=16 STAMP=1017390768
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_14_gp97btr1_.arc RECID=17 STAMP=1017390770
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_15_gp97c0z1_.arc RECID=18 STAMP=1017390777
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_16_gp97cn87_.arc RECID=19 STAMP=1017390796
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_17_gp97cqd9_.arc RECID=20 STAMP=1017390799
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_18_gp99tq22_.arc RECID=21 STAMP=1017393327
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_19_gp99tqg6_.arc RECID=22 STAMP=1017393327
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_20_gp99tr2s_.arc RECID=23 STAMP=1017393328
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_21_gpbnhvgn_.arc RECID=24 STAMP=1017437011
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_27/o1_mf_1_22_gpbnvqp4_.arc RECID=25 STAMP=1017437391
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_08_30/o1_mf_1_23_gpjx2j1o_.arc RECID=26 STAMP=1017642418
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_04/o1_mf_1_24_gpy94wdf_.arc RECID=27 STAMP=1018080763
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_05/o1_mf_1_25_gq0xot71_.arc RECID=28 STAMP=1018167337
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_08/o1_mf_1_26_gq8nbyvw_.arc RECID=29 STAMP=1018419905
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_08/o1_mf_1_27_gq8oyo1l_.arc RECID=30 STAMP=1018421555
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_11/o1_mf_1_28_gqjj5f9n_.arc RECID=31 STAMP=1018677765
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_11/o1_mf_1_29_gqjkxpnt_.arc RECID=32 STAMP=1018679571
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_11/o1_mf_1_30_gqlbfznp_.arc RECID=33 STAMP=1018737433
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_12/o1_mf_1_31_gqny54rx_.arc RECID=34 STAMP=1018823165
deleted archived log
archived log file name=/m01/app/oracle/fast_recovery_area/ORAPROD3/archivelog/2019_09_14/o1_mf_1_32_gqrpv37c_.arc RECID=35 STAMP=1018946780
Deleted 35 objects


database name is "ORAPROD3" and DBID is 1859619209




database name is "ORAPROD3" and DBID is 1859619209
database dropped




Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more