aix下新建实例和手动创建数据库

/* Edit in BLUE characters by Kurt Z, 200507xx */
在已有的系统环境中建立test数据库。
具体操作如下：
1. 查看当前机器的物理卷环境:用命令”lspv”查看.

2 . #smit mkvg
Volume Group name: oradata
Physical Volume Name : hdisk1
Force the creation of a Volume group:yes

3 . #smit ->;
System Storage Management ->;
Logic Volume Managerment ->;
Volume Group ->;
Set Characteristics of a Volume Group ->;
Add a Physical Volume to a Volume Group:
Force the creation of a VG: Yes
Volume Group Name: oradata
Physical Volume Name: hdisk2,3


4.用命令“lspv”检验hdisk1, hdisk2, hdisk3 都在同一个卷组oradata.

5 . #smit ->;
System Storage Management ->;
Logic Volume Manager ->;
Logic Volume ->;
Add Logic Volume ->;
(select oradata)
Logic Volume Name: oradata1~3
Number of LP: Q值
/**Q值的计算公式: PP/（需要的空间）G=Q值,通过命令 “lsvg oradata”来查看PP值.*/

Physical Volume Name: hdisk1~3
Logic Volume type: JFS
以上步骤之后,运行的命令”lsvg –l oradata”检验之.

6 . #mkdir /oradisk1~3 ( 在根目录创建三个文件,oradisk1,2,3)
#chown oracle /oradisk* (改变文件的所有者)
#chgrp dba /oradisk* （改变文件所属的群组）
或用命令”chown oracle:dba /oradisk*”.

7 . #smit ->;
System Storage Management ->;
File System ->;
Add/Change/Watch/Delete FS ->;
JFS ->;
add a JFS on a defined LV ->;
add a larger file JFS
LV Name: oradisk1~3
Auto mount when reboot: Yes

8 . 用命令”reboot或shutdown -Fr”重启机器 /**测试系统是否能自动
mount */
9 . #df –g
上面的命令检验每个逻辑卷容量是否都是64G

10 .用ORACLE用户登陆AIX,用编辑软件对 /home/oracle/.profile 文件进行编辑:在”DISPLAY …这行前加#号"

11. 运行$dbca, 在第六步时注意.要改变参数(如下)
Step 6 of 8: charsets: UTF8
DBSizing: 32KB Blocksize
SortAreaSize: 2M
Step 7 of 8: Data files path: /oradiskd1/test/..
Redolog groups: /oradisk1~3/test/..
Control files: /oradisk1~3/test/
/* 如果数据量比较大, 可以在hdisk0再加一个控制文件 */

然后建库,如果弹出提示"找不到 /etc/oratab", 不用管它, 继续. 完成以后, 按照提示修改 sys和system用户的密码都为oracle.
12. $lsnrctl start
如果提示正确启动, 然后找一台Windows机器配个数据连接到小型机, 然后tnsping [oracle_sid], 要返回正确信息.
13以oracle用户登录AIX, 然后
$sqlplus “/ as sysdba”
SQL>;connect system/manager as sysdba
SQL>;startup
然后一步一步的建立数据库表空间与数据文件.

14. 建立test数据库以后,根据实际情况手动增加临时表空间和回滚段空间和其他的表空间. 数据文件在必须平均分配分布在oradisk1~3中.

如果是OLTP型数据库建议所有的表空间都不设置为自动扩展. 然后运行 Oracle Enterprise Console ->; database ->; Storage ->; data files
监视数据文件和表空间的增长情况.


15.用编辑软件对/etc/oratab文件进行编辑.在最后一行添加下面句子.
“test:/oracle/app/oracle/product/920:Y”
编辑完后对文件的所属进行改变,用以下命令改变.
#chown oracle /etc/oratab
#chgrg dba /etc/oratab
16.修改oratab文件后,对/etc/inittab文件进行编辑,在文件最后添加(如下)自启动语句
oracle:2:once:/bin/su - oracle -c /$ORACLE_HOME/bin/lsnrctl start
oracle:2:wait:/bin/su - oracle -c /$ORACLE_HOME/bin/dbstart

下面还有一步最重要的步骤,对小”鸡”进行优化.
SQLPLUS 进入数据库,运行下面SQL语句.
SQL>;CREATE PFILE FROM SPFILE;
SQL>;EXIT
进入$ORACLE_HOME/dbs下将spfiletest.ora文件改名为spfiletest.ora.rig
然后对../dbs目录下的init[SID].ora文件进行编辑(主要是对文件中的几个参数按照小机的实际硬件配置进行改动.)
db_cache_size=”????M”
large_pool_size=”?M”
pga_aggregate_target=?M
shared_pool_size=?M
以上SGA参数可以在进入 sqlplus以后,用
SQL>; show parameter SGA
另外，可以在PL/SQL Developer等工具里面用SQL语句查看：
select * from v$datafile; 看数据文件
select * from v$controlfile; 看控制文件路径
select nls_characterset from v$nls_parameters; 看数据库字符集
select * from v$database; 看数据库实例等相关信息
select * from v$session; 看当前连接信息
select * from v$dbfile; 看数据库文件路径

CODE:

**********************************
字符下新建数据库
1. 创建相关目录：
$mkdir /oracle/app/oracle/admin/test
$mkdir /oracle/app/oracle/admin/test/bdump
$mkdir /oracle/app/oracle/admin/test/cdump
$mkdir /oracle/app/oracle/admin/test/create
$mkdir /oracle/app/oracle/admin/test/pfile
$mkdir /oracle/app/oracle/admin/test/udump
$mkdir /oracle/app/oracle/oradata/test
$mkdir /oracle/app/oracle/product/920/dbs
$mkdir /oracle/test

在创建相关目录后，用命令ls -la查看文件属性是否为oracle用户

2. 设置系统变量和修改系统文件
$setenv ORACLE_SID test
$echo Add this entry in the oratab:test:orace/app/oracle/product/920:Y

3. 通过orapwd.exe命令，建立口令文件。
$/oracle/app/oracle/product/920/bin/orapwd file=/oracle/app/oracle/product/920/dbs/orapwdtest.ora password=change_on_install
4.创建数据库
$sqlplus "/ as sysbda"
SQL>;connect sys/change_on_install as sysdba
SQL>;set echo on
SQL>;spool /oracle/app/oracle/product/920/assistants/dbca/logs/createDB.log
SQL>;startup nomount pfile="/oracle/app/oracle/admin/test/scripts/init.ora";
SQL>;create database test
2 maxinstances 1
3 maxloghistory 1
4 maxlogfiles 5
5 maxlogmembers 3
6 maxdatafiles 100
7 datafile "/oracle/test/system01.dbf" size 250M reuse autoextend on next 10240k maxsize unlimited
8 extent management local
9 default temporary tablespace temp tempfile "/oracle/test/temp01.dbf" size 40M reuse autoextend on next 640k maxsize unlimited
10 undo tablespace "undotbs1" datafile "oracel/test/undotbs01.dbf" size 200M reuse autoextend on next 5120k maxsize unlimited
11 character set UTF8
12 national character set AL16UTF16
13 logfile group 1 ("oracel/test/redo01.log") size 102400k,
14 group 2 ("oracel/test/redo02.log") size 102400k,
15 group 3 ("oracel/test/redo03.log") size 102400k;
SQL>;spool off
SQL>;exit;
5.创建系统表空间和数据文件
connect SYS/change_on_install as SYSDBA
set echo on
spool /oracle/app/oracle/product/920/assistants/dbca/logs/CreateDBFiles.log
CREATE TABLESPACE "INDX" LOGGING DATAFILE '/oracle/hz2004/indx01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/oracle/hz2004/tools01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "USERS" LOGGING DATAFILE '/oracle/hz2004/users01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
spool off
exit;

6.创建数据字典
$sqlplus "/ as sysbda"
SQL>;sqlplus "/ as sysbda"connect SYS/change_on_install as SYSDBA
SQL>;set echo on
SQL>;spool /oracle/app/oracle/product/920/assistants/dbca/logs/CreateDBCatalog.log
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/catalog.sql;
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/catexp7.sql;
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/catblock.sql;
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/catproc.sql;
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/catoctk.sql;
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/owminst.plb;
SQL>;connect SYSTEM/manager
SQL>;@/oracle/app/oracle/product/920/sqlplus/admin/pupbld.sql;
SQL>;connect SYSTEM/manager
SQL>;set echo on
SQL>;spool /oracle/app/oracle/product/920/assistants/dbca/logs/sqlPlusHelp.log
SQL>;@/oracle/app/oracle/product/920/sqlplus/admin/help/hlpbld.sql helpus.sql;
SQL>;spool off
SQL>;spool off
SQL>;exit;

7．POSTDBCreation
$sqlplus "/ as sysbda"
SQL>;connect SYS/change_on_install as SYSDBA
SQL>;set echo on
SQL>;spool /oracle/app/oracle/product/920/assistants/dbca/logs/postDBCreation.log
SQL>;@/oracle/app/oracle/product/920/rdbms/admin/utlrp.sql;
SQL>;shutdown ;
SQL>;connect SYS/change_on_install as SYSDBA
SQL>;set echo on
SQL>;spool /oracle/app/oracle/product/920/assistants/dbca/logs/postDBCreation.log
SQL>;create spfile='/oracle/app/oracle/product/920/dbs/spfilehz2004.ora' FROM pfile='/oracle/app/oracle/admin/hz2004/scripts/init.ora';
SQL>;startup ;