Oracle Database 19c: Create Database 文で Databaseを作成してみてみた

CREATE DATABASE SQL文の使用は、Oracle Database Configuration Assistant (DBCA)を使用するよりも手動操作の多いデータベース作成アプローチです。DBCAのかわりにこの文を使用する利点は、スクリプト内からデータベースを作成できることです。
ということで、Create Database文 で Container Databases(CDB)を作成して、Pluggable Database (PDB)作成、SQL*NET接続、RMANバックアップなど一連操作をしてみてみます。

■ Oracle Database 19cインストール

Oracle Cloud Market Place にOracle Database 19c があるのでこれを使用します。

・参考: Oracle Cloud: 東京-大阪リージョン間で手動Data Guardしてみてみた

■ ORACLE OSユーザーログイン

Oracle DatabaseをインストールしたOSユーザーへログイン

[oracle@db19c ~]$ id -a
	uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba) 

■ 環境変数設定

Creata Database実行前に インスタンス識別子（SID）を指定し、必要な環境変数が設定されていることを確認

・ 環境変数設定

[oracle@db19c ~]$ vi .bashrc
	export ORACLE_BASE=/u01/app/oracle
	export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
	export PATH=$PATH:/u01/app/oracle/product/19c/dbhome_1/bin
	export LD_LIBRARY_PATH=/u01/app/oracle/product/19c/dbhome_1/lib
	export ORACLE_UNQNAME=CDB
	export ORACLE_SID=CDB
	#export NLS_LANG=JAPANESE_JAPAN.UTF8
	export NLS_LANG=AMERICAN_AMERICA.UTF8

・ 環境変数確認

[oracle@db19c ~]$ env | grep ORA
	ORACLE_UNQNAME=CDB
	ORACLE_SID=CDB
	ORACLE_BASE=/u01/app/oracle
	ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
[oracle@db19c ~]$ env | grep LAN
	NLS_LANG=AMERICAN_AMERICA.UTF8
	LANG=en_US.UTF-8

■ データベース管理者の認証方法

次の方法で、必要な権限を持つ管理者として認証できます。
・パスワードファイル付き
・オペレーティングシステム認証あり

● passwdファイル作成

パスワード・ファイル認証を使用した接続パスワード・ファイル認証を使用すると、管理ユーザーは、SQL * PlusCONNECTコマンドを使用して、ローカルまたはリモートのデータベースに接続および認証できます。デフォルトでは、パスワードでは大文字と小文字が区別されます。

[oracle@db19c dbs]$ cd $ORACLE_HOME/dbs
[oracle@db19c dbs]$ orapwd FILE=orapwCDB FORMAT=12.2

	Enter password for SYS: sys_password

■ 初期化パラメータファイル作成

CDBを作成するには、ENABLE_PLUGGABLE_DATABASE初期化パラメータがTRUEに設定されている必要
また、db_block_size はCDBの作成時にDB_BLOCK_SIZE初期化パラメータによって指定しますが、CDBの作成後は変更できません。
その他のパラメーターは最低限以下のようなものを設定しておきます。
パラメーターの内容は データベース・リファレンスを参照

[oracle@db19c admin]$ cd $ORACLE_HOME/dbs
[oracle@db19c admin]$ cat initCDB.ora
	*.audit_file_dest='/u01/app/oracle/admin/CDB/adump'
	*.audit_trail='db'
	*.compatible='19.0.0.0'
	*.control_management_pack_access='DIAGNOSTIC+TUNING'
	*.control_files='/u01/app/oracle/oradata/CDB/controlfile/control01.ctl','/u01/app/oracle/oradata/CDB/controlfile/control02.ctl'
	*.db_block_size=8192
	*.db_name='CDB'
	*.diagnostic_dest='/u01/app/oracle'
	*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBXDB)'
	*.fast_start_mttr_target=300
	*.filesystemio_options='setall'
	*.local_listener='LISTENER_CDB'
	*.log_archive_format='%t_%s_%r.dbf'
	*.log_buffer=33554432
	*.nls_language='JAPANESE'
	*.nls_territory='JAPAN'
	*.open_cursors=1000
	*.pga_aggregate_limit=14592m
	*.pga_aggregate_target=7296m
	*.processes=800
	*.remote_login_passwordfile='EXCLUSIVE'
	*.sga_target=4638m
	*.undo_retention=900
	*.undo_tablespace='UNDOTBS1'
	*.parallel_degree_policy=AUTO
	*.DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'
	*.ENABLE_PLUGGABLE_DATABASE=TRUE

● tnsnames.ora設定

LOCAL_LISTENER の情報を必要に応じて設定

[oracle@db19c admin]$ cat tnsnames.ora
	# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
	# Generated by Oracle configuration tools.

	LISTENER_CDB =
	(ADDRESS = (PROTOCOL = TCP)(HOST = db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT = 1521))

	CDB =
	(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT = 1521))
		(CONNECT_DATA =
		(SERVER = DEDICATED)
		(SERVICE_NAME = CDB)
		)
	)

■ Database ディレクトリ作成

mkdir -p /u01/app/oracle/admin/CDB/adump
mkdir -p /u01/app/oracle/oradata/CDB/
mkdir -p /u01/app/oracle/oradata/CDB/controlfile
mkdir -p /u01/app/oracle/oradata/CDB/datafile
mkdir -p /u01/app/oracle/oradata/CDB/onlinelog
mkdir -p /u01/app/oracle/oradata/CDB/pdbseed
mkdir -p /u01/app/oracle/oradata/CDB/pdbseed/datafile

■ インスタンス接続

[oracle@db19c ~]$ sqlplus / as sysdba

	SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 7 01:35:00 2021
	Version 19.11.0.0.0

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

	Connected to an idle instance.

	SQL>

■ spfile作成

initCDB.ora から spfileCDB.ora ファイル作成
サーバーパラメータファイルを使用すると、ALTER SYSTEMコマンドを使用して初期化パラメータを変更し、データベースのシャットダウンと起動の間、変更を永続化できます。編集したテキスト初期化ファイルからサーバーパラメータファイルを作成します。

1) spfile作成

SQL> create spfile from pfile;

	File created.

2) spfile作成確認

SQL>  host ls -l $ORACLE_HOME/dbs
	total 8
	-rw-r--r--. 1 oracle oinstall  656 Aug  4 13:53 initCDB.ora
	-rw-r--r--. 1 oracle oinstall 3079 May 14  2015 init.ora
	-rw-r-----. 1 oracle oinstall 6144 Aug  4 13:39 orapwCDB
	-rw-r-----. 1 oracle dba      2560 Aug  4 13:54 spfileCDB.ora

■ Instanceを NOMOUNT起動

NOMOUNT句を指定してSTARTUPコマンドを実行
1) sysdba接続

[oracle@db19c oradata]$ sqlplus / as sysdba

	SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 5 14:01:08 2021
	Version 19.11.0.0.0

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

	Connected to an idle instance.

2) NOMOUNT起動

SQL> startup nomount
		ORACLE instance started.

	Total System Global Area 4865390928 bytes
	Fixed Size		    9144656 bytes
	Variable Size		  922746880 bytes
	Database Buffers	 3925868544 bytes
	Redo Buffers		    7630848 bytes

■ CREATE DATABASEステートメント発行

以下変更できない設定を事前に決めて Create Dataabseを実行

DB名
CHARACTER SET と NATIONAL CHARACTER SET 
REDOログ・ブロック・サイズ

SQL> CREATE DATABASE CDB
	USER SYS IDENTIFIED BY sys_password
	USER SYSTEM IDENTIFIED BY system_password
	LOGFILE   GROUP 1 ('/u01/app/oracle/oradata/CDB/onlinelog/redo01a.log','/u01/app/oracle/oradata/CDB/onlinelog/redo01b.log') SIZE 100M BLOCKSIZE 512,
			GROUP 2 ('/u01/app/oracle/oradata/CDB/onlinelog/redo02a.log','/u01/app/oracle/oradata/CDB/onlinelog/redo02b.log') SIZE 100M BLOCKSIZE 512,
			GROUP 3 ('/u01/app/oracle/oradata/CDB/onlinelog/redo03a.log','/u01/app/oracle/oradata/CDB/onlinelog/redo03b.log') SIZE 100M BLOCKSIZE 512
	MAXLOGHISTORY 1
	MAXLOGFILES 16
	MAXLOGMEMBERS 3
	MAXDATAFILES 1024
	CHARACTER SET AL32UTF8
	NATIONAL CHARACTER SET AL16UTF16
	EXTENT MANAGEMENT LOCAL
	DATAFILE '/u01/app/oracle/oradata/CDB/datafile/system01.dbf'
		SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
	SYSAUX DATAFILE '/u01/app/oracle/oradata/CDB/datafile/sysaux01.dbf'
		SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
	DEFAULT TABLESPACE users
		DATAFILE '/u01/app/oracle/oradata/CDB/datafile/users01.dbf'
		SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
	DEFAULT TEMPORARY TABLESPACE tempts1
		TEMPFILE '/u01/app/oracle/oradata/CDB/datafile/temp01.dbf'
		SIZE 20M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
	UNDO TABLESPACE undotbs1
		DATAFILE '/u01/app/oracle/oradata/CDB/datafile/undotbs01.dbf'
		SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
	ENABLE PLUGGABLE DATABASE
	SEED FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB/','/u01/app/oracle/oradata/CDB/pdbseed/')
		SYSTEM DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
		SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
	LOCAL UNDO ON
	SET TIME_ZONE='Asia/Tokyo'
	;

● catcdb.sql SQLスクリプトを実行

最初のプロンプトに対して/tmpを、2番目のプロンプトに対してcreate_cdb.logを入力
その他管理者パスワードや一時表領域名の入力を求めるプロンプトが表示されたら必要な他の情報を入力

SQL> host export PATH=$ORACLE_HOME/perl/bin:$PATH
SQL> @?/rdbms/admin/catcdb.sql
	・・・
	
	SQL>
	SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
	Enter value for 1: /tmp
	Enter value for 2: create_cdb.log
	Enter new password for SYS: sys_password
	Enter new password for SYSTEM: system_password
	Enter temporary tablespace name: TEMP
	
	・・・

	catcon::sureunlink: confirmed that /u01/app/oracle/oradata/catcdb__catcon_3876_exec_DB_script.done no longer exists after 1 attempts

	catcon::exec_DB_script: deleted /u01/app/oracle/oradata/catcdb__catcon_3876_exec_DB_script.done after running a script

	catcon::exec_DB_script: closed Reader

※以下が表示された場合は、export PATH=$ORACLE_HOME/perl/bin:$PATH を設定して再実行

Can't locate Term/ReadKey.pm in @INC (@INC contains: /u01/app/oracle/product/19c/dbhome_1/rdbms/admin /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /u01/app/oracle/product/19c/dbhome_1/rdbms/admin/catcdb.pl line 37.
BEGIN failed--compilation aborted at /u01/app/oracle/product/19c/dbhome_1/rdbms/admin/catcdb.pl line 37.

■ ACHIVE LOGモード,FLASH BACK Database等設定

● ACHIVE LOGモード設定

1) flash_recovery_areaディレクトリ作成

SQL> host mkdir -p /u01/app/oracle/oradata/CDB/flash_recovery_area
SQL> ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/oradata/CDB/flash_recovery_area' scope=both;

	System altered.

2) db_recovery_file_dest_size パラメータ設定

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=100G scope=both;

	System altered.

3) log_archive_dest_1 パラメータ設定

SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;

	System altered.

4) log_archive_formatパラメータ設定

    SQL> ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' scope=BOTH;

        System altered.

5) MOUNTモード切替
ARCHIVELOGモード設定するためにMOUNTモードに切り替えます

SQL> SHUTDOWN IMMEDIATE
	Database closed.
	Database dismounted.
	ORACLE instance shut down.

SQL> STARTUP MOUNT
	ORACLE instance started.

	Total System Global Area 4865390928 bytes
	Fixed Size		    9144656 bytes
	Variable Size		  922746880 bytes
	Database Buffers	 3925868544 bytes
	Redo Buffers		    7630848 bytes
	Database mounted.

6) ARCHIVELOGモード設定

SQL> ALTER DATABASE ARCHIVELOG;

	Database altered.

7) ARCHIVELOGモード設定確認

SQL> col NAME  format a10
SQL> select NAME,LOG_MODE,FLASHBACK_ON from v$database;

	NAME	   LOG_MODE				FLASHBACK_ON
	---------- ------------------------------------ -------------------------
	CDB	   ARCHIVELOG				NO

● FLASH BACK Database設定

FLASH BACK Database も MOUNTモードで設定する必要があるため MOUNTモード状態で設定します

1) FLASH BACK Database設定

SQL> ALTER DATABASE FLASHBACK ON;

	Database altered.

2) FLASH BACK Database設定確認

SQL> select NAME,LOG_MODE,FLASHBACK_ON from v$database;

	NAME	   LOG_MODE				FLASHBACK_ON
	---------- ------------------------------------ -------------------------
	CDB	   ARCHIVELOG				YES

● BLOCK CHANGE TRACKING設定

1) BLOCK CHANGE TRACKING設定

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

	Database altered.

2) BLOCK CHANGE TRACKING設定確認

SQL> col FILENAME format a70
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

	STATUS			       FILENAME
	------------------------------ ----------------------------------------------------------------------
	ENABLED 		       /u01/app/oracle/oradata/CDB/changetracking/o1_mf_jjr0notl_.chg

● Database OPEN

DatabaseをOPENして起動し設定を反映

SQL> alter database open;

	Database altered.

■ 設定確認

● キャラクタセット確認

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER like '%CHARACTERSET';
	PARAMETER		       VALUE
	------------------------------ ----------
	NLS_NCHAR_CHARACTERSET	       AL16UTF16
	NLS_CHARACTERSET	       AL32UTF8

● Local UNDO設定確認

SQL> col PROPERTY_NAME format a20
SQL> col PROPERTY_VALUE  format a10
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
	FROM   DATABASE_PROPERTIES
	WHERE  PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

	PROPERTY_NAME	     PROPERTY_V
	-------------------- ----------
	LOCAL_UNDO_ENABLED   TRUE

● 表領域確認

col FILE_NAME format a70
col TABLESPACE_NAME format a10
set lin 200 pages 1000
SELECT 
    a.TABLESPACE_NAME 
    ,a.BYTES/1024/1024 as "Size(MB)"
    ,a.FILE_NAME
    ,a.autoextensible as "Auto_Extent"
    ,a.increment_by * (select VALUE from v$parameter where NAME='db_block_size') /1024/1024 as "Auto_Extent_Size(MB)"
    ,TO_CHAR(a.MAXBYTES/1024/1024,'999,999,999') as "MAX_SIZE(MB)"
    ,b.BLOCK_SIZE
    ,b.BIGFILE
FROM DBA_DATA_FILES a
    , DBA_TABLESPACES b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME
union all
SELECT 
    x.TABLESPACE_NAME 
    ,x.BYTES/1024/1024 as "Size(MB)"
    ,x.FILE_NAME
    ,x.autoextensible as "Auto_Extent"
    ,x.increment_by * (select VALUE from v$parameter where NAME='db_block_size') /1024/1024 as "Auto_Extent_Size(MB)"
	,TO_CHAR(x.MAXBYTES/1024/1024,'999,999,999') as "MAX_SIZE(MB)"
    ,y.BLOCK_SIZE
    ,y.BIGFILE
FROM dba_temp_files x
    , DBA_TABLESPACES y
WHERE x.TABLESPACE_NAME=y.TABLESPACE_NAME  
ORDER By TABLESPACE_NAME
;

	TABLESPACE   Size(MB) FILE_NAME 							     Auto_Exte Auto_Extent_Size(MB) MAX_SIZE(MB) BLOCK_SIZE BIGFILE
	---------- ---------- ---------------------------------------------------------------------- --------- -------------------- ------------ ---------- ---------
	SYSAUX		  550 /u01/app/oracle/oradata/CDB/datafile/sysaux01.dbf 		     YES		      10	    32,768       8192 NO
	SYSTEM		  700 /u01/app/oracle/oradata/CDB/datafile/system01.dbf 		     YES		      10	    32,768       8192 NO
	TEMPTS1 	   30 /u01/app/oracle/oradata/CDB/datafile/temp01.dbf			     YES		      10	    32,768       8192 NO
	UNDOTBS1	  325 /u01/app/oracle/oradata/CDB/datafile/undotbs01.dbf		     YES		       2	    32,768       8192 NO
	USERS		  100 /u01/app/oracle/oradata/CDB/datafile/users01.dbf			     YES		      10	    32,768       8192 NO

● REDO確認

col MEMBER format a60
SELECT 
    b.THREAD#
    ,b.MEMBERS
    ,a.GROUP#
    ,b.SEQUENCE#
    ,a.MEMBER
    ,b.bytes/1024/1024 as "Bytes(MB)"
    ,a.TYPE
    ,b.STATUS
    ,b.ARCHIVED
FROM
	v$logfile a
	,v$log b
WHERE a.GROUP#=b.GROUP#
ORDER BY 1,2,3;

	THREAD#    MEMBERS	  GROUP#  SEQUENCE# MEMBER							  Bytes(MB) TYPE		  STATUS				   ARCHIVED
	---------- ---------- ---------- ---------- ------------------------------------------------------------ ---------- --------------------- ------------------------------------------------ ---------
		1	    2	       1	 28 /u01/app/oracle/oradata/CDB/onlinelog/redo01a.log			100 ONLINE		  INACTIVE				   YES
		1	    2	       1	 28 /u01/app/oracle/oradata/CDB/onlinelog/redo01b.log			100 ONLINE		  INACTIVE				   YES
		1	    2	       2	 29 /u01/app/oracle/oradata/CDB/onlinelog/redo02a.log			100 ONLINE		  INACTIVE				   YES
		1	    2	       2	 29 /u01/app/oracle/oradata/CDB/onlinelog/redo02b.log			100 ONLINE		  INACTIVE				   YES
		1	    2	       3	 30 /u01/app/oracle/oradata/CDB/onlinelog/redo03a.log			100 ONLINE		  CURRENT				   NO
		1	    2	       3	 30 /u01/app/oracle/oradata/CDB/onlinelog/redo03b.log			100 ONLINE		  CURRENT				   NO

	6 rows selected.

■ PDB作成

1) 作成PDBディレクトリ作成

SQL> host mkdir -p /u01/app/oracle/oradata/CDB/pdb1/

2) PDB 作成

SQL> CREATE PLUGGABLE DATABASE pdb1 ADMIN USER admin IDENTIFIED BY password ROLES=(DBA)
  DEFAULT TABLESPACE users 
    DATAFILE '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf' SIZE 10M 
    AUTOEXTEND ON
    PATH_PREFIX = '/u01/app/oracle/oradata/CDB/pdb1/'
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB/pdbseed/', 
                         '/u01/app/oracle/oradata/CDB/pdb1/');


	Pluggable database created.

※間違って作成したときは、次のように削除

SQL> DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILES;

3）PDB 作成確認

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  MOUNTED

4) PDB OPEN起動

SQL> alter pluggable database pdb1 open;

	Pluggable database altered.

5) PDB 起動確認

SQL> show pdbs

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

6) CDB から PDB接続

SQL> alter session set container=PDB1;

	Session altered.

SQL> show con_name

    CON_NAME
    ------------------------------
    PDB1

■ ORACLE*NET設定と接続確認

● listener.ora設定

1) listener.ora設定

[oracle@db19c admin]$ cat listener.ora
	# listener.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
	# Generated by Oracle configuration tools.

	LISTENER =
	(DESCRIPTION_LIST =
		(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = db19c.publicsunbnet01.vcn102000.oraclevcn.com)(PORT = 1521))
		(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
		)
	)

2) LISTENER 起動

[oracle@db19c ~]$ lsnrctl start

	LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-AUG-2021 13:54:47

	Copyright (c) 1991, 2021, Oracle.  All rights reserved.

	Starting /u01/app/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

	TNSLSNR for Linux: Version 19.0.0.0.0 - Production
	System parameter file is /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
	Log messages written to /u01/app/oracle/diag/tnslsnr/db19c/listener/alert/log.xml
	Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db19c)(PORT=1521)))
	Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT=1521)))
	STATUS of the LISTENER
	------------------------
	Alias                     LISTENER
	Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
	Start Date                06-AUG-2021 13:54:48
	Uptime                    0 days 0 hr. 0 min. 0 sec
	Trace Level               off
	Security                  ON: Local OS Authentication
	SNMP                      OFF
	Listener Parameter File   /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
	Listener Log File         /u01/app/oracle/diag/tnslsnr/db19c/listener/alert/log.xml
	Listening Endpoints Summary...
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db19c)(PORT=1521)))
	(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
	The listener supports no services
	The command completed successfully

2) LISTENER　ステータス確認
作成した CDBインスタンス と CDBサービス, PDB1 サービスがリスニングされていることを確認

[oracle@db19c ~]$ lsnrctl stat

	LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-AUG-2021 13:56:11

	Copyright (c) 1991, 2021, Oracle.  All rights reserved.

	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT=1521)))
	STATUS of the LISTENER
	------------------------
	Alias                     LISTENER
	Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
	Start Date                06-AUG-2021 13:54:48
	Uptime                    0 days 0 hr. 1 min. 22 sec
	Trace Level               off
	Security                  ON: Local OS Authentication
	SNMP                      OFF
	Listener Parameter File   /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora
	Listener Log File         /u01/app/oracle/diag/tnslsnr/db19c/listener/alert/log.xml
	Listening Endpoints Summary...
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db19c)(PORT=1521)))
	(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
	Services Summary...
	Service "CDB" has 1 instance(s).
	Instance "CDB", status READY, has 1 handler(s) for this service...
	Service "CDBXDB" has 1 instance(s).
	Instance "CDB", status READY, has 1 handler(s) for this service...
	Service "c8e5257bb2c56f86e055000017026583" has 1 instance(s).
	Instance "CDB", status READY, has 1 handler(s) for this service...
	Service "pdb1" has 1 instance(s).
	Instance "CDB", status READY, has 1 handler(s) for this service...
	The command completed successfully

● tnsnames.ora設定

1) tnsnames.ora登録
作成した CDB と PDB1 の接続情報を追加

[oracle@db19c admin]$ cat $ORACLE_HOME/netwok/admin/tnsnames.ora
	# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
	# Generated by Oracle configuration tools.

	LISTENER_CDB =
	(ADDRESS = (PROTOCOL = TCP)(HOST = db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT = 1521))

	CDB =
	(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT = 1521))
		(CONNECT_DATA =
		(SERVER = DEDICATED)
		(SERVICE_NAME = CDB)
		)
	)

	PDB1 =
	(DESCRIPTION =
		(ADDRESS = (PROTOCOL = TCP)(HOST = db19c.publicsubnet01.vcntokyo.oraclevcn.com)(PORT = 1521))
		(CONNECT_DATA =
		(SERVER = DEDICATED)
		(SERVICE_NAME = PDB1)
		)
	)

● CDB 接続

1) CDB 接続

[oracle@db19c admin]$ sqlplus system/system_password@CDB

	SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 6 14:03:00 2021
	Version 19.11.0.0.0

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

	Last Successful login time: Fri Aug 06 2021 13:31:10 +00:00

	Connected to:
	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
	Version 19.11.0.0.0

2) CDB 接続確認

SQL> select NAME FROM V$DATABASE;

	NAME
	---------------------------
	CDB

● PDB接続

1) PDB1 接続

[oracle@db19c admin]$ sqlplus admin/password@PDB1

	SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 6 14:05:45 2021
	Version 19.11.0.0.0

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

	Last Successful login time: Fri Aug 06 2021 13:39:44 +00:00

	Connected to:
	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
	Version 19.11.0.0.0

2) PDB1 接続確認

SQL> show con_name

	CON_NAME
	------------------------------
	PDB1

3) PDB1 Data File確認

SQL> col FILE_NAME format a70
SQL> col TABLESPACE_NAME format a10
SQL> set lin 200 pages 1000
SQL> SELECT
		a.TABLESPACE_NAME
		,a.BYTES/1024/1024 as "Size(MB)"
		,a.FILE_NAME
		,a.autoextensible as "Auto_Extent"
		,a.increment_by * (select VALUE from v$parameter where NAME='db_block_size') /1024 as "Auto_Extent_Size(MB)"
		,b.MAX_SIZE/1024/1024 as "MAX_SIZE(MB)"
		,b.BLOCK_SIZE
		,b.BIGFILE
	FROM DBA_DATA_FILES a
		, DBA_TABLESPACES b
	WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME
	union all
	SELECT
		x.TABLESPACE_NAME
		,x.BYTES/1024/1024 as "Size(MB)"
		,x.FILE_NAME
		,x.autoextensible as "Auto_Extent"
		,x.increment_by * (select VALUE from v$parameter where NAME='db_block_size') /1024 as "Auto_Extent_Size(MB)"
		,y.MAX_SIZE/1024/1024 as "MAX_SIZE(MB)"
		,y.BLOCK_SIZE
		,y.BIGFILE
	FROM dba_temp_files x
		, DBA_TABLESPACES y
	WHERE x.TABLESPACE_NAME=y.TABLESPACE_NAME
	ORDER By TABLESPACE_NAME
	; 

	TABLESPACE   Size(MB) FILE_NAME 							     Auto_Exte Auto_Extent_Size(MB) MAX_SIZE(MB) BLOCK_SIZE BIGFILE
	---------- ---------- ---------------------------------------------------------------------- --------- -------------------- ------------ ---------- ---------
	SYSAUX		  120 /u01/app/oracle/oradata/CDB/pdb1/datafile/sysaux01.dbf		     YES		      10240	    2048       8192 NO
	SYSTEM		  180 /u01/app/oracle/oradata/CDB/pdb1/datafile/system01.dbf		     YES		      10240	    2048       8192 NO
	TEMPTS1 	   20 /u01/app/oracle/oradata/CDB/pdb1/datafile/temp01.dbf		     YES		      10240	    2048       8192 NO
	UNDOTBS1	  235 /u01/app/oracle/oradata/CDB/pdb1/datafile/undotbs01.dbf		     YES		       5120	    2048       8192 NO
	USERS		  100 /u01/app/oracle/oradata/CDB/pdb1/datafile/users01.dbf		     YES		      10240	    2048       8192 NO

	5 rows selected.

■ RMAN バックアップ

メディア障害が発生した場合にリカバリするための完全なファイル・セットが確実に存在するように、データベースの全体バックアップを作成

1) RMAN接続

[oracle@db19c admin]$ rman target /

	Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 6 15:18:31 2021
	Version 19.11.0.0.0

	Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

	connected to target database: CDB (DBID=2196473942)

2) Backup実行

RMAN> BACKUP INCREMENTAL LEVEL 0  DATABASE　DATABASE PLUS ARCHIVELOG;


	Starting backup at 06-AUG-21
	current log archived
	using target database control file instead of recovery catalog
	allocated channel: ORA_DISK_1
	channel ORA_DISK_1: SID=26 device type=DISK
	channel ORA_DISK_1: starting archived log backup set
	channel ORA_DISK_1: specifying archived log(s) in backup set
	input archived log thread=1 sequence=29 RECID=1 STAMP=1079877165
	input archived log thread=1 sequence=30 RECID=2 STAMP=1079882340
	channel ORA_DISK_1: starting piece 1 at 06-AUG-21
	channel ORA_DISK_1: finished piece 1 at 06-AUG-21
	piece handle=/u01/app/oracle/oradata/CDB/flash_recovery_area/CDB/backupset/2021_08_06/o1_mf_annnn_TAG20210806T151901_jjtnq5pc_.bkp tag=TAG20210806T151901 comment=NONE
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
	Finished backup at 06-AUG-21

	Starting backup at 06-AUG-21
	using channel ORA_DISK_1
	channel ORA_DISK_1: starting incremental level 0 datafile backup set
	channel ORA_DISK_1: specifying datafile(s) in backup set
	input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/datafile/system01.dbf
	input datafile file number=00003 name=/u01/app/oracle/oradata/CDB/datafile/sysaux01.dbf
	input datafile file number=00005 name=/u01/app/oracle/oradata/CDB/datafile/undotbs01.dbf
	input datafile file number=00007 name=/u01/app/oracle/oradata/CDB/datafile/users01.dbf
	
	・・・

	piece handle=/u01/app/oracle/oradata/CDB/flash_recovery_area/CDB/C8E3C7F88EF34D10E055000017026583/backupset/2021_08_06/o1_mf_nnnd0_TAG20210806T151903_jjtnrnob_.bkp tag=TAG20210806T151903 comment=NONE
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
	channel ORA_DISK_1: starting incremental level 0 datafile backup set
	channel ORA_DISK_1: specifying datafile(s) in backup set
	input datafile file number=00006 name=/u01/app/oracle/oradata/CDB/pdbseed/datafile/undotbs01.dbf
	input datafile file number=00002 name=/u01/app/oracle/oradata/CDB/pdbseed/datafile/system01.dbf
	input datafile file number=00004 name=/u01/app/oracle/oradata/CDB/pdbseed/datafile/sysaux01.dbf
	input datafile file number=00008 name=/u01/app/oracle/oradata/CDB/pdbseed/datafile/users01.dbf
	channel ORA_DISK_1: starting piece 1 at 06-AUG-21
	channel ORA_DISK_1: finished piece 1 at 06-AUG-21
	piece handle=/u01/app/oracle/oradata/CDB/flash_recovery_area/CDB/C8E3C7F88EF34D10E055000017026583/backupset/2021_08_06/o1_mf_nnnd0_TAG20210806T151903_jjtns3v8_.bkp tag=TAG20210806T151903 comment=NONE
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
	Finished backup at 06-AUG-21

	Starting backup at 06-AUG-21
	current log archived
	using channel ORA_DISK_1
	channel ORA_DISK_1: starting archived log backup set
	channel ORA_DISK_1: specifying archived log(s) in backup set
	input archived log thread=1 sequence=31 RECID=3 STAMP=1079882418
	channel ORA_DISK_1: starting piece 1 at 06-AUG-21
	channel ORA_DISK_1: finished piece 1 at 06-AUG-21
	piece handle=/u01/app/oracle/oradata/CDB/flash_recovery_area/CDB/backupset/2021_08_06/o1_mf_annnn_TAG20210806T152018_jjtnslh3_.bkp tag=TAG20210806T152018 comment=NONE
	channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
	Finished backup at 06-AUG-21

	Starting Control File and SPFILE Autobackup at 06-AUG-21
	piece handle=/u01/app/oracle/oradata/CDB/flash_recovery_area/CDB/autobackup/2021_08_06/o1_mf_s_1079882419_jjtnsmvn_.bkp comment=NONE
	Finished Control File and SPFILE Autobackup at 06-AUG-21

■ 参考

・データベース管理者ガイド:Oracle Databaseの作成および構成
・CDBの作成および構成