环境:
OS:Red Hat Linux As 5
DB:11.2.0.1
11G版本对Dataguard上有了比较大的改进,就是在open备库的情况下,备库依然可以应用主库传过来的归档日志,记得在10g的时候,想打开备库查看数据数据是否已经传输过来,还必须先停掉日志应用.下面是介绍如何创建物理备库的过程,数据库采用的是普通文件管理的方式(主备库数据文件路径保持一致),过程是比较有点繁琐,呵呵.
--------------------------主库上的操作-------------------------------------------
1.设置主库为force logging模式
SQL> alter database force logging;
Database altered.
2.编辑初始化参数
SQL> create pfile='/u01/export/home/oracle/pfile.txt' from spfile;
File created.
Vi pfile.txt 添加红色部分的内容
[oracle@primary ~]$ more pfile.txt
oracl.__db_cache_size=272629760
oracl.__java_pool_size=4194304
oracl.__large_pool_size=4194304
oracl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oracl.__pga_aggregate_target=276824064
oracl.__sga_target=415236096
oracl.__shared_io_pool_size=0
oracl.__shared_pool_size=125829120
oracl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oracl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oracl/control01.ctl','/u02/app/oracle/oradata/oracl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oracl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraclXDB)'
*.memory_target=692060160
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=oracl
*.log_archive_config='dg_config=(oracl,oraclbak)'
*.log_archive_dest_1=
'location=/u02/archive_log/
valid_for=(all_logfiles,all_roles)
db_unique_name=oracl'
*.log_archive_dest_2=
'service=dup_oracl async
valid_for=(online_logfiles,primary_role)
db_unique_name=oraclbak'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management=auto
*.fal_server=dup_oracl
*.fal_client=tar_oracl
*.db_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata/oracl/'
*.log_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata/oracl/','/u02/app/oracle/oradata/oracl/','/u02/app/oracle/oradata/oracl/'
3.使用步骤2修改的参数启动主库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/export/home/oracle/pfile.txt';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 690503680 bytes
Fixed Size 1338756 bytes
Variable Size 411042428 bytes
Database Buffers 272629760 bytes
Redo Buffers 5492736 bytes
Database mounted.
Database opened.
4.添加standby日志文件,大小跟v$log中的日志文件大小保持一致,这里的日志组从4开始,因为主库已经创建了3组online日志组了.
alter database add standby logfile group 4 ('/u02/app/oracle/oradata/oracl/stdbyredo01.log') SIZE 512m;
alter database add standby logfile group 5 ('/u02/app/oracle/oradata/oracl/stdbyredo02.log') SIZE 512m;
alter database add standby logfile group 6 ('/u02/app/oracle/oradata/oracl/stdbyredo03.log') SIZE 512m;
alter database add standby logfile group 7 ('/u02/app/oracle/oradata/oracl/stdbyredo04.log') SIZE 512m;
5.创建standby控制文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 690503680 bytes
Fixed Size 1338756 bytes
Variable Size 411042428 bytes
Database Buffers 272629760 bytes
Redo Buffers 5492736 bytes
Database mounted.
SQL> alter database create standby controlfile AS '/u01/export/home/oracle/standby.ctl';
Database altered.
SQL> alter database open;
Database altered.
6.配置tnsnames文件
使用netca配置tnsnames,文件内容如下:
[oracle@primary admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/n
etwork/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TAR_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracl)
)
)
DUP_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oraclbak)
)
)
7.查看数据文件和日志文件的目录,以便在备库也创建相应的目录
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/oracl/system01.dbf
/u01/app/oracle/oradata/oracl/sysaux01.dbf
/u01/app/oracle/oradata/oracl/undotbs01.dbf
/u01/app/oracle/oradata/oracl/users01.dbf
/u01/app/oracle/oradata/oracl/hxl001.dbf
/u01/app/oracle/oradata/oracl/hxl002.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/oracl/redo0301.log
/u02/app/oracle/oradata/oracl/redo0302.log
/u01/app/oracle/oradata/oracl/redo0201.log
/u02/app/oracle/oradata/oracl/redo0202.log
/u01/app/oracle/oradata/oracl/redo0101.log
/u02/app/oracle/oradata/oracl/redo0102.log
/u02/app/oracle/oradata/oracl/stdbyredo01.log
/u02/app/oracle/oradata/oracl/stdbyredo02.log
/u02/app/oracle/oradata/oracl/stdbyredo03.log
/u02/app/oracle/oradata/oracl/stdbyredo04.log
SQL>select name from v$controlfile;
NAME
---------------------------------------------
/u01/app/oracle/oradata/oracl/control01.ctl
/u02/app/oracle/oradata/oracl/control02.ctl
8.shutdown数据库
将数据文件,日志文件(online日志文件和standby日志文件),standby控制文件(/u01/export/home/oracle/standby.ctl),tnsnames文件,密码文件拷贝到备库的相应目录.
可以使用tar工具将如上文件一起打包传输到备库.
这个时候可以先不启动数据库,等整个过程完成后再启动数据库.
--------------------------------备库上的操作--------------------------------
1.创建相应目录(这里的目录为了维护方便,保持跟主库一致)
mkdir –p /u02/app/oracle/oradata/oracl/
midir –p /u01/app/oracle/oradata/oracl
2.将主库上传过来的相应文件存放到备库的相应目录,除了控制文件需要替换为standby控制文件外,其他的文件不需要做任何改动.这里需要将standby.ctl替换掉主库拷贝过来的control01.ctl,control02.ctl
[oracle@stdby oracl]$ cp standby.ctl control01.ctl
[oracle@stdby oracl]$ cp standby.ctl control02.ctl
3.配置监听和tnsnames(监听器需要重新配置,tnsnames可以使用从主库拷贝过来的)
使用netca配置监听和tnsnames,tnsnames的内容如下:
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/n
etwork/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TAR_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracl)
)
)
DUP_ORACL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oraclbak)
)
)
4.修改从主库传过来的参数文件
修改后的内容如下,注意红色部分.
[oracle@stdby ftp]$ more pfile.txt
oraclbak.__db_cache_size=272629760
oraclbak.__java_pool_size=4194304
oraclbak.__large_pool_size=4194304
oraclbak.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oraclbak.__pga_aggregate_target=276824064
oraclbak.__sga_target=415236096
oraclbak.__shared_io_pool_size=0
oraclbak.__shared_pool_size=125829120
oraclbak.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oracl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oracl/control01.ctl','/u02/app/oracle/oradata/oracl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oracl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraclXDB)'
*.memory_target=692060160
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=oraclbak
*.log_archive_config='dg_config=(oracl,oraclbak)'
*.log_archive_dest_1=
'location=/u02/archive_log/
valid_for=(all_logfiles,all_roles)
db_unique_name=oraclbak'
*.log_archive_dest_2=
'service=tar_oracl async
valid_for=(online_logfiles,primary_role)
db_unique_name=oracl'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management=auto
*.fal_server=tar_oracl
*.fal_client=dup_oracl
*.db_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata/oracl/'
*.log_file_name_convert='/u01/app/oracle/oradata/oracl/','/u01/app/oracle/oradata/oracl/','/u02/app/oracle/oradata/oracl/','/u02/app/oracle/oradata/oracl/'
创建参数文件中定义的目录(仔细检查每个目录,没有创建的话需要创建)
mkdir –p /u02/archive_log/
mkdir –p /u01/app/oracle/admin/oracl/adump
5.创建密码文件,创建自己的密码文件好像有问题,备库的密码文件需要跟主库一致,否则导致日志传输不到备库,有待验证.我最后是将主库的日志直接copy到备库,重命名后使用.
cd $ORACLE_HOME/dbs
orapwd file=orapworaclbak password=sys entries=10 force=y
6.确保备库的环境变量ORACLE_SID=oraclbak后,使用步骤4改好的pfile创建spfile
[oracle@stdby archive_log]$ echo $ORACLE_SID
oraclbak
SQL> connect / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/ftp/pfile.txt';
File created.
7.mount数据库
SQL> startup mount
ORACLE instance started.
Total System Global Area 690503680 bytes
Fixed Size 1338756 bytes
Variable Size 411042428 bytes
Database Buffers 272629760 bytes
Redo Buffers 5492736 bytes
Database mounted.
8.应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
这样主备库就搭建完成了,主备库的验证这里就省略了.
备库可以在打开的情况下同时应用归档日志.
alter database recover managed standby database cancel;
alter database open;--alter database open read only以只读方式打开
alter database recover managed standby database using current logfile disconnect from session;
搭建过程中遇到的问题:
问题1:主库的日志无法传输到备库
ORA-16191: Primary log shipping client not logged on standby
原因是备库的监听器无法识别服务,监听使用的动态监听,不懂为什么监听不到服务,我猜测是因为备库没有打开的原因,后来干脆使用静态监听.
最后备库的监听文件内容如下(红色部分是手工添加的静态监听):
[oracle@stdby admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/n
etwork/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oraclbak)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = oraclbak)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
问题2:备库在启动的时候报如下错误
Startup mount
startup ORA-00845: MEMORY_TARGET not supported on this system
刚开始我以为是主库和备库的SGA不一致导致,因为备库的有关内存初始化参数
我没有修改,后来我试着将备库的SGA改小,但问题依旧.后来网上找到相关资料是说11G采用内存自动管理的情况下,/dev/shm需要大于MEMORY_TARGET.经过核查当前的/dev/shm确实小于MEMORY_TARGET.
解决办法:增加/dev/shn
1.编辑/etc/fstab 将/dev/shm添加到2g
[oracle@stdby archive_log]$ more /etc/fstab
LABEL=/ / ext3 defaults 1 1
devpts /dev/pts devpts gid=5,mode=620 0 0
tmpfs /dev/shm tmpfs defaults,size=2g 0 0
proc /proc proc defaults 0 0
sysfs /sys sysfs defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
/dev/sdb1 /u01 ext3 defaults 0 0
/dev/sdc1 /u02 ext3 defaults 0 0
2.重启系统或是重新remount
mount -o remount,size=2g /dev/shm
问题3.在主库添加一个数据文件传到备份变成了
select name from v$datafile t where file#=7;
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007
原因是没有正确设置参数: db_file_name_convert
说明:
A.要想启动日志实时传送到备库需要如下处理
1.主库参数
alter system set log_archive_dest_2='service=dup_oracl lgwr async
valid_for=(online_logfiles,primary_role)
db_unique_name=oraclbak' scope=spfile;
或
alter system set log_archive_dest_2='service=dup_oracl lgwr sync
valid_for=(online_logfiles,primary_role)
db_unique_name=oraclbak' scope=spfile;
这里可以设置延迟多长时间
alter system set log_archive_dest_2='service=dup_oracl lgwr sync affirm delay=5
valid_for=(online_logfiles,primary_role)
db_unique_name=oraclbak' scope=spfile;
这里设置延迟5分钟
2.备库
alter database recover managed standby database using current logfile disconnect from session;
B.Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
C.主备库添加online/Standby日志组
主库
主库添加online日志组
alter database add logfile
group 8 ('/u01/app/oracle/oradata/oracl/redo0401.log','/u01/app/oracle/oradata/oracl/redo0402.log') size 512m;
主库添加Standby日志组
alter database add standby logfile
group 9 ('/u02/app/oracle/oradata/oracl/stdbyredo05.log') SIZE 512m;
备库:
备库添加standby日志组
alter database recover managed standby database cancel;
alter database add standby logfile
group 9 ('/u02/app/oracle/oradata/oracl/stdbyredo05.log') SIZE 512m
备库添加online日志组
SQL> alter session set nls_language=american;
Session altered.
SQL> alter database add logfile
2 group 8 ('/u01/app/oracle/oradata/oracl/redo0401.log',
3 '/u01/app/oracle/oradata/oracl/redo0402.log') size 512m;
alter database add logfile
*
ERROR at line 1:
ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is
automatic.
SQL> alter system set standby_file_management='manual';
System altered.
SQL> alter database add logfile
2 group 8 ('/u01/app/oracle/oradata/oracl/redo0401.log',
3 '/u01/app/oracle/oradata/oracl/redo0402.log') size 512m;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
System altered.
---------------------------主备库切换过程-----------------------------
1.Data Guard 主备库切换
Data Guard的主备库切换分为2种,switchover和failover.遇到突发事件,基本都是采用failover切换. 在主备库切换完成后应该修改数据库服务器的IP地址,即备库的地址.这样才能保证系统的正常运作.
1.1. switchover
一般SWITCHOVER切换都是计划中的切换，特点是在切换后，不会丢失任何的数据，而且这个过程是可逆的，整个DATA GUARD环境不会被破坏，原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
主库上的操作
1.查看switchover状态
sql> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
2.切换成从库
alter database commit to switchover to physical standby with session shutdown;
3.确保实例在mount状态下
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 343935360 bytes
Database Buffers 71303168 bytes
Redo Buffers 6094848 bytes
Database mounted.
5.切换成从库
SQL> alter database commit to switchover to physical standby;
Database altered.
6.应用日志
SQL> alter database recover managed standby database disconnect from session;
Database altered.
备库上的操作:
1.查看switchover状态
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
2.关闭所有的客户端连接后切换为主库
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL> alter database commit to switchover to primary;
Database altered.
3.打开数据库
SQL> alter database open;
Database altered.
1.2 Failovers：
FAILOVER切换一般是PRIMARY数据库发生故障后的切换，这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后，可能会造成数据的丢失。而且这个过程不是可逆的，DATA GUARD环境会被破坏。由于PRIMARY数据库已经无法启动，所以FAILOVER切换所需的条件并不多，只要检查STANDBY是否运行在最大保护模式下，如果是的话，需要将其置为最大性能模式，否则切换到PRIMARY角色也无法启动。
FailOver后,主库会做一次resetlogs,整个DG环境也就破坏了.
1.查看是否有日志GAP,没有应用的日志：
SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如果有，则拷贝过来并且注册
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '路径';
重复查看直到没有应用的日志：
2.然后停止应用归档：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
3.下面将STANDBY数据库切换为PRIMARY数据库：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
检查数据库是否已经切换成功：
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
-- The End --
