DataGuard维护及故障处理文档

一：主备库正常切换（switchover)

1）：检查主库状态

select name,database_role,switchover_status from v$database;

1：查询结果为 TO STANDBY

正常状况，可以切换。

2：查询结果为空的

Dataguard配置不正确，确认dataguard配置，

（例如：

所有LOG_ARCHIVE_DEST_n参数是否正确。

tnsnames.ora里面是否配置正确，是否可以连通。

密码文件Orapw$ORACLE_SID是否正确，可用sqlplus sys/sys@beadb as sysdba 测试。

）

3：查询结果为 sessions active

2）：switchover主库

1：将主库更改成备库

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

2：重启主库

Shutdown immediate

Startup mount;

3）：检查主库状态

select name,database_role,switchover_status from v$database;

1：查询结果为 TO_PRIMARY

正常状况，可以切换。

2：查询结果为空的

Dataguard配置不正确，确认dataguard配置，

（例如：

所有LOG_ARCHIVE_DEST_n参数是否正确。

tnsnames.ora里面是否配置正确，是否可以连通。

密码文件Orapw$ORACLE_SID是否正确，可用sqlplus sys/sys@beadb as sysdba 测试。

）

3：查询结果为 sessions active

4）：switchover standby to primary

1: 将备库更改为主库

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2：将备库（原备库）开启

Alter database open;

（如果备库原来为open read-only状态，重启备库）

Shutdown immediate

Startup;

3: 重新启用log_archive_dest_state_2

alter system set log_archive_dest_state_2 = 'defer';

alter system set log_archive_dest_state_2 = 'enable';

5）：新备库（原主库）开启Redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

6）：检查主备库同步情况

二：主备库异常切换（failovers)

1）：查看归档日志差异与处理日志差异

1：查看差异日志

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

如果结果不为no rows，则将其差异的归档日志从主库的服务器中手工拷到备库当中。

例：

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

1 114 114

查看最大归档日志

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

2：将114的归档日志从主库的服务器中拷贝到备库的服务器的归档目录底下。

rcp 1_114_714650269.arch 192.168.143.219:/archive/1_114_714650269.arch

3：注册114归档日志

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/archive/1_114_714650269.arch';

4：查看日志差异

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

5：查看最大日志

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

THREAD LAST

---------- ----------

1 115

6：将主库服务器的归档目录中大于115的归档日志全部拷贝至备库服务器的归档目录下

-rw-r----- 1 oracle oinstall 1024 Mar 29 16:45 1_115_714650269.arch

-rw-r----- 1 oracle oinstall 13312 Mar 29 16:49 1_116_714650269.arch

-rw-r----- 1 oracle oinstall 1024 Mar 29 16:49 1_117_714650269.arch

-rw-r----- 1 oracle oinstall 1536 Mar 29 16:50 1_118_714650269.arch

-rw-r----- 1 oracle oinstall 1024 Mar 29 16:50 1_119_714650269.arch

-rw-r----- 1 oracle oinstall 1024 Mar 29 16:50 1_120_714650269.arch

scp 1_116_714650269.arch 1_117_714650269.arch 1_118_714650269.arch 1_119_714650269.arch 1_120_714650269.arch 192.168.143.219:/archive

7：注册大于115的归档日志

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/archive/1_116_714650269.arch';

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/archive/1_117_714650269.arch';

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/archive/1_118_714650269.arch';

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/archive/1_119_714650269.arch';

ALTER DATABASE REGISTER PHYSICAL LOGFILE '/archive/1_120_714650269.arch';

8：查看最大日志与日志差异

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

THREAD LAST

---------- ----------

1 120

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

2）：结束standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

3）：将备库转为主库

1：切换备库为主库

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2：查看数据库状态

SQL> select name,database_role,switchover_status from v$database;

NAME DATABASE_ROLE SWITCHOVER_STATUS

------------------ -------------------------------- ----------------------------------------

BEADB PRIMARY TO STANDBY

3：开启数据库

SQL> alter database open;

Database altered.

4）：备份新主库

5）：恢复原主库为备库

SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW FROM V$ARCHIVE_DEST;

三．常见错误

3.1.0

standby服务器里存在日志，但日志无法恢复.

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/dev/raw/raw7'

standby服务器里存在日志，但日志无法恢复

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

1 4306 4300 6

Ls -l

-rw-r----- 1 oracle oinstall 292538880 May 15 10:43 1_4300_709008341.dbf

-rw-r----- 1 oracle oinstall 292537856 May 15 15:12 1_4301_709008341.dbf

-rw-r----- 1 oracle oinstall 292538880 May 15 23:03 1_4302_709008341.dbf

-rw-r----- 1 oracle oinstall 168815104 May 16 00:59 1_4303_709008341.dbf

-rw-r----- 1 oracle oinstall 1439744 May 16 01:01 1_4304_709008341.dbf

-rw-r----- 1 oracle oinstall 10695680 May 16 02:09 1_4305_709008341.dbf

-rw-r----- 1 oracle oinstall 1361920 May 16 02:17 1_4306_709008341.dbf

1:使用

SQL> alter database recover managed standby database disconnect;

并未报错，但执行过后无效果，查看告警日志得：

Sun May 16 02:10:00 2010

MRP0: Background Managed Standby Recovery process started (uweb2)

Managed Standby Recovery not using Real Time Apply

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 264600193) is orphaned on incarnation#=2

MRP0: Background Media Recovery terminated with error 19909

Sun May 16 02:10:05 2010

Errors in file /oracle/admin/uweb2/bdump/uweb2_mrp0_4167.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/dev/raw/raw7'

Sun May 16 02:10:05 2010

Errors in file /oracle/admin/uweb2/bdump/uweb2_mrp0_4167.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: '/dev/raw/raw7'

Sun May 16 02:10:05 2010

MRP0: Background Media Recovery process shutdown (uweb2)

Sun May 16 02:10:06 2010

Completed: alter database recover managed standby database disconnect

2：查看metalink获得类似文章（554358.1）

1：）主库上执行

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 UWEB 3593855639 PARENT 1 02-SEP-09

2 2 UWEB 3593855639 CURRENT 62816220 23-JAN-10

2：）standby库上执行：

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1 1 UWEB 3593855639 PARENT 1 02-SEP-09

2 2 UWEB 3593855639 PARENT 62816220 23-JAN-10

4 4 UWEB 3593855639 ORPHAN 179188114 06-APR-10

5 5 UWEB 3593855639 ORPHAN 192969711 10-APR-10

7 7 UWEB 3593855639 ORPHAN 203529715 14-APR-10

6 6 UWEB 3593855639 ORPHAN 228550743 23-APR-10

3 3 UWEB 3593855639 CURRENT 245774503 02-MAY-10

3：）reset database to incarnation

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> exit

SQL> recover standby database;

ORA-00279: change 264600193 generated at 05/15/2010 10:43:59 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4301_709008341.dbf

ORA-00280: change 264600193 for thread 1 is in sequence #4301

Specify log: {=suggested | filename | AUTO | CANCEL}

AUTO

ORA-00279: change 264658823 generated at 05/15/2010 15:13:20 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4302_709008341.dbf

ORA-00280: change 264658823 for thread 1 is in sequence #4302

ORA-00278: log file '/rman/archive/archive/1_4301_709008341.dbf' no longer

needed for this recovery

ORA-00279: change 264712365 generated at 05/15/2010 23:02:14 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4303_709008341.dbf

ORA-00280: change 264712365 for thread 1 is in sequence #4303

ORA-00278: log file '/rman/archive/archive/1_4302_709008341.dbf' no longer

needed for this recovery

ORA-00279: change 264776345 generated at 05/16/2010 01:00:04 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4304_709008341.dbf

ORA-00280: change 264776345 for thread 1 is in sequence #4304

ORA-00278: log file '/rman/archive/archive/1_4303_709008341.dbf' no longer

needed for this recovery

ORA-00279: change 264776783 generated at 05/16/2010 01:02:37 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4305_709008341.dbf

ORA-00280: change 264776783 for thread 1 is in sequence #4305

ORA-00278: log file '/rman/archive/archive/1_4304_709008341.dbf' no longer

needed for this recovery

ORA-00279: change 264781851 generated at 05/16/2010 02:09:43 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4306_709008341.dbf

ORA-00280: change 264781851 for thread 1 is in sequence #4306

ORA-00278: log file '/rman/archive/archive/1_4305_709008341.dbf' no longer

needed for this recovery

ORA-00279: change 264782579 generated at 05/16/2010 02:18:21 needed for thread

1

ORA-00289: suggestion : /rman/archive/archive/1_4307_709008341.dbf

ORA-00280: change 264782579 for thread 1 is in sequence #4307

ORA-00278: log file '/rman/archive/archive/1_4306_709008341.dbf' no longer

needed for this recovery

ORA-16145: archival for thread# 1 sequence# 4307 in progress

SQL> recover managed standby database disconnect;

Media recovery complete.

SQL> alter database recover managed standby database disconnect;

alter database recover managed standby database disconnect

*

ERROR at line 1:

ORA-01153: an incompatible media recovery is active

查看结果：

SQL> SQL> check standby archive log.

SQL>

Thread Last Sequence Received Last Sequence Applied Difference

---------- ---------------------- --------------------- ----------

1 4306 4306 0

SQL> SQL> check standby archive gap.

SQL>

no rows selected

ITPub博客

Dataguard 维护文档

最新文章