oracle11g的adg需要付费嘛,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 / <

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

)

/