数据库启动之OPEN-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

数据库启动之OPEN

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:48 PM

openstartstart updatabasemodel

1、数据库OPEN模式 start open,默认是此模式，或alter database open；这是数据库正常操作的状态，此状态下，任何合法用户都可以数据库连接和执行正常的数据访问操作。此时， ORACLE启动实例，并打开控制文件，打开所有联机数据文件，打开所有联机重做日志

1、数据库OPEN模式

start open,默认是此模式，或alter database open；这是数据库正常操作的状态，此状态下，任何合法用户都可以数据库连接和执行正常的数据访问操作。

此时，ORACLE启动实例，并打开控制文件，打开所有联机数据文件，打开所有联机重做日志文件。

此时任何数据文件或联机重做日志文件不存在，ORACLE将返回出错信息。此后ORACLE将检查所有联机数据文件和所有联机重做日志文件能否打开并检查数据一致性，如不一致则由SMON后台进程进行实例恢复。

2.数据库OPEN的几种特殊模式：

alterdatabase read only; 将数据库打开至只读模式。

alterdatabase read write; 恢复至正常模式。

只读模式的数据库可以： 执行查询，使用本地管理的表空间来执行磁盘排序。将数据文件脱机、联机，不能对表空间进行此操作。执行数据文件和表空间的脱机恢复。

数据库的限制模式： 此时只有具有restricted session系统权限的用户才可以登陆,。startup restrict

将已运行的数据库设置为限制模式 alter system enable restricted session; 此时已经登陆的用户将不受影响，未登陆的需要有restricted session系统权限。可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' 杀掉已登陆用户进程。

查询有哪些用户已经登陆：

SQL>select sid,serial#,username,type from v$session where type like '%USER';

3.控制文件重要性

由于控制文件中记录了数据库数据文件、日志文件位置、检查点信息等，在OPEN阶段，ORACLE根据控制文件中记录的这些信息找到对应文件，然后进行检查点及完整性检查。不存在问题则正常启动数据库。如存在检查点不一致或文件丢失，则需要进行恢复。

4.在OPEN过程吕，ORACLE检查以下两项：

检查数据文件头中检查点计数（checkpoint CNT）是否与控制文件中检查点计数（checkpoint CNT）一致。以此确定数据文件是否来自恢复文件。

如果检查点计数检查通过，数据库进行第二次检查，检查数据文件头的开始SCN和控制文件中记录的该文件的结束SCN是否一致，如果控制文件中记录的结束SCN等于数据文件头的开始SCN，不需要恢复。
关于实例恢复的详细分析：http://blog.csdn.net/haibusuanyun/article/details/17082235

以上两次检查通过后，打开数据库，锁定数据文件，并将每个数据文件的结束SCN设置为无穷大。

下面是实验：

SQL>alter database open;

alterdatabase open

第 1 行出现错误:

ORA-01157:无法标识/锁定数据文件 1 - 请参阅 DBWR 跟踪文件

ORA-01110:数据文件 1:'E:\SYSORADATA\SYSTEM01.DBF' ----------------在本文开始部分重命令了这个文件

SQL>host move e:\sysoradata\system01bak.dbf e:\sysoradata\system01.dbf

SQL>alter database open;

数据库已更改。

SQL>select status from v$instance;

STATUS

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

OPEN

5.OPEN时的ALERT日志中信息---对字符集的判断

日志中红字的Database Characterset is ZHS16GBK 是每次数据库启动时，ORACLE判断控制文件中记录的字符集为数据库中字符集是否相符，相符则记录此条记录。

不相符，则以数据库中字符集为准更新控制文件中字符集信息，产生日志类似：updating Character set in controlfile to ZHS16GBK

第一次因数据文件不存在时的日志：

SunFeb 03 22:44:07 2013

alterdatabase open

SunFeb 03 22:44:07 2013

Errorsin file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_988.trc:

ORA-01157:cannot identify/lock data file 1 - see DBWR trace file

ORA-01110:data file 1: 'E:\SYSORADATA\SYSTEM01.DBF'

ORA-27041:unable to open file

OSD-04002:无法打开文件

O/S-Error:(OS 2) 系统找不到指定的文件。

Errorsin file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_324.trc:

ORA-01157:无法标识/锁定数据文件 1 - 请参阅 DBWR 跟踪文件

ORA-01110:数据文件 1:'E:\SYSORADATA\SYSTEM01.DBF'

ORA-1157signalled during: alter database open...

SunFeb 03 22:44:09 2013

Checkerrun found 1 new persistent data failures

恢复数据文件后OPEN日志： Database Characterset is ZHS16GBK语句在偏后

SunFeb 03 22:45:01 2013

alterdatabase open

SunFeb 03 22:45:01 2013

LGWR:STARTING ARCH PROCESSES

SunFeb 03 22:45:01 2013

ARC0started with pid=19, OS id=420

ARC0:Archival started

LGWR:STARTING ARCH PROCESSES COMPLETE

ARC0:STARTING ARCH PROCESSES

SunFeb 03 22:45:02 2013

ARC1started with pid=21, OS id=2440

SunFeb 03 22:45:02 2013

ARC2started with pid=22, OS id=3216

ARC1:Archival started

ARC2:Archival started

ARC1:Becoming the 'no FAL' ARCH

ARC1:Becoming the 'no SRL' ARCH

ARC2:Becoming the heartbeat ARCH

SunFeb 03 22:45:02 2013

ARC3started with pid=23, OS id=2460

Thread1 opened at log sequence 56

Current log# 8 seq# 56 mem# 0: D:\DISK1\REDO05A.LOG

Current log# 8 seq# 56 mem# 1:D:\DISK2\REDO05B.LOG

Current log# 8 seq# 56 mem# 2: D:\DISK3\REDO05C.LOG

Current log# 8 seq# 56 mem# 3:D:\DISK4\REDO05D.LOG

Current log# 8 seq# 56 mem# 4: D:\DISK5\REDO05E.LOG

Successfulopen of redo thread 1

MTTRadvisory is disabled because FAST_START_MTTR_TARGET is not set

SunFeb 03 22:45:02 2013

SMON:enabling cache recovery

ARC3:Archival started

ARC0:STARTING ARCH PROCESSES COMPLETE

ArchivedLog entry 86 added for thread 1 sequence 53 ID 0x4f44a7a1 dest 1:

Successfullyonlined Undo Tablespace 2.

Verifyingfile header compatibility for 11gtablespace encryption..

Verifying11g file header compatibilityfor tablespace encryption completed

SMON:enabling tx recovery

SunFeb 03 22:45:17 2013

ArchivedLog entry 87 added for thread 1 sequence 54 ID 0x4f44a7a1 dest 1:

ArchivedLog entry 88 added for thread 1 sequence 54 ID 0x4f44a7a1 dest 2:

ArchivedLog entry 89 added for thread 1 sequence 54 ID 0x4f44a7a1 dest 3:

ArchivedLog entry 90 added for thread 1 sequence 54 ID 0x4f44a7a1 dest 4:

ArchivedLog entry 91 added for thread 1 sequence 54 ID 0x4f44a7a1 dest 5:

SunFeb 03 22:45:18 2013

ArchivedLog entry 92 added for thread 1 sequence 55 ID 0x4f44a7a1 dest 1:

ArchivedLog entry 93 added for thread 1 sequence 55 ID 0x4f44a7a1 dest 2:

ArchivedLog entry 94 added for thread 1 sequence 55 ID 0x4f44a7a1 dest 3:

ArchivedLog entry 95 added for thread 1 sequence 55 ID 0x4f44a7a1 dest 4:

ArchivedLog entry 96 added for thread 1 sequence 55 ID 0x4f44a7a1 dest 5:

SunFeb 03 22:45:18 2013

Database Characterset is ZHS16GBK

NoResource Manager plan active

SunFeb 03 22:45:30 2013

Startingbackground process QMNC

SunFeb 03 22:45:30 2013

QMNCstarted with pid=24, OS id=3684

SunFeb 03 22:45:40 2013

Completed: alter database open

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles