Oracle: April 2014

Monday, April 7, 2014

Manually create a Database in an Existing Oracle 11g RAC - ASM - Grid Infrastructure

Objective: Manually create (through command line interface) an Oracle 11g RAC Database in an existing Oracle 11g Grid Infrastructure - ASM Environment. The new RAC database will be called "mani" and it will have 2 instances called "mani1" and "mani2".

Method Used: Create as a single instance standalone database in one node and then convert it into a RAC database running on a 2 node cluster.

Environment: Oracle 11g Grid Infrastructure, ASM, Oracle 11g RAC Database, 64 bit Oracle Linux Server 6.3, VMware Hypervisor ESXi 5.5

Existing RAC Setup: 2 nodes called "rac1" and "rac2", RAC database "babu" has 2 instances called "babu1" and "babu2" (currently shutdown). A shared disk / DISKGROUP to place the database files of the new database.

1) Start up the Cluster Resources in both nodes (if down)

In node 1, as "root" user

[oracle@rac1 ~]$ su root
Password:

[root@rac1 oracle]# . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle

[root@rac1 oracle]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[root@rac1 oracle]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
ora.DSK_GRP_02.dg
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
ora.LISTENER.lsnr
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
ora.asm
               ONLINE ONLINE       rac1                     Started
               ONLINE ONLINE       rac2                     Started
ora.gsd
               OFFLINE OFFLINE      rac1
               OFFLINE OFFLINE      rac2
ora.net1.network
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
ora.ons
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       rac2
ora.LISTENER_SCAN2.lsnr
      1        ONLINE ONLINE       rac1
ora.LISTENER_SCAN3.lsnr
      1        ONLINE ONLINE       rac1
ora.babu.db
      1        OFFLINE OFFLINE                               Instance Shutdown
      2        OFFLINE OFFLINE                               Instance Shutdown
ora.cvu
      1        ONLINE ONLINE       rac1
ora.oc4j
      1        ONLINE ONLINE       rac1
ora.rac1.vip
      1        ONLINE ONLINE       rac1
ora.rac2.vip
      1        ONLINE ONLINE       rac2
ora.scan1.vip
      1        ONLINE ONLINE       rac2
ora.scan2.vip
      1        ONLINE ONLINE       rac1
ora.scan3.vip
      1        ONLINE ONLINE       rac1

[root@rac1 oracle]# crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1
ora....P_02.dg ora....up.type ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac2
ora....N2.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1
ora.babu.db    ora....se.type OFFLINE   OFFLINE
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    OFFLINE   OFFLINE
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    OFFLINE   OFFLINE
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2
ora.scan1.vip ora....ip.type ONLINE    ONLINE    rac2
ora.scan2.vip ora....ip.type ONLINE    ONLINE    rac1
ora.scan3.vip ora....ip.type ONLINE    ONLINE    rac1

Repeat above in node 2 as well

2) We will be using the new DISKGROUP "DSK_GRP_02" (that we created previously) for the new database, so lets make sure its available in both nodes.
In node 1 as "oracle" user

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysasm

SQL> ALTER DISKGROUP DSK_GRP_02 CHECK ALL;

Diskgroup altered.

SQL> ALTER DISKGROUP DSK_GRP_02 ADD DIRECTORY '+DSK_GRP_02/MANI';

Diskgroup altered

[oracle@rac1 ~]$ asmcmd ls -lt +DSK_GRP_02
Type Redund Striped Time             Sys Name
                                        N    MANI/
In node 2 as "oracle" user

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM2
The Oracle base has been set to /u01/app/oracle

[oracle@rac2 ~]$ asmcmd
ASMCMD> ls -l
State    Type    Rebal Name
MOUNTED EXTERN N      DATA/
MOUNTED EXTERN N      DSK_GRP_02/

3) In node 1 as "oracle" user, create necessary "oracle dump" directories, copy init.ora file from existing database (babu) and edit it:-

[oracle@rac1 ~]$ cd /u01/app/oracle/admin
[oracle@rac1 admin]$ mkdir mani
[oracle@rac1 admin]$ cd mani
[oracle@rac1 mani]$ mkdir adump
[oracle@rac1 mani]$ mkdir dpdump
[oracle@rac1 mani]$ mkdir hdump

Create "initmani1.ora" file by copying it from an existing database

[oracle@rac1 babu]$ cp /u01/app/oracle/admin/babu/pfile/init.ora.012014202025 $ORACLE_HOME/dbs/initmani1.ora

Content of edited "initmani1.ora" is as follows:-

audit_file_dest="/u01/app/oracle/admin/mani/adump"
audit_trail=none
# cluster_database=true
compatible=11.2.0.0.0
db_block_size=8192
db_create_file_dest="+DSK_GRP_02"
db_domain=localdomain
db_name="mani"
diagnostic_dest=/u01/app/oracle
dispatchers="(PROTOCOL=TCP) (SERVICE=maniXDB)"
memory_target=786432000
open_cursors=300
processes=150
remote_listener=rac-scan.localdomain:1521
remote_login_passwordfile=exclusive
sec_case_sensitive_logon=false
mani1.instance_number=1
mani2.instance_number=2
mani1.thread=1
mani2.thread=2
mani1.undo_tablespace=UNDOTBS1
mani2.undo_tablespace=UNDOTBS2

4) Create password file

[oracle@rac1 babu]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 babu

[oracle@rac1 dbs]$ export ORACLE_SID=mani1
[oracle@rac1 dbs]$ echo $ORACLE_SID
mani1

[oracle@rac1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

[oracle@rac1 dbs]$ ls -l
total 32
-rw-rw---- 1 oracle oinstall 1544 Feb 15 10:25 hc_babu1.dat
-rw-r----- 1 oracle oinstall   35 Jan 1 20:20 initbabu1.ora
-rw-r--r-- 1 oracle oinstall 555 Feb 15 11:03 initmani1.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu1
-rw-r----- 1 oracle oinstall 1536 Feb 15 11:13 orapwmani1

5) Listener and TNS files

[oracle@rac1 admin]$ srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

[oracle@rac1 admin]$ srvctl config listener
Name: LISTENER
Network: 1, Owner: oracle
Home:
End points: TCP:1521

[oracle@rac1 admin]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): rac2,rac1

[oracle@rac1 admin]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac1

[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin

[oracle@rac1 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-FEB-2014 11:31:46

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stat
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                15-FEB-2014 18:24:01
Uptime                    0 days 3 hr. 7 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.71)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@rac1 admin]$ more listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))        # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))        # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))        # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))        # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON        # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON        # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON        # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON        # line added by Agent

Edit "tnsnames.ora" file

[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin

[oracle@rac1 admin]$ more tnsnames.ora
BABU =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = babu.localdomain)
    )
)

BABU1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.81)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = babu.localdomain)
      (INSTANCE_NAME = babu1)
    )
)

BABU2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = babu.localdomain)
      (INSTANCE_NAME = babu2)

    )
)

MANI =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mani.localdomain)
    )
)

MANI1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.81)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mani.localdomain)
      (INSTANCE_NAME = mani1)

    )
)

MANI2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mani.localdomain)
      (INSTANCE_NAME = mani2)
    )
)

6) In node 1 as "oracle" user, create the database

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
The Oracle base has been set to /u01/app/oracle

[oracle@rac1 ~]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 babu

[oracle@rac1 ~]$ export ORACLE_SID=mani1

[oracle@rac1 ~]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 mani1

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size            2232472 bytes
Variable Size        524291944 bytes
Database Buffers    255852544 bytes
Redo Buffers            2621440 bytes

SQL> select * from V$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION     STARTUP_T STATUS    PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS       SHU DATABASE_STATUS    INSTANCE_ROLE        ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
          1 mani1
rac1.localdomain
11.2.0.3.0    22-FEB-14 STARTED    YES          0 STOPPED
ALLOSat    NO ACTIVE        UNKNOWN        NORMAL    NO

SQL> CREATE DATABASE "MANI"
    MAXLOGFILES 192    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE GROUP 1 SIZE 50M,
    GROUP 2 SIZE 50M
DATAFILE SIZE 300M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE SIZE 50M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
USER SYS IDENTIFIED BY ORACLE
USER SYSTEM IDENTIFIED BY ORACLE
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
NOARCHIVELOG;

Database created.

Create UNDO tablespace and run below post database creation scripts:-

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2048M;

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql

7) Control Files and Log Files changes

SQL> select value from v$parameter where name ='control_files';

VALUE
--------------------------------------------------------------------------------
+DSK_GRP_02/mani/controlfile/current.256.839537095

SQL> show parameter control_files

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_files                 string     +DSK_GRP_02/mani/controlfile/current.256.839537095

Add above value for the "control_files" in the initmani1.ora

vi /$ORACLE_HOME/dbs/initmani1.ora

control_files = '+DSK_GRP_02/mani/controlfile/current.256.839537095'

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DSK_GRP_02/mani/onlinelog/group_1.257.839537099
+DSK_GRP_02/mani/onlinelog/group_2.258.839537101

2 rows selected.

Thread 2 is for instance on 2nd node, see init parameter "mani2.thread=2"

SQL> alter database add logfile thread 2 group 3 size 50m, group 4 size 50m;

Database altered.

SQL> alter database enable public thread 2;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         13   52428800        512          1 NO INACTIVE                191937 22-FEB-14       199062 22-FEB-14
         2          1         14   52428800        512          1 NO CURRENT                 199062 22-FEB-14   2.8147E+14
         3          2          1   52428800        512          1 NO CURRENT                 209541 22-FEB-14   2.8147E+14 22-FEB-14
         4          2          0   52428800        512          1 YES UNUSED                       0                      0

4 rows selected.

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE +DSK_GRP_02/mani/onlinelog/group_1.257.839537099   NO
         2         ONLINE +DSK_GRP_02/mani/onlinelog/group_2.258.839537101   NO
         3         ONLINE +DSK_GRP_02/mani/onlinelog/group_3.265.839549313   NO
         4         ONLINE +DSK_GRP_02/mani/onlinelog/group_4.266.839549319   NO

4 rows selected.

8) Convert standalone database to RAC database

SQL> show parameter cluster_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

SQL> show parameter instance

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     1
instance_groups                      string
instance_name                        string      mani1
instance_number                      integer     1
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
parallel_server_instances            integer     1

SQL> shutdown immediate;

Uncomment below init parameter and start up the database

cluster_database=true

SQL> startup

SQL> show parameter cluster_database
show parameter instance

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

SQL> show parameter instance
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
active_instance_count                integer
cluster_database_instances           integer     2
instance_groups                      string
instance_name                        string      mani1
instance_number                      integer     1
instance_type                        string      RDBMS
open_links_per_instance              integer     4
parallel_instance_group              string
parallel_server_instances            integer     2Create the dictionary views needed for Oracle RAC databases.

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catclust.sql

9) Create spfile from pfile and make the database use it.

SQL> create spfile='+DSK_GRP_02/mani/spfilemani.ora' from pfile;

File created.

Shutdown, backup existing "initmani1.ora", update it to use the spfile we just created and startup the database.

SQL> shutdown immediate;
Database closed.

[oracle@rac1 dbs]$ ls -l
total 28
-rw-rw---- 1 oracle oinstall 1544 Feb 15 13:36 hc_babu1.dat
-rw-rw---- 1 oracle oinstall 1544 Feb 22 14:49 hc_mani1.dat
-rw-r----- 1 oracle oinstall   35 Jan 1 20:20 initbabu1.ora
-rw-r--r-- 1 oracle oinstall 655 Feb 22 14:26 initmani1.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu1
-rw-r----- 1 oracle oinstall 1536 Feb 15 11:13 orapwmani1

[oracle@rac1 dbs]$ mv /$ORACLE_HOME/dbs/initmani1.ora /$ORACLE_HOME/dbs/initmani1.ora.bkp

[oracle@rac1 dbs]$ vi /$ORACLE_HOME/dbs/initmani1.ora

SPFILE='+DSK_GRP_02/mani/spfilemani.ora'

[oracle@rac1 dbs]$ ls -l
total 32
-rw-rw---- 1 oracle oinstall 1544 Feb 15 13:36 hc_babu1.dat
-rw-rw---- 1 oracle oinstall 1544 Feb 22 14:49 hc_mani1.dat
-rw-r----- 1 oracle oinstall   35 Jan 1 20:20 initbabu1.ora
-rw-r--r-- 1 oracle oinstall   42 Feb 22 14:50 initmani1.ora
-rw-r--r-- 1 oracle oinstall 655 Feb 22 14:26 initmani1.ora.bkp
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu1
-rw-r----- 1 oracle oinstall 1536 Feb 15 11:13 orapwmani1

SQL> startup

Make sure the DB is using spfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DSK_GRP_02/mani/spfilemani.ora

SQL> select value from v$parameter where name='spfile';

VALUE
----------------------------------------------------------------------------------------------------
+DSK_GRP_02/mani/spfilemani.ora

1 row selected.

10) Copy the init and password file to node 2

[oracle@rac1 dbs]$ scp $ORACLE_HOME/dbs/initmani1.ora rac2:$ORACLE_HOME/dbs/initmani2.ora
initmani1.ora         100%   42     0.0KB/s   00:00

[oracle@rac1 dbs]$ scp $ORACLE_HOME/dbs/orapwmani1 rac2:$ORACLE_HOME/dbs/orapwmani2
orapwmani1        100% 1536     1.5KB/s   00:00

Check if below files are present in node 2

[oracle@rac2 dbs]$ ls -l
total 24
-rw-rw---- 1 oracle oinstall 1544 Feb 22 14:57 hc_babu2.dat
-rw-r----- 1 oracle oinstall   35 Jan 1 20:20 initbabu2.ora
-rw-r--r-- 1 oracle oinstall   42 Feb 22 15:07 initmani2.ora
-rw-r--r-- 1 oracle oinstall 2851 Jan 1 19:53 init.ora
-rw-r----- 1 oracle oinstall 1536 Jan 1 20:16 orapwbabu2
-rw-r----- 1 oracle oinstall 1536 Feb 22 15:07 orapwmani2

11) In node 2 as "oracle" user, create necessary "oracle dump" directories and start up the database:-

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ cd /$ORACLE_BASE/admin
[oracle@rac2 admin]$ mkdir mani
[oracle@rac2 admin]$ cd mani
[oracle@rac2 mani]$ mkdir adump
[oracle@rac2 mani]$ mkdir dpdump
[oracle@rac2 mani]$ mkdir hdump

[oracle@rac2 ~]$ echo $ORACLE_BASE $ORACLE_HOME $ORACLE_SID
/u01/app/oracle /u01/app/oracle/product/11.2.0/dbhome_1 mani2

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL> startup

SQL> col host_name for a20
SQL> col instance_name for a20
SQL> select instance_name,host_name from gv$instance;
INSTANCE_NAME         HOST_NAME
-------------------- --------------------
mani2             rac2.localdomain
mani1             rac1.localdomain

Check the participating instances:-

SQL> select * from v$active_instances;
INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
    1 rac1.localdomain:mani1
    2 rac2.localdomain:mani2

Also check the participating instances from node1:-

SQL> select * from v$active_instances;

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
          1 rac1.localdomain:mani1
          2 rac2.localdomain:mani2

2 rows selected.

SQL> col instance_name for a20
SQL> col host_name for a20
SQL> select instance_name,host_name from gv$instance;
INSTANCE_NAME        HOST_NAME
-------------------- --------------------
mani1                rac1.localdomain
mani2                rac2.localdomain

2 rows selected.

12) Add the new database in the cluster resource

- Shutdown the new database (both database instances)
- Cluster Resources in both nodes should be up and running

Perform the following from node1:-

[oracle@rac1 ~]$ su root
Password:
[root@rac1 oracle]# . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@rac1 oracle]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$ export ORACLE_SID=mani1
[oracle@rac1 ~]$ echo $ORACLE_SID $ORACLE_HOME $ORACLE_BASE
mani1 /u01/app/oracle/product/11.2.0/dbhome_1 /u01/app/oracle
[oracle@rac1 ~]$ srvctl add database -d mani -o $ORACLE_HOME -m localdomain
[oracle@rac1 ~]$ srvctl add instance -d mani -i mani1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d mani -i mani2 -n rac2

Check the status of new database:-

[root@rac1 oracle]# crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora.DATA.dg    ora....up.type 0/5    0/     ONLINE    ONLINE    rac1
ora....P_02.dg ora....up.type 0/5    0/     ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type 0/5    0/     ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac2
ora....N2.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   0/5    0/     ONLINE    ONLINE    rac1
ora.babu.db    ora....se.type 0/2    0/1    OFFLINE   OFFLINE
ora.cvu        ora.cvu.type   0/5    0/0    ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   0/5    0/     OFFLINE   OFFLINE
ora.mani.db    ora....se.type 0/2    0/1    OFFLINE   OFFLINE
ora....network ora....rk.type 0/5    0/     ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type 0/1    0/2    ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   0/3    0/     ONLINE    ONLINE    rac1
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    0/5    0/0    OFFLINE   OFFLINE
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac1
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    0/5    0/0    OFFLINE   OFFLINE
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2
ora.rac2.vip   ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac2
ora.scan1.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac2
ora.scan2.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac1
ora.scan3.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac1

[oracle@rac1 ~]$ srvctl status database -d mani
Instance mani1 is not running on node rac1
Instance mani2 is not running on node rac2
[oracle@rac1 ~]$ srvctl status asm -n rac1
ASM is running on rac1
[oracle@rac1 ~]$ srvctl status asm -n rac2
ASM is running on rac2
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani1
Instance mani1 is not running on node rac1
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani2
Instance mani2 is not running on node rac2

[oracle@rac1 ~]$ srvctl config database -d mani
Database unique name: mani
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mani
Database instances: mani1,mani2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed

[oracle@rac1 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-FEB-2014 08:34:32

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                18-FEB-2014 18:15:50
Uptime                    0 days 0 hr. 18 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.71)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully

13) Start the new database:-

[root@rac1 oracle]# srvstl start database -d mani

Again, check the status of new database:-

[root@rac1 oracle]# crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora.DATA.dg    ora....up.type 0/5    0/     ONLINE    ONLINE    rac1
ora....P_02.dg ora....up.type 0/5    0/     ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type 0/5    0/     ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac2
ora....N2.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   0/5    0/     ONLINE    ONLINE    rac1
ora.babu.db    ora....se.type 0/2    0/1    OFFLINE   OFFLINE
ora.cvu        ora.cvu.type   0/5    0/0    ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   0/5    0/     OFFLINE   OFFLINE
ora.mani.db    ora....se.type 0/2    0/1    ONLINE    ONLINE    rac1
ora....network ora....rk.type 0/5    0/     ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type 0/1    0/2    ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   0/3    0/     ONLINE    ONLINE    rac1
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    0/5    0/0    OFFLINE   OFFLINE
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac1
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    0/5    0/0    OFFLINE   OFFLINE
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2
ora.rac2.vip   ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac2
ora.scan1.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac2
ora.scan2.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac1
ora.scan3.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac1

[oracle@rac1 ~]$ srvctl status database -d mani
Instance mani1 is running on node rac1
Instance mani2 is running on node rac2
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani1
Instance mani1 is running on node rac1
[oracle@rac1 ~]$ srvctl status instance -d mani -i mani2
Instance mani2 is running on node rac2
[oracle@rac1 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-FEB-2014 08:50:10

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                18-FEB-2014 18:15:50
Uptime                    0 days 0 hr. 34 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.71)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.81)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "mani.localdomain" has 1 instance(s).
Instance "mani1", status READY, has 1 handler(s) for this service...
Service "maniXDB.localdomain" has 1 instance(s).
Instance "mani1", status READY, has 1 handler(s) for this service...
The command completed successfully

Log into the databases and check the status

[oracle@rac1 ~]$ export ORACLE_SID=mani1 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rac1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$ echo $ORACLE_SID $ORACLE_HOME $PATH
mani1 /u01/app/oracle/product/11.2.0/dbhome_1 /usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> select * from v$active_instances;

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
    1 rac1.localdomain:mani1
    2 rac2.localdomain:mani2

SQL> col host_name for a25
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -------------------------
mani1        rac1.localdomain

SQL> conn sys/*****@mani2 as sysdba

Connected.
SQL> select * from v$active_instances;

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
    1 rac1.localdomain:mani1
    2 rac2.localdomain:mani2

SQL> col host_name for a25
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -------------------------
mani2        rac2.localdomain

14) Shutdown the new database

[oracle@rac1 ~]$ export ORACLE_SID=mani1 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@rac1 ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@rac1 ~]$ srvctl stop database -d mani

[oracle@rac1 ~]$ srvctl status database -d mani
Instance mani1 is not running on node rac1
Instance mani2 is not running on node rac2

[oracle@rac1 ~]$ srvctl status instance -d mani -i mani1
Instance mani1 is not running on node rac1

[oracle@rac1 ~]$ srvctl status instance -d mani -i mani2
Instance mani2 is not running on node rac2

[oracle@rac1 ~]$ srvctl status instance -d mani -n rac1
Instance mani1 is not running on node rac1

[oracle@rac1 ~]$ su root
Password:

[root@rac1 oracle]# . oraenv
ORACLE_SID = [mani1] ? +ASM1
The Oracle base has been set to /u01/app/oracle

[root@rac1 oracle]# crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora.DATA.dg    ora....up.type 0/5    0/     ONLINE    ONLINE    rac1
ora....P_02.dg ora....up.type 0/5    0/     ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type 0/5    0/     ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac2
ora....N2.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type 0/5    0/0    ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   0/5    0/     ONLINE    ONLINE    rac1
ora.babu.db    ora....se.type 0/2    0/1    OFFLINE   OFFLINE
ora.cvu        ora.cvu.type   0/5    0/0    ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   0/5    0/     OFFLINE   OFFLINE
ora.mani.db    ora....se.type 0/2    0/1    OFFLINE   OFFLINE
ora....network ora....rk.type 0/5    0/     ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type 0/1    0/2    ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   0/3    0/     ONLINE    ONLINE    rac1
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    0/5    0/0    OFFLINE   OFFLINE
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac1
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    0/5    0/0    OFFLINE   OFFLINE
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2
ora.rac2.vip   ora....t1.type 0/0    0/0    ONLINE    ONLINE    rac2
ora.scan1.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac2
ora.scan2.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac1
ora.scan3.vip ora....ip.type 0/0    0/0    ONLINE    ONLINE    rac1

15) Check the alert log to see of everything looks okay for the new database "mani"

export ORACLE_SID=mani1 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin

[oracle@rac1 ~]$ tail -70 /u01/app/oracle/diag/rdbms/mani/mani1/trace/alert_mani1.log

The End...

Thursday, April 3, 2014

Manually Add a Shared Disk and Create a DISKGROUP in ASM

Objective: Below steps will explain how to manually add a shared disk, create and mount a DISKGROUP in ASM using the newly created shared disk.

Environment: Oracle 11g Grid Infrastructure, ASM, Oracle 11g RAC Database, 64 bit Oracle Linux Server 6.3, VMware Hypervisor ESXi 5.5

Existing RAC setup: 2 nodes called "rac1" and "rac2", RAC database "babu" has 2 instances called "babu1" and "babu2".

Hardware (VM) Configuration:

1) Follow Steps 4 through 17 in Section 2 of this link on how to create a Shared Disk.

OS / ASM Configuration:

2) In Node 1, connect as "root" and see if the newly added disk is visible:-

[root@rac1 oracle]# ls -l /dev/sd*
brw-rw---- 1 root disk 8, 0 Jan 20 13:19 /dev/sda
brw-rw---- 1 root disk 8, 1 Jan 20 13:19 /dev/sda1
brw-rw---- 1 root disk 8, 2 Jan 20 13:19 /dev/sda2
brw-rw---- 1 root disk 8, 16 Jan 20 13:19 /dev/sdb
brw-rw---- 1 root disk 8, 17 Jan 20 13:19 /dev/sdb1
brw-rw---- 1 root disk 8, 32 Jan 20 13:19 /dev/sdc

3) Create a new partition (in the new disk) using all available space as shown below:-

[root@rac1 oracle]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x7fb6e683.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

4) Once partition is created, list it to see if its visible:-

[root@rac1 oracle]# ls -l /dev/sd*
brw-rw---- 1 root disk 8, 0 Jan 20 13:19 /dev/sda
brw-rw---- 1 root disk 8, 1 Jan 20 13:19 /dev/sda1
brw-rw---- 1 root disk 8, 2 Jan 20 13:19 /dev/sda2
brw-rw---- 1 root disk 8, 16 Jan 20 13:19 /dev/sdb
brw-rw---- 1 root disk 8, 17 Jan 20 13:19 /dev/sdb1
brw-rw---- 1 root disk 8, 32 Jan 20 13:24 /dev/sdc
brw-rw---- 1 root disk 8, 33 Jan 20 13:24 /dev/sdc1

5) Create an ASM disk called "DISK2" using the newly partitioned/created disk:-

[root@rac1 oracle]# oracleasm createdisk DISK2 /dev/sdc1
Writing disk header: done
Instantiating disk: done

[root@rac1 oracle]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

[root@rac1 oracle]# oracleasm listdisks
DISK1
DISK2

6) Switch to "oracle" user and start the existing database "babu":-

[root@rac1 oracle]# su oracle

[oracle@rac1 ~]$ . oraenv

ORACLE_SID = [+ASM1] ? babu
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac1 ~]$ srvctl start database -d babu

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [babu] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

7) Connect to ASM instance 1 and check the mounted / participating ASM instances (optional step):-

[oracle@rac1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 13:50:13 2014

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> select * from v$active_instances;

INST_NUMBER INST_NAME
----------- ------------------------------------------------------------
      1 rac1.localdomain:+ASM1
      2 rac2.localdomain:+ASM2

8) Verify if the new disk is visible from the ASM instance 1:-

SQL> SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,NAME,FAILGROUP,PATH,FAILGROUP_TYPE FROM V$ASM_DISK;

MOUNT_S HEADER_STATU MODE_ST NAME       FAILGROUP PATH                       FAILGRO
------- ------------ ------- ---------- ---------- -------------------------- -------
CLOSED PROVISIONED ONLINE                        /dev/oracleasm/disks/DISK2 REGULAR
CACHED MEMBER       ONLINE DATA_0000 DATA_0000 /dev/oracleasm/disks/DISK1 REGULAR

9) Create DISKGROUP using the new disk:-

SQL> CREATE DISKGROUP DSK_GRP_02
        EXTERNAL REDUNDANCY
        DISK '/dev/oracleasm/disks/DISK2';

Diskgroup created.

10) See if the new DISKGROUP is visible from the ASM instance:-

SQL> SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,NAME,FAILGROUP,PATH,FAILGROUP_TYPE FROM V$ASM_DISK;

MOUNT_S HEADER_STATU MODE_ST NAME             FAILGROUP       PATH                       FAILGRO
------- ------------ ------- ---------------- --------------- -------------------------- -------
CACHED MEMBER       ONLINE DSK_GRP_02_0000 DSK_GRP_02_0000 /dev/oracleasm/disks/DISK2 REGULAR
CACHED MEMBER       ONLINE DATA_0000        DATA_0000       /dev/oracleasm/disks/DISK1 REGULAR

11) As "root" user, check the status. You will see its online in node 1, but offline (not mounted) in node2:-

[root@rac1 oracle]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
ora.DSK_GRP_02.dg
               ONLINE ONLINE       rac1
               OFFLINE OFFLINE      rac2

12) In Node2, login as "oracle" user and verify the status of the newly created DISKGROUP:-

From database instance 2:-

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
expThe Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ export ORACLE_SID=babu2
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 14:04:55 2014

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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE         TOTAL_MB    FREE_MB
------------ ------------------------------ ----------- ---------- ----------
           1 DATA                           CONNECTED         3592       1332
           0 DSK_GRP_02                     DISMOUNTED           0          0

From ASM instance 2:-

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM2
The Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 14:02:29 2014

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> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE         TOTAL_MB    FREE_MB
------------ -------------------- ----------- ---------- ----------
           1 DATA                 MOUNTED           3592       1332
           0 DSK_GRP_02           DISMOUNTED           0          0

13) Login as "root" user, enable and mount the new diskgroup:-

[root@rac2 oracle]# srvctl enable diskgroup -g DSK_GRP_02
PRCC-1010 : DSK_GRP_02 was already enabled
PRCR-1002 : Resource ora.DSK_GRP_02.dg is already enabled

[root@rac2 oracle]# srvctl start diskgroup -g DSK_GRP_02

[root@rac2 oracle]# crsctl stat resource -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2
ora.DSK_GRP_02.dg
               ONLINE ONLINE       rac1
               ONLINE ONLINE       rac2

From ASM instance 2:-

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                 STATE         TOTAL_MB    FREE_MB
------------ -------------------- ----------- ---------- ----------
           1 DATA                 MOUNTED           3592       1332
           2 DSK_GRP_02           MOUNTED           5114       5021

From database instance 2:-

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? babu
expThe Oracle base has been set to /u01/app/oracle
[oracle@rac2 ~]$ export ORACLE_SID=babu2
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 14:04:55 2014

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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;

GROUP_NUMBER NAME                           STATE         TOTAL_MB    FREE_MB
------------ ------------------------------ ----------- ---------- ----------
           1 DATA                           CONNECTED         3592       1332
           2 DSK_GRP_02                     MOUNTED           5114       5021

Now the new diskgroup DSK_GRP_02 is ready for use.

Next step is to manually create a 11g RAC database and placing the database files in the newly created DISKGROUP.

Oracle

Blog Archive

About Me

Monday, April 7, 2014

Manually create a Database in an Existing Oracle 11g RAC - ASM - Grid Infrastructure

Thursday, April 3, 2014

Manually Add a Shared Disk and Create a DISKGROUP in ASM