Oracle Migration

Notes On Migrating Oracle 9i data to Oracle 10g

Now comes the hard part, moving the existing Oracle 9i database to the new Oracle 10g server. We need to extract the parts of the Oracle 9i database that comprises the data from the users, while leaving out the data that comprises the applications and servers.

The exporting and importing of the data will be split into four components:

• Tablespaces,
• Roles and privileges,
• Users,
• Tables.

Part of the problem with exporting and importing the structure, roles and users of the existing database is that some objects rely on other objects to exist first. In order to handle the dependencies, I've split the export/import process into "before" parts and "after" parts. You'll see the differences in the descriptions that follows.

Exporting

Begin to create scripts and data files that will export the currently running database over to the new database.

Tablespaces

The biggest dependencies come from tables that are assigned a specific tablespace. So tablespaces must be created first.

To create a list of tablespaces for the new database, run the following script. It will create a series of SQL*Plus commands that will generate the new tablespaces. Each tablespace will be corrected for the location of the UNIX file holding the tablespace data, because the new database system will including migration to a different filesystem structure.

The code to create the tablespaces is:

#
#  This script will list all "non-system" tablespaces currently in Oracle
#

#  Set Oracle environment
. /usr/opt/bin/oracle_setup.sh

#  Create a tablespace creation script
mc -e '

-- Open connection to Oracle
Set Session=OracleConnect("ecn2", "system", "")

-- Location of final data files
Set Directory="/package/oracle/SunOS5.8/10.1.0.2.0/oradata/ecn2"


-- For each tablespace
Select From OracleTable(Session, "SELECT * FROM dba_tablespaces")
Do
	-- Skip system tablespaces
	If 
tablespace_name="SYSTEM" Or
tablespace_name="UNDOTBS1" Or
tablespace_name="SYSAUX" Or
tablespace_name="TEMP" Or
tablespace_name="USERS"
	Then
		Continue
	EndIf
	If contents<>"PERMANENT" Then
		Continue
	EndIf

	-- Create tablespace
	Print "CREATE TABLESPACE ", tablespace_name;

	-- Get original filename(s) of tablespace
	Set Command=Join(
		"SELECT file_name ",
		"FROM sys.dba_data_files ",
		"WHERE tablespace_name=",
		Quote(tablespace_name, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		-- Create datafile
		Print " DATAFILE ",

			-- Correct filename
			Quote(
				Join(
					Directory,
					LCase(
						Join(
							BaseName(file_name),
							".",
							Extension(file_name)
						)
					)
				), Char(39)
			);
		Print " SIZE 10M";
		Print " AUTOEXTEND ON";

		-- Do not create more than one file
		Break
	EndSelect
	Print ";"
EndSelect

-- Close connection to Oracle
Call OracleClose(Session)
' >TS-BEFORE

This script will generate a file called TS-BEFORE. Later on, we will copy this file to the new database server as the name ts.sql and run it through SQL*Plus.

Roles

Roles and privileges are next. These will need to be done in two passes, once before loading existing users and existing tables, and one for after. There are some roles and privileges that depend on the users and tablespaces existing before they can be assigned.

First, create the "before" roles. Use the following script:

#
#  This script will list all "non-system" roles currently in Oracle
#

#  Set Oracle environment
. /usr/opt/bin/oracle_setup.sh

#  Create the "before" roles creation script
mc -e '

-- Open connection to Oracle
Set Session=OracleConnect("ecn2", "system", "")

-- Get set of roles to skip
Set Roles=TableOpen("init-roles.dt")

-- For each role
Select From OracleTable(Session, "SELECT * FROM dba_roles")
Do
	-- Skip if role is in skip list
	If RowFindFirst(Roles, "RoleName", Role) Then
		Continue
	EndIf
	If Role Like "*$*" Then
		Print "# Skipping role ", Role
		Continue
	EndIf

	-- Create role
	Print "CREATE ROLE ", Role;
	If password_required<>"NO" Then
		Print "IDENTIFIED BY ??password??";
	EndIf
	Print ";"

	-- Seperate
	Print
EndSelect

-- Close connection to Oracle
Call OracleClose(Session)
' >ROLES-BEFORE

This script will generate a file called ROLES-BEFORE. Later on, we will copy this file to the new database server as the name rolesb.sql and run it through SQL*Plus. Now we need to have the "after" script to finish up roles after the tables and users are loaded:

#
#  This script will list all "non-system" roles currently in Oracle
#

#  Set Oracle environment
. /usr/opt/bin/oracle_setup.sh

#  Create the "after" roles creation script
mc -e '

-- Open connection to Oracle
Set Session=OracleConnect("ecn2", "system", "")

-- Get set of roles to skip
Set Roles=TableOpen("init-roles.dt")

-- For each role
Select From OracleTable(Session, "SELECT * FROM dba_roles")
Do
	-- Skip if role in skip list
	If RowFindFirst(Roles, "RoleName", Role) Then
		Continue
	EndIf
	If Role Like "*$*" Then
		Print "# Skipping role ", Role
		Continue
	EndIf

	-- Get role privileges
	Set Command=Join(
		"SELECT granted_role, admin_option ",
		"FROM dba_role_privs ",
		"WHERE grantee=",
		Quote(Role, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "GRANT ", granted_role, " TO ", Role;
		If admin_option="YES" Then
			Print " WITH ADMIN OPTION";
		Endif
		Print ";"
	EndSelect

	-- Get role system privileges
	Set Command=Join(
		"SELECT privilege, admin_option ",
		"FROM dba_sys_privs ",
		"WHERE grantee=",
		Quote(Role, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "GRANT ", privilege, " TO ", Role;
		If admin_option="YES" Then
			Print " WITH ADMIN OPTION";
		Endif
		Print ";"
	EndSelect

	-- Get role table privileges
	Set Command=Join(
		"SELECT owner, table_name, privilege, grantable ",
		"FROM dba_tab_privs ",
		"WHERE grantee=",
		Quote(Role, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "GRANT ", privilege;
		Print " ON ", owner, ".", table_name;
		Print " TO ", Role;
		If grantable="YES" Then
			Print " WITH GRANT OPTION";
		Endif
		Print ";"
	EndSelect

	-- Seperate
	Print
EndSelect

-- Close connection to Oracle
Call OracleClose(Session)
' >ROLES-AFTER

This script will generate a file called ROLES-AFTER. Later on, we will copy this file to the new database server as the name rolesa.sql and run it through SQL*Plus.

When creating the list of roles, we will skip the following predefined roles:

AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE, AUTHENTICATEDUSER, CONNECT, CTXAPP
DBA, DELETE_CATALOG_ROLE, DMUSER_ROLE, DM_CATALOG_ROLE, EJBCLIENT
END_USER_LAYER_ACCESS, EXECUTE_CATALOG_ROLE, EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS, GLOBAL_AQ_USER_ROLE, HS_ADMIN_ROLE
IMP_FULL_DATABASE, JAVADEBUGPRIV, JAVAIDPRIV, JAVASYSPRIV, JAVAUSERPRIV
JAVA_ADMIN, JAVA_DEPLOY, LOGSTDBY_ADMINISTRATOR, MGMT_USER, OEM_MONITOR
OLAP_DBA, OLAP_USER, ORAFORMS$BGM, ORAFORMS$DBG, ORAFORMS$OSC
RECOVERY_CATALOG_OWNER, RESOURCE, SCHEDULER_ADMIN, SELECT_CATALOG_ROLE
SNMPAGENT, WKADMIN, WKUSER, WM_ADMIN_ROLE, XDBADMIN

(This is the contents of the table init-roles.dt.)

Users

After creating the tablespaces and the initial set of roles, load the users. But there will need to be two scripts, a "before" and "after" script. The before script creates the users and some roles and privileges, then the tables must be loaded, followed by another user create that will assign table privileges to the users. Here is the "before" script to create users:

#
#  This script will list all "non-system" users currently in Oracle
#

#  Set Oracle environment
. /usr/opt/bin/oracle_setup.sh

#  Create the "before" users creation script
mc -e '

-- Open connection to Oracle
Set Session=OracleConnect("ecn2", "system", "")

-- Get set of users and roles to skip
Set Users=TableOpen("init-users.dt")
Set Roles=TableOpen("init-roles.dt")

-- For each user
Select From OracleTable(Session, "SELECT username FROM all_users")
Do
	-- Skip if user in skip list
	If RowFindFirst(Users, "UserName", UserName) Then
		Continue
	EndIf
	If UserName Like "*$*" Then
		Print "# Skipping user ", UserName
		Continue
	EndIf

	-- Create user
	Set Command=Join(
		"SELECT password, default_tablespace, ",
		"temporary_tablespace, profile ",
		"FROM sys.dba_users ",
		"WHERE username=",
		Quote(UserName, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "CREATE USER ", UserName;
		Print " IDENTIFIED BY VALUES ", Quote(password, Char(39));
		Print " DEFAULT TABLESPACE ", default_tablespace;
		Print " TEMPORARY TABLESPACE ", temporary_tablespace;
		Print " PROFILE ", profile;
		Print ";"
	EndSelect

	-- Grant roles
	Set Command=Join(
		"SELECT granted_role, admin_option ",
		"FROM sys.dba_role_privs ",
		"WHERE grantee=",
		Quote(UserName, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		If granted_role Like "*$*" Then
			Continue
		EndIf
		Print "GRANT ", granted_role, " TO ", UserName;
		If admin_option="YES" Then
			Print " WITH ADMIN OPTION";
		EndIf
		Print ";"
	EndSelect

	-- Grant system privileges
	Set Command=Join(
		"SELECT privilege, admin_option ",
		"FROM sys.dba_sys_privs ",
		"WHERE grantee=",
		Quote(UserName, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "GRANT ", privilege, " TO ", UserName;
		If admin_option="YES" Then
			Print " WITH ADMIN OPTION";
		EndIf
		Print ";"
	EndSelect

	-- Set default role
	Set RoleList=""
	Set Command=Join(
		"SELECT * ",
		"FROM sys.dba_role_privs ",
		"WHERE grantee=",
		Quote(UserName, Char(39)), " ",
		"AND default_role=",
		Quote("YES", Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		If granted_role Like "*$*" Then
			Continue
		EndIf
		If RoleList<>"" Then
			Set RoleList=Join(RoleList, ",", granted_role)
		Else
			Set RoleList=granted_role
		EndIf
	EndSelect
	If RoleList<>"" Then
		Print "ALTER USER ", UserName, " DEFAULT ROLE ", RoleList, ";"
	EndIf

	-- Seperate
	Print
EndSelect

-- Close connection to Oracle
Call OracleClose(Session)
' >USERS-BEFORE

This script will create the script USERS-BEFORE. Later on, we will copy this file to the new database server as the name usersb.sql and run it through SQL*Plus. Now create the "after" script:

#
#  This script will list all "non-system" users currently in Oracle
#

#  Set Oracle environment
. /usr/opt/bin/oracle_setup.sh

#  Create the "after" users creation script
mc -e '

-- Open connection to Oracle
Set Session=OracleConnect("ecn2", "system", "")

-- Get set of users to skip
Set Users=TableOpen("init-users.dt")

-- For each user
Select From OracleTable(Session, "SELECT username FROM all_users")
Do
	-- Skip if user in skip list
	If RowFindFirst(Users, "UserName", UserName) Then
		Continue
	EndIf
	If UserName Like "*$*" Then
		Print "# Skipping user ", UserName
		Continue
	EndIf

	-- Grant table privileges
	Set Command=Join(
		"SELECT privilege, owner, table_name ",
		"FROM sys.dba_tab_privs ",
		"WHERE grantee=",
		Quote(UserName, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "GRANT ", privilege, " ON ",
			owner, ".", table_name, " TO ", UserName, ";"
	EndSelect

	-- Grant column privileges
	Set Command=Join(
		"SELECT privilege, owner, table_name, column_name ",
		"FROM sys.dba_col_privs ",
		"WHERE grantee=",
		Quote(UserName, Char(39))
	)
	Select From OracleTable(Session, Command)
	Do
		Print "GRANT ", privilege, " ON ",
			owner, ".", table_name, "(", column_name, ") ",
			"TO ", UserName, ";"
	EndSelect

	-- Seperate
	Print
EndSelect

-- Close connection to Oracle
Call OracleClose(Session)
' >USERS-AFTER

This script will create the script USERS-AFTER. Later on, we will copy this file to the new database server as the name usersa.sql and run it through SQL*Plus.

When creating the list of users, we will skip the following predefined users:

ANONYMOUS, AURORA$JIS$UTILITY$, AURORA$ORB$UNAUTHENTICATED, BROWSER
CTXSYS, DBSNMP, DIP, DMSYS, EXFSYS, MDDATA, MDSYS, MGMT_VIEW, OLAPSYS
ORDPLUGINS, ORDSYS, OSE$HTTP$ADMIN, OUTLN, SCOTT, SI_INFORMTN_SCHEMA
SYS, SYSMAN, SYSTEM, TRACESVR, WKPROXY, WKSYS, WK_TEST, WMSYS, XDB

(This is the contents of the table init-users.dt.)

Export Tables

Now export the tables. Use the following script to export all tables by the users:

#
#  Export all "user" tables
#

#  Set Oracle environment
. /usr/opt/bin/oracle_setup.sh

#  Create a list of users to export
USERS=`mc -e '

-- Get list of users to skip
Set Users=TableOpen("init-users.dt")

-- Connect to database
Set Session=OracleConnect("ecn2", "system", "")

-- Create comma seperated list
Set Result=""
Select From OracleTable(Session, "SELECT username FROM all_users")
Do
	If Not RowFindFirst(Users, "UserName", UserName) Then
		If Result="" Then
			Set Result=UserName
		Else
			Set Result=Join(Result, ",", UserName)
		EndIf
	EndIf
EndSelect

-- Disconnect
Call OracleClose(Session)

-- Show result
Print Result
'`

#  Export all tables by users
exp 'OWNER=('$USERS')' FILE=output.exp

When running the export command, Oracle will prompt for logon credentials. Use sys@ecn2 as sysdba followed by the password.

This will probably take a while to complete.

Importing

Now that the database is nothing more that a few files, begin the process of import the results of the exporting to the new database system.

Prep by getting the SQL*Plus script from the old database server. Use the following script to load the scripts to the current directory:

#
#  Get the creation scripts made by the Oracle account on Levee
#

#  Set starting point
ORACLECN=/home/pier/c/oraclecn/cs

#  Get scripts
cp $ORACLECN/ROLES-AFTER rolesa.sql
cp $ORACLECN/ROLES-BEFORE rolesb.sql
cp $ORACLECN/USERS-AFTER usersa.sql
cp $ORACLECN/USERS-BEFORE usersb.sql
cp $ORACLECN/TS-BEFORE ts.sql

Run the script. Be sure to examine the scripts to make sure no errors occurred.

Next create the migration script. This script will be used to load all of the parts of the database that we exported in the previous steps. The script looks like this:

#
#  Restore the oracle installed products
#

#
#  Do the "before" scripts:
#	Create tablespaces
#	Create roles
#	Create users
#
sqlplus -L '/ as sysdba' <<'XXX'
startup
@ts
@rolesb
@usersb
quit
XXX

#  Now load the data files from the users' tables
imp full=y file=/home/pier/c/oraclecn/cs/output.exp <<'XXX'
/ as sysdba
XXX

#  Do the "after" scripts:
#  Create more roles
#  Add more privileges for users
#
sqlplus -L '/ as sysdba' <<'XXX'
@rolesa
@usersa
quit
XXX

#  Install a tnsnames file
cat >/var/opt/oracle/tnsnames.ora <<'XXX'
ecn2, ecn2.ecn.purdue.edu =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS =
            (COMMUNITY = ECN_PURDUE_EDU)
            (PROTOCOL = TCP)
            (HOST = levee.ecn.purdue.edu)
            (PORT = 1521)
         )
      )
      (CONNECT_DATA =
         (sid = ecn2)
         (SERVICE_NAME = ecn2.ecn.purdue.edu)
      )
   )
XXX

#  Install an oratab file
cat >/var/opt/oracle/oratab <<'XXX'
#  This file is used by ORACLE utilities.  It is created by root.sh
#  and updated by the ORACLE 10g and SQL*Net install procedures.

#
#  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 field 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.
#
#
ecn2:/package/oracle/SunOS5.8/10.1.0.2.0:Y
XXX

# stop the database 
sqlplus -L '/ as sysdba' <<'XXX'
shutdown
quit
XXX

Ready to begin? Be sure that the database is a clean, blank database and that the database server is not running. Then run the migration script.

It will take about 1 hour to run.

Finished

That's the end of this document. Proceed to the next document, Oracle Post-Configuration, which describes the process of required configuration settings in the new server.