（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';
扫一扫
1086
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
