OCP factory training notes (Day 1)

1. ou: Oracle University
2. Instructor name *** @ oracle.com

3. Oca. OCP. OCM
OCM requires that OCP Be the first to pass two advanced courses, lab for 2 days
4. course schedule
Am-Am
PM-PM
5. Q &
Ekit Teaching Materials
Other Oracle Application Systems
Oracle IAS
J2EE server: Solaris
Oracle applicaction 11i

 

Oracle jdevelop: Oracle Developer Suite
10g means grid.
Otn.oracle.com global Oracle Forum Global Grid Forum: GCF

What are the advantages of grid:
1. Virtualization: there is no need to know where the computing is located during computing.
2. Dynamic supply: 24x7 is implemented through dataguard
3. Resource Pool: RAC can combine cheap blades to form powerful functions.

Oracle's original article means: God's predictions, instructions
Oracle Server (RDBMS) = instance + database
Instance = SGA shared memory + background process

Oracle Memory Structure

Server process: PGA (translated as program or private)
Shared_pool: PLSQL and SQL are all placed in this pool, and dictionary is placed in this pool: dictionary cache, library cache, cursors
Database buffer cache: Database Buffer
Java pool: dedicated to Java code
Stream pool (defines database selection specifications)
Large pool (select the File Buffer in the large pool for RMAN)

Is the Web server used Apache? Yes, 9i and 8i are all on one disk, but 10 Gb is a separate disc.

Relationship and difference between connect and session
1> physical connection
2> session logic concepts

 

What operations will the following DML statement do?
Select * from Em where empno = 100;
Step 3:
1. parse: access to the shared pool. The data dictionary is planned.
2. Execute scheduling job
3. Fetch the data.
DDL operations do not perform fetch operations.

 

SMON
Pmon)

How do I set the number of dbwr instances?
If the system has four more CPUs (cores), a dbwr process will be added.

Check Point, modify control files and data files.
If a process fails, it will be restored to the nearest successful checkpoint through SMON.
SMON performs a roll forward operation and then performs a roll back operation)
During the specific process, SMON reads the control file, retrieves the SCN corresponding to the checkpoint time, and then reads the redo block,
Execute all redo operations starting from that time until the end of the redo block.

What is the concept of a thing:
A group of DML statements that are executed as part of a group of logic are all executed or all canceled.

There are two types of checkpoints,
1: Incremental (when the checkpoint not complete alarm is triggered, the alarm is reported during Incremental backup.
2. Complete (manual checkpoint)

Physical Io should be minimized for OLAP Systems
In the pinned state, the server process is in this State when the block is being updated. At this time, the operating system will generate a latch
This latch will be released only when dbwr writes the gray data.
When latch is used in the dbwr and new cache areas, other users cannot use the same cache area as the new one.

Redolog
Oracle requires at least two redolog groups and one control file
One or more member. Member relationships can be mirror in each group.
If multiple control files are specified, these control files can be written in parallel.

Oracle introduces a binary parameter file, and spfile is one of them. Its advantage is that it can be modified without text editing.
The purpose of passwd file is to verify that the login user has sufficient permissions.
The purpose of the sysaux tablespace is to reduce the pressure on the system tablespace, which is called the auxiliary tablespace of the system (the auxiliary tablespace of the system)
We can call a specific PLSQL process to migrate data from sysaux to another tablespace.

The size of ORACLE data block is determined by db_block_size, which must be a multiple of the operating system blocks.
Data, index, and undo are all segment concepts.
When performing sort operations on a large amount of data, the temp table in the temporary tablespace is usually used.

Schema is an important concept of oracle,
It indicates the object set under the account.

For example, HR does not represent a user, but application,

The session is user-based (the object link graph, ERD)
Oracle is an object-oriented relational database ordbms.

The update step of the Shadow process first writes dirty data and then updates the data file header.

Oui: Java (any support for Java is available)
Onm: Network Configuration
OEM: Database Control, which is built on Oracle's Java Server "Solaris. (Communicate with it through JDBC)
ISQLPLUS browser version.
Oracle Secure Backup: A tape backup tool.
DataPump: logically imports and exports data.
Sqlloader: Load External data into Oracle.

Flash recovery to support two full backups, the default size is twice that of the database.
It is feasible to run 9i and 10G on the same machine and require a separate Oracle Home.

 

Ofa: optimal flexible architecture organizes a large number of software

Purpose,
1. Placing a large number of disks in different locations mainly serves as an IO balancing function. With the popularization of RAID, it becomes less important,
2. comply with another Specification
/U01 // disk01/Corresponding mount point
/U01/APP/Oracle/u01/APP/applm folder naming rules,
. DBF. CTL File naming rules.

Oracle_base
The base of Oracle direcoprocessor structure of ofa.
Oracle_sid
Is case sensitive)

AIX does not need to edit kernel parameters. You can use the default parameter.

 

Install Oracle software
Run the following two scripts:
1> orainstallroot. Sh creates a file name. in/etc, the name is orainst. Loc, telling inventory where
2> root. Sh creates an oratab file. Each time an instance is created, a row is created under oratab and some agents are read.
For example, if dbconsole has an agent built in, it will read the file parent oratab and find the database target on the machine.

 

 

The grid function is to clone databases.
When using dbca, you can create a response file, record all the response information, and record all operations, which can be stored elsewhere,
Other machines can use this code to automatically configure your configurations.

 

The listener process is only used for handshaking between user processes and server processes.
After a handshake, the session will no longer rely on listener.

 

 

Database Type
Data warehouse: PGA is relatively large, and SGA is relatively small, which is helpful for query.
Transaction process: the value of SGA is larger than that of PGA.

OLAP option (Data Mining) DH (online analysis function)

 

Custom Database: custom database
You can create a custom database by executing a large number of scripts. (Data Dictionary)
Custom SID Name: Oracle requires no more than 8 bytes.
The domain name contained in the Global dbname and the operating system domain name.

Oracle Java Server.

 

 

Sysman and Dbsnmp are used to monitor the database status.

 

OMF management mechanism is used to facilitate file management,
OMF saves 100 MB of time to create files.
The tablespace managed by OMF is deleted using Oracle commands, and files are automatically deleted (in the file system)
If db_create_file_dest is defined, Oracle automatically starts OMF.

When a database is created, the default mode is non-archive.

The database folder in Windows is relative to DBs in UNIX.

To create a template from an existing database, use the following steps to export the database:
Create Database-> select template Module

Redo log files and control files can be used as up to 5 images.

How is the number of processes of the server process determined?
Based on the user session, if there are 300 users, you need to set the processes parameter to 450, and then add 150
Why do we set this?
Because a session also produces a lot of processes, when querying in parallel, this will reduce the number of original processes,
That is, a session may correspond to multiple processes.
For 300 user sessions, you need to set more processes than 300.

Oracle character set mainly includes
Database Character Set + National Character Set

It is recommended that the SGA size be set to at least 210 m, and the share pool Oracle should be at least 180 m in 10 GB
The Shared Server Mode certainly supports the dedicated mode, but the dedicated mode does not necessarily support the Shared Server mode.

 

Database Control: You can manage only one database and log on to the Enterprise Console.
Grid contrl can manage multiple databases.

 

 

Differences between sys operator and sysdba:
Sys operator cannot view the business table. sysdba can view the business table.
Both sys operator and DBA can start and stop databases.

Sysdba is not recommended for ISQLPLUS access.
The database can be used as ISQLPLUS sysdba only after authorization in the Java environment.

Oracle SQL script
Sqlplus HR/hr <EOF
Select ...;
Update ...;
Quit
EOF
Exit

Sqlplus "/As sysdba" adopts OS authentication.
> Show user; displays the current user name

How to execute External SQL scripts in Oracle:

Sqlplus HR/hr @ cc. SQL

Only one sqlplus buffer can exist
Modify the variable and directly modify the previous SQL script.
Process
SQL> List
Select * from HR. Orders ments;
> DEFINE _ editor = gedit
> DEFINE _ editor = vi
> Edit

 

 

Db_file_mutiblock: Number of processes in which one data block can be shared in I/O memory.

 

 

Dynamic parameters: parameters that take effect without restarting. Alter system set ** = ** scope = memory, spfile, both;
Static Parameters: parameters that must be restarted to take effect. Alter system set ** = ** scope = spfile;
Whether the custom database is archived must be specified through the CTL file.

Database startup and status
1. Search for the order of parameter files: spfilesid. ora-> spfile. ora-> initsid. ora
2. instance Startup Process
Startup nomount-> open alert. log, and perform write operations-> Read the CTL file, and mount operations (data files do not need to be read)-> Read data files, open operations (recovery is required if recovery is required)
Shutdown abort is equivalent to power-down the database, but it does not have a strong physical pulse
Shutdown transaction: Shut down the database only when the transaction ends.
Shutdown normal all users disconnect before shutting down the database
Shutdown; = shutdown normal

Alert history has nothing to do with alertlog, just read the warning information in the dynamic view that exceeds the threshold.
The dynamic performance view includes
V $ SQL, V $ session, V $ lock, etc.

V $ _ fixed_table. These fixed tables can only be queried and cannot be modified. The information of these statements will be modified according to changes in a timely manner.

 

V $ views have many functions,

 

For example, you can query SQL statements that have been executed for more than 200000 milliseconds.
SQL> select SQL _text, executions from V $ SQL where cpu_time> 200000;
SQL> select Sid, ctime
SQL> select status from V $ instance; check the instance status.