pat98's always & forever

- 11g -> 19c 업그레이드 작업후 후속작업 진행시 compatible 문제로 startup 불가..

- 장애상황

업그레이드 완료후 11.2.0.4 -> 19.0.0 으로 compatible 변경 진행하였으나 실패

startup 실패함.

ORA-38880: Cannot advance compatibility from 11.2.0.4.0 to 19.0.0.0.0 due toguaranteed restore points

- Solution

1. 변경 작업전 parameter 백업

SQL> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.0.0
                                                 .0/dbhome_1/dbs/spfileORCL.ora
SQL> create pfile='/tmp/test.ora' from spfile;

File created.

2. 11.2.0.4.0 -> 19.0.0 으로 compatible 변경
-> compatible 값은 downgrade 가 되지 않으므로 반드시 parameter 백업을 받아야 한다.

SQL> alter system set compatible='19.0.0' scope=spfile;

System altered.

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

Total System Global Area 3053449792 bytes
Fixed Size                  8929856 bytes
Variable Size             620756992 bytes
Database Buffers         2415919104 bytes
Redo Buffers                7843840 bytes


ORA-38880: Cannot advance compatibility from 11.2.0.4.0 to 19.0.0.0.0 due to
guaranteed restore points

-> 업그레이드시 사용한 GRP 영역 때문에 변경이 되지 않는다.

3. 백업 받은 화일의 이전 값으로  다시 startup
SQL> startup pfile='/tmp/test.ora'
ORACLE instance started.

Total System Global Area 3053449792 bytes
Fixed Size                  8929856 bytes
Variable Size             620756992 bytes
Database Buffers         2415919104 bytes
Redo Buffers                7843840 bytes
Database mounted.
Database opened.


4. 오픈되면 GRP 영역을 확인한 후, drop 처리한다.
SQL> set lines 121
SQL> col name for A30
SQL> col time for A40
SQL> col DATABASE_INCARNATION# for 99999999
SQL> SELECT NAME, SCN, TO_CHAR(TIME,'YYYY-MM-DD HH24:MI:SS') TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE = 'YES';

NAME                                  SCN TIME                                     DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------------ ---------- ---------------------------------------- --------------------- --- ------------
AUTOUPGRADE_9212_ORCL112040        266377 2024-04-09 15:34:16                                          1 YES    681574400

SQL> drop restore point AUTOUPGRADE_9212_ORCL112040;

Restore point dropped.

5. 다시 재기동  
SQL> shutdown immediate
startup
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 3053449792 bytes
Fixed Size                  8929856 bytes
Variable Size             620756992 bytes
Database Buffers         2415919104 bytes
Redo Buffers                7843840 bytes
Database mounted.
Database opened.

6. 정상 확인
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE

- silent mode 에서 전부 Big file 로 만드는 방법 (Tablespace 별 개별적으로 true로 하지 않고)

1. Templates 화일의 최소 충족요건으로 변경

$ORACLE_HOME/assistants/dbca/templates/New_Database.dbt 화일내의 USERS tabllespace 의 datafile 최소크기를 최소 7M 로 변경함.

      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf">
         <tablespace>USERS</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">7</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">1280</increment>
         <maxSize unit="MB">-1</maxSize>
      </DatafileAttributes>

2. -useBigFileForTablespace 옵션 사용 
dbca -silent -createDatabase -gdbName BIGORCL -templateName New_Database.dbt -useBigFileForTablespace true


3. 확인
sys@BIGORCL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

TABLESPACE_NAME                BIG
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
TEMP                           YES
USERS                          YES