1. 代码100分首页
  2. 未分类

对象名无效（查看无效对象）

小小码 • • 未分类 • 阅读 107

对象名无效（查看无效对象）

接上文发现数据库出现问题后，进行补丁更新操作，步骤如下。

补丁下载

登陆mos输入https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=443878077487755&id=2118136.2&_afrWindowMode=0&_adf.ctrl-state=1bd2vhpwox_55

选择补丁类型选择RU

对象名无效（查看无效对象）

选择对应的版本12.2.0.1

对象名无效（查看无效对象）

选择最新的补丁

对象名无效（查看无效对象）

选择对应的操作系统

对象名无效（查看无效对象）

点击下载

对象名无效（查看无效对象）

查看readme信息确认opatch版本

You must use the OPatch utility version 12.2.0.1.28 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2, which

查看当前数据库的opath版本及补丁情况

$ opatch version
OPatch Version: 12.2.0.1.6 版本过低
$opatch lspatches
There are no Interim patches installed in this Oracle Home "/u01/app/oracle/product/12.2.0/dbhome_1".
$opatch lsinventory
Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
查看补丁更新情况
SQL>set line 200
col ACTION_TIME  format a40
col COMMENTS   format a30
col version   format a25
SQL> SQ
SQL> 
SQL> select action_time, version,id,COMMENTS from dba_registry_history;

ACTION_TIME				 VERSION			   ID COMMENTS
---------------------------------------- ------------------------- ---------- ------------------------------
					 12.2.0.1			      RDBMS_12.2.0.1.0_LINUX.X64_170
没有安装任何补丁。

下载opatch

https://updates.oracle.com/download/6880880.html 选择对应的版本及操作系统

对象名无效（查看无效对象）

点击下载

对象名无效（查看无效对象）

补丁更新过程

1、数据库状态检查-日常巡检脚本检查数据库无问题。数据库备份等工作。

2、更新OPatch工具

oracle用户
添加opatch路径到环境变量vi .bash_profile修改如下
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
source .bash_profile
$ unzip p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
$ opatch version
OPatch Version: 12.2.0.1.37
OPatch succeeded.

3、冲突检查

解压补丁
$ unzip p33587128_122010_Linux-x86-64-Patch.zip
cd 33587128/
$ ls
custom  etc  files  README.html  README.txt
$ opatch prereq CheckConflictAgainstOHWithDetail -ph http://www.toutiao.com/a7245191000011391520/
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2023-06-13_17-33-29PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.   没有冲突，如果发现冲突需要解决后才可继续
OPatch succeeded.

4、数据库及监听停止

SQL>shutdown immediate
$lsnrctl stop
更新前建议手动重启数据库一次确认数据库可正常启动停止。
建议针对数据库进行冷备包括软件及数据库文件。
tar -pcvzf app.tar.gz app

5、应用补丁

数据库及监听停止后进行补丁应用，进入补丁目录执行opatch apply
33587128]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2023-06-13_17-34-03PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   33587128  
Do you want to proceed? [y|n]
y User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0/dbhome_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Patching component oracle.rdbms.util, 12.2.0.1.0...
Patching component oracle.rdbms, 12.2.0.1.0...
Patching component oracle.network.rsf, 12.2.0.1.0...
Patching component oracle.rdbms.rsf, 12.2.0.1.0...
Patching component oracle.ctx, 12.2.0.1.0...
Patching component oracle.has.common.cvu, 12.2.0.1.0...
Patching component oracle.ldap.owm, 12.2.0.1.0...
Patching component oracle.ldap.rsf, 12.2.0.1.0...
Patching component oracle.nlsrtl.rsf, 12.2.0.1.0...
Patching component oracle.oracore.rsf, 12.2.0.1.0...
Patching component oracle.oraolap, 12.2.0.1.0...
Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...
Patching component oracle.rdbms.deconfig, 12.2.0.1.0...
Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...
Patching component oracle.sdo, 12.2.0.1.0...
Patching component oracle.sdo.locator, 12.2.0.1.0...
OPatch found the word "error" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
chmod: changing permissions of ‘/u01/app/oracle/product/12.2.0/dbhome_1/bin/extjobO’: Operation not permitted
make: [iextjob] Error 1 (ignored)
Patch 33587128 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2023-06-13_17-34-03PM_1.log

补丁更新过程出现如下错误。

OPatch found the word “error” in the stderr of the make command.Please look at this stderr. You can re-run this make command.Stderr output:chmod: changing permissions of ‘/u01/app/oracle/product/12.2.0/dbhome_1/bin/extjobO’: Operation not permitted

make: [iextjob] Error 1 (ignored)

查看mos后报错可以忽略《安装Proactive Bundle / PSU Patch报错 “chmod: changing permissions of `$ORACLE_HOME/bin/extjobO’: Operation not permitted” (Doc ID 2668094.1)》

对象名无效（查看无效对象）

6、数据库加载补丁（datapatch -verbose）

启动数据库进行补丁加载
QL> startup
ORACLE instance started.
Total System Global Area 2516582400 bytes
Fixed Size		    8623832 bytes
Variable Size		  671090984 bytes
Database Buffers	 1828716544 bytes
Redo Buffers		    8151040 bytes
Database mounted.
Database opened.
SQL> select action_time, version,id,COMMENTS from dba_registry_history;

ACTION_TIME
---------------------------------------------------------------------------
VERSION 			       ID
------------------------------ ----------
COMMENTS
--------------------------------------------------------------------------------
12.2.0.1
RDBMS_12.2.0.1.0_LINUX.X64_170125   数据库内仍然为数据库的基础版本补丁未加载。

cd $ORACLE_HOME/OPatch
 OPatch]$ http://www.toutiao.com/a7245191000011391520/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Tue Jun 13 17:47:19 2023
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_16537_2023_06_13_17_47_19/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of SQL patches:
Bundle series DBRU:
  ID 220118 in the binary registry and not installed in the SQL registry
Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    33587128 (DATABASE JAN 2022 RELEASE UPDATE 12.2.0.1.220118)
Installing patches...
[oracle@ora12c 33587128]$ cd $ORACLE_HOME/OPatch
[oracle@ora12c OPatch]$ http://www.toutiao.com/a7245191000011391520/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Tue Jun 13 17:47:19 2023
Copyright (c) 2012, 2021, Oracle.  All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_16537_2023_06_13_17_47_19/sqlpatch_invocation.log
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 220118 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    33587128 (DATABASE JAN 2022 RELEASE UPDATE 12.2.0.1.220118)
Installing patches...
Patch installation complete.  Total patches installed: 1
Validating logfiles...
Patch 33587128 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33587128/24566093/33587128_apply_ORADB_2023Jun13_17_47_28.log (no errors)
SQL Patching tool complete on Tue Jun 13 17:49:03 2023
再次查看数据库信息
select patch_id,version,action,status,description,bundle_series from dba_registry_sqlpatch
SQL> /

  PATCH_ID VERSION		ACTION		STATUS			  DESCRIPTION			 BUNDLE_SERIES
---------- -------------------- --------------- ------------------------- ------------------------------ ------------------------------
  33587128 12.2.0.1		APPLY		SUCCESS 		  DATABASE JAN 2022 RELEASE UPDA DBRU
									  TE 12.2.0.1.220118
补丁加载成功

注：曾经遇到过补丁加载不成功报错如下（个例）

$ datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Fri Jun xx 10:07:10 2023
Copyright (c) 2012, 2021, Oracle. All rights reserved.
mkdir /data/app/oracle/cfgtoollogs: 13 at /data1/oradata/product/12.1.0/dbhome_1/sqlpatch/sqlpatch.pm line 802.
Please refer to MOS Note 1609718.1 and/or the invocation log
for information on how to resolve the above errors.
SQL Patching tool complete on Fri Jun xx 10:07:10 2023

查看此文件的802行sqlpatch/sqlpatch.pm line，确定是对应的logdir无法创建导致。
 802       if (!make_path($invocation_logdir)) {
 803         # Could not create directory
 804         print "Could not create invocation log directory $invocation_logdir\n";
 805         $ret = 1;
 806         goto initialize_complete;
 807       }
进行目录权限检查，发现目录的属主为root，oracle肯定无法创建。
 ls -ld /data/app/oracle
drwxr-xr-x. 3 root root 19 Mar 14 21:14 /data/app/oracle
临时解决给oracle的目录增加w权限
]# chmod o+w .
[root@xx oracle]# ls -ld .
drwxr-xrwx. 3 root root 19 Mar 14 21:14 .
再次运行datapatch命令，补丁成功加载
$ datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Fri Jun 16 10:10:52 2023
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /data/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12302_2023_06_16_10_10_52/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

7、无效对象处理

查看无效对象
select t.owner, t.object_type, t.object_name, t.status from dba_objects t where t.status = 'INVALID';
如果返回为0则没有无效对象产生，如果有返回值执行如下
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

补丁回滚

如补丁更新后数据库不正常或应用访问存在问题可能会进行补丁回滚，步骤如下

1、停止数据库

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

2、回滚补丁

$ opatch rollback -id 33587128   执行rollback命令
Oracle Interim Patch Installer version 12.2.0.1.37
Copyright (c) 2023, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.37
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2023-06-14_09-08-14AM_1.log
Patches will be rolled back in the following order: 
   33587128
The following patch(es) will be rolled back: 33587128  
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/12.2.0/dbhome_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
RollbackSession removing interim patch '33587128' from inventory
Log file location: /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2023-06-14_09-08-14AM_1.log
OPatch succeeded.

3、数据库中回滚补丁

启动数据库
此时数据库中补丁信息仍然存在
SQL> select patch_id,version,action,status,description,bundle_series from dba_registry_sqlpatch
  2  ;

  PATCH_ID VERSION		ACTION		STATUS
---------- -------------------- --------------- -------------------------
DESCRIPTION
--------------------------------------------------------------------------------
BUNDLE_SERIES
------------------------------
  33587128 12.2.0.1		APPLY		SUCCESS
DATABASE JAN 2022 RELEASE UPDATE 12.2.0.1.220118
DBRU
执行datapatch -verbose命令rollback对应的补丁。
$ datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Wed Jun 14 09:12:17 2023
Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18446_2023_06_14_09_12_18/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done

   

    对象名无效（查看无效对象）
    

   
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  Not installed in the binary registry and ID 220118 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  The following patches will be rolled back:
    33587128 (DATABASE JAN 2022 RELEASE UPDATE 12.2.0.1.220118)
  Nothing to apply

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 33587128 rollback: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33587128/24566093/33587128_rollback_ORADB_2023Jun14_09_12_23.log (no errors)
SQL Patching tool complete on Wed Jun 14 09:13:53 2023

4、查看数据库中的补丁情况

select patch_id,version,action,status,description,bundle_series from dba_registry_sqlpatch
SQL> /

  PATCH_ID VERSION		ACTION		STATUS			  DESCRIPTION			 BUNDLE_SERIES
---------- -------------------- --------------- ------------------------- ------------------------------ ------------------------------
  33587128 12.2.0.1		APPLY		SUCCESS 		  DATABASE JAN 2022 RELEASE UPDA DBRU
									  TE 12.2.0.1.220118

  33587128 12.2.0.1		ROLLBACK	SUCCESS 		  DATABASE JAN 2022 RELEASE UPDA DBRU
									  TE 12.2.0.1.220118
补丁已经成功rollback

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/33348.html

赞 (0)
小小码小小码
0 0
上一篇 2023-09-01 20:30
下一篇 2023-09-02 10:30

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注