📄 备份恢复实验.txt
字号:
switch datafile 7;
recover tablespace users;
alter database open;}
SQL> select * from scott.com2;
N1
----------
1
2.1.4 lab11
没有备份的情况下，恢复一个数据文件，不能是system或者undo段
SQL> drop tablespace testtbs INCLUDING CONTENTS;
SQL> create tablespace testtbs datafile 'D:\oracle\oradata\a00215\testtbs.dbf' size 1m;
SQL> alter system switch logfile;
SQL> create table scott.com4 (n1 number) tablespace testtbs;
SQL> alter system switch logfile;
SQL> insert into scott.com4 values(1);
SQL> commit;
SQL> alter system switch logfile;
假设 testtbs.dbf坏了
我这里采取的办法是先将testtbs.dbf指向另外一个目录。
SQL> alter tablespace testtbs offline immediate;
SQL> alter database create datafile 'D:\oracle\oradata\a00215\testtbs.dbf' as 'D:\oracle\oradata\a00215\u1\testtbs1.dbf';
SQL> select * from scott.com4;
select * from scott.com4
*
ERROR 位于第 1 行:
ORA-00376: 此时无法读取文件 2
ORA-01110: 数据文件 2: 'D:\ORACLE\ORADATA\A00215\TESTTBS.DBF'
恢复
RMAN> run{
2> set newname for datafile 2 to 'D:\oracle\oradata\a00215\u1\testtbs1.dbf';
3> }
RMAN> recover tablespace testtbs ;
SQL> alter tablespace testtbs online;
SQL> select * from scott.com4;
N1
----------
1
恢复成功。
不完全恢复的三个实验
2.2.1 lab12
基于时间的恢复
注册表 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0 确认存在 NLS_DATE_FORMAT=’YYYY-MM-DD:HH24:MI:SS’，如果没有，则加上
SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;
2004-09-26:17:43:43
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
2004-09-26:17:43:43之后，将scott.emp表删除
SQL> drop table scott.emp ;
SQL> create table scott.com1 (n1 number);
SQL> insert into scott.com1 values(1);
SQL> alter system switch logfile;
SQL> select * from scott.com1;
N1
----------
1
将数据库恢复到2004-09-26:17:43:43的时间点
SQL> shutdown abort
SQL> startup mount
RMAN> RUN {
SET UNTIL TIME = '2004-09-26:17:43:42';
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; }
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
SQL> select * from scott.com1;
select * from scott.com1
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
2.2.2 lab13
基于序列号 （sequence number）
做不完全恢复之前，要给数据库做一个备份 这里用rman 做
sequence number是指 redo log的序列号
SQL> create table scott.test1(n1 number);
SQL> insert into scott.test1 values(1);
SQL> insert into scott.test1 values(2);
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into scott.test1 values(3);
SQL> insert into scott.test1 values(4);
SQL> commit;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 661 1048576 1 YES ACTIVE 153702 2004-09-26:18:35:31
2 1 662 1048576 1 NO CURRENT 153866 2004-09-26:18:35:53
3 1 660 1048576 1 YES INACTIVE 132723 2004-09-26:15:40:25
SQL> shutdown abort
2号组为当前组，将其破坏
SQL> startup mount
RUN {
SET UNTIL SEQUENCE 662 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESESTLOGS;}
SQL> select * from scott.test1
2 /
N1
----------
1
2
scott.test1 中3,4没有归档，丢失
2.2.3 lab14
恢复时，使用备份的控制文件
这里必须建立恢复目录catalog，而不能用control file
注，以上的六个实验，也能用catalog再做一遍
准备工作
这里先建一个数据库orcl(用来存放catalog)
创建表空间
SQL> create tablespace rman_ts datafile
2 'D:\oracle\oradata\orcl\rman_ts.dbf' size 20m
3 autoextend on next 1m maxsize unlimited
4 extent management local uniform size 128k;
创建用户
SQL> create user rman_owner identified by rman_owner
2 default tablespace rman_ts
3 quota unlimited on rman_ts;
授权
grant connect,resource,recovery_catalog_owner to rman_owner;
登陆a00215数据库和恢复目录数据库（orcl）
D:\>rman catalog rman_owner/rman_owner@orcl target sys/oracle@a00215
创建恢复目录
RMAN> create catalog tablespace rman_ts
注册在恢复目录中的数据库
RMAN> register database;
给数据库做一个备份
注意：可能需要切换副本
RMAN> list incarnation of database;
数据库 Incarnations 列表
DB 关键字 Inc 关键字 DB 名 DB ID CUR 重置 SCN 重置时间
------- ------- -------- ---------------- --- ---------- ----------
1 11 A00215 3493611191 NO 1 2004-09-26:15:12:55
1 2 A00215 3493611191 YES 153867 2004-09-26:18:42:50
RMAN> reset database to incarnation 11;
SQL> create table scott.test1(n1 number) tablespace users;
SQL> insert into scott.test1 values(1);
SQL> insert into scott.test1 values(2);
SQL> alter system switch logfile
2 /
SQL> drop tablespace users including contents and datafiles;
SQL> create table scott.test2(n1 number) tablespace tools;
SQL> insert into scott.test2 values(2);
SQL> shutdown abort
在 alert_a00215.log里
Sun Sep 26 20:49:23 2004
drop tablespace users including contents and datafiles
恢复到2004-09-26:20:49:22的样子
RMAN> startup nomount
RMAN>
run {
set until time='2004-09-26:20:49:22';
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;}
恢复完之后
SQL> select * from scott.test1
2 /
N1
----------
1
SQL> select * from scott.test2;
select * from scott.test2
*
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -