MYSQL网站制作之Oracle诊断案例-Spfile案例一则

采用DBaaS解决方案，他们也可以使用同大企业一样的技术。在大型组织中，DBaaS可以提供部门级解决MySQL学习教程，而无需IT部门和采购部门的介入，提供更快和更容易的方法来实现小型解决方案。oracle
Oracle诊断案例-Spfile案例一则



link:

http://www.eygle.com/case/spfile.htm

情形申明:
体系：SUNSolaris8
数据库版本:9203
成绩形貌：工程职员呈报，数据库在从头启动时没法一般启动.反省发明UNDO表空间丧失.
成绩诊断及办理历程以下:



1.上岸体系反省alert.log文件

反省alert.log文件是一般是我们诊断数据库成绩的第一步

SunOS5.8

login:root
Password:
Lastlogin:ThuApr111:39:16from10.123.7.162
SunMicrosystemsInc.SunOS5.8GenericPatchOctober2001
Youhavenewmail.
#su-oracle
bash-2.03$cd$ORACLE_BASE/admin/*/bdump
bash-2.03$vi*.log

"alert_gzhs.log"7438lines,283262characters
SatFeb720:30:062004
StartingORACLEinstance(normal)
LICENSE_MAX_SESSION=0
LICENSE_SESSIONS_WARNING=0
SCNscheme3
Usinglog_archive_destparameterdefaultvalue
LICENSE_MAX_USERS=0
SYSauditingisdisabled
StartingupORACLERDBMSVersion:9.2.0.3.0.
Systemparameterswithnon-defaultvalues:
processes=150
timed_statistics=TRUE
shared_pool_size=1157627904
large_pool_size=16777216
java_pool_size=637534208
control_files=/u01/oradata/gzhs/control01.ctl,
/u02/oradata/gzhs/control02.ctl,
/u03/oradata/gzhs/control03.ctl
db_block_size=8192
db_cache_size=2516582400
compatible=9.2.0.0.0
log_archive_start=TRUE
log_archive_dest_1=LOCATION=/u06/oradata/gzhs/arch
log_archive_format=%t_%s.dbf
db_file_multiblock_read_count=16
fast_start_mttr_target=300
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800
remote_login_passwordfile=EXCLUSIVE
db_domain=
instance_name=gzhs
dispatchers=(PROTOCOL=TCP)(SERVICE=gzhsXDB)
job_queue_processes=10
hash_join_enabled=TRUE
background_dump_dest=/oracle/admin/gzhs/bdump
user_dump_dest=/oracle/admin/gzhs/udump
core_dump_dest=/oracle/admin/gzhs/cdump
sort_area_size=524288
db_name=gzhs
open_cursors=300
star_transformation_enabled=FALSE
query_rewrite_enabled=FALSE
pga_aggregate_target=838860800
aq_tm_processes=1
PMONstartedwithpid=2
DBW0startedwithpid=3
LGWRstartedwithpid=4
CKPTstartedwithpid=5
SMONstartedwithpid=6
"alert_gzhs.log"7438lines,283262characters
USER:terminatinginstanceduetoerror30012
InstanceterminatedbyUSER,pid=26433
ORA-1092signalledduring:ALTERDATABASEOPEN...
ThuApr111:11:082004
StartingORACLEinstance(normal)
LICENSE_MAX_SESSION=0
LICENSE_SESSIONS_WARNING=0
SCNscheme3
Usinglog_archive_destparameterdefaultvalue
LICENSE_MAX_USERS=0
SYSauditingisdisabled
StartingupORACLERDBMSVersion:9.2.0.3.0.
Systemparameterswithnon-defaultvalues:
processes=150
timed_statistics=TRUE
shared_pool_size=1157627904
large_pool_size=16777216
java_pool_size=637534208
control_files=/u01/oradata/gzhs/control01.ctl,/u02/oradata/gzhs/control02.ctl,/u03/oradata/gzhs/control03.ctl
db_block_size=8192
db_cache_size=2516582400
compatible=9.2.0.0.0
log_archive_start=TRUE
log_archive_dest_1=LOCATION=/u06/oradata/gzhs/arch
log_archive_format=%t_%s.dbf
db_file_multiblock_read_count=16
fast_start_mttr_target=300
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800
remote_login_passwordfile=EXCLUSIVE
db_domain=
instance_name=gzhs
dispatchers=(PROTOCOL=TCP)(SERVICE=gzhsXDB)
job_queue_processes=10
hash_join_enabled=TRUE
background_dump_dest=/oracle/admin/gzhs/bdump
user_dump_dest=/oracle/admin/gzhs/udump
core_dump_dest=/oracle/admin/gzhs/cdump
sort_area_size=524288
db_name=gzhs
open_cursors=300
star_transformation_enabled=FALSE
query_rewrite_enabled=FALSE
pga_aggregate_target=838860800
aq_tm_processes=1
PMONstartedwithpid=2
DBW0startedwithpid=3
LGWRstartedwithpid=4
CKPTstartedwithpid=5
SMONstartedwithpid=6
RECOstartedwithpid=7
CJQ0startedwithpid=8
ThuApr111:11:132004
startingup1sharedserver(s)...
QMN0startedwithpid=9
ThuApr111:11:132004
startingup1dispatcher(s)fornetworkaddress(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))...
ARCH:STARTINGARCHPROCESSES
ARC0startedwithpid=12
ARC0:Archivalstarted
ARC1startedwithpid=13
ThuApr111:11:132004
ARCH:STARTINGARCHPROCESSESCOMPLETE
ThuApr111:11:132004
ARC0:Threadnotmounted
ThuApr111:11:132004
ARC1:Archivalstarted
ARC1:Threadnotmounted
ThuApr111:11:142004
ALTERDATABASEMOUNT
ThuApr111:11:182004
Successfulmountofredothread1,withmountid1088380178.
ThuApr111:11:182004
DatabasemountedinExclusiveMode.
Completed:ALTERDATABASEMOUNT
ThuApr111:11:272004
alterdatabaseopen
ThuApr111:11:272004
Beginningcrashrecoveryof1threads
ThuApr111:11:272004
Startedfirstpassscan
ThuApr111:11:282004
Completedfirstpassscan
1redoblocksread,0datablocksneedrecovery
ThuApr111:11:282004
Startedrecoveryat
Thread1:logseq177,block2,scn0.33104793
RecoveryofOnlineRedoLog:Thread1Group3Seq177Readingmem0
Mem#0errs0:/u01/oradata/gzhs/redo03.log
ThuApr111:11:282004
Completedredoapplication
ThuApr111:11:282004
Endedrecoveryat
Thread1:logseq177,block3,scn0.33124794
0datablocksread,0datablockswritten,1redoblocksread
Crashrecoverycompletedsuccessfully
ThuApr111:11:282004
LGWR:PrimarydatabaseisinCLUSTERCONSISTENTmode
Thread1advancedtologsequence178
Thread1openedatlogsequence178
Currentlog#1seq#178mem#0:/u01/oradata/gzhs/redo01.log
Successfulopenofredothread1.
ThuApr111:11:282004
ARC0:Evaluatingarchivelog3thread1sequence177
ThuApr111:11:282004
ARC0:Beginningtoarchivelog3thread1sequence177
CreatingarchivedestinationLOG_ARCHIVE_DEST_1:/u06/oradata/gzhs/arch/1_177.dbf
ThuApr111:11:282004
SMON:enablingcacherecovery
ARC0:Completedarchivinglog3thread1sequence177
ThuApr111:11:282004
Errorsinfile/oracle/admin/gzhs/udump/gzhs_ora_27781.trc:
ORA-30012:263267317373261355277325274344UNDOTBS1262273264346324332273362300340320315262273325375310
267
ThuApr111:11:282004
Error30012happenedduringdbopen,shuttingdowndatabase
USER:terminatinginstanceduetoerror30012
InstanceterminatedbyUSER,pid=27781
ORA-1092signalledduring:alterdatabaseopen...
:q



.............



在警报日记开端显现了数据库在Open形态由于毛病而非常停止.

2.实验从头启动数据库




bash-2.03$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-Productionon木曜日4月111:43:522004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.

已毗连到余暇例程。

SQL>startup
ORACLE例程已启动。

TotalSystemGlobalArea4364148184bytes
FixedSize736728bytes
VariableSize1845493760bytes
DatabaseBuffers2516582400bytes
RedoBuffers1335296bytes
数据库装载终了。
ORA-01092:ORACLE例程停止。强行断开毗连


.............






工程职员呈报的成绩重现.



3.反省数据文件





bash-2.03$cd/u01/oradata/gzhs
bash-2.03$ls-l
total55702458
-rw-r-----1oracledba1073750016Apr111:44UNDOTBS2.dbf
-rw-r-----1oracledba1073750016Apr111:44WAP12_BILLINGDETAIL.dbf
-rw-r-----1oracledba1073750016Apr111:44WAP12_MAIN.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN10.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN11.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN2.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN3.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN4.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN5.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN6.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN7.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN8.dbf
-rw-r-----1oracledba2097160192Apr111:44WAP12_MAIN9.dbf
-rw-r-----1oracledba1073750016Apr111:44WAP12_MVIEW.dbf
-rw-r-----1oracledba1073750016Mar2417:15WAP12_TEMP1.dbf
.........................
.............





发明存在文件UNDOTBS2.dbf

4.mount数据库，反省体系参数




bash-2.03$sqlplus"/assysdba"SQL*Plus:Release9.2.0.3.0-Productionon木曜日4月111:46:202004
Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.已毗连到余暇例程。SQL>SQL>SQL>startupmount;
ORACLE例程已启动。
TotalSystemGlobalArea4364148184bytesFixedSize736728bytesVariableSize1845493760bytesDatabaseBuffers2516582400bytesRedoBuffers1335296bytes数据库装载终了。
SQL>selectnamefromv$datafile;NAME--------------------------------------------------------------------------------/u01/oradata/gzhs/system01.dbf/u01/oradata/gzhs/cwmlite01.dbf/u01/oradata/gzhs/drsys01.dbf/u01/oradata/gzhs/example01.dbf/u01/oradata/gzhs/indx01.dbf/u01/oradata/gzhs/odm01.dbf/u01/oradata/gzhs/tools01.dbf/u01/oradata/gzhs/users01.dbf/u01/oradata/gzhs/xdb01.dbf........................./u01/oradata/gzhs/UNDOTBS2.dbf
已选择23行。
SQL>SQL>showparameterundoNAMETYPEVALUE
-----------------------------------------------------------------------------undo_managementstringAUTOundo_retentioninteger10800undo_suppress_errorsbooleanFALSE
undo_tablespacestringUNDOTBS1
SQL>showparameterspfile
NAMETYPEVALUE
-----------------------------------------------------------------------------
spfilestring

.........................
.............




发明体系没有利用spfile,而初始化参数设置的undo表空间为UNDOTBS1

5.反省参数文件





bash-2.03$cd$ORACLE_HOME/dbsbash-2.03$ls
init.orainitgzhs.orainitgzhs.ora.oldorapwgzhsinitdw.orainitgzhs.ora.hurraylkGZHSsnapcf_gzhs.fbash-2.03$viinitgzhs.ora
"initgzhs.ora"[Incompletelastline]105lines,3087characters
####################################################
#Copyright(c)1991,2001,2002byOracleCorporation
####################################################
###########################################
#Archive
###########################################
log_archive_dest_1=LOCATION=/u06/oradata/gzhs/arch
log_archive_format=%t_%s.dbflog_archive_start=true###########################################
#CacheandI/O
###########################################
db_block_size=8192
db_cache_size=2516582400
db_file_multiblock_read_count=16
###########################################
#CursorsandLibraryCache
###########################################
open_cursors=300
......................

###########################################
#SystemManagedUndoandRollbackSegments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
:q!
.............





这个设置是极为可疑的.
嫌疑参数文件和实践数据库设置不符.

6.再次反省alert文件
查找关于UNDO表空间的操纵

第一部分，创立数据库时的信息:






SatFeb720:30:122004CREATEDATABASEgzhsMAXINSTANCES1MAXLOGHISTORY1MAXLOGFILES5MAXLOGMEMBERS3MAXDATAFILES100DATAFILE/u01/oradata/gzhs/system01.dbfSIZE500MREUSEAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITEDEXTENTMANAGEMENTLOCALDEFAULTTEMPORARYTABLESPACETEMPTEMPFILE/u01/oradata/gzhs/temp01.dbfSIZE1000MREUSEAUTOEXTENDONNEXT250MMAXSIZEUNLIMITEDUNDOTABLESPACE"UNDOTBS1"DATAFILE/u01/oradata/gzhs/undotbs01.dbfSIZE1000MREUSEAUTOEXTENDONNEXT100MMAXSIZEUNLIMITED
CHARACTERSETZHS16GBK
NATIONALCHARACTERSETAL16UTF16
LOGFILEGROUP1(/u01/oradata/gzhs/redo01.log)SIZE256M,
GROUP2(/u01/oradata/gzhs/redo02.log)SIZE256M,
GROUP3(/u01/oradata/gzhs/redo03.log)SIZE256M
.............




注重，这也是OCP课本上提到的两种创立UNDO表空间的体例之一

第二部分,发明创立UNDOTBS2的纪录信息:





WedMar2420:20:582004/*OracleOEM*/CREATEUNDOTABLESPACE"UNDOTBS2"DATAFILE/u01/oradata/gzhs/UNDOTBS2.dbfSIZE1024MAUTOEXTENDONNEXT100MMAXSIZEUNLIMITEDWedMar2420:22:372004CreatedUndoSegment_SYSSMU11$CreatedUndoSegment_SYSSMU12$CreatedUndoSegment_SYSSMU13$CreatedUndoSegment_SYSSMU14$CreatedUndoSegment_SYSSMU15$CreatedUndoSegment_SYSSMU16$CreatedUndoSegment_SYSSMU17$CreatedUndoSegment_SYSSMU18$CreatedUndoSegment_SYSSMU19$CreatedUndoSegment_SYSSMU20$Completed:/*OracleOEM*/CREATEUNDOTABLESPACE"UNDOTBS2"
WedMar2420:24:252004
UndoSegment11Onlined
UndoSegment12Onlined
UndoSegment13Onlined
UndoSegment14Onlined
UndoSegment15Onlined
UndoSegment16Onlined
UndoSegment17Onlined
UndoSegment18Onlined
UndoSegment19Onlined
UndoSegment20Onlined
SuccessfullyonlinedUndoTablespace15.
UndoSegment1Offlined
UndoSegment2Offlined
UndoSegment3Offlined
UndoSegment4Offlined
UndoSegment5Offlined
UndoSegment6Offlined
UndoSegment7Offlined
UndoSegment8Offlined
UndoSegment9Offlined
UndoSegment10Offlined
UndoTablespace1successfullyswitchedout.
.............




第三部分，新的UNDO表空间被使用

WedMar2420:24:252004
ALTERSYSTEMSETundo_tablespace=UNDOTBS2SCOPE=MEMORY;

我们发明成绩就在这里,创立了新的UNDO表空间今后，由于利用的是pfile文件，修正的只对以后实例失效，操纵职员健忘了修正pfile文件.

假如利用spfile，缺省的修正局限是both,会同时修正spfile文件，就能够制止以上成绩的呈现.

第四部分，删除UNDOTBS1的信息




WedMar2420:25:012004/*OracleOEM*/DROPTABLESPACE"UNDOTBS1"INCLUDINGCONTENTSANDDATAFILESCASCADECONSTRAINTSWedMar2420:25:032004Deletedfile/u01/oradata/gzhs/undotbs01.dbfCompleted:/*OracleOEM*/DROPTABLESPACE"UNDOTBS1"INCLUDI
.............






如许再次从头启动数据库的时分，成绩呈现了，pfile中界说的UNDOTBS1找不到了，并且操纵其实好久之前，没人能回想起来，乃至没法得知是甚么人的操纵。




7.变动pfile，启动数据库

修正undo表空间



###########################################
#SystemManagedUndoandRollbackSegments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS2

....

bash-2.03$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-Productionon木曜日4月111:55:112004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


毗连到:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-64bitProduction
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>select*fromv$version;

BANNER
----------------------------------------------------------------
Oracle9iEnterpriseEditionRelease9.2.0.3.0-64bitProduction
PL/SQLRelease9.2.0.3.0-Production
CORE9.2.0.3.0Production
TNSforSolaris:Version9.2.0.3.0-Production
NLSRTLVersion9.2.0.3.0-Production

SQL>exit
从Oracle9iEnterpriseEditionRelease9.2.0.3.0-64bitProduction
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production中止开
bash-2.03$





在这里我们能够看到，利用spfile能够免除手工修正pfile文件的贫苦，削减了出错的大概。

既然Oracle9i给我们供应了这个新特征，就值得我们进修利用它.




为维持生产环境和非生产环境需要不断地投入管理支持费用MySQL学习教程，最终导致客户为数据库资源投入巨额成本。

所以你总能得到相应的升级版本，来满足你的需求。

我个人认为就是孜孜不懈的学习

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

连做梦都在想页面结构是怎么样的，绝非虚言

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..