Check Pre-Conditions on database SRTR1
Check list of platforms that we can convert source database into as below.
SQL> SELECT * FROM v$db_transportable_platform;
Shutdown and start newly created SRTR1 database in "READ ONLY" mode.
SQL> shutdown immediate;
Comment out below parameters:
*.audit_trail ='DB'
*.audit_file_dest='/backup/SRTR1/oradata'
SQL> startup mount;
SQL> alter database open read only;
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
SRTR1 READ ONLY
Transfer SRTR1 files from Solaris to AIX
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. Copy the control file trace to AIX_Server:/backup.
Copy the init.ora file of SRTR1 to AIX_Server:/backup
$ scp initSRTR1.ora AIX_Server:/backup/SRTR1
Go to /optware/oracle/oradata on Solaris_Server and run the below command to get a count of the datafiles of SRTR1
Sql> spool SRTR1_data_file_count.log
Sql> select count(*) from dba_data_files;
Sql> spool off
Shutdown the database SRTR1 on Solaris_Server.
Sql > select instance_name from v$instance;
SRTR1
Sql> shutdown immediate;
Change back the init parameters and copy the init.ora file of SRTR1 to AIX_Server:/backup
$ cd /optware/oracle/SRTR1
Delete below entries:
*.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
$ cd /optware/oracle/SRTR1
$ scp initSRTR1.ora AIX_Server:/backup/SRTR1
Comment out SRTR1 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:
SRTR1:/optware/oracle/10.2.0.4:Y
Transfer the files from Solaris_Server to AIX_Server
Make directories with 50Gb each in size within the filesystems to have the datafiles to be copied in multiple streams. Below are the directories to be created:
/u11=171GB db=57.3 GB db1=57.9 GB db2=56.3 GB
$ cd /backup/SRTR1/u11
$ mkdir 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 /backup/SRTR1/u11/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &
cd /backup/SRTR1/u11/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &
cd /backup/SRTR1/u11/db2
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &
/u13=169.8 GB db=57.3 GB db1= 56.6 GB db2= 55.8 GB
$ cd /backup/SRTR1/u13
$ 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/SRTR1/u13/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &
cd /backup/SRTR1/u13/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &
cd /backup/SRTR1/u13/db2
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &
/u02=4.6 GB
cd /backup/SRTR1/u02
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u02 ;zcat | tar xvf -" &
/u09=4.2 GB
cd /u11/GDPFCNV1/u09
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u09 ;zcat | tar xvf -" &
Stop here, once /u02 and /u09 finish, start /12 and /14
/u12=106.9 GB db=52.8 GB db1=54.1 GB
$ cd /backup/SRTR1/u12
$ mkdir db db1
$ mv *psindex.0*dbf db; mv *psindex.11.dbf db; mv *psindex.13.dbf db; mv *psindex.15.dbf db; mv *psindex.17.dbf db
$ mv *.dbf db1
cd /backup/SRTR1/u12/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u12 ;zcat | tar xvf -" &
cd /backup/SRTR1/u12/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u12 ;zcat | tar xvf -" &
/u14=92.69 GB db=48.12 GB db1=44.56 GB
$ cd /u11/SRTR1/u14
$ mkdir db db1
$ mv *.0* db; mv *.1* db
$ mv *.dbf db1
cd /u11/SRTR1/u14/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u14 ;zcat | tar xvf -" &
cd /u11/SRTR1/u14/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u14 ;zcat | tar xvf -" &
Also make sure that you have copied the correct control file trace generated and init.ora files to /backup on AIX_Server
Locations of the files on Solaris_Server:
a. trace file location: /backup/SRTR1/oradump
b. init.ora file location: /optware/oracle/SRTR1
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 PHPRDH and SRTR1. Run PHPRDH_datafile_count.ksh and SRTR1_datafile_count.ksh
Ex- cat SRTR1_datafile_count.ksh on SOLARIS
ls -ltr /backup/SRTR1/d11/*.dbf | wc -l
ls -ltr /backup/SRTR1/d13/*.dbf | wc -l
ls -ltr /backup/SRTR1/d12/*.dbf | wc -l
ls -ltr /u11/SRTR1/d14/*.dbf | wc -l
ls -ltr /backup/SRTR1/d02/*.dbf | wc -l
ls -ltr /u11/SRTR1/d09/*.dbf | wc –l
cat SRTR1_datafile_count.ksh on AIX
ls -ltr /backup/SRTR1/d11/*.dbf | wc -l
ls -ltr /backup/SRTR1/d13/*.dbf | wc -l
ls -ltr /backup/SRTR1/d12/*.dbf | wc -l
ls -ltr /backup/SRTR1/d14/*.dbf | wc -l
ls -ltr /backup/SRTR1/d02/*.dbf | wc -l
ls -ltr /backup/SRTR1/d09/*.dbf | wc –l
Run ps –ef | grep SRTR1 on Solaris_Server which will show copy processes since we run them in background.
Conversion
Bring up the database SRTR1and Convert the datafiles of SRTR1on the target server (AIX_Server)
Note: Create any missing directories
1. Enable the entry for SRTR1in /etc/oratab for the 10.2.0.3_Q108 ORACLE_HOME(already there)
# SRTR1:/optware/oracle/10.2.0.3_Q108:Y
2. Copy the init.ora file from /backup to/ dbms/oracle/local/SRTR1/etc
Cp initSRTR1.ora /dbms/oracle/local/SRTR1/etc
3. Go to /dbms/oracle/local/SRTR1/etc and
Edit the following in init.ora for SRTR1located in /dbms/oracle/local/SRTR1/etc to reflect the correct file systems.
AUDIT_FILE_DEST ='/backup/ SRTR1/oradump /adump'
background_dump_dest='/backup/SRTR1/oradump/bdump'
core_dump_dest='/backup/ SRTR1/oradump /cdump'
user_dump_dest='/backup/ SRTR1/oradump /udump'
control_files='/backup/SRTR1/u11/SRTR1.control.01.ctl',
'/backup/SRTR1/u13/SRTR1.control.02.ctl',
'/backup/SRTR1/u13/SRTR1.control.03.ctl'
db_name=’SRTR1’
log_archive_dest_1='LOCATION=/backup/SRTR1/archive'
log_archive_format='SRTR1_%s_%t_%r.arc'
service_names=' SRTR1.world',' SRTR1'
4. Run the env file in /dbms/oracle/local/SRTR1/etc to set up environment for SRTR1
oracle@erpstedb202p[GDPFPRG] /dbms/oracle/local/SRTR1/etc > . SRTR1.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/SRTR1/etc/initSRTR1.ora initSRTR1.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 SRTR1_ora_22678.trc /optware/oracle/work/UPGRADE_PROD
$ cd /optware/oracle/work/UPGRADE_PROD
$ mv xxxxxx.trc create_controlfile_SRTR1.sql
8. Modify the create_controlfile_SRTR1.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. /u11/SRTR1/u14/*.dbf to /backup/SRTR1/u14/*.dbf
c. /u11/SRTR1/u09/*.dbf to /backup/GDPFDNV1/u09/*.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_SRTR1.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 PSTEMP ADD TEMPFILE '/backup/SRTR1/SRTR1.pstemp.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;
Open the database SRTR1in 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
--------- ----------
SRTR1 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 /backup/SRTR1/dXX to AIX format and places them under /dXX/oradata/GDPFPRG/. It also creates transport script in /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript. It took 4.5 hours on PHUATH
RMAN> CONVERT DATABASE NEW DATABASE 'GDPFPRG'
transport script '/optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript'
to platform 'AIX-Based Systems (64-bit)'
db_file_name_convert
'/backup/SRTR1/u11' '/u11/oradata/GDPFPRG',
'/backup/SRTR1/u13' '/u13/oradata/GDPFPRG',
'/backup/SRTR1/u12' '/u12/oradata/GDPFPRG',
'/backup/SRTR1/u14' '/u14/oradata/GDPFPRG',
'/backup/SRTR1/u02' '/u02/oradata/GDPFPRG',
'/backup/SRTR1/u09' '/u09/oradata/GDPFPRG' ;
Note: After successful conversion of the data files success messages are prompted and the converted data files are created under /oradata/GDPFPRG/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=/backup/SRTR1/u13/SRTR1.ptrpts.01.dbf
converted datafile=/u13/oradata/GDPFPRG/SRTR1.ptrpts.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=/backup/SRTR1/u11/SRTR1.rwlarge.01.dbf
converted datafile=/u11/oradata/GDPFPRG/SRTR1.rwlarge.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=/backup/SRTR1/u13/SRTR1.scapp.01.dbf
converted datafile=/u13/oradata/GDPFPRG/SRTR1.scapp.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=/backup/SRTR1/u11/SRTR1.bplarge.01.dbf
converted datafile=/u11/oradata/GDPFPRG/SRTR1.bplarge.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
Task6. Bring up database SRTR1
01. A new control file creation script “transportscript” is created under /optware/oracle/10.2.0.3_Q108/dbs/convertdb
02. Rename the datafiles from SRTR1to SRTR1by executing the script rename_files_from_SRTR1_to_gdpfprg.ksh located in /optware/oracle/work/UPGRADE_PROD
03. Point the Oracle Home of SRTR1to 10.2.0.3_Q108 in /etc/oratab like below.
GDPFPRG:/optware/oracle/10.2.0.3_Q108_dbv:Y
04. Run the env file located in /dbms/oracle/local/GDPFPRG/etc.
$ /dbms/oracle/local/GDPFPRG/etc > . GDPFPRG.env
05. Make sure the environment is set correctly for GDPFPRG. Use below
echo $ORACLE_SID
echo $ORACLE_HOME
echo $PATH
06. Go to $ORACLE_HOME/dbs and create a soft link for initGDPFPRG.ora in $ORACLE_HOME/dbs to point to /dbms/oracle/local/GDPFPRG/etc. Use below
ln -s /dbms/oracle/local/GDPFPRG/etc/initGDPFPRG.ora initGDPFPRG.ora
07. Make sure the initSRTR1to reflect
AUDIT_FILE_DEST =' /u01/oradump/GDPFPRG/adump'
background_dump_dest=' /u01/oradump/GDPFPRG/bdump'
core_dump_dest=' /u01/oradump/GDPFPRG/cdump'
user_dump_dest=' /u01/oradump/GDPFPRG/udump'
control_files ='/u11/oradata/GDPFPRG/gdpfprg.control.01.ctl'
,'/u13/oradata/GDPFPRG/gdpfprg.control.02.ctl'
,'/u13/oradata/GDPFPRG/gdpfprg.control.03.ctl'
db_name=’GDPFPRG’
log_archive_dest_1='LOCATION=/u07/oradata/GDPFPRG'
undo_tablespace ='PSRBS1'
log_archive_format='GDPFPRG_%s_%t_%r.arc'
service_names=' GDPFPRG.world',' GDPFPRG'
08. Make a copy of create_controlfile_SRTR1.sql in /optware/oracle/work/UPGRADE_PROD to create_controlfile_GDPFPRG.sql (RECOMMENDED)
$/optware/oracle/work/UPGRADE_PROD>cp create_controlfile_SRTR1.sql create_controlfile_GDPFPRG.sql
OR use the transport script to create a control file…
*************************
Modify "transportscript" script to reflect the converted data files location, intiPHUATH.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:
SRTR1 to GDPFPRG
Redo log locations as follows:
GROUP 1 (
'/dev/roraGDPFPRGr1_01',
'/dev/roraGDPFPRGr2_01'
) SIZE 495M,
GROUP 2 (
'/dev/roraGDPFPRGr1_02',
'/dev/roraGDPFPRGr2_02'
) SIZE 495M,
GROUP 3 (
'/dev/roraGDPFPRGr1_03',
'/dev/roraGDPFPRGr2_03'
) SIZE 495M,
GROUP 4 (
'/dev/roraGDPFPRGr1_04',
'/dev/roraGDPFPRGr2_04'
) SIZE 495M,
GROUP 5 (
'/dev/roraGDPFPRGr1_05',
'/dev/roraGDPFPRGr2_05'
) SIZE 495M
Modify the datafile locations as below
'/backup/SRTR1/u11' to '/u11/oradata/GDPFPRG',
'/backup/SRTR1/u13' to '/u13/oradata/GDPFPRG',
'/backup/SRTR1/u12' to '/u12/oradata/GDPFPRG',
'/backup/SRTR1/u14' to '/u14/oradata/GDPFPRG',
'/backup/SRTR1/u02' to '/u02/oradata/GDPFPRG',
'/backup/SRTR1/u09' to '/u09/oradata/GDPFPRG';
10. Open another session and monitor alert log for SRTR1in /u01/oradump/GDPFPRG/bdump
11. cd to /optware/oracle/work/UPGRADE_PROD directory Run the create_controfile_GDPFPRG.sql script in your work directory
Ex: $SQLPLUS
Sql> startup nomount
SQL>@/optware/oracle/work/UPGRADE_PROD/ create_controlfile_GDPFPRG.sql
SQL> alter database open resetlogs;
12. Add temp file to PSTEMP tablespace
Sql>ALTER TABLESPACE PSTEMP ADD TEMPFILE '/u11/oradata/GDPFPRG/gdpfprg.pstemp.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. Go to /optware/oracle/work/UPGRADE_PROD and run add_undo_files_GDPFPRG.sql
Sql> spool add_undo_files_GDPFPRG.log
Sql> @add_undo_files_GDPFPRG.sql
17. Drop the temporary tablespace PSTEMP
Sql> drop tablespace PSTEMP including datafiles and contents cascade constraints;
drop tablespace pstemp including contents and datafiles;
18. Create temporary tablespace PSTEMP
Sql> CREATE TEMPORARY TABLESPACE "PSTEMP" TEMPFILE '/dev/roraGDPFPRGt_001' SIZE 3990M REUSE ;
19. Go to /optware/oracle/work/UPGRADE_PROD and run add_temp_files_GDPFPRG.sql to add temp files
Sql > spool add_temp_files_GDPFPRG.log
Sql> add_temp_files_GDPFPRG.sql
20. Create block changing data file
Sql> alter database enable block change tracking using file '/u13/oradata/GDPFPRG/GDPFPRG.bct.01.dbf' reuse;
21. Update the PS OWNER
SQL> select * from psdbowner;
DBNAME OWNERID
-------- --------
PHPRDH SYSADM
SQL> update psdbowner set dbname='GDPFPRG';
Sql> commit;
SQL> select * from psdbowner;
DBNAME OWNERID
-------- --------
SRTR1 SYSADM
24. Make the sysaux tablespace autoextend
Make sure that the database SRTR1is up and running.
25. Bring up the listener.
26. Shutdown the SRTR1database
SQL> select instance_name from v$instance ;
SRTR1
SQL> shutdown immediate
Showing posts with label Database Migration. Show all posts
Showing posts with label Database Migration. Show all posts
Monday, March 1, 2010
Subscribe to:
Posts (Atom)