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

文章来源: 陶老师运维笔记-微信公众号

1. DBCA 介绍

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

2. DBCA 图形创建数据库

用dbca创建数据库。

$cd database
$dbca

选择Advanced configuration。

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

Finish。

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

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

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

假设数据库名字为gpdb。

$export DISPLAY=:0.0
$env |grep ora -i
$dbca

最终生成文件如下：

#gpdb为database名字。
$cd /u01/app/oracle/admin/gpdb/
$tree -L 2
scripts/
scripts/tempControl.ctl
scripts/postDBCreation.sql
scripts/initgpdbTempOMF.ora
scripts/gpdb.sh
scripts/init.ora
scripts/CloneRmanRestore.sql
scripts/lockAccount.sql
scripts/rmanRestoreDatafiles.sql
scripts/initgpdbTemp.ora
scripts/cloneDBCreation.sql
scripts/gpdb.sql
scripts/postScripts.sql

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

gpdb.sh

#!/bin/sh

OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/admin/gpdb/adump
mkdir -p /u01/app/oracle/admin/gpdb/dpdump
mkdir -p /u01/app/oracle/admin/gpdb/pfile
mkdir -p /u01/app/oracle/audit
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/gpdb
mkdir -p /u01/app/oracle/oradata/gpdb
mkdir -p /u01/app/oracle/product/12.2.0/db_1/dbs
umask ${OLD_UMASK}
PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
ORACLE_SID=gpdb; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab: gpdb:/u01/app/oracle/product/12.2.0/db_1:Y
/u01/app/oracle/product/12.2.0/db_1/bin/sqlplus /nolog @/u01/app/oracle/admin/gpdb/scripts/gpdb.sql

init.ora

cat init.ora  |grep -v '#'|grep -v "^ $" 
db_block_size=8192
open_cursors=300
db_name="gpdb"
control_files=("/u01/app/oracle/oradata/gpdb/control01.ctl", "/u01/app/oracle/oradata/gpdb/control02.ctl")
compatible=12.2.0
diagnostic_dest=/u01/app/oracle
nls_language="AMERICAN"
nls_territory="AMERICA"
processes=300
sga_target=1148m
audit_file_dest="/u01/app/oracle/admin/gpdb/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=gpdbXDB)"
pga_aggregate_target=383m
undo_tablespace=UNDOTBS1

cloneDBCreation.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/gpdb/scripts/cloneDBCreation.log append
shutdown abort;
startup nomount pfile="/u01/app/oracle/admin/gpdb/scripts/init.ora";
Create controlfile reuse set database "gpdb"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile 
'&&file0',
'&&file1',
'&&file2',
'&&file3'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/gpdb/redo01.log') SIZE 200M,
GROUP 2 ('/u01/app/oracle/oradata/gpdb/redo02.log') SIZE 200M,
GROUP 3 ('/u01/app/oracle/oradata/gpdb/redo03.log') SIZE 200M RESETLOGS;
exec dbms_backup_restore.zerodbid(0);
shutdown immediate;
startup nomount pfile="/u01/app/oracle/admin/gpdb/scripts/initgpdbTemp.ora";
Create controlfile reuse set database "gpdb"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile 
'&&file0',
'&&file1',
'&&file2',
'&&file3'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/gpdb/redo01.log') SIZE 200M,
GROUP 2 ('/u01/app/oracle/oradata/gpdb/redo02.log') SIZE 200M,
GROUP 3 ('/u01/app/oracle/oradata/gpdb/redo03.log') SIZE 200M RESETLOGS;
alter system enable restricted session;
alter database "gpdb" open resetlogs;
DECLARE 
cursor cur_services is 
select name from dba_services where name like 'seeddata%'; 
BEGIN 
 for i in cur_services loop 
 dbms_service.delete_service(i.name); 
 end loop; 
END; 
/
alter database rename global_name to "gpdb";
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/gpdb/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
select tablespace_name from dba_tablespaces where tablespace_name='USERS';
ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION null;
alter user sys account unlock identified by "&&sysPassword";
connect "SYS"/"&&sysPassword" as SYSDBA
alter user system account unlock identified by "&&systemPassword";
select sid, program, serial#, username from v$session;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter database national character set INTERNAL_CONVERT AL16UTF16;
alter system disable restricted session;

lockAccount.sql

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

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

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

DBCA管理模板:

生成的模板文件：

$cd /u01/app/oracle/product/12.2.0/db_1/assistants/dbca/templates
<DatabaseTemplate name="dbca template dev" description="" version="12.2.0.1.0">
   <CommonAttributes>
      <option name="OMS" value="true"/>
      <option name="JSERVER" value="true"/>
      <option name="SPATIAL" value="true"/>
      <option name="IMEDIA" value="true"/>
      <option name="ORACLE_TEXT" value="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="SAMPLE_SCHEMA" value="true">
         <tablespace id="USERS"/>
      </option>
      <option name="CWMLITE" value="true">
         <tablespace id="SYSAUX"/>
      </option>
      <option name="APEX" value="false"/>
      <option name="DV" value="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="12.2.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="1610612736"/>
         <initParam name="nls_territory" value="AMERICA"/>
         <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">550</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">810</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">70</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>

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

5. DBCA配置数据库选项

将依图中进行组件安装。

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

参考:

• Oracle12c图形化安装及问题处理
• docs.oracle.com/cd/E26854_0…

---