DBCA命令行建库

DBCA命令行建库

1 命令行建库non-cdb

1.1 设置实例（SID）的名称

export ORACLE_SID=prod

1.2 确认其它环境变量的值

echo $ORACLE_BASE
echo $ORACLE_HOME

1.3 生成口令文件

cd $ORACLE_HOME/dbs
cp orapworcl orapwprod

1.4 生成参数文件

export ORACLE_SID=orcl
sqlplus / as sysdba

create pfile from spfile;

cd $ORACLE_HOME/dbs
cp initorcl.ora initprod.ora

修改参数文件，将原参数文件中的orcl，ORCL替换为prod，PROD

vi initprod.ora

:%s#orcl#prod#g

:%s#ORCL#PROD#g

1.5 创建目录

mkdir /u01/app/oracle/oradata/PROD
mkdir -p /u01/app/oracle/admin/prod/adump

1.6 制作创建数据库的脚本

vi /home/oracle/createdb.sql

写入：

CREATE DATABASE PROD
  USER SYS IDENTIFIED BY oracle
  USER SYSTEM IDENTIFIED BY oracle
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/redo01a.log','/u01/app/oracle/oradata/PROD/redo01b.log') SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/app/oracle/oradata/PROD/redo02a.log','/u01/app/oracle/oradata/PROD/redo02b.log') SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/app/oracle/oradata/PROD/redo03a.log','/u01/app/oracle/oradata/PROD/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/PROD/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users DATAFILE '/u01/app/oracle/oradata/PROD/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/PROD/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  USER_DATA TABLESPACE usertbs DATAFILE '/u01/app/oracle/oradata/PROD/usertbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

1.7 连接实例执行创建数据库脚本

设置环境变量

export ORACLE_SID=prod
sqlplus / as sysdba

startup nomount;
@/home/oracle/createdb.sql

1.8 生成spfile

create spfile from pfile;

1.9 执行脚本

conn / as sysdba
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

1.10 查看进程信息及数据库信息

select instance_name from v$instance;
select cdb from v$database;

ps -ef | grep _prod | grep -v grep

 

2 命令行建库CDB

2.1 设置实例（SID）的名称

export ORACLE_SID=cdb2

2.2 确认其它环境变量的值

echo $ORACLE_BASE
echo $ORACLE_HOME

2.3 生成口令文件

cd $ORACLE_HOME/dbs
cp orapworcl orapwcdb2

2.4 生成参数文件

export ORACLE_SID=orcl
sqlplus / as sysdba

create pfile from spfile;

cd $ORACLE_HOME/dbs
cp initorcl.ora initcdb2.ora

修改参数文件，将原参数文件中的orcl，ORCL替换为cdb2，CDB2

vi initcdb2.ora

:%s#orcl#cdb2#g

:%s#ORCL#CDB2#g

注意：一定要添加enable_pluggable_database=true;否则，执行建库脚本的时候，会提示ORA-65093: multitenant container database not set up properly错误

2.5 创建目录

mkdir -p /u01/app/oracle/admin/cdb2/adump
mkdir /u01/app/oracle/oradata/CDB2/
mkdir /u01/app/oracle/oradata/pdbseed/

2.6 制作创建数据库的脚本

vi /home/oracle/createcdb2.sql

写入：

CREATE DATABASE CDB2
  USER SYS IDENTIFIED BY oracle
  USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/u01/app/oracle/oradata/CDB2/redo01a.log','/u01/app/oracle/oradata/CDB2/redo01b.log') SIZE 100M BLOCKSIZE 512,
                                           GROUP 2 ('/u01/app/oracle/oradata/CDB2/redo02a.log','/u01/app/oracle/oradata/CDB2/redo02b.log') SIZE 100M BLOCKSIZE 512,
                                           GROUP 3 ('/u01/app/oracle/oradata/CDB2/redo03a.log','/u01/app/oracle/oradata/CDB2/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/CDB2/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/CDB2/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE deftbs DATAFILE '/u01/app/oracle/oradata/CDB2/deftbs01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/CDB2/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/oradata/CDB2/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
      FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/CDB2/','/u01/app/oracle/oradata/pdbseed/')
    LOCAL UNDO ON;

2.7 连接实例执行创建数据库脚本

设置环境变量

export ORACLE_SID=cdb2
sqlplus / as sysdba

startup nomount;
@/home/oracle/creatcdb2.sql

2.8 生成spfile

create spfile from pfile;

2.9 执行脚本

@?/rdbms/admin/catcdb.sql

创建数据字典的过程，时间较长，耐心等待...

创建完成...

2.10 查看进程信息及数据库信息

select instance_name,status from v$instance;
select cdb from v$database;

ps -ef | grep _cdb2 | grep -v grep

 

3 DBCA静默安装CDB（附带安装一个pdb1）

3.1 修改建库响应文件

cd /u01/app/oracle/product/19.3.0/dbhome_1/assistants/dbca
mv dbca.rsp dbca.rsp.bak
vi dbca.rsp

写入：

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
gdbName=cdb3.example.com
sid=cdb3
databaseConfigType=SI
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=pdb1
pdbAdminPassword=oracle
templateName=General_Purpose.dbc
sysPassword=oracle
systemPassword=oracle
datafileDestination=/u01/app/oracle/oradata/CDB3
sampleSchema=true
memoryPercentage=30

3.2 建库

dbca -silent -createDatabase -responsefile /u01/app/oracle/product/19.3.0/dbhome_1/assistants/dbca/dbca.rsp

3.3 登陆数据库

export ORACLE_SID=cdb3
sqlplus / as sysdba

select instance_name,status from v$instance;
select cdb from v$database;
show pdbs
show parameter instance_name