日志挖掘和基于时间点的克隆恢复
2019独角兽企业重金招聘Python工程师标准>>> 
之前我们演示过完全恢复和不完全恢复，在实际生产中往往用户误操作之后并不能准确提供误操作的时间点，所以在这种情况下用上面的不完全恢复就无法操作了。
作为dba可以使用日志挖掘来根据用户提供的部分信息比如表名，表空间名称来找出误操作的时间点或者scn号。
而且在实际生产环境中大部分都是7*24小时运行的，不允许我们对数据库做关闭操作，所以可以使用克隆来操作。
1 先rman 全库备份
RMAN> backup database;Starting backup at 14-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/data/vdb/u01/product/oracle/oradata/myyun1/sysaux01.dbf
input datafile file number=00001 name=/data/vdb/u01/product/oracle/oradata/myyun1/system01.dbf
input datafile file number=00003 name=/data/vdb/u01/product/oracle/oradata/myyun1/undotbs01.dbf
input datafile file number=00005 name=/data/vdb/u01/product/oracle/oradata/myyun1/test.dbf
input datafile file number=00006 name=/data/vdb/u01/product/oracle/oradata/myyun1/test2.dbf
input datafile file number=00004 name=/data/vdb/u01/product/oracle/oradata/myyun1/users01.dbf
input datafile file number=00007 name=/data/vdb/u01/product/oracle/oradata/myyun1/space2.dbf
channel ORA_DISK_1: starting piece 1 at 14-DEC-16
channel ORA_DISK_1: finished piece 1 at 14-DEC-16
piece handle=/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_nnndf_TAG20161214T211757_d52kn5vb_.bkp tag=TAG20161214T211757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-DEC-16
channel ORA_DISK_1: finished piece 1 at 14-DEC-16
piece handle=/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_ncsnf_TAG20161214T211757_d52know4_.bkp tag=TAG20161214T211757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-DEC-162 删除原来的表
select * from tmp_wzl_20161214_1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 3570.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3500.00 20
7839 KING PRESIDENT 1981-11-17 6000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1500.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3500.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
drop table tmp_wzl_20161214_1 purge;
# 表已经删除3 查看当前使用的是哪个日志文件
select group#,status from v$log;
select * from v$logfile;
4 日志挖掘
4.1 设置挖掘的日志文件
SQL> exec dbms_logmnr.add_logfile('/data/vdb/u01/product/oracle/oradata/myyun1/redo01.log');PL/SQL procedure successfully completed.
4.2 开始分析日志数据
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);PL/SQL procedure successfully completed.4.3 查看日志分析后的数据
SQL> select scn,sql_redo,sql_undo from v$logmnr_contents where table_name ='TMP_WZL_20161214_1';SCN SQL_REDO SQL_UNDO
---------- ---------------------------------------------------------------------- ------------------------------------------------------------------------------------------2604438 create table tmp_wzl_20161214_1 as select * from scott.emp;2604749 drop table tmp_wzl_20161214_1 purge
# 能看出来删除表的动作的scn号是2604749 ，克隆恢复的时候从2604748开始4.4 关闭日志挖掘，回收内存
SQL> exec dbms_logmnr.end_logmnr;PL/SQL procedure successfully completed.
5 配置克隆新数据库所需要的环境
5.1 配置监听
保存
监听配置的代码：
[oracle@myyun1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/vdb/u01/product/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(GLOBAL_DBNAME = base1)(ORACLE_HOME = /data/vdb/u01/product/oracle/11g)(SID_NAME = myyun1))(SID_DESC =(GLOBAL_DBNAME = base2)(ORACLE_HOME = /data/vdb/u01/product/oracle/11g)(SID_NAME = tmyyun1)))LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = myyun1)(PORT = 1521)))ADR_BASE_LISTENER = /data/vdb/u01/product/oracletnsnames.ora 文件如下：
[oracle@myyun1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /data/vdb/u01/product/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
b1 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = myyun1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = base1)))b2 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = myyun1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = base2)))启动监听
[oracle@myyun1 admin]$ lsnrctl start
[oracle@myyun1 admin]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-DEC-2016 23:19:42Copyright (c) 1991, 2011, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myyun1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 14-DEC-2016 23:04:12
Uptime 0 days 0 hr. 15 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/vdb/u01/product/oracle/11g/network/admin/listener.ora
Listener Log File /data/vdb/u01/product/oracle/diag/tnslsnr/myyun1/listener/alert/log.xml
Listening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myyun1)(PORT=1521)))
Services Summary...
Service "base1" has 1 instance(s).Instance "myyun1", status UNKNOWN, has 1 handler(s) for this service...
Service "base2" has 1 instance(s).Instance "tmyyun1", status UNKNOWN, has 1 handler(s) for this service...
Service "myyun1.orcl" has 1 instance(s).Instance "myyun1", status READY, has 1 handler(s) for this service...
Service "myyun1XDB.orcl" has 1 instance(s).Instance "myyun1", status READY, has 1 handler(s) for this service...
The command completed successfully
# 新建的两个监听当前显示UNKNOWN 静态监听启动成功检查tns文件是否可用
[oracle@myyun1 admin]$ tnsping b1TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-DEC-2016 23:21:22Copyright (c) 1997, 2011, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myyun1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = base1)))
OK (0 msec)
# 实例1 域名拼成功
[oracle@myyun1 admin]$ tnsping b2TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-DEC-2016 23:22:09Copyright (c) 1997, 2011, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myyun1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = base2)))
OK (0 msec)
#实例2 tnsping 成功6 配置新数据库路径和参数
6.1 新建存放数据文件的路径
[oracle@myyun1 oradata]$ mkdir tmyyun
[oracle@myyun1 fast_recovery_area]$ mkdir tmyyun1
[oracle@myyun1 admin]$ mkdir tmyyun1
[oracle@myyun1 admin]$ ll tmyyun1/
总用量 8
drwxr-xr-x 2 oracle dba 4096 12月 14 23:17 adump
drwxr-xr-x 2 oracle dba 4096 12月 14 23:14 dpdump6.2 新建密码文件
密码文件在./11g/dbs/ 里面
[oracle@myyun1 dbs]$ orapwd file=orapwtmyyun1 password=oracle6.3 新建参数文件
参数文件可以用已经有的参数文件复制一份修改就可以了
[oracle@myyun1 dbs]$ cp initmyyun1.ora inittmyyun1.ora
[oracle@myyun1 dbs]$ cat inittmyyun1.ora
db_block_size=8192
db_name=tmyyun1
control_files=('/data/vdb/u01/product/oracle/oradata/tmyyun1/control01.ctl')
db_recovery_file_dest='/data/vdb/u01/product/oracle/fast_recovery_area'
db_recovery_file_dest_size=4g
undo_tablespace=undotbs17 rman 下进行恢复
7.1 克隆据库启动到nomount状态下
[oracle@myyun1 dbs]$ sqlplus sys/oracle@b2 as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 14 23:43:58 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> startup nomount;
ORACLE instance started.Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
7.2 rman中对两个数据库进行连接
[oracle@myyun1 dbs]$ rman target sys/oracle@b1 auxiliary sys/oracle@b2;Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 14 23:42:03 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: MYYUN1 (not mounted)
connected to auxiliary database (not started)7.3 在rman 中运行下面的脚本
run{
set until scn 2604748;
duplicate target database to tmporcl
db_file_name_convert=('/data/vdb/u01/product/oracle/oradata/myyun1','/data/vdb/u01/product/oracle/oradata/tmyyun1')
logfile
group 1 ('/data/vdb/u01/product/oracle/oradata/tmyyun1/redo01.log') size 50M,
group 2 ('/data/vdb/u01/product/oracle/oradata/tmyyun1/redo02.log') size 50M,
group 3 ('/data/vdb/u01/product/oracle/oradata/tmyyun1/redo03.log') size 50M;
}其中datafile 可以在v$datafile 中查询，logfile 可以在v$logfile中查询
select name from v$datafile;
select * from v$logfile;
等运行完上面编写的脚本（不能出现错误）克隆数据库应该克隆成功了
7.4 检查克隆数据库是否成功
[oracle@myyun1 ~]$ sqlplus sys/oracle@b2 as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 15 00:12:10 2016Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> sart;
SP2-0042: unknown command "sart" - rest of line ignored.
SQL> start ;
SP2-1506: START, @ or @@ command has no arguments
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
Database mounted.
Database opened.
# 数据库挂载，打开成功SQL> select tablespace_name from user_tablespaces;TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST
TEST2
SPACE28 rows selected.SQL> select instance_name from v$instance;INSTANCE_NAME
----------------
tmyyun1SQL>
# 数据库克隆成功SQL> select tablespace_name,table_name from user_tables where tablespace_name = 'SPACE2';TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SPACE2 TMP_20SQL> select tablespace_name,table_name from user_tables where table_name like 'TMP_WZL%';TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYSTEM TMP_WZL_20161214_1
# 表TMP_WZL_20161214_1 之前是被删除的，现在克隆复原了1* select * from TMP_WZL_20161214_1
SQL> /EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 3570 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3500 207839 KING PRESIDENT 17-NOV-81 6000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1500 20EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3500 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.
# 数据也还原可以访问8 用数据传输把克隆数据库里面的删除的数据重新插入到源数据库就可以了。
SQL> select tablespace_name,table_name from user_tables where table_name = 'TMP_WZL_20161214_1';TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYSTEM TMP_WZL_20161214_1用exp 导出表 TMP_WZL_20161214_1 的数据
[oracle@myyun1 document]$ export ORACLE_SID=tmyyun1
[oracle@myyun1 document]$ exp \'/ as sysdba\' file='/tmp/table1.sql' tables=TMP_WZL_20161214_1;Export: Release 11.2.0.3.0 - Production on Thu Dec 15 21:04:28 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)About to export specified tables via Conventional Path ...
. . exporting table TMP_WZL_20161214_1 14 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
# 表导出成功用imp 导入到原来的数据库中
[oracle@myyun1 document]$ export ORACLE_SID=myyun1
[oracle@myyun1 document]$ imp \'/ as sysdba\' file='/tmp/table1.sql' tables=TMP_WZL_20161214_1;Import: Release 11.2.0.3.0 - Production on Thu Dec 15 21:06:34 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "TMP_WZL_20161214_1" 14 rows imported
Import terminated successfully without warnings.
检查原来数据库中是否把表TMP_WZL_20161214_1 恢复了
SQL> l1* select * from TMP_WZL_20161214_1
SQL> /EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 3570 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3500 207839 KING PRESIDENT 17-NOV-81 6000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1500 20EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3500 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.
SQL> select instance_name from v$instance;INSTANCE_NAME
----------------
myyun1
# 删除的表已经恢复成功
转载于:https://my.oschina.net/wangzilong/blog/806101
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！