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 があるのでこれを使用します。
![Market.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/109087/883d5d0e-c328-de95-fb8e-246fb1a07c60.png)
・参考: [Oracle Cloud: 東京-大阪リージョン間で手動Data Guardしてみてみた](https://qiita.com/shirok/items/356da23cd304ae9ab6b4)

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

```shell
[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）を指定し、必要な環境変数が設定されていることを確認

**・ 環境変数設定**

```shell
[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
```


**・ 環境変数確認**

```shell
[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ファイル作成
[パスワード・ファイル認証](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/getting-started-with-database-administration.html#GUID-202A0C6B-2861-489A-8E22-6359FD1E112C)を使用した接続パスワード・ファイル認証を使用すると、管理ユーザーは、SQL * PlusCONNECTコマンドを使用して、ローカルまたはリモートのデータベースに接続および認証できます。デフォルトでは、パスワードでは大文字と小文字が区別されます。

```shell
[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の作成後は変更できません。
その他のパラメーターは最低限以下のようなものを設定しておきます。
パラメーターの内容は [データベース・リファレンス](https://docs.oracle.com/cd/F19136_01/refrn/index.html)を参照

```shell
[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 の情報を必要に応じて設定

```shell
[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 ディレクトリ作成

```shell
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
```

# ■ インスタンス接続

```shell
[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作成**

```shell
SQL> create spfile from pfile;

	File created.
```

**2) spfile作成確認**

```shell
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接続**


```shell
[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起動**


```shell
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ログ・ブロック・サイズ

```shell
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を入力
その他管理者パスワードや一時表領域名の入力を求めるプロンプトが表示されたら必要な他の情報を入力

```shell
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ディレクトリ作成**

```shell
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 パラメータ設定**

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

	System altered.
```

**3) log_archive_dest_1 パラメータ設定**

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

	System altered.
```

**4) log_archive_formatパラメータ設定**

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

        System altered.
```


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

```shell
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モード設定**

```shell
SQL> ALTER DATABASE ARCHIVELOG;

	Database altered.
```


**7) ARCHIVELOGモード設定確認**

```shell
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設定**

```shell
SQL> ALTER DATABASE FLASHBACK ON;

	Database altered.
```

**2) FLASH BACK Database設定確認**

```shell
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設定**

```shell
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

	Database altered.
```


**2) BLOCK CHANGE TRACKING設定確認**

```shell
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して起動し設定を反映

```shell
SQL> alter database open;

	Database altered.
```


# ■ 設定確認
## ● キャラクタセット確認

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


## ● Local UNDO設定確認

```shell
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
```


## ● 表領域確認

```shell
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確認

```shell
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ディレクトリ作成**

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


**2) PDB 作成**

```shell
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.
```

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

```shell
SQL> DROP PLUGGABLE DATABASE PDB1 INCLUDING DATAFILES;
```


**3）PDB 作成確認**

```shell
SQL> show pdbs

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

**4) PDB OPEN起動**

```shell
SQL> alter pluggable database pdb1 open;

	Pluggable database altered.
```

**5) PDB 起動確認**

```shell
SQL> show pdbs

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

**6) CDB から PDB接続**

```shell
SQL> alter session set container=PDB1;

	Session altered.

SQL> show con_name

    CON_NAME
    ------------------------------
    PDB1
```

# ■ ORACLE*NET設定と接続確認

## ● listener.ora設定
**1) listener.ora設定**

```shell
[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 起動**

```shell
[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 サービスがリスニングされていることを確認


```shell
[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 の接続情報を追加

```shell
[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 接続**

```shell
[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 接続確認**

```shell
SQL> select NAME FROM V$DATABASE;

	NAME
	---------------------------
	CDB
```

## ● PDB接続

**1) PDB1 接続**

```shell
[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 接続確認**

```shell
SQL> show con_name

	CON_NAME
	------------------------------
	PDB1
```

**3) PDB1 Data File確認**

```shell
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接続**

```shell
[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実行**

```shell
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の作成および構成](https://docs.oracle.com/cd/F19136_01/admin/creating-and-configuring-an-oracle-database.html#GUID-807DE711-C82C-4BB2-8C31-5EE89CA71349)
・[CDBの作成および構成](https://docs.oracle.com/cd/F19136_01/multi/creating-and-configuring-a-cdb.html#GUID-54C5126C-427A-4BFE-81BF-950FB1A062D5)