之前我们演示过完全恢复和不完全恢复，在实际生产中往往用户误操作之后并不能准确提供误操作的时间点，所以在这种情况下用上面的不完全恢复就无法操作了。 作为dba可以使用日志挖掘来根据用户提供的部分信息比如
之前我们演示过完全恢复和不完全恢复，在实际生产中往往用户误操作之后并不能准确提供误操作的时间点，所以在这种情况下用上面的不完全恢复就无法操作了。
作为dba可以使用日志挖掘来根据用户提供的部分信息比如表名，表空间名称来找出误操作的时间点或者scn号。
而且在实际生产环境中大部分都是7*24小时运行的，不允许我们对数据库做关闭操作，所以可以使用克隆来操作。
1 先rman 全库备份
RMAN> backup database;Starting backup at 14-DEC-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=41 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00002 name=/data/vdb/u01/product/oracle/oradata/myyun1/sysaux01.dbfinput datafile file number=00001 name=/data/vdb/u01/product/oracle/oradata/myyun1/system01.dbfinput datafile file number=00003 name=/data/vdb/u01/product/oracle/oradata/myyun1/undotbs01.dbfinput datafile file number=00005 name=/data/vdb/u01/product/oracle/oradata/myyun1/test.dbfinput datafile file number=00006 name=/data/vdb/u01/product/oracle/oradata/myyun1/test2.dbfinput datafile file number=00004 name=/data/vdb/u01/product/oracle/oradata/myyun1/users01.dbfinput datafile file number=00007 name=/data/vdb/u01/product/oracle/oradata/myyun1/space2.dbfchannel ORA_DISK_1: starting piece 1 at 14-DEC-16channel ORA_DISK_1: finished piece 1 at 14-DEC-16piece handle=/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_nnndf_TAG20161214T211757_d52kn5vb_.bkp tag=TAG20161214T211757 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 14-DEC-16channel ORA_DISK_1: finished piece 1 at 14-DEC-16piece handle=/data/vdb/u01/product/oracle/fast_recovery_area/MYYUN1/backupset/2016_12_14/o1_mf_ncsnf_TAG20161214T211757_d52know4_.bkp tag=TAG20161214T211757 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 14-DEC-162 删除原来的表
select * from tmp_wzl_20161214_1;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH CLERK 7902 1980-12-17 800.00 207499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 307521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 307566 JONES MANAGER 7839 1981-04-02 3570.00 207654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 307698 BLAKE MANAGER 7839 1981-05-01 2850.00 307782 CLARK MANAGER 7839 1981-06-09 2450.00 107788 SCOTT ANALYST 7566 1987-04-19 3500.00 207839 KING PRESIDENT 1981-11-17 6000.00 107844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 307876 ADAMS CLERK 7788 1987-05-23 1500.00 207900 JAMES CLERK 7698 1981-12-03 950.00 307902 FORD ANALYST 7566 1981-12-03 3500.00 207934 MILLER CLERK 7782 1982-01-23 1300.00 10drop 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 LISTENERVersion TNSLSNR for Linux: Version 11.2.0.3.0 - ProductionStart Date 14-DEC-2016 23:04:12Uptime 0 days 0 hr. 15 min. 30 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /data/vdb/u01/product/oracle/11g/network/admin/listener.oraListener Log File /data/vdb/u01/product/oracle/diag/tnslsnr/myyun1/listener/alert/log.xmlListening 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 aliasAttempting 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 aliasAttempting 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/总用量 8drwxr-xr-x 2 oracle dba 4096 12月 14 23:17 adumpdrwxr-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=8192db_name=tmyyun1control_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=4gundo_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 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytes7.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 tmporcldb_file_name_convert=('/data/vdb/u01/product/oracle/oradata/myyun1','/data/vdb/u01/product/oracle/oradata/tmyyun1')logfilegroup 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 ProductionWith 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 argumentsSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 238034944 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytesDatabase mounted.Database opened.# 数据库挂载，打开成功SQL> select tablespace_name from user_tablespaces;TABLESPACE_NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSTESTTEST2SPACE28 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_1SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 3570 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3500 20 7839 KING PRESIDENT 17-NOV-81 6000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1500 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3500 20 7934 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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and UTF8 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)About to export specified tables via Conventional Path .... . exporting table TMP_WZL_20161214_1 14 rows exportedEXP-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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport done in US7ASCII character set and UTF8 NCHAR character setimport 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 importedImport terminated successfully without warnings.检查原来数据库中是否把表TMP_WZL_20161214_1 恢复了
SQL> l 1* select * from TMP_WZL_20161214_1SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 3570 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3500 20 7839 KING PRESIDENT 17-NOV-81 6000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1500 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3500 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> select instance_name from v$instance;INSTANCE_NAME----------------myyun1# 删除的表已经恢复成功
