Oracle11g ADG 搭建

環境：
Oracle 11.2.0.4 single instance 兩套
備庫只安裝Oracle軟件及監聽。
一、主庫操作
1、主庫備份pfile以便記錄原參數

SQL>create pfile='/home/oracle/pfilebak.ora'  from spfile;

2、修改數據庫參數

 更改force logging： alter database force logging;
 歸檔模式：archive log list; ###為歸檔模式
 查看：select log_mode,force_logging from v$database;
alter system set log_archive_config='DG_CONFIG=(orcl,prod)' scope=spfile;
alter system set log_archive_dest_1='location=/u01/app/archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'  scope=both sid='*';
alter system set log_archive_dest_2='service=prod LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=prod'  scope=both sid='*';
alter system set log_archive_dest_state_1='enable' scope=both sid='*';
alter system set log_archive_dest_state_2='enable' scope=both sid='*';
alter system set fal_client='orcl'  scope=both sid='*';
alter system set fal_server='prod'  scope=both sid='*';
alter system set standby_file_management='AUTO'  scope=both sid='*';
文件路徑轉換參數需要重啟數據庫生效
alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/prod/' scope=spfile sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/prod/'  scope=spfile sid='*';
創建最新pfile文件SQL> create pfile='/home/oracle/pfile.ora' from spfile;

二、備庫操作
檢查目錄ORALCE_BASE,ORACLE_HOME,archive_log,orainventory,controlfile,datafile,adump
三、主庫備份

vi /u01/backup/rman.sh
chmod 777 rman.sh
nohup sh /u01/backup/rman.sh &
export ORACLE_SID=orcl
rman target / <<EOF
run{
allocate channel a1 device type disk;
allocate channel a2 device type disk;
allocate channel a3 device type disk;
crosscheck archivelog all;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup full database format='/u01/backup/full_%U%T' include current controlfile for standby;
backup current controlfile for standby format '/u01/backup/control01.ctl';
backup archivelog all format '/u01/backup/arch_%d_%T_%U.arc';
release channel a1;
release channel a2;
release channel a3;
}
exit;
EOF

四、主庫操作
scp 密碼文件（需要將的sid改為備庫sid)，最新pfile文件，備份文件，redo
五、備庫操作

更改pfile文件
db_name='orcl'應與主庫一致
*.db_unique_name='prod'
*.audit_file_dest='/u01/app/oracle/admin/prod/adump' 注意路徑
log_archive_dest_1='location=/u01/archivelog'
*.db_recovery_file_dest
修改oracle_base
刪除log_archive_dest_2
控制文件路徑
檢查文件中的所有路徑是否正確

六、恢復備庫

SQL>startup nomount pfile='/home/oracle/pfile.ora';
rman target / nocatalog
RMAN> restore standby controlfile from '/u01/backup/control01.ctl';
SQL>alter database mount;
catalog start with '/u01/backup/';
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
set newname for datafile  1 to '/u01/app/oracle/oradata/prod/system01.dbf';
set newname for datafile  2 to '/u01/app/oracle/oradata/prod/sysaux01.dbf';
set newname for datafile  3 to '/u01/app/oracle/oradata/prod/undotbs01.dbf';
set newname for datafile  4 to '/u01/app/oracle/oradata/prod/users01.dbf';
set newname for datafile  5 to '/u01/app/oracle/oradata/prod/example01.dbf';
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
recover database;

七、主備庫添加standby日志(比online log至少多一個)

主庫
alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/orcl/standby04.log') size 50M;
alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/orcl/standby05.log') size 50M;
alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/orcl/standby06.log') size 50M;
alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/orcl/standby07.log') size 50M;
alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/oradata/orcl/standby08.log') size 50M;
備庫
alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/prod/standby04.log') size 50M;
alter database add standby logfile thread 1 group 5 ('/u01/app/oracle/oradata/prod/standby05.log') size 50M;
alter database add standby logfile thread 1 group 6 ('/u01/app/oracle/oradata/prod/standby06.log') size 50M;
alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/prod/standby07.log') size 50M;
alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/oradata/prod/standby08.log') size 50M;

八、主備庫tnsnames一致
測試 tnsping orcl tnsping prod
九、備庫操作

SQL>alter database recover managed standby database disconnect from session;
SQL> recover managed standby database cancel;
SQL>alter database open read only;
SQL>alter database recover managed standby database using current logfile disconnect from session;

十、驗證

主庫 v$archived_log
SQL> select thread#,max(sequence#) from v$archived_log where applied='NO' group by thread#;
備庫 v$archived_log
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
備庫 v$managed_standby;
select process,status,thread#,sequence# from v$managed_standby;
或者
SELECT 'RECEIVED'||chr(9)||rtrim(received1)||'-> '||received_time1||chr(9)||rtrim(received2)||'-> '||received_time2
FROM
(select max(sequence#) received1, to_char(max(next_time),'YYYY/MM/DD HH24:MI:SS') RECEIVED_TIME1
from V$ARCHIVED_LOG
where thread#=1
),
(select max(sequence#) received2, to_char(max(next_time),'YYYY/MM/DD HH24:MI:SS') RECEIVED_TIME2
from V$ARCHIVED_LOG
where thread#=2
)
/
SELECT 'APPLIED '||chr(9)||rtrim(applied1)||'-> '||applied_time1||chr(9)||rtrim(applied2)||'-> '||applied_time2
from
(select MAX(SEQUENCE#) applied1, TO_CHAR(MAX(COMPLETION_TIME),'YYYY/MM/DD HH24:MI:SS') APPLIED_TIME1
from V$ARCHIVED_LOG where applied='YES' and thread#=1
),
(select MAX(SEQUENCE#) applied2, TO_CHAR(MAX(COMPLETION_TIME),'YYYY/MM/DD HH24:MI:SS') APPLIED_TIME2
from V$ARCHIVED_LOG where applied='YES' and thread#=2
)
/