4节点RAC创建逻辑备库
1、搭建备库环境
1.1创建oracle用户以及相应文件夹
[root@justin ~]# cd /home
[root@justin home]# ls
lost+found
[root@justin home]# mkdir oracle
[root@justin home]# cd oracle
[root@justin oracle]# groupadd -g 700 oinstall
[root@justin oracle]# groupadd -g 900 dba
[root@justin oracle]# useradd -u 600 -g oinstall -G dba oracle
[root@justin home]# chown oracle /home/oracle
[root@justin home]# chmod -R 744 /home/oracle
1.2安装oracle软件
解压10205.tar.gz到/data/oracle/product
然后relink all
1.3配置oracle所需OS patch
red hat安装oracle需要配置OS,采用以下命令
yum install oracle-validated
不过该server上没有配置/etc/yum.repos.d/public-yum-el5.repo,从已有的物理备库上copy
1.4配置其他参数文件
配置tnsnames.ora
pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = racdg)
)
)
std03 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
配置listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /data/oracle/product/10205/db1)
(SID_NAME = orcl)
)
)
配置参数文件
*.db_file_name_convert='+DATA/pri/datafile','/data/oracle/oradata','+DATA/pri/tempfile','/data/oracle/oradata'
*.log_file_name_convert='+DATA/pri/onlinelog','/data/oracle/oradata','+ARCH','/data/oracle/oradata'
*.log_archive_config='DG_CONFIG=(pri,std01,std02,std03)'
*.log_archive_dest_1='LOCATION=/data/oracle/oradata/arch'
*.FAL_CLIENT='std03'
*.fal_server='pri'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_UNIQUE_NAME='std03'
*.db_name='ORCL'
复制standby控制文件
从物理备库02上复制,如果从主库复制,恢复的时候还需要为每个数据文件设置set newname for datafile
2、配置RAC主库
2.1修改tnsnames.ora
std03 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.2修改参数文件(使用pfile参数)
*.log_archive_config='DG_CONFIG=(pri,std01,std02,std03)'
*.log_archive_dest_7='SERVICE=std03 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std03'
*.log_archive_dest_state_7='ENABLE'
并且在sqlplus中做修改
2.3将口令文件传送到remote server
3、利用RMAN恢复物理备库
3.1恢复rman备份
run
{allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
restore database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
期间监控rman恢复进度,整个过程预计消耗2-3个小时
SQL> SELECT inst_id,
2 sid,
3 serial#,
4 opname,
5 ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
6 FROM gV$SESSION_LONGOPS
7 WHERE OPNAME LIKE 'RMAN%'
8 --AND OPNAME NOT LIKE '%aggregate%'
9 AND TOTALWORK != 0
10 AND SOFAR <> TOTALWORK;
INST_ID SID SERIAL# OPNAME %_COMPLETE
---------- ---------- ---------- ---------------------------------------------------------------- ----------
1 1084 1 RMAN: full datafile restore 22.15
1 1083 1 RMAN: full datafile restore 24.81
1 1085 5 RMAN: full datafile restore 25.41
1 1086 28 RMAN: aggregate input 99.99
1 1091 40 RMAN: full datafile restore 31.32
改进一下,预测语句执行时间长度
SQL> select inst_id,sid,serial#,opname,COMPLETE,
2 trunc(((to_char(last_update_time,'dd')-to_char(start_time,'dd'))*60*24+(to_char(last_update_time,'hh24')-to_char(start_time,'hh24'))*60 +(to_char(last_update_time,'mi')-to_char(start_time,'mi')))*100/complete) min from
3 (
4 SELECT inst_id,
5 sid,
6 serial#,
7 opname,
8 ROUND(SOFAR / TOTALWORK * 100, 2) COMPLETE,
9 LAST_UPDATE_TIME,
10 START_TIME
11 FROM gV$SESSION_LONGOPS
12 WHERE OPNAME LIKE 'RMAN%'
13 --AND OPNAME NOT LIKE '%aggregate%'
14 AND TOTALWORK != 0
15 AND SOFAR <> TOTALWORK
16 ) t ;
INST_ID SID SERIAL# OPNAME COMPLETE MIN
---------- ---------- ---------- ---------------------------------------------------------------- ---------- ----------
1 1084 1 RMAN: full datafile restore 82.62 124
1 1083 1 RMAN: full datafile restore 81.01 127
1 1085 5 RMAN: full datafile restore 95.42 107
1 1086 28 RMAN: aggregate input 100 102
3.2传输相应日志
Rman恢复时同时从02复制剩余archive log,时间范围:从rman备份开始到现在
select name, thread#, to_char(COMPLETION_TIME, 'yyyy-mm-dd hh24:mi:ss')
from v$archived_log
where COMPLETION_TIME between to_date('2011-06-02 05:30:00', 'yyyy-mm-dd hh24:mi:ss') and sysdate;
总共好几十个不可能一条一条的这么手工输入
scp /data/oracle/oradata/orcl/arch/4_85_657561562.dbf *.*.*.*:/data/oracle/rman
配置02和55的oracle用户等价,然后将所有语句放入shell脚本,运行脚本即可实现自动化
3.3添加standby log
restore完成后,为备库添加缺失的standby log(添加之前需要先挨个drop)
alter database add standby logfile group 9 '/data/oracle/oradata/stdby_redo9.log' size 500m;
alter database add standby logfile group 10 '/data/oracle/oradata/stdby_redo10.log' size 500m;
alter database add standby logfile group 11 '/data/oracle/oradata/stdby_redo11.log' size 500m;
alter database add standby logfile group 12 '/data/oracle/oradata/stdby_redo12.log' size 500m;
alter database add standby logfile group 13 '/data/oracle/oradata/stdby_redo13.log' size 500m;
alter database add standby logfile group 14 '/data/oracle/oradata/stdby_redo14.log' size 500m;
alter database add standby logfile group 3 '/data/oracle/oradata/stdby_redo3.log' size 500m;
alter database add standby logfile group 4 '/data/oracle/oradata/stdby_redo4.log' size 500m;
alter database add standby logfile group 5 '/data/oracle/oradata/stdby_redo5.log' size 500m;
alter database add standby logfile group 6 '/data/oracle/oradata/stdby_redo6.log' size 500m;
alter database add standby logfile group 7 '/data/oracle/oradata/stdby_redo7.log' size 500m;
alter database add standby logfile group 8 '/data/oracle/oradata/stdby_redo8.log' size 500m;
alter database add standby logfile group 25 '/data/oracle/oradata/stdby_redo25.log' size 500m;
alter database add standby logfile group 26 '/data/oracle/oradata/stdby_redo26.log' size 500m;
alter database add standby logfile group 27 '/data/oracle/oradata/stdby_redo27.log' size 500m;
alter database add standby logfile group 28 '/data/oracle/oradata/stdby_redo28.log' size 500m;
alter database add standby logfile group 29 '/data/oracle/oradata/stdby_redo29.log' size 500m;
alter database add standby logfile group 30 '/data/oracle/oradata/stdby_redo30.log' size 500m;
alter database add standby logfile group 31 '/data/oracle/oradata/stdby_redo31.log' size 500m;
alter database add standby logfile group 32 '/data/oracle/oradata/stdby_redo32.log' size 500m;
alter database add standby logfile group 33 '/data/oracle/oradata/stdby_redo33.log' size 500m;
alter database add standby logfile group 34 '/data/oracle/oradata/stdby_redo34.log' size 500m;
alter database add standby logfile group 35 '/data/oracle/oradata/stdby_redo35.log' size 500m;
alter database add standby logfile group 36 '/data/oracle/oradata/stdby_redo36.log' size 500m;
物理备库建成
4、转换为逻辑备库
--在主库的节点4上执行
SQL> set timing on
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
Elapsed: 00:00:27.69
--在物理备库执行
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2126480 bytes
Variable Size 2147487088 bytes
Database Buffers 1.5016E+10 bytes
Redo Buffers 14647296 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
5、错误处理
逻辑备库建成后并没有应用从主库传来的日志, 查看相应视图
V$LOGSTDBY_PROGRESS中的applied_time值为null,
v$logstdby_state 里显示在等待字典信息
SQL> select session_id,state from v$logstdby_state;
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 WAITING FOR DICTIONARY LOGS
而DBA_LOGSTDBY_LOG中好多没有应用的log file
SQL> select file_name,applied from DBA_LOGSTDBY_LOG ;
FILE_NAME APPLIED
---------------------------------------------------------------------------------------------------- --------
/data/oracle/oradata/arch/1_116718_657561562.dbf NO
/data/oracle/oradata/arch/1_116719_657561562.dbf NO
/data/oracle/oradata/arch/1_116720_657561562.dbf NO
/data/oracle/oradata/arch/1_116721_657561562.dbf NO
/data/oracle/oradata/arch/1_116722_657561562.dbf NO
/data/oracle/oradata/arch/1_116723_657561562.dbf NO
/data/oracle/oradata/arch/2_3639_657561562.dbf NO
/data/oracle/oradata/arch/2_3640_657561562.dbf NO
/data/oracle/oradata/arch/2_3641_657561562.dbf NO
/data/oracle/oradata/arch/2_3642_657561562.dbf NO
/data/oracle/oradata/arch/2_3643_657561562.dbf NO
/data/oracle/oradata/arch/3_499_657561562.dbf NO
/data/oracle/oradata/arch/3_500_657561562.dbf NO
/data/oracle/oradata/arch/3_501_657561562.dbf NO
/data/oracle/oradata/arch/4_103_657561562.dbf CURRENT
/data/oracle/oradata/arch/4_104_657561562.dbf CURRENT
/data/oracle/oradata/arch/4_105_657561562.dbf NO
/data/oracle/oradata/arch/4_106_657561562.dbf NO
/data/oracle/oradata/arch/4_107_657561562.dbf NO
/data/oracle/oradata/arch/4_108_657561562.dbf NO
/data/oracle/oradata/arch/4_109_657561562.dbf NO
/data/oracle/oradata/arch/4_110_657561562.dbf NO
查看alert.log,发现有如下错误,日志路径有问题:
LOGSTDBY: Attempting to pre-register dictionary build logfiles
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch3_495_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch1_116714_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch3_496_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch4_103_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch2_3636_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch2_3635_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch1_116715_657561562.dbf
解决方案:
手工注册
alter database stop logical standby apply;
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/3_495_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/1_116714_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/3_496_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/4_103_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/2_3636_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/2_3635_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/1_116715_657561562.dbf';
alter database start logical standby apply immediate;
目前的情况
SQL> select file_name,applied from DBA_LOGSTDBY_LOG ;
FILE_NAME APPLIED
---------------------------------------------------------------------------------------------------- --------
/data/oracle/oradata/arch/1_116730_657561562.dbf CURRENT
/data/oracle/oradata/arch/2_3654_657561562.dbf CURRENT
/data/oracle/oradata/arch/2_3652_657561562.dbf CURRENT
/data/oracle/oradata/arch/2_3653_657561562.dbf CURRENT
/data/oracle/oradata/arch/4_114_657561562.dbf CURRENT
/data/oracle/oradata/arch/3_506_657561562.dbf CURRENT
SQL> select session_id,state from v$logstdby_state;
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 APPLYING
1.1创建oracle用户以及相应文件夹
[root@justin ~]# cd /home
[root@justin home]# ls
lost+found
[root@justin home]# mkdir oracle
[root@justin home]# cd oracle
[root@justin oracle]# groupadd -g 700 oinstall
[root@justin oracle]# groupadd -g 900 dba
[root@justin oracle]# useradd -u 600 -g oinstall -G dba oracle
[root@justin home]# chown oracle /home/oracle
[root@justin home]# chmod -R 744 /home/oracle
1.2安装oracle软件
解压10205.tar.gz到/data/oracle/product
然后relink all
1.3配置oracle所需OS patch
red hat安装oracle需要配置OS,采用以下命令
yum install oracle-validated
不过该server上没有配置/etc/yum.repos.d/public-yum-el5.repo,从已有的物理备库上copy
1.4配置其他参数文件
配置tnsnames.ora
pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = racdg)
)
)
std03 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
配置listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /data/oracle/product/10205/db1)
(SID_NAME = orcl)
)
)
配置参数文件
*.db_file_name_convert='+DATA/pri/datafile','/data/oracle/oradata','+DATA/pri/tempfile','/data/oracle/oradata'
*.log_file_name_convert='+DATA/pri/onlinelog','/data/oracle/oradata','+ARCH','/data/oracle/oradata'
*.log_archive_config='DG_CONFIG=(pri,std01,std02,std03)'
*.log_archive_dest_1='LOCATION=/data/oracle/oradata/arch'
*.FAL_CLIENT='std03'
*.fal_server='pri'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_UNIQUE_NAME='std03'
*.db_name='ORCL'
复制standby控制文件
从物理备库02上复制,如果从主库复制,恢复的时候还需要为每个数据文件设置set newname for datafile
2、配置RAC主库
2.1修改tnsnames.ora
std03 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.*)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.2修改参数文件(使用pfile参数)
*.log_archive_config='DG_CONFIG=(pri,std01,std02,std03)'
*.log_archive_dest_7='SERVICE=std03 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std03'
*.log_archive_dest_state_7='ENABLE'
并且在sqlplus中做修改
2.3将口令文件传送到remote server
3、利用RMAN恢复物理备库
3.1恢复rman备份
run
{allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
restore database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
期间监控rman恢复进度,整个过程预计消耗2-3个小时
SQL> SELECT inst_id,
2 sid,
3 serial#,
4 opname,
5 ROUND(SOFAR / TOTALWORK * 100, 2) "%_COMPLETE"
6 FROM gV$SESSION_LONGOPS
7 WHERE OPNAME LIKE 'RMAN%'
8 --AND OPNAME NOT LIKE '%aggregate%'
9 AND TOTALWORK != 0
10 AND SOFAR <> TOTALWORK;
INST_ID SID SERIAL# OPNAME %_COMPLETE
---------- ---------- ---------- ---------------------------------------------------------------- ----------
1 1084 1 RMAN: full datafile restore 22.15
1 1083 1 RMAN: full datafile restore 24.81
1 1085 5 RMAN: full datafile restore 25.41
1 1086 28 RMAN: aggregate input 99.99
1 1091 40 RMAN: full datafile restore 31.32
改进一下,预测语句执行时间长度
SQL> select inst_id,sid,serial#,opname,COMPLETE,
2 trunc(((to_char(last_update_time,'dd')-to_char(start_time,'dd'))*60*24+(to_char(last_update_time,'hh24')-to_char(start_time,'hh24'))*60 +(to_char(last_update_time,'mi')-to_char(start_time,'mi')))*100/complete) min from
3 (
4 SELECT inst_id,
5 sid,
6 serial#,
7 opname,
8 ROUND(SOFAR / TOTALWORK * 100, 2) COMPLETE,
9 LAST_UPDATE_TIME,
10 START_TIME
11 FROM gV$SESSION_LONGOPS
12 WHERE OPNAME LIKE 'RMAN%'
13 --AND OPNAME NOT LIKE '%aggregate%'
14 AND TOTALWORK != 0
15 AND SOFAR <> TOTALWORK
16 ) t ;
INST_ID SID SERIAL# OPNAME COMPLETE MIN
---------- ---------- ---------- ---------------------------------------------------------------- ---------- ----------
1 1084 1 RMAN: full datafile restore 82.62 124
1 1083 1 RMAN: full datafile restore 81.01 127
1 1085 5 RMAN: full datafile restore 95.42 107
1 1086 28 RMAN: aggregate input 100 102
3.2传输相应日志
Rman恢复时同时从02复制剩余archive log,时间范围:从rman备份开始到现在
select name, thread#, to_char(COMPLETION_TIME, 'yyyy-mm-dd hh24:mi:ss')
from v$archived_log
where COMPLETION_TIME between to_date('2011-06-02 05:30:00', 'yyyy-mm-dd hh24:mi:ss') and sysdate;
总共好几十个不可能一条一条的这么手工输入
scp /data/oracle/oradata/orcl/arch/4_85_657561562.dbf *.*.*.*:/data/oracle/rman
配置02和55的oracle用户等价,然后将所有语句放入shell脚本,运行脚本即可实现自动化
3.3添加standby log
restore完成后,为备库添加缺失的standby log(添加之前需要先挨个drop)
alter database add standby logfile group 9 '/data/oracle/oradata/stdby_redo9.log' size 500m;
alter database add standby logfile group 10 '/data/oracle/oradata/stdby_redo10.log' size 500m;
alter database add standby logfile group 11 '/data/oracle/oradata/stdby_redo11.log' size 500m;
alter database add standby logfile group 12 '/data/oracle/oradata/stdby_redo12.log' size 500m;
alter database add standby logfile group 13 '/data/oracle/oradata/stdby_redo13.log' size 500m;
alter database add standby logfile group 14 '/data/oracle/oradata/stdby_redo14.log' size 500m;
alter database add standby logfile group 3 '/data/oracle/oradata/stdby_redo3.log' size 500m;
alter database add standby logfile group 4 '/data/oracle/oradata/stdby_redo4.log' size 500m;
alter database add standby logfile group 5 '/data/oracle/oradata/stdby_redo5.log' size 500m;
alter database add standby logfile group 6 '/data/oracle/oradata/stdby_redo6.log' size 500m;
alter database add standby logfile group 7 '/data/oracle/oradata/stdby_redo7.log' size 500m;
alter database add standby logfile group 8 '/data/oracle/oradata/stdby_redo8.log' size 500m;
alter database add standby logfile group 25 '/data/oracle/oradata/stdby_redo25.log' size 500m;
alter database add standby logfile group 26 '/data/oracle/oradata/stdby_redo26.log' size 500m;
alter database add standby logfile group 27 '/data/oracle/oradata/stdby_redo27.log' size 500m;
alter database add standby logfile group 28 '/data/oracle/oradata/stdby_redo28.log' size 500m;
alter database add standby logfile group 29 '/data/oracle/oradata/stdby_redo29.log' size 500m;
alter database add standby logfile group 30 '/data/oracle/oradata/stdby_redo30.log' size 500m;
alter database add standby logfile group 31 '/data/oracle/oradata/stdby_redo31.log' size 500m;
alter database add standby logfile group 32 '/data/oracle/oradata/stdby_redo32.log' size 500m;
alter database add standby logfile group 33 '/data/oracle/oradata/stdby_redo33.log' size 500m;
alter database add standby logfile group 34 '/data/oracle/oradata/stdby_redo34.log' size 500m;
alter database add standby logfile group 35 '/data/oracle/oradata/stdby_redo35.log' size 500m;
alter database add standby logfile group 36 '/data/oracle/oradata/stdby_redo36.log' size 500m;
物理备库建成
4、转换为逻辑备库
--在主库的节点4上执行
SQL> set timing on
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
PL/SQL procedure successfully completed.
Elapsed: 00:00:27.69
--在物理备库执行
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2126480 bytes
Variable Size 2147487088 bytes
Database Buffers 1.5016E+10 bytes
Redo Buffers 14647296 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
5、错误处理
逻辑备库建成后并没有应用从主库传来的日志, 查看相应视图
V$LOGSTDBY_PROGRESS中的applied_time值为null,
v$logstdby_state 里显示在等待字典信息
SQL> select session_id,state from v$logstdby_state;
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 WAITING FOR DICTIONARY LOGS
而DBA_LOGSTDBY_LOG中好多没有应用的log file
SQL> select file_name,applied from DBA_LOGSTDBY_LOG ;
FILE_NAME APPLIED
---------------------------------------------------------------------------------------------------- --------
/data/oracle/oradata/arch/1_116718_657561562.dbf NO
/data/oracle/oradata/arch/1_116719_657561562.dbf NO
/data/oracle/oradata/arch/1_116720_657561562.dbf NO
/data/oracle/oradata/arch/1_116721_657561562.dbf NO
/data/oracle/oradata/arch/1_116722_657561562.dbf NO
/data/oracle/oradata/arch/1_116723_657561562.dbf NO
/data/oracle/oradata/arch/2_3639_657561562.dbf NO
/data/oracle/oradata/arch/2_3640_657561562.dbf NO
/data/oracle/oradata/arch/2_3641_657561562.dbf NO
/data/oracle/oradata/arch/2_3642_657561562.dbf NO
/data/oracle/oradata/arch/2_3643_657561562.dbf NO
/data/oracle/oradata/arch/3_499_657561562.dbf NO
/data/oracle/oradata/arch/3_500_657561562.dbf NO
/data/oracle/oradata/arch/3_501_657561562.dbf NO
/data/oracle/oradata/arch/4_103_657561562.dbf CURRENT
/data/oracle/oradata/arch/4_104_657561562.dbf CURRENT
/data/oracle/oradata/arch/4_105_657561562.dbf NO
/data/oracle/oradata/arch/4_106_657561562.dbf NO
/data/oracle/oradata/arch/4_107_657561562.dbf NO
/data/oracle/oradata/arch/4_108_657561562.dbf NO
/data/oracle/oradata/arch/4_109_657561562.dbf NO
/data/oracle/oradata/arch/4_110_657561562.dbf NO
查看alert.log,发现有如下错误,日志路径有问题:
LOGSTDBY: Attempting to pre-register dictionary build logfiles
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch3_495_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch1_116714_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch3_496_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch4_103_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch2_3636_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch2_3635_657561562.dbf
Fri Jun 03 06:35:03 CST 2011
LOGMINER: Error 308 encountered, failed to read logfile /data/oracle/oradata/arch1_116715_657561562.dbf
解决方案:
手工注册
alter database stop logical standby apply;
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/3_495_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/1_116714_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/3_496_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/4_103_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/2_3636_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/2_3635_657561562.dbf';
ALTER DATABASE REGISTER LOGICAL LOGFILE '/data/oracle/oradata/arch/1_116715_657561562.dbf';
alter database start logical standby apply immediate;
目前的情况
SQL> select file_name,applied from DBA_LOGSTDBY_LOG ;
FILE_NAME APPLIED
---------------------------------------------------------------------------------------------------- --------
/data/oracle/oradata/arch/1_116730_657561562.dbf CURRENT
/data/oracle/oradata/arch/2_3654_657561562.dbf CURRENT
/data/oracle/oradata/arch/2_3652_657561562.dbf CURRENT
/data/oracle/oradata/arch/2_3653_657561562.dbf CURRENT
/data/oracle/oradata/arch/4_114_657561562.dbf CURRENT
/data/oracle/oradata/arch/3_506_657561562.dbf CURRENT
SQL> select session_id,state from v$logstdby_state;
SESSION_ID STATE
---------- ----------------------------------------------------------------
1 APPLYING
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-697085/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-697085/