[오라클] 사일런트 모드로 템플릿 생성

실습 환경: 오라클 19c

createTemplateFromDB 명령으로 기존 DB에서 템플릿을 가져와 (생성) 하는 명령은 다음과 같다. 

[oracle@enp0s3 dbca]$ dbca -silent -createTemplateFromDB -templateName My_New_Template -sourceDB orcl
DB 작업 준비
33% 완료
데이터베이스에서 템플리트 생성 중
37% 완료
40% 완료
43% 완료
47% 완료
50% 완료
53% 완료
57% 완료
60% 완료
63% 완료
67% 완료
템플리트 생성 중
100% 완료
템플리트 "/u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/templates/My_New_Template.dbt" 생성이 완료되었습니다.
자세한 내용은 로그 파일 "/u01/app/oracle/cfgtoollogs/dbca/silent.log_2021-11-18_10-28-47AM"을(를) 참조하십시오.

이때 템플릿 이름은 마음대로 정하면 되고 소스 DB는 현재 생성된 것에서 가져올 수 있다. 파라미터가 충분하지 않을 경우 다음과 같은 프롬프트가 출력된다.

[INS-04008] 명령행에서 부적합한 조합의 인수가 전달되었습니다. -createTemplateFromDB 인수에 대해 하나 이상의 필수 종속 인수가 전달되지 않았습니다.
        -createTemplateFromDB - 기존 데이터베이스에서 템플리트를 생성하는 명령입니다.
                -templateName <DB 생성을 위해 기본 위치 또는 전체 템플리트 경로의 기존 템플리트를 지정하거나 템플리트 생성을 위해 새 템플리트 이름을 입력하십시오.>
                -sourceDB <RAC 데이터베이스의 경우 데이터베이스 고유 이름, 단일 인스턴스 데이터베이스의 경우 SID>
                [-connectionString <원격 데이터베이스에 접속하기 위한 EZCONNECT 문자열입니다(예: "host:port/servicename").>]
                [-useWalletForDBCredentials <true | false> 전자 지갑에서 데이터베이스 인증서를 로드하려면 true를 지정하십시오.]
                        -dbCredentialsWalletLocation <전자 지갑 파일이 포함된 디렉토리의 경로>
                        [-dbCredentialsWalletPassword <자동 로그인이 사용 안함으로 설정된 전자 지갑을 열기 위한 비밀번호>]
                [-sysDBAUserName <SYSDBA 권한이 있는 사용자 이름>]
                [-maintainFileLocations <true | false>]
                [-sysDBAPassword <sysDBAUserName 사용자 이름에 대한 비밀번호>]

가장 위에서 생성했던 데이터베이스는 오라클에서 제공하는 General_Purpose.dbc라는 템플릿을 사용했고 생성이 완료된 템플릿 또한 동일한 디렉토리에 지정한 이름인 My_New_Template.dbt와 같은 이름으로 생성된 것을 확인할 수 있다. 

---

오라클 템플릿은 xml 형식으로 샘플 스키마 지정같은 일반 속성, db_name, dispatchers 같은 InitParamAttributes 등 여러 카테고리로 구성되어 있다. 다음은 General_Purpose.dbc 파일의 내용이다. 

<?xml version = '1.0'?>
<DatabaseTemplate name="General Purpose" description=" " version="19.0.0.0.0">
   <CommonAttributes>
      <option name="OMS" value="true" includeInPDBs="true"/>
      <option name="JSERVER" value="true" includeInPDBs="true"/>
      <option name="SPATIAL" value="true" includeInPDBs="true"/>
      <option name="IMEDIA" value="true" includeInPDBs="true"/>
      <option name="ORACLE_TEXT" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="SAMPLE_SCHEMA" value="false" includeInPDBs="false"/>
      <option name="CWMLITE" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="APEX" value="false" includeInPDBs="false"/>
      <option name="DV" value="true" includeInPDBs="true"/>
   </CommonAttributes>
   <Variables/>
   <CustomScripts Execute="false"/>
   <InitParamAttributes>
      <InitParams>
         <initParam name="db_name" value=""/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
         <initParam name="compatible" value="19.0.0"/>
         <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
         <initParam name="undo_tablespace" value="UNDOTBS1"/>
         <initParam name="control_files" value="(&quot;{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl&quot;, &quot;{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl&quot;)"/>
         <initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>
         <initParam name="audit_trail" value="db"/>
         <initParam name="db_block_size" value="8" unit="KB"/>
         <initParam name="open_cursors" value="300"/>
      </InitParams>
      <MiscParams>
         <databaseType>MULTIPURPOSE</databaseType>
         <maxUserConn>20</maxUserConn>
         <percentageMemTOSGA>40</percentageMemTOSGA>
         <customSGA>false</customSGA>
         <dataVaultEnabled>false</dataVaultEnabled>
         <archiveLogMode>false</archiveLogMode>
         <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
      </MiscParams>
      <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
   </InitParamAttributes>
   <StorageAttributes>
      <DataFiles>
         <Location>{ORACLE_HOME}/assistants/dbca/templates/Seed_Database.dfb</Location>
         <SourceDBName cdb="true">seeddata</SourceDBName>
         <Name id="3" Tablespace="SYSAUX" Contents="PERMANENT" Size="400" autoextend="true" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf</Name>
         <Name id="1" Tablespace="SYSTEM" Contents="PERMANENT" Size="880" autoextend="true" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf</Name>
         <Name id="4" Tablespace="UNDOTBS1" Contents="UNDO" Size="25" autoextend="true" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf</Name>
         <Name id="7" Tablespace="USERS" Contents="PERMANENT" Size="5" autoextend="true" blocksize="8192" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf</Name>
      </DataFiles>
      <TempFiles>
         <Name id="1" Tablespace="TEMP" Contents="TEMPORARY" Size="20" con_id="1">{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf</Name>
      </TempFiles>
      <ControlfileAttributes id="Controlfile">
         <maxDatafiles>100</maxDatafiles>
         <maxLogfiles>16</maxLogfiles>
         <maxLogMembers>3</maxLogMembers>
         <maxLogHistory>1</maxLogHistory>
         <maxInstances>8</maxInstances>
         <image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
         <image name="control02.ctl" filepath="{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/"/>
      </ControlfileAttributes>
      <RedoLogGroupAttributes id="1">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="2">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="3">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
   </StorageAttributes>
</DatabaseTemplate>

 

다음은 createTemplateFromDB 명령으로 생성한 My_New_Template.dbt 파일의 내용이다. 

<DatabaseTemplate name="My New Template" description=" " version="19.0.0.0.0">
   <CommonAttributes>
      <option name="OMS" value="true" includeInPDBs="true"/>
      <option name="JSERVER" value="true" includeInPDBs="true"/>
      <option name="SPATIAL" value="true" includeInPDBs="true"/>
      <option name="IMEDIA" value="true" includeInPDBs="true"/>
      <option name="ORACLE_TEXT" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="SAMPLE_SCHEMA" value="false" includeInPDBs="false"/>
      <option name="CWMLITE" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="APEX" value="false" includeInPDBs="false"/>
      <option name="DV" value="true" includeInPDBs="true">
         <tablespace id="SYSAUX"/>
      </option>
   </CommonAttributes>
   <Variables/>
   <CustomScripts Execute="false"/>
   <InitParamAttributes>
      <InitParams>
         <initParam name="db_name" value=""/>
         <initParam name="db_domain" value=""/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
         <initParam name="compatible" value="19.0.0"/>
         <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
         <initParam name="processes" value="300"/>
         <initParam name="undo_tablespace" value="UNDOTBS1"/>
         <initParam name="control_files" value="(&quot;{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl&quot;, &quot;{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl&quot;)"/>
         <initParam name="diagnostic_dest" value="/u01/app/oracle"/>
         <initParam name="audit_trail" value="DB"/>
         <initParam name="memory_target" value="1207959552"/>
         <initParam name="nls_territory" value="KOREA"/>
         <initParam name="db_block_size" value="8192"/>
         <initParam name="open_cursors" value="300"/>
         <initParam name="nls_language" value="AMERICAN"/>
      </InitParams>
      <MiscParams>
         <customSGA>false</customSGA>
         <characterSet>AL32UTF8</characterSet>
         <nationalCharacterSet>AL16UTF16</nationalCharacterSet>
         <archiveLogMode>false</archiveLogMode>
         <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
      </MiscParams>
      <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
   </InitParamAttributes>
   <StorageAttributes>
      <ControlfileAttributes id="Controlfile">
         <maxDatafiles>100</maxDatafiles>
         <maxLogfiles>16</maxLogfiles>
         <maxLogMembers>3</maxLogMembers>
         <maxLogHistory>292</maxLogHistory>
         <maxInstances>8</maxInstances>
         <image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
         <image name="control02.ctl" filepath="{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/"/>
      </ControlfileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf" con_id="0">
         <tablespace>SYSAUX</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">520</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">10240</increment>
         <maxSize unit="MB">32767</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf" con_id="0">
         <tablespace>SYSTEM</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">900</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">10240</increment>
         <maxSize unit="MB">32767</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf" con_id="0">
         <tablespace>TEMP</tablespace>
         <temporary>true</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">32</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">640</increment>
         <maxSize unit="MB">32767</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf" con_id="0">
         <tablespace>UNDOTBS1</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">335</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">5120</increment>
         <maxSize unit="MB">32767</maxSize>
      </DatafileAttributes>
      <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf" con_id="0">
         <tablespace>USERS</tablespace>
         <temporary>false</temporary>
         <online>true</online>
         <status>0</status>
         <size unit="MB">5</size>
         <reuse>true</reuse>
         <autoExtend>true</autoExtend>
         <increment unit="KB">1280</increment>
         <maxSize unit="MB">32767</maxSize>
      </DatafileAttributes>
      <TablespaceAttributes id="SYSAUX" con_id="0">
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>8192</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">0</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>2147483645</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <autoSegmentMgmt>true</autoSegmentMgmt>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="SYSTEM" con_id="0">
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>8192</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">0</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>2147483645</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <autoSegmentMgmt>false</autoSegmentMgmt>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="TEMP" con_id="0">
         <temporary>true</temporary>
         <defaultTemp>true</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>8192</blockSize>
         <allocation>2</allocation>
         <uniAllocSize unit="KB">1024</uniAllocSize>
         <initSize unit="KB">1024</initSize>
         <increment unit="KB">1024</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>-1</maxExtends>
         <minExtendsSize unit="KB">1024</minExtendsSize>
         <logging>false</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <autoSegmentMgmt>false</autoSegmentMgmt>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="UNDOTBS1" con_id="0">
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>true</undo>
         <local>true</local>
         <blockSize>8192</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">0</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>2147483645</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <autoSegmentMgmt>false</autoSegmentMgmt>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <TablespaceAttributes id="USERS" con_id="0">
         <temporary>false</temporary>
         <defaultTemp>false</defaultTemp>
         <undo>false</undo>
         <local>true</local>
         <blockSize>8192</blockSize>
         <allocation>1</allocation>
         <uniAllocSize unit="KB">-1</uniAllocSize>
         <initSize unit="KB">64</initSize>
         <increment unit="KB">0</increment>
         <incrementPercent>0</incrementPercent>
         <minExtends>1</minExtends>
         <maxExtends>2147483645</maxExtends>
         <minExtendsSize unit="KB">64</minExtendsSize>
         <logging>true</logging>
         <recoverable>false</recoverable>
         <maxFreeSpace>0</maxFreeSpace>
         <autoSegmentMgmt>true</autoSegmentMgmt>
         <bigfile>false</bigfile>
         <datafilesList>
            <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf"/>
         </datafilesList>
      </TablespaceAttributes>
      <RedoLogGroupAttributes id="1">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="2">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
      <RedoLogGroupAttributes id="3">
         <reuse>false</reuse>
         <fileSize unit="KB">204800</fileSize>
         <Thread>1</Thread>
         <member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
      </RedoLogGroupAttributes>
   </StorageAttributes>
</DatabaseTemplate>

보다시피 createTemplateFromDB 명령으로 생성한 템플릿 파일 내용이 오라클에서 제공하는 범용 템플릿 샘플 파일보다 많은 것을 확인할 수 있다. 그중에는

<initParam name="db_domain" value=""/>
<initParam name="processes" value="300"/>   
<initParam name="memory_target" value="1207959552"/>
<initParam name="nls_territory" value="KOREA"/>
<initParam name="db_block_size" value="8192"/>
<initParam name="nls_language" value="AMERICAN"/>
<characterSet>AL32UTF8</characterSet>

위와 같은 파라미터가 새로 쓰이거나 수정되어있다. 범용 템플릿 파일에 <initParam name="db_block_size" value="8" unit="KB"/> 이런 식으로 값, 유닛 사이즈로 설정됐던 파라미터는 (소스 DB에서 복사하여) 생성한 템플릿 파일에서, <initParam name="db_block_size" value="8192"/> 이렇게 유닛 사이즈와 값을 해당 단위로 적용해서 값 하나로만 표시한다. 이 외에도 범용 템플릿에 지정하지 않은 수많은 파라미터를 명시적으로 포함한다.