阿里oracle主从复制

环境：

角色 机器名 操作系统 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