AdnanDBA: UPGRADE / MIGRATION

You will need to change the database name and the DBID internally. Oracle has a utility for this, the NEWID application.

This document will detail the steps required to perform the database name change using NEWID.

Database Name : OMS112

New Database Name : OMSREP

Overview of Process

There are four steps to perform to change an Oracle database name (SID).

Preliminary prep work
Use NID application
Change SID References
Start database and finalize changes

1. PRELIMINARY PREP WORK

Once the database name change, changes will need to be made to the Oracle parameter file. If the database is using an server parameter file (SPFILE) instead of a parameter file (PFILE), you will need to convert from a SPFILE to a PFILE before the database name change occurs.

1.1 Determining if SPFILE is in Use

Run the following query to see if your database was started with a PFILE or SPFILE:

SQL> sho parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/12.1.0

.2/dbhome_1/dbs/spfileOMS112.ora

If the database starts with a PFILE, no further prep work is necessary and you can proceed to step 2 – Use NID application. Otherwise, continue with the next step.

1.2 Creating a PFILE from SPFILE

Run the following sql to create a PFILE from an SPFILE:

Figure 1.2 Creating a PFILE from SPFILE.

Run the following query to see if your database was started with a PFILE or SPFILE:

SQL> create pfile from spfile;

File created.

Figure 1.3 Copying the old PFILE to its new name

[oracle@xxxxx ~]$ cd /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs

[oracle@xxxxx dbs]$ ll

total 20

-rw-rw----. 1 oracle oinstall 1544 May 2 23:05 hc_OMS112.dat

-rw-r-----. 1 oracle oinstall 1271 May 2 23:48 initOMS112.ora

-rw-r--r--. 1 oracle oinstall 2992 Feb 3 2012 init.ora

-rw-r-----. 1 oracle oinstall 24 May 2 22:31 lkOMS112

-rw-r-----. 1 oracle oinstall 3584 May 2 23:05 spfileOMS112.ora

[oracle@xxxxx dbs]$ cp initOMS112.ora initOMSREP.ora

2. USE NID APPLICATION

To use the NID application, you will need to log into the database server as the oracle user and perform the following steps.

Shutdown the database
Start the database in mount mode
Run the NID command
Shut the database down

2.1 Shutdown the Database

[oracle@xxxxxxx dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 2 23:56:16 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 2932632 bytes

Variable Size 2466250856 bytes

Database Buffers 1811939328 bytes

Redo Buffers 13844480 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@xxxxxxx dbs]$ nid target=sys dbname=OMSREP

DBNEWID: Release 12.1.0.2.0 - Production on Wed May 3 00:03:32 2017

Password:

Connected to database OMS112 (DBID=187475667)

Connected to server version 12.1.0

Control Files in database:

/u02/oradata/OMS112/OMS112/control01.ctl

/u02/oradata/OMS112/OMS112/control02.ctl

Change database ID and database name OMS112 to OMSREP? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 187475667 to 4204721757

Changing database name from OMS112 to OMSREP

Control File /u02/oradata/OMS112/OMS112/control01.ctl - modified

Control File /u02/oradata/OMS112/OMS112/control02.ctl - modified

Datafile /u02/oradata/OMS112/OMS112/system01.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/sysaux01.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/undotbs01.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/users01.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/mgmt_ecm_depot1.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/mgmt.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/mgmt_deepdive.db - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/audi - dbid changed, wrote new name

Datafile /u02/oradata/OMS112/OMS112/temp01.db - dbid changed, wrote new name

Control File /u02/oradata/OMS112/OMS112/control01.ctl - dbid changed, wrote new name

Control File /u02/oradata/OMS112/OMS112/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to OMSREP.

Modify parameter file and generate a new password file before restarting.

Database ID for database OMSREP changed to 4204721757.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

Output from NID program showing a successful completion.

3. CHANGE SID REFERENCES

Changes will need to be made in the database parameter, tnsnames, oratab, and environment files to reflect the change of the database SID.

Oracle parameter file needs to be renamed and edited.
Oratab needs to have the SID renamed.
ora file needs the SID renamed
If the ORACLE_SID is set in the profile, it will need to be changed.

3.1 Oracle parameter file needs to be renamed and edited

The Oracle parameter file will need to be edited and have two lines changed. Change to where the parameter file is stored, the $ORACLE_HOME/dbs folder.

[oracle@xxxxx dbs]$ vi initOMSREP.ora

*.db_name='OMSREP'

*.db_recovery_file_dest='/u03/fast_recovery_area'

*.db_recovery_file_dest_size=19587399680

*.db_unique_name='OMSREP'

*.diagnostic_dest='/u01/app/oracle'

3.2 Oratab needs to have the SID renamed

Edit the file and change the old SID entry to the new SID. stored, the $ORACLE_HOME/dbs folder.

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

# The first and second fields are the system identifier and home

# directory of the database respectively. The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

# Multiple entries with the same $ORACLE_SID are not allowed.

OMSREP:/u01/app/oracle/product/12.1.0.2/dbhome_1:Y

Example ORATAB file showing changed SID reference from dwprd to dwqa. The changed value is in bold.

3.3 TNSNAMES needs to have the SID renamed

TNSNAMES is located in the folder $ORACLE_HOME/network/admin. Edit the file and change the old SID entry to the new SID, and change the HOST address to match the new system. See Figure 3.3.1 for an example.

stored, the $ORACLE_HOME/dbs folder.

[oracle@xxxxx dbs]$ vi /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora

OMSREP =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = FCISCOPRDOEM.fcicanada.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = OMS112.fcicanada.com)

)

LISTENER_OMSREP =

(ADDRESS = (PROTOCOL = TCP)(HOST = FCISCOPRDOEM.fcicanada.com)(PORT = 1521))

4. START DATABASE AND FINALIZE CHANGES

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initOMSREP.ora';

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 2932632 bytes

Variable Size 2466250856 bytes

Database Buffers 1811939328 bytes

Redo Buffers 13844480 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size 2932632 bytes

Variable Size 2466250856 bytes

Database Buffers 1811939328 bytes

Redo Buffers 13844480 bytes

Database mounted.

SQL> select name from v$database;

NAME

---------

OMSREP

SQL> alter database open resetlogs;

Database altered.

OLAP is not an option available with standard edition database but it gets added in an invalid state when a database is created using one of the provided database templates (transaction processing database) with oracle. Although option get installed it remains in a invalid state.
Metalink note "How To Find Out If OLAP Is Being Used And How To Remove OLAP [Doc ID 739032.1]" could be used to verify if olap is installed and used. (being used in a standard edition system is unlikely). The remove steps listed in this metalink note cannot be run on standard edition as those files are not installed with standard edition.

There's another metalink that list "How to Remove OLAP From the Standard Edition database. [ID 1362752.1]" which is by dropping the OLAPSYS user and running utlrp afterwards. This will remove the OLAP Catalog option from the database but OLAP Analytic Workspace and Oracle OLAP API will remain in an invalid state.

set lines 200;
col COMP_ID for a15;
col COMP_NAME for a40;
col VERSION for a10;
col STATUS for a15;
select comp_id, comp_name, version, status
from dba_registry
where comp_name like '%OLAP%';

select name "FEATURE", first_usage_date "FROM", last_usage_date "TO"
from DBA_FEATURE_USAGE_STATISTICS
where name like '%OLAP%';

=========================================================================

Running the validate_ordim gives the following errors

SQL> set serveroutput on
SQL> execute validate_ordim;
Locator INVALID OBJECTS: PRVT_IDX - 5 - 11
Locator INVALID OBJECTS: SDO_GEOM - 5 - 11
Locator INVALID OBJECTS: QRY2OPT - 5 - 8
Locator INVALID OBJECTS: SDO_PQRY - 5 - 8
Locator INVALID OBJECTS: SAMCLUST_IMP_T - 5 - 14

PL/SQL procedure successfully completed.

=========================================================================

From the output it seems that some of the spatial component related objects are invalid (since spatial option is off after the upgrade) thus oracle media that depends on it also becomes invalid. Remove the Oracle media component which also removes spatial component which is depends on media.

SQL> @?/rdbms/admin/catcmprm.sql ORDIM

About to remove Oracle Multimedia.
Checking to see if anyone is using Oracle Multimedia.

Oracle Multimedia is not being used

PL/SQL procedure successfully completed.

Are you sure you want to remove Oracle Multimedia (Y/N): Y

PL/SQL procedure successfully completed.

Removing Oracle Multimedia

PL/SQL procedure successfully completed.

=========================================================================

SQL> select comp_id, comp_name, version, status from dba_registry order by 4;

COMP_ID COMP_NAME VERSION STATUS
--------------- ---------------------------------------- ---------- ---------------
AMD OLAP Catalog 10.2.0.5.0 OPTION OFF
XOQ Oracle OLAP API 10.2.0.5.0 OPTION OFF
APS OLAP Analytic Workspace 10.2.0.5.0 OPTION OFF
ODM Oracle Data Mining 10.2.0.5.0 OPTION OFF
CATJAVA Oracle Database Java Packages 11.2.0.3.0 VALID
EXF Oracle Expression Filter 11.2.0.3.0 VALID
RUL Oracle Rules Manager 11.2.0.3.0 VALID
OWM Oracle Workspace Manager 11.2.0.3.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.3.0 VALID
EM Oracle Enterprise Manager 11.2.0.3.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0 VALID
XML Oracle XDK 11.2.0.3.0 VALID
CONTEXT Oracle Text 11.2.0.3.0 VALID
XDB Oracle XML Database 11.2.0.3.0 VALID
RAC Oracle Real Application Clusters 11.2.0.3.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.3.0 VALID

16 rows selected.

===========================================================================

This will increase the number of invalid objects but these will be OLAP related objects and has no impact on the rest of the database operation.

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

OWNER OBJECT_TYPE COUNT(*)
------------------------------ ------------------- ----------
OLAPSYS VIEW 25
PUBLIC SYNONYM 92
OLAPSYS PACKAGE BODY 3

===========================================================================

It is possible to drop the olapsys user and also drop remaining public synonyms.

DROP USER OLAPSYS CASCADE;

select 'drop '||object_type||' '||substr(object_name,1,40) ||';' from dba_objects where status='INVALID';

===========================================================================

After this expdp/impdp could run into following error

ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."INSTANCE_EXTENDED_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SYS.DBMS_METADATA", line 9114

Metalink notes How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1] and ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1] explains the solution for this, which is If the OLAP option is not used delete DBMS_CUBE_EXP OLAP package from the export view as follows

SQL> select PACKAGE,SCHEMA,class from exppkgact$ where (schema,package) not in (select owner,object_name from dba_objects where object_type='PACKAGE');

PACKAGE SCHEMA CLASS
------------------------------ ------------------------------ ----------
DBMS_CUBE_EXP SYS 4

SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS';

1 row deleted.

SQL> commit;

Commit complete.

============================================================================

After this export will work as expected and there will no invalid objects nor any invalid database components

@?/rdbms/admin/utlrp

============================================================================

select comp_name,version,status from dba_registry order by 3,1,2;

COMP_NAME VERSION STATUS
---------------------------------------- ---------- ---------------
OLAP Analytic Workspace 10.2.0.5.0 OPTION OFF
Oracle Data Mining 10.2.0.5.0 OPTION OFF
Oracle OLAP API 10.2.0.5.0 OPTION OFF
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Real Application Clusters 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID

15 rows selected.

CHANGING A DATABASE NAME USING NEWID

1. PRELIMINARY PREP WORK

2. USE NID APPLICATION

3. CHANGE SID REFERENCES

Remove OLAP From the Standard Edition database