Oracle 11g 手工建库

2022-01-28 15:16:25

假设数据库软件已经安装好，现在没有图形界面无法用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:

手工删库

https://www.cnblogs.com/kerrycode/p/5447757.html

码农公寓