解决gap 采用increapment scn 方式 操作。

###########1 

 

 

1.查看备库的scn

⚠️如果控制文件,数据文件,数据文件头部的scn不一致,需要根据日志中的gap的起始sequence# 找到对应的scn

col  current_scn for a999999999;

select current_scn from v$database;

 

SQL> select scn_to_timestamp(current_scn) from v$database;

##注意在尝试将scn 转换成对应的时间的时候 scn_to_timestam。有可能报错,ORA-08181

原因如下: timestamp_to_scn 或者 scn_to_timestamp 有时间限制,大概只能采取5天以内的SCN,转成成时间。超过5天就会报错

and here is something to illustrate the "time limit":
timestamp_to_scn 或者 scn_to_timestamp 有时间限制,大概只能采取5天以内的SCN,转成成时间。超过5天就会报错

SELECT t, timestamp_to_scn( t )
FROM times;
T TIMESTAMP_TO_SCN(T)
------------------------------ -------------------
17-OCT-12 09.34.39.000000 AM 1157515351230
17-OCT-12 09.34.40.000000 AM 1157515351231
17-OCT-12 09.34.41.000000 AM 1157515351231
17-OCT-12 09.34.42.000000 AM 1157515351231
17-OCT-12 09.34.43.000000 AM 1157515351232
17-OCT-12 09.34.44.000000 AM 1157515351232
17-OCT-12 09.34.45.000000 AM 1157515351232
17-OCT-12 09.34.46.000000 AM 1157515351233
17-OCT-12 09.34.47.000000 AM 1157515351233
17-OCT-12 09.34.48.000000 AM 1157515351233

and here is something to illustrate the "time limit":

SQL> SELECT timestamp_to_scn( systimestamp - 10 ) FROM dual;
SELECT timestamp_to_scn( systimestamp - 10 ) FROM dual
*
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1


SQL> SELECT scn_to_timestamp( 1156373700000 ) FROM dual;
SCN_TO_TIMESTAMP(1156373700000)
---------------------------------------------------------------------------
12-OCT-12 10.03.07.000000000 PM

SQL> SELECT scn_to_timestamp( 1156373600000 ) FROM dual;
SELECT scn_to_timestamp( 1156373600000 ) FROM dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

HTH,

Bruno.

 

2.查看主库在断点后是否添加数据文件

select file#,name, from  v$datafile where current_change#>=备库查得的断电处scn;

 

3.备库停止日志应用服务

alter database recover managed standby  database cancel;

 

4.主库增量备份并传输到备库上

RMAN> backup as compressed backupset incremental from scn 断点处的scn database format ‘备份存放路径’

scp 

 

5.备库上进行恢复

RMAN>catalog START with ‘拷贝过来的主库的增量备份集的路径’

⚠️ 如果此时库是read only,需要转换为mount状态。

RMAN>recover database noredo

 

6.主库上创建standby controlfile 文件并scp到备库

RMAN>backup current controlfile for standby format '备份路径‘;

7.恢复备库的控制文件

备库重启到nomount状态,恢复控制文件,启动到mount

RMAN>restore standby controlfile from '主库的控制文件的rman备份文件'

RMAN>alter database open mount

 

8.清空备库日志组(选做)

⚠️如果dg中使用了standby log 模式,不需要此步骤,否则有几组需要清空几组。

alter database clear logfile group 组号;

 

9.备库重设flashback(选做)

alter database flashback on/off;

 

10.备库重新接收并应用日志

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

 

11.开启备库到redo only状态

alter database recover managed standby database cancel;

alter database open;

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

 

12.验证修复是否成功

在主库端执行:alter system switch logfile;

在各个备库中执行:select max(sequence#) from v$arhcived_log;

 

 

 

######2  

1.Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes

STEPS

1. Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been  applied to a small subset of the database:

1. List the files that have had nologging changes applied by querying the V$DATAFILE view on the standby database. For example:

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

FILE#      FIRST_NONLOGGED_SCN
---------- -------------------
         4              225979
         5              230184


2. Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. On the standby database, offline the datafiles (recorded in step 0) that have had nologging changes. Taking these datafiles offline ensures redo data is not skipped for the corrupt blocks while the incremental backups are performed.

SQL> ALTER DATABASE DATAFILE 4 OFFLINE FOR DROP;
SQL> ALTER DATABASE DATAFILE 5 OFFLINE FOR DROP;

4. Start Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

5. While connected to the primary database as the RMAN target, create an incremental backup for each datafile listed in the FIRST_NONLOGGED_SCN column (recorded in step 0). For example:

RMAN> BACKUP INCREMENTAL FROM SCN 225979 DATAFILE 4 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';
RMAN> BACKUP INCREMENTAL FROM SCN 230184 DATAFILE 5 FORMAT '/tmp/ForStandby_%U' TAG 'FOR STANDBY';

6. Transfer all backup sets created on the primary system to the standby system. (Note that there may be more than one backup file created.)

% scp /tmp/ForStandby_* standby:/tmp

7. While connected to the physical standby database as the RMAN target, catalog all incremental backup pieces. For example:

RMAN> CATALOG START WITH '/tmp/ForStandby_';

8. Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

9. Online the datafiles on the standby database

SQL> ALTER DATABASE DATAFILE 4 ONLINE;
SQL> ALTER DATABASE DATAFILE 5 ONLINE;

10. While connected to the physical standby database as the RMAN target, apply the incremental backup sets:

RMAN> RECOVER DATAFILE 4, 5 NOREDO;

11. Query the V$DATAFILE view on the standby database to verify there are no datafiles with nologged changes. The following query should return zero rows

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

12. Recreate the Standby Controlfile following:

Note 459411.1 Steps to recreate a Physical Standby Controlfile

13. Remove the incremental backups from the standby system:

RMAN> DELETE BACKUP TAG 'FOR STANDBY';

14. Manually remove the incremental backups from the primary system. For example, the following example uses the Linux rm command:

% rm /tmp/ForStandby_*

15. Start Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

Note: Starting from 12c we can use RECOVER DATABASE...FROM SERVICE clause in RMAN to generate, transfer and apply the incremental backup in a single step. Please refer below document for examples:

Note 1987763.1 ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C 

 

2. Follow this step-by-step procedure to roll forward a physical standby database for which nologging changes have been applied to a large portion of the database:

1. Query the V$DATAFILE view on the standby database to record the lowest FIRST_NONLOGGED_SCN:

SQL> SELECT MIN(FIRST_NONLOGGED_SCN) FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN>0;

MIN(FIRST_NONLOGGED_SCN)
------------------------
                  223948

2.Stop Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.While connected to the primary database as the RMAN target, create an incremental backup from the lowest FIRST_NONLOGGED_SCN (recorded in step 0)

RMAN> BACKUP INCREMENTAL FROM SCN 223948 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FOR STANDBY';

4.Transfer all backup sets created on the primary system to the standby system. (Note that more than one backup file may have been created.) The following example uses the scp command to copy the files:

% scp /tmp/ForStandby_* standby:/tmp

5.While connected to the standby database as the RMAN target, catalog all incremental backup piece(s)

RMAN> CATALOG START WITH '/tmp/ForStandby_';

6.While connected to the standby database as the RMAN target, apply the incremental backups:

RMAN> RECOVER DATABASE NOREDO;

7.Query the V$DATAFILE view to verify there are no datafiles with nologged changes. The following query on the standby database should return zero rows:

SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

8. Recreate the Standby Controlfile following:

Note 459411.1 Steps to recreate a Physical Standby Controlfile

9.Remove the incremental backups from the standby system:

RMAN> DELETE BACKUP TAG 'FOR STANDBY';

10.Manually remove the incremental backups from the primary system. For example, the following removes the backups using the Linux rm command:

% rm /tmp/ForStandby_*

11.Start Redo Apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Note:

If the affected files belong to a READ ONLY tablespace, those files will be ignored during backup. To bypass the issue, at Primary Database, switch the tablespace from read only to read write and back to read only again :

SQL> alter tablespace <tablespace_name> read write ;
SQL> alter tablespace <tablespace_name> read only ;
posted @   feiyun8616  阅读(593)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示