HeroEngine Database Setup

From HEWIKI

(Redirected from Database Schemas)

Jump to: navigation, search

Overview

There are 3 schemas specific to a world:

Client Database Schema for the client GOM
Server Database Schema for the server GOM
Repository Database Schema for the Repository

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

FSService schema
The 2 ID schemas, FS_ID and FS_ID_USER
Access control
Administration (To manage all schemas), HE_ADMIN
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

Download the latest perforce client
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
If you put the following in .bash_profile you'll want to logout and log back in for the changes to take effect.
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:

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

Prepare the Environment

Ensure that the hostname of the server is present in the /etc/hosts file with the interface-IP address of the machine.
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
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
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 .
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.
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

Start the Database Configuration Assistant with the following command:
- dbca (running logged in as oracle)
Welcome Screen
- Click Next
Step 1 of 12: Operations
- Select Create a Database
- Click Next
Step 2 of 12: Database Templates
- Select Custom Database
- Click Next
Step 3 of 12: Database Identification
- Enter a Global Database Name of hedb.world
- Enter an SID of hedb
- Click Next
Step 4 of 12: Management Options
- 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
Step 5 of 12: Database Credentials
- Set the password(s) for the required user accounts. Note: Make sure you don't forget these!
- Click Next
Step 6 of 12: Storage Options
- Select File System
- Click Next
Step 7 of 12: Database File Locations
- Select Use Oracle-Managed Files
- Enter Database Area as /data
- Click Next
Step 8 of 12: Recovery Configuration
- 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
Step 9 of 12: Database Content
- Deselect all options except Enterprise Manager Repository
- Click Standard Database Components
- Deselect all options except Oracle JVM
- Click OK
- Click Next
Step 10 of 12: Initialization Parameters
- 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
- Set Block Size to 16384 Bytes
- Set Processes to 1000
- Click All Initialization Parameters
- 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
Step 11 of 12: Database Storage
- Select Redo Log Groups
- Click Create to add Log Group 4
- Set File Size to 51200 KB
- Click Create
- Click Create to add Log Group 5
- Set File Size to 51200 KB
- Click Create
- Click Next
Step 12 of 12: Creation Options
- Click Finish
- Click OK on the Confirmation Dialog
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.
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
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.
Completion Dialog
- Click Exit
Start the Enterprise Manager Agent, if it is not already started
- At the command prompt, enter emctl start dbconsole

Initializing the HE schemas

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
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.
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.
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).
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:

Configure backups:
- Turning on archive logging
- Configure and schedule RMan backup jobs
- Configure and schedule some form of backup to tape
Configure TNSNames.ora if the server needs to talk to any other database

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:

Confirm the database is not in archivelog mode:


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

Create the parameter file from the running database:


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

Shutdown your database. (REMEMBER TO SHUTDOWN YOUR HEROENGINE WORLD, etc.)


   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".

Edit the parameter file, changing the parameter LOG_ARCHIVE_DEST (all lower case) to 'location=/flash/HEDB/archivelogs'
Bring up the database partially, using the changed pfile, alter it into archivelog mode, and then bring it up the rest of the way:


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

Confirm that the parameters are correct:


   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

Save the current parameters to SPFILE.


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

All should be well. Double-check:


   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

Decide on a retention policy: either a redundancy of 3, or a window of recovery of 7 days.
Implement the policy:

[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.

Verify the policy:

[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.

Implement the backup script:

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;

modify the permissions so that the script can be executed chmod 700 backupDatabaseFull.sh

See also: Oracle 2-day DBA Course: http://www.oracle.com/technology/obe/2day_dba/index.html
See also: Oracle RMAN Overview: http://www.oracle.com/technology/deploy/availability/htdocs/rman_overview.htm

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.

emca -deconfig dbcontrol db
emca -repos recreate
emca -config dbcontrol db

HeroEngine Database Setup

Contents

Overview

New HE Database Setup

Setup Perforce and Sync

Prepare the Environment

Creating the Database

Initializing the HE schemas

Post-installation Tasks

Archive Logging

RMAN

Create a DBLINK

Reconfiguration of Oracle Enterprise Manager

See also

Personal tools

Namespaces

Variants

Views

Actions

Search

Navigation

Useful Links

Toolbox