OracleDataGuard部署分析
这篇文章主要介绍“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 prod1 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-FEB-2019 11:46:06 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting 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_s TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 25-FEB-2019 11:47:04 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting 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]$ ls hc_PROD1.dat init.ora lkPROD1 orapwPROD1 spfilePROD1.ora [oracle@test1 dbs]$ scp orapwPROD1 test2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs oracle@test2's password: orapwPROD1 100% 1536 1.5KB/s 00:00
6.修改参数文件配置:
[oracle@test1 dbs]$ ls hc_PROD1.dat init.ora initPROD1.ora lkPROD1 orapwPROD1 spfilePROD1.ora [oracle@test1 dbs]$ cat initPROD1.ora PROD1.__db_cache_size=260046848 PROD1.__java_pool_size=4194304 PROD1.__large_pool_size=8388608 PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment PROD1.__pga_aggregate_target=398458880 PROD1.__sga_target=390070272 PROD1.__shared_io_pool_size=0 PROD1.__shared_pool_size=104857600 PROD1.__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=PROD1 LOG_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=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 FAL_SERVER=PROD1 STANDBY_FILE_MANAGEMENT=AUTO
传输静态参数文件到备库:
[oracle@test1 dbs]$ scp initPROD1.ora test2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs oracle@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=LEILEI LOG_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=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30 FAL_SERVER=PROD1 STANDBY_FILE_MANAGEMENT=AUTO
7.在备库上,创建静态参数文件中对应的目录文件夹
[oracle@test2 dbs]$ mkdir -p /home/oracle/flash mkdir -p /u01/app/oracle/admin/PROD1/adump mkdir -p /u01/app/oracle/oradata/PROD1 mkdir -p /u01/app/oracle/fast_recovery_area/PROD1
8.启动备库到【nomount】阶段;
[oracle@test2 ~]$ export ORACLE_SID=PROD1 [oracle@test2 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 25 13:59:23 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SYS@PROD1 >startup nomount; ORACLE instance started. Total System Global Area 1219260416 bytes Fixed Size 2252744 bytes Variable Size 788529208 bytes Database Buffers 419430400 bytes Redo 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 2019 Copyright (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:31 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=10 device type=DISK contents 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:32 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 device type=DISK Finished 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:33 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD1.f tag=TAG20190225T134333 RECID=2 STAMP=1001166214 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 2019-02-25 13:43:36 Starting restore at 2019-02-25 13:43:36 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished 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:43 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf output file name=/u01/app/oracle/oradata/PROD1/system01.dbf tag=TAG20190225T134343 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf output file name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf tag=TAG20190225T134343 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf output file name=/u01/app/oracle/oradata/PROD1/example01.dbf tag=TAG20190225T134343 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf output file name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf tag=TAG20190225T134343 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf output file name=/u01/app/oracle/oradata/PROD1/users01.dbf tag=TAG20190225T134343 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished 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 copy input datafile copy RECID=2 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=6 STAMP=1001167339 file name=/u01/app/oracle/oradata/PROD1/example01.dbf Finished 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 Production With 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 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With 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 15 ARCH CONNECTED 0 ...... ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS 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
Ø 测试1 Test1: SYS@PROD1 >create table t1 (id number); Table created. Test2: SYS@PROD1 >desc t1 ERROR: ORA-04043: object t1 does not exist SYS@PROD1 >recover managed standby database disconnect; Media recovery complete. SYS@PROD1 >desc t1 ERROR: 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 bytes Fixed Size 2252744 bytes Variable Size 788529208 bytes Database Buffers 419430400 bytes Redo Buffers 9048064 bytes Database 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部署分析”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!
网页题目:OracleDataGuard部署分析
网站网址:http://pwwzsj.com/article/jpiepg.html