Linux下创建Oracle数据库的实例

1.安装前准备

1.1.安装前准备一

安装前准备信息

数据库版本：	Oracle19.16.0.0.0
Linux版本：	Red Hat Enterprise Linux Server release 7.9 (Maipo)
主机名：	ace-test-84
IP：	10.10.10.212
SID：	dcpfareint
service_name：	dcpfarei
数据库使用字符集：	这里用AL32UTF8 字符集

 

1.2.安装前准备二

1.2.1.创建用户组

-- 创建用户组
[root@ace-test-84 ~]$ groupadd -g 5001 oinstall
[root@ace-test-84 ~]$ groupadd -g 5002 dba

1.2.2.删除用户组（拓展）

-- 删除用户组
[root@ace-test-84 ~]$ groupdel test
-- 如果组里面有用户需要先删除用户或移动用户，才能删除组
[root@ace-test-84 ~]$  userdel test ###删除用户

1.2.3.创建oracle用户

#创建oracle用户—编号为6001，默认组为oinstall ，并添加到dba组，主要是oinstall组发生作用
[root@ace-test-84 ~]$  useradd -u 6001 -g oinstall -G dba oracle

1.2.4.修改oracle得密码

# passwd oracle ----设置oracle用户密码（Zyl@0411）
 
[root@ace-test-84 ~]$ passwd oracle
Changing password for user oracle.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

1.2.5. 检查创建的oracle用户

#检查创建的oracle用户
[root@ace-test-84 ~]$  su - oracle
[oracle@ace-test-84 ~]$ id
uid=6001(oracle) gid=5001(oinstall) groups=5001(oinstall),5002(dba)
[oracle@ace-test-84 ~]$ 

2.查看环境

[oracle@ace-test-84 ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH


export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=tstttdp1
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
stty erase ^H
[oracle@ace-test-84 ~]$ echo $ORACLE_HOME
/oracle/app/oracle/product/19.3.0/db_1
 


[oracle@ace-test-84 ~]$ echo $ORACLE_BASE
/oracle/app/oracle
[oracle@ace-test-84 ~]$ 

3.创建新的实例名

首先设置要创建的ORACLE的SID

[oracle@ace-test-84 ~]$ export ORACLE_SID=dcpfareint

4.创建初始化文件

cd $ORACLE_HOME/dbs目录下

命名方法：init+实例名.ora 本例中initdcpfareint

[oracle@ace-test-84 ~]$ cd $ORACLE_HOME/dbs
[oracle@ace-test-84 dbs]$ vi initdcpfareint.ora

# 添加如下内容：
dcpfareint.__data_transfer_cache_size=0
dcpfareint.__db_cache_size=72209137664
dcpfareint.__inmemory_ext_roarea=0
dcpfareint.__inmemory_ext_rwarea=0
dcpfareint.__java_pool_size=1879048192
dcpfareint.__large_pool_size=1342177280
dcpfareint.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
dcpfareint.__pga_aggregate_target=10737418240
dcpfareint.__sga_target=85899345920
dcpfareint.__shared_io_pool_size=268435456
dcpfareint.__shared_pool_size=9395240960
dcpfareint.__streams_pool_size=536870912
dcpfareint.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/dcpfareint/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/TSDCPFAREINT/controle/control01.ctl','/oracle/app/oracle/fast_recovery_area/DCPFAREINT/control02.ctl'
*.db_block_size=8192
*.db_name='dcpfareint'
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcpfareintXDB)'
*.local_listener='LISTENER_DCPFAREINT'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=10g
*.processes=10000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=80g
*.undo_tablespace='UNDOTBS1'

注意：
1.不要用原来的<ORACLE_BASE>作为路径，修改为对应的绝对路径，否则会报错

2.注意control_files 应该是新的文件，否则会报文件已存在的错误

5.创建密码文件

orapwd file=$ORACLE_HOME/dbs/orapw+实例名 password=sys的密码 entries=16 force=y

[oracle@ace-test-84 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdcpfareint password=AFuwd7_@5K entries=16 force=y

 6.创建相应的目录

[oracle@ace-test-84 ~]$ mkdir -p /oracle/app/oracle/oradata/ORCL/controle
[oracle@ace-test-84 ~]$ mkdir -p /oracle/app/oracle/oradata/ORCL/dataFile
[oracle@ace-test-84 ~]$ mkdir -p /oracle/app/oracle/oradata/ORCL/redoLog

7.启动新建实例的数据库状态为nomount

切换到新建的实例下面，然后使用数据库的静态文件启动数据库到nomount状态

注意：

使用静态文件启动的nomount状态前，需要先创建审计目录

执行：STARTUP NOMOUNT PFILE= '/oracle/app/oracle/product/19.3.0/db_1/dbs/initdcpfareint.ora'; 前，

需要先创建下面的路径才行，不然报错
mkdir -p /oracle/app/oracle/admin/dcpfareint/adump

7.1.创建审计目录

[oracle@ace-test-84 ~]$ mkdir -p /oracle/app/oracle/admin/dcpfareint/adump

7.2.切换到新建的实例环境下，然后使用静态文件启动到nomount状态下 

[oracle@ace-test-84 ~]$ export ORACLE_SID=dcpfareint
[oracle@ace-test-84 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 26 15:34:05 2024
Version 19.16.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> STARTUP NOMOUNT PFILE= '/oracle/app/oracle/product/19.3.0/db_1/dbs/initdcpfareint.ora';
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                 37219408 bytes
Variable Size            1.3153E+10 bytes
Database Buffers         7.2478E+10 bytes
Redo Buffers              231215104 bytes
SQL> 

7.3.在nomount状态下，创建数据库的动态启动文件

# 在nomount状态下，创建数据库的动态启动文件
SQL> CREATE SPFILE = '/oracle/app/oracle/product/19.3.0/db_1/dbs/spfiledcpfareint.ora' FROM PFILE = '/oracle/app/oracle/product/19.3.0/db_1/dbs/initdcpfareint.ora';

7.4.关闭数据库，重启数据库到nomount状态下

# 在上面启动到nomount的情况下，关闭数据库
SQL> shutdown immediate

# 重启数据库，使用动态启动文件
[oracle@ace-test-84 ~]$ export ORACLE_SID=tstttdp1
[oracle@ace-test-84 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 26 15:34:05 2024
Version 19.16.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> startup nomount
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                 37219408 bytes
Variable Size            1.3153E+10 bytes
Database Buffers         7.2478E+10 bytes
Redo Buffers              231215104 bytes
SQL> 

8.在nomount状态下，创建数据库

在上面使用动态文件重启到nomount的情况下，创建数据库

SQL> create database dcpfarei
		user sys identified by oracle
		user system identified by oracle
		logfile group 1 ('/oracle/app/oracle/oradata/TSDCPFAREINT/redoLog/redo01.log') size 100M,
				group 2 ('/oracle/app/oracle/oradata/TSDCPFAREINT/redoLog/redo02.log') size 100M,
				group 3 ('/oracle/app/oracle/oradata/TSDCPFAREINT/redoLog/redo03.log') size 100M
	    maxlogfiles 10
	    maxlogmembers 4
	    maxloghistory 5
	    maxdatafiles 100
	    maxinstances 1
	    character set AL32UTF8
	    national character set AL16UTF16
	    datafile '/oracle/app/oracle/oradata/TSDCPFAREINT/dataFile/system.dbf' size 16g
	    sysaux datafile '/oracle/app/oracle/oradata/TSDCPFAREINT/dataFile/sysaux.dbf' size 16g
	    default temporary tablespace temp tempfile '/oracle/app/oracle/oradata/TSDCPFAREINT/dataFile/temp.dbf' size 10g
	    undo tablespace UNDOTBS1 datafile '/oracle/app/oracle/oradata/TSDCPFAREINT/dataFile/undotbs.dbf' size 10G;

9.执行建库和数据字典脚本(nomount下执行)

9.1.执行下面的命令

执行下面命令：
SQL> @?/rdbms/admin/catalog.sql;   --创建数据库的统计视图，审计视图
SQL> @?/rdbms/admin/catproc.sql;   -- 数据库具有pl/sql的使用功能。    生成同义词典表与相应视图的架构表（最重要的，建库必须运行的） 
SQL> @?/rdbms/admin/catexp.sql;    --数据库具有导入导出功能，即可以使用exp、imp、expdp、impdp
SQL> @?/rdbms/admin/utlrp.sql      --编译
SQL> @?/sqlplus/admin/pupbld.sql   --system运行
SQL> @?/rdbms/admin/cataudit.sql   --创建审计数据字典视图

@?/rdbms/admin/catjobq.sql        --创建关于job的视图
@?/rdbms/admin/dbmsutil.sql       --针对的是DBMS_SYSTEM            GRANT EXECUTE ON DBMS_SYSTEM TO username;

@?/rdbms/admin/utlsampl.sql    --生成一个系统事例默认的用户名与密码（用户名为scott密码为tiger）从oracle7开始一直就有了。

 9.2.打开数据库

SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE OPEN;

10.修改监听配置文件listener.ora

到$ORACLE_HOME/network/admin目录下：vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ace-test-84)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
 )

11.修改配置tns文件

到$ORACLE_HOME/network/admin目录下：vi tnsnames.ora

LISTENER_DCPFAREI =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ace-test-84)(PORT = 1521))


TSTTTDP1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ace-test-84)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tstttdp1)
    )
  )

DCPFAREI =                 ---这个名字随便
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ace-test-84)(PORT = 1521)) -- IP地址，如果使用hostname的话，需要在/etc/hosts 添加 10.10.10.212   ace-test-84
    (CONNECT_DATA =
      (SERVER = DEDICATED)  ---连接方式，这里是专用
      (SERVICE_NAME = dcpfarei) -- 服务名
    )
  )

12.配置好tns后，数据库启动监听的时候，监听不到新建的服务，需要执行下面的操作

注册动态监听
SQL> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=10.211.129.51)(PORT=1521))' sid='dcpfareint';

System altered.
SQL> alter system register;

System altered.

执行上面的操作后，动态参数的变化：
vi spfiledcpfareint.ora 

执行前：
*.local_listener='LISTENER_DCPFAREINT'

执行后：
*.local_listener='LISTENER_DCPFAREINT'
dcpfareint.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.212)(PORT=1521))'

13.启动监听

-- 启动监听
[oracle@ace-test-84 ~]$ lsnrctl start
-- 查看监听的状态
[oracle@ace-test-84 ~]$ lsnrctl status
-- 停止监听
[oracle@ace-test-84 ~]$ lsnrctl stop
-- 重启监听
[oracle@ace-test-84 ~]$ lsnrctl reload

14.启停实例

[oracle@ace-test-84 ~]$ export ORACLE_SID=dcpfareint
[oracle@ace-test-84 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 26 16:34:32 2024
Version 19.16.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> 


# 启动数据库
SQL> startup

# 关闭数据库
SQL> shutdown immediate

 

至此，Linux新建实例结束