ORA-16649: possible failover to another database prevents this database from being opened

We have two-node primary RAC database & two-node standby RAC database. For the business testing purpose, they will want both side of the database in the READ WRITE Mode. So we have decided to disabled the log shipping and made the standby database as a new primary database using failover database.

During the standby failover process, we made the existing primary database down. We have successfully failover the standby database as a new primary database. Once the process completed, we have disabled the DG configuration.

When we try to start the old primary database, we go the below error message. After analyzing further, we found the DG configuration file has been update from the standby database DG configuration file. Since the standby database become a new primary. So it has update the DG configuration file in the old primary database.

ORA-16649: possible failover to another database prevents this database from being opened

SOLUTION

After disabling the DG_BROKER_START=FALSE;  the old primary database able to start in the READ WRITE mode.

SQL> startup force

ORACLE instance started.

Total System Global Area 7.2958E+10 bytes

Fixed Size                  2235808 bytes

Variable Size            1.9059E+10 bytes

Database Buffers         5.3687E+10 bytes

Redo Buffers              209866752 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from being opened

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

SQL> alter database open;

Database altered.

ORA-16649: possible failover to another database prevents this database from being opened

We have two node primary & standby RAC. It is in sync and i have to split the primary database & standby database.

I have shutdown the primary database and went to the standby database server.

I logged in as DGMGRL and performed a fail over to the standby. Now the standby database become a primary database. Once i complete the failover; i have disabled the DG configuration using the DG broker.

When i try to started the old primary which i have shutdown before performing a failover to the standby. I got a error message saying  “ORA-16649: possible failover to another database prevents this database from being opened”

SQL> startup force

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 7.2958E+10 bytes

Fixed Size                  2235808 bytes

Variable Size            1.9059E+10 bytes

Database Buffers         5.3687E+10 bytes

Redo Buffers              209866752 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from being opened

WHEN I CHECKED THE DB ROLE; it is primary

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

Then i managed to find that database control file get the information from the DG broker config file. So i have disable the config file on the primary database.

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

-         After disabling the DG broker; i can able to open the database without any issue.

SQL> alter database open;

Database altered.

index Rebuild - Progress & Index creation Progress

To identify the index rebuild progress.

i have used the oracle schema to identify the session which are active in nature.

Note: For some reason V$SESSION_LONGOPS doesn't show the progress of index Rebuild process. use the below query for index rebuild. index creation can be seen in the below query and other query with the
V$SESSION_LONGOPS

SQL> SELECT MESSAGE FROM V$SESSION_LONGOPS WHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME='BHUVAN' AND STATUS='ACTIVE') ORDER BY START_TIME;

MESSAGE

-----------------------------------------------------------------------------------Table Scan:  (stale or locked) obj# 31940: 1426408 out of 1426408 Blocks done

Table Scan:  BHUVAN.EMP: 2388523 out of 2388523 Blocks done

Sort Output:  : 235280 out of 235280 Blocks done

Table Scan:  BHUVAN.EMP: 154318 out of 2388523 Blocks done

-- i have merge active session with the V$SESSION_LONGOPS view to retrieve progress of index creation with the index creation syntax.

SQL> col a.sid format 9999

SQL> select a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow

  2  from v$session a ,  v$sql b, v$session_longops c

  3  where a.sid=c.sid

  4  and a.sql_address = b.address

  5  and a.sql_address = c.sql_address

and status  = 'ACTIVE';

 SID

SQL_TEXT

TARGET                                                                SOFAR  TOTALWORK      STILL    TILLNOW

---------------------------------------------------------------- ---------- ------ 366

CREATE INDEX "EMP~EXT" ON "EMP" ("CLIENT", "BPEXT") PCTFREE 10 INITRANS 002 TABLESPACE PBHUVAN COMPRESS 2 STORAGE (INITIAL 0000000064 K NEXT 0000001024 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED PCTINCREASE 0000 FREELISTS 001)

BHUVAN.EMP                                                        894046    2388523        563        337

Hope this help you..... Happy Learning

Increasing ASM memory parameter

Note: if you are huge pages then automatic memory management is not support. please check that before using AMM.


Today I have increase the ASM memory on two environment. I have noticed huge difference in the ASM behaviour.

#1 (bhuora002/  bhuora003).

I have increase the ASM memory through command prompt.

Alter system set memory_max_target=2G scope=spfile sid=’*’;

Alter system set memory_target=2G scope=spfile sid=’*’;

Behaviour: When I try to bounce the GRID, ASM start without any issues.

#2 ( bhuora004/  bhuora005).

I have increase the ASM memory through command prompt.

Alter system set sga_max_target=2G scope=spfile sid=’*’;

Alter system set sga_target=2G scope=spfile sid=’*’;

Behaviour: When I try to bounce the GRID, ASM is not start and it has thrown error message. I taken the backup of spfile and change the values in the init file.  Once I have modified the values, I have used the same pfile to start the ASM instance(mount stage). Once it is mount, I have created the spfile from the pfile which I have used.This spfile set in the all the places.  Then I have stopped the ASM instance & bounced the GRID.

 So please use the memory_target & memory_max_target parameters while trying to increase the ASM memory parameters.

ORA-00376: file 6 cannot be read at this time

When I try to start a standby database in the read only mode, I have been thrown the below error message

oracle BHU_1> srvctl start database -d BHU_B

PRCR-1079 : Failed to start resource ora.BHU_b.db

CRS-5017: The resource action "ora.BHU_b.db start" encountered the following error:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193'

Process ID: 9165

Session ID: 3497 Serial number: 1

. For details refer to "(:CLSN00107:)" in "/oracle/GRID/11203/log/bhurac01/agent/crsd/oraagent_oracle/oraagent_oracle.log"

It is an undo tablespace (datafile) which is throwing the error and I have checked the datafile it is in the recovery mode.

select file#,name,status,enabled from v$datafile where file#=6;

6 +BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193                                                                                                   RECOVER READ WRITE

SO I HAVE FOLLOWED BELOW PROCEDURE TO OVERCOME THIS PROBLEM

#1

1 A)  Stop the apply process in the standby database

DGMGRL> edit database 'BHU_B' set state='APPLY-OFF';

Succeeded.

2 B)  It is RAC Database, kept only one instance in mount stage and other instance are in the offline mode(shutdown)

3

  C)  It is standby database (undo tablespace-datafile). So I am not able to do offline

#2 – Take a online backup of datafile through RMAN

RMAN> copy datafile 6 to '+BHU_B_DATA1';

Starting backup at 25-JUL-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1842 instance=BHU_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193

output file name=+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 tag=TAG20120725T124447 RECID=69 STAMP=789569388

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:05

Finished backup at 25-JUL-12

Starting Control File and SPFILE Autobackup at 25-JUL-12

piece handle=/oracle/BHU/11203/dbs/c-72629545-20120725-01 comment=NONE

Finished Control File and SPFILE Autobackup at 25-JUL-12

#3 I doing a rename through RMAN itself, there is no need of using the RENAME command in the sqlplus

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089"

RMAN> exit

#4 when I check the status of the datafile, it looks in the RECOVER MODE. So I cant open the database in the READ-ONLY MODE.

SQL> select name,status from v$datafile where file#=6;

NAME                                                 STATUS

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

+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089  RECOVER

#5 Started the Recover through DG Broker

DGMGRL> edit database 'BHU_B' set state='APPLY-ON';

Succeeded.

#6 Monitor the apply Lag

I could see that the system is using the old archive log to recover the datafile.

Once the recovery is completed, you can open the database in the read only mode.

I have see the status of the datafile; it is in the online mode

SQL> select name,status from v$datafile where file#=6;

NAME                                  STATUS

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

+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 ONLINE

change the ASM spfile in 11gR2

How to change the ASM spfile in 11gR2

Logical steps to change the ASM spfile

1. Create intermediate pfile from the current spfile or pfile
2. Create spfile in a new disk group from the intermediate pfile
3. Restart the HA stack to verify that ASM starts up fine with moved spfile
4. Remove the original spfile

You can verify whether you are using the correct spfile for the ASM in the cluster environment.

Multiple way to check the spfile details in 11gR2

#1

oracle +ASM1> asmcmd spget

+OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957

#2

oracle +ASM1 > gpnptool get

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="11" ClusterUId="3aebd9e0996e5f57bf5fa6a0accabc43" ClusterName="bhurac1a" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net2" IP="10.217.11.16" Adapter="bond4" Use="cluster_interconnect"/><gpnp:Network id="net1" Adapter="bond2" IP="10.218.8.0" Use="public"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="/dev/oracleasm/disks" SPFile="+OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>1ZtgbzWQAuybhO0J12R2X5D+gMc=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>A1Bx+lPu1QSsxGYrRJ5jVbhJ/oVkP8DcKxoCgV90gLk7/m4CztcatcRftHSDvg92z/0HzEog7AGltl0pDZZLMgA9sglWUop/GOPkzF1jxO9I7qbjQjeqLOoS79+XXV9M9LQ8KtosNvGE50VdE2tswdWc2IDJ8AelhL9wNCabBwg=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

#3

oracle +ASM1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 23 12:22:00 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile

NAME                                 TYPE                                                  VALUE

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

spfile                               string  +OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957

If you want to create a spfile from the existing spfile with some modification then you need to take backup from existing one.

SQL> create pfile='/home/oracle/init+ASM.ora' from spfile;

In the below case, my spfile is located in the environment and it is NOT picked up by the cluster during the startup.

So I am creating the pfile from spfile which is located in the diskgroup.

SQL> create pfile='/home/oracle/init+ASM.ora' from spfile='+OCR_VOTE/bhurac1a/ASMPARAMETERFILE/REGISTRY.253.757697363';

If you need modify any parameter in the ASM parameter, we can modify it.

Create a new spfile from the pfile

SQL> create spfile=’+OCR_VOTE’ from pfile='/home/oracle/init+ASM.ora';

Once the spfile is create, if you check spfile location in the cluster environment. it will show the new file.

You can verify using the below command. Note: THIS SPFILE WILL BE INCLUDING TO ALL THE CLUSTER NODES IN THE ENVIRONMENT.

#1 set the ASM environment values

$ asmcmd spget

(Or)

$ gpnptool get

Restart the cluster and you could see the cluster is picking up the new spfile.

Global sqlplus setting in oracle database & client

I had a situation to change the global array size setting and i found that there is a wonderful option available to change the default value of sqlplus setting on the client & database side.


go to $ORACLE_HOME/sqlplus/admin folder
you will find a file called "glogin.sql" or "login.sql" depends on the oracle version.
you can add/modify the default value to the new value.


Example:





SQL> show array
arraysize 15


After adding it in the glogin.sql file


$ more glogin.sql

--
-- Copyright (c) 1988, 2004, Oracle Corporation.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command
--
-- USAGE
--   This script is automatically run
--
set arraysize 2500

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 29 14:10:49 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show array

arraysize 2500

SQL>

Difference between Conventional path Export & Direct path Export

Conventional path Export. 
Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file. 

 Direct path Export. 
When using a direct path Export, the data is read from disk directly into the export session's program global area (PGA): the rows are transferred directly to the Export session's private buffer. This also means that the SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file. 

. The parameter DIRECT specifies whether you use the direct path Export (DIRECT=Y) or the conventional path Export (DIRECT=N).

 You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export.  Your exact performance gain depends upon the following factors: 
- DB_BLOCK_SIZE 
- the types of columns in your table 
- your I/O layout (the drive receiving the export file should be separate from the disk drive where the database files reside) 

For example, invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%. This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH

LIMITATIONS

1)  A Direct path Export does not influence the time it takes to Import the data. That is, an export file created using direct path Export or Conventional path Export, will take the same amount of time to Import. 

2) You cannot use the DIRECT=Y parameter when exporting in transportable tablespace mode.  You can use the DIRECT=Y parameter when exporting in full, user or table mode

3) The parameter QUERY applies ONLY to conventional path Export. It cannot be specified in a direct path export (DIRECT=Y).

4) A Direct path Export can only export the data when the NLS_LANG environment variable of the session who is invoking the export, is equal to the database character set. If NLS_LANG is not set (default is AMERICAN_AMERICA.US7ASCII) and/or NLS_LANG is different, Export will display the warning EXP-41 and abort with EXP-0.

ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type

ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type

I have 3 node RAC system. I am trying to convert from single to the RAC system. I have created undo tablespace for instance 2 & 3. I am having thrown the below error messages.

SQL> startup

ORACLE instance started.

Total System Global Area 1219334144 bytes

Fixed Size                  2227824 bytes

Variable Size             620757392 bytes

Database Buffers          587202560 bytes

Redo Buffers                9146368 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30012: undo tablespace 'UNDO_2' does not exist or of wrong type

Process ID: 6920

Session ID: 67 Serial number: 5

Note: you might the get this error message when you have specified the wrong undo tablespace for the particular instance in the rac environment or single instance environment. In that case, you have to create a pfile and you have modify the undo_tablespace value.

I have login to the Instance-1 and try to drop the undo tablespace which has been created for instance-2 & instance-3

SQL> drop tablespace UNDO_2 including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace UNDO_3 including contents and datafiles;

Tablespace dropped.

Then I have used the CREATE UNDO TABLESPACE option to create the tablespace for Instance – 2 & 3.

An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.

SQL> create undo tablespace UNDO_2 datafile '+BHU_A_DATA1' size 4096M reuse;

Tablespace created.

SQL> create undo tablespace UNDO_3 datafile '+BHU_A_DATA1' size 4096M reuse;

Tablespace created.

I have modified the undo tablespace for the Instance 2 & 3 in the spfile

SQL>  alter system set undo_tablespace='UNDO_2' scope=spfile sid='BHU_2';

System altered.

SQL> alter system set undo_tablespace='UNDO_3' scope=spfile sid='BHU_3';

System altered.

After the modification, I am trying to open the instance-2 & 3. it opens with out any issues.

SQL> startup

ORACLE instance started.

Total System Global Area 1219334144 bytes

Fixed Size                  2227824 bytes

Variable Size             620757392 bytes

Database Buffers          587202560 bytes

Redo Buffers                9146368 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter undo

NAME                                 TYPE        VALUE

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

_in_memory_undo                      boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer         900

undo_tablespace                      string        UNDO_2

SQL>

I hope this resolve your issue and happy learning!!!

ORA-16737: the redo transport service for standby database 'BHU_B" has an error

ORA-16737: the redo transport service for standby database "BHU_B" has an error

Error message

DGMGRL> show database verbose 'BHU_A';

Database – BHU_A

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    BHU_1

    BHU_2

    BHU_3

      Error: ORA-16737: the redo transport service for standby database "BHU_B" has an error

  Database Warning(s):

    ORA-16629: database reports a different protection level from the protection mode

#1

CHECK: when you have the above problem, you will get the protection mode in the    Primary & standby v$database.protection_level shows  as "RESYNCHRONIZATION"

select protection_mode, protection_level from v$database; 

#2

CHECK: whether you are getting any in the archive location 

select dest_id,status,error from v$archive_dest;

#3

CHECK: Check whether online redo log are configured in the primary & standby database properly, it includes size & accessibility 

Note: primary &standby online redo log should be same

select group#,thread#,sequence#,bytes,archived,status from v$log;  

#4

CHECK: Check whether standby redo log are configured in the primary &standby database properly, it includes size & accessibility 

Note: primary &standby standby redo log should be same 

select member from v$logfile where type='STANDBY'; 

#5

CHECK: Check parameters are configured properly; some times instance parameters have a different value. Ex: some common parameter will have different value for each instance in the cluster database. You need to check on the primary &standby cluster database environment.

#6

Check whether maximum Availability is enabled, when you have LogXptMode is synchronization

SYMP:

ORA-16629: database reports a different protection level from the protection mode

In DG Broker

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

#7 check your password with the setting
A)
1) check for the "sec_case_sensitive_logon" parameter.
2) if the problem exist, create the password file with ignorecase option in the orapwd password creation.
3) after recreating the password, restart both the primary & standby database.

B)

DGMGRL> show database verbose 'BHU_A' LogXptStatus;
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS
            BHU1_1             BHU1_B
            BHU1_2             BHU1_B ORA-16191: Primary log shipping client not logged on standby

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