Oracle11G DataGard学习以及线上修复standby库的经历总结

n =============================

n 知识准备储备工作

n =============================

1·准备工作

打开Forced Logging模式通过 SELECT FORCE_LOGGING FROM V$DATABASE;查看yes就已经打开了

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

YES

SQL> ALTER DATABASE no force logging;

Database altered.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

NO

1.2创建密码文件

1.3配置Standby Redo Log

添加Standby redo log的操作方式与online redo log几乎一模一样，

(1) alter database add standby logfile group 4('/data/.../standbyrd01.log') size 20M;

删除也同样简单

(2) alter database drop standby logfile group 4;

另外，从可靠性方面考虑，建议primary数据库也创建standby redologs，这样一旦切换，不会影响primary做为standby的正常工作。

验证standby redo log文件组是否成功创建

select group#, thread#, sequence#, archived, status from v$stanby_log;

1.4、设置初始化参数

1.5、确保数据库处于归档模式archive log list;

如果当前primary数据库未处于归档模式，通过如下命令将db设置成归档模式

start mount;

alter database archivelog;

alter database open;

2·创建物理standby

2.1 创建备份手工复制或者通过rman操作 -- primary库操作

2.2 创建控制文件 -- primary库操作

alter database create standby controlfile as '/data/jsspdg01.ctl';

2.3 创建初始化参数文件

创建客户端初始化参数文件

create pfile='/tmp/imdb.ora' from spfile;

修改初始化参数文件中的参数

2.4 复制文件到standby服务器

至少 3部分：数据文件，控制文件，修改过的初始化参数文件，注意路径。

2.5 配置standby数据库

简单步骤

1创建新的OracleService(windows环境下需要)

2创建密码文件，注意保持与primary一致

3配置监听并启动

4修改primary和standby的tnsnames.ora，各自增加对应的Net Service Name。

2.6 启动standby

注意：物理standby极少情况下可以以read-write模式打开，某些情况可以以read-only模式打开，默认情况下加载到mount状态即可。

startup mount;

启动redo应用

alter database recover managed standby database disconnect from session;

启动实时应用

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

disconnect session字句并非必须，该字句用于指定启动完应用后自动退出到命令操作符前，不指定的话，当前session就会一直停留处理redo应用，如果想做其他操作，就只能新建一个连接。

2.7停止standby

正常情况下，先停止redo应用，

alter database recover managed standby database calcel;

然后再停止standby数据库

shutdown immediate;

n ====================

n 测试开始实际操作

n ====================

[1] 查看同步情况：

show parameter instance_name;

-- primary 以及standby上执行查看

select max(sequence#) from V$archived_log;

[2] 查看dg是否逻辑备份还是物理备份

select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

1 scp rman备份文件到standby库

su -l oracle -c "$BIN/rman target / msglog=$BACKUP_PATH/backlog/bakl$LEVEL.log cmdfile=/DATABASE/oracle/rmanbak/script/bak_level_$LEVEL"

/DATABASE/oracle/rmanbak/backlog/

/DATABASE/oracle/rmanbak/script/

2 备份控制文件

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';

3 备份参数文件

create pfile='/tmp/imdb.ora' from spfile;

4检查并配置环境

/DATABASE/oracle/rmantest/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/

> mkdir -p /DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/

> cp /DATABASE/oracle/rmantest/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/*

/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/

先查看/DATABASE/oracle/rmanbak/archlog是否存在，不存在则添加目录

> mkdir -p /DATABASE/oracle/rmanbak/archlog

> cp logl0_IMDB_1304_1_8onqlg57_20121119.bak /DATABASE/oracle/rmanbak/archlog

> mkdir -p /DATABASE/oracle/rmanbak/data

> cp level0_IMDB_1302_1_8mnqlfiu_20121119.bak /DATABASE/oracle/rmanbak/data

5 控制文件

/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/o1_mf_s_799719582_8bl2nzh6_.bkp

6 先取消同步

alter database recover management cancel (命令我忘记了，大概就是这个)

RESTORE CONTROLFILE FROM AUTOBACKUP;

restore controlfile from backupset(备份集的编号)

restore controlfile from backupset(/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/o1_mf_s_799719637_8bl2popt_.bkp)

RESTORE CONTROLFILE FROM '/xx/xx.bak';

7 在主库上跑

rman

restore archivelog from time 'sysdate-15';

然后修改备库的参数，重启同步

cp /DATABASE/oracle/ORACLE11G/oradata/IMDB/control01.ctl /DATABASE/control01.ctl.20121123

cp /DATABASE/oracle/ORACLE11G/flash_recovery_area/IMDB/control02.ctl /DATABASE/control02.ctl.20121123

n ===========================

n 线上恢复standby从库

n ===========================

======================开始恢复standby数据库 begin ====

1. 关闭，重启db

rman> SHUTDOWN IMMEDIATE;

RMAN> startup nomount;

sql> -- 查看db状态 select open_mode from v$database

select status from v$instance;

2.先恢复初始化参数文件

RMAN> restore spfile to pfile '/DATABASE/oracle/ORACLE11G/11g/dbs/spfileIMDB.ora' from '/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/o1_mf_s_799719637_8bl2popt_.bkp';

然后使用刚刚创建的参数文件重新启动到未加载状态(注意，你最好打开该参数文件，看一下路径是否都确实存在，或者是否正确。)

3.恢复控制文件并进入到加载状态

RMAN> restore controlfile from '/DATABASE/oracle/ORACLE11G/flash_recovery_area/DG01/autobackup/2012_11_19/o1_mf_s_799719637_8bl2popt_.bkp';

RMAN> alter database mount;

4.修复数据库，我这里是源路径修复，如果你要恢复的文件地址与源库地址不同的话，需要通过SET NEWNAME FOR DATAFILE命令来为数据文件重新设定路径。

RMAN> restore database;

/DATABASE/oracle/ORACLE11G/arch

archived log file name=/DATABASE/oracle/ORACLE11G/arch/

5.恢复数据库

RMAN> recover database;

archived log file name=/DATABASE/oracle/ORACLE11G/arch/1_18174_771119325.dbf thread=1 sequence=18174

unable to find archived log

archived log thread=1 sequence=18175

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 11/25/2012 22:36:41

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18175 and starting SCN of 61579926652

在sqlplus窗口执行

alter database register or replace logfile '/DATABASE/oracle/ORACLE11G/arch/1_18175_771119325.dbf';

sql 'alter database register or replace logfile '/DATABASE/oracle/ORACLE11G/arch/1_18175_771119325.dbf/'';

Sun Nov 25 23:14:12 2012

RFS[12]: Assigned to RFS process 16280

RFS[12]: Database mount ID mismatch [0x5857d13d:0x58730296] (1482150205:1483932310)

RFS[12]: Not using real application clusters

Errors in file /DATABASE/oracle/ORACLE11G/diag/rdbms/dg02/IMDB/trace/IMDB_rfs_16280.trc:

ORA-16009: 重做传输目标无效

-- 1482150205和主库从库的dbid不一致，所以导致归档传输报错。开始诊断

rman> SHUTDOWN IMMEDIATE;

RMAN> startup mount;

alter database open;

报错，启动不起来

Sun Nov 25 23:44:25 2012

RFS[4]: Assigned to RFS process 18805

RFS[4]: Identified database type as 'physical standby': Client is LGWR SYNC pid 3137

Primary database is in MAXIMUM PERFORMANCE mode

SRL log 4 needs clearing because log has not been created

Errors in file /DATABASE/oracle/ORACLE11G/diag/rdbms/dg02/IMDB/trace/IMDB_rfs_18805.trc:

ORA-00367: 日志文件标头中的校验和错误

ORA-00315: 日志 4 (用于线程 0)标头中的线程 # 1 错误

ORA-00312: 联机日志 4线程 0: '/DATABASE/oracle/ORACLE11G/oradata/IMDB/standbyrd01.log'

SRL log 5 needs clearing because log has not been created

Errors in file /DATABASE/oracle/ORACLE11G/diag/rdbms/dg02/IMDB/trace/IMDB_rfs_18805.trc:

ORA-00367: 日志文件标头中的校验和错误

ORA-00315: 日志 5 (用于线程 0)标头中的线程 # 1 错误

ORA-00312: 联机日志 5线程 0: '/DATABASE/oracle/ORACLE11G/oradata/IMDB/standbyrd02.log'

RFS[4]: Selected log 6 for thread 1 sequence 18180 dbid 1454788955 branch 771119325

SRL log 4 needs clearing because log has not been created

Errors in file /DATABASE/oracle/ORACLE11G/diag/rdbms/dg02/IMDB/trace/IMDB_rfs_18798.trc:

ORA-00367: 日志文件标头中的校验和错误

ORA-00315: 日志 4 (用于线程 0)标头中的线程 # 1 错误

ORA-00312: 联机日志 4线程 0: '/DATABASE/oracle/ORACLE11G/oradata/IMDB/standbyrd01.log'

SRL log 5 needs clearing because log has not been created

Errors in file /DATABASE/oracle/ORACLE11G/diag/rdbms/dg02/IMDB/trace/IMDB_rfs_18798.trc:

ORA-00367: 日志文件标头中的校验和错误

ORA-00315: 日志 5 (用于线程 0)标头中的线程 # 1 错误

ORA-00312: 联机日志 5线程 0: '/DATABASE/oracle/ORACLE11G/oradata/IMDB/standbyrd02.log'

RFS[3]: Selected log 7 for thread 1 sequence 18179 dbid 1454788955 branch 771119325

Archived Log entry 5 added for thread 1 sequence 18179 ID 0x56b64d5b dest 1:

继续

-- 关闭数据库

sql> SHUTDOWN IMMEDIATE;

-- =================== 从备份集上面创建新的控制文件

sql> alter database create standby controlfile as '/DATABASE/oracle/ORACLE11G/oradata/IMDB/control03.ctl';

-- 备份当前的控制文件

[root@im_17_2 DATABASE]# cp /DATABASE/oracle/ORACLE11G/oradata/IMDB/control01.ctl /home/

[root@im_17_2 DATABASE]# cp /DATABASE/oracle/ORACLE11G/flash_recovery_area/IMDB/control02.ctl /home

-- 覆盖旧的控制文件

cp /DATABASE/oracle/ORACLE11G/oradata/IMDB/control03.ctl /DATABASE/oracle/ORACLE11G/flash_recovery_area/IMDB/control02.ctl

cp /DATABASE/oracle/ORACLE11G/oradata/IMDB/control03.ctl /DATABASE/oracle/ORACLE11G/oradata/IMDB/control01.ctl

至此基本搞定,问题原因是控制文件故障导致，所以重新覆盖控制文件。

-- 启动standby库

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.3495E+10 bytes

Fixed Size 2218032 bytes

Variable Size 6845106128 bytes

Database Buffers 6576668672 bytes

Redo Buffers 71471104 bytes

Database mounted.

SQL> select database_role from v$database

2 ;

DATABASE_ROLE

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

PHYSICAL STANDBY

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ ONLY

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

-- check检查，主从上面执行下面sql语句。

select count(*) from openfire.fpsession;

【】在此过程中，请密切关注从库的alert报警日志：

(1) 报警日志：

tail -f /DATABASE/oracle/ORACLE11G/diag/rdbms/dg02/IMDB/trace/alert_IMDB.log

(2) 查看是primary还是standby库：

select database_role from v$database

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

(3) 查看数据库启动状态：

select open_mode from v$database;

(4) 查看数据库dbid

select dbid from v$database;

(5) 查看报警日志路径：

select value from v$diag_info where name ='Diag Alert';

找到value值中的上一级目录的trace目录下alert_$ORACLE_SID.log

6 将备库置于自动恢复状态

SQL〉

recover managed standby database disconnect from session;(这里就开始自动修复了)

7.然后通过open resetlogs方式打开数据库

RMAN> alter database open resetlogs;

[PS]:本人Oracle半吊子，如果纪录中有错误，欢迎指点啊，好让我有所进步，谢谢！