Oracle 11G R2利用RMAN搭建DataGuard环境

Oracle 11G R2利用RMAN搭建DataGuard环境

环境：

角色	机器名	操作系统	IP	备注
主库	db1	CentOS 5.11 x86_64	192.168.2.241	安装Oracle，创建数据库
备库	db2	CentOS 5.11 x86_64	192.168.2.242	只安装Oracle

准备工作:
在db1的/etc/hosts里增加
127.0.0.1      db1
192.168.2.242    db2
在db2的/etc/hosts里增加
127.0.0.1      db2
192.168.2.241    db1

目录

1. 主库打开归档及强制归档
2. 创建3组standby redolog
3. 修改参数文件
4. 修改监听文件
5. RMAN备份主库
6. 复制文件至备库
7. 恢复参数文件(db2)
8. 修改备库参数文件(db2)
9. 准备RMAN恢复工作(db2)
10. 生成备库参数文件(db2)
11. 恢复数据库(db2)
12. 启动备库(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;
    GROUP#        MEMBER
--------------------------------------------------------------------------------
        3                /opt/oracle/oradata/tpy100/redo03.log
        2                /opt/oracle/oradata/tpy100/redo02.log
        1                /opt/oracle/oradata/tpy100/redo01.log
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby04.log') size 50m;
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby05.log') size 50m;
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby06.log') size 50m;
SQL> alter database add standby logfile ('/opt/oracle/oradata/tpy100/standby07.log') size 50m;
SQL> select group#,member from v$logfile;
    GROUP#        MEMBER
--------------------------------------------------------------------------------
        3                /opt/oracle/oradata/tpy100/redo03.log
        2                /opt/oracle/oradata/tpy100/redo02.log
        1                /opt/oracle/oradata/tpy100/redo01.log
        4                /opt/oracle/oradata/tpy100/standby04.log
        5                /opt/oracle/oradata/tpy100/standby05.log
        6                /opt/oracle/oradata/tpy100/standby06.log

        7                /opt/oracle/oradata/tpy100/standby07.log

3.修改参数文件
修改参数文件前，我们先进行备份
SQL> create pfile='/tmp/tpy100.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;
SQL> alter system set log_archive_dest_1= 'location=/opt/oracle/flash_recovery_area/ valid_for=(all_logfiles,all_roles)  db_unique_name=db1' 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=/opt/oracle/flash_recovery_area/ valid_for=(all_logfiles,all_roles)  db_unique_name=db1' scope=both;
SQL> alter system set log_archive_dest_2= 'service=db2 async  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='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area' scope=spfile;
SQL> alter system set log_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area' scope=spfile;

4.修改监听文件
[oracle@db1 ~]$ vim /opt/oracle/product/11.2.0/network/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)
    )
  )

5.RMAN备份主库
创建备份存放目录

[oracle@db1 ~]$ mkdir -p /opt/oracle/dbackup
执行备份
rman>run{
allocate channel c1 type disk;
backup format '/opt/oracle/dbackup/tpy100_%T_%s_%p' database;
sql 'alter system archive log current';
backup format '/opt/oracle/dbackup/archive_log_%T_%s_%p' archivelog all;
backup spfile format '/opt/oracle/dbackup/spfile_%u_%T.bak';
release channel c1;
}
rman>copy current controlfile for standby to '/opt/oracle/dbackup/standby.ctl';

6.复制文件至备库
[oracle@db1 ~]$ scp -r /opt/oracle/dbackup/ db2:/opt/oracle
[oracle@db1 ~]$ cd $ORACLE_HOME/dbs
[oracle@db1 dbs]$ scp -r orapwtpy100 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

7.恢复参数文件(db2)
RMAN> set dbid 2926260986
RMAN> startup nomount;
#这里会报错不用理会即可
RMAN> restore spfile to pfile '/tmp/tpy100.pfile' from '/opt/oracle/dbackup/spfile_04quaekm_20160219.bak';
#我们将参数文件恢复至/tmp/tpy100.pfile,因为这个是主库的参数文件，备库略有不同
8.修改备库参数文件(db2)
[oracle@db2 ~]$ vim /tmp/tpy100.pfile
#将里面的DB1 db1变成相应的DB2 db2,将DB1变成db1
tpy100.__db_cache_size=322961408
tpy100.__java_pool_size=4194304
tpy100.__large_pool_size=4194304
tpy100.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
tpy100.__pga_aggregate_target=339738624
tpy100.__sga_target=503316480
tpy100.__shared_io_pool_size=0
tpy100.__shared_pool_size=159383552
tpy100.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/tpy100/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/tpy100/control01.ctl','/opt/oracle/flash_recovery_area/tpy100/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area'
*.db_name='tpy100'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='DB2'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tpy100XDB)'
*.fal_client='DB2'
*.fal_server='DB1'
*.log_archive_config='dg_config=(db2,db1)'
*.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ valid_for=(all_logfiles,all_roles)  db_unique_name=db2'
*.log_archive_dest_2='service=db1 async  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='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area'
*.memory_target=843055104
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

9.准备RMAN恢复工作(db2)
创建相应的目录，复制备库控制文件到相应的位置
[oracle@db2 ~]$ mkdir -p /opt/oracle/admin/tpy100/adump
[oracle@db2 ~]$ mkdir -p /opt/oracle/oradata/tpy100
[oracle@db2 ~]$ mkdir -p /opt/oracle/flash_recovery_area/tpy100
[oracle@db2 ~]$ cp /opt/oracle/dbackup/standby.ctl /opt/oracle/oradata/tpy100/control01.ctl
[oracle@db2 ~]$ cp /opt/oracle/dbackup/standby.ctl /opt/oracle/flash_recovery_area/tpy100/control02.ctl
[oracle@db2 ~]$ lsnrctl start
#启动监听

10.生成备库参数文件(db2)

SQL> shutdown immediate;
SQL> startup nomount pfile='/tmp/tpy100.pfile'
SQL> create spfile from pfile='/tmp/tpy100.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/oradata/tpy100/system01.dbf'
先使用shutdown immediate关闭后再重新启动

SQL > alter database recover managed standby database using current logfile disconnect from session;

如果需要重启备库,则需要按如下命令进行重启
SQL> startup;

SQL> alter database recover managed standby database using current logfile disconnect from session;
注意：刚重启完你会发现主库的数据还没过来，但是过段时间就过来了，在生产环境中我们需要快速处理这个问题，以便减少宕机时间。
关闭完备库后，在主库查看
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秒自动连接，这边为了快读处理
解决办法：在主库执行
SQL> alter system set log_archive_dest_state_2= enable;
再查询，如果依然是如此则需要检查备库的网络及监听

测试有如下语句：
SQL> select sequence#,applied from v$archived_log;
SQL> select process,status from v$managed_standby;
SQL> select sequence# from v$log_history;

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

Oracle Data Guard 重要配置参数

基于同一主机配置 Oracle 11g Data Guard

探索Oracle之11g DataGuard

Oracle Data Guard （RAC+DG) 归档删除策略及脚本

Oracle Data Guard 的角色转换

Oracle Data Guard的日志FAL gap问题

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法