pat98's always & forever

19c user명 변경 테스트

test1 유저에서 test2 로 유저명을 변경해 보자.

sys@TEST> create user test1 identified by test1;

관련 Parameter 기본값 조회

KSPPINM                                  KSPPSTVL
---------------------------------------- ------------------------------
_enable_rename_user                      FALSE
_asm_allow_system_alias_rename           FALSE
_asm_skip_rename_check                   FALSE


sys@TEST> alter session set "_enable_rename_user" = true;

Session altered.

Elapsed: 00:00:00.00
sys@TEST> alter system enable restricted session;

System altered.

Elapsed: 00:00:04.16
sys@TEST> alter user test1 rename to test2 identified by test2;
alter user test1 rename to test2 identified by test2
                 *
ERROR at line 1:
ORA-03001: unimplemented feature

테스트 결론

-> 11g/12c 는 아래와 같은 꼼수로 user명 변경이 가능함.
alter session set "_enable_rename_user" = true;
alter system enable restricted session;

-> 19c 에서는 되지 않는다.
export의  fromuser,touser 또는 datapump의 remap_schema 옵션으로 넘겨야 할듯...

아래와 같이 작성하여 sqlplus 매번 접속없이 스크립트로 받아서 모니터링 하기.

최종적으로 실행은 아래와 같이 작성한다.

sh session.sh | sqlplus -L -S > session_$(date +%Y_%m_%d_%H:%M).log

- 스크립트 내용
vi session.sh
#!/usr/bin/ksh 

SLEEP_SEC=1 
LOOP_MAX=100000
COUNT=0 

echo "system/manager"

while [ $COUNT -lt $LOOP_MAX ] 
do 
        echo "@session.sql" 
        sleep $SLEEP_SEC 
        COUNT=`expr $COUNT + 1` 
done
======================================
session.sql 의 내용 (내용은 구미에 맞게 수정하도록 한다)

--
-- Session Monitor
--

set verify off
set echo off

col sid format 99990
col serial# format 99990
col pid format 99999
col spid format a8
col process format a8
col username format a10
col program format a24 trunc
col machine format a12 trunc
col module format a15 trunc
col status format a8
col lockwait format a4

select /*+ rule */
  s.sid,
  s.serial#,
--  p.pid,
  p.spid,
--  s.process,
  s.username,
  replace(s.program,'(TNS V1-V3)','') program,
  replace(s.machine,'(TNS V1-V3)','') machine,
  s.module,
--  s.terminal,
  s.status,
--  decode(s.lockwait, null, '', 'WAIT')
--  s.client_info
  s.logon_time,
  s.last_call_et
from v$session s, v$process p
where s.paddr = p.addr
and type <> 'BACKGROUND'
-- and s.username <> 'SYS'
-- and s.logon_time > '2023/02/11 19:43:00'
-- order by s.logon_time, s.program, s.module, spid
order by s.status
/

- OCR 백업위치 테스트 (CFS 환경)
-> ocr 백업위치 변경 바로 반영되는지 확인..auto 화일도 변경된 위치에 떨어지는지는 4시간후 확인..

[root@ocfs1:/root]# ocrconfig -showbackup

ocfs2     2021/09/10 18:02:22     /vote02/backup00.ocr     724960844
ocfs2     2021/09/10 14:02:18     /vote02/backup01.ocr     724960844
ocfs2     2021/09/10 14:02:18     /vote02/day.ocr     724960844
ocfs2     2021/09/10 14:02:18     /vote02/week.ocr     724960844
ocfs1     2023/01/25 22:07:27     /vote02/backup_20230125_220727.ocr     3007461856  
   
[root@ocfs1:/root]# ocrconfig -backuploc /vote01

[root@ocfs1:/root]# ocrconfig -showbackup

ocfs2     2021/09/10 18:02:22     /vote02/backup00.ocr     724960844
ocfs2     2021/09/10 14:02:18     /vote02/backup01.ocr     724960844
ocfs2     2021/09/10 14:02:18     /vote02/day.ocr     724960844
ocfs2     2021/09/10 14:02:18     /vote02/week.ocr     724960844
ocfs1     2023/01/25 22:07:27     /vote02/backup_20230125_220727.ocr     3007461856  

[root@ocfs1:/root]# ocrconfig -showbackuploc
The Oracle Cluster Registry backup location is [/vote01]. 

[root@ocfs1:/etc/oracle]# ocrconfig -manualbackup

ocfs1     2023/03/14 19:49:20     /vote01/backup_20230314_194920.ocr     3007461856     
ocfs1     2023/01/25 22:07:27     /vote02/backup_20230125_220727.ocr     3007461856 

Windows : Postpatch fails with ORA-12638 after applying BP 19.10 (Doc ID 2768922.1)

ORA-12638 on Windows only from Oracle 19.10.0 onwards (mikedietrichde.com)

WIndows 플랫폼으로 RAC 구성할 일이 거의 없긴 하지만..

증상 : 

1. Windows 19c RAC 기본 19.3 으로 설치/구성은 올바르게 작동하고

이후 패치작업을 위해  Windows BP만 적용하면 한쪽 2번 노드가 올라오지 못하는 증상이 있다고 한다. (아마 노드간 통신이 안되니 ASM 이 기동안되면서 Fail 되는 듯 하다.

-> 21c 도 비슷한 이슈인듯 

s2. ingle 구성도 WIndows BP 적용하면 이런 증상이 나타나는 듯 하다. ORA-12638: credential retrieval failed

- GI Alert log

2021-04-12 16:14:10.364 [OLOGGERD(5724)]CRS-8500: Oracle Clusterware OLOGGERD process is starting with operating system process ID 5724
2021-04-12 16:22:30.059 [ORAROOTAGENT(7076)]CRS-5818: Aborted command 'start' for resource 'ora.storage'. Details at(:CRSAGF00113:) {0:5:3}(D:\oracle\grid\diag\crs\＜host_name＞\crs\trace\ohasd_oraroot
agent_system.trc)
2021-04-12 16:22:30.840 [ORAROOTAGENT(7076)]CRS-5017: The resource action "ora.storage start" encountered the following error:
2021-04-12 16:22:30.840+agent's abort action pending。Details at "(:CLSN00107:)"("D:\oracle\grid\diag\crs\＜host_name＞\crs\trace\ohasd_orarootagent_system.trc")
2021-04-12 16:22:30.841 [OHASD(7668)]CRS-2757: Command 'Start' timed out waiting for response from the resource 'ora.storage'. Details at (:CRSPE00221:) {0:5:3}(D:\oracle\grid\diag\crs\＜host_name＞\crs\trace\ohasd.trc)

- Ohasd_orarootagent_system.trc

2021-04-12 16:20:01.078 : USRTHRD:9468: [ INFO] {0:5:3} [ora.storage] 9607 Error 4 querying length of attr ASM_DISCOVERY_ADDRESS

2021-04-12 16:20:01.080 : USRTHRD:9468: [ INFO] {0:5:3} [ora.storage] 9607 Error 4 querying length of attr ASM_STATIC_DISCOVERY_ADDRESS

2021-04-12 16:20:01.104 : USRTHRD:9468: [ INFO] {0:5:3} [ora.storage] 9325 Error 4 opening dom system in 000002052EF5AB80

2021-04-12 16:20:01.424 : USRTHRD:2760: [ INFO] {0:5:3} Thread:kgfoCheckMountExt Tasklet::doTask m_cv.timewait returned
2021-04-12 16:20:02.225 : USRTHRD:2760: [ INFO] {0:5:3} Thread:kgfoCheckMountExt Tasklet::doTask m_cv.timewait returned
2021-04-12 16:20:02.374 : USRTHRD:9468: [ INFO] {0:5:3} [ora.storage] -- trace dump on error exit --

2021-04-12 16:20:02.374 : USRTHRD:9468: [ INFO] {0:5:3} [ora.storage] Error [kgfoAl06] in [kgfokge] at kgfo.c:3180

2021-04-12 16:20:02.374 : USRTHRD:9468: [ INFO] {0:5:3} [ora.storage] ORA-12638: credential retrieval failed

원인 :

windows BP 19.10 부터 보안 버그 수정으로 인해 SQLNET.NO_NTLM 기본값이 TRUE로 변경되어짐

NTLM(NT Lan Manager) 은 Windows 네트워크 LAN 관리자 인증 서비스이다.

해결책 :

보안상의 이유로  Windows 의  NTLM 설정을 disable 하려면 아래와 같이 조치해야 한다.

sqlnet.ora에서 $GRID_HOME/network/admin의 SQLNET.NO_NTLM = FALSE를 설정

또는 클라이언트 환경의 sqlnet.ora 를 열고 동일하게 SQLNET.NO_NTLM = FALSE를 설정

1번 노드는 잘되는데 2번 노드에서 문제 발생시, 조치 요령

--관련 MOS 26861652.1

--- 문제 발생하면 나는 에러메세지
[root@rac1 ~]# /u01/app/19.0.0.0/grid/perl/bin/perl /u01/app/19.0.0.0/grid/crs/install/rootcrs.pl -postpatch
Using configuration parameter file: /u01/app/19.0.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/rac1/crsconfig/crs_postpatch_apply_inplace_rac1_2022-01-22_11-10-36AM.log
2023/01/22 11:10:44 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.service'
2023/01/22 11:12:25 CLSRSC-915: ASM postpatch operation failed.
Died at /u01/app/19.0.0.0/grid/crs/install/crspatch.pm line 1845.

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

조치방법

crsctl query crs activeversion -f

(root)
- OCR 백업 필요
ocrconfig -manualbackup
clscfg -patch
crsctl stop rollingpatch

- Normal 로 보이는지 확인
crsctl query crs activeversion -f

From ASM,
SQL> SELECT SYS_CONTEXT('SYS_CLUSTER_PROPERTIES', 'CLUSTER_STATE') FROM DUAL;

https://blogs.oracle.com/maa/post/introducing-dg-redo-decrypt-for-hybrid-cloud

Hybrid DR환경에서 암호화 선택여부 가능해짐. (19.16 RU 부터 가능한 신기능) <- 2022년 7월 RU 적용필요

(DB50) Clusterware Fails to Start Because CSSD Cannot Get Real-Time Priority (Doc ID 2903663.1)
Issue #2 로 인한 Merge 패치 34719373 작업

-> GI_HOME, DB_HOME 모두 적용해야 함.

(crs 종료)
$GI_HOME/crs/install/rootcrs.sh -prepatch  <---- 반드시 prepatch로 내려야 함.

(root로 변경 및 확인)
ls -ld $GI_HOME/bin/oradism
-rwsr-x--- 1 root dba 145920 Oct 20 03:59 /u01/app/19.0.0.0/grid/bin/oradism

chown grid $GI_HOME/bin/oradism
chmod 0750 $GI_HOME/bin/oradism
ls -ld $GI_HOME/bin/oradism

(grid)
chown -R grid:dba /u01/install/34719373
$GI_HOME/OPatch/opatch apply -oh /u01/app/19.0.0.0/grid -local /u01/install/34719373

(root로 변경 및 확인)
ls -ld $ORACLE_HOME/bin/oradism
chown oracle $ORACLE_HOME/bin/oradism
chmod 0750 $ORACLE_HOME/bin/oradism
ls -ld $ORACLE_HOME/bin/oradism

(oracle)
chown -R oracle:dba /u01/install/34719373
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/19.0.0.0/dbhome_1  -local /u01/install/34719373

(grid)
opatch lspatches -oh $GI_HOME

(oracle) 
opatch lspatches -oh $ORACLE_HOME

(원복)
ls -ld $GI_HOME/bin/oradism
chown root $GI_HOME/bin/oradism
chmod 4750 $GI_HOME/bin/oradism
ls -ld $GI_HOME/bin/oradism

ls -ld $ORACLE_HOME/bin/oradism
chown root $ORACLE_HOME/bin/oradism
chmod 4750 $ORACLE_HOME/bin/oradism
ls -ld $ORACLE_HOME/bin/oradism

(crs 기동)
$GI_HOME/crs/install/rootcrs.sh -postpatch

패치작업 19.18.0.0.230117 (GI PSU 34762026 )

Database Patch Set Update : 19.18.0.0.230117 (34765931)
OCW Patch Set Update      : 19.18.0.0.230117 (34768559)
ACFS Patch Set Update     : 19.18.0.0.230117 (34768569)
Tomcat Release Update     : 19.0.0.0.0       (34863894)
DBWLM Release Update      : 19.0.0.0.0       (33575402)

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

- grid 유저
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026/34765931
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026/34768559
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026/34768569
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026/33575402
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026/34863894

- oracle 유저
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026/34765931
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <UNZIPPED_PATCH_LOCATION>/34762026 34768559

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

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

(grid 유저)
export GI_HOME=/u01/app/19.3.0.0/grid
cd /u01/install
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./34762026/34768559 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./34762026/34768569 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./34762026/34765931 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./34762026/33575402 -silent
$GI_HOME/OPatch/opatch apply -oh $GI_HOME -local ./34762026/34863894 -silent

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

(root 유저)
export GI_HOME=/u01/app/19.3.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.3.0.0/grid
cd /u01/install
$GI_HOME/OPatch/opatch nrollback -local -id 34768559,34768569,34765931,33575402,34863894 -oh $GI_HOME -silent

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

Run post script
(root로)
export GI_HOME=/u01/app/19.3.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

19c RAC로 올린 이후 Log File Sync wait event가 증가하는 사례

• 현상
➢ RAC 19c로 upgrade 이후에 LGWR process가 Log File Sync 로 대기 및 RDBMS IPC message wait event가 급격히 증가하는 이슈
➢ AWR 정보에서 "redo write broadcast ack time"이 증가하는 증상으로 현상 파악
➢ LGWR에 의해서 SCN ACK messages가 delayed completion되면서 대기 시간이 늘어나는 문제
➢ LGWR 트레이스 파일에 ‘Warning: log write broadcast wait time xxx ms’ 메시지가 나타남

• 원인
➢ OS Watcher report를 분석했을 때 OS 단에서 IO가 지연되는 증상은 없는 경우임
➢ Bug.30614411 - LMS ignoring message handler for SCN broadcast message causing the originating instance to wait very
long for broadcast completion
➢ <Note:2699312.1> Intermittent Long 'log file sync' Waits on 2 Node RAC After Upgrading to 19c

• 해결 방안
1. DB RU 19.8 에서 fix되었다고 되어 있으나, 실제 inventory.xml을 보면 19.9 에서 Bug.30614411에 대한 Fix를 포함하고 있음
2. Patch 30614411 적용
3. <Note:2698757.1> Wait Time for Log File Sync Increases on RAC

• 현상
➢ RAC 환경에서 ‘gc buffer busy release’를 오랫 동안 대기하면서 데이터베이스가 hang 걸린것처럼 보이는 현상. 여러 세션에서 ‘gc buffer busy release’ 현상이 나타남
➢ X holder가 존재하는 동안, read-mostly object를 exclusive lock 을 대기하여 session hang 발생

• 원인
➢ Bug.30381614 – RAC Database Sessions Waiting for a Long Time on ‘gc buffer busy release’
➢ <Note:30381614.8> - RAC Database Sessions Waiting for a Long Time on ‘gc buffer busy release’

• 해결 방안
1. Patch 30381614 적용 또는 mostly locking 을 Disable (Workaround)해야 함
2. Mostly locking을 disable하는 방안 : _gc_read_mostly_locking=FALSE
Rolling 방식으로 mostly locking을 disable하기 위한 방안 :
_gc_persistent_read_mostly=FALSE and _lm_drm_disable=4