环境:
角色 机器名 操作系统 IP 备注
主库 db1 CentOS 7.3 x86_64 192.168.31.128 安装Oracle,创建数据库
备库 db2 CentOS 7.3 x86_64 192.168.31.129 只安装Oracle
准备工作:
在db1的/etc/hosts里增加
192.168.31.128 db1
192.168.31.129 db2
在db2的/etc/hosts里增加
192.168.31.128 db1
192.168.31.129 db2
1.主库打开归档及强制归档(db1)
--检查Oracle是否开启归档
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
--可以看到Automatic archival Disabled说明未打开归档
--打开归档(打开归档需要先关闭Oracle,然后将数据库启动至mount状态才能修改)
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog; --打开归档
SQL> alter database force logging; --打开强制归档也可以在数据库open状态下打开
SQL> alter database open; --打开数据库
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
2.创建多组standby redo log
--最少需要多一组,standby redo log是使用Real Time Apply的必要条件
SQL> select group#,member from v$logfile;
SQL> select bytes/1024/1024 from v$log;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby04.log') size 50m;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby05.log') size 50m;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby06.log') size 50m;
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/orcl/standby07.log') size 50m;
SQL> select group#,member from v$logfile;
3.修改参数文件
--修改参数文件前,我们先进行备份
SQL> create pfile='/tmp/orcl.pfile' from spfile;
--在修改前我们需要查看下备份的参数文件,根据具体环境更改下面语句
SQL> alter system set db_unique_name=db1 scope=spfile;
SQL> alter system set log_archive_config='dg_config=(db1,db2)' scope=both;
--报错:
--ERROR at line 1:
--ORA-02097: parameter cannot be modified because specified value is invalid
--ORA-16053: DB_UNIQUE_NAME db1 is not in the Data Guard Configuration
--可能会遇上如下报错信息,这个时候需要重启下数据库
SQL> shutdown immediate;
SQL> startup
SQL> alter system set log_archive_dest_1= 'location=/u01/app/oracle/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=db1' scope=both;
SQL> alter system set log_archive_dest_2= 'service=db2 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db2' scope=both;
SQL> alter system set log_archive_dest_state_1=enable scope=both;
SQL> alter system set log_archive_dest_state_2=enable scope=both;
SQL> alter system set standby_file_management=auto scope=both;
SQL> alter system set fal_server=db2 scope=both;
SQL> alter system set fal_client=db1 scope=both;
SQL> alter system set db_file_name_convert='/u01/app/oracle/arch/','/u01/app/oracle/arch/' scope=spfile;
SQL> alter system set log_file_name_convert='/u01/app/oracle/arch/','/u01/app/oracle/arch/' scope=spfile;
4.修改监听文件
[oracle@db1 ~]$ vim $ORACLE_HOME/admin/tnsnames.ora
#在后面增加
db1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)
db2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
)
)
#可能需要重配一下监听
#验证
[oracle@db1 admin]$ tnsping db1
[oracle@db1 admin]$ sqlplus sys/oracle@db1 as sysdba
5.RMAN备份主库
#创建备份存放目录
[oracle@db1 ~]$ mkdir -p /u01/app/oracle/rman_backup
#执行备份
rman>run{
allocate channel c1 type disk;
backup format '/u01/app/oracle/rman_backup/orcl_%T_%s_%p' database;
sql 'alter system archive log current';
backup format '/u01/app/oracle/rman_backup/archive_log_%T_%s_%p' archivelog all;
backup spfile format '/u01/app/oracle/rman_backup/spfile_%u_%T.bak';
release channel c1;
}
rman>copy current controlfile for standby to '/u01/app/oracle/rman_backup/standby.ctl';
6.复制文件至备库
#数据文件
[oracle@db1 oracle]$ scp -r rman_backup db2:/u01/app/oracle
#参数文件
[oracle@db1 ~]$ cd $ORACLE_HOME/dbs
[oracle@db1 dbs]$ scp -r initorcl.ora db2:$ORACLE_HOME/dbs
#监听文件
[oracle@db1 dbs]$ cd $ORACLE_HOME/network/admin
[oracle@db1 admin]$ scp -r listener.ora tnsnames.ora db2:$ORACLE_HOME/network/admin
#密码文件
[oracle@db1 dbs]$ orapwd file=orapworcl password=oracle force=y
[oracle@db1 dbs]$ scp -r orapworcl db2:$ORACLE_HOME/dbs
7.恢复参数文件(db2)
RMAN> set dbid 3044204071
RMAN> startup nomount;
--这里会报错不用理会即可
RMAN> restore spfile to pfile '/tmp/orcl.pfile' from '/u01/app/oracle/rman_backup/spfile_1dtapp8k_20180817.bak';
--我们将参数文件恢复至/tmp/orcl.pfile,因为这个是主库的参数文件,备库略有不同
8.修改备库参数文件(db2)
[oracle@db2 ~]$ vim /tmp/orcl.pfile
#将里面的 DB1 db1 变成相应的 DB2 db2 ,将 DB1 变成 db1
orcl.__db_cache_size=754974720
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=369098752
orcl.__sga_target=1090519040
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=268435456
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/arch/','/u01/app/oracle/arch/'
*.db_name='orcl'
*.db_unique_name='DB2'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='DB2'
*.fal_server='DB1'
*.log_archive_config='dg_config=(db2,db1)'
*.log_archive_dest_1='location=/u01/app/oracle/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=db2'
*.log_archive_dest_2='service=db1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/u01/app/oracle/arch/','/u01/app/oracle/arch/'
*.open_cursors=300
*.pga_aggregate_target=362807296
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.sga_target=1088421888
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
9.准备RMAN恢复工作(db2)
#创建相应的目录,复制备库控制文件到相应的位置
[oracle@db2 ~]$ mkdir -p /u01/app/oracle/admin/orcl/adump
[oracle@db2 ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@db2 ~]$ mkdir -p /u01/app/oracle/arch/
[oracle@db2 ~]$ cp /u01/app/oracle/rman_backup/standby.ctl /u01/app/oracle/oradata/orcl/control01.ctl
[oracle@db2 ~]$ cp /u01/app/oracle/rman_backup/standby.ctl /u01/app/oracle/oradata/orcl/control02.ctl
[oracle@db2 ~]$ lsnrctl start
#启动监听(可能需要重建)
10.生成备库参数文件(db2)
SQL> shutdown immediate;
SQL> startup nomount pfile='/tmp/orcl.pfile';
SQL> create spfile from pfile='/tmp/orcl.pfile';
SQL> alter database mount;
11.恢复数据库(db2)
RMAN> restore database;
12.启动备库(db2)
SQL > alter database open read only;
--在这里启动的时候如果出现
--ERROR at line 1:
--ORA-10458: standby database requires recovery
--ORA-01152: file 1 was not restored from a sufficiently old backup
--ORA-01110: data file 1: '/opt/oracle/oradat
--先使用shutdown immediate关闭后再重新启动
13 主从同步复制
--先使用shutdown immediate关闭后再重新启动
SQl> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database using current logfile disconnect from session;
--注意:刚重启完你会发现主库的数据还没过来,但是过段时间就过来了,在生产环境中我们需要快速处理这个问题,以便减少宕机时间。
--关闭完备库后,在主库查看
--db1:
SQL> select dest_name,status,error from v$archive_dest;
DEST_NAME STATUS ERROR
------------------------------ --------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 ERROR ORA-03113: end-of-file on communication channel
--可以看到LOG_ARCHIVE_DEST_2是错误的,这个是因为没有连接到备库的归档路径,默认情况下Dataguard会每300秒自动连接,这边为了快读处理
--解决办法:在主库执行
--1 重启一下主库
--2 执行如下
SQL> alter system set log_archive_dest_state_2= enable;
--再查询,如果依然是如此则需要检查备库的网络及监听
--如果db2还不能启动,用如下命令
--启动到recover mange模式
sql> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database using current logfile disconnect from session;
--如果不行就用下面命令,停止应用日志
SQL> recover managed standby database cancel;
--然后再read only启库
SQL> alter database open read only;
14 测试语句
--测试有如下语句:
select sequence#,applied from v$archived_log;
select process,status from v$managed_standby;
select sequence# from v$log_history;
col DEST_NAME for a50;
select dest_name,status,error from v$archive_dest;
15 部署异常处理
altert出现:PING[ARC1]: Heartbeat failed to connect to standby 'orcl_pd'. Error is 16191.
#参照如下url
http://blog.51cto.com/xiaomodian2/1341624
#如果主备不同步,参照如下url
#oracle11g dataguard 备库数据同步的检查方法
https://blog.csdn.net/ydwheel/article/details/70124908
16 常用命令
--1.查询数据库角色,主从状态
col open_mode for a25
col database_role for a30
col db_unique_name for a20
select open_mode,database_role,db_unique_name from v$database;
--2.查询保护模式语句
SELECT PROTECTION_MODE FROM V$DATABASE;
--3.切换模式
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
--4.查看归档状态是否有效
SELECT dest_name, status, destination FROM v$archive_dest;
--5.查询数据库状态
set lines 1000;
col file_name for a60;
col TABLESPACE_NAME for a50;
col MEMBER for a60;
col name for a60;
set pages 500;
select file_id,file_name,tablespace_name,bytes/1024/1024/1024 from dba_data_files order by 1;
select file_id,file_name,tablespace_name,bytes/1024/1024/1024 from dba_temp_files order by 1;
select * from v$logfile;
select * from v$log;
select bytes/1024/1024 from v$log;
show parameter spfile;
select * from v$controlfile;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select open_mode from v$database;
--db1 备库
--6.更改为最大可用模式
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
SQL> shutdown immediate;
SQL> alter database mount standby database;
SQL> alter database open read only;
--db2 备库
--看alert日志
SQL> alter database recover managed standby database;
--db1 查看主库是否开启了实时应用
SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
---------------------------------------------------------------------
MANAGED REAL TIME APPLY
--db2 查看主库是否开启了实时应用
SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
RECOVERY_MODE
---------------------------------------------------------------------
IDLE
17 验证主从同步
--db1 主 ddl
SQL> create table t1 as select * from dba_objects;
SQL> select count(*) from t1;
COUNT(*)
----------
86954
--db2 从
SQL> select count(*) from t1;
COUNT(*)
----------
86954
--db1 主 dml
SQL> insert into t1 select * from t1;
SQL> commit;
SQL> select count(*) from t1;
COUNT(*)
----------
173908
--db2 从
SQL> select count(*) from t1;
COUNT(*)
----------
173908