//
you're reading...
Oracle Database Upgrade

Database Upgrade from Oracle 9i R2 (9.2.0.7) HP-UX to 11g R2 (11.2.0.4) OEL cross platform – section4

In section 3, the database has been upgraded from Oracle 9i to Oracle 10g. Now we can use the Oracle Data Pump to tranfer the database from 10g R2 to 11g R2

Data Pump configuration on source database (10g R2)

--Create temp tablespace
CREATE TEMPORARY TABLESPACE temp01 TEMPFILE 'temp01.dbf' SIZE 4096M;

alter database default temporary tablespace temp01;

execute dbms_metadata_util.load_stylesheets

--create directory for data pump
SQL> create directory PUMP_DIR AS '/backup01';

--To reslove the error, it will depend on your configuration
/*
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-04063: package body "SYS.KUPW$WORKER" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPW$WORKER"

ORA-39097: Data Pump job encountered unexpected error -6508
*/

SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS  AS SELECT * FROM sys.ku_noexp_view;
Table created.
SQL> GRANT SELECT ON sys.ku$noexp_tab TO PUBLIC;
Grant succeeded.
SQL> GRANT INSERT ON sys.ku$noexp_tab TO PUBLIC;
Grant succeeded.
SQL> alter package KUPW$WORKER compile BODY;
Package body altered.

Export DDL for creating tablespaces

--List all avaiable tablespace

SQL>
COLUMN Tablespace      FORMAT a30
COLUMN "Size (MB)"     FORMAT 9,999,999,999
COLUMN "Free (MB)"     FORMAT 9,999,999,999
COLUMN "% Free"        FORMAT 999
COLUMN "% Used"        FORMAT 999 

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
        FROM dba_data_files
        GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
     (SELECT tablespace_name,bytes_free,bytes_used
      FROM v$temp_space_header
      GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 1 DESC;

Tablespace                          Size (MB)      Free (MB) % Free % Used
------------------------------ -------------- -------------- ------ ------
XDB                                        60             14     23     77
USERS                                      50             41     81     19
UNDOTBS1                               15,500         11,463     74     26
TOOLS                                      10             10     99      1
TEMP01                                  4,096          3,765     92      8
SYSTEM                                  6,500          5,543     85     15
SYSAUX                                  4,000          3,822     96      4
STATSPACK                              20,000          2,464     12     88
testdb_INDX01                          16,384          8,028     49     51
testdb_DATA01                          16,384         10,977     67     33

Run get_ddl to get the create tablespace statement

SQL> SET PAGESIZE 0
SQL> SET LONG 10000
SQL> select dbms_metadata.get_ddl('TABLESPACE','testdb_DATA01') from dual;

  CREATE TABLESPACE "testdb_DATA01" DATAFILE
  '/data01/oradata/testdb/testdb_data01.dbf' SIZE 4194304000,
  '/data01/oradata/testdb/testdb_data02.dbf' SIZE 4194304000,
  '/data01/oradata/testdb/testdb_data03.dbf' SIZE 4194304000,
  '/data01/oradata/testdb/testdb_data04.dbf' SIZE 4194304000
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

SQL> SET PAGESIZE 0
SQL> SET LONG 10000
SQL> select dbms_metadata.get_ddl('TABLESPACE','testdb_INDX01') from dual;

  CREATE TABLESPACE "testdb_INDX01" DATAFILE
  '/data01/oradata/testdb/testdb_indx01.dbf' SIZE 4194304000,
  '/data01/oradata/testdb/testdb_indx02.dbf' SIZE 4194304000,
  '/data01/oradata/testdb/testdb_indx03.dbf' SIZE 4194304000,
  '/data01/oradata/testdb/testdb_indx04.dbf' SIZE 4194304000
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

In this case, our target database use ASM as storage management. So the output create tablespace SQL statements need to be modified when we re-create the tablespace in target database.

-- Recreate the tablespace testdb_DATA01
SQL> CREATE TABLESPACE "testdb_DATA01" DATAFILE '+TESTDBDATA' LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

-- Recreate the tablespace testdb_INDX01
SQL> CREATE TABLESPACE "testdb_INDX01" DATAFILE '+TESTDBDATA' LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

SQL> ALTER TABLESPACE testdb_DATA01 ADD DATAFILE '+TESTDBDATA' SIZE 4096M;
SQL> ALTER TABLESPACE testdb_DATA01 ADD DATAFILE '+TESTDBDATA' SIZE 4096M;
SQL> ALTER TABLESPACE testdb_DATA01 ADD DATAFILE '+TESTDBDATA' SIZE 4096M;

SQL> ALTER TABLESPACE testdb_INDX01 ADD DATAFILE '+TESTDBDATA' SIZE 4096M;
SQL> ALTER TABLESPACE testdb_INDX01 ADD DATAFILE '+TESTDBDATA' SIZE 4096M;
SQL> ALTER TABLESPACE testdb_INDX01 ADD DATAFILE '+TESTDBDATA' SIZE 4096M;

export all non-system users

SQL> SET PAGESIZE 0
SQL> SET LONG 20000

SQL>
SELECT dbms_metadata.get_ddl('USER', username)
FROM   dba_users
WHERE  username
NOT IN
	(
		'CTXSYS',
		'DBSNMP',
		'DIP',
		'DMSYS',
		'MDDATA',
		'MDSYS',
		'ODM',
		'ODM_MTR',
		'ODS',
		'OLAPSYS',
		'ORACLE_OCM',
		'ORDPLUGINS',
		'OUTLN',
		'PERFSTAT',
		'PIMS',
		'SI_INFORMTN_SCHEMA',
		'SYS',
		'SYSTEM',
		'TSMSYS',
		'WKPROXY',
		'WKSYS',
		'WMSYS',
		'XDB',
		'ORDSYS'
	);

Save all output sql statement for create users.

export the user schema from source database

----export testdb_APP schema only through data pump with multi dump files
$ expdp \'sys/oracle123@testdb as sysdba\' parallel=4 SCHEMAS='testdb_App' job_name=export_testdb_APP01 directory=PUMP_DIR filesize=10G dumpfile=PUMP_DIR:expdp_testdb_APP_%U.dmp logfile=testdb_APP_exp.log

Export: Release 10.2.0.5.0 - 64bit Production on Tuesday, 15 December, 2015 10:40:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."EXPORT_testdb_APP01":  "sys/********@test AS SYSDBA" parallel=4 SCHEMAS=pas job_name=export_testdb_APP01 directory=PUMP_DIR filesize=10G dumpfile=PUMP_DIR:expdp_testdb_APP_%U.dmp logfile=testdb_APP_exp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.564 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
................................................

After Data Pump export task completed on the source database, some export dump file will be created

$ id
uid=106(oracle) gid=104(dba)
$ cd /backup01
$ ll -ls expdp*
5849840 -rw-r-----   1 oracle     dba        2989244416 Dec 15 10:45 expdp_test_App_01.dmp
3513936 -rw-r-----   1 oracle     dba        1795604480 Dec 15 10:45 expdp_test_App_02.dmp
2511824 -rw-r-----   1 oracle     dba        1283526656 Dec 15 10:45 expdp_test_App_03.dmp
187672 -rw-r-----   1 oracle     dba        95895552 Dec 15 10:45 expdp_test_App_04.dmp
$

use the scp command to copy the dump file from source database server to target database server

Import the user schema to the target database

--import testdb_App schema only through data pump with multi dump files

[oracle@newzonetest01 backup01]impdp \'sys/oracle123@testdb as sysdba\' schemas='testdb_App' job_name=import_testdb_APP01 directory=PUMP_DIR dumpfile=PUMP_DIR:expdp_testdb_APP_%U.dmp logfile=testdb_APP_imp.log

;;;
Import: Release 11.2.0.4.0 - Production on Tue Dec 15 12:49:30 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."IMPORT_testdb_APP01" successfully loaded/unloaded
Starting "SYS"."IMPORT_testdb_APP01":  "sys/********@testdb AS SYSDBA" parallel=4 schemas=pas job_name=import_testdb_APP01 directory=PUMP_DIR dumpfile=PUMP_DIR:expdp_testdb_APP_%
U.dmp logfile=testdb_APP_imp.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"PAS" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.....................................................
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: