Changing the Oracle ASM Disk Group Name

Changing the ASM disk group name from +IMPHAL to +IMPHAL_DG

Monday Morning (between 9-11am ) is definitely not the right time to do any installation.... Ahmmm

One fine Monday morning I did the gird installation and created the disk group name as IMPHAL.As per our customer's standard , any diskgroup name should/must have a suffix of _DG.

From ASM command line or from ASMCA if you try to drop the disk you will get the following error

SQL> select name from v$asm_diskgroup;

NAME

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

IMPHAL

SYDNEY_DG

MELB_DG

SQL> drop diskgroup IMPHAL including contents;

drop diskgroup IMPHAL including contents

*

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15027: active use of diskgroup "IMPHAL" precludes its dismount

To resolve this above problem , use the following

SQL>

SQL> alter diskgroup IMPHAL dismount force;

Diskgroup altered.

SQL> drop diskgroup IMPHAL force including contents;

Diskgroup dropped.

And its gone....

Check the disk group details again

SQL> select name from v$asm_diskgroup;

NAME

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

SYDNEY_DG

MELB_DG

Configuring Enterprise Manager DB Control in RAC environment

Steps to configure Enterprise Manager DB Control in RAC

-bash-4.1$ ./emca -deconfig dbcontrol db -repos drop -cluster

STARTED EMCA at May 20, 2012 9:31:57 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: IMPHAL
Service name: IMPHAL
Listener ORACLE_HOME [ /u01/app/11.2.0/grid ]:
Password for SYS user:
Password for SYSMAN user:
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
May 20, 2012 9:32:19 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/IMPHAL/emca_2012_05_20_09_31_56.log.
May 20, 2012 9:32:20 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
May 20, 2012 9:32:43 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
May 20, 2012 9:35:45 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 20, 2012 9:35:48 AM
-bash-4.1$ ./emca -config dbcontrol db -repos create -cluster

STARTED EMCA at May 20, 2012 9:36:50 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: IMPHAL
Service name: IMPHAL
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/11.2.0/grid ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: evdclus
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/app/11.2.0/grid ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
May 20, 2012 9:37:36 AM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
WARNING: Error during db connection : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

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

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0

Database instance hostname ................ Listener ORACLE_HOME ................ /u01/app/11.2.0/grid
Listener port number ................ 1521
Cluster name ................ evdclus
Database unique name ................ IMPHAL
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u01/app/11.2.0/grid
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
May 20, 2012 9:37:44 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/IMPHAL/emca_2012_05_20_09_36_49.log.
May 20, 2012 9:37:46 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 20, 2012 9:43:56 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
May 20, 2012 9:44:01 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
May 20, 2012 9:45:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
May 20, 2012 9:45:04 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/app/oracle/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_IMPHAL1_IMPHAL to remote nodes ...
May 20, 2012 9:45:05 AM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/app/oracle/product/11.2.0/oc4j/j2ee/OC4J_DBConsole_IMPHAL2_IMPHAL to remote nodes ...
May 20, 2012 9:45:08 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/app/oracle/product/11.2.0/IMPHAL1_IMPHAL to remote nodes ...
May 20, 2012 9:45:10 AM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/app/oracle/product/11.2.0/IMPHAL2_IMPHAL to remote nodes ...
May 20, 2012 9:45:14 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
May 20, 2012 9:45:31 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 20, 2012 9:46:20 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 20, 2012 9:46:20 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://IMPHAL1:1158/em <<<<<<<<<<<
May 20, 2012 9:46:23 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

IMPHAL          IMPHAL1          IMPHAL1
IMPHAL          IMPHAL2          IMPHAL1


May 20, 2012 9:46:23 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/IMPHAL1_IMPHAL/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 20, 2012 9:46:23 AM
-bash-4.1$ ./emca -displayConfig dbcontrol -cluster

STARTED EMCA at May 20, 2012 9:46:39 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: IMPHAL
Service name: IMPHAL
Do you wish to continue? [yes(Y)/no(N)]: Y
May 20, 2012 9:46:48 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/IMPHAL/emca_2012_05_20_09_46_39.log.
May 20, 2012 9:46:51 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

IMPHAL          IMPHAL1          IMPHAL1
IMPHAL          IMPHAL2          IMPHAL1


Enterprise Manager configuration completed successfully
FINISHED EMCA at May 20, 2012 9:46:51 AM
-bash-4.1$ ./emca -reconfig dbcontrol -cluster -EM_NODE IMPHAL2 -EM_NODE_LIST IMPHAL2

STARTED EMCA at May 20, 2012 9:47:21 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database unique name: IMPHAL
Service name: IMPHAL
Do you wish to continue? [yes(Y)/no(N)]: Y
May 20, 2012 9:47:34 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/IMPHAL/emca_2012_05_20_09_47_20.log.
May 20, 2012 9:47:36 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
May 20, 2012 9:47:39 AM oracle.sysman.emcp.EMAgentConfig performDbcReconfiguration
INFO: Propagating /u01/app/oracle/product/11.2.0/IMPHAL2_IMPHAL/sysman/config/emd.properties to remote nodes ...
May 20, 2012 9:47:40 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 20, 2012 9:48:03 AM oracle.sysman.emcp.EMDBPostConfig performDbcReconfiguration
INFO: Database Control started successfully
May 20, 2012 9:48:03 AM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************  Current Configuration  ****************
 INSTANCE            NODE           DBCONTROL_UPLOAD_HOST
----------        ----------        ---------------------

IMPHAL          IMPHAL1          IMPHAL1
IMPHAL          IMPHAL2          IMPHAL2


Enterprise Manager configuration completed successfully
FINISHED EMCA at May 20, 2012 9:48:03 AM
-bash-4.1$ ./emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://IMPHAL1:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/IMPHAL1_IMPHAL/sysman/log

Node2

-bash-4.1$ ./emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://IMPHAL2:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/IMPHAL2_IMPHAL/sysman/log

Oracle RAC ssh configuration

Steps to configure ssh for configuring Oracle RAC on Unix environment


Add user and groups
# groupadd -g 100 oinstall
# groupadd -g 101 dba
# useradd -u 102 -g oinstall -G dba -m -d /home/oracle oracle
# passwd xyz#$123


$ mkdir ~/.ssh
$ chmod 755 ~/.ssh
$ /usr/bin/ssh-keygen -t rsa
$ /usr/bin/ssh-keygen -t dsa
$ touch ~/.ssh/authorized_keys
$ cd .ssh
$ ssh node1 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
$ ssh node1 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys

Run the below command on node2 logging as oracle user.

$ mkdir ~/.ssh
$ chmod 755 ~/.ssh
$ /usr/bin/ssh-keygen -t rsa
$ /usr/bin/ssh-keygen -t dsa
$ touch ~/.ssh/authorized_keys
$ cd .ssh
$ ssh node2 cat /home/oracle/.ssh/id_rsa.pub >> authorized_keys
$ ssh node2 cat /home/oracle/.ssh/id_dsa.pub >> authorized_keys



• Run the below command on node1 (From where OUI will be run) logging as oracle user.

$ cd .ssh
$ ssh node2 cat /home/oracle/.ssh/authorized_keys >> authorized_keys
$ scp authorized_keys oracle@node2:/home/oracle/.ssh/
$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add

DataGuard Broker configuration

Steps to configure Oracle 11gR2 DataGuard Broker

Primary database: IMPHAL
Secondary database : IMPHALDG

On both the primary and secondary database start the DG_BROKER process

SQL > alter system set DG_BROKER_START=TRUE SCOPE=TRUE


Edit the listener.ora on both the nodes


Create the configuration

• On the primary node,connect to dgmgrl

            DGMGRL> connect sys/Password@IMPHAL
            Connected

            Here Primary is the SID of the primary/production database

• Check the configuration.Since the DG broker has not been configured, it will give the following error

          DGMGRL> show configuration

          ORA-16532: Data Guard broker configuration does not exist

          Configuration details cannot be determined by DGMGRL

• Create the DG Broker configuration

             Primary Database

             DGMGRL> create configuration 'IMPHAL'

              as

              IMPHAL database is 'IMPHAL'

              connect identifier is 'IMPHAL'

              ;

            Configuration "IMPHAL" created with IMPHAL database "IMPHAL"

            Add the secondary database

           DGMGRL> add database 'IMPHALDG'
           as
           connect identifier is 'IMPHALDG';

           Database "IMPHALDG" added

• Show the DG Broker configuration

          DGMGRL> show configuration

          Configuration - IMPHAL

          Protection Mode: MaxPerformance

          Databases:

         IMPHAL   - IMPHAL database

        IMPHALDG - Physical standby database

         Fast-Start Failover: DISABLED

        Configuration Status:

         DISABLED

• Enable the DG broker and check the configuration configuration

      DGMGRL> enable configuration;

      Enabled.

       DGMGRL> show configuration;

      Configuration - IMPHAL

      Protection Mode: MaxPerformance

      Databases:

      IMPHAL   - IMPHAL database

     IMPHALDG - Physical standby database

     Fast-Start Failover: DISABLED

    Configuration Status:

    SUCCESS

• Check the database status

    DGMGRL>  show database verbose 'IMPHAL';

Database - IMPHAL

  Role:            IMPHAL

  Intended State:  TRANSPORT-ON

  Instance(s):

    IMPHAL

  Properties:

    DGConnectIdentifier             = 'IMPHAL'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '+DATA_DG1, +DATA_DG2, +RECO_DG1, +RECO_DG2'

    LogFileNameConvert              = '+DATA_DG1, +DATA_DG2, +RECO_DG2, +RECO_DG2'

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    SidName                         = 'IMPHAL'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=IMPHAL_DGMGRL)(INSTANCE_NAME=IMPHAL)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '+RECO_DG1'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'IMPHAL_%t_%s_%r.arch'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL> show database verbose 'IMPHALDG';

Database - IMPHALDG

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   1 minute 48 seconds

  Apply Lag:       3 minutes 41 seconds

  Real Time Query: OFF

  Instance(s):

    IMPHAL

  Properties:

    DGConnectIdentifier             = 'IMPHALDG'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'auto'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '+DATA_DG2, +DATA_DG1, +RECO_DG2, +RECO_DG1'

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    SidName                         = 'IMPHAL'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=IMPHALDG_DGMGRL)(INSTANCE_NAME=IMPHAL)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '+RECO_DG2'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = 'IMPHAL_%t_%s_%r.arch'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

• Performing a switch-over

Performing a switchover

Note: In this case, currently the Primary Database is IMPHAL and the Standby database is IMMPHALDG.

DGMGRL> switchover to 'IMPHALDG'

Performing switchover NOW, please wait...

New primary database "IMPHALDG" is opening...

Operation requires shutdown of instance "IMPHAL" on database "IMPHAL"

Shutting down instance "IMPHAL"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "IMPHAL" on database "IMPHAL"

Starting instance "IMPHAL"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "IMPHALDG"