ORACLE Flashback+DataGuard进行数据恢复

上周,在公司中心范围内进行了一次技术分享,主要阐述一下Datagurd技术的使用范围,其中涉及一些Flashback技术的应用:我们主要利用Flashback闪回特性进行应用人员的误操作,进行恢复。

实际操作流程如下:

主库:Travelskydba            
备库:Travelskydba_std

主库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba                   READ WRITE           PRIMARY          YES NO


备库:	
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES

SQL> show parameter db_flashback_retention_target   

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

主库执行事务:
SQL> create user albert identified by albert;

User created.

SQL> grant connect,resource to albert;

Grant succeeded.

SQL> conn albert/albert
Connected.

SQL> create table Travelskydba (id1 number,id2 number,id3 number);

Table created.

SQL> insert into Travelskydba values(1,2,3);

1 row created.

SQL> insert into Travelskydba values(4,5,6);

1 row created.

SQL> commit;

Commit complete.

主库:
SQL>  select * from albert.Travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
         4          5          6

SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   20619438


--开启实时应用的备库已经完成redo应用,数据已经保持与主库同步了:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES

SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         4          5          6

有时我们接收到应用人员的电话,数据误删除了,或者对数据的调研不足,导致实时应用的数据当做历史数据删除掉了,请求DBA需要进行数据恢复,此时我们可以利用DataGuard 备库已经开启的flashback特性进行数据恢复。

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES   
SQL> select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'yyyymmdd hh24:mi:ss') time,RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TIME              RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
            20611974 20200325 12:16:12             1440      629145600               1323294720


如果数据量较少,主库可以利用DBLINK 进行数据恢复:
SQL> show user
USER is "ALBERT"
SQL> insert into travelskydba select * from albert.travelskydba@TRAVELSKYDBA_LINK where id1=1;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
         4          5          6


--数据恢复后,备库可以继续进行实时应用		 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1543505184 bytes
Database Buffers         2550136832 bytes
Redo Buffers               12857344 bytes
Database mounted.
Database opened.		

--备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0
  2  /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1       6942          1             5            5

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1       6942          1             5            5
RFS       CLOSING      UNKNOWN           1       6944          1             0            0
RFS       OPENING      UNKNOWN           1       6943          0             0            0
RFS       OPENING      UNKNOWN           1       6942          0             0            0
RFS       OPENING      UNKNOWN           1       6946          0             0            0
RFS       OPENING      UNKNOWN           1       6948          0             0            0

6 rows selected.
 
 
--主库:
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          5             0            0

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          6             0            0

SQL> /
/
PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          7             0            0

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archive_Travelskydba/Traveskydba
Oldest online log sequence     6948
Next log sequence to archive   6950
Current log sequence           6950

上述情形适用于误删除数据量较小的情况,如果遇到误删除的数据较多,利用DB_LINK的方式效率相对是比较低下的,我们需要将备库打开至read write mode,将数据导入导出,从而达到进行数据恢复的目的

--备库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba  Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES



SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

主库误删除用户:
SQL> drop user albert cascade;

User dropped.

备库:
SQL> select * from albert.travelskydba;
select * from albert.travelskydba
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> create restore point tra guarantee flashback database; --创建resotre point

Restore point created.

SQL>  flashback database to scn 20668180;

Flashback complete.


SQL>  ALTER DATABASE ACTIVATE STANDBY DATABASE;--将备库打开至可读可写状态

Database altered.

SQL> alter database open;

Database altered.


SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ WRITE           PRIMARY          YES YES


SQL> select * from albert.travelskydba; --数据恢复完成,将此用户导出,导入恢复至主库

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
		 
SQL> create directory tra_dir as '/home/ora11g/';

Directory created.

SQL> 
SQL> grant read,write,execute on directory tra_dir to public;

Grant succeeded.


[ora11g@albert ]$ expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log 

Export: Release 11.2.0.4.0 - Production on Wed Mar 25 09:59:48 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11g/albert_dmp.20200325
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 25 10:00:08 2020 elapsed 0 00:00:17

[ora11g@albert ~]$ scp albert_dmp.20200325 ******:/home/ora11g
The authenticity of host '*******' can't be established.
RSA key fingerprint is 45:b9:52:12:86:55:8d:e5:33:8b:b8:59:02:68:81:ff.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '******' (RSA) to the list of known hosts.
ora11g@******'s password: 
albert_dmp.20200325                                                                                                                                     100%  180KB 180.0KB/s   00:00 


--导入主库:

SQL> create directory tra_dir as '/home/ora11g/';

Directory created.

SQL> grant read,write,execute on directory tra_dir to public;

Grant succeeded.


[ora11g@vm-kvm17146-ora ~]$ impdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log 

Import: Release 11.2.0.4.0 - Production on Wed Mar 25 20:28:48 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Mar 25 20:28:59 2020 elapsed 0 00:00:04

主库数据恢复:
SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

主库数据已经恢复,但是我们现在要继续处理备库,让备库继续进行实时应用

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> flashback database to restore point tra; --闪回至restore point

Flashback complete.


SQL> alter database convert to physical standby; 更新control_file,database_role为physical standby

Database altered.


SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES



SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


主库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6972         90             0            0

SQL> alter system switch logfile;

System altered.

SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0
  2  ;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6973          4             0            0


备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS       IDLE         LGWR              1       6973          8             0            0
MRP0      APPLYING_LOG N/A               1       6973          8             5            5


SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

我们还可以利用Oracle snapshot方式进行备库的read write模式打开

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba  Travelskydba                   READ WRITE           PRIMARY          YES NO                    20755536


SQL>  select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3



--备库:

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES                   20755688

SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3


--主库误删除数据:


SQL> drop user albert cascade;

User dropped.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba                   READ WRITE           PRIMARY          YES NO                    20755761

   

--备库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES                   20755770
   

SQL> select * from albert.travelskydba;
select * from albert.travelskydba
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

准备恢复数据:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
                                                 1.ora
dg_broker_config_file2               string      /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
                                                 2.ora
dg_broker_start                      boolean     TRUE
SQL> alter system set dg_broker_start=false;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> flashback database to scn 20755688;

Flashback complete.

SQL> alter database open;

Database altered.

SQL>  select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> 
SQL> alter database convert to snapshot standby;

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              SNAPSHOT STANDBY YES YES                          0


SQL> alter database open; 

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba  Travelskydba_std                  READ WRITE           SNAPSHOT STANDBY YES YES                   20755867


SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

[ora11g@albert ~]$  expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log 

Export: Release 11.2.0.4.0 - Production on Thu Mar 26 01:12:23 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11g/albert_dmp.20200326
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 26 01:12:44 2020 elapsed 0 00:00:18

--数据导入过程省略--

--备库恢复至standby模式:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20755688  --之前闪回的SCN点,flashback database to scn 20755688;




SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757332


SQL> /

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757332


SQL> alter database open; 

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757498 

主库:
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1         19        206             0            0

SQL> 
SQL> alter system switch logfile;

System altered.

SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1         20          6             0            0

备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1         19        142             5            5
RFS       IDLE         LGWR              1         19        142             0            0

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1         20         55             5            5
RFS       IDLE         LGWR              1         20         55             0            0