手工创建database笔记

手工创建database笔记

1 准备工作

export ORACLE_SID=oradb

mkdir -p /u01/app/oracle/admin/oradb/bdump
mkdir -p /u01/app/oracle/admin/oradb/udump
mkdir -p /u01/app/oracle/admin/oradb/cdump
mkdir -p /u01/app/oracle/admin/oradb/adump
mkdir -p /u01/app/oracle/admin/oradb/pfile

password文件
orapwd file=/u01/app/oracle/oracle/product/10.2.0/db_1/dbs/orapworadb entries=4 password=admin000

数据文件路径
mkdir -p /u01/app/oracle/oradata/oradb

归档日志文件路径
mkdir -p /u01/app/oracle/oradata/oradb/arch

flashback文件路径
mkdir -p /u01/app/oracle/oradata/oradb/fra

参数文件
control_files              = (/u01/app/oracle/oradata/oradb/control.001.dbf,/u01/app/oracle/oradata/oradb/control.002.dbf,/u01/app/oracle/oradata/oradb/control.003.dbf)
db_name                    = oradb
log_archive_dest_1         = "LOCATION=/u01/app/oracle/oradata/oradb/arch"
log_archive_dest_state_1   = enable
db_block_size              = 8192
processes                  = 150
sessions                   = 1200
open_cursors               = 1024
undo_management            = AUTO
undo_tablespace            = undotbs
compatible                 = 10.2.0
sga_target                 = 300M
nls_language               = AMERICAN
nls_territory              = AMERICA
db_recovery_file_dest      =/u01/app/oracle/oradata/oradb/fra
db_recovery_file_dest_size = 10G
db_create_file_dest     = '/u01/app/oracle/oradata/oradb'
db_create_online_log_dest_1 = '/u01/app/oracle/oradata/oradb'
background_dump_dest       = '/u01/app/oracle/admin/oradb/bdump'
core_dump_dest             = '/u01/app/oracle/admin/oradb/cdump'
user_dump_dest         = '/u01/app/oracle/admin/oradb/udump'

2 创建db
startup nomount pfile='/u01/app/oracle/admin/oradb/pfile/initoradb.ora'

创建spfile
create spfile from pfile='/u01/app/oracle/admin/oradb/pfile/initoradb.ora'

shutdown immediate

startup nomount

--create database
CREATE DATABASE oradb
   USER SYS IDENTIFIED BY admin000
   USER SYSTEM IDENTIFIED BY admin000
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/oradb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/oradb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/oradb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/app/oracle/oradata/oradb/system01.dbf' SIZE 325M
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/app/oracle/oradata/oradb/sysaux01.dbf' SIZE 325M
   DEFAULT TABLESPACE tbs_1 DATAFILE '/u01/app/oracle/oradata/oradb/tbs_01.dbf' SIZE 325M
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/u01/app/oracle/oradata/oradb/temp01.dbf'
      SIZE 20M
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/oradb/undotbs01.dbf'
      SIZE 200M AUTOEXTEND ON MAXSIZE UNLIMITED;

运行script
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc

创建临时tablespace
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/oradb/temp2_data01.dbf' SIZE 200M  AUTOEXTEND ON NEXT 640K MAXSIZE 200M EXTENT MANAGEMENT LOCAL ;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

创建undo tablespace
CREATE UNDO TABLESPACE undotbs2
     DATAFILE '/u01/app/oracle/oradata/oradb/undo_data02.dbf' SIZE 2M AUTOEXTEND ON NEXT 640K MAXSIZE 200M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

以oracle managed file来创建表空间
CREATE TABLESPACE tbs1
     DATAFILE SIZE 2M AUTOEXTEND ON NEXT 640K MAXSIZE 200M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER TABLESPACE tbs1
     ADD DATAFILE SIZE 2M AUTOEXTEND ON NEXT 640K MAXSIZE 200M;

创建spfile和pfile
create pfile='/u01/init.ora' from spfile;
create spfile from pfile='/u01/init.ora';

创建大文件表空间
CREATE BIGFILE TABLESPACE bigtbs
    DATAFILE '/u01/app/oracle/oradata/oradb/bigtbs01.dbf' SIZE 10M AUTOEXTEND ON NEXT 640K MAXSIZE 50G
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ALTER TABLESPACE bigtbs resize 20M;

全备份数据库
run
{
backup database format '/u03/backup/db_%u%p%s.bak' include current controlfile;
backup archivelog all format '/u03/backup/arch_%u%p%s.bak' delete input;
}

配置网络连接
listener.ora
network.ora

配置共享进程
ALTER SYSTEM SET SHARED_SERVERS = 5 scope=both;

ALTER SYSTEM SET SHARED_SERVERS = 0;

select * from v$shared_server;

alter system set DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)" scope=both;

打开跟踪文件
sqlnet.ora
trace_level_client = 10
trace_unique_client = on
trace_file_client = sqlnet.trc
trace_directory_client =

trace_level_server = 10
trace_file_server = server.trc
trace_directory_server = [/code]

TNSPING.TRACE_LEVEL = 4
TNSPING.TRACE_DIRECTORY =

listener.ora
#TRACE_LEVEL_LISTENER=16
TRACE_FILE_LISTENER=listener.trc
TRACE_DIRECTORY_LISTENER=/u01/app/oracle/oracle/product/10.2.0/db_1/network/trace

动态listener注册问题

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oradb.test.com)(PORT = 1521))
    )
  )

其中机器名oradb.test.com必须在hosts或者dns域名解析中

也可以用alter database register来注册listener

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27143/viewspace-672598/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-672598/