オラクル・データベースのマルチテナントの機能として提供されている、色々なクローン手順を使って、アプリケーションのテストを行う環境を作ってみます。
手順の検証に、Oracle Cloudのコンピュート・インスタンスにインストールしたOracle Database 23c Freeを使用しています。
以下の３種類のクローン方法を紹介します。
- データベース・リンク経由でリフレッシュ可能クローンを作成する。
- リフレッシュ可能クローンから更新可能なクローンを作成する。
- 更新可能クローンをリード・オンリーに変更し、そのスナップショット・コピーを作成する。
プロダクションの環境を想定しているコンピュート・インスタンスがひとつあり、PDBとしてFREEPDB1が作成済みとします。
テスト環境として想定しているコンピュート・インスタンスがひとつあり、シード以外のPDBは未作成という状態から始めます。
準備作業１ - ポート1521の通信許可
リモート・クローンに使用するデータベース・リンクを作成するため、ポート番号1521の通信を許可します。プロダクションとテストの双方のコンピュート・インスタンスで実施します。
firewalldで禁止されているTCPの1521番ポートへの接続を許可します。
firewall-cmd --add-port=1521/tcpfirewall-cmd --runtime-to-permanent
firewall-cmd --list-ports
[root@apex-prod ~]# firewall-cmd --add-port=1521/tcp
success
[root@apex-prod ~]# firewall-cmd --runtime-to-permanent
success
[root@apex-prod ~]# firewall-cmd --list-ports
1521/tcp
[root@apex-prod ~]#
Oracle Database 23c Freeのインストールでは、TNSリスナーはデフォルトでlocalhostにバインドしています。
netstat -an | grep 1521 | grep LISTEN
[oracle@apex-prod admin]$ netstat -an | grep 1521 | grep LISTEN
tcp 0 0 127.0.0.1:1521 0.0.0.0:* LISTEN
unix 2 [ ACC ] STREAM LISTENING 27618 /var/tmp/.oracle/sEXTPROC1521
[oracle@apex-prod admin]$
この状態では、localhost以外からの接続を受け付けません。
[oracle@apex-test admin]$ lsnrctl stop
LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 01-JUN-2023 09:32:51
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@apex-test admin]$
DEFAULT_SERVICE_LISTENER = FREE
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
netstat -an | grep 1521 | grep LISTEN
[oracle@apex-test admin]$ lsnrctl start
LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 01-JUN-2023 09:48:50
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Starting /opt/oracle/product/23c/dbhomeFree/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release
System parameter file is /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/apex-test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release
Start Date 01-JUN-2023 09:48:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service FREE
Listener Parameter File /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/apex-test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@apex-test admin]$ netstat -an | grep 1521 | grep LISTEN
tcp 0 0 0.0.0.0:1521 0.0.0.0:* LISTEN
unix 2 [ ACC ] STREAM LISTENING 53617 /var/tmp/.oracle/sEXTPROC1521
[oracle@apex-test admin]$
準備作業２ - アーカイブログ・モードに変更
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/product/23c/dbhomeFree/dbs/arch
Oldest online log sequence 4
Current log sequence 6
SQL>
startup mount
alter database archivelog;
shutdown
startup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1222532352 bytes
Fixed Size 10042624 bytes
Variable Size 536870912 bytes
Database Buffers 671088640 bytes
Redo Buffers 4530176 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1222532352 bytes
Fixed Size 10042624 bytes
Variable Size 536870912 bytes
Database Buffers 671088640 bytes
Redo Buffers 4530176 bytes
Database mounted.
Database opened.
SQL>
archive log list
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/product/23c/dbhomeFree/dbs/arch
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL>
準備３ - データベース・リンクの作成
drop user c##cloneadmin;
create user c##cloneadmin identified by <パスワード> container=all;
grant create session, resource, dba to c##cloneadmin container=all;
grant sysoper, create pluggable database to c##cloneadmin container=all;
SQL> drop user c##cloneadmin;
User dropped.
SQL> create user c##cloneadmin identified by <パスワード> container=all;
User created.
SQL> grant create session, resource, dba to c##cloneadmin container=all;
Grant succeeded.
SQL> grant sysoper, create pluggable database to c##cloneadmin container=all;
Grant succeeded.
SQL>
create public database link link$clone connect to c##cloneadmin identified by <c##cloneadminのパスワード> using '//<プロダクション想定のプライベートIP>/free';
SQL> drop public database link link$clone;
Database link dropped.
SQL> create public database link link$clone connect to c##cloneadmin identified by <パスワード> using '//<プライベートIP>/free';
Database link created.
SQL>
select * from dual@link$clone;
SQL> select * from dual@link$clone;
D
-
X
SQL>
リフレッシュ可能クローンの作成
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL>
drop pluggable database freepdb1 including datafiles;
SQL> alter pluggable database freepdb1 close immediate;
Pluggable database altered.
SQL> drop pluggable database freepdb1 including datafiles;
Pluggable database dropped.
SQL>
alter pluggable database freepdb1_ref open read only;
SQL> set timing on
SQL> create pluggable database freepdb1_ref from freepdb1@link$clone refresh mode manual file_name_convert = ('FREEPDB1','FREEPDB1_REF');
Pluggable database created.
Elapsed: 00:00:51.77
SQL> alter pluggable database freepdb1_ref open read only;
Pluggable database altered.
Elapsed: 00:00:00.95
SQL>
alter pluggable database freepdb2 open read write;
SQL> create pluggable database freepdb2 from freepdb1_ref file_name_convert = ('FREEPDB1_REF','FREEPDB2');
Pluggable database created.
Elapsed: 00:01:38.60
SQL> alter pluggable database freepdb2 open read write;
Pluggable database altered.
Elapsed: 00:00:06.73
SQL>
--admin-user sys \
--db-pool pdb2 --db-hostname localhost --db-port 1521 --db-servicename freepdb2 \
--feature-db-api true --feature-rest-enabled-sql true --feature-sdw true \
--gateway-mode proxied --gateway-user APEX_PUBLIC_USER --proxy-user \
--config-only
[oracle@apex-test config]$ ords --config /etc/ords/config install \
> --admin-user sys \
> --db-pool pdb2 --db-hostname localhost --db-port 1521 --db-servicename freepdb2 \
> --feature-db-api true --feature-rest-enabled-sql true --feature-sdw true \
> --gateway-mode proxied --gateway-user APEX_PUBLIC_USER --proxy-user \
> --config-only
ORDS: Release 23.1 Production on Thu Jun 01 03:18:20 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/etc/ords/config/
Enter the database password for SYS AS SYSDBA: ******
Enter the database password for ORDS_PUBLIC_USER: ******
Confirm password: ******
Oracle REST Data Services - Non-Interactive Install
The setting named: db.connectionType was set to: basic in configuration: pdb2
The setting named: db.hostname was set to: localhost in configuration: pdb2
The setting named: db.port was set to: 1521 in configuration: pdb2
The setting named: db.servicename was set to: freepdb2 in configuration: pdb2
The setting named: plsql.gateway.mode was set to: proxied in configuration: pdb2
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: pdb2
The setting named: db.password was set to: ****** in configuration: pdb2
The setting named: feature.sdw was set to: true in configuration: pdb2
The setting named: restEnabledSql.active was set to: true in configuration: pdb2
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: pdb2
2023-06-01T03:18:27.072Z INFO To run in standalone mode, use the ords serve command:
2023-06-01T03:18:27.074Z INFO ords --config /etc/ords/config serve
2023-06-01T03:18:27.074Z INFO Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
[oracle@apex-test config]$
alter session set container = freepdb2;
alter user ords_public_user identified by <ORDS_PUBLIC_USERのパスワード> account unlock;
SQL> alter session set container = freepdb2;
Session altered.
SQL> alter user ords_public_user identified by <ORDS_PUBLIC_USERのパスワード> account unlock;
User altered.
SQL>
alter pluggable database close;
alter pluggable database refresh;
alter pluggable database open read only;
SQL> alter session set container=freepdb1_ref;
Session altered.
SQL> alter pluggable database close;
Pluggable database altered.
SQL> set timing on
SQL> alter pluggable database refresh;
Pluggable database altered.
Elapsed: 00:00:03.13
SQL> alter pluggable database open read only;
Pluggable database altered.
Elapsed: 00:00:00.70
SQL>
alter session set container=cdb$root;
alter pluggable database freepdb2 close immediate;
drop pluggable database freepdb2 including datafiles;
create pluggable database freepdb2 from freepdb1_ref file_name_convert = ('FREEPDB1_REF','FREEPDB2');
alter pluggable database freepdb2 open read write;
alter session set container=freepdb2;
alter user ords_public_user identified by <ORDS_PUBLIC_USERのパスワード> account unlock;
SQL> set timing off
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database freepdb2 close immediate;
Pluggable database altered.
SQL> drop pluggable database freepdb2 including datafiles;
Pluggable database dropped.
SQL> create pluggable database freepdb2 from freepdb1_ref file_name_convert = ('FREEPDB1_REF','FREEPDB2');
Pluggable database created.
SQL> alter pluggable database freepdb2 open read write;
Pluggable database altered.
SQL> alter session set container=freepdb2;
Session altered.
SQL> alter user ords_public_user identified by <ORDS_PUBLIC_USERのパスワード> account unlock;
User altered.
SQL>
スナップショット・コピーの作成
show parameter clonedb
SQL> show parameter clonedb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE
clonedb_dir string
SQL>
shutdown immediate
startup
show parameter clonedb
SQL> alter system set clonedb=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1222532352 bytes
Fixed Size 10042624 bytes
Variable Size 805306368 bytes
Database Buffers 402653184 bytes
Redo Buffers 4530176 bytes
Database mounted.
Database opened.
SQL> show parameter clonedb
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean TRUE
clonedb_dir string
SQL>
alter pluggable database freepdb2 open read only;
set timing on
create pluggable database freepdb3 from freepdb2 file_name_convert = ('FREEPDB2','FREEPDB3');
SQL> alter pluggable database freepdb2 open read only;
Pluggable database altered.
SQL> set timing on
SQL> create pluggable database freepdb3 from freepdb2 file_name_convert = ('FREEPDB2','FREEPDB3');
Pluggable database created.
Elapsed: 00:01:40.13
SQL>
set timing on
create pluggable database freepdb3 from freepdb2 snapshot copy file_name_convert = ('FREEPDB2','FREEPDB3');
SQL> drop pluggable database freepdb3 including datafiles;
Pluggable database dropped.
SQL> set timing on
SQL> create pluggable database freepdb3 from freepdb2 snapshot copy file_name_convert = ('FREEPDB2','FREEPDB3');
Pluggable database created.
Elapsed: 00:00:01.57
SQL>
alter pluggable database freepdb3 open read write;
SQL> alter pluggable database freepdb3 open read write;
Pluggable database altered.
SQL>
--admin-user sys \
--db-pool pdb3 --db-hostname localhost --db-port 1521 --db-servicename freepdb3 \
--feature-db-api true --feature-rest-enabled-sql true --feature-sdw true \
--gateway-mode proxied --gateway-user APEX_PUBLIC_USER --proxy-user \
--config-only
[oracle@apex-test config]$ ords --config /etc/ords/config install \
> --admin-user sys \
> --db-pool pdb3 --db-hostname localhost --db-port 1521 --db-servicename freepdb3 \
> --feature-db-api true --feature-rest-enabled-sql true --feature-sdw true \
> --gateway-mode proxied --gateway-user APEX_PUBLIC_USER --proxy-user \
> --config-only
ORDS: Release 23.1 Production on Thu Jun 01 04:23:22 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/etc/ords/config/
Enter the database password for SYS AS SYSDBA: *******
Enter the database password for ORDS_PUBLIC_USER: *******
Confirm password: *******
Oracle REST Data Services - Non-Interactive Install
The setting named: db.connectionType was set to: basic in configuration: pdb3
The setting named: db.hostname was set to: localhost in configuration: pdb3
The setting named: db.port was set to: 1521 in configuration: pdb3
The setting named: db.servicename was set to: freepdb3 in configuration: pdb3
The setting named: plsql.gateway.mode was set to: proxied in configuration: pdb3
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: pdb3
The setting named: db.password was set to: ****** in configuration: pdb3
The setting named: feature.sdw was set to: true in configuration: pdb3
The setting named: restEnabledSql.active was set to: true in configuration: pdb3
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: pdb3
2023-06-01T04:23:28.870Z INFO To run in standalone mode, use the ords serve command:
2023-06-01T04:23:28.875Z INFO ords --config /etc/ords/config serve
2023-06-01T04:23:28.875Z INFO Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
[oracle@apex-test config]$
