HeroEngine Database Setup

From HEWIKI
Jump to: navigation, search

Contents

He server.png Documentation on this page is intended for customers of HeroEngine managing their own server(s).

This page describes how to install Oracle and setup HeroEngine schemas.

Overview

There are 3 schemas specific to a world:

  1. Client Database Schema for the client GOM
  2. Server Database Schema for the server GOM
  3. Repository Database Schema for the Repository

There are 6 support schemas that can be shared between worlds:

  1. FSService schema
  2. The 2 ID schemas, FS_ID and FS_ID_USER
  3. Access control
  4. Administration (To manage all schemas), HE_ADMIN
  5. Dream Manager - stores data for access by the DreamManager

New HE Database Setup

We will now walk you through setting up a new HE Database. It is assumed that Oracle has been installed as per the instructions under Linux Server Setup, which also covers Oracle setup.

Setup Perforce and Sync

  1. Download the latest perforce client
  2. Set the following -- for example in .bash_profile
    1. P4CLIENT=ADMINISTRATOR-MAIN-HOSTNAME
    2. P4PASSWD=xxxxxxx
    3. P4PORT=perforce.heroengine.com:x666
    4. P4USER=--UserUsedToAccessPerForce
  3. If you put the following in .bash_profile you'll want to logout and log back in for the changes to take effect.
  4. Now run 'p4 client'. Settings here will be VERY specific to your perforce installation. At the office we'll reference an internal oracle server for the specific settings. You should end up with something similar to the screen shot below:

Oracle p4 client.png

Now run 'p4 sync' to populate /opt/oracle/schema_database

Prepare the Environment

  1. Ensure that the hostname of the server is present in the /etc/hosts file with the interface-IP address of the machine.
  2. Create a data partition /data (at least 200 GB recommended) Must be done as root and owned by user oracle and group oinstall.
    • chown oracle.oinstall /data
  3. Create a flash-recovery partition /flash (at least 600 GB recommended) Must be done as root and owned by user oracle and group oinstall.
    • chown oracle.oinstall /flash
  4. Copy the following files from the perforce repository you synced to in the previous section to the oracle home directory:
    • cd ~
    • cp /opt/oracle/schema_database/create_HE_ADMIN.sql .
    • cp /opt/oracle/schema_database/initial_import.sql .
  5. Log onto the server via an X-windows terminal as the oracle user.
    • Do not use XMing, as the Oracle dbca utility is known to not work with it. Cygwin X-server is known to work.
  6. Ensure that the Oracle environment variables are set correctly (the actual paths depends on your actual installation):
    • export ORACLE_HOME=/opt/oracle/product/10.2.0
    • export ORACLE_BASE=/opt/oracle
    • export ORACLE_SID=hedb

Creating the Database

  1. Start the Database Configuration Assistant with the following command:
    • dbca (running logged in as oracle)
  2. Welcome Screen
    • DBCA Welcome.png
    • Click Next
  3. Step 1 of 12: Operations
    • DBCA Step 1.png
    • Select Create a Database
    • Click Next
  4. Step 2 of 12: Database Templates
    • DBCA Step 2.png
    • Select Custom Database
    • Click Next
  5. Step 3 of 12: Database Identification
    • DBCA Step 3.png
    • Enter a Global Database Name of hedb.world
    • Enter an SID of hedb
    • Click Next
  6. Step 4 of 12: Management Options
    • ChangeStep4of12.PNG
    • Check Configure the Database with Enterprise Manager
    • Select Use Database Control for Database Management
    • If you want email notifications of Database warnings, select Enable Email Notifications and enter the SMTP server and the email address to notify
    • Uncheck Enable Daily Backup. These are offline backups. Online backups will be configured further down.
    • Click Next
  7. Step 5 of 12: Database Credentials
    • DBCA Step 5.png
    • Set the password(s) for the required user accounts. Note: Make sure you don't forget these!
    • Click Next
  8. Step 6 of 12: Storage Options
    • DBCA Step 6.png
    • Select File System
    • Click Next
  9. Step 7 of 12: Database File Locations
    • DBCA Step 7.png
    • Select Use Oracle-Managed Files
    • Enter Database Area as /data
    • Click Next
  10. Step 8 of 12: Recovery Configuration
    • DBCA Step 8.png
    • Select Specify Flash Recovery Area
    • Set Flash Recovery Area to /flash
    • Set Flash Recovery Area Size to the size of your /flash drive(s)
    • Click Next
  11. Step 9 of 12: Database Content
    • DBCA Step 9.png
    • Deselect all options except Enterprise Manager Repository
    • Click Standard Database Components
    • DBCA Components step10.png
    • Deselect all options except Oracle JVM
    • Click OK
    • Click Next
  12. Step 10 of 12: Initialization Parameters
    • DBCA Step 10b.png
    • Select Custom
    • Set Shared Memory Management to Automatic
    • Set SGA Size to xxx MB where xxx is 50% of the physical RAM on the server.
    • Set PGA Size to yyy MB where yyy is all but 1024 megabytes of the remaining physical RAM on the server.
      In other words, OS & Oracle code: 1024 megabytes, SGA = 50% of RAM, PGA = remaining RAM
    • Select the Sizing tab
    • DBCA Step 10c.png
    • Set Block Size to 16384 Bytes
    • Set Processes to 1000
    • Click All Initialization Parameters
    • DBCA Parameters Step 11.png
    • Click Show Advances
    • Set filesystemio_options to setall
    • Set job_queue_processes to 26
    • Set sga_max_size to xxxM where xxx is 50% of the physical RAM on the server.
    • Set sort_area_size to 1048576
    • Set undo_retention to 28800
    • Click Close
    • Click Next
  13. Step 11 of 12: Database Storage
    • DBCA Step 11b.png
    • Select Redo Log Groups
    • Click Create to add Log Group 4
    • DBCA Redo Step 12.png
    • Set File Size to 51200 KB
    • Click Create
    • Click Create to add Log Group 5
    • Set File Size to 51200 KB
    • Click Create
    • DBCA Redo Final Step 13.png
    • Click Next
  14. Step 12 of 12: Creation Options
    • DBCA Step 14.png
    • Click Finish
  15. DBCA Confirmation Step 15.png
    • Click OK on the Confirmation Dialog
  16. Wait while progress progresses.
    • If a dialog presents itself with steps regarding the securing of the enterprise manager console, follow the steps listed after "bouncing the database" below.
  17. Bounce the database and start the listener.
    • At the command prompt, enter sqlplus "/ as sysdba"
    • At the sqlplus prompt, enter shutdown immediate; This may take a few minutes.
    • At the sqlplus prompt, enter host lsnrctl start
    • At the sqlplus prompt, enter startup;
    • At the sqlplus prompt, enter exit
  18. Now follow the "securing" steps listed in the dialog.
    • When prompted during the next steps, enter in the sysman password selected during database configuration.
    • At the command prompt, enter emctl stop dbconsole
    • At the command prompt, enter emctl config emkey -repos -sysman_pwd
    • At the command prompt, enter emctl secure dbconsole -sysman_pwd
    • At the command prompt, enter emctl start dbconsole
    • At the command prompt, enter emctl config emkey -remove_from_repos -sysman_pwd
    • BACKUP the emkey.ora file specified.
  19. Completion Dialog
    • DBCA Complete Step 16.png
    • Click Exit
  20. Start the Enterprise Manager Agent, if it is not already started
    • At the command prompt, enter emctl start dbconsole

Initializing the HE schemas

  1. Create the utl_mail package in oracle
    • Log into the database server as oracle
    • cd $ORACLE_HOME/rdbms/admin
    • Log into oracle as Sys, with the following command: sqlplus "/ as sysdba" (or sqlplus sys/password@hetest as sysdba )
    • At the sqlplus prompt, enter @utlmail.sql
    • At the sqlplus prompt, enter @prvtmail.plb
  2. Create the HE_ADMIN user
    • You can do this from your workstation or from the server
      • From the server
        • Enter the following command: sqlplus "/ as sysdba" (or sqlplus sys/password@hetest as sysdba )
        • At the sqlplus prompt, enter @create_HE_ADMIN.sql
        • When prompted, enter the value for the DATA_PUMP_IMPORTS directory: usually this is "/flash/HEDB/exports". The value will depend on the location of the database clean_support data file containing the support schemas to be imported below.
      • From a cmd prompt on your workstation's HE_ADMIN subdirectory
        • Enter the following command: sqlplus sys/password@hetest as sysdba
          • I.e. sqlplus <userName>/<password>@<dbName> as <privilegeLevel>
        • At the sqlplus prompt, enter @create_HE_ADMIN.sql
        • When prompted, enter the value for the DATA_PUMP_IMPORTS directory: usually this is "/flash/HEDB/exports". The value will depend on the location of the database clean_support data file containing the support schemas to be imported below.
  3. Now as the new user he_admin, populate the database schema.
    • This can be done two ways, one if from the server the other is from a workstation:
      • Change to the he_admin directory which includes the make.sql script
      • From the server, sqlplus he_admin/password@hetest @make.sql When it finishes, type "/" and "exit" as the final @_.sql is vestigial and does not need to run.
      • OR
      • From a workstation sqlplus he_admin/password@hetest @make.sql (where hetest is the tns name associated with the server you are setting up).
        • The log will show some scattered errors, mostly of the "Name already in use" type. These are not a problem.
  4. Now as the new user he_admin, run the finish script.
    • This can be done two ways, one if from the server the other is from a workstation:
      • Change back to the database directory which includes the finish_HE_ADMIN.sql script
      • From the server, sqlplus he_admin/password@hetest @finish_HE_ADMIN.sql
      • OR
      • From a workstation sqlplus he_admin/password@hetest @finish_HE_ADMIN.sql (where hetest is the tns name associated with the server you are setting up).
  5. Import the other schemas -- Make sure initial_import.sql is in the /data/hedb/utl_file/he_admin directory (mentioned earlier).
    • Copy the initial_import.sql file into the he_admin directory if it is not there
    • Copy the current version of the clean_support_?_??_?.dat export file into the previously specified exports directory (e.g.: /flash/HEDB/exports/)
      • The DBMigration tool is used to update clean_support schemas to the latest appropriate releases after clean_support_*.dat (use the current clean_support schema, which can be found in the downloads area) has been loaded.
    • From a command prompt, enter sqlplus he_admin/password@hetest @initial_import.sql
      • You'll be prompted to enter a clean_support_filename. You should enter the name of the clean support .dat file, do not include the .dat extension. The latest version will be available at http://account.heroengine.com under the section "HeroEngine Clean Support Exports".
        • e.g. clean_support_1_25_0

Post-installation Tasks

Tasks to be done after installation vary according to the specifics of the individual installation, but may include some or all of the following:

Archive Logging

Turning on archive logging simply enough (alter database archivelog). But first, you need to decide where on the server the archive log files will go. And then the database will have to be configured to use that place to stuff archive logs. And persisted to the spfile so that those settings are preserved across resets of the database.

We generally choose to place the archive logs at /flash/HEDB/archivelogs/...

 [root@mfg-db HEDB]# pwd
/flash/HEDB
[root@mfg-db HEDB]# mkdir archivelogs ; chown oracle.oinstall archivelogs ; chmod 710 archivelogs
 sqlplus "/ as sysdba"


To turn on archive logging, some parameter files must be saved, the database must be brought down, some parameters set in said file, the database instance brought partially up using the changed parameter file, altered into archivelog mode, brought up the rest of the way, and then the current running parameters saved to the spfile (saved parameter file) for auto-restarts to have the correct parameters. These steps are approximate:


   oracle> sqlplus "/ as sysdba"
  SQL> SELECT LOG_MODE FROM V$DATABASE;
  LOG_MODE
  ------------
  NOARCHIVELOG


   SQL> CREATE PFILE='/tmp/pfile.ora' FROM SPFILE;


   SQL> shutdown immediate;
  In most cases, this is enough.  Some very heavily loaded databases may take a few minutes to shut down in this fashion.  Don't be tempted to "shutdown abort".


   SQL> startup PFILE='/tmp/pfile.ora' NOMOUNT;
  SQL> alter database mount;
  SQL> alter database archivelog;
  SQL> alter database open;


   SQL> SELECT LOG_MODE FROM V$DATABASE;
  LOG_MODE
  ----------
  ARCHIVELOG
   SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'log_archive_dest';
  NAME        VALUE
  --------    -------------------------------------------------------
  log_arch... Location=/flash/HEDB/archivelogs


   CREATE SPFILE FROM PFILE='/tmp/pfile.ora';


   SQL> ARCHIVE LOG LIST


Next, you must decide where to put the backup files created by RMAN. We recommend /flash/HEDB/backupsets/... Once that has been done, then RMAN gets configured to use it.

Thirdly, you should back up those files to some place safe.

Fourthly --- and probably most importantly -- you'll have to test your recovery in case of database crash of some sort, data corruption, run-away "bad script" changing things, the database server walks out the door at the co-location center, etc.

RMAN

[oracle@HEdatabase ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 13 10:42:15 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: HEDB (DBID=855444710)

----
RMAN> configure retention policy to redundancy 3;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored

----
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

----
RMAN> exit
Recovery Manager complete.
[oracle@HEdatabase ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 13 10:42:15 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: HEDB (DBID=855444710)
RMAN> show retention policy;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> exit
Recovery Manager complete.
crontab -l
0 2 * * * /opt/oracle/admin/hedb/scripts/backupDatabaseFull.sh

[oracle@HEdatabase ~]$ cat /opt/oracle/admin/hedb/scripts/backupDatabaseFull.sh


#!/bin/sh


export MAIL=/bin/mail


export ORACLE_SID=hedb export ORACLE_BASE=/opt/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0 export ORACLE_LIB=$ORACLE_HOME/lib export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_LIB:$LD_LIBRARY_PATH cd $ORACLE_BASE/admin/$ORACLE_SID/scripts


( echo "Backup started: " `date` rman target / @backupDatabaseFull.rman || (echo '** ERROR OCCURRED DURING DB BACKUP **' | $MAIL -s "DB Oracle Backup ERROR OCCURRED" trouble@heroengine.com admin@heroengine.com he-YOURLIST@heroengine.com) echo "Backup completed: " `date` ) | $MAIL -s "DB Oracle Backup Report" he-YOURLIST@heroengine.com andy@heroengine.com


[oracle@HEdatabase ~]$ cat /opt/oracle/admin/hedb/scripts/backupDatabaseFull.rman


backup device type disk tag 'FULL_ARC' archivelog all not backed up delete all input; backup device type disk tag 'FULL_DB' database; allocate channel for maintenance type disk; delete noprompt obsolete device type disk; release channel; CATALOG RECOVERY AREA NOPROMPT; exit;



Create a DBLINK

For each database added that will host worlds, in the master control database (even if it is the same db) you must create a DBLINK so master control knows how to talk to it. That means, the database which holds the FS_SERVICE utilized by master control must have a link for itself AND every additional database controlled by a specific instance of master control.

sqlplus / as sysdba

 create database link DB2_C002.WORLD connect to HE_ADMIN identified by PASSWORD_HERE using 'DB2.A-USWEST-1A-C002.DYN.CLOUD.HEROENGINE.NET/HEDB.WORLD';

Reconfiguration of Oracle Enterprise Manager

The following three commands are used to reconfigure Oracle Enterprise Manager whenever necessary. For example, when the host name changes.

See also

Personal tools
Namespaces
Variants
Actions
Navigation
Toolbox