pat98's always & forever

- ASM 환경인데 아래와 같이 file system 에 잘못 만들었을 때..당황하지 말고 move 하자..단 DML 작업 있을 시 감안하여 작업

SQL> create tablepsace TEST datafile '/test/test01.dbf' size 100M;

TABLESPACE_NAME      FILE_NAME                                           TOT_KB      FREE_KB USAGE_P
-------------------- --------------------------------------------- ------------ ------------ -------
TEST                 /test/test01.dbf                                   102,400      101,376     1.0
 
SQL> alter database move datafile '/test/test01.dbf' to '+DATAC1';

TABLESPACE_NAME      FILE_NAME                                           TOT_KB      FREE_KB USAGE_P
-------------------- --------------------------------------------- ------------ ------------ -------
TEST                 +DATAC1/GTEXA/DATAFILE/test.281.1163090277         102,400      101,376     1.0

19c Patch 작업후에 rman 접속할 때 아래와 같은 메세지 발생할 때 조치!!

[gtexa1]oracle@gtexadb01:/home/oracle$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 7 16:21:03 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.20.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.20.00.00 in TARGET database is not current

--------------------------------------------------------------------------------------------------------------------------------
PL/SQL package SYS.DBMS_BACKUP_RESTORE Version is Not Current (Doc ID 2741760.1)

- 해당 Package Compile 수행

@?/rdbms/admin/dbmsrman.sql
@?/rdbms/admin/dbmsbkrs.sql
@?/rdbms/admin/prvtrmns.plb
@?/rdbms/admin/prvtbkrs.plb

완료후 재접속 정상!!

[gtexa1]oracle@gtexadb01:/home/oracle$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 7 16:59:45 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GTEXA (DBID=2412954275)

Oracle Single 환경에 OCW Patch 를 적용해야 할까?

기본적으로 19.3 버전을  초기 설치하게 되면 non RAC / non ASM 환경임에도 
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399) 해당 패치가 같이 설치가 된다.

이후 Patch 관리를 해줘야 할 떄, Database RU 외에 OCW (Grid)도 같이 해 줘야 하나? 하는 의문이 생긴다.

-> 결론부터 말하면 선택사항이며 오라클에서는 보안패치가 포함되어 있기 때문에 같이 해줄것을 권장하고 있다.
-> 같이 관리해 주고 싶다면 GI RU 를 받아서 OCW와 DB RU를 개별적으로 Patch 적용해 주면 된다.

==========================================================================

APPLIES TO:

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

GOAL

Is it or not necessary to apply OCW patches on my ORACLE_HOME even if I don't use ASM, RAC or CLUSTERWARE?

It is optional, but even if no GI Stack (ASM, Clusterware or RAC) is used inside the server, it is recommended not to ignore the security patches of the installed components. And apply the most recent OCW Patch.

From version 19c onwards, the Patch 29585399 OCW RU is included from the initial installation.  

[oracle@localhost ~]$ opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242) 

OPatch succeeded. 

SOLUTION

To apply the latest OCW patch, it is necessary to do it manually. This must be done by downloading the latest GI RU patch, unzipping it and placing it in the directory corresponding to the OCW and apply it using "opatch apply". 

Applying OCW and DB RUs 

Patch 35319490: GI RELEASE UPDATE 19.20.0.0.0.0 will be used.

You can download it from the following link:
https://updates.oracle.com/download/35319490.html 

NOTE: For rollback, installation, and post-installation instructions refer to README of the respective patches.

[oracle@localhost ~]$ unzip p35319490_190000_Linux-x86-64.zip

[oracle@localhost ~]$ cd 35319490/35320149 ◄◄◄ OCW Patch ID

[oracle@localhost ~]$ opatch apply

Patch 35320149 successfully applied.
OPatch succeeded.

[oracle@localhost ~]$ cd cd 35319490/35320081 ◄◄◄ DB RU Patch ID

[oracle@localhost ~]$ opatch apply

Patch 35320081 successfully applied.
OPatch succeeded.

Proceed to start the database and execute datapatch

SQL> startup
SQL> exit

[oracle@localhost ~]$ datapatch -verbose
SQL Patching tool complete on Sat Aug 26 21:29:07 2023 

[oracle@localhost ~]$ opatch lspatches

35320081;Database Release Update : 19.20.0.0.230718 (35320081)
35320149;OCW RELEASE UPDATE 19.20.0.0.0 (35320149)

OPatch succeeded.

[oracle@localhost ~]$

Already updated OCW and DB Release Update to the latest version

공식적인 방법은 아니니 에디션별 기능 등을 quick 하게 테스트 해야 할 경우 해 볼수 있겠다.

- Enterprise -> Standard 로 변경. shutdown 후 relink

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk edition_standard ioracle

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

select * from v$option where parameter='Partitioning';

PARAMETER            VALUE          CON_ID

-------------------- ---------- ----------

Partitioning         FALSE                0

- Standard -> Enterprise 로 변경. shutdown 후 relink

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk edition_enterprise ioracle

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

PARAMETER            VALUE          CON_ID

-------------------- ---------- ----------

Partitioning         TRUE                0

- 현재  ins_rdbms.mk 화일에 code 되어 있는 내용은 아래와 같음

grep -i edi /oracle/app/oracle/product/19.0.0/rdbms/lib/ins_rdbms.mk

edition_corestandard:

        $(SILENT)$(ECHO) "Deploying Oracle Database Core Standard Edition"

edition_coreenterprise:

        $(SILENT)$(ECHO) "Deploying Oracle Database Core Enterprise Edition"

edition_standard:

        $(SILENT)$(ECHO) "Deploying Oracle Database Standard Edition"

edition_enterprise:

        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition"

edition_highperf:

        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition High Performance"

edition_extremeperf:

        $(SILENT)$(ECHO) "Deploying Oracle Database Enterprise Edition Extreme Performance"

edition_express:

        $(SILENT)$(ECHO) "Deploying Oracle Database Express Edition"

시력을 잃어 가는 남자

청력을 잃어 말하지 못하는 여자

소설의 줄거리는 중요하지 않다.

두 사람이 만나는 이야기 이지만 사랑이라 부를수 있을까?

때로는 침묵이 더 효과적인 언어수단이며

고요한 적막이 우리에게 커다란 울림을 느끼게 한다. 

Exadata 이미지 업그레이드 작업 버그 하나 (22.1.17, 23.1.8)

Issue: During OS Image Upgrade, node gets into boot loop

Impacted releases: Direct upgrade from 21.2.10 or earlier to 22.1.17 / 22.1.18 / 22.1.19 / 23.1.8 /23.1.9 / 23.1.10

Root Cause :
There is a known bug in kernel-ueknano-4.14.35-2047.511.5.5.3.el7uek.x86_64 Kernel due to which FW update of Whitney+ card crashes Kernel.
Above Kernel is included in Nov-2023 releases (22.1.17, 23.1.8).
Bug 35844212/35848949 - bnxtnvm failed to update/downgrade whitneyplus firmware
Fix for above bug is included in uptrack-update that is also included in Nov-2023 releases.
While upgrading compute during firstboot, it comes up with new Kernel and FW updates are applied.
There is no issue if FW upgrade activity starts after botting up with new Kernel and uptrack update is effective.
Due to timing issues, if FW upgrade starts after booting up with new Kernel and before uptrack-update is effective, then kernel crashes and node gets rebooted.
During boot up time (firstboot), FW upgrade kicks in again causes Kernel to crash. This is happening in a loop forever.

Workaround:
• Disable FW updates during impacted upgrade path ( touch /opt/oracle.cellos/DISABLE_HARDWARE_FIRMWARE_CHECKS)
• Post upgrades, install FW upgrades
o rm /opt/oracle.cellos/DISABLE_HARDWARE_FIRMWARE_CHECKS
o /opt/oracle.cellos/CheckHWnFWProfile -action updatefw -mode exact
Next steps:
Kernel with fix is included in Feb-2024 releases (22.1.20, 23.1.11).

CRS-2717: Server '<hostname>' is not in any of the server pool(s) hosting resource 'ora.<sid>.db'

Resources Unable to be Started on a Given Cluster Node (Doc ID 2122592.1)


- 증상 

startup 했는데 뜬금없이 resource 에 등록되어 있지 않다고 에러발생

srvctl 명령어 에러시

<root_user>@node2:# <ORACLE_HOME>/srvctl start instance -db <dbname> -node <hostname>
PRCR-1013 : Failed to start resource ora.<sid>.db
PRCR-1064 : Failed to start resource ora.<sid>.db on node node2
CRS-2717: Server '<hostname>' is not in any of the server pool(s) hosting resource 'ora.<sid>.db'

startup 명령어 에러시

SQL> startup
ORA-39510: CRS error performing start on instance <instance name?
CRS-2549: Resource <resource name> cannot be placed on <node name> as it is not a valid candidate as per the placement policy
CRS-0223: Resource <resource name> has placement error.


<GI_HOME>/bin/crsctl stat res ora.crsd -f -init | grep RESOURCE_USE_ENABLED
RESOURCE_USE_ENABLED=0

정상적이면 이 값은 1이 되어야 한다.


- 원인
Bug 23733697 에 의한 것으로 rootcs.pl -prepatch 로 패치하는 동안 임시로 값을 0 으로 만드는데 작업이 중단/실패되거나 했을 경우 다시 0 -> 1로 바꾸어야 하는데 그러지 못하고 0 상태로 원복하지 못함.

<GI_HOME>/bin/crsctl set resource use 1
위와 같이 조치하고 CRS 재기동

crsctl stop crs
crsctl start crs

정상확인 !!

패치작업 19.22.0.0.240116 (GI PSU 35940989)

Database Patch Set Update : 19.22.0.0.240116 (35943157)
OCW Patch Set Update      : 19.22.0.0.240116 (35967489)
ACFS Patch Set Update     : 19.22.0.0.240116 (35956421)
Tomcat Release Update     : 19.0.0.0.0       (36115038)
DBWLM Release Update      : 19.0.0.0.0       (33575402)

Oracle Grid Infrastructure Patch Set Update 19.22.0.0.240116 
-------------------------------------
GI_HOME, ORACLE_HOME 을 개별로 각각 할때

- grid 유저
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/35943157
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/35967489
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/35956421
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/33575402
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/36115038

- oracle 유저
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/35943157
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/35940989/35967489

(oracle)
$ <ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name>

(root 유저)
export GI_HOME=/u01/app/19.0.0.0/grid
$GI_HOME/crs/install/rootcrs.sh -prepatch 

(grid 유저)
export GI_HOME=/u01/app/19.0.0.0/grid
cd /u01/install
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./35940989/35967489 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./35940989/35956421 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./35940989/35943157 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./35940989/33575402 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./35940989/36115038 -silent

(oracle 유저)
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1
cd /u01/install
./35940989/35967489/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local ./35940989/35967489 -silent
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -local ./35940989/35943157 -silent
./35940989/35967489/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME 

(root 유저)
export GI_HOME=/u01/app/19.0.0.0/grid
$GI_HOME/rdbms/install/rootadd_rdbms.sh
$GI_HOME/crs/install/rootcrs.sh -postpatch 

(oracle)
$ <ORACLE_HOME>/bin/srvctl start home -o <ORACLE_HOME> -s <status file location> -n <node name> 

- Loading Modified SQL Files into the Database
sqlplus /nolog
SQL> conect / as sysdba
SQL> startup
SQL> quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose

### [롤백하는 경우] ###########

(oracle)
$ <ORACLE_HOME>/bin/srvctl stop home -o <ORACLE_HOME> -s <status file location> -n <node name>

GI Home
(root로)
$GI_HOME/crs/install/rootcrs.sh -prepatch -rollback

(grid 유저로)
export GI_HOME=/u01/app/19.0.0.0/grid
cd /u01/install
$GI_HOME/OPatch/opatch nrollback -local -id 35967489,35956421,35943157,33575402,36115038 -oh $GI_HOME -silent

(oracle 유저로)
export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1
cd /u01/install
./35940989/35967489/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME 
$ORACLE_HOME/OPatch/opatch nrollback -local -id 35967489,35943157 -oh /u01/app/oracle/product/19.3.0.0/dbhome_1 -silent
./35940989/35967489/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME

Run post script
(root로)
export GI_HOME=/u01/app/19.0.0.0/grid
$GI_HOME/rdbms/install/rootadd_rdbms.sh
$GI_HOME/crs/install/rootcrs.sh -postpatch -rollback

(oracle)
$ <ORACLE_HOME>/bin/srvctl start home -o <ORACLE_HOME> -s <status file location> -n <node name>

sqlplus /nolog
SQL> conect / as sysdba
SQL> startup
SQL> quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose