Oracle 19C利用Create Database手工创建数据库

除了利用DBCA创建数据库之外，还可以利用Create Database语句手工创建数据库，不过Oracle建议利用DBCA建库，本篇主要探讨如何手工进行数据库的创建。

从其它单点库打包一个相应版本的数据库软件

Root用户登录：

cd  /;   tar  zcvf  oracle19.13.tar.gz   oracle

将压缩包上传到部署节点/下:

创建用户：

/usr/sbin/groupadd -g 5003 oinstall

/usr/sbin/groupadd -g 5004 dba     #如果没有dba可能sqlplus登录时报用户名密码错误

/usr/sbin/useradd -u 6003 -g oinstall -G dba -d /home/oracle oracle

echo oracle|passwd --stdin oracle

参数资源限制文件系统等基线参数配置忽略，按其它文档操作即可，这里只整理手工建库步骤部分

root用户登录：

cd  /;  tar xvf  oracle19.13.tar.gz

#删除旧库信息

rm -rf  /oracle/admin/*     

rm -rf  /oracle/diag/tnslsnr/*

rm -rf  /oracle/diag/rdbms/*

主要创建过程如下：

一、创建目录

[oracle@test oracle]$ mkdir -p/oracle/admin/testdb/adump   #审计文件存放位置

[oracle@test oracle]$ mkdir -p /oracle/oradata/testdb        #数据存放位置

[oracle@test oracle]$

二、创建Pfile

[oracle@test oracle]$ vim /oracle/db19c/dbs/inittestdb.ora

*._library_cache_advice=FALSE

*._serial_direct_read='NEVER'

*.audit_file_dest='/oracle/admin/testdb/adump'

*.audit_trail='NONE'

*.compatible='19.0.0'    #兼容版本

*.control_files='/oracle/oradata/testdb/control01.ctl','/oracle/oradata/testdb/control02.ctl'

*.db_block_size=8192

*.db_files=1500

*.db_name='testdb'     #库名

*.deferred_segment_creation=FALSE

*.diagnostic_dest='/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.event='10949 TRACE NAME CONTEXT FOREVER:28401 TRACE NAME CONTEXT FOREVER,level 1' #*.local_listener='LISTENER_SJYZDB03'

*.max_dump_file_size='500M'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=2000

*.parallel_force_local=TRUE

*.pga_aggregate_target=1289m

*.processes=1000

*.remote_listener=''

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=200

*.sga_target=5868m 

*.undo_tablespace='UNDOTBS1'

#sga和pga这里是按32G物理内存分配的，需要根据实际调整，否则无法起库

三、创建密码文件（这里忽略即可）

[oracle@test oracle]$ orapwd file=/oracle/db19c/dbs/orapwtestdb entries=10

Enter password for SYS:

[oracle@test oracle]$

四、连接实例

[oracle@test oracle]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 Production on Tue Sep 5 05:42:59 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved

Connected to an idle instance.

五、创建SPfile

SQL> create spfile from pfile;

File created.

六、启动到NoMount状态

SQL> startup nomount

ORACLE instance started.

Total System Global Area  521936896 bytes

Fixed Size     2254824 bytes

Variable Size   314574872 bytes

Database Buffers   201326592 bytes

Redo Buffers     3780608 bytes

SQL>

七、创建数据库

CREATE DATABASE testdb 

USER SYS IDENTIFIED BY Oracle123

USER SYSTEM IDENTIFIED BY Oracle123

LOGFILE GROUP 1 ('/oracle/oradata/testdb/redo0101.log','/oracle/oradata/testdb/redo0102.log') SIZE 1000M BLOCKSIZE 512,

GROUP 2 ('/oracle/oradata/testdb/redo0201.log','/oracle/oradata/testdb/redo0202.log') SIZE 1000M BLOCKSIZE 512,

GROUP 3 ('/oracle/oradata/testdb/redo0301.log','/oracle/oradata/testdb/redo0302.log') SIZE 1000M BLOCKSIZE 512

MAXLOGFILES 30

MAXLOGMEMBERS 5

MAXLOGHISTORY 1000

MAXDATAFILES 5000

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/oracle/oradata/testdb/system01.dbf' SIZE 10G REUSE

SYSAUX DATAFILE '/oracle/oradata/testdb/sysaux01.dbf' SIZE 10G REUSE

DEFAULT TABLESPACE users

DATAFILE '/oracle/oradata/testdb/users01.dbf' SIZE 5G REUSE

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/oracle/oradata/testdb/temp01.dbf' SIZE 10G REUSE

UNDO TABLESPACE undotbs1

DATAFILE '/oracle/oradata/testdb/undotbs01.dbf' SIZE 10G REUSE;

Database created.

SQL> select instance_name,status from v$instance;--->创建完数据库，数据库自动打开

INSTANCE_NAME  STATUS

---------------- ------------

testdb  OPEN

SQL>

八、运行脚本建立数据字典视图

以SYSDBA管理权限运行下面的脚本：

SQL> @?/rdbms/admin/catalog.sql --->创建数据字典视图、动态性能视图和同义词

SQL> @?/rdbms/admin/catproc.sql --->运行所有PL/SQL需要或使用的脚本

SQL> @?/rdbms/admin/utlrp.sql --->重新编译失效状态的PL/SQL模块，包括包、过程或类型

 以SYSTEM用户执行下面的脚本：

SQL> conn system/oracle

Connected.

SQL> @?/sqlplus/admin/pupbld.sql --->SQL*Plus相关的

九、利用netmgr配置监听

具体配置可参考Oracle 11g利用Netmgr配置监听器和服务

[oracle@test oracle]$ more /oracle/db19c/network/admin/listener.ora

 # listener.ora Network Configuration File: /oracle/db19c/network/admin/listener.ora # Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11.109.136.41)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

 )

[oracle@test oracle]$

[oracle@test oracle]$ lsnrctl start

------------------------

Alias                     testdb

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                09-SEP-2017 23:32:09

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle//oracle/db19c/network/admin/listener.ora

Listener Log File         /oracle/diag/tnslsnr/test/testdb/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))

Services Summary...

Service "testdb" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

The command completed successfully

#参数调整、归档、创建用户表空间等参照其它文档

#根据网络资源验证整理