drop database in 11gR2 with RAC

I am planning to remove my cluster database which is running on 11gR2

Stop the entire cluster environment  

bhuora01[BHU_1]>srvctl stop database -d BHU_a

Start only one instance to edit the cluster_database parameter to FALSE

bhuora01[BHU_1]>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 10 16:03:03 2012

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2.6924E+10 bytes

Fixed Size                  2241104 bytes

Variable Size            1.3086E+10 bytes

Database Buffers         1.3824E+10 bytes

Redo Buffers               11227136 bytes

Database mounted.

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shutdown abort;

ORACLE instance shut down.

Now starting only one instance after editing below parameter CLUSTER_DATABASE parameters to FALSE

SQL> startup mount exclusive restrict

ORACLE instance started.

Total System Global Area 2.6924E+10 bytes

Fixed Size                  2241104 bytes

Variable Size            1.3086E+10 bytes

Database Buffers         1.3824E+10 bytes

Redo Buffers               11227136 bytes

Database mounted.

Make sure whether you have started in the restricted mode

SQL>  select logins,parallel from v$instance;

LOGINS     PAR

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

RESTRICTED NO

When you issue this command, this will drop the database including datafiles, control files, redo log files & archive log files

SQL> drop database;

Database dropped.

To drop the database including the backup, we can go for the below option

RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

listener supports no services

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@bhu1

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

bhuora01[BHU1_1]>lsnrctl stat LSNR_VIPB_BHU1

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_BHU1)))

STATUS of the LISTENER

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

Alias                     LSNR_VIPB_BHU1

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/BHU1/11202/network/admin/listener.ora

Listener Log File         /oracle/BHU1/diag/tnslsnr/bhuora01/lsnr_vipb_BHU1/alert/log.xml

Listening Endpoints Summary...

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

  (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=BHU1_n1)(local_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora01-dg-vip)(PORT=1521)))) (remote_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora02-dg-vip)(PORT=1521)))))' sid='BHU1_1' scope=spfile;

alter system set listener_networks='((name=BHU1_n2)(local_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora02-dg-vip)(PORT=1521)))) (remote_listener= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bhuora01-dg-vip)(PORT=1521)))))' sid='BHU1_2' scope=spfile;

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

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

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

alter system set remote_listener='racorabhua-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;

bhuora01[BHU1_1]>lsnrctl stat LSNR_VIPB_BHU1

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_BHU1)))

STATUS of the LISTENER

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

Alias                     LSNR_VIPB_BHU1

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/bhuora01/lsnr_vipb_BHU1/alert/log.xml

Listening Endpoints Summary...

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

  (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 "BHU1_B" has 1 instance(s).

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

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

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

The command completed successfully

bhuora01[BHU1_1]>

Hope this help you. Happy learning

RENAMEDISK & DELETEDISK through ASMLIB in 11gR2

RENAME/DELETE DISKLABEL through ASMLIB

We can rename a DISKLABEL in asm through two ways

1)      RENAMING BY PROVIDING  DISKLABEL NAME

In the below example, we are rename a disk label by providing the CURRENT DISKLABEL name to NEW DISKLABEL name

[root@ bhuora01]#  /etc/init.d/oracleasm force-renamedisk TEMP5 TEMP6

Renaming disk "TEMP5" to "TEMP6":                          [  OK  ]

 [root@bhuora01]# oracleasm querydisk /dev/mapper/VOTE_05

Device "/dev/mapper/VOTE_05" is marked an ASM disk with the label "TEMP6"

2)      RENAMING BY PROVIDING THE DISK

In the below example, we are rename a disk label by providing the disk and new name to be allocated for the disk

[root@bhuora01]#  /etc/init.d/oracleasm force-renamedisk /dev/mapper/VOTE_05 TEMP5

Renaming disk "/dev/mapper/VOTE_05" to "TEMP5":            [  OK  ]

[root@bhuora01]# oracleasm querydisk /dev/mapper/VOTE_05

Device "/dev/mapper/VOTE_05" is marked an ASM disk with the label "TEMP5"

We can DELETE a DISKLABEL in asm through two ways

1)      DELETE ASM DISK LABEL BY PROVIDING  DISKLABEL NAME

In below example we are check the disk to find the DISKLABEL and we are deleting a disklabel by providing the disklabel name

[root@bhuora01]# oracleasm querydisk /dev/mapper/VOTE_05

Device "/dev/mapper/VOTE_05" is marked an ASM disk with the label "TEMP5"

 [root@bhuora01]# oracleasm deletedisk TEMP5

Clearing disk header: done

Dropping disk: done

2)      DELETE ASM DISK LABEL BY PROVIDING THE DISK

In below example, we are deleting a disklabel by providing the disk and we are check the disk status after deleting the disklabel

[root@bhuora01]# oracleasm deletedisk /dev/mapper/VOTE_05

Clearing disk header: done

Dropping disk: done

[root@bhuora01]#  oracleasm querydisk /dev/mapper/VOTE_05

Device "/dev/mapper/VOTE_05" is not marked as an ASM disk

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."

hope this helps you & happy learning

PRVF-4007 : User equivalence check failed

when you are running pre-check using runcluvfy.sh utility.



./runcluvfy.sh stage -pre crsinst -n  bhuora01,bhuora02  -verbose > /tmp/bhuora01.txt

#  if you get the below issue

PRVF-4007 : User equivalence check failed

Please check whether ssh is working from the node-1 to node-2 and at the same time check whether you are able to do a ssh to the same host.

from node#1

[oracle@ bhuora01  grid]$ ssh  bhuora02  date --> to other nodes in the cluster
Wed Feb  1 16:29:38 GMT 2012

[oracle@ bhuora01  grid]$ ssh  bhuora01  date --> to the same node
Wed Feb  1 16:29:39 GMT 2012

if it is working, precheck should complete with out any issues. check for the file permission.

authorized_keys should have 600 permission from the corresponding user.