[oracle实验] Locking:数据一致性...-oracle恢复删除的数据-怎样在RedHat Linux上使用oracle-validated包

当前位置: 数据库>oracle

本页文章导读:

▪[oracle实验] Locking:数据一致性和完整性公车上看concept，有关oracle锁机制，跟MSSQL有些不同，抽空坐下实验验证一下oracle通过锁机制在事务间提供数据并发、一致性和完整性，这些操作自动执行，无需用户干预。情景模拟：多个用户.........

▪oracle恢复删除的数据分为两种方法：scn和时间戳两种方法恢复。一、通过scn恢复删除且已提交的数据　　1、获得当前数据库的scn号　　　　select current_scn from v$database; (切换到sys用户或system用户查询) 　　　　.........

▪怎样在RedHat Linux上使用oracle-validated包首先，参考官方文档：http://docs.oracle.com/cd/B28359_01/install.111/b32002/install_overview.htmIf you are not a ULN customer, and you are running Red Hat Enterprise Linux or Oracle Linux, then you can obtain the Oracle Validated RPM at the fo.........

[1][oracle实验] Locking:数据一致性和完整性

来源: 发布时间: 2013-10-29

公车上看concept，有关oracle锁机制，跟MSSQL有些不同，抽空坐下实验验证一下

oracle通过锁机制在事务间提供数据并发、一致性和完整性，这些操作自动执行，无需用户干预。
情景模拟：多个用户并发修改数据表的某一行。这里实验一个B/S应用，多用户环境使用下列语句修改 HR.EMPLOYEES表

UPDATE employees
SET    email = ?, phone_number = ?
WHERE  employee_id = ?
AND    email = ?
AND    phone_number = ?

这个语句确保在应用程序查询并显示给终端用户之后，正在修改的employee_id数据不会被修改。这样，应用程序避免出现一个用户覆盖了另一个用户做出的修改的问题，或叫lost update

跟着下表操作验证：

时间
Session 1
Session 2
解释

t0

SELECT employee_id, email, 
       phone_number 
FROM   hr.employees 
WHERE  last_name = 'Himuro';
EMPLOYEE_ID EMAIL   PHONE_NUMBER
----------- ------- ------------        
118 GHIMURO 515.127.4565

In session 1, the hr1 user queries
hr.employees for the Himuro record
and displays the employee_id (118),
email (GHIMURO), and phone number
(515.127.4565) attributes.

SELECT employee_id, email, 
       phone_number 
FROM   hr.employees 
WHERE  last_name = 'Himuro';
EMPLOYEE_ID EMAIL   PHONE_NUMBER
----------- ------- ------------        
118 GHIMURO 515.127.4565

In session 2, the hr2 user queries
hr.employees for the Himuro record
and displays the employee_id (118),
email (GHIMURO), and phone number
(515.127.4565) attributes.

UPDATE hr.employees 
SET phone_number='515.555.1234' 
WHERE employee_id=118
AND email='GHIMURO'
AND phone_number='515.127.4565';
1 row updated.

In session 1, the hr1 user updates the
phone number in the row to
515.555.1234, which acquires a lock on
the GHIMURO row.

UPDATE hr.employees 
SET phone_number='515.555.1235' 
WHERE employee_id=118
AND email='GHIMURO'
AND phone_number='515.127.4565';
-- SQL*Plus does not show
-- a row updated message or
-- return the prompt.

In session 2, the hr2 user attempts to
update the same row, but is blocked
because hr1 is currently processing the
row.
The attempted update by hr2 occurs
almost simultaneously with the hr1
update.

COMMIT;
Commit complete.

In session 1, the hr1 user commits the
transaction.
The commit makes the change for
Himuro permanent and unblocks
session 2, which has been waiting.

t5

0 rows updated.
In session 2, the hr2 user discovers that

the GHIMURO row was modified in such a
way that it no longer matches its
predicate.
Because the predicates do not match,
session 2 updates no records.

UPDATE hr.employees 
SET phone_number='515.555.1235' 
WHERE employee_id=118
AND email='GHIMURO'
AND phone_number='515.555.1234';
1 row updated.

In session 1, the hr1 user realizes that it
updated the GHIMURO row with the
wrong phone number. The user starts a
new transaction and updates the phone
number in the row to 515.555.1235,
which locks the GHIMURO row.

SELECT employee_id, email, 
       phone_number 
FROM   hr.employees 
WHERE  last_name = '

[2]oracle恢复删除的数据

来源: 发布时间: 2013-10-29

分为两种方法：scn和时间戳两种方法恢复。

一、通过scn恢复删除且已提交的数据

　　1、获得当前数据库的scn号

　　　　select current_scn from v$database; (切换到sys用户或system用户查询)

　　　　查询到的scn号为：1499223

　　2、查询当前scn号之前的scn

　　　　select * from 表名 as of scn 1499220; (确定删除的数据是否存在，如果存在，则恢复数据；如果不是，则继续缩小scn号)

　　3、恢复删除且已提交的数据

　　　　flashback table 表名 to scn 1499220;

二、通过时间恢复删除且已提交的数据

　　1、查询当前系统时间

　　　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

　　2、查询删除数据的时间点的数据

　　　　select * from 表名 as of timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss'); (如果不是，则继续缩小范围)

　　3、恢复删除且已提交的数据

　　　　flashback table 表名 to timestamp to_timestamp('2013-05-29 15:29:00','yyyy-mm-dd hh24:mi:ss');

　　　　注意：如果在执行上面的语句，出现错误。可以尝试执行 alter table 表名 enable row movement; //允许更改时间戳

本文链接

[3]怎样在RedHat Linux上使用oracle-validated包

来源: 发布时间: 2013-10-29

首先，参考官方文档：http://docs.oracle.com/cd/B28359_01/install.111/b32002/install_overview.htm

If you are not a ULN customer, and you are running Red Hat Enterprise Linux or Oracle Linux, then you can obtain the Oracle Validated RPM at the following URLs:

Oracle Linux 4:

http://oss.oracle.com/el4/oracle-validated/

Oracle Linux 5:

http://oss.oracle.com/el5/oracle-validated/

1.下载 oracle-validated package

[root@ora10g ~]# cd /tmp
[root@ora10g tmp]# wget https://oss.oracle.com/el5/oracle-validated/oracle-validated-1.0.0-18.el5.x86_64.rpm
--2013-05-29 22:01:42--  https://oss.oracle.com/el5/oracle-validated/oracle-validated-1.0.0-18.el5.x86_64.rpm
Resolving oss.oracle.com... 137.254.17.12
Connecting to oss.oracle.com|137.254.17.12|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 15224 (15K) [application/x-rpm]
Saving to: `oracle-validated-1.0.0-18.el5.x86_64.rpm'
100%[========================================>] 15,224      66.6K/s   in 0.2s    
2013-05-29 22:01:58 (66.6 KB/s) - `oracle-validated-1.0.0-18.el5.x86_64.rpm' saved [15224/15224]

2.安装package:

[root@ora10g tmp]# yum install oracle-validated-1.0.0-18.el5.x86_64.rpm

可能需要导入RPM GPG KEY：

rpm --import http://oss.oracle.com/el5/RPM-GPG-KEY-oracle

包安装完毕后，参数已修改：

[root@ora10g tmp]# ls -l /etc/sysctl.conf*
-rw-r--r-- 1 root root 1465 May 29 22:02 /etc/sysctl.conf
-rw-r--r-- 1 root root  996 May 29 21:59 /etc/sysctl.conf.orabackup
[root@ora10g tmp]# dff /etc/sysctl.conf /etc/sysctl.conf.orabackup 
-bash: dff: command not found
[root@ora10g tmp]# diff /etc/sysctl.conf /etc/sysctl.conf.orabackup 
16c16
< kernel.sysrq = 1
---
> kernel.sysrq = 0
29c29
< kernel.msgmax = 8192
---
> kernel.msgmax = 65536
32c32
< kernel.shmmax = 4398046511104
---
> kernel.shmmax = 68719476736
35,48c35
< kernel.shmall = 1073741824
< fs.file-max = 327679
< kernel.msgmni = 2878
< kernel.sem = 250 32000 100 142
< kernel.shmmni = 4096
< net.core.rmem_default = 262144
< net.core.rmem_max = 4194304
< net.core.wmem_default = 262144
< net.core.wmem_max = 262144
< fs.aio-max-nr = 3145728
< net.ipv4.ip_local_port_range = 1024 65000
< # For 11g recommended value for net.core.rmem_max is 4194304
< # For 10g uncomment the following line, comment other entries for this parameter and re-run sysctl -p
< # net.core.rmem_max=2097152
---
> kernel.shmall = 4294967296

/etc/security/limits.conf：

[root@ora10g tmp]# ls -l /etc/security/limits.conf*
-rw-r--r-- 1 root root 2059 May 29 22:02 /etc/security/limits.conf
-rw-r--r-- 1 root root 1789 May 29 21:59 /etc/security/limits.conf.orabackup
[root@ora10g tmp]# diff /etc/security/limits.conf /etc/security/limits.conf.orabackup
44,51d43
< oracle   soft   nofile    131072
< oracle   hard   nofile    131072
< oracle   soft   nproc    131072
< oracle   hard   nproc    131072
< oracle   soft   core    unlimited
< oracle   hard   core    unlimited
< oracle   soft   memlock    50000000
< oracle   hard   memlock    50000000

附上本地yum配置：/etc/yum.repos.d/local.repo

[local_yum_source]
name=my_local_source
baseurl=file:///media/disk/Server
gpgcheck=0
enable=1
gpgkey=file:///etc/yum.repos.d/RPM-GPG-KEY-oracle

感谢 http://perumal.org/prepare-your-linux-server-to-install-oracle-database-using-oracle-validated-rpm/