rac-single data guard-白红宇

rac-single data guard

阅读量：5836 次

发布时间：2019-06-18

本文共 10087 字，大约阅读时间需要 33 分钟。

一、环境

操作系统： Red Hat Enterprise Linux Server release 5 (Tikanga)

Primary数据库(rac)：

IP地址：172.168.1.206 172.168.1.208

vip地址：172.168.1.116 172.168.1.118

数据库SID：syk1 syk2

DB_NAME：syk

数据、控制、日志文件放在 ASM

Standby数据库(单机)：

IP地址：172.168.1.222

数据库SID：syk

DB_NAME：syk

数据、控制、日志文件使用文件系统 /oradata

二、tnsnames.ora在rac和standby相同

SYK2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = syk)

(INSTANCE_NAME = syk2)

)

SYK1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = syk)

(INSTANCE_NAME = syk1)

)

SYK =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = syk)

)

SYK_SINGLE =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = syk)

)

LISTENERS_SYK =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

standby上的listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = syk)

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

(SID_NAME = syk)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

三、修改参数(在rac1上如下操作)

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

添加如下参数

*.db_unique_name='SYK'

*.service_names='syk_rac'

*.log_archive_config='dg_config=(syk,syk_single)'

*.log_archive_dest_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='syk'

*.log_archive_dest_2='service=syk_single LGWR valid_for=(online_logfiles,primary_role) db_unique_name=syk_single'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.db_file_name_convert='/oradata/syk','+ASM_DATA/syk/datafile','/oradata/syk','+ASM_DATA/syk/tempfile'

*.log_file_name_convert='/oradata/syk','+ASM_DATA/syk/onlinelog','/oradata/syk','+ASM_RECOVER/syk/onlinelog'

syk1.fal_client='syk1'

syk2.fal_client='syk2'

*.fal_server='syk_single'

*.standby_file_management='auto'

*.STANDBY_ARCHIVE_DEST='/home/oracle/archive/'

注意:*.db_unique_name='SYK' 不要改变db_unique_name的值，在参数文件里加上它(show parameter db_unique_name看到的)，

但是值不要改变

create spfile='+ASM_RECOVER/syk/spfilesyk.ora' from pfile='/home/oracle/pfilesyk.ora';

也可以直接修改spfile，如下：

ALTER SYSTEM SET DB_UNIQUE_NAME=syk scope=spfile;

alter system set service_names='syk_rac'

alter system set log_archive_config='dg_config=(syk,syk_single)' scope=both;

alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE'

alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE'

alter system set log_archive_dest_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' DB_UNIQUE_NAME='syk'

alter system set log_archive_dest_2='service=syk_single valid_for=(online_logfiles,primary_role) db_unique_name=syk_single'

alter system set db_file_name_convert='/oradata/syk','+ASM_DATA/syk/datafile','/oradata/syk','+ASM_DATA/syk/tempfile'

alter system set log_file_name_convert='/oradata/syk','+ASM_DATA/syk/onlinelog','/oradata/syk','+ASM_RECOVER/syk/onlinelog'

alter system set standby_file_management=auto

alter system set fal_server='syk_single'

alter system set syk1.fal_client='syk1'

alter system set syk2.fal_client='syk2'

alter system set STANDBY_ARCHIVE_DEST='/home/oracle/archive/'

根据上面修改standby库的参数(在standby上操作)

single上最后参数如下：

syk.__db_cache_size=134217728

syk.__java_pool_size=4194304

syk.__large_pool_size=4194304

syk.__shared_pool_size=67108864

syk.__streams_pool_size=0

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

*.background_dump_dest='/u01/app/oracle/admin/syk/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/oradata/syk/control01.ctl','/oradata/syk/control02.ctl','/oradata/syk/control03.ctl'#Restore

Controlfile

*.core_dump_dest='/u01/app/oracle/admin/syk/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='+ASM_DATA/syk/datafile','/oradata/syk','+ASM_DATA/syk/tempfile','/oradata/syk'

*.db_name='syk'

*.db_unique_name='syk_single'

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

*.fal_client='syk_single'

*.fal_server='syk1','syk2'

*.job_queue_processes=10

*.log_archive_config='dg_config=(syk,syk_single)'

*.log_archive_dest_1='location=/home/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=syk_single'

*.log_archive_dest_2='service=syk1 LGWR valid_for=(online_logfiles,primary_role) db_unique_name=syk'

*.log_checkpoints_to_alert=TRUE

*.log_file_name_convert='+ASM_DATA/syk/onlinelog','/oradata/syk','+ASM_RECOVER/syk/onlinelog','/oradata/syk'

*.open_cursors=300

*.pga_aggregate_target=71303168

*.processes=300

*.remote_login_passwordfile='exclusive'

*.service_names='syk_single'

*.sessions=335

*.sga_target=216006656

*.standby_archive_dest='/home/oracle/archive'

*.standby_file_management='auto'

*.thread=1

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/syk/udump'

四、rac上相关准备操作(rac1上操作)

强制日志

SQL> alter database force logging;

备份

RMAN> backup database format '/home/oracle/dgbak/racdbfull%u_%s_%p';

生成standby上的控制文件

SQL> alter system archive log current;

SQL> alter database create standby controlfile as '/home/oracle/standby-controlfile.ora';

把所有文件(上面生成的控制文件，备份文件)拷到standby相应目录下

五、启动standby到nomount下(standby上做操作)

生成密码文件和参数文件

orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwpridb password=admin123

两个库的密码要相同，否则会有报错

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

六、创建standby库(rac1上操作)

rman target / auxiliary

duplicate target database for standby;

七、standby上做检查(在standby上做操作)

select open_mode from v$database

select name from v$datafile

select name from v$tempfile

select member from v$logfile

八、创建standby日志(rac上每thread有两组日志，那在这里要创建三组)(在standby上做操作)

alter database add standby logfile thread 1 group 5 ('/oradata/syk/st_1_5.rdo') size 50m;

alter database add standby logfile thread 1 group 6 ('/oradata/syk/st_1_6.rdo') size 50m;

alter database add standby logfile thread 1 group 7 ('/oradata/syk/st_1_7.rdo') size 50m;

alter database add standby logfile thread 2 group 8 ('/oradata/syk/st_2_8.rdo') size 50m;

alter database add standby logfile thread 2 group 9 ('/oradata/syk/st_2_9.rdo') size 50m;

alter database add standby logfile thread 2 group 10 ('/oradata/syk/st_2_10.rdo') size 50m;

确认日志

select member from v$logfile

九、开始应用，启动MRP(在standby上操作)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

取消日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

十、rac各个实例查看日志传送情况(每个实例上都检查)

SQL> select dest_name,status,error from v$archive_dest;

测试：

standby上做检查

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oradata/syk/system.260.765584315

/oradata/syk/undotbs1.262.765584301

/oradata/syk/sysaux.265.765584299

/oradata/syk/users.267.765584315

/oradata/syk/undotbs2.261.765584301

/oradata/syk/moe.264.765584315

/oradata/syk/sunsyk.269.766200437

7 rows selected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

rac1做检查

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+ASM_DATA/syk/datafile/system.260.765584315

+ASM_DATA/syk/datafile/undotbs1.262.765584301

+ASM_DATA/syk/datafile/sysaux.265.765584299

+ASM_DATA/syk/datafile/users.267.765584315

+ASM_DATA/syk/datafile/undotbs2.261.765584301

+ASM_DATA/syk/datafile/moe.264.765584315

+ASM_DATA/syk/datafile/sunsyk.269.766200437

7 rows selected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

standby上创建表空间

SQL> create tablespace test datafile '/oradata/syk/test01.dbf' size 1m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oradata/syk/system.260.765584315

/oradata/syk/undotbs1.262.765584301

/oradata/syk/sysaux.265.765584299

/oradata/syk/users.267.765584315

/oradata/syk/undotbs2.261.765584301

/oradata/syk/moe.264.765584315

/oradata/syk/sunsyk.269.766200437

/oradata/syk/test01.dbf

8 rows selected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

rac1上做检查，看是否同步过来

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

+ASM_DATA/syk/datafile/system.260.765584315

+ASM_DATA/syk/datafile/undotbs1.262.765584301

+ASM_DATA/syk/datafile/sysaux.265.765584299

+ASM_DATA/syk/datafile/users.267.765584315

+ASM_DATA/syk/datafile/undotbs2.261.765584301

+ASM_DATA/syk/datafile/moe.264.765584315

+ASM_DATA/syk/datafile/sunsyk.269.766200437

+ASM_DATA/syk/datafile/test.275.766246589

8 rows selected.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

switchover

rac上只能在一个实例上操作，所以关闭rac2

一、rac2

SQL> shutdown immediate

二、rac1转成standby

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown immediate

三、standby转成主库

SQL> alter database commit to switchover to primary with session shutdown;

SQL> shutdown immediate

SQL> startup

四、rac1添加standby日志

SQL> startup mount;

alter database add standby logfile thread 1 group 5 size 50m;

alter database add standby logfile thread 1 group 6 size 50m;

alter database add standby logfile thread 1 group 7 size 50m;

alter database add standby logfile thread 2 group 8 size 50m;

alter database add standby logfile thread 2 group 9 size 50m;

alter database add standby logfile thread 2 group 10 size 50m;

开始同步

SQL> alter database recover managed standby database disconnect from session;

测试同上。

转载于:https://blog.51cto.com/qhd2004/706211

你可能感兴趣的文章

Linux Oracle启动SqlPlus无法连接

查看>>

Android中自定义checkbox样式

当用户有登录到的限制时，远程桌面登录报错的解决

从标准输入读取几行输入。每行输入都要打印到标准输出上，前面加上行号。...

查看>>

part 1--入门：

查看>>

Android APK应用安装原理(1)-解析AndroidManifest原理-PackageParser.parserPackage

Windows server 2012安装.NET 3.5

查看>>

Spring 常用的注解及“依赖注入”的实现

查看>>