笔记：创建具有最优性能的数据库
这里的日志，仅作读书笔记，代码主要摘录自《Oracle database 12c：性能优化攻略》，日志内容为书中知识点与实验在我自己的环境中的实践。
SQL*Plus，手工建库：
在这里，根据书上的实验，我打算手工建库的实例名为：o12c。
1. 手工创建两个路径
|
1 2 3 4 5 |
[root@ogg1 ~]# su - oracle [oracle@ogg1 ~]$ [oracle@ogg1 ~]$ mkdir -p $ORACLE_HOME/oradata/o12c [oracle@ogg1 ~]$ mkdir -p $ORACLE_HOME/admin/o12c/{adump,dpdump,pfile} [oracle@ogg1 ~]$ |
2. 编写初始化参数文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@ogg1 ~]$ cat $ORACLE_HOME/dbs/inito12c.ora db_name='o12c' memory_target=1G processes = 150 audit_file_dest='/u01/app/oracle/admin/o12c/adump' audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest_size=2G diagnostic_dest='/u01/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=o12cXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='UNDOTBS' control_files = ('/u01/app/oracle/oradata/o12c/control01.ora','/u01/app/oracle/oradata/o12c/control02.ora') compatible ='11.2.0' [oracle@ogg1 ~]$ |
3. 以o12c实例启动数据库到nomount阶段：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[oracle@ogg1 dbs]$ env | grep SID ORACLE_SID=o12c [oracle@ogg1 dbs]$ [oracle@ogg1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 6 05:01:28 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> 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 SQL> |
4. 执行创建数据库的命令：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> create database o12c 2 maxlogfiles 16 3 maxlogmembers 4 4 maxdatafiles 1024 5 maxinstances 1 6 maxloghistory 680 7 character set al32utf8 8 datafile '/u01/app/oracle/oradata/o12c/system01.dbf' size 500m reuse extent management local 9 undo tablespace undotbs datafile '/u01/app/oracle/oradata/o12c/undotbs01.dbf' size 800m 10 sysaux datafile '/u01/app/oracle/oradata/o12c/sysaux01.dbf' size 500m 11 default temporary tablespace temp tempfile '/u01/app/oracle/oradata/o12c/temp01.dbf' size 500m 12 default tablespace users datafile '/u01/app/oracle/oradata/o12c/users01.dbf' size 500m 13 logfile 14 group 1 ('/u01/app/oracle/oradata/o12c/redo01a.dbf','/u01/app/oracle/oradata/o12c/redo01b.dbf') size 300m, 15 group 2 ('/u01/app/oracle/oradata/o12c/redo02a.dbf','/u01/app/oracle/oradata/o12c/redo02b.dbf') size 300m, 16 group 3 ('/u01/app/oracle/oradata/o12c/redo03a.dbf','/u01/app/oracle/oradata/o12c/redo03b.dbf') size 300m 17 user sys identified by oracle 18 user system identified by oracle 19 ; Database created. SQL> |
上面的命令执行成功后，数据库实例的状态会自动切换到打开：
|
1 2 3 4 5 6 7 |
SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ o12c OPEN SQL> |
这样，数据库实例，就创建完成了。
你可以去文件系统里查看下存放数据的目录的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[oracle@ogg1 ~]$ ls -ltr $ORACLE_BASE/oradata/o12c total 4235196 -rw-r----- 1 oracle oinstall 524296192 Apr 6 04:49 temp01.dbf -rw-r----- 1 oracle oinstall 314573312 Apr 6 05:03 redo02a.dbf -rw-r----- 1 oracle oinstall 314573312 Apr 6 05:03 redo02b.dbf -rw-r----- 1 oracle oinstall 314573312 Apr 6 05:03 redo03a.dbf -rw-r----- 1 oracle oinstall 314573312 Apr 6 05:03 redo03b.dbf -rw-r----- 1 oracle oinstall 524296192 Apr 6 05:03 system01.dbf -rw-r----- 1 oracle oinstall 524296192 Apr 6 05:03 sysaux01.dbf -rw-r----- 1 oracle oinstall 524296192 Apr 6 05:03 users01.dbf -rw-r----- 1 oracle oinstall 838868992 Apr 6 05:03 undotbs01.dbf -rw-r----- 1 oracle oinstall 314573312 Apr 6 05:03 redo01a.dbf -rw-r----- 1 oracle oinstall 314573312 Apr 6 05:03 redo01b.dbf -rw-r----- 1 oracle oinstall 18268160 Apr 6 05:04 control01.ora -rw-r----- 1 oracle oinstall 18268160 Apr 6 05:04 control02.ora [oracle@ogg1 ~]$ [oracle@ogg1 ~]$ du -sh $ORACLE_BASE/oradata/o12c 4.1G /u01/app/oracle/oradata/o12c [oracle@ogg1 ~]$ du -sh $ORACLE_BASE/oradata/o12c/* 18M /u01/app/oracle/oradata/o12c/control01.ora 18M /u01/app/oracle/oradata/o12c/control02.ora 301M /u01/app/oracle/oradata/o12c/redo01a.dbf 301M /u01/app/oracle/oradata/o12c/redo01b.dbf 301M /u01/app/oracle/oradata/o12c/redo02a.dbf 301M /u01/app/oracle/oradata/o12c/redo02b.dbf 301M /u01/app/oracle/oradata/o12c/redo03a.dbf 301M /u01/app/oracle/oradata/o12c/redo03b.dbf 501M /u01/app/oracle/oradata/o12c/sysaux01.dbf 501M /u01/app/oracle/oradata/o12c/system01.dbf 1.0M /u01/app/oracle/oradata/o12c/temp01.dbf 801M /u01/app/oracle/oradata/o12c/undotbs01.dbf 501M /u01/app/oracle/oradata/o12c/users01.dbf [oracle@ogg1 ~]$ |
这时候的数据库实例，是没有数据字典表的。
如果你需要它们，你可以在SQL*Plus中，执行以下脚本：
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catexp.sql
——————————————————————————
Done。
