There are the steps we are going to do:

1. Find a hot backup of production database ELAPROD (version 8.1.7) located on PEKO Unix server:
User: orapeko
Directory: /peko/backups/elaprod/hotbkup___2005

2. Move it other Unix server – BIBBLE – where Oracle10g server is installed

3. Startup ELAPROD database with a new name ELA10G.

Upgrade Database

Pre-Upgrade Tasks

4. Go to production database SQL*Plus console:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

5. Go to Udump directory and find the new created trace file.

6. For easy use rename it and move to the Hot Backup directory:
$ mv elaprod_ora_21768.trc /peko/backups/elaprod/hotbkup_Feb_28_2005/control.sql

7. In addition move init.ora file to the same directory.

8. Go to the server when Oracle10g is installed. Build there all the appropriate directories for datafiles, dump directories and archive directory.

9. Move Hot Backup directory to the server (see step 5.):
/peko/backups/elaprod> cd /peko/backups/elaprod/hotbkup_Feb_28_2005
/peko/backups/elaprod/hotbkup_Feb_28_2005> rcp *.* oracle@bibble:/bibble/dw/ela10g
/peko/backups/elaprod/hotbkup_Feb_28_2005> rcp arclog/*.arc oracle@bibble:/bibble/dw/ela10g

10. Go to 10G Unix server (in our case BIBBLE) and make the following changes:

a) Copy init.ora to dbs of Oracle8i server directory and make appropriated changes

b) Make appropriate changes in control.sql file.

c) Make appropriate environment (817). CHECK TWICE THE ORACLE DATABASE VERSION: 32-bit or 64-bit

d) Create password file for a new database.

e) Drop old control files.

11. /bibble/dw/ela10g> sts ela10g

/bibble/dw/ela10g> sqlplus '/as sysdba'
SQLPLUS> connect internal

SQLPLUS> startup nomount
SQLPLUS> @control.sql
SQLPLUS> recover database using backup controlfile until cancel;
SQLPLUS> alter database open resetlogs;

12. Change all the datafiles to be AUTOEXTENSIBLE:

SVRMGRL> select 'ALTER DATABASE DATAFILE '||chr(39)||file_name|| chr(39)||' AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;'
from dba_data_files
union
select 'ALTER DATABASE TEMPFILE'|| chr(39)||file_name|| chr(39)||' AUTOEXTEND ON NEXT 512K MAXSIZE UNLIMITED;'
from dba_temp_files;
SVRMGRL> Shutdown immediate;
SVRMGRL> exit;

13. Take a cold backup in order to restore the db fast in case of Upgrade failure

14. Put the following line in oratab file:
ela10g:/bibble/app01/oracle/product/8.1.7:N

15. Increase shared_pool_size parameter up to 500M!

16. Start the database before upgrade.

Upgrade Tasks

1. Invoke 10g environment and define ORACLE_SID variable
/bibble/app01/oracle/product> 10g
/bibble/app01/oracle/product> setenv ORACLE_SID ela10g

2. Invoke Oracle10g Database Upgrade Assistant and check the database you want to upgrade:
/bibble/app01/oracle/product> dbua

3. If you get "cannot start already-running" error then remove a shared memory segment or semaphores as per the following link:

Cannot Start – Already running

4. Shutdown an upgraded 10g database and take a cold backup.

Export Metadata

Check whether all the tablespaces for transport can be transported.

SQL> DECLARE

TS_LIST CLOB;

INCL_CONSTRAINTS BOOLEAN;

FULL_CHECK BOOLEAN;

BEGIN

INCL_CONSTRAINTS := NULL;

FULL_CHECK := NULL;

SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('TBS_DATA_BIG, TBS_DATA_SMALL, TBS_INDEX_BIG, TBS_INDEX_SMALL', INCL_CONSTRAINTS, TRUE );

COMMIT;

END;

Then check sys.transport_set_violations. If there are no rows, then do the export. If rows are returned, there are violations.

SQL> select count(*) from sys.transport_set_violations;

Make the necessary tablespaces read only.
Run export command:

/bibble/app01/oracle/product> exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=TBS_ELA_DATA_BIG, TBS_ELA_DATA_SMALL, TBS_ELA_INDEX_BIG, TBS_ELA_INDEX_SMALL, TBS_LOG_TAB_TMP file=/bibble/dw/ela10g/TTS_10g.dmp log=/bibble/dw/ela10g/TTS_10g.log statistics=none

Execute the CONVERT tablespace command to change datafiles to Linux format.

Change COMPATIBLE parameter to 10.1.0.3.0
Restart 10g database.
Prepare the list of all tablespaces within a new 10g database.
Check the OS platform compatibility, from Solaris to Linux

SQL> set pages 50000 lines 120

SQL> col platform_name for a40

SQL> select * from v$transportable_platform where upper(PLATFORM_NAME) like '%SOLARIS%';

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

1 Solaris[tm] OE (32-bit) Big

2 Solaris[tm] OE (64-bit) Big

17 Solaris Operating System (x86) Little

SQL> select * from v$transportable_platform where upper(PLATFORM_NAME) like '%AMD%';

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

13 Linux 64-bit for AMD Little

12 Microsoft Windows 64-bit for AMD Little

Because endian_format is different between both platforms conversion is required.

Invoke RMAN utility.

{oracle} /bibble/dw/ela10g [Bibble] > rman target=/

Recovery Manager: Release 10.1.0.3.0 - 64bit Production

connected to target database: ELA10G (DBID=1310944443)

RMAN> report schema;

using target database controlfile instead of recovery catalog

Report of database schema

File K-bytes Tablespace RB segs Datafile Name

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

1 460800 SYSTEM *** /bibble/dw/ela10g/system_01_elatest.dbf

2 205312 RBS *** /bibble/dw/ela10g/rbs_01.dbf

3 2634752 TBS_ELA *** /bibble/dw/ela10g/tbs_ela_01.dbf

4 1644280 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_02.dbf

5 2041544 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_03.dbf

6 2047384 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_04.dbf

15 1999368 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_07.dbf

16 16 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_08.dbf

17 51200 SQL_REPOSITORY *** /bibble/dw/ela10g/tbs_sql_repository_01.dbf

18 962560 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_09.dbf

19 71680 TBS_ELA_DATA *** /bibble/dw/ela10g/tbs_data_elatest_10.dbf

RMAN> convert tablespace TBS_ELA_DATA_BIG, TBS_ELA_DATA_SMALL, TBS_ELA_INDEX_BIG, TBS_ELA_INDEX_SMALL, TBS_LOG_TAB_TMP to platform 'Linux 64-bit for AMD' format='/bibble/dw/ela10g/converted_db/%U';

Starting backup at 14/03/2005 09:39:37

using target database controlfile instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=1080 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00092 name=/bibble/dw/ela10g/tbs_ela_data_big_01.dbf

converted datafile=/bibble/dw/ela10g/converted_db/data_D-ELA10G_I-1310944443_TS-TBS_ELA_DATA_BIG_FNO-92_01gf9dit

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:06

Finished backup at 14/03/2005 09:43:47

6. Rename all the files to native names:
mv data_D-ELA10G_I-1310944443_TS-TBS_ELA_INDX_FNO-76_08ged3f3 tbs_indexe_elatest_04.dbf

Copy all the data files and export file to the Linux server:
rcp /bibble/dw/ela10g/converted_db/*.* oracle@smithers:/smithers/od01/ela10g/

Import Metadata

1. Create a new 10g database (ela10g).

2. Do not forget to change COMPATIBLE parameter in a new database exactly as in the source database.

3. Create all appropriate users, which depend on transportable tablespaces.

4. Invoke import utility in order to import the transportable tablespaces:
imp userid=\'/ as sysdba\' transport_tablespace=y datafiles= /smithers/od01/ela10g/tbs_ela_01.dbf, /smithers/od01/ela10g/tbs_snapilh_01.dbf, /smithers/od01/ela10g/tbs_log_tab_01.dbf file=TTS_10g.dmp log=TTS_imp_10g.log

Check in both databases (target and source) the equal amount of the objects:

SQL> select owner, count(*)
from dba_segments
where tablespace_name in ('TBS_ELA_DATA', 'TBS_SNAP_DATA', 'TBS_LOG_TAB' )
and owner not in ('CTXSYS','IFLOCAL35','OPS$ORACLE','OUTLN','SYSTEM')
group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
ELAADM                                  5
NEWILH                                285
ROAMING                                31
SNAPILH                                99
STAT                                    4
During the attempt to alter the TBS_ELA_DATA and TBS_SNAP_DATA to read write the error had appeared:
SQL> alter tablespace tbs_snap_data read write;
alter tablespace tbs_snap_data read write
*
ERROR at line 1:
ORA-12915: Cannot alter dictionary managed tablespace to read write
Error:      ORA-12915 (ORA-12915)
Text: Cannot alter dictionary managed tablespace to read write
---------------------------------------------------------------------------
Cause:      Attemp to alter dictionary managed tablespace to read write in
database which has system tablespace as locally managed. This tablespace can only be dropped.
Action:     Command cannot be issued.

It means that if one of the plugged-in tablespaces was DICTIONARY managed you must to move all the objects to Locally-managed tablespace and drop Dictionary tbs.

Simple Oracle, MS SQL, Postgres, AWS and more

Thursday, December 17, 2009

How to upgrade Oracle8i Database to Oracle10g and move it from Unix to Linux

Introduction

Upgrade Database

1 comment: