ORACLE手工创建数据库(sales)

一.准备初始化参数文件

1.进入初始化参数文件路径

cd $ORACLE_HOME/dbs

2.查看路径下文件

3.根据现有的db19c的spfile文件创建pfile文件

create pfile from spfile;

4.创建sales的pfile文件

cp initdb19c.ora initsales.ora

5.编辑sales的pfile文件

vim initsales.ora

db19c.__data_transfer_cache_size=0
db19c.__db_cache_size=520093696
db19c.__inmemory_ext_roarea=0
db19c.__inmemory_ext_rwarea=0
db19c.__java_pool_size=16777216
db19c.__large_pool_size=16777216
db19c.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
db19c.__pga_aggregate_target=587202560
db19c.__sga_target=855638016
db19c.__shared_io_pool_size=50331648
db19c.__shared_pool_size=234881024
db19c.__streams_pool_size=0
db19c.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/db19c/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/DB19C/controlfile/o1_mf_lj3fsb7x_.ctl','/u01/app/oracle/fast_recovery_area/DB19C/controlfile/o1_mf_lj3fsb8c_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='db19c'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db19cXDB)'
*.local_listener='LISTENER_DB19C'
*.memory_target=1369m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

6.将sales文件中的db19c替换成sales

sales.__data_transfer_cache_size=0
:sales.__db_cache_size=520093696
sales.__inmemory_ext_roarea=0
sales.__inmemory_ext_rwarea=0
sales.__java_pool_size=16777216
sales.__large_pool_size=16777216
sales.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
sales.__pga_aggregate_target=587202560
sales.__sga_target=855638016
sales.__shared_io_pool_size=50331648
sales.__shared_pool_size=234881024
sales.__streams_pool_size=0
sales.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/sales/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/sales/control01.ctl','/u01/app/oracle/fast_recovery_area/sales/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='sales'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=salesXDB)'
*.local_listener='LISTENER_SALES'
*.memory_target=1369m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


:1,$s/db19c/sales/g  --全局替换db19c为sales

二.创建initsales.ora文件中相应的目录

mkdir -p /u01/app/oracle/admin/sales/adump
mkdir -p /u01/app/oracle/oradata/SALES
mkdir -p /u01/app/oracle/fast_recovery_area/sales

三.将sales设为当前例程并启动

查看当前例程

echo $ORACLE_SID

设置当前例程

export ORACLE_SID=sales

四.使用nomount模式启动数据库

sqlplus / as sysdba

startup nomount

五.创建表空间与重做日志

表空间：system-系统表空间，sysaux-辅助系统表空间，undo-撤销表空间，temp-临时表空间，users-用户表空间
重做日志：redo

create database sales 
datafile '/u01/app/oracle/oradata/sales/system01.dbf' size 400m 
sysaux datafile '/u01/app/oracle/oradata/sales/sysaux01.dbf' size 400m 
undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/sales/undotbs01.dbf' size 50m 
default tablespace users datafile '/u01/app/oracle/oradata/sales/users01.dbf' size 20m 
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/sales/temp01.dbf' size 20m 
logfile
group 1('/u01/app/oracle/oradata/sales/redo01.log') size 10m,
group 2('/u01/app/oracle/oradata/sales/redo02.log') size 10m,
group 3('/u01/app/oracle/oradata/sales/redo03.log') size 10m;

六.创建数据字典视图

1.查看数据字典位置

cd $ORACLE_HOME/rdbms/admin

ls catalog.sql  数据字典脚本

2.创建数据字典

@?/rdbms/admin/catalog

七.创建口令验证文件

orapwd file=orapwsales password=admin1#3

八.创建spfile

1.查看spfile文件位置

show parameter spfile

2.通过pfile创建spfile文件

create spfile from pfile;

3.重启sales数据库

shutdown   immediate  ---关闭数据库

startup   ---开启数据库

九.创建oracle内部包

@?/rdbms/admin/catproc

十.创建scott方案

1.执行scott方案

@?/rdbms/admin/utlsampl

sqlplus   scott/tiger

select * from emp;

2.查看emp结果集

十一.创建hr方案

1.创建hr方案中需要的文件

mkdir /u01/log

2.创建hr方案

conn  / as sysdba

@?/u01/app/oracle/product/19.3.0/dbhome_1/demo/schema/human_resources/hr_main.sql

3.参数确认

十二.配置监听(服务器端)和服务名(客户端)

打开管理器

netmgr

十三.配置EM Express

desc dbms_xdb_config

select dbms_xdb_config.gethttpsport from dual;

exec dbms_xdb_config.sethttpsport(9110);

https://192.168.11.200:9110/em  默认为JET Oracle EM Express功能有限,只能看到performance
如果要回到以前使用的flash oracle em express执行脚本：
@?/rdbms/admin/execemx emx
再切换会JET Oracle EM Express:
@?/rdbms/admin/execemx omx