Oracle手工建库步骤(19c)
1 新建环境变量
## 备份原来的环境变量
[oracle@orcl19 ~]$ cd /home/oracle/
[oracle@orcl19 ~]$ mv .bash_profile .bash_profile.orcl_bak
[oracle@orcl19 ~]$ cp .bash_profile.orcl_bak .bash_profile
[oracle@orcl19 ~]$ vim .bash_profile
## 主要修改了
# export ORACLE_SID=orcl19
export ORACLE_SID=test2
# export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
[oracle@orcl19 ~]$ env |grep ORACLE
ORACLE_SID=orcl19
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
2 创建pfile
[oracle@orcl19 ~]$ cd $ORACLE_HOME/dbs
[oracle@orcl19 dbs]$ ls
hc_orcl19.dat init.ora lkORCL orapworcl19 snapcf_orcl19.f spfileorcl19.ora
## 用初始文件，拷下来改
[oracle@orcl19 dbs]$ tail -200 init.ora
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'
新建一个
[oracle@orcl19 dbs]$ vim inittest2.ora
db_name='test2'
memory_target=1G
processes = 150
audit_file_dest='/opt/oracle/admin/test2/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/opt/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/opt/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=TEST2XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/opt/oracle/oradata/test2/control01.ctl,/opt/oracle/oradata/test2/control02.ctl)
compatible ='11.2.0'
3 把对应目录创建好
mkdir -p /opt/oracle/oradata/test2
mkdir -p /opt/oracle/fast_recovery_area
mkdir -p /opt/oracle/admin/test2/adump
#审计
4 创建密码文件
密码文件在dbs目录下
[oracle@orcl19 dbs]$ ls
hc_orcl19.dat init.ora lkORCL orapworcl19 snapcf_orcl19.f spfileorcl19.ora
## 命名：orapw+SID
[oracle@orcl19 dbs]$ orapwd file=orapwtest2 password=test2@1234
[oracle@orcl19 dbs]$ ls
hc_orcl19.dat init.ora lkORCL orapworcl19 orapwtest2 snapcf_orcl19.f spfileorcl19.ora
5 启动实例 创建spfile
sqlplus / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
## 创建出来了
[oracle@orcl19 dbs]$ ls
hc_orcl19.dat init.ora inittest2.ora lkORCL orapworcl19 orapwtest2 snapcf_orcl19.f spfiletest2.ora
6 启动到nomount 状态
## 控制文件还没有创建，所以需要nomount状态
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 616562688 bytes
Database Buffers 440401920 bytes
Redo Buffers 7868416 bytes
7 sql脚本建库
创建一个脚本，这样子方便移植到其他机器上批量建库
## 需要建一些必需的表空间与数据库文件
[oracle@orcl19 dbs]$ vi db_create.sql
CREATE DATABASE test2
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/opt/oracle/oradata/test2/redo01a.log') SIZE 50M BLOCKSIZE 512,
GROUP 2 ('/opt/oracle/oradata/test2/redo02a.log') SIZE 50M BLOCKSIZE 512,
GROUP 3 ('/opt/oracle/oradata/test2/redo03a.log') SIZE 50M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 8192
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/opt/oracle/oradata/test2/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/opt/oracle/oradata/test2/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE '/opt/oracle/oradata/test2/users01.dbf'
SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/opt/oracle/oradata/test2/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/opt/oracle/oradata/test2/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
[oracle@orcl19 dbs]$ vi db_run.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/sqlplus/admin/pupbld.sql
8 运行脚本
## @号是在当前目录运行
[oracle@orcl19 dbs]$ sqlplus / as sysdba
SQL> @db_create.sql
Database created.
## 自动进入读写模式
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/test2/system01.dbf
/opt/oracle/oradata/test2/sysaux01.dbf
/opt/oracle/oradata/test2/undotbs01.dbf
/opt/oracle/oradata/test2/users01.dbf
## 等待十几分钟
SQL > @db_run.sql
SQL> Rem See sqlsessstart.sql for the corresponding start script.
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/sqlsessend.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/sqlsessend.sql
SQL> Rem SQL_PHASE: MISC
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem surman 05/04/18 - 27464252: Update SQL_PHASE
SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
SQL> Rem surman 03/08/13 - Created
SQL> Rem
SQL>
SQL> alter session set "_ORACLE_SCRIPT" = false;
Session altered.
....
## 运行完脚本后，重启一下
SQL> shutdown immediate;
SQL> startup;
9 启动监听
[oracle@orcl19 dbs]$ lsnrctl start
[oracle@orcl19 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-MAR-2023 17:38:59
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 06-MAR-2023 17:38:01
Uptime 0 days 0 hr. 1 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/orcl19/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl19.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "TEST2XDB" has 1 instance(s).
Instance "test2", status READY, has 1 handler(s) for this service...
Service "test2" has 1 instance(s).
Instance "test2", status READY, has 1 handler(s) for this service...
The command completed successfully
## ***XDB，这是一个用来高效处理XML的一个服务，是正常的
10 登录到实例
登录到不同实例，是根据环境变量的 ORACLE_SID来区分的
[oracle@orcl19 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 6 17:42:32 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
test2
