-
一、rac主库配置
-
1.1、db_name、db_unique_name、instance_name、service_name
-
db_name：数据库名，多节点的数据库名相同，实例名不相同
-
db_unique_name：HA的应用，它不同于db_name，dg中要求主从库db_name相同；db_unique_name直接决定了service_name，即服务名
-
创建数据库时会指定全局数据库名和sid，sid直接决定了instance_name；监听中global_name和db_name可以不同
-
-
rac主库：db_name=amondbs，db_unique_name=amondbs_primary，ORACLE_SID=amondbs1,amondbs2，service=amondbs_pri_ser
-
单实例备库：db_name=amondbs，db_unique_name=amondbs_standby，ORACLE_SID=amondbs_phydb，service=amondbs_sta_ser
-
-
rac的数据库名相同，但是实例名不同，实例名（instance_name）来源于ORACLE_SID；默认本地服务名也称为动态注册监听默认服务名（service_names，也可以通过lsnrctl status查看）来源于db_unique_name、db_name；配置连接字符串时connect_data配置的service_name，service_name其实就是service_names的值
-
-
（0）修改db_unique_name和ORACLE_SID后，会自动创建$ORACLE_BASE/diag/rdbms/db_unique_name/ORACLE_SID/trace
-
（1）密码文件是以实例名为标识，即ORACLE_SID，与db_unique_name无关
-
（2）执行backup archivelog all将当前重做日志切换并归档，然后再做备份归档
-
（3）静态注册中的sid_name：数据库运行的实例名，必须和instance_name相同；global_dbname：可以是任意值，配置tns时service_name跟它相同
-
（4）一个instance_name可以对应多个service_name
-
（5）oracle用户安装oracle数据库，指定Global Database Name不会自动填充，它的值默认就是db_name、db_unique_name的值
-
-
1.2、rac主库必须置为归档模式
-
root@node1.localdomain[+ASM1]/root$crsctl start crs
-
oracle@node1.localdomain[amondbs1]/home/oracle$sqlplus / as sysdba
-
SQL> select name,open_mode,log_mode,force_logging from gv$database;
-
-
NAME OPEN_MODE LOG_MODE FOR
-
--------- -------------------- ------------ ---
-
AMONDBS READ WRITE ARCHIVELOG YES
-
AMONDBS READ WRITE ARCHIVELOG YES
-
-
alter database archivelog;
-
alter database force logging;
-
-
1.3、rac主库必须置为force logging模式
-
SQL> select inst_id,name,log_mode,force_logging from gv$database;
-
-
INST_ID NAME LOG_MODE FOR
-
---------- --------- ------------ ---
-
1 AMONDBS ARCHIVELOG YES
-
2 AMONDBS ARCHIVELOG YES
-
-
SQL> alter database force logging;
-
-
1.4、rac主库执行rman全备尤其是控制文件
-
oracle@node1.localdomain[amondbs1]/oracle$mkdir /oracle/rman_backup
-
RMAN>
-
run
-
{delete noprompt obsolete;
-
allocate channel c1 type disk MAXPIECESIZE 1G;
-
allocate channel c2 type disk MAXPIECESIZE 1G;
-
backup AS COMPRESSED BACKUPSET filesperset 2 format '/oracle/rman_backup/db_full_%T_%d_%t_%s_%p' database;
-
backup AS COMPRESSED BACKUPSET format '/oracle/rman_backup/arch_full_%T_%d_%t_%s_%p' archivelog all delete input;
-
backup current controlfile for standby format '/oracle/rman_backup/ctl_standby_%T_%d_%t_%s_%p';
-
release channel c1;
-
release channel c2;
-
}
-
-
1.5、rac主库创建从库的初始化参数文件到全备目录init+sid
-
SQL> show parameter pfile
-
SQL> create pfile='/oracle/rman_backup/initamondbs_phydb.ora' from spfile='+DATADG/amondbs/spfileamondbs.ora'; ##spfile应该指定磁盘组的spfile初始化参数文件
-
-
1.6、rac主库修改双节点共享的初始化参数文件，spfile是共享的，每个节点的pfile有指向spfile的路径
-
SQL> select name from v$datafile;
-
-
NAME
-
--------------------------------------------------------------------------------
-
+DATADG/system01.dbf
-
+DATADG/sysaux01.dbf
-
+DATADG/undotbs101.dbf
-
+DATADG/undotbs201.dbf
-
+DATADG/users.dbf
-
+DATADG/testtbs01
-
-
SQL> shutdown immediate;
-
-
##通过共享的spfile产生pfile，修改rac所有节点的参数，dg配置说明了*/oradata/db_unique_name创建目录
-
*.db_name='amondbs'
-
*.db_unique_name='amondbs_primary'
-
*.log_archive_config='dg_config=(amondbs_primary,amondbs_standby)'
-
*.log_archive_dest_2='service=amondbs_sta_ser lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=amondbs_standby'
-
*.log_archive_dest_state_1=enable
-
*.log_archive_dest_state_2=defer
-
*.remote_login_passwordfile='EXCLUSIVE'
-
*.db_file_name_convert='/home/oracle/oracle/oradata/amondbs_standby','+DATADG/amondbs/datafile' ##standby库oracle用户创建$ORACLE_BASE/oradata
-
*.log_file_name_convert='/home/oracle/oracle/oradata/amondbs_standby','+DATADG/amondbs'
-
*.fal_server=amondbs_standby
-
*.standby_file_managent=auto
-
还有角色切换后新角色的配置
-
-
##rac单个节点生成spfile即可
-
##关闭双节点数据库shutdown，启动后竟然使用修改的单节点pfile启动数据库，因为create pfile改变了SPFILE=，所以必须create spfile创建双节点共享的初始化参数文件、修改pfile
-
SQL> create spfile='+DATADG/amondbs/spfileamondbs.ora' from pfile='/oracle/rman_backup/initamondbs_phydb.ora';
-
oracle@node1.localdomain[amondbs1]/oracle/db11g_11.2.0.4/dbs$vi initamondbs1.ora
-
SPFILE='+DATADG/amondbs/spfileamondbs.ora'
-
SQL> startup
-
SQL> alter system set log_archive_dest_state_2=defer sid='*' scope=both;
-
-
##此时服务名不再是默认的服务名，@amondbs_primary时报错，如果配置tnsnames，service_name根据db_unique_name
-
oracle@node1.localdomain[amondbs1]/oracle/db11g_11.2.0.4/dbs$lsnrctl status
-
Service "+ASM" has 1 instance(s).
-
Instance "+ASM1", status READY, has 1 handler(s) for this service...
-
Service "amondbs_primary" has 1 instance(s).
-
Instance "amondbs1", status READY, has 1 handler(s) for this service...
-
-
root@node1.localdomain[+ASM1]/root$sqlplus sys/a@amondbs_primary as sysdba
-
-
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 15 10:58:25 2014
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
ERROR:
-
ORA-12154: TNS:could not resolve the connect identifier specified
-
-
-
注意：
-
（1）rac主库的*.db_unique_name='amondbs_primary'最好不要设置，保持默认避免scan listener起不来，因为tnsnames.ora中配置的服务名是amondbs；db_unique_name和tns服务名必须保持相同
-
（2）SQL> alter system set log_archive_dest_2='service=amondbs_sta_ser lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=amondbs_standby' sid='*' scope=both;
-
-
1.6、rac主库修改口令文件，使双节点sys用户口令相同，dg从库的sys口令与双节点sys口令相同
-
sqlplus sys/123456@192.168.0.31:1521/amondbs_primary as sysdba
-
-
1.7、rac主库配置tnsname.ora资料库，即配置amondbs_pri_ser、amondbs_sta_ser服务名，需要改双节点
-
##rac主库配置tnsname.ora资料库后，记得使用tnsping测试网络是否可用
-
oracle@node1.localdomain[amondbs1]/home/oracle$vi $ORACLE_HOME/network/admin/tnsnames.ora
-
# tnsnames.ora Network Configuration File: /oracle/db11g_11.2.0.4/network/admin/tnsnames.ora
-
# Generated by Oracle configuration tools.
-
amondbs_pri_ser =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(LOAD_BALANCE = on)
-
(FAILOVER = on)
-
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.4)(PORT=1521))
-
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.6)(PORT=1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = amondbs_primary)
-
(FAILOVER_MODE = (METHOD = basic)(TYPE = select)(DELAY = 5)(RETRIES = 3))
-
)
-
)
-
-
amondbs_sta_ser =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.11)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVER = dedicated)
-
(SERVICE_NAME = amondbs_standby)
-
)
-
)
-
-
###测试一下
-
sqlplus system/123456@amondbs_pri_ser
-
-
1.8、为主库添加standby redo，以便主库切换为从库后使用；跟从库创建的standby redo 线程、日志组相同；路径取log_file_name_convert
-
alter database add standby logfile thread 1 group 5 ('+DATADG/amondbs/standbyredo01.log') size 50m,
-
group 6 ('+DATADG/amondbs/standbyredo02.log') size 50m,
-
group 7 ('+DATADG/amondbs/standbyredo03.log') size 50m;
-
alter database add standby logfile thread 2 group 8 ('+DATADG/amondbs/standbyredo04.log') size 50m,
-
group 9 ('+DATADG/amondbs/standbyredo05.log') size 50m,
-
group 10 ('+DATADG/amondbs/standbyredo06.log') size 50m;
-
-
SQL> select * from v$logfile;
-
-
GROUP# STATUS TYPE MEMBER IS_
-
---------- ------- ------- -------------------------------------------------- ---
-
1 ONLINE +DATADG/amondbs/redo01.log NO
-
2 ONLINE +DATADG/amondbs/redo02.log NO
-
3 ONLINE +DATADG/amondbs/redo03.log NO
-
4 ONLINE +DATADG/amondbs/redo04.log NO
-
5 STANDBY +DATADG/amondbs/standbyredo01.log NO
-
6 STANDBY +DATADG/amondbs/standbyredo02.log NO
-
7 STANDBY +DATADG/amondbs/standbyredo03.log NO
-
8 STANDBY +DATADG/amondbs/standbyredo04.log NO
-
9 STANDBY +DATADG/amondbs/standbyredo05.log NO
-
10 STANDBY +DATADG/amondbs/standbyredo06.log NO
-
-
-
-
-
注意：
-
（1）如果指定了db_unique_name，创建$ORACLE_BASE/diag/rdbms/db_unique_name目录/sid；$ORACLE_BASE/diag/rdbms/amondbs_standby/amondbs_phydb
-
-
-
二、standby从库配置
-
注意：主库和从库的SID都是amondbs，与db_unique_name不同
-
-
1.1、oracle用户创建目录
-
$ mkdir -p $ORACLE_BASE/oradata/amondbs_standby ##db_unique_name
-
$ mkdir -p $ORACLE_BASE/fast_recovery_area
-
$ mkdir /home/oracle/oracle/admin/amondbs_standby/adump
-
$ mkdir /home/oracle/oracle/archivelog
-
-
1.2、将rac主库的备份拷贝到从库
-
-
1.3、从库修改初始化参数文件->启动oracle
-
在$ORACLE_HOME/dbs创建一个临时pfile，然后create spfile会自动生成带有sid的spfile
-
*.audit_file_dest='/home/oracle/oracle/admin/amondbs_standby/adump'
-
*.audit_trail='DB'
-
*.cluster_database=false
-
*.compatible='11.2.0.4.0'
-
*.control_files='/home/oracle/oracle/oradata/amondbs_standby/control01.ctl','/home/oracle/oracle/oradata/amondbs_standby/control02.ctl'
-
*.db_block_size=8192
-
*.db_domain=''
-
*.db_recovery_file_dest='/home/oracle/oracle/fast_recovery_area'
-
*.db_recovery_file_dest_size=6005194752
-
*.diagnostic_dest='/home/oracle/oracle/' ##oracle基础目录
-
*.event=''
-
*.log_archive_format='%t_%s_%r.dbf'
-
*.memory_target=400000000
-
*.open_cursors=300
-
*.processes=150
-
*.undo_management='auto'
-
*.undo_tablespace='undotbs1'
-
*.db_name='amondbs'
-
*.db_unique_name='amondbs_standby'
-
*.log_archive_config='dg_config=(amondbs_primary,amondbs_standby)'
-
*.log_archive_dest_1='LOCATION=/home/oracle/oracle/archivelog'
-
*.log_archive_dest_2='service=amondbs_pri_ser lgwr async affirm valid_for=(online_logfiles,primary_role) db_unique_name=amondbs_primary'
-
*.log_archive_dest_state_1=enable
-
*.log_archive_dest_state_2=enable
-
*.remote_login_passwordfile='EXCLUSIVE'
-
*.db_file_name_convert='+DATADG/amondbs/datafile','/home/oracle/oracle/oradata/amondbs_standby'
-
*.log_file_name_convert='+DATADG/amondbs','/home/oracle/oracle/oradata/amondbs_standby'
-
*.fal_server=amondbs_primary
-
*.standby_file_managent=auto
-
-
SQL> create spfile from pfile
-
1.4、创建监听
-
$ lsnrctl status ##没有监听
-
动态监听
-
$ lsnrctl status
-
-
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-JAN-2016 11:10:58
-
-
Copyright (c) 1991, 2013, Oracle. All rights reserved.
-
-
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
-
STATUS of the LISTENER
-
------------------------
-
Alias LISTENER
-
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
-
Start Date 15-JAN-2016 11:10:15
-
Uptime 0 days 0 hr. 0 min. 42 sec
-
Trace Level off
-
Security ON: Local OS Authentication
-
SNMP OFF
-
Listener Log File /home/oracle/oracle/diag/tnslsnr/dataguard/listener/alert/log.xml
-
Listening Endpoints Summary...
-
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dataguard)(PORT=1521)))
-
Services Summary...
-
Service "amondbs_standby" has 1 instance(s).
-
Instance "amondbs", status READY, has 1 handler(s) for this service...
-
The command completed successfully
-
-
本机tnsping成功，其他机器tnsping报错：TNS-12543: TNS:destination host unreachable，本机的防火墙在开着
-
-
配置listener.ora
-
LISTENER =
-
(DESCRIPTION_LIST =
-
(DESCRIPTION =
-
##(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.51)(PORT = 1521))
-
)
-
)
-
-
ADR_BASE_LISTENER = /home/oracle/oracle
-
-
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
-
#####仍然存在动态监听，可以远程使用sys登录
-
$ lsnrctl status
-
-
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-JAN-2016 13:09:44
-
-
Copyright (c) 1991, 2013, Oracle. All rights reserved.
-
-
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.51)(PORT=1521)))
-
STATUS of the LISTENER
-
------------------------
-
Alias LISTENER
-
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
-
Start Date 15-JAN-2016 13:07:56
-
Uptime 0 days 0 hr. 1 min. 48 sec
-
Trace Level off
-
Security ON: Local OS Authentication
-
SNMP OFF
-
Listener Parameter File /home/oracle/oracle/product/11g/network/admin/listener.ora
-
Listener Log File /home/oracle/oracle/diag/tnslsnr/dataguard/listener/alert/log.xml
-
Listening Endpoints Summary...
-
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.51)(PORT=1521)))
-
Services Summary...
-
Service "amondbs_standby" has 1 instance(s).
-
Instance "amondbs", status READY, has 1 handler(s) for this service...
-
The command completed successfully
-
-
配置listener.ora，加SID_LIST，动态监听和静态监听同时存在
-
-
$ lsnrctl start
-
#######使用tnsping进行测试
-
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
-
amondbs_pri_ser =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(LOAD_BALANCE = on)
-
(FAILOVER = on)
-
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.31)(PORT=1521))
-
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.41)(PORT=1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = amondbs_primary)
-
(FAILOVER_MODE = (METHOD = basic)(TYPE = select)(DELAY = 5)(RETRIES = 3))
-
)
-
)
-
-
amondbs_sta_ser =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.51)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVER = dedicated)
-
(SERVICE_NAME = amondbs_standby)
-
)
-
)
-
-
1.5、备库创建密码文件和主库相同
-
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=123456 entries=10 force=yes
-
-
最好把主库的密码文件拷贝到从库，重命名为orapw<sid>
-
sqlplus sys/123456@amondbs_sta_ser as sysdba从主库可以连接，但是system不能连接，原因：sys使用控制文件认证，普通用户使用数据文件认证，这不是使用
-
静态注册的一个原因
-
-
1.6、从库启动到nomount，sqlplus后根据ORACLE_SID找对应pfile，然后启动，根据ORACLE_SID切换到不同的实例
-
注意将主库rman的备份拷贝到从库的同一目录，不然备份是过期的，执行set newname报错ORA-01180: can not create datafile 1
-
$sqlplus / as sysdba
-
SQL> startup nomount
-
-
rman target /
-
RMAN> restore standby controlfile from '/oracle/rman_backup/ctl_standby_20160115_AMONDBS_901207070_50_1';
-
output file name=/home/oracle/oracle/oradata/amondbs_standby/control01.ctl
-
output file name=/home/oracle/oracle/oradata/amondbs_standby/control02.ctl
-
RMAN> alter database mount;
-
RMAN> list backup;
-
List of Backup Sets
-
===================
-
BS Key Type LV Size Device Type Elapsed Time Completion Time
-
------- ---- -- ---------- ----------- ------------ ---------------
-
42 Full 15.28M DISK 00:00:14 15-JAN-16
-
BP Key: 42 Status: AVAILABLE Compressed: YES Tag: TAG20160115T151616
-
Piece Name: /oracle/rman_backup/db_full_20160115_AMONDBS_901206977_43_1
-
-
RMAN> restore database;
-
-
Starting restore at 15-JAN-16
-
Starting implicit crosscheck backup at 15-JAN-16
-
allocated channel: ORA_DISK_1
-
channel ORA_DISK_1: SID=19 device type=DISK
-
Crosschecked 8 objects
-
Finished implicit crosscheck backup at 15-JAN-16
-
-
Starting implicit crosscheck copy at 15-JAN-16
-
using channel ORA_DISK_1
-
Finished implicit crosscheck copy at 15-JAN-16
-
-
searching for all files in the recovery area
-
cataloging files...
-
no files cataloged
-
-
using channel ORA_DISK_1
-
-
channel ORA_DISK_1: starting datafile backup set restore
-
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
-
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oracle/oradata/amondbs_standby/sysaux.261.900422621
-
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/oracle/oradata/amondbs_standby/users.265.900422741
-
channel ORA_DISK_1: reading from backup piece /oracle/rman_backup/db_full_20160115_AMONDBS_901206977_43_1
-
channel ORA_DISK_1: piece handle=/oracle/rman_backup/db_full_20160115_AMONDBS_901206977_43_1 tag=TAG20160115T151616
-
channel ORA_DISK_1: restored backup piece 1
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
-
channel ORA_DISK_1: starting datafile backup set restore
-
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
-
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oracle/oradata/amondbs_standby/undotbs1.262.900422677
-
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oracle/oradata/amondbs_standby/undotbs2.264.900422721
-
channel ORA_DISK_1: reading from backup piece /oracle/rman_backup/db_full_20160115_AMONDBS_901206994_44_1
-
channel ORA_DISK_1: piece handle=/oracle/rman_backup/db_full_20160115_AMONDBS_901206994_44_1 tag=TAG20160115T151616
-
channel ORA_DISK_1: restored backup piece 1
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
-
channel ORA_DISK_1: starting datafile backup set restore
-
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
-
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/oracle/oradata/amondbs_standby/system.260.900422559
-
channel ORA_DISK_1: reading from backup piece /oracle/rman_backup/db_full_20160115_AMONDBS_901206977_42_1
-
channel ORA_DISK_1: piece handle=/oracle/rman_backup/db_full_20160115_AMONDBS_901206977_42_1 tag=TAG20160115T151616
-
channel ORA_DISK_1: restored backup piece 1
-
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
-
-
1.9、接受日志信息，这里没有创建standby logfile，所以接受的是归档日志
-
SQL> ALTER SYSTEM SET log_archive_dest_state_2=enable SCOPE=BOTH SID='*';
-
此时主节点数据库alert日志如下：
-
******************************************************************
-
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
-
******************************************************************
-
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='*';
-
Fri Jan 15 16:43:10 2016
-
Archived Log entry 65 added for thread 1 sequence 42 ID 0x235d93c2 dest 1:
-
-
此时从库数据库alert日志如下：
-
Fri Jan 15 16:42:53 2016
-
Using STANDBY_ARCHIVE_DEST parameter default value as /home/oracle/oracle/archivelog
-
Fri Jan 15 16:42:55 2016
-
Primary database is in MAXIMUM PERFORMANCE mode
-
RFS[1]: Assigned to RFS process 4966
-
RFS[1]: No standby redo logfiles created
-
RFS[1]: Opened log for thread 1 sequence 43 dbid 593350850 branch 900422530
-
Fri Jan 15 16:42:57 2016
-
RFS[2]: Assigned to RFS process 4970
-
RFS[2]: Opened log for thread 2 sequence 24 dbid 593350850 branch 900422530
-
Fri Jan 15 16:42:58 2016
-
RFS[3]: Assigned to RFS process 4972
-
RFS[3]: Opened log for thread 2 sequence 22 dbid 593350850 branch 900422530
-
Fri Jan 15 16:42:59 2016
-
RFS[4]: Assigned to RFS process 4968
-
RFS[4]: Opened log for thread 2 sequence 23 dbid 593350850 branch 900422530
-
Archived Log entry 1 added for thread 2 sequence 24 rlc 900422530 ID 0x235d93c2 dest 2:
-
Archived Log entry 2 added for thread 2 sequence 22 rlc 900422530 ID 0x235d93c2 dest 2:
-
Archived Log entry 3 added for thread 2 sequence 23 rlc 900422530 ID 0x235d93c2 dest 2:
-
Fri Jan 15 16:43:00 2016
-
RFS[5]: Assigned to RFS process 4974
-
RFS[5]: Opened log for thread 1 sequence 41 dbid 593350850 branch 900422530
-
Archived Log entry 4 added for thread 1 sequence 41 rlc 900422530 ID 0x235d93c2 dest 2:
-
Fri Jan 15 16:43:08 2016
-
Primary database is in MAXIMUM PERFORMANCE mode
-
RFS[6]: Assigned to RFS process 4978
-
RFS[6]: No standby redo logfiles created
-
RFS[6]: Opened log for thread 2 sequence 26 dbid 593350850 branch 900422530
-
Fri Jan 15 16:43:11 2016
-
RFS[7]: Assigned to RFS process 4982
-
RFS[7]: Opened log for thread 1 sequence 42 dbid 593350850 branch 900422530
-
Fri Jan 15 16:43:11 2016
-
RFS[8]: Assigned to RFS process 4984
-
RFS[8]: Opened log for thread 2 sequence 25 dbid 593350850 branch 900422530
-
Archived Log entry 5 added for thread 1 sequence 42 rlc 900422530 ID 0x235d93c2 dest 2:
-
Archived Log entry 6 added for thread 2 sequence 25 rlc 900422530 ID 0x235d93c2 dest 2:
-
-
-
1.9、启用redo应用
-
SQL> alter database recover managed standby database disconnect from session;
-
-
#####正常信息：如果删除归档目录的日志，执行redo应用时，FAL会从主库fetch日志
-
##Media Recovery Log /home/oracle/oracle/archivelog/2_22_900422530.dbf
-
##Error opening /home/oracle/oracle/archivelog/2_22_900422530.dbf
-
##Attempting refetch
-
##Media Recovery Waiting for thread 2 sequence 22
-
##Fetching gap sequence in thread 2, gap sequence 22-22
-
##FS[15]: Allowing overwrite of partial archivelog for thread 2 sequence 22
-
-
SQL> alter database recover managed standby database cancel;
-
SQL> alter database open; ##可以打开数据库，之前打不开，ORA-10458: standby database requires recovery
-
SQL> select open_mode,log_mode from v$database; ###控制文件是standby
-
-
OPEN_MODE LOG_MODE
-
-------------------- ------------
-
READ ONLY ARCHIVELOG
-
-
1.10、打开日志应用后，从库MRP启动，创建了online redo
-
#MRP0 started with pid=30, OS id=5258
-
#MRP0: Background Managed Standby Recovery process started (amondbs)
-
#Serial Media Recovery started
-
#Managed Standby Recovery not using Real Time Apply
-
#Waiting for all non-current ORLs to be archived...
-
#All non-current ORLs have been archived.
-
#Errors in file /home/oracle/oracle/diag/rdbms/amondbs_standby/amondbs/trace/amondbs_mrp0_5258.trc:
-
#ORA-00313: open failed for members of log group 1 of thread 1
-
#ORA-00312: online log 1 thread 1: '/home/oracle/oracle/oradata/amondbs_standby/redo01.log'
-
#ORA-27037: unable to obtain file status
-
#Linux-x86_64 Error: 2: No such file or directory
-
#Additional information: 3
-
#Clearing online redo logfile 1 /home/oracle/oracle/oradata/amondbs_standby/redo01.log
-
#Clearing online log 1 of thread 1 sequence number 45
-
-
SQL> select group#,members,thread#,sequence#,bytes/1024/1024,status,archived from v$log;
-
-
GROUP# MEMBERS THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- ---------- --------------- ---------------- ---
-
1 1 1 45 50 CLEARING YES
-
2 1 1 46 50 CURRENT YES
-
3 1 2 29 50 CURRENT YES
-
4 1 2 28 50 CLEARING YES
-
-
1.11、创建standby日志，创建的大小要和主库online redo保持一致
-
创建的原则：
-
① Standby Redo Log 的文件大小与主库 online redo log 文件大小相同
-
② Standby Redo Log 日志文件组的个数依照下面的原则进行计算：
-
Standby redo log组数公式 >= (每个instance日志组个数+1)*instance个数
-
例如在我的环境中，只有一个节点，这个节点有三组redo，所以Standby redo log组数公式>=(3+1)*1 == 4所以需要创建4组Standby redo log
-
③ 每一日志组为了安全起见，可以做多路镜像
-
-
从库创建standby redo日志--------
-
alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m; ##默认创建到db_recovery_file_dest
-
alter database drop standby logfile group 5,group 6,group 7;
-
-
SQL> alter database add standby logfile thread 1 group 5 ('/home/oracle/oracle/oradata/amondbs_standby/standbyredo01.log') size 50m,group 6 ('/home/oracle/oracle/oradata/amondbs_standby/standbyredo02.log') size 50m,group 7 ('/home/oracle/oracle/oradata/amondbs_standby/standbyredo03.log') size 50m;
-
SQL> alter database add standby logfile thread 2 group 8 ('/home/oracle/oracle/oradata/amondbs_standby/standbyredo04.log') size 50m,group 9 ('/home/oracle/oracle/oradata/amondbs_standby/standbyredo05.log') size 50m,group 10 ('/home/oracle/oracle/oradata/amondbs_standby/standbyredo06.log') size 50m;
-
-
SQL> select group#,thread#,sequence#,bytes/1024/1024,status,archived from v$standby_log;
-
-
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- --------------- ---------- ---
-
5 1 0 50 UNASSIGNED YES
-
6 1 0 50 UNASSIGNED YES
-
7 1 0 50 UNASSIGNED YES
-
8 2 0 50 UNASSIGNED YES
-
9 2 0 50 UNASSIGNED YES
-
10 2 0 50 UNASSIGNED YES
-
-
主库，主库的日志信息-------------
-
SQL> select group#,members,thread#,sequence#,bytes/1024/1024,status,archived from v$log;
-
-
SQL> select group#,members,thread#,sequence#,bytes/1024/1024,status,archived from v$log;
-
-
GROUP# MEMBERS THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- ---------- --------------- ---------------- ---
-
1 1 1 45 50 INACTIVE YES
-
2 1 1 46 50 CURRENT NO
-
3 1 2 29 50 CURRENT NO
-
4 1 2 28 50 INACTIVE YES
-
-
SQL> alter system archive log current;
-
SQL> select group#,members,thread#,sequence#,bytes/1024/1024,status,archived from v$log;
-
-
GROUP# MEMBERS THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- ---------- --------------- ---------------- ---
-
1 1 1 47 50 CURRENT NO
-
2 1 1 46 50 ACTIVE YES
-
3 1 2 29 50 ACTIVE YES
-
4 1 2 30 50 CURRENT NO
-
从库####
-
SQL> select group#,thread#,sequence#,bytes/1024/1024,status,archived from v$standby_log;
-
-
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- --------------- ---------- ---
-
5 1 47 50 ACTIVE YES
-
6 1 0 50 UNASSIGNED YES
-
7 1 0 50 UNASSIGNED YES
-
8 2 30 50 ACTIVE YES
-
9 2 0 50 UNASSIGNED YES
-
10 2 0 50 UNASSIGNED YES
-
-
SQL> alter system archive log current;
-
SQL> select group#,members,thread#,sequence#,bytes/1024/1024,status,archived from v$log;
-
-
GROUP# MEMBERS THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- ---------- --------------- ---------------- ---
-
1 1 1 47 50 ACTIVE YES
-
2 1 1 48 50 CURRENT NO
-
3 1 2 31 50 CURRENT NO
-
4 1 2 30 50 ACTIVE YES
-
-
从库####
-
SQL> select group#,thread#,sequence#,bytes/1024/1024,status,archived from v$standby_log;
-
-
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 STATUS ARC
-
---------- ---------- ---------- --------------- ---------- ---
-
5 1 48 50 ACTIVE YES
-
6 1 0 50 UNASSIGNED YES
-
7 1 0 50 UNASSIGNED YES
-
8 2 31 50 ACTIVE YES
-
9 2 0 50 UNASSIGNED YES
-
10 2 0 50 UNASSIGNED YES
-
总结：主库的current online redo和standby库的active状态的standby redo 日志序列号相同，证明redo传输服务启用了实时传输。
-
-
1.12、备库实时应用redo
-
11gR2开始，物理standby数据库在open状态也能应用日志，称为active dataguard
-
SQL> alter database recover managed standby database using current logfile disconnect from session; ##启动MRPn进程，进行real time apply，通过读取standby redo 进行恢复
-
-
SQL> select dbid,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database;
-
-
DBID SWITCHOVER_STATUS DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CURRENT_SCN
-
---------- -------------------- ------------------------------ ---------------- -------------------- -----------
-
593350850 NOT ALLOWED amondbs_standby PHYSICAL STANDBY READ ONLY WITH APPLY 637067
-
-
1.13、创建表空间测试文件同步
-
主库操作创建表空间，从库验证-----
-
set line 200
-
col FILE_NAME for a50
-
select tablespace_name,file_name,bytes/1024/1024 as total_space from dba_data_files;
-
-
TABLESPACE_NAME FILE_NAME TOTAL_SPACE
-
------------------------------ -------------------------------------------------- -----------
-
SYSTEM +DATADG/amondbs/datafile/system.260.900422559 700
-
SYSAUX +DATADG/amondbs/datafile/sysaux.261.900422621 600
-
UNDOTBS1 +DATADG/amondbs/datafile/undotbs1.262.900422677 200
-
UNDOTBS2 +DATADG/amondbs/datafile/undotbs2.264.900422721 200
-
USERS +DATADG/amondbs/datafile/users.265.900422741 5
-
-
create tablespace cuishoutest
-
datafile '+DATADG/amondbs/datafile/cuishoutest01.dbf' size 100m
-
autoextend on next 1m maxsize unlimited
-
logging
-
extent management local autoallocate
-
blocksize 8k
-
segment space management auto
-
flashback on;
-
-
主库操作删除表空间，从库验证----
-
drop tablespace cuishoutest including contents and datafiles;
-
-
1.14、测试dataguard switchover功能
-
####检查从库是否归档模式、force logging模式----
-
select name,open_mode,log_mode,force_logging from gv$database;
-
-
NAME OPEN_MODE LOG_MODE FOR
-
---------------------------------------------------------------------------------------------------- -------------------- ------------ ---
-
AMONDBS READ ONLY WITH APPLY ARCHIVELOG YES
-
-
-
####关闭日志传输
-
SQL> alter system set log_archive_dest_state_2=defer scope=both;
-
-
（1）停止rac节点2
-
SQL> select thread#,inst_id,instance_name,status from gv$instance;
-
-
THREAD# INST_ID INSTANCE_NAME STATUS
-
---------- ---------- ---------------- ------------
-
2 2 amondbs2 OPEN
-
1 1 amondbs1 OPEN
-
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 2 SESSIONS ACTIVE amondbs_primary MAXIMUM PERFORMANCE PRIMARY YES READ WRITE 649547
-
593350850 1 SESSIONS ACTIVE amondbs_primary MAXIMUM PERFORMANCE PRIMARY YES READ WRITE 649547
-
-
（2）rac节点1切换为从库
-
SQL> show parameter instance_name
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 1 TO STANDBY amondbs_primary MAXIMUM PERFORMANCE PRIMARY YES READ WRITE 650442
-
-
###主库切为从库，同时关闭实例，需要启动，open后数据库处于read only
-
SQL> alter database commit to switchover to physical standby with session shutdown;
-
-
SQL> startup
-
Physical standby database opened for read only access.
-
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 1 TO PRIMARY amondbs_primary MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY 670661
-
-
（3）从库切换为主库，此时单节点主库只使用线程1的日志组
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 1 TO PRIMARY amondbs_standby MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY 670661
-
-
-
SQL> alter database commit to switchover to primary;
-
######数据库和切换后的主库不一致，打开日志应用
-
*
-
ERROR at line 1:
-
ORA-16139: media recovery required
-
Database not available for switchover
-
Incomplete recovery SCN:0:670662 archive SCN:0:690868
-
Database not available for switchover
-
Incomplete recovery SCN:0:670662 archive SCN:0:690868
-
-
SQL> alter database recover managed standby database using current logfile disconnect from session;
-
-
#####不cancel日志应用，直接切换
-
SQL> alter database commit to switchover to primary;
-
Role Change: Canceling MRP - no more redo to apply
-
MRP0: Background Media Recovery process shutdown (amondbs)
-
Backup controlfile written to trace file /home/oracle/oracle/diag/rdbms/amondbs_standby/amondbs/trace/amondbs_ora_6312.trc
-
SwitchOver after complete recovery through change 690868
-
Online log /home/oracle/oracle/oradata/amondbs_standby/redo01.log: Thread 1 Group 1 was previously cleared
-
Online log /home/oracle/oracle/oradata/amondbs_standby/redo02.log: Thread 1 Group 2 was previously cleared
-
Online log /home/oracle/oracle/oradata/amondbs_standby/redo03.log: Thread 2 Group 3 was previously cleared
-
Online log /home/oracle/oracle/oradata/amondbs_standby/redo04.log: Thread 2 Group 4 was previously cleared
-
Standby became primary SCN: 690866
-
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 1 NOT ALLOWED amondbs_standby MAXIMUM PERFORMANCE PRIMARY YES MOUNTED 0
-
-
SQL> alter database open;
-
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 1 NOT ALLOWED amondbs_standby MAXIMUM PERFORMANCE PRIMARY YES READ WRITE 691176
-
-
（4）open rac节点2
-
SQL> startup
-
-
SQL> select dbid,inst_id,switchover_status,db_unique_name,protection_mode,database_role,force_logging,open_mode,current_scn from gv$database;
-
-
DBID INST_ID SWITCHOVER_STATUS DB_UNIQUE_NAME PROTECTION_MODE DATABASE_ROLE FOR OPEN_MODE CURRENT_SCN
-
---------- ---------- -------------------- ------------------------------ -------------------- ---------------- --- -------------------- -----------
-
593350850 1 SESSIONS ACTIVE amondbs_primary MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY 690867
-
593350850 2 SESSIONS ACTIVE amondbs_primary MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY 690867
-
-
（5）打开主库日志传输服务
-
alter system set log_archive_dest_state_2=enable scope=both;
-
-
-
总结：
-
（1）从库的online redo和standby redo，根据主库的对应的thread1或n online redo切换而切换
-
（2）主库是rac，从库是单实例，物理standby概念-物理结构相同决定了从库数据文件和redo 文件的个数及其对应的thread个数与主库相同
-
（3）备库是rac时，节点1和节点2对redo传输和应用有序、协调地工作
阅读(956) | 评论(0) | 转发(0) |
