A-A+

手工建库

2014年05月06日 BasicKnowledge 暂无评论 阅读 1,006 次

这一篇介绍如何手工建库的文档,之前在大学那会自己操作过,记得当时好像还在宿舍录了一个手工建库的优酷视频来着,现在重新复习一下!

创建相关目录

[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!正所谓是温故而知新...

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: