Important Note: Before proceeding with database conversion from one platform to another is recommended to make a copy of original database on a file system different from the source database and do the rest of conversion steps. This is to avoid downtime and any performance impacts and corruption issues.
1. Some Pre-Requisites or assumptions of source and target directories etc:
ORCL Directory Structure for datafiles Solaris_Server1 or SOURCE server:
/u01/ORCL/oradata/
/u02/ORCL/oradata/
/u03/ORCL/oradata/
/u04/ORCL/oradata/
/u05/ORCL/oradata/
/u06/ORCL/oradata/
Directory for environment files: /dbms/oracle/local/ORCL/etc
ORCL_COPY Directory Structures for datafiles Solaris_Server1 or SOURCE server:
/aix_conv/ORCL_COPY/u01/
/aix_conv/ORCL_COPY/u02/
/aix_conv/ORCL_COPY/u03/
/aix_conv/ORCL_COPY/u04/
/aix_conv/ORCL_COPY/u05/
/aix_conv/ORCL_COPY/u06/
Directory for environment files: /dbms/oracle/local/ORCL_COPY/etc
ORCL_COPY Directory Structures for datafiles AIX_Server1 or TARGET server:
/aix_conv/ORCL_COPY/u01/
/aix_conv/ORCL_COPY/u02/
/aix_conv/ORCL_COPY/u03/
/aix_conv/ORCL_COPY/u04/
/aix_conv/ORCL_COPY/u05/
/aix_conv/ORCL_COPY/u06/
Directory for environment files: /dbms/oracle/local/ORCL_COPY/etc
ORCL_COPY Directory Structures for datafiles AIX_Server1 or TARGET server:
/u01/ORCL_NEW/oradata/
/u02/ORCL_NEW/oradata/
/u03/ORCL_NEW/oradata/
/u04/ORCL_NEW/oradata/
/u05/ORCL_NEW/oradata/
/u06/ORCL_NEW/oradata/
Directory for environment files: /dbms/oracle/local/ORCL_NEW/etc
2. For ORCL_NEW we will be using raw data files for redo logs and.
Pre-Requisite Checks and prep work to transfer the data files to TARGET Server:
1. Some Pre-Conditions to be made on source(copy) or newly created database ORCL_COPY from ORCL:
I. Check list of platforms that we can convert source database into as below.
SQL> SELECT * FROM v$db_transportable_platform;
II. Shutdown and start newly created ORCL_COPY database in "READ ONLY" mode.
SQL> shutdown immediate;
Comment out below parameters:
audit_trail ='DB'
audit_file_dest='/aix_conv/ORCL_COPY/oradata'
db_cache_size =4000M
pga_aggregate_target =9000M
shared_pool_size =5000M
Add below entries from the init.ora file:
db_cache_size =1000M
pga_aggregate_target =2000M
shared_pool_size =1000M
Note: The above change of values to three parameters are recommended since we
don't need high memory values for a copy of the source database and also
recommended in order to minimize impact on the actual database.
SQL> startup mount;
SQL> alter database open read only;
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
ORCL_COPY READ ONLY
III.Use the following PL/SQL to ensure that no Internal Conditions exist that would prevent the transport of the database.
SQL> set serveroutput on
declare
db_ready boolean;
begin
db_ready:= dbms_tdb.check_db ('AIX-Based Systems (64-bit)', dbms_tdb.skip_none);
End;
/
Success output should be:
PS/PL SQL executed successfully
In case of an error output:
Note 1: If output error is similar to:
declare
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_TDB", line 346
ORA-06512: at "SYS.DBMS_TDB", line 397
ORA-06512: at line 4
Resolution:
a. Shutdown the database and startup is READ/WRITE mode
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
b. Add a datafile to TEMP Tablespace and proceed to next step
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘file_location_as_of_your_other_datafiles’ SIZE 500MB;
c. Repeat steps 1 & 2 again and see if you get the “Success output”, if you get it then proceed with next steps
Note 2: If the output error is similar to
Some files in the database have not been converted to the 10.0 compatibility format. Open the database in READ/WRITE mode and alter tablespaces HRAPP, HRLARGE, HRWORK, ............... to READ/WRITE. Then open the database in READ-ONLY mode and retry.
PL/SQL procedure successfully completed.
Resolution:
a. Shutdown the database and startup in READ/WRITE mode
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
b. Take one of the above mentioned tablespaces offline and bring them back online again.
SQL> alter tablespace HRLARGE offline;
SQL> alter tablespace HRLARGE online;
Note: Repeat Step b similarly for all the tablespaces as mentioned in Note2 output before proceeding.
c. Repeat steps 1 & 2 again and see if you get the “Success output”, if you get it proceed with next steps
IV. Execute below PL/SQL to identify any external objects.
SQL> set serveroutput on
declare
external boolean;
begin
external := dbms_tdb.check_external;
end;
/
Success output:
PS/PL SQL executed successfully
In case of an error output:
Note: If output ERROR is something similar to:
The following directories exist in the database:
SYS.DATA_PUMP_DIR
Resolution:
a. Shutdown and startup the database
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
b. Drop the above mentioned directory
SQL> DROP DIRECTORY DATA_PUMP_DIR;
c. Repeat Steps 1 & 3 and see if you get “Success output”, if you get it the proceed with next steps
V. If no error resolutions were as in Step 2: Note 2. Please do the following steps just for any one tablespace. This is to avoid an “ORA-“error or target server regarding SYSTEM file header before proceeding further on. This will update the system file header.
EX: SQL> alter tablespace HRLARGE offline;
SQL> alter tablespace HRLARGE online;
2. Backup control file to trace using the below command and shutdown database
SQL> alter database backup controlfile to trace;
The trace file is generated in the udump directory by the format xxxxxx.trc.
3. Copy the control file trace to temporary location where you need to stage the database for conversion AIX_Server1:/aix_convp. Remember this is the actual location where you will placing the database and will bringing up for future production usage.
4. Copy the init.ora file of ORCL_COPY to AIX_Server1:/aix_conv/ORCL_COPY
$ scp initORCL.ora AIX_Server:/aix_conv/ORCL_COPY
5. Get a count of data files on the source server Solaris_Server1 for database ORCL_COPY
SQL> spool ORCL_data_file_count.log
SQL> select count(*) from dba_data_files;
SQL> spool off
6. Shutdown the database ORCL_COPY on Solaris_Server.
SQL> select instance_name from v$instance;
INSTANCE_NAME
-------------
ORCL_COPY
SQL> shutdown immediate;
7. Change back the init parameters and copy the init.ora file of ORCL_COPY to AIX_Server1:/backup
Delete below entries from the init.ora file:
db_cache_size =1000M
pga_aggregate_target =2000M
shared_pool_size =1000M
Uncomment below parameters:
db_cache_size =4000M
pga_aggregate_target =9000M
shared_pool_size =5000M
$ scp initORCL_COPY1.ora AIX_Server1:/backup/ORCL_COPY
8. Comment out any ORCL_COPY entries in oratab file to avoid future auto startup of the database incase server is rebooted.
$cd /var/opt/oracle
And comment out below entry:
ORCL_COPY:/optware/oracle/10.2.0.4:Y
9. Transfer the files from Solaris_Server1 to AIX_Server1. Make directories with 50Gb each in size within the filesystems to have the datafiles to be copied in multiple streams and to avoid file too large errors on the unix size while copying. :
An Example of break-up of files and how to copy:
/u01=171GB db=57.3 GB db1=57.9 GB db2=56.3 GB
$ cd /aix_conv/ORCL_COPY/u01
$ mkdir db db1 db2
Directory structure under /aix_conv/ORCL_COPY/u01
Ex: /aix_conv/ORCL_COPY/u01/db
/db1
/db2
$ mv *hrlarge.0*dbf db; mv *hrlarge.12.dbf db; mv *hrlarge.13.dbf db
$ mv *hrlarge*.dbf db1; mv *usrtblspc*dbf db1;mv srtr1.pttbl.01.dbf db1
$ mv *.dbf db2
cd /aix_conv/ORCL_COPY/u01/db
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u01 ;zcat | tar xvf -" &
cd /aix_conv/ORCL_COPY/u01/db1
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u01 ;zcat | tar xvf -" &
cd /aix_conv/ORCL_COPY/u01/db2
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u01 ;zcat | tar xvf -" &
/u03=169.8 GB db=57.3 GB db1= 56.6 GB db2= 55.8 GB
$ cd /aix_conv/ORCL_COPY/u03
$ mkdir db db1 db2
$ mv *hrlarge.0*dbf db; mv *hrlarge.10.dbf db; mv *hrlarge.11.dbf db; mv *hrlarge.14.dbf db
$ mv *hrlarge*.dbf db1; mv *ewlarge.0* db1;
$ mv *.dbf db2; mv db1/srt1.hrlarge.27.dbf db2;
cd /backup/ORCL_COPY/u03/db
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u03 ;zcat | tar xvf -" &
cd /backup/ORCL_COPY/u03/db1
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u03 ;zcat | tar xvf -" &
cd /backup/ORCL_COPY/u03/db2
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u03 ;zcat | tar xvf -" &
/u02=4.6 GB
cd /aix_conv/ORCL_COPY/u02
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u02 ;zcat | tar xvf -" &
/u06=4.2 GB
cd /aix_conv/ORCL_COPY/u06
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /backup/u09 ;zcat | tar xvf -" &
Stop here, once /u02 and /u06 finish, start /02 and /04 just to avoid any
performance impact on source database due these copy file sessions when you have
both source and copy of the source on the same server
/u12=106.9 GB db=52.8 GB db1=54.1 GB
$ cd /aix_conv/ORCL_COPY/u02
$ mkdir db db1
$ mv *hrindex.0*dbf db; mv *hrindex.11.dbf db; mv *hrindex.13.dbf db; mv *psindex.15.dbf db; mv *hrindex.17.dbf db
$ mv *.dbf db1
cd /aix_conv/ORCL_COPY/u02/db
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u02 ;zcat | tar xvf -" &
cd /aix_conv/ORCL_COPY/u02/db1
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u02 ;zcat | tar xvf -" &
/u14=92.69 GB db=48.12 GB db1=44.56 GB
$ cd /aix_conv/ORCL_COPY/u04
$ mkdir db db1
$ mv *.0* db; mv *.1* db
$ mv *.dbf db1
cd /aix_conv/ORCL_COPY/u04/db
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u04 ;zcat | tar xvf -" &
cd /aix_conv/ORCL_COPY/u04/db1
tar -cvf - *.dbf | compress | ssh AIX_Server1 "cd /aix_conv/ORCL_COPY/u04 ;zcat | tar xvf -" &
10. Also make sure that you have copied the correct control file trace generated and init.ora files to /backup on AIX_Server1
Locations of the files on Solaris_Server1:
a. trace file location: /backup/ORCL_COPY/oradump
b. init.ora file location: /optware/oracle/ORCL_COPY
Check if all datafiles have been copied correctly
Note: After completion of above confirm the transfer of files.
Ways to check the datafiles have been copied correctly
Compare the datafile count of the file systems of ORCL and ORCL_COPY. Run ORCL_datafile_count.ksh and ORCL_COPY_datafile_count.ksh
Ex- cat ORCL_COPY_datafile_count.ksh on SOLARIS
ls -ltr /aix_conv/ORCL_COPY/u01/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u02/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u03/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u04/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u05/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u06/*.dbf | wc –l
cat ORCL_COPY_datafile_count.ksh on AIX
ls -ltr /aix_conv/ORCL_COPY/u01/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u02/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u03/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u04/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u05/*.dbf | wc -l
ls -ltr /aix_conv/ORCL_COPY/u06/*.dbf | wc –l
Run ps –ef | grep ORCL_COPY on Solaris_Server which will show copy processes since we run them in background.
Conversion on Target:
Bring up the database ORCL_COPY and Convert the datafiles of ORCL_COPY on the target server (AIX_Server1)
Note: Create any missing directories
1. Create and entry for ORCL_COPY in /etc/oratab for the 10.2.0.3_Q108 ORACLE_HOME
ORCL_COPY:/optware/oracle/10.2.0.3_Q108:Y
2. Copy the init.ora file from /backup to/ dbms/oracle/local/ORCL_COPY/etc
$ cp initORCL_COPY.ora /dbms/oracle/local/ORCL_COPY/etc
3. Go to /dbms/oracle/local/ORCL_COPY/etc and
Edit the following in init.ora for ORCL_COPY located in /dbms/oracle/local/ORCL_COPY/etc to reflect the correct file systems.
AUDIT_FILE_DEST ='/aix_conv/ORCL_COPY/oradump/adump'
background_dump_dest='/aix_conv/ORCL_COPY/oradump/bdump'
core_dump_dest='/aix_conv/ORCL_COPY/oradump/cdump'
user_dump_dest='/aix_conv/ORCL_COPY/oradump/udump'
control_files='/aix_conv/ORCL_COPY/u01/SRTR1.control.01.ctl',
'/aix_conv/ORCL_COPY/u02/SRTR1.control.02.ctl',
'/aix_conv/ORCL_COPY/u03/SRTR1.control.03.ctl';
db_name=’ORCL_COPY’
log_archive_dest_1='LOCATION=/aix_conv/ORCL_COPY/archive'
log_archive_format='ORCL_COPY_%s_%t_%r.arc'
service_names=' ORCL_COPY.world',' ORCL_COPY'
4. Run the env file in /dbms/oracle/local/ORCL_COPY/etc to set up environment for ORCL_COPY.
oracle@AIX_Server1[ORCL_COPY] /dbms/oracle/local/ORCL_COPY/etc > . ORCL_COPY.env
5. Make sure the environment is set correctly for SRTR1. Use below
$echo $ORACLE_SID
$echo $ORACLE_HOME
$echo $PATH
6. Create a link in $ORACLE_HOME/dbs for init.ora
$ cd $ORACLE_HOME/dbs
$ ln -s /dbms/oracle/local/ORCL_COPY/etc/initORCL_COPY.ora initORCL_COPY.ora
7. Make a copy of the trace file under /backup to /optware/oracle/work/UPGRADE_PROD and rename it from xxxx.trc to controlfile_create_SRTR1.sql
$ cp ORCL_COPY_ora_22678.trc /optware/oracle/work/UPGRADE_PROD
$ cd /optware/oracle/work/UPGRADE_PROD
$ mv xxxxxx.trc create_controlfile_ORCL_COPY.sql
8. Modify the create_controlfile_ORCL_COPY.sql under /optware/oracle/work/UPGRADE to reflect the correct paths for data files and the correct SID name like below
a. Modify “REUSE” to “SET” in the control file trace and
b. /aix_conv/ORCL_COPY/u04/*.dbf to /aix_conv/ORCL_COPY/u04/*.dbf
c. /aix_conv/ORCL_COPY/u06/*.dbf to /aix_conv/ORCL_COPY/u06/*.dbf
9. Check if all the datafiles are in correct location where the database needs to be staged. Cd to /optware/oracle/work/upgrade. Log on to sqlplus and Startup nomount the database
$cd /optware/oracle/work/UPGRADE
$ sqlplus
SQL> startup nomount
10. Run the control file create sql
SQL> @controlfile_create_ORCL_COPY.sql
Note: Check for any errors in the alert log while creating the control files and resolve them before proceeding with next step.
11. Open the database with reset logs option
SQL> alter database open resetlogs;
12. Add a temp file to the temporary tablespace.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/aix_conv/ORCL_COPY/ORCL_COPY.temp.10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;
13. Check list of platforms that we can convert source database into as below
SQL> SELECT * FROM v$db_transportable_platform;
14. Open the database ORCL_COPY in READ ONLY MODE;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open read only;
14. Make sure that the database in READ ONLY mode
SQL> select NAME,OPEN_MODE from v$database;
NAME OPEN_MODE
--------- ----------
ORCL_COPY READ ONLY
SQL> exit
13. Create a directory named convertdb under /optware/oracle/10.2.0.3_Q108/dbs
$cd $ORACLE_HOME/dbs
$mkdir convertdb
15. Open another session and monitor the alert log to see any error during conversion
16. At this point the RMAN convert database can be used:
$ cd /optware/oracle/10.2.0.3_Q108/
$ rman
--Connect to the target
RMAN> connect target;
--the below converts the datafiles under /aix_conv/ORCL_COPY/uXX to AIX format
and places them under /uXX/oradata/ORCL/. It also creates transport script
in /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript.
RMAN> CONVERT DATABASE NEW DATABASE 'ORCL_NEW'
transport script '/optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript'
to platform 'AIX-Based Systems (64-bit)'
db_file_name_convert
'/aix_conv/ORCL_COPY/u01' '/u01/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u02' '/u02/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u03' '/u03/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u04' '/u04/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u05' '/u05/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u06' '/u06/oradata/ORCL_NEW' ;
Note: After successful conversion of the data files success messages are prompted and the converted data files are created under uxx/oradata/ORCL/db. The conversion should take approximately 6 hours.
The conversion success message at the end of the conversion is as the sample below
************************************************************
input datafile fno=00101 name=/aix_conv/ORCL_COPY/u02/ORCL_COPY.hrtrpts.01.dbf
converted datafile=/u02/oradata/ORCL_NEW/ORCL_NEW.hrtrpts.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00102 name=/aix_conv/ORCL_COPY/u01/ORCL_COPY.hrwlarge.01.dbf
converted datafile=/u01/oradata/ORCL_NEW/ORCL_NEW.hrwlarge.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00103 name==/aix_conv/ORCL_COPY/u03/oradata/ORCL/ORCL.pcapp.01.dbf
converted datafile=/u03/oradata/ORCL_NEW/ORCL_NEW.pcapp.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00104 name==/aix_conv/ORCL_COPY/u01/SRTR1.bplarge.01.dbf
converted datafile=/u01/oradata/ORCL_NEW/ORCL_NEW.hplarge.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript on the target platform to create database
Edit init.ora file /optware/oracle/10.2.0.3_Q108/dbs/init_00k7onuv_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 19-FEB-09
15. After successful completion of RMAN conversion exit out.
RMAN > exit
Bring up database ORCL_NEW:
1. A new control file creation script “transportscript” is created under /optware/oracle/10.2.0.3_Q108/dbs/convertdb
2. Rename the datafiles from ORCL to ORCL_NEW by executing the script rename_files_from_ORCL_COPY_to_ORCL_NEW.ksh located in /optware/oracle/work/UPGRADE_PROD
3. Point the Oracle Home of ORCL_NEW to 10.2.0.3_Q108 in /etc/oratab like below.
ORCL_NEW:/optware/oracle/10.2.0.3_Q108_dbv:Y
4. Run the env file located in /dbms/oracle/local/ORCL_NEW/etc.
$ /dbms/oracle/local/ORCL_NEW/etc > . ORCL_NEW.env
5. Make sure the environment is set correctly for ORCL_NEW. Use below
$echo $ORACLE_SID
$echo $ORACLE_HOME
$echo $PATH
6. Go to $ORACLE_HOME/dbs and create a soft link for initORCL_NEW.ora in $ORACLE_HOME/dbs to point to /dbms/oracle/local/ORCL_NEW/etc. Use below
ln -s /dbms/oracle/local/ORCL_NEW/etc/initORCL_NEW.ora initORCL_NEW.ora
7. Make changes to initORCL_NEW.ora to reflect
AUDIT_FILE_DEST =' /u01/oradump/ORCL_NEW/adump'
background_dump_dest=' /u01/oradump/ORCL_NEW/bdump'
core_dump_dest=' /u01/oradump/ORCL_NEW/cdump'
user_dump_dest=' /u01/oradump/ORCL_NEW/udump'
control_files ='/u01/oradata/ORCL_NEW/ORCL_NEW.control.01.ctl',
'/u02/oradata/ORCL_NEW/ORCL_NEW.control.02.ctl',
'/u03/oradata/ORCL_NEW/ORCL_NEW.control.03.ctl'
db_name=’ORCL_NEW’
log_archive_dest_1='LOCATION=/u07/oradata/ORCL_NEW'
undo_tablespace ='HRRBS'
log_archive_format='ORCL_NEW_%s_%t_%r.arc'
service_names='ORCL_NEW.world','ORCL_NEW'
08. Make a copy of create_controlfile_SRTR1.sql in /optware/oracle/work/UPGRADE_PROD to create_controlfile_ORCL_NEW.sql (RECOMMENDED)
$/optware/oracle/work/UPGRADE_PROD>cp create_controlfile_ORCL_NEW.sql create_controlfile_ORCL_NEW.sql
OR use the transport script to create a control file…
*************************
Modify "transportscript" script to reflect the converted data files location, intiORCL_NEW.ora location and the location for utlxxx scripts as per the ORACLE_HOME. Also make other any other necessary changes.
*********************
09. Modify the create_controlfile_GDPFPRG.sql to reflect the following:
ORCL_COPY to ORCL_NEW
Redo log locations as follows since I am using raw file systems here :
GROUP 1 (
'/dev/roraORCL_NEWr1_01',
'/dev/roraORCL_NEWr2_01'
) SIZE 495M,
GROUP 2 (
'/dev/roraORCL_NEWr1_02',
'/dev/roraORCL_NEWr2_02'
) SIZE 495M,
GROUP 3 (
'/dev/roraORCL_NEWr1_03',
'/dev/roraORCL_NEWr2_03'
) SIZE 495M,
GROUP 4 (
'/dev/roraORCL_NEWr1_04',
'/dev/roraORCL_NEWr2_04'
) SIZE 495M,
GROUP 5 (
'/dev/roraORCL_NEWr1_05',
'/dev/roraORCL_NEWr2_05'
) SIZE 495M
Modify the datafile locations as below
'/aix_conv/ORCL_COPY/u01' to '/u01/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u02' to '/u02/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u03' to '/u03/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u04' to '/u04/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u05' to '/u05/oradata/ORCL_NEW',
'/aix_conv/ORCL_COPY/u06' to '/u06/oradata/ORCL_NEW';
10. Open another session and monitor alert log for ORCL_NEW in /u01/oradump/ORCL_NEW/bdump
11. cd to /optware/oracle/work/UPGRADE_PROD directory Run the create_controfile_ORCL_NEW.sql script in your work directory
Ex: $SQLPLUS
SQL> startup nomount
SQL>@/optware/oracle/work/UPGRADE_PROD/create_controlfile_ORCL_NEW.sql
SQL> alter database open resetlogs;
12. Add temp file to PSTEMP tablespace
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ORCL_NEW/ORCL_NEW.temp.10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;
13. Shutdown and startup just to see if any errors in the alert log. Closely monitor the alert log during this
SQL> shutdown immediate
SQL> startup
14. Run utlirp.sql by shutting down and startup in upgrade mode.
SQL> shutdown immediate
SQL> startup upgrade;
SQL> @?/rdbms/admin/utlirp.sql
15. Run utlrp.sql after shutting down the database
SQL> shutdown immediate
SQL> startup
SQL> @ ?/rdbms/admin/utlrp.sql
16. Drop the OLD temporary tablespace TEMP
SQL> drop tablespace TEMP including datafiles and contents cascade constraints;
SQL> drop tablespace pstemp including contents and datafiles;
17. Create temporary tablespace NEW_TEMP
SQL> CREATE TEMPORARY TABLESPACE "NEW_TEMP" TEMPFILE '/dev/roraORCL_NEWt_001' SIZE 3990M REUSE ;
18. Go to /optware/oracle/work/UPGRADE_PROD and run add_temp_files_ORCL_NEW.sql to add rest of the temp files as needed
SQL> spool add_temp_files_ORCL_NEW.log
SQL> add_temp_files_ORCL_NEW.sql
20. Create block changing data file to enable incremental backups of the database
SQL> alter database enable block change tracking using file '/u13/oradata/ORCL_NEW/ORCL_NEW.bct.01.dbf' reuse;
21. Update the PS OWNER table if you database is a PeopleSoft Database in my case it is.
SQL> select * from psdbowner;
DBNAME OWNERID
-------- --------
ORCL SYSADM
SQL> update psdbowner set dbname='ORCL_NEW';
Sql> commit;
SQL> select * from psdbowner;
DBNAME OWNERID
-------- --------
ORCL_NEW SYSADM
24. Configure and bring up the listener.
No comments:
Post a Comment