Oracle 11g R2 DBA Operations Guide (first three chapters)

The Oracle 11g R2 DBA Operations Guide was the first book I learned about Oracle database knowledge, where I recorded my own learning notes, and I verified the knowledge points in the book by actual operation.

1. Oracle Database Foundation 1.1. relational database 1.1.1. relational Database model

1. relational Database model three core concepts: relationships, attributes, domains

2. Relationships: Tables made up of columns

Properties: Columns in a relationship

Domain: A collection of column values

3. Each row is called a tuple

4. rules for relational data models:

⑴ the order of columns in a table is arbitrary

⑵ attribute values for each tuple cannot be the same, and each tuple's corresponding attribute will contain a value

5. Key: A tuple can be determined by a key, which can consist of one or more properties

1.1.2. founder of relational data model

1. cod

1.3. database RDBMS

1. RDBMS: relational database management system

2. functions: Data definition, database creation, database maintenance, data manipulation, database operation management

1.4.SQL Language

1. SQL: Structured Query Language

2. Classification of SQL languages:

⑴ Data manipulation statements: Querying or manipulating data within an existing schema object, for example:Select, Insert,update,delete , etc.

⑵ data definition Statements: Define or modify schema objects, for example:create,rename, etc.

⑶ Transaction Control statements: Manage changes to data by DML statements and organize logically related DML statements into transactions

⑷ Session Control Statements: Managing properties of User sessions

⑸ System Control Statement: Modify the properties of the instance,alter system is the only control statement

⑹ embedding SQL Statements

2. installation of the database 2.1.windows Environment

1. The listener is used when accessing the database server over the network, and the client connects to the database through a connection tool or program . the listener is responsible for handing the user request to the database server process

2.Check the status of the listener:lsnrctl Status

3 . sqlplus tool location underwindows

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/89/E5/wKioL1ghQfbjAN3qAACRmYsoZeI889.png-wh_500x0-wm_3 -wmp_4-s_828806727.png "title=" image 1.png "alt=" Wkiol1ghqfbjan3qaacrmysozei889.png-wh_50 "/>

4. use alter user Scott identified by password account unlock; Unlock Scott user and set a password, Scott the user's default password is Tiger

5. If you mistakenly delete a Scott user or data, you can use the $ORACLE _home\rdbms\admin\scott.sql script to recover

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/89/E5/wKioL1ghQlbg98stAAApMQ_PHgk698.png-wh_500x0-wm_3 -wmp_4-s_1315074016.png "title=" image 2.png "alt=" Wkiol1ghqlbg98staaapmq_phgk698.png-wh_50 "/>

2.2.Linux Environment

1.

650) this.width=650; "Src=" Http://s4.51cto.com/wyfs02/M02/89/E5/wKioL1ghQnnwKKtVAAETyP0s3ME093.png-wh_500x0-wm_3 -wmp_4-s_2631189415.png "title=" image 3.png "alt=" Wkiol1ghqnnwkktvaaetyp0s3me093.png-wh_50 "/>

2. required Software catalog

⑴Oracle Base: Top-level catalog of Oracle software, recommended for /mount_point/app/software_owner

⑵Oracle Inventory:

3. startup and shutdown of the database 3.1. starting the database 3.1.1. database startup process

1. three states of database startup

Nomount Status: Only open DB instance, read parameter file

Mount Status:ORACLE locates and opens a control file to read various parameter information in the control file, such as the location of data files and log files, based on the location of the control files recorded in the parameter file

Open state: Opens the data file and carries out a series of checks that work for data recovery

3.1.2. booting to the nomount State

1. when the database boots to the Nomount state, the DB instance is opened, the parameter file is read, and the 5 required background processes are started:DBWR(Database write process) ,lgwr(log write process),Smon(System monitoring process),Pmon(Process monitoring process), CKPT (inspection point process)

2.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/89/E5/wKioL1ghQpjgcR7IAACAE7cgEz8271.png-wh_500x0-wm_3 -wmp_4-s_502437235.png "title=" image 4.png "alt=" Wkiol1ghqpjgcr7iaacae7cgez8271.png-wh_50 "/>

3. Database startup process is recorded in the alarm trace file, the method of querying the directory

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/89/E8/wKiom1ghQrfA5j4GAAAdruuiuaI115.png-wh_500x0-wm_3 -wmp_4-s_4051070.png "title=" image 5.png "alt=" Wkiom1ghqrfa5j4gaaadruuiuai115.png-wh_50 "/>

file name is Alert_orcl.log

4.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/89/E8/wKiom1ghQtOAXk_iAABXGz7tMrk603.png-wh_500x0-wm_3 -wmp_4-s_1046711411.png "title=" image 6.png "alt=" Wkiom1ghqtoaxk_iaabxgz7tmrk603.png-wh_50 "/>

this is because in the Nomount State,v$controlfile This dynamic view, if the control file is not open, you cannot query to the location of the control file, and Nomount the state does not open the control file; we can get the location of the control file by v$parameter This dynamic view

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/89/E8/wKiom1ghQuzRm68oAABjNkL661o717.png-wh_500x0-wm_3 -wmp_4-s_1069125138.png "title=" image 7.png "alt=" Wkiom1ghquzrm68oaabjnkl661o717.png-wh_50 "/>

3.1.3. database boot to Mount State

1. Two ways the database is booted into the Mount state

⑴ Direct Startup Mount

⑵ boot to nomount state before using ALTER DATABASE Mount

2. This is where we can view the V$controlfile dynamic view to get information about the control file because the control file is opened in the Mount state.

3.1.4. database boot to open State

1. Two ways the database starts up to the open state

⑴ starting directly with startup open or startup

⑵ boot to Mount state before using ALTER DATABASE open

3.2. shutting down the database 3.2.1. database shutdown process

1. close→dismount→shutdown

Close data file, log file (to Mount State):ALTER DATABASE close

Close Control file (to nomount State):ALTER DATABASE dismount

Close instance (close instance completely):shutdown

2. Direct Use of shutdown can completely shut down the database, but it is generally used in conjunction with parameters

⑴ shutdown normal: By default, when the database is closed, the new database connection is not allowed and the database is closed only if all current connections are exited, which is the safest way to shut down the database, but it can be time-consuming due to the number of connections

⑵shutdown Immediate: a faster and more secure shutdown of the database, which interrupts the current transaction, rolls back uncommitted transactions, forces all users to connect, and performs checkpoints to write dirty data to the data file. However, the speed of the shutdown can also be affected by the number of transactions and user connections

⑶shutdown Transactional: When the database is closed, the current connection continues, but the new connection is not allowed, and the database is closed once all the current transactions have completed

⑷shutdown Abort: Unsafe shutdown mode, which disconnects all current user connections, denies new connections, disconnects all currently executing transactions, shuts down the database immediately, and requires database recovery when the database restarts because it does not roll back incomplete transactions , and no checkpoint operation is performed .

Oracle 11g R2 DBA Operations Guide (first three chapters)