[20140425]11GR2 truncate后恢复测试.txt

[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 level<=1e3;
SCOTT@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,主要目的为了以后出现问题,不会手忙脚乱.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1149127/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1149127/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值