问题:两个关键表,在不同表空间,都是业务表数据关键实时更新,一个表突然没了,只有一个表,检查发现有人误操作,删除了表空间,恢复数据。
诊断:基于时间点不完全恢复,alert或logmnr来找到删除的时间;flashback database;Tablespace Point-in-Time Recovery表空间时间点恢复
表空间不完全恢复TSPITR
==============
(1)环境准备,归档模式,建立两个表空间,做个冷备或热备
C:\Documents and Settings\Administrator>set nls_lang=american_america.ZHS16GBK
C:\Documents and Settings\Administrator>set oracle_sid=ymh
C:\Documents and Settings\Administrator>sqlplus sys/ymh as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 24 16:28:06 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set sqlpr "主库ymh SQL> "
主库ymh SQL> COL FILE# FORMAT 999
主库ymh SQL> COL STATUS FORMAT 999
主库ymh SQL> COL ERROR FORMAT A20
主库ymh SQL> COL TABLESPACE_NAME FORMAT A10
主库ymh SQL> COL datafile FORMAT A40
主库ymh SQL> COL NAME FORMAT A60
主库ymh SQL> COL MEMBER FOR A60
主库ymh SQL> COL DNAME FOR A50
主库ymh SQL> set pagesize 300
主库ymh SQL> set linesize 400
主库ymh SQL> set pause off
主库ymh SQL> set serveroutput on
主库ymh SQL> set feedback on
主库ymh SQL> set echo on
主库ymh SQL> set numformat 999999999999999
主库ymh SQL> column today new_val dt
主库ymh SQL>
主库ymh SQL> drop tablespace tbs01 including contents and datafiles;
drop tablespace tbs01 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'TBS01' does not exist
主库ymh SQL> drop tablespace tbs02 including contents and datafiles;
Tablespace dropped.
主库ymh SQL> create tablespace tbs01 datafile 'D:\oracle\product\10.2.0\oradata\ymh\TBS01.dbf' size 10M reuse;
Tablespace created.
主库ymh SQL> create tablespace tbs02 datafile 'D:\oracle\product\10.2.0\oradata\ymh\TBS02.dbf' size 10M reuse;
Tablespace created.
主库ymh SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# NAME
----- ------- ------------------ ------------------------------------------------------------
1 SYSTEM 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\SYSTEM01.DBF
2 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TEST2.DBF
3 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\SYSAUX01.DBF
4 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\USERS01.DBF
5 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TEST_DICT.DBF
6 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TEST_DICT.DBF2
7 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TEST_LOCAL.DBF
8 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TEST_LOCAL2.DBF
9 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\YMH_TEST.DBF
10 ONLINE 2037661 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\UNDOTBS2.DBF
11 ONLINE 2037836 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TBS01.DBF
12 ONLINE 2037868 D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TBS02.DBF
12 rows selected.
主库ymh SQL> alter database backup controlfile to trace as 'd:\controlfile.sql' reuse;
Database altered.
主库ymh SQL> create pfile='d:\clone.ora' from spfile;
File created.
<== 备份下控制文件和pfile参数文件,再做个全库冷备份
(2)创建测试用户,在两个表空间上建立两张测试表,插入测试数据
主库ymh SQL> drop user u01;
User dropped.
主库ymh SQL> create user u01 identified by u01;<== 建立用户u01进行测试
User created.
主库ymh SQL> grant connect,resource to u01;
Grant succeeded.
主库ymh SQL>
主库ymh SQL> conn u01/u01<== 切换到用户u01
Connected.
主库ymh SQL>
主库ymh SQL> create table timer1 (a number(5),b date) tablespace tbs01;
Table created.
主库ymh SQL> create table timer2 (a number(5),b date) tablespace tbs02;
Table created.
主库ymh SQL> insert into timer1 values(1,sysdate);
1 row created.
主库ymh SQL> insert into timer2 values(1,sysdate);
1 row created.
主库ymh SQL> commit;
Commit complete.
主库ymh SQL> insert into timer1 values(2,sysdate);
1 row created.
主库ymh SQL> insert into timer2 values(2,sysdate);
1 row created.
主库ymh SQL> commit;
Commit complete.
主库ymh SQL> select a,to_char(b,'hh24:mi:ss') from timer1 order by 1;
A TO_CHAR(
---------- --------
1 21:18:05
2 21:18:18
主库ymh SQL> select a,to_char(b,'hh24:mi:ss') from timer2 order by 1;
A TO_CHAR(
---------- --------
1 21:18:09
2 21:18:22
主库ymh SQL>
(3)删除第一张测试表以及所在表空间,插入另一张测试表新数据
主库ymh SQL> drop table timer1;
Table dropped.
主库ymh SQL> insert into timer2 values(3,sysdate);
1 row created.
主库ymh SQL> insert into timer2 values(4,sysdate);
1 row created.
主库ymh SQL> commit;
Commit complete.
主库ymh SQL> select a,to_char(b,'hh24:mi:ss') from timer1 order by 1;
select a,to_char(b,'hh24:mi:ss') from timer1 order by 1
*
ERROR at line 1:
ORA-00942: table or view does not exist
主库ymh SQL> select a,to_char(b,'hh24:mi:ss') from timer2 order by 1;
A TO_CHAR(
---------- --------
1 21:18:09
2 21:18:22
3 21:26:03
4 21:26:08
4 rows selected.
主库ymh SQL> conn sys/ymh as sysdba;
Connected.
主库ymh SQL> drop tablespace tbs01;
Tablespace dropped.
主库ymh SQL>
(4)检查alert_ymh.log日志
Wed Aug 24 21:19:48 2011
Shutting down archive processes
Wed Aug 24 21:19:53 2011
ARCH shutting down
ARC2: Archival stopped
Wed Aug 24 21:26:37 2011
drop tablespace tbs01
Completed: drop tablespace tbs01
=========》表空间删除可以在alert_ymh.log日志中找到
=========》前面的表删除属于DML操作,在警告日志中没有记录,需要使用logmnr日志挖掘
<== 下面我们运用克隆数据库和传输表空间的方法来实现
(5)logmnr日志挖掘查找表的删除时间
主库ymh SQL> conn sys/ymh as sysdba;
Connected.
主库ymh SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\REDO01.LOG' ,options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
主库ymh SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\REDO02.LOG' ,options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
主库ymh SQL> execute dbms_logmnr.add_logfile(logfilename=>'D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\REDO03.LOG' ,options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.========》增加日志
主库ymh SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.========》开始分析
主库ymh SQL> create table myredolog as select * from v$logmnr_contents;
Table created.======》保存到其他表,在内存中关闭后就丢失
主库ymh SQL> execute dbms_logmnr.end_logmnr();=======》关闭
PL/SQL procedure successfully completed.
主库ymh SQL> col username for a10
主库ymhSQL> col seg_owner for a10
主库ymh SQL> col sql_redo for a70
主库ymh SQL> col sql_undo for a70
主库ymh SQL> select username,seg_owner,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:mm') time,sql_redo
from myredolog whereseg_owner='U01' and peration='DDL';
USERNAME SEG_OWNER SCN TIME SQL_REDO
---------- ---------- -------- ------------------- ----------------------------------------------------------
SYS U01 2068081 2011-08-24 21:17:08 create user u01 identified by VALUES '04A4E8316659C6D5' ;
U01 2068103 2011-08-24 21:17:08 create table timer1 (a number(5),b date) tablespace tbs01;
U01 2068116 2011-08-24 21:18:08 create table timer2 (a number(5),b date) tablespace tbs02;
U01 2068370 2011-08-24 21:25:08 ALTER TABLE "U01"."TIMER1" RENAME TO "BIN$K0bvzduATju0C2v2eLht4w==$0" ;
U01 2068373 2011-08-24 21:25:08 drop table timer1 AS "BIN$K0bvzduATju0C2v2eLht4w==$0" ;
SYS U01 2068395 2011-08-24 21:26:08 drop table "U01"."BIN$K0bvzduATju0C2v2eLht4w==$0" purge;
主库ymh SQL> select to_char(scn_to_timestamp(2068373),'yyyy-mm-dd hh24:mi:ss') as time from dual;
IME
------------------
011-08-24 21:25:58
主库ymh SQL> select timestamp_to_scn(to_date('2011-08-24 21:24:00','yyyy-mm-dd hh24:mi:ss')) as scn from dual;
SCN
----------
2068284
主库ymh SQL> alter system switch logfile;
System altered.
主库ymh SQL> alter system switch logfile;
System altered.
主库ymh SQL> alter system archive log current;
System altered.
主库ymh SQL> alter system archive log current;
System altered.
主库ymh SQL>
(6)修改之前备份的pfile文件
修改这个pfile文件,如下:
clone.__db_cache_size=327155712
clone.__java_pool_size=4194304
clone.__large_pool_size=4194304
clone.__shared_pool_size=268435456
clone.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0\admin\clone\adump'
*.background_dump_dest='D:\oracle\product\10.2.0\admin\clone\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0\oradata\clone\control01.ctl','D:\oracle\product\10.2.0\oradata\clone\control02.ctl','D:\oracle\product\10.2.0\oradata\clone\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0\admin\clone\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ymh'===========》这里的db_name要和原来的保持一致,因为启动时会检查控制文件、数据文件、日志文件的db_name是否相同,所以不能改
*.db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area'=======>我的归档在默认路径下,所以这里不该,后面也不拷贝
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloneXDB)'
*.job_queue_processes=10
*.log_checkpoints_to_alert=TRUE
*.nls_date_format='DD-MON-RR'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.nls_time_format='HH.MI.SSXFF AM'
*.open_cursors=300
*.parallel_max_servers=8
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='clone'
*.sga_target=612368384
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='D:\oracle\product\10.2.0\admin\clone\udump'
<== 注意,由于本实验的克隆数据库与生产库位于同一台主机,那么我们需要修改的部分有db_name、instance_name、以及路径信息
<==9i下另外加上一行*.lock_name_space=CLONE,表示锁定这个是克隆库,10G下该参数废弃不用加
(7)创建一个克隆库的服务(windows下)
C:\Documents and Settings\Administrator>oradim -new -sid clone -intpwd clone
Instance created.
=========》可以看到创建了一个服务, windows专用,unix不需要
(8)然后创建参数文件中的路径目录,将数据文件、归档日志,在线日志文件、控制文件等拷贝过去,总之是完全的复制克隆
如果这样重建控制文件后打开就是完全克隆,打开的结果与正式库一样,表被删除,这里主要是为了在克隆库上恢复到表被删除之前,因此还需要将之前的备份
的数据文件拷贝到数据文件目录下进行覆盖,再利用日志进行不完全恢复,恢复到表被删除前的时间点,不需要要使用备份的控制文件,因为这里的路径等
与正式库不同,需要重建数据库,如果是在另一台服务器上,路径完全相同,则可以使用备份的控制文件。
(9)用pfile启动克隆库到nomount,拷回之前的冷备
C:\Documents and Settings\Administrator>set nls_lang=american_america.ZHS16GBK
C:\Documents and Settings\Administrator>set oracle_sid=clone
C:\Documents and Settings\Administrator>sqlplus sys/clone as sysdba;
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 24 19:23:31 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> set sqlpr "克隆clone SQL> "
克隆clone SQL> COL FILE# FORMAT 999
克隆clone SQL> COL STATUS FORMAT 999
克隆clone SQL> COL ERROR FORMAT A20
克隆clone SQL> COL TABLESPACE_NAME FORMAT A10
克隆clone SQL> COL datafile FORMAT A40
克隆clone SQL> COL NAME FORMAT A60
克隆clone SQL> COL MEMBER FOR A60
克隆clone SQL> COL DNAME FOR A50
克隆clone SQL> set pagesize 300
克隆clone SQL> set linesize 400
克隆clone SQL> set pause off
克隆clone SQL> set serveroutput on
克隆clone SQL> set feedback on
克隆clone SQL> set echo on
克隆clone SQL> set numformat 999999999999999
克隆clone SQL> column today new_val dt
克隆clone SQL>
克隆clone SQL>
克隆clone SQL> startup nomount pfile='d:\clone.ora'
ORA-32006: LOCK_NAME_SPACE initialization parameter has been deprecated====》10g下LOCK_NAME_SPACE参数已经过时了
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 297798532 bytes
Database Buffers 306184192 bytes
Redo Buffers 7135232 bytes
克隆clone SQL> @D:\02.log
克隆clone SQL> host copy D:\backup_dir\SYSAUX01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSAUX01.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\SYSTEM01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSTEM01.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\TBS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TBS01.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\TBS02.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TBS02.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\TEST2.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST2.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\TEST_DICT.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_DICT.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\TEST_DICT.DBF2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_DICT.DBF2
已复制 1 个文件。
克隆clone SQL> --TEST_LOCAL
克隆clone SQL> host copy D:\backup_dir\TEST_LOCAL2.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_LOCAL2.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\TEST_LOCAL.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_LOCAL.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\UNDOTBS2.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\UNDOTBS2.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\USERS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\USERS01.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\YMH_TEST.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\YMH_TEST.DBF
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\CONTROL01.CTL D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\CONTROL01.CTL
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\CONTROL02.CTL D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\CONTROL02.CTL
已复制 1 个文件。
克隆clone SQL> host copy D:\backup_dir\CONTROL03.CTL D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\CONTROL03.CTL
已复制 1 个文件。
(10)重建控制文件
克隆clone SQL> CREATE CONTROLFILE REUSE DATABASE "YMH" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\REDO03.LOG' SIZE 50M
11 DATAFILE
12 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\SYSTEM01.DBF',
13 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST2.DBF',
14 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\SYSAUX01.DBF',
15 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\USERS01.DBF',
16 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_DICT.DBF',
17 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_DICT.DBF2',
18 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_LOCAL.DBF',
19 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_LOCAL2.DBF',
21 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\UNDOTBS2.DBF',
22 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TBS02.DBF'
23 CHARACTER SET ZHS16GBK
24 ;
CREATE CONTROLFILE REUSE DATABASE "YMH" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01158: database already mounted
========》因为同一台机器上主库和克隆库的db_name相同,要先把原来的关闭,同时造成了主库的控制文件被重建,打开时要介质恢复一下
克隆clone SQL>host net stop oracleserviceymh;
OracleServiceYMH 服务正在停止................
OracleServiceYMH 服务已成功停止。
克隆clone SQL> CREATE CONTROLFILE REUSE DATABASE "YMH" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\REDO03.LOG' SIZE 50M
11 DATAFILE
12 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\SYSTEM01.DBF',
13 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST2.DBF',
14 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\SYSAUX01.DBF',
15 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\USERS01.DBF',
16 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_DICT.DBF',
17 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_DICT.DBF2',
18 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_LOCAL.DBF',
19 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TEST_LOCAL2.DBF',
21 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\UNDOTBS2.DBF',
22 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ymh\TBS02.DBF'
23 CHARACTER SET ZHS16GBK
24 ;
Control file created.
==================>这里不小心少了一个TBS01.DBF
克隆clone SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
1 row selected.
(11)对克隆库进行不完全恢复
克隆clone SQL>recover database using backup controlfile until time '2011-08-24 21:24:00'====》恢复到删除表之前的一个时间点
ORA-00279: change 2068296 generated at 08/24/2011 21:24:37 needed for thread 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\CLONE\ARCHIVELOG\2011_08_24\O1_MF_1_9_%U_.ARC
ORA-00280: change 2068296 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
克隆clone SQL> alter database open resetlogs;
Database altered.
克隆clone SQL> select a,to_char(b,'hh24:mi:ss') from timer2 order by 1;
A TO_CHAR(
---------------- --------
1 21:18:09
2 21:18:22
2 rows selected.
克隆clone SQL> select a,to_char(b,'hh24:mi:ss') from timer1 order by 1;
select a,to_char(b,'hh24:mi:ss') from timer1 order by 1
*
ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00011'
============》刚才控制文件里少加了TBS01.DBF,导致该文件丢失
附注:
克隆clone SQL> recover database using backup controlfile until time '2011-08-24 16:40:00'
ORA-00283: recovery session canceled due to errors
ORA-19907:recovery time or SCN does not belong to recovered incarnation
=============》因为刚才主库ymh的服务没有关闭,导致把控制文件重建到主库,而之前我恢复时做了完全恢复,重新拷贝了一次数据文件、日志文件进行恢复,所以这里已经产生incarnation不同。只能从新弄过。所以正式库上不要在一台服务器上这样操作。
(12)对重建控制文件时少写了的数据文件进行处理(正常情况下不需要)
克隆clone SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE BYTES BLOCKS STATUS
------------------------------------------------------------ ---------------- ---------- ---------------- ---------------- --------- -
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_DICT.DBF2 6 TEST_DICT 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_DICT.DBF 5 TEST_DICT 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSTEM01.DBF 1 SYSTEM 524288000 64000 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TBS02.DBF 12 TBS02 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\UNDOTBS2.DBF 10 UNDOTBS2 25165824 3072 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_LOCAL2.DBF 8 TEST_LOCAL 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_LOCAL.DBF 7 TEST_LOCAL 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\USERS01.DBF 4 USERS 69468160 8480 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSAUX01.DBF 3 SYSAUX 314572800 38400 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST2.DBF 2 TEST2 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00011 11 TBS01 AVAILABLE
克隆clone SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00011' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TBS01.DBF';
Database altered.
=================》重命名
克隆clone SQL> select * from dba_data_files;
FILE_NAME FILE_ID TABLESPACE BYTES BLOCKS STATUS
------------------------------------------------------------ ---------------- ---------- ---------------- ---------------- --------- ---
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_DICT.DBF2 6 TEST_DICT 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_DICT.DBF 5 TEST_DICT 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSTEM01.DBF 1 SYSTEM 524288000 64000 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TBS02.DBF 12 TBS02 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\UNDOTBS2.DBF 10 UNDOTBS2 25165824 3072 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_LOCAL2.DBF 8 TEST_LOCAL 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST_LOCAL.DBF 7 TEST_LOCAL 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\USERS01.DBF 4 USERS 69468160 8480 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\SYSAUX01.DBF 3 SYSAUX 314572800 38400 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEST2.DBF 2 TEST2 10485760 1280 AVAILABLE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\YMH\TBS01.DBF 11 TBS01 AVAILABLE
克隆clone SQL> select file#, status,checkpoint_change#,last_change# from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE#
----- ------- ------------------ ----------------
1 SYSTEM 2068298
2 ONLINE 2068298
3 ONLINE 2068298
4 ONLINE 2068298
5 ONLINE 2068298
6 ONLINE 2068298
7 ONLINE 2068298
8 ONLINE 2068298
10 ONLINE 2068298
11 RECOVER 0 2068417
12 ONLINE 2068298
12 rows selected.
克隆clone SQL> recover datafile 11;============》进行介质恢复
ORA-00279: change 2067697 generated at 08/24/2011 21:09:59 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\CLONE\ARCHIVELOG\2011_08_24\O1_MF_1_9_%U_.ARC
ORA-00280: change 2067697 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
克隆clone SQL> select file#, status,checkpoint_change#,last_change# from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE#
----- ------- ------------------ ----------------
1 SYSTEM 2068298
2 ONLINE 2068298
3 ONLINE 2068298
4 ONLINE 2068298
5 ONLINE 2068298
6 ONLINE 2068298
7 ONLINE 2068298
8 ONLINE 2068298
10 ONLINE 2068298
11 OFFLINE 2068417 2068417========》脱机状态
12 ONLINE 2068298
12 rows selected.
克隆clone SQL>alter database datafile 11 online;=============》联机
Database altered.
克隆clone SQL> conn u01/u01
Connected.
克隆clone SQL> select a,to_char(b,'hh24:mi:ss') from timer1 order by 1;
A TO_CHAR(
---------------- --------
1 21:18:05
2 21:18:18
2 rows selected.
克隆clone SQL> select a,to_char(b,'hh24:mi:ss') from timer2 order by 1;
A TO_CHAR(
---------------- --------
1 21:18:09
2 21:18:22
2 rows selected.
====================》正常恢复到表删除之前了
(13)表空间迁移1:从克隆库导出(表空间要设置为只读)
克隆clone SQL> conn sys/ymh as sysdba;
已连接。
克隆clone SQL>alter tablespace tbs01 read only;
表空间已更改。
克隆clone SQL> host exp 'sys/cloneas sysdba' transport_tablespace=y tablespaces=tbs01 file=d:\tbs01_clone.dmp log=d:\tbs01_exp_clone.log
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
EXP-00008: 遇到 ORACLE 错误 25153
ORA-25153: 临时表空间为空
EXP-00000: 导出终止失败
=======》备份还原时不对临时表空间做备份导致的
克隆clone SQL> ALTER TABLESPACE temp ADD TEMPFILE 'D:\oracle\product\10.2.0\oradata\clone\temp03.dbf' SIZE 100M;
表空间已更改。===========》给临时表空间添加一个文件
克隆clone SQL> select * from dba_temp_files;
FILE_NAME
----------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CLONE\TEMP03.DBF
SQL>
克隆clone SQL>host exp 'sys/cloneas sysdba' transport_tablespace=y tablespaces=tbs01 file=d:\tbs01_clone.dmp log=d:\tbs01_exp_clone.log
Export: Release 10.2.0.1.0 - Production on 星期二 8月 23 21:15:21 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 TBS01...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表 TIMER1
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。
=========》表空间元数据导出,具体参照表空间迁移
(14)表空间迁移2:从克隆库拷贝文件到正式库(完成后表空间设置回可读写)
克隆clone SQL>host copy D:\oracle\product\10.2.0\oradata\clone\tbs01.dbfD:\oracle\product\10.2.0\oradata\ymh\
已复制 1 个文件。
克隆clone SQL>alter tablespace tbs01 read write;
表空间已更改。
(15)表空间迁移3:表空间导入正式库(完成后表空间设置回可读写)
主库ymh SQL>host imp 'sys/ymh as sysdba' transport_tablespace=y tablespaces=tbs01 file=d:\tbs01_clone.dmp log=d:\tbs01_exp_clone.log datafiles='D:\oracle\product\10.2.0\oradata\ymh\tbs01.dbf'
Import: Release 10.2.0.1.0 - Production on 星期四 8月 25 00:37:01 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 SYS 的对象导入到 SYS
. 正在将 SYS 的对象导入到 SYS
. 正在将 U01 的对象导入到 U01
. . 正在导入表 "TIMER1"
. 正在将 SYS 的对象导入到 SYS
成功终止导入, 没有出现警告。
主库ymh SQL>
(16)检查验证
主库ymh SQL> conn u01/u01
已连接。
主库ymh SQL> select a,to_char(b,'hh24:mi:ss') from timer1 order by 1;
A TO_CHAR(
---------- --------
1 21:18:05
2 21:18:18
主库ymh SQL> select a,to_char(b,'hh24:mi:ss') from timer2 order by 1;
A TO_CHAR(
---------- --------
1 21:18:09
2 21:18:22
3 21:26:03
4 21:26:08
主库ymh SQL>