Oracle 数据库升级

最新推荐文章于 2021-02-18 17:58:11 发布

ckth47350

最新推荐文章于 2021-02-18 17:58:11 发布

阅读量230

点赞数

文章标签：数据库开发工具 java

Oracle 数据库升级

升级

一 目的：11.2.0.1.0--------&gt11.2.0.3.0

二 原库基本信息

1 查看数据库版本

SQL> select * from v$version where rownum<=2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

2 查看失效的对象

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

----------

3 查看参数

SQL> show parameter shared

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

hi_shared_memory_address integer 0

max_shared_servers integer

shared_memory_address integer 0

shared_pool_reserved_size big integer 3774873

shared_pool_size big integer 0

shared_server_sessions integer

shared_servers integer 1

4 查看SGA

SQL> show sga

Total System Global Area 409194496 bytes

Fixed Size 2213856 bytes

Variable Size 289409056 bytes

Database Buffers 113246208 bytes

Redo Buffers 4325376 bytes

5 查看审计表

SQL> select * from aud$;

6 查看各个组件版本

SQL> col comp_name for a40

SQL> col status for a10

SQL> col version for a20

SQL> select comp_name,status,version from dba_server_registry;

COMP_NAME STATUS VERSION

---------------------------------------- ---------- --------------------

OWB VALID 11.2.0.1.0

Oracle Application Express VALID 3.2.1.00.10

Oracle Enterprise Manager VALID 11.2.0.1.0

OLAP Catalog VALID 11.2.0.1.0

Spatial VALID 11.2.0.1.0

Oracle Multimedia VALID 11.2.0.1.0

Oracle XML Database VALID 11.2.0.1.0

Oracle Text VALID 11.2.0.1.0

Oracle Expression Filter VALID 11.2.0.1.0

Oracle Rules Manager VALID 11.2.0.1.0

Oracle Workspace Manager VALID 11.2.0.1.0

COMP_NAME STATUS VERSION

---------------------------------------- ---------- --------------------

Oracle Database Catalog Views VALID 11.2.0.1.0

Oracle Database Packages and Types VALID 11.2.0.1.0

JServer JAVA Virtual Machine VALID 11.2.0.1.0

Oracle XDK VALID 11.2.0.1.0

Oracle Database Java Packages VALID 11.2.0.1.0

OLAP Analytic Workspace VALID 11.2.0.1.0

Oracle OLAP API VALID 11.2.0.1.0

18 rows selected.

二 备份数据库

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

[oracle@chen ~]$ mkdir backup

[oracle@chen ~]$ rman target / nocatalog

RMAN> backup database plus archivelog delete input format '/home/oracle/backup/chen_%U.bak';

[oracle@chen ~]$ sqlplus / as sysdba

三 停止数据库，备份数据库软件目录和产品目录

SQL> shutdown immediate

[oracle@chen ~]$ lsnrctl stop

[oracle@chen ~]$ emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://chen:1158/em/console/aboutApplication

Oracle Enterprise Manager 11g is running.

------------------------------------------------------------------

Logs are generated in directory /u01/app/oracle/product/11.2.0/chen_chen/sysman/log

[oracle@chen ~]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://chen:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

all attemps to stop oc4j failed... now trying to kill 9

--- Failed to shutdown DBConsole Gracefully ---

... Stopped.

[oracle@chen ~]$ tar -zcvf prou.tar /u01/app/oracle/

四 解压11.2.0.3.0安装介质

[root@chen u01]# mkdir database-1

[root@chen u01]# mv /root/p10404530_112030_Linux-x86-64_* database-1/

[root@chen database-1]# ll -rth

total 2.4G

-rw-r--r-- 1 root root 1.3G Jun 12 10:39 p10404530_112030_Linux-x86-64_1of7.zip

-rw-r--r-- 1 root root 1.1G Jun 12 11:07 p10404530_112030_Linux-x86-64_2of7.zip

[root@chen database-1]# unzip p10404530_112030_Linux-x86-64_1of7.zip

[root@chen database-1]# unzip p10404530_112030_Linux-x86-64_2of7.zip

[root@chen u01]# chown -R oracle.oinstall /u01/

五 升级数据软件

1 ./runInstaller

[root@chen database-1]# xhost +

xhost: unable to open display ""

[root@chen database-1]# su - oracle

[oracle@chen database]$ pwd

/u01/database-1/database

[oracle@chen database]$ ./runInstaller

[root@chen database-1]# cd /u01/app/oracle/product/11.2.4/

[root@chen 11.2.4]# ./root.sh

Performing root user operation for Oracle 11g

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /u01/app/oracle/product/11.2.4

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying oraenv to /usr/local/bin ...

The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)

[n]: y

Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

3 配置环境变量

[oracle@chen ~]$ vim .bash_profile

......

export ORACLE_HOME=$ORACLE_BASE/product/11.2.4

......

[oracle@chen ~]$ source .bash_profile

[oracle@chen ~]$ vim /etc/oratab

chen:/u01/app/oracle/product/11.2.4:N

4 复制参数文件

[oracle@chen ~]$ cd $ORACLE_HOME/dbs

[oracle@chen dbs]$ pwd

/u01/app/oracle/product/11.2.4/dbs

[oracle@chen dbs]$ ls

init.ora

[oracle@chen dbs]$ cp /u01/app/oracle/product/11.2.0/dbs/* .

[oracle@chen dbs]$ ls

hc_chen.dat init.ora lkCHEN orapwchen snapcf_chen.f spfilechen.ora

5 复制网络文件

[oracle@chen 11.2.4]$ cd $ORACLE_HOME/network/admin

[oracle@chen admin]$ ls

samples shrept.lst

[oracle@chen admin]$ pwd

/u01/app/oracle/product/11.2.4/network/admin

[oracle@chen admin]$ cp -r /u01/app/oracle/product/11.2.0/network/admin/* .

[oracle@chen admin]$ ls

listener.ora samples shrept.lst sqlnet.ora tnsnames.ora

三 执行预升级脚本检测

1升级模式启动数据库

[oracle@chen admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 4 13:54:34 2015

Connected to an idle instance.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 409194496 bytes

Fixed Size 2228864 bytes

Variable Size 281021824 bytes

Database Buffers 121634816 bytes

Redo Buffers 4308992 bytes

Database mounted.

Database opened.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

-------------------------------- ------------------------

chen OPEN MIGRATE

2 执行检测脚本(检测是否有不符合升级的选项)

SQL> spool /home/oracle/c_utl01.txt

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 08-04-2015 13:58:22

Script Version: 11.2.0.3.0 Build: 001

**********************************************************************

Database:

**********************************************************************

--&gt name: CHEN

--&gt version: 11.2.0.1.0

--&gt compatible: 11.2.0.0.0

--&gt blocksize: 8192

--&gt platform: Linux x86 64-bit

--&gt timezone file: V11

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--&gt SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 679 MB

--&gt SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 463 MB

--&gt UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 400 MB

--&gt TEMP tablespace is adequate for the upgrade.

.... minimum required size: 60 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.

**********************************************************************

--&gt If Target Oracle is 32-Bit, refer here for Update Parameters:

WARNING: --&gt "memory_target" needs to be increased to at least 436 MB

--&gt If Target Oracle is 64-Bit, refer here for Update Parameters:

WARNING: --&gt "memory_target" needs to be increased to at least 620 MB

**********************************************************************

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]

**********************************************************************

--&gt Oracle Catalog Views [upgrade] VALID

--&gt Oracle Packages and Types [upgrade] VALID

--&gt JServer JAVA Virtual Machine [upgrade] VALID

--&gt Oracle XDK for Java [upgrade] VALID

--&gt Oracle Workspace Manager [upgrade] VALID

--&gt OLAP Analytic Workspace [upgrade] VALID

--&gt OLAP Catalog [upgrade] VALID

--&gt EM Repository [upgrade] VALID

--&gt Oracle Text [upgrade] VALID

--&gt Oracle XML Database [upgrade] VALID

--&gt Oracle Java Packages [upgrade] VALID

--&gt Oracle interMedia [upgrade] VALID

--&gt Spatial [upgrade] VALID

--&gt Expression Filter [upgrade] VALID

--&gt Rule Manager [upgrade] VALID

--&gt Oracle Application Express [upgrade] VALID

... APEX will only be upgraded if the version of APEX in

... the target Oracle home is higher than the current one.

--&gt Oracle OLAP API [upgrade] VALID

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: --&gt 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 11.2.0.1.0 database timezone version

.... to the latest version which comes with the new release.

WARNING: --&gt 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 8 INVALID objects.

.... USER CTXSYS has 1 INVALID objects.

.... USER SYS has 11 INVALID objects.

WARNING: --&gt Database contains schemas with objects dependent on DBMS_LDAP package.

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

.... USER APEX_030200 has dependent objects.

**********************************************************************

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 removing all hidden parameters prior to upgrading.

To view existing hidden parameters execute the following command

while connected AS SYSDBA:

SELECT name,description from SYS.V$PARAMETER WHERE name

LIKE '\_%' ESCAPE '\'

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

**********************************************************************

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> spool off

3 查看并修改不满足项

SQL> show parameter shared

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

hi_shared_memory_address integer 0

max_shared_servers integer

shared_memory_address integer 0

shared_pool_reserved_size big integer 6501171

shared_pool_size big integer 0

shared_server_sessions integer

shared_servers integer 1

4 启动数据库闪回，创建回滚点

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------------------------

SQL> shutdown immediate

SQL> startup mount

SQL> alter database flashback on;

SQL> create restore point chen_rollback guarantee flashback database;

Restore point created.

SQL> col name for a15

SQL> select scn,name,storage_size from v$restore_point;

SCN NAME STORAGE_SIZE

---------- --------------- ------------

968263 CHEN_ROLLBACK 52428800

5 再次进行检查

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 08-04-2015 14:14:24

Script Version: 11.2.0.3.0 Build: 001

**********************************************************************

Database:

**********************************************************************

--&gt name: CHEN

--&gt version: 11.2.0.3.0

--&gt compatible: 11.2.0.0.0

--&gt blocksize: 8192

--&gt timezone file: V14

WARNING: --&gt Database not in OPEN state.

Database must be in OPEN state for script to execute correctly.

Current Status: MOUNTED.

SQL> shutdown immediate

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 409194496 bytes

Fixed Size 2228864 bytes

Variable Size 281021824 bytes

Database Buffers 121634816 bytes

Redo Buffers 4308992 bytes

Database mounted.

Database opened.

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 08-04-2015 14:17:12

Script Version: 11.2.0.3.0 Build: 001

**********************************************************************

Database:

**********************************************************************

--&gt name: CHEN

--&gt version: 11.2.0.1.0

--&gt compatible: 11.2.0.0.0

--&gt blocksize: 8192

--&gt platform: Linux x86 64-bit

--&gt timezone file: V11

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--&gt SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 679 MB

--&gt SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 463 MB

--&gt UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 400 MB

--&gt TEMP tablespace is adequate for the upgrade.

.... minimum required size: 60 MB

**********************************************************************

Flashback: ON

**********************************************************************

FlashbackInfo:

--&gt name: /u01/app/oracle/flash_recovery_area

--&gt limit: 3882 MB

--&gt used: 1090 MB

--&gt size: 3882 MB

--&gt reclaim: 0 MB

--&gt files: 5

WARNING: --&gt Flashback Recovery Area Set. Please ensure adequate disk space in recover

y areas before performing an upgrade.