3.5. Creating an Oracle Database

3.5.1. Creating Network Configuration Files

Creating the network configuration files consists of creating the following Oracle TNS-related files: listener.ora, tnsnames.ora, and sqlnet.ora.

	Note
	The $TNS_ADMIN directory should reside in CDPN, assuming that the default directory for $TNS_ADMIN is $ORACLE_HOME/network/admin.

To create the network configuration files, follow these steps:

1. Create the listener.ora file; refer to Example 3-4 for a sample file.

2. Create the tnsnames.ora file; refer to Example 3-5 for a sample file.

3. Create the sqlnet.ora file; refer to Example 3-6 for a sample file.

4. Proceed to Section 3.5.2 Enabling network CDPN.

listener_rac1=
   (DESCRIPTION_LIST =
      (DESCRIPTION =
         (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
         )
         (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.example.com)(PORT = 1521))
         )
      )
   )

SID_LIST_LISTENER_RAC1 =  
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = PLSExtProc)
         (ORACLE_HOME = /mnt/oracle_base/product/9ir2)
         (PROGRAM = extproc)
      )
      (SID_DESC =
         (GLOBAL_DBNAME = swing)
         (ORACLE_HOME = /mnt/oracle_base/product/9ir2)
         (SID_NAME = rac1)
      )
   )

MYDB.EXAMPLE.COM 
   (DESCRIPTION =
      (LOAD_BALANCE = ON)
      (FAILOVER = ON)
      (ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=RAC1.EXAMPLE.COM)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=RAC2.EXAMPLE.COM)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=RAC3.EXAMPLE.COM)(PORT=1521))

(ADDRESS=(PROTOCOL=TCP)(HOST=RAC4.EXAMPLE.COM)(PORT=1521)) 
   )
   (CONNECT_DATA=
      (SERVICE_NAME=MYDB.EXAMPLE.COM)
      (failover_mode =
         (type = select)
      (method=basic)
      (retries=5)
      (delay=2)
         )
      )
   )

rac1 =
   (DESCRIPTION =
      (ADDRESS_LIST=    
      (ADDRESS=(PROTOCOL=TCP)(HOST=rac1.example.com)(PORT=1521))

   )
   (CONNECT_DATA=
      (SID = rac1)
      )
   )
rac2 =
   (DESCRIPTION =
      (ADDRESS_LIST=    
      (ADDRESS=(PROTOCOL=TCP)(HOST=rac2.example.com)(PORT=1521))

   )
   (CONNECT_DATA=
      (SID = rac2)
      )
   )
rac3 =
   (DESCRIPTION =
      (ADDRESS_LIST=    
      (ADDRESS=(PROTOCOL=TCP)(HOST=rac3.example.com)(PORT=1521))

   )
   (CONNECT_DATA=
      (SID = rac3)
      )
   )
rac4 =
   (DESCRIPTION =
      (ADDRESS_LIST=    
      (ADDRESS=(PROTOCOL=TCP)(HOST=rac4.example.com)(PORT=1521))

   )
   (CONNECT_DATA=
      (SID = rac4)
      )
   )

NAMES.DEFAULT_DOMAIN = example.com
NAMES.DIRECTORY_PATH= (TNSNAMES)

3.5.2. Enabling network CDPN

To enable CDPN, follow these steps:

1. At any node in the network, change to the $ORACLE_HOME directory and create your CDPN directories as follows:

   $ for i in `ls -d rac*`; do cp -r network $i; done

2. Move the network directory to network.orig using the following command:

   $ mv network network.orig

3. To create the CDPN, enter the following:

   $ ln -s @hostname/network network

4. At each node, navigate to the $ORACLE_HOME/network/admin directory and edit the listener.ora file to reflect the node-specific information.

5. After you have edited the node-specific information in the listener.ora file, start your listener and test your configuration. If they all respond with OK, your network configuration is done; stop the listener. Examples of starting and stopping a listener are shown as follows:

   $ lsnrctl start listener_mydb <--- Starting a listener

   $ lsnrctl stop listener_mydb <--- Stopping a listener

6. Make sure that the 9.2.0.4 patch properly sets the permissions for the dbsnmp file in the $ORACLE_HOME/bin directory. It should be owned by oracle and the permissions set at 750.

7. Proceed to Section 3.5.3 Setting Up the Cluster Configuration File.

3.5.3. Setting Up the Cluster Configuration File

To set up the cluster configuration file, follow these steps:

1. At each node, if the srvconfig.loc file does not exist in the /var/opt/oracle directory, log in as root, change directories, and run the rootadd.sh script as follows:

   # cd $ORACLE_HOME/srvm/utl # ./rootadd.sh

   	Note
   	This step adds the srvconfig.loc file to the /var/opt/oracle directory. If it fails, run the commands in the script manually. Make sure to perform this step on each node.

2. As user oracle on the master node, configure the Oracle cluster information device as follows:

   $ srvconfig -init $ gsdctl start $ srvctl add database -d mydb -o /mnt/oracle_base/product/9ir2 $ srvctl add instance -d mydb -i mydb1 -n rac1 $ srvctl add instance -d mydb -i mydb2 -n rac2 $ srvctl add instance -d mydb -i mydb3 -n rac3 $ srvctl add instance -d mydb -i mydb3 -n rac3

3. Start the network listener on the master node as follows:

   $ lsnrctl start listener_mydb1

4. At each node, issue the following commands:

   $ gsdctl start $ agentctl start

5. Proceed to Section 3.5.4 Setting Up the Database.

3.5.4. Setting Up the Database

Before setting up the database, check the following:

• All the Oracle-required environment variables are set.

• ORACLE_SID is set to the instance name.

• Unset the LANG environment variable.

You can create a database either manually or by using the Oracle DBCA (Database Configuration Assistant). Create the database from the master node only. To set up the database follow these steps:

1. In the init.ora file, use the pound sign (#) to comment out the local_listener parameter of each node except the one from which you are working. Refer to Example 3-7 for a sample of an init.ora file that has been edited for working from the node named mydb1.

   ... ... cluster_database_instances = 4 ... cluster_database=true mydb1.instance_name=mydb1 mydb1.instance_number=1 mydb1.local_listener=LISTENER_MYDB1 mydb1.local_listener=mydb1.example.com mydb1.thread=1 mydb1.undo_tablespace=UNDOTBS1 mydb2.instance_name=mydb2 mydb2.instance_number=2 #mydb2.local_listener=LISTENER_MYDB2 <-- mydb2 local_listener commented out mydb2.local_listener=mydb2.example.com mydb2.thread=2 mydb2.undo_tablespace=UNDOTBS2 mydb3.instance_name=mydb3 mydb3.instance_number=3 #mydb3.local_listener=LISTENER_MYDB3 <-- mydb3 local_listener commented out mydb3.local_listener=mydb3.example.com mydb3.thread=3 mydb3.undo_tablespace=UNDOTBS3 mydb4.instance_name=mydb4 mydb4.instance_number=4 #mydb4.local_listener=LISTENER_MYDB4 <-- mydb4 local_listener commented out mydb4.local_listener=mydb4.example.com mydb4.thread=4 mydb4.undo_tablespace=UNDOTBS4 ... ...

   Example 3-7. Sample File: init.ora

2. Set the environment variables. For example:

   $ export ORACLE_BASE=</mnt/oracle> $ export ORACLE_HOME =$ORACLE_BASE/product/9iR2 $ unset LANG $ export ORACLE_SID=mydb1

3. Assuming that your database conforms to the Oracle Optimal Flexible Architecture (OFA) guidelines, and that bdump, udump, cdump, pfile, and any other relevant directories are under $ORACLE_BASE/admin, put $ORACLE_BASE/admin in CDPN:

   $ cd $ORACLE_BASE $ for i in `ls -d product/9ir2/rac*`; do cp -r admin $i; done $ mv admin admin.orig $ ln -s product/9ir2/@hostname/admin admin

4. Create the password files, spfiles, from the respective nodes/directories.

5. Start the database instances from the respective nodes using srvctl (the preferred method) or SQL*Plus. For example:

   1. srvctl

      $ srvctl start instance -d mydb -i mydb2

   2. SQL*Plus

      $ sqlplus /nolog SQL> connect / as sysdba SQL> startup ORACLE instance started. Total System Global Area 252777144 bytes Fixed Size 451256 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened.

6. Start the listeners from the respective nodes.