OCP factory training notes (Third Day)

TCL: transaction control language, such as commit and rollback

PLSQL: Provides object-oriented support. You can call C to manipulate the hardware.

When called, PLSQL is put into the library cache in the share pool.

PLSQL Structure
Package
Package body (the pakage body can be encrypted to protect the property rights of PLSQL)


Differences between trigger and function:
Trigger is automatically triggered, while function is manually called, and function must return values.
Package is generated when packaging a series of PLSQL processes.
Store procedure stored procedures do not require a return value.

The package structure is as follows:
1> package specification)
2> package body)
Oracle has 350 built-in packages.

Object Type:
1> segment object (table, index, will be continuously added)
2> source object (original code object, cannot be extended)

Object in Oracle: if it does not exist, it will be identified as invalid.

Triggered by an oracle event,
DML content: insert event. insert, update, delete
DDL create, drop, alter, Grant, revoke, rename
Database logon, Logoff

Unlike other languages, Oracle generally does not recommend the use of triggers for business logic programming (because Oracle's delete statement does not automatically submit, You need to finally input commit, similar databases such as SQL Server are automatically submitted)
Oracle generally uses procedure for business logic programming.
Therefore, if the code of the trigger on SQL Server is to be transplanted to the Oracle platform, it is generally executed through procedure.

Oracle lock mechanism:
The purpose of the lock is to prevent concurrent modification operations on the same data.
One feature of the lock is that it will not be automatically upgraded. (Some locks are automatically upgraded)
The lock is related to the transaction. Once the transaction ends, the lock will disappear.
(Oracle transaction start and end:
Starting from the first DML statement implicit
Transaction to commit, rollbak ends
)

Two types of locks:
S, X shared locks, exclusive locks

An attribute of the lock. No lock is found during query.
Another attribute of the lock, which can be automatically queued and unlocked after being locked.

DML statements generate two locks:
1> the affected row generates an exclusive lock)
2> table-level shared locks

If other statements perform DML operations on the same row, this operation will wait (rather than report an error) until the lock is released.

DDL statements generate an exclusive lock,

Two types of conflict:
1> Blocking
Cause: the transaction has not been committed for a long time; a transaction has been running for a long time; the user uses an unnecessary High-Level Lock.
Solution: Kill session
Syntax
Select Sid, serial #, username from V $ session where Sid in (select blocking_session from V $ session );
2> deadlock
Once a deadlock occurs in Oracle, Oracle manages the lock and automatically submits an error to a session.
At this time, you need to manually end a session and re-run the blocked session.

About undo
You can create multiple Undo tablespaces, but for one instance, there is only one undo tablespace.
In the RAC environment, each node's instance has its own undo tablespace

Undo segment:
10 Gb is automatically managed, but it is manually managed on 8i.
At 10 Gb, Oracle sets up the number of rollback segments based on 1.1 times of the number of sessions.

When deleting a large table, we recommend that you specify a large undo tablespace. In addition, the submit operation is performed periodically.
We recommend that you set the Undo license time (undo_retention)> = the maximum possible query time of the user

If the Undo tablespace is insufficient, an error occurs.
Ora-01650: unable to extend rollback segment

Undo_retention (not mandatory by default)
1. Have enough time to accommodate
2. Large Object Parameters
3. Retain Parameters

Undo_retention
In any case, Oracle forces a transaction to be retainable within 15 minutes.

With undo advisor, we can view the estimated sampling
SQL> alter tablespace undosbs1 retention guarantee)
By default, the Undo tablespace is in the noguarantee state.

View rollback segment information:
Select USN, xacts from V $ rollstat;
USN xacts
0 0
1 1
2 0
3 0

Here (USN, xacts) is (0, 0) corresponding to the system rollback segment, (USN = 0 indicates the system rollback segment) (xacts indicates the number of active sessions in the system rollback segment)
The USN is equal to the transaction rollback segment. () Indicates a transaction rollback segment with an active session.

To view the Undo consultant, follow these steps:
A) In Enterprise Manager, select administration> related links> advisor central.
B) Click undo management.
C) Click undo advisor.
D) in the new undo retention field, enter 2. Then select days from the drop-down list.
E) Select last seven days in the analysis time period drop-down list. Results of
Analysis will be displayed.
Note: the values that you see are likely to be different from those shown here.

 

 

Security

DBA responsibility 1> industry security requirement Sarbanes-oxelay Act (SOx) financial security specifications to ensure financial data
How long can it be deleted after it is saved?
2> HIPAA: privacy and security specifications to ensure that personal privacy cannot be accessed.

Audit: 1. Users with DBA permissions must be trusted
2. audit data
3. DBA permissions must be shared
4. DBA accounts cannot be shared (DBAs cannot use sys for management and should use their respective accounts)
5. the DBA and system administrator must be two different accounts (root and sysdba are two separate roles)
6. Separate operator and DBA Permissions

Database Security 1. Restrict access to data (outside the firewall, connected to the card)
2. Verify the user, Advanced Security Options
3. Check audit activities.

Minimum permission Principle
1. Only install the smallest Service
2. open only required services
3. Only provide required services
4. Ensure the root user password
5. Permission restriction (select any table permission restriction)

Grant select any table to HR;
In this way, the HR user can see all the tables in the database (except the tables in the system tablespace)

If authorization is granted to public, any user is available.

Restrict users with administrator permissions,
Restrict remote operating system authentication
Remote_ OS _authent = false; (the default value is false. You do not need to change the index)
If this option is enabled,
If the password of the local and remote ORACLE machine happens to be the same, the remote user can log on to the database directly without authentication. (However, you still need to use tnsnames. ora
Connect to listener)

Audit (mandatory auditory)
Standard Database Auditing (standard audit, disabled by default)
Value_based Auditing
Fine-grained auditing (FGA, fine-grained Audit)

If a standard audit record is enabled, Oracle places the audit data in AUD $. Based on this table, the dba_aut_trail and dba_common_audit_trail views are generated.
If fine-grained audit records are opened, Oracle will record the audit records to fga_log $. The dba_aut_f * And dba_common_audit_trail views are generated based on this table.

Trial Process
1. Open Audit
2. Specify the audit content
3. generate audit records
4. generate audit records (AUT $)
5. View

Dba_aut_trail (Standard view) dba_aut_f_trail (fine-grained view)
Dba_common_audit_trail (Comprehensive Standard view and fine-grained view)

Alter system set audit_trail = a scope = spfile; (static parameter)
A = 'true (db) 'records are stored in the database's dictionary.
A = 'OS' records are stored in the OS (in the file system folder)
A = 'xml' is stored in the OS file system folder in XML format.

SQL> Audit update, delete on HR. Employees by access)
Audit update, delete on HR. Employees by session; (for Continuous sessions, there is only one audit record)

Non-focus audit:
SQL> audit all on HR. Employees; no focus audit

Delete audit records
SQL> noaudit delete on hr.jobs;

Audit by trigger (you can know the pre-image of the content to be audited)
Context Function: sys_context ()

Create or replace trigger system. hrsalary_audit
After update of salary
On HR. Employees
Referencing new as new old as old
For each row
Begin
If: Old. salary! =: New. Salary then
Insert into system. audit_employees
Values (sys_context ('userenv', 'OS _ user'), sysdate,
Sys_context ('userenv', 'IP _ address '),
: New. employee_id |
'Salary changed from' |: Old. Salary |
'To' |: New. Salary );
End if;
End;
/

Fine-grained auditing (conditional audits are supported. Standard audits only know what operations are performed)
1. Select Insert update, delete
2. dbms_fga

Audit DML rules
Delete will always be audited
Merge-> read as insert and update to audit
Records-> qualified

Unconditional audit: NULL Condition
If the audit content does not exist, an error occurs, ORA-28112
Data audit must be placed out of the database

If you set the system parameter audit_sys_operations to true
Oracle records all sys operations at the location of audit_file_dest.


If a standard audit record is enabled, Oracle places the audit data in AUD $. Based on this table, the dba_aut_trail and dba_common_audit_trail views are generated.
If fine-grained audit records are opened, Oracle will record the audit records to fga_log $. The dba_aut_f * And dba_common_audit_trail views are generated based on this table.

If you want to open the audit and view the specific SQL commands of the operation, you need to set
SQL> alter system set audit_trail = 'db _ extended 'scope = spfile;

 

Oracle Network Environment
Underlying interface: OCI interface Java.
JDBC interface (including OCI Interface)

Sqlnet. ora is required by both the client and server (network configuration file)

View globalname
SQL> show parameter service;
Check the database instance and Host Name
Select instance_name, host_name from V $ instance;

Dedicated server
Then, the server process is placed at the position of PGA.

Static registration of listener:
Write the corresponding information to listener. ora. The database is automatically registered to listener at startup and handler is automatically generated.
The service status is unkown, indicating static registration.

Dynamic Registration of listener:
The database background pmon registers the database process to the port listener in port 1521.

Listener status
Plsextproc: (call the C language interface service)

Connection method:
1> easy CONNECT: conn HR/hr@db.us.oracle.com: 1521/SERVICE_NAME
2> Local naming: Use tnsnames. ora

3> direcow.naming: centralized management
Edit sqlnet. ora and add the directory server address

Connector routing method:
From machine A to machine C, you must first use machine B. The original route uses a CMAN (Connection Manager)

Configure tnsnanme Connection Properties, fault tolerance and load balancing
Configure database connection Error Tolerance: Until one succeeds
Load Balancing: Randomly

Test:
Configure the listener and local naming connection mode for netmgr
Netca configuration listener and connection local naming connection method (equivalent to the next step of netca)

Shared Pool connection mode: Dispatcher connection mode
1. Put it in the Request queue through dispacher
2.
3. Pull the response queue and send it to the user process.

In the Shared Pool connection mode,
The shared pool of the SGA will be placed in the user's private information. If the SGA defines a large pool, it will be placed in the large pool.

When should I use the sharing method?
1. When the database is closed
2. backup and recovery
3. Load Balancing
The dispacher and share server parameters are configured on the server.
In order to configure the share connection on the client.

Exercise:
Configure the listener server Load balancer for the client:
Configure two listener parameters for the two databases.
Custom redundancy in service naming.

Configuration process
1. Use netmgr to modify listener and create two listener ports. One of the two ports, which is assumed to be listener1, must be statically registered.
2. Use netmgr to modify the connection string, orcl information, and add another server. Customize redundancy in service naming.
3. Open listener,
LSNRCTL start
LSNRCTL start listener1

 

The listener. ora file is similar:
Orcl =
(Description =
(Address_list =
(Address = (Protocol = TCP)
(Host = edsir294) (Port = 1521)
(Address = (Protocol = TCP)
(Host = edsir294) (Port = 1523)

If you want to register 2nd listeners dynamically, you must modify the local_listener parameter.
You can view the document by yourself.

 

After you stop listener and enable it, if the database adopts the dynamic registration method, it will take one minute before pmon registers the database to the listener.
Because pmon polls every minute by default.

Tns_admin
Environment variables in Windows-> System Variables
Set profile> export profile in UNIX

You must set the tns_admin parameter to tell Oracle to capture the tnsnames. ora parameters from this location by default.