Oracle Data Guard部署分析
这篇文章主要介绍"Oracle Data Guard部署分析",在日常操作中,相信很多人在Oracle Data Guard部署分析问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答"Oracle Data Guard部署分析"的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
序言
DATAGUARD是通过建立一个PRIMARY和STANDBY组来确立其参照关系。
STANDBY一旦创建,DATAGUARD就会通过将主数据库(PRIMARY)的REDO传递给STANDBY数据库,然后在STANDBY中应用REDO实现数据库的同步。
有两种类型的STANDBY:物理STANDBY和逻辑STANDBY。
物理STANDBY提供与主数据库完全一样的拷贝(块到块),数据库SCHEMA,包括索引都是一样的。它是直接应用REDO实现同步的。逻辑STANDBY则不是这样,在逻辑STANDBY中,逻辑信息是相同的,但物理组织和数据结构可以不同,它和主库保持同步的方法是将接收的REDO转换成SQL语句,然后在STANDBY上执行SQL语句。逻辑STANDBY除灾难恢复外还有其它用途,比如用于用户进行查询和报表。
安装环境
在主库--primary上搭建数据库软件,建立监听,采用dbca搭建实例;
在备库--standby上搭建数据库软件,建立监听,但是不需要建立实例。
在linux上搭建oracle数据库,过程略。
步骤概述:
1. 主库开启归档并设置快速恢复区;
2. 主库创建归档同步目录;
3. 主库添加STANDBY日志文件;
4. 主备皆配置网络---监听配置文件;
5. 将主库的密码文件传输到备库;
6. 修改主库的参数文件传输到备库,需修改部分信息;
7. 以动态参数文件的形式启动备库数据库;
8. 主库登录RMAN,【duplicate】复制数据库;
一、 主备库配置
1.创建归档同步目录
[oracle@test1 ~]$ mkdir -p /home/oracle/flash[oracle@test2 ~]$ mkdir -p /home/oracle/flash
2.开启归档并设置快速恢复区
SYS@PROD1 > alter system set db_recovery_file_dest='/home/oracle/flash';alter system set db_recovery_file_dest_size=4G;alter database archivelog;
3.主库添加STANDBY日志文件
alter database add standby logfile group 4 '/home/oracle/s1.log' size 50M;alter database add standby logfile group 5 '/home/oracle/s2.log' size 50M;alter database add standby logfile group 6 '/home/oracle/s3.log' size 50M;alter database add standby logfile group 7 '/home/oracle/s4.log' size 50M;
4.主备库修改监听配置文件
[oracle@test1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@test1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = test1.us.oracle.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=PROD1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1) (SID_NAME=PROD1)))
[oracle@test1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools. PROD1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test1.us.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PROD1) ) ) PROD1_S = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test2.us.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = PROD1) ) )
启动监听,并测试服务名网络的通畅。两台主机均需测试两个服务名(此省略主库的测试):
[oracle@test1 admin]$ lsnrctl start[oracle@test2 admin]$ lsnrctl start
[oracle@test2 admin]$ tnsping prod1TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-FEB-2019 11:46:06Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test1.us.oracle.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD1)))OK (0 msec) [oracle@test2 admin]$ tnsping prod1_sTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-FEB-2019 11:47:04Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test2.us.oracle.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PROD1)))OK (0 msec)
5.传输密码文件:
[oracle@test1 dbs]$ cd $ORACLE_HOME/dbs[oracle@test1 dbs]$ lshc_PROD1.dat init.ora lkPROD1 orapwPROD1 spfilePROD1.ora[oracle@test1 dbs]$ scp orapwPROD1 test2:/u01/app/oracle/product/11.2.0/dbhome_1/dbsoracle@test2's password: orapwPROD1 100% 1536 1.5KB/s 00:00
6.修改参数文件配置:
[oracle@test1 dbs]$ lshc_PROD1.dat init.ora initPROD1.ora lkPROD1 orapwPROD1 spfilePROD1.ora[oracle@test1 dbs]$ cat initPROD1.ora PROD1.__db_cache_size=260046848PROD1.__java_pool_size=4194304PROD1.__large_pool_size=8388608PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentPROD1.__pga_aggregate_target=398458880PROD1.__sga_target=390070272PROD1.__shared_io_pool_size=0PROD1.__shared_pool_size=104857600PROD1.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/PROD1/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='PROD1'*.db_recovery_file_dest='/home/oracle/flash'*.db_recovery_file_dest_size=4294967296*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'*.memory_target=786432000*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=PROD1LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,LEILEI)'LOG_ARCHIVE_DEST_1= 'LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD1'LOG_ARCHIVE_DEST_2= 'SERVICE=PROD1_S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LEILEI'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=30FAL_SERVER=PROD1STANDBY_FILE_MANAGEMENT=AUTO
传输静态参数文件到备库:
[oracle@test1 dbs]$ scp initPROD1.ora test2:/u01/app/oracle/product/11.2.0/dbhome_1/dbsoracle@test2's password: initPROD1.ora 100% 1399 1.4KB/s 00:00
在备库上修改静态参数文件:
[oracle@test2 dbs]$ cat initPROD1.ora *.control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='PROD1'*.db_recovery_file_dest='/home/oracle/flash'*.db_recovery_file_dest_size=4294967296*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'*.memory_target=786432000*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=LEILEILOG_ARCHIVE_CONFIG='DG_CONFIG=(LEILEI,PROD1)'LOG_ARCHIVE_DEST_1= 'LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=LEILEI'LOG_ARCHIVE_DEST_2= 'SERVICE=PROD1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD1'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=30FAL_SERVER=PROD1STANDBY_FILE_MANAGEMENT=AUTO
7.在备库上,创建静态参数文件中对应的目录文件夹
[oracle@test2 dbs]$ mkdir -p /home/oracle/flashmkdir -p /u01/app/oracle/admin/PROD1/adumpmkdir -p /u01/app/oracle/oradata/PROD1mkdir -p /u01/app/oracle/fast_recovery_area/PROD1
8.启动备库到【nomount】阶段;
[oracle@test2 ~]$ export ORACLE_SID=PROD1[oracle@test2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 13:59:23 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance. SYS@PROD1 >startup nomount;ORACLE instance started.Total System Global Area 1219260416 bytesFixed Size 2252744 bytesVariable Size 788529208 bytesDatabase Buffers 419430400 bytesRedo Buffers 9048064 bytes
9.主库登录RMAN,复制数据库;
[oracle@test1 ~]$ rman target sys/oracle@prod1 auxiliary sys/oracle@prod1_s Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 25 13:43:23 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: PROD1 (DBID=2222506242)connected to auxiliary database: PROD1 (not mounted) RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 2019-02-25 13:43:31using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=10 device type=DISKcontents of Memory Script:{ backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPROD1' auxiliary format '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPROD1' ;}executing Memory Script Starting backup at 2019-02-25 13:43:32allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=17 device type=DISKFinished backup at 2019-02-25 13:43:33 contents of Memory Script:{ backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/PROD1/control01.ctl'; restore clone controlfile to '/u01/app/oracle/fast_recovery_area/PROD1/control02.ctl' from '/u01/app/oracle/oradata/PROD1/control01.ctl';}executing Memory Script Starting backup at 2019-02-25 13:43:33using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copycopying standby control fileoutput file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD1.f tag=TAG20190225T134333 RECID=2 STAMP=1001166214channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 2019-02-25 13:43:36 Starting restore at 2019-02-25 13:43:36using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copyFinished restore at 2019-02-25 13:43:37 contents of Memory Script:{ sql clone 'alter database mount standby database';}executing Memory Script sql statement: alter database mount standby database contents of Memory Script:{ set newname for tempfile 1 to "/u01/app/oracle/oradata/PROD1/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/PROD1/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/PROD1/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/PROD1/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/PROD1/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/PROD1/example01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/PROD1/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/PROD1/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/PROD1/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/PROD1/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/PROD1/example01.dbf" ; sql 'alter system archive log current';}executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/PROD1/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 2019-02-25 13:43:43using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbfoutput file name=/u01/app/oracle/oradata/PROD1/system01.dbf tag=TAG20190225T134343channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbfoutput file name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf tag=TAG20190225T134343channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbfoutput file name=/u01/app/oracle/oradata/PROD1/example01.dbf tag=TAG20190225T134343channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbfoutput file name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf tag=TAG20190225T134343channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbfoutput file name=/u01/app/oracle/oradata/PROD1/users01.dbf tag=TAG20190225T134343channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 2019-02-25 13:45:16 sql statement: alter system archive log current contents of Memory Script:{ switch clone datafile all;}executing Memory Script datafile 1 switched to datafile copyinput datafile copy RECID=2 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=3 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=4 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=5 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/users01.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=6 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/example01.dbfFinished Duplicate Db at 2019-02-25 13:45:32
二、 检查主备搭建情况
1. 主库
[oracle@test1 ~]$ export ORACLE_SID=PROD1[oracle@test1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 13:46:56 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS---------------------------------------- -------------------------------- ----------------------------------------READ WRITE PRIMARY SESSIONS ACTIVE SYS@PROD1 >select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------------- ------------ ------------------- 1 1 16 52428800 512 1 NO CURRENT 996478 2019-02-25 14:19:03 2.8147E+14 2 1 14 52428800 512 1 YES INACTIVE 996073 2019-02-25 14:12:33 996161 2019-02-25 14:13:55 3 1 15 52428800 512 1 YES ACTIVE 996161 2019-02-25 14:13:55 996478 2019-02-25 14:19:03
2. 备库
[oracle@test2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 14:04:36 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS---------------------------------------- -------------------------------- ----------------------------------------MOUNTED PHYSICAL STANDBY RECOVERY NEEDED 备库处于MOUNTED模式下,只能开启实时应用在线日志或者归档日志。 SYS@PROD1 >select PROCESS,STATUS,SEQUENCE# from v$managed_standby; PROCESS STATUS SEQUENCE#------------------ ---------------------- -----------------ARCH CLOSING 15ARCH CONNECTED 0......ARCH CONNECTED 0ARCH CONNECTED 0ARCH CONNECTED 0RFS IDLE 0RFS IDLE 0RFS IDLE 16 33 rows selected. SYS@PROD1 >alter database open;Database altered. SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS------------- ----------------- -------------------READ ONLY PHYSICAL STANDBY NOT ALLOWED
三、 测试主备功能及切换应用
Ø 测试1
Ø 测试1Test1:SYS@PROD1 >create table t1 (id number);Table created. Test2:SYS@PROD1 >desc t1ERROR:ORA-04043: object t1 does not exist SYS@PROD1 >recover managed standby database disconnect;Media recovery complete. SYS@PROD1 >desc t1ERROR:ORA-04043: object t1 does not exist Test1:SYS@PROD1 >alter system switch logfile;System altered. Test2:SYS@PROD1 >desc t1 Name Null? Type ---------------------------------------- ID NUMBER
Ø 测试2
11G新特性:ADG
Test2:SYS@PROD1 >recover managed standby database cancel;Media recovery complete. SYS@PROD1 >recover managed standby database using current logfile disconnect from session;Media recovery complete. Test1:SYS@PROD1 >create table t2 (id number);Table created. Test2:SYS@PROD1 >desc t2 Name Null? Type ----------------------------------- ID NUMBER
Ø 测试3
以下为Data Guard最为重要的两个功能:
参考博客:http://www.cnblogs.com/hllnj2008/p/4995099.html
角色切换
TEST1:SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS---------------------------------------- -------------------------------- ----------------------------------------READ WRITE PRIMARY TO STANDBY SYS@PROD1 >alter database commit to switchover to physical standby; Database altered. SYS@PROD1 >conn / as sysdba;Connected to an idle instance.SYS@PROD1 >startup ORACLE instance started. Total System Global Area 1219260416 bytesFixed Size 2252744 bytesVariable Size 788529208 bytesDatabase Buffers 419430400 bytesRedo Buffers 9048064 bytesDatabase mounted.Database opened.SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS---------------------------------------- -------------------------------- ----------------------------------------READ ONLY PHYSICAL STANDBY TO PRIMARY TEST2:SYS@PROD1 >recover managed standby database cancel;Media recovery complete. SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS---------------------------------------- -------------------------------- ----------------------------------------READ ONLY PHYSICAL STANDBY TO PRIMARY SYS@PROD1 >alter database commit to switchover to primary;Database altered. SYS@PROD1 >select open_mode,database_role,switchover_status from v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS---------------------------------------- -------------------------------- ----------------------------------------MOUNTED PRIMARY NOT ALLOWED SYS@PROD1 >alter database open;Database altered. SYS@PROD1 >insert into t1 values (1);1 row created. SYS@PROD1 >commit;Commit complete. SYS@PROD1 >select * from t1; ID---------- 1 TEST1:SYS@PROD1 >select * from t1;no rows selected SYS@PROD1 >recover managed standby database using current logfile disconnect from session;Media recovery complete. SYS@PROD1 >select * from t1; ID---------- 1
到此,关于"Oracle Data Guard部署分析"的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注网站,小编会继续努力为大家带来更多实用的文章!