使用CREATE DATABASE语句创建数据库

设置数据库环境
export ORACLE_SID=newcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1 export PATH=$ORACLEB​ASE/product/19.0.0/dbhome1​exportPATH=ORACLE_HOME/bin:$PATH

export | grep ‘ORACLE|PATH’

使用密码文件
orapwd FILE=’/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwnewcdb’ ENTRIES=10 FORMAT=12

创建初始化参数文件
cd $ORACLE_HOME/dbs
vi initnewcdb.ora

sga_target=4096M
pga_aggregate_target=1024M
audit_file_dest="/u01/app/oracle/admin/newcdb/adump"
audit_trail=db
compatible=12.1.0.2.0
control_files=("/u01/app/oracle/oradata/newcdb/control01.ctl","/u01/app/oracle/fast_recovery_area/newcdb/control02.ctl")
db_block_size=8192
db_domain=localdomain
db_name=“newcdb”
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4560m
diagnostic_dest=/u01/app/oracle
dispatchers="(PROTOCOL=TCP) (SERVICE=newcdbXDB)"
enable_pluggable_database=true
open_cursors=300
processes=300
remote_login_passwordfile=EXCLUSIVE
undo_tablespace=UNDOTBS1

创建所需的目录
控制文件
mkdir -p /u01/app/oracle/oradata/newcdb
mkdir -p /u01/app/oracle/fast_recovery_area/newcdb

mkdir -p /u01/app/oracle/oradata/newcdb/pdbseed
日志文件
mkdir -p /u02/app/oracle/oradata/newcdb
mkdir -p /u01/app/oracle/admin/newcdb/adump

创建数据库脚本
mkdir /home/oracle/bin
vi /home/oracle/bin/create_db_newcdb.sql



CREATE DATABASE newcdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 (’/u01/app/oracle/oradata/newcdb/redo01a.log’,’/u02/app/oracle/oradata/newcdb/redo01b.log’)
SIZE 100M BLOCKSIZE 512,
GROUP 2 (’/u01/app/oracle/oradata/newcdb/redo02a.log’,’/u02/app/oracle/oradata/newcdb/redo02b.log’)
SIZE 100M BLOCKSIZE 512,
GROUP 3 (’/u01/app/oracle/oradata/newcdb/redo03a.log’,’/u02/app/oracle/oradata/newcdb/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/newcdb/system01.dbf’
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/newcdb/sysaux01.dbf’
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS
DATAFILE ‘/u01/app/oracle/oradata/newcdb/users01.dbf’
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u01/app/oracle/oradata/newcdb/temp01.dbf’
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS1
DATAFILE ‘/u01/app/oracle/oradata/newcdb/undotbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = (’/u01/app/oracle/oradata/newcdb/’,
‘/u01/app/oracle/oradata/newcdb/pdbseed/’)
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
登录数据库
sqlplus / as sysdba

创建参数文件
create spfile from pfile=’/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initnewcdb.ora’;

启动实例
;startup nomount;

使用CREATE DATABASE语句创建新的CDB
set echo on
set timing on
@/home/oracle/bin/create_db_newcdb.sql

数据库是否为CDB
SELECT dbid, name, created, log_mode, open_mode, cdb, con_id FROM v$database;

种子数据库
col name for a8
SELECT con_id, dbid, name, open_mode, restricted, TO_CHAR(open_time, ‘DD-MON-YY HH:MI:SS AM’) OPEN_TIME FROM v$containers;

查询CDB / PDB名称，表空间名称，数据文件位置，大小和状态

col db_name for a8
col TABLESPACE_NAME for a8
col DATA_FILE_NAME for a55
set line 300
SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status FROM v$tablespace a, v$tablespacea,vdatafile b, v$containers c
WHERE a.con_id = b.con_id
AND a.con_id = c.con_id
AND a.ts# = b.ts#
ORDER BY a.con_id, a.TS#;

检查控制文件
show parameter control_files

col members for a55
SELECT a.group#, a.members, a.bytes/1024/1024 SIZE_MB, a.status, b.member
FROM v$log a, v$loga,vlogfile b
WHERE a.group# = b.group#;
GROUP# MEMBERS SIZE_MB STATUS MEMBER

是否按照响应文件设置了NLS参数
SELECT * FROM v$nls_parameters WHERE parameter LIKE ‘%CHARACTERSET%’;
PARAMETER VALUE CON_ID

运行下个面4个脚本
catalog.sql创建数据字典表的视图，动态性能视图以及许多视图的公共同义词
catproc.sql运行PL / SQL所需或与PL / SQL一起使用的所有脚本
pupbld.sql: 对于SQL * Plus是必需的。启用SQL * Plus以禁用用户命令。
utlrp.sql：重新编译处于无效状态的所有PL / SQL模块，包括程序包，过程和类型。
@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql


@?/rdbms/admin/sqlsessend.sql

查询数据库组件的状态
SELECT name, comp_name, version, status FROM v$database, dba_registry;
NAME COMP_NAME VERSION STATUS




CREATE PLUGGABLE DATABASE newpdb1 ADMIN USER pdbadmin IDENTIFIED BY oracle
FILE_NAME_CONVERT=(’/u01/app/oracle/oradata/newcdb/pdbseed/’,’/u01/app/oracle/oradata/newcdb/newpdb1/’);

show pdbs

ALTER PLUGGABLE DATABASE newpdb1 OPEN;

show pdbs