Oracle搭建rac到单库的adg

上海主库rac的数据目录全放在ASM下面

北京备库的数据目录存放在/data/uencordb/下面

主库：

1、把密码文件拷贝过去

2、创建备库控制文件，拷贝到备库

alter database create standby controlfile as '/home/oracle/stdctl.ctl';

3、把redo改成合适的大小和组数

4、创建standby redo log  --主库创建这个是为了转换为备库，不转备库的话没必要建

alter system set standby_file_management='manual';

alter database add standby logfile thread 1 group 41 ('+DATA/uencordb/onlinelog/standby41.log') size 1g;

alter database add standby logfile thread 1 group 42 ('+DATA/uencordb/onlinelog/standby42.log') size 1g;

alter database add standby logfile thread 1 group 43 ('+DATA/uencordb/onlinelog/standby43.log') size 1g;

alter database add standby logfile thread 1 group 44 ('+DATA/uencordb/onlinelog/standby44.log') size 1g;

alter database add standby logfile thread 1 group 45 ('+DATA/uencordb/onlinelog/standby45.log') size 1g;

alter database add standby logfile thread 1 group 46 ('+DATA/uencordb/onlinelog/standby46.log') size 1g;

alter database add standby logfile thread 1 group 47 ('+DATA/uencordb/onlinelog/standby47.log') size 1g;

alter database add standby logfile thread 2 group 51 ('+DATA/uencordb/onlinelog/standby51.log') size 1g;

alter database add standby logfile thread 2 group 52 ('+DATA/uencordb/onlinelog/standby52.log') size 1g;

alter database add standby logfile thread 2 group 53 ('+DATA/uencordb/onlinelog/standby53.log') size 1g;

alter database add standby logfile thread 2 group 54 ('+DATA/uencordb/onlinelog/standby54.log') size 1g;

alter database add standby logfile thread 2 group 55 ('+DATA/uencordb/onlinelog/standby55.log') size 1g;

alter database add standby logfile thread 2 group 56 ('+DATA/uencordb/onlinelog/standby56.log') size 1g;

alter database add standby logfile thread 2 group 57 ('+DATA/uencordb/onlinelog/standby57.log') size 1g;

alter system set standby_file_management='auto';

5、修改主库数据库参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(uencordb,bjcordb)' scope=both  sid='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+ARCH  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=uencordb' scope=both  sid='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=bjcordb LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bjcordb' 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 LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both  sid='*';

ALTER SYSTEM SET FAL_CLIENT = 'uencordb' scope=both  sid='*';

ALTER SYSTEM SET FAL_SERVER ='bjcordb' scope=both  sid='*';

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO scope=both  sid='*';

如果rac主库要被切换为备库的话，就配置下面的参数，需要重启数据库

SQL> alter system set db_file_name_convert ='/data/uencordb/','+DATA/uencordb/datafile/','/data/uencordb/','+DATA/uencordb/tempfile/' scope=spfile sid='*';

SQL> alter system set log_file_name_convert='/data/uencordb/','+DATA/uencordb/onlinelog/' scope=spfile sid='*';

6、配置北京备库参数

*.audit_file_dest='/u01/app/oracle/admin/uencordb/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data/uencordb/control01.ctl','/data/uencordb/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='uencordb'

*.db_unique_name='bjcordb'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=uencordbXDB)'

*.fal_client='bjcordb'

*.fal_server='uencordb'

*.log_archive_config='dg_config=(uencordb,bjcordb)'

*.log_archive_dest_1='LOCATION=/data/arch/coradg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bjcordb'

*.log_archive_dest_2='SERVICE=uencordb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=uencordb'

*.db_file_name_convert='+DATA/uencordb/datafile/','/data/uencordb/','+DATA/uencordb/tempfile/','/data/uencordb/'

*.log_file_name_convert='+DATA/uencordb/onlinelog/','/data/uencordb/'

*.standby_file_management='auto'

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=16135487488

*.processes=5000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=5505

*.sga_max_size=85899345920

*.sga_target=48407511040

*.undo_tablespace='UNDOTBS1'

7、配置静态监听：

主库配置静态监听是为了配置dgbroker

主库节点1

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME = uencordb_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = uencordb1)

)

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME = uencordb_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = uencordb2)

)

)

北京备库

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME = uencordb_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/ dbhome_1)

(SID_NAME = uencordb)

)

)

8、配置好监听后，主备互相验证：

sqlplus sys/xxx@uencordb as sysdba

然后进去rman还原控制文件：

restore controlfile from '/home/oracle/stdctl.ctl';

然后重新进来做dumplicate

[oracle@adg admin]$ rman target sys/xxx@uencordb auxiliary sys/xxx@bjcordb

RMAN> duplicate target database for standby from active database;

恢复好后，进去sql模式(当前是mount)，开启进程追下归档

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

等日志追平后，再开到readonly

select thread#,sequence#, applied from v$archived_log order by 3 desc,2;

select THREAD#,max(SEQUENCE#) from v$archived_log group by THREAD#;

select process,status from v$managed_standby;

SQL>  alter database recover managed standby database cancel;

SQL> ALTER DATABASE OPEN READ ONLY;

结束