ORACLE-12C补丁升级以及报错处理

#使用ROOT用户：
上传补丁包：使用远程工具拷贝进去  p20448066_121020_Linux-x86-64.zip

> cd /out
> unzip p20448066_121020_Linux-x86-64.zip
> chown -R oracle:oinstall 20448066

#切换到数据库用户

> su - oracle

>sqlplus / as sysdba

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

> lsnrctl status   #查看监听是否开启
> lsnrctl stop

> ps -ef|grep smon
#还有相关与Oracle的进程，则需要 kill -9 pid   #没有相关进程则不需要

> cd /out/20448066
> $ORACLE_HOME/OPatch/opatch apply

#这时出现报错：
Oracle Home        : /out/oracle/12.2.0
Central Inventory  : /out/oracle/oraInventory
    from           : /out/oracle/12.2.0/oraInst.loc
Unable to lock Central Inventory.  OPatch will attempt to re-lock.

> cd $ORACLE_HOME
> vi oraInst.loc
inventory_loc=/out/oracle/oraInventory  #-->/out/oracle目录里没有oraInventory    #修改路径：inventory_loc=/out/oraInventory
inst_group=oinstall

> cd /out/20448066
> $ORACLE_HOME/OPatch/opatch apply

List of Homes on this system:

  Home name= OraDB12Home1, Location= "/out/oracle/11.2.0"   #这里出现/out/oracle/11.2.0
OPatchSession cannot load inventory for the given Oracle Home /out/oracle/12.2.0. Possible causes are:    #这里出现/out/oracle/12.2.0 ,
                                                                                                            #这样看起来就知道问题出现在哪里了，版本不一致,需要修改为12.2.0
   No read or write permission to ORACLE_HOME/.patch_storage    #这里虽然表明权限不足，不过得认真仔细看全文，
   Central Inventory is locked by another OUI instance
   No read permission to Central Inventory
   The lock file exists in ORACLE_HOME/.patch_storage
   The Oracle Home does not exist in Central Inventory

UtilSession failed: RawInventory gets null OracleHomeInfo
Log file location: /iems_sit_app/oraiems/12.2.0/cfgtoollogs/opatch/opatch2022-07-11_09-35-34AM_1.log

OPatch failed with error code 73

#这时需要进入修改配置文件

> cd /out/oraInventory/ContentsXML
> vi  inventory.xml

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2014, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>12.1.0.2.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDB12Home1" LOC="/out/oracle/11.2.0" TYPE="O" IDX="1"/>       #将"/out/oracle/11.2.0"修改为："/out/oracle/12.2.0"
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

> cd /out/20448066
> $ORACLE_HOME/OPatch/opatch apply

Patch 20448066 successfully applied.
OPatch Session completed with warnings.

> lsnrctl start 实例名  -->这里指的是数据库的实例名

LSNRCTL for Linux: Version 12.2.0.2.0 - Production on 12-JUL-2022 15:10:05

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     oracle
Version                   TNSLSNR for Linux: Version 12.2.0.2.0 - Production
Start Date                12-JUL-2022 13:54:22
Uptime                    0 days 1 hr. 15 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /out/oracle/12.2.0/network/admin/listener.ora
Listener Log File         /out/oracle/diag/tnslsnr/ora123/iems_sit/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0RDIEMSSIT)))
Services Summary...
Service "ORACLE" has 1 instance(s).
  Instance "ORACLE", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

> sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area 1.0737E+10 bytes
Fixed Size                  7654304 bytes
Variable Size            2147484768 bytes
Database Buffers         8556380160 bytes
Redo Buffers               25899008 bytes
Database mounted.
Database opened.

#数据库配置及用户授权：
SQL>alter database archivelog;
ORA-01126: 数据库必须已装载到此实例并且不在任何实例中打开,这个报错就需要执行下面停库开启mount状态
SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>archive log list;
SQL>alter database open;

SQL>alter database add supplemental log data;
SQL>alter database force logging;
SQL>alter system set enable_goldengate_replication=true;
SQL>create user c##test identified by oggmsadm;
SQL>exec dbms_goldengate_auth.grant_admin_privilege (grantee => 'c##test', privilege_type => 'CAPTURE', grant_select_privileges => TRUE, do_grants => TRUE, container => 'ALL' );
PL/SQL procedure successfully completed.

SQL>grant dba,connect,create session to c##test;   #PDB状态，可以添加CONTAINER=ALL;
Grant succeeded.

SQL>alter system set streams_pool_size=2G scope=spfile;
System altered.

---install UTL_SPADV for analyzing LogMiner processes
SQL> @$ORACLE_HOME/rdbms/admin/utlspadv.sql  

>>报错ORA-00942: table or view does not exist   先不用管

3:版本校验

 3.1：如果是12c或者19c，有用容器，则需要增加一个同义词，给平台自动校验版本

   create table ogg_pdbs as select con_id, name from v$pdbs;
   grant select on ogg_pdbs to public;
   create public synonym ogg_pdbs for ogg_pdbs;

 3.2：如果是12c或者19c，没有用容器，则建一张表给平台校验

  create table ogg_pdbs (con_id int,name varchar2(16));
  insert into ogg_pdbs (con_id,name) values (3,'pdb');

#重启数据库:
ORA-00821: Specified value of sga_target 8192M is too small, needs to be at least 9232M
sga_target 8192M的指定值太小，需要至少为9232M
alter system set sga_target=10240M scope=spfile;

ORA-01103: database name 'oracle_io' in control file is not 'oracle'

cd /out/oracle/19/dbs/initora.ora
#修改实例名才能启动库
SQL>startup