1. 没有 设置归档 和 备份 的话, Drop 后无法恢复. (Delete 就有 Rollback). 2. select * from v$locked_object; select * from v$transaction; 3. 表 test中记录:MODE BREADTH PCS ----------------------- A 10 5 A 20 10 A 30 3 B 10 2 B 20 NULL B 30 6 C 10 NULL C 20 8BREADTH只有三种值:10、20、30SELECT mode1,SUM(DECODE(breadth,'10',pcs,NULL)) breadth10, SUM(DECODE(breadth,'20',pcs,NULL)) breadth20, SUM(DECODE(breadth,'30',pcs,NULL)) breadth30 FROM test GROUP BY mode1;MODE1 BREADTH10 BREADTH20 BREADTH30 ----- ---------- ---------- ---------- A 5 10 3 B 2 6 C 8
1: 4.4 不完全恢复案例 4.4.1 OS备份下的基于时间的恢复 不完全恢复可以分为基于时间的恢复,基于改变的恢复与基于撤消的恢复,这里已基于时间的恢复为例子来说明不完全恢复过程。 基于时间的恢复可以不完全恢复到现在时间之前的某一个时间,对于某些误操作,如删除了一个数据表,可以在备用恢复环境上恢复到表的删除时间之前,然后把该表导出到正式环境,避免一个人为的错误。 1、连接数据库,创建测试表并插入记录 SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003 (c) Copyright 1999 Oracle Corporation. All rights reserved. SQL> connect internal/password as sysdba; Connected. SQL> create table test(a int); Table created SQL> insert into test values(1); 1 row inserted SQL> commit; Commit complete2、备份数据库,这里最好备份所有的数据文件,包括临时数据文件 SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql 或冷备份也可以3、删除测试表,假定删除前的时间为T1,在删除之前,便于测试,继续插入数据并应用到归档。 SQL> insert into test values(2); 1 row inserted SQL> commit; Commit complete SQL> select * from test; A --------------------------------------- 1 2 SQL> alter system switch logfile; Statement processed. SQL> alter system switch logfile; Statement processed.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2003-05-21 14:43:01 SQL> drop table test; Table dropped.4、准备恢复到时间点T1,找回删除的表,先关闭数据库 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.5、拷贝刚才备份的所有数据文件回来 C:\>copy D:\DATABAK\*.DBF D:\ORACLE\ORADATA\TEST 6、启动到mount下 SQL> startup mount; ORACLE instance started. Total System Global Area 102020364 bytes Fixed Size 70924 bytes Variable Size 85487616 bytes Database Buffers 16384000 bytes Redo Buffers 77824 bytes Database mounted.7、开始不完全恢复数据库到T1时间 SQL> recover database until time '2003-05-21:14:43:01'; ORA-00279: change 30944 generated at 05/21/2003 14:40:06 needed for thread 1 ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC ORA-00280: change 30944 for thread 1 is in sequence #191Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto Log applied. Media recovery complete. 8、打开数据库,检查数据 SQL> alter database open resetlogs;Database altered. SQL> select * from test; A --------------------------------------- 1 2 2: v$transaction3: SQL*PLus> desc emp; 名称 是否为空? 类型 ----------------------------------------- -------- ----------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)SQL*PLus> select job, deptno, count(*) 2 from emp 3 group by job, deptno;JOB DEPTNO COUNT(*) --------- ---------- ---------- CLERK 10 2 CLERK 20 4 CLERK 30 2 ANALYST 20 4 MANAGER 10 2 MANAGER 20 2 MANAGER 30 2 SALESMAN 30 8 PRESIDENT 10 2已选择9行。SQL*PLus> select job, 2 max( decode( deptno, 10, cnt, null ) ) dept_10, 3 max( decode( deptno, 20, cnt, null ) ) dept_20, 4 max( decode( deptno, 30, cnt, null ) ) dept_30, 5 max( decode( deptno, 40, cnt, null ) ) dept_40 6 from ( select job, deptno, count(*) cnt 7 from emp 8 group by job, deptno ) 9 group by job 10 /JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40 --------- ---------- ---------- ---------- ---------- ANALYST 4 CLERK 2 4 2 MANAGER 2 2 2 PRESIDENT 2 SALESMAN 8----------------------------------------------------------------------------------- 各位,我有如下一个工资表,如: 姓名 工资项 工资 张三 基本工资 1000 张三 岗位工资 2000 张三 效益工资 200 李四 基本工资 1000 李四 效益工资 1000 .......我需要用一个SQL语句得到如下所示的结果: 姓名 基本工资 岗位工资 效益工资 ..... 张三 1000 2000 200 ..... 李四 1000 0 1000 ..... .... select 姓名, sum(decode(工资项,'基本工资',工资,0)) 基本工资, sum(decode(工资项,'岗位工资',工资,0)) 岗位工资, .... from yourtable group by 姓名
3、 select 代号,max(decode(名称,'A',单价,0)) a,max(decode(名称,'B',单价,0)) b, sum(数量),sum(金额) from tab group by 代号;
2. select * from v$locked_object;
select * from v$transaction;
3.
表 test中记录:MODE BREADTH PCS
-----------------------
A 10 5
A 20 10
A 30 3
B 10 2
B 20 NULL
B 30 6
C 10 NULL
C 20 8BREADTH只有三种值:10、20、30SELECT mode1,SUM(DECODE(breadth,'10',pcs,NULL)) breadth10,
SUM(DECODE(breadth,'20',pcs,NULL)) breadth20,
SUM(DECODE(breadth,'30',pcs,NULL)) breadth30
FROM test
GROUP BY mode1;MODE1 BREADTH10 BREADTH20 BREADTH30
----- ---------- ---------- ----------
A 5 10 3
B 2 6
C 8
4.4 不完全恢复案例
4.4.1 OS备份下的基于时间的恢复
不完全恢复可以分为基于时间的恢复,基于改变的恢复与基于撤消的恢复,这里已基于时间的恢复为例子来说明不完全恢复过程。
基于时间的恢复可以不完全恢复到现在时间之前的某一个时间,对于某些误操作,如删除了一个数据表,可以在备用恢复环境上恢复到表的删除时间之前,然后把该表导出到正式环境,避免一个人为的错误。
1、连接数据库,创建测试表并插入记录
SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
SQL> connect internal/password as sysdba;
Connected.
SQL> create table test(a int);
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete2、备份数据库,这里最好备份所有的数据文件,包括临时数据文件
SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql
或冷备份也可以3、删除测试表,假定删除前的时间为T1,在删除之前,便于测试,继续插入数据并应用到归档。
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
SQL> alter system switch logfile;
Statement processed.
SQL> alter system switch logfile;
Statement processed.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2003-05-21 14:43:01
SQL> drop table test;
Table dropped.4、准备恢复到时间点T1,找回删除的表,先关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.5、拷贝刚才备份的所有数据文件回来
C:\>copy D:\DATABAK\*.DBF D:\ORACLE\ORADATA\TEST
6、启动到mount下
SQL> startup mount;
ORACLE instance started.
Total System Global Area 102020364 bytes
Fixed Size 70924 bytes
Variable Size 85487616 bytes
Database Buffers 16384000 bytes
Redo Buffers 77824 bytes
Database mounted.7、开始不完全恢复数据库到T1时间
SQL> recover database until time '2003-05-21:14:43:01';
ORA-00279: change 30944 generated at 05/21/2003 14:40:06 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC
ORA-00280: change 30944 for thread 1 is in sequence #191Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
8、打开数据库,检查数据
SQL> alter database open resetlogs;Database altered.
SQL> select * from test;
A
---------------------------------------
1
2
2:
v$transaction3:
SQL*PLus> desc emp;
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)SQL*PLus> select job, deptno, count(*)
2 from emp
3 group by job, deptno;JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 2
CLERK 20 4
CLERK 30 2
ANALYST 20 4
MANAGER 10 2
MANAGER 20 2
MANAGER 30 2
SALESMAN 30 8
PRESIDENT 10 2已选择9行。SQL*PLus> select job,
2 max( decode( deptno, 10, cnt, null ) ) dept_10,
3 max( decode( deptno, 20, cnt, null ) ) dept_20,
4 max( decode( deptno, 30, cnt, null ) ) dept_30,
5 max( decode( deptno, 40, cnt, null ) ) dept_40
6 from ( select job, deptno, count(*) cnt
7 from emp
8 group by job, deptno )
9 group by job
10 /JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40
--------- ---------- ---------- ---------- ----------
ANALYST 4
CLERK 2 4 2
MANAGER 2 2 2
PRESIDENT 2
SALESMAN 8-----------------------------------------------------------------------------------
各位,我有如下一个工资表,如:
姓名 工资项 工资
张三 基本工资 1000
张三 岗位工资 2000
张三 效益工资 200
李四 基本工资 1000
李四 效益工资 1000
.......我需要用一个SQL语句得到如下所示的结果:
姓名 基本工资 岗位工资 效益工资 .....
张三 1000 2000 200 .....
李四 1000 0 1000 .....
....
select 姓名, sum(decode(工资项,'基本工资',工资,0)) 基本工资,
sum(decode(工资项,'岗位工资',工资,0)) 岗位工资, ....
from yourtable
group by 姓名
select 代号,max(decode(名称,'A',单价,0)) a,max(decode(名称,'B',单价,0)) b,
sum(数量),sum(金额) from tab
group by 代号;
-如果有逻辑备分的话,可以使用imp实用程序恢复。