RAC TO RAC ADG

(1)开启归档和强制在日志

srvctl stop database -d orcl

srvctl start instance -d orcl -i orcl1 -o mount

alter database archivelog;

alter database force logging;

alter database open;

srvctl start instance -d orcl -i instance orcl2

 

(2) 创建Standby Redo日志(SLRs)

SQL> select max (bytes), count (1) from v$log;

52428800 4

SQL> select thread#,bytes/1024/1024 "SIZE(M)",members from v$log;

THREAD# SIZE(M) MEMBERS

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

1 50 1

1 50 1

2 50 1

2 50 1

 

可以看出目前有两个节点,每个节点有两个日志组,每个日志成员大小为50m,每个日志组里都有一个成员。下面给每个节点添加三个日志组:

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

alter database add standby logfile thread 1 group 5 ('+DATA/orcl/standbylog/standbylog01.log') size 50M;

alter database add standby logfile thread 1 group 6 ('+DATA/orcl/standbylog/standbylog02.log') size 50M;

alter database add standby logfile thread 1 group 7 ('+DATA/orcl/standbylog/standbylog03.log') size 50M;

alter database add standby logfile thread 2 group 8 ('+DATA/orcl/standbylog/standbylog04.log') size 50M;

alter database add standby logfile thread 2 group 9 ('+DATA/orcl/standbylog/standbylog05.log') size 50M;

alter database add standby logfile thread 2 group 10 ('+DATA/orcl/standbylog/standbylog06.log') size 50M;

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

 

SQL> select group#,thread#,status from v$standby_log;

GROUP# THREAD# STATUS

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

5 1 UNASSIGNED

6 1 UNASSIGNED

7 1 UNASSIGNED

8 2 UNASSIGNED

9 2 UNASSIGNED

10 2 UNASSIGNED

 

(3)修改参数

alter system set DB_UNIQUE_NAME=primary scope=spfile sid='*';

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)' scope=spfile sid='*';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=spfile sid='*';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile sid='*';

alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile sid='*';

alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile sid='*';

alter system set LOG_ARCHIVE_MAX_PROCESSES=10 scope=spfile sid='*';

alter system set FAL_SERVER=standby scope=spfile sid='*';

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile sid='*';

alter system set DB_FILE_NAME_CONVERT='+DATA','+DATA02' scope=spfile sid='*';

alter system setlog_file_name_convert='+ARCH02/standby','+DATA/orcl','+ARCH02/standby/standbylog','+ARCH/primary/standbylog' scope=spfile sid='*';

 

在节点1和节点2上设置local_listener,host里面填写的是vip地址

orcl2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=60.60.60.217)(PORT=1521))'

orcl1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=60.60.60.216)(PORT=1521))'

 

检查一下是不是修改过来

set linesize 500 pages 0

col value for a90

col name for a50

 

select name, value

from v$parameter

where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',  'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');

 

db_file_name_convert                                   +DATA02, +DATA

log_file_name_convert                                   +ARCH02/standby, +DATA/orcl, +ARCH02/standby/standbylog, +ARCH/primary/standbylog

log_archive_dest_1                                   LOCATION=+ARCH/PRIMARY/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary

 

log_archive_dest_2                                   SERVICE=standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby

log_archive_dest_state_1                           ENABLE

log_archive_dest_state_2                           ENABLE

fal_server                                           STANDBY

log_archive_config                                   DG_CONFIG=(primary,standby)

log_archive_format                                   %t_%s_%r.arc

log_archive_max_processes                           8

standby_file_management                            AUTO

remote_login_passwordfile                           EXCLUSIVE

db_name                                            orcl

db_unique_name                                      PRIMARY

 

主库的initorcl.ora参数文件

 

*.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/control01.ctl','+DATA/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

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

*.db_name='orcl'

*.db_unique_name='PRIMARY'

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

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

*.fal_server='STANDBY'

orcl2.instance_number=2

orcl1.instance_number=1

orcl2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=60.60.60.217)(PORT=1521))'

orcl1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=60.60.60.216)(PORT=1521))'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='LOCATION=+ARCH/PRIMARY/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

*.log_archive_dest_2='SERVICE=standby ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.log_archive_max_processes=8

*.log_file_name_convert='+ARCH02/standby','+DATA/orcl','+ARCH02/standby/standbylog','+ARCH/primary/standbylog'

*.memory_target=1606418432

*.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'

 

 

 

 

复制密码文件

scp $ORACLE_HOME/dbs/orapworcl1 dr-node1:$ORACLE_HOME/dbs/orapworcl1

scp $ORACLE_HOME/dbs/orapworcl1 dr-node2:$ORACLE_HOME/dbs/orapworcl2

 

 

 

在备库创建dump文件的路径

 

On standby1:

 

mkdir –p /u01/app/oracle/admin/standby/adump

mkdir –p /u01/app/oracle/diag/rdbms/standby/orcl1

cd /u01/app/oracle/diag/rdbms/standby/orcl1

mkdir trace cdump

[oracle@standby1 ~]$ chmod 775 /u01/app/oracle/admin/standby/adump

 

On standby2:

 

mkdir –p /u01/app/oracle/admin/standby/adump

mkdir –p /u01/app/oracle/diag/rdbms/standby/orcl2

cd /u01/app/oracle/diag/rdbms/standby/orcl2

mkdir trace cdump

[oracle@standby2 ~]$ chmod 775 /u01/app/oracle/admin/standby/adump

 

复制修改standby的参数文件

 

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

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+DATA02/standby/controlfile/control01.ctl'

*.db_block_size=8192

*.db_domain=''

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

*.db_name='orcl'

*.db_unique_name='standby'

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

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

*.fal_server='PRIMARY'

orcl2.instance_number=2

orcl1.instance_number=1

orcl1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=60.60.60.211)(PORT=1521))'

orcl2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=60.60.60.212)(PORT=1521))'

*.log_archive_config='DG_CONFIG=(primary,standby)'

*.log_archive_dest_1='LOCATION=+ARCH02 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.log_archive_dest_2='SERVICE=primary ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.log_archive_max_processes=8

*.log_file_name_convert='+DATA/orcl','+ARCH02/standby','+ARCH/primary/standbylog','+ARCH02/standby/standbylog'

*.memory_target=1606418432

*.open_cursors=300

*.processes=150

*.remote_listener='standby-scan:1521'

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

orcl2.thread=2

orcl1.thread=1

orcl2.undo_tablespace='UNDOTBS2'

orcl1.undo_tablespace='UNDOTBS1'

 

 

创建asm文件路径

ASMCMD> mkdir data02/standby

ASMCMD> cd data02/standby

ASMCMD> mkdir PARAMETERFILE DATAFILE CONTROLFILE TEMPFILE ONLINELOG

 

ASMCMD> mkdir arch02/standby

ASMCMD> cd arch02/standby

ASMCMD> mkdir ARCHIVELOG CONTROLFILE ONLINELOG STANDBYLOG

 

修改主库primary和备库standby的tnsname.ora

primary =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

    )

  )

 

standby =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

      (UR = A)

    )

  )

 

primary1 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

      (SID = orcl1)

    )

  )

standby1 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

      (SID = orcl1)

      (UR = A)

    )

  )

primary2 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

      (SID = orcl2)

    )

  )

standby2 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

      (SID = orcl2)

      (UR = A)

    )

  )

 

创建standby库的监听文件,grid用户下,在节点1和节点2均要创建,要重启监听

节点1

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

    (GLOBAL_DBNAME = standby)

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

    (SID_NAME = orcl1)

   )

 )

ADR_BASE_LISTENER = /u01/app/oracle

 

节点2

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

    (GLOBAL_DBNAME = standby)

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

    (SID_NAME = orcl2)

   )

 )

ADR_BASE_LISTENER = /u01/app/oracle

 

连接辅助数据库,开始复制,primary1和standby1配置的是连接一个节点的vip

rman target sys/oracle@primary1 auxiliary sys/oracle@standby1

Rman> duplicate target database for standby from active database;

 

备库开启复制:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

在主库切换日志

alter system switch logfile

 

在primary和standby两面,查看日志的同步的情况

select * from v$archive_gap;

select process, client_process, sequence#, status from v$managed_standby;

select sequence#, first_time, next_time, applied from v$archived_log;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select thread#, max (sequence#) from v$log_history group by thread#;

select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

 

 

 

在备库创建spfileorcl.orcl

create spfile='+DATA02/standby/parameterfile/spfileorcl.ora' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora';

 

修改initorcl1.ora,指定spfileorcl.ora的路径

On dr-node1:

[oracle@standby1 dbs]$ cat initorcl1.ora

SPFILE='+DATA02/standby/parameterfile/spfileorcl.ora'

 

On dr-node2:

[oracle@standby2 dbs]$ cat initorcl2.ora

SPFILE='+DATA02/standby/parameterfile/spfileorcl.ora'

 

注册OCR

srvctl add database -d standby -n orcl -o /u01/app/oracle/product/11.2.0/db_1  -p +DATA02/standby/parameterfile/spfileorcl.ora -r physical_standby -a DATA02,ARCH02

 

参考:有域名还要添加域名

Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-c {RACONENODE | RAC | SINGLE} [-e <server_list>] [-i <inst_name>] [-w <timeout>]] [-m <domain_name>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL | NORESTART}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [-j "<acfs_path_list>"]

 

srvctl add instance -d standby -i orcl1 -n standby1

srvctl add instance -d standby -i orcl2 -n standby2

srvctl config database -d standby

Database unique name: standby

Database name: orcl

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA02/standby/parameterfile/spfileorcl.ora

Domain: luocs.com

Start options: open

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools: standby

Database instances: orcl1,orcl2

Disk Groups: DATA02,ARCH02

Mount point paths:

Services:

Type: RAC

Database is administrator managed

 

 

srvctl stop database -d standby

srvctl start database -d standby

 

SQL>alter database recover managed standby database cancel;

 

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

 

SQL> select open_mode,database_role,name from gv$database;

 

OPEN_MODE             DATABASE_ROLE    NAME

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

READ ONLY WITH APPLY PHYSICAL STANDBY ORCL

READ ONLY WITH APPLY PHYSICAL STANDBY ORCL

 

 

 

 

修改保护模式

 

6.1 最高可用性MAXIMUM AVAILABILITY

查看当前保护模式

On Primary:

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> select dest_id,database_mode,recovery_mode,protection_mode from v$archive_dest_status where dest_id=2;

 

DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE

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

2 OPEN_READ-ONLY IDLE MAXIMUM PERFORMANCE

 

 

停止Redo Apply

On Standby Node1:

SQL> alter database recover managed standby database cancel;

修改参数:

On Primary Node1:

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=both sid='*';

重启Primary Database到mount模式:

[grid@rac1 ~]$ srvctl stop database -d orcl

[grid@rac1 ~]$ srvctl start database -d orcl -o mount

切换到最高可用性模式

SQL> alter database set standby database to maximize availability;

SQL> alter database open;

On Primary Node2:

SQL> alter database open;

 

查看结果:

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> select dest_id,database_mode,recovery_mode,protection_mode from v$archive_dest_status where dest_id=2;

 

DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE

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

2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM AVAILABILITY

 

On Standby:

启动Redo实时应用

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

 

Primary和Standby数据库切换

7.1 Switchover到Physical Standby Database

保证Primary SIDE只运行于一个节点,先关闭Node2 Instance:

[oracle@orcl1 ~]$ srvctl stop instance -d orcl -i orcl2

[oracle@orcl1 ~]$ srvctl status database -d orcl

Instance orcl1 is running on node orcl1

Instance orcl2 is not running on node orcl2

备注:如果Primary SIDE运行于双节点,在下面进行切换的时候会报错:

SQL> alter database commit to switchover to physical standby;

ERROR at line 1:

ORA-01105: mount is incompatible with mounts by other instances

 

On Primary Node1:

查看Primary Database是否满足切换条件:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

 

备注:有时候会显示SESSION ACTIVE,这表示当前还有活动的会话连接。

执行下面命令进行切换:

SQL> alter database commit to switchover to physical standby;

备注:如果上面切换条件查看显示SESSION ACTIVE,那么在这里使用下面命令

SQL> alter database commit to switchover to physical standby WITH SESSION SHUTDOWN;

 

 

关闭数据库重启到mount

[oracle@orcl1 ~]$ srvctl stop instance -d orcl -i orcl1

[oracle@orcl1 ~]$ srvctl start database -d orcl -o mount

用Read Only模式打开

On Primary Node1:

SQL> alter database open read only;

 

On Primary Node2:

SQL> alter database open read only;

 

 

启动Redo应用模式

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

切换后查看角色

SQL> select database_role,switchover_status from v$database;

 

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

 

On Standby Node1:

查看Standby Database是否满足切换条件

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY

执行下面命令进行切换:

SQL> alter database commit to switchover to primary;

打开数据库:

On Current On Primary Node1:

SQL> alter database open;

Current On Primary Node2:

SQL> alter database open;

 

查看数据保护模式

On Current Primary Node1:

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

 

SQL> select dest_id,database_mode,recovery_mode,protection_mode from v$archive_dest_status where dest_id=2;

DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE

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

2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM AVAILABILITY

 

查看有没有确实的Redo日志

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

如果有,通过下面命令去手动注册

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值