A-A+
手工建库
这一篇介绍如何手工建库的文档，之前在大学那会自己操作过，记得当时好像还在宿舍录了一个手工建库的优酷视频来着，现在重新复习一下！
创建相关目录
[oracle@ocm1 oracle]$ pwd /u01/app/oracle [oracle@ocm1 oracle]$ mkdir -p test/bdump [oracle@ocm1 oracle]$ mkdir -p test/cdump [oracle@ocm1 oracle]$ mkdir -p test/udump
创建参数文件
[oracle@ocm1 ~]$ export ORACLE_SID=test [oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs [oracle@ocm1 dbs]$ cat init.ora |grep -v ^#| grep -v ^$ > inittest.ora [oracle@ocm1 dbs]$ cat inittest.ora db_name=test db_files = 180 # SMALL db_file_multiblock_read_count = 8 # SMALL #db_block_buffers = 100 # SMALL #shared_pool_size = 3500000 # SMALL log_checkpoint_interval = 10000 processes = 150 # SMALL parallel_max_servers = 5 # SMALL #log_buffer = 32768 # SMALL max_dump_file_size = 10240 # limit trace file size to 5 Meg each global_names = TRUE #begin add parameters sga_max_size = 300M sga_target = 300M undo_management = auto undo_tablespace= undotbs background_dump_dest=/u01/app/oracle/test/bdump core_dump_dest=/u01/app/oracle/test/cdump user_dump_dest=/u01/app/oracle/test/udump control_files = (/u01/app/oracle/test/control1,/u01/app/oracle/test/control2)
注意：该处需要人为将所有内存参数注销掉，同一由sga_max_size和sga_target2个参数替代管理；需要修改数据库名db_name；需要调大参数值db_files,processes;增加参数undo_tablespace,undo_management;修改控制文件路径control_files;添加日志目录的路径background_dump_dest,core_dump_dest,user_dump_dest.
修改OS参数/etc/oratab
[oracle@ocm1 ~]$ tail -1 /etc/oratab test:/u01/app/oracle/product/10.2.0/db_1:N
生成spfile
[oracle@ocm1 dbs]$ env| grep ORACLE_SID ORACLE_SID=test [oracle@ocm1 dbs]$ sqlplus "/as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 5 08:54:55 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/inittest.ora'; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 96470416 bytes Database Buffers 213909504 bytes Redo Buffers 2973696 bytes SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/inittest.ora'; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 96470416 bytes Database Buffers 213909504 bytes Redo Buffers 2973696 bytes SQL>
创建数据库
SQL> CREATE DATABASE test
2 USER SYS IDENTIFIED BY oracle
3 USER SYSTEM IDENTIFIED BY oracle
4 LOGFILE GROUP 1 ('/u01/app/oracle/test/redo01.log') SIZE 100M,
5 GROUP 2 ('/u01/app/oracle/test/redo02.log') SIZE 100M,
6 GROUP 3 ('/u01/app/oracle/test/redo03.log') SIZE 100M
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 1
10 MAXDATAFILES 100
11 MAXINSTANCES 1
12 CHARACTER SET US7ASCII
13 NATIONAL CHARACTER SET AL16UTF16
14 DATAFILE '/u01/app/oracle/test/system01.dbf' SIZE 325M REUSE
15 EXTENT MANAGEMENT LOCAL
16 SYSAUX DATAFILE '/u01/app/oracle/test/sysaux01.dbf' SIZE 325M REUSE
17 DEFAULT TEMPORARY TABLESPACE tempts1
18 TEMPFILE '/u01/app/oracle/test/temp01.dbf'
19 SIZE 20M REUSE
20 UNDO TABLESPACE undotbs
21 DATAFILE '/u01/app/oracle/test/undotbs01.dbf'
22 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
注意：数据库名和oracle_sid一致；sys,system密码；redo路径；数据文件路径；字符集设定；默认表空间设置。
跑数据字典脚本和PL/SQL的脚本
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
创建密码文件
[oracle@ocm1 ~]$ cd $ORACLE_HOME/dbs/
[oracle@ocm1 dbs]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.
[oracle@ocm1 dbs]$ orapwd file=orapwtest password=oracle entries=10
检查数据库
SQL> desc dba_registry Name Null? Type ----------------------------------------- -------- ---------------------------- COMP_ID NOT NULL VARCHAR2(30) COMP_NAME VARCHAR2(255) VERSION VARCHAR2(30) STATUS VARCHAR2(11) MODIFIED VARCHAR2(20) NAMESPACE NOT NULL VARCHAR2(30) CONTROL NOT NULL VARCHAR2(30) SCHEMA NOT NULL VARCHAR2(30) PROCEDURE VARCHAR2(61) STARTUP VARCHAR2(8) PARENT_ID VARCHAR2(30) OTHER_SCHEMAS VARCHAR2(4000) SQL> set line 400 SQL> col COMP_NAME for a40 SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ------------------------------ ---------------------------------------- ------------------------------ ----------- CATALOG Oracle Database Catalog Views 10.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 10.2.0.1.0 VALID 2 rows selected.
进行冷备份
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@ocm1 dbs]$ cp -r /u01/app/oracle/test /tmp/ [oracle@ocm1 dbs]$ cp $ORACLE_HOME/dbs/*test* /tmp
至此全部OK！正所谓是温故而知新...
