Recently took a Oracle 9i upgrade to 10 g RAC test, following brief records:
1.View my current database version
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/honcho/system01.dbf
2 /u01/app/oracle/oradata/honcho/undotbs01.dbf
3 /u01/app/oracle/oradata/honcho/hongzx01.dbf
4 /u01/app/oracle/oradata/honcho/drsys01.dbf
5 /u01/app/oracle/oradata/honcho/example01.dbf
9 /u01/app/oracle/oradata/honcho/users01.dbf
10 /u01/app/oracle/oradata/honcho/xdb01.dbf
FILE# NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/honcho/temp01.dbf
2.Full backup database
RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup full database format='/u01/backup/honcho_full_%U_%T' tag='honcho_full';
5> sql 'alter system archive log current';
6> backup archivelog all tag='arc_bak' format='/u01/backup/arch_%U_%T' delete input;
7> backup current controlfile tag='bak_ctlfile' format='/u01/backup/ctl_file_%U_%T';
8> backup spfile tag='spfile' format='/u01/backup/honcho_spfile_%U_%T';
9> release channel c1;
10> }
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1 B F A DISK 08-NOV-11 1 1 honcho_FULL
2 B F A DISK 08-NOV-11 1 1
3 B A A DISK 08-NOV-11 1 1 ARC_BAK
4 B F A DISK 08-NOV-11 1 1 BAK_CTLFILE
5 B F A DISK 08-NOV-11 1 1 SPFILE
6 B F A DISK 08-NOV-11 1 1
3.Installation 10g Clusterware and ASM instance
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
3.Copy 9.2.0.4 pfile to 192.168.1.131 corresponding directory and create corresponding directory at two nodes.
[oracle@rac1 dbs]$ mkdir -p /u01/app/oracle/admin/honcho/bdump
[oracle@rac1 dbs]$ mkdir -p /u01/app/oracle/admin/honcho/cdump
[oracle@rac1 dbs]$ mkdir -p /u01/app/oracle/admin/honcho/udump
4.Modification of the parameters are as follows:
[oracle@rac1 dbs]$ cat inithoncho1.ora
*.aq_tm_processes=0
*.background_dump_dest='/u01/app/oracle/admin/honcho/bdump'
*.compatible='10.2.0.1.0'
*.core_dump_dest='/u01/app/oracle/admin/honcho/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='honcho'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=honchoXDB)'
*.fast_start_mttr_target=300
*.instance_name='honcho'
*.java_pool_size=83886080
*.job_queue_processes=0
*.large_pool_size=16777216
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.user_dump_dest='/u01/app/oracle/admin/honcho/udump'
*.cluster_database_instances=2
*.cluster_database=true
*.control_files='+DATA/honcho/controlfile/control01.ctl','+RECOVERY/honcho/controlfile/control02.ctl'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+RECOVERY'
*.db_recovery_file_dest_size=2147483648
honcho1.instance_name='honcho1'
honcho2.instance_name='honcho2'
honcho1.instance_number=1
honcho2.instance_number=2
*.log_archive_dest_1='LOCATION=+RECOVERY/honcho/archivelog'
honcho1.thread=1
honcho2.thread=2
honcho1.undo_tablespace='UNDOTBS1'
honcho2.undo_tablespace='UNDOTBS2'
5.Create spfile from pfile.
[oracle@rac1 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 10:52:17 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create spfile='+DATA/honcho/PARAMETERFILE/spfilehoncho.ora' from pfile='/u01/app/oracle/product/10.0.2.1/db_1/dbs/inithoncho1.ora';
File created.
6.In all nodes, modify pfile , and it will point to the spfile at share equipment
[oracle@rac1 dbs]$ echo "SPFILE='+DATA/honcho/PARAMETERFILE/spfilehoncho.ora'" > /u01/app/oracle/product/10.0.2.1/db_1/dbs/inithoncho1.ora
[oracle@rac2 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac2 dbs]$ touch inithoncho2.ora
[oracle@rac2 dbs]$ echo "SPFILE='+DATA/honcho/PARAMETERFILE/spfilehoncho.ora'" > /u01/app/oracle/product/10.0.2.1/db_1/dbs/inithoncho2.ora
7.In all nodes, create password file.
[oracle@rac1 dbs]$ orapwd file=orapwhoncho1 password=oracle entries=20
[oracle@rac2 dbs]$ orapwd file=orapwhoncho2 password=oracle entries=20
8.Start database to nomount status:
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1218628 bytes
Variable Size 184551356 bytes
Database Buffers 33554432 bytes
Redo Buffers 2973696 bytes
9. Recvery controlfile from backupset.
RMAN> restore controlfile from '/u01/backup/ctl_file_04mr66b3_1_1_20111108';
Starting restore at 09-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 instance=honcho1 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+DATA/honcho/controlfile/control01.ctl
output filename=+RECOVERY/honcho/controlfile/control02.ctl
Finished restore at 09-NOV-11
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
10.restore database
RMAN> run {
2> set newname for datafile 1 to '+DATA/honcho/DATAFILE/system01.dbf';
3> set newname for datafile 2 to '+DATA/honcho/DATAFILE/undotbs01.dbf';
4> set newname for datafile 3 to '+DATA/honcho/DATAFILE/hongzx01.dbf';
5> set newname for datafile 4 to '+DATA/honcho/DATAFILE/drsys01.dbf';
6> set newname for datafile 5 to '+DATA/honcho/DATAFILE/example01.dbf';
7> set newname for datafile 9 to '+DATA/honcho/DATAFILE/users01.dbf';
8> set newname for datafile 10 to '+DATA/honcho/DATAFILE/xdb01.dbf';
9> restore database;
10> switch datafile all;
11> switch tempfile all;
12> }
There is no restore tempfile
11.recover database
RMAN> recover database;
Starting recover at 09-NOV-11
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /u01/backup/arch_03mr66ar_1_1_20111108
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/arch_03mr66ar_1_1_20111108 tag=ARC_BAK
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=+RECOVERY/honcho/archivelog/1_5_766710700.dbf thread=1 sequence=5
archive log filename=+RECOVERY/honcho/archivelog/1_6_766710700.dbf thread=1 sequence=6
unable to find archive log
archive log thread=1 sequence=7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/09/2011 13:39:31
RMAN-06054: media recovery requesting unknown log: thread 1 seq 7 lowscn 196183
12.Modify online logfile to ASM file.
SQL> alter database rename file '/u01/app/oracle/oradata/honcho/redo03.log' to '+DATA/honcho/ONLINELOG/redo03.log';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/honcho/redo02.log' to '+DATA/honcho/ONLINELOG/redo02.log';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/honcho/redo01.log' to '+DATA/honcho/ONLINELOG/redo01.log';
Database altered.
13.Open the database for resetlogs.
alter database open resetlogs;
14.Upgrade database to 10g
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1218628 bytes
Variable Size 184551356 bytes
Database Buffers 33554432 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
View the alert log
Errors in file /u01/app/oracle/admin/honcho/udump/honcho1_ora_16126.trc:
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Error 39701 happened during db open, shutting down database
USER: terminating instance due to error 39701
Solution:
[oracle@rac1]$ cd $ORACLE_HOME/nls/data/old
[oracle@rac1 old]$ perl cr9idata.pl
Creating directory /u01/app/oracle/product/10.0.2.1/db_1/nls/data/9idata ...
Copying files to /u01/app/oracle/product/10.0.2.1/db_1/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/app/oracle/product/10.0.2.1/db_1/nls/data/9idata!
Viet the environment variables,add:
export ORA_NLS10=/u01/app/oracle/product/10.0.2.1/db_1/nls/data/9idata
Remove the parameter related with cluster.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1218628 bytes
Variable Size 184551356 bytes
Database Buffers 33554432 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
15.Add tempfile to temporary tablespace.
SQL> alter tablespace temp add tempfile '+DATA' size 100m;
16.Because there was no sysaux tablespace in 9i,add sysaux tablespace :
SQL> create tablespace sysaux datafile '+DATA' size 500m reuse
2 extent management local
3 segment space management auto;
Tablespace created.
17.Running the upgrade script.
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/catclust.sql
@?/rdbms/admin/utlrp.sql
18.Add some of the parameters related with the RAC:
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> alter system set cluster_database_instances=2 scope=spfile;
System altered.
SQL> alter system set instance_number=1 scope=spfile sid='honcho1';
System altered.
SQL> alter system set instance_number=2 scope=spfile sid='honcho2';
System altered.
SQL> alter system set thread=1 scope=spfile sid='honcho1';
System altered.
SQL> alter system set thread=2 scope=spfile sid='honcho2';
System altered.
19.Add undo tablespace and redologfile for node 2.
SQL> create undo tablespace UNDOTBS2 datafile '+DATA/honcho/datafile/undotbs02.dbf' size 50m;
Tablespace created.
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='honcho2';
System altered.
SQL> alter database add logfile thread 2 group 4 '+DATA' size 100m;
Database altered.
SQL> alter database add logfile thread 2 group 5 '+DATA' size 100m;
Database altered.
SQL> alter database add logfile thread 2 group 6 '+DATA' size 100m;
Database altered.
SQL> alter database enable thread 2;
Database altered.
20.Restart database at all nodes.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1218628 bytes
Variable Size 184551356 bytes
Database Buffers 33554432 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
[oracle@rac2 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 9 18:24:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1218628 bytes
Variable Size 184551356 bytes
Database Buffers 33554432 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select instance_number,instance_name,host_name from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ------------------------------
1 honcho1 rac1
2 honcho2 rac2
2 rows selected.
21.Configuration listener.
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
Use netca to configuration:
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
22.Add RAC service.
[oracle@rac1 ~]$ srvctl add database -d honcho -o $ORACLE_HOME -p +DATA/honcho/PARAMETERFILE/spfilehoncho.ora
[oracle@rac1 ~]$ srvctl add instance -d honcho -i honcho1 -n rac1
[oracle@rac1 ~]$ srvctl add instance -d honcho -i honcho2 -n rac2
[oracle@rac1 ~]$ srvctl modify instance -d honcho -i honcho1 -s +ASM1
[oracle@rac1 ~]$ srvctl modify instance -d honcho -i honcho2 -s +ASM2
[oracle@rac1 ~]$ srvctl start database -d honcho
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.honcho.db application ONLINE ONLINE rac1
ora....b1.inst application ONLINE ONLINE rac1
ora....b2.inst application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Adjust some parameters ,OK. Game over!