ORACLE RAC ADG容灾实施

--1、主库检查

检查修改主库的force logging 模式

sqlplus / as sysdba

SQL>select FORCE_LOGGING from v$database;

alter database force logging;

--主库remote_login_passwordfile 为EXCLUSIVE

show parameter remote_login_passwordfile

---2、主库参数

alter system set DB_UNIQUE_NAME=orcl scope=spfile;

alter system set log_archive_config='DG_CONFIG=(orcl,adg)' scope=both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=adgdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adg';

alter system set standby_file_management='AUTO' sid='*' scope=both;

alter system set fal_client='orcldg';

alter system set fal_server='adgdg';

alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

--3、新增tnsname.ora

--主库和备库 1节点

orcldg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.23)(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

adgdg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.133)(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ADG)

)

)

--主库和备库 2节点

orcldg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.12)(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

adgdg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.134)(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = adg)

)

)

--4、备份主库 和 control file

rman target /

backup as compressed backupset database format '/tmp/bak/racdb%U';

sqlplus / as sysdba

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/stb_ctl';

--5、创建pfile

sqlplus / as sysdba

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

more pfile

orcl1.__db_cache_size=855638016

orcl2.__db_cache_size=855638016

orcl1.__java_pool_size=16777216

orcl2.__java_pool_size=16777216

orcl1.__large_pool_size=33554432

orcl2.__large_pool_size=33554432

orcl2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl1.__pga_aggregate_target=872415232

orcl2.__pga_aggregate_target=872415232

orcl1.__sga_target=1275068416

orcl2.__sga_target=1275068416

orcl1.__shared_io_pool_size=0

orcl2.__shared_io_pool_size=0

orcl1.__shared_pool_size=352321536

orcl2.__shared_pool_size=352321536

orcl1.__streams_pool_size=0

orcl2.__streams_pool_size=0

*.aq_tm_processes=5

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

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+DATA/orcl/controlfile/current.261.995444255','+DATA/orcl/controlfile/current.260.995444255'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_name_convert='+DATA','+DATA'

*.db_name='orcl'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=1073741824

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

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

*.enable_goldengate_replication=TRUE

*.fal_client='orcldg'

*.fal_server='adgdg'

orcl1.instance_number=1

orcl2.instance_number=2

*.log_archive_config='DG_CONFIG=(orcl,adg)'

*.log_archive_dest_1='LOCATION=+data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_2='SERVICE=adg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adg'

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

*.log_file_name_convert='+DATA','+DATA'

*.memory_target=2147483648

*.open_cursors=300

*.processes=150

*.remote_listener='rac-scan:1521'

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

orcl2.thread=2

orcl1.thread=1

orcl2.undo_tablespace='UNDOTBS2'

orcl1.undo_tablespace='UNDOTBS1'

--6、拷贝到备库修改pfile

按照db_unique_name 不同修改，但要保持 db_name 相同，需要指定control file的位置

scp pfile.ora oracle@standby:/home/oracle/

more pfile.ora

adg1.__db_cache_size=855638016

adg2.__db_cache_size=855638016

adg1.__java_pool_size=16777216

adg2.__java_pool_size=16777216

adg1.__large_pool_size=33554432

adg2.__large_pool_size=33554432

adg2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

adg1.__pga_aggregate_target=872415232

adg2.__pga_aggregate_target=872415232

adg1.__sga_target=1275068416

adg2.__sga_target=1275068416

adg1.__shared_io_pool_size=0

adg2.__shared_io_pool_size=0

adg1.__shared_pool_size=352321536

adg2.__shared_pool_size=352321536

adg1.__streams_pool_size=0

adg2.__streams_pool_size=0

*.aq_tm_processes=5

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

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+DATA/adg/controlfile/control01.ctl','+DATA/adg/controlfile/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_name_convert='+DATA','+DATA'

*.db_name='orcl'

*.db_unique_name='adg'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=1073741824

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

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

*.enable_goldengate_replication=TRUE

*.fal_client='adgdg'

*.fal_server='adgdg'

adg1.instance_number=1

adg2.instance_number=2

*.log_archive_config='DG_CONFIG=(adg,adg)'

*.log_archive_dest_1='LOCATION=+data/ORCL/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adg'

*.log_archive_dest_2='SERVICE=adg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=adg'

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

*.log_file_name_convert='+DATA','+DATA'

*.memory_target=2147483648

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.standby_file_management='AUTO'

adg2.thread=2

adg1.thread=1

adg2.undo_tablespace='UNDOTBS2'

adg1.undo_tablespace='UNDOTBS1'

--7、备库创建目前并以pfile起动

mkdir -p /u01/app/oracle/admin/adg/adump

export ORACLE_SID=adg1

sqlplus / as sysdba

startup nomount pfile='/home/oracle/pfile.ora';

这里可能会报内存空间不足，可能是由于shm不足引起，需要重新挂载shm

[root@rac2 ~]# more /etc/fstab

LABEL=/ / ext3 defaults 1 1

LABEL=/boot /boot ext3 defaults 1 2

tmpfs /dev/shm tmpfs defaults,size=1024M 0 0

devpts /dev/pts devpts gid=5,mode=620 0 0

sysfs /sys sysfs defaults 0 0

proc /proc proc defaults 0 0

LABEL=SWAP-sda2 swap swap defaults 0 0

增大到了3G，然后重新mount即可生效。

mount -o remount /dev/shm

--8、拉起备库，并调整参数

sqlplus / as sysdba

startup nomount

create spfile='+data' from pfile='/home/oracle/pfile.ora';

vi $ORACLE_HOME/dbs/initadg1.ora

spfile='+DATA/adg/parameterfile/spfile.258.1004832971'

vi $ORACLE_HOME/dbs/initadg2.ora

spfile='+DATA/adg/parameterfile/spfile.258.1004832971'

shutdown immediate;

startup mount

alter system set DB_UNIQUE_NAME=adg scope=spfile;

alter system set log_archive_config='DG_CONFIG=(orcl,adg)' scope=both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=adg' scope=both;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';

alter system set standby_file_management='AUTO' sid='*' scope=both;

alter system set fal_client='adgdg';

alter system set fal_server='orcldg';

alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

--9、在备库还原control file和数据库

ASMCMD> cp /home/grid/stb_ctl control01.ctl

ASMCMD> cp /home/grid/stb_ctl control02.ctl

rman target /

catlog start with '/home/oracle/bak';

restore database;

catlog start with '+data/adg/datafile';

switch database to copy;

--10、在备库先添加standby redo，然后再在主库添加

alter database add standby logfile thread 1 group 51 '+data' size 50m;

alter database add standby logfile thread 1 group 61 '+data' size 50m;

alter database add standby logfile thread 2 group 71 '+data' size 50m;

alter database add standby logfile thread 2 group 81 '+data' size 50m;

--11、起动验证

sqlplus / as sysdba

alter database recover managed standby database using current logfile disconnect from session；

--开启read only模式

alter database recover managed standby database cancel;

alter database open read only;

alter database recover managed standby database using current logfile disconnect from session ;

--12、拉起2节点

注意环境变量，tnsnames.ora、监听状态

sqlplus / as sysdba

startup

--13、注册数据库到CRS

srvctl add database -d adg -n orcl -o $ORACLE_HOME

srvctl add instance -d adg -i adg1 -n adg1

srvctl add instance -d adg -i adg2 -n adg2

--14、检查

--备库

col scn for 9999999999999999999

select current_scn from v$database;

--主库

select current_scn as scn from dual;

select group#,thread#, SEQUENCE#,ARCHIVED from v$log;

select PROCESS,THREAD#,SEQUENCE# from v$managed_standby;