假设数据库软件已经安装好，现在没有图形界面无法用dbca安装数据库，那么用手工建库，数据库名为edw
创建目录
[oracle@localhost ~]$ mkdir -p /u01/app/oracle/admin/edw/adump [oracle@localhost ~]$ mkdir -p /u01/app/oracle/oradata/edw [oracle@localhost ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
创建密码文件
[oracle@localhost ~]$ dbs [oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ orapwd file=orapwedw password=oracle entries=30
创建参数文件pfile
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ cat init.ora | grep -v ^# | grep -v ^$ > initedw.ora
然后vim做一些替换操作
:%s/ORCL/edw/g :%s/orcl/edw/g :%s##$ORACLE_BASE#g :%s#ora_control1#/u01/app/oracle/oradata/edw/ora_control1.ctl#g :%s#ora_control2#/u01/app/oracle/oradata/edw/ora_control2.ctl#g
得到的pfile如下
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ cat initedw.ora db_name=‘edw‘ memory_target=1G processes = 150 audit_file_dest=‘$ORACLE_BASE/admin/edw/adump‘ audit_trail =‘db‘ db_block_size=8192 db_domain=‘‘ db_recovery_file_dest=‘$ORACLE_BASE/flash_recovery_area‘ db_recovery_file_dest_size=2G diagnostic_dest=‘$ORACLE_BASE‘ dispatchers=‘(PROTOCOL=TCP) (SERVICE=edwXDB)‘ open_cursors=300 remote_login_passwordfile=‘EXCLUSIVE‘ undo_tablespace=‘UNDOTBS1‘ control_files = (/u01/app/oracle/oradata/edw/ora_control1.ctl, /u01/app/oracle/oradata/edw/ora_control2.ctl) compatible =‘11.2.0‘ [oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$
启动到nomount根据pfile生成spfile
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ export ORACLE_SID=edw [oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ sql SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:49:38 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. 16:49:38 SYS@edw> startup nomount ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 671089544 bytes Database Buffers 390070272 bytes Redo Buffers 5517312 bytes 16:49:47 SYS@edw> create spfile from pfile; File created. Elapsed: 00:00:00.01 16:50:01 SYS@edw>
创建数据库
从官方文档Administrator‘s Guide –> 2 Creating and Configuring an Oracle Database –> Creating a Database with the CREATE DATABASE Statement –> Step 9: Issue the CREATE DATABASE Statement 复制建库语句然后做些vim替换操作
:%s/mynewdb/edw/g :%s/sys_password/oracle/g :%s/system_password/oracle/g :%s#/u01/logs/my/#/u01/app/oracle/oradata/edw/#g :%s#/u02/logs/my/#/u01/app/oracle/oradata/edw/#g :%s/US7ASCII/AL32UTF8/g :%s/undotbs/undotbs1/g
得到的建库语句是
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ cat crtdb.sql
CREATE DATABASE edw
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/edw/redo01a.log‘,‘/u01/app/oracle/oradata/edw/redo01b.log‘) SIZE 100M BLOCKSIZE 512,
GROUP 2 (‘/u01/app/oracle/oradata/edw/redo02a.log‘,‘/u01/app/oracle/oradata/edw/redo02b.log‘) SIZE 100M BLOCKSIZE 512,
GROUP 3 (‘/u01/app/oracle/oradata/edw/redo03a.log‘,‘/u01/app/oracle/oradata/edw/redo03b.log‘) SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE ‘/u01/app/oracle/oradata/edw/system01.dbf‘ SIZE 325M REUSE
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/edw/sysaux01.dbf‘ SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE ‘/u01/app/oracle/oradata/edw/users01.dbf‘
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/u01/app/oracle/oradata/edw/temp01.dbf‘
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE ‘/u01/app/oracle/oradata/edw/undotbs101.dbf‘
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$
进入数据库执行
16:50:01 SYS@edw> @crtdb.sql Database created. Elapsed: 00:00:32.12 17:10:20 SYS@edw>
建数据字典
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ cat crtdic.sql @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql conn system/oracle @?/sqlplus/admin/pupbld.sql exit [oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ sql SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 17:12:10 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 17:12:10 SYS@study> @crtdic.sql (many more lines suppressed) Synonym created. Elapsed: 00:00:00.01 17:20:52 SYSTEM@study> DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE; Synonym dropped. Elapsed: 00:00:00.00 17:20:52 SYSTEM@study> CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS; Synonym created. Elapsed: 00:00:00.00 17:20:52 SYSTEM@study> 17:20:52 SYSTEM@study> -- End of pupbld.sql 17:20:52 SYSTEM@study> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$
验证下
[oracle@localhost /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs]$ sql SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 17:23:18 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 17:23:18 SYS@study> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production Elapsed: 00:00:00.00 17:23:32 SYS@study>
ps:
手工创建scott方案
@?/rdbms/admin/utlsampl.sql
alter user scott account unlock identified by tiger;
ps2:
手工删库