ORA-01586 ORA-39701 database must be mounted EXCLUSIVE and not open for this operation

Dropping the database :
 
Mount the database in restrict mode:
 SQL> startup mount restrict;
ORACLE instance started.


Database mounted.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbm1

SQL> drop database;

Database dropped.

What if you get the below error:


SQL> startup mount  restrict;
ORACLE instance started.


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

Even the startup upgrade will fail:

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2232640 bytes
Variable Size             704646848 bytes
Database Buffers         2348810240 bytes
Redo Buffers              151146496 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 46501
Session ID: 652 Serial number: 3


Then mostly likely you are destroying a RAC database and you need to change the cluster_database=FALSE.

SQL> alter system set cluster_database=FALSE scope=spfile;


System altered.

SQL> startup mount restrict;
ORACLE instance started.


Redo Buffers              151146496 bytes
Database mounted.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbm1

SQL> drop database;

Database dropped.

ORA-38729: Not enough flashback database log data to do FLASHBACK.

I have faced an interesting issue a month before. When I try to perform a flashback database, I was getting the below error message.

I had enough flashback logs but still I have been thrown the below error message. 

Error message

RMAN> FLASHBACK DATABASE TO SCN 69979801;

Starting flashback at 10-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1252 instance=RMAN> FLASHBACK DATABASE TO SCN 69979801;

Starting flashback at 10-NOV-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1252 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1377 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1502 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1628 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=1753 instance=BHU_1 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=1876 instance=BHU_1 device type=DISK


starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 11/10/2011 16:42:04
ORA-38729: Not enough flashback database log data to do FLASHBACK.

RMAN> 

BELOW ERROR MESSAGE FOR THE SAME ISSUE
In the last error you had, the problem was that the archive logs weren't available, so you got an error

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 39221435 to SCN 39240345
ORA-38761: redo log sequence 56 in thread 1, incarnation 7 could not be accessed 

This is slightly different than if the flashback log is not available
ORA-38729: Not enough flashback database log data to do FLASHBACK.

My environment è we had 2 node RAC database. When I try to flashback the database but I got the above error.

Did the flashback is enabled for the database

SQL> select flashback_on from gv$database;

FLASHBACK_ON
------------------
YES

YES

What is the size & retention set for the flashback?

SQL> show parameter db_rec

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 270G
db_recycle_cache_size big integer 0

SQL> show parameter db_flash

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file string
db_flash_cache_size big integer 0
db_flashback_retention_target integer 1440  à 1 day

When I try to check the flashback log, do I have enough flashback logs to perform a flashback?

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
84854010 09-NOV-11 1440 3698974720 935731200

We can able to check, till the second of last flashback log that is present in the flashback log.

SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss') from v$flashback_database_log;

TO_CHAR(OLDEST_FL
-----------------
11/09/11 22:00:12

What is the size occupied by the flashback logs

SQL> select space_used/(1024*1024),space_limit/(1024*1024) from v$recovery_file_dest;

SPACE_USED/(1024*1024) SPACE_LIMIT/(1024*1024)
---------------------- -----------------------
3709 276480

Then I come to know that flashback database command requires both flashback logs and archive logs for period which we mention in the “FLASHBACK DATABASE SCN/TIME/ SEQUENCE XXXXXXXXXX”.

DIFFERENT OPTION OF PERFORMING FLASHBACK

SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 2/24);

SQL> FLASHBACK DATABASE TO SCN n;

SQL> FLASHBACK DATABASE TO SEQUENCE=M THREAD=N;

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2012-01-25 13:54:00';

SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2012-01-25 13:54:00', 'YYYY-MM-DD HH24:MI:SS');

Flashback database use the flashback logs to bring the database files to a SCN before the time/SCN you want to flashback. This is an approximate point based on the flashback snapshots taken to the flashback logs. Oracle then uses the archives to apply redo to get to the exact SCN or time you want to flashback.

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#CFHCEIHB

"When you use Flashback Database to rewind a database to a past target time, the command determines which blocks changed after the target time and restores them from the flashback logs. The database restores the version of each block that is immediately before the target time. The database then uses redo logs to reapply changes that were made after these blocks were written to the flashback logs.

Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. For example, if the flashback retention target is 1 week, then you must ensure that online and archived redo logs that contain all changes for the past week are accessible. In practice, redo logs are typically needed much longer than the flashback retention target to support point-in-time recovery."

Listener supports no services in RAC

I have 2 node RAC system, when try to connect to the database using the TNS-Entry I am got the below error message

oracle> sqlplus system/manager@KMM1

ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

When I checked the listener status, it was display as below and specified as no service are running

KMMora01[KMM1_1]>lsnrctl stat LSNR_VIPB_KMM1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-FEB-2012 11:27:14

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_VIPB_KMM1)))

STATUS of the LISTENER

------------------------

Alias                     LSNR_VIPB_KMM1

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                09-FEB-2012 11:01:32

Uptime                    0 days 0 hr. 25 min. 42 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/KMM1/11202/network/admin/listener.ora

Listener Log File         /oracle/KMM1/diag/tnslsnr/KMMora01/lsnr_vipb_KMM1/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LSNR_VIPB_KMM1)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.17)(PORT=1524)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.65)(PORT=1524)))

The listener supports no services

The command completed successfully

I try to do “alter system register” and try multiple things. But nothing work out for me.

Reason for no service are displayed in the listener status, No service are register with the listener. To register the service in the listener, we need to configure local_listener,remote_listener & listener_networks properly

Note: I am configuring the listener_networks, because I use a second IP or different IP for the data guard services.

Please find same local_listener, remote_listener & listener_network parameters

alter system set listener_networks='((name=KMM1_n1)(local_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KMMora01-dg-vip)(PORT=1521)))) (remote_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KMMora02-dg-vip)(PORT=1521)))))' sid='KMM1_1' scope=spfile;

alter system set listener_networks='((name=KMM1_n2)(local_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KMMora02-dg-vip)(PORT=1521)))) (remote_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KMMora01-dg-vip)(PORT=1521)))))' sid='KMM1_2' scope=spfile;

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KMMora01-vip)(PORT=1524)))' sid='KMM1_1' scope=spfile;

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KMMora02-vip)(PORT=1524)))' sid='KMM1_2' scope=spfile;

alter system set remote_listener='racoraKMMa-scan:1529' SID='ZE1_1' scope=spfile;

alter system set remote_listener='racoraKMMa-scan:1529' SID='ZE1_2' scope=spfile;

Once you restart the database, if you see the status of the listener. We should see the service up and running

If you still feel that service are running, then issue the below command on each instance

SQL> ALTER SYSTEM REGISTER;

KMMora01[KMM1_1]>lsnrctl stat LSNR_VIPB_KMM1

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-FEB-2012 12:56:12

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNR_VIPB_KMM1)))

STATUS of the LISTENER

------------------------

Alias                     LSNR_VIPB_KMM1

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                09-FEB-2012 12:55:29

Uptime                    0 days 0 hr. 0 min. 43 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/GRID/11202/network/admin/listener.ora

Listener Log File         /oracle/BASE/diag/tnslsnr/KMMora01/lsnr_vipb_KMM1/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LSNR_VIPB_KMM1)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.17)(PORT=1524)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.21.13.65)(PORT=1524)))

Services Summary...

Service "KMM1_B" has 1 instance(s).

  Instance "KMM1_1", status READY, has 1 handler(s) for this service...

Service "KMM1_B.UK.CENTRICAPLC.COM" has 1 instance(s).

  Instance "KMM1_1", status READY, has 1 handler(s) for this service...

The command completed successfully

KMMora01[KMM1_1]>

Hope this help you. Happy learning