Oracle Cloud Infrastructure (OCI) の Oracle Cloud Database の自動バックアップ機能は、ORACLEによって管理される Oracle Management Bucket (OMB) の Objeect Storage Bucketに格納され、ユーザーはこの領域にアクセスできません。自動バックアップは、Standard Object Storage バケットにのみ保存されます。
ユーザー管理の Bucket である Customer Management Bucket (CMB) に OMB のコピーが必要な場合、OMB から CMB へのコピーは、 MV2Bucketツールを利用して実行できます。
CMB に OMB コンテンツがあると、そこから Database を復元できます。
ということで、異なるテナント、異なるリージョンへ Databaseを複製してみてみます。
■ 環境
今回、異なるテナント、リージョン、VCN の DNS名 (DB_DOMAIN, GLOBAL_NAME)、Release Update (RU) へ複製してみてみます。
| パラメータ | Target DB(複製先) | Source DB(複製元) |
|---|---|---|
| Region | Osaka | Tokyo |
| Subnet DNS Name | subnet01.osaka.oraclevcn.com | subnet01.tokyo.oraclevcn.com |
| Software Edition | Enterprise Editon | Enterprise Editon |
| Release Update(RU) | 19.18.0.0.0 | 19.17.0.0.0 |
| DB_UNIQUE_NAME | cdb_prod | cdb_prod |
| GLOBAL_NAME | CDB.SUBNET01.OSAKA.ORACLEVCN.COM | CDB.SUBNET01.TOKYO.ORACLEVCN.COM |
| PDB Name | PDB | PDB |
| Database Character Set | AL32UTF8 | AL32UTF8 |
| National Character Set | AL16UTF16 | AL16UTF16 |
| Time zone | UTC | UTC |
※ DB_NAME, DB_UNIQUE_NAME を変更したい場合は、事前に OCI機能の DBシステムのクローニング もしくは、バックアップを使用した初期データベースの作成を使用して変更しておきます。
・参考: OCI: How to Change db_unique_name in Virtual Machine DB System (Doc ID 2846452.1)
■ 手順概要
複製元 Oracle Database 環境確認
複製元 Oracle Database バックアップ
複製元 DB自動バックアップを Object Storage Bucketへコピー
複製先 新規環境へ Oracle Databse Service作成
複製先 Oracle Database Cloud Backup Module for OCI 設定
複製先 TDE Wallet Location 設定
複製先 SPFILE リストア
複製先 Controlfile リストア
複製先 Database リストア/リカバリ
複製先 Database OPEN
複製先 DB_DOMAIN, GLOBAL_NAME 変更 (オプション)
複製先 Release Update (RU) の datapatch 適用(オプション)
複製先 Databse Service 起動/停止確認
参考
■ 複製元 Oracle Database環境確認
複製する Database Serviceを 複製元と同一作成にするために設定情報をメモしておきます
● OCIコンソールで Database Service設定情報確認
1) Database System 情報確認
・Time zone
・software edition
・DB system version :Release Update (RU)
2) Database 情報確認
・Database Name (CDB)
・Database unique name
・Pluugable Database Name (PDB)
・Password (SYS,SYSTEM,PSB Adomin, WAllet)
・Database Character set
・National character set
● DB にログインして設定情報確認
1) DBID, DB_NAME, DB_UNIQUE_NAME, FLASHBACK 確認
SQL> select DBID, NAME, DB_UNIQUE_NAME, CDB, OPEN_MODE, FLASHBACK_ON from V$DATABASE;
DBID NAME DB_UNIQUE_NAME CDB OPEN_MODE FLASHBACK_ON
---------- --------- ------------------------------ --- -------------------- ------------------
2245251976 CDB cdb_prod YES READ WRITE NO
2) GLOBAL_NAME, DB_DOMAIN 確認
SQL> select GLOBAL_NAME from GLOBAL_NAME;
GLOBAL_NAME
--------------------------------------------------------------------------------
CDB.SUBNET01.TOKYO.ORACLEVCN.COM
SQL> show parameter DB_DOMAIN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string subnet01.tokyo.oraclevcn.com
2) Wallet 確認
Wallet ディレクトリ、AUTOLOGIN 確認
Oracle Database Service は暗号化のため Walletが設定されています。
Walletの鍵ディレクトリと、AUTOLOGIN が設定されていることを確認
SQL> set line 120
SQL> col WRL_PARAMETER format a60
col STATUS format a8
SQL> select status, wrl_parameter, wallet_type, keystore_mode, con_id from v$encryption_wallet;
STATUS WRL_PARAMETER WALLET_TYPE KEYSTORE CON_ID
-------- ------------------------------------------------------------ -------------------- -------- ----------
OPEN /opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ AUTOLOGIN NONE 1
OPEN AUTOLOGIN UNITED 2
OPEN AUTOLOGIN UNITED 3
3) V$VERSION
SQL> select BANNER_FULL from v$version;
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
3) Database release Update Patch (RU) 確認
RU は DBA_REGISTRY_SQLPATCH View で確認
SQL> select PATCH_TYPE,ACTION, STATUS,DESCRIPTION, SOURCE_VERSION,SOURCE_BUILD_DESCRIPTION ,TARGET_VERSION, TARGET_BUILD_DESCRIPTION from DBA_REGISTRY_SQLPATCH;
PATCH_TYPE ACTION STATUS DESCRIPTION TARGET_VERSION TARGET_BUILD_DESCRIP
---------- --------------- ------------------------- ------------------------------------------------------------ --------------- --------------------
INTERIM APPLY SUCCESS OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846) 19.1.0.0.0 Feature Release
RU APPLY SUCCESS Database Release Update : 19.17.0.0.221018 (34419443) 19.17.0.0.0 Release_Update
■ 複製元 Oracle Database バックアップ
1) Database Backup
OCI コンソールを使用して、自動バックアップ もしくは Create Backupをクリックしてバックアップ
2) ORACLE*NET関連ファイル バックアップ
ORACLE_HOME/network/admin もしくは TNS_ADMIN ディレクトリ内 の ORACLE*NET関連のファイルをバックアップ
3) orapwdファイル バックアップ
ORACLE パスワード・ファイルをバックアップ
4) その他
RMANでバックアップされない必要なものをバックアップ
■ 複製元 DB自動バックアップを Object Storage Bucketへコピー
● MV2Bucketツールで自動バックアップを Object Storage Bucketへコピー
OMB から CMB へのコピーは、 次を参考に MV2Bucketツールを利用して実行します。
・参考: MV2BUCKET を使用して Oracle管理のバックアップをコピー
● Object Storage Bucket を 複製環境の Bucketへコピー(必要な場合)
Object Storage Bucket から 異なる環境の Bucketへコピーは、次を参考に RCLONEツールを利用して実行します。
・参考: Rcloneを使用して Object Storage から 異なる テナントの Object Storage へデーターをコピー
■ 複製先 新規環境へ Oracle Databse Service作成
複製先に 複製元と同じ情報でOracle Databse Service を作成し、作成される Database はダミーとして作成されるので削除します。
● Oracle Databse Service作成
複製元 Database と同一になるように値を同一にして Oracle Databse Service を作成
・Software Edition
・Database Version
・Database Unique Name
・PDB Name
・Character Set
・National Character Set
・Time Zone
・等々
● OCIコンソールで作成されたデータベースを手動で削除
Database を複製するために初期データベースを削除
1) データベース・ファイル削除スクリプト作成
スクリプトを作成して、すべてのデータベース・ファイルを削除
SQL> set heading off linesize 999 pagesize 0 feedback off trimspool on
SQL> spool /tmp/delete_ASM_files.sh
SQL> select 'asmcmd rm '||name from v$datafile
union all
select 'asmcmd rm '||name from v$tempfile
union all
select 'asmcmd rm '||member from v$logfile;
SQL> spool off
SQL> create pfile='/tmp/cdb_prod.pfile' from spfile;
SQL> host chmod 777 /tmp/delete_ASM_files.sh
SQL> host cat /tmp/delete_ASM_files.sh
SQL> select 'asmcmd rm '||name from v$datafile
2 union all
3 select 'asmcmd rm '||name from v$tempfile
4 union all
5 select 'asmcmd rm '||member from v$logfile;
asmcmd rm +DATA/cdb_prod/DATAFILE/system.291.1115821901
asmcmd rm +DATA/cdb_prod/DE03BE2347E67ADBE053C00DD10A4229/DATAFILE/system.271.1115821673
asmcmd rm +DATA/cdb_prod/DATAFILE/sysaux.297.1115821887
asmcmd rm +DATA/cdb_prod/DE03BE2347E67ADBE053C00DD10A4229/DATAFILE/sysaux.301.1115821673
asmcmd rm +DATA/cdb_prod/DATAFILE/undotbs1.292.1115821915
asmcmd rm +DATA/cdb_prod/DE03BE2347E67ADBE053C00DD10A4229/DATAFILE/undotbs1.300.1115821673
asmcmd rm +DATA/cdb_prod/DATAFILE/users.290.1115821929
asmcmd rm +DATA/cdb_prod/DATAFILE/undotbs2.289.1115821937
asmcmd rm +DATA/cdb_prod/E909A6304600B2CAE05353010A0AA253/DATAFILE/system.274.1115822297
asmcmd rm +DATA/cdb_prod/E909A6304600B2CAE05353010A0AA253/DATAFILE/sysaux.279.1115822305
asmcmd rm +DATA/cdb_prod/E909A6304600B2CAE05353010A0AA253/DATAFILE/undotbs1.264.1115822309
asmcmd rm +DATA/cdb_prod/E909A6304600B2CAE05353010A0AA253/DATAFILE/undo_2.283.1115822315
asmcmd rm +DATA/cdb_prod/E909A6304600B2CAE05353010A0AA253/DATAFILE/users.270.1115822295
asmcmd rm +DATA/cdb_prod/TEMPFILE/temp.285.1115822003
asmcmd rm +DATA/cdb_prod/E9098419BE65BF0DE05366010A0AED64/TEMPFILE/temp.299.1115821703
asmcmd rm +DATA/cdb_prod/E909A6304600B2CAE05353010A0AA253/TEMPFILE/temp.277.1115822291
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_4.261.1115821627
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_3.265.1115821627
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_2.278.1115821627
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_1.267.1115821627
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_5.298.1115822129
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_6.296.1115822131
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_7.295.1115822133
asmcmd rm +DATA/cdb_prod/ONLINELOG/group_8.294.1115822135
SQL> spool off
2) Database 停止
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3) スクリプトを実行
/tmp/delete_ASM_files.sh を実行 これで、初期データベースのすべてのファイルが削除されました。
[oracle@db19c-New ~]$ bash /tmp/delete_ASM_files.sh
[oracle@db19c-New ~]$ asmcmd ls +DATA/cdb_prod
CONTROLFILE/
DATAGUARDCONFIG/
PARAMETERFILE/
PASSWORD/
[oracle@db19c-New ~]$ asmcmd ls +DATA/cdb_prod/ONLINELOG
ASMCMD-8002: entry 'ONLINELOG' does not exist in directory '+DATAC1/cdb_prod/'
■ Oracle Database Cloud Backup Module for OCI 設定
● OCI CLI設定
Oracle Database Cloud Backup Module のインストールに必要な OCI CLIを次を参考にインストール
・参考: Oracle Database Service へ OCI コマンド・ライン・インタフェース(CLI) をインストール
● Oracle Database Cloud Backup Module
1) ダウンロード
Oracle Database Cloud Backup Module サイトから Backup Module for OCI (opc_installer.zip) をダウンロード
2) ダウンロード確認
[oracle@db19c-New ~]$ ls -l ~/opc_installer.zip
-rw-r--r-- 1 oracle oinstall 1816114 Mar 10 14:31 /home/oracle/opc_installer.zip
● Oracle Database Cloud Backup Module を複製先ホストにインストール
1) 必要ディレクトリ作成
[oracle@db19c-New ~]$ mkdir -p ~/oci_bckmodule/lib ~/oci_bckmodule/wallet
2) opc_installer.zip 解凍
[oracle@db19c-New ~]$ cd /home/oracle/
[oracle@db19c-New ~]$ unzip opc_installer.zip
Archive: opc_installer.zip
creating: opc_installer/
inflating: opc_installer/.DS_Store
creating: __MACOSX/
creating: __MACOSX/opc_installer/
inflating: __MACOSX/opc_installer/._.DS_Store
creating: opc_installer/oci_installer/
inflating: opc_installer/oci_installer/oci_readme.txt
creating: __MACOSX/opc_installer/oci_installer/
inflating: __MACOSX/opc_installer/oci_installer/._oci_readme.txt
inflating: opc_installer/oci_installer/oci_install.jar
inflating: __MACOSX/opc_installer/oci_installer/._oci_install.jar
inflating: __MACOSX/opc_installer/._oci_installer
inflating: opc_installer/readme.txt
inflating: __MACOSX/opc_installer/._readme.txt
creating: opc_installer/opc_installer/
inflating: opc_installer/opc_installer/opc_readme.txt
creating: __MACOSX/opc_installer/opc_installer/
inflating: __MACOSX/opc_installer/opc_installer/._opc_readme.txt
inflating: opc_installer/opc_installer/opc_install.jar
inflating: __MACOSX/opc_installer/opc_installer/._opc_install.jar
inflating: __MACOSX/opc_installer/._opc_installer
inflating: __MACOSX/._opc_installer
[oracle@db19c-New ~]$ cd ~/opc_installer/oci_installer
[oracle@db19c-New ~]$ ls -l
total 968
-rw-r--r-- 1 oracle oinstall 971147 Oct 17 2019 oci_install.jar
-rw-r--r-- 1 oracle oinstall 15204 Oct 18 2019 oci_readme.txt
● Oracle Database Cloud Backup インストーラー実行
1) oci_install.jar 実行時の引数を用意して実行
| 変数 | 内容 | 設定値 |
|---|---|---|
| -pvtKeyFile | OCI-CLIのoci_api_key.pemパス | /home/oracle/.oci/oci_api_key.pem |
| -pubFingerPrint | OCI-CLI設定時のAPI KeysのFingerprint | 05:d3:99:8c:2f:c9:fc:c2:78 |
| -tOCID | Tenancy OCID | ocid1.tenancy.oc1..aaaaaaaa3mb7s84ffssgbe8bsx |
| -cOCID | Compartment OCID | ocid1.compartment.oc1..aaaaaaaa2dmjokxvji6x4z |
| -uOCID | User OCID | ocid1.user.oc1..aaaaaaaadi92tk2a32gjm4yyq6hrx |
| -walletDir | Backup Module wallets パス | /home/oracle/oci_bckmodule/wallet/ |
| -libDir | Backup Module libパス | /home/oracle/oci_bckmodule/config |
| -bucket | Object Storageのバケット名 | bucket-Backup-cdb_osaka |
| -host | Object Storage End point | https://objectstorage.ap-osaka-1.oraclecloud.com |
[oracle@db19c-New ~]$ cd /home/oracle/opc_installer/oci_installer
[oracle@db19c oci_installer]$ java -jar oci_install.jar \
-pvtKeyFile /home/oracle/.oci/oci_api_key.pem \
-pubFingerPrint 05:d3:99:8c:2f:c9:fc:c2:78 \
-tOCID ocid1.tenancy.oc1..aaaaaaaa3mb7s84ffssgbe8bsx \
-cOCID ocid1.compartment.oc1..aaaaaaaa2dmjokxvji6x4z \
-uOCID ocid1.user.oc1..aaaaaaaadi92tk2a32gjm4yyq6hrx \
-walletDir ~/oci_bckmodule/wallet/ \
-libDir ~/oci_bckmodule/lib \
-configFile ~/oci_bckmodule/config \
-bucket bucket-Backup-cdb_osaka \
-host https://objectstorage.ap-osaka-1.oraclecloud.com
Oracle Database Cloud Backup Module Install Tool, build 19.3.0.0.0DBBKPCSBP_2019-10-16
Oracle Database Cloud Backup Module credentials are valid.
Backups would be sent to bucket test-bucket.
Oracle Database Cloud Backup Module wallet created in directory /opt/oracle/dcs/commonstore/wallets/cdb_prod/tde.
Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/opccdb.ora created.
Downloading Oracle Database Cloud Backup Module Software Library from Oracle Cloud Infrastructure.
Download complete.
2) 設定確認
[oracle@db19c-New ~]$ cat ~/oci_bckmodule/config
OPC_HOST=https://objectstorage.ap-osaka-1.oraclecloud.com/n/shirok
OPC_WALLET='LOCATION=file:/home/oracle/oci_bckmodule/wallet CREDENTIAL_ALIAS=alias_oci'
OPC_CONTAINER=bucket-Backup-cdb_osaka
OPC_COMPARTMENT_ID=ocid1.compartment.oc1..aaaaaaaa2dmjokxvji6x4z
OPC_AUTH_SCHEME=BMC
■ TDE Wallet Location 設定
● 既存環境の Wallet Location をリストア
Databaseは Wallet機能によって暗号化されているため既存Wallet鍵を複製先Databaseへリストアします
ソース TDE Walletを '/opt/oracle/dcs/commonstore/wallets//tde' ディレクトリにコピーします。
手動バックアップを取得した場合は '/TDEWallet.tar.gz' として CMB に保存されます。そうでない場合は、ソース ホストからWalletを取得する必要があります。
1) Bucket内に自動バックアップされた Wallet Directory確認
自動バックアップの場合、Bucket内に DB System Nameのディレクトリへ TDEWallet.tar.gz としてバックアップされています
2) 初期 TDEディレクトリ確認
[oracle@db19c-New ~]$ ls -l /opt/oracle/dcs/commonstore/wallets/cdb_prod
total 108
-rw-r--r-- 1 oracle oinstall 309 Mar 10 13:52 cdb_ocids.json
drwxr-xr-x 2 oracle oinstall 20480 Mar 10 13:52 F68CF0FF2C986458E0531300050AB9E3
drwxr-x--- 2 oracle oinstall 20480 Mar 10 13:43 tde
3) Backupした tdeディレクトリ解凍
自動バックアップ、もしくは既存DBの Wallet tde ディレクトリをリストア
[oracle@db19c-New ~]$ tar zxvf TDEWallet.tar.gz
opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ewallet_2023021301315591.p12
opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ewallet.p12.lck
opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ewallet.p12
opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ewallet_2023021301365949.p12
4) Wallet Directory リストア
[oracle@db19c-New ~]$ mv ./opt/oracle/dcs/commonstore/wallets/cdb_prod/tde /opt/oracle/dcs/commonstore/wallets/cdb_prod/
5) Wallet Directory リストア確認
[oracle@db19c-New ~]$ ls -l /opt/oracle/dcs/commonstore/wallets/cdb_prod/tde
total 16
-rw------- 1 oracle oinstall 2555 Feb 13 01:31 ewallet_2023021301315591.p12
-rw------- 1 oracle oinstall 3995 Feb 13 01:36 ewallet_2023021301365949.p12
-rw------- 1 oracle oinstall 5467 Feb 13 01:36 ewallet.p12
-rw------- 1 oracle oinstall 0 Feb 13 01:36 ewallet.p12.lck
● Wallet設定確認
1) 環境変数設定確認
ORACLE_SID, ORACLE_HOME設定確認
[oracle@db19c-New ~]$ env | grep ORA
ORACLE_UNQNAME=cdb_prod
ORACLE_SID=cdb
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
2) Dummy pfile 作成
次の内容で ダミーの Database 初期化パラメータを作成
[oracle@db19c-New ~]$ vi /home/oracle/dummy.ora
[oracle@db19c-New ~]$ cat /home/oracle/dummy.ora
db_name='cdb'
wallet_root='/opt/oracle/dcs/commonstore/wallets/cdb_prod'
tde_configuration='KEYSTORE_CONFIGURATION=FILE'
3) Dummy pfile を使用して Startup NOMOUNT
[oracle@db19c-New ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 10 14:48:09 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/dummy.ora';
ORACLE instance started.
Total System Global Area 398455080 bytes
Fixed Size 8925480 bytes
Variable Size 314572800 bytes
Database Buffers 67108864 bytes
Redo Buffers 7847936 bytes
4) AUTOLOGIN チェック
ウォレットに自動ログイン機能を設定されていることを確認
WALLET_TYPE が、AUTOLOGIN であれば、自動ログインが設定されています。
SQL> set linesize 132
SQL> col WRL_PARAMETER format a60
SQL> select status, wrl_parameter, wallet_type, keystore_mode, con_id from v$encryption_wallet;
STATUS WRL_PARAMETER WALLET_TYPE KEYSTORE CON_ID
------------------------------ ------------------------------------------------------------ -------------------- -------- ----------
CLOSED /opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ UNKNOWN NONE 0
5) AUTO LOGIN KEYSTORE 設定
自動ログインが設定されていない場合は、AUTO_LOGIN KEYSTORE を作成
パスワードは、複製元 Wallet と同じパスワードを設定します
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/oracle/dcs/commonstore/wallets/cdb_prod/tde' identified by <Wallet Password>;
keystore altered.
SQL> select status, wrl_parameter, wallet_type, keystore_mode, con_id from v$encryption_wallet;
STATUS WRL_PARAMETER WALLET_TYPE KEYSTORE CON_ID
------------------------------ ------------------------------------------------------------ -------------------- -------- ----------
OPEN /opt/oracle/dcs/commonstore/wallets/cdb_prod/tde/ AUTOLOGIN NONE 0
SQL> exit
■ SPFILE リストア
1) RMAN接続
[oracle@db19c-New ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 11 01:02:10 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (not mounted)
2）DBID設定
複製先の DBで取得したDBIDを設定
RMAN> SET DBID=2245251976;
executing command: SET DBID
3）RESTORE SPFILE TO PFILE
RMAN> run {
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/oci_bckmodule/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/oci_bckmodule/config)';
RESTORE SPFILE TO PFILE '/home/oracle/pfile.ora' FROM AUTOBACKUP MAXDAYS 10;
}
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=436 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 11-MAR-23
channel c1: looking for AUTOBACKUP on day: 20230311
channel c1: looking for AUTOBACKUP on day: 20230310
channel c1: looking for AUTOBACKUP on day: 20230309
channel c1: looking for AUTOBACKUP on day: 20230308
channel c1: looking for AUTOBACKUP on day: 20230307
channel c1: looking for AUTOBACKUP on day: 20230306
channel c1: looking for AUTOBACKUP on day: 20230305
channel c1: looking for AUTOBACKUP on day: 20230304
channel c1: looking for AUTOBACKUP on day: 20230303
channel c1: looking for AUTOBACKUP on day: 20230302
channel c1: AUTOBACKUP found: c-2245251976-20230302-01
channel c1: restoring spfile from AUTOBACKUP c-2245251976-20230302-01
channel c1: SPFILE restore from AUTOBACKUP complete
Finished restore at 11-MAR-23
released channel: c1
RMAN> exit
4）RESTOREした PFILE確認と編集
udit_file_dest などの変更可能なパラメータを必要に応じて修正
[oracle@db19c-New ~]$ cat /home/oracle/pfile.ora
cdb.__data_transfer_cache_size=0
cdb.__db_cache_size=13052674048
cdb.__inmemory_ext_roarea=0
cdb.__inmemory_ext_rwarea=0
cdb.__java_pool_size=67108864
cdb.__large_pool_size=100663296
cdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cdb.__pga_aggregate_target=3892314112
cdb.__sga_target=15569256448
cdb.__shared_io_pool_size=134217728
cdb.__shared_pool_size=2046820352
cdb.__streams_pool_size=0
cdb.__unified_pga_pool_size=0
*._datafile_write_errors_crash_instance=false
*._db_writer_coalesce_area_size=16777216
*._disable_interface_checking=TRUE
*._enable_numa_support=FALSE
*._file_size_increase_increment=2143289344
*._fix_control='18960760:on'
*._gc_policy_time=20
*._gc_undo_affinity=TRUE
*.audit_file_dest='/u01/app/oracle/admin/cdb_prod/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='19.0.0.0'
*.control_file_record_keep_time=40
*.control_files='+RECO/cdb_prod/CONTROLFILE/current.256.1128648457'
*.control_management_pack_access='DIAGNOSTIC+TUNING'
*.cpu_count=0
*.cursor_sharing='EXACT'
*.db_block_checking='OFF'
*.db_block_checksum='TYPICAL'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RECO'
*.db_domain='subnet01.tokyo.oraclevcn.com'
*.db_files=1024
*.db_lost_write_protect='TYPICAL'
*.db_name='cdb'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=255g
*.db_unique_name='cdb_prod'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdbXDB)'
*.enable_ddl_logging=TRUE
*.enable_pluggable_database=true
*.encrypt_new_tablespaces='ALWAYS'
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=TRUE
*.local_listener='LISTENER_CDB'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=134217728
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.os_authent_prefix='ops$'
*.parallel_execution_message_size=16384
*.parallel_threads_per_cpu=2
*.pga_aggregate_limit=7424m
*.pga_aggregate_target=3712m
*.processes=400
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=100
*.sga_target=14848m
*.spatial_vector_acceleration=TRUE
*.sql92_security=TRUE
*.tde_configuration='keystore_configuration=FILE'
*.undo_retention=900
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='only'
*.wallet_root='/opt/oracle/dcs/commonstore/wallets/cdb_prod'
● Create SPFILE from PFILE
今回, SPFILEを $ORACLE_HOME/dbs/spfile.ora へ作成
1) orapasswdファイル・リストア
複製先 Databse passwd ファイルを $ORACLE_HOME/dbs/ へリストア
2) SPFILE作成
SQL> create spfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfilecdb.ora' from pfile='/home/oracle/pfile.ora';
File created.
3) orapasswd と SPFILE確認
SQL> host ls -l /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/
total 20
-rw-rw---- 1 oracle asmadmin 1544 Mar 10 14:48 hc_cdb.dat
-rw-r--r-- 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r----- 1 oracle oinstall 2048 Mar 10 13:34 orapwcdb
-rw-r----- 1 oracle asmadmin 5632 Mar 10 14:56 spfilecdb.ora
4) Database NOMOUNT起動
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 1.5569E+10 bytes
Fixed Size 9181888 bytes
Variable Size 2214592512 bytes
Database Buffers 1.3187E+10 bytes
Redo Buffers 158588928 bytes
5) 起動SPFILE確認
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfilecdb.ora
■ Controlfile リストア
1) RMAN接続
[oracle@db19c-New ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Mar 10 15:03:19 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (not mounted)
2) DBIDセット
RMAN> SET DBID=2245251976;
executing command: SET DBID
3) Restore Controlfile
RMAN> run {
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/oci_bckmodule/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/oci_bckmodule/config)';
restore controlfile from autobackup MAXDAYS 10;
}
allocated channel: c1
channel c1: SID=482 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 11-MAR-23
channel c1: looking for AUTOBACKUP on day: 20230311
channel c1: looking for AUTOBACKUP on day: 20230310
channel c1: looking for AUTOBACKUP on day: 20230309
channel c1: looking for AUTOBACKUP on day: 20230308
channel c1: looking for AUTOBACKUP on day: 20230307
channel c1: looking for AUTOBACKUP on day: 20230306
channel c1: looking for AUTOBACKUP on day: 20230305
channel c1: looking for AUTOBACKUP on day: 20230304
channel c1: looking for AUTOBACKUP on day: 20230303
channel c1: looking for AUTOBACKUP on day: 20230302
channel c1: AUTOBACKUP found: c-2245251976-20230302-01
channel c1: restoring control file from AUTOBACKUP c-2245251976-20230302-01
channel c1: control file restore from AUTOBACKUP complete
output file name=+RECO/cdb_prod/CONTROLFILE/current.269.1131153015
Finished restore at 11-MAR-23
released channel: c1
5) Datababase MOUNT
RMAN> ALTER DATABASE MOUNT;
Statement processed
■ Database リストア/リカバリ
● Restore Database
RMAN> run {
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/oci_bckmodule/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/oci_bckmodule/config)';
RESTORE DATABASE;
}
allocated channel: c1
channel c1: SID=469 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting restore at 11-MAR-23
Starting implicit crosscheck backup at 11-MAR-23
Crosschecked 1 objects
Finished implicit crosscheck backup at 11-MAR-23
Starting implicit crosscheck copy at 11-MAR-23
Finished implicit crosscheck copy at 11-MAR-23
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +RECO/cdb_prod/ARCHIVELOG/2023_03_10/thread_1_seq_2.262.1131114177
File Name: +RECO/cdb_prod/AUTOBACKUP/2023_03_10/n_1131116972.267.1131116973
File Name: +RECO/cdb_prod/CONTROLFILE/backup.260.1131112497
File Name: +RECO/cdb_prod/CONTROLFILE/current.268.1131118129
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_051m0sml_5_1_1_20230302_1130394325_set5"
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_041m0sml_4_1_1_20230302_1130394325_set4"
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_071m0sne_7_1_1_20230302_1130394350_set7"
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_081m0snl_8_1_1_20230302_1130394357_set8"
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_091m0snn_9_1_1_20230302_1130394359_set9"
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_061m0sne_6_1_1_20230302_1130394350_set6"
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to +DATA/cdb_prod/DATAFILE/sysaux.261.1128648409
channel c1: restoring datafile 00011 to +DATA/cdb_prod/DATAFILE/users.271.1128649159
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_041m0sml_4_1_1_20230302_1130394325_set4
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_041m0sml_4_1_1_20230302_1130394325_set4 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to +DATA/cdb_prod/DATAFILE/system.260.1128648375
channel c1: restoring datafile 00004 to +DATA/cdb_prod/DATAFILE/undotbs1.262.1128648425
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_051m0sml_5_1_1_20230302_1130394325_set5
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_051m0sml_5_1_1_20230302_1130394325_set5 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00009 to +DATA/cdb_prod/F48BE137EC241AD4E0531300010A76A8/DATAFILE/sysaux.270.1128649033
channel c1: restoring datafile 00010 to +DATA/cdb_prod/F48BE137EC241AD4E0531300010A76A8/DATAFILE/undotbs1.275.1128649165
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_061m0sne_6_1_1_20230302_1130394350_set6
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_061m0sne_6_1_1_20230302_1130394350_set6 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to +DATA/cdb_prod/EB3A921CAD1A96F8E0538C01F40A7C7C/DATAFILE/system.264.1128648507
channel c1: restoring datafile 00007 to +DATA/cdb_prod/EB3A921CAD1A96F8E0538C01F40A7C7C/DATAFILE/undotbs1.266.1128648507
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_071m0sne_7_1_1_20230302_1130394350_set7
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_071m0sne_7_1_1_20230302_1130394350_set7 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00006 to +DATA/cdb_prod/EB3A921CAD1A96F8E0538C01F40A7C7C/DATAFILE/sysaux.265.1128648507
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_081m0snl_8_1_1_20230302_1130394357_set8
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_081m0snl_8_1_1_20230302_1130394357_set8 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00008 to +DATA/cdb_prod/F48BE137EC241AD4E0531300010A76A8/DATAFILE/system.273.1128649025
channel c1: restoring datafile 00012 to +DATA/cdb_prod/F48BE137EC241AD4E0531300010A76A8/DATAFILE/users.274.1128649159
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_091m0snn_9_1_1_20230302_1130394359_set9
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_091m0snn_9_1_1_20230302_1130394359_set9 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
Finished restore at 11-MAR-23
released channel: c1
● Recover Database
RMAN> run {
allocate channel c1 device type sbt PARMS 'SBT_LIBRARY=/home/oracle/oci_bckmodule/lib/libopc.so, SBT_PARMS=(OPC_PFILE=/home/oracle/oci_bckmodule/config)';
RECOVER DATABASE UNTIL AVAILABLE REDO;
}
allocated channel: c1
channel c1: SID=469 device type=SBT_TAPE
channel c1: Oracle Database Backup Service Library VER=19.0.0.1
Starting recover at 11-MAR-23
starting media recovery
new media label is "objectstorag~.ap-osaka-1.oraclecloud.com/n/orasejap~/test-bucket" for piece "DBTLongterm1677736551682J8O__CDB_2245251976_0a1m0snu_10_1_1_20230302_1130394366_set10"
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=7
channel c1: reading from backup piece DBTLongterm1677736551682J8O__CDB_2245251976_0a1m0snu_10_1_1_20230302_1130394366_set10
channel c1: piece handle=DBTLongterm1677736551682J8O__CDB_2245251976_0a1m0snu_10_1_1_20230302_1130394366_set10 tag=DBTLONGTERM1677736551682J8O
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
archived log file name=+RECO/cdb_prod/ARCHIVELOG/2023_03_11/thread_1_seq_7.270.1131153117 thread=1 sequence=7
channel default: deleting archived log(s)
archived log file name=+RECO/cdb_prod/ARCHIVELOG/2023_03_11/thread_1_seq_7.270.1131153117 RECID=7 STAMP=1131153117
warning: attempt media recovery until thread 1, sequence 8
Finished recover at 11-MAR-23
released channel: c1
● CHANGE TRACKING 再設定
1) BLOCK CHANGE TRACKING 無効化
RMAN> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Statement processed
2) 無効化確認
SQL> col FILENAME format a50
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME
---------- --------------------------------------------------
DISABLED
3) BLOCK CHANGE TRACKING設定
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING; USING FILE '+DATA/cdb_prod/CHANGETRACKING/rman_change_track.f';
もしくは
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Statement processed
SQL> col FILENAME format a60
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
STATUS FILENAME
---------- --------------------------------------------------
ENABLED +DATA/cdb_prod/CHANGETRACKING/ctf.327.1131196219
■ Database OPEN
RESETLOGS を付与して DATABASE OPEN
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.17.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.17.00.00 in TARGET database is not current
■ DB_DOMAIN, GLOBAL_NAME 変更 (オプション)
複製先で作成した Database Service コンソール表示 と異なる DB_DOMAIN で Restore/Recovery された場合、コンソール、CRS/Grid と合わせるために Database の DB_DOMAIN, GLOBAL_NAME 変更をします
● CRS/Grid 確認
CRS/Grid の Domain は subnet01.osaka.oraclevcn.com で設定されていることを確認できます。
1) nodeapps 確認
[oracle@db19c tmp]$ srvctl config nodeapps -a | grep Name
IP Name: db19c-vip.SUBNET01.osaka.oraclevcn.com
2) scan 確認
[oracle@db19c tmp]$ srvctl config scan
SCAN name: db19c-scan, Network: 1
Subnet IPv4: 10.5.0.0/255.255.255.0/ens3, static
Subnet IPv6:
SCAN 1 IPv4 VIP: 10.5.0.19
SCAN VIP is disabled.
3) config database 確認
[oracle@db19c tmp]$ srvctl config database -d cdb_prod -a | grep Domain
Domain: SUBNET01.osaka.oraclevcn.com
● ORACLE*NET確認
SERVICE_NAME は subnet01.osaka.oraclevcn.com で設定されていることを確認できます。
1) tnsname.ora 確認
[oracle@db19c ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora | grep SERVICE_NAME
(SERVICE_NAME = cdb_prod.subnet01.osaka.oraclevcn.com)
● 複製 Database 確認
Database の Domain は SUBNET01.tokyo.oraclevcn.com で設定されていることを確認できます。
1) DB_DOMAIN 確認
SQL> show parameter DB_DOMAIN
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string SUBNET01.tokyo.oraclevcn.com
2) GLOBAL_NAME 確認
SQL> select GLOBAL_NAME from GLOBAL_NAME;
GLOBAL_NAME
--------------------------------------------------------------------------------
CDB.SUBNET01.TOKYO.ORACLEVCN.COM
● DB_DOMAIN,GLOBAL_NAME 変更
1) DB_DOMAIN 変更
SQL> alter system set db_domain='SUBNET01.osaka.oraclevcn.com' scope=spfile;
System altered.
2) GLOBAL_NAME 変更
SQL> ALTER DATABASE RENAME GLOBAL_NAME to "CDB.SUBNET01.OSAKA.ORACLEVCN.COM";
Database altered.
● 設定反映 Database 再起動
[oracle@db19c ~]$ srvctl stop db -d cdb_prod
[oracle@db19c ~]$ srvctl start db -d cdb_prod
[oracle@db19c ~]$ srvctl status db -d cdb_prod
Instance cdb is running on node db19c
● 変更確認
1) db_domain 変更確認
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string SUBNET01.OSAKA.ORACLEVCN.COM
2) GLOBAL_NAME 変更確認
SQL> select GLOBAL_NAME from GLOBAL_NAME;
GLOBAL_NAME
--------------------------------------------------------------------------------
CDB.SUBNET01.OSAKA.ORACLEVCN.COM
■ Release Update (RU) の datapatch 適用(オプション)
Databaseの Restor/Recovery は、複製元のRU Versionで複製されます。
DB System Version (DB_HOME Version) と Restor/Recoveryした DB Versionが異なる場合、それぞれの Version を揃える必要があります。そのため、パッチの Redmeの説明に従ってパッチが適用されている同じ ORACLE_HOMEで実行されている個別のデータベースごとに、datapatchユーティリティ等を実行しVersionを揃えます。
※ パッチの Redme は My Oracle Supprt からダウンロードして手順を確認し実行します。
今回、RU 19.18.0.0.0 の Single/Multitenant (CDB/PDB) DB の手順で datapatch を実行してみてみます。
● Release Update (RU) Version 確認
今回、複製先の DB System Version 19.18.0.0.0 ですが Database Versionは、19.17.0.0 です。
1) DB System Version 確認
[oracle@db19c-New ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 13 05:31:44 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> select BANNER_FULL from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
2) Database Version 確認
SQL> set lin 300
SQL> col DESCRIPTION format a60
SQL> col PATCH_DESCRIPTOR format a50
SQL> col SOURCE_BUILD_DESCRIPTION format a20
SQL> col TARGET_BUILD_DESCRIPTION format a20
SQL> select PATCH_TYPE,ACTION, STATUS,DESCRIPTION, TARGET_VERSION, TARGET_BUILD_DESCRIPTION from DBA_REGISTRY_SQLPATCH;
PATCH_TYPE ACTION STATUS DESCRIPTION TARGET_VERSION TARGET_BUILD_DESCRIP
---------- --------------- ------------------------- ------------------------------------------------------------ --------------- --------------------
INTERIM APPLY SUCCESS OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846) 19.1.0.0.0 Feature Release
RU APPLY SUCCESS Database Release Update : 19.17.0.0.221018 (34419443) 19.17.0.0.0 Release_Update
● Release Update (RU) datapatch 適用
1) CDB と PDB 起動確認
SQL> sqlplus / as sysdba
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
2) datapatch 適用
datapatchは、$ORACLE_HOME/OPatch/datapatch にあります。
[oracle@db19c-New ~]$ cd $ORACLE_HOME/OPatch
[oracle@db19c-New OPatch]$ ls -l datapatch
-rwxr-x--- 1 oracle oinstall 589 Jan 11 11:43 datapatch
[oracle@db19c-New OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.18.0.0.0 Production on Mon Mar 13 05:26:15 2023
Copyright (c) 2012, 2023, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_83841_2023_03_13_05_26_15/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 34411846 (OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846)):
Binary registry: Not installed
PDB CDB$ROOT: Applied successfully on 17-OCT-22 12.44.17.790781 PM
PDB PDB: Applied successfully on 17-OCT-22 12.44.26.903098 PM
PDB PDB$SEED: Applied successfully on 17-OCT-22 12.44.26.903098 PM
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
Binary registry: Installed
PDB CDB$ROOT: Not installed
PDB PDB: Not installed
PDB PDB$SEED: Not installed
Current state of release update SQL patches:
Binary registry:
19.18.0.0.0 Release_Update 230127005551: Installed
PDB CDB$ROOT:
Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 17-OCT-22 12.44.17.785984 PM
PDB PDB:
Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 17-OCT-22 12.44.26.898847 PM
PDB PDB$SEED:
Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 17-OCT-22 12.44.26.898847 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB
The following interim patches will be rolled back:
34411846 (OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846))
Patch 34765931 (DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)):
Apply from 19.17.0.0.0 Release_Update 220924224051 to 19.18.0.0.0 Release_Update 230127005551
The following interim patches will be applied:
34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990))
Installing patches...
Patch installation complete. Total patches installed: 9
Validating logfiles...done
Patch 34411846 rollback (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB_CDBROOT_2023Mar13_05_26_58.log (no errors)
Patch 34765931 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_CDB_CDBROOT_2023Mar13_05_27_48.log (no errors)
Patch 34786990 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CDB_CDBROOT_2023Mar13_05_27_48.log (no errors)
Patch 34411846 rollback (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB_PDBSEED_2023Mar13_05_28_41.log (no errors)
Patch 34765931 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_CDB_PDBSEED_2023Mar13_05_28_56.log (no errors)
Patch 34786990 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CDB_PDBSEED_2023Mar13_05_28_56.log (no errors)
Patch 34411846 rollback (pdb PDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34411846/24917919/34411846_rollback_CDB_PDB_2023Mar13_05_28_41.log (no errors)
Patch 34765931 apply (pdb PDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_CDB_PDB_2023Mar13_05_28_56.log (no errors)
Patch 34786990 apply (pdb PDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_CDB_PDB_2023Mar13_05_28_56.log (no errors)
SQL Patching tool complete on Mon Mar 13 05:29:44 2023
3）datapatch適用確認
[oracle@db19c-New OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 13 05:31:44 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> select PATCH_TYPE,ACTION, STATUS,DESCRIPTION, TARGET_VERSION, TARGET_BUILD_DESCRIPTION from DBA_REGISTRY_SQLPATCH;
PATCH_TYPE ACTION STATUS DESCRIPTION TARGET_VERSION TARGET_BUILD_DESCRIP
---------- --------------- ------------------------- ------------------------------------------------------------ --------------- --------------------
INTERIM APPLY SUCCESS OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846) 19.1.0.0.0 Feature Release
RU APPLY SUCCESS Database Release Update : 19.17.0.0.221018 (34419443) 19.17.0.0.0 Release_Update
INTERIM ROLLBACK SUCCESS OJVM RELEASE UPDATE: 19.17.0.0.221018 (34411846) 19.18.0.0.0 Release_Update
INTERIM APPLY SUCCESS OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990) 19.17.0.0.0 Release_Update
RU APPLY SUCCESS DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) ( 19.18.0.0.0 Release_Update
4）INVALID Object確認
無効になっている オブジェクトを確認し、もし存在すれば、パッチの Redme にある utlrp.sql など実行してリコンパイルして有効化
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS from DBA_OBJECTS where status != 'VALID' ;
no rows selected
■ Databse Service 起動/停止確認
● OCIコンソールでのノード起動/停止
OCIコンソールでノード起動/停止確認
● Oracle Clusterware での DB起動/停止確認
1) Database 停止
[grid@db19c ~]$ srvctl stop db -d cdb_prod
[grid@db19c ~]$ srvctl status db -d cdb_prod
Instance cdb is not running on node db19c
2) Database 起動
[grid@db19c ~]$ srvctl start db -d cdb_prod
3) Clusterware DB登録情報確認
[grid@db19c ~]$ srvctl config database -d cdb_prod
Database unique name: cdb_prod
Database name: cdb
Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfilecdb.ora
Password file:
Domain: subnet01.tokyo.oraclevcn.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths: /opt/oracle/dcs/commonstore
Services: cdb_pdb.paas.oracle.com
Type: SINGLE
OSDBA group: dba
OSOPER group: dbaoper
Database instance: cdb
Configured nodes: db19c
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
4) CDB 確認
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
5) PDB 確認
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
6) SPFILE 確認
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfilecdb.ora
7) DBID, DB_UNIQUE_NAME, FLASHBACK 確認
SQL> select DBID, NAME, DB_UNIQUE_NAME, CDB, OPEN_MODE, FLASHBACK_ON from v$database;
DBID NAME DB_UNIQUE_NAME CDB OPEN_MODE FLASHBACK_ON
---------- --------- ------------------------------ --- -------------------- ------------------
2245251976 CDB cdb_prod YES READ WRITE NO
■ 参考
・ Oracle Database Backup Cloud Service
・ Oracle Database Backup Cloud Serviceのバックアップ・モジュールのインストール
・ Oracle Database Backup Cloud Service: 新しいデータベース・ホストへのリストア
・ Ensure Disaster Recovery by replicating Automatic Backups across Regions in Oracle Cloud
・ データベース管理者ガイド: グローバル・データベース名の決定
・ My Oracle Support: (OCI) How to restore a Database using a copy of OMB content done with MV2Bucket (Doc ID 2919463.1)
・ My Oracle Support: My Oracle Support: Patch 34765931: DATABASE RELEASE UPDATE 19.18.0.0.0 (REL-JAN230131)
・ My Oracle Support: OCI: How to Change db_unique_name in Virtual Machine DB System (Doc ID 2846452.1)
・ My Oracle Support: How to Change the Domain Name for a RAC Database Server (Doc ID 1059776.1)