数据库从9.2.0.6 升级到 9.2.0.8 RAC

ALLSTARS_ORACLE

数据库从9.2.0.6 升级到 9.2.0.8 RAC



数据库升级过程
3.1、数据库库安装
1. 备份目前的数据库软件 $ORACLE_BASE,$ORACLE_HOME,oui,oraInventory(see /etc/oraInst.loc)  ,jre  .
cd /oracle/app
tar cvf oracle.9.2.0.6.tar oracle
2.数据库进行备份
3.停止数据库，监听
4. 开始新版本数据库的安装

$ patchset_directory/Disk1
$ ./runInstaller

5. When prompted, run the $ORACLE_HOME/root.sh script as the root user.

说明： 如果为 RAC,需要在2个节点上分别运行root.sh

3.2. 数据库补丁的安装

1 安装cpu 补丁9655027

$ cd 9655027
opatch apply -local

(1).Run the remove_demo.sh script to remove vulnerable Oracle HTTP Server demos.

To run remove_demo.sh, go to the directory where you downloaded the patch and enter the following commands:

cd 9655027
sh remove_demo.sh
5.Remove the following line in $ORACLE_HOME/Apache/Apache/conf/httpd.conf, if it exists:

FastCGIServer fcgi-bin/echo -initial-env ORACLE_HOME -initial-env NLS_LANG
(2).Run the following commands as the root user:

chown root $ORACLE_HOME/bin/dbsnmp
chmod 6750 $ORACLE_HOME/bin/dbsnmp

chown root dbsnmp
chmod 6750 dbsnmp
7.This step is required only if your database contains an Identity Management Metadata Repository.
If you are not sure whether you need to perform this step, you can enter the following query
(a non-null result means you should perform the step):

SQL> select USERNAME from dba_users where upper(USERNAME) like '%ODS%';
Run the script installaci.sh to address an ACI to be placed on the Root
Directory Server Entry (DSE) of the Oracle Internet Directory on which this
patch is being applied.

To do so, make sure the database and Oracle Internet Directory are running.
Then go to the directory where you downloaded the patch and enter the following commands:

cd 9655027
sh installaci.sh
The command will prompt you to input the following parameters:

<host> is the Oracle Internet Directory hostname.domainname being patched.

<port> is the port on which the Directory being patched is listening for LDAP traffic.

<bindDN> is the DN of the admin user. Specify "cn=orcladmin", unless
privileges associated with this account have been distributed across multiple users.

<password> is the password for the admin user.

3.2.5 Post Installation Instructions for RAC Databases
Select one node to execute the post installation steps. Follow the same set
of instructions as mentioned in the Section 3.2.3,
"Post Installation Instructions for a Non-RAC Environment".

Follow these steps:

1.Make sure that no users access the selected instance, and that all other instances are shut down.

2.Restart instances on all nodes.

You do not need to run post installation steps again on other nodes.

2 安装one-off补丁

4008133
4605569
4741381
5386204
5496862
5508574
5576565
5606601
5656453
5763245
5868257
6068628
6128197
6200132
6243069
6401576
7253303


export  OPATCH_PLATFORM_ID=212

cd 4008133
opatch apply -local
cd ..
cd 4605569
opatch apply -local
cd ..
cd 4741381
opatch apply -local
cd ..
cd 5386204
opatch apply -local
cd ..
cd 5496862
opatch apply -local
cd ..
cd 5508574
opatch apply -local
cd ..
cd 5576565
opatch apply -local
cd ..
cd 5606601
opatch apply -local
cd ..
cd 5656453
opatch apply -local
cd ..
cd 5763245
opatch apply -local
cd ..
cd 5868257
opatch apply -local
cd ..
cd 6068628
opatch apply -local
cd ..
cd 6128197
opatch apply -local
cd ..
cd 6200132
opatch apply -local
cd ..
cd 6243069
opatch apply -local
cd ..
cd 6401576
opatch apply -local
cd ..
cd 7253303
opatch apply -local


3.3、升级数据库字典

安装前检查：

(1). Check SYSTEM Tablespace Size

If JServer is part of the installation ensure that there is at least 10 MB  

(2). Check XDB Tablespace Size
For Oracle RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.

(3). Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Users who have JVM (Java enabled) or JVM and XDB installed on their 9.2.0.1 databases should make sure
that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the
catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script.
Please note that JVM and XML DB was shipped as part of the default 9.2.0.1 seed database,
and will be present unless the user explicitly installed a 9.2.0.1 instance without them.

1.以sys用户登录
$ sqlplus/nolog
SQL> CONNECT SYS/SYS_password AS SYSDBA
2.启动数据库:

SQL> STARTUP nomount;

3. 查看是否以spfile参数启动了数据库

SQL> SHOW PARAMETER PFILE;

-- 如果没有spfile,建议使用pfile 创建spfile

4.检查SHARED_POOL_SIZE，JAVA_POOL_SIZE参数的大小

SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE

5.修改 SHARED_POOL_SIZE，JAVA_POOL_SIZE参数的值到150M以上

说明: 最佳为500M以上！！

a.If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:

注意：生产环境都满足，不需要调整该参数

--- ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

b.If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:

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

说明:如果数据使用初始化参数文件(非spfile文件)，则需要在initsid.ora中进行修改。

6. 如果为RAC数据库，则需要修改CLUSTER_DATABASE 参数值为false

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;


7.停止数据库的所有实例

SQL> SHUTDOWN immediate;

8.oracle用户登录,RAC2个节点上启动Global Services Daemon (GSD) 进程:

$ gsdctl start

9.RAC的2个节点上启动监听

$ lsnrctl start

10.启动数据库的第一个实例

sqlplus "/as sysdba"

SQL> STARTUP MIGRATE;
show parameter java_pool_size    --确保值在500M以上
show parameter shared_pool_size  --确保值在500M以上
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql    --  开始
SQL> SPOOL OFF

11.检查patch.log日志

12.启动数据库(第一个实例上)

SQL> SHUTDOWN immediate;
SQL> STARTUP;

13.运行 utlrp.sql脚本来编译无效对象

SQL> @?/rdbms/admin/utlrp.sql


/*******************************
14.RAC数据库需要重新将CLUSTER_DATABASE参数设为true.

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;

15.重启数据库

SQL> SHUTDOWN immediate;
SQL> EXIT;

$ srvctl start database -d db_name

*******************************/

16. 如果数据库备份使用 RMAN catalog, 则需要执行:

$ rman target / catalog username/password@alias

RMAN> UPGRADE CATALOG;
  
17. 如果本机上有其他非dba组的用户需要访问数据库，则需要开放ORACLE_HOME的访问权限

在RAC的所有节点上运行changePerm.sh脚本

$ cd $ORACLE_HOME/install
./changePerm.sh

3.4、加载 cpu补丁中的sql

1.For each database instance running on the Oracle home being patched,
connect to the database using SQL*Plus. Connect as SYSDBA and run the
catcpu.sql script as follows:

cd $ORACLE_HOME/cpu/CPUJul2010
sqlplus "/as sysdba"
SQL> @catcpu.sql
SQL> @?/rdbms/admin/utlrp.sql
You can check for any invalid objects by executing following statement:

SQL> SELECT owner,OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS= 'INVALID';

3.2.3.2 Recompiling Views in the Database

You may skip this section if you have recompiled views for this database during the installation of a previous CPU.

The time required to recompile the views and related objects depends on the total number of objects
and on your system configuration. In one internal Oracle test with approximately 2000
views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql
and utlrp.sql was about 30 minutes.

If you want to check whether view recompilation has already been performed for the database,
execute the following statement.

SELECT * FROM registry$history where ID = '6452863';
If the view recompilation has been performed, this statement returns one or more rows.
If the view recompilation has not been performed, this statement returns no rows.

To recompile the views in the database, follow these steps:

1.Run the pre-check script (so named because it was initially released in CPUJan2008),
which reports the maximum number of views and objects that may be recompiled:

cd $ORACLE_HOME/cpu/view_recompile
sqlplus "/as sysdba"
SQL> @recompile_precheck_jan2008cpu.sql
SQL> QUIT
The purpose of this step is to help you determine whether view recompilation should be
done at the same time as the CPU install, or scheduled later.

2. 如果为非RAC数据库, perform this step. (If the database is in a RAC environment, go to the next step.)

Run the view recompilation script. Note that this script is run with the database in
upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus "/as sysdba"
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MIGRATE;
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN immediate;
SQL> STARTUP;
SQL> QUIT

3. 如果为RAC数据库，If the database is in a RAC environment, run the view recompilation script as follows.
Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
     show parameter cluster_database;
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
SQL> SHUTDOWN immediate;
SQL> STARTUP MIGRATE;
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN immediate;
SQL> STARTUP NOMOUNT;

Set the CLUSTER_DATABASE initialization parameter to TRUE:

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;

Restart the database:

SQL> SHUTDOWN immediate;
SQL> STARTUP;
SQL> QUIT
4.Check the log file for any errors. The log file is in the current directory and is named:
vcomp_<sid>_<timestamp>.log

5.If any invalid objects were reported, run the utlrp.sql script as follows:

sqlplus "/as sysdba"
SQL> @?/rdbms/admin/utlrp.sql
Then, manually recompile any invalid objects. For example:

SQL> alter package schemaname.packagename compile;
6.Verify that the view recompilation has been performed for the database, by executing the following statement:

SELECT * FROM registry$history where ID = '6452863';
      
3.5、 查看无效对象。

set linesize 400
set pagesize 200
select owner,object_name,object_type from dba_objects where status='INVALID';

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

select owner,object_type,object_name from dba_objects where status='INVALID'  and owner='SYS';