11 Replies Latest reply: Oct 8, 2012 10:17 AM by 954935 RSS

    a problem to drop database and its backups.

    954935
      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)
          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
            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
               
              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....
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              all right, it is dropped after 15 minutes

                              SQL> SQL> DROP DATABASE;


                              Database dropped.



                              thank god, so tough.

                              thank you very much all