oracle 过程更新数据丢失,记一次数据库字典丢失并恢复过程

工程师到用户现场后发现客户升级 oracle 10g rac 10.2.0.3 到 10.2.0.5失败，crs进程无法正常启动。详细解决步骤如下：

(一) 、工程师重新升级安装各个节点crs,rac软件(附官方文档加注释)

Installing the Oracle Database 10g Patch Set Interactively

使用交互安装升级oracle 10.2.0.5补丁

To install the Oracle Database 10g patch set interactively:

Note:

If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or higher installation, Oracle Universal Installer displays a warning dialog with the following error:

OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 4

10.2.0.5

The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.

1. Log in as the oracle user.  ----su – oracle 使用oracle用户登录

2. If you are not installing the software on the local computer, then run the following command on remote machine:

o Bourne, Bash, or Korn shell:

o $ export DISPLAY=localhost:0.0

o C shell:

o % setenv DISPLAY local_host:0.0

In this example, local_host is the host name or IP address of the computer that you want to use to display Oracle Universal Installer.

Now to enable X applications, run the following command on the machine that you want to use to display Oracle Universal Installer:

$ xhost + [fully_qualified_remote_host_name]

通过下面方法登录:

#xhost +

#Su  -  oracle

$

3. Enter the following commands to start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software:

4. % cd patchset_directory/Disk1

5. % ./runInstaller

Cd  安装目录

./runinstaller    ----安装补丁

6. On the Welcome screen, click Next.

7. On the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.

8. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.

9. On the Product-specific Prerequisite Checks screen, correct any reported errors, and click Next.

10. Enter details regarding the CSI Number, OracleMetaLink Account user name and Country code on the Oracle Configuration Manager Registration screen and click Next.

生级过程选择全部节点,当出现选择目录的选项时,需要先选择crs的升级目录进行升级.

Note:

The registration and configuration can also be done manually after patchset installation.

The OCM registration page appears only while applying 10.2.0.4 patch over existing DB instances alone.

11. On the Summary screen, click Install.

This screen lists all of the patches available for installation.

12. When prompted, run the $ORACLE_HOME/root.sh script. as the root user. If you are applying the patch set to an Oracle RAC installation, then run the root.sh script. on each node of the cluster.

Note:

If you are applying this patch set to an Oracle Clusterware installation, then complete all of the steps displayed on the Oracle Universal Installer screen.

The Oracle Clusterware installation instructions displayed on the Oracle Universal Installer screen are also available in the CRS_home/install/readme.txt file.

o When applying this patch set on an Oracle Clusterware home, Oracle Universal Installer prompts you to run the root102.sh script. from $ORACLE_HOME/install. You must run this script. as the root user on all nodes.

o Before you add a new node, ensure that you run rootpre.sh script. as the root user on the new node.

升级完成后会提示用root用户执行两个*.sh的脚本,分别在两个节点上执行即可

完毕后重新执行

./runinstaller    ----安装补丁

升级过程选择全部节点,当出现选择目录的选项时,需要先选择db的升级目录进行升级.

升级完成后会提示用root用户执行两个*.sh的脚本,分别在两个节点上执行即可

----注意 ：升级 过程 一定要注意升级目录的选择，执行*.sh脚本时需要一个节点把所有脚本都执行完毕后再在另一个节点上执行脚本

(二) 、通过执行upgrade脚本升级数据库版本，数据库实例可以正常启动。

db升级数据库步骤(官方文档)

在任意一个节点上执行

1. For Oracle RAC installations:

a. Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:

b. $ sqlplus /nolog

c. SQL> CONNECT SYS AS SYSDBA

d. Enter password: SYS_password

e. SQL> STARTUP NOMOUNT

f. Set the CLUSTER_DATABASE initialization parameter to FALSE:

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

h. Shut down the database:

i. SQL> SHUTDOWN

$srvctl stop database –d ora8    ----全局关闭数据库

$ sqlplus /nolog      -----以下步骤都在一号节点上执行

SQL> CONNECT SYS AS SYSDBA

SQL> STARTUP NOMOUNT

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

2. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE    ----执行db升级

SQL> SPOOL patch.log

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

SQL> SPOOL OFF

3. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.

4. If necessary, rerun the catupgrd.sql script. after correcting any problems.

5. Restart the database:

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

6. Run the utlrp.sql script. to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

SQL> @?/rdbms/admin/utlrp.sql    重新编译无效对象

7.

Note:

When the 10.2.0.4 patch set is applied to an Oracle Database 10g Standard Edition database, there may be 54 invalid objects after the utlrp.sql script. runs. These objects belong to the unsupported components and do not affect the database operation.

Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following:

BIN$4lzljWIt9gfgMFeM2hVSoA==$0

8. Run the following command to check the status of all the components after the upgrade:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;   ---查看组件

In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.

--&gt Oracle Catalog Views         [upgrade]  VALID

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

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

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

--&gt Oracle Java Packages         [upgrade]  VALID

--&gt Oracle Text                  [upgrade]  VALID

--&gt Oracle XML Database          [upgrade]  VALID

--&gt Oracle Workspace Manager     [upgrade]  VALID

--&gt Oracle Data Mining           [upgrade]  VALID

--&gt Messaging Gateway            [upgrade]  VALID

--&gt OLAP Analytic Workspace      [upgrade]  VALID

--&gt OLAP Catalog                 [upgrade]  VALID

--&gt Oracle OLAP API              [upgrade]  VALID

--&gt Oracle interMedia            [upgrade]  VALID

--&gt Spatial                      [upgrade]  INVALID

--&gt Oracle Ultra Search          [upgrade]  VALID

--&gt Oracle Label Security        [upgrade]  VALID

--&gt Expression Filter            [upgrade]  VALID

--&gt EM Repository                [upgrade]  VALID

升级过程中出现大量错误，系统提示找不到对象，书库组件有部分状态为invalid：根据用户之前的反应的情况，了解到数据库在做升级之前就发现大量丢失系统表的现象，工程师需要重建构建数据库系统表：

升级之后同时发现ora-1403 encount ，ORA-00942: table or view does not exist

,数据库无法正常通过expdp,exp工具导出数据库数据库(升级之前用户就发现有这些问题出现)

(三) 、通过执行oracle数据库字典重建脚本，重新修复数据库对象重新执行数据库升级脚本所有组件在修正完毕后状态为valid.数据库已经可以正常通过expdp,exp导出数据库，但仍然报ora-1403 encount的错误。

重建系统表，并修复无效对象

STARTUP UPGRADE

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

Shutdown immedaite

重新升级db

SQL> STARTUP UPGRADE    ----重新执行db升级

SQL> SPOOL patch.log

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

SQL> SPOOL OFF

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

@?/rdbms/admin/utlrp.sql

9. If you are using the Oracle Recovery Manager catalog, enter the following command:

10. $ rman catalog

11. For Oracle RAC installations:

a. 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

b. Start any database services that you want to use:

c. $ srvctl start service -d db_name -s service_name

(四) 、为了彻底解决ora-1403 encount的报错问题，最后工程将生产数据库数据导入测试数据库，并配合用户方负责工程师测试业务流程，测试数据是否可以正常运行，最大程度的保证数据的完整性及业务的连续性。

测试完毕后，工程师将生产系统rac 数据库删除并重新建库，将之前通过expdp工具导出的数据导入生产系统rac 数据库。

(五) 、生产系统恢复正常，业务正常开展