Skip to content

Cross-Platform Transportable Database

October 2, 2013

Cross-Platform Transportable Database:

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

 

RMAN CONVERT DATABASE from Windows to Solaris using RMAN, and upgrade the same from 10.2.0.1 to 10.2.0.2.

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

The RMAN CONVERT DATABASE command is used to automate the movement of an entire database

From one platform (the source platform) to another (the destination platform).

Provided that the source and destination platforms are of the same endian format,

RMAN automates most of the steps of creating a new database on the destination platform,

Which contains the same data as the source database, and which has, with a few exceptions, the same settings as the source database.

Windows: Win-7 32 bit: Source

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

SQL> set pages 50
SQL> select * from v$transportable_platform order by 2;
PLATFORM_ID PLATFORM_NAME                                      ENDIAN_FORMAT
----------- -------------------------------------------------- --------------
6 AIX-Based Systems (64-bit)                         Big
16 Apple Mac OS                                       Big
15 HP Open VMS                                        Little
5 HP Tru64 UNIX                                      Little
3 HP-UX (64-bit)                                     Big
4 HP-UX IA (64-bit)                                  Big
18 IBM Power Based Linux                              Big
9 IBM zSeries Based Linux                            Big
13 Linux 64-bit for AMD                               Little
10 Linux IA (32-bit)                                  Little
11 Linux IA (64-bit)                                  Little
12 Microsoft Windows 64-bit for AMD                   Little
7 Microsoft Windows IA (32-bit)                      Little  (our Source)
8 Microsoft Windows IA (64-bit)                      Little
17 Solaris Operating System (x86)                    Little  (our Target)
1 Solaris[tm] OE (32-bit)                            Big
2 Solaris[tm] OE (64-bit)                            Big
17 rows selected.
col PLATFORM_NAME for a50
select d.platform_name, endian_format from
v$transportable_platform tp,
v$database d where tp.platform_name = d.platform_name ;
PLATFORM_NAME                                      ENDIAN_FORMAT
-------------------------------------------------- --------------
Microsoft Windows IA (32-bit)                      Little
SQL> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.1.0

 

Solaris: (Target)

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

-bash-3.00$ uname -a
SunOS orasolaris1 5.10 Generic_142910-17 i86pc i386 i86pc
-bash-3.00$ cat /etc/release
Oracle Solaris 10 9/10 s10x_u9wos_14a X86
Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
Assembled 11 August 2010
-bash-3.00$ isainfo -kv
32-bit i386 kernel modules
You can check the Platform from one of the existing DB’s on Target as below.
col PLATFORM_NAME for a50
select d.platform_name, endian_format from
v$transportable_platform tp,
v$database d where tp.platform_name = d.platform_name ;
PLATFORM_NAME                                      ENDIAN_FORMAT
-------------------------------------------------- --------------
Solaris Operating System (x86)                     Little

Windows : (Source)

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

SQL> select name from v$database;
NAME
---------
Q041

CheckSize of DB:
=================
set line 200
select round(DF.TOTAL/1024/1024/1024,2) "DataFile Size GB",
round(LOG.TOTAL/1024/1024/1024,2) "Redo Log Size GB",
round(CONTROL.TOTAL/1024/1024,2) "Control File Size MB",
round((DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576,2) "Total Size Mb",
round((DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576/1024,2) "Total Size Gb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
DataFile Size GB Redo Log Size GB Control File Size MB Total Size Mb Total Size Gb
---------------- ---------------- -------------------- ------------- -------------
.61              .15                21.14        796.14           .78

SQL> create table mtest(nam varchar2(20));
Table created.
SQL> insert into mtest values('Cross Plat Mig');
1 row created.
SQL> commit;
Commit complete.

Check Pre-requisite on Source DB:

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

SQL>
set serveroutput on
declare
db_ready boolean;
begin
db_ready := dbms_tdb.check_db('Solaris Operating System (x86)');
end;
/
Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and retry.
PL/SQL procedure successfully completed.
SQL>
set serveroutput on
declare
external boolean;
begin
/* value of external is ignored, but with SERVEROUTPUT set to ON
* dbms_tdb.check_external displays report of external objects
* on console */
external := dbms_tdb.check_external;
end;
/
The following directories exist in the database:
SYS.DATA_PUMP_DIR
PL/SQL procedure successfully completed.

Make the Source DB as Read only:

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

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  369098752 bytes
Fixed Size                  1249080 bytes
Variable Size              83886280 bytes
Database Buffers          276824064 bytes
Redo Buffers                7139328 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
set line 200
select NAME,DB_UNIQUE_NAME,OPEN_MODE,CONTROLFILE_TYPE,DATABASE_ROLE,LOG_MODE,PROTECTION_MODE from v$database;
NAME      DB_UNIQUE_NAME                 OPEN_MODE  CONTROL DATABASE_ROLE    LOG_MODE     PROTECTION_MODE
--------- ------------------------------ ---------- ------- ---------------- ------------ --------------------
Q041      q041                           READ ONLY  CURRENT PRIMARY          ARCHIVELOG   MAXIMUM PERFORMANCE

Check datafile location:
=========================
select distinct substr(NAME,1,instr(name,'\',-1)-1) PATH from v$datafile;
PATH
========================
C:\ORACLE\ORADATA\Q041

Now Covert the Source DB(q041):

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

c:\Oracle>set oracle
ORACLE_HOME=c:\oracle\product\10.2.0\db_1
oracle_sid=q041
rman target /
CONVERT DATABASE NEW DATABASE 'test'
transport script 'C:\VMDisks\winshare\orabkp\migtest\trans.sql'
to platform 'Solaris Operating System (x86)'
db_file_name_convert 'C:\ORACLE\ORADATA\Q041' 'C:\VMDisks\winshare\orabkp\migtest'
;
c:\Oracle>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Sep 27 12:12:02 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: Q041 (DBID=697726678)
RMAN> CONVERT DATABASE NEW DATABASE 'test'
2>         transport script 'C:\VMDisks\winshare\orabkp\migtest\trans.sql'
3>         to platform 'Solaris Operating System (x86)'
4>         db_file_name_convert 'C:\ORACLE\ORADATA\Q041' 'C:\VMDisks\winshare\orabkp\migtest'
5>         ;
Starting convert at 27-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
Directory SYS.DATA_PUMP_DIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=C:\ORACLE\ORADATA\Q041\SYSTEM01.DBF
converted datafile=C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=C:\ORACLE\ORADATA\Q041\UNDOTBS01.DBF
converted datafile=C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=C:\ORACLE\ORADATA\Q041\SYSAUX01.DBF
converted datafile=C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=C:\ORACLE\ORADATA\Q041\USERS01.DBF
converted datafile=C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\TRANS.SQL on the target platform to create database
Edit init.ora file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00OL2HTU_1_0.ORA. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 27-SEP-13
RMAN>

Manually create pfile to converted location, though it’s not required.

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

create pfile=’C:\VMDisks\winshare\orabkp\migtest\pfile.ora’ from spfile;

OR

Edit init.ora file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00OL2HTU_1_0.ORA, and set values as per your new environment.This PFILE will be used to create the database on the target platform.

# Please change the values of the following parameters:

control_files            = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CF_D-TEST_ID-697726678_00OKUCLB"
audit_file_dest          = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ADUMP"
background_dump_dest     = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\BDUMP"
user_dump_dest           = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UDUMP"
core_dump_dest           = "C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\CDUMP"
db_name                  = "TEST"

Modified values

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

*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.control_files='/oradata1/test/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'
*.db_name="TEST"

Ftp/Scp/transfer the converted files to Solaris target host:

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

This ftp can be avoided if this filesystem is shared between Windows and Solaris as in my case below.

 

C:\VMDisks\winshare\orabkp\migtest\*

 C:\Windows\System32>dir C:\VMDisks\winshare\orabkp\migtest\*
 Volume in drive C is Root
 Volume Serial Number is BCFF-7226
 
 Directory of C:\VMDisks\winshare\orabkp\migtest
 
29-09-2013  12:13    <DIR>          .
29-09-2013  12:13    <DIR>          ..
29-09-2013  12:13       125,837,312 SYSAUX01.DBF
29-09-2013  12:12       314,580,992 SYSTEM01.DBF
29-09-2013  12:13             2,834 TRANS.SQL
29-09-2013  12:13       209,723,392 UNDOTBS01.DBF
29-09-2013  12:13         5,251,072 USERS01.DBF
               5 File(s)    655,395,602 bytes
               2 Dir(s)  116,189,528,064 bytes free
 
-bash-3.00$ df -h |grep win
winshare               225G   117G   108G    52%    /mnt/sf_winshare
 
-bash-3.00$ cd /mnt/sf_winshare/orabkp/migtest
-bash-3.00$ ls -ltrh
total 1280070
-rwxrwxrwx   1 root     vboxsf      300M Sep 29 12:12 SYSTEM01.DBF
-rwxrwxrwx   1 root     vboxsf      200M Sep 29 12:13 UNDOTBS01.DBF
-rwxrwxrwx   1 root     vboxsf      120M Sep 29 12:13 SYSAUX01.DBF
-rwxrwxrwx   1 root     vboxsf      5.0M Sep 29 12:13 USERS01.DBF
-rwxrwxrwx   1 root     vboxsf      2.8K Sep 29 12:13 TRANS.SQL
-bash-3.00$

 

 Copy the datafiles to required directories in Solaris

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

cp /mnt/sf_winshare/orabkp/migtest/*  /oradata1/test/
-bash-3.00$ cd /oradata1/test/
 -bash-3.00$ ls -ltrh
total 1602926
-rwxr-xr-x   1 oracle   dba          942 Sep 27 22:48 pfile.ora
-rwxr-xr-x   1 oracle   dba         2.3K Sep 27 22:48 TRANS.SQL
drwxr-xr-x   2 oracle   dba          512 Sep 29 22:38 arch
-rwxr-xr-x   1 oracle   dba         5.0M Sep 29 22:59 USERS01.DBF
-rwxr-xr-x   1 oracle   dba         120M Sep 29 22:59 SYSAUX01.DBF
-rwxr-xr-x   1 oracle   dba         300M Sep 29 22:59 SYSTEM01.DBF
-rwxr-xr-x   1 oracle   dba         200M Sep 29 22:59 UNDOTBS01.DBF
-rw-r-----   1 oracle   dba         7.0M Sep 29 22:59 control01.ctl


Create required directories for test DB

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

 

cd /u01/app/oracle/admin
mkdir test
cd test
mkdir adump bdump cdump udump

 

Create Archive folder

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

 cd /oradata1/test

mkdir arch
cd arch
pwd
/oradata1/test/arch


Verify and Copy the pfile to ORACLE_HOME/dbs in Solaris.

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

-bash-3.00$ cat /oradata1/test/pfile.ora
test.__db_cache_size=276824064
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__shared_pool_size=75497472
test.__streams_pool_size=0
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.java_pool_size=0
*.job_queue_processes=10
*.log_archive_format='test_arch_T%tS%sR%r.arc'
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=367001600
*.sga_target=367001600
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_name='test'
*.log_archive_dest_1='LOCATION=/oradata1/test/arch'
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.background_dump_dest='/u01/app/oracle/admin/test/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oradata1/test/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/test/cdump'
*.user_dump_dest='/u01/app/oracle/admin/test/udump'

 

Copy the pfile to init<SID>.ora

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

 cp /oradata1/test/pfile.ora $ORACLME/dbs/inittest.ora
 

Modify and run file TRANS.SQL which got generated as part of convert

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

Before Modification

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

 

STARTUP NOMOUNT PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00OL2HTU_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-TEST_ID-697726678_S-23_T-1_A-826055072_00OL2HTU'  SIZE 50M,
  GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-TEST_ID-697726678_S-24_T-1_A-826055072_00OL2HTU'  SIZE 50M,
  GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-TEST_ID-697726678_S-22_T-1_A-826055072_00OL2HTU'  SIZE 50M
DATAFILE
  'C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\SYSTEM01.DBF',
  'C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\UNDOTBS01.DBF',
  'C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\SYSAUX01.DBF',
  'C:\VMDISKS\WINSHARE\ORABKP\MIGTEST\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;
 
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-TEST_I-697726678_TS-TEMP_FNO-1_00OL2HTU'
     SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
 
SHUTDOWN IMMEDIATE 
STARTUP UPGRADE PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00OL2HTU_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql 
SHUTDOWN IMMEDIATE 
STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00OL2HTU_1_0.ORA'
@@ ?/rdbms/admin/utlrp.sql 
set feedback 6;

 

After modification

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

-bash-3.00$ cat TRANS.SQL
 
REM STARTUP NOMOUNT PFILE='/oradata1/test/pfile.ora'
STARTUP NOMOUNT 
CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata1/test/redo01.rdo'  SIZE 50M,
  GROUP 2 '/oradata1/test/redo02.rdo'  SIZE 50M,
  GROUP 3 '/oradata1/test/redo03.rdo'  SIZE 50M
DATAFILE
  '/oradata1/test/SYSTEM01.DBF',
  '/oradata1/test/UNDOTBS01.DBF',
  '/oradata1/test/SYSAUX01.DBF',
  '/oradata1/test/USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;
 
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
 
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/test/temp01.dbf'
     SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
 
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt *    or the global database name for this database. Use the
prompt *    NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
SHUTDOWN IMMEDIATE
REM STARTUP UPGRADE PFILE='/oradata1/test/pfile.ora'
STARTUP UPGRADE 
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
REM STARTUP PFILE='/oradata1/test/pfile.ora'
STARTUP 
 
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql

 

*****************End of TRANS.SQL **************

 

Run the script TRANS.SQL

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

export ORACLE_SID=test
 sh-3.00$ sqlplus / as sysdba
 SQL*Plus: Release 10.2.0.2.0 - Production on Mon Sep 30 20:50:52 2013
 Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
 Connected to an idle instance.
------------------------------------------------------------------------------
SQL> set echo on
SQL> @TRANS.SQL
SQL> -- The following commands will create a new control file and use it
SQL> -- to open the database.
SQL> -- Data used by Recovery Manager will be lost.
SQL> -- The contents of online logs will be lost and all backups will
SQL> -- be invalidated. Use this only if online logs are damaged.
SQL>
SQL> -- After mounting the created controlfile, the following SQL
SQL> -- statement will place the database in the appropriate
SQL> -- protection mode:
SQL> --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
SQL>
REM> STARTUP NOMOUNT PFILE='/oradata1/test/pfile.ora'
SQL> STARTUP NOMOUNT 
 
ORACLE instance started.
 
Total System Global Area  369098752 bytes
Fixed Size                  1280300 bytes
Variable Size             113247956 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
 
SQL> CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata1/test/redo01.rdo'  SIZE 50M,
  9    GROUP 2 '/oradata1/test/redo02.rdo'  SIZE 50M,
 10    GROUP 3 '/oradata1/test/redo03.rdo'  SIZE 50M
 11  DATAFILE
 12    '/oradata1/test/SYSTEM01.DBF',
 13    '/oradata1/test/UNDOTBS01.DBF',
 14    '/oradata1/test/SYSAUX01.DBF',
 15    '/oradata1/test/USERS01.DBF'
 16  CHARACTER SET WE8MSWIN1252
 17  ;
 
Control file created.
 
SQL>
SQL> -- Database can now be opened zeroing the online logs.
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
  

SQL>
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/test/temp01.dbf'
  2       SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ERROR:
ORA-03114: not connected to ORACLE
 
 
SQL> -- End of tempfile additions.
SQL> --
SQL>
SQL> set echo off
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
*    or the global database name for this database. Use the
*    NEWDBID Utility (nid).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if there the database was not opened in UPGRADE mode
DOC>
DOC>   If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC>   re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
  2  WHERE status != 'OPEN MIGRATE';
ERROR:
ORA-03114: not connected to ORACLE

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
sh-3.00$
sh-3.00$ exit
 

——————————————————————————

 

Note: Script failed to open database in Reset Logs because the Our Source DB version was 10.2.0.1 and on Solaris , Oracle software is 10.2.0.2. we need to upgrade before continuing further steps.

 

Alert.log

==========

Mon Sep 30 20:52:40 2013
Errors in file /u01/app/oracle/admin/test/udump/test_ora_1577.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Mon Sep 30 20:52:40 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1577
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...

 

We need to manually perform further steps to upgrade to 10.2.0.2 as below:

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

 

SQL> startup upgrade
ORACLE instance started.
 
Total System Global Area  369098752 bytes
Fixed Size                  1280300 bytes
Variable Size             113247956 bytes
Database Buffers          251658240 bytes
Redo Buffers                2912256 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oradata1/test/SYSTEM01.DBF'

SQL>

SQL> select GROUP#,SEQUENCE#,FIRST_CHANGE# from  v$log;
 
    GROUP#  SEQUENCE#   FIRST_CHANGE#
---------- ---------- ---------------
         1          0               0
         3          1          317338
         2          0               0
 
SQL> !ls -ltrh /oradata1/test/*.rdo
-rw-r-----   1 oracle   dba          50M Sep 30 20:52 /oradata1/test/redo01.rdo
-rw-r-----   1 oracle   dba          50M Sep 30 20:52 /oradata1/test/redo02.rdo
-rw-r-----   1 oracle   dba          50M Sep 30 20:52 /oradata1/test/redo03.rdo

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 317339 generated at 09/30/2013 20:52:39 needed for thread 1
ORA-00289: suggestion : /oradata1/test/arch/test_arch_T1S1R827527954.arc
ORA-00280: change 317339 for thread 1 is in sequence #1
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata1/test/redo03.rdo  => Here we applied the last redo generated from resetlogs.
Log applied.                                   
Media recovery complete.


Now database recovered successfully ,so open database with resetlogs for upgrade as below

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

SQL> ALTER DATABASE OPEN RESETLOGS upgrade; 
Database altered.

 

Alert.log

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

Mon Sep 30 21:06:42 2013
ALTER DATABASE RECOVER    LOGFILE '/oradata1/test/redo03.rdo'
Mon Sep 30 21:06:42 2013
Media Recovery Log /oradata1/test/redo03.rdo
Mon Sep 30 21:06:42 2013
Incomplete recovery applied all redo ever generated.
Recovery completed through change 317341
Mon Sep 30 21:06:42 2013
Media Recovery Complete (test)
Completed: ALTER DATABASE RECOVER    LOGFILE '/oradata1/test/redo03.rdo'
Mon Sep 30 21:08:21 2013
ALTER DATABASE OPEN RESETLOGS upgrade
Mon Sep 30 21:08:21 2013
RESETLOGS after complete recovery through change 317341
Resetting resetlogs activation ID 2125700729 (0x7eb39e79)
Online log /oradata1/test/redo01.rdo: Thread 1 Group 1 was previously cleared
Online log /oradata1/test/redo02.rdo: Thread 1 Group 2 was previously cleared
Mon Sep 30 21:08:23 2013
Setting recovery target incarnation to 3
Mon Sep 30 21:08:23 2013
Assigning activation ID 2125681971 (0x7eb35533)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=13, OS id=1620
Mon Sep 30 21:08:23 2013
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thread 1 opened at log sequence 1
  Current log# 3 seq# 1 mem# 0: /oradata1/test/redo03.rdo
….
….
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Mon Sep 30 21:08:24 2013
SMON: enabling tx recovery
Mon Sep 30 21:08:24 2013
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:
 
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
 
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is WE8MSWIN1252
Updating 10.2.0.1.0 NLS parameters in sys.props$
-- adding 10.2.0.2.0 NLS parameters.
….

MMON started with pid=11, OS id=1626
MMNL started with pid=12, OS id=1628
Mon Sep 30 21:08:29 2013
ALTER SYSTEM enable restricted session;
Mon Sep 30 21:08:29 2013
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Mon Sep 30 21:08:29 2013
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Mon Sep 30 21:08:29 2013
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
Threshold validation cannot be done before catproc is loaded.
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Sep 30 21:08:30 2013
LOGSTDBY: Validating controlfile with logical metadata
Mon Sep 30 21:08:30 2013
LOGSTDBY: Validation complete
Global Name changed to TEST
Completed: ALTER DATABASE OPEN RESETLOGS upgrade
Mon Sep 30 21:09:23 2013

 

Add Temp tablespace:

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

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata1/test/temp01.dbf'
SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
 
Tablespace altered.
 

col HOST_NAME for a20
col DB_UNIQUE_NAME for a10
set line 200
select NAME,DB_UNIQUE_NAME,STATUS,OPEN_MODE,CONTROLFILE_TYPE,DATABASE_ROLE,LOG_MODE,PROTECTION_MODE,VERSION from v$instance,v$database;
 
NAME      DB_UNIQUE_ STATUS       OPEN_MODE  CONTROL DATABASE_ROLE    LOG_MODE     PROTECTION_MODE      VERSION
--------- ---------- ------------ ---------- ------- ---------------- ------------ -------------------- -----------------
TEST      test       OPEN MIGRATE READ WRITE CURRENT PRIMARY          ARCHIVELOG   MAXIMUM PERFORMANCE  10.2.0.2.0

 

Upgrade DB, as the database is open in Upgrade mode:

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

Run the Catupgrd.sql to upgrade DB to 10.2.0.2

 

SQL> @?/rdbms/admin/catupgrd.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END  2013-09-30 21:49:50
.
Oracle Database 10.2 Upgrade Status Utility           09-30-2013 21:49:50
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.2.0  00:09:08
Oracle Enterprise Manager                 VALID      10.2.0.2.0  00:00:43
.
Total Upgrade Time: 00:09:51
DOC>#######################################################################
DOC>#######################################################################
……
……
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################

 

Alert.log

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

Mon Sep 30 21:40:01 2013
SERVER COMPONENT id=PATCH_BGN: timestamp=2013-09-30 21:40:01
….

Thread 1 advanced to log sequence 9
  Current log# 2 seq# 9 mem# 0: /oradata1/test/redo02.rdo
Mon Sep 30 21:49:06 2013
SERVER COMPONENT id=CATPROC: timestamp=2013-09-30 21:49:06
SERVER COMPONENT id=RDBMS: status=VALID, version=10.2.0.2.0, timestamp=2013-09-30 21:49:06
Mon Sep 30 21:49:11 2013
Thread 1 advanced to log sequence 10
  Current log# 3 seq# 10 mem# 0: /oradata1/test/redo03.rdo
Mon Sep 30 21:49:50 2013
SERVER COMPONENT id=EM: status=VALID, version=10.2.0.2.0, timestamp=2013-09-30 21:49:50
SERVER ACTION=UPGRADE id=: Upgraded from 10.2.0.1.0
SERVER COMPONENT id=UPGRD_END: timestamp=2013-09-30 21:49:50
Mon Sep 30 21:52:41 2013
 
set line 200
col COMP_NAME for a55
SELECT COMP_ID,comp_name, status, substr(version,1,10)as version,MODIFIED from dba_registry;
 
COMP_ID                        COMP_NAME                                               STATUS      VERSION    MODIFIED
------------------------------ ------------------------------------------------------- ----------- ---------- --------------------
OWM                            Oracle Workspace Manager                                VALID       10.2.0.1.0 13-SEP-2013 15:53:13
EM                             Oracle Enterprise Manager                               VALID       10.2.0.2.0 30-SEP-2013 21:49:50
CATALOG                        Oracle Database Catalog Views                           VALID       10.2.0.2.0 30-SEP-2013 21:49:06
CATPROC                        Oracle Database Packages and Types                      VALID       10.2.0.2.0 30-SEP-2013 21:49:06
SQL>
SQL>

 

 

Now follow further steps from TRANS.SQL for migrating:

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

 SHUTDOWN IMMEDIATE
REM STARTUP UPGRADE PFILE='/oradata1/test/pfile.ora'
STARTUP UPGRADE 
@@ ?/rdbms/admin/utlirp.sql
 

Out Put of above utlirp.sql

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

….
….
 
SQL> Rem ===========================================================================
SQL> Rem END utlip.sql
SQL> Rem ===========================================================================
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   utlirp.sql completed successfully. All PL/SQL objects in the
DOC>   database have been invalidated.
DOC>
DOC>   Shut down and restart the database in normal mode and run utlrp.sql to
DOC>   recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################

 

Execute Below further from TRANS.sql

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

 

SHUTDOWN IMMEDIATE
REM STARTUP PFILE='/oradata1/test/pfile.ora'
STARTUP 
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oradata1/test/arch
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16 
 
SQL> select * from mtest;
 
NAM
--------------------
Cross Plat Mig
 
set line 200
col COMP_NAME for a55
SELECT COMP_ID,comp_name, status, substr(version,1,10)as version,MODIFIED from dba_registry;
 
COMP_ID                        COMP_NAME                                               STATUS      VERSION    MODIFIED
------------------------------ ------------------------------------------------------- ----------- ---------- --------------------
OWM                            Oracle Workspace Manager                                VALID       10.2.0.1.0 30-SEP-2013 22:07:40
EM                             Oracle Enterprise Manager                               VALID       10.2.0.2.0 30-SEP-2013 21:49:50
CATALOG                        Oracle Database Catalog Views                           VALID       10.2.0.2.0 30-SEP-2013 22:07:40
CATPROC                        Oracle Database Packages and Types                      VALID       10.2.0.2.0 30-SEP-2013 22:07:40
 

 

* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
 
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
*    or the global database name for this database. Use the
*    NEWDBID Utility (nid).

 

Reference :

http://docs.oracle.com/cd/B19306_01/backup.102/b14191/dbxptrn.htm

Migration of Oracle Instances Across OS Platforms – Doc ID: Note:733205.1

Click here to get the PDF Doc.

Advertisements

From → Oracle

Leave a Comment

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: