SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 23 20:06:25 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Total System Global Area 229683200 bytes Fixed Size 2251936 bytes Variable Size 171967328 bytes Database Buffers 50331648 bytes Redo Buffers 5132288 bytes
SQL> column name format a30 SQL> column value format a20 SQL> column issys_modifiable format a10 SQL> select name,value,issys_modifiable from v$parameterwhere name like 'log_archive%';
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
--sysdba SQL> conn / as sysdba Connected. SQL> select username,default_tablespace from dba_users where username = 'SCOTT';
USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SCOTT USERS SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='USERS';
TABLESPACE_NAME ------------------------------ FILE_NAME -------------------------------------------------------------------------------- USERS /alidata/oracle/oradata/testdb/users01.dbf SQL> alter tablespace users begin backup;
Tablespace altered.
--scott SQL> insert into dep01 values (50,'DBA','SHANGHAI');
1 row created. SQL> select * from dep01;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DBA SHANGHAI
--scott SQL> insert into dep01 values (52,'DBA02','SHANGHAI');
1 row created.
SQL> commit;
Commit complete.
--sysdba SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/alidata/oracle/oradata/testdb/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Total System Global Area 2137886720 bytes Fixed Size 2215064 bytes Variable Size 1392509800 bytes Database Buffers 738197504 bytes Redo Buffers 4964352 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/alidata/oracle/oradata/testdb/users01.dbf'
SQL> alter database datafile 4 offline;
Database altered. SQL> alter database open;
Database altered. SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ---------- ------- ------- ERROR CHANGE# ----------------------------------------------------------------- ---------- TIME --------- 4 OFFLINE OFFLINE FILE NOT FOUND SQL> !cp /home/oracle/hotbk/users01.dbf /alidata/oracle/oradata/testdb/users01.dbf SQL> recover datafile 4; ORA-00279: change 3571792 generated at 12/01/2019 02:14:56 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_120_1019996090.dbf ORA-00280: change 3571792 for thread 1 is in sequence #120
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00279: change 3572245 generated at 12/01/2019 02:34:20 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_121_1019996090.dbf ORA-00280: change 3572245 for thread 1 is in sequence #121
ORA-00279: change 3572248 generated at 12/01/2019 02:34:22 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_122_1019996090.dbf ORA-00280: change 3572248 for thread 1 is in sequence #122
ORA-00279: change 3572251 generated at 12/01/2019 02:34:26 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_123_1019996090.dbf ORA-00280: change 3572251 for thread 1 is in sequence #123
ORA-00279: change 3572254 generated at 12/01/2019 02:34:28 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_124_1019996090.dbf ORA-00280: change 3572254 for thread 1 is in sequence #124
ORA-00279: change 3572313 generated at 12/01/2019 02:36:16 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_125_1019996090.dbf ORA-00280: change 3572313 for thread 1 is in sequence #125
ORA-00279: change 3572316 generated at 12/01/2019 02:36:17 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_126_1019996090.dbf ORA-00280: change 3572316 for thread 1 is in sequence #126
Log applied. Media recovery complete. SQL> alter database datafile 4 online;
Database altered. SQL> select * from dep01;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DBA SHANGHAI 51 DBA01 SHANGHAI 52 DBA02 SHANGHAI
SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 1 18:49:02 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name from v$instances; select instance_name from v$instances * ERROR at line 1: ORA-00942: table or view does not exist
SQL> create table scott.t01 tablespace booboo as select * from scott.emp;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> insert into scott.t01 select * from scott.t01;
14 rows created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> !rm -rf /home/oracle/booboo.dbf
SQL> startup force ORACLE instance started.
Total System Global Area 2137886720 bytes Fixed Size 2215064 bytes Variable Size 1392509800 bytes Database Buffers 738197504 bytes Redo Buffers 4964352 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/home/oracle/booboo.dbf'
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database create datafile 6 as '/home/oracle/booboo.dbf';
Database altered.
SQL> recover datafile 6; ORA-00279: change 3630702 generated at 12/01/2019 18:53:13 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_131_1019996090.dbf ORA-00280: change 3630702 for thread 1 is in sequence #131
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 3631012 generated at 12/01/2019 18:54:28 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_132_1019996090.dbf ORA-00280: change 3631012 for thread 1 is in sequence #132
ORA-00279: change 3631015 generated at 12/01/2019 18:54:29 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_133_1019996090.dbf ORA-00280: change 3631015 for thread 1 is in sequence #133
ORA-00279: change 3631019 generated at 12/01/2019 18:54:33 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_134_1019996090.dbf ORA-00280: change 3631019 for thread 1 is in sequence #134
ORA-00279: change 3631022 generated at 12/01/2019 18:54:34 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_135_1019996090.dbf ORA-00280: change 3631022 for thread 1 is in sequence #135
ORA-00279: change 3631039 generated at 12/01/2019 18:55:17 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_136_1019996090.dbf ORA-00280: change 3631039 for thread 1 is in sequence #136
ORA-00279: change 3631042 generated at 12/01/2019 18:55:19 needed for thread 1 ORA-00289: suggestion : /home/oracle/arc_testdb_dest11_137_1019996090.dbf ORA-00280: change 3631042 for thread 1 is in sequence #137
Log applied. Media recovery complete. SQL> alter database datafile 6 online;
Database altered.
SQL> select count(*) from scott.t01;
COUNT(*) ---------- 14
实践06-不完全恢复
模拟故障场景
人为误操作后将数据库进行恢复：
在线热备所有表空间
人为误操作将某张表drop
继续正常操作其他表
发现表异常需要进行恢复
恢复数据
确认是否有全备份
确认灾难产生时间点和误操作SQL（dbms_logmnr进行挖掘）
exec dbms_logmnr.add_logfile('/home/oracle/arc_booboo_dest1/1_18_1023917451.dbf',dbms_logmnr.new); exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); select scn,sql_redo from v$logmnr_contents where seg_name='T01'and seg_owner='SCOTT';
恢复数据库到灾难产生时间点recover database using backup controlfile until change 279430;
拷贝需要的归档日志
使用resetlogs方式打开数据库
操作记录
SQL> set linesize 100 SQL> set pagesize 100 select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)|| '!cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)|| 'alter tablespace '||tablespace_name||' end backup;' 4 from dba_data_files;
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'||CHR(10)||'!CP-V'||FILE -------------------------------------------------------------------------- alter tablespace SYSTEM begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/system01.dbf /home/oracle/hotbk/ alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/sysaux01.dbf /home/oracle/hotbk/ alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/undotbs01.dbf /home/oracle/hotbk/ alter tablespace UNDOTBS1 end backup;
alter tablespace USERS begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/users01.dbf /home/oracle/hotbk/ alter tablespace USERS end backup;
alter tablespace SYSTEM begin backup; lter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/undotbs01.dbf /home/oracle/hotbk/ alter tablespace UNDOTBS1 end backup;
alter tablespace USERS begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/system01.dbf /home/oracle/hotbk/ alter tablespace SYSTEM end backup;
alter tablespace SYSAUX begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/sysaux01.dbf /home/oracle/hotbk/ alter tablespace SYSAUX end backup;
Tablespace altered.
SQL> alter tablespace UNDOTBS1 begin backup; `/u01/app/oracle/oradata/BOOBOO/system01.dbf' -> `/home/oracle/hotbk/syste !cp -v /u01/app/oracle/oradata/BOOBOO/undotbs01.dbf /home/oracle/hotbk/ alter tablespace UNDOTBS1 end backup; alter tablespace USERS begin backup; !cp -v /u01/app/oracle/oradata/BOOBOO/users01.dbf /home/oracle/hotbk/ alter tablespace USERS end backup; SQL> Tablespace altered. SQL> SQL> Tablespace altered. SQL> `/u01/app/oracle/oradata/BOOBOO/sysaux01.dbf' -> `/home/oracle/hotbk/
X Y ---------- -------------------- 1 中国 2 巴西 3 美国
SQL> create table t01 as select * from emp;
Table created.
SQL> insert into t01 select * from t01;
14 rows created.
SQL> insert into t01 select * from t01;
28 rows created.
SQL> insert into t01 select * from t01;
56 rows created.
SQL> insert into t01 select * from t01;
112 rows created.
SQL> insert into t01 select * from t01;
224 rows created.
SQL> insert into t01 select * from t01;
448 rows created.
SQL> conn / as sysdba Connected. SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> insert into t01 select *from t01; insert into t01 select *from t01 * ERROR at line 1: ORA-00942: table or view does not exist
SQL> insert into scott.t01 select *from scott.t01;
896 rows created.
SQL> /
1792 rows created.
SQL> /
3584 rows created.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> insert into scott.t01 select *from scott.t01;
7168 rows created.
SQL> alter system switch logfile;
System altered.
SQL> drop table scott.t01;
Table dropped.
SQL> insert into scott.t02 select *from scott.t02; insert into scott.t02 select *from scott.t02 * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C003974) violated
SQL> create table scott.t03 as select * from scott.emp;
Table created.
SQL> insert into scott.t03 select * from scott.t03;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> /
112 rows created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> insert into scott.t03 select * from scott.t03;
224 rows created.
SQL> /
448 rows created.
SQL> /
896 rows created.
SQL> /
1792 rows created.
SQL> /
3584 rows created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select * from scott.t01; select * from scott.t01 * ERROR at line 1: ORA-00942: table or view does not exist
exec dbms_logmnr.add_logfile('/home/oracle/arc_booboo_dest1/1_18_1023917451.dbf',dbms_logmnr.new); exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); select scn,sql_redo from v$logmnr_contents where seg_name='T01'and seg_owner='SCOTT';
SQL>desc v$logmnr_contents Name Null? Type ----------------------------------------------------- -------- ------------------------------------ SCN NUMBER START_SCN NUMBER COMMIT_SCN NUMBER TIMESTAMPDATE START_TIMESTAMP DATE COMMIT_TIMESTAMP DATE XIDUSN NUMBER XIDSLT NUMBER XIDSQN NUMBER XID RAW(8) PXIDUSN NUMBER PXIDSLT NUMBER PXIDSQN NUMBER PXID RAW(8) TX_NAME VARCHAR2(256) OPERATION VARCHAR2(32) OPERATION_CODE NUMBER ROLLBACK NUMBER SEG_OWNER VARCHAR2(32) SEG_NAME VARCHAR2(256) TABLE_NAME VARCHAR2(32) SEG_TYPE NUMBER SEG_TYPE_NAME VARCHAR2(32) TABLE_SPACE VARCHAR2(32) ROW_ID VARCHAR2(18) USERNAME VARCHAR2(30) OS_USERNAME VARCHAR2(4000) MACHINE_NAME VARCHAR2(4000) AUDIT_SESSIONID NUMBER SESSION# NUMBER SERIAL# NUMBER SESSION_INFO VARCHAR2(4000) THREAD# NUMBER SEQUENCE# NUMBER RBASQN NUMBER RBABLK NUMBER RBABYTE NUMBER UBAFIL NUMBER UBABLK NUMBER UBAREC NUMBER UBASQN NUMBER ABS_FILE# NUMBER REL_FILE# NUMBER DATA_BLK# NUMBER DATA_OBJ# NUMBER DATA_OBJV# NUMBER DATA_OBJD# NUMBER SQL_REDO VARCHAR2(4000) SQL_UNDO VARCHAR2(4000) RS_ID VARCHAR2(32) SSN NUMBER CSF NUMBER INFO VARCHAR2(32) STATUS NUMBER REDO_VALUE NUMBER UNDO_VALUE NUMBER SAFE_RESUME_SCN NUMBER CSCN NUMBER OBJECT_ID RAW(16) EDITION_NAME VARCHAR2(30) CLIENT_ID VARCHAR2(64)
在新的表空间中创建表，将表空间写满数据 create table scott.t01 tablespace tbs1 as select * from scott.emp;
切换日志： alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile;
模拟数据增长 insert into scott.t01 select * from scott.t01; commit;
切换日志： alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile;
模拟数据文件丢失 !rm -f /home/oracle/tbs01.dbf
重新启动数据库,发现文件丢失 startup force
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/home/oracle/tbs01.dbf'
将5号文件offline打开数据库 SQL> alter database datafile 5 offline; SQL> alter database open;
根据控制创建崭新的数据文件 alter database create datafile 5 as '/u01/app/oracle/oradata/db01/tbs01.dbf';
使用日志恢复数据文件 SQL> recover datafile 5;
将数据文件online SQL> alter database datafile 5 online;
校验数据： select count(*) from scott.t01; \################################################################################# 热备份全库： select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)|| '!cp -v '||file_name||' /home/oracle/hotbk/'||chr(10)|| 'alter tablespace '||tablespace_name||' end backup;' from dba_data_files;
切换日志： alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile; \################################################################################# 不完全恢复：
1.要有全备 2.要确定灾难产生时间点(log miner) 生成挖掘队列：告诉oracle要对哪些日志进行挖掘 exec dbms_logmnr.add_logfile('/home/oracle/arc_db01_dest1/1_39_924710753.dbf',dbms_logmnr.new); 开始挖掘 exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); 查看挖掘结果 select scn,sql_redo from v$logmnr_contentswhere seg_name='E01' and seg_owner='SCOTT';
SCN ---------- SQL_REDO --------------------------- -------------------- 1032938 drop table scott.e01 purge;