1. 패치 작업 수행
패치 작업 자체는 non-CDB와 완벽히 동일하다.
1.1. Patch 파일 unzip
|
oracle@orcl : CDB $ROOT:/ home / oracle / patch_files $ ls -al total 1461912 drwxr-xr-x . 2 oracle dba 87 Jun 21 22 : 21 . drwx------ . 9 oracle dba 4096 Jun 21 22 : 21 .. - rw-r--r-- . 1 oracle dba 1375008229 Jun 21 22 : 20 p32545013_190000_Linux-x86-64 . zip - rw-r--r-- . 1 oracle dba 121981878 Jun 21 22 : 19 p6880880_190000_Linux-x86-64 . zip oracle@orcl : CDB $ROOT:/ home / oracle / patch_files $ unzip p6880880_190000_Linux-x86-64.zip Archive : p6880880_190000_Linux-x86-64 . zip creating : OPatch / inflating : OPatch / README . txt inflating : OPatch / datapatch inflating : OPatch / emdpatch . pl ( 하략 )
oracle@orcl : CDB $ROOT:/ home / oracle / patch_files $ ls OPatch p32545013_190000_Linux-x86-64 . zip p6880880_190000_Linux-x86-64 . zip oracle@orcl : CDB $ROOT:/ home / oracle / patch_files $ unzip p32545013_190000_Linux-x86-64.zip Archive : p32545013_190000_Linux-x86-64 . zip creating : 32545013 / creating : 32545013 / etc / creating : 32545013 / etc / config / ( 하략 ) |
1.2. Opatch 버전 Upgrade
|
oracle@orcl :CDB $ROOT :/ home / oracle / patch_files $ ls -al total 1462728 drwxr - xr - x. 4 oracle dba 140 Jun 21 22 :22 . drwx ------. 9 oracle dba 4096 Jun 21 22:21 .. drwxr - xr - x. 5 oracle dba 81 Apr 16 07 :40 32545013 drwxr - x ---. 15 oracle dba 4096 Apr 20 05:45 OPatch - rw - r --r--. 1 oracle dba 1375008229 Jun 21 22:20 p32545013_190000_Linux-x86-64.zip - rw - r --r--. 1 oracle dba 121981878 Jun 21 22:19 p6880880_190000_Linux-x86-64.zip - rw - rw - r --. 1 oracle dba 828833 Apr 20 21:51 PatchSearch.xml oracle@orcl :CDB $ROOT :/ home / oracle / patch_files $ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch_old_bak |
1.3. Opatch 버전 확인
|
oracle@orcl :CDB $ROOT :/ home / oracle / patch_files $ mv OPatch $ORACLE_HOME/ oracle@orcl : CDB $ROOT:/ home / oracle / patch_files $ opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.25 Copyright ( c ) 2021 , Oracle Corporation . All rights reserved .
Oracle Home : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 Central Inventory : / u01 / app / oraInventory from : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / oraInst . loc OPatch version : 12.2.0.1.25 OUI version : 12.2.0.7.0 Log file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / opatch2021-06-21_22-25-36PM_1 . log
Lsinventory Output file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / lsinv / lsinventory2021-06-21_22-25-36PM . txt -------------------------------------------------------------------------------- Local Machine Information :: Hostname : localhost ARU platform id : 226 ARU platform description :: Linux x86-64
Installed Top-level Products ( 1 ):
Oracle Database 19 c 19.0.0.0.0 There are 1 products installed in this Oracle Home .
Interim patches ( 2 ) :
Patch 29585399 : applied on Thu Apr 18 03 : 21 : 33 EDT 2019 Unique Patch ID : 22840393 Patch description : "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)" Created on 9 Apr 2019 , 19 : 12 : 47 hrs PST8PDT Bugs fixed : 27222128 , 27572040 , 27604329 , 27760043 , 27877830 , 28302580 , 28470673 28621543 , 28642469 , 28699321 , 28710663 , 28755846 , 28772816 , 28785321 28800508 , 28808652 , 28815557 , 28847541 , 28847572 , 28870496 , 28871040 28874416 , 28877252 , 28881191 , 28881848 , 28888083 , 28911140 , 28925250 ... (중략) ... --------------------------------------------------------------------------------
OPatch succeeded . |
1.4. 백업
1.4.1. ORACLE_HOME 백업
|
oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 $ cd $ORACLE_HOME oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 / dbhome_1 $ cd .. oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 $ ls -al total 4 drwxr-xr-x . 3 oracle dba 22 Jun 8 06 : 01 . drwxr-xr-x . 3 oracle dba 22 Jun 8 06 : 01 .. drwxr-xr-x . 71 oracle dba 4096 Jun 21 22 : 25 dbhome_1 oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 $ tar -pcvf ./oracle_home_bkup.tar dbhome_1 dbhome_1 / dbhome_1 / LINUX . X64_193000_db_home . zip dbhome_1 / drdaas / ( 하략 )
oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 $ ls -al total 10522584 drwxr-xr-x . 3 oracle dba 50 Jun 21 22 : 37 . drwxr-xr-x . 3 oracle dba 22 Jun 8 06 : 01 .. drwxr-xr-x . 71 oracle dba 4096 Jun 21 22 : 25 dbhome_1 - rw-r--r-- . 1 oracle dba 10775121920 Jun 21 22 : 41 oracle_home_bkup . tar |
1.4.2. oraInvetory 백업
|
oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 $ cat /etc/oraInst.loc inventory_loc =/ u01 / app / oraInventory inst_group = dba oracle@orcl : CDB $ROOT:/ u01 / app / oracle / product / 11.2.0.4 $ cd /u01/app/oraInventory oracle@orcl : CDB $ROOT:/ u01 / app / oraInventory $ cd .. oracle@orcl : CDB $ROOT:/ u01 / app $ ls -al total 0 drwxr-xr-x . 4 oracle dba 40 Jun 8 06 : 10 . drwxr-xr-x . 3 oracle dba 17 Jun 8 06 : 01 .. drwxr-xr-x . 9 oracle dba 112 Jun 8 12 : 29 oracle drwxrwx--- . 4 oracle dba 78 Jun 21 22 : 47 oraInventory oracle@orcl : CDB $ROOT:/ u01 / app $ tar -pcvf./oracle_Inv_bkup.tar oraInventory oraInventory / oraInventory / logs / ( 하략 ) oracle@orcl : CDB $ROOT:/ u01 / app $ ls oracle oracle_Inv_bkup . tar oraInventory |
1.4.3. Opatch Prerequisite 수행
|
oracle@orcl : CDB $ROOT:/ u01 / app $ cd ~/patch_files/32545013/ oracle@orcl : CDB $ROOT:/ home / oracle / patch_files / 32545013 $ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.25 Copyright ( c ) 2021 , Oracle Corporation . All rights reserved .
PREREQ session
Oracle Home : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 Central Inventory : / u01 / app / oraInventory from : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / oraInst . loc OPatch version : 12.2.0.1.25 OUI version : 12.2.0.7.0 Log file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / opatch2021-06-21_22-50-34PM_1 . log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed .
OPatch succeeded . |
1.4.4. listener 및 instance 종료
|
oracle@orcl :CDB $ROOT :/ home / oracle / patch_files / 32545013 $ lsnrctl stop
LSNRCTL for Linux : Version 19.0.0.0.0 - Production on 21 - JUN - 2021 23 :13:01
Copyright ( c ) 1991 , 2019 , Oracle. All rights reserved.
Connecting to ( DESCRIPTION =( ADDRESS =( PROTOCOL = TCP )( HOST = orcl )( PORT = 11521 ))) The command completed successfully
oracle@orcl :CDB $ROOT :/ home / oracle / patch_files / 32545013 $ sqlplus / as sysdba
SQL * Plus : Release 19.0.0.0.0 - Production on Mon Jun 21 23 :10:50 2021 Version 19.3.0.0.0
Copyright ( c ) 1982 , 2019 , Oracle. All rights reserved.
Connected to : Oracle Database 19 c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL > show con_name
CON_NAME ------------------------------ CDB$ROOT
SQL > shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SQL > exit Disconnected from Oracle Database 19 c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 oracle@orcl :CDB $ROOT :/ home / oracle / patch_files / 32545013 $ opatch apply Oracle Interim Patch Installer version 12.2.0.1.25 Copyright ( c ) 2021 , Oracle Corporation. All rights reserved. |
1.4.5. 엔진 패치 적용
|
oracle@orcl :CDB $ROOT :/ home / oracle / patch_files / 32545013 $ opatch apply Oracle Home : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 Central Inventory : / u01 / app / oraInventory from : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / oraInst.loc OPatch version : 12.2.0.1.25 OUI version : 12.2.0.7.0 Log file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / opatch2021 - 06 - 21 _23 - 12 - 46 PM_1.log
Verifying environment and performing prerequisite checks... OPatch continues with these patches : 32545013
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/11.2.0.4/dbhome_1' )
Is the local system ready for patching? [ y|n ] y User Responded with : Y Backing up files... Applying interim patch '32545013' to OH '/u01/app/oracle/product/11.2.0.4/dbhome_1' ApplySession : Optional component( s ) [ oracle.network.gsm , 19.0.0.0.0 ] , [ oracle.rdbms.ic , 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2 , 19.0.0.0.0 ] , [ oracle.tfa , 19.0.0.0.0 ] , [ oracle. options .olap.api , 19.0.0.0.0 ] , [ oracle.ons.cclient , 19.0.0.0.0 ] , [ oracle. options .olap , 19.0.0.0.0 ] , [ oracle.network.cman , 19.0.0.0.0 ] , [ oracle.oid.client , 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat , 19.0.0.0.0 ] , [ oracle.net.cman , 19.0.0.0.0 ] , [ oracle.xdk.companion , 19.0.0.0.0 ] , [ oracle.jdk , 1.8.0.191.0 ] not present in the Oracle Home or a higher version is found .
( 중략 )
Patch 32545013 successfully applied. Sub - set patch [ 29517242 ] has become inactive due to the application of a super - set patch [ 32545013 ] . Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / opatch2021 - 06 - 21 _23 - 12 - 46 PM_1.log
OPatch succeeded. |
1.4.6. 데이터 패치(Sql Patch) 적용
|
oracle@orcl :CDB $ROOT :/ u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / OPatch $ cd $ORACLE_HOME/OPatch oracle@orcl :CDB $ROOT :/ u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / OPatch $ ./datapatch -verbose SQL Patching tool version 19.11.0.0.0 Production on Mon Jun 21 23 :32:18 2021 Copyright ( c ) 2012 , 2021 , Oracle. All rights reserved.
Log file for this invocation : / u01 / app / oracle / cfgtoollogs / sqlpatch / sqlpatch_18490_2021_06_21_23_32_18 / sqlpatch_invocation.log
Connecting to database ...OK Gathering database info...done
Note : Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state , no patches will be applied to closed PDBs. Please refer to Note : Datapatch : Database 12 c Post Patch SQL Automation ( Doc ID 1585822.1 )
Warning : PDB PDB2 is in mode MOUNTED and will be skipped. Bootstrapping registry and package to current versions...done Determining current state ...done
Current state of interim SQL patches : No interim patches found
Current state of release update SQL patches : Binary registry : 19.11.0.0.0 Release_Update 210413004009 : Installed PDB CDB$ROOT : Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 08 - JUN - 21 12.53.17.002846 PM PDB PDB$SEED : Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 08 - JUN - 21 12.53.18.070545 PM PDB PDB1 : Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 08 - JUN - 21 12.53.18.070545 PM
Adding patches to installation queue and performing prereq checks...done Installation queue : For the following PDBs : CDB$ROOT PDB$SEED PDB1 No interim patches need to be rolled back Patch 32545013 ( Database Release Update : 19.11.0.0.210420 ( 32545013 )): Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.11.0.0.0 Release_Update 210413004009 No interim patches need to be applied
Installing patches... |
패치 내역을 보면 Trouble Shooting 실습을 위해 MOUNT 상태로 패치를 진행했기 때문에,
PDB2가 패치 적용 리스트에서 빠져있고 Warning Message가 출력되고 있다.
|
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED |
1.5. 패치 적용 확인
1.5.1. 엔진(Opatch) 패치 적용 확인
non-CDB와 동일하게 opatch lsinventory 명령어로 확인 가능하다.
|
oracle@orcl : CDB $ROOT:/ home / oracle $ opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.25 Copyright ( c ) 2021 , Oracle Corporation . All rights reserved .
Oracle Home : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 Central Inventory : / u01 / app / oraInventory from : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / oraInst . loc OPatch version : 12.2.0.1.25 OUI version : 12.2.0.7.0 Log file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / opatch2021-06-22_02-05-04AM_1 . log
Lsinventory Output file location : / u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / cfgtoollogs / opatch / lsinv / lsinventory2021-06-22_02-05-04AM . txt -------------------------------------------------------------------------------- Local Machine Information :: Hostname : localhost ARU platform id : 226 ARU platform description :: Linux x86-64
Installed Top-level Products ( 1 ):
Oracle Database 19 c 19.0.0.0.0 There are 1 products installed in this Oracle Home .
Interim patches ( 2 ) :
Patch 32545013 : applied on Mon Jun 21 23 : 17 : 46 EDT 2021 Unique Patch ID : 24175065 Patch description : "Database Release Update : 19.11.0.0.210420 (32545013)" Created on 16 Apr 2021 , 07 : 40 : 05 hrs UTC Bugs fixed : 7391838 , ... (하략) |
1.5.2. 데이터 패치(SQL Patch) 적용 확인
dba_registry_history, cdb_registry_history, dba_registry_sqlpatch 딕셔너리로 확인 가능하다 .
dba_... 딕셔너리는 현재 접속한 컨테이너의 정보를 출력하고 ,
cdb … 는 CDB$ROOT 에 접속한 상태에서 모든 컨테이너의 정보만 출력하지만 , 다른 cdb_... 뷰와 같이 OPEN 상태의 컨테이너만 확인 가능하다 .
예외사항으로 cdb_registry_sqlpatch 뷰는 자신이 접속한 PDB 의 정보만 출력한다 .
|
SQL > set lin 250 col description for a60 col comments for a80 col source_version for a15 col target_version for a15 col action for a10 col status for a20 col action_time for a30 col namespace for a15 col version for a15 col name for a10 col open_mode for a10 select action_time , action , namespace , version , comments from dba_registry_history ;
ACTION_TIME ACTION NAMESPACE VERSION COMMENTS ------------------------------ ---------- --------------- --------------- -------------------------------------------------------------------------------- BOOTSTRAP DATAPATCH 19 RDBMS_19.11 .0.0.0 DBRU_LINUX.X64_210412 20210608 12 :52:48 .922384 RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0 : Release_Update - 190410122720 20210621 23 :36:25 .669163 RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.11.0.0.0 : Release_Update - 210413004009
SQL > select p. name , p.open_mode , h.action_time , h. action , h. namespace , h. version , h. comments from cdb_registry_history h , v$pdbs p where h. con_id(+)= p.con_Id order by p.con_id , action_time ;
NAME OPEN_MODE ACTION_TIME ACTION NAMESPACE VERSION COMMENTS ---------- ---------- ------------------------------ ---------- --------------- --------------- -------------------------------------------------------------------------------- PDB$SEED READ ONLY PDB1 READ WRITE 20210608 12 :53:09 .349256 RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0 : Release_Update - 190410122720 PDB1 READ WRITE 20210621 23 :40:37 .950066 RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.11.0.0.0 : Release_Update - 210413004009 PDB1 READ WRITE BOOTSTRAP DATAPATCH 19 RDBMS_19.11 .0.0.0 DBRU_LINUX.X64_210412 PDB2 MOUNTED
SQL > select patch_id , action , status , action_time , description , source_version , target_version from dba_registry_sqlpatch order by install_id ;
PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION SOURCE_VERSION TARGET_VERSION ---------- ---------- -------------------- ------------------------------ ------------------------------------------------------------ --------------- --------------- 29517242 APPLY SUCCESS 20210608 12 :53:17 .002846 Database Release Update : 19.3.0.0.190416 ( 29517242 ) 19.1.0.0.0 19.3.0.0.0 32545013 APPLY SUCCESS 20210621 23 :43:10 .980272 Database Release Update : 19.11.0.0.210420 ( 32545013 ) 19.3.0.0.0 19.11.0.0.0 |
1.5.3. PDB$SEED의 Patch 현황 확인
PDB$SEED 의 경우 제대로 패치가 적용되었음에도 불구하고 cdb_registry_history 딕셔너리를 통해 확인 시 버전적용 여부가 확인되지 않으며 ,
아래와 같이 강제로 READ WRITE OPEN 하여도 동일하다 .
|
SQL > alter pluggable database PDB$SEED open read write force ;
Pluggable database altered.
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ WRITE NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED
SQL > set lin 250 col description for a60 col comments for a80 col source_version for a15 col target_version for a15 col action for a10 col status for a20 col action_time for a30 col namespace for a15 col version for a15 col name for a10 select p. name , p.open_mode , h.action_time , h. action , h. namespace , h. version , h. comments from cdb_registry_history h , v$pdbs p where h. con_id(+)= p.con_Id order by p.con_id , action_time ;
NAME OPEN_MODE ACTION_TIME ACTION NAMESPACE VERSION COMMENTS ---------- ---------- ------------------------------ ---------- --------------- --------------- -------------------------------------------------------------------------------- PDB$SEED READ WRITE PDB1 READ WRITE 20210608 12 :53:09 .349256 RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0 : Release_Update - 190410122720 PDB1 READ WRITE 20210621 23 :40:37 .950066 RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.11.0.0.0 : Release_Update - 210413004009 PDB1 READ WRITE BOOTSTRAP DATAPATCH 19 RDBMS_19.11 .0.0.0 DBRU_LINUX.X64_210412 PDB2 READ WRITE
5 rows selected. |
PDB 에 직접 접속하여 dba_... 딕셔너리를 통해서 확인하면 정상적으로 출력되는 것을 알 수 있다 .
|
SQL > alter session set container = PDB$SEED ;
Session altered.
SQL > show con_name
CON_NAME ------------------------------ PDB$SEED
SQL > select action_time , action , namespace , version , comments from dba_registry_history ;
ACTION_TIME ACTION NAMESPACE VERSION COMMENTS ------------------------------ ---------- --------------- --------------- -------------------------------------------------------------------------------- BOOTSTRAP DATAPATCH 19 RDBMS_19.11 .0.0.0 DBRU_LINUX.X64_210412 20210608 12 :53:09 .349256 RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0 : Release_Update - 190410122720 20210621 23 :41:23 .866719 RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.11.0.0.0 : Release_Update - 210413004009
3 rows selected.
SQL > select patch_id , action , status , action_time , description , source_version , target_version from dba_registry_sqlpatch order by install_id ;
PATCH_ID ACTION STATUS ACTION_TIME DESCRIPTION SOURCE_VERSION TARGET_VERSION ---------- ---------- -------------------- ------------------------------ ------------------------------------------------------------ --------------- --------------- 29517242 APPLY SUCCESS 20210608 12 :53:18 .070545 Database Release Update : 19.3.0.0.190416 ( 29517242 ) 19.1.0.0.0 19.3.0.0.0 32545013 APPLY SUCCESS 20210621 23 :43:18 .049111 Database Release Update : 19.11.0.0.210420 ( 32545013 ) 19.3.0.0.0 19.11.0.0.0
2 rows selected.
SQL > alter pluggable database pdb$seed open read only force ;
Pluggable database altered.
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO |
2. 비정상 케이스에 대한 작업
2.1. 패치 실패 PDB에 대한 패치 적용
2.1.1. 패치 실패/누락 PDB OPEN
패치 도중 일부러 CLOSE 상태도 둔 PDB2 의 경우 OPEN 시 , 에러와 함께 RESTRICT 모드로 OPEN 된다 .
|
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED SQL > alter pluggable database pdb2 open ;
Warning : PDB altered with errors.
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE YES |
2.1.2. alert 로그 확인
alert_{CDB_SID}.log 를 확인해 보면 아래와 같이 PDB2에 대한 로그가 생성된 것을 알 수 있다.
|
PDB2 ( 4 ):*************************************************************** PDB2 ( 4 ): WARNING : Pluggable Database PDB2 with pdb id - 4 is PDB2 ( 4 ): altered with errors or warnings . Please look into PDB2 ( 4 ): PDB_PLUG_IN_VIOLATIONS view for more details . PDB2 ( 4 ):*************************************************************** |
2.1.3. PDB_PLUG_IN_VIOLATIONS 뷰 확인
PDB_PLUG_IN_VIOLATIONS 뷰를 확인해 보면 , 아래와 같이 datapatch 버전이 맞지 않으니 datapatch 를 적용하라는 ACTION Guidance 를 확인 할 수 있다.
|
SQL > col message for a50 col action for a50 col error for a30 SELECT type , message , action FROM pdb_plug_in_violations WHERE name = 'PDB3' ;
TYPE MESSAGE ACTION ------------------ -------------------------------------------------- -------------------------------------------------- ERROR '19.11.0.0.0 Release_Update 2104130040' is install Call datapatch to install in the PDB or the CDB ed in the CDB but '19.3.0.0.0 Release_Update 19041 01227' is installed in the PDB |
2.1.4. Datapatch PDB 별도 적용
OPatch 디렉토리에서 datapatch -pdbs {PDB_NAME} 를 실행하여 적용한다 .
※ datapatch 명령어만 사용해도 무관하다 . 현재 OPEN 상태인 모든 (CDB 포함 ) DB 에 적용하지만 , 이미 patch 가 적용된 DB 에는 영향이 없다 .
|
oracle@orcl :CDB $ROOT :/ home / oracle $ cd $ORACLE_HOME / OPatch oracle@orcl :CDB $ROOT :/ u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / OPatch $ . / datapatch - pdbs PDB2 SQL Patching tool version 19.11.0.0.0 Production on Tue Jun 22 01 :20:25 2021 Copyright ( c ) 2012 , 2021 , Oracle. All rights reserved.
Log file for this invocation : / u01 / app / oracle / cfgtoollogs / sqlpatch / sqlpatch_25511_2021_06_22_01_20_25 / sqlpatch_invocation.log
Connecting to database ...OK Gathering database info...done
Note : Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state , no patches will be applied to closed PDBs. Please refer to Note : Datapatch : Database 12 c Post Patch SQL Automation ( Doc ID 1585822.1 )
Bootstrapping registry and package to current versions...done Determining current state ...done
Current state of interim SQL patches : No interim patches found
Current state of release update SQL patches : Binary registry : 19.11.0.0.0 Release_Update 210413004009 : Installed PDB PDB2 : Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 08 - JUN - 21 12.53.18.070545 PM
Adding patches to installation queue and performing prereq checks...done Installation queue : For the following PDBs : PDB2 No interim patches need to be rolled back Patch 32545013 ( Database Release Update : 19.11.0.0.210420 ( 32545013 )): Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.11.0.0.0 Release_Update 210413004009 No interim patches need to be applied
Installing patches... Patch installation complete. Total patches installed : 1
Validating logfiles...done Patch 32545013 apply ( pdb PDB2 ): SUCCESS logfile : / u01 / app / oracle / cfgtoollogs / sqlpatch / 32545013 / 24175065 / 32545013 _apply_ORCL_PDB2_2021Jun22_01_20_59. log ( no errors ) SQL Patching tool complete on Tue Jun 22 01 :23:50 2021 oracle@orcl :CDB $ROOT :/ u01 / app / oracle / product / 11.2.0.4 / dbhome_1 / OPatch $ |
2.1.5. 패치 정상 적용 확인
|
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE YES SQL > alter pluggable database pdb2 close immediate ;
Pluggable database altered.
SQL > alter pluggable database pdb2 open ;
Pluggable database altered.
SQL > show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO
SQL > select p. name , p.open_mode , h.action_time , h. action , h. namespace , h. version , h. comments from cdb_registry_history h , v$pdbs p where h. con_id(+)= p.con_Id order by p.con_id , action_time ;
NAME OPEN_MODE ACTION_TIME ACTION NAMESPACE VERSION COMMENTS ---------- ---------- ------------------------------ ---------- --------------- --------------- -------------------------------------------------------------------------------- PDB$SEED READ ONLY PDB1 READ WRITE 20210608 12 :53:09 .349256 RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0 : Release_Update - 190410122720 PDB1 READ WRITE 20210621 23 :40:37 .950066 RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.11.0.0.0 : Release_Update - 210413004009 PDB1 READ WRITE BOOTSTRAP DATAPATCH 19 RDBMS_19.11 .0.0.0 DBRU_LINUX.X64_210412 PDB2 READ WRITE 20210608 12 :53:09 .349256 RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0 : Release_Update - 190410122720 PDB2 READ WRITE 20210622 01 :22:40 .340730 RU_APPLY SERVER 19.0.0.0.0 Patch applied from 19.3.0.0.0 to 19.11.0.0.0 : Release_Update - 210413004009 PDB2 READ WRITE BOOTSTRAP DATAPATCH 19 RDBMS_19.11 .0.0.0 DBRU_LINUX.X64_210412
7 rows selected. |
2.2. 다른 버전의 PDB Plug 및 OPEN
이전 버전 당시 Unplug 했던 PDB3을 Plug 및 OPEN 하는 과정이다.
※ 상세 내용은 Document 1935365.1 (oracle.com) 를 참고.
2.2.1. Violation 검증 및 에러메시지 확인
dbms_pdb.check_plug_compatibility 패키지를 사용하여 Violation 검증을 수행한다.
해당 패키지를 수행 할 시, PDB Unplug를 하면서 생성되는 .xml 파일을 이용하므로 경로를 정확히 입력한다.
패키지 수행 후, PDB2 작업 때와 같이 PDB_PLUG_IN_VIOLATION을 확인해 보면 아래와 같이 출력되는 것을 알 수 있다.
|
SQL > BEGIN IF dbms_pdb. check_plug_compatibility( '/home/oracle/pdb3.xml' ) THEN dbms_output. put_line( 'no violations found' ); ELSE dbms_output. put_line( 'violations found' ); END IF ; END ; / violations found
PL / SQL procedure successfully completed.
SQL > col message for a50 col action for a50 col error for a30 SELECT type , message , action FROM pdb_plug_in_violations WHERE name = 'PDB3' ;
TYPE MESSAGE ACTION ------------------ -------------------------------------------------- -------------------------------------------------- ERROR '19.11.0.0.0 Release_Update 2104130040' is install Call datapatch to install in the PDB or the CDB ed in the CDB but '19.3.0.0.0 Release_Update 19041 01227' is installed in the PDB |
이전 버전 상태인 PDB3 을 Plug 및 Restricted로 OPEN 한다.
|
SQL > create pluggable database pdb3 using '/home/oracle/pdb3.xml' nocopy ;
Pluggable database created.
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB3 MOUNTED
SQL > alter pluggable database pdb3 open restricted ;
Warning : PDB altered with errors.
SQL > show pdbs ;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB3 READ WRITE YES |
이후 , 2.1. 패치 실패 PDB 에 대한 패치 적용 과 동일하게 데이터 패치를 적용하면 정상 사용 가능하게 된다.
※ PDB Plug의 경우, 위의 경우와 같이 Datafile이 모두 정상적으로 원래 위치에 있는 경우 "NOCOPY" 옵션이 사용 가능하며, 그 외의 경우에는 다양한 옵션을 사용해야 한다.
상세 내용은 Plugging In an Unplugged PDB (oracle.com) 을 참고한다.
