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