2.2.7 Oracle Database Creation and Setting

Allocate a database

Create a database on the shared disk. The shared disk must be activated.
The files are allocated as follows:

Files	Locations	Note
Init.ora file	Local	Allocate on a local disk of each node
Server parameter file	Share	-
Control files	Share	-
Data files	Share	-
Redo log files	Share	-
Archive log files	Arbitrary	Allocate either on a local disk or a shared disk
Flash Recovery Area	Share	-
the Alert Log, Trace Files, Dump Files	Local	Allocate on a local disk of each node

Note

Note that there is sufficient space to archive for archived redo log files if they are located on shared disks. If there is insufficient space, and data updating processing through monitoring SQL hangs, an oracle resource might fail. Moreover, userApplication failover will fail and operating of Oracle database will completely stop.

Information

Check if the Oracle user has privileges to write in a shared disk before creating the database.
If you use ASM (Automatic Storage Management) for Oracle 10g or later, it is necessary to register the ASM as an RMS resource. See “2.4.2 ASM (Automatic Storage Management)”.

Create a database (Scalable operation with Oracle9i RAC)
Create a database on any one of the nodes. The database must be accessible from the standby nodes.
See
Refer to the Oracle RAC manual.
Create a database (Standby operation)
- Operation node
  Create a database on the operating node. The database must be accessible from the standby nodes.
  See
  Refer to the Oracle manual.
- Standby node
  Set up the standby nodes in the same configuration (directory creation, file copy, and links) as the operating node.
  - Under <$ORACLE_HOME>/dbs
  - Under <$ORACLE_BASE>/admin/<$ORACLE_SID>
  - Under <$ORACLE_BASE>/diag (Oracle 11g)
  - Under <$ORACLE_HOME>/network/admin
  The access privilege to the directories and files must be also the same as that on the operating node.
  If you set where archive log is output on the operating node, it is necessary to set the same on the standby nodes.
  See
  The required files on the operating node can be backed up in the tar format with the “cloracpy” command. See “4.4 cloracpy - Create Backup File for Oracle Database Setup”.

Example

In Mutual standby, N:1 standby and other Standby operation, create a database on the operating node. The database must be accessible from the standby nodes.

Mutual standby

2:1 Standby

Parameter file
The initial parameter setting of LOCAL_LISTENER must be compatible with that of listener.ora and tnsnames.ora. You should also pay close attention to set up Oracle network.

If you operate Oracle servers, parameters, and files (parameter files), configure them on a shared disk. If they are not configured on the shared disk, the settings will be inconsistent between operating and standby nodes. Specify the path in the initial parameter file to allocate the servers, parameters, and files.
Example
How to set the initialized parameter file:
(<$ORACLE_HOME>/dbs/init<$ORACLE_SID>.ora
```
spfile = /mnt2/o9idb/parameter/spfile.ora
```
Note
If you create a database using DBCA (Database Configuration Assistant) in the Oracle 10g or later environment, you might not be able to specify where the server parameter file is stored. If this occurs, move the server parameter file to the shared disk after creating the database as follows:
1. Mount the shared disk if it is not mounted. Start userApplication that is created at “2.2.3 userApplication Creation”.
2. Move the server parameter file to the shared disk.
```
#mv <$ORACLE_HOME>/dbs/spfile<$ORACLE_SID>.ora <shared disk>/spfile<$ORACLE_SID>.ora
```
3. Edit the initialized parameter file <$ORACLE_HOME>/dbs/init<$ORACLE_SID>.ora as follows:
```
spfile=< shared disk >/spfile<$ORACLE_SID>.ora
```
Execute step 3 on both of the operating node and standby nodes.
You can also use the cloracpy command to execute it on the standby nodes after the operating node.
Network setup
Set up the Oracle network.
```
$ORACLE_HOME/network/admin/listener.ora, tnsnames.ora
```
- listener.ora
  If you use Oracle listener switching operation using a logical IP address for standby operation, specify the logical IP address for the IP address of the Oracle listener.
  Example
```
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <logical IP address>)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ora9i)
      (ORACLE_HOME = /opt/oracle/product/9.0.1)
      (SID_NAME = ora9i)
    )
  )
```
  Note
  In Oracle 10g or later environment, operating system authentication (OS authentication) of Listener must be enabled. There are two methods to enable it:
  - Not define the “LOCAL_OS_AUTHENTICATION_<LISTENER_NAME>” parameter in listener.ora file. (default)
  - If the parameter is defined, its value should be “ON”.
    LOCAL_OS_AUTHENTICATION_<LISTENER_NAME> = ON
- tnsnames.ora
  If the Oracle listener is monitored through tnsping, set up the “tnsnames.ora” file. Then, specify the network service name set for tnsnames.ora in the environment setting of the Oracle listener of PRIMECLUSTER Wizard for Oracle. In that case, ORACLE_SID, host (logical IP address), and port number must be the same as those of the Oracle listener.
  Example
```
Network service name =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <logical IP address>)(PORT = 1521))
    (CONNECT_DATA = (SID = ora9i))
  )
```
SYSTEM user password
PRIMECLUSTER Wizard for Oracle accesses and monitors Oracle as the Oracle SYSTEM user. Therefore, the SYSTEM user's password must be registered in PRIMECLUSTER Wizard for Oracle. See “4.3 clorapass - Register Password for Monitoring” to register the password.
Check Oracle operation
Check if Oracle is properly running by starting and stopping the Oracle instance and Oracle listener manually.
Note
- The Oracle “TWO_TASK” environment variable must not be changed.
  It is used to add a connect identifier to connect to Oracle then automatically connect to network. In PRIMECLUSTER Wizard for Oracle, network connection is not supported. Users are supposed to connect to Oracle in local connection (“ / as sysdba”). If network connection is required, specify “@connect identifier” instead of using the “TWO_TASK” environment variable when connecting at the connecting.
- The processes other than definition of system environment variables must not be added to login.sql of Oracle DBA users.
  SQL can be described in the SQL*Plus profile login.sql. However, it might automatically be executed and affect ongoing operations because Oracle is controlled through SQL*Plus in PRIMECLUSTER Wizard for Oracle.
  It is available to setup the system environment variables to login.sql by SET command.