Oracle手动建库步骤.docx
《Oracle手动建库步骤.docx》由会员分享，可在线阅读，更多相关《Oracle手动建库步骤.docx（13页珍藏版）》请在冰豆网上搜索。
Oracle手动建库步骤
Oracle手动建库步骤
1.建立需要的目录
mkdir-p/ora/oracle/admin/zjlg/adump
mkdir-p/ora/oracle/admin/zjlg/bdump
mkdir-p/ora/oracle/admin/zjlg/cdump
mkdir-p/ora/oracle/admin/zjlg/udump
mkdir-p/ora/oracle/admin/zjlg/pfile
mkdir-p/ora/oracle/admin/zjlg/create
mkdir-p/arch/archivelog
mkdir-p/ora/oracle/oradata/zjlg
mkdir-p/ora/oracle/flash_recovery_area
2.设置环境变量，在用户环境变量里面加
$exportORACLE_BASE=/ora/oracle
$exportORACLE_HOME=$ORACLE_BASE/product/10.2.0
$exportORACLE_SID=zjlg
3.建初始化参数文件
$vi$ORACLE_HOME/dbs/initzjlg.ora---此处instancename大小写于环境变量定义不一致
#以下为建库必需参数
control_files='/ora/oracle/oradata/zjlg/control1.ctl','/ora/oracle/oradata/zjlg/control2.ctl','/ora/oracle/oradata/zjlg/control3.ctl'
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
db_name='zjlg'
db_block_size=8192
sga_max_size=2048M
sga_target=2048M
audit_file_dest='/ora/oracle/admin/zjlg/adump'
background_dump_dest='/ora/oracle/admin/zjlg/bdump'
core_dump_dest='/ora/oracle/admin/zjlg/cdump'
user_dump_dest='/ora/oracle/admin/zjlg/udump'
db_domain=''
open_cursors=500
processes=1000--#不设置默认40,根据具体业务多少，设大小,可以改的,无所谓.
log_archive_dest_1='LOCATION=/arch/archivelog'
log_archive_format='log_%t_%s_%r.arc'
job_queue_processes=10
undo_retention=10800
#audit_sys_operations='TRUE'
#如果需要开通审计功能，设置如下参数
#audit_trail=db,extended---#这里注意，如果将来会转换成物理备库，这里就不能设置db，否则将来物理备库没法打开readonly模式
db_recovery_file_dest='/ora/oracle/flash_recovery_area'#OMF模式必需设置
db_recovery_file_dest_size=2G--#OMF模式必需设置
#如果采用OMF管理数据库文件，则还需设置以下参数
db_create_file_dest='/ora/oracle/admin/oradata/zjlg'#自动在该目录下建立./{db_name}/datafile目录
db_create_online_log_dest_1='/ora/oracle/admin/oradata/zjlg'#自动在该目录下建立./{db_name}/onlinelog目录
注：
创建相关目录同时修改文件夹的权限,包括数据文件目录的文件以及dump相关的目录的权限
4.建立密码文件,使用操作系统验证就不要下面这一行，使用口文件验证就用这一行，具体看sqlnet中的设置
$ORACLE_HOME/bin/orapwdfile=$ORACLE_HOME/dbs/orapw$ORACLE_SIDpassword=oracleentries=5force=y
5.建spfile后启动实例并开始建库
$sqlplus'/assysdba'
SQL>startupnomountpfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora
SQL>createspfilefrompfile;
SQL>shutdownimmediate
SQL>startupnomount
SQL>createdatabasezjlg
Controlfilereuse
usersysidentifiedbyoracle
usersystemidentifiedbyoracle
logfilegroup1（'/ora/oracle/oradata/zjlg/redo01.log'）size100m,
group2（'/ora/oracle/oradata/zjlg/redo02.log'）size100m,
group3（'/ora/oracle/oradata/zjlg/redo03.log'）size100m
maxlogfiles5
maxlogmembers5
maxloghistory1
maxdatafiles100
maxinstances1
charactersetzhs16gbk
nationalcharactersetal16utf16
datafile'/ora/oracle/oradata/zjlg/system01.dbf'size1024mreuse
extentmanagementlocal
sysauxdatafile'/ora/oracle/oradata/zjlg/sysaux01.dbf'size800mreuse
defaulttablespaceusersdatafile'/ora/oracle/oradata/zjlg/users01.dbf'size500mreuse
defaulttemporarytablespacetemp
tempfile'/ora/oracle/oradata/zjlg/temp01.dbf'
size4096mreuse
undotablespaceundotbs1
datafile'/ora/oracle/oradata/zjlg/undotbs01.dbf'
size2048mreuseautoextendonmaxsizeunlimited;
注：
在创建数据库的过程中可以查看alert.log,查看是否出现错误
6.运行数据字典脚本，其中catalog和catproc是必需的，其它可选
SQL>spool/ora/oracle/catlog.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>spooloff
执行完后检查/home/catlog.log看有没有错误
7.新建sqlplus属性和帮助、USERS表空间
SQL>alterusersysidentifiedbymanager;
SQL>alterusersystemidentifiedbymanager;
SQL>connsystem/manager
SQL>@?
/sqlplus/admin/pupbld.sql
SQL>@?
/sqlplus/admin/help/hlpbld.sql
SQL>@?
/sqlplus/admin/help/helpus.sql
SQL>conn/assysdba
SQL>alterdatabasedefaulttablespaceusers;
SQL>alterusersystemporarytablespacetemp;
8.最后修改为归档模式并重启
SQL>shutdownimmediate;
SQL>conn/assysdba
SQL>startupmount
SQL>alterdatabasearchivelog;
SQL>alterdatabaseopen;
重新编译所有失效对象
SQL>executeutl_recomp.recomp_serial（）;
------------------------------------------------------------------------------------------------------参考
参考spfile
zjlg.__db_cache_size=3925868544
zjlg.__java_pool_size=16777216
zjlg.__large_pool_size=16777216
zjlg.__shared_pool_size=1056964608
zjlg.__streams_pool_size=0
*.audit_file_dest='/ora/oracle/admin/zjlg/adump'
*.background_dump_dest='/ora/oracle/admin/zjlg/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/ora/oracle/oradata/zjlg/control01.ctl','/ora/oracle/oradata/zj
lg/control02.ctl','/ora/oracle/oradata/zjlg/control03.ctl'
*.core_dump_dest='/ora/oracle/admin/zjlg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='zjlg'
*.db_recovery_file_dest='/ora/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='（PROTOCOL=TCP）（SERVICE=zjlgXDB）'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1675624448
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.sga_target=5028970496
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora/oracle/admin/zjlg/udump'
*.log_archive_dest_1='LOCATION=/arch/archivelog'
*.log_archive_format='log_%t_%s_%r.arc'
参考controlfile
--ThefollowingarecurrentSystem-scopeREDOLogArchivalrelated
--parametersandcanbeincludedinthedatabaseinitializationfile.
--
--LOG_ARCHIVE_DEST=''
--LOG_ARCHIVE_DUPLEX_DEST=''
--
--LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
--DB_UNIQUE_NAME="zjlg"
--
--LOG_ARCHIVE_CONFIG='SEND,RECEIVE,NODG_CONFIG'
--LOG_ARCHIVE_MAX_PROCESSES=2
--STANDBY_FILE_MANAGEMENT=MANUAL
--STANDBY_ARCHIVE_DEST=?
/dbs/arch
--FAL_CLIENT=''
--FAL_SERVER=''
--
--LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
--LOG_ARCHIVE_DEST_10='OPTIONALREOPEN=300NODELAY'
--LOG_ARCHIVE_DEST_10='ARCHNOAFFIRMNOEXPEDITENOVERIFYSYNC'
--LOG_ARCHIVE_DEST_10='REGISTERNOALTERNATENODEPENDENCY'
--LOG_ARCHIVE_DEST_10='NOMAX_FAILURENOQUOTA_SIZENOQUOTA_USEDNODB_UNIQUE_NAME
'
--LOG_ARCHIVE_DEST_10='VALID_FOR=（PRIMARY_ROLE,ONLINE_LOGFILES）'
--LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
--BelowaretwosetsofSQLstatements,eachofwhichcreatesanew
--controlfileandusesittoopenthedatabase.Thefirstsetopens
--thedatabasewiththeNORESETLOGSoptionandshouldbeusedonlyif
--thecurrentversionsofallonlinelogsareavailable.Thesecond
--setopensthedatabasewiththeRESETLOGSoptionandshouldbeused
--ifonlinelogsareunavailable.
--Theappropriatesetofstatementscanbecopiedfromthetraceinto
--ascriptfile,editedasnecessary,andexecutedwhenthereisa
--needtore-createthecontrolfile.
--
--Set#1.NORESETLOGScase
--
--Thefollowingcommandswillcreateanewcontrolfileanduseit
--toopenthedatabase.
--DatausedbyRecoveryManagerwillbelost.
--Additionallogsmayberequiredformediarecoveryofoffline
--Usethisonlyifthecurrentversionsofallonlinelogsare
--available.
--Aftermountingthecreatedcontrolfile,thefollowingSQL
--statementwillplacethedatabaseintheappropriate
--protectionmode:
--ALTERDATABASESETSTANDBYDATABASETOMAXIMIZEPERFORMANCE
STARTUPNOMOUNT
CREATECONTROLFILEREUSEDATABASE"ZJLG"NORESETLOGSNOARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY12080
LOGFILE
GROUP1'/ora/oracle/oradata/zjlg/redo01.log'SIZE50M,
GROUP2'/ora/oracle/oradata/zjlg/redo02.log'SIZE50M,
GROUP3'/ora/oracle/oradata/zjlg/redo03.log'SIZE50M
--STANDBYLOGFILE
DATAFILE
'/ora/oracle/oradata/zjlg/system01.dbf',
'/ora/oracle/oradata/zjlg/undotbs01.dbf',
'/ora/oracle/oradata/zjlg/sysaux01.dbf',
'/ora/oracle/oradata/zjlg/users01.dbf',
'/ora/oracle/oradata/zjlg/zfdxc01.ora',
'/ora/oracle/oradata/zjlg/HRM01.DBF',
'/ora/oracle/oradata/zjlg/YXXT01.ora',
'/ora/oracle/oradata/zjlg/LXXT01.ora',
'/ora/oracle/oradata/zjlg/zf01.ora',
'/ora/oracle/oradata/zjlg/zfdxc02.dbf',
'/ora/oracle/oradata/zjlg/zfdxc03.dbf',
'/ora/oracle/oradata/zjlg/zfdxc04.dbf',
'/ora/oracle/oradata/zjlg/hrm02.dbf',
'/ora/oracle/oradata/zjlg/yxxt02.dbf',
'/ora/oracle/oradata/zjlg/lxxt02.dbf',
'/ora/oracle/oradata/zjlg/zf02.dbf',
'/ora/oracle/oradata/zjlg/hrm03.dbf',
'/ora/oracle/oradata/zjlg/yxxt03.dbf',
'/ora/oracle/oradata/zjlg/lxxt03.dbf',
'/ora/oracle/oradata/zjlg/zf03.dbf',
'/ora/oracle/oradata/zjlg/yjs01.ora',
'/ora/oracle/oradata/zjlg/yjs02.dbf',
'/ora/oracle/oradata/zjlg/zf04.dbf',
'/ora/oracle/oradata/zjlg/zf05.dbf',
'/ora/oracle/oradata/zjlg/xyzh01.ora',
'/ora/oracle/oradata/zjlg/xyzh02.dbf',
'/ora/oracle/oradata/zjlg/zf7',
'/ora/oracle/oradata/zjlg/zf8',
'/ora/oracle/oradata/zjlg/zf9',
'/ora/oracle/oradata/zjlg/zf10',
'/ora/oracle/oradata/zjlg/zf11',
'/ora/oracle/oradata/zjlg/zf',
'/ora/oracle/oradata/zjlg/zf12',
'/ora/oracle/oradata/zjlg/zf13',
'/ora/oracle/oradata/zjlg/zf15',
'/ora/oracle/oradata/zjlg/zf14',
'/ora/oracle/oradata/zjlg/zf202',
'/ora/oracle/oradata/zjlg/zf16',
'/ora/oracle/oradata/zjlg/zf17',
'/ora/oracle/oradata/zjlg/zf2_1',
'/ora/oracle/oradata/zjlg/zf2_2',
'/ora/oracle/oradata/zjlg/zf2_3',
'/ora/oracle/oradata/zjlg/zf20',
'/ora/oracle/oradata/zjlg/zf21',
'/ora/oracle/oradata/zjlg/zf24',
'/ora/oracle/oradata/zjlg/yxxt4',
'/ora/oracle/oradata/zjlg/yxxt5',
'/ora/oracle/oradata/g_2000q4.dbf',
'/ora/oracle/oradata/g_2001q1.dbf',
'/ora/oracle/oradata/g_2001q2.dbf',
'/ora/oracle/oradata/zjlg/zf32'
CHARACTERSETZHS16GBK
;
--Commandstore-createincarnationtable
--BelowlognamesMUSTbechangedtoexistingfilenameson
--disk.Anyonelogfilefromeachbranchcanbeusedto
--re-createincarnationrecords.
--ALTERDATABASEREGISTERLOGFILE'/ora/oracle/flash_recovery_area/ZJLG/archivelog/2010_09
升级会员 