ORACLE RAC+DG調整redo大小

由於資料庫還沒有正式上生產，資料量很小，本案例先調整RAC主庫Redo，主庫調整完成後重建dg備庫；

一:調整redo log file
(1) 建立臨時日誌組,5,6,7,8;
(2) 切換當前日誌組到新建的臨時日誌組；
(3) 刪除原日誌組1,2,3,4;
(4) 建立新日誌組1,2,3,4，同時調大redo大小；
(5) 切換當前日誌組到新建的日誌組；
(6) 刪除臨時日誌組,5,6,7,8;

二:調整standby log file 
(1)停止備庫例項；
(2)刪除原standby log file日誌組；
(3)建立新日誌組，同時調大redo大小；

三:重建立standby備庫
(1)刪除備庫資料檔案，日誌檔案，控制檔案等；
(2)重建standby備庫

四:校驗主、備庫資料是同步
五:常見問題

一:調整redo log file 
檢視主庫online redo資訊。
select a.members,
       a.thread#,
       a.status,
       a.bytes / 1024 / 1024,
       b.type,
       b.member,
       b.group#
  from v$log a, v$logfile b
 where a.group# = b.group#;

(1) 建立臨時日誌組,5,6,7,8;
alter database add logfile thread 1 group 5 ('+DATA/ncdb/onlinelog/redo05a.log','+FRA/ncdb/onlinelog/redo05b.log') size 100M; 
alter database add logfile thread 1 group 6 ('+DATA/ncdb/onlinelog/redo06a.log','+FRA/ncdb/onlinelog/redo06b.log') size 100M; 
alter database add logfile thread 2 group 7 ('+DATA/ncdb/onlinelog/redo07a.log','+FRA/ncdb/onlinelog/redo07b.log') size 100M; 
alter database add logfile thread 2 group 8 ('+DATA/ncdb/onlinelog/redo08a.log','+FRA/ncdb/onlinelog/redo08b.log') size 100M; 

(2) 切換當前日誌組到新建的臨時日誌組；
alter system switch logfile; ---切換當前日誌組
alter system checkpoint;  ---將ACTIVE切換到INACTIVE

(3) 刪除原日誌組1,2,3,4;
alter database drop logfile group 1; 
alter database drop logfile group 2; 
alter database drop logfile group 3; 
alter database drop logfile group 4; 

(4) 建立新日誌組1,2,3,4，同時調大redo大小；
alter database add logfile thread 1 group 1 ('+DATA/ncdb/onlinelog/redo01a.log','+FRA/ncdb/onlinelog/redo01b.log') size 100M; 
alter database add logfile thread 1 group 2 ('+DATA/ncdb/onlinelog/redo02a.log','+FRA/ncdb/onlinelog/redo02b.log') size 100M; 
alter database add logfile thread 2 group 3 ('+DATA/ncdb/onlinelog/redo03a.log','+FRA/ncdb/onlinelog/redo03b.log') size 100M; 
alter database add logfile thread 2 group 4 ('+DATA/ncdb/onlinelog/redo04a.log','+FRA/ncdb/onlinelog/redo04b.log') size 100M; 

(5) 切換當前日誌組到新建的日誌組；
alter system switch logfile; ---切換當前日誌組
alter system checkpoint;  ---將ACTIVE切換到INACTIVE

(6) 刪除臨時日誌組,5,6,7,8;
alter database drop logfile group 5; 
alter database drop logfile group 6; 
alter database drop logfile group 7; 
alter database drop logfile group 8; 

二:調整standby log file 
檢視standby log file資訊
select a.member,
       a.thread#,
       a.status,
       b.bytes / 1024 / 1024,
       b.group#,
       b.used / 1024 / 1024,
       b.status
  from v$logfile a, v$standby_log b
 where a.group# = b.group#;

(1)停止備庫例項；
(2)刪除原standby log file日誌組； 
alter database drop standby logfile group 11; 
alter database drop standby logfile group 12; 
alter database drop standby logfile group 13; 
alter database drop standby logfile group 14; 
alter database drop standby logfile group 15; 
alter database drop standby logfile group 16; 
alter database drop standby logfile group 17; 
alter database drop standby logfile group 18; 
alter database drop standby logfile group 19; 
alter database drop standby logfile group 20; 

(3)建立新日誌組，同時調大redo大小；
alter database add standby logfile thread 1 group 11 '+DATA/ncdb/onlinelog/standby_redo11.log' size 100m;
alter database add standby logfile thread 1 group 12 '+DATA/ncdb/onlinelog/standby_redo12.log' size 100m;
alter database add standby logfile thread 1 group 13 '+DATA/ncdb/onlinelog/standby_redo13.log' size 100m;
alter database add standby logfile thread 1 group 14 '+DATA/ncdb/onlinelog/standby_redo14.log' size 100m;
alter database add standby logfile thread 1 group 15 '+DATA/ncdb/onlinelog/standby_redo15.log' size 100m;
alter database add standby logfile thread 2 group 16 '+DATA/ncdb/onlinelog/standby_redo16.log' size 100m;
alter database add standby logfile thread 2 group 17 '+DATA/ncdb/onlinelog/standby_redo17.log' size 100m;
alter database add standby logfile thread 2 group 18 '+DATA/ncdb/onlinelog/standby_redo18.log' size 100m;
alter database add standby logfile thread 2 group 19 '+DATA/ncdb/onlinelog/standby_redo19.log' size 100m;
alter database add standby logfile thread 2 group 20 '+DATA/ncdb/onlinelog/standby_redo20.log' size 100m;

三:重建立standby備庫
(1)刪除備庫資料檔案，日誌檔案，控制檔案等；
(2)重建standby備庫
[oracle@rac1 ~]$ rman target / auxiliary sys/oracle@standby
RMAN> duplicate target database for standby from active database dorecover;

四:校驗主、備庫資料是同步
---啟動備庫
SQL> alter database open;
---啟動mgr
SQL> alter database recover managed standby database disconnect from session;
---啟動實時應用
SQL> recover managed standby database cancel;
SQL> recover managed standby database using current logfile disconnect from session;

---主庫rac1建立測試資料
create tablespace test datafile '+DATA/ncdb/datafile/test01.dbf' size 10M;
create user test identified by a default tablespace test;
grant connect,resource,dba to test;
create table test.t1 as select level as id from dual connect by level <=10;

---主庫rac1,rac2,備庫standby檢視資料是否同步；
select * from test.t1;


五:常見問題
如果不取樣重建備庫的方式，直接在備庫新增或刪除日誌組可能會遇到如下問題：

(1)ORA-01156:進行中的恢復或閃回可能需要訪問檔案
alter database recover managed standby database cancel;

(2)ORA-01275:自動進行備用檔案管理時，不允許進行ADD LOGFILE操作
alter system set standby_file_management=manual;

(3)ORA-01624,ORA-00312:
alter database clear logfile group xxx;
alter database drop logfile group xxx;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2152256/，如需轉載，請註明出處，否則將追究法律責任。