精华内容
下载资源
问答
  • 想了解Oracle手动建库安装部署超详细教程的相关内容吗,东瑜在本文为您仔细讲解Oracle手动建库的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:Oracle手动建库,Oracle安装部署,下面大家一起来学习吧。...

    想了解Oracle手动建库安装部署超详细教程的相关内容吗,东瑜在本文为您仔细讲解Oracle手动建库的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:Oracle手动建库,Oracle安装部署,下面大家一起来学习吧。

    手动创建PROD数据库

    步骤1 指定SID

    Oracle_SID是用于区别数据库实例的环境变量。

    ORACLE_SID=prod

    export ORACLE_SID

    步骤2 确定Oracle环境的环境变量

    在操作系统平台下,Oracle_SID和ORACLE_HOME必须指定,建议指定PATH包含ORACLE_HOME/bin目录。环境变量的实际操作如下:

    创建数据目录和审计目录:

    [oracle@db1 oradata]$ mkdir -p /u01/app/oradata/prod

    [oracle@db1 oradata]$ mkdir -p $ORACLE_BASE/admin/prod/adump

    [oracle@db1 dbs]$ mkdir -p /u01/app/flash_recovery_area

    环境变量配置:

    ORACLE_BASE=/u01/app

    ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

    #ORACLE_SID=oradb

    ORACLE_SID=prod

    export ORACLE_BASE ORACLE_HOME ORACLE_SID

    PATH=$ORACLE_HOME/bin:$PATH

    export PATH

    alias sqlplus='rlwrap sqlplus'

    alias rman='rlwrap rman'

    即时生效,验证ORACLE_SID是否是prod

    [oracle@db1 ~]$ source .bash_profile

    [oracle@db1 ~]$ echo $ORACLE_SID

    prod

    可选操作,部署glogin.sql和rlwrap

    $ORACLE_HOME/sqlplus/admin/glogin.sql

    set sqlprompt _user'@'_connect_identifier>

    rlwarp的配置,参考:rlwarp的使用

    步骤3 设置Oracle的口令认证方式

    创建Oracle的口令文件

    [oracle@db1 ~]$ cd $ORACLE_HOME/dbs

    [oracle@db1 dbs]$ orapwd file=orapwPROD password=oracle entries=30

    步骤4 创建初始化参数文件

    官方文档的参考文件链接为:Oracle参数文件的例子

    cd $ORACLE_HOME/dbs

    官方的参数文件init.ora例子的具体内容如下:

    db_name='ORCL'

    memory_target=1G

    processes = 150

    audit_file_dest='/admin/orcl/adump'

    audit_trail ='db'

    db_block_size=8192

    db_domain=''

    db_recovery_file_dest='/flash_recovery_area'

    db_recovery_file_dest_size=2G

    diagnostic_dest=''

    dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

    open_cursors=300

    remote_login_passwordfile='EXCLUSIVE'

    undo_tablespace='UNDOTBS1'

    # You may want to ensure that control files are created on separate physical

    # devices

    control_files = (ora_control1, ora_control2)

    compatible ='11.2.0'

    通过vi命令中的

    :%s#orcl#prod#gi替换里面的orcl的字符串。其中i命令不区分大小写。

    :%s#ora_control1#/u01/app/oradata/prod/control01.ctl#g替换ora_control1的字符串。

    :%s#ora_control2#/u01/app/oradata/prod/control02.ctl#g替换ora_control2的字符串。

    :%s##/u01/app#g替换的字符串。

    :wq命令保存配置。

    修改后,我个人的参数文件initprod.ora如下:

    db_name='prod'

    memory_target=1G

    processes = 150

    audit_file_dest='/u01/app/admin/prod/adump'

    audit_trail ='db'

    db_block_size=8192

    db_domain=''

    db_recovery_file_dest='/u01/app/flash_recovery_area'

    db_recovery_file_dest_size=2G

    diagnostic_dest='/u01/app'

    dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

    open_cursors=300

    remote_login_passwordfile='EXCLUSIVE'

    undo_tablespace='UNDOTBS1'

    # You may want to ensure that control files are created on separate physical

    # devices

    control_files = (/u01/app/oradata/prod/control01.ctl, /u01/app/oradata/prod/control02.ctl)

    compatible ='11.2.0'

    步骤5 连接数据库实例

    有命令文件,可以通过如下命令连接:

    [oracle@db1 dbs]$ sqlplus /nolog

    @> connect sys as sysdba

    Enter password:

    Connected to an idle instance.

    SYS@prod>

    OS认证登录方式,命令如下:

    [oracle@db1 dbs]$ sqlplus /nolog

    @> conn /as sysdba

    Connected to an idle instance.

    SYS@prod>

    步骤6 创建spfile文件

    SYS@prod> CREATE SPFILE FROM PFILE;

    步骤7 启动实例

    STARTUP NOMOUNT

    步骤8 使用CREATE DATABASE语句建库

    官方给与建库的例句,你可以修改你自己所需的数据库的样子,如下:

    前提:有数据库目录:/u01/app/oradata/prod

    CREATE DATABASE prod

    USER SYS IDENTIFIED BY oracle

    USER SYSTEM IDENTIFIED BY oracle

    LOGFILE GROUP 1 ('/u01/app/oradata/prod/redo01a.log','/u01/app/oradata/prod/redo01b.log') SIZE 100M BLOCKSIZE 512,

    GROUP 2 ('/u01/app/oradata/prod/redo02a.log','/u01/app/oradata/prod/redo02b.log') SIZE 100M BLOCKSIZE 512,

    GROUP 3 ('/u01/app/oradata/prod/redo03a.log','/u01/app/oradata/prod/redo03b.log') SIZE 100M BLOCKSIZE 512

    MAXLOGFILES 5

    MAXLOGMEMBERS 5

    MAXLOGHISTORY 1

    MAXDATAFILES 100

    CHARACTER SET AL32UTF8

    NATIONAL CHARACTER SET AL16UTF16

    EXTENT MANAGEMENT LOCAL

    DATAFILE '/u01/app/oradata/prod/system01.dbf' SIZE 325M REUSE

    SYSAUX DATAFILE '/u01/app/oradata/prod/sysaux01.dbf' SIZE 325M REUSE

    DEFAULT TABLESPACE users

    DATAFILE '/u01/app/oradata/prod/users01.dbf'

    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

    DEFAULT TEMPORARY TABLESPACE tempts1

    TEMPFILE '/u01/app/oradata/prod/temp01.dbf'

    SIZE 20M REUSE

    UNDO TABLESPACE undotbs1

    DATAFILE '/u01/app/oradata/prod/undotbs01.dbf'

    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    执行语句如下:

    [oracle@db1 ~]$ chmod 755 create_database.sql

    [oracle@db1 ~]$ sqlplus "/as sysdba"

    SYS@prod> @create_database.sql

    Database created.

    步骤9 创建额外的空间

    对于应用程序来说,应该有属于自己的表空间,对于我们这边的都是以ARP命名,如下:

    CREATE TABLESPACE apps_arp LOGGING

    DATAFILE '/u01/app/oradata/prod/apps_arp01.dbf'

    SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

    EXTENT MANAGEMENT LOCAL;

    -- create a tablespace for indexes, separate from user tablespace (optional)

    CREATE TABLESPACE indx_arp LOGGING

    DATAFILE '/u01/app/oradata/prod/indx_arp01.dbf'

    SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

    EXTENT MANAGEMENT LOCAL;

    步骤10 执行脚本构建数据字典视图

    使用SYSDBA权限的用户,连接数据库如下:

    [oracle@db1 ~]$ sqlplus /nolog

    @> conn /as sysdba

    Connected.

    SYS@prod>

    执行如下语句:

    @?/rdbms/admin/catalog.sql

    @?/rdbms/admin/catproc.sql

    @?/rdbms/admin/utlrp.sql

    使用SYSTEM用户,执行如下语句:

    [oracle@db1 ~]$ sqlplus /nolog

    @> conn system/oracle

    Connected.

    SYSTEM@prod> @?/sqlplus/admin/pupbld.sql

    【可选操作】,如果需要scott用户用于数据库的实验,可以做如下操作:

    [oracle@db1 ~]$ sqlplus /nolog

    @> conn / as sysdba

    Connected.

    SYS@prod> @?/rdbms/admin/utlsampl.sql

    验证数据库,是否正常,如下:

    [oracle@db1 ~]$ sqlplus "/as sysdba"

    SYS@prod> select INSTANCE_NAME,STATUS from v$instance;

    INSTANCE_NAME STATUS

    ---------------- ------------

    prod OPEN

    参考文献

    官方文档:

    相关文章

    展开全文
  • oracle手工建库

    2016-09-20 19:56:46
    oracle 手工建库
  • 1、创建目录$ORACLE_BASE/admin/$ORACLE_BASE/oradata$ORACLE_BASE/flash_recovery_area2、建初始化参数文件cd $ORACLE_HOME/dbsdb_name=orclmemory_target=1Gprocesses = 150audit_file_dest=‘/oracle/app/product...

    5268f80b9b1e01f982625ef6fac83ca1.png

    1、创建目录

    $ORACLE_BASE/admin/

    $ORACLE_BASE/oradata

    $ORACLE_BASE/flash_recovery_area

    2、建初始化参数文件

    cd   $ORACLE_HOME/dbs

    db_name=orcl

    memory_target=1G

    processes = 150

    audit_file_dest=‘/oracle/app/product/admin/orcl/adump‘

    audit_trail =‘db‘

    db_block_size=8192

    db_domain=‘‘

    db_recovery_file_dest=‘/oracle/app/product/flash_recovery_area‘

    db_recovery_file_dest_size=2G

    diagnostic_dest=‘/oracle/app/product‘

    dispatchers=‘(PROTOCOL=TCP) (SERVICE=ORCLXDB)‘

    open_cursors=300

    remote_login_passwordfile=‘EXCLUSIVE‘

    undo_tablespace=‘UNDOTBS1‘

    control_files = (/oracle/app/product/oradata/ora_control1,/oracle/app/product/oradata/ora_control2,/oracle/app/product/oradata/ora_control3)

    compatible =‘11.2.0‘

    undo_management=AUTO

    3、创建密码文件

    orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle force=y

    4、建库

    a、创建spfile

    $ sqlplus ‘/as sysdba‘

    SQL> create spfile from pfile; (会自动读取pfile:$ORACLE_HOME/dbs/init$ORACLE_SID.ora)

    b、启动到nomount状态创数据库------期间自动创建控制文件

    SQL> startup nomount;

    SQL> @/home/oracle/createdb.sql-------------------创建数据库

    创看脚本

    create database orcl CONTROLFILE REUSE

    MAXINSTANCES 8 MAXDATAFILES 2000

    MAXLOGHISTORY 2 MAXLOGFILES 64 MAXLOGMEMBERS 5

    character set ZHS16GBK national character set AL16UTF16

    logfile group 1 (‘/oracle/app/product/oradata/orcl/redo1.log‘) size 50M reuse,

    group 2 (‘/oracle/app/product/oradata/orcl/redo2.log‘) size 50M reuse,

    group 3 (‘/oracle/app/product/oradata/orcl/redo3.log‘) size 50M reuse

    datafile ‘/oracle/app/product/oradata/orcl/system.dbf‘ size 1024M reuse autoextend on next 100M maxsize unlimited extent management local

    sysaux datafile ‘/oracle/app/product/oradata/orcl/sysaux.dbf‘ size 1024M autoextend on next 100M maxsize unlimited

    default temporary tablespace temp tempfile ‘/oracle/app/product/oradata/orcl/temp01.dbf‘ size 100M reuse autoextend on next 10M maxsize unlimited

    undo tablespace undotbs1 datafile ‘/oracle/app/product/oradata/orcl/undotbs1.dbf‘ size 100M reuse autoextend on next 10M maxsize unlimited

    USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle";

    c、运行数据字典脚本,其中catalog和catproc是必需的,其它可选: (运行时间比较长)

    SQL> spool /opt/oradata/cat_testdb.log

    SQL> @?/rdbms/admin/catalog.sql----------------(建数据字典视图)-----------------------必须

    SQL> @?/rdbms/admin/catproc.sql-----------(建存储过程包)-----------------------------------必须

    SQL> @?/rdbms/admin/catblock.sql -----------(建锁相关的几个视图)

    SQL> @?/rdbms/admin/catoctk.sql ----------(建密码工具包dbms_crypto_toolkit)

    SQL> @?/rdbms/admin/owminst.plb---------------(建工作空间管理相关对象,如dmbs_wm)

    SQL> spool off

    d、 新建sqlplus属性和帮助、USERS表空间

    SQL> connect system/oracle

    SQL> @?/sqlplus/admin/pupbld.sql

    SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql

    SQL> connect /as sysdba

    SQL> CREATE TABLESPACE USERS LOGGING DATAFILE ‘/oracle/app/product/oradata/orcl/users01.dbf‘ SIZE 1024M REUSE AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

    SQL> ALTER DATABASE DEFAULT TABLESPACE USERS;

    SQL> execute utl_recomp.recomp_serial();

    配置网络listener  sqlnet.ora  tnsname.ora

    建立和配置EM(省略此步骤):

    SQL> @?/sysman/admin/emdrep/sql/emreposcre /oracle/product/10.2 SYSMAN oracle TEMP ON;

    SQL> alter user SYSMAN identified by "sysman" account unlock;

    SQL> alter user DBSNMP identified by "dbsnmp" account unlock;

    SQL> host emca -config dbcontrol db -silent -DB_UNIQUE_NAME ypyhtb -PORT 1521 -EM_HOME /oracle/product/10.2/bin -LISTENER LISTENER -SERVICE_NAME ypythb.LK -SYS_PWD "oracle" -SID ypythb -ORACLE_HOME /oracle/product/10.2 -DBSNMP_PWD "dbsnmp" -HOST "172.19.201.184" -LISTENER_OH /oracle/product/10.2 -LOG_FILE /oradata/ypythb/emConfig.log -SYSMAN_PWD "sysman";

    本文出自 “11300506” 博客,请务必保留此出处http://11310506.blog.51cto.com/11300506/1961543

    linux下oracle手工建库过程

    标签:linux下oracle手工建库过程

    1428d0e076c3959ab11d28a39bc84fab.png

    5268f80b9b1e01f982625ef6fac83ca1.png

    本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉

    本文系统来源:http://11310506.blog.51cto.com/11300506/1961543

    展开全文
  • BLOG_Oracle手动建库.pdf

    2018-08-02 17:55:30
    BLOG_Oracle手动建库.pdfBLOG_Oracle手动建库.pdfBLOG_Oracle手动建库.pdfBLOG_Oracle手动建库.pdf
  • Oracle手工建库教程发布时间:2020-05-23 11:45:00来源:亿速云阅读:156作者:鸽子系统环境:操作系统: RedHat EL6OracleOracle 10g and Oracle 11g手工建库相对来说很容易实现,本案例是从10g和11g,通过手工...

    Oracle手工建库教程

    发布时间:2020-05-23 11:45:00

    来源:亿速云

    阅读:156

    作者:鸽子

    系统环境:

    操作系统: RedHat EL6

    Oracle:  Oracle 10g and Oracle 11g

    手工建库相对来说很容易实现,本案例是从10g和11g,通过手工建库的方式做一个简单的对比,可以看出11g和10g之间的一个简单的差异!

    案例一:

    在Oracle 10g 环境下手工建库

    DB_NAME='cuug'

    INSTANCE_NAME='cuug'

    1、建立Instance的初始化参数文件和口令文件:

    [oracle@oracle dbs]$ cat initcuug.ora

    db_name = cuug

    db_block_size = 8192

    pga_aggregate_target = 30M

    db_cache_size = 80M

    shared_pool_size = 60M

    parallel_threads_per_cpu = 4

    optimizer_mode = choose

    star_transformation_enabled = true

    db_file_multiblock_read_count = 16

    query_rewrite_enabled = true

    query_rewrite_integrity = trusted

    background_dump_dest = $ORACLE_BASE/admin/cuug/bdump

    user_dump_dest = $ORACLE_BASE/admin/cuug/udump

    core_dump_dest = $ORACLE_BASE/admin/cuug/cdump

    control_files = $ORACLE_BASE/oradata/cuug/control01.ctl

    undo_management = auto

    undo_tablespace = rtbs

    [oracle@oracle dbs]$orapwd file=orapwcuug password=oracle entries=3

    2、建立数据库相关目录

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/cuug/bdump

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/cuug/cdump

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/cuug/udump

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/oradata/cuug

    3、建立建库脚本(参考Oracle 11g Online)

    [oracle@oracle dbs]$cat cr_db.sql

    create database cuug

    user sys identified by oracle

    user system identified by oracle

    datafile '$ORACLE_BASE/oradata/cuug/system01.dbf' size 300m

    sysaux datafile '$ORACLE_BASE/oradata/cuug/sysaux01.dbf' size 100m

    default temporary tablespace temp tempfile '$ORACLE_BASE/oradata/cuug/temp01.dbf' size 100m

    undo tablespace rtbs datafile '$ORACLE_BASE/oradata/cuug/rtbs01.dbf' size 100m

    logfile

    group 1 '$ORACLE_BASE/oradata/cuug/redo01a.log' size 10m,

    group 2 '$ORACLE_BASE/oradata/cuug/redo02a.log' size 10m

    character set zhs16gbk;

    4、启动Instance建立数据库

    [oracle@oracle dbs]$export ORACLE_SID=cuug

    [oracle@oracle dbs]$sqlplus '/as sysdba'

    10:59:59 SYS@ test1>startup nomount;

    ORACLE instance started.

    Total System Global Area  417546240 bytes

    Fixed Size                  2213936 bytes

    Variable Size             268437456 bytes

    Database Buffers          142606336 bytes

    Redo Buffers                4288512 bytes

    11:00:12 SYS@ test1>@/home/oracle/cr_db.sql

    Database created.

    查看告警日志信息(alert_cuug.log);

    [oracle@oracle dbs]tail -f /u01/app/oracle/admin/cuug/bdump/alert_cuug.log

    create tablespace SYSTEM datafile  '$ORACLE_BASE/oradata/test/system01.dbf' size 400m

    default storage (initial 10K next 10K) online

    Sat Aug 20 00:26:34 2011

    Completed: create tablespace SYSTEM datafile  '$ORACLE_BASE/oradata/test/system01.dbf' size 400m

    default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online

    Sat Aug 20 00:26:34 2011

    create rollback segment SYSTEM tablespace SYSTEM

    storage (initial 50K next 50K)

    Completed: create rollback segment SYSTEM tablespace SYSTEM

    storage (initial 50K next 50K)

    Sat Aug 20 00:26:49 2011

    Thread 1 advanced to log sequence 2

    Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/test/redo02a.log

    Sat Aug 20 00:26:50 2011

    CREATE UNDO TABLESPACE RTBS DATAFILE  '$ORACLE_BASE/oradata/test/rtbs01.dbf' size 100m

    Sat Aug 20 00:26:51 2011

    Successfully onlined Undo Tablespace 1.

    Completed: CREATE UNDO TABLESPACE RTBS DATAFILE  '$ORACLE_BASE/oradata/test/rtbs01.dbf' size 100m

    Sat Aug 20 00:26:51 2011

    create tablespace SYSAUX datafile  '$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online

    Completed: create tablespace SYSAUX datafile  '$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online

    Sat Aug 20 00:26:54 2011

    CREATE TEMPORARY TABLESPACE TEMP TEMPFILE  '$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

    Completed: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE  '$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

    Sat Aug 20 00:26:55 2011

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

    Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

    Sat Aug 20 00:26:55 2011

    ALTER DATABASE DEFAULT TABLESPACE SYSTEM

    Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM

    Sat Aug 20 00:27:01 2011

    SMON: enabling tx recovery

    Sat Aug 20 00:27:02 2011

    Threshold validation cannot be done before catproc is loaded.

    replication_dependency_tracking turned off (no async multimaster replication found)

    Starting background process QMNC

    QMNC started with pid=13, OS id=6485

    Sat Aug 20 00:27:03 2011

    Completed: create database test

    user sys identified by **user system identified by *datafile '$ORACLE_BASE/oradata/test/system01.dbf' size 400m

    sysaux datafile '$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

    undo tablespace rtbs datafile '$ORACLE_BASE/oradata/test/rtbs01.dbf' size 100m

    default temporary tablespace temp tempfile '$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

    logfile

    group 1 '$ORACLE_BASE/oradata/test/redo01a.log' size 10m,

    group 2 '$ORACLE_BASE/oradata/test/redo02a.log' size 10m,

    group 3 '$ORACLE_BASE/oradata/test/redo03a.log' size 10m

    character set zhs16gbk

    5、建立数据字典

    数据字典脚本:

    [oracle@oracle dbs]cat cr_dict.sql

    @$ORACLE_HOME/rdbms/admin/catalog.sql

    @$ORACLE_HOME/rdbms/admin/catproc.sql

    conn system/oracle

    @$ORACLE_HOME/sqlplus/admin/pupbld.sql

    11:20:12 SYS@ test1>@/home/oracle/cr_dict.sql

    6、建立Users表空间并设为默认表空间

    系统默认的表空间为system,如果以system为默认表空间,会影响数据库的管理和性能

    12:09:56 SQL> create tablespace users

    12:10:01   2   datafile '/u01/app/oracle/oradata/cuug/user01.dbf' size 100m;

    Tablespace created.

    12:10:41 SQL> alter database default tablespace users;

    Database altered.

    7、添加example案例

    12:11:45 SQL> @$ORACLE_HOME/rdbms/admin/utlsampl.sql

    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

    With the Partitioning, OLAP and Data Mining options

    [oracle@oracle ~]$

    @至此,手工建库完成!

    展开全文
  • oracle手动建库

    2013-04-12 19:08:32
    oracle手动建库Oracle中建库,通常有两种方法。一是使用Oracle建库工且DBCA,这是一个图形界面工且,使用起来方便且很容易理解,因为它的界面友好、美观,而且提示也比较齐全。在Windows系统中,这个工具可以...
  • oracle手工建库步骤

    2013-07-16 13:46:46
    oracle手工建库步骤,文档详细描述了手工建库的步骤.
  • oracle静默建库删库脚本.txt
  • 1、建立口令文件,用于sys用户远程登录的认证(remote_login_passwordfile=exclusive),位置$ORACLE_HOME/dbs/orapwSID.创建命令: orapwd[oracle@work dbs]$ orapwd file=orapwprod password=oracle entries=5force=...

    1、建立口令文件,用于sys用户远程登录的认证(remote_login_passwordfile=exclusive),位置

    $ORACLE_HOME/dbs/orapwSID.创建命令: orapwd

    [oracle@work dbs]$ orapwd file=orapwprod password=oracle entries=5

    force=y

    remote_login_passwordfile

    1)none 拒绝sys用户从远程连接

    2)exclusive sys用户可以从远程连接

    3)share 多个库可以共享口令文件

    SQL>ALTER SYSTEM SET remote_login_passwordfile=NONE SCOPE=SPFILE

    【拒绝远程登录】

    2、创建init parameter 文件

    [oracle@oracle dbs]$more initdw.ora |grep -v '^#'|grep -v '^$'

    >initlx02.ora

    建立目录

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/lx02/bdump

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/lx02/cdump

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/admin/lx02/udump

    [oracle@oracle dbs]$ mkdir -p $ORACLE_BASE/oradata/lx02【存放控制文件的位置】

    修改初始化文件

    [oracle@oracle dbs]$ vi initlx02.ora

    db_name = lx02

    sga_target = 300M

    db_block_size = 8192

    pga_aggregate_target = 30M

    db_cache_size = 80M

    shared_pool_size = 60M

    parallel_threads_per_cpu = 4

    optimizer_mode = choose

    star_transformation_enabled = true

    db_file_multiblock_read_count = 16

    query_rewrite_enabled = true

    query_rewrite_integrity = trusted

    background_dump_dest = $ORACLE_BASE/admin/lx02/bdump

    user_dump_dest = $ORACLE_BASE/admin/lx02/udump

    core_dump_dest = $ORACLE_BASE/admin/lx02/cdump

    control_files = $ORACLE_BASE/oradata/lx02/control01.ctl

    undo_management = auto

    undo_tablespace = rtbs

    3、建立建库脚本

    1、库名

    2、表空间及数据文件的位置和大小

    3、redo 日志文件的位置和大小

    4、字符集

    (1)建库脚本:vi  cr_db.sql

    CREATE DATABASE anny

    USER SYS IDENTIFIED BY oracle

    USER SYSTEM IDENTIFIED BY oracle

    LOGFILE GROUP 1 ('/u01/app/oracle/oradata/anny/redo01.log') SIZE 100M,

    GROUP 2 ('/u01/app/oracle/oradata/anny/redo02.log') SIZE 100M,

    GROUP 3 ('/u01/app/oracle/oradata/anny/redo03.log') SIZE 100M

    MAXLOGFILES 10

    MAXLOGMEMBERS 5

    MAXLOGHISTORY 1

    MAXDATAFILES 100

    MAXINSTANCES 1

    CHARACTER SET zhs16gbk

    NATIONAL CHARACTER SET AL16UTF16

    DATAFILE '/u01/app/oracle/oradata/anny/system01.dbf' SIZE 325M REUSE

    EXTENT MANAGEMENT LOCAL(启动本地管理空闲区)

    SYSAUX DATAFILE '/u01/app/oracle/oradata/anny/sysaux01.dbf' SIZE 325M

    REUSE

    DEFAULT TEMPORARY TABLESPACE temp

    TEMPFILE '/u01/app/oracle/oradata/anny/temp01.dbf'

    SIZE 20M REUSE

    UNDO TABLESPACE rtbs【这个名字要和初始化文件的一致】

    DATAFILE '/u01/app/oracle/oradata/anny/rtbs01.dbf'

    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

    【根据自己的实际需求更改】

    告警日志信息:

    create tablespace SYSTEM datafile  '$ORACLE_BASE/oradata/test/system01.dbf'

    size 400m

    default storage (initial 10K next 10K) online

    Sat Aug 20 00:26:34 2011

    Completed: create tablespace SYSTEM datafile

    '$ORACLE_BASE/oradata/test/system01.dbf' size 400m

    default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY

    online

    Sat Aug 20 00:26:34 2011

    create rollback segment SYSTEM tablespace SYSTEM

    storage (initial 50K next 50K)

    Completed: create rollback segment SYSTEM tablespace SYSTEM

    storage (initial 50K next 50K)

    Sat Aug 20 00:26:49 2011

    Thread 1 advanced to log sequence 2

    Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/test/redo02a.log

    Sat Aug 20 00:26:50 2011

    CREATE UNDO TABLESPACE RTBS DATAFILE  '$ORACLE_BASE/oradata/test/rtbs01.dbf'

    size 100m

    Sat Aug 20 00:26:51 2011

    Successfully onlined Undo Tablespace 1.

    Completed: CREATE UNDO TABLESPACE RTBS DATAFILE

    '$ORACLE_BASE/oradata/test/rtbs01.dbf' size 100m

    Sat Aug 20 00:26:51 2011

    create tablespace SYSAUX datafile  '$ORACLE_BASE/oradata/test/sysaux01.dbf'

    size 100m

    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online

    Completed: create tablespace SYSAUX datafile

    '$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online

    Sat Aug 20 00:26:54 2011

    CREATE TEMPORARY TABLESPACE TEMP TEMPFILE

    '$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

    Completed: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE

    '$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

    Sat Aug 20 00:26:55 2011

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

    Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

    Sat Aug 20 00:26:55 2011

    ALTER DATABASE DEFAULT TABLESPACE SYSTEM

    Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM

    Sat Aug 20 00:27:01 2011

    SMON: enabling tx recovery

    Sat Aug 20 00:27:02 2011

    Threshold validation cannot be done before catproc is loaded.

    replication_dependency_tracking turned off (no async multimaster replication

    found)

    Starting background process QMNC

    QMNC started with pid=13, OS id=6485

    Sat Aug 20 00:27:03 2011

    Completed: create database test

    user sys identified by **user system identified by *datafile

    '$ORACLE_BASE/oradata/test/system01.dbf' size 400m

    sysaux datafile '$ORACLE_BASE/oradata/test/sysaux01.dbf' size 100m

    undo tablespace rtbs datafile '$ORACLE_BASE/oradata/test/rtbs01.dbf' size

    100m

    default temporary tablespace temp tempfile

    '$ORACLE_BASE/oradata/test/temp01.dbf' size 100m

    logfile

    group 1 '$ORACLE_BASE/oradata/test/redo01a.log' size 10m,

    group 2 '$ORACLE_BASE/oradata/test/redo02a.log' size 10m,

    group 3 '$ORACLE_BASE/oradata/test/redo03a.log' size 10m

    character set zhs16gbk

    (2)export  ORACLE_SID=lx02

    (3)启动数据库到nomount状态,startup nomount

    (4)启动告警日志  tail -f /$ORACLE_BASE/admin/lx02/bdump

    (5)运行建库脚本:@/export/home/oracle/cr_db.sql

    4、建立数据字典

    创建数据字典脚本:vi  cr_dict.sql

    @$ORACLE_HOME/rdbms/admin/catalog.sql

    @$ORACLE_HOME/rdbms/admin/catproc.sql

    conn system/oracle

    @$ORACLE_HOME/sqlplus/admin/pupbld.sql

    执行数据字典脚本:@/export/home/oracle/cr_dict.sql

    5、创建users 表空间,作为普通用户的默认表空间

    SQL> select tablespace_name from dba_tablespaces;【如果执行不成功,说明上一步有问题】

    TABLESPACE_NAME

    ------------------------------

    SYSTEM

    RTBS

    SYSAUX

    TEMP

    4 rows selected.

    08:08:27 SQL> col file_name for a50

    08:08:37 SQL> select file_id,file_name,tablespace_name from

    dba_data_files;

    FILE_ID FILE_NAME

    TABLESPACE_NAME

    ---------- --------------------------------------------------

    ------------------------------

    1 /u01/app/oracle/oradata/lx02/system01.dbf          SYSTEM

    2 /u01/app/oracle/oradata/lx02/rtbs01.dbf            RTBS

    3 /u01/app/oracle/oradata/lx02/sysaux01.dbf          SYSAUX

    SQL> create tablespace users

    2   datafile '/u01/app/oracle/oradata/lx02/user01.dbf' size 100m;【创建表空间】

    Tablespace created.

    select * from database_properties;【查看数据库的属性】

    SQL> alter database default tablespace users;【修改users的默认表空间】

    Database altered.

    08:10:45 SQL>  select file_id,file_name,tablespace_name from

    dba_data_files;【查看数据文件】

    FILE_ID FILE_NAME

    TABLESPACE_NAME

    ---------- --------------------------------------------------

    ------------------------------

    1 /u01/app/oracle/oradata/lx02/system01.dbf          SYSTEM

    2 /u01/app/oracle/oradata/lx02/rtbs01.dbf            RTBS

    3 /u01/app/oracle/oradata/lx02/sysaux01.dbf          SYSAUX

    4 /u01/app/oracle/oradata/lx02/user01.dbf            USERS

    SQL> select file_name,file_id,tablespace_name from dba_temp_files;【查看临时表空间,临时表不和数据文件放在一起】

    FILE_NAME                                             FILE_ID

    TABLESPACE_NAME

    -------------------------------------------------- ----------

    ----------------------------------------------------------------------

    /u01/app/oracle/oradata/anny/temp01.dbf                     1 TEMP

    select username,default_tablespace,temporary_tablespace from

    dba_users;【查看用户的默认表空间】

    USERNAME        DEFAULT_TABLESP TEMPORARY_TABLESPACE

    --------------- ---------------

    ------------------------------------------------------------------------------------------

    OUTLN           SYSTEM          TEMP

    SYS             SYSTEM          TEMP

    SYSTEM          SYSTEM          TEMP

    SCOTT           USERS           TEMP

    TOM             USERS           TEMP

    DBSNMP          SYSAUX          TEMP

    TSMSYS          USERS           TEMP

    DIP             USERS           TEMP

    4 rows selected.

    6、添加scott 案例

    SQL> @$ORACLE_HOME/rdbms/admin/utlsampl.sql【运行该脚本就可以使用系统模板】

    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -

    Production

    With the Partitioning, OLAP and Data Mining options

    到此,完成手工建库~~

    展开全文
  • Oracle手动建库常见问题 BLOG文档结构图 前言部分 ...
  • 环境:VM6.0 + REDHAT9.0 + ORACLE9.2在尝试多次用DBCA建库未能成功的情况下(最后一步迟迟不开始,仍未找到原因)决定用手工建库,实践成功!虽然其中的参数设置,有很多不合理的地方,但至少前进了一大步,终于把...
  • oradim -new -sid east -intpwd oracle -startmode manual -pfile 'd:oracleora92databaseinitEAST.ora'set oracle_home=d:oracleora92set oracle_sid=eastsqlplus/nolog--操作系统验证connect / as sysdba;...
  • ORACLE手工建库

    2015-04-27 12:35:43
    oracle 手工建库 第一步: 创建目录,赋予权限 oinstall组 手工建库的权限一般 755 1建立instance-------------控制文件 2passwor...
  • Oracle手工建库

    千次阅读 2012-02-07 22:35:31
    Oracle手工建库  最近几天学习总结一下,手工建库是作为dba必须掌握的一个基本技能,当然一般情况下不一定能用上,但是他能让我门清楚的认识 oracle的目录结构?oracle所必需的文件、理解表空间、认证方式等概念...
  • ORACLE 手动建库

    2013-12-15 01:08:54
    ORACLE手动建库ORACLE10GR2手动建库大致分为以下几个步骤编辑.bash_profile文件,设置环境变量创建所需目录结构创建初始化参数文件执行建库脚本下面以创建test数据库为例1、编辑.bash_profile文件,添加ORACLE_SID...
  • oracle 手动建库

    2019-03-29 01:19:10
    NULL 博文链接:https://listnumber.iteye.com/blog/1102399
  • oracle 手工建库

    2018-03-06 16:10:21
    手工建库 环境:centos 6.5 + oracle 11g [TOC] 1.设置当前$ORACLE_ SID=实例名 Export $ORACLE_SID=wk 2.为实例创建密码文件 orapwd file=$ORACLE_HOME/dbs/orapwwk password=123456 entries=10 3....
  • oracle cluster 建库脚本

    2012-12-14 15:26:57
    oracle RAC 手动建库,避免gsd起不来不能使用dbca
  • Oracle除了用DBCA可以建库外,也可以手动来建立数据库,手动建库能够更了解数据库的一些启动过程和运作机制,对深入学习oracle 很有帮助,本文演示了10g下手动建库的流程和可能碰到的问题。第一步:建立参数文件,这...
  • Oracle 手工建库

    2011-05-07 09:03:52
    最近在网上看到很多初学Oracle或是从旧版的 Oracle升级到10G的朋友们都问如何在Oracle 10G里手工建库的问题。今天正好有空,于是乎把在Oracle 10G里手工建库的全过程写出来,供各位从事Oracle工作和学习的同朋友们...
  • Oracle 手动建库

    2017-10-26 22:51:00
    其实手动建库可以提供更大的自由发挥的空间,根据情况进行定制 登录Oracle用户 指定SID(Instance Identifier) export ORACLE_SID=OCM 确保必须的环境变量已经设置,大多数平台上 ORACLE_SID 和 ORACLE_HOME 必须...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 12,843
精华内容 5,137
关键字:

oracle重新建库