Migrating from Oracle 11g R2 to Oracle 12c

Pre-requisites:

TrueSight Infrastructure Management: Should be on 10.x,11.x
Oracle DB: Should be on 11.2.0.x. (11g R2 latest patch) 

Summary of steps to be carried out

The upgrade-migrate of oracle 11GR2 to 12C need to be achieved in two phases. This document describes the process to migrate using Phase 2.
Phase 1: Install Oracle 12C on same machine where oracle 11 is installed or another machine.
Phase 2: Migrate data from 11gr2 db to 12c database server.

1. Install Oracle 12C fresh with a new SID on a new machine.
2. Set INIT Parameters on CDB level.
3. Create one pdbs for TrueSight Infrastructure Management. 
4. Create users, tablespaces on pdb. 
5. Stop the TrueSight Infrastructure Management server by running the command pw system stop – Down time starts.
6. Export data from source database (11gr2).
7. Import user and data into 12c pdb.
8. Change DB configuration for TrueSight Infrastructure Management.
9. Start TrueSight Infrastructure Management server by running the command pw system start - Down time ends.

Create pdb for TSIM setup

1. On the Oracle 12 C machine, create pdb, using database configuration assistant.

2. Please ensure that oracle Home is set, the same can be verified as below:
C:\<my_oracle_folder>\Utilities\oracle\12C\PDB>set | findstr oracle
ORACLE_HOME=C:\oracle\product\12.1.0\dbhome_2

3. Once pdb created, ensure that in the
<Oracle_install_directory>\product\12.1.0\dbhome_2\NETWORK\ADMIN\tnsnames.ora file has the following entries for the pdb in the format below.

4. (For Oracle 12c Pluggable) Add the USE_SID_AS_SERVICE_<listener_name>=on parameter in listener.ora and reload the listener settings with the lsnrctl reload command.

5. Set tnsnames.ora

Ensure that created pdb are running and opened, to verify that by running the below command

Create users, tablespaces on pdb

1. Please ensure that oracle Home and oracle_sid is set, the same can be verified as below:
C:\Windows_<vertion>\Windows\Utilities\oracle\12C>set | findstr oracle
ORACLE_HOME=C:\oracle\produc\12.1.0\dbhome_2
 C:\ora12c\product\12.1.0\dbhome_2>set ORACLE_SID
 ORACLE_SID=ORA12C

2. From the TrueSight Infrastructure Management10.x,11.x installable directory copy the entire utilities directory to the oracle 12c server machine. [C:\Windows_<version>\Windows\Utilities\oracle\12C]

3. Using cdb SID, configure the oracle parameters by running the following script as mentioned below:
<copied directory>\Utilities\oracle\12c\cdb\set_system_parameter_oracle_pnet.sql
Connecting to cdb with ora12c as the SID.
cmd> sqlplus sys/<password>@ora12c as sysdba
SQL> @set_system_parameter_oracle_pnet.sql
This script will set the parameters on the oracle 12c and requests for restarting the database. Please do after taking backup as prescribed by script. Re-login as specified by script.

Verify pdb is started or not. Once re-started, connect to the pdb and create the required users,
and tablespaces, required for TrueSight Infrastructure Management server.

TrueSight Infrastructure Management

Users : proact

Report user: proactru

Password: As 11g DB users
Main tablespace name: pnet_ts (Ensure that you create the same tablespace names as present in oracle 11gr2).
cmd> sqlplus sys/<password@ora12cpdb as sysdba
sql> @run_oracle_db_scripts.sql
This script asks for the tablespace, main user and report user details. On providing, it will create the same.
Once the required schemas are created, import the exported data from 11g users into 12c.

Export data from source database (11gr2)

Stop the TrueSight Infrastructure Management server using pw sys stop and verify using pw p l if stop or not
On oracle server machine (ora112db_host):
Ensure that the oracle Home path is set as above.
1. Create a directory for dumping exported data:
c:\tmp\ora112db
2. Ensure that this directory has read/write permission.
    Connect to oracle:
    Login with the new sid created c:\oracle\product\11.2.0>sqlplus sys/<password>@ ora112db as sysdba

    SQL> CREATE DIRECTORY DATA_PUMP_DIR_11G AS 'C:\tmp\ora112db ';
    SQL> GRANT READ, WRITE on DIRECTORY DATA_PUMP_DIR_11G to sys;
    SQL>quit
3. Export the main user and report users of TrueSight Infrastructure Management server.
   C:\tmp\ora112db>expdp DIRECTORY=DATA_PUMP_DIR_11G DUMPFILE=tsim-expdp.dmp LOGFILE=tsim-expdp.log SCHEMAS=PROACT, PROACTRU
   username: sys/bmcAdm1n@ora112db as sysdba
   Note: - Where ora112db is the oracle SID
  Copy tsim-expdp.dmp file from the oracle 11g server machine to oracle 12c server machine.

Import user and data into 12c pdb ORA12CPDB

cmd> sqlplus sys/<password>@ora12cpdb as sysdba
 SQL> create directory PUMP_DIR_12C as 'c:\Oracle_dump_copied';
SQL> grant read,write on directory PUMP_DIR_12C to sys;
 SQL> quit
C:\Oracle_dump_copied>impdp SCHEMAS=PROACT, PROACTRU DIRECTORY=PUMP_DIR_12C LOGFILE=tsim-impdpdp.log DUMPFILE=tsim-expdp.dmp

Username: sys/<password>@ora12cpdb as sysdba
Once import is complete, verify the number of tables present in the imported database matches with what is exported [Verify Import and Export logs. tsim-impdpdp.log, tsim-expdp.log]
Note: As we have already created the usernames, while importing it will show error specifying that the users are already present. This can be ignored.
Note: Below error can ignore as you already created user in 12c
ORA-31684: Object type USER:"PROACT" already exists.
ORA-31684: Object type USER:"PROACTRU" already exists.

Change DB configuration for TrueSight Infrastructure Management on ORACLE 12C

Connect to the TrueSight Infrastructure Management server box command prompt. Make sure TrueSight Infrastructure Management is not running.
cmd> pw dbconfig set -database oracle -sid ora12cpdb -host <ora12cpdb_hostname>:1521 -username proact -password proact -repusername proactru -reppassword proactru
Restart the server using pw sys start
Verify with command pw p l and pw lic list
Verify that the TrueSight Infrastructure Management log in works.