A-A+
solaris下oracle 9.2.0.4 rac升级到11.2.0.4 rac的操作手记
以前的一个核心数据库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、数据量较大，停机时间短（约一个多小时）
官方支持的升级路劲图
前期工作
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
至此，升级全部结束。
