A-A+

solaris下oracle 9.2.0.4 rac升级到11.2.0.4 rac的操作手记

2016年08月04日 DG&RAC&OGG 暂无评论 阅读 907 次

以前的一个核心数据库RAC跨版本升级的操作文档,这份文档有的厂商报价到了10万+,今天花时间整理出来放给大家,以飨众人,算是近两年来没有更新技博的自我检讨吧!

迁移升级背景:
机器型号:v890 sparcx64 -> T5-2 sparcx64
集群件:sun cluster -> oracle cluster
操作系统:solaris
数据库:9.2.0.4 双节点 RAC -> 11.2.0.4 双节点 RAC

迁移难点:
1、集群件更换
2、数据库夸版本升级
3、数据量较大,停机时间短(约一个多小时)

官方支持的升级路劲图
upgradeto11gpath

前期工作
1、在2个新机器安装好11.2.0.4 crs,asm,db software
2、在新机器节点1安装好10.2.0.5的db software
3、提前准备好9.2.0.4原始库的全量备份文件

#######以下所有操作均在新的2台数据库T5-2机器上执行#######

*****************************************************************
第一步:用节点1的10.2.0.5的db software 恢复数据到11.2.0.4 的asm
*****************************************************************
节点1创建相关目录

bash-3.2$ id
uid=100(grid) gid=100(oinstall)
ASMCMD> pwd
+data/xxdb
ASMCMD> mkdir datafile
ASMCMD> mkdir archivelog
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE
ASMCMD> mkdir TEMPFILE
ASMCMD> pwd
+fra
ASMCMD> mkdir xxdb
ASMCMD> cd xxdb
ASMCMD> mkdir ONLINELOG

节点1使用如下参数文件启动数据库实例

bash-3.2$ cat pfile_10g 
*.__db_cache_size=1174405120
*.__java_pool_size=16777216
*.__large_pool_size=16777216
*.__shared_pool_size=352321536
*.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/xxdb/adump'
*.cluster_database_instances=2
*.cluster_database=false
*.db_block_size=16384
*.compatible='10.2.0.5.0'
*.control_files='+DATA/xxdb/controlfile/control01.ctl','+DATA/xxdb/controlfile/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/xxdb/cdump'
xxdb1.instance_name='xxdb1'
xxdb2.instance_name='xxdb2'
xxdb2.instance_number=2
xxdb1.instance_number=1
*.log_archive_dest='+data/xxdb/archivelog'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='kingxxdb'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=5000
*.pga_aggregate_target=5368709120
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1335
*.sga_target=10739500056
xxdb1.thread=1
xxdb2.thread=2
*.undo_management='AUTO'
*.undo_retention=10800
xxdb2.undo_tablespace='UNDOTBS2'
xxdb1.undo_tablespace='UNDOTBS1'

节点1启动数据库实例到nomount
恢复控制文件

bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/10.2.5/db_1
bash-3.2$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
bash-3.2$ cd /u01/app/oracle/product/10.2.5/db_1/bin
bash-3.2$ ./sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on 星期四 9月 25 09:23:50 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

已连接到空闲例程。

SQL> startup nomount pfile='/home/oracle/pfile_10g';
ORACLE 例程已经启动。

Total System Global Area 1.0754E+10 bytes
Fixed Size                  2069696 bytes
Variable Size            1543506752 bytes
Database Buffers         9059696640 bytes
Redo Buffers              148922368 bytes
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
bash-3.2$ ./rman target /

恢复管理器: Release 10.2.0.5.0 - Production on 星期四 9月 25 09:24:26 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到目标数据库: kingxxdb (未装载)

RMAN> restore controlfile from '/datamount/app/oracle/rman/20141011/ctl_file_4mpkoguc_1_1_20141011';

启动 restore 于 25-9月 -14
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=1076 devtype=DISK

通道 ORA_DISK_1: 正在还原控制文件
通道 ORA_DISK_1: 还原完成, 用时: 00:00:07
输出文件名=+DATA/xxdb/controlfile/control01.ctl
输出文件名=+FRA/xxdb/controlfile/control02.ctl
完成 restore 于 25-9月 -14


在节点1将原库的全量备份文件注册到控制文件中
RMAN> startup mount

数据库已经启动
数据库已装载
释放的通道: ORA_DISK_1

RMAN> catalog start with '/datamount/app/oracle/rman';


继续恢复数据文件到asm

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
set newname for datafile 30 to '+data/xxdb/datafile/aaa15.dbf';
set newname for datafile 19 to '+data/xxdb/datafile/aaa08.dbf';
set newname for datafile 06 to '+data/xxdb/datafile/aaa01.dbf';
set newname for datafile 02 to '+data/xxdb/datafile/system02.dbf';
set newname for datafile 05 to '+data/xxdb/datafile/indx02.dbf';
set newname for datafile 33 to '+data/xxdb/datafile/aaa17.dbf';
set newname for datafile 14 to '+data/xxdb/datafile/aaa04.dbf';
set newname for datafile 15 to '+data/xxdb/datafile/aaa05.dbf';
set newname for datafile 03 to '+data/xxdb/datafile/undotbs01.dbf';
set newname for datafile 09 to '+data/xxdb/datafile/tools01.dbf';
set newname for datafile 28 to '+data/xxdb/datafile/aaa13.dbf';
set newname for datafile 01 to '+data/xxdb/datafile/system01.dbf';
set newname for datafile 04 to '+data/xxdb/datafile/indx01.dbf';
set newname for datafile 10 to '+data/xxdb/datafile/tools02.dbf';
set newname for datafile 18 to '+data/xxdb/datafile/perfstat.dbf';
set newname for datafile 32 to '+data/xxdb/datafile/aaa16.dbf';
set newname for datafile 31 to '+data/xxdb/datafile/indx04.dbf';
set newname for datafile 07 to '+data/xxdb/datafile/aaa02.dbf';
set newname for datafile 11 to '+data/xxdb/datafile/undotbs02.dbf';
set newname for datafile 21 to '+data/xxdb/datafile/perfstat01.dbf';
set newname for datafile 16 to '+data/xxdb/datafile/aaa06.dbf';
set newname for datafile 17 to '+data/xxdb/datafile/aaa07.dbf';
set newname for datafile 22 to '+data/xxdb/datafile/aaa10.dbf';
set newname for datafile 08 to '+data/xxdb/datafile/aaa03.dbf';
set newname for datafile 12 to '+data/xxdb/datafile/users01.dbf';
set newname for datafile 29 to '+data/xxdb/datafile/aaa14.dbf';
set newname for datafile 25 to '+data/xxdb/datafile/aaa11.dbf';
set newname for datafile 26 to '+data/xxdb/datafile/perfstat03.dbf';
set newname for datafile 24 to '+data/xxdb/datafile/perfstat02.dbf';
set newname for datafile 27 to '+data/xxdb/datafile/aaa12.dbf';
set newname for datafile 20 to '+data/xxdb/datafile/aaa09.dbf';
set newname for datafile 23 to '+data/xxdb/datafile/indx03.dbf';
set newname for datafile 13 to '+data/xxdb/datafile/users02.dbf';
restore database;
recover database;
switch datafile all;
release channel c1;
release channel c2;	 
release channel c3;
release channel c4;		
}

在原始库上插入测试数据

SQL> drop table test_20141017;

Table dropped.

SQL> create table test_20141017 (id number,my_date date);

Table created.

SQL> insert into test_20141017 values (1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select id,to_char(my_date,'yyyy/mm/dd hh24:mi:ss') from test_20141017;

        ID TO_CHAR(MY_DATE,'YY
---------- -------------------
         1 2014/10/18 00:27:28

可以使用以下脚本统计指定用户下所有表的条目数及总条目数

set serverout on
declare 
v_sum_num number :=0;
v_num number :=0;
v_table_name varchar2(100);
str_sql varchar2(500);
cursor tab_name is select table_name from dba_tables where owner= upper('&user_name') order by 1;
begin
	dbms_output.put_line('---------TABLE SUM------------');
	for i in tab_name
		loop
			v_table_name:=i.table_name;
			execute immediate 'select count(*) from ' || v_table_name into v_num;
			dbms_output.put_line(v_table_name||':'||chr(9)||v_num);
			v_sum_num:=v_num+v_sum_num;
		end loop;
	dbms_output.put_line('------------------------------');
	dbms_output.put_line('SUM_NUM'||':'||chr(9)||v_sum_num);
end;
/	

开始停止业务并拷贝原始数据库当天所有节点的所有归档到新库并注册
在原始数据库各节点切换日志

alter system checkpoint;
alter system switch logfile;
alter system archive log current;

root@xxdb1 # scp 10.2.2.23:/oradata1/rman/arch1/* /oradata1/rman/arch1/
root@xxdb1 # scp 10.2.2.26:/oradata1/rman/arch2/* /oradata1/rman/arch1/
root@xxdb1 # chown -R oracle:oinstall /oradata1/rman/arch1

在新的节点1注册相关归档并恢复

bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/10.2.5/db_1
bash-3.2$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
bash-3.2$ cd /u01/app/oracle/product/10.2.5/db_1/bin
bash-3.2$ ./rman target /

RMAN> catalog start with '/oradata1/rman/arch1';

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database;
release channel c1;
release channel c2;	 
release channel c3;
release channel c4;		
}

在新节点1更改redo路径

bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/10.2.5/db_1
bash-3.2$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
bash-3.2$ cd /u01/app/oracle/product/10.2.5/db_1/bin/
bash-3.2$ ./sqlplus "/as sysdba"

alter database rename file '/opt/oracle/oradata/king/redo01.log' to '+DATA/xxdb/ONLINELOG/redo01.log';
alter database rename file '/opt/oracle/oradata/king/redo02.log' to '+DATA/xxdb/ONLINELOG/redo02.log';
alter database rename file '/opt/oracle/oradata/king/redo03.log' to '+DATA/xxdb/ONLINELOG/redo03.log';
alter database rename file '/opt/oracle/oradata/king/redo04.log' to '+DATA/xxdb/ONLINELOG/redo04.log';
alter database rename file '/opt/oracle/oradata/king/redo05.log' to '+DATA/xxdb/ONLINELOG/redo05.log';
alter database rename file '/opt/oracle/oradata/king/redo06.log' to '+DATA/xxdb/ONLINELOG/redo06.log';
alter database rename file '/opt/oracle/oradata/king/redo07.log' to '+DATA/xxdb/ONLINELOG/redo07.log';
alter database rename file '/opt/oracle/oradata/king/redo08.log' to '+DATA/xxdb/ONLINELOG/redo08.log';
alter database rename file '/opt/oracle/oradata/king/redo09.log' to '+DATA/xxdb/ONLINELOG/redo09.log';
alter database rename file '/opt/oracle/oradata/king/redo10.log' to '+DATA/xxdb/ONLINELOG/redo10.log';
alter database rename file '/opt/oracle/oradata/king/redo11.log' to '+DATA/xxdb/ONLINELOG/redo11.log';
alter database rename file '/opt/oracle/oradata/king/redo12.log' to '+DATA/xxdb/ONLINELOG/redo12.log';

新的节点1继续修改相关参数

SQL> create spfile from pfile='/home/oracle/pfile_10g';

文件已创建。

SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount; 
ORACLE 例程已经启动。

Total System Global Area 1.0754E+10 bytes
Fixed Size                  2069696 bytes
Variable Size            1543506752 bytes
Database Buffers         9059696640 bytes
Redo Buffers              148922368 bytes
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='800M' SCOPE=spfile; 

系统已更改。

SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='512M' SCOPE=spfile; 

系统已更改。

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

*****************************************************************
第二步:用节点1的10.2.0.5的db software 升级数据字典到10.2.0.5
由于9i所使用到的数据库组件较少,故升级数据字典也就花了几分钟而已
*****************************************************************

bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/10.2.5/db_1
bash-3.2$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
bash-3.2$ pwd
/u01/app/oracle/product/10.2.0/db_1/bin
bash-3.2$ ./sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Sun May 25 21:23:03 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 1.0754E+10 bytes
Fixed Size                  2069888 bytes
Variable Size             855640704 bytes
Database Buffers         9848225792 bytes
Redo Buffers               48259072 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
bash-3.2$ ./sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Sun May 25 21:24:03 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup UPGRADE
ORACLE instance started.

Total System Global Area 1.0754E+10 bytes
Fixed Size                  2069888 bytes
Variable Size             889195136 bytes
Database Buffers         9814671360 bytes
Redo Buffers               48259072 bytes
Database mounted.
Database opened.
SQL> alter tablespace temp add tempfile '+DATA/xxdb/TEMPFILE/temp02.dbf' size 10240M;

Tablespace altered.

SQL> create tablespace SYSAUX datafile '+DATA/xxdb/datafile/sysaux01.dbf'
  2       size 2048M reuse
  3       extent management local
  4       segment space management auto
  5       online;

Tablespace created.

SQL> create pfile='/home/oracle/pfile_10g_new' from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup UPGRADE
ORACLE instance started.

Total System Global Area 1.0754E+10 bytes
Fixed Size                  2069888 bytes
Variable Size             889195136 bytes
Database Buffers         9814671360 bytes
Redo Buffers               48259072 bytes
Database mounted.
Database opened.


SQL> SPOOL '/home/oracle/10g_patch.log'
SQL> @/u01/app/oracle/product/10.2.5/db_1/rdbms/admin/catupgrd.sql

SPOOL OFF

SQL> set line 400
SQL> select comp_id,status,version from dba_registry order by 1;

COMP_ID                        STATUS                 VERSION
------------------------------ ---------------------- ------------------------------
CATALOG                        VALID                  10.2.0.5.0
CATPROC                        VALID                  10.2.0.5.0
OWM                            VALID                  10.2.0.5.0
RAC                            INVALID                10.2.0.5.0

已选择4行。

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.0754E+10 bytes
Fixed Size                  2069888 bytes
Variable Size             889195136 bytes
Database Buffers         9814671360 bytes
Redo Buffers               48259072 bytes
Database mounted.
Database opened.

*****************************************************************
第三步:用节点1的11.2.0.4的db software 升级数据字典到11.2.0.4
此处升级数据字典使用了40分钟左右,算是正常时长
*****************************************************************

bash-3.2$ ./sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Mon May 26 07:32:59 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 05-26-2014 07:33:26
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          kingxxdb
--> version:       10.2.0.5.0
--> compatible:    10.2.0.5.0
--> blocksize:     16384
--> platform:      Solaris[tm] OE (64-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 534 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 282 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> Real Application Clusters    [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 40 INVALID objects.
.... USER SYSTEM has 5 INVALID objects.
.... USER SYS has 179 INVALID objects.
.... USER CAUSER has 3 INVALID objects.
.... USER WMSYS has 1 INVALID objects.
.... USER PERFSTAT has 2 INVALID objects.
.... USER OCSPUSER has 2 INVALID objects.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
        PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "10" is too low.

.... Starting with Oracle Database 11g Release 2 (11.2), setting
.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still run. This parameter must be updated to
.... a value greater than 128  (default value is 1000) prior to upgrade.
.... Not doing so will affect the running of utlrp.sql after the upgrade
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

bash-3.2$ cat /home/oracle/pfile_11g 
*.__db_cache_size=1174405120
*.__java_pool_size=16777216
*.__large_pool_size=16777216
*.__shared_pool_size=352321536
*.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/xxdb/adump'
*.cluster_database_instances=2
*.cluster_database=false
*.compatible='10.2.0.5.0'
*.control_files='+DATA/xxdb/controlfile/control01.ctl','+DATA/xxdb/controlfile/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/xxdb/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='kingxxdb'
xxdb1.instance_name='xxdb1'
xxdb2.instance_name='xxdb2'
xxdb2.instance_number=2
xxdb1.instance_number=1
*.java_pool_size=536870912
*.job_queue_processes=10
*.log_archive_dest='+data/xxdb/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=5000
*.pga_aggregate_target=5368709120
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=10739500056
*.shared_pool_size=838860800
xxdb1.thread=1
xxdb2.thread=2
*.undo_management='AUTO'
*.undo_retention=10800
xxdb2.undo_tablespace='UNDOTBS2'
xxdb1.undo_tablespace='UNDOTBS1'


bash-3.2$ env|grep ORACLE
ORACLE_SID=xxdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 26 07:57:08 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/pfile_11g';
ORACLE instance started.

Total System Global Area 1.0726E+10 bytes
Fixed Size                  2191016 bytes
Variable Size            2046824792 bytes
Database Buffers         8657043456 bytes
Redo Buffers               20021248 bytes
SQL> create spfile from pfile='/home/oracle/pfile_11g';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1.0726E+10 bytes
Fixed Size                  2191016 bytes
Variable Size            1845498200 bytes
Database Buffers         8858370048 bytes
Redo Buffers               20021248 bytes
Database mounted.
Database opened.


SQL> spool '/home/oracle/11g_update.log';
SQL> @?/rdbms/admin/catupgrd.sql



bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 26 08:59:45 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1.0726E+10 bytes
Fixed Size                  2191016 bytes
Variable Size            1845498200 bytes
Database Buffers         8858370048 bytes
Redo Buffers               20021248 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           05-26-2014 09:03:37
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:14:21
Oracle Real Application Clusters
.                                         VALID      11.2.0.4.0  00:00:00
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:36
Final Actions
.                                                                00:00:32
Total Upgrade Time: 00:15:31

PL/SQL procedure successfully completed.

数据字典升级到11.2.0.4后的无效对象如下:

SQL> select count(*) FROM dba_invalid_objects;

  COUNT(*)
----------
       717

SQL> select OWNER,count(*) FROM dba_invalid_objects group by OWNER;

OWNER                            COUNT(*)
------------------------------ ----------
PUBLIC                                267
SYSTEM                                 10
SYS                                   405
CAUSER                                  3
WMSYS                                  28
PERFSTAT                                2
OCSPUSER                                2

7 rows selected.

原库9i的无效对象如下:

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                     7

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status='INVALID';

OWNER                          OBJECT_NAME                              OBJECT_TYPE
------------------------------ ---------------------------------------- ------------------
SYS                            DBMS_AQADM_SYS                           PACKAGE BODY
SYS                            DBMS_IREFRESH                            PACKAGE BODY
SYS                            DBMS_PCLXUTIL                            PACKAGE BODY
SYS                            DBMS_PRVTAQIP                            PACKAGE BODY
SYS                            DBMS_SNAPSHOT                            PACKAGE BODY
SYS                            DBMS_STATS                               PACKAGE BODY
SYS                            UTL_RECOMP                               PACKAGE BODY

7 rows selected.

继续编译无效对象

SQL> spool '/home/oracle/utlrp.log';
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) FROM dba_invalid_objects;

  COUNT(*)
----------
         0

SQL> spool off
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

修改相关配置文件
节点1如下:

bash-3.2$ env|grep ORACLE
ORACLE_SID=xxdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
bash-3.2$ tail -5 /var/opt/oracle/oratab 
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM1:/u01/crs/oracle/product/11.2.0/crs_1:N            # line added by Agent
xxdb1:/u01/app/oracle/product/11.2.0/db_1:N

节点2如下:

bash-3.2$ env|grep ORACLE
ORACLE_SID=xxdb2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
bash-3.2$ tail -5 /var/opt/oracle/oratab
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
+ASM2:/u01/crs/oracle/product/11.2.0/crs_1:N            # line added by Agent
xxdb2:/u01/app/oracle/product/11.2.0/db_1:N

*****************************************************************
第四步:注册相关资源到11.2.0.4的CRS
*****************************************************************
创建spfile到asm
修改后参数如下:

bash-3.2$ cat /home/oracle/pfile_new.txt 
*.memory_max_target=51539607552	#48GB
*.memory_target=51539607552	#48GB
*.audit_file_dest='/u01/app/oracle/admin/xxdb/adump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/xxdb/controlfile/control01.ctl','+DATA/xxdb/controlfile/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/xxdb/cdump'
*.db_block_size=16384
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='kingxxdb'
xxdb1.instance_name='xxdb1'
xxdb2.instance_name='xxdb2'
xxdb2.instance_number=2
xxdb1.instance_number=1
*.job_queue_processes=10
*.log_archive_dest='+data/xxdb/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=5000
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1335
xxdb1.thread=1
xxdb2.thread=2
*.undo_management='AUTO'
*.undo_retention=10800
xxdb2.undo_tablespace='UNDOTBS2'
xxdb1.undo_tablespace='UNDOTBS1'
*.SESSION_CACHED_CURSORS=300
xxdb1.cluster_interconnects='162.34.30.35'
xxdb2.cluster_interconnects='162.34.30.36'


bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 26 09:46:55 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/pfile_new.txt'
ORACLE instance started.

Total System Global Area 1.0726E+10 bytes
Fixed Size                  2191016 bytes
Variable Size            2113933656 bytes
Database Buffers         8589934592 bytes
Redo Buffers               20021248 bytes
SQL> create spfile='+DATA/xxdb/datafile/spfilekingxxdb.ora' from pfile='/home/oracle/pfile_new.txt';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

bash-3.2$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
bash-3.2$ cat initxxdb1.ora
SPFILE='+DATA/xxdb/datafile/spfilekingxxdb.ora'
bash-3.2$ scp initxxdb1.ora xxdb2:/u01/app/oracle/product/11.2.0/db_1/dbs/initxxdb2.ora
initxxdb1.ora        100% |*********************************************************************************|    48       00:00    
bash-3.2$ 

创建密码文件

bash-3.2$ id
uid=101(oracle) gid=100(oinstall)
节点1:
bash-3.2$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwxxdb1 entries=10 password=oracle force=y

节点2:
bash-3.2$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwxxdb2 entries=10 password=oracle force=y

清理其他无用参数文件
节点1如下:

bash-3.2$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
bash-3.2$ ls -ltr
总数 12
-rw-r--r--   1 oracle   oinstall      48  9月 24日 11:52 initxxdb1.ora
-rw-r-----   1 oracle   oinstall    2560  9月 25日 12:32 orapwxxdb1
-rw-rw----   1 oracle   oinstall    1544  9月 25日 12:39 hc_xxdb1.dat

bash-3.2$ pwd
/u01/app/oracle/product/10.2.5/db_1/dbs
bash-3.2$ rm -rf *

节点2如下:

bash-3.2$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
bash-3.2$ ls -ltr
总数 12
-rw-r--r--   1 oracle   oinstall      48  9月 24日 11:53 initxxdb2.ora
-rw-r-----   1 oracle   oinstall    2560  9月 25日 12:33 orapwxxdb2
-rw-rw----   1 oracle   oinstall    1544  9月 25日 12:45 hc_xxdb2.dat
bash-3.2$ id
uid=101(grid) gid=100(oinstall)
bash-3.2$ crs_stat -t
名称           类型           目标      状态      主机        
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    xxdb1       
ora.FRA.dg     ora....up.type ONLINE    ONLINE    xxdb1       
ora....ER.lsnr ora....er.type ONLINE    ONLINE    xxdb1       
ora....N1.lsnr ora....er.type ONLINE    ONLINE    xxdb1       
ora.OCRVOTE.dg ora....up.type ONLINE    ONLINE    xxdb1       
ora.asm        ora.asm.type   ONLINE    ONLINE    xxdb1       
ora....SM1.asm application    ONLINE    ONLINE    xxdb1       
ora....B1.lsnr application    ONLINE    ONLINE    xxdb1       
ora.xxdb1.gsd  application    OFFLINE   OFFLINE               
ora.xxdb1.ons  application    ONLINE    ONLINE    xxdb1       
ora.xxdb1.vip  ora....t1.type ONLINE    ONLINE    xxdb1       
ora....SM2.asm application    ONLINE    ONLINE    xxdb2       
ora....B2.lsnr application    ONLINE    ONLINE    xxdb2       
ora.xxdb2.gsd  application    OFFLINE   OFFLINE               
ora.xxdb2.ons  application    ONLINE    ONLINE    xxdb2       
ora.xxdb2.vip  ora....t1.type ONLINE    ONLINE    xxdb2       
ora.cvu        ora.cvu.type   ONLINE    ONLINE    xxdb1       
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    xxdb1       
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    xxdb1       
ora.ons        ora.ons.type   ONLINE    ONLINE    xxdb1       
ora....ry.acfs ora....fs.type ONLINE    ONLINE    xxdb1       
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    xxdb1 


注册相关资源

bash-3.2$ id
uid=101(oracle) gid=100(oinstall)
bash-3.2$ env|grep ORACLE
ORACLE_SID=xxdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
bash-3.2$ srvctl add database -d kingxxdb -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/xxdb/datafile/spfilekingxxdb.ora
bash-3.2$ srvctl add instance -d kingxxdb -n xxdb1 -i xxdb1
bash-3.2$ srvctl add instance -d kingxxdb -n xxdb2 -i xxdb2
bash-3.2$ srvctl start database -d kingxxdb
bash-3.2$ srvctl status asm
ASM 正在 xxdb1,xxdb2 上运行
bash-3.2$ srvctl status database -d kingxxdb
实例 xxdb1 正在节点 xxdb1 上运行
实例 xxdb2 正在节点 xxdb2 上运行
bash-3.2$ srvctl status listener 
监听程序 LISTENER 已启用
监听程序 LISTENER 正在节点上运行: xxdb1,xxdb2

												 
bash-3.2$ id
uid=101(grid) gid=100(oinstall)
bash-3.2$ crs_stat -t
名称           类型           目标      状态      主机        
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    xxdb1       
ora.FRA.dg     ora....up.type ONLINE    ONLINE    xxdb1       
ora....ER.lsnr ora....er.type ONLINE    ONLINE    xxdb1       
ora....N1.lsnr ora....er.type ONLINE    ONLINE    xxdb1       
ora.OCRVOTE.dg ora....up.type ONLINE    ONLINE    xxdb1       
ora.asm        ora.asm.type   ONLINE    ONLINE    xxdb1       
ora....SM1.asm application    ONLINE    ONLINE    xxdb1       
ora....B1.lsnr application    ONLINE    ONLINE    xxdb1       
ora.xxdb1.gsd  application    OFFLINE   OFFLINE               
ora.xxdb1.ons  application    ONLINE    ONLINE    xxdb1       
ora.xxdb1.vip  ora....t1.type ONLINE    ONLINE    xxdb1       
ora....SM2.asm application    ONLINE    ONLINE    xxdb2       
ora....B2.lsnr application    ONLINE    ONLINE    xxdb2       
ora.xxdb2.gsd  application    OFFLINE   OFFLINE               
ora.xxdb2.ons  application    ONLINE    ONLINE    xxdb2       
ora.xxdb2.vip  ora....t1.type ONLINE    ONLINE    xxdb2       
ora....xxdb.db ora....se.type ONLINE    ONLINE    xxdb1       
ora.cvu        ora.cvu.type   ONLINE    ONLINE    xxdb1       
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE               
ora....network ora....rk.type ONLINE    ONLINE    xxdb1       
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    xxdb1       
ora.ons        ora.ons.type   ONLINE    ONLINE    xxdb1       
ora....ry.acfs ora....fs.type ONLINE    ONLINE    xxdb1       
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    xxdb1    												 
												 

修改redo

alter system checkpoint;
alter system switch logfile;
alter system archive log current;

set line 400
col MEMBER for a60
set pagesize 30
select l.thread#,l.group#,l.bytes/1024/1024 mb,l.status,lf.member 
from v$log l,v$logfile lf 
where l.group#=lf.group#
order by 1,2,lf.member    
/

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;
ALTER DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP 12;


ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATA/xxdb/onlinelog/redo01a.log','+FRA/xxdb/onlinelog/redo01b.log') size 512m reuse;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATA/xxdb/onlinelog/redo02a.log','+FRA/xxdb/onlinelog/redo02b.log') size 512m reuse;  
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 ('+DATA/xxdb/onlinelog/redo03a.log','+FRA/xxdb/onlinelog/redo03b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 ('+DATA/xxdb/onlinelog/redo04a.log','+FRA/xxdb/onlinelog/redo04b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ('+DATA/xxdb/onlinelog/redo05a.log','+FRA/xxdb/onlinelog/redo05b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ('+DATA/xxdb/onlinelog/redo06a.log','+FRA/xxdb/onlinelog/redo06b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+DATA/xxdb/onlinelog/redo07a.log','+FRA/xxdb/onlinelog/redo07b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+DATA/xxdb/onlinelog/redo08a.log','+FRA/xxdb/onlinelog/redo08b.log') size 512m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ('+DATA/xxdb/onlinelog/redo09a.log','+FRA/xxdb/onlinelog/redo09b.log') size 512m reuse;  
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DATA/xxdb/onlinelog/redo10a.log','+FRA/xxdb/onlinelog/redo10b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 ('+DATA/xxdb/onlinelog/redo11a.log','+FRA/xxdb/onlinelog/redo11b.log') size 512m reuse; 
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 12 ('+DATA/xxdb/onlinelog/redo12a.log','+FRA/xxdb/onlinelog/redo12b.log') size 512m reuse; 

alter system checkpoint;
alter system switch logfile;
alter system archive log current;


ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP 12;

查看新数据库的版本信息机相关组件信息

bash-3.2$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 26 10:39:21 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set line 400
SQL> select * from gv$version;

   INST_ID BANNER
---------- --------------------------------------------------------------------------------
         1 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
         1 PL/SQL Release 11.2.0.4.0 - Production
         1 CORE 11.2.0.4.0      Production
         1 TNS for Solaris: Version 11.2.0.4.0 - Production
         1 NLSRTL Version 11.2.0.4.0 - Production
         2 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
         2 PL/SQL Release 11.2.0.4.0 - Production
         2 CORE 11.2.0.4.0      Production
         2 TNS for Solaris: Version 11.2.0.4.0 - Production
         2 NLSRTL Version 11.2.0.4.0 - Production

10 rows selected.

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0


SQL> select * from gv$option order by 1;

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1 Partitioning                                                     TRUE
         1 Objects                                                          TRUE
         1 Real Application Clusters                                        TRUE
         1 Advanced replication                                             TRUE
         1 Bit-mapped indexes                                               TRUE
         1 Connection multiplexing                                          TRUE
         1 Connection pooling                                               TRUE
         1 Database queuing                                                 TRUE
         1 Incremental backup and recovery                                  TRUE
         1 Instead-of triggers                                              TRUE
         1 Parallel backup and recovery                                     TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1 Parallel execution                                               TRUE
         1 Parallel load                                                    TRUE
         1 Point-in-time tablespace recovery                                TRUE
         1 Fine-grained access control                                      TRUE
         1 Proxy authentication/authorization                               TRUE
         1 Change Data Capture                                              TRUE
         1 Plan Stability                                                   TRUE
         1 Online Index Build                                               TRUE
         1 Coalesce Index                                                   TRUE
         1 Managed Standby                                                  TRUE
         1 Materialized view rewrite                                        TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1 Database resource manager                                        TRUE
         1 Spatial                                                          TRUE
         1 Automatic Storage Management                                     TRUE
         1 Export transportable tablespaces                                 TRUE
         1 Transparent Application Failover                                 TRUE
         1 Fast-Start Fault Recovery                                        TRUE
         1 Sample Scan                                                      TRUE
         1 Duplexed backups                                                 TRUE
         1 Java                                                             TRUE
         1 OLAP Window Functions                                            TRUE
         1 Block Media Recovery                                             TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1 Fine-grained Auditing                                            TRUE
         1 Application Role                                                 TRUE
         1 Enterprise User Security                                         TRUE
         1 Oracle Data Guard                                                TRUE
         1 Oracle Label Security                                            FALSE
         1 OLAP                                                             TRUE
         1 Basic Compression                                                TRUE
         1 Join index                                                       TRUE
         1 Trial Recovery                                                   TRUE
         1 Data Mining                                                      TRUE
         1 Online Redefinition                                              TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1 Streams Capture                                                  TRUE
         1 File Mapping                                                     TRUE
         1 Block Change Tracking                                            TRUE
         1 Flashback Table                                                  TRUE
         1 Flashback Database                                               TRUE
         1 Transparent Data Encryption                                      TRUE
         1 Backup Encryption                                                TRUE
         1 Unused Block Compression                                         TRUE
         1 Oracle Database Vault                                            FALSE
         1 Result Cache                                                     TRUE
         1 SQL Plan Management                                              TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1 SecureFiles Encryption                                           TRUE
         1 Real Application Testing                                         TRUE
         1 Flashback Data Archive                                           TRUE
         1 DICOM                                                            TRUE
         1 Active Data Guard                                                TRUE
         1 Server Flash Cache                                               TRUE
         1 Advanced Compression                                             TRUE
         1 XStream                                                          TRUE
         1 Deferred Segment Creation                                        TRUE
         1 Data Redaction                                                   TRUE
         2 Data Redaction                                                   TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 Partitioning                                                     TRUE
         2 Objects                                                          TRUE
         2 Real Application Clusters                                        TRUE
         2 Advanced replication                                             TRUE
         2 Bit-mapped indexes                                               TRUE
         2 Connection multiplexing                                          TRUE
         2 Connection pooling                                               TRUE
         2 Database queuing                                                 TRUE
         2 Incremental backup and recovery                                  TRUE
         2 Instead-of triggers                                              TRUE
         2 Parallel backup and recovery                                     TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 Parallel execution                                               TRUE
         2 Parallel load                                                    TRUE
         2 Point-in-time tablespace recovery                                TRUE
         2 Fine-grained access control                                      TRUE
         2 Proxy authentication/authorization                               TRUE
         2 Change Data Capture                                              TRUE
         2 Plan Stability                                                   TRUE
         2 Online Index Build                                               TRUE
         2 Coalesce Index                                                   TRUE
         2 Managed Standby                                                  TRUE
         2 Materialized view rewrite                                        TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 Database resource manager                                        TRUE
         2 Spatial                                                          TRUE
         2 Automatic Storage Management                                     TRUE
         2 Export transportable tablespaces                                 TRUE
         2 Transparent Application Failover                                 TRUE
         2 Fast-Start Fault Recovery                                        TRUE
         2 Sample Scan                                                      TRUE
         2 Duplexed backups                                                 TRUE
         2 Java                                                             TRUE
         2 OLAP Window Functions                                            TRUE
         2 Block Media Recovery                                             TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 Fine-grained Auditing                                            TRUE
         2 Application Role                                                 TRUE
         2 Enterprise User Security                                         TRUE
         2 Oracle Data Guard                                                TRUE
         2 Oracle Label Security                                            FALSE
         2 OLAP                                                             TRUE
         2 Basic Compression                                                TRUE
         2 Join index                                                       TRUE
         2 Trial Recovery                                                   TRUE
         2 Data Mining                                                      TRUE
         2 Online Redefinition                                              TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 Streams Capture                                                  TRUE
         2 File Mapping                                                     TRUE
         2 Block Change Tracking                                            TRUE
         2 Flashback Table                                                  TRUE
         2 Flashback Database                                               TRUE
         2 Transparent Data Encryption                                      TRUE
         2 Backup Encryption                                                TRUE
         2 Unused Block Compression                                         TRUE
         2 Oracle Database Vault                                            FALSE
         2 Result Cache                                                     TRUE
         2 SQL Plan Management                                              TRUE

   INST_ID PARAMETER                                                        VALUE
---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 SecureFiles Encryption                                           TRUE
         2 Real Application Testing                                         TRUE
         2 Flashback Data Archive                                           TRUE
         2 DICOM                                                            TRUE
         2 Active Data Guard                                                TRUE
         2 Server Flash Cache                                               TRUE
         2 Advanced Compression                                             TRUE
         2 XStream                                                          TRUE
         2 Deferred Segment Creation                                        TRUE

已选择130行。

设置增量备份trace

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+FRA/xxdb/tracking.trc' REUSE;

修改rman配置信息

rman target /
show all;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata1/rman/controlbak/ca_controlfile_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/xxdb/snapcf_xxdb1.f';

开始测试数据完整性。

在操作机上执行以下命令
查看升级之前新建的测试数据是否存在

select id,to_char(my_date,'yyyy/mm/dd hh24:mi:ss') from test_20141017;

查看升级之后应用用户表的条目数是否一致

set serverout on
declare 
v_sum_num number :=0;
v_num number :=0;
v_table_name varchar2(100);
str_sql varchar2(500);
cursor tab_name is select table_name from dba_tables where owner= upper('&user_name') order by 1;
begin
	dbms_output.put_line('---------TABLE SUM------------');
	for i in tab_name
		loop
			v_table_name:=i.table_name;
			execute immediate 'select count(*) from ' || v_table_name into v_num;
			dbms_output.put_line(v_table_name||':'||chr(9)||v_num);
			v_sum_num:=v_num+v_sum_num;
		end loop;
	dbms_output.put_line('------------------------------');
	dbms_output.put_line('SUM_NUM'||':'||chr(9)||v_sum_num);
end;
/	


---------TABLE SUM------------
ABCDEFGP:        5
ABCDEFGR:        386
ABCDEFGTBLCERT:  56453925
ABCDEFGTBLREVOKE:        9489064
ABCDEFGABLE:     4
ABCDEFGTBLCERT:  0
ABCDEFGTBLREVOKE:        0
ABCDEFGHCERT:    0
ABCDEFGHCERTEX:  0
ABCDEFGEYCERT:   1
ABCDEFGT:        52578693
ABCDEFGTACTIVATE:        740
ABCDEFGTCOUNTER: 22
ABCDEFGTREQ:     73777925
ABCDEFGL:        24
ABCDEFGAYKEYINFO:        1
ABCDEFGBLECERT:  112983
ABCDEFGINFO:     16213
ABCDEFGOKE:      14770154
ABCDEFGNFO:      151
ABCDEFGSER:      196
ABCDEFGFDEFINFO: 2
ABCDEFGTISTIC:   150889524
ABCDEFGCONST:    6
ABCDEFGVAR:      30
ABCDEFGG:        154543835
ABCDEFGK_SYN:    1
ABCDEFGP_REVOKE: 7479553
------------------------------
SUM_NUM:        520113438

PL/SQL procedure successfully completed.

Elapsed: 00:07:10.45

至此,升级全部结束。

标签:

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: