2 Upgrading Oracle GoldenGate Veridata

2.2.1 Creating Schema for Oracle Database

If you are using Oracle database, create the Oracle Platform Security Services (OPSS) schema using RCU. In the Select Components page of RCU, select OPSS as follows:

See Creating Schemas with the Repository Creation Utility for more information.

2.2.2 Creating Schema for Microsoft SQL Server

For upgrading to version 12.1.3, MS SQL Server users should run RCU to create the Service Table (STB). Select only Service Table in the Select Components page:

See Creating Schemas with the Repository Creation Utility for more information.

2.2.3 Creating Schema for MySQL

For upgrading to version 12.1.3, MySQL users should run RCU to create the Service Table. Select only STB in the Select Components page of the RCU (See Figure 2-2).

See Creating Schemas with the Repository Creation Utility for more information about using RCU.

Setting the configuration parameters for MySQL

Before running RCU for MySQL, execute the following commands at the MySQL command prompt:

mysql>SET GLOBAL INNODB_FILE_PER_TABLE="ON";
mysql>SET GLOBAL INNODB_FILE_FORMAT="Barracuda";
mysql>SET GLOBAL INNODB_LARGE_PREFIX="ON"; 
mysql>SET GLOBAL LOG_BIN_TRUST_FUNCTION_CREATORS="ON";

Renaming the database user name

The lower_case_table_names system variable in MySQL determines if the MySQL table lookup is case sensitive or not.

If lower_case_table_names is "1", the table lookup is case insensitive.

If lower_case_table_names is "0", the table lookup is case sensitive.

Run the following command at the MySQL prompt to see the value of the lower_case_table_names variable:

mysql > SHOW VARIABLES LIKE "lower_case_table_names";

Depending on the value of lower_case_table_names, follow the steps below to rename the database user name in uppercase.

• If lower_case_table_names=1, then rename the MySQL user name as follows:

  RENAME USER old_user@'host' TO new_user@'host';
  

  where

  old_user is the user name of the current Veridata repository.

  new_user is the user name that is same as the database name and this should be in uppercase.

  host is the host name of the machine from which you need access to the database. To allow access from all machines, specify '%' for the host name.

  Example:

  If the database name is veridataDB and user name is veridataUser, execute the following command:

  RENAME USER veridataUser@'localhost' to VERIDATADB@'localhost';
  
  or
  RENAME USER veridataUser@'%' to VERIDATADB@'%';
  

• If lower_case_table_names=0 and if the database name is in uppercase, then rename the MySQL user name to uppercase as same as the database name:

  RENAME USER old_user@'host' TO new_user@'host';
  

  where

  old_user is the user name of the current Veridata repository.

  new_user is the user name that is same as the database name and this should be in uppercase.

  host is the host name of the machine from which you need access to the database. To allow access from all machines, specify '%' for the host name.

• If lower_case_table_names=0 and if the database name is in lowercase or mixed case, then create a new database and a new user with both the user name and database name in uppercase. See "Creating a new database and user for MySQL database".

  Example:

  If the user name is veridataUser and old database name is veridataDB, then create a new database with the name as VERIDATAUSER and copy contents of veridataDB to VERIDATAUSER. You should also create a new user VERIDATAUSER with access to the new database.

Creating a new database and user for MySQL database

To create a database:

1. Enter the following commands to create a new database from the old database dump:

   bash>mysqldump -u[username] -p[pass] -v [olddatabase] > [file path to create sqldump olddbdump.sql]
   
   bash>mysql -u[username] -p[pass]
   
   mysql>create schema [NEWDATABASE]
   
   mysql>use [NEWDATABASE]
   
   mysql>source [file path for sqldump olddbdump.sql]
   

2. To grant access to the database:

   mysql>grant all on `[NEWDATABASE]`.* to [NEWDATABASE]@'localhost' identified by '[Password]';
   mysql>grant grant option on `[NEWDATABASE]`.* to [NEWDATABASE]@'localhost';
   mysql>grant all on `[NEWDATABASE]`.* to [NEWDATABASE]@'%' identified by '[Password]';
   mysql>grant grant option on `[NEWDATABASE]`.* to [NEWDATABASE]@'%';
   mysql>flush privileges;
   

Example:

Old database: veridata18_9

New database: VERIDATA18_9

New user name: VERIDATA18_9

Note that both user name and database name are the same.

bash> mysqldump -uroot -pwelcome -v veridata18_9 > /home/veridata18_9.sql
bash> mysql -uroot –pwelcome
mysql> create schema VERIDATA18_9;
mysql> use VERIDATA18_9; 
mysql> source /home/veridata18_9.sql;
mysql> grant all on `VERIDATA18_9`.* to VERIDATA18_9@'localhost' identified by 'VERIDATA18_9';
mysql> grant grant option on `VERIDATA18_9`.* to VERIDATA18_9@'localhost'; 
mysql> grant all on `VERIDATA18_9`.* to VERIDATA18_9@'%' identified by 'VERIDATA18_9';
mysql> grant grant option on `VERIDATA18_9`.* to VERIDATA18_9@'%';
mysql> flush privileges;