Shulou(Shulou.com)06/01 Report--

Oracle installation 1. Install virtualbox:

# yum install gcc kernel-devel kernel-headers

# yum install virtualbox-5....

# / etc/init.d/vboxdrv setup compiles kernel modules manually and completes them automatically during installation

# usermod-G vboxusers root

two。 Install linux:

Create a virtual machine:

Name: oracle11gR2_RHEL6.4_x64

Type: linux 64bit

Memory: 2048MB

Hard disk size: 100GB

Set up:

Boot sequence: hard disk, network

Network: network card 1, bridging eth0

Remove sound, usb devices

Installation: desktop mode

Hostname: node1.test.com,ip uses dhcp,swap4GB and the rest is given to /

3. Adjust the linux system:

Turn off the firewall:

# service iptables stop

# service ip6tables stop

# chkconfig iptables off

# chkconfig ip6tables off

Disabled Firewall in Management tools

Turn off selinux:

# vi / etc/selinux/config

SELINUX=disabled

Configure yum:

# rm-f / etc/yum.repos.d/*

# wget ftp://172.16.8.100/rhel6.repo-P / etc/yum.repos.d/

Or:

# vi / etc/yum.repos.d/rhel6.repo

[Server]

Name=Server

Baseurl= file:///media/"RHEL_6.5 x86 / 64 Disc 1 "/ Server

Enabled=1

Gpgcheck=0

Install vb enhancements:

# yum-y install gcc kernel-devel

# ln-s / usr/src/kernels/2.6.32-431.el6.x86_64/ / usr/src/linux

Device-- > install enhancements

Right-click the eject eject CD

4. Confirm the identity of root:

# id

5. Hardware configuration:

# grep MemTotal / proc/meminfo

# grep SwapTotal / proc/meminfo

# vi / etc/fstab (permanently modified)

Tmpfs / dev/shm tmpfs defaults,size=2G 0 0

# mount-o remount / dev/shm

Temporary modification

# mount-t tmpfs shmfs-o size=2g / dev/shm

# uname-m

# df-h

6. Rpm check and install:

# cat / etc/redhat-release

# uname-r

# rpm-qa | grep glibc

# yum install glibc

# yum install glibc.i686

Binutils-2.20.51.0.2-5.11.el6 (x86 / 64)

Compat-libcap1-1.10-1 (x86x64)

Compat-libstdc++-33-3.2.3-69.el6 (x86x64)

Compat-libstdc++-33-3.2.3-69.el6.i686

Gcc-4.4.4-13.el6 (x86 / 64)

Gcc-c++-4.4.4-13.el6 (x86 / 64)

Glibc-2.12-1.7.el6 (i686)

Glibc-2.12-1.7.el6 (x86 / 64)

Glibc-devel-2.12-1.7.el6 (x86 / 64)

Glibc-devel-2.12-1.7.el6.i686

Ksh

Libgcc-4.4.4-13.el6 (i686)

Libgcc-4.4.4-13.el6 (x86 / 64)

Libstdc++-4.4.4-13.el6 (x86 / 64)

Libstdc++-4.4.4-13.el6.i686

Libstdc++-devel-4.4.4-13.el6 (x86 / 64)

Libstdc++-devel-4.4.4-13.el6.i686

Libaio-0.3.107-10.el6 (x86 / 64)

Libaio-0.3.107-10.el6.i686

Libaio-devel-0.3.107-10.el6 (x86 / 64)

Libaio-devel-0.3.107-10.el6.i686

Make-3.81-19.el6

Sysstat-9.0.4-11.el6 (x86 / 64)

7. Create a user:

# groupadd-g 1000 oinstall

# groupadd-g 1001 dba

# groupadd-g 1002 oper

# useradd-u 1000-g oinstall-G dba,oper oracle

# passwd oracle

8. Modify kernel parameters:

# vi / etc/sysctl.conf

Fs.aio-max-nr = 1048576

Fs.file-max = 6815744

Kernel.shmall = 2097152

Kernel.shmmax = 536870912

Kernel.shmmni = 4096

Kernel.sem = 250 32000 100 128

Net.ipv4.ip_local_port_range = 9000 65500

Net.core.rmem_default = 262144

Net.core.rmem_max = 4194304

Net.core.wmem_default = 262144

Net.core.wmem_max = 1048576

# sysctl-p

9. Modify resource limits:

# vi / etc/security/limits.conf

Oracle soft nofile 1024

Oracle hard nofile 65536

Oracle soft nproc 2047

Oracle hard nproc 16384

Oracle soft stack 10240

Oracle hard stack 32768

10. Create a directory:

# mkdir-p / u01/app/oracle

# chown-R oracle:oinstall / U01

# chmod-R 775 / U01

11. Modify the profile:

# vi ~ oracle/.bash_profile

Export ORACLE_SID=orcl

Export ORACLE_BASE=/u01/app/oracle

Export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

Export ORACLE_HOSTNAME=node1.test.com

Export ORACLE_UNQNAME=orcl

Export PATH=$PATH:$ORACLE_HOME/bin

Export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

Export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Export NLS_LANG=american_america.AL32UTF8

Export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'

Export EDITOR=vi

Export

twelve。 Use the hosts file to resolve the hostname:

# hostname

Node1.test.com

# vi / etc/hosts

192.168.0.1 node1.test.com node1

# ping node1

# ping node1.test.com

13. Extract the installation package:

# cd / installation

# unzip p10404530_112030_Linux-x86-64 _ [12] of7.zip

Or:

Extract the 2 installation packages in the physical host and share them with the virtual machine with the following command:

# mkdir / database

# mount-t vboxsf database / database

14. Graphical interface installation:

# xhost +

# su-oracle

$cd / database/

$. / runInstaller

Slightly

Execute two scripts as root

15. Create a monitor and database:

Create a monitor under the graphical interface, and execute it with oracle:

$netca

$lsnrctl status

$netstat-tlnp | grep 1521

Graphical interface creation db,oracle execution:

$dbca

16. Test:

Sqlplus Test:

$sqlplus sys/sys@orcl as sysdba

$sqlplus/as sysdba

SQL > show user

SQL > select count (*) from hr.employees

SQL > exit

Browser test:

Https://192.168.0.1:1158/em

Sys/password sysdba

Install rlwrap:

Root identity installation

# yum install rlwrap-0.42-1.el6.x86_64.rpm

# vi ~ oracle/.bashrc

Alias sqlplus='rlwrap sqlplus'

Alias rman='rlwrap rman'

# su-oracle

$sqlplus / as sysdba

Root identity installation

# yum install flash-plugin-11.2.202.508-release.x86_64.rpm

Sqldeveloper Test:

Root identity installation

# yum install jdk-8u51-linux-x64.rpm

# yum install sqldeveloper-4.1.1.19.59-1.noarch.rpm

# / usr/local/bin/sqldeveloper

Enter the path / usr/java/jdk1.8.0_51/ of the jdk

Click the + sign in the upper left corner to create a new connection:

Sys/password, connection type: basic, role: sysdba

192.168.0.1 Port 1521 sid:orcl

Test, save.

17. Add a startup script:

# vi / etc/oratab change N to Y

Orcl:/u01/app/oracle/product/11.2.0/db_1:Y

# vi / etc/rc.d/init.d/oracle

#! / bin/bash

# chkconfig:35 99 01

Case "$1" in

Start)

Echo-n "Starting Oracle Database& Listener:"

Su-oracle-c 'dbstart $ORACLE_HOME' > / dev/null

Echo "[ok]"

Echo-n "Starting Oracle EM dbconsole:"

Su-oracle-c "emctl start dbconsole" > / dev/null

Echo "[ok]"

Touch / var/lock/subsys/oracle

Stop)

Echo-n "Shutting Down Oracle EM dbconsole:"

Su-oracle-c "emctl stop dbconsole" > / dev/null

Echo "[ok]"

Echo-n "Shutting Down Oracle Database& Listener:"

Su-oracle-c 'dbshut $ORACLE_HOME' > / dev/null

Echo "[ok]"

Rm-f / var/lock/subsys/oracle

*)

Echo "Usage: oracle {start | stop}"

Exit 1

Esac

# chmod 755 / etc/rc.d/init.d/oracle

# chkconfig-add oracle

# chkconfig-list oracle

18. Delete password validity period:

# su-oracle

$sqlplus / as sysdba

SQL > alter profile default limit PASSWORD_LIFE_TIME unlimited

19. Modify the network settings for virtualbox (optional):

Global settings à add hostonly network, set ip to 192.168.0.253, cancel dhcp (linuxonly)

Virtual machine network changed to hostonly

Change the ip of the network card in the virtual machine to static, 192.168.0.1

# vi / etc/sysconfig/network-scripts/ifcfg-eth0

BOOTPROTO=none

IPADDR=192.168.0.1

PREFIX=24

# ifdown eth0;ifup eth0

# vi / etc/hosts

192.168.0.1 node1.test.com node1

SQLselect query statement 1. View the table under the hr user name and unlock the hr user:

$sqlplus / as sysdba or SQL > conn / as sysdba

SQL > show user

SQL > select table_name from dba_tables where owner='HR'

SQL > select * from hr.employees

SQL > alter user hr account unlock identified by hr

$sqlplus hr/hr or SQL > conn hr/hr

SQL > show user

SQL > select * from tab

SQL > desc employees

Exercise:

View the table under the scott user name and unlock the scott user:

Scott/tiger

two。 Use the full-screen editing feature of sqlplus:

$echo $EDITOR

SQL > select * from hr.employees

SQL > ed

SQL > / execute

3. Basic select statement:

SQL > select * from employees

SQL > desc employees

SQL > select LAST_NAME, SALARY, COMMISSION_PCT from employees

SQL > desc departments

SQL > select department_id, department_name from departments

SQL > select distinct DEPARTMENT_ID from employees

SQL > select last_name, salary*12* (1+commission_pct) total_salary, department_id from employees

SQL > select first_name | |','| | last_name from employees

SQL > select first_name | |','| | last_name fullname from employees

Exercise:

Output the following employee information:

Eleni (first_name) Zlotkey (last_name) employeeid is... At department.. Total salary is...

4. Use hyphens to construct statements:

SQL > select table_name from user_tables

SQL > select 'grant select on hr.' | | table_name | |' to scott;' from user_tables

SQL > spool / home/oracle/grant.sql

SQL > set head off remove title

SQL > set feed off removal feedback

SQL > select 'grant select on hr.' | | table_name | |' to scott;' from user_tables

SQL > spool off

$vi / home/oracle/grant.sql manually removes useless lines, which is equivalent to removing headings and feedback

SQL > @ / home/oracle/grant.sql executes the sql script

5. Handling of single quotation marks:

SQL > select'Ischemm teaher' from dual

ERROR:

ORA-01756: quoted string not properly terminated

SQL > select'Illumination teaher' from dual

SQL > select q' {Ihumm teaher} 'from dual; [] () is fine.

Where and orderby

Numeric condition:

SQL > select salary from employees where employee_id=100

The string is case sensitive:

SQL > select last_name, salary from employees where last_name='King'

SQL > select table_name, tablespace_name from user_tables where table_name='EMPLOYEES'

The date is format sensitive:

SQL > alter session set nls_date_format='RR-Mon-dd'

SQL > select last_name from employees where hire_date='2006-05-23'

SQL > select last_name from employees where hire_date=to_date ('2006-05-23,' yyyy-mm-dd')

Interval query:

SQL > select last_name from employees where salary > = 3400 and salary select last_name from employees where salary between 3400 and 4000

SQL > select last_name from employees where salary between 3000 and 5000 and department_id=50

In:

SQL > select last_name from employees where department_id=30 or department_id=40 or department_id=50

SQL > select last_name from employees where department_id in (30,40,50)

Wildcard characters:

SQL > select last_name, job_id from employees where job_id like'%\ _ MAN' escape'\'; prohibit the special meaning of the symbol after the escape character. Like _ matches a certain character, and like% matches some characters.

Null as a condition:

SQL > select last_name from employees where commission_pct is null

SQL > select last_name from employees where commission_pct is not null

And/or/not:

SQL > select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary > = 8000

SQL > select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary > = 8000

Sort:

SQL > select last_name, salary from employees order by salary; ascending order

SQL > select last_name, salary from employees order by salary desc; descending

SQL > select last_name, salary from employees order by last_name

SQL > select last_name, hire_date from employees order by hire_date

SQL > select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc

SQL > select last_name, salary*12* (1+commission_pct) from employees order by 2

SQL > select last_name, salary*12* (1+commission_pct) total_salary from employees order by total_salary

Exercise:

Select employees in department 30

List the names, numbers and departments of all employees (CLERK)

Identify employees whose salary is greater than 5000

Find employees whose bonus is higher than 0.1

Find out the details of all employees in department 50 and managers in department 30

Find out the different job positions of the employees who receive bonuses. Each position is displayed once.

Identify employees who do not receive bonuses or receive wages of less than 5000

Displays the names of employees in last_name without'R'

Select last_name name from employees where not last_name like'% R%'

Displays the names, jobs and salaries of all employees, sorted in descending order of work, while jobs are sorted in ascending order of salary at the same time

One-line function

SQL > select upper (first_name), lower (last_name), length (last_name) from employees

SQL > select (sysdate-hire_date) / 7 from employees

SQL > select trunc ((sysdate-hire_date) / 30,0) from employees

SQL > select trunc (months_between (sysdate,hire_date), 0) from employees

SQL > select sysdate+3650 from dual

SQL > select add_months (sysdate, 120) from dual

SQL > select next_day ('2015-09-01,' friday') from dual

SQL > select next_day ('2015-10-01, 6) from dual

SQL > select last_day (sysdate) from dual

SQL > select round (to_date ('2015-10-10 mm from dual'), 'MONTH') from dual

SQL > select round (to_date ('2015-10-16mm MONTH') from dual

SQL > select round (to_date ('2015-10-10 mm from dual'), 'YEAR') from dual

SQL > select round (sysdate, 'DAY') from dual

Exercise:

Find out all employees employed in the last three days of each month

Extract (month from hire_date+4)! = extract (month from hire_date)

Identify employees who were employed 25 years ago

Months_between (sysdate, hire_date) / 300 > = 25

Displays the name of the employee that is exactly 6 characters

Length (last_name) = 6

Displays the first three characters of the names of all employees

Substr (last_name, 1,3)

Display the names of all employees and replace all'A 'with a

Replace (last_name,'A','a')

Type conversion and other functions

SQL > select to_char (salary,'$999999.00') from employees

SQL > select last_name, to_char (hire_date, 'dd-Mon-RR') from employees

SQL > select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') from dual

SQL > select to_char (sysdate, 'yyyy-mm-dd hh:mi:ss AM') from dual

SQL > select last_name from employees where hire_date=to_date ('2006-05-23,' yyyy-mm-dd')

SQL > select to_number ('$123456.78,'$999999.00') from dual

Exercise:

Enquire about the new employees in 2006:

Select last_name

From employees

Where hire_date between to_date ('2006-01-01-01,' yyyy-mm-dd')

And to_date ('2006-12-31,' yyyy-mm-dd')

Select last_name

From employees

Where to_char (hire_date, 'yyyy') =' 2006'

Select last_name

From employees

Where extract (year from hire_date) = 2006

-- not recommended

Select last_name

From employees

Where hire_date like '2006%'

To enquire about the employees who joined the staff in September of the year:

Select last_name

From employees

Where to_char (hire_date, 'mm') =' 09'

Select last_name from employees where extract (month from hire_date) = 9

Other functions:

Nvl:

Nvl (val1, val2)

If val1 is not null

Then

Return val1

Else

Return val2

SQL > select last_name, salary*12* (1+nvl (commission_pct, 0)) total_salary from employees

Exercise:

Show all employee department numbers, no department display "unassigned department"

Select nvl (to_char (department_id), 'unassigned department') from employees

Case and decode:

IT_PROG + 1000

SA_REP+1500

ST_CLERK + 2000

Other people's wages remain the same.

Select salary+1000 from employees where job_id='IT_PROG'

Select last_name, job_id, salary

Case job_id

When 'IT_PROG' then salary+1000

When 'SA_REP' then salary+1500

When 'ST_CLERK' then salary+2000

Else salary

End new_salary

From employees

Select last_name, job_id, salary

Decode (job_id

'IT_PROG', salary+1000

'SA_REP', salary+1500

'ST_CLERK', salary+2000

Salary) new_salary

From employees

Exercise:

The grading of employees according to their wages shows:

A 20001-25000

B 15001-20000

C 10001-15000

D 5001-10000

E 0-5000

Answer:

Select last_name,salary

Decode (trunc (salary/5000,0))

0thecontrol

1Magnesia D'

2 cynical C'

3. Recorder B'

4 cynical A'

Salary) n_sal

From employees

The answer is expanded:

Select last_name,salary

Decode (trunc (salary/5000,0))

0thecontrol

1Magnesia D'

2 cynical C'

3. Recorder B'

(4) n_sal

From employees

Order by salary desc,n_sal

Grouping function

SQL > select count (*), sum (salary), avg (salary), min (salary), max (salary) from employees

SQL > create table T1 (x int)

SQL > insert into T1 values (null)

SQL > insert into T1 values (1)

SQL > commit

SQL > select count (*) from T1

SQL > select count (x) from T1

SQL > select max (x) from T1

SQL > select min (x) from T1

SQL > select sum (x) from T1

SQL > select avg (x) from T1

SQL > select avg (salary), avg (nvl (commission_pct, 0)) from employees

SQL > select count (distinct department_id) from employees; removes duplicate values

Groupby grouping:

SQL > select department_id, avg (salary) from employees group by department_id

Multiple column grouping:

SQL > select department_id, job_id, max (salary) from employees group by department_id, job_id

SQL > select department_id, job_id, max (salary), last_name from employees group by department_id, job_id; error syntax

Exercise:

The number of different positions in the company

Select job_id,count (job_id)

From employees

Group by job_id

Calculate the number of people in each department

Select department_id,count (last_name)

From employees

Group by department_id

Select department_id,count (department_id)

From employees

Group by department_id

Group by year and calculate the sum of employees' wages

Select to_char (hire_date,'yyyy') year,sum (salary)

From employees

Group by to_char (hire_date,'yyyy')

Order by year

Selec extract (year from hire_date) year,sum (salary)

From employees

Group by extract (year from hire_date)

Order by year

Having statement:

SQL > select department_id, avg (salary) from employees where avg (salary) > = 5000 group by department_id; error statement

SQL > select department_id, avg (salary) from employees group by department_id having avg (salary) > = 5000

Exercise:

The average salary of all ordinary employees in a department is calculated by department. the average salary of less than 5000 in the department is not displayed, and the final result is arranged in descending order.

Select department_id,trunc (avg (salary)) avg_sal

From employees

Where department_id is not null

Group by department_id

Having trunc (avg (salary)) > = 5000

Order by avg_sal desc

Select department_id, avg (salary) avg_sal

From employees

Where job_id not like'%\ _ MGR' escape'\ 'and department_id is not null

Group by department_id

Having avg (salary) > = 5000

Order by avg_sal desc

Multi-table connection

Emp: dept:

Empno ename deptno deptno dname

100 abc 10 10 sales

101 def 10 20 market

102 xyz 20 30 it

103 opq null

For emp in 100.. one hundred and three

For dept in 10.. thirty

Emp.deptno=dept.deptno

100 abc 10 10 sales

101 def 10 10 sales

102 xyz 20 20 market

Order form:

CustID StoreID ProdID ChannelID

100 S100 P100 C100

Customer list:

CustID name creditlevel

100 abc

Address table:

CustID adress

100 bj

100 tj

Get the following information to prepare for the work:

Employees:

Total number of employees: 107

SQL > select count (*) from employees

Number of employees in a department: 106

SQL > select count (*) from employees where department_id is not null

SQL > select count (department_id) from employees

Number of employees without department: 1

SQL > select count (*) from employees where department_id is null

Departments:

Total number of departments: 27

SQL > select count (*) from departments

Number of departments with employees: 11

SQL > select count (distinct department_id) from employees

Number of departments without employees: 16

SQL > select count (*) from departments where department_id not in (select department_id from employees where department_id is not null)

For dept in 1..27

For emp in 1..107

Dept.deptid does not appear in the emp table

Select count (*)

From employees e, departments d

Where e.department_id (+) = d.department_id

And e.employee_id is null

Select count (*)

From departments d

Where not exists

(select 1 from employees where department_id=d.department_id)

Select (select count (*) from departments)-(select count (distinct department_id) from employees) from dual

Internal connection: 106 (106,11)

Select e.last_name, d.department_name

From employees e, departments d

Where e.department_id=d.department_id

Select e.last_name, d.department_name

From employees e join departments d on e.department_id=d.department_id

Left outer connection: 107 (106 / 1)

Select e.last_name, d.department_name

From employees e, departments d

Where e.department_id=d.department_id (+)

Select e.last_name, d.department_name

From departments d, employees e

Where e.department_id=d.department_id (+)

Select e.last_name, d.department_name

From employees e left outer join departments d

On e.department_id=d.department_id

Right outer connection: 122 (106 / 16)

Select e.last_name, d.department_name

From employees e, departments d

Where e.department_id (+) = d.department_id

Select e.last_name, d.department_name

From employees e right outer join departments d

On e.department_id=d.department_id

Full external connection: 123 (106 / 1 / 16)

Select e.last_name, d.department_name

From employees e full outer join departments d

On e.department_id=d.department_id

Extension of multi-table join:

N table connections:

Select e.last_name, d.department_name, l.city

From employees e, departments d, locations l

Where e.department_id=d.department_id

And d.location_id=l.location_id

Select e.last_name, d.department_name, l.city

From employees e join departments d on e.department_id=d.department_id

Join locations l on d.location_id=l.location_id

Select e.last_name, d.department_name, l.city

From employees e, departments d, locations l

Where e.department_id=d.department_id (+)

And d.location_id=l.location_id (+)

Select e.last_name, d.department_name, l.city

From employees e left outer join departments d on e.department_id=d.department_id

Left outer join locations l on d.location_id=l.location_id

Exercise:

Query all employee names, department names, department city (city), country (country) and region (region) names, and replace null values with "none". (NCMA)

(use the syntax of oracle and sql99)

Select e.last_name, d.department_name, l.city, c.country_name, r.region_name

From employees e, departments d, locations l, countries c, regions r

Where e.department_id=d.department_id (+)

And d.location_id=l.location_id (+)

And l.country_id=c.country_id (+)

And c.region_id=r.region_id (+)

Select e.last_namee.last_name, d.department_name, l.city, c.country_name, r.region_name

From employees e

Left outer join departments d on e.department_id=d.department_id

Left outer join locations l on d.location_id=l.location_id

Left outer join countries c on l.country_id=c.country_id

Left outer join regions r on c.region_id=r.region_id

Self-connect:

Empid ename mgrid

100 abc

101 def 100

102 xyz 100

Emp: mgr:

Empid ename mgrid empid mgrname

100 abc 100 abc

101 def 100

102 xyz 100

101 def 100 100 abc

102 xyz 100 100 abc

Select emp.ename, mgr.mgrname

From emp, mgr

Where emp.mgrid=mgr.empid

Emp: mgr:

Empid ename mgrid empid ename mgrid

100 abc 100 abc

101 def 100 101 def 100

102 xyz 100 102 xyz 100

Select e.last_name, m.last_name

From employees e, employees m

Where e.manager_id=m.employee_id

Number of employees with managers: 106

SQL > select count (*) from employees where manager_id is not null

Number of employees without a manager: 1

SQL > select count (*) from employees where manager_id is null

Exercise:

Displays the names of all employees and managers, with no manager showing "none".

Select e.last_name, nvl (m.last_name, 'Ngamma A')

From employees e, employees m

Where e.manager_id=m.employee_id (+)

Unequal connection:

Conn scott/tiger

Select e.ename, sg.grade

From emp e, salgrade sg

Where e.sal between sg.losal and sg.hisal

Exercise:

Find out the name of the employee whose salary is higher than the average salary of his department.

Create table avg_sal_dept as select department_id, avg (salary) avg_sal from employees where department_id is not null group by department_id

Select e.last_name, e.salary, asd.avg_sal

From employees e, avg_sal_dept asd

Where e.department_id=asd.department_id

And e.salary > asd.avg_sal

Select e.last_name, e.salary, asd.avg_sal

From employees e, (select department_id, avg (salary) avg_sal from employees where department_id is not null group by department_id) asd

Where e.department_id=asd.department_id

And e.salary > asd.avg_sal

Subquery

The idea of a single-line subquery:

SQL > select salary from employees where last_name='Feeney'

SQL > select last_name from employees where salary > 3000

SQL > select last_name from employees where salary > (select salary from employees where last_name='Feeney')

The idea of multi-line subquery:

SQL > select distinct department_id from employees where department_id is not null

SQL > select department_name from departments where department_id in (10,200.30)

SQL > select department_name from departments where department_id in (select department_id from employees where department_id is not null)

Rewrite with multi-table join:

Select distinct d.department_name

From employees e, departments d

Where e.department_id=d.department_id

For dept in 1..27

For emp in 1..107

Check to see if deptid appears in emp

Exercise:

The name of the employee whose salary is higher than the average salary of the whole company.

SQL > select last_name from employees where salary > (select avg (salary) from employees)

Name of the employee who joined Feeney in the same year

Select last_name, hire_date

From employees

Where extract (year from hire_date) =

(select extract (year from hire_date) from employees where last_name='Feeney')

And last_name! = 'Feeney'

Select last_name, hire_date

From employees

Where hire_date between

(select to_date (to_char (hire_date, 'yyyy') | |' 0101, 'yyyymmdd') from employees where last_name='Feeney')

And

(select to_date (to_char (hire_date, 'yyyy') | |' 1231, 'yyyymmdd') from employees where last_name='Feeney')

Names of all employees working at Seattle

Select last_name

From employees

Where department_id in

(select department_id from departments

Where location_id=

(select location_id from locations where city='Seattle'))

Find the name of an employee who meets the following criteria: in the same department as Abel, the salary is higher than Olson

Select last_name from employees

Where department_id=

(select department_id from employees where last_name='Abel')

And salary >

(select salary from employees where last_name='Olson')

Pairing subquery:

Names of employees in the same department and in the same position as Feeney:

Select last_name, department_id, job_id

From employees

Where department_id=

(select department_id from employees where last_name='Feeney')

And job_id=

(select job_id from employees where last_name='Feeney')

And last_name! = 'Feeney'

Select last_name, department_id, job_id

From employees

Where (department_id, job_id) =

(select department_id, job_id from employees where last_name='Feeney')

And last_name! = 'Feeney'

In and notin are affected by null values:

Names of all managers:

SQL > select last_name from employees where employee_id in (select manager_id from employees)

Names of all ordinary employees:

SQL > select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null)

Associated subquery:

The name of the employee whose salary is higher than the average salary of his department.

For i in 1.. 107 all employees

{

Select avg (salary) from employees where department_id=i.department_id

If i.salary > I the average salary of the department

Keep this record

}

Select last_name,salary,department_id

From employees outer

Where salary >

(select avg (salary) from employees

Where department_id = outer.department_id)

Order by department_id

Select e.last_name, e.salary, asd.avg_sal

From employees e, (select department_id, avg (salary) avg_sal from employees where department_id is not null group by department_id) asd

Where e.department_id=asd.department_id

And e.salary > asd.avg_sal

Exists/not exists query:

For i in 1.. 27 all departments

{

All employees of forjin1..107

{

If i.department_id = j.department_id

Keep this record

Break

}

}

Select department_name

From departments outer

Where exists

(select 1 from employees where department_id=outer.department_id)

Select department_name

From departments outer

Where not exists

(select 1 from employees where department_id=outer.department_id)

Exercise:

Names of all managers:

For i in 1.. 107 all employees

{

All employees of forjin1..107

{

If i.employee_id = j.manager_id

Keep this record

Break

}

}

Select last_name

From employees outer

Where exists

(select 1 from employees where manager_id=outer.employee_id)

Select last_name

From employees

Where employee_id in

(select manager_id from employees)

Select last_name

From employees, (select distinct (manager_id) from employees) asd

Where employee_id=asd.manager_id

Names of all ordinary employees:

Select last_name

From employees outer

Where not exists

(select 1 from employees where manager_id=outer.employee_id)

Select last_name

From employees

Where employee_id not in (select manager_id from employees where manager_id is not null)

Transformation of subqueries and multi-table joins:

The name of the department with employees

Select department_name

From departments

Where department_id in

(select department_id from employees)

Select department_name

From departments outer

Where exists

(select 1 from employees where department_id=outer.department_id)

Select distinct d.department_name

From employees e, departments d

Where e.department_id=d.department_id

Exercise:

Names of all employees working in Seattle (using subqueries and multi-table joins)

Select last_name

From employees

Where department_id in

(select department_id from departments

Where location_id=

(select location_id from locations where city='Seattle'))

Select e.last_name

From employees e, departments d, locations l

Where e.department_id=d.department_id

And d.location_id=l.location_id

And l.cityboy Seattle

Maximum query:

SQL > select last_name from employees where salary= (select max (salary) from employees)

Top-N query:

SQL > select last_name, salary from employees where rownum select * from (select last_name, salary from employees order by salary desc) where rownum select * from

(select * from

(select * from

(select last_name, salary from employees order by salary desc)

Where rownum create table T1 (x int, y char (1), z date)

SQL > insert into T1 (x, y, z) values (1,'a, sysdate)

SQL > insert into T1 (x, z, y) values (2, sysdate+1,'b')

SQL > insert into T1 (x, y, z) values (1, null, sysdate)

SQL > insert into T1 (x, z) values (2, sysdate+1)

SQL > insert into T1 values (1, null, sysdate)

SQL > create table my_emp as select * from employees

SQL > create table my_emp as select last_name, salary from employees where department_id=50

SQL > create table avg_sal as select department_id, avg (salary) avg_sal from employees group by department_id

SQL > create table my_emp as select * from employees where 1: 0

SQL > insert into my_emp select * from employees

Update:

SQL > update my_emp set salary=salary*1.1

SQL > update my_emp set salary=salary*1.1 where department_id=50

SQL > update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197

Delete:

SQL > delete from my_emp where employee_id=197

SQL > delete from my_emp where department_id=50

SQL > delete from my_emp

Subquery:

SQL > create table my_emp as select * from employees

SQL > alter table my_emp add (department_name varchar2 (30))

SQL > update my_emp outer set department_name= (select department_name from departments where department_id=outer.department_id)

Update (select t1.department_name as aname,t2.department_name bname from my_emp T1, departments T2 where t1.department_id=t2.department_id) set aname=bname

Exercise:

Delete departments without employees in the new_ dept table

SQL > create table my_dept as select * from departments

Delete from my_dept outer

Where not exists

(select 1 from my_emp

Where department_id=outer.department_id)

Delete and truncate:

Delete truncate

Statement type dml ddl

Undo data produces a large amount of undo data, not undo data.

Space management does not release release

Syntax where deletes all data

DDL

String:

SQL > create table T1 (x char (10), y varchar2 (10))

SQL > insert into T1 values ('x','y')

SQL > select dump (x), dump (y) from T1

Numerical value:

SQL > create table T1 (x number (5 number 2), y number (5))

SQL > insert into T1 values (123.45, 12345)

SQL > insert into T1 values (12.345, 12345)

SQL > insert into T1 values (12.345, 123.45)

SQL > select * from T1

SQL > insert into T1 values (12.345, 112345)

Date and time:

SQL > create table T1 (a date, b timestamp, c timestamp with time zone, d timestamp with local time zone)

SQL > insert into T1 values (sysdate, systimestamp, systimestamp, systimestamp)

SQL > alter session set time_zone='+9:00'

SQL > select * from T1

Modify the table structure:

SQL > alter table T1 add (e char (10))

SQL > alter table T1 drop (e)

SQL > alter table T1 modify (d not null)

Constraints:

Field (column): not null, check (salary > 0)

Line and line: primary key, unique

Between tables: foreign key

Create table dept (

Deptno int constraint dept_deptno_pk primary key

Dname varchar2 (20) constraint dept_dname_nn not null)

Create table emp (

Empno int constraint emp_empno_pk primary key

Ename varchar2 (20) constraint emp_ename_nn not null

Email varchar2 (50) constraint emp_email_uq unique

Salary int constraint emp_salary_ck check (salary > 0)

Deptno int constraint emp_deptno_fk references dept (deptno))

SQL > select constraint_name, constraint_type from user_constraints where table_name in ('DEPT',' EMP')

SQL > insert into emp values (100,100 'abc',' abc@123.com', 10000, 10)

Insert into emp values (100,100, 'abc',' abc@123.com', 10000, 10)

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.EMP_DEPTNO_FK) violated-parent key not

Found

SQL > insert into dept values (10, 'sales')

1 row created.

SQL > insert into dept values (10, 'market')

Insert into dept values (10, 'market')

*

ERROR at line 1:

ORA-00001: unique constraint (HR.DEPT_DEPTNO_PK) violated

SQL > insert into dept values (20, 'market')

1 row created.

SQL > commit

Commit complete.

SQL > insert into emp values (101,101,' def', 'def@123.com', 10000, 20)

Create table emp (

Empno int constraint emp_empno_pk primary key

Ename varchar2 (20) constraint emp_ename_nn not null

Email varchar2 (50) constraint emp_email_uq unique

Salary int constraint emp_salary_ck check (salary > 0)

Deptno int constraint emp_deptno_fk references dept (deptno) on delete set null) or on delete cascade

Instead of trigger View trigger

Sequence:

SQL > create sequence test_seq increment by 1 start with 1 maxvalue 1000 nocycle cache 20

SQL > create table T1 (x int primary key, y int)

SQL > insert into T1 values (test_seq.nextval, 11); execute repeatedly

SQL > select * from T1

Index:

Primary keys and uniqueness constraints automatically create indexes:

SQL > select constraint_name, constraint_type from user_constraints where table_name='EMPLOYEES'

SQL > select index_name, index_type from user_indexes where table_name='EMPLOYEES'

SQL > set autot on

SQL > select last_name from employees where employee_id=100; walk the index

SQL > select email from employees; walk the index

SQL > select last_name from employees where salary=2100; full table scan

SQL > create index emp_salary_ix on employees (salary)

SQL > select last_name from employees where salary=2100; walk the index

SQL > set autot off

PL/SQL

$vi $ORACLE_HOME/sqlplus/admin/glogin.sql

Set serveroutput on

Vernier

General explicit Vernier exercise:

Specify the job number of the employee

If it is higher than or equal to the average salary of your department, output first_name (column) last_name 's salary: (show employee's salary) higherorequal than avgrage salary of department department_name (column): (show department salary).

If it is lower than the average salary of your department, output first_name last_name's salary lowerer than avgrage salary of department department_name.

If the employee does not belong to any department, output first_name last_name nodepartment!

DECLARE

V_empno employees.employee_id%type: = 100

V_emp_rec employees%rowtype

V_avg_sal employees.salary%type

V_department_name departments.department_name%type

BEGIN

Select *

Into v_emp_rec

From employees

Where employee_id=v_empno

Select avg (salary)

Into v_avg_sal

From employees

Where department_id=v_emp_rec.department_id

Select department_name

Into v_department_name

From departments

Where department_id=v_emp_rec.department_id

If v_emp_rec.salary > = v_avg_sal

Then

Dbms_output.put_line (v_emp_rec.first_name | |','| | v_emp_rec.last_name)

End if

END

/

Check the salary for all employees and output the same content as the exercise above.

BEGIN

For v_emp_rec in (select * from employees)

Loop

If v_emp_rec.department_id is null then

Dbms_output...

Else

Select avg (salary)

Into v_avg_sal

From employees

Where department_id=v_emp_rec.department_id

Select department_name

Into v_department_name

From departments

Where department_id=v_emp_rec.department_id

If...

End loop

END

DECLARE

Cursor emp_cur is

Select e.last_name, e.salary, asd.avg_sal

From employees e, (select department_id, avg (salary) avg_sal from employees where department_id is not null group by department_id) asd

Where e.department_id=asd.department_id

BEGIN

For v_emp_rec in emp_cur

Loop

If v_emp_rec.salary > = v_emp_rec.avg_sal then

Elsif

End loop

Parameter cursor exercise:

Specify the department number, showing the top 3 with the highest salary in the department (all less than 3). Output: Department number, name, salary.

DECLARE

Cursor emp_sal_cur (p_department_id number) is

Select department_id, last_name, salary

From employees

Where department_id=p_department_id

Order by salary desc

BEGIN

For v_emp_rec in emp_sal_cur (10)

Loop

Exit with emp_sal_cur%rowcount > 3

Dbms_output...

End loop

END

DECLARE

Cursor emp_sal_cur (p_department_id number) is

Select * from (select department_id, last_name, salary

From employees

Where department_id=p_department_id

Order by salary desc) where rownum3

Dbms_output...

End loop

End loop

END

DECLARE

Cursor emp_sal_cur (p_department_id number) is

Select * from (select department_id, last_name, salary

From employees

Where department_id=p_department_id

Order by salary desc) where rownum create table new_emp as select * from employees

DECLARE

V_employee_id...

V_email...

BEGIN

Update new_emp set email=v_email

Where first_name=v_first_name and last_name=...

If SQL%notfound then

Insert into new_emp values ()

End if

END

Abnormal

Predefined exception activity:

Enter the employee's job number and return the employee's name and salary. For inaccurate work numbers, the prompt message "the work number does not exist" is given.

BEGIN

Select first_name, last_name, salary

Into v_first_name, v_last_name, v_salary

From employees

Where employee_id=&emp_id

Dbms_output.put_line (…)

EXCEPTION

WHEN no_data_found THEN

Dbms_output...

WHENothers THEN

Dbms_output...

END

Non-predefined exception activity:

Add new employee information to new_emp for duplicate job ID, missing name, salary create table new_emp as select * from employees

SQL > alter table new_emp modify (employee_id primary key)

SQL > alter table new_emp modify (check (salary > 0))

SQL > alter table new_emp modify (email null)

SQL > alter table new_emp modify (hire_date null)

SQL > alter table new_emp modify (job_id null)

Insert into new_emp...

Declare

E_name exception

E_salary exception

Pragma exception_init (eBay name, Mei Yu 2292)

Begin

Insert into new_emp values ()

Exception

When DUP_VAL_ON_INDEX then

Dbms_output...

When e_name then

Dbms_output...

When e_salary then

Dbms_output...

When others then

... .

End

Custom exception exercise:

Modify the employees in the new_ emptable, assign the job number, and modify the salary of the employees. For the revised salary show sga, the job number does not exist.

SQL > show parameter sga_max_size

SQL > select * from V$SGAINFO

Oem: server in browser-- > memory guide (https://192.168.0.1:1158/em)

Sharedpool:

SQL > show parameter shared_pool_size

SQL > select component, current_size from V$SGA_DYNAMIC_COMPONENTS where component='shared pool'

SQL > select * from v$SGAINFO

Db buffer cache:

SQL > show parameter db_block_size

SQL > show parameter db_cache_size

SQL > select * from v$SGAINFO

SQL > select component, current_size from V$SGA_DYNAMIC_COMPONENTS where component='DEFAULT buffer cache'

Redo log buffer:

SQL > show parameter log_buffer

SQL > select * from v$sgainfo

PGA:

SQL > show parameter pga_aggregate_target

SQL > select * from V$PGASTAT where NAME='total PGA allocated'

Background process:

SQL > select name from v$bgprocess where paddr'00'

SQL >! Ps-ef | grep ora_

SQL > show parameter db_writer_processes

Data file:

$ll / u01/app/oracle/oradata/orcl/*.dbf

SQL > select name from v$datafile

SQL > select name from v$tempfile

Control file:

$find / U01-name 'control0 [12] .ctl'

SQL > select name from v$controlfile

SQL > select TYPE, RECORD_SIZE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section

Redo log:

$ll / u01/app/oracle/oradata/orcl/*.log

SQL > select group#, member from v$logfile

Parameter file:

$ll $ORACLE_HOME/dbs/spfile*.ora

$strings $ORACLE_HOME/dbs/spfileorcl.ora

SQL > show parameter spfile

SQL > show parameter

Password file:

$ll $ORACLE_HOME/dbs/orapw*

Archive logs:

SQL > select name from v$archived_log

Warning log:

$find / U01-name 'alert_*.log'

Instance management monitoring

Start monitoring:

$netstat-tlnp | grep 1521

$lsnrctl

LSNRCTL > help

$lsnrctl status

$lsnrctl stop

$netstat-tln | grep 1521

Fast dynamic registration: (optional)

$sqlplus / as sysdba

SQL > alter system register

$lsnrctl status

Em

Start EM:

$netstat-tlnp | grep 1158

$echo $ORACLE_SID

Orcl

$echo $ORACLE_UNQNAME

Orcl

If ORACLE_UNQNAME is not set:

$export ORACLE_UNQNAME=orcl

Or

$vi .bash _ profile

Export ORACLE_UNQNAME=orcl

$. .bash _ profile

Ensure that the hostname is resolved properly:

$cat / etc/hosts

$emctl status dbconsole

$emctl stop dbconsole

$emctl start dbconsole

View the port of oem:

$find / U01-name portlist.ini

Reconfigure em:

$emca

$emca-deconfig dbcontrol db

$emca-config dbcontrol db

Database

Start the instance:

$ls $ORACLE_HOME/dbs/spfileorcl.ora

SQL > shutdown immediate

SQL > startup nomount

SQL > select status from v$instance

SQL > select * from v$sgainfo

SQL > select * from hr.employees

SQL > conn hr/hr

SQL > startup mount or alter database mount

SQL > show parameter control_files

SQL > select status from v$instance

SQL > select * from v$controlfile

SQL > select name from v$datafile

SQL > select * from hr.employees

SQL > conn hr/hr

SQL > startup or alter database open

SQL > select status from v$instance

SQL > select * from hr.employees

SQL > conn hr/hr

Shut down the database:

SQL > shutdown normal

SQL > shutdown transactional

SQL > shutdown immediate

SQL > shutdown abort

Control script:

$vi / etc/oratab

$vi $ORACLE_HOME/bin/dbstart

$vi $ORACLE_HOME/bin/dbshut

Restart the database:

SQL > startup force

Startup force = shutdown abort + startup

Startup force mount = shutdown abort + startup mount

Exercise:

Be familiar with the startup and shutdown of three services

Parameter management

Parameter file:

$ls $ORACLE_HOME/dbs/spfileorcl.ora

Pre-backup:

$cp spfileorcl.ora spfileorcl.ora.bak

Cannot be modified directly through vi!

View parameters:

SQL > show parameter

SQL > select NAME, VALUE from v$parameter

Modify the parameters:

Dynamic parameters:

$strings $ORACLE_HOME/dbs/spfileorcl.ora | grep reource_limit

SQL > show parameter RESOURCE_LIMIT

SQL > alter system set RESOURCE_LIMIT=true

SQL > show parameter RESOURCE_LIMIT

$strings $ORACLE_HOME/dbs/spfileorcl.ora | grep reource_limit

Static parameters:

$strings $ORACLE_HOME/dbs/spfileorcl.ora | grep processes

* .processes=150

The value of SQL > show parameter processes is 150

SQL > alter system set processes=300 scope=spfile

The value of SQL > show parameter processes is 150

$cat $ORACLE_HOME/dbs/spfileorcl.ora

* .processes=300

Restart the database, and the static parameter modification takes effect:

SQL > shutdown immediate

SQL > startup

SQL > show parameter processes value is 300

Modify parameters at session level:

SQL > select sysdate from dual

SQL > alter session set nls_date_format='dd-mon rr'

SQL > select sysdate from dual; only affects the current session, not the system

SQL > conn / as sysdba

SQL > select sysdate from dual; restore default settings

Pfile/spfile format conversion:

SQL > create pfile='/home/oracle/initorcl.ora' from spfile

$cat / home/oracle/initorcl.ora

$mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak

SQL > shutdown immediate

SQL > create spfile from pfile='/home/oracle/initorcl.ora'

$strings $ORACLE_HOME/dbs/spfileorcl.ora

Specify the parameter file to start the database:

SQL > startup pfile='/home/oracle/initorcl.ora'

Exercise:

Modify the resource_limit and processes parameters. And find the corresponding information in alertlog.

Warning logs and dump files for background processes

$find / U01-name alert_*.log

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

SQL > show parameter dump

Exercise:

View the details of the most recent startup / shutdown of the instance in alertlog.

View the changes made to the parameters by the users recorded in alertlog.

Data dictionary / dynamic performance view

SQL > conn hr/hr

SQL > desc user_tables

SQL > select TABLE_NAME from user_tables

SQL > desc user_views

SQL > select VIEW_NAME from user_views

SQL > desc user_indexes

SQL > select INDEX_NAME, TABLE_NAME from user_indexes

SQL > conn scott/tiger

SQL > select TABLE_NAME from user_tables

SQL > select VIEW_NAME from user_views

SQL > select INDEX_NAME, TABLE_NAME from user_indexes

SQL > conn hr/hr

SQL > select count (*) from all_tables

SQL > conn scott/tiger

SQL > select count (*) from all_tables

SQL > conn hr/hr

SQL > select count (*) from dba_tables

SQL > conn scott/tiger

SQL > select count (*) from dba_tables

SQL > desc v$instance

SQL > desc v$database

Storage management

Query predefined tablespaces:

SQL > select TABLESPACE_NAME,CONTENTS from dba_tablespaces

SQL > select FILE_NAME, TABLESPACE_NAME from dba_data_files

Create a new tablespace:

SQL > create tablespace tbs01 datafile'/ u01/app/oracle/oradata/orcl/tbs01.dbf'SIZE 10MAUTOEXTEND ON NEXT 10m MAXSIZE 100m

Create a table in the specified tablespace:

SQL > create table T1 tablespace tbs01 as select * from dba_objects where 1: 0

SQL > select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1'

SQL > insert into T1 select * from dba_objects

SQL > select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1'

SQL > select extent_id, bytes, blocks from dba_extents where segment_name='T1'

SQL > insert into T1 select * from T1

SQL > insert into T1 select * from T1

SQL > insert into T1 select * from T1

SQL > select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1'

SQL > select extent_id, bytes, blocks from dba_extents where segment_name='T1'

SQL > insert into T1 select * from T1; insufficient space, error reported

SQL > rollback

SQL > select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1'; space is not released

SQL > alter table T1 move; frees up space

Read-only tablespace:

SQL > alter tablespace tbs01 read only

SQL > delete T1; disable dml

SQL > insert into T1 select * from T1; disable dml

SQL > create table T2 (x int) tablespace tbs01; failed

SQL > alter table T1 add (x int); successful

SQL > update T1 set xchang1; failed

SQL > drop table T1; successful

The difference between dml and ddl

Change the tablespace size:

Resize,autoextend,adddatafile

Delete tablespaces:

SQL > drop tablespace tbs01 including contents and datafile

Oracle NET

1. The client uses the name @ ora10g to go to the tnsname.ora file to get the specific connection information of the server.

two。 The client sends a link request to the server through the description in tnsname.ora

3. After receiving the connection request, the listener of the server verifies the validity of the requested service

4. The server generates a service process to establish a connection with the client process.

View the session establishment process:

$netstat-tlnp | grep 1521

$sqlplus sys/password@orcl as sysdba

$netstat-tnp | grep sqlplus

$kill-9 1234 kills the process of maintaining sqlplus

Configuration of monitoring

Configuration file:

$vi$ORACLE_HOME/network/admin/listener.ora

Add a new listening service Listener15210 through netca, and the port uses 15210

$vi listener.ora

$netstat-tln | grep 1521

$lsnrctl status listener15210

Configure advanced options through netmgr

Start / stop / view / reload listeners / services through the lsnrctl command

Lsnrctl start | stop | status | reload | service

Specify the name of the monitor:

$lsnrctl status listener15210

Listener.ora and / etc/hosts files need to be checked for changes in the network environment

Netca Delete Listener15210

Configuration of instance

Static and dynamic registration

What is static registration?

That is, the configuration file of the listener specifies which instance needs to be configured with SID_DESC field.

You can use SID_NAME or SERVICE_NAME to locate an instance.

What is dynamic registration?

That is, which instance is not specified in the listener's configuration file

To tell the listener the specific instance to listen to through PMON

PMON tells the SERVER_NAME to the listener, and the process is to register.

By default, PMON registers once a minute, which means you cannot connect when you start monitoring and do not register.

Add 3 ways, delete the following, and rewrite it with the document

Distinguish between static registration and dynamic registration

Lsnrctl status

It's ready. It's dynamic.

It's unknow. It's static.

Static registration listener.ora file information:

$vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = ora11g)

)

)

$lsnrctl reload

.

Services Summary...

Service "ora11g" has 1 instance (s).

Instance "ora11g", status UNKNOWN, has 1 handler (s) for this service...

The status is always unknown, and when there is a request, the listener confirms whether the data exists.

Dynamic registration

Oracle9i instances use dynamic service registration to inform listeners about their database services.

Service registration depends on the PMON process registering instance information with the listener at an interval of about 1 minute.

Manually register the command alter system register

There is no need to set any information in the listener.ora file. This file may not exist.

There are three ways to register:

Local default port snooping

Local non-default port snooping

Remote monitoring

Local non-default port:

Netca creates listener15210, using port 15210

$netstat-tlnp | grep 15210

Write to the alias of the monitor:

$cd $ORACLE_HOME/network/admin

$vi tnsnames.ora

Listener15210 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 15210))

)

Modify the parameters:

SQL > ALTER SYSTEM SET LOCAL_LISTENER=listener15210

SQL > ALTER SYSTEM register; register now (optional)

Service information has been added to the new monitor:

$lsnrctl status listener15210

Client specifies a new port connection:

$sqlplus sys/password@192.168.0.1:1521/orcl as sysdba reported an error

$sqlplus sys/password@192.168.0.1:15210/orclas sysdba

Delete the configuration:

SQL > ALTER SYSTEM SET LOCAL_LISTENER=''

SQL > ALTER SYSTEM register

$vi tnsnames.ora removes the listener15210 alias

Netca removes snooping on port 15210

Client configuration

Easy to connect:

$sqlplus sys/password@192.168.0.1:1521/orcl as sysdba

Suitable for temporary connections

Local naming:

View an existing host connection string

$cd $ORACLE_HOME/network/admin

$vi tnsnames.ora

$sqlplus sys/password@orcl as sysdba

Use netca to add a new host connection string orcl192

$sqlplus sys/password@orcl192 as sysdba

SQL > select name from v$database

The tnsping test does not require a username and password:

$tnsping 192.168.0.1:1521/orcl

$tnsping orcl192

The order of the parsing methods:

$vi sqlnet.ora

Netca can be modified

Restore the default settings:

SQL > alter system set local_listener=''

SQL > alter sytem register

Netca removes unwanted monitoring and connection configurations

Shared server mode

Proprietary service and shared service model

Proprietary mode

Establish a connection between each user process and the service process through a listener

Process information is stored in PGA, that is, as many user processes as there are PGA generated

The process connection alone is not enough to operate the database, and the generated session information is also needed.

Session information is stored in UGA, and UGA exists in PGA in proprietary mode

Because processes and processes are isolated from each other, session information is also relatively independent

This results in that the service process can only know the session request information of the current user process and can only serve the current user process.

Sharing mode

The request of the user process is received by the listener, which does not delegate the server process, but returns the scheduler information to the client

The scheduler puts requests from the user process into the request queue

One of the multiple service processes gets the request from the user process from the queue and processes the request from the user process

After processing, the service process puts the processing results into the response queue, and each scheduler has its own response queue.

Feedback the information of the response queue to the corresponding scheduler

The scheduler then returns the result of the service process to the user process.

The session information of the user process in shared mode is visible to each server process.

Because the UGA information of shared mode exists in SGA, the request of one user process can be completed by multiple service processes.

The configuration of the shared server is configured through the initialization parameter dispatchers

Can be modified through DBCA

$lsnrctl service

SQL > show parameter disp

SQL > show parameter shared_server

Proprietary mode and shared mode can be used together.

It depends on how the user chooses.

In tnsnames.ora

ORA10G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = dba.up.com) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED) # if you don't write this value, you will match according to the mode of the server.

# shared specifies to connect by sharing

# DEDICATED specifies to connect in proprietary mode

(SERVICE_NAME = raw10g)

)

)

Test three modes

ORCL_default =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

ORCL_dedicated =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

ORCL_shared =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(CONNECT_DATA =

(SERVER = SHARED)

(SERVICE_NAME = orcl)

)

)

Test:

$sqlplus sys/password@orcl_default as sysdba

$sqlplus sys/password@orcl_dedicated as sysdba

$sqlplus sys/password@orcl_shared as sysdba

SQL > select SID, SERVER,PROGRAM from v$session where USERNAME='SYS'

SQL > select distinct sid from v$mystat

Management and maintenance, import of large amounts of data, backup and recovery are not suitable for sharing:

Cannot issue management commands under SQL > shutdown immediate shared connection

Restore the original settings:

Dbca is modified to dedicated mode

Dblink

Database An accesses the table above remote database B.

In database A, use netca to create the host description string orcl_dblink to point to database B.

$netca

$tnsping orcl_dblink

Create a database link in database A:

SQL > create database link orcl_dblink_hr connect to hr identified by hr using 'orcl_dblink'

Using uses the host description string orcl_dblink

The user and password are on database B.

The name of db link does not need to be consistent with the host description string

Access the table in database B:

SQL > select count (*) from hr.employees@orcl_dblink_hr

SQL > select count (*) from hr.employees, departments@orcl_dblink_hr

User management

AAA:

Authentication: authentication

Authorization: rights management

Audition: audit

Authentication

Predefined system users:

SQL > select USERNAME, ACCOUNT_STATUS from dba_users

Users with open status:

SQL > select USERNAME, ACCOUNT_STATUS from dba_users ACCOUNT_STATUS='OPEN'

System management account:

SYS SYSTEM DBSNMP SYSMAN

There are three authentication methods:

Password authentication:

Create a user user01 in the browser

Or use the command to create:

SQL > create user user01 identified by password

SQL > grant create session to user01

Test:

$sqlplus user01/password

External (os) verification:

Create a user in the operating system:

$su-

Password:

[root@node1 ~] # useradd osuser

[root@node1 ~] # passwd osuser

$sqlplus / as sysdba

External users use a fixed prefix:

SQL > show parameter os_auth

SQL > create user ops$osuser identified externally

SQL > grant create session to ops$osuser

Do not su-osuser, keep the environment variables:

$su osuser

Password:

[osuser@node1 admin] $sqlplus /

SQL > show user

USER is "OPS$OSUSER"

Authentication of the administrator:

Local connection:

Local connection, preset ORACLE_SID, operating system user is a member of the dba group

$id

Uid=1001 (oracle) gid=1000 (oinstall) groups=1000 (oinstall), 1031 (dba), 1032 (oper)

$sqlplus / as sysdba

SQL > show user

USER is "SYS"

$su-

# usermod-G oper oracle or

# gpasswd-d oracle dba

# exit

$sqlplus / as sysdba

Report an error, lack of authority

As long as you are a member of the dba group, you can log in as sqlplus / as sysdba without knowing the sys password.

And the identity is sys.

Restore:

# gpasswd-an oracle dba

Remote client connection:

$sqlplus sys/password@orcl as sysdba

$ls $ORACLE_HOME/dbs/orapworcl

$orapwd

Authorization

System permissions:

Sys Executive Authorization:

Create a test table in advance

SQL > create table T1 (x int)

SQL > create user user01 identified by password

SQL > grant create session to user01

SQL > grant select any table to user01

User01 Test:

$sqlplus user01/password

SQL > select count (*) from hr.employees (hr.departments scott.emp)

SQL > delete from scott.emp; failed!

SQL > select * from sys.t1; failed!

Select any table nmur1 mode

Sys re-authorizes:

SQL > grant select any dictionary to user01

User01 Test:

SQL > select * from sys.t1; succeeded

Select any table (nmur1) + select any dictionary (1)

Sys Licensing:

SQL > grant create table to user01

User01 Test:

SQL > create table T1 (x int)

Sys Licensing:

SQL > grant unlimited tablespace to user01

User01 Test:

SQL > insert into T1 values (1)

Object permissions:

Reference permissions for the table:

Dept

Deptno (competitive) dname

10 sales

20 market

My_emp

Empno deptno (fk)

100 10

Sys Licensing:

SQL > grant select on hr.employees to user01

User01 Test:

SQL > select count (*) from hr.employees

SQL > delete from hr.employees; failed

SQL > select count (*) from hr.departments; failed

Sys Licensing:

SQL > grant index on hr.employees to user01

SQL > grant unlimited tablespace to user01

User01 Test:

SQL > create index emp_sal_idx on hr.employees (salary)

SQL > select index_name from user_indexes where table_name='EMPLOYEES'

Create any table create table

Alter any table alter table

Drop any table drop table

Cascade deletion of permissions:

System permissions:

Sys preparation work:

SQL > drop user user01 cascade

SQL > drop user user02 cascade

SQL > create user user01 identified by password

SQL > create user user02 identified by password

SQL > grant create session to user01

SQL > grant create session to user02

Sys Licensing:

SQL > grant select any table to user01 with admin option

The user01 test succeeded and was authorized to user02:

SQL > select count (*) from hr.employees

SQL > grant select any table to user02 with admin option

The user02 test was successful:

SQL > select count (*) from hr.employees

Sys withdraws permissions:

SQL > revoke select any table from user01

User01 operation failed:

SQL > select count (*) from hr.employees

The user02 test was successful:

SQL > select count (*) from hr.employees

Object permissions:

SQL > grant select on hr.employees to user01 with grant option

Dba+sysdba=sys

Role

The role is the group in the database!

The role of roles: simplify the management of permissions and dynamically update users' permissions.

Predefined roles:

SQL > select role from dba_roles

Create a role:

SQL > create role hr_mgr

SQL > create role hr_clerk

SQL > grant select any table to hr_mgr

SQL > grant select on hr.employees to hr_clerk

SQL > grant hr_mgr to user01

SQL > grant hr_clerk to user02

User01/user02 Test:

You must log in again if the role is effective.

Profile

Profile mainly controls two aspects:

1 user's resource consumption

2 user's password security

SQL > select * from dba_profiles where profile='DEFAULT'

SQL > select username, profile from dba_users

SQL > switch parameters for show parameter resource_limit resource management

View the script for the complexity function:

$cd $ORACLE_HOME/rdbms/admin

$vi utlpwdmg.sql

$cp utlpwdmg.sql / home/oracle/utlpwdmg.sql

$vi / home/oracle/utlpwdmg.sql retains only part of the check function

$sqlplus / as sysdba

SQL > @ / home/oracle/utlpwdmg.sql

Sys creates a profile:

SQL > CREATE PROFILE HR_PROFILE LIMIT

PASSWORD_LIFE_TIME 30

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_MAX 3

PASSWORD_REUSE_TIME unlimited

PASSWORD_LOCK_TIME 5/1440

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_VERIFY_FUNCTION verify_function_11G

Associate with the user:

SQL > ALTER USER HR PROFILE HR_PROFILE

Test:

$sqlplus hr/hr

SQL > alter user hr identified by password123 replace hr

Audit

Turn on switch parameters:

SQL > show parameter audit_trail

Set audit options:

Each time a new audit option is set, the test user needs to reconnect

Sys preparation work:

SQL > drop user user01 cascade

SQL > create user user01 identified by password

SQL > grant create session, create table, create any table to user01

Audit system permissions:

SQL > AUDIT CREATE ANY TABLE, CREATE TABLE BY USER01 BY ACCESS

User01 Test:

SQL > create table T1 (x int)

SQL > create table T1 (x int); failed

SQL > create table hr.t1 (x int)

SQL > create table hr.t1 (x int); failed

Sys to view the audit results:

SQL > desc aud$

SQL > desc dba_audit_trail

View in browser

Sys add audit conditions:

SQL > AUDIT SELECT ANY TABLE BY user01 BY ACCESS

SQL > grant select any table to user01

User01 Test:

SQL > select * from T1

SQL > select * from hr.t1

Sys to view the audit results:

In the browser or view the dba_audit_ trail table

Delete audit options:

SQL > NOAUDIT CREATE ANY TABLE BY USER01

SQL > NOAUDIT CREATE TABLE BY USER01

SQL > NOAUDIT SELECT ANY TABLE BY user01

Audit object:

Sys sets audit options:

SQL > AUDIT SELECT ON hr.employees BY ACCESS

SQL > drop user user01 cascade

SQL > create user user01 identified by password

SQL > grant create session to user01

Sys authorization, each time a statement is executed, user01 is tested:

SQL > grant select any table to user01

SQL > revoke select any table from user01

SQL > grant select on hr.employees to user01

User01 test (4 times):

SQL > select count (*) from hr.employees

The behavior of sys is not recorded by default:

SQL > select count (*) from hr.employees

Delete audit options:

SQL > NOAUDIT SELECT ON hr.employees

Audit statement:

Sys sets audit options:

SQL > AUDIT TABLE BY user01 BY ACCESS

User01 Test:

SQL > create table T1 (x int); failed

SQL > create table T1 (x int)

SQL > create table T1 (y int); failed

SQL > drop table T1

Sys to view the results:

In the browser or in the DBA_AUDIT_OBJECT table

Delete audit options:

SQL > NOAUDIT TABLE BY USER01

Audit the operation of sys:

SQL > show parameter audit

Modify two parameters

Concurrency management

Read-write conflicts are resolved through read consistency:

Sys preparation work:

SQL > create user user01 identified by password

SQL > grant dba to user01

The following are all in user01:

SQL > conn user01/password

Connected.

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

Session1:

SQL > update T1 set Xuan 11 where Xuan 1

SQL > select * from T1

Session 2:

SQL > select * from T1

Session 1:

SQL > commit

Session 2:

SQL > select * from T1

Test serializable:

Session1:

SQL > alter session set isolation_level=serializable

Repeat the above steps

The conflict between write and write is resolved through the locking mechanism:

Session 1:

SQL > update T1 set Xuan 11 where Xuan 1

View lock information in browser

Session 2:

SQL > update T1 set x111 where xym1; blocked

View lock information in browser

Session 1:

SQL > rollback

View lock information in browser

Deadlock:

Session1:

SQL > select * from T1

X

-

one

two

SQL > update T1 set Xuan 11 where Xuan 1

Session2:

SQL > update T1 set Xerox 22 where Xue2

Session1:

SQL > update T1 set x = 222 where x = 2; blocking

Session2:

SQL > update T1 set x111 where xlock1; deadlock

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Locks and foreign keys

Select... For update

Revoke management

What is undo data:

1. Deal fallback: transactions that are not submitted can be rollback

two。 Recovery of transactions: when the database crashes, restore the incorrect data from the disk to before the transaction

3. Read consistency: the queried record is occupied by a transaction. Turn to the rollback segment to find the mirror before modification.

4. Flashback data: constructing historical data from a rollback segment

Transaction and revocation data:

Redo and undo:

AUM:

3 parameters, 2 tablespace properties

Undo_management=AUTO rollback the segment management mode of the tablespace segment, the administrator only needs to have enough tablespace capacity, and oracle will automatically manage the number of extended rollback segments. Only one UNDO tablespace can be used.

Undo_tablespace: can only be used in automatic management mode. Indicates which UNDO tablespace to use

Undo_retention=900:

The amount of time that the old image remains in the rollback segment after the submission.

Unforced fallback hold time. (if there is not enough room to roll back, the old image will be overwritten.)

Autoextend: automatic tablespace expansion

Mandatory maintenance: but it requires a lot of space, so you should use it with caution. (support starts with 10g)

Alter tablespace UNDOTABS1 RETENTION GUARANTEE

Select tablespace_name,RETENTION from dba_tablespaces

UNDO_RETENTION specifies (in seconds) how long already committed undo information is to be retained. The only time you must set this parameter is when:

The undo tablespace has the AUTOEXTEND option enabled

You want to set undo retention for LOBs

You want to guarantee retention

Undo advisor:

Scheduling job

$ps-ef | grep cjq

SQL > show parameter job_queue_processes

Automatic management jobs set in advance in the background:

Custom Job:

SQL > create table session_history (snap_time timestamp with local time zone, num_session number)

Create a job in em:

Use plsql blocks:

Declare

Session_count number

Begin

Select count (*) into session_count from v$session

Insert into session_history values (systimestamp, session_count)

Commit

End

Globalization support

$vi .bash _ profile

# export NLS_LANG=american_america.AL32UTF8

# export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'

$unset NLS_LANG

$unset NLS_DATE_FORMAT

SQL > select sysdate from dual

Backup recovery configuration recoverability

Control file

SQL > show parameter control_files

SQL > select * from v$controlfile

Modify the path:

$cd $ORACLE_HOME/dbs

$cp spfileorcl.ora spfileorcl.ora.bak

SQL > alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/ home/oracle/control02.ctl' scope=spfile

SQL > shutdown immediate

$mv / u01/app/oracle/fast_recovery_area/orcl/control02.ctl / home/oracle/control02.ctl

SQL > startup

SQL > show parameter control_files

SQL > select * from v$controlfile

Add mirrors:

SQL > alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/ home/oracle/control02.ctl','/ home/oracle/control03.ctl' scope=spfile

SQL > shutdown immediate

$cp / home/oracle/control02.ctl / home/oracle/control03.ctl

SQL > startup

SQL > show parameter control_files

SQL > select * from v$controlfile

Log file:

Add members and log groups:

SQL > select GROUP#, SEQUENCE#, STATUS, MEMBERS from v$log

SQL > select GROUP#, MEMBER from v$logfile

SQL > alter database add logfile member'/ home/oracle/redo01b.log' to group 1

SQL > alter database add logfile member'/ home/oracle/redo02b.log' to group 2

SQL > alter database add logfile member'/ home/oracle/redo03b.log' to group 3

SQL > alter database add logfile group 4 ('/ u01 home/oracle/redo04b.log' size size 50m)

FRA:

SQL > show parameter db_recovery

Backupset: 10GB, archived log: 5GB

10g, 10g

Turn on archive mode:

SQL > archive log list check the current settings

SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > alter database open

SQL > archive log list

SQL > show parameter log_archive_dest

SQL > select group#, sequence#, status, archived from v$log

SQL > alter system switch logfile

SQL > select group#, sequence#, status, archived from v$log

SQL > select NAME, SEQUENCE#, STATUS from v$archived_log

$ls / u01/app/oracle/fast_recovery_area/ORCL

RMAN configuration

$rman target / or rman target sys/password@orcl

RMAN > show all

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP On

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP clear

RMAN > backup tablespace users

RMAN > list backup

You can also view it in the SQL > desc v$backup_set browser

Automatic channel management:

RMAN > CONFIGURE DEVICE TYPE DISK PARALLELISM 2

RMAN > backup tablespace users, example

$mkdir / home/oracle/disk1 / home/oracle/disk2

RMAN > configure channel 1 device type disk to destination'/ home/oracle/disk1'

RMAN > configure channel 2 device type disk to destination'/ home/oracle/disk2'

RMAN > backup tablespace users, example

Restore default

RMAN > CONFIGURE DEVICE TYPE DISK clear

RMAN > CONFIGURE CHANNEL 1 device type disk clear

RMAN > CONFIGURE CHANNEL 2 device type disk clear

Manual channel management:

RMAN > run {

Allocate channel C1 device type disk to destination'/ home/oracle/disk1'

Allocate channel c2 device type disk to destination'/ home/oracle/disk2'

Backup tablespace users, example

Or

Backup (tablespace users channel C1) (tablespace example channel c2)

}

Backup section size 500M datafile 1

Specify the backup format:

RMAN > backup tablespace users

RMAN > backup as compressed backupset tablespace users

RMAN > backup as copy tablespace users

RMAN > list backup of tablespace users

RMAN > list copy of tablespace users

Encryption of backup:

RMAN backup

Unarchived archiving

Online offline online offline

Complete part

Shutdown nomount mount open

Back up the data file:

SQL > select file_id, file_name from dba_data_files

RMAN > backup datafile 4

RMAN > backup datafile 4pr 5

RMAN > backup datafile'/ u01qqappActionoradataUS01.dbf'

RMAN > backup tablespace users

RMAN > backup tablespace users, example

RMAN > backup database

RMAN > list backup

Desc v$backup_set, browser to view backup

Incremental backup:

RMAN > backup incremental level 0 tablespace users

RMAN > list backup of tablespace users

SQL > create table T1 (x int) tablespace users

SQL > insert into T1 values (1)

SQL > commit

RMAN > backup incremental level 1 tablespace users

RMAN > backup incremental level 1 tablespace users

SQL > create table T2 (x int) tablespace users

SQL > insert into T2 values (1)

SQL > commit

RMAN > backup incremental level 1 cumulative tablespace users

RMAN > list backup of tablespace users

Turn on block tracking:

SQL > alter database enable block change tracking using file'/ home/oracle/blk_trk.chg'

Need to reconnect the session

SQL > select * from v$block_change_tracking

SQL > select DATAFILE_BLOCKS, BLOCKS_READ, BLOCKS, USED_CHANGE_TRACKING from v$backup_datafile where INCREMENTAL_LEVEL > 0

SQL > create table T3 (x int) tablespace users

SQL > insert into T3 values (1)

SQL > commit

RMAN > backup incremental level 1 tablespace users

SQL > select DATAFILE_BLOCKS, BLOCKS_READ, BLOCKS, USED_CHANGE_TRACKING from v$backup_datafile where INCREMENTAL_LEVEL > 0

SQL > alter database disable block change tracking; off

Incremental updates:

SQL > create table T1 (x int) tablespace users

SQL > insert into T1 values (1)

SQL > commit

RMAN > backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users; creates a level 0 backup for the first time

RMAN > list copy; record time and scn

SQL > insert into T1 values (2)

SQL > commit

RMAN > backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users; creates a level 1 backup for the second time

RMAN > list backup; backupset format

RMAN > recover copy of tablespace users with tag 'update_copy'

RMAN > list copy; time and scn updates

Script form:

RMAN > run {

Backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users

Recover copy of tablespace users with tag 'update_copy'

}

Back up the archive log:

RMAN > list archivelog all

RMAN > backup archivelog all delete all input

RMAN > list archivelog all

RMAN > list backup

Backup maintenance:

View:

RMAN > list backup

RMAN > list copy

RMAN > list backup of tablespace users

RMAN > list backup of datafile 4

RMAN > list archivelog all

Check the backup:

RMAN > delete backup; delete backup

RMAN > delete copy

RMAN > list backup; list copy

RMAN > report need backup; check according to policy

RMAN > backup tablespace users

RMAN > report need backup

RMAN > CONFIGURE RETENTION POLICY TO REDUNDANCY 2

RMAN > report need backup

RMAN > CONFIGURE RETENTION POLICY clear

RMAN > report obsolete

Delete backup:

RMAN > delete backupset of tablespace users

RMAN > delete backupset 1234

RMAN > backup tablespace users

RMAN > backup tablespace users

RMAN > show all

RMAN > delete obsolete

Crosscheck:

RMAN > delete backup

RMAN > backup tablespace users

RMAN > list backup of tablespace users

$mv / u01/app/oracle/fast_recovery_area/ORCL/backupset/... Rename

RMAN > restore datafile 4; error report

RMAN > crosscheck backup

RMAN > list backup of tablespace users; scrap status

RMAN > list expired backup

$mv / u01/app/oracle/fast_recovery_area/ORCL/backupset/... Restore the original name

RMAN > crosscheck backup

RMAN > list backup of tablespace users; availability status

RMAN > delete expired backup

Catalog:

$cp / u01/app/oracle/fast_recovery_area/ORCL/backupset/... Copy

RMAN > delete backup

RMAN > list backup; backupset disappears

$mv / u01/app/oracle/fast_recovery_area/ORCL/backupset/... Restore the original name

RMAN > catalog recovery area noprompt

RMAN > list backup; backupset recovery

RMAN recovery data file

RPO/RTO

Data file:

A data file is lost without archiving:

SQL > archive log list

Work before backup:

SQL > create table T1 (x varchar2 (50)) tablespace users

SQL > insert into T1 values ('friday, before backup')

SQL > commit

Backup:

SQL > query v$datafile, v$logfile, v$tempfile, v$controlfile

SQL > shutdown immediate

$cd $ORACLE_BASE/oradata/

$cp-r orcl orcl.bak

$cd $ORACLE_BASE/fast_recovery_area/orcl

$cp control02.ctl control02.ctl.bak

SQL > startup

Work after backup:

SQL > insert into T1 values ('monday, after backup')

SQL > commit

Fault:

SQL > alter system flush buffer_cache

$cd $ORACLE_BASE/oradata/orcl

$> users01.dbf

SQL > select * from T1; error report

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

$dbv file=/u01/app/oracle/oradata/orcl/users01.dbf

Restore:

SQL > shutdown abort

$cd $ORACLE_BASE/oradata

$rm-rf orcl

$mv orcl.bak orcl

$cd $ORACLE_BASE/fast_recovery_area/orcl

$mv control02.ctl.bak control02.ctl

SQL > startup

SQL > select * from T1

A data file is missing in archive mode:

SQL > archive log list

Work before backup:

SQL > create table T1 (x varchar2 (50)) tablespace users

SQL > insert into T1 values ('friday, before backup')

SQL > commit

Backup:

RMAN > backup tablespace users tag "tbs_users_weekend_backup"

Work after backup:

SQL > select group#, sequence#, status, archived from v$log

SQL > insert into T1 values ('after backup, logseq 7, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 8, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 9, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 10, current')

SQL > commit

SQL > insert into T1 values ('after backup, logseq 10, current, uncommitted')

SQL > select * from T1

Fault:

SQL > shutdown abort

$rm $ORACLE_BASE/oradata/orcl/users01.dbf

SQL > startup reported an error

SQL > select open_mode from v$database

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Restore:

RMAN > list backup of tablespace users

RMAN > list archivelog all

SQL > alter database datafile 4 offline; system and undotbs cannot offline

SQL > alter database open

RMAN > restore datafile 4

RMAN > recover datafile 4

SQL > alter database datafile 4 online

SQL > select * from T1

Resolve user misoperation through incomplete recovery:

SQL > archive log list

Work before backup:

SQL > create table T1 (x varchar2 (50)) tablespace users

SQL > insert into T1 values ('before backup')

SQL > commit

Backup:

RMAN > backup database tag 'weekend_DB_full_backup'

After backup:

SQL > insert into T1 values ('after backup, before delete')

SQL > commit

Misoperation:

SQL > select sysdate from dual

SQL > select dbms_flashback.get_system_change_number from dual

SQL > delete T1

SQL > commit

SQL > create table after_delete (x int); correct operation

SQL > insert into after_delete values (1)

SQL > commit

Restore:

RMAN > run {

Startup force mount

Set until scn= 1806683

Restore database

Recover database

Alter database open resetlogs

}

Set until time='2015-10-26 11-13-13-23; recovery based on point-in-time

SQL > select * from T1

SQL > select * from after_delete; is missing

SQL > select group#, sequence#, status, archived from v$log

Resolve archive log discontiguity through incomplete recovery:

SQL > archive log list

Before backup:

SQL > create table T1 (x varchar2 (50)) tablespace users

SQL > insert into T1 values ('before backup')

SQL > commit

Backup:

RMAN > backup database tag 'weekend_DB_full_backup'

After backup:

SQL > select GROUP#, SEQUENCE#, STATUS, archived from v$log

SQL > insert into T1 values ('after backup, logseq 1, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 2, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 3, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 4, archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, logseq 5, current')

SQL > commit

SQL > alter system checkpoint

Fault:

SQL > shutdown abort

$rm / u01/app/oracle/oradata/orcl/users01.dbf

$rm / u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_03_01/o1_mf_1_5_cfbcxo84_.arc

Restore:

SQL > startup

The first attempt to restore only one data file failed.

RMAN > run {

Startup force mount

Set until sequence 5

Restore database

Recover database

Alter database open resetlogs

}

SQL > select * from T1

Control file

Missing part of the control file:

SQL > select * from v$controlfile

$> / u01/app/oracle/oradata/orcl/control01.ctl

SQL > select * from vested tablespace; error reported

SQL > alter system checkpoint; reported an error

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

SQL > shutdown abort

SQL > startup nomount

SQL > show parameter control_files

$cp / u01/app/oracle/fast_recovery_area/orcl/control02.ctl / u01/app/oracle/oradata/orcl/control01.ctl

SQL > alter database mount

SQL > alter database open

Loss of all control files (with automatic backup):

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP ON

RMAN > backup datafile 4

$> / u01/app/oracle/oradata/orcl/control01.ctl

$> / u01/app/oracle/fast_recovery_area/orcl/control02.ctl

SQL > select * from vested tablespace; error reported

SQL > alter system checkpoint; reported an error

SQL > shutdown abort

SQL > startup nomount

RMAN > restore controlfile from autobackup

RMAN > alter database mount

RMAN > recover database

RMAN > alter database open resetlogs

Lose all control files (no automatic backup):

SQL > alter database backup controlfile to'/ home/oracle/control.bak'

SQL > alter database backup controlfile to trace

SQL > select * from v$diag_info

Spfile

There are automatic backups:

RMAN > CONFIGURE CONTROLFILE AUTOBACKUP ON

RMAN > backup datafile 4

Online recovery:

$mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak

SQL > alter system set resource_limit=true; reported an error

SQL > create spfile='/home/oracle/spfile.bak' from memory

$mv / home/oracle/spfile.bak $ORACLE_HOME/dbs/spfileorcl.ora

Offline recovery:

SQL > shutdown immediate

$mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak

RMAN > startup

RMAN > restore spfile from'/ u01ActionActionoracleUniplicationfastfastfastfastrecoverycosareaorCLandautobackupand201510 / 26pluso1mfangs894118741c2vkgo8xchang.bkp'

RMAN > startup force

No backup:

Use the parameter values in alert_orcl.log to construct initorcl.ora

SQL > create spfile='/home/oracle/spfile.bak' from pfile

Take advantage of backup init.ora:

$vi / u01/app/oracle/product/11.2.0/db_1/dbs/init.ora perfect parameters

SQL > startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora'

SQL > create spfile from pfile

Redo log

Lose a member:

SQL > select GROUP#, MEMBERS from v$log

SQL > select GROUP#, MEMBER from v$logfile

SQL > alter database add logfile member'/ home/oracle/redo01b.log' to group 1

SQL > alter database add logfile member'/ home/oracle/redo02b.log' to group 2

SQL > alter database add logfile member'/ home/oracle/redo03b.log' to group 3

SQL > alter system switch logfile

SQL > alter system switch logfile

SQL > alter system switch logfile

Fault:

SQL > select group#, status from vault log; confirm current group

$rm-f / home/oracle/redo02b.log delete current group member

SQL > alter system switch logfile

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Restore:

SQL > alter database drop logfile member'/ home/oracle/redo02b.log'

SQL > alter database add logfile member'/ home/oracle/redo02b.log' reuse to group 2

If it is the current log group, the member cannot be deleted and can only be switched and then modified.

Missing inactive log group:

Fault:

SQL > alter system checkpoint

SQL > select group#, status from vault log; confirm inactive group

SQL > shutdown abort

$rm-f / home/oracle/redo03b.log / u01/app/oracle/oradata/orcl/redo03.log

$startup reported an error

Restore:

SQL > startup mount

SQL > select group#, status, archived from v$log

SQL > alter database clear logfile group 3

SQL > alter database open

If the log is not archived:

SQL > alter database clear unarchived logfile group 3

Make a full backup of the database

Lose the current log group (shut down the database normally):

Fault:

SQL > select group#, status from vault log; confirm current group

SQL > shutdown immediate

$rm-f / home/oracle/redo02b.log / u01/app/oracle/oradata/orcl/redo02.log

SQL > startup reported an error

Restore:

SQL > startup mount

SQL > select group#, status, archived from v$log

SQL > alter database clear unarchived logfile group 2

SQL > alter database open

Make a full backup of the database

Missing current log group (abnormal database shutdown):

Fault:

RMAN > backup database

SQL > create table T1 (x varchar2 (50))

SQL > insert into T1 values ('after backup, before archived')

SQL > commit

SQL > alter system switch logfile

SQL > insert into T1 values ('after backup, after archived, current')

SQL > commit

SQL > insert into T1 values ('after backup, after archived, current, uncommitted')

SQL > alter system checkpoint

SQL > shutdown abort

$rm-f / home/oracle/redo03b.log / u01/app/oracle/oradata/orcl/redo03.log

SQL > startup reported an error

SQL > select group#, sequence#, status, archived from vault log; confirm the log sequence number

Restore:

RMAN > run {

Startup force mount

Set until sequence 10

Restore database

Recover database

Alter database open resetlogs;}

SQL > select * from T1; lost data

Missing active log group:

Recover data block

Fault:

SQL > create tablespace tbs01 datafile'/ home/oracle/tbs01.dbf' size 5m

SQL > create table T1 tablespace tbs01 as select * from dba_objects where rownum backup tablespace tbs01

SQL > alter system flush buffer_cache

$dd of=/home/oracle/tbs01.dbf bs=8k conv=notrunc seek=300 select file#, block# from v$database_block_corruption

RMAN > recover datafile 6 block 300

RMAN > recover corruption list

DBMS_REPAIR packet isolated data block

Rman recovery directory

SQL > show parametercontrol_file_record_keep_time

Create database rc with dbca (do not configure em, fra,200M memory, character set unicode)

Or:

Use netca to create the host connection string rc that points to itself.

Rc:

$sqlplus sys/password@rc as sysdba

SQL > create tablespace rc_tbs datafile'/ home/oracle/rc_tbs.dbf' size 50m

SQL > create user rcowner identified by password default tablespace rc_tbs quota unlimited on rc_tbs

SQL > grant recovery_catalog_owner to rcowner

$rman catalog rcowner/password@rc

RMAN > create catalog

$rman target sys/password@orcl catalog rcowner/password@rc

Or

$rman target / catalog rcowner/password@rc

RMAN > register database

Dbca Delete rc

Flashback

Typical errors in parameters related to functional dependent components

Query undo tbs undo_retention dml

Version query undo tbs undo_retention dml

Flashback table undo tbs undo_retention dml

Flashback drop recyclebin recyclebin, freespace drop table

Transaction query supplemental log dml

Fda flashback archive dml

Database flashback log db_flashback_retention_target ddl

Sys does not allow flashbacks, create new users

SQL > create user user01 identified by password

SQL > grant dba to user01

SQL > conn user01/password

Flashbackquery

User01:

SQL > create table T1 (x int)

SQL > create index t1_x_idx on T1 (x)

SQL > insert into T1 values (1)

SQL > commit

SQL > select sysdate from dual

SQL > select dbms_flashback.get_system_change_number from dual

SQL > delete T1

SQL > commit

SQL > select * from T1

SQL > select * from T1 as of scn 1446069

SQL > select * from T1 as of timestamp to_timestamp ('2015-10-28 10 from 31 as of timestamp to_timestamp 54,' yyyy-mm-dd hh34:mi:ss')

SQL > truncate table T1; or alter table T1 move; or shrink data file

SQL > select * from T1 as of scn 1446069; flashback failed due to physical structure change

Logminer

Flashback version query

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > update T1 set Xero2

SQL > commit

SQL > update T1 set Xero3

SQL > commit

SQL > update T1 set Xero4

SQL > commit

SQL > select versions_starttime, versions_endtime, versions_xid, versions_operation, x

From t1

Versions between scn minvalue and maxvalue

Order by versions_starttime

Versions between timestamp to_timestamp ('2015-10-28 900 and to_timestamp,' yyyy-mm-dd hh34:mi:ss') and to_timestamp ('2015-10-28 10 and to_timestamp,' yyyy-mm-dd hh34:mi:ss')

SQL > truncate table T1; physical structure changed and query failed

Flashback table

SQL > conn user01/password

SQL > create table my_dept (deptno int primary key, dname varchar2 (20))

SQL > create table my_emp (empno int primary key, deptno int references my_dept)

SQL > insert into my_dept values (10, 'sales')

SQL > insert into my_emp values (100,10)

SQL > commit

SQL > select dbms_flashback.get_system_change_number from dual

SQL > delete my_emp

SQL > delete my_dept

SQL > commit

SQL > alter table my_dept enable row movement

SQL > alter table my_emp enable row movement

SQL > flashback table my_emp to scn 1451706; failed

SQL > flashback table my_dept to scn 1451706

SQL > flashback table my_emp to scn 1451706

SQL > select INDEX_NAME, STATUS from user_indexes where table_name='MY_EMP'; automatically maintains the index

SQL > select INDEX_NAME, STATUS from user_indexes where table_name='MY_DEPT'

SQL > truncate table my_emp

SQL > flashback table my_emp to scn 1451706; failed

Flashback drop

SQL > show parameter recyclebin

SQL > purge recyclebin

SQL > create tablespace tbs01 datafile'/ home/oracle/tbs01.dbf' size 5m

SQL > create table T1 tablespace tbs01 as select * from dba_objects where rownum create index t1_object_id_idx on T1 (object_id) tablespace tbs01

SQL > select INDEX_NAME from user_indexes where TABLE_NAME='T1'

SQL > drop table T1

SQL > select table_name from user_tables

SQL > show recyclebin

SQL > select object_name, original_name, type, droptime from user_recyclebin; contains index

SQL > select count (*) from "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0"

SQL > flashback table T1 to before drop

SQL > select INDEX_NAME from user_indexes where TABLE_NAME='T1'

SQL > alter index "BIN$LRyc7hA1JaPgUwEAqMDzWw==$0" rename to T1 recovery object ID X; restore index name

Handling of duplicate names:

SQL > flashback table "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0" to before drop

SQL > flashback table T1 to before drop rename to T2

SQL > drop table T1

SQL > show recyclebin in the recycling bin

SQL > create table T2 tablespace tbs01 as select * from dba_objects where rownum show recyclebin T1 is overwritten

SQL > drop table T2 purge

SQL > purge recyclebin

Flashback transaction query

SQL > alter database add supplemental log data

SQL > alter database add supplemental log data (primary key) columns

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > update T1 set x operations 11 where x operations 1; misoperated transactions

SQL > commit

SQL > insert into T1 values (2)

SQL > commit

Select versions_starttime, versions_endtime, versions_xid, versions_operation, x

From t1

Versions between scn minvalue and maxvalue

Order by versions_starttime; gets the xid of the misoperated transaction

SQL > select UNDO_SQL, OPERATION from flashback_transaction_query where xid='02000F0059040000'

Flashback database

SQL > shutdown immediate

SQL > startup mount

SQL > alter database flashback on; database in archive mode

SQL > show parameter db_flashback_retention_target

SQL > select OLDEST_FLASHBACK_TIME from v$flashback_database_log

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > select dbms_flashback.get_system_change_number from dual

SQL > truncate table T1

SQL > create table after_truncate (x int); other correct actions

SQL > select OLDEST_FLASHBACK_TIME, OLDEST_FLASHBACK_SCN from vested flashbackdatabases database log; confirm whether it is in the recovery range

SQL > shutdown abort

SQL > startup mount

SQL > flashback database to scn 1495195

SQL > alter database open resetlogs

SQL > select * from T1

SQL > select * from after_truncate; disappears

Mobile data sqlloader

SQL > create table T1 (id int constraint t1_id_pk primary key, name varchar2 (20), salary int constraint t1_salary_ck check (salary > 0))

$vi ~ / loader.dat

100,100, "abc", 1000

100,100, "def", 2000

102,102, "xyz"-1000

Regular import in em, automatically dealing with records that violate constraints

Import directly into em

SQL > select CONSTRAINT_NAME, STATUS from user_constraints where TABLE_NAME='T1'

SQL > select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1'

SQL > alter table T1 enable validate constraint T1 SALARY CK; failed

SQL > @? / rdbms/admin/utlexpt1.sql

Working with check constraints:

SQL > alter table T1 enable validate constraint T1_SALARY_CK exceptions into exceptions

SQL > select * from T1 where rowid in (select ROW_ID from exceptions)

SQL > update T1 set salary=abs (salary) where id=102

SQL > truncate table exceptions

SQL > alter table T1 enable validate constraint T1_SALARY_CK exceptions into exceptions

Deal with competitive constraints:

SQL > alter table T1 disable novalidate constraint T1_ID_PK

SQL > alter table T1 enable validate constraint T1_ID_PK exceptions into exceptions

SQL > select * from T1 where rowid in (select ROW_ID from exceptions)

SQL > update T1 set id=101 where name='def'

SQL > truncate table exceptions

SQL > alter table T1 enable validate constraint T1_ID_PK exceptions into exceptions

SQL > select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1'

External table

Oracle_datapump driver

Unloading:

CREATE TABLE oe.inventories_xt

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY DATA_PUMP_DIR

LOCATION ('inv_xt.dmp')

)

AS SELECT * FROM oe.inventories

SQL > delete oe.inventories_xt; failed

Loading:

CREATE TABLE oe.inventories_xt2

(

Product_id NUMBER (6)

Warehouse_id NUMBER (3)

Quantity_on_hand NUMBER (8)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY DATA_PUMP_DIR

LOCATION ('inv_xt.dmp')

);

SQL > delete oe.inventories_xt2; failed

Optimize

DB time = CPU time + Wait time

Automatic maintenance preparation work

Dictionary table:

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; value is empty

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; update

SQL > insert into T1 values (2)

SQL > commit

SQL > select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; is out of date

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from dba_tables where table_name='T1'; updates

Table v$:

SQL > select name, value from v$sysstat where name like'% sort%'

SQL > select * from hr.employees order by salary

SQL > select name, value from v$sysstat where name like'% sort%'; increased

SQL > shutdown immediate

SQL > startup

SQL > select name, value from v$sysstat where name like'% sort%'; return to zero

Parameters:

SQL > show parameterstatistics_level cannot be basic

AWR

View basic settings in em

Generate and view awr reports in em

$ll / u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awr*.sql

Serveralert

SQL > create tablespace tbs01 datafile'/ home/oracle/tbs01.dbf' size 5m

50% of the warnings are modified in em, and 80% of them are serious.

SQL > create table T1 tablespace tbs01 as select * from dba_objects where 1: 0

SQL > insert into T1 select * from dba_objects where rownum commit; over 50%

SQL > insert into T1 select * from dba_objects where rownum commit; over 80%

View the warning message in em.

ADDM

Session1:

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > update T1 set Xero11

Session 2:

SQL > update T1 set Xerox 22

ASH

Session1:

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > update T1 set Xero11

Session 2:

SQL > update T1 set Xerox 22

Find the root of the problem in em

Make ash report in em

$ll / u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ash*.sql

AMM

Initorcl.ora

Spfileorcl.ora

ASMM

AMM

SQL > select bytes/1024/1024 from v$sgainfo where name='Granule Size'

SQL > show parameter memory

SQL > select COMPONENT, CURRENT_SIZE/1024/1024 from v$memory_dynamic_components

Memory guidance in em

Implicit parameters at the beginning of $strings $ORACLE_HOME/dbs/spfileorcl.ora _ _ retain the optimized settings

Adjustment of javapool:

SQL > select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='java pool'

DECLARE

I NUMBER

V_sql VARCHAR2 (200)

BEGIN

FOR i IN 1..200 LOOP

-- Build up a dynamic statement to create a uniquely named java stored proc.

The "chr (10)" is there to put a CR/LF in the source code.

V_sql: = 'create or replace and compile' | | chr (10) | |

'java source named "SmallJavaProc' | | I | |'"'| | chr (10) | |

'as' | | chr (10) | |

'import java.lang.*;' | | chr (10) | |

Public class Util' | | I | | 'extends Object' | | chr (10) | |

'{int v1 countries 1 int v2 countries 2 int v3 countries 3 countries int v4 countries 4 countries int v5 countries 5 countries int v6 countries 6 cities int v7;}'

EXECUTE IMMEDIATE v_sql

END LOOP

END

/

SQL > select CURRENT_SIZE from v$memory_dynamic_components where COMPONENT='java pool'; java pool change

Java pool expansion, buffercache contraction

SQL > select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='java pool'

SQL > select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='DEFAULT buffer cache'

Adjustment of largepool:

SQL > select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool'

SQL > create table T1 as select rownum x from dual connect by level alter table T1 parallel 64; you can also specify the degree of parallelism when querying

SQL > select / * + parallel (T1 24) * / count (*) from (select / * + parallel (T1 24) * / * from T1 group by x)

SQL > select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool'; large pool change

SQL > select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='large pool'

Backup spfile

$cp $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak

AMM à ASMM

Under amm, sga and pga do not need to be set

SQL > show parameter sga_max_size

SQL > show parameter sga_target

SQL > show parameter pga_aggregate_target

SQL > alter system set memory_target=0

SQL > show parameter sga_target amm and asmm both have a pair of parameters

SQL > show parameter sga_max_size

SQL > show parameter pga_aggregate_target

SQL > alter system set sga_target=300M; modify manually

ASMM à manual

SQL > show parameter shared_pool_size value is 0

SQL > alter system set sga_target=0

SQL > show parameter shared_pool_size fixed

Manualsand asmmroomamm

Modify sga_target or memory_target to clear all legacy parameters

Recommendations for memory size:

SQL > select SHARED_POOL_SIZE_FOR_ESTIMATE, SHARED_POOL_SIZE_FACTOR, ESTD_LC_TIME_SAVED from V$SHARED_POOL_ADVICE

SQL > select SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PHYSICAL_READS, ESTD_PHYSICAL_READ_FACTOR from V$DB_CACHE_ADVICE

SQL > select * from V$SGA_TARGET_ADVICE

SQL > select PGA_TARGET_FOR_ESTIMATE, PGA_TARGET_FACTOR, ESTD_PGA_CACHE_HIT_PERCENTAGE from V$PGA_TARGET_ADVICE

SQL > select * from V$MEMORY_TARGET_ADVICE

SAA

SQL > alter system flush shared_pool

SQL > grant dba to hr

SQL > conn hr/hr

SQL > set autot on

SQL > select e.last_name, d.department_name

From employees e, departments d

Where e.department_id=d.department_id

Execute saa in em. Filter conditions are table: hr.employees, hr.departments

STA

SQL > alter system flush shared_pool

SQL > conn hr/hr

SQL > set autot on

SQL > select / * + full (employees) * / * from employees where employee_id=100

Create tuningset in em and call sta analysis

SQL optimizer

Reference: optimizer introduction .ppt

Query rewriting:

Predicate passing:

SQL > set autot trace exp

SQL > select e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

And e.department_id=50

Automatically add 3-access ("D". "DEPARTMENT_ID" = 50) predicates

For i in 1.. 107 (employees)

For j in 1.. 27 (departments)

I department = j department and I department = 50

Endloop

Endloop

For i in 1.. 10 (employeesin deptno 50)

For j in 1.. 27 (departments)

I's department = j's department

Endloop

Endloop

For i in 1.. 10 (employeesin deptno 50)

I department = 50

Endloop

Child query and query nesting:

SQL > select last_name

From hr.employees outer

Where salary >

(select avg (salary) from hr.employees

Where department_id = outer.department_id)

Rewritten as a multi-table join

The difference between CBO and RBO:

SQL > create table T1 as select 1 id, object_name from dba_objects

SQL > update T1 set id=2 where rownum commit

SQL > select id, count (*) from T1 group by id

SQL > create index t1_id_idx on T1 (id)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > set autot trace exp

SQL > select * from T1 where id=1; cbo mode

SQL > select / * + rule * / * from T1 where id=1

SQL > select * from T1 where id=2; error

SQL > select / * + rule * / * from T1 where id=2; index, correct

SQL > exec dbms_stats.gather_table_stats ('sys', 't1'); repeat collection to get column value distribution

Exec dbms_stats.gather_table_stats ('sys',' T1, method_opt = > 'for columns size auto id')

SQL > select * from T1 where id=1; cbo mode, correct

SQL > select * from T1 where id=2; cbo mode, correct

SQL > select / * + rule * / * from T1 where id=1; index, error

SQL > select / * + rule * / * from T1 where id=2

Failure to update statistical information in time, resulting in errors

SQL > update T1 set id=2

SQL > commit

SQL > select * from T1 where id=2; go index, error

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select * from T1 where id=2; is correct

SQL > exec dbms_stats.delete_table_stats ('sys', 't1')

SQL > update T1 set id=1 where rownum commit

SQL > select * from T1 where id=1; dynamic sampling

SQL > select * from T1 where id=2; dynamic sampling

Initialization parameters that affect cbo:

SQL > show parameter optimizer

Impact of all_rows and first_rows on the implementation of the plan:

SQL > alter session set optimizer_mode=first_rows or all_rows

SQL > set autot trace exp

Select e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

Select / * + all_rows * / e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.departmentprincipid.departmentaccounid.Use sort merge

Select / * + first_rows * / e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.departmentprincipid.departmentaccounid.Use nested loop

Carry out the plan

Explain plan

SQL > desc plan_table

SQL > select * from plan_table

SQL > explain plan set statement_id='test' for select * from hr.employees

SQL > select PLAN_ID, poor readability of OPERATION from plan_table where statement_id='test';

SQL > select plan_table_output from table (dbms_xplan.display); good readability

If the statement is not actually executed, there may be errors for statements that use bound variables

Autotrace

SQL > set autot on

SQL > select count (*) from hr.employees

SQL > set autot trace

SQL > select count (*) from hr.employees

SQL > set autot trace exp

SQL > select count (*) from hr.employees

SQL > set autot trace stat

SQL > select count (*) from hr.employees

SQL > set autot off

The on option actually executes the statement, but there may be errors for statements that use bound variables

Other options do not actually execute the statement

DBMS_XPLAN

Reference: PL/SQL Packages and Types ReferenceDBMS_XPLAN

Work with explainplan:

Refer to the explain plan example

Display_cursor:

Look at the previous sql statement:

SQL > SET PAGESIZE 0

SQL > select count (*) from hr.employees

SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR)

SQL > select count (*) from hr.employees

SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR (null, null, 'advanced'))

SQL > desc v$sql

SQL > desc v$sql_plan

SQL > desc v$sql_plan_statistics

SQL > select count (*) from hr.employees

SQL > select sql_id, child_number, sql_text from v$sql where sql_text like 'select count (*) from hr.employees'

SQL > select OPERATION, OPTIONS, OBJECT_NAME from v$sql_plan where SQL_ID='3ghpkw4yp4dzm' and CHILD_NUMBER=0

SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR ('3ghpkw4yp4dzm),' advanced'))

You can also get the sql execution plan from the awr snapshot, display_awr

Sql trace:

SQL > show parameter sql_trace

SQL > show parameter statistics_level

SQL > show parameter timed_statistics

Auxiliary parameter

SQL > show parameter max_dump_file_size

SQL > show parameter diagnostic_dest

SQL > show parameter tracefile_identifier

SQL > select * from v$diag_info

SQL > alter session set sql_trace=true

SQL > select count (*) from hr.employees

SQL > select count (*) from hr.departments

SQL > alter session set sql_trace=false

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6651.trc

$tkprof / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6651.trc / home/oracle/output.trc

$vi / home/oracle/output.trc

Use DBMS_MONITOR monitoring to specify session:

SQL > select sid, serial# from v$session where USERNAME='HR'

SQL > EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE (30, 4145, TRUE, TRUE)

Session of hr:

SQL > select count (*) from employees

Sys turns off tracking:

SQL > EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE (30, 4145)

Trace file for hr:

SQL > select * from v$diag_info

$vi / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10676.trc

Use trcsess to summarize the user session information under the shared server connection.

Perform scheduled reads:

SQL > set linesize 999

SQL > set autot trace exp

SQL > select e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

View in sqlplus and sql developer

Statistics:

SQL > alter system flush shared_pool

SQL > alter system flush buffer_cache

SQL > set autot on

SQL > select count (*) from hr.employees

SQL > select count (*) from hr.employees

Optimizer operation full table scan

SQL > select * from hr.employees

Scan all blocks below the high water mark

Proportion of queries, physical order, table size, no index, parallel

Selectivity and cardinality reference: introduction to optimizer .ppt

The effect of physical order on full table scanning:

SQL > create table T1 as select rownum x, dbms_random.value y from dual connect by level alter table T1 add constraint t1_x_pk primary key (x)

SQL > create table T2 as select * from T1 order by y

SQL > alter table T2 add constraint t2_x_pk primary key (x)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > exec dbms_stats.gather_table_stats ('sys', 't2')

SQL > select * from T1 where x between 1 and 100

SQL > select * from T2 where x between 1 and 100

SQL > select INDEX_NAME, CLUSTERING_FACTOR from dba_indexes where table_name in ('T1, 'T2')

The impact of query statements on full table scans:

SQL > select * from hr.employees

SQL > select * from hr.employees order by employee_id

SQL > select employee_id from hr.employees

SQL > select department_id from hr.employees

The impact of db_file_multiblock_read_count on full table scans:

SQL > show parameter db_file_multiblock_read_count

SQL > create table T1 as select * from dba_objects

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > set autot on

SQL > alter system set db_file_multiblock_read_count=16

SQL > select count (*) from T1

SQL > alter system set db_file_multiblock_read_count=64

SQL > select count (*) from T1

Effect of high water mark on full table scan:

SQL > delete T1

SQL > commit

SQL > set autot on

SQL > select count (*) from T1; after the data is deleted, the hwm does not decrease, resulting in cr reading too much

SQL > alter table T1 move

SQL > select count (*) from T1

How to use INDEX

INDEX UNIQUE SCAN:

SQL > select * from hr.employees where employee_id=100

INDEX RANGE SCAN:

SQL > select * from hr.employees where employee_id between 100 and 110

SQL > select * from hr.employees where department_id=10

SQL > select * from hr.employees where last_name='King'

SQL > select INDEX_NAME, UNIQUENESS from dba_indexes where TABLE_NAME='EMPLOYEES'

INDEX FULL SCAN: monolithic, ordered

SQL > select * from hr.employees order by employee_id

SQL > select / * + full (employees) * / * from hr.employees order by employee_id

SQL > select * from hr.employees order by department_id; full table scan because of null values

INDEX FAST FULL SCAN: multi-block, disordered

SQL > create table T1 as select rownum id, object_name from dba_objects

SQL > alter table T1 add constraint t1_id_pk primary key (id)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select count (*) from T1

SQL > select / * + index (T1 t1_id_pk) * / count (*) from T1; full scan is expensive

SQL > select / * + full (T1) * / count (*) from T1

SQL > select / * + index_ffs (employees emp_emp_id_pk) * / employee_id from hr.employees

INDEX SKIP SCAN:

SQL > create table T1 as select * from dba_objects

SQL > select count (distinct owner), count (distinct object_type), count (distinct object_name) from T1

SQL > create index t1_idx on T1 (owner, object_type, object_name)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select * from T1 where owner='SYS' and object_type='TABLE' and object_name='AUD$'

SQL > select * from T1 where owner='SYS' and object_type='TABLE'

SQL > select * from T1 where object_type='TABLE' and object_name='AUD$'

SQL > select / * + full (T1) * / * from T1 where object_type='TABLE' and object_name='AUD$'

Class_no: 5

Stud_no: 50 (per class)

one

One, two, three... fifty

two

One, two, three... fifty

five

One, two, three... fifty

Where stud_no between 5 and 10

Where class_no=1 and stud_nobetween 5 and 10

Or class_no=2 and stud_nobetween 5 and 10

Or class_no=3 and stud_nobetween 5 and 10

The impact of null on indexes:

SQL > create table T1 (x int, y char (1))

SQL > insert into T1 values (null,'a')

SQL > insert into T1 values (1,'a')

SQL > insert into T1 values (2,'a')

SQL > create index t1_x_idx on T1 (x)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

Test the following statements:

Select x from t1

Select count (*) from T1

Select count (*) from T1 where x is not null

Select count (x) from T1

Select max (x) from T1

Select min (x), max (x) from T1

Exclude null and test again:

SQL > delete T1 where x is null

SQL > commit

SQL > alter table T1 modify (x not null)

SQL > select (select min (x) from T1), (select max (x) from T1) from dual

The effect of duplicate values on the index:

SQL > create table T1 (x int not null, y int)

SQL > insert into T1 select rownum, 11 from dual connect by level commit

SQL > create index t1_x_idx on T1 (x)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select * from T1 where Xero1; full table scan

SQL > select INDEX_NAME, UNIQUENESS from dba_indexes where TABLE_NAME='T1'

SQL > drop index t1_x_idx

SQL > create unique index t1_x_idx on T1 (x)

SQL > select * from T1 where Xero1; index unique scan

The influence of foreign keys on indexes:

SQL > create table dept (deptno int constraint dept_deptno_pk primary key, dname varchar2 (10))

SQL > create table emp (empno int, deptno int constraint dept_emp_deptno_fk references dept (deptno))

SQL > insert into dept values (10, 'sales')

SQL > insert into dept values (20, 'market')

SQL > insert into dept values (30, 'it')

SQL > insert into emp values (100,10)

SQL > commit

SQL > exec dbms_stats.gather_table_stats ('sys',' dept')

SQL > exec dbms_stats.gather_table_stats ('sys',' emp')

SQL > alter session set sql_trace=true

SQL > delete dept where deptno=10; reported an error

SQL > delete dept where deptno=20

SQL > alter session set sql_trace=false

SQL > select * from v$diag_info

$tkprof / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22830.trc / home/oracle/output.trc

$vi / home/oracle/output.trc

SQL > alter table emp modify (deptno not null)

SQL > create index emp_deptno_idx on emp (deptno)

SQL > alter session set sql_trace=true

SQL > delete dept where deptno=10; reported an error

SQL > alter session set sql_trace=false

The impact of type conversion on the index:

SQL > create table T1 (x char (1) primary key, y int)

SQL > insert into T1 values ('1customers, 11)

SQL > insert into T1 values ('2clients, 22)

SQL > commit

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > set autot trace exp

SQL > select * from T1 where Xero1

Function index:

SQL > create table T1 as select * from dba_objects

SQL > create index t1_object_name_fbi on T1 (lower (object_name))

SQL > select * from T1 where lower (object_name) = 'aud$'

Create index t1_idx on T1 (reverse (x))

Where x like reserve ('% abc%')

Bitmap index:

Emp

Ename gender deptno location job_id

Abc M 10 BJ MGR

Def F 20 SH EGR

Xyz M 30 GZ MGR

Select ename

From emp

Where gender='M' and (deptno=10 or location='GZ') and job_id='MGR'

Gender M F

Abc 1 0

Def 0 1

Xyz 1 0

Deptno 10 20 30

Abc 1 0 0

Def 0 1 0

Xyz 0 0 1

Location BJ SH GZ

Abc 1 0 0

Def 0 1 0

Xyz 0 0 1

Job_id MGR EGR

Abc 1 0

Def 0 1

Xyz 1 0

Gender (M) and (deptno (10) or location (GZ)) and job_id (mgr)

Abc 1 1 0 1 1

Def 0 0 0

Xyz 1 0 1 1 1

Multi-table connection

Nested loop:

For emp in 1..107

For dept in 1..27

Emp.deptno=dept.deptno

End

End

For dept 1-27

For emp 1-107

Sort merge:

Emp sorts according to deptno

Dept sorts according to deptno

Merge emp and dept

Hashjoin:

Emp divides deptno according to hash function

Dept divides deptno according to hash function

Select / * + first_rows * / e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

Or use / * + use_nl (e d) * /, but limit the driven table and the driven table, so it is not flexible

SQL > alter index hr.EMP_DEPARTMENT_IX invisible; disables foreign key indexing on emp

When performing a multi-table join, the primary key index of dept is used as an internally driven table, and nl will try its best to avoid full table scanning of the driven table.

SQL > alter index hr.EMP_DEPARTMENT_IX visible; restore index

Select / * + use_merge (e d) * / e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

Select / * + use_hash (e d) * / e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

When an index is missing, you tend to use hashjoin:

SQL > alter index hr.EMP_DEPARTMENT_IX invisible

SQL > alter index hr.DEPT_ID_PK invisible

Select e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

SQL > alter index hr.EMP_DEPARTMENT_IX visible

SQL > alter index hr.DEPT_ID_PKvisible

Basic View of Statistics

SQL > desc dba_tab_statistics

SQL > desc dba_tab_col_statistics

SQL > desc dba_ind_statistics

SQL > create table T1 as select * from dba_objects

SQL > create index t1_object_id_idx on T1 (object_id)

SQL > create index t1_owner_idx on T1 (owner)

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select * from DBA_TAB_STATISTICS where TABLE_NAME='T1'

SQL > select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'

SQL > select * from DBA_IND_STATISTICS where TABLE_NAME='T1'

SQL > select count (*) from T1 where owner='SYS'; has no histogram and uses the wrong plan

Histogram

SQL > create table T1 (x int not null, y varchar2)

SQL > create index t1_x_idx on T1 (x)

SQL > insert into T1 select 1, object_name from dba_objects where rownum insert into T1 select 2, object_name from dba_objects where rownum commit

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > select x, count (*) from T1 group by x

SQL > select * from DBA_TAB_STATISTICS where TABLE_NAME='T1'

SQL > select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; did not collect histogram

SQL > select * from DBA_IND_STATISTICS where TABLE_NAME='T1'

SQL > set autot on

SQL > select * from T1 where xroom1; rows is not accurate

SQL > select * from T1 where Xero2

SQL > exec dbms_stats.gather_table_stats ('sys', 't1records, method_opt = >' for columns x size skewonly'); collect the histogram of column x

SQL > select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'

SQL > select * from dba_histograms where table_name='T1' and column_name='X'

SQL > select * from T1 where Xero1; correct

SQL > select * from T1 where X. 2; correct

SQL > select count (distinct y) from T1; over 254,

SQL > select * from T1 where y like 'DBA%'

SQL > exec dbms_stats.gather_table_stats ('sys', 't1percent, method_opt = >' for columns size auto y')

SQL > select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; create contour histogram

Select COLUMN_NAME, HISTOGRAM, NUM_BUCKETS from DBA_TAB_COLUMNS where TABLE_NAME='T1'

Bind variables and shared cursors

Shared cursor shared curosr:

SQL > conn / as sysdba

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > alter system flush shared_pool

SQL > select * from T1

View the parent cursor, child cursor, and execution plan:

Select sql_text, sql_id, plan_hash_value, version_count from v$sqlarea where sql_text like 'select * from T1'

Select plan_hash_value, child_number from v$sql where sql_id='27uhu2q2xuu7r'

Select * from v$sql_plan where plan_hash_value='3617692013'

SQL > conn hr/hr

SQL > create table T1 (x int primary key)

SQL > insert into T1 values (1)

SQL > commit

SQL > select * from T1

Query the parent cursor, child cursor and execution plan again: 3 statements

SQL > select * from T1; the literal value is required to be exactly the same

Select sql_text, sql_id, plan_hash_value, version_count from v$sqlarea where sql_text like 'select * from% T1'

Session cursor:

SQL > show parameter open_cursors

SQL > show parameter session_cached_cursors

SQL > select * from T1

SQL > select distinct sid from v$mystat

SQL > select * from v$open_cursor where sid=33

Performance differences:

SQL > create table T1 (x int not null, y int)

SQL > create index t1_x_idx on T1 (x)

SQL > insert into T1 select rownum, 11 from dual connect by level commit

SQL > exec dbms_stats.gather_table_stats ('sys', 't1')

Alter session set sql_trace=true

Begin

For i in 1..100 loop

Execute immediate 'select * from T1 where Xerox' | | I

End loop

End

/

Begin

For i in 1..100 loop

Execute immediate 'select * from T1 where Xuan using I

End loop

End

/

Alter session set sql_trace=false

Bindingvariablepeeking and acs:

SQL > create table T1 (x int not null, y int)

SQL > create index t1_x_idx on T1 (x)

SQL > insert into T1 select 1, 11 from dual connect by level insert into T1 values (2,22)

SQL > commit

SQL > exec dbms_stats.gather_table_stats ('sys', 't1clients, method_opt= >' for all columns')

SQL > select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; collect histogram

SQL > alter session set optimizer_features_enable='10.2.0.1'

SQL > alter system flush shared_pool

SQL > var x number

SQL > exec: X: = 1

SQL > select * from T1 where x

SQL > set pagesize 0

SQL > select * from table (dbms_xplan.display_cursor (null, null, 'advanced'))

View parent and child cursors:

Select sql_text, sql_id, plan_hash_value, version_count, executions from v$sqlarea where sql_text like 'select * from T1 where x percent'

Select plan_hash_value, child_number from v$sql where sql_id='8h3m8wg51m8nm'

Select * from v$sql_plan where plan_hash_value='3617692013'

SQL > exec: X: = 2

SQL > select * from T1 where x

SQL > select * from table (dbms_xplan.display_cursor (null, null, 'advanced')); error

Check the cursor again: 3 statements

Acs:

SQL > conn / as sysdba restore optimizer version

SQL > alter system flush shared_pool

SQL > var x number

SQL > exec: X: = 1

SQL > select * from T1 where x

SQL > select * from table (dbms_xplan.display_cursor (null, null, 'advanced'))

View cursors: 3 statements

SQL > exec: X: = 2

SQL > select * from T1 where x

SQL > select * from table (dbms_xplan.display_cursor (null, null, 'advanced')); unchanged

SQL > select * from T1 where x

SQL > select * from table (dbms_xplan.display_cursor (null, null, 'advanced')); index

View cursors: 3 statements

Sharedpool

Latch and mutex:

Number of shared pool latch:

Select a.ksppinm, b.ksppstvl, a.ksppdesc

From x$ksppi a, x$ksppsv b

Where a.indx=b.indx

And a. KsppinmThe kghdsidxcount`

SQL > create table T1 as select rownum x from dual connect by level exec dbms_stats.gather_table_stats ('sys', 't1')

SQL > alter system flush shared_pool

SQL > exec dbms_workload_repository.create_snapshot ()

SQL > select distinct sid from v$mystat

Simulate hard parsing:

Begin

For i in 1..500000 loop

Execute immediate 'select * from T1 where Xerox' | | I

End loop

End

/

Another session monitor:

SQL > select * from v$session_wait where sid=143

SQL > exec dbms_workload_repository.create_snapshot ()

Em do awr report and addm,ash report and mine drilldown

Mutex wait event:

SQL > alter system set memory_target=0

SQL > alter system set sga_target=0

SQL > alter system flush shared_pool

SQL > exec dbms_workload_repository.create_snapshot ()

Execute simultaneously in both session:

Begin

Loop

Execute immediate 'alter system flush shared_pool'

For i in 1..1000 loop

Execute immediate 'select * from T1 where Xerox' | | I

End loop

End loop

End

/

Another session monitor:

Select * from v$session_wait where sid=143

SQL > exec dbms_workload_repository.create_snapshot ()

Em do awr report and addm,ash report and mine drilldown

Buffercache

Latch:cache buffer chains

SQL > create table T1 (x int)

SQL > insert into T1 values (1)

SQL > commit

SQL > select distinct sid from v$mystat

SQL > exec dbms_workload_repository.create_snapshot ()

Both session execute simultaneously:

Declare

V1 int

Begin

For i in 1..99999999

Loop

Select count (*) into v1 from T1

End loop

End

/

In the third session:

SQL > select * from v$session_wait where sid in (136,137)

SQL > exec dbms_workload_repository.create_snapshot ()

Buffer busy waits

Run in two session:

Declare

V1 int

Begin

For i in 1..99999999

Loop

Insert into T1 values (I)

End loop

End

/

In the third session:

SQL > select * from v$session_wait where sid in (136,137)

SQL > exec dbms_workload_repository.create_snapshot ()

Optimization example

Create table T1 (x int, y char (1))

Insert into T1 values (1,'a')

Insert into T1 values (2,'a')

Create index t1_x_idx on T1 (x)

Exec dbms_stats.gather_table_stats ('sys', 't1')

Why didn't you use the index?

Select x from t1

Create table t1 as select 1 id, object_name from dba_objects

Update T1 set id=2 where rownum 'FOR ALL COLUMNS size 1')

Why didn't you use the index?

Select * from T1 where Xerox 2

Alter session set optimizer_mode='first_rows'

Customers complain about long response time

Select e.last_name, d.department_name

From hr.employees e, hr.departments d

Where e.department_id=d.department_id

Create table T1 (x int)

Create or replace procedure proc1

As

Begin

For iin 1..100000 loop

Execute immediate 'insert into T1 values (' | | I | |')'

Commit

End loop

End

/

Customers complain about long running time

Begin

Proc1

End

/

1. Original statement, dynamic sql, without binding variables:

Drop table t1 purge

Create table T1 (x int)

Create or replace procedure proc1

As

Begin

For iin 1..100000 loop

Execute immediate 'insert into T1 values (' | | I | |')'

Commit

End loop

End

/

Alter system flush shared_pool

Set timing on

Exec proc1

Select count (*) from T1

Completed in 44 seconds.

Select sql_text, sql_id, parse_calls, executions from v$sql where lower (sql_text) like'% insert into T1 values%'

Dynamic sql is flexible, and it is convenient to deal with ddl or dml objects when they do not exist in advance, but they are parsed at run time, resulting in poor performance.

Without binding variables, each statement is parsed once and executed once, which is inefficient.

two。 Overwrite, using binding variables:

Drop table t1 purge

Create table T1 (x int)

Create or replace procedure proc1

As

Begin

For i in 1..100000 loop

Execute immediate 'insert into T1 values (: X)' using I

Commit

End loop

End

/

Alter system flush shared_pool

Set timing on

Exec proc1

Complete in 12 seconds.

Select sql_text, sql_id, parse_calls, executions from v$sql where lower (sql_text) like'% insert into T1 values%'

Using binding variables, parse once and execute 100000 times.

3. Overwrite, using static sql:

Drop table t1 purge

Create table T1 (x int)

Create or replace procedure proc1

As

Begin

For i in 1..100000 loop

Insert into T1 values (I)

Commit

End loop

End

/

Alter system flush shared_pool

Set timing on

Exec proc1

Completed in 10 seconds.

Select sql_text, sql_id, parse_calls, executions from v$sql where lower (sql_text) like'% insert into T1 values%'

Static sql customizes the use of bound variables, parses once and executes 100000 times. And parse it during the compilation process.

4. Rewrite, batch submit:

Drop table t1 purge

Create table T1 (x int)

Create or replace procedure proc1

As

Begin

For i in 1..100000 loop

Insert into T1 values (I)

End loop

Commit

End

/

Alter system flush shared_pool

Set timing on

Exec proc1

Completed in 5 seconds.

5. Overwrite, using collection operations:

Drop table t1 purge

Create table T1 (x int)

Insert into T1 select rownum from dual connect by level select * from v$nls_parameters

Delete the header information of the asm disk:

# dd if=/dev/zero of=/dev/sdb bs=1M count=1

Manually establish a ssh trust relationship:

On node1/node2

# su-grid

$mkdir ~ / .ssh

$chmod 700 ~ / .ssh

$ssh-keygen-t rsa

$ssh-copy-id 192.168.0.2 node1

$ssh-copy-id 192.168.0.1 node2

Ssh node1 date

Ssh node2 date

Ssh node1-priv date

Ssh node2-priv date

Turn off automatic mount to avoid desktop crash:

Chmod-x / usr/libexec/gvfs-gdu-volume-monitor

System structure

Vi / etc/init/oracle-ohasd.conf ohasd startup item

Ps-ef | more has-related processes

# / u01/app/11.2.0/grid/bin/crsctl stat res-Resources for init-t crs and ohas

# / Resources managed by u01/app/11.2.0/grid/bin/crsctl stat res-t crs

Two instances connect to db (through scan-vip and node-vip) to add data tests.

Node1/node2:

# su-oracle

$sqlplus / as sysdba

SQL > select instance_name from v$instance

SQL > select name from v$database

$sqlplus sys/password@scan.test.com:1521/orcl as sysdba create more connections

$sqlplus sys/password@192.168.0.11:1521/orcl as sysdba

$sqlplus sys/password@192.168.0.12:1521/orcl as sysdba

Cluster management

Start / close crs and resources, both on node1 and node2 (as root):

# / u01/app/11.2.0/grid/bin/crsctlstopcrs [- f]

# / u01/app/11.2.0/grid/bin/crsctl start crs

Start / close resources and execute on one node (as root):

# / u01/app/11.2.0/grid/bin/crsctlstop cluster-all

# / u01/app/11.2.0/grid/bin/crsctlstart cluster-all

Query resources (grid users can):

$crsctlstatres-t

$olsnodes-h

Case management

Start and shut down

Sqlplus (oracle user) / oem/srvctl (grid user)

Instance level:

# su-oracle

$sqlplus / as sysdba

SQL > shutdown immediate

# su-grid

$srvctl status instance-d orcl-I orcl1,orcl2 to view instance status

$srvctl stop instance-d orcl-I orcl1-o immediate closes the instance on any node

$srvctl start instance-d orcl-I orcl1

Database level:

$srvctl status db-d orcl

$srvctl stop db-d orcl-o immediate

$srvctl start db-d orcl-o mount

$srvctl modify db-d orcl-s open

$srvctl config db-d orcl-a

$srvctl modify db-d orcl-y manual

$srvctl modify db-d orcl-y automatic

Asm instance

# su-grid

$ps-ef | grep asm*

$sqlplus / as sysasm

SQL > startup | shutdown immediate is executed separately on each node

$srvctl status asm

$srvctl stop asm-n node1-o abort-f

$srvctl start asm-n node1

Spfile

View:

# su-oracle

$sqlplus / as sysdba

Orcl1 > show parameter spfile

# su-grid

$asmcmd

ASMCMD > cd + data/orcl

# su-oracle

$sqlplus / as sysdba

Orcl1 > create pfile='/home/oracle/pfile.ora' from spfile

$vi / home/oracle/pfile.ora

Modify:

Orcl1 and orcl2:

# su-oracle

$sqlplus / as sysdba

Orcl1 > show parameter open_cursors

Orcl1 > alter system set open_cursors=600

Same SQL > alter system set open_cursors=600 sid='*';

Orcl2 > show parameter open_cursors

Orcl1 > alter system set open_cursors=800 sid='orcl2'

Orcl2 > show parameter open_cursors

Restore:

Orcl1 > alter system reset open_cursors sid='orcl2'

Orcl1 > alter system reset open_cursors sid='*'

Orcl1 > alter system set open_cursors=300 sid='*'

Storage:

SQL > show parameter control_files

SQL > show parameter undo_tablespace

SQL > select GROUP#, THREAD#, STATUS, MEMBERS from v$log

SQL > select GROUP#, MEMBER from v$logfile

SQL > ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 50m

SQL > ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 SIZE 50m

Ocr and votingdisk

# vi / etc/oracle/ocr.loc ocr location

# cd / u01/app/11.2.0/grid/bin

#. / ocrcheck

#. / ocrconfig-showbackup to view automatic backups on the primary node

#. / ocrconfig-manualbackup manual backup, root identity, saved in the primary node

#. / ocrconfig-backuploc... Change the backup path

#. / ocrdump;vi OCRDUMPFILE

# rm OCRDUMPFILE

#. / ocrdump-h

#. / ocrcheck-config

#. / ocrconfig-add + DATA image

#. / ocrcheck-config

# cat / etc/oracle/ocr.loc

#. / ocrconfig-delete + CRS delete the image

Restore:

#. / ocrconfig-add + CRS

#. / ocrconfig-delete + DATA

Olr:

# ll / u01/app/11.2.0/grid/cdata/node1.olr

#. / ocrcheck-local

#. / ocrdump-local / root/node1.olr

# vi / root/node1.olr

#. / ocrconfig-local-manualbackup

Votingdisk:

#. / crsctl query css votedisk

The network

Public and private:

# ifconfig or ip add

# oifcfg getif

Node vip and scanvip:

# ip add

$sqlplus sys/password@192.168.0.1:1521/orcl as sysdba

$sqlplus sys/password@192.168.0.11:1521/orcl as sysdba

$sqlplus sys/password@192.168.0.101:1521/orcl as sysdba assumes 101on node1

# ifdown eth0 node1 close the network card and observe the drift of ip

# ip add or $crsctl stat res-t

$sqlplus sys/password@192.168.0.1:1521/orcl as sysdba

$sqlplus sys/password@192.168.0.11:1521/orcl as sysdba

$sqlplus sys/password@192.168.0.101:1521/orcl as sysdba

# ifup eth0 node1 enable the network card to observe the drift of ip

Test 3 connections again

Listener:

# su-grid

$lsnrctl status

$lsnrctl status listener_scan1/2/3

# su-oracle

$sqlplus / as sysdba

SQL > show parameter listener to view parameters related to monitoring

$srvctl relocate vip (scan)-h

$srvctl relocate scan-I 1-n node1

Automatic Management of private ip (haip):

$crsctl stat res-t-init confirms the haip basic service maintained by ohasd

$ifconfig

$oifcfg getif

$oifcfg iflist-p-n

SQL > select name, ip_address from v$cluster_interconnects

Network modification:

Modify public hostname: reinstall rac

Modify privatehostname:11.2.0.2 before reinstalling rac,11.2.0.2 and change it at / etc/hosts at will

Modify public/privateip: same network, restart rac, different network, modify orc

Modify node vip/scan vip: modify using srvctl

Publiceth0: 192.168.0.1 Compact 24 à 172.16.0.1 Universe 16

Private eth2: 192.168.1.1 Compact 24 à 10.0.0.0 Compact 8

Node vip: 192.168.0.11, 24 à 172.16.0.11, 16

Scan vip: 192.168.0.100 (1 2) à 172.16.0.100 (1 2) / 16

Modify publicip:

# modify publicip in os of vi / etc/sysconfig/network-scripts/ifcfg-eth0 node1/node2

On node1:

# su-grid

$oifcfg getif

$oifcfg delif-global eth0

$oifcfg setif-global eth0/172.16.0.0:public

ASM

ASM=RAID+LVM

SAME

An example of asm:

$ps-ef | grep asm

# su-grid

$sqlplus / as sysasm

+ ASM1 > show parameter memory

+ ASM1 > select component, current_size from v$sga_dynamic_components

+ ASM1 > show parameter listener

Start and shut down the instance:

SQL > startup/shutdown abort starts to stop in the nomount phase

$srvctl start asm

$srvctl stop asm-f

SQL > show parameter

Diskgroup:

Striped RAID 0, mirrored RAID1,RAID1+0

Add 3 1GB hard drives to vb's node1 and share them with node2

# for i in h i j

Do

Echo "KERNEL==\" sd*\ ", BUS==\" scsi\ ", PROGRAM==\" / sbin/scsi_id-- whitelisted-- replace-whitespace-- device=/dev/\ $name\ ", RESULT==\" `/ sbin/scsi_id-- whitelisted-- replace-whitespace-- device=/dev/sd$ i` ", NAME=\" asm-disk$i\ ", OWNER=\" grid\ ", GROUP=\" asmadmin\ ", MODE=\" 0660\ "> > / etc/udev/rules.d/99-oracle-asmdevices.rules

Done

# start_udev;ls / dev/asm* confirm to generate asmdisk.

Use sqlplus/oem/asmca/asmcmd

Grid users log in to the desktop, and asmca creates diskgroup:testdg,normal.

# su-grid

$sqlplus / as sysasm

+ ASM1 > select NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup

$asmcmd

ASMCMD > lsdg

# su-oracle

$sqlplus / as sysdba

Orcl1 > create tablespace tbs01 datafile'+ testdg' size 600m

Orcl1 > select path, failgroup, free_mb from v$asm_disk where group_number=4

Orcl 1 > select NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup

ASMCMD > lsdsk-k

Add / remove disks:

# su-grid

+ ASM1 > show parameter asm_power_limit

+ ASM1 > select name, total_mb, free_mb from v$asm_disk

+ ASM1 > alter diskgroup testdg add disk'/ dev/asm-diskj'

+ ASM1 > select name, total_mb, free_mb from v$asm_disk

+ ASM1 > alter diskgroup testdg drop disk TESTDG_0002

+ ASM1 > select name, total_mb, free_mb from v$asm_disk

Failgroup:

+ ASM1 > select path, FAILGROUP from v$asm_disk where group_number=4

+ ASM1 > alter diskgroup testdg drop disk TESTDG_0002

+ ASM1 > alter diskgroup testdg add failgroup testdg_0000 disk'/ dev/asm-diskj'

+ ASM1 > select name, total_mb, free_mb from v$asm_disk

Oracle user deletes tablespace, grid user asmca deletes testdg,root delete / etc/udev/rules.d/99-oracle-asmdevices.rules last 3 hard drives

SQL > startup mount

SQL > select FILE#, NAME from v$datafile

SQL > alter database datafile 4 offline

SQL > alter database open

SQL > drop tablespace tbs01 force; (including contents and datafiles)

HA and LB

HA of scanvip

Nslookup parsing scan returns 3 ip to implement ha

# su-grid

$srvctl status scan

$srvctl status scan_listener

# su-oracle

$tnsping orcl

# su-grid

$srvctl stop scan_listener-I 1 (23)

$srvctl stop scan-I 1 (23)

Close scanlistener and scanvip one by one and test the ha of the client

# su-grid

$srvctl stop instance-d orcl-I orcl1-o immediate

# su-oracle

$sqlplus sys/password@orcl as sysdba

SQL > select instance_name from v$instance

HA of node vip

Modify tnsnames local resolution on node1 and node2

# su-oracle

$vi $ORACLE_HOME/network/admin/tnsnames.ora

TESTHA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.12) (PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

$sqlplus sys/password@testha as sysdba is tested with the client on node2

Testha > select instance_name from vogue instance; always connect to orcl1

# ifdown eth0 shuts down the network card of node1

$sqlplus sys/password@testha as sysdba is tested with the client on node2

Testha > select instance_name from vault instance; connect to orcl2

Node1 resumes eth0, client reconnects to orcl1

LB of scanvip

Set up multiple session and automatically assign them to orcl1 and orcl2

$sqlplus sys/password@orcl as sysdba

SQL > select instance_name from v$instance

LB of nodevip

# su-oracle

$vi $ORACLE_HOME/network/admin/tnsnames.ora

TESTLB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.12) (PORT = 1521))

(LOAD_BALANCE = yes)

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

Set up multiple session and automatically assign them to orcl1 and orcl2

$sqlplus sys/password@testlb as sysdba

SQL > select instance_name from v$instance

Service

Db name, instance name, global name, sid, service name, db_unique_name

Orcl db name

Orcl global name (service name)

To enquire about existing services:

SQL > select name from v$database

SQL > select instance_name from v$instance

SQL > select name from v$services

$vi tnsnames.ora; lsnrctl status

# su-grid

$srvctl status service-d orcl

Create a service:

# su-oracle

$srvctl add service-d orcl-s testsvc-r orcl1-an orcl2

# su-grid

$srvctl start service-d orcl-s testsvc

$crsctl stat res-t

$lsnrctl status; lsnrctl listener_scan1

# su-oracle

$sqlplus sys/password@scan.test.com:1521/testsvc as sysdba

SQL > select instance_name from v$instance

SQL > select name from v$database

Resolution of adding testsvc to netca

SQL > shutdown immediate turn off orcl1

$sqlplus sys/password@scan.test.com:1521/testsvc as sysdba

SQL > select instance_name from vault instance; connect to orcl2

# su-grid

$crsctl stat res-t

$srvctl status service-d orcl-s testsvc

$srvctl start instance-d orcl-I orcl1

$crsctl stat res-t testsvc is still on orcl2 and will not automatically failback

$srvctl relocate service-d orcl-s testsvc-I orcl2-t orcl1

$crsctl stat res-t

$srvctl stop service-d orcl-s testsvc

$srvctl remove service-d orcl-s testsvc

Resource manager/scheduler/sql trace

Cache fusion

Ops

Memory > network > disk

Node1:

# su-oracle

$sqlplus / as sysdba

Orcl1 > create tablespace tbs01

Orcl1 > create table T1 (x int, y int) tablespace tbs01

Orcl1 > insert into T1 values (1,1)

Orcl1 > insert into T1 values (2,2)

Orcl1 > commit

Orcl1 > select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from T1

Orcl1 > alter system checkpint

Orcl1 > alter system flush buffer_cache

Node1:

Begin

For i in 1..10000 loop

Update T1 set Yellowi where Xero1

End loop

End

/

Node2:

Begin

For i in 1..10000 loop

Update T1 set Yellowi where Xero2

End loop

End

/

Xcurial pirated cr

Add/delete node

Add:

Node3 does all the preparatory work.

Verify on node1:

# su-grid

$cluvfy stage-pre nodeadd-n node3

$. / addNode.sh "CLUSTER_NEW_NODES= {node3}"CLUSTER_NEW_VIRTUAL_HOSTNAMES= {node3-vip}"

Execute root.sh as root on node3

Run dbca on node1 to add a new instance

Crsctl stat res-t

DG installation

Node1 and node2 install os,db software, create snooping, and create orcl databases on node1.

Or

Vb replicates a single instance virtual machine, reinitializes the network card, and renames the two nodes to:

Node1_DG_11gR2_RHEL6u5_x64

Node2_DG_11gR2_RHEL6u5_x64

Node1 restores network settings:

# vi / etc/udev/rules.d/70-persistent-net.rules Delete 2 lines of eth0 and change eth2 to eth0

# vi / etc/sysconfig/network-scripts/ifcfg-eth0 to replace the original mac address

# shutdown-h now

Node2 restores network settings:

# vi / etc/udev/rules.d/70-persistent-net.rules Delete 2 lines of eth0 and change eth2 to eth0

# vi / etc/sysconfig/network-scripts/ifcfg-eth0 to replace the original mac address

# reboot

Oracle logs in to the desktop, dbca deletes the orcl database

Root identity modification hostname and ip:

# vi / etc/sysconfig/network-scripts/ifcfg-eth0 ip changed to 192.168.0.2

# vi / etc/sysconfig/network changed to node2.test.com

# hostname node2.test.com

# vi / etc/hosts

192.168.0.1 node1.test.com node1

192.168.0.2 node2.test.com node2

Temporarily shut down the oracle service (optional):

# chkconfig oracle off

# chkconfig-list oracle

Environment for oracle identity tuning node2:

$vi $ORACLE_HOME/network/admin/listener.ora changed to node2.test.com

$lsnrctl stop; lsnrctl start

$vi ~ oracle/.bash_profile

Export ORACLE_HOSTNAME=node2.test.com

Export ORACLE_SID=orclps

Export ORACLE_UNQNAME=orclps

$. ~ oracle/.bash_profile

Node1 (primary):

# vi / etc/hosts

# su-oracle

SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > alter database open

SQL > alter database force logging

Node1 (primary) modifies the primary database parameters:

SQL > alter system set log_archive_config='dg_config= (orcl,orclps)'

SQL > alter system set log_archive_dest_2='service=orclps async valid_for= (online_logfile,primary_role) db_unique_name=orclps'

Add standby database parameters:

SQL > alter system set fal_server=orclps

SQL > alter system set fal_client=orcl

SQL > alter system set standby_file_management=auto

SQL > alter system set db_file_name_convert='/orclps/','/orcl/' scope=spfile

SQL > alter system set log_file_name_convert='/orclps/','/orcl/' scope=spfile

$vi / u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCLPS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node2.test.com) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclps)

)

)

Node2 (ps):

# su-oracle

$mkdir-p $ORACLE_BASE/fast_recovery_area/orclps

$mkdir-p $ORACLE_BASE/admin/orclps/adump

$mkdir-p $ORACLE_BASE/admin/orclps/dpdump

$mkdir-p $ORACLE_BASE/oradata/orclps

$vi $ORACLE_HOME/network/admin/listener.ora add static registration

SID_LIST_listener=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=orclps)

(SID_NAME=orclps)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

$lsnrctl reload; lsnrctl status

$vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node1.test.com) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

ORCLPS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = node2.test.com) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclps)

)

)

$tnsping orcl test

Generate a password file on node2:

$scp node1:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworclps

Generate pfile on node1:

SQL > create pfile from spfile

Generate spfile on node2:

$scp node1:$ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initorclps.ora

Delete orcl from node2. For the parameters at the beginning, modify the following:

$vi $ORACLE_HOME/dbs/initorclps.ora

* .audit_file_dest='/u01/app/oracle/admin/orclps/adump'

* .audit_trail='db'

* .compatible='11.2.0.0.0'

* .control_files='/u01/app/oracle/oradata/orclps/control01.ctl','/u01/app/oracle/fast_recovery_area/orclps/control02.ctl'

* .db_block_size=8192

* .db_domain=''

* .db_file_name_convert='/orcl/','/orclps/'

* .db_name='orcl'

* .db_unique_name='orclps'

* .db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

* .db_recovery_file_dest_size=4322230272

* .diagnostic_dest='/u01/app/oracle'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=orclpsXDB)'

* .fal_client='ORCLPS'

* .fal_server='ORCL'

* .log_archive_config='dg_config= (orcl,orclps)'

* .log_archive_dest_2='service=orcl async valid_for= (online_logfile,primary_role) db_unique_name=orcl'

* .log_file_name_convert='/orcl/','/orclps/'

* .memory_target=1073741824

* .open_cursors=300

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

SQL > create spfile from pfile

SQL > startup nomount

$rman target sys/password@orcl auxiliary sys/password@orclps

RMAN > duplicate target database for standby from active database dorecover

If the master and slave data directories are the same, nofilenamecheck is required.

SQL > select status from vogue instance; mount status

If you need to start the standby database manually:

SQL > startup nomount

SQL > alter database mount standby database

Node1 (primary) create srl:

SQL > alter database add standby logfile'/ u01/app/oracle/oradata/orcl/srl01 (234). Log' size 50m

Node2 (ps) create srl:

SQL > alter database add standby logfile'/ u01/app/oracle/oradata/orclps/srl01 (234). Log' size 50m

Node2 (ps) enable redoapply:

SQL > alter database recover managed standby database using current logfile disconnect

SQL > select name from v$datafile

SQL > select name from v$tempfile

SQL > select name from v$controlfile

SQL > select member from v$logfile

SQL > select protection_mode, protection_level from v$database

SQL > select sequence#, applied from v$archived_log

Switch logs on SQL > alter system switch logfile; node1

SQL > select sequence#, applied from vault archived log; new archive appears

$vi / u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora delete static registration

Delete init parameter file (optional)

Node2 (ps) modify the startup script:

# vi / etc/init.d/oracle

#! / bin/bash

# chkconfig:35 99 01

Case "$1" in

Start)

Su-oracle-c "sqlplus / nolog" / dev/null

Touch / var/lock/subsys/oracle

Stop)

Su-oracle-c "lsnrctl stop" > / dev/null

Su-oracle-c "sqlplus / nolog" create table T1 (x int)

Test the code:

Node1 (primary):

SQL > insert into T1 values (1)

SQL > commit

Node2 (standby)

SQL > select status, sequence#, block# from v$managed_standby where client_process='LGWR'

Or check under adg

SQL > recover managed standby database cancel

SQL > alter database open

SQL > alter database recover managed standby database using current logfile disconnect

SQL > select * from T1

Maximum performance:

Nod1 (primary)

SQL > alter system set log_archive_dest_2='service=orclps async valid_for= (online_logfile,primary_role) db_unique_name=orclps'

SQL > ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

SQL > select protection_mode, protection_level from v$database

test

Maximum availability:

Node1 (primary)

SQL > alter system set log_archive_dest_2='service=orclps sync affirm net_timeout=30 valid_for= (online_logfile,primary_role) db_unique_name=orclps'

SQL > ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY

SQL > select protection_mode, protection_level from v$database

test

Maximum protection:

Node1 (primary)

SQL > alter system set log_archive_dest_2='service=orclps sync affirm valid_for= (online_logfile,primary_role) db_unique_name=orclps'

SQL > ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION

SQL > select protection_mode, protection_level from v$database

test

Modify the protection mode step by step. There is no need to restart db. Performance à availablity has to wait for resync to complete, and then à protection.

Restore to maximum performance:

SQL > ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

SQL > alter system set log_archive_dest_2='service=orclps async valid_for= (online_logfile,primary_role) db_unique_name=orclps'

Active data guard

Read-only mode is on

Node1 (primary)

SQL > create table T1 (x int); insert into T1 values (1); commit

Node2 (standby)

SQL > select open_mode from vault database; confirm to be mount

SQL > recover managed standby database cancel

SQL > alter database open

SQL > select open_mode from vested database; read only

SQL > select * from T1

SQL > insert into T1 values (2); do it on commit; node1

SQL > select * from T1; out of sync with node1

SQL > delete T1; error report

SQL > alter system switch logfile; node1 switch log

SQL > select sequence#, applied from vault archived log; transfer but not apply

SQL > shutdown immediate

SQL > startup mount

SQL > alter database recover managed standby database using current logfile disconnect

SQL > select sequence#, applied from vault archived log; log of applying node1

Snapshot standby

Node1 (primary)

SQL > create table T1 (x int); insert into T1 values (1); commit

Node2 (standby)

SQL > show parameter db_recovery_file_dest

SQL > recover managed standby database cancel

SQL > shutdown immediate

SQL > startup mount

SQL > alter database convert to snapshot standby

SQL > alter database open

SQL > select open_mode from vested database; read write

$ls / u01/app/oracle/fast_recovery_area/ORCLPS/flashback

SQL > select * from T1

SQL > insert into T1 values (1); commit; node1 modification

SQL > select * from T1 * insert into T1 values (2); commit; cannot see the node1 changes, but can modify them itself

SQL > alter system switch logfile; node1 switch log

SQL > select sequence#, applied from vault archived log; transfer but not apply

SQL > shutdown immediate

SQL > startup mount

SQL > alter database convert to physical standby; node2 lost changes

SQL > shutdown immediate

SQL > startup mount

SQL > alter database recover managed standby database using current logfile disconnect

SQL > select sequence#, applied from vault archived log; node2 application log

Snapshot standby + flashbackdb

Node1 (primary)

SQL > create table T1 (x int); insert into T1 values (1); commit

Node2 (standby)

Confirm that the database is in mount state

SQL > show parameter db_recovery_file_dest

SQL > recover managed standby database cancel

SQL > alter database flashback on

SQL > alter database convert to snapshot standby

SQL > alter database open

SQL > select open_mode from vested database; read write

SQL > insert into T1 values (2); commit; import test data

SQL > create restore point before_test GUARANTEE flashback database

SQL > delete T1 commit

SQL > shutdown immediate

SQL > startup mount

SQL > flashback database to restore point before_test

SQL > alter database open resetlogs

SQL > select * from T1

SQL > shutdown immediate

SQL > startup mount

SQL > alter database convert to physical standby; node2 lost changes

SQL > shutdown immediate

SQL > startup mount

SQL > alter database recover managed standby database using current logfile disconnect

SQL > drop restore point before_test

ADG

Node1 (primary)

SQL > create table T1 (x int); insert into T1 values (1); commit

Node2 (standby)

SQL > select open_mode from vault database; confirm to be mount

SQL > recover managed standby database cancel

SQL > alter database open; or SQL > startup

SQL > alter database recover managed standby database using current logfile disconnect

SQL > insert into T1 values (2); modified on commit; node1

SQL > select * from T1; View real-time data on node2

Role conversion switch over

Preparation of node1 (primary):

Fal_server/fal_client/standby_file_management

Create srl

Node2 (standby)

Log_archive_dest_2/log_archive_config

Node1 (primary) confirms that the log transfer is complete (nogap)

SQL > SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2

Node2 (standby) confirms two lag

SQL > SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS

Node1 (primary)

SQL > SELECT SWITCHOVER_STATUS FROM database; should be TO STANDBY

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

SQL > SHUTDOWN ABORT

Node2 (standby new primary)

SQL > SELECT SWITCHOVER_STATUS FROM database; should be TO PRIMARY

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

SQL > ALTER DATABASE OPEN

Node1 (roomy new standby)

SQL > STARTUP MOUNT

SQL > alter database recover managed standby database using current logfile disconnect

Failover

Node2 (primary) and node1 (standby) enable flashback

Node2 (primary) prepares:

SQL > create table T1 (x int); insert into T1 values (1); commit

Node2 (primary) simulates a failure:

# ifdown eth0 node2 (primary) shuts down the network

SQL > insert into T1 values (2); commit

SQL > shutdown abort

Node1 (standby)

SQL > recover managed standby database cancel

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH

SQL > SELECT SWITCHOVER_STATUS FROM V$DATABASE

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

SQL > ALTER DATABASE OPEN

Using flashback to recover node2 (recovery yearly new standby)

Node1 (new primary)

SQL > SELECT TO_CHAR (STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE

Node2 (new standby)

SQL > startup mount

SQL > FLASHBACK DATABASE TO SCN 1275936

SQL > ALTER DATABASE CONVERT TO PHYSICAL STANDBY

SQL > SHUTDOWN IMMEDIATE

SQL > STARTUP MOUNT

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

Database design

ATM instance based on oracle

Upgrade single instance

11.2.0.3.0 à 11.2.0.4.0

# mkdir / patch

# chmod 777 / patch

# mount-t vboxsf patch / patch

$unzip p13390677_112040_Linux-x86-64 million 1 (2) of7.zip

Out of service:

$emctl stop dbconsole

$lsnrctl stop

$sqlplus / as sysdba

SQL > shutdown immediate

Change the environment:

$vi ~ / .bash_profile

Export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1

$. .bash _ profile desktop environment requires re-login

$echo $ORACLE_HOME

$. / runInstaller

Select upgrade and change the path confirmation to / u01/app/oracle/product/11.2.0.4/db_1

# / u01/app/oracle/product/11.2.0.4/db_1/root.sh prompt to execute script with root

Netca: creating new versions of listener and service parsing

Dbua: do not move files

SQL > select * from version; version of db

SQL > select comp_name, the version of the version from dba_server_registry; component

$rm-rf / u01/app/oracle/product/11.2.0 confirmed success. Delete the old version.

11.2.0.4.0 à 11.2.0.4.8

$$ORACLE_HOME/OPatch/opatch version

$unzip p6880880_112000_Linux-x86-64.zip-d $ORACLE_HOME must be an oracle user

$$ORACLE_HOME/OPatch/opatch version

$cd / patch

$unzip p21352635_112040_Linux-x86-64\ (11.2.0.4.8_db\). Zip must be unzipped with oracle user

$cd 21352635

$emctl stop dbconsole; dbshut $ORACLE_HOME close all components of db

$$ORACLE_HOME/OPatch/opatch apply

Do not enter email, confirm to continue

$dbstart $ORACLE_HOME; emctl start dbconsole

SQL > @? / rdbms/admin/catbundle.sql psu apply

Check:

$$ORACLE_HOME/OPatch/opatch lspatches

SQL > select action, comments from registry$history

DG

Add a database to / etc/oratab on node2

$vi / etc/oratab

Orclps:/u01/app/oracle/product/11.2.0/db_1:Y

Em is not configured on node2

Select install Software only on node1

Copy the listener.ora, sqlnet.ora and tnsnames.ora spfileorcl.ora orapworcl in the path of the old version to the corresponding directory of the new version

Failed to install the software:

$vi / u01/app/oraInventory/ContentsXML/inventory.xml

RAC

Upgrade gi:

Node1 and node2:

# chown grid / u01/app

$su-grid

$vi .bash _ profile

Export ORACLE_HOME=/u01/app/11.2.0.4/grid

$. .bash _ profile

Node1:

Desktop root login

# xhost +

$su-grid

$/ patch/grid/runInstaller

Upgrade gi & asm, test the ssh connection (no configuration), change the path to / u01/app/11.2.0.4/grid

Run rootupgrade.sh on node1 and node2

$crsctl query crs activeversion confirms the new version

Upgrade dbsoftware:

Node1 and node2:

# su-oracle

$vi .bash _ profile

Export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1

$. .bash _ profile

Node1:

$. / runInstaller

Select upgrade existing database, select node1/node2,ssh to enter the password, and change the path to / u01Universe oracleproductUniverse 11.2.0.4 Universe DB2 Node1hand Node2 to execute the root script and dbua to upgrade the database.

Upgrade psu:

Node1 and node2:

Su-grid

Unzip p6880880_112000_linux-x86-64.zip-d $ORACLE_HOME

$ORACLE_HOME/OPatch/opatch version

Su-oracle

Unzip p6880880_112000_linux-x86-64.zip-d $ORACLE_HOME

$ORACLE_HOME/OPatch/opatch version

Node1:

Su-grid

$unzip p21523375_112040_Linux-x86-64 (11.2.0.4.8_gi&db) .zip

Node1 and node2:

Su-oracle

Emctl stop dbconsole

On node1 and node2, root generates ocm response files and applies psu:

# cd / patch/21523375/

# / u01/app/11.2.0.4/grid/OPatch/ocm/bin/emocmrsp-no_banner

# / u01/app/11.2.0.4/grid/OPatch/opatch auto / patch/21523375/-ocmrf / patch/21523375/ocm.rsp

On node1:

Su-oracle

Sqlplus / as sysdba

SQL > @? / rdbms/admin/catbundle.sql psu apply

Check:

$ORACLE_HOME/OPatch/opatch lspatches

Sqlplus / as sysdba

Select * from v$version

Select action,comments from registry$history

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.