[20140425]11GR2 truncate后恢复测试.txt
--做一个truncate后恢复测试在11GR2下，我的测试环境使用使用dataguard。备用库打开flashback。
--可以利用在备用库flashback到truncate前，然后在传输到主库的方式看看。
1.建立测试环境:
-- 我的测试环境：primary的tnsnames别名test，standby主机的tnsnames别名testdg。数据库版本：11GR2.
-- 备用库打开在read only模式，并且real-time apply 应用日志。
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
SYS@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@testdg> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE YES YES
SCOTT@test> create table t1 as select rownum id,'test' name from dual connect by levelSCOTT@test> select current_scn ,sysdate from v$database;
CURRENT_SCN SYSDATE
----------- -------------------
3269885933 2014-04-25 09:32:39
--记下当前SCN,以及时间.如果生产系统出现,可以查询logminer确定时间以及scn.
SCOTT@test> truncate table t1;
Table truncated.
SCOTT@testdg> select count(*) from t1;
COUNT(*)
----------
0
2.现在开始恢复:
--首先停止日志应用。注意这个日志依旧在传输到备用库，只不过不apply。
DGMGRL> edit database testdg set state='APPLY-OFF';
Succeeded.
--关闭备用库
SYS@testdg> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@testdg> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@testdg> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@testdg> select process,pid,status,group#,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#,group# ;
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ---------- ---------- ---------- ---------- ----------
ARCH 13806 CONNECTED N/A 0 0 0 0 0
ARCH 13808 CONNECTED N/A 0 0 0 0 0
ARCH 13812 CONNECTED N/A 0 0 0 0 0
RFS 13831 IDLE N/A 0 0 0 0 0
RFS 13823 IDLE N/A 0 0 0 0 0
RFS 13821 IDLE 1 1 43 37 1 0
ARCH 13810 CLOSING 5 1 42 26624 1247 0
7 rows selected.
--观察rfs依旧在接收日志,但是不应用.
SYS@testdg> flashback database to scn 3269885933;
Flashback complete.
SYS@testdg> alter database open read only ;
Database altered.
SYS@testdg> select count(*) from scott.t1;
COUNT(*)
----------
1000
--可以发现现在在备用机器已经看的见.
3.取回数据到主库:
--建立db-link.
SCOTT@test> CREATE PUBLIC DATABASE LINK "TESTDG.COM" CONNECT TO scott IDENTIFIED BY btbtms USING '192.168.101.115:1521/testdg.com';
Database link created.
SCOTT@test> select count(*) from scott.t1@testdg.com;
COUNT(*)
----------
1000
--实际上取回的方法很多,使用insert into t1 select * from t1;或者copy都可以.
SCOTT@test> help copy
COPY
----
Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
4.测试使用impdp测试看看.这样大数据比较快一些.
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle11g/admin/test/dpdump/';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO SCOTT;
--方法1
$ impdp scott/btbtms DIRECTORY=DATA_PUMP_DIR LOGFILE=t1.log NETWORK_LINK=testdg.com TABLES=t1 content=data_only
Import: Release 11.2.0.3.0 - Production on Fri Apr 25 09:55:49 2014
Copyright (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
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** DIRECTORY=DATA_PUMP_DIR LOGFILE=t1.log NETWORK_LINK=testdg.com TABLES=t1 content=data_only */
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . imported "SCOTT"."T1" 1000 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 09:56:09
SCOTT@test> select count(*) from scott.t1;
COUNT(*)
----------
1000
--可以发现已经恢复.
--方法2,因为正常生产系统不能停,先放入另外一张表t1_rec.
$ impdp scott/btbtms DIRECTORY=DATA_PUMP_DIR LOGFILE=t1.log NETWORK_LINK=testdg.com TABLES=t1 REMAP_TABLE=t1:t1_rec
Import: Release 11.2.0.3.0 - Production on Fri Apr 25 09:59:15 2014
Copyright (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
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** DIRECTORY=DATA_PUMP_DIR LOGFILE=t1.log NETWORK_LINK=testdg.com TABLES=t1 REMAP_TABLE=t1:t1_rec */
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."T1_REC" 1000 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 09:59:24
SCOTT@test> select count(*) from scott.t1_rec;
COUNT(*)
----------
1000
5.恢复现场:
DGMGRL> edit database testdg set state='apply-on';
Succeeded.
SYS@testdg> select process,pid,status,group#,thread#,sequence#,block#,blocks,delay_mins from v$managed_standby order by thread#,group# ;
PROCESS PID STATUS GROUP# THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------- ------------ ------- ---------- ---------- ---------- ---------- ----------
ARCH 13806 CONNECTED N/A 0 0 0 0 0
ARCH 13808 CONNECTED N/A 0 0 0 0 0
ARCH 13812 CONNECTED N/A 0 0 0 0 0
RFS 13823 IDLE N/A 0 0 0 0 0
RFS 13831 IDLE N/A 0 0 0 0 0
RFS 13821 IDLE 1 1 43 14594 1 0
ARCH 13810 CLOSING 5 1 42 26624 1247 0
MRP0 14044 APPLYING_LOG N/A 1 43 14594 102400 0
8 rows selected.
--可以发现MRP进程已经起来,并且在应用.
SCOTT@test> insert into t1 values (1001,'aaaa');
1 row created.
SCOTT@test> commit ;
Commit complete.
SYS@testdg> select * from scott.t1 where id=1001;
ID NAME
---------- --------------------
1001 aaaa
--OK!正确.
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
总结:
自己做一个workshop,主要目的为了以后出现问题,不会手忙脚乱.