Oracle Database 12c R2 から 19c へ Refreshable PDB Clone 移行してみてみた


Oracle Database 12c Release 2 (12.2) の新機能 [リフレッシュ可能なクローンPDB (Refreshable PDB Clone)](https://qiita.com/shirok/items/56d79f6826f9ec48a9f1) は、ソースPDBと定期的に同期できる読取り専用クローンです。この機能を使用することでアップグレード時間短縮できます。
リリース・バージョンが異なる CDB 間で リフレッシュ可能な PDB を作成し、最後の瞬間まで同期を保ち、最後に移行するPDBをアップグレードすることでバージョンアップのダウンタイム時間を短縮することができます。
ということで、オンプレミスの Oracle Database 12c R2 から Oracle Cloud Infrastructure (OCI) の 19c へ Refreshable PDB Clone 移行してみてみます。


# ■ 構成
![構成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/e86125fd-d8f5-3af9-627f-6314eaf8df53.png)
|  |Source (On-Premises)  |Target (OCI)  |
|---|---|---|
|Database Version  |12.2.0.1.0 |19.22.0.0.0  |
|Server  |Intel NUC 13 Pro |Exadata Database Service  |
|Host Name  |rac12cR2-node1 |exadata-19c-tokyo1  |
|IP Address  |192.168.0.12 |10.0.0.19  |
|CDB Name  |CDB12cR2 |CDB19c  |
|PDB Name  |PDB12cR2 |PDB12cR2_REFRESHABLE_CLONE  |

今回、12c はオンプレミス、19c は Oracle Cloud Infrastructure (OCI)を使用します。
　・ 19c環境: [Oracle Exadata Cloud Infrastructure X9M を作成](https://qiita.com/shirok/items/1f7a10d254849a9fdddb)
　・ 12c環境: [Oracle Real Application Clusters 12cR2 (12cR2 RAC)をインストール]()
　・ オンプレ: [VMware vSphere Hypervisor (ESXi) 8 を Intel NUC 13 へ インストール](https://qiita.com/shirok/items/d1c067a4023f33b69af0)
　・ VPN接続: [Cisco 841M ルーターを IKEv2 と BGPで IPSec VPN接続してみてみた](https://qiita.com/shirok/items/1542af8efcd4d94c4b3a)

# ■ 前提条件
次の前提条件を満たしている必要があります。
　・ [リモートPDBのクローニング前提条件](https://docs.oracle.com/cd/F19136_01/multi/cloning-a-pdb.html#GUID-2512C9E9-FD11-4F33-871B-892ED5FC4079)
　・ [リフレッシュ可能なクローンPDBの要件](https://docs.oracle.com/cd/F19136_01/multi/cloning-a-pdb.html#GUID-30E0CC68-4F4C-4E58-9451-6390406D90A1)




# ■ Source DB (12c) 設定
## ● CDB へ Refreshable Clone用 USER作成
**1) CDB へ Login**
```
[oracle@rac12cR2-node1 ~]$ sql / as sysdba

SQLcl: Release 12.2.0.1.0 RC on Sun Jul 21 13:16:13 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
```

**2) Create User**
データベース リンクが接続するリモート データベースのユーザーは、CREATE PLUGGABLE DATABASE 権限が必要です。

```
SQL> CREATE USER c##refresh IDENTIFIED BY <Password> CONTAINER=ALL;

  User C##REFRESH created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##refresh CONTAINER=ALL;

  Grant succeeded. 
```

## ● Source PDB環境確認
**1) Local UNDO 確認**
Local UNDO は、クローン作成プロセス中にソースが読み取り/書き込み状態を維持できるようにするために必要です。
```
SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

    PROPERTY_NAME                  PROPERTY_VALUE
    ------------------------------ ------------------------------
    LOCAL_UNDO_ENABLED             TRUE
```

**2) 既存 PDB確認**
```
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB12CR2                       READ WRITE NO
```


## ● 既存 PDBへテスト・データ作成
```
SQL> alter session set container=PDB12cR2;

  Session altered.

SQL> CREATE TABLE test_table1 (id NUMBER(8), now DATE) TABLESPACE USERS;

	Table TEST_TABLE1 created.

SQL> INSERT INTO test_table1 VALUES (1,SYSDATE);

  1 row inserted.

SQL> commit;

  Commit complete.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

  Session altered.

SQL> select * from test_table1;

        ID NOW
---------- -------------------
         1 2024/07/21 15:59:19
```

# ■ Target DB 設定
## ● SQL*NET設定
lsnrctl 出力情報から、SERVICE_NAME を取得し接続記述子を作成します。

**1) Source DB の lsnrctl Service確認**
tnsnames.ora へ設定する PDB の SERVICE_NAME パラメータを確認
ここでは、SERVICE_NAME = PDB12cR2.oracle.com になります。
```
[oracle@rac12cR2-node1 admin]$ lsnrctl stat | grep Service
    Services Summary...
    Service "+ASM" has 1 instance(s).
    Service "+ASM_CRS" has 1 instance(s).
    Service "+ASM_DATA" has 1 instance(s).
    Service "+ASM_RECO" has 1 instance(s).
    Service "1dbcc5ac29a906ade0637964a8c0bcde.oracle.com" has 1 instance(s).
    Service "CDB12cR2.oracle.com" has 1 instance(s).
    Service "CDB12cR2XDB.oracle.com" has 1 instance(s).
    Service "PDB12cR2.oracle.com" has 1 instance(s).
```

**2) Target DBへ tnsnames.ora設定**
HOSTへ Source DB のIPを設定
SERVICE_NAMEは Source DB の service_name を設定
```
[oracle@exadata-19c-tokyo1 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
[oracle@exadata-19c-tokyo1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
PDB12cR2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB12cR2.oracle.com)
    )
  )
```

3 接続テスト
```
[oracle@exadata-19c-tokyo1 ~]$  sql system/<Password>@PDB12cR2


SQLcl: Release 21.4 Production on Sun Jul 21 06:54:34 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Sun Jul 21 2024 06:54:35 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs

   CON_ID    CON_NAME     OPEN MODE    RESTRICTED
_________ ___________ _____________ _____________
        3 PDB12CR2    READ WRITE    NO
```


## ● Create dblink

ターゲット DB 上に ソース DB への DBLink を作成します。

**1) SQLcl 接続**
```
[oracle@exadata-19c-tokyo1 ~]$ sql / as sysdba


SQLcl: Release 21.4 Production on Sun Jul 21 06:49:49 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
```



**2) Public DBLink 作成**
```
SQL> create public database link refresh_pdb connect to c##refresh identified by Password using 'PDB12cR2';

Database link REFRESH_PDB created.
```

**3) Public DBLink 作成確認**
データベースリンクがターゲットデータベースで機能しているかどうかを確認します
```
SQL> alter session set global_names=false;

  Session altered.

SQL> select * from dual@refresh_pdb;

    DUMMY
    ________
    X
```

## ● Refreshable Clone PDB作成

**1) 既存PDB確認**
```
SQL> show pdbs

   CON_ID     CON_NAME     OPEN MODE    RESTRICTED
_________ ____________ _____________ _____________
        2 PDB$SEED     READ ONLY     NO
        4 PDB_TOKYO    READ WRITE    NO
```


**2) Refreshable Clone PDB作成**
```
SQL> CREATE PLUGGABLE DATABASE PDB12cR2_Refreshable_Clone FROM PDB12cR2@refresh_pdb REFRESH MODE EVERY 30 MINUTES KEYSTORE IDENTIFIED BY Password;

Pluggable database REFRESHABLE_CLONE_PDB created.
```

**3) Refreshable Clone PDB作成確認**
```
SQL> show pdbs

   CON_ID                      CON_NAME     OPEN MODE    RESTRICTED
_________ _____________________________ _____________ _____________
        2 PDB$SEED                      READ ONLY     NO
        3 PDB_TOKYO                     READ WRITE    NO
        4 PDB12CR2_REFRESHABLE_CLONE    MOUNTED
```



# ■ 手動 Refresh 
Source PDB で更新したデータを Target PDB へデータ同期します。

## ● Source PDB (12c PDB)更新

**1) Source PDB (12c PDB)接続**
```
[oracle@rac12cR2-node1 ~]$ sql / as sysdba

    SQLcl: Release 12.2.0.1.0 RC on Sun Jul 21 16:19:50 2024

    Copyright (c) 1982, 2024, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=PDB12cR2;

    Session altered.
```


**2) テスト・データINSERT**
```
SQL> INSERT INTO test_table1 VALUES (2,SYSDATE);

	1 row created.

SQL> commit;

	Commit complete.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> select * from test_table1;

        ID NOW
---------- -------------------
         1 2024/07/21 15:59:19
         2 2024/07/21 16:20:18
```

## ● Target PDB 手動リフレッシュ
```
SQL> ALTER PLUGGABLE DATABASE PDB12CR2_REFRESHABLE_CLONE REFRESH;

  Pluggable database PDB12CR2_REFRESHABLE_CLONE altered.
```

**-- Alert Log --**
```
    2024-07-21T07:26:50.789893+00:00
    Applying media recovery for pdb-4099 from SCN 91503411 to SCN 91504545
    Remote log information: count-2
    thr-2,seq-2,logfile-+RECO/CDB12CR2/partial_archivelog/2024_07_21/thread_2_seq_2.277.1174926411,los-687489,nxs-18446744073709551615,maxblks-0
    thr-1,seq-11,logfile-+RECO/CDB12CR2/partial_archivelog/2024_07_21/thread_1_seq_11.276.1174926411,los-652918,nxs-18446744073709551615,maxblks-0
    PDB12CR2_REFRESHABLE_CLONE(3):Media Recovery Start
    2024-07-21T07:26:50.793316+00:00
    PDB12CR2_REFRESHABLE_CLONE(3):Serial Media Recovery started
    PDB12CR2_REFRESHABLE_CLONE(3):max_pdb is 5
    2024-07-21T07:26:50.885302+00:00
    PDB12CR2_REFRESHABLE_CLONE(3):Media Recovery Log +RECO/CDB12CR2/partial_archivelog/2024_07_21/thread_2_seq_2.277.1174926411
    2024-07-21T07:26:51.186271+00:00
    ALTER SYSTEM SET listener_networks='(( NAME=net1)(LOCAL_LISTENER=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.0.19)(PORT=1521))))' SCOPE=MEMORY SID='CDB19c';
    2024-07-21T07:26:51.370262+00:00
    PDB12CR2_REFRESHABLE_CLONE(3):Media Recovery Log +RECO/CDB12CR2/partial_archivelog/2024_07_21/thread_1_seq_11.276.1174926411
    2024-07-21T07:27:05.215807+00:00
    PDB12CR2_REFRESHABLE_CLONE(3):Incomplete Recovery applied until change 91504545 time 07/21/2024 16:26:30
    2024-07-21T07:27:05.219221+00:00
    PDB12CR2_REFRESHABLE_CLONE(3):Media Recovery Complete (CDB19c)
```

# ■ ターゲットPDB Verison UP
## ● リフレッシュ可能でないPDBへ変換
PDB を アップグレードするために Refreshable 機能を停止します。
ALTER PLUGGABLE DATABASE文にREFRESH MODE NONE句を含め、PDBを読取り/書込みモードでオープンすることにより、リフレッシュ可能なクローンPDBを通常のPDBに変更できます。
通常のPDBはリフレッシュ可能なクローンPDBに変更できません。リフレッシュ可能なクローンPDBが通常のPDBに変換された後は、リフレッシュ可能なクローンPDBに戻すことができません。
```
SQL> ALTER PLUGGABLE DATABASE PDB12CR2_REFRESHABLE_CLONE REFRESH MODE NONE;

    Pluggable database PDB12CR2_REFRESHABLE_CLONE altered.
```

## ● ターゲットPDB Verison UP
**1) ターゲットPDB OPEN UPGRADE**
ターゲットPDB を  UPGRADEモードで OPEN
```
SQL> ALTER PLUGGABLE DATABASE PDB12CR2_REFRESHABLE_CLONE OPEN UPGRADE;

Pluggable database PDB12CR2_REFRESHABLE_CLONE altered.


SQL> show pdbs

   CON_ID                      CON_NAME     OPEN MODE    RESTRICTED
_________ _____________________________ _____________ _____________
        2 PDB$SEED                      READ ONLY     NO
        3 PDB_TOKYO                     READ WRITE    NO
        4 PDB12CR2_REFRESHABLE_CLONE    MIGRATE       YES
```

**2) ターゲットPDB Verison UP**
dbupgradeコマンドで Cloneした PDB (12cR2) を CDB (19c) のバージョンへアップグレードします。

**・ dbupgradeコマンド確認**
-n オプションで -n Processes並列数等設定できます。
```
[oracle@basedb19c-tokyo ~]$ dbupgrade -h
Unknown option: h

  Usage: catctl [-A AutomaticUpgrade]
                [-c QuotedSpaceSeparatedInclusionListOfPDBs]
                [-C QuotedSpaceSeparatedExclusionListOfPDBs]
                [-d Directory]
                [-e EchoOff]
                [-E Simulate]
                [-F Forced cleanup]
                [-i Identifier]
                [-l LogDirectory]
                [-L PriorityList]
                [-M UpgradeMode]
                [-n Processes]
                [-N PDB Processes]
                [-p StartPhase]
                [-P EndPhase]
                [-R UpgradeAutoResume]
                [-s Script]
                [-S SerialUpgrade]
                [-T ReadOnlyTablespaces]
                [-u UserName]
                [-y DIsplayPhases]
                [-z CatconDebug]
                [-Z 1 CatctlDebug]
                FileName

  Supported Options:
     -A  Unsupported option. Intended for Oracle internal use only.
     -c  Inclusion list of containers.  Run filename in the quoted,
         space separated argument list of containers only, omitting
         all other containers of the CDB. For example,
         Unix:
           -c 'PDB1 PDB2'
         Windows:
           -c "PDB1 PDB2"
         This switch is mutually exclusive with -C

     -C  Exclusion list of containers.  Run filename in all containers
         in the CDB, EXCEPT those explicitly listed in the quoted, space
         separated argument.  For example,
         Unix:
           -C 'CDB$ROOT PDB3'
         Windows:
           -C "CDB$ROOT PDB3"
         This switch is mutually exclusive with -c

     -d  Directory containing the files to be run
     -e  Sets echo off while running the scripts
     -E  Simulate the upgrade
         For traditional databases this parameter is ignored
     -F  Force a cleanup of previous upgrade errors. This option is used
         with an inclusion list (-c option) for CDB containers.  For traditional
         databases only the -F option is required. For example,

         $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -F -c 'CDB$ROOT' $ORACLE_HOME/rdbms/admin/catupgrd.sql

         Note: This command does not run the upgrade but removes errors from a previous upgrade run

     -i  Identifier to use when creating spool log files
     -l  Directory to use for spool log files
     -L  Priority list file name in priority number and Pdb Name format. List
         will be sorted and upgrades will be processed by priority number.
         The lower priority numbers will be upgraded first. For example in
         the list below PDB1 PDB2 will be upgraded first and PDB3 and PDB4
         will be upgraded second. CDB$ROOT and PDB$SEED are always priorities
         1 and 2 and cannot be changed.  CDB$ROOT will alway be processed first,
         PDB$SEED will always be processed in the first set of upgrades.
         All other PDBs not included in the list will be processed last.
         PDB priorities must start at 1.
           1,PDB1
           1,PDB2
           2,PDB3
           2,PDB4
         To quickly generate a priority list file place the
         following lines in a file like priority.sql file.
         Then execute priority.sql in sqlplus.

           SET NEWPAGE 0 SPACE 0 PAGESIZE 0 FEEDBACK OFF
           SET HEADING OFF VERIFY OFF ECHO OFF TERMOUT OFF
           SPOOL priority_list.txt
           SELECT CON_ID || ',' || NAME FROM V$CONTAINERS;
           SPOOL off

           SQLPLUS > @priority.sql

         Modify priority_list.txt and adjust the priority numbers
         to your specifications.

         For traditional databases this parameter is ignored

     -M  CDB$ROOT is set to upgrade mode while upgrading all containers
         If -M is unspecified then CDB$ROOT defaults to normal mode
         while upgrading all containers
     -n  Maximum number of parallel SQL processes to use when upgrading the
         database. Multitenant database defaults to total number of CPUs on
         your system. Traditional database defaults to 4.
     -N  Maximum number of parallel SQL processes to use per PDB during its
         upgrade in multitenant environment defaults to 2. Ignored for
         traditional databases.
     -p  Start phase (skip successful phases on a rerun)
     -P  Stop phase (phase you want to stop on)
     -R  Automatically resumes an upgrade from the first failed phase. Automatic
         upgrade resume is turn off by default.
         This switch is mutually exclusive with -p
     -s  SQL script to initialize sessions
     -S  Run Sql in Serial Mode
            Note: As of 12.2 catupgrd.sql is not supported using this option.
     -T  Places user table space(s) in read-only mode during upgrade
     -u  username (prompts for password)
     -y  Display phases only
     -z  Turns on production catcon.pm debugging info while running this script
     -Z  Turns on catctl debug tracing while running this script. Set to number
         1 for debugging -Z 1

     filename top-level sqlplus script to run
```

**・ dbupgrade 実行**
```
[oracle@exadata-19c-tokyo1 ~]$ mkdir /home/oracle/logs
[oracle@exadata-19c-tokyo1 ~]$ dbupgrade -d $ORACLE_HOME/rdbms/admin  -c PDB12CR2_REFRESHABLE_CLONE -l /home/oracle/logs

Argument list for [/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = PDB12CR2_REFRESHABLE_CLONE
Do not run in                C = 0
Input Directory              d = /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = /home/oracle/logs
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.22.0.0.0DBRU_LINUX.X64_231229.1]


/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/logs]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/logs/catupgrd_catcon_11914.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/logs/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/logs/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 8
Database Name         = CDB19c_Tokyo
DataBase Version      = 19.0.0.0.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 8
Concurrent PDB Upgrades               = 4
Generated PDB Inclusion:[PDB12CR2_REFRESHABLE_CLONE]
CDB$ROOT  Open Mode = [OPEN]
Concurrent PDB Upgrades Reset           = 1
DataBase Version      = 19.0.0.0.0

Start processing of PDBs (PDB12CR2_REFRESHABLE_CLONE)
[/u01/app/oracle/product/19.0.0.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/catctl.pl -d /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin -c 'PDB12CR2_REFRESHABLE_CLONE' -l /home/oracle/logs -I -i pdb12cr2_refreshable_clone -n 2 /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/catupgrd.sql]

Argument list for [/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = PDB12CR2_REFRESHABLE_CLONE
Do not run in                C = 0
Input Directory              d = /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = pdb12cr2_refreshable_clone
Child Process                I = 1
Log Dir                      l = /home/oracle/logs
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 2
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.22.0.0.0DBRU_LINUX.X64_231229.1]


/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/logs]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/logs/catupgrdpdb12cr2_refreshable_clone_catcon_12377.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/logs/catupgrdpdb12cr2_refreshable_clone*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/logs/catupgrdpdb12cr2_refreshable_clone_*.lst] files for spool files, if any


Number of Cpus        = 8
Database Name         = CDB19c_Tokyo
DataBase Version      = 19.0.0.0.0
PDB12CR2_REFRESHABLE_CLONE Open Mode = [MIGRATE]
Generated PDB Inclusion:[PDB12CR2_REFRESHABLE_CLONE]
CDB$ROOT  Open Mode = [OPEN]
Components in [PDB12CR2_REFRESHABLE_CLONE]
    Installed [CATALOG CATPROC OWM RAC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM SDO WK XML XOQ]
DataBase Version      = 19.0.0.0.0

------------------------------------------------------
Phases [0-107]         Start Time:[2024_07_21 08:07:36]
Container Lists Inclusion:[PDB12CR2_REFRESHABLE_CLONE] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 16s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [PDB12CR2_REFRESHABLE_CLONE] Files:5    Time: 20s
Restart  Phase #:2    [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [PDB12CR2_REFRESHABLE_CLONE] Files:19   Time: 16s
Restart  Phase #:4    [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [PDB12CR2_REFRESHABLE_CLONE] Files:7    Time: 8s
*****************   Catproc Start   ****************
Serial   Phase #:6    [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 6s
*****************   Catproc Types   ****************
Serial   Phase #:7    [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 6s
Restart  Phase #:8    [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [PDB12CR2_REFRESHABLE_CLONE] Files:72   Time: 16s
Restart  Phase #:10   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 34s
Restart  Phase #:12   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
**************   Catproc Procedures   **************
Parallel Phase #:13   [PDB12CR2_REFRESHABLE_CLONE] Files:95   Time: 4s
Restart  Phase #:14   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Parallel Phase #:15   [PDB12CR2_REFRESHABLE_CLONE] Files:123  Time: 6s
Restart  Phase #:16   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:17   [PDB12CR2_REFRESHABLE_CLONE] Files:26   Time: 3s
Restart  Phase #:18   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
*****************   Catproc Views   ****************
Parallel Phase #:19   [PDB12CR2_REFRESHABLE_CLONE] Files:32   Time: 11s
Restart  Phase #:20   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:21   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 7s
Restart  Phase #:22   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Parallel Phase #:23   [PDB12CR2_REFRESHABLE_CLONE] Files:25   Time: 80s
Restart  Phase #:24   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Parallel Phase #:25   [PDB12CR2_REFRESHABLE_CLONE] Files:12   Time: 50s
Restart  Phase #:26   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:27   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:28   [PDB12CR2_REFRESHABLE_CLONE] Files:7    Time: 3s
Serial   Phase #:29   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Restart  Phase #:30   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Restart  Phase #:32   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:34   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [PDB12CR2_REFRESHABLE_CLONE] Files:299  Time: 13s
Serial   Phase #:36   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Restart  Phase #:37   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:38   [PDB12CR2_REFRESHABLE_CLONE] Files:10   Time: 2s
Restart  Phase #:39   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 20s
Restart  Phase #:41   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
******************   Catproc SQL   *****************
Parallel Phase #:42   [PDB12CR2_REFRESHABLE_CLONE] Files:13   Time: 48s
Restart  Phase #:43   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Parallel Phase #:44   [PDB12CR2_REFRESHABLE_CLONE] Files:11   Time: 3s
Restart  Phase #:45   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Parallel Phase #:46   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 2s
Restart  Phase #:47   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 6s
Restart  Phase #:49   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 7s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Restart  Phase #:52   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 13s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:56   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 5s
Serial   Phase #:57   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 2s
Parallel Phase #:58   [PDB12CR2_REFRESHABLE_CLONE] Files:10   Time: 3s
Parallel Phase #:59   [PDB12CR2_REFRESHABLE_CLONE] Files:25   Time: 5s
Serial   Phase #:60   [PDB12CR2_REFRESHABLE_CLONE] Files:4    Time: 7s
Serial   Phase #:61   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:62   [PDB12CR2_REFRESHABLE_CLONE] Files:32   Time: 4s
Serial   Phase #:63   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Parallel Phase #:64   [PDB12CR2_REFRESHABLE_CLONE] Files:6    Time: 7s
Serial   Phase #:65   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 11s
Serial   Phase #:66   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 14s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Serial   Phase #:69   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Parallel Phase #:70   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 2s
Restart  Phase #:71   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Parallel Phase #:72   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 2s
Serial   Phase #:73   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 2s
*****************   Upgrading SDO   ****************
Restart  Phase #:74   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Serial   Phase #:76   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Serial   Phase #:77   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 2s
Restart  Phase #:78   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:79   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 3s
Restart  Phase #:80   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Parallel Phase #:81   [PDB12CR2_REFRESHABLE_CLONE] Files:3    Time: 2s
Restart  Phase #:82   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Serial   Phase #:83   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Restart  Phase #:84   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:85   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Restart  Phase #:86   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Parallel Phase #:87   [PDB12CR2_REFRESHABLE_CLONE] Files:4    Time: 2s
Restart  Phase #:88   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Serial   Phase #:89   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Restart  Phase #:90   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 0s
Serial   Phase #:91   [PDB12CR2_REFRESHABLE_CLONE] Files:2    Time: 2s
Restart  Phase #:92   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Serial   Phase #:93   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Restart  Phase #:94   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:95   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Restart  Phase #:96   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
***********   Final Component scripts    ***********
Serial   Phase #:97   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
*************   Final Upgrade scripts   ************
Serial   Phase #:98   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 26s
*******************   Migration   ******************
Serial   Phase #:99   [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:100  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Serial   Phase #:101  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Serial   Phase #:102  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
*****************   Post Upgrade   *****************
Serial   Phase #:103  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 3s
****************   Summary report   ****************
Serial   Phase #:104  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 1s
Serial   Phase #:106  [PDB12CR2_REFRESHABLE_CLONE] Files:1    Time: 2s
Serial   Phase #:107  [PDB12CR2_REFRESHABLE_CLONE] Files:1     Time: 0s

------------------------------------------------------
Phases [0-107]         End Time:[2024_07_21 08:16:45]
Container Lists Inclusion:[PDB12CR2_REFRESHABLE_CLONE] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 549s [PDB12CR2_REFRESHABLE_CLONE]

 LOG FILES: (/home/oracle/logs/catupgrdpdb12cr2_refreshable_clone*.log)

Upgrade Summary Report Located in:
/home/oracle/logs/upg_summary.log

     Time: 569s For PDB(s)

Grand Total Time: 569s

 LOG FILES: (/home/oracle/logs/catupgrd*.log)


Grand Total Upgrade Time:    [0d:0h:9m:29s]
```





**3) Target PDB 起動**
```
[oracle@exadata-19c-tokyo1 ~]$ sql / as sysdba


    SQLcl: Release 21.4 Production on Sun Jul 21 08:18:08 2024

    Copyright (c) 1982, 2024, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
    Version 19.22.0.0.0

SQL> alter pluggable database PDB12CR2_REFRESHABLE_CLONE open;

    Pluggable database PDB12CR2_REFRESHABLE_CLONE altered.

SQL> show pdbs

   CON_ID                      CON_NAME     OPEN MODE    RESTRICTED
_________ _____________________________ _____________ _____________
        2 PDB$SEED                      READ ONLY     NO
        3 PDB_TOKYO                     READ WRITE    NO
        4 PDB12CR2_REFRESHABLE_CLONE    READ WRITE    NO
```

**4) Target PDB Version確認**
CloneしたPDBが 12cR2 (12.2) から 19c (19.22) へアップグレードされていることを確認
```
SQL> select * from v$version;

                                                                BANNER                                                                                BANNER_FULL                                                          BANNER_LEGACY    CON_ID
______________________________________________________________________ __________________________________________________________________________________________ ______________________________________________________________________ _________
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production    Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.22.0.0.0    Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production            0
```


**5）データ確認**
Sorce PDB (12cR2) で更新したデータが、Target PDB へRefleshでデータ更新していることを確認
```
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered.

SQL> alter session set container=PDB12CR2_REFRESHABLE_CLONE;

    Session altered.

SQL> select * from test_table1;

   ID                    NOW
_____ ______________________
    1 2024/07/21 15:59:19
    2 2024/07/21 16:20:18
```


## ● PDB 自動起動設定
CDBの再起動時にPDBも起動するように設定します。

**1) PDB起動状態確認**
自動起動するPDB の OPEN MODE を READ WRITE にします。
MOUNT状態の場合、OPEN してREAD WRITE にします。
```
SQL> show pdbs

   CON_ID                      CON_NAME     OPEN MODE    RESTRICTED
_________ _____________________________ _____________ _____________
        2 PDB$SEED                      READ ONLY     NO
        3 PDB_TOKYO                     READ WRITE    NO
        4 PDB12CR2_REFRESHABLE_CLONE    READ WRITE    NO
```

**2) PDB起動状態保存**
CDB 起動時に現在の PDB の OPEN MODEになるよう設定
```
SQL> alter pluggable database PDB12CR2_REFRESHABLE_CLONE save state;

Pluggable database PDB12CR2_REFRESHABLE_CLONE altered.
```


# ■ Oracle Database 23ai への Remote Clone の場合
19c 以前ではdbupgradeコマンドなどを使用した[手動アップグレード](https://mikedietrichde.com/2020/03/06/upgrade-testing-online-clone-a-pdb-to-19c/)する必要がありましたが、21c より、AutoUpgrade機能が追加されました。PDBをオープンすると、Oracle DatabaseによってPDBとCDBとの[互換性がチェック](https://docs.oracle.com/en/database/oracle/oracle-database/23/multi/administering-pdbs-with-sql-plus.html#GUID-7C114EF1-A602-4420-8C6D-483F3FAD03E2)され、PDBとCDBルートの間でバージョンの不一致があった場合は、自動的にアップグレードされます。

・参考: [Oracle Database 19c から 23ai へ PDB リモート・クローン移行してみてみた](https://qiita.com/shirok/items/258092e2024bd96899ef)

https://qiita.com/shirok/items/258092e2024bd96899ef



# ■ 参考
**・ Oracle Database Documents**
　- [リフレッシュ可能なクローンPDBについて](https://docs.oracle.com/cd/F19136_01/multi/cloning-a-pdb.html#GUID-C476B273-469B-4999-8637-A6BA1BD6300A)
　- [プラガブル・データベースのアップグレード構文](https://docs.oracle.com/cd/F19136_01/spums/about-upgrading-pluggable-databases-in-parallel.html)
　- [マルチテナント・アーキテクチャのOracle Databaseの手動アップグレード・シナリオ](https://docs.oracle.com/cd/F19136_01/upgrd/manual-upgrade-scenarios-multitenant-architecture-oracle-databases.html)
　- [新しいCDBへのPDBの抜き差しおよびアップグレード](https://docs.oracle.com/cd/E96517_01/spupu/index.html)
　- [CDB再起動時のPDBのオープン・モードの保持または破棄](https://docs.oracle.com/cd/E96517_01/multi/administering-pdbs-with-sql-plus.html#GUID-7CE00CF0-9D11-44BF-AF8A-F881D129A3F9)
　- [Oracle Database 19c の複数のリリースに渡る変更、サポート終了および非推奨リスト](https://docs.oracle.com/cd/F19136_01/upgrd/oracle-database-changes-deprecations-desupports.html#GUID-44E0A12C-2918-4565-BB19-A1CBA4C0FDCD)
　- [Oracle Database 23ai の複数のリリースに渡る変更、サポート終了および非推奨リスト](https://docs.oracle.com/en/database/oracle/oracle-database/23/upgrd/oracle-database-changes-deprecations-desupports.html#GUID-44E0A12C-2918-4565-BB19-A1CBA4C0FDCD)

**・ Blogs**
　- [Refreshable Clone PDB](http://www.brokedba.com/2021/12/19c-pdb-refreshable-clones.html)
　- [Minimal Downtime Migration and Upgrade using Refreshable PDB Clones](https://database-heartbeat.com/2021/02/09/minimal-downtime-migration-and-upgrade-using-refreshable-pdb-clones/)
　- [Upgrade Testing with a Refreshable PDB – does this work?](https://mikedietrichde.com/2020/05/05/upgrade-testing-with-a-refreshable-pdb-does-this-work/)


**・ YouTube**
　- [The concept of refreshable clones for upgrades of Oracle Database](https://www.youtube.com/watch?v=mcYO0hrWYkY)
　- [Demo of refreshable clones for upgrades of Oracle Database](https://www.youtube.com/watch?v=8Iq1L8bd7L0)