This discussion is archived
11 Replies Latest reply: Oct 8, 2012 8:17 AM by 954935 RSS

a problem to drop database and its backups.

954935 Newbie
Currently Being Moderated
HI,

database: 10.2.0.4.0
OS: AIX

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2084336 bytes
Variable Size 385876496 bytes
Database Buffers 1174405120 bytes
Redo Buffers 48246784 bytes
Database mounted.
SQL> alter system enable restricted session;
sql> exit

rman target / catalog rman@rmancatalog


RMAN> drop database including backups;

starting full resync of recovery catalog
full resync complete
database name is "xxxx" and DBID is xxxxx

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 09/28/2012 15:40:12
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

RMAN> sql 'alter system enable restricted session';


sql statement: alter system enable restricted session

RMAN>
RMAN> drop database including backups;

database name is "xxxxx" and DBID is xxxxx

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of drop db & bck command at 09/28/2012 15:41:37
RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.


what is wrong?


thank you

Edited by: 951932 on Sep 28, 2012 1:49 PM
  • 1. Re: a problem to drop database and its backups.
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    Database must be closed and mounted EXCLUSIVE and RESTRICTED.
    DB should not be Open.
    shutdown
    startup restrict mount

    Read http://aprakash.wordpress.com/2010/02/18/drop-database-using-rman/
  • 2. Re: a problem to drop database and its backups.
    954935 Newbie
    Currently Being Moderated
    thank you for response, anyway, same problem.....


    SQL> startup restrict mount

    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed Size 2084336 bytes
    Variable Size 385876496 bytes
    Database Buffers 1174405120 bytes
    Redo Buffers 48246784 bytes
    Database mounted.
    SQL> SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    /home/oracle>rman target / catalog rman@rcat


    Recovery Manager: Release 10.2.0.4.0 - Production on Fri Sep 28 16:50:16 2012

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

    connected to target database: xxx (DBID=xxxx)
    recovery catalog database Password:
    connected to recovery catalog database

    RMAN> drop database including backups;

    starting full resync of recovery catalog
    full resync complete
    database name is "xxx" and DBID is xxx
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of drop db & bck command at 09/28/2012 16:50:43
    RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

    RMAN> sql 'alter system enable restricted session';

    sql statement: alter system enable restricted session

    RMAN> drop database including backups;

    database name is "xxx" and DBID is xxx
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of drop db & bck command at 09/28/2012 16:51:09
    RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

    Edited by: 951932 on Sep 28, 2012 2:54 PM
  • 3. Re: a problem to drop database and its backups.
    Shivananda Rao Guru
    Currently Being Moderated
     
    SQL> startup mount exclusive;
    ORACLE instance started.
    
    Total System Global Area 1610612736 bytes
    Fixed Size 2084336 bytes
    Variable Size 385876496 bytes
    Database Buffers 1174405120 bytes
    Redo Buffers 48246784 bytes
    Database mounted.
    SQL> alter system enable restricted session;
    sql> exit
    
    rman target / catalog rman@rmancatalog
    Make sure you are connecting to the right target database. Do as:
    export ORACLE_SID=<oracle SID>
    sqlplus / as sysdba
    startup force mount
    sql>alter system enable restricted session;
    sql>exit
    rman target / catalog sys/<pwd>@<catdb>
    rman>drop database including backups;
  • 4. Re: a problem to drop database and its backups.
    Aman.... Oracle ACE
    Currently Being Moderated
    There can't be any other reason except what Shivananda has mentioned. Are you sure that you are connected to the right database?

    Aman....
  • 5. Re: a problem to drop database and its backups.
    Niket Kumar Pro
    Currently Being Moderated
    Hope this will work for you.....



    SQL>
    SQL>
    SQL>
    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area 3390558208 bytes
    Fixed Size 2180464 bytes
    Variable Size 1862273680 bytes
    Database Buffers 1509949440 bytes
    Redo Buffers 16154624 bytes
    SQL> alter database mount exclusive;

    Database altered.

    SQL> alter system enable restricted session;

    System altered.

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    C:\Users\j>rman target /

    Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 17:11:43 2012

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

    connected to target database: ORACLE (DBID=1654533908, not open)

    RMAN> drop database including backups;

    database name is "ORACLE" and DBID is 1654533908

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

    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=63 device type=DISK
    specification does not match any backup in the repository
  • 6. Re: a problem to drop database and its backups.
    954935 Newbie
    Currently Being Moderated
    Hello,

    this is what I did, anyway, still failed. thank you very much.

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup restrict mount
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed Size 2084336 bytes
    Variable Size 385876496 bytes
    Database Buffers 1174405120 bytes
    Redo Buffers 48246784 bytes
    Database mounted.
    SQL> drop database;
    drop database
    *
    ERROR at line 1:
    ORA-01586: database must be mounted EXCLUSIVE and not open for this operation


    SQL> alter system enable restricted session;

    System altered.

    SQL> drop database;
    drop database
    *
    ERROR at line 1:
    ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

    ### try another way.



    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed Size 2084336 bytes
    Variable Size 385876496 bytes
    Database Buffers 1174405120 bytes
    Redo Buffers 48246784 bytes
    SQL> alter database mount exclusive;


    Database altered.

    SQL> SQL> alter system enable restricted session;


    System altered.

    SQL> SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [oracle][gamfcs]/home/oracle>rman target /


    Recovery Manager: Release 10.2.0.4.0 - Production on Mon Oct 8 09:29:36 2012

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

    connected to target database: GAMFCS (DBID=2082941597)

    RMAN>
    RMAN> drop database including backups;


    database name is "GAMFCS" and DBID is 2082941597
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of drop db & bck command at 10/08/2012 09:29:57
    RMAN-06941: Database must be closed and mounted EXCLUSIVE and RESTRICTED.

    Edited by: 951932 on Oct 8, 2012 7:33 AM
  • 7. Re: a problem to drop database and its backups.
    Fran Guru
    Currently Being Moderated
    SQL> startup restrict mount
    SQL> alter system enable restricted session;
    You set "restrict" mode two times, but never exclusive mode. So it's normal that you get "ORA-01586: database must be mounted EXCLUSIVE and not open for this operation"

    please follow the steps that they told you:

    $ sqlplus / as sysdba
    1) shutdown immediate;
    2) startup mount exclusive restrict;
    4) drop database;


    also, you can use rman:
    http://docs.oracle.com/cd/B19306_01/backup.102/b14192/maint005.htm

    Edited by: Fran on 08-oct-2012 7:50
  • 8. Re: a problem to drop database and its backups.
    954935 Newbie
    Currently Being Moderated
    Hello,

    Did like this, still failed. thank you!!!


    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount exclusive restrict;
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed Size 2084336 bytes
    Variable Size 385876496 bytes
    Database Buffers 1174405120 bytes
    Redo Buffers 48246784 bytes
    Database mounted.

    SQL> drop database;
    drop database
    *
    ERROR at line 1:
    ORA-01586: database must be mounted EXCLUSIVE and not open for this operation
  • 9. Re: a problem to drop database and its backups.
    Fran Guru
    Currently Being Moderated
    are your database in RAC?
    If yes, try:

    SQL> alter system set cluster_database=false scope=spfile sid='database_to_drop_name';

    later try again dropping database

    Edited by: Fran on 08-oct-2012 8:05
  • 10. Re: a problem to drop database and its backups.
    954935 Newbie
    Currently Being Moderated
    thank you,

    this is not a RAC, but it has its own phsical standby db before, now standby has been deleted, and the only left is to delete primary db.

    doing this now..

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP NOMOUNT RESTRICT;
    ORACLE instance started.

    Total System Global Area 1610612736 bytes
    Fixed Size 2084336 bytes
    Variable Size 385876496 bytes
    Database Buffers 1174405120 bytes
    Redo Buffers 48246784 bytes
    SQL> ALTER DATABASE MOUNT;


    Database altered.

    SQL> SQL> DROP DATABASE;

    .....

    (seems like works, but now hanging there already for 10 minutes..)
  • 11. Re: a problem to drop database and its backups.
    954935 Newbie
    Currently Being Moderated
    all right, it is dropped after 15 minutes

    SQL> SQL> DROP DATABASE;


    Database dropped.



    thank god, so tough.

    thank you very much all

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points