手工创建数据库(新建一个实例，在同一个数据库上跑两个实例)

来源：互联网发布：c语言逻辑运算符号编辑：程序博客网时间：2024/04/30 01:14

说明：学习小布老师的oracle教程，动手实践，整理的笔记，以备不时之需。感谢小布老师的精彩视频！

--写在国庆节期间，普天同庆！

1.手工创建数据库(新建一个实例，在同一个数据库上跑两个实例)

   (1).设置好所需的环境变量，实例SID

           --bbk.env文件的内容：
               export ORACLE_SID=bbk
               export ORACLE_BASE=/home/oracle/oracle
               export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
               export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
               export PATH=$ORACLE_HOME/bin:$PATH

           [oracle@localhost dbs]$ . ./bbk.env

   (2).创建密码文件(形如orapwSID)，用操作系统命令orapwd创建

           [oracle@localhost dbs]$ orapwd file=orapwbbk password=oracle entries=10 force=y

   (3).创建参数文件，形如initSID(此处为initbbk.ora)
           --参数文件在目录/home/oracle/oracle/product/10.2.0/db_1/dbs下面
           [oracle@localhost dbs]$ strings spfileorcl.ora > initbbk.ora
           然后再修改initbbk.ora的内容，并准备好所有所需的目录结构：

               oradata/bbk
               db_1/admin/bbk/adump
               db_1/admin/bbk/bdump
               db_1/admin/bbk/cdump
               db_1/admin/bbk/udump

   (4).以sys登录数据库

           [oracle@localhost dbs]$ sqlplus /nolog
           SQL> conn sys/oracle as sysdba
           Connected to an idle instance.

   (5).从pfile创建spfile

           SQL> create spfie from pfile

   (6).以nomount状态启动实例

        SQL> startup nomount

   (7).执行crate database语句

               --手工创建数据库的脚本，详细的参数含义参见SQL Reference
               create database "bbk"
                   MAXDATAFILES 500
                   MAXINSTANCES 8
                   MAXLOGFILES 32
                   CHARACTER SET "UTF8"
                   NATIONAL CHARACTER SET AL16UTF16
                   DATAFILE
                       '/home/oracle/oracle/product/10.2.0/oradata/bbk/system01.dbf' SIZE 300M
                   EXTENT MANAGEMENT LOCAL
                   SYSAUX DATAFILE '/home/oracle/oracle/product/10.2.0/oradata/bbk/sysaux01.dbf' SIZE 300M
                   DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/home/oracle/oracle/product/10.2.0/oradata/bbk/temptbs01.dbf' SIZE 100M
                   UNDO TABLESPACE "UNDOTBS1"
                   DATAFILE '/home/oracle/oracle/product/10.2.0/oradata/bbk/undotbs01.dbf' SIZE 200M
                   LOGFILE
                   GROUP 1(
                   '/home/oracle/oracle/product/10.2.0/oradata/bbk/redo01a.rdo',
                   '/home/oracle/oracle/product/10.2.0/oradata/bbk/redo01b.rdo') SIZE 100M,
                   GROUP 2(
                       '/home/oracle/oracle/product/10.2.0/oradata/bbk/redo02a.rdo',
                      '/home/oracle/oracle/product/10.2.0/oradata/bbk/redo02b.rdo') SIZE 100M,
                  GROUP 3(
                       '/home/oracle/oracle/product/10.2.0/oradata/bbk/redo03a.rdo',
                      '/home/oracle/oracle/product/10.2.0/oradata/bbk/redo03b.rdo') SIZE 100M   ;

   (8).创建额外的表空间

SQL> create tablespace mytbs

datafile '/home/oracle/oracle/product/10.2.0/oradata/bbk/mytbs01.dbf' size 100M

                      extent management local;

   (9).运行相关的脚本创建数据字典视图

           @?rdbms/admin/catalog.sql
           @?rdbms/admin/catproc.sql
           @?rdbms/admin/pupbld.sql

   (10).Run Scripts to install additional option(可选项)

   至此一个新的实例bbk就建立起来了

2.启动实例的过程

   [oracle@localhost dbs]$ pwd
   /home/oracle/oracle/product/10.2.0/db_1/dbs --数据库的启动参数文件放在该目录下面
   [oracle@localhost dbs]$ ll
   总计 568
   -rwxr-xr-x 1 oracle oinstall    212 09-17 13:37 bbk.env
   -rw-r--r-- 1 oracle oinstall     20 09-18 02:06 create_db.log
   -rw-r--r-- 1 oracle oinstall   1073 09-18 02:01 createdb.sql
   -rw-rw---- 1 oracle oinstall   1544 09-18 00:43 hc_bbk.dat
   -rw-rw---- 1 oracle oinstall   1544 09-14 16:33 hc_orcl.dat
   -rw-r--r-- 1 oracle oinstall   1145 09-18 00:41 initbbk.ora
   -rw-r--r-- 1 oracle oinstall     43 09-14 16:41 initorcl01.ora
   -rw-r--r-- 1 oracle oinstall   1202 09-14 16:24 initorcl.ora
   -rw-rw---- 1 oracle oinstall     24 09-18 02:02 lkBBK
   -rw-rw---- 1 oracle oinstall     24 09-14 16:33 lkORCL
   drwxr-xr-x 2 oracle oinstall   4096 09-17 12:29 old
   -rw-r----- 1 oracle oinstall   3584 09-18 03:09 orapwbbk
   -rw-r----- 1 oracle oinstall   1536 09-17 12:52 orapworcl
   -rw-r----- 1 oracle oinstall   3584 09-18 03:36 spfilebbk.ora
   -rw-r----- 1 oracle oinstall   3584 10-03 12:12 spfileorcl.ora
   -rw-r--r-- 1 oracle oinstall 324913 09-18 02:46 sqllog2.log
   -rw-r--r-- 1 oracle oinstall   1374 09-18 02:58 sqllog3.log
   -rw-r--r-- 1 oracle oinstall 167150 09-18 02:31 sqllog.log
   -rw-r--r-- 1 oracle oinstall    745 09-18 03:11 sqlnet.log
   [oracle@localhost dbs]$ cat bbk.env
   export ORACLE_SID=bbk
   export ORACLE_BASE=/home/oracle/oracle
   export ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
   export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
   export PATH=$ORACLE_HOME/bin:$PATH

   --临时改变环境变量为新创建的实例(此处为bbk)的环境变量
   [oracle@localhost dbs]$ . ./bbk.env
   [oracle@localhost dbs]$ env |grep ORA
   ORACLE_SID=bbk
   ORACLE_BASE=/home/oracle/oracle
   ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1

   --再以sys登录启动新的实例
   [oracle@localhost dbs]$ sqlplus /nolog
   SQL> conn sys/oracle as sysdba
   Connected to an idle instance.
   SQL> startup

   --忘记sys的密码可以作如下操作，置新密码，以下将sys密码置为oracle
   [oracle@localhost dbs]$ orapwd file=orapwbbk password=oracle entries=10 force=y

3.监听器的配置

   [oracle@localhost admin]$ cat listener.ora
   # listener.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
   # Generated by Oracle configuration tools.

   SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
          (PROGRAM = extproc)
        )

        (SID_DESC =

          (GLOBAL_DBNAME = orcl)      --实例orcl

          (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)

          (SID_NAME = orcl)

        )
       (SID_DESC =
            (GLOBAL_DBNAME = bbk)     --新创建的实例bbk
            (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
            (SID_NAME = bbk)

            )
      )

   LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = oracle_IPC))
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT = 1521))
        )
      )

4.配置tnsname

   bbk =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = bbk)
       (SID = bbk)
     )

)

这时候就可以访问一个数据库上的两个实例了。