暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 【Oracle学习03】DBCA创建及配置数据库

【Oracle学习03】DBCA创建及配置数据库

陶老师运维笔记 2019-11-28
3631

【Oracle学习03】DBCA创建及配置数据库

1. DBCA 介绍

DBCA (Database Configuration Assistant)，数据库配置助手，可以方便地创建数据库，生成创建脚本,管理数据库模板及配置数据库选项等。

2. DBCA 图形创建数据库

用dbca创建数据库。

  1. $cd database
  2. $dbca

选择Advanced configuration。

密码太简单会提示，可选yes略过。

Finish。

点击close后完成。恭喜你数据库安装成功！！！

说明：也可以用OMF“Oracle managed file”创建数据库。

3. DBCA 生成数据库创建脚本

假设数据库名字为gpdb。

  1. $export DISPLAY=:0.0
  2. $env |grep ora -i
  3. $dbca

最终生成文件如下：

  1. #gpdb为database名字。
  2. $cd /u01/app/oracle/admin/gpdb/
  3. $tree -L 2
  4. scripts/
  5. scripts/tempControl.ctl
  6. scripts/postDBCreation.sql
  7. scripts/initgpdbTempOMF.ora
  8. scripts/gpdb.sh
  9. scripts/init.ora
  10. scripts/CloneRmanRestore.sql
  11. scripts/lockAccount.sql
  12. scripts/rmanRestoreDatafiles.sql
  13. scripts/initgpdbTemp.ora
  14. scripts/cloneDBCreation.sql
  15. scripts/gpdb.sql
  16. scripts/postScripts.sql

  18. #nc -l 1234 | tar xzvf - > scripts
  19. #tar czvf - scripts | nc 192.168.56.1 1234

gpdb.sh

  1. #!/bin/sh

  3. OLD_UMASK=`umask`
  4. umask 0027
  5. mkdir -p /u01/app/oracle
  6. mkdir -p /u01/app/oracle/admin/gpdb/adump
  7. mkdir -p /u01/app/oracle/admin/gpdb/dpdump
  8. mkdir -p /u01/app/oracle/admin/gpdb/pfile
  9. mkdir -p /u01/app/oracle/audit
  10. mkdir -p /u01/app/oracle/cfgtoollogs/dbca/gpdb
  11. mkdir -p /u01/app/oracle/oradata/gpdb
  12. mkdir -p /u01/app/oracle/product/12.2.0/db_1/dbs
  13. umask ${OLD_UMASK}
  14. PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
  15. ORACLE_SID=gpdb; export ORACLE_SID
  16. PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
  17. echo You should Add this entry in the /etc/oratab: gpdb:/u01/app/oracle/product/12.2.0/db_1:Y
  18. /u01/app/oracle/product/12.2.0/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/gpdb/scripts/gpdb.sql

init.ora

  1. cat init.ora  |grep -v '#'|grep -v "^ $" 
  2. db_block_size=8192
  3. open_cursors=300
  4. db_name="gpdb"
  5. control_files=("/u01/app/oracle/oradata/gpdb/control01.ctl", "/u01/app/oracle/oradata/gpdb/control02.ctl")
  6. compatible=12.2.0
  7. diagnostic_dest=/u01/app/oracle
  8. nls_language="AMERICAN"
  9. nls_territory="AMERICA"
  10. processes=300
  11. sga_target=1148m
  12. audit_file_dest="/u01/app/oracle/admin/gpdb/adump"
  13. audit_trail=db
  14. remote_login_passwordfile=EXCLUSIVE
  15. dispatchers="(PROTOCOL=TCP) (SERVICE=gpdbXDB)"
  16. pga_aggregate_target=383m
  17. undo_tablespace=UNDOTBS1

cloneDBCreation.sql

  1. SET VERIFY OFF
  2. connect "SYS"/"&&sysPassword" as SYSDBA
  3. set echo on
  4. spool /u01/app/oracle/admin/gpdb/scripts/cloneDBCreation.log append
  5. shutdown abort;
  6. startup nomount pfile="/u01/app/oracle/admin/gpdb/scripts/init.ora";
  7. Create controlfile reuse set database "gpdb"
  8. MAXINSTANCES 8
  9. MAXLOGHISTORY 1
  10. MAXLOGFILES 16
  11. MAXLOGMEMBERS 3
  12. MAXDATAFILES 100
  13. Datafile 
  14. '&&file0',
  15. '&&file1',
  16. '&&file2',
  17. '&&file3'
  18. LOGFILE GROUP 1 ('/u01/app/oracle/oradata/gpdb/redo01.log') SIZE 200M,
  19. GROUP 2 ('/u01/app/oracle/oradata/gpdb/redo02.log') SIZE 200M,
  20. GROUP 3 ('/u01/app/oracle/oradata/gpdb/redo03.log') SIZE 200M RESETLOGS;
  21. exec dbms_backup_restore.zerodbid(0);
  22. shutdown immediate;
  23. startup nomount pfile="/u01/app/oracle/admin/gpdb/scripts/initgpdbTemp.ora";
  24. Create controlfile reuse set database "gpdb"
  25. MAXINSTANCES 8
  26. MAXLOGHISTORY 1
  27. MAXLOGFILES 16
  28. MAXLOGMEMBERS 3
  29. MAXDATAFILES 100
  30. Datafile 
  31. '&&file0',
  32. '&&file1',
  33. '&&file2',
  34. '&&file3'
  35. LOGFILE GROUP 1 ('/u01/app/oracle/oradata/gpdb/redo01.log') SIZE 200M,
  36. GROUP 2 ('/u01/app/oracle/oradata/gpdb/redo02.log') SIZE 200M,
  37. GROUP 3 ('/u01/app/oracle/oradata/gpdb/redo03.log') SIZE 200M RESETLOGS;
  38. alter system enable restricted session;
  39. alter database "gpdb" open resetlogs;
  40. DECLARE
  41. cursor cur_services is 
  42. select name from dba_services where name like 'seeddata%'; 
  43. BEGIN 
  44.  for i in cur_services loop
  45.  dbms_service.delete_service(i.name); 
  46.  end loop; 
  47. END; 
  48. /
  49. alter database rename global_name to "gpdb";
  50. ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/gpdb/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
  51. select tablespace_name from dba_tablespaces where tablespace_name='USERS';
  52. ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION null;
  53. alter user sys account unlock identified by "&&sysPassword";
  54. connect "SYS"/"&&sysPassword" as SYSDBA
  55. alter user system account unlock identified by "&&systemPassword";
  56. select sid, program, serial#, username from v$session;
  57. alter database character set INTERNAL_CONVERT AL32UTF8;
  58. alter database national character set INTERNAL_CONVERT AL16UTF16;
  59. alter system disable restricted session;

lockAccount.sql

  1. SET VERIFY OFF
  2. connect "SYS"/"&&sysPassword" as SYSDBA
  3. set echo on
  4. spool /u01/app/oracle/admin/gpdb/scripts/lockAccount.log append
  5. BEGIN 
  6.  FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN ( 
  7. 'SYS','SYSTEM') ) 
  8.  LOOP
  9.   dbms_output.put_line('Locking and Expiring: ' || item.USERNAME); 
  10.   execute immediate 'alter user ' ||
  11.          sys.dbms_assert.enquote_name(
  12.          sys.dbms_assert.schema_name(
  13.          item.USERNAME),false) || ' password expire account lock' ;
  14.  END LOOP;
  15. END;
  16. /
  17. spool off

4. DBCA 管理数据库设计模板

  1. $echo $ORACLE_HOME
  2. /u01/app/oracle/product/12.2.0/db_1
  3. $env |grep ora -i
  4. #管理模板
  5. $dbca
  6. #
  7. $cat $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

DBCA管理模板:

生成的模板文件：

  1. $cd u01/app/oracle/product/12.2.0/db_1/assistants/dbca/templates
  2. <DatabaseTemplate name="dbca template dev" description="" version="12.2.0.1.0">
  3.    <CommonAttributes>
  4.       <option name="OMS" value="true"/>
  5.       <option name="JSERVER" value="true"/>
  6.       <option name="SPATIAL" value="true"/>
  7.       <option name="IMEDIA" value="true"/>
  8.       <option name="ORACLE_TEXT" value="true">
  9.          <tablespace id="SYSAUX"/>
  10.       </option>
  11.       <option name="SAMPLE_SCHEMA" value="true">
  12.          <tablespace id="USERS"/>
  13.       </option>
  14.       <option name="CWMLITE" value="true">
  15.          <tablespace id="SYSAUX"/>
  16.       </option>
  17.       <option name="APEX" value="false"/>
  18.       <option name="DV" value="true">
  19.          <tablespace id="SYSAUX"/>
  20.       </option>
  21.    </CommonAttributes>
  22.    <Variables/>
  23.    <CustomScripts Execute="false"/>
  24.    <InitParamAttributes>
  25.       <InitParams>
  26.          <initParam name="db_name" value=""/>
  27.          <initParam name="db_domain" value=""/>
  28.          <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
  29.          <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
  30.          <initParam name="compatible" value="12.2.0"/>
  31.          <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
  32.          <initParam name="processes" value="300"/>
  33.          <initParam name="undo_tablespace" value="UNDOTBS1"/>
  34.          <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;)"/>
  35.          <initParam name="diagnostic_dest" value="/u01/app/oracle"/>
  36.          <initParam name="audit_trail" value="DB"/>
  37.          <initParam name="memory_target" value="1610612736"/>
  38.          <initParam name="nls_territory" value="AMERICA"/>
  39.          <initParam name="db_block_size" value="8192"/>
  40.          <initParam name="open_cursors" value="300"/>
  41.          <initParam name="nls_language" value="AMERICAN"/>
  42.       </InitParams>
  43.       <MiscParams>
  44.          <customSGA>false</customSGA>
  45.          <characterSet>AL32UTF8</characterSet>
  46.          <nationalCharacterSet>AL16UTF16</nationalCharacterSet>
  47.          <archiveLogMode>false</archiveLogMode>
  48.          <initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
  49.       </MiscParams>
  50.       <SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
  51.    </InitParamAttributes>
  52.    <StorageAttributes>
  53.       <ControlfileAttributes id="Controlfile">
  54.          <maxDatafiles>100</maxDatafiles>
  55.          <maxLogfiles>16</maxLogfiles>
  56.          <maxLogMembers>3</maxLogMembers>
  57.          <maxLogHistory>292</maxLogHistory>
  58.          <maxInstances>8</maxInstances>
  59.          <image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
  60.          <image name="control02.ctl" filepath="{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/"/>
  61.       </ControlfileAttributes>
  62.       <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf" con_id="0">
  63.          <tablespace>SYSAUX</tablespace>
  64.          <temporary>false</temporary>
  65.          <online>true</online>
  66.          <status>0</status>
  67.          <size unit="MB">550</size>
  68.          <reuse>true</reuse>
  69.          <autoExtend>true</autoExtend>
  70.          <increment unit="KB">10240</increment>
  71.          <maxSize unit="MB">32767</maxSize>
  72.       </DatafileAttributes>
  73.       <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf" con_id="0">
  74.          <tablespace>SYSTEM</tablespace>
  75.          <temporary>false</temporary>
  76.          <online>true</online>
  77.          <status>0</status>
  78.          <size unit="MB">810</size>
  79.          <reuse>true</reuse>
  80.          <autoExtend>true</autoExtend>
  81.          <increment unit="KB">10240</increment>
  82.          <maxSize unit="MB">32767</maxSize>
  83.       </DatafileAttributes>
  84.       <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf" con_id="0">
  85.          <tablespace>TEMP</tablespace>
  86.          <temporary>true</temporary>
  87.          <online>true</online>
  88.          <status>0</status>
  89.          <size unit="MB">32</size>
  90.          <reuse>true</reuse>
  91.          <autoExtend>true</autoExtend>
  92.          <increment unit="KB">640</increment>
  93.          <maxSize unit="MB">32767</maxSize>
  94.       </DatafileAttributes>
  95.       <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf" con_id="0">
  96.          <tablespace>UNDOTBS1</tablespace>
  97.          <temporary>false</temporary>
  98.          <online>true</online>
  99.          <status>0</status>
  100.          <size unit="MB">70</size>
  101.          <reuse>true</reuse>
  102.          <autoExtend>true</autoExtend>
  103.          <increment unit="KB">5120</increment>
  104.          <maxSize unit="MB">32767</maxSize>
  105.       </DatafileAttributes>
  106.       <DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf" con_id="0">
  107.          <tablespace>USERS</tablespace>
  108.          <temporary>false</temporary>
  109.          <online>true</online>
  110.          <status>0</status>
  111.          <size unit="MB">5</size>
  112.          <reuse>true</reuse>
  113.          <autoExtend>true</autoExtend>
  114.          <increment unit="KB">1280</increment>
  115.          <maxSize unit="MB">32767</maxSize>
  116.       </DatafileAttributes>
  117.       <TablespaceAttributes id="SYSAUX" con_id="0">
  118.          <temporary>false</temporary>
  119.          <defaultTemp>false</defaultTemp>
  120.          <undo>false</undo>
  121.          <local>true</local>
  122.          <blockSize>8192</blockSize>
  123.          <allocation>1</allocation>
  124.          <uniAllocSize unit="KB">-1</uniAllocSize>
  125.          <initSize unit="KB">64</initSize>
  126.          <increment unit="KB">0</increment>
  127.          <incrementPercent>0</incrementPercent>
  128.          <minExtends>1</minExtends>
  129.          <maxExtends>2147483645</maxExtends>
  130.          <minExtendsSize unit="KB">64</minExtendsSize>
  131.          <logging>true</logging>
  132.          <recoverable>false</recoverable>
  133.          <maxFreeSpace>0</maxFreeSpace>
  134.          <autoSegmentMgmt>true</autoSegmentMgmt>
  135.          <bigfile>false</bigfile>
  136.          <datafilesList>
  137.             <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf"/>
  138.          </datafilesList>
  139.       </TablespaceAttributes>
  140.       <TablespaceAttributes id="SYSTEM" con_id="0">
  141.          <temporary>false</temporary>
  142.          <defaultTemp>false</defaultTemp>
  143.          <undo>false</undo>
  144.          <local>true</local>
  145.          <blockSize>8192</blockSize>
  146.          <allocation>1</allocation>
  147.          <uniAllocSize unit="KB">-1</uniAllocSize>
  148.          <initSize unit="KB">64</initSize>
  149.          <increment unit="KB">0</increment>
  150.          <incrementPercent>0</incrementPercent>
  151.          <minExtends>1</minExtends>
  152.          <maxExtends>2147483645</maxExtends>
  153.          <minExtendsSize unit="KB">64</minExtendsSize>
  154.          <logging>true</logging>
  155.          <recoverable>false</recoverable>
  156.          <maxFreeSpace>0</maxFreeSpace>
  157.          <autoSegmentMgmt>false</autoSegmentMgmt>
  158.          <bigfile>false</bigfile>
  159.          <datafilesList>
  160.             <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf"/>
  161.          </datafilesList>
  162.       </TablespaceAttributes>
  163.       <TablespaceAttributes id="TEMP" con_id="0">
  164.          <temporary>true</temporary>
  165.          <defaultTemp>true</defaultTemp>
  166.          <undo>false</undo>
  167.          <local>true</local>
  168.          <blockSize>8192</blockSize>
  169.          <allocation>2</allocation>
  170.          <uniAllocSize unit="KB">1024</uniAllocSize>
  171.          <initSize unit="KB">1024</initSize>
  172.          <increment unit="KB">1024</increment>
  173.          <incrementPercent>0</incrementPercent>
  174.          <minExtends>1</minExtends>
  175.          <maxExtends>-1</maxExtends>
  176.          <minExtendsSize unit="KB">1024</minExtendsSize>
  177.          <logging>false</logging>
  178.          <recoverable>false</recoverable>
  179.          <maxFreeSpace>0</maxFreeSpace>
  180.          <autoSegmentMgmt>false</autoSegmentMgmt>
  181.          <bigfile>false</bigfile>
  182.          <datafilesList>
  183.             <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf"/>
  184.          </datafilesList>
  185.       </TablespaceAttributes>
  186.       <TablespaceAttributes id="UNDOTBS1" con_id="0">
  187.          <temporary>false</temporary>
  188.          <defaultTemp>false</defaultTemp>
  189.          <undo>true</undo>
  190.          <local>true</local>
  191.          <blockSize>8192</blockSize>
  192.          <allocation>1</allocation>
  193.          <uniAllocSize unit="KB">-1</uniAllocSize>
  194.          <initSize unit="KB">64</initSize>
  195.          <increment unit="KB">0</increment>
  196.          <incrementPercent>0</incrementPercent>
  197.          <minExtends>1</minExtends>
  198.          <maxExtends>2147483645</maxExtends>
  199.          <minExtendsSize unit="KB">64</minExtendsSize>
  200.          <logging>true</logging>
  201.          <recoverable>false</recoverable>
  202.          <maxFreeSpace>0</maxFreeSpace>
  203.          <autoSegmentMgmt>false</autoSegmentMgmt>
  204.          <bigfile>false</bigfile>
  205.          <datafilesList>
  206.             <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf"/>
  207.          </datafilesList>
  208.       </TablespaceAttributes>
  209.       <TablespaceAttributes id="USERS" con_id="0">
  210.          <temporary>false</temporary>
  211.          <defaultTemp>false</defaultTemp>
  212.          <undo>false</undo>
  213.          <local>true</local>
  214.          <blockSize>8192</blockSize>
  215.          <allocation>1</allocation>
  216.          <uniAllocSize unit="KB">-1</uniAllocSize>
  217.          <initSize unit="KB">64</initSize>
  218.          <increment unit="KB">0</increment>
  219.          <incrementPercent>0</incrementPercent>
  220.          <minExtends>1</minExtends>
  221.          <maxExtends>2147483645</maxExtends>
  222.          <minExtendsSize unit="KB">64</minExtendsSize>
  223.          <logging>true</logging>
  224.          <recoverable>false</recoverable>
  225.          <maxFreeSpace>0</maxFreeSpace>
  226.          <autoSegmentMgmt>true</autoSegmentMgmt>
  227.          <bigfile>false</bigfile>
  228.          <datafilesList>
  229.             <TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/users01.dbf"/>
  230.          </datafilesList>
  231.       </TablespaceAttributes>
  232.       <RedoLogGroupAttributes id="1">
  233.          <reuse>false</reuse>
  234.          <fileSize unit="KB">204800</fileSize>
  235.          <Thread>1</Thread>
  236.          <member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
  237.       </RedoLogGroupAttributes>
  238.       <RedoLogGroupAttributes id="2">
  239.          <reuse>false</reuse>
  240.          <fileSize unit="KB">204800</fileSize>
  241.          <Thread>1</Thread>
  242.          <member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
  243.       </RedoLogGroupAttributes>
  244.       <RedoLogGroupAttributes id="3">
  245.          <reuse>false</reuse>
  246.          <fileSize unit="KB">204800</fileSize>
  247.          <Thread>1</Thread>
  248.          <member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
  249.       </RedoLogGroupAttributes>
  250.    </StorageAttributes>
  251. </DatabaseTemplate>

模板使用:以后就可以用此模板来生成DB了。

5. DBCA配置数据库选项

将依图中进行组件安装。

  1. sqlplus / as sysdba
  2. Set lines 100;
  3. Set pages 100;
  4. select * from dual;
  5. select comp_name,version,status from dba_registry order by 1;

参考:


oracle
最后修改时间：2019-12-17 12:28:24
文章转载自陶老师运维笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论