4節點RAC建立邏輯備庫
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（4）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（1）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（2）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（3）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（5）
- 4 Creating a Logical Standby Database 建立邏輯備庫Database
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (四)--新增一個物理dg節點
- 邏輯備庫Switchover
- 配置物理備庫+邏輯備庫
- dataguard回顧之安裝——建立邏輯備庫
- rac與邏輯備庫不能自動建表空間，物理備庫正常
- 11 管理邏輯備庫
- 主庫RAC,備庫單節點ASM的dataguard搭建ASM
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (三)
- 資料庫邏輯備份(轉)資料庫
- RAC一個節點恢復另一個節點在帶庫上的備份
- 邏輯備份全庫或者邏輯備份多個使用者的資料
- oracle邏輯備用資料庫（一）Oracle資料庫
- 刪除oracle10g rac(rhel4)節點_節點Oracle
- DG為RAC的邏輯備庫?LOG_AUTO_DELETE（TRUE）引數不起作用delete
- 邏輯Data Guard主備庫的轉換
- 【DataGuarad】邏輯遷移與standby備庫
- dataguard之邏輯備庫表空間不足
- 邏輯備庫上有指定表不應用
- ORACLE資料庫的邏輯備份(轉)Oracle資料庫
- Oracle 10g 邏輯Standby 建立及注意點Oracle 10g
- 邏輯備份--mysqldumpMySql
- 邏輯DG主備庫轉換的failoverAI
- linux建立邏輯卷，遠端共享為備份盤Linux
- 4、邏輯運算子
- RAC資料庫的RMAN備份異機恢復到單節點資料庫資料庫
- mysql 邏輯備份 （mysqldump）MySql
- mysql的邏輯備份MySql
- 建立邏輯STANDBY資料庫——DATA GUARD概念和管理資料庫
- 【RAC】刪除RAC資料庫節點（二）——刪除ASM資料庫ASM
- 【RAC】刪除RAC資料庫節點（五）——刪除ONS資料庫