프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.5 (64bit)


DB 환경 : Oracle Database 11.2.0.4, 12.1.0.1, 19.7.0.0


쿼리 : 오라클 패치 정보를 조회할 수 있는 뷰 DBA_REGISTRY_SQLPATCH

오라클은 패치정보를 조회할 수 있는 뷰를 제공함

11g, 12c 에서 아래 두개의 SQL로 조회가 가능함


11g 패치 정보 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL>
SET LINESIZE 400
COLUMN ACTION_TIME FORMAT A20
COLUMN ACTION FORMAT A20
COLUMN NAMESPACE FORMAT A20
COLUMN VERSION FORMAT A10
COLUMN COMMENTS FORMAT A30
COLUMN BUNDLE_SERIES FORMAT A10
 
SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME
      ,ACTION
      ,NAMESPACE
      ,VERSION
      ,ID
      ,COMMENTS
      ,BUNDLE_SERIES
FROM   DBA_REGISTRY_HISTORY
ORDER BY ACTION_TIME
/
 
ACTION_TIME         ACTION          NAMESPACE           VERSION          ID COMMENTS                BUNDLE_SER
-------------------- -------------------- -------------------- ---------- ---------- ------------------------------ ----------
20191015 09:20:15    APPLY          SERVER           11.2.0.4        0 Patchset 11.2.0.2.0        PSU


위 뷰 대신 SYS.REGISTRY$HISTORY 를 사용하기도 한다.


12c 업그레이드시 DBA_REGISTRY_HISTORY 구조가 변경되어 


DBA_REGISTRY_SQLPATCH 뷰를 사용하도록 권장하고 있음


12c 패치 정보 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL>
SET LINESIZE 400
COLUMN ACTION_TIME FORMAT A20
COLUMN ACTION FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN DESCRIPTION FORMAT A40
COLUMN VERSION FORMAT A10
COLUMN BUNDLE_SERIES FORMAT A10
 
SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME
      ,ACTION
      ,STATUS
      ,DESCRIPTION
      ,VERSION
      ,PATCH_ID
      ,BUNDLE_SERIES
FROM   DBA_REGISTRY_SQLPATCH
ORDER BY ACTION_TIME
/
 
no rows selected

패치를 하지않아서 no rows 로 나옴



11g, 12c DBA_REGISTRY_HISTORY 뷰 비교

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
11g
SQL> desc DBA_REGISTRY_HISTORY
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACTION_TIME                    TIMESTAMP(6)
 ACTION                         VARCHAR2(30)
 NAMESPACE                        VARCHAR2(30)
 VERSION                        VARCHAR2(30)
 ID                                NUMBER
 BUNDLE_SERIES                    VARCHAR2(30)
 COMMENTS                        VARCHAR2(255)
 
12c
SQL> desc DBA_REGISTRY_HISTORY
 Name                           Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACTION_TIME                   TIMESTAMP(6)
 ACTION                        VARCHAR2(30)
 NAMESPACE                       VARCHAR2(30)
 VERSION                       VARCHAR2(30)
 ID                               NUMBER
 COMMENTS                       VARCHAR2(255)

12c 에서는 DBA_REGISTRY_HISTORY뷰에서 BUNDLE_SERIES 가 없어짐



BUNDLE_SERIES 이 DBA_REGISTRY_SQLPATCH 뷰에 포함됨

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> desc DBA_REGISTRY_SQLPATCH
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
PATCH_ID                                      NOT NULL NUMBER
 PATCH_UID                                      NOT NULL NUMBER
 VERSION                                      NOT NULL VARCHAR2(20)
 FLAGS                                           VARCHAR2(10)
 ACTION                                       NOT NULL VARCHAR2(15)
 STATUS                                        VARCHAR2(15)
 INSTALL_ID                                       NUMBER
 ACTION_TIME                                      NOT NULL TIMESTAMP(6)
 DESCRIPTION                                       VARCHAR2(100)
 BUNDLE_SERIES                                       VARCHAR2(30)
 BUNDLE_ID                                       NUMBER
 BUNDLE_DATA                                       XMLTYPE
 PATCH_DESCRIPTOR                                   XMLTYPE
 PATCH_DIRECTORY                                   BLOB
 LOGFILE                                       VARCHAR2(500)



19c 에서는 DBA_REGISTRY_SQLPATCH 뷰가 12c와 차이가 많이남

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> desc DBA_REGISTRY_SQLPATCH
 Name                               Null?    Type
 ----------------------------------------------------- -------- 
 INSTALL_ID                           NOT NULL NUMBER
 PATCH_ID                           NOT NULL NUMBER
 PATCH_UID                           NOT NULL NUMBER
 PATCH_TYPE                           NOT NULL VARCHAR2(10)
 ACTION                            NOT NULL VARCHAR2(15)
 STATUS                            NOT NULL VARCHAR2(25)
 ACTION_TIME                           NOT NULL TIMESTAMP(6)
 DESCRIPTION                            VARCHAR2(100)
 LOGFILE                           NOT NULL VARCHAR2(500)
 RU_LOGFILE                            VARCHAR2(500)
 FLAGS                                VARCHAR2(10)
 PATCH_DESCRIPTOR                        XMLTYPE
 PATCH_DIRECTORY                        BLOB
 SOURCE_VERSION                         VARCHAR2(15)
 SOURCE_BUILD_DESCRIPTION                    VARCHAR2(80)
 SOURCE_BUILD_TIMESTAMP                     TIMESTAMP(6)
 TARGET_VERSION                         VARCHAR2(15)
 TARGET_BUILD_DESCRIPTION                    VARCHAR2(80)
 TARGET_BUILD_TIMESTAMP                     TIMESTAMP(6)



19c 용 DBA_REGISTRY_SQLPATCH 조회 쿼리

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL>
SET LINESIZE 400
COLUMN ACTION_TIME FORMAT A20
COLUMN ACTION FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN DESCRIPTION FORMAT A60
COLUMN VERSION FORMAT A10
SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME
,ACTION
,STATUS
,DESCRIPTION
,SOURCE_VERSION
,TARGET_VERSION
,PATCH_ID
FROM DBA_REGISTRY_SQLPATCH
ORDER BY ACTION_TIME
/
 
ACTION_TIME         ACTION    STATUS       DESCRIPTION                            SOURCE_VERSION    TARGET_VERSION      PATCH_ID
-------------------- ---------- ---------- ------------------------------------------------------------ --------------- --------------- ----------
20200602 17:09:32    APPLY    SUCCESS    OJVM RELEASE UPDATE19.7.0.0.200414 (30805684)        19.1.0.0.0    19.1.0.0.0      30805684
20200602 17:09:32    APPLY    SUCCESS    Database Release Update : 19.7.0.0.200414 (30869156)     19.1.0.0.0    19.7.0.0.0      30869156
20200602 23:52:59    ROLLBACK    SUCCESS    OJVM RELEASE UPDATE19.7.0.0.200414 (30805684)        19.3.0.0.0    19.3.0.0.0      30805684
20200602 23:52:59    ROLLBACK    SUCCESS    Database Release Update : 19.7.0.0.200414 (30869156)     19.7.0.0.0    19.3.0.0.0      30869156



참조 : http://www.koreaoug.org/administration/2300

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_REGISTRY_SQLPATCH.html#GUID-F6D2BC12-606C-41FE-B9E8-F3702CD32F89

https://docs.oracle.com/database/121/REFRN/GUID-F6D2BC12-606C-41FE-B9E8-F3702CD32F89.htm#REFRN-GUID-F6D2BC12-606C-41FE-B9E8-F3702CD32F89